X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FCA.pm;h=28ffc2e73df9fa3506ee019cc59f3b2aa66b1a97;hb=b61db412d0d913a4fcecbce6678710ad0eb76cf6;hp=dfe67837d7a9aeb413df4e1e5ec3adc30b09cf4f;hpb=4dbb09950c9f5596646537c12d991c99086fe7c1;p=kivitendo-erp.git

diff --git a/SL/CA.pm b/SL/CA.pm
index dfe67837d..28ffc2e73 100644
--- a/SL/CA.pm
+++ b/SL/CA.pm
@@ -34,7 +34,6 @@
 #
 #======================================================================
 
-
 package CA;
 use Data::Dumper;
 
@@ -44,6 +43,7 @@ sub all_accounts {
   my ($self, $myconfig, $form) = @_;
 
   my $amount = ();
+
   # connect to database
   my $dbh = $form->dbconnect($myconfig);
 
@@ -56,10 +56,10 @@ sub all_accounts {
   $sth->execute || $form->dberror($query);
 
   while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
-    $amount{$ref->{accno}} = $ref->{amount}
+    $amount{ $ref->{accno} } = $ref->{amount};
   }
   $sth->finish;
- 
+
   $query = qq|SELECT accno, description
               FROM gifi|;
   $sth = $dbh->prepare($query);
@@ -77,10 +77,10 @@ sub all_accounts {
 	      ORDER BY accno|;
   $sth = $dbh->prepare($query);
   $sth->execute || $form->dberror($query);
- 
+
   while (my $ca = $sth->fetchrow_hashref(NAME_lc)) {
-    $ca->{amount} = $amount{$ca->{accno}};
-    $ca->{gifi_description} = $gifi{$ca->{gifi_accno}};
+    $ca->{amount}           = $amount{ $ca->{accno} };
+    $ca->{gifi_description} = $gifi{ $ca->{gifi_accno} };
     if ($ca->{amount} < 0) {
       $ca->{debit} = $ca->{amount} * -1;
     } else {
@@ -95,7 +95,6 @@ sub all_accounts {
   $main::lxdebug->leave_sub();
 }
 
-
 sub all_transactions {
   $main::lxdebug->enter_sub();
 
@@ -122,25 +121,27 @@ sub all_transactions {
 
   my $fromdate_where;
   my $todate_where;
-  
+
   my $where = '1 = 1';
+
   # build WHERE clause from dates if any
-#  if ($form->{fromdate}) {
-#    $where .= " AND ac.transdate >= '$form->{fromdate}'";
-#  }
-#  if ($form->{todate}) {
-#    $where .= " AND ac.transdate <= '$form->{todate}'";
-#  }
-  
+  #  if ($form->{fromdate}) {
+  #    $where .= " AND ac.transdate >= '$form->{fromdate}'";
+  #  }
+  #  if ($form->{todate}) {
+  #    $where .= " AND ac.transdate <= '$form->{todate}'";
+  #  }
+
   if ($form->{fromdate}) {
-      $fromto = " AND ac.transdate >= '$form->{fromdate}'";
-      $subwhere .= " AND transdate >= '$form->{fromdate}'";
-      $glwhere = " AND ac.transdate >= '$form->{fromdate}'";
+    $fromto = " AND ac.transdate >= '$form->{fromdate}'";
+    $subwhere .= " AND transdate >= '$form->{fromdate}'";
+    $glwhere = " AND ac.transdate >= '$form->{fromdate}'";
   }
 
   if ($form->{todate}) {
-    $fromto .= " AND ac.transdate <= '$form->{todate}'";
+    $fromto   .= " AND ac.transdate <= '$form->{todate}'";
     $subwhere .= " AND transdate <= '$form->{todate}'";
+    $glwhere .= " AND ac.transdate <= '$form->{todate}'";
   }
 
   if ($form->{eur}) {
@@ -160,31 +161,31 @@ sub all_transactions {
 		     WHERE link LIKE '%AP_paid%'
 		     $subwhere
 		   )|;
-    } else {
-      $where .= $fromto;
-      $AR_PAID = "";
-      $AP_PAID = "";
-      $glwhere = "";
+  } else {
+    $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'|;
-  
+  my $sortorder = join ', ',
+    $form->sort_columns(qw(transdate reference description));
+  my $false = ($myconfig->{dbdriver} eq 'Pg') ? FALSE: q|'0'|;
+
   # Oracle workaround, use ordinal positions
-  my %ordinal = ( transdate => 4,
-		  reference => 2,
-		  description => 3 );
+  my %ordinal = (transdate   => 4,
+                 reference   => 2,
+                 description => 3);
   map { $sortorder =~ s/$_/$ordinal{$_}/ } keys %ordinal;
 
-
   my ($null, $department_id) = split /--/, $form->{department};
   my $dpt_where;
   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
 		  |;
   }
 
@@ -196,6 +197,7 @@ sub all_transactions {
   }
 
   if ($form->{accno} || $form->{gifi_accno}) {
+
     # get category for account
     $query = qq|SELECT c.category
                 FROM chart c
@@ -213,7 +215,7 @@ sub all_transactions {
     $sth->execute || $form->dberror($query);
     ($form->{category}) = $sth->fetchrow_array;
     $sth->finish;
-    
+
     if ($form->{fromdate}) {
 
       # get beginning balance
@@ -229,11 +231,11 @@ sub all_transactions {
 
       if ($form->{project_id}) {
 
-	$query .= qq|
+        $query .= qq|
 
 	       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)
@@ -247,7 +249,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)
@@ -272,14 +274,14 @@ sub all_transactions {
 		  $dpt_where
 		  $project
 		  |;
-		  
-	if ($form->{project_id}) {
 
-	  $query .= qq|
+        if ($form->{project_id}) {
+
+          $query .= qq|
 
 	       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)
@@ -293,7 +295,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)
@@ -306,9 +308,9 @@ sub all_transactions {
 		  $project
 		  |;
 
-	}
+        }
       }
-      
+
       $sth = $dbh->prepare($query);
 
       $sth->execute || $form->dberror($query);
@@ -320,25 +322,34 @@ sub all_transactions {
   $query = "";
   my $union = "";
 
-   foreach my $id (@id) {
-    
+  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
@@ -347,30 +358,30 @@ 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
 		$AP_PAID
 		AND a.vendor_id = v.id
 		|;
-      $union = qq|
+    $union = qq|
       UNION ALL
       |;
 
     if ($form->{project_id}) {
 
       $fromdate_where =~ s/ac\./a\./;
-      $todate_where =~ s/ac\./a\./;
-      
+      $todate_where   =~ s/ac\./a\./;
+
       $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)
@@ -381,11 +392,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)
@@ -397,13 +408,13 @@ sub all_transactions {
 		 $dpt_where
 		 $project
 		 |;
-		 
+
       $fromdate_where =~ s/a\./ac\./;
-      $todate_where =~ s/a\./ac\./;
- 
+      $todate_where   =~ s/a\./ac\./;
+
     }
-		 
-      $union = qq|
+
+    $union = qq|
              UNION ALL
                  |;
   }
@@ -415,7 +426,7 @@ sub all_transactions {
   $sth->execute || $form->dberror($query);
 
   while (my $ca = $sth->fetchrow_hashref(NAME_lc)) {
-    
+
     # gl
     if ($ca->{module} eq "gl") {
       $ca->{module} = "gl";
@@ -432,17 +443,17 @@ sub all_transactions {
     }
 
     if ($ca->{amount} < 0) {
-      $ca->{debit} = $ca->{amount} * -1;
+      $ca->{debit}  = $ca->{amount} * -1;
       $ca->{credit} = 0;
     } else {
       $ca->{credit} = $ca->{amount};
-      $ca->{debit} = 0;
+      $ca->{debit}  = 0;
     }
 
     push @{ $form->{CA} }, $ca;
-    
+
   }
- 
+
   $sth->finish;
   $dbh->disconnect;
 
@@ -450,4 +461,3 @@ sub all_transactions {
 }
 
 1;
-