From ec2cb1a293749522772f1bf967d3d2e0acb0920d Mon Sep 17 00:00:00 2001
From: Dan Wells <dbw2@calvin.edu>
Date: Mon, 12 Feb 2018 10:48:14 -0500
Subject: [PATCH] LP#1748924 Upgrade script for expanding billing timestamps
Signed-off-by: Dan Wells <dbw2@calvin.edu>
Signed-off-by: Jeff Godin <jgodin@tadl.org>
---
.../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.11.0