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

[..hidden..]

Revision: 5340
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=5340&view=rev
Author:   einhverfr
Date:     2012-12-12 04:59:07 +0000 (Wed, 12 Dec 2012)
Log Message:
-----------
Added option for PNL to ignore yearend or not.

Modified Paths:
--------------
    trunk/sql/modules/Fixes.sql
    trunk/sql/modules/PNL.sql
    trunk/sql/modules/test/PNL.sql

Modified: trunk/sql/modules/Fixes.sql
===================================================================
--- trunk/sql/modules/Fixes.sql	2012-12-11 15:48:58 UTC (rev 5339)
+++ trunk/sql/modules/Fixes.sql	2012-12-12 04:59:07 UTC (rev 5340)
@@ -78,6 +78,7 @@
 
 BEGIN;
 
+DROP VIEW IF EXISTS cash_impact;
 CREATE VIEW cash_impact AS
 SELECT id, '1'::numeric AS portion, 'gl' as rel, gl.transdate FROM gl
 UNION ALL

Modified: trunk/sql/modules/PNL.sql
===================================================================
--- trunk/sql/modules/PNL.sql	2012-12-11 15:48:58 UTC (rev 5339)
+++ trunk/sql/modules/PNL.sql	2012-12-12 04:59:07 UTC (rev 5340)
@@ -41,7 +41,7 @@
      JOIN acc_trans ac ON ac.chart_id = a.id
      JOIN invoice i ON i.id = ac.invoice_id
      JOIN account_link l ON l.account_id = a.id
-     JOIN ar ON ar.id = ac.trans_id
+     JOIN ar ON ar.id = ac.trans_id 
 LEFT JOIN (select as_array(bu.path) as bu_ids, entry_id
              from business_unit_inv bui 
              JOIN bu_tree bu ON bui.bu_id = bu.id
@@ -79,12 +79,13 @@
 
 
 CREATE OR REPLACE FUNCTION pnl__income_statement_accrual
-(in_from_date date, in_to_date date, in_business_units int[])
+(in_from_date date, in_to_date date, in_ignore_yearend text, 
+in_business_units int[])
 RETURNS SETOF pnl_line AS
 $$
 WITH RECURSIVE bu_tree (id, parent, path) AS (
       SELECT id, null, row(array[id])::tree_record FROM business_unit
-       WHERE id = any($3)
+       WHERE id = any($4)
       UNION ALL
       SELECT bu.id, parent, row((path).t || bu.id)::tree_record
         FROM business_unit bu
@@ -105,21 +106,30 @@
     WHERE ac.approved is true 
           AND ($1 IS NULL OR ac.transdate >= $1) 
           AND ($2 IS NULL OR ac.transdate <= $2)
-          AND ($3 = '{}' 
-              OR $3 is null or in_tree($3, bu_ids))
+          AND ($4 = '{}' 
+              OR $4 is null or in_tree($4, bu_ids))
           AND a.category IN ('I', 'E')
+          AND ($3 = 'none' 
+               OR ($3 = 'all' 
+                   AND NOT EXISTS (SELECT * FROM yearend WHERE trans_id = gl.id
+                   ))
+               OR ($3 = 'last'
+                   AND NOT EXISTS (SELECT 1 FROM yearend 
+                                   HAVING max(trans_id) = gl.id))
+              )
  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[])
+(in_from_date date, in_to_date date, in_ignore_yearend text, 
+in_business_units int[])
 RETURNS SETOF pnl_line AS
 $$
 WITH RECURSIVE bu_tree (id, parent, path) AS (
       SELECT id, null, row(array[id])::tree_record FROM business_unit
-       WHERE id = any($3)
+       WHERE id = any($4)
       UNION ALL
       SELECT bu.id, parent, row((path).t || bu.id)::tree_record
         FROM business_unit bu
@@ -145,9 +155,17 @@
          GROUP BY entry_id) bu 
           ON (ac.entry_id = bu.entry_id)
     WHERE ac.approved is true 
-          AND ($3 = '{}' 
-              OR $3 is null or in_tree($3, bu_ids))
+          AND ($4 = '{}' 
+              OR $4 is null or in_tree($4, bu_ids))
           AND a.category IN ('I', 'E')
+          AND ($3 = 'none' 
+               OR ($3 = 'all' 
+                   AND NOT EXISTS (SELECT * FROM yearend WHERE trans_id = gl.id
+                   ))
+               OR ($3 = 'last'
+                   AND NOT EXISTS (SELECT 1 FROM yearend 
+                                   HAVING max(trans_id) = gl.id))
+              )
  GROUP BY a.id, a.accno, a.description, a.category, 
           ah.id, ah.accno, ah.description
  ORDER BY a.category DESC, a.accno ASC;

Modified: trunk/sql/modules/test/PNL.sql
===================================================================
--- trunk/sql/modules/test/PNL.sql	2012-12-11 15:48:58 UTC (rev 5339)
+++ trunk/sql/modules/test/PNL.sql	2012-12-12 04:59:07 UTC (rev 5340)
@@ -15,8 +15,8 @@
 
 INSERT INTO country_tax_form (country_id, form_name, id) VALUES (232, 'Testing Form', -511);
 
-INSERT INTO parts (id, partnumber, description) values (-255, 'test1', 'test 1');
-INSERT INTO parts (id, partnumber, description) values (-256, 'test2', 'test 2');
+INSERT INTO parts (id, partnumber, description) values (-255, '-test1', 'test 1');
+INSERT INTO parts (id, partnumber, description) values (-256, '-test2', 'test 2');
 
 -- Set up an ECAs, for AP.
 
@@ -212,39 +212,39 @@
 
 INSERT INTO test_result(test_name, success)
 SELECT 'Account -1001 shows up in accrual income statement', count(*) = 1
-  FROM pnl__income_statement_accrual(date1(), date2() - 1, ARRAY[]::int[])
+  FROM pnl__income_statement_accrual(date1(), date2() - 1, 'none', ARRAY[]::int[])
  WHERE account_id = -1001;
 
 INSERT INTO test_result(test_name, success)
 SELECT 'Account -1001 accrual total 4040', amount = 4040
-  FROM pnl__income_statement_accrual(date1(), date2() - 1, ARRAY[]::int[])
+  FROM pnl__income_statement_accrual(date1(), date2() - 1, 'none', ARRAY[]::int[])
  WHERE account_id = -1001;
 
 INSERT INTO test_result(test_name, success)
 SELECT 'Account -1001 does not show up in future accrual pnl', count(*) = 0
-  FROM pnl__income_statement_accrual(date2(), date2() + 365, ARRAY[]::int[])
+  FROM pnl__income_statement_accrual(date2(), date2() + 365, 'none', ARRAY[]::int[])
  WHERE account_id = -1001;
 
 INSERT INTO test_result(test_name, success)
 SELECT 'Account -1001 shows up in cash income statement', count(*) = 1
-  FROM pnl__income_statement_cash(date1(), date2() - 1, ARRAY[]::int[])
+  FROM pnl__income_statement_cash(date1(), date2() - 1, 'none', ARRAY[]::int[])
  WHERE account_id = -1001;
 
 INSERT INTO test_result(test_name, success)
 SELECT 'Account -1001 cash total 3030', amount = 3030
-  FROM pnl__income_statement_cash(date1(), date2() - 1, ARRAY[]::int[])
+  FROM pnl__income_statement_cash(date1(), date2() - 1, 'none', ARRAY[]::int[])
  WHERE account_id = -1001;
 
-SELECT * FROM pnl__income_statement_cash(date1(), date2() - 1, ARRAY[]::int[]);
+SELECT * FROM pnl__income_statement_cash(date1(), date2() - 1, 'none', ARRAY[]::int[]);
 
 INSERT INTO test_result(test_name, success)
 SELECT 'Account -1001 shows up in future cash pnl', count(*) = 1
-  FROM pnl__income_statement_cash(date2() - 5, date2() + 20, ARRAY[]::int[])
+  FROM pnl__income_statement_cash(date2() - 5, date2() + 20, 'none', ARRAY[]::int[])
  WHERE account_id = -1001;
 
 INSERT INTO test_result(test_name, success)
 SELECT 'Account -1001 future cash total 505', sum(amount) = 505
-  FROM pnl__income_statement_cash(date2() - 5, date2() + 20, ARRAY[]::int[])
+  FROM pnl__income_statement_cash(date2() - 5, date2() + 20, 'none', ARRAY[]::int[])
  WHERE account_id = -1001;
 
 SELECT * FROM test_result;

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