1 --Upgrade Script for 2.4.5 to 2.4.6
2 \set eg_version '''2.4.6'''
4 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.4.6', :eg_version);
6 SELECT evergreen.upgrade_deps_block_check('0852', :eg_version);
8 CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity(
11 copy_context_ou INT DEFAULT NULL
12 -- TODO maybe? hold_context_ou INT DEFAULT NULL. This would optionally
13 -- support an "ahprox" measurement: adjust prox between copy circ lib and
14 -- hold request lib, but I'm unsure whether to use this theoretical
15 -- argument only in the baseline calculation or later in the other
16 -- queries in this function.
17 ) RETURNS NUMERIC AS $f$
19 aoupa actor.org_unit_proximity_adjustment%ROWTYPE;
20 ahr action.hold_request%ROWTYPE;
21 acp asset.copy%ROWTYPE;
22 acn asset.call_number%ROWTYPE;
23 acl asset.copy_location%ROWTYPE;
24 baseline_prox NUMERIC;
34 SELECT * INTO ahr FROM action.hold_request WHERE id = ahr_id;
35 SELECT * INTO acp FROM asset.copy WHERE id = acp_id;
36 SELECT * INTO acn FROM asset.call_number WHERE id = acp.call_number;
37 SELECT * INTO acl FROM asset.copy_location WHERE id = acp.location;
39 IF copy_context_ou IS NULL THEN
40 copy_context_ou := acp.circ_lib;
43 -- First, gather the baseline proximity of "here" to pickup lib
44 SELECT prox INTO baseline_prox FROM actor.org_unit_proximity WHERE from_org = copy_context_ou AND to_org = ahr.pickup_lib;
46 -- Find any absolute adjustments, and set the baseline prox to that
47 SELECT adj.* INTO aoupa
48 FROM actor.org_unit_proximity_adjustment adj
49 LEFT JOIN actor.org_unit_ancestors_distance(copy_context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib)
50 LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib)
51 LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acl_ol.id = adj.copy_location)
52 LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
53 LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
54 WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND
55 (adj.item_circ_lib IS NULL OR adj.item_circ_lib = acp_cl.id) AND
56 (adj.item_owning_lib IS NULL OR adj.item_owning_lib = acn_ol.id) AND
57 (adj.copy_location IS NULL OR adj.copy_location = acl_ol.id) AND
58 (adj.hold_pickup_lib IS NULL OR adj.hold_pickup_lib = ahr_pl.id) AND
59 (adj.hold_request_lib IS NULL OR adj.hold_request_lib = ahr_rl.id) AND
60 absolute_adjustment AND
61 COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
63 COALESCE(acp_cl.distance,999)
64 + COALESCE(acn_ol.distance,999)
65 + COALESCE(acl_ol.distance,999)
66 + COALESCE(ahr_pl.distance,999)
67 + COALESCE(ahr_rl.distance,999),
72 baseline_prox := aoupa.prox_adjustment;
75 -- Now find any relative adjustments, and change the baseline prox based on them
78 FROM actor.org_unit_proximity_adjustment adj
79 LEFT JOIN actor.org_unit_ancestors_distance(copy_context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib)
80 LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib)
81 LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acn_ol.id = adj.copy_location)
82 LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
83 LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
84 WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND
85 (adj.item_circ_lib IS NULL OR adj.item_circ_lib = acp_cl.id) AND
86 (adj.item_owning_lib IS NULL OR adj.item_owning_lib = acn_ol.id) AND
87 (adj.copy_location IS NULL OR adj.copy_location = acl_ol.id) AND
88 (adj.hold_pickup_lib IS NULL OR adj.hold_pickup_lib = ahr_pl.id) AND
89 (adj.hold_request_lib IS NULL OR adj.hold_request_lib = ahr_rl.id) AND
90 NOT absolute_adjustment AND
91 COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
93 baseline_prox := baseline_prox + aoupa.prox_adjustment;
101 CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity(
104 copy_context_ou INT DEFAULT NULL
105 -- TODO maybe? hold_context_ou INT DEFAULT NULL. This would optionally
106 -- support an "ahprox" measurement: adjust prox between copy circ lib and
107 -- hold request lib, but I'm unsure whether to use this theoretical
108 -- argument only in the baseline calculation or later in the other
109 -- queries in this function.
110 ) RETURNS NUMERIC AS $f$
112 aoupa actor.org_unit_proximity_adjustment%ROWTYPE;
113 ahr action.hold_request%ROWTYPE;
114 acp asset.copy%ROWTYPE;
115 acn asset.call_number%ROWTYPE;
116 acl asset.copy_location%ROWTYPE;
117 baseline_prox NUMERIC;
127 SELECT * INTO ahr FROM action.hold_request WHERE id = ahr_id;
128 SELECT * INTO acp FROM asset.copy WHERE id = acp_id;
129 SELECT * INTO acn FROM asset.call_number WHERE id = acp.call_number;
130 SELECT * INTO acl FROM asset.copy_location WHERE id = acp.location;
132 IF copy_context_ou IS NULL THEN
133 copy_context_ou := acp.circ_lib;
136 -- First, gather the baseline proximity of "here" to pickup lib
137 SELECT prox INTO baseline_prox FROM actor.org_unit_proximity WHERE from_org = copy_context_ou AND to_org = ahr.pickup_lib;
139 -- Find any absolute adjustments, and set the baseline prox to that
140 SELECT adj.* INTO aoupa
141 FROM actor.org_unit_proximity_adjustment adj
142 LEFT JOIN actor.org_unit_ancestors_distance(copy_context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib)
143 LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib)
144 LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acl_ol.id = adj.copy_location)
145 LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
146 LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
147 WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND
148 (adj.item_circ_lib IS NULL OR adj.item_circ_lib = acp_cl.id) AND
149 (adj.item_owning_lib IS NULL OR adj.item_owning_lib = acn_ol.id) AND
150 (adj.copy_location IS NULL OR adj.copy_location = acl_ol.id) AND
151 (adj.hold_pickup_lib IS NULL OR adj.hold_pickup_lib = ahr_pl.id) AND
152 (adj.hold_request_lib IS NULL OR adj.hold_request_lib = ahr_rl.id) AND
153 absolute_adjustment AND
154 COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
156 COALESCE(acp_cl.distance,999)
157 + COALESCE(acn_ol.distance,999)
158 + COALESCE(acl_ol.distance,999)
159 + COALESCE(ahr_pl.distance,999)
160 + COALESCE(ahr_rl.distance,999),
165 baseline_prox := aoupa.prox_adjustment;
168 -- Now find any relative adjustments, and change the baseline prox based on them
171 FROM actor.org_unit_proximity_adjustment adj
172 LEFT JOIN actor.org_unit_ancestors_distance(copy_context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib)
173 LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib)
174 LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acn_ol.id = adj.copy_location)
175 LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
176 LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
177 WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND
178 (adj.item_circ_lib IS NULL OR adj.item_circ_lib = acp_cl.id) AND
179 (adj.item_owning_lib IS NULL OR adj.item_owning_lib = acn_ol.id) AND
180 (adj.copy_location IS NULL OR adj.copy_location = acl_ol.id) AND
181 (adj.hold_pickup_lib IS NULL OR adj.hold_pickup_lib = ahr_pl.id) AND
182 (adj.hold_request_lib IS NULL OR adj.hold_request_lib = ahr_rl.id) AND
183 NOT absolute_adjustment AND
184 COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
186 baseline_prox := baseline_prox + aoupa.prox_adjustment;
189 RETURN baseline_prox;
191 $f$ LANGUAGE PLPGSQL;