X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FBackgroundJob%2FSelfTest%2FTransactions.pm;h=6307ea99fed24d352b49829bf4e1c0eb7d5c2552;hb=deb4d2dbb676d7d6f69dfe7815d6e0cb09bd4a44;hp=a3000c2395291284e751ad47b6df23557ded0937;hpb=f7d51d3e6c5e9b16f688e2e9417f54aee64ed23a;p=kivitendo-erp.git

diff --git a/SL/BackgroundJob/SelfTest/Transactions.pm b/SL/BackgroundJob/SelfTest/Transactions.pm
index a3000c239..6307ea99f 100644
--- a/SL/BackgroundJob/SelfTest/Transactions.pm
+++ b/SL/BackgroundJob/SelfTest/Transactions.pm
@@ -15,11 +15,14 @@ sub run {
 
   $self->_setup;
 
-  $self->tester->plan(tests => 14);
+  $self->tester->plan(tests => 18);
 
   $self->check_konten_mit_saldo_nicht_in_guv;
+  $self->check_bilanzkonten_mit_pos_eur;
   $self->check_balanced_individual_transactions;
   $self->check_verwaiste_acc_trans_eintraege;
+  $self->check_verwaiste_invoice_eintraege;
+  $self->check_ar_acc_trans_amount;
   $self->check_netamount_laut_invoice_ar;
   $self->check_invnumbers_unique;
   $self->check_summe_stornobuchungen;
@@ -30,6 +33,7 @@ sub run {
   $self->check_paid_stornos;
   $self->check_stornos_ohne_partner;
   $self->check_overpayments;
+  $self->check_every_account_with_taxkey;
   $self->calc_saldenvortraege;
 }
 
@@ -70,6 +74,20 @@ sub check_konten_mit_saldo_nicht_in_guv {
   }
 }
 
+sub check_bilanzkonten_mit_pos_eur {
+  my ($self) = @_;
+
+  my $query = qq|SELECT accno, description FROM chart WHERE (category = 'A' OR category = 'L' OR category = 'Q') AND (pos_eur IS NOT NULL OR pos_eur != 0)|;
+
+  my $bilanzkonten_mit_pos_eur = selectall_hashref_query($::form, $self->dbh, $query);
+  if (@$bilanzkonten_mit_pos_eur) {
+     $self->tester->ok(0, "Es gibt Bilanzkonten die der GuV/EÜR zugeordnet sind)");
+     $self->tester->diag("$_->{accno}  $_->{description}") for @$bilanzkonten_mit_pos_eur;
+  } else {
+     $self->tester->ok(1, "Keine Bilanzkonten in der GuV");
+  }
+}
+
 sub check_balanced_individual_transactions {
   my ($self) = @_;
 
@@ -112,6 +130,23 @@ sub check_verwaiste_acc_trans_eintraege {
   }
 }
 
+sub check_verwaiste_invoice_eintraege {
+  # this check is always run for all invoice entries in the entire database
+  my ($self) = @_;
+  my $query = qq|
+     select * from invoice i
+      where trans_id not in (select id from ar union select id from ap order by id) |;
+
+  my $verwaiste_invoice = selectall_hashref_query($::form, $self->dbh, $query);
+  if (@$verwaiste_invoice) {
+     $self->tester->ok(0, "Es gibt verwaiste invoice Einträge! (wo ar/ap-Eintrag fehlt)");
+     for my $invoice ( @{ $verwaiste_invoice }) {
+        $self->tester->diag("invoice: id: $invoice->{id}  trans_id: $invoice->{trans_id}   description: $invoice->{description}  itime: $invoice->{itime}");
+     };
+  } else {
+     $self->tester->ok(1, "Keine verwaisten invoice Einträge (wo ar/ap-Eintrag fehlt)");                                                                                       }
+}
+
 sub check_netamount_laut_invoice_ar {
   my ($self) = @_;
   my $query = qq|
@@ -167,8 +202,8 @@ sub check_summe_stornobuchungen {
       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 + stronierte) soll 0 sein');
-  $self->tester->ok($summe_stornobuchungen_ar == 0, 'Summe aller Verkaufsrechnungen (stornos + stronierte) soll 0 sein');
+  $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 Einkaufsrechnungen (ar): $summe_stornobuchungen_ar") if $summe_stornobuchungen_ar;
   $self->tester->diag("Summe Einkaufsrechnungen (ap): $summe_stornobuchungen_ap") if $summe_stornobuchungen_ap;
 }
@@ -284,16 +319,22 @@ sub check_stornos_ohne_partner {
   my ($self) = @_;
 
   my $query = qq|
-      select ar.id,invnumber,storno,amount,transdate,type,customernumber
-    from ar
-    left join customer c on (c.id = ar.customer_id)
-    where storno_id is null and storno is true and ar.id not in (select storno_id from ar where storno_id is not null and storno is true);
+    SELECT (SELECT cast ('ar' as text)) as invoice ,ar.id,invnumber,storno,amount,transdate,type,customernumber as cv_number
+    FROM ar
+    LEFT JOIN customer c on (c.id = ar.customer_id)
+    WHERE storno_id is null AND storno is true AND ar.id not in (SELECT storno_id FROM ar WHERE storno_id is not null AND storno is true)
+    UNION
+    SELECT (SELECT cast ('ap' as text)) as invoice,ap.id,invnumber,storno,amount,transdate,type,vendornumber as cv_number
+    FROM ap
+    LEFT JOIN vendor v on (v.id = ap.vendor_id)
+    WHERE storno_id is null AND storno is true AND ap.id not in (SELECT storno_id FROM ap WHERE storno_id is not null AND storno is true);
   |;
+
   my $stornos_ohne_partner =  selectall_hashref_query($::form, $self->dbh, $query);
 
   $self->tester->ok(@$stornos_ohne_partner == 0, 'Es sollte keine Stornos ohne Partner geben');
   if (@$stornos_ohne_partner) {
-    $self->tester->diag("stornos ohne partner:   (kann passieren wenn Stornorechnung außerhalb Zeitraum liegt)");
+    $self->tester->diag("Stornos ohne Partner:   (kann passieren wenn Stornorechnung außerhalb Zeitraum liegt)");
     $self->tester->diag("gilt aber trotzdem als paid zu dem Zeitpunkt, oder?");
   }
   my $stornoheader = 0;
@@ -371,6 +412,45 @@ sub calc_saldenvortraege {
   $self->tester->diag("Saldo $saldenvortragskonto am 31.12.@{[DateTime->today->year]}: @{[ $saldo_9000_jahresende * 1 ]}    (sollte 0 sein)");
 }
 
+sub check_every_account_with_taxkey {
+  my ($self) = @_;
+
+  my $query = qq|SELECT accno, description FROM chart WHERE id NOT IN (select chart_id from taxkeys)|;
+  my $accounts_without_tk = selectall_hashref_query($::form, $self->dbh, $query);
+
+  if ( scalar @{ $accounts_without_tk } > 0 ){
+    $self->tester->ok(0, "Folgende Konten haben keinen gültigen Steuerschlüssel:");
+
+    for my $account_without_tk (@{ $accounts_without_tk } ) {
+      $self->tester->diag("Kontonummer: $account_without_tk->{accno} Beschreibung: $account_without_tk->{description}");
+    }
+  } else {
+    $self->tester->ok(1, "Jedes Konto hat einen gültigen Steuerschlüssel!");
+  }
+}
+
+sub check_ar_acc_trans_amount {
+  my ($self) = @_;
+
+  my $query = qq|
+          select ar.invnumber, ar.netamount, ac.amount
+           from ar left join acc_trans ac on (ac.trans_id = ar.id) where ac.chart_link like 'AR_amount%' AND ac.amount <> ar.netamount|;
+
+  my $ar_amount_not_ac_amount = selectall_hashref_query($::form, $self->dbh, $query);
+
+  if ( scalar @{ $ar_amount_not_ac_amount } > 0 ) {
+    $self->tester->ok(0, "Folgende Ausgangsrechnungen haben einen falschen Netto-Wert im Nebenbuch:");
+
+    for my $ar_ac_amount_nok (@{ $ar_amount_not_ac_amount } ) {
+      $self->tester->diag("Rechnungsnummer: $ar_ac_amount_nok->{invnumber} Hauptbuch-Wert: $ar_ac_amount_nok->{amount}
+                            Nebenbuch-Wert: $ar_ac_amount_nok->{netamount}");
+    }
+  } else {
+    $self->tester->ok(1, "Hauptbuch-Nettowert und Nebenbuch-Nettowert stimmen überein.");
+  }
+
+}
+
 1;
 
 __END__
@@ -391,8 +471,8 @@ Several tests for data integrity.
 
 =head1 AUTHOR
 
-Geoffrey Richardsom E<lt>information@richardsonbueren.deE<gt>
-Jan Büren E<lt>information@richardsonbueren.deE<gt>
+G. Richardson E<lt>information@richardson-bueren.deE<gt>
+Jan Büren E<lt>information@richardson-bueren.deE<gt>
 Sven Schoeling E<lt>s.schoeling@linet-services.deE<gt>
 
 =cut