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