$self->_setup;
- $self->tester->plan(tests => 21);
+ $self->tester->plan(tests => 26);
$self->check_konten_mit_saldo_nicht_in_guv;
$self->check_bilanzkonten_mit_pos_eur;
$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;
}
sub _setup {
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 <= ?|;
+ SELECT sum(amount) from ar a WHERE a.id IN
+ (SELECT id from ap 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 <= ?|;
+ SELECT sum(amount) from ap a WHERE a.id IN
+ (SELECT id from ap 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');
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}");
}
}
# 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"
+ 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 transdate >= ? AND transdate <= ?|;
+ 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->todate, $self->fromdate, $self->todate);
+ 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})");
}
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 'AR_amount%'
+ WHERE ac.chart_link like '%AP_amount%' OR ac.chart_link like '%IC_cogs%'
AND ac.transdate >= ? AND ac.transdate <= ?
- group by invnumber,netamount having sum(ac.amount) <> ap.netamount*-1|;
+ 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);
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 Steuer auf:");
+ $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.");
+ 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_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 id not in (SELECT bank_transaction_id from reconciliation_links)
+ 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);
+}
1;
__END__
projects / kivitendo-erp.git / blobdiff