4 INSERT INTO config.upgrade_log (version) VALUES ('0168'); -- 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_user';
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 explode_array(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 explode_array(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 action.hold_request_permit_test( pickup_ou INT, request_ou INT, match_item BIGINT, match_user INT, match_requestor INT ) RETURNS SETOF action.matrix_test_result AS $func$
180 user_object actor.usr%ROWTYPE;
181 age_protect_object config.rule_age_hold_protect%ROWTYPE;
182 standing_penalty config.standing_penalty%ROWTYPE;
183 transit_range_ou_type actor.org_unit_type%ROWTYPE;
184 transit_source actor.org_unit%ROWTYPE;
185 item_object asset.copy%ROWTYPE;
186 ou_skip actor.org_unit_setting%ROWTYPE;
187 result action.matrix_test_result;
188 hold_test config.hold_matrix_matchpoint%ROWTYPE;
190 hold_transit_prox INT;
191 frozen_hold_count INT;
192 context_org_list INT[];
195 SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
196 SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( pickup_ou );
198 result.success := TRUE;
200 -- Fail if we couldn't find a user
201 IF user_object.id IS NULL THEN
202 result.fail_part := 'no_user';
203 result.success := FALSE;
209 SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
211 -- Fail if we couldn't find a copy
212 IF item_object.id IS NULL THEN
213 result.fail_part := 'no_item';
214 result.success := FALSE;
220 SELECT INTO matchpoint_id action.find_hold_matrix_matchpoint(pickup_ou, request_ou, match_item, match_user, match_requestor);
221 result.matchpoint := matchpoint_id;
223 SELECT INTO ou_skip * FROM actor.org_unit_setting WHERE name = 'circ.holds.target_skip_me' AND org_unit = item_object.circ_lib;
225 -- Fail if the circ_lib for the item has circ.holds.target_skip_me set to true
226 IF ou_skip.id IS NOT NULL AND ou_skip.value = 'true' THEN
227 result.fail_part := 'circ.holds.target_skip_me';
228 result.success := FALSE;
234 -- Fail if user is barred
235 IF user_object.barred IS TRUE THEN
236 result.fail_part := 'actor.usr.barred';
237 result.success := FALSE;
243 -- Fail if we couldn't find any matchpoint (requires a default)
244 IF matchpoint_id IS NULL THEN
245 result.fail_part := 'no_matchpoint';
246 result.success := FALSE;
252 SELECT INTO hold_test * FROM config.hold_matrix_matchpoint WHERE id = matchpoint_id;
254 IF hold_test.holdable IS FALSE THEN
255 result.fail_part := 'config.hold_matrix_test.holdable';
256 result.success := FALSE;
261 IF hold_test.transit_range IS NOT NULL THEN
262 SELECT INTO transit_range_ou_type * FROM actor.org_unit_type WHERE id = hold_test.transit_range;
263 IF hold_test.distance_is_from_owner THEN
264 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;
266 SELECT INTO transit_source * FROM actor.org_unit WHERE id = item_object.circ_lib;
269 PERFORM * FROM actor.org_unit_descendants( transit_source.id, transit_range_ou_type.depth ) WHERE id = pickup_ou;
272 result.fail_part := 'transit_range';
273 result.success := FALSE;
279 FOR standing_penalty IN
280 SELECT DISTINCT csp.*
281 FROM actor.usr_standing_penalty usp
282 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
283 WHERE usr = match_user
284 AND usp.org_unit IN ( SELECT * FROM explode_array(context_org_list) )
285 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
286 AND csp.block_list LIKE '%HOLD%' LOOP
288 result.fail_part := standing_penalty.name;
289 result.success := FALSE;
294 IF hold_test.stop_blocked_user IS TRUE THEN
295 FOR standing_penalty IN
296 SELECT DISTINCT csp.*
297 FROM actor.usr_standing_penalty usp
298 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
299 WHERE usr = match_user
300 AND usp.org_unit IN ( SELECT * FROM explode_array(context_org_list) )
301 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
302 AND csp.block_list LIKE '%CIRC%' LOOP
304 result.fail_part := standing_penalty.name;
305 result.success := FALSE;
311 IF hold_test.max_holds IS NOT NULL THEN
312 SELECT INTO hold_count COUNT(*)
313 FROM action.hold_request
314 WHERE usr = match_user
315 AND fulfillment_time IS NULL
316 AND cancel_time IS NULL
317 AND CASE WHEN hold_test.include_frozen_holds THEN TRUE ELSE frozen IS FALSE END;
319 IF hold_count >= hold_test.max_holds THEN
320 result.fail_part := 'config.hold_matrix_test.max_holds';
321 result.success := FALSE;
327 IF item_object.age_protect IS NOT NULL THEN
328 SELECT INTO age_protect_object * FROM config.rule_age_hold_protect WHERE id = item_object.age_protect;
330 IF item_object.create_date + age_protect_object.age > NOW() THEN
331 IF hold_test.distance_is_from_owner THEN
332 SELECT INTO hold_transit_prox prox FROM actor.org_unit_prox WHERE from_org = item_cn_object.owning_lib AND to_org = pickup_ou;
334 SELECT INTO hold_transit_prox prox FROM actor.org_unit_prox WHERE from_org = item_object.circ_lib AND to_org = pickup_ou;
337 IF hold_transit_prox > age_protect_object.prox THEN
338 result.fail_part := 'config.rule_age_hold_protect.prox';
339 result.success := FALSE;
352 $func$ LANGUAGE plpgsql;