Uebernahme der kompletten Version, so wie sie Philip als "Demo-Version" gezeigt hat...

[mfinanz.git] / SL / USTVA.pm

diff --git a/SL/USTVA.pm b/SL/USTVA.pm
index 865bfae6aa9e408e2b51ec39c2dfccc0e8b5a75a..84dc4f3122f246bb9687f730df6a1533e71e2e0f 100644 (file)
--- a/SL/USTVA.pm
+++ b/SL/USTVA.pm
@@ -724,17 +724,14 @@ sub get_accounts_ustva {
 
         UNION
 
-                SELECT sum(
-                  CASE WHEN c.link LIKE '%AR%' THEN ac.amount * -1
-                       WHEN c.link LIKE '%AP%' THEN ac.amount * 1
-                   END
-                ) AS amount,
+                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
@@ -742,6 +739,61 @@ sub get_accounts_ustva {
 
                 |;
 
+    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 {
 
     if ($department_id) {
@@ -755,12 +807,7 @@ sub get_accounts_ustva {
 
     $query = qq|
 
-                SELECT sum(
-                  CASE WHEN c.link LIKE '%AR%' THEN ac.amount * -1
-                       WHEN c.link LIKE '%AP%' THEN ac.amount * 1
-                   END          
-                
-                ) AS amount,
+                SELECT sum(ac.amount) AS amount,
                 c.$category
                 FROM acc_trans ac
                 JOIN chart c ON (c.id = ac.chart_id)
@@ -770,24 +817,58 @@ sub get_accounts_ustva {
                 $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
+                |;
+
+    }
   }
 
   my @accno;
   my $accno;
   my $ref;
 
-  # Show all $query in Debuglevel LXDebug::QUERY
-  $callingdetails = (caller (0))[3];
-  $main::lxdebug->message(LXDebug::QUERY, "$callingdetails \$query=\n $query");
-              
+  #print $query;
   my $sth = $dbh->prepare($query);
   $sth->execute || $form->dberror($query);
 
   while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
-# Bug 365 solved?!
-#    if ($ref->{amount} < 0) {
+    if ($ref->{amount} < 0) {
       $ref->{amount} *= -1;
-#    }
+    }
     if ($category eq "pos_bwa") {
       if ($last_period) {
         $form->{ $ref->{$category} }{kumm} += $ref->{amount};