[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
- Subject: SF.net SVN: ledger-smb:[3066] addons/1.3/enhanced_tb/trunk/sql/modules
- From: ..hidden..
- Date: Wed, 22 Sep 2010 20:26:06 +0000
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.