From ec2cb1a293749522772f1bf967d3d2e0acb0920d Mon Sep 17 00:00:00 2001 From: Dan Wells Date: Mon, 12 Feb 2018 10:48:14 -0500 Subject: [PATCH] LP#1748924 Upgrade script for expanding billing timestamps Signed-off-by: Dan Wells Signed-off-by: Jeff Godin --- .../XXXX.data.expand_billing_timestamps.sql | 45 +++++++++++++++++++ 1 file changed, 45 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.data.expand_billing_timestamps.sql diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.data.expand_billing_timestamps.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.data.expand_billing_timestamps.sql new file mode 100644 index 0000000000..e88b4cc3af --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.data.expand_billing_timestamps.sql @@ -0,0 +1,45 @@ +BEGIN; + +--SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +ALTER TABLE money.billing + ADD COLUMN create_date TIMESTAMP WITH TIME ZONE, + ADD COLUMN period_start TIMESTAMP WITH TIME ZONE, + ADD COLUMN period_end TIMESTAMP WITH TIME ZONE; + +--Limit to btype=1 / 'Overdue Materials' +--Update day-granular fines first (i.e. 24 hour, 1 day, 2 day, etc., all of which are multiples of 86400 seconds), and simply remove the time portion of timestamp +UPDATE money.billing mb + SET period_start = date_trunc('day', billing_ts), period_end = date_trunc('day', billing_ts) + (ac.fine_interval - '1 second') + FROM action.circulation ac +WHERE mb.xact = ac.id + AND mb.btype = 1 + AND (EXTRACT(EPOCH FROM ac.fine_interval))::integer % 86400 = 0; + +--Update fines for non-day intervals +UPDATE money.billing mb + SET period_start = billing_ts - ac.fine_interval + interval '1 sec', period_end = billing_ts + FROM action.circulation ac +WHERE mb.xact = ac.id + AND mb.btype = 1 + AND (EXTRACT(EPOCH FROM ac.fine_interval))::integer % 86400 > 0; + +SET CONSTRAINTS ALL IMMEDIATE; +UPDATE money.billing SET create_date = COALESCE(period_start, billing_ts); + +ALTER TABLE money.billing ALTER COLUMN create_date SET DEFAULT NOW(); +ALTER TABLE money.billing ALTER COLUMN create_date SET NOT NULL; + +CREATE INDEX m_b_create_date_idx ON money.billing (create_date); +CREATE INDEX m_b_period_start_idx ON money.billing (period_start); +CREATE INDEX m_b_period_end_idx ON money.billing (period_end); + +CREATE OR REPLACE FUNCTION money.maintain_billing_ts () RETURNS TRIGGER AS $$ +BEGIN + NEW.billing_ts := COALESCE(NEW.period_end, NEW.create_date); + RETURN NEW; +END; +$$ LANGUAGE PLPGSQL; +CREATE TRIGGER maintain_billing_ts_tgr BEFORE INSERT OR UPDATE ON money.billing FOR EACH ROW EXECUTE PROCEDURE money.maintain_billing_ts(); + +COMMIT; -- 2.43.2