From 574cab2d98b6ea6e58a57edd8824cc516c45ade3 Mon Sep 17 00:00:00 2001 From: blake Date: Thu, 19 Nov 2015 16:14:37 -0600 Subject: [PATCH] LP1319998_materialized_summary_billing_del_ADDS_to_balance_owed Switched the plus sign to a minus sign. This will keep the materialized view correct when deleting rows from money.billing. Signed-off-by: blake Signed-off-by: Kathy Lussier --- Open-ILS/src/sql/Pg/080.schema.money.sql | 2 +- ...alized_billing_summmary_delete_trigger.sql | 32 +++++++++++++++++++ 2 files changed, 33 insertions(+), 1 deletion(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.materialized_billing_summmary_delete_trigger.sql diff --git a/Open-ILS/src/sql/Pg/080.schema.money.sql b/Open-ILS/src/sql/Pg/080.schema.money.sql index 41c7c861c6..3bba2edf91 100644 --- a/Open-ILS/src/sql/Pg/080.schema.money.sql +++ b/Open-ILS/src/sql/Pg/080.schema.money.sql @@ -387,7 +387,7 @@ BEGIN IF NOT OLD.voided THEN UPDATE money.materialized_billable_xact_summary SET total_owed = total_owed - OLD.amount, - balance_owed = balance_owed + OLD.amount + balance_owed = balance_owed - OLD.amount WHERE id = OLD.xact; END IF; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.materialized_billing_summmary_delete_trigger.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.materialized_billing_summmary_delete_trigger.sql new file mode 100644 index 0000000000..3d1d6217f9 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.materialized_billing_summmary_delete_trigger.sql @@ -0,0 +1,32 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('XXXX'); + +CREATE OR REPLACE FUNCTION money.materialized_summary_billing_del () RETURNS TRIGGER AS $$ +DECLARE + prev_billing money.billing%ROWTYPE; + old_billing money.billing%ROWTYPE; +BEGIN + SELECT * INTO prev_billing FROM money.billing WHERE xact = OLD.xact AND NOT voided ORDER BY billing_ts DESC LIMIT 1 OFFSET 1; + SELECT * INTO old_billing FROM money.billing WHERE xact = OLD.xact AND NOT voided ORDER BY billing_ts DESC LIMIT 1; + + IF OLD.id = old_billing.id THEN + UPDATE money.materialized_billable_xact_summary + SET last_billing_ts = prev_billing.billing_ts, + last_billing_note = prev_billing.note, + last_billing_type = prev_billing.billing_type + WHERE id = OLD.xact; + END IF; + + IF NOT OLD.voided THEN + UPDATE money.materialized_billable_xact_summary + SET total_owed = total_owed - OLD.amount, + balance_owed = balance_owed - OLD.amount + WHERE id = OLD.xact; + END IF; + + RETURN OLD; +END; +$$ LANGUAGE PLPGSQL; + +COMMIT; -- 2.43.2