3 SELECT evergreen.upgrade_deps_block_check('0951', :eg_version);
5 ALTER TABLE config.standing_penalty
6 ADD COLUMN ignore_proximity INTEGER;
8 CREATE OR REPLACE FUNCTION action.hold_request_permit_test( pickup_ou INT, request_ou INT, match_item BIGINT, match_user INT, match_requestor INT, retargetting BOOL ) RETURNS SETOF action.matrix_test_result AS $func$
11 user_object actor.usr%ROWTYPE;
12 age_protect_object config.rule_age_hold_protect%ROWTYPE;
13 standing_penalty config.standing_penalty%ROWTYPE;
14 transit_range_ou_type actor.org_unit_type%ROWTYPE;
15 transit_source actor.org_unit%ROWTYPE;
16 item_object asset.copy%ROWTYPE;
17 item_cn_object asset.call_number%ROWTYPE;
18 item_status_object config.copy_status%ROWTYPE;
19 item_location_object asset.copy_location%ROWTYPE;
20 ou_skip actor.org_unit_setting%ROWTYPE;
21 result action.matrix_test_result;
22 hold_test config.hold_matrix_matchpoint%ROWTYPE;
24 age_protect_date TIMESTAMP WITH TIME ZONE;
26 hold_transit_prox INT;
27 frozen_hold_count INT;
28 context_org_list INT[];
31 v_pickup_ou ALIAS FOR pickup_ou;
32 v_request_ou ALIAS FOR request_ou;
36 SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
37 SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( v_pickup_ou );
39 result.success := TRUE;
41 -- The HOLD penalty block only applies to new holds.
42 -- The CAPTURE penalty block applies to existing holds.
43 hold_penalty := 'HOLD';
45 hold_penalty := 'CAPTURE';
48 -- Fail if we couldn't find a user
49 IF user_object.id IS NULL THEN
50 result.fail_part := 'no_user';
51 result.success := FALSE;
57 SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
59 -- Fail if we couldn't find a copy
60 IF item_object.id IS NULL THEN
61 result.fail_part := 'no_item';
62 result.success := FALSE;
68 SELECT INTO matchpoint_id action.find_hold_matrix_matchpoint(v_pickup_ou, v_request_ou, match_item, match_user, match_requestor);
69 result.matchpoint := matchpoint_id;
71 SELECT INTO ou_skip * FROM actor.org_unit_setting WHERE name = 'circ.holds.target_skip_me' AND org_unit = item_object.circ_lib;
73 -- Fail if the circ_lib for the item has circ.holds.target_skip_me set to true
74 IF ou_skip.id IS NOT NULL AND ou_skip.value = 'true' THEN
75 result.fail_part := 'circ.holds.target_skip_me';
76 result.success := FALSE;
82 -- Fail if user is barred
83 IF user_object.barred IS TRUE THEN
84 result.fail_part := 'actor.usr.barred';
85 result.success := FALSE;
91 SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number;
92 SELECT INTO item_status_object * FROM config.copy_status WHERE id = item_object.status;
93 SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
95 -- Fail if we couldn't find any matchpoint (requires a default)
96 IF matchpoint_id IS NULL THEN
97 result.fail_part := 'no_matchpoint';
98 result.success := FALSE;
104 SELECT INTO hold_test * FROM config.hold_matrix_matchpoint WHERE id = matchpoint_id;
106 IF hold_test.holdable IS FALSE THEN
107 result.fail_part := 'config.hold_matrix_test.holdable';
108 result.success := FALSE;
113 IF item_object.holdable IS FALSE THEN
114 result.fail_part := 'item.holdable';
115 result.success := FALSE;
120 IF item_status_object.holdable IS FALSE THEN
121 result.fail_part := 'status.holdable';
122 result.success := FALSE;
127 IF item_location_object.holdable IS FALSE THEN
128 result.fail_part := 'location.holdable';
129 result.success := FALSE;
134 IF hold_test.transit_range IS NOT NULL THEN
135 SELECT INTO transit_range_ou_type * FROM actor.org_unit_type WHERE id = hold_test.transit_range;
136 IF hold_test.distance_is_from_owner THEN
137 SELECT INTO transit_source ou.* FROM actor.org_unit ou JOIN asset.call_number cn ON (cn.owning_lib = ou.id) WHERE cn.id = item_object.call_number;
139 SELECT INTO transit_source * FROM actor.org_unit WHERE id = item_object.circ_lib;
142 PERFORM * FROM actor.org_unit_descendants( transit_source.id, transit_range_ou_type.depth ) WHERE id = v_pickup_ou;
145 result.fail_part := 'transit_range';
146 result.success := FALSE;
152 -- Proximity of user's home_ou to the pickup_lib to see if penalty should be ignored.
153 SELECT INTO pickup_prox prox FROM actor.org_unit_proximity WHERE from_org = user_object.home_ou AND to_org = v_pickup_ou;
154 -- Proximity of user's home_ou to the items' lib to see if penalty should be ignored.
155 IF hold_test.distance_is_from_owner THEN
156 SELECT INTO item_prox prox FROM actor.org_unit_proximity WHERE from_org = user_object.home_ou AND to_org = item_cn_object.owning_lib;
158 SELECT INTO item_prox prox FROM actor.org_unit_proximity WHERE from_org = user_object.home_ou AND to_org = item_object.circ_lib;
161 FOR standing_penalty IN
162 SELECT DISTINCT csp.*
163 FROM actor.usr_standing_penalty usp
164 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
165 WHERE usr = match_user
166 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
167 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
168 AND (csp.ignore_proximity IS NULL OR csp.ignore_proximity < item_prox
169 OR csp.ignore_proximity < pickup_prox)
170 AND csp.block_list LIKE '%' || hold_penalty || '%' LOOP
172 result.fail_part := standing_penalty.name;
173 result.success := FALSE;
178 IF hold_test.stop_blocked_user IS TRUE THEN
179 FOR standing_penalty IN
180 SELECT DISTINCT csp.*
181 FROM actor.usr_standing_penalty usp
182 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
183 WHERE usr = match_user
184 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
185 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
186 AND csp.block_list LIKE '%CIRC%' LOOP
188 result.fail_part := standing_penalty.name;
189 result.success := FALSE;
195 IF hold_test.max_holds IS NOT NULL AND NOT retargetting THEN
196 SELECT INTO hold_count COUNT(*)
197 FROM action.hold_request
198 WHERE usr = match_user
199 AND fulfillment_time IS NULL
200 AND cancel_time IS NULL
201 AND CASE WHEN hold_test.include_frozen_holds THEN TRUE ELSE frozen IS FALSE END;
203 IF hold_count >= hold_test.max_holds THEN
204 result.fail_part := 'config.hold_matrix_test.max_holds';
205 result.success := FALSE;
211 IF item_object.age_protect IS NOT NULL THEN
212 SELECT INTO age_protect_object * FROM config.rule_age_hold_protect WHERE id = item_object.age_protect;
213 IF hold_test.distance_is_from_owner THEN
214 SELECT INTO use_active_date value FROM actor.org_unit_ancestor_setting('circ.holds.age_protect.active_date', item_cn_object.owning_lib);
216 SELECT INTO use_active_date value FROM actor.org_unit_ancestor_setting('circ.holds.age_protect.active_date', item_object.circ_lib);
218 IF use_active_date = 'true' THEN
219 age_protect_date := COALESCE(item_object.active_date, NOW());
221 age_protect_date := item_object.create_date;
223 IF age_protect_date + age_protect_object.age > NOW() THEN
224 IF hold_test.distance_is_from_owner THEN
225 SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number;
226 SELECT INTO hold_transit_prox prox FROM actor.org_unit_proximity WHERE from_org = item_cn_object.owning_lib AND to_org = v_pickup_ou;
228 SELECT INTO hold_transit_prox prox FROM actor.org_unit_proximity WHERE from_org = item_object.circ_lib AND to_org = v_pickup_ou;
231 IF hold_transit_prox > age_protect_object.prox THEN
232 result.fail_part := 'config.rule_age_hold_protect.prox';
233 result.success := FALSE;
246 $func$ LANGUAGE plpgsql;
248 CREATE OR REPLACE FUNCTION action.item_user_circ_test( circ_ou INT, match_item BIGINT, match_user INT, renewal BOOL ) RETURNS SETOF action.circ_matrix_test_result AS $func$
250 user_object actor.usr%ROWTYPE;
251 standing_penalty config.standing_penalty%ROWTYPE;
252 item_object asset.copy%ROWTYPE;
253 item_status_object config.copy_status%ROWTYPE;
254 item_location_object asset.copy_location%ROWTYPE;
255 result action.circ_matrix_test_result;
256 circ_test action.found_circ_matrix_matchpoint;
257 circ_matchpoint config.circ_matrix_matchpoint%ROWTYPE;
258 circ_limit_set config.circ_limit_set%ROWTYPE;
259 hold_ratio action.hold_stats%ROWTYPE;
262 context_org_list INT[];
267 -- Assume success unless we hit a failure condition
268 result.success := TRUE;
270 -- Need user info to look up matchpoints
271 SELECT INTO user_object * FROM actor.usr WHERE id = match_user AND NOT deleted;
273 -- (Insta)Fail if we couldn't find the user
274 IF user_object.id IS NULL THEN
275 result.fail_part := 'no_user';
276 result.success := FALSE;
282 -- Need item info to look up matchpoints
283 SELECT INTO item_object * FROM asset.copy WHERE id = match_item AND NOT deleted;
285 -- (Insta)Fail if we couldn't find the item
286 IF item_object.id IS NULL THEN
287 result.fail_part := 'no_item';
288 result.success := FALSE;
294 SELECT INTO circ_test * FROM action.find_circ_matrix_matchpoint(circ_ou, item_object, user_object, renewal);
296 circ_matchpoint := circ_test.matchpoint;
297 result.matchpoint := circ_matchpoint.id;
298 result.circulate := circ_matchpoint.circulate;
299 result.duration_rule := circ_matchpoint.duration_rule;
300 result.recurring_fine_rule := circ_matchpoint.recurring_fine_rule;
301 result.max_fine_rule := circ_matchpoint.max_fine_rule;
302 result.hard_due_date := circ_matchpoint.hard_due_date;
303 result.renewals := circ_matchpoint.renewals;
304 result.grace_period := circ_matchpoint.grace_period;
305 result.buildrows := circ_test.buildrows;
307 -- (Insta)Fail if we couldn't find a matchpoint
308 IF circ_test.success = false THEN
309 result.fail_part := 'no_matchpoint';
310 result.success := FALSE;
316 -- All failures before this point are non-recoverable
317 -- Below this point are possibly overridable failures
319 -- Fail if the user is barred
320 IF user_object.barred IS TRUE THEN
321 result.fail_part := 'actor.usr.barred';
322 result.success := FALSE;
327 -- Fail if the item can't circulate
328 IF item_object.circulate IS FALSE THEN
329 result.fail_part := 'asset.copy.circulate';
330 result.success := FALSE;
335 -- Fail if the item isn't in a circulateable status on a non-renewal
336 IF NOT renewal AND item_object.status NOT IN ( 0, 7, 8 ) THEN
337 result.fail_part := 'asset.copy.status';
338 result.success := FALSE;
341 -- Alternately, fail if the item isn't checked out on a renewal
342 ELSIF renewal AND item_object.status <> 1 THEN
343 result.fail_part := 'asset.copy.status';
344 result.success := FALSE;
349 -- Fail if the item can't circulate because of the shelving location
350 SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
351 IF item_location_object.circulate IS FALSE THEN
352 result.fail_part := 'asset.copy_location.circulate';
353 result.success := FALSE;
358 -- Use Circ OU for penalties and such
359 SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( circ_ou );
361 -- Proximity of user's home_ou to circ_ou to see if penalties should be ignored.
362 SELECT INTO home_prox prox FROM actor.org_unit_proximity WHERE from_org = user_object.home_ou AND to_org = circ_ou;
364 -- Proximity of user's home_ou to item circ_lib to see if penalties should be ignored.
365 SELECT INTO item_prox prox FROM actor.org_unit_proximity WHERE from_org = user_object.home_ou AND to_org = item_object.circ_lib;
368 penalty_type = '%RENEW%';
370 penalty_type = '%CIRC%';
373 FOR standing_penalty IN
374 SELECT DISTINCT csp.*
375 FROM actor.usr_standing_penalty usp
376 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
377 WHERE usr = match_user
378 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
379 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
380 AND (csp.ignore_proximity IS NULL
381 OR csp.ignore_proximity < home_prox
382 OR csp.ignore_proximity < item_prox)
383 AND csp.block_list LIKE penalty_type LOOP
385 result.fail_part := standing_penalty.name;
386 result.success := FALSE;
391 -- Fail if the test is set to hard non-circulating
392 IF circ_matchpoint.circulate IS FALSE THEN
393 result.fail_part := 'config.circ_matrix_test.circulate';
394 result.success := FALSE;
399 -- Fail if the total copy-hold ratio is too low
400 IF circ_matchpoint.total_copy_hold_ratio IS NOT NULL THEN
401 SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
402 IF hold_ratio.total_copy_ratio IS NOT NULL AND hold_ratio.total_copy_ratio < circ_matchpoint.total_copy_hold_ratio THEN
403 result.fail_part := 'config.circ_matrix_test.total_copy_hold_ratio';
404 result.success := FALSE;
410 -- Fail if the available copy-hold ratio is too low
411 IF circ_matchpoint.available_copy_hold_ratio IS NOT NULL THEN
412 IF hold_ratio.hold_count IS NULL THEN
413 SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
415 IF hold_ratio.available_copy_ratio IS NOT NULL AND hold_ratio.available_copy_ratio < circ_matchpoint.available_copy_hold_ratio THEN
416 result.fail_part := 'config.circ_matrix_test.available_copy_hold_ratio';
417 result.success := FALSE;
423 -- Fail if the user has too many items out by defined limit sets
424 FOR circ_limit_set IN SELECT ccls.* FROM config.circ_limit_set ccls
425 JOIN config.circ_matrix_limit_set_map ccmlsm ON ccmlsm.limit_set = ccls.id
426 WHERE ccmlsm.active AND ( ccmlsm.matchpoint = circ_matchpoint.id OR
427 ( ccmlsm.matchpoint IN (SELECT * FROM unnest(result.buildrows)) AND ccmlsm.fallthrough )
429 IF circ_limit_set.items_out > 0 AND NOT renewal THEN
430 SELECT INTO context_org_list ARRAY_AGG(aou.id)
431 FROM actor.org_unit_full_path( circ_ou ) aou
432 JOIN actor.org_unit_type aout ON aou.ou_type = aout.id
433 WHERE aout.depth >= circ_limit_set.depth;
434 IF circ_limit_set.global THEN
435 WITH RECURSIVE descendant_depth AS (
438 FROM actor.org_unit ou
439 WHERE ou.id IN (SELECT * FROM unnest(context_org_list))
443 FROM actor.org_unit ou
444 JOIN descendant_depth ot ON (ot.id = ou.parent_ou)
445 ) SELECT INTO context_org_list ARRAY_AGG(ou.id) FROM actor.org_unit ou JOIN descendant_depth USING (id);
447 SELECT INTO items_out COUNT(DISTINCT circ.id)
448 FROM action.circulation circ
449 JOIN asset.copy copy ON (copy.id = circ.target_copy)
450 LEFT JOIN action.circulation_limit_group_map aclgm ON (circ.id = aclgm.circ)
451 WHERE circ.usr = match_user
452 AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
453 AND circ.checkin_time IS NULL
454 AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL)
455 AND (copy.circ_modifier IN (SELECT circ_mod FROM config.circ_limit_set_circ_mod_map WHERE limit_set = circ_limit_set.id)
456 OR copy.location IN (SELECT copy_loc FROM config.circ_limit_set_copy_loc_map WHERE limit_set = circ_limit_set.id)
457 OR aclgm.limit_group IN (SELECT limit_group FROM config.circ_limit_set_group_map WHERE limit_set = circ_limit_set.id)
459 IF items_out >= circ_limit_set.items_out THEN
460 result.fail_part := 'config.circ_matrix_circ_mod_test';
461 result.success := FALSE;
466 SELECT INTO result.limit_groups result.limit_groups || ARRAY_AGG(limit_group) FROM config.circ_limit_set_group_map WHERE limit_set = circ_limit_set.id AND NOT check_only;
469 -- If we passed everything, return the successful matchpoint
476 $func$ LANGUAGE plpgsql;