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