X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FCA.pm;h=91df43cace0a74b8d228875a231aa856bccfd818;hb=69da19a7739b94fa368cec6092a83e37542dac9d;hp=1a77102391f3db0662d554fd464c4bf2db4e9633;hpb=e0d47e23c8ef3863f7c2387e49e2207f9140f0db;p=kivitendo-erp.git

diff --git a/SL/CA.pm b/SL/CA.pm
index 1a7710239..91df43cac 100644
--- a/SL/CA.pm
+++ b/SL/CA.pm
@@ -71,76 +71,36 @@ sub all_accounts {
   }
   $sth->finish;
 
-  $query = qq|
-
-    SELECT c.id,
+  $query = qq{
+    SELECT 
       c.accno,
+      c.id,
       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,
-
+      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
+      FROM chart c2
+      WHERE c2.id = c.id
       ) AS new_account
     FROM chart c
-    ORDER BY accno
-  |;
+    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);