[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[3120] trunk/UI
- Subject: SF.net SVN: ledger-smb:[3120] trunk/UI
- From: ..hidden..
- Date: Thu, 17 Feb 2011 20:37:13 +0000
Revision: 3120
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=3120&view=rev
Author: einhverfr
Date: 2011-02-17 20:37:13 +0000 (Thu, 17 Feb 2011)
Log Message:
-----------
Adding support for rounding to monetary type before aggregating on trial balances
Modified Paths:
--------------
addons/1.3/enhanced_tb/trunk/UI/trial_balance/search.html
addons/1.3/enhanced_tb/trunk/sql/modules/trial_balance.sql
trunk/LedgerSMB/RP.pm
trunk/UI/rp-search-generate_trial_balance.html
Modified: addons/1.3/enhanced_tb/trunk/UI/trial_balance/search.html
===================================================================
--- addons/1.3/enhanced_tb/trunk/UI/trial_balance/search.html 2011-02-10 22:48:12 UTC (rev 3119)
+++ addons/1.3/enhanced_tb/trunk/UI/trial_balance/search.html 2011-02-17 20:37:13 UTC (rev 3120)
@@ -57,6 +57,15 @@
} ?>
</td>
</tr>
+ <tr>
+ <th colspan=2><?lsmb text('Round Amounts First') ?>
+ <td><?lsmb PROCESS input element_data={
+ name => 'discrete_amounts',
+ type => 'checkbox',
+ value => 'Y',
+ } -?>
+ </td>
+ </tr>
</table>
</td>
</tr>
Modified: addons/1.3/enhanced_tb/trunk/sql/modules/trial_balance.sql
===================================================================
--- addons/1.3/enhanced_tb/trunk/sql/modules/trial_balance.sql 2011-02-10 22:48:12 UTC (rev 3119)
+++ addons/1.3/enhanced_tb/trunk/sql/modules/trial_balance.sql 2011-02-17 20:37:13 UTC (rev 3120)
@@ -22,7 +22,7 @@
CREATE OR REPLACE FUNCTION trial_balance__generate
(i_date_from DATE, i_date_to DATE, in_heading INT, in_accounts INT[],
- in_ignore_yearend TEXT, in_department INT)
+ in_ignore_yearend TEXT, in_department INT, in_discrete_amounts bool)
returns setof tb_row AS
$$
DECLARE
@@ -35,8 +35,13 @@
yearend_trans INT;
yearends INT[];
include_trans INT;
+ precision int;
BEGIN
-
+ IF in_discrete_amounts THEN
+ SELECT value INTO precision
+ FROM defaults
+ WHERE setting_key = 'decimal_places';
+ END IF;
-- don't bother with checking from/to dates for NULL below.
IF i_date_from IS NULL THEN
SELECT min(transdate) - '1 day'::interval INTO date_from from acc_trans;
@@ -91,8 +96,9 @@
-- main trial balance query
SELECT a.id, a.accno, a.description, a.gifi_accno,
(COALESCE(SUM(CASE WHEN ac.transdate < date_from
- THEN COALESCE(ac.amount, 0) ELSE 0 END), 0) +
- COALESCE(cpa.amount, 0))
+ THEN COALESCE(round(ac.amount, COALESCE(precision, 1000)), 0)
+ ELSE 0 END), 0) +
+ COALESCE(round(cpa.amount, COALESCE(precision, 1000)), 0))
* CASE WHEN a.contra THEN -1 ELSE 1 END
* CASE WHEN a.category IN ('A', 'E') THEN -1 ELSE 1 END,
@@ -101,17 +107,17 @@
-- between the start checkpoint and the start_date.
-- Most conditions are for the case of either start or the
-- checkpoint missing.
- COALESCE(abs(cpb.debits), 0) +
+ COALESCE(abs(round(cpb.debits, COALESCE(precision, 1000))), 0) +
COALESCE(SUM(CASE WHEN COALESCE(ac.amount, 1) > 0 THEN 0
ELSE
CASE WHEN (cpb.end_date IS NOT NULL
AND ac.transdate > cpb.end_date) OR
(cpb.end_date IS NULL
AND ac.transdate >= date_from)
- THEN ac.amount * -1
+ THEN round(ac.amount, COALESCE(precision, 1000)) * -1
WHEN (cpb.end_date IS NOT NULL AND
ac.transdate < date_from)
- THEN ac.amount * -1
+ THEN round(ac.amount, COALESCE(precision, 1000)) * -1
ELSE 0
END
END), 0) -
@@ -122,14 +128,14 @@
ELSE 0 END,
-- see comment in debits for explanation of the case below.
- COALESCE(cpb.credits, 0) +
+ COALESCE(round(cpb.credits, COALESCE(precision, 1000)), 0) +
COALESCE(SUM (CASE WHEN COALESCE(ac.amount, -1) < 0 THEN 0
ELSE
CASE WHEN (cpb.end_date IS NOT NULL
AND ac.transdate > cpb.end_date) OR
(cpb.end_date IS NULL
AND ac.transdate >= date_from)
- THEN ac.amount
+ THEN round(ac.amount, COALESCE(precision, 1000))
WHEN (cpb.end_date IS NOT NULL AND
ac.transdate < date_from)
THEN -ac.amount
@@ -144,8 +150,8 @@
ac.transdate > cpb.end_date) AND
(ac.transdate >= date_from)
THEN
- COALESCE(ac.amount, 0) ELSE 0 END), 0) +
- COALESCE(cpb.amount, 0))
+ COALESCE(round(ac.amount, COALESCE(precision, 1000)), 0) ELSE 0 END), 0) +
+ COALESCE(round(cpb.amount, COALESCE(precision, 1000)), 0))
* CASE WHEN a.contra
THEN -1 ELSE 1 END
* CASE WHEN a.category IN ('A', 'E')
Modified: trunk/LedgerSMB/RP.pm
===================================================================
--- trunk/LedgerSMB/RP.pm 2011-02-10 22:48:12 UTC (rev 3119)
+++ trunk/LedgerSMB/RP.pm 2011-02-17 20:37:13 UTC (rev 3120)
@@ -1339,6 +1339,12 @@
( $form->{fromdate}, $form->{todate} ) =
$form->from_to( $form->{year}, $form->{month}, $form->{interval} )
if $form->{year} && $form->{month};
+ my $amount_cast; # Whitelisted, safe for interpolation
+ if ($form->{discrete_money}){
+ $amount_cast = "NUMERIC(30,$LedgerSMB::Sysconfig::decimal_places)";
+ } else {
+ $amount_cast = "NUMERIC";
+ };
# get beginning balances
if ( ($department_id or $form->{accounttype} eq 'gifi') and $form->{fromdate}) {
@@ -1346,7 +1352,7 @@
$query = qq|
SELECT g.accno, c.category,
- SUM(ac.amount) AS amount,
+ SUM(ac.amount::$amount_cast) AS amount,
g.description, c.contra
FROM acc_trans ac
JOIN chart c ON (ac.chart_id = c.id)
@@ -1368,7 +1374,7 @@
$query = qq|
SELECT c.accno, c.category,
- SUM(ac.amount) AS amount,
+ SUM(ac.amount::$amount_cast) AS amount,
c.description, c.contra
FROM acc_trans ac
JOIN chart c ON (ac.chart_id = c.id)
@@ -1460,17 +1466,17 @@
$query = "SELECT c.id AS chart_id, c.accno, c.description, c.contra,
c.category,
SUM(CASE WHEN ac.transdate < $datefrom
- THEN ac.amount
+ THEN ac.amount::$amount_cast
ELSE 0 END) AS balance,
SUM(CASE WHEN ac.transdate >=
coalesce($datefrom, ac.transdate)
AND ac.amount > 0
- THEN ac.amount
+ THEN ac.amount::$amount_cast
ELSE 0 END) AS credit,
SUM(CASE WHEN ac.transdate >=
coalesce($datefrom, ac.transdate)
AND ac.amount < 0
- THEN ac.amount
+ THEN ac.amount::$amount_cast
ELSE 0 END) * -1 AS debit,
SUM(CASE WHEN ac.transdate >=
coalesce($datefrom, ac.transdate)
@@ -1535,7 +1541,7 @@
$query = qq|
SELECT g.accno, g.description, c.category,
- SUM(ac.amount) AS amount, c.contra
+ SUM(ac.amount::$amount_cast) AS amount, c.contra
FROM acc_trans ac
JOIN chart c ON (c.id = ac.chart_id)
JOIN gifi g ON (c.gifi_accno = g.accno)
@@ -1555,7 +1561,7 @@
$query = qq|
SELECT c.accno, c.description, c.category,
- SUM(ac.amount) AS amount, c.contra
+ SUM(ac.amount::$amount_cast) AS amount, c.contra
FROM acc_trans ac
JOIN chart c ON (c.id = ac.chart_id)
$dpt_join
@@ -1576,7 +1582,7 @@
# prepare query for each account
$query = qq|
- SELECT (SELECT SUM(ac.amount) * -1 FROM acc_trans ac
+ SELECT (SELECT SUM(ac.amount::$amount_cast) * -1 FROM acc_trans ac
JOIN chart c ON (c.id = ac.chart_id)
$dpt_join
JOIN (SELECT id, approved FROM gl UNION
@@ -1587,7 +1593,7 @@
AND ($approved OR ac.approved)
AND ($approved OR gl.approved)
AND c.accno = ?) AS debit,
- (SELECT SUM(ac.amount) FROM acc_trans ac
+ (SELECT SUM(ac.amount::$amount_cast) FROM acc_trans ac
JOIN chart c ON (c.id = ac.chart_id)
$dpt_join
JOIN (SELECT id, approved FROM gl UNION
@@ -1602,7 +1608,7 @@
if ( $form->{accounttype} eq 'gifi' ) {
$query = qq|
- SELECT (SELECT SUM(ac.amount) * -1
+ SELECT (SELECT SUM(ac.amount::$amount_cast) * -1
FROM acc_trans ac
JOIN chart c ON (c.id = ac.chart_id)
$dpt_join
@@ -1610,7 +1616,7 @@
AND ($approved OR ac.approved)
AND c.gifi_accno = ?) AS debit,
- (SELECT SUM(ac.amount)
+ (SELECT SUM(ac.amount::$amount_cast)
FROM acc_trans ac
JOIN chart c ON (c.id = ac.chart_id)
$dpt_join
Modified: trunk/UI/rp-search-generate_trial_balance.html
===================================================================
--- trunk/UI/rp-search-generate_trial_balance.html 2011-02-10 22:48:12 UTC (rev 3119)
+++ trunk/UI/rp-search-generate_trial_balance.html 2011-02-17 20:37:13 UTC (rev 3120)
@@ -77,5 +77,11 @@
value => 'Y',
label => text('All Accounts'),
} -?>
+<?lsmb PROCESS input element_data={
+ name => 'discrete_money',
+ type => 'checkbox',
+ value => 'Y',
+ label => text('Whole Monetary Amounts Only'),
+ } -?>
</td>
</tr>
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.