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(ahr_id INT, acp_id BIGINT, context_ou INT DEFAULT NULL) RETURNS NUMERIC AS $f$
37 aoupa actor.org_unit_proximity_adjustment%ROWTYPE;
38 ahr action.hold_request%ROWTYPE;
39 acp asset.copy%ROWTYPE;
40 acn asset.call_number%ROWTYPE;
41 acl asset.copy_location%ROWTYPE;
42 baseline_prox NUMERIC;
52 SELECT * INTO ahr FROM action.hold_request WHERE id = ahr_id;
53 SELECT * INTO acp FROM asset.copy WHERE id = acp_id;
54 SELECT * INTO acn FROM asset.call_number WHERE id = acp.call_number;
55 SELECT * INTO acl FROM asset.copy_location WHERE id = acp.location;
57 IF context_ou IS NULL THEN
58 context_ou := acp.circ_lib;
61 -- First, gather the baseline proximity of "here" to pickup lib
62 SELECT prox INTO baseline_prox FROM actor.org_unit_proximity WHERE from_org = context_ou AND to_org = ahr.pickup_lib;
64 -- Find any absolute adjustments, and set the baseline prox to that
65 SELECT adj.* INTO aoupa
66 FROM actor.org_unit_proximity_adjustment adj
67 LEFT JOIN actor.org_unit_ancestors_distance(context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib)
68 LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib)
69 LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acn_ol.id = adj.copy_location)
70 LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
71 LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
72 WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND
73 absolute_adjustment AND
74 COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
76 COALESCE(acp_cl.distance,999)
77 + COALESCE(acn_ol.distance,999)
78 + COALESCE(acl_ol.distance,999)
79 + COALESCE(ahr_pl.distance,999)
80 + COALESCE(ahr_rl.distance,999),
85 baseline_prox := aoupa.prox_adjustment;
88 -- Now find any relative adjustments, and change the baseline prox based on them
91 FROM actor.org_unit_proximity_adjustment adj
92 LEFT JOIN actor.org_unit_ancestors_distance(context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib)
93 LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib)
94 LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acn_ol.id = adj.copy_location)
95 LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
96 LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
97 WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND
98 NOT absolute_adjustment AND
99 COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
101 baseline_prox := baseline_prox + aoupa.prox_adjustment;
104 RETURN baseline_prox;
106 $f$ LANGUAGE PLPGSQL;
108 ALTER TABLE actor.org_unit_proximity_adjustment
109 ADD CONSTRAINT actor_org_unit_proximity_adjustment_circ_mod_fkey
110 FOREIGN KEY (circ_mod) REFERENCES config.circ_modifier (code)
111 DEFERRABLE INITIALLY DEFERRED;
113 ALTER TABLE action.hold_copy_map ADD COLUMN proximity NUMERIC;