X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FCA.pm;h=91df43cace0a74b8d228875a231aa856bccfd818;hb=527617f281c24f7164b44b2ba0e389463f0571c8;hp=3517a3488d3b992b92172f9a072bec43da4f529b;hpb=ee2ec4e93920b1ce9f00aee7a31fd5cc70894a54;p=kivitendo-erp.git

diff --git a/SL/CA.pm b/SL/CA.pm
index 3517a3488..91df43cac 100644
--- 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,16 +65,43 @@ 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;
   }
   $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.accno,
+      c.id,
+      c.description,
+      c.charttype,
+      c.category,
+      c.link,
+      c.pos_bwa,
+      c.pos_bilanz,
+      c.pos_eur,
+      c.valid_from,
+      c.datevautomatik,
+      comma(tk.startdate) AS startdate,
+      comma(tk.taxkey_id) AS taxkey,
+      comma(tx.taxdescription || to_char (tx.rate, '99V99' ) || '%') AS taxdescription,
+      comma(tx.taxnumber) AS taxaccount,
+      comma(tk.pos_ustva) AS tk_ustva,
+      ( SELECT accno
+      FROM chart c2
+      WHERE c2.id = c.id
+      ) AS new_account
+    FROM chart c
+    LEFT JOIN taxkeys tk ON (c.id = tk.chart_id)
+    LEFT JOIN tax tx ON (tk.tax_id = tx.id)
+    GROUP BY c.accno, c.id, c.description, c.charttype, c.gifi_accno,
+      c.category, c.link, c.pos_bwa, c.pos_bilanz, c.pos_eur, c.valid_from,      
+      c.datevautomatik
+    ORDER BY c.accno
+  };
+
   $sth = $dbh->prepare($query);
   $sth->execute || $form->dberror($query);
 
@@ -141,31 +168,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 = ""; # note! gl will be aliased as "a" later...
-  }
+
+  $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'|;
@@ -323,14 +333,14 @@ 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.
+    # 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