$self->_setup;
- $self->tester->plan(tests => 18);
+ $self->tester->plan(tests => 34);
$self->check_konten_mit_saldo_nicht_in_guv;
$self->check_bilanzkonten_mit_pos_eur;
$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;
$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 {
# 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);
}
my ($self) = @_;
my $query = qq|
select * from invoice i
- where trans_id not in (select id from ar union select id from ap order by id) |;
+ 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);
+
- my $verwaiste_invoice = selectall_hashref_query($::form, $self->dbh, $query);
if (@$verwaiste_invoice) {
$self->tester->ok(0, "Es gibt verwaiste invoice Einträge! (wo ar/ap-Eintrag fehlt)");
for my $invoice ( @{ $verwaiste_invoice }) {
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) {
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 {
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);
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);
$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}");
}
}
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);
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);
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");
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 }) {
# 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"}
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
}
my ($self) = @_;
my $query = qq|
- select ar.invnumber, ar.netamount, ac.amount
- 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|;
+ 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);
+ 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:");
$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;
Several tests for data integrity.
-=head1 FUNCTIONS
-
-=head1 BUGS
-
=head1 AUTHOR
G. Richardson E<lt>information@richardson-bueren.deE<gt>
Sven Schoeling E<lt>s.schoeling@linet-services.deE<gt>
=cut
-
projects / kivitendo-erp.git / blobdiff