Open-ILS/src/sql/Pg/100.circ_matrix.sql

   1
   2 BEGIN;
   3 -- NOTE: current config.item_type should get sip2_media_type and magnetic_media columns
   4
   5 -- New table needed to handle circ modifiers inside the DB.  Will still require
   6 -- central admin.  The circ_modifier column on asset.copy will become an fkey to this table.
   7 CREATE TABLE config.circ_modifier (
   8     code            TEXT    PRIMARY KEY,
   9     name            TEXT    UNIQUE NOT NULL,
  10     description        TEXT    NOT NULL,
  11     sip2_media_type    TEXT    NOT NULL,
  12     magnetic_media     BOOL    NOT NULL DEFAULT TRUE,
  13     avg_wait_time      INTERVAL
  14 );
  15
  16 /*
  17 -- for instance ...
  18 INSERT INTO config.circ_modifier VALUES ( 'DVD', 'DVD', 'um ... DVDs', '001', FALSE );
  19 INSERT INTO config.circ_modifier VALUES ( 'VIDEO', 'VIDEO', 'Tapes', '001', TRUE );
  20 INSERT INTO config.circ_modifier VALUES ( 'BOOK', 'BOOK', 'Dead tree', '001', FALSE );
  21 INSERT INTO config.circ_modifier VALUES ( 'CRAZY_ARL-ATH_SETTING', 'R2R_TAPE', 'reel2reel tape', '007', TRUE );
  22 */
  23
  24 -- But, just to get us started, use this
  25 /*
  26
  27 UPDATE asset.copy SET circ_modifier = UPPER(circ_modifier) WHERE circ_modifier IS NOT NULL AND circ_modifier <> '';
  28 UPDATE asset.copy SET circ_modifier = NULL WHERE circ_modifier = '';
  29
  30 INSERT INTO config.circ_modifier (code, name, description, sip2_media_type )
  31     SELECT DISTINCT
  32             UPPER(circ_modifier),
  33             UPPER(circ_modifier),
  34             LOWER(circ_modifier),
  35             '001'
  36       FROM  asset.copy
  37       WHERE circ_modifier IS NOT NULL;
  38
  39 */
  40
  41 -- add an fkey pointing to the new circ mod table
  42 ALTER TABLE asset.copy ADD CONSTRAINT circ_mod_fkey FOREIGN KEY (circ_modifier) REFERENCES config.circ_modifier (code) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED;
  43
  44 -- config table to hold the vr_format names
  45 CREATE TABLE config.videorecording_format_map (
  46     code    TEXT    PRIMARY KEY,
  47     value    TEXT    NOT NULL
  48 );
  49
  50 INSERT INTO config.videorecording_format_map VALUES ('a','Beta');
  51 INSERT INTO config.videorecording_format_map VALUES ('b','VHS');
  52 INSERT INTO config.videorecording_format_map VALUES ('c','U-matic');
  53 INSERT INTO config.videorecording_format_map VALUES ('d','EIAJ');
  54 INSERT INTO config.videorecording_format_map VALUES ('e','Type C');
  55 INSERT INTO config.videorecording_format_map VALUES ('f','Quadruplex');
  56 INSERT INTO config.videorecording_format_map VALUES ('g','Laserdisc');
  57 INSERT INTO config.videorecording_format_map VALUES ('h','CED');
  58 INSERT INTO config.videorecording_format_map VALUES ('i','Betacam');
  59 INSERT INTO config.videorecording_format_map VALUES ('j','Betacam SP');
  60 INSERT INTO config.videorecording_format_map VALUES ('k','Super-VHS');
  61 INSERT INTO config.videorecording_format_map VALUES ('m','M-II');
  62 INSERT INTO config.videorecording_format_map VALUES ('o','D-2');
  63 INSERT INTO config.videorecording_format_map VALUES ('p','8 mm.');
  64 INSERT INTO config.videorecording_format_map VALUES ('q','Hi-8 mm.');
  65 INSERT INTO config.videorecording_format_map VALUES ('u','Unknown');
  66 INSERT INTO config.videorecording_format_map VALUES ('v','DVD');
  67 INSERT INTO config.videorecording_format_map VALUES ('z','Other');
  68
  69
  70
  71 /**
  72  **  Here we define the tables that make up the circ matrix.  Conceptually, this implements
  73  **  the "sparse matrix" that everyone talks about, instead of using traditional rules logic.
  74  **  Physically, we cut the matrix up into separate tables (almost 3rd normal form!) that handle
  75  **  different portions of the matrix.  This wil simplify creation of the UI (I hope), and help the
  76  **  developers focus on specific parts of the matrix.
  77  **/
  78
  79 CREATE TABLE config.circ_matrix_matchpoint (
  80     id                   SERIAL    PRIMARY KEY,
  81     active               BOOL    NOT NULL DEFAULT TRUE,
  82     org_unit             INT        NOT NULL 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"
  83     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
  84     circ_modifier        TEXT    REFERENCES config.circ_modifier (code) DEFERRABLE INITIALLY DEFERRED,
  85     marc_type            TEXT    REFERENCES config.item_type_map (code) DEFERRABLE INITIALLY DEFERRED,
  86     marc_form            TEXT    REFERENCES config.item_form_map (code) DEFERRABLE INITIALLY DEFERRED,
  87     marc_vr_format       TEXT    REFERENCES config.videorecording_format_map (code) DEFERRABLE INITIALLY DEFERRED,
  88     copy_circ_lib        INT     REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
  89     copy_owning_lib      INT     REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
  90     user_home_ou         INT     REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
  91     ref_flag             BOOL,
  92     juvenile_flag        BOOL,
  93     is_renewal           BOOL,
  94     usr_age_lower_bound  INTERVAL,
  95     usr_age_upper_bound  INTERVAL,
  96     circulate            BOOL    NOT NULL DEFAULT TRUE,    -- Hard "can't circ" flag requiring an override
  97     duration_rule        INT     NOT NULL REFERENCES config.rule_circ_duration (id) DEFERRABLE INITIALLY DEFERRED,
  98     recurring_fine_rule  INT     NOT NULL REFERENCES config.rule_recurring_fine (id) DEFERRABLE INITIALLY DEFERRED,
  99     max_fine_rule        INT     NOT NULL REFERENCES config.rule_max_fine (id) DEFERRABLE INITIALLY DEFERRED,
 100     hard_due_date        INT     REFERENCES config.hard_due_date (id) DEFERRABLE INITIALLY DEFERRED,
 101     script_test          TEXT,                           -- javascript source
 102     total_copy_hold_ratio     FLOAT,
 103     available_copy_hold_ratio FLOAT,
 104     CONSTRAINT ep_once_per_grp_loc_mod_marc UNIQUE (
 105         grp, org_unit, circ_modifier, marc_type, marc_form, marc_vr_format, ref_flag,
 106         juvenile_flag, usr_age_lower_bound, usr_age_upper_bound, is_renewal, copy_circ_lib,
 107         copy_owning_lib
 108     )
 109 );
 110
 111
 112 -- Tests for max items out by circ_modifier
 113 CREATE TABLE config.circ_matrix_circ_mod_test (
 114     id          SERIAL     PRIMARY KEY,
 115     matchpoint  INT     NOT NULL REFERENCES config.circ_matrix_matchpoint (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 116     items_out   INT     NOT NULL -- Total current active circulations must be less than this, NULL means skip (always pass)
 117 );
 118
 119 CREATE TABLE config.circ_matrix_circ_mod_test_map (
 120     id      SERIAL  PRIMARY KEY,
 121     circ_mod_test   INT NOT NULL REFERENCES config.circ_matrix_circ_mod_test (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 122     circ_mod        TEXT    NOT NULL REFERENCES config.circ_modifier (code) ON DELETE CASCADE ON UPDATE CASCADE  DEFERRABLE INITIALLY DEFERRED,
 123     CONSTRAINT cm_once_per_test UNIQUE (circ_mod_test, circ_mod)
 124 );
 125
 126 CREATE OR REPLACE FUNCTION action.find_circ_matrix_matchpoint( context_ou INT, match_item BIGINT, match_user INT, renewal BOOL ) RETURNS config.circ_matrix_matchpoint AS $func$
 127 DECLARE
 128     user_object     actor.usr%ROWTYPE;
 129     item_object     asset.copy%ROWTYPE;
 130     cn_object       asset.call_number%ROWTYPE;
 131     rec_descriptor  metabib.rec_descriptor%ROWTYPE;
 132     matchpoint      config.circ_matrix_matchpoint%ROWTYPE;
 133     weights         config.circ_matrix_weights%ROWTYPE;
 134     user_age        INTERVAL;
 135     denominator     INT;
 136 BEGIN
 137     SELECT INTO user_object     * FROM actor.usr                WHERE id = match_user;
 138     SELECT INTO item_object     * FROM asset.copy               WHERE id = match_item;
 139     SELECT INTO cn_object       * FROM asset.call_number        WHERE id = item_object.call_number;
 140     SELECT INTO rec_descriptor  * FROM metabib.rec_descriptor   WHERE record = cn_object.record;
 141
 142     -- Pre-generate this so we only calc it once
 143     IF user_object.dob IS NOT NULL THEN
 144         SELECT INTO user_age age(user_object.dob);
 145     END IF;
 146
 147     -- Grab the closest set circ weight setting.
 148     SELECT INTO weights cw.*
 149       FROM config.weight_assoc wa
 150            JOIN config.circ_matrix_weights cw ON (cw.id = wa.circ_weights)
 151            JOIN actor.org_unit_ancestors_distance( context_ou ) d ON (wa.org_unit = d.id)
 152       WHERE active
 153       ORDER BY d.distance
 154       LIMIT 1;
 155
 156     -- No weights? Bad admin! Defaults to handle that anyway.
 157     IF weights.id IS NULL THEN
 158         weights.grp                 := 11;
 159         weights.org_unit            := 10;
 160         weights.circ_modifier       := 5;
 161         weights.marc_type           := 4;
 162         weights.marc_form           := 3;
 163         weights.marc_vr_format      := 2;
 164         weights.copy_circ_lib       := 8;
 165         weights.copy_owning_lib     := 8;
 166         weights.user_home_ou        := 8;
 167         weights.ref_flag            := 1;
 168         weights.juvenile_flag       := 6;
 169         weights.is_renewal          := 7;
 170         weights.usr_age_lower_bound := 0;
 171         weights.usr_age_upper_bound := 0;
 172     END IF;
 173
 174     -- Determine the max (expected) depth (+1) of the org tree and max depth of the permisson tree
 175     -- If you break your org tree with funky parenting this may be wrong
 176     -- Note: This CTE is duplicated in the find_hold_matrix_matchpoint function, and it may be a good idea to split it off to a function
 177     -- We use one denominator for all tree-based checks for when permission groups and org units have the same weighting
 178     WITH all_distance(distance) AS (
 179             SELECT depth AS distance FROM actor.org_unit_type
 180         UNION
 181             SELECT distance AS distance FROM permission.grp_ancestors_distance((SELECT id FROM permission.grp_tree WHERE parent IS NULL))
 182         )
 183     SELECT INTO denominator MAX(distance) + 1 FROM all_distance;
 184
 185     -- Select the winning matchpoint into the matchpoint variable for returning
 186     SELECT INTO matchpoint m.*
 187       FROM  config.circ_matrix_matchpoint m
 188             /*LEFT*/ JOIN permission.grp_ancestors_distance( user_object.profile ) upgad ON m.grp = upgad.id
 189             /*LEFT*/ JOIN actor.org_unit_ancestors_distance( context_ou ) ctoua ON m.org_unit = ctoua.id
 190             LEFT JOIN actor.org_unit_ancestors_distance( cn_object.owning_lib ) cnoua ON m.copy_owning_lib = cnoua.id
 191             LEFT JOIN actor.org_unit_ancestors_distance( item_object.circ_lib ) iooua ON m.copy_circ_lib = iooua.id
 192             LEFT JOIN actor.org_unit_ancestors_distance( user_object.home_ou  ) uhoua ON m.user_home_ou = uhoua.id
 193       WHERE m.active
 194             -- Permission Groups
 195          -- AND (m.grp                      IS NULL OR upgad.id IS NOT NULL) -- Optional Permission Group?
 196             -- Org Units
 197          -- AND (m.org_unit                 IS NULL OR ctoua.id IS NOT NULL) -- Optional Org Unit?
 198             AND (m.copy_owning_lib          IS NULL OR cnoua.id IS NOT NULL)
 199             AND (m.copy_circ_lib            IS NULL OR iooua.id IS NOT NULL)
 200             AND (m.user_home_ou             IS NULL OR uhoua.id IS NOT NULL)
 201             -- Circ Type
 202             AND (m.is_renewal               IS NULL OR m.is_renewal = renewal)
 203             -- Static User Checks
 204             AND (m.juvenile_flag            IS NULL OR m.juvenile_flag = user_object.juvenile)
 205             AND (m.usr_age_lower_bound      IS NULL OR (user_age IS NOT NULL AND m.usr_age_lower_bound < user_age))
 206             AND (m.usr_age_upper_bound      IS NULL OR (user_age IS NOT NULL AND m.usr_age_upper_bound > user_age))
 207             -- Static Item Checks
 208             AND (m.circ_modifier            IS NULL OR m.circ_modifier = item_object.circ_modifier)
 209             AND (m.marc_type                IS NULL OR m.marc_type = COALESCE(item_object.circ_as_type, rec_descriptor.item_type))
 210             AND (m.marc_form                IS NULL OR m.marc_form = rec_descriptor.item_form)
 211             AND (m.marc_vr_format           IS NULL OR m.marc_vr_format = rec_descriptor.vr_format)
 212             AND (m.ref_flag                 IS NULL OR m.ref_flag = item_object.ref)
 213       ORDER BY
 214             -- Permission Groups
 215             CASE WHEN upgad.distance        IS NOT NULL THEN 2^(2*weights.grp - (upgad.distance/denominator)) ELSE 0 END +
 216             -- Org Units
 217             CASE WHEN ctoua.distance        IS NOT NULL THEN 2^(2*weights.org_unit - (ctoua.distance/denominator)) ELSE 0 END +
 218             CASE WHEN cnoua.distance        IS NOT NULL THEN 2^(2*weights.copy_owning_lib - (cnoua.distance/denominator)) ELSE 0 END +
 219             CASE WHEN iooua.distance        IS NOT NULL THEN 2^(2*weights.copy_circ_lib - (iooua.distance/denominator)) ELSE 0 END +
 220             CASE WHEN uhoua.distance        IS NOT NULL THEN 2^(2*weights.user_home_ou - (uhoua.distance/denominator)) ELSE 0 END +
 221             -- Circ Type                    -- Note: 4^x is equiv to 2^(2*x)
 222             CASE WHEN m.is_renewal          IS NOT NULL THEN 4^weights.is_renewal ELSE 0 END +
 223             -- Static User Checks
 224             CASE WHEN m.juvenile_flag       IS NOT NULL THEN 4^weights.juvenile_flag ELSE 0 END +
 225             CASE WHEN m.usr_age_lower_bound IS NOT NULL THEN 4^weights.usr_age_lower_bound ELSE 0 END +
 226             CASE WHEN m.usr_age_upper_bound IS NOT NULL THEN 4^weights.usr_age_upper_bound ELSE 0 END +
 227             -- Static Item Checks
 228             CASE WHEN m.circ_modifier       IS NOT NULL THEN 4^weights.circ_modifier ELSE 0 END +
 229             CASE WHEN m.marc_type           IS NOT NULL THEN 4^weights.marc_type ELSE 0 END +
 230             CASE WHEN m.marc_form           IS NOT NULL THEN 4^weights.marc_form ELSE 0 END +
 231             CASE WHEN m.marc_vr_format      IS NOT NULL THEN 4^weights.marc_vr_format ELSE 0 END +
 232             CASE WHEN m.ref_flag            IS NOT NULL THEN 4^weights.ref_flag ELSE 0 END DESC,
 233             -- Final sort on id, so that if two rules have the same sorting in the previous sort they have a defined order
 234             -- This prevents "we changed the table order by updating a rule, and we started getting different results"
 235             m.id;
 236
 237     -- Return the entire matchpoint
 238     RETURN matchpoint;
 239 END;
 240 $func$ LANGUAGE plpgsql;
 241
 242 CREATE TYPE action.hold_stats AS (
 243     hold_count              INT,
 244     copy_count              INT,
 245     available_count         INT,
 246     total_copy_ratio        FLOAT,
 247     available_copy_ratio    FLOAT
 248 );
 249
 250 CREATE OR REPLACE FUNCTION action.copy_related_hold_stats (copy_id INT) RETURNS action.hold_stats AS $func$
 251 DECLARE
 252     output          action.hold_stats%ROWTYPE;
 253     hold_count      INT := 0;
 254     copy_count      INT := 0;
 255     available_count INT := 0;
 256     hold_map_data   RECORD;
 257 BEGIN
 258
 259     output.hold_count := 0;
 260     output.copy_count := 0;
 261     output.available_count := 0;
 262
 263     SELECT  COUNT( DISTINCT m.hold ) INTO hold_count
 264       FROM  action.hold_copy_map m
 265             JOIN action.hold_request h ON (m.hold = h.id)
 266       WHERE m.target_copy = copy_id
 267             AND NOT h.frozen;
 268
 269     output.hold_count := hold_count;
 270
 271     IF output.hold_count > 0 THEN
 272         FOR hold_map_data IN
 273             SELECT  DISTINCT m.target_copy,
 274                     acp.status
 275               FROM  action.hold_copy_map m
 276                     JOIN asset.copy acp ON (m.target_copy = acp.id)
 277                     JOIN action.hold_request h ON (m.hold = h.id)
 278               WHERE m.hold IN ( SELECT DISTINCT hold FROM action.hold_copy_map WHERE target_copy = copy_id ) AND NOT h.frozen
 279         LOOP
 280             output.copy_count := output.copy_count + 1;
 281             IF hold_map_data.status IN (0,7,12) THEN
 282                 output.available_count := output.available_count + 1;
 283             END IF;
 284         END LOOP;
 285         output.total_copy_ratio = output.copy_count::FLOAT / output.hold_count::FLOAT;
 286         output.available_copy_ratio = output.available_count::FLOAT / output.hold_count::FLOAT;
 287
 288     END IF;
 289
 290     RETURN output;
 291
 292 END;
 293 $func$ LANGUAGE PLPGSQL;
 294
 295 CREATE TYPE action.matrix_test_result AS ( success BOOL, matchpoint INT, fail_part TEXT );
 296 CREATE OR REPLACE FUNCTION action.item_user_circ_test( circ_ou INT, match_item BIGINT, match_user INT, renewal BOOL ) RETURNS SETOF action.matrix_test_result AS $func$
 297 DECLARE
 298     user_object        actor.usr%ROWTYPE;
 299     standing_penalty    config.standing_penalty%ROWTYPE;
 300     item_object        asset.copy%ROWTYPE;
 301     item_status_object    config.copy_status%ROWTYPE;
 302     item_location_object    asset.copy_location%ROWTYPE;
 303     result            action.matrix_test_result;
 304     circ_test        config.circ_matrix_matchpoint%ROWTYPE;
 305     out_by_circ_mod        config.circ_matrix_circ_mod_test%ROWTYPE;
 306     circ_mod_map        config.circ_matrix_circ_mod_test_map%ROWTYPE;
 307     hold_ratio          action.hold_stats%ROWTYPE;
 308     penalty_type         TEXT;
 309     tmp_grp         INT;
 310     items_out        INT;
 311     context_org_list        INT[];
 312     done            BOOL := FALSE;
 313 BEGIN
 314     result.success := TRUE;
 315
 316     -- Fail if the user is BARRED
 317     SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
 318
 319     -- Fail if we couldn't find the user
 320     IF user_object.id IS NULL THEN
 321         result.fail_part := 'no_user';
 322         result.success := FALSE;
 323         done := TRUE;
 324         RETURN NEXT result;
 325         RETURN;
 326     END IF;
 327
 328     SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
 329
 330     -- Fail if we couldn't find the item
 331     IF item_object.id IS NULL THEN
 332         result.fail_part := 'no_item';
 333         result.success := FALSE;
 334         done := TRUE;
 335         RETURN NEXT result;
 336         RETURN;
 337     END IF;
 338
 339     SELECT INTO circ_test * FROM action.find_circ_matrix_matchpoint(circ_ou, match_item, match_user, renewal);
 340     result.matchpoint := circ_test.id;
 341
 342     -- Fail if we couldn't find a matchpoint
 343     IF result.matchpoint IS NULL THEN
 344         result.fail_part := 'no_matchpoint';
 345         result.success := FALSE;
 346         done := TRUE;
 347         RETURN NEXT result;
 348     END IF;
 349
 350     IF user_object.barred IS TRUE THEN
 351         result.fail_part := 'actor.usr.barred';
 352         result.success := FALSE;
 353         done := TRUE;
 354         RETURN NEXT result;
 355     END IF;
 356
 357     -- Fail if the item can't circulate
 358     IF item_object.circulate IS FALSE THEN
 359         result.fail_part := 'asset.copy.circulate';
 360         result.success := FALSE;
 361         done := TRUE;
 362         RETURN NEXT result;
 363     END IF;
 364
 365     -- Fail if the item isn't in a circulateable status on a non-renewal
 366     IF NOT renewal AND item_object.status NOT IN ( 0, 7, 8 ) THEN
 367         result.fail_part := 'asset.copy.status';
 368         result.success := FALSE;
 369         done := TRUE;
 370         RETURN NEXT result;
 371     ELSIF renewal AND item_object.status <> 1 THEN
 372         result.fail_part := 'asset.copy.status';
 373         result.success := FALSE;
 374         done := TRUE;
 375         RETURN NEXT result;
 376     END IF;
 377
 378     -- Fail if the item can't circulate because of the shelving location
 379     SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
 380     IF item_location_object.circulate IS FALSE THEN
 381         result.fail_part := 'asset.copy_location.circulate';
 382         result.success := FALSE;
 383         done := TRUE;
 384         RETURN NEXT result;
 385     END IF;
 386
 387     SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( circ_test.org_unit );
 388
 389     -- Fail if the test is set to hard non-circulating
 390     IF circ_test.circulate IS FALSE THEN
 391         result.fail_part := 'config.circ_matrix_test.circulate';
 392         result.success := FALSE;
 393         done := TRUE;
 394         RETURN NEXT result;
 395     END IF;
 396
 397     -- Fail if the total copy-hold ratio is too low
 398     IF circ_test.total_copy_hold_ratio IS NOT NULL THEN
 399         SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
 400         IF hold_ratio.total_copy_ratio IS NOT NULL AND hold_ratio.total_copy_ratio < circ_test.total_copy_hold_ratio THEN
 401             result.fail_part := 'config.circ_matrix_test.total_copy_hold_ratio';
 402             result.success := FALSE;
 403             done := TRUE;
 404             RETURN NEXT result;
 405         END IF;
 406     END IF;
 407
 408     -- Fail if the available copy-hold ratio is too low
 409     IF circ_test.available_copy_hold_ratio IS NOT NULL THEN
 410         SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
 411         IF hold_ratio.available_copy_ratio IS NOT NULL AND hold_ratio.available_copy_ratio < circ_test.available_copy_hold_ratio THEN
 412             result.fail_part := 'config.circ_matrix_test.available_copy_hold_ratio';
 413             result.success := FALSE;
 414             done := TRUE;
 415             RETURN NEXT result;
 416         END IF;
 417     END IF;
 418
 419     IF renewal THEN
 420         penalty_type = '%RENEW%';
 421     ELSE
 422         penalty_type = '%CIRC%';
 423     END IF;
 424
 425     FOR standing_penalty IN
 426         SELECT  DISTINCT csp.*
 427           FROM  actor.usr_standing_penalty usp
 428                 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
 429           WHERE usr = match_user
 430                 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
 431                 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
 432                 AND csp.block_list LIKE penalty_type LOOP
 433
 434         result.fail_part := standing_penalty.name;
 435         result.success := FALSE;
 436         done := TRUE;
 437         RETURN NEXT result;
 438     END LOOP;
 439
 440     -- Fail if the user has too many items with specific circ_modifiers checked out
 441     FOR out_by_circ_mod IN SELECT * FROM config.circ_matrix_circ_mod_test WHERE matchpoint = circ_test.id LOOP
 442         SELECT  INTO items_out COUNT(*)
 443           FROM  action.circulation circ
 444             JOIN asset.copy cp ON (cp.id = circ.target_copy)
 445           WHERE circ.usr = match_user
 446                AND circ.circ_lib IN ( SELECT * FROM unnest(context_org_list) )
 447             AND circ.checkin_time IS NULL
 448             AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL)
 449             AND cp.circ_modifier IN (SELECT circ_mod FROM config.circ_matrix_circ_mod_test_map WHERE circ_mod_test = out_by_circ_mod.id);
 450         IF items_out >= out_by_circ_mod.items_out THEN
 451             result.fail_part := 'config.circ_matrix_circ_mod_test';
 452             result.success := FALSE;
 453             done := TRUE;
 454             RETURN NEXT result;
 455         END IF;
 456     END LOOP;
 457
 458     -- If we passed everything, return the successful matchpoint id
 459     IF NOT done THEN
 460         RETURN NEXT result;
 461     END IF;
 462
 463     RETURN;
 464 END;
 465 $func$ LANGUAGE plpgsql;
 466
 467 CREATE OR REPLACE FUNCTION action.item_user_circ_test( INT, BIGINT, INT ) RETURNS SETOF action.matrix_test_result AS $func$
 468     SELECT * FROM action.item_user_circ_test( $1, $2, $3, FALSE );
 469 $func$ LANGUAGE SQL;
 470
 471 CREATE OR REPLACE FUNCTION action.item_user_renew_test( INT, BIGINT, INT ) RETURNS SETOF action.matrix_test_result AS $func$
 472     SELECT * FROM action.item_user_circ_test( $1, $2, $3, TRUE );
 473 $func$ LANGUAGE SQL;
 474
 475
 476 CREATE OR REPLACE FUNCTION actor.calculate_system_penalties( match_user INT, context_org INT ) RETURNS SETOF actor.usr_standing_penalty AS $func$
 477 DECLARE
 478     user_object         actor.usr%ROWTYPE;
 479     new_sp_row          actor.usr_standing_penalty%ROWTYPE;
 480     existing_sp_row     actor.usr_standing_penalty%ROWTYPE;
 481     collections_fines   permission.grp_penalty_threshold%ROWTYPE;
 482     max_fines           permission.grp_penalty_threshold%ROWTYPE;
 483     max_overdue         permission.grp_penalty_threshold%ROWTYPE;
 484     max_items_out       permission.grp_penalty_threshold%ROWTYPE;
 485     tmp_grp             INT;
 486     items_overdue       INT;
 487     items_out           INT;
 488     context_org_list    INT[];
 489     current_fines        NUMERIC(8,2) := 0.0;
 490     tmp_fines            NUMERIC(8,2);
 491     tmp_groc            RECORD;
 492     tmp_circ            RECORD;
 493     tmp_org             actor.org_unit%ROWTYPE;
 494     tmp_penalty         config.standing_penalty%ROWTYPE;
 495     tmp_depth           INTEGER;
 496 BEGIN
 497     SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
 498
 499     -- Max fines
 500     SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
 501
 502     -- Fail if the user has a high fine balance
 503     LOOP
 504         tmp_grp := user_object.profile;
 505         LOOP
 506             SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 1 AND org_unit = tmp_org.id;
 507
 508             IF max_fines.threshold IS NULL THEN
 509                 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
 510             ELSE
 511                 EXIT;
 512             END IF;
 513
 514             IF tmp_grp IS NULL THEN
 515                 EXIT;
 516             END IF;
 517         END LOOP;
 518
 519         IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
 520             EXIT;
 521         END IF;
 522
 523         SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
 524
 525     END LOOP;
 526
 527     IF max_fines.threshold IS NOT NULL THEN
 528
 529         RETURN QUERY
 530             SELECT  *
 531               FROM  actor.usr_standing_penalty
 532               WHERE usr = match_user
 533                     AND org_unit = max_fines.org_unit
 534                     AND (stop_date IS NULL or stop_date > NOW())
 535                     AND standing_penalty = 1;
 536
 537         SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( max_fines.org_unit );
 538
 539         SELECT  SUM(f.balance_owed) INTO current_fines
 540           FROM  money.materialized_billable_xact_summary f
 541                 JOIN (
 542                     SELECT  r.id
 543                       FROM  booking.reservation r
 544                       WHERE r.usr = match_user
 545                             AND r.pickup_lib IN (SELECT * FROM unnest(context_org_list))
 546                             AND xact_finish IS NULL
 547                                 UNION ALL
 548                     SELECT  g.id
 549                       FROM  money.grocery g
 550                       WHERE g.usr = match_user
 551                             AND g.billing_location IN (SELECT * FROM unnest(context_org_list))
 552                             AND xact_finish IS NULL
 553                                 UNION ALL
 554                     SELECT  circ.id
 555                       FROM  action.circulation circ
 556                       WHERE circ.usr = match_user
 557                             AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
 558                             AND xact_finish IS NULL ) l USING (id);
 559
 560         IF current_fines >= max_fines.threshold THEN
 561             new_sp_row.usr := match_user;
 562             new_sp_row.org_unit := max_fines.org_unit;
 563             new_sp_row.standing_penalty := 1;
 564             RETURN NEXT new_sp_row;
 565         END IF;
 566     END IF;
 567
 568     -- Start over for max overdue
 569     SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
 570
 571     -- Fail if the user has too many overdue items
 572     LOOP
 573         tmp_grp := user_object.profile;
 574         LOOP
 575
 576             SELECT * INTO max_overdue FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 2 AND org_unit = tmp_org.id;
 577
 578             IF max_overdue.threshold IS NULL THEN
 579                 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
 580             ELSE
 581                 EXIT;
 582             END IF;
 583
 584             IF tmp_grp IS NULL THEN
 585                 EXIT;
 586             END IF;
 587         END LOOP;
 588
 589         IF max_overdue.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
 590             EXIT;
 591         END IF;
 592
 593         SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
 594
 595     END LOOP;
 596
 597     IF max_overdue.threshold IS NOT NULL THEN
 598
 599         RETURN QUERY
 600             SELECT  *
 601               FROM  actor.usr_standing_penalty
 602               WHERE usr = match_user
 603                     AND org_unit = max_overdue.org_unit
 604                     AND (stop_date IS NULL or stop_date > NOW())
 605                     AND standing_penalty = 2;
 606
 607         SELECT  INTO items_overdue COUNT(*)
 608           FROM  action.circulation circ
 609                 JOIN  actor.org_unit_full_path( max_overdue.org_unit ) fp ON (circ.circ_lib = fp.id)
 610           WHERE circ.usr = match_user
 611             AND circ.checkin_time IS NULL
 612             AND circ.due_date < NOW()
 613             AND (circ.stop_fines = 'MAXFINES' OR circ.stop_fines IS NULL);
 614
 615         IF items_overdue >= max_overdue.threshold::INT THEN
 616             new_sp_row.usr := match_user;
 617             new_sp_row.org_unit := max_overdue.org_unit;
 618             new_sp_row.standing_penalty := 2;
 619             RETURN NEXT new_sp_row;
 620         END IF;
 621     END IF;
 622
 623     -- Start over for max out
 624     SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
 625
 626     -- Fail if the user has too many checked out items
 627     LOOP
 628         tmp_grp := user_object.profile;
 629         LOOP
 630             SELECT * INTO max_items_out FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 3 AND org_unit = tmp_org.id;
 631
 632             IF max_items_out.threshold IS NULL THEN
 633                 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
 634             ELSE
 635                 EXIT;
 636             END IF;
 637
 638             IF tmp_grp IS NULL THEN
 639                 EXIT;
 640             END IF;
 641         END LOOP;
 642
 643         IF max_items_out.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
 644             EXIT;
 645         END IF;
 646
 647         SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
 648
 649     END LOOP;
 650
 651
 652     -- Fail if the user has too many items checked out
 653     IF max_items_out.threshold IS NOT NULL THEN
 654
 655         RETURN QUERY
 656             SELECT  *
 657               FROM  actor.usr_standing_penalty
 658               WHERE usr = match_user
 659                     AND org_unit = max_items_out.org_unit
 660                     AND (stop_date IS NULL or stop_date > NOW())
 661                     AND standing_penalty = 3;
 662
 663         SELECT  INTO items_out COUNT(*)
 664           FROM  action.circulation circ
 665                 JOIN  actor.org_unit_full_path( max_items_out.org_unit ) fp ON (circ.circ_lib = fp.id)
 666           WHERE circ.usr = match_user
 667                 AND circ.checkin_time IS NULL
 668                 AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL);
 669
 670            IF items_out >= max_items_out.threshold::INT THEN
 671             new_sp_row.usr := match_user;
 672             new_sp_row.org_unit := max_items_out.org_unit;
 673             new_sp_row.standing_penalty := 3;
 674             RETURN NEXT new_sp_row;
 675            END IF;
 676     END IF;
 677
 678     -- Start over for collections warning
 679     SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
 680
 681     -- Fail if the user has a collections-level fine balance
 682     LOOP
 683         tmp_grp := user_object.profile;
 684         LOOP
 685             SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 4 AND org_unit = tmp_org.id;
 686
 687             IF max_fines.threshold IS NULL THEN
 688                 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
 689             ELSE
 690                 EXIT;
 691             END IF;
 692
 693             IF tmp_grp IS NULL THEN
 694                 EXIT;
 695             END IF;
 696         END LOOP;
 697
 698         IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
 699             EXIT;
 700         END IF;
 701
 702         SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
 703
 704     END LOOP;
 705
 706     IF max_fines.threshold IS NOT NULL THEN
 707
 708         RETURN QUERY
 709             SELECT  *
 710               FROM  actor.usr_standing_penalty
 711               WHERE usr = match_user
 712                     AND org_unit = max_fines.org_unit
 713                     AND (stop_date IS NULL or stop_date > NOW())
 714                     AND standing_penalty = 4;
 715
 716         SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( max_fines.org_unit );
 717
 718         SELECT  SUM(f.balance_owed) INTO current_fines
 719           FROM  money.materialized_billable_xact_summary f
 720                 JOIN (
 721                     SELECT  r.id
 722                       FROM  booking.reservation r
 723                       WHERE r.usr = match_user
 724                             AND r.pickup_lib IN (SELECT * FROM unnest(context_org_list))
 725                             AND r.xact_finish IS NULL
 726                                 UNION ALL
 727                     SELECT  g.id
 728                       FROM  money.grocery g
 729                       WHERE g.usr = match_user
 730                             AND g.billing_location IN (SELECT * FROM unnest(context_org_list))
 731                             AND g.xact_finish IS NULL
 732                                 UNION ALL
 733                     SELECT  circ.id
 734                       FROM  action.circulation circ
 735                       WHERE circ.usr = match_user
 736                             AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
 737                             AND circ.xact_finish IS NULL ) l USING (id);
 738
 739         IF current_fines >= max_fines.threshold THEN
 740             new_sp_row.usr := match_user;
 741             new_sp_row.org_unit := max_fines.org_unit;
 742             new_sp_row.standing_penalty := 4;
 743             RETURN NEXT new_sp_row;
 744         END IF;
 745     END IF;
 746
 747     -- Start over for in collections
 748     SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
 749
 750     -- Remove the in-collections penalty if the user has paid down enough
 751     -- This penalty is different, because this code is not responsible for creating
 752     -- new in-collections penalties, only for removing them
 753     LOOP
 754         tmp_grp := user_object.profile;
 755         LOOP
 756             SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 30 AND org_unit = tmp_org.id;
 757
 758             IF max_fines.threshold IS NULL THEN
 759                 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
 760             ELSE
 761                 EXIT;
 762             END IF;
 763
 764             IF tmp_grp IS NULL THEN
 765                 EXIT;
 766             END IF;
 767         END LOOP;
 768
 769         IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
 770             EXIT;
 771         END IF;
 772
 773         SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
 774
 775     END LOOP;
 776
 777     IF max_fines.threshold IS NOT NULL THEN
 778
 779         SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( max_fines.org_unit );
 780
 781         -- first, see if the user had paid down to the threshold
 782         SELECT  SUM(f.balance_owed) INTO current_fines
 783           FROM  money.materialized_billable_xact_summary f
 784                 JOIN (
 785                     SELECT  r.id
 786                       FROM  booking.reservation r
 787                       WHERE r.usr = match_user
 788                             AND r.pickup_lib IN (SELECT * FROM unnest(context_org_list))
 789                             AND r.xact_finish IS NULL
 790                                 UNION ALL
 791                     SELECT  g.id
 792                       FROM  money.grocery g
 793                       WHERE g.usr = match_user
 794                             AND g.billing_location IN (SELECT * FROM unnest(context_org_list))
 795                             AND g.xact_finish IS NULL
 796                                 UNION ALL
 797                     SELECT  circ.id
 798                       FROM  action.circulation circ
 799                       WHERE circ.usr = match_user
 800                             AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
 801                             AND circ.xact_finish IS NULL ) l USING (id);
 802
 803         IF current_fines IS NULL OR current_fines <= max_fines.threshold THEN
 804             -- patron has paid down enough
 805
 806             SELECT INTO tmp_penalty * FROM config.standing_penalty WHERE id = 30;
 807
 808             IF tmp_penalty.org_depth IS NOT NULL THEN
 809
 810                 -- since this code is not responsible for applying the penalty, it can't
 811                 -- guarantee the current context org will match the org at which the penalty
 812                 --- was applied.  search up the org tree until we hit the configured penalty depth
 813                 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
 814                 SELECT INTO tmp_depth depth FROM actor.org_unit_type WHERE id = tmp_org.ou_type;
 815
 816                 WHILE tmp_depth >= tmp_penalty.org_depth LOOP
 817
 818                     RETURN QUERY
 819                         SELECT  *
 820                           FROM  actor.usr_standing_penalty
 821                           WHERE usr = match_user
 822                                 AND org_unit = tmp_org.id
 823                                 AND (stop_date IS NULL or stop_date > NOW())
 824                                 AND standing_penalty = 30;
 825
 826                     IF tmp_org.parent_ou IS NULL THEN
 827                         EXIT;
 828                     END IF;
 829
 830                     SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
 831                     SELECT INTO tmp_depth depth FROM actor.org_unit_type WHERE id = tmp_org.ou_type;
 832                 END LOOP;
 833
 834             ELSE
 835
 836                 -- no penalty depth is defined, look for exact matches
 837
 838                 RETURN QUERY
 839                     SELECT  *
 840                       FROM  actor.usr_standing_penalty
 841                       WHERE usr = match_user
 842                             AND org_unit = max_fines.org_unit
 843                             AND (stop_date IS NULL or stop_date > NOW())
 844                             AND standing_penalty = 30;
 845             END IF;
 846
 847         END IF;
 848
 849     END IF;
 850
 851     RETURN;
 852 END;
 853 $func$ LANGUAGE plpgsql;
 854
 855 COMMIT;
 856