diff options
| -rw-r--r-- | sql/modules/Report.sql | 169 |
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; + |