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, has_hoo BOOL DEFAULT TRUE )
  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 initial_time IS NULL THEN
  90         initial_time := initial::TIME;
  91     END IF;
  92
  93     final_time := (initial + '1 second'::INTERVAL)::TEXT;
  94     LOOP
  95         breakout := breakout + 1;
  96
  97         time_adjusted := FALSE;
  98
  99         IF has_hoo THEN -- Don't check hours if they have no hoo. I think the behavior in that case is that we act like they're always open? Better than making things due in 2 years.
 100                         -- Don't expect anyone to call this with it set to false; it's just for our own recursive use.
 101             day_number := EXTRACT(ISODOW FROM final_time::TIMESTAMPTZ) - 1; --Get which day of the week  it is from which it started on.
 102             plus_days := 0;
 103             has_hoo := FALSE; -- set has_hoo to false to check if any days are open (for the first recursion where it's always true)
 104             FOR i IN 1..7 LOOP
 105                 EXECUTE 'SELECT dow_' || day_number || '_open, dow_' || day_number || '_close FROM actor.hours_of_operation WHERE id = $1'
 106                     INTO hoo_open, hoo_close
 107                     USING circ_lib;
 108
 109                 -- RAISE NOTICE 'initial time: %; dow: %; close: %',initial_time,day_number,hoo_close;
 110
 111                 IF hoo_close = '00:00:00' THEN -- bah ... I guess we'll check the next day
 112                     day_number := (day_number + 1) % 7;
 113                     plus_days := plus_days + 1;
 114                     time_adjusted := TRUE;
 115                     CONTINUE;
 116                 ELSE
 117                     has_hoo := TRUE; --We do have hours open sometimes, yay!
 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             IF NOT has_hoo THEN -- If always closed then forget the extra days - just determine based on closures.
 128                 plus_days := 0;
 129             END IF;
 130
 131             final_time := DATE(final_time::TIMESTAMPTZ + (plus_days || ' days')::INTERVAL)::TEXT;
 132             IF hoo_close <> '00:00:00' AND hourly THEN -- Not a day-granular circ
 133                 final_time := final_time||' '|| hoo_close;
 134             ELSE
 135                 final_time := final_time||' 23:59:59';
 136             END IF;
 137         END IF;
 138
 139         --RAISE NOTICE 'final_time: %',final_time;
 140
 141         -- Loop through other closings
 142         LOOP
 143             SELECT * INTO adjacent FROM actor.org_unit_closed WHERE org_unit = circ_lib AND final_time::TIMESTAMPTZ between close_start AND close_end;
 144             EXIT WHEN adjacent.id IS NULL;
 145             time_adjusted := TRUE;
 146             -- RAISE NOTICE 'recursing for closings with final_time: %',final_time;
 147             final_time := evergreen.find_next_open_time(circ_lib, adjacent.close_end::TIMESTAMPTZ, hourly, initial_time, has_hoo)::TEXT;
 148         END LOOP;
 149
 150         EXIT WHEN breakout > 100;
 151         EXIT WHEN NOT time_adjusted;
 152
 153     END LOOP;
 154
 155     RETURN final_time;
 156 END;
 157 $$ LANGUAGE PLPGSQL;
 158
 159 CREATE TYPE action.emergency_closing_stage_1_count AS (circulations INT, reservations INT, holds INT);
 160 CREATE OR REPLACE FUNCTION action.emergency_closing_stage_1 ( e_closing INT )
 161     RETURNS SETOF action.emergency_closing_stage_1_count AS $$
 162 DECLARE
 163     tmp     INT;
 164     touched action.emergency_closing_stage_1_count%ROWTYPE;
 165 BEGIN
 166     -- First, gather circs
 167     INSERT INTO action.emergency_closing_circulation (emergency_closing, circulation)
 168         SELECT  e_closing,
 169                 circ.id
 170           FROM  actor.org_unit_closed closing
 171                 JOIN action.emergency_closing ec ON (closing.emergency_closing = ec.id AND ec.id = e_closing)
 172                 JOIN action.circulation circ ON (
 173                     circ.circ_lib = closing.org_unit
 174                     AND circ.due_date BETWEEN closing.close_start AND (closing.close_end + '1s'::INTERVAL)
 175                     AND circ.xact_finish IS NULL
 176                 )
 177           WHERE NOT EXISTS (SELECT 1 FROM action.emergency_closing_circulation t WHERE t.emergency_closing = e_closing AND t.circulation = circ.id);
 178
 179     GET DIAGNOSTICS tmp = ROW_COUNT;
 180     touched.circulations := tmp;
 181
 182     INSERT INTO action.emergency_closing_reservation (emergency_closing, reservation)
 183         SELECT  e_closing,
 184                 res.id
 185           FROM  actor.org_unit_closed closing
 186                 JOIN action.emergency_closing ec ON (closing.emergency_closing = ec.id AND ec.id = e_closing)
 187                 JOIN booking.reservation res ON (
 188                     res.pickup_lib = closing.org_unit
 189                     AND res.end_time BETWEEN closing.close_start AND (closing.close_end + '1s'::INTERVAL)
 190                 )
 191           WHERE NOT EXISTS (SELECT 1 FROM action.emergency_closing_reservation t WHERE t.emergency_closing = e_closing AND t.reservation = res.id);
 192
 193     GET DIAGNOSTICS tmp = ROW_COUNT;
 194     touched.reservations := tmp;
 195
 196     INSERT INTO action.emergency_closing_hold (emergency_closing, hold)
 197         SELECT  e_closing,
 198                 hold.id
 199           FROM  actor.org_unit_closed closing
 200                 JOIN action.emergency_closing ec ON (closing.emergency_closing = ec.id AND ec.id = e_closing)
 201                 JOIN action.hold_request hold ON (
 202                     pickup_lib = closing.org_unit
 203                     AND hold.shelf_expire_time BETWEEN closing.close_start AND (closing.close_end + '1s'::INTERVAL)
 204                     AND hold.fulfillment_time IS NULL
 205                     AND hold.cancel_time IS NULL
 206                 )
 207           WHERE NOT EXISTS (SELECT 1 FROM action.emergency_closing_hold t WHERE t.emergency_closing = e_closing AND t.hold = hold.id);
 208
 209     GET DIAGNOSTICS tmp = ROW_COUNT;
 210     touched.holds := tmp;
 211
 212     UPDATE  action.emergency_closing
 213       SET   process_start_time = NOW(),
 214             last_update_time = NOW()
 215       WHERE id = e_closing;
 216
 217     RETURN NEXT touched;
 218 END;
 219 $$ LANGUAGE PLPGSQL;
 220
 221 CREATE OR REPLACE FUNCTION action.emergency_closing_stage_2_hold ( hold_closing_entry INT )
 222     RETURNS BOOL AS $$
 223 DECLARE
 224     hold        action.hold_request%ROWTYPE;
 225     e_closing   action.emergency_closing%ROWTYPE;
 226     e_c_hold    action.emergency_closing_hold%ROWTYPE;
 227     closing     actor.org_unit_closed%ROWTYPE;
 228     day_number  INT;
 229     hoo_close   TIME WITHOUT TIME ZONE;
 230     plus_days   INT;
 231 BEGIN
 232     -- Gather objects involved
 233     SELECT  * INTO e_c_hold
 234       FROM  action.emergency_closing_hold
 235       WHERE id = hold_closing_entry;
 236
 237     IF e_c_hold.process_time IS NOT NULL THEN
 238         -- Already processed ... moving on
 239         RETURN FALSE;
 240     END IF;
 241
 242     SELECT  * INTO e_closing
 243       FROM  action.emergency_closing
 244       WHERE id = e_c_hold.emergency_closing;
 245
 246     IF e_closing.process_start_time IS NULL THEN
 247         -- Huh... that's odd. And wrong.
 248         RETURN FALSE;
 249     END IF;
 250
 251     SELECT  * INTO closing
 252       FROM  actor.org_unit_closed
 253       WHERE emergency_closing = e_closing.id;
 254
 255     SELECT  * INTO hold
 256       FROM  action.hold_request h
 257       WHERE id = e_c_hold.hold;
 258
 259     -- Record the processing
 260     UPDATE  action.emergency_closing_hold
 261       SET   original_shelf_expire_time = hold.shelf_expire_time,
 262             process_time = NOW()
 263       WHERE id = hold_closing_entry;
 264
 265     UPDATE  action.emergency_closing
 266       SET   last_update_time = NOW()
 267       WHERE id = e_closing.id;
 268
 269     UPDATE  action.hold_request
 270       SET   shelf_expire_time = evergreen.find_next_open_time(closing.org_unit, hold.shelf_expire_time, TRUE)
 271       WHERE id = hold.id;
 272
 273     RETURN TRUE;
 274 END;
 275 $$ LANGUAGE PLPGSQL;
 276
 277 CREATE OR REPLACE FUNCTION action.emergency_closing_stage_2_circ ( circ_closing_entry INT )
 278     RETURNS BOOL AS $$
 279 DECLARE
 280     circ            action.circulation%ROWTYPE;
 281     e_closing       action.emergency_closing%ROWTYPE;
 282     e_c_circ        action.emergency_closing_circulation%ROWTYPE;
 283     closing         actor.org_unit_closed%ROWTYPE;
 284     adjacent        actor.org_unit_closed%ROWTYPE;
 285     bill            money.billing%ROWTYPE;
 286     last_bill       money.billing%ROWTYPE;
 287     day_number      INT;
 288     hoo_close       TIME WITHOUT TIME ZONE;
 289     plus_days       INT;
 290     avoid_negative  BOOL;
 291     extend_grace    BOOL;
 292     new_due_date    TEXT;
 293 BEGIN
 294     -- Gather objects involved
 295     SELECT  * INTO e_c_circ
 296       FROM  action.emergency_closing_circulation
 297       WHERE id = circ_closing_entry;
 298
 299     IF e_c_circ.process_time IS NOT NULL THEN
 300         -- Already processed ... moving on
 301         RETURN FALSE;
 302     END IF;
 303
 304     SELECT  * INTO e_closing
 305       FROM  action.emergency_closing
 306       WHERE id = e_c_circ.emergency_closing;
 307
 308     IF e_closing.process_start_time IS NULL THEN
 309         -- Huh... that's odd. And wrong.
 310         RETURN FALSE;
 311     END IF;
 312
 313     SELECT  * INTO closing
 314       FROM  actor.org_unit_closed
 315       WHERE emergency_closing = e_closing.id;
 316
 317     SELECT  * INTO circ
 318       FROM  action.circulation
 319       WHERE id = e_c_circ.circulation;
 320
 321     -- Record the processing
 322     UPDATE  action.emergency_closing_circulation
 323       SET   original_due_date = circ.due_date,
 324             process_time = NOW()
 325       WHERE id = circ_closing_entry;
 326
 327     UPDATE  action.emergency_closing
 328       SET   last_update_time = NOW()
 329       WHERE id = e_closing.id;
 330
 331     SELECT value::BOOL INTO avoid_negative FROM actor.org_unit_ancestor_setting('bill.prohibit_negative_balance_on_overdues', circ.circ_lib);
 332     SELECT value::BOOL INTO extend_grace FROM actor.org_unit_ancestor_setting('circ.grace.extend', circ.circ_lib);
 333
 334     new_due_date := evergreen.find_next_open_time( closing.org_unit, circ.due_date, EXTRACT(EPOCH FROM circ.duration)::INT % 86400 > 0 )::TEXT;
 335     UPDATE action.circulation SET due_date = new_due_date::TIMESTAMPTZ WHERE id = circ.id;
 336
 337     -- Now, see if we need to get rid of some fines
 338     SELECT  * INTO last_bill
 339       FROM  money.billing b
 340       WHERE b.xact = circ.id
 341             AND NOT b.voided
 342             AND b.btype = 1
 343       ORDER BY billing_ts DESC
 344       LIMIT 1;
 345
 346     FOR bill IN
 347         SELECT  *
 348           FROM  money.billing b
 349           WHERE b.xact = circ.id
 350                 AND b.btype = 1
 351                 AND NOT b.voided
 352                 AND (
 353                     b.billing_ts BETWEEN closing.close_start AND new_due_date::TIMESTAMPTZ
 354                     OR (extend_grace AND last_bill.billing_ts <= new_due_date::TIMESTAMPTZ + circ.grace_period)
 355                 )
 356                 AND NOT EXISTS (SELECT 1 FROM money.account_adjustment a WHERE a.billing = b.id)
 357           ORDER BY billing_ts
 358     LOOP
 359         IF avoid_negative THEN
 360             PERFORM FROM money.materialized_billable_xact_summary WHERE id = circ.id AND balance_owed < bill.amount;
 361             EXIT WHEN FOUND; -- We can't go negative, and voiding this bill would do that...
 362         END IF;
 363
 364         UPDATE  money.billing
 365           SET   voided = TRUE,
 366                 void_time = NOW(),
 367                 note = COALESCE(note,'') || ' :: Voided by emergency closing handler'
 368           WHERE id = bill.id;
 369     END LOOP;
 370
 371     RETURN TRUE;
 372 END;
 373 $$ LANGUAGE PLPGSQL;
 374
 375 CREATE OR REPLACE FUNCTION action.emergency_closing_stage_2_reservation ( res_closing_entry INT )
 376     RETURNS BOOL AS $$
 377 DECLARE
 378     res             booking.reservation%ROWTYPE;
 379     e_closing       action.emergency_closing%ROWTYPE;
 380     e_c_res         action.emergency_closing_reservation%ROWTYPE;
 381     closing         actor.org_unit_closed%ROWTYPE;
 382     adjacent        actor.org_unit_closed%ROWTYPE;
 383     bill            money.billing%ROWTYPE;
 384     day_number      INT;
 385     hoo_close       TIME WITHOUT TIME ZONE;
 386     plus_days       INT;
 387     avoid_negative  BOOL;
 388     new_due_date    TEXT;
 389 BEGIN
 390     -- Gather objects involved
 391     SELECT  * INTO e_c_res
 392       FROM  action.emergency_closing_reservation
 393       WHERE id = res_closing_entry;
 394
 395     IF e_c_res.process_time IS NOT NULL THEN
 396         -- Already processed ... moving on
 397         RETURN FALSE;
 398     END IF;
 399
 400     SELECT  * INTO e_closing
 401       FROM  action.emergency_closing
 402       WHERE id = e_c_res.emergency_closing;
 403
 404     IF e_closing.process_start_time IS NULL THEN
 405         -- Huh... that's odd. And wrong.
 406         RETURN FALSE;
 407     END IF;
 408
 409     SELECT  * INTO closing
 410       FROM  actor.org_unit_closed
 411       WHERE emergency_closing = e_closing.id;
 412
 413     SELECT  * INTO res
 414       FROM  booking.reservation
 415       WHERE id = e_c_res.reservation;
 416
 417     IF res.pickup_lib IS NULL THEN -- Need to be far enough along to have a pickup lib
 418         RETURN FALSE;
 419     END IF;
 420
 421     -- Record the processing
 422     UPDATE  action.emergency_closing_reservation
 423       SET   original_end_time = res.end_time,
 424             process_time = NOW()
 425       WHERE id = res_closing_entry;
 426
 427     UPDATE  action.emergency_closing
 428       SET   last_update_time = NOW()
 429       WHERE id = e_closing.id;
 430
 431     SELECT value::BOOL INTO avoid_negative FROM actor.org_unit_ancestor_setting('bill.prohibit_negative_balance_on_overdues', res.pickup_lib);
 432
 433     new_due_date := evergreen.find_next_open_time( closing.org_unit, res.end_time, EXTRACT(EPOCH FROM res.booking_interval)::INT % 86400 > 0 )::TEXT;
 434     UPDATE booking.reservation SET end_time = new_due_date::TIMESTAMPTZ WHERE id = res.id;
 435
 436     -- Now, see if we need to get rid of some fines
 437     FOR bill IN
 438         SELECT  *
 439           FROM  money.billing b
 440           WHERE b.xact = res.id
 441                 AND b.btype = 1
 442                 AND NOT b.voided
 443                 AND b.billing_ts BETWEEN closing.close_start AND new_due_date::TIMESTAMPTZ
 444                 AND NOT EXISTS (SELECT 1 FROM money.account_adjustment a WHERE a.billing = b.id)
 445     LOOP
 446         IF avoid_negative THEN
 447             PERFORM FROM money.materialized_billable_xact_summary WHERE id = res.id AND balance_owed < bill.amount;
 448             EXIT WHEN FOUND; -- We can't go negative, and voiding this bill would do that...
 449         END IF;
 450
 451         UPDATE  money.billing
 452           SET   voided = TRUE,
 453                 void_time = NOW(),
 454                 note = COALESCE(note,'') || ' :: Voided by emergency closing handler'
 455           WHERE id = bill.id;
 456     END LOOP;
 457
 458     RETURN TRUE;
 459 END;
 460 $$ LANGUAGE PLPGSQL;
 461
 462 COMMIT;
 463