From 4bd5e0b84af5c14cd9e4edbe631e193df4de4274 Mon Sep 17 00:00:00 2001 From: James Fournie Date: Mon, 19 Sep 2011 16:01:01 -0700 Subject: [PATCH] Addresses LP#800477 where some acq views calculate the totals incorrectly or in unexpected ways. Signed-off-by: James Fournie Signed-off-by: Bill Erickson --- Open-ILS/src/sql/Pg/200.schema.acq.sql | 34 +++++++++---------- .../sql/Pg/upgrade/xxxx.schema.acq_lp800477 | 34 +++++++++++++++++++ 2 files changed, 51 insertions(+), 17 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/xxxx.schema.acq_lp800477 diff --git a/Open-ILS/src/sql/Pg/200.schema.acq.sql b/Open-ILS/src/sql/Pg/200.schema.acq.sql index b566ff58f6..9276248fdf 100644 --- a/Open-ILS/src/sql/Pg/200.schema.acq.sql +++ b/Open-ILS/src/sql/Pg/200.schema.acq.sql @@ -2460,27 +2460,27 @@ CREATE OR REPLACE VIEW acq.fund_allocation_total AS GROUP BY 1; CREATE OR REPLACE VIEW acq.fund_debit_total AS - SELECT fund.id AS fund, - fund_debit.encumbrance AS encumbrance, - SUM( COALESCE( fund_debit.amount, 0 ) ) AS amount - FROM acq.fund AS fund - LEFT JOIN acq.fund_debit AS fund_debit - ON ( fund.id = fund_debit.fund ) - GROUP BY 1,2; + SELECT fund.id AS fund, + SUM(COALESCE(fund_debit.amount, 0::numeric)) AS amount + FROM acq.fund fund + LEFT JOIN acq.fund_debit fund_debit ON fund.id = fund_debit.fund + GROUP BY fund.id; CREATE OR REPLACE VIEW acq.fund_encumbrance_total AS - SELECT fund, - SUM(amount) AS amount - FROM acq.fund_debit_total - WHERE encumbrance IS TRUE - GROUP BY 1; + SELECT fund.id AS fund, + SUM(COALESCE(fund_debit.amount, 0::numeric)) AS amount + FROM acq.fund fund + LEFT JOIN acq.fund_debit fund_debit ON fund.id = fund_debit.fund + WHERE fund_debit.encumbrance + GROUP BY fund.id; CREATE OR REPLACE VIEW acq.fund_spent_total AS - SELECT fund, - SUM(amount) AS amount - FROM acq.fund_debit_total - WHERE encumbrance IS FALSE - GROUP BY 1; + SELECT fund.id AS fund, + SUM(COALESCE(fund_debit.amount, 0::numeric)) AS amount + FROM acq.fund fund + LEFT JOIN acq.fund_debit fund_debit ON fund.id = fund_debit.fund + WHERE NOT fund_debit.encumbrance + GROUP BY fund.id; CREATE OR REPLACE VIEW acq.fund_combined_balance AS SELECT c.fund, diff --git a/Open-ILS/src/sql/Pg/upgrade/xxxx.schema.acq_lp800477 b/Open-ILS/src/sql/Pg/upgrade/xxxx.schema.acq_lp800477 new file mode 100644 index 0000000000..dc21cd0373 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/xxxx.schema.acq_lp800477 @@ -0,0 +1,34 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version, install_date) VALUES ('XXXXXXX',now()); + +-- acq.fund_combined_balance is unchanged however we need to drop it to recreate the other views +-- we need to drop all our views because we change the number of columns +-- for example, debit_total does not need an encumberance column when we have a sepearate total for that + +DROP VIEW acq.fund_combined_balance; +DROP VIEW acq.fund_encumbrance_total; +DROP VIEW acq.fund_spent_total; +DROP VIEW acq.fund_debit_total; + +CREATE OR REPLACE VIEW acq.fund_debit_total AS + SELECT fund.id AS fund, sum(COALESCE(fund_debit.amount, 0::numeric)) AS amount + FROM acq.fund fund + LEFT JOIN acq.fund_debit fund_debit ON fund.id = fund_debit.fund + GROUP BY fund.id; + +CREATE OR REPLACE VIEW acq.fund_encumbrance_total AS +SELECT fund.id AS fund, sum(COALESCE(fund_debit.amount, 0::numeric)) AS amount FROM acq.fund fund + LEFT JOIN acq.fund_debit fund_debit ON fund.id = fund_debit.fund WHERE fund_debit.encumbrance GROUP BY fund.id; + +CREATE OR REPLACE VIEW acq.fund_spent_total AS +SELECT fund.id AS fund, sum(COALESCE(fund_debit.amount, 0::numeric)) AS amount FROM acq.fund fund + LEFT JOIN acq.fund_debit fund_debit ON fund.id = fund_debit.fund WHERE NOT fund_debit.encumbrance GROUP BY fund.id; + +CREATE OR REPLACE VIEW acq.fund_combined_balance AS + SELECT c.fund, c.amount - COALESCE(d.amount, 0.0) AS amount + FROM acq.fund_allocation_total c + LEFT JOIN acq.fund_debit_total d USING (fund); + + +COMMIT; -- 2.43.2