3a6714cfd621bc1d6f8d863ec88b5744b0b0bc93
[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, label, enabled)
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;
101
102 CREATE OR REPLACE FUNCTION action.usr_visible_holds (usr_id INT) RETURNS SETOF action.hold_request AS $func$
103 DECLARE
104     h               action.hold_request%ROWTYPE;
105     view_age        INTERVAL;
106     view_count      INT;
107     usr_view_count  actor.usr_setting%ROWTYPE;
108     usr_view_age    actor.usr_setting%ROWTYPE;
109     usr_view_start  actor.usr_setting%ROWTYPE;
110 BEGIN
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';
114
115     FOR h IN
116         SELECT  *
117           FROM  action.hold_request
118           WHERE usr = usr_id
119                 AND fulfillment_time IS NULL
120                 AND cancel_time IS NULL
121           ORDER BY request_time DESC
122     LOOP
123         RETURN NEXT h;
124     END LOOP;
125
126     IF usr_view_start.value IS NULL THEN
127         RETURN;
128     END IF;
129
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);
134         ELSE
135             view_age := oils_json_to_text(usr_view_age.value)::INTERVAL;
136         END IF;
137     ELSE
138         -- User opted in
139         view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
140     END IF;
141
142     IF usr_view_count.value IS NOT NULL THEN
143         view_count := oils_json_to_text(usr_view_count.value)::INT;
144     ELSE
145         view_count := 1000;
146     END IF;
147
148     -- show some fulfilled/canceled holds
149     FOR h IN
150         SELECT  *
151           FROM  action.hold_request
152           WHERE usr = usr_id
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
156           LIMIT view_count
157     LOOP
158         RETURN NEXT h;
159     END LOOP;
160
161     RETURN;
162 END;
163 $func$ LANGUAGE PLPGSQL;