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

   1 BEGIN;
   2
   3 CREATE TABLE actor.org_unit_proximity_adjustment (
   4     id                  SERIAL   PRIMARY KEY,
   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)
  15 );
  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);
  23
  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 (
  26             SELECT $1, 0
  27         UNION
  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
  31     )
  32     SELECT * FROM org_unit_ancestors_distance;
  33 $$ LANGUAGE SQL STABLE ROWS 1;
  34
  35 CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity(
  36     ahr_id INT,
  37     acp_id BIGINT,
  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$
  45 DECLARE
  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;
  52
  53     icl_list        INT[];
  54     iol_list        INT[];
  55     isl_list        INT[];
  56     hpl_list        INT[];
  57     hrl_list        INT[];
  58
  59 BEGIN
  60
  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;
  65
  66     IF copy_context_ou IS NULL THEN
  67         copy_context_ou := acp.circ_lib;
  68     END IF;
  69
  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;
  72
  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
  84       ORDER BY
  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),
  90             adj.pos
  91       LIMIT 1;
  92
  93     IF FOUND THEN
  94         baseline_prox := aoupa.prox_adjustment;
  95     END IF;
  96
  97     -- Now find any relative adjustments, and change the baseline prox based on them
  98     FOR aoupa IN
  99         SELECT  adj.*
 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
 109     LOOP
 110         baseline_prox := baseline_prox + aoupa.prox_adjustment;
 111     END LOOP;
 112
 113     RETURN baseline_prox;
 114 END;
 115 $f$ LANGUAGE PLPGSQL;
 116
 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;
 121
 122 ALTER TABLE action.hold_copy_map ADD COLUMN proximity NUMERIC;
 123
 124 COMMIT;