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

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