From 822aab0d6923a31fa5daae1d2fcbc167c0cf316d Mon Sep 17 00:00:00 2001 From: Andrew McMillan Date: Wed, 10 Jun 2009 19:06:14 +1200 Subject: [PATCH] Fix bugs in year end. New functions for previous year sum and daily balance. --- dba/functions.sql | 156 ++++++++++++++++++++++++++++++++++++++++++++++++++++-- 1 file changed, 152 insertions(+), 4 deletions(-) diff --git a/dba/functions.sql b/dba/functions.sql index 091fb90..dfc8029 100644 --- a/dba/functions.sql +++ b/dba/functions.sql @@ -78,6 +78,51 @@ END; ' LANGUAGE plpgsql STABLE; + +CREATE or REPLACE FUNCTION previous_summary( TEXT, INT, NUMERIC, INT, INT ) RETURNS NUMERIC AS $$ +DECLARE + et ALIAS FOR $1; + ec ALIAS FOR $2; + ac ALIAS FOR $3; + mcode ALIAS FOR $4; + years ALIAS FOR $5; + result NUMERIC; + year_end NUMERIC; + mth INT4; +BEGIN + SELECT oldm.monthcode INTO mth FROM month oldm JOIN month newm ON newm.startdate = (oldm.startdate + (years::text||' years')::interval) + WHERE newm.monthcode = mcode; + IF NOT FOUND THEN + RAISE NOTICE 'No month record found (%).', mcode; + RETURN NULL; + END IF; + + SELECT sum(balance) INTO result FROM accountbalance ab + WHERE entitytype = et AND entitycode = ec AND accountcode = ac AND monthcode <= mth; + IF NOT FOUND OR result IS NULL THEN + result := 0::numeric; + END IF; + + SELECT accountcode INTO year_end FROM chartofaccount LEFT JOIN accountgroup ag USING(accountgroupcode) + WHERE accountcode = ac AND grouptype = 'P'; + IF NOT FOUND THEN + RETURN result; + END IF; + + SELECT sum(amount) INTO year_end FROM accttran + LEFT JOIN document USING (batchcode,documentcode) + WHERE entitytype = et AND entitycode = ec AND accountcode = ac AND monthcode = mth + AND documenttype = 'YEND'; + IF FOUND AND year_end IS NOT NULL THEN + result := result - year_end; + END IF; + + RETURN result; +END; +$$ LANGUAGE plpgsql STABLE; + + + CREATE or REPLACE FUNCTION year_end_balance( TEXT, INT, INT ) RETURNS SETOF accountbalance AS $$ DECLARE et ALIAS FOR $1; @@ -102,13 +147,13 @@ BEGIN RIGHT JOIN chartofaccount USING(accountcode) LEFT JOIN accountgroup USING(accountgroupcode) WHERE (month.financialyearcode = fy OR (grouptype != 'P' AND month.financialyearcode < fy) ) - AND entitytype = et AND entitycode = ec + AND accountbalance.entitytype = et AND accountbalance.entitycode = ec GROUP BY accountgroup.grouptype, accountcode LOOP SELECT sum(amount) INTO yend_reversals FROM accttran JOIN document USING (batchcode,documentcode) - WHERE entitytype = et AND entitycode = ec AND accountcode = abrec.accountcode - AND documenttype = 'YEND' AND monthcode = fy_last_month + WHERE accttran.entitytype = et AND accttran.entitycode = ec AND accttran.accountcode = abrec.accountcode + AND documenttype = 'YEND' AND accttran.monthcode = fy_last_month GROUP BY accountcode; result.balance := coalesce(abrec.balance,0) - coalesce(yend_reversals,0); result.budget := coalesce(abrec.budget,0); @@ -118,6 +163,33 @@ BEGIN RETURN NEXT result; END IF; END LOOP; + + -- That caught most of them, but for the ones that got zeroed out, and which have no budget + -- or revised budget, the accountbalance records get removed by a trigger, so we need a special + -- pass through looking at anything which had YEND transactions, and which now doesn't exist! + FOR abrec IN + SELECT accttran.accountcode, sum(amount) AS balance, 0::numeric AS budget, 0::numeric AS revisedbudget, ag.grouptype + FROM accttran LEFT JOIN document USING (batchcode,documentcode) + LEFT JOIN chartofaccount USING(accountcode) + LEFT JOIN accountgroup ag USING(accountgroupcode) + WHERE entitytype = et AND entitycode = ec + AND documenttype = 'YEND' AND monthcode = fy_last_month AND grouptype = 'P' + AND NOT EXISTS( SELECT 1 FROM month LEFT JOIN accountbalance USING (monthcode) + RIGHT JOIN chartofaccount USING(accountcode) + LEFT JOIN accountgroup USING(accountgroupcode) + WHERE month.financialyearcode = fy AND accountgroup.grouptype = 'P' AND accountbalance.entitytype = accttran.entitytype + AND accountbalance.entitycode = accttran.entitycode AND accountbalance.accountcode = accttran.accountcode ) + GROUP BY ag.grouptype, accountcode + LOOP + result.balance := 0 - coalesce(abrec.balance,0); + IF result.balance != 0 THEN + result.budget := 0; + result.revisedbudget := 0; + result.accountcode := abrec.accountcode; + RETURN NEXT result; + END IF; + END LOOP; + RETURN; END; $$ LANGUAGE plpgsql STABLE; @@ -233,4 +305,80 @@ END; CREATE or REPLACE FUNCTION get_office_setting( TEXT ) RETURNS TEXT AS $$ SELECT setvalue FROM office JOIN officesettings USING ( officecode ) WHERE setname = $1 AND thisoffice; -$$ LANGUAGE sql STRICT IMMUTABLE; \ No newline at end of file +$$ LANGUAGE sql STRICT IMMUTABLE; + + +CREATE or REPLACE FUNCTION date_series( DATE, INT4, INT4 ) RETURNS SETOF DATE AS $$ +DECLARE + base_date ALIAS FOR $1; + repeats ALIAS FOR $2; + period ALIAS FOR $3; + this_date DATE; + counter INT; + gap INTERVAL; +BEGIN + this_date := base_date; + counter := 0; + gap := (period::text || ' days')::interval; + + WHILE counter < repeats LOOP + RETURN NEXT this_date; + counter := counter + 1; + this_date := this_date + gap; + END LOOP; + RETURN; +END; +$$ LANGUAGE plpgsql STRICT IMMUTABLE; + + +DROP TYPE day_balance_type CASCADE; +CREATE TYPE day_balance_type AS ( + day DATE, + balance NUMERIC +); + + +CREATE or REPLACE FUNCTION daily_balance( CHAR, INT4, NUMERIC, DATE, INT4 ) RETURNS SETOF day_balance_type AS $$ +DECLARE + et ALIAS FOR $1; + ec ALIAS FOR $2; + ac ALIAS FOR $3; + base_date ALIAS FOR $4; + days ALIAS FOR $5; + result day_balance_type%ROWTYPE; + delta RECORD; + starting NUMERIC; + mth INT4; +BEGIN + SELECT monthcode INTO mth FROM month WHERE month.startdate <= base_date AND month.enddate >= base_date; + IF NOT FOUND THEN + RAISE NOTICE 'No month record covering start of period (%).', base_date; + RETURN; + END IF; + + SELECT sum(balance) INTO starting FROM accountbalance WHERE entitytype = et AND entitycode = ec AND accountcode = ac AND monthcode < mth; + IF NOT FOUND OR starting IS NULL THEN + starting := 0; + END IF; + result.day := base_date - '1 day'::interval; + result.balance := starting; + + SELECT sum(amount) INTO starting FROM accttran WHERE entitytype = et AND entitycode = ec AND accountcode = ac AND monthcode >= mth AND date < base_date; + IF FOUND THEN + result.balance := result.balance + COALESCE(starting,0); + END IF; + + FOR delta IN + SELECT date_series, sum(amount) FROM date_series( base_date, days, 1) + LEFT JOIN accttran ON (accttran.date = date_series AND entitytype = et AND entitycode = ec AND accountcode = ac AND monthcode >= mth) + GROUP BY date_series ORDER BY date_series + LOOP + result.day := delta.date_series; + result.balance := result.balance + COALESCE(delta.sum, 0); + RETURN NEXT result; + END LOOP; + + RETURN; + +END; +$$ LANGUAGE plpgsql STRICT IMMUTABLE; -- 2.11.4.GIT