SelfTests: check_paid_ap ID in E-Mail-Bericht übergeben -> einfachere Analyse

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

diff --git a/SL/BackgroundJob/SelfTest/Transactions.pm b/SL/BackgroundJob/SelfTest/Transactions.pm
index 0333ec9b36588bf93be6c597ca77c2e62c1e21ae..a05ecd741f572596930073008e9a9b9aab9cdc35 100644 (file)
--- a/SL/BackgroundJob/SelfTest/Transactions.pm
+++ b/SL/BackgroundJob/SelfTest/Transactions.pm
@@ -1,4 +1,4 @@
-ackage SL::BackgroundJob::SelfTest::Transactions;
+package SL::BackgroundJob::SelfTest::Transactions;

 
 use utf8;
 use strict;
 
 use utf8;
 use strict;


@@ -15,7 +15,7 @@ sub run {
 
   $self->_setup;
 
 
   $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_konten_mit_saldo_nicht_in_guv;
   $self->check_bilanzkonten_mit_pos_eur;


@@ -37,18 +37,18 @@ sub run {
   $self->check_every_account_with_taxkey;
   $self->calc_saldenvortraege;
   $self->check_missing_tax_bookings;
   $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 ($self) = @_;
 
   # TODO FIXME calc dates better, unless this is wanted
 }
 
 sub _setup {
   my ($self) = @_;
 
   # TODO FIXME calc dates better, unless this is wanted

-#  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->fromdate(DateTime->new(day => 1, month => 1, year => DateTime->today->year));

   $self->todate($self->fromdate->clone->add(years => 1)->add(days => -1));
   $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);
 }
 
   $self->dbh($::form->get_standard_dbh);
 }
 


@@ -140,9 +140,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 }) {


@@ -199,15 +205,15 @@ sub check_summe_stornobuchungen {
   my ($self) = @_;
 
   my $query = qq|
   my ($self) = @_;
 
   my $query = qq|

-    select sum(amount) from ar a JOIN customer c ON (a.customer_id = c.id)
-    WHERE storno is true
-      AND a.transdate >= ? and a.transdate <= ?|;
+    SELECT sum(amount) from ar a WHERE a.id IN
+      (SELECT id from ap where storno is true
+       AND a.transdate >= ? and a.transdate <= ?)|;

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

-    select sum(amount) from ap a JOIN vendor c ON (a.vendor_id = c.id)
-    WHERE storno is true
-      AND a.transdate >= ? and a.transdate <= ?|;
+    SELECT sum(amount) from ap a WHERE a.id IN
+      (SELECT id from ap where storno is true
+       AND a.transdate >= ? and a.transdate <= ?)|;

   my ($summe_stornobuchungen_ap) = selectfirst_array_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
 
   $self->tester->ok($summe_stornobuchungen_ap == 0, 'Summe aller Einkaufsrechnungen (stornos + stornierte) soll 0 sein');
   my ($summe_stornobuchungen_ap) = selectfirst_array_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
 
   $self->tester->ok($summe_stornobuchungen_ap == 0, 'Summe aller Einkaufsrechnungen (stornos + stornierte) soll 0 sein');


@@ -227,7 +233,7 @@ sub check_ar_paid {
     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
     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

-      AND transdate >= ? and transdate <= ?
+      AND ar.id in (SELECT id from ar where transdate >= ? and transdate <= ?)

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


@@ -246,13 +252,13 @@ sub check_ap_paid {
   my ($self) = @_;
 
   my $query = qq|
   my ($self) = @_;
 
   my $query = qq|

-      select invnumber,paid,
+      select invnumber,paid,id,

             (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 ,
             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
             (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 ,
             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

-       AND transdate >= ? and transdate <= ?
+      AND ap.id in (SELECT id from ap where transdate >= ? and transdate <= ?)

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


@@ -262,7 +268,7 @@ sub check_ap_paid {
   $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;
   $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;

-     $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}");
+     $self->tester->diag("ap invnumber: $paid_diff_ap->{invnumber} : ID :: ID :  $paid_diff_ap->{id}  : paid: $paid_diff_ap->{paid}    acc_paid= $paid_diff_ap->{accpaid}    diff: $paid_diff_ap->{diff}");

   }
 }
 
   }
 }
 


@@ -362,22 +368,23 @@ sub check_overpayments {
 
   # Vergleich ar.paid und das was laut acc_trans bezahlt wurde
   # "als bezahlt markieren" ohne sauberes Ausbuchen führt zu Differenzen bei offenen Forderungen
 
   # Vergleich ar.paid und das was laut acc_trans bezahlt wurde
   # "als bezahlt markieren" ohne sauberes Ausbuchen führt zu Differenzen bei offenen Forderungen

-  # geht nur auf wenn acc_trans Zahlungseingänge auch im Untersuchungszeitraum lagen
-  # Stornos werden rausgefiltert
+  # Berücksichtigt Zahlungseingänge im Untersuchungszeitraums und
+  # prüft weitere Zahlungen und Buchungen über trans_id (kein Zeitfilter)
+

   my $query = qq|
     SELECT
     invnumber,customernumber,name,ar.transdate,ar.datepaid,
     amount,
     amount-paid as "open via ar",
     paid as "paid via ar",
   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"
+    coalesce((SELECT sum(amount)*-1 FROM acc_trans
+      WHERE chart_link ilike '%paid%' AND acc_trans.trans_id=ar.id),0) as "paid via acc_trans"

     FROM ar left join customer c on (c.id = ar.customer_id)
     WHERE
      ar.storno IS FALSE
     FROM ar left join customer c on (c.id = ar.customer_id)
     WHERE
      ar.storno IS FALSE

-     AND transdate >= ? AND transdate <= ?|;
+     AND ar.id in (SELECT trans_id from acc_trans where transdate >= ? AND transdate <= ? AND chart_link ilike '%paid%')|;

 
 

-  my $invoices = selectall_hashref_query($::form, $self->dbh, $query, $self->todate, $self->fromdate, $self->todate);
+  my $invoices = selectall_hashref_query($::form, $self->dbh, $query, $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"}


@@ -390,7 +397,7 @@ sub check_overpayments {
   if ($count_overpayments) {
     for my $invoice (@{ $invoices }) {
       if ($invoice->{"paid via ar"} != $invoice->{"paid via acc_trans"}) {
   if ($count_overpayments) {
     for my $invoice (@{ $invoices }) {
       if ($invoice->{"paid via ar"} != $invoice->{"paid via acc_trans"}) {

-        $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!)");
+        $self->tester->diag("Rechnung: $invoice->{invnumber}, Kunde $invoice->{name}  Nebenbuch-Bezahlwert: (@{[ $invoice->{'paid via ar'} * 1 ]}) !=   Hauptbuch-Bezahlwert:  (@{[ $invoice->{'paid via acc_trans'} * 1 ]}) (at least until transdate!)");

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


@@ -471,9 +478,9 @@ sub check_ap_acc_trans_amount {
   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)
   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 <= ?
           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 $ap_amount_not_ac_amount = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
 


@@ -498,25 +505,121 @@ sub check_missing_tax_bookings {
   # 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)
   # 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 <> 0 AND trans_id NOT IN
+                    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 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 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 ) {
                     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 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}.
 
     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.");
   }
 }
 
     }
   } 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.trans_id in (SELECT trans_id from acc_trans ac where 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.trans_id in (SELECT trans_id from acc_trans ac where 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, "Kein Bezahl-Datum ohne Bezahl-Wert und ohne wirkliche Zahlungen gefunden (arap.datepaid, arap.paid konsistent).");
+  }
+}

 
 1;
 
 
 1;