Stamping upgrade script for lost items blocking
[working/Evergreen.git] / Open-ILS / src / sql / Pg / upgrade / 0794.add_option_for_including_lost_in_items_out_and_patron_blocking_by_lost.sql
1 BEGIN;
2
3 SELECT evergreen.upgrade_deps_block_check('0794', :eg_version);
4
5 INSERT INTO config.standing_penalty (id,name,label,block_list,staff_alert)
6     VALUES (5,'PATRON_EXCEEDS_LOST_COUNT',oils_i18n_gettext(5, 'Patron exceeds max lost item threshold', 'csp', 'label'),'CIRC|FULFILL|HOLD|CAPTURE|RENEW', TRUE);
7
8 INSERT INTO config.org_unit_setting_type ( name, grp, label, description, datatype ) VALUES (
9     'circ.tally_lost', 'circ',
10     oils_i18n_gettext(
11         'circ.tally_lost',
12         'Include Lost circulations in lump sum tallies in Patron Display.',
13         'coust',
14         'label'),
15     oils_i18n_gettext(
16         'circ.tally_lost',
17         'In the Patron Display interface, the number of total active circulations for a given patron is presented in the Summary sidebar and underneath the Items Out navigation button.  This setting will include Lost circulations as counting toward these tallies.',
18         'coust',
19         'description'),
20     'bool'
21 );
22
23 -- Function: actor.calculate_system_penalties(integer, integer)
24 -- DROP FUNCTION actor.calculate_system_penalties(integer, integer);
25
26 CREATE OR REPLACE FUNCTION actor.calculate_system_penalties(match_user integer, context_org integer)
27     RETURNS SETOF actor.usr_standing_penalty AS
28 $BODY$
29 DECLARE
30     user_object             actor.usr%ROWTYPE;
31     new_sp_row            actor.usr_standing_penalty%ROWTYPE;
32     existing_sp_row       actor.usr_standing_penalty%ROWTYPE;
33     collections_fines      permission.grp_penalty_threshold%ROWTYPE;
34     max_fines               permission.grp_penalty_threshold%ROWTYPE;
35     max_overdue           permission.grp_penalty_threshold%ROWTYPE;
36     max_items_out       permission.grp_penalty_threshold%ROWTYPE;
37     max_lost                         permission.grp_penalty_threshold%ROWTYPE;
38     tmp_grp                 INT;
39     items_overdue        INT;
40     items_out              INT;
41     items_lost              INT;
42     context_org_list     INT[];
43     current_fines          NUMERIC(8,2) := 0.0;
44     tmp_fines               NUMERIC(8,2);
45     tmp_groc               RECORD;
46     tmp_circ                RECORD;
47     tmp_org                actor.org_unit%ROWTYPE;
48     tmp_penalty          config.standing_penalty%ROWTYPE;
49     tmp_depth            INTEGER;
50 BEGIN
51     SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
52
53     -- Max fines
54     SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
55
56     -- Fail if the user has a high fine balance
57     LOOP
58         tmp_grp := user_object.profile;
59         LOOP
60             SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 1 AND org_unit = tmp_org.id;
61
62             IF max_fines.threshold IS NULL THEN
63                 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
64             ELSE
65                 EXIT;
66             END IF;
67
68             IF tmp_grp IS NULL THEN
69                 EXIT;
70             END IF;
71         END LOOP;
72
73         IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
74             EXIT;
75         END IF;
76
77         SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
78
79     END LOOP;
80
81     IF max_fines.threshold IS NOT NULL THEN
82
83         RETURN QUERY
84             SELECT  *
85               FROM  actor.usr_standing_penalty
86               WHERE usr = match_user
87                     AND org_unit = max_fines.org_unit
88                     AND (stop_date IS NULL or stop_date > NOW())
89                     AND standing_penalty = 1;
90
91         SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( max_fines.org_unit );
92
93         SELECT  SUM(f.balance_owed) INTO current_fines
94           FROM  money.materialized_billable_xact_summary f
95                 JOIN (
96                     SELECT  r.id
97                       FROM  booking.reservation r
98                       WHERE r.usr = match_user
99                             AND r.pickup_lib IN (SELECT * FROM unnest(context_org_list))
100                             AND xact_finish IS NULL
101                                 UNION ALL
102                     SELECT  g.id
103                       FROM  money.grocery g
104                       WHERE g.usr = match_user
105                             AND g.billing_location IN (SELECT * FROM unnest(context_org_list))
106                             AND xact_finish IS NULL
107                                 UNION ALL
108                     SELECT  circ.id
109                       FROM  action.circulation circ
110                       WHERE circ.usr = match_user
111                             AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
112                             AND xact_finish IS NULL ) l USING (id);
113
114         IF current_fines >= max_fines.threshold THEN
115             new_sp_row.usr := match_user;
116             new_sp_row.org_unit := max_fines.org_unit;
117             new_sp_row.standing_penalty := 1;
118             RETURN NEXT new_sp_row;
119         END IF;
120     END IF;
121
122     -- Start over for max overdue
123     SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
124
125     -- Fail if the user has too many overdue items
126     LOOP
127         tmp_grp := user_object.profile;
128         LOOP
129
130             SELECT * INTO max_overdue FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 2 AND org_unit = tmp_org.id;
131
132             IF max_overdue.threshold IS NULL THEN
133                 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
134             ELSE
135                 EXIT;
136             END IF;
137
138             IF tmp_grp IS NULL THEN
139                 EXIT;
140             END IF;
141         END LOOP;
142
143         IF max_overdue.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
144             EXIT;
145         END IF;
146
147         SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
148
149     END LOOP;
150
151     IF max_overdue.threshold IS NOT NULL THEN
152
153         RETURN QUERY
154             SELECT  *
155               FROM  actor.usr_standing_penalty
156               WHERE usr = match_user
157                     AND org_unit = max_overdue.org_unit
158                     AND (stop_date IS NULL or stop_date > NOW())
159                     AND standing_penalty = 2;
160
161         SELECT  INTO items_overdue COUNT(*)
162           FROM  action.circulation circ
163                 JOIN  actor.org_unit_full_path( max_overdue.org_unit ) fp ON (circ.circ_lib = fp.id)
164           WHERE circ.usr = match_user
165             AND circ.checkin_time IS NULL
166             AND circ.due_date < NOW()
167             AND (circ.stop_fines = 'MAXFINES' OR circ.stop_fines IS NULL);
168
169         IF items_overdue >= max_overdue.threshold::INT THEN
170             new_sp_row.usr := match_user;
171             new_sp_row.org_unit := max_overdue.org_unit;
172             new_sp_row.standing_penalty := 2;
173             RETURN NEXT new_sp_row;
174         END IF;
175     END IF;
176
177     -- Start over for max out
178     SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
179
180     -- Fail if the user has too many checked out items
181     LOOP
182         tmp_grp := user_object.profile;
183         LOOP
184             SELECT * INTO max_items_out FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 3 AND org_unit = tmp_org.id;
185
186             IF max_items_out.threshold IS NULL THEN
187                 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
188             ELSE
189                 EXIT;
190             END IF;
191
192             IF tmp_grp IS NULL THEN
193                 EXIT;
194             END IF;
195         END LOOP;
196
197         IF max_items_out.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
198             EXIT;
199         END IF;
200
201         SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
202
203     END LOOP;
204
205     -- Fail if the user has too many items checked out
206     IF max_items_out.threshold IS NOT NULL THEN
207         RETURN QUERY
208             SELECT  *
209             FROM  actor.usr_standing_penalty
210             WHERE usr = match_user
211                 AND org_unit = max_items_out.org_unit
212                 AND (stop_date IS NULL or stop_date > NOW())
213                 AND standing_penalty = 3;
214             SELECT  INTO items_out COUNT(*)
215             FROM  action.circulation circ
216             JOIN  actor.org_unit_full_path( max_items_out.org_unit ) fp ON (circ.circ_lib = fp.id)
217             WHERE circ.usr = match_user
218                 AND circ.checkin_time IS NULL
219                 AND (circ.stop_fines IN (
220                     SELECT 'MAXFINES'::TEXT
221                     UNION ALL
222                     SELECT 'LONGOVERDUE'::TEXT
223                     UNION ALL
224                     SELECT 'LOST'::TEXT
225                     WHERE 'true' ILIKE
226                     (
227                         SELECT CASE
228                             WHEN (SELECT value FROM actor.org_unit_ancestor_setting('circ.tally_lost', circ.circ_lib)) ILIKE 'true' THEN 'true'
229                             ELSE 'false'
230                         END
231                     )
232                     UNION ALL
233                     SELECT 'CLAIMSRETURNED'::TEXT
234                     WHERE 'false' ILIKE
235                         (
236                             SELECT CASE
237                             WHEN (SELECT value FROM actor.org_unit_ancestor_setting('circ.do_not_tally_claims_returned', circ.circ_lib)) ILIKE 'true' THEN 'true'
238                             ELSE 'false'
239                             END
240                         )
241                     ) OR circ.stop_fines IS NULL)
242                 AND xact_finish IS NULL;
243
244     IF items_out >= max_items_out.threshold::INT THEN
245         new_sp_row.usr := match_user;
246         new_sp_row.org_unit := max_items_out.org_unit;
247         new_sp_row.standing_penalty := 3;
248         RETURN NEXT new_sp_row;
249    END IF;
250 END IF;
251
252     -- Start over for max lost
253     SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
254
255     -- Fail if the user has too many lost items
256     LOOP
257         tmp_grp := user_object.profile;
258         LOOP
259             SELECT * INTO max_lost FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 5 AND org_unit = tmp_org.id;
260             IF max_lost.threshold IS NULL THEN
261                 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
262             ELSE
263                 EXIT;
264             END IF;
265
266             IF tmp_grp IS NULL THEN
267                 EXIT;
268             END IF;
269         END LOOP;
270
271         IF max_lost.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
272             EXIT;
273         END IF;
274
275         SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
276
277     END LOOP;
278
279     IF max_lost.threshold IS NOT NULL THEN
280         RETURN QUERY
281             SELECT  *
282             FROM  actor.usr_standing_penalty
283             WHERE usr = match_user
284                 AND org_unit = max_lost.org_unit
285                 AND (stop_date IS NULL or stop_date > NOW())
286                 AND standing_penalty = 5;
287
288         SELECT INTO items_lost COUNT(*)
289         FROM  action.circulation circ
290             JOIN  actor.org_unit_full_path( max_lost.org_unit ) fp ON (circ.circ_lib = fp.id)
291             WHERE circ.usr = match_user
292                 AND circ.checkin_time IS NULL
293                 AND (circ.stop_fines = 'LOST')
294                 AND xact_finish IS NULL;
295
296         IF items_lost >= max_lost.threshold::INT AND 0 < max_lost.threshold::INT THEN
297             new_sp_row.usr := match_user;
298             new_sp_row.org_unit := max_lost.org_unit;
299             new_sp_row.standing_penalty := 5;
300             RETURN NEXT new_sp_row;
301         END IF;
302     END IF;
303
304     -- Start over for collections warning
305     SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
306
307     -- Fail if the user has a collections-level fine balance
308     LOOP
309         tmp_grp := user_object.profile;
310         LOOP
311             SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 4 AND org_unit = tmp_org.id;
312             IF max_fines.threshold IS NULL THEN
313                 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
314             ELSE
315                 EXIT;
316             END IF;
317
318             IF tmp_grp IS NULL THEN
319                 EXIT;
320             END IF;
321         END LOOP;
322
323         IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
324             EXIT;
325         END IF;
326
327         SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
328
329     END LOOP;
330
331     IF max_fines.threshold IS NOT NULL THEN
332
333         RETURN QUERY
334             SELECT  *
335                 FROM  actor.usr_standing_penalty
336                 WHERE usr = match_user
337                     AND org_unit = max_fines.org_unit
338                     AND (stop_date IS NULL or stop_date > NOW())
339                     AND standing_penalty = 4;
340
341         SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( max_fines.org_unit );
342
343         SELECT  SUM(f.balance_owed) INTO current_fines
344             FROM  money.materialized_billable_xact_summary f
345                 JOIN (
346                     SELECT  r.id
347                         FROM  booking.reservation r
348                         WHERE r.usr = match_user
349                             AND r.pickup_lib IN (SELECT * FROM unnest(context_org_list))
350                             AND r.xact_finish IS NULL
351                                 UNION ALL
352                     SELECT  g.id
353                         FROM  money.grocery g
354                         WHERE g.usr = match_user
355                             AND g.billing_location IN (SELECT * FROM unnest(context_org_list))
356                             AND g.xact_finish IS NULL
357                                 UNION ALL
358                     SELECT  circ.id
359                         FROM  action.circulation circ
360                         WHERE circ.usr = match_user
361                             AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
362                             AND circ.xact_finish IS NULL ) l USING (id);
363
364         IF current_fines >= max_fines.threshold THEN
365             new_sp_row.usr := match_user;
366             new_sp_row.org_unit := max_fines.org_unit;
367             new_sp_row.standing_penalty := 4;
368             RETURN NEXT new_sp_row;
369         END IF;
370     END IF;
371
372     -- Start over for in collections
373     SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
374
375     -- Remove the in-collections penalty if the user has paid down enough
376     -- This penalty is different, because this code is not responsible for creating 
377     -- new in-collections penalties, only for removing them
378     LOOP
379         tmp_grp := user_object.profile;
380         LOOP
381             SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 30 AND org_unit = tmp_org.id;
382
383             IF max_fines.threshold IS NULL THEN
384                 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
385             ELSE
386                 EXIT;
387             END IF;
388
389             IF tmp_grp IS NULL THEN
390                 EXIT;
391             END IF;
392         END LOOP;
393
394         IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
395             EXIT;
396         END IF;
397
398         SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
399
400     END LOOP;
401
402     IF max_fines.threshold IS NOT NULL THEN
403
404         SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( max_fines.org_unit );
405
406         -- first, see if the user had paid down to the threshold
407         SELECT  SUM(f.balance_owed) INTO current_fines
408           FROM  money.materialized_billable_xact_summary f
409                 JOIN (
410                     SELECT  r.id
411                         FROM  booking.reservation r
412                         WHERE r.usr = match_user
413                             AND r.pickup_lib IN (SELECT * FROM unnest(context_org_list))
414                             AND r.xact_finish IS NULL
415                                 UNION ALL
416                     SELECT  g.id
417                         FROM  money.grocery g
418                         WHERE g.usr = match_user
419                             AND g.billing_location IN (SELECT * FROM unnest(context_org_list))
420                             AND g.xact_finish IS NULL
421                                 UNION ALL
422                     SELECT  circ.id
423                         FROM  action.circulation circ
424                         WHERE circ.usr = match_user
425                             AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
426                             AND circ.xact_finish IS NULL ) l USING (id);
427
428         IF current_fines IS NULL OR current_fines <= max_fines.threshold THEN
429             -- patron has paid down enough
430
431             SELECT INTO tmp_penalty * FROM config.standing_penalty WHERE id = 30;
432
433             IF tmp_penalty.org_depth IS NOT NULL THEN
434
435                 -- since this code is not responsible for applying the penalty, it can't 
436                 -- guarantee the current context org will match the org at which the penalty 
437                 --- was applied.  search up the org tree until we hit the configured penalty depth
438                 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
439                 SELECT INTO tmp_depth depth FROM actor.org_unit_type WHERE id = tmp_org.ou_type;
440
441                 WHILE tmp_depth >= tmp_penalty.org_depth LOOP
442
443                     RETURN QUERY
444                         SELECT  *
445                             FROM  actor.usr_standing_penalty
446                             WHERE usr = match_user
447                                 AND org_unit = tmp_org.id
448                                 AND (stop_date IS NULL or stop_date > NOW())
449                                 AND standing_penalty = 30;
450
451                     IF tmp_org.parent_ou IS NULL THEN
452                         EXIT;
453                     END IF;
454
455                     SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
456                     SELECT INTO tmp_depth depth FROM actor.org_unit_type WHERE id = tmp_org.ou_type;
457                 END LOOP;
458
459             ELSE
460
461                 -- no penalty depth is defined, look for exact matches
462
463                 RETURN QUERY
464                     SELECT  *
465                         FROM  actor.usr_standing_penalty
466                         WHERE usr = match_user
467                             AND org_unit = max_fines.org_unit
468                             AND (stop_date IS NULL or stop_date > NOW())
469                             AND standing_penalty = 30;
470             END IF;
471
472         END IF;
473
474     END IF;
475
476     RETURN;
477 END;
478 $BODY$
479     LANGUAGE plpgsql VOLATILE
480     COST 100
481     ROWS 1000;
482
483 COMMIT;