Open-ILS/src/sql/Pg/upgrade/XXXX.schema.emergency_closing.sql

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