From 4d335199555ca2504d19756933b42650bd32b5e1 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Mon, 24 Sep 2018 14:34:07 -0400 Subject: [PATCH] JBAS-2117 Aged billing/payment tables local SQL Signed-off-by: Bill Erickson --- KCLS/sql/schema/deploy/aged-billings-payments.sql | 160 ++++++++++++++++++++++ KCLS/sql/schema/revert/aged-billings-payments.sql | 7 + KCLS/sql/schema/sqitch.plan | 1 + 3 files changed, 168 insertions(+) create mode 100644 KCLS/sql/schema/deploy/aged-billings-payments.sql create mode 100644 KCLS/sql/schema/revert/aged-billings-payments.sql diff --git a/KCLS/sql/schema/deploy/aged-billings-payments.sql b/KCLS/sql/schema/deploy/aged-billings-payments.sql new file mode 100644 index 0000000000..efa4f57e38 --- /dev/null +++ b/KCLS/sql/schema/deploy/aged-billings-payments.sql @@ -0,0 +1,160 @@ +-- Deploy kcls-evergreen:aged-billings-payments to pg +-- requires: ecard-notice-validator + +BEGIN; + +SET STATEMENT_TIMEOUT = 0; + +SELECT CLOCK_TIMESTAMP(), 'Applying schema changes'; + +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); + +CREATE OR REPLACE VIEW money.all_payments AS + SELECT * FROM money.payment_view + UNION ALL + SELECT * FROM money.aged_payment; + +CREATE OR REPLACE VIEW money.all_billings AS + SELECT * FROM money.billing + UNION ALL + SELECT * FROM money.aged_billing; + +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'; + +SELECT CLOCK_TIMESTAMP(), 'Building money.aged_payment'; + +INSERT INTO money.aged_payment + SELECT mp.* FROM money.payment_view mp + JOIN action.aged_circulation circ ON (circ.id = mp.xact); + +SELECT CLOCK_TIMESTAMP(), 'Building money.aged_billing'; + +INSERT INTO money.aged_billing + SELECT mb.* FROM money.billing mb + JOIN action.aged_circulation circ ON (circ.id = mb.xact); + +-- Clean up payments first so account_adjustments won't stick +-- around referencing deleted billings + +SELECT CLOCK_TIMESTAMP(), 'Disabling payment triggers'; + +ALTER TABLE money.payment DISABLE TRIGGER mat_summary_del_tgr; +ALTER TABLE money.cash_payment DISABLE TRIGGER mat_summary_del_tgr; +ALTER TABLE money.check_payment DISABLE TRIGGER mat_summary_del_tgr; +ALTER TABLE money.credit_card_payment DISABLE TRIGGER mat_summary_del_tgr; +ALTER TABLE money.forgive_payment DISABLE TRIGGER mat_summary_del_tgr; +ALTER TABLE money.credit_payment DISABLE TRIGGER mat_summary_del_tgr; +ALTER TABLE money.goods_payment DISABLE TRIGGER mat_summary_del_tgr; + +SELECT CLOCK_TIMESTAMP(), 'Deleting payments'; + +DELETE FROM money.payment WHERE id IN ( + SELECT mp.id FROM money.payment mp + JOIN money.aged_payment USING (id) +); + +SELECT CLOCK_TIMESTAMP(), 'Enabling payment triggers'; + +ALTER TABLE money.payment ENABLE TRIGGER mat_summary_del_tgr; +ALTER TABLE money.cash_payment ENABLE TRIGGER mat_summary_del_tgr; +ALTER TABLE money.check_payment ENABLE TRIGGER mat_summary_del_tgr; +ALTER TABLE money.credit_card_payment ENABLE TRIGGER mat_summary_del_tgr; +ALTER TABLE money.forgive_payment ENABLE TRIGGER mat_summary_del_tgr; +ALTER TABLE money.credit_payment ENABLE TRIGGER mat_summary_del_tgr; +ALTER TABLE money.goods_payment ENABLE TRIGGER mat_summary_del_tgr; + +SELECT CLOCK_TIMESTAMP(), 'Creating tmp billing table'; + +-- Copy all money.billing rows that are not represented in money.aged_billing +CREATE TEMPORARY TABLE tmp_money_billing ON COMMIT DROP AS + SELECT mb.* FROM money.billing mb + LEFT JOIN money.aged_billing mab USING (id) + WHERE mab.id IS NULL; + +SELECT CLOCK_TIMESTAMP(), 'Truncating money.billing'; + +ALTER TABLE money.billing DISABLE TRIGGER ALL; + +-- drop and rebuild the billing foreign key constraint. +-- needed for truncate +ALTER TABLE money.account_adjustment + DROP CONSTRAINT account_adjustment_billing_fkey; + +TRUNCATE money.billing; + +SELECT CLOCK_TIMESTAMP(), 'Rebuilding money.billing'; + +INSERT INTO money.billing SELECT * FROM tmp_money_billing; + +SELECT CLOCK_TIMESTAMP(), 'Done rebuilding money.billing'; + +ALTER TABLE money.billing ENABLE TRIGGER ALL; +ALTER TABLE money.account_adjustment + ADD CONSTRAINT account_adjustment_billing_fkey + FOREIGN KEY (billing) REFERENCES money.billing (id); + +COMMIT; + +SELECT CLOCK_TIMESTAMP(), 'Analyzing money.billing'; + +ANALYZE money.billing; + +SELECT CLOCK_TIMESTAMP(), 'Analyzing payment tables'; + +VACUUM ANALYZE money.credit_card_payment; +VACUUM ANALYZE money.forgive_payment; +VACUUM ANALYZE money.cash_payment; + +SELECT CLOCK_TIMESTAMP(), 'All Done'; + diff --git a/KCLS/sql/schema/revert/aged-billings-payments.sql b/KCLS/sql/schema/revert/aged-billings-payments.sql new file mode 100644 index 0000000000..15754391ac --- /dev/null +++ b/KCLS/sql/schema/revert/aged-billings-payments.sql @@ -0,0 +1,7 @@ +-- Revert kcls-evergreen:aged-billings-payments from pg + +BEGIN; + +-- XXX Add DDLs here. + +COMMIT; diff --git a/KCLS/sql/schema/sqitch.plan b/KCLS/sql/schema/sqitch.plan index da3722233c..265fd6c5e1 100644 --- a/KCLS/sql/schema/sqitch.plan +++ b/KCLS/sql/schema/sqitch.plan @@ -72,6 +72,7 @@ acq-inv-close-fields [search-index-keep-periods] 2018-04-12T19:03:29Z Bill Erick ecard-data [2.10-to-2.12-upgrade] 2018-01-03T21:55:03Z Bill Erickson,,, # Ecard lib settings, policy data, etc. remove-gender [ecard-data] 2018-06-06T14:44:36Z Bill Erickson,,, # Remove gender field/data ecard-notice-validator [remove-gender] 2018-07-26T14:33:57Z Bill Erickson,,, # eCard UMS notice validator +aged-billings-payments [ecard-notice-validator] 2018-09-24T18:00:57Z Bill Erickson,,, # Aged money/billing schema and data stock-browse-schema [ecard-notice-validator] 2018-08-31T15:22:58Z Bill Erickson,,, # Recover stock browse data tables, etc. stock-browse-headings-report [stock-browse-schema] 2018-10-04T15:56:18Z Bill Erickson,,, # New heading report updates for stock browse stock-browse-cleanup [stock-browse-schema] 2018-10-03T18:05:49Z Bill Erickson,,, # Delete old browse data -- 2.11.0