4 INSERT INTO config.upgrade_log (version) VALUES ('0449'); -- miker
6 CREATE OR REPLACE FUNCTION action.item_user_circ_test( circ_ou INT, match_item BIGINT, match_user INT, renewal BOOL ) RETURNS SETOF action.matrix_test_result AS $func$
8 user_object actor.usr%ROWTYPE;
9 standing_penalty config.standing_penalty%ROWTYPE;
10 item_object asset.copy%ROWTYPE;
11 item_status_object config.copy_status%ROWTYPE;
12 item_location_object asset.copy_location%ROWTYPE;
13 result action.matrix_test_result;
14 circ_test config.circ_matrix_matchpoint%ROWTYPE;
15 out_by_circ_mod config.circ_matrix_circ_mod_test%ROWTYPE;
16 circ_mod_map config.circ_matrix_circ_mod_test_map%ROWTYPE;
17 hold_ratio action.hold_stats%ROWTYPE;
21 context_org_list INT[];
24 result.success := TRUE;
26 -- Fail if the user is BARRED
27 SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
29 -- Fail if we couldn't find the user
30 IF user_object.id IS NULL THEN
31 result.fail_part := 'no_user';
32 result.success := FALSE;
38 SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
40 -- Fail if we couldn't find the item
41 IF item_object.id IS NULL THEN
42 result.fail_part := 'no_item';
43 result.success := FALSE;
49 SELECT INTO circ_test * FROM action.find_circ_matrix_matchpoint(circ_ou, match_item, match_user, renewal);
50 result.matchpoint := circ_test.id;
52 -- Fail if we couldn't find a matchpoint
53 IF result.matchpoint IS NULL THEN
54 result.fail_part := 'no_matchpoint';
55 result.success := FALSE;
60 IF user_object.barred IS TRUE THEN
61 result.fail_part := 'actor.usr.barred';
62 result.success := FALSE;
67 -- Fail if the item can't circulate
68 IF item_object.circulate IS FALSE THEN
69 result.fail_part := 'asset.copy.circulate';
70 result.success := FALSE;
75 -- Fail if the item isn't in a circulateable status on a non-renewal
76 IF NOT renewal AND item_object.status NOT IN ( 0, 7, 8 ) THEN
77 result.fail_part := 'asset.copy.status';
78 result.success := FALSE;
81 ELSIF renewal AND item_object.status <> 1 THEN
82 result.fail_part := 'asset.copy.status';
83 result.success := FALSE;
88 -- Fail if the item can't circulate because of the shelving location
89 SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
90 IF item_location_object.circulate IS FALSE THEN
91 result.fail_part := 'asset.copy_location.circulate';
92 result.success := FALSE;
97 SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( circ_test.org_unit );
99 -- Fail if the test is set to hard non-circulating
100 IF circ_test.circulate IS FALSE THEN
101 result.fail_part := 'config.circ_matrix_test.circulate';
102 result.success := FALSE;
107 -- Fail if the total copy-hold ratio is too low
108 IF circ_test.total_copy_hold_ratio IS NOT NULL THEN
109 SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
110 IF hold_ratio.total_copy_ratio IS NOT NULL AND hold_ratio.total_copy_ratio < circ_test.total_copy_hold_ratio THEN
111 result.fail_part := 'config.circ_matrix_test.total_copy_hold_ratio';
112 result.success := FALSE;
118 -- Fail if the available copy-hold ratio is too low
119 IF circ_test.available_copy_hold_ratio IS NOT NULL THEN
120 SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
121 IF hold_ratio.available_copy_ratio IS NOT NULL AND hold_ratio.available_copy_ratio < circ_test.available_copy_hold_ratio THEN
122 result.fail_part := 'config.circ_matrix_test.available_copy_hold_ratio';
123 result.success := FALSE;
130 penalty_type = '%RENEW%';
132 penalty_type = '%CIRC%';
135 FOR standing_penalty IN
136 SELECT DISTINCT csp.*
137 FROM actor.usr_standing_penalty usp
138 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
139 WHERE usr = match_user
140 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
141 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
142 AND csp.block_list LIKE penalty_type LOOP
144 result.fail_part := standing_penalty.name;
145 result.success := FALSE;
150 -- Fail if the user has too many items with specific circ_modifiers checked out
151 FOR out_by_circ_mod IN SELECT * FROM config.circ_matrix_circ_mod_test WHERE matchpoint = circ_test.id LOOP
152 SELECT INTO items_out COUNT(*)
153 FROM action.circulation circ
154 JOIN asset.copy cp ON (cp.id = circ.target_copy)
155 WHERE circ.usr = match_user
156 AND circ.circ_lib IN ( SELECT * FROM unnest(context_org_list) )
157 AND circ.checkin_time IS NULL
158 AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL)
159 AND cp.circ_modifier IN (SELECT circ_mod FROM config.circ_matrix_circ_mod_test_map WHERE circ_mod_test = out_by_circ_mod.id);
160 IF items_out >= out_by_circ_mod.items_out THEN
161 result.fail_part := 'config.circ_matrix_circ_mod_test';
162 result.success := FALSE;
168 -- If we passed everything, return the successful matchpoint id
175 $func$ LANGUAGE plpgsql;
177 CREATE OR REPLACE FUNCTION actor.calculate_system_penalties( match_user INT, context_org INT ) RETURNS SETOF actor.usr_standing_penalty AS $func$
179 user_object actor.usr%ROWTYPE;
180 new_sp_row actor.usr_standing_penalty%ROWTYPE;
181 existing_sp_row actor.usr_standing_penalty%ROWTYPE;
182 collections_fines permission.grp_penalty_threshold%ROWTYPE;
183 max_fines permission.grp_penalty_threshold%ROWTYPE;
184 max_overdue permission.grp_penalty_threshold%ROWTYPE;
185 max_items_out permission.grp_penalty_threshold%ROWTYPE;
189 context_org_list INT[];
190 current_fines NUMERIC(8,2) := 0.0;
191 tmp_fines NUMERIC(8,2);
194 tmp_org actor.org_unit%ROWTYPE;
195 tmp_penalty config.standing_penalty%ROWTYPE;
198 SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
201 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
203 -- Fail if the user has a high fine balance
205 tmp_grp := user_object.profile;
207 SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 1 AND org_unit = tmp_org.id;
209 IF max_fines.threshold IS NULL THEN
210 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
215 IF tmp_grp IS NULL THEN
220 IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
224 SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
228 IF max_fines.threshold IS NOT NULL THEN
232 FROM actor.usr_standing_penalty
233 WHERE usr = match_user
234 AND org_unit = max_fines.org_unit
235 AND (stop_date IS NULL or stop_date > NOW())
236 AND standing_penalty = 1;
238 SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( max_fines.org_unit );
240 SELECT SUM(f.balance_owed) INTO current_fines
241 FROM money.materialized_billable_xact_summary f
244 FROM booking.reservation r
245 WHERE r.usr = match_user
246 AND r.pickup_lib IN (SELECT * FROM unnest(context_org_list))
247 AND xact_finish IS NULL
251 WHERE g.usr = match_user
252 AND g.billing_location IN (SELECT * FROM unnest(context_org_list))
253 AND xact_finish IS NULL
256 FROM action.circulation circ
257 WHERE circ.usr = match_user
258 AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
259 AND xact_finish IS NULL ) l USING (id);
261 IF current_fines >= max_fines.threshold THEN
262 new_sp_row.usr := match_user;
263 new_sp_row.org_unit := max_fines.org_unit;
264 new_sp_row.standing_penalty := 1;
265 RETURN NEXT new_sp_row;
269 -- Start over for max overdue
270 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
272 -- Fail if the user has too many overdue items
274 tmp_grp := user_object.profile;
277 SELECT * INTO max_overdue FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 2 AND org_unit = tmp_org.id;
279 IF max_overdue.threshold IS NULL THEN
280 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
285 IF tmp_grp IS NULL THEN
290 IF max_overdue.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
294 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
298 IF max_overdue.threshold IS NOT NULL THEN
302 FROM actor.usr_standing_penalty
303 WHERE usr = match_user
304 AND org_unit = max_overdue.org_unit
305 AND (stop_date IS NULL or stop_date > NOW())
306 AND standing_penalty = 2;
308 SELECT INTO items_overdue COUNT(*)
309 FROM action.circulation circ
310 JOIN actor.org_unit_full_path( max_overdue.org_unit ) fp ON (circ.circ_lib = fp.id)
311 WHERE circ.usr = match_user
312 AND circ.checkin_time IS NULL
313 AND circ.due_date < NOW()
314 AND (circ.stop_fines = 'MAXFINES' OR circ.stop_fines IS NULL);
316 IF items_overdue >= max_overdue.threshold::INT THEN
317 new_sp_row.usr := match_user;
318 new_sp_row.org_unit := max_overdue.org_unit;
319 new_sp_row.standing_penalty := 2;
320 RETURN NEXT new_sp_row;
324 -- Start over for max out
325 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
327 -- Fail if the user has too many checked out items
329 tmp_grp := user_object.profile;
331 SELECT * INTO max_items_out FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 3 AND org_unit = tmp_org.id;
333 IF max_items_out.threshold IS NULL THEN
334 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
339 IF tmp_grp IS NULL THEN
344 IF max_items_out.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
348 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
353 -- Fail if the user has too many items checked out
354 IF max_items_out.threshold IS NOT NULL THEN
358 FROM actor.usr_standing_penalty
359 WHERE usr = match_user
360 AND org_unit = max_items_out.org_unit
361 AND (stop_date IS NULL or stop_date > NOW())
362 AND standing_penalty = 3;
364 SELECT INTO items_out COUNT(*)
365 FROM action.circulation circ
366 JOIN actor.org_unit_full_path( max_items_out.org_unit ) fp ON (circ.circ_lib = fp.id)
367 WHERE circ.usr = match_user
368 AND circ.checkin_time IS NULL
369 AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL);
371 IF items_out >= max_items_out.threshold::INT THEN
372 new_sp_row.usr := match_user;
373 new_sp_row.org_unit := max_items_out.org_unit;
374 new_sp_row.standing_penalty := 3;
375 RETURN NEXT new_sp_row;
379 -- Start over for collections warning
380 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
382 -- Fail if the user has a collections-level fine balance
384 tmp_grp := user_object.profile;
386 SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 4 AND org_unit = tmp_org.id;
388 IF max_fines.threshold IS NULL THEN
389 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
394 IF tmp_grp IS NULL THEN
399 IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
403 SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
407 IF max_fines.threshold IS NOT NULL THEN
411 FROM actor.usr_standing_penalty
412 WHERE usr = match_user
413 AND org_unit = max_fines.org_unit
414 AND (stop_date IS NULL or stop_date > NOW())
415 AND standing_penalty = 4;
417 SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( max_fines.org_unit );
419 SELECT SUM(f.balance_owed) INTO current_fines
420 FROM money.materialized_billable_xact_summary f
423 FROM booking.reservation r
424 WHERE r.usr = match_user
425 AND r.pickup_lib IN (SELECT * FROM unnest(context_org_list))
426 AND r.xact_finish IS NULL
430 WHERE g.usr = match_user
431 AND g.billing_location IN (SELECT * FROM unnest(context_org_list))
432 AND g.xact_finish IS NULL
435 FROM action.circulation circ
436 WHERE circ.usr = match_user
437 AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
438 AND circ.xact_finish IS NULL ) l USING (id);
440 IF current_fines >= max_fines.threshold THEN
441 new_sp_row.usr := match_user;
442 new_sp_row.org_unit := max_fines.org_unit;
443 new_sp_row.standing_penalty := 4;
444 RETURN NEXT new_sp_row;
448 -- Start over for in collections
449 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
451 -- Remove the in-collections penalty if the user has paid down enough
452 -- This penalty is different, because this code is not responsible for creating
453 -- new in-collections penalties, only for removing them
455 tmp_grp := user_object.profile;
457 SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 30 AND org_unit = tmp_org.id;
459 IF max_fines.threshold IS NULL THEN
460 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
465 IF tmp_grp IS NULL THEN
470 IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
474 SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
478 IF max_fines.threshold IS NOT NULL THEN
480 SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( max_fines.org_unit );
482 -- first, see if the user had paid down to the threshold
483 SELECT SUM(f.balance_owed) INTO current_fines
484 FROM money.materialized_billable_xact_summary f
487 FROM booking.reservation r
488 WHERE r.usr = match_user
489 AND r.pickup_lib IN (SELECT * FROM unnest(context_org_list))
490 AND r.xact_finish IS NULL
494 WHERE g.usr = match_user
495 AND g.billing_location IN (SELECT * FROM unnest(context_org_list))
496 AND g.xact_finish IS NULL
499 FROM action.circulation circ
500 WHERE circ.usr = match_user
501 AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
502 AND circ.xact_finish IS NULL ) l USING (id);
504 IF current_fines IS NULL OR current_fines <= max_fines.threshold THEN
505 -- patron has paid down enough
507 SELECT INTO tmp_penalty * FROM config.standing_penalty WHERE id = 30;
509 IF tmp_penalty.org_depth IS NOT NULL THEN
511 -- since this code is not responsible for applying the penalty, it can't
512 -- guarantee the current context org will match the org at which the penalty
513 --- was applied. search up the org tree until we hit the configured penalty depth
514 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
515 SELECT INTO tmp_depth depth FROM actor.org_unit_type WHERE id = tmp_org.ou_type;
517 WHILE tmp_depth >= tmp_penalty.org_depth LOOP
521 FROM actor.usr_standing_penalty
522 WHERE usr = match_user
523 AND org_unit = tmp_org.id
524 AND (stop_date IS NULL or stop_date > NOW())
525 AND standing_penalty = 30;
527 IF tmp_org.parent_ou IS NULL THEN
531 SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
532 SELECT INTO tmp_depth depth FROM actor.org_unit_type WHERE id = tmp_org.ou_type;
537 -- no penalty depth is defined, look for exact matches
541 FROM actor.usr_standing_penalty
542 WHERE usr = match_user
543 AND org_unit = max_fines.org_unit
544 AND (stop_date IS NULL or stop_date > NOW())
545 AND standing_penalty = 30;
554 $func$ LANGUAGE plpgsql;