X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;ds=sidebyside;f=sql%2FPg-upgrade2%2Ftax_constraints.pl;h=cc2ccd2e46359f2ac9254da2b6ae66fc7330b1dd;hb=da2f5aa8cea79ff0a3e1e5e88e74cbf7e9e79794;hp=70fe83d3594fa1aa686588a6135fbac2fcb32472;hpb=067e94571b0f00cbb77c6b6fbeb1ed5e1e31402e;p=kivitendo-erp.git
diff --git a/sql/Pg-upgrade2/tax_constraints.pl b/sql/Pg-upgrade2/tax_constraints.pl
index 70fe83d35..cc2ccd2e4 100644
--- 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
-# @charset: utf-8
+package SL::DBUpgrade2::tax_constraints;
-use utf8;
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:") . "
$msg
" . $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= <db_query($query);
#check automatic tax accounts
$query= <selectrow_array($query);
+ my ($invalid_tax_account) = $self->dbh->selectrow_array($query);
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
- 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")) {
- push @{ $main::form->{TAX} }, $ref;
+ push @{ $::form->{TAX} }, $ref;
}
$sth->finish;
- $main::form->{invalid_tax_account} = 1;
+ $::form->{invalid_tax_account} = 1;
print_error_message();
return 0;
}
@@ -70,7 +56,7 @@ 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
@@ -84,16 +70,16 @@ 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")) {
- push @{ $main::form->{TAX} }, $ref;
+ push @{ $::form->{TAX} }, $ref;
}
$sth->finish;
- $main::form->{taxkey_is_null} = 1;
+ $::form->{taxkey_is_null} = 1;
print_error_message();
return 0;
}
@@ -108,7 +94,7 @@ 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:
@@ -123,7 +109,7 @@ SQL
AND tax_id IS NOT NULL;
SQL
- do_query($query);
+ $self->db_query($query);
#taxkeys.taxkey_id and taxkeys.tax_id are NULL:
@@ -135,7 +121,7 @@ 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
@@ -150,13 +136,13 @@ 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")) {
- push @{ $main::form->{TAXID} }, $ref;
+ push @{ $::form->{TAXID} }, $ref;
$rowcount++;
}
$sth->finish;
@@ -166,22 +152,22 @@ SQL
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|
- 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|
- 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
@@ -195,32 +181,31 @@ 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")) {
- push @{ $main::form->{TAXKEYS} }, $ref;
+ push @{ $::form->{TAXKEYS} }, $ref;
$rowcount++;
}
$sth->finish;
for my $i (0 .. $rowcount-1){
- $query= qq|
+ $query= < 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)
-|;
+ WHERE (tk1.chart_id = ?)
+ AND (tk1.startdate = ?)
+ AND (tk1.id <> (
+ SELECT id
+ FROM taxkeys
+ WHERE (chart_id = ?)
+ AND (startdate = ?)
+ LIMIT 1))
+SQL
- do_query($query);
+ $self->db_query($query, bind => [ ($::form->{TAXKEYS}[$i]->{chart_id}, $::form->{TAXKEYS}[$i]->{startdate}) x 2 ]);
}
#END CHECK OF taxkeys
@@ -233,28 +218,28 @@ 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= <db_query($query);
#Create foreign key for tax.chart_id to chart.id
$query= <db_query($query);
#Create NOT NULL constraint for tax.taxkey
$query= <db_query($query);
#Create NOT NULL constraint for taxkey.chart_id and foreign key for taxkey.chart_id
$query= <db_query($query);
#Create NOT NULL constraint for taxkey.startdate
$query= <db_query($query);
#Create NOT NULL constraint for taxkey.taxkey_id
$query= <db_query($query);
#Create NOT NULL constraint for taxkey.tax_id
$query= <db_query($query);
#The triple chart_id, taxkey_id, startdate should be unique:
$query= <db_query($query);
#ALL CONSTRAINTS WERE ADDED
return 1;
-}; # end do_update
+} # end run
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;