X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;ds=sidebyside;f=SL%2FUSTVA.pm;h=31b13bd036be373a5b292560811c7699de363ff5;hb=1e2a4a3836d317a33166b34f514ca6863e4824c1;hp=09f56836aa30b36eb518f57b9cdb8a9acbd63338;hpb=785ef602f0f48db5ebc7f29c621b24277a71d0fe;p=kivitendo-erp.git

diff --git a/SL/USTVA.pm b/SL/USTVA.pm
index 09f56836a..31b13bd03 100644
--- a/SL/USTVA.pm
+++ b/SL/USTVA.pm
@@ -597,6 +597,27 @@ sub ustva {
   &get_accounts_ustva($dbh, $last_period, $form->{fromdate}, $form->{todate},
                       $form, $category);
 
+
+  # 16%/19% Umstelung
+  # Umordnen der Kennziffern und abfangen von Fehlern 
+
+#  $form->header;
+#  print $form->{81};
+
+  if ( $form->{year} < 2007) {
+    $form->{35} += $form->{81};
+    $form->{36} += $form->{811};
+    $form->{95} += $form->{89};
+    $form->{98} += $form->{891};
+    map { delete $form->{$_} } qw(81 811 89 891);
+  } else {
+    $form->{35} += $form->{51};
+    $form->{36} += $form->{511};
+    $form->{95} += $form->{97};
+    $form->{98} += $form->{971};
+    map { delete $form->{$_} } qw(51 511 97 971);
+  }
+
   #
   # Berechnung der USTVA Formularfelder laut Bogen 207
   #
@@ -694,7 +715,8 @@ sub get_accounts_ustva {
      ( 
        SELECT SUM(acc.amount)
        FROM acc_trans acc
-       INNER JOIN chart c ON (acc.chart_id = c.id AND c.link like '%AR_paid%')
+       INNER JOIN chart c ON (acc.chart_id   =   c.id 
+                              AND c.link   like  '%AR_paid%')
        WHERE
         1=1 
         $ARwhere
@@ -705,47 +727,73 @@ sub get_accounts_ustva {
       select amount from ar where id = ac.trans_id  
      )
    ) AS amount,
-   c.pos_ustva
+   tk.pos_ustva
    FROM acc_trans ac
-   JOIN chart c ON (c.id = ac.chart_id)
-   --JOIN ar ON (ar.id = ac.trans_id)
-   where 
+   LEFT JOIN chart c ON (c.id  = ac.chart_id)
+   LEFT JOIN ar      ON (ar.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 startdate <= COALESCE(ar.deliverydate, ar.transdate)
+       ORDER BY startdate DESC LIMIT 1
+     )
+   )
+   WHERE 
      1=1 
      -- Here no where, please. All Transactions ever should be
      -- testet if they are paied in the USTVA report period.
-   GROUP BY c.pos_ustva
+   GROUP BY tk.pos_ustva
 
  UNION -- alle Ausgaben AP erfassen
 
      SELECT
-     sum(ac.amount) AS amount, pos_ustva
+     sum(ac.amount) AS amount, 
+     tk.pos_ustva
      FROM acc_trans ac
      JOIN AP ON (AP.id = ac.trans_id )
-     JOIN chart c ON (c.id = ac.chart_id AND pos_ustva NOT LIKE '')
+     JOIN chart c ON (c.id = ac.chart_id)
+     LEFT JOIN taxkeys tk ON (
+       tk.id = (
+         SELECT id FROM taxkeys 
+         WHERE chart_id=ac.chart_id 
+           AND taxkey_id=ac.taxkey 
+           AND startdate <= COALESCE(AP.transdate)
+         ORDER BY startdate DESC LIMIT 1
+       )
+     )
      WHERE
        1=1
        $APwhere
        $dpt_where
        $project
-     GROUP BY pos_ustva
+     GROUP BY tk.pos_ustva
 
  UNION -- alle Ausgaben und Einnahmen direkter gl Buchungen erfassen
 
    SELECT sum
-   (
-     CASE WHEN c.link LIKE '%AR%' THEN ac.amount * -1
-          WHEN c.link LIKE '%AP%' THEN ac.amount * 1
-     END
-   ) AS amount, c.$category
+   ( - 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 startdate <= COALESCE(ac.transdate)
+       ORDER BY startdate DESC LIMIT 1
+     )
+   )
+
    $dpt_join
    WHERE $where
    $dpt_from
-   AND NOT (c.link = 'AR' OR c.link = 'AP')
    $project
-   GROUP BY c.$category
+   GROUP BY tk.pos_ustva
    |;
 
   } else {