From fcedac0ca4404ca6a6207979b4a1854a99984385 Mon Sep 17 00:00:00 2001 From: Galen Charlton Date: Wed, 2 Oct 2019 11:31:06 -0400 Subject: [PATCH] LP#1846357: fix circulation and billing aging An error merging an update to action.age_circ_on_delete() resulted in a regression to the aging of billings. This patch fixes it. To test ------- [1] Apply the patch, then age circulations. Verify that relevant billings are also aged. Signed-off-by: Galen Charlton --- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- .../Pg/upgrade/1192.schema.fix_circ_aging.sql | 57 +++++++++++++++++++ 2 files changed, 58 insertions(+), 1 deletion(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/1192.schema.fix_circ_aging.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index d40a36d9d1..8607b3d0b6 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -92,7 +92,7 @@ CREATE TRIGGER no_overlapping_deps BEFORE INSERT OR UPDATE ON config.db_patch_dependencies FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('deprecates'); -INSERT INTO config.upgrade_log (version, applied_to) VALUES ('1191', :eg_version); -- miker/jeffdavis/sandbergja +INSERT INTO config.upgrade_log (version, applied_to) VALUES ('1192', :eg_version); -- gmcharlt CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/upgrade/1192.schema.fix_circ_aging.sql b/Open-ILS/src/sql/Pg/upgrade/1192.schema.fix_circ_aging.sql new file mode 100644 index 0000000000..8a1cf92a4a --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/1192.schema.fix_circ_aging.sql @@ -0,0 +1,57 @@ +BEGIN; + +SELECT evergreen.upgrade_deps_block_check('1192', :eg_version); + +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, + auto_renewal, auto_renewal_remaining) + 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, + auto_renewal, auto_renewal_remaining + 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.payment WHERE xact = OLD.id; + DELETE FROM money.billing WHERE xact = OLD.id; + + RETURN OLD; +END; +$$ LANGUAGE 'plpgsql'; + +COMMIT; -- 2.43.2