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