[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

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



Revision: 3066
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=3066&view=rev
Author:   aurynn_cmd
Date:     2010-09-22 20:26:06 +0000 (Wed, 22 Sep 2010)

Log Message:
-----------
Adding the completed-for-staging enhanced_tb codebase.

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

Added Paths:
-----------
    addons/1.3/enhanced_tb/trunk/sql/modules/enhanced_tb/
    addons/1.3/enhanced_tb/trunk/sql/modules/enhanced_tb/menu.sql

Added: addons/1.3/enhanced_tb/trunk/sql/modules/enhanced_tb/menu.sql
===================================================================
--- addons/1.3/enhanced_tb/trunk/sql/modules/enhanced_tb/menu.sql	                        (rev 0)
+++ addons/1.3/enhanced_tb/trunk/sql/modules/enhanced_tb/menu.sql	2010-09-22 20:26:06 UTC (rev 3066)
@@ -0,0 +1,64 @@
+-- Create the trial balance menu entries.
+
+BEGIN;
+
+CREATE OR REPLACE FUNCTION enhanced_trial_balance_menu () RETURNS int AS $body$
+
+    DECLARE
+        v_tb_node int;
+        v_tb_attr_node int;
+        
+        v_new_id int;
+        v_reports_id int;
+    BEGIN
+    
+        SELECT id 
+          INTO v_tb_node 
+          FROM menu_node
+         WHERE label = 'Trial Balance';
+       
+        DELETE FROM menu_attribute WHERE node_id = v_tb_node;
+        INSERT INTO menu_attribute (node_id, attribute, value ) 
+             VALUES (v_tb_node, 'menu', 1)
+             RETURNING id INTO v_tb_attr_node;
+        
+        INSERT INTO menu_node (label, parent, position) 
+             VALUES ('New', v_tb_node, 2)
+          RETURNING id INTO v_new_id;
+          
+        INSERT INTO menu_node (label, parent, position)
+             VALUES ('Reports', v_tb_node, 1)
+          RETURNING id INTO v_reports_id;
+        
+        /*
+           Set up the control structire for the menu attribute stuff.
+           
+           First, new. We need:
+           * action
+           * module (code file)
+        
+        */
+        
+        INSERT INTO menu_attribute (node_id, attribute, value)
+             VALUES (v_new_id, 'action', 'new');
+             
+        INSERT INTO menu_attribute (node_id, attribute, value)
+             VALUES (v_new_id, 'module', 'trial_balance.pl');
+        
+        
+        /* Then the reports */
+        
+        INSERT INTO menu_attribute (node_id, attribute, value)
+             VALUES (v_reports_id, 'action', 'reports');
+        
+        INSERT INTO menu_attribute (node_id, attribute_value)
+             VALUES (v_reports_id, 'module', 'trial_balance.pl');
+        
+    END;
+$body$ LANGUAGE PLPGSQL;
+
+DROP FUNCTION enhanced_trial_balance_menu ();
+
+SELECT 'Commit if 2 records.';
+
+SELECT * FROM menu_attribute WHERE node_id = (SELECT c.id FROM menu_node p, menu_node c WHERE p.id = c.parent AND p.label = 'Trial Balance');

Modified: addons/1.3/enhanced_tb/trunk/sql/modules/trial_balance.sql
===================================================================
--- addons/1.3/enhanced_tb/trunk/sql/modules/trial_balance.sql	2010-09-22 18:02:54 UTC (rev 3065)
+++ addons/1.3/enhanced_tb/trunk/sql/modules/trial_balance.sql	2010-09-22 20:26:06 UTC (rev 3066)
@@ -8,15 +8,16 @@
   LANGUAGE 'sql' IMMUTABLE;
 
 
-  create type tb_row AS (
-     account_id int,
-     account_number text,
-     account_desc text,
-     starting_balance numeric,
-     debits numeric,
-     credits numeric,
-     ending_balance numeric
-  );
+create type tb_row AS (
+   account_id int,
+   account_number text,
+   account_desc text,
+   gifi_accno	text,
+   starting_balance numeric,
+   debits numeric,
+   credits numeric,
+   ending_balance numeric
+);
 
 
 CREATE OR REPLACE FUNCTION trial_balance__generate 
@@ -35,29 +36,29 @@
 	yearends        INT[];
     include_trans   INT;
 BEGIN
-
+    
     -- don't bother with checking from/to dates for NULL below.
     IF i_date_from IS NULL THEN
      SELECT INTO date_from '1900-01-01';
     ELSE
      date_from = i_date_from;
     END IF;          
-
+    
     IF i_date_to IS NULL THEN
      SELECT INTO date_to current_date + 1;
     ELSE
      date_to = i_date_to;
     END IF;
-
+				
     -- assemble a list of transactions that shouldn't be included in a trial
     -- balance
     IF in_ignore_yearend = 'last' THEN
         SELECT  trans_id INTO include_trans
-        FROM yearend WHERE transdate <= date_to AND transdate >= date_from
+        FROM yearend WHERE transdate <= date_to
         ORDER by transdate DESC LIMIT 1;
         yearends = yearends || include_trans;
     END IF;
-
+    
     IF in_ignore_yearend = 'all' THEN
         FOR yearend_trans IN SELECT trans_id FROM yearend 
         WHERE transdate <= date_to
@@ -65,35 +66,35 @@
             yearends = yearends || yearend_trans;
         END LOOP;
     END IF;
-
+    
     -- setup checkpoint stuff since can't do this in the from clause so well
     IF i_date_from IS NOT NULL THEN
         SELECT end_date INTO cpa_date 
         FROM account_checkpoint
-        WHERE end_date <= date_from
+        WHERE end_date < date_from
         ORDER BY end_date DESC LIMIT 1;
     ELSE
         cpa_date = NULL;
     END IF;
-
+    
     IF i_date_to IS NOT NULL THEN
         SELECT end_date INTO cpb_date
         FROM account_checkpoint
-        WHERE end_date > date_from AND end_date <= date_to
+        WHERE end_date >= date_from AND end_date <= date_to
         ORDER BY end_date DESC LIMIT 1;
     ELSE
         cpb_date = NULL;
     END IF;
-
+    
 	FOR out_row IN
                 -- main trial balance query
-		SELECT a.id, a.accno, a.description,
+		SELECT a.id, a.accno, a.description, a.gifi_accno,
 			(COALESCE(SUM(CASE WHEN ac.transdate < date_from 
                 THEN COALESCE(ac.amount, 0) ELSE 0 END), 0) + 
                 COALESCE(cpa.amount, 0))
                 * CASE WHEN a.contra THEN -1 ELSE 1 END
 				* CASE WHEN a.category IN ('A', 'E') THEN -1 ELSE 1 END,
-
+			
 			-- debits value is end_checkpoint.debit - start_checkpoint.debit
 			-- + all transactions after end_checkpoint - all transactions
 			-- between the start checkpoint and the start_date.
@@ -118,7 +119,7 @@
 			CASE WHEN cpb.end_date IS NOT NULL THEN
 			    COALESCE(cpa.debits, 0)
 			ELSE 0 END,
-
+			
 			-- see comment in debits for explanation of the case below.            
             COALESCE(cpb.credits, 0) +     
             COALESCE(SUM (CASE WHEN COALESCE(ac.amount, -1) < 0 THEN 0 
@@ -137,7 +138,7 @@
             CASE WHEN cpb.end_date IS NOT NULL THEN 
                 COALESCE(cpa.credits, 0)
             ELSE 0 END,
-
+                  
 			(COALESCE(SUM(CASE WHEN (cpb.end_date IS NULL OR
 			               ac.transdate > cpb.end_date) AND 
 		                   (ac.transdate >= date_from)
@@ -148,7 +149,7 @@
                         THEN -1 ELSE 1 END
                * CASE WHEN a.category IN ('A', 'E') 
                         THEN -1 ELSE 1 END
-
+        
        -- acc_trans has the most rows among all tables. We try to eliminate
        -- most before the join. 
 		FROM account a
@@ -182,8 +183,8 @@
 		WHERE ((in_heading IS NOT NULL AND a.heading = in_heading) OR 
 			  (in_accounts IS NOT NULL AND a.id = any(in_accounts)) OR
 			  (in_accounts IS NULL AND in_heading IS NULL))
-		GROUP BY a.id, a.description, a.accno, a.contra, a.category,
-                 cpa.end_date, cpb.end_date,
+		GROUP BY a.id, a.description, a.accno, a.gifi_accno, 
+				 a.contra, a.category,cpa.end_date, cpb.end_date,
                  cpa.amount, cpb.amount, cpa.debits, cpb.debits,
                  cpa.credits, cpb.credits                                         
 	LOOP
@@ -203,18 +204,17 @@
             THEN ac.amount ELSE 0 END) AS debits,
             SUM(CASE WHEN ac.transdate >= date_from AND ac.amount > 0 
             THEN ac.amount ELSE 0 END) AS credits,
-            SUM(CASE WHEN ac.transdate >= date_from 
-            THEN ac.amount ELSE 0 END) 
+            SUM(ac.amount) 
             * CASE WHEN a.contra THEN -1 ELSE 1 END
 			* CASE WHEN a.category IN ('A', 'E') THEN -1 ELSE 1 END
             AS end
-
+            
 	        INTO adj_row FROM acc_trans ac JOIN account a 
 	        ON (a.id = ac.chart_id) WHERE 
 	        ac.chart_id = out_row.account_id AND 
 	        ac.trans_id = any(yearends) AND ac.transdate <= date_to
 	        GROUP BY a.contra,a.category;
-
+	        
 	        IF (ROW(adj_row.start, adj_row.debits, adj_row.credits,
 	            adj_row.end) != ROW(0,0,0,0)) THEN
 	            out_row.starting_balance = 
@@ -234,33 +234,6 @@
 $$ language plpgsql;
 
 
-CREATE TYPE trial_balance_all_acc_data AS (
-    account_id int,
-    account_number text,
-    account_desc text,
-    starting_balance numeric,
-    debits numeric,
-    credits numeric,
-    ending_balance numeric,
-    gifi_accno text
-);
-
-
-CREATE OR REPLACE FUNCTION trial_balance__account_data (
-    i_date_from DATE, 
-    i_date_to DATE, 
-    in_heading INT, 
-    in_accounts INT[],
-    in_ignore_yearend TEXT,
-    in_department INT
-) RETURNS SETOF trial_balance_all_acc_data AS $body$
-
-    SELECT g.*,
-           a.gifi_accno
-      FROM trial_balance__generate($1, $2, $3, $4, $5, $6) g
-LEFT OUTER JOIN account a ON a.accno = g.account_number OR a.gifi_accno = g.account_number;
-$body$ LANGUAGE SQL;
-
 CREATE TABLE trial_balance__yearend_types (
     type text primary key
 );


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