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

[..hidden..]

Revision: 2373
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=2373&view=rev
Author:   einhverfr
Date:     2008-10-17 23:46:20 +0000 (Fri, 17 Oct 2008)

Log Message:
-----------
Draft search type is now case insensitive, test cases added

Modified Paths:
--------------
    trunk/sql/modules/Drafts.sql

Added Paths:
-----------
    trunk/sql/modules/test/Draft.sql

Modified: trunk/sql/modules/Drafts.sql
===================================================================
--- trunk/sql/modules/Drafts.sql	2008-10-16 19:31:20 UTC (rev 2372)
+++ trunk/sql/modules/Drafts.sql	2008-10-17 23:46:20 UTC (rev 2373)
@@ -15,28 +15,28 @@
 	FOR out_row IN
 		SELECT trans.id, trans.transdate, trans.reference, 
 			trans.description, 
-			sum(case when in_type = 'ap' AND chart.link = 'AP'
+			sum(case when lower(in_type) = 'ap' AND chart.link = 'AP'
 				 THEN line.amount
-				 WHEN in_type = 'ar' AND chart.link = 'AR'
+				 WHEN lower(in_type) = 'ar' AND chart.link = 'AR'
 				 THEN line.amount * -1
-				 WHEN in_type = 'gl' AND line.amount > 0
+				 WHEN lower(in_type) = 'gl' AND line.amount > 0
 				 THEN line.amount
 			 	 ELSE 0
 			    END) as amount
 		FROM (
 			SELECT id, transdate, reference, description, 
 				approved from gl
-			WHERE in_type = 'gl'
+			WHERE lower(in_type) = 'gl'
 			UNION
 			SELECT id, transdate, invnumber as reference, 
 				description::text,
 				approved from ap
-			WHERE in_type = 'ap'
+			WHERE lower(in_type) = 'ap'
 			UNION
 			SELECT id, transdate, invnumber as reference,
 				description, 
 				approved from ar
-			WHERE in_type = 'ar'
+			WHERE lower(in_type) = 'ar'
 			) trans
 		JOIN acc_trans line ON (trans.id = line.trans_id)
 		JOIN chart ON (line.chart_id = chart.id)

Added: trunk/sql/modules/test/Draft.sql
===================================================================
--- trunk/sql/modules/test/Draft.sql	                        (rev 0)
+++ trunk/sql/modules/test/Draft.sql	2008-10-17 23:46:20 UTC (rev 2373)
@@ -0,0 +1,85 @@
+BEGIN;
+\i Base.sql
+
+INSERT INTO entity_credit_account (id, entity_id, entity_class, meta_number)
+SELECT '-1', min(id), 1, '_test vendor'
+FROM entity;
+
+INSERT INTO entity_credit_account (id, entity_id, entity_class, meta_number)
+SELECT '-2', min(id), 2, '_test customer'
+FROM entity;
+
+INSERT INTO chart (accno, description, charttype, category, link)
+VALUES ('00001', 'AP Test', 'A', 'L', 'AP');
+
+INSERT INTO chart (accno, description, charttype, category, link)
+VALUES ('00002', 'AR Test', 'A', 'A', 'AP');
+
+INSERT INTO ap (invnumber, entity_credit_account, amount, netamount, paid, 
+	approved, curr)
+select '_TEST AP', min(id), '100', '100', '0', FALSE, 'USD'
+FROM entity_credit_account WHERE entity_class = 1;
+
+INSERT INTO acc_trans (chart_id, trans_id, amount, approved)
+SELECT id, currval('id'), '100', TRUE FROM chart WHERE accno = '00001';
+INSERT INTO acc_trans (chart_id, trans_id, amount, approved)
+SELECT id, currval('id'), '-100', TRUE FROM chart WHERE accno = '00002';
+
+INSERT INTO ar (invnumber, entity_credit_account, amount, netamount, paid, 
+	approved, curr)
+select '_TEST AR', min(id), '100', '100', '0', FALSE, 'USD'
+FROM entity_credit_account WHERE entity_class = 2;
+
+INSERT INTO acc_trans (chart_id, trans_id, amount, approved)
+SELECT id, currval('id'), '-100', TRUE FROM chart WHERE accno = '00001';
+INSERT INTO acc_trans (chart_id, trans_id, amount, approved)
+SELECT id, currval('id'), '100', TRUE FROM chart WHERE accno = '00002';
+
+INSERT INTO gl (reference, description, approved)
+VALUES ('_TEST GL', 'Testing GL Drafts', false);
+
+INSERT INTO acc_trans (chart_id, trans_id, amount, approved)
+SELECT id, currval('id'), '-100', TRUE FROM chart WHERE accno = '00001';
+INSERT INTO acc_trans (chart_id, trans_id, amount, approved)
+SELECT id, currval('id'), '100', TRUE FROM chart WHERE accno = '00002';
+
+INSERT INTO test_result(test_name, success)
+SELECT '"ap" search successful', count(*) = 1
+FROM draft__search('ap',  NULL, NULL, NULL, NULL, NULL) 
+WHERE reference = '_TEST AP';
+
+INSERT INTO test_result(test_name, success)
+SELECT '"AP" search successful', count(*) = 1
+FROM draft__search('AP',  NULL, NULL, NULL, NULL, NULL)
+WHERE reference = '_TEST AP';
+
+INSERT INTO test_result(test_name, success)
+SELECT '"ar" search successful', count(*) = 1
+FROM draft__search('ar',  NULL, NULL, NULL, NULL, NULL)
+WHERE reference = '_TEST AR';
+
+INSERT INTO test_result(test_name, success)
+SELECT '"AR" search successful', count(*) = 1
+FROM draft__search('AR',  NULL, NULL, NULL, NULL, NULL)
+WHERE reference = '_TEST AR';
+
+SELECT *
+FROM draft__search('gl',  NULL, NULL, NULL, NULL, NULL);
+INSERT INTO test_result(test_name, success)
+SELECT '"gl" search successful', count(*) = 1
+FROM draft__search('gl',  NULL, NULL, NULL, NULL, NULL)
+WHERE reference = '_TEST GL';
+
+INSERT INTO test_result(test_name, success)
+SELECT '"GL" search successful', count(*) = 1
+FROM draft__search('GL',  NULL, NULL, NULL, NULL, NULL)
+WHERE reference = '_TEST GL';
+
+SELECT * FROM test_result;
+
+SELECT (select count(*) from test_result where success is true) 
+|| ' tests passed and ' 
+|| (select count(*) from test_result where success is not true) 
+|| ' failed' as message;
+
+ROLLBACK;


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