From 4654d9931a51aaa867c425c1fc63618337df67e9 Mon Sep 17 00:00:00 2001 From: Philip Reetz Date: Thu, 7 May 2009 07:17:06 +0000 Subject: [PATCH] Berichte Kontenblatt und SuSa auch fuer Zeitraueme ueber eine Jahr hinaus fit gemacht --- SL/CA.pm | 14 +++++++------- SL/RP.pm | 27 +++++++++++++++++++++------ bin/mozilla/ca.pl | 28 ++++------------------------ 3 files changed, 32 insertions(+), 37 deletions(-) diff --git a/SL/CA.pm b/SL/CA.pm index cf4ccce08..e1079c83a 100644 --- a/SL/CA.pm +++ b/SL/CA.pm @@ -210,32 +210,32 @@ sub all_transactions { # get last transaction date my $todate = ($form->{todate}) ? " AND ac.transdate <= '$form->{todate}' " : ""; - $query = qq|SELECT max(ac.transdate) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) $todate AND c.accno = ? $acc_cash_where|; + $query = qq|SELECT max(ac.transdate) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id) WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) $todate AND c.accno = ? $acc_cash_where|; ($form->{last_transaction}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno}); # get old saldo - $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) AND ac.transdate < ? AND c.accno = ? $acc_cash_where|; + $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) AND ac.transdate < ? AND c.accno = ? AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $acc_cash_where|; ($form->{saldo_old}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{fromdate}, $form->{accno}); #get old balance - $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) AND ac.transdate < ? AND c.accno = ? AND ac.amount < 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $acc_cash_where|; + $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) AND ac.transdate < ? AND c.accno = ? AND ac.amount < 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $acc_cash_where|; ($form->{old_balance_debit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{fromdate}, $form->{accno}); - $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) AND ac.transdate < ? AND c.accno = ? AND ac.amount > 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $acc_cash_where|; + $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) AND ac.transdate < ? AND c.accno = ? AND ac.amount > 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $acc_cash_where|; ($form->{old_balance_credit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{fromdate}, $form->{accno}); # get current saldo my $todate = ($form->{todate} ne "") ? " AND ac.transdate <= '$form->{todate}' " : ""; - $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) $todate AND c.accno = ? $acc_cash_where|; + $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) $todate AND c.accno = ? AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $acc_cash_where|; ($form->{saldo_new}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno}); #get current balance my $todate = ($form->{todate} ne "") ? " AND ac.transdate <= '$form->{todate}' " : ""; - $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) $todate AND c.accno = ? AND ac.amount < 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $acc_cash_where|; + $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) $todate AND c.accno = ? AND ac.amount < 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $acc_cash_where|; ($form->{current_balance_debit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno}); my $todate = ($form->{todate} ne "") ? " AND ac.transdate <= '$form->{todate}' " : ""; - $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) $todate AND c.accno = ? AND ac.amount > 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $acc_cash_where|; + $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) $todate AND c.accno = ? AND ac.amount > 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $acc_cash_where|; ($form->{current_balance_credit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno}); } } diff --git a/SL/RP.pm b/SL/RP.pm index 859296f2e..0b0000eac 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 @@ -700,6 +700,7 @@ sub trial_balance { 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}; @@ -941,7 +942,7 @@ sub trial_balance { 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 @@ -1009,7 +1010,7 @@ sub trial_balance { 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 @@ -1052,7 +1053,7 @@ sub trial_balance { $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); @@ -1100,7 +1101,21 @@ sub trial_balance { $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; + } + } elsif ($ref->{soll_saldo} != 0) { + $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->{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); diff --git a/bin/mozilla/ca.pl b/bin/mozilla/ca.pl index b0bd5e878..c6070fbd1 100644 --- a/bin/mozilla/ca.pl +++ b/bin/mozilla/ca.pl @@ -492,7 +492,8 @@ sub list_transactions { CA->all_transactions(\%myconfig, \%$form); - + $form->{saldo_old} += $form->{beginning_balance}; + $form->{saldo_new} += $form->{beginning_balance}; my $saldo_old = format_debit_credit($form->{saldo_old}); my $eb_string = format_debit_credit($form->{beginning_balance}); $form->{balance} = $form->{saldo_old}; @@ -654,18 +655,7 @@ sub list_transactions { }; } - my $sh = ""; - if ($form->{balance} < 0) { - $sh = " S"; - $ml = -1; - } elsif ($form->{balance} > 0) { - $sh = " H"; - $ml = 1; - } - my $data = $form->format_amount(\%myconfig, ($form->{balance} * $ml), 2); - $data .= $sh; - - $row->{balance}->{data} = $data; + $row->{balance}->{data} = $form->format_amount(\%myconfig, $form->{balance}, 2, 'DRCR'); if ($ca->{index} ne $previous_index) { # $report->add_data($row_set) if ($row_set); @@ -698,18 +688,8 @@ sub list_transactions { my $row = create_subtotal_row(\%totals, \@columns, \%column_alignment, 'listtotal'); - my $sh = ""; - if ($form->{balance} < 0) { - $sh = " S"; - $ml = -1; - } elsif ($form->{balance} > 0) { - $sh = " H"; - $ml = 1; - } - my $data = $form->format_amount(\%myconfig, ($form->{balance} * $ml), 2); - $data .= $sh; - $row->{balance}->{data} = $data; + $row->{balance}->{data} = $form->format_amount(\%myconfig, $form->{balance}, 2, 'DRCR'); $report->add_data($row); -- 2.20.1