2 * Copyright (C) 2018 Equinox Open Library Initiative Inc.
3 * Mike Rylander <mrylander@gmail.com>
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.
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.
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
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;
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
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);
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
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);
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
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);
62 CREATE OR REPLACE VIEW action.emergency_closing_status AS
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)
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 $$
83 hoo_open TIME WITHOUT TIME ZONE;
84 hoo_close TIME WITHOUT TIME ZONE;
85 adjacent actor.org_unit_closed%ROWTYPE;
93 IF initial_time IS NULL THEN
94 initial_time := initial::TIME;
97 final_time := (initial + '1 second'::INTERVAL)::TEXT;
99 breakout := breakout + 1;
101 time_adjusted := FALSE;
103 IF dow_count > 0 THEN -- we're recursing, so check for HOO closing
104 day_number := EXTRACT(ISODOW FROM final_time::TIMESTAMPTZ) - 1;
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
111 -- RAISE NOTICE 'initial time: %; dow: %; close: %',initial_time,day_number,hoo_close;
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;
120 IF hoo_close IS NULL THEN -- no hours of operation ... assume no closing?
121 hoo_close := '23:59:59';
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;
131 final_time := final_time||' 23:59:59';
135 -- Loop through other closings
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;
144 EXIT WHEN breakout > 100;
145 EXIT WHEN NOT time_adjusted;
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 $$
158 touched action.emergency_closing_stage_1_count%ROWTYPE;
160 -- First, gather circs
161 INSERT INTO action.emergency_closing_circulation (emergency_closing, circulation)
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
171 WHERE NOT EXISTS (SELECT 1 FROM action.emergency_closing_circulation t WHERE t.emergency_closing = e_closing AND t.circulation = circ.id);
173 GET DIAGNOSTICS tmp = ROW_COUNT;
174 touched.circulations := tmp;
176 INSERT INTO action.emergency_closing_reservation (emergency_closing, reservation)
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)
185 WHERE NOT EXISTS (SELECT 1 FROM action.emergency_closing_reservation t WHERE t.emergency_closing = e_closing AND t.reservation = res.id);
187 GET DIAGNOSTICS tmp = ROW_COUNT;
188 touched.reservations := tmp;
190 INSERT INTO action.emergency_closing_hold (emergency_closing, hold)
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
201 WHERE NOT EXISTS (SELECT 1 FROM action.emergency_closing_hold t WHERE t.emergency_closing = e_closing AND t.hold = hold.id);
203 GET DIAGNOSTICS tmp = ROW_COUNT;
204 touched.holds := tmp;
206 UPDATE action.emergency_closing
207 SET process_start_time = NOW(),
208 last_update_time = NOW()
209 WHERE id = e_closing;
215 CREATE OR REPLACE FUNCTION action.emergency_closing_stage_2_hold ( hold_closing_entry INT )
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;
223 hoo_close TIME WITHOUT TIME ZONE;
226 -- Gather objects involved
227 SELECT * INTO e_c_hold
228 FROM action.emergency_closing_hold
229 WHERE id = hold_closing_entry;
231 IF e_c_hold.process_time IS NOT NULL THEN
232 -- Already processed ... moving on
236 SELECT * INTO e_closing
237 FROM action.emergency_closing
238 WHERE id = e_c_hold.emergency_closing;
240 IF e_closing.process_start_time IS NULL THEN
241 -- Huh... that's odd. And wrong.
245 SELECT * INTO closing
246 FROM actor.org_unit_closed
247 WHERE emergency_closing = e_closing.id;
250 FROM action.hold_request h
251 WHERE id = e_c_hold.hold;
253 -- Record the processing
254 UPDATE action.emergency_closing_hold
255 SET original_shelf_expire_time = hold.shelf_expire_time,
257 WHERE id = hold_closing_entry;
259 UPDATE action.emergency_closing
260 SET last_update_time = NOW()
261 WHERE id = e_closing.id;
263 UPDATE action.hold_request
264 SET shelf_expire_time = evergreen.find_next_open_time(closing.org_unit, hold.shelf_expire_time, TRUE)
271 CREATE OR REPLACE FUNCTION action.emergency_closing_stage_2_circ ( circ_closing_entry INT )
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;
282 hoo_close TIME WITHOUT TIME ZONE;
288 -- Gather objects involved
289 SELECT * INTO e_c_circ
290 FROM action.emergency_closing_circulation
291 WHERE id = circ_closing_entry;
293 IF e_c_circ.process_time IS NOT NULL THEN
294 -- Already processed ... moving on
298 SELECT * INTO e_closing
299 FROM action.emergency_closing
300 WHERE id = e_c_circ.emergency_closing;
302 IF e_closing.process_start_time IS NULL THEN
303 -- Huh... that's odd. And wrong.
307 SELECT * INTO closing
308 FROM actor.org_unit_closed
309 WHERE emergency_closing = e_closing.id;
312 FROM action.circulation
313 WHERE id = e_c_circ.circulation;
315 -- Record the processing
316 UPDATE action.emergency_closing_circulation
317 SET original_due_date = circ.due_date,
319 WHERE id = circ_closing_entry;
321 UPDATE action.emergency_closing
322 SET last_update_time = NOW()
323 WHERE id = e_closing.id;
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);
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;
331 -- Now, see if we need to get rid of some fines
332 SELECT * INTO last_bill
334 WHERE b.xact = circ.id
337 ORDER BY billing_ts DESC
343 WHERE b.xact = circ.id
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)
350 AND NOT EXISTS (SELECT 1 FROM money.account_adjustment a WHERE a.billing = b.id)
353 IF avoid_negative THEN
354 PERFORM FROM money.materialized_billable_xact_summary WHERE id = circ.id AND balance_owed < bill.amount;
355 EXIT WHEN FOUND; -- We can't go negative, and voiding this bill would do that...
361 note = COALESCE(note,'') || ' :: Voided by emergency closing handler'
369 CREATE OR REPLACE FUNCTION action.emergency_closing_stage_2_reservation ( res_closing_entry INT )
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;
379 hoo_close TIME WITHOUT TIME ZONE;
384 -- Gather objects involved
385 SELECT * INTO e_c_res
386 FROM action.emergency_closing_reservation
387 WHERE id = res_closing_entry;
389 IF e_c_res.process_time IS NOT NULL THEN
390 -- Already processed ... moving on
394 SELECT * INTO e_closing
395 FROM action.emergency_closing
396 WHERE id = e_c_res.emergency_closing;
398 IF e_closing.process_start_time IS NULL THEN
399 -- Huh... that's odd. And wrong.
403 SELECT * INTO closing
404 FROM actor.org_unit_closed
405 WHERE emergency_closing = e_closing.id;
408 FROM booking.reservation
409 WHERE id = e_c_res.reservation;
411 IF res.pickup_lib IS NULL THEN -- Need to be far enough along to have a pickup lib
415 -- Record the processing
416 UPDATE action.emergency_closing_reservation
417 SET original_end_time = res.end_time,
419 WHERE id = res_closing_entry;
421 UPDATE action.emergency_closing
422 SET last_update_time = NOW()
423 WHERE id = e_closing.id;
425 SELECT value::BOOL INTO avoid_negative FROM actor.org_unit_ancestor_setting('bill.prohibit_negative_balance_on_overdues', res.pickup_lib);
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;
430 -- Now, see if we need to get rid of some fines
434 WHERE b.xact = res.id
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)
440 IF avoid_negative THEN
441 PERFORM FROM money.materialized_billable_xact_summary WHERE id = res.id AND balance_owed < bill.amount;
442 EXIT WHEN FOUND; -- We can't go negative, and voiding this bill would do that...
448 note = COALESCE(note,'') || ' :: Voided by emergency closing handler'