Open-ILS/src/sql/Pg/096.schema.emergency_closing.sql

   1 /*
   2  * Copyright (C) 2018  Equinox Open Library Initiative Inc.
   3  * Mike Rylander <mrylander@gmail.com>
   4  *
   5  * This program is free software; you can redistribute it and/or
   6  * modify it under the terms of the GNU General Public License
   7  * as published by the Free Software Foundation; either version 2
   8  * of the License, or (at your option) any later version.
   9  *
  10  * This program is distributed in the hope that it will be useful,
  11  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  12  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  13  * GNU General Public License for more details.
  14  *
  15  */
  16
  17 BEGIN;
  18
  19 CREATE TABLE action.emergency_closing (
  20     id                  SERIAL      PRIMARY KEY,
  21     creator             INT         NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  22     create_time         TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  23     process_start_time  TIMESTAMPTZ,
  24     process_end_time    TIMESTAMPTZ,
  25     last_update_time    TIMESTAMPTZ
  26 );
  27
  28 ALTER TABLE actor.org_unit_closed
  29     ADD COLUMN emergency_closing INT
  30         REFERENCES action.emergency_closing (id) ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED;
  31
  32 CREATE TABLE action.emergency_closing_circulation (
  33     id                  BIGSERIAL   PRIMARY KEY,
  34     emergency_closing   INT         NOT NULL REFERENCES action.emergency_closing (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  35     circulation         INT         NOT NULL REFERENCES action.circulation (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  36     original_due_date   TIMESTAMPTZ,
  37     process_time        TIMESTAMPTZ
  38 );
  39 CREATE INDEX emergency_closing_circulation_emergency_closing_idx ON action.emergency_closing_circulation (emergency_closing);
  40 CREATE INDEX emergency_closing_circulation_circulation_idx ON action.emergency_closing_circulation (circulation);
  41
  42 CREATE TABLE action.emergency_closing_reservation (
  43     id                  BIGSERIAL   PRIMARY KEY,
  44     emergency_closing   INT         NOT NULL REFERENCES action.emergency_closing (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  45     reservation         INT         NOT NULL REFERENCES booking.reservation (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  46     original_end_time   TIMESTAMPTZ,
  47     process_time        TIMESTAMPTZ
  48 );
  49 CREATE INDEX emergency_closing_reservation_emergency_closing_idx ON action.emergency_closing_reservation (emergency_closing);
  50 CREATE INDEX emergency_closing_reservation_reservation_idx ON action.emergency_closing_reservation (reservation);
  51
  52 CREATE TABLE action.emergency_closing_hold (
  53     id                  BIGSERIAL   PRIMARY KEY,
  54     emergency_closing   INT         NOT NULL REFERENCES action.emergency_closing (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  55     hold                INT         NOT NULL REFERENCES action.hold_request (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  56     original_shelf_expire_time   TIMESTAMPTZ,
  57     process_time        TIMESTAMPTZ
  58 );
  59 CREATE INDEX emergency_closing_hold_emergency_closing_idx ON action.emergency_closing_hold (emergency_closing);
  60 CREATE INDEX emergency_closing_hold_hold_idx ON action.emergency_closing_hold (hold);
  61
  62 CREATE OR REPLACE VIEW action.emergency_closing_status AS
  63     SELECT  e.*,
  64             COALESCE(c.count, 0) AS circulations,
  65             COALESCE(c.completed, 0) AS circulations_complete,
  66             COALESCE(b.count, 0) AS reservations,
  67             COALESCE(b.completed, 0) AS reservations_complete,
  68             COALESCE(h.count, 0) AS holds,
  69             COALESCE(h.completed, 0) AS holds_complete
  70       FROM  action.emergency_closing e
  71             LEFT JOIN (SELECT emergency_closing, count(*) count, SUM((process_time IS NOT NULL)::INT) completed FROM action.emergency_closing_circulation GROUP BY 1) c ON (c.emergency_closing = e.id)
  72             LEFT JOIN (SELECT emergency_closing, count(*) count, SUM((process_time IS NOT NULL)::INT) completed FROM action.emergency_closing_reservation GROUP BY 1) b ON (b.emergency_closing = e.id)
  73             LEFT JOIN (SELECT emergency_closing, count(*) count, SUM((process_time IS NOT NULL)::INT) completed FROM action.emergency_closing_hold GROUP BY 1) h ON (h.emergency_closing = e.id)
  74 ;
  75
  76 CREATE OR REPLACE FUNCTION evergreen.find_next_open_time ( circ_lib INT, initial TIMESTAMPTZ, hourly BOOL DEFAULT FALSE, initial_time TIME DEFAULT NULL, dow_count INT DEFAULT 0 )
  77     RETURNS TIMESTAMPTZ AS $$
  78 DECLARE
  79     day_number      INT;
  80     plus_days       INT;
  81     final_time      TEXT;
  82     time_adjusted   BOOL;
  83     hoo_open        TIME WITHOUT TIME ZONE;
  84     hoo_close       TIME WITHOUT TIME ZONE;
  85     adjacent        actor.org_unit_closed%ROWTYPE;
  86     breakout        INT := 0;
  87 BEGIN
  88
  89     IF dow_count > 6 THEN
  90         RETURN initial;
  91     END IF;
  92
  93     IF initial_time IS NULL THEN
  94         initial_time := initial::TIME;
  95     END IF;
  96
  97     final_time := (initial + '1 second'::INTERVAL)::TEXT;
  98     LOOP
  99         breakout := breakout + 1;
 100
 101         time_adjusted := FALSE;
 102
 103         IF dow_count > 0 THEN -- we're recursing, so check for HOO closing
 104             day_number := EXTRACT(ISODOW FROM final_time::TIMESTAMPTZ) - 1;
 105             plus_days := 0;
 106             FOR i IN 1..7 LOOP
 107                 EXECUTE 'SELECT dow_' || day_number || '_open, dow_' || day_number || '_close FROM actor.hours_of_operation WHERE id = $1'
 108                     INTO hoo_open, hoo_close
 109                     USING circ_lib;
 110
 111                 -- RAISE NOTICE 'initial time: %; dow: %; close: %',initial_time,day_number,hoo_close;
 112
 113                 IF hoo_close = '00:00:00' THEN -- bah ... I guess we'll check the next day
 114                     day_number := (day_number + 1) % 7;
 115                     plus_days := plus_days + 1;
 116                     time_adjusted := TRUE;
 117                     CONTINUE;
 118                 END IF;
 119
 120                 IF hoo_close IS NULL THEN -- no hours of operation ... assume no closing?
 121                     hoo_close := '23:59:59';
 122                 END IF;
 123
 124                 EXIT;
 125             END LOOP;
 126
 127             final_time := DATE(final_time::TIMESTAMPTZ + (plus_days || ' days')::INTERVAL)::TEXT;
 128             IF hoo_close <> '00:00:00' AND hourly THEN -- Not a day-granular circ
 129                 final_time := final_time||' '|| hoo_close;
 130             ELSE
 131                 final_time := final_time||' 23:59:59';
 132             END IF;
 133         END IF;
 134
 135         -- Loop through other closings
 136         LOOP
 137             SELECT * INTO adjacent FROM actor.org_unit_closed WHERE org_unit = circ_lib AND final_time::TIMESTAMPTZ between close_start AND close_end;
 138             EXIT WHEN adjacent.id IS NULL;
 139             time_adjusted := TRUE;
 140             -- RAISE NOTICE 'recursing for closings with final_time: %',final_time;
 141             final_time := evergreen.find_next_open_time(circ_lib, adjacent.close_end::TIMESTAMPTZ, hourly, initial_time, dow_count + 1)::TEXT;
 142         END LOOP;
 143
 144         EXIT WHEN breakout > 100;
 145         EXIT WHEN NOT time_adjusted;
 146
 147     END LOOP;
 148
 149     RETURN final_time;
 150 END;
 151 $$ LANGUAGE PLPGSQL;
 152
 153 CREATE TYPE action.emergency_closing_stage_1_count AS (circulations INT, reservations INT, holds INT);
 154 CREATE OR REPLACE FUNCTION action.emergency_closing_stage_1 ( e_closing INT )
 155     RETURNS SETOF action.emergency_closing_stage_1_count AS $$
 156 DECLARE
 157     tmp     INT;
 158     touched action.emergency_closing_stage_1_count%ROWTYPE;
 159 BEGIN
 160     -- First, gather circs
 161     INSERT INTO action.emergency_closing_circulation (emergency_closing, circulation)
 162         SELECT  e_closing,
 163                 circ.id
 164           FROM  actor.org_unit_closed closing
 165                 JOIN action.emergency_closing ec ON (closing.emergency_closing = ec.id AND ec.id = e_closing)
 166                 JOIN action.circulation circ ON (
 167                     circ.circ_lib = closing.org_unit
 168                     AND circ.due_date BETWEEN closing.close_start AND (closing.close_end + '1s'::INTERVAL)
 169                     AND circ.xact_finish IS NULL
 170                 )
 171           WHERE NOT EXISTS (SELECT 1 FROM action.emergency_closing_circulation t WHERE t.emergency_closing = e_closing AND t.circulation = circ.id);
 172
 173     GET DIAGNOSTICS tmp = ROW_COUNT;
 174     touched.circulations := tmp;
 175
 176     INSERT INTO action.emergency_closing_reservation (emergency_closing, reservation)
 177         SELECT  e_closing,
 178                 res.id
 179           FROM  actor.org_unit_closed closing
 180                 JOIN action.emergency_closing ec ON (closing.emergency_closing = ec.id AND ec.id = e_closing)
 181                 JOIN booking.reservation res ON (
 182                     res.pickup_lib = closing.org_unit
 183                     AND res.end_time BETWEEN closing.close_start AND (closing.close_end + '1s'::INTERVAL)
 184                 )
 185           WHERE NOT EXISTS (SELECT 1 FROM action.emergency_closing_reservation t WHERE t.emergency_closing = e_closing AND t.reservation = res.id);
 186
 187     GET DIAGNOSTICS tmp = ROW_COUNT;
 188     touched.reservations := tmp;
 189
 190     INSERT INTO action.emergency_closing_hold (emergency_closing, hold)
 191         SELECT  e_closing,
 192                 hold.id
 193           FROM  actor.org_unit_closed closing
 194                 JOIN action.emergency_closing ec ON (closing.emergency_closing = ec.id AND ec.id = e_closing)
 195                 JOIN action.hold_request hold ON (
 196                     pickup_lib = closing.org_unit
 197                     AND hold.shelf_expire_time BETWEEN closing.close_start AND (closing.close_end + '1s'::INTERVAL)
 198                     AND hold.fulfillment_time IS NULL
 199                     AND hold.cancel_time IS NULL
 200                 )
 201           WHERE NOT EXISTS (SELECT 1 FROM action.emergency_closing_hold t WHERE t.emergency_closing = e_closing AND t.hold = hold.id);
 202
 203     GET DIAGNOSTICS tmp = ROW_COUNT;
 204     touched.holds := tmp;
 205
 206     UPDATE  action.emergency_closing
 207       SET   process_start_time = NOW(),
 208             last_update_time = NOW()
 209       WHERE id = e_closing;
 210
 211     RETURN NEXT touched;
 212 END;
 213 $$ LANGUAGE PLPGSQL;
 214
 215 CREATE OR REPLACE FUNCTION action.emergency_closing_stage_2_hold ( hold_closing_entry INT )
 216     RETURNS BOOL AS $$
 217 DECLARE
 218     hold        action.hold_request%ROWTYPE;
 219     e_closing   action.emergency_closing%ROWTYPE;
 220     e_c_hold    action.emergency_closing_hold%ROWTYPE;
 221     closing     actor.org_unit_closed%ROWTYPE;
 222     day_number  INT;
 223     hoo_close   TIME WITHOUT TIME ZONE;
 224     plus_days   INT;
 225 BEGIN
 226     -- Gather objects involved
 227     SELECT  * INTO e_c_hold
 228       FROM  action.emergency_closing_hold
 229       WHERE id = hold_closing_entry;
 230
 231     IF e_c_hold.process_time IS NOT NULL THEN
 232         -- Already processed ... moving on
 233         RETURN FALSE;
 234     END IF;
 235
 236     SELECT  * INTO e_closing
 237       FROM  action.emergency_closing
 238       WHERE id = e_c_hold.emergency_closing;
 239
 240     IF e_closing.process_start_time IS NULL THEN
 241         -- Huh... that's odd. And wrong.
 242         RETURN FALSE;
 243     END IF;
 244
 245     SELECT  * INTO closing
 246       FROM  actor.org_unit_closed
 247       WHERE emergency_closing = e_closing.id;
 248
 249     SELECT  * INTO hold
 250       FROM  action.hold_request h
 251       WHERE id = e_c_hold.hold;
 252
 253     -- Record the processing
 254     UPDATE  action.emergency_closing_hold
 255       SET   original_shelf_expire_time = hold.shelf_expire_time,
 256             process_time = NOW()
 257       WHERE id = hold_closing_entry;
 258
 259     UPDATE  action.emergency_closing
 260       SET   last_update_time = NOW()
 261       WHERE id = e_closing.id;
 262
 263     UPDATE  action.hold_request
 264       SET   shelf_expire_time = evergreen.find_next_open_time(closing.org_unit, hold.shelf_expire_time, TRUE)
 265       WHERE id = hold.id;
 266
 267     RETURN TRUE;
 268 END;
 269 $$ LANGUAGE PLPGSQL;
 270
 271 CREATE OR REPLACE FUNCTION action.emergency_closing_stage_2_circ ( circ_closing_entry INT )
 272     RETURNS BOOL AS $$
 273 DECLARE
 274     circ            action.circulation%ROWTYPE;
 275     e_closing       action.emergency_closing%ROWTYPE;
 276     e_c_circ        action.emergency_closing_circulation%ROWTYPE;
 277     closing         actor.org_unit_closed%ROWTYPE;
 278     adjacent        actor.org_unit_closed%ROWTYPE;
 279     bill            money.billing%ROWTYPE;
 280     last_bill       money.billing%ROWTYPE;
 281     day_number      INT;
 282     hoo_close       TIME WITHOUT TIME ZONE;
 283     plus_days       INT;
 284     avoid_negative  BOOL;
 285     extend_grace    BOOL;
 286     new_due_date    TEXT;
 287 BEGIN
 288     -- Gather objects involved
 289     SELECT  * INTO e_c_circ
 290       FROM  action.emergency_closing_circulation
 291       WHERE id = circ_closing_entry;
 292
 293     IF e_c_circ.process_time IS NOT NULL THEN
 294         -- Already processed ... moving on
 295         RETURN FALSE;
 296     END IF;
 297
 298     SELECT  * INTO e_closing
 299       FROM  action.emergency_closing
 300       WHERE id = e_c_circ.emergency_closing;
 301
 302     IF e_closing.process_start_time IS NULL THEN
 303         -- Huh... that's odd. And wrong.
 304         RETURN FALSE;
 305     END IF;
 306
 307     SELECT  * INTO closing
 308       FROM  actor.org_unit_closed
 309       WHERE emergency_closing = e_closing.id;
 310
 311     SELECT  * INTO circ
 312       FROM  action.circulation
 313       WHERE id = e_c_circ.circulation;
 314
 315     -- Record the processing
 316     UPDATE  action.emergency_closing_circulation
 317       SET   original_due_date = circ.due_date,
 318             process_time = NOW()
 319       WHERE id = circ_closing_entry;
 320
 321     UPDATE  action.emergency_closing
 322       SET   last_update_time = NOW()
 323       WHERE id = e_closing.id;
 324
 325     SELECT value::BOOL INTO avoid_negative FROM actor.org_unit_ancestor_setting('bill.prohibit_negative_balance_on_overdues', circ.circ_lib);
 326     SELECT value::BOOL INTO extend_grace FROM actor.org_unit_ancestor_setting('circ.grace.extend', circ.circ_lib);
 327
 328     new_due_date := evergreen.find_next_open_time( closing.org_unit, circ.due_date, EXTRACT(EPOCH FROM circ.duration)::INT % 86400 > 0 )::TEXT;
 329     UPDATE action.circulation SET due_date = new_due_date::TIMESTAMPTZ WHERE id = circ.id;
 330
 331     -- Now, see if we need to get rid of some fines
 332     SELECT  * INTO last_bill
 333       FROM  money.billing b
 334       WHERE b.xact = circ.id
 335             AND NOT b.voided
 336             AND b.btype = 1
 337       ORDER BY billing_ts DESC
 338       LIMIT 1;
 339
 340     FOR bill IN
 341         SELECT  *
 342           FROM  money.billing b
 343           WHERE b.xact = circ.id
 344                 AND b.btype = 1
 345                 AND NOT b.voided
 346                 AND (
 347                     b.billing_ts BETWEEN closing.close_start AND new_due_date::TIMESTAMPTZ
 348                     OR (extend_grace AND last_bill.billing_ts <= new_due_date::TIMESTAMPTZ + circ.grace_period)
 349                 )
 350                 AND NOT EXISTS (SELECT 1 FROM money.account_adjustment a WHERE a.billing = b.id)
 351           ORDER BY billing_ts
 352     LOOP
 353         IF avoid_negative THEN
 354             PERFORM FROM money.materialized_billable_xact_summary WHERE id = circ.id AND balanced_owd < bill.amount;
 355             EXIT WHEN FOUND; -- We can't go negative, and voiding this bill would do that...
 356         END IF;
 357
 358         UPDATE  money.billing
 359           SET   voided = TRUE,
 360                 void_time = NOW(),
 361                 note = COALESCE(note,'') || ' :: Voided by emergency closing handler'
 362           WHERE id = bill.id;
 363     END LOOP;
 364
 365     RETURN TRUE;
 366 END;
 367 $$ LANGUAGE PLPGSQL;
 368
 369 CREATE OR REPLACE FUNCTION action.emergency_closing_stage_2_reservation ( res_closing_entry INT )
 370     RETURNS BOOL AS $$
 371 DECLARE
 372     res             booking.reservation%ROWTYPE;
 373     e_closing       action.emergency_closing%ROWTYPE;
 374     e_c_res         action.emergency_closing_reservation%ROWTYPE;
 375     closing         actor.org_unit_closed%ROWTYPE;
 376     adjacent        actor.org_unit_closed%ROWTYPE;
 377     bill            money.billing%ROWTYPE;
 378     day_number      INT;
 379     hoo_close       TIME WITHOUT TIME ZONE;
 380     plus_days       INT;
 381     avoid_negative  BOOL;
 382     new_due_date    TEXT;
 383 BEGIN
 384     -- Gather objects involved
 385     SELECT  * INTO e_c_res
 386       FROM  action.emergency_closing_reservation
 387       WHERE id = res_closing_entry;
 388
 389     IF e_c_res.process_time IS NOT NULL THEN
 390         -- Already processed ... moving on
 391         RETURN FALSE;
 392     END IF;
 393
 394     SELECT  * INTO e_closing
 395       FROM  action.emergency_closing
 396       WHERE id = e_c_res.emergency_closing;
 397
 398     IF e_closing.process_start_time IS NULL THEN
 399         -- Huh... that's odd. And wrong.
 400         RETURN FALSE;
 401     END IF;
 402
 403     SELECT  * INTO closing
 404       FROM  actor.org_unit_closed
 405       WHERE emergency_closing = e_closing.id;
 406
 407     SELECT  * INTO res
 408       FROM  booking.reservation
 409       WHERE id = e_c_res.reservation;
 410
 411     IF res.pickup_lib IS NULL THEN -- Need to be far enough along to have a pickup lib
 412         RETURN FALSE;
 413     END IF;
 414
 415     -- Record the processing
 416     UPDATE  action.emergency_closing_reservation
 417       SET   original_end_time = res.end_time,
 418             process_time = NOW()
 419       WHERE id = res_closing_entry;
 420
 421     UPDATE  action.emergency_closing
 422       SET   last_update_time = NOW()
 423       WHERE id = e_closing.id;
 424
 425     SELECT value::BOOL INTO avoid_negative FROM actor.org_unit_ancestor_setting('bill.prohibit_negative_balance_on_overdues', res.pickup_lib);
 426
 427     new_due_date := evergreen.find_next_open_time( closing.org_unit, res.end_time, EXTRACT(EPOCH FROM res.booking_interval)::INT % 86400 > 0 )::TEXT;
 428     UPDATE booking.reservation SET end_time = new_due_date::TIMESTAMPTZ WHERE id = res.id;
 429
 430     -- Now, see if we need to get rid of some fines
 431     FOR bill IN
 432         SELECT  *
 433           FROM  money.billing b
 434           WHERE b.xact = res.id
 435                 AND b.btype = 1
 436                 AND NOT b.voided
 437                 AND b.billing_ts BETWEEN closing.close_start AND new_due_date::TIMESTAMPTZ
 438                 AND NOT EXISTS (SELECT 1 FROM money.account_adjustment a WHERE a.billing = b.id)
 439     LOOP
 440         IF avoid_negative THEN
 441             PERFORM FROM money.materialized_billable_xact_summary WHERE id = res.id AND balanced_owd < bill.amount;
 442             EXIT WHEN FOUND; -- We can't go negative, and voiding this bill would do that...
 443         END IF;
 444
 445         UPDATE  money.billing
 446           SET   voided = TRUE,
 447                 void_time = NOW(),
 448                 note = COALESCE(note,'') || ' :: Voided by emergency closing handler'
 449           WHERE id = bill.id;
 450     END LOOP;
 451
 452     RETURN TRUE;
 453 END;
 454 $$ LANGUAGE PLPGSQL;
 455
 456 COMMIT;
 457