X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=sql%2FPg-upgrade2%2Fsteuerfilterung.pl;h=b7be34167028578d4447cc4d650f2910a44f3cb7;hb=d21bdcaf73d55909d130fbbe26137fd45114adde;hp=741c6ad835b3de13a3cb5d21373fedda87e25221;hpb=87eebe6ce8ab9e35d305a6ffce3dd5bae36e844e;p=kivitendo-erp.git

diff --git a/sql/Pg-upgrade2/steuerfilterung.pl b/sql/Pg-upgrade2/steuerfilterung.pl
index 741c6ad83..b7be34167 100644
--- a/sql/Pg-upgrade2/steuerfilterung.pl
+++ b/sql/Pg-upgrade2/steuerfilterung.pl
@@ -1,6 +1,6 @@
 # @tag: steuerfilterung
 # @description: Steuern in Dialogbuchungen filtern.
-# @depends: release_3_0_0
+# @depends: release_3_0_0 tax_constraints
 package SL::DBUpgrade2::steuerfilterung;
 
 use strict;
@@ -15,8 +15,10 @@ sub run {
   my $categories;
   my $tax_id;
 
+  my $query = qq|ALTER TABLE tax ADD chart_categories TEXT|;
+  $self->db_query($query);
+
   if ( $::form->{continued_tax} ) {
-    my $update_query;
     foreach my $i (1 .. $::form->{rowcount}) {
       $tax_id = $::form->{"tax_id_$i"};
       $categories = '';
@@ -26,25 +28,22 @@ sub run {
       $categories .= 'C' if $::form->{"costs_$i"};
       $categories .= 'I' if $::form->{"revenue_$i"};
       $categories .= 'E' if $::form->{"expense_$i"};
-      $update_query = qq|UPDATE tax SET chart_categories = '$categories' WHERE id=$tax_id;|;
-      $self->db_query($update_query);
+      $self->db_query(qq|UPDATE tax SET chart_categories = ? WHERE id = ?|, bind => [ $categories, $tax_id ]);
     }
-    $update_query = qq|ALTER TABLE tax ALTER COLUMN chart_categories SET NOT NULL|;
-    $self->db_query($update_query);
-    $self->dbh->commit();
+    $self->db_query(qq|UPDATE tax SET chart_categories = 'ALQCIE' WHERE chart_categories IS NULL|);
+    $self->db_query(qq|ALTER TABLE tax ALTER COLUMN chart_categories SET NOT NULL|);
     return 1;
   }
 
-  my $query = qq|ALTER TABLE tax ADD chart_categories TEXT|;
-  $self->db_query($query);
-  $self->dbh->commit();
-
   my @well_known_taxes = (
+      # German SKR03
       { taxkey => 0,  rate => 0,    taxdescription => qr{keine.*steuer}i,                       categories => 'ALQCIE' },
       { taxkey => 1,  rate => 0,    taxdescription => qr{frei}i,                                categories => 'ALQCIE' },
       { taxkey => 2,  rate => 0.07, taxdescription => qr{umsatzsteuer}i,                        categories => 'I' },
       { taxkey => 3,  rate => 0.16, taxdescription => qr{umsatzsteuer}i,                        categories => 'I' },
       { taxkey => 3,  rate => 0.19, taxdescription => qr{umsatzsteuer}i,                        categories => 'I' },
+      { taxkey => 5,  rate => 0.16, taxdescription => qr{umsatzsteuer}i,                        categories => 'I' },
+      { taxkey => 7,  rate => 0.16, taxdescription => qr{vorsteuer}i,                           categories => 'E' },
       { taxkey => 8,  rate => 0.07, taxdescription => qr{vorsteuer}i,                           categories => 'E' },
       { taxkey => 9,  rate => 0.16, taxdescription => qr{vorsteuer}i,                           categories => 'E' },
       { taxkey => 9,  rate => 0.19, taxdescription => qr{vorsteuer}i,                           categories => 'E' },
@@ -53,10 +52,20 @@ sub run {
       { taxkey => 12, rate => 0.07, taxdescription => qr{steuerpflichtig.*lieferung.*erm}i,     categories => 'I' },
       { taxkey => 13, rate => 0.16, taxdescription => qr{steuerpflichtig.*lieferung.*voll}i,    categories => 'I' },
       { taxkey => 13, rate => 0.19, taxdescription => qr{steuerpflichtig.*lieferung.*voll}i,    categories => 'I' },
-      { taxkey => 18, rate => 0.07,  taxdescription => qr{innergem.*erwerb.*erm}i,              categories => 'E' },
+      { taxkey => 15, rate => 0.16, taxdescription => qr{steuerpflicht.*eg.*lieferung}i,        categories => 'I' },
+      { taxkey => 17, rate => 0.16, taxdescription => qr{steuerpflicht.*eg.*erwerb}i,           categories => 'E' },
+      { taxkey => 18, rate => 0.07, taxdescription => qr{innergem.*erwerb.*erm}i,               categories => 'E' },
       { taxkey => 19, rate => 0.16, taxdescription => qr{innergem.*erwerb.*voll}i,              categories => 'E' },
       { taxkey => 19, rate => 0.19, taxdescription => qr{innergem.*erwerb.*voll}i,              categories => 'E' },
-      );
+
+      # Swiss
+      { taxkey => 2,  rate => 0.08,  taxdescription => qr{mwst}i,                                categories => 'I' },
+      { taxkey => 3,  rate => 0.025, taxdescription => qr{mwst}i,                                categories => 'I' },
+      { taxkey => 4,  rate => 0.08,  taxdescription => qr{mwst}i,                                categories => 'E' },
+      { taxkey => 5,  rate => 0.025, taxdescription => qr{mwst}i,                                categories => 'E' },
+      { taxkey => 6,  rate => 0.08,  taxdescription => qr{mwst}i,                                categories => 'E' },
+      { taxkey => 7,  rate => 0.025, taxdescription => qr{mwst}i,                                categories => 'E' },
+  );
 
   $query = qq|SELECT taxkey, taxdescription, rate, id AS tax_id FROM tax order by taxkey, rate;|;
 
@@ -73,12 +82,9 @@ sub run {
       && ($ref->{taxdescription} =~ $_->{taxdescription})
     } @well_known_taxes;
     if ($well_known_tax) {
-      $categories = $well_known_tax->{categories};
-      $tax_id = $ref->{tax_id};
-      $query = qq|UPDATE tax SET chart_categories = '$categories' WHERE id=$tax_id;|;
-      $self->db_query($query);
+      $self->db_query(qq|UPDATE tax SET chart_categories = ? WHERE id = ?|, bind => [ $well_known_tax->{categories}, $ref->{tax_id} ]);
     } else {
-      $ref->{rate} = $::form->format_amount(\%::myconfig, $::form->round_amount($ref->{rate} * 100));
+      $ref->{rate} = $::form->format_amount(\%::myconfig, $ref->{rate} * 100);
       push @{ $::form->{PARTS} }, $ref;
     }
   }