$self->_setup;
- $self->tester->plan(tests => 20);
+ $self->tester->plan(tests => 24);
$self->check_konten_mit_saldo_nicht_in_guv;
$self->check_bilanzkonten_mit_pos_eur;
$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;
}
sub _setup {
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%'
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_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.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.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, "Hauptbuch Bezahl-Wert und Kreditoren-Nebenbuch-Bezahlwert stimmen überein.");
+ }
+}
1;
projects / kivitendo-erp.git / blobdiff