X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FRP.pm;h=5f04b998c21a58a6a3160d7705766620f8cdf8c0;hb=c19f41e1e0b92c7721963e0b40797b257bc88b3e;hp=ab2ad2d31be7e97a7313e17220f4179034a77c61;hpb=5be291594a4ead20f6900b249357deb262137cac;p=kivitendo-erp.git diff --git a/SL/RP.pm b/SL/RP.pm index ab2ad2d31..5f04b998c 100644 --- a/SL/RP.pm +++ b/SL/RP.pm @@ -37,8 +37,8 @@ package RP; use SL::DBUtils; use Data::Dumper; use List::Util qw(sum); -use strict; -use warnings; +# use strict; +# use warnings; # new implementation of balance sheet @@ -46,9 +46,7 @@ use warnings; # # stuff missing from the original implementation: # - bold stuff -# - format (2 places, varying signs of negative amounts) -# - rounding (might not be necessary) -# - accno and subdescription +# - subdescription # - proper testing for heading charts # - transmission from $form to TMPL realm is not as clear as i'd like sub balance_sheet { @@ -66,9 +64,6 @@ sub balance_sheet { $form->{period} = $form->{this_period} = conv_dateq($form->{asofdate}); } - $form->{decimalplaces} *= 1; - my $dec = $form->{decimalplaces}; - get_accounts($dbh, $last_period, "", $form->{asofdate}, $form, \@categories); # if there are any compare dates @@ -92,50 +87,48 @@ sub balance_sheet { foreach my $category (grep { !/C/ } @categories) { $TMPL_DATA->{$category} = []; + my $ml = $account{$category}{ml}; foreach my $key (sort keys %{ $form->{$category} }) { - my $row = { %{ $form->{$category}{$key} } }; + my $row = { %{ $form->{$category}{$key} } }; # if charttype "heading" - calculate this entry, start a new batch of charts belonging to this heading and skip the rest bo the loop + # header charts are not real charts. start a sub aggregation with them, but don't calculate anything with them if ($row->{charttype} eq "H") { if ($account{$category}{subtotal} && $form->{l_subtotal}) { $row->{subdescription} = $account{$category}{subdescription}; - $row->{this} = $account{$category}{subthis} * $account{$category}{ml}; # format: $dec, $dash - $row->{last} = $account{$category}{sublast} * $account{$category}{ml} if $last_period; # format: $dec, $dash + $row->{this} = $account{$category}{subthis} * $ml; # format: $dec, $dash + $row->{last} = $account{$category}{sublast} * $ml if $last_period; # format: $dec, $dash } $row->{subheader} = 1; - $account{$category}{subthis} = $form->{$category}{$key}{this}; - $account{$category}{sublast} = $form->{$category}{$key}{last}; - $account{$category}{subdescription} = $form->{$category}{$key}{description}; + $account{$category}{subthis} = $row->{this}; + $account{$category}{sublast} = $row->{last}; + $account{$category}{subdescription} = $row->{description}; $account{$category}{subtotal} = 1; - $form->{$category}{$key}{this} = 0; - $form->{$category}{$key}{last} = 0; + $row->{this} = 0; + $row->{last} = 0; next unless $form->{l_heading}; } - $row->{this} = $form->{$category}{$key}{this} * $account{$category}{ml}; - - # only add assets - if ($row->{charttype} eq 'A') { - $form->{total}{$category}{this} += $row->{this}; - } - - if ($last_period) { - $row->{last} = $form->{$category}{$key}{last} * $account{$category}{ml}; - $form->{total}{$category}{last} += $row->{last}; + for my $period (qw(this last)) { + next if ($period eq 'last' && !$last_period); + # only add assets + $row->{$period} *= $ml; + $form->{total}{$category}{$period} += $row->{$period}; # if ($row->{charttype} eq 'A') { # why?? } push @{ $TMPL_DATA->{$category} }, $row; } # foreach + # resolve heading/subtotal if ($account{$category}{subtotal} && $form->{l_subtotal}) { $TMPL_DATA->{$category}[-1]{subdescription} = $account{$category}{subdescription}; - $TMPL_DATA->{$category}[-1]{this} = $account{$category}{subthis} * $account{$category}{ml}; # format: $dec, $dash - $TMPL_DATA->{$category}[-1]{last} = $account{$category}{sublast} * $account{$category}{ml} if $last_period; # format: $dec, $dash + $TMPL_DATA->{$category}[-1]{this} = $account{$category}{subthis} * $ml; # format: $dec, $dash + $TMPL_DATA->{$category}[-1]{last} = $account{$category}{sublast} * $ml if $last_period; # format: $dec, $dash } $TMPL_DATA->{total}{$category}{this} = sum map { $_->{this} } @{ $TMPL_DATA->{$category} }; @@ -616,7 +609,7 @@ sub get_accounts_g { sub trial_balance { $main::lxdebug->enter_sub(); - my ($self, $myconfig, $form) = @_; + my ($self, $myconfig, $form, %options) = @_; my $dbh = $form->dbconnect($myconfig); @@ -651,40 +644,75 @@ sub trial_balance { if ($form->{method} eq "cash") { - $acc_cash_where = qq| AND (ac.trans_id IN (SELECT id FROM ar WHERE datepaid>='$form->{fromdate}' AND datepaid<='$form->{todate}' UNION SELECT id FROM ap WHERE datepaid>='$form->{fromdate}' AND datepaid<='$form->{todate}' UNION SELECT id FROM gl WHERE transdate>='$form->{fromdate}' AND transdate<='$form->{todate}')) |; + $acc_cash_where = + qq| AND (ac.trans_id IN ( + SELECT id + FROM ar + WHERE datepaid >= '$form->{fromdate}' + AND datepaid <= '$form->{todate}' + + UNION + + SELECT id + FROM ap + WHERE datepaid >= '$form->{fromdate}' + AND datepaid <= '$form->{todate}' + + UNION + + SELECT id + FROM gl + WHERE transdate >= '$form->{fromdate}' + AND transdate <= '$form->{todate}' + )) |; # $ar_ap_cash_where = qq| AND (a.datepaid>='$form->{fromdate}' AND a.datepaid<='$form->{todate}') |; } - # get beginning balances - $query = - qq|SELECT c.accno, c.category, SUM(ac.amount) AS amount, c.description - 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 - $dpt_where - $project - GROUP BY c.accno, c.category, c.description |; + if ($options{beginning_balances}) { + foreach my $prefix (qw(from to)) { + next if ($form->{"${prefix}date"}); + + my $min_max = $prefix eq 'from' ? 'min' : 'max'; + $query = qq|SELECT ${min_max}(transdate) + FROM acc_trans ac + $dpt_join + WHERE (1 = 1) + $dpt_where + $project|; + ($form->{"${prefix}date"}) = selectfirst_array_query($form, $dbh, $query); + } - $sth = prepare_execute_query($form, $dbh, $query, $form->{fromdate}); + # get beginning balances + $query = + qq|SELECT c.accno, c.category, SUM(ac.amount) AS amount, c.description + 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 + $dpt_where + $project + GROUP BY c.accno, c.category, c.description |; - while (my $ref = $sth->fetchrow_hashref("NAME_lc")) { + $sth = prepare_execute_query($form, $dbh, $query, $form->{fromdate}); - if ($ref->{amount} != 0 || $form->{all_accounts}) { - $trb{ $ref->{accno} }{description} = $ref->{description}; - $trb{ $ref->{accno} }{charttype} = 'A'; + while (my $ref = $sth->fetchrow_hashref("NAME_lc")) { - if ($ref->{amount} > 0) { - $trb{ $ref->{accno} }{haben_eb} = $ref->{amount}; - } else { - $trb{ $ref->{accno} }{soll_eb} = $ref->{amount} * -1; + if ($ref->{amount} != 0 || $form->{all_accounts}) { + $trb{ $ref->{accno} }{description} = $ref->{description}; + $trb{ $ref->{accno} }{charttype} = 'A'; + $trb{ $ref->{accno} }{beginning_balance} = $ref->{amount}; + + if ($ref->{amount} > 0) { + $trb{ $ref->{accno} }{haben_eb} = $ref->{amount}; + } else { + $trb{ $ref->{accno} }{soll_eb} = $ref->{amount} * -1; + } + $trb{ $ref->{accno} }{category} = $ref->{category}; } - $trb{ $ref->{accno} }{category} = $ref->{category}; - } + } + $sth->finish; } - $sth->finish; - # get headings $query = @@ -719,23 +747,23 @@ sub trial_balance { 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) "; @@ -744,84 +772,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)"; @@ -914,7 +868,7 @@ qq| AND ((ac.trans_id IN (SELECT id from ar) AND WHERE $saldowhere $dpt_where $project - AND c.accno = ?) AS saldo, + AND c.accno = ? AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)) AS saldo, (SELECT SUM(ac.amount) FROM acc_trans ac @@ -982,7 +936,7 @@ qq| AND ((ac.trans_id IN (SELECT id from ar) AND WHERE $saldowhere $dpt_where $project - AND c.accno = ?) AS saldo, + AND c.accno = ? AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)) AS saldo, (SELECT SUM(ac.amount) FROM acc_trans ac @@ -1025,7 +979,7 @@ qq| AND ((ac.trans_id IN (SELECT id from ar) AND $ref->{accno} = $accno; map { $ref->{$_} = $trb{$accno}{$_} } - qw(description category charttype amount soll_eb haben_eb); + qw(description category charttype amount soll_eb haben_eb beginning_balance); $ref->{balance} = $form->round_amount($balance{ $ref->{accno} }, 2); @@ -1073,7 +1027,21 @@ qq| AND ((ac.trans_id IN (SELECT id from ar) AND $ref->{debit} = $form->round_amount($ref->{debit}, 2); $ref->{credit} = $form->round_amount($ref->{credit}, 2); - $ref->{haben_saldo} = $form->round_amount($ref->{haben_saldo}, 2); + + if ($ref->{haben_saldo} != 0) { + $ref->{haben_saldo} = $ref->{haben_saldo} + $ref->{beginning_balance}; + if ($ref->{haben_saldo} < 0) { + $ref->{soll_saldo} = $form->round_amount(($ref->{haben_saldo} *- 1), 2); + $ref->{haben_saldo} = 0; + } + } else { + $ref->{soll_saldo} = $ref->{soll_saldo} - $ref->{beginning_balance}; + if ($ref->{soll_saldo} < 0) { + $ref->{haben_saldo} = $form->round_amount(($ref->{soll_saldo} * -1), 2); + $ref->{soll_saldo} = 0; + } + } + $ref->{haben_saldo} = $form->round_amount($ref->{haben_saldo}, 2); $ref->{soll_saldo} = $form->round_amount($ref->{soll_saldo}, 2); $ref->{haben_kumuliert} = $form->round_amount($ref->{haben_kumuliert}, 2); $ref->{soll_kumuliert} = $form->round_amount($ref->{soll_kumuliert}, 2); @@ -1102,12 +1070,13 @@ qq| AND ((ac.trans_id IN (SELECT id from ar) AND foreach my $accno (@headingaccounts) { foreach $ref (@{ $form->{TB} }) { if ($accno eq $ref->{accno}) { - $ref->{debit} = $trb{$accno}{debit}; - $ref->{credit} = $trb{$accno}{credit}; - $ref->{soll_saldo} = $trb{$accno}{soll_saldo}; - $ref->{haben_saldo} = $trb{$accno}{haben_saldo}; + $ref->{debit} = $trb{$accno}{debit}; + $ref->{credit} = $trb{$accno}{credit}; + $ref->{soll_saldo} = $trb{$accno}{soll_saldo}; + $ref->{haben_saldo} = $trb{$accno}{haben_saldo}; $ref->{soll_kumuliert} = $trb{$accno}{soll_kumuliert}; - $ref->{haben_kumuliert} = $trb{$accno}{haben_kumuliert}; } + $ref->{haben_kumuliert} = $trb{$accno}{haben_kumuliert}; + } } } @@ -1602,7 +1571,7 @@ sub bwa { } my @periods = qw(jetzt kumm); - my @gesamtleistung = qw(1 2 3); + my @gesamtleistung = qw(1 3); my @gesamtkosten = qw (10 11 12 13 14 15 16 17 18 19 20); my @ergebnisse = qw (rohertrag betriebrohertrag betriebsergebnis neutraleraufwand neutralerertrag ergebnisvorsteuern ergebnis gesamtleistung gesamtkosten); @@ -1625,6 +1594,8 @@ sub bwa { foreach my $item (@gesamtleistung) { $form->{ "$key" . "gesamtleistung" } += $form->{$item}{$key}; } + $form->{ "$key" . "gesamtleistung" } -= $form->{2}{$key}; + foreach my $item (@gesamtkosten) { $form->{ "$key" . "gesamtkosten" } += $form->{$item}{$key}; } @@ -1877,28 +1848,20 @@ sub income_statement { $form->{decimalplaces} *= 1; - foreach my $item (@categories_einnahmen) { - $form->{$item} = 0; - } - foreach my $item (@categories_ausgaben) { - $form->{$item} = 0; - } - foreach my $item (@ergebnisse) { - $form->{$item} = 0; - } &get_accounts_g($dbh, $last_period, $form->{fromdate}, $form->{todate}, $form, "pos_eur"); + foreach my $item (@categories_einnahmen) { $form->{"eur${item}"} = - $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2)); + $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),2); $form->{"sumeura"} += $form->{$item}; } foreach my $item (@categories_ausgaben) { $form->{"eur${item}"} = - $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2)); + $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),2); $form->{"sumeurb"} += $form->{$item}; } @@ -1906,7 +1869,7 @@ sub income_statement { foreach my $item (@ergebnisse) { $form->{$item} = - $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2)); + $form->format_amount($myconfig, $form->round_amount($form->{$item}, 2),2); } $main::lxdebug->leave_sub(); }