3 SELECT evergreen.upgrade_deps_block_check('0852', :eg_version);
5 CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity(
8 copy_context_ou INT DEFAULT NULL
9 -- TODO maybe? hold_context_ou INT DEFAULT NULL. This would optionally
10 -- support an "ahprox" measurement: adjust prox between copy circ lib and
11 -- hold request lib, but I'm unsure whether to use this theoretical
12 -- argument only in the baseline calculation or later in the other
13 -- queries in this function.
14 ) RETURNS NUMERIC AS $f$
16 aoupa actor.org_unit_proximity_adjustment%ROWTYPE;
17 ahr action.hold_request%ROWTYPE;
18 acp asset.copy%ROWTYPE;
19 acn asset.call_number%ROWTYPE;
20 acl asset.copy_location%ROWTYPE;
21 baseline_prox NUMERIC;
31 SELECT * INTO ahr FROM action.hold_request WHERE id = ahr_id;
32 SELECT * INTO acp FROM asset.copy WHERE id = acp_id;
33 SELECT * INTO acn FROM asset.call_number WHERE id = acp.call_number;
34 SELECT * INTO acl FROM asset.copy_location WHERE id = acp.location;
36 IF copy_context_ou IS NULL THEN
37 copy_context_ou := acp.circ_lib;
40 -- First, gather the baseline proximity of "here" to pickup lib
41 SELECT prox INTO baseline_prox FROM actor.org_unit_proximity WHERE from_org = copy_context_ou AND to_org = ahr.pickup_lib;
43 -- Find any absolute adjustments, and set the baseline prox to that
44 SELECT adj.* INTO aoupa
45 FROM actor.org_unit_proximity_adjustment adj
46 LEFT JOIN actor.org_unit_ancestors_distance(copy_context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib)
47 LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib)
48 LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acl_ol.id = adj.copy_location)
49 LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
50 LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
51 WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND
52 (adj.item_circ_lib IS NULL OR adj.item_circ_lib = acp_cl.id) AND
53 (adj.item_owning_lib IS NULL OR adj.item_owning_lib = acn_ol.id) AND
54 (adj.copy_location IS NULL OR adj.copy_location = acl_ol.id) AND
55 (adj.hold_pickup_lib IS NULL OR adj.hold_pickup_lib = ahr_pl.id) AND
56 (adj.hold_request_lib IS NULL OR adj.hold_request_lib = ahr_rl.id) AND
57 absolute_adjustment AND
58 COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
60 COALESCE(acp_cl.distance,999)
61 + COALESCE(acn_ol.distance,999)
62 + COALESCE(acl_ol.distance,999)
63 + COALESCE(ahr_pl.distance,999)
64 + COALESCE(ahr_rl.distance,999),
69 baseline_prox := aoupa.prox_adjustment;
72 -- Now find any relative adjustments, and change the baseline prox based on them
75 FROM actor.org_unit_proximity_adjustment adj
76 LEFT JOIN actor.org_unit_ancestors_distance(copy_context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib)
77 LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib)
78 LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acn_ol.id = adj.copy_location)
79 LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
80 LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
81 WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND
82 (adj.item_circ_lib IS NULL OR adj.item_circ_lib = acp_cl.id) AND
83 (adj.item_owning_lib IS NULL OR adj.item_owning_lib = acn_ol.id) AND
84 (adj.copy_location IS NULL OR adj.copy_location = acl_ol.id) AND
85 (adj.hold_pickup_lib IS NULL OR adj.hold_pickup_lib = ahr_pl.id) AND
86 (adj.hold_request_lib IS NULL OR adj.hold_request_lib = ahr_rl.id) AND
87 NOT absolute_adjustment AND
88 COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
90 baseline_prox := baseline_prox + aoupa.prox_adjustment;