1 package SL::BackgroundJob::SelfTest::Transactions;
5 use parent qw(SL::BackgroundJob::SelfTest::Base);
9 use Rose::Object::MakeMethods::Generic (
10 scalar => [ qw(dbh fromdate todate) ],
18 $self->tester->plan(tests => 20);
20 $self->check_konten_mit_saldo_nicht_in_guv;
21 $self->check_bilanzkonten_mit_pos_eur;
22 $self->check_balanced_individual_transactions;
23 $self->check_verwaiste_acc_trans_eintraege;
24 $self->check_verwaiste_invoice_eintraege;
25 $self->check_ar_acc_trans_amount;
26 $self->check_ap_acc_trans_amount;
27 $self->check_netamount_laut_invoice_ar;
28 $self->check_invnumbers_unique;
29 $self->check_summe_stornobuchungen;
32 $self->check_ar_overpayments;
33 $self->check_ap_overpayments;
34 $self->check_paid_stornos;
35 $self->check_stornos_ohne_partner;
36 $self->check_overpayments;
37 $self->check_every_account_with_taxkey;
38 $self->calc_saldenvortraege;
39 $self->check_missing_tax_bookings;
45 # TODO FIXME calc dates better, unless this is wanted
46 $self->fromdate(DateTime->new(day => 1, month => 1, year => DateTime->today->year));
47 $self->todate($self->fromdate->clone->add(years => 1)->add(days => -1));
49 $self->dbh($::form->get_standard_dbh);
52 sub check_konten_mit_saldo_nicht_in_guv {
56 SELECT c.accno, c.description, c.category, SUM(a.amount) AS Saldo
59 WHERE c.id = a.chart_id
60 and (c.category like 'I' or c.category like 'E')
63 and a.transdate >= ? and a.transdate <= ?
64 GROUP BY c.accno,c.description,c.category,c.pos_bilanz,c.pos_eur
67 my $konten_nicht_in_guv = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
69 my $correct = 0 == scalar grep { $_->{Saldo} } @$konten_nicht_in_guv;
71 $self->tester->ok($correct, "Erfolgskonten mit Saldo nicht in GuV (Saldenvortragskonten können ignoriert werden, sollten aber 0 sein)");
73 for my $konto (@$konten_nicht_in_guv) {
74 $self->tester->diag($konto);
79 sub check_bilanzkonten_mit_pos_eur {
82 my $query = qq|SELECT accno, description FROM chart WHERE (category = 'A' OR category = 'L' OR category = 'Q') AND (pos_eur IS NOT NULL OR pos_eur != 0)|;
84 my $bilanzkonten_mit_pos_eur = selectall_hashref_query($::form, $self->dbh, $query);
85 if (@$bilanzkonten_mit_pos_eur) {
86 $self->tester->ok(0, "Es gibt Bilanzkonten die der GuV/EÜR zugeordnet sind)");
87 $self->tester->diag("$_->{accno} $_->{description}") for @$bilanzkonten_mit_pos_eur;
89 $self->tester->ok(1, "Keine Bilanzkonten in der GuV");
93 sub check_balanced_individual_transactions {
97 select sum(ac.amount) as amount,trans_id,ar.invnumber as ar,ap.invnumber as ap,gl.reference as gl
99 left join ar on (ar.id = ac.trans_id)
100 left join ap on (ap.id = ac.trans_id)
101 left join gl on (gl.id = ac.trans_id)
102 where ac.transdate >= ? AND ac.transdate <= ?
103 group by trans_id,ar.invnumber,ap.invnumber,gl.reference
104 having sum(ac.amount) != 0;|;
106 my $acs = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
108 $self->tester->ok(0, "Es gibt unausgeglichene acc_trans-Transaktionen:");
109 for my $ac (@{ $acs }) {
110 $self->tester->diag("trans_id: $ac->{trans_id}, amount = $ac->{amount}, ar: $ac->{ar} ap: $ac->{ap} gl: $ac->{gl}");
113 $self->tester->ok(1, "Alle acc_trans Transaktionen ergeben in Summe 0, keine unausgeglichenen Transaktionen");
117 sub check_verwaiste_acc_trans_eintraege {
121 select trans_id,amount,accno,description from acc_trans a
122 left join chart c on (c.id = a.chart_id)
123 where trans_id not in (select id from gl union select id from ar union select id from ap order by id)
124 and a.transdate >= ? and a.transdate <= ? ;|;
126 my $verwaiste_acs = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
127 if (@$verwaiste_acs) {
128 $self->tester->ok(0, "Es gibt verwaiste acc-trans Einträge! (wo ar/ap/gl-Eintrag fehlt)");
129 $self->tester->diag($_) for @$verwaiste_acs;
131 $self->tester->ok(1, "Keine verwaisten acc-trans Einträge (wo ar/ap/gl-Eintrag fehlt)");
135 sub check_verwaiste_invoice_eintraege {
136 # this check is always run for all invoice entries in the entire database
139 select * from invoice i
140 where trans_id not in (select id from ar union select id from ap order by id) |;
142 my $verwaiste_invoice = selectall_hashref_query($::form, $self->dbh, $query);
143 if (@$verwaiste_invoice) {
144 $self->tester->ok(0, "Es gibt verwaiste invoice Einträge! (wo ar/ap-Eintrag fehlt)");
145 for my $invoice ( @{ $verwaiste_invoice }) {
146 $self->tester->diag("invoice: id: $invoice->{id} trans_id: $invoice->{trans_id} description: $invoice->{description} itime: $invoice->{itime}");
149 $self->tester->ok(1, "Keine verwaisten invoice Einträge (wo ar/ap-Eintrag fehlt)"); }
152 sub check_netamount_laut_invoice_ar {
155 select sum(round(cast(i.qty*(i.fxsellprice * (1-i.discount)) as numeric), 2))
157 left join ar a on (a.id = i.trans_id)
158 where a.transdate >= ? and a.transdate <= ?;|;
159 my ($netamount_laut_invoice) = selectfirst_array_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
161 $query = qq| select sum(netamount) from ar where transdate >= ? and transdate <= ? AND invoice; |;
162 my ($netamount_laut_ar) = selectfirst_array_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
164 my $correct = $netamount_laut_invoice - $netamount_laut_ar == 0;
166 $self->tester->ok($correct, "Summe laut Verkaufsbericht sollte gleich Summe aus Verkauf -> Berichte -> Rechnungen sein");
168 $self->tester->diag("Netto-Summe laut Verkaufsbericht (invoice): $netamount_laut_invoice");
169 $self->tester->diag("Netto-Summe laut Verkauf -> Berichte -> Rechnungen: $netamount_laut_ar");
173 sub check_invnumbers_unique {
176 my $query = qq| select invnumber,count(invnumber) as count from ar
177 where transdate >= ? and transdate <= ?
179 having count(invnumber) > 1; |;
180 my $non_unique_invnumbers = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
182 if (@$non_unique_invnumbers) {
183 $self->tester->ok(0, "Es gibt doppelte Rechnungsnummern");
184 for my $invnumber (@{ $non_unique_invnumbers }) {
185 $self->tester->diag("invnumber: $invnumber->{invnumber} $invnumber->{count}x");
188 $self->tester->ok(1, "Alle Rechnungsnummern sind eindeutig");
192 sub check_summe_stornobuchungen {
196 select sum(amount) from ar a JOIN customer c ON (a.customer_id = c.id)
198 AND a.transdate >= ? and a.transdate <= ?|;
199 my ($summe_stornobuchungen_ar) = selectfirst_array_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
202 select sum(amount) from ap a JOIN vendor c ON (a.vendor_id = c.id)
204 AND a.transdate >= ? and a.transdate <= ?|;
205 my ($summe_stornobuchungen_ap) = selectfirst_array_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
207 $self->tester->ok($summe_stornobuchungen_ap == 0, 'Summe aller Einkaufsrechnungen (stornos + stornierte) soll 0 sein');
208 $self->tester->ok($summe_stornobuchungen_ar == 0, 'Summe aller Verkaufsrechnungen (stornos + stornierte) soll 0 sein');
209 $self->tester->diag("Summe Verkaufsrechnungen (ar): $summe_stornobuchungen_ar") if $summe_stornobuchungen_ar;
210 $self->tester->diag("Summe Einkaufsrechnungen (ap): $summe_stornobuchungen_ap") if $summe_stornobuchungen_ap;
217 select invnumber,paid,
218 (select sum(amount) from acc_trans a left join chart c on (c.id = a.chart_id) where trans_id = ar.id and c.link like '%AR_paid%') as accpaid ,
219 paid+(select sum(amount) from acc_trans a left join chart c on (c.id = a.chart_id) where trans_id = ar.id and c.link like '%AR_paid%') as diff
222 (select sum(amount) from acc_trans a left join chart c on (c.id = a.chart_id) where trans_id = ar.id and c.link like '%AR_paid%') is not null
224 AND transdate >= ? and transdate <= ?
227 my $paid_diffs_ar = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
229 my $errors = scalar grep { $_->{diff} != 0 } @$paid_diffs_ar;
231 $self->tester->ok(!$errors, "Vergleich ar paid mit acc_trans AR_paid");
233 for my $paid_diff_ar (@{ $paid_diffs_ar }) {
234 next if $paid_diff_ar->{diff} == 0;
235 $self->tester->diag("ar invnumber: $paid_diff_ar->{invnumber} : paid: $paid_diff_ar->{paid} acc_paid= $paid_diff_ar->{accpaid} diff: $paid_diff_ar->{diff}");
243 select invnumber,paid,
244 (select sum(amount) from acc_trans a left join chart c on (c.id = a.chart_id) where trans_id = ap.id and c.link like '%AP_paid%') as accpaid ,
245 paid-(select sum(amount) from acc_trans a left join chart c on (c.id = a.chart_id) where trans_id = ap.id and c.link like '%AP_paid%') as diff
248 (select sum(amount) from acc_trans a left join chart c on (c.id = a.chart_id) where trans_id = ap.id and c.link like '%AP_paid%') is not null
249 AND transdate >= ? and transdate <= ?
252 my $paid_diffs_ap = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
254 my $errors = scalar grep { $_->{diff} != 0 } @$paid_diffs_ap;
256 $self->tester->ok(!$errors, "Vergleich ap paid mit acc_trans AP_paid");
257 for my $paid_diff_ap (@{ $paid_diffs_ap }) {
258 next if $paid_diff_ap->{diff} == 0;
259 $self->tester->diag("ap invnumber: $paid_diff_ap->{invnumber} : paid: $paid_diff_ap->{paid} acc_paid= $paid_diff_ap->{accpaid} diff: $paid_diff_ap->{diff}");
263 sub check_ar_overpayments {
267 select invnumber,paid,amount,transdate,c.customernumber,c.name from ar left join customer c on (ar.customer_id = c.id)
268 where abs(paid) > abs(amount)
269 AND transdate >= ? and transdate <= ?
270 order by invnumber;|;
272 my $overpaids_ar = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
274 my $correct = 0 == @$overpaids_ar;
276 $self->tester->ok($correct, "Keine Überzahlungen laut ar.paid");
277 for my $overpaid_ar (@{ $overpaids_ar }) {
278 $self->tester->diag("ar invnumber: $overpaid_ar->{invnumber} : paid: $overpaid_ar->{paid} amount= $overpaid_ar->{amount} transdate = $overpaid_ar->{transdate}");
282 sub check_ap_overpayments {
286 select invnumber,paid,amount,transdate,vc.vendornumber,vc.name from ap left join vendor vc on (ap.vendor_id = vc.id)
287 where abs(paid) > abs(amount)
288 AND transdate >= ? and transdate <= ?
289 order by invnumber;|;
291 my $overpaids_ap = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
293 my $correct = 0 == @$overpaids_ap;
295 $self->tester->ok($correct, "Überzahlungen laut ap.paid:");
296 for my $overpaid_ap (@{ $overpaids_ap }) {
297 $self->tester->diag("ap invnumber: $overpaid_ap->{invnumber} : paid: $overpaid_ap->{paid} amount= $overpaid_ap->{amount} transdate = $overpaid_ap->{transdate}");
301 sub check_paid_stornos {
305 SELECT ar.invnumber,sum(amount - COALESCE((SELECT sum(amount)*-1 FROM acc_trans LEFT JOIN chart ON (acc_trans.chart_id=chart.id) WHERE link ilike '%paid%' AND acc_trans.trans_id=ar.id ),0)) as "open"
309 AND (ar.customer_id = customer.id)
310 AND ar.transdate >= ? and ar.transdate <= ?
311 GROUP BY ar.invnumber;|;
312 my $paid_stornos = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
314 $self->tester->ok(0 == @$paid_stornos, "Keine bezahlten Stornos");
315 for my $paid_storno (@{ $paid_stornos }) {
316 $self->tester->diag("invnumber: $paid_storno->{invnumber} offen: $paid_storno->{open}");
320 sub check_stornos_ohne_partner {
324 SELECT (SELECT cast ('ar' as text)) as invoice ,ar.id,invnumber,storno,amount,transdate,type,customernumber as cv_number
326 LEFT JOIN customer c on (c.id = ar.customer_id)
327 WHERE storno_id is null AND storno is true AND ar.id not in (SELECT storno_id FROM ar WHERE storno_id is not null AND storno is true)
329 SELECT (SELECT cast ('ap' as text)) as invoice,ap.id,invnumber,storno,amount,transdate,type,vendornumber as cv_number
331 LEFT JOIN vendor v on (v.id = ap.vendor_id)
332 WHERE storno_id is null AND storno is true AND ap.id not in (SELECT storno_id FROM ap WHERE storno_id is not null AND storno is true);
335 my $stornos_ohne_partner = selectall_hashref_query($::form, $self->dbh, $query);
337 $self->tester->ok(@$stornos_ohne_partner == 0, 'Es sollte keine Stornos ohne Partner geben');
338 if (@$stornos_ohne_partner) {
339 $self->tester->diag("Stornos ohne Partner: (kann passieren wenn Stornorechnung außerhalb Zeitraum liegt)");
340 $self->tester->diag("gilt aber trotzdem als paid zu dem Zeitpunkt, oder?");
342 my $stornoheader = 0;
343 for my $storno (@{ $stornos_ohne_partner }) {
344 if (!$stornoheader++) {
345 $self->tester->diag(join "\t", keys %$storno);
347 $self->tester->diag(join "\t", map { $storno->{$_} } keys %$storno);
351 sub check_overpayments {
354 # Vergleich ar.paid und das was laut acc_trans bezahlt wurde
355 # "als bezahlt markieren" ohne sauberes Ausbuchen führt zu Differenzen bei offenen Forderungen
356 # geht nur auf wenn acc_trans Zahlungseingänge auch im Untersuchungszeitraum lagen
357 # Stornos werden rausgefiltert
360 invnumber,customernumber,name,ar.transdate,ar.datepaid,
362 amount-paid as "open via ar",
363 paid as "paid via ar",
364 coalesce((SELECT sum(amount)*-1 FROM acc_trans LEFT JOIN chart ON (acc_trans.chart_id=chart.id) WHERE link ilike '%paid%' AND acc_trans.trans_id=ar.id AND acc_trans.transdate <= ?),0) as "paid via acc_trans"
365 FROM ar left join customer c on (c.id = ar.customer_id)
368 AND (transdate <= ? )
371 my $invoices = selectall_hashref_query($::form, $self->dbh, $query, $self->todate, $self->todate);
373 my $count_overpayments = scalar grep {
374 $_->{"paid via ar"} != $_->{"paid via acc_trans"}
375 || ( $_->{"amount"} - $_->{"paid via acc_trans"} != $_->{"open via ar"}
376 && $_->{"paid via ar"} != $_->{"paid via acc_trans"} )
379 $self->tester->ok($count_overpayments == 0, 'Vergleich ar.paid und das was laut acc_trans bezahlt wurde');
381 if ($count_overpayments) {
382 for my $invoice (@{ $invoices }) {
383 if ($invoice->{"paid via ar"} != $invoice->{"paid via acc_trans"}) {
384 $self->tester->diag("paid via ar (@{[ $invoice->{'paid via ar'} * 1 ]}) != paid via acc_trans (@{[ $invoice->{'paid via acc_trans'} * 1 ]}) (at least until transdate!)");
385 if (defined $invoice->{datepaid}) {
386 $self->tester->diag("datepaid = $invoice->{datepaid})");
388 $self->tester->diag("Überzahlung bei Rechnung: $invoice->{invnumber}") if $invoice->{"paid via acc_trans"} > $invoice->{amount};
389 } elsif ( $invoice->{"amount"} - $invoice->{"paid via acc_trans"} != $invoice->{"open via ar"} && $invoice->{"paid via ar"} != $invoice->{"paid via acc_trans"}) {
390 $self->tester->diag("amount - paid_via_acc_trans != open_via_ar");
391 $self->tester->diag("Überzahlung bei Rechnung: $invoice->{invnumber}") if $invoice->{"paid via acc_trans"} > $invoice->{amount};
399 sub calc_saldenvortraege {
402 my $saldenvortragskonto = '9000';
404 # Saldo Saldenvortragskonto 9000 am Jahresanfang
406 select sum(amount) from acc_trans where chart_id = (select id from chart where accno = ?) and transdate <= ?|;
407 my ($saldo_9000_jahresanfang) = selectfirst_array_query($::form, $self->dbh, $query, $saldenvortragskonto, DateTime->new(day => 1, month => 1, year => DateTime->today->year));
408 $self->tester->diag("Saldo 9000 am 01.01.@{[DateTime->today->year]}: @{[ $saldo_9000_jahresanfang * 1 ]} (sollte 0 sein)");
410 # Saldo Saldenvortragskonto 9000 am Jahresende
412 select sum(amount) from acc_trans where chart_id = (select id from chart where accno = ?) and transdate <= ?|;
413 my ($saldo_9000_jahresende) = selectfirst_array_query($::form, $self->dbh, $query, $saldenvortragskonto, DateTime->new(day => 31, month => 12, year => DateTime->today->year));
414 $self->tester->diag("Saldo $saldenvortragskonto am 31.12.@{[DateTime->today->year]}: @{[ $saldo_9000_jahresende * 1 ]} (sollte 0 sein)");
417 sub check_every_account_with_taxkey {
420 my $query = qq|SELECT accno, description FROM chart WHERE id NOT IN (select chart_id from taxkeys)|;
421 my $accounts_without_tk = selectall_hashref_query($::form, $self->dbh, $query);
423 if ( scalar @{ $accounts_without_tk } > 0 ){
424 $self->tester->ok(0, "Folgende Konten haben keinen gültigen Steuerschlüssel:");
426 for my $account_without_tk (@{ $accounts_without_tk } ) {
427 $self->tester->diag("Kontonummer: $account_without_tk->{accno} Beschreibung: $account_without_tk->{description}");
430 $self->tester->ok(1, "Jedes Konto hat einen gültigen Steuerschlüssel!");
434 sub check_ar_acc_trans_amount {
438 select ar.invnumber, ar.netamount, ac.amount
439 from ar left join acc_trans ac on (ac.trans_id = ar.id) where ac.chart_link like 'AR_amount%' AND ac.amount <> ar.netamount|;
441 my $ar_amount_not_ac_amount = selectall_hashref_query($::form, $self->dbh, $query);
443 if ( scalar @{ $ar_amount_not_ac_amount } > 0 ) {
444 $self->tester->ok(0, "Folgende Ausgangsrechnungen haben einen falschen Netto-Wert im Nebenbuch:");
446 for my $ar_ac_amount_nok (@{ $ar_amount_not_ac_amount } ) {
447 $self->tester->diag("Rechnungsnummer: $ar_ac_amount_nok->{invnumber} Hauptbuch-Wert: $ar_ac_amount_nok->{amount}
448 Nebenbuch-Wert: $ar_ac_amount_nok->{netamount}");
451 $self->tester->ok(1, "Hauptbuch-Nettowert und Debitoren-Nebenbuch-Nettowert stimmen überein.");
456 sub check_ap_acc_trans_amount {
460 select ap.invnumber, ap.netamount, ac.amount
461 from ap left join acc_trans ac on (ac.trans_id = ap.id) where ac.chart_link like 'AP_amount%' AND ac.amount <> ap.netamount*-1|;
463 my $ap_amount_not_ac_amount = selectall_hashref_query($::form, $self->dbh, $query);
465 if ( scalar @{ $ap_amount_not_ac_amount } > 0 ) {
466 $self->tester->ok(0, "Folgende Eingangsrechnungen haben einen falschen Netto-Wert im Nebenbuch:");
468 for my $ap_ac_amount_nok (@{ $ap_amount_not_ac_amount } ) {
469 $self->tester->diag("Rechnungsnummer: $ap_ac_amount_nok->{invnumber} Hauptbuch-Wert: $ap_ac_amount_nok->{amount}
470 Nebenbuch-Wert: $ap_ac_amount_nok->{netamount}");
473 $self->tester->ok(1, "Hauptbuch-Nettowert und Kreditoren-Nebenbuch-Nettowert stimmen überein.");
479 sub check_missing_tax_bookings {
483 # check tax bookings. all taxkey <> 0 should have tax bookings in acc_trans
485 my $query = qq| select trans_id, chart.accno,transdate from acc_trans left join chart on (chart.id = acc_trans.chart_id)
486 WHERE taxkey <> 0 AND trans_id NOT IN
487 (select trans_id from acc_trans where chart_link ilike '%tax%' and trans_id IN
488 (SELECT trans_id from acc_trans where taxkey <> 0))|;
490 my $missing_tax_bookings = selectall_hashref_query($::form, $self->dbh, $query);
492 if ( scalar @{ $missing_tax_bookings } > 0 ) {
493 $self->tester->ok(0, "Folgende Konten weisen Buchungen ohne Steuer auf:");
495 for my $acc_trans_nok (@{ $missing_tax_bookings } ) {
496 $self->tester->diag("Kontonummer: $acc_trans_nok->{accno} Belegdatum: $acc_trans_nok->{transdate} Trans-ID: $acc_trans_nok->{trans_id}.
497 Kann über System -> Korrekturen im Hauptbuch bereinigt werden.");
500 $self->tester->ok(1, "Hauptbuch-Nettowert und Nebenbuch-Nettowert stimmen überein.");
513 SL::BackgroundJob::SelfTest::Transactions - base tests
517 Several tests for data integrity.
525 G. Richardson E<lt>information@richardson-bueren.deE<gt>
526 Jan Büren E<lt>information@richardson-bueren.deE<gt>
527 Sven Schoeling E<lt>s.schoeling@linet-services.deE<gt>