From 23ece264e9729dd8316ca1116a5eed2b4732fd42 Mon Sep 17 00:00:00 2001 From: blake Date: Fri, 5 Jun 2015 16:34:20 -0500 Subject: [PATCH] LP1174498 Payment by billing type breakdown Added the upgrade script. This has been running in production for 6 months. Signed-off-by: blake --- ...XXXX.money.materialized_payment_by_billing_type | 35 ++++++++++++++++++++++ 1 file changed, 35 insertions(+) diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.money.materialized_payment_by_billing_type b/Open-ILS/src/sql/Pg/upgrade/XXXX.money.materialized_payment_by_billing_type index 9d81e08b59..63a077728a 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.money.materialized_payment_by_billing_type +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.money.materialized_payment_by_billing_type @@ -517,3 +517,38 @@ CREATE TRIGGER calculate_payment_by_btype_tgr COMMIT; + + +-- Now Populate the materialized table + +BEGIN; + +CREATE OR REPLACE FUNCTION tmp_populate_p_b_bt () RETURNS BOOL AS $$ +DECLARE + p RECORD; +BEGIN + FOR p IN + SELECT DISTINCT xact + FROM money.payment + WHERE NOT voided + AND amount > 0.0 + LOOP + + INSERT INTO money.materialized_payment_by_billing_type ( + xact, payment, billing, payment_ts, billing_ts, + payment_type, billing_type, amount, billing_ou, payment_ou + ) SELECT xact, payment, billing, payment_ts, billing_ts, + payment_type, billing_type, amount, billing_ou, payment_ou + FROM money.payment_by_billing_type( p.xact ); + + END LOOP; + + RETURN TRUE; +END; +$$ LANGUAGE PLPGSQL; + +SELECT tmp_populate_p_b_bt(); + +DROP FUNCTION tmp_populate_p_b_bt (); + +COMMIT; -- 2.11.0