From 657e7f3826a5d0e8841bc4286ae37a49ec18c1ec Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Thu, 16 Jan 2014 14:11:34 -0500 Subject: [PATCH] Upgrade script Signed-off-by: Mike Rylander Signed-off-by: Lebbeous Fogle-Weekley Signed-off-by: Mike Rylander --- .../Pg/upgrade/XXXX.function.prox_adjust.sql | 95 +++++++++++++++++++ 1 file changed, 95 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.function.prox_adjust.sql diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.function.prox_adjust.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.prox_adjust.sql new file mode 100644 index 0000000000..aeb03eab03 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.prox_adjust.sql @@ -0,0 +1,95 @@ +BEGIN; + +CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity( + ahr_id INT, + acp_id BIGINT, + copy_context_ou INT DEFAULT NULL + -- TODO maybe? hold_context_ou INT DEFAULT NULL. This would optionally + -- support an "ahprox" measurement: adjust prox between copy circ lib and + -- hold request lib, but I'm unsure whether to use this theoretical + -- argument only in the baseline calculation or later in the other + -- queries in this function. +) RETURNS NUMERIC AS $f$ +DECLARE + aoupa actor.org_unit_proximity_adjustment%ROWTYPE; + ahr action.hold_request%ROWTYPE; + acp asset.copy%ROWTYPE; + acn asset.call_number%ROWTYPE; + acl asset.copy_location%ROWTYPE; + baseline_prox NUMERIC; + + icl_list INT[]; + iol_list INT[]; + isl_list INT[]; + hpl_list INT[]; + hrl_list INT[]; + +BEGIN + + SELECT * INTO ahr FROM action.hold_request WHERE id = ahr_id; + SELECT * INTO acp FROM asset.copy WHERE id = acp_id; + SELECT * INTO acn FROM asset.call_number WHERE id = acp.call_number; + SELECT * INTO acl FROM asset.copy_location WHERE id = acp.location; + + IF copy_context_ou IS NULL THEN + copy_context_ou := acp.circ_lib; + END IF; + + -- First, gather the baseline proximity of "here" to pickup lib + SELECT prox INTO baseline_prox FROM actor.org_unit_proximity WHERE from_org = copy_context_ou AND to_org = ahr.pickup_lib; + + -- Find any absolute adjustments, and set the baseline prox to that + SELECT adj.* INTO aoupa + FROM actor.org_unit_proximity_adjustment adj + LEFT JOIN actor.org_unit_ancestors_distance(copy_context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib) + LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib) + LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acl_ol.id = adj.copy_location) + LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib) + LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib) + WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND + (adj.item_circ_lib IS NULL OR adj.item_circ_lib = acp_cl.id) AND + (adj.item_owning_lib IS NULL OR adj.item_owning_lib = acn_ol.id) AND + (adj.copy_location IS NULL OR adj.copy_location = acl_ol.id) AND + (adj.hold_pickup_lib IS NULL OR adj.hold_pickup_lib = ahr_pl.id) AND + (adj.hold_request_lib IS NULL OR adj.hold_request_lib = ahr_rl.id) AND + absolute_adjustment AND + COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL + ORDER BY + COALESCE(acp_cl.distance,999) + + COALESCE(acn_ol.distance,999) + + COALESCE(acl_ol.distance,999) + + COALESCE(ahr_pl.distance,999) + + COALESCE(ahr_rl.distance,999), + adj.pos + LIMIT 1; + + IF FOUND THEN + baseline_prox := aoupa.prox_adjustment; + END IF; + + -- Now find any relative adjustments, and change the baseline prox based on them + FOR aoupa IN + SELECT adj.* + FROM actor.org_unit_proximity_adjustment adj + LEFT JOIN actor.org_unit_ancestors_distance(copy_context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib) + LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib) + LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acn_ol.id = adj.copy_location) + LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib) + LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib) + WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND + (adj.item_circ_lib IS NULL OR adj.item_circ_lib = acp_cl.id) AND + (adj.item_owning_lib IS NULL OR adj.item_owning_lib = acn_ol.id) AND + (adj.copy_location IS NULL OR adj.copy_location = acl_ol.id) AND + (adj.hold_pickup_lib IS NULL OR adj.hold_pickup_lib = ahr_pl.id) AND + (adj.hold_request_lib IS NULL OR adj.hold_request_lib = ahr_rl.id) AND + NOT absolute_adjustment AND + COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL + LOOP + baseline_prox := baseline_prox + aoupa.prox_adjustment; + END LOOP; + + RETURN baseline_prox; +END; +$f$ LANGUAGE PLPGSQL; + +COMMIT; -- 2.43.2