From 543d78225ec609e9f67ecb1544e135ca88149234 Mon Sep 17 00:00:00 2001 From: "G. Richardson" Date: Tue, 22 Jan 2019 14:56:24 +0100 Subject: [PATCH] Spalte taxnumber aus Tabelle tax entfernt MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit 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! --- SL/AM.pm | 2 -- SL/CA.pm | 3 ++- SL/DB/MetaSetup/Tax.pm | 1 - SL/IC.pm | 3 ++- SL/IR.pm | 6 ++++-- SL/IS.pm | 25 ++++++++++++++++++++++--- SL/OE.pm | 14 +++++++++++--- SL/Taxkeys.pm | 2 +- 8 files changed, 42 insertions(+), 14 deletions(-) diff --git a/SL/AM.pm b/SL/AM.pm index a06f8b309..b5a283abb 100644 --- a/SL/AM.pm +++ b/SL/AM.pm @@ -1181,7 +1181,6 @@ sub _save_tax { taxdescription = ?, rate = ?, chart_id = ?, - taxnumber = (SELECT accno FROM chart WHERE id = ? ), skonto_sales_chart_id = ?, skonto_purchase_chart_id = ?, chart_categories = ? @@ -1195,7 +1194,6 @@ sub _save_tax { taxdescription, rate, chart_id, - taxnumber, skonto_sales_chart_id, skonto_purchase_chart_id, chart_categories, diff --git a/SL/CA.pm b/SL/CA.pm index d95448213..85f8dfdb9 100644 --- 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(tx.taxnumber::text) AS taxaccount, + comma(taxchart.accno::text) AS taxaccount, 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) + LEFT JOIN chart taxchart ON (taxchart.id = tx.chart_id) 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 f4a2e2d78..55aba86bc 100644 --- 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 }, - taxnumber => { type => 'text' }, ); __PACKAGE__->meta->primary_key_columns([ 'id' ]); diff --git a/SL/IC.pm b/SL/IC.pm index a2a0a86a8..7783c5f65 100644 --- a/SL/IC.pm +++ b/SL/IC.pm @@ -790,7 +790,8 @@ sub retrieve_accounts { SQL my $query_tax = <{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 @@ -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 = - 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 diff --git a/SL/IS.pm b/SL/IS.pm index 97e724811..181743964 100644 --- 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"}); - 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{%}); } @@ -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 = - 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 @@ -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 = - 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 diff --git a/SL/OE.pm b/SL/OE.pm index 74c9ffdfb..40b29f4a1 100644 --- 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 = - 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) | . @@ -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"}); - 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{%}); } diff --git a/SL/Taxkeys.pm b/SL/Taxkeys.pm index d921a9056..a682dc22c 100644 --- 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| - 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) -- 2.20.1