BEGIN; INSERT INTO config.upgrade_log (version) VALUES ('0168'); -- miker 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$ DECLARE user_object actor.usr%ROWTYPE; standing_penalty config.standing_penalty%ROWTYPE; item_object asset.copy%ROWTYPE; item_status_object config.copy_status%ROWTYPE; item_location_object asset.copy_location%ROWTYPE; result action.matrix_test_result; circ_test config.circ_matrix_matchpoint%ROWTYPE; out_by_circ_mod config.circ_matrix_circ_mod_test%ROWTYPE; circ_mod_map config.circ_matrix_circ_mod_test_map%ROWTYPE; hold_ratio action.hold_stats%ROWTYPE; penalty_type TEXT; tmp_grp INT; items_out INT; context_org_list INT[]; done BOOL := FALSE; BEGIN result.success := TRUE; -- Fail if the user is BARRED SELECT INTO user_object * FROM actor.usr WHERE id = match_user; -- Fail if we couldn't find the user IF user_object.id IS NULL THEN result.fail_part := 'no_user'; result.success := FALSE; done := TRUE; RETURN NEXT result; RETURN; END IF; SELECT INTO item_object * FROM asset.copy WHERE id = match_item; -- Fail if we couldn't find the item IF item_object.id IS NULL THEN result.fail_part := 'no_user'; result.success := FALSE; done := TRUE; RETURN NEXT result; RETURN; END IF; SELECT INTO circ_test * FROM action.find_circ_matrix_matchpoint(circ_ou, match_item, match_user, renewal); result.matchpoint := circ_test.id; -- Fail if we couldn't find a matchpoint IF result.matchpoint IS NULL THEN result.fail_part := 'no_matchpoint'; result.success := FALSE; done := TRUE; RETURN NEXT result; END IF; IF user_object.barred IS TRUE THEN result.fail_part := 'actor.usr.barred'; result.success := FALSE; done := TRUE; RETURN NEXT result; END IF; -- Fail if the item can't circulate IF item_object.circulate IS FALSE THEN result.fail_part := 'asset.copy.circulate'; result.success := FALSE; done := TRUE; RETURN NEXT result; END IF; -- Fail if the item isn't in a circulateable status on a non-renewal IF NOT renewal AND item_object.status NOT IN ( 0, 7, 8 ) THEN result.fail_part := 'asset.copy.status'; result.success := FALSE; done := TRUE; RETURN NEXT result; ELSIF renewal AND item_object.status <> 1 THEN result.fail_part := 'asset.copy.status'; result.success := FALSE; done := TRUE; RETURN NEXT result; END IF; -- Fail if the item can't circulate because of the shelving location SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location; IF item_location_object.circulate IS FALSE THEN result.fail_part := 'asset.copy_location.circulate'; result.success := FALSE; done := TRUE; RETURN NEXT result; END IF; SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( circ_test.org_unit ); -- Fail if the test is set to hard non-circulating IF circ_test.circulate IS FALSE THEN result.fail_part := 'config.circ_matrix_test.circulate'; result.success := FALSE; done := TRUE; RETURN NEXT result; END IF; -- Fail if the total copy-hold ratio is too low IF circ_test.total_copy_hold_ratio IS NOT NULL THEN SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item); IF hold_ratio.total_copy_ratio IS NOT NULL AND hold_ratio.total_copy_ratio < circ_test.total_copy_hold_ratio THEN result.fail_part := 'config.circ_matrix_test.total_copy_hold_ratio'; result.success := FALSE; done := TRUE; RETURN NEXT result; END IF; END IF; -- Fail if the available copy-hold ratio is too low IF circ_test.available_copy_hold_ratio IS NOT NULL THEN SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item); IF hold_ratio.available_copy_ratio IS NOT NULL AND hold_ratio.available_copy_ratio < circ_test.available_copy_hold_ratio THEN result.fail_part := 'config.circ_matrix_test.available_copy_hold_ratio'; result.success := FALSE; done := TRUE; RETURN NEXT result; END IF; END IF; IF renewal THEN penalty_type = '%RENEW%'; ELSE penalty_type = '%CIRC%'; END IF; FOR standing_penalty IN SELECT DISTINCT csp.* FROM actor.usr_standing_penalty usp JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty) WHERE usr = match_user AND usp.org_unit IN ( SELECT * FROM explode_array(context_org_list) ) AND (usp.stop_date IS NULL or usp.stop_date > NOW()) AND csp.block_list LIKE penalty_type LOOP result.fail_part := standing_penalty.name; result.success := FALSE; done := TRUE; RETURN NEXT result; END LOOP; -- Fail if the user has too many items with specific circ_modifiers checked out FOR out_by_circ_mod IN SELECT * FROM config.circ_matrix_circ_mod_test WHERE matchpoint = circ_test.id LOOP SELECT INTO items_out COUNT(*) FROM action.circulation circ JOIN asset.copy cp ON (cp.id = circ.target_copy) WHERE circ.usr = match_user AND circ.circ_lib IN ( SELECT * FROM explode_array(context_org_list) ) AND circ.checkin_time IS NULL AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL) 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); IF items_out >= out_by_circ_mod.items_out THEN result.fail_part := 'config.circ_matrix_circ_mod_test'; result.success := FALSE; done := TRUE; RETURN NEXT result; END IF; END LOOP; -- If we passed everything, return the successful matchpoint id IF NOT done THEN RETURN NEXT result; END IF; RETURN; END; $func$ LANGUAGE plpgsql; 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$ DECLARE matchpoint_id INT; user_object actor.usr%ROWTYPE; age_protect_object config.rule_age_hold_protect%ROWTYPE; standing_penalty config.standing_penalty%ROWTYPE; transit_range_ou_type actor.org_unit_type%ROWTYPE; transit_source actor.org_unit%ROWTYPE; item_object asset.copy%ROWTYPE; ou_skip actor.org_unit_setting%ROWTYPE; result action.matrix_test_result; hold_test config.hold_matrix_matchpoint%ROWTYPE; hold_count INT; hold_transit_prox INT; frozen_hold_count INT; context_org_list INT[]; done BOOL := FALSE; BEGIN SELECT INTO user_object * FROM actor.usr WHERE id = match_user; SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( pickup_ou ); result.success := TRUE; -- Fail if we couldn't find a user IF user_object.id IS NULL THEN result.fail_part := 'no_user'; result.success := FALSE; done := TRUE; RETURN NEXT result; RETURN; END IF; SELECT INTO item_object * FROM asset.copy WHERE id = match_item; -- Fail if we couldn't find a copy IF item_object.id IS NULL THEN result.fail_part := 'no_item'; result.success := FALSE; done := TRUE; RETURN NEXT result; RETURN; END IF; SELECT INTO matchpoint_id action.find_hold_matrix_matchpoint(pickup_ou, request_ou, match_item, match_user, match_requestor); result.matchpoint := matchpoint_id; SELECT INTO ou_skip * FROM actor.org_unit_setting WHERE name = 'circ.holds.target_skip_me' AND org_unit = item_object.circ_lib; -- Fail if the circ_lib for the item has circ.holds.target_skip_me set to true IF ou_skip.id IS NOT NULL AND ou_skip.value = 'true' THEN result.fail_part := 'circ.holds.target_skip_me'; result.success := FALSE; done := TRUE; RETURN NEXT result; RETURN; END IF; -- Fail if user is barred IF user_object.barred IS TRUE THEN result.fail_part := 'actor.usr.barred'; result.success := FALSE; done := TRUE; RETURN NEXT result; RETURN; END IF; -- Fail if we couldn't find any matchpoint (requires a default) IF matchpoint_id IS NULL THEN result.fail_part := 'no_matchpoint'; result.success := FALSE; done := TRUE; RETURN NEXT result; RETURN; END IF; SELECT INTO hold_test * FROM config.hold_matrix_matchpoint WHERE id = matchpoint_id; IF hold_test.holdable IS FALSE THEN result.fail_part := 'config.hold_matrix_test.holdable'; result.success := FALSE; done := TRUE; RETURN NEXT result; END IF; IF hold_test.transit_range IS NOT NULL THEN SELECT INTO transit_range_ou_type * FROM actor.org_unit_type WHERE id = hold_test.transit_range; IF hold_test.distance_is_from_owner THEN 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; ELSE SELECT INTO transit_source * FROM actor.org_unit WHERE id = item_object.circ_lib; END IF; PERFORM * FROM actor.org_unit_descendants( transit_source.id, transit_range_ou_type.depth ) WHERE id = pickup_ou; IF NOT FOUND THEN result.fail_part := 'transit_range'; result.success := FALSE; done := TRUE; RETURN NEXT result; END IF; END IF; FOR standing_penalty IN SELECT DISTINCT csp.* FROM actor.usr_standing_penalty usp JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty) WHERE usr = match_user AND usp.org_unit IN ( SELECT * FROM explode_array(context_org_list) ) AND (usp.stop_date IS NULL or usp.stop_date > NOW()) AND csp.block_list LIKE '%HOLD%' LOOP result.fail_part := standing_penalty.name; result.success := FALSE; done := TRUE; RETURN NEXT result; END LOOP; IF hold_test.stop_blocked_user IS TRUE THEN FOR standing_penalty IN SELECT DISTINCT csp.* FROM actor.usr_standing_penalty usp JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty) WHERE usr = match_user AND usp.org_unit IN ( SELECT * FROM explode_array(context_org_list) ) AND (usp.stop_date IS NULL or usp.stop_date > NOW()) AND csp.block_list LIKE '%CIRC%' LOOP result.fail_part := standing_penalty.name; result.success := FALSE; done := TRUE; RETURN NEXT result; END LOOP; END IF; IF hold_test.max_holds IS NOT NULL THEN SELECT INTO hold_count COUNT(*) FROM action.hold_request WHERE usr = match_user AND fulfillment_time IS NULL AND cancel_time IS NULL AND CASE WHEN hold_test.include_frozen_holds THEN TRUE ELSE frozen IS FALSE END; IF hold_count >= hold_test.max_holds THEN result.fail_part := 'config.hold_matrix_test.max_holds'; result.success := FALSE; done := TRUE; RETURN NEXT result; END IF; END IF; IF item_object.age_protect IS NOT NULL THEN SELECT INTO age_protect_object * FROM config.rule_age_hold_protect WHERE id = item_object.age_protect; IF item_object.create_date + age_protect_object.age > NOW() THEN IF hold_test.distance_is_from_owner THEN SELECT INTO hold_transit_prox prox FROM actor.org_unit_prox WHERE from_org = item_cn_object.owning_lib AND to_org = pickup_ou; ELSE SELECT INTO hold_transit_prox prox FROM actor.org_unit_prox WHERE from_org = item_object.circ_lib AND to_org = pickup_ou; END IF; IF hold_transit_prox > age_protect_object.prox THEN result.fail_part := 'config.rule_age_hold_protect.prox'; result.success := FALSE; done := TRUE; RETURN NEXT result; END IF; END IF; END IF; IF NOT done THEN RETURN NEXT result; END IF; RETURN; END; $func$ LANGUAGE plpgsql; COMMIT;