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