Buchungsjournal: Filter "Bearbeiter" gefixt

[mfinanz.git] / sql / Pg-upgrade2 / tax_constraints.pl

diff --git a/sql/Pg-upgrade2/tax_constraints.pl b/sql/Pg-upgrade2/tax_constraints.pl
index 70fe83d3594fa1aa686588a6135fbac2fcb32472..23d9b84b2c0fe4faa83bdaa15b55f39460fdfaec 100644 (file)
--- a/sql/Pg-upgrade2/tax_constraints.pl
+++ b/sql/Pg-upgrade2/tax_constraints.pl
@@ -1,30 +1,16 @@
 # @tag: tax_constraints
 # @description: Setzt Fremdschlüssel und andere constraints auf die Tabellen tax und taxkeys
 # @depends: release_3_0_0 charts_without_taxkey
 # @tag: tax_constraints
 # @description: Setzt Fremdschlüssel und andere constraints auf die Tabellen tax und taxkeys
 # @depends: release_3_0_0 charts_without_taxkey

-# @charset: utf-8
+package SL::DBUpgrade2::tax_constraints;

 
 

-use utf8;

 use strict;
 use strict;

-use SL::Locale;
-
-die("This script cannot be run from the command line.") unless ($main::form);
-
-sub mydberror {
-  my ($msg) = @_;
-  die($dbup_locale->text("Database update error:") . "<br>$msg<br>" . $DBI::errstr);
-}
+use utf8;

 
 

-sub do_query {
-  my ($query, $may_fail) = @_;
+use parent qw(SL::DBUpgrade2::Base);

 
 

-  if (!$dbh->do($query)) {
-    mydberror($query) unless ($may_fail);
-    $dbh->rollback();
-    $dbh->begin_work();
-  }
-}
+sub run {
+  my ($self) = @_;

 
 

-sub do_update {

   #CHECK CONSISTANCY OF tax
   #update tax.rate and tax.taxdescription in order to set later NOT NULL constraints
   my $query= <<SQL;
   #CHECK CONSISTANCY OF tax
   #update tax.rate and tax.taxdescription in order to set later NOT NULL constraints
   my $query= <<SQL;


@@ -32,14 +18,14 @@ sub do_update {
     UPDATE tax SET taxdescription='-' WHERE COALESCE(taxdescription, '') = '';
 SQL
 
     UPDATE tax SET taxdescription='-' WHERE COALESCE(taxdescription, '') = '';
 SQL
 

-  do_query($query);
+  $self->db_query($query);

 
   #check automatic tax accounts
   $query= <<SQL;
     SELECT count(*) FROM tax WHERE chart_id NOT IN (SELECT id FROM chart);
 SQL
 
 
   #check automatic tax accounts
   $query= <<SQL;
     SELECT count(*) FROM tax WHERE chart_id NOT IN (SELECT id FROM chart);
 SQL
 

-  my ($invalid_tax_account) = $dbh->selectrow_array($query);
+  my ($invalid_tax_account) = $self->dbh->selectrow_array($query);

 
   if ($invalid_tax_account > 0){
     #list all invalid tax accounts
 
   if ($invalid_tax_account > 0){
     #list all invalid tax accounts


@@ -51,16 +37,16 @@ SQL
       FROM tax WHERE chart_id NOT IN (SELECT id FROM chart);
 SQL
 
       FROM tax WHERE chart_id NOT IN (SELECT id FROM chart);
 SQL
 

-    my $sth = $dbh->prepare($query);
-    $sth->execute || $main::form->dberror($query);
+    my $sth = $self->dbh->prepare($query);
+    $sth->execute || $::form->dberror($query);

 
 

-    $main::form->{TAX} = [];
+    $::form->{TAX} = [];

     while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
     while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {

-      push @{ $main::form->{TAX} }, $ref;
+      push @{ $::form->{TAX} }, $ref;

     }
     $sth->finish;
 
     }
     $sth->finish;
 

-    $main::form->{invalid_tax_account} = 1;
+    $::form->{invalid_tax_account} = 1;

     print_error_message();
     return 0;
   }
     print_error_message();
     return 0;
   }


@@ -70,7 +56,7 @@ SQL
     SELECT count(*) FROM tax WHERE taxkey IS NULL;
 SQL
 
     SELECT count(*) FROM tax WHERE taxkey IS NULL;
 SQL
 

-  my ($taxkey_is_null) = $dbh->selectrow_array($query);
+  my ($taxkey_is_null) = $self->dbh->selectrow_array($query);

 
   if ($taxkey_is_null > 0){
     #list all invalid tax accounts
 
   if ($taxkey_is_null > 0){
     #list all invalid tax accounts


@@ -84,16 +70,16 @@ SQL
       WHERE taxkey IS NULL;
 SQL
 
       WHERE taxkey IS NULL;
 SQL
 

-    my $sth = $dbh->prepare($query);
-    $sth->execute || $main::form->dberror($query);
+    my $sth = $self->dbh->prepare($query);
+    $sth->execute || $::form->dberror($query);

 
 

-    $main::form->{TAX} = [];
+    $::form->{TAX} = [];

     while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
     while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {

-      push @{ $main::form->{TAX} }, $ref;
+      push @{ $::form->{TAX} }, $ref;

     }
     $sth->finish;
 
     }
     $sth->finish;
 

-    $main::form->{taxkey_is_null} = 1;
+    $::form->{taxkey_is_null} = 1;

     print_error_message();
     return 0;
   }
     print_error_message();
     return 0;
   }


@@ -108,7 +94,7 @@ SQL
     OR startdate IS NULL;
 SQL
 
     OR startdate IS NULL;
 SQL
 

-  do_query($query);
+  $self->db_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:


@@ -123,7 +109,7 @@ SQL
     AND tax_id IS NOT NULL;
 SQL
 
     AND tax_id IS NOT NULL;
 SQL
 

-  do_query($query);
+  $self->db_query($query);

 
   #taxkeys.taxkey_id and taxkeys.tax_id are NULL:
 
 
   #taxkeys.taxkey_id and taxkeys.tax_id are NULL:
 


@@ -135,7 +121,7 @@ SQL
     AND tax_id IS NULL;
 SQL
 
     AND tax_id IS NULL;
 SQL
 

-  do_query($query);
+  $self->db_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
 


@@ -150,13 +136,13 @@ SQL
     AND tax_id IS NULL;
 SQL
 
     AND tax_id IS NULL;
 SQL
 

-  my $sth = $dbh->prepare($query);
-  $sth->execute || $main::form->dberror($query);
+  my $sth = $self->dbh->prepare($query);
+  $sth->execute || $::form->dberror($query);

 
 

-  $main::form->{TAXID} = [];
+  $::form->{TAXID} = [];

   my $rowcount = 0;
   while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
   my $rowcount = 0;
   while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {

-    push @{ $main::form->{TAXID} }, $ref;
+    push @{ $::form->{TAXID} }, $ref;

     $rowcount++;
   }
   $sth->finish;
     $rowcount++;
   }
   $sth->finish;


@@ -166,22 +152,22 @@ SQL
   my $tax_id;
   for my $i (0 .. $rowcount-1){
     $query= qq|
   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
+      SELECT id FROM tax WHERE rate = 0 and taxkey=| . $::form->{TAXID}[$i]->{taxkey_id} . qq| LIMIT 1

 |;
 |;

-    ($tax_id) = $dbh->selectrow_array($query);
+    ($tax_id) = $self->dbh->selectrow_array($query);

     if ( not $tax_id ){
       $insertquery=qq|
     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') . $::form->{TAXID}[$i]->{taxkey_id} .  $::locale->text('. Automatically generated.') .
+        qq|', | . $::form->{TAXID}[$i]->{taxkey_id} . qq|);

 |;
 |;

-      do_query($insertquery);
-      ($tax_id) = $dbh->selectrow_array($query);
-      $tax_id || $main::form->dberror($query);
+      $self->db_query($insertquery);
+      ($tax_id) = $self->dbh->selectrow_array($query);
+      $tax_id || $::form->dberror($query);

     }
     $updatequery = qq|
     }
     $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 = | . $::form->{TAXID}[$i]->{taxkey_id} . qq| AND tax_id IS NULL

 |;
 |;

-    do_query($updatequery);
+    $self->db_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


@@ -195,13 +181,13 @@ SQL
            AND   tk2.startdate = tk1.startdate) > 1;
 SQL
 
            AND   tk2.startdate = tk1.startdate) > 1;
 SQL
 

-  $sth = $dbh->prepare($query);
-  $sth->execute || $main::form->dberror($query);
+  $sth = $self->dbh->prepare($query);
+  $sth->execute || $::form->dberror($query);

 
 

-  $main::form->{TAXKEYS} = [];
+  $::form->{TAXKEYS} = [];

   $rowcount = 0;
   while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
   $rowcount = 0;
   while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {

-    push @{ $main::form->{TAXKEYS} }, $ref;
+    push @{ $::form->{TAXKEYS} }, $ref;

     $rowcount++;
   }
   $sth->finish;
     $rowcount++;
   }
   $sth->finish;


@@ -215,12 +201,12 @@ SQL
             AND   tk2.startdate = tk1.startdate) > 1
       AND NOT tk1.id = (SELECT id
                         FROM taxkeys
             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|'
+                        WHERE chart_id  = | . $::form->{TAXKEYS}[$i]->{chart_id} . qq|
+                        AND   startdate = '| . $::form->{TAXKEYS}[$i]->{startdate} . qq|'

                         LIMIT 1)
 |;
 
                         LIMIT 1)
 |;
 

-    do_query($query);
+    $self->db_query($query);

   }
 
   #END CHECK OF taxkeys
   }
 
   #END CHECK OF taxkeys


@@ -233,28 +219,28 @@ SQL
     ALTER TABLE tax ALTER COLUMN rate SET DEFAULT 0;
 SQL
 
     ALTER TABLE tax ALTER COLUMN rate SET DEFAULT 0;
 SQL
 

-  do_query($query);
+  $self->db_query($query);

 
   #Create NOT NULL constraint for tax.description
   $query= <<SQL;
     ALTER TABLE tax ALTER COLUMN taxdescription SET NOT NULL;
 SQL
 
 
   #Create NOT NULL constraint for tax.description
   $query= <<SQL;
     ALTER TABLE tax ALTER COLUMN taxdescription SET NOT NULL;
 SQL
 

-  do_query($query);
+  $self->db_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);
 SQL
 
 
   #Create foreign key for tax.chart_id to chart.id
   $query= <<SQL;
     ALTER TABLE tax ADD FOREIGN KEY (chart_id) REFERENCES chart(id);
 SQL
 

-  do_query($query);
+  $self->db_query($query);

 
   #Create NOT NULL constraint for tax.taxkey
   $query= <<SQL;
     ALTER TABLE tax ALTER COLUMN taxkey SET NOT NULL;
 SQL
 
 
   #Create NOT NULL constraint for tax.taxkey
   $query= <<SQL;
     ALTER TABLE tax ALTER COLUMN taxkey SET NOT NULL;
 SQL
 

-  do_query($query);
+  $self->db_query($query);

 
   #Create NOT NULL constraint for taxkey.chart_id and foreign key for taxkey.chart_id
   $query= <<SQL;
 
   #Create NOT NULL constraint for taxkey.chart_id and foreign key for taxkey.chart_id
   $query= <<SQL;


@@ -262,43 +248,43 @@ SQL
     ALTER TABLE taxkeys ADD FOREIGN KEY (chart_id) REFERENCES chart(id);
 SQL
 
     ALTER TABLE taxkeys ADD FOREIGN KEY (chart_id) REFERENCES chart(id);
 SQL
 

-  do_query($query);
+  $self->db_query($query);

 
   #Create NOT NULL constraint for taxkey.startdate
   $query= <<SQL;
     ALTER TABLE taxkeys ALTER COLUMN startdate SET NOT NULL;
 SQL
 
 
   #Create NOT NULL constraint for taxkey.startdate
   $query= <<SQL;
     ALTER TABLE taxkeys ALTER COLUMN startdate SET NOT NULL;
 SQL
 

-  do_query($query);
+  $self->db_query($query);

 
   #Create NOT NULL constraint for taxkey.taxkey_id
   $query= <<SQL;
     ALTER TABLE taxkeys ALTER COLUMN taxkey_id SET NOT NULL;
 SQL
 
 
   #Create NOT NULL constraint for taxkey.taxkey_id
   $query= <<SQL;
     ALTER TABLE taxkeys ALTER COLUMN taxkey_id SET NOT NULL;
 SQL
 

-  do_query($query);
+  $self->db_query($query);

 
   #Create NOT NULL constraint for taxkey.tax_id
   $query= <<SQL;
     ALTER TABLE taxkeys ALTER COLUMN tax_id SET NOT NULL;
 SQL
 
 
   #Create NOT NULL constraint for taxkey.tax_id
   $query= <<SQL;
     ALTER TABLE taxkeys ALTER COLUMN tax_id SET NOT NULL;
 SQL
 

-  do_query($query);
+  $self->db_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);
 SQL
 
 
   #The triple chart_id, taxkey_id, startdate should be unique:
   $query= <<SQL;
     CREATE UNIQUE INDEX taxkeys_chartid_startdate ON taxkeys(chart_id, startdate);
 SQL
 

-  do_query($query);
+  $self->db_query($query);

   #ALL CONSTRAINTS WERE ADDED
 
   return 1;
   #ALL CONSTRAINTS WERE ADDED
 
   return 1;

-}; # end do_update
+} # end run

 
 
 sub print_error_message {
 
 
 sub print_error_message {

-  print $main::form->parse_html_template("dbupgrade/tax_constraints");
+  print $::form->parse_html_template("dbupgrade/tax_constraints");

 }
 
 }
 

-return do_update();
+1;