From dcb0b06f692db05a856ae6c340227291d1b17cc7 Mon Sep 17 00:00:00 2001
From: =?utf8?q?Bernd=20Ble=C3=9Fmann?= <bernd@kivitendo-premium.de>
Date: Fri, 28 Feb 2020 12:31:51 +0100
Subject: [PATCH] SelfTest: Transactions: check_summe_stornobuchungen optimiert
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

Für den zu beobachteten Zeitraum werden jetzt getrennt betrachtet, ob
Rechnungen aus diesem Zeitraum storniert sind und mit Ihren Partnern in
der Summe nicht 0 ergeben,
oder ob
Stornorechnungen in diesem Zeitraum erstellt wurden und mit Ihrem Partner
in der Summer nicht 0 ergeben.

Die Partner müssen jeweils nicht im zu beobachteten Zeitraum liegen.

Da gab es auch schon mal einen Versuch im
commit 56e0744e2835cffe496531708ecfdaabc5b3bfee
"SelfTests check_summe_stornobuchungen optimiert".

Dieser führte aber nur zu langen Abfragezeiten.
---
 SL/BackgroundJob/SelfTest/Transactions.pm | 47 +++++++++++++++--------
 1 file changed, 30 insertions(+), 17 deletions(-)

diff --git a/SL/BackgroundJob/SelfTest/Transactions.pm b/SL/BackgroundJob/SelfTest/Transactions.pm
index 6ad354937..9eda7222c 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 => 32);
+  $self->tester->plan(tests => 34);
 
   $self->check_konten_mit_saldo_nicht_in_guv;
   $self->check_bilanzkonten_mit_pos_eur;
@@ -208,22 +208,35 @@ sub check_invnumbers_unique {
 sub check_summe_stornobuchungen {
   my ($self) = @_;
 
-  my $query = qq|
-    SELECT sum(amount) from ar a WHERE a.id IN
-      (SELECT id from ar 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 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');
-  $self->tester->ok($summe_stornobuchungen_ar == 0, 'Summe aller Verkaufsrechnungen (stornos + stornierte) soll 0 sein');
-  $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;
+  my %sums_canceled;
+  my %sums_storno;
+  foreach my $table (qw(ar ap)) {
+    # check invoices canceled (stornoed) in consideration period (corresponding stornos do not have to be in this period)
+    my $query = qq|
+      SELECT sum(amount) FROM $table WHERE id IN (
+        SELECT id FROM $table WHERE storno IS TRUE AND storno_id IS NULL AND transdate >= ? AND transdate <= ?
+        UNION
+        SELECT id FROM $table WHERE storno IS TRUE AND storno_id IS NOT NULL AND storno_id IN
+          (SELECT id FROM $table WHERE storno IS TRUE AND storno_id IS NULL AND transdate >= ? AND transdate <= ?)
+      )|;
+    ($sums_canceled{$table}) = selectfirst_array_query($::form, $self->dbh, $query, $self->fromdate, $self->todate, $self->fromdate, $self->todate);
+
+    # check storno invoices in consideration period (corresponding canceled (stornoed) invoices do not have to be in this period)
+    $query = qq|
+      SELECT sum(amount) FROM $table WHERE id IN (
+        SELECT storno_id FROM $table WHERE storno IS TRUE AND storno_id IS NOT NULL AND transdate >= ? AND transdate <= ?
+        UNION
+        SELECT id FROM $table WHERE storno IS TRUE AND storno_id IS NOT NULL AND transdate >= ? AND transdate <= ?
+      )|;
+    ($sums_storno{$table}) = selectfirst_array_query($::form, $self->dbh, $query, $self->fromdate, $self->todate, $self->fromdate, $self->todate);
+
+    my $text_rg = ($table eq 'ar') ? 'Verkaufsrechnungen' : 'Einkaufsrechnungen';
+
+    $self->tester->ok($sums_canceled{$table} == 0, "Summe aller $text_rg (stornos + stornierte) soll 0 sein (für stornierte Rechnungen)");
+    $self->tester->ok($sums_storno  {$table} == 0, "Summe aller $text_rg (stornos + stornierte) soll 0 sein (für Storno-Rechnungen)");
+    $self->tester->diag("Summe $text_rg ($table) (für stornierte Rechnungen) : " . $sums_canceled{$table}) if $sums_canceled{$table} != 0;
+    $self->tester->diag("Summe $text_rg ($table) (für Storno-Rechnungen)     : " . $sums_storno  {$table}) if $sums_storno  {$table} != 0;
+  }
 }
 
 sub check_ar_paid {
-- 
2.20.1