SF.net SVN: ledger-smb:[4894] addons/1.3/enhanced_tb/trunk/sql/modules/ trial_balance.sql

[..hidden..]

Revision: 4894
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=4894&view=rev
Author:   einhverfr
Date:     2012-06-14 12:08:58 +0000 (Thu, 14 Jun 2012)
Log Message:
-----------
Enhanced trial balance stored proc works now and passes all tests.

Modified Paths:
--------------
    addons/1.3/enhanced_tb/trunk/sql/modules/trial_balance.sql

Modified: addons/1.3/enhanced_tb/trunk/sql/modules/trial_balance.sql
===================================================================
--- addons/1.3/enhanced_tb/trunk/sql/modules/trial_balance.sql	2012-06-13 11:12:23 UTC (rev 4893)
+++ addons/1.3/enhanced_tb/trunk/sql/modules/trial_balance.sql	2012-06-14 12:08:58 UTC (rev 4894)
@@ -29,7 +29,7 @@
 	out_row         tb_row;
         t_roll_forward  date;
         t_cp            account_checkpoint;
-        ignore_trans    int;
+        ignore_trans    int[];
         t_start_date    date; 
         t_end_date      date;
 BEGIN
@@ -47,9 +47,9 @@
     ELSIF in_date_from IS NULL THEN
        SELECT min(transdate)  INTO t_roll_forward
          FROM (select min(transdate) as transdate from ar
-                union
+                union ALL
                select min(transdate) from ap
-                union
+                union all
                select min(transdate) from gl) gl;
                            
     ELSE
@@ -63,11 +63,35 @@
     END IF;
 
     IF in_ignore_yearend = 'last' THEN
-       SELECT trans_id INTO ignore_trans FROM yearend 
+       SELECT ARRAY[trans_id] INTO ignore_trans FROM yearend 
      ORDER BY transdate DESC LIMIT 1;
+    ELSIF in_ignore_yearend = 'all' THEN
+       SELECT array_agg(trans_id) INTO ignore_trans FROM yearend;
+    ELSE 
+       ignore_trans := '{}';
     END IF;
 
+    IF in_date_to IS NULL THEN
+        SELECT max(transdate) INTO t_end_date FROM acc_trans;
+    ELSE
+        t_end_date := in_date_to;
+    END IF;
+
+
     RETURN QUERY
+       WITH ac (transdate, amount, chart_id) AS (
+       SELECT ac.transdate, ac.amount, ac.chart_id
+         FROM acc_trans ac
+         JOIN (SELECT id, approved, department_id FROM ar UNION ALL
+               SELECT id, approved, department_id FROM ap UNION ALL
+               SELECT id, approved, department_id FROM gl) gl
+                   ON ac.approved and gl.approved and ac.trans_id = gl.id
+        WHERE ac.transdate BETWEEN t_roll_forward + '1 day'::interval 
+                                    AND t_end_date
+              AND ac.trans_id <> ALL(ignore_trans)
+              AND (in_department is null 
+                 or gl.department_id = in_department)
+       )
        SELECT a.id, a.accno, a.description, a.gifi_accno,
          case when in_date_from is null then 0 else
               CASE WHEN a.category IN ('A', 'E') THEN -1 ELSE 1 END 
@@ -90,38 +114,12 @@
               CASE WHEN a.category IN ('A', 'E') THEN -1 ELSE 1 END 
               * (coalesce(cp.amount, 0) + sum(coalesce(ac.amount, 0)))
          FROM account a
-    LEFT JOIN (select acc_trans.*, department_id 
-                 from acc_trans 
-                 JOIN (SELECT id, approved, department_id FROM ar UNION ALL
-                       SELECT id, approved, department_id FROM ap UNION ALL
-                       SELECT id, approved, department_id FROM gl) gl
-                       ON acc_trans.approved and gl.approved 
-                              and acc_trans.trans_id = gl.id
-                WHERE t_roll_forward is null
-                union
-               select acc_trans.*, department_id 
-                 from acc_trans
-                 JOIN (SELECT id, approved, department_id FROM ar UNION ALL
-                       SELECT id, approved, department_id FROM ap UNION ALL
-                       SELECT id, approved, department_id FROM gl) gl 
-                       ON acc_trans.approved and gl.approved 
-                              and acc_trans.trans_id = gl.id
-                where acc_trans.transdate >= t_roll_forward) ac 
-              ON ac.chart_id = a.id
+    LEFT JOIN ac ON ac.chart_id = a.id
     LEFT JOIN account_checkpoint cp ON cp.account_id = a.id
               AND end_date = t_roll_forward
-    LEFT JOIN yearend y ON y.trans_id = ac.trans_id
         WHERE (in_accounts IS NULL OR in_accounts = '{}' 
                OR a.id = ANY(in_accounts))
-             and (ac.trans_id is null or 
-             (cp.end_date is null or cp.end_date < ac.transdate)
-             AND (in_date_to is null or ac.transdate <= in_date_to))
-   --           AND (in_department is null 
-   --              or ac.department_id = in_department)
-   --           AND (in_heading IS NULL OR in_heading = a.heading)
-   --           AND (in_ignore_yearend = 'none' OR
-   --                (in_ignore_yearend = 'last' AND ac.trans_id <> ignore_trans)
-   --                OR y.trans_id is null)
+              AND (in_heading IS NULL OR in_heading = a.heading)
      GROUP BY a.id, a.accno, a.description, a.category, a.gifi_accno,
               cp.end_date, cp.account_id, cp.amount, cp.debits, cp.credits
      ORDER BY a.accno;

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