Open-ILS/src/sql/Pg/upgrade/XXXX.schema.custom_system_penalties.sql

   1 BEGIN;
   2
   3 SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
   4
   5 INSERT INTO config.org_unit_setting_type (name, label, grp, description, datatype, fm_class) VALUES
   6 (   'circ.custom_penalty_override.PATRON_EXCEEDS_FINES',
   7     oils_i18n_gettext('circ.custom_penalty_override.PATRON_EXCEEDS_FINES',
   8         'Custom PATRON_EXCEEDS_FINES penalty',
   9         'coust', 'label'),
  10     'circ',
  11     oils_i18n_gettext('circ.custom_penalty_override.PATRON_EXCEEDS_FINES',
  12         'Specifies a non-default standing penalty to apply to patrons that exceed the max-fine threshold for their group.',
  13         'coust', 'description'),
  14     'link', 'csp'),
  15 (   'circ.custom_penalty_override.PATRON_EXCEEDS_OVERDUE_COUNT',
  16     oils_i18n_gettext('circ.custom_penalty_override.PATRON_EXCEEDS_OVERDUE_COUNT',
  17         'Custom PATRON_EXCEEDS_OVERDUE_COUNT penalty',
  18         'coust', 'label'),
  19     'circ',
  20     oils_i18n_gettext('circ.custom_penalty_override.PATRON_EXCEEDS_OVERDUE_COUNT',
  21         'Specifies a non-default standing penalty to apply to patrons that exceed the overdue count threshold for their group.',
  22         'coust', 'description'),
  23     'link', 'csp'),
  24 (   'circ.custom_penalty_override.PATRON_EXCEEDS_CHECKOUT_COUNT',
  25     oils_i18n_gettext('circ.custom_penalty_override.PATRON_EXCEEDS_CHECKOUT_COUNT',
  26         'Custom PATRON_EXCEEDS_CHECKOUT_COUNT penalty',
  27         'coust', 'label'),
  28     'circ',
  29     oils_i18n_gettext('circ.custom_penalty_override.PATRON_EXCEEDS_CHECKOUT_COUNT',
  30         'Specifies a non-default standing penalty to apply to patrons that exceed the checkout count threshold for their group.',
  31         'coust', 'description'),
  32     'link', 'csp'),
  33 (   'circ.custom_penalty_override.PATRON_EXCEEDS_COLLECTIONS_WARNING',
  34     oils_i18n_gettext('circ.custom_penalty_override.PATRON_EXCEEDS_COLLECTIONS_WARNING',
  35         'Custom PATRON_EXCEEDS_COLLECTIONS_WARNING penalty',
  36         'coust', 'label'),
  37     'circ',
  38     oils_i18n_gettext('circ.custom_penalty_override.PATRON_EXCEEDS_COLLECTIONS_WARNING',
  39         'Specifies a non-default standing penalty to apply to patrons that exceed the collections fine warning threshold for their group.',
  40         'coust', 'description'),
  41     'link', 'csp'),
  42 (   'circ.custom_penalty_override.PATRON_EXCEEDS_LOST_COUNT',
  43     oils_i18n_gettext('circ.custom_penalty_override.PATRON_EXCEEDS_LOST_COUNT',
  44         'Custom PATRON_EXCEEDS_LOST_COUNT penalty',
  45         'coust', 'label'),
  46     'circ',
  47     oils_i18n_gettext('circ.custom_penalty_override.PATRON_EXCEEDS_LOST_COUNT',
  48         'Specifies a non-default standing penalty to apply to patrons that exceed the lost item count threshold for their group.',
  49         'coust', 'description'),
  50     'link', 'csp'),
  51 (   'circ.custom_penalty_override.PATRON_EXCEEDS_LONGOVERDUE_COUNT',
  52     oils_i18n_gettext('circ.custom_penalty_override.PATRON_EXCEEDS_LONGOVERDUE_COUNT',
  53         'Custom PATRON_EXCEEDS_LONGOVERDUE_COUNT penalty',
  54         'coust', 'label'),
  55     'circ',
  56     oils_i18n_gettext('circ.custom_penalty_override.PATRON_EXCEEDS_LONGOVERDUE_COUNT',
  57         'Specifies a non-default standing penalty to apply to patrons that exceed the long-overdue item count threshold for their group.',
  58         'coust', 'description'),
  59     'link', 'csp'),
  60 (   'circ.custom_penalty_override.PATRON_IN_COLLECTIONS',
  61     oils_i18n_gettext('circ.custom_penalty_override.PATRON_IN_COLLECTIONS',
  62         'Custom PATRON_IN_COLLECTIONS penalty',
  63         'coust', 'label'),
  64     'circ',
  65     oils_i18n_gettext('circ.custom_penalty_override.PATRON_IN_COLLECTIONS',
  66         'Specifies a non-default standing penalty that may have been applied to patrons that have been placed into collections and that should be automatically removed if they have paid down their balance below the threshold for their group. Use of this feature will likely require configuration and coordination with an external collection agency.',
  67         'coust', 'description'),
  68     'link', 'csp')
  69 ;
  70
  71 CREATE OR REPLACE FUNCTION actor.calculate_system_penalties( match_user INT, context_org INT ) RETURNS SETOF actor.usr_standing_penalty AS $func$
  72 DECLARE
  73     user_object         actor.usr%ROWTYPE;
  74     new_sp_row          actor.usr_standing_penalty%ROWTYPE;
  75     existing_sp_row     actor.usr_standing_penalty%ROWTYPE;
  76     collections_fines   permission.grp_penalty_threshold%ROWTYPE;
  77     max_fines           permission.grp_penalty_threshold%ROWTYPE;
  78     max_overdue         permission.grp_penalty_threshold%ROWTYPE;
  79     max_items_out       permission.grp_penalty_threshold%ROWTYPE;
  80     max_lost            permission.grp_penalty_threshold%ROWTYPE;
  81     max_longoverdue     permission.grp_penalty_threshold%ROWTYPE;
  82     penalty_id          INT;
  83     tmp_grp             INT;
  84     items_overdue       INT;
  85     items_out           INT;
  86     items_lost          INT;
  87     items_longoverdue   INT;
  88     context_org_list    INT[];
  89     current_fines        NUMERIC(8,2) := 0.0;
  90     tmp_fines            NUMERIC(8,2);
  91     tmp_groc            RECORD;
  92     tmp_circ            RECORD;
  93     tmp_org             actor.org_unit%ROWTYPE;
  94     tmp_penalty         config.standing_penalty%ROWTYPE;
  95     tmp_depth           INTEGER;
  96 BEGIN
  97     SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
  98
  99     -- Max fines
 100     SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
 101     SELECT BTRIM(value,'"')::INT INTO penalty_id FROM actor.org_unit_ancestor_setting('circ.custom_penalty_override.PATRON_EXCEEDS_FINES', context_org);
 102     IF NOT FOUND THEN penalty_id := 1; END IF;
 103
 104     -- Fail if the user has a high fine balance
 105     LOOP
 106         tmp_grp := user_object.profile;
 107         LOOP
 108             SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = penalty_id AND org_unit = tmp_org.id;
 109
 110             IF max_fines.threshold IS NULL THEN
 111                 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
 112             ELSE
 113                 EXIT;
 114             END IF;
 115
 116             IF tmp_grp IS NULL THEN
 117                 EXIT;
 118             END IF;
 119         END LOOP;
 120
 121         IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
 122             EXIT;
 123         END IF;
 124
 125         SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
 126
 127     END LOOP;
 128
 129     IF max_fines.threshold IS NOT NULL THEN
 130         -- The IN clause in all of the RETURN QUERY calls is used to surface now-stale non-custom penalties
 131         -- so that the calling code can clear them at the boundary where custom penalties are configured.
 132         -- Otherwise we would see orphaned "stock" system penalties that would never go away on their own.
 133         RETURN QUERY
 134             SELECT  *
 135               FROM  actor.usr_standing_penalty
 136               WHERE usr = match_user
 137                     AND org_unit = max_fines.org_unit
 138                     AND (stop_date IS NULL or stop_date > NOW())
 139                     AND standing_penalty IN (1, penalty_id);
 140
 141         SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( max_fines.org_unit );
 142
 143         SELECT  SUM(f.balance_owed) INTO current_fines
 144           FROM  money.materialized_billable_xact_summary f
 145                 JOIN (
 146                     SELECT  r.id
 147                       FROM  booking.reservation r
 148                       WHERE r.usr = match_user
 149                             AND r.pickup_lib IN (SELECT * FROM unnest(context_org_list))
 150                             AND xact_finish IS NULL
 151                                 UNION ALL
 152                     SELECT  g.id
 153                       FROM  money.grocery g
 154                       WHERE g.usr = match_user
 155                             AND g.billing_location IN (SELECT * FROM unnest(context_org_list))
 156                             AND xact_finish IS NULL
 157                                 UNION ALL
 158                     SELECT  circ.id
 159                       FROM  action.circulation circ
 160                       WHERE circ.usr = match_user
 161                             AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
 162                             AND xact_finish IS NULL ) l USING (id);
 163
 164         IF current_fines >= max_fines.threshold THEN
 165             new_sp_row.usr := match_user;
 166             new_sp_row.org_unit := max_fines.org_unit;
 167             new_sp_row.standing_penalty := penalty_id;
 168             RETURN NEXT new_sp_row;
 169         END IF;
 170     END IF;
 171
 172     -- Start over for max overdue
 173     SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
 174     SELECT BTRIM(value,'"')::INT INTO penalty_id FROM actor.org_unit_ancestor_setting('circ.custom_penalty_override.PATRON_EXCEEDS_OVERDUE_COUNT', context_org);
 175     IF NOT FOUND THEN penalty_id := 2; END IF;
 176
 177     -- Fail if the user has too many overdue items
 178     LOOP
 179         tmp_grp := user_object.profile;
 180         LOOP
 181
 182             SELECT * INTO max_overdue FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = penalty_id AND org_unit = tmp_org.id;
 183
 184             IF max_overdue.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_overdue.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     IF max_overdue.threshold IS NOT NULL THEN
 204
 205         RETURN QUERY
 206             SELECT  *
 207               FROM  actor.usr_standing_penalty
 208               WHERE usr = match_user
 209                     AND org_unit = max_overdue.org_unit
 210                     AND (stop_date IS NULL or stop_date > NOW())
 211                     AND standing_penalty IN (2, penalty_id);
 212
 213         SELECT  INTO items_overdue COUNT(*)
 214           FROM  action.circulation circ
 215                 JOIN  actor.org_unit_full_path( max_overdue.org_unit ) fp ON (circ.circ_lib = fp.id)
 216           WHERE circ.usr = match_user
 217             AND circ.checkin_time IS NULL
 218             AND circ.due_date < NOW()
 219             AND (circ.stop_fines = 'MAXFINES' OR circ.stop_fines IS NULL);
 220
 221         IF items_overdue >= max_overdue.threshold::INT THEN
 222             new_sp_row.usr := match_user;
 223             new_sp_row.org_unit := max_overdue.org_unit;
 224             new_sp_row.standing_penalty := penalty_id;
 225             RETURN NEXT new_sp_row;
 226         END IF;
 227     END IF;
 228
 229     -- Start over for max out
 230     SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
 231     SELECT BTRIM(value,'"')::INT INTO penalty_id FROM actor.org_unit_ancestor_setting('circ.custom_penalty_override.PATRON_EXCEEDS_CHECKOUT_COUNT', context_org);
 232     IF NOT FOUND THEN penalty_id := 3; END IF;
 233
 234     -- Fail if the user has too many checked out items
 235     LOOP
 236         tmp_grp := user_object.profile;
 237         LOOP
 238             SELECT * INTO max_items_out FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = penalty_id AND org_unit = tmp_org.id;
 239
 240             IF max_items_out.threshold IS NULL THEN
 241                 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
 242             ELSE
 243                 EXIT;
 244             END IF;
 245
 246             IF tmp_grp IS NULL THEN
 247                 EXIT;
 248             END IF;
 249         END LOOP;
 250
 251         IF max_items_out.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
 252             EXIT;
 253         END IF;
 254
 255         SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
 256
 257     END LOOP;
 258
 259
 260     -- Fail if the user has too many items checked out
 261     IF max_items_out.threshold IS NOT NULL THEN
 262
 263         RETURN QUERY
 264             SELECT  *
 265               FROM  actor.usr_standing_penalty
 266               WHERE usr = match_user
 267                     AND org_unit = max_items_out.org_unit
 268                     AND (stop_date IS NULL or stop_date > NOW())
 269                     AND standing_penalty IN (3, penalty_id);
 270
 271         SELECT  INTO items_out COUNT(*)
 272           FROM  action.circulation circ
 273                 JOIN  actor.org_unit_full_path( max_items_out.org_unit ) fp ON (circ.circ_lib = fp.id)
 274           WHERE circ.usr = match_user
 275                 AND circ.checkin_time IS NULL
 276                 AND (circ.stop_fines IN (
 277                     SELECT 'MAXFINES'::TEXT
 278                     UNION ALL
 279                     SELECT 'LONGOVERDUE'::TEXT
 280                     UNION ALL
 281                     SELECT 'LOST'::TEXT
 282                     WHERE 'true' ILIKE
 283                     (
 284                         SELECT CASE
 285                             WHEN (SELECT value FROM actor.org_unit_ancestor_setting('circ.tally_lost', circ.circ_lib)) ILIKE 'true' THEN 'true'
 286                             ELSE 'false'
 287                         END
 288                     )
 289                     UNION ALL
 290                     SELECT 'CLAIMSRETURNED'::TEXT
 291                     WHERE 'false' ILIKE
 292                     (
 293                         SELECT CASE
 294                             WHEN (SELECT value FROM actor.org_unit_ancestor_setting('circ.do_not_tally_claims_returned', circ.circ_lib)) ILIKE 'true' THEN 'true'
 295                             ELSE 'false'
 296                         END
 297                     )
 298                     ) OR circ.stop_fines IS NULL)
 299                 AND xact_finish IS NULL;
 300
 301            IF items_out >= max_items_out.threshold::INT THEN
 302             new_sp_row.usr := match_user;
 303             new_sp_row.org_unit := max_items_out.org_unit;
 304             new_sp_row.standing_penalty := penalty_id;
 305             RETURN NEXT new_sp_row;
 306            END IF;
 307     END IF;
 308
 309     -- Start over for max lost
 310     SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
 311     SELECT BTRIM(value,'"')::INT INTO penalty_id FROM actor.org_unit_ancestor_setting('circ.custom_penalty_override.PATRON_EXCEEDS_LOST_COUNT', context_org);
 312     IF NOT FOUND THEN penalty_id := 5; END IF;
 313
 314     -- Fail if the user has too many lost items
 315     LOOP
 316         tmp_grp := user_object.profile;
 317         LOOP
 318
 319             SELECT * INTO max_lost FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = penalty_id AND org_unit = tmp_org.id;
 320
 321             IF max_lost.threshold IS NULL THEN
 322                 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
 323             ELSE
 324                 EXIT;
 325             END IF;
 326
 327             IF tmp_grp IS NULL THEN
 328                 EXIT;
 329             END IF;
 330         END LOOP;
 331
 332         IF max_lost.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
 333             EXIT;
 334         END IF;
 335
 336         SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
 337
 338     END LOOP;
 339
 340     IF max_lost.threshold IS NOT NULL THEN
 341
 342         RETURN QUERY
 343             SELECT  *
 344             FROM  actor.usr_standing_penalty
 345             WHERE usr = match_user
 346                 AND org_unit = max_lost.org_unit
 347                 AND (stop_date IS NULL or stop_date > NOW())
 348                 AND standing_penalty IN (5, penalty_id);
 349
 350         SELECT  INTO items_lost COUNT(*)
 351         FROM  action.circulation circ
 352             JOIN  actor.org_unit_full_path( max_lost.org_unit ) fp ON (circ.circ_lib = fp.id)
 353         WHERE circ.usr = match_user
 354             AND circ.checkin_time IS NULL
 355             AND (circ.stop_fines = 'LOST')
 356             AND xact_finish IS NULL;
 357
 358         IF items_lost >= max_lost.threshold::INT AND 0 < max_lost.threshold::INT THEN
 359             new_sp_row.usr := match_user;
 360             new_sp_row.org_unit := max_lost.org_unit;
 361             new_sp_row.standing_penalty := penalty_id;
 362             RETURN NEXT new_sp_row;
 363         END IF;
 364     END IF;
 365
 366     -- Start over for max longoverdue
 367     SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
 368     SELECT BTRIM(value,'"')::INT INTO penalty_id FROM actor.org_unit_ancestor_setting('circ.custom_penalty_override.PATRON_EXCEEDS_LONGOVERDUE_COUNT', context_org);
 369     IF NOT FOUND THEN penalty_id := 35; END IF;
 370
 371     -- Fail if the user has too many longoverdue items
 372     LOOP
 373         tmp_grp := user_object.profile;
 374         LOOP
 375
 376             SELECT * INTO max_longoverdue
 377                 FROM permission.grp_penalty_threshold
 378                 WHERE grp = tmp_grp AND
 379                     penalty = penalty_id AND
 380                     org_unit = tmp_org.id;
 381
 382             IF max_longoverdue.threshold IS NULL THEN
 383                 SELECT parent INTO tmp_grp
 384                     FROM permission.grp_tree WHERE id = tmp_grp;
 385             ELSE
 386                 EXIT;
 387             END IF;
 388
 389             IF tmp_grp IS NULL THEN
 390                 EXIT;
 391             END IF;
 392         END LOOP;
 393
 394         IF max_longoverdue.threshold IS NOT NULL
 395                 OR tmp_org.parent_ou IS NULL THEN
 396             EXIT;
 397         END IF;
 398
 399         SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
 400
 401     END LOOP;
 402
 403     IF max_longoverdue.threshold IS NOT NULL THEN
 404
 405         RETURN QUERY
 406             SELECT  *
 407             FROM  actor.usr_standing_penalty
 408             WHERE usr = match_user
 409                 AND org_unit = max_longoverdue.org_unit
 410                 AND (stop_date IS NULL or stop_date > NOW())
 411                 AND standing_penalty IN (35, penalty_id);
 412
 413         SELECT INTO items_longoverdue COUNT(*)
 414         FROM action.circulation circ
 415             JOIN actor.org_unit_full_path( max_longoverdue.org_unit ) fp
 416                 ON (circ.circ_lib = fp.id)
 417         WHERE circ.usr = match_user
 418             AND circ.checkin_time IS NULL
 419             AND (circ.stop_fines = 'LONGOVERDUE')
 420             AND xact_finish IS NULL;
 421
 422         IF items_longoverdue >= max_longoverdue.threshold::INT
 423                 AND 0 < max_longoverdue.threshold::INT THEN
 424             new_sp_row.usr := match_user;
 425             new_sp_row.org_unit := max_longoverdue.org_unit;
 426             new_sp_row.standing_penalty := penalty_id;
 427             RETURN NEXT new_sp_row;
 428         END IF;
 429     END IF;
 430
 431
 432     -- Start over for collections warning
 433     SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
 434     SELECT BTRIM(value,'"')::INT INTO penalty_id FROM actor.org_unit_ancestor_setting('circ.custom_penalty_override.PATRON_EXCEEDS_COLLECTIONS_WARNING', context_org);
 435     IF NOT FOUND THEN penalty_id := 4; END IF;
 436
 437     -- Fail if the user has a collections-level fine balance
 438     LOOP
 439         tmp_grp := user_object.profile;
 440         LOOP
 441             SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = penalty_id AND org_unit = tmp_org.id;
 442
 443             IF max_fines.threshold IS NULL THEN
 444                 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
 445             ELSE
 446                 EXIT;
 447             END IF;
 448
 449             IF tmp_grp IS NULL THEN
 450                 EXIT;
 451             END IF;
 452         END LOOP;
 453
 454         IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
 455             EXIT;
 456         END IF;
 457
 458         SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
 459
 460     END LOOP;
 461
 462     IF max_fines.threshold IS NOT NULL THEN
 463
 464         RETURN QUERY
 465             SELECT  *
 466               FROM  actor.usr_standing_penalty
 467               WHERE usr = match_user
 468                     AND org_unit = max_fines.org_unit
 469                     AND (stop_date IS NULL or stop_date > NOW())
 470                     AND standing_penalty IN (4, penalty_id);
 471
 472         SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( max_fines.org_unit );
 473
 474         SELECT  SUM(f.balance_owed) INTO current_fines
 475           FROM  money.materialized_billable_xact_summary f
 476                 JOIN (
 477                     SELECT  r.id
 478                       FROM  booking.reservation r
 479                       WHERE r.usr = match_user
 480                             AND r.pickup_lib IN (SELECT * FROM unnest(context_org_list))
 481                             AND r.xact_finish IS NULL
 482                                 UNION ALL
 483                     SELECT  g.id
 484                       FROM  money.grocery g
 485                       WHERE g.usr = match_user
 486                             AND g.billing_location IN (SELECT * FROM unnest(context_org_list))
 487                             AND g.xact_finish IS NULL
 488                                 UNION ALL
 489                     SELECT  circ.id
 490                       FROM  action.circulation circ
 491                       WHERE circ.usr = match_user
 492                             AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
 493                             AND circ.xact_finish IS NULL ) l USING (id);
 494
 495         IF current_fines >= max_fines.threshold THEN
 496             new_sp_row.usr := match_user;
 497             new_sp_row.org_unit := max_fines.org_unit;
 498             new_sp_row.standing_penalty := penalty_id;
 499             RETURN NEXT new_sp_row;
 500         END IF;
 501     END IF;
 502
 503     -- Start over for in collections
 504     SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
 505     SELECT BTRIM(value,'"')::INT INTO penalty_id FROM actor.org_unit_ancestor_setting('circ.custom_penalty_override.PATRON_IN_COLLECTIONS', context_org);
 506     IF NOT FOUND THEN penalty_id := 30; END IF;
 507
 508     -- Remove the in-collections penalty if the user has paid down enough
 509     -- This penalty is different, because this code is not responsible for creating
 510     -- new in-collections penalties, only for removing them
 511     LOOP
 512         tmp_grp := user_object.profile;
 513         LOOP
 514             SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = penalty_id AND org_unit = tmp_org.id;
 515
 516             IF max_fines.threshold IS NULL THEN
 517                 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
 518             ELSE
 519                 EXIT;
 520             END IF;
 521
 522             IF tmp_grp IS NULL THEN
 523                 EXIT;
 524             END IF;
 525         END LOOP;
 526
 527         IF max_fines.threshold IS NOT NULL OR 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
 533     END LOOP;
 534
 535     IF max_fines.threshold IS NOT NULL THEN
 536
 537         SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( max_fines.org_unit );
 538
 539         -- first, see if the user had paid down to the threshold
 540         SELECT  SUM(f.balance_owed) INTO current_fines
 541           FROM  money.materialized_billable_xact_summary f
 542                 JOIN (
 543                     SELECT  r.id
 544                       FROM  booking.reservation r
 545                       WHERE r.usr = match_user
 546                             AND r.pickup_lib IN (SELECT * FROM unnest(context_org_list))
 547                             AND r.xact_finish IS NULL
 548                                 UNION ALL
 549                     SELECT  g.id
 550                       FROM  money.grocery g
 551                       WHERE g.usr = match_user
 552                             AND g.billing_location IN (SELECT * FROM unnest(context_org_list))
 553                             AND g.xact_finish IS NULL
 554                                 UNION ALL
 555                     SELECT  circ.id
 556                       FROM  action.circulation circ
 557                       WHERE circ.usr = match_user
 558                             AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
 559                             AND circ.xact_finish IS NULL ) l USING (id);
 560
 561         IF current_fines IS NULL OR current_fines <= max_fines.threshold THEN
 562             -- patron has paid down enough
 563
 564             SELECT INTO tmp_penalty * FROM config.standing_penalty WHERE id = penalty_id;
 565
 566             IF tmp_penalty.org_depth IS NOT NULL THEN
 567
 568                 -- since this code is not responsible for applying the penalty, it can't
 569                 -- guarantee the current context org will match the org at which the penalty
 570                 --- was applied.  search up the org tree until we hit the configured penalty depth
 571                 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
 572                 SELECT INTO tmp_depth depth FROM actor.org_unit_type WHERE id = tmp_org.ou_type;
 573
 574                 WHILE tmp_depth >= tmp_penalty.org_depth LOOP
 575
 576                     RETURN QUERY
 577                         SELECT  *
 578                           FROM  actor.usr_standing_penalty
 579                           WHERE usr = match_user
 580                                 AND org_unit = tmp_org.id
 581                                 AND (stop_date IS NULL or stop_date > NOW())
 582                                 AND standing_penalty IN (30, penalty_id);
 583
 584                     IF tmp_org.parent_ou IS NULL THEN
 585                         EXIT;
 586                     END IF;
 587
 588                     SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
 589                     SELECT INTO tmp_depth depth FROM actor.org_unit_type WHERE id = tmp_org.ou_type;
 590                 END LOOP;
 591
 592             ELSE
 593
 594                 -- no penalty depth is defined, look for exact matches
 595
 596                 RETURN QUERY
 597                     SELECT  *
 598                       FROM  actor.usr_standing_penalty
 599                       WHERE usr = match_user
 600                             AND org_unit = max_fines.org_unit
 601                             AND (stop_date IS NULL or stop_date > NOW())
 602                             AND standing_penalty IN (30, penalty_id);
 603             END IF;
 604
 605         END IF;
 606
 607     END IF;
 608
 609     RETURN;
 610 END;
 611 $func$ LANGUAGE plpgsql;
 612
 613 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$
 614 DECLARE
 615     user_object             actor.usr%ROWTYPE;
 616     standing_penalty        config.standing_penalty%ROWTYPE;
 617     item_object             asset.copy%ROWTYPE;
 618     item_status_object      config.copy_status%ROWTYPE;
 619     item_location_object    asset.copy_location%ROWTYPE;
 620     result                  action.circ_matrix_test_result;
 621     circ_test               action.found_circ_matrix_matchpoint;
 622     circ_matchpoint         config.circ_matrix_matchpoint%ROWTYPE;
 623     circ_limit_set          config.circ_limit_set%ROWTYPE;
 624     hold_ratio              action.hold_stats%ROWTYPE;
 625     penalty_type            TEXT;
 626     penalty_id              INT;
 627     items_out               INT;
 628     context_org_list        INT[];
 629     permit_renew            TEXT;
 630     done                    BOOL := FALSE;
 631     item_prox               INT;
 632     home_prox               INT;
 633 BEGIN
 634     -- Assume success unless we hit a failure condition
 635     result.success := TRUE;
 636
 637     -- Need user info to look up matchpoints
 638     SELECT INTO user_object * FROM actor.usr WHERE id = match_user AND NOT deleted;
 639
 640     -- (Insta)Fail if we couldn't find the user
 641     IF user_object.id IS NULL THEN
 642         result.fail_part := 'no_user';
 643         result.success := FALSE;
 644         done := TRUE;
 645         RETURN NEXT result;
 646         RETURN;
 647     END IF;
 648
 649     -- Need item info to look up matchpoints
 650     SELECT INTO item_object * FROM asset.copy WHERE id = match_item AND NOT deleted;
 651
 652     -- (Insta)Fail if we couldn't find the item
 653     IF item_object.id IS NULL THEN
 654         result.fail_part := 'no_item';
 655         result.success := FALSE;
 656         done := TRUE;
 657         RETURN NEXT result;
 658         RETURN;
 659     END IF;
 660
 661     SELECT INTO circ_test * FROM action.find_circ_matrix_matchpoint(circ_ou, item_object, user_object, renewal);
 662
 663     circ_matchpoint             := circ_test.matchpoint;
 664     result.matchpoint           := circ_matchpoint.id;
 665     result.circulate            := circ_matchpoint.circulate;
 666     result.duration_rule        := circ_matchpoint.duration_rule;
 667     result.recurring_fine_rule  := circ_matchpoint.recurring_fine_rule;
 668     result.max_fine_rule        := circ_matchpoint.max_fine_rule;
 669     result.hard_due_date        := circ_matchpoint.hard_due_date;
 670     result.renewals             := circ_matchpoint.renewals;
 671     result.grace_period         := circ_matchpoint.grace_period;
 672     result.buildrows            := circ_test.buildrows;
 673
 674     -- (Insta)Fail if we couldn't find a matchpoint
 675     IF circ_test.success = false THEN
 676         result.fail_part := 'no_matchpoint';
 677         result.success := FALSE;
 678         done := TRUE;
 679         RETURN NEXT result;
 680         RETURN;
 681     END IF;
 682
 683     -- All failures before this point are non-recoverable
 684     -- Below this point are possibly overridable failures
 685
 686     -- Fail if the user is barred
 687     IF user_object.barred IS TRUE THEN
 688         result.fail_part := 'actor.usr.barred';
 689         result.success := FALSE;
 690         done := TRUE;
 691         RETURN NEXT result;
 692     END IF;
 693
 694     -- Fail if the item can't circulate
 695     IF item_object.circulate IS FALSE THEN
 696         result.fail_part := 'asset.copy.circulate';
 697         result.success := FALSE;
 698         done := TRUE;
 699         RETURN NEXT result;
 700     END IF;
 701
 702     -- Fail if the item isn't in a circulateable status on a non-renewal
 703     IF NOT renewal AND item_object.status <> 8 AND item_object.status NOT IN (
 704         (SELECT id FROM config.copy_status WHERE is_available) ) THEN
 705         result.fail_part := 'asset.copy.status';
 706         result.success := FALSE;
 707         done := TRUE;
 708         RETURN NEXT result;
 709     -- Alternately, fail if the item isn't checked out on a renewal
 710     ELSIF renewal AND item_object.status <> 1 THEN
 711         result.fail_part := 'asset.copy.status';
 712         result.success := FALSE;
 713         done := TRUE;
 714         RETURN NEXT result;
 715     END IF;
 716
 717     -- Fail if the item can't circulate because of the shelving location
 718     SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
 719     IF item_location_object.circulate IS FALSE THEN
 720         result.fail_part := 'asset.copy_location.circulate';
 721         result.success := FALSE;
 722         done := TRUE;
 723         RETURN NEXT result;
 724     END IF;
 725
 726     -- Use Circ OU for penalties and such
 727     SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( circ_ou );
 728
 729     -- Proximity of user's home_ou to circ_ou to see if penalties should be ignored.
 730     SELECT INTO home_prox prox FROM actor.org_unit_proximity WHERE from_org = user_object.home_ou AND to_org = circ_ou;
 731
 732     -- Proximity of user's home_ou to item circ_lib to see if penalties should be ignored.
 733     SELECT INTO item_prox prox FROM actor.org_unit_proximity WHERE from_org = user_object.home_ou AND to_org = item_object.circ_lib;
 734
 735     IF renewal THEN
 736         penalty_type = '%RENEW%';
 737     ELSE
 738         penalty_type = '%CIRC%';
 739     END IF;
 740
 741     -- Look up any custom override for PATRON_EXCEEDS_FINES penalty
 742     SELECT BTRIM(value,'"')::INT INTO penalty_id FROM actor.org_unit_ancestor_setting('circ.custom_penalty_override.PATRON_EXCEEDS_FINES', circ_ou);
 743     IF NOT FOUND THEN penalty_id := 1; END IF;
 744
 745     FOR standing_penalty IN
 746         SELECT  DISTINCT csp.*
 747           FROM  actor.usr_standing_penalty usp
 748                 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
 749           WHERE usr = match_user
 750                 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
 751                 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
 752                 AND (csp.ignore_proximity IS NULL
 753                      OR csp.ignore_proximity < home_prox
 754                      OR csp.ignore_proximity < item_prox)
 755                 AND csp.block_list LIKE penalty_type LOOP
 756         -- override PATRON_EXCEEDS_FINES penalty for renewals based on org setting
 757         IF renewal AND standing_penalty.id = penalty_id THEN
 758             SELECT INTO permit_renew value FROM actor.org_unit_ancestor_setting('circ.permit_renew_when_exceeds_fines', circ_ou);
 759             IF permit_renew IS NOT NULL AND permit_renew ILIKE 'true' THEN
 760                 CONTINUE;
 761             END IF;
 762         END IF;
 763
 764         result.fail_part := standing_penalty.name;
 765         result.success := FALSE;
 766         done := TRUE;
 767         RETURN NEXT result;
 768     END LOOP;
 769
 770     -- Fail if the test is set to hard non-circulating
 771     IF circ_matchpoint.circulate IS FALSE THEN
 772         result.fail_part := 'config.circ_matrix_test.circulate';
 773         result.success := FALSE;
 774         done := TRUE;
 775         RETURN NEXT result;
 776     END IF;
 777
 778     -- Fail if the total copy-hold ratio is too low
 779     IF circ_matchpoint.total_copy_hold_ratio IS NOT NULL THEN
 780         SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
 781         IF hold_ratio.total_copy_ratio IS NOT NULL AND hold_ratio.total_copy_ratio < circ_matchpoint.total_copy_hold_ratio THEN
 782             result.fail_part := 'config.circ_matrix_test.total_copy_hold_ratio';
 783             result.success := FALSE;
 784             done := TRUE;
 785             RETURN NEXT result;
 786         END IF;
 787     END IF;
 788
 789     -- Fail if the available copy-hold ratio is too low
 790     IF circ_matchpoint.available_copy_hold_ratio IS NOT NULL THEN
 791         IF hold_ratio.hold_count IS NULL THEN
 792             SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
 793         END IF;
 794         IF hold_ratio.available_copy_ratio IS NOT NULL AND hold_ratio.available_copy_ratio < circ_matchpoint.available_copy_hold_ratio THEN
 795             result.fail_part := 'config.circ_matrix_test.available_copy_hold_ratio';
 796             result.success := FALSE;
 797             done := TRUE;
 798             RETURN NEXT result;
 799         END IF;
 800     END IF;
 801
 802     -- Fail if the user has too many items out by defined limit sets
 803     FOR circ_limit_set IN SELECT ccls.* FROM config.circ_limit_set ccls
 804       JOIN config.circ_matrix_limit_set_map ccmlsm ON ccmlsm.limit_set = ccls.id
 805       WHERE ccmlsm.active AND ( ccmlsm.matchpoint = circ_matchpoint.id OR
 806         ( ccmlsm.matchpoint IN (SELECT * FROM unnest(result.buildrows)) AND ccmlsm.fallthrough )
 807         ) LOOP
 808             IF circ_limit_set.items_out > 0 AND NOT renewal THEN
 809                 SELECT INTO context_org_list ARRAY_AGG(aou.id)
 810                   FROM actor.org_unit_full_path( circ_ou ) aou
 811                     JOIN actor.org_unit_type aout ON aou.ou_type = aout.id
 812                   WHERE aout.depth >= circ_limit_set.depth;
 813                 IF circ_limit_set.global THEN
 814                     WITH RECURSIVE descendant_depth AS (
 815                         SELECT  ou.id,
 816                             ou.parent_ou
 817                         FROM  actor.org_unit ou
 818                         WHERE ou.id IN (SELECT * FROM unnest(context_org_list))
 819                             UNION
 820                         SELECT  ou.id,
 821                             ou.parent_ou
 822                         FROM  actor.org_unit ou
 823                             JOIN descendant_depth ot ON (ot.id = ou.parent_ou)
 824                     ) SELECT INTO context_org_list ARRAY_AGG(ou.id) FROM actor.org_unit ou JOIN descendant_depth USING (id);
 825                 END IF;
 826                 SELECT INTO items_out COUNT(DISTINCT circ.id)
 827                   FROM action.circulation circ
 828                     JOIN asset.copy copy ON (copy.id = circ.target_copy)
 829                     LEFT JOIN action.circulation_limit_group_map aclgm ON (circ.id = aclgm.circ)
 830                   WHERE circ.usr = match_user
 831                     AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
 832                     AND circ.checkin_time IS NULL
 833                     AND circ.xact_finish IS NULL
 834                     AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL)
 835                     AND (copy.circ_modifier IN (SELECT circ_mod FROM config.circ_limit_set_circ_mod_map WHERE limit_set = circ_limit_set.id)
 836                         OR copy.location IN (SELECT copy_loc FROM config.circ_limit_set_copy_loc_map WHERE limit_set = circ_limit_set.id)
 837                         OR aclgm.limit_group IN (SELECT limit_group FROM config.circ_limit_set_group_map WHERE limit_set = circ_limit_set.id)
 838                     );
 839                 IF items_out >= circ_limit_set.items_out THEN
 840                     result.fail_part := 'config.circ_matrix_circ_mod_test';
 841                     result.success := FALSE;
 842                     done := TRUE;
 843                     RETURN NEXT result;
 844                 END IF;
 845             END IF;
 846             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;
 847     END LOOP;
 848
 849     -- If we passed everything, return the successful matchpoint
 850     IF NOT done THEN
 851         RETURN NEXT result;
 852     END IF;
 853
 854     RETURN;
 855 END;
 856 $func$ LANGUAGE plpgsql;
 857
 858 COMMIT;
 859