From 33c46ab6c830b58932b4fa7b707f95edcf698db2 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Fri, 21 Sep 2018 14:53:33 -0400 Subject: [PATCH] LP#1793802 Aged billings and payments Creates tables money.aged_billing and money.aged_payment. When a circulation is aged, billings/payments linked to the circ are migrated to the aged_* table. Upgrade script includes a migration script for migrating existing billings and payments. Includes IDL entries for reporting and pcrud access just in case it's needed. Note the 'xact' column on these new tables links directly to the aged circulation instead of the non-existent money.billable_xact. Signed-off-by: Bill Erickson Signed-off-by: Chris Sharp Signed-off-by: Galen Charlton --- Open-ILS/examples/fm_IDL.xml | 56 +++++++++++++ Open-ILS/src/sql/Pg/080.schema.money.sql | 6 +- Open-ILS/src/sql/Pg/090.schema.action.sql | 11 +++ .../XXXX.schema.aged-billing-payment.sql | 82 +++++++++++++++++++ 4 files changed, 154 insertions(+), 1 deletion(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.aged-billing-payment.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 1a8598848b..19e65a9f85 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -8066,6 +8066,30 @@ SELECT usr, + + + + + + + + + + + + + + + + + + + + + @@ -8338,6 +8362,38 @@ SELECT usr, + + + + + + + + + + + + + + + + + + + + + + + + + + + + + diff --git a/Open-ILS/src/sql/Pg/080.schema.money.sql b/Open-ILS/src/sql/Pg/080.schema.money.sql index b5f3b27241..85352ab2ab 100644 --- a/Open-ILS/src/sql/Pg/080.schema.money.sql +++ b/Open-ILS/src/sql/Pg/080.schema.money.sql @@ -77,7 +77,6 @@ END; $$ LANGUAGE PLPGSQL; CREATE TRIGGER maintain_billing_ts_tgr BEFORE INSERT OR UPDATE ON money.billing FOR EACH ROW EXECUTE PROCEDURE money.maintain_billing_ts(); - CREATE TABLE money.payment ( id BIGSERIAL PRIMARY KEY, xact BIGINT NOT NULL, -- money.billable_xact.id @@ -689,5 +688,10 @@ CREATE OR REPLACE VIEW money.cashdrawer_payment_view AS LEFT JOIN money.payment_view t ON (p.id = t.id); +-- Create 'aged' clones of billing and payment_view tables +CREATE TABLE money.aged_payment (LIKE money.payment INCLUDING INDEXES); +ALTER TABLE money.aged_payment ADD COLUMN payment_type TEXT NOT NULL; +CREATE TABLE money.aged_billing (LIKE money.billing INCLUDING INDEXES); + COMMIT; diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql index 900c1b36d3..fe3c78a6cc 100644 --- a/Open-ILS/src/sql/Pg/090.schema.action.sql +++ b/Open-ILS/src/sql/Pg/090.schema.action.sql @@ -350,6 +350,17 @@ BEGIN max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ FROM action.all_circulation WHERE id = OLD.id; + -- Migrate billings and payments to aged tables + + INSERT INTO money.aged_billing + SELECT * FROM money.billing WHERE xact = OLD.id; + + INSERT INTO money.aged_payment + SELECT * FROM money.payment_view WHERE xact = OLD.id; + + DELETE FROM money.billing WHERE xact = OLD.id; + DELETE FROM money.payment WHERE xact = OLD.id; + RETURN OLD; END; $$ LANGUAGE 'plpgsql'; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.aged-billing-payment.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.aged-billing-payment.sql new file mode 100644 index 0000000000..f465411dc9 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.aged-billing-payment.sql @@ -0,0 +1,82 @@ + +BEGIN; + +--SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +\qecho Migrating aged billing and payment data. This might take a while. + +CREATE TABLE money.aged_payment (LIKE money.payment INCLUDING INDEXES); +ALTER TABLE money.aged_payment ADD COLUMN payment_type TEXT NOT NULL; +CREATE TABLE money.aged_billing (LIKE money.billing INCLUDING INDEXES); + +INSERT INTO money.aged_payment + SELECT mp.* FROM money.payment_view mp + JOIN action.aged_circulation circ ON (circ.id = mp.xact); + +INSERT INTO money.aged_billing + SELECT mb.* FROM money.billing mb + JOIN action.aged_circulation circ ON (circ.id = mb.xact); + +DELETE FROM money.payment WHERE id IN ( + SELECT mp.id FROM money.payment mp + JOIN action.aged_circulation circ ON (circ.id = mp.xact) +); + +DELETE FROM money.billing WHERE id IN ( + SELECT mb.id FROM money.billing mb + JOIN action.aged_circulation circ ON (circ.id = mb.xact) +); + + +CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$ +DECLARE +found char := 'N'; +BEGIN + + -- If there are any renewals for this circulation, don't archive or delete + -- it yet. We'll do so later, when we archive and delete the renewals. + + SELECT 'Y' INTO found + FROM action.circulation + WHERE parent_circ = OLD.id + LIMIT 1; + + IF found = 'Y' THEN + RETURN NULL; -- don't delete + END IF; + + -- Archive a copy of the old row to action.aged_circulation + + INSERT INTO action.aged_circulation + (id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location, + copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy, + circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date, + stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine, + max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule, + max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ) + SELECT + id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location, + copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy, + circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date, + stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine, + max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule, + max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ + FROM action.all_circulation WHERE id = OLD.id; + + -- Migrate billings and payments to aged tables + + INSERT INTO money.aged_billing + SELECT * FROM money.billing WHERE xact = OLD.id; + + INSERT INTO money.aged_payment + SELECT * FROM money.payment_view WHERE xact = OLD.id; + + DELETE FROM money.billing WHERE xact = OLD.id; + DELETE FROM money.payment WHERE xact = OLD.id; + + RETURN OLD; +END; +$$ LANGUAGE 'plpgsql'; + +COMMIT; + -- 2.43.2