SF.net SVN: ledger-smb: [1914] trunk/sql

[..hidden..]

Revision: 1914
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=1914&view=rev
Author:   einhverfr
Date:     2007-11-28 17:21:15 -0800 (Wed, 28 Nov 2007)

Log Message:
-----------
Correcting more Payment Problems

Modified Paths:
--------------
    trunk/sql/Pg-database.sql
    trunk/sql/modules/Payment.sql

Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql	2007-11-28 05:35:34 UTC (rev 1913)
+++ trunk/sql/Pg-database.sql	2007-11-29 01:21:15 UTC (rev 1914)
@@ -83,7 +83,7 @@
 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 @@
    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 @@
   memo text,
   invoice_id int,
   approved bool default true,
+  cleared_on date,
+  reconciled_on date,
   entry_id SERIAL PRIMARY KEY
 );
 --
@@ -611,7 +613,8 @@
   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 @@
   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 $$;

Modified: trunk/sql/modules/Payment.sql
===================================================================
--- trunk/sql/modules/Payment.sql	2007-11-28 05:35:34 UTC (rev 1913)
+++ trunk/sql/modules/Payment.sql	2007-11-29 01:21:15 UTC (rev 1914)
@@ -66,13 +66,13 @@
 		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 @@
 		  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 @@
 		                   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 @@
 		                 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 @@
 		                          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


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.