X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FUSTVA.pm;h=59e41b7b07dd846915e7033edb3797ae787f51b3;hb=8423c7b21bc3b4b2db7d0ffef756919619336e97;hp=c27d9377caa05b9297406ed84b14339f840ef7b4;hpb=e99f795f9bc71f46b27c29b692e7f10c3b052846;p=kivitendo-erp.git diff --git a/SL/USTVA.pm b/SL/USTVA.pm index c27d9377c..59e41b7b0 100644 --- a/SL/USTVA.pm +++ b/SL/USTVA.pm @@ -568,19 +568,19 @@ sub ustva { my $last_period = 0; my $category = "pos_ustva"; - my @categories_cent = qw(511 861 36 80 971 931 98 96 53 74 + my @category_cent = qw(511 861 36 80 971 931 98 96 53 74 85 65 66 61 62 67 63 64 59 69 39 83 Z43 Z45 Z53 Z62 Z65 Z67); - my @categories_euro = qw(41 44 49 43 48 51 86 35 77 76 91 97 93 + my @category_euro = qw(41 44 49 43 48 51 86 35 77 76 91 97 93 95 94 42 60 45 52 73 84); $form->{decimalplaces} *= 1; - foreach $item (@categories_cent) { + foreach $item (@category_cent) { $form->{"$item"} = 0; } - foreach $item (@categories_euro) { + foreach $item (@category_euro) { $form->{"$item"} = 0; } @@ -590,33 +590,23 @@ sub ustva { # # Berechnung der USTVA Formularfelder # + $form->{"51r"} = $form->{"511"}; $form->{"86r"} = $form->{"861"}; $form->{"97r"} = $form->{"971"}; $form->{"93r"} = $form->{"931"}; $form->{"Z43"} = - $form->{"511"} + $form->{"861"} + $form->{"36"} + $form->{"80"} + - $form->{"971"} + $form->{"931"} + $form->{"96"} + $form->{"98"}; + $form->{"511"} + $form->{"861"} + $form->{"36"} + $form->{"80"} + + $form->{"971"} + $form->{"931"} + $form->{"96"} + $form->{"98"}; $form->{"Z45"} = $form->{"Z43"}; $form->{"Z53"} = $form->{"Z43"}; $form->{"Z62"} = - $form->{"Z43"} - $form->{"66"} - $form->{"61"} - $form->{"62"} - - $form->{"63"} - $form->{"64"} - $form->{"59"}; + $form->{"Z43"} - $form->{"66"} - $form->{"61"} - $form->{"62"} - + $form->{"63"} - $form->{"64"} - $form->{"59"}; $form->{"Z65"} = $form->{"Z62"} - $form->{"69"}; $form->{"83"} = $form->{"Z65"} - $form->{"39"}; - - foreach $item (@categories_cent) { - $form->{$item} = - $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2), - 2, '0'); - } - - foreach $item (@categories_euro) { - $form->{$item} = - $form->format_amount($myconfig, $form->round_amount($form->{$item}, 0), - 0, '0'); - } - + # Hier fehlen moeglicherweise noch einige Berechnungen! + $dbh->disconnect; $main::lxdebug->leave_sub(); @@ -637,6 +627,7 @@ sub get_accounts_ustva { my $glwhere = ""; my $subwhere = ""; my $ARwhere = ""; + my $APwhere = ''; my $arwhere = ""; my $item; @@ -645,6 +636,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 +645,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 +662,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 +698,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 - $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,55 +747,20 @@ 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; @@ -870,9 +776,7 @@ sub get_accounts_ustva { while ($ref = $sth->fetchrow_hashref(NAME_lc)) { # Bug 365 solved?! -# if ($ref->{amount} < 0) { - $ref->{amount} *= -1; -# } + $ref->{amount} *= -1; if ($category eq "pos_bwa") { if ($last_period) { $form->{ $ref->{$category} }{kumm} += $ref->{amount};