path: root/sql/modules/Report.sql

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;