SF.net SVN: ledger-smb:[2483] trunk/sql/modules

[..hidden..]

Revision: 2483
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=2483&view=rev
Author:   einhverfr
Date:     2009-03-10 17:29:07 +0000 (Tue, 10 Mar 2009)

Log Message:
-----------
Correcting pending_transactions api to avoid pulling unapproved transactions

Modified Paths:
--------------
    trunk/sql/modules/Reconciliaton.sql
    trunk/sql/modules/test/Reconciliation.sql
    trunk/sql/modules/test/data/Reconciliation.sql

Modified: trunk/sql/modules/Reconciliaton.sql
===================================================================
--- trunk/sql/modules/Reconciliaton.sql	2009-03-09 01:06:00 UTC (rev 2482)
+++ trunk/sql/modules/Reconciliaton.sql	2009-03-10 17:29:07 UTC (rev 2483)
@@ -272,17 +272,18 @@
 			ac.voucher_id, min(ac.entry_id), ac.transdate
 		FROM acc_trans ac
 		JOIN transactions t on (ac.trans_id = t.id)
-		JOIN (select id, entity_credit_account::text as ref, 'ar' as table FROM ar
+		JOIN (select id, entity_credit_account::text as ref, 'ar' as table FROM ar where approved
 			UNION
-		      select id, entity_credit_account::text, 'ap' as table FROM ap
+		      select id, entity_credit_account::text, 'ap' as table FROM ap WHERE approved
 			UNION
-		      select id, reference, 'gl' as table FROM gl) gl
+		      select id, reference, 'gl' as table FROM gl WHERE approved) gl 
 			ON (gl.table = t.table_name AND gl.id = t.id)
 		LEFT JOIN cr_report_line rl ON (rl.report_id = in_report_id
 			AND ((rl.ledger_id = ac.trans_id 
 				AND ac.voucher_id IS NULL) 
 				OR (rl.voucher_id = ac.voucher_id)))
 		WHERE ac.cleared IS FALSE
+			AND ac.approved IS TRUE
 			AND ac.chart_id = in_chart_id
 			AND ac.transdate <= in_end_date
 		GROUP BY gl.ref, ac.source, ac.transdate,

Modified: trunk/sql/modules/test/Reconciliation.sql
===================================================================
--- trunk/sql/modules/test/Reconciliation.sql	2009-03-09 01:06:00 UTC (rev 2482)
+++ trunk/sql/modules/test/Reconciliation.sql	2009-03-10 17:29:07 UTC (rev 2483)
@@ -31,7 +31,7 @@
 SELECT '1 Report Approved', reconciliation__report_approve(currval('cr_report_id_seq')::int) > 0;
 
 INSERT INTO test_result(test_name, success)
-SELECT '1 Transactions closed', count(*) = 0 FROM acc_trans where chart_id = -200 and cleared is false;
+SELECT '1 Transactions closed', count(*) = 2 FROM acc_trans where chart_id = -200 and cleared is false;
 
 INSERT INTO test_result(test_name, success)
 SELECT '1 Create Recon Report', 
@@ -58,7 +58,7 @@
 SELECT '1 Report Approved', reconciliation__report_approve(currval('cr_report_id_seq')::int) > 0;
 
 INSERT INTO test_result(test_name, success)
-SELECT '1 Transactions open', count(*) = 12 FROM acc_trans where chart_id = -201 and cleared is false;
+SELECT '1 Transactions open', count(*) = 14 FROM acc_trans where chart_id = -201 and cleared is false;
 
 INSERT INTO test_result(test_name, success)
 SELECT '1 Cleared balance post-approval is 10', reconciliation__get_cleared_balance(-201) = 10;
@@ -81,7 +81,7 @@
 SELECT 'Report Approved', reconciliation__report_approve(currval('cr_report_id_seq')::int) > 0;
 
 INSERT INTO test_result(test_name, success)
-SELECT 'Transactions closed', count(*) = 0 FROM acc_trans where chart_id = -200 and cleared is false;
+SELECT 'Transactions closed', count(*) = 2 FROM acc_trans where chart_id = -200 and cleared is false;
 
 INSERT INTO test_result(test_name, success)
 SELECT 'Cleared balance post-approval is 130', reconciliation__get_cleared_balance(-201) = 130;

Modified: trunk/sql/modules/test/data/Reconciliation.sql
===================================================================
--- trunk/sql/modules/test/data/Reconciliation.sql	2009-03-09 01:06:00 UTC (rev 2482)
+++ trunk/sql/modules/test/data/Reconciliation.sql	2009-03-10 17:29:07 UTC (rev 2483)
@@ -23,7 +23,9 @@
 INSERT INTO gl (id, reference, transdate) values (-203, 'Recon gl test 2', '1000-01-01');
 INSERT INTO gl (id, reference, transdate) values (-210, 'Recon gl test 3', '1000-01-03');
 INSERT INTO gl (id, reference, transdate) values (-211, 'Recon gl test 4', '1000-01-03');
-INSERT INTO gl (id, reference, transdate) values (-212, 'Cleared gl trans', '1000-01-03');
+INSERT INTO gl (id, reference, transdate, approved) values (-212, 'Cleared gl trans', '1000-01-03', true);
+INSERT INTO gl (id, reference, transdate, approved) values (-213, 'Unapproved gl trans', '1000-01-03', false);
+INSERT INTO gl (id, reference, transdate, approved) values (-214, 'gl trans, unapproved lines', '1000-01-03', false);
 
 INSERT INTO acc_trans (trans_id, chart_id, transdate, amount, source) values (-200, -200, '1000-01-01', -10, '1');
 INSERT INTO acc_trans (trans_id, chart_id, transdate, amount, source, cleared, approved) values (-200, -200, '1000-01-01', -10, '1', true, false);
@@ -53,3 +55,7 @@
 INSERT INTO acc_trans (trans_id, chart_id, transdate, amount, source) values (-211, -201, '1000-01-03', 10, '1');
 INSERT INTO acc_trans (trans_id, chart_id, transdate, amount, source, cleared) values (-212, -200, '1000-01-03', -10, '1', true);
 INSERT INTO acc_trans (trans_id, chart_id, transdate, amount, source, cleared) values (-212, -201, '1000-01-03', 10, '1', true);
+INSERT INTO acc_trans (trans_id, chart_id, transdate, amount, source, cleared) values (-213, -200, '1000-01-03', -10, '1', false);
+INSERT INTO acc_trans (trans_id, chart_id, transdate, amount, source, cleared) values (-213, -201, '1000-01-03', 10, '1', false);
+INSERT INTO acc_trans (trans_id, chart_id, transdate, amount, source, approved) values (-214, -200, '1000-01-03', -10, '1', false);
+INSERT INTO acc_trans (trans_id, chart_id, transdate, amount, source, approved) values (-214, -201, '1000-01-03', 10, '1', false);


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.