From 2ae29658a1a3ca7dd001c967457fef8034c3b93a Mon Sep 17 00:00:00 2001 From: Udo Spallek Date: Sat, 18 Nov 2006 13:54:06 +0000 Subject: [PATCH] Solve Bug 425: USTVA Vorsteuer calculation was wrong. Tested by balzer: http://lx-office.org/forum/forum_entry.php?id=3018 --- SL/USTVA.pm | 218 +++++++++++++++++----------------------------------- 1 file changed, 69 insertions(+), 149 deletions(-) diff --git a/SL/USTVA.pm b/SL/USTVA.pm index 84dc4f312..0f2378309 100644 --- a/SL/USTVA.pm +++ b/SL/USTVA.pm @@ -637,6 +637,7 @@ sub get_accounts_ustva { my $glwhere = ""; my $subwhere = ""; my $ARwhere = ""; + my $APwhere = ''; my $arwhere = ""; my $item; @@ -645,6 +646,7 @@ sub get_accounts_ustva { $subwhere .= " AND transdate >= '$fromdate'"; $glwhere = " AND ac.transdate >= '$fromdate'"; $ARwhere .= " AND acc.transdate >= '$fromdate'"; + $APwhere .= " AND AP.transdate >= '$fromdate'"; } $where .= " AND ac.transdate >= '$fromdate'"; } @@ -653,6 +655,7 @@ sub get_accounts_ustva { $where .= " AND ac.transdate <= '$todate'"; $ARwhere .= " AND acc.transdate <= '$todate'"; $subwhere .= " AND transdate <= '$todate'"; + $APwhere .= " AND AP.transdate <= '$todate'"; } if ($department_id) { @@ -669,13 +672,18 @@ sub get_accounts_ustva { AND ac.project_id = $form->{project_id} |; } +######################################### +# Method eq 'cash' = IST Versteuerung +######################################### - if ($form->{method} eq 'cash') { + if ($form->{method} eq 'cash') { $query = qq| - SELECT - SUM( ac.amount * + -- Alle tatsaechlichen Zahlungseingaenge + -- im Voranmeldezeitraum erfassen + -- (Teilzahlungen werden prozentual auf verschiedene Steuern aufgeteilt) + SUM( ac.amount * -- Bezahlt / Rechnungssumme ( SELECT SUM(acc.amount) @@ -700,101 +708,44 @@ sub get_accounts_ustva { -- Here no where, please. All Transactions ever should be -- testet if they are paied in the USTVA report period. GROUP BY c.pos_ustva - UNION - - SELECT sum(ac.amount) AS amount, - c.$category - FROM acc_trans ac - JOIN chart c ON (c.id = ac.chart_id) - JOIN ap a ON (a.id = ac.trans_id) - $dpt_join - WHERE $where - $dpt_where - AND ac.trans_id IN - ( - SELECT trans_id - FROM acc_trans - JOIN chart ON (chart_id = id) - WHERE link LIKE '%AP_amount%' - $subwhere - ) - - $project - GROUP BY c.$category - - UNION - - SELECT sum(ac.amount) AS amount, - c.$category - FROM acc_trans ac - JOIN chart c ON (c.id = ac.chart_id) - JOIN gl a ON (a.id = ac.trans_id) - $dpt_join - WHERE $where - $glwhere - $dpt_from - AND NOT (c.link = 'AR' OR c.link = 'AP') - $project - GROUP BY c.$category - - |; - if ($form->{project_id}) { - - $query .= qq| - - UNION - - SELECT SUM(ac.sellprice * ac.qty) AS amount, - c.$category - FROM invoice ac - JOIN ar a ON (a.id = ac.trans_id) - JOIN parts p ON (ac.parts_id = p.id) - JOIN chart c on (p.income_accno_id = c.id) - $dpt_join - -- use transdate from subwhere - WHERE 1 = 1 $subwhere - AND c.category = 'I' - $dpt_where - AND ac.trans_id IN - ( - SELECT trans_id - FROM acc_trans - JOIN chart ON (chart_id = id) - WHERE link LIKE '%AR_paid%' - $subwhere - ) - - $project - GROUP BY c.$category - - UNION - - SELECT SUM(ac.sellprice) AS amount, - c.$category - FROM invoice ac - JOIN ap a ON (a.id = ac.trans_id) - JOIN parts p ON (ac.parts_id = p.id) - JOIN chart c on (p.expense_accno_id = c.id) - $dpt_join - WHERE 1 = 1 $subwhere - AND c.category = 'E' - $dpt_where - AND ac.trans_id IN - ( - SELECT trans_id - FROM acc_trans - JOIN chart ON (chart_id = id) - WHERE link LIKE '%AP_paid%' - $subwhere - ) - - $project - GROUP BY c.$category - |; - } - - } else { + UNION -- alle Ausgaben AP erfassen + + SELECT + sum(ac.amount) AS amount, pos_ustva + FROM acc_trans ac + JOIN AP ON (AP.id = ac.trans_id ) + JOIN chart c ON (c.id = ac.chart_id AND pos_ustva NOT LIKE '') + WHERE + 1=1 + $APwhere + $dpt_where + $project + GROUP BY pos_ustva + + UNION -- alle Ausgaben und Einnahmen direkter gl Buchungen erfassen + + SELECT sum + ( + CASE WHEN c.link LIKE '%AR%' THEN ac.amount * -1 + WHEN c.link LIKE '%AP%' THEN ac.amount * 1 + END + ) AS amount, c.$category + FROM acc_trans ac + JOIN chart c ON (c.id = ac.chart_id) + JOIN gl a ON (a.id = ac.trans_id) + $dpt_join + WHERE $where + $dpt_from + AND NOT (c.link = 'AR' OR c.link = 'AP') + $project + GROUP BY c.$category + |; + + } else { +######################################### +# Method eq 'accrual' = Soll Versteuerung +######################################### if ($department_id) { $dpt_join = qq| @@ -806,69 +757,38 @@ sub get_accounts_ustva { } $query = qq| - - SELECT sum(ac.amount) AS amount, - c.$category - FROM acc_trans ac - JOIN chart c ON (c.id = ac.chart_id) - $dpt_join - WHERE $where - $dpt_where - $project - GROUP BY c.$category - |; - - if ($form->{project_id}) { - - $query .= qq| - - UNION - - SELECT SUM(ac.sellprice * ac.qty) AS amount, - c.$category - FROM invoice ac - JOIN ar a ON (a.id = ac.trans_id) - JOIN parts p ON (ac.parts_id = p.id) - JOIN chart c on (p.income_accno_id = c.id) - $dpt_join - -- use transdate from subwhere - WHERE 1 = 1 $subwhere - AND c.category = 'I' - $dpt_where - $project - GROUP BY c.$category - - UNION - - SELECT SUM(ac.sellprice * ac.qty) * -1 AS amount, - c.$category - FROM invoice ac - JOIN ap a ON (a.id = ac.trans_id) - JOIN parts p ON (ac.parts_id = p.id) - JOIN chart c on (p.expense_accno_id = c.id) - $dpt_join - WHERE 1 = 1 $subwhere - AND c.category = 'E' - $dpt_where - $project - GROUP BY c.$category - |; - - } + SELECT sum + ( + CASE WHEN c.link LIKE '%AR%' THEN ac.amount * -1 + WHEN c.link LIKE '%AP%' THEN ac.amount * 1 + END + ) AS amount, c.$category + FROM acc_trans ac + JOIN chart c ON (c.id = ac.chart_id) + $dpt_join + WHERE $where + $dpt_where + $project + GROUP BY c.$category + |; } my @accno; my $accno; my $ref; - #print $query; + # Show all $query in Debuglevel LXDebug::QUERY + $callingdetails = (caller (0))[3]; + $main::lxdebug->message(LXDebug::QUERY, "$callingdetails \$query=\n $query"); + my $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - if ($ref->{amount} < 0) { +# Bug 365 solved?! +# if ($ref->{amount} < 0) { $ref->{amount} *= -1; - } +# } if ($category eq "pos_bwa") { if ($last_period) { $form->{ $ref->{$category} }{kumm} += $ref->{amount}; -- 2.20.1