X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;ds=sidebyside;f=SL%2FRP.pm;h=2aa1e8d980533a6f51c815ee1ad74b08d6c5326b;hb=8f1fb2d695599019da043422cc3314b2a3f4fede;hp=8ea8f4f7e28c52a06cab107a7e39348d68c05226;hpb=e81d41a5dea2f4d3ba9bf011533a1833065021fa;p=kivitendo-erp.git diff --git a/SL/RP.pm b/SL/RP.pm index 8ea8f4f7e..2aa1e8d98 100644 --- a/SL/RP.pm +++ b/SL/RP.pm @@ -37,9 +37,9 @@ package RP; use SL::DBUtils; use Data::Dumper; use List::Util qw(sum); -# use strict; -# use warnings; +# use warnings; +use strict; # new implementation of balance sheet # readme! @@ -439,6 +439,7 @@ sub get_accounts_g { my $glwhere = ""; my $prwhere = ""; my $subwhere = ""; + my $inwhere = ""; my $item; if ($fromdate) { @@ -447,6 +448,7 @@ sub get_accounts_g { $subwhere .= " AND (transdate >= $fromdate)"; $glwhere = " AND (ac.transdate >= $fromdate)"; $prwhere = " AND (a.transdate >= $fromdate)"; + $inwhere = " AND (acc.transdate >= $fromdate)"; } else { $where .= " AND (ac.transdate >= $fromdate)"; } @@ -457,6 +459,7 @@ sub get_accounts_g { $subwhere .= " AND (transdate <= $todate)"; $where .= " AND (ac.transdate <= $todate)"; $prwhere .= " AND (a.transdate <= $todate)"; + $inwhere .= " AND (acc.transdate <= $todate)"; } if ($department_id) { @@ -468,9 +471,42 @@ sub get_accounts_g { $project = qq| AND (ac.project_id = | . conv_i($form->{project_id}) . qq|) |; } +# +# GUV patch by Ronny Rentner (Bug 1190) +# +# GUV IST-Versteuerung +# +# Alle tatsaechlichen _Zahlungseingaenge_ +# im Zeitraum erfassen +# (Teilzahlungen werden prozentual auf verschiedene Steuern aufgeteilt) +# +# + if ($form->{method} eq 'cash') { $query = qq| + SELECT SUM( ac.amount * + (SELECT SUM(acc.amount) * -1 + FROM acc_trans acc + INNER JOIN chart c ON (acc.chart_id = c.id AND c.link LIKE '%AR_paid%') + WHERE 1=1 $inwhere AND acc.trans_id = ac.trans_id) + / (SELECT amount FROM ar WHERE id = ac.trans_id) + ) AS amount, c.pos_eur + FROM acc_trans ac + LEFT JOIN chart c ON (c.id = ac.chart_id) + LEFT JOIN ar ON (ar.id = ac.trans_id) + LEFT JOIN taxkeys tk ON (tk.id = ( + SELECT id FROM taxkeys + WHERE chart_id = ac.chart_id + AND startdate <= COALESCE(ar.deliverydate,ar.transdate) + ORDER BY startdate DESC LIMIT 1 + ) + ) + WHERE ac.trans_id IN (SELECT DISTINCT trans_id FROM acc_trans WHERE 1=1 $subwhere) + + GROUP BY c.pos_eur + +/* SELECT SUM(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category FROM acc_trans ac JOIN chart c ON (c.id = ac.chart_id) @@ -480,7 +516,7 @@ sub get_accounts_g { 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.amount * chart_category_to_sgn(c.category)) AS amount, c.$category @@ -588,9 +624,7 @@ sub get_accounts_g { my $accno; my $ref; - my $sth = prepare_execute_query($form, $dbh, $query); - - while ($ref = $sth->fetchrow_hashref("NAME_lc")) { + foreach my $ref (selectall_hashref_query($form, $dbh, $query)) { if ($category eq "pos_bwa") { if ($last_period) { $form->{ $ref->{$category} }{kumm} += $ref->{amount}; @@ -601,7 +635,6 @@ sub get_accounts_g { $form->{ $ref->{$category} } += $ref->{amount}; } } - $sth->finish; $main::lxdebug->leave_sub(); } @@ -688,7 +721,7 @@ sub trial_balance { FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id) $dpt_join - WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) AND ac.ob_transaction $acc_cash_where + WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) AND ac.ob_transaction $dpt_where $project GROUP BY c.accno, c.category, c.description |; @@ -744,26 +777,27 @@ sub trial_balance { my $glwhere = ''; my $glsumwhere = ''; my $tofrom; + my ($fromdate, $todate); if ($form->{fromdate} || $form->{todate}) { if ($form->{fromdate}) { - my $fromdate = conv_dateq($form->{fromdate}); + $fromdate = conv_dateq($form->{fromdate}); $tofrom .= " AND (ac.transdate >= $fromdate)"; - $subwhere .= " AND (transdate >= $fromdate)"; - $sumsubwhere .= " AND (transdate >= (select date_trunc('year', date $fromdate))) "; - $saldosubwhere .= " AND transdate>=(select date_trunc('year', date $fromdate)) "; + $subwhere .= " AND (ac.transdate >= $fromdate)"; + $sumsubwhere .= " AND (ac.transdate >= (select date_trunc('year', date $fromdate))) "; + $saldosubwhere .= " AND (ac,transdate>=(select date_trunc('year', date $fromdate))) "; $invwhere .= " AND (a.transdate >= $fromdate)"; $glsaldowhere .= " AND ac.transdate>=(select date_trunc('year', date $fromdate)) "; $glwhere = " AND (ac.transdate >= $fromdate)"; $glsumwhere = " AND (ac.transdate >= (select date_trunc('year', date $fromdate))) "; } if ($form->{todate}) { - my $todate = conv_dateq($form->{todate}); + $todate = conv_dateq($form->{todate}); $tofrom .= " AND (ac.transdate <= $todate)"; $invwhere .= " AND (a.transdate <= $todate)"; - $saldosubwhere .= " AND (transdate <= $todate)"; - $sumsubwhere .= " AND (transdate <= $todate)"; - $subwhere .= " AND (transdate <= $todate)"; + $saldosubwhere .= " AND (ac.transdate <= $todate)"; + $sumsubwhere .= " AND (ac.transdate <= $todate)"; + $subwhere .= " AND (ac.transdate <= $todate)"; $glwhere .= " AND (ac.transdate <= $todate)"; $glsumwhere .= " AND (ac.transdate <= $todate) "; $glsaldowhere .= " AND (ac.transdate <= $todate) "; @@ -772,84 +806,10 @@ sub trial_balance { if ($form->{method} eq "cash") { $where .= - qq| AND ((ac.trans_id IN (SELECT id from ar) AND - ac.trans_id IN - ( - SELECT trans_id - FROM acc_trans - JOIN chart ON (chart_id = id) - WHERE (link LIKE '%AR_paid%') - $subwhere - ) - ) - OR - (ac.trans_id in (SELECT id from ap) AND - ac.trans_id IN - ( - SELECT trans_id - FROM acc_trans - JOIN chart ON (chart_id = id) - WHERE (link LIKE '%AP_paid%') - $subwhere - ) - ) - OR - (ac.trans_id in (SELECT id from gl) - $glwhere) - )|; - $saldowhere .= - qq| AND ((ac.trans_id IN (SELECT id from ar) AND - ac.trans_id IN - ( - SELECT trans_id - FROM acc_trans - JOIN chart ON (chart_id = id) - WHERE (link LIKE '%AR_paid%') - $saldosubwhere - ) - ) - OR - (ac.trans_id in (SELECT id from ap) AND - ac.trans_id IN - ( - SELECT trans_id - FROM acc_trans - JOIN chart ON (chart_id = id) - WHERE (link LIKE '%AP_paid%') - $saldosubwhere - ) - ) - OR - (ac.trans_id in (SELECT id from gl) - $glsaldowhere) - )|; - $sumwhere .= - qq| AND ((ac.trans_id IN (SELECT id from ar) AND - ac.trans_id IN - ( - SELECT trans_id - FROM acc_trans - JOIN chart ON (chart_id = id) - WHERE (link LIKE '%AR_paid%') - $sumsubwhere - ) - ) - OR - (ac.trans_id in (SELECT id from ap) AND - ac.trans_id IN - ( - SELECT trans_id - FROM acc_trans - JOIN chart ON (chart_id = id) - WHERE (link LIKE '%AP_paid%') - $sumsubwhere - ) - ) - OR - (ac.trans_id in (SELECT id from gl) - $glsumwhere) - )|; + qq| AND(ac.trans_id IN (SELECT id FROM ar WHERE datepaid>= $fromdate AND datepaid<= $todate UNION SELECT id FROM ap WHERE datepaid>= $fromdate AND datepaid<= $todate UNION SELECT id FROM gl WHERE transdate>= $fromdate AND transdate<= $todate)) AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL) |; + $saldowhere .= qq| AND(ac.trans_id IN (SELECT id FROM ar WHERE datepaid>= $fromdate AND datepaid<= $todate UNION SELECT id FROM ap WHERE datepaid>= $fromdate AND datepaid<= $todate UNION SELECT id FROM gl WHERE transdate>= $fromdate AND transdate<= $todate)) AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL) |; + $sumwhere .= qq| AND(ac.trans_id IN (SELECT id FROM ar WHERE datepaid>= $fromdate AND datepaid<= $todate UNION SELECT id FROM ap WHERE datepaid>= $fromdate AND datepaid<= $todate UNION SELECT id FROM gl WHERE transdate>= $fromdate AND transdate<= $todate)) AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL) |; } else { $where .= $tofrom . " AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL)"; $saldowhere .= $glsaldowhere . " AND (NOT ac.cb_transaction OR ac.cb_transaction IS NULL)"; @@ -1108,10 +1068,10 @@ sub trial_balance { $ref->{soll_saldo} = $form->round_amount(($ref->{haben_saldo} *- 1), 2); $ref->{haben_saldo} = 0; } - } elsif ($ref->{soll_saldo} != 0) { + } else { $ref->{soll_saldo} = $ref->{soll_saldo} - $ref->{beginning_balance}; if ($ref->{soll_saldo} < 0) { - $ref->{haben_saldo} = $form->round_amount(($ref->{haben_saldo} * -1), 2); + $ref->{haben_saldo} = $form->round_amount(($ref->{soll_saldo} * -1), 2); $ref->{soll_saldo} = 0; } }