4 SELECT evergreen.upgrade_deps_block_check('1181', :eg_version);
6 \qecho Migrating aged billing and payment data. This might take a while.
8 CREATE TABLE money.aged_payment (LIKE money.payment INCLUDING INDEXES);
9 ALTER TABLE money.aged_payment ADD COLUMN payment_type TEXT NOT NULL;
11 CREATE TABLE money.aged_billing (LIKE money.billing INCLUDING INDEXES);
14 /* LP 1858448 : Disable initial aged money migration
16 INSERT INTO money.aged_payment
17 SELECT mp.* FROM money.payment_view mp
18 JOIN action.aged_circulation circ ON (circ.id = mp.xact);
20 INSERT INTO money.aged_billing
21 SELECT mb.* FROM money.billing mb
22 JOIN action.aged_circulation circ ON (circ.id = mb.xact);
25 CREATE OR REPLACE VIEW money.all_payments AS
26 SELECT * FROM money.payment_view
28 SELECT * FROM money.aged_payment;
30 CREATE OR REPLACE VIEW money.all_billings AS
31 SELECT * FROM money.billing
33 SELECT * FROM money.aged_billing;
35 CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$
40 -- If there are any renewals for this circulation, don't archive or delete
41 -- it yet. We'll do so later, when we archive and delete the renewals.
44 FROM action.circulation
45 WHERE parent_circ = OLD.id
49 RETURN NULL; -- don't delete
52 -- Archive a copy of the old row to action.aged_circulation
54 INSERT INTO action.aged_circulation
55 (id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
56 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
57 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
58 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
59 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
60 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ)
62 id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
63 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
64 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
65 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
66 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
67 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ
68 FROM action.all_circulation WHERE id = OLD.id;
70 -- Migrate billings and payments to aged tables
73 /* LP 1858448 : Disable initial aged money migration
74 INSERT INTO money.aged_billing
75 SELECT * FROM money.billing WHERE xact = OLD.id;
77 INSERT INTO money.aged_payment
78 SELECT * FROM money.payment_view WHERE xact = OLD.id;
80 DELETE FROM money.payment WHERE xact = OLD.id;
81 DELETE FROM money.billing WHERE xact = OLD.id;
86 $$ LANGUAGE 'plpgsql';
89 /* LP 1858448 : Disable initial aged money migration
91 -- NOTE you could COMMIT here then start a new TRANSACTION if desired.
93 \qecho Deleting aged payments and billings from active payment/billing
94 \qecho tables. This may take a while...
96 ALTER TABLE money.payment DISABLE TRIGGER mat_summary_del_tgr;
97 ALTER TABLE money.cash_payment DISABLE TRIGGER mat_summary_del_tgr;
98 ALTER TABLE money.check_payment DISABLE TRIGGER mat_summary_del_tgr;
99 ALTER TABLE money.credit_card_payment DISABLE TRIGGER mat_summary_del_tgr;
100 ALTER TABLE money.forgive_payment DISABLE TRIGGER mat_summary_del_tgr;
101 ALTER TABLE money.credit_payment DISABLE TRIGGER mat_summary_del_tgr;
102 ALTER TABLE money.goods_payment DISABLE TRIGGER mat_summary_del_tgr;
104 DELETE FROM money.payment WHERE id IN (SELECT id FROM money.aged_payment);
106 ALTER TABLE money.payment ENABLE TRIGGER mat_summary_del_tgr;
107 ALTER TABLE money.cash_payment ENABLE TRIGGER mat_summary_del_tgr;
108 ALTER TABLE money.check_payment ENABLE TRIGGER mat_summary_del_tgr;
109 ALTER TABLE money.credit_card_payment ENABLE TRIGGER mat_summary_del_tgr;
110 ALTER TABLE money.forgive_payment ENABLE TRIGGER mat_summary_del_tgr;
111 ALTER TABLE money.credit_payment ENABLE TRIGGER mat_summary_del_tgr;
112 ALTER TABLE money.goods_payment ENABLE TRIGGER mat_summary_del_tgr;
114 -- TODO: This approach assumes most of the money.billing rows have been
115 -- copied to money.aged_billing. If that is not the case, which would
116 -- happen if circ anonymization is not enabled, it will be faster to
117 -- perform a simple delete instead of a truncate/rebuild.
119 -- Copy all money.billing rows that are not represented in money.aged_billing
120 CREATE TEMPORARY TABLE tmp_money_billing ON COMMIT DROP AS
121 SELECT mb.* FROM money.billing mb
122 LEFT JOIN money.aged_billing mab USING (id)
123 WHERE mab.id IS NULL;
125 ALTER TABLE money.billing DISABLE TRIGGER ALL;
127 -- temporarily remove the foreign key constraint to money.billing on
128 -- account adjusment. Needed for money.billing truncate.
129 ALTER TABLE money.account_adjustment
130 DROP CONSTRAINT account_adjustment_billing_fkey;
132 TRUNCATE money.billing;
134 INSERT INTO money.billing SELECT * FROM tmp_money_billing;
136 ALTER TABLE money.billing ENABLE TRIGGER ALL;
137 ALTER TABLE money.account_adjustment
138 ADD CONSTRAINT account_adjustment_billing_fkey
139 FOREIGN KEY (billing) REFERENCES money.billing (id);
145 -- Good to run after truncating -- OK to run after COMMIT.
146 /* LP 1858448 : Disable initial aged money migration
147 ANALYZE money.billing;