4 --SELECT evergreen.upgrade_deps_block_check('XXXX', :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);
13 INSERT INTO money.aged_payment
14 SELECT mp.* FROM money.payment_view mp
15 JOIN action.aged_circulation circ ON (circ.id = mp.xact);
17 INSERT INTO money.aged_billing
18 SELECT mb.* FROM money.billing mb
19 JOIN action.aged_circulation circ ON (circ.id = mb.xact);
21 DELETE FROM money.payment WHERE id IN (
22 SELECT mp.id FROM money.payment mp
23 JOIN action.aged_circulation circ ON (circ.id = mp.xact)
26 DELETE FROM money.billing WHERE id IN (
27 SELECT mb.id FROM money.billing mb
28 JOIN action.aged_circulation circ ON (circ.id = mb.xact)
31 CREATE OR REPLACE VIEW money.all_payments AS
32 SELECT * FROM money.payment_view
34 SELECT * FROM money.aged_payment;
36 CREATE OR REPLACE VIEW money.all_billings AS
37 SELECT * FROM money.billing
39 SELECT * FROM money.aged_billing;
41 CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$
46 -- If there are any renewals for this circulation, don't archive or delete
47 -- it yet. We'll do so later, when we archive and delete the renewals.
50 FROM action.circulation
51 WHERE parent_circ = OLD.id
55 RETURN NULL; -- don't delete
58 -- Archive a copy of the old row to action.aged_circulation
60 INSERT INTO action.aged_circulation
61 (id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
62 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
63 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
64 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
65 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
66 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ)
68 id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
69 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
70 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
71 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
72 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
73 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ
74 FROM action.all_circulation WHERE id = OLD.id;
76 -- Migrate billings and payments to aged tables
78 INSERT INTO money.aged_billing
79 SELECT * FROM money.billing WHERE xact = OLD.id;
81 INSERT INTO money.aged_payment
82 SELECT * FROM money.payment_view WHERE xact = OLD.id;
84 DELETE FROM money.billing WHERE xact = OLD.id;
85 DELETE FROM money.payment WHERE xact = OLD.id;
89 $$ LANGUAGE 'plpgsql';