Anscheinend gab es irgendwo im Upgrade-Pfad Unstimmigkeiten, sodass nicht alle Datenb...

[kivitendo-erp.git] / SL / USTVA.pm

diff --git a/SL/USTVA.pm b/SL/USTVA.pm
index 97a8a6f..31b13bd 100644 (file)
--- a/SL/USTVA.pm
+++ b/SL/USTVA.pm
@@ -573,16 +573,16 @@ sub ustva {
   my $last_period     = 0;
   my $category        = "pos_ustva";
   my @category_cent = qw(
-     511 861 36  80  971 931 98  96 53 74
-     85  65  66  61  62  67  63  64 59 69 
-     39  83  Z43 Z45 Z53 Z62 Z65 Z67
+     511  861  36   80   971  931  98   96   53   74
+     85   65   66   61   62   67   63   64   59   69 
+     39   83   811  891  Z43  Z45  Z53  Z62  Z65  Z67
   );
 
   my @category_euro = qw(
      41 44 49 43 48 51 
      86 35 77 76 91 97 
      93 95 94 42 60 45 
-     52 73 84
+     52 73 84 81 89
   );
 
   $form->{decimalplaces} *= 1;
@@ -597,25 +597,52 @@ 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
+  # Berechnung der USTVA Formularfelder laut Bogen 207
   #
 
   $form->{"51r"} = $form->{"511"};
   $form->{"86r"} = $form->{"861"};
   $form->{"97r"} = $form->{"971"};
   $form->{"93r"} = $form->{"931"};
-  $form->{"Z43"} =
-  $form->{"511"} + $form->{"861"} + $form->{"36"} + $form->{"80"} +
-  $form->{"971"} + $form->{"931"} + $form->{"96"} + $form->{"98"};
+
+  $form->{"Z43"} = $form->{"511"}     + $form->{"811"} + $form->{"861"} 
+                     + $form->{"36"}  + $form->{"80"}  + $form->{"971"} 
+                     + $form->{"891"} + $form->{"931"} + $form->{"96"} 
+                     + $form->{"98"};
+
   $form->{"Z45"} = $form->{"Z43"};
-  $form->{"Z53"} = $form->{"Z43"};
-  $form->{"Z62"} =
-  $form->{"Z43"} - $form->{"66"} - $form->{"61"} - $form->{"62"} -
-  $form->{"63"} - $form->{"64"} - $form->{"59"};
-  $form->{"Z65"} = $form->{"Z62"} - $form->{"69"};
-  $form->{"83"}  = $form->{"Z65"} - $form->{"39"};
-  # Hier fehlen moeglicherweise noch einige Berechnungen!
+
+  $form->{"Z53"} = $form->{"Z45"}     + $form->{"53"}  + $form->{"74"}  
+                     + $form->{"85"}  + $form->{"65"};
+                     
+  $form->{"Z62"} = $form->{"Z43"}     - $form->{"66"}  - $form->{"61"} 
+                     - $form->{"62"}  - $form->{"67"}  - $form->{"63"}  
+                     - $form->{"64"}  - $form->{"59"};
+                      
+  $form->{"Z65"} = $form->{"Z62"}     - $form->{"69"};
+  $form->{"83"}  = $form->{"Z65"}     - $form->{"39"};
   
   $dbh->disconnect;
 
@@ -688,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
@@ -699,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 {