Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
0.00%
0 / 215
0.00%
0 / 4
 CRAP
0.00%
0 / 1
ItvEmailReminders
0.00%
0 / 215
0.00%
0 / 4
 870
0.00%
0 / 1
 handle
0.00%
0 / 6
0.00%
0 / 1
 6
 isEmailValid
0.00%
0 / 4
0.00%
0 / 1
 6
 email_reminders
0.00%
0 / 125
0.00%
0 / 1
 306
 email_reminders_mileage
0.00%
0 / 80
0.00%
0 / 1
 72
1<?php
2
3namespace App\Console\Commands;
4
5use App\Models\TblItv;
6use App\Models\TblUsers;
7use App\Services\ResultCache;
8use App\Services\SendgridLogger;
9use Illuminate\Console\Command;
10use Illuminate\Support\Facades\DB;
11use Illuminate\Support\Facades\Log;
12use SendGrid\Mail\Mail;
13
14class ItvEmailReminders extends Command
15{
16    /**
17     * The name and signature of the console command.
18     *
19     * @var string
20     */
21    protected $signature = 'itv:reminders {duration} {is_booked}';
22
23    /**
24     * The console command description.
25     *
26     * @var string
27     */
28    protected $description = 'Email reminders before ITV required';
29
30    /**
31     * Execute the console command.
32     */
33    public function handle(): void
34    {
35        try {
36
37            $duration = $this->argument('duration');
38            $isBooked = $this->argument('is_booked');
39
40            $this->email_reminders($duration, $isBooked);
41            $this->email_reminders_mileage($duration);
42
43        } catch (\Exception $e) {
44            Log::channel('itv_reminders')->error($e->getMessage());
45        }
46    }
47
48    public function isEmailValid($email): bool
49    {
50        // Regular expression pattern for email validation
51        $pattern = '/^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$/';
52
53        // Check if the email matches the pattern
54        if (preg_match($pattern, (string) $email)) {
55            return true; // Valid email
56        } else {
57            return false; // Invalid email
58        }
59    }
60
61    public function email_reminders($duration, $isBooked)
62    {
63
64        $where = '';
65        $due = '';
66        $body = '';
67        $subjectM = __('language.itv_reminders.subject');
68        $footer = __('language.itv_reminders.body_message_footer');
69
70        if ($isBooked == 0) {
71            if ($duration == 30) {
72                $where = 'DATE(a.next_itv_date) = DATE_ADD(CURDATE(), INTERVAL 30 DAY)';
73                $due = __('language.itv_reminders.in_one_month');
74            } elseif ($duration == 15) {
75                $where = 'DATE(a.next_itv_date) = DATE_ADD(CURDATE(), INTERVAL 15 DAY)';
76                $due = __('language.itv_reminders.in_15_days');
77            } elseif ($duration == 1) {
78                $where = 'DATE(a.next_itv_date) = DATE_ADD(CURDATE(), INTERVAL 1 DAY)';
79                $due = __('language.itv_reminders.tomorrow');
80                $subjectM = __('language.itv_reminders.subject_urgent');
81                $footer = __('language.itv_reminders.body_message_footer_urgent');
82            } else {
83                return response(['message' => 'KO']);
84            }
85            $where .= ' AND a.is_due IS NULL ';
86        } elseif ($isBooked == 1) {
87            if ($duration == 7) {
88                $where = 'DATE(a.appointment_date) = DATE_ADD(CURDATE(), INTERVAL 7 DAY)';
89                $due = __('language.itv_reminders.app_7_days');
90                $subjectM = __('language.itv_reminders.subject_7_days');
91                $footer = __('language.itv_reminders.body_message_footer_appointment_urgent');
92            } elseif ($duration == 1) {
93                $where = 'DATE(a.appointment_date) = DATE_ADD(CURDATE(), INTERVAL 1 DAY)';
94                $due = __('language.itv_reminders.app_tomorrow');
95                $subjectM = __('language.itv_reminders.subject_tomorrow');
96                $footer = __('language.itv_reminders.body_message_footer_appointment_urgent');
97            } else {
98                return response(['message' => 'KO']);
99            }
100            $where .= ' AND a.is_due_appointment IS NULL ';
101        }
102
103        $query = "SELECT 
104                    a.id,
105                    a.region, 
106                    a.company_id,
107                    b.name company_name,
108                    a.registration_date,
109                    a.brand, 
110                    a.vehicle_type,
111                    a.license_plate,
112                    a.mileage,
113                    a.last_itv_date,
114                    a.next_itv_date,
115                    DATE_FORMAT(a.next_itv_date, '%d/%m/%Y') next_itv_date_translate,
116                    DATE_FORMAT(a.appointment_date, '%d/%m/%Y') appointment_date_translate,
117                    a.is_due_appointment,
118                    a.location,
119                    a.is_booked,
120                    a.appointment_time,
121                    a.appointment_date,
122                    a.mileage_threshold,
123                    a.driver,
124                    a.responsible_name,
125                    a.responsible_email,
126                    a.created_by,
127                    a.created_at,
128                    a.updated_by,
129                    a.updated_at,
130                    CASE
131                        WHEN DATEDIFF(a.next_itv_date, NOW()) <= 0 THEN 0
132                        WHEN DATEDIFF(a.next_itv_date, NOW()) <= 1 THEN 1
133                        WHEN DATEDIFF(a.next_itv_date, NOW()) <= 15 THEN 15
134                        WHEN DATEDIFF(a.next_itv_date, NOW()) <= 30 THEN 30
135                    ELSE NULL
136                    END AS next_itv_date_due,
137                    a.is_due,
138                    a.is_due_mileage,
139                    a.comments
140                FROM 
141                    tbl_itv a 
142                    LEFT JOIN tbl_companies b ON a.company_id = b.company_id
143                WHERE {$where}";
144
145        $result = DB::select($query);
146
147        $totalSent = 0;
148
149        if (count($result) > 0) {
150            $body = '';
151            $subject = '';
152
153            foreach ($result as $item) {
154
155                $email = new Mail;
156
157                $user = TblUsers::where('name', $item->created_by)->first();
158                $email->addTo($user->email);
159
160                if ($user->email != $item->responsible_email) {
161                    $isValid = $this->isEmailValid($item->responsible_email);
162                    if ($isValid) {
163                        $email->addTo($item->responsible_email);
164                    } else {
165                        Log::channel('itv_reminders')->error('Invalid email: '.$item->responsible_email);
166
167                        continue;
168                    }
169                }
170
171                $subject = $subjectM;
172                $subject = str_replace('{{brand}}', $item->brand, $subject);
173                $subject = str_replace('{{license_plate}}', $item->license_plate, $subject);
174
175                $email->setFrom('fire@fire.es', 'Fire Service Titan');
176                $email->setSubject($subject);
177
178                $imgpath = file_get_contents(public_path('fireservicetitan.png'));
179
180                $email->addAttachment(
181                    $imgpath,
182                    'image/png',
183                    'fireservicetitan.png',
184                    'inline',
185                    'fireservicetitan'
186                );
187
188                $url = config('app.frontend_url')."itv/{$item->id}";
189
190                $bodyHeaders = __('language.itv_reminders.body_message_header');
191
192                if ($isBooked == 1) {
193                    $bodyHeaders = __('language.itv_reminders.body_message_header_appointment');
194                }
195
196                $body .= __('language.itv_reminders.body_hello');
197                $body = str_replace('{{responsible_name}}', $item->responsible_name, $body);
198
199                $body .= $bodyHeaders;
200                $body = str_replace('{{duration}}', $due, $body);
201                $body = str_replace('{{click}}', $url, $body);
202
203                $body .= __('language.itv_reminders.body_vehicle');
204                $body = str_replace('{{vehicle}}', $item->brand, $body);
205
206                $body .= __('language.itv_reminders.body_license_plate');
207                $body = str_replace('{{license_plate}}', $item->license_plate, $body);
208
209                $body .= __('language.itv_reminders.body_vehicle_type');
210                $body = str_replace('{{vehicle_type}}', $item->vehicle_type, $body);
211
212                if ($isBooked == 1) {
213                    $body .= __('language.itv_reminders.body_appointment_date');
214                    $item->appointment_date_translate = date('d/m/Y', strtotime((string) $item->appointment_date));
215                    $body = str_replace('{{appointment_date}}', $item->appointment_date_translate, $body);
216
217                    $body .= __('language.itv_reminders.body_appointment_time');
218                    $formattedTime = date('g:i A', strtotime((string) $item->appointment_time));
219                    $body = str_replace('{{appointment_time}}', $formattedTime, $body);
220
221                    $body .= __('language.itv_reminders.body_location');
222                    $body = str_replace('{{location}}', $item->location, $body);
223                } else {
224                    $body .= __('language.itv_reminders.body_next_itv_date');
225                    $item->next_itv_date_translate = date('m/Y', strtotime((string) $item->next_itv_date));
226                    $body = str_replace('{{next_itv_date}}', $item->next_itv_date_translate, $body);
227
228                    $body .= __('language.itv_reminders.body_driver');
229                    $body = str_replace('{{driver}}', $item->driver, $body);
230                }
231
232                $body .= $footer;
233
234                $body .= '<p>Fire Service Titan</p>';
235                $body .= "<img src='cid:fireservicetitan' style='height: 45px;' />";
236
237                $html = '<!DOCTYPE html>';
238                $html .= '<html>';
239                $html .= '<head>';
240                $html .= '<meta charset="UTF-8">';
241                $html .= '<meta name="viewport" content="width=device-width, initial-scale=1.0">';
242                $html .= '</head>';
243                $html .= '<body>';
244                $html .= $body;
245                $html .= '</body>';
246                $html .= '</html>';
247
248                $email->setFrom('titan@fire.es');
249                $email->setSubject($subject);
250
251                $email->addContent('text/html', $html);
252
253                $sendgrid = new \SendGrid(config('services.sendgrid.api_key'));
254
255                try {
256                    $response = $sendgrid->send($email);
257                    SendgridLogger::log($email, $response);
258                } catch (\Throwable $sendException) {
259                    SendgridLogger::logException($email, $sendException);
260                    throw $sendException;
261                }
262
263                if ($response->statusCode() == 202) {
264                    $totalSent++;
265
266                    $toUpdate = ['is_due' => $duration, 'updated_by' => 'System', 'updated_at' => date('Y-m-d H:i:s')];
267
268                    if ($isBooked == 1) {
269                        $toUpdate = ['is_due_appointment' => $duration, 'updated_by' => 'System', 'updated_at' => date('Y-m-d H:i:s')];
270                    }
271
272                    TblItv::where('id', $item->id)->update($toUpdate);
273                    Log::channel('itv_reminders')->error("ITV[{$duration}] OK: ".$item->responsible_email);
274                } else {
275                    Log::channel('itv_reminders')->error("ITV[{$duration}] KO: ".$response->body());
276                }
277
278                $body = '';
279                $subject = '';
280            }
281        }
282
283        // FIRE-1145: was Cache::flush() — only ITV reminder dates changed; scope to 'itv'.
284        ResultCache::forgetDomain('itv');
285
286        Log::channel('itv_reminders')->info("ITV[{$duration}]: TotalSent[{$totalSent}]");
287    }
288
289    public function email_reminders_mileage($duration)
290    {
291
292        $where = '';
293        $due = '';
294
295        if ($duration == 3000) {
296            $where = 'ABS(a.mileage - a.mileage_threshold) BETWEEN 1 AND 3000';
297        } else {
298            return response(['message' => 'KO']);
299        }
300
301        $query = "SELECT 
302                    a.id,
303                    a.region, 
304                    a.company_id,
305                    b.name company_name,
306                    a.registration_date,
307                    a.brand, 
308                    a.vehicle_type,
309                    a.license_plate,
310                    a.mileage,
311                    a.last_itv_date,
312                    a.next_itv_date,
313                    DATE_FORMAT(a.next_itv_date, '%d/%m/%Y') next_itv_date_translate,
314                    a.mileage_threshold,
315                    a.driver,
316                    a.responsible_name,
317                    a.responsible_email,
318                    a.created_by,
319                    a.created_at,
320                    a.updated_by,
321                    a.updated_at,
322                    CASE
323                        WHEN DATEDIFF(a.next_itv_date, NOW()) <= 0 THEN 0
324                        WHEN DATEDIFF(a.next_itv_date, NOW()) <= 1 THEN 1
325                        WHEN DATEDIFF(a.next_itv_date, NOW()) <= 15 THEN 15
326                        WHEN DATEDIFF(a.next_itv_date, NOW()) <= 30 THEN 30
327                    ELSE NULL
328                    END AS next_itv_date_due,
329                    a.is_due,
330                    a.is_due_mileage,
331                    a.comments
332                FROM 
333                    tbl_itv a 
334                    LEFT JOIN tbl_companies b ON a.company_id = b.company_id
335                WHERE {$where} AND a.is_due_mileage IS NULL";
336
337        $result = DB::select($query);
338
339        $totalSent = 0;
340
341        if (count($result) > 0) {
342            $body = '';
343            $subject = '';
344
345            foreach ($result as $item) {
346
347                $email = new Mail;
348
349                $user = TblUsers::where('name', $item->created_by)->first();
350                $email->addTo($user->email);
351
352                if ($item->created_by != $item->responsible_name) {
353                    $isValid = $this->isEmailValid($item->responsible_email);
354                    if ($isValid) {
355                        $email->addTo($item->responsible_email);
356                    } else {
357                        Log::channel('itv_reminders')->error('Invalid email: '.$item->responsible_email);
358
359                        continue;
360                    }
361                }
362
363                $subject = __('language.itv_reminders_km.subject');
364                $subject = str_replace('{{brand}}', $item->brand, $subject);
365                $subject = str_replace('{{license_plate}}', $item->license_plate, $subject);
366
367                $email->setFrom('fire@fire.es', 'Fire Service Titan');
368                $email->setSubject($subject);
369
370                $imgpath = file_get_contents(public_path('fireservicetitan.png'));
371
372                $email->addAttachment(
373                    $imgpath,
374                    'image/png',
375                    'fireservicetitan.png',
376                    'inline',
377                    'fireservicetitan'
378                );
379
380                $url = config('app.frontend_url')."itv/{$item->id}";
381
382                $body .= __('language.itv_reminders_km.body_hello');
383                $body = str_replace('{{responsible_name}}', $item->responsible_name, $body);
384
385                $body .= __('language.itv_reminders_km.body_message_header');
386                $body = str_replace('{{click}}', $url, $body);
387
388                $body .= __('language.itv_reminders_km.body_vehicle');
389                $body = str_replace('{{vehicle}}', $item->brand, $body);
390
391                $body .= __('language.itv_reminders_km.body_license_plate');
392                $body = str_replace('{{license_plate}}', $item->license_plate, $body);
393
394                $body .= __('language.itv_reminders_km.body_vehicle_type');
395                $body = str_replace('{{vehicle_type}}', $item->vehicle_type, $body);
396
397                $body .= __('language.itv_reminders_km.body_mileage');
398                $body = str_replace('{{mileage}}', number_format($item->mileage, 2, ',', '.'), $body);
399
400                $body .= __('language.itv_reminders_km.body_mileage_threshold');
401                $body = str_replace('{{mileage_threshold}}', number_format($item->mileage_threshold, 2, ',', '.'), $body);
402
403                $body .= __('language.itv_reminders_km.body_message_footer');
404
405                $body .= '<p>Fire Service Titan</p>';
406                $body .= "<img src='cid:fireservicetitan' style='height: 45px;' />";
407
408                $html = '<!DOCTYPE html>';
409                $html .= '<html>';
410                $html .= '<head>';
411                $html .= '<meta charset="UTF-8">';
412                $html .= '<meta name="viewport" content="width=device-width, initial-scale=1.0">';
413                $html .= '</head>';
414                $html .= '<body>';
415                $html .= $body;
416                $html .= '</body>';
417                $html .= '</html>';
418
419                $email->setFrom('titan@fire.es');
420                $email->setSubject($subject);
421
422                $email->addContent('text/html', $html);
423
424                $sendgrid = new \SendGrid(config('services.sendgrid.api_key'));
425
426                try {
427                    $response = $sendgrid->send($email);
428                    SendgridLogger::log($email, $response);
429                } catch (\Throwable $sendException) {
430                    SendgridLogger::logException($email, $sendException);
431                    throw $sendException;
432                }
433
434                if ($response->statusCode() == 202) {
435                    $totalSent++;
436                    TblItv::where('id', $item->id)->update(['is_due_mileage' => $duration]);
437                    Log::channel('itv_reminders')->error("ITV[{$duration}] OK: ".$item->responsible_email);
438                }
439
440                $body = '';
441                $subject = '';
442            }
443        }
444
445        // FIRE-1145: was Cache::flush() — only ITV reminder dates changed; scope to 'itv'.
446        ResultCache::forgetDomain('itv');
447
448        Log::channel('itv_reminders')->info("ITV[{$duration}]: TotalSent[{$totalSent}]");
449    }
450}