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;