3 SELECT evergreen.upgrade_deps_block_check('1221', :eg_version);
5 CREATE OR REPLACE FUNCTION action.copy_calculated_proximity(
12 ) RETURNS NUMERIC AS $f$
14 baseline_prox NUMERIC;
15 aoupa actor.org_unit_proximity_adjustment%ROWTYPE;
18 -- First, gather the baseline proximity of "here" to pickup lib
19 SELECT prox INTO baseline_prox FROM actor.org_unit_proximity WHERE from_org = vacp_cl AND to_org = pickup;
21 -- Find any absolute adjustments, and set the baseline prox to that
22 SELECT adj.* INTO aoupa
23 FROM actor.org_unit_proximity_adjustment adj
24 LEFT JOIN actor.org_unit_ancestors_distance(vacp_cl) acp_cl ON (acp_cl.id = adj.item_circ_lib)
25 LEFT JOIN actor.org_unit_ancestors_distance(vacn_ol) acn_ol ON (acn_ol.id = adj.item_owning_lib)
26 LEFT JOIN actor.org_unit_ancestors_distance(vacl_ol) acl_ol ON (acl_ol.id = adj.copy_location)
27 LEFT JOIN actor.org_unit_ancestors_distance(pickup) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
28 LEFT JOIN actor.org_unit_ancestors_distance(request) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
29 WHERE (adj.circ_mod IS NULL OR adj.circ_mod = vacp_cm) AND
30 (adj.item_circ_lib IS NULL OR adj.item_circ_lib = acp_cl.id) AND
31 (adj.item_owning_lib IS NULL OR adj.item_owning_lib = acn_ol.id) AND
32 (adj.copy_location IS NULL OR adj.copy_location = acl_ol.id) AND
33 (adj.hold_pickup_lib IS NULL OR adj.hold_pickup_lib = ahr_pl.id) AND
34 (adj.hold_request_lib IS NULL OR adj.hold_request_lib = ahr_rl.id) AND
35 absolute_adjustment AND
36 COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
38 COALESCE(acp_cl.distance,999)
39 + COALESCE(acn_ol.distance,999)
40 + COALESCE(acl_ol.distance,999)
41 + COALESCE(ahr_pl.distance,999)
42 + COALESCE(ahr_rl.distance,999),
47 baseline_prox := aoupa.prox_adjustment;
50 -- Now find any relative adjustments, and change the baseline prox based on them
53 FROM actor.org_unit_proximity_adjustment adj
54 LEFT JOIN actor.org_unit_ancestors_distance(vacp_cl) acp_cl ON (acp_cl.id = adj.item_circ_lib)
55 LEFT JOIN actor.org_unit_ancestors_distance(vacn_ol) acn_ol ON (acn_ol.id = adj.item_owning_lib)
56 LEFT JOIN actor.org_unit_ancestors_distance(vacl_ol) acl_ol ON (acn_ol.id = adj.copy_location)
57 LEFT JOIN actor.org_unit_ancestors_distance(pickup) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
58 LEFT JOIN actor.org_unit_ancestors_distance(request) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
59 WHERE (adj.circ_mod IS NULL OR adj.circ_mod = vacp_cm) AND
60 (adj.item_circ_lib IS NULL OR adj.item_circ_lib = acp_cl.id) AND
61 (adj.item_owning_lib IS NULL OR adj.item_owning_lib = acn_ol.id) AND
62 (adj.copy_location IS NULL OR adj.copy_location = acl_ol.id) AND
63 (adj.hold_pickup_lib IS NULL OR adj.hold_pickup_lib = ahr_pl.id) AND
64 (adj.hold_request_lib IS NULL OR adj.hold_request_lib = ahr_rl.id) AND
65 NOT absolute_adjustment AND
66 COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
68 baseline_prox := baseline_prox + aoupa.prox_adjustment;
75 CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity(
78 copy_context_ou INT DEFAULT NULL
79 -- TODO maybe? hold_context_ou INT DEFAULT NULL. This would optionally
80 -- support an "ahprox" measurement: adjust prox between copy circ lib and
81 -- hold request lib, but I'm unsure whether to use this theoretical
82 -- argument only in the baseline calculation or later in the other
83 -- queries in this function.
84 ) RETURNS NUMERIC AS $f$
86 ahr action.hold_request%ROWTYPE;
87 acp asset.copy%ROWTYPE;
88 acn asset.call_number%ROWTYPE;
89 acl asset.copy_location%ROWTYPE;
94 SELECT * INTO ahr FROM action.hold_request WHERE id = ahr_id;
95 SELECT * INTO acp FROM asset.copy WHERE id = acp_id;
96 SELECT * INTO acn FROM asset.call_number WHERE id = acp.call_number;
97 SELECT * INTO acl FROM asset.copy_location WHERE id = acp.location;
99 IF copy_context_ou IS NULL THEN
100 copy_context_ou := acp.circ_lib;
103 SELECT action.copy_calculated_proximity(
114 $f$ LANGUAGE PLPGSQL;
116 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$
119 user_object actor.usr%ROWTYPE;
120 age_protect_object config.rule_age_hold_protect%ROWTYPE;
121 standing_penalty config.standing_penalty%ROWTYPE;
122 transit_range_ou_type actor.org_unit_type%ROWTYPE;
123 transit_source actor.org_unit%ROWTYPE;
124 item_object asset.copy%ROWTYPE;
125 item_cn_object asset.call_number%ROWTYPE;
126 item_status_object config.copy_status%ROWTYPE;
127 item_location_object asset.copy_location%ROWTYPE;
128 ou_skip actor.org_unit_setting%ROWTYPE;
129 calc_age_prox actor.org_unit_setting%ROWTYPE;
130 result action.matrix_test_result;
131 hold_test config.hold_matrix_matchpoint%ROWTYPE;
132 use_active_date TEXT;
134 age_protect_date TIMESTAMP WITH TIME ZONE;
136 hold_transit_prox NUMERIC;
137 frozen_hold_count INT;
138 context_org_list INT[];
141 v_pickup_ou ALIAS FOR pickup_ou;
142 v_request_ou ALIAS FOR request_ou;
146 SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
147 SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( v_pickup_ou );
149 result.success := TRUE;
151 -- The HOLD penalty block only applies to new holds.
152 -- The CAPTURE penalty block applies to existing holds.
153 hold_penalty := 'HOLD';
155 hold_penalty := 'CAPTURE';
158 -- Fail if we couldn't find a user
159 IF user_object.id IS NULL THEN
160 result.fail_part := 'no_user';
161 result.success := FALSE;
167 SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
169 -- Fail if we couldn't find a copy
170 IF item_object.id IS NULL THEN
171 result.fail_part := 'no_item';
172 result.success := FALSE;
178 SELECT INTO matchpoint_id action.find_hold_matrix_matchpoint(v_pickup_ou, v_request_ou, match_item, match_user, match_requestor);
179 result.matchpoint := matchpoint_id;
181 SELECT INTO ou_skip * FROM actor.org_unit_setting WHERE name = 'circ.holds.target_skip_me' AND org_unit = item_object.circ_lib;
183 -- Fail if the circ_lib for the item has circ.holds.target_skip_me set to true
184 IF ou_skip.id IS NOT NULL AND ou_skip.value = 'true' THEN
185 result.fail_part := 'circ.holds.target_skip_me';
186 result.success := FALSE;
192 -- Fail if user is barred
193 IF user_object.barred IS TRUE THEN
194 result.fail_part := 'actor.usr.barred';
195 result.success := FALSE;
201 SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number;
202 SELECT INTO item_status_object * FROM config.copy_status WHERE id = item_object.status;
203 SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
205 -- Fail if we couldn't find any matchpoint (requires a default)
206 IF matchpoint_id IS NULL THEN
207 result.fail_part := 'no_matchpoint';
208 result.success := FALSE;
214 SELECT INTO hold_test * FROM config.hold_matrix_matchpoint WHERE id = matchpoint_id;
216 IF hold_test.holdable IS FALSE THEN
217 result.fail_part := 'config.hold_matrix_test.holdable';
218 result.success := FALSE;
223 IF item_object.holdable IS FALSE THEN
224 result.fail_part := 'item.holdable';
225 result.success := FALSE;
230 IF item_status_object.holdable IS FALSE THEN
231 result.fail_part := 'status.holdable';
232 result.success := FALSE;
237 IF item_location_object.holdable IS FALSE THEN
238 result.fail_part := 'location.holdable';
239 result.success := FALSE;
244 IF hold_test.transit_range IS NOT NULL THEN
245 SELECT INTO transit_range_ou_type * FROM actor.org_unit_type WHERE id = hold_test.transit_range;
246 IF hold_test.distance_is_from_owner THEN
247 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;
249 SELECT INTO transit_source * FROM actor.org_unit WHERE id = item_object.circ_lib;
252 PERFORM * FROM actor.org_unit_descendants( transit_source.id, transit_range_ou_type.depth ) WHERE id = v_pickup_ou;
255 result.fail_part := 'transit_range';
256 result.success := FALSE;
262 -- Proximity of user's home_ou to the pickup_lib to see if penalty should be ignored.
263 SELECT INTO pickup_prox prox FROM actor.org_unit_proximity WHERE from_org = user_object.home_ou AND to_org = v_pickup_ou;
264 -- Proximity of user's home_ou to the items' lib to see if penalty should be ignored.
265 IF hold_test.distance_is_from_owner THEN
266 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;
268 SELECT INTO item_prox prox FROM actor.org_unit_proximity WHERE from_org = user_object.home_ou AND to_org = item_object.circ_lib;
271 FOR standing_penalty IN
272 SELECT DISTINCT csp.*
273 FROM actor.usr_standing_penalty usp
274 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
275 WHERE usr = match_user
276 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
277 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
278 AND (csp.ignore_proximity IS NULL OR csp.ignore_proximity < item_prox
279 OR csp.ignore_proximity < pickup_prox)
280 AND csp.block_list LIKE '%' || hold_penalty || '%' LOOP
282 result.fail_part := standing_penalty.name;
283 result.success := FALSE;
288 IF hold_test.stop_blocked_user IS TRUE THEN
289 FOR standing_penalty IN
290 SELECT DISTINCT csp.*
291 FROM actor.usr_standing_penalty usp
292 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
293 WHERE usr = match_user
294 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
295 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
296 AND csp.block_list LIKE '%CIRC%' LOOP
298 result.fail_part := standing_penalty.name;
299 result.success := FALSE;
305 IF hold_test.max_holds IS NOT NULL AND NOT retargetting THEN
306 SELECT INTO hold_count COUNT(*)
307 FROM action.hold_request
308 WHERE usr = match_user
309 AND fulfillment_time IS NULL
310 AND cancel_time IS NULL
311 AND CASE WHEN hold_test.include_frozen_holds THEN TRUE ELSE frozen IS FALSE END;
313 IF hold_count >= hold_test.max_holds THEN
314 result.fail_part := 'config.hold_matrix_test.max_holds';
315 result.success := FALSE;
321 IF item_object.age_protect IS NOT NULL THEN
322 SELECT INTO age_protect_object * FROM config.rule_age_hold_protect WHERE id = item_object.age_protect;
323 IF hold_test.distance_is_from_owner THEN
324 SELECT INTO use_active_date value FROM actor.org_unit_ancestor_setting('circ.holds.age_protect.active_date', item_cn_object.owning_lib);
326 SELECT INTO use_active_date value FROM actor.org_unit_ancestor_setting('circ.holds.age_protect.active_date', item_object.circ_lib);
328 IF use_active_date = 'true' THEN
329 age_protect_date := COALESCE(item_object.active_date, NOW());
331 age_protect_date := item_object.create_date;
333 IF age_protect_date + age_protect_object.age > NOW() THEN
334 SELECT INTO calc_age_prox * FROM actor.org_unit_setting WHERE name = 'circ.holds.calculated_age_proximity' AND org_unit = item_object.circ_lib;
335 IF hold_test.distance_is_from_owner THEN
336 prox_ou := item_cn_object.owning_lib;
338 prox_ou := item_object.circ_lib;
340 IF calc_age_prox.id IS NOT NULL AND calc_age_prox.value = 'true' THEN
341 SELECT INTO hold_transit_prox action.copy_calculated_proximity(
345 item_object.circ_modifier,
346 item_cn_object.owning_lib,
347 item_location_object.owning_lib
350 SELECT INTO hold_transit_prox prox::NUMERIC FROM actor.org_unit_proximity WHERE from_org = prox_ou AND to_org = v_pickup_ou;
353 IF hold_transit_prox > age_protect_object.prox::NUMERIC THEN
354 result.fail_part := 'config.rule_age_hold_protect.prox';
355 result.success := FALSE;
368 $func$ LANGUAGE plpgsql;