LP#1766716: Adjust date display logic
[working/Evergreen.git] / 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