tuS6mzAk

1
2CREATE OR REPLACE FUNCTION weekday() RETURNS integer AS $$
3    BEGIN
4        RETURN extract(dow from now())::integer;
5    END;
6$$ LANGUAGE plpgsql;
7
8CREATE OR REPLACE FUNCTION closest_weekday(weekday integer, from_date timestamp with time zone) RETURNS TIMESTAMP AS $$
9    DECLARE
10        today INT;
11    BEGIN
12        today := extract(dow from from_date)::integer;
13        RETURN from_date + CONCAT(
14            CASE
15                WHEN today > weekday THEN 7 - today + weekday
16                ELSE weekday - today
17            END, ' day')::interval;
18    END;
19$$ LANGUAGE plpgsql;
20
21DROP FUNCTION IF EXISTS events_exact_termins;
22DROP TYPE events_exact_termins_out;
23
24CREATE TYPE events_exact_termins_out AS (
25    events_id INT,
26    firms_id INT,
27    date_from DATE,
28    date_to DATE,
29    time_from TIME WITHOUT TIME ZONE,
30    time_to TIME WITHOUT TIME ZONE,
31    exact_date DATE
32);
33
34CREATE OR REPLACE FUNCTION events_exact_termins(
35    event_id INT
36) RETURNS SETOF events_exact_termins_out AS
37    $func$
38        DECLARE
39            d_to TIMESTAMP WITH TIME ZONE;
40            stop_date TIMESTAMP WITH TIME ZONE;
41            event_record RECORD;
42        BEGIN
43
44            stop_date := (SELECT
45                    MAX(eft.date_to) AS stop_date
46                FROM events e
47                    JOIN events_firms ef on ef.events_id = e.id
48                    JOIN firms f on ef.firms_id = f.id
49                    LEFT JOIN events_firms_termin eft on ef.id = eft.event_firm_id
50                WHERE eft.date_from IS NOT NULL
51                    AND eft.date_to >= NOW()
52                    AND e.id = event_id
53                LIMIT 1)::timestamp;
54
55            d_to := stop_date;
56
57            IF NOW() + INTERVAL '7 DAY' <= stop_date THEN
58                stop_date := stop_date - INTERVAL '6 DAY';
59            ELSE
60                stop_date := stop_date - AGE(stop_date, NOW());
61            END IF;
62
63            WHILE stop_date >= NOW() LOOP
64                FOR event_record IN (
65                    SELECT
66                        e.id AS events_id,
67                        ef.firms_id AS firms_id,
68                        eft.date_from AS date_from,
69                        eft.date_to AS date_to,
70                        ett.time_from AS time_from,
71                        ett.time_to AS time_to,
72                        closest_weekday(etr.weekday, stop_date)::DATE as exact_date
73                    FROM events e
74                        JOIN events_firms ef on ef.events_id = e.id
75                        JOIN firms f on ef.firms_id = f.id
76                        LEFT JOIN events_firms_termin eft on ef.id = eft.event_firm_id
77                        LEFT JOIN event_termin_time ett on eft.id = ett.termin_id
78                        LEFT JOIN event_termin_repeat etr on eft.id = etr.termin_id
79                    WHERE eft.date_from IS NOT NULL
80                        AND eft.date_to >= now()
81                        AND etr.weekday IS NOT NULL
82                        AND closest_weekday(etr.weekday, stop_date) BETWEEN NOW() AND d_to
83                        AND closest_weekday(etr.weekday, stop_date) BETWEEN eft.date_from AND d_to
84                        AND e.id = event_id
85                ) LOOP
86                    RETURN NEXT event_record;
87                END LOOP;
88
89                IF stop_date - INTERVAL '7 DAY' > NOW() THEN
90                    stop_date := stop_date - INTERVAL '7 DAY';
91                ELSIF stop_date = NOW() THEN
92                    stop_date := stop_date - INTERVAL '1 DAY';
93                ELSE
94                    stop_date := stop_date - AGE(stop_date, NOW());
95                END IF;
96            END LOOP;
97
98            FOR event_record IN (
99                SELECT
100                    e.id AS events_id,
101                    ef.firms_id AS firms_id,
102                    eft.date_from AS date_from,
103                    eft.date_to AS date_to,
104                    ett.time_from AS time_from,
105                    ett.time_to AS time_to,
106                    eft.date_from::DATE as exact_date
107                FROM events e
108                JOIN events_firms ef on ef.events_id = e.id
109                JOIN firms f on ef.firms_id = f.id
110                LEFT JOIN events_firms_termin eft on ef.id = eft.event_firm_id
111                LEFT JOIN event_termin_time ett on eft.id = ett.termin_id
112                LEFT JOIN event_termin_repeat etr on eft.id = etr.termin_id
113                WHERE eft.date_from IS NOT NULL
114                    AND eft.date_from >= NOW()
115                    AND etr.weekday IS NOT NULL
116                    AND eft.date_to IS NULL
117                    AND e.id = event_id
118            ) LOOP
119                RETURN NEXT event_record;
120            END LOOP;
121        END
122    $func$
123LANGUAGE plpgsql;
124
125create table if not exists events_exact_termins
126(
127    id serial  not null constraint events_exact_termins_pk primary key,
128    events_id  integer not null,
129    firms_id   integer not null,
130    date_from  date,
131    date_to    date,
132    time_from  time,
133    time_to    time,
134    exact_date date
135);
136
137alter table events_exact_termins
138    owner to postgres;
139
140create or replace function recalc_events_exact_termins(event_id INT) returns INT
141    language plpgsql
142as
143$$
144BEGIN
145	  DELETE FROM events_exact_termins WHERE events_id = events_id;
146	  INSERT INTO events_exact_termins (events_id, firms_id, date_from, date_to, time_from, time_to, exact_date)
147        (SELECT DISTINCT * FROM events_exact_termins(event_id));
148	  RETURN event_id;
149END
150$$;
151
152SELECT recalc_events_exact_termins(116490); -- Prepocita nove terminy pre udalost
153SELECT DISTINCT * FROM events_exact_termins(116490) f order by f.exact_date; -- proste len select
154
155