3 CREATE TABLE actor.org_unit_proximity_adjustment (
5 item_circ_lib INT REFERENCES actor.org_unit (id),
6 item_owning_lib INT REFERENCES actor.org_unit (id),
7 copy_location INT REFERENCES asset.copy_location (id),
8 hold_pickup_lib INT REFERENCES actor.org_unit (id),
9 hold_request_lib INT REFERENCES actor.org_unit (id),
10 pos INT NOT NULL DEFAULT 0,
11 absolute_adjustment BOOL NOT NULL DEFAULT FALSE,
12 prox_adjustment NUMERIC,
13 circ_mod TEXT, -- REFERENCES config.circ_modifier (code),
14 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)
16 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);
17 CREATE INDEX prox_adj_circ_lib_idx ON actor.org_unit_proximity_adjustment (item_circ_lib);
18 CREATE INDEX prox_adj_owning_lib_idx ON actor.org_unit_proximity_adjustment (item_owning_lib);
19 CREATE INDEX prox_adj_copy_location_idx ON actor.org_unit_proximity_adjustment (copy_location);
20 CREATE INDEX prox_adj_pickup_lib_idx ON actor.org_unit_proximity_adjustment (hold_pickup_lib);
21 CREATE INDEX prox_adj_request_lib_idx ON actor.org_unit_proximity_adjustment (hold_request_lib);
22 CREATE INDEX prox_adj_circ_mod_idx ON actor.org_unit_proximity_adjustment (circ_mod);
24 CREATE OR REPLACE FUNCTION actor.org_unit_ancestors_distance( INT ) RETURNS TABLE (id INT, distance INT) AS $$
25 WITH RECURSIVE org_unit_ancestors_distance(id, distance) AS (
28 SELECT ou.parent_ou, ouad.distance+1
29 FROM actor.org_unit ou JOIN org_unit_ancestors_distance ouad ON (ou.id = ouad.id)
30 WHERE ou.parent_ou IS NOT NULL
32 SELECT * FROM org_unit_ancestors_distance;
33 $$ LANGUAGE SQL STABLE ROWS 1;
35 CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity(
38 copy_context_ou INT DEFAULT NULL
39 -- TODO maybe? hold_context_ou INT DEFAULT NULL. This would optionally
40 -- support an "ahprox" measurement: adjust prox between copy circ lib and
41 -- hold request lib, but I'm unsure whether to use this theoretical
42 -- argument only in the baseline calculation or later in the other
43 -- queries in this function.
44 ) RETURNS NUMERIC AS $f$
46 aoupa actor.org_unit_proximity_adjustment%ROWTYPE;
47 ahr action.hold_request%ROWTYPE;
48 acp asset.copy%ROWTYPE;
49 acn asset.call_number%ROWTYPE;
50 acl asset.copy_location%ROWTYPE;
51 baseline_prox NUMERIC;
61 SELECT * INTO ahr FROM action.hold_request WHERE id = ahr_id;
62 SELECT * INTO acp FROM asset.copy WHERE id = acp_id;
63 SELECT * INTO acn FROM asset.call_number WHERE id = acp.call_number;
64 SELECT * INTO acl FROM asset.copy_location WHERE id = acp.location;
66 IF copy_context_ou IS NULL THEN
67 copy_context_ou := acp.circ_lib;
70 -- First, gather the baseline proximity of "here" to pickup lib
71 SELECT prox INTO baseline_prox FROM actor.org_unit_proximity WHERE from_org = copy_context_ou AND to_org = ahr.pickup_lib;
73 -- Find any absolute adjustments, and set the baseline prox to that
74 SELECT adj.* INTO aoupa
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 absolute_adjustment AND
83 COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
85 COALESCE(acp_cl.distance,999)
86 + COALESCE(acn_ol.distance,999)
87 + COALESCE(acl_ol.distance,999)
88 + COALESCE(ahr_pl.distance,999)
89 + COALESCE(ahr_rl.distance,999),
94 baseline_prox := aoupa.prox_adjustment;
97 -- Now find any relative adjustments, and change the baseline prox based on them
100 FROM actor.org_unit_proximity_adjustment adj
101 LEFT JOIN actor.org_unit_ancestors_distance(copy_context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib)
102 LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib)
103 LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acn_ol.id = adj.copy_location)
104 LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
105 LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
106 WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND
107 NOT absolute_adjustment AND
108 COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
110 baseline_prox := baseline_prox + aoupa.prox_adjustment;
113 RETURN baseline_prox;
115 $f$ LANGUAGE PLPGSQL;
117 ALTER TABLE actor.org_unit_proximity_adjustment
118 ADD CONSTRAINT actor_org_unit_proximity_adjustment_circ_mod_fkey
119 FOREIGN KEY (circ_mod) REFERENCES config.circ_modifier (code)
120 DEFERRABLE INITIALLY DEFERRED;
122 ALTER TABLE action.hold_copy_map ADD COLUMN proximity NUMERIC;