summaryrefslogtreecommitdiff
path: root/sql
diff options
context:
space:
mode:
authoreinhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46>2008-11-25 06:53:38 +0000
committereinhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46>2008-11-25 06:53:38 +0000
commit57d4a99298f9ba1c761915db4f0922d99a3c9509 (patch)
tree36562d7321e235d527cf279fac53b6114fa5d97e /sql
parentca652e04320debad4a1a5a98dec07e46a51344c6 (diff)
Changes to UI when invoices locked, changes to source numbering behavior in bulk payments, test cases added, minor code cleanup for readability
git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@2420 4979c152-3d1c-0410-bac9-87ea11338e46
Diffstat (limited to 'sql')
-rw-r--r--sql/modules/Payment.sql34
-rw-r--r--sql/modules/test/Payment.sql33
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)