LP#1527342 Patron checkout history SQL/IDL
[working/Evergreen.git] / Open-ILS / src / sql / Pg / upgrade / XXXX.schema.decouple_co_history.sql
1
2 BEGIN;
3
4 -- TODO process to delete history items once the age threshold 
5 -- history.circ.retention_age is reached?
6
7 -- SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); 
8
9 CREATE TABLE action.usr_circ_history (
10     id           BIGSERIAL PRIMARY KEY,
11     usr          INTEGER NOT NULL REFERENCES actor.usr(id)
12                  DEFERRABLE INITIALLY DEFERRED,
13     xact_start   TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
14     target_copy  BIGINT NOT NULL REFERENCES asset.copy(id)
15                  DEFERRABLE INITIALLY DEFERRED,
16     due_date     TIMESTAMP WITH TIME ZONE NOT NULL,
17     checkin_time TIMESTAMP WITH TIME ZONE,
18     source_circ  BIGINT REFERENCES action.circulation(id)
19                  ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
20 );
21
22 CREATE OR REPLACE FUNCTION action.maintain_usr_circ_history() 
23     RETURNS TRIGGER AS $FUNK$
24 DECLARE
25     cur_circ  BIGINT;
26     first_circ BIGINT;
27 BEGIN                                                                          
28
29     -- Any retention value signifies history is enabled.
30     -- This assumes that clearing these values via external 
31     -- process deletes the action.usr_circ_history rows.
32     -- TODO: replace these settings w/ a single bool setting?
33     PERFORM 1 FROM actor.usr_setting 
34         WHERE usr = NEW.usr AND value IS NOT NULL AND name IN (
35             'history.circ.retention_age', 
36             'history.circ.retention_start'
37         );
38
39     IF NOT FOUND THEN
40         RETURN NEW;
41     END IF;
42
43     IF TG_OP = 'INSERT' AND NEW.parent_circ IS NULL THEN
44         -- Starting a new circulation.  Insert the history row.
45         INSERT INTO action.usr_circ_history 
46             (usr, xact_start, target_copy, due_date, source_circ)
47         VALUES (
48             NEW.usr, 
49             NEW.xact_start, 
50             NEW.target_copy, 
51             NEW.due_date, 
52             NEW.id
53         );
54
55         RETURN NEW;
56     END IF;
57
58     -- find the first and last circs in the circ chain 
59     -- for the currently modified circ.
60     FOR cur_circ IN SELECT id FROM action.circ_chain(NEW.id) LOOP
61         IF first_circ IS NULL THEN
62             first_circ := cur_circ;
63             CONTINUE;
64         END IF;
65         -- Allow the loop to continue so that at as the loop
66         -- completes cur_circ points to the final circulation.
67     END LOOP;
68
69     IF NEW.id <> cur_circ THEN
70         -- Modifying an intermediate circ.  Ignore it.
71         RETURN NEW;
72     END IF;
73
74     -- Update the due_date/checkin_time on the history row if the current 
75     -- circ is the last circ in the chain and an update is warranted.
76
77     UPDATE action.usr_circ_history 
78         SET 
79             due_date = NEW.due_date,
80             checkin_time = NEW.checkin_time
81         WHERE 
82             source_circ = first_circ 
83             AND (
84                 due_date <> NEW.due_date OR (
85                     (checkin_time IS NULL AND NEW.checkin_time IS NOT NULL) OR
86                     (checkin_time IS NOT NULL AND NEW.checkin_time IS NULL) OR
87                     (checkin_time <> NEW.checkin_time)
88                 )
89             );
90     RETURN NEW;
91 END;                                                                           
92 $FUNK$ LANGUAGE PLPGSQL; 
93
94 CREATE TRIGGER maintain_usr_circ_history_tgr 
95     AFTER INSERT OR UPDATE ON action.circulation 
96     FOR EACH ROW EXECUTE PROCEDURE action.maintain_usr_circ_history();
97
98 UPDATE action_trigger.hook 
99     SET core_type = 'auch' 
100     WHERE key ~ '^circ.format.history.'; 
101
102 UPDATE action_trigger.event_definition SET template = 
103 $$
104 [%- USE date -%]
105 [%- SET user = target.0.usr -%]
106 To: [%- params.recipient_email || user.email %]
107 From: [%- params.sender_email || default_sender %]
108 Subject: Circulation History
109
110     [% FOR circ IN target %]
111             [% helpers.get_copy_bib_basics(circ.target_copy.id).title %]
112             Barcode: [% circ.target_copy.barcode %]
113             Checked Out: [% date.format(helpers.format_date(circ.xact_start), '%Y-%m-%d') %]
114             Due Date: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %]
115             Returned: [%
116                 date.format(
117                     helpers.format_date(circ.checkin_time), '%Y-%m-%d') 
118                     IF circ.checkin_time; 
119             %]
120     [% END %]
121 $$
122 WHERE id = 25 AND template = 
123 $$
124 [%- USE date -%]
125 [%- SET user = target.0.usr -%]
126 To: [%- params.recipient_email || user.email %]
127 From: [%- params.sender_email || default_sender %]
128 Subject: Circulation History
129
130     [% FOR circ IN target %]
131             [% helpers.get_copy_bib_basics(circ.target_copy.id).title %]
132             Barcode: [% circ.target_copy.barcode %]
133             Checked Out: [% date.format(helpers.format_date(circ.xact_start), '%Y-%m-%d') %]
134             Due Date: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %]
135             Returned: [% date.format(helpers.format_date(circ.checkin_time), '%Y-%m-%d') %]
136     [% END %]
137 $$;
138
139 -- avoid TT undef date errors
140 UPDATE action_trigger.event_definition SET template = 
141 $$
142 [%- USE date -%]
143 <div>
144     <style> li { padding: 8px; margin 5px; }</style>
145     <div>[% date.format %]</div>
146     <br/>
147
148     [% user.family_name %], [% user.first_given_name %]
149     <ol>
150     [% FOR circ IN target %]
151         <li>
152             <div>[% helpers.get_copy_bib_basics(circ.target_copy.id).title %]</div>
153             <div>Barcode: [% circ.target_copy.barcode %]</div>
154             <div>Checked Out: [% date.format(helpers.format_date(circ.xact_start), '%Y-%m-%d') %]</div>
155             <div>Due Date: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %]</div>
156             <div>Returned: [%
157                 date.format(
158                     helpers.format_date(circ.checkin_time), '%Y-%m-%d') 
159                     IF circ.checkin_time; -%]
160             </div>
161         </li>
162     [% END %]
163     </ol>
164 </div>
165 $$
166 WHERE id = 26 AND template = -- only replace template if it matches stock
167 $$
168 [%- USE date -%]
169 <div>
170     <style> li { padding: 8px; margin 5px; }</style>
171     <div>[% date.format %]</div>
172     <br/>
173
174     [% user.family_name %], [% user.first_given_name %]
175     <ol>
176     [% FOR circ IN target %]
177         <li>
178             <div>[% helpers.get_copy_bib_basics(circ.target_copy.id).title %]</div>
179             <div>Barcode: [% circ.target_copy.barcode %]</div>
180             <div>Checked Out: [% date.format(helpers.format_date(circ.xact_start), '%Y-%m-%d') %]</div>
181             <div>Due Date: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %]</div>
182             <div>Returned: [% date.format(helpers.format_date(circ.checkin_time), '%Y-%m-%d') %]</div>
183         </li>
184     [% END %]
185     </ol>
186 </div>
187 $$;
188
189 -- NOTE: ^-- stock CSV template does not include checkin_time, so 
190 -- no modifications are required.
191
192 -- Create circ history rows for existing circ history data.
193 DO $FUNK$
194 DECLARE
195     cur_usr   INTEGER;
196     cur_circ  action.circulation%ROWTYPE;
197     last_circ action.circulation%ROWTYPE;
198     counter   INTEGER DEFAULT 1;
199 BEGIN
200
201     RAISE NOTICE 
202         'Migrating circ history for % users.  This might take a while...',
203         (SELECT COUNT(DISTINCT(au.id)) FROM actor.usr au
204             JOIN actor.usr_setting aus ON (aus.usr = au.id)
205             WHERE NOT au.deleted AND 
206                 aus.name ~ '^history.circ.retention_');
207
208     FOR cur_usr IN 
209         SELECT DISTINCT(au.id)
210             FROM actor.usr au 
211             JOIN actor.usr_setting aus ON (aus.usr = au.id)
212             WHERE NOT au.deleted AND 
213                 aus.name ~ '^history.circ.retention_' LOOP
214
215         FOR cur_circ IN SELECT * FROM action.usr_visible_circs(cur_usr) LOOP
216
217             -- Find the last circ in the circ chain.
218             SELECT INTO last_circ * 
219                 FROM action.circ_chain(cur_circ.id) 
220                 ORDER BY xact_start DESC LIMIT 1;
221
222             -- Create the history row.
223             -- It's OK if last_circ = cur_circ
224             INSERT INTO action.usr_circ_history 
225                 (usr, xact_start, target_copy, 
226                     due_date, checkin_time, source_circ)
227             VALUES (
228                 cur_circ.usr, 
229                 cur_circ.xact_start, 
230                 cur_circ.target_copy, 
231                 last_circ.due_date, 
232                 last_circ.checkin_time,
233                 cur_circ.id
234             );
235
236             -- useful for alleviating administrator anxiety.
237             IF counter % 10000 = 0 THEN
238                 RAISE NOTICE 'Migrated history for % total users', counter;
239             END IF;
240
241             counter := counter + 1;
242
243         END LOOP;
244     END LOOP;
245
246 END $FUNK$;
247
248 DROP FUNCTION IF EXISTS action.usr_visible_circs (INTEGER);
249 DROP FUNCTION IF EXISTS action.usr_visible_circ_copies (INTEGER);
250
251 -- remove user retention age checks
252 CREATE OR REPLACE FUNCTION action.purge_circulations () RETURNS INT AS $func$
253 DECLARE
254     org_keep_age    INTERVAL;
255     org_use_last    BOOL = false;
256     org_age_is_min  BOOL = false;
257     org_keep_count  INT;
258
259     keep_age        INTERVAL;
260
261     target_acp      RECORD;
262     circ_chain_head action.circulation%ROWTYPE;
263     circ_chain_tail action.circulation%ROWTYPE;
264
265     count_purged    INT;
266     num_incomplete  INT;
267
268     last_finished   TIMESTAMP WITH TIME ZONE;
269 BEGIN
270
271     count_purged := 0;
272
273     SELECT value::INTERVAL INTO org_keep_age FROM config.global_flag WHERE name = 'history.circ.retention_age' AND enabled;
274
275     SELECT value::INT INTO org_keep_count FROM config.global_flag WHERE name = 'history.circ.retention_count' AND enabled;
276     IF org_keep_count IS NULL THEN
277         RETURN count_purged; -- Gimme a count to keep, or I keep them all, forever
278     END IF;
279
280     SELECT enabled INTO org_use_last FROM config.global_flag WHERE name = 'history.circ.retention_uses_last_finished';
281     SELECT enabled INTO org_age_is_min FROM config.global_flag WHERE name = 'history.circ.retention_age_is_min';
282
283     -- First, find copies with more than keep_count non-renewal circs
284     FOR target_acp IN
285         SELECT  target_copy,
286                 COUNT(*) AS total_real_circs
287           FROM  action.circulation
288           WHERE parent_circ IS NULL
289                 AND xact_finish IS NOT NULL
290           GROUP BY target_copy
291           HAVING COUNT(*) > org_keep_count
292     LOOP
293         -- And, for those, select circs that are finished and older than keep_age
294         FOR circ_chain_head IN
295             -- For reference, the subquery uses a window function to order the circs newest to oldest and number them
296             -- The outer query then uses that information to skip the most recent set the library wants to keep
297             -- End result is we don't care what order they come out in, as they are all potentials for deletion.
298             SELECT ac.* FROM action.circulation ac JOIN (
299               SELECT  rank() OVER (ORDER BY xact_start DESC), ac.id
300                 FROM  action.circulation ac
301                 WHERE ac.target_copy = target_acp.target_copy
302                   AND ac.parent_circ IS NULL
303                 ORDER BY ac.xact_start ) ranked USING (id)
304                 WHERE ranked.rank > org_keep_count
305         LOOP
306
307             SELECT * INTO circ_chain_tail FROM action.circ_chain(circ_chain_head.id) ORDER BY xact_start DESC LIMIT 1;
308             SELECT COUNT(CASE WHEN xact_finish IS NULL THEN 1 ELSE NULL END), MAX(xact_finish) INTO num_incomplete, last_finished FROM action.circ_chain(circ_chain_head.id);
309             CONTINUE WHEN circ_chain_tail.xact_finish IS NULL OR num_incomplete > 0;
310
311             IF NOT org_use_last THEN
312                 last_finished := circ_chain_tail.xact_finish;
313             END IF;
314
315             keep_age := COALESCE( org_keep_age, '2000 years'::INTERVAL );
316
317             IF org_age_is_min THEN
318                 keep_age := GREATEST( keep_age, org_keep_age );
319             END IF;
320
321             CONTINUE WHEN AGE(NOW(), last_finished) < keep_age;
322
323             -- We've passed the purging tests, purge the circ chain starting at the end
324             -- A trigger should auto-purge the rest of the chain.
325             DELETE FROM action.circulation WHERE id = circ_chain_tail.id;
326
327             count_purged := count_purged + 1;
328
329         END LOOP;
330     END LOOP;
331
332     return count_purged;
333 END;
334 $func$ LANGUAGE PLPGSQL;
335
336
337 COMMIT;
338