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

diff --git a/SL/CA.pm b/SL/CA.pm
index dfe67837d..1a7710239 100644
--- a/SL/CA.pm
+++ b/SL/CA.pm
@@ -34,7 +34,6 @@
 #
 #======================================================================
 
-
 package CA;
 use Data::Dumper;
 
@@ -43,7 +42,8 @@ sub all_accounts {
 
   my ($self, $myconfig, $form) = @_;
 
-  my $amount = ();
+  my %amount;
+
   # connect to database
   my $dbh = $form->dbconnect($myconfig);
 
@@ -56,31 +56,98 @@ 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);
   $sth->execute || $form->dberror($query);
 
-  my $gifi = ();
+  my %gifi;
   while (my ($accno, $description) = $sth->fetchrow_array) {
     $gifi{$accno} = $description;
   }
   $sth->finish;
 
-  $query = qq|SELECT c.id, c.accno, c.description, c.charttype, c.gifi_accno,
-              c.category, c.link
-              FROM chart c
-	      ORDER BY accno|;
+  $query = qq|
+
+    SELECT c.id,
+      c.accno,
+      c.description,
+      c.charttype,
+      c.gifi_accno,
+      c.category,
+      c.link,
+      c.pos_ustva,
+      c.pos_bwa,
+      c.pos_bilanz,
+      c.pos_eur,
+      c.valid_from,
+      c.datevautomatik,
+--      ( SELECT comma(taxkey_id)
+--        FROM taxkeys tk
+--        WHERE tk.chart_id = c.id
+--          AND c.taxkey_id = tk.taxkey_id
+--        ORDER BY c.id
+--      ) AS taxkey_id,
+
+      ( SELECT comma(taxkey)
+        FROM tax tx
+        WHERE tx.id in (
+          SELECT tk.tax_id from taxkeys tk 
+          WHERE tk.chart_id = c.id
+        ) 
+        ORDER BY c.accno
+      ) AS taxkey,
+
+      ( SELECT comma(taxdescription)
+        FROM tax tx
+        WHERE tx.id in (
+          SELECT tk.tax_id from taxkeys tk 
+          WHERE tk.chart_id = c.id
+        ) 
+        ORDER BY c.accno
+      ) AS taxdescription,
+      
+      ( SELECT comma(taxnumber)
+        FROM tax tx
+        WHERE tx.id in (
+          SELECT tk.tax_id from taxkeys tk 
+          WHERE tk.chart_id = c.id
+        ) 
+        ORDER BY c.accno
+      ) AS taxaccount,
+
+      ( SELECT comma(tk.pos_ustva)
+        FROM taxkeys tk
+        WHERE tk.chart_id = c.id
+          AND c.taxkey_id = tk.taxkey_id
+        ORDER BY c.id
+      ) AS tk_ustva,
+
+      ( SELECT comma(startdate)
+        FROM taxkeys tk
+        WHERE tk.chart_id = c.id
+          AND c.taxkey_id = tk.taxkey_id
+        ORDER BY c.id
+      ) AS startdate,
+
+      ( SELECT accno
+        FROM chart c2
+        WHERE c2.id = c.id
+      ) AS new_account
+    FROM chart c
+    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 +162,6 @@ sub all_accounts {
   $main::lxdebug->leave_sub();
 }
 
-
 sub all_transactions {
   $main::lxdebug->enter_sub();
 
@@ -122,69 +188,53 @@ 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}) {
-    $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 = "";
-  }
-  my $sortorder = join ', ', $form->sort_columns(qw(transdate reference description));
-  my $false = ($myconfig->{dbdriver} eq 'Pg') ? FALSE : q|'0'|;
-  
+
+  $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'|;
+
   # 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 +246,7 @@ sub all_transactions {
   }
 
   if ($form->{accno} || $form->{gifi_accno}) {
+
     # get category for account
     $query = qq|SELECT c.category
                 FROM chart c
@@ -213,7 +264,7 @@ sub all_transactions {
     $sth->execute || $form->dberror($query);
     ($form->{category}) = $sth->fetchrow_array;
     $sth->finish;
-    
+
     if ($form->{fromdate}) {
 
       # get beginning balance
@@ -229,11 +280,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 +298,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 +323,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 +344,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 +357,9 @@ sub all_transactions {
 		  $project
 		  |;
 
-	}
+        }
       }
-      
+
       $sth = $dbh->prepare($query);
 
       $sth->execute || $form->dberror($query);
@@ -320,25 +371,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 +407,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 +441,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 +457,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 +475,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 +492,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 +510,3 @@ sub all_transactions {
 }
 
 1;
-