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 => 29);
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;
40 $self->check_bank_transactions_overpayments;
41 $self->check_ar_paid_acc_trans;
42 $self->check_ap_paid_acc_trans;
43 $self->check_zero_amount_paid_but_datepaid_exists;
44 $self->check_orphaned_reconciliated_links;
45 $self->check_recommended_client_settings;
46 $self->check_orphaned_bank_transaction_acc_trans_links;
52 # TODO FIXME calc dates better, unless this is wanted
53 $self->fromdate(DateTime->new(day => 1, month => 1, year => DateTime->today->year));
54 $self->todate($self->fromdate->clone->add(years => 1)->add(days => -1));
55 $self->dbh($::form->get_standard_dbh);
58 sub check_konten_mit_saldo_nicht_in_guv {
62 SELECT c.accno, c.description, c.category, SUM(a.amount) AS Saldo
65 WHERE c.id = a.chart_id
66 and (c.category like 'I' or c.category like 'E')
69 and a.transdate >= ? and a.transdate <= ?
70 GROUP BY c.accno,c.description,c.category,c.pos_bilanz,c.pos_eur
73 my $konten_nicht_in_guv = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
75 my $correct = 0 == scalar grep { $_->{Saldo} } @$konten_nicht_in_guv;
77 $self->tester->ok($correct, "Erfolgskonten mit Saldo nicht in GuV (Saldenvortragskonten können ignoriert werden, sollten aber 0 sein)");
79 for my $konto (@$konten_nicht_in_guv) {
80 $self->tester->diag($konto);
85 sub check_bilanzkonten_mit_pos_eur {
88 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)|;
90 my $bilanzkonten_mit_pos_eur = selectall_hashref_query($::form, $self->dbh, $query);
91 if (@$bilanzkonten_mit_pos_eur) {
92 $self->tester->ok(0, "Es gibt Bilanzkonten die der GuV/EÜR zugeordnet sind)");
93 $self->tester->diag("$_->{accno} $_->{description}") for @$bilanzkonten_mit_pos_eur;
95 $self->tester->ok(1, "Keine Bilanzkonten in der GuV");
99 sub check_balanced_individual_transactions {
103 select sum(ac.amount) as amount,trans_id,ar.invnumber as ar,ap.invnumber as ap,gl.reference as gl
105 left join ar on (ar.id = ac.trans_id)
106 left join ap on (ap.id = ac.trans_id)
107 left join gl on (gl.id = ac.trans_id)
108 where ac.transdate >= ? AND ac.transdate <= ?
109 group by trans_id,ar.invnumber,ap.invnumber,gl.reference
110 having sum(ac.amount) != 0;|;
112 my $acs = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
114 $self->tester->ok(0, "Es gibt unausgeglichene acc_trans-Transaktionen:");
115 for my $ac (@{ $acs }) {
116 $self->tester->diag("trans_id: $ac->{trans_id}, amount = $ac->{amount}, ar: $ac->{ar} ap: $ac->{ap} gl: $ac->{gl}");
119 $self->tester->ok(1, "Alle acc_trans Transaktionen ergeben in Summe 0, keine unausgeglichenen Transaktionen");
123 sub check_verwaiste_acc_trans_eintraege {
127 select trans_id,amount,accno,description from acc_trans a
128 left join chart c on (c.id = a.chart_id)
129 where trans_id not in (select id from gl union select id from ar union select id from ap order by id)
130 and a.transdate >= ? and a.transdate <= ? ;|;
132 my $verwaiste_acs = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
133 if (@$verwaiste_acs) {
134 $self->tester->ok(0, "Es gibt verwaiste acc-trans Einträge! (wo ar/ap/gl-Eintrag fehlt)");
135 $self->tester->diag($_) for @$verwaiste_acs;
137 $self->tester->ok(1, "Keine verwaisten acc-trans Einträge (wo ar/ap/gl-Eintrag fehlt)");
141 sub check_verwaiste_invoice_eintraege {
142 # this check is always run for all invoice entries in the entire database
145 select * from invoice i
146 where trans_id not in (select id from ar WHERE ar.transdate >=? AND ar.transdate <=?
148 select id from ap WHERE ap.transdate >= ? and ap.transdate <= ?)
149 AND i.transdate >=? AND i.transdate <=?|;
151 my $verwaiste_invoice = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate,
152 $self->fromdate, $self->todate, $self->fromdate, $self->todate);
155 if (@$verwaiste_invoice) {
156 $self->tester->ok(0, "Es gibt verwaiste invoice Einträge! (wo ar/ap-Eintrag fehlt)");
157 for my $invoice ( @{ $verwaiste_invoice }) {
158 $self->tester->diag("invoice: id: $invoice->{id} trans_id: $invoice->{trans_id} description: $invoice->{description} itime: $invoice->{itime}");
161 $self->tester->ok(1, "Keine verwaisten invoice Einträge (wo ar/ap-Eintrag fehlt)"); }
164 sub check_netamount_laut_invoice_ar {
167 select sum(round(cast(i.qty*(i.fxsellprice * (1-i.discount)) as numeric), 2))
169 left join ar a on (a.id = i.trans_id)
170 where a.transdate >= ? and a.transdate <= ?;|;
171 my ($netamount_laut_invoice) = selectfirst_array_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
173 $query = qq| select sum(netamount) from ar where transdate >= ? and transdate <= ? AND invoice; |;
174 my ($netamount_laut_ar) = selectfirst_array_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
176 # should be enough to get a diff below 1. We have currently the following issues:
177 # verkaufsbericht berücksichtigt keinen rabatt
178 # fxsellprice ist mit mwst-inklusive
179 my $correct = abs($netamount_laut_invoice - $netamount_laut_ar) < 1;
181 $self->tester->ok($correct, "Summe laut Verkaufsbericht sollte gleich Summe aus Verkauf -> Berichte -> Rechnungen sein");
183 $self->tester->diag("Netto-Summe laut Verkaufsbericht (invoice): $netamount_laut_invoice");
184 $self->tester->diag("Netto-Summe laut Verkauf -> Berichte -> Rechnungen: $netamount_laut_ar");
188 sub check_invnumbers_unique {
191 my $query = qq| select invnumber,count(invnumber) as count from ar
192 where transdate >= ? and transdate <= ?
194 having count(invnumber) > 1; |;
195 my $non_unique_invnumbers = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
197 if (@$non_unique_invnumbers) {
198 $self->tester->ok(0, "Es gibt doppelte Rechnungsnummern");
199 for my $invnumber (@{ $non_unique_invnumbers }) {
200 $self->tester->diag("invnumber: $invnumber->{invnumber} $invnumber->{count}x");
203 $self->tester->ok(1, "Alle Rechnungsnummern sind eindeutig");
207 sub check_summe_stornobuchungen {
211 SELECT sum(amount) from ar a WHERE a.id IN
212 (SELECT id from ap where storno is true
213 AND a.transdate >= ? and a.transdate <= ?)|;
214 my ($summe_stornobuchungen_ar) = selectfirst_array_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
217 SELECT sum(amount) from ap a WHERE a.id IN
218 (SELECT id from ap where storno is true
219 AND a.transdate >= ? and a.transdate <= ?)|;
220 my ($summe_stornobuchungen_ap) = selectfirst_array_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
222 $self->tester->ok($summe_stornobuchungen_ap == 0, 'Summe aller Einkaufsrechnungen (stornos + stornierte) soll 0 sein');
223 $self->tester->ok($summe_stornobuchungen_ar == 0, 'Summe aller Verkaufsrechnungen (stornos + stornierte) soll 0 sein');
224 $self->tester->diag("Summe Verkaufsrechnungen (ar): $summe_stornobuchungen_ar") if $summe_stornobuchungen_ar;
225 $self->tester->diag("Summe Einkaufsrechnungen (ap): $summe_stornobuchungen_ap") if $summe_stornobuchungen_ap;
232 select invnumber,paid,
233 (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 ,
234 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
237 (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
239 AND ar.id in (SELECT id from ar where transdate >= ? and transdate <= ?)
242 my $paid_diffs_ar = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
244 my $errors = scalar grep { $_->{diff} != 0 } @$paid_diffs_ar;
246 $self->tester->ok(!$errors, "Vergleich ar paid mit acc_trans AR_paid");
248 for my $paid_diff_ar (@{ $paid_diffs_ar }) {
249 next if $paid_diff_ar->{diff} == 0;
250 $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}");
258 select invnumber,paid,id,
259 (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 ,
260 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
263 (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
264 AND ap.id in (SELECT id from ap where transdate >= ? and transdate <= ?)
267 my $paid_diffs_ap = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
269 my $errors = scalar grep { $_->{diff} != 0 } @$paid_diffs_ap;
271 $self->tester->ok(!$errors, "Vergleich ap paid mit acc_trans AP_paid");
272 for my $paid_diff_ap (@{ $paid_diffs_ap }) {
273 next if $paid_diff_ap->{diff} == 0;
274 $self->tester->diag("ap invnumber: $paid_diff_ap->{invnumber} : ID :: ID : $paid_diff_ap->{id} : paid: $paid_diff_ap->{paid} acc_paid= $paid_diff_ap->{accpaid} diff: $paid_diff_ap->{diff}");
278 sub check_ar_overpayments {
282 select invnumber,paid,amount,transdate,c.customernumber,c.name from ar left join customer c on (ar.customer_id = c.id)
283 where abs(paid) > abs(amount)
284 AND transdate >= ? and transdate <= ?
285 order by invnumber;|;
287 my $overpaids_ar = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
289 my $correct = 0 == @$overpaids_ar;
291 $self->tester->ok($correct, "Keine Überzahlungen laut ar.paid");
292 for my $overpaid_ar (@{ $overpaids_ar }) {
293 $self->tester->diag("ar invnumber: $overpaid_ar->{invnumber} : paid: $overpaid_ar->{paid} amount= $overpaid_ar->{amount} transdate = $overpaid_ar->{transdate}");
297 sub check_ap_overpayments {
301 select invnumber,paid,amount,transdate,vc.vendornumber,vc.name from ap left join vendor vc on (ap.vendor_id = vc.id)
302 where abs(paid) > abs(amount)
303 AND transdate >= ? and transdate <= ?
304 order by invnumber;|;
306 my $overpaids_ap = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
308 my $correct = 0 == @$overpaids_ap;
310 $self->tester->ok($correct, "Überzahlungen laut ap.paid:");
311 for my $overpaid_ap (@{ $overpaids_ap }) {
312 $self->tester->diag("ap invnumber: $overpaid_ap->{invnumber} : paid: $overpaid_ap->{paid} amount= $overpaid_ap->{amount} transdate = $overpaid_ap->{transdate}");
316 sub check_paid_stornos {
320 SELECT ar.invnumber,sum(amount - COALESCE((SELECT sum(amount)*-1
321 FROM acc_trans LEFT JOIN chart ON (acc_trans.chart_id=chart.id)
322 WHERE link ilike '%paid%' AND acc_trans.trans_id=ar.id ),0)) as "open"
326 AND (ar.customer_id = customer.id)
327 AND ar.transdate >= ? and ar.transdate <= ?
328 GROUP BY ar.invnumber|;
329 my $paid_stornos = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
331 $self->tester->ok(0 == @$paid_stornos, "Keine bezahlten Stornos");
332 for my $paid_storno (@{ $paid_stornos }) {
333 $self->tester->diag("invnumber: $paid_storno->{invnumber} offen: $paid_storno->{open}");
337 sub check_stornos_ohne_partner {
341 SELECT (SELECT cast ('ar' as text)) as invoice ,ar.id,invnumber,storno,amount,transdate,type,customernumber as cv_number
343 LEFT JOIN customer c on (c.id = ar.customer_id)
344 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)
345 AND ar.transdate >= ? and ar.transdate <= ?
347 SELECT (SELECT cast ('ap' as text)) as invoice,ap.id,invnumber,storno,amount,transdate,type,vendornumber as cv_number
349 LEFT JOIN vendor v on (v.id = ap.vendor_id)
350 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)
351 AND ap.transdate >= ? and ap.transdate <= ?|;
353 my $stornos_ohne_partner = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate,
354 $self->fromdate, $self->todate);
356 $self->tester->ok(@$stornos_ohne_partner == 0, 'Es sollte keine Stornos ohne Partner geben');
357 if (@$stornos_ohne_partner) {
358 $self->tester->diag("Stornos ohne Partner, oder Storno über Jahreswechsel hinaus");
360 my $stornoheader = 0;
361 for my $storno (@{ $stornos_ohne_partner }) {
362 if (!$stornoheader++) {
363 $self->tester->diag(join "\t", keys %$storno);
365 $self->tester->diag(join "\t", map { $storno->{$_} } keys %$storno);
369 sub check_overpayments {
372 # Vergleich ar.paid und das was laut acc_trans bezahlt wurde
373 # "als bezahlt markieren" ohne sauberes Ausbuchen führt zu Differenzen bei offenen Forderungen
374 # Berücksichtigt Zahlungseingänge im Untersuchungszeitraums und
375 # prüft weitere Zahlungen und Buchungen über trans_id (kein Zeitfilter)
379 invnumber,customernumber,name,ar.transdate,ar.datepaid,
381 amount-paid as "open via ar",
382 paid as "paid via ar",
383 coalesce((SELECT sum(amount)*-1 FROM acc_trans
384 WHERE chart_link ilike '%paid%' AND acc_trans.trans_id=ar.id),0) as "paid via acc_trans"
385 FROM ar left join customer c on (c.id = ar.customer_id)
388 AND ar.id in (SELECT trans_id from acc_trans where transdate >= ? AND transdate <= ? AND chart_link ilike '%paid%')|;
390 my $invoices = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
392 my $count_overpayments = scalar grep {
393 $_->{"paid via ar"} != $_->{"paid via acc_trans"}
394 || ( $_->{"amount"} - $_->{"paid via acc_trans"} != $_->{"open via ar"}
395 && $_->{"paid via ar"} != $_->{"paid via acc_trans"} )
398 $self->tester->ok($count_overpayments == 0, 'Vergleich ar.paid und das was laut acc_trans bezahlt wurde');
400 if ($count_overpayments) {
401 for my $invoice (@{ $invoices }) {
402 if ($invoice->{"paid via ar"} != $invoice->{"paid via acc_trans"}) {
403 $self->tester->diag("Rechnung: $invoice->{invnumber}, Kunde $invoice->{name} Nebenbuch-Bezahlwert: (@{[ $invoice->{'paid via ar'} * 1 ]}) != Hauptbuch-Bezahlwert: (@{[ $invoice->{'paid via acc_trans'} * 1 ]}) (at least until transdate!)");
404 if (defined $invoice->{datepaid}) {
405 $self->tester->diag("datepaid = $invoice->{datepaid})");
407 $self->tester->diag("Überzahlung bei Rechnung: $invoice->{invnumber}") if $invoice->{"paid via acc_trans"} > $invoice->{amount};
408 } elsif ( $invoice->{"amount"} - $invoice->{"paid via acc_trans"} != $invoice->{"open via ar"} && $invoice->{"paid via ar"} != $invoice->{"paid via acc_trans"}) {
409 $self->tester->diag("amount - paid_via_acc_trans != open_via_ar");
410 $self->tester->diag("Überzahlung bei Rechnung: $invoice->{invnumber}") if $invoice->{"paid via acc_trans"} > $invoice->{amount};
418 sub calc_saldenvortraege {
421 my $saldenvortragskonto = '9000';
423 # Saldo Saldenvortragskonto 9000 am Jahresanfang
425 select sum(amount) from acc_trans where chart_id = (select id from chart where accno = ?) and transdate <= ?|;
426 my ($saldo_9000_jahresanfang) = selectfirst_array_query($::form, $self->dbh, $query, $saldenvortragskonto, DateTime->new(day => 1, month => 1, year => DateTime->today->year));
427 $self->tester->diag("Saldo 9000 am 01.01.@{[DateTime->today->year]}: @{[ $saldo_9000_jahresanfang * 1 ]} (sollte 0 sein)");
429 # Saldo Saldenvortragskonto 9000 am Jahresende
431 select sum(amount) from acc_trans where chart_id = (select id from chart where accno = ?) and transdate <= ?|;
432 my ($saldo_9000_jahresende) = selectfirst_array_query($::form, $self->dbh, $query, $saldenvortragskonto, DateTime->new(day => 31, month => 12, year => DateTime->today->year));
433 $self->tester->diag("Saldo $saldenvortragskonto am 31.12.@{[DateTime->today->year]}: @{[ $saldo_9000_jahresende * 1 ]} (sollte 0 sein)");
436 sub check_every_account_with_taxkey {
439 my $query = qq|SELECT accno, description FROM chart WHERE id NOT IN (select chart_id from taxkeys)|;
440 my $accounts_without_tk = selectall_hashref_query($::form, $self->dbh, $query);
442 if ( scalar @{ $accounts_without_tk } > 0 ){
443 $self->tester->ok(0, "Folgende Konten haben keinen gültigen Steuerschlüssel:");
445 for my $account_without_tk (@{ $accounts_without_tk } ) {
446 $self->tester->diag("Kontonummer: $account_without_tk->{accno} Beschreibung: $account_without_tk->{description}");
449 $self->tester->ok(1, "Jedes Konto hat einen gültigen Steuerschlüssel!");
453 sub check_ar_acc_trans_amount {
457 select sum(ac.amount) as amount, ar.invnumber,ar.netamount
458 from acc_trans ac left join ar on (ac.trans_id = ar.id)
459 WHERE ac.chart_link like 'AR_amount%'
460 AND ac.transdate >= ? AND ac.transdate <= ?
461 group by invnumber,netamount having sum(ac.amount) <> ar.netamount|;
463 my $ar_amount_not_ac_amount = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
465 if ( scalar @{ $ar_amount_not_ac_amount } > 0 ) {
466 $self->tester->ok(0, "Folgende Ausgangsrechnungen haben einen falschen Netto-Wert im Nebenbuch:");
468 for my $ar_ac_amount_nok (@{ $ar_amount_not_ac_amount } ) {
469 $self->tester->diag("Rechnungsnummer: $ar_ac_amount_nok->{invnumber} Hauptbuch-Wert: $ar_ac_amount_nok->{amount}
470 Nebenbuch-Wert: $ar_ac_amount_nok->{netamount}");
473 $self->tester->ok(1, "Hauptbuch-Nettowert und Debitoren-Nebenbuch-Nettowert stimmen überein.");
478 sub check_ap_acc_trans_amount {
482 select sum(ac.amount) as amount, ap.invnumber,ap.netamount
483 from acc_trans ac left join ap on (ac.trans_id = ap.id)
484 WHERE (ac.chart_link like '%AP_amount%' OR ac.chart_link like '%IC_cogs%')
485 AND ac.transdate >= ? AND ac.transdate <= ?
486 group by invnumber,trans_id,netamount having sum(ac.amount) <> ap.netamount*-1|;
488 my $ap_amount_not_ac_amount = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
490 if ( scalar @{ $ap_amount_not_ac_amount } > 0 ) {
491 $self->tester->ok(0, "Folgende Eingangsrechnungen haben einen falschen Netto-Wert im Nebenbuch:");
493 for my $ap_ac_amount_nok (@{ $ap_amount_not_ac_amount } ) {
494 $self->tester->diag("Rechnungsnummer: $ap_ac_amount_nok->{invnumber} Hauptbuch-Wert: $ap_ac_amount_nok->{amount}
495 Nebenbuch-Wert: $ap_ac_amount_nok->{netamount}");
498 $self->tester->ok(1, "Hauptbuch-Nettowert und Kreditoren-Nebenbuch-Nettowert stimmen überein.");
504 sub check_missing_tax_bookings {
508 # check tax bookings. all taxkey <> 0 should have tax bookings in acc_trans
510 my $query = qq| select trans_id, chart.accno,transdate from acc_trans left join chart on (chart.id = acc_trans.chart_id)
511 WHERE taxkey NOT IN (SELECT taxkey from tax where rate=0) AND trans_id NOT IN
512 (select trans_id from acc_trans where chart_link ilike '%tax%' and trans_id IN
513 (SELECT trans_id from acc_trans where taxkey NOT IN (SELECT taxkey from tax where rate=0)))
514 AND transdate >= ? AND transdate <= ?|;
516 my $missing_tax_bookings = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
518 if ( scalar @{ $missing_tax_bookings } > 0 ) {
519 $self->tester->ok(0, "Folgende Konten weisen Buchungen ohne Steuerverknüpfung auf:");
521 for my $acc_trans_nok (@{ $missing_tax_bookings } ) {
522 $self->tester->diag("Kontonummer: $acc_trans_nok->{accno} Belegdatum: $acc_trans_nok->{transdate} Trans-ID: $acc_trans_nok->{trans_id}.
523 Kann über System -> Korrekturen im Hauptbuch bereinigt werden. Falls es ein Zahlungskonto ist, wurde
524 ggf. ein Brutto-Skonto-Konto mit einer Netto-Rechnung verknüpft. Kann nur per SQL geändert werden.");
527 $self->tester->ok(1, "Hauptbuch-Nettowert und Nebenbuch-Nettowert stimmen überein.");
531 sub check_bank_transactions_overpayments {
535 select id,amount,invoice_amount, purpose,transdate from bank_transactions where abs(invoice_amount) > abs(amount)
536 AND transdate >= ? AND transdate <= ? order by transdate|;
538 my $overpaids_bank_transactions = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
540 my $correct = 0 == @$overpaids_bank_transactions;
542 $self->tester->ok($correct, "Keine überbuchte Banktransaktion (der zugeordnete Betrag ist nicht höher, als der Überweisungsbetrag).");
543 for my $overpaid_bank_transaction (@{ $overpaids_bank_transactions }) {
544 $self->tester->diag("Überbuchte Bankbewegung!
545 Verwendungszweck: $overpaid_bank_transaction->{purpose}
546 Transaktionsdatum: $overpaid_bank_transaction->{transdate}
547 Betrag= $overpaid_bank_transaction->{amount} Zugeordneter Betrag = $overpaid_bank_transaction->{invoice_amount}
548 Bitte kontaktieren Sie Ihren kivitendo-DB-Admin, der die Überweisung wieder zurücksetzt (Table: bank_transactions Column: invoice_amount).");
552 sub check_ar_paid_acc_trans {
556 select sum(ac.amount) as paid_amount, ar.invnumber,ar.paid
557 from acc_trans ac left join ar on (ac.trans_id = ar.id)
558 WHERE ac.chart_link like '%AR_paid%'
559 AND ac.trans_id in (SELECT trans_id from acc_trans ac where ac.transdate >= ? AND ac.transdate <= ?)
560 group by invnumber, paid having sum(ac.amount) <> ar.paid*-1|;
562 my $ar_amount_not_ac_amount = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
564 if ( scalar @{ $ar_amount_not_ac_amount } > 0 ) {
565 $self->tester->ok(0, "Folgende Ausgangsrechnungen haben einen falschen Bezahl-Wert im Nebenbuch:");
567 for my $ar_ac_amount_nok (@{ $ar_amount_not_ac_amount } ) {
568 $self->tester->diag("Rechnungsnummer: $ar_ac_amount_nok->{invnumber} Hauptbuch-Wert: $ar_ac_amount_nok->{paid_amount}
569 Nebenbuch-Wert: $ar_ac_amount_nok->{paid}");
572 $self->tester->ok(1, "Hauptbuch-Bezahlwert und Debitoren-Nebenbuch-Bezahlwert stimmen überein.");
576 sub check_ap_paid_acc_trans {
580 select sum(ac.amount) as paid_amount, ap.invnumber,ap.paid
581 from acc_trans ac left join ap on (ac.trans_id = ap.id)
582 WHERE ac.chart_link like '%AP_paid%'
583 AND ac.trans_id in (SELECT trans_id from acc_trans ac where ac.transdate >= ? AND ac.transdate <= ?)
584 group by trans_id,invnumber,paid having sum(ac.amount) <> ap.paid|;
586 my $ap_amount_not_ac_amount = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
588 if ( scalar @{ $ap_amount_not_ac_amount } > 0 ) {
589 $self->tester->ok(0, "Folgende Eingangsrechnungen haben einen falschen Bezahl-Wert im Nebenbuch:");
591 for my $ap_ac_amount_nok (@{ $ap_amount_not_ac_amount } ) {
592 $self->tester->diag("Rechnungsnummer: $ap_ac_amount_nok->{invnumber} Hauptbuch-Wert: $ap_ac_amount_nok->{paid_amount}
593 Nebenbuch-Wert: $ap_ac_amount_nok->{paid}");
596 $self->tester->ok(1, "Hauptbuch Bezahl-Wert und Kreditoren-Nebenbuch-Bezahlwert stimmen überein.");
600 sub check_zero_amount_paid_but_datepaid_exists {
603 my $query = qq|(SELECT invnumber,datepaid from ar where datepaid is NOT NULL AND paid = 0
604 AND id not IN (select trans_id from acc_trans WHERE chart_link like '%paid%' AND acc_trans.trans_id = ar.id)
605 AND datepaid >= ? AND datepaid <= ?)
607 (SELECT invnumber,datepaid from ap where datepaid is NOT NULL AND paid = 0
608 AND id not IN (select trans_id from acc_trans WHERE chart_link like '%paid%' AND acc_trans.trans_id = ap.id)
609 AND datepaid >= ? AND datepaid <= ?)|;
611 my $datepaid_should_be_null = selectall_hashref_query($::form, $self->dbh, $query,
612 $self->fromdate, $self->todate,
613 $self->fromdate, $self->todate);
615 if ( scalar @{ $datepaid_should_be_null } > 0 ) {
616 $self->tester->ok(0, "Folgende Rechnungen haben ein Bezahl-Datum, aber keinen Bezahl-Wert im Nebenbuch:");
618 for my $datepaid_should_be_null_nok (@{ $datepaid_should_be_null } ) {
619 $self->tester->diag("Rechnungsnummer: $datepaid_should_be_null_nok->{invnumber}
620 Bezahl-Datum: $datepaid_should_be_null_nok->{datepaid}");
623 $self->tester->ok(1, "Kein Bezahl-Datum ohne Bezahl-Wert und ohne wirkliche Zahlungen gefunden (arap.datepaid, arap.paid konsistent).");
627 sub check_orphaned_reconciliated_links {
631 SELECT purpose from bank_transactions
632 WHERE cleared is true
633 AND id not in (SELECT bank_transaction_id from reconciliation_links)
634 AND transdate >= ? AND transdate <= ?|;
636 my $bt_cleared_no_link = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
638 if ( scalar @{ $bt_cleared_no_link } > 0 ) {
639 $self->tester->ok(0, "Verwaiste abgeglichene Bankbewegungen gefunden. Bei folgenden Bankbewegungen ist die abgleichende Verknüpfung gelöscht worden:");
641 for my $bt_orphaned (@{ $bt_cleared_no_link }) {
642 $self->tester->diag("Verwendungszweck: $bt_orphaned->{purpose}");
645 $self->tester->ok(1, "Keine verwaisten Einträge in abgeglichenen Bankbewegungen.");
649 sub check_recommended_client_settings {
654 # expand: check datev && check mark_as_paid
655 my %settings_values_nok = (
656 SL::DB::Default->get->is_changeable => 1,
657 SL::DB::Default->get->ar_changeable => 1,
658 SL::DB::Default->get->ap_changeable => 1,
659 SL::DB::Default->get->ir_changeable => 1,
660 SL::DB::Default->get->gl_changeable => 1,
663 foreach (keys %settings_values_nok) {
664 if ($_ == $settings_values_nok{$_}) {
665 $self->tester->ok(0, "Buchungskonfiguration: Mindestens ein Belegtyp ist immer änderbar.");
670 # payments more strict (avoid losing payments acc_trans_ids)
671 my $payments_ok = SL::DB::Default->get->payments_changeable == 0 ? 1 : 0;
672 $self->tester->ok(0, "Manuelle Zahlungen sind zu lange änderbar (Empfehlung: niemals).") unless $payments_ok;
674 $self->tester->ok(1, "Mandantenkonfiguration optimal eingestellt.") if ($payments_ok && $all_ok);
677 sub check_orphaned_bank_transaction_acc_trans_links {
681 SELECT purpose from bank_transactions
682 WHERE invoice_amount <> 0
683 AND id not in (SELECT bank_transaction_id from bank_transaction_acc_trans)
684 AND itime > (SELECT min(itime) from bank_transaction_acc_trans)
685 AND transdate >= ? AND transdate <= ?|;
687 my $bt_assigned_no_link = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
689 if ( scalar @{ $bt_assigned_no_link } > 0 ) {
690 $self->tester->ok(0, "Verwaiste Verknüpfungen zu Bankbewegungen gefunden. Bei folgenden Bankbewegungen ist eine interne Verknüpfung gelöscht worden:");
692 for my $bt_orphaned (@{ $bt_assigned_no_link }) {
693 $self->tester->diag("Verwendungszweck: $bt_orphaned->{purpose}");
696 $self->tester->ok(1, "Keine verwaisten Einträge in verknüpften Bankbewegungen (Richtung Bank).");
698 # check for deleted acc_trans_ids
700 SELECT purpose from bank_transactions
702 (SELECT bank_transaction_id from bank_transaction_acc_trans
703 where acc_trans_id NOT IN (select acc_trans_id from acc_trans)
704 AND transdate >= ? AND transdate <= ?)|;
706 my $bt_assigned_no_acc_trans = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
708 if ( scalar @{ $bt_assigned_no_acc_trans } > 0 ) {
709 $self->tester->ok(0, "Verwaiste Verknüpfungen zu Bankbewegungen gefunden. Bei folgenden Bankbewegungen ist eine interne Verknüpfung gelöscht worden:");
711 for my $bt_orphaned (@{ $bt_assigned_no_acc_trans }) {
712 $self->tester->diag("Verwendungszweck: $bt_orphaned->{purpose}");
715 $self->tester->ok(1, "Keine verwaisten Einträge in verknüpften Bankbewegungen (Richtung Buchung (Richtung Buchung)).");
727 SL::BackgroundJob::SelfTest::Transactions - base tests
731 Several tests for data integrity.
739 G. Richardson E<lt>information@richardson-bueren.deE<gt>
740 Jan Büren E<lt>information@richardson-bueren.deE<gt>
741 Sven Schoeling E<lt>s.schoeling@linet-services.deE<gt>