From 3272baf1fd431ae5a10dd634366a8de0095210f8 Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Mon, 23 Mar 2020 10:36:48 -0400 Subject: [PATCH] LP#1818912: normalize time by truncating to second granularity When the emergency closing handler goes to grab the list of circulations and holds it should work on, it can miss some due to sub-second components of the closing start/end timestamps. This commit introduces a function for truncating timestamps to second-granularity, and then uses that function when looking for in-range objects. Signed-off-by: Mike Rylander --- Open-ILS/src/sql/Pg/000.functions.general.sql | 4 + .../src/sql/Pg/096.schema.emergency_closing.sql | 18 +- ...nction.emergency_closing_time_normalization.sql | 334 +++++++++++++++++++++ 3 files changed, 349 insertions(+), 7 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.function.emergency_closing_time_normalization.sql diff --git a/Open-ILS/src/sql/Pg/000.functions.general.sql b/Open-ILS/src/sql/Pg/000.functions.general.sql index e0aae8d6db..368fb6caa7 100644 --- a/Open-ILS/src/sql/Pg/000.functions.general.sql +++ b/Open-ILS/src/sql/Pg/000.functions.general.sql @@ -7,6 +7,10 @@ BEGIN; CREATE SCHEMA evergreen; +CREATE OR REPLACE FUNCTION evergreen.truncate_timestamptz_to_second (TIMESTAMPTZ) RERTURNS TIMESTAMPTZ AS $$ + SELECT TIMESTAMP WITH TIME ZONE 'epoch' + TRUNC(EXTRACT(EPOCH FROM $1)) * INTERVAL '1 second'; +$$ LANGUAGE SQL STRICT STABLE; + CREATE OR REPLACE FUNCTION evergreen.change_db_setting(setting_name TEXT, settings TEXT[]) RETURNS VOID AS $$ BEGIN EXECUTE 'ALTER DATABASE ' || quote_ident(current_database()) || ' SET ' || quote_ident(setting_name) || ' = ' || array_to_string(settings, ','); 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..46f6b03b47 100644 --- a/Open-ILS/src/sql/Pg/096.schema.emergency_closing.sql +++ b/Open-ILS/src/sql/Pg/096.schema.emergency_closing.sql @@ -94,7 +94,7 @@ BEGIN initial_time := initial::TIME; END IF; - final_time := (initial + '1 second'::INTERVAL)::TEXT; + final_time := (evergreen.truncate_timestamptz_to_second(initial) + '1 second'::INTERVAL)::TEXT; LOOP breakout := breakout + 1; @@ -134,7 +134,11 @@ BEGIN -- Loop through other closings LOOP - SELECT * INTO adjacent FROM actor.org_unit_closed WHERE org_unit = circ_lib AND final_time::TIMESTAMPTZ between close_start AND close_end; + SELECT * INTO adjacent + FROM actor.org_unit_closed + WHERE org_unit = circ_lib + AND final_time::TIMESTAMPTZ BETWEEN evergreen.truncate_timestamptz_to_second(close_start) AND evergreen.truncate_timestamptz_to_second(close_end); + EXIT WHEN adjacent.id IS NULL; time_adjusted := TRUE; -- RAISE NOTICE 'recursing for closings with final_time: %',final_time; @@ -165,7 +169,7 @@ BEGIN 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 evergreen.truncate_timestamptz_to_second(circ.due_date) BETWEEN evergreen.truncate_timestamptz_to_second(closing.close_start) AND evergreen.truncate_timestamptz_to_second(closing.close_end) 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); @@ -180,7 +184,7 @@ BEGIN 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) + AND evergreen.truncate_timestamptz_to_second(res.end_time) BETWEEN evergreen.truncate_timestamptz_to_second(closing.close_start) AND evergreen.truncate_timestamptz_to_second(closing.close_end) ) WHERE NOT EXISTS (SELECT 1 FROM action.emergency_closing_reservation t WHERE t.emergency_closing = e_closing AND t.reservation = res.id); @@ -194,7 +198,7 @@ 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 evergreen.truncate_timestamptz_to_second(hold.shelf_expire_time) BETWEEN evergreen.truncate_timestamptz_to_second(closing.close_start) AND evergreen.truncate_timestamptz_to_second(closing.close_end) AND hold.fulfillment_time IS NULL AND hold.cancel_time IS NULL ) @@ -344,7 +348,7 @@ BEGIN AND b.btype = 1 AND NOT b.voided AND ( - b.billing_ts BETWEEN closing.close_start AND new_due_date::TIMESTAMPTZ + evergreen.truncate_timestamptz_to_second(b.billing_ts) BETWEEN evergreen.truncate_timestamptz_to_second(closing.close_start) AND evergreen.truncate_timestamptz_to_second(new_due_date::TIMESTAMPTZ) OR (extend_grace AND last_bill.billing_ts <= new_due_date::TIMESTAMPTZ + circ.grace_period) ) AND NOT EXISTS (SELECT 1 FROM money.account_adjustment a WHERE a.billing = b.id) @@ -434,7 +438,7 @@ BEGIN WHERE b.xact = res.id AND b.btype = 1 AND NOT b.voided - AND b.billing_ts BETWEEN closing.close_start AND new_due_date::TIMESTAMPTZ + AND evergreen.truncate_timestamptz_to_second(b.billing_ts) BETWEEN evergreen.truncate_timestamptz_to_second(closing.close_start) AND evergreen.truncate_timestamptz_to_second(new_due_date::TIMESTAMPTZ) AND NOT EXISTS (SELECT 1 FROM money.account_adjustment a WHERE a.billing = b.id) LOOP IF avoid_negative THEN diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.function.emergency_closing_time_normalization.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.emergency_closing_time_normalization.sql new file mode 100644 index 0000000000..11db797e22 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.emergency_closing_time_normalization.sql @@ -0,0 +1,334 @@ +BEGIN; + +CREATE OR REPLACE FUNCTION evergreen.truncate_timestamptz_to_second (TIMESTAMPTZ) RERTURNS TIMESTAMPTZ AS $$ + SELECT TIMESTAMP WITH TIME ZONE 'epoch' + TRUNC(EXTRACT(EPOCH FROM $1)) * INTERVAL '1 second'; +$$ LANGUAGE SQL STRICT STABLE; + +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 ) + RETURNS TIMESTAMPTZ AS $$ +DECLARE + day_number INT; + plus_days INT; + final_time TEXT; + time_adjusted BOOL; + hoo_open TIME WITHOUT TIME ZONE; + hoo_close TIME WITHOUT TIME ZONE; + adjacent actor.org_unit_closed%ROWTYPE; + breakout INT := 0; +BEGIN + + IF dow_count > 6 THEN + RETURN initial; + END IF; + + IF initial_time IS NULL THEN + initial_time := initial::TIME; + END IF; + + final_time := (evergreen.truncate_timestamptz_to_second(initial) + '1 second'::INTERVAL)::TEXT; + LOOP + breakout := breakout + 1; + + time_adjusted := FALSE; + + IF dow_count > 0 THEN -- we're recursing, so check for HOO closing + day_number := EXTRACT(ISODOW FROM final_time::TIMESTAMPTZ) - 1; + plus_days := 0; + FOR i IN 1..7 LOOP + EXECUTE 'SELECT dow_' || day_number || '_open, dow_' || day_number || '_close FROM actor.hours_of_operation WHERE id = $1' + INTO hoo_open, hoo_close + USING circ_lib; + + -- RAISE NOTICE 'initial time: %; dow: %; close: %',initial_time,day_number,hoo_close; + + IF hoo_close = '00:00:00' THEN -- bah ... I guess we'll check the next day + day_number := (day_number + 1) % 7; + plus_days := plus_days + 1; + time_adjusted := TRUE; + CONTINUE; + END IF; + + IF hoo_close IS NULL THEN -- no hours of operation ... assume no closing? + hoo_close := '23:59:59'; + END IF; + + EXIT; + END LOOP; + + final_time := DATE(final_time::TIMESTAMPTZ + (plus_days || ' days')::INTERVAL)::TEXT; + IF hoo_close <> '00:00:00' AND hourly THEN -- Not a day-granular circ + final_time := final_time||' '|| hoo_close; + ELSE + final_time := final_time||' 23:59:59'; + END IF; + END IF; + + -- Loop through other closings + LOOP + SELECT * INTO adjacent + FROM actor.org_unit_closed + WHERE org_unit = circ_lib + AND final_time::TIMESTAMPTZ BETWEEN evergreen.truncate_timestamptz_to_second(close_start) AND evergreen.truncate_timestamptz_to_second(close_end); + + EXIT WHEN adjacent.id IS NULL; + time_adjusted := TRUE; + -- RAISE NOTICE 'recursing for closings with final_time: %',final_time; + final_time := evergreen.find_next_open_time(circ_lib, adjacent.close_end::TIMESTAMPTZ, hourly, initial_time, dow_count + 1)::TEXT; + END LOOP; + + EXIT WHEN breakout > 100; + EXIT WHEN NOT time_adjusted; + + END LOOP; + + RETURN final_time; +END; +$$ LANGUAGE PLPGSQL; + +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 evergreen.truncate_timestamptz_to_second(circ.due_date) BETWEEN evergreen.truncate_timestamptz_to_second(closing.close_start) AND evergreen.truncate_timestamptz_to_second(closing.close_end) + 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 evergreen.truncate_timestamptz_to_second(res.end_time) BETWEEN evergreen.truncate_timestamptz_to_second(closing.close_start) AND evergreen.truncate_timestamptz_to_second(closing.close_end) + ) + 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 evergreen.truncate_timestamptz_to_second(hold.shelf_expire_time) BETWEEN evergreen.truncate_timestamptz_to_second(closing.close_start) AND evergreen.truncate_timestamptz_to_second(closing.close_end) + 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_circ ( circ_closing_entry INT ) + RETURNS BOOL AS $$ +DECLARE + circ action.circulation%ROWTYPE; + e_closing action.emergency_closing%ROWTYPE; + e_c_circ action.emergency_closing_circulation%ROWTYPE; + closing actor.org_unit_closed%ROWTYPE; + adjacent actor.org_unit_closed%ROWTYPE; + bill money.billing%ROWTYPE; + last_bill money.billing%ROWTYPE; + day_number INT; + hoo_close TIME WITHOUT TIME ZONE; + plus_days INT; + avoid_negative BOOL; + extend_grace BOOL; + new_due_date TEXT; +BEGIN + -- Gather objects involved + SELECT * INTO e_c_circ + FROM action.emergency_closing_circulation + WHERE id = circ_closing_entry; + + IF e_c_circ.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_circ.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 circ + FROM action.circulation + WHERE id = e_c_circ.circulation; + + -- Record the processing + UPDATE action.emergency_closing_circulation + SET original_due_date = circ.due_date, + process_time = NOW() + WHERE id = circ_closing_entry; + + UPDATE action.emergency_closing + SET last_update_time = NOW() + WHERE id = e_closing.id; + + SELECT value::BOOL INTO avoid_negative FROM actor.org_unit_ancestor_setting('bill.prohibit_negative_balance_on_overdues', circ.circ_lib); + SELECT value::BOOL INTO extend_grace FROM actor.org_unit_ancestor_setting('circ.grace.extend', circ.circ_lib); + + new_due_date := evergreen.find_next_open_time( closing.org_unit, circ.due_date, EXTRACT(EPOCH FROM circ.duration)::INT % 86400 > 0 )::TEXT; + UPDATE action.circulation SET due_date = new_due_date::TIMESTAMPTZ WHERE id = circ.id; + + -- Now, see if we need to get rid of some fines + SELECT * INTO last_bill + FROM money.billing b + WHERE b.xact = circ.id + AND NOT b.voided + AND b.btype = 1 + ORDER BY billing_ts DESC + LIMIT 1; + + FOR bill IN + SELECT * + FROM money.billing b + WHERE b.xact = circ.id + AND b.btype = 1 + AND NOT b.voided + AND ( + evergreen.truncate_timestamptz_to_second(b.billing_ts) BETWEEN evergreen.truncate_timestamptz_to_second(closing.close_start) AND evergreen.truncate_timestamptz_to_second(new_due_date::TIMESTAMPTZ) + OR (extend_grace AND last_bill.billing_ts <= new_due_date::TIMESTAMPTZ + circ.grace_period) + ) + AND NOT EXISTS (SELECT 1 FROM money.account_adjustment a WHERE a.billing = b.id) + ORDER BY billing_ts + LOOP + IF avoid_negative THEN + PERFORM FROM money.materialized_billable_xact_summary WHERE id = circ.id AND balanced_owd < bill.amount; + EXIT WHEN FOUND; -- We can't go negative, and voiding this bill would do that... + END IF; + + UPDATE money.billing + SET voided = TRUE, + void_time = NOW(), + note = COALESCE(note,'') || ' :: Voided by emergency closing handler' + WHERE id = bill.id; + END LOOP; + + RETURN TRUE; +END; +$$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION action.emergency_closing_stage_2_reservation ( res_closing_entry INT ) + RETURNS BOOL AS $$ +DECLARE + res booking.reservation%ROWTYPE; + e_closing action.emergency_closing%ROWTYPE; + e_c_res action.emergency_closing_reservation%ROWTYPE; + closing actor.org_unit_closed%ROWTYPE; + adjacent actor.org_unit_closed%ROWTYPE; + bill money.billing%ROWTYPE; + day_number INT; + hoo_close TIME WITHOUT TIME ZONE; + plus_days INT; + avoid_negative BOOL; + new_due_date TEXT; +BEGIN + -- Gather objects involved + SELECT * INTO e_c_res + FROM action.emergency_closing_reservation + WHERE id = res_closing_entry; + + IF e_c_res.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_res.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 res + FROM booking.reservation + WHERE id = e_c_res.reservation; + + IF res.pickup_lib IS NULL THEN -- Need to be far enough along to have a pickup lib + RETURN FALSE; + END IF; + + -- Record the processing + UPDATE action.emergency_closing_reservation + SET original_end_time = res.end_time, + process_time = NOW() + WHERE id = res_closing_entry; + + UPDATE action.emergency_closing + SET last_update_time = NOW() + WHERE id = e_closing.id; + + SELECT value::BOOL INTO avoid_negative FROM actor.org_unit_ancestor_setting('bill.prohibit_negative_balance_on_overdues', res.pickup_lib); + + new_due_date := evergreen.find_next_open_time( closing.org_unit, res.end_time, EXTRACT(EPOCH FROM res.booking_interval)::INT % 86400 > 0 )::TEXT; + UPDATE booking.reservation SET end_time = new_due_date::TIMESTAMPTZ WHERE id = res.id; + + -- Now, see if we need to get rid of some fines + FOR bill IN + SELECT * + FROM money.billing b + WHERE b.xact = res.id + AND b.btype = 1 + AND NOT b.voided + AND evergreen.truncate_timestamptz_to_second(b.billing_ts) BETWEEN evergreen.truncate_timestamptz_to_second(closing.close_start) AND evergreen.truncate_timestamptz_to_second(new_due_date::TIMESTAMPTZ) + AND NOT EXISTS (SELECT 1 FROM money.account_adjustment a WHERE a.billing = b.id) + LOOP + IF avoid_negative THEN + PERFORM FROM money.materialized_billable_xact_summary WHERE id = res.id AND balanced_owd < bill.amount; + EXIT WHEN FOUND; -- We can't go negative, and voiding this bill would do that... + END IF; + + UPDATE money.billing + SET voided = TRUE, + void_time = NOW(), + note = COALESCE(note,'') || ' :: Voided by emergency closing handler' + WHERE id = bill.id; + END LOOP; + + RETURN TRUE; +END; +$$ LANGUAGE PLPGSQL; + +COMMIT; -- 2.11.0