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
10 die("This script cannot be run from the command line.") unless ($main::form);
14 die($dbup_locale->text("Database update error:") . "<br>$msg<br>" . $DBI::errstr);
18 my ($query, $may_fail) = @_;
20 if (!$dbh->do($query)) {
21 mydberror($query) unless ($may_fail);
28 #CHECK CONSISTANCY OF tax
29 #update tax.rate and tax.taxdescription in order to set later NOT NULL constraints
31 UPDATE tax SET rate=0 WHERE rate IS NULL;
32 UPDATE tax SET taxdescription='-' WHERE COALESCE(taxdescription, '') = '';
37 #check automatic tax accounts
39 SELECT count(*) FROM tax WHERE chart_id NOT IN (SELECT id FROM chart);
42 my ($invalid_tax_account) = $dbh->selectrow_array($query);
44 if ($invalid_tax_account > 0){
45 #list all invalid tax accounts
50 round(rate * 100, 2) AS rate
51 FROM tax WHERE chart_id NOT IN (SELECT id FROM chart);
54 my $sth = $dbh->prepare($query);
55 $sth->execute || $main::form->dberror($query);
57 $main::form->{TAX} = [];
58 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
59 push @{ $main::form->{TAX} }, $ref;
63 $main::form->{invalid_tax_account} = 1;
64 print_error_message();
68 #check entry tax.taxkey of NOT NULL
70 SELECT count(*) FROM tax WHERE taxkey IS NULL;
73 my ($taxkey_is_null) = $dbh->selectrow_array($query);
75 if ($taxkey_is_null > 0){
76 #list all invalid tax accounts
80 round(rate * 100, 2) AS rate,
81 (SELECT accno FROM chart WHERE id = chart_id) AS taxnumber,
82 (SELECT description FROM chart WHERE id = chart_id) AS account_description
87 my $sth = $dbh->prepare($query);
88 $sth->execute || $main::form->dberror($query);
90 $main::form->{TAX} = [];
91 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
92 push @{ $main::form->{TAX} }, $ref;
96 $main::form->{taxkey_is_null} = 1;
97 print_error_message();
102 #CHECK CONSISTANCY OF taxkeys
103 #delete invalide entries in taxkeys
106 WHERE chart_id IS NULL
107 OR chart_id NOT IN (SELECT id FROM chart)
108 OR startdate IS NULL;
113 #There are 3 cases for taxkeys.tax_id and taxkeys.taxkey_id
114 #taxkeys.taxkey_id is NULL and taxkeys.tax_id is not NULL:
116 #Update taxkeys.taxkey_id with tax.taxkey
119 SET taxkey_id = (SELECT t.taxkey
122 WHERE taxkey_id IS NULL
123 AND tax_id IS NOT NULL;
128 #taxkeys.taxkey_id and taxkeys.tax_id are NULL:
130 #Set taxkey 0 in this case:
133 SET taxkey_id = 0, tax_id = (SELECT id FROM tax WHERE taxkey=0)
134 WHERE taxkey_id IS NULL
140 #Last case where taxkeys.taxkey_id is not null and taxkeys.tax_id is null
142 #If such entries exist we update with an entry in tax where tax.rate=0
143 #and tax.taxkey corresponds to taxkeys.taxkey_id.
144 #If no entry in tax with rate 0 and taxkey taxkeys.taxkey_id exists
147 SELECT DISTINCT taxkey_id
149 WHERE taxkey_id IS NOT NULL
153 my $sth = $dbh->prepare($query);
154 $sth->execute || $main::form->dberror($query);
156 $main::form->{TAXID} = [];
158 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
159 push @{ $main::form->{TAXID} }, $ref;
167 for my $i (0 .. $rowcount-1){
169 SELECT id FROM tax WHERE rate = 0 and taxkey=| . $main::form->{TAXID}[$i]->{taxkey_id} . qq| LIMIT 1
171 ($tax_id) = $dbh->selectrow_array($query);
174 INSERT INTO tax (rate, taxdescription, taxkey) VALUES (0, '| . $::locale->text('0% tax with taxkey') . $main::form->{TAXID}[$i]->{taxkey_id} . $::locale->text('. Automatically generated.') .
175 qq|', | . $main::form->{TAXID}[$i]->{taxkey_id} . qq|);
177 do_query($insertquery);
178 ($tax_id) = $dbh->selectrow_array($query);
179 $tax_id || $main::form->dberror($query);
182 UPDATE taxkeys SET tax_id= | . $tax_id . qq| WHERE taxkey_id = | . $main::form->{TAXID}[$i]->{taxkey_id} . qq| AND tax_id IS NULL
184 do_query($updatequery);
187 #The triple taxkey_id, chart_id, startdate in taxkeys has to be unique
188 #Select these entries:
190 SELECT DISTINCT tk1.chart_id AS chart_id, tk1.startdate AS startdate
192 WHERE (SELECT count(*)
194 WHERE tk2.chart_id = tk1.chart_id
195 AND tk2.startdate = tk1.startdate) > 1;
198 $sth = $dbh->prepare($query);
199 $sth->execute || $main::form->dberror($query);
201 $main::form->{TAXKEYS} = [];
203 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
204 push @{ $main::form->{TAXKEYS} }, $ref;
209 for my $i (0 .. $rowcount-1){
211 DELETE FROM taxkeys tk1
212 WHERE (SELECT count(*)
214 WHERE tk2.chart_id = tk1.chart_id
215 AND tk2.startdate = tk1.startdate) > 1
216 AND NOT tk1.id = (SELECT id
218 WHERE chart_id = | . $main::form->{TAXKEYS}[$i]->{chart_id} . qq|
219 AND startdate = '| . $main::form->{TAXKEYS}[$i]->{startdate} . qq|'
226 #END CHECK OF taxkeys
229 #Now the database is consistent, so we can add constraints:
230 #Crate NOT NULL constraint for tax.rate with default value 0
232 ALTER TABLE tax ALTER COLUMN rate SET NOT NULL;
233 ALTER TABLE tax ALTER COLUMN rate SET DEFAULT 0;
238 #Create NOT NULL constraint for tax.description
240 ALTER TABLE tax ALTER COLUMN taxdescription SET NOT NULL;
245 #Create foreign key for tax.chart_id to chart.id
247 ALTER TABLE tax ADD FOREIGN KEY (chart_id) REFERENCES chart(id);
252 #Create NOT NULL constraint for tax.taxkey
254 ALTER TABLE tax ALTER COLUMN taxkey SET NOT NULL;
259 #Create NOT NULL constraint for taxkey.chart_id and foreign key for taxkey.chart_id
261 ALTER TABLE taxkeys ALTER COLUMN chart_id SET NOT NULL;
262 ALTER TABLE taxkeys ADD FOREIGN KEY (chart_id) REFERENCES chart(id);
267 #Create NOT NULL constraint for taxkey.startdate
269 ALTER TABLE taxkeys ALTER COLUMN startdate SET NOT NULL;
274 #Create NOT NULL constraint for taxkey.taxkey_id
276 ALTER TABLE taxkeys ALTER COLUMN taxkey_id SET NOT NULL;
281 #Create NOT NULL constraint for taxkey.tax_id
283 ALTER TABLE taxkeys ALTER COLUMN tax_id SET NOT NULL;
288 #The triple chart_id, taxkey_id, startdate should be unique:
290 CREATE UNIQUE INDEX taxkeys_chartid_startdate ON taxkeys(chart_id, startdate);
294 #ALL CONSTRAINTS WERE ADDED
300 sub print_error_message {
301 print $main::form->parse_html_template("dbupgrade/tax_constraints");