Request for comment on trial balance function

[David Mora <..hidden..>]

Hi, i just built this sql to get a report balance with totals on all
the headers using different levels, my english
is not great but ill try to explain:

When you build a trial balance you want the headers to show you a
debit, credit, new balance, old balance amount, but currently this is
not supported
the only option you have is to get a subtotal of the accounts on the
same header but it does not do header transversal, this code is
intendend to do that
please check it out, there is no UI for this report yet because i
havent had the time to check how this is done on lsmb.

If you can help me out with this it would be great, thanks a lot.

Please excuse my english and sql, im not good at either :P, if you can
make it work better or cleaner please let me know.


DM


BEGIN SQL CODE:

DROP TABLE tempchart CASCADE;

DROP TYPE trial_balance CASCADE;
CREATE TYPE trial_balance AS
(
id int,
charttype char(1),
category char(1),
accno text,
description text,
old_balance numeric,
debit numeric,
credit numeric,
new_balance numeric);

-- Primero se genera la tabla temporal

CREATE OR REPLACE FUNCTION report_balance (in_from_date date,
in_to_date date, in_from_accno text, in_to_accno text)
RETURNS SETOF trial_balance AS
$$
DECLARE out_balance trial_balance;
BEGIN
 CREATE TEMPORARY TABLE tempchart AS
 SELECT ch.id, ch.charttype, ch.category, ch.accno, ch.description,
oldbal.old_balance AS old_balance,
        dbt.debit AS debit, crd.credit AS credit, bal.new_balance AS
new_balance
       FROM chart ch
       LEFT JOIN
       (SELECT chart_id, SUM(amount)*-1 as debit
        FROM acc_trans
        WHERE amount < 0
        AND (transdate >= in_from_date OR in_from_date IS NULL)
        AND   (transdate <= in_to_date OR in_to_date IS NULL)
        GROUP BY chart_id) dbt ON (dbt.chart_id = ch.id)
       LEFT JOIN
       (SELECT chart_id, SUM(amount) as credit
        FROM acc_trans
        WHERE amount > 0
        AND (transdate >= in_from_date OR in_from_date IS NULL)
        AND   (transdate <= in_to_date OR in_to_date IS NULL)
        GROUP BY chart_id) crd ON (crd.chart_id = ch.id)
       LEFT JOIN
       (SELECT a.chart_id,
               (CASE WHEN c.category = 'A' OR c.category = 'E' THEN
                 SUM(a.amount)*-1
                ELSE
                 SUM(a.amount)
                END) as new_balance
        FROM acc_trans a
        JOIN chart c ON (c.id = a.chart_id)
        WHERE (transdate >= in_from_date OR in_from_date IS NULL)
        AND   (transdate <= in_to_date OR in_to_date IS NULL)
        GROUP BY chart_id, c.charttype, c.category) bal ON
(bal.chart_id = ch.id)
       LEFT JOIN
       (SELECT chart_id,
       (CASE WHEN in_from_date IS NOT NULL THEN
         SUM(amount)
       ELSE
         '0'
       END) as old_balance
       FROM acc_trans
       WHERE (transdate < in_from_date OR in_from_date IS NULL)
       GROUP BY chart_id) oldbal ON (oldbal.chart_id = ch.id)
       WHERE ch.charttype = 'A'
       AND (ch.accno >= in_from_accno OR in_from_accno IS NULL)
       AND (ch.accno <= in_to_accno OR in_to_accno IS NULL)
       ORDER BY ch.accno;

 INSERT INTO tempchart (
       SELECT c.id, c.charttype, c.category, c.accno, c.description,
sum(tmp.old_balance) AS old_balance,
              sum(tmp.debit) AS debit, sum(tmp.credit) as credit,
sum(tmp.new_balance) as new_balance
       FROM tempchart tmp
       JOIN chart c ON (tmp.accno LIKE c.accno || '%'  )
       WHERE c.charttype = 'H'
       AND (c.accno >= in_from_accno OR in_from_accno IS NULL)
       AND (c.accno <= in_to_accno OR in_to_accno IS NULL)
       GROUP BY c.id, c.id, c.charttype, c.category, c.accno, c.description
       );

 FOR out_balance IN
         SELECT * FROM tempchart ORDER BY accno
 LOOP
         RETURN NEXT out_balance;
 END LOOP;

 DROP TABLE tempchart;
END;
$$ LANGUAGE PLPGSQL;

Attachment: report_balance.sql
Description: Binary data