From f40f5ddaf1efb4e26167d7527f9e0726dce6d9e4 Mon Sep 17 00:00:00 2001 From: Jason Boyer Date: Thu, 30 Apr 2020 16:36:07 -0400 Subject: [PATCH] LP1174498: Use Staff home_ou for grocery payment_ou Odds are if the staff home_ou differs from the billing_location, odds are that's where the payment was taken. Also clean up debug comments. Signed-off-by: Jason Boyer --- ....money.materialized_payment_by_billing_type.sql | 50 +++------------------- 1 file changed, 6 insertions(+), 44 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 abdcb2ea51..40d30307ee 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,10 +15,6 @@ 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), --- Debug used_billings BIGINT[], --- Debug billing_l INT, CONSTRAINT x_p_b_once UNIQUE (xact,payment,billing) ); @@ -70,16 +66,8 @@ 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; + SELECT count(id) INTO billing_row_count FROM money.billing WHERE xact = p_xact; + -- Loop through the positive payments FOR current_payment IN SELECT * @@ -100,7 +88,7 @@ BEGIN JOIN actor.workstation ws ON (p.cash_drawer = ws.id) WHERE p.id = current_payment.id; ELSE - payment_ou = billing_ou; + SELECT home_ou INTO payment_ou FROM actor.usr WHERE id = (SELECT accepting_usr FROM money.bnm_payment WHERE id = current_payment.id); END IF; -- Were we looking at a billing from a previous step in the loop? @@ -114,10 +102,6 @@ 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; --- Debug current_result.used_billings = billing_id_used; --- Debug current_result.billing_l = billing_loop; IF billing_remainder >= payment_remainder THEN current_result.amount = payment_remainder; @@ -194,10 +178,6 @@ 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; --- Debug current_result.used_billings = billing_id_used; --- Debug current_result.billing_l = billing_loop; IF billing_remainder >= payment_remainder THEN current_result.amount = payment_remainder; @@ -253,10 +233,6 @@ 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; --- Debug current_result.used_billings = billing_id_used; --- Debug current_result.billing_l = billing_loop; IF billing_remainder >= payment_remainder THEN current_result.amount = payment_remainder; @@ -298,16 +274,8 @@ BEGIN 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; + -- figure out how many voided billing rows there are + SELECT count(id) INTO billing_row_count FROM money.billing WHERE xact = p_xact AND voided; -- Loop through the negative payments, these are refunds on voided billings FOR current_payment IN @@ -325,7 +293,7 @@ BEGIN JOIN actor.workstation ws ON (p.cash_drawer = ws.id) WHERE p.id = current_payment.id; ELSE - payment_ou = billing_ou; + SELECT home_ou INTO payment_ou FROM actor.usr WHERE id = (SELECT accepting_usr FROM money.bnm_payment WHERE id = current_payment.id); END IF; payment_remainder = -current_payment.amount; -- invert @@ -345,9 +313,6 @@ 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; --- Debug current_result.billing_l = billing_loop; IF billing_remainder >= payment_remainder THEN current_result.amount = payment_remainder; @@ -410,9 +375,6 @@ 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; --- Debug current_result.billing_l = billing_loop; IF billing_remainder >= payment_remainder THEN current_result.amount = payment_remainder; -- 2.11.0