X-Git-Url: http://wagnertech.de/gitweb/gitweb.cgi/mfinanz.git/blobdiff_plain/7c6b944f86080cac2bc7723a46622ef4bda2e6f1..31e6d33a8e4ec4cf11a25ad63696948bc55f63bc:/SL/BackgroundJob/SelfTest/Transactions.pm

diff --git a/SL/BackgroundJob/SelfTest/Transactions.pm b/SL/BackgroundJob/SelfTest/Transactions.pm
index 6307ea99f..64c4ad987 100644
--- a/SL/BackgroundJob/SelfTest/Transactions.pm
+++ b/SL/BackgroundJob/SelfTest/Transactions.pm
@@ -15,7 +15,7 @@ sub run {
 
   $self->_setup;
 
-  $self->tester->plan(tests => 18);
+  $self->tester->plan(tests => 20);
 
   $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_ap_acc_trans_amount;
   $self->check_netamount_laut_invoice_ar;
   $self->check_invnumbers_unique;
   $self->check_summe_stornobuchungen;
@@ -35,6 +36,7 @@ sub run {
   $self->check_overpayments;
   $self->check_every_account_with_taxkey;
   $self->calc_saldenvortraege;
+  $self->check_missing_tax_bookings;
 }
 
 sub _setup {
@@ -156,7 +158,7 @@ sub check_netamount_laut_invoice_ar {
     where a.transdate >= ? and a.transdate <= ?;|;
   my ($netamount_laut_invoice) =  selectfirst_array_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
 
-  $query = qq| select sum(netamount) from ar where transdate >= ? and transdate <= ?; |;
+  $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;
@@ -204,7 +206,7 @@ sub check_summe_stornobuchungen {
 
   $self->tester->ok($summe_stornobuchungen_ap == 0, 'Summe aller Einkaufsrechnungen (stornos + stornierte) soll 0 sein');
   $self->tester->ok($summe_stornobuchungen_ar == 0, 'Summe aller Verkaufsrechnungen (stornos + stornierte) soll 0 sein');
-  $self->tester->diag("Summe Einkaufsrechnungen (ar): $summe_stornobuchungen_ar") if $summe_stornobuchungen_ar;
+  $self->tester->diag("Summe Verkaufsrechnungen (ar): $summe_stornobuchungen_ar") if $summe_stornobuchungen_ar;
   $self->tester->diag("Summe Einkaufsrechnungen (ap): $summe_stornobuchungen_ap") if $summe_stornobuchungen_ap;
 }
 
@@ -383,10 +385,10 @@ WHERE
         if (defined $invoice->{datepaid}) {
           $self->tester->diag("datepaid = $invoice->{datepaid})");
         }
-        $self->tester->diag("Überzahlung!") if $invoice->{"paid via acc_trans"} > $invoice->{amount};
+        $self->tester->diag("Überzahlung bei Rechnung: $invoice->{invnumber}") if $invoice->{"paid via acc_trans"} > $invoice->{amount};
       } elsif ( $invoice->{"amount"} - $invoice->{"paid via acc_trans"} != $invoice->{"open via ar"} && $invoice->{"paid via ar"} != $invoice->{"paid via acc_trans"}) {
         $self->tester->diag("amount - paid_via_acc_trans !=  open_via_ar");
-        $self->tester->diag("Überzahlung!") if $invoice->{"paid via acc_trans"} > $invoice->{amount};
+        $self->tester->diag("Überzahlung bei Rechnung: $invoice->{invnumber}") if $invoice->{"paid via acc_trans"} > $invoice->{amount};
       } else {
         # nothing wrong
       }
@@ -433,8 +435,9 @@ sub check_ar_acc_trans_amount {
   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%' group by invnumber,netamount having sum(ac.amount) <> ar.netamount|;
 
   my $ar_amount_not_ac_amount = selectall_hashref_query($::form, $self->dbh, $query);
 
@@ -446,11 +449,61 @@ sub check_ar_acc_trans_amount {
                             Nebenbuch-Wert: $ar_ac_amount_nok->{netamount}");
     }
   } else {
-    $self->tester->ok(1, "Hauptbuch-Nettowert und Nebenbuch-Nettowert stimmen überein.");
+    $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 'AR_amount%' group by invnumber,netamount having sum(ac.amount) <> ap.netamount*-1|;
+
+  my $ap_amount_not_ac_amount = selectall_hashref_query($::form, $self->dbh, $query);
+
+  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 <> 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))|;
+
+  my $missing_tax_bookings = selectall_hashref_query($::form, $self->dbh, $query);
+
+  if ( scalar @{ $missing_tax_bookings } > 0 ) {
+    $self->tester->ok(0, "Folgende Konten weisen Buchungen ohne Steuer 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.");
+    }
+  } else {
+    $self->tester->ok(1, "Hauptbuch-Nettowert und Nebenbuch-Nettowert stimmen überein.");
+  }
+}
+
+
 1;
 
 __END__