· 4 years ago · Apr 13, 2021, 11:26 AM
1DROP PROCEDURE IF EXISTS RLS_procedure;
2DELIMITER $$
3CREATE PROCEDURE RLS_procedure(IN username_rls VARCHAR(50), IN company_rls VARCHAR(50))
4BEGIN
5
6 CREATE TEMPORARY TABLE location1_tmp (
7 location_1 VARCHAR(1000));
8
9 CREATE TEMPORARY TABLE location2_tmp (
10 location_2 VARCHAR(1000));
11
12 CREATE TEMPORARY TABLE location3_tmp (
13 location_3 VARCHAR(1000));
14
15 CREATE TEMPORARY TABLE location4_tmp (
16 location_4 VARCHAR(1000));
17
18 CREATE TEMPORARY TABLE location5_tmp (
19 location_5 VARCHAR(1000));
20
21 CREATE TEMPORARY TABLE location6_tmp (
22 location_6 VARCHAR(1000));
23
24 CREATE TEMPORARY TABLE location7_tmp (
25 location_7 VARCHAR(1000));
26
27 CREATE TEMPORARY TABLE location8_tmp (
28 location_8 VARCHAR(1000));
29
30 CREATE TEMPORARY TABLE location9_tmp (
31 location_9 VARCHAR(1000));
32
33 CREATE TEMPORARY TABLE location10_tmp (
34 location_10 VARCHAR(1000));
35
36 CREATE TEMPORARY TABLE location11_tmp (
37 location_11 VARCHAR(1000));
38
39 CREATE TEMPORARY TABLE location12_tmp (
40 location_12 VARCHAR(1000));
41
42 CREATE TEMPORARY TABLE location13_tmp (
43 location_13 VARCHAR(1000));
44
45 CREATE TEMPORARY TABLE location14_tmp (
46 location_14 VARCHAR(1000));
47
48 CREATE TEMPORARY TABLE location15_tmp (
49 location_15 VARCHAR(1000));
50
51 CREATE TEMPORARY TABLE location16_tmp (
52 location_16 VARCHAR(1000));
53
54 CREATE TEMPORARY TABLE location17_tmp (
55 location_17 VARCHAR(1000));
56
57 CREATE TEMPORARY TABLE location18_tmp (
58 location_18 VARCHAR(1000));
59
60 CREATE TEMPORARY TABLE location19_tmp (
61 location_19 VARCHAR(1000));
62
63 CREATE TEMPORARY TABLE location20_tmp (
64 location_20 VARCHAR(1000));
65
66 SET @location1 = '';
67 SET @location2 = '';
68 SET @location3 = '';
69 SET @location4 = '';
70 SET @location5 = '';
71 SET @location6 = '';
72 SET @location7 = '';
73 SET @location8 = '';
74 SET @location9 = '';
75 SET @location10 = '';
76 SET @location11 = '';
77 SET @location12 = '';
78 SET @location13 = '';
79 SET @location14 = '';
80 SET @location15 = '';
81 SET @location16 = '';
82 SET @location17 = '';
83 SET @location18 = '';
84 SET @location19 = '';
85 SET @location20 = '';
86
87 SELECT s.location1, s.location2, s.location3, s.location4, s.location5, s.location6, s.location7, s.location8, s.location9, s.location10,
88 s.location11, s.location12, s.location13, s.location14, s.location15, s.location16, s.location17, s.location18, s.location19, s.location20
89 INTO
90 @location1, @location2, @location3, @location4, @location5, @location6, @location7, @location8, @location9, @location10,
91 @location11, @location12, @location13, @location14, @location15, @location16, @location17, @location18, @location19, @location20
92 FROM `security` s
93 WHERE s.username = username_rls AND s.company = company_rls;
94
95
96 IF @location1 IS NOT NULL AND @location1 != '' THEN
97 -- COUNT number of pipes+1 (locations to iterate)
98 SET @num_locationsbypipes = 0;
99 SELECT LENGTH(s.location1) - LENGTH(REPLACE(s.location1, "|", ""))+1 INTO @num_locationsbypipes
100 FROM `security` s WHERE s.username = username_rls AND s.company = company_rls;
101
102 IF @num_locationsbypipes > 0 THEN
103 -- iterate over locations and insert into @location_iterated
104 SET @it = 0;
105 WHILE @it < @num_locationsbypipes DO
106 SET @it = @it+1;
107
108 INSERT INTO location1_tmp (location_1)
109 SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(s.location1, "|", @it), "|", -1)
110 FROM `security` s
111 WHERE s.username = username_rls AND s.company = company_rls;
112 END WHILE;
113 ELSE
114 INSERT INTO location1_tmp (location_1)
115 SELECT s.location1
116 FROM `security` s
117 WHERE s.username = username_rls AND s.company = company_rls;
118 END IF;
119 ELSE
120 INSERT INTO location1_tmp (location_1) SELECT '';
121 END IF;
122
123
124 IF @location2 IS NOT NULL AND @location2 != '' THEN
125 -- COUNT number of pipes+1 (locations to iterate)
126 SET @num_locationsbypipes = 0;
127 SELECT LENGTH(s.location2) - LENGTH(REPLACE(s.location2, "|", ""))+1 INTO @num_locationsbypipes
128 FROM `security` s WHERE s.username = username_rls AND s.company = company_rls;
129
130 IF @num_locationsbypipes > 0 THEN
131 -- iterate over locations and insert into @location_iterated
132 SET @it = 0;
133 WHILE @it < @num_locationsbypipes DO
134 SET @it = @it+1;
135
136 INSERT INTO location2_tmp (location_2)
137 SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(s.location2, "|", @it), "|", -1)
138 FROM `security` s
139 WHERE s.username = username_rls AND s.company = company_rls;
140 END WHILE;
141 ELSE
142 INSERT INTO location2_tmp (location_2)
143 SELECT s.location2
144 FROM `security` s
145 WHERE s.username = username_rls AND s.company = company_rls;
146 END IF;
147 ELSE
148 INSERT INTO location2_tmp (location_2) SELECT '';
149 END IF;
150
151 IF @location3 IS NOT NULL AND @location3 != '' THEN
152 -- COUNT number of pipes+1 (locations to iterate)
153 SET @num_locationsbypipes = 0;
154 SELECT LENGTH(s.location3) - LENGTH(REPLACE(s.location3, "|", ""))+1 INTO @num_locationsbypipes
155 FROM `security` s WHERE s.username = username_rls AND s.company = company_rls;
156
157 IF @num_locationsbypipes > 0 THEN
158 -- iterate over locations and insert into @location_iterated
159 SET @it = 0;
160 WHILE @it < @num_locationsbypipes DO
161 SET @it = @it+1;
162
163 INSERT INTO location3_tmp (location_3)
164 SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(s.location3, "|", @it), "|", -1)
165 FROM `security` s
166 WHERE s.username = username_rls AND s.company = company_rls;
167 END WHILE;
168 ELSE
169 INSERT INTO location3_tmp (location_3)
170 SELECT s.location3
171 FROM `security` s
172 WHERE s.username = username_rls AND s.company = company_rls;
173 END IF;
174 ELSE
175 INSERT INTO location3_tmp (location_3) SELECT '';
176 END IF;
177
178 IF @location4 IS NOT NULL AND @location4 != '' THEN
179 -- COUNT number of pipes+1 (locations to iterate)
180 SET @num_locationsbypipes = 0;
181 SELECT LENGTH(s.location4) - LENGTH(REPLACE(s.location4, "|", ""))+1 INTO @num_locationsbypipes
182 FROM `security` s WHERE s.username = username_rls AND s.company = company_rls;
183
184 IF @num_locationsbypipes > 0 THEN
185 -- iterate over locations and insert into @location_iterated
186 SET @it = 0;
187 WHILE @it < @num_locationsbypipes DO
188 SET @it = @it+1;
189
190 INSERT INTO location4_tmp (location_4)
191 SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(s.location4, "|", @it), "|", -1)
192 FROM `security` s
193 WHERE s.username = username_rls AND s.company = company_rls;
194 END WHILE;
195 ELSE
196 INSERT INTO location4_tmp (location_4)
197 SELECT s.location4
198 FROM `security` s
199 WHERE s.username = username_rls AND s.company = company_rls;
200 END IF;
201 ELSE
202 INSERT INTO location4_tmp (location_4) SELECT '';
203 END IF;
204
205 IF @location5 IS NOT NULL AND @location5 != '' THEN
206 -- COUNT number of pipes+1 (locations to iterate)
207 SET @num_locationsbypipes = 0;
208 SELECT LENGTH(s.location5) - LENGTH(REPLACE(s.location5, "|", ""))+1 INTO @num_locationsbypipes
209 FROM `security` s WHERE s.username = username_rls AND s.company = company_rls;
210
211 IF @num_locationsbypipes > 0 THEN
212 -- iterate over locations and insert into @location_iterated
213 SET @it = 0;
214 WHILE @it < @num_locationsbypipes DO
215 SET @it = @it+1;
216
217 INSERT INTO location5_tmp (location_5)
218 SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(s.location5, "|", @it), "|", -1)
219 FROM `security` s
220 WHERE s.username = username_rls AND s.company = company_rls;
221 END WHILE;
222 ELSE
223 INSERT INTO location5_tmp (location_5)
224 SELECT s.location5
225 FROM `security` s
226 WHERE s.username = username_rls AND s.company = company_rls;
227 END IF;
228 ELSE
229 INSERT INTO location5_tmp (location_5) SELECT '';
230 END IF;
231
232 IF @location6 IS NOT NULL AND @location6 != '' THEN
233 -- COUNT number of pipes+1 (locations to iterate)
234 SET @num_locationsbypipes = 0;
235 SELECT LENGTH(s.location6) - LENGTH(REPLACE(s.location6, "|", ""))+1 INTO @num_locationsbypipes
236 FROM `security` s WHERE s.username = username_rls AND s.company = company_rls;
237
238 IF @num_locationsbypipes > 0 THEN
239 -- iterate over locations and insert into @location_iterated
240 SET @it = 0;
241 WHILE @it < @num_locationsbypipes DO
242 SET @it = @it+1;
243
244 INSERT INTO location6_tmp (location_6)
245 SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(s.location6, "|", @it), "|", -1)
246 FROM `security` s
247 WHERE s.username = username_rls AND s.company = company_rls;
248 END WHILE;
249 ELSE
250 INSERT INTO location6_tmp (location_6)
251 SELECT s.location6
252 FROM `security` s
253 WHERE s.username = username_rls AND s.company = company_rls;
254 END IF;
255 ELSE
256 INSERT INTO location6_tmp (location_6) SELECT '';
257 END IF;
258
259 IF @location7 IS NOT NULL AND @location7 != '' THEN
260 -- COUNT number of pipes+1 (locations to iterate)
261 SET @num_locationsbypipes = 0;
262 SELECT LENGTH(s.location7) - LENGTH(REPLACE(s.location7, "|", ""))+1 INTO @num_locationsbypipes
263 FROM `security` s WHERE s.username = username_rls AND s.company = company_rls;
264
265 IF @num_locationsbypipes > 0 THEN
266 -- iterate over locations and insert into @location_iterated
267 SET @it = 0;
268 WHILE @it < @num_locationsbypipes DO
269 SET @it = @it+1;
270
271 INSERT INTO location7_tmp (location_7)
272 SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(s.location7, "|", @it), "|", -1)
273 FROM `security` s
274 WHERE s.username = username_rls AND s.company = company_rls;
275 END WHILE;
276 ELSE
277 INSERT INTO location7_tmp (location_7)
278 SELECT s.location7
279 FROM `security` s
280 WHERE s.username = username_rls AND s.company = company_rls;
281 END IF;
282 ELSE
283 INSERT INTO location7_tmp (location_7) SELECT '';
284 END IF;
285
286 IF @location8 IS NOT NULL AND @location8 != '' THEN
287 -- COUNT number of pipes+1 (locations to iterate)
288 SET @num_locationsbypipes = 0;
289 SELECT LENGTH(s.locatio8) - LENGTH(REPLACE(s.location8, "|", ""))+1 INTO @num_locationsbypipes
290 FROM `security` s WHERE s.username = username_rls AND s.company = company_rls;
291
292 IF @num_locationsbypipes > 0 THEN
293 -- iterate over locations and insert into @location_iterated
294 SET @it = 0;
295 WHILE @it < @num_locationsbypipes DO
296 SET @it = @it+1;
297
298 INSERT INTO location8_tmp (location_8)
299 SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(s.location8, "|", @it), "|", -1)
300 FROM `security` s
301 WHERE s.username = username_rls AND s.company = company_rls;
302 END WHILE;
303 ELSE
304 INSERT INTO location8_tmp (location_8)
305 SELECT s.location8
306 FROM `security` s
307 WHERE s.username = username_rls AND s.company = company_rls;
308 END IF;
309 ELSE
310 INSERT INTO location8_tmp (location8) SELECT '';
311 END IF;
312
313 IF @location9 IS NOT NULL AND @location9 != '' THEN
314 -- COUNT number of pipes+1 (locations to iterate)
315 SET @num_locationsbypipes = 0;
316 SELECT LENGTH(s.location9) - LENGTH(REPLACE(s.location9, "|", ""))+1 INTO @num_locationsbypipes
317 FROM `security` s WHERE s.username = username_rls AND s.company = company_rls;
318
319 IF @num_locationsbypipes > 0 THEN
320 -- iterate over locations and insert into @location_iterated
321 SET @it = 0;
322 WHILE @it < @num_locationsbypipes DO
323 SET @it = @it+1;
324
325 INSERT INTO location9_tmp (location_9)
326 SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(s.location9, "|", @it), "|", -1)
327 FROM `security` s
328 WHERE s.username = username_rls AND s.company = company_rls;
329 END WHILE;
330 ELSE
331 INSERT INTO location9_tmp (location_9)
332 SELECT s.location9
333 FROM `security` s
334 WHERE s.username = username_rls AND s.company = company_rls;
335 END IF;
336 ELSE
337 INSERT INTO location9_tmp (location_9) SELECT '';
338 END IF;
339
340 IF @location10 IS NOT NULL AND @location10 != '' THEN
341 -- COUNT number of pipes+1 (locations to iterate)
342 SET @num_locationsbypipes = 0;
343 SELECT LENGTH(s.location10) - LENGTH(REPLACE(s.location10, "|", ""))+1 INTO @num_locationsbypipes
344 FROM `security` s WHERE s.username = username_rls AND s.company = company_rls;
345
346 IF @num_locationsbypipes > 0 THEN
347 -- iterate over locations and insert into @location_iterated
348 SET @it = 0;
349 WHILE @it < @num_locationsbypipes DO
350 SET @it = @it+1;
351
352 INSERT INTO location10_tmp (location_10)
353 SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(s.location10, "|", @it), "|", -1)
354 FROM `security` s
355 WHERE s.username = username_rls AND s.company = company_rls;
356 END WHILE;
357 ELSE
358 INSERT INTO location10_tmp (location_10)
359 SELECT s.location10
360 FROM `security` s
361 WHERE s.username = username_rls AND s.company = company_rls;
362 END IF;
363 ELSE
364 INSERT INTO location10_tmp (location_10) SELECT '';
365 END IF;
366
367
368
369 IF @location11 IS NOT NULL AND @location11 != '' THEN
370 -- COUNT number of pipes+1 (locations to iterate)
371 SET @num_locationsbypipes = 0;
372 SELECT LENGTH(s.location11) - LENGTH(REPLACE(s.location11, "|", ""))+1 INTO @num_locationsbypipes
373 FROM `security` s WHERE s.username = username_rls AND s.company = company_rls;
374
375 IF @num_locationsbypipes > 0 THEN
376 -- iterate over locations and insert into @location_iterated
377 SET @it = 0;
378 WHILE @it < @num_locationsbypipes DO
379 SET @it = @it+1;
380
381 INSERT INTO location11_tmp (location_11)
382 SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(s.location11, "|", @it), "|", -1)
383 FROM `security` s
384 WHERE s.username = username_rls AND s.company = company_rls;
385 END WHILE;
386 ELSE
387 INSERT INTO location11_tmp (location_11)
388 SELECT s.location11
389 FROM `security` s
390 WHERE s.username = username_rls AND s.company = company_rls;
391 END IF;
392 ELSE
393 INSERT INTO location11_tmp (location_11) SELECT '';
394 END IF;
395
396
397 IF @location12 IS NOT NULL AND @location12 != '' THEN
398 -- COUNT number of pipes+1 (locations to iterate)
399 SET @num_locationsbypipes = 0;
400 SELECT LENGTH(s.location12) - LENGTH(REPLACE(s.location12, "|", ""))+1 INTO @num_locationsbypipes
401 FROM `security` s WHERE s.username = username_rls AND s.company = company_rls;
402
403 IF @num_locationsbypipes > 0 THEN
404 -- iterate over locations and insert into @location_iterated
405 SET @it = 0;
406 WHILE @it < @num_locationsbypipes DO
407 SET @it = @it+1;
408
409 INSERT INTO location12_tmp (location_12)
410 SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(s.location12, "|", @it), "|", -1)
411 FROM `security` s
412 WHERE s.username = username_rls AND s.company = company_rls;
413 END WHILE;
414 ELSE
415 INSERT INTO location12_tmp (location_12)
416 SELECT s.location12
417 FROM `security` s
418 WHERE s.username = username_rls AND s.company = company_rls;
419 END IF;
420 ELSE
421 INSERT INTO location12_tmp (location_12) SELECT '';
422 END IF;
423
424 IF @location13 IS NOT NULL AND @location13 != '' THEN
425 -- COUNT number of pipes+1 (locations to iterate)
426 SET @num_locationsbypipes = 0;
427 SELECT LENGTH(s.location13) - LENGTH(REPLACE(s.location13, "|", ""))+1 INTO @num_locationsbypipes
428 FROM `security` s WHERE s.username = username_rls AND s.company = company_rls;
429
430 IF @num_locationsbypipes > 0 THEN
431 -- iterate over locations and insert into @location_iterated
432 SET @it = 0;
433 WHILE @it < @num_locationsbypipes DO
434 SET @it = @it+1;
435
436 INSERT INTO location13_tmp (location_13)
437 SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(s.location13, "|", @it), "|", -1)
438 FROM `security` s
439 WHERE s.username = username_rls AND s.company = company_rls;
440 END WHILE;
441 ELSE
442 INSERT INTO location13_tmp (location_13)
443 SELECT s.location13
444 FROM `security` s
445 WHERE s.username = username_rls AND s.company = company_rls;
446 END IF;
447 ELSE
448 INSERT INTO location13_tmp (location_13) SELECT '';
449 END IF;
450
451 IF @location14 IS NOT NULL AND @location14 != '' THEN
452 -- COUNT number of pipes+1 (locations to iterate)
453 SET @num_locationsbypipes = 0;
454 SELECT LENGTH(s.location14) - LENGTH(REPLACE(s.location14, "|", ""))+1 INTO @num_locationsbypipes
455 FROM `security` s WHERE s.username = username_rls AND s.company = company_rls;
456
457 IF @num_locationsbypipes > 0 THEN
458 -- iterate over locations and insert into @location_iterated
459 SET @it = 0;
460 WHILE @it < @num_locationsbypipes DO
461 SET @it = @it+1;
462
463 INSERT INTO location14_tmp (location_14)
464 SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(s.location14, "|", @it), "|", -1)
465 FROM `security` s
466 WHERE s.username = username_rls AND s.company = company_rls;
467 END WHILE;
468 ELSE
469 INSERT INTO location14_tmp (location_14)
470 SELECT s.location14
471 FROM `security` s
472 WHERE s.username = username_rls AND s.company = company_rls;
473 END IF;
474 ELSE
475 INSERT INTO location14_tmp (location_14) SELECT '';
476 END IF;
477
478 IF @location15 IS NOT NULL AND @location15 != '' THEN
479 -- COUNT number of pipes+1 (locations to iterate)
480 SET @num_locationsbypipes = 0;
481 SELECT LENGTH(s.location15) - LENGTH(REPLACE(s.location15, "|", ""))+1 INTO @num_locationsbypipes
482 FROM `security` s WHERE s.username = username_rls AND s.company = company_rls;
483
484 IF @num_locationsbypipes > 0 THEN
485 -- iterate over locations and insert into @location_iterated
486 SET @it = 0;
487 WHILE @it < @num_locationsbypipes DO
488 SET @it = @it+1;
489
490 INSERT INTO location15_tmp (location_15)
491 SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(s.location15, "|", @it), "|", -1)
492 FROM `security` s
493 WHERE s.username = username_rls AND s.company = company_rls;
494 END WHILE;
495 ELSE
496 INSERT INTO location15_tmp (location_15)
497 SELECT s.location15
498 FROM `security` s
499 WHERE s.username = username_rls AND s.company = company_rls;
500 END IF;
501 ELSE
502 INSERT INTO location15_tmp (location_15) SELECT '';
503 END IF;
504
505 IF @location16 IS NOT NULL AND @location16 != '' THEN
506 -- COUNT number of pipes+1 (locations to iterate)
507 SET @num_locationsbypipes = 0;
508 SELECT LENGTH(s.location16) - LENGTH(REPLACE(s.location16, "|", ""))+1 INTO @num_locationsbypipes
509 FROM `security` s WHERE s.username = username_rls AND s.company = company_rls;
510
511 IF @num_locationsbypipes > 0 THEN
512 -- iterate over locations and insert into @location_iterated
513 SET @it = 0;
514 WHILE @it < @num_locationsbypipes DO
515 SET @it = @it+1;
516
517 INSERT INTO location16_tmp (location_16)
518 SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(s.location16, "|", @it), "|", -1)
519 FROM `security` s
520 WHERE s.username = username_rls AND s.company = company_rls;
521 END WHILE;
522 ELSE
523 INSERT INTO location16_tmp (location_16)
524 SELECT s.location16
525 FROM `security` s
526 WHERE s.username = username_rls AND s.company = company_rls;
527 END IF;
528 ELSE
529 INSERT INTO location16_tmp (location_16) SELECT '';
530 END IF;
531
532 IF @location17 IS NOT NULL AND @location17 != '' THEN
533 -- COUNT number of pipes+1 (locations to iterate)
534 SET @num_locationsbypipes = 0;
535 SELECT LENGTH(s.location17) - LENGTH(REPLACE(s.location17, "|", ""))+1 INTO @num_locationsbypipes
536 FROM `security` s WHERE s.username = username_rls AND s.company = company_rls;
537
538 IF @num_locationsbypipes > 0 THEN
539 -- iterate over locations and insert into @location_iterated
540 SET @it = 0;
541 WHILE @it < @num_locationsbypipes DO
542 SET @it = @it+1;
543
544 INSERT INTO location17_tmp (location_17)
545 SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(s.location17, "|", @it), "|", -1)
546 FROM `security` s
547 WHERE s.username = username_rls AND s.company = company_rls;
548 END WHILE;
549 ELSE
550 INSERT INTO location17_tmp (location_17)
551 SELECT s.location17
552 FROM `security` s
553 WHERE s.username = username_rls AND s.company = company_rls;
554 END IF;
555 ELSE
556 INSERT INTO location17_tmp (location_17) SELECT '';
557 END IF;
558
559 IF @location18 IS NOT NULL AND @location18 != '' THEN
560 -- COUNT number of pipes+1 (locations to iterate)
561 SET @num_locationsbypipes = 0;
562 SELECT LENGTH(s.location18) - LENGTH(REPLACE(s.location18, "|", ""))+1 INTO @num_locationsbypipes
563 FROM `security` s WHERE s.username = username_rls AND s.company = company_rls;
564
565 IF @num_locationsbypipes > 0 THEN
566 -- iterate over locations and insert into @location_iterated
567 SET @it = 0;
568 WHILE @it < @num_locationsbypipes DO
569 SET @it = @it+1;
570
571 INSERT INTO location18_tmp (location_18)
572 SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(s.location18, "|", @it), "|", -1)
573 FROM `security` s
574 WHERE s.username = username_rls AND s.company = company_rls;
575 END WHILE;
576 ELSE
577 INSERT INTO location18_tmp (location_18)
578 SELECT s.location18
579 FROM `security` s
580 WHERE s.username = username_rls AND s.company = company_rls;
581 END IF;
582 ELSE
583 INSERT INTO location18_tmp (location18) SELECT '';
584 END IF;
585
586 IF @location19 IS NOT NULL AND @location19 != '' THEN
587 -- COUNT number of pipes+1 (locations to iterate)
588 SET @num_locationsbypipes = 0;
589 SELECT LENGTH(s.location19) - LENGTH(REPLACE(s.location19, "|", ""))+1 INTO @num_locationsbypipes
590 FROM `security` s WHERE s.username = username_rls AND s.company = company_rls;
591
592 IF @num_locationsbypipes > 0 THEN
593 -- iterate over locations and insert into @location_iterated
594 SET @it = 0;
595 WHILE @it < @num_locationsbypipes DO
596 SET @it = @it+1;
597
598 INSERT INTO location19_tmp (location_19)
599 SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(s.location19, "|", @it), "|", -1)
600 FROM `security` s
601 WHERE s.username = username_rls AND s.company = company_rls;
602 END WHILE;
603 ELSE
604 INSERT INTO location19_tmp (location_19)
605 SELECT s.location19
606 FROM `security` s
607 WHERE s.username = username_rls AND s.company = company_rls;
608 END IF;
609 ELSE
610 INSERT INTO location19_tmp (location_19) SELECT '';
611 END IF;
612
613 IF @location20 IS NOT NULL AND @location20 != '' THEN
614 -- COUNT number of pipes+1 (locations to iterate)
615 SET @num_locationsbypipes = 0;
616 SELECT LENGTH(s.location20) - LENGTH(REPLACE(s.location20, "|", ""))+1 INTO @num_locationsbypipes
617 FROM `security` s WHERE s.username = username_rls AND s.company = company_rls;
618
619 IF @num_locationsbypipes > 0 THEN
620 -- iterate over locations and insert into @location_iterated
621 SET @it = 0;
622 WHILE @it < @num_locationsbypipes DO
623 SET @it = @it+1;
624
625 INSERT INTO location20_tmp (location_20)
626 SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(s.location20, "|", @it), "|", -1)
627 FROM `security` s
628 WHERE s.username = username_rls AND s.company = company_rls;
629 END WHILE;
630 ELSE
631 INSERT INTO location20_tmp (location_20)
632 SELECT s.location20
633 FROM `security` s
634 WHERE s.username = username_rls AND s.company = company_rls;
635 END IF;
636 ELSE
637 INSERT INTO location20_tmp (location_20) SELECT '';
638 END IF;
639
640
641
642 -- delete and then insert (update)
643 DELETE FROM drivers_rls
644 WHERE username = username_rls AND company = company_rls;
645
646 INSERT INTO drivers_rls (username, company, employee_number)
647 SELECT username_rls AS username, s_d.company, s_d.employee_number
648 FROM summary_drivers AS s_d
649 WHERE s_d.company = company_rls
650 AND IF(@location1 != '' AND @location1 IS NOT NULL, s_d.location1_d, '') IN (SELECT location_1 FROM location1_tmp)
651 AND IF(@location2 != '' AND @location2 IS NOT NULL, s_d.location2_d, '') IN (SELECT location_2 FROM location2_tmp)
652 AND IF(@location3 != '' AND @location3 IS NOT NULL, s_d.location3_d, '') IN (SELECT location_3 FROM location3_tmp)
653 AND IF(@location4 != '' AND @location4 IS NOT NULL, s_d.location4_d, '') IN (SELECT location_4 FROM location4_tmp)
654 AND IF(@location5 != '' AND @location5 IS NOT NULL, s_d.location5_d, '') IN (SELECT location_5 FROM location5_tmp)
655 AND IF(@location6 != '' AND @location6 IS NOT NULL, s_d.location6_d, '') IN (SELECT location_6 FROM location6_tmp)
656 AND IF(@location7 != '' AND @location7 IS NOT NULL, s_d.location7_d, '') IN (SELECT location_7 FROM location7_tmp)
657 AND IF(@location8 != '' AND @location8 IS NOT NULL, s_d.location8_d, '') IN (SELECT location_8 FROM location8_tmp)
658 AND IF(@location9 != '' AND @location9 IS NOT NULL, s_d.location9_d, '') IN (SELECT location_9 FROM location9_tmp)
659 AND IF(@location10 != '' AND @location10 IS NOT NULL, s_d.location10_d, '') IN (SELECT location_10 FROM location10_tmp)
660 AND IF(@location11 != '' AND @location11 IS NOT NULL, s_d.location11_d, '') IN (SELECT location_11 FROM location11_tmp)
661 AND IF(@location12 != '' AND @location12 IS NOT NULL, s_d.location12_d, '') IN (SELECT location_12 FROM location12_tmp)
662 AND IF(@location13 != '' AND @location13 IS NOT NULL, s_d.location13_d, '') IN (SELECT location_13 FROM location13_tmp)
663 AND IF(@location14 != '' AND @location14 IS NOT NULL, s_d.location14_d, '') IN (SELECT location_14 FROM location14_tmp)
664 AND IF(@location15 != '' AND @location15 IS NOT NULL, s_d.location15_d, '') IN (SELECT location_15 FROM location15_tmp)
665 AND IF(@location16 != '' AND @location16 IS NOT NULL, s_d.location16_d, '') IN (SELECT location_16 FROM location16_tmp)
666 AND IF(@location17 != '' AND @location17 IS NOT NULL, s_d.location17_d, '') IN (SELECT location_17 FROM location17_tmp)
667 AND IF(@location18 != '' AND @location18 IS NOT NULL, s_d.location18_d, '') IN (SELECT location_18 FROM location18_tmp)
668 AND IF(@location19 != '' AND @location19 IS NOT NULL, s_d.location19_d, '') IN (SELECT location_19 FROM location19_tmp)
669 AND IF(@location20 != '' AND @location20 IS NOT NULL, s_d.location20_d, '') IN (SELECT location_20 FROM location20_tmp);
670
671 DROP TEMPORARY TABLE IF EXISTS location1_tmp;
672 DROP TEMPORARY TABLE IF EXISTS location2_tmp;
673 DROP TEMPORARY TABLE IF EXISTS location3_tmp;
674 DROP TEMPORARY TABLE IF EXISTS location4_tmp;
675 DROP TEMPORARY TABLE IF EXISTS location5_tmp;
676 DROP TEMPORARY TABLE IF EXISTS location6_tmp;
677 DROP TEMPORARY TABLE IF EXISTS location7_tmp;
678 DROP TEMPORARY TABLE IF EXISTS location8_tmp;
679 DROP TEMPORARY TABLE IF EXISTS location9_tmp;
680 DROP TEMPORARY TABLE IF EXISTS location10_tmp;
681 DROP TEMPORARY TABLE IF EXISTS location11_tmp;
682 DROP TEMPORARY TABLE IF EXISTS location12_tmp;
683 DROP TEMPORARY TABLE IF EXISTS location13_tmp;
684 DROP TEMPORARY TABLE IF EXISTS location14_tmp;
685 DROP TEMPORARY TABLE IF EXISTS location15_tmp;
686 DROP TEMPORARY TABLE IF EXISTS location16_tmp;
687 DROP TEMPORARY TABLE IF EXISTS location17_tmp;
688 DROP TEMPORARY TABLE IF EXISTS location18_tmp;
689 DROP TEMPORARY TABLE IF EXISTS location19_tmp;
690 DROP TEMPORARY TABLE IF EXISTS location20_tmp;
691
692END$$
693
694DELIMITER ;