3 --SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
5 ALTER TABLE money.billing
6 ADD COLUMN create_date TIMESTAMP WITH TIME ZONE,
7 ADD COLUMN period_start TIMESTAMP WITH TIME ZONE,
8 ADD COLUMN period_end TIMESTAMP WITH TIME ZONE;
10 --Limit to btype=1 / 'Overdue Materials'
11 --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
12 UPDATE money.billing mb
13 SET period_start = date_trunc('day', billing_ts), period_end = date_trunc('day', billing_ts) + (ac.fine_interval - '1 second')
14 FROM action.circulation ac
17 AND (EXTRACT(EPOCH FROM ac.fine_interval))::integer % 86400 = 0;
19 --Update fines for non-day intervals
20 UPDATE money.billing mb
21 SET period_start = billing_ts - ac.fine_interval + interval '1 sec', period_end = billing_ts
22 FROM action.circulation ac
25 AND (EXTRACT(EPOCH FROM ac.fine_interval))::integer % 86400 > 0;
27 SET CONSTRAINTS ALL IMMEDIATE;
28 UPDATE money.billing SET create_date = COALESCE(period_start, billing_ts);
30 ALTER TABLE money.billing ALTER COLUMN create_date SET DEFAULT NOW();
31 ALTER TABLE money.billing ALTER COLUMN create_date SET NOT NULL;
33 CREATE INDEX m_b_create_date_idx ON money.billing (create_date);
34 CREATE INDEX m_b_period_start_idx ON money.billing (period_start);
35 CREATE INDEX m_b_period_end_idx ON money.billing (period_end);
37 CREATE OR REPLACE FUNCTION money.maintain_billing_ts () RETURNS TRIGGER AS $$
39 NEW.billing_ts := COALESCE(NEW.period_end, NEW.create_date);
43 CREATE TRIGGER maintain_billing_ts_tgr BEFORE INSERT OR UPDATE ON money.billing FOR EACH ROW EXECUTE PROCEDURE money.maintain_billing_ts();