3 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0797', :eg_version); -- tsbere/Dyrcona/dbwells
5 -- New global flags for the purge function
6 INSERT INTO config.global_flag (name, label, enabled)
8 'history.hold.retention_age',
9 oils_i18n_gettext('history.hold.retention_age', 'Historical Hold Retention Age', 'cgf', 'label'),
12 'history.hold.retention_age_fulfilled',
13 oils_i18n_gettext('history.hold.retention_age_fulfilled', 'Historical Hold Retention Age - Fulfilled', 'cgf', 'label'),
16 'history.hold.retention_age_canceled',
17 oils_i18n_gettext('history.hold.retention_age_canceled', 'Historical Hold Retention Age - Canceled (Default)', 'cgf', 'label'),
20 'history.hold.retention_age_canceled_1',
21 oils_i18n_gettext('history.hold.retention_age_canceled_1', 'Historical Hold Retention Age - Canceled (Untarged expiration)', 'cgf', 'label'),
24 'history.hold.retention_age_canceled_2',
25 oils_i18n_gettext('history.hold.retention_age_canceled_2', 'Historical Hold Retention Age - Canceled (Hold Shelf expiration)', 'cgf', 'label'),
28 'history.hold.retention_age_canceled_3',
29 oils_i18n_gettext('history.hold.retention_age_canceled_3', 'Historical Hold Retention Age - Canceled (Patron via phone)', 'cgf', 'label'),
32 'history.hold.retention_age_canceled_4',
33 oils_i18n_gettext('history.hold.retention_age_canceled_4', 'Historical Hold Retention Age - Canceled (Patron in person)', 'cgf', 'label'),
36 'history.hold.retention_age_canceled_5',
37 oils_i18n_gettext('history.hold.retention_age_canceled_5', 'Historical Hold Retention Age - Canceled (Staff forced)', 'cgf', 'label'),
40 'history.hold.retention_age_canceled_6',
41 oils_i18n_gettext('history.hold.retention_age_canceled_6', 'Historical Hold Retention Age - Canceled (Patron via OPAC)', 'cgf', 'label'),
45 CREATE OR REPLACE FUNCTION action.purge_holds() RETURNS INT AS $func$
53 user_start TIMESTAMPTZ;
58 SELECT INTO cgf_d value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age' AND enabled;
59 SELECT INTO cgf_f value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age_fulfilled' AND enabled;
60 SELECT INTO cgf_c value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age_canceled' AND enabled;
63 rank() OVER (PARTITION BY usr ORDER BY COALESCE(fulfillment_time, cancel_time) DESC),
64 cgf_cs.value::INTERVAL as cgf_cs,
67 action.hold_request ahr
68 LEFT JOIN config.global_flag cgf_cs ON (ahr.cancel_cause IS NOT NULL AND cgf_cs.name = 'history.hold.retention_age_canceled_' || ahr.cancel_cause AND cgf_cs.enabled)
70 (fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL)
72 IF prev_usr IS NULL OR prev_usr != current_hold.usr THEN
73 prev_usr := current_hold.usr;
74 SELECT INTO user_start oils_json_to_text(value)::TIMESTAMPTZ FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_start';
75 SELECT INTO user_age oils_json_to_text(value)::INTERVAL FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_age';
76 SELECT INTO user_count oils_json_to_text(value)::INT FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_count';
77 IF user_start IS NOT NULL THEN
78 user_age := LEAST(user_age, AGE(NOW(), user_start));
80 IF user_count IS NULL THEN
81 user_count := 1000; -- Assumption based on the user visible holds routine
84 -- Library keep age trumps user keep anything, for purposes of being able to hold on to things when staff canceled and such.
85 IF current_hold.fulfillment_time IS NOT NULL AND current_hold.fulfillment_time > NOW() - COALESCE(cgf_f, cgf_d) THEN
88 IF current_hold.cancel_time IS NOT NULL AND current_hold.cancel_time > NOW() - COALESCE(current_hold.cgf_cs, cgf_c, cgf_d) THEN
92 -- User keep age needs combining with count. If too old AND within the count, keep!
93 IF user_start IS NOT NULL AND COALESCE(current_hold.fulfillment_time, current_hold.cancel_time) > NOW() - user_age AND current_hold.rank <= user_count THEN
97 -- All checks should have passed, delete!
98 DELETE FROM action.hold_request WHERE id = current_hold.id;
99 purged_holds := purged_holds + 1;
103 $func$ LANGUAGE plpgsql;
105 CREATE OR REPLACE FUNCTION action.usr_visible_holds (usr_id INT) RETURNS SETOF action.hold_request AS $func$
107 h action.hold_request%ROWTYPE;
110 usr_view_count actor.usr_setting%ROWTYPE;
111 usr_view_age actor.usr_setting%ROWTYPE;
112 usr_view_start actor.usr_setting%ROWTYPE;
114 SELECT * INTO usr_view_count FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_count';
115 SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_age';
116 SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_start';
120 FROM action.hold_request
122 AND fulfillment_time IS NULL
123 AND cancel_time IS NULL
124 ORDER BY request_time DESC
129 IF usr_view_start.value IS NULL THEN
133 IF usr_view_age.value IS NOT NULL THEN
134 -- User opted in and supplied a retention age
135 IF oils_json_to_text(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ) THEN
136 view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
138 view_age := oils_json_to_text(usr_view_age.value)::INTERVAL;
142 view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
145 IF usr_view_count.value IS NOT NULL THEN
146 view_count := oils_json_to_text(usr_view_count.value)::INT;
151 -- show some fulfilled/canceled holds
154 FROM action.hold_request
156 AND ( fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL )
157 AND COALESCE(fulfillment_time, cancel_time) > NOW() - view_age
158 ORDER BY COALESCE(fulfillment_time, cancel_time) DESC
166 $func$ LANGUAGE PLPGSQL;
168 CREATE TABLE action.aged_hold_request (
170 usr_home_ou INT NOT NULL,
171 usr_profile INT NOT NULL,
173 staff_placed BOOLEAN NOT NULL,
174 LIKE action.hold_request
176 ALTER TABLE action.aged_hold_request
177 ADD PRIMARY KEY (id),
179 DROP COLUMN requestor,
180 DROP COLUMN sms_carrier,
181 ALTER COLUMN phone_notify TYPE BOOLEAN
182 USING CASE WHEN phone_notify IS NULL OR phone_notify = '' THEN FALSE ELSE TRUE END,
183 ALTER COLUMN sms_notify TYPE BOOLEAN
184 USING CASE WHEN sms_notify IS NULL OR sms_notify = '' THEN FALSE ELSE TRUE END,
185 ALTER COLUMN phone_notify SET NOT NULL,
186 ALTER COLUMN sms_notify SET NOT NULL;
187 CREATE INDEX aged_hold_request_target_idx ON action.aged_hold_request (target);
188 CREATE INDEX aged_hold_request_pickup_lib_idx ON action.aged_hold_request (pickup_lib);
189 CREATE INDEX aged_hold_request_current_copy_idx ON action.aged_hold_request (current_copy);
190 CREATE INDEX aged_hold_request_fulfillment_staff_idx ON action.aged_hold_request ( fulfillment_staff );
192 CREATE OR REPLACE VIEW action.all_hold_request AS
194 COALESCE(a.post_code, b.post_code) AS usr_post_code,
195 p.home_ou AS usr_home_ou,
196 p.profile AS usr_profile,
197 EXTRACT(YEAR FROM p.dob)::INT AS usr_birth_year,
198 CAST(ahr.requestor <> ahr.usr AS BOOLEAN) AS staff_placed,
202 ahr.fulfillment_time,
212 ahr.fulfillment_staff,
219 ahr.holdable_formats,
221 WHEN ahr.phone_notify IS NULL THEN FALSE
222 WHEN ahr.phone_notify = '' THEN FALSE
227 WHEN ahr.sms_notify IS NULL THEN FALSE
228 WHEN ahr.sms_notify = '' THEN FALSE
236 ahr.shelf_expire_time,
237 ahr.current_shelf_lib
238 FROM action.hold_request ahr
239 JOIN actor.usr p ON (ahr.usr = p.id)
240 LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id)
241 LEFT JOIN actor.usr_address b ON (p.billing_address = b.id)
280 FROM action.aged_hold_request;
282 CREATE OR REPLACE FUNCTION action.age_hold_on_delete () RETURNS TRIGGER AS $$
285 -- Archive a copy of the old row to action.aged_hold_request
287 INSERT INTO action.aged_hold_request
361 FROM action.all_hold_request WHERE id = OLD.id;
365 $$ LANGUAGE 'plpgsql';
367 CREATE TRIGGER action_hold_request_aging_tgr
368 BEFORE DELETE ON action.hold_request
370 EXECUTE PROCEDURE action.age_hold_on_delete ();