sql/Pg-upgrade2/tax_constraints.pl

   1 # @tag: tax_constraints
   2 # @description: Setzt Fremdschlüssel und andere constraints auf die Tabellen tax und taxkeys
   3 # @depends: release_3_0_0 charts_without_taxkey
   4 # @charset: utf-8
   5
   6 use utf8;
   7 use strict;
   8 use SL::Locale;
   9
  10 die("This script cannot be run from the command line.") unless ($main::form);
  11
  12 sub mydberror {
  13   my ($msg) = @_;
  14   die($dbup_locale->text("Database update error:") . "<br>$msg<br>" . $DBI::errstr);
  15 }
  16
  17 sub do_query {
  18   my ($query, $may_fail) = @_;
  19
  20   if (!$dbh->do($query)) {
  21     mydberror($query) unless ($may_fail);
  22     $dbh->rollback();
  23     $dbh->begin_work();
  24   }
  25 }
  26
  27 sub do_update {
  28   #CHECK CONSISTANCY OF tax
  29   #update tax.rate and tax.taxdescription in order to set later NOT NULL constraints
  30   my $query= <<SQL;
  31     UPDATE tax SET rate=0 WHERE rate IS NULL;
  32     UPDATE tax SET taxdescription='-' WHERE COALESCE(taxdescription, '') = '';
  33 SQL
  34
  35   do_query($query);
  36
  37   #check automatic tax accounts
  38   $query= <<SQL;
  39     SELECT count(*) FROM tax WHERE chart_id NOT IN (SELECT id FROM chart);
  40 SQL
  41
  42   my ($invalid_tax_account) = $dbh->selectrow_array($query);
  43
  44   if ($invalid_tax_account > 0){
  45     #list all invalid tax accounts
  46     $query = <<SQL;
  47       SELECT id,
  48         taxkey,
  49         taxdescription,
  50         round(rate * 100, 2) AS rate
  51       FROM tax WHERE chart_id NOT IN (SELECT id FROM chart);
  52 SQL
  53
  54     my $sth = $dbh->prepare($query);
  55     $sth->execute || $main::form->dberror($query);
  56
  57     $main::form->{TAX} = [];
  58     while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
  59       push @{ $main::form->{TAX} }, $ref;
  60     }
  61     $sth->finish;
  62
  63     $main::form->{invalid_tax_account} = 1;
  64     print_error_message();
  65     return 0;
  66   }
  67
  68   #check entry tax.taxkey of NOT NULL
  69   $query= <<SQL;
  70     SELECT count(*) FROM tax WHERE taxkey IS NULL;
  71 SQL
  72
  73   my ($taxkey_is_null) = $dbh->selectrow_array($query);
  74
  75   if ($taxkey_is_null > 0){
  76     #list all invalid tax accounts
  77     $query = <<SQL;
  78       SELECT id,
  79         taxdescription,
  80         round(rate * 100, 2) AS rate,
  81         (SELECT accno FROM chart WHERE id = chart_id) AS taxnumber,
  82         (SELECT description FROM chart WHERE id = chart_id) AS account_description
  83       FROM tax
  84       WHERE taxkey IS NULL;
  85 SQL
  86
  87     my $sth = $dbh->prepare($query);
  88     $sth->execute || $main::form->dberror($query);
  89
  90     $main::form->{TAX} = [];
  91     while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
  92       push @{ $main::form->{TAX} }, $ref;
  93     }
  94     $sth->finish;
  95
  96     $main::form->{taxkey_is_null} = 1;
  97     print_error_message();
  98     return 0;
  99   }
 100   #END CHECK OF tax
 101
 102   #CHECK CONSISTANCY OF taxkeys
 103   #delete invalide entries in taxkeys
 104   $query= <<SQL;
 105     DELETE FROM taxkeys
 106     WHERE chart_id IS NULL
 107     OR chart_id NOT IN (SELECT id FROM chart)
 108     OR startdate IS NULL;
 109 SQL
 110
 111   do_query($query);
 112
 113   #There are 3 cases for taxkeys.tax_id and taxkeys.taxkey_id
 114   #taxkeys.taxkey_id is NULL and taxkeys.tax_id is not NULL:
 115
 116   #Update taxkeys.taxkey_id with tax.taxkey
 117   $query= <<SQL;
 118     UPDATE taxkeys
 119     SET taxkey_id = (SELECT t.taxkey
 120                         FROM tax t
 121                         WHERE t.id=tax_id)
 122     WHERE taxkey_id IS NULL
 123     AND tax_id IS NOT NULL;
 124 SQL
 125
 126   do_query($query);
 127
 128   #taxkeys.taxkey_id and taxkeys.tax_id are NULL:
 129
 130   #Set taxkey 0 in this case:
 131   $query= <<SQL;
 132     UPDATE taxkeys
 133     SET taxkey_id = 0, tax_id = (SELECT id FROM tax WHERE taxkey=0)
 134     WHERE taxkey_id IS NULL
 135     AND tax_id IS NULL;
 136 SQL
 137
 138   do_query($query);
 139
 140   #Last case where taxkeys.taxkey_id is not null and taxkeys.tax_id is null
 141
 142   #If such entries exist we update with an entry in tax where tax.rate=0
 143   #and tax.taxkey corresponds to taxkeys.taxkey_id.
 144   #If no entry in tax with rate 0 and taxkey taxkeys.taxkey_id exists
 145   #we create one.
 146   $query= <<SQL;
 147     SELECT DISTINCT taxkey_id
 148     FROM taxkeys
 149     WHERE taxkey_id IS NOT NULL
 150     AND tax_id IS NULL;
 151 SQL
 152
 153   my $sth = $dbh->prepare($query);
 154   $sth->execute || $main::form->dberror($query);
 155
 156   $main::form->{TAXID} = [];
 157   my $rowcount = 0;
 158   while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
 159     push @{ $main::form->{TAXID} }, $ref;
 160     $rowcount++;
 161   }
 162   $sth->finish;
 163
 164   my $insertquery;
 165   my $updatequery;
 166   my $tax_id;
 167   for my $i (0 .. $rowcount-1){
 168     $query= qq|
 169       SELECT id FROM tax WHERE rate = 0 and taxkey=| . $main::form->{TAXID}[$i]->{taxkey_id} . qq| LIMIT 1
 170 |;
 171     ($tax_id) = $dbh->selectrow_array($query);
 172     if ( not $tax_id ){
 173       $insertquery=qq|
 174         INSERT INTO tax (rate, taxdescription, taxkey) VALUES (0, '| . $::locale->text('0% tax with taxkey') . $main::form->{TAXID}[$i]->{taxkey_id} .  $::locale->text('. Automatically generated.') .
 175         qq|', | . $main::form->{TAXID}[$i]->{taxkey_id} . qq|);
 176 |;
 177       do_query($insertquery);
 178       ($tax_id) = $dbh->selectrow_array($query);
 179       $tax_id || $main::form->dberror($query);
 180     }
 181     $updatequery = qq|
 182       UPDATE taxkeys SET tax_id= | . $tax_id . qq| WHERE taxkey_id = | . $main::form->{TAXID}[$i]->{taxkey_id} . qq| AND tax_id IS NULL
 183 |;
 184     do_query($updatequery);
 185   }
 186
 187   #The triple taxkey_id, chart_id, startdate in taxkeys has to be unique
 188   #Select these entries:
 189   $query= <<SQL;
 190     SELECT DISTINCT tk1.chart_id AS chart_id, tk1.startdate AS startdate
 191     FROM taxkeys tk1
 192     WHERE (SELECT count(*)
 193            FROM taxkeys tk2
 194            WHERE tk2.chart_id  = tk1.chart_id
 195            AND   tk2.startdate = tk1.startdate) > 1;
 196 SQL
 197
 198   $sth = $dbh->prepare($query);
 199   $sth->execute || $main::form->dberror($query);
 200
 201   $main::form->{TAXKEYS} = [];
 202   $rowcount = 0;
 203   while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
 204     push @{ $main::form->{TAXKEYS} }, $ref;
 205     $rowcount++;
 206   }
 207   $sth->finish;
 208
 209   for my $i (0 .. $rowcount-1){
 210     $query= qq|
 211       DELETE FROM taxkeys tk1
 212       WHERE (SELECT count(*)
 213             FROM taxkeys tk2
 214             WHERE tk2.chart_id  = tk1.chart_id
 215             AND   tk2.startdate = tk1.startdate) > 1
 216       AND NOT tk1.id = (SELECT id
 217                         FROM taxkeys
 218                         WHERE chart_id  = | . $main::form->{TAXKEYS}[$i]->{chart_id} . qq|
 219                         AND   startdate = '| . $main::form->{TAXKEYS}[$i]->{startdate} . qq|'
 220                         LIMIT 1)
 221 |;
 222
 223     do_query($query);
 224   }
 225
 226   #END CHECK OF taxkeys
 227
 228   #ADD CONSTRAINTS:
 229   #Now the database is consistent, so we can add constraints:
 230   #Crate NOT NULL constraint for tax.rate with default value 0
 231   $query= <<SQL;
 232     ALTER TABLE tax ALTER COLUMN rate SET NOT NULL;
 233     ALTER TABLE tax ALTER COLUMN rate SET DEFAULT 0;
 234 SQL
 235
 236   do_query($query);
 237
 238   #Create NOT NULL constraint for tax.description
 239   $query= <<SQL;
 240     ALTER TABLE tax ALTER COLUMN taxdescription SET NOT NULL;
 241 SQL
 242
 243   do_query($query);
 244
 245   #Create foreign key for tax.chart_id to chart.id
 246   $query= <<SQL;
 247     ALTER TABLE tax ADD FOREIGN KEY (chart_id) REFERENCES chart(id);
 248 SQL
 249
 250   do_query($query);
 251
 252   #Create NOT NULL constraint for tax.taxkey
 253   $query= <<SQL;
 254     ALTER TABLE tax ALTER COLUMN taxkey SET NOT NULL;
 255 SQL
 256
 257   do_query($query);
 258
 259   #Create NOT NULL constraint for taxkey.chart_id and foreign key for taxkey.chart_id
 260   $query= <<SQL;
 261     ALTER TABLE taxkeys ALTER COLUMN chart_id SET NOT NULL;
 262     ALTER TABLE taxkeys ADD FOREIGN KEY (chart_id) REFERENCES chart(id);
 263 SQL
 264
 265   do_query($query);
 266
 267   #Create NOT NULL constraint for taxkey.startdate
 268   $query= <<SQL;
 269     ALTER TABLE taxkeys ALTER COLUMN startdate SET NOT NULL;
 270 SQL
 271
 272   do_query($query);
 273
 274   #Create NOT NULL constraint for taxkey.taxkey_id
 275   $query= <<SQL;
 276     ALTER TABLE taxkeys ALTER COLUMN taxkey_id SET NOT NULL;
 277 SQL
 278
 279   do_query($query);
 280
 281   #Create NOT NULL constraint for taxkey.tax_id
 282   $query= <<SQL;
 283     ALTER TABLE taxkeys ALTER COLUMN tax_id SET NOT NULL;
 284 SQL
 285
 286   do_query($query);
 287
 288   #The triple chart_id, taxkey_id, startdate should be unique:
 289   $query= <<SQL;
 290     CREATE UNIQUE INDEX taxkeys_chartid_startdate ON taxkeys(chart_id, startdate);
 291 SQL
 292
 293   do_query($query);
 294   #ALL CONSTRAINTS WERE ADDED
 295
 296   return 1;
 297 }; # end do_update
 298
 299
 300 sub print_error_message {
 301   print $main::form->parse_html_template("dbupgrade/tax_constraints");
 302 }
 303
 304 return do_update();