Open-ILS/src/sql/Pg/upgrade/0487.circ_matrix_fallthrough.sql

   1 BEGIN;
   2
   3 INSERT INTO config.upgrade_log (version) VALUES ('0487'); -- tsbere via miker
   4
   5 -- Circ matchpoint table changes
   6
   7 ALTER TABLE config.circ_matrix_matchpoint
   8     ALTER COLUMN circulate DROP NOT NULL, -- Fallthrough enable
   9     ALTER COLUMN circulate DROP DEFAULT, -- Stop defaulting to true to enable default to fallthrough
  10     ALTER COLUMN duration_rule DROP NOT NULL, -- Fallthrough enable
  11     ALTER COLUMN recurring_fine_rule DROP NOT NULL, -- Fallthrough enable
  12     ALTER COLUMN max_fine_rule DROP NOT NULL, -- Fallthrough enable
  13     ADD COLUMN renewals INT; -- Renewals override
  14
  15 -- Changing return types requires explicit dropping of old versions
  16 DROP FUNCTION action.find_circ_matrix_matchpoint( context_ou INT, match_item BIGINT, match_user INT, renewal BOOL );
  17 DROP FUNCTION action.item_user_circ_test( circ_ou INT, match_item BIGINT, match_user INT, renewal BOOL );
  18 DROP FUNCTION action.item_user_circ_test( INT, BIGINT, INT );
  19 DROP FUNCTION action.item_user_renew_test( INT, BIGINT, INT );
  20
  21 -- New return types
  22 CREATE TYPE action.found_circ_matrix_matchpoint AS ( success BOOL, matchpoint config.circ_matrix_matchpoint, buildrows INT[] );
  23 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 );
  24
  25 -- Replacement functions
  26 CREATE OR REPLACE FUNCTION action.find_circ_matrix_matchpoint( context_ou INT, item_object asset.copy, user_object actor.usr, renewal BOOL ) RETURNS action.found_circ_matrix_matchpoint AS $func$
  27 DECLARE
  28     cn_object       asset.call_number%ROWTYPE;
  29     rec_descriptor  metabib.rec_descriptor%ROWTYPE;
  30     cur_matchpoint  config.circ_matrix_matchpoint%ROWTYPE;
  31     matchpoint      config.circ_matrix_matchpoint%ROWTYPE;
  32     weights         config.circ_matrix_weights%ROWTYPE;
  33     user_age        INTERVAL;
  34     denominator     NUMERIC(6,2);
  35     row_list        INT[];
  36     result          action.found_circ_matrix_matchpoint;
  37 BEGIN
  38     -- Assume failure
  39     result.success = false;
  40
  41     -- Fetch useful data
  42     SELECT INTO cn_object       * FROM asset.call_number        WHERE id = item_object.call_number;
  43     SELECT INTO rec_descriptor  * FROM metabib.rec_descriptor   WHERE record = cn_object.record;
  44
  45     -- Pre-generate this so we only calc it once
  46     IF user_object.dob IS NOT NULL THEN
  47         SELECT INTO user_age age(user_object.dob);
  48     END IF;
  49
  50     -- Grab the closest set circ weight setting.
  51     SELECT INTO weights cw.*
  52       FROM config.weight_assoc wa
  53            JOIN config.circ_matrix_weights cw ON (cw.id = wa.circ_weights)
  54            JOIN actor.org_unit_ancestors_distance( context_ou ) d ON (wa.org_unit = d.id)
  55       WHERE active
  56       ORDER BY d.distance
  57       LIMIT 1;
  58
  59     -- No weights? Bad admin! Defaults to handle that anyway.
  60     IF weights.id IS NULL THEN
  61         weights.grp                 := 11.0;
  62         weights.org_unit            := 10.0;
  63         weights.circ_modifier       := 5.0;
  64         weights.marc_type           := 4.0;
  65         weights.marc_form           := 3.0;
  66         weights.marc_vr_format      := 2.0;
  67         weights.copy_circ_lib       := 8.0;
  68         weights.copy_owning_lib     := 8.0;
  69         weights.user_home_ou        := 8.0;
  70         weights.ref_flag            := 1.0;
  71         weights.juvenile_flag       := 6.0;
  72         weights.is_renewal          := 7.0;
  73         weights.usr_age_lower_bound := 0.0;
  74         weights.usr_age_upper_bound := 0.0;
  75     END IF;
  76
  77     -- Determine the max (expected) depth (+1) of the org tree and max depth of the permisson tree
  78     -- If you break your org tree with funky parenting this may be wrong
  79     -- 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
  80     -- We use one denominator for all tree-based checks for when permission groups and org units have the same weighting
  81     WITH all_distance(distance) AS (
  82             SELECT depth AS distance FROM actor.org_unit_type
  83         UNION
  84             SELECT distance AS distance FROM permission.grp_ancestors_distance((SELECT id FROM permission.grp_tree WHERE parent IS NULL))
  85         )
  86     SELECT INTO denominator MAX(distance) + 1 FROM all_distance;
  87
  88     -- Loop over all the potential matchpoints
  89     FOR cur_matchpoint IN
  90         SELECT m.*
  91           FROM  config.circ_matrix_matchpoint m
  92                 /*LEFT*/ JOIN permission.grp_ancestors_distance( user_object.profile ) upgad ON m.grp = upgad.id
  93                 /*LEFT*/ JOIN actor.org_unit_ancestors_distance( context_ou ) ctoua ON m.org_unit = ctoua.id
  94                 LEFT JOIN actor.org_unit_ancestors_distance( cn_object.owning_lib ) cnoua ON m.copy_owning_lib = cnoua.id
  95                 LEFT JOIN actor.org_unit_ancestors_distance( item_object.circ_lib ) iooua ON m.copy_circ_lib = iooua.id
  96                 LEFT JOIN actor.org_unit_ancestors_distance( user_object.home_ou  ) uhoua ON m.user_home_ou = uhoua.id
  97           WHERE m.active
  98                 -- Permission Groups
  99              -- AND (m.grp                      IS NULL OR upgad.id IS NOT NULL) -- Optional Permission Group?
 100                 -- Org Units
 101              -- AND (m.org_unit                 IS NULL OR ctoua.id IS NOT NULL) -- Optional Org Unit?
 102                 AND (m.copy_owning_lib          IS NULL OR cnoua.id IS NOT NULL)
 103                 AND (m.copy_circ_lib            IS NULL OR iooua.id IS NOT NULL)
 104                 AND (m.user_home_ou             IS NULL OR uhoua.id IS NOT NULL)
 105                 -- Circ Type
 106                 AND (m.is_renewal               IS NULL OR m.is_renewal = renewal)
 107                 -- Static User Checks
 108                 AND (m.juvenile_flag            IS NULL OR m.juvenile_flag = user_object.juvenile)
 109                 AND (m.usr_age_lower_bound      IS NULL OR (user_age IS NOT NULL AND m.usr_age_lower_bound < user_age))
 110                 AND (m.usr_age_upper_bound      IS NULL OR (user_age IS NOT NULL AND m.usr_age_upper_bound > user_age))
 111                 -- Static Item Checks
 112                 AND (m.circ_modifier            IS NULL OR m.circ_modifier = item_object.circ_modifier)
 113                 AND (m.marc_type                IS NULL OR m.marc_type = COALESCE(item_object.circ_as_type, rec_descriptor.item_type))
 114                 AND (m.marc_form                IS NULL OR m.marc_form = rec_descriptor.item_form)
 115                 AND (m.marc_vr_format           IS NULL OR m.marc_vr_format = rec_descriptor.vr_format)
 116                 AND (m.ref_flag                 IS NULL OR m.ref_flag = item_object.ref)
 117           ORDER BY
 118                 -- Permission Groups
 119                 CASE WHEN upgad.distance        IS NOT NULL THEN 2^(2*weights.grp - (upgad.distance/denominator)) ELSE 0.0 END +
 120                 -- Org Units
 121                 CASE WHEN ctoua.distance        IS NOT NULL THEN 2^(2*weights.org_unit - (ctoua.distance/denominator)) ELSE 0.0 END +
 122                 CASE WHEN cnoua.distance        IS NOT NULL THEN 2^(2*weights.copy_owning_lib - (cnoua.distance/denominator)) ELSE 0.0 END +
 123                 CASE WHEN iooua.distance        IS NOT NULL THEN 2^(2*weights.copy_circ_lib - (iooua.distance/denominator)) ELSE 0.0 END +
 124                 CASE WHEN uhoua.distance        IS NOT NULL THEN 2^(2*weights.user_home_ou - (uhoua.distance/denominator)) ELSE 0.0 END +
 125                 -- Circ Type                    -- Note: 4^x is equiv to 2^(2*x)
 126                 CASE WHEN m.is_renewal          IS NOT NULL THEN 4^weights.is_renewal ELSE 0.0 END +
 127                 -- Static User Checks
 128                 CASE WHEN m.juvenile_flag       IS NOT NULL THEN 4^weights.juvenile_flag ELSE 0.0 END +
 129                 CASE WHEN m.usr_age_lower_bound IS NOT NULL THEN 4^weights.usr_age_lower_bound ELSE 0.0 END +
 130                 CASE WHEN m.usr_age_upper_bound IS NOT NULL THEN 4^weights.usr_age_upper_bound ELSE 0.0 END +
 131                 -- Static Item Checks
 132                 CASE WHEN m.circ_modifier       IS NOT NULL THEN 4^weights.circ_modifier ELSE 0.0 END +
 133                 CASE WHEN m.marc_type           IS NOT NULL THEN 4^weights.marc_type ELSE 0.0 END +
 134                 CASE WHEN m.marc_form           IS NOT NULL THEN 4^weights.marc_form ELSE 0.0 END +
 135                 CASE WHEN m.marc_vr_format      IS NOT NULL THEN 4^weights.marc_vr_format ELSE 0.0 END +
 136                 CASE WHEN m.ref_flag            IS NOT NULL THEN 4^weights.ref_flag ELSE 0.0 END DESC,
 137                 -- Final sort on id, so that if two rules have the same sorting in the previous sort they have a defined order
 138                 -- This prevents "we changed the table order by updating a rule, and we started getting different results"
 139                 m.id LOOP
 140
 141         -- Record the full matching row list
 142         row_list := row_list || cur_matchpoint.id;
 143
 144         -- No matchpoint yet?
 145         IF matchpoint.id IS NULL THEN
 146             -- Take the entire matchpoint as a starting point
 147             matchpoint := cur_matchpoint;
 148             CONTINUE; -- No need to look at this row any more.
 149         END IF;
 150
 151         -- Incomplete matchpoint?
 152         IF matchpoint.circulate IS NULL THEN
 153             matchpoint.circulate := cur_matchpoint.circulate;
 154         END IF;
 155         IF matchpoint.duration_rule IS NULL THEN
 156             matchpoint.duration_rule := cur_matchpoint.duration_rule;
 157         END IF;
 158         IF matchpoint.recurring_fine_rule IS NULL THEN
 159             matchpoint.recurring_fine_rule := cur_matchpoint.recurring_fine_rule;
 160         END IF;
 161         IF matchpoint.max_fine_rule IS NULL THEN
 162             matchpoint.max_fine_rule := cur_matchpoint.max_fine_rule;
 163         END IF;
 164         IF matchpoint.hard_due_date IS NULL THEN
 165             matchpoint.hard_due_date := cur_matchpoint.hard_due_date;
 166         END IF;
 167         IF matchpoint.total_copy_hold_ratio IS NULL THEN
 168             matchpoint.total_copy_hold_ratio := cur_matchpoint.total_copy_hold_ratio;
 169         END IF;
 170         IF matchpoint.available_copy_hold_ratio IS NULL THEN
 171             matchpoint.available_copy_hold_ratio := cur_matchpoint.available_copy_hold_ratio;
 172         END IF;
 173         IF matchpoint.renewals IS NULL THEN
 174             matchpoint.renewals := cur_matchpoint.renewals;
 175         END IF;
 176     END LOOP;
 177
 178     -- Check required fields
 179     IF matchpoint.circulate             IS NOT NULL AND
 180        matchpoint.duration_rule         IS NOT NULL AND
 181        matchpoint.recurring_fine_rule   IS NOT NULL AND
 182        matchpoint.max_fine_rule         IS NOT NULL THEN
 183         -- All there? We have a completed match.
 184         result.success := true;
 185     END IF;
 186
 187     -- Include the assembled matchpoint, even if it isn't complete
 188     result.matchpoint := matchpoint;
 189
 190     -- Include (for debugging) the full list of matching rows
 191     result.buildrows := row_list;
 192
 193     -- Hand the result back to caller
 194     RETURN result;
 195 END;
 196 $func$ LANGUAGE plpgsql;
 197
 198 -- Helper function - For manual calling, it can be easier to pass in IDs instead of objects
 199 CREATE OR REPLACE FUNCTION action.find_circ_matrix_matchpoint( context_ou INT, match_item BIGINT, match_user INT, renewal BOOL ) RETURNS SETOF action.found_circ_matrix_matchpoint AS $func$
 200 DECLARE
 201     item_object asset.copy%ROWTYPE;
 202     user_object actor.usr%ROWTYPE;
 203 BEGIN
 204     SELECT INTO item_object * FROM asset.copy   WHERE id = match_item;
 205     SELECT INTO user_object * FROM actor.usr    WHERE id = match_user;
 206
 207     RETURN QUERY SELECT * FROM action.find_circ_matrix_matchpoint( context_ou, item_object, user_object, renewal );
 208 END;
 209 $func$ LANGUAGE plpgsql;
 210
 211 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$
 212 DECLARE
 213     user_object             actor.usr%ROWTYPE;
 214     standing_penalty        config.standing_penalty%ROWTYPE;
 215     item_object             asset.copy%ROWTYPE;
 216     item_status_object      config.copy_status%ROWTYPE;
 217     item_location_object    asset.copy_location%ROWTYPE;
 218     result                  action.circ_matrix_test_result;
 219     circ_test               action.found_circ_matrix_matchpoint;
 220     circ_matchpoint         config.circ_matrix_matchpoint%ROWTYPE;
 221     out_by_circ_mod         config.circ_matrix_circ_mod_test%ROWTYPE;
 222     circ_mod_map            config.circ_matrix_circ_mod_test_map%ROWTYPE;
 223     hold_ratio              action.hold_stats%ROWTYPE;
 224     penalty_type            TEXT;
 225     items_out               INT;
 226     context_org_list        INT[];
 227     done                    BOOL := FALSE;
 228 BEGIN
 229     -- Assume success unless we hit a failure condition
 230     result.success := TRUE;
 231
 232     -- Fail if the user is BARRED
 233     SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
 234
 235     -- Fail if we couldn't find the user
 236     IF user_object.id IS NULL THEN
 237         result.fail_part := 'no_user';
 238         result.success := FALSE;
 239         done := TRUE;
 240         RETURN NEXT result;
 241         RETURN;
 242     END IF;
 243
 244     SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
 245
 246     -- Fail if we couldn't find the item
 247     IF item_object.id IS NULL THEN
 248         result.fail_part := 'no_item';
 249         result.success := FALSE;
 250         done := TRUE;
 251         RETURN NEXT result;
 252         RETURN;
 253     END IF;
 254
 255     IF user_object.barred IS TRUE THEN
 256         result.fail_part := 'actor.usr.barred';
 257         result.success := FALSE;
 258         done := TRUE;
 259         RETURN NEXT result;
 260     END IF;
 261
 262     -- Fail if the item can't circulate
 263     IF item_object.circulate IS FALSE THEN
 264         result.fail_part := 'asset.copy.circulate';
 265         result.success := FALSE;
 266         done := TRUE;
 267         RETURN NEXT result;
 268     END IF;
 269
 270     -- Fail if the item isn't in a circulateable status on a non-renewal
 271     IF NOT renewal AND item_object.status NOT IN ( 0, 7, 8 ) THEN
 272         result.fail_part := 'asset.copy.status';
 273         result.success := FALSE;
 274         done := TRUE;
 275         RETURN NEXT result;
 276     ELSIF renewal AND item_object.status <> 1 THEN
 277         result.fail_part := 'asset.copy.status';
 278         result.success := FALSE;
 279         done := TRUE;
 280         RETURN NEXT result;
 281     END IF;
 282
 283     -- Fail if the item can't circulate because of the shelving location
 284     SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
 285     IF item_location_object.circulate IS FALSE THEN
 286         result.fail_part := 'asset.copy_location.circulate';
 287         result.success := FALSE;
 288         done := TRUE;
 289         RETURN NEXT result;
 290     END IF;
 291
 292     SELECT INTO circ_test * FROM action.find_circ_matrix_matchpoint(circ_ou, item_object, user_object, renewal);
 293
 294     circ_matchpoint             := circ_test.matchpoint;
 295     result.matchpoint           := circ_matchpoint.id;
 296     result.circulate            := circ_matchpoint.circulate;
 297     result.duration_rule        := circ_matchpoint.duration_rule;
 298     result.recurring_fine_rule  := circ_matchpoint.recurring_fine_rule;
 299     result.max_fine_rule        := circ_matchpoint.max_fine_rule;
 300     result.hard_due_date        := circ_matchpoint.hard_due_date;
 301     result.renewals             := circ_matchpoint.renewals;
 302     result.buildrows            := circ_test.buildrows;
 303
 304     -- Fail if we couldn't find a matchpoint
 305     IF circ_test.success = false THEN
 306         result.fail_part := 'no_matchpoint';
 307         result.success := FALSE;
 308         done := TRUE;
 309         RETURN NEXT result;
 310         RETURN; -- All tests after this point require a matchpoint. No sense in running on an incomplete or missing one.
 311     END IF;
 312
 313     -- Apparently....use the circ matchpoint org unit to determine what org units are valid.
 314     SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( circ_matchpoint.org_unit );
 315
 316     IF renewal THEN
 317         penalty_type = '%RENEW%';
 318     ELSE
 319         penalty_type = '%CIRC%';
 320     END IF;
 321
 322     FOR standing_penalty IN
 323         SELECT  DISTINCT csp.*
 324           FROM  actor.usr_standing_penalty usp
 325                 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
 326           WHERE usr = match_user
 327                 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
 328                 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
 329                 AND csp.block_list LIKE penalty_type LOOP
 330
 331         result.fail_part := standing_penalty.name;
 332         result.success := FALSE;
 333         done := TRUE;
 334         RETURN NEXT result;
 335     END LOOP;
 336
 337     -- Fail if the test is set to hard non-circulating
 338     IF circ_matchpoint.circulate IS FALSE THEN
 339         result.fail_part := 'config.circ_matrix_test.circulate';
 340         result.success := FALSE;
 341         done := TRUE;
 342         RETURN NEXT result;
 343     END IF;
 344
 345     -- Fail if the total copy-hold ratio is too low
 346     IF circ_matchpoint.total_copy_hold_ratio IS NOT NULL THEN
 347         SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
 348         IF hold_ratio.total_copy_ratio IS NOT NULL AND hold_ratio.total_copy_ratio < circ_matchpoint.total_copy_hold_ratio THEN
 349             result.fail_part := 'config.circ_matrix_test.total_copy_hold_ratio';
 350             result.success := FALSE;
 351             done := TRUE;
 352             RETURN NEXT result;
 353         END IF;
 354     END IF;
 355
 356     -- Fail if the available copy-hold ratio is too low
 357     IF circ_matchpoint.available_copy_hold_ratio IS NOT NULL THEN
 358         IF hold_ratio.hold_count IS NULL THEN
 359             SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
 360         END IF;
 361         IF hold_ratio.available_copy_ratio IS NOT NULL AND hold_ratio.available_copy_ratio < circ_matchpoint.available_copy_hold_ratio THEN
 362             result.fail_part := 'config.circ_matrix_test.available_copy_hold_ratio';
 363             result.success := FALSE;
 364             done := TRUE;
 365             RETURN NEXT result;
 366         END IF;
 367     END IF;
 368
 369     -- Fail if the user has too many items with specific circ_modifiers checked out
 370     FOR out_by_circ_mod IN SELECT * FROM config.circ_matrix_circ_mod_test WHERE matchpoint = circ_matchpoint.id LOOP
 371         SELECT  INTO items_out COUNT(*)
 372           FROM  action.circulation circ
 373             JOIN asset.copy cp ON (cp.id = circ.target_copy)
 374           WHERE circ.usr = match_user
 375                AND circ.circ_lib IN ( SELECT * FROM unnest(context_org_list) )
 376             AND circ.checkin_time IS NULL
 377             AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL)
 378             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);
 379         IF items_out >= out_by_circ_mod.items_out THEN
 380             result.fail_part := 'config.circ_matrix_circ_mod_test';
 381             result.success := FALSE;
 382             done := TRUE;
 383             RETURN NEXT result;
 384         END IF;
 385     END LOOP;
 386
 387     -- If we passed everything, return the successful matchpoint id
 388     IF NOT done THEN
 389         RETURN NEXT result;
 390     END IF;
 391
 392     RETURN;
 393 END;
 394 $func$ LANGUAGE plpgsql;
 395
 396 CREATE OR REPLACE FUNCTION action.item_user_circ_test( INT, BIGINT, INT ) RETURNS SETOF action.circ_matrix_test_result AS $func$
 397     SELECT * FROM action.item_user_circ_test( $1, $2, $3, FALSE );
 398 $func$ LANGUAGE SQL;
 399
 400 CREATE OR REPLACE FUNCTION action.item_user_renew_test( INT, BIGINT, INT ) RETURNS SETOF action.circ_matrix_test_result AS $func$
 401     SELECT * FROM action.item_user_circ_test( $1, $2, $3, TRUE );
 402 $func$ LANGUAGE SQL;
 403
 404 COMMIT;