$FUNC$ LANGUAGE SQL;
+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;
+ fast_forward BOOLEAN := FALSE;
+ maintain_billing_remainder BOOLEAN := FALSE;
+ billing_loop INT := -1;
+ billing_row_count INT := 0;
+ current_billing_id BIGINT := 0;
+ billing_id_used BIGINT ARRAY;
+ billing_l INT := 0;
+ continuing_payment BOOLEAN := FALSE;
+ continuing_payment_last_row BOOLEAN := FALSE;
+BEGIN
+
+ /* 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;
+
+ SELECT count(id) INTO billing_row_count FROM money.billing WHERE xact = p_xact;
+
+ -- 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;
+ -- With every new payment row, we need to fast forward
+ -- the billing lines up to the last paid billing row
+ fast_forward := TRUE;
+
+ 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;
+ -- If we don't do this then OPAC CC payments have no payment_ou
+ IF NOT FOUND THEN
+ 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?
+ 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;
+ -- 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;
+ billing_id_used = array_append( billing_id_used, current_billing_id );
+ 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;
+ billing_id_used = array_append( billing_id_used, current_billing_id );
+ continuing_payment := TRUE;
+ maintain_billing_remainder := FALSE;
+ END IF;
+
+ 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;
+ END IF;
+
+ CONTINUE WHEN payment_remainder = 0.0;
+
+ -- next billing, please
+ billing_loop := -1;
+
+ FOR current_billing IN
+ SELECT *
+ FROM money.billing
+ WHERE xact = p_xact
+ -- Gotta put the voided billing rows at the bottom (last)
+ ORDER BY voided,billing_ts
+ LOOP
+ billing_loop = billing_loop + 1;
+
+ -- Skip billing rows that we have already paid
+ IF billing_id_used @> ARRAY[current_billing.id] THEN CONTINUE;
+ END IF;
+
+ IF maintain_billing_remainder THEN
+ CONTINUE WHEN current_billing.id <> current_billing_id;
+ -- Account adjustment - we expect to pay billing rows that are identical amounts
+ ELSE IF current_payment.payment_type = 'account_adjustment' THEN
+ -- Go ahead and allow the row through when it's the last row and we still haven't found one with equal payment amount
+ CONTINUE WHEN ( ( current_billing.amount <> current_payment.amount ) AND ( billing_loop + 1 <> billing_row_count ) );
+ END IF;
+ 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;
+ fast_forward := FALSE;
+ current_billing_id := current_billing.id;
+ continuing_payment := FALSE;
+
+ 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;
+ -- 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;
+ billing_id_used = array_append( billing_id_used, current_billing_id );
+ END IF;
+ ELSE
+ current_result.amount = billing_remainder;
+ payment_remainder = payment_remainder - billing_remainder;
+ continuing_payment := TRUE;
+ IF billing_loop + 1 = billing_row_count THEN
+ -- We have a situation where we are on the last billing row and we are in the middle of a payment row
+ -- We need to start back at the beginning of the billing rows and pay
+ continuing_payment_last_row := TRUE;
+ END IF;
+ billing_remainder = 0.0;
+ billing_offset = billing_offset + 1;
+ billing_id_used = array_append( billing_id_used, current_billing_id );
+ END IF;
+
+ RETURN NEXT current_result;
+ IF continuing_payment_last_row THEN
+ -- This should only occur when the account_adjustment's do not line up exactly with the billing
+ -- So we are going to pay some other type of billing row with this odd account_adjustment
+ -- And we need to stay in the current_payment row while doing so
+ billing_loop := -1;
+ FOR current_billing IN
+ SELECT *
+ FROM money.billing
+ WHERE xact = p_xact
+ ORDER BY voided,billing_ts
+ LOOP
+ billing_loop = billing_loop + 1;
+ -- Skip billing rows that we have already paid
+ IF billing_id_used @> ARRAY[current_billing.id] THEN CONTINUE; END IF;
+
+ billing_remainder = current_billing.amount;
+ current_billing_id := current_billing.id;
+ continuing_payment := FALSE;
+
+ 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;
+ -- 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;
+ billing_id_used = array_append( billing_id_used, current_billing_id );
+ END IF;
+ ELSE
+ current_result.amount = billing_remainder;
+ payment_remainder = payment_remainder - billing_remainder;
+ billing_remainder = 0.0;
+ billing_offset = billing_offset + 1;
+ billing_id_used = array_append( billing_id_used, current_billing_id );
+ END IF;
+
+ RETURN NEXT current_result;
+ EXIT WHEN payment_remainder = 0.0;
+ END LOOP;
+
+ END IF;
+ 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 array_length(billing_id_used, 1) = billing_row_count;
+
+ END LOOP;
+
+ payment_remainder := 0.0;
+ 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
+ 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
+ SELECT *
+ FROM money.payment_view
+ WHERE xact = p_xact
+ AND NOT voided
+ AND amount < 0.0
+ ORDER BY payment_ts
+ LOOP
+
+ 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;
+
+ IF NOT FOUND THEN
+ 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
+ -- 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
+
+ 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;
+ -- 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;
+
+ 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;
+
+ -- next billing, please
+ billing_loop := -1;
+ FOR current_billing IN
+ SELECT *
+ FROM money.billing
+ WHERE xact = p_xact
+ AND voided
+ ORDER BY billing_ts
+ LOOP
+ billing_loop = billing_loop + 1; -- first iteration billing_loop=0, it starts at -1
+ -- Fast forward through the rows until we get to the billing row
+ -- where we left off
+ IF fast_forward THEN
+ 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;
+ fast_forward := FALSE;
+
+ 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;
+ -- 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;
+ 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;
+ -- 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;
+
+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 involving
+ -- 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;
--- /dev/null
+BEGIN;
+
+SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
+
+
+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;
+ fast_forward BOOLEAN := FALSE;
+ maintain_billing_remainder BOOLEAN := FALSE;
+ billing_loop INT := -1;
+ billing_row_count INT := 0;
+ current_billing_id BIGINT := 0;
+ billing_id_used BIGINT ARRAY;
+ billing_l INT := 0;
+ continuing_payment BOOLEAN := FALSE;
+ continuing_payment_last_row BOOLEAN := FALSE;
+BEGIN
+
+ /* 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;
+
+ SELECT count(id) INTO billing_row_count FROM money.billing WHERE xact = p_xact;
+
+ -- 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;
+ -- With every new payment row, we need to fast forward
+ -- the billing lines up to the last paid billing row
+ fast_forward := TRUE;
+
+ 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;
+ -- If we don't do this then OPAC CC payments have no payment_ou
+ IF NOT FOUND THEN
+ 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?
+ 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;
+ -- 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;
+ billing_id_used = array_append( billing_id_used, current_billing_id );
+ 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;
+ billing_id_used = array_append( billing_id_used, current_billing_id );
+ continuing_payment := TRUE;
+ maintain_billing_remainder := FALSE;
+ END IF;
+
+ 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;
+ END IF;
+
+ CONTINUE WHEN payment_remainder = 0.0;
+
+ -- next billing, please
+ billing_loop := -1;
+
+ FOR current_billing IN
+ SELECT *
+ FROM money.billing
+ WHERE xact = p_xact
+ -- Gotta put the voided billing rows at the bottom (last)
+ ORDER BY voided,billing_ts
+ LOOP
+ billing_loop = billing_loop + 1;
+
+ -- Skip billing rows that we have already paid
+ IF billing_id_used @> ARRAY[current_billing.id] THEN CONTINUE;
+ END IF;
+
+ IF maintain_billing_remainder THEN
+ CONTINUE WHEN current_billing.id <> current_billing_id;
+ -- Account adjustment - we expect to pay billing rows that are identical amounts
+ ELSE IF current_payment.payment_type = 'account_adjustment' THEN
+ -- Go ahead and allow the row through when it's the last row and we still haven't found one with equal payment amount
+ CONTINUE WHEN ( ( current_billing.amount <> current_payment.amount ) AND ( billing_loop + 1 <> billing_row_count ) );
+ END IF;
+ 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;
+ fast_forward := FALSE;
+ current_billing_id := current_billing.id;
+ continuing_payment := FALSE;
+
+ 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;
+ -- 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;
+ billing_id_used = array_append( billing_id_used, current_billing_id );
+ END IF;
+ ELSE
+ current_result.amount = billing_remainder;
+ payment_remainder = payment_remainder - billing_remainder;
+ continuing_payment := TRUE;
+ IF billing_loop + 1 = billing_row_count THEN
+ -- We have a situation where we are on the last billing row and we are in the middle of a payment row
+ -- We need to start back at the beginning of the billing rows and pay
+ continuing_payment_last_row := TRUE;
+ END IF;
+ billing_remainder = 0.0;
+ billing_offset = billing_offset + 1;
+ billing_id_used = array_append( billing_id_used, current_billing_id );
+ END IF;
+
+ RETURN NEXT current_result;
+ IF continuing_payment_last_row THEN
+ -- This should only occur when the account_adjustment's do not line up exactly with the billing
+ -- So we are going to pay some other type of billing row with this odd account_adjustment
+ -- And we need to stay in the current_payment row while doing so
+ billing_loop := -1;
+ FOR current_billing IN
+ SELECT *
+ FROM money.billing
+ WHERE xact = p_xact
+ ORDER BY voided,billing_ts
+ LOOP
+ billing_loop = billing_loop + 1;
+ -- Skip billing rows that we have already paid
+ IF billing_id_used @> ARRAY[current_billing.id] THEN CONTINUE; END IF;
+
+ billing_remainder = current_billing.amount;
+ current_billing_id := current_billing.id;
+ continuing_payment := FALSE;
+
+ 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;
+ -- 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;
+ billing_id_used = array_append( billing_id_used, current_billing_id );
+ END IF;
+ ELSE
+ current_result.amount = billing_remainder;
+ payment_remainder = payment_remainder - billing_remainder;
+ billing_remainder = 0.0;
+ billing_offset = billing_offset + 1;
+ billing_id_used = array_append( billing_id_used, current_billing_id );
+ END IF;
+
+ RETURN NEXT current_result;
+ EXIT WHEN payment_remainder = 0.0;
+ END LOOP;
+
+ END IF;
+ 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 array_length(billing_id_used, 1) = billing_row_count;
+
+ END LOOP;
+
+ payment_remainder := 0.0;
+ 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
+ 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
+ SELECT *
+ FROM money.payment_view
+ WHERE xact = p_xact
+ AND NOT voided
+ AND amount < 0.0
+ ORDER BY payment_ts
+ LOOP
+
+ 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;
+
+ IF NOT FOUND THEN
+ 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
+ -- 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
+
+ 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;
+ -- 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;
+
+ 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;
+
+ -- next billing, please
+ billing_loop := -1;
+ FOR current_billing IN
+ SELECT *
+ FROM money.billing
+ WHERE xact = p_xact
+ AND voided
+ ORDER BY billing_ts
+ LOOP
+ billing_loop = billing_loop + 1; -- first iteration billing_loop=0, it starts at -1
+ -- Fast forward through the rows until we get to the billing row
+ -- where we left off
+ IF fast_forward THEN
+ 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;
+ fast_forward := FALSE;
+
+ 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;
+ -- 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;
+ 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;
+ -- 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;
+
+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 involving
+ -- 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;
+
+
+-- 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
+ 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;
projects / evergreen-equinox.git / commitdiff