3 SELECT evergreen.upgrade_deps_block_check('0759', :eg_version);
5 CREATE TABLE actor.org_unit_proximity_adjustment (
7 item_circ_lib INT REFERENCES actor.org_unit (id),
8 item_owning_lib INT REFERENCES actor.org_unit (id),
9 copy_location INT REFERENCES asset.copy_location (id),
10 hold_pickup_lib INT REFERENCES actor.org_unit (id),
11 hold_request_lib INT REFERENCES actor.org_unit (id),
12 pos INT NOT NULL DEFAULT 0,
13 absolute_adjustment BOOL NOT NULL DEFAULT FALSE,
14 prox_adjustment NUMERIC,
15 circ_mod TEXT, -- REFERENCES config.circ_modifier (code),
16 CONSTRAINT prox_adj_criterium CHECK (COALESCE(item_circ_lib::TEXT,item_owning_lib::TEXT,copy_location::TEXT,hold_pickup_lib::TEXT,hold_request_lib::TEXT,circ_mod) IS NOT NULL)
18 CREATE UNIQUE INDEX prox_adj_once_idx ON actor.org_unit_proximity_adjustment (item_circ_lib,item_owning_lib,copy_location,hold_pickup_lib,hold_request_lib,circ_mod);
19 CREATE INDEX prox_adj_circ_lib_idx ON actor.org_unit_proximity_adjustment (item_circ_lib);
20 CREATE INDEX prox_adj_owning_lib_idx ON actor.org_unit_proximity_adjustment (item_owning_lib);
21 CREATE INDEX prox_adj_copy_location_idx ON actor.org_unit_proximity_adjustment (copy_location);
22 CREATE INDEX prox_adj_pickup_lib_idx ON actor.org_unit_proximity_adjustment (hold_pickup_lib);
23 CREATE INDEX prox_adj_request_lib_idx ON actor.org_unit_proximity_adjustment (hold_request_lib);
24 CREATE INDEX prox_adj_circ_mod_idx ON actor.org_unit_proximity_adjustment (circ_mod);
26 CREATE OR REPLACE FUNCTION actor.org_unit_ancestors_distance( INT ) RETURNS TABLE (id INT, distance INT) AS $$
27 WITH RECURSIVE org_unit_ancestors_distance(id, distance) AS (
30 SELECT ou.parent_ou, ouad.distance+1
31 FROM actor.org_unit ou JOIN org_unit_ancestors_distance ouad ON (ou.id = ouad.id)
32 WHERE ou.parent_ou IS NOT NULL
34 SELECT * FROM org_unit_ancestors_distance;
35 $$ LANGUAGE SQL STABLE ROWS 1;
37 CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity(
40 copy_context_ou INT DEFAULT NULL
41 -- TODO maybe? hold_context_ou INT DEFAULT NULL. This would optionally
42 -- support an "ahprox" measurement: adjust prox between copy circ lib and
43 -- hold request lib, but I'm unsure whether to use this theoretical
44 -- argument only in the baseline calculation or later in the other
45 -- queries in this function.
46 ) RETURNS NUMERIC AS $f$
48 aoupa actor.org_unit_proximity_adjustment%ROWTYPE;
49 ahr action.hold_request%ROWTYPE;
50 acp asset.copy%ROWTYPE;
51 acn asset.call_number%ROWTYPE;
52 acl asset.copy_location%ROWTYPE;
53 baseline_prox NUMERIC;
63 SELECT * INTO ahr FROM action.hold_request WHERE id = ahr_id;
64 SELECT * INTO acp FROM asset.copy WHERE id = acp_id;
65 SELECT * INTO acn FROM asset.call_number WHERE id = acp.call_number;
66 SELECT * INTO acl FROM asset.copy_location WHERE id = acp.location;
68 IF copy_context_ou IS NULL THEN
69 copy_context_ou := acp.circ_lib;
72 -- First, gather the baseline proximity of "here" to pickup lib
73 SELECT prox INTO baseline_prox FROM actor.org_unit_proximity WHERE from_org = copy_context_ou AND to_org = ahr.pickup_lib;
75 -- Find any absolute adjustments, and set the baseline prox to that
76 SELECT adj.* INTO aoupa
77 FROM actor.org_unit_proximity_adjustment adj
78 LEFT JOIN actor.org_unit_ancestors_distance(copy_context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib)
79 LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib)
80 LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acn_ol.id = adj.copy_location)
81 LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
82 LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
83 WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND
84 absolute_adjustment AND
85 COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
87 COALESCE(acp_cl.distance,999)
88 + COALESCE(acn_ol.distance,999)
89 + COALESCE(acl_ol.distance,999)
90 + COALESCE(ahr_pl.distance,999)
91 + COALESCE(ahr_rl.distance,999),
96 baseline_prox := aoupa.prox_adjustment;
99 -- Now find any relative adjustments, and change the baseline prox based on them
102 FROM actor.org_unit_proximity_adjustment adj
103 LEFT JOIN actor.org_unit_ancestors_distance(copy_context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib)
104 LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib)
105 LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acn_ol.id = adj.copy_location)
106 LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
107 LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
108 WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND
109 NOT absolute_adjustment AND
110 COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
112 baseline_prox := baseline_prox + aoupa.prox_adjustment;
115 RETURN baseline_prox;
117 $f$ LANGUAGE PLPGSQL;
119 ALTER TABLE actor.org_unit_proximity_adjustment
120 ADD CONSTRAINT actor_org_unit_proximity_adjustment_circ_mod_fkey
121 FOREIGN KEY (circ_mod) REFERENCES config.circ_modifier (code)
122 DEFERRABLE INITIALLY DEFERRED;
124 ALTER TABLE action.hold_copy_map ADD COLUMN proximity NUMERIC;