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;
