Open-ILS/src/sql/Pg/upgrade/0118.schema.collections-penalty.sql

   1 BEGIN;
   2
   3 INSERT INTO config.upgrade_log (version) VALUES ('0118');
   4
   5 INSERT INTO config.standing_penalty (id,name,label) VALUES (30,'PATRON_IN_COLLECTIONS','Patron has been referred to a collections agency');
   6
   7 CREATE OR REPLACE FUNCTION actor.calculate_system_penalties( match_user INT, context_org INT ) RETURNS SETOF actor.usr_standing_penalty AS $func$
   8 DECLARE
   9     user_object         actor.usr%ROWTYPE;
  10     new_sp_row          actor.usr_standing_penalty%ROWTYPE;
  11     existing_sp_row     actor.usr_standing_penalty%ROWTYPE;
  12     collections_fines   permission.grp_penalty_threshold%ROWTYPE;
  13     max_fines           permission.grp_penalty_threshold%ROWTYPE;
  14     max_overdue         permission.grp_penalty_threshold%ROWTYPE;
  15     max_items_out       permission.grp_penalty_threshold%ROWTYPE;
  16     tmp_grp             INT;
  17     items_overdue       INT;
  18     items_out           INT;
  19     context_org_list    INT[];
  20     current_fines        NUMERIC(8,2) := 0.0;
  21     tmp_fines            NUMERIC(8,2);
  22     tmp_groc            RECORD;
  23     tmp_circ            RECORD;
  24     tmp_org             actor.org_unit%ROWTYPE;
  25     tmp_penalty         config.standing_penalty%ROWTYPE;
  26     tmp_depth           INTEGER;
  27 BEGIN
  28     SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
  29
  30     -- Max fines
  31     SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
  32
  33     -- Fail if the user has a high fine balance
  34     LOOP
  35         tmp_grp := user_object.profile;
  36         LOOP
  37             SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 1 AND org_unit = tmp_org.id;
  38
  39             IF max_fines.threshold IS NULL THEN
  40                 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
  41             ELSE
  42                 EXIT;
  43             END IF;
  44
  45             IF tmp_grp IS NULL THEN
  46                 EXIT;
  47             END IF;
  48         END LOOP;
  49
  50         IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
  51             EXIT;
  52         END IF;
  53
  54         SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
  55
  56     END LOOP;
  57
  58     IF max_fines.threshold IS NOT NULL THEN
  59
  60         FOR existing_sp_row IN
  61                 SELECT  *
  62                   FROM  actor.usr_standing_penalty
  63                   WHERE usr = match_user
  64                         AND org_unit = max_fines.org_unit
  65                         AND (stop_date IS NULL or stop_date > NOW())
  66                         AND standing_penalty = 1
  67                 LOOP
  68             RETURN NEXT existing_sp_row;
  69         END LOOP;
  70
  71         SELECT  SUM(f.balance_owed) INTO current_fines
  72           FROM  money.materialized_billable_xact_summary f
  73                 JOIN (
  74                     SELECT  r.id
  75                       FROM  booking.reservation r
  76                             JOIN  actor.org_unit_full_path( max_fines.org_unit ) fp ON (r.pickup_lib = fp.id)
  77                       WHERE usr = match_user
  78                             AND xact_finish IS NULL
  79                                 UNION ALL
  80                     SELECT  g.id
  81                       FROM  money.grocery g
  82                             JOIN  actor.org_unit_full_path( max_fines.org_unit ) fp ON (g.billing_location = fp.id)
  83                       WHERE usr = match_user
  84                             AND xact_finish IS NULL
  85                                 UNION ALL
  86                     SELECT  circ.id
  87                       FROM  action.circulation circ
  88                             JOIN  actor.org_unit_full_path( max_fines.org_unit ) fp ON (circ.circ_lib = fp.id)
  89                       WHERE usr = match_user
  90                             AND xact_finish IS NULL ) l USING (id);
  91
  92         IF current_fines >= max_fines.threshold THEN
  93             new_sp_row.usr := match_user;
  94             new_sp_row.org_unit := max_fines.org_unit;
  95             new_sp_row.standing_penalty := 1;
  96             RETURN NEXT new_sp_row;
  97         END IF;
  98     END IF;
  99
 100     -- Start over for max overdue
 101     SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
 102
 103     -- Fail if the user has too many overdue items
 104     LOOP
 105         tmp_grp := user_object.profile;
 106         LOOP
 107
 108             SELECT * INTO max_overdue FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 2 AND org_unit = tmp_org.id;
 109
 110             IF max_overdue.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_overdue.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_overdue.threshold IS NOT NULL THEN
 130
 131         FOR existing_sp_row IN
 132                 SELECT  *
 133                   FROM  actor.usr_standing_penalty
 134                   WHERE usr = match_user
 135                         AND org_unit = max_overdue.org_unit
 136                         AND (stop_date IS NULL or stop_date > NOW())
 137                         AND standing_penalty = 2
 138                 LOOP
 139             RETURN NEXT existing_sp_row;
 140         END LOOP;
 141
 142         SELECT  INTO items_overdue COUNT(*)
 143           FROM  action.circulation circ
 144                 JOIN  actor.org_unit_full_path( max_overdue.org_unit ) fp ON (circ.circ_lib = fp.id)
 145           WHERE circ.usr = match_user
 146             AND circ.checkin_time IS NULL
 147             AND circ.due_date < NOW()
 148             AND (circ.stop_fines = 'MAXFINES' OR circ.stop_fines IS NULL);
 149
 150         IF items_overdue >= max_overdue.threshold::INT THEN
 151             new_sp_row.usr := match_user;
 152             new_sp_row.org_unit := max_overdue.org_unit;
 153             new_sp_row.standing_penalty := 2;
 154             RETURN NEXT new_sp_row;
 155         END IF;
 156     END IF;
 157
 158     -- Start over for max out
 159     SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
 160
 161     -- Fail if the user has too many checked out items
 162     LOOP
 163         tmp_grp := user_object.profile;
 164         LOOP
 165             SELECT * INTO max_items_out FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 3 AND org_unit = tmp_org.id;
 166
 167             IF max_items_out.threshold IS NULL THEN
 168                 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
 169             ELSE
 170                 EXIT;
 171             END IF;
 172
 173             IF tmp_grp IS NULL THEN
 174                 EXIT;
 175             END IF;
 176         END LOOP;
 177
 178         IF max_items_out.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
 179             EXIT;
 180         END IF;
 181
 182         SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
 183
 184     END LOOP;
 185
 186
 187     -- Fail if the user has too many items checked out
 188     IF max_items_out.threshold IS NOT NULL THEN
 189
 190         FOR existing_sp_row IN
 191                 SELECT  *
 192                   FROM  actor.usr_standing_penalty
 193                   WHERE usr = match_user
 194                         AND org_unit = max_items_out.org_unit
 195                         AND (stop_date IS NULL or stop_date > NOW())
 196                         AND standing_penalty = 3
 197                 LOOP
 198             RETURN NEXT existing_sp_row;
 199         END LOOP;
 200
 201         SELECT  INTO items_out COUNT(*)
 202           FROM  action.circulation circ
 203                 JOIN  actor.org_unit_full_path( max_items_out.org_unit ) fp ON (circ.circ_lib = fp.id)
 204           WHERE circ.usr = match_user
 205                 AND circ.checkin_time IS NULL
 206                 AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL);
 207
 208            IF items_out >= max_items_out.threshold::INT THEN
 209             new_sp_row.usr := match_user;
 210             new_sp_row.org_unit := max_items_out.org_unit;
 211             new_sp_row.standing_penalty := 3;
 212             RETURN NEXT new_sp_row;
 213            END IF;
 214     END IF;
 215
 216     -- Start over for collections warning
 217     SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
 218
 219     -- Fail if the user has a collections-level fine balance
 220     LOOP
 221         tmp_grp := user_object.profile;
 222         LOOP
 223             SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 4 AND org_unit = tmp_org.id;
 224
 225             IF max_fines.threshold IS NULL THEN
 226                 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
 227             ELSE
 228                 EXIT;
 229             END IF;
 230
 231             IF tmp_grp IS NULL THEN
 232                 EXIT;
 233             END IF;
 234         END LOOP;
 235
 236         IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
 237             EXIT;
 238         END IF;
 239
 240         SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
 241
 242     END LOOP;
 243
 244     IF max_fines.threshold IS NOT NULL THEN
 245
 246         FOR existing_sp_row IN
 247                 SELECT  *
 248                   FROM  actor.usr_standing_penalty
 249                   WHERE usr = match_user
 250                         AND org_unit = max_fines.org_unit
 251                         AND (stop_date IS NULL or stop_date > NOW())
 252                         AND standing_penalty = 4
 253                 LOOP
 254             RETURN NEXT existing_sp_row;
 255         END LOOP;
 256
 257         SELECT  SUM(f.balance_owed) INTO current_fines
 258           FROM  money.materialized_billable_xact_summary f
 259                 JOIN (
 260                     SELECT  r.id
 261                       FROM  booking.reservation r
 262                             JOIN  actor.org_unit_full_path( max_fines.org_unit ) fp ON (r.pickup_lib = fp.id)
 263                       WHERE usr = match_user
 264                             AND xact_finish IS NULL
 265                                 UNION ALL
 266                     SELECT  g.id
 267                       FROM  money.grocery g
 268                             JOIN  actor.org_unit_full_path( max_fines.org_unit ) fp ON (g.billing_location = fp.id)
 269                       WHERE usr = match_user
 270                             AND xact_finish IS NULL
 271                                 UNION ALL
 272                     SELECT  circ.id
 273                       FROM  action.circulation circ
 274                             JOIN  actor.org_unit_full_path( max_fines.org_unit ) fp ON (circ.circ_lib = fp.id)
 275                       WHERE usr = match_user
 276                             AND xact_finish IS NULL ) l USING (id);
 277
 278         IF current_fines >= max_fines.threshold THEN
 279             new_sp_row.usr := match_user;
 280             new_sp_row.org_unit := max_fines.org_unit;
 281             new_sp_row.standing_penalty := 4;
 282             RETURN NEXT new_sp_row;
 283         END IF;
 284     END IF;
 285
 286     -- Start over for in collections
 287     SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
 288
 289     -- Remove the in-collections penalty if the user has paid down enough
 290     -- This penalty is different, because this code is not responsible for creating
 291     -- new in-collections penalties, only for removing them
 292     LOOP
 293         tmp_grp := user_object.profile;
 294         LOOP
 295             SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 30 AND org_unit = tmp_org.id;
 296
 297             IF max_fines.threshold IS NULL THEN
 298                 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
 299             ELSE
 300                 EXIT;
 301             END IF;
 302
 303             IF tmp_grp IS NULL THEN
 304                 EXIT;
 305             END IF;
 306         END LOOP;
 307
 308         IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
 309             EXIT;
 310         END IF;
 311
 312         SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
 313
 314     END LOOP;
 315
 316     IF max_fines.threshold IS NOT NULL THEN
 317
 318         -- first, see if the user had paid down to the threshold
 319         SELECT  SUM(f.balance_owed) INTO current_fines
 320           FROM  money.materialized_billable_xact_summary f
 321                 JOIN (
 322                     SELECT  r.id
 323                       FROM  booking.reservation r
 324                             JOIN  actor.org_unit_full_path( max_fines.org_unit ) fp ON (r.pickup_lib = fp.id)
 325                       WHERE usr = match_user
 326                             AND xact_finish IS NULL
 327                                 UNION ALL
 328                     SELECT  g.id
 329                       FROM  money.grocery g
 330                             JOIN  actor.org_unit_full_path( max_fines.org_unit ) fp ON (g.billing_location = fp.id)
 331                       WHERE usr = match_user
 332                             AND xact_finish IS NULL
 333                                 UNION ALL
 334                     SELECT  circ.id
 335                       FROM  action.circulation circ
 336                             JOIN  actor.org_unit_full_path( max_fines.org_unit ) fp ON (circ.circ_lib = fp.id)
 337                       WHERE usr = match_user
 338                             AND xact_finish IS NULL ) l USING (id);
 339
 340         IF current_fines IS NULL OR current_fines <= max_fines.threshold THEN
 341             -- patron has paid down enough
 342
 343             SELECT INTO tmp_penalty * FROM config.standing_penalty WHERE id = 30;
 344
 345             IF tmp_penalty.org_depth IS NOT NULL THEN
 346
 347                 -- since this code is not responsible for applying the penalty, it can't
 348                 -- guarantee the current context org will match the org at which the penalty
 349                 --- was applied.  search up the org tree until we hit the configured penalty depth
 350                 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
 351                 SELECT INTO tmp_depth depth FROM actor.org_unit_type WHERE id = tmp_org.ou_type;
 352
 353                 WHILE tmp_depth >= tmp_penalty.org_depth LOOP
 354
 355                     FOR existing_sp_row IN
 356                             SELECT  *
 357                             FROM  actor.usr_standing_penalty
 358                             WHERE usr = match_user
 359                                     AND org_unit = tmp_org.id
 360                                     AND (stop_date IS NULL or stop_date > NOW())
 361                                     AND standing_penalty = 30
 362                             LOOP
 363
 364                         -- Penalty exists, return it for removal
 365                         RETURN NEXT existing_sp_row;
 366                     END LOOP;
 367
 368                     IF tmp_org.parent_ou IS NULL THEN
 369                         EXIT;
 370                     END IF;
 371
 372                     SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
 373                     SELECT INTO tmp_depth depth FROM actor.org_unit_type WHERE id = tmp_org.ou_type;
 374                 END LOOP;
 375
 376             ELSE
 377
 378                 -- no penalty depth is defined, look for exact matches
 379
 380                 FOR existing_sp_row IN
 381                         SELECT  *
 382                         FROM  actor.usr_standing_penalty
 383                         WHERE usr = match_user
 384                                 AND org_unit = max_fines.org_unit
 385                                 AND (stop_date IS NULL or stop_date > NOW())
 386                                 AND standing_penalty = 30
 387                         LOOP
 388                     -- Penalty exists, return it for removal
 389                     RETURN NEXT existing_sp_row;
 390                 END LOOP;
 391             END IF;
 392
 393         END IF;
 394
 395     END IF;
 396
 397     RETURN;
 398 END;
 399 $func$ LANGUAGE plpgsql;
 400
 401
 402 COMMIT;