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

[..hidden..]

Revision: 5209
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=5209&view=rev
Author:   einhverfr
Date:     2012-11-19 08:58:47 +0000 (Mon, 19 Nov 2012)
Log Message:
-----------
Some fixes for PNL functions

Modified Paths:
--------------
    trunk/sql/modules/PNL.sql
    trunk/sql/modules/Util.sql

Modified: trunk/sql/modules/PNL.sql
===================================================================
--- trunk/sql/modules/PNL.sql	2012-11-19 07:21:14 UTC (rev 5208)
+++ trunk/sql/modules/PNL.sql	2012-11-19 08:58:47 UTC (rev 5209)
@@ -14,7 +14,7 @@
     account_id int,
     account_number text,
     account_description text,
-    account_category text,
+    account_category char,
     account_heading_id int,
     account_heading_number text,
     account_heading_description text,
@@ -25,42 +25,58 @@
 (in_from_date date, in_to_date date, in_parts_id int, in_business_units int[])
 RETURNS SETOF onl_line AS 
 $$
-WITH RECURSIVE bu_tree (id, parent) AS (
-      SELECT id, null FROM business_unit
-       WHERE id = any(in_business_units)
+WITH RECURSIVE bu_tree (id, parent, path) AS (
+      SELECT id, null, row(array[id])::tree_record FROM business_unit
+       WHERE id = any($4)
       UNION ALL
-      SELECT id, parent 
+      SELECT bu.id, parent, row((path).t || bu.id)::tree_record
         FROM business_unit bu
-        JOIN bu_tree ON bu.parent = bu_tree.id
+        JOIN bu_tree ON bu.parent_id = bu_tree.id
 )
    SELECT a.id, a.accno, a.description, a.category, ah.id, ah.accno,
           ah.description, 
-          ac.amount * -1 
+          sum(ac.amount) * -1 
      FROM account a
      JOIN account_heading ah on a.heading = ah.id
      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
+LEFT JOIN (select as_array(row(bu.path)) as bu_ids, entry_id
+             from business_unit_inv bui 
+             JOIN bu_tree bu ON bui.bu_id = bu.id
+         GROUP BY entry_id) bui ON bui.entry_id = i.id
     WHERE i.parts_id = $3
           AND (acc_trans.transdate >= $1 OR $1 IS NULL) 
           AND (acc_trans.transdate <= $2 OR $2 IS NULL)
           AND ar.approved
           AND l.description = 'IC_expense'
+          AND ($4 is null or $4 = '{}' OR
+              in_tree($4, bu_ids))
+ GROUP BY a.id, a.accno, a.description, a.category, ah.id, ah.accno,
+          ah.description
     UNION
    SELECT a.id, a.accno, a.description, a.category, ah.id, ah.accno,
           ah.description, 
-          i.sellprice * i.qty * (1 - coalesce(i.disc, 0))
+          sum(i.sellprice * i.qty * (1 - coalesce(i.disc, 0)))
      FROM parts p
      JOIN invoice i ON i.id = p.id
      JOIN acc_trans ac ON ac.invoice_id = i.id
      JOIN account a ON p.income_accno_id = a.id
      JOIN ar ON ar.id = ac.trans_id
      JOIN account_heading ah on a.heading = ah.id
+LEFT JOIN (select as_array(row(bu.path)) as bu_ids, entry_id
+             from business_unit_inv bui 
+             JOIN bu_tree bu ON bui.bu_id = bu.id
+         GROUP BY entry_id) bui ON bui.entry_id = i.id
     WHERE i.parts_id = $3
           AND (acc_trans.transdate >= $1 OR $1 IS NULL) 
           AND (acc_trans.transdate <= $2 OR $2 IS NULL)
           AND ar.approved;
+          AND ($4 is null or $4 = '{}' OR
+              in_tree($4, bu_ids))
+ GROUP BY a.id, a.accno, a.description, a.category, ah.id, ah.accno,
+          ah.description
 $$ language SQL;
 
 
@@ -68,27 +84,29 @@
 (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)
+WITH RECURSIVE bu_tree (id, parent, path) AS (
+      SELECT id, null, row(array[id])::tree_record FROM business_unit
+       WHERE id = any($3)
       UNION ALL
-      SELECT id, parent 
+      SELECT bu.id, parent, row((path).t || bu.id)::tree_record
         FROM business_unit bu
-        JOIN bu_tree ON bu.parent = bu_tree.id
+        JOIN bu_tree ON bu.parent_id = 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 account_heading ah on a.heading = ah.id
      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) 
+LEFT JOIN (select array_agg(path) as bu_ids, 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))
+             JOIN bu_tree ON bu_tree.id = buac.bu_id
+        GROUP BY buac.entry_id) bu
+          ON (ac.entry_id = bu.entry_id)
     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)
+          AND ($3 = '{}' 
+              OR $3 is null or in_tree($3, bu_ids))
  GROUP BY a.id, a.accno, a.description, a.category, 
           ah.id, ah.accno, ah.description
  ORDER BY a.category DESC, a.accno ASC;
@@ -114,13 +132,13 @@
      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) 
+LEFT JOIN (select array_agg(path) 
              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)
+          AND ($3 = '{}' 
+              OR $3 is null or in_tree($3, bu_ids))
  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/Util.sql
===================================================================
--- trunk/sql/modules/Util.sql	2012-11-19 07:21:14 UTC (rev 5208)
+++ trunk/sql/modules/Util.sql	2012-11-19 08:58:47 UTC (rev 5209)
@@ -49,3 +49,23 @@
 	RETURN retval;
 END;
 $$ LANGUAGE PLPGSQL;
+
+
+CREATE TYPE tree_record AS (t int[]);
+
+CREATE OR REPLACE FUNCTION in_tree 
+(in_node_id int, in_search_array tree_record[])
+RETURNS BOOL IMMUTABLE LANGUAGE SQL AS
+$$
+SELECT CASE WHEN count(*) > 0 THEN true ELSE false END
+  FROM unnest($2) r
+ WHERE t @> array[$1];
+$$;
+
+CREATE OR REPLACE FUNCTION in_tree
+(in_node_id int[], in_search_array tree_record[])
+RETURNS BOOL IMMUTABLE LANGUAGE SQL AS
+$$
+SELECT bool_and(in_tree(e, $2))
+  FROM unnest($1) e;
+$$;

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