X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FRP.pm;h=4bb82f2b2a368ec89201dc2eceff60b8f6a0e836;hb=b632cee8434442efd8ae3962126c34123172daac;hp=072c54bd42123ed4151035c51b09e87839dd6b05;hpb=48963f462b5fff937438ca93bcc537d78e454f30;p=kivitendo-erp.git

diff --git a/SL/RP.pm b/SL/RP.pm
index 072c54bd4..4bb82f2b2 100644
--- a/SL/RP.pm
+++ b/SL/RP.pm
@@ -213,7 +213,7 @@ sub get_accounts {
 
   # if l_ob is selected l_cb is always ignored
   if ( $form->{l_ob} ) {
-    $where .= ' AND ac.ob_transaction is true  ' 
+    $where .= ' AND ac.ob_transaction is true  '
   } elsif ( not $form->{l_cb} ) {
     $where .= ' AND ac.cb_transaction is false ';
   };
@@ -516,13 +516,17 @@ sub get_accounts_g {
   if ($form->{method} eq 'cash') {
     $query =
       qq|
-       SELECT SUM( ac.amount *
+       SELECT SUM( ac.amount * CASE WHEN COALESCE((SELECT amount FROM ar WHERE id = ac.trans_id), 0) != 0 THEN
+            /* ar amount is not zero, so we can divide by amount   */
                     (SELECT SUM(acc.amount) * -1
                      FROM acc_trans acc
                      INNER JOIN chart c ON (acc.chart_id = c.id AND c.link LIKE '%AR_paid%')
                      WHERE 1=1 $inwhere AND acc.trans_id = ac.trans_id)
-                  / COALESCE((SELECT amount FROM ar WHERE id = ac.trans_id and amount != 0 ), 1)
-                ) AS amount, c.pos_eur
+                  / (SELECT amount FROM ar WHERE id = ac.trans_id) 
+            ELSE 0 
+            /* ar amount is zero, or we are checking with a non-ar-transaction, so we return 0 in both cases as multiplicator of ac.amount */
+            END
+                ) AS amount, c.$category
        FROM acc_trans ac
        LEFT JOIN chart c ON (c.id  = ac.chart_id)
        LEFT JOIN ar      ON (ar.id = ac.trans_id)
@@ -535,7 +539,7 @@ sub get_accounts_g {
                                 )
       WHERE ac.trans_id IN (SELECT DISTINCT trans_id FROM acc_trans WHERE 1=1 $subwhere)
 
-      GROUP BY c.pos_eur
+      GROUP BY c.$category
 
 /*
        SELECT SUM(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category
@@ -1171,6 +1175,8 @@ sub aging {
 
   my ($invoice, $arap, $buysell, $ct, $ct_id, $ml);
 
+  # falls customer ziehen wir die offene forderungsliste
+  # anderfalls für die lieferanten die offenen verbindlichkeitne
   if ($form->{ct} eq "customer") {
     $invoice = "is";
     $arap = "ar";
@@ -1186,12 +1192,42 @@ sub aging {
   }
   $ct_id = "${ct}_id";
 
-  $form->{todate} = $form->current_date($myconfig) unless ($form->{todate});
-  my $todate = conv_dateq($form->{todate});
-  my $fromdate = conv_dateq($form->{fromdate});
+  # erweiterung um einen freien zeitraum oder einen stichtag
+  # mit entsprechender altersstrukturliste (s.a. Bug 1842)
+  # eine neue variable an der oberfläche eingeführt, somit ist
+  # todate == freier zeitrau und fordate == stichtag
 
-  my $fromwhere = ($form->{fromdate} ne "") ? " AND (transdate >= (date $fromdate)) " : "";
+  my ($review_of_aging_list, $todate, $fromdate, $fromwhere, $fordate);
 
+  if ($form->{reporttype} eq 'custom') {  # altersstrukturliste
+
+    # explizit rausschmeissen was man für diesen bericht nicht braucht
+    delete $form->{fromdate};
+    delete $form->{todate};
+
+    # an der oberfläche ist das tagesaktuelle datum vorausgewählt
+    # falls es dennoch per Benutzereingabe gelöscht wird, lieber wieder vorbelegen
+    # ferner muss für die spätere DB-Abfrage muss todate gesetzt sein.
+    $form->{fordate}  = $form->current_date($myconfig) unless ($form->{fordate});
+    $fordate          = conv_dateq($form->{fordate});
+    $todate           = $fordate;
+
+    if ($form->{review_of_aging_list}) { # falls die liste leer ist, alles anzeigen
+      if ($form->{review_of_aging_list} =~ m "-") {             # ..  periode von bis
+        my @period = split(/-/, $form->{review_of_aging_list}); # ... von periode bis periode
+        $review_of_aging_list = " AND $period[0] <  (date $fordate) - duedate
+                                  AND (date $fordate) - duedate  < $period[1]";
+      } else {
+        $form->{review_of_aging_list} =~ s/[^0-9]//g;   # größer 120 das substitute ist nur für das '>' zeichen
+        $review_of_aging_list = " AND $form->{review_of_aging_list} < (date $fordate) - duedate";
+      }
+    }
+  } else {  # freier zeitraum OHNE review_of_aging_list
+    $form->{todate}  = $form->current_date($myconfig) unless ($form->{todate});
+    $todate = conv_dateq($form->{todate});
+    $fromdate = conv_dateq($form->{fromdate});
+    $fromwhere = ($form->{fromdate} ne "") ? " AND (transdate >= (date $fromdate)) " : "";
+  }
   my $where = " 1 = 1 ";
   my ($name, $null);
 
@@ -1209,19 +1245,7 @@ sub aging {
     $where .= qq| AND (a.department_id = | . conv_i($department_id, 'NULL') . qq|)|;
     $where_dpt = qq| AND (${arap}.department_id = | . conv_i($department_id, 'NULL') . qq|)|;
   }
-  my $review_of_aging_list;
-  if ($form->{review_of_aging_list}) {
-    if ($form->{review_of_aging_list} =~ m "-"){
-      my @period = split(/-/, $form->{review_of_aging_list});
-      $review_of_aging_list = " AND $period[0] < date_part('days', now() - duedate)
-                                AND date_part('days', now() - duedate)  < $period[1]";
-    } else {
-      $form->{review_of_aging_list} =~ s/[^0-9]//g;
-      $review_of_aging_list = " AND $form->{review_of_aging_list} < date_part('days', now() - duedate)";
-    }
-  }
-
-  my $q_details = qq|
+ my $q_details = qq|
 
     SELECT ${ct}.id AS ctid, ${ct}.name,
       street, zipcode, city, country, contact, email,
@@ -1237,7 +1261,7 @@ sub aging {
     WHERE ((paid != amount) OR (datepaid > (date $todate) AND datepaid is not null))
       AND NOT COALESCE (${arap}.storno, 'f')
       AND (${arap}.${ct}_id = ${ct}.id)
-      $where_dpt 
+      $where_dpt
       AND (${ct}.id = ?)
       AND (transdate <= (date $todate) $fromwhere )
       $review_of_aging_list
@@ -1629,7 +1653,7 @@ sub bwa {
 
   my @periods        = qw(jetzt kumm);
   my @gesamtleistung = qw(1 3);
-  my @gesamtkosten   = qw (10 11 12 13 14 15 16 17 18 19 20);
+  my @gesamtkosten   = qw (10 11 12 13 14 15 16 17 18 20);
   my @ergebnisse     =
     qw (rohertrag betriebrohertrag betriebsergebnis neutraleraufwand neutralerertrag ergebnisvorsteuern ergebnis gesamtleistung gesamtkosten);
 
@@ -1664,13 +1688,13 @@ sub bwa {
       $form->{ "$key" . "betriebrohertrag" } -
       $form->{ "$key" . "gesamtkosten" };
     $form->{ "$key" . "neutraleraufwand" } =
-      $form->{30}{$key} + $form->{31}{$key};
-    $form->{ "$key" . "neutralertrag" } =
+      $form->{19}{$key} + $form->{30}{$key} + $form->{31}{$key};
+    $form->{ "$key" . "neutralerertrag" } =
       $form->{32}{$key} + $form->{33}{$key} + $form->{34}{$key};
     $form->{ "$key" . "ergebnisvorsteuern" } =
       $form->{ "$key" . "betriebsergebnis" } -
       $form->{ "$key" . "neutraleraufwand" } +
-      $form->{ "$key" . "neutralertrag" };
+      $form->{ "$key" . "neutralerertrag" };
     $form->{ "$key" . "ergebnis" } =
       $form->{ "$key" . "ergebnisvorsteuern" } - $form->{35}{$key};