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.
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,
RETURNS SETOF gl_report_item AS
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));
t_balance := null;
FOR retval IN
WITH RECURSIVE bu_tree (id, path) AS (
SELECT id, id::text AS path
WHERE parent_id is null
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,
sum(ac.amount) over (rows unbounded preceding) + t_balance
FROM (select id, 'gl' as type, false as invoice, reference,
null::text as till
SELECT ar.id, 'ar', invoice, invnumber, e.name, approved, till
JOIN entity_credit_account eca ON ar.entity_credit_account
JOIN entity e ON e.id = eca.entity_id
SELECT ap.id, 'ap', invoice, invnumber, e.name, approved,
null as till
JOIN entity_credit_account eca ON ap.entity_credit_account
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
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
RETURN NEXT retval;
$$ 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