Open-ILS/src/sql/Pg/upgrade/0759.schema.org_prox_adjust.sql

   1 BEGIN;
   2
   3 SELECT evergreen.upgrade_deps_block_check('0759', :eg_version);
   4
   5 CREATE TABLE actor.org_unit_proximity_adjustment (
   6     id                  SERIAL   PRIMARY KEY,
   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)
  17 );
  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);
  25
  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 (
  28             SELECT $1, 0
  29         UNION
  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
  33     )
  34     SELECT * FROM org_unit_ancestors_distance;
  35 $$ LANGUAGE SQL STABLE ROWS 1;
  36
  37 CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity(
  38     ahr_id INT,
  39     acp_id BIGINT,
  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$
  47 DECLARE
  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;
  54
  55     icl_list        INT[];
  56     iol_list        INT[];
  57     isl_list        INT[];
  58     hpl_list        INT[];
  59     hrl_list        INT[];
  60
  61 BEGIN
  62
  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;
  67
  68     IF copy_context_ou IS NULL THEN
  69         copy_context_ou := acp.circ_lib;
  70     END IF;
  71
  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;
  74
  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
  86       ORDER BY
  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),
  92             adj.pos
  93       LIMIT 1;
  94
  95     IF FOUND THEN
  96         baseline_prox := aoupa.prox_adjustment;
  97     END IF;
  98
  99     -- Now find any relative adjustments, and change the baseline prox based on them
 100     FOR aoupa IN
 101         SELECT  adj.*
 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
 111     LOOP
 112         baseline_prox := baseline_prox + aoupa.prox_adjustment;
 113     END LOOP;
 114
 115     RETURN baseline_prox;
 116 END;
 117 $f$ LANGUAGE PLPGSQL;
 118
 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;
 123
 124 ALTER TABLE action.hold_copy_map ADD COLUMN proximity NUMERIC;
 125
 126 COMMIT;