Add purge_holds DB function
[working/Evergreen.git] / Open-ILS / src / sql / Pg / upgrade / XXXX.purge_holds.sql
1
2 -- New global flags for the purge function
3 INSERT INTO config.global_flag  (name, enabled, label)
4     VALUES (
5         'history.hold.retention_age',
6         oils_i18n_gettext('history.hold.retention_age', 'Historical Hold Retention Age', 'cgf', 'label'),
7         TRUE
8     ),(
9         'history.hold.retention_age_fulfilled',
10         oils_i18n_gettext('history.hold.retention_age_fulfilled', 'Historical Hold Retention Age - Fulfilled', 'cgf', 'label'),
11         FALSE
12     ),(
13         'history.hold.retention_age_canceled',
14         oils_i18n_gettext('history.hold.retention_age_canceled', 'Historical Hold Retention Age - Canceled (Default)', 'cgf', 'label'),
15         FALSE
16     ),(
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'),
19         FALSE
20     ),(
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'),
23         FALSE
24     ),(
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'),
27         TRUE
28     ),(
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'),
31         TRUE
32     ),(
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'),
35         TRUE
36     ),(
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'),
39         FALSE
40     );
41
42 CREATE OR REPLACE FUNCTION action.purge_holds() RETURNS INT AS $func$
43 DECLARE
44   current_hold RECORD;
45   purged_holds INT;
46   cgf_d INTERVAL;
47   cgf_f INTERVAL;
48   cgf_c INTERVAL;
49   prev_usr INT;
50   user_start TIMESTAMPTZ;
51   user_age INTERVAL;
52   user_count INT;
53 BEGIN
54   purged_holds := 0;
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;
58   FOR current_hold IN
59     SELECT
60       rank() OVER (PARTITION BY usr ORDER BY COALESCE(fulfillment_time, cancel_time) DESC),
61       cgf_cs.value::INTERVAL as cgf_cs,
62       ahr.*
63     FROM
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)
66     WHERE
67       (fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL)
68   LOOP
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));
76       END IF;
77       IF user_count IS NULL THEN
78         user_count := 1000; -- Assumption based on the user visible holds routine
79       END IF;
80     END IF;
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
83       CONTINUE;
84     END IF;
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
86       CONTINUE;
87     END IF;
88
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
91       CONTINUE;
92     END IF;
93
94     -- All checks should have passed, delete!
95     DELETE FROM action.hold_request WHERE id = current_hold.id;
96     purged_holds := purged_holds + 1;
97   END LOOP;
98   RETURN purged_holds;
99 END;
100 $func$ LANGUAGE plpgsql;