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