3 SELECT evergreen.upgrade_deps_block_check('TODO', :eg_version);
5 INSERT INTO config.global_flag (name, value, enabled, label)
7 'history.money.age_with_circs',
11 'history.money.age_with_circs',
12 'Age billings and payments when cirulcations are aged.',
16 'history.money.retention_age',
20 'history.money.retention_age',
21 'Age billings and payments whose transactions were completed ' ||
22 'this long ago. For circulation transactions, this setting ' ||
23 'is superseded by the "history.money.age_with_circs" setting',
28 DROP VIEW money.all_payments;
30 CREATE OR REPLACE VIEW money.payment_view_for_aging AS
35 FROM money.payment_view p
36 LEFT JOIN money.bnm_payment bnm ON bnm.id = p.id
37 LEFT JOIN money.bnm_desk_payment bnmd ON bnmd.id = p.id
38 LEFT JOIN money.account_adjustment maa ON maa.id = p.id;
40 ALTER TABLE money.aged_payment
41 ADD COLUMN accepting_usr INTEGER,
42 ADD COLUMN cash_drawer INTEGER,
43 ADD COLUMN billing BIGINT;
45 CREATE INDEX aged_payment_accepting_usr_idx ON money.aged_payment(accepting_usr);
46 CREATE INDEX aged_payment_cash_drawer_idx ON money.aged_payment(cash_drawer);
47 CREATE INDEX aged_payment_billing_idx ON money.aged_payment(billing);
49 CREATE OR REPLACE VIEW money.all_payments AS
50 SELECT * FROM money.payment_view_for_aging
52 SELECT * FROM money.aged_payment;
54 CREATE OR REPLACE FUNCTION money.age_billings_and_payments() RETURNS INTEGER AS $FUNC$
55 -- Age billings and payments linked to transactions which were
56 -- completed at least 'older_than' time ago.
59 counter INTEGER DEFAULT 0;
63 SELECT value::INTERVAL INTO keep_age FROM config.global_flag
64 WHERE name = 'history.money.retention_age' AND enabled;
66 -- Confirm interval-based aging is enabled.
67 IF keep_age IS NULL THEN RETURN counter; END IF;
69 -- Start with non-circulation transactions
70 FOR xact_id IN SELECT DISTINCT(xact.id) FROM money.billable_xact xact
71 -- confirm there is something to age
72 JOIN money.billing mb ON mb.xact = xact.id
73 -- Avoid aging money linked to non-aged circulations.
74 LEFT JOIN action.circulation circ ON circ.id = xact.id
75 WHERE circ.id IS NULL AND AGE(NOW(), xact.xact_finish) > keep_age LOOP
77 PERFORM money.age_billings_and_payments_for_xact(xact_id);
78 counter := counter + 1;
81 -- Then handle aged circulation money.
82 FOR xact_id IN SELECT DISTINCT(xact.id) FROM action.aged_circulation xact
83 -- confirm there is something to age
84 JOIN money.billing mb ON mb.xact = xact.id
85 WHERE AGE(NOW(), xact.xact_finish) > keep_age LOOP
87 PERFORM money.age_billings_and_payments_for_xact(xact_id);
88 counter := counter + 1;
93 $FUNC$ LANGUAGE PLPGSQL;
95 CREATE OR REPLACE FUNCTION money.age_billings_and_payments_for_xact
96 (xact_id BIGINT) RETURNS VOID AS $FUNC$
98 INSERT INTO money.aged_billing
99 SELECT * FROM money.billing WHERE xact = $1;
101 INSERT INTO money.aged_payment
102 SELECT * FROM money.payment_view_for_aging WHERE xact = xact_id;
104 DELETE FROM money.payment WHERE xact = $1;
105 DELETE FROM money.billing WHERE xact = $1;
109 CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$
114 -- If there are any renewals for this circulation, don't archive or delete
115 -- it yet. We'll do so later, when we archive and delete the renewals.
117 SELECT 'Y' INTO found
118 FROM action.circulation
119 WHERE parent_circ = OLD.id
123 RETURN NULL; -- don't delete
126 -- Archive a copy of the old row to action.aged_circulation
128 INSERT INTO action.aged_circulation
129 (id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
130 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
131 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
132 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
133 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
134 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ,
135 auto_renewal, auto_renewal_remaining)
137 id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
138 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
139 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
140 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
141 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
142 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ,
143 auto_renewal, auto_renewal_remaining
144 FROM action.all_circulation WHERE id = OLD.id;
146 -- Migrate billings and payments to aged tables
148 SELECT 'Y' INTO found FROM config.global_flag
149 WHERE name = 'history.money.age_with_circs' AND enabled;
152 PERFORM money.age_billings_and_payments_for_xact(OLD.id);
157 $$ LANGUAGE 'plpgsql';