Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
0.00%
0 / 1016
0.00%
0 / 45
 CRAP
0.00%
0 / 1
Notifications
0.00%
0 / 1016
0.00%
0 / 45
 32942
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 / 23
0.00%
0 / 1
 72
 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_contents(public_path('sendfollowups.png'));
309                    $img = "<br><img src='cid:sendfollowups' />";
310                    $body .= $img;
311
312                    $imgpath = file_get_contents(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_contents(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
1090                    if ($user) {
1091                        $this->send_executive_report($item->email, $user->name, $user->id, $data['processed_by']);
1092                        continue;
1093                    }
1094
1095                    // FIRE-1025: don't silently drop recipients whose email
1096                    // isn't in tbl_users. Berta added Duarte on 2026-03-30
1097                    // expecting him to receive the report; the previous
1098                    // `if ($user) { ... }` guard skipped him (and Albert and
1099                    // Pablo) on every Saturday with no log line. Fall back
1100                    // to a sensible display name + user_id = 1 (System) so
1101                    // the email goes out, and surface the missing-user
1102                    // case in the cron log so admins can clean up
1103                    // tbl_cc_bcc_executive_notifications later.
1104                    Log::channel('cron_send_executive_report')->warning(
1105                        "send:executive-report — recipient {$item->email} has no matching tbl_users row; sending anyway with default name and user_id=1 (System)."
1106                    );
1107                    $defaultName = strstr($item->email, '@', true) ?: $item->email;
1108                    $this->send_executive_report($item->email, $defaultName, 1, $data['processed_by']);
1109                }
1110            }
1111
1112            return response([
1113                'message' => 'OK',
1114            ]);
1115
1116        } catch (\Exception $e) {
1117            report(AppException::fromException($e, 'SEND_EXECUTIVE_EXCEPTION'));
1118            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1119        }
1120
1121    }
1122
1123    public function send_executive_report($toEmail, $toName, $toUserId, $sentBy = 'System')
1124    {
1125
1126        try {
1127
1128            $result = TblNotificationSettings::where('id', 1)->first();
1129
1130            $currentYear = date('Y');
1131            $previousYear = date('Y', strtotime('-1 year', strtotime(date('Y-m-d'))));
1132
1133            if ($toEmail != null && $result->is_auto_send_executive == 1) {
1134
1135                if(config('services.sendgrid.staging')){
1136                    $user = TblUsers::where('id', $this->userId)->first();
1137                    $toEmail = $user->email;
1138                }
1139
1140                $result = (object) $this->get_executive_reports();
1141
1142                $weekNumber = $result->weekNumber;
1143                $monday = $result->monday;
1144                $totalBudgets = number_format($result->totalBudgets, 0, ',', '.');
1145                $totalAmountApproved = $result->totalAmountApproved;
1146                $totalAmount = $result->totalAmount;
1147                $dividendMaintenance = $result->dividendMaintenance;
1148                $dividendCorrectives = $result->dividendCorrectives;
1149                $percentageMoreThanLastWeek = $result->percentageMoreThanLastWeek;
1150                $approvalRate = $result->approvalRate;
1151
1152                $subject = "FST executive report week $weekNumber (starting on monday - $monday) - Automated";
1153
1154                $body = "<p>Hello {$toName},</p>";
1155
1156                $url = config('app.frontend_url') . "analytics?week={$weekNumber}";
1157                $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>";
1158                $body .= "<p><b>{$totalBudgets} budget(s)</b> ({$percentageMoreThanLastWeek}% compared to last week) created for a total of <b>{$totalAmount}</b> ({$dividendMaintenance}% “Mantenimiento”, {$dividendCorrectives}% “Correctivos”) - ";
1159                $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>";
1160
1161                $query = "SELECT 
1162                            a.company_id, 
1163                            a.name,
1164                            (
1165                            SELECT 
1166                                COUNT(created_at) 
1167                            FROM 
1168                                tbl_quotations 
1169                            WHERE 
1170                                company_id = a.company_id
1171                                AND for_add = 0 
1172                                AND created_at IS NOT NULL 
1173                                AND issue_date IS NOT NULL
1174                                AND YEAR(created_at) = YEAR(issue_date)
1175                            ) total 
1176                        FROM 
1177                            tbl_companies a 
1178                            WHERE is_send_executive = 1
1179                            -- AND company_id IN ({$this->companyId})
1180                        ORDER BY 
1181                            3 DESC
1182                        ";
1183
1184                $companies = DB::select($query);
1185                
1186                $li = [];
1187    
1188                foreach ($companies as $company) {
1189                    $result = (object) $this->get_executive_reports($company->company_id);
1190                    $result->name = $company->name;
1191                    array_push($li, $result);
1192                }
1193    
1194                usort($li, fn($a, $b) => $b->totalBudgets - $a->totalBudgets);
1195                
1196                $body .= "<ul>";
1197                foreach ($li as $result) {
1198                    $totalBudgets = number_format($result->totalBudgets, 0, ',', '.');
1199                    $totalAmount = $result->totalAmount;
1200                    $dividendMaintenance = $result->dividendMaintenance;
1201                    $dividendCorrectives = $result->dividendCorrectives;
1202                    $percentageMoreThanLastWeek = $result->percentageMoreThanLastWeek;
1203                    $approvalRate = $result->approvalRate;
1204
1205                    $body .= "<li>{$totalBudgets} order(s) in “<b>{$result->name}</b>” ({$dividendMaintenance}% “Mantenimiento”, {$dividendCorrectives}% “Correctivos”) for total of <b>{$totalAmount}</b></li>";
1206                }
1207                $body .= '</ul>';
1208
1209                $result = (object) $this->get_executive_reports(null, true, $currentYear);
1210
1211                $totalBudgets = number_format($result->totalBudgets, 0, ',', '.');
1212                $totalAmountApproved = $result->totalAmountApproved;
1213                $totalAmount = $result->totalAmount;
1214                $dividendMaintenance = $result->dividendMaintenance;
1215                $dividendCorrectives = $result->dividendCorrectives;
1216                $percentageMoreThanLastWeek = $result->percentageMoreThanLastWeek;
1217                $approvalRate = $result->approvalRate;
1218                $totalDaysFromStartOfYear = date('z') + 1;
1219                $totalDaysOfCurrentYear = date('z', mktime(0, 0, 0, 12, 31, date('Y'))) + 1;
1220                $expected = 0;
1221
1222                $totalGoalForTheYear = 0;
1223                if ($result->totalGoalForTheYear > 0) {
1224                    $totalGoalForTheYear = number_format(($result->totalBudgets / $result->totalGoalForTheYear) * 100, 0, ',', '.');
1225                    $expected = number_format(($totalDaysFromStartOfYear / $totalDaysOfCurrentYear) * 100, 0, ',', '.');
1226                }
1227
1228                $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”) ";
1229                $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>";
1230
1231                $query = "SELECT 
1232                            a.company_id, 
1233                            a.name,
1234                            (
1235                            SELECT 
1236                                COUNT(created_at) 
1237                            FROM 
1238                                tbl_quotations 
1239                            WHERE 
1240                                company_id = a.company_id
1241                                AND for_add != 1
1242                                AND amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1243                                AND created_at IS NOT NULL 
1244                                AND YEAR(created_at) = {$currentYear}                                
1245                            ) total 
1246                        FROM 
1247                            tbl_companies a 
1248                            WHERE is_send_executive = 1
1249                            -- AND company_id IN ({$this->companyId})
1250                        ORDER BY 
1251                            3 DESC
1252                        ";
1253
1254                $companies = DB::select($query);
1255
1256                $li = [];
1257    
1258                foreach ($companies as $company) {
1259                    $result = (object) $this->get_executive_reports($company->company_id, true, $currentYear);
1260                    $result->name = $company->name;
1261                    array_push($li, $result);
1262                }
1263    
1264                usort($li, fn($a, $b) => $b->totalBudgets - $a->totalBudgets);
1265
1266                $body .= '<ul>';
1267                foreach ($li as $result) {
1268
1269                    $totalBudgets = number_format($result->totalBudgets, 0, ',', '.');
1270                    $totalAmountApproved = $result->totalAmountApproved;
1271                    $totalAmount = $result->totalAmount;
1272                    $dividendMaintenance = $result->dividendMaintenance;
1273                    $dividendCorrectives = $result->dividendCorrectives;
1274                    $percentageMoreThanLastWeek = $result->percentageMoreThanLastWeek;
1275                    $approvalRate = $result->approvalRate;
1276                    $goalForTheYear = number_format($result->totalGoalForTheYear, 0, ',', '.');
1277                    $totalDaysFromStartOfYear = date('z') + 1;
1278                    $totalDaysOfCurrentYear = date('z', mktime(0, 0, 0, 12, 31, date('Y'))) + 1;
1279                    $expected = 0;
1280
1281                    $totalGoalForTheYear = 0;
1282                    $style = "style='color: green'";
1283                    if ($result->totalGoalForTheYear > 0) {
1284                        $totalGoalForTheYear = number_format(($result->totalBudgets / $result->totalGoalForTheYear) * 100, 0, ',', '.');
1285                        $expected = number_format(($totalDaysFromStartOfYear / $totalDaysOfCurrentYear) * 100, 0, ',', '.');
1286
1287                        if ($totalGoalForTheYear < $expected) {
1288                            $style = "style='color: red'";
1289                        }
1290                    }
1291
1292                    $body .= "<li>{$totalBudgets} order(s) in “<b>{$result->name}</b>” ({$dividendMaintenance}% “Mantenimiento”, {$dividendCorrectives}% “Correctivos”) for total of <b>{$totalAmount}</b></li>";
1293                    $body .= '<ul>';
1294                    $body .= "<li><i><b>{$goalForTheYear} goal</b> for the year. <b {$style}>{$totalGoalForTheYear}%</b> accomplished to date vs <b>{$expected}% expected</b></i></li>";
1295                    $body .= "<li><i>Approval rate of <b>{$approvalRate}%</b> (<span style='color: blue;'>{$totalAmountApproved} approved</span>)</i></li>";
1296                    $body .= '</ul>';
1297                }
1298
1299                $body .= '</ul>';
1300
1301                $result = (object) $this->get_executive_reports(null, true, $previousYear);
1302
1303                $totalBudgets = number_format($result->totalBudgets, 0, ',', '.');
1304                $totalAmount = $result->totalAmount;
1305                $dividendMaintenance = $result->dividendMaintenance;
1306                $dividendCorrectives = $result->dividendCorrectives;
1307                $percentageMoreThanLastWeek = $result->percentageMoreThanLastWeek;
1308                $approvalRate = $result->approvalRate;
1309
1310                $body .= "<p>For your reference, in <b>{$previousYear}</b>, <b>{$totalBudgets} budgets for a total of {$totalAmount}</b> ({$dividendMaintenance}% “Mantenimientos”, {$dividendCorrectives}% “Correctivos”) ";
1311                $body .= "were created across all companies - <i>we have an approval rate of <b>{$approvalRate}%</b> for the year</i></p>";
1312
1313                $query = "SELECT 
1314                            a.company_id, 
1315                            a.name,
1316                            (
1317                            SELECT 
1318                                COUNT(created_at) 
1319                            FROM 
1320                                tbl_quotations 
1321                            WHERE 
1322                                company_id = a.company_id
1323                                AND for_add != 1
1324                                AND amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1325                                AND created_at IS NOT NULL 
1326                                AND YEAR(created_at) = {$currentYear}
1327                            ) total 
1328                        FROM 
1329                            tbl_companies a 
1330                            WHERE is_send_executive = 1
1331                            -- AND company_id IN ({$this->companyId})
1332                        ORDER BY 
1333                            3 DESC
1334                        ";
1335
1336                $companies = DB::select($query);
1337
1338                $li = [];
1339    
1340                foreach ($companies as $company) {
1341                    $result = (object) $this->get_executive_reports($company->company_id, true, $previousYear);
1342                    $result->name = $company->name;
1343                    array_push($li, $result);
1344                }
1345    
1346                usort($li, fn($a, $b) => $b->totalBudgets - $a->totalBudgets);
1347
1348                $body .= '<ul>';
1349
1350                foreach ($li as $result) {
1351                    $totalBudgets = number_format($result->totalBudgets, 0, ',', '.');
1352                    $totalAmount = $result->totalAmount;
1353                    $dividendMaintenance = $result->dividendMaintenance;
1354                    $dividendCorrectives = $result->dividendCorrectives;
1355                    $percentageMoreThanLastWeek = $result->percentageMoreThanLastWeek;
1356                    $approvalRate = $result->approvalRate;
1357
1358                    $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>";
1359                }
1360
1361                $body .= '</ul><br><br>';
1362
1363                $imgpath = file_get_contents(public_path('fireservicetitan.png'));
1364
1365                $content = $body;
1366
1367                $body .= '<br><p>Fire Service Titan</p>';
1368                $body .= "<img src='cid:fireservicetitan' style='height: 45px;' />";
1369
1370                $html = '<!DOCTYPE html>';
1371                $html .= '<html>';
1372                $html .= '<head>';
1373                $html .= '<meta charset="UTF-8">';
1374                $html .= '<meta name="viewport" content="width=device-width, initial-scale=1.0">';
1375                $html .= '</head>';
1376                $html .= '<body>';
1377                $html .= $body;
1378                $html .= '</body>';
1379                $html .= '</html>';
1380
1381                $email = new \SendGrid\Mail\Mail;
1382
1383                $email->setFrom('fire@fire.es', 'Fire Service Titan');
1384                $email->setSubject($subject);
1385
1386                $email->addTo($toEmail);
1387                $email->addContent('text/html', $html);
1388
1389                $email->addAttachment(
1390                    $imgpath,
1391                    'image/png',
1392                    'fireservicetitan.png',
1393                    'inline',
1394                    'fireservicetitan'
1395                );
1396    
1397                $sendgrid = new \SendGrid(config('services.sendgrid.api_key'));
1398    
1399                $response = $sendgrid->send($email);
1400                if ($response->statusCode() == 202) {
1401                    Log::channel('email_log')->info('ID:'.$toEmail.' - EXECUTIVE REPORT EMAIL NOTIFICATION SENT');
1402
1403                    TblNotifications::create(
1404                        [
1405                            'user_id' => $toUserId,
1406                            'content' => $content,
1407                            'is_open' => 1,
1408                            'created_by' => 'System',
1409                        ]
1410                    );
1411
1412                    TblNotificationLogs::create(
1413                        [
1414                            'commercial' => $toName,
1415                            'total_executive_report' => 1,
1416                            'created_by' => $sentBy
1417                        ]
1418                    );
1419                } else {
1420                    $error = true;
1421                    Log::channel('email_log')->error('ID:'.$toEmail.' - '.$response->body());
1422                }
1423
1424                $body = '';
1425
1426                return response([
1427                    'message' => 'OK',
1428                ]);
1429            }
1430
1431        } catch (\Exception $e) {
1432            report(AppException::fromException($e, 'SEND_EXECUTIVE_REPORT_EXCEPTION'));
1433            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1434        }
1435
1436    }
1437
1438    function get_executive_reports($companyId = null, $wholeYear = false, $year = null): array{
1439
1440        $where = '';
1441        $whereYear = '';
1442        $whereMonth = "DATE_FORMAT(q.created_at, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m') ";
1443
1444        if ($companyId != null) {
1445            $where = " AND company_id = {$companyId} ";
1446        }
1447
1448        $now = date('Y-m-d');
1449        $weekNumber = date('W', strtotime($now));
1450        $monday = date('F j, Y', strtotime($now.' - '.(date('N', strtotime($now)) - 1).' days'));
1451
1452        $mondayDate = date('Y-m-d', strtotime($now.' - '.(date('N', strtotime($now)) - 1).' days'));
1453        $sundayDate = date('Y-m-d', strtotime($mondayDate.' + 6 days'));
1454
1455        $mondayLastWeekDate = date('Y-m-d', strtotime($mondayDate.' - 1 week'));
1456        $sundayLastWeekDate = date('Y-m-d', strtotime($mondayLastWeekDate.' + 6 days'));
1457
1458        $whereAccMonth = '';
1459        if ($wholeYear == false) {
1460            $whereYear = " AND DATE_FORMAT(q.created_at, '%Y-%m-%d') BETWEEN '{$mondayDate}' AND '{$sundayDate}' ";
1461            $whereMonth = " AND DATE_FORMAT(q.created_at, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m') ";
1462            $whereAccMonth = " AND DATE_FORMAT(q.acceptance_date, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m') ";
1463        } else {
1464            $whereYear = " AND YEAR(q.created_at) = {$year} ";
1465            $whereMonth = " AND YEAR(q.created_at) = {$year} ";
1466            $whereAccMonth = " AND YEAR(q.acceptance_date) = {$year} ";
1467        }
1468
1469        $query = "SELECT 
1470                    COUNT(q.created_at) totalBudgets, 
1471                    SUM(q.amount) totalAmount, 
1472                    COALESCE(
1473                        SUM(CASE WHEN bt.budget_type_group_id = 3 THEN 1 END)
1474                        , 0
1475                    ) / 
1476                    COUNT(q.created_at) * 100 dividendMaintenance, 
1477                    COALESCE(
1478                        SUM(CASE WHEN bt.budget_type_group_id = 5 THEN 1 END)
1479                        , 0
1480                    ) / 
1481                    COUNT(q.created_at) * 100 dividendCorrectives 
1482                FROM 
1483                    tbl_quotations q 
1484                    LEFT JOIN tbl_budget_types bt ON q.budget_type_id = bt.budget_type_id 
1485                WHERE 
1486                    q.for_add != 1
1487                    AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1488                    AND q.created_at IS NOT NULL 
1489                    AND q.company_id IN (SELECT company_id FROM tbl_companies WHERE is_send_executive = 1 {$where})
1490                    AND q.budget_type_id != 7
1491                    AND YEAR(q.created_at) = YEAR(q.issue_date)
1492                    {$whereYear}
1493                    ";
1494
1495        $result = DB::select($query)[0];
1496
1497        $totalBudgets = $result->totalBudgets;
1498        $totalAmount = $this->currency($result->totalAmount, 1, 0);
1499        $totalAmountC = $result->totalAmount;
1500        $dividendMaintenance = number_format($result->dividendMaintenance, 0);
1501        $dividendCorrectives = number_format($result->dividendCorrectives, 0);
1502
1503        $query = "SELECT 
1504                        COUNT(q.created_at) totalBudgetLastWeek 
1505                    FROM tbl_quotations q WHERE 
1506                    DATE_FORMAT(q.created_at, '%Y-%m-%d') BETWEEN '{$mondayLastWeekDate}' 
1507                    AND '{$sundayLastWeekDate}' 
1508                    AND q.for_add != 1
1509                    AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1510                    AND q.created_at IS NOT NULL 
1511                    AND q.budget_type_id != 7
1512                    AND q.budget_type_id IS NOT NULL 
1513                    AND YEAR(q.created_at) = YEAR(q.issue_date)
1514                    AND q.company_id IN (SELECT company_id FROM tbl_companies WHERE is_send_executive = 1 {$where})";
1515
1516        $result = DB::select($query)[0];
1517
1518        $percentageMoreThanLastWeek = 0;
1519
1520        if ($result->totalBudgetLastWeek > 0) {
1521            $percentageMoreThanLastWeek = number_format($totalBudgets / $result->totalBudgetLastWeek * 100, 2);
1522        }
1523
1524        $query = "SELECT 
1525                    SUM(q.amount) totalAmount
1526                FROM tbl_quotations q WHERE 
1527                q.for_add != 1
1528                AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1529                AND q.created_at IS NOT NULL 
1530                AND q.budget_type_id != 7
1531                AND q.budget_type_id IS NOT NULL 
1532                AND YEAR(q.created_at) = YEAR(q.issue_date)
1533                AND q.company_id IN (SELECT company_id FROM tbl_companies WHERE is_send_executive = 1 {$where})
1534                {$whereMonth}";
1535
1536        $result = DB::select($query)[0];
1537
1538        $totalAmountCC = $result->totalAmount;
1539
1540        $query = "SELECT 
1541                COUNT(CASE WHEN q.created_at IS NOT NULL THEN 1 END) totalBudgets,
1542                COUNT(CASE WHEN q.acceptance_date IS NOT NULL AND q.budget_status_id = 3 THEN 1 END) totalAcceptance,
1543                SUM(CASE WHEN q.acceptance_date IS NOT NULL AND q.budget_status_id = 3 THEN q.amount END) totalAmountApproved
1544            FROM tbl_quotations q WHERE 
1545            q.for_add != 1
1546            AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1547            AND q.created_at IS NOT NULL 
1548            AND q.budget_type_id != 7
1549            AND q.budget_type_id IS NOT NULL 
1550            AND YEAR(q.created_at) = YEAR(q.issue_date)
1551            AND q.company_id IN (SELECT company_id FROM tbl_companies WHERE is_send_executive = 1 {$where})
1552            {$whereAccMonth}";
1553
1554        $result = DB::select($query)[0];
1555
1556        $approvalRate = 0;
1557        $totalAmountApproved = $result->totalAmountApproved;
1558        if ($totalAmountCC > 0) {
1559            $approvalRate = number_format(($totalAmountApproved / $totalAmountCC) * 100, 0);
1560        }
1561
1562        $query = "SELECT SUM(goal_for_the_year) goalForTheYear FROM tbl_companies WHERE is_send_executive = 1 {$where}";
1563        $result = DB::select($query)[0];
1564
1565        return [
1566            'weekNumber' => $weekNumber,
1567            'monday' => $monday,
1568            'totalBudgets' => $totalBudgets,
1569            'totalAmount' => $totalAmount,
1570            'totalAmountApproved' => $this->currency($totalAmountApproved, 1, 0),
1571            'dividendMaintenance' => $dividendMaintenance,
1572            'dividendCorrectives' => $dividendCorrectives,
1573            'percentageMoreThanLastWeek' => $percentageMoreThanLastWeek,
1574            'approvalRate' => $approvalRate,
1575            'totalGoalForTheYear' => $result->goalForTheYear
1576        ];
1577
1578    }
1579
1580    public function currency($amount, $withEuro = null, $n = 2)
1581    {
1582
1583        if ($withEuro != null) {
1584            $withEuro = ' €';
1585        }
1586
1587        return number_format($amount, $n, ',', '.').$withEuro;
1588    }
1589
1590    function get_cc_bcc_executive(): ResponseFactory|Response{
1591
1592        try {
1593
1594            $result = TblCcBccExecutiveNotifications::get();
1595
1596            return response(['message' => 'OK', 'data' => $result]);
1597
1598        } catch (\Exception $e) {
1599            report(AppException::fromException($e, 'GET_CC_BCC_EXECUTIVE_EXCEPTION'));
1600            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1601        }
1602    }
1603
1604    function delete_cc_bcc_executive($id): ResponseFactory|Response{
1605
1606        try {
1607            
1608            $id = addslashes((string) $id);
1609
1610            TblCcBccExecutiveNotifications::where('id', $id)->delete();
1611
1612            return response(['message' => 'OK']);
1613
1614        } catch (\Exception $e) {
1615            report(AppException::fromException($e, 'DELETE_CC_BCC_EXECUTIVE_EXCEPTION'));
1616            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1617        }
1618    }
1619
1620    function update_cc_bcc_executive(Request $request, $id): ResponseFactory|Response{
1621
1622        try {
1623
1624            $data = $request->all();
1625            $id = addslashes((string) $id);
1626
1627            $x = $this->isEmailValid($data['email']);
1628
1629            if($x){
1630                $result = TblCcBccExecutiveNotifications::where('email', strtolower((string) $data['email']))->count();
1631
1632                if ($result > 0) {
1633                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
1634                } else {
1635                    $data['updated_at'] = date('Y-m-d H:i:s');
1636                    TblCcBccExecutiveNotifications::where('id', $id)->update($data);
1637                }
1638            } else {
1639                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
1640            }
1641
1642            return response(['message' => 'OK']);
1643
1644        } catch (\Exception $e) {
1645            report(AppException::fromException($e, 'UPDATE_CC_BCC_EXECUTIVE_EXCEPTION'));
1646            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1647        }
1648    }
1649
1650    function create_cc_bcc_executive(Request $request): ResponseFactory|Response{
1651
1652        try {
1653
1654            $data = $request->all();
1655
1656            $x = $this->isEmailValid($data['email']);
1657
1658            if($x){
1659                $result = TblCcBccExecutiveNotifications::where('email', strtolower((string) $data['email']))->count();
1660
1661                if ($result > 0) {
1662                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
1663                } else {
1664                    TblCcBccExecutiveNotifications::create($data);
1665                }
1666            } else {
1667                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
1668            }
1669
1670            return response(['message' => 'OK']);
1671
1672        } catch (\Exception $e) {
1673            report(AppException::fromException($e, 'CREATE_CC_BCC_EXECUTIVE_EXCEPTION'));
1674            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1675        }
1676    }
1677
1678    function update_notification_settings(Request $request): ResponseFactory|Response{
1679
1680        try {
1681
1682            $data = $request->all();
1683
1684            $data['updated_at'] = date('Y-m-d H:i:s');
1685            TblNotificationSettings::where('id', 1)->update($data);
1686
1687            return response(['message' => 'OK']);
1688
1689        } catch (\Exception $e) {
1690            report(AppException::fromException($e, 'UPDATE_NOTIFICATION_SETTINGS_EXCEPTION'));
1691            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1692        }
1693
1694    }
1695
1696    function get_notification_settings(): ResponseFactory|Response{
1697
1698        try {
1699
1700            $result = TblNotificationSettings::where('id', 1)->first();
1701
1702            return response(['message' => 'OK', 'data' => $result]);
1703
1704        } catch (\Exception $e) {
1705            report(AppException::fromException($e, 'GET_NOTIFICATION_SETTINGS_EXCEPTION'));
1706            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1707        }
1708    }
1709
1710    function send_report_logs(Request $request): ResponseFactory|Response{
1711
1712        try {
1713
1714            $data = $request->all();
1715
1716            $toEmail = config('services.sendgrid.to_email');
1717
1718            $imgpath = file_get_contents(public_path('fireservicetitan.png'));
1719
1720            $body = $data['logs'];
1721
1722            $body .= '<br><br><p>Fire Service Titan</p>';
1723            $body .= "<img src='cid:fireservicetitan' style='height: 45px;' />";
1724
1725            $html = '<!DOCTYPE html>';
1726            $html .= '<html>';
1727            $html .= '<head>';
1728            $html .= '<meta charset="UTF-8">';
1729            $html .= '<meta name="viewport" content="width=device-width, initial-scale=1.0">';
1730            $html .= '</head>';
1731            $html .= '<body>';
1732            $html .= $body;
1733            $html .= '</body>';
1734            $html .= '</html>';
1735
1736            $email = new \SendGrid\Mail\Mail;
1737            $subject = 'FST Error report '.date('Y-m-d H:i:s');
1738
1739            $email->setFrom('fire@fire.es', 'Fire Service Titan');
1740            $email->setSubject($subject);
1741
1742            $email->addTo($toEmail);
1743            $email->addContent('text/html', $html);
1744
1745            $email->addAttachment(
1746                $imgpath,
1747                'image/png',
1748                'fireservicetitan.png',
1749                'inline',
1750                'fireservicetitan'
1751            );
1752
1753            $sendgrid = new \SendGrid(config('services.sendgrid.api_key'));
1754
1755            $response = $sendgrid->send($email);
1756            if ($response->statusCode() == 202) {
1757                Log::channel('email_log')->info('ID:'.$toEmail.' - REPORT LOG EMAIL SENT');
1758            }
1759
1760            return response(['message' => 'OK']);
1761
1762        } catch (\Exception $e) {
1763            report(AppException::fromException($e, 'SEND_REPORT_LOGS_EXCEPTION'));
1764            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1765        }
1766    }
1767
1768    function get_emails_acceptance_notifications($companyId): ResponseFactory|Response{
1769
1770        try {
1771
1772            $companyId = addslashes((string) $companyId);
1773
1774            $cc = TblCcAcceptanceNotifications::where('company_id', $companyId)->get();
1775            $to = TblToAcceptanceNotifications::where('company_id', $companyId)->get();
1776
1777            return response(['message' => 'OK', 'cc' => $cc, 'to' => $to]);
1778
1779        } catch (\Exception $e) {
1780            report(AppException::fromException($e, 'GET_EMAILS_ACCEPTANCE_NOTIFICATIONS_EXCEPTION'));
1781            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1782        }
1783    }
1784
1785    function create_to_email_acceptance(Request $request): ResponseFactory|Response{
1786
1787        try {
1788
1789            $data = $request->all();
1790
1791            $x = $this->isEmailValid($data['email']);
1792
1793            if($x){
1794                $result = TblToAcceptanceNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
1795
1796                if ($result > 0) {
1797                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
1798                } else {
1799                    TblToAcceptanceNotifications::create($data);
1800                }
1801            } else {
1802                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
1803            }
1804
1805            return response(['message' => 'OK']);
1806
1807        } catch (\Exception $e) {
1808            report(AppException::fromException($e, 'CREATE_TO_EMAIL_ACCEPTANCE_EXCEPTION'));
1809            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1810        }
1811    }
1812
1813    function delete_to_email_acceptance($id): ResponseFactory|Response{
1814
1815        try {
1816            
1817            $id = addslashes((string) $id);
1818
1819            TblToAcceptanceNotifications::where('id', $id)->delete();
1820
1821            return response(['message' => 'OK']);
1822
1823        } catch (\Exception $e) {
1824            report(AppException::fromException($e, 'DELETE_TO_EMAIL_ACCEPTANCE_EXCEPTION'));
1825            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1826        }
1827    }
1828
1829    function update_to_email_acceptance(Request $request, $id): ResponseFactory|Response{
1830
1831        try {
1832
1833            $data = $request->all();
1834            $id = addslashes((string) $id);
1835
1836            $x = $this->isEmailValid($data['email']);
1837
1838            if($x){
1839                $to = TblToAcceptanceNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
1840                $cc = TblCcAcceptanceNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
1841
1842                if ($to > 0 || $cc > 0) {
1843                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
1844                } else {
1845                    $data['updated_at'] = date('Y-m-d H:i:s');
1846                    TblToAcceptanceNotifications::where('id', $id)->update($data);
1847                }
1848            } else {
1849                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
1850            }
1851
1852            return response(['message' => 'OK']);
1853
1854        } catch (\Exception $e) {
1855            report(AppException::fromException($e, 'UPDATE_TO_EMAIL_ACCEPTANCE_EXCEPTION'));
1856            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1857        }
1858    }
1859
1860    function create_cc_email_acceptance(Request $request): ResponseFactory|Response{
1861
1862        try {
1863
1864            $data = $request->all();
1865
1866            $x = $this->isEmailValid($data['email']);
1867
1868            if($x){
1869                $result = TblCcAcceptanceNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
1870
1871                if ($result > 0) {
1872                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
1873                } else {
1874                    TblCcAcceptanceNotifications::create($data);
1875                }
1876            } else {
1877                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
1878            }
1879
1880            return response(['message' => 'OK']);
1881
1882        } catch (\Exception $e) {
1883            report(AppException::fromException($e, 'CREATE_CC_EMAIL_ACCEPTANCE_EXCEPTION'));
1884            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1885        }
1886    }
1887
1888    function delete_cc_email_acceptance($id): ResponseFactory|Response{
1889
1890        try {
1891            
1892            $id = addslashes((string) $id);
1893
1894            TblCcAcceptanceNotifications::where('id', $id)->delete();
1895
1896            return response(['message' => 'OK']);
1897
1898        } catch (\Exception $e) {
1899            report(AppException::fromException($e, 'DELETE_CC_EMAIL_ACCEPTANCE_EXCEPTION'));
1900            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1901        }
1902    }
1903
1904    function update_cc_email_acceptance(Request $request, $id): ResponseFactory|Response{
1905
1906        try {
1907
1908            $data = $request->all();
1909            $id = addslashes((string) $id);
1910
1911            $x = $this->isEmailValid($data['email']);
1912
1913            if($x){
1914                $cc = TblCcAcceptanceNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
1915                $to = TblToAcceptanceNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
1916
1917                if ($cc > 0 || $to > 0) {
1918                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
1919                } else {
1920                    $data['updated_at'] = date('Y-m-d H:i:s');
1921                    TblCcAcceptanceNotifications::where('id', $id)->update($data);
1922                }
1923            } else {
1924                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
1925            }
1926
1927            return response(['message' => 'OK']);
1928
1929        } catch (\Exception $e) {
1930            report(AppException::fromException($e, 'UPDATE_CC_EMAIL_ACCEPTANCE_EXCEPTION'));
1931            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1932        }
1933    }
1934
1935    function list_last_follow_up_date($companyId): ResponseFactory|Response{
1936
1937        try {
1938            
1939            $companyId = addslashes((string) $companyId);
1940
1941            $result = TblLastFollowUpDate::where('company_id', $companyId)->orderBy('row_id', 'ASC')->get();
1942
1943            $data = [];
1944
1945            for ($i = 0; $i < count($result); $i++) {
1946                $data[$result[$i]->row_id]['row_id'] = $result[$i]->row_id;
1947                $data[$result[$i]->row_id]['last_follow_up_date'] = $result[$i]->last_follow_up_date;
1948                if ($result[$i]->budget_type_id != null) {
1949                    $data[$result[$i]->row_id]['budget_types'][] = $result[$i]->budget_type_id;
1950                }
1951            }
1952
1953            return response(['message' => 'OK', 'data' => array_values($data)]);
1954
1955        } catch (\Exception $e) {
1956            report(AppException::fromException($e, 'LIST_LAST_FOLLOW_UP_DATE_EXCEPTION'));
1957            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1958        }
1959
1960    }
1961
1962    public function update_last_follow_up_date_working_days(Request $request)
1963    {
1964
1965        try {
1966
1967            $data = $request->all();
1968            $companyId = addslashes((string) $data['company_id']);
1969            $rowId = addslashes((string) $data['row_id']);
1970
1971            unset($data['company_id']);
1972            unset($data['row_id']);
1973
1974            $data['updated_at'] = date('Y-m-d H:i:s');
1975
1976            TblLastFollowUpDate::where('company_id', $companyId)->where('row_id', $rowId)->update($data);
1977
1978            return $this->list_last_follow_up_date($companyId);
1979
1980        } catch (\Exception $e) {
1981            report(AppException::fromException($e, 'UPDATE_LAST_FOLLOW_UP_DATE_WORKING_DAYS_EXCEPTION'));
1982            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1983        }
1984
1985    }
1986
1987    public function update_last_follow_up_date(Request $request)
1988    {
1989
1990        try {
1991
1992            $data = $request->all();
1993            $companyId = addslashes((string) $data['company_id']);
1994            $rowId = addslashes((string) $data['row_id']);
1995            unset($data['company_id']);
1996            unset($data['row_id']);
1997
1998            $previousData = TblLastFollowUpDate::where('company_id', $companyId)->where('row_id', $rowId)->first();
1999
2000            $lastBudgetTypeId = end($data['budget_type_id']);
2001            $lastFollowUpDate = TblLastFollowUpDate::where('company_id', $companyId)->where('budget_type_id', $lastBudgetTypeId)->first();
2002
2003            TblLastFollowUpDate::where('company_id', $companyId)->whereIn('budget_type_id', $data['budget_type_id'])->delete();
2004            TblLastFollowUpDate::where('company_id', $companyId)->where('row_id', $rowId)->delete();
2005
2006            foreach ($data['budget_type_id'] as $budgetTypeId) {
2007                TblLastFollowUpDate::create(
2008                    [
2009                        'company_id' => $companyId,
2010                        'row_id' => $rowId,
2011                        'budget_type_id' => $budgetTypeId,
2012                        'last_follow_up_date' => $previousData->last_follow_up_date,
2013                        'updated_by' => $data['updated_by'],
2014                        'updated_at' => date('Y-m-d H:i:s')
2015                    ]
2016                );
2017            }
2018
2019            if ($lastFollowUpDate != null) {
2020                if ($lastFollowUpDate->budget_type_id != null) {
2021                    TblLastFollowUpDate::create(
2022                        [
2023                            'company_id' => $companyId,
2024                            'row_id' => $lastFollowUpDate->row_id,
2025                            'budget_type_id' => null,
2026                            'last_follow_up_date' => $lastFollowUpDate->last_follow_up_date,
2027                            'created_by' => $data['updated_by']
2028                        ]
2029                    );
2030                }
2031            }
2032
2033            return $this->list_last_follow_up_date($companyId);
2034
2035        } catch (\Exception $e) {
2036            report(AppException::fromException($e, 'UPDATE_LAST_FOLLOW_UP_DATE_EXCEPTION'));
2037            return response(['message' => 'KO', 'error' => $e->getMessage()]);
2038        }
2039
2040    }
2041
2042    public function delete_last_follow_up_date(Request $request)
2043    {
2044
2045        try {
2046
2047            $data = $request->all();
2048            $companyId = addslashes((string) $data['company_id']);
2049            $rowId = addslashes((string) $data['row_id']);
2050            unset($data['company_id']);
2051            unset($data['row_id']);
2052
2053            $count = TblLastFollowUpDate::where('company_id', $companyId)->where('row_id', $rowId)->count();
2054
2055            if ($count > 1) {
2056                TblLastFollowUpDate::where('company_id', $companyId)->where('budget_type_id', $data['budget_type_id'])->delete();
2057            }else{
2058                TblLastFollowUpDate::where('company_id', $companyId)->where('row_id', $rowId)->update(['budget_type_id' => null]);
2059            }
2060
2061            return $this->list_last_follow_up_date($companyId);
2062
2063        } catch (\Exception $e) {
2064            report(AppException::fromException($e, 'DELETE_LAST_FOLLOW_UP_DATE_EXCEPTION'));
2065            return response(['message' => 'KO', 'error' => $e->getMessage()]);
2066        }
2067
2068    }
2069}