Weitere Abfragen umformatiert.

[kivitendo-erp.git] / SL / CA.pm

diff --git a/SL/CA.pm b/SL/CA.pm
index 2ab7e47..8122b9d 100644 (file)
--- a/SL/CA.pm
+++ b/SL/CA.pm
@@ -42,7 +42,7 @@ sub all_accounts {
 
   my ($self, $myconfig, $form) = @_;
 
-  my $amount = ();
+  my %amount;
 
   # connect to database
   my $dbh = $form->dbconnect($myconfig);
@@ -65,7 +65,7 @@ sub all_accounts {
   $sth = $dbh->prepare($query);
   $sth->execute || $form->dberror($query);
 
-  my $gifi = ();
+  my %gifi;
   while (my ($accno, $description) = $sth->fetchrow_array) {
     $gifi{$accno} = $description;
   }
@@ -141,31 +141,14 @@ sub all_transactions {
   if ($form->{todate}) {
     $fromto   .= " AND ac.transdate <= '$form->{todate}'";
     $subwhere .= " AND transdate <= '$form->{todate}'";
+    $glwhere .= " AND ac.transdate <= '$form->{todate}'";
   }
 
-  if ($form->{eur}) {
-    $AR_PAID = qq|AND ac.trans_id IN
-                  (
-                    SELECT trans_id
-                    FROM acc_trans
-                    JOIN chart ON (chart_id = id)
-                    WHERE link LIKE '%AR_paid%'
-                    $subwhere
-                  )|;
-    $AP_PAID = qq|AND ac.trans_id IN
-                  (
-                    SELECT trans_id
-                    FROM acc_trans
-                    JOIN chart ON (chart_id = id)
-                    WHERE link LIKE '%AP_paid%'
-                    $subwhere
-                  )|;
-  } else {
-    $where .= $fromto;
-    $AR_PAID = "";
-    $AP_PAID = "";
-    $glwhere = "";
-  }
+
+  $where .= $fromto;
+  $AR_PAID = "";
+  $AP_PAID = "";
+  $glwhere = "";    # note! gl will be aliased as "a" later...
   my $sortorder = join ', ',
     $form->sort_columns(qw(transdate reference description));
   my $false = ($myconfig->{dbdriver} eq 'Pg') ? FALSE: q|'0'|;
@@ -181,10 +164,10 @@ sub all_transactions {
   my $dpt_join;
   if ($department_id) {
     $dpt_join = qq|
-                   JOIN department t ON (t.trans_id = ac.trans_id)
+                   JOIN department t ON (t.id = a.department_id)
                  |;
-    $dpt_where == qq|
-                  AND t.department_id = $department_id
+    $dpt_where = qq|
+                  AND t.id = $department_id
                  |;
   }
 
@@ -234,7 +217,7 @@ sub all_transactions {
 
               UNION
 
-                 SELECT SUM(ac.sellprice)
+                 SELECT SUM(ac.qty * ac.sellprice)
                  FROM invoice ac
                  JOIN ar a ON (ac.trans_id = a.id)
                  JOIN parts p ON (ac.parts_id = p.id)
@@ -248,7 +231,7 @@ sub all_transactions {
 
               UNION
 
-                 SELECT SUM(ac.sellprice)
+                 SELECT SUM(ac.qty * ac.sellprice)
                  FROM invoice ac
                  JOIN ap a ON (ac.trans_id = a.id)
                  JOIN parts p ON (ac.parts_id = p.id)
@@ -280,7 +263,7 @@ sub all_transactions {
 
               UNION
 
-                 SELECT SUM(ac.sellprice)
+                 SELECT SUM(ac.qty * ac.sellprice)
                  FROM invoice ac
                  JOIN ar a ON (ac.trans_id = a.id)
                  JOIN parts p ON (ac.parts_id = p.id)
@@ -294,7 +277,7 @@ sub all_transactions {
 
               UNION
 
-                 SELECT SUM(ac.sellprice)
+                 SELECT SUM(ac.qty * ac.sellprice)
                  FROM invoice ac
                  JOIN ap a ON (ac.trans_id = a.id)
                  JOIN parts p ON (ac.parts_id = p.id)
@@ -323,23 +306,32 @@ sub all_transactions {
 
   foreach my $id (@id) {
 
+    # NOTE:
+    #  Postgres is really picky about the order of implicit CROSS JOINs with ','
+    #  if you alias the  tables and want to use the alias later in another JOIN.
+    #  the alias you want to use has to be the most recent in the list, otherwise
+    #  Postgres will overwrite the alias internally and complain.
+    #  For this reason, in the next 3 SELECTs, the 'a' alias is last in the list.
+    #  Don't change this, and if you do, substitute the ',' with CROSS JOIN
+    #  ... that also works.
+
     # get all transactions
     $query .= qq|$union
-      SELECT g.id, g.reference, g.description, ac.transdate,
+      SELECT a.id, a.reference, a.description, ac.transdate,
             $false AS invoice, ac.amount, 'gl' as module
-               FROM gl g, acc_trans ac $dpt_from
+               FROM acc_trans ac, gl a $dpt_join
                WHERE $where
                $glwhere
-               $dpt_join
+               $dpt_where
                $project
                AND ac.chart_id = $id
-               AND ac.trans_id = g.id
+               AND ac.trans_id = a.id
       UNION
       SELECT a.id, a.invnumber, c.name, ac.transdate,
             a.invoice, ac.amount, 'ar' as module
-               FROM ar a, acc_trans ac, customer c $dpt_from
+               FROM acc_trans ac, customer c, ar a $dpt_join
                WHERE $where
-               $dpt_join
+               $dpt_where
                $project
                AND ac.chart_id = $id
                AND ac.trans_id = a.id
@@ -348,9 +340,9 @@ sub all_transactions {
       UNION
       SELECT a.id, a.invnumber, v.name, ac.transdate,
             a.invoice, ac.amount, 'ap' as module
-               FROM ap a, acc_trans ac, vendor v $dpt_from
+               FROM acc_trans ac, vendor v, ap a $dpt_join
                WHERE $where
-               $dpt_join
+               $dpt_where
                $project
                AND ac.chart_id = $id
                AND ac.trans_id = a.id
@@ -369,9 +361,9 @@ sub all_transactions {
       $query .= qq|
 
              UNION ALL
-      
+
                  SELECT a.id, a.invnumber, c.name, a.transdate,
-                a.invoice, ac.sellprice, 'ar' as module
+                a.invoice, ac.qty * ac.sellprice AS sellprice, 'ar' as module
                 FROM ar a
                 JOIN invoice ac ON (ac.trans_id = a.id)
                 JOIN parts p ON (ac.parts_id = p.id)
@@ -382,11 +374,11 @@ sub all_transactions {
                 $todate_where
                 $dpt_where
                 $project
-      
+
              UNION ALL
-      
+
                  SELECT a.id, a.invnumber, v.name, a.transdate,
-                a.invoice, ac.sellprice, 'ap' as module
+                a.invoice, ac.qty * ac.sellprice AS sellprice, 'ap' as module
                 FROM ap a
                 JOIN invoice ac ON (ac.trans_id = a.id)
                 JOIN parts p ON (ac.parts_id = p.id)
@@ -451,4 +443,3 @@ sub all_transactions {
 }
 
 1;
-