[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

SF.net SVN: ledger-smb:[4630] branches/1.3/sql/modules/Payment.sql



Revision: 4630
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=4630&view=rev
Author:   ehuelsmann
Date:     2012-03-29 18:52:58 +0000 (Thu, 29 Mar 2012)
Log Message:
-----------
Change payment_get_open_accounts() and payment_get_all_contact_invoices()
to adjust for the fact that the columns ar.paid and ap.paid are no longer
maintained.

(Note the change in payment_get_all_contact_invoices() only replaces
  "AND a.amount <> a.paid" with "AND due <> 0"; the rest is untabification.)

Modified Paths:
--------------
    branches/1.3/sql/modules/Payment.sql

Modified: branches/1.3/sql/modules/Payment.sql
===================================================================
--- branches/1.3/sql/modules/Payment.sql	2012-03-29 01:57:55 UTC (rev 4629)
+++ branches/1.3/sql/modules/Payment.sql	2012-03-29 18:52:58 UTC (rev 4630)
@@ -69,29 +69,40 @@
 
 -- payment_get_open_accounts and the option to get all accounts need to be
 -- refactored and redesigned.  -- CT
-CREATE OR REPLACE FUNCTION payment_get_open_accounts(in_account_class int) 
+CREATE OR REPLACE FUNCTION payment_get_open_accounts(in_account_class int)
 returns SETOF entity AS
 $$
 DECLARE out_entity entity%ROWTYPE;
 BEGIN
-	FOR out_entity IN
-		SELECT ec.id, cp.legal_name as name, e.entity_class, e.created 
-		FROM entity e
-		JOIN entity_credit_account ec ON (ec.entity_id = e.id)
-		JOIN company cp ON (cp.entity_id = e.id)
-			WHERE ec.entity_class = in_account_class
+        FOR out_entity IN
+                SELECT ec.id, cp.legal_name as name, e.entity_class, e.created
+                FROM entity e
+                JOIN entity_credit_account ec ON (ec.entity_id = e.id)
+                JOIN company cp ON (cp.entity_id = e.id)
+                        WHERE ec.entity_class = in_account_class
                         AND CASE WHEN in_account_class = 1 THEN
-	           		ec.id IN (SELECT entity_credit_account FROM ap 
-	           			WHERE amount <> paid
-		   			GROUP BY entity_credit_account)
-		    	       WHEN in_account_class = 2 THEN
-		   		ec.id IN (SELECT entity_credit_account FROM ar
-		   			WHERE amount <> paid
-		   			GROUP BY entity_credit_account)
-		   	  END
-	LOOP
-		RETURN NEXT out_entity;
-	END LOOP;
+                                ec.id IN
+                                (SELECT entity_credit_account
+                                   FROM acc_trans
+                                   JOIN chart ON (acc_trans.chart_id = chart.id)
+                                   JOIN ap ON (acc_trans.trans_id = ap.id)
+                                   WHERE link = 'AP'
+                                   GROUP BY chart_id,
+                                         trans_id, entity_credit_account
+                                   HAVING SUM(acc_trans.amount) <> 0)
+                               WHEN in_account_class = 2 THEN
+                                ec.id IN (SELECT entity_credit_account
+                                   FROM acc_trans
+                                   JOIN chart ON (acc_trans.chart_id = chart.id)
+                                   JOIN ar ON (acc_trans.trans_id = ar.id)
+                                   WHERE link = 'AR'
+                                   GROUP BY chart_id,
+                                         trans_id, entity_credit_account
+                                   HAVING SUM(acc_trans.amount) <> 0)
+                          END
+        LOOP
+                RETURN NEXT out_entity;
+        END LOOP;
 END;
 $$ LANGUAGE PLPGSQL;
 
@@ -302,125 +313,126 @@
 
 CREATE OR REPLACE FUNCTION payment_get_all_contact_invoices
 (in_account_class int, in_business_id int, in_currency char(3),
-	in_date_from date, in_date_to date, in_batch_id int, 
-	in_ar_ap_accno text, in_meta_number text)
+        in_date_from date, in_date_to date, in_batch_id int, 
+        in_ar_ap_accno text, in_meta_number text)
 RETURNS SETOF payment_contact_invoice AS
 $$
 DECLARE payment_item payment_contact_invoice;
 BEGIN
-	FOR payment_item IN
-		  SELECT c.id AS contact_id, e.control_code as econtrol_code, 
-			c.description as eca_description, 
-			e.name AS contact_name,
-		         c.meta_number AS account_number,
-			 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::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,
-		              (CASE WHEN c.discount_terms 
-		                        < extract('days' FROM age(a.transdate))
-		                   THEN 0
-		                   ELSE (coalesce(p.due, 0) * coalesce(c.discount, 0) / 100)
-		              END)::text, 
-		              (coalesce(p.due, 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))::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)
-				]]),
+        FOR payment_item IN
+                  SELECT c.id AS contact_id, e.control_code as econtrol_code, 
+                        c.description as eca_description, 
+                        e.name AS contact_name,
+                         c.meta_number AS account_number,
+                         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::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,
+                              (CASE WHEN c.discount_terms 
+                                        < extract('days' FROM age(a.transdate))
+                                   THEN 0
+                                   ELSE (coalesce(p.due, 0) * coalesce(c.discount, 0) / 100)
+                              END)::text, 
+                              (coalesce(p.due, 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))::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 = (
-					select id from users WHERE username =
-					SESSION_USER))))
+                                  else 0 END),
+                              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 ap.id, invnumber, transdate, amount, entity_id, 
-				 curr, 1 as invoice_class,
-		                 entity_credit_account, on_hold, v.batch_id,
-				 approved, paid
-		            FROM ap
-		       LEFT JOIN (select * from voucher where batch_class = 1) v 
-			         ON (ap.id = v.trans_id)
-			   WHERE in_account_class = 1
-			         AND (v.batch_class = 1 or v.batch_id IS NULL)
-		           UNION
-		          SELECT ar.id, invnumber, transdate, amount, entity_id,
-		                 curr, 2 as invoice_class,
-		                 entity_credit_account, on_hold, v.batch_id,
-				 approved, paid
-		            FROM ar
-		       LEFT JOIN (select * from voucher where batch_class = 2) v 
-			         ON (ar.id = v.trans_id)
-			   WHERE in_account_class = 2
-			         AND (v.batch_class = 2 or v.batch_id IS NULL)
-			ORDER BY transdate
-		         ) a ON (a.entity_credit_account = c.id)
-		    JOIN transactions t ON (a.id = t.id)
-		    JOIN (SELECT acc_trans.trans_id, 
-		                 sum(CASE WHEN in_account_class = 1 THEN amount
-		                          WHEN in_account_class = 2 
-		                          THEN amount * -1
-		                     END) AS due 
-		            FROM acc_trans 
-		            JOIN account coa ON (coa.id = acc_trans.chart_id)
+                    FROM entity e
+                    JOIN entity_credit_account c ON (e.id = c.entity_id)
+                    JOIN (SELECT ap.id, invnumber, transdate, amount, entity_id, 
+                                 curr, 1 as invoice_class,
+                                 entity_credit_account, on_hold, v.batch_id,
+                                 approved, paid
+                            FROM ap
+                       LEFT JOIN (select * from voucher where batch_class = 1) v 
+                                 ON (ap.id = v.trans_id)
+                           WHERE in_account_class = 1
+                                 AND (v.batch_class = 1 or v.batch_id IS NULL)
+                           UNION
+                          SELECT ar.id, invnumber, transdate, amount, entity_id,
+                                 curr, 2 as invoice_class,
+                                 entity_credit_account, on_hold, v.batch_id,
+                                 approved, paid
+                            FROM ar
+                       LEFT JOIN (select * from voucher where batch_class = 2) v 
+                                 ON (ar.id = v.trans_id)
+                           WHERE in_account_class = 2
+                                 AND (v.batch_class = 2 or v.batch_id IS NULL)
+                        ORDER BY transdate
+                         ) a ON (a.entity_credit_account = c.id)
+                    JOIN transactions t ON (a.id = t.id)
+                    JOIN (SELECT acc_trans.trans_id, 
+                                 sum(CASE WHEN in_account_class = 1 THEN amount
+                                          WHEN in_account_class = 2 
+                                          THEN amount * -1
+                                     END) AS due 
+                            FROM acc_trans 
+                            JOIN account coa ON (coa.id = acc_trans.chart_id)
                             JOIN account_link al ON (al.account_id = coa.id)
-		       LEFT JOIN voucher v ON (acc_trans.voucher_id = v.id)
-		           WHERE ((al.description = 'AP' AND in_account_class = 1)
-		                 OR (al.description = 'AR' AND in_account_class = 2))
-			   AND (approved IS TRUE or v.batch_class IN (3, 6))
-		        GROUP BY acc_trans.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
-		         AND a.amount <> a.paid 
-			 AND NOT a.on_hold
+                       LEFT JOIN voucher v ON (acc_trans.voucher_id = v.id)
+                           WHERE ((al.description = 'AP' AND in_account_class = 1)
+                                 OR (al.description = 'AR' AND in_account_class = 2))
+                           AND (approved IS TRUE or v.batch_class IN (3, 6))
+                        GROUP BY acc_trans.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
+                         AND due <> 0
+                         AND NOT a.on_hold
                          AND a.curr = in_currency
-		         AND EXISTS (select trans_id FROM acc_trans
-		                      WHERE trans_id = a.id AND
-		                            chart_id = (SELECT id from account
-		                                         WHERE accno
-		                                               = in_ar_ap_accno)
-		                    )))
-		         AND (in_meta_number IS NULL OR 
+                         AND EXISTS (select trans_id FROM acc_trans
+                                      WHERE trans_id = a.id AND
+                                            chart_id = (SELECT id from account
+                                                         WHERE accno
+                                                               = in_ar_ap_accno)
+                                    )))
+                         AND (in_meta_number IS NULL OR 
                              in_meta_number = c.meta_number)
-		GROUP BY c.id, e.name, c.meta_number, c.threshold, 
-			e.control_code, c.description
-		  HAVING  (sum(p.due) >= c.threshold
-			OR sum(case when a.batch_id = in_batch_id then 1
+                GROUP BY c.id, e.name, c.meta_number, c.threshold, 
+                        e.control_code, c.description
+                  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
-	LOOP
-		RETURN NEXT payment_item;
-	END LOOP;
+        LOOP
+                RETURN NEXT payment_item;
+        END LOOP;
 END;
 $$ LANGUAGE plpgsql;
 
 COMMENT ON FUNCTION payment_get_all_contact_invoices
 (in_account_class int, in_business_id int, in_currency char(3),
-	in_date_from date, in_date_to date, in_batch_id int, 
-	in_ar_ap_accno text, in_meta_number text) IS
+        in_date_from date, in_date_to date, in_batch_id int, 
+        in_ar_ap_accno text, in_meta_number text) IS
 $$
 This function takes the following arguments (all prefaced with in_ in the db):
 account_class: 1 for vendor, 2 for customer

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