From d261cbfbec49ca16021b6c4bf1c77e3f0750e898 Mon Sep 17 00:00:00 2001 From: Remington Steed Date: Thu, 29 Jan 2015 16:12:08 -0500 Subject: [PATCH] LP#1422379 Upgrade Script for moved money.billing timestamps Signed-off-by: Remington Steed Signed-off-by: Dan Wells --- .../XXXX.data.move_fine_timestamps_back.sql | 38 ++++++++++++++++++++++ 1 file changed, 38 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.data.move_fine_timestamps_back.sql diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.data.move_fine_timestamps_back.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.data.move_fine_timestamps_back.sql new file mode 100644 index 0000000000..659c1949d0 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.data.move_fine_timestamps_back.sql @@ -0,0 +1,38 @@ +BEGIN; + +--SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +--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 billing_ts = date_trunc('day', 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; + +UPDATE money.materialized_billable_xact_summary mmbxs + SET last_billing_ts = date_trunc('day', last_billing_ts) + FROM action.circulation ac +WHERE mmbxs.id = ac.id + AND mmbxs.last_billing_type = 'Overdue materials' + AND mmbxs.xact_type = 'circulation' + AND (EXTRACT(EPOCH FROM ac.fine_interval))::integer % 86400 = 0; + +--Update fines for non-day intervals +UPDATE money.billing mb + SET billing_ts = billing_ts - ac.fine_interval + interval '1 sec' + FROM action.circulation ac +WHERE mb.xact = ac.id + AND mb.btype = 1 + AND (EXTRACT(EPOCH FROM ac.fine_interval))::integer % 86400 > 0; + +UPDATE money.materialized_billable_xact_summary mmbxs + SET last_billing_ts = last_billing_ts - ac.fine_interval + interval '1 sec' + FROM action.circulation ac +WHERE mmbxs.id = ac.id + AND mmbxs.last_billing_type = 'Overdue materials' + AND mmbxs.xact_type = 'circulation' + AND (EXTRACT(EPOCH FROM ac.fine_interval))::integer % 86400 > 0; + +COMMIT; -- 2.11.0