From 1ef9fe2f5208b9d372cba17d896050f5f3e01bd9 Mon Sep 17 00:00:00 2001 From: Udo Spallek Date: Tue, 8 Nov 2005 21:28:03 +0000 Subject: [PATCH] Unbalanced Ledger Workaround... Betr. Bugzilla ID 124 --- SL/GL.pm | 117 +++++++++++++++++++++++++--------------------- bin/mozilla/gl.pl | 18 ++++--- 2 files changed, 75 insertions(+), 60 deletions(-) diff --git a/SL/GL.pm b/SL/GL.pm index 8772ec9e0..486bc71da 100644 --- a/SL/GL.pm +++ b/SL/GL.pm @@ -298,19 +298,22 @@ sub all_transactions { ($form->{ml}) = $sth->fetchrow_array; $sth->finish; - if ($form->{datefrom}) { + #if ($form->{datefrom}) { $query = qq|SELECT SUM(ac.amount) FROM acc_trans ac, chart c WHERE ac.chart_id = c.id - AND c.accno = '$form->{accno}' - AND ac.transdate < date '$form->{datefrom}' - |; + AND c.accno = '$form->{accno}'|; + $query .= qq|AND ac.transdate > date '$form->{datefrom}' + | if ($form->{datefrom});; + $query .= qq|AND ac.transdate < date '$form->{dateto}' + | if ($form->{dateto}); + $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); ($form->{balance}) = $sth->fetchrow_array; $sth->finish; - } + #} } if ($form->{gifi_accno}) { @@ -345,7 +348,7 @@ sub all_transactions { my $query = qq|SELECT g.id, 'gl' AS type, $false AS invoice, g.reference, ac.taxkey, t.taxkey AS sorttax, g.description, ac.transdate, ac.source, ac.trans_id, - ac.amount, c.accno, c.gifi_accno, g.notes, t.chart_id, ac.oid + ac.amount*100 AS amount, c.accno, c.gifi_accno, g.notes, t.chart_id, ac.oid FROM gl g, acc_trans ac, chart c LEFT JOIN tax t ON (t.chart_id=c.id) WHERE $glwhere @@ -354,7 +357,7 @@ sub all_transactions { UNION SELECT a.id, 'ar' AS type, a.invoice, a.invnumber, ac.taxkey, t.taxkey AS sorttax, ct.name, ac.transdate, ac.source, ac.trans_id, - ac.amount, c.accno, c.gifi_accno, a.notes, t.chart_id, ac.oid + ac.amount*100 AS amount, c.accno, c.gifi_accno, a.notes, t.chart_id, ac.oid FROM ar a, acc_trans ac, customer ct, chart c LEFT JOIN tax t ON (t.chart_id=c.id) WHERE $arwhere @@ -364,7 +367,7 @@ sub all_transactions { UNION SELECT a.id, 'ap' AS type, a.invoice, a.invnumber, ac.taxkey, t.taxkey AS sorttax, ct.name, ac.transdate, ac.source, ac.trans_id, - ac.amount, c.accno, c.gifi_accno, a.notes, t.chart_id, ac.oid + ac.amount*100 AS amount, c.accno, c.gifi_accno, a.notes, t.chart_id, ac.oid FROM ap a, acc_trans ac, vendor ct, chart c LEFT JOIN tax t ON (t.chart_id=c.id) WHERE $apwhere @@ -401,70 +404,78 @@ sub all_transactions { } } $balance = $ref->{amount}; + $ref->{amount} = $ref->{amount} * 1.0; $i = 0; $j = 0; $k = 0; $l = 0; if ($ref->{amount} < 0) { if ($ref->{chart_id} > 0) { - $ref->{debit_tax}{$i} = $ref->{amount} * -1; + $ref->{debit_tax}{$i} = ($ref->{amount} / 100.0) * -1; $ref->{debit_tax_accno}{$i} = $ref->{accno}; } else { - $ref->{debit}{$k} = $ref->{amount} * -1; + $ref->{debit}{$k} = ($ref->{amount} / 100.0) * -1; $ref->{debit_accno}{$k} = $ref->{accno}; $ref->{debit_taxkey}{$k} = $ref->{taxkey}; } } else { if ($ref->{chart_id} > 0) { - $ref->{credit_tax}{$j} = $ref->{amount}; + $ref->{credit_tax}{$j} = $ref->{amount} / 100.0; $ref->{credit_tax_accno}{$j} = $ref->{accno}; } else { - $ref->{credit}{$l} = $ref->{amount}; + $ref->{credit}{$l} = $ref->{amount} / 100.0; $ref->{credit_accno}{$l} = $ref->{accno}; $ref->{credit_taxkey}{$l} = $ref->{taxkey}; } } - - while (abs($balance) >= 0.015) { - my $ref2 = $sth->fetchrow_hashref(NAME_lc) - || $form->error("Unbalanced ledger!"); - - $balance = - (int($balance * 100000) + int(100000 * $ref2->{amount})) / 100000; - print(STDERR $balance, " BAlance\n"); - if ($ref2->{amount} < 0) { - if ($ref2->{chart_id} > 0) { - if ($ref->{debit_tax_accno}{$i} ne "") { - $i++; - } - $ref->{debit_tax}{$i} = $ref2->{amount} * -1; - $ref->{debit_tax_accno}{$i} = $ref2->{accno}; - } else { - if ($ref->{debit_accno}{$k} ne "") { - $k++; - } - $ref->{debit}{$k} = $ref2->{amount} * -1; - $ref->{debit_accno}{$k} = $ref2->{accno}; - $ref->{debit_taxkey}{$k} = $ref2->{taxkey}; - } - } else { - if ($ref2->{chart_id} > 0) { - if ($ref->{credit_tax_accno}{$j} ne "") { - $j++; - } - $ref->{credit_tax}{$j} = $ref2->{amount}; - $ref->{credit_tax_accno}{$j} = $ref2->{accno}; - } else { - if ($ref->{credit_accno}{$l} ne "") { - $l++; - } - $ref->{credit}{$l} = $ref2->{amount}; - $ref->{credit_accno}{$l} = $ref2->{accno}; - $ref->{credit_taxkey}{$l} = $ref2->{taxkey}; - } - } - } - +# if ($form->{accno} eq ''){ # flo & udo: if general report, + # then check balance + +# while ($balance != 0) { +# my $ref2 = $sth->fetchrow_hashref(NAME_lc) +# || $form->error(sprintf("Unbalanced ledger! trans-id: %i", $ref->{trans_id})); +# +# $balance = +# (int($balance * 100000) + int(100000 * $ref2->{amount})) / 100000; +# $balance += $ref2->{amount}; + + #print(STDERR $balance, " BAlance\n"); + #print(STDERR $ref2->{amount}, " Ref2->amount\n"); +# if ($ref2->{amount} < 0) { +# if ($ref2->{chart_id} > 0) { +# if ($ref->{debit_tax_accno}{$i} ne "") { +# $i++; +# } +# $ref->{debit_tax}{$i} = ($ref2->{amount} / 100.0) * -1; +# $ref->{debit_tax_accno}{$i} = $ref2->{accno}; +# } else { +# if ($ref->{debit_accno}{$k} ne "") { +# $k++; +# } +# $ref->{debit}{$k} = ($ref2->{amount} / 100.0) * -1; +# $ref->{debit_accno}{$k} = $ref2->{accno}; +# $ref->{debit_taxkey}{$k} = $ref2->{taxkey}; +# } +# } else { +# if ($ref2->{chart_id} > 0) { +# if ($ref->{credit_tax_accno}{$j} ne "") { +# $j++; +# } +# $ref->{credit_tax}{$j} = $ref2->{amount} / 100.0; +# $ref->{credit_tax_accno}{$j} = $ref2->{accno}; +# } else { +# if ($ref->{credit_accno}{$l} ne "") { +# $l++; +# } +# $ref->{credit}{$l} = $ref2->{amount} / 100.0; +# $ref->{credit_accno}{$l} = $ref2->{accno}; +# $ref->{credit_taxkey}{$l} = $ref2->{taxkey}; +# } +# } +# } +# } else { +# 1; +# } # print(STDERR Dumper($ref)); push @{ $form->{GL} }, $ref; $balance = 0; diff --git a/bin/mozilla/gl.pl b/bin/mozilla/gl.pl index 0c7f7350c..da2da5b37 100644 --- a/bin/mozilla/gl.pl +++ b/bin/mozilla/gl.pl @@ -391,7 +391,7 @@ sub generate_report { $form->{title} = $locale->text('General Ledger'); - $ml = ($form->{ml} =~ /(A|E)/) ? -1 : 1; + $ml = ($form->{ml} =~ /(A|E|Q)/) ? -1 : 1; unless ($form->{category} eq 'X') { $form->{title} .= " : " . $locale->text($acctype{ $form->{category} }); @@ -616,18 +616,18 @@ sub generate_report { |; } - + $form->{balance} *= $ml; foreach $ref (@{ $form->{GL} }) { - + $form->{balance} *= $ml; # if item ne sort print subtotal if ($form->{l_subtotal} eq 'Y') { if ($sameitem ne $ref->{ $form->{sort} }) { &gl_subtotal; } } - foreach $key (sort keys(%{ $ref->{amount} })) { - $form->{balance} += $ref->{amount}{$key}; - } + #foreach $key (sort keys(%{ $ref->{amount} })) { + # $form->{balance} += $ref->{amount}{$key}; + #} $debit = ""; foreach $key (sort keys(%{ $ref->{debit} })) { @@ -639,6 +639,7 @@ sub generate_report { $debit .= "
" . $form->format_amount(\%myconfig, $ref->{debit}{$key}, 2, 0); } + $form->{balance} = abs($form->{balance}) - abs($ref->{debit}{$key}); } $credit = ""; @@ -651,6 +652,7 @@ sub generate_report { $credit .= "
" . $form->format_amount(\%myconfig, $ref->{credit}{$key}, 2, 0); } + $form->{balance} = abs($form->{balance}) - abs( $ref->{credit}{$key}); } $debittax = ""; @@ -664,6 +666,7 @@ sub generate_report { $debittax .= "
" . $form->format_amount(\%myconfig, $ref->{debit_tax}{$key}, 2, 0); } + $form->{balance} = abs($form->{balance}) - abs($ref->{debit_tax}{$key}); } $credittax = ""; @@ -677,6 +680,7 @@ sub generate_report { $credittax .= "
" . $form->format_amount(\%myconfig, $ref->{credit_tax}{$key}, 2, 0); } + $form->{balance} = abs($form->{balance}) - abs($ref->{credit_tax}{$key}); } $debitaccno = ""; @@ -769,7 +773,7 @@ sub generate_report { "{gifi_accno}&callback=$callback>$ref->{gifi_accno} "; $column_data{balance} = "" - . $form->format_amount(\%myconfig, $form->{balance} * $ml, 2, 0) + . $form->format_amount(\%myconfig, $form->{balance}, 2, 0) . ""; $i++; -- 2.20.1