Adding some SQL queries for reports that are being moved out of the old codebase because they are broken

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

1 files changed, 169 insertions, 0 deletions

diff --git a/sql/modules/Report.sql b/sql/modules/Report.sql
new file mode 100644
index 00000000..c63641db
--- /dev/null
+++ b/sql/modules/Report.sql
@@ -0,0 +1,169 @@
+CREATE TYPE report_aging_item AS (
+	entity_id int,
+	meta_number varchar(24),
+	name text,
+	address1 text,
+	address2 text,
+	address3 text,
+	city_province text,
+	mail_code text,
+	country text,
+	contact_name text,
+	email text,
+	phone text,
+	fax text,
+	invnumber text,
+	transdate date,
+	till varchar(20),
+	ordnumber text,
+	ponumber text,
+	notes text,
+	c0 numeric,
+	c30 numeric,
+	c60 numeric,
+	c90 numeric,
+	duedate date,
+	id int,
+	curr varchar(3),
+	exchangerate numeric,
+	line_items text[][]
+);
+
+
+CREATE AGGREGATE as_array (
+	BASETYPE = ANYELEMENT,
+	STYPE = ANYARRAY,
+	SFUNC = ARRAY_APPEND,
+	INITCOND = '{}'
+);
+
+CREATE AGGREGATE compound_array (
+	BASETYPE = ANYARRAY,
+	STYPE = ANYARRAY,
+	SFUNC = ARRAY_CAT,
+	INITCOND = '{}'
+);
+
+CREATE OR REPLACE FUNCTION 
+report_invoice_aging(in_entity_id int, in_entity_class int) 
+RETURNS SETOF report_aging_item
+AS
+$$
+DECLARE
+	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;
+END;
+$$ language plpgsql;
+