Open-ILS/src/sql/Pg/upgrade/XXXX.circ_limits.sql

   1 -- Limit groups for circ counting
   2 CREATE TABLE config.circ_limit_group (
   3     id          SERIAL  PRIMARY KEY,
   4     name        TEXT    UNIQUE NOT NULL,
   5     description TEXT
   6 );
   7
   8 -- Limit sets
   9 CREATE TABLE config.circ_limit_set (
  10     id          SERIAL  PRIMARY KEY,
  11     name        TEXT    UNIQUE NOT NULL,
  12     owning_lib  INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
  13     items_out   INT     NOT NULL, -- Total current active circulations must be less than this. 0 means skip counting (always pass)
  14     depth       INT     NOT NULL DEFAULT 0, -- Depth count starts at
  15     global      BOOL    NOT NULL DEFAULT FALSE, -- If enabled, include everything below depth, otherwise ancestors/descendants only
  16     description TEXT
  17 );
  18
  19 -- Linkage between matchpoints and limit sets
  20 CREATE TABLE config.circ_matrix_limit_set_map (
  21     id          SERIAL  PRIMARY KEY,
  22     matchpoint  INT     NOT NULL REFERENCES config.circ_matrix_matchpoint (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  23     limit_set   INT     NOT NULL REFERENCES config.circ_limit_set (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  24     fallthrough BOOL    NOT NULL DEFAULT FALSE, -- If true fallthrough will grab this rule as it goes along
  25     active      BOOL    NOT NULL DEFAULT TRUE,
  26     CONSTRAINT circ_limit_set_once_per_matchpoint UNIQUE (matchpoint, limit_set)
  27 );
  28
  29 -- Linkage between limit sets and circ mods
  30 CREATE TABLE config.circ_limit_set_circ_mod_map (
  31     id          SERIAL  PRIMARY KEY,
  32     limit_set   INT     NOT NULL REFERENCES config.circ_limit_set (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  33     circ_mod    TEXT    NOT NULL REFERENCES config.circ_modifier (code) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
  34     CONSTRAINT cm_once_per_set UNIQUE (limit_set, circ_mod)
  35 );
  36
  37 -- Linkage between limit sets and limit groups
  38 CREATE TABLE config.circ_limit_set_group_map (
  39     id          SERIAL  PRIMARY KEY,
  40     limit_set    INT     NOT NULL REFERENCES config.circ_limit_set (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  41     limit_group INT     NOT NULL REFERENCES config.circ_limit_group (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  42     check_only  BOOL    NOT NULL DEFAULT FALSE, -- If true, don't accumulate this limit_group for storing with the circulation
  43     CONSTRAINT clg_once_per_set UNIQUE (limit_set, limit_group)
  44 );
  45
  46 -- Linkage between limit groups and circulations
  47 CREATE TABLE action.circulation_limit_group_map (
  48     circ        BIGINT      NOT NULL REFERENCES action.circulation (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  49     limit_group INT         NOT NULL REFERENCES config.circ_limit_group (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  50     PRIMARY KEY (circ, limit_group)
  51 );
  52
  53 -- Function for populating the circ/limit group mappings
  54 CREATE OR REPLACE FUNCTION action.link_circ_limit_groups ( BIGINT, INT[] ) RETURNS VOID AS $func$
  55     INSERT INTO action.circulation_limit_group_map(circ, limit_group) SELECT $1, id FROM config.circ_limit_group WHERE id IN (SELECT * FROM UNNEST($2));
  56 $func$ LANGUAGE SQL;
  57
  58 DROP TYPE IF EXISTS action.circ_matrix_test_result CASCADE;
  59 CREATE TYPE action.circ_matrix_test_result AS ( success BOOL, fail_part TEXT, buildrows INT[], matchpoint INT, circulate BOOL, duration_rule INT, recurring_fine_rule INT, max_fine_rule INT, hard_due_date INT, renewals INT, grace_period INTERVAL, limit_groups INT[] );
  60
  61 CREATE OR REPLACE FUNCTION action.item_user_circ_test( circ_ou INT, match_item BIGINT, match_user INT, renewal BOOL ) RETURNS SETOF action.circ_matrix_test_result AS $func$
  62 DECLARE
  63     user_object             actor.usr%ROWTYPE;
  64     standing_penalty        config.standing_penalty%ROWTYPE;
  65     item_object             asset.copy%ROWTYPE;
  66     item_status_object      config.copy_status%ROWTYPE;
  67     item_location_object    asset.copy_location%ROWTYPE;
  68     result                  action.circ_matrix_test_result;
  69     circ_test               action.found_circ_matrix_matchpoint;
  70     circ_matchpoint         config.circ_matrix_matchpoint%ROWTYPE;
  71     circ_limit_set          config.circ_limit_set%ROWTYPE;
  72     hold_ratio              action.hold_stats%ROWTYPE;
  73     penalty_type            TEXT;
  74     items_out               INT;
  75     context_org_list        INT[];
  76     done                    BOOL := FALSE;
  77 BEGIN
  78     -- Assume success unless we hit a failure condition
  79     result.success := TRUE;
  80
  81     -- Need user info to look up matchpoints
  82     SELECT INTO user_object * FROM actor.usr WHERE id = match_user AND NOT deleted;
  83
  84     -- (Insta)Fail if we couldn't find the user
  85     IF user_object.id IS NULL THEN
  86         result.fail_part := 'no_user';
  87         result.success := FALSE;
  88         done := TRUE;
  89         RETURN NEXT result;
  90         RETURN;
  91     END IF;
  92
  93     -- Need item info to look up matchpoints
  94     SELECT INTO item_object * FROM asset.copy WHERE id = match_item AND NOT deleted;
  95
  96     -- (Insta)Fail if we couldn't find the item
  97     IF item_object.id IS NULL THEN
  98         result.fail_part := 'no_item';
  99         result.success := FALSE;
 100         done := TRUE;
 101         RETURN NEXT result;
 102         RETURN;
 103     END IF;
 104
 105     SELECT INTO circ_test * FROM action.find_circ_matrix_matchpoint(circ_ou, item_object, user_object, renewal);
 106
 107     circ_matchpoint             := circ_test.matchpoint;
 108     result.matchpoint           := circ_matchpoint.id;
 109     result.circulate            := circ_matchpoint.circulate;
 110     result.duration_rule        := circ_matchpoint.duration_rule;
 111     result.recurring_fine_rule  := circ_matchpoint.recurring_fine_rule;
 112     result.max_fine_rule        := circ_matchpoint.max_fine_rule;
 113     result.hard_due_date        := circ_matchpoint.hard_due_date;
 114     result.renewals             := circ_matchpoint.renewals;
 115     result.grace_period         := circ_matchpoint.grace_period;
 116     result.buildrows            := circ_test.buildrows;
 117
 118     -- (Insta)Fail if we couldn't find a matchpoint
 119     IF circ_test.success = false THEN
 120         result.fail_part := 'no_matchpoint';
 121         result.success := FALSE;
 122         done := TRUE;
 123         RETURN NEXT result;
 124         RETURN;
 125     END IF;
 126
 127     -- All failures before this point are non-recoverable
 128     -- Below this point are possibly overridable failures
 129
 130     -- Fail if the user is barred
 131     IF user_object.barred IS TRUE THEN
 132         result.fail_part := 'actor.usr.barred';
 133         result.success := FALSE;
 134         done := TRUE;
 135         RETURN NEXT result;
 136     END IF;
 137
 138     -- Fail if the item can't circulate
 139     IF item_object.circulate IS FALSE THEN
 140         result.fail_part := 'asset.copy.circulate';
 141         result.success := FALSE;
 142         done := TRUE;
 143         RETURN NEXT result;
 144     END IF;
 145
 146     -- Fail if the item isn't in a circulateable status on a non-renewal
 147     IF NOT renewal AND item_object.status NOT IN ( 0, 7, 8 ) THEN
 148         result.fail_part := 'asset.copy.status';
 149         result.success := FALSE;
 150         done := TRUE;
 151         RETURN NEXT result;
 152     -- Alternately, fail if the item isn't checked out on a renewal
 153     ELSIF renewal AND item_object.status <> 1 THEN
 154         result.fail_part := 'asset.copy.status';
 155         result.success := FALSE;
 156         done := TRUE;
 157         RETURN NEXT result;
 158     END IF;
 159
 160     -- Fail if the item can't circulate because of the shelving location
 161     SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
 162     IF item_location_object.circulate IS FALSE THEN
 163         result.fail_part := 'asset.copy_location.circulate';
 164         result.success := FALSE;
 165         done := TRUE;
 166         RETURN NEXT result;
 167     END IF;
 168
 169     -- Use Circ OU for penalties and such
 170     SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( circ_ou );
 171
 172     IF renewal THEN
 173         penalty_type = '%RENEW%';
 174     ELSE
 175         penalty_type = '%CIRC%';
 176     END IF;
 177
 178     FOR standing_penalty IN
 179         SELECT  DISTINCT csp.*
 180           FROM  actor.usr_standing_penalty usp
 181                 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
 182           WHERE usr = match_user
 183                 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
 184                 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
 185                 AND csp.block_list LIKE penalty_type LOOP
 186
 187         result.fail_part := standing_penalty.name;
 188         result.success := FALSE;
 189         done := TRUE;
 190         RETURN NEXT result;
 191     END LOOP;
 192
 193     -- Fail if the test is set to hard non-circulating
 194     IF circ_matchpoint.circulate IS FALSE THEN
 195         result.fail_part := 'config.circ_matrix_test.circulate';
 196         result.success := FALSE;
 197         done := TRUE;
 198         RETURN NEXT result;
 199     END IF;
 200
 201     -- Fail if the total copy-hold ratio is too low
 202     IF circ_matchpoint.total_copy_hold_ratio IS NOT NULL THEN
 203         SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
 204         IF hold_ratio.total_copy_ratio IS NOT NULL AND hold_ratio.total_copy_ratio < circ_matchpoint.total_copy_hold_ratio THEN
 205             result.fail_part := 'config.circ_matrix_test.total_copy_hold_ratio';
 206             result.success := FALSE;
 207             done := TRUE;
 208             RETURN NEXT result;
 209         END IF;
 210     END IF;
 211
 212     -- Fail if the available copy-hold ratio is too low
 213     IF circ_matchpoint.available_copy_hold_ratio IS NOT NULL THEN
 214         IF hold_ratio.hold_count IS NULL THEN
 215             SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
 216         END IF;
 217         IF hold_ratio.available_copy_ratio IS NOT NULL AND hold_ratio.available_copy_ratio < circ_matchpoint.available_copy_hold_ratio THEN
 218             result.fail_part := 'config.circ_matrix_test.available_copy_hold_ratio';
 219             result.success := FALSE;
 220             done := TRUE;
 221             RETURN NEXT result;
 222         END IF;
 223     END IF;
 224
 225     -- Fail if the user has too many items out by defined limit sets
 226     FOR circ_limit_set IN SELECT ccls.* FROM config.circ_limit_set ccls
 227       JOIN config.circ_matrix_limit_set_map ccmlsm ON ccmlsm.limit_set = ccls.id
 228       WHERE ccmlsm.active AND ( ccmlsm.matchpoint = circ_matchpoint.id OR
 229         ( ccmlsm.matchpoint IN (SELECT * FROM unnest(result.buildrows)) AND ccmlsm.fallthrough )
 230         ) LOOP
 231             IF circ_limit_set.items_out > 0 AND NOT renewal THEN
 232                 SELECT INTO context_org_list ARRAY_AGG(aou.id)
 233                   FROM actor.org_unit_full_path( circ_ou ) aou
 234                     JOIN actor.org_unit_type aout ON aou.ou_type = aout.id
 235                   WHERE aout.depth >= circ_limit_set.depth;
 236                 IF circ_limit_set.global THEN
 237                     WITH RECURSIVE descendant_depth AS (
 238                         SELECT  ou.id,
 239                             ou.parent_ou
 240                         FROM  actor.org_unit ou
 241                         WHERE ou.id IN (SELECT * FROM unnest(context_org_list))
 242                             UNION
 243                         SELECT  ou.id,
 244                             ou.parent_ou
 245                         FROM  actor.org_unit ou
 246                             JOIN descendant_depth ot ON (ot.id = ou.parent_ou)
 247                     ) SELECT INTO context_org_list ARRAY_AGG(ou.id) FROM actor.org_unit ou JOIN descendant_depth USING (id);
 248                 END IF;
 249                 SELECT INTO items_out COUNT(DISTINCT circ.id)
 250                   FROM action.circulation circ
 251                     JOIN asset.copy copy ON (copy.id = circ.target_copy)
 252                     LEFT JOIN action.circulation_limit_group_map aclgm ON (circ.id = aclgm.circ)
 253                   WHERE circ.usr = match_user
 254                     AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
 255                     AND circ.checkin_time IS NULL
 256                     AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL)
 257                     AND (copy.circ_modifier IN (SELECT circ_mod FROM config.circ_limit_set_circ_mod_map WHERE limit_set = circ_limit_set.id)
 258                         OR aclgm.limit_group IN (SELECT limit_group FROM config.circ_limit_set_group_map WHERE limit_set = circ_limit_set.id)
 259                     );
 260                 IF items_out >= circ_limit_set.items_out THEN
 261                     result.fail_part := 'config.circ_matrix_circ_mod_test';
 262                     result.success := FALSE;
 263                     done := TRUE;
 264                     RETURN NEXT result;
 265                 END IF;
 266             END IF;
 267             SELECT INTO result.limit_groups result.limit_groups || ARRAY_AGG(limit_group) FROM config.circ_limit_set_group_map WHERE limit_set = circ_limit_set.id AND NOT check_only;
 268     END LOOP;
 269
 270     -- If we passed everything, return the successful matchpoint
 271     IF NOT done THEN
 272         RETURN NEXT result;
 273     END IF;
 274
 275     RETURN;
 276 END;
 277 $func$ LANGUAGE plpgsql;
 278
 279 -- We need to re-create these, as they got dropped with the type above.
 280 CREATE OR REPLACE FUNCTION action.item_user_circ_test( INT, BIGINT, INT ) RETURNS SETOF action.circ_matrix_test_result AS $func$
 281     SELECT * FROM action.item_user_circ_test( $1, $2, $3, FALSE );
 282 $func$ LANGUAGE SQL;
 283
 284 CREATE OR REPLACE FUNCTION action.item_user_renew_test( INT, BIGINT, INT ) RETURNS SETOF action.circ_matrix_test_result AS $func$
 285     SELECT * FROM action.item_user_circ_test( $1, $2, $3, TRUE );
 286 $func$ LANGUAGE SQL;
 287
 288 -- Temp function for migrating circ mod limits.
 289 CREATE OR REPLACE FUNCTION evergreen.temp_migrate_circ_mod_limits() RETURNS VOID AS $func$
 290 DECLARE
 291     circ_mod_group config.circ_matrix_circ_mod_test%ROWTYPE;
 292     current_set INT;
 293     circ_mod_count INT;
 294 BEGIN
 295     FOR circ_mod_group IN SELECT * FROM config.circ_matrix_circ_mod_test LOOP
 296         INSERT INTO config.circ_limit_set(name, owning_lib, items_out, depth, global, description)
 297             SELECT org_unit || ' : Matchpoint ' || circ_mod_group.matchpoint || ' : Circ Mod Test ' || circ_mod_group.id, org_unit, circ_mod_group.items_out, 0, false, 'Migrated from Circ Mod Test System'
 298                 FROM config.circ_matrix_matchpoint WHERE id = circ_mod_group.matchpoint
 299             RETURNING id INTO current_set;
 300         INSERT INTO config.circ_matrix_limit_set_map(matchpoint, limit_set, fallthrough, active) VALUES (circ_mod_group.matchpoint, current_set, false, true);
 301         INSERT INTO config.circ_limit_set_circ_mod_map(limit_set, circ_mod)
 302             SELECT current_set, circ_mod FROM config.circ_matrix_circ_mod_test_map WHERE circ_mod_test = circ_mod_group.id;
 303         SELECT INTO circ_mod_count count(id) FROM config.circ_limit_set_circ_mod_map WHERE limit_set = current_set;
 304         RAISE NOTICE 'Created limit set with id % and % circ modifiers attached to matchpoint %', current_set, circ_mod_count, circ_mod_group.matchpoint;
 305     END LOOP;
 306 END;
 307 $func$ LANGUAGE plpgsql;
 308
 309 -- Run the temp function
 310 SELECT * FROM evergreen.temp_migrate_circ_mod_limits();
 311
 312 -- Drop the temp function
 313 DROP FUNCTION evergreen.temp_migrate_circ_mod_limits();
 314
 315 --Drop the old tables
 316 --Not sure we want to do this. Keeping them may help "something went wrong" correction.
 317 --DROP TABLE IF EXISTS config.circ_matrix_circ_mod_test_map, config.circ_matrix_circ_mod_test;