kivitendo/sql/Pg-upgrade2/tax_constraints.pl @ 7b1da9c3
| 18931692 | Niclas Zimmermann | # @tag: tax_constraints
|
||
# @description: Setzt Fremdschlüssel und andere constraints auf die Tabellen tax und taxkeys
|
||||
| 32ddb71c | Niclas Zimmermann | # @depends: release_3_0_0 charts_without_taxkey
|
||
| 347f2cff | Moritz Bunkus | package SL::DBUpgrade2::tax_constraints;
|
||
| 18931692 | Niclas Zimmermann | |||
use strict;
|
||||
| 347f2cff | Moritz Bunkus | use utf8;
|
||
| 18931692 | Niclas Zimmermann | |||
| 347f2cff | Moritz Bunkus | use parent qw(SL::DBUpgrade2::Base);
|
||
| 18931692 | Niclas Zimmermann | |||
| 347f2cff | Moritz Bunkus | sub run {
|
||
my ($self) = @_;
|
||||
| 18931692 | Niclas Zimmermann | |||
| 32ddb71c | Niclas Zimmermann | #CHECK CONSISTANCY OF tax
|
||
| 18931692 | Niclas Zimmermann | #update tax.rate and tax.taxdescription in order to set later NOT NULL constraints
|
||
| 32ddb71c | Niclas Zimmermann | my $query= <<SQL;
|
||
UPDATE tax SET rate=0 WHERE rate IS NULL;
|
||||
UPDATE tax SET taxdescription='-' WHERE COALESCE(taxdescription, '') = '';
|
||||
| 18931692 | Niclas Zimmermann | SQL
|
||
| 347f2cff | Moritz Bunkus | $self->db_query($query);
|
||
| 18931692 | Niclas Zimmermann | |||
#check automatic tax accounts
|
||||
$query= <<SQL;
|
||||
| 32ddb71c | Niclas Zimmermann | SELECT count(*) FROM tax WHERE chart_id NOT IN (SELECT id FROM chart);
|
||
| 18931692 | Niclas Zimmermann | SQL
|
||
| 32ddb71c | Niclas Zimmermann | |||
| 347f2cff | Moritz Bunkus | my ($invalid_tax_account) = $self->dbh->selectrow_array($query);
|
||
| 32ddb71c | Niclas Zimmermann | |||
| 18931692 | Niclas Zimmermann | if ($invalid_tax_account > 0){
|
||
#list all invalid tax accounts
|
||||
$query = <<SQL;
|
||||
| 32ddb71c | Niclas Zimmermann | SELECT id,
|
||
taxkey,
|
||||
taxdescription,
|
||||
round(rate * 100, 2) AS rate
|
||||
FROM tax WHERE chart_id NOT IN (SELECT id FROM chart);
|
||||
| 18931692 | Niclas Zimmermann | SQL
|
||
| 347f2cff | Moritz Bunkus | my $sth = $self->dbh->prepare($query);
|
||
$sth->execute || $::form->dberror($query);
|
||||
| 18931692 | Niclas Zimmermann | |||
| 347f2cff | Moritz Bunkus | $::form->{TAX} = [];
|
||
| 18931692 | Niclas Zimmermann | while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
|
||
| 347f2cff | Moritz Bunkus | push @{ $::form->{TAX} }, $ref;
|
||
| 18931692 | Niclas Zimmermann | }
|
||
$sth->finish;
|
||||
| 347f2cff | Moritz Bunkus | $::form->{invalid_tax_account} = 1;
|
||
| 18931692 | Niclas Zimmermann | print_error_message();
|
||
return 0;
|
||||
| 32ddb71c | Niclas Zimmermann | }
|
||
| 18931692 | Niclas Zimmermann | |||
#check entry tax.taxkey of NOT NULL
|
||||
$query= <<SQL;
|
||||
| 32ddb71c | Niclas Zimmermann | SELECT count(*) FROM tax WHERE taxkey IS NULL;
|
||
| 18931692 | Niclas Zimmermann | SQL
|
||
| 32ddb71c | Niclas Zimmermann | |||
| 347f2cff | Moritz Bunkus | my ($taxkey_is_null) = $self->dbh->selectrow_array($query);
|
||
| 32ddb71c | Niclas Zimmermann | |||
| 18931692 | Niclas Zimmermann | if ($taxkey_is_null > 0){
|
||
#list all invalid tax accounts
|
||||
$query = <<SQL;
|
||||
| 32ddb71c | Niclas Zimmermann | 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;
|
||||
| 18931692 | Niclas Zimmermann | SQL
|
||
| 347f2cff | Moritz Bunkus | my $sth = $self->dbh->prepare($query);
|
||
$sth->execute || $::form->dberror($query);
|
||||
| 18931692 | Niclas Zimmermann | |||
| 347f2cff | Moritz Bunkus | $::form->{TAX} = [];
|
||
| 18931692 | Niclas Zimmermann | while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
|
||
| 347f2cff | Moritz Bunkus | push @{ $::form->{TAX} }, $ref;
|
||
| 18931692 | Niclas Zimmermann | }
|
||
$sth->finish;
|
||||
| 347f2cff | Moritz Bunkus | $::form->{taxkey_is_null} = 1;
|
||
| 18931692 | Niclas Zimmermann | print_error_message();
|
||
return 0;
|
||||
| 32ddb71c | Niclas Zimmermann | }
|
||
#END CHECK OF tax
|
||||
| 18931692 | Niclas Zimmermann | |||
| 32ddb71c | Niclas Zimmermann | #CHECK CONSISTANCY OF taxkeys
|
||
| 18931692 | Niclas Zimmermann | #delete invalide entries in taxkeys
|
||
$query= <<SQL;
|
||||
| 32ddb71c | Niclas Zimmermann | DELETE FROM taxkeys
|
||
WHERE chart_id IS NULL
|
||||
OR chart_id NOT IN (SELECT id FROM chart)
|
||||
OR startdate IS NULL;
|
||||
| 18931692 | Niclas Zimmermann | SQL
|
||
| 347f2cff | Moritz Bunkus | $self->db_query($query);
|
||
| 18931692 | Niclas Zimmermann | |||
| 32ddb71c | Niclas Zimmermann | #There are 3 cases for taxkeys.tax_id and taxkeys.taxkey_id
|
||
#taxkeys.taxkey_id is NULL and taxkeys.tax_id is not NULL:
|
||||
| 18931692 | Niclas Zimmermann | |||
#Update taxkeys.taxkey_id with tax.taxkey
|
||||
$query= <<SQL;
|
||||
| 32ddb71c | Niclas Zimmermann | UPDATE taxkeys
|
||
SET taxkey_id = (SELECT t.taxkey
|
||||
FROM tax t
|
||||
| 18931692 | Niclas Zimmermann | WHERE t.id=tax_id)
|
||
| 32ddb71c | Niclas Zimmermann | WHERE taxkey_id IS NULL
|
||
AND tax_id IS NOT NULL;
|
||||
| 18931692 | Niclas Zimmermann | SQL
|
||
| 32ddb71c | Niclas Zimmermann | |||
| 347f2cff | Moritz Bunkus | $self->db_query($query);
|
||
| 18931692 | Niclas Zimmermann | |||
| 32ddb71c | Niclas Zimmermann | #taxkeys.taxkey_id and taxkeys.tax_id are NULL:
|
||
| 18931692 | Niclas Zimmermann | #Set taxkey 0 in this case:
|
||
$query= <<SQL;
|
||||
| 32ddb71c | Niclas Zimmermann | 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;
|
||||
| 18931692 | Niclas Zimmermann | SQL
|
||
| 32ddb71c | Niclas Zimmermann | |||
| 347f2cff | Moritz Bunkus | $self->db_query($query);
|
||
| 18931692 | Niclas Zimmermann | |||
| 32ddb71c | Niclas Zimmermann | #Last case where taxkeys.taxkey_id is not null and taxkeys.tax_id is null
|
||
| 18931692 | Niclas Zimmermann | |||
#If such entries exist we update with an entry in tax where tax.rate=0
|
||||
#and tax.taxkey corresponds to taxkeys.taxkey_id.
|
||||
| 32ddb71c | Niclas Zimmermann | #If no entry in tax with rate 0 and taxkey taxkeys.taxkey_id exists
|
||
| 18931692 | Niclas Zimmermann | #we create one.
|
||
$query= <<SQL;
|
||||
| 32ddb71c | Niclas Zimmermann | SELECT DISTINCT taxkey_id
|
||
FROM taxkeys
|
||||
WHERE taxkey_id IS NOT NULL
|
||||
AND tax_id IS NULL;
|
||||
| 18931692 | Niclas Zimmermann | SQL
|
||
| 347f2cff | Moritz Bunkus | my $sth = $self->dbh->prepare($query);
|
||
$sth->execute || $::form->dberror($query);
|
||||
| 18931692 | Niclas Zimmermann | |||
| 347f2cff | Moritz Bunkus | $::form->{TAXID} = [];
|
||
| 18931692 | Niclas Zimmermann | my $rowcount = 0;
|
||
while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
|
||||
| 347f2cff | Moritz Bunkus | push @{ $::form->{TAXID} }, $ref;
|
||
| 18931692 | Niclas Zimmermann | $rowcount++;
|
||
}
|
||||
$sth->finish;
|
||||
| 32ddb71c | Niclas Zimmermann | |||
| 18931692 | Niclas Zimmermann | my $insertquery;
|
||
my $updatequery;
|
||||
my $tax_id;
|
||||
for my $i (0 .. $rowcount-1){
|
||||
$query= qq|
|
||||
| 347f2cff | Moritz Bunkus | SELECT id FROM tax WHERE rate = 0 and taxkey=| . $::form->{TAXID}[$i]->{taxkey_id} . qq| LIMIT 1
|
||
| 32ddb71c | Niclas Zimmermann | |;
|
||
| 347f2cff | Moritz Bunkus | ($tax_id) = $self->dbh->selectrow_array($query);
|
||
| 18931692 | Niclas Zimmermann | if ( not $tax_id ){
|
||
$insertquery=qq|
|
||||
| 347f2cff | Moritz Bunkus | 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|);
|
||||
| 18931692 | Niclas Zimmermann | |;
|
||
| 347f2cff | Moritz Bunkus | $self->db_query($insertquery);
|
||
($tax_id) = $self->dbh->selectrow_array($query);
|
||||
$tax_id || $::form->dberror($query);
|
||||
| 18931692 | Niclas Zimmermann | }
|
||
$updatequery = qq|
|
||||
| 347f2cff | Moritz Bunkus | UPDATE taxkeys SET tax_id= | . $tax_id . qq| WHERE taxkey_id = | . $::form->{TAXID}[$i]->{taxkey_id} . qq| AND tax_id IS NULL
|
||
| 18931692 | Niclas Zimmermann | |;
|
||
| 347f2cff | Moritz Bunkus | $self->db_query($updatequery);
|
||
| 18931692 | Niclas Zimmermann | }
|
||
| 32ddb71c | Niclas Zimmermann | #The triple taxkey_id, chart_id, startdate in taxkeys has to be unique
|
||
| 18931692 | Niclas Zimmermann | #Select these entries:
|
||
$query= <<SQL;
|
||||
| 32ddb71c | Niclas Zimmermann | 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;
|
||||
| 18931692 | Niclas Zimmermann | SQL
|
||
| 347f2cff | Moritz Bunkus | $sth = $self->dbh->prepare($query);
|
||
$sth->execute || $::form->dberror($query);
|
||||
| 18931692 | Niclas Zimmermann | |||
| 347f2cff | Moritz Bunkus | $::form->{TAXKEYS} = [];
|
||
| 18931692 | Niclas Zimmermann | $rowcount = 0;
|
||
while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
|
||||
| 347f2cff | Moritz Bunkus | push @{ $::form->{TAXKEYS} }, $ref;
|
||
| 18931692 | Niclas Zimmermann | $rowcount++;
|
||
}
|
||||
$sth->finish;
|
||||
for my $i (0 .. $rowcount-1){
|
||||
| 1b960273 | Moritz Bunkus | $query= <<SQL;
|
||
| 32ddb71c | Niclas Zimmermann | DELETE FROM taxkeys tk1
|
||
| 1b960273 | Moritz Bunkus | WHERE (tk1.chart_id = ?)
|
||
AND (tk1.startdate = ?)
|
||||
AND (tk1.id <> (
|
||||
SELECT id
|
||||
FROM taxkeys
|
||||
WHERE (chart_id = ?)
|
||||
AND (startdate = ?)
|
||||
LIMIT 1))
|
||||
SQL
|
||||
| 18931692 | Niclas Zimmermann | |||
| 1b960273 | Moritz Bunkus | $self->db_query($query, bind => [ ($::form->{TAXKEYS}[$i]->{chart_id}, $::form->{TAXKEYS}[$i]->{startdate}) x 2 ]);
|
||
| 18931692 | Niclas Zimmermann | }
|
||
| 32ddb71c | Niclas Zimmermann | #END CHECK OF taxkeys
|
||
| 18931692 | Niclas Zimmermann | |||
| 32ddb71c | Niclas Zimmermann | #ADD CONSTRAINTS:
|
||
#Now the database is consistent, so we can add constraints:
|
||||
| 18931692 | Niclas Zimmermann | #Crate NOT NULL constraint for tax.rate with default value 0
|
||
$query= <<SQL;
|
||||
| 32ddb71c | Niclas Zimmermann | ALTER TABLE tax ALTER COLUMN rate SET NOT NULL;
|
||
ALTER TABLE tax ALTER COLUMN rate SET DEFAULT 0;
|
||||
| 18931692 | Niclas Zimmermann | SQL
|
||
| 347f2cff | Moritz Bunkus | $self->db_query($query);
|
||
| 18931692 | Niclas Zimmermann | |||
#Create NOT NULL constraint for tax.description
|
||||
$query= <<SQL;
|
||||
| 32ddb71c | Niclas Zimmermann | ALTER TABLE tax ALTER COLUMN taxdescription SET NOT NULL;
|
||
| 18931692 | Niclas Zimmermann | SQL
|
||
| 347f2cff | Moritz Bunkus | $self->db_query($query);
|
||
| 18931692 | Niclas Zimmermann | |||
#Create foreign key for tax.chart_id to chart.id
|
||||
$query= <<SQL;
|
||||
| 32ddb71c | Niclas Zimmermann | ALTER TABLE tax ADD FOREIGN KEY (chart_id) REFERENCES chart(id);
|
||
| 18931692 | Niclas Zimmermann | SQL
|
||
| 32ddb71c | Niclas Zimmermann | |||
| 347f2cff | Moritz Bunkus | $self->db_query($query);
|
||
| 32ddb71c | Niclas Zimmermann | |||
| 18931692 | Niclas Zimmermann | #Create NOT NULL constraint for tax.taxkey
|
||
$query= <<SQL;
|
||||
| 32ddb71c | Niclas Zimmermann | ALTER TABLE tax ALTER COLUMN taxkey SET NOT NULL;
|
||
| 18931692 | Niclas Zimmermann | SQL
|
||
| 347f2cff | Moritz Bunkus | $self->db_query($query);
|
||
| 18931692 | Niclas Zimmermann | |||
#Create NOT NULL constraint for taxkey.chart_id and foreign key for taxkey.chart_id
|
||||
$query= <<SQL;
|
||||
| 32ddb71c | Niclas Zimmermann | ALTER TABLE taxkeys ALTER COLUMN chart_id SET NOT NULL;
|
||
ALTER TABLE taxkeys ADD FOREIGN KEY (chart_id) REFERENCES chart(id);
|
||||
| 18931692 | Niclas Zimmermann | SQL
|
||
| 32ddb71c | Niclas Zimmermann | |||
| 347f2cff | Moritz Bunkus | $self->db_query($query);
|
||
| 18931692 | Niclas Zimmermann | |||
#Create NOT NULL constraint for taxkey.startdate
|
||||
$query= <<SQL;
|
||||
| 32ddb71c | Niclas Zimmermann | ALTER TABLE taxkeys ALTER COLUMN startdate SET NOT NULL;
|
||
| 18931692 | Niclas Zimmermann | SQL
|
||
| 32ddb71c | Niclas Zimmermann | |||
| 347f2cff | Moritz Bunkus | $self->db_query($query);
|
||
| 18931692 | Niclas Zimmermann | |||
#Create NOT NULL constraint for taxkey.taxkey_id
|
||||
$query= <<SQL;
|
||||
| 32ddb71c | Niclas Zimmermann | ALTER TABLE taxkeys ALTER COLUMN taxkey_id SET NOT NULL;
|
||
| 18931692 | Niclas Zimmermann | SQL
|
||
| 32ddb71c | Niclas Zimmermann | |||
| 347f2cff | Moritz Bunkus | $self->db_query($query);
|
||
| 18931692 | Niclas Zimmermann | |||
#Create NOT NULL constraint for taxkey.tax_id
|
||||
$query= <<SQL;
|
||||
| 32ddb71c | Niclas Zimmermann | ALTER TABLE taxkeys ALTER COLUMN tax_id SET NOT NULL;
|
||
| 18931692 | Niclas Zimmermann | SQL
|
||
| 32ddb71c | Niclas Zimmermann | |||
| 347f2cff | Moritz Bunkus | $self->db_query($query);
|
||
| 18931692 | Niclas Zimmermann | |||
#The triple chart_id, taxkey_id, startdate should be unique:
|
||||
$query= <<SQL;
|
||||
| 32ddb71c | Niclas Zimmermann | CREATE UNIQUE INDEX taxkeys_chartid_startdate ON taxkeys(chart_id, startdate);
|
||
| 18931692 | Niclas Zimmermann | SQL
|
||
| 32ddb71c | Niclas Zimmermann | |||
| 347f2cff | Moritz Bunkus | $self->db_query($query);
|
||
| 32ddb71c | Niclas Zimmermann | #ALL CONSTRAINTS WERE ADDED
|
||
| 18931692 | Niclas Zimmermann | |||
return 1;
|
||||
| 347f2cff | Moritz Bunkus | } # end run
|
||
| 18931692 | Niclas Zimmermann | |||
sub print_error_message {
|
||||
| 347f2cff | Moritz Bunkus | print $::form->parse_html_template("dbupgrade/tax_constraints");
|
||
| 18931692 | Niclas Zimmermann | }
|
||
| 347f2cff | Moritz Bunkus | 1;
|