3 SELECT evergreen.upgrade_deps_block_check('0831', :eg_version);
5 -- TODO: check for penalty ID collision before master merge; affects
6 -- config.standing_penalty and actor.calculate_system_penalties
8 INSERT INTO config.standing_penalty
9 (id, name, label, block_list, staff_alert)
12 'PATRON_EXCEEDS_LONGOVERDUE_COUNT',
15 'Patron Exceeds Max Long-Overdue Threshold',
19 'CIRC|FULFILL|HOLD|CAPTURE|RENEW',
24 CREATE OR REPLACE FUNCTION actor.calculate_system_penalties( match_user INT, context_org INT ) RETURNS SETOF actor.usr_standing_penalty AS $func$
26 user_object actor.usr%ROWTYPE;
27 new_sp_row actor.usr_standing_penalty%ROWTYPE;
28 existing_sp_row actor.usr_standing_penalty%ROWTYPE;
29 collections_fines permission.grp_penalty_threshold%ROWTYPE;
30 max_fines permission.grp_penalty_threshold%ROWTYPE;
31 max_overdue permission.grp_penalty_threshold%ROWTYPE;
32 max_items_out permission.grp_penalty_threshold%ROWTYPE;
33 max_lost permission.grp_penalty_threshold%ROWTYPE;
34 max_longoverdue permission.grp_penalty_threshold%ROWTYPE;
39 items_longoverdue INT;
40 context_org_list INT[];
41 current_fines NUMERIC(8,2) := 0.0;
42 tmp_fines NUMERIC(8,2);
45 tmp_org actor.org_unit%ROWTYPE;
46 tmp_penalty config.standing_penalty%ROWTYPE;
49 SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
52 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
54 -- Fail if the user has a high fine balance
56 tmp_grp := user_object.profile;
58 SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 1 AND org_unit = tmp_org.id;
60 IF max_fines.threshold IS NULL THEN
61 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
66 IF tmp_grp IS NULL THEN
71 IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
75 SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
79 IF max_fines.threshold IS NOT NULL THEN
83 FROM actor.usr_standing_penalty
84 WHERE usr = match_user
85 AND org_unit = max_fines.org_unit
86 AND (stop_date IS NULL or stop_date > NOW())
87 AND standing_penalty = 1;
89 SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( max_fines.org_unit );
91 SELECT SUM(f.balance_owed) INTO current_fines
92 FROM money.materialized_billable_xact_summary f
95 FROM booking.reservation r
96 WHERE r.usr = match_user
97 AND r.pickup_lib IN (SELECT * FROM unnest(context_org_list))
98 AND xact_finish IS NULL
102 WHERE g.usr = match_user
103 AND g.billing_location IN (SELECT * FROM unnest(context_org_list))
104 AND xact_finish IS NULL
107 FROM action.circulation circ
108 WHERE circ.usr = match_user
109 AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
110 AND xact_finish IS NULL ) l USING (id);
112 IF current_fines >= max_fines.threshold THEN
113 new_sp_row.usr := match_user;
114 new_sp_row.org_unit := max_fines.org_unit;
115 new_sp_row.standing_penalty := 1;
116 RETURN NEXT new_sp_row;
120 -- Start over for max overdue
121 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
123 -- Fail if the user has too many overdue items
125 tmp_grp := user_object.profile;
128 SELECT * INTO max_overdue FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 2 AND org_unit = tmp_org.id;
130 IF max_overdue.threshold IS NULL THEN
131 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
136 IF tmp_grp IS NULL THEN
141 IF max_overdue.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
145 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
149 IF max_overdue.threshold IS NOT NULL THEN
153 FROM actor.usr_standing_penalty
154 WHERE usr = match_user
155 AND org_unit = max_overdue.org_unit
156 AND (stop_date IS NULL or stop_date > NOW())
157 AND standing_penalty = 2;
159 SELECT INTO items_overdue COUNT(*)
160 FROM action.circulation circ
161 JOIN actor.org_unit_full_path( max_overdue.org_unit ) fp ON (circ.circ_lib = fp.id)
162 WHERE circ.usr = match_user
163 AND circ.checkin_time IS NULL
164 AND circ.due_date < NOW()
165 AND (circ.stop_fines = 'MAXFINES' OR circ.stop_fines IS NULL);
167 IF items_overdue >= max_overdue.threshold::INT THEN
168 new_sp_row.usr := match_user;
169 new_sp_row.org_unit := max_overdue.org_unit;
170 new_sp_row.standing_penalty := 2;
171 RETURN NEXT new_sp_row;
175 -- Start over for max out
176 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
178 -- Fail if the user has too many checked out items
180 tmp_grp := user_object.profile;
182 SELECT * INTO max_items_out FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 3 AND org_unit = tmp_org.id;
184 IF max_items_out.threshold IS NULL THEN
185 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
190 IF tmp_grp IS NULL THEN
195 IF max_items_out.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
199 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
204 -- Fail if the user has too many items checked out
205 IF max_items_out.threshold IS NOT NULL THEN
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;
215 SELECT INTO items_out COUNT(*)
216 FROM action.circulation circ
217 JOIN actor.org_unit_full_path( max_items_out.org_unit ) fp ON (circ.circ_lib = fp.id)
218 WHERE circ.usr = match_user
219 AND circ.checkin_time IS NULL
220 AND (circ.stop_fines IN (
221 SELECT 'MAXFINES'::TEXT
223 SELECT 'LONGOVERDUE'::TEXT
229 WHEN (SELECT value FROM actor.org_unit_ancestor_setting('circ.tally_lost', circ.circ_lib)) ILIKE 'true' THEN 'true'
234 SELECT 'CLAIMSRETURNED'::TEXT
238 WHEN (SELECT value FROM actor.org_unit_ancestor_setting('circ.do_not_tally_claims_returned', circ.circ_lib)) ILIKE 'true' THEN 'true'
242 ) OR circ.stop_fines IS NULL)
243 AND xact_finish IS NULL;
245 IF items_out >= max_items_out.threshold::INT THEN
246 new_sp_row.usr := match_user;
247 new_sp_row.org_unit := max_items_out.org_unit;
248 new_sp_row.standing_penalty := 3;
249 RETURN NEXT new_sp_row;
253 -- Start over for max lost
254 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
256 -- Fail if the user has too many lost items
258 tmp_grp := user_object.profile;
261 SELECT * INTO max_lost FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 5 AND org_unit = tmp_org.id;
263 IF max_lost.threshold IS NULL THEN
264 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
269 IF tmp_grp IS NULL THEN
274 IF max_lost.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
278 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
282 IF max_lost.threshold IS NOT NULL THEN
286 FROM actor.usr_standing_penalty
287 WHERE usr = match_user
288 AND org_unit = max_lost.org_unit
289 AND (stop_date IS NULL or stop_date > NOW())
290 AND standing_penalty = 5;
292 SELECT INTO items_lost COUNT(*)
293 FROM action.circulation circ
294 JOIN actor.org_unit_full_path( max_lost.org_unit ) fp ON (circ.circ_lib = fp.id)
295 WHERE circ.usr = match_user
296 AND circ.checkin_time IS NULL
297 AND (circ.stop_fines = 'LOST')
298 AND xact_finish IS NULL;
300 IF items_lost >= max_lost.threshold::INT AND 0 < max_lost.threshold::INT THEN
301 new_sp_row.usr := match_user;
302 new_sp_row.org_unit := max_lost.org_unit;
303 new_sp_row.standing_penalty := 5;
304 RETURN NEXT new_sp_row;
308 -- Start over for max longoverdue
309 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
311 -- Fail if the user has too many longoverdue items
313 tmp_grp := user_object.profile;
316 SELECT * INTO max_longoverdue
317 FROM permission.grp_penalty_threshold
318 WHERE grp = tmp_grp AND
320 org_unit = tmp_org.id;
322 IF max_longoverdue.threshold IS NULL THEN
323 SELECT parent INTO tmp_grp
324 FROM permission.grp_tree WHERE id = tmp_grp;
329 IF tmp_grp IS NULL THEN
334 IF max_longoverdue.threshold IS NOT NULL
335 OR tmp_org.parent_ou IS NULL THEN
339 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
343 IF max_longoverdue.threshold IS NOT NULL THEN
347 FROM actor.usr_standing_penalty
348 WHERE usr = match_user
349 AND org_unit = max_longoverdue.org_unit
350 AND (stop_date IS NULL or stop_date > NOW())
351 AND standing_penalty = 35;
353 SELECT INTO items_longoverdue COUNT(*)
354 FROM action.circulation circ
355 JOIN actor.org_unit_full_path( max_longoverdue.org_unit ) fp
356 ON (circ.circ_lib = fp.id)
357 WHERE circ.usr = match_user
358 AND circ.checkin_time IS NULL
359 AND (circ.stop_fines = 'LONGOVERDUE')
360 AND xact_finish IS NULL;
362 IF items_longoverdue >= max_longoverdue.threshold::INT
363 AND 0 < max_longoverdue.threshold::INT THEN
364 new_sp_row.usr := match_user;
365 new_sp_row.org_unit := max_longoverdue.org_unit;
366 new_sp_row.standing_penalty := 35;
367 RETURN NEXT new_sp_row;
372 -- Start over for collections warning
373 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
375 -- Fail if the user has a collections-level fine balance
377 tmp_grp := user_object.profile;
379 SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 4 AND org_unit = tmp_org.id;
381 IF max_fines.threshold IS NULL THEN
382 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
387 IF tmp_grp IS NULL THEN
392 IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
396 SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
400 IF max_fines.threshold IS NOT NULL THEN
404 FROM actor.usr_standing_penalty
405 WHERE usr = match_user
406 AND org_unit = max_fines.org_unit
407 AND (stop_date IS NULL or stop_date > NOW())
408 AND standing_penalty = 4;
410 SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( max_fines.org_unit );
412 SELECT SUM(f.balance_owed) INTO current_fines
413 FROM money.materialized_billable_xact_summary f
416 FROM booking.reservation r
417 WHERE r.usr = match_user
418 AND r.pickup_lib IN (SELECT * FROM unnest(context_org_list))
419 AND r.xact_finish IS NULL
423 WHERE g.usr = match_user
424 AND g.billing_location IN (SELECT * FROM unnest(context_org_list))
425 AND g.xact_finish IS NULL
428 FROM action.circulation circ
429 WHERE circ.usr = match_user
430 AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
431 AND circ.xact_finish IS NULL ) l USING (id);
433 IF current_fines >= max_fines.threshold THEN
434 new_sp_row.usr := match_user;
435 new_sp_row.org_unit := max_fines.org_unit;
436 new_sp_row.standing_penalty := 4;
437 RETURN NEXT new_sp_row;
441 -- Start over for in collections
442 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
444 -- Remove the in-collections penalty if the user has paid down enough
445 -- This penalty is different, because this code is not responsible for creating
446 -- new in-collections penalties, only for removing them
448 tmp_grp := user_object.profile;
450 SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 30 AND org_unit = tmp_org.id;
452 IF max_fines.threshold IS NULL THEN
453 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
458 IF tmp_grp IS NULL THEN
463 IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
467 SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
471 IF max_fines.threshold IS NOT NULL THEN
473 SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( max_fines.org_unit );
475 -- first, see if the user had paid down to the threshold
476 SELECT SUM(f.balance_owed) INTO current_fines
477 FROM money.materialized_billable_xact_summary f
480 FROM booking.reservation r
481 WHERE r.usr = match_user
482 AND r.pickup_lib IN (SELECT * FROM unnest(context_org_list))
483 AND r.xact_finish IS NULL
487 WHERE g.usr = match_user
488 AND g.billing_location IN (SELECT * FROM unnest(context_org_list))
489 AND g.xact_finish IS NULL
492 FROM action.circulation circ
493 WHERE circ.usr = match_user
494 AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
495 AND circ.xact_finish IS NULL ) l USING (id);
497 IF current_fines IS NULL OR current_fines <= max_fines.threshold THEN
498 -- patron has paid down enough
500 SELECT INTO tmp_penalty * FROM config.standing_penalty WHERE id = 30;
502 IF tmp_penalty.org_depth IS NOT NULL THEN
504 -- since this code is not responsible for applying the penalty, it can't
505 -- guarantee the current context org will match the org at which the penalty
506 --- was applied. search up the org tree until we hit the configured penalty depth
507 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
508 SELECT INTO tmp_depth depth FROM actor.org_unit_type WHERE id = tmp_org.ou_type;
510 WHILE tmp_depth >= tmp_penalty.org_depth LOOP
514 FROM actor.usr_standing_penalty
515 WHERE usr = match_user
516 AND org_unit = tmp_org.id
517 AND (stop_date IS NULL or stop_date > NOW())
518 AND standing_penalty = 30;
520 IF tmp_org.parent_ou IS NULL THEN
524 SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
525 SELECT INTO tmp_depth depth FROM actor.org_unit_type WHERE id = tmp_org.ou_type;
530 -- no penalty depth is defined, look for exact matches
534 FROM actor.usr_standing_penalty
535 WHERE usr = match_user
536 AND org_unit = max_fines.org_unit
537 AND (stop_date IS NULL or stop_date > NOW())
538 AND standing_penalty = 30;
547 $func$ LANGUAGE plpgsql;