3 SELECT evergreen.upgrade_deps_block_check('1115', :eg_version);
5 INSERT INTO permission.perm_list (id,code,description) VALUES ( 607, 'EMERGENCY_CLOSING', 'Create and manage Emergency Closings');
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');
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
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;
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
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);
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
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);
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
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);
54 CREATE OR REPLACE VIEW action.emergency_closing_status AS
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)
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 $$
75 hoo_open TIME WITHOUT TIME ZONE;
76 hoo_close TIME WITHOUT TIME ZONE;
77 adjacent actor.org_unit_closed%ROWTYPE;
85 IF initial_time IS NULL THEN
86 initial_time := initial::TIME;
89 final_time := (initial + '1 second'::INTERVAL)::TEXT;
91 breakout := breakout + 1;
93 time_adjusted := FALSE;
95 IF dow_count > 0 THEN -- we're recursing, so check for HOO closing
96 day_number := EXTRACT(ISODOW FROM final_time::TIMESTAMPTZ) - 1;
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
103 -- RAISE NOTICE 'initial time: %; dow: %; close: %',initial_time,day_number,hoo_close;
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;
112 IF hoo_close IS NULL THEN -- no hours of operation ... assume no closing?
113 hoo_close := '23:59:59';
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;
123 final_time := final_time||' 23:59:59';
127 -- Loop through other closings
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;
136 EXIT WHEN breakout > 100;
137 EXIT WHEN NOT time_adjusted;
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 $$
150 touched action.emergency_closing_stage_1_count%ROWTYPE;
152 -- First, gather circs
153 INSERT INTO action.emergency_closing_circulation (emergency_closing, circulation)
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
163 WHERE NOT EXISTS (SELECT 1 FROM action.emergency_closing_circulation t WHERE t.emergency_closing = e_closing AND t.circulation = circ.id);
165 GET DIAGNOSTICS tmp = ROW_COUNT;
166 touched.circulations := tmp;
168 INSERT INTO action.emergency_closing_reservation (emergency_closing, reservation)
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)
177 WHERE NOT EXISTS (SELECT 1 FROM action.emergency_closing_reservation t WHERE t.emergency_closing = e_closing AND t.reservation = res.id);
179 GET DIAGNOSTICS tmp = ROW_COUNT;
180 touched.reservations := tmp;
182 INSERT INTO action.emergency_closing_hold (emergency_closing, hold)
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
193 WHERE NOT EXISTS (SELECT 1 FROM action.emergency_closing_hold t WHERE t.emergency_closing = e_closing AND t.hold = hold.id);
195 GET DIAGNOSTICS tmp = ROW_COUNT;
196 touched.holds := tmp;
198 UPDATE action.emergency_closing
199 SET process_start_time = NOW(),
200 last_update_time = NOW()
201 WHERE id = e_closing;
207 CREATE OR REPLACE FUNCTION action.emergency_closing_stage_2_hold ( hold_closing_entry INT )
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;
215 hoo_close TIME WITHOUT TIME ZONE;
218 -- Gather objects involved
219 SELECT * INTO e_c_hold
220 FROM action.emergency_closing_hold
221 WHERE id = hold_closing_entry;
223 IF e_c_hold.process_time IS NOT NULL THEN
224 -- Already processed ... moving on
228 SELECT * INTO e_closing
229 FROM action.emergency_closing
230 WHERE id = e_c_hold.emergency_closing;
232 IF e_closing.process_start_time IS NULL THEN
233 -- Huh... that's odd. And wrong.
237 SELECT * INTO closing
238 FROM actor.org_unit_closed
239 WHERE emergency_closing = e_closing.id;
242 FROM action.hold_request h
243 WHERE id = e_c_hold.hold;
245 -- Record the processing
246 UPDATE action.emergency_closing_hold
247 SET original_shelf_expire_time = hold.shelf_expire_time,
249 WHERE id = hold_closing_entry;
251 UPDATE action.emergency_closing
252 SET last_update_time = NOW()
253 WHERE id = e_closing.id;
255 UPDATE action.hold_request
256 SET shelf_expire_time = evergreen.find_next_open_time(closing.org_unit, hold.shelf_expire_time, TRUE)
263 CREATE OR REPLACE FUNCTION action.emergency_closing_stage_2_circ ( circ_closing_entry INT )
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;
274 hoo_close TIME WITHOUT TIME ZONE;
280 -- Gather objects involved
281 SELECT * INTO e_c_circ
282 FROM action.emergency_closing_circulation
283 WHERE id = circ_closing_entry;
285 IF e_c_circ.process_time IS NOT NULL THEN
286 -- Already processed ... moving on
290 SELECT * INTO e_closing
291 FROM action.emergency_closing
292 WHERE id = e_c_circ.emergency_closing;
294 IF e_closing.process_start_time IS NULL THEN
295 -- Huh... that's odd. And wrong.
299 SELECT * INTO closing
300 FROM actor.org_unit_closed
301 WHERE emergency_closing = e_closing.id;
304 FROM action.circulation
305 WHERE id = e_c_circ.circulation;
307 -- Record the processing
308 UPDATE action.emergency_closing_circulation
309 SET original_due_date = circ.due_date,
311 WHERE id = circ_closing_entry;
313 UPDATE action.emergency_closing
314 SET last_update_time = NOW()
315 WHERE id = e_closing.id;
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);
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;
323 -- Now, see if we need to get rid of some fines
324 SELECT * INTO last_bill
326 WHERE b.xact = circ.id
329 ORDER BY billing_ts DESC
335 WHERE b.xact = circ.id
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)
342 AND NOT EXISTS (SELECT 1 FROM money.account_adjustment a WHERE a.billing = b.id)
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...
353 note = COALESCE(note,'') || ' :: Voided by emergency closing handler'
361 CREATE OR REPLACE FUNCTION action.emergency_closing_stage_2_reservation ( res_closing_entry INT )
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;
371 hoo_close TIME WITHOUT TIME ZONE;
376 -- Gather objects involved
377 SELECT * INTO e_c_res
378 FROM action.emergency_closing_reservation
379 WHERE id = res_closing_entry;
381 IF e_c_res.process_time IS NOT NULL THEN
382 -- Already processed ... moving on
386 SELECT * INTO e_closing
387 FROM action.emergency_closing
388 WHERE id = e_c_res.emergency_closing;
390 IF e_closing.process_start_time IS NULL THEN
391 -- Huh... that's odd. And wrong.
395 SELECT * INTO closing
396 FROM actor.org_unit_closed
397 WHERE emergency_closing = e_closing.id;
400 FROM booking.reservation
401 WHERE id = e_c_res.reservation;
403 IF res.pickup_lib IS NULL THEN -- Need to be far enough along to have a pickup lib
407 -- Record the processing
408 UPDATE action.emergency_closing_reservation
409 SET original_end_time = res.end_time,
411 WHERE id = res_closing_entry;
413 UPDATE action.emergency_closing
414 SET last_update_time = NOW()
415 WHERE id = e_closing.id;
417 SELECT value::BOOL INTO avoid_negative FROM actor.org_unit_ancestor_setting('bill.prohibit_negative_balance_on_overdues', res.pickup_lib);
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;
422 -- Now, see if we need to get rid of some fines
426 WHERE b.xact = res.id
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)
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...
440 note = COALESCE(note,'') || ' :: Voided by emergency closing handler'