Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
0.00%
0 / 1010
0.00%
0 / 45
 CRAP
0.00%
0 / 1
Notifications
0.00%
0 / 1010
0.00%
0 / 45
 32580
0.00%
0 / 1
 __construct
0.00%
0 / 13
0.00%
0 / 1
 20
 list_notifications
0.00%
0 / 18
0.00%
0 / 1
 20
 update_notifications
0.00%
0 / 17
0.00%
0 / 1
 12
 send_follow_up_notification
0.00%
0 / 152
0.00%
0 / 1
 272
 send_request
0.00%
0 / 10
0.00%
0 / 1
 12
 send_g3w_warning
0.00%
0 / 20
0.00%
0 / 1
 12
 send_request_notification
0.00%
0 / 138
0.00%
0 / 1
 272
 get_cc_bcc
0.00%
0 / 6
0.00%
0 / 1
 6
 delete_cc_bcc
0.00%
0 / 6
0.00%
0 / 1
 6
 update_cc_bcc
0.00%
0 / 14
0.00%
0 / 1
 20
 create_cc_bcc
0.00%
0 / 12
0.00%
0 / 1
 20
 isEmailValid
0.00%
0 / 4
0.00%
0 / 1
 6
 delete_notifications
0.00%
0 / 9
0.00%
0 / 1
 6
 list_notification_logs
0.00%
0 / 12
0.00%
0 / 1
 12
 delete_notification_logs
0.00%
0 / 6
0.00%
0 / 1
 6
 get_cc_g3w
0.00%
0 / 6
0.00%
0 / 1
 6
 delete_cc_g3w
0.00%
0 / 6
0.00%
0 / 1
 6
 update_cc_g3w
0.00%
0 / 14
0.00%
0 / 1
 20
 create_cc_g3w
0.00%
0 / 12
0.00%
0 / 1
 20
 get_cc_bcc_request
0.00%
0 / 6
0.00%
0 / 1
 6
 delete_cc_bcc_request
0.00%
0 / 6
0.00%
0 / 1
 6
 update_cc_bcc_request
0.00%
0 / 14
0.00%
0 / 1
 20
 create_cc_bcc_request
0.00%
0 / 12
0.00%
0 / 1
 20
 send_executive
0.00%
0 / 17
0.00%
0 / 1
 56
 send_executive_report
0.00%
0 / 181
0.00%
0 / 1
 240
 get_executive_reports
0.00%
0 / 65
0.00%
0 / 1
 30
 currency
0.00%
0 / 3
0.00%
0 / 1
 6
 get_cc_bcc_executive
0.00%
0 / 5
0.00%
0 / 1
 6
 delete_cc_bcc_executive
0.00%
0 / 6
0.00%
0 / 1
 6
 update_cc_bcc_executive
0.00%
0 / 14
0.00%
0 / 1
 20
 create_cc_bcc_executive
0.00%
0 / 12
0.00%
0 / 1
 20
 update_notification_settings
0.00%
0 / 7
0.00%
0 / 1
 6
 get_notification_settings
0.00%
0 / 5
0.00%
0 / 1
 6
 send_report_logs
0.00%
0 / 37
0.00%
0 / 1
 12
 get_emails_acceptance_notifications
0.00%
0 / 7
0.00%
0 / 1
 6
 create_to_email_acceptance
0.00%
0 / 12
0.00%
0 / 1
 20
 delete_to_email_acceptance
0.00%
0 / 6
0.00%
0 / 1
 6
 update_to_email_acceptance
0.00%
0 / 15
0.00%
0 / 1
 30
 create_cc_email_acceptance
0.00%
0 / 12
0.00%
0 / 1
 20
 delete_cc_email_acceptance
0.00%
0 / 6
0.00%
0 / 1
 6
 update_cc_email_acceptance
0.00%
0 / 15
0.00%
0 / 1
 30
 list_last_follow_up_date
0.00%
0 / 12
0.00%
0 / 1
 20
 update_last_follow_up_date_working_days
0.00%
0 / 11
0.00%
0 / 1
 6
 update_last_follow_up_date
0.00%
0 / 36
0.00%
0 / 1
 30
 delete_last_follow_up_date
0.00%
0 / 13
0.00%
0 / 1
 12
1<?php
2
3namespace App\Http\Controllers;
4
5use App\Models\TblCcAcceptanceNotifications;
6use App\Models\TblCcBccExecutiveNotifications;
7use App\Models\TblCcBccNotifications;
8use App\Models\TblCcBccRequestNotifications;
9use App\Models\TblCcG3WEmailReminders;
10use App\Models\TblCompanies;
11use App\Models\TblCompanyUsers;
12use App\Models\TblLastFollowUpDate;
13use App\Models\TblNotificationLogs;
14use App\Models\TblNotifications;
15use App\Models\TblNotificationSettings;
16use App\Models\TblToAcceptanceNotifications;
17use App\Models\TblUsers;
18use Illuminate\Http\Request;
19use Illuminate\Support\Facades\App;
20use Illuminate\Support\Facades\Artisan;
21use Illuminate\Support\Facades\DB;
22use Illuminate\Support\Facades\Log;
23use SendGrid\Mail\Mail;
24use Illuminate\Contracts\Routing\ResponseFactory;
25use Illuminate\Http\Response;
26use App\Exceptions\AppException;
27
28class Notifications extends Controller
29{
30    private $locale;
31    private $userId;
32    private $region;
33    private $companyIds;
34    private $companyId;
35
36    public function __construct(){
37        $this->locale = request()->header('Locale-Id');
38        $this->userId = request()->header('User-Id');
39        $this->region = request()->header('Region');
40
41        App::setLocale($this->locale);
42
43        $this->companyIds = [];
44
45        if($this->region != null && $this->region != "" && $this->region != "All"){
46            $this->region = urldecode((string) $this->region);
47
48            $query = 'SELECT
49                        b.company_id
50                    FROM
51                        tbl_company_users a
52                        LEFT JOIN tbl_companies b ON a.company_id = b.company_id
53                    WHERE
54                        a.user_id = ?
55                        AND b.region = ?';
56
57            $this->companyIds = DB::select($query, [intval($this->userId), $this->region]);
58
59            $this->companyIds = collect($this->companyIds)->pluck('company_id')->toArray();
60        } else {
61            $this->companyIds = TblCompanyUsers::where('user_id', $this->userId)->pluck('company_id')->all();
62        }
63
64        $this->companyId = implode(',', $this->companyIds);
65    }
66
67    function list_notifications(Request $request): ResponseFactory|Response{
68
69        try {
70
71            $data = $request->all();
72            $userId = addslashes((string) $data['user_id']);
73
74            $query = "SELECT
75                        notification_id,
76                        user_id,
77                        REPLACE(content, '<br>', '') content,
78                        link,
79                        is_open,
80                        created_by,
81                        created_at,
82                        updated_by,
83                        updated_at,
84                        CASE
85                            WHEN TIMESTAMPDIFF(SECOND, created_at, NOW()) < 60 THEN
86                                CASE
87                                    WHEN TIMESTAMPDIFF(SECOND, created_at, NOW()) = 1 THEN '1 second ago'
88                                    ELSE CONCAT(TIMESTAMPDIFF(SECOND, created_at, NOW()), ' seconds ago')
89                                END
90                            WHEN TIMESTAMPDIFF(MINUTE, created_at, NOW()) < 60 THEN
91                                CASE
92                                    WHEN TIMESTAMPDIFF(MINUTE, created_at, NOW()) = 1 THEN '1 minute ago'
93                                    ELSE CONCAT(TIMESTAMPDIFF(MINUTE, created_at, NOW()), ' minutes ago')
94                                END
95                            WHEN TIMESTAMPDIFF(HOUR, created_at, NOW()) < 24 THEN
96                                CASE
97                                    WHEN TIMESTAMPDIFF(HOUR, created_at, NOW()) = 1 THEN '1 hour ago'
98                                    ELSE CONCAT(TIMESTAMPDIFF(HOUR, created_at, NOW()), ' hours ago')
99                                END
100                            WHEN TIMESTAMPDIFF(DAY, created_at, NOW()) < 7 THEN
101                                CASE
102                                    WHEN TIMESTAMPDIFF(DAY, created_at, NOW()) = 1 THEN '1 day ago'
103                                    ELSE CONCAT(TIMESTAMPDIFF(DAY, created_at, NOW()), ' days ago')
104                                END
105                            WHEN TIMESTAMPDIFF(WEEK, created_at, NOW()) < 4 THEN
106                                CASE
107                                    WHEN TIMESTAMPDIFF(WEEK, created_at, NOW()) = 1 THEN '1 week ago'
108                                    ELSE CONCAT(TIMESTAMPDIFF(WEEK, created_at, NOW()), ' weeks ago')
109                                END
110                            WHEN TIMESTAMPDIFF(MONTH, created_at, NOW()) < 12 THEN
111                                CASE
112                                    WHEN TIMESTAMPDIFF(MONTH, created_at, NOW()) = 1 THEN '1 month ago'
113                                    ELSE CONCAT(TIMESTAMPDIFF(MONTH, created_at, NOW()), ' months ago')
114                                END
115                            ELSE
116                                CASE
117                                    WHEN TIMESTAMPDIFF(YEAR, created_at, NOW()) = 1 THEN '1 year ago'
118                                    ELSE CONCAT(TIMESTAMPDIFF(YEAR, created_at, NOW()), ' years ago')
119                                END
120                        END AS formatted_created_at
121                    FROM
122                        tbl_notifications
123                    WHERE user_id = {$userId}
124                    ORDER BY
125                        created_at DESC";
126
127            $result = DB::select($query);
128
129            $totalOpenNotification = 0;
130            foreach ($result as $item) {
131                if ($item->is_open > 0) {
132                    $totalOpenNotification++;
133                }
134            }
135
136            return response([
137                'message' => 'OK',
138                'data' => $result,
139                'total' => $totalOpenNotification,
140            ]);
141
142        } catch (\Exception $e) {
143            report(AppException::fromException($e, 'LIST_NOTIFICATIONS_EXCEPTION'));
144            return response(['message' => 'KO', 'error' => $e->getMessage()]);
145        }
146
147    }
148
149    public function update_notifications(Request $request, $notificationId)
150    {
151
152        try {
153
154            $data = $request->all();
155
156            if($notificationId == 999999999){
157                TblNotifications::where('user_id', $data['user_id'])->update([
158                    'is_open' => null
159                ]);   
160            }else{
161                TblNotifications::where('notification_id', $notificationId)->update([
162                    'is_open' => null,
163                    'updated_at' => date('Y-m-d H:i:s'),
164                    'updated_by' => $data['updated_by']
165                ]);
166            }
167
168            $r = new Request([
169                'user_id' => $data['user_id'],
170            ]);
171            
172            return $this->list_notifications($r);
173
174        } catch (\Exception $e) {
175            report(AppException::fromException($e, 'UPDATE_NOTIFICATION_EXCEPTION'));
176            return response(['message' => 'KO', 'error' => $e->getMessage()]);
177        }
178    }
179
180    function send_follow_up_notification($userId = null): ResponseFactory|Response{
181
182        try {
183
184            if ($userId != null) {
185                $this->userId = $userId;
186                $this->locale = 'es';
187                App::setLocale($this->locale);
188            }
189
190            $users = TblUsers::get();
191            $body = '';
192            $allTotalFollowUps = 0;
193            $totalOrders = 0;
194            $totalCompanies = 0;
195            foreach ($users as $user) {
196                $userCompanies = TblCompanyUsers::where('user_id', $user->id)->get();
197
198                $body .= __('language.send_follow_up_notification.body_hello');
199                $body = str_replace('{{username}}', $user->name, $body);
200
201                $body .= __('language.send_follow_up_notification.body_message');
202
203                $hasFollowUp = false;
204                $companyCcIds = [];
205                foreach ($userCompanies as $userCompany) {
206
207                    $companyId = $userCompany->company_id;
208                    $company = TblCompanies::where('company_id', $companyId)->first();
209
210                    if ($company->is_send_follow_up > 0) {
211                        $toEmail = $user->email;
212
213                        $query = "SELECT 
214                                    id,
215                                    quote_id,
216                                    amount,
217                                    DATE_FORMAT(last_follow_up_date, '%d/%m/%Y') last_follow_up_date,
218                                    commercial 
219                                FROM 
220                                    tbl_quotations 
221                                WHERE 
222                                    last_follow_up_date < NOW() 
223                                    AND email IS NOT NULL 
224                                    AND email <> ''
225                                    AND NOT EXISTS (
226                                    SELECT 
227                                        1 
228                                    FROM 
229                                        tbl_blocked_domains bd 
230                                    WHERE 
231                                        email LIKE CONCAT('%', bd.domain, '%') 
232                                        AND bd.company_id = company_id
233                                    )
234                                    AND last_follow_up_date IS NOT NULL
235                                    AND reason_for_not_following_up_id IS NULL
236                                    AND last_follow_up_date > 0
237                                    AND last_follow_up_date < NOW()
238                                    AND total_sent < 3
239                                    AND for_add = 0
240                                    AND company_id = {$companyId}
241                                    AND commercial IS NOT NULL 
242                                    AND commercial = '{$user->name}'
243                                    AND budget_status_id = 2
244                                ORDER BY last_follow_up_date ASC, CAST(amount AS DOUBLE) DESC";
245
246                        $result = DB::select($query);
247
248                        $totalFolloUps = count($result);
249
250                        if ($totalFolloUps > 0) {
251                            $allTotalFollowUps = $allTotalFollowUps + $totalFolloUps;
252                            array_push($companyCcIds, $companyId);
253
254                            $hasFollowUp = true;
255                            $body .= __('language.send_follow_up_notification.body_company_message');
256                            $body = str_replace('{{total}}', $totalFolloUps, $body);
257                            $body = str_replace('{{company}}', $company->name, $body);
258    
259                            $clickhRef = config('app.frontend_url') . "orders?commercial={$user->name}&status=Enviado&last_follow_up_date=1&company_id={$companyId}";
260                            $body = str_replace('{{click}}', $clickhRef, $body);
261
262                            $ul = '<ul>';
263
264                            $list = '';
265                            $totalAmount = 0;
266                            for ($i = 0; $i < $totalFolloUps; $i++) {
267                                $li = __('language.send_follow_up_notification.body_due');
268
269                                $id = $result[$i]->id;
270                                $date = $result[$i]->last_follow_up_date;
271                                $quoteId = $result[$i]->quote_id;
272                                $totalAmount = $totalAmount + $result[$i]->amount;
273                                $amount = $this->currency($result[$i]->amount, 1);
274    
275                                $url = config('app.frontend_url') . "orders/{$id}?company_id={$companyId}";
276                                $href = "<a href='{$url}'>{$quoteId}</a>";
277
278                                $li = str_replace('{{quote_id}}', $href, $li);
279                                $li = str_replace('{{amount}}', $amount, $li);
280                                $list .= str_replace('{{date}}', $date, $li);
281                            }
282                            $ul .= $list;
283                            $ul .= '</ul>';
284
285                            $totalAmount = $this->currency($totalAmount, 1);
286                            $body = str_replace('{{amount}}', $totalAmount, $body);
287
288                            $content = $body.$ul;
289
290                            $body .= $ul;
291
292                        } else {
293                            continue;
294                        }
295                    } else {
296                        continue;
297                    }
298                }
299
300                if ($hasFollowUp) {
301
302                    $body = str_replace('{{totalOrders}}', $allTotalFollowUps, $body);
303                    $body = str_replace('{{totalCompanies}}', count($companyCcIds), $body);
304
305                    $clickhRef = config('app.frontend_url') . "orders?commercial={$user->name}&status=Enviado&last_follow_up_date=1&company_id=0";
306                    $body = str_replace('{{clickAll}}', $clickhRef, $body);
307
308                    $imgpathS = \File::get(public_path('sendfollowups.png'));
309                    $img = "<br><img src='cid:sendfollowups' />";
310                    $body .= $img;
311
312                    $imgpath = \File::get(public_path('fireservicetitan.png'));
313                    $body .= '<br><p>Fire Service Titan</p>';
314                    $body .= "<img src='cid:fireservicetitan' style='height: 45px;' />";
315
316                    $html = '<!DOCTYPE html>';
317                    $html .= '<html>';
318                    $html .= '<head>';
319                    $html .= '<meta charset="UTF-8">';
320                    $html .= '<meta name="viewport" content="width=device-width, initial-scale=1.0">';
321                    $html .= '</head>';
322                    $html .= '<body>';
323                    $html .= $body;
324                    $html .= '</body>';
325                    $html .= '</html>';
326
327                    $dateNow = date_create(date('Y-m-d'));
328                    $dateNow = date_format($dateNow, 'd/m/Y');
329
330                    $subject = __('language.send_follow_up_notification.subject');
331                    $subject = str_replace('{{dateNow}}', $dateNow, $subject);
332
333                    if ($toEmail != null) {
334                        $email = new \SendGrid\Mail\Mail;
335                        $sentBy = 'System';
336
337                        $email->setFrom('fire@fire.es', 'Fire Service Titan');
338                        $email->setSubject($subject);
339
340                        $userAdmin = TblUsers::where('id', $this->userId)->first();
341                        $sentBy = $userAdmin->name;
342
343                        if(config('services.sendgrid.staging')){
344                            $toEmail = $userAdmin->email;
345                            $sentBy = $userAdmin->name;
346                            $user->id = $this->userId;
347                        } else {
348                            $companyCcIds = array_values(array_unique($companyCcIds));
349                            $ccBcc = TblCcBccNotifications::whereIn('company_id', $companyCcIds)->get();
350
351                            $inCc = [];
352
353                            if (count($ccBcc) > 0) {
354                                foreach ($ccBcc as $data) {
355                                    if ($data->email != $toEmail && ! in_array($data->email, $inCc)) {
356                                        $email->addCc($data->email);
357                                        array_push($inCc, $data->email);
358                                    }
359                                }
360                            }
361                        }
362
363                        $email->addTo($toEmail);
364                        $email->addContent('text/html', $html);
365
366                        $email->addAttachment(
367                            $imgpath,
368                            'image/png',
369                            'fireservicetitan.png',
370                            'inline',
371                            'fireservicetitan'
372                        );
373
374                        $email->addAttachment(
375                            $imgpathS,
376                            'image/png',
377                            'sendfollowups.png',
378                            'inline',
379                            'sendfollowups'
380                        );
381
382                        $sendgrid = new \SendGrid(config('services.sendgrid.api_key'));
383
384                        $response = $sendgrid->send($email);
385                        if ($response->statusCode() == 202) {
386                            Log::channel('email_log')->info('ID: '.$toEmail.' - TO FOLLOW UP EMAIL NOTIFICATION SENT');
387
388                            TblNotifications::create(
389                                [
390                                    'user_id' => $user->id,
391                                    'content' => $content,
392                                    'is_open' => 1,
393                                    'created_by' => 'System',
394                                ]
395                            );
396
397                            TblNotificationLogs::create(
398                                [
399                                    'commercial' => $user->name,
400                                    'total_follow_ups' => $allTotalFollowUps,
401                                    'created_by' => $sentBy
402                                ]
403                            );
404
405                        } else {
406                            $error = true;
407                            Log::channel('email_log')->error('ID: '.$toEmail.' - '.$response->body());
408                        }
409
410                        $body = '';
411                        $content = '';
412                    }
413                }
414
415                $hasFollowUp = false;
416                $body = '';
417                $content = '';
418                $allTotalFollowUps = 0;
419            }
420
421            return response([
422                'message' => 'OK',
423            ]);
424
425        } catch (\Exception $e) {
426            report(AppException::fromException($e, 'SEND_FOLLOW_UP_NOTIFICATION_EXCEPTION'));
427            return response(['message' => 'KO', 'error' => $e->getMessage()]);
428        }
429    }
430
431    function send_request($companyId): ResponseFactory|Response{
432
433        try {
434
435            $companyId = addslashes((string) $companyId);
436
437            $user = ['commercial'];
438            
439            foreach ($user as $col) {
440                // $col is just 'commercial', but send_request_notification takes a $userId which isn't used as such.
441                // Assuming it was intended to only notify all users when called via CLI command.
442                $this->send_request_notification(); // Fixed method call
443            }
444
445            return response([
446                'message' => 'OK',
447            ]);
448
449        } catch (\Exception $e) {
450            report(AppException::fromException($e, 'SEND_REQUEST_EXCEPTION'));
451            return response(['message' => 'KO', 'error' => $e->getMessage()]);
452        }
453
454    }
455
456    function send_g3w_warning($companyId): ResponseFactory|Response{
457
458        try {
459
460            $companyId = addslashes((string) $companyId);
461            
462            $userAdmin = TblUsers::where('id', $this->userId)->first();
463
464            if(config('services.sendgrid.staging')){                
465                Artisan::call('send:g3w-email-reminders', [
466                    'sent_by' => $userAdmin->name,
467                    'email' => $userAdmin->email,
468                    'company_id' => $companyId,
469                ]);
470            } else {
471                Artisan::call('send:g3w-email-reminders', [
472                    'sent_by' => $userAdmin->name,
473                    'email' => null,
474                    'company_id' => $companyId,
475                ]);
476            }
477
478            $output = Artisan::output();
479
480            return response([
481                'message' => 'OK',
482            ]);
483
484        } catch (\Exception $e) {
485            report(AppException::fromException($e, 'SEND_G3W_WARNING_EXCEPTION'));
486            return response(['message' => 'KO', 'error' => $e->getMessage()]);
487        }
488
489    }
490
491    function send_request_notification($userId = null): ResponseFactory|Response{
492
493        try {
494
495            if ($userId != null) {
496                $this->userId = $userId;
497                $this->locale = 'es';
498                App::setLocale($this->locale);
499            }
500
501            $users = TblUsers::get();
502            $body = '';
503            $allTotalRequest = 0;
504
505            foreach ($users as $user) {
506                $userCompanies = TblCompanyUsers::where('user_id', $user->id)->get();
507
508                $body .= __('language.send_request_notification.body_hello');
509                $body = str_replace('{{username}}', $user->name, $body);
510                $body .= __('language.send_request_notification.body_intro');
511
512                $hasRequest = false;
513                $companyCcIds = [];
514                foreach ($userCompanies as $userCompany) {
515
516                    $companyId = $userCompany->company_id;
517                    $company = TblCompanies::where('company_id', $companyId)->first();
518
519                    if ($company->is_send_request > 0) {
520                        $toEmail = $user->email;
521
522                        $query = "SELECT 
523                                    id,
524                                    quote_id,
525                                    DATE_FORMAT(created_at, '%d/%m/%Y') created_at,
526                                    commercial,
527                                    created_by
528                                FROM 
529                                    tbl_quotations 
530                                WHERE 
531                                    company_id = {$companyId}
532                                    AND budget_status_id IN (6)
533                                    AND commercial = '{$user->name}'";
534
535                        $result = DB::select($query);
536
537                        $totalRequest = count($result);
538
539                        if ($totalRequest > 0) {
540                            $allTotalRequest = $allTotalRequest + $totalRequest;
541                            $hasRequest = true;
542                            array_push($companyCcIds, $companyId);
543
544                            $body .= __('language.send_request_notification.body_message_commercial');
545                            $body = str_replace('{{total}}', $totalRequest, $body);
546                            $body = str_replace('{{company}}', $company->name, $body);
547    
548                            $clickhRef = config('app.frontend_url') . "orders?commercial={$user->name}&status=Solicitud&company_id={$companyId}";
549                            $body = str_replace('{{click}}', $clickhRef, $body);
550
551                            $ul = '<ul>';
552
553                            $list = '';
554                            $totalAmount = 0;
555                            for ($i = 0; $i < $totalRequest; $i++) {
556                                $li = __('language.send_request_notification.body_due');
557
558                                $id = $result[$i]->id;
559                                $date = $result[$i]->created_at;
560                                $quoteId = $result[$i]->quote_id;
561
562                                $url = config('app.frontend_url') . "orders/{$id}?company_id={$companyId}";
563                                $href = "<a href='{$url}'>{$quoteId}</a>";
564
565                                $li = str_replace('{{quote_id}}', $href, $li);
566                                $list .= str_replace('{{date}}', $date, $li);
567                            }
568                            $ul .= $list;
569                            $ul .= '</ul>';
570
571                            $totalAmount = $this->currency($totalAmount, 1);
572                            $body = str_replace('{{amount}}', $totalAmount, $body);
573
574                            $content = $body.$ul;
575
576                            $body .= $ul;
577
578                        } else {
579                            continue;
580                        }
581                    } else {
582                        continue;
583                    }
584                }
585
586                if ($hasRequest) {
587
588                    $body = str_replace('{{totalOrders}}', $allTotalRequest, $body);
589                    $body = str_replace('{{totalCompanies}}', count($companyCcIds), $body);
590
591                    $clickhRef = config('app.frontend_url') . "orders?commercial={$user->name}&status=Solicitud&company_id=0";
592                    $body = str_replace('{{clickAll}}', $clickhRef, $body);
593
594                    $imgpath = \File::get(public_path('fireservicetitan.png'));
595
596                    $body .= '<br><p>Fire Service Titan</p>';
597                    $body .= "<img src='cid:fireservicetitan' style='height: 45px;' />";
598
599                    $html = '<!DOCTYPE html>';
600                    $html .= '<html>';
601                    $html .= '<head>';
602                    $html .= '<meta charset="UTF-8">';
603                    $html .= '<meta name="viewport" content="width=device-width, initial-scale=1.0">';
604                    $html .= '</head>';
605                    $html .= '<body>';
606                    $html .= $body;
607                    $html .= '</body>';
608                    $html .= '</html>';
609
610                    $dateNow = date_create(date('Y-m-d'));
611                    $dateNow = date_format($dateNow, 'd/m/Y');
612
613                    $subject = __('language.send_request_notification.subject');
614                    $subject = str_replace('{{dateNow}}', $dateNow, $subject);
615                    $subject = str_replace('{{total}}', $allTotalRequest, $subject);
616
617                    if ($toEmail != null) {
618                        $email = new \SendGrid\Mail\Mail;
619                        $sentBy = 'System';
620
621                        $email->setFrom('fire@fire.es', 'Fire Service Titan');
622                        $email->setSubject($subject);
623
624                        $userAdmin = TblUsers::where('id', $this->userId)->first();
625                        $sentBy = $userAdmin->name;
626
627                        if(config('services.sendgrid.staging')){
628                            $toEmail = $userAdmin->email;
629                            $sentBy = $userAdmin->name;
630                            $user->id = $this->userId;
631                        } else {
632                            $companyCcIds = array_values(array_unique($companyCcIds));
633                            $ccBcc = TblCcBccRequestNotifications::whereIn('company_id', $companyCcIds)->get();
634
635                            $inCc = [];
636
637                            if (count($ccBcc) > 0) {
638                                foreach ($ccBcc as $data) {
639                                    if ($data->email != $toEmail && ! in_array($data->email, $inCc)) {
640                                        $email->addCc($data->email);
641                                        array_push($inCc, $data->email);
642                                    }
643                                }
644                            }
645                        }
646
647                        $email->addTo($toEmail);
648                        $email->addContent('text/html', $html);
649
650                        $email->addAttachment(
651                            $imgpath,
652                            'image/png',
653                            'fireservicetitan.png',
654                            'inline',
655                            'fireservicetitan'
656                        );
657
658                        $sendgrid = new \SendGrid(config('services.sendgrid.api_key'));
659
660                        $response = $sendgrid->send($email);
661                        if ($response->statusCode() == 202) {
662                            Log::channel('email_log')->info('ID:'.$toEmail.' - REQUEST EMAIL NOTIFICATION SENT');
663
664                            TblNotifications::create(
665                                [
666                                    'user_id' => $user->id,
667                                    'content' => $content,
668                                    'is_open' => 1,
669                                    'created_by' => 'System',
670                                ]
671                            );
672
673                            TblNotificationLogs::create(
674                                [
675                                    'company_id' => $companyId,
676                                    'commercial' => $user->name,
677                                    'total_request' => $allTotalRequest,
678                                    'created_by' => $sentBy
679                                ]
680                            );
681
682                        } else {
683                            $error = true;
684                            Log::channel('email_log')->error('ID:'.$toEmail.' - '.$response->body());
685                        }
686
687                        $body = '';
688                        $content = '';
689                    }
690                }
691
692                $hasRequest = false;
693                $body = '';
694                $content = '';
695                $allTotalRequest = 0;
696            }
697
698            return response([
699                'message' => 'OK',
700            ]);
701
702        } catch (\Exception $e) {
703            report(AppException::fromException($e, 'SEND_REQUEST_EXCEPTION'));
704            return response(['message' => 'KO', 'error' => $e->getMessage()]);
705        }
706    }
707
708    function get_cc_bcc($id): ResponseFactory|Response{
709
710        try {
711            
712            $companyId = addslashes((string) $id);
713
714            $result = TblCcBccNotifications::where('company_id', $companyId)->get();
715
716            return response(['message' => 'OK', 'data' => $result]);
717
718        } catch (\Exception $e) {
719            report(AppException::fromException($e, 'GET_CC_BCC_EXCEPTION'));
720            return response(['message' => 'KO', 'error' => $e->getMessage()]);
721        }
722    }
723
724    function delete_cc_bcc($id): ResponseFactory|Response{
725
726        try {
727            
728            $id = addslashes((string) $id);
729
730            TblCcBccNotifications::where('id', $id)->delete();
731
732            return response(['message' => 'OK']);
733
734        } catch (\Exception $e) {
735            report(AppException::fromException($e, 'DELETE_CC_BCC_EXCEPTION'));
736            return response(['message' => 'KO', 'error' => $e->getMessage()]);
737        }
738    }
739
740    function update_cc_bcc(Request $request, $id): ResponseFactory|Response{
741
742        try {
743
744            $data = $request->all();
745            $id = addslashes((string) $id);
746
747            $x = $this->isEmailValid($data['email']);
748
749            if($x){
750                $result = TblCcBccNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
751
752                if ($result > 0) {
753                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
754                } else {
755                    $data['updated_at'] = date('Y-m-d H:i:s');
756                    TblCcBccNotifications::where('id', $id)->update($data);
757                }
758            } else {
759                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
760            }
761
762            return response(['message' => 'OK']);
763
764        } catch (\Exception $e) {
765            report(AppException::fromException($e, 'UPDATE_CC_BCC_EXCEPTION'));
766            return response(['message' => 'KO', 'error' => $e->getMessage()]);
767        }
768    }
769
770    function create_cc_bcc(Request $request): ResponseFactory|Response{
771
772        try {
773
774            $data = $request->all();
775
776            $x = $this->isEmailValid($data['email']);
777
778            if($x){
779                $result = TblCcBccNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
780
781                if ($result > 0) {
782                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
783                } else {
784                    TblCcBccNotifications::create($data);
785                }
786            } else {
787                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
788            }
789
790            return response(['message' => 'OK']);
791
792        } catch (\Exception $e) {
793            report(AppException::fromException($e, 'CREATE_CC_BCC_EXCEPTION'));
794            return response(['message' => 'KO', 'error' => $e->getMessage()]);
795        }
796    }
797
798    function isEmailValid($email): bool {
799        // Regular expression pattern for email validation
800        $pattern = '/^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$/';
801
802        // Check if the email matches the pattern
803        if (preg_match($pattern, (string) $email)) {
804            return true; // Valid email
805        } else {
806            return false; // Invalid email
807        }
808    }
809
810    public function delete_notifications($userId)
811    {
812
813        try {
814            
815            $userId = addslashes((string) $userId);
816
817            TblNotifications::where('user_id', $userId)->delete();
818
819            $r = new Request([
820                'user_id' => $userId,
821            ]);
822            
823            return $this->list_notifications($r);
824
825        } catch (\Exception $e) {
826            report(AppException::fromException($e, 'DELETE_NOTIFICATIONS_EXCEPTION'));
827            return response(['message' => 'KO', 'error' => $e->getMessage()]);
828        }
829
830    }
831
832    function list_notification_logs($companyId): ResponseFactory|Response{
833
834        try {
835            
836            $companyId = addslashes((string) $companyId);
837
838            if ($companyId != 0) {
839                $where = " company_id = {$companyId} ";
840            } else {
841                $where = " company_id IN ({$this->companyId}) ";
842            }
843
844            $query = "SELECT
845                        id,
846                        commercial,
847                        total_follow_ups,
848                        total_request,
849                        total_executive_report,
850                        total_g3w_warning,
851                        CASE 
852                            WHEN total_follow_ups IS NOT NULL THEN total_follow_ups
853                            WHEN total_request IS NOT NULL THEN total_request
854                            WHEN total_executive_report IS NOT NULL THEN total_executive_report
855                            WHEN total_g3w_warning IS NOT NULL THEN total_g3w_warning
856                        END totals,
857                        created_by,
858                        created_at
859                    FROM `tbl_notification_logs` 
860                    WHERE {$where}
861                    OR company_id IS NULL
862                    ORDER BY created_at DESC";
863
864            $result = DB::select($query);
865
866            return response(['message' => 'OK', 'data' => $result]);
867
868        } catch (\Exception $e) {
869            report(AppException::fromException($e, 'LIST_NOTIFICATION_LOGS_EXCEPTION'));
870            return response(['message' => 'KO', 'error' => $e->getMessage()]);
871        }
872
873    }
874
875    function delete_notification_logs($companyId): ResponseFactory|Response{
876
877        try {
878            
879            $companyId = addslashes((string) $companyId);
880
881            TblNotificationLogs::where('company_id', $companyId)->orWhere('company_id', null)->delete();
882
883            return response(['message' => 'OK']);
884
885        } catch (\Exception $e) {
886            report(AppException::fromException($e, 'DELETE_NOTIFICATION_LOGS_EXCEPTION'));
887            return response(['message' => 'KO', 'error' => $e->getMessage()]);
888        }
889    }
890
891    function get_cc_g3w($id): ResponseFactory|Response{
892
893        try {
894            
895            $companyId = addslashes((string) $id);
896
897            $result = TblCcG3WEmailReminders::where('company_id', $companyId)->get();
898
899            return response(['message' => 'OK', 'data' => $result]);
900
901        } catch (\Exception $e) {
902            report(AppException::fromException($e, 'GET_CC_G3W_EXCEPTION'));
903            return response(['message' => 'KO', 'error' => $e->getMessage()]);
904        }
905    }
906
907    function delete_cc_g3w($id): ResponseFactory|Response{
908
909        try {
910            
911            $id = addslashes((string) $id);
912
913            TblCcG3WEmailReminders::where('id', $id)->delete();
914
915            return response(['message' => 'OK']);
916
917        } catch (\Exception $e) {
918            report(AppException::fromException($e, 'DELETE_CC_G3W_EXCEPTION'));
919            return response(['message' => 'KO', 'error' => $e->getMessage()]);
920        }
921    }
922
923    function update_cc_g3w(Request $request, $id): ResponseFactory|Response{
924
925        try {
926
927            $data = $request->all();
928            $id = addslashes((string) $id);
929
930            $x = $this->isEmailValid($data['email']);
931
932            if($x){
933                $result = TblCcG3WEmailReminders::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
934
935                if ($result > 0) {
936                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
937                } else {
938                    $data['updated_at'] = date('Y-m-d H:i:s');
939                    TblCcG3WEmailReminders::where('id', $id)->update($data);
940                }
941            } else {
942                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
943            }
944
945            return response(['message' => 'OK']);
946
947        } catch (\Exception $e) {
948            report(AppException::fromException($e, 'UPDATE_CC_G3W_EXCEPTION'));
949            return response(['message' => 'KO', 'error' => $e->getMessage()]);
950        }
951    }
952
953    function create_cc_g3w(Request $request): ResponseFactory|Response{
954
955        try {
956
957            $data = $request->all();
958
959            $x = $this->isEmailValid($data['email']);
960
961            if($x){
962                $result = TblCcG3WEmailReminders::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
963
964                if ($result > 0) {
965                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
966                } else {
967                    TblCcG3WEmailReminders::create($data);
968                }
969            } else {
970                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
971            }
972
973            return response(['message' => 'OK']);
974
975        } catch (\Exception $e) {
976            report(AppException::fromException($e, 'CREATE_CC_G3W_EXCEPTION'));
977            return response(['message' => 'KO', 'error' => $e->getMessage()]);
978        }
979    }
980
981    function get_cc_bcc_request($id): ResponseFactory|Response{
982
983        try {
984            
985            $companyId = addslashes((string) $id);
986
987            $result = TblCcBccRequestNotifications::where('company_id', $companyId)->get();
988
989            return response(['message' => 'OK', 'data' => $result]);
990
991        } catch (\Exception $e) {
992            report(AppException::fromException($e, 'GET_CC_BCC_REQUEST_EXCEPTION'));
993            return response(['message' => 'KO', 'error' => $e->getMessage()]);
994        }
995    }
996
997    function delete_cc_bcc_request($id): ResponseFactory|Response{
998
999        try {
1000            
1001            $id = addslashes((string) $id);
1002
1003            TblCcBccRequestNotifications::where('id', $id)->delete();
1004
1005            return response(['message' => 'OK']);
1006
1007        } catch (\Exception $e) {
1008            report(AppException::fromException($e, 'DELETE_CC_BCC_REQUEST_EXCEPTION'));
1009            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1010        }
1011    }
1012
1013    function update_cc_bcc_request(Request $request, $id): ResponseFactory|Response{
1014
1015        try {
1016
1017            $data = $request->all();
1018            $id = addslashes((string) $id);
1019
1020            $x = $this->isEmailValid($data['email']);
1021
1022            if($x){
1023                $result = TblCcBccRequestNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
1024
1025                if ($result > 0) {
1026                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
1027                } else {
1028                    $data['updated_at'] = date('Y-m-d H:i:s');
1029                    TblCcBccRequestNotifications::where('id', $id)->update($data);
1030                }
1031            } else {
1032                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
1033            }
1034
1035            return response(['message' => 'OK']);
1036
1037        } catch (\Exception $e) {
1038            report(AppException::fromException($e, 'UPDATE_CC_BCC_REQUEST_EXCEPTION'));
1039            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1040        }
1041    }
1042
1043    function create_cc_bcc_request(Request $request): ResponseFactory|Response{
1044
1045        try {
1046
1047            $data = $request->all();
1048
1049            $x = $this->isEmailValid($data['email']);
1050
1051            if($x){
1052                $result = TblCcBccRequestNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
1053
1054                if ($result > 0) {
1055                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
1056                } else {
1057                    TblCcBccRequestNotifications::create($data);
1058                }
1059            } else {
1060                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
1061            }
1062
1063            return response(['message' => 'OK']);
1064
1065        } catch (\Exception $e) {
1066            report(AppException::fromException($e, 'CREATE_CC_BCC_REQUEST_EXCEPTION'));
1067            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1068        }
1069    }
1070
1071    function send_executive(Request $request): ResponseFactory|Response{
1072
1073        try {
1074
1075            $data = $request->all();
1076
1077            $executiveEmails = TblCcBccExecutiveNotifications::get();
1078
1079            if (isset($data['User-ID'])) {
1080                $this->userId = $data['User-ID'];
1081            }
1082
1083            if (isset($data['self']) && $data['self'] == 1) {
1084                $user = TblUsers::where('id', $this->userId)->first();
1085                $this->send_executive_report($user->email, $user->name, $user->id, $data['processed_by']);
1086            } else {
1087                foreach ($executiveEmails as $item) {
1088                    $user = TblUsers::where('email', $item->email)->first();
1089                    if ($user) {
1090                        $this->send_executive_report($item->email, $user->name, $user->id, $data['processed_by']);
1091                    }
1092                }
1093            }
1094
1095            return response([
1096                'message' => 'OK',
1097            ]);
1098
1099        } catch (\Exception $e) {
1100            report(AppException::fromException($e, 'SEND_EXECUTIVE_EXCEPTION'));
1101            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1102        }
1103
1104    }
1105
1106    public function send_executive_report($toEmail, $toName, $toUserId, $sentBy = 'System')
1107    {
1108
1109        try {
1110
1111            $result = TblNotificationSettings::where('id', 1)->first();
1112
1113            $currentYear = date('Y');
1114            $previousYear = date('Y', strtotime('-1 year', strtotime(date('Y-m-d'))));
1115
1116            if ($toEmail != null && $result->is_auto_send_executive == 1) {
1117
1118                if(config('services.sendgrid.staging')){
1119                    $user = TblUsers::where('id', $this->userId)->first();
1120                    $toEmail = $user->email;
1121                }
1122
1123                $result = (object) $this->get_executive_reports();
1124
1125                $weekNumber = $result->weekNumber;
1126                $monday = $result->monday;
1127                $totalBudgets = number_format($result->totalBudgets, 0, ',', '.');
1128                $totalAmountApproved = $result->totalAmountApproved;
1129                $totalAmount = $result->totalAmount;
1130                $dividendMaintenance = $result->dividendMaintenance;
1131                $dividendCorrectives = $result->dividendCorrectives;
1132                $percentageMoreThanLastWeek = $result->percentageMoreThanLastWeek;
1133                $approvalRate = $result->approvalRate;
1134
1135                $subject = "FST executive report week $weekNumber (starting on monday - $monday) - Automated";
1136
1137                $body = "<p>Hello {$toName},</p>";
1138
1139                $url = config('app.frontend_url') . "analytics?week={$weekNumber}";
1140                $body .= "<p>Find a summary of the commercial activity across <b>Grupo Fire</b> (see the <a href='{$url}'>report on FST here</a>) for week {$weekNumber} (starting on monday - {$monday})</p>";
1141                $body .= "<p><b>{$totalBudgets} budget(s)</b> ({$percentageMoreThanLastWeek}% compared to last week) created for a total of <b>{$totalAmount}</b> ({$dividendMaintenance}% “Mantenimiento”, {$dividendCorrectives}% “Correctivos”) - ";
1142                $body .= "this week across all companies - <i>we have an approval rate of <b>{$approvalRate}%</b> for the month <b>(<span style='color: blue;'>{$totalAmountApproved} approved</span>)</b></i></p>";
1143
1144                $query = "SELECT 
1145                            a.company_id, 
1146                            a.name,
1147                            (
1148                            SELECT 
1149                                COUNT(created_at) 
1150                            FROM 
1151                                tbl_quotations 
1152                            WHERE 
1153                                company_id = a.company_id
1154                                AND for_add = 0 
1155                                AND created_at IS NOT NULL 
1156                                AND issue_date IS NOT NULL
1157                                AND YEAR(created_at) = YEAR(issue_date)
1158                            ) total 
1159                        FROM 
1160                            tbl_companies a 
1161                            WHERE is_send_executive = 1
1162                            -- AND company_id IN ({$this->companyId})
1163                        ORDER BY 
1164                            3 DESC
1165                        ";
1166
1167                $companies = DB::select($query);
1168                
1169                $li = [];
1170    
1171                foreach ($companies as $company) {
1172                    $result = (object) $this->get_executive_reports($company->company_id);
1173                    $result->name = $company->name;
1174                    array_push($li, $result);
1175                }
1176    
1177                usort($li, fn($a, $b) => $b->totalBudgets - $a->totalBudgets);
1178                
1179                $body .= "<ul>";
1180                foreach ($li as $result) {
1181                    $totalBudgets = number_format($result->totalBudgets, 0, ',', '.');
1182                    $totalAmount = $result->totalAmount;
1183                    $dividendMaintenance = $result->dividendMaintenance;
1184                    $dividendCorrectives = $result->dividendCorrectives;
1185                    $percentageMoreThanLastWeek = $result->percentageMoreThanLastWeek;
1186                    $approvalRate = $result->approvalRate;
1187
1188                    $body .= "<li>{$totalBudgets} order(s) in “<b>{$result->name}</b>” ({$dividendMaintenance}% “Mantenimiento”, {$dividendCorrectives}% “Correctivos”) for total of <b>{$totalAmount}</b></li>";
1189                }
1190                $body .= '</ul>';
1191
1192                $result = (object) $this->get_executive_reports(null, true, $currentYear);
1193
1194                $totalBudgets = number_format($result->totalBudgets, 0, ',', '.');
1195                $totalAmountApproved = $result->totalAmountApproved;
1196                $totalAmount = $result->totalAmount;
1197                $dividendMaintenance = $result->dividendMaintenance;
1198                $dividendCorrectives = $result->dividendCorrectives;
1199                $percentageMoreThanLastWeek = $result->percentageMoreThanLastWeek;
1200                $approvalRate = $result->approvalRate;
1201                $totalDaysFromStartOfYear = date('z') + 1;
1202                $totalDaysOfCurrentYear = date('z', mktime(0, 0, 0, 12, 31, date('Y'))) + 1;
1203                $expected = 0;
1204
1205                $totalGoalForTheYear = 0;
1206                if ($result->totalGoalForTheYear > 0) {
1207                    $totalGoalForTheYear = number_format(($result->totalBudgets / $result->totalGoalForTheYear) * 100, 0, ',', '.');
1208                    $expected = number_format(($totalDaysFromStartOfYear / $totalDaysOfCurrentYear) * 100, 0, ',', '.');
1209                }
1210
1211                $body .= "<p><b>For {$currentYear}</b> as a whole, <b><span style='color: blue;'>{$totalBudgets}</span> budgets (<span style='color: blue;'>{$totalGoalForTheYear}% of the goal for the year accomplished</span> vs {$expected}% expected) for a total of <span style='color: blue;'>{$totalAmount}</span></b> ({$dividendMaintenance}% “Mantenimientos”, {$dividendCorrectives}% “Correctivos”) ";
1212                $body .= "were created across all companies - <i>we have an approval rate of <b>{$approvalRate}%</b> for the year <b>(<span style='color: blue;'>{$totalAmountApproved} approved</span>)</b></i></p>";
1213
1214                $query = "SELECT 
1215                            a.company_id, 
1216                            a.name,
1217                            (
1218                            SELECT 
1219                                COUNT(created_at) 
1220                            FROM 
1221                                tbl_quotations 
1222                            WHERE 
1223                                company_id = a.company_id
1224                                AND for_add != 1
1225                                AND amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1226                                AND created_at IS NOT NULL 
1227                                AND YEAR(created_at) = {$currentYear}                                
1228                            ) total 
1229                        FROM 
1230                            tbl_companies a 
1231                            WHERE is_send_executive = 1
1232                            -- AND company_id IN ({$this->companyId})
1233                        ORDER BY 
1234                            3 DESC
1235                        ";
1236
1237                $companies = DB::select($query);
1238
1239                $li = [];
1240    
1241                foreach ($companies as $company) {
1242                    $result = (object) $this->get_executive_reports($company->company_id, true, $currentYear);
1243                    $result->name = $company->name;
1244                    array_push($li, $result);
1245                }
1246    
1247                usort($li, fn($a, $b) => $b->totalBudgets - $a->totalBudgets);
1248
1249                $body .= '<ul>';
1250                foreach ($li as $result) {
1251
1252                    $totalBudgets = number_format($result->totalBudgets, 0, ',', '.');
1253                    $totalAmountApproved = $result->totalAmountApproved;
1254                    $totalAmount = $result->totalAmount;
1255                    $dividendMaintenance = $result->dividendMaintenance;
1256                    $dividendCorrectives = $result->dividendCorrectives;
1257                    $percentageMoreThanLastWeek = $result->percentageMoreThanLastWeek;
1258                    $approvalRate = $result->approvalRate;
1259                    $goalForTheYear = number_format($result->totalGoalForTheYear, 0, ',', '.');
1260                    $totalDaysFromStartOfYear = date('z') + 1;
1261                    $totalDaysOfCurrentYear = date('z', mktime(0, 0, 0, 12, 31, date('Y'))) + 1;
1262                    $expected = 0;
1263
1264                    $totalGoalForTheYear = 0;
1265                    $style = "style='color: green'";
1266                    if ($result->totalGoalForTheYear > 0) {
1267                        $totalGoalForTheYear = number_format(($result->totalBudgets / $result->totalGoalForTheYear) * 100, 0, ',', '.');
1268                        $expected = number_format(($totalDaysFromStartOfYear / $totalDaysOfCurrentYear) * 100, 0, ',', '.');
1269
1270                        if ($totalGoalForTheYear < $expected) {
1271                            $style = "style='color: red'";
1272                        }
1273                    }
1274
1275                    $body .= "<li>{$totalBudgets} order(s) in “<b>{$result->name}</b>” ({$dividendMaintenance}% “Mantenimiento”, {$dividendCorrectives}% “Correctivos”) for total of <b>{$totalAmount}</b></li>";
1276                    $body .= '<ul>';
1277                    $body .= "<li><i><b>{$goalForTheYear} goal</b> for the year. <b {$style}>{$totalGoalForTheYear}%</b> accomplished to date vs <b>{$expected}% expected</b></i></li>";
1278                    $body .= "<li><i>Approval rate of <b>{$approvalRate}%</b> (<span style='color: blue;'>{$totalAmountApproved} approved</span>)</i></li>";
1279                    $body .= '</ul>';
1280                }
1281
1282                $body .= '</ul>';
1283
1284                $result = (object) $this->get_executive_reports(null, true, $previousYear);
1285
1286                $totalBudgets = number_format($result->totalBudgets, 0, ',', '.');
1287                $totalAmount = $result->totalAmount;
1288                $dividendMaintenance = $result->dividendMaintenance;
1289                $dividendCorrectives = $result->dividendCorrectives;
1290                $percentageMoreThanLastWeek = $result->percentageMoreThanLastWeek;
1291                $approvalRate = $result->approvalRate;
1292
1293                $body .= "<p>For your reference, in <b>{$previousYear}</b>, <b>{$totalBudgets} budgets for a total of {$totalAmount}</b> ({$dividendMaintenance}% “Mantenimientos”, {$dividendCorrectives}% “Correctivos”) ";
1294                $body .= "were created across all companies - <i>we have an approval rate of <b>{$approvalRate}%</b> for the year</i></p>";
1295
1296                $query = "SELECT 
1297                            a.company_id, 
1298                            a.name,
1299                            (
1300                            SELECT 
1301                                COUNT(created_at) 
1302                            FROM 
1303                                tbl_quotations 
1304                            WHERE 
1305                                company_id = a.company_id
1306                                AND for_add != 1
1307                                AND amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1308                                AND created_at IS NOT NULL 
1309                                AND YEAR(created_at) = {$currentYear}
1310                            ) total 
1311                        FROM 
1312                            tbl_companies a 
1313                            WHERE is_send_executive = 1
1314                            -- AND company_id IN ({$this->companyId})
1315                        ORDER BY 
1316                            3 DESC
1317                        ";
1318
1319                $companies = DB::select($query);
1320
1321                $li = [];
1322    
1323                foreach ($companies as $company) {
1324                    $result = (object) $this->get_executive_reports($company->company_id, true, $previousYear);
1325                    $result->name = $company->name;
1326                    array_push($li, $result);
1327                }
1328    
1329                usort($li, fn($a, $b) => $b->totalBudgets - $a->totalBudgets);
1330
1331                $body .= '<ul>';
1332
1333                foreach ($li as $result) {
1334                    $totalBudgets = number_format($result->totalBudgets, 0, ',', '.');
1335                    $totalAmount = $result->totalAmount;
1336                    $dividendMaintenance = $result->dividendMaintenance;
1337                    $dividendCorrectives = $result->dividendCorrectives;
1338                    $percentageMoreThanLastWeek = $result->percentageMoreThanLastWeek;
1339                    $approvalRate = $result->approvalRate;
1340
1341                    $body .= "<li>{$totalBudgets} order(s) in “<b>{$result->name}</b>” ({$dividendMaintenance}% “Mantenimiento”, {$dividendCorrectives}% “Correctivos”) for total of <b>{$totalAmount}</b> <i> - approval rate of <b>{$approvalRate}%</b></i></li>";
1342                }
1343
1344                $body .= '</ul><br><br>';
1345
1346                $imgpath = \File::get(public_path('fireservicetitan.png'));
1347
1348                $content = $body;
1349
1350                $body .= '<br><p>Fire Service Titan</p>';
1351                $body .= "<img src='cid:fireservicetitan' style='height: 45px;' />";
1352
1353                $html = '<!DOCTYPE html>';
1354                $html .= '<html>';
1355                $html .= '<head>';
1356                $html .= '<meta charset="UTF-8">';
1357                $html .= '<meta name="viewport" content="width=device-width, initial-scale=1.0">';
1358                $html .= '</head>';
1359                $html .= '<body>';
1360                $html .= $body;
1361                $html .= '</body>';
1362                $html .= '</html>';
1363
1364                $email = new \SendGrid\Mail\Mail;
1365
1366                $email->setFrom('fire@fire.es', 'Fire Service Titan');
1367                $email->setSubject($subject);
1368
1369                $email->addTo($toEmail);
1370                $email->addContent('text/html', $html);
1371
1372                $email->addAttachment(
1373                    $imgpath,
1374                    'image/png',
1375                    'fireservicetitan.png',
1376                    'inline',
1377                    'fireservicetitan'
1378                );
1379    
1380                $sendgrid = new \SendGrid(config('services.sendgrid.api_key'));
1381    
1382                $response = $sendgrid->send($email);
1383                if ($response->statusCode() == 202) {
1384                    Log::channel('email_log')->info('ID:'.$toEmail.' - EXECUTIVE REPORT EMAIL NOTIFICATION SENT');
1385
1386                    TblNotifications::create(
1387                        [
1388                            'user_id' => $toUserId,
1389                            'content' => $content,
1390                            'is_open' => 1,
1391                            'created_by' => 'System',
1392                        ]
1393                    );
1394
1395                    TblNotificationLogs::create(
1396                        [
1397                            'commercial' => $toName,
1398                            'total_executive_report' => 1,
1399                            'created_by' => $sentBy
1400                        ]
1401                    );
1402                } else {
1403                    $error = true;
1404                    Log::channel('email_log')->error('ID:'.$toEmail.' - '.$response->body());
1405                }
1406
1407                $body = '';
1408
1409                return response([
1410                    'message' => 'OK',
1411                ]);
1412            }
1413
1414        } catch (\Exception $e) {
1415            report(AppException::fromException($e, 'SEND_EXECUTIVE_REPORT_EXCEPTION'));
1416            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1417        }
1418
1419    }
1420
1421    function get_executive_reports($companyId = null, $wholeYear = false, $year = null): array{
1422
1423        $where = '';
1424        $whereYear = '';
1425        $whereMonth = "DATE_FORMAT(q.created_at, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m') ";
1426
1427        if ($companyId != null) {
1428            $where = " AND company_id = {$companyId} ";
1429        }
1430
1431        $now = date('Y-m-d');
1432        $weekNumber = date('W', strtotime($now));
1433        $monday = date('F j, Y', strtotime($now.' - '.(date('N', strtotime($now)) - 1).' days'));
1434
1435        $mondayDate = date('Y-m-d', strtotime($now.' - '.(date('N', strtotime($now)) - 1).' days'));
1436        $sundayDate = date('Y-m-d', strtotime($mondayDate.' + 6 days'));
1437
1438        $mondayLastWeekDate = date('Y-m-d', strtotime($mondayDate.' - 1 week'));
1439        $sundayLastWeekDate = date('Y-m-d', strtotime($mondayLastWeekDate.' + 6 days'));
1440
1441        $whereAccMonth = '';
1442        if ($wholeYear == false) {
1443            $whereYear = " AND DATE_FORMAT(q.created_at, '%Y-%m-%d') BETWEEN '{$mondayDate}' AND '{$sundayDate}' ";
1444            $whereMonth = " AND DATE_FORMAT(q.created_at, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m') ";
1445            $whereAccMonth = " AND DATE_FORMAT(q.acceptance_date, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m') ";
1446        } else {
1447            $whereYear = " AND YEAR(q.created_at) = {$year} ";
1448            $whereMonth = " AND YEAR(q.created_at) = {$year} ";
1449            $whereAccMonth = " AND YEAR(q.acceptance_date) = {$year} ";
1450        }
1451
1452        $query = "SELECT 
1453                    COUNT(q.created_at) totalBudgets, 
1454                    SUM(q.amount) totalAmount, 
1455                    COALESCE(
1456                        SUM(CASE WHEN bt.budget_type_group_id = 3 THEN 1 END)
1457                        , 0
1458                    ) / 
1459                    COUNT(q.created_at) * 100 dividendMaintenance, 
1460                    COALESCE(
1461                        SUM(CASE WHEN bt.budget_type_group_id = 5 THEN 1 END)
1462                        , 0
1463                    ) / 
1464                    COUNT(q.created_at) * 100 dividendCorrectives 
1465                FROM 
1466                    tbl_quotations q 
1467                    LEFT JOIN tbl_budget_types bt ON q.budget_type_id = bt.budget_type_id 
1468                WHERE 
1469                    q.for_add != 1
1470                    AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1471                    AND q.created_at IS NOT NULL 
1472                    AND q.company_id IN (SELECT company_id FROM tbl_companies WHERE is_send_executive = 1 {$where})
1473                    AND q.budget_type_id != 7
1474                    AND YEAR(q.created_at) = YEAR(q.issue_date)
1475                    {$whereYear}
1476                    ";
1477
1478        $result = DB::select($query)[0];
1479
1480        $totalBudgets = $result->totalBudgets;
1481        $totalAmount = $this->currency($result->totalAmount, 1, 0);
1482        $totalAmountC = $result->totalAmount;
1483        $dividendMaintenance = number_format($result->dividendMaintenance, 0);
1484        $dividendCorrectives = number_format($result->dividendCorrectives, 0);
1485
1486        $query = "SELECT 
1487                        COUNT(q.created_at) totalBudgetLastWeek 
1488                    FROM tbl_quotations q WHERE 
1489                    DATE_FORMAT(q.created_at, '%Y-%m-%d') BETWEEN '{$mondayLastWeekDate}' 
1490                    AND '{$sundayLastWeekDate}' 
1491                    AND q.for_add != 1
1492                    AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1493                    AND q.created_at IS NOT NULL 
1494                    AND q.budget_type_id != 7
1495                    AND q.budget_type_id IS NOT NULL 
1496                    AND YEAR(q.created_at) = YEAR(q.issue_date)
1497                    AND q.company_id IN (SELECT company_id FROM tbl_companies WHERE is_send_executive = 1 {$where})";
1498
1499        $result = DB::select($query)[0];
1500
1501        $percentageMoreThanLastWeek = 0;
1502
1503        if ($result->totalBudgetLastWeek > 0) {
1504            $percentageMoreThanLastWeek = number_format($totalBudgets / $result->totalBudgetLastWeek * 100, 2);
1505        }
1506
1507        $query = "SELECT 
1508                    SUM(q.amount) totalAmount
1509                FROM tbl_quotations q WHERE 
1510                q.for_add != 1
1511                AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1512                AND q.created_at IS NOT NULL 
1513                AND q.budget_type_id != 7
1514                AND q.budget_type_id IS NOT NULL 
1515                AND YEAR(q.created_at) = YEAR(q.issue_date)
1516                AND q.company_id IN (SELECT company_id FROM tbl_companies WHERE is_send_executive = 1 {$where})
1517                {$whereMonth}";
1518
1519        $result = DB::select($query)[0];
1520
1521        $totalAmountCC = $result->totalAmount;
1522
1523        $query = "SELECT 
1524                COUNT(CASE WHEN q.created_at IS NOT NULL THEN 1 END) totalBudgets,
1525                COUNT(CASE WHEN q.acceptance_date IS NOT NULL AND q.budget_status_id = 3 THEN 1 END) totalAcceptance,
1526                SUM(CASE WHEN q.acceptance_date IS NOT NULL AND q.budget_status_id = 3 THEN q.amount END) totalAmountApproved
1527            FROM tbl_quotations q WHERE 
1528            q.for_add != 1
1529            AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1530            AND q.created_at IS NOT NULL 
1531            AND q.budget_type_id != 7
1532            AND q.budget_type_id IS NOT NULL 
1533            AND YEAR(q.created_at) = YEAR(q.issue_date)
1534            AND q.company_id IN (SELECT company_id FROM tbl_companies WHERE is_send_executive = 1 {$where})
1535            {$whereAccMonth}";
1536
1537        $result = DB::select($query)[0];
1538
1539        $approvalRate = 0;
1540        $totalAmountApproved = $result->totalAmountApproved;
1541        if ($totalAmountCC > 0) {
1542            $approvalRate = number_format(($totalAmountApproved / $totalAmountCC) * 100, 0);
1543        }
1544
1545        $query = "SELECT SUM(goal_for_the_year) goalForTheYear FROM tbl_companies WHERE is_send_executive = 1 {$where}";
1546        $result = DB::select($query)[0];
1547
1548        return [
1549            'weekNumber' => $weekNumber,
1550            'monday' => $monday,
1551            'totalBudgets' => $totalBudgets,
1552            'totalAmount' => $totalAmount,
1553            'totalAmountApproved' => $this->currency($totalAmountApproved, 1, 0),
1554            'dividendMaintenance' => $dividendMaintenance,
1555            'dividendCorrectives' => $dividendCorrectives,
1556            'percentageMoreThanLastWeek' => $percentageMoreThanLastWeek,
1557            'approvalRate' => $approvalRate,
1558            'totalGoalForTheYear' => $result->goalForTheYear
1559        ];
1560
1561    }
1562
1563    public function currency($amount, $withEuro = null, $n = 2)
1564    {
1565
1566        if ($withEuro != null) {
1567            $withEuro = ' €';
1568        }
1569
1570        return number_format($amount, $n, ',', '.').$withEuro;
1571    }
1572
1573    function get_cc_bcc_executive(): ResponseFactory|Response{
1574
1575        try {
1576
1577            $result = TblCcBccExecutiveNotifications::get();
1578
1579            return response(['message' => 'OK', 'data' => $result]);
1580
1581        } catch (\Exception $e) {
1582            report(AppException::fromException($e, 'GET_CC_BCC_EXECUTIVE_EXCEPTION'));
1583            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1584        }
1585    }
1586
1587    function delete_cc_bcc_executive($id): ResponseFactory|Response{
1588
1589        try {
1590            
1591            $id = addslashes((string) $id);
1592
1593            TblCcBccExecutiveNotifications::where('id', $id)->delete();
1594
1595            return response(['message' => 'OK']);
1596
1597        } catch (\Exception $e) {
1598            report(AppException::fromException($e, 'DELETE_CC_BCC_EXECUTIVE_EXCEPTION'));
1599            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1600        }
1601    }
1602
1603    function update_cc_bcc_executive(Request $request, $id): ResponseFactory|Response{
1604
1605        try {
1606
1607            $data = $request->all();
1608            $id = addslashes((string) $id);
1609
1610            $x = $this->isEmailValid($data['email']);
1611
1612            if($x){
1613                $result = TblCcBccExecutiveNotifications::where('email', strtolower((string) $data['email']))->count();
1614
1615                if ($result > 0) {
1616                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
1617                } else {
1618                    $data['updated_at'] = date('Y-m-d H:i:s');
1619                    TblCcBccExecutiveNotifications::where('id', $id)->update($data);
1620                }
1621            } else {
1622                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
1623            }
1624
1625            return response(['message' => 'OK']);
1626
1627        } catch (\Exception $e) {
1628            report(AppException::fromException($e, 'UPDATE_CC_BCC_EXECUTIVE_EXCEPTION'));
1629            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1630        }
1631    }
1632
1633    function create_cc_bcc_executive(Request $request): ResponseFactory|Response{
1634
1635        try {
1636
1637            $data = $request->all();
1638
1639            $x = $this->isEmailValid($data['email']);
1640
1641            if($x){
1642                $result = TblCcBccExecutiveNotifications::where('email', strtolower((string) $data['email']))->count();
1643
1644                if ($result > 0) {
1645                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
1646                } else {
1647                    TblCcBccExecutiveNotifications::create($data);
1648                }
1649            } else {
1650                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
1651            }
1652
1653            return response(['message' => 'OK']);
1654
1655        } catch (\Exception $e) {
1656            report(AppException::fromException($e, 'CREATE_CC_BCC_EXECUTIVE_EXCEPTION'));
1657            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1658        }
1659    }
1660
1661    function update_notification_settings(Request $request): ResponseFactory|Response{
1662
1663        try {
1664
1665            $data = $request->all();
1666
1667            $data['updated_at'] = date('Y-m-d H:i:s');
1668            TblNotificationSettings::where('id', 1)->update($data);
1669
1670            return response(['message' => 'OK']);
1671
1672        } catch (\Exception $e) {
1673            report(AppException::fromException($e, 'UPDATE_NOTIFICATION_SETTINGS_EXCEPTION'));
1674            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1675        }
1676
1677    }
1678
1679    function get_notification_settings(): ResponseFactory|Response{
1680
1681        try {
1682
1683            $result = TblNotificationSettings::where('id', 1)->first();
1684
1685            return response(['message' => 'OK', 'data' => $result]);
1686
1687        } catch (\Exception $e) {
1688            report(AppException::fromException($e, 'GET_NOTIFICATION_SETTINGS_EXCEPTION'));
1689            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1690        }
1691    }
1692
1693    function send_report_logs(Request $request): ResponseFactory|Response{
1694
1695        try {
1696
1697            $data = $request->all();
1698
1699            $toEmail = config('services.sendgrid.to_email');
1700
1701            $imgpath = \File::get('fireservicetitan.png');
1702
1703            $body = $data['logs'];
1704
1705            $body .= '<br><br><p>Fire Service Titan</p>';
1706            $body .= "<img src='cid:fireservicetitan' style='height: 45px;' />";
1707
1708            $html = '<!DOCTYPE html>';
1709            $html .= '<html>';
1710            $html .= '<head>';
1711            $html .= '<meta charset="UTF-8">';
1712            $html .= '<meta name="viewport" content="width=device-width, initial-scale=1.0">';
1713            $html .= '</head>';
1714            $html .= '<body>';
1715            $html .= $body;
1716            $html .= '</body>';
1717            $html .= '</html>';
1718
1719            $email = new \SendGrid\Mail\Mail;
1720            $subject = 'FST Error report '.date('Y-m-d H:i:s');
1721
1722            $email->setFrom('fire@fire.es', 'Fire Service Titan');
1723            $email->setSubject($subject);
1724
1725            $email->addTo($toEmail);
1726            $email->addContent('text/html', $html);
1727
1728            $email->addAttachment(
1729                $imgpath,
1730                'image/png',
1731                'fireservicetitan.png',
1732                'inline',
1733                'fireservicetitan'
1734            );
1735
1736            $sendgrid = new \SendGrid(config('services.sendgrid.api_key'));
1737
1738            $response = $sendgrid->send($email);
1739            if ($response->statusCode() == 202) {
1740                Log::channel('email_log')->info('ID:'.$toEmail.' - REPORT LOG EMAIL SENT');
1741            }
1742
1743            return response(['message' => 'OK']);
1744
1745        } catch (\Exception $e) {
1746            report(AppException::fromException($e, 'SEND_REPORT_LOGS_EXCEPTION'));
1747            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1748        }
1749    }
1750
1751    function get_emails_acceptance_notifications($companyId): ResponseFactory|Response{
1752
1753        try {
1754
1755            $companyId = addslashes((string) $companyId);
1756
1757            $cc = TblCcAcceptanceNotifications::where('company_id', $companyId)->get();
1758            $to = TblToAcceptanceNotifications::where('company_id', $companyId)->get();
1759
1760            return response(['message' => 'OK', 'cc' => $cc, 'to' => $to]);
1761
1762        } catch (\Exception $e) {
1763            report(AppException::fromException($e, 'GET_EMAILS_ACCEPTANCE_NOTIFICATIONS_EXCEPTION'));
1764            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1765        }
1766    }
1767
1768    function create_to_email_acceptance(Request $request): ResponseFactory|Response{
1769
1770        try {
1771
1772            $data = $request->all();
1773
1774            $x = $this->isEmailValid($data['email']);
1775
1776            if($x){
1777                $result = TblToAcceptanceNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
1778
1779                if ($result > 0) {
1780                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
1781                } else {
1782                    TblToAcceptanceNotifications::create($data);
1783                }
1784            } else {
1785                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
1786            }
1787
1788            return response(['message' => 'OK']);
1789
1790        } catch (\Exception $e) {
1791            report(AppException::fromException($e, 'CREATE_TO_EMAIL_ACCEPTANCE_EXCEPTION'));
1792            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1793        }
1794    }
1795
1796    function delete_to_email_acceptance($id): ResponseFactory|Response{
1797
1798        try {
1799            
1800            $id = addslashes((string) $id);
1801
1802            TblToAcceptanceNotifications::where('id', $id)->delete();
1803
1804            return response(['message' => 'OK']);
1805
1806        } catch (\Exception $e) {
1807            report(AppException::fromException($e, 'DELETE_TO_EMAIL_ACCEPTANCE_EXCEPTION'));
1808            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1809        }
1810    }
1811
1812    function update_to_email_acceptance(Request $request, $id): ResponseFactory|Response{
1813
1814        try {
1815
1816            $data = $request->all();
1817            $id = addslashes((string) $id);
1818
1819            $x = $this->isEmailValid($data['email']);
1820
1821            if($x){
1822                $to = TblToAcceptanceNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
1823                $cc = TblCcAcceptanceNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
1824
1825                if ($to > 0 || $cc > 0) {
1826                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
1827                } else {
1828                    $data['updated_at'] = date('Y-m-d H:i:s');
1829                    TblToAcceptanceNotifications::where('id', $id)->update($data);
1830                }
1831            } else {
1832                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
1833            }
1834
1835            return response(['message' => 'OK']);
1836
1837        } catch (\Exception $e) {
1838            report(AppException::fromException($e, 'UPDATE_TO_EMAIL_ACCEPTANCE_EXCEPTION'));
1839            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1840        }
1841    }
1842
1843    function create_cc_email_acceptance(Request $request): ResponseFactory|Response{
1844
1845        try {
1846
1847            $data = $request->all();
1848
1849            $x = $this->isEmailValid($data['email']);
1850
1851            if($x){
1852                $result = TblCcAcceptanceNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
1853
1854                if ($result > 0) {
1855                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
1856                } else {
1857                    TblCcAcceptanceNotifications::create($data);
1858                }
1859            } else {
1860                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
1861            }
1862
1863            return response(['message' => 'OK']);
1864
1865        } catch (\Exception $e) {
1866            report(AppException::fromException($e, 'CREATE_CC_EMAIL_ACCEPTANCE_EXCEPTION'));
1867            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1868        }
1869    }
1870
1871    function delete_cc_email_acceptance($id): ResponseFactory|Response{
1872
1873        try {
1874            
1875            $id = addslashes((string) $id);
1876
1877            TblCcAcceptanceNotifications::where('id', $id)->delete();
1878
1879            return response(['message' => 'OK']);
1880
1881        } catch (\Exception $e) {
1882            report(AppException::fromException($e, 'DELETE_CC_EMAIL_ACCEPTANCE_EXCEPTION'));
1883            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1884        }
1885    }
1886
1887    function update_cc_email_acceptance(Request $request, $id): ResponseFactory|Response{
1888
1889        try {
1890
1891            $data = $request->all();
1892            $id = addslashes((string) $id);
1893
1894            $x = $this->isEmailValid($data['email']);
1895
1896            if($x){
1897                $cc = TblCcAcceptanceNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
1898                $to = TblToAcceptanceNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
1899
1900                if ($cc > 0 || $to > 0) {
1901                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
1902                } else {
1903                    $data['updated_at'] = date('Y-m-d H:i:s');
1904                    TblCcAcceptanceNotifications::where('id', $id)->update($data);
1905                }
1906            } else {
1907                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
1908            }
1909
1910            return response(['message' => 'OK']);
1911
1912        } catch (\Exception $e) {
1913            report(AppException::fromException($e, 'UPDATE_CC_EMAIL_ACCEPTANCE_EXCEPTION'));
1914            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1915        }
1916    }
1917
1918    function list_last_follow_up_date($companyId): ResponseFactory|Response{
1919
1920        try {
1921            
1922            $companyId = addslashes((string) $companyId);
1923
1924            $result = TblLastFollowUpDate::where('company_id', $companyId)->orderBy('row_id', 'ASC')->get();
1925
1926            $data = [];
1927
1928            for ($i = 0; $i < count($result); $i++) {
1929                $data[$result[$i]->row_id]['row_id'] = $result[$i]->row_id;
1930                $data[$result[$i]->row_id]['last_follow_up_date'] = $result[$i]->last_follow_up_date;
1931                if ($result[$i]->budget_type_id != null) {
1932                    $data[$result[$i]->row_id]['budget_types'][] = $result[$i]->budget_type_id;
1933                }
1934            }
1935
1936            return response(['message' => 'OK', 'data' => array_values($data)]);
1937
1938        } catch (\Exception $e) {
1939            report(AppException::fromException($e, 'LIST_LAST_FOLLOW_UP_DATE_EXCEPTION'));
1940            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1941        }
1942
1943    }
1944
1945    public function update_last_follow_up_date_working_days(Request $request)
1946    {
1947
1948        try {
1949
1950            $data = $request->all();
1951            $companyId = addslashes((string) $data['company_id']);
1952            $rowId = addslashes((string) $data['row_id']);
1953
1954            unset($data['company_id']);
1955            unset($data['row_id']);
1956
1957            $data['updated_at'] = date('Y-m-d H:i:s');
1958
1959            TblLastFollowUpDate::where('company_id', $companyId)->where('row_id', $rowId)->update($data);
1960
1961            return $this->list_last_follow_up_date($companyId);
1962
1963        } catch (\Exception $e) {
1964            report(AppException::fromException($e, 'UPDATE_LAST_FOLLOW_UP_DATE_WORKING_DAYS_EXCEPTION'));
1965            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1966        }
1967
1968    }
1969
1970    public function update_last_follow_up_date(Request $request)
1971    {
1972
1973        try {
1974
1975            $data = $request->all();
1976            $companyId = addslashes((string) $data['company_id']);
1977            $rowId = addslashes((string) $data['row_id']);
1978            unset($data['company_id']);
1979            unset($data['row_id']);
1980
1981            $previousData = TblLastFollowUpDate::where('company_id', $companyId)->where('row_id', $rowId)->first();
1982
1983            $lastBudgetTypeId = end($data['budget_type_id']);
1984            $lastFollowUpDate = TblLastFollowUpDate::where('company_id', $companyId)->where('budget_type_id', $lastBudgetTypeId)->first();
1985
1986            TblLastFollowUpDate::where('company_id', $companyId)->whereIn('budget_type_id', $data['budget_type_id'])->delete();
1987            TblLastFollowUpDate::where('company_id', $companyId)->where('row_id', $rowId)->delete();
1988
1989            foreach ($data['budget_type_id'] as $budgetTypeId) {
1990                TblLastFollowUpDate::create(
1991                    [
1992                        'company_id' => $companyId,
1993                        'row_id' => $rowId,
1994                        'budget_type_id' => $budgetTypeId,
1995                        'last_follow_up_date' => $previousData->last_follow_up_date,
1996                        'updated_by' => $data['updated_by'],
1997                        'updated_at' => date('Y-m-d H:i:s')
1998                    ]
1999                );
2000            }
2001
2002            if ($lastFollowUpDate != null) {
2003                if ($lastFollowUpDate->budget_type_id != null) {
2004                    TblLastFollowUpDate::create(
2005                        [
2006                            'company_id' => $companyId,
2007                            'row_id' => $lastFollowUpDate->row_id,
2008                            'budget_type_id' => null,
2009                            'last_follow_up_date' => $lastFollowUpDate->last_follow_up_date,
2010                            'created_by' => $data['updated_by']
2011                        ]
2012                    );
2013                }
2014            }
2015
2016            return $this->list_last_follow_up_date($companyId);
2017
2018        } catch (\Exception $e) {
2019            report(AppException::fromException($e, 'UPDATE_LAST_FOLLOW_UP_DATE_EXCEPTION'));
2020            return response(['message' => 'KO', 'error' => $e->getMessage()]);
2021        }
2022
2023    }
2024
2025    public function delete_last_follow_up_date(Request $request)
2026    {
2027
2028        try {
2029
2030            $data = $request->all();
2031            $companyId = addslashes((string) $data['company_id']);
2032            $rowId = addslashes((string) $data['row_id']);
2033            unset($data['company_id']);
2034            unset($data['row_id']);
2035
2036            $count = TblLastFollowUpDate::where('company_id', $companyId)->where('row_id', $rowId)->count();
2037
2038            if ($count > 1) {
2039                TblLastFollowUpDate::where('company_id', $companyId)->where('budget_type_id', $data['budget_type_id'])->delete();
2040            }else{
2041                TblLastFollowUpDate::where('company_id', $companyId)->where('row_id', $rowId)->update(['budget_type_id' => null]);
2042            }
2043
2044            return $this->list_last_follow_up_date($companyId);
2045
2046        } catch (\Exception $e) {
2047            report(AppException::fromException($e, 'DELETE_LAST_FOLLOW_UP_DATE_EXCEPTION'));
2048            return response(['message' => 'KO', 'error' => $e->getMessage()]);
2049        }
2050
2051    }
2052}