The stored procedures follow the conventions and design principles laid out in the very first post on this blog. Arguments are prefixed with 'in_' and given consistent names from report criteria. Anyone wanting to write reports should start with that post in terms of understanding where to start.
Additionally there are a number of standardized conventions for common elements. The most important of these currently is that many dates require a date range, and the arguments here are standardized as in_date_from and in_date_to (corresponding to date_from and date_to on the application side). The conventions are set up on the Perl side so these will be covered in the next section.
In general, there are a number of do's and dont's associated with these procedures.
1. Do provide a default ordering that is useful.
2. Don't provide windowing functions that depend on ordering that could be overridden. Running totals should not be implemented here. As a good general rule, if you use ROWS UNBOUNDED PROCEDING in one of these functions, that's a red flag.
3. Do try to have one central query with, possibly, some small support logic.
4. Do define your types so several different reports can share the same output type.
5. Do write test scripts to test your reporting functions. Use transactions that roll back.
7. Do use plpgsql instead of sql if you have more than a few arguments. named arguments are easier to read. Once we can drop support for 9.2 and lower this will cease to be an issue though.
An example:
CREATE OR REPLACE FUNCTION report__gl
(in_reference text, in_accno text, in_category char(1),
in_source text, in_memo text, in_description text, in_from_date date,
in_to_date date, in_approved bool, in_from_amount numeric, in_to_amount numeric,
in_business_units int[])
RETURNS SETOF gl_report_item AS
$$
DECLARE
retval gl_report_item;
t_balance numeric;
t_chart_id int;
BEGIN
IF in_from_date IS NULL THEN
t_balance := 0;
ELSIF in_accno IS NOT NULL THEN
SELECT id INTO t_chart_id FROM account WHERE accno = in_accno;
t_balance := account__obtain_balance(in_from_date ,
(select id from account
where accno = in_accno));
ELSE
t_balance := null;
END IF;
FOR retval IN
WITH RECURSIVE bu_tree (id, path) AS (
SELECT id, id::text AS path
FROM business_unit
WHERE parent_id is null
UNION
SELECT bu.id, bu_tree.path || ',' || bu.id
FROM business_unit bu
JOIN bu_tree ON bu_tree.id = bu.parent_id
)
SELECT g.id, g.type, g.invoice, g.reference, g.description, ac.transdate,
ac.source, ac.amount, c.accno, c.gifi_accno,
g.till, ac.cleared, ac.memo, c.description AS accname,
ac.chart_id, ac.entry_id,
sum(ac.amount) over (rows unbounded preceding) + t_balance
as running_balance,
compound_array(ARRAY[ARRAY[bac.class_id, bac.bu_id]])
FROM (select id, 'gl' as type, false as invoice, reference,
description, approved,
null::text as till
FROM gl
UNION
SELECT ar.id, 'ar', invoice, invnumber, e.name, approved, till
FROM ar
JOIN entity_credit_account eca ON ar.entity_credit_account
= eca.id
JOIN entity e ON e.id = eca.entity_id
UNION
SELECT ap.id, 'ap', invoice, invnumber, e.name, approved,
null as till
FROM ap
JOIN entity_credit_account eca ON ap.entity_credit_account
= eca.id
JOIN entity e ON e.id = eca.entity_id) g
JOIN acc_trans ac ON ac.trans_id = g.id
JOIN account c ON ac.chart_id = c.id
LEFT JOIN business_unit_ac bac ON ac.entry_id = bac.entry_id
LEFT JOIN bu_tree ON bac.bu_id = bu_tree.id
WHERE (g.reference ilike in_reference || '%' or in_reference is null)
AND (c.accno = in_accno OR in_accno IS NULL)
AND (ac.source ilike '%' || in_source || '%'
OR in_source is null)
AND (ac.memo ilike '%' || in_memo || '%' OR in_memo is null)
AND (in_description IS NULL OR
g.description
@@
plainto_tsquery(get_default_lang()::regconfig, in_description))
AND (transdate BETWEEN in_from_date AND in_to_date
OR (transdate >= in_from_date AND in_to_date IS NULL)
OR (transdate <= in_to_date AND in_from_date IS NULL)
OR (in_to_date IS NULL AND in_from_date IS NULL))
AND (in_approved is false OR (g.approved AND ac.approved))
AND (in_from_amount IS NULL OR ac.amount >= in_from_amount)
AND (in_to_amount IS NULL OR ac.amount <= in_to_amount)
AND (in_category = c.category OR in_category IS NULL)
GROUP BY g.id, g.type, g.invoice, g.reference, g.description, ac.transdate,
ac.source, ac.amount, c.accno, c.gifi_accno,
g.till, ac.cleared, ac.memo, c.description,
ac.chart_id, ac.entry_id, ac.trans_id
HAVING in_business_units is null or in_business_units
<@ compound_array(string_to_array(bu_tree.path, ',')::int[])
ORDER BY ac.transdate, ac.trans_id, c.accno
LOOP
RETURN NEXT retval;
END LOOP;
END;
$$ language plpgsql;
The above is the function that provides the GL report and search. It is a monster query and we could use RETURN QUERY but for older versions this seems more reliable. Ideally, the inline view would be moved to a formal one, and a few other tweaks, but it works and works well enough and despite the length and the inline view, it is not hard to debug.
Others in series:
Part 1: Overview
Part 2: Filter Screens
Part 4: Perl Modules
Part 5: Conclusions
No comments:
Post a Comment