[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[4893] addons/1.3/enhanced_tb/trunk/sql/modules/ trial_balance.sql
- Subject: SF.net SVN: ledger-smb:[4893] addons/1.3/enhanced_tb/trunk/sql/modules/ trial_balance.sql
- From: ..hidden..
- Date: Wed, 13 Jun 2012 11:12:23 +0000
Revision: 4893
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=4893&view=rev
Author: einhverfr
Date: 2012-06-13 11:12:23 +0000 (Wed, 13 Jun 2012)
Log Message:
-----------
Enhanced trial balance now seems to work with a small issue that accounts which have been inactive for long enough fall out of the unbounded report ignoring all yearends. I believe this is actually a bug in the checkpoint logic however
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-12 01:27:44 UTC (rev 4892)
+++ addons/1.3/enhanced_tb/trunk/sql/modules/trial_balance.sql 2012-06-13 11:12:23 UTC (rev 4893)
@@ -21,229 +21,117 @@
CREATE OR REPLACE FUNCTION trial_balance__generate
-(i_date_from DATE, i_date_to DATE, in_heading INT, in_accounts INT[],
- in_ignore_yearend TEXT, in_department INT, in_precision int)
+(in_date_from DATE, in_date_to DATE, in_heading INT, in_accounts INT[],
+ in_ignore_yearend TEXT, in_department INT)
returns setof tb_row AS
$$
DECLARE
out_row tb_row;
- adj_row RECORD;
- cpa_date DATE;
- cpb_date DATE;
- date_to DATE;
- date_from DATE;
- yearend_trans INT;
- yearends INT[];
- include_trans INT;
- precision int;
+ t_roll_forward date;
+ t_cp account_checkpoint;
+ ignore_trans int;
+ t_start_date date;
+ t_end_date date;
BEGIN
- precision := coalesce(in_precision, 1000);
- -- don't bother with checking from/to dates for NULL below.
- IF i_date_from IS NULL THEN
- SELECT min(transdate) - '1 day'::interval INTO date_from from acc_trans;
+
+ IF in_date_from IS NULL AND in_ignore_yearend = 'none' THEN
+ SELECT max(end_date) INTO t_roll_forward
+ FROM account_checkpoint;
+ ELSIF in_date_from IS NULL AND in_ignore_yearend = 'last' THEN
+ SELECT max(end_date) INTO t_roll_forward
+ FROM account_checkpoint
+ WHERE end_date < (select max(gl.transdate)
+ FROM gl JOIN yearend y ON y.trans_id = gl.id
+ WHERE y.transdate < coalesce(in_date_to, gl.transdate)
+ );
+ ELSIF in_date_from IS NULL THEN
+ SELECT min(transdate) INTO t_roll_forward
+ FROM (select min(transdate) as transdate from ar
+ union
+ select min(transdate) from ap
+ union
+ select min(transdate) from gl) gl;
+
ELSE
- date_from = i_date_from;
- END IF;
-
- IF i_date_to IS NULL THEN
- SELECT max(transdate) + '1 day'::interval INTO date_to from acc_trans;
- ELSE
- date_to = i_date_to;
+ SELECT max(end_date) INTO t_roll_forward
+ FROM account_checkpoint
+ WHERE end_date < in_date_from;
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
- ORDER by transdate DESC LIMIT 1;
- yearends = yearends || include_trans;
+
+ IF t_roll_forward IS NULL THEN
+ SELECT min(transdate) INTO t_roll_forward FROM acc_trans;
END IF;
-
- IF in_ignore_yearend = 'all' THEN
- FOR yearend_trans IN SELECT trans_id FROM yearend
- WHERE transdate <= date_to
- LOOP
- yearends = yearends || yearend_trans;
- END LOOP;
- END IF;
-
- -- setup checkpoint stuff since can't do this in the from clause so well
- -- the latest checkpoint before the start of the balance period
-
- IF i_date_from IS NOT NULL THEN
- SELECT end_date INTO cpa_date
- FROM account_checkpoint
- WHERE end_date < date_from
- ORDER BY end_date DESC LIMIT 1;
- ELSE
- cpa_date = NULL;
+ IF in_ignore_yearend = 'last' THEN
+ SELECT trans_id INTO ignore_trans FROM yearend
+ ORDER BY transdate DESC LIMIT 1;
END IF;
-
- -- the latest checkpoint before the end of the balance period
- SELECT end_date INTO cpb_date
- FROM account_checkpoint
- WHERE end_date >= date_from AND end_date <= date_to
- ORDER BY end_date DESC LIMIT 1;
-
-
- FOR out_row IN
- -- main trial balance query
- SELECT a.id, a.accno, a.description, a.gifi_accno,
- (COALESCE(SUM(CASE WHEN ac.transdate < date_from
- THEN COALESCE(round(ac.amount, precision), 0)
- ELSE 0 END), 0) +
- COALESCE(round(cpa.amount, precision), 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.
- -- Most conditions are for the case of either start or the
- -- checkpoint missing.
- COALESCE(abs(round(cpb.debits, precision)), 0) +
- COALESCE(SUM(CASE WHEN COALESCE(ac.amount, 1) > 0 THEN 0
- ELSE
- CASE WHEN (cpb.end_date IS NOT NULL
- AND ac.transdate > cpb.end_date) OR
- (cpb.end_date IS NULL
- AND ac.transdate >= date_from)
- THEN round(ac.amount, precision) * -1
- WHEN (cpb.end_date IS NOT NULL AND
- ac.transdate < date_from)
- THEN round(ac.amount, precision) * -1
- ELSE 0
- END
- END), 0) -
- -- we should only substract starting segment if we have added
- -- the value of ending one first.
- 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(round(cpb.credits, precision), 0) +
- COALESCE(SUM (CASE WHEN COALESCE(ac.amount, -1) < 0 THEN 0
- ELSE
- CASE WHEN (cpb.end_date IS NOT NULL
- AND ac.transdate > cpb.end_date) OR
- (cpb.end_date IS NULL
- AND ac.transdate >= date_from)
- THEN round(ac.amount, precision)
- WHEN (cpb.end_date IS NOT NULL AND
- ac.transdate < date_from)
- THEN -ac.amount
- ELSE 0
- END
- END), 0) -
- 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)
- THEN
- COALESCE(round(ac.amount, precision), 0) ELSE 0 END), 0) +
- COALESCE(round(cpb.amount, precision), 0))
- * CASE WHEN a.contra
- 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
- LEFT JOIN
- (SELECT ac.chart_id, ac.trans_id, ac.amount, ac.transdate
- FROM acc_trans ac
- JOIN -- Moving this join into the inline view so as to avoid
- -- inner vs left join problems
- (SELECT id, approved FROM ar
- UNION
- SELECT id, approved FROM gl
- UNION
- SELECT id, approved FROM ap) gl
- ON (ac.trans_id = gl.id AND gl.approved IS TRUE)
- WHERE ac.approved IS TRUE AND transdate <= date_to AND
- ((cpa_date IS NULL AND cpb_date IS NOT NULL AND
- (ac.transdate < date_from OR ac.transdate > cpb_date)) OR
- (cpa_date IS NOT NULL AND cpb_date IS NOT NULL AND
- ((ac.transdate > cpa_date AND ac.transdate < date_from)
- OR ac.transdate > cpb_date)) OR
- (cpa_date IS NULL AND cpb_date IS NULL) OR
- (cpa_date IS NOT NULL AND cpb_date IS NULL AND
- ac.transdate > cpa_date))) ac ON (ac.chart_id = a.id)
- LEFT JOIN (SELECT account_id, amount, end_date, debits, credits
- FROM account_checkpoint
- WHERE end_date = cpa_date) cpa
- ON (cpa.account_id = a.id)
- LEFT JOIN (SELECT account_id, amount, end_date, debits, credits
- FROM account_checkpoint
- WHERE end_date = cpb_date) cpb
- ON (cpb.account_id = a.id)
- WHERE ((a.heading = in_heading) OR
- (a.id = any(in_accounts)) OR
- ((in_accounts IS NULL or in_accounts = '{}')
- AND in_heading IS NULL))
- 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
- ORDER BY a.accno
- LOOP
- -- if ignore_yearends is none - we are done. Otherwise we have to
- -- substract the value of yearend transactions for each account.
- -- We can do it in the main query, cause some of these transactions
- -- are never visited there, since their amounts are included in the
- -- checkpoints.
- IF in_ignore_yearend != 'none' THEN
- SELECT
- SUM(CASE WHEN ac.transdate < date_from
- THEN ac.amount ELSE 0 END)
- * CASE WHEN a.contra THEN -1 ELSE 1 END
- * CASE WHEN a.category IN ('A', 'E') THEN -1 ELSE 1 END
- AS start,
- SUM(CASE WHEN ac.transdate >= date_from AND ac.amount < 0
- 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(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 =
- out_row.starting_balance - adj_row.start;
- out_row.debits =
- out_row.debits - adj_row.debits;
- out_row.credits =
- out_row.credits - adj_row.credits;
- out_row.ending_balance =
- out_row.ending_balance - adj_row.end;
- END IF;
- END IF;
- RETURN NEXT out_row;
- END LOOP;
- RETURN;
-END
+
+ RETURN QUERY
+ 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
+ * (coalesce(cp.amount, 0)
+ + sum(CASE WHEN ac.transdate <= coalesce(in_date_from,
+ t_roll_forward)
+ THEN ac.amount ELSE 0 END)) end,
+ sum(CASE WHEN ac.transdate BETWEEN coalesce(in_date_from,
+ t_roll_forward)
+ AND coalesce(in_date_to,
+ ac.transdate)
+ AND ac.amount < 0 THEN ac.amount * -1 ELSE 0 END) -
+ case when in_date_from is null then coalesce(cp.debits, 0) else 0 end,
+ sum(CASE WHEN ac.transdate BETWEEN coalesce(in_date_from,
+ t_roll_forward)
+ AND coalesce(in_date_to,
+ ac.transdate)
+ AND ac.amount > 0 THEN ac.amount ELSE 0 END) +
+ case when in_date_from is null then coalesce(cp.credits, 0) else 0 end,
+ 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 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)
+ 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;
+END;
$$ language plpgsql;
CREATE TABLE trial_balance__yearend_types (
type text primary key
);
-
INSERT INTO trial_balance__yearend_types (type) VALUES ('none');
INSERT INTO trial_balance__yearend_types (type) VALUES ('all');
INSERT INTO trial_balance__yearend_types (type) VALUES ('last');
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.