· 5 years ago · Oct 15, 2020, 11:26 AM
1
2
3CREATE OR REPLACE FUNCTION weekday() RETURNS integer AS $$
4 BEGIN
5 RETURN extract(dow from now())::integer;
6 END;
7$$ LANGUAGE plpgsql;
8
9CREATE OR REPLACE FUNCTION closest_weekday(weekday integer, from_date timestamp with time zone) RETURNS TIMESTAMP AS $$
10 BEGIN
11 RETURN (
12 SELECT s.a as cw
13 FROM generate_series(from_date, from_date + INTERVAL '6 DAY', '1 DAY') s(a)
14 WHERE extract(dow from s.a) = weekday % 7
15 )::timestamp;
16 END;
17$$ LANGUAGE plpgsql;
18
19DROP FUNCTION IF EXISTS events_exact_termins;
20DROP TYPE events_exact_termins_out;
21
22CREATE TYPE events_exact_termins_out AS (
23 events_id INT,
24 firms_id INT,
25 date_from DATE,
26 date_to DATE,
27 time_from TIME WITHOUT TIME ZONE,
28 time_to TIME WITHOUT TIME ZONE,
29 exact_date DATE
30);
31
32CREATE OR REPLACE FUNCTION events_exact_termins(
33 event_id INT
34) RETURNS SETOF events_exact_termins_out AS
35 $func$
36 DECLARE
37 d_to TIMESTAMP WITH TIME ZONE;
38 stop_date TIMESTAMP WITH TIME ZONE;
39 event_record RECORD;
40 events RECORD;
41 BEGIN
42
43 d_to := (SELECT
44 MAX(eft.date_to) AS stop_date
45 FROM events e
46 JOIN events_firms ef on ef.events_id = e.id
47 JOIN firms f on ef.firms_id = f.id
48 LEFT JOIN events_firms_termin eft on ef.id = eft.event_firm_id
49 WHERE eft.date_from IS NOT NULL
50 AND eft.date_to >= NOW()
51 AND e.id = event_id
52 LIMIT 1)::timestamp;
53
54 FOR event_record IN (
55 SELECT
56 e.id AS events_id,
57 ef.firms_id AS firms_id,
58 eft.date_from AS date_from,
59 eft.date_to AS date_to,
60 ett.time_from AS time_from,
61 ett.time_to AS time_to,
62 etr.weekday AS weekday
63 FROM events e
64 JOIN events_firms ef on ef.events_id = e.id
65 JOIN firms f on ef.firms_id = f.id
66 LEFT JOIN events_firms_termin eft on ef.id = eft.event_firm_id
67 LEFT JOIN event_termin_time ett on eft.id = ett.termin_id
68 LEFT JOIN event_termin_repeat etr on eft.id = etr.termin_id
69 WHERE eft.date_from IS NOT NULL
70 AND eft.date_to >= now()
71 AND etr.weekday IS NOT NULL
72 AND e.id = event_id
73 ) LOOP
74
75 stop_date := d_to;
76 IF NOW() + INTERVAL '6 DAY' <= stop_date THEN
77 stop_date := stop_date - INTERVAL '6 DAY';
78 ELSE
79 stop_date := stop_date - AGE(stop_date, NOW());
80 END IF;
81
82 WHILE stop_date >= NOW() LOOP
83 IF (closest_weekday(event_record.weekday, stop_date) BETWEEN NOW() AND d_to)
84 AND (closest_weekday(event_record.weekday, stop_date) BETWEEN event_record.date_from AND d_to)
85 THEN
86 RETURN NEXT (
87 event_record.events_id,
88 event_record.firms_id,
89 event_record.date_from,
90 event_record.date_to,
91 event_record.time_from,
92 event_record.time_to,
93 closest_weekday(event_record.weekday, stop_date)::DATE
94 );
95 END IF;
96
97 IF stop_date - INTERVAL '6 DAY' > NOW() THEN
98 stop_date := stop_date - INTERVAL '6 DAY';
99 ELSIF stop_date = NOW() THEN
100 stop_date := stop_date - INTERVAL '1 DAY';
101 ELSE
102 stop_date := stop_date - AGE(stop_date, NOW());
103 END IF;
104 END LOOP;
105 END LOOP;
106
107 FOR event_record IN (
108 SELECT
109 e.id AS events_id,
110 ef.firms_id AS firms_id,
111 eft.date_from AS date_from,
112 eft.date_to AS date_to,
113 ett.time_from AS time_from,
114 ett.time_to AS time_to,
115 eft.date_from::DATE as exact_date
116 FROM events e
117 JOIN events_firms ef on ef.events_id = e.id
118 JOIN firms f on ef.firms_id = f.id
119 LEFT JOIN events_firms_termin eft on ef.id = eft.event_firm_id
120 LEFT JOIN event_termin_time ett on eft.id = ett.termin_id
121 WHERE eft.date_from IS NOT NULL
122 AND eft.date_from >= NOW()
123 AND eft.date_to IS NULL
124 AND e.id = event_id
125 ) LOOP
126 RETURN NEXT event_record;
127 END LOOP;
128 END
129 $func$
130LANGUAGE plpgsql;
131
132create table if not exists events_exact_termins
133(
134 id serial not null constraint events_exact_termins_pk primary key,
135 events_id integer not null,
136 firms_id integer not null,
137 date_from date,
138 date_to date,
139 time_from time,
140 time_to time,
141 exact_date date
142);
143
144alter table events_exact_termins
145 owner to postgres;
146
147create index if not exists events_exact_termins_events_id_index
148 on events_exact_termins (events_id desc);
149
150create index if not exists events_exact_termins_exact_date_index
151 on events_exact_termins (exact_date desc);
152
153create index if not exists events_exact_termins_firms_id_index
154 on events_exact_termins (firms_id desc);
155
156
157create or replace function recalc_events_exact_termins(event_id INT) returns INT
158 language plpgsql
159as
160$$
161BEGIN
162 DELETE FROM events_exact_termins WHERE events_id = event_id OR exact_date < now();
163 INSERT INTO events_exact_termins (events_id, firms_id, date_from, date_to, time_from, time_to, exact_date)
164 (SELECT DISTINCT * FROM events_exact_termins(event_id));
165 RETURN event_id;
166END
167$$;
168
169CREATE OR REPLACE FUNCTION events_firms_termin_change() RETURNS TRIGGER
170 LANGUAGE plpgsql
171AS $func$
172BEGIN
173 IF OLD.id IS NULL THEN
174 PERFORM recalc_events_exact_termins(
175 (
176 SELECT
177 e.id AS events_id
178 FROM events e
179 JOIN events_firms ef on ef.events_id = e.id
180 JOIN firms f on ef.firms_id = f.id
181 JOIN events_firms_termin eft on ef.id = eft.event_firm_id
182 WHERE eft.id = NEW.id
183 LIMIT 1
184 )::integer
185 );
186 ELSE
187 PERFORM recalc_events_exact_termins(
188 (
189 SELECT
190 e.id AS events_id
191 FROM events e
192 JOIN events_firms ef on ef.events_id = e.id
193 JOIN firms f on ef.firms_id = f.id
194 JOIN events_firms_termin eft on ef.id = eft.event_firm_id
195 WHERE eft.id = OLD.id
196 LIMIT 1
197 )::integer
198 );
199 END IF;
200
201 RETURN NULL;
202END;
203$func$;
204
205DROP TRIGGER IF EXISTS events_firms_termin_change ON events_firms_termin;
206CREATE TRIGGER events_firms_termin_change
207 AFTER UPDATE OR INSERT OR DELETE
208 ON events_firms_termin
209 FOR EACH ROW
210 EXECUTE PROCEDURE events_firms_termin_change();
211
212CREATE OR REPLACE FUNCTION events_termin_time_change() RETURNS TRIGGER
213 LANGUAGE plpgsql
214AS $func$
215BEGIN
216 IF OLD.termin_id IS NULL THEN
217 PERFORM recalc_events_exact_termins(
218 (
219 SELECT
220 e.id AS events_id
221 FROM events e
222 JOIN events_firms ef on ef.events_id = e.id
223 JOIN firms f on ef.firms_id = f.id
224 JOIN events_firms_termin eft on ef.id = eft.event_firm_id
225 JOIN event_termin_time ett on eft.id = ett.termin_id
226 WHERE ett.termin_id = NEW.termin_id
227 LIMIT 1
228 )::integer
229 );
230 ELSE
231 PERFORM recalc_events_exact_termins(
232 (
233 SELECT
234 e.id AS events_id
235 FROM events e
236 JOIN events_firms ef on ef.events_id = e.id
237 JOIN firms f on ef.firms_id = f.id
238 JOIN events_firms_termin eft on ef.id = eft.event_firm_id
239 JOIN event_termin_time ett on eft.id = ett.termin_id
240 WHERE ett.termin_id = OLD.termin_id
241 LIMIT 1
242 )::integer
243 );
244 END IF;
245
246 RETURN NULL;
247END;
248$func$;
249
250DROP TRIGGER IF EXISTS events_termin_time_change ON event_termin_time;
251CREATE TRIGGER events_termin_time_change
252 AFTER UPDATE OR INSERT OR DELETE
253 ON event_termin_time
254 FOR EACH ROW
255 EXECUTE PROCEDURE events_termin_time_change();
256
257CREATE OR REPLACE FUNCTION events_termin_repeat_change() RETURNS TRIGGER
258 LANGUAGE plpgsql
259AS $func$
260BEGIN
261 IF OLD.termin_id IS NULL THEN
262 PERFORM recalc_events_exact_termins(
263 (
264 SELECT
265 e.id AS events_id
266 FROM events e
267 JOIN events_firms ef on ef.events_id = e.id
268 JOIN firms f on ef.firms_id = f.id
269 JOIN events_firms_termin eft on ef.id = eft.event_firm_id
270 JOIN event_termin_repeat etr on eft.id = etr.termin_id
271 WHERE etr.termin_id = NEW.termin_id
272 LIMIT 1
273 )::integer
274 );
275 ELSE
276 PERFORM recalc_events_exact_termins(
277 (
278 SELECT
279 e.id AS events_id
280 FROM events e
281 JOIN events_firms ef on ef.events_id = e.id
282 JOIN firms f on ef.firms_id = f.id
283 JOIN events_firms_termin eft on ef.id = eft.event_firm_id
284 JOIN event_termin_repeat etr on eft.id = etr.termin_id
285 WHERE etr.termin_id = OLD.termin_id
286 LIMIT 1
287 )::integer
288 );
289 END IF;
290
291 RETURN NULL;
292END;
293$func$;
294
295DROP TRIGGER IF EXISTS events_termin_repeat_change ON event_termin_repeat;
296CREATE TRIGGER events_termin_repeat_change
297 AFTER UPDATE OR INSERT OR DELETE
298 ON event_termin_repeat
299 FOR EACH ROW
300 EXECUTE PROCEDURE events_termin_repeat_change();
301
302DROP FUNCTION recalc_all_event_termins;
303CREATE OR REPLACE FUNCTION recalc_all_event_termins() RETURNS INT AS
304 $func$
305 DECLARE
306 event_record RECORD;
307 BEGIN
308 FOR event_record IN (
309 SELECT DISTINCT
310 e.id AS events_id
311 FROM events e
312 JOIN events_firms ef on ef.events_id = e.id
313 JOIN firms f on ef.firms_id = f.id
314 LEFT JOIN events_firms_termin eft on ef.id = eft.event_firm_id
315 WHERE eft.date_from IS NOT NULL
316 AND eft.date_to >= now() OR (eft.date_from >= now() AND eft.date_to IS NULL)
317 ) LOOP
318 PERFORM recalc_events_exact_termins(event_record.events_id);
319 END LOOP;
320 RETURN 1;
321 END
322 $func$
323LANGUAGE plpgsql;
324
325SELECT recalc_all_event_termins();
326
327SELECT DISTINCT * FROM events_exact_termins f order by f.exact_date; -- proste len select
328
329
330
331