From 4843602e7efa761ff8003d4d41b17a8b186b8315 Mon Sep 17 00:00:00 2001 From: blake Date: Wed, 24 Sep 2014 11:23:01 -0500 Subject: [PATCH] LP1174498 Payment by billing type breakdown This is the code submitted via attachment to LP by Mike Rylander (mrlander) and by Jeff Godin (jgodin) I felt I needed to get it into git so that I could commit my changes Conflicts: Open-ILS/examples/fm_IDL.xml --- Open-ILS/examples/fm_IDL.xml | 23 ++ Open-ILS/src/sql/Pg/080.schema.money.sql | 409 +++++++++++++++++++++++++++++++ 2 files changed, 432 insertions(+) diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index f43851ad35..b8b3651f52 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -2778,6 +2778,29 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + + + + + + + + + + + + + + + + + + + + + + + diff --git a/Open-ILS/src/sql/Pg/080.schema.money.sql b/Open-ILS/src/sql/Pg/080.schema.money.sql index b5f3b27241..16f1015a6d 100644 --- a/Open-ILS/src/sql/Pg/080.schema.money.sql +++ b/Open-ILS/src/sql/Pg/080.schema.money.sql @@ -688,6 +688,415 @@ CREATE OR REPLACE VIEW money.cashdrawer_payment_view AS LEFT JOIN money.bnm_desk_payment p ON (ws.id = p.cash_drawer) LEFT JOIN money.payment_view t ON (p.id = t.id); +CREATE TABLE money.materialized_payment_by_billing_type ( + id BIGSERIAL PRIMARY KEY, + xact BIGINT NOT NULL, + payment BIGINT NOT NULL, + billing BIGINT NOT NULL, + payment_ts TIMESTAMPTZ NOT NULL, + billing_ts TIMESTAMPTZ NOT NULL, + amount NUMERIC(8,2) NOT NULL, + payment_type TEXT, + billing_type TEXT, + payment_ou INT, + billing_ou INT, + CONSTRAINT x_p_b_once UNIQUE (xact,payment,billing) +); + +CREATE INDEX p_by_b_payment_ts_idx + ON money.materialized_payment_by_billing_type (payment_ts); + + + +CREATE OR REPLACE FUNCTION money.payment_by_billing_type ( + p_xact BIGINT +) RETURNS SETOF money.materialized_payment_by_billing_type AS $$ + +DECLARE + current_result money.materialized_payment_by_billing_type%ROWTYPE; + current_payment money.payment_view%ROWTYPE; + current_billing money.billing%ROWTYPE; + payment_remainder NUMERIC(8,2) := 0.0; + billing_remainder NUMERIC(8,2) := 0.0; + payment_offset INT := 0; + billing_offset INT := 0; + billing_ou INT := 0; + payment_ou INT := 0; +BEGIN + + /* This algorithm is fairly straight-forward. We take a transaction id + * and fetch its payments in chronological order. We apply the payment + * amount, or a portion thereof, to each billing on the transaction, + * also in chronological order, until we run out of money from that + * payment. For each billing we encounter while we have money left from + * a payment we emmit a row of output containing the information about + * the billing and payment, and the amount of the current payment that + * was applied to the current billing. + */ + + -- First we'll go get the xact location. That will be the fallback location. + + SELECT billing_location INTO billing_ou FROM money.grocery WHERE id = p_xact; + IF NOT FOUND THEN + SELECT circ_lib INTO billing_ou FROM action.circulation WHERE id = p_xact; + END IF; + + -- Loop through the positive payments + FOR current_payment IN + SELECT * + FROM money.payment_view + WHERE xact = p_xact + AND NOT voided + AND amount > 0.0 + ORDER BY payment_ts + LOOP + + payment_remainder = current_payment.amount; + + 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 + JOIN actor.workstation ws ON (p.cash_drawer = ws.id) + WHERE p.id = current_payment.id; + ELSE + payment_ou = billing_ou; + END IF; + + -- Were we looking at a billing from a previous step in the loop? + IF billing_remainder > 0.0 THEN + + current_result.xact = p_xact; + current_result.payment = current_payment.id; + current_result.billing = current_billing.id; + current_result.payment_ts = current_payment.payment_ts; + current_result.billing_ts = current_billing.billing_ts; + current_result.payment_type = current_payment.payment_type; + current_result.billing_type = current_billing.billing_type; + current_result.payment_ou = payment_ou; + current_result.billing_ou = billing_ou; + + 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; + ELSE + current_result.amount = billing_remainder; + payment_remainder = payment_remainder - billing_remainder; + billing_remainder = 0.0; + END IF; + + RETURN NEXT current_result; + + END IF; + + CONTINUE WHEN payment_remainder = 0.0; + + -- next billing, please + + FOR current_billing IN + SELECT * + FROM money.billing + WHERE xact = p_xact + -- AND NOT voided + ORDER BY billing_ts + LOOP + + billing_remainder = current_billing.amount; + + current_result.xact = p_xact; + current_result.payment = current_payment.id; + current_result.billing = current_billing.id; + current_result.payment_ts = current_payment.payment_ts; + current_result.billing_ts = current_billing.billing_ts; + current_result.payment_type = current_payment.payment_type; + current_result.billing_type = current_billing.billing_type; + current_result.payment_ou = payment_ou; + current_result.billing_ou = billing_ou; + + IF billing_remainder >= payment_remainder THEN + current_result.amount = payment_remainder; + billing_remainder = billing_remainder - payment_remainder; + IF NOT current_billing.voided THEN + payment_remainder = 0.0; + END IF; + ELSE + current_result.amount = billing_remainder; + IF NOT current_billing.voided THEN + payment_remainder = payment_remainder - billing_remainder; + END IF; + billing_remainder = 0.0; + billing_offset = billing_offset + 1; + END IF; + + RETURN NEXT current_result; + + EXIT WHEN payment_remainder = 0.0; + + END LOOP; + + payment_offset = payment_offset + 1; + + END LOOP; + + payment_remainder := 0.0; + billing_remainder := 0.0; + payment_offset := 0; + billing_offset := 0; + + -- Loop through the negative payments, these are refunds on voided billings + FOR current_payment IN + SELECT * + FROM money.payment_view + WHERE xact = p_xact + AND NOT voided + AND amount < 0.0 + ORDER BY payment_ts + LOOP + + 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 + JOIN actor.workstation ws ON (p.cash_drawer = ws.id) + WHERE p.id = current_payment.id; + ELSE + payment_ou = billing_ou; + END IF; + + payment_remainder = -current_payment.amount; -- invert + + -- Were we looking at a billing from a previous step in the loop? + IF billing_remainder > 0.0 THEN + + current_result.xact = p_xact; + current_result.payment = current_payment.id; + current_result.billing = current_billing.id; + current_result.payment_ts = current_payment.payment_ts; + current_result.billing_ts = current_billing.billing_ts; + current_result.payment_type = 'REFUND'; + current_result.billing_type = current_billing.billing_type; + current_result.payment_ou = payment_ou; + current_result.billing_ou = billing_ou; + + 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; + ELSE + current_result.amount = billing_remainder; + payment_remainder = payment_remainder - billing_remainder; + billing_remainder = 0.0; + END IF; + + current_result.amount = -current_result.amount; + RETURN NEXT current_result; + + END IF; + + CONTINUE WHEN payment_remainder = 0.0; + + -- next billing, please + + FOR current_billing IN + SELECT * + FROM money.billing + WHERE xact = p_xact + AND voided + ORDER BY billing_ts + LOOP + + billing_remainder = current_billing.amount; + + current_result.xact = p_xact; + current_result.payment = current_payment.id; + current_result.billing = current_billing.id; + current_result.payment_ts = current_payment.payment_ts; + current_result.billing_ts = current_billing.billing_ts; + current_result.payment_type = 'REFUND'; + current_result.billing_type = current_billing.billing_type; + current_result.payment_ou = payment_ou; + current_result.billing_ou = billing_ou; + + IF billing_remainder >= payment_remainder THEN + current_result.amount = payment_remainder; + billing_remainder = billing_remainder - payment_remainder; + payment_remainder = 0.0; + ELSE + 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; + + EXIT WHEN payment_remainder = 0.0; + + END LOOP; + + payment_offset = payment_offset + 1; + + END LOOP; + +END; + +$$ LANGUAGE PLPGSQL; + + + +CREATE OR REPLACE FUNCTION money.payment_by_billing_type ( + range_start TIMESTAMPTZ, + range_end TIMESTAMPTZ, + location INT +) RETURNS SETOF money.materialized_payment_by_billing_type AS $$ + +DECLARE + current_transaction RECORD; + current_result money.materialized_payment_by_billing_type%ROWTYPE; +BEGIN + + -- first, we find transactions at specified locations involivng + -- positve, unvoided payments within the specified range + FOR current_transaction IN + SELECT DISTINCT x.id + FROM action.circulation x + JOIN money.payment p ON (x.id = p.xact) + JOIN actor.org_unit_descendants(location) d + ON (d.id = x.circ_lib) + WHERE p.payment_ts BETWEEN range_start AND range_end + AND NOT p.voided + AND p.amount > 0.0 + UNION ALL + SELECT DISTINCT x.id + FROM money.grocery x + JOIN money.payment p ON (x.id = p.xact) + JOIN actor.org_unit_descendants(location) d + ON (d.id = x.billing_location) + WHERE p.payment_ts BETWEEN range_start AND range_end + AND NOT p.voided + AND p.amount > 0.0 + LOOP + + -- then, we send each transaction to the payment-by-billing-type + -- calculator, and return rows for payments we're interested in + FOR current_result IN + SELECT * FROM money.payment_by_billing_type( current_transaction.id ) + LOOP + IF current_result.payment_ts BETWEEN range_start AND range_end THEN + RETURN NEXT current_result; + END IF; + END LOOP; + + END LOOP; + +END; + +$$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION money.payment_by_billing_type_trigger () +RETURNS TRIGGER AS $$ + +BEGIN + + IF TG_OP = 'INSERT' THEN + DELETE FROM money.materialized_payment_by_billing_type + WHERE xact = NEW.xact; + + 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( NEW.xact ); + + ELSIF TG_OP = 'UPDATE' THEN + DELETE FROM money.materialized_payment_by_billing_type + WHERE xact IN (OLD.xact,NEW.xact); + + 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( 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 + ) SELECT xact, payment, billing, payment_ts, billing_ts, + payment_type, billing_type, amount, billing_ou, payment_ou + FROM money.payment_by_billing_type( OLD.xact ); + END IF; + + ELSE + DELETE FROM money.materialized_payment_by_billing_type + WHERE xact = OLD.xact; + + 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( OLD.xact ); + + RETURN OLD; + END IF; + + RETURN NEW; + +END; + +$$ LANGUAGE PLPGSQL; + + +CREATE TRIGGER calculate_payment_by_btype_tgr + AFTER INSERT OR UPDATE OR DELETE ON money.billing + FOR EACH ROW EXECUTE PROCEDURE money.payment_by_billing_type_trigger(); + +CREATE TRIGGER calculate_payment_by_btype_tgr + AFTER INSERT OR UPDATE OR DELETE ON money.payment + FOR EACH ROW EXECUTE PROCEDURE money.payment_by_billing_type_trigger(); + +CREATE TRIGGER calculate_payment_by_btype_tgr + AFTER INSERT OR UPDATE OR DELETE ON money.bnm_payment + FOR EACH ROW EXECUTE PROCEDURE money.payment_by_billing_type_trigger(); + +CREATE TRIGGER calculate_payment_by_btype_tgr + AFTER INSERT OR UPDATE OR DELETE ON money.forgive_payment + FOR EACH ROW EXECUTE PROCEDURE money.payment_by_billing_type_trigger(); + +CREATE TRIGGER calculate_payment_by_btype_tgr + AFTER INSERT OR UPDATE OR DELETE ON money.work_payment + FOR EACH ROW EXECUTE PROCEDURE money.payment_by_billing_type_trigger(); + +CREATE TRIGGER calculate_payment_by_btype_tgr + AFTER INSERT OR UPDATE OR DELETE ON money.credit_payment + FOR EACH ROW EXECUTE PROCEDURE money.payment_by_billing_type_trigger(); + +CREATE TRIGGER calculate_payment_by_btype_tgr + AFTER INSERT OR UPDATE OR DELETE ON money.goods_payment + FOR EACH ROW EXECUTE PROCEDURE money.payment_by_billing_type_trigger(); + +CREATE TRIGGER calculate_payment_by_btype_tgr + AFTER INSERT OR UPDATE OR DELETE ON money.bnm_desk_payment + FOR EACH ROW EXECUTE PROCEDURE money.payment_by_billing_type_trigger(); + +CREATE TRIGGER calculate_payment_by_btype_tgr + AFTER INSERT OR UPDATE OR DELETE ON money.cash_payment + FOR EACH ROW EXECUTE PROCEDURE money.payment_by_billing_type_trigger(); + +CREATE TRIGGER calculate_payment_by_btype_tgr + AFTER INSERT OR UPDATE OR DELETE ON money.check_payment + FOR EACH ROW EXECUTE PROCEDURE money.payment_by_billing_type_trigger(); + +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