From 1a344dd59350e494024e81054bc6c752b08c668c Mon Sep 17 00:00:00 2001
From: Philip Reetz <p.reetz@linet-services.de>
Date: Mon, 26 Feb 2007 14:31:24 +0000
Subject: [PATCH] 2 Fehler im USTVA Modul beseitigt. Zwei Steuerkonten wurden
 nicht beruecksichtigt und bei GL-Buchungen wurde die Steuer nicht immer
 korrekt aufsummiert

---
 SL/USTVA.pm                                   | 42 ++++++++++++++++---
 ...t_taxkeys_tax_add_missing_tax_accounts.sql | 26 ++++++++++++
 2 files changed, 62 insertions(+), 6 deletions(-)
 create mode 100644 sql/Pg-upgrade2/ustva_setup_2007_update_chart_taxkeys_tax_add_missing_tax_accounts.sql

diff --git a/SL/USTVA.pm b/SL/USTVA.pm
index 4ac3c1a2c..d7e2e428e 100644
--- a/SL/USTVA.pm
+++ b/SL/USTVA.pm
@@ -664,14 +664,15 @@ sub get_accounts_ustva {
   my $APwhere  = '';
   my $arwhere  = "";
   my $item;
+  my $gltaxkey_where = "(tk.pos_ustva>=59 AND tk.pos_ustva<=66)";
 
   if ($fromdate) {
     if ($form->{method} eq 'cash') {
       $subwhere .= " AND transdate >= '$fromdate'";
       $glwhere = " AND ac.transdate >= '$fromdate'";
-      $ARwhere .= " AND acc.transdate >= '$fromdate'";
-      $APwhere .= " AND AP.transdate >= '$fromdate'"; 
+      $ARwhere .= " AND acc.transdate >= '$fromdate'"; 
     }
+    $APwhere .= " AND AP.transdate >= '$fromdate'";
     $where .= " AND ac.transdate >= '$fromdate'";
   }
 
@@ -738,7 +739,7 @@ sub get_accounts_ustva {
            SELECT id FROM taxkeys 
            WHERE chart_id   = ac.chart_id 
              -- AND taxkey_id  = ac.taxkey 
-             AND startdate <= COALESCE(ar.deliverydate, ar.transdate)
+             AND startdate <= COALESCE(ar.transdate)
            ORDER BY startdate DESC LIMIT 1
          )
        )
@@ -776,7 +777,7 @@ sub get_accounts_ustva {
          tk.id = (
            SELECT id FROM taxkeys 
            WHERE chart_id   = ac.chart_id 
-             AND startdate <= COALESCE(ar.deliverydate, ar.transdate)
+             AND startdate <= COALESCE(ar.transdate)
            ORDER BY startdate DESC LIMIT 1
          )
        )
@@ -823,7 +824,7 @@ sub get_accounts_ustva {
        $project
        GROUP BY tk.pos_ustva
 
-     UNION -- alle Ausgaben und Einnahmen direkter gl Buchungen erfassen
+     UNION -- Einnahmen direkter gl Buchungen erfassen
 
        SELECT sum
          ( - ac.amount) AS amount, 
@@ -835,7 +836,8 @@ sub get_accounts_ustva {
          tk.id = (
            SELECT id FROM taxkeys 
            WHERE chart_id=ac.chart_id 
-             --AND taxkey_id=ac.taxkey 
+             --AND taxkey_id=ac.taxkey
+             AND NOT $gltaxkey_where  
              AND startdate <= COALESCE(ac.transdate)
            ORDER BY startdate DESC LIMIT 1
          )
@@ -847,6 +849,34 @@ sub get_accounts_ustva {
        $dpt_from
        $project
        GROUP BY tk.pos_ustva
+
+
+     UNION -- Ausgaben direkter gl Buchungen erfassen
+
+       SELECT sum
+         (ac.amount) AS amount, 
+         tk.pos_ustva
+       FROM acc_trans ac
+       JOIN chart c ON (c.id = ac.chart_id)
+       JOIN gl a ON (a.id = ac.trans_id)
+       LEFT JOIN taxkeys tk ON (
+         tk.id = (
+           SELECT id FROM taxkeys 
+           WHERE chart_id=ac.chart_id 
+             --AND taxkey_id=ac.taxkey
+             AND $gltaxkey_where 
+             AND startdate <= COALESCE(ac.transdate)
+           ORDER BY startdate DESC LIMIT 1
+         )
+       )
+
+       $dpt_join
+       WHERE 1 = 1
+       $where
+       $dpt_from
+       $project
+       GROUP BY tk.pos_ustva
+
   |;
 
   my @accno;
diff --git a/sql/Pg-upgrade2/ustva_setup_2007_update_chart_taxkeys_tax_add_missing_tax_accounts.sql b/sql/Pg-upgrade2/ustva_setup_2007_update_chart_taxkeys_tax_add_missing_tax_accounts.sql
new file mode 100644
index 000000000..0026cccaf
--- /dev/null
+++ b/sql/Pg-upgrade2/ustva_setup_2007_update_chart_taxkeys_tax_add_missing_tax_accounts.sql
@@ -0,0 +1,26 @@
+-- @tag: ustva_setup_2007_update_chart_taxkeys_tax_add_missing_tax_accounts
+-- @description: Aktualisierung des Kontenrahmens SKR03, einfuegen der fehlenden Steuerkonten in die Tabelle taxkeys
+-- @depends: ustva_setup_2007_update_chart_taxkeys_tax
+
+
+
+--#############################################################
+--#
+--# Anpassungen Tabelle taxkeys
+--#
+--#############################################################
+
+
+INSERT INTO taxkeys (
+  chart_id, tax_id, taxkey_id, pos_ustva, startdate)
+  SELECT chart.id, '0', '0', '66', '1970-01-01'
+  FROM chart
+  LEFT JOIN tax ON (chart.id = tax.chart_id)
+  WHERE chart.accno in  ('1571', '1575')
+  AND
+  EXISTS ( -- update only for SKR03
+    SELECT coa FROM defaults 
+    WHERE defaults.coa='Germany-DATEV-SKR03EU'
+  )
+;
+
-- 
2.20.1