1 # @tag: acc_trans_constraints
2 # @description: Fügt NOT-NULL-Constraints ein für die Spalten
4 package SL::DBUpgrade2::acc_trans_constraints;
9 use parent qw(SL::DBUpgrade2::Base);
14 my $query = qq|SELECT count(*) FROM acc_trans WHERE chart_id IS NULL|;
15 my ($no_chart_id) = $self->dbh->selectrow_array($query);
16 $query = qq|SELECT count(*) FROM acc_trans WHERE trans_id IS NULL|;
17 my ($no_trans_id) = $self->dbh->selectrow_array($query);
19 $::form->{no_chart_id}=$no_chart_id;
20 $::form->{no_trans_id}=$no_trans_id;
22 if ($no_chart_id > 0 or $no_trans_id > 0){
23 #list all invalid transactions where only chart_id is null:
24 $query = qq|SELECT acc.acc_trans_id,
27 ABS(acc.amount) AS amount,
36 p.description AS projectdescription
38 JOIN gl ON (gl.id=acc.trans_id)
39 LEFT JOIN employee e ON (gl.employee_id=e.id)
40 LEFT JOIN project p ON (p.id=acc.project_id)
41 WHERE acc.chart_id IS NULL
45 SELECT acc.acc_trans_id,
48 ABS(acc.amount) AS amount,
51 a.invnumber AS reference,
52 c.name AS description,
57 p.description AS projectdescription
59 JOIN ar a ON (a.id=acc.trans_id)
60 LEFT JOIN employee e ON (a.employee_id=e.id)
61 LEFT JOIN customer c ON (a.customer_id=c.id)
62 LEFT JOIN project p ON (p.id=acc.project_id)
63 WHERE acc.chart_id IS NULL
67 SELECT acc.acc_trans_id,
70 ABS(acc.amount) AS amount,
73 a.invnumber AS reference,
74 v.name AS description,
79 p.description AS projectdescription
81 JOIN ap a ON (a.id=acc.trans_id)
82 LEFT JOIN employee e ON (a.employee_id=e.id)
83 LEFT JOIN vendor v ON (a.vendor_id=v.id)
84 LEFT JOIN project p ON (p.id=acc.project_id)
85 WHERE acc.chart_id IS NULL;|;
87 my $sth = $self->dbh->prepare($query);
88 $sth->execute || $::form->dberror($query);
90 $::form->{NO_CHART_ID} = [];
91 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
92 push @{ $::form->{NO_CHART_ID} }, $ref;
96 #List all invalid transactions where trans_id is null:
97 $query = qq|SELECT acc.acc_trans_id,
100 ABS(acc.amount) AS amount,
106 p.description AS projectdescription
108 LEFT JOIN chart c ON (c.id=acc.chart_id)
109 LEFT JOIN project p ON (p.id=acc.project_id)
110 WHERE acc.trans_id IS NULL;|;
112 $sth = $self->dbh->prepare($query);
113 $sth->execute || $::form->dberror($query);
115 $::form->{NO_TRANS_ID} = [];
116 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
117 $ref->{category} = ($ref->{category} eq 'A') ? $::locale->text('Account Category A')
118 : ($ref->{category} eq 'E') ? $::locale->text('Account Category E')
119 : ($ref->{category} eq 'L') ? $::locale->text('Account Category L')
120 : ($ref->{category} eq 'I') ? $::locale->text('Account Category I')
121 : ($ref->{category} eq 'Q') ? $::locale->text('Account Category Q')
122 : ($ref->{category} eq 'C') ? $::locale->text('Account Category C')
123 : ($ref->{category} eq 'G') ? $::locale->text('Account Category G')
124 : $::locale->text('Unknown Category') . ': ' . $ref->{category};
125 push @{ $::form->{NO_TRANS_ID} }, $ref;
129 print_error_message();
133 $query = qq|ALTER TABLE acc_trans ALTER COLUMN chart_id SET NOT NULL;|;
134 $query .= qq|ALTER TABLE acc_trans ALTER COLUMN trans_id SET NOT NULL;|;
136 $self->db_query($query);
140 sub print_error_message {
141 print $::form->parse_html_template("dbupgrade/acc_trans_constraints");