Open-ILS/src/sql/Pg/upgrade/0449.schema.kill_explode_array_and_use_unnest.sql

   1
   2 BEGIN;
   3
   4 INSERT INTO config.upgrade_log (version) VALUES ('0449'); -- miker
   5
   6 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$
   7 DECLARE
   8     user_object        actor.usr%ROWTYPE;
   9     standing_penalty    config.standing_penalty%ROWTYPE;
  10     item_object        asset.copy%ROWTYPE;
  11     item_status_object    config.copy_status%ROWTYPE;
  12     item_location_object    asset.copy_location%ROWTYPE;
  13     result            action.matrix_test_result;
  14     circ_test        config.circ_matrix_matchpoint%ROWTYPE;
  15     out_by_circ_mod        config.circ_matrix_circ_mod_test%ROWTYPE;
  16     circ_mod_map        config.circ_matrix_circ_mod_test_map%ROWTYPE;
  17     hold_ratio          action.hold_stats%ROWTYPE;
  18     penalty_type         TEXT;
  19     tmp_grp         INT;
  20     items_out        INT;
  21     context_org_list        INT[];
  22     done            BOOL := FALSE;
  23 BEGIN
  24     result.success := TRUE;
  25
  26     -- Fail if the user is BARRED
  27     SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
  28
  29     -- Fail if we couldn't find the user
  30     IF user_object.id IS NULL THEN
  31         result.fail_part := 'no_user';
  32         result.success := FALSE;
  33         done := TRUE;
  34         RETURN NEXT result;
  35         RETURN;
  36     END IF;
  37
  38     SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
  39
  40     -- Fail if we couldn't find the item
  41     IF item_object.id IS NULL THEN
  42         result.fail_part := 'no_item';
  43         result.success := FALSE;
  44         done := TRUE;
  45         RETURN NEXT result;
  46         RETURN;
  47     END IF;
  48
  49     SELECT INTO circ_test * FROM action.find_circ_matrix_matchpoint(circ_ou, match_item, match_user, renewal);
  50     result.matchpoint := circ_test.id;
  51
  52     -- Fail if we couldn't find a matchpoint
  53     IF result.matchpoint IS NULL THEN
  54         result.fail_part := 'no_matchpoint';
  55         result.success := FALSE;
  56         done := TRUE;
  57         RETURN NEXT result;
  58     END IF;
  59
  60     IF user_object.barred IS TRUE THEN
  61         result.fail_part := 'actor.usr.barred';
  62         result.success := FALSE;
  63         done := TRUE;
  64         RETURN NEXT result;
  65     END IF;
  66
  67     -- Fail if the item can't circulate
  68     IF item_object.circulate IS FALSE THEN
  69         result.fail_part := 'asset.copy.circulate';
  70         result.success := FALSE;
  71         done := TRUE;
  72         RETURN NEXT result;
  73     END IF;
  74
  75     -- Fail if the item isn't in a circulateable status on a non-renewal
  76     IF NOT renewal AND item_object.status NOT IN ( 0, 7, 8 ) THEN
  77         result.fail_part := 'asset.copy.status';
  78         result.success := FALSE;
  79         done := TRUE;
  80         RETURN NEXT result;
  81     ELSIF renewal AND item_object.status <> 1 THEN
  82         result.fail_part := 'asset.copy.status';
  83         result.success := FALSE;
  84         done := TRUE;
  85         RETURN NEXT result;
  86     END IF;
  87
  88     -- Fail if the item can't circulate because of the shelving location
  89     SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
  90     IF item_location_object.circulate IS FALSE THEN
  91         result.fail_part := 'asset.copy_location.circulate';
  92         result.success := FALSE;
  93         done := TRUE;
  94         RETURN NEXT result;
  95     END IF;
  96
  97     SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( circ_test.org_unit );
  98
  99     -- Fail if the test is set to hard non-circulating
 100     IF circ_test.circulate IS FALSE THEN
 101         result.fail_part := 'config.circ_matrix_test.circulate';
 102         result.success := FALSE;
 103         done := TRUE;
 104         RETURN NEXT result;
 105     END IF;
 106
 107     -- Fail if the total copy-hold ratio is too low
 108     IF circ_test.total_copy_hold_ratio IS NOT NULL THEN
 109         SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
 110         IF hold_ratio.total_copy_ratio IS NOT NULL AND hold_ratio.total_copy_ratio < circ_test.total_copy_hold_ratio THEN
 111             result.fail_part := 'config.circ_matrix_test.total_copy_hold_ratio';
 112             result.success := FALSE;
 113             done := TRUE;
 114             RETURN NEXT result;
 115         END IF;
 116     END IF;
 117
 118     -- Fail if the available copy-hold ratio is too low
 119     IF circ_test.available_copy_hold_ratio IS NOT NULL THEN
 120         SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
 121         IF hold_ratio.available_copy_ratio IS NOT NULL AND hold_ratio.available_copy_ratio < circ_test.available_copy_hold_ratio THEN
 122             result.fail_part := 'config.circ_matrix_test.available_copy_hold_ratio';
 123             result.success := FALSE;
 124             done := TRUE;
 125             RETURN NEXT result;
 126         END IF;
 127     END IF;
 128
 129     IF renewal THEN
 130         penalty_type = '%RENEW%';
 131     ELSE
 132         penalty_type = '%CIRC%';
 133     END IF;
 134
 135     FOR standing_penalty IN
 136         SELECT  DISTINCT csp.*
 137           FROM  actor.usr_standing_penalty usp
 138                 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
 139           WHERE usr = match_user
 140                 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
 141                 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
 142                 AND csp.block_list LIKE penalty_type LOOP
 143
 144         result.fail_part := standing_penalty.name;
 145         result.success := FALSE;
 146         done := TRUE;
 147         RETURN NEXT result;
 148     END LOOP;
 149
 150     -- Fail if the user has too many items with specific circ_modifiers checked out
 151     FOR out_by_circ_mod IN SELECT * FROM config.circ_matrix_circ_mod_test WHERE matchpoint = circ_test.id LOOP
 152         SELECT  INTO items_out COUNT(*)
 153           FROM  action.circulation circ
 154             JOIN asset.copy cp ON (cp.id = circ.target_copy)
 155           WHERE circ.usr = match_user
 156                AND circ.circ_lib IN ( SELECT * FROM unnest(context_org_list) )
 157             AND circ.checkin_time IS NULL
 158             AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL)
 159             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);
 160         IF items_out >= out_by_circ_mod.items_out THEN
 161             result.fail_part := 'config.circ_matrix_circ_mod_test';
 162             result.success := FALSE;
 163             done := TRUE;
 164             RETURN NEXT result;
 165         END IF;
 166     END LOOP;
 167
 168     -- If we passed everything, return the successful matchpoint id
 169     IF NOT done THEN
 170         RETURN NEXT result;
 171     END IF;
 172
 173     RETURN;
 174 END;
 175 $func$ LANGUAGE plpgsql;
 176
 177 CREATE OR REPLACE FUNCTION actor.calculate_system_penalties( match_user INT, context_org INT ) RETURNS SETOF actor.usr_standing_penalty AS $func$
 178 DECLARE
 179     user_object         actor.usr%ROWTYPE;
 180     new_sp_row          actor.usr_standing_penalty%ROWTYPE;
 181     existing_sp_row     actor.usr_standing_penalty%ROWTYPE;
 182     collections_fines   permission.grp_penalty_threshold%ROWTYPE;
 183     max_fines           permission.grp_penalty_threshold%ROWTYPE;
 184     max_overdue         permission.grp_penalty_threshold%ROWTYPE;
 185     max_items_out       permission.grp_penalty_threshold%ROWTYPE;
 186     tmp_grp             INT;
 187     items_overdue       INT;
 188     items_out           INT;
 189     context_org_list    INT[];
 190     current_fines        NUMERIC(8,2) := 0.0;
 191     tmp_fines            NUMERIC(8,2);
 192     tmp_groc            RECORD;
 193     tmp_circ            RECORD;
 194     tmp_org             actor.org_unit%ROWTYPE;
 195     tmp_penalty         config.standing_penalty%ROWTYPE;
 196     tmp_depth           INTEGER;
 197 BEGIN
 198     SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
 199
 200     -- Max fines
 201     SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
 202
 203     -- Fail if the user has a high fine balance
 204     LOOP
 205         tmp_grp := user_object.profile;
 206         LOOP
 207             SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 1 AND org_unit = tmp_org.id;
 208
 209             IF max_fines.threshold IS NULL THEN
 210                 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
 211             ELSE
 212                 EXIT;
 213             END IF;
 214
 215             IF tmp_grp IS NULL THEN
 216                 EXIT;
 217             END IF;
 218         END LOOP;
 219
 220         IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
 221             EXIT;
 222         END IF;
 223
 224         SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
 225
 226     END LOOP;
 227
 228     IF max_fines.threshold IS NOT NULL THEN
 229
 230         RETURN QUERY
 231             SELECT  *
 232               FROM  actor.usr_standing_penalty
 233               WHERE usr = match_user
 234                     AND org_unit = max_fines.org_unit
 235                     AND (stop_date IS NULL or stop_date > NOW())
 236                     AND standing_penalty = 1;
 237
 238         SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( max_fines.org_unit );
 239
 240         SELECT  SUM(f.balance_owed) INTO current_fines
 241           FROM  money.materialized_billable_xact_summary f
 242                 JOIN (
 243                     SELECT  r.id
 244                       FROM  booking.reservation r
 245                       WHERE r.usr = match_user
 246                             AND r.pickup_lib IN (SELECT * FROM unnest(context_org_list))
 247                             AND xact_finish IS NULL
 248                                 UNION ALL
 249                     SELECT  g.id
 250                       FROM  money.grocery g
 251                       WHERE g.usr = match_user
 252                             AND g.billing_location IN (SELECT * FROM unnest(context_org_list))
 253                             AND xact_finish IS NULL
 254                                 UNION ALL
 255                     SELECT  circ.id
 256                       FROM  action.circulation circ
 257                       WHERE circ.usr = match_user
 258                             AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
 259                             AND xact_finish IS NULL ) l USING (id);
 260
 261         IF current_fines >= max_fines.threshold THEN
 262             new_sp_row.usr := match_user;
 263             new_sp_row.org_unit := max_fines.org_unit;
 264             new_sp_row.standing_penalty := 1;
 265             RETURN NEXT new_sp_row;
 266         END IF;
 267     END IF;
 268
 269     -- Start over for max overdue
 270     SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
 271
 272     -- Fail if the user has too many overdue items
 273     LOOP
 274         tmp_grp := user_object.profile;
 275         LOOP
 276
 277             SELECT * INTO max_overdue FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 2 AND org_unit = tmp_org.id;
 278
 279             IF max_overdue.threshold IS NULL THEN
 280                 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
 281             ELSE
 282                 EXIT;
 283             END IF;
 284
 285             IF tmp_grp IS NULL THEN
 286                 EXIT;
 287             END IF;
 288         END LOOP;
 289
 290         IF max_overdue.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
 291             EXIT;
 292         END IF;
 293
 294         SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
 295
 296     END LOOP;
 297
 298     IF max_overdue.threshold IS NOT NULL THEN
 299
 300         RETURN QUERY
 301             SELECT  *
 302               FROM  actor.usr_standing_penalty
 303               WHERE usr = match_user
 304                     AND org_unit = max_overdue.org_unit
 305                     AND (stop_date IS NULL or stop_date > NOW())
 306                     AND standing_penalty = 2;
 307
 308         SELECT  INTO items_overdue COUNT(*)
 309           FROM  action.circulation circ
 310                 JOIN  actor.org_unit_full_path( max_overdue.org_unit ) fp ON (circ.circ_lib = fp.id)
 311           WHERE circ.usr = match_user
 312             AND circ.checkin_time IS NULL
 313             AND circ.due_date < NOW()
 314             AND (circ.stop_fines = 'MAXFINES' OR circ.stop_fines IS NULL);
 315
 316         IF items_overdue >= max_overdue.threshold::INT THEN
 317             new_sp_row.usr := match_user;
 318             new_sp_row.org_unit := max_overdue.org_unit;
 319             new_sp_row.standing_penalty := 2;
 320             RETURN NEXT new_sp_row;
 321         END IF;
 322     END IF;
 323
 324     -- Start over for max out
 325     SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
 326
 327     -- Fail if the user has too many checked out items
 328     LOOP
 329         tmp_grp := user_object.profile;
 330         LOOP
 331             SELECT * INTO max_items_out FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 3 AND org_unit = tmp_org.id;
 332
 333             IF max_items_out.threshold IS NULL THEN
 334                 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
 335             ELSE
 336                 EXIT;
 337             END IF;
 338
 339             IF tmp_grp IS NULL THEN
 340                 EXIT;
 341             END IF;
 342         END LOOP;
 343
 344         IF max_items_out.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
 345             EXIT;
 346         END IF;
 347
 348         SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
 349
 350     END LOOP;
 351
 352
 353     -- Fail if the user has too many items checked out
 354     IF max_items_out.threshold IS NOT NULL THEN
 355
 356         RETURN QUERY
 357             SELECT  *
 358               FROM  actor.usr_standing_penalty
 359               WHERE usr = match_user
 360                     AND org_unit = max_items_out.org_unit
 361                     AND (stop_date IS NULL or stop_date > NOW())
 362                     AND standing_penalty = 3;
 363
 364         SELECT  INTO items_out COUNT(*)
 365           FROM  action.circulation circ
 366                 JOIN  actor.org_unit_full_path( max_items_out.org_unit ) fp ON (circ.circ_lib = fp.id)
 367           WHERE circ.usr = match_user
 368                 AND circ.checkin_time IS NULL
 369                 AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL);
 370
 371            IF items_out >= max_items_out.threshold::INT THEN
 372             new_sp_row.usr := match_user;
 373             new_sp_row.org_unit := max_items_out.org_unit;
 374             new_sp_row.standing_penalty := 3;
 375             RETURN NEXT new_sp_row;
 376            END IF;
 377     END IF;
 378
 379     -- Start over for collections warning
 380     SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
 381
 382     -- Fail if the user has a collections-level fine balance
 383     LOOP
 384         tmp_grp := user_object.profile;
 385         LOOP
 386             SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 4 AND org_unit = tmp_org.id;
 387
 388             IF max_fines.threshold IS NULL THEN
 389                 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
 390             ELSE
 391                 EXIT;
 392             END IF;
 393
 394             IF tmp_grp IS NULL THEN
 395                 EXIT;
 396             END IF;
 397         END LOOP;
 398
 399         IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
 400             EXIT;
 401         END IF;
 402
 403         SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
 404
 405     END LOOP;
 406
 407     IF max_fines.threshold IS NOT NULL THEN
 408
 409         RETURN QUERY
 410             SELECT  *
 411               FROM  actor.usr_standing_penalty
 412               WHERE usr = match_user
 413                     AND org_unit = max_fines.org_unit
 414                     AND (stop_date IS NULL or stop_date > NOW())
 415                     AND standing_penalty = 4;
 416
 417         SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( max_fines.org_unit );
 418
 419         SELECT  SUM(f.balance_owed) INTO current_fines
 420           FROM  money.materialized_billable_xact_summary f
 421                 JOIN (
 422                     SELECT  r.id
 423                       FROM  booking.reservation r
 424                       WHERE r.usr = match_user
 425                             AND r.pickup_lib IN (SELECT * FROM unnest(context_org_list))
 426                             AND r.xact_finish IS NULL
 427                                 UNION ALL
 428                     SELECT  g.id
 429                       FROM  money.grocery g
 430                       WHERE g.usr = match_user
 431                             AND g.billing_location IN (SELECT * FROM unnest(context_org_list))
 432                             AND g.xact_finish IS NULL
 433                                 UNION ALL
 434                     SELECT  circ.id
 435                       FROM  action.circulation circ
 436                       WHERE circ.usr = match_user
 437                             AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
 438                             AND circ.xact_finish IS NULL ) l USING (id);
 439
 440         IF current_fines >= max_fines.threshold THEN
 441             new_sp_row.usr := match_user;
 442             new_sp_row.org_unit := max_fines.org_unit;
 443             new_sp_row.standing_penalty := 4;
 444             RETURN NEXT new_sp_row;
 445         END IF;
 446     END IF;
 447
 448     -- Start over for in collections
 449     SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
 450
 451     -- Remove the in-collections penalty if the user has paid down enough
 452     -- This penalty is different, because this code is not responsible for creating
 453     -- new in-collections penalties, only for removing them
 454     LOOP
 455         tmp_grp := user_object.profile;
 456         LOOP
 457             SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 30 AND org_unit = tmp_org.id;
 458
 459             IF max_fines.threshold IS NULL THEN
 460                 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
 461             ELSE
 462                 EXIT;
 463             END IF;
 464
 465             IF tmp_grp IS NULL THEN
 466                 EXIT;
 467             END IF;
 468         END LOOP;
 469
 470         IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
 471             EXIT;
 472         END IF;
 473
 474         SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
 475
 476     END LOOP;
 477
 478     IF max_fines.threshold IS NOT NULL THEN
 479
 480         SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( max_fines.org_unit );
 481
 482         -- first, see if the user had paid down to the threshold
 483         SELECT  SUM(f.balance_owed) INTO current_fines
 484           FROM  money.materialized_billable_xact_summary f
 485                 JOIN (
 486                     SELECT  r.id
 487                       FROM  booking.reservation r
 488                       WHERE r.usr = match_user
 489                             AND r.pickup_lib IN (SELECT * FROM unnest(context_org_list))
 490                             AND r.xact_finish IS NULL
 491                                 UNION ALL
 492                     SELECT  g.id
 493                       FROM  money.grocery g
 494                       WHERE g.usr = match_user
 495                             AND g.billing_location IN (SELECT * FROM unnest(context_org_list))
 496                             AND g.xact_finish IS NULL
 497                                 UNION ALL
 498                     SELECT  circ.id
 499                       FROM  action.circulation circ
 500                       WHERE circ.usr = match_user
 501                             AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
 502                             AND circ.xact_finish IS NULL ) l USING (id);
 503
 504         IF current_fines IS NULL OR current_fines <= max_fines.threshold THEN
 505             -- patron has paid down enough
 506
 507             SELECT INTO tmp_penalty * FROM config.standing_penalty WHERE id = 30;
 508
 509             IF tmp_penalty.org_depth IS NOT NULL THEN
 510
 511                 -- since this code is not responsible for applying the penalty, it can't
 512                 -- guarantee the current context org will match the org at which the penalty
 513                 --- was applied.  search up the org tree until we hit the configured penalty depth
 514                 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
 515                 SELECT INTO tmp_depth depth FROM actor.org_unit_type WHERE id = tmp_org.ou_type;
 516
 517                 WHILE tmp_depth >= tmp_penalty.org_depth LOOP
 518
 519                     RETURN QUERY
 520                         SELECT  *
 521                           FROM  actor.usr_standing_penalty
 522                           WHERE usr = match_user
 523                                 AND org_unit = tmp_org.id
 524                                 AND (stop_date IS NULL or stop_date > NOW())
 525                                 AND standing_penalty = 30;
 526
 527                     IF tmp_org.parent_ou IS NULL THEN
 528                         EXIT;
 529                     END IF;
 530
 531                     SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
 532                     SELECT INTO tmp_depth depth FROM actor.org_unit_type WHERE id = tmp_org.ou_type;
 533                 END LOOP;
 534
 535             ELSE
 536
 537                 -- no penalty depth is defined, look for exact matches
 538
 539                 RETURN QUERY
 540                     SELECT  *
 541                       FROM  actor.usr_standing_penalty
 542                       WHERE usr = match_user
 543                             AND org_unit = max_fines.org_unit
 544                             AND (stop_date IS NULL or stop_date > NOW())
 545                             AND standing_penalty = 30;
 546             END IF;
 547
 548         END IF;
 549
 550     END IF;
 551
 552     RETURN;
 553 END;
 554 $func$ LANGUAGE plpgsql;
 555
 556 COMMIT;
 557