diff options
Diffstat (limited to 'sql/modules')
| -rw-r--r-- | sql/modules/Payment.sql | 34 | ||||
| -rw-r--r-- | sql/modules/test/Payment.sql | 33 |
2 files changed, 55 insertions, 12 deletions
diff --git a/sql/modules/Payment.sql b/sql/modules/Payment.sql index 175cac40..1bcba008 100644 --- a/sql/modules/Payment.sql +++ b/sql/modules/Payment.sql @@ -237,12 +237,18 @@ BEGIN c.description as eca_description, e.name AS contact_name, c.meta_number AS account_number, - sum (coalesce(p.due, 0) - + sum( case when u.username IS NULL or + u.username = SESSION_USER + THEN + coalesce(p.due::numeric, 0) - CASE WHEN c.discount_terms > extract('days' FROM age(a.transdate)) THEN 0 - ELSE (coalesce(p.due, 0)) * coalesce(c.discount, 0) / 100 - END) AS total_due, + ELSE (coalesce(p.due::numeric, 0)) * + coalesce(c.discount::numeric, 0) / 100 + END + ELSE 0::numeric + END) AS total_due, compound_array(ARRAY[[ a.id::text, a.invnumber, a.transdate::text, a.amount::text, (a.amount - p.due)::text, @@ -256,7 +262,14 @@ BEGIN > extract('days' FROM age(a.transdate)) THEN 0 ELSE (coalesce(p.due, 0)) * coalesce(c.discount, 0) / 100 - END))::text]]), + END))::text, + case when u.username IS NOT NULL + and u.username <> SESSION_USER + THEN 0::text + ELSE 1::text + END, + COALESCE(u.username, 0::text) + ]]), sum(case when a.batch_id = in_batch_id then 1 else 0 END), bool_and(lock_record(a.id, (select max(session_id) FROM "session" where users_id = ( @@ -297,6 +310,8 @@ BEGIN WHERE ((chart.link = 'AP' AND in_account_class = 1) OR (chart.link = 'AR' AND in_account_class = 2)) GROUP BY trans_id) p ON (a.id = p.trans_id) + LEFT JOIN "session" s ON (s."session_id" = t.locked_by) + LEFT JOIN users u ON (u.id = s.users_id) WHERE a.batch_id = in_batch_id OR (a.invoice_class = in_account_class AND a.approved @@ -307,14 +322,11 @@ BEGIN AND c.entity_class = in_account_class AND a.curr = in_currency AND a.entity_credit_account = c.id + AND (in_meta_number IS NULL OR + in_meta_number = c.meta_number) AND p.due <> 0 AND a.amount <> a.paid AND NOT a.on_hold - AND NOT (t.locked_by IS NOT NULL AND t.locked_by IN - (select "session_id" FROM "session" - WHERE users_id IN - (select id from users - where username <> SESSION_USER))) AND EXISTS (select trans_id FROM acc_trans WHERE trans_id = a.id AND chart_id = (SELECT id frOM chart @@ -323,9 +335,7 @@ BEGIN )) GROUP BY c.id, e.name, c.meta_number, c.threshold, e.control_code, c.description - HAVING (in_meta_number IS NULL - OR in_meta_number = c.meta_number) AND - (sum(p.due) >= c.threshold + HAVING (sum(p.due) >= c.threshold OR sum(case when a.batch_id = in_batch_id then 1 else 0 END) > 0) ORDER BY c.meta_number ASC diff --git a/sql/modules/test/Payment.sql b/sql/modules/test/Payment.sql index b74269e3..26b60eac 100644 --- a/sql/modules/test/Payment.sql +++ b/sql/modules/test/Payment.sql @@ -9,6 +9,15 @@ DELETE FROM users WHERE username = CURRENT_USER; INSERT INTO users (entity_id, username) SELECT -100, CURRENT_USER; +INSERT INTO entity(name, id, entity_class, control_code) +values ('test user 1', -200, 3, 'Test User 1'); + +insert into users (entity_id, username, id) +values (-200, '_test1', -200); + +insert into session (session_id, users_id, token, last_used, transaction_id) +values (-200, -200, md5(random()::text), now(), 0); + INSERT INTO person(first_name, last_name, entity_id, id) VALUES ('test', 'test', -100, -100); @@ -55,6 +64,17 @@ VALUES (true, now()::date, '100000', currval('id'), (select id from chart where INSERT INTO acc_trans (approved, transdate, amount, trans_id, chart_id) VALUES (true, now()::date, '-100000', currval('id'), (select id from chart where accno = '00002')); +INSERT INTO ap (id, invnumber, entity_credit_account, approved, amount, netamount, curr, transdate, paid) +VALUES (-300, 'test_show3', -101, true, 1000000, 1000000, 'USD', now()::date, 0); + +INSERT INTO acc_trans (approved, transdate, amount, trans_id, chart_id) +VALUES (true, now()::date, '1000000', -300, (select id from chart where accno = '00001')); + +INSERT INTO acc_trans (approved, transdate, amount, trans_id, chart_id) +VALUES (true, now()::date, '-1000000', -300, (select id from chart where accno = '00002')); + +update transactions set locked_by = -200 where id = -300; + INSERT INTO ap (invnumber, entity_credit_account, approved, amount, netamount, curr, transdate, paid) values ('test_show', -101, false, '1', '1', 'USD', now()::date, 0); @@ -81,6 +101,14 @@ SELECT invoices FROM payment_get_all_contact_invoices(1, NULL, 'USD', NULL, NULL )p)); INSERT INTO test_result(test_name, success) +VALUES ('Locked Invoice In Payment Selection', + (SELECT test_convert_array(invoices) LIKE '%::test_show3::%' + FROM + ( +SELECT invoices FROM payment_get_all_contact_invoices(1, NULL, 'USD', NULL, NULL, currval('batch_id_seq')::int, '00001', 'TEST1') +)p)); + +INSERT INTO test_result(test_name, success) VALUES ('Threshold met', (SELECT test_convert_array(invoices) LIKE '%::test_show2::%' FROM @@ -95,6 +123,11 @@ VALUES ('Non-Batch Voucher Not In Payment Selection', FROM (SELECT invoices FROM payment_get_all_contact_invoices(1, NULL, 'USD', NULL, NULL, currval('batch_id_seq')::int, '00001', 'TEST1'))p )); +INSERT INTO test_result(test_name, success) +VALUES ('Locked Invoice not in total', + (SELECT total_due < 1000000 + FROM payment_get_all_contact_invoices(1, NULL, 'USD', NULL, NULL, currval('batch_id_seq')::int, '00001', 'TEST1')) ); + SELECT * FROM TEST_RESULT; SELECT (select count(*) from test_result where success is true) |