From 7a19078a34ff71b2f8cf2329fb66e83e2de36e90 Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Mon, 24 Jul 2017 10:13:31 -0400 Subject: [PATCH] LP#1705524: Adjust day-granular due date pushing Now that due dates are globally stored in the configured timezone of the circulating library, the automatic adjustment to day-granular due dates needs to take those timezones into account. An optional SQL command is provided by the upgrade script to retroactively adjust existing due dates after library configuration is complete. Signed-off-by: Mike Rylander Signed-off-by: Tina Ji --- Open-ILS/src/sql/Pg/090.schema.action.sql | 15 ++++++- .../sql/Pg/upgrade/XXXX.data.tz_org_setting.sql | 50 ++++++++++++++++++++++ 2 files changed, 63 insertions(+), 2 deletions(-) diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql index 11ee4c3865..14ec176a1f 100644 --- a/Open-ILS/src/sql/Pg/090.schema.action.sql +++ b/Open-ILS/src/sql/Pg/090.schema.action.sql @@ -173,9 +173,20 @@ CREATE TRIGGER mat_summary_change_tgr AFTER UPDATE ON action.circulation FOR EAC CREATE TRIGGER mat_summary_remove_tgr AFTER DELETE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_delete (); CREATE OR REPLACE FUNCTION action.push_circ_due_time () RETURNS TRIGGER AS $$ +DECLARE + proper_tz TEXT := COALESCE( + oils_json_to_text(( + SELECT value + FROM actor.org_unit_ancestor_setting('lib.timezone',NEW.circ_lib) + LIMIT 1 + )), + CURRENT_SETTING('timezone') + ); BEGIN - IF (EXTRACT(EPOCH FROM NEW.duration)::INT % EXTRACT(EPOCH FROM '1 day'::INTERVAL)::INT) = 0 THEN - NEW.due_date = (NEW.due_date::DATE + '1 day'::INTERVAL - '1 second'::INTERVAL)::TIMESTAMPTZ; + + IF (EXTRACT(EPOCH FROM NEW.duration)::INT % EXTRACT(EPOCH FROM '1 day'::INTERVAL)::INT) = 0 -- day-granular duration + AND SUBSTRING((NEW.due_date AT TIME ZONE proper_tz)::TIME::TEXT FROM 1 FOR 8) <> '23:59:59' THEN -- has not yet been pushed + NEW.due_date = ((NEW.due_date AT TIME ZONE proper_tz)::DATE + '1 day'::INTERVAL - '1 second'::INTERVAL) || ' ' || proper_tz; END IF; RETURN NEW; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.data.tz_org_setting.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.data.tz_org_setting.sql index 53b1b1a376..4d7f24c9bd 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.data.tz_org_setting.sql +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.data.tz_org_setting.sql @@ -23,5 +23,55 @@ UPDATE actor.org_unit_closed SET full_day = TRUE AND SUBSTRING(close_start::time::text FROM 1 FOR 8) = '00:00:00' AND SUBSTRING(close_end::time::text FROM 1 FOR 8) = '23:59:59'; +CREATE OR REPLACE FUNCTION action.push_circ_due_time () RETURNS TRIGGER AS $$ +DECLARE + proper_tz TEXT := COALESCE( + oils_json_to_text(( + SELECT value + FROM actor.org_unit_ancestor_setting('lib.timezone',NEW.circ_lib) + LIMIT 1 + )), + CURRENT_SETTING('timezone') + ); +BEGIN + + IF (EXTRACT(EPOCH FROM NEW.duration)::INT % EXTRACT(EPOCH FROM '1 day'::INTERVAL)::INT) = 0 -- day-granular duration + AND SUBSTRING((NEW.due_date AT TIME ZONE proper_tz)::TIME::TEXT FROM 1 FOR 8) <> '23:59:59' THEN -- has not yet been pushed + NEW.due_date = ((NEW.due_date AT TIME ZONE proper_tz)::DATE + '1 day'::INTERVAL - '1 second'::INTERVAL) || ' ' || proper_tz; + END IF; + + RETURN NEW; +END; +$$ LANGUAGE PLPGSQL; + COMMIT; +\qecho The following query will adjust all historical, unaged circulations so +\qecho that if their due date field pushed to the end of the day, it is done +\qecho in the circulating library's time zone, and not the server time zone. +\qecho +\qecho It is safe to run this after any change to library time zones. +\qecho +\qecho Running this is not required, as no code before this change has +\qecho depended on the time string of '23:59:59'. It is also not necessary +\qecho if all of your libraries are in the same time zone, and that time zone +\qecho is the same as the databases configured time zone. +\qecho +\qecho DO $$ +\qecho declare +\qecho new_tz text; +\qecho ou_id int; +\qecho begin +\qecho for ou_id in select id from actor.org_unit loop +\qecho for new_tz in select oils_json_to_text(value) from actor.org_unit_ancestor_setting('lib.timezone',ou_id) loop +\qecho if new_tz is not null then +\qecho update action.circulation +\qecho set due_date = (due_date::timestamp || ' ' || new_tz)::timestamptz +\qecho where circ_lib = ou_id +\qecho and substring((due_date at time zone new_tz)::time::text from 1 for 8) <> '23:59:59'; +\qecho end if; +\qecho end loop; +\qecho end loop; +\qecho end; +\qecho $$; +\qecho -- 2.11.0