From 2bd65699e909ccce25c5294721ab82d0c9186108 Mon Sep 17 00:00:00 2001 From: Jason Boyer Date: Wed, 5 Aug 2020 10:19:43 -0400 Subject: [PATCH] And put the debugging back. Signed-off-by: Jason Boyer --- ....money.materialized_payment_by_billing_type.sql | 73 +++++++++++++++++++--- 1 file changed, 63 insertions(+), 10 deletions(-) diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.money.materialized_payment_by_billing_type.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.money.materialized_payment_by_billing_type.sql index 40d30307ee..ca167a2dbe 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.money.materialized_payment_by_billing_type.sql +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.money.materialized_payment_by_billing_type.sql @@ -15,6 +15,10 @@ CREATE TABLE money.materialized_payment_by_billing_type ( billing_type TEXT, payment_ou INT, billing_ou INT, + billing_leftover NUMERIC(8,2), -- debugging + payment_leftover NUMERIC(8,2), -- debugging + used_billings BIGINT[], -- debugging + billing_l INT, -- debugging CONSTRAINT x_p_b_once UNIQUE (xact,payment,billing) ); @@ -102,6 +106,10 @@ BEGIN current_result.billing_type = current_billing.billing_type; current_result.payment_ou = payment_ou; current_result.billing_ou = billing_ou; + current_result.billing_leftover = billing_remainder; -- debugging + current_result.payment_leftover = payment_remainder; -- debugging + current_result.used_billings = billing_id_used; -- debugging + current_result.billing_l = billing_loop; -- debugging IF billing_remainder >= payment_remainder THEN current_result.amount = payment_remainder; @@ -127,6 +135,10 @@ BEGIN continuing_payment := TRUE; END IF; + current_result.billing_leftover = billing_remainder; -- debugging + current_result.payment_leftover = payment_remainder; -- debugging + current_result.used_billings = billing_id_used; -- debugging + current_result.billing_l = billing_loop; -- debugging RETURN NEXT current_result; -- Done paying the billing rows when we run out of rows to pay (out of bounds) EXIT WHEN array_length(billing_id_used, 1) = billing_row_count; @@ -178,6 +190,10 @@ BEGIN current_result.billing_type = current_billing.billing_type; current_result.payment_ou = payment_ou; current_result.billing_ou = billing_ou; + current_result.billing_leftover = billing_remainder; -- debugging + current_result.payment_leftover = payment_remainder; -- debugging + current_result.used_billings = billing_id_used; -- debugging + current_result.billing_l = billing_loop; -- debugging IF billing_remainder >= payment_remainder THEN current_result.amount = payment_remainder; @@ -203,6 +219,10 @@ BEGIN billing_offset = billing_offset + 1; billing_id_used = array_append( billing_id_used, current_billing_id ); END IF; + current_result.billing_leftover = billing_remainder; -- debugging + current_result.payment_leftover = payment_remainder; -- debugging + current_result.used_billings = billing_id_used; -- debugging + current_result.billing_l = billing_loop; -- debugging RETURN NEXT current_result; IF continuing_payment_last_row THEN @@ -233,6 +253,10 @@ BEGIN current_result.billing_type = current_billing.billing_type; current_result.payment_ou = payment_ou; current_result.billing_ou = billing_ou; + current_result.billing_leftover = billing_remainder; -- debugging + current_result.payment_leftover = payment_remainder; -- debugging + current_result.used_billings = billing_id_used; -- debugging + current_result.billing_l = billing_loop; -- debugging IF billing_remainder >= payment_remainder THEN current_result.amount = payment_remainder; @@ -253,6 +277,10 @@ BEGIN billing_id_used = array_append( billing_id_used, current_billing_id ); END IF; + current_result.billing_leftover = billing_remainder; -- debugging + current_result.payment_leftover = payment_remainder; -- debugging + current_result.used_billings = billing_id_used; -- debugging + current_result.billing_l = billing_loop; -- debugging RETURN NEXT current_result; EXIT WHEN payment_remainder = 0.0; END LOOP; @@ -313,6 +341,9 @@ BEGIN current_result.billing_type = current_billing.billing_type; current_result.payment_ou = payment_ou; current_result.billing_ou = billing_ou; + current_result.billing_leftover = billing_remainder; -- debugging + current_result.payment_leftover = payment_remainder; -- debugging + current_result.billing_l = billing_loop; -- debugging IF billing_remainder >= payment_remainder THEN current_result.amount = payment_remainder; @@ -335,6 +366,11 @@ BEGIN END IF; current_result.amount = -current_result.amount; + current_result.billing_leftover = billing_remainder; -- debugging + current_result.payment_leftover = payment_remainder; -- debugging + current_result.used_billings = billing_id_used; -- debugging + current_result.billing_l = billing_loop; -- debugging + RETURN NEXT current_result; -- Done paying the billing rows when we run out of rows to pay (out of bounds) EXIT WHEN billing_offset = billing_row_count + 1; @@ -375,6 +411,9 @@ BEGIN current_result.billing_type = current_billing.billing_type; current_result.payment_ou = payment_ou; current_result.billing_ou = billing_ou; + current_result.billing_leftover = billing_remainder; -- debugging + current_result.payment_leftover = payment_remainder; -- debugging + current_result.billing_l = billing_loop; -- debugging IF billing_remainder >= payment_remainder THEN current_result.amount = payment_remainder; @@ -394,6 +433,10 @@ BEGIN END IF; current_result.amount = -current_result.amount; + current_result.billing_leftover = billing_remainder; -- debugging + current_result.payment_leftover = payment_remainder; -- debugging + current_result.used_billings = billing_id_used; -- debugging + current_result.billing_l = billing_loop; -- debugging RETURN NEXT current_result; EXIT WHEN payment_remainder = 0.0; @@ -472,9 +515,11 @@ BEGIN INSERT INTO money.materialized_payment_by_billing_type ( xact, payment, billing, payment_ts, billing_ts, - payment_type, billing_type, amount, billing_ou, payment_ou + payment_type, billing_type, amount, billing_ou, payment_ou, +billing_leftover,payment_leftover,used_billings,billing_l -- debugging ) SELECT xact, payment, billing, payment_ts, billing_ts, - payment_type, billing_type, amount, billing_ou, payment_ou + payment_type, billing_type, amount, billing_ou, payment_ou, +billing_leftover,payment_leftover,used_billings,billing_l -- debugging FROM money.payment_by_billing_type( NEW.xact ); ELSIF TG_OP = 'UPDATE' THEN @@ -483,17 +528,21 @@ BEGIN INSERT INTO money.materialized_payment_by_billing_type ( xact, payment, billing, payment_ts, billing_ts, - payment_type, billing_type, amount, billing_ou, payment_ou + payment_type, billing_type, amount, billing_ou, payment_ou, +billing_leftover,payment_leftover,used_billings,billing_l -- debugging ) SELECT xact, payment, billing, payment_ts, billing_ts, - payment_type, billing_type, amount, billing_ou, payment_ou + payment_type, billing_type, amount, billing_ou, payment_ou, +billing_leftover,payment_leftover,used_billings,billing_l -- debugging FROM money.payment_by_billing_type( NEW.xact ); IF NEW.xact <> OLD.xact THEN INSERT INTO money.materialized_payment_by_billing_type ( xact, payment, billing, payment_ts, billing_ts, - payment_type, billing_type, amount, billing_ou, payment_ou + payment_type, billing_type, amount, billing_ou, payment_ou, +billing_leftover,payment_leftover,used_billings,billing_l -- debugging ) SELECT xact, payment, billing, payment_ts, billing_ts, - payment_type, billing_type, amount, billing_ou, payment_ou + payment_type, billing_type, amount, billing_ou, payment_ou, +billing_leftover,payment_leftover,used_billings,billing_l -- debugging FROM money.payment_by_billing_type( OLD.xact ); END IF; @@ -503,9 +552,11 @@ BEGIN INSERT INTO money.materialized_payment_by_billing_type ( xact, payment, billing, payment_ts, billing_ts, - payment_type, billing_type, amount, billing_ou, payment_ou + payment_type, billing_type, amount, billing_ou, payment_ou, +billing_leftover,payment_leftover,used_billings,billing_l -- debugging ) SELECT xact, payment, billing, payment_ts, billing_ts, - payment_type, billing_type, amount, billing_ou, payment_ou + payment_type, billing_type, amount, billing_ou, payment_ou, +billing_leftover,payment_leftover,used_billings,billing_l -- debugging FROM money.payment_by_billing_type( OLD.xact ); RETURN OLD; @@ -581,9 +632,11 @@ BEGIN INSERT INTO money.materialized_payment_by_billing_type ( xact, payment, billing, payment_ts, billing_ts, - payment_type, billing_type, amount, billing_ou, payment_ou + payment_type, billing_type, amount, billing_ou, payment_ou, + billing_leftover,payment_leftover,used_billings,billing_l -- debugging ) SELECT xact, payment, billing, payment_ts, billing_ts, - payment_type, billing_type, amount, billing_ou, payment_ou + payment_type, billing_type, amount, billing_ou, payment_ou, + billing_leftover,payment_leftover,used_billings,billing_l -- debugging FROM money.payment_by_billing_type( p.xact ); END LOOP; -- 2.11.0