From 3461f7a3b557f9c89efd06b46a5790beb37afd4a Mon Sep 17 00:00:00 2001 From: blake Date: Wed, 24 Sep 2014 11:28:02 -0500 Subject: [PATCH] LP1174498 Payment by billing type breakdown Improved function loop tracking to prevent unique constraint issues I have tested it and this materialized table is populating the way I think it should Signed-off-by: blake --- Open-ILS/src/sql/Pg/080.schema.money.sql | 148 ++++++++++++++++++++++++++----- 1 file changed, 125 insertions(+), 23 deletions(-) diff --git a/Open-ILS/src/sql/Pg/080.schema.money.sql b/Open-ILS/src/sql/Pg/080.schema.money.sql index 16f1015a6d..79c8ed5dae 100644 --- a/Open-ILS/src/sql/Pg/080.schema.money.sql +++ b/Open-ILS/src/sql/Pg/080.schema.money.sql @@ -700,6 +700,8 @@ CREATE TABLE money.materialized_payment_by_billing_type ( billing_type TEXT, payment_ou INT, billing_ou INT, +-- Debug billing_leftover NUMERIC(8,2), +-- Debug payment_leftover NUMERIC(8,2), CONSTRAINT x_p_b_once UNIQUE (xact,payment,billing) ); @@ -721,7 +723,11 @@ DECLARE payment_offset INT := 0; billing_offset INT := 0; billing_ou INT := 0; - payment_ou INT := 0; + payment_ou INT := 0; + fast_forward BOOLEAN := FALSE; + maintain_billing_remainder BOOLEAN := FALSE; + billing_loop INT := -1; + billing_row_count INT := 0; BEGIN /* This algorithm is fairly straight-forward. We take a transaction id @@ -741,6 +747,16 @@ BEGIN SELECT circ_lib INTO billing_ou FROM action.circulation WHERE id = p_xact; END IF; + -- figure out how many billing rows there are + FOR current_billing IN + SELECT * + FROM money.billing + WHERE xact = p_xact + -- AND NOT voided + ORDER BY billing_ts + LOOP + billing_row_count = billing_row_count + 1; + END LOOP; -- Loop through the positive payments FOR current_payment IN SELECT * @@ -752,7 +768,9 @@ BEGIN LOOP payment_remainder = current_payment.amount; - + -- With every new payment row, we need to fast forward + -- the billing lines up to the last paid billing row + fast_forward := TRUE; IF current_payment.payment_type IN ( 'cash_payment', 'check_payment', 'credit_card_payment' ) THEN SELECT ws.owning_lib INTO payment_ou FROM money.bnm_desk_payment p @@ -763,8 +781,7 @@ BEGIN END IF; -- Were we looking at a billing from a previous step in the loop? - IF billing_remainder > 0.0 THEN - + IF billing_remainder > 0.0 THEN current_result.xact = p_xact; current_result.payment = current_payment.id; current_result.billing = current_billing.id; @@ -774,20 +791,33 @@ BEGIN current_result.billing_type = current_billing.billing_type; current_result.payment_ou = payment_ou; current_result.billing_ou = billing_ou; +-- Debug current_result.billing_leftover = billing_remainder; +-- Debug current_result.payment_leftover = payment_remainder; IF billing_remainder >= payment_remainder THEN current_result.amount = payment_remainder; billing_remainder = billing_remainder - payment_remainder; payment_remainder = 0.0; payment_offset = payment_offset + 1; + -- If it is equal then we need to close up the billing line and move to the next + -- This prevents 0 amounts applied to billing lines + IF billing_remainder = payment_remainder THEN + billing_remainder = 0.0; + billing_offset = billing_offset + 1; + ELSE + maintain_billing_remainder := TRUE; + END IF; + ELSE current_result.amount = billing_remainder; payment_remainder = payment_remainder - billing_remainder; billing_remainder = 0.0; + billing_offset = billing_offset + 1; END IF; 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; END IF; CONTINUE WHEN payment_remainder = 0.0; @@ -798,12 +828,26 @@ BEGIN SELECT * FROM money.billing WHERE xact = p_xact - -- AND NOT voided - ORDER BY billing_ts + -- AND NOT voided + -- Gotta put the voided billing rows at the bottom (last) + ORDER BY voided,billing_ts LOOP - - billing_remainder = current_billing.amount; - + -- Fast forward through the rows until we get to the billing row + -- where we left off + IF fast_forward THEN + billing_loop = billing_loop + 1; -- first iteration billing_loop=0, it starts at -1 + CONTINUE WHEN billing_loop <> billing_offset; + END IF; + + -- Keep the old remainder if we were in the middle of a billing row + IF NOT maintain_billing_remainder THEN + billing_remainder = current_billing.amount; + END IF; + + maintain_billing_remainder := FALSE; + billing_loop := -1; + fast_forward := FALSE; + current_result.xact = p_xact; current_result.payment = current_payment.id; current_result.billing = current_billing.id; @@ -813,6 +857,8 @@ BEGIN current_result.billing_type = current_billing.billing_type; current_result.payment_ou = payment_ou; current_result.billing_ou = billing_ou; +-- Debug current_result.billing_leftover = billing_remainder; +-- Debug current_result.payment_leftover = payment_remainder; IF billing_remainder >= payment_remainder THEN current_result.amount = payment_remainder; @@ -820,6 +866,12 @@ BEGIN IF NOT current_billing.voided THEN payment_remainder = 0.0; END IF; + -- If it is equal then we need to close up the billing line and move to the next + -- This prevents 0 amounts applied to billing lines + IF billing_remainder = payment_remainder THEN + billing_remainder = 0.0; + billing_offset = billing_offset + 1; + END IF; ELSE current_result.amount = billing_remainder; IF NOT current_billing.voided THEN @@ -832,10 +884,11 @@ BEGIN RETURN NEXT current_result; EXIT WHEN payment_remainder = 0.0; - END LOOP; payment_offset = payment_offset + 1; + -- Done paying the billing rows when we run out of rows to pay (out of bounds) + EXIT WHEN billing_offset = billing_row_count+1; END LOOP; @@ -843,7 +896,20 @@ BEGIN billing_remainder := 0.0; payment_offset := 0; billing_offset := 0; - + billing_row_count := 0; + billing_loop := -1; + +-- figure out how many voided billing rows there are + FOR current_billing IN + SELECT * + FROM money.billing + WHERE xact = p_xact + AND voided + ORDER BY billing_ts + LOOP + billing_row_count = billing_row_count + 1; + END LOOP; + -- Loop through the negative payments, these are refunds on voided billings FOR current_payment IN SELECT * @@ -864,7 +930,10 @@ BEGIN END IF; payment_remainder = -current_payment.amount; -- invert - + -- With every new payment row, we need to fast forward + -- the billing lines up to the last paid billing row + fast_forward := TRUE; + -- Were we looking at a billing from a previous step in the loop? IF billing_remainder > 0.0 THEN @@ -877,21 +946,33 @@ BEGIN current_result.billing_type = current_billing.billing_type; current_result.payment_ou = payment_ou; current_result.billing_ou = billing_ou; +-- Debug current_result.billing_leftover = billing_remainder; +-- Debug current_result.payment_leftover = payment_remainder; IF billing_remainder >= payment_remainder THEN - current_result.amount = payment_remainder; - billing_remainder = billing_remainder - payment_remainder; - payment_remainder = 0.0; - payment_offset = payment_offset + 1; + current_result.amount = payment_remainder; + billing_remainder = billing_remainder - payment_remainder; + payment_remainder = 0.0; + payment_offset = payment_offset + 1; + -- If it is equal then we need to close up the billing line and move to the next + -- This prevents 0 amounts applied to billing lines + IF billing_remainder = payment_remainder THEN + billing_remainder = 0.0; + billing_offset = billing_offset + 1; + ELSE + maintain_billing_remainder := TRUE; + END IF; ELSE - current_result.amount = billing_remainder; + current_result.amount = billing_remainder; payment_remainder = payment_remainder - billing_remainder; billing_remainder = 0.0; + billing_offset = billing_offset + 1; END IF; current_result.amount = -current_result.amount; 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; END IF; CONTINUE WHEN payment_remainder = 0.0; @@ -905,8 +986,21 @@ BEGIN AND voided ORDER BY billing_ts LOOP - - billing_remainder = current_billing.amount; + -- Fast forward through the rows until we get to the billing row + -- where we left off + IF fast_forward THEN + billing_loop = billing_loop + 1; -- first iteration billing_loop=0, it starts at -1 + CONTINUE WHEN billing_loop <> billing_offset; + END IF; + + -- Keep the old remainder if we were in the middle of a billing row + IF NOT maintain_billing_remainder THEN + billing_remainder = current_billing.amount; + END IF; + + maintain_billing_remainder := FALSE; + billing_loop := -1; + fast_forward := FALSE; current_result.xact = p_xact; current_result.payment = current_payment.id; @@ -917,11 +1011,19 @@ BEGIN current_result.billing_type = current_billing.billing_type; current_result.payment_ou = payment_ou; current_result.billing_ou = billing_ou; +-- Debug current_result.billing_leftover = billing_remainder; +-- Debug current_result.payment_leftover = payment_remainder; IF billing_remainder >= payment_remainder THEN current_result.amount = payment_remainder; billing_remainder = billing_remainder - payment_remainder; payment_remainder = 0.0; + -- If it is equal then we need to close up the billing line and move to the next + -- This prevents 0 amounts applied to billing lines + IF billing_remainder = payment_remainder THEN + billing_remainder = 0.0; + billing_offset = billing_offset + 1; + END IF; ELSE current_result.amount = billing_remainder; payment_remainder = payment_remainder - billing_remainder; @@ -937,6 +1039,8 @@ BEGIN END LOOP; payment_offset = payment_offset + 1; + -- Done paying the billing rows when we run out of rows to pay (out of bounds) + EXIT WHEN billing_offset = billing_row_count+1; END LOOP; @@ -1096,7 +1200,5 @@ CREATE TRIGGER calculate_payment_by_btype_tgr AFTER INSERT OR UPDATE OR DELETE ON money.credit_card_payment FOR EACH ROW EXECUTE PROCEDURE money.payment_by_billing_type_trigger(); - - COMMIT; -- 2.11.0