summaryrefslogtreecommitdiff
path: root/sql
diff options
context:
space:
mode:
authoreinhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46>2008-10-17 23:46:20 +0000
committereinhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46>2008-10-17 23:46:20 +0000
commitf084ce511dbdda4fcb65c6f64746c201cc5f2caa (patch)
treee75007742fd8a5335df10c612251270e7c5b7581 /sql
parentc182111d97c8cba093403ebf6ab438cfd9f1d2d9 (diff)
Draft search type is now case insensitive, test cases added
git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@2373 4979c152-3d1c-0410-bac9-87ea11338e46
Diffstat (limited to 'sql')
-rw-r--r--sql/modules/Drafts.sql12
-rw-r--r--sql/modules/test/Draft.sql85
2 files changed, 91 insertions, 6 deletions
diff --git a/sql/modules/Drafts.sql b/sql/modules/Drafts.sql
index 9e8a95c2..e5a7b940 100644
--- a/sql/modules/Drafts.sql
+++ b/sql/modules/Drafts.sql
@@ -15,28 +15,28 @@ BEGIN
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)
diff --git a/sql/modules/test/Draft.sql b/sql/modules/test/Draft.sql
new file mode 100644
index 00000000..a5f799c6
--- /dev/null
+++ b/sql/modules/test/Draft.sql
@@ -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;