projekt kivitendo

package SL::BackgroundJob::SelfTest::Transactions;

use utf8;

use strict;

use parent qw(SL::BackgroundJob::SelfTest::Base);

use SL::DBUtils;

use Rose::Object::MakeMethods::Generic (

scalar => [ qw(dbh fromdate todate) ],

);

sub run {

my ($self) = @_;

$self->_setup;

$self->check_konten_mit_saldo_nicht_in_guv;

$self->check_verwaiste_acc_trans_eintraege;

$self->check_invnumbers_unique;

$self->check_summe_stornobuchungen;

$self->check_ar_paid;

$self->check_ap_paid;

$self->check_ar_overpayments;

$self->check_ap_overpayments;

$self->check_paid_stornos;

$self->check_stornos_ohne_partner;

$self->check_overpayments;

$self->check_ap_paid_acc_trans;

sub _setup {

my ($self) = @_;

# TODO FIXME calc dates better, unless this is wanted

$self->dbh($::form->get_standard_dbh);

}

sub check_konten_mit_saldo_nicht_in_guv {

my ($self) = @_;

my $query = qq|

SELECT c.accno, c.description, c.category, SUM(a.amount) AS Saldo

FROM chart c,

acc_trans a

WHERE c.id = a.chart_id

and (c.category like 'I' or c.category like 'E')

and amount != 0

and pos_eur is null

and a.transdate >= ? and a.transdate <= ?

GROUP BY c.accno,c.description,c.category,c.pos_bilanz,c.pos_eur

ORDER BY c.accno|;

my $konten_nicht_in_guv = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);

my $correct = 0 == scalar grep { $_->{Saldo} } @$konten_nicht_in_guv;

$self->tester->ok($correct, "Erfolgskonten mit Saldo nicht in GuV (Saldenvortragskonten können ignoriert werden, sollten aber 0 sein)");

if (!$correct) {

for my $konto (@$konten_nicht_in_guv) {

$self->tester->diag($konto);

}

}

}

my ($self) = @_;

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)|;

my $bilanzkonten_mit_pos_eur = selectall_hashref_query($::form, $self->dbh, $query);

if (@$bilanzkonten_mit_pos_eur) {

$self->tester->ok(0, "Es gibt Bilanzkonten die der GuV/EÜR zugeordnet sind)");

$self->tester->diag("$_->{accno} $_->{description}") for @$bilanzkonten_mit_pos_eur;

} else {

$self->tester->ok(1, "Keine Bilanzkonten in der GuV");

}

}

my ($self) = @_;

my $query = qq|

select sum(ac.amount) as amount,trans_id,ar.invnumber as ar,ap.invnumber as ap,gl.reference as gl

from acc_trans ac

left join ar on (ar.id = ac.trans_id)

left join ap on (ap.id = ac.trans_id)

left join gl on (gl.id = ac.trans_id)

where ac.transdate >= ? AND ac.transdate <= ?

group by trans_id,ar.invnumber,ap.invnumber,gl.reference

having sum(ac.amount) != 0;|;

my $acs = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);

if (@$acs) {

$self->tester->ok(0, "Es gibt unausgeglichene acc_trans-Transaktionen:");

for my $ac (@{ $acs }) {

$self->tester->diag("trans_id: $ac->{trans_id}, amount = $ac->{amount}, ar: $ac->{ar} ap: $ac->{ap} gl: $ac->{gl}");

}

} else {

$self->tester->ok(1, "Alle acc_trans Transaktionen ergeben in Summe 0, keine unausgeglichenen Transaktionen");

}

}

sub check_verwaiste_acc_trans_eintraege {

my ($self) = @_;

my $query = qq|

select trans_id,amount,accno,description from acc_trans a

left join chart c on (c.id = a.chart_id)

where trans_id not in (select id from gl union select id from ar union select id from ap order by id)

and a.transdate >= ? and a.transdate <= ? ;|;

my $verwaiste_acs = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);

if (@$verwaiste_acs) {

$self->tester->ok(0, "Es gibt verwaiste acc-trans Einträge! (wo ar/ap/gl-Eintrag fehlt)");

$self->tester->diag($_) for @$verwaiste_acs;

} else {

$self->tester->ok(1, "Keine verwaisten acc-trans Einträge (wo ar/ap/gl-Eintrag fehlt)");

}

}

my $query = qq|

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)"); }

my ($self) = @_;

my $query = qq|

left join ar a on (a.id = i.trans_id)

where a.transdate >= ? and a.transdate <= ?;|;

my ($netamount_laut_invoice) = selectfirst_array_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);

# 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) {

$self->tester->diag("Netto-Summe laut Verkaufsbericht (invoice): $netamount_laut_invoice");

$self->tester->diag("Netto-Summe laut Verkauf -> Berichte -> Rechnungen: $netamount_laut_ar");

}

}

sub check_invnumbers_unique {

my ($self) = @_;

my $query = qq| select invnumber,count(invnumber) as count from ar

where transdate >= ? and transdate <= ?

group by invnumber

having count(invnumber) > 1; |;

my $non_unique_invnumbers = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);

if (@$non_unique_invnumbers) {

$self->tester->ok(0, "Es gibt doppelte Rechnungsnummern");

for my $invnumber (@{ $non_unique_invnumbers }) {

$self->tester->diag("invnumber: $invnumber->{invnumber} $invnumber->{count}x");

}

} else {

$self->tester->ok(1, "Alle Rechnungsnummern sind eindeutig");

}

}

sub check_summe_stornobuchungen {

my ($self) = @_;

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 {

my ($self) = @_;

my $query = qq|

select invnumber,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 accpaid ,

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

from ar

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

my $paid_diffs_ar = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);

my $errors = scalar grep { $_->{diff} != 0 } @$paid_diffs_ar;

$self->tester->ok(!$errors, "Vergleich ar paid mit acc_trans AR_paid");

for my $paid_diff_ar (@{ $paid_diffs_ar }) {

next if $paid_diff_ar->{diff} == 0;

$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}");

}

}

sub check_ap_paid {

my ($self) = @_;

my $query = qq|

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

my $paid_diffs_ap = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);

my $errors = scalar grep { $_->{diff} != 0 } @$paid_diffs_ap;

$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;

}

sub check_ar_overpayments {

my ($self) = @_;

my $query = qq|

select invnumber,paid,amount,transdate,c.customernumber,c.name from ar left join customer c on (ar.customer_id = c.id)

my $overpaids_ar = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);

my $correct = 0 == @$overpaids_ar;

$self->tester->ok($correct, "Keine Überzahlungen laut ar.paid");

for my $overpaid_ar (@{ $overpaids_ar }) {

$self->tester->diag("ar invnumber: $overpaid_ar->{invnumber} : paid: $overpaid_ar->{paid} amount= $overpaid_ar->{amount} transdate = $overpaid_ar->{transdate}");

}

}

sub check_ap_overpayments {

my ($self) = @_;

my $query = qq|

select invnumber,paid,amount,transdate,vc.vendornumber,vc.name from ap left join vendor vc on (ap.vendor_id = vc.id)

my $overpaids_ap = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);

my $correct = 0 == @$overpaids_ap;

$self->tester->ok($correct, "Überzahlungen laut ap.paid:");

for my $overpaid_ap (@{ $overpaids_ap }) {

$self->tester->diag("ap invnumber: $overpaid_ap->{invnumber} : paid: $overpaid_ap->{paid} amount= $overpaid_ap->{amount} transdate = $overpaid_ap->{transdate}");

}

}

sub check_paid_stornos {

my ($self) = @_;

my $query = qq|

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"

WHERE paid != amount

AND ar.storno

AND (ar.customer_id = customer.id)

AND ar.transdate >= ? and ar.transdate <= ?

$self->tester->ok(0 == @$paid_stornos, "Keine bezahlten Stornos");

for my $paid_storno (@{ $paid_stornos }) {

$self->tester->diag("invnumber: $paid_storno->{invnumber} offen: $paid_storno->{open}");

}

}

sub check_stornos_ohne_partner {

my ($self) = @_;

my $query = qq|

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)

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)

AND ap.transdate >= ? and ap.transdate <= ?|;

$self->fromdate, $self->todate);

$self->tester->ok(@$stornos_ohne_partner == 0, 'Es sollte keine Stornos ohne Partner geben');

if (@$stornos_ohne_partner) {

my $stornoheader = 0;

for my $storno (@{ $stornos_ohne_partner }) {

if (!$stornoheader++) {

$self->tester->diag(join "\t", keys %$storno);

}

$self->tester->diag(join "\t", map { $storno->{$_} } keys %$storno);

}

}

sub check_overpayments {

my ($self) = @_;

# Vergleich ar.paid und das was laut acc_trans bezahlt wurde

# "als bezahlt markieren" ohne sauberes Ausbuchen führt zu Differenzen bei offenen Forderungen

# prüft weitere Zahlungen und Buchungen über trans_id (kein Zeitfilter)

invnumber,customernumber,name,ar.transdate,ar.datepaid,

amount,

amount-paid as "open via ar",

paid as "paid via ar",

WHERE chart_link ilike '%paid%' AND acc_trans.trans_id=ar.id),0) as "paid via acc_trans"

WHERE

ar.storno IS FALSE

my $count_overpayments = scalar grep {

$_->{"paid via ar"} != $_->{"paid via acc_trans"}

|| ( $_->{"amount"} - $_->{"paid via acc_trans"} != $_->{"open via ar"}

&& $_->{"paid via ar"} != $_->{"paid via acc_trans"} )

} @$invoices;

$self->tester->ok($count_overpayments == 0, 'Vergleich ar.paid und das was laut acc_trans bezahlt wurde');

if ($count_overpayments) {

for my $invoice (@{ $invoices }) {

if ($invoice->{"paid via ar"} != $invoice->{"paid via acc_trans"}) {

$self->tester->diag("datepaid = $invoice->{datepaid})");

}

$self->tester->diag("amount - paid_via_acc_trans != open_via_ar");

# nothing wrong

}

}

}

}

sub calc_saldenvortraege {

my ($self) = @_;

my $saldenvortragskonto = '9000';

# Saldo Saldenvortragskonto 9000 am Jahresanfang

my $query = qq|

select sum(amount) from acc_trans where chart_id = (select id from chart where accno = ?) and transdate <= ?|;

my ($saldo_9000_jahresanfang) = selectfirst_array_query($::form, $self->dbh, $query, $saldenvortragskonto, DateTime->new(day => 1, month => 1, year => DateTime->today->year));

$self->tester->diag("Saldo 9000 am 01.01.@{[DateTime->today->year]}: @{[ $saldo_9000_jahresanfang * 1 ]} (sollte 0 sein)");

# Saldo Saldenvortragskonto 9000 am Jahresende

$query = qq|

select sum(amount) from acc_trans where chart_id = (select id from chart where accno = ?) and transdate <= ?|;

my ($saldo_9000_jahresende) = selectfirst_array_query($::form, $self->dbh, $query, $saldenvortragskonto, DateTime->new(day => 31, month => 12, year => DateTime->today->year));

$self->tester->diag("Saldo $saldenvortragskonto am 31.12.@{[DateTime->today->year]}: @{[ $saldo_9000_jahresende * 1 ]} (sollte 0 sein)");

}

my ($self) = @_;

my $accounts_without_tk = selectall_hashref_query($::form, $self->dbh, $query);

$self->tester->ok(0, "Folgende Konten haben keinen gültigen Steuerschlüssel:");

for my $account_without_tk (@{ $accounts_without_tk } ) {

$self->tester->diag("Kontonummer: $account_without_tk->{accno} Beschreibung: $account_without_tk->{description}");

}

$self->tester->ok(1, "Jedes Konto hat einen gültigen Steuerschlüssel!");

}

}

my ($self) = @_;

my $query = qq|

from acc_trans ac left join ar on (ac.trans_id = ar.id)

AND ac.transdate >= ? AND ac.transdate <= ?

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 {

}

}

sub check_ap_acc_trans_amount {

my ($self) = @_;

my $query = qq|

from acc_trans ac left join ap on (ac.trans_id = ap.id)

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)

if ( scalar @{ $missing_tax_bookings } > 0 ) {

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}.

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.");

}

}

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}