Revision e35b5c40
Von Jan Büren vor mehr als 9 Jahren hinzugefügt
| SL/BackgroundJob/SelfTest/Transactions.pm | ||
|---|---|---|
|
package SL::BackgroundJob::SelfTest::Transactions;
|
||
|
ackage SL::BackgroundJob::SelfTest::Transactions;
|
||
|
|
||
|
use utf8;
|
||
|
use strict;
|
||
| ... | ... | |
|
my ($self) = @_;
|
||
|
|
||
|
# TODO FIXME calc dates better, unless this is wanted
|
||
|
$self->fromdate(DateTime->new(day => 1, month => 1, year => DateTime->today->year));
|
||
|
# my $year =
|
||
|
#$self->fromdate(DateTime->new(day => 1, month => 1, year => DateTime->today->year));
|
||
|
#$self->todate($self->fromdate->clone->add(years => 1)->add(days => -1));
|
||
|
$self->fromdate(DateTime->new(day => 1, month => 1, year => 2015));
|
||
|
$self->todate($self->fromdate->clone->add(years => 1)->add(days => -1));
|
||
|
|
||
|
$main::lxdebug->message(0, 'hier und dort:' . $self->fromdate . ' mit ' . $self->todate);
|
||
|
$self->dbh($::form->get_standard_dbh);
|
||
|
}
|
||
|
|
||
| ... | ... | |
|
$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) {
|
||
| ... | ... | |
|
|
||
|
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 }) {
|
||
| ... | ... | |
|
# geht nur auf wenn acc_trans Zahlungseingänge auch im Untersuchungszeitraum lagen
|
||
|
# Stornos werden rausgefiltert
|
||
|
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 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 >= ? AND transdate <= ?|;
|
||
|
|
||
|
my $invoices = selectall_hashref_query($::form, $self->dbh, $query, $self->todate, $self->fromdate, $self->todate);
|
||
|
|
||
|
my $count_overpayments = scalar grep {
|
||
|
$_->{"paid via ar"} != $_->{"paid via acc_trans"}
|
||
| ... | ... | |
|
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%' group by invnumber,netamount having sum(ac.amount) <> ar.netamount|;
|
||
|
WHERE ac.chart_link like 'AR_amount%'
|
||
|
AND ac.transdate >= ? AND ac.transdate <= ?
|
||
|
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:");
|
||
| ... | ... | |
|
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%' group by invnumber,netamount having sum(ac.amount) <> ap.netamount*-1|;
|
||
|
WHERE ac.chart_link like 'AR_amount%'
|
||
|
AND ac.transdate >= ? AND ac.transdate <= ?
|
||
|
group by invnumber,netamount having sum(ac.amount) <> ap.netamount*-1|;
|
||
|
|
||
|
my $ap_amount_not_ac_amount = selectall_hashref_query($::form, $self->dbh, $query);
|
||
|
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:");
|
||
| ... | ... | |
|
my $query = qq| select trans_id, chart.accno,transdate from acc_trans left join chart on (chart.id = acc_trans.chart_id)
|
||
|
WHERE taxkey <> 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 <> 0))|;
|
||
|
(SELECT trans_id from acc_trans where taxkey <> 0))
|
||
|
AND transdate >= ? AND transdate <= ?|;
|
||
|
|
||
|
my $missing_tax_bookings = selectall_hashref_query($::form, $self->dbh, $query);
|
||
|
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:");
|
||
Auch abrufbar als: Unified diff
Selftests konsequent auf fromdate -> todate umgestellt
Ferner Kosmetik von hässlichen SQL Formatierungen