2 -- New global flags for the purge function
3 INSERT INTO config.global_flag (name, enabled, label)
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.retenetion_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;