epic-ts

[mfinanz.git] / SL / USTVA.pm

diff --git a/SL/USTVA.pm b/SL/USTVA.pm
index 83551a6c0cd8b6b30d6f31f9ed19d946604605f3..ee2d21f78252815877764987ca08a37feaaca98e 100644 (file)
--- a/SL/USTVA.pm
+++ b/SL/USTVA.pm
@@ -26,12 +26,15 @@
 
 package USTVA;
 
 
 package USTVA;
 

+use Carp;
+use Data::Dumper;

 use List::Util qw(first);
 
 use SL::DB;
 use SL::DBUtils;
 use SL::DB::Default;
 use SL::DB::Finanzamt;
 use List::Util qw(first);
 
 use SL::DB;
 use SL::DBUtils;
 use SL::DB::Default;
 use SL::DB::Finanzamt;

+use SL::Locale::String qw(t8);

 
 use utf8;
 use strict;
 
 use utf8;
 use strict;


@@ -523,6 +526,9 @@ sub ustva {
 
   $form->{coa} = $::instance_conf->get_coa;
 
 
   $form->{coa} = $::instance_conf->get_coa;
 

+  unless ($form->{coa} eq 'Germany-DATEV-SKR03EU' or $form->{coa} eq 'Germany-DATEV-SKR04EU') {
+    croak t8("Advance turnover tax return only valid for SKR03 or SKR04");
+  }

   my @category_cent = USTVA->report_variables({
       myconfig    => $myconfig,
       form        => $form,
   my @category_cent = USTVA->report_variables({
       myconfig    => $myconfig,
       form        => $form,


@@ -568,7 +574,7 @@ sub ustva {
 
   # Germany
 
 
   # Germany
 

-  if ( $form->{coa} eq 'Germany-DATEV-SKR03EU' or $form->{coa} eq 'Germany-DATEV-SKR04EU'){
+  if ( $form->{coa} eq 'Germany-DATEV-SKR03EU' or $form->{coa} eq 'Germany-DATEV-SKR04EU') {

 
     # 16%/19% Umstellung
     # Umordnen der Kennziffern
 
     # 16%/19% Umstellung
     # Umordnen der Kennziffern


@@ -687,7 +693,7 @@ sub get_accounts_ustva {
          SUM( ac.amount *
             -- Bezahlt / Rechnungssumme
            (
          SUM( ac.amount *
             -- Bezahlt / Rechnungssumme
            (

-             SELECT SUM(acc.amount), t.rate
+             SELECT SUM(acc.amount)

              FROM acc_trans acc
              INNER JOIN chart c ON (acc.chart_id   =   c.id
                                     AND c.link   like  '%AR_paid%')
              FROM acc_trans acc
              INNER JOIN chart c ON (acc.chart_id   =   c.id
                                     AND c.link   like  '%AR_paid%')


@@ -695,13 +701,12 @@ sub get_accounts_ustva {
               1=1
               $ARwhere
               AND acc.trans_id = ac.trans_id
               1=1
               $ARwhere
               AND acc.trans_id = ac.trans_id

-              )
-           /
+              )           /

            (
             SELECT amount FROM ar WHERE id = ac.trans_id
            )
          ) AS amount,
            (
             SELECT amount FROM ar WHERE id = ac.trans_id
            )
          ) AS amount,

-         tk.pos_ustva
+         tk.pos_ustva,  t.rate, c.accno

        FROM acc_trans ac
        LEFT JOIN chart c ON (c.id  = ac.chart_id)
        LEFT JOIN ar      ON (ar.id = ac.trans_id)
        FROM acc_trans ac
        LEFT JOIN chart c ON (c.id  = ac.chart_id)
        LEFT JOIN ar      ON (ar.id = ac.trans_id)


@@ -717,7 +722,7 @@ sub get_accounts_ustva {
        )
        WHERE
        $acc_trans_where
        )
        WHERE
        $acc_trans_where

-       GROUP BY tk.pos_ustva, t.rate
+       GROUP BY tk.pos_ustva, t.rate, c.accno

     |;
 
   } elsif ($form->{accounting_method} eq 'accrual') {
     |;
 
   } elsif ($form->{accounting_method} eq 'accrual') {


@@ -729,7 +734,7 @@ sub get_accounts_ustva {
        -- Alle Einnahmen AR und pos_ustva erfassen
        SELECT
          - sum(ac.amount) AS amount,
        -- Alle Einnahmen AR und pos_ustva erfassen
        SELECT
          - sum(ac.amount) AS amount,

-         tk.pos_ustva, t.rate
+         tk.pos_ustva, t.rate, c.accno

        FROM acc_trans ac
        JOIN chart c ON (c.id = ac.chart_id)
        JOIN ar ON (ar.id = ac.trans_id)
        FROM acc_trans ac
        JOIN chart c ON (c.id = ac.chart_id)
        JOIN ar ON (ar.id = ac.trans_id)


@@ -745,7 +750,7 @@ sub get_accounts_ustva {
        $dpt_join
        WHERE 1 = 1
        $where
        $dpt_join
        WHERE 1 = 1
        $where

-       GROUP BY tk.pos_ustva, t.rate
+       GROUP BY tk.pos_ustva, t.rate, c.accno

   |;
 
   } else {
   |;
 
   } else {


@@ -763,7 +768,7 @@ sub get_accounts_ustva {
 
        SELECT
          sum(ac.amount) AS amount,
 
        SELECT
          sum(ac.amount) AS amount,

-         tk.pos_ustva, t.rate
+         tk.pos_ustva, t.rate, c.accno

        FROM acc_trans ac
        JOIN ap ON (ap.id = ac.trans_id )
        JOIN chart c ON (c.id = ac.chart_id)
        FROM acc_trans ac
        JOIN ap ON (ap.id = ac.trans_id )
        JOIN chart c ON (c.id = ac.chart_id)


@@ -781,13 +786,13 @@ sub get_accounts_ustva {
        WHERE
        1=1
        $where
        WHERE
        1=1
        $where

-       GROUP BY tk.pos_ustva, t.rate
+       GROUP BY tk.pos_ustva, t.rate, c.accno

 
      UNION -- Einnahmen direkter gl Buchungen erfassen
 
        SELECT sum
          ( - ac.amount) AS amount,
 
      UNION -- Einnahmen direkter gl Buchungen erfassen
 
        SELECT sum
          ( - ac.amount) AS amount,

-         tk.pos_ustva, t.rate
+         tk.pos_ustva, t.rate, c.accno

        FROM acc_trans ac
        JOIN chart c ON (c.id = ac.chart_id)
        JOIN gl a ON (a.id = ac.trans_id)
        FROM acc_trans ac
        JOIN chart c ON (c.id = ac.chart_id)
        JOIN gl a ON (a.id = ac.trans_id)


@@ -805,14 +810,14 @@ sub get_accounts_ustva {
        $dpt_join
        WHERE 1 = 1
        $where
        $dpt_join
        WHERE 1 = 1
        $where

-       GROUP BY tk.pos_ustva, t.rate
+       GROUP BY tk.pos_ustva, t.rate, c.accno

 
 
      UNION -- Ausgaben direkter gl Buchungen erfassen
 
        SELECT sum
          (ac.amount) AS amount,
 
 
      UNION -- Ausgaben direkter gl Buchungen erfassen
 
        SELECT sum
          (ac.amount) AS amount,

-         tk.pos_ustva, t.rate
+         tk.pos_ustva, t.rate, c.accno

        FROM acc_trans ac
        JOIN chart c ON (c.id = ac.chart_id)
        JOIN gl a ON (a.id = ac.trans_id)
        FROM acc_trans ac
        JOIN chart c ON (c.id = ac.chart_id)
        JOIN gl a ON (a.id = ac.trans_id)


@@ -830,14 +835,10 @@ sub get_accounts_ustva {
        $dpt_join
        WHERE 1 = 1
        $where
        $dpt_join
        WHERE 1 = 1
        $where

-       GROUP BY tk.pos_ustva, t.rate
+       GROUP BY tk.pos_ustva, t.rate, c.accno

 
   |;
 
 
   |;
 

-  my @accno;
-  my $accno;
-  my $ref;
-

   # Show all $query in Debuglevel LXDebug::QUERY
   my $callingdetails = (caller (0))[3];
   $main::lxdebug->message(LXDebug->QUERY(), "$callingdetails \$query=\n $query");
   # Show all $query in Debuglevel LXDebug::QUERY
   my $callingdetails = (caller (0))[3];
   $main::lxdebug->message(LXDebug->QUERY(), "$callingdetails \$query=\n $query");


@@ -845,29 +846,53 @@ sub get_accounts_ustva {
   my $sth = $dbh->prepare($query);
 
   $sth->execute || $form->dberror($query);
   my $sth = $dbh->prepare($query);
 
   $sth->execute || $form->dberror($query);

+  # ugly, but we need to use static accnos
+  my ($accno_five, $accno_sixteen, $corr);
+
+  if ($form->{coa} eq 'Germany-DATEV-SKR03EU') {
+    $accno_five     = 1773;
+    $accno_sixteen  = 1775;
+  } elsif (($form->{coa} eq 'Germany-DATEV-SKR04EU')) {
+    $accno_five     = 3803; # SKR04
+    $accno_sixteen  = 3805; # SKR04
+  } else {die "wrong call"; }

 
   while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
     next unless $ref->{$category};
 
   while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
     next unless $ref->{$category};

+    $corr = 0;

     $ref->{amount} *= -1;
     $ref->{amount} *= -1;

-    $form->{ $ref->{$category} } += $ref->{amount};
-
-    # umsatzsteuer 16% temp
-    if ($ref->{rate} == 0.16 && $ref->{pos_ustva} ne '66') {
-      if ($ref->{pos_ustva} eq '35') {
+    # USTVA Pos 35
+    if ($ref->{pos_ustva} eq '35') {
+      if ($ref->{rate} == 0.16) {

         $form->{"pos_ustva_81b_kivi"} += $ref->{amount};
         $form->{"pos_ustva_81b_kivi"} += $ref->{amount};

-      } elsif ($ref->{pos_ustva} eq '36') {
-        $form->{"pos_ustva_811b_kivi"} += $ref->{amount};
-      } else { die "Kein pos_ustva Eintrag!" . Dumper($ref); }
-    }
-    # umsatzsteuer 5% temp
-    if ($ref->{rate} == 0.05 && $ref->{pos_ustva} ne '66') {
-      if ($ref->{pos_ustva} eq '35') {
+      } elsif ($ref->{rate} == 0.05) {

         $form->{"pos_ustva_86b_kivi"} += $ref->{amount};
         $form->{"pos_ustva_86b_kivi"} += $ref->{amount};

-      } elsif ($ref->{pos_ustva} eq '36') {
+      } elsif ($ref->{rate} == 0.19) {
+        # pos_ustva says 16, but rate says 19
+        # (pos_ustva should be tax dependent and not taxkeys dependent)
+        # correction hotfix for this case:
+        # bookings exists with 19% ->
+        # move 19% bookings to the 19% position
+        # Dont rely on dates of taxkeys
+        $corr = 1;
+        $form->{"81"} += $ref->{amount};
+      }  elsif ($ref->{rate} == 0.07) {
+        # pos_ustva says 5, but rate says 7
+        # see comment above:
+        # Dont rely on dates of taxkeys
+        $corr = 1;
+        $form->{"86"} += $ref->{amount};
+      } else {die ("No valid tax rate for pos 35" . Dumper($ref)); }
+    }
+    # USTVA Pos 36 (Steuerkonten)
+    if ($ref->{pos_ustva} eq '36') {
+      if ($ref->{accno} =~ /^$accno_sixteen/) {
+        $form->{"pos_ustva_811b_kivi"} += $ref->{amount};
+      } elsif ($ref->{accno} =~ /^$accno_five/) {

         $form->{"pos_ustva_861b_kivi"} += $ref->{amount};
         $form->{"pos_ustva_861b_kivi"} += $ref->{amount};

-      } else { die "Kein pos_ustva Eintrag!" . Dumper($ref); }
+      } else { die ("No valid accno for pos 36" . Dumper($ref)); }

     }
     }

-
+  $form->{ $ref->{$category} } += $ref->{amount} unless $corr;

   }
 
   $sth->finish;
   }
 
   $sth->finish;