/ - Diff - projekt kivitendo - wissen

« Zurück | Weiter »

Revision 32ddb71c

Von Niclas Zimmermann vor mehr als 11 Jahren hinzugefügt

ID 32ddb71c1b2a113b297e729817dda69a1afa203c
Vorgänger 18931692
Nachfolger b4359a89

Verbesserung Upgrade-Script

Dieser Commit enthält noch einige kleine Verbesserungen zum Update
tax_constraints.pl. Es wurden Einrückungen verändert, trailing white-
spaces entfernt und eine Abhängigkeit hinzugefügt.

     # @tag: tax_constraints
     # @description: Setzt Fremdschlüssel und andere constraints auf die Tabellen tax und taxkeys
     # @depends: release_3_0_0
     # @depends: release_3_0_0 charts_without_taxkey
     # @charset: utf-8
     use utf8;
-...
+    }
     sub do_update {
     #CHECK CONSISTANCY OF tax
       #CHECK CONSISTANCY OF tax
       #update tax.rate and tax.taxdescription in order to set later NOT NULL constraints
     my $query= <<SQL;
     UPDATE tax SET rate=0 WHERE rate IS NULL;
     UPDATE tax SET taxdescription='-' WHERE taxdescription IS NULL;
       my $query= <<SQL;
         UPDATE tax SET rate=0 WHERE rate IS NULL;
         UPDATE tax SET taxdescription='-' WHERE COALESCE(taxdescription, '') = '';
     SQL
       do_query($query);
       #check automatic tax accounts
       $query= <<SQL;
     SELECT count(*) FROM tax WHERE chart_id NOT IN (SELECT id FROM chart);
         SELECT count(*) FROM tax WHERE chart_id NOT IN (SELECT id FROM chart);
     SQL
       my ($invalid_tax_account) = $dbh->selectrow_array($query);
       if ($invalid_tax_account > 0){
         #list all invalid tax accounts
         $query = <<SQL;
     SELECT id,
       taxkey,
       taxdescription,
       round(rate * 100, 2) AS rate
     FROM tax WHERE chart_id NOT IN (SELECT id FROM chart);
           SELECT id,
             taxkey,
             taxdescription,
             round(rate * 100, 2) AS rate
           FROM tax WHERE chart_id NOT IN (SELECT id FROM chart);
     SQL
         my $sth = $dbh->prepare($query);
-...
         $main::form->{invalid_tax_account} = 1;
         print_error_message();
         return 0;
+      }
+      }
       #check entry tax.taxkey of NOT NULL
       $query= <<SQL;
     SELECT count(*) FROM tax WHERE taxkey IS NULL;
         SELECT count(*) FROM tax WHERE taxkey IS NULL;
     SQL
       my ($taxkey_is_null) = $dbh->selectrow_array($query);
       if ($taxkey_is_null > 0){
         #list all invalid tax accounts
         $query = <<SQL;
     SELECT id,
       taxdescription,
       round(rate * 100, 2) AS rate,
       (SELECT accno FROM chart WHERE id = chart_id) AS taxnumber,
       (SELECT description FROM chart WHERE id = chart_id) AS account_description
     FROM tax
     WHERE taxkey IS NULL;
           SELECT id,
             taxdescription,
             round(rate * 100, 2) AS rate,
             (SELECT accno FROM chart WHERE id = chart_id) AS taxnumber,
             (SELECT description FROM chart WHERE id = chart_id) AS account_description
           FROM tax
           WHERE taxkey IS NULL;
     SQL
         my $sth = $dbh->prepare($query);
-...
         $main::form->{taxkey_is_null} = 1;
         print_error_message();
         return 0;
+      }
     #END CHECK OF tax
+      }
       #END CHECK OF tax
     #CHECK CONSISTANCY OF taxkeys
       #CHECK CONSISTANCY OF taxkeys
       #delete invalide entries in taxkeys
       $query= <<SQL;
     DELETE FROM taxkeys
     WHERE chart_id IS NULL
     OR chart_id NOT IN (SELECT id FROM chart)
     OR startdate IS NULL;
         DELETE FROM taxkeys
         WHERE chart_id IS NULL
         OR chart_id NOT IN (SELECT id FROM chart)
         OR startdate IS NULL;
     SQL
       do_query($query);
     #There are 3 cases for taxkeys.tax_id and taxkeys.taxkey_id
     #taxkeys.taxkey_id is NULL and taxkeys.tax_id is not NULL:
       #There are 3 cases for taxkeys.tax_id and taxkeys.taxkey_id
       #taxkeys.taxkey_id is NULL and taxkeys.tax_id is not NULL:
       #Update taxkeys.taxkey_id with tax.taxkey
       $query= <<SQL;
     UPDATE taxkeys
     SET taxkey_id = (SELECT t.taxkey
                             FROM tax t
         UPDATE taxkeys
         SET taxkey_id = (SELECT t.taxkey
                             FROM tax t
                             WHERE t.id=tax_id)
     WHERE taxkey_id IS NULL
     AND tax_id IS NOT NULL;
         WHERE taxkey_id IS NULL
         AND tax_id IS NOT NULL;
     SQL
       do_query($query);
     #taxkeys.taxkey_id and taxkeys.tax_id are NULL:
       #taxkeys.taxkey_id and taxkeys.tax_id are NULL:
       #Set taxkey 0 in this case:
       $query= <<SQL;
     UPDATE taxkeys
     SET taxkey_id = 0, tax_id = (SELECT id FROM tax WHERE taxkey=0)
     WHERE taxkey_id IS NULL
     AND tax_id IS NULL;
         UPDATE taxkeys
         SET taxkey_id = 0, tax_id = (SELECT id FROM tax WHERE taxkey=0)
         WHERE taxkey_id IS NULL
         AND tax_id IS NULL;
     SQL
       do_query($query);
     #Last case where taxkeys.taxkey_id is not null and taxkeys.tax_id is null
       #Last case where taxkeys.taxkey_id is not null and taxkeys.tax_id is null
       #If such entries exist we update with an entry in tax where tax.rate=0
       #and tax.taxkey corresponds to taxkeys.taxkey_id.
       #If no entry in tax with rate 0 and taxkey taxkeys.taxkey_id exists
       #If no entry in tax with rate 0 and taxkey taxkeys.taxkey_id exists
       #we create one.
       $query= <<SQL;
     SELECT DISTINCT taxkey_id
     FROM taxkeys
     WHERE taxkey_id IS NOT NULL
     AND tax_id IS NULL;
         SELECT DISTINCT taxkey_id
         FROM taxkeys
         WHERE taxkey_id IS NOT NULL
         AND tax_id IS NULL;
     SQL
       my $sth = $dbh->prepare($query);
-...
         $rowcount++;
+      }
       $sth->finish;
       my $insertquery;
       my $updatequery;
       my $tax_id;
       for my $i (0 .. $rowcount-1){
         $query= qq|
     SELECT id FROM tax WHERE rate = 0 and taxkey=| . $main::form->{TAXID}[$i]->{taxkey_id} . qq| LIMIT 1
     |;
         ($tax_id) = $dbh->selectrow_array($query);
           SELECT id FROM tax WHERE rate = 0 and taxkey=| . $main::form->{TAXID}[$i]->{taxkey_id} . qq| LIMIT 1
     |;
         ($tax_id) = $dbh->selectrow_array($query);
         if ( not $tax_id ){
           $insertquery=qq|
     INSERT INTO tax (rate, taxdescription, taxkey) VALUES (0, '| . $::locale->text('0% tax with taxkey') . $main::form->{TAXID}[$i]->{taxkey_id} .  $::locale->text('. Automatically generated.') .
     qq|', | . $main::form->{TAXID}[$i]->{taxkey_id} . qq|);
             INSERT INTO tax (rate, taxdescription, taxkey) VALUES (0, '| . $::locale->text('0% tax with taxkey') . $main::form->{TAXID}[$i]->{taxkey_id} .  $::locale->text('. Automatically generated.') .
             qq|', | . $main::form->{TAXID}[$i]->{taxkey_id} . qq|);
     |;
           do_query($insertquery);
           ($tax_id) = $dbh->selectrow_array($query);
           $tax_id || $main::form->dberror($query);
           $tax_id || $main::form->dberror($query);
+        }
         $updatequery = qq|
     UPDATE taxkeys SET tax_id= | . $tax_id . qq| WHERE taxkey_id = | . $main::form->{TAXID}[$i]->{taxkey_id} . qq| AND tax_id IS NULL
           UPDATE taxkeys SET tax_id= | . $tax_id . qq| WHERE taxkey_id = | . $main::form->{TAXID}[$i]->{taxkey_id} . qq| AND tax_id IS NULL
     |;
         do_query($updatequery);
+      }
     #The triple taxkey_id, chart_id, startdate in taxkeys has to be unique
       #The triple taxkey_id, chart_id, startdate in taxkeys has to be unique
       #Select these entries:
       $query= <<SQL;
     SELECT DISTINCT tk1.chart_id AS chart_id, tk1.startdate AS startdate
     FROM taxkeys tk1
     WHERE (SELECT count(*)
            FROM taxkeys tk2
            WHERE tk2.chart_id  = tk1.chart_id
            AND   tk2.startdate = tk1.startdate) > 1;
         SELECT DISTINCT tk1.chart_id AS chart_id, tk1.startdate AS startdate
         FROM taxkeys tk1
         WHERE (SELECT count(*)
                FROM taxkeys tk2
                WHERE tk2.chart_id  = tk1.chart_id
                AND   tk2.startdate = tk1.startdate) > 1;
     SQL
       $sth = $dbh->prepare($query);
-...
       $sth->finish;
       for my $i (0 .. $rowcount-1){
         $query= qq|
     DELETE FROM taxkeys tk1
     WHERE (SELECT count(*)
            FROM taxkeys tk2
            WHERE tk2.chart_id  = tk1.chart_id
            AND   tk2.startdate = tk1.startdate) > 1
     AND NOT tk1.id = (SELECT id
                       FROM taxkeys
                       WHERE chart_id  = | . $main::form->{TAXKEYS}[$i]->{chart_id} . qq|
                       AND   startdate = '| . $main::form->{TAXKEYS}[$i]->{startdate} . qq|'
                       LIMIT 1)
         $query= qq|
           DELETE FROM taxkeys tk1
           WHERE (SELECT count(*)
                 FROM taxkeys tk2
                 WHERE tk2.chart_id  = tk1.chart_id
                 AND   tk2.startdate = tk1.startdate) > 1
           AND NOT tk1.id = (SELECT id
                             FROM taxkeys
                             WHERE chart_id  = | . $main::form->{TAXKEYS}[$i]->{chart_id} . qq|
                             AND   startdate = '| . $main::form->{TAXKEYS}[$i]->{startdate} . qq|'
                             LIMIT 1)
     |;
         do_query($query);
+      }
     #END CHECK OF taxkeys
       #END CHECK OF taxkeys
     #ADD CONSTRAINTS:
     #Now the database is consistent, so we can add constraints:
       #ADD CONSTRAINTS:
       #Now the database is consistent, so we can add constraints:
       #Crate NOT NULL constraint for tax.rate with default value 0
       $query= <<SQL;
     ALTER TABLE tax ALTER COLUMN rate SET NOT NULL;
     ALTER TABLE tax ALTER COLUMN rate SET DEFAULT 0;
         ALTER TABLE tax ALTER COLUMN rate SET NOT NULL;
         ALTER TABLE tax ALTER COLUMN rate SET DEFAULT 0;
     SQL
       do_query($query);
       #Create NOT NULL constraint for tax.description
       $query= <<SQL;
     ALTER TABLE tax ALTER COLUMN taxdescription SET NOT NULL;
         ALTER TABLE tax ALTER COLUMN taxdescription SET NOT NULL;
     SQL
       do_query($query);
       #Create foreign key for tax.chart_id to chart.id
       $query= <<SQL;
     ALTER TABLE tax ADD FOREIGN KEY (chart_id) REFERENCES chart(id);
         ALTER TABLE tax ADD FOREIGN KEY (chart_id) REFERENCES chart(id);
     SQL
       do_query($query);
       #Create NOT NULL constraint for tax.taxkey
       $query= <<SQL;
     ALTER TABLE tax ALTER COLUMN taxkey SET NOT NULL;
         ALTER TABLE tax ALTER COLUMN taxkey SET NOT NULL;
     SQL
       do_query($query);
       #Create NOT NULL constraint for taxkey.chart_id and foreign key for taxkey.chart_id
       $query= <<SQL;
     ALTER TABLE taxkeys ALTER COLUMN chart_id SET NOT NULL;
     ALTER TABLE taxkeys ADD FOREIGN KEY (chart_id) REFERENCES chart(id);
         ALTER TABLE taxkeys ALTER COLUMN chart_id SET NOT NULL;
         ALTER TABLE taxkeys ADD FOREIGN KEY (chart_id) REFERENCES chart(id);
     SQL
       do_query($query);
       #Create NOT NULL constraint for taxkey.startdate
       $query= <<SQL;
     ALTER TABLE taxkeys ALTER COLUMN startdate SET NOT NULL;
         ALTER TABLE taxkeys ALTER COLUMN startdate SET NOT NULL;
     SQL
       do_query($query);
       #Create NOT NULL constraint for taxkey.taxkey_id
       $query= <<SQL;
     ALTER TABLE taxkeys ALTER COLUMN taxkey_id SET NOT NULL;
         ALTER TABLE taxkeys ALTER COLUMN taxkey_id SET NOT NULL;
     SQL
       do_query($query);
       #Create NOT NULL constraint for taxkey.tax_id
       $query= <<SQL;
     ALTER TABLE taxkeys ALTER COLUMN tax_id SET NOT NULL;
         ALTER TABLE taxkeys ALTER COLUMN tax_id SET NOT NULL;
     SQL
       do_query($query);
       #The triple chart_id, taxkey_id, startdate should be unique:
       $query= <<SQL;
     CREATE UNIQUE INDEX taxkeys_chartid_startdate ON taxkeys(chart_id, startdate);
         CREATE UNIQUE INDEX taxkeys_chartid_startdate ON taxkeys(chart_id, startdate);
     SQL
       do_query($query);
     #ALL CONSTRAINTS WERE ADDED
       #ALL CONSTRAINTS WERE ADDED
       return 1;
     }; # end do_update

Auch abrufbar als: Unified diff

Projekt

Allgemein

Profil

projekt kivitendo

Revision 32ddb71c

Von Niclas Zimmermann vor mehr als 11 Jahren hinzugefügt