Spalte taxnumber aus Tabelle tax entfernt

tax.taxnumber war ein redundanter Eintrag, und entsprach dem Wert von
chart.accno aus tax.chart_id.

Z.B. in SKR04 hatte Steuerschlüssel 3 (Umsatzsteuer 19%) die taxnumber
1776 und die chart_id 775 (chart mit id 775 ist das Konto 1776).

Ein Problem dabei ist, daß wenn man in den Konteneinstellungen die
Kontonummer von 1776 ändert, dies nicht automatisch in tax.taxnumber mit
aktualisiert wurde.

Im Code wurde taxnumber v.A. verwendet, um bei Belegen die Steuern zu
gruppieren, mit der taxnumber als Schlüssel.

taxnumber wurde nun also entfernt, und obwohl zum Gruppieren der Steuern
immer noch diese Kontonummer verwendet wird, wird diese Kontonummer
nicht mehr zum Suchen des entsprechenden Taxeintrags verwendet, sondern
die Suche passiert indirekt über die chart_id.

Das ganze System basiert derzeit darauf, daß es für jeden tax-Eintrag ein
eindeutiges Automatikkonto gibt, in der Praxis muß dies aber nicht der
Fall sein!

diff --git a/SL/AM.pm b/SL/AM.pm
index a06f8b3096fb169a81336385e212b8a6e8a1f48c..b5a283abbfc87b19329fcd28abbafaa87f480312 100644 (file)
--- a/SL/AM.pm
+++ b/SL/AM.pm
@@ -1181,7 +1181,6 @@ sub _save_tax {
                   taxdescription           = ?,
                   rate                     = ?,
                   chart_id                 = ?,
                   taxdescription           = ?,
                   rate                     = ?,
                   chart_id                 = ?,

-                  taxnumber                = (SELECT accno FROM chart WHERE id = ? ),

                   skonto_sales_chart_id    = ?,
                   skonto_purchase_chart_id = ?,
                   chart_categories         = ?
                   skonto_sales_chart_id    = ?,
                   skonto_purchase_chart_id = ?,
                   chart_categories         = ?


@@ -1195,7 +1194,6 @@ sub _save_tax {
                   taxdescription,
                   rate,
                   chart_id,
                   taxdescription,
                   rate,
                   chart_id,

-                  taxnumber,

                   skonto_sales_chart_id,
                   skonto_purchase_chart_id,
                   chart_categories,
                   skonto_sales_chart_id,
                   skonto_purchase_chart_id,
                   chart_categories,

diff --git a/SL/CA.pm b/SL/CA.pm
index d954482135a859f2bc301eb1e4f270b081de5a31..85f8dfdb9e5c2fc01c39ba8d6ce1066e07f14840 100644 (file)
--- a/SL/CA.pm
+++ b/SL/CA.pm
@@ -104,7 +104,7 @@ sub all_accounts {
       comma(tk.startdate::text) AS startdate,
       comma(tk.taxkey_id::text) AS taxkey,
       comma(tx.taxdescription || to_char (tx.rate, '99V99' ) || '%') AS taxdescription,
       comma(tk.startdate::text) AS startdate,
       comma(tk.taxkey_id::text) AS taxkey,
       comma(tx.taxdescription || to_char (tx.rate, '99V99' ) || '%') AS taxdescription,

-      comma(tx.taxnumber::text) AS taxaccount,
+      comma(taxchart.accno::text) AS taxaccount,

       comma(tk.pos_ustva::text) AS tk_ustva,
       ( SELECT accno
       FROM chart c2
       comma(tk.pos_ustva::text) AS tk_ustva,
       ( SELECT accno
       FROM chart c2


@@ -113,6 +113,7 @@ sub all_accounts {
     FROM chart c
     LEFT JOIN taxkeys tk ON (c.id = tk.chart_id)
     LEFT JOIN tax tx ON (tk.tax_id = tx.id)
     FROM chart c
     LEFT JOIN taxkeys tk ON (c.id = tk.chart_id)
     LEFT JOIN tax tx ON (tk.tax_id = tx.id)

+    LEFT JOIN chart taxchart ON (taxchart.id = tx.chart_id)

     WHERE 1=1
     $where
     GROUP BY c.accno, c.id, c.description, c.charttype,
     WHERE 1=1
     $where
     GROUP BY c.accno, c.id, c.description, c.charttype,

diff --git a/SL/DB/MetaSetup/Tax.pm b/SL/DB/MetaSetup/Tax.pm
index f4a2e2d78cf83799c7414f840fc37efc15d4349b..55aba86bc4157018f190922966fec6fcbb51b19a 100644 (file)
--- a/SL/DB/MetaSetup/Tax.pm
+++ b/SL/DB/MetaSetup/Tax.pm
@@ -19,7 +19,6 @@ __PACKAGE__->meta->columns(
   skonto_sales_chart_id    => { type => 'integer' },
   taxdescription           => { type => 'text', not_null => 1 },
   taxkey                   => { type => 'integer', not_null => 1 },
   skonto_sales_chart_id    => { type => 'integer' },
   taxdescription           => { type => 'text', not_null => 1 },
   taxkey                   => { type => 'integer', not_null => 1 },

-  taxnumber                => { type => 'text' },

 );
 
 __PACKAGE__->meta->primary_key_columns([ 'id' ]);
 );
 
 __PACKAGE__->meta->primary_key_columns([ 'id' ]);

diff --git a/SL/IC.pm b/SL/IC.pm
index a2a0a86a8b7b77fad4edcc8865c1725778ba9234..7783c5f65ce3d06d86001e842cacabf52cc48a11 100644 (file)
--- a/SL/IC.pm
+++ b/SL/IC.pm
@@ -790,7 +790,8 @@ sub retrieve_accounts {
 SQL
 
   my $query_tax = <<SQL;
 SQL
 
   my $query_tax = <<SQL;

-    SELECT c.accno, t.taxdescription AS description, t.rate, t.taxnumber
+    SELECT c.accno, t.taxdescription AS description, t.rate,
+           c.accno as taxnumber

     FROM tax t
     LEFT JOIN chart c ON c.id = t.chart_id
     WHERE t.id IN
     FROM tax t
     LEFT JOIN chart c ON c.id = t.chart_id
     WHERE t.id IN

diff --git a/SL/IR.pm b/SL/IR.pm
index c85daa334086420d75a14f93ea874df8dd85f747..aec03dcc71d5e6cf0b3c76743fa764b7462df154 100644 (file)
--- a/SL/IR.pm
+++ b/SL/IR.pm
@@ -1071,7 +1071,9 @@ sub retrieve_invoice {
     # get tax rates and description
     my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
     $query =
     # get tax rates and description
     my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
     $query =

-      qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
+      qq|SELECT c.accno, t.taxdescription, t.rate,
+                c.accno as taxnumber   -- taxnumber is same as accno, but still accessed as taxnumber in code
+         FROM tax t

          LEFT JOIN chart c ON (c.id = t.chart_id)
          WHERE t.id in
            (SELECT tk.tax_id FROM taxkeys tk
          LEFT JOIN chart c ON (c.id = t.chart_id)
          WHERE t.id in
            (SELECT tk.tax_id FROM taxkeys tk


@@ -1339,7 +1341,7 @@ sub retrieve_item {
     # get tax rates and description
     my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
     $query =
     # get tax rates and description
     my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
     $query =

-      qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
+      qq|SELECT c.accno, t.taxdescription, t.rate, c.accno as taxnumber

          FROM tax t
          LEFT JOIN chart c on (c.id = t.chart_id)
          WHERE t.id IN
          FROM tax t
          LEFT JOIN chart c on (c.id = t.chart_id)
          WHERE t.id IN

diff --git a/SL/IS.pm b/SL/IS.pm
index 97e724811839c61feff671d4bef3f4580d3349c6..181743964b8266f80e22581fe94db63fd0914fcd 100644 (file)
--- a/SL/IS.pm
+++ b/SL/IS.pm
@@ -506,7 +506,25 @@ sub invoice_details {
     push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} },  $form->{"${item}_rate"} * 100);
     push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} },      $form->{"${item}_taxnumber"});
 
     push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} },  $form->{"${item}_rate"} * 100);
     push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} },      $form->{"${item}_taxnumber"});
 

-    my $tax_obj     = SL::DB::Manager::Tax->find_by(taxnumber => $form->{"${item}_taxnumber"});
+    # taxnumber is used for grouping the amount of the various taxes
+
+    # this code assumes that at most one tax entry can point to the same
+    # chart_id, even though chart_id does not have a unique constraint!
+
+    # this chart_id is then looked up via its accno, which is the key that is
+    # used to group the different taxes by for a record
+
+    # not every tax has a taxnumber (e.g. tax-free), but that is ok, because
+    # then there would be no tax amount to assign it to
+
+    my $tax_objs = SL::DB::Manager::Tax->get_objects_from_sql(
+      sql  => 'SELECT * FROM tax WHERE chart_id = (SELECT id FROM chart WHERE accno = ?)',
+      args => [ $form->{"${item}_taxnumber"} ]
+    );
+    my $tax_obj;
+    if ( $tax_objs ) {
+      $tax_obj     = $tax_objs->[0];
+    }

     my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription',  $form->{language_id}, 0) : '';
     push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
   }
     my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription',  $form->{language_id}, 0) : '';
     push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
   }


@@ -2069,7 +2087,8 @@ sub _retrieve_invoice {
       # get tax rates and description
       my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
       $query =
       # get tax rates and description
       my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
       $query =

-        qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
+        qq|SELECT c.accno, t.taxdescription, t.rate, c.accno as taxnumber
+           FROM tax t

            LEFT JOIN chart c ON (c.id = t.chart_id)
            WHERE t.id IN
              (SELECT tk.tax_id FROM taxkeys tk
            LEFT JOIN chart c ON (c.id = t.chart_id)
            WHERE t.id IN
              (SELECT tk.tax_id FROM taxkeys tk


@@ -2393,7 +2412,7 @@ sub retrieve_item {
     # get tax rates and description
     my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
     $query =
     # get tax rates and description
     my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
     $query =

-      qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
+      qq|SELECT c.accno, t.taxdescription, t.rate, c.accno as taxnumber

          FROM tax t
          LEFT JOIN chart c ON (c.id = t.chart_id)
          WHERE t.id in
          FROM tax t
          LEFT JOIN chart c ON (c.id = t.chart_id)
          WHERE t.id in

diff --git a/SL/OE.pm b/SL/OE.pm
index 74c9ffdfb874cbe6808fb2ca7d8b9b9df10dad0e..40b29f4a1b7dcfec0ed6f3877d0b4582cd96ac5e 100644 (file)
--- a/SL/OE.pm
+++ b/SL/OE.pm
@@ -1177,8 +1177,9 @@ sub _retrieve {
       # get tax rates and description
       my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
       $query =
       # get tax rates and description
       my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
       $query =

-        qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber | .
-        qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | .
+        qq|SELECT c.accno, t.taxdescription, t.rate, c.accno as taxnumber | .
+        qq|FROM tax t | .
+        qq|LEFT JOIN chart c on (c.id = t.chart_id) | .

         qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
         qq|               WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
         qq|                 AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
         qq|WHERE t.id IN (SELECT tk.tax_id FROM taxkeys tk | .
         qq|               WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
         qq|                 AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .


@@ -1574,7 +1575,14 @@ sub order_details {
     push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} },  $form->{"${item}_rate"} * 100);
     push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} },      $form->{"${item}_taxnumber"});
 
     push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} },  $form->{"${item}_rate"} * 100);
     push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} },      $form->{"${item}_taxnumber"});
 

-    my $tax_obj     = SL::DB::Manager::Tax->find_by(taxnumber => $form->{"${item}_taxnumber"});
+    my $tax_objs     = SL::DB::Manager::Tax->get_objects_from_sql(
+      sql  => 'SELECT * from tax where chart_id = (SELECT id FROM chart WHERE accno = ?)',
+      args => [ $form->{"${item}_taxnumber"} ]
+    );
+    my $tax_obj;
+    if ( $tax_objs ) {
+      $tax_obj     = $tax_objs->[0];
+    }

     my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription',  $form->{language_id}, 0) : '';
     push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
   }
     my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription',  $form->{language_id}, 0) : '';
     push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
   }

diff --git a/SL/Taxkeys.pm b/SL/Taxkeys.pm
index d921a9056716b6272d5ddd73bf1bf9a3a9a8e0ef..a682dc22cb421483e52426b5800161898fd3f4dc 100644 (file)
--- a/SL/Taxkeys.pm
+++ b/SL/Taxkeys.pm
@@ -60,7 +60,7 @@ sub get_tax_info {
 
   if (!$self->{handles}->{get_tax_info}) {
     $self->{queries}->{get_tax_info} = qq|
 
   if (!$self->{handles}->{get_tax_info}) {
     $self->{queries}->{get_tax_info} = qq|

-      SELECT t.rate AS taxrate, t.taxnumber, t.taxdescription, t.chart_id AS taxchart_id,
+      SELECT t.rate AS taxrate, c.accno as taxnumber, t.taxdescription, t.chart_id AS taxchart_id,

         c.accno AS taxaccno, c.description AS taxaccount
       FROM taxkeys tk
       LEFT JOIN tax t   ON (tk.tax_id  = t.id)
         c.accno AS taxaccno, c.description AS taxaccount
       FROM taxkeys tk
       LEFT JOIN tax t   ON (tk.tax_id  = t.id)