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 <blake@mobiusconsortium.org>
Signed-off-by: Kathy Lussier <klussier@masslnc.org>

diff --git a/Open-ILS/src/sql/Pg/080.schema.money.sql b/Open-ILS/src/sql/Pg/080.schema.money.sql
index 41c7c86..3bba2ed 100644 (file)
--- 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 (file)
index 0000000..3d1d621
--- /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;