From 9d2d867c1a5569ea0ddee859f77f9edead770720 Mon Sep 17 00:00:00 2001 From: Niclas Zimmermann Date: Fri, 31 May 2013 17:29:19 +0200 Subject: [PATCH] tax_id auslesen MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Statt die tax_id aus einem Join auf die Tabelle taxkeys zu bekommen, wird nun direkt von der acc_trans auf die Steuer zugegriffen. Dies geschieht an verschiedenen Stellen: Die tax_id wird jetzt in Kreditoren-/Debitoren- und Dialogbuchungen aus der acc_trans gelesen statt aus der Tabelle taxkeys. Im DATEV-Export wird jetzt die id über den Eintrag in der acc_trans ermittelt anstatt über die Tabelle taxkeys. --- SL/DATEV.pm | 19 ++++++++++--------- SL/Form.pm | 9 +-------- SL/GL.pm | 14 +------------- 3 files changed, 12 insertions(+), 30 deletions(-) diff --git a/SL/DATEV.pm b/SL/DATEV.pm index dfd154236..0aec84897 100644 --- a/SL/DATEV.pm +++ b/SL/DATEV.pm @@ -31,7 +31,6 @@ use strict; use SL::DBUtils; use SL::DATEV::KNEFile; -use SL::Taxkeys; use Data::Dumper; use DateTime; @@ -360,7 +359,6 @@ sub _get_transactions { $fromto =~ s/transdate/ac\.transdate/g; - my $taxkeys = Taxkeys->new(); my $filter = ''; # Useful for debugging purposes my %all_taxchart_ids = selectall_as_map($form, $self->dbh, qq|SELECT DISTINCT chart_id, TRUE AS is_set FROM tax|, 'chart_id', 'is_set'); @@ -370,11 +368,13 @@ sub _get_transactions { ar.invnumber, ar.duedate, ar.amount as umsatz, ar.deliverydate, ct.name, c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link, - ar.invoice + ar.invoice, + t.rate AS taxrate FROM acc_trans ac LEFT JOIN ar ON (ac.trans_id = ar.id) LEFT JOIN customer ct ON (ar.customer_id = ct.id) LEFT JOIN chart c ON (ac.chart_id = c.id) + LEFT JOIN tax t ON (ac.tax_id = t.id) WHERE (ar.id IS NOT NULL) AND $fromto $trans_id_filter @@ -386,11 +386,13 @@ sub _get_transactions { ap.invnumber, ap.duedate, ap.amount as umsatz, ap.deliverydate, ct.name, c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link, - ap.invoice + ap.invoice, + t.rate AS taxrate FROM acc_trans ac LEFT JOIN ap ON (ac.trans_id = ap.id) LEFT JOIN vendor ct ON (ap.vendor_id = ct.id) LEFT JOIN chart c ON (ac.chart_id = c.id) + LEFT JOIN tax t ON (ac.tax_id = t.id) WHERE (ap.id IS NOT NULL) AND $fromto $trans_id_filter @@ -402,10 +404,12 @@ sub _get_transactions { gl.reference AS invnumber, gl.transdate AS duedate, ac.amount as umsatz, NULL as deliverydate, gl.description AS name, c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link, - FALSE AS invoice + FALSE AS invoice, + t.rate AS taxrate FROM acc_trans ac LEFT JOIN gl ON (ac.trans_id = gl.id) LEFT JOIN chart c ON (ac.chart_id = c.id) + LEFT JOIN tax t ON (ac.tax_id = t.id) WHERE (gl.id IS NOT NULL) AND $fromto $trans_id_filter @@ -475,7 +479,6 @@ sub _get_transactions { } } - my %taxid_taxkeys = (); my $absumsatz = 0; if (scalar(@{$trans}) <= 2) { push @{ $self->{DATEV} }, $trans; @@ -545,13 +548,11 @@ sub _get_transactions { push @{ $self->{DATEV} }, [ \%new_trans, $trans->[$j] ]; } elsif (($j != $notsplitindex) && !$trans->[$j]->{is_tax}) { - my %tax_info = $taxkeys->get_full_tax_info('transdate' => $trans->[$j]->{transdate}, - 'deliverydate' => $trans->[$j]->{deliverydate}); my %new_trans = (); map { $new_trans{$_} = $trans->[$notsplitindex]->{$_}; } keys %{ $trans->[$notsplitindex] }; - my $tax_rate = $tax_info{taxkeys}->{ $trans->[$j]->{'taxkey'} }->{taxrate}; + my $tax_rate = $trans->[$j]->{'taxrate'}; $new_trans{'net_amount'} = $trans->[$j]->{'amount'} * -1; $new_trans{'tax_rate'} = 1 + $tax_rate; diff --git a/SL/Form.pm b/SL/Form.pm index 39bd9a578..2d1dce5c0 100644 --- a/SL/Form.pm +++ b/SL/Form.pm @@ -2762,14 +2762,7 @@ sub create_links { FROM acc_trans a LEFT JOIN chart c ON (c.id = a.chart_id) LEFT JOIN project p ON (p.id = a.project_id) - LEFT JOIN tax t ON (t.id= (SELECT tk.tax_id FROM taxkeys tk - WHERE (tk.taxkey_id=a.taxkey) AND - ((CASE WHEN a.chart_id IN (SELECT chart_id FROM taxkeys WHERE taxkey_id = a.taxkey) - THEN tk.chart_id = a.chart_id - ELSE 1 = 1 - END) - OR (c.link='%tax%')) AND - (startdate <= a.transdate) ORDER BY startdate DESC LIMIT 1)) + LEFT JOIN tax t ON (t.id= a.tax_id) WHERE a.trans_id = ? AND a.fx_transaction = '0' ORDER BY a.acc_trans_id, a.transdate|; diff --git a/SL/GL.pm b/SL/GL.pm index e6c87b314..594d57c7f 100644 --- a/SL/GL.pm +++ b/SL/GL.pm @@ -670,19 +670,7 @@ sub transaction { FROM acc_trans a JOIN chart c ON (c.id = a.chart_id) LEFT JOIN project p ON (p.id = a.project_id) - LEFT JOIN tax t ON - (t.id = - (SELECT tk.tax_id - FROM taxkeys tk - WHERE (tk.taxkey_id = a.taxkey) AND - ((CASE WHEN a.chart_id IN - (SELECT chart_id FROM taxkeys WHERE taxkey_id = a.taxkey) - THEN tk.chart_id = a.chart_id - ELSE 1 = 1 - END) - OR (c.link LIKE '%tax%')) - AND (startdate <= a.transdate) - ORDER BY startdate DESC LIMIT 1)) + LEFT JOIN tax t ON (t.id = a.tax_id) WHERE (a.trans_id = ?) AND (a.fx_transaction = '0') ORDER BY a.acc_trans_id, a.transdate|; -- 2.20.1