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(ahr_id INT, acp_id BIGINT, context_ou INT DEFAULT NULL) RETURNS NUMERIC AS $f$
  36 DECLARE
  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;
  43
  44     icl_list        INT[];
  45     iol_list        INT[];
  46     isl_list        INT[];
  47     hpl_list        INT[];
  48     hrl_list        INT[];
  49
  50 BEGIN
  51
  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;
  56
  57     IF context_ou IS NULL THEN
  58         context_ou := acp.circ_lib;
  59     END IF;
  60
  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;
  63
  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
  75       ORDER BY
  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),
  81             adj.pos
  82       LIMIT 1;
  83
  84     IF FOUND THEN
  85         baseline_prox := aoupa.prox_adjustment;
  86     END IF;
  87
  88     -- Now find any relative adjustments, and change the baseline prox based on them
  89     FOR aoupa IN
  90         SELECT  adj.*
  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
 100     LOOP
 101         baseline_prox := baseline_prox + aoupa.prox_adjustment;
 102     END LOOP;
 103
 104     RETURN baseline_prox;
 105 END;
 106 $f$ LANGUAGE PLPGSQL;
 107
 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;
 112
 113 ALTER TABLE action.hold_copy_map ADD COLUMN proximity NUMERIC;
 114
 115 COMMIT;