From 1cba268f013f3855e4dbed71bcbfb061a4faec30 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Fri, 21 Sep 2018 14:53:33 -0400 Subject: [PATCH] LP#793802 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 --- Open-ILS/examples/fm_IDL.xml | 56 +++++++++++++++ Open-ILS/src/sql/Pg/080.schema.money.sql | 5 ++ Open-ILS/src/sql/Pg/090.schema.action.sql | 11 +++ .../upgrade/XXXX.schema.aged-billing-payment.sql | 82 ++++++++++++++++++++++ 4 files changed, 154 insertions(+) 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 458c56c3e8..e9a0007d2e 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -7595,6 +7595,30 @@ SELECT usr, + + + + + + + + + + + + + + + + + + + + + @@ -7864,6 +7888,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 6265ae8da2..4188bdfd8a 100644 --- a/Open-ILS/src/sql/Pg/080.schema.money.sql +++ b/Open-ILS/src/sql/Pg/080.schema.money.sql @@ -675,5 +675,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 35ed3ac023..3498285960 100644 --- a/Open-ILS/src/sql/Pg/090.schema.action.sql +++ b/Open-ILS/src/sql/Pg/090.schema.action.sql @@ -345,6 +345,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.11.0