X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FBackgroundJob%2FSelfTest%2FTransactions.pm;h=d953966c6cf0da57a9f09c870f2139da2d875b75;hb=452e195fa7d3ecf6cb625938d8016dd04bcc33ce;hp=1f706e540c5bbcc51303445f2102d4c21da0b4d8;hpb=13f40529221ea3f6d8afbd5a047be7df7fa2a93c;p=kivitendo-erp.git

diff --git a/SL/BackgroundJob/SelfTest/Transactions.pm b/SL/BackgroundJob/SelfTest/Transactions.pm
index 1f706e540..d953966c6 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 => 23);
+  $self->tester->plan(tests => 26);
 
   $self->check_konten_mit_saldo_nicht_in_guv;
   $self->check_bilanzkonten_mit_pos_eur;
@@ -40,6 +40,9 @@ sub run {
   $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;
+  $self->check_orphaned_reconciliated_links;
+  $self->check_recommended_client_settings;
 }
 
 sub _setup {
@@ -204,15 +207,15 @@ sub check_summe_stornobuchungen {
   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|
-    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');
@@ -232,7 +235,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
-      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);
@@ -251,13 +254,13 @@ sub check_ap_paid {
   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
-       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);
@@ -267,7 +270,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->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}");
   }
 }
 
@@ -367,22 +370,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
-  # 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",
-    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
-     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"}
@@ -395,7 +399,7 @@ sub check_overpayments {
   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})");
         }
@@ -476,7 +480,7 @@ 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)
-          WHERE ac.chart_link like '%AP_amount%'
+          WHERE ac.chart_link like '%AP_amount%' OR ac.chart_link like '%IC_cogs%'
           AND ac.transdate >= ? AND ac.transdate <= ?
           group by invnumber,trans_id,netamount having sum(ac.amount) <> ap.netamount*-1|;
 
@@ -551,7 +555,7 @@ sub check_ar_paid_acc_trans {
           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 <= ?
+          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);
@@ -575,7 +579,7 @@ sub check_ap_paid_acc_trans {
           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 <= ?
+          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);
@@ -592,7 +596,82 @@ sub check_ap_paid_acc_trans {
   }
 }
 
+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).");
+  }
+}
+
+sub check_orphaned_reconciliated_links {
+  my ($self) = @_;
 
+  my $query = qq|
+          SELECT purpose from bank_transactions
+          WHERE cleared is true
+          AND id not in (SELECT bank_transaction_id from reconciliation_links)
+          AND transdate >= ? AND transdate <= ?|;
+
+  my $bt_cleared_no_link = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
+
+  if ( scalar @{ $bt_cleared_no_link } > 0 ) {
+    $self->tester->ok(0, "Verwaiste abgeglichene Bankbewegungen gefunden. Bei folgenden Bankbewegungen ist die abgleichende Verknüpfung gelöscht worden:");
+
+    for my $bt_orphaned (@{ $bt_cleared_no_link }) {
+      $self->tester->diag("Verwendungszweck: $bt_orphaned->{purpose}");
+    }
+  } else {
+    $self->tester->ok(1, "Keine verwaisten Einträge in abgeglichenen Bankbewegungen.");
+  }
+}
+
+sub check_recommended_client_settings {
+  my ($self) = @_;
+
+  my $all_ok = 1;
+
+  # expand: check datev && check mark_as_paid
+  my %settings_values_nok = (
+                              SL::DB::Default->get->is_changeable => 1,
+                              SL::DB::Default->get->ar_changeable => 1,
+                              SL::DB::Default->get->ap_changeable => 1,
+                              SL::DB::Default->get->ir_changeable => 1,
+                              SL::DB::Default->get->gl_changeable => 1,
+                             );
+
+  foreach (keys %settings_values_nok) {
+    if ($_ == $settings_values_nok{$_}) {
+      $self->tester->ok(0, "Buchungskonfiguration: Mindestens ein Belegtyp ist immer änderbar.");
+      undef $all_ok;
+    }
+  }
+
+  # payments more strict (avoid losing payments acc_trans_ids)
+  my $payments_ok = SL::DB::Default->get->payments_changeable == 0 ? 1 : 0;
+  $self->tester->ok(0, "Manuelle Zahlungen sind zu lange änderbar (Empfehlung: niemals).") unless $payments_ok;
+
+  $self->tester->ok(1, "Mandantenkonfiguration optimal eingestellt.") if ($payments_ok && $all_ok);
+}
 1;
 
 __END__