X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FBackgroundJob%2FSelfTest%2FTransactions.pm;h=ab145ceceb2bfb156d6d233eba015e54737fac12;hb=b293ff8ad52fc76ba0c44783e3982418114d6b08;hp=9b5daed21546a06bbe5a49516ca55ed3235726b1;hpb=61cdba5d566357f3beabe0e7f3f0cb2d7bdccd73;p=kivitendo-erp.git diff --git a/SL/BackgroundJob/SelfTest/Transactions.pm b/SL/BackgroundJob/SelfTest/Transactions.pm index 9b5daed21..ab145cece 100644 --- a/SL/BackgroundJob/SelfTest/Transactions.pm +++ b/SL/BackgroundJob/SelfTest/Transactions.pm @@ -15,12 +15,15 @@ sub run { $self->_setup; - $self->tester->plan(tests => 16); + $self->tester->plan(tests => 34); $self->check_konten_mit_saldo_nicht_in_guv; $self->check_bilanzkonten_mit_pos_eur; $self->check_balanced_individual_transactions; $self->check_verwaiste_acc_trans_eintraege; + $self->check_verwaiste_invoice_eintraege; + $self->check_ar_acc_trans_amount; + $self->check_ap_acc_trans_amount; $self->check_netamount_laut_invoice_ar; $self->check_invnumbers_unique; $self->check_summe_stornobuchungen; @@ -33,6 +36,15 @@ sub run { $self->check_overpayments; $self->check_every_account_with_taxkey; $self->calc_saldenvortraege; + $self->check_missing_tax_bookings; + $self->check_bank_transactions_overpayments; + $self->check_ar_paid_acc_trans; + $self->check_ap_paid_acc_trans; + $self->check_zero_amount_paid_but_datepaid_exists; + $self->check_orphaned_reconciliated_links; + $self->check_recommended_client_settings; + $self->check_orphaned_bank_transaction_acc_trans_links; + $self->check_consistent_itimes; } sub _setup { @@ -41,7 +53,6 @@ sub _setup { # TODO FIXME calc dates better, unless this is wanted $self->fromdate(DateTime->new(day => 1, month => 1, year => DateTime->today->year)); $self->todate($self->fromdate->clone->add(years => 1)->add(days => -1)); - $self->dbh($::form->get_standard_dbh); } @@ -128,6 +139,29 @@ sub check_verwaiste_acc_trans_eintraege { } } +sub check_verwaiste_invoice_eintraege { + # this check is always run for all invoice entries in the entire database + my ($self) = @_; + my $query = qq| + select * from invoice i + where trans_id not in (select id from ar WHERE ar.transdate >=? AND ar.transdate <=? + UNION + select id from ap WHERE ap.transdate >= ? and ap.transdate <= ?) + AND i.transdate >=? AND i.transdate <=?|; + + my $verwaiste_invoice = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate, + $self->fromdate, $self->todate, $self->fromdate, $self->todate); + + + if (@$verwaiste_invoice) { + $self->tester->ok(0, "Es gibt verwaiste invoice Einträge! (wo ar/ap-Eintrag fehlt)"); + for my $invoice ( @{ $verwaiste_invoice }) { + $self->tester->diag("invoice: id: $invoice->{id} trans_id: $invoice->{trans_id} description: $invoice->{description} itime: $invoice->{itime}"); + }; + } else { + $self->tester->ok(1, "Keine verwaisten invoice Einträge (wo ar/ap-Eintrag fehlt)"); } +} + sub check_netamount_laut_invoice_ar { my ($self) = @_; my $query = qq| @@ -137,10 +171,13 @@ sub check_netamount_laut_invoice_ar { where a.transdate >= ? and a.transdate <= ?;|; my ($netamount_laut_invoice) = selectfirst_array_query($::form, $self->dbh, $query, $self->fromdate, $self->todate); - $query = qq| select sum(netamount) from ar where transdate >= ? and transdate <= ?; |; + $query = qq| select sum(netamount) from ar where transdate >= ? and transdate <= ? AND invoice; |; my ($netamount_laut_ar) = selectfirst_array_query($::form, $self->dbh, $query, $self->fromdate, $self->todate); - my $correct = $netamount_laut_invoice - $netamount_laut_ar == 0; + # should be enough to get a diff below 1. We have currently the following issues: + # verkaufsbericht berücksichtigt keinen rabatt + # fxsellprice ist mit mwst-inklusive + my $correct = abs($netamount_laut_invoice - $netamount_laut_ar) < 1; $self->tester->ok($correct, "Summe laut Verkaufsbericht sollte gleich Summe aus Verkauf -> Berichte -> Rechnungen sein"); if (!$correct) { @@ -171,22 +208,35 @@ sub check_invnumbers_unique { sub check_summe_stornobuchungen { my ($self) = @_; - my $query = qq| - select sum(amount) from ar a JOIN customer c ON (a.customer_id = c.id) - WHERE storno is true - AND a.transdate >= ? and a.transdate <= ?|; - my ($summe_stornobuchungen_ar) = selectfirst_array_query($::form, $self->dbh, $query, $self->fromdate, $self->todate); - - $query = qq| - select sum(amount) from ap a JOIN vendor c ON (a.vendor_id = c.id) - WHERE storno is true - AND a.transdate >= ? and a.transdate <= ?|; - my ($summe_stornobuchungen_ap) = selectfirst_array_query($::form, $self->dbh, $query, $self->fromdate, $self->todate); - - $self->tester->ok($summe_stornobuchungen_ap == 0, 'Summe aller Einkaufsrechnungen (stornos + stornierte) soll 0 sein'); - $self->tester->ok($summe_stornobuchungen_ar == 0, 'Summe aller Verkaufsrechnungen (stornos + stornierte) soll 0 sein'); - $self->tester->diag("Summe Einkaufsrechnungen (ar): $summe_stornobuchungen_ar") if $summe_stornobuchungen_ar; - $self->tester->diag("Summe Einkaufsrechnungen (ap): $summe_stornobuchungen_ap") if $summe_stornobuchungen_ap; + my %sums_canceled; + my %sums_storno; + foreach my $table (qw(ar ap)) { + # check invoices canceled (stornoed) in consideration period (corresponding stornos do not have to be in this period) + my $query = qq| + SELECT sum(amount) FROM $table WHERE id IN ( + SELECT id FROM $table WHERE storno IS TRUE AND storno_id IS NULL AND transdate >= ? AND transdate <= ? + UNION + SELECT id FROM $table WHERE storno IS TRUE AND storno_id IS NOT NULL AND storno_id IN + (SELECT id FROM $table WHERE storno IS TRUE AND storno_id IS NULL AND transdate >= ? AND transdate <= ?) + )|; + ($sums_canceled{$table}) = selectfirst_array_query($::form, $self->dbh, $query, $self->fromdate, $self->todate, $self->fromdate, $self->todate); + + # check storno invoices in consideration period (corresponding canceled (stornoed) invoices do not have to be in this period) + $query = qq| + SELECT sum(amount) FROM $table WHERE id IN ( + SELECT storno_id FROM $table WHERE storno IS TRUE AND storno_id IS NOT NULL AND transdate >= ? AND transdate <= ? + UNION + SELECT id FROM $table WHERE storno IS TRUE AND storno_id IS NOT NULL AND transdate >= ? AND transdate <= ? + )|; + ($sums_storno{$table}) = selectfirst_array_query($::form, $self->dbh, $query, $self->fromdate, $self->todate, $self->fromdate, $self->todate); + + my $text_rg = ($table eq 'ar') ? 'Verkaufsrechnungen' : 'Einkaufsrechnungen'; + + $self->tester->ok($sums_canceled{$table} == 0, "Summe aller $text_rg (stornos + stornierte) soll 0 sein (für stornierte Rechnungen)"); + $self->tester->ok($sums_storno {$table} == 0, "Summe aller $text_rg (stornos + stornierte) soll 0 sein (für Storno-Rechnungen)"); + $self->tester->diag("Summe $text_rg ($table) (für stornierte Rechnungen) : " . $sums_canceled{$table}) if $sums_canceled{$table} != 0; + $self->tester->diag("Summe $text_rg ($table) (für Storno-Rechnungen) : " . $sums_storno {$table}) if $sums_storno {$table} != 0; + } } sub check_ar_paid { @@ -200,7 +250,7 @@ sub check_ar_paid { where (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 AND storno is false - AND transdate >= ? and transdate <= ? + AND ar.id in (SELECT id from ar where transdate >= ? and transdate <= ?) order by diff |; my $paid_diffs_ar = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate); @@ -219,13 +269,13 @@ sub check_ap_paid { my ($self) = @_; my $query = qq| - select invnumber,paid, + select invnumber,paid,id, (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 , 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 from ap where (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 - AND transdate >= ? and transdate <= ? + AND ap.id in (SELECT id from ap where transdate >= ? and transdate <= ?) order by diff |; my $paid_diffs_ap = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate); @@ -235,7 +285,7 @@ sub check_ap_paid { $self->tester->ok(!$errors, "Vergleich ap paid mit acc_trans AP_paid"); for my $paid_diff_ap (@{ $paid_diffs_ap }) { next if $paid_diff_ap->{diff} == 0; - $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}"); + $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}"); } } @@ -244,9 +294,9 @@ sub check_ar_overpayments { my $query = qq| select invnumber,paid,amount,transdate,c.customernumber,c.name from ar left join customer c on (ar.customer_id = c.id) - where abs(paid) > abs(amount) + where abs(paid) > abs(amount) AND transdate >= ? and transdate <= ? - order by invnumber;|; + order by invnumber;|; my $overpaids_ar = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate); @@ -263,9 +313,9 @@ sub check_ap_overpayments { my $query = qq| select invnumber,paid,amount,transdate,vc.vendornumber,vc.name from ap left join vendor vc on (ap.vendor_id = vc.id) - where abs(paid) > abs(amount) + where abs(paid) > abs(amount) AND transdate >= ? and transdate <= ? - order by invnumber;|; + order by invnumber;|; my $overpaids_ap = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate); @@ -281,13 +331,15 @@ sub check_paid_stornos { my ($self) = @_; my $query = qq| - 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" + 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" FROM ar, customer WHERE paid != amount AND ar.storno AND (ar.customer_id = customer.id) AND ar.transdate >= ? and ar.transdate <= ? - GROUP BY ar.invnumber;|; + GROUP BY ar.invnumber|; my $paid_stornos = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate); $self->tester->ok(0 == @$paid_stornos, "Keine bezahlten Stornos"); @@ -304,19 +356,20 @@ sub check_stornos_ohne_partner { FROM ar LEFT JOIN customer c on (c.id = ar.customer_id) 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) + AND ar.transdate >= ? and ar.transdate <= ? UNION SELECT (SELECT cast ('ap' as text)) as invoice,ap.id,invnumber,storno,amount,transdate,type,vendornumber as cv_number FROM ap LEFT JOIN vendor v on (v.id = ap.vendor_id) - 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); - |; + 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) + AND ap.transdate >= ? and ap.transdate <= ?|; - my $stornos_ohne_partner = selectall_hashref_query($::form, $self->dbh, $query); + my $stornos_ohne_partner = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate, + $self->fromdate, $self->todate); $self->tester->ok(@$stornos_ohne_partner == 0, 'Es sollte keine Stornos ohne Partner geben'); if (@$stornos_ohne_partner) { - $self->tester->diag("Stornos ohne Partner: (kann passieren wenn Stornorechnung außerhalb Zeitraum liegt)"); - $self->tester->diag("gilt aber trotzdem als paid zu dem Zeitpunkt, oder?"); + $self->tester->diag("Stornos ohne Partner, oder Storno über Jahreswechsel hinaus"); } my $stornoheader = 0; for my $storno (@{ $stornos_ohne_partner }) { @@ -332,22 +385,23 @@ sub check_overpayments { # Vergleich ar.paid und das was laut acc_trans bezahlt wurde # "als bezahlt markieren" ohne sauberes Ausbuchen führt zu Differenzen bei offenen Forderungen - # geht nur auf wenn acc_trans Zahlungseingänge auch im Untersuchungszeitraum lagen - # Stornos werden rausgefiltert + # Berücksichtigt Zahlungseingänge im Untersuchungszeitraums und + # prüft weitere Zahlungen und Buchungen über trans_id (kein Zeitfilter) + my $query = qq| -SELECT -invnumber,customernumber,name,ar.transdate,ar.datepaid, -amount, -amount-paid as "open via ar", -paid as "paid via ar", -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" -FROM ar left join customer c on (c.id = ar.customer_id) -WHERE - (ar.storno IS FALSE) - AND (transdate <= ? ) -;|; - - my $invoices = selectall_hashref_query($::form, $self->dbh, $query, $self->todate, $self->todate); + SELECT + invnumber,customernumber,name,ar.transdate,ar.datepaid, + amount, + amount-paid as "open via ar", + paid as "paid via ar", + coalesce((SELECT sum(amount)*-1 FROM acc_trans + WHERE chart_link ilike '%paid%' AND acc_trans.trans_id=ar.id),0) as "paid via acc_trans" + FROM ar left join customer c on (c.id = ar.customer_id) + WHERE + ar.storno IS FALSE + AND ar.id in (SELECT trans_id from acc_trans where transdate >= ? AND transdate <= ? AND chart_link ilike '%paid%')|; + + my $invoices = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate); my $count_overpayments = scalar grep { $_->{"paid via ar"} != $_->{"paid via acc_trans"} @@ -360,14 +414,14 @@ WHERE if ($count_overpayments) { for my $invoice (@{ $invoices }) { if ($invoice->{"paid via ar"} != $invoice->{"paid via acc_trans"}) { - $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!)"); + $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!)"); if (defined $invoice->{datepaid}) { $self->tester->diag("datepaid = $invoice->{datepaid})"); } - $self->tester->diag("Überzahlung!") if $invoice->{"paid via acc_trans"} > $invoice->{amount}; + $self->tester->diag("Überzahlung bei Rechnung: $invoice->{invnumber}") if $invoice->{"paid via acc_trans"} > $invoice->{amount}; } elsif ( $invoice->{"amount"} - $invoice->{"paid via acc_trans"} != $invoice->{"open via ar"} && $invoice->{"paid via ar"} != $invoice->{"paid via acc_trans"}) { $self->tester->diag("amount - paid_via_acc_trans != open_via_ar"); - $self->tester->diag("Überzahlung!") if $invoice->{"paid via acc_trans"} > $invoice->{amount}; + $self->tester->diag("Überzahlung bei Rechnung: $invoice->{invnumber}") if $invoice->{"paid via acc_trans"} > $invoice->{amount}; } else { # nothing wrong } @@ -410,6 +464,343 @@ sub check_every_account_with_taxkey { } } +sub check_ar_acc_trans_amount { + my ($self) = @_; + + my $query = qq| + select sum(ac.amount) as amount, ar.invnumber,ar.netamount + from acc_trans ac left join ar on (ac.trans_id = ar.id) + WHERE ac.chart_link like 'AR_amount%' + AND ac.transdate >= ? AND ac.transdate <= ? + AND ar.type = 'invoice' + group by invnumber,netamount having sum(ac.amount) <> ar.netamount|; + + my $ar_amount_not_ac_amount = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate); + + if ( scalar @{ $ar_amount_not_ac_amount } > 0 ) { + $self->tester->ok(0, "Folgende Ausgangsrechnungen haben einen falschen Netto-Wert im Nebenbuch:"); + + for my $ar_ac_amount_nok (@{ $ar_amount_not_ac_amount } ) { + $self->tester->diag("Rechnungsnummer: $ar_ac_amount_nok->{invnumber} Hauptbuch-Wert: $ar_ac_amount_nok->{amount} + Nebenbuch-Wert: $ar_ac_amount_nok->{netamount}"); + } + } else { + $self->tester->ok(1, "Hauptbuch-Nettowert und Debitoren-Nebenbuch-Nettowert stimmen überein."); + } + +} + +sub check_ap_acc_trans_amount { + my ($self) = @_; + + my $query = qq| + select sum(ac.amount) as amount, ap.invnumber,ap.netamount + from acc_trans ac left join ap on (ac.trans_id = ap.id) + WHERE (ac.chart_link like '%AP_amount%' OR ac.chart_link like '%IC_cogs%') + AND ac.transdate >= ? AND ac.transdate <= ? + group by invnumber,trans_id,netamount having sum(ac.amount) <> ap.netamount*-1|; + + my $ap_amount_not_ac_amount = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate); + + if ( scalar @{ $ap_amount_not_ac_amount } > 0 ) { + $self->tester->ok(0, "Folgende Eingangsrechnungen haben einen falschen Netto-Wert im Nebenbuch:"); + + for my $ap_ac_amount_nok (@{ $ap_amount_not_ac_amount } ) { + $self->tester->diag("Rechnungsnummer: $ap_ac_amount_nok->{invnumber} Hauptbuch-Wert: $ap_ac_amount_nok->{amount} + Nebenbuch-Wert: $ap_ac_amount_nok->{netamount}"); + } + } else { + $self->tester->ok(1, "Hauptbuch-Nettowert und Kreditoren-Nebenbuch-Nettowert stimmen überein."); + } + +} + + +sub check_missing_tax_bookings { + + my ($self) = @_; + + # check tax bookings. all taxkey <> 0 should have tax bookings in acc_trans + + my $query = qq| select trans_id, chart.accno,transdate from acc_trans left join chart on (chart.id = acc_trans.chart_id) + WHERE taxkey NOT IN (SELECT taxkey from tax where rate=0) AND trans_id NOT IN + (select trans_id from acc_trans where chart_link ilike '%tax%' and trans_id IN + (SELECT trans_id from acc_trans where taxkey NOT IN (SELECT taxkey from tax where rate=0))) + AND transdate >= ? AND transdate <= ?|; + + my $missing_tax_bookings = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate); + + if ( scalar @{ $missing_tax_bookings } > 0 ) { + $self->tester->ok(0, "Folgende Konten weisen Buchungen ohne Steuerverknüpfung auf:"); + + for my $acc_trans_nok (@{ $missing_tax_bookings } ) { + $self->tester->diag("Kontonummer: $acc_trans_nok->{accno} Belegdatum: $acc_trans_nok->{transdate} Trans-ID: $acc_trans_nok->{trans_id}. + Kann über System -> Korrekturen im Hauptbuch bereinigt werden. Falls es ein Zahlungskonto ist, wurde + ggf. ein Brutto-Skonto-Konto mit einer Netto-Rechnung verknüpft. Kann nur per SQL geändert werden."); + } + } else { + $self->tester->ok(1, "Hauptbuch-Nettowert und Nebenbuch-Nettowert stimmen überein."); + } +} + +sub check_bank_transactions_overpayments { + my ($self) = @_; + + my $query = qq| + select id,amount,invoice_amount, purpose,transdate from bank_transactions where abs(invoice_amount) > abs(amount) + AND transdate >= ? AND transdate <= ? order by transdate|; + + my $overpaids_bank_transactions = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate); + + my $correct = 0 == @$overpaids_bank_transactions; + + $self->tester->ok($correct, "Keine überbuchte Banktransaktion (der zugeordnete Betrag ist nicht höher, als der Überweisungsbetrag)."); + for my $overpaid_bank_transaction (@{ $overpaids_bank_transactions }) { + $self->tester->diag("Überbuchte Bankbewegung! + Verwendungszweck: $overpaid_bank_transaction->{purpose} + Transaktionsdatum: $overpaid_bank_transaction->{transdate} + Betrag= $overpaid_bank_transaction->{amount} Zugeordneter Betrag = $overpaid_bank_transaction->{invoice_amount} + Bitte kontaktieren Sie Ihren kivitendo-DB-Admin, der die Überweisung wieder zurücksetzt (Table: bank_transactions Column: invoice_amount)."); + } +} + +sub check_ar_paid_acc_trans { + my ($self) = @_; + + my $query = qq| + select sum(ac.amount) as paid_amount, ar.invnumber,ar.paid + from acc_trans ac left join ar on (ac.trans_id = ar.id) + WHERE ac.chart_link like '%AR_paid%' + AND ac.trans_id in (SELECT trans_id from acc_trans ac where ac.transdate >= ? AND ac.transdate <= ?) + group by invnumber, paid having sum(ac.amount) <> ar.paid*-1|; + + my $ar_amount_not_ac_amount = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate); + + if ( scalar @{ $ar_amount_not_ac_amount } > 0 ) { + $self->tester->ok(0, "Folgende Ausgangsrechnungen haben einen falschen Bezahl-Wert im Nebenbuch:"); + + for my $ar_ac_amount_nok (@{ $ar_amount_not_ac_amount } ) { + $self->tester->diag("Rechnungsnummer: $ar_ac_amount_nok->{invnumber} Hauptbuch-Wert: $ar_ac_amount_nok->{paid_amount} + Nebenbuch-Wert: $ar_ac_amount_nok->{paid}"); + } + } else { + $self->tester->ok(1, "Hauptbuch-Bezahlwert und Debitoren-Nebenbuch-Bezahlwert stimmen überein."); + } +} + +sub check_ap_paid_acc_trans { + my ($self) = @_; + + my $query = qq| + select sum(ac.amount) as paid_amount, ap.invnumber,ap.paid + from acc_trans ac left join ap on (ac.trans_id = ap.id) + WHERE ac.chart_link like '%AP_paid%' + AND ac.trans_id in (SELECT trans_id from acc_trans ac where ac.transdate >= ? AND ac.transdate <= ?) + group by trans_id,invnumber,paid having sum(ac.amount) <> ap.paid|; + + my $ap_amount_not_ac_amount = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate); + + if ( scalar @{ $ap_amount_not_ac_amount } > 0 ) { + $self->tester->ok(0, "Folgende Eingangsrechnungen haben einen falschen Bezahl-Wert im Nebenbuch:"); + + for my $ap_ac_amount_nok (@{ $ap_amount_not_ac_amount } ) { + $self->tester->diag("Rechnungsnummer: $ap_ac_amount_nok->{invnumber} Hauptbuch-Wert: $ap_ac_amount_nok->{paid_amount} + Nebenbuch-Wert: $ap_ac_amount_nok->{paid}"); + } + } else { + $self->tester->ok(1, "Hauptbuch Bezahl-Wert und Kreditoren-Nebenbuch-Bezahlwert stimmen überein."); + } +} + +sub check_zero_amount_paid_but_datepaid_exists { + my ($self) = @_; + + my $query = qq|(SELECT invnumber,datepaid from ar where datepaid is NOT NULL AND paid = 0 + AND id not IN (select trans_id from acc_trans WHERE chart_link like '%paid%' AND acc_trans.trans_id = ar.id) + AND datepaid >= ? AND datepaid <= ?) + UNION + (SELECT invnumber,datepaid from ap where datepaid is NOT NULL AND paid = 0 + AND id not IN (select trans_id from acc_trans WHERE chart_link like '%paid%' AND acc_trans.trans_id = ap.id) + AND datepaid >= ? AND datepaid <= ?)|; + + my $datepaid_should_be_null = selectall_hashref_query($::form, $self->dbh, $query, + $self->fromdate, $self->todate, + $self->fromdate, $self->todate); + + if ( scalar @{ $datepaid_should_be_null } > 0 ) { + $self->tester->ok(0, "Folgende Rechnungen haben ein Bezahl-Datum, aber keinen Bezahl-Wert im Nebenbuch:"); + + for my $datepaid_should_be_null_nok (@{ $datepaid_should_be_null } ) { + $self->tester->diag("Rechnungsnummer: $datepaid_should_be_null_nok->{invnumber} + Bezahl-Datum: $datepaid_should_be_null_nok->{datepaid}"); + } + } else { + $self->tester->ok(1, "Kein Bezahl-Datum ohne Bezahl-Wert und ohne wirkliche Zahlungen gefunden (arap.datepaid, arap.paid konsistent)."); + } +} + +sub check_orphaned_reconciliated_links { + my ($self) = @_; + + my $query = qq| + SELECT purpose from bank_transactions + WHERE cleared is true + AND NOT EXISTS (SELECT bank_transaction_id from reconciliation_links WHERE bank_transaction_id = bank_transactions.id) + AND transdate >= ? AND transdate <= ?|; + + my $bt_cleared_no_link = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate); + + if ( scalar @{ $bt_cleared_no_link } > 0 ) { + $self->tester->ok(0, "Verwaiste abgeglichene Bankbewegungen gefunden. Bei folgenden Bankbewegungen ist die abgleichende Verknüpfung gelöscht worden:"); + + for my $bt_orphaned (@{ $bt_cleared_no_link }) { + $self->tester->diag("Verwendungszweck: $bt_orphaned->{purpose}"); + } + } else { + $self->tester->ok(1, "Keine verwaisten Einträge in abgeglichenen Bankbewegungen."); + } +} + +sub check_recommended_client_settings { + my ($self) = @_; + + my $all_ok = 1; + + # expand: check datev && check mark_as_paid + my %settings_values_nok = ( + SL::DB::Default->get->is_changeable => 1, + SL::DB::Default->get->ar_changeable => 1, + SL::DB::Default->get->ap_changeable => 1, + SL::DB::Default->get->ir_changeable => 1, + SL::DB::Default->get->gl_changeable => 1, + ); + + foreach (keys %settings_values_nok) { + if ($_ == $settings_values_nok{$_}) { + $self->tester->ok(0, "Buchungskonfiguration: Mindestens ein Belegtyp ist immer änderbar."); + undef $all_ok; + } + } + + # payments more strict (avoid losing payments acc_trans_ids) + my $payments_ok = SL::DB::Default->get->payments_changeable == 0 ? 1 : 0; + $self->tester->ok(0, "Manuelle Zahlungen sind zu lange änderbar (Empfehlung: niemals).") unless $payments_ok; + + $self->tester->ok(1, "Mandantenkonfiguration optimal eingestellt.") if ($payments_ok && $all_ok); +} + +sub check_orphaned_bank_transaction_acc_trans_links { + my ($self) = @_; + + my $query = qq| + SELECT purpose from bank_transactions + WHERE invoice_amount <> 0 + AND NOT EXISTS (SELECT bank_transaction_id FROM bank_transaction_acc_trans WHERE bank_transaction_id = bank_transactions.id) + AND itime > (SELECT min(itime) from bank_transaction_acc_trans) + AND transdate >= ? AND transdate <= ?|; + + my $bt_assigned_no_link = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate); + + if ( scalar @{ $bt_assigned_no_link } > 0 ) { + $self->tester->ok(0, "Verwaiste Verknüpfungen zu Bankbewegungen gefunden. Bei folgenden Bankbewegungen ist eine interne Verknüpfung gelöscht worden:"); + + for my $bt_orphaned (@{ $bt_assigned_no_link }) { + $self->tester->diag("Verwendungszweck: $bt_orphaned->{purpose}"); + } + } else { + $self->tester->ok(1, "Keine verwaisten Einträge in verknüpften Bankbewegungen (Richtung Bank)."); + } + # check for deleted acc_trans_ids + $query = qq| + SELECT purpose from bank_transactions + WHERE id in + (SELECT bank_transaction_id from bank_transaction_acc_trans + WHERE NOT EXISTS (SELECT acc_trans.acc_trans_id FROM acc_trans WHERE acc_trans.acc_trans_id = bank_transaction_acc_trans.acc_trans_id) + AND transdate >= ? AND transdate <= ?)|; + + my $bt_assigned_no_acc_trans = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate); + + if ( scalar @{ $bt_assigned_no_acc_trans } > 0 ) { + $self->tester->ok(0, "Verwaiste Verknüpfungen zu Bankbewegungen gefunden. Bei folgenden Bankbewegungen ist eine interne Verknüpfung gelöscht worden:"); + + for my $bt_orphaned (@{ $bt_assigned_no_acc_trans }) { + $self->tester->diag("Verwendungszweck: $bt_orphaned->{purpose}"); + } + } else { + $self->tester->ok(1, "Keine verwaisten Einträge in verknüpften Bankbewegungen (Richtung Buchung (Richtung Buchung))."); + } +} + +sub check_consistent_itimes { + my ($self) = @_; + my $query; + + $query = qq| + SELECT mtime, itime,gldate, acc_trans_id, trans_id + FROM acc_trans a + WHERE itime::date <> gldate::date + AND a.transdate >= ? and a.transdate <= ?|; + + my $itimes_ac = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate); + + if ( scalar @{ $itimes_ac } > 0 ) { + $self->tester->ok(0, "Inkonsistente Zeitstempel in der acc_trans gefunden. Bei folgenden ids:"); + for my $bogus_time (@{ $itimes_ac }) { + $self->tester->diag("ID: $bogus_time->{trans_id} acc_trans_id: $bogus_time->{acc_trans_id} "); + } + } else { + $self->tester->ok(1, "Keine inkonsistenten Zeitstempel in der acc_trans."); + } + $query = qq| + SELECT amount, itime, gldate, id + FROM ap a + WHERE itime::date <> gldate::date + AND a.transdate >= ? and a.transdate <= ?|; + + my $itimes_ap = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate); + + if ( scalar @{ $itimes_ap } > 0 ) { + $self->tester->ok(0, "Inkonsistente Zeitstempel in ap gefunden. Bei folgenden ids:"); + for my $bogus_time (@{ $itimes_ap }) { + $self->tester->diag("ID: $bogus_time->{id} itime: $bogus_time->{itime} mtime: $bogus_time->{mtime} "); + } + } else { + $self->tester->ok(1, "Keine inkonsistenten Zeitstempel in ap."); + } + $query = qq| + SELECT amount, itime, gldate, id + FROM ar a + WHERE itime::date <> gldate::date + AND a.transdate >= ? and a.transdate <= ?|; + + my $itimes_ar = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate); + + if ( scalar @{ $itimes_ap } > 0 ) { + $self->tester->ok(0, "Inkonsistente Zeitstempel in ar gefunden. Bei folgenden ids:"); + for my $bogus_time (@{ $itimes_ar }) { + $self->tester->diag("ID: $bogus_time->{id} itime: $bogus_time->{itime} mtime: $bogus_time->{mtime} "); + } + } else { + $self->tester->ok(1, "Keine inkonsistenten Zeitstempel in ar."); + } + $query = qq| + SELECT itime, gldate, id, mtime + FROM gl a + WHERE itime::date <> gldate::date + AND a.transdate >= ? and a.transdate <= ?|; + + my $itimes_gl = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate); + + if ( scalar @{ $itimes_gl } > 0 ) { + $self->tester->ok(0, "Inkonsistente Zeitstempel in gl gefunden. Bei folgenden ids:"); + for my $bogus_time (@{ $itimes_ar }) { + $self->tester->diag("ID: $bogus_time->{id} itime: $bogus_time->{itime} mtime: $bogus_time->{mtime} "); + } + } else { + $self->tester->ok(1, "Keine inkonsistenten Zeitstempel in gl."); + } +} + 1; __END__ @@ -424,10 +815,6 @@ SL::BackgroundJob::SelfTest::Transactions - base tests Several tests for data integrity. -=head1 FUNCTIONS - -=head1 BUGS - =head1 AUTHOR G. Richardson Einformation@richardson-bueren.deE @@ -435,4 +822,3 @@ Jan Büren Einformation@richardson-bueren.deE Sven Schoeling Es.schoeling@linet-services.deE =cut -