SelfTests: Weitere Randfälle zumindestens in der Analyse-Ausgabe erwähnen

[mfinanz.git] / SL / BackgroundJob / SelfTest / Transactions.pm

diff --git a/SL/BackgroundJob/SelfTest/Transactions.pm b/SL/BackgroundJob/SelfTest/Transactions.pm
index b3093f97a8a5d5e996c28977a2d82e1480c14ec9..1f706e540c5bbcc51303445f2102d4c21da0b4d8 100644 (file)
--- a/SL/BackgroundJob/SelfTest/Transactions.pm
+++ b/SL/BackgroundJob/SelfTest/Transactions.pm
@@ -15,7 +15,7 @@ sub run {
 
   $self->_setup;
 
 
   $self->_setup;
 

-  $self->tester->plan(tests => 18);
+  $self->tester->plan(tests => 23);

 
   $self->check_konten_mit_saldo_nicht_in_guv;
   $self->check_bilanzkonten_mit_pos_eur;
 
   $self->check_konten_mit_saldo_nicht_in_guv;
   $self->check_bilanzkonten_mit_pos_eur;


@@ -23,6 +23,7 @@ sub run {
   $self->check_verwaiste_acc_trans_eintraege;
   $self->check_verwaiste_invoice_eintraege;
   $self->check_ar_acc_trans_amount;
   $self->check_verwaiste_acc_trans_eintraege;
   $self->check_verwaiste_invoice_eintraege;
   $self->check_ar_acc_trans_amount;

+  $self->check_ap_acc_trans_amount;

   $self->check_netamount_laut_invoice_ar;
   $self->check_invnumbers_unique;
   $self->check_summe_stornobuchungen;
   $self->check_netamount_laut_invoice_ar;
   $self->check_invnumbers_unique;
   $self->check_summe_stornobuchungen;


@@ -35,6 +36,10 @@ sub run {
   $self->check_overpayments;
   $self->check_every_account_with_taxkey;
   $self->calc_saldenvortraege;
   $self->check_overpayments;
   $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;

 }
 
 sub _setup {
 }
 
 sub _setup {


@@ -43,7 +48,6 @@ sub _setup {
   # TODO FIXME calc dates better, unless this is wanted
   $self->fromdate(DateTime->new(day => 1, month => 1, year => DateTime->today->year));
   $self->todate($self->fromdate->clone->add(years => 1)->add(days => -1));
   # TODO FIXME calc dates better, unless this is wanted
   $self->fromdate(DateTime->new(day => 1, month => 1, year => DateTime->today->year));
   $self->todate($self->fromdate->clone->add(years => 1)->add(days => -1));

-

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


@@ -135,9 +139,15 @@ sub check_verwaiste_invoice_eintraege {
   my ($self) = @_;
   my $query = qq|
      select * from invoice i
   my ($self) = @_;
   my $query = qq|
      select * from invoice i

-      where trans_id not in (select id from ar union select id from ap order by id) |;
+      where trans_id not in (select id from ar WHERE ar.transdate >=? AND ar.transdate <=?
+                             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);
+

 
 

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

   if (@$verwaiste_invoice) {
      $self->tester->ok(0, "Es gibt verwaiste invoice Einträge! (wo ar/ap-Eintrag fehlt)");
      for my $invoice ( @{ $verwaiste_invoice }) {
   if (@$verwaiste_invoice) {
      $self->tester->ok(0, "Es gibt verwaiste invoice Einträge! (wo ar/ap-Eintrag fehlt)");
      for my $invoice ( @{ $verwaiste_invoice }) {


@@ -159,7 +169,10 @@ sub check_netamount_laut_invoice_ar {
   $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);
 
   $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) {
 
   $self->tester->ok($correct, "Summe laut Verkaufsbericht sollte gleich Summe aus Verkauf -> Berichte -> Rechnungen sein");
   if (!$correct) {


@@ -263,9 +276,9 @@ sub check_ar_overpayments {
 
   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 $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 <= ?
        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 $overpaids_ar =  selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
 


@@ -282,9 +295,9 @@ sub check_ap_overpayments {
 
   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 $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 <= ?
       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 $overpaids_ap =  selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
 


@@ -300,13 +313,15 @@ sub check_paid_stornos {
   my ($self) = @_;
 
   my $query = qq|
   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 <= ?
     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");
   my $paid_stornos = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
 
   $self->tester->ok(0 == @$paid_stornos, "Keine bezahlten Stornos");


@@ -323,19 +338,20 @@ sub check_stornos_ohne_partner {
     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)
     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)
     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->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 }) {
   }
   my $stornoheader = 0;
   for my $storno (@{ $stornos_ohne_partner }) {


@@ -354,19 +370,19 @@ sub check_overpayments {
   # geht nur auf wenn acc_trans Zahlungseingänge auch im Untersuchungszeitraum lagen
   # Stornos werden rausgefiltert
   my $query = qq|
   # 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 $count_overpayments = scalar grep {
        $_->{"paid via ar"} != $_->{"paid via acc_trans"}


@@ -433,10 +449,13 @@ sub check_ar_acc_trans_amount {
   my ($self) = @_;
 
   my $query = qq|
   my ($self) = @_;
 
   my $query = qq|

-          select ar.invnumber, ar.netamount, ac.amount
-           from ar left join acc_trans ac on (ac.trans_id = ar.id) where ac.chart_link like 'AR_amount%' AND ac.amount <> ar.netamount|;
+          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%'
+          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:");
 
   if ( scalar @{ $ar_amount_not_ac_amount } > 0 ) {
     $self->tester->ok(0, "Folgende Ausgangsrechnungen haben einen falschen Netto-Wert im Nebenbuch:");


@@ -445,12 +464,135 @@ sub check_ar_acc_trans_amount {
       $self->tester->diag("Rechnungsnummer: $ar_ac_amount_nok->{invnumber} Hauptbuch-Wert: $ar_ac_amount_nok->{amount}
                             Nebenbuch-Wert: $ar_ac_amount_nok->{netamount}");
     }
       $self->tester->diag("Rechnungsnummer: $ar_ac_amount_nok->{invnumber} Hauptbuch-Wert: $ar_ac_amount_nok->{amount}
                             Nebenbuch-Wert: $ar_ac_amount_nok->{netamount}");
     }

+  } else {
+    $self->tester->ok(1, "Hauptbuch-Nettowert und Debitoren-Nebenbuch-Nettowert  stimmen überein.");
+  }
+
+}
+
+sub check_ap_acc_trans_amount {
+  my ($self) = @_;
+
+  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 '%AP_amount%'
+          AND ac.transdate >= ? AND ac.transdate <= ?
+          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);
+
+  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)
+                    WHERE taxkey NOT IN (SELECT taxkey from tax where rate=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 NOT IN (SELECT taxkey from tax where rate=0)))
+                    AND transdate >= ? AND transdate <= ?|;
+
+  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 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. 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.");
   }
   } 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.");
+  }
+}
+
+

 1;
 
 __END__
 1;
 
 __END__