X-Git-Url: http://wagnertech.de/gitweb/gitweb.cgi/mfinanz.git/blobdiff_plain/b11c7ad55b4a6445860e4494e550cb3ff8d09eac..644b730bf07a0187ea05439bfd36b4e75fad2a3d:/SL/BackgroundJob/SelfTest/Transactions.pm

diff --git a/SL/BackgroundJob/SelfTest/Transactions.pm b/SL/BackgroundJob/SelfTest/Transactions.pm
index 4d67c35e3..3eeb57f09 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 => 26);
+  $self->tester->plan(tests => 32);
 
   $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 {
@@ -630,7 +631,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 +681,8 @@ 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 <= ?|;
 
   my $bt_assigned_no_link = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
@@ -695,11 +697,11 @@ sub check_orphaned_bank_transaction_acc_trans_links {
     $self->tester->ok(1, "Keine verwaisten Einträge in verknüpften Bankbewegungen (Richtung Bank).");
   }
   # check for deleted acc_trans_ids
-  my $query = qq|
+  $query = qq|
           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);
@@ -715,6 +717,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__