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

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)