Open-ILS/src/sql/Pg/upgrade/0831.schema.long-overdue-penalty.sql

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