From 50e5b71124f903b3dd3d30fbac227b10612ebbb4 Mon Sep 17 00:00:00 2001 From: Rogan Hamby Date: Fri, 27 Mar 2020 14:13:46 -0400 Subject: [PATCH] adding hold expirations to emergency closing handler Signed-off-by: Rogan Hamby --- .../src/sql/Pg/096.schema.emergency_closing.sql | 21 +++- ...x.schema.emergency_closing_hold_expirations.sql | 140 +++++++++++++++++++++ .../Administration/Emergency_Closing_Handler.adoc | 6 + 3 files changed, 164 insertions(+), 3 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/xxxx.schema.emergency_closing_hold_expirations.sql create mode 100644 docs/RELEASE_NOTES_NEXT/Administration/Emergency_Closing_Handler.adoc diff --git a/Open-ILS/src/sql/Pg/096.schema.emergency_closing.sql b/Open-ILS/src/sql/Pg/096.schema.emergency_closing.sql index 88abf35409..c29ea256c3 100644 --- a/Open-ILS/src/sql/Pg/096.schema.emergency_closing.sql +++ b/Open-ILS/src/sql/Pg/096.schema.emergency_closing.sql @@ -54,6 +54,7 @@ CREATE TABLE action.emergency_closing_hold ( emergency_closing INT NOT NULL REFERENCES action.emergency_closing (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, hold INT NOT NULL REFERENCES action.hold_request (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, original_shelf_expire_time TIMESTAMPTZ, + orginal_expire_time TIMESTAMPTZ, process_time TIMESTAMPTZ ); CREATE INDEX emergency_closing_hold_emergency_closing_idx ON action.emergency_closing_hold (emergency_closing); @@ -194,8 +195,12 @@ BEGIN JOIN action.emergency_closing ec ON (closing.emergency_closing = ec.id AND ec.id = e_closing) JOIN action.hold_request hold ON ( pickup_lib = closing.org_unit - AND hold.shelf_expire_time BETWEEN closing.close_start AND (closing.close_end + '1s'::INTERVAL) - AND hold.fulfillment_time IS NULL + AND + ( + hold.shelf_expire_time BETWEEN closing.close_start AND (closing.close_end + '1s'::INTERVAL) + OR hold.expire_time BETWEEN closing.close_start AND (closing.close_end + '1s'::INTERVAL) + ) + AND hold.fulfillment_time IS NULL AND hold.cancel_time IS NULL ) WHERE NOT EXISTS (SELECT 1 FROM action.emergency_closing_hold t WHERE t.emergency_closing = e_closing AND t.hold = hold.id); @@ -253,6 +258,7 @@ BEGIN -- Record the processing UPDATE action.emergency_closing_hold SET original_shelf_expire_time = hold.shelf_expire_time, + original_expire_time = hold.expire_time, process_time = NOW() WHERE id = hold_closing_entry; @@ -261,7 +267,16 @@ BEGIN WHERE id = e_closing.id; UPDATE action.hold_request - SET shelf_expire_time = evergreen.find_next_open_time(closing.org_unit, hold.shelf_expire_time, TRUE) + SET shelf_expire_time = CASE + WHEN shelf_expire_time BETWEEN closing.close_start AND (closing.close_end + '1s'::INTERVAL) + THEN evergreen.find_next_open_time(closing.org_unit, hold.shelf_expire_time, TRUE) + ELSE hold.shelf_expire_time + END, + expire_time = CASE + WHEN expire_time BETWEEN closing.close_start AND (closing.close_end + '1s'::INTERVAL) + THEN evergreen.find_next_open_time(closing.org_unit, hold.expire_time, TRUE) + ELSE hold.expire_time + END WHERE id = hold.id; RETURN TRUE; diff --git a/Open-ILS/src/sql/Pg/upgrade/xxxx.schema.emergency_closing_hold_expirations.sql b/Open-ILS/src/sql/Pg/upgrade/xxxx.schema.emergency_closing_hold_expirations.sql new file mode 100644 index 0000000000..9d60fb12f3 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/xxxx.schema.emergency_closing_hold_expirations.sql @@ -0,0 +1,140 @@ +BEGIN; + +SELECT evergreen.upgrade_deps_block_check('xxxx', :eg_version); + +ALTER TABLE action.emergency_closing_hold ADD COLUMN original_expire_time timestamptz; + +CREATE TYPE action.emergency_closing_stage_1_count AS (circulations INT, reservations INT, holds INT); +CREATE OR REPLACE FUNCTION action.emergency_closing_stage_1 ( e_closing INT ) + RETURNS SETOF action.emergency_closing_stage_1_count AS $$ +DECLARE + tmp INT; + touched action.emergency_closing_stage_1_count%ROWTYPE; +BEGIN + -- First, gather circs + INSERT INTO action.emergency_closing_circulation (emergency_closing, circulation) + SELECT e_closing, + circ.id + FROM actor.org_unit_closed closing + JOIN action.emergency_closing ec ON (closing.emergency_closing = ec.id AND ec.id = e_closing) + JOIN action.circulation circ ON ( + circ.circ_lib = closing.org_unit + AND circ.due_date BETWEEN closing.close_start AND (closing.close_end + '1s'::INTERVAL) + AND circ.xact_finish IS NULL + ) + WHERE NOT EXISTS (SELECT 1 FROM action.emergency_closing_circulation t WHERE t.emergency_closing = e_closing AND t.circulation = circ.id); + + GET DIAGNOSTICS tmp = ROW_COUNT; + touched.circulations := tmp; + + INSERT INTO action.emergency_closing_reservation (emergency_closing, reservation) + SELECT e_closing, + res.id + FROM actor.org_unit_closed closing + JOIN action.emergency_closing ec ON (closing.emergency_closing = ec.id AND ec.id = e_closing) + JOIN booking.reservation res ON ( + res.pickup_lib = closing.org_unit + AND res.end_time BETWEEN closing.close_start AND (closing.close_end + '1s'::INTERVAL) + ) + WHERE NOT EXISTS (SELECT 1 FROM action.emergency_closing_reservation t WHERE t.emergency_closing = e_closing AND t.reservation = res.id); + + GET DIAGNOSTICS tmp = ROW_COUNT; + touched.reservations := tmp; + + INSERT INTO action.emergency_closing_hold (emergency_closing, hold) + SELECT e_closing, + hold.id + FROM actor.org_unit_closed closing + JOIN action.emergency_closing ec ON (closing.emergency_closing = ec.id AND ec.id = e_closing) + JOIN action.hold_request hold ON ( + pickup_lib = closing.org_unit + AND + ( + hold.shelf_expire_time BETWEEN closing.close_start AND (closing.close_end + '1s'::INTERVAL) + OR hold.expire_time BETWEEN closing.close_start AND (closing.close_end + '1s'::INTERVAL) + ) + AND hold.fulfillment_time IS NULL + AND hold.cancel_time IS NULL + ) + WHERE NOT EXISTS (SELECT 1 FROM action.emergency_closing_hold t WHERE t.emergency_closing = e_closing AND t.hold = hold.id); + + GET DIAGNOSTICS tmp = ROW_COUNT; + touched.holds := tmp; + + UPDATE action.emergency_closing + SET process_start_time = NOW(), + last_update_time = NOW() + WHERE id = e_closing; + + RETURN NEXT touched; +END; +$$ LANGUAGE PLPGSQL; + + +CREATE OR REPLACE FUNCTION action.emergency_closing_stage_2_hold ( hold_closing_entry INT ) + RETURNS BOOL AS $$ +DECLARE + hold action.hold_request%ROWTYPE; + e_closing action.emergency_closing%ROWTYPE; + e_c_hold action.emergency_closing_hold%ROWTYPE; + closing actor.org_unit_closed%ROWTYPE; + day_number INT; + hoo_close TIME WITHOUT TIME ZONE; + plus_days INT; +BEGIN + -- Gather objects involved + SELECT * INTO e_c_hold + FROM action.emergency_closing_hold + WHERE id = hold_closing_entry; + + IF e_c_hold.process_time IS NOT NULL THEN + -- Already processed ... moving on + RETURN FALSE; + END IF; + + SELECT * INTO e_closing + FROM action.emergency_closing + WHERE id = e_c_hold.emergency_closing; + + IF e_closing.process_start_time IS NULL THEN + -- Huh... that's odd. And wrong. + RETURN FALSE; + END IF; + + SELECT * INTO closing + FROM actor.org_unit_closed + WHERE emergency_closing = e_closing.id; + + SELECT * INTO hold + FROM action.hold_request h + WHERE id = e_c_hold.hold; + + -- Record the processing + UPDATE action.emergency_closing_hold + SET original_shelf_expire_time = hold.shelf_expire_time, + original_expire_time = hold.expire_time, + process_time = NOW() + WHERE id = hold_closing_entry; + + UPDATE action.emergency_closing + SET last_update_time = NOW() + WHERE id = e_closing.id; + + UPDATE action.hold_request + SET shelf_expire_time = CASE + WHEN shelf_expire_time BETWEEN closing.close_start AND (closing.close_end + '1s'::INTERVAL) + THEN evergreen.find_next_open_time(closing.org_unit, hold.shelf_expire_time, TRUE) + ELSE hold.shelf_expire_time + END, + expire_time = CASE + WHEN expire_time BETWEEN closing.close_start AND (closing.close_end + '1s'::INTERVAL) + THEN evergreen.find_next_open_time(closing.org_unit, hold.expire_time, TRUE) + ELSE hold.expire_time + END + WHERE id = hold.id; + + RETURN TRUE; +END; +$$ LANGUAGE PLPGSQL; + +COMMIT; diff --git a/docs/RELEASE_NOTES_NEXT/Administration/Emergency_Closing_Handler.adoc b/docs/RELEASE_NOTES_NEXT/Administration/Emergency_Closing_Handler.adoc new file mode 100644 index 0000000000..0777311aba --- /dev/null +++ b/docs/RELEASE_NOTES_NEXT/Administration/Emergency_Closing_Handler.adoc @@ -0,0 +1,6 @@ +Emergency Closing Handler Enhancement +^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ + +The Emgerency Closing Handler will now also push out the +expiration dates of holds past the end of the closed date +in addition to their shelf expirations. -- 2.11.0