Open-ILS/src/sql/Pg/upgrade/0105.schema.booking-integration.sql

   1 BEGIN;
   2
   3 INSERT INTO config.upgrade_log (version) VALUES ('0105'); -- miker
   4
   5 CREATE TRIGGER mat_summary_create_tgr AFTER INSERT ON booking.reservation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_create ('reservation');
   6 CREATE TRIGGER mat_summary_change_tgr AFTER UPDATE ON booking.reservation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_update ();
   7 CREATE TRIGGER mat_summary_remove_tgr AFTER DELETE ON booking.reservation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_delete ();
   8
   9 CREATE OR REPLACE VIEW money.open_billable_xact_summary AS
  10     SELECT  xact.id AS id,
  11         xact.usr AS usr,
  12         COALESCE(circ.circ_lib,groc.billing_location,res.pickup_lib) AS billing_location,
  13         xact.xact_start AS xact_start,
  14         xact.xact_finish AS xact_finish,
  15         SUM(credit.amount) AS total_paid,
  16         MAX(credit.payment_ts) AS last_payment_ts,
  17         LAST(credit.note) AS last_payment_note,
  18         LAST(credit.payment_type) AS last_payment_type,
  19         SUM(debit.amount) AS total_owed,
  20         MAX(debit.billing_ts) AS last_billing_ts,
  21         LAST(debit.note) AS last_billing_note,
  22         LAST(debit.billing_type) AS last_billing_type,
  23         COALESCE(SUM(debit.amount),0) - COALESCE(SUM(credit.amount),0) AS balance_owed,
  24         p.relname AS xact_type
  25       FROM  money.billable_xact xact
  26         JOIN pg_class p ON (xact.tableoid = p.oid)
  27         LEFT JOIN "action".circulation circ ON (circ.id = xact.id)
  28         LEFT JOIN money.grocery groc ON (groc.id = xact.id)
  29         LEFT JOIN booking.reservation res ON (groc.id = xact.id)
  30         LEFT JOIN (
  31             SELECT  billing.xact,
  32                 billing.voided,
  33                 sum(billing.amount) AS amount,
  34                 max(billing.billing_ts) AS billing_ts,
  35                 last(billing.note) AS note,
  36                 last(billing.billing_type) AS billing_type
  37               FROM  money.billing
  38               WHERE billing.voided IS FALSE
  39               GROUP BY billing.xact, billing.voided
  40         ) debit ON (xact.id = debit.xact AND debit.voided IS FALSE)
  41         LEFT JOIN (
  42             SELECT  payment_view.xact,
  43                 payment_view.voided,
  44                 sum(payment_view.amount) AS amount,
  45                 max(payment_view.payment_ts) AS payment_ts,
  46                 last(payment_view.note) AS note,
  47                 last(payment_view.payment_type) AS payment_type
  48               FROM  money.payment_view
  49               WHERE payment_view.voided IS FALSE
  50               GROUP BY payment_view.xact, payment_view.voided
  51         ) credit ON (xact.id = credit.xact AND credit.voided IS FALSE)
  52       WHERE xact.xact_finish IS NULL
  53       GROUP BY 1,2,3,4,5,15
  54       ORDER BY MAX(debit.billing_ts), MAX(credit.payment_ts);
  55
  56 CREATE OR REPLACE FUNCTION actor.calculate_system_penalties( match_user INT, context_org INT ) RETURNS SETOF actor.usr_standing_penalty AS $func$
  57 DECLARE
  58     user_object         actor.usr%ROWTYPE;
  59     new_sp_row          actor.usr_standing_penalty%ROWTYPE;
  60     existing_sp_row     actor.usr_standing_penalty%ROWTYPE;
  61     collections_fines   permission.grp_penalty_threshold%ROWTYPE;
  62     max_fines           permission.grp_penalty_threshold%ROWTYPE;
  63     max_overdue         permission.grp_penalty_threshold%ROWTYPE;
  64     max_items_out       permission.grp_penalty_threshold%ROWTYPE;
  65     tmp_grp             INT;
  66     items_overdue       INT;
  67     items_out           INT;
  68     context_org_list    INT[];
  69     current_fines        NUMERIC(8,2) := 0.0;
  70     tmp_fines            NUMERIC(8,2);
  71     tmp_groc            RECORD;
  72     tmp_circ            RECORD;
  73     tmp_org             actor.org_unit%ROWTYPE;
  74 BEGIN
  75     SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
  76
  77     -- Max fines
  78     SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
  79
  80     -- Fail if the user has a high fine balance
  81     LOOP
  82         tmp_grp := user_object.profile;
  83         LOOP
  84             SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 1 AND org_unit = tmp_org.id;
  85
  86             IF max_fines.threshold IS NULL THEN
  87                 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
  88             ELSE
  89                 EXIT;
  90             END IF;
  91
  92             IF tmp_grp IS NULL THEN
  93                 EXIT;
  94             END IF;
  95         END LOOP;
  96
  97         IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
  98             EXIT;
  99         END IF;
 100
 101         SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
 102
 103     END LOOP;
 104
 105     IF max_fines.threshold IS NOT NULL THEN
 106
 107         FOR existing_sp_row IN
 108                 SELECT  *
 109                   FROM  actor.usr_standing_penalty
 110                   WHERE usr = match_user
 111                         AND org_unit = max_fines.org_unit
 112                         AND (stop_date IS NULL or stop_date > NOW())
 113                         AND standing_penalty = 1
 114                 LOOP
 115             RETURN NEXT existing_sp_row;
 116         END LOOP;
 117
 118         SELECT  SUM(f.balance_owed) INTO current_fines
 119           FROM  money.materialized_billable_xact_summary f
 120                 JOIN (
 121                     SELECT  r.id
 122                       FROM  booking.reservation r
 123                             JOIN  actor.org_unit_full_path( max_fines.org_unit ) fp ON (r.pickup_lib = fp.id)
 124                       WHERE usr = match_user
 125                             AND xact_finish IS NULL
 126                                 UNION ALL
 127                     SELECT  g.id
 128                       FROM  money.grocery g
 129                             JOIN  actor.org_unit_full_path( max_fines.org_unit ) fp ON (g.billing_location = fp.id)
 130                       WHERE usr = match_user
 131                             AND xact_finish IS NULL
 132                                 UNION ALL
 133                     SELECT  circ.id
 134                       FROM  action.circulation circ
 135                             JOIN  actor.org_unit_full_path( max_fines.org_unit ) fp ON (circ.circ_lib = fp.id)
 136                       WHERE usr = match_user
 137                             AND xact_finish IS NULL ) l USING (id);
 138
 139         IF current_fines >= max_fines.threshold THEN
 140             new_sp_row.usr := match_user;
 141             new_sp_row.org_unit := max_fines.org_unit;
 142             new_sp_row.standing_penalty := 1;
 143             RETURN NEXT new_sp_row;
 144         END IF;
 145     END IF;
 146
 147     -- Start over for max overdue
 148     SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
 149
 150     -- Fail if the user has too many overdue items
 151     LOOP
 152         tmp_grp := user_object.profile;
 153         LOOP
 154
 155             SELECT * INTO max_overdue FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 2 AND org_unit = tmp_org.id;
 156
 157             IF max_overdue.threshold IS NULL THEN
 158                 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
 159             ELSE
 160                 EXIT;
 161             END IF;
 162
 163             IF tmp_grp IS NULL THEN
 164                 EXIT;
 165             END IF;
 166         END LOOP;
 167
 168         IF max_overdue.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
 169             EXIT;
 170         END IF;
 171
 172         SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
 173
 174     END LOOP;
 175
 176     IF max_overdue.threshold IS NOT NULL THEN
 177
 178         FOR existing_sp_row IN
 179                 SELECT  *
 180                   FROM  actor.usr_standing_penalty
 181                   WHERE usr = match_user
 182                         AND org_unit = max_overdue.org_unit
 183                         AND (stop_date IS NULL or stop_date > NOW())
 184                         AND standing_penalty = 2
 185                 LOOP
 186             RETURN NEXT existing_sp_row;
 187         END LOOP;
 188
 189         SELECT  INTO items_overdue COUNT(*)
 190           FROM  action.circulation circ
 191                 JOIN  actor.org_unit_full_path( max_overdue.org_unit ) fp ON (circ.circ_lib = fp.id)
 192           WHERE circ.usr = match_user
 193             AND circ.checkin_time IS NULL
 194             AND circ.due_date < NOW()
 195             AND (circ.stop_fines = 'MAXFINES' OR circ.stop_fines IS NULL);
 196
 197         IF items_overdue >= max_overdue.threshold::INT THEN
 198             new_sp_row.usr := match_user;
 199             new_sp_row.org_unit := max_overdue.org_unit;
 200             new_sp_row.standing_penalty := 2;
 201             RETURN NEXT new_sp_row;
 202         END IF;
 203     END IF;
 204
 205     -- Start over for max out
 206     SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
 207
 208     -- Fail if the user has too many checked out items
 209     LOOP
 210         tmp_grp := user_object.profile;
 211         LOOP
 212             SELECT * INTO max_items_out FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 3 AND org_unit = tmp_org.id;
 213
 214             IF max_items_out.threshold IS NULL THEN
 215                 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
 216             ELSE
 217                 EXIT;
 218             END IF;
 219
 220             IF tmp_grp IS NULL THEN
 221                 EXIT;
 222             END IF;
 223         END LOOP;
 224
 225         IF max_items_out.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
 226             EXIT;
 227         END IF;
 228
 229         SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
 230
 231     END LOOP;
 232
 233
 234     -- Fail if the user has too many items checked out
 235     IF max_items_out.threshold IS NOT NULL THEN
 236
 237         FOR existing_sp_row IN
 238                 SELECT  *
 239                   FROM  actor.usr_standing_penalty
 240                   WHERE usr = match_user
 241                         AND org_unit = max_items_out.org_unit
 242                         AND (stop_date IS NULL or stop_date > NOW())
 243                         AND standing_penalty = 3
 244                 LOOP
 245             RETURN NEXT existing_sp_row;
 246         END LOOP;
 247
 248         SELECT  INTO items_out COUNT(*)
 249           FROM  action.circulation circ
 250                 JOIN  actor.org_unit_full_path( max_items_out.org_unit ) fp ON (circ.circ_lib = fp.id)
 251           WHERE circ.usr = match_user
 252                 AND circ.checkin_time IS NULL
 253                 AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL);
 254
 255            IF items_out >= max_items_out.threshold::INT THEN
 256             new_sp_row.usr := match_user;
 257             new_sp_row.org_unit := max_items_out.org_unit;
 258             new_sp_row.standing_penalty := 3;
 259             RETURN NEXT new_sp_row;
 260            END IF;
 261     END IF;
 262
 263     -- Start over for collections warning
 264     SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
 265
 266     -- Fail if the user has a collections-level fine balance
 267     LOOP
 268         tmp_grp := user_object.profile;
 269         LOOP
 270             SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 4 AND org_unit = tmp_org.id;
 271
 272             IF max_fines.threshold IS NULL THEN
 273                 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
 274             ELSE
 275                 EXIT;
 276             END IF;
 277
 278             IF tmp_grp IS NULL THEN
 279                 EXIT;
 280             END IF;
 281         END LOOP;
 282
 283         IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
 284             EXIT;
 285         END IF;
 286
 287         SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
 288
 289     END LOOP;
 290
 291     IF max_fines.threshold IS NOT NULL THEN
 292
 293         FOR existing_sp_row IN
 294                 SELECT  *
 295                   FROM  actor.usr_standing_penalty
 296                   WHERE usr = match_user
 297                         AND org_unit = max_fines.org_unit
 298                         AND (stop_date IS NULL or stop_date > NOW())
 299                         AND standing_penalty = 4
 300                 LOOP
 301             RETURN NEXT existing_sp_row;
 302         END LOOP;
 303
 304         SELECT  SUM(f.balance_owed) INTO current_fines
 305           FROM  money.materialized_billable_xact_summary f
 306                 JOIN (
 307                     SELECT  r.id
 308                       FROM  booking.reservation r
 309                             JOIN  actor.org_unit_full_path( max_fines.org_unit ) fp ON (r.pickup_lib = fp.id)
 310                       WHERE usr = match_user
 311                             AND xact_finish IS NULL
 312                                 UNION ALL
 313                     SELECT  g.id
 314                       FROM  money.grocery g
 315                             JOIN  actor.org_unit_full_path( max_fines.org_unit ) fp ON (g.billing_location = fp.id)
 316                       WHERE usr = match_user
 317                             AND xact_finish IS NULL
 318                                 UNION ALL
 319                     SELECT  circ.id
 320                       FROM  action.circulation circ
 321                             JOIN  actor.org_unit_full_path( max_fines.org_unit ) fp ON (circ.circ_lib = fp.id)
 322                       WHERE usr = match_user
 323                             AND xact_finish IS NULL ) l USING (id);
 324
 325         IF current_fines >= max_fines.threshold THEN
 326             new_sp_row.usr := match_user;
 327             new_sp_row.org_unit := max_fines.org_unit;
 328             new_sp_row.standing_penalty := 4;
 329             RETURN NEXT new_sp_row;
 330         END IF;
 331     END IF;
 332
 333
 334     RETURN;
 335 END;
 336 $func$ LANGUAGE plpgsql;
 337
 338 COMMIT;
 339