X-Git-Url: http://wagnertech.de/gitweb/gitweb.cgi/mfinanz.git/blobdiff_plain/88286589319955ce2a0479a5d053e9bda2f0c777..51b0e217d00123575f362fb81827e89d1534d6fb:/SL/BackgroundJob/SelfTest/Transactions.pm

diff --git a/SL/BackgroundJob/SelfTest/Transactions.pm b/SL/BackgroundJob/SelfTest/Transactions.pm
index b64966a74..a30acc701 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 => 25);
+  $self->tester->plan(tests => 32);
 
   $self->check_konten_mit_saldo_nicht_in_guv;
   $self->check_bilanzkonten_mit_pos_eur;
@@ -42,6 +42,9 @@ sub run {
   $self->check_ap_paid_acc_trans;
   $self->check_zero_amount_paid_but_datepaid_exists;
   $self->check_orphaned_reconciliated_links;
+  $self->check_recommended_client_settings;
+  $self->check_orphaned_bank_transaction_acc_trans_links;
+  $self->check_consistent_itimes;
 }
 
 sub _setup {
@@ -479,7 +482,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%' OR ac.chart_link like '%IC_cogs%'
+          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|;
 
@@ -644,6 +647,145 @@ sub check_orphaned_reconciliated_links {
   }
 }
 
+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);
+}
+
+sub check_orphaned_bank_transaction_acc_trans_links {
+  my ($self) = @_;
+
+  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 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);
+
+  if ( scalar @{ $bt_assigned_no_link } > 0 ) {
+    $self->tester->ok(0, "Verwaiste Verknüpfungen zu Bankbewegungen gefunden. Bei folgenden Bankbewegungen ist eine interne Verknüpfung gelöscht worden:");
+
+    for my $bt_orphaned (@{ $bt_assigned_no_link }) {
+      $self->tester->diag("Verwendungszweck: $bt_orphaned->{purpose}");
+    }
+  } else {
+    $self->tester->ok(1, "Keine verwaisten Einträge in verknüpften Bankbewegungen (Richtung Bank).");
+  }
+  # check for deleted acc_trans_ids
+  my $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)
+           AND transdate >= ? AND transdate <= ?)|;
+
+  my $bt_assigned_no_acc_trans = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
+
+  if ( scalar @{ $bt_assigned_no_acc_trans } > 0 ) {
+    $self->tester->ok(0, "Verwaiste Verknüpfungen zu Bankbewegungen gefunden. Bei folgenden Bankbewegungen ist eine interne Verknüpfung gelöscht worden:");
+
+    for my $bt_orphaned (@{ $bt_assigned_no_acc_trans }) {
+      $self->tester->diag("Verwendungszweck: $bt_orphaned->{purpose}");
+    }
+  } else {
+    $self->tester->ok(1, "Keine verwaisten Einträge in verknüpften Bankbewegungen (Richtung Buchung (Richtung Buchung)).");
+  }
+}
+
+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;