From 83eab28ec4af01be9ef73a99c03d915b411c419b Mon Sep 17 00:00:00 2001 From: gmc Date: Tue, 10 Aug 2010 18:23:26 +0000 Subject: [PATCH] fix age protection proximity test for in-DB circ Patch from John Craig. Signed-off-by: Galen Charlton git-svn-id: svn://svn.open-ils.org/ILS/trunk@17153 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/110.hold_matrix.sql | 4 +- .../0368.schema.fix_age_protect_prox_test.sql | 183 ++++++++++++++++++ 3 files changed, 186 insertions(+), 3 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0368.schema.fix_age_protect_prox_test.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index febd65e89c..666471bf8a 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -68,7 +68,7 @@ CREATE TABLE config.upgrade_log ( install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -INSERT INTO config.upgrade_log (version) VALUES ('0367'); -- miker +INSERT INTO config.upgrade_log (version) VALUES ('0368'); -- gmc CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/110.hold_matrix.sql b/Open-ILS/src/sql/Pg/110.hold_matrix.sql index 3250a8ab88..aba9110aac 100644 --- a/Open-ILS/src/sql/Pg/110.hold_matrix.sql +++ b/Open-ILS/src/sql/Pg/110.hold_matrix.sql @@ -320,9 +320,9 @@ BEGIN 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; + SELECT INTO hold_transit_prox prox FROM actor.org_unit_proximity 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; + SELECT INTO hold_transit_prox prox FROM actor.org_unit_proximity WHERE from_org = item_object.circ_lib AND to_org = pickup_ou; END IF; IF hold_transit_prox > age_protect_object.prox THEN diff --git a/Open-ILS/src/sql/Pg/upgrade/0368.schema.fix_age_protect_prox_test.sql b/Open-ILS/src/sql/Pg/upgrade/0368.schema.fix_age_protect_prox_test.sql new file mode 100644 index 0000000000..5de06a1458 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0368.schema.fix_age_protect_prox_test.sql @@ -0,0 +1,183 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0368'); -- gmc + +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_proximity WHERE from_org = item_cn_object.owning_lib AND to_org = pickup_ou; + ELSE + SELECT INTO hold_transit_prox prox FROM actor.org_unit_proximity 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; + -- 2.43.2