Correcting more Payment Problems

git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@1914 4979c152-3d1c-0410-bac9-87ea11338e46

2 files changed, 24 insertions, 8 deletions

diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql
index ab575a0c..61edb2e7 100644
--- a/sql/Pg-database.sql
+++ b/sql/Pg-database.sql
@@ -83,7 +83,7 @@ It provies a referential integrity enforcement mechanism for the financial data
 and also some common features such as discretionary (and pessimistic) locking 
 for long batch workflows. $$;
 
-CREATE FUNCTION lock_record (int, int) returns bool as 
+CREATE OR REPLACE FUNCTION lock_record (int, int) returns bool as 
 $$
 declare
    locked int;
@@ -91,7 +91,7 @@ begin
    SELECT locked_by into locked from transactions where id = $1;
    IF NOT FOUND THEN
 	RETURN FALSE;
-   ELSEIF locked_by is not null AND locked_by <> $2 THEN
+   ELSEIF locked is not null AND locked <> $2 THEN
         RETURN FALSE;
    END IF;
    UPDATE transactions set locked_by = $2 where id = $1;
@@ -371,6 +371,8 @@ CREATE TABLE acc_trans (
   memo text,
   invoice_id int,
   approved bool default true,
+  cleared_on date,
+  reconciled_on date,
   entry_id SERIAL PRIMARY KEY
 );
 --
@@ -611,7 +613,8 @@ CREATE TABLE ar (
   ponumber text,
   on_hold bool default false,
   reverse bool default false,
-  approved bool default true
+  approved bool default true,
+  description text
 );
 
 COMMENT ON COLUMN ar.entity_id IS $$ Used to be customer_id, but customer is now metadata. You need to push to entity $$;
@@ -644,7 +647,9 @@ CREATE TABLE ap (
   on_hold bool default false,
   approved bool default true,
   reverse bool default false,
-  terms int2 DEFAULT 0
+  terms int2 DEFAULT 0,
+  description text,
+  credit_account int
 );
 
 COMMENT ON COLUMN ap.entity_id IS $$ Used to be customer_id, but customer is now metadata. You need to push to entity $$;
diff --git a/sql/modules/Payment.sql b/sql/modules/Payment.sql
index b5beb196..1562a2ed 100644
--- a/sql/modules/Payment.sql
+++ b/sql/modules/Payment.sql
@@ -66,13 +66,13 @@ BEGIN
 		SELECT a.id AS invoice_id, a.invnumber, 
 		       a.transdate AS invoice_date, a.amount, 
 		       CASE WHEN discount_terms 
-		                 < extract('days' FROM age(a.transdate))
+		                 > extract('days' FROM age(a.transdate))
 		            THEN 0
 		            ELSE (a.amount - a.paid) * c.discount / 100  
 		       END AS discount,
 		       a.amount - a.paid - 
 		       CASE WHEN discount_terms 
-		                 < extract('days' FROM age(a.transdate))
+		                 > extract('days' FROM age(a.transdate))
 		            THEN 0
 		            ELSE (a.amount - a.paid) * c.discount / 100  
 		       END AS due
@@ -87,6 +87,7 @@ BEGIN
 		  JOIN entity_credit_account c USING (entity_id)
 		 WHERE a.invoice_class = in_account_class
 		       AND c.entity_class = in_account_class
+		       AND a.amount - a.paid <> 0
 		       AND a.curr = in_curr
 		       AND a.entity_id = coalesce(in_entity_id, a.entity_id)
 	LOOP
@@ -131,11 +132,14 @@ BEGIN
 		                   ELSE (a.amount - a.paid) * c.discount / 100
 		              END)::text, 
 		              (a.amount - a.paid -
-		              CASE WHEN c.discount_terms 
+		              (CASE WHEN c.discount_terms 
 		                        > extract('days' FROM age(a.transdate))
 		                   THEN 0
 		                   ELSE (a.amount - a.paid) * c.discount / 100
-		              END)::text]]) 
+		              END))::text]]),
+		              bool_and(lock_record(a.id, (select max(session_id) 				FROM "session" where users_id = (
+					select id from users WHERE username =
+					SESSION_USER))))
 		    FROM entity e
 		    JOIN entity_credit_account c ON (e.id = c.entity_id)
 		    JOIN (SELECT id, invnumber, transdate, amount, entity_id, 
@@ -146,6 +150,7 @@ BEGIN
 		                 paid, curr, 2 as invoice_class
 		            FROM ar
 		         ) a USING (entity_id)
+		    JOIN transactions t ON (a.id = t.id)
 		   WHERE a.invoice_class = in_account_class
 		         AND ((a.transdate >= in_date_from
 		               AND a.transdate <= in_date_to)
@@ -153,6 +158,12 @@ BEGIN
 		                          WHERE batch_id = in_batch_id))
 		         AND c.entity_class = in_account_class
 		         AND a.curr = in_currency
+		         AND a.amount - a.paid <> 0
+			 AND t.locked_by NOT 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