Open-ILS/src/sql/Pg/upgrade/0794.add_option_for_including_lost_in_items_out_and_patron_blocking_by_lost.sql

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