Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
0.00%
0 / 784
0.00%
0 / 17
 CRAP
0.00%
0 / 1
Itv
0.00%
0 / 784
0.00%
0 / 17
 38612
0.00%
0 / 1
 __construct
0.00%
0 / 13
0.00%
0 / 1
 20
 list_itv
0.00%
0 / 249
0.00%
0 / 1
 5700
 get_dates
0.00%
0 / 9
0.00%
0 / 1
 6
 update_itv
0.00%
0 / 94
0.00%
0 / 1
 1332
 create_itv
0.00%
0 / 75
0.00%
0 / 1
 870
 delete_itv
0.00%
0 / 24
0.00%
0 / 1
 20
 get_itv
0.00%
0 / 8
0.00%
0 / 1
 6
 human_filesize
0.00%
0 / 3
0.00%
0 / 1
 2
 get_itv_files
0.00%
0 / 12
0.00%
0 / 1
 20
 delete_itv_file
0.00%
0 / 10
0.00%
0 / 1
 12
 download_itv_file
0.00%
0 / 10
0.00%
0 / 1
 20
 isEmailValid
0.00%
0 / 4
0.00%
0 / 1
 6
 email_reminders
0.00%
0 / 128
0.00%
0 / 1
 306
 email_reminders_mileage
0.00%
0 / 90
0.00%
0 / 1
 56
 get_distincts
0.00%
0 / 21
0.00%
0 / 1
 6
 get_all_users
0.00%
0 / 16
0.00%
0 / 1
 12
 download_itv
0.00%
0 / 18
0.00%
0 / 1
 2
1<?php
2
3namespace App\Http\Controllers;
4
5use App\Models\TblCompanyUsers;
6use App\Models\TblItv;
7use App\Models\TblItvEmailLogs;
8use App\Models\TblItvFiles;
9use App\Models\TblUsers;
10use Illuminate\Http\Request;
11use Illuminate\Support\Facades\App;
12use Illuminate\Support\Facades\Cache;
13use Illuminate\Contracts\Routing\ResponseFactory;
14use Illuminate\Http\Response;
15use Illuminate\Support\Facades\DB;
16use Illuminate\Support\Facades\Storage;
17
18class Itv extends Controller
19{
20    private $locale;
21    private $userId;
22    private $region;
23    private $companyIds;
24    private $companyId;
25
26    public function __construct(){
27        $this->locale = request()->header('Locale-Id');
28        $this->userId = request()->header('User-Id');
29        $this->region = request()->header('Region');
30
31        App::setLocale($this->locale);
32
33        $this->companyIds = [];
34
35        if($this->region != null && $this->region != "" && $this->region != "All"){
36            $this->region = urldecode((string) $this->region);
37
38            $query = 'SELECT 
39                        b.company_id
40                    FROM 
41                        tbl_company_users a 
42                        LEFT JOIN tbl_companies b ON a.company_id = b.company_id 
43                    WHERE 
44                        a.user_id = ?
45                        AND b.region = ?';
46
47            $this->companyIds = DB::select($query, [intval($this->userId), $this->region]);
48
49            $this->companyIds = collect($this->companyIds)->pluck('company_id')->toArray();
50        } else {
51            $this->companyIds = TblCompanyUsers::where('user_id', $this->userId)->pluck('company_id')->all();
52        }
53
54        $this->companyId = implode(',', $this->companyIds);
55    }
56
57    public function list_itv(Request $request): ResponseFactory|Response{
58
59        try {
60
61            $data = $request->all();
62            $companyId = addslashes((string) $data['company_id']);
63            $userId = addslashes((string) $data['user_id']);
64            $filter = $data['filterModel'];
65            $sort = $data['sortModel'];
66            $result = [];
67            $subquery = "";
68            $where = "";
69            $having = "";
70            $orderBy = "";
71            $start = addslashes((string) $data['start']);
72            $end = addslashes((string) $data['end']);
73            $totalRowCount = 0;
74            $withFilters = '';
75
76            $filterType = [
77                'contains' => "LIKE '%[value]%'",
78                'notContains' => "NOT LIKE '%[value]%'",
79                'equals' => "= '[value]'",
80                'notEqual' => "<> '[value]'",
81                'startsWith' => "LIKE '[value]%'",
82                'endsWith' => "LIKE '%[value]'",
83                'blank' => "IS NULL",
84                'notBlank' => "IS NOT NULL",
85                'lessThan' => "< [value]",
86                'lessThanOrEqual' => "<= [value]",
87                'greaterThan' => "> [value]",
88                'greaterThanOrEqual' => ">= [value]",
89                'inRange' => "BETWEEN [value1] AND [value2]"
90            ];
91
92
93            if(isset($data['ids']) && count($data['ids']) > 0){
94                $ids = implode(",", $data['ids']);
95                $where = " AND a.id IN ({$ids}) ";
96            }
97
98            if (isset($data['ids_not_in']) && count($data['ids_not_in']) > 0) {
99                $ids = implode(',', $data['ids_not_in']);
100                $where = " AND a.id NOT IN ({$ids}) ";
101            }
102
103            $matchScoreCol = '';
104            $matchScoreOrderBy = '';
105
106            if (isset($data['searchText']) && $data['searchText'] != null) {
107
108                $availableParameters = [
109                    'a.region',
110                    'b.name',
111                    'a.brand',
112                    'a.vehicle_type',
113                    'a.license_plate',
114                    'a.mileage',
115                    'a.last_itv_date',
116                    'a.next_itv_date',
117                    'a.mileage_threshold',
118                    'a.is_booked',
119                    'a.driver',
120                    'a.responsible_name',
121                    'a.responsible_email',
122                    'a.created_by',
123                    'a.created_at',
124                    'a.updated_by',
125                    'a.updated_at',
126                ];
127
128                $searchText = addslashes((string) $data['searchText']);
129                $searchTextArray = explode(" ", $searchText);
130
131                $searchArray = [];
132                $splitSearchArray = [];
133                $matchScoreArray = [];
134                $sc = 1;
135                foreach ($availableParameters as $field) {
136                    if ($field == 'a.license_plate' || $field == 'a.created_at') {
137                        $sc = 3;
138                    } elseif ($field == 'a.last_itv_date') {
139                        $sc = 2;
140                    } else {
141                        $sc = 1;
142                    }
143
144                    $l = "{$field} LIKE '%{$searchText}%'";
145
146                    $d = "IFNULL((LENGTH(LOWER({$field})) - LENGTH(REPLACE(LOWER({$field}), LOWER('{$searchText}'), ''))) / LENGTH(LOWER('{$searchText}')), 0) * {$sc}";
147
148                    if (count($searchTextArray) > 1) {
149                        foreach ($searchTextArray as $word) {
150                            if (! is_numeric($word)) {
151                                $d .= " + IFNULL((LENGTH(LOWER({$field})) - LENGTH(REPLACE(LOWER({$field}), LOWER('{$word}'), ''))) / LENGTH(LOWER('{$word}')), 0) * {$sc}";
152                            }
153                        }
154                    }
155
156                    array_push($matchScoreArray, $d);
157
158                    if (is_numeric($searchText)) {
159                        array_push($searchArray, "({$l} OR {$field} = CAST('{$searchText}' AS UNSIGNED))");
160                    } else {
161                        array_push($searchArray, "({$l} OR DATE_FORMAT({$field}, '%d/%m/%Y') = DATE_FORMAT(STR_TO_DATE('{$searchText}', '%d/%m/%Y'), '%d/%m/%Y'))");
162                    }
163
164                    if (count($searchTextArray) > 1) {
165                        foreach ($searchTextArray as $word) {
166
167                            $l = "{$field} LIKE '%{$word}%'";
168
169                            if (is_numeric($word)) {
170                                array_push($splitSearchArray, "{$l} OR {$field} = CAST('{$word}' AS UNSIGNED)");
171                            } else {
172                                array_push($splitSearchArray, "{$l} OR DATE_FORMAT({$field}, '%d/%m/%Y') = DATE_FORMAT(STR_TO_DATE('{$word}', '%d/%m/%Y'), '%d/%m/%Y')");
173                            }
174                        }
175                    }
176
177                    $sc = 1;
178                }
179
180                if (count($splitSearchArray) > 0) {
181                    $splitSearchArray = implode(' OR ', $splitSearchArray);
182                    $splitSearchArray = " OR ({$splitSearchArray}) ";
183                } else {
184                    $splitSearchArray = '';
185                }
186
187                $searchArray = implode(' OR ', $searchArray);
188                $matchScoreArray = implode(',', $matchScoreArray);
189                $matchScoreCol = ", GREATEST({$matchScoreArray}) match_score";
190                $matchScoreOrderBy = 'match_score DESC,';
191                $where .= " AND ({$searchArray} {$splitSearchArray})";
192            }
193
194            if (count($sort) > 0) {
195                $field = $sort[0]['colId'];
196                $sortBy = $sort[0]['sort'];
197                
198                if(str_contains((string) $field, "translate")){
199                    $field = str_replace("_translate", "", $field);
200                }else{
201                    if($field == "company_name"){
202                        $field = "b.name";
203                    }
204                }
205
206                if ($matchScoreOrderBy) {
207                    $matchScoreOrderBy = ', match_score DESC';
208                }
209
210                $orderBy = " ORDER BY {$field} {$sortBy} {$matchScoreOrderBy}";
211            } else {
212                $orderBy = " ORDER BY {$matchScoreOrderBy} a.id DESC";
213            }
214
215            foreach ($filter as $key => $data) {                
216                if(str_contains((string) $key, "translate")){
217
218                    $field = str_replace('_translate', '', $key);
219
220                    if ($field == 'created_at') {
221                        $field = 'a.created_at';
222                    } elseif ($field == 'updated_at') {
223                        $field = 'a.updated_at';
224                    } elseif ($field == 'last_itv_date') {
225                        $field = 'a.last_itv_date';
226                    } elseif ($field == 'next_itv_date') {
227                        $field = 'a.next_itv_date';
228                    }
229
230                    $whereDates = '';
231                    $z = 0;
232
233                    if (isset($data['filters']) && ! empty($data['filters'])) {
234                        foreach ($data['filters'] as $yearKey => $yearData) {
235                            $yearsMonths = [];
236                            $yearsWeeks = [];
237                            
238                            if($z > 0){
239                                $whereDates .= " OR (YEAR($field) = {$yearKey} ";
240                            } else {
241                                $whereDates .= " (YEAR($field) = {$yearKey} ";
242                            }
243
244                            for ($i = 0; $i < count($yearData['months']); $i++) {
245                                if ($yearData['months'][$i]['isChecked']) {
246                                    array_push($yearsMonths, $yearData['months'][$i]['value']);
247                                }
248                            }
249
250                            $yearsMonths = implode("','", $yearsMonths);
251                            $whereDates .= " AND (MONTH({$field}) IN ('{$yearsMonths}')";
252
253                            for ($i = 0; $i < count($yearData['weeks']); $i++) {
254                                if ($yearData['weeks'][$i]['isChecked']) {
255                                    array_push($yearsWeeks, $yearData['weeks'][$i]['value']);
256                                }
257                            }
258
259                            $yearsWeeks = implode("','", $yearsWeeks);
260                            if ($yearsWeeks != '') {
261                                $whereDates .= " OR WEEK({$field}) IN ('{$yearsWeeks}') ";
262                            }
263
264                            $whereDates .= ')) ';
265                            $z++;
266                        }
267                    }
268
269                    $whereDataUptoToday = '';
270                    if (isset($data['isDataUptoToday'])) {
271                        if ($data['isDataUptoToday']) {
272                            $whereDates = '';
273                            $whereDataUptoToday .= " AND {$field} < NOW() AND {$field} > 0 ";
274                        }
275                    }
276
277                    $whereBlanks = '';
278                    if (isset($data['isBlanks'])) {
279                        if ($data['isBlanks']) {
280                            $conj = 'OR';
281                            if ($whereDates == '') {
282                                $conj = '';
283                            }
284                            $whereBlanks .= " {$conj} {$field} IS NULL ";
285                        } else {
286                            $conj = 'AND';
287                            if ($whereDates == '') {
288                                $conj = '';
289                            }
290                            $whereBlanks .= " {$conj} {$field} IS NOT NULL ";
291                        }
292                    }
293
294                    $where .= " AND ({$whereDates} {$whereBlanks} {$whereDataUptoToday}) ";
295                }else{
296                    if($data['filterType'] == 'number'){
297                        if(array_key_exists('operator', $data)){
298                            if($data['condition1']['type'] != 'blank' && $data['condition2']['type'] != 'notBlank'){
299                                $data['condition1']['filter'] = addslashes((string) $data['condition1']['filter']);
300                                $data['condition2']['filter'] = addslashes((string) $data['condition2']['filter']);
301
302                                if($data['condition1']['type'] == 'inRange'){
303                                    $data['condition1']['filterTo'] = addslashes((string) $data['condition1']['filterTo']);
304                                    $inRange = str_replace("[value1]", $data['condition1']['filter'], $filterType['inRange']);
305                                    $val1 = str_replace("[value2]", $data['condition1']['filterTo'], $inRange);
306                                }else{
307                                    $val1 = str_replace("[value]", $data['condition1']['filter'], $filterType[$data['condition1']['type']]);
308                                }
309    
310                                if($data['condition2']['type'] == 'inRange'){
311                                    $data['condition2']['filterTo'] = addslashes((string) $data['condition2']['filterTo']);
312                                    $inRange = str_replace("[value1]", $data['condition2']['filter'], $filterType['inRange']);
313                                    $val2 = str_replace("[value2]", $data['condition2']['filterTo'], $inRange);
314                                }else{
315                                    $val2 = str_replace("[value]", $data['condition2']['filter'], $filterType[$data['condition2']['type']]);
316                                }
317
318                            } else {
319                                $val1 = $filterType[$data['condition1']['type']];
320                                $val2 = $filterType[$data['condition2']['type']];
321                            }
322
323                            $where .= " AND a.{$key} {$val1} {$data['operator']} a.{$key} {$val2} ";
324                        }else{
325                            if($data['type'] != 'blank' && $data['type'] != 'notBlank'){
326                                $data['filter'] = addslashes((string) $data['filter']);
327                                
328                                if($data['type'] == 'inRange'){
329                                    $data['filterTo'] = addslashes((string) $data['filterTo']);
330                                    $inRange = str_replace("[value1]", $data['filter'], $filterType['inRange']);
331                                    $val = str_replace("[value2]", $data['filterTo'], $inRange);
332                                }else{
333                                    $val = str_replace("[value]", $data['filter'], $filterType[$data['type']]);
334                                }
335                            } else {
336                                $val = $filterType[$data['type']];
337                            }
338
339                            $where .= " AND a.{$key} {$val} ";
340                        }
341                    }
342
343                    if($data['filterType'] == 'text'){
344                        if(array_key_exists('operator', $data)){
345                            if($data['condition1']['type'] != 'blank' && $data['condition2']['type'] != 'notBlank'){
346                                $data['condition1']['filter'] = addslashes((string) $data['condition1']['filter']);
347                                $val1 = str_replace("[value]", $data['condition1']['filter'], $filterType[$data['condition1']['type']]);
348                            }
349
350                            if($data['condition2']['type'] != 'blank' && $data['condition2']['type'] != 'notBlank'){
351                                $data['condition2']['filter'] = addslashes((string) $data['condition2']['filter']);
352                                $val2 = str_replace("[value]", $data['condition2']['filter'], $filterType[$data['condition2']['type']]);
353                            }
354
355                            $where .= " AND {$key} {$val1} {$data['operator']} {$key} {$val2} ";
356                        }else{
357                            if($data['type'] != 'blank' && $data['type'] != 'notBlank'){
358                                $data['filter'] = addslashes((string) $data['filter']);
359                                $val = str_replace("[value]", $data['filter'], $filterType[$data['type']]);
360                            }else{
361                                $val = $filterType[$data['type']];
362                            }
363
364                            $where .= " AND {$key} {$val} ";
365                        }
366                    }
367
368                    if ($data['filterType'] == 'set') {
369                        $statusName = $key;
370
371                        if ($key == 'updated_by') {
372                            $statusName = 'a.updated_by';
373                        } elseif ($key == 'created_by') {
374                            $statusName = 'a.created_by';
375                        } elseif ($key == 'company_name') {
376                            $statusName = 'b.name';
377                        } elseif ($key == 'region') {
378                            $statusName = 'a.region';
379                        } elseif ($key == 'is_booked') {
380                            $statusName = 'a.is_booked';
381                            if ($data['values']) {
382                                foreach ($data['values'] as $k => $v) {
383                                    if ($v == 'No') {
384                                        $data['values'][$k] = 0;
385                                    } elseif ($v == null) {
386                                        $data['values'][$k] = null;
387                                    } else {
388                                        $data['values'][$k] = 1;
389                                    }
390                                }
391                            }
392                        }
393
394                        $val = implode("','", $data['values']);
395
396                        if (in_array(null, $data['values'], true)) {
397                            $where .= " AND ({$statusName} IN ('{$val}') OR {$statusName} IS NULL) ";
398                        } else {
399                            $where .= " AND {$statusName} IN ('{$val}') ";
400                        }
401                    }
402                }
403            }
404
405            $offset = $start;
406            $limit = $end - $start;
407
408            $subquery = ",(SELECT can_write FROM tbl_company_users WHERE company_id = a.company_id AND user_id = {$userId}) can_write";
409
410            $query = "SELECT 
411                        a.id,
412                        a.region, 
413                        a.company_id,
414                        b.name company_name,
415                        a.registration_date,
416                        a.brand, 
417                        a.model,
418                        a.vehicle_type,
419                        a.license_plate,
420                        a.mileage,
421                        a.last_itv_date,
422                        a.next_itv_date,
423                        a.mileage_threshold,
424                        a.driver,
425                        a.responsible_name,
426                        a.responsible_email,
427                        a.created_by,
428                        a.created_at,
429                        a.updated_by,
430                        a.updated_at,
431                        a.is_due,
432                        a.is_due_appointment,
433                        CASE
434                            WHEN DATEDIFF(a.next_itv_date, NOW()) <= 0 THEN 0
435                            WHEN DATEDIFF(a.next_itv_date, NOW()) <= 1 THEN 1
436                            WHEN DATEDIFF(a.next_itv_date, NOW()) <= 15 THEN 15
437                            WHEN DATEDIFF(a.next_itv_date, NOW()) <= 30 THEN 30
438                        ELSE NULL
439                        END AS next_itv_date_due,
440                        CASE
441                            WHEN DATEDIFF(a.appointment_date, NOW()) <= 0 THEN 0
442                            WHEN DATEDIFF(a.appointment_date, NOW()) <= 1 THEN 1
443                            WHEN DATEDIFF(a.appointment_date, NOW()) <= 7 THEN 7
444                        ELSE NULL
445                        END AS appointment_date_due,
446                        a.comments,
447                        a.location,
448                        a.is_booked,
449                        a.appointment_time,
450                        a.appointment_date,
451                        DATE_FORMAT(a.registration_date, '%m/%Y') registration_date_translate,
452                        DATE_FORMAT(a.last_itv_date, '%d/%m/%Y') last_itv_date_translate,
453                        DATE_FORMAT(a.next_itv_date, '%m/%Y') next_itv_date_translate,
454                        DATE_FORMAT(a.created_at, '%d/%m/%Y') created_at_translate,
455                        DATE_FORMAT(a.updated_at, '%d/%m/%Y') updated_at_translate,
456                        DATE_FORMAT(a.appointment_date, '%d/%m/%Y') appointment_date_translate
457                        {$matchScoreCol}
458                        {$subquery}
459                    FROM 
460                        tbl_itv a 
461                        LEFT JOIN tbl_companies b ON a.company_id = b.company_id
462                    WHERE a.id > 0 {$where}
463                    GROUP BY a.id
464                    {$orderBy}
465                    LIMIT {$offset}, {$limit}
466                    ";
467            // return $query;
468            $value = Cache::get(base64_encode($query));
469
470            if (! $value) {
471                $result = DB::select($query);
472
473                Cache::put(base64_encode($query), $result, 600);
474            } else {
475                $result = $value;
476            }
477
478            $totalQuery = "SELECT 
479                            COUNT(a.id) totalRowCount
480                        FROM 
481                            tbl_itv a 
482                            LEFT JOIN tbl_companies b ON a.company_id = b.company_id 
483                        WHERE a.id > 0
484                        {$where}";
485
486            $value = Cache::get(base64_encode($totalQuery));
487
488            if (! $value) {
489                $countQuery = DB::select($totalQuery);
490
491                Cache::put(base64_encode($totalQuery), $countQuery, 600);
492            } else {
493                $countQuery = $value;
494            }
495
496            return response([
497                'message' => 'OK',
498                'data' => $result,
499                'totalRowCount' => $countQuery[0]->totalRowCount,
500            ]);
501
502        } catch (\Exception $e) {
503            return response(['message' => 'KO', 'error' => $e->getMessage()]);
504        }
505
506    }
507
508    public function get_dates(): ResponseFactory|Response{
509
510        try {
511
512            $query = "SELECT
513                        DATE_FORMAT(a.last_itv_date, '%d/%m/%Y') last_itv_date_translate,
514                        DATE_FORMAT(a.next_itv_date, '%d/%m/%Y') next_itv_date_translate,
515                        DATE_FORMAT(a.created_at, '%d/%m/%Y') created_at_translate,
516                        DATE_FORMAT(a.updated_at, '%d/%m/%Y') updated_at_translate
517                    FROM tbl_itv a";
518
519            $result = DB::select($query);
520
521            return response([
522                'message' => 'OK',
523                'data' => $result,
524            ]);
525
526        } catch (\Exception $e) {
527            return response(['message' => 'KO', 'error' => $e->getMessage()]);
528        }
529
530    }
531
532    function update_itv(Request $request, $id): ResponseFactory|Response{
533
534        try {
535            
536            $id = addslashes((string) $id);
537            $data = $request->all();
538
539            $files = $request->file('files');
540            unset($data['files']);
541
542            $itv = TblItv::where('id', $id)->first();
543
544            $isSendItv = 0;
545            $daysDifference = 0;
546            $duration = 0;
547            $isBooked = $data['is_booked'];
548
549            if ($data['is_booked'] == 0 || $data['is_booked'] == null) {
550                if (isset($data['next_itv_date'])) {
551                    $incommingDate = strtotime($data['next_itv_date']);
552                    $currentDate = strtotime(date('Y-m-d'));
553                    $secondsDifference = abs($incommingDate - $currentDate);
554                    $daysDifference = floor($secondsDifference / (60 * 60 * 24));
555
556                    $duration = 99999999;
557
558                    if ($daysDifference >= 15 && $daysDifference <= 30) {
559                        $duration = 30;
560                    } elseif ($daysDifference > 1 && $daysDifference <= 15) {
561                        $duration = 15;
562                    } elseif ($daysDifference == 1) {
563                        $duration = 1;
564                    }
565
566                    if ($daysDifference <= 30) {
567                        if ($itv->is_due != $duration) {
568                            $isSendItv = 1;
569                        }
570                    } else {
571                        $data['is_due'] = null;
572                    }
573                }
574            } elseif ($data['is_booked'] == 1) {
575                if (isset($data['appointment_date'])) {
576                    $incommingDate = strtotime($data['appointment_date']);
577                    $currentDate = strtotime(date('Y-m-d'));
578                    $secondsDifference = abs($incommingDate - $currentDate);
579                    $daysDifference = floor($secondsDifference / (60 * 60 * 24));
580
581                    $duration = 99999999;
582
583                    if ($daysDifference > 1 && $daysDifference <= 7) {
584                        $duration = 7;
585                    } elseif ($daysDifference == 1) {
586                        $duration = 1;
587                    }
588
589                    if ($daysDifference <= 7) {
590                        if ($itv->is_due_appointment != $duration) {
591                            $isSendItv = 1;
592                        }
593                    } else {
594                        $data['is_due_appointment'] = null;
595                    }
596                }
597            }
598
599            $isSendItvMileage = 0;
600            $mileageDuration = 0;
601            if (isset($data['mileage']) && isset($data['mileage_threshold'])) {
602                if (($data['mileage'] > 0 && $data['mileage'] != null) && ($data['mileage_threshold'] > 0 && $data['mileage_threshold'] != null)) {
603                    $mileage = floor($data['mileage']);
604                    $mileageThreshold = floor($data['mileage_threshold']);
605
606                    if ($mileage < $mileageThreshold) {
607                        $mileageDuration = abs($mileage - $mileageThreshold);
608
609                        if ($mileageDuration <= 3000) {
610                            if ($itv->is_due_mileage == null) {
611                                $isSendItvMileage = 1;
612                            }
613                        }
614                    }
615                }
616            }
617
618            $fileCount = TblItvFiles::where('itv_id', $id)->count();
619
620            if ($files) {
621                $totalFileCount = $fileCount + count($files);
622                if ($totalFileCount > 3) {
623                    return response(['message' => 'KO', 'error' => __('language.file_count_exceeded')]);
624                }
625            }
626
627            $data['updated_at'] = date('Y-m-d H:i:s');
628            TblItv::where('id', $id)->update($data);
629
630            if ($isSendItv == 1) {
631                $this->email_reminders($duration, $id, $isBooked);
632            }
633
634            if ($isSendItvMileage == 1) {
635                $this->email_reminders_mileage($mileageDuration, $id);
636            }
637
638            if ($files) {
639
640                $directory = 'public/uploads';
641                $uploadedFiles = [];
642                $i = 0;
643
644                $combinedFilesSize = 0;
645                foreach ($files as $file) {
646                    $i++;
647                    $origFilename = str_replace(' ', '', $file->getClientOriginalName());
648                    $filename = $id.'-ITV'.time().'-'.$origFilename;
649
650                    $combinedFilesSize = $combinedFilesSize + $file->getSize();
651
652                    if ($combinedFilesSize > 25000000) {
653                        return response(['message' => 'KO', 'error' => __('language.file_size_exceeded')]);
654                    }
655
656                    Storage::putFileAs($directory, $file, $filename);
657                    Storage::disk('google')->put($filename, file_get_contents(storage_path().'/app/public/uploads/'.$filename));
658
659                    if (in_array($origFilename, $uploadedFiles)) {
660                        $origFilename = $origFilename.$i;
661                    }
662
663                    TblItvFiles::create(
664                        [
665                            'itv_id' => $id,
666                            'original_name' => $origFilename,
667                            'filename' => $filename,
668                            'uploaded_by' => $data['updated_by']
669                        ]
670                    );
671
672                    $uploadedFiles[] = $file->getClientOriginalName();
673                }
674            }
675
676            Cache::flush();
677
678            return response([
679                'message' => 'OK', $daysDifference, $itv->is_due, $duration,
680            ]);
681
682        } catch (\Exception $e) {
683            return response(['message' => 'KO', 'error' => $e->getMessage()]);
684        }
685
686    }
687
688    function create_itv(Request $request): ResponseFactory|Response{
689
690        try {
691
692            $data = $request->all();
693
694            $files = $request->file('files');
695            unset($data['files']);
696
697            $result = TblItv::create($data);
698
699            $id = $result->id;
700
701            $isBooked = $data['is_booked'];
702
703            if ($data['is_booked'] == 0 || $data['is_booked'] == null) {
704                if (isset($data['next_itv_date'])) {
705                    $incommingDate = strtotime($data['next_itv_date']);
706                    $currentDate = strtotime(date('Y-m-d'));
707                    $secondsDifference = abs($incommingDate - $currentDate);
708                    $daysDifference = floor($secondsDifference / (60 * 60 * 24));
709
710                    $duration = 99999999;
711
712                    if ($daysDifference >= 15 && $daysDifference <= 30) {
713                        $duration = 30;
714                    } elseif ($daysDifference > 1 && $daysDifference <= 15) {
715                        $duration = 15;
716                    } elseif ($daysDifference == 1) {
717                        $duration = 1;
718                    }
719
720                    if ($daysDifference <= 30) {
721                        $this->email_reminders($duration, $id, $isBooked);
722                    }
723                }
724            } elseif ($data['is_booked'] == 1) {
725                if (isset($data['appointment_date'])) {
726                    $incommingDate = strtotime($data['appointment_date']);
727                    $currentDate = strtotime(date('Y-m-d'));
728                    $secondsDifference = abs($incommingDate - $currentDate);
729                    $daysDifference = floor($secondsDifference / (60 * 60 * 24));
730
731                    $duration = 99999999;
732
733                    if ($daysDifference > 1 && $daysDifference <= 7) {
734                        $duration = 7;
735                    } elseif ($daysDifference == 1) {
736                        $duration = 1;
737                    }
738
739                    if ($daysDifference <= 7) {
740                        if ($result->is_due_appointment != $duration) {
741                            $this->email_reminders($duration, $id, $isBooked);
742                        }
743                    } else {
744                        $data['is_due_appointment'] = null;
745                    }
746                }
747            }
748
749            $mileageDuration = 0;
750            if (isset($data['mileage']) && isset($data['mileage_threshold'])) {
751                if (($data['mileage'] > 0 && $data['mileage'] != null) && ($data['mileage_threshold'] > 0 && $data['mileage_threshold'] != null)) {
752                    $mileage = floor($data['mileage']);
753                    $mileageThreshold = floor($data['mileage_threshold']);
754
755                    $mileageDuration = abs($mileage - $mileageThreshold);
756
757                    if ($mileageDuration <= 3000) {
758                        $this->email_reminders_mileage($mileageDuration, $id);
759                    }
760                }
761            }
762
763            if ($files) {
764
765                $directory = 'public/uploads';
766                $uploadedFiles = [];
767                $i = 0;
768
769                $combinedFilesSize = 0;
770                foreach ($files as $file) {
771                    $i++;
772                    $origFilename = str_replace(' ', '', $file->getClientOriginalName());
773                    $filename = $id.'-ITV'.time().'-'.$origFilename;
774
775                    $combinedFilesSize = $combinedFilesSize + $file->getSize();
776
777                    if ($combinedFilesSize > 25000000) {
778                        return response(['message' => 'KO', 'error' => __('language.file_size_exceeded')]);
779                    }
780
781                    Storage::putFileAs($directory, $file, $filename);
782                    Storage::disk('google')->put($filename, file_get_contents(storage_path().'/app/public/uploads/'.$filename));
783
784                    if (in_array($origFilename, $uploadedFiles)) {
785                        $origFilename = $origFilename.$i;
786                    }
787
788                    TblItvFiles::create(
789                        [
790                            'itv_id' => $id,
791                            'original_name' => $origFilename,
792                            'filename' => $filename,
793                            'uploaded_by' => $data['created_by']
794                        ]
795                    );
796
797                    $uploadedFiles[] = $file->getClientOriginalName();
798                }
799            }
800
801            Cache::flush();
802
803            return response([
804                'message' => 'OK',
805            ]);
806
807        } catch (\Exception $e) {
808            return response(['message' => 'KO', 'error' => $e->getMessage()]);
809        }
810
811    }
812
813    public function delete_itv(Request $request): ResponseFactory|Response{
814
815        try {
816
817            $data = $request->all();
818            $result = [];
819
820            $r = new Request([
821                'filterModel' => $data['filterModel'],
822                'sortModel' => $data['sortModel'],
823                'start' => 0,
824                'end' => 999999999,
825                'company_id' => $data['company_id'],
826                'user_id' => $data['user_id'],
827                'ids' => $data['ids'],
828                'searchText' => $data['searchText'],
829                'ids_not_in' => $data['ids_not_in'],
830            ]);
831
832            $result = $this->list_itv($r);
833            $result = $result->original['data'];
834
835            $outputArray = [];
836
837            foreach ($result as $item) {
838                if (isset($item->id)) {
839                    $outputArray[] = $item->id;
840                }
841            }
842
843            TblItv::whereIn('id', $outputArray)->delete();
844
845            Cache::flush();
846
847            return response(['message' => 'OK', 'data' => $result]);
848
849        } catch (\Exception $e) {
850            return response(['message' => 'KO', 'error' => $e->getMessage()]);
851        }
852
853    }
854
855    function get_itv($id): ResponseFactory|Response{
856
857        try {
858
859            $id = addslashes((string) $id);
860
861            $query = "SELECT 
862                        a.id,
863                        a.region, 
864                        a.company_id,
865                        b.name company_name,
866                        a.registration_date,
867                        a.brand, 
868                        a.model,
869                        a.vehicle_type,
870                        a.license_plate,
871                        a.mileage,
872                        a.last_itv_date,
873                        a.next_itv_date,
874                        a.mileage_threshold,
875                        a.driver,
876                        a.responsible_name,
877                        a.responsible_email,
878                        a.created_by,
879                        a.created_at,
880                        a.updated_by,
881                        a.updated_at,            
882                        a.is_due,
883                        a.is_due_appointment,
884                        a.location,
885                        a.is_booked,
886                        a.appointment_time,
887                        a.appointment_date,
888                        DATE_FORMAT(a.registration_date, '%m/%Y') registration_date_translate,
889                        DATE_FORMAT(a.last_itv_date, '%d/%m/%Y') last_itv_date_translate,
890                        DATE_FORMAT(a.next_itv_date, '%m/%Y') next_itv_date_translate,
891                        DATE_FORMAT(a.created_at, '%d/%m/%Y') created_at_translate,
892                        DATE_FORMAT(a.updated_at, '%d/%m/%Y') updated_at_translate,
893                        DATE_FORMAT(a.appointment_date, '%d/%m/%Y') appointment_date_translate,
894                        CASE
895                            WHEN DATEDIFF(a.next_itv_date, NOW()) <= 0 THEN 0
896                            WHEN DATEDIFF(a.next_itv_date, NOW()) <= 1 THEN 1
897                            WHEN DATEDIFF(a.next_itv_date, NOW()) <= 15 THEN 15
898                            WHEN DATEDIFF(a.next_itv_date, NOW()) <= 30 THEN 30
899                        ELSE NULL
900                        END AS next_itv_date_due,
901                        CASE
902                            WHEN DATEDIFF(a.appointment_date, NOW()) <= 0 THEN 0
903                            WHEN DATEDIFF(a.appointment_date, NOW()) <= 1 THEN 1
904                            WHEN DATEDIFF(a.appointment_date, NOW()) <= 7 THEN 7
905                        ELSE NULL
906                        END AS appointment_date_due,
907                        a.comments
908                    FROM 
909                        tbl_itv a 
910                        LEFT JOIN tbl_companies b ON a.company_id = b.company_id
911                    WHERE a.id = {$id}";
912
913            $result = DB::select($query);
914
915            Cache::flush();
916
917            return response(['message' => 'OK', 'data' => $result]);
918
919        } catch (\Exception $e) {
920            return response(['message' => 'KO', 'error' => $e->getMessage()]);
921        }
922    }
923
924    function human_filesize($bytes, $decimals = 2): string {
925        $size = ['B', 'KB', 'MB'];
926    
927        $factor = floor((strlen($bytes) - 1) / 3);
928        return number_format($bytes / 1024 ** $factor, 2, ',', '.') . $size[$factor];
929    }        
930
931    function get_itv_files($id): ResponseFactory|Response{
932
933        try {
934            
935            $id = addslashes((string) $id);
936
937            $result = TblItvFiles::where('itv_id', $id)->get();
938
939            foreach ($result as $key => $value) {
940                $path = storage_path('app/public/uploads/'.$result[$key]->filename);
941
942                if (\File::exists($path)) {
943                    $fileSizeBytes = \File::size($path);
944                    $result[$key]->setAttribute('filesize', $this->human_filesize($fileSizeBytes));
945                    $result[$key]->original_name = $result[$key]->original_name." ({$result[$key]->getAttribute('filesize')})";
946                }
947            }
948
949            $emailLogs = TblItvEmailLogs::where('itv_id', $id)->get();
950
951            return response(['message' => 'OK', 'data' => $result, 'itvEmailLogs' => $emailLogs]);
952
953        } catch (\Exception $e) {
954            return response(['message' => 'KO', 'error' => $e->getMessage()]);
955        }
956    }
957
958    function delete_itv_file($fileId): ResponseFactory|Response{
959
960        try {
961            
962            $fileId = addslashes((string) $fileId);
963            $file = TblItvFiles::where('file_id', $fileId)->first();
964            $result = TblItvFiles::where('file_id', $fileId)->first();
965
966            if ($result) {
967                TblItvFiles::where('file_id', $fileId)->delete();
968                $path = storage_path('app/public/uploads/'.$result->filename);
969                Storage::disk('public')->delete('uploads/'.$result->filename);
970            }
971
972            return response(['message' => 'OK']);
973
974        } catch (\Exception $e) {
975            return response(['message' => 'KO', 'error' => $e->getMessage()]);
976        }
977    }
978
979    function download_itv_file($fileId): ResponseFactory|Response{
980
981        try {
982            
983            $fileId = addslashes((string) $fileId);
984
985            $result = TblItvFiles::where('file_id', $fileId)->first();
986
987            if ($result) {
988                $path = storage_path('app/public/uploads/'.$result->filename);
989
990                if (! Storage::disk('public')->exists('uploads/'.$result->filename)) {
991                    return response(['message' => 'KO']);
992                }
993
994                return response()->download($path);
995            }
996
997            return response(['message' => 'KO']);
998
999        } catch (\Exception $e) {
1000            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1001        }
1002
1003    }
1004
1005    function isEmailValid($email): bool {
1006        // Regular expression pattern for email validation
1007        $pattern = '/^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$/';
1008
1009        // Check if the email matches the pattern
1010        if (preg_match($pattern, (string) $email)) {
1011            return true; // Valid email
1012        } else {
1013            return false; // Invalid email
1014        }
1015    }
1016
1017    public function email_reminders($duration, $id, $isBooked = 0)
1018    {
1019
1020        $item = TblItv::where('id', $id)->first();
1021
1022        $due = '';
1023        $subject = __('language.itv_reminders.subject');
1024        $body = '';
1025        $footer = __('language.itv_reminders.body_message_footer');
1026
1027        if ($isBooked == 0) {
1028            if ($duration == 30) {
1029                $due = __('language.itv_reminders.in_one_month');
1030            } elseif ($duration == 15) {
1031                $due = __('language.itv_reminders.in_15_days');
1032            } elseif ($duration == 1) {
1033                $due = __('language.itv_reminders.tomorrow');
1034                $subject = __('language.itv_reminders.subject_urgent');
1035                $footer = __('language.itv_reminders.body_message_footer_urgent');
1036            } else {
1037                return false;
1038            }
1039        } elseif ($isBooked == 1) {
1040            if ($duration == 7) {
1041                $due = __('language.itv_reminders.app_7_days');
1042                $subject = __('language.itv_reminders.subject_7_days');
1043                $footer = __('language.itv_reminders.body_message_footer_appointment');
1044            } elseif ($duration == 1) {
1045                $due = __('language.itv_reminders.app_tomorrow');
1046                $subject = __('language.itv_reminders.subject_tomorrow');
1047                $footer = __('language.itv_reminders.body_message_footer_appointment_urgent');
1048            } else {
1049                return false;
1050            }
1051        }
1052
1053        $email = new \SendGrid\Mail\Mail;
1054
1055        $user = TblUsers::where('id', $this->userId)->first();
1056        $userName = $user->name;
1057
1058        $addTo = [];
1059
1060        if(config('services.sendgrid.staging')){
1061            $email->addTo($user->email);
1062            array_push($addTo, $user->email);
1063        } else {
1064
1065            $user = TblUsers::where('name', $item->created_by)->first();
1066            $email->addTo($user->email);
1067            array_push($addTo, $user->email);
1068
1069            if ($item->created_by != $item->responsible_name) {
1070                $isValid = $this->isEmailValid($item->responsible_email);
1071                if ($isValid) {
1072                    $email->addTo($item->responsible_email);
1073                    array_push($addTo, $item->responsible_email);
1074                } else {
1075                    TblItvEmailLogs::create(
1076                        [
1077                            'itv_id' => $item->id,
1078                            'email' => $item->responsible_email,
1079                            'sent_by' => $userName,
1080                            'status' => 'Invalid email'
1081                        ]
1082                    );  
1083                }
1084            }
1085        }
1086
1087        $subject = str_replace('{{brand}}', $item->brand, $subject);
1088        $subject = str_replace('{{license_plate}}', $item->license_plate, $subject);
1089
1090        $email->setFrom('fire@fire.es', 'Fire Service Titan');
1091        $email->setSubject($subject);
1092
1093        $imgpath = \File::get('fireservicetitan.png');
1094
1095        $email->addAttachment(
1096            $imgpath,
1097            'image/png',
1098            'fireservicetitan.png',
1099            'inline',
1100            'fireservicetitan'
1101        );
1102
1103        $url = config('app.frontend_url') . "itv/{$item->id}";
1104
1105        $bodyHeaders = __('language.itv_reminders.body_message_header');
1106
1107        if ($isBooked == 1) {
1108            $bodyHeaders = __('language.itv_reminders.body_message_header_appointment');
1109        }
1110
1111        $body .= __('language.itv_reminders.body_hello');
1112        $body = str_replace('{{responsible_name}}', $item->responsible_name, $body);
1113
1114        $body .= $bodyHeaders;
1115        $body = str_replace('{{duration}}', $due, $body);
1116        $body = str_replace('{{click}}', $url, $body);
1117
1118        $body .= __('language.itv_reminders.body_vehicle');
1119        $body = str_replace('{{vehicle}}', $item->brand, $body);
1120
1121        $body .= __('language.itv_reminders.body_license_plate');
1122        $body = str_replace('{{license_plate}}', $item->license_plate, $body);
1123
1124        $body .= __('language.itv_reminders.body_vehicle_type');
1125        $body = str_replace('{{vehicle_type}}', $item->vehicle_type, $body);
1126
1127        if ($isBooked == 1) {
1128            $body .= __('language.itv_reminders.body_appointment_date');
1129            $item->appointment_date_translate = date("d/m/Y", strtotime((string) $item->appointment_date));
1130            $body = str_replace('{{appointment_date}}', $item->appointment_date_translate, $body);
1131    
1132            $body .= __('language.itv_reminders.body_appointment_time');
1133            $formattedTime = date("g:i A", strtotime((string) $item->appointment_time));
1134            $body = str_replace('{{appointment_time}}', $formattedTime, $body);
1135
1136            $body .= __('language.itv_reminders.body_location');
1137            $body = str_replace('{{location}}', $item->location, $body);
1138        } else {
1139            $body .= __('language.itv_reminders.body_next_itv_date');
1140            $item->next_itv_date_translate = date("m/Y", strtotime((string) $item->next_itv_date));
1141            $body = str_replace('{{next_itv_date}}', $item->next_itv_date_translate, $body);
1142    
1143            $body .= __('language.itv_reminders.body_driver');
1144            $body = str_replace('{{driver}}', $item->driver, $body);
1145        }
1146
1147        $body .= $footer;
1148
1149        $body .= '<p>Fire Service Titan</p>';
1150        $body .= "<img src='cid:fireservicetitan' style='height: 45px;' />";
1151
1152        $html = '<!DOCTYPE html>';
1153        $html .= '<html>';
1154        $html .= '<head>';
1155        $html .= '<meta charset="UTF-8">';
1156        $html .= '<meta name="viewport" content="width=device-width, initial-scale=1.0">';
1157        $html .= '</head>';
1158        $html .= '<body>';
1159        $html .= $body;
1160        $html .= '</body>';
1161        $html .= '</html>';
1162
1163        $email->addContent('text/html', $html);
1164
1165        $sendgrid = new \SendGrid(config('services.sendgrid.api_key'));
1166
1167        $response = $sendgrid->send($email);
1168        if ($response->statusCode() == 202) {
1169
1170            foreach ($addTo as $addToEmail) {
1171                TblItvEmailLogs::create(
1172                    [
1173                        'itv_id' => $item->id,
1174                        'email' => $addToEmail,
1175                        'sent_by' => $userName,
1176                        'status' => 'OK'
1177                    ]
1178                );  
1179            }
1180            
1181            $toUpdate = ['is_due' => $duration, 'updated_by' => $userName, 'updated_at' => date('Y-m-d H:i:s')];
1182
1183            if($isBooked == 1){
1184                $toUpdate = ['is_due_appointment' => $duration, 'updated_by' => $userName, 'updated_at' => date('Y-m-d H:i:s')];
1185            }
1186
1187            TblItv::where('id', $item->id)->update($toUpdate);
1188        } else {
1189            foreach ($addTo as $addToEmail) {
1190                TblItvEmailLogs::create(
1191                    [
1192                        'itv_id' => $item->id,
1193                        'email' => $addToEmail,
1194                        'sent_by' => $userName,
1195                        'status' => $response->body()
1196                    ]
1197                );    
1198            }
1199        }
1200    }
1201
1202    function email_reminders_mileage($duration, $id): void{
1203
1204        $item = TblItv::where('id', $id)->first();
1205
1206        $body = '';
1207        $subject = '';
1208
1209        $email = new \SendGrid\Mail\Mail;
1210
1211        $user = TblUsers::where('id', $this->userId)->first();
1212        $userName = $user->name;
1213
1214        $addTo = [];
1215
1216        if(config('services.sendgrid.staging')){
1217            $email->addTo($user->email);
1218            array_push($addTo, $user->email);
1219        } else {
1220
1221            $user = TblUsers::where('name', $item->created_by)->first();
1222            $email->addTo($user->email);
1223            array_push($addTo, $user->email);
1224
1225            if ($item->created_by != $item->responsible_name) {
1226                $isValid = $this->isEmailValid($item->responsible_email);
1227                if ($isValid) {
1228                    $email->addTo($item->responsible_email);
1229                    array_push($addTo, $item->responsible_email);
1230                } else {
1231                    TblItvEmailLogs::create(
1232                        [
1233                            'itv_id' => $item->id,
1234                            'email' => $item->responsible_email,
1235                            'sent_by' => $userName,
1236                            'status' => 'Invalid email'
1237                        ]
1238                    );
1239                }
1240            }
1241        }
1242
1243        $subject = __('language.itv_reminders_km.subject');
1244        $subject = str_replace('{{brand}}', $item->brand, $subject);
1245        $subject = str_replace('{{license_plate}}', $item->license_plate, $subject);
1246
1247        $email->setFrom('fire@fire.es', 'Fire Service Titan');
1248        $email->setSubject($subject);
1249
1250        $imgpath = \File::get('fireservicetitan.png');
1251
1252        $email->addAttachment(
1253            $imgpath,
1254            'image/png',
1255            'fireservicetitan.png',
1256            'inline',
1257            'fireservicetitan'
1258        );
1259
1260        $url = config('app.frontend_url') . "itv/{$item->id}";
1261
1262        $body .= __('language.itv_reminders_km.body_hello');
1263        $body = str_replace('{{responsible_name}}', $item->responsible_name, $body);
1264
1265        $body .= __('language.itv_reminders_km.body_message_header');
1266        $body = str_replace('{{click}}', $url, $body);
1267
1268        $body .= __('language.itv_reminders_km.body_vehicle');
1269        $body = str_replace('{{vehicle}}', $item->brand, $body);
1270
1271        $body .= __('language.itv_reminders_km.body_license_plate');
1272        $body = str_replace('{{license_plate}}', $item->license_plate, $body);
1273
1274        $body .= __('language.itv_reminders.body_vehicle_type');
1275        $body = str_replace('{{vehicle_type}}', $item->vehicle_type, $body);
1276
1277        $body .= __('language.itv_reminders_km.body_mileage');
1278        $body = str_replace('{{mileage}}', number_format($item->mileage, 2, ',', '.'), $body);
1279
1280        $body .= __('language.itv_reminders_km.body_mileage_threshold');
1281        $body = str_replace('{{mileage_threshold}}', number_format($item->mileage_threshold, 2, ',', '.'), $body);
1282
1283        $body .= __('language.itv_reminders_km.body_message_footer');
1284
1285        $body .= '<p>Fire Service Titan</p>';
1286        $body .= "<img src='cid:fireservicetitan' style='height: 45px;' />";
1287
1288        $html = '<!DOCTYPE html>';
1289        $html .= '<html>';
1290        $html .= '<head>';
1291        $html .= '<meta charset="UTF-8">';
1292        $html .= '<meta name="viewport" content="width=device-width, initial-scale=1.0">';
1293        $html .= '</head>';
1294        $html .= '<body>';
1295        $html .= $body;
1296        $html .= '</body>';
1297        $html .= '</html>';
1298
1299        $email->addContent('text/html', $html);
1300
1301        $sendgrid = new \SendGrid(config('services.sendgrid.api_key'));
1302
1303        $response = $sendgrid->send($email);
1304        if ($response->statusCode() == 202) {
1305
1306            foreach ($addTo as $addToEmail) {
1307                TblItvEmailLogs::create(
1308                    [
1309                        'itv_id' => $item->id,
1310                        'email' => $addToEmail,
1311                        'sent_by' => $userName,
1312                        'status' => 'OK'
1313                    ]
1314                );  
1315            }
1316            
1317            TblItv::where('id', $item->id)->update(['is_due_mileage' => $duration, 'updated_by' => $userName, 'updated_at' => date('Y-m-d H:i:s')]);
1318        }else{
1319            foreach ($addTo as $addToEmail) {
1320                TblItvEmailLogs::create(
1321                    [
1322                        'itv_id' => $item->id,
1323                        'email' => $addToEmail,
1324                        'sent_by' => $userName,
1325                        'status' => $response->body()
1326                    ]
1327                );    
1328            }
1329        }
1330    }
1331
1332
1333    function get_distincts(): ResponseFactory|Response{
1334
1335        try {
1336
1337            $query = 'SELECT DISTINCT region FROM tbl_itv ORDER BY region ASC';
1338            $regions = DB::select($query);
1339
1340            $query = 'SELECT DISTINCT brand FROM tbl_itv ORDER BY brand ASC';
1341            $brands = DB::select($query);
1342
1343            $query = 'SELECT 
1344                        DISTINCT b.name 
1345                    FROM tbl_itv a 
1346                    LEFT JOIN tbl_companies b
1347                        ON a.company_id = b.company_id
1348                    ORDER BY b.name ASC';
1349
1350            $companies = DB::select($query);
1351
1352            $query = 'SELECT DISTINCT responsible_name FROM tbl_itv ORDER BY responsible_name ASC';
1353            $responsibleNames = DB::select($query);
1354
1355            $query = 'SELECT DISTINCT driver FROM tbl_itv ORDER BY driver ASC';
1356            $drivers = DB::select($query);
1357
1358            return response([
1359                'message' => 'OK',
1360                'regions' => $regions,
1361                'brands' => $brands,
1362                'companies' => $companies,
1363                'responsibleNames' => $responsibleNames,
1364                'drivers' => $drivers,
1365            ]);
1366
1367        } catch (\Exception $e) {
1368            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1369        }
1370
1371    }
1372
1373    function get_all_users($companyId): ResponseFactory|Response{
1374
1375        try {
1376            
1377            $companyId = addslashes((string) $companyId);
1378
1379            $companyId = intval($companyId);
1380
1381            $where = '';
1382
1383            if ($companyId != 0) {
1384                $where = "WHERE company_id = {$companyId} ";
1385            } else {
1386                $where = "WHERE company_id IN ({$this->companyId}) ";
1387            }
1388
1389            $query = "SELECT 
1390                        DISTINCT responsible_name 
1391                    FROM 
1392                        tbl_itv
1393                    {$where}
1394                    ORDER BY 
1395                    responsible_name ASC";
1396
1397            $responsibleNames = DB::select($query);
1398
1399            return response([
1400                'message' => 'OK',
1401                'responsibleNames' => $responsibleNames,
1402            ]);
1403
1404        } catch (\Exception $e) {
1405            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1406        }
1407    }
1408
1409    function download_itv(Request $request): ResponseFactory|Response{
1410        ini_set('max_execution_time', 123456);
1411        $data = $request->all();
1412        $companyId = addslashes((string) $data['company_id']);
1413        $userId = addslashes((string) $data['user_id']);
1414
1415        $r = new Request([
1416            'filterModel' => $data['filterModel'],
1417            'sortModel' => $data['sortModel'],
1418            'start' => 0,
1419            'end' => 999999999,
1420            'company_id' => $data['company_id'],
1421            'user_id' => $data['user_id'],
1422            'ids' => $data['ids'],
1423            'searchText' => $data['searchText'],
1424            'ids_not_in' => $data['ids_not_in'],
1425        ]);
1426
1427        $result = $this->list_itv($r);
1428        $result = $result->original['data'];
1429        return response(['data' => $result]);
1430    }
1431}