Weitere Verbesserungen am asynchronen Import.
[kivitendo-erp.git] / SL / BackgroundJob / SelfTest / Transactions.pm
1 package SL::BackgroundJob::SelfTest::Transactions;
2
3 use utf8;
4 use strict;
5 use parent qw(SL::BackgroundJob::SelfTest::Base);
6
7 use SL::DBUtils;
8
9 use Rose::Object::MakeMethods::Generic (
10   scalar => [ qw(dbh fromdate todate) ],
11 );
12
13 sub run {
14   my ($self) = @_;
15
16   $self->_setup;
17
18   $self->tester->plan(tests => 14);
19
20   $self->check_konten_mit_saldo_nicht_in_guv;
21   $self->check_balanced_individual_transactions;
22   $self->check_verwaiste_acc_trans_eintraege;
23   $self->check_netamount_laut_invoice_ar;
24   $self->check_invnumbers_unique;
25   $self->check_summe_stornobuchungen;
26   $self->check_ar_paid;
27   $self->check_ap_paid;
28   $self->check_ar_overpayments;
29   $self->check_ap_overpayments;
30   $self->check_paid_stornos;
31   $self->check_stornos_ohne_partner;
32   $self->check_overpayments;
33   $self->calc_saldenvortraege;
34 }
35
36 sub _setup {
37   my ($self) = @_;
38
39   # TODO FIXME calc dates better, unless this is wanted
40   $self->fromdate(DateTime->new(day => 1, month => 1, year => DateTime->today->year));
41   $self->todate($self->fromdate->clone->add(years => 1)->add(days => -1));
42
43   $self->dbh($::form->get_standard_dbh);
44 }
45
46 sub check_konten_mit_saldo_nicht_in_guv {
47   my ($self) = @_;
48
49   my $query = qq|
50     SELECT c.accno, c.description, c.category, SUM(a.amount) AS Saldo
51     FROM chart c,
52          acc_trans a
53     WHERE c.id = a.chart_id
54      and  (c.category like 'I' or c.category like 'E')
55      and  amount != 0
56      and  pos_eur is null
57          and  a.transdate >= ? and a.transdate <= ?
58     GROUP BY c.accno,c.description,c.category,c.pos_bilanz,c.pos_eur
59     ORDER BY c.accno|;
60
61   my $konten_nicht_in_guv =  selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
62
63   my $correct = 0 == scalar grep { $_->{Saldo} } @$konten_nicht_in_guv;
64
65   $self->tester->ok($correct, "Erfolgskonten mit Saldo nicht in GuV (Saldenvortragskonten können ignoriert werden, sollten aber 0 sein)");
66   if (!$correct) {
67     for my $konto (@$konten_nicht_in_guv) {
68       $self->tester->diag($konto);
69     }
70   }
71 }
72
73 sub check_balanced_individual_transactions {
74   my ($self) = @_;
75
76   my $query = qq|
77     select sum(ac.amount) as amount,trans_id,ar.invnumber as ar,ap.invnumber as ap,gl.reference as gl
78       from acc_trans ac
79       left join ar on (ar.id = ac.trans_id)
80       left join ap on (ap.id = ac.trans_id)
81       left join gl on (gl.id = ac.trans_id)
82     where ac.transdate >= ? AND ac.transdate <= ?
83     group by trans_id,ar.invnumber,ap.invnumber,gl.reference
84     having sum(ac.amount) != 0;|;
85
86   my $acs = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
87   if (@$acs) {
88     $self->tester->ok(0, "Es gibt unausgeglichene acc_trans-Transaktionen:");
89     for my $ac (@{ $acs }) {
90       $self->tester->diag("trans_id: $ac->{trans_id},  amount = $ac->{amount}, ar: $ac->{ar} ap: $ac->{ap} gl: $ac->{gl}");
91     }
92   } else {
93     $self->tester->ok(1, "Alle acc_trans Transaktionen ergeben in Summe 0, keine unausgeglichenen Transaktionen");
94   }
95 }
96
97 sub check_verwaiste_acc_trans_eintraege {
98   my ($self) = @_;
99
100   my $query = qq|
101       select trans_id,amount,accno,description from acc_trans a
102     left join chart c on (c.id = a.chart_id)
103     where trans_id not in (select id from gl union select id from ar union select id from ap order by id)
104       and a.transdate >= ? and a.transdate <= ? ;|;
105
106   my $verwaiste_acs = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
107   if (@$verwaiste_acs) {
108      $self->tester->ok(0, "Es gibt verwaiste acc-trans Einträge! (wo ar/ap/gl-Eintrag fehlt)");
109      $self->tester->diag($_) for @$verwaiste_acs;
110   } else {
111      $self->tester->ok(1, "Keine verwaisten acc-trans Einträge (wo ar/ap/gl-Eintrag fehlt)");
112   }
113 }
114
115 sub check_netamount_laut_invoice_ar {
116   my ($self) = @_;
117   my $query = qq|
118     select sum(round(cast(i.qty*(i.fxsellprice * (1-i.discount)) as numeric), 2))
119     from invoice i
120     left join ar a on (a.id = i.trans_id)
121     where a.transdate >= ? and a.transdate <= ?;|;
122   my ($netamount_laut_invoice) =  selectfirst_array_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
123
124   $query = qq| select sum(netamount) from ar where transdate >= ? and transdate <= ?; |;
125   my ($netamount_laut_ar) =  selectfirst_array_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
126
127   my $correct = $netamount_laut_invoice - $netamount_laut_ar == 0;
128
129   $self->tester->ok($correct, "Summe laut Verkaufsbericht sollte gleich Summe aus Verkauf -> Berichte -> Rechnungen sein");
130   if (!$correct) {
131     $self->tester->diag("Netto-Summe laut Verkaufsbericht (invoice): $netamount_laut_invoice");
132     $self->tester->diag("Netto-Summe laut Verkauf -> Berichte -> Rechnungen: $netamount_laut_ar");
133   }
134 }
135
136 sub check_invnumbers_unique {
137   my ($self) = @_;
138
139   my $query = qq| select  invnumber,count(invnumber) as count from ar
140                where transdate >= ? and transdate <= ?
141                group by invnumber
142                having count(invnumber) > 1; |;
143   my $non_unique_invnumbers =  selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
144
145   if (@$non_unique_invnumbers) {
146     $self->tester->ok(0, "Es gibt doppelte Rechnungsnummern");
147     for my $invnumber (@{ $non_unique_invnumbers }) {
148       $self->tester->diag("invnumber: $invnumber->{invnumber}    $invnumber->{count}x");
149     }
150   } else {
151     $self->tester->ok(1, "Alle Rechnungsnummern sind eindeutig");
152   }
153 }
154
155 sub check_summe_stornobuchungen {
156   my ($self) = @_;
157
158   my $query = qq|
159     select sum(amount) from ar a JOIN customer c ON (a.customer_id = c.id)
160     WHERE storno is true
161       AND a.transdate >= ? and a.transdate <= ?|;
162   my ($summe_stornobuchungen_ar) = selectfirst_array_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
163
164   $query = qq|
165     select sum(amount) from ap a JOIN vendor c ON (a.vendor_id = c.id)
166     WHERE storno is true
167       AND a.transdate >= ? and a.transdate <= ?|;
168   my ($summe_stornobuchungen_ap) = selectfirst_array_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
169
170   $self->tester->ok($summe_stornobuchungen_ap == 0, 'Summe aller Einkaufsrechnungen (stornos + stornierte) soll 0 sein');
171   $self->tester->ok($summe_stornobuchungen_ar == 0, 'Summe aller Verkaufsrechnungen (stornos + stornierte) soll 0 sein');
172   $self->tester->diag("Summe Einkaufsrechnungen (ar): $summe_stornobuchungen_ar") if $summe_stornobuchungen_ar;
173   $self->tester->diag("Summe Einkaufsrechnungen (ap): $summe_stornobuchungen_ap") if $summe_stornobuchungen_ap;
174 }
175
176 sub check_ar_paid {
177   my ($self) = @_;
178
179   my $query = qq|
180       select invnumber,paid,
181            (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 ,
182            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
183     from ar
184     where
185           (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
186             AND storno is false
187       AND transdate >= ? and transdate <= ?
188     order by diff |;
189
190   my $paid_diffs_ar = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
191
192   my $errors = scalar grep { $_->{diff} != 0 } @$paid_diffs_ar;
193
194   $self->tester->ok(!$errors, "Vergleich ar paid mit acc_trans AR_paid");
195
196   for my $paid_diff_ar (@{ $paid_diffs_ar }) {
197     next if $paid_diff_ar->{diff} == 0;
198     $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}");
199   }
200 }
201
202 sub check_ap_paid {
203   my ($self) = @_;
204
205   my $query = qq|
206       select invnumber,paid,
207             (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 ,
208             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
209      from ap
210      where
211            (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
212        AND transdate >= ? and transdate <= ?
213      order by diff |;
214
215   my $paid_diffs_ap = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
216
217   my $errors = scalar grep { $_->{diff} != 0 } @$paid_diffs_ap;
218
219   $self->tester->ok(!$errors, "Vergleich ap paid mit acc_trans AP_paid");
220   for my $paid_diff_ap (@{ $paid_diffs_ap }) {
221      next if $paid_diff_ap->{diff} == 0;
222      $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}");
223   }
224 }
225
226 sub check_ar_overpayments {
227   my ($self) = @_;
228
229   my $query = qq|
230        select invnumber,paid,amount,transdate,c.customernumber,c.name from ar left join customer c on (ar.customer_id = c.id)
231      where abs(paid) > abs(amount)
232        AND transdate >= ? and transdate <= ?
233          order by invnumber;|;
234
235   my $overpaids_ar =  selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
236
237   my $correct = 0 == @$overpaids_ar;
238
239   $self->tester->ok($correct, "Keine Überzahlungen laut ar.paid");
240   for my $overpaid_ar (@{ $overpaids_ar }) {
241     $self->tester->diag("ar invnumber: $overpaid_ar->{invnumber} : paid: $overpaid_ar->{paid}    amount= $overpaid_ar->{amount}  transdate = $overpaid_ar->{transdate}");
242   }
243 }
244
245 sub check_ap_overpayments {
246   my ($self) = @_;
247
248   my $query = qq|
249       select invnumber,paid,amount,transdate,vc.vendornumber,vc.name from ap left join vendor vc on (ap.vendor_id = vc.id)
250     where abs(paid) > abs(amount)
251       AND transdate >= ? and transdate <= ?
252         order by invnumber;|;
253
254   my $overpaids_ap =  selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
255
256   my $correct = 0 == @$overpaids_ap;
257
258   $self->tester->ok($correct, "Überzahlungen laut ap.paid:");
259   for my $overpaid_ap (@{ $overpaids_ap }) {
260     $self->tester->diag("ap invnumber: $overpaid_ap->{invnumber} : paid: $overpaid_ap->{paid}    amount= $overpaid_ap->{amount}  transdate = $overpaid_ap->{transdate}");
261   }
262 }
263
264 sub check_paid_stornos {
265   my ($self) = @_;
266
267   my $query = qq|
268     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"
269     FROM ar, customer
270     WHERE paid != amount
271       AND ar.storno
272       AND (ar.customer_id = customer.id)
273       AND ar.transdate >= ? and ar.transdate <= ?
274     GROUP BY ar.invnumber;|;
275   my $paid_stornos = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
276
277   $self->tester->ok(0 == @$paid_stornos, "Keine bezahlten Stornos");
278   for my $paid_storno (@{ $paid_stornos }) {
279     $self->tester->diag("invnumber: $paid_storno->{invnumber}   offen: $paid_storno->{open}");
280   }
281 }
282
283 sub check_stornos_ohne_partner {
284   my ($self) = @_;
285
286   my $query = qq|
287     SELECT (SELECT cast ('ar' as text)) as invoice ,ar.id,invnumber,storno,amount,transdate,type,customernumber as cv_number
288     FROM ar
289     LEFT JOIN customer c on (c.id = ar.customer_id)
290     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)
291     UNION
292     SELECT (SELECT cast ('ap' as text)) as invoice,ap.id,invnumber,storno,amount,transdate,type,vendornumber as cv_number
293     FROM ap
294     LEFT JOIN vendor v on (v.id = ap.vendor_id)
295     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);
296   |;
297  
298   my $stornos_ohne_partner =  selectall_hashref_query($::form, $self->dbh, $query);
299
300   $self->tester->ok(@$stornos_ohne_partner == 0, 'Es sollte keine Stornos ohne Partner geben');
301   if (@$stornos_ohne_partner) {
302     $self->tester->diag("Stornos ohne Partner:   (kann passieren wenn Stornorechnung außerhalb Zeitraum liegt)");
303     $self->tester->diag("gilt aber trotzdem als paid zu dem Zeitpunkt, oder?");
304   }
305   my $stornoheader = 0;
306   for my $storno (@{ $stornos_ohne_partner }) {
307     if (!$stornoheader++) {
308       $self->tester->diag(join "\t", keys %$storno);
309     }
310     $self->tester->diag(join "\t", map { $storno->{$_} } keys %$storno);
311   }
312 }
313
314 sub check_overpayments {
315   my ($self) = @_;
316
317   # Vergleich ar.paid und das was laut acc_trans bezahlt wurde
318   # "als bezahlt markieren" ohne sauberes Ausbuchen führt zu Differenzen bei offenen Forderungen
319   # geht nur auf wenn acc_trans Zahlungseingänge auch im Untersuchungszeitraum lagen
320   # Stornos werden rausgefiltert
321   my $query = qq|
322 SELECT
323 invnumber,customernumber,name,ar.transdate,ar.datepaid,
324 amount,
325 amount-paid as "open via ar",
326 paid as "paid via ar",
327 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"
328 FROM ar left join customer c on (c.id = ar.customer_id)
329 WHERE
330  (ar.storno IS FALSE)
331  AND (transdate <= ? )
332 ;|;
333
334   my $invoices = selectall_hashref_query($::form, $self->dbh, $query, $self->todate, $self->todate);
335
336   my $count_overpayments = scalar grep {
337        $_->{"paid via ar"} != $_->{"paid via acc_trans"}
338     || (    $_->{"amount"} - $_->{"paid via acc_trans"} != $_->{"open via ar"}
339          && $_->{"paid via ar"} != $_->{"paid via acc_trans"} )
340   } @$invoices;
341
342   $self->tester->ok($count_overpayments == 0, 'Vergleich ar.paid und das was laut acc_trans bezahlt wurde');
343
344   if ($count_overpayments) {
345     for my $invoice (@{ $invoices }) {
346       if ($invoice->{"paid via ar"} != $invoice->{"paid via acc_trans"}) {
347         $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!)");
348         if (defined $invoice->{datepaid}) {
349           $self->tester->diag("datepaid = $invoice->{datepaid})");
350         }
351         $self->tester->diag("Überzahlung!") if $invoice->{"paid via acc_trans"} > $invoice->{amount};
352       } elsif ( $invoice->{"amount"} - $invoice->{"paid via acc_trans"} != $invoice->{"open via ar"} && $invoice->{"paid via ar"} != $invoice->{"paid via acc_trans"}) {
353         $self->tester->diag("amount - paid_via_acc_trans !=  open_via_ar");
354         $self->tester->diag("Überzahlung!") if $invoice->{"paid via acc_trans"} > $invoice->{amount};
355       } else {
356         # nothing wrong
357       }
358     }
359   }
360 }
361
362 sub calc_saldenvortraege {
363   my ($self) = @_;
364
365   my $saldenvortragskonto = '9000';
366
367   # Saldo Saldenvortragskonto 9000 am Jahresanfang
368   my $query = qq|
369       select sum(amount) from acc_trans where chart_id = (select id from chart where accno = ?) and transdate <= ?|;
370   my ($saldo_9000_jahresanfang) = selectfirst_array_query($::form, $self->dbh, $query, $saldenvortragskonto, DateTime->new(day => 1, month => 1, year => DateTime->today->year));
371   $self->tester->diag("Saldo 9000 am 01.01.@{[DateTime->today->year]}: @{[ $saldo_9000_jahresanfang * 1 ]}    (sollte 0 sein)");
372
373     # Saldo Saldenvortragskonto 9000 am Jahresende
374   $query = qq|
375       select sum(amount) from acc_trans where chart_id = (select id from chart where accno = ?) and transdate <= ?|;
376   my ($saldo_9000_jahresende) = selectfirst_array_query($::form, $self->dbh, $query, $saldenvortragskonto, DateTime->new(day => 31, month => 12, year => DateTime->today->year));
377   $self->tester->diag("Saldo $saldenvortragskonto am 31.12.@{[DateTime->today->year]}: @{[ $saldo_9000_jahresende * 1 ]}    (sollte 0 sein)");
378 }
379
380 1;
381
382 __END__
383
384 =encoding utf-8
385
386 =head1 NAME
387
388 SL::BackgroundJob::SelfTest::Transactions - base tests
389
390 =head1 DESCRIPTION
391
392 Several tests for data integrity.
393
394 =head1 FUNCTIONS
395
396 =head1 BUGS
397
398 =head1 AUTHOR
399
400 G. Richardson E<lt>information@richardson-bueren.deE<gt>
401 Jan Büren E<lt>information@richardson-bueren.deE<gt>
402 Sven Schoeling E<lt>s.schoeling@linet-services.deE<gt>
403
404 =cut
405