· 8 years ago · Jun 08, 2018, 09:10 AM
1DELIMITER $$
2
3DROP PROCEDURE IF EXISTS `sp_remove_time` $$
4# from_date and to_date it's date range
5# weekday_start - number of weekday start ex. 1 - Nd, 2 - Pn etc
6# weekday_end - number of weekday end ex. 1 - Nd, 2 - Pn etc
7
8# start_time - is connected with weekday_start
9# end_time - is connected with weekday_end
10CREATE PROCEDURE `sp_remove_time`(
11 IN from_date DATETIME,
12 IN to_date DATETIME,
13 IN weekday_start INT,
14 IN weekday_end INT,
15 IN start_time TIME,
16 IN end_time TIME
17)
18#RETURNS DECIMAL(10, 2)
19BEGIN
20
21 DECLARE fr_date DATETIME;
22 DECLARE value_to_remove INT;
23 DECLARE inverval_minutes INT;
24 DECLARE wk INT;
25 DECLARE need_reduce INT;
26 DECLARE full_day_seconds INT;
27 DECLARE dts TIME; # czas poczatkowej daty (date_time_start)
28 DECLARE dte TIME; # czas koncowej daty (date_time_end)zink2_velopadma_05_23
29
30 SET fr_date = from_date;
31
32 # base minutes which we will reduce interval time
33 SET value_to_remove = 0;
34
35 # total seconds of date interval
36 SET inverval_minutes = TIME_TO_SEC(TIMEDIFF(to_date, from_date));
37
38 # full day seconds
39 SET full_day_seconds = 86400;
40
41 DROP TABLE temp;
42 CREATE TEMPORARY TABLE IF NOT EXISTS temp (
43 dt datetime,
44 remove_time DECIMAL(10, 2),
45 r VARCHAR(255) NULL
46 );
47
48 # let's loop over date range
49 # until from_date is less than to_date
50 WHILE DATE(fr_date) <= DATE(to_date) DO
51
52 SET need_reduce = -1;
53 # from_date weekday number
54 SET wk = DAYOFWEEK(fr_date);
55
56 # let's check if we need reduce time
57 # we have to know if current weekday is in weekday_start and weekday_end range
58 if weekday_start >= weekday_end THEN
59 if wk = weekday_start or wk = weekday_end THEN
60 SET need_reduce = 1;
61 ELSEIF wk < weekday_start and wk < weekday_end THEN
62 SET need_reduce = 1;
63 ELSEIF wk > weekday_start and wk > weekday_end THEN
64 SET need_reduce = 1;
65 END IF;
66 ELSEIF weekday_start <= weekday_end THEN
67 IF wk >= weekday_start and wk <= weekday_end THEN
68 SET need_reduce = 1;
69 END IF;
70 END IF;
71
72 # if we need reduce time let's
73 # calc how much time we should reduce
74 IF need_reduce = 1 THEN
75
76 SET dts = TIME(from_date);
77 SET dte = TIME(to_date);
78
79 # jeżeli obie daty są takie same należy sprawdzić przedział z zakresu
80 # wykluczenia dni czy dane godziny znajdujÄ… siÄ™ w odpowiednim przedziale
81 IF DATE(from_date) = DATE(to_date) THEN
82
83 # sprawdzamy czy wykluczany dzień
84 if weekday_start = weekday_end THEN
85
86 IF dts < start_time and dte < end_time and dte > start_time THEN
87 SET value_to_remove = value_to_remove + TIME_TO_SEC(TIMEDIFF(dte, start_time));
88 INSERT INTO temp SET dt = from_date, r = 'from_date = to_date, dts < start_time and dte < end_time and dte > start_time', remove_time = TIME_TO_SEC(TIMEDIFF(dte, start_time));
89 ELSEIF dts <= start_time and dte > end_time THEN
90 SET value_to_remove = value_to_remove + TIME_TO_SEC(TIMEDIFF(end_time, start_time));
91 INSERT INTO temp SET dt = from_date, r = 'from_date = to_date, dts <= start_time and dte > end_time', remove_time = TIME_TO_SEC(TIMEDIFF(end_time, start_time));
92 ELSEIF dts < start_time and dte > start_time THEN
93 SET value_to_remove = value_to_remove + TIME_TO_SEC(TIMEDIFF(dte, start_time));
94 INSERT INTO temp SET dt = from_date, r = 'from_date = to_date, dts < start_time and dte > start_time', remove_time = TIME_TO_SEC(TIMEDIFF(dte, start_time));
95 ELSEIF dts >= start_time and dte <= end_time THEN
96 SET value_to_remove = value_to_remove + TIME_TO_SEC(TIMEDIFF(dte, dts));
97 INSERT INTO temp SET dt = from_date, r = 'from_date = to_date, dts >= start_time and dte <= end_time', remove_time = TIME_TO_SEC(TIMEDIFF(dte, dts));
98 ELSEIF dts > start_time and dte > end_time and dts < end_time THEN
99 SET value_to_remove = value_to_remove + TIME_TO_SEC(TIMEDIFF(end_time, dts));
100 INSERT INTO temp SET dt = from_date, r = 'from_date = to_date, dts > start_time and dte > end_time and dts < end_time', remove_time = TIME_TO_SEC(TIMEDIFF(end_time, dts));
101 END IF;
102
103 ELSEIF wk = weekday_start THEN
104
105 IF dts >= start_time and dte <= start_time THEN
106 # wykluczam roznice dts i dte
107 SET value_to_remove = value_to_remove + TIME_TO_SEC(TIMEDIFF(dts, dte));
108 INSERT INTO temp SET dt = from_date, r = 'from_date = to_date, wk = weekday_start, dts >= start_time and dte <= start_time', remove_time = TIME_TO_SEC(TIMEDIFF(dts, dte));
109 ELSEIF dts >= start_time and dte > start_time THEN
110 SET value_to_remove = value_to_remove + TIME_TO_SEC(TIMEDIFF(dte, dts));
111 INSERT INTO temp SET dt = from_date, r = 'from_date = to_date, wk = weekday_start, dts >= start_time and dte > start_time', remove_time = TIME_TO_SEC(TIMEDIFF(dte, dts));
112 ELSEIF dts < start_time and dte > start_time THEN
113 # wykluczam roznice dts i 00:00
114 SET value_to_remove = value_to_remove + TIME_TO_SEC(TIMEDIFF(dte, start_time));
115 INSERT INTO temp SET dt = from_date, r = 'from_date = to_date, wk = weekday_start, dts < start_time and dte > start_time', remove_time = TIME_TO_SEC(TIMEDIFF(dte, start_time));
116 END IF;
117
118 ELSEIF wk = weekday_end THEN
119 IF dts < end_time and dte > end_time THEN
120 SET value_to_remove = value_to_remove + TIME_TO_SEC(TIMEDIFF(end_time, dts));
121 INSERT INTO temp SET dt = from_date, r = 'from_date = to_date, wk = weekday_end, dts < end_time and dte > end_time', remove_time = TIME_TO_SEC(TIMEDIFF(end_time, dts));
122 ELSEIF dte <= end_time THEN
123 SET value_to_remove = value_to_remove + TIME_TO_SEC(TIMEDIFF(dte, dts));
124 INSERT INTO temp SET dt = from_date, r = 'from_date = to_date, wk = weekday_end, dte <= end_time', remove_time = TIME_TO_SEC(TIMEDIFF(dte, dts));
125 END IF;
126 ELSE
127 SET value_to_remove = value_to_remove + TIME_TO_SEC(TIMEDIFF(dte, dts));
128 INSERT INTO temp SET dt = from_date, r = 'from_date = to_date, wk = weekday_end, else', remove_time = TIME_TO_SEC(TIMEDIFF(dte, dts));
129 END IF;
130
131 ELSEIF DATE(fr_date) = DATE(from_date) and wk = 1 THEN
132 # jeżeli sprawdzana data jest początkiem interwału
133 IF dts >= start_time THEN
134 # jeżeli czas rozpoczęcia jest większy od wykluczanego
135 # musimy wykluczyć czas od czasu rozpoczęcia np.
136 # dts = 23:00, start_time = 22:00, wykluczamy czas od 23 do północy
137 SET value_to_remove = value_to_remove + TIME_TO_SEC(TIMEDIFF('23:59:59', dts)) + 1;
138 INSERT INTO temp SET dt = from_date, r = 'fr_date = from_date wk = 1, dts >= start_time', remove_time = TIME_TO_SEC(TIMEDIFF('23:59:59', dts)) + 1;
139 ELSE
140 # w przeciwny wypadku wykluczamy od start_time gdzie start_time to 22:00
141 SET value_to_remove = value_to_remove + TIME_TO_SEC(TIMEDIFF(start_time, dts));
142 INSERT INTO temp SET dt = from_date, r = 'fr_date = from_date wk = 1, else', remove_time = TIME_TO_SEC(TIMEDIFF(start_time, dts));
143 END IF;
144 ELSEIF DATE(fr_date) = DATE(from_date) and wk = 6 THEN
145 # jeżeli sprawdzana data jest początkiem interwału
146 IF dts >= start_time THEN
147 # jeżeli czas rozpoczęcia jest większy od wykluczanego
148 # musimy wykluczyć czas od czasu rozpoczęcia np.
149 # dts = 23:00, start_time = 22:00, wykluczamy czas od 23 do północy
150 SET value_to_remove = value_to_remove + TIME_TO_SEC(TIMEDIFF('23:59:59', dts)) + 1;
151 INSERT INTO temp SET dt = from_date, r = 'fr_date = from_date wk = 6', remove_time = TIME_TO_SEC(TIMEDIFF('23:59:59', dts)) + 1;
152 ELSE
153 # w przeciwny wypadku wykluczamy od start_time gdzie start_time to 22:00
154 SET value_to_remove = value_to_remove + TIME_TO_SEC(TIMEDIFF('23:59:59', start_time)) + 1;
155 INSERT INTO temp SET dt = from_date, r = 'else fr_date = from_date wk = 6', remove_time = TIME_TO_SEC(TIMEDIFF('23:59:59', start_time)) + 1;
156 END IF;
157 ELSEIF DATE(fr_date) = DATE(to_date) and wk = 1 THEN
158 # jeżeli sprawdzana data jest końcem interwału
159 IF dte >= end_time THEN
160 # jeżeli czas zakończenia interwału jest większy od wykluczanego
161 # np. dte = 23:00, end_time = 22:00 wówczas wykluczamy od północy do 22:00
162 SET value_to_remove = value_to_remove + TIME_TO_SEC(TIMEDIFF(end_time, '00:00'));
163 INSERT INTO temp SET dt = fr_date, r = 'fr_date = to_date wk = 1', remove_time = TIME_TO_SEC(TIMEDIFF(end_time, '00:00'));
164 ELSE
165 # w przeciwnym wypadku wykluczamy czas do czasu zakończenia
166 # np dte = 14:00, end_time = 22:00 wówczas wykluczamy od północy do 14:00
167 SET value_to_remove = value_to_remove + TIME_TO_SEC(TIMEDIFF(dte, '00:00'));
168 INSERT INTO temp SET dt = fr_date, r = 'fr_date = to_date wk = 1', remove_time = TIME_TO_SEC(TIMEDIFF(dte, '00:00'));
169 END IF;
170 ELSEIF wk = 7 and DATE(from_date) != DATE(fr_date) and DATE(to_date) != DATE(fr_date) THEN
171 SET value_to_remove = value_to_remove + full_day_seconds;
172 INSERT INTO temp SET dt = fr_date, r = 'wk = 7 and DAYOFWEEK(from_date) != 7 and DAYOFWEEK(to_date) != 7', remove_time = full_day_seconds;
173 ELSE
174 IF wk = 7 THEN
175 IF DATE(fr_date) = DATE(to_date) THEN
176 SET value_to_remove = value_to_remove + TIME_TO_SEC(TIMEDIFF(dte, '00:00'));
177 INSERT INTO temp SET dt = fr_date, r = 'wk = 7 DATE(fr_date) = DATE(to_date)', remove_time = TIME_TO_SEC(TIMEDIFF(dte, '00:00'));
178 ELSE
179 SET value_to_remove = value_to_remove + TIME_TO_SEC(TIMEDIFF('23:59:59', dts)) + 1;
180 INSERT INTO temp SET dt = fr_date, r = 'wk = 7 else', remove_time = TIME_TO_SEC(TIMEDIFF('23:59:59', dts)) + 1;
181 END IF;
182 ELSE
183 SET value_to_remove = value_to_remove;
184 IF wk = 6 and dts >= end_time THEN
185 SET value_to_remove = value_to_remove + TIME_TO_SEC(TIMEDIFF('23:59:59', dts)) + 1;
186 INSERT INTO temp SET dt = fr_date, r = 'else, wk = 6 and dts > end_time', remove_time = TIME_TO_SEC(TIMEDIFF('23:59:59', dts)) + 1;
187 ELSEIF wk = 6 and dts < end_time THEN
188 SET value_to_remove = value_to_remove + TIME_TO_SEC(TIMEDIFF('23:59:59', end_time)) + 1;
189 INSERT INTO temp SET dt = fr_date, r = 'else, wk = 6 and dts < end_time', remove_time = TIME_TO_SEC(TIMEDIFF('23:59:59', end_time)) + 1;
190 ELSEIF wk = 1 THEN
191 SET value_to_remove = value_to_remove + TIME_TO_SEC(TIMEDIFF(end_time, '00:00'));
192 INSERT INTO temp SET dt = fr_date, r = 'else wk = 7, wk = 1', remove_time = TIME_TO_SEC(TIMEDIFF(end_time, '00:00'));
193 END IF;
194 END IF;
195 END IF;
196
197 END IF;
198 # increment from_date for while loop
199 SET fr_date = DATE_ADD(fr_date, INTERVAL 1 DAY);
200
201 END WHILE;
202
203 SELECT dt, remove_time / 60, r, inverval_minutes / 60 as 't', value_to_remove / 60 as 'r', ROUND(((inverval_minutes - value_to_remove) / 60), 2) as 'v' from temp;
204 SELECT ROUND(((inverval_minutes - value_to_remove) / 60), 2);
205
206 #return value_to_remove / 60;
207 # let's return reduced total inverval time
208 #RETURN ROUND(((inverval_minutes - value_to_remove) / 60), 2);
209
210END $$
211
212
213DELIMITER ;