From 72cb6a5fe4f431aace242d6f5010148c84c7655e Mon Sep 17 00:00:00 2001 From: miker Date: Wed, 6 Oct 2010 03:07:30 +0000 Subject: [PATCH] Add a switch to turn on strict OU matching for all OUs required by a hold matchpoint git-svn-id: svn://svn.open-ils.org/ILS/trunk@18191 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/examples/fm_IDL.xml | 1 + Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/110.hold_matrix.sql | 31 +++- .../Pg/upgrade/0430.schema.strict_ou_test.sql | 143 ++++++++++++++++++ .../global/config/hold_matrix_matchpoint.tt2 | 2 +- 5 files changed, 172 insertions(+), 7 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0430.schema.strict_ou_test.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 8fd048ce0a..18fb37e7fd 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -999,6 +999,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index faabe40fae..3ae50de58e 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -70,7 +70,7 @@ CREATE TABLE config.upgrade_log ( install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -INSERT INTO config.upgrade_log (version) VALUES ('0428'); -- miker +INSERT INTO config.upgrade_log (version) VALUES ('0430'); -- miker 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 7a67e2743b..73298be17a 100644 --- a/Open-ILS/src/sql/Pg/110.hold_matrix.sql +++ b/Open-ILS/src/sql/Pg/110.hold_matrix.sql @@ -31,6 +31,7 @@ BEGIN; CREATE TABLE config.hold_matrix_matchpoint ( id SERIAL PRIMARY KEY, active BOOL NOT NULL DEFAULT TRUE, + strict_ou_match BOOL NOT NULL DEFAULT FALSE, user_home_ou INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, -- Set to the top OU for the matchpoint applicability range; we can use org_unit_prox to choose the "best" request_ou INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, -- Set to the top OU for the matchpoint applicability range; we can use org_unit_prox to choose the "best" pickup_ou INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, -- Set to the top OU for the matchpoint applicability range; we can use org_unit_prox to choose the "best" @@ -128,27 +129,47 @@ BEGIN -- caclulate the rule match weight IF current_mp.item_owning_ou IS NOT NULL THEN - SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.item_owning_ou, item_cn_object.owning_lib)::FLOAT + 1.0)::FLOAT; + IF NOT current_mp.strict_ou_match THEN + SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.item_owning_ou, item_cn_object.owning_lib)::FLOAT + 1.0)::FLOAT; + ELSE + tmp_weight := CASE WHEN current_mp.item_owning_ou = item_cn_object.owning_lib THEN 1.0 ELSE 0.0 END; + END IF; current_mp_weight := current_mp_weight - tmp_weight; END IF; IF current_mp.item_circ_ou IS NOT NULL THEN - SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.item_circ_ou, item_object.circ_lib)::FLOAT + 1.0)::FLOAT; + IF NOT current_mp.strict_ou_match THEN + SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.item_circ_ou, item_object.circ_lib)::FLOAT + 1.0)::FLOAT; + ELSE + tmp_weight := CASE WHEN current_mp.item_circ_ou = item_object.circ_lib THEN 1.0 ELSE 0.0 END; + END IF; current_mp_weight := current_mp_weight - tmp_weight; END IF; IF current_mp.pickup_ou IS NOT NULL THEN - SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.pickup_ou, pickup_ou)::FLOAT + 1.0)::FLOAT; + IF NOT current_mp.strict_ou_match THEN + SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.pickup_ou, pickup_ou)::FLOAT + 1.0)::FLOAT; + ELSE + tmp_weight := CASE WHEN current_mp.pickup_ou = pickiup_ou THEN 1.0 ELSE 0.0 END; + END IF; current_mp_weight := current_mp_weight - tmp_weight; END IF; IF current_mp.request_ou IS NOT NULL THEN - SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.request_ou, request_ou)::FLOAT + 1.0)::FLOAT; + IF NOT current_mp.strict_ou_match THEN + SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.request_ou, request_ou)::FLOAT + 1.0)::FLOAT; + ELSE + tmp_weight := CASE WHEN current_mp.request_ou = request_ou THEN 1.0 ELSE 0.0 END; + END IF; current_mp_weight := current_mp_weight - tmp_weight; END IF; IF current_mp.user_home_ou IS NOT NULL THEN - SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.user_home_ou, user_object.home_ou)::FLOAT + 1.0)::FLOAT; + IF NOT current_mp.strict_ou_match THEN + SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.user_home_ou, user_object.home_ou)::FLOAT + 1.0)::FLOAT; + ELSE + tmp_weight := CASE WHEN current_mp.user_home_ou = user_object.home_lib THEN 1.0 ELSE 0.0 END; + END IF; current_mp_weight := current_mp_weight - tmp_weight; END IF; diff --git a/Open-ILS/src/sql/Pg/upgrade/0430.schema.strict_ou_test.sql b/Open-ILS/src/sql/Pg/upgrade/0430.schema.strict_ou_test.sql new file mode 100644 index 0000000000..a97651d92c --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0430.schema.strict_ou_test.sql @@ -0,0 +1,143 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0430'); -- miker + +ALTER TABLE config.hold_matrix_matchpoint ADD COLUMN strict_ou_match BOOL NOT NULL DEFAULT FALSE; + +CREATE OR REPLACE FUNCTION action.find_hold_matrix_matchpoint( pickup_ou INT, request_ou INT, match_item BIGINT, match_user INT, match_requestor INT ) RETURNS INT AS $func$ +DECLARE + current_requestor_group permission.grp_tree%ROWTYPE; + requestor_object actor.usr%ROWTYPE; + user_object actor.usr%ROWTYPE; + item_object asset.copy%ROWTYPE; + item_cn_object asset.call_number%ROWTYPE; + rec_descriptor metabib.rec_descriptor%ROWTYPE; + current_mp_weight FLOAT; + matchpoint_weight FLOAT; + tmp_weight FLOAT; + current_mp config.hold_matrix_matchpoint%ROWTYPE; + matchpoint config.hold_matrix_matchpoint%ROWTYPE; +BEGIN + SELECT INTO user_object * FROM actor.usr WHERE id = match_user; + SELECT INTO requestor_object * FROM actor.usr WHERE id = match_requestor; + SELECT INTO item_object * FROM asset.copy WHERE id = match_item; + SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number; + SELECT INTO rec_descriptor r.* FROM metabib.rec_descriptor r WHERE r.record = item_cn_object.record; + + PERFORM * FROM config.internal_flag WHERE name = 'circ.holds.usr_not_requestor' AND enabled; + + IF NOT FOUND THEN + SELECT INTO current_requestor_group * FROM permission.grp_tree WHERE id = requestor_object.profile; + ELSE + SELECT INTO current_requestor_group * FROM permission.grp_tree WHERE id = user_object.profile; + END IF; + + LOOP + -- for each potential matchpoint for this ou and group ... + FOR current_mp IN + SELECT m.* + FROM config.hold_matrix_matchpoint m + WHERE m.requestor_grp = current_requestor_group.id AND m.active + ORDER BY CASE WHEN m.circ_modifier IS NOT NULL THEN 16 ELSE 0 END + + CASE WHEN m.juvenile_flag IS NOT NULL THEN 16 ELSE 0 END + + CASE WHEN m.marc_type IS NOT NULL THEN 8 ELSE 0 END + + CASE WHEN m.marc_form IS NOT NULL THEN 4 ELSE 0 END + + CASE WHEN m.marc_vr_format IS NOT NULL THEN 2 ELSE 0 END + + CASE WHEN m.ref_flag IS NOT NULL THEN 1 ELSE 0 END DESC LOOP + + current_mp_weight := 5.0; + + IF current_mp.circ_modifier IS NOT NULL THEN + CONTINUE WHEN current_mp.circ_modifier <> item_object.circ_modifier OR item_object.circ_modifier IS NULL; + END IF; + + IF current_mp.marc_type IS NOT NULL THEN + IF item_object.circ_as_type IS NOT NULL THEN + CONTINUE WHEN current_mp.marc_type <> item_object.circ_as_type; + ELSE + CONTINUE WHEN current_mp.marc_type <> rec_descriptor.item_type; + END IF; + END IF; + + IF current_mp.marc_form IS NOT NULL THEN + CONTINUE WHEN current_mp.marc_form <> rec_descriptor.item_form; + END IF; + + IF current_mp.marc_vr_format IS NOT NULL THEN + CONTINUE WHEN current_mp.marc_vr_format <> rec_descriptor.vr_format; + END IF; + + IF current_mp.juvenile_flag IS NOT NULL THEN + CONTINUE WHEN current_mp.juvenile_flag <> user_object.juvenile; + END IF; + + IF current_mp.ref_flag IS NOT NULL THEN + CONTINUE WHEN current_mp.ref_flag <> item_object.ref; + END IF; + + + -- caclulate the rule match weight + IF current_mp.item_owning_ou IS NOT NULL THEN + IF NOT current_mp.strict_ou_match THEN + SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.item_owning_ou, item_cn_object.owning_lib)::FLOAT + 1.0)::FLOAT; + ELSE + tmp_weight := CASE WHEN current_mp.item_owning_ou = item_cn_object.owning_lib THEN 1.0 ELSE 0.0 END; + END IF; + current_mp_weight := current_mp_weight - tmp_weight; + END IF; + + IF current_mp.item_circ_ou IS NOT NULL THEN + IF NOT current_mp.strict_ou_match THEN + SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.item_circ_ou, item_object.circ_lib)::FLOAT + 1.0)::FLOAT; + ELSE + tmp_weight := CASE WHEN current_mp.item_circ_ou = item_object.circ_lib THEN 1.0 ELSE 0.0 END; + END IF; + current_mp_weight := current_mp_weight - tmp_weight; + END IF; + + IF current_mp.pickup_ou IS NOT NULL THEN + IF NOT current_mp.strict_ou_match THEN + SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.pickup_ou, pickup_ou)::FLOAT + 1.0)::FLOAT; + ELSE + tmp_weight := CASE WHEN current_mp.pickup_ou = pickiup_ou THEN 1.0 ELSE 0.0 END; + END IF; + current_mp_weight := current_mp_weight - tmp_weight; + END IF; + + IF current_mp.request_ou IS NOT NULL THEN + IF NOT current_mp.strict_ou_match THEN + SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.request_ou, request_ou)::FLOAT + 1.0)::FLOAT; + ELSE + tmp_weight := CASE WHEN current_mp.request_ou = request_ou THEN 1.0 ELSE 0.0 END; + END IF; + current_mp_weight := current_mp_weight - tmp_weight; + END IF; + + IF current_mp.user_home_ou IS NOT NULL THEN + IF NOT current_mp.strict_ou_match THEN + SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.user_home_ou, user_object.home_ou)::FLOAT + 1.0)::FLOAT; + ELSE + tmp_weight := CASE WHEN current_mp.user_home_ou = user_object.home_lib THEN 1.0 ELSE 0.0 END; + END IF; + current_mp_weight := current_mp_weight - tmp_weight; + END IF; + + -- set the matchpoint if we found the best one + IF matchpoint_weight IS NULL OR matchpoint_weight > current_mp_weight THEN + matchpoint = current_mp; + matchpoint_weight = current_mp_weight; + END IF; + + END LOOP; + + EXIT WHEN current_requestor_group.parent IS NULL OR matchpoint.id IS NOT NULL; + + SELECT INTO current_requestor_group * FROM permission.grp_tree WHERE id = current_requestor_group.parent; + END LOOP; + + RETURN matchpoint.id; +END; +$func$ LANGUAGE plpgsql; + +COMMIT; + diff --git a/Open-ILS/web/templates/default/conify/global/config/hold_matrix_matchpoint.tt2 b/Open-ILS/web/templates/default/conify/global/config/hold_matrix_matchpoint.tt2 index 296efd0917..ab87bb155d 100644 --- a/Open-ILS/web/templates/default/conify/global/config/hold_matrix_matchpoint.tt2 +++ b/Open-ILS/web/templates/default/conify/global/config/hold_matrix_matchpoint.tt2 @@ -18,7 +18,7 @@