1 --Upgrade Script for 3.4.2 to 3.4.3
2 \set eg_version '''3.4.3'''
4 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('3.4.3', :eg_version);
6 SELECT evergreen.upgrade_deps_block_check('1202', :eg_version);
8 INSERT INTO config.global_flag (name, value, enabled, label)
10 'history.money.age_with_circs',
14 'history.money.age_with_circs',
15 'Age billings and payments when cirulcations are aged.',
19 'history.money.retention_age',
23 'history.money.retention_age',
24 'Age billings and payments whose transactions were completed ' ||
25 'this long ago. For circulation transactions, this setting ' ||
26 'is superseded by the "history.money.age_with_circs" setting',
31 DROP VIEW money.all_payments;
33 CREATE OR REPLACE VIEW money.payment_view_for_aging AS
38 FROM money.payment_view p
39 LEFT JOIN money.bnm_payment bnm ON bnm.id = p.id
40 LEFT JOIN money.bnm_desk_payment bnmd ON bnmd.id = p.id
41 LEFT JOIN money.account_adjustment maa ON maa.id = p.id;
43 ALTER TABLE money.aged_payment
44 ADD COLUMN accepting_usr INTEGER,
45 ADD COLUMN cash_drawer INTEGER,
46 ADD COLUMN billing BIGINT;
48 CREATE INDEX aged_payment_accepting_usr_idx ON money.aged_payment(accepting_usr);
49 CREATE INDEX aged_payment_cash_drawer_idx ON money.aged_payment(cash_drawer);
50 CREATE INDEX aged_payment_billing_idx ON money.aged_payment(billing);
52 CREATE OR REPLACE VIEW money.all_payments AS
53 SELECT * FROM money.payment_view_for_aging
55 SELECT * FROM money.aged_payment;
57 CREATE OR REPLACE FUNCTION money.age_billings_and_payments() RETURNS INTEGER AS $FUNC$
58 -- Age billings and payments linked to transactions which were
59 -- completed at least 'older_than' time ago.
62 counter INTEGER DEFAULT 0;
66 SELECT value::INTERVAL INTO keep_age FROM config.global_flag
67 WHERE name = 'history.money.retention_age' AND enabled;
69 -- Confirm interval-based aging is enabled.
70 IF keep_age IS NULL THEN RETURN counter; END IF;
72 -- Start with non-circulation transactions
73 FOR xact_id IN SELECT DISTINCT(xact.id) FROM money.billable_xact xact
74 -- confirm there is something to age
75 JOIN money.billing mb ON mb.xact = xact.id
76 -- Avoid aging money linked to non-aged circulations.
77 LEFT JOIN action.circulation circ ON circ.id = xact.id
78 WHERE circ.id IS NULL AND AGE(NOW(), xact.xact_finish) > keep_age LOOP
80 PERFORM money.age_billings_and_payments_for_xact(xact_id);
81 counter := counter + 1;
84 -- Then handle aged circulation money.
85 FOR xact_id IN SELECT DISTINCT(xact.id) FROM action.aged_circulation xact
86 -- confirm there is something to age
87 JOIN money.billing mb ON mb.xact = xact.id
88 WHERE AGE(NOW(), xact.xact_finish) > keep_age LOOP
90 PERFORM money.age_billings_and_payments_for_xact(xact_id);
91 counter := counter + 1;
96 $FUNC$ LANGUAGE PLPGSQL;
98 CREATE OR REPLACE FUNCTION money.age_billings_and_payments_for_xact
99 (xact_id BIGINT) RETURNS VOID AS $FUNC$
101 INSERT INTO money.aged_billing
102 SELECT * FROM money.billing WHERE xact = $1;
104 INSERT INTO money.aged_payment
105 SELECT * FROM money.payment_view_for_aging WHERE xact = xact_id;
107 DELETE FROM money.payment WHERE xact = $1;
108 DELETE FROM money.billing WHERE xact = $1;
112 CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$
117 -- If there are any renewals for this circulation, don't archive or delete
118 -- it yet. We'll do so later, when we archive and delete the renewals.
120 SELECT 'Y' INTO found
121 FROM action.circulation
122 WHERE parent_circ = OLD.id
126 RETURN NULL; -- don't delete
129 -- Archive a copy of the old row to action.aged_circulation
131 INSERT INTO action.aged_circulation
132 (id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
133 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
134 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
135 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
136 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
137 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ,
138 auto_renewal, auto_renewal_remaining)
140 id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
141 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
142 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
143 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
144 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
145 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ,
146 auto_renewal, auto_renewal_remaining
147 FROM action.all_circulation WHERE id = OLD.id;
149 -- Migrate billings and payments to aged tables
151 SELECT 'Y' INTO found FROM config.global_flag
152 WHERE name = 'history.money.age_with_circs' AND enabled;
155 PERFORM money.age_billings_and_payments_for_xact(OLD.id);
160 $$ LANGUAGE 'plpgsql';
162 SELECT evergreen.upgrade_deps_block_check('1204', :eg_version);
164 INSERT INTO permission.perm_list ( id, code, description ) VALUES
165 ( 621, 'VIEW_BOOKING_RESOURCE_TYPE', oils_i18n_gettext(621,
166 'View booking resource types', 'ppl', 'description')),
167 ( 622, 'VIEW_BOOKING_RESOURCE', oils_i18n_gettext(622,
168 'View booking resources', 'ppl', 'description'))