Open-ILS/src/sql/Pg/110.hold_matrix.sql

   1 /*
   2
   3 -- If, for some reason, you need to reload this chunk of the schema
   4 -- just use the following two statements to remove the tables before
   5 -- running the rest of the file.  See 950.data.seed-values.sql for
   6 -- the one default entry to add back to config.hold_matrix_matchpoint.
   7
   8 DROP TABLE config.hold_matrix_matchpoint CASCADE;
   9 DROP TABLE config.hold_matrix_test CASCADE;
  10
  11 */
  12
  13 BEGIN;
  14
  15
  16 --
  17 --                 ****** Which ruleset and tests to use *******
  18 --
  19 -- * Most specific range for org_unit and grp wins.
  20 --
  21 -- * circ_modifier match takes precidence over marc_type match, if circ_modifier is set here
  22 --
  23 -- * marc_type is first checked against the circ_as_type from the copy, then the item type from the marc record
  24 --
  25 -- * If neither circ_modifier nor marc_type is set (both are NULLABLE) then the entry defines the default
  26 --   ruleset and tests for the OU + group (like BOOK in PINES)
  27 --
  28
  29
  30
  31 CREATE TABLE config.hold_matrix_matchpoint (
  32         id                                  SERIAL      PRIMARY KEY,
  33         active                          BOOL    NOT NULL DEFAULT TRUE,
  34         user_home_ou            INT         REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,       -- Set to the top OU for the matchpoint applicability range; we can use org_unit_prox to choose the "best"
  35         request_ou                      INT         REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,       -- Set to the top OU for the matchpoint applicability range; we can use org_unit_prox to choose the "best"
  36         pickup_ou                       INT         REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,       -- Set to the top OU for the matchpoint applicability range; we can use org_unit_prox to choose the "best"
  37         item_owning_ou          INT         REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,       -- Set to the top OU for the matchpoint applicability range; we can use org_unit_prox to choose the "best"
  38         item_circ_ou            INT         REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,       -- Set to the top OU for the matchpoint applicability range; we can use org_unit_prox to choose the "best"
  39         usr_grp                         INT         REFERENCES permission.grp_tree (id) DEFERRABLE INITIALLY DEFERRED,  -- Set to the top applicable group from the group tree; will need descendents and prox functions for filtering
  40         requestor_grp           INT         NOT NULL REFERENCES permission.grp_tree (id) DEFERRABLE INITIALLY DEFERRED, -- Set to the top applicable group from the group tree; will need descendents and prox functions for filtering
  41         circ_modifier           TEXT    REFERENCES config.circ_modifier (code) DEFERRABLE INITIALLY DEFERRED,
  42         marc_type                       TEXT    REFERENCES config.item_type_map (code) DEFERRABLE INITIALLY DEFERRED,
  43         marc_form                       TEXT    REFERENCES config.item_form_map (code) DEFERRABLE INITIALLY DEFERRED,
  44         marc_vr_format          TEXT    REFERENCES config.videorecording_format_map (code) DEFERRABLE INITIALLY DEFERRED,
  45         ref_flag                        BOOL,
  46         holdable                        BOOL    NOT NULL DEFAULT TRUE,                          -- Hard "can't hold" flag requiring an override
  47         distance_is_from_owner  BOOL    NOT NULL DEFAULT FALSE,                         -- How to calculate transit_range.  True means owning lib, false means copy circ lib
  48         transit_range               INT     REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,          -- Can circ inside range of cn.owner/cp.circ_lib at depth of the org_unit_type specified here
  49         max_holds                       INT,                                                    -- Total hold requests must be less than this, NULL means skip (always pass)
  50         include_frozen_holds    BOOL    NOT NULL DEFAULT TRUE,                          -- Include frozen hold requests in the count for max_holds test
  51         stop_blocked_user       BOOL    NOT NULL DEFAULT FALSE,                         -- Stop users who cannot check out items from placing holds
  52         age_hold_protect_rule   INT         REFERENCES config.rule_age_hold_protect (id) DEFERRABLE INITIALLY DEFERRED, -- still not sure we want to move this off the copy
  53         CONSTRAINT hous_once_per_grp_loc_mod_marc UNIQUE (user_home_ou, request_ou, pickup_ou, item_owning_ou, item_circ_ou, requestor_grp, usr_grp, circ_modifier, marc_type, marc_form, marc_vr_format)
  54 );
  55
  56 CREATE OR REPLACE FUNCTION action.find_hold_matrix_matchpoint( pickup_ou INT, request_ou INT, match_item BIGINT, match_user INT, match_requestor INT ) RETURNS INT AS $func$
  57 DECLARE
  58         current_requestor_group permission.grp_tree%ROWTYPE;
  59         root_ou                 actor.org_unit%ROWTYPE;
  60         requestor_object        actor.usr%ROWTYPE;
  61         user_object             actor.usr%ROWTYPE;
  62         item_object             asset.copy%ROWTYPE;
  63         item_cn_object          asset.call_number%ROWTYPE;
  64         rec_descriptor          metabib.rec_descriptor%ROWTYPE;
  65         current_mp_weight       FLOAT;
  66         matchpoint_weight       FLOAT;
  67         tmp_weight              FLOAT;
  68         current_mp              config.hold_matrix_matchpoint%ROWTYPE;
  69         matchpoint              config.hold_matrix_matchpoint%ROWTYPE;
  70 BEGIN
  71         SELECT INTO root_ou * FROM actor.org_unit WHERE parent_ou IS NULL;
  72         SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
  73         SELECT INTO requestor_object * FROM actor.usr WHERE id = match_requestor;
  74         SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
  75         SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number;
  76         SELECT INTO rec_descriptor r.* FROM metabib.rec_descriptor r WHERE r.record = item_cn_object.record;
  77         SELECT INTO current_requestor_group * FROM permission.grp_tree WHERE id = requestor_object.profile;
  78
  79         LOOP
  80                 -- for each potential matchpoint for this ou and group ...
  81                 FOR current_mp IN
  82                         SELECT  m.*
  83                           FROM  config.hold_matrix_matchpoint m
  84                           WHERE m.requestor_grp = current_requestor_group.id AND m.active
  85                           ORDER BY      CASE WHEN m.circ_modifier       IS NOT NULL THEN 16 ELSE 0 END +
  86                                         CASE WHEN m.marc_type           IS NOT NULL THEN 8 ELSE 0 END +
  87                                         CASE WHEN m.marc_form           IS NOT NULL THEN 4 ELSE 0 END +
  88                                         CASE WHEN m.marc_vr_format      IS NOT NULL THEN 2 ELSE 0 END +
  89                                         CASE WHEN m.ref_flag            IS NOT NULL THEN 1 ELSE 0 END DESC LOOP
  90
  91                         current_mp_weight := 5.0;
  92
  93                         IF current_mp.circ_modifier IS NOT NULL THEN
  94                                 CONTINUE WHEN current_mp.circ_modifier <> item_object.circ_modifier;
  95                         END IF;
  96
  97                         IF current_mp.marc_type IS NOT NULL THEN
  98                                 IF item_object.circ_as_type IS NOT NULL THEN
  99                                         CONTINUE WHEN current_mp.marc_type <> item_object.circ_as_type;
 100                                 ELSE
 101                                         CONTINUE WHEN current_mp.marc_type <> rec_descriptor.item_type;
 102                                 END IF;
 103                         END IF;
 104
 105                         IF current_mp.marc_form IS NOT NULL THEN
 106                                 CONTINUE WHEN current_mp.marc_form <> rec_descriptor.item_form;
 107                         END IF;
 108
 109                         IF current_mp.marc_vr_format IS NOT NULL THEN
 110                                 CONTINUE WHEN current_mp.marc_vr_format <> rec_descriptor.vr_format;
 111                         END IF;
 112
 113                         IF current_mp.ref_flag IS NOT NULL THEN
 114                                 CONTINUE WHEN current_mp.ref_flag <> item_object.ref;
 115                         END IF;
 116
 117
 118                         -- caclulate the rule match weight
 119                         IF current_mp.item_owning_ou IS NOT NULL AND current_mp.item_owning_ou <> root_ou.id THEN
 120                                 SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.item_owning_ou, item_cn_object.owning_lib)::FLOAT + 1.0)::FLOAT;
 121                                 current_mp_weight := current_mp_weight - tmp_weight;
 122                         END IF;
 123
 124                         IF current_mp.item_circ_ou IS NOT NULL AND current_mp.item_circ_ou <> root_ou.id THEN
 125                                 SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.item_circ_ou, item_object.circ_lib)::FLOAT + 1.0)::FLOAT;
 126                                 current_mp_weight := current_mp_weight - tmp_weight;
 127                         END IF;
 128
 129                         IF current_mp.pickup_ou IS NOT NULL AND current_mp.pickup_ou <> root_ou.id THEN
 130                                 SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.pickup_ou, pickup_ou)::FLOAT + 1.0)::FLOAT;
 131                                 current_mp_weight := current_mp_weight - tmp_weight;
 132                         END IF;
 133
 134                         IF current_mp.request_ou IS NOT NULL AND current_mp.request_ou <> root_ou.id THEN
 135                                 SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.request_ou, request_ou)::FLOAT + 1.0)::FLOAT;
 136                                 current_mp_weight := current_mp_weight - tmp_weight;
 137                         END IF;
 138
 139                         IF current_mp.user_home_ou IS NOT NULL AND current_mp.user_home_ou <> root_ou.id THEN
 140                                 SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.user_home_ou, user_object.home_ou)::FLOAT + 1.0)::FLOAT;
 141                                 current_mp_weight := current_mp_weight - tmp_weight;
 142                         END IF;
 143
 144                         -- set the matchpoint if we found the best one
 145                         IF matchpoint_weight IS NULL OR matchpoint_weight > current_mp_weight THEN
 146                                 matchpoint = current_mp;
 147                                 matchpoint_weight = current_mp_weight;
 148                         END IF;
 149
 150                 END LOOP;
 151
 152                 EXIT WHEN current_requestor_group.parent IS NULL OR matchpoint.id IS NOT NULL;
 153
 154                 SELECT INTO current_requestor_group * FROM permission.grp_tree WHERE id = current_requestor_group.parent;
 155         END LOOP;
 156
 157         RETURN matchpoint.id;
 158 END;
 159 $func$ LANGUAGE plpgsql;
 160
 161
 162 CREATE OR REPLACE FUNCTION action.hold_request_permit_test( pickup_ou INT, request_ou INT, match_item BIGINT, match_user INT, match_requestor INT ) RETURNS SETOF action.matrix_test_result AS $func$
 163 DECLARE
 164         matchpoint_id           INT;
 165         user_object             actor.usr%ROWTYPE;
 166         age_protect_object      config.rule_age_hold_protect%ROWTYPE;
 167         transit_range_ou_type   actor.org_unit_type%ROWTYPE;
 168         transit_source          actor.org_unit%ROWTYPE;
 169         item_object             asset.copy%ROWTYPE;
 170         result                  action.matrix_test_result;
 171         hold_test               config.hold_matrix_matchpoint%ROWTYPE;
 172         hold_count              INT;
 173         hold_transit_prox       INT;
 174         frozen_hold_count       INT;
 175         patron_penalties        INT;
 176         done                    BOOL := FALSE;
 177 BEGIN
 178         SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
 179
 180         -- Fail if we couldn't find a user
 181         IF user_object.id IS NULL THEN
 182                 result.fail_part := 'no_user';
 183                 result.success := FALSE;
 184                 done := TRUE;
 185                 RETURN NEXT result;
 186                 RETURN;
 187         END IF;
 188
 189         -- Fail if user is barred
 190         IF user_object.barred IS TRUE THEN
 191                 result.fail_part := 'actor.usr.barred';
 192                 result.success := FALSE;
 193                 done := TRUE;
 194                 RETURN NEXT result;
 195                 RETURN;
 196         END IF;
 197
 198         SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
 199
 200         -- Fail if we couldn't find a copy
 201         IF item_object.id IS NULL THEN
 202                 result.fail_part := 'no_item';
 203                 result.success := FALSE;
 204                 done := TRUE;
 205                 RETURN NEXT result;
 206                 RETURN;
 207         END IF;
 208
 209         SELECT INTO matchpoint_id action.find_hold_matrix_matchpoint(pickup_ou, request_ou, match_item, match_user, match_requestor);
 210
 211         -- Fail if we couldn't find any matchpoint (requires a default)
 212         IF matchpoint_id IS NULL THEN
 213                 result.fail_part := 'no_matchpoint';
 214                 result.success := FALSE;
 215                 done := TRUE;
 216                 RETURN NEXT result;
 217                 RETURN;
 218         END IF;
 219
 220         SELECT INTO hold_test * FROM config.hold_matrix_matchpoint WHERE id = matchpoint_id;
 221
 222         result.matchpoint := hold_test.id;
 223         result.success := TRUE;
 224
 225         IF hold_test.holdable IS FALSE THEN
 226                 result.fail_part := 'config.hold_matrix_test.holdable';
 227                 result.success := FALSE;
 228                 done := TRUE;
 229                 RETURN NEXT result;
 230         END IF;
 231
 232         IF hold_test.transit_range IS NOT NULL THEN
 233                 SELECT INTO transit_range_ou_type * FROM actor.org_unit_type WHERE id = hold_test.transit_range;
 234                 IF hold_test.distance_is_from_owner THEN
 235                         SELECT INTO transit_source ou.* FROM actor.org_unit ou JOIN asset.call_number cn ON (cn.owning_lib = ou.id) WHERE cn.id = item_object.call_number;
 236                 ELSE
 237                         SELECT INTO transit_source * FROM actor.org_unit WHERE id = item_object.circ_lib;
 238                 END IF;
 239
 240                 PERFORM * FROM actor.org_unit_descendants( transit_source.id, transit_range_ou_type.depth ) WHERE id = pickup_ou;
 241
 242                 IF NOT FOUND THEN
 243                         result.fail_part := 'transit_range';
 244                         result.success := FALSE;
 245                         done := TRUE;
 246                         RETURN NEXT result;
 247                 END IF;
 248         END IF;
 249
 250         SELECT  INTO patron_penalties COUNT(*)
 251           FROM  actor.usr_standing_penalty usp
 252             JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
 253           WHERE usr = match_user
 254             AND csp.block_list LIKE '%HOLD%';
 255
 256         IF patron_penalties > 0 THEN
 257                 result.fail_part := 'config.hold_matrix_test.stop_blocked_user.hold';
 258                 result.success := FALSE;
 259                 done := TRUE;
 260                 RETURN NEXT result;
 261         END IF;
 262
 263     patron_penalties := 0;
 264
 265         IF hold_test.stop_blocked_user IS TRUE THEN
 266                 SELECT  INTO patron_penalties COUNT(*)
 267                   FROM  actor.usr_standing_penalty usp
 268                 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
 269                   WHERE usr = match_user
 270                 AND csp.block_list LIKE '%CIRC%';
 271
 272                 IF patron_penalties > 0 THEN
 273                         result.fail_part := 'config.hold_matrix_test.stop_blocked_user.circ';
 274                         result.success := FALSE;
 275                         done := TRUE;
 276                         RETURN NEXT result;
 277                 END IF;
 278         END IF;
 279
 280         IF hold_test.max_holds IS NOT NULL THEN
 281                 SELECT  INTO hold_count COUNT(*)
 282                   FROM  action.hold_request
 283                   WHERE usr = match_user
 284                         AND fulfillment_time IS NULL
 285                         AND cancel_time IS NULL
 286                         AND CASE WHEN hold_test.include_frozen_holds THEN TRUE ELSE frozen IS FALSE END;
 287
 288                 IF hold_count >= hold_test.max_holds THEN
 289                         result.fail_part := 'config.hold_matrix_test.max_holds';
 290                         result.success := FALSE;
 291                         done := TRUE;
 292                         RETURN NEXT result;
 293                 END IF;
 294         END IF;
 295
 296         IF item_object.age_protect IS NOT NULL THEN
 297                 SELECT INTO age_protect_object * FROM config.rule_age_hold_protect WHERE id = item_object.age_protect;
 298
 299                 IF item_object.create_date + age_protect_object.age > NOW() THEN
 300                         IF hold_test.distance_is_from_owner THEN
 301                                 SELECT INTO hold_transit_prox prox FROM actor.org_unit_prox WHERE from_org = item_cn_object.owning_lib AND to_org = pickup_ou;
 302                         ELSE
 303                                 SELECT INTO hold_transit_prox prox FROM actor.org_unit_prox WHERE from_org = item_object.circ_lib AND to_org = pickup_ou;
 304                         END IF;
 305
 306                         IF hold_transit_prox > age_protect_object.prox THEN
 307                                 result.fail_part := 'config.rule_age_hold_protect.prox';
 308                                 result.success := FALSE;
 309                                 done := TRUE;
 310                                 RETURN NEXT result;
 311                         END IF;
 312                 END IF;
 313         END IF;
 314
 315         IF NOT done THEN
 316                 RETURN NEXT result;
 317         END IF;
 318
 319         RETURN;
 320 END;
 321 $func$ LANGUAGE plpgsql;
 322
 323 COMMIT;
 324