X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FBackgroundJob%2FSelfTest%2FTransactions.pm;h=9eda7222c82838e9b36c89f3b31305166fd82103;hb=c2e1239427e2a7964d8a7f87a06645a1cacaea25;hp=0176f858d444ed0f7d626cc13440956ab702520d;hpb=43f67d0dad4851ed0d2b78c595ad70e3da235237;p=kivitendo-erp.git

diff --git a/SL/BackgroundJob/SelfTest/Transactions.pm b/SL/BackgroundJob/SelfTest/Transactions.pm
index 0176f858d..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 => 29);
+  $self->tester->plan(tests => 34);
 
   $self->check_konten_mit_saldo_nicht_in_guv;
   $self->check_bilanzkonten_mit_pos_eur;
@@ -44,6 +44,7 @@ sub run {
   $self->check_orphaned_reconciliated_links;
   $self->check_recommended_client_settings;
   $self->check_orphaned_bank_transaction_acc_trans_links;
+  $self->check_consistent_itimes;
 }
 
 sub _setup {
@@ -207,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 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 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 {
@@ -630,7 +644,7 @@ sub check_orphaned_reconciliated_links {
   my $query = qq|
           SELECT purpose from bank_transactions
           WHERE cleared is true
-          AND id not in (SELECT bank_transaction_id from reconciliation_links)
+          AND NOT EXISTS (SELECT bank_transaction_id from reconciliation_links WHERE bank_transaction_id = bank_transactions.id)
           AND transdate >= ? AND transdate <= ?|;
 
   my $bt_cleared_no_link = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
@@ -680,7 +694,7 @@ sub check_orphaned_bank_transaction_acc_trans_links {
   my $query = qq|
           SELECT purpose from bank_transactions
           WHERE invoice_amount <> 0
-          AND id not in (SELECT bank_transaction_id from bank_transaction_acc_trans)
+          AND NOT EXISTS (SELECT bank_transaction_id FROM bank_transaction_acc_trans WHERE bank_transaction_id = bank_transactions.id)
           AND itime > (SELECT min(itime) from bank_transaction_acc_trans)
           AND transdate >= ? AND transdate <= ?|;
 
@@ -700,7 +714,7 @@ sub check_orphaned_bank_transaction_acc_trans_links {
           SELECT purpose from bank_transactions
           WHERE id in
           (SELECT bank_transaction_id from bank_transaction_acc_trans
-           where acc_trans_id NOT IN (select acc_trans_id from acc_trans)
+           WHERE NOT EXISTS (SELECT acc_trans.acc_trans_id FROM acc_trans WHERE acc_trans.acc_trans_id = bank_transaction_acc_trans.acc_trans_id)
            AND transdate >= ? AND transdate <= ?)|;
 
   my $bt_assigned_no_acc_trans = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
@@ -716,6 +730,76 @@ sub check_orphaned_bank_transaction_acc_trans_links {
   }
 }
 
+sub check_consistent_itimes {
+  my ($self) = @_;
+  my $query;
+
+  $query = qq|
+    SELECT mtime, itime,gldate, acc_trans_id, trans_id
+    FROM  acc_trans a
+    WHERE itime::date <> gldate::date
+    AND a.transdate >= ? and a.transdate <= ?|;
+
+  my $itimes_ac = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
+
+  if ( scalar @{ $itimes_ac } > 0 ) {
+    $self->tester->ok(0, "Inkonsistente Zeitstempel in der acc_trans gefunden. Bei folgenden ids:");
+    for my $bogus_time (@{ $itimes_ac }) {
+      $self->tester->diag("ID: $bogus_time->{trans_id} acc_trans_id: $bogus_time->{acc_trans_id} ");
+    }
+  } else {
+    $self->tester->ok(1, "Keine inkonsistenten Zeitstempel in der acc_trans.");
+  }
+  $query = qq|
+    SELECT amount, itime, gldate, id
+    FROM ap a
+    WHERE itime::date <> gldate::date
+    AND a.transdate >= ? and a.transdate <= ?|;
+
+  my $itimes_ap = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
+
+  if ( scalar @{ $itimes_ap } > 0 ) {
+    $self->tester->ok(0, "Inkonsistente Zeitstempel in ap gefunden. Bei folgenden ids:");
+    for my $bogus_time (@{ $itimes_ap }) {
+      $self->tester->diag("ID: $bogus_time->{id} itime: $bogus_time->{itime} mtime: $bogus_time->{mtime} ");
+    }
+  } else {
+    $self->tester->ok(1, "Keine inkonsistenten Zeitstempel in ap.");
+  }
+  $query = qq|
+    SELECT amount, itime, gldate, id
+    FROM ar a
+    WHERE itime::date <> gldate::date
+    AND a.transdate >= ? and a.transdate <= ?|;
+
+  my $itimes_ar = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
+
+  if ( scalar @{ $itimes_ap } > 0 ) {
+    $self->tester->ok(0, "Inkonsistente Zeitstempel in ar gefunden. Bei folgenden ids:");
+    for my $bogus_time (@{ $itimes_ar }) {
+      $self->tester->diag("ID: $bogus_time->{id} itime: $bogus_time->{itime} mtime: $bogus_time->{mtime} ");
+    }
+  } else {
+    $self->tester->ok(1, "Keine inkonsistenten Zeitstempel in ar.");
+  }
+  $query = qq|
+    SELECT itime, gldate, id, mtime
+    FROM gl a
+    WHERE itime::date <> gldate::date
+    AND a.transdate >= ? and a.transdate <= ?|;
+
+  my $itimes_gl = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
+
+  if ( scalar @{ $itimes_gl } > 0 ) {
+    $self->tester->ok(0, "Inkonsistente Zeitstempel in gl gefunden. Bei folgenden ids:");
+    for my $bogus_time (@{ $itimes_ar }) {
+      $self->tester->diag("ID: $bogus_time->{id} itime: $bogus_time->{itime} mtime: $bogus_time->{mtime} ");
+    }
+  } else {
+    $self->tester->ok(1, "Keine inkonsistenten Zeitstempel in gl.");
+  }
+}
+
 1;
 
 __END__
@@ -730,10 +814,6 @@ SL::BackgroundJob::SelfTest::Transactions - base tests
 
 Several tests for data integrity.
 
-=head1 FUNCTIONS
-
-=head1 BUGS
-
 =head1 AUTHOR
 
 G. Richardson E<lt>information@richardson-bueren.deE<gt>
@@ -741,4 +821,3 @@ Jan Büren E<lt>information@richardson-bueren.deE<gt>
 Sven Schoeling E<lt>s.schoeling@linet-services.deE<gt>
 
 =cut
-