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, has_hoo BOOL DEFAULT TRUE )
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;
89 IF initial_time IS NULL THEN
90 initial_time := initial::TIME;
93 final_time := (initial + '1 second'::INTERVAL)::TEXT;
95 breakout := breakout + 1;
97 time_adjusted := FALSE;
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.
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)
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
109 -- RAISE NOTICE 'initial time: %; dow: %; close: %',initial_time,day_number,hoo_close;
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;
117 has_hoo := TRUE; --We do have hours open sometimes, yay!
120 IF hoo_close IS NULL THEN -- no hours of operation ... assume no closing?
121 hoo_close := '23:59:59';
127 IF NOT has_hoo THEN -- If always closed then forget the extra days - just determine based on closures.
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;
135 final_time := final_time||' 23:59:59';
139 --RAISE NOTICE 'final_time: %',final_time;
141 -- Loop through other closings
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;
150 EXIT WHEN breakout > 100;
151 EXIT WHEN NOT time_adjusted;
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 $$
164 touched action.emergency_closing_stage_1_count%ROWTYPE;
166 -- First, gather circs
167 INSERT INTO action.emergency_closing_circulation (emergency_closing, circulation)
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
177 WHERE NOT EXISTS (SELECT 1 FROM action.emergency_closing_circulation t WHERE t.emergency_closing = e_closing AND t.circulation = circ.id);
179 GET DIAGNOSTICS tmp = ROW_COUNT;
180 touched.circulations := tmp;
182 INSERT INTO action.emergency_closing_reservation (emergency_closing, reservation)
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)
191 WHERE NOT EXISTS (SELECT 1 FROM action.emergency_closing_reservation t WHERE t.emergency_closing = e_closing AND t.reservation = res.id);
193 GET DIAGNOSTICS tmp = ROW_COUNT;
194 touched.reservations := tmp;
196 INSERT INTO action.emergency_closing_hold (emergency_closing, hold)
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
207 WHERE NOT EXISTS (SELECT 1 FROM action.emergency_closing_hold t WHERE t.emergency_closing = e_closing AND t.hold = hold.id);
209 GET DIAGNOSTICS tmp = ROW_COUNT;
210 touched.holds := tmp;
212 UPDATE action.emergency_closing
213 SET process_start_time = NOW(),
214 last_update_time = NOW()
215 WHERE id = e_closing;
221 CREATE OR REPLACE FUNCTION action.emergency_closing_stage_2_hold ( hold_closing_entry INT )
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;
229 hoo_close TIME WITHOUT TIME ZONE;
232 -- Gather objects involved
233 SELECT * INTO e_c_hold
234 FROM action.emergency_closing_hold
235 WHERE id = hold_closing_entry;
237 IF e_c_hold.process_time IS NOT NULL THEN
238 -- Already processed ... moving on
242 SELECT * INTO e_closing
243 FROM action.emergency_closing
244 WHERE id = e_c_hold.emergency_closing;
246 IF e_closing.process_start_time IS NULL THEN
247 -- Huh... that's odd. And wrong.
251 SELECT * INTO closing
252 FROM actor.org_unit_closed
253 WHERE emergency_closing = e_closing.id;
256 FROM action.hold_request h
257 WHERE id = e_c_hold.hold;
259 -- Record the processing
260 UPDATE action.emergency_closing_hold
261 SET original_shelf_expire_time = hold.shelf_expire_time,
263 WHERE id = hold_closing_entry;
265 UPDATE action.emergency_closing
266 SET last_update_time = NOW()
267 WHERE id = e_closing.id;
269 UPDATE action.hold_request
270 SET shelf_expire_time = evergreen.find_next_open_time(closing.org_unit, hold.shelf_expire_time, TRUE)
277 CREATE OR REPLACE FUNCTION action.emergency_closing_stage_2_circ ( circ_closing_entry INT )
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;
288 hoo_close TIME WITHOUT TIME ZONE;
294 -- Gather objects involved
295 SELECT * INTO e_c_circ
296 FROM action.emergency_closing_circulation
297 WHERE id = circ_closing_entry;
299 IF e_c_circ.process_time IS NOT NULL THEN
300 -- Already processed ... moving on
304 SELECT * INTO e_closing
305 FROM action.emergency_closing
306 WHERE id = e_c_circ.emergency_closing;
308 IF e_closing.process_start_time IS NULL THEN
309 -- Huh... that's odd. And wrong.
313 SELECT * INTO closing
314 FROM actor.org_unit_closed
315 WHERE emergency_closing = e_closing.id;
318 FROM action.circulation
319 WHERE id = e_c_circ.circulation;
321 -- Record the processing
322 UPDATE action.emergency_closing_circulation
323 SET original_due_date = circ.due_date,
325 WHERE id = circ_closing_entry;
327 UPDATE action.emergency_closing
328 SET last_update_time = NOW()
329 WHERE id = e_closing.id;
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);
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;
337 -- Now, see if we need to get rid of some fines
338 SELECT * INTO last_bill
340 WHERE b.xact = circ.id
343 ORDER BY billing_ts DESC
349 WHERE b.xact = circ.id
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)
356 AND NOT EXISTS (SELECT 1 FROM money.account_adjustment a WHERE a.billing = b.id)
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...
367 note = COALESCE(note,'') || ' :: Voided by emergency closing handler'
375 CREATE OR REPLACE FUNCTION action.emergency_closing_stage_2_reservation ( res_closing_entry INT )
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;
385 hoo_close TIME WITHOUT TIME ZONE;
390 -- Gather objects involved
391 SELECT * INTO e_c_res
392 FROM action.emergency_closing_reservation
393 WHERE id = res_closing_entry;
395 IF e_c_res.process_time IS NOT NULL THEN
396 -- Already processed ... moving on
400 SELECT * INTO e_closing
401 FROM action.emergency_closing
402 WHERE id = e_c_res.emergency_closing;
404 IF e_closing.process_start_time IS NULL THEN
405 -- Huh... that's odd. And wrong.
409 SELECT * INTO closing
410 FROM actor.org_unit_closed
411 WHERE emergency_closing = e_closing.id;
414 FROM booking.reservation
415 WHERE id = e_c_res.reservation;
417 IF res.pickup_lib IS NULL THEN -- Need to be far enough along to have a pickup lib
421 -- Record the processing
422 UPDATE action.emergency_closing_reservation
423 SET original_end_time = res.end_time,
425 WHERE id = res_closing_entry;
427 UPDATE action.emergency_closing
428 SET last_update_time = NOW()
429 WHERE id = e_closing.id;
431 SELECT value::BOOL INTO avoid_negative FROM actor.org_unit_ancestor_setting('bill.prohibit_negative_balance_on_overdues', res.pickup_lib);
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;
436 -- Now, see if we need to get rid of some fines
440 WHERE b.xact = res.id
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)
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...
454 note = COALESCE(note,'') || ' :: Voided by emergency closing handler'