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