X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FUSTVA.pm;h=0f237830953c16b785b8a7a796de8cc16fc9c41d;hb=fbbfe531162f1aa0bfff02e1dbd944b6851915b7;hp=51296f1bedf1f938a64f3d11e60eeacefda8a947;hpb=0f2d4920a798878ab2ff60ba11b190854c23c5fb;p=kivitendo-erp.git

diff --git a/SL/USTVA.pm b/SL/USTVA.pm
index 51296f1be..0f2378309 100644
--- a/SL/USTVA.pm
+++ b/SL/USTVA.pm
@@ -637,6 +637,7 @@ sub get_accounts_ustva {
   my $glwhere  = "";
   my $subwhere = "";
   my $ARwhere  = "";
+  my $APwhere  = '';
   my $arwhere  = "";
   my $item;
 
@@ -645,6 +646,7 @@ sub get_accounts_ustva {
       $subwhere .= " AND transdate >= '$fromdate'";
       $glwhere = " AND ac.transdate >= '$fromdate'";
       $ARwhere .= " AND acc.transdate >= '$fromdate'";
+      $APwhere .= " AND AP.transdate >= '$fromdate'"; 
     }
     $where .= " AND ac.transdate >= '$fromdate'";
   }
@@ -653,6 +655,7 @@ sub get_accounts_ustva {
     $where    .= " AND ac.transdate <= '$todate'";
     $ARwhere  .= " AND acc.transdate <= '$todate'";
     $subwhere .= " AND transdate <= '$todate'";
+    $APwhere  .= " AND AP.transdate <= '$todate'";     
   }
 
   if ($department_id) {
@@ -669,13 +672,18 @@ sub get_accounts_ustva {
                  AND ac.project_id = $form->{project_id}
 		 |;
   }
+#########################################
+# Method eq 'cash' = IST Versteuerung
+#########################################
 
-  if ($form->{method} eq 'cash') {
+  if ($form->{method} eq 'cash') {  
 
     $query = qq|
-
  SELECT
-   SUM( ac.amount *
+   -- Alle tatsaechlichen Zahlungseingaenge 
+   -- im Voranmeldezeitraum erfassen 
+   -- (Teilzahlungen werden prozentual auf verschiedene Steuern aufgeteilt)
+   SUM( ac.amount * 
       -- Bezahlt / Rechnungssumme
      ( 
        SELECT SUM(acc.amount)
@@ -700,101 +708,44 @@ sub get_accounts_ustva {
      -- Here no where, please. All Transactions ever should be
      -- testet if they are paied in the USTVA report period.
    GROUP BY c.pos_ustva
-	UNION
-
-	         SELECT sum(ac.amount) AS amount,
-		 c.$category
-		 FROM acc_trans ac
-		 JOIN chart c ON (c.id = ac.chart_id)
-		 JOIN ap a ON (a.id = ac.trans_id)
-		 $dpt_join
-		 WHERE $where
-		 $dpt_where
-		 AND ac.trans_id IN
-		   (
-		     SELECT trans_id
-		     FROM acc_trans
-		     JOIN chart ON (chart_id = id)
-		     WHERE link LIKE '%AP_amount%'
-		     $subwhere
-		   )
-
-		 $project
-		 GROUP BY c.$category
-
-        UNION
-
-		 SELECT sum(ac.amount) AS amount,
-		 c.$category
-		 FROM acc_trans ac
-		 JOIN chart c ON (c.id = ac.chart_id)
-		 JOIN gl a ON (a.id = ac.trans_id)
-		 $dpt_join
-		 WHERE $where
-		 $glwhere
-		 $dpt_from
-		 AND NOT (c.link = 'AR' OR c.link = 'AP')
-		 $project
-		 GROUP BY c.$category
-
-		 |;
 
-    if ($form->{project_id}) {
-
-      $query .= qq|
-
-	 UNION
-
-		 SELECT SUM(ac.sellprice * ac.qty) AS amount,
-		 c.$category
-		 FROM invoice ac
-	         JOIN ar a ON (a.id = ac.trans_id)
-		 JOIN parts p ON (ac.parts_id = p.id)
-		 JOIN chart c on (p.income_accno_id = c.id)
-	         $dpt_join
-	-- use transdate from subwhere
-		 WHERE 1 = 1 $subwhere
-		 AND c.category = 'I'
-		 $dpt_where
-		 AND ac.trans_id IN
-		   (
-		     SELECT trans_id
-		     FROM acc_trans
-		     JOIN chart ON (chart_id = id)
-		     WHERE link LIKE '%AR_paid%'
-		     $subwhere
-		   )
-
-		 $project
-		 GROUP BY c.$category
-
-	 UNION
-
-		 SELECT SUM(ac.sellprice) AS amount,
-		 c.$category
-		 FROM invoice ac
-	         JOIN ap a ON (a.id = ac.trans_id)
-		 JOIN parts p ON (ac.parts_id = p.id)
-		 JOIN chart c on (p.expense_accno_id = c.id)
-	         $dpt_join
-		 WHERE 1 = 1 $subwhere
-		 AND c.category = 'E'
-		 $dpt_where
-		 AND ac.trans_id IN
-		   (
-		     SELECT trans_id
-		     FROM acc_trans
-		     JOIN chart ON (chart_id = id)
-		     WHERE link LIKE '%AP_paid%'
-		     $subwhere
-		   )
-
-		 $project
-		 GROUP BY c.$category
-		 |;
-    }
-
-  } else {
+ UNION -- alle Ausgaben AP erfassen
+
+     SELECT
+     sum(ac.amount) AS amount, pos_ustva
+     FROM acc_trans ac
+     JOIN AP ON (AP.id = ac.trans_id )
+     JOIN chart c ON (c.id = ac.chart_id AND pos_ustva NOT LIKE '')
+     WHERE
+       1=1
+       $APwhere
+       $dpt_where
+       $project
+     GROUP BY pos_ustva
+
+ UNION -- alle Ausgaben und Einnahmen direkter gl Buchungen erfassen
+
+   SELECT sum
+   (
+     CASE WHEN c.link LIKE '%AR%' THEN ac.amount * -1
+          WHEN c.link LIKE '%AP%' THEN ac.amount * 1
+     END
+   ) AS amount, c.$category
+   FROM acc_trans ac
+   JOIN chart c ON (c.id = ac.chart_id)
+   JOIN gl a ON (a.id = ac.trans_id)
+   $dpt_join
+   WHERE $where
+   $dpt_from
+   AND NOT (c.link = 'AR' OR c.link = 'AP')
+   $project
+   GROUP BY c.$category
+   |;
+
+  } else { 
+#########################################
+# Method eq 'accrual' = Soll Versteuerung
+#########################################
 
     if ($department_id) {
       $dpt_join = qq|
@@ -806,55 +757,20 @@ sub get_accounts_ustva {
     }
 
     $query = qq|
-
-		 SELECT sum(ac.amount) AS amount,
-		 c.$category
-		 FROM acc_trans ac
-		 JOIN chart c ON (c.id = ac.chart_id)
-		 $dpt_join
-		 WHERE $where
-		 $dpt_where
-		 $project
-		 GROUP BY c.$category
-		 |;
-
-    if ($form->{project_id}) {
-
-      $query .= qq|
-
-	UNION
-
-		 SELECT SUM(ac.sellprice * ac.qty) AS amount,
-		 c.$category
-		 FROM invoice ac
-	         JOIN ar a ON (a.id = ac.trans_id)
-		 JOIN parts p ON (ac.parts_id = p.id)
-		 JOIN chart c on (p.income_accno_id = c.id)
-	         $dpt_join
-	-- use transdate from subwhere
-		 WHERE 1 = 1 $subwhere
-		 AND c.category = 'I'
-		 $dpt_where
-		 $project
-		 GROUP BY c.$category
-
-	UNION
-
-		 SELECT SUM(ac.sellprice * ac.qty) * -1 AS amount,
-		 c.$category
-		 FROM invoice ac
-	         JOIN ap a ON (a.id = ac.trans_id)
-		 JOIN parts p ON (ac.parts_id = p.id)
-		 JOIN chart c on (p.expense_accno_id = c.id)
-	         $dpt_join
-		 WHERE 1 = 1 $subwhere
-		 AND c.category = 'E'
-		 $dpt_where
-		 $project
-		 GROUP BY c.$category
-		 |;
-
-    }
+   SELECT sum
+   (
+     CASE WHEN c.link LIKE '%AR%' THEN ac.amount * -1
+          WHEN c.link LIKE '%AP%' THEN ac.amount * 1
+     END		 
+   ) AS amount, c.$category
+   FROM acc_trans ac
+   JOIN chart c ON (c.id = ac.chart_id)
+   $dpt_join
+   WHERE $where
+   $dpt_where
+   $project
+   GROUP BY c.$category
+   |;
   }
 
   my @accno;
@@ -869,9 +785,10 @@ sub get_accounts_ustva {
   $sth->execute || $form->dberror($query);
 
   while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
-    if ($ref->{amount} < 0) {
+# Bug 365 solved?!
+#    if ($ref->{amount} < 0) {
       $ref->{amount} *= -1;
-    }
+#    }
     if ($category eq "pos_bwa") {
       if ($last_period) {
         $form->{ $ref->{$category} }{kumm} += $ref->{amount};