SF.net SVN: ledger-smb:[5046] trunk/sql

[..hidden..]

Revision: 5046
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=5046&view=rev
Author:   einhverfr
Date:     2012-07-26 02:15:55 +0000 (Thu, 26 Jul 2012)
Log Message:
-----------
Initial stored procedures for income statements
Also adding a few views which can be used to replace inline views in many queries.

Modified Paths:
--------------
    trunk/sql/Pg-database.sql
    trunk/sql/modules/PNL.sql

Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql	2012-07-25 14:44:19 UTC (rev 5045)
+++ trunk/sql/Pg-database.sql	2012-07-26 02:15:55 UTC (rev 5046)
@@ -4739,4 +4739,39 @@
 );
 
 ALTER TABLE cr_report_line ADD FOREIGN KEY(ledger_id) REFERENCES acc_trans(entry_id);
+
+
+CREATE VIEW tx_report AS
+SELECT id, reference, null::int as entity_credit_account, 'gl' as table, 
+       approved
+  FROM gl
+UNION ALL
+SELECT id, invnumber, entity_credit_account, 'ap', approved
+  FROM ap
+UNION ALL
+SELECT id, invnumber, entity_credit_account, 'ar', approved
+  FROM ar;
+
+COMMENT ON VIEW tx_report IS
+$$ This view provides join and approval information for transactions.$$;
+
+CREATE VIEW cash_impact AS
+SELECT id, '1'::numeric, 'gl' as rel FROM gl
+UNION ALL
+SELECT id, gl.amount / (gl.amount - sum(ac.amount * -1)), 'ar' as rel
+  FROM ar gl
+  JOIN acc_trans ac ON ac.trans_id = gl.id
+  JOIN account_link al ON ac.chart_id = al.account_id and al.description = 'AR'
+ GROUP BY gl.id, gl.amount
+UNION ALL
+SELECT id, gl.amount / (gl.amount - sum(ac.amount)), 'ap' as rel
+  FROM ap gl
+  JOIN acc_trans ac ON ac.trans_id = gl.id
+  JOIN account_link al ON ac.chart_id = al.account_id and al.description = 'AP'
+ GROUP BY gl.id, gl.amount;
+
+COMMENT ON VIEW cash_impact IS
+$$ This view is used by cash basis reports to determine the fraction of a
+transaction to be counted.$$;
+
 commit;

Modified: trunk/sql/modules/PNL.sql
===================================================================
--- trunk/sql/modules/PNL.sql	2012-07-25 14:44:19 UTC (rev 5045)
+++ trunk/sql/modules/PNL.sql	2012-07-26 02:15:55 UTC (rev 5046)
@@ -21,6 +21,81 @@
     amount numeric
 );
 
+CREATE OR REPLACE FUNCTION pnl__income_statement_accrual
+(in_from_date date, in_to_date date, in_business_units int[])
+RETURNS SETOF pnl_line AS
+$$
+WITH RECURSIVE bu_tree (id, parent) AS (
+      SELECT id, null FROM business_unit
+       WHERE id = any(in_business_units)
+      UNION ALL
+      SELECT id, parent 
+        FROM business_unit bu
+        JOIN bu_tree ON bu.parent = bu_tree.id
+)
+   SELECT a.id, a.accno, a.description, a.category, ah.id, ah.accno,
+          ah.description, 
+          CASE WHEN a.category = 'E' THEN -1 ELSE 1 END * sum(ac.amount)
+     FROM account a
+     JOIN acc_trans ac ON a.id = ac.chart_id AND ac.approved
+     JOIN tx_report gl ON ac.trans_id = gl.id
+LEFT JOIN (select array_agg(entry_id) 
+             FROM business_unit_ac buac
+             JOIN bu_tree ON bu_tree.id = buac.bu_id) bu 
+          ON (ac.entry_id = any(b_unit_ids))
+    WHERE ac.approved is true AND ac.transdate BETWEEN $1 AND $2
+          AND (in_business_units = '{}' 
+              OR in_business_units IS NULL OR ac.entry_id IN)
+ GROUP BY a.id, a.accno, a.description, a.category, 
+          ah.id, ah.accno, ah.description
+ ORDER BY a.category DESC, a.accno ASC;
+$$ LANGUAGE SQL;
+
+CREATE OR REPLACE FUNCTION pnl__income_statement_cash
+(in_from_date date, in_to_date date, in_business_units int[])
+RETURNS SETOF pnl_line AS
+$$
+WITH RECURSIVE bu_tree (id, parent) AS (
+      SELECT id, null FROM business_unit
+       WHERE id = any(in_business_units)
+      UNION ALL
+      SELECT id, parent 
+        FROM business_unit bu
+        JOIN bu_tree ON bu.parent = bu_tree.id
+)
+   SELECT a.id, a.accno, a.description, a.category, ah.id, ah.accno,
+          ah.description, 
+          CASE WHEN a.category = 'E' THEN -1 ELSE 1 END * sum(ac.amount) *
+          ca.impact
+     FROM account a
+     JOIN acc_trans ac ON a.id = ac.chart_id AND ac.approved
+     JOIN tx_report gl ON ac.trans_id = gl.id
+     JOIN cash_impact ca ON gl.id = ca.id
+LEFT JOIN (select array_agg(entry_id) 
+             FROM business_unit_ac buac
+             JOIN bu_tree ON bu_tree.id = buac.bu_id) bu 
+          ON (ac.entry_id = any(b_unit_ids))
+    WHERE ac.approved is true AND ac.transdate BETWEEN $1 AND $2
+          AND (in_business_units = '{}' 
+              OR in_business_units IS NULL OR ac.entry_id IN)
+ GROUP BY a.id, a.accno, a.description, a.category, 
+          ah.id, ah.accno, ah.description
+ ORDER BY a.category DESC, a.accno ASC;
+$$ LANGUAGE SQL;
+
+CREATE OR REPLACE FUNCTION pnl__invoice(in_id int) RETURNS SETOF pnl_line AS
+$$
+SELECT a.id, a.accno, a.description, a.category, 
+       ah.id, ah.accno, ah.description,
+       CASE WHEN a.category = 'E' THEN -1 ELSE 1 END * sum(ac.amount)
+  FROM account a
+  JOIN acc_trans ac ON a.id = ac.chart_id
+ WHERE ac.approved is true and ac.trans_id = $1
+ GROUP BY a.id, a.accno, a.description, a.category, 
+          ah.id, ah.accno, ah.description
+ ORDER BY a.category DESC, a.accno ASC;
+$$ LANGUAGE sql;
+
 CREATE OR REPLACE FUNCTION pnl__customer
 (in_id int, in_from_date date, in_to_date date)
 RETURNS SETOF pnl_line AS

This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.