Monday, November 11, 2013

Writing Reports in LedgerSMB 1.4, part 3, Stored Procedure Conventions and Best Practices

The stored procedures/UDFs form the heart of the LedgerSMB reports. Everything else basically is there to handle input to the stored procedure and output from it.  It's for this reason that the stored procedures are covered before the Perl modules.

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:

(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
         retval gl_report_item;
         t_balance numeric;
         t_chart_id int;

IF in_from_date IS NULL THEN
   t_balance := 0;
   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
              FROM business_unit
             WHERE parent_id is null
            SELECT, bu_tree.path || ',' ||
              FROM business_unit bu
              JOIN bu_tree ON = bu.parent_id
       SELECT, 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
               SELECT, 'ar', invoice, invnumber,, approved, till
                 FROM ar
                 JOIN entity_credit_account eca ON ar.entity_credit_account
                 JOIN entity e ON = eca.entity_id
               SELECT, 'ap', invoice, invnumber,, approved,
                      null as till
                 FROM ap
                 JOIN entity_credit_account eca ON ap.entity_credit_account
                 JOIN entity e ON = eca.entity_id) g
         JOIN acc_trans ac ON ac.trans_id =
         JOIN account c ON ac.chart_id =
    LEFT JOIN business_unit_ac bac ON ac.entry_id = bac.entry_id
    LEFT JOIN bu_tree ON bac.bu_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
                  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.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