1 # @tag: acc_trans_constraints
2 # @description: Fügt NOT-NULL-Constraints ein für die Spalten
9 die("This script cannot be run from the command line.") unless ($main::form);
13 die($dbup_locale->text("Database update error:") . "<br>$msg<br>" . $DBI::errstr);
17 my ($query, $may_fail) = @_;
19 if (!$dbh->do($query)) {
20 mydberror($query) unless ($may_fail);
27 my $query = qq|SELECT count(*) FROM acc_trans WHERE chart_id IS NULL|;
28 my ($no_chart_id) = $dbh->selectrow_array($query);
29 $query = qq|SELECT count(*) FROM acc_trans WHERE trans_id IS NULL|;
30 my ($no_trans_id) = $dbh->selectrow_array($query);
32 $form->{no_chart_id}=$no_chart_id;
33 $form->{no_trans_id}=$no_trans_id;
35 if ($no_chart_id > 0 or $no_trans_id > 0){
36 #list all invalid transactions where only chart_id is null:
37 $query = qq|SELECT acc.acc_trans_id,
40 ABS(acc.amount) AS amount,
49 p.description AS projectdescription
51 JOIN gl ON (gl.id=acc.trans_id)
52 LEFT JOIN employee e ON (gl.employee_id=e.id)
53 LEFT JOIN project p ON (p.id=acc.project_id)
54 WHERE acc.chart_id IS NULL
58 SELECT acc.acc_trans_id,
61 ABS(acc.amount) AS amount,
64 a.invnumber AS reference,
65 c.name AS description,
70 p.description AS projectdescription
72 JOIN ar a ON (a.id=acc.trans_id)
73 LEFT JOIN employee e ON (a.employee_id=e.id)
74 LEFT JOIN customer c ON (a.customer_id=c.id)
75 LEFT JOIN project p ON (p.id=acc.project_id)
76 WHERE acc.chart_id IS NULL
80 SELECT acc.acc_trans_id,
83 ABS(acc.amount) AS amount,
86 a.invnumber AS reference,
87 v.name AS description,
92 p.description AS projectdescription
94 JOIN ap a ON (a.id=acc.trans_id)
95 LEFT JOIN employee e ON (a.employee_id=e.id)
96 LEFT JOIN vendor v ON (a.vendor_id=v.id)
97 LEFT JOIN project p ON (p.id=acc.project_id)
98 WHERE acc.chart_id IS NULL;|;
100 my $sth = $dbh->prepare($query);
101 $sth->execute || $main::form->dberror($query);
103 $main::form->{NO_CHART_ID} = [];
104 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
105 map {$ref->{$_} = $::locale->{iconv_utf8}->convert($ref->{$_})} keys %$ref;
106 push @{ $main::form->{NO_CHART_ID} }, $ref;
110 #List all invalid transactions where trans_id is null:
111 $query = qq|SELECT acc.acc_trans_id,
114 ABS(acc.amount) AS amount,
120 p.description AS projectdescription
122 LEFT JOIN chart c ON (c.id=acc.chart_id)
123 LEFT JOIN project p ON (p.id=acc.project_id)
124 WHERE acc.trans_id IS NULL;|;
126 $sth = $dbh->prepare($query);
127 $sth->execute || $main::form->dberror($query);
129 $main::form->{NO_TRANS_ID} = [];
130 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
131 map {$ref->{$_} = $::locale->{iconv_utf8}->convert($ref->{$_})} keys %$ref;
132 $ref->{category} = ($ref->{category} eq 'A') ? $::locale->text('Account Category A')
133 : ($ref->{category} eq 'E') ? $::locale->text('Account Category E')
134 : ($ref->{category} eq 'L') ? $::locale->text('Account Category L')
135 : ($ref->{category} eq 'I') ? $::locale->text('Account Category I')
136 : ($ref->{category} eq 'Q') ? $::locale->text('Account Category Q')
137 : ($ref->{category} eq 'C') ? $::locale->text('Account Category C')
138 : ($ref->{category} eq 'G') ? $::locale->text('Account Category G')
139 : $::locale->text('Unknown Category') . ': ' . $ref->{category};
140 push @{ $main::form->{NO_TRANS_ID} }, $ref;
144 print_error_message();
148 $query = qq|ALTER TABLE acc_trans ALTER COLUMN chart_id SET NOT NULL;|;
149 $query .= qq|ALTER TABLE acc_trans ALTER COLUMN trans_id SET NOT NULL;|;
155 sub print_error_message {
156 print $main::form->parse_html_template("dbupgrade/acc_trans_constraints");