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

SF.net SVN: ledger-smb:[4737] trunk/sql/modules/Report.sql



Revision: 4737
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=4737&view=rev
Author:   einhverfr
Date:     2012-05-20 13:48:53 +0000 (Sun, 20 May 2012)
Log Message:
-----------
Consolidation and correction of new aging report logic

Modified Paths:
--------------
    trunk/sql/modules/Report.sql

Modified: trunk/sql/modules/Report.sql
===================================================================
--- trunk/sql/modules/Report.sql	2012-05-20 11:36:31 UTC (rev 4736)
+++ trunk/sql/modules/Report.sql	2012-05-20 13:48:53 UTC (rev 4737)
@@ -15,9 +15,6 @@
 	mail_code text,
 	country text,
 	contact_name text,
-	email text,
-	phone text,
-	fax text,
 	invnumber text,
 	transdate date,
 	till varchar(20),
@@ -36,8 +33,9 @@
 );
 
 
-CREATE OR REPLACE FUNCTION 
-report__invoice_aging(in_entity_id int, in_entity_class int) 
+CREATE OR REPLACE FUNCTION report__invoice_aging_detail
+(in_entity_id int, in_entity_class int, in_accno text, in_to_date timestamp,
+ in_business_units int[])) 
 RETURNS SETOF report_aging_item
 AS
 $$
@@ -45,117 +43,109 @@
 	item report_aging_item;
 BEGIN
 	IF in_entity_class = 1 THEN
-		FOR item IN
-			SELECT c.entity_id, 
-			       c.meta_number, e.name,
-			       l.line_one as address1, l.line_two as address2, 
-			       l.line_three as address3,
-			       l.city_province, l.mail_code,
-			       country.name as country, 
-			       '' as contact_name, '' as email,
-		               '' as phone, '' as fax, 
-		               a.invnumber, a.transdate, a.till, a.ordnumber, 
-			       a.ponumber, a.notes, 
-			       CASE WHEN 
-			                 EXTRACT(days FROM age(a.transdate)/30) 
-			                 = 0
-			                 THEN (a.amount - a.paid) ELSE 0 END
-			            as c0, 
-			       CASE WHEN EXTRACT(days FROM age(a.transdate)/30)
-			                 = 1
-			                 THEN (a.amount - a.paid) ELSE 0 END
-			            as c30, 
-			       CASE WHEN EXTRACT(days FROM age(a.transdate)/30)
-			                 = 2
-			                 THEN (a.amount - a.paid) ELSE 0 END
-			            as c60, 
-			       CASE WHEN EXTRACT(days FROM age(a.transdate)/30)
-			                 > 2
-			                 THEN (a.amount - a.paid) ELSE 0 END
-			            as c90, 
-			       a.duedate, a.id, a.curr,
-			       COALESCE((SELECT sell FROM exchangerate ex
-			         WHERE a.curr = ex.curr
-			              AND ex.transdate = a.transdate), 1)
-			       AS exchangerate,
-				(SELECT compound_array(ARRAY[[p.partnumber,
-						i.description, i.qty::text]])
-					FROM parts p 
-					JOIN invoice i ON (i.parts_id = p.id)
-					WHERE i.trans_id = a.id) AS line_items
-			  FROM ap a
-			  JOIN entity_credit_account c USING (entity_id)
-			  JOIN entity e ON (e.id = c.entity_id)
-			 CROSS JOIN location l
-			  JOIN country ON (country.id = l.country_id)
-			 WHERE a.entity_id like coalesce(in_entity_id::text, '%')
-				AND l.id = (SELECT min(location_id) 
-					FROM company_to_location 
-					WHERE company_id = (select min(id) 
-						FROM company
-						WHERE entity_id = c.entity_id))
-			ORDER BY entity_id, curr, transdate, invnumber
-		LOOP
-			return next item;
-		END LOOP;
-	ELSIF in_entity_class = 2 THEN
-		FOR item IN 
-			SELECT c.entity_id, 
-			       c.meta_number, e.name,
-			       l.line_one as address1, l.line_two as address2, 
-			       l.line_three as address3,
-			       l.city_province, l.mail_code,
-			       country.name as country, 
-			       '' as contact_name, '' as email,
-		               '' as phone, '' as fax, 
-		               a.invnumber, a.transdate, a.till, a.ordnumber, 
-			       a.ponumber, a.notes, 
-			       CASE WHEN 
-			                 EXTRACT(days FROM age(a.transdate)/30) 
-			                 = 0
-			                 THEN (a.amount - a.paid) ELSE 0 END
-			            as c0, 
-			       CASE WHEN EXTRACT(days FROM age(a.transdate)/30)
-			                 = 1
-			                 THEN (a.amount - a.paid) ELSE 0 END
-			            as c30, 
-			       CASE WHEN EXTRACT(days FROM age(a.transdate)/30)
-			                 = 2
-			                 THEN (a.amount - a.paid) ELSE 0 END
-			            as c60, 
-			       CASE WHEN EXTRACT(days FROM age(a.transdate)/30)
-			                 > 2
-			                 THEN (a.amount - a.paid) ELSE 0 END
-			            as c90, 
-			       a.duedate, a.id, a.curr,
-			       (SELECT buy FROM exchangerate ex
-			         WHERE a.curr = ex.curr
-			              AND ex.transdate = a.transdate) 
-			       AS exchangerate,
-				(SELECT compound_array(ARRAY[[p.partnumber,
-						i.description, i.qty::text]])
-					FROM parts p 
-					JOIN invoice i ON (i.parts_id = p.id)
-					WHERE i.trans_id = a.id) AS line_items
-			  FROM ar a
-			  JOIN entity_credit_account c USING (entity_id)
-			  JOIN entity e ON (e.id = c.entity_id)
-			 CROSS JOIN location l
-			  JOIN country ON (country.id = l.country_id)
-			 WHERE a.entity_id like coalesce(in_entity_id::text, '%')
-				AND l.id = (SELECT min(location_id) 
-					FROM company_to_location 
-					WHERE company_id = (select min(id) 
-						FROM company
-						WHERE entity_id = c.entity_id))
-			ORDER BY entity_id, curr, transdate, invnumber
-		LOOP
-			return next item;
-		END LOOP;
-	ELSE
-		RAISE EXCEPTION 'Entity Class % unsupported in aging report', 
-			in_entity_class;
-	END IF;
+	FOR item IN
+                  WITH RECURSIVE bu_tree (id, path) AS (
+                SELECT id, id::text AS path
+                  FROM business_unit
+                 WHERE id in(in_business_units)
+                 UNION
+                SELECT bu.id, bu_tree.path || ',' || bu.id
+                  FROM business_unit bu
+                  JOIN bu_tree ON bu_tree.id = bu.parent_id
+                       )
+		SELECT c.entity_id, c.meta_number, e.name,
+		       l.line_one as address1, l.line_two as address2, 
+		       l.line_three as address3,
+		       l.city_province, l.mail_code, country.name as country, 
+		       e.name as contact_name, 
+	               a.invnumber, a.transdate, a.till, a.ordnumber, 
+		       a.ponumber, a.notes, 
+		       CASE WHEN 
+		                 EXTRACT(days FROM age(min(ac.transdate), 
+                                                       coalesce(in_to_date,
+                                                                now())/30) 
+		                 = 0
+		                 THEN (a.amount - (a.sign * sum(ac.amount)) 
+                            ELSE 0 END
+		            as c0, 
+		       CASE WHEN EXTRACT(days FROM age(min(ac.transdate), 
+                                                       coalesce(in_to_date,
+                                                                now())/30)
+		                 = 1
+		                 THEN (a.amount - (a.sign * sum(ac.amount)) 
+                            ELSE 0 END
+		            as c30, 
+		       CASE WHEN EXTRACT(days FROM age(min(ac.transdate),
+                                                       coalesce(in_to_date,
+                                                                now())/30)
+		                 = 2
+		            THEN (a.amount - (a.sign * sum(ac.amount),
+                                                       coalesce(in_to_date,
+                                                                now()) 
+                            ELSE 0 END
+		            as c60, 
+		       CASE WHEN EXTRACT(days FROM age(min(ac.transdate),
+                                                       coalesce(in_to_date,
+                                                                now())/30)
+		                 > 2
+		            THEN (a.amount - (a.sign * sum(ac.amount)) 
+                            ELSE 0 END
+		            as c90, 
+		       a.duedate, a.id, a.curr,
+		       COALESCE((SELECT sell FROM exchangerate ex
+		         WHERE a.curr = ex.curr
+		              AND ex.transdate = a.transdate), 1)
+		       AS exchangerate,
+			(SELECT compound_array(ARRAY[[p.partnumber,
+					i.description, i.qty::text]])
+				FROM parts p 
+				JOIN invoice i ON (i.parts_id = p.id)
+				WHERE i.trans_id = a.id) AS line_items
+		  FROM (select id, invnumber, till, ordnumber, amount, duedate,
+                               curr, ponumber, notes, entity_credit_account,
+                               -1 AS sign
+                          FROM ar
+                         WHERE in_entity_class = 2
+                         UNION 
+                        SELECT id, invnumber, null, ordnumber, amount, duedate,
+                               curr, ponumber, notes, entity_credit_account,
+                               1 as sign
+                          FROM ap
+                         WHERE in_entity_class = 1) a
+                  JOIN acc_trans ac ON ac.trans_id = a.id
+                  JOIN account acc ON ac.chart_id = acc.id
+                  JOIN account_link acl ON acl.account_id = acc.id
+                       AND ((in_entity_class = 1 
+                              AND acl.description = 'AP')
+                           OR (in_entity_class = 2
+                              AND acl.description = 'AR'))
+		  JOIN entity_credit_account c 
+                       ON a.entity_credit_account = c.id
+		  JOIN entity e ON (e.id = c.entity_id)
+             LEFT JOIN business_unit_ac buac ON ac.entry_id = buac.entry_id
+             LEFT JOIN bu_tree ON buac.bu_id = bu_tree.id
+	     LEFT JOIN entity_to_location e2l 
+                       ON e.id = e2l.entity_id 
+                       AND e2l.location_class = 3
+             LEFT JOIN location l ON l.id = e2l.location_id
+	     LEFT JOIN country ON (country.id = l.country_id)
+                 WHERE (e.id = in_entity_id OR in_entity_id IS NULL)
+                       AND (in_accno IS NULL or acc.accno = in_accno)
+              GROUP BY c.entity_id, c.meta_number, e.name,
+                       l.line_one as address1, l.line_two as address2,
+                       l.line_three as address3,
+                       l.city_province, l.mail_code, country.name as country,
+                       e.name as contact_name,
+                       a.invnumber, a.transdate, a.till, a.ordnumber,
+                       a.ponumber, a.notes,
+                       a.duedate, a.id, a.curr
+                HAVING in_business_units is null or in_business_units 
+                       <@ compound_array(string_to_array(bu_tree.path, 
+                                         ',')::int[])
+	      ORDER BY entity_id, curr, transdate, invnumber
+	LOOP
+		return next item;
+        END LOOP;
 END;
 $$ language plpgsql;
 

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