2 -- New global flags for the purge function
3 INSERT INTO config.global_flag (name, label, enabled)
5 'history.hold.retention_age',
6 oils_i18n_gettext('history.hold.retention_age', 'Historical Hold Retention Age', 'cgf', 'label'),
9 'history.hold.retention_age_fulfilled',
10 oils_i18n_gettext('history.hold.retention_age_fulfilled', 'Historical Hold Retention Age - Fulfilled', 'cgf', 'label'),
13 'history.hold.retention_age_canceled',
14 oils_i18n_gettext('history.hold.retention_age_canceled', 'Historical Hold Retention Age - Canceled (Default)', 'cgf', 'label'),
17 'history.hold.retention_age_canceled_1',
18 oils_i18n_gettext('history.hold.retention_age_canceled_1', 'Historical Hold Retention Age - Canceled (Untarged expiration)', 'cgf', 'label'),
21 'history.hold.retention_age_canceled_2',
22 oils_i18n_gettext('history.hold.retention_age_canceled_2', 'Historical Hold Retention Age - Canceled (Hold Shelf expiration)', 'cgf', 'label'),
25 'history.hold.retention_age_canceled_3',
26 oils_i18n_gettext('history.hold.retention_age_canceled_3', 'Historical Hold Retention Age - Canceled (Patron via phone)', 'cgf', 'label'),
29 'history.hold.retention_age_canceled_4',
30 oils_i18n_gettext('history.hold.retention_age_canceled_4', 'Historical Hold Retention Age - Canceled (Patron in person)', 'cgf', 'label'),
33 'history.hold.retention_age_canceled_5',
34 oils_i18n_gettext('history.hold.retention_age_canceled_5', 'Historical Hold Retention Age - Canceled (Staff forced)', 'cgf', 'label'),
37 'history.hold.retention_age_canceled_6',
38 oils_i18n_gettext('history.hold.retention_age_canceled_6', 'Historical Hold Retention Age - Canceled (Patron via OPAC)', 'cgf', 'label'),
42 CREATE OR REPLACE FUNCTION action.purge_holds() RETURNS INT AS $func$
50 user_start TIMESTAMPTZ;
55 SELECT INTO cgf_d value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age' AND enabled;
56 SELECT INTO cgf_f value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age_fulfilled' AND enabled;
57 SELECT INTO cgf_c value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age_canceled' AND enabled;
60 rank() OVER (PARTITION BY usr ORDER BY COALESCE(fulfillment_time, cancel_time) DESC),
61 cgf_cs.value::INTERVAL as cgf_cs,
64 action.hold_request ahr
65 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)
67 (fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL)
69 IF prev_usr IS NULL OR prev_usr != current_hold.usr THEN
70 prev_usr := current_hold.usr;
71 SELECT INTO user_start oils_json_to_text(value)::TIMESTAMPTZ FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_start';
72 SELECT INTO user_age oils_json_to_text(value)::INTERVAL FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_age';
73 SELECT INTO user_count oils_json_to_text(value)::INT FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_count';
74 IF user_start IS NOT NULL THEN
75 user_age := LEAST(user_age, AGE(NOW(), user_start));
77 IF user_count IS NULL THEN
78 user_count := 1000; -- Assumption based on the user visible holds routine
81 -- Library keep age trumps user keep anything, for purposes of being able to hold on to things when staff canceled and such.
82 IF current_hold.fulfillment_time IS NOT NULL AND current_hold.fulfillment_time > NOW() - COALESCE(cgf_f, cgf_d) THEN
85 IF current_hold.cancel_time IS NOT NULL AND current_hold.cancel_time > NOW() - COALESCE(current_hold.cgf_cs, cgf_c, cgf_d) THEN
89 -- User keep age needs combining with count. If too old AND within the count, keep!
90 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
94 -- All checks should have passed, delete!
95 DELETE FROM action.hold_request WHERE id = current_hold.id;
96 purged_holds := purged_holds + 1;
100 $func$ LANGUAGE plpgsql;
102 CREATE OR REPLACE FUNCTION action.usr_visible_holds (usr_id INT) RETURNS SETOF action.hold_request AS $func$
104 h action.hold_request%ROWTYPE;
107 usr_view_count actor.usr_setting%ROWTYPE;
108 usr_view_age actor.usr_setting%ROWTYPE;
109 usr_view_start actor.usr_setting%ROWTYPE;
111 SELECT * INTO usr_view_count FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_count';
112 SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_age';
113 SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_start';
117 FROM action.hold_request
119 AND fulfillment_time IS NULL
120 AND cancel_time IS NULL
121 ORDER BY request_time DESC
126 IF usr_view_start.value IS NULL THEN
130 IF usr_view_age.value IS NOT NULL THEN
131 -- User opted in and supplied a retention age
132 IF oils_json_to_text(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ) THEN
133 view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
135 view_age := oils_json_to_text(usr_view_age.value)::INTERVAL;
139 view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
142 IF usr_view_count.value IS NOT NULL THEN
143 view_count := oils_json_to_text(usr_view_count.value)::INT;
148 -- show some fulfilled/canceled holds
151 FROM action.hold_request
153 AND ( fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL )
154 AND COALESCE(fulfillment_time, cancel_time) > NOW() - view_age
155 ORDER BY COALESCE(fulfillment_time, cancel_time) DESC
163 $func$ LANGUAGE PLPGSQL;
165 CREATE TABLE action.aged_hold_request (
167 usr_home_ou INT NOT NULL,
168 usr_profile INT NOT NULL,
170 staff_placed BOOLEAN NOT NULL,
171 LIKE action.hold_request
173 ALTER TABLE action.aged_hold_request
174 ADD PRIMARY KEY (id),
176 DROP COLUMN requestor,
177 DROP COLUMN sms_carrier,
178 ALTER COLUMN phone_notify TYPE BOOLEAN
179 USING CASE WHEN phone_notify IS NULL OR phone_notify = '' THEN FALSE ELSE TRUE END,
180 ALTER COLUMN sms_notify TYPE BOOLEAN
181 USING CASE WHEN sms_notify IS NULL OR sms_notify = '' THEN FALSE ELSE TRUE END,
182 ALTER COLUMN phone_notify SET NOT NULL,
183 ALTER COLUMN sms_notify SET NOT NULL;
184 CREATE INDEX aged_hold_request_target_idx ON action.aged_hold_request (target);
185 CREATE INDEX aged_hold_request_pickup_lib_idx ON action.aged_hold_request (pickup_lib);
186 CREATE INDEX aged_hold_request_current_copy_idx ON action.aged_hold_request (current_copy);
187 CREATE INDEX aged_hold_request_fulfillment_staff_idx ON action.aged_hold_request ( fulfillment_staff );
189 CREATE OR REPLACE VIEW action.all_hold_request AS
191 COALESCE(a.post_code, b.post_code) AS usr_post_code,
192 p.home_ou AS usr_home_ou,
193 p.profile AS usr_profile,
194 EXTRACT(YEAR FROM p.dob)::INT AS usr_birth_year,
195 CAST(ahr.requestor <> ahr.usr AS BOOLEAN) AS staff_placed,
199 ahr.fulfillment_time,
209 ahr.fulfillment_staff,
216 ahr.holdable_formats,
218 WHEN ahr.phone_notify IS NULL THEN FALSE
219 WHEN ahr.phone_notify = '' THEN FALSE
224 WHEN ahr.sms_notify IS NULL THEN FALSE
225 WHEN ahr.sms_notify = '' THEN FALSE
233 ahr.shelf_expire_time,
234 ahr.current_shelf_lib
235 FROM action.hold_request ahr
236 JOIN actor.usr p ON (ahr.usr = p.id)
237 LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id)
238 LEFT JOIN actor.usr_address b ON (p.billing_address = b.id)
277 FROM action.aged_hold_request;
279 CREATE OR REPLACE FUNCTION action.age_hold_on_delete () RETURNS TRIGGER AS $$
282 -- Archive a copy of the old row to action.aged_hold_request
284 INSERT INTO action.aged_hold_request
358 FROM action.all_hold_request WHERE id = OLD.id;
362 $$ LANGUAGE 'plpgsql';
364 CREATE TRIGGER action_hold_request_aging_tgr
365 BEFORE DELETE ON action.hold_request
367 EXECUTE PROCEDURE action.age_hold_on_delete ();