Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
0.00%
0 / 1413
0.00%
0 / 61
 CRAP
0.00%
0 / 1
Notifications
0.00%
0 / 1413
0.00%
0 / 61
 61256
0.00%
0 / 1
 __construct
0.00%
0 / 13
0.00%
0 / 1
 20
 list_notifications
0.00%
0 / 24
0.00%
0 / 1
 6
 update_notifications
0.00%
0 / 17
0.00%
0 / 1
 12
 send_follow_up_notification
0.00%
0 / 131
0.00%
0 / 1
 210
 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 / 142
0.00%
0 / 1
 306
 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 / 28
0.00%
0 / 1
 20
 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 / 185
0.00%
0 / 1
 272
 get_executive_reports
0.00%
0 / 65
0.00%
0 / 1
 30
 currency
0.00%
0 / 3
0.00%
0 / 1
 6
 send_weekly_commercial_report
0.00%
0 / 32
0.00%
0 / 1
 272
 send_weekly_commercial_report_email
0.00%
0 / 78
0.00%
0 / 1
 110
 get_weekly_regional_performance
0.00%
0 / 59
0.00%
0 / 1
 72
 query_regional_metrics
0.00%
0 / 26
0.00%
0 / 1
 6
 blank_region_metrics
0.00%
0 / 7
0.00%
0 / 1
 2
 add_region_metrics
0.00%
0 / 3
0.00%
0 / 1
 6
 ytd_diff_pct
0.00%
0 / 3
0.00%
0 / 1
 6
 get_weekly_individual_performance
0.00%
0 / 36
0.00%
0 / 1
 12
 blank_commercial_metrics
0.00%
0 / 8
0.00%
0 / 1
 2
 build_weekly_commercial_report_html
0.00%
0 / 68
0.00%
0 / 1
 20
 wcr_section_title
0.00%
0 / 1
0.00%
0 / 1
 2
 wcr_caption
0.00%
0 / 1
0.00%
0 / 1
 2
 wcr_ytd_row
0.00%
0 / 19
0.00%
0 / 1
 72
 wcr_ytd_prev_row
0.00%
0 / 10
0.00%
0 / 1
 2
 wcr_individual_full_table
0.00%
0 / 19
0.00%
0 / 1
 12
 wcr_individual_below_table
0.00%
0 / 14
0.00%
0 / 1
 12
 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 / 41
0.00%
0 / 1
 20
 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\Exceptions\AppException;
6use App\Jobs\Email\SendFollowUpDigest;
7use App\Models\TblCcAcceptanceNotifications;
8use App\Models\TblCcBccExecutiveNotifications;
9use App\Models\TblCcBccNotifications;
10use App\Models\TblCcBccRequestNotifications;
11use App\Models\TblCcG3WEmailReminders;
12use App\Models\TblCompanies;
13use App\Models\TblCompanyUsers;
14use App\Models\TblLastFollowUpDate;
15use App\Models\TblNotificationLogs;
16use App\Models\TblNotifications;
17use App\Models\TblNotificationSettings;
18use App\Models\TblToAcceptanceNotifications;
19use App\Models\TblUsers;
20use App\Services\SendgridLogger;
21use App\Services\UserCompanies;
22use Illuminate\Contracts\Routing\ResponseFactory;
23use Illuminate\Http\Request;
24use Illuminate\Http\Response;
25use Illuminate\Support\Facades\App;
26use Illuminate\Support\Facades\Artisan;
27use Illuminate\Support\Facades\DB;
28use Illuminate\Support\Facades\Log;
29use SendGrid\Mail\Mail;
30
31class Notifications extends Controller
32{
33    private $locale;
34
35    private $userId;
36
37    private $region;
38
39    private $companyIds;
40
41    private $companyId;
42
43    public function __construct()
44    {
45        $this->locale = request()->header('Locale-Id');
46        $this->userId = request()->header('User-Id');
47        $this->region = request()->header('Region');
48
49        App::setLocale($this->locale);
50
51        $this->companyIds = [];
52
53        if ($this->region != null && $this->region != '' && $this->region != 'All') {
54            $this->region = urldecode((string) $this->region);
55
56            $query = 'SELECT
57                        b.company_id
58                    FROM
59                        tbl_company_users a
60                        LEFT JOIN tbl_companies b ON a.company_id = b.company_id
61                    WHERE
62                        a.user_id = ?
63                        AND b.region = ?';
64
65            $this->companyIds = DB::select($query, [intval($this->userId), $this->region]);
66
67            $this->companyIds = collect($this->companyIds)->pluck('company_id')->toArray();
68        } else {
69            // FIRE-1146: prefer the middleware-resolved attribute; fall back to a fresh fetch only on unattached paths.
70            $this->companyIds = request()->attributes->get('user_company_ids', UserCompanies::forUser((int) $this->userId));
71        }
72
73        $this->companyId = implode(',', $this->companyIds);
74    }
75
76    public function list_notifications(Request $request): ResponseFactory|Response
77    {
78
79        try {
80            // FIRE-1149: paginated rewrite.
81            // Was: full table scan of all of this user's notifications (51k row table),
82            // returned the entire history, counted unread in PHP by iterating every row.
83            // Now: index seek on idx_notifications_user_created for both the unread
84            // count (filtered) and the paginated page.
85            $userId = (int) $request->input('user_id');
86            $perPage = min(100, max(1, (int) $request->input('per_page', 50)));
87            $page = max(1, (int) $request->input('page', 1));
88            $offset = ($page - 1) * $perPage;
89
90            // Unread count — preserves the topbar badge semantics
91            // (topbar.component.ts:212 reads response.total as the bell-icon count).
92            $totalOpenNotification = (int) DB::table('tbl_notifications')
93                ->where('user_id', $userId)
94                ->where('is_open', '>', 0)
95                ->count();
96
97            // Page query — same projection as before (formatted_created_at relative
98            // time string), just with LIMIT / OFFSET and parameterised user_id.
99            $query = "SELECT
100                        notification_id,
101                        user_id,
102                        REPLACE(content, '<br>', '') content,
103                        link,
104                        is_open,
105                        created_by,
106                        created_at,
107                        updated_by,
108                        updated_at,
109                        CASE
110                            WHEN TIMESTAMPDIFF(SECOND, created_at, NOW()) < 60 THEN
111                                CASE
112                                    WHEN TIMESTAMPDIFF(SECOND, created_at, NOW()) = 1 THEN '1 second ago'
113                                    ELSE CONCAT(TIMESTAMPDIFF(SECOND, created_at, NOW()), ' seconds ago')
114                                END
115                            WHEN TIMESTAMPDIFF(MINUTE, created_at, NOW()) < 60 THEN
116                                CASE
117                                    WHEN TIMESTAMPDIFF(MINUTE, created_at, NOW()) = 1 THEN '1 minute ago'
118                                    ELSE CONCAT(TIMESTAMPDIFF(MINUTE, created_at, NOW()), ' minutes ago')
119                                END
120                            WHEN TIMESTAMPDIFF(HOUR, created_at, NOW()) < 24 THEN
121                                CASE
122                                    WHEN TIMESTAMPDIFF(HOUR, created_at, NOW()) = 1 THEN '1 hour ago'
123                                    ELSE CONCAT(TIMESTAMPDIFF(HOUR, created_at, NOW()), ' hours ago')
124                                END
125                            WHEN TIMESTAMPDIFF(DAY, created_at, NOW()) < 7 THEN
126                                CASE
127                                    WHEN TIMESTAMPDIFF(DAY, created_at, NOW()) = 1 THEN '1 day ago'
128                                    ELSE CONCAT(TIMESTAMPDIFF(DAY, created_at, NOW()), ' days ago')
129                                END
130                            WHEN TIMESTAMPDIFF(WEEK, created_at, NOW()) < 4 THEN
131                                CASE
132                                    WHEN TIMESTAMPDIFF(WEEK, created_at, NOW()) = 1 THEN '1 week ago'
133                                    ELSE CONCAT(TIMESTAMPDIFF(WEEK, created_at, NOW()), ' weeks ago')
134                                END
135                            WHEN TIMESTAMPDIFF(MONTH, created_at, NOW()) < 12 THEN
136                                CASE
137                                    WHEN TIMESTAMPDIFF(MONTH, created_at, NOW()) = 1 THEN '1 month ago'
138                                    ELSE CONCAT(TIMESTAMPDIFF(MONTH, created_at, NOW()), ' months ago')
139                                END
140                            ELSE
141                                CASE
142                                    WHEN TIMESTAMPDIFF(YEAR, created_at, NOW()) = 1 THEN '1 year ago'
143                                    ELSE CONCAT(TIMESTAMPDIFF(YEAR, created_at, NOW()), ' years ago')
144                                END
145                        END AS formatted_created_at
146                    FROM
147                        tbl_notifications
148                    WHERE user_id = ?
149                    ORDER BY
150                        created_at DESC
151                    LIMIT ? OFFSET ?";
152
153            $result = DB::select($query, [$userId, $perPage, $offset]);
154
155            return response([
156                'message' => 'OK',
157                'data' => $result,
158                'total' => $totalOpenNotification,
159                '_pagination' => [
160                    'current_page' => $page,
161                    'per_page' => $perPage,
162                    'has_more' => count($result) === $perPage,
163                ],
164            ]);
165
166        } catch (\Exception $e) {
167            report(AppException::fromException($e, 'LIST_NOTIFICATIONS_EXCEPTION'));
168
169            return response(['message' => 'KO', 'error' => $e->getMessage()]);
170        }
171
172    }
173
174    public function update_notifications(Request $request, $notificationId)
175    {
176
177        try {
178
179            $data = $request->all();
180
181            if ($notificationId == 999999999) {
182                TblNotifications::where('user_id', $data['user_id'])->update([
183                    'is_open' => null,
184                ]);
185            } else {
186                TblNotifications::where('notification_id', $notificationId)->update([
187                    'is_open' => null,
188                    'updated_at' => date('Y-m-d H:i:s'),
189                    'updated_by' => $data['updated_by'],
190                ]);
191            }
192
193            $r = new Request([
194                'user_id' => $data['user_id'],
195            ]);
196
197            return $this->list_notifications($r);
198
199        } catch (\Exception $e) {
200            report(AppException::fromException($e, 'UPDATE_NOTIFICATION_EXCEPTION'));
201
202            return response(['message' => 'KO', 'error' => $e->getMessage()]);
203        }
204    }
205
206    public function send_follow_up_notification($userId = null): ResponseFactory|Response
207    {
208
209        try {
210
211            if ($userId != null) {
212                $this->userId = $userId;
213                $this->locale = 'es';
214                App::setLocale($this->locale);
215            }
216
217            $users = TblUsers::get();
218            $totalOrders = 0;
219            $totalCompanies = 0;
220            foreach ($users as $user) {
221                // FIRE-1113: $body, $allTotalFollowUps, $companyCcIds must reset per user.
222                // Pre-fix they were declared above the loop and only appended to (`.=`),
223                // so user N's email contained the concatenated content + running totals
224                // of users 1..N-1 — a leak of every commercial's pipeline to the next.
225                $body = '';
226                $allTotalFollowUps = 0;
227                $userCompanies = TblCompanyUsers::where('user_id', $user->id)->get();
228
229                $body .= __('language.send_follow_up_notification.body_hello');
230                $body = str_replace('{{username}}', $user->name, $body);
231
232                $body .= __('language.send_follow_up_notification.body_message');
233
234                $hasFollowUp = false;
235                $companyCcIds = [];
236                foreach ($userCompanies as $userCompany) {
237
238                    $companyId = $userCompany->company_id;
239                    $company = TblCompanies::where('company_id', $companyId)->first();
240
241                    if ($company->is_send_follow_up > 0) {
242                        $toEmail = $user->email;
243
244                        $query = "SELECT 
245                                    id,
246                                    quote_id,
247                                    amount,
248                                    DATE_FORMAT(last_follow_up_date, '%d/%m/%Y') last_follow_up_date,
249                                    commercial 
250                                FROM 
251                                    tbl_quotations 
252                                WHERE 
253                                    last_follow_up_date < NOW() 
254                                    AND email IS NOT NULL 
255                                    AND email <> ''
256                                    AND NOT EXISTS (
257                                    SELECT 
258                                        1 
259                                    FROM 
260                                        tbl_blocked_domains bd 
261                                    WHERE 
262                                        email LIKE CONCAT('%', bd.domain, '%') 
263                                        AND bd.company_id = company_id
264                                    )
265                                    AND last_follow_up_date IS NOT NULL
266                                    AND reason_for_not_following_up_id IS NULL
267                                    AND last_follow_up_date > 0
268                                    AND last_follow_up_date < NOW()
269                                    AND total_sent < 3
270                                    AND for_add = 0
271                                    AND company_id = {$companyId}
272                                    AND commercial IS NOT NULL 
273                                    AND commercial = '{$user->name}'
274                                    AND budget_status_id = 2
275                                ORDER BY last_follow_up_date ASC, CAST(amount AS DOUBLE) DESC";
276
277                        $result = DB::select($query);
278
279                        $totalFolloUps = count($result);
280
281                        if ($totalFolloUps > 0) {
282                            $allTotalFollowUps = $allTotalFollowUps + $totalFolloUps;
283                            array_push($companyCcIds, $companyId);
284
285                            $hasFollowUp = true;
286                            $body .= __('language.send_follow_up_notification.body_company_message');
287                            $body = str_replace('{{total}}', $totalFolloUps, $body);
288                            $body = str_replace('{{company}}', $company->name, $body);
289
290                            $clickhRef = config('app.frontend_url')."orders?commercial={$user->name}&status=Enviado&last_follow_up_date=1&company_id={$companyId}";
291                            $body = str_replace('{{click}}', $clickhRef, $body);
292
293                            $ul = '<ul>';
294
295                            $list = '';
296                            $totalAmount = 0;
297                            for ($i = 0; $i < $totalFolloUps; $i++) {
298                                $li = __('language.send_follow_up_notification.body_due');
299
300                                $id = $result[$i]->id;
301                                $date = $result[$i]->last_follow_up_date;
302                                $quoteId = $result[$i]->quote_id;
303                                $totalAmount = $totalAmount + $result[$i]->amount;
304                                $amount = $this->currency($result[$i]->amount, 1);
305
306                                $url = config('app.frontend_url')."orders/{$id}?company_id={$companyId}";
307                                $href = "<a href='{$url}'>{$quoteId}</a>";
308
309                                $li = str_replace('{{quote_id}}', $href, $li);
310                                $li = str_replace('{{amount}}', $amount, $li);
311                                $list .= str_replace('{{date}}', $date, $li);
312                            }
313                            $ul .= $list;
314                            $ul .= '</ul>';
315
316                            $totalAmount = $this->currency($totalAmount, 1);
317                            $body = str_replace('{{amount}}', $totalAmount, $body);
318
319                            $content = $body.$ul;
320
321                            $body .= $ul;
322
323                        } else {
324                            continue;
325                        }
326                    } else {
327                        continue;
328                    }
329                }
330
331                if ($hasFollowUp) {
332
333                    $body = str_replace('{{totalOrders}}', $allTotalFollowUps, $body);
334                    $body = str_replace('{{totalCompanies}}', count($companyCcIds), $body);
335
336                    $clickhRef = config('app.frontend_url')."orders?commercial={$user->name}&status=Enviado&last_follow_up_date=1&company_id=0";
337                    $body = str_replace('{{clickAll}}', $clickhRef, $body);
338
339                    $imgpathS = file_get_contents(public_path('sendfollowups.png'));
340                    $img = "<br><img src='cid:sendfollowups' />";
341                    $body .= $img;
342
343                    $imgpath = file_get_contents(public_path('fireservicetitan.png'));
344                    $body .= '<br><p>Fire Service Titan</p>';
345                    $body .= "<img src='cid:fireservicetitan' style='height: 45px;' />";
346
347                    $html = '<!DOCTYPE html>';
348                    $html .= '<html>';
349                    $html .= '<head>';
350                    $html .= '<meta charset="UTF-8">';
351                    $html .= '<meta name="viewport" content="width=device-width, initial-scale=1.0">';
352                    $html .= '</head>';
353                    $html .= '<body>';
354                    $html .= $body;
355                    $html .= '</body>';
356                    $html .= '</html>';
357
358                    $dateNow = date_create(date('Y-m-d'));
359                    $dateNow = date_format($dateNow, 'd/m/Y');
360
361                    $subject = __('language.send_follow_up_notification.subject');
362                    $subject = str_replace('{{dateNow}}', $dateNow, $subject);
363
364                    if ($toEmail != null) {
365                        $userAdmin = TblUsers::where('id', $this->userId)->first();
366                        $sentBy = $userAdmin?->name ?? 'System';
367
368                        $cc = [];
369                        if (config('services.sendgrid.staging')) {
370                            $toEmail = $userAdmin?->email ?? $toEmail;
371                            // In staging we don't CC anyone — redirect every
372                            // send to the admin running the cron.
373                        } else {
374                            $companyCcIds = array_values(array_unique($companyCcIds));
375                            $ccBcc = TblCcBccNotifications::whereIn('company_id', $companyCcIds)->get();
376                            $inCc = [];
377
378                            foreach ($ccBcc as $data) {
379                                if ($data->email != $toEmail && ! in_array($data->email, $inCc)) {
380                                    $cc[] = $data->email;
381                                    $inCc[] = $data->email;
382                                }
383                            }
384                        }
385
386                        // FIRE-1147: synchronous side effects (TblNotifications
387                        // in-app banner + TblNotificationLogs audit row) land
388                        // immediately; the email send is queued so a slow or
389                        // failing SendGrid doesn't bottleneck the cron's
390                        // per-user loop.
391                        TblNotifications::create(
392                            [
393                                'user_id' => $user->id,
394                                'content' => $content,
395                                'is_open' => 1,
396                                'created_by' => 'System',
397                            ]
398                        );
399
400                        TblNotificationLogs::create(
401                            [
402                                'commercial' => $user->name,
403                                'total_follow_ups' => $allTotalFollowUps,
404                                'created_by' => $sentBy,
405                            ]
406                        );
407
408                        SendFollowUpDigest::dispatch(
409                            toEmail: (string) $toEmail,
410                            subject: $subject,
411                            html: $html,
412                            cc: $cc,
413                            userId: $user->id,
414                        )->onQueue('email');
415
416                        $body = '';
417                        $content = '';
418                    }
419                }
420
421                $hasFollowUp = false;
422                $body = '';
423                $content = '';
424                $allTotalFollowUps = 0;
425            }
426
427            return response([
428                'message' => 'OK',
429            ]);
430
431        } catch (\Exception $e) {
432            report(AppException::fromException($e, 'SEND_FOLLOW_UP_NOTIFICATION_EXCEPTION'));
433
434            return response(['message' => 'KO', 'error' => $e->getMessage()]);
435        }
436    }
437
438    public function send_request($companyId): ResponseFactory|Response
439    {
440
441        try {
442
443            $companyId = addslashes((string) $companyId);
444
445            $user = ['commercial'];
446
447            foreach ($user as $col) {
448                // $col is just 'commercial', but send_request_notification takes a $userId which isn't used as such.
449                // Assuming it was intended to only notify all users when called via CLI command.
450                $this->send_request_notification(); // Fixed method call
451            }
452
453            return response([
454                'message' => 'OK',
455            ]);
456
457        } catch (\Exception $e) {
458            report(AppException::fromException($e, 'SEND_REQUEST_EXCEPTION'));
459
460            return response(['message' => 'KO', 'error' => $e->getMessage()]);
461        }
462
463    }
464
465    public function send_g3w_warning($companyId): ResponseFactory|Response
466    {
467
468        try {
469
470            $companyId = addslashes((string) $companyId);
471
472            $userAdmin = TblUsers::where('id', $this->userId)->first();
473
474            if (config('services.sendgrid.staging')) {
475                Artisan::call('send:g3w-email-reminders', [
476                    'sent_by' => $userAdmin->name,
477                    'email' => $userAdmin->email,
478                    'company_id' => $companyId,
479                ]);
480            } else {
481                Artisan::call('send:g3w-email-reminders', [
482                    'sent_by' => $userAdmin->name,
483                    'email' => null,
484                    'company_id' => $companyId,
485                ]);
486            }
487
488            $output = Artisan::output();
489
490            return response([
491                'message' => 'OK',
492            ]);
493
494        } catch (\Exception $e) {
495            report(AppException::fromException($e, 'SEND_G3W_WARNING_EXCEPTION'));
496
497            return response(['message' => 'KO', 'error' => $e->getMessage()]);
498        }
499
500    }
501
502    public function send_request_notification($userId = null): ResponseFactory|Response
503    {
504
505        try {
506
507            if ($userId != null) {
508                $this->userId = $userId;
509                $this->locale = 'es';
510                App::setLocale($this->locale);
511            }
512
513            $users = TblUsers::get();
514
515            foreach ($users as $user) {
516                // FIRE-1113: $body, $allTotalRequest, $companyCcIds must reset per user.
517                // Pre-fix they were declared above the loop and only appended to (`.=`),
518                // so user N's email contained the concatenated content + running totals
519                // of users 1..N-1 — a leak of every commercial's pipeline to the next.
520                $body = '';
521                $allTotalRequest = 0;
522                $userCompanies = TblCompanyUsers::where('user_id', $user->id)->get();
523
524                $body .= __('language.send_request_notification.body_hello');
525                $body = str_replace('{{username}}', $user->name, $body);
526                $body .= __('language.send_request_notification.body_intro');
527
528                $hasRequest = false;
529                $companyCcIds = [];
530                foreach ($userCompanies as $userCompany) {
531
532                    $companyId = $userCompany->company_id;
533                    $company = TblCompanies::where('company_id', $companyId)->first();
534
535                    if ($company->is_send_request > 0) {
536                        $toEmail = $user->email;
537
538                        $query = "SELECT 
539                                    id,
540                                    quote_id,
541                                    DATE_FORMAT(created_at, '%d/%m/%Y') created_at,
542                                    commercial,
543                                    created_by
544                                FROM 
545                                    tbl_quotations 
546                                WHERE 
547                                    company_id = {$companyId}
548                                    AND budget_status_id IN (6)
549                                    AND commercial = '{$user->name}'";
550
551                        $result = DB::select($query);
552
553                        $totalRequest = count($result);
554
555                        if ($totalRequest > 0) {
556                            $allTotalRequest = $allTotalRequest + $totalRequest;
557                            $hasRequest = true;
558                            array_push($companyCcIds, $companyId);
559
560                            $body .= __('language.send_request_notification.body_message_commercial');
561                            $body = str_replace('{{total}}', $totalRequest, $body);
562                            $body = str_replace('{{company}}', $company->name, $body);
563
564                            $clickhRef = config('app.frontend_url')."orders?commercial={$user->name}&status=Solicitud&company_id={$companyId}";
565                            $body = str_replace('{{click}}', $clickhRef, $body);
566
567                            $ul = '<ul>';
568
569                            $list = '';
570                            $totalAmount = 0;
571                            for ($i = 0; $i < $totalRequest; $i++) {
572                                $li = __('language.send_request_notification.body_due');
573
574                                $id = $result[$i]->id;
575                                $date = $result[$i]->created_at;
576                                $quoteId = $result[$i]->quote_id;
577
578                                $url = config('app.frontend_url')."orders/{$id}?company_id={$companyId}";
579                                $href = "<a href='{$url}'>{$quoteId}</a>";
580
581                                $li = str_replace('{{quote_id}}', $href, $li);
582                                $list .= str_replace('{{date}}', $date, $li);
583                            }
584                            $ul .= $list;
585                            $ul .= '</ul>';
586
587                            $totalAmount = $this->currency($totalAmount, 1);
588                            $body = str_replace('{{amount}}', $totalAmount, $body);
589
590                            $content = $body.$ul;
591
592                            $body .= $ul;
593
594                        } else {
595                            continue;
596                        }
597                    } else {
598                        continue;
599                    }
600                }
601
602                if ($hasRequest) {
603
604                    $body = str_replace('{{totalOrders}}', $allTotalRequest, $body);
605                    $body = str_replace('{{totalCompanies}}', count($companyCcIds), $body);
606
607                    $clickhRef = config('app.frontend_url')."orders?commercial={$user->name}&status=Solicitud&company_id=0";
608                    $body = str_replace('{{clickAll}}', $clickhRef, $body);
609
610                    $imgpath = file_get_contents(public_path('fireservicetitan.png'));
611
612                    $body .= '<br><p>Fire Service Titan</p>';
613                    $body .= "<img src='cid:fireservicetitan' style='height: 45px;' />";
614
615                    $html = '<!DOCTYPE html>';
616                    $html .= '<html>';
617                    $html .= '<head>';
618                    $html .= '<meta charset="UTF-8">';
619                    $html .= '<meta name="viewport" content="width=device-width, initial-scale=1.0">';
620                    $html .= '</head>';
621                    $html .= '<body>';
622                    $html .= $body;
623                    $html .= '</body>';
624                    $html .= '</html>';
625
626                    $dateNow = date_create(date('Y-m-d'));
627                    $dateNow = date_format($dateNow, 'd/m/Y');
628
629                    $subject = __('language.send_request_notification.subject');
630                    $subject = str_replace('{{dateNow}}', $dateNow, $subject);
631                    $subject = str_replace('{{total}}', $allTotalRequest, $subject);
632
633                    if ($toEmail != null) {
634                        $email = new Mail;
635                        $sentBy = 'System';
636
637                        $email->setFrom('fire@fire.es', 'Fire Service Titan');
638                        $email->setSubject($subject);
639
640                        $userAdmin = TblUsers::where('id', $this->userId)->first();
641                        $sentBy = $userAdmin->name;
642
643                        if (config('services.sendgrid.staging')) {
644                            $toEmail = $userAdmin->email;
645                            $sentBy = $userAdmin->name;
646                            $user->id = $this->userId;
647                        } else {
648                            $companyCcIds = array_values(array_unique($companyCcIds));
649                            $ccBcc = TblCcBccRequestNotifications::whereIn('company_id', $companyCcIds)->get();
650
651                            $inCc = [];
652
653                            if (count($ccBcc) > 0) {
654                                foreach ($ccBcc as $data) {
655                                    if ($data->email != $toEmail && ! in_array($data->email, $inCc)) {
656                                        $email->addCc($data->email);
657                                        array_push($inCc, $data->email);
658                                    }
659                                }
660                            }
661                        }
662
663                        $email->addTo($toEmail);
664                        $email->addContent('text/html', $html);
665
666                        $email->addAttachment(
667                            $imgpath,
668                            'image/png',
669                            'fireservicetitan.png',
670                            'inline',
671                            'fireservicetitan'
672                        );
673
674                        $sendgrid = new \SendGrid(config('services.sendgrid.api_key'));
675
676                        try {
677                            $response = $sendgrid->send($email);
678                            SendgridLogger::log($email, $response);
679                        } catch (\Throwable $sendException) {
680                            SendgridLogger::logException($email, $sendException);
681                            throw $sendException;
682                        }
683
684                        if ($response->statusCode() == 202) {
685                            Log::channel('email_log')->info('ID:'.$toEmail.' - REQUEST EMAIL NOTIFICATION SENT');
686
687                            TblNotifications::create(
688                                [
689                                    'user_id' => $user->id,
690                                    'content' => $content,
691                                    'is_open' => 1,
692                                    'created_by' => 'System',
693                                ]
694                            );
695
696                            TblNotificationLogs::create(
697                                [
698                                    'company_id' => $companyId,
699                                    'commercial' => $user->name,
700                                    'total_request' => $allTotalRequest,
701                                    'created_by' => $sentBy,
702                                ]
703                            );
704
705                        } else {
706                            $error = true;
707                            Log::channel('email_log')->error('ID:'.$toEmail.' - '.$response->body());
708                        }
709
710                        $body = '';
711                        $content = '';
712                    }
713                }
714
715                $hasRequest = false;
716                $body = '';
717                $content = '';
718                $allTotalRequest = 0;
719            }
720
721            return response([
722                'message' => 'OK',
723            ]);
724
725        } catch (\Exception $e) {
726            report(AppException::fromException($e, 'SEND_REQUEST_EXCEPTION'));
727
728            return response(['message' => 'KO', 'error' => $e->getMessage()]);
729        }
730    }
731
732    public function get_cc_bcc($id): ResponseFactory|Response
733    {
734
735        try {
736
737            $companyId = addslashes((string) $id);
738
739            $result = TblCcBccNotifications::where('company_id', $companyId)->get();
740
741            return response(['message' => 'OK', 'data' => $result]);
742
743        } catch (\Exception $e) {
744            report(AppException::fromException($e, 'GET_CC_BCC_EXCEPTION'));
745
746            return response(['message' => 'KO', 'error' => $e->getMessage()]);
747        }
748    }
749
750    public function delete_cc_bcc($id): ResponseFactory|Response
751    {
752
753        try {
754
755            $id = addslashes((string) $id);
756
757            TblCcBccNotifications::where('id', $id)->delete();
758
759            return response(['message' => 'OK']);
760
761        } catch (\Exception $e) {
762            report(AppException::fromException($e, 'DELETE_CC_BCC_EXCEPTION'));
763
764            return response(['message' => 'KO', 'error' => $e->getMessage()]);
765        }
766    }
767
768    public function update_cc_bcc(Request $request, $id): ResponseFactory|Response
769    {
770
771        try {
772
773            $data = $request->all();
774            $id = addslashes((string) $id);
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                    $data['updated_at'] = date('Y-m-d H:i:s');
785                    TblCcBccNotifications::where('id', $id)->update($data);
786                }
787            } else {
788                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
789            }
790
791            return response(['message' => 'OK']);
792
793        } catch (\Exception $e) {
794            report(AppException::fromException($e, 'UPDATE_CC_BCC_EXCEPTION'));
795
796            return response(['message' => 'KO', 'error' => $e->getMessage()]);
797        }
798    }
799
800    public function create_cc_bcc(Request $request): ResponseFactory|Response
801    {
802
803        try {
804
805            $data = $request->all();
806
807            $x = $this->isEmailValid($data['email']);
808
809            if ($x) {
810                $result = TblCcBccNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
811
812                if ($result > 0) {
813                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
814                } else {
815                    TblCcBccNotifications::create($data);
816                }
817            } else {
818                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
819            }
820
821            return response(['message' => 'OK']);
822
823        } catch (\Exception $e) {
824            report(AppException::fromException($e, 'CREATE_CC_BCC_EXCEPTION'));
825
826            return response(['message' => 'KO', 'error' => $e->getMessage()]);
827        }
828    }
829
830    public function isEmailValid($email): bool
831    {
832        // Regular expression pattern for email validation
833        $pattern = '/^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$/';
834
835        // Check if the email matches the pattern
836        if (preg_match($pattern, (string) $email)) {
837            return true; // Valid email
838        } else {
839            return false; // Invalid email
840        }
841    }
842
843    public function delete_notifications($userId)
844    {
845
846        try {
847
848            $userId = addslashes((string) $userId);
849
850            TblNotifications::where('user_id', $userId)->delete();
851
852            $r = new Request([
853                'user_id' => $userId,
854            ]);
855
856            return $this->list_notifications($r);
857
858        } catch (\Exception $e) {
859            report(AppException::fromException($e, 'DELETE_NOTIFICATIONS_EXCEPTION'));
860
861            return response(['message' => 'KO', 'error' => $e->getMessage()]);
862        }
863
864    }
865
866    public function list_notification_logs(Request $request, $companyId): ResponseFactory|Response
867    {
868
869        try {
870            // FIRE-1149: paginated rewrite. Was a full scan of all 25k+
871            // rows on every page load. Now uses idx_notiflogs_company_created
872            // for an index-only scan of the requested page.
873            $companyId = (int) $companyId;
874            $perPage = min(100, max(1, (int) $request->input('per_page', 50)));
875            $page = max(1, (int) $request->input('page', 1));
876            $offset = ($page - 1) * $perPage;
877
878            if ($companyId !== 0) {
879                $where = ' (company_id = ? OR company_id IS NULL) ';
880                $bindings = [$companyId, $perPage, $offset];
881            } else {
882                // Multi-company case: $this->companyId is a CSV of ints set
883                // in the constructor from ResolveUserCompanies middleware,
884                // safe to interpolate (only used through ResultCache::remember
885                // and friends elsewhere — same trust model).
886                // Defensive: if the user has no companies assigned, fall back
887                // to "company_id IS NULL only" (the IN () form is illegal SQL).
888                $companyIdsList = trim((string) $this->companyId);
889                $where = $companyIdsList !== ''
890                    ? " (company_id IN ({$companyIdsList}) OR company_id IS NULL) "
891                    : ' company_id IS NULL ';
892                $bindings = [$perPage, $offset];
893            }
894
895            $query = "SELECT
896                        id,
897                        commercial,
898                        total_follow_ups,
899                        total_request,
900                        total_executive_report,
901                        total_g3w_warning,
902                        CASE
903                            WHEN total_follow_ups IS NOT NULL THEN total_follow_ups
904                            WHEN total_request IS NOT NULL THEN total_request
905                            WHEN total_executive_report IS NOT NULL THEN total_executive_report
906                            WHEN total_g3w_warning IS NOT NULL THEN total_g3w_warning
907                        END totals,
908                        created_by,
909                        created_at
910                    FROM `tbl_notification_logs`
911                    WHERE {$where}
912                    ORDER BY created_at DESC
913                    LIMIT ? OFFSET ?";
914
915            $result = DB::select($query, $bindings);
916
917            return response([
918                'message' => 'OK',
919                'data' => $result,
920                '_pagination' => [
921                    'current_page' => $page,
922                    'per_page' => $perPage,
923                    'has_more' => count($result) === $perPage,
924                ],
925            ]);
926
927        } catch (\Exception $e) {
928            report(AppException::fromException($e, 'LIST_NOTIFICATION_LOGS_EXCEPTION'));
929
930            return response(['message' => 'KO', 'error' => $e->getMessage()]);
931        }
932
933    }
934
935    public function delete_notification_logs($companyId): ResponseFactory|Response
936    {
937
938        try {
939
940            $companyId = addslashes((string) $companyId);
941
942            TblNotificationLogs::where('company_id', $companyId)->orWhere('company_id', null)->delete();
943
944            return response(['message' => 'OK']);
945
946        } catch (\Exception $e) {
947            report(AppException::fromException($e, 'DELETE_NOTIFICATION_LOGS_EXCEPTION'));
948
949            return response(['message' => 'KO', 'error' => $e->getMessage()]);
950        }
951    }
952
953    public function get_cc_g3w($id): ResponseFactory|Response
954    {
955
956        try {
957
958            $companyId = addslashes((string) $id);
959
960            $result = TblCcG3WEmailReminders::where('company_id', $companyId)->get();
961
962            return response(['message' => 'OK', 'data' => $result]);
963
964        } catch (\Exception $e) {
965            report(AppException::fromException($e, 'GET_CC_G3W_EXCEPTION'));
966
967            return response(['message' => 'KO', 'error' => $e->getMessage()]);
968        }
969    }
970
971    public function delete_cc_g3w($id): ResponseFactory|Response
972    {
973
974        try {
975
976            $id = addslashes((string) $id);
977
978            TblCcG3WEmailReminders::where('id', $id)->delete();
979
980            return response(['message' => 'OK']);
981
982        } catch (\Exception $e) {
983            report(AppException::fromException($e, 'DELETE_CC_G3W_EXCEPTION'));
984
985            return response(['message' => 'KO', 'error' => $e->getMessage()]);
986        }
987    }
988
989    public function update_cc_g3w(Request $request, $id): ResponseFactory|Response
990    {
991
992        try {
993
994            $data = $request->all();
995            $id = addslashes((string) $id);
996
997            $x = $this->isEmailValid($data['email']);
998
999            if ($x) {
1000                $result = TblCcG3WEmailReminders::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
1001
1002                if ($result > 0) {
1003                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
1004                } else {
1005                    $data['updated_at'] = date('Y-m-d H:i:s');
1006                    TblCcG3WEmailReminders::where('id', $id)->update($data);
1007                }
1008            } else {
1009                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
1010            }
1011
1012            return response(['message' => 'OK']);
1013
1014        } catch (\Exception $e) {
1015            report(AppException::fromException($e, 'UPDATE_CC_G3W_EXCEPTION'));
1016
1017            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1018        }
1019    }
1020
1021    public function create_cc_g3w(Request $request): ResponseFactory|Response
1022    {
1023
1024        try {
1025
1026            $data = $request->all();
1027
1028            $x = $this->isEmailValid($data['email']);
1029
1030            if ($x) {
1031                $result = TblCcG3WEmailReminders::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
1032
1033                if ($result > 0) {
1034                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
1035                } else {
1036                    TblCcG3WEmailReminders::create($data);
1037                }
1038            } else {
1039                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
1040            }
1041
1042            return response(['message' => 'OK']);
1043
1044        } catch (\Exception $e) {
1045            report(AppException::fromException($e, 'CREATE_CC_G3W_EXCEPTION'));
1046
1047            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1048        }
1049    }
1050
1051    public function get_cc_bcc_request($id): ResponseFactory|Response
1052    {
1053
1054        try {
1055
1056            $companyId = addslashes((string) $id);
1057
1058            $result = TblCcBccRequestNotifications::where('company_id', $companyId)->get();
1059
1060            return response(['message' => 'OK', 'data' => $result]);
1061
1062        } catch (\Exception $e) {
1063            report(AppException::fromException($e, 'GET_CC_BCC_REQUEST_EXCEPTION'));
1064
1065            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1066        }
1067    }
1068
1069    public function delete_cc_bcc_request($id): ResponseFactory|Response
1070    {
1071
1072        try {
1073
1074            $id = addslashes((string) $id);
1075
1076            TblCcBccRequestNotifications::where('id', $id)->delete();
1077
1078            return response(['message' => 'OK']);
1079
1080        } catch (\Exception $e) {
1081            report(AppException::fromException($e, 'DELETE_CC_BCC_REQUEST_EXCEPTION'));
1082
1083            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1084        }
1085    }
1086
1087    public function update_cc_bcc_request(Request $request, $id): ResponseFactory|Response
1088    {
1089
1090        try {
1091
1092            $data = $request->all();
1093            $id = addslashes((string) $id);
1094
1095            $x = $this->isEmailValid($data['email']);
1096
1097            if ($x) {
1098                $result = TblCcBccRequestNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
1099
1100                if ($result > 0) {
1101                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
1102                } else {
1103                    $data['updated_at'] = date('Y-m-d H:i:s');
1104                    TblCcBccRequestNotifications::where('id', $id)->update($data);
1105                }
1106            } else {
1107                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
1108            }
1109
1110            return response(['message' => 'OK']);
1111
1112        } catch (\Exception $e) {
1113            report(AppException::fromException($e, 'UPDATE_CC_BCC_REQUEST_EXCEPTION'));
1114
1115            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1116        }
1117    }
1118
1119    public function create_cc_bcc_request(Request $request): ResponseFactory|Response
1120    {
1121
1122        try {
1123
1124            $data = $request->all();
1125
1126            $x = $this->isEmailValid($data['email']);
1127
1128            if ($x) {
1129                $result = TblCcBccRequestNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
1130
1131                if ($result > 0) {
1132                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
1133                } else {
1134                    TblCcBccRequestNotifications::create($data);
1135                }
1136            } else {
1137                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
1138            }
1139
1140            return response(['message' => 'OK']);
1141
1142        } catch (\Exception $e) {
1143            report(AppException::fromException($e, 'CREATE_CC_BCC_REQUEST_EXCEPTION'));
1144
1145            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1146        }
1147    }
1148
1149    public function send_executive(Request $request): ResponseFactory|Response
1150    {
1151
1152        try {
1153
1154            $data = $request->all();
1155
1156            $executiveEmails = TblCcBccExecutiveNotifications::get();
1157
1158            if (isset($data['User-ID'])) {
1159                $this->userId = $data['User-ID'];
1160            }
1161
1162            if (isset($data['self']) && $data['self'] == 1) {
1163                $user = TblUsers::where('id', $this->userId)->first();
1164                $this->send_executive_report($user->email, $user->name, $user->id, $data['processed_by']);
1165            } else {
1166                foreach ($executiveEmails as $item) {
1167                    $user = TblUsers::where('email', $item->email)->first();
1168
1169                    if ($user) {
1170                        $this->send_executive_report($item->email, $user->name, $user->id, $data['processed_by']);
1171
1172                        continue;
1173                    }
1174
1175                    // FIRE-1025: don't silently drop recipients whose email
1176                    // isn't in tbl_users. Berta added Duarte on 2026-03-30
1177                    // expecting him to receive the report; the previous
1178                    // `if ($user) { ... }` guard skipped him (and Albert and
1179                    // Pablo) on every Saturday with no log line. Fall back
1180                    // to a sensible display name + user_id = 1 (System) so
1181                    // the email goes out, and surface the missing-user
1182                    // case in the cron log so admins can clean up
1183                    // tbl_cc_bcc_executive_notifications later.
1184                    Log::channel('cron_send_executive_report')->warning(
1185                        "send:executive-report — recipient {$item->email} has no matching tbl_users row; sending anyway with default name and user_id=1 (System)."
1186                    );
1187                    $defaultName = strstr($item->email, '@', true) ?: $item->email;
1188                    $this->send_executive_report($item->email, $defaultName, 1, $data['processed_by']);
1189                }
1190            }
1191
1192            return response([
1193                'message' => 'OK',
1194            ]);
1195
1196        } catch (\Exception $e) {
1197            report(AppException::fromException($e, 'SEND_EXECUTIVE_EXCEPTION'));
1198
1199            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1200        }
1201
1202    }
1203
1204    public function send_executive_report($toEmail, $toName, $toUserId, $sentBy = 'System')
1205    {
1206
1207        try {
1208
1209            $result = TblNotificationSettings::where('id', 1)->first();
1210
1211            $currentYear = date('Y');
1212            $previousYear = date('Y', strtotime('-1 year', strtotime(date('Y-m-d'))));
1213
1214            if ($toEmail != null && $result->is_auto_send_executive == 1) {
1215
1216                if (config('services.sendgrid.staging')) {
1217                    $user = TblUsers::where('id', $this->userId)->first();
1218                    $toEmail = $user->email;
1219                }
1220
1221                $result = (object) $this->get_executive_reports();
1222
1223                $weekNumber = $result->weekNumber;
1224                $monday = $result->monday;
1225                $totalBudgets = number_format($result->totalBudgets, 0, ',', '.');
1226                $totalAmountApproved = $result->totalAmountApproved;
1227                $totalAmount = $result->totalAmount;
1228                $dividendMaintenance = $result->dividendMaintenance;
1229                $dividendCorrectives = $result->dividendCorrectives;
1230                $percentageMoreThanLastWeek = $result->percentageMoreThanLastWeek;
1231                $approvalRate = $result->approvalRate;
1232
1233                $subject = "FST executive report week $weekNumber (starting on monday - $monday) - Automated";
1234
1235                $body = "<p>Hello {$toName},</p>";
1236
1237                $url = config('app.frontend_url')."analytics?week={$weekNumber}";
1238                $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>";
1239                $body .= "<p><b>{$totalBudgets} budget(s)</b> ({$percentageMoreThanLastWeek}% compared to last week) created for a total of <b>{$totalAmount}</b> ({$dividendMaintenance}% “Mantenimiento”, {$dividendCorrectives}% “Correctivos”) - ";
1240                $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>";
1241
1242                $query = "SELECT 
1243                            a.company_id, 
1244                            a.name,
1245                            (
1246                            SELECT 
1247                                COUNT(created_at) 
1248                            FROM 
1249                                tbl_quotations 
1250                            WHERE 
1251                                company_id = a.company_id
1252                                AND for_add = 0 
1253                                AND created_at IS NOT NULL 
1254                                AND issue_date IS NOT NULL
1255                                AND YEAR(created_at) = YEAR(issue_date)
1256                            ) total 
1257                        FROM 
1258                            tbl_companies a 
1259                            WHERE is_send_executive = 1
1260                            -- AND company_id IN ({$this->companyId})
1261                        ORDER BY 
1262                            3 DESC
1263                        ";
1264
1265                $companies = DB::select($query);
1266
1267                $li = [];
1268
1269                foreach ($companies as $company) {
1270                    $result = (object) $this->get_executive_reports($company->company_id);
1271                    $result->name = $company->name;
1272                    array_push($li, $result);
1273                }
1274
1275                usort($li, fn ($a, $b) => $b->totalBudgets - $a->totalBudgets);
1276
1277                $body .= '<ul>';
1278                foreach ($li as $result) {
1279                    $totalBudgets = number_format($result->totalBudgets, 0, ',', '.');
1280                    $totalAmount = $result->totalAmount;
1281                    $dividendMaintenance = $result->dividendMaintenance;
1282                    $dividendCorrectives = $result->dividendCorrectives;
1283                    $percentageMoreThanLastWeek = $result->percentageMoreThanLastWeek;
1284                    $approvalRate = $result->approvalRate;
1285
1286                    $body .= "<li>{$totalBudgets} order(s) in “<b>{$result->name}</b>” ({$dividendMaintenance}% “Mantenimiento”, {$dividendCorrectives}% “Correctivos”) for total of <b>{$totalAmount}</b></li>";
1287                }
1288                $body .= '</ul>';
1289
1290                $result = (object) $this->get_executive_reports(null, true, $currentYear);
1291
1292                $totalBudgets = number_format($result->totalBudgets, 0, ',', '.');
1293                $totalAmountApproved = $result->totalAmountApproved;
1294                $totalAmount = $result->totalAmount;
1295                $dividendMaintenance = $result->dividendMaintenance;
1296                $dividendCorrectives = $result->dividendCorrectives;
1297                $percentageMoreThanLastWeek = $result->percentageMoreThanLastWeek;
1298                $approvalRate = $result->approvalRate;
1299                $totalDaysFromStartOfYear = date('z') + 1;
1300                $totalDaysOfCurrentYear = date('z', mktime(0, 0, 0, 12, 31, date('Y'))) + 1;
1301                $expected = 0;
1302
1303                $totalGoalForTheYear = 0;
1304                if ($result->totalGoalForTheYear > 0) {
1305                    $totalGoalForTheYear = number_format(($result->totalBudgets / $result->totalGoalForTheYear) * 100, 0, ',', '.');
1306                    $expected = number_format(($totalDaysFromStartOfYear / $totalDaysOfCurrentYear) * 100, 0, ',', '.');
1307                }
1308
1309                $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”) ";
1310                $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>";
1311
1312                $query = "SELECT 
1313                            a.company_id, 
1314                            a.name,
1315                            (
1316                            SELECT 
1317                                COUNT(created_at) 
1318                            FROM 
1319                                tbl_quotations 
1320                            WHERE 
1321                                company_id = a.company_id
1322                                AND for_add != 1
1323                                AND amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1324                                AND created_at IS NOT NULL 
1325                                AND YEAR(created_at) = {$currentYear}                                
1326                            ) total 
1327                        FROM 
1328                            tbl_companies a 
1329                            WHERE is_send_executive = 1
1330                            -- AND company_id IN ({$this->companyId})
1331                        ORDER BY 
1332                            3 DESC
1333                        ";
1334
1335                $companies = DB::select($query);
1336
1337                $li = [];
1338
1339                foreach ($companies as $company) {
1340                    $result = (object) $this->get_executive_reports($company->company_id, true, $currentYear);
1341                    $result->name = $company->name;
1342                    array_push($li, $result);
1343                }
1344
1345                usort($li, fn ($a, $b) => $b->totalBudgets - $a->totalBudgets);
1346
1347                $body .= '<ul>';
1348                foreach ($li as $result) {
1349
1350                    $totalBudgets = number_format($result->totalBudgets, 0, ',', '.');
1351                    $totalAmountApproved = $result->totalAmountApproved;
1352                    $totalAmount = $result->totalAmount;
1353                    $dividendMaintenance = $result->dividendMaintenance;
1354                    $dividendCorrectives = $result->dividendCorrectives;
1355                    $percentageMoreThanLastWeek = $result->percentageMoreThanLastWeek;
1356                    $approvalRate = $result->approvalRate;
1357                    $goalForTheYear = number_format($result->totalGoalForTheYear, 0, ',', '.');
1358                    $totalDaysFromStartOfYear = date('z') + 1;
1359                    $totalDaysOfCurrentYear = date('z', mktime(0, 0, 0, 12, 31, date('Y'))) + 1;
1360                    $expected = 0;
1361
1362                    $totalGoalForTheYear = 0;
1363                    $style = "style='color: green'";
1364                    if ($result->totalGoalForTheYear > 0) {
1365                        $totalGoalForTheYear = number_format(($result->totalBudgets / $result->totalGoalForTheYear) * 100, 0, ',', '.');
1366                        $expected = number_format(($totalDaysFromStartOfYear / $totalDaysOfCurrentYear) * 100, 0, ',', '.');
1367
1368                        if ($totalGoalForTheYear < $expected) {
1369                            $style = "style='color: red'";
1370                        }
1371                    }
1372
1373                    $body .= "<li>{$totalBudgets} order(s) in “<b>{$result->name}</b>” ({$dividendMaintenance}% “Mantenimiento”, {$dividendCorrectives}% “Correctivos”) for total of <b>{$totalAmount}</b></li>";
1374                    $body .= '<ul>';
1375                    $body .= "<li><i><b>{$goalForTheYear} goal</b> for the year. <b {$style}>{$totalGoalForTheYear}%</b> accomplished to date vs <b>{$expected}% expected</b></i></li>";
1376                    $body .= "<li><i>Approval rate of <b>{$approvalRate}%</b> (<span style='color: blue;'>{$totalAmountApproved} approved</span>)</i></li>";
1377                    $body .= '</ul>';
1378                }
1379
1380                $body .= '</ul>';
1381
1382                $result = (object) $this->get_executive_reports(null, true, $previousYear);
1383
1384                $totalBudgets = number_format($result->totalBudgets, 0, ',', '.');
1385                $totalAmount = $result->totalAmount;
1386                $dividendMaintenance = $result->dividendMaintenance;
1387                $dividendCorrectives = $result->dividendCorrectives;
1388                $percentageMoreThanLastWeek = $result->percentageMoreThanLastWeek;
1389                $approvalRate = $result->approvalRate;
1390
1391                $body .= "<p>For your reference, in <b>{$previousYear}</b>, <b>{$totalBudgets} budgets for a total of {$totalAmount}</b> ({$dividendMaintenance}% “Mantenimientos”, {$dividendCorrectives}% “Correctivos”) ";
1392                $body .= "were created across all companies - <i>we have an approval rate of <b>{$approvalRate}%</b> for the year</i></p>";
1393
1394                $query = "SELECT 
1395                            a.company_id, 
1396                            a.name,
1397                            (
1398                            SELECT 
1399                                COUNT(created_at) 
1400                            FROM 
1401                                tbl_quotations 
1402                            WHERE 
1403                                company_id = a.company_id
1404                                AND for_add != 1
1405                                AND amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1406                                AND created_at IS NOT NULL 
1407                                AND YEAR(created_at) = {$currentYear}
1408                            ) total 
1409                        FROM 
1410                            tbl_companies a 
1411                            WHERE is_send_executive = 1
1412                            -- AND company_id IN ({$this->companyId})
1413                        ORDER BY 
1414                            3 DESC
1415                        ";
1416
1417                $companies = DB::select($query);
1418
1419                $li = [];
1420
1421                foreach ($companies as $company) {
1422                    $result = (object) $this->get_executive_reports($company->company_id, true, $previousYear);
1423                    $result->name = $company->name;
1424                    array_push($li, $result);
1425                }
1426
1427                usort($li, fn ($a, $b) => $b->totalBudgets - $a->totalBudgets);
1428
1429                $body .= '<ul>';
1430
1431                foreach ($li as $result) {
1432                    $totalBudgets = number_format($result->totalBudgets, 0, ',', '.');
1433                    $totalAmount = $result->totalAmount;
1434                    $dividendMaintenance = $result->dividendMaintenance;
1435                    $dividendCorrectives = $result->dividendCorrectives;
1436                    $percentageMoreThanLastWeek = $result->percentageMoreThanLastWeek;
1437                    $approvalRate = $result->approvalRate;
1438
1439                    $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>";
1440                }
1441
1442                $body .= '</ul><br><br>';
1443
1444                $imgpath = file_get_contents(public_path('fireservicetitan.png'));
1445
1446                $content = $body;
1447
1448                $body .= '<br><p>Fire Service Titan</p>';
1449                $body .= "<img src='cid:fireservicetitan' style='height: 45px;' />";
1450
1451                $html = '<!DOCTYPE html>';
1452                $html .= '<html>';
1453                $html .= '<head>';
1454                $html .= '<meta charset="UTF-8">';
1455                $html .= '<meta name="viewport" content="width=device-width, initial-scale=1.0">';
1456                $html .= '</head>';
1457                $html .= '<body>';
1458                $html .= $body;
1459                $html .= '</body>';
1460                $html .= '</html>';
1461
1462                $email = new Mail;
1463
1464                $email->setFrom('fire@fire.es', 'Fire Service Titan');
1465                $email->setSubject($subject);
1466
1467                $email->addTo($toEmail);
1468                $email->addContent('text/html', $html);
1469
1470                $email->addAttachment(
1471                    $imgpath,
1472                    'image/png',
1473                    'fireservicetitan.png',
1474                    'inline',
1475                    'fireservicetitan'
1476                );
1477
1478                $sendgrid = new \SendGrid(config('services.sendgrid.api_key'));
1479
1480                try {
1481                    $response = $sendgrid->send($email);
1482                    SendgridLogger::log($email, $response);
1483                } catch (\Throwable $sendException) {
1484                    SendgridLogger::logException($email, $sendException);
1485                    throw $sendException;
1486                }
1487
1488                if ($response->statusCode() == 202) {
1489                    Log::channel('email_log')->info('ID:'.$toEmail.' - EXECUTIVE REPORT EMAIL NOTIFICATION SENT');
1490
1491                    TblNotifications::create(
1492                        [
1493                            'user_id' => $toUserId,
1494                            'content' => $content,
1495                            'is_open' => 1,
1496                            'created_by' => 'System',
1497                        ]
1498                    );
1499
1500                    TblNotificationLogs::create(
1501                        [
1502                            'commercial' => $toName,
1503                            'total_executive_report' => 1,
1504                            'created_by' => $sentBy,
1505                        ]
1506                    );
1507                } else {
1508                    $error = true;
1509                    Log::channel('email_log')->error('ID:'.$toEmail.' - '.$response->body());
1510                }
1511
1512                $body = '';
1513
1514                return response([
1515                    'message' => 'OK',
1516                ]);
1517            }
1518
1519        } catch (\Exception $e) {
1520            report(AppException::fromException($e, 'SEND_EXECUTIVE_REPORT_EXCEPTION'));
1521
1522            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1523        }
1524
1525    }
1526
1527    public function get_executive_reports($companyId = null, $wholeYear = false, $year = null): array
1528    {
1529
1530        $where = '';
1531        $whereYear = '';
1532        $whereMonth = "DATE_FORMAT(q.created_at, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m') ";
1533
1534        if ($companyId != null) {
1535            $where = " AND company_id = {$companyId} ";
1536        }
1537
1538        $now = date('Y-m-d');
1539        $weekNumber = date('W', strtotime($now));
1540        $monday = date('F j, Y', strtotime($now.' - '.(date('N', strtotime($now)) - 1).' days'));
1541
1542        $mondayDate = date('Y-m-d', strtotime($now.' - '.(date('N', strtotime($now)) - 1).' days'));
1543        $sundayDate = date('Y-m-d', strtotime($mondayDate.' + 6 days'));
1544
1545        $mondayLastWeekDate = date('Y-m-d', strtotime($mondayDate.' - 1 week'));
1546        $sundayLastWeekDate = date('Y-m-d', strtotime($mondayLastWeekDate.' + 6 days'));
1547
1548        $whereAccMonth = '';
1549        if ($wholeYear == false) {
1550            $whereYear = " AND DATE_FORMAT(q.created_at, '%Y-%m-%d') BETWEEN '{$mondayDate}' AND '{$sundayDate}' ";
1551            $whereMonth = " AND DATE_FORMAT(q.created_at, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m') ";
1552            $whereAccMonth = " AND DATE_FORMAT(q.acceptance_date, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m') ";
1553        } else {
1554            $whereYear = " AND YEAR(q.created_at) = {$year} ";
1555            $whereMonth = " AND YEAR(q.created_at) = {$year} ";
1556            $whereAccMonth = " AND YEAR(q.acceptance_date) = {$year} ";
1557        }
1558
1559        $query = "SELECT 
1560                    COUNT(q.created_at) totalBudgets, 
1561                    SUM(q.amount) totalAmount, 
1562                    COALESCE(
1563                        SUM(CASE WHEN bt.budget_type_group_id = 3 THEN 1 END)
1564                        , 0
1565                    ) / 
1566                    COUNT(q.created_at) * 100 dividendMaintenance, 
1567                    COALESCE(
1568                        SUM(CASE WHEN bt.budget_type_group_id = 5 THEN 1 END)
1569                        , 0
1570                    ) / 
1571                    COUNT(q.created_at) * 100 dividendCorrectives 
1572                FROM 
1573                    tbl_quotations q 
1574                    LEFT JOIN tbl_budget_types bt ON q.budget_type_id = bt.budget_type_id 
1575                WHERE 
1576                    q.for_add != 1
1577                    AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1578                    AND q.created_at IS NOT NULL 
1579                    AND q.company_id IN (SELECT company_id FROM tbl_companies WHERE is_send_executive = 1 {$where})
1580                    AND q.budget_type_id != 7
1581                    AND YEAR(q.created_at) = YEAR(q.issue_date)
1582                    {$whereYear}
1583                    ";
1584
1585        $result = DB::select($query)[0];
1586
1587        $totalBudgets = $result->totalBudgets;
1588        $totalAmount = $this->currency($result->totalAmount, 1, 0);
1589        $totalAmountC = $result->totalAmount;
1590        $dividendMaintenance = number_format($result->dividendMaintenance, 0);
1591        $dividendCorrectives = number_format($result->dividendCorrectives, 0);
1592
1593        $query = "SELECT 
1594                        COUNT(q.created_at) totalBudgetLastWeek 
1595                    FROM tbl_quotations q WHERE 
1596                    DATE_FORMAT(q.created_at, '%Y-%m-%d') BETWEEN '{$mondayLastWeekDate}' 
1597                    AND '{$sundayLastWeekDate}' 
1598                    AND q.for_add != 1
1599                    AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1600                    AND q.created_at IS NOT NULL 
1601                    AND q.budget_type_id != 7
1602                    AND q.budget_type_id IS NOT NULL 
1603                    AND YEAR(q.created_at) = YEAR(q.issue_date)
1604                    AND q.company_id IN (SELECT company_id FROM tbl_companies WHERE is_send_executive = 1 {$where})";
1605
1606        $result = DB::select($query)[0];
1607
1608        $percentageMoreThanLastWeek = 0;
1609
1610        if ($result->totalBudgetLastWeek > 0) {
1611            $percentageMoreThanLastWeek = number_format($totalBudgets / $result->totalBudgetLastWeek * 100, 2);
1612        }
1613
1614        $query = "SELECT 
1615                    SUM(q.amount) totalAmount
1616                FROM tbl_quotations q WHERE 
1617                q.for_add != 1
1618                AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1619                AND q.created_at IS NOT NULL 
1620                AND q.budget_type_id != 7
1621                AND q.budget_type_id IS NOT NULL 
1622                AND YEAR(q.created_at) = YEAR(q.issue_date)
1623                AND q.company_id IN (SELECT company_id FROM tbl_companies WHERE is_send_executive = 1 {$where})
1624                {$whereMonth}";
1625
1626        $result = DB::select($query)[0];
1627
1628        $totalAmountCC = $result->totalAmount;
1629
1630        $query = "SELECT 
1631                COUNT(CASE WHEN q.created_at IS NOT NULL THEN 1 END) totalBudgets,
1632                COUNT(CASE WHEN q.acceptance_date IS NOT NULL AND q.budget_status_id = 3 THEN 1 END) totalAcceptance,
1633                SUM(CASE WHEN q.acceptance_date IS NOT NULL AND q.budget_status_id = 3 THEN q.amount END) totalAmountApproved
1634            FROM tbl_quotations q WHERE 
1635            q.for_add != 1
1636            AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1637            AND q.created_at IS NOT NULL 
1638            AND q.budget_type_id != 7
1639            AND q.budget_type_id IS NOT NULL 
1640            AND YEAR(q.created_at) = YEAR(q.issue_date)
1641            AND q.company_id IN (SELECT company_id FROM tbl_companies WHERE is_send_executive = 1 {$where})
1642            {$whereAccMonth}";
1643
1644        $result = DB::select($query)[0];
1645
1646        $approvalRate = 0;
1647        $totalAmountApproved = $result->totalAmountApproved;
1648        if ($totalAmountCC > 0) {
1649            $approvalRate = number_format(($totalAmountApproved / $totalAmountCC) * 100, 0);
1650        }
1651
1652        $query = "SELECT SUM(goal_for_the_year) goalForTheYear FROM tbl_companies WHERE is_send_executive = 1 {$where}";
1653        $result = DB::select($query)[0];
1654
1655        return [
1656            'weekNumber' => $weekNumber,
1657            'monday' => $monday,
1658            'totalBudgets' => $totalBudgets,
1659            'totalAmount' => $totalAmount,
1660            'totalAmountApproved' => $this->currency($totalAmountApproved, 1, 0),
1661            'dividendMaintenance' => $dividendMaintenance,
1662            'dividendCorrectives' => $dividendCorrectives,
1663            'percentageMoreThanLastWeek' => $percentageMoreThanLastWeek,
1664            'approvalRate' => $approvalRate,
1665            'totalGoalForTheYear' => $result->goalForTheYear,
1666        ];
1667
1668    }
1669
1670    public function currency($amount, $withEuro = null, $n = 2)
1671    {
1672
1673        if ($withEuro != null) {
1674            $withEuro = ' €';
1675        }
1676
1677        return number_format($amount, $n, ',', '.').$withEuro;
1678    }
1679
1680    // ---------------------------------------------------------------------
1681    // New weekly commercial performance report (redesigned executive email).
1682    //
1683    // Replaces the prose summary built by send_executive / send_executive_report
1684    // with two tabular parts:
1685    //   PART 1 — Regional performance (per region + Total, with a prior-year
1686    //            comparison): weekly #/€ and Preventive/Corrective/Installation
1687    //            splits, plus YTD progress against the per-region annual € target.
1688    //   PART 2 — Individual performance: top-3 by value and by volume, plus the
1689    //            below-weekly-target lists (by € value and by budget volume).
1690    //
1691    // The old send_executive* methods are intentionally left untouched so the
1692    // previous report stays callable. Service-type € is split by
1693    // tbl_budget_types.budget_type_group_id (3 = Preventive/Mantenimiento,
1694    // 5 = Corrective/Correctivos, 4 = Installation/Instalaciones), matching the
1695    // group ids used across the analytics controllers.
1696    // ---------------------------------------------------------------------
1697
1698    // Weekly per-person targets (spec: fixed thresholds).
1699    const WCR_TARGET_VALUE = 30000;   // € — "Sales reps" by value
1700
1701    const WCR_TARGET_VOLUME = 50;     // # budgets — "Sales Specialists" by volume
1702
1703    public function send_weekly_commercial_report(Request $request): ResponseFactory|Response
1704    {
1705        try {
1706            $data = $request->all();
1707
1708            $executiveEmails = TblCcBccExecutiveNotifications::get();
1709
1710            if (isset($data['User-ID'])) {
1711                $this->userId = $data['User-ID'];
1712            }
1713
1714            // For a "send to self" request the recipient is the authenticated
1715            // user, so take the id from the request header rather than a body
1716            // param. `backend-user-id` is set from the validated token by the
1717            // auth.token middleware (so it can't be spoofed); fall back to a
1718            // literal `User-ID` header, then the body param.
1719            if (isset($data['self']) && $data['self'] == 1) {
1720                $headerUserId = $request->header('backend-user-id') ?? $request->header('User-ID');
1721                if ($headerUserId !== null && $headerUserId !== '') {
1722                    $this->userId = $headerUserId;
1723                }
1724            }
1725
1726            $sentBy = $data['processed_by'] ?? 'System';
1727
1728            // Preview mode: render and return the report HTML directly (no email
1729            // dispatched). Use ?preview=1 — handy for inspecting the layout in
1730            // Postman or a browser.
1731            if (isset($data['preview']) && $data['preview'] == 1) {
1732                $toName = 'Preview';
1733                if ($this->userId) {
1734                    $previewUser = TblUsers::where('id', $this->userId)->first();
1735                    $toName = $previewUser->name ?? 'Preview';
1736                }
1737
1738                return $this->send_weekly_commercial_report_email(null, $toName, $this->userId ?: 1, $sentBy, true);
1739            }
1740
1741            if (isset($data['self']) && $data['self'] == 1) {
1742                $user = TblUsers::where('id', $this->userId)->first();
1743                $this->send_weekly_commercial_report_email($user->email, $user->name, $user->id, $sentBy);
1744            } else {
1745                foreach ($executiveEmails as $item) {
1746                    $user = TblUsers::where('email', $item->email)->first();
1747
1748                    if ($user) {
1749                        $this->send_weekly_commercial_report_email($item->email, $user->name, $user->id, $sentBy);
1750
1751                        continue;
1752                    }
1753
1754                    // Mirror FIRE-1025: don't silently drop recipients whose
1755                    // email isn't in tbl_users — send anyway with a default
1756                    // name and user_id = 1 (System), and log the gap.
1757                    Log::channel('cron_send_executive_report')->warning(
1758                        "send:executive-report (weekly commercial) — recipient {$item->email} has no matching tbl_users row; sending anyway with default name and user_id=1 (System)."
1759                    );
1760                    $defaultName = strstr($item->email, '@', true) ?: $item->email;
1761                    $this->send_weekly_commercial_report_email($item->email, $defaultName, 1, $sentBy);
1762                }
1763            }
1764
1765            return response(['message' => 'OK']);
1766
1767        } catch (\Exception $e) {
1768            report(AppException::fromException($e, 'SEND_WEEKLY_COMMERCIAL_REPORT_EXCEPTION'));
1769
1770            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1771        }
1772    }
1773
1774    public function send_weekly_commercial_report_email($toEmail, $toName, $toUserId, $sentBy = 'System', $preview = false)
1775    {
1776        try {
1777            $settings = TblNotificationSettings::where('id', 1)->first();
1778
1779            // Preview mode (used for testing) skips the auto-send gate and the
1780            // staging redirect, and returns the rendered HTML instead of
1781            // dispatching the email.
1782            if (! $preview && ($toEmail == null || $settings->is_auto_send_executive != 1)) {
1783                return response(['message' => 'OK']);
1784            }
1785
1786            // Staging safety: never email real recipients — redirect to the
1787            // logged-in user (same guard as send_executive_report).
1788            if (! $preview && config('services.sendgrid.staging')) {
1789                $user = TblUsers::where('id', $this->userId)->first();
1790                $toEmail = $user->email;
1791            }
1792
1793            $regional = $this->get_weekly_regional_performance();
1794            $individual = $this->get_weekly_individual_performance($regional['mondayDate'], $regional['sundayDate']);
1795
1796            $weekNumber = $regional['weekNumber'];
1797            $subject = "FST weekly commercial report — week {$weekNumber} (week of {$regional['mondayDate']}) - Automated";
1798
1799            $content = $this->build_weekly_commercial_report_html($regional, $individual);
1800
1801            $imgpath = file_get_contents(public_path('fireservicetitan.png'));
1802
1803            // Branded shell: Fire Service Titan red header band, white card on a
1804            // light grey canvas, footer. All inline styles for email clients.
1805            $brand = '#dc3545';
1806            $shell = "<div style='background:#f4f5f7;padding:24px 12px;font-family:Arial,Helvetica,sans-serif;'>";
1807            $shell .= "<div style='max-width:1000px;margin:0 auto;background:#ffffff;border:1px solid #e6e6e6;border-radius:8px;overflow:hidden;'>";
1808            $shell .= "<table width='100%' style='border-collapse:collapse;background:{$brand};'><tr>"
1809                ."<td style='padding:18px 24px;'>"
1810                ."<div style='color:#ffffff;font-size:20px;font-weight:bold;letter-spacing:.3px;'>Fire Service Titan</div>"
1811                ."<div style='color:#ffd9dd;font-size:13px;margin-top:3px;'>Weekly commercial performance &middot; Week {$weekNumber}</div>"
1812                .'</td>'
1813                ."<td style='padding:18px 24px;text-align:right;'>"
1814                ."<img src='cid:fireservicetitan' style='height:40px;' alt='Fire Service Titan' />"
1815                .'</td></tr></table>';
1816            $shell .= "<div style='padding:24px;color:#2b2b2b;'>";
1817            $shell .= "<p style='margin:0 0 4px;font-size:15px;'>Hello {$toName},</p>";
1818            $shell .= "<p style='margin:0 0 18px;color:#666;font-size:13px;'>Weekly commercial performance across <b>Grupo Fire</b> for <b>week {$weekNumber}</b> ({$regional['mondayDate']} &rarr; {$regional['sundayDate']}).</p>";
1819            $shell .= $content;
1820            $shell .= '</div>';
1821            $shell .= "<div style='padding:14px 24px;border-top:1px solid #eee;background:#fafafa;color:#999;font-size:11px;'>Generated automatically by Fire Service Titan.</div>";
1822            $shell .= '</div></div>';
1823
1824            $html = '<!DOCTYPE html>';
1825            $html .= '<html>';
1826            $html .= '<head>';
1827            $html .= '<meta charset="UTF-8">';
1828            $html .= '<meta name="viewport" content="width=device-width, initial-scale=1.0">';
1829            $html .= '</head>';
1830            $html .= '<body style="margin:0;padding:0;">';
1831            $html .= $shell;
1832            $html .= '</body>';
1833            $html .= '</html>';
1834
1835            if ($preview) {
1836                return response($html)->header('Content-Type', 'text/html; charset=UTF-8');
1837            }
1838
1839            $email = new Mail;
1840            $email->setFrom('fire@fire.es', 'Fire Service Titan');
1841            $email->setSubject($subject);
1842            $email->addTo($toEmail);
1843            $email->addContent('text/html', $html);
1844            $email->addAttachment(
1845                $imgpath,
1846                'image/png',
1847                'fireservicetitan.png',
1848                'inline',
1849                'fireservicetitan'
1850            );
1851
1852            $sendgrid = new \SendGrid(config('services.sendgrid.api_key'));
1853
1854            try {
1855                $response = $sendgrid->send($email);
1856                SendgridLogger::log($email, $response);
1857            } catch (\Throwable $sendException) {
1858                SendgridLogger::logException($email, $sendException);
1859                throw $sendException;
1860            }
1861
1862            if ($response->statusCode() == 202) {
1863                Log::channel('email_log')->info('ID:'.$toEmail.' - WEEKLY COMMERCIAL REPORT EMAIL NOTIFICATION SENT');
1864
1865                TblNotifications::create([
1866                    'user_id' => $toUserId,
1867                    'content' => $content,
1868                    'is_open' => 1,
1869                    'created_by' => 'System',
1870                ]);
1871
1872                TblNotificationLogs::create([
1873                    'commercial' => $toName,
1874                    'total_executive_report' => 1,
1875                    'created_by' => $sentBy,
1876                ]);
1877            } else {
1878                Log::channel('email_log')->error('ID:'.$toEmail.' - '.$response->body());
1879            }
1880
1881            return response(['message' => 'OK']);
1882
1883        } catch (\Exception $e) {
1884            report(AppException::fromException($e, 'SEND_WEEKLY_COMMERCIAL_REPORT_EMAIL_EXCEPTION'));
1885
1886            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1887        }
1888    }
1889
1890    /**
1891     * PART 1 data: per-region weekly + YTD figures for the current year, the
1892     * prior-year YTD comparison, the per-region annual € target, and Totals.
1893     */
1894    public function get_weekly_regional_performance(): array
1895    {
1896        $now = date('Y-m-d');
1897        $weekNumber = (int) date('W', strtotime($now));
1898        $mondayDate = date('Y-m-d', strtotime($now.' - '.(date('N', strtotime($now)) - 1).' days'));
1899        $sundayDate = date('Y-m-d', strtotime($mondayDate.' + 6 days'));
1900        $currentYear = (int) date('Y');
1901        $previousYear = $currentYear - 1;
1902        $currentMonth = date('Y-m');
1903        // Same calendar month one year earlier (e.g. 2025-06 vs 2026-06) so the
1904        // prior-year reference row's "Month" column compares like-for-like. The
1905        // previous-year query is filtered to YEAR = $previousYear, so passing
1906        // $currentMonth ('2026-06') here would never match and always yield 0.
1907        $previousMonth = $previousYear.'-'.date('m');
1908
1909        // Display order + label overrides (DB stores "Comunidad Valenciana").
1910        $regionOrder = [
1911            'Madrid' => 'Madrid',
1912            'Cataluña' => 'Cataluña',
1913            'Comunidad Valenciana' => 'Valencia',
1914            'Baleares' => 'Baleares',
1915        ];
1916
1917        $currentRows = $this->query_regional_metrics($currentYear, $mondayDate, $sundayDate, $currentMonth);
1918        $previousRows = $this->query_regional_metrics($previousYear, $mondayDate, $sundayDate, $previousMonth);
1919
1920        // Per-region annual target (sum across companies sharing a region;
1921        // realistically one "send executive" company per region).
1922        $targets = [];
1923        foreach (DB::select('SELECT region, COALESCE(SUM(annual_target), 0) annual_target FROM tbl_companies WHERE is_send_executive = 1 AND region IS NOT NULL GROUP BY region') as $t) {
1924            $targets[$t->region] = (float) $t->annual_target;
1925        }
1926
1927        // Merge any region present in the data but missing from the display map.
1928        foreach (array_keys($currentRows + $previousRows + $targets) as $regionKey) {
1929            if (! isset($regionOrder[$regionKey])) {
1930                $regionOrder[$regionKey] = $regionKey;
1931            }
1932        }
1933
1934        $ytdFactor = $weekNumber / 52;
1935        $regions = [];
1936        $totalsCurrent = $this->blank_region_metrics();
1937        $totalsPrevious = $this->blank_region_metrics();
1938        $totalAnnualTarget = 0.0;
1939
1940        foreach ($regionOrder as $regionKey => $label) {
1941            $cur = $currentRows[$regionKey] ?? $this->blank_region_metrics();
1942            $prev = $previousRows[$regionKey] ?? $this->blank_region_metrics();
1943            $annualTarget = $targets[$regionKey] ?? 0.0;
1944
1945            // Skip regions with no target AND no activity at all (keeps the
1946            // table to the operating regions).
1947            if ($annualTarget == 0 && $cur['ytd_budgets'] == 0 && $prev['ytd_budgets'] == 0) {
1948                continue;
1949            }
1950
1951            $regions[] = [
1952                'label' => $label,
1953                'current' => $cur,
1954                'previous' => $prev,
1955                'annual_target' => $annualTarget,
1956                'ytd_target' => $annualTarget * $ytdFactor,
1957                'diff_pct' => $this->ytd_diff_pct($cur['ytd_amount'], $annualTarget * $ytdFactor),
1958            ];
1959
1960            $totalsCurrent = $this->add_region_metrics($totalsCurrent, $cur);
1961            $totalsPrevious = $this->add_region_metrics($totalsPrevious, $prev);
1962            $totalAnnualTarget += $annualTarget;
1963        }
1964
1965        return [
1966            'weekNumber' => $weekNumber,
1967            'mondayDate' => $mondayDate,
1968            'sundayDate' => $sundayDate,
1969            'currentYear' => $currentYear,
1970            'previousYear' => $previousYear,
1971            'regions' => $regions,
1972            'totals' => [
1973                'current' => $totalsCurrent,
1974                'previous' => $totalsPrevious,
1975                'annual_target' => $totalAnnualTarget,
1976                'ytd_target' => $totalAnnualTarget * $ytdFactor,
1977                'diff_pct' => $this->ytd_diff_pct($totalsCurrent['ytd_amount'], $totalAnnualTarget * $ytdFactor),
1978            ],
1979        ];
1980    }
1981
1982    /**
1983     * Per-region conditional aggregation for one year. Returns a map keyed by
1984     * region with weekly, current-month and YTD figures (+ service splits).
1985     */
1986    private function query_regional_metrics($year, $mondayDate, $sundayDate, $currentMonth): array
1987    {
1988        $week = "DATE_FORMAT(q.created_at, '%Y-%m-%d') BETWEEN '{$mondayDate}' AND '{$sundayDate}'";
1989
1990        $query = "SELECT
1991                    c.region region,
1992                    COUNT(CASE WHEN {$week} THEN 1 END) week_budgets,
1993                    COALESCE(SUM(CASE WHEN {$week} THEN q.amount END), 0) week_amount,
1994                    COALESCE(SUM(CASE WHEN {$week} AND bt.budget_type_group_id = 3 THEN q.amount END), 0) week_preventive,
1995                    COALESCE(SUM(CASE WHEN {$week} AND bt.budget_type_group_id = 5 THEN q.amount END), 0) week_corrective,
1996                    COALESCE(SUM(CASE WHEN {$week} AND bt.budget_type_group_id = 4 THEN q.amount END), 0) week_installation,
1997                    COALESCE(SUM(CASE WHEN DATE_FORMAT(q.created_at, '%Y-%m') = '{$currentMonth}' THEN q.amount END), 0) month_amount,
1998                    COUNT(*) ytd_budgets,
1999                    COALESCE(SUM(q.amount), 0) ytd_amount,
2000                    COALESCE(SUM(CASE WHEN bt.budget_type_group_id = 3 THEN q.amount END), 0) ytd_preventive,
2001                    COALESCE(SUM(CASE WHEN bt.budget_type_group_id = 5 THEN q.amount END), 0) ytd_corrective,
2002                    COALESCE(SUM(CASE WHEN bt.budget_type_group_id = 4 THEN q.amount END), 0) ytd_installation
2003                FROM tbl_quotations q
2004                    JOIN tbl_companies c ON q.company_id = c.company_id
2005                    LEFT JOIN tbl_budget_types bt ON q.budget_type_id = bt.budget_type_id
2006                WHERE c.is_send_executive = 1
2007                    AND c.region IS NOT NULL
2008                    AND q.for_add = 0
2009                    AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
2010                    AND q.created_at IS NOT NULL
2011                    AND q.budget_type_id != 7
2012                    AND q.budget_type_id IS NOT NULL
2013                    AND YEAR(q.created_at) = YEAR(q.issue_date)
2014                    AND YEAR(q.created_at) = {$year}
2015                GROUP BY c.region";
2016
2017        $rows = [];
2018        foreach (DB::select($query) as $r) {
2019            $rows[$r->region] = [
2020                'week_budgets' => (int) $r->week_budgets,
2021                'week_amount' => (float) $r->week_amount,
2022                'week_preventive' => (float) $r->week_preventive,
2023                'week_corrective' => (float) $r->week_corrective,
2024                'week_installation' => (float) $r->week_installation,
2025                'month_amount' => (float) $r->month_amount,
2026                'ytd_budgets' => (int) $r->ytd_budgets,
2027                'ytd_amount' => (float) $r->ytd_amount,
2028                'ytd_preventive' => (float) $r->ytd_preventive,
2029                'ytd_corrective' => (float) $r->ytd_corrective,
2030                'ytd_installation' => (float) $r->ytd_installation,
2031            ];
2032        }
2033
2034        return $rows;
2035    }
2036
2037    private function blank_region_metrics(): array
2038    {
2039        return [
2040            'week_budgets' => 0, 'week_amount' => 0.0,
2041            'week_preventive' => 0.0, 'week_corrective' => 0.0, 'week_installation' => 0.0,
2042            'month_amount' => 0.0,
2043            'ytd_budgets' => 0, 'ytd_amount' => 0.0,
2044            'ytd_preventive' => 0.0, 'ytd_corrective' => 0.0, 'ytd_installation' => 0.0,
2045        ];
2046    }
2047
2048    private function add_region_metrics(array $acc, array $row): array
2049    {
2050        foreach ($acc as $k => $v) {
2051            $acc[$k] = $v + $row[$k];
2052        }
2053
2054        return $acc;
2055    }
2056
2057    private function ytd_diff_pct($ytdAmount, $ytdTarget)
2058    {
2059        if ($ytdTarget <= 0) {
2060            return null; // no target set → no pace comparison
2061        }
2062
2063        return (($ytdAmount - $ytdTarget) / $ytdTarget) * 100;
2064    }
2065
2066    /**
2067     * PART 2 data: aggregate the current week by commercial (restricted to
2068     * is_commercial users in "send executive" companies). Returns top-3 by
2069     * value and by volume, plus the below-weekly-target lists.
2070     */
2071    public function get_weekly_individual_performance($mondayDate, $sundayDate): array
2072    {
2073        $currentYear = (int) date('Y');
2074
2075        // Roster: commercials active this year (so dormant/ex names drop off);
2076        // anyone here who is under target this week — including zero activity —
2077        // belongs in a "below target" list.
2078        $roster = [];
2079        $rosterQuery = "SELECT DISTINCT q.commercial commercial
2080                FROM tbl_quotations q
2081                    JOIN tbl_companies c ON q.company_id = c.company_id
2082                    JOIN tbl_users u ON u.name = q.commercial AND u.is_commercial = 1
2083                WHERE c.is_send_executive = 1
2084                    AND q.for_add != 1
2085                    AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
2086                    AND q.created_at IS NOT NULL
2087                    AND q.budget_type_id != 7
2088                    AND q.commercial IS NOT NULL AND q.commercial != ''
2089                    AND YEAR(q.created_at) = YEAR(q.issue_date)
2090                    AND YEAR(q.created_at) = {$currentYear}";
2091        foreach (DB::select($rosterQuery) as $r) {
2092            $roster[$r->commercial] = $this->blank_commercial_metrics($r->commercial);
2093        }
2094
2095        // Current-week aggregates per commercial.
2096        $weekQuery = "SELECT
2097                    q.commercial commercial,
2098                    COUNT(*) n_budgets,
2099                    COALESCE(SUM(q.amount), 0) total_amount,
2100                    COALESCE(SUM(CASE WHEN bt.budget_type_group_id = 3 THEN q.amount END), 0) preventive,
2101                    COALESCE(SUM(CASE WHEN bt.budget_type_group_id = 5 THEN q.amount END), 0) corrective,
2102                    COALESCE(SUM(CASE WHEN bt.budget_type_group_id = 4 THEN q.amount END), 0) installation
2103                FROM tbl_quotations q
2104                    JOIN tbl_companies c ON q.company_id = c.company_id
2105                    LEFT JOIN tbl_budget_types bt ON q.budget_type_id = bt.budget_type_id
2106                    JOIN tbl_users u ON u.name = q.commercial AND u.is_commercial = 1
2107                WHERE c.is_send_executive = 1
2108                    AND q.for_add != 1
2109                    AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
2110                    AND DATE_FORMAT(q.created_at, '%Y-%m-%d') BETWEEN '{$mondayDate}' AND '{$sundayDate}'
2111                    AND q.budget_type_id != 7
2112                    AND q.commercial IS NOT NULL AND q.commercial != ''
2113                    AND YEAR(q.created_at) = YEAR(q.issue_date)
2114                GROUP BY q.commercial";
2115        foreach (DB::select($weekQuery) as $r) {
2116            $roster[$r->commercial] = [
2117                'name' => $r->commercial,
2118                'n_budgets' => (int) $r->n_budgets,
2119                'total_amount' => (float) $r->total_amount,
2120                'preventive' => (float) $r->preventive,
2121                'corrective' => (float) $r->corrective,
2122                'installation' => (float) $r->installation,
2123            ];
2124        }
2125
2126        $all = array_values($roster);
2127
2128        // Top 3 by value / volume — only those with activity this week.
2129        $active = array_values(array_filter($all, fn ($x) => $x['n_budgets'] > 0));
2130
2131        $byValue = $active;
2132        usort($byValue, fn ($a, $b) => $b['total_amount'] <=> $a['total_amount']);
2133        $topValue = array_slice($byValue, 0, 3);
2134
2135        $byVolume = $active;
2136        usort($byVolume, fn ($a, $b) => $b['n_budgets'] <=> $a['n_budgets']);
2137        $topVolume = array_slice($byVolume, 0, 3);
2138
2139        // Below weekly target — across the full active-this-year roster.
2140        $belowValue = array_values(array_filter($all, fn ($x) => $x['total_amount'] < self::WCR_TARGET_VALUE));
2141        usort($belowValue, fn ($a, $b) => $a['total_amount'] <=> $b['total_amount']);
2142
2143        $belowVolume = array_values(array_filter($all, fn ($x) => $x['n_budgets'] < self::WCR_TARGET_VOLUME));
2144        usort($belowVolume, fn ($a, $b) => $a['n_budgets'] <=> $b['n_budgets']);
2145
2146        return [
2147            'topValue' => $topValue,
2148            'topVolume' => $topVolume,
2149            'belowValue' => $belowValue,
2150            'belowVolume' => $belowVolume,
2151        ];
2152    }
2153
2154    private function blank_commercial_metrics($name): array
2155    {
2156        return [
2157            'name' => $name,
2158            'n_budgets' => 0,
2159            'total_amount' => 0.0,
2160            'preventive' => 0.0,
2161            'corrective' => 0.0,
2162            'installation' => 0.0,
2163        ];
2164    }
2165
2166    /**
2167     * Render the two-part HTML body (inline-styled, Fire Service Titan branded
2168     * tables for email clients). Brand red is #dc3545.
2169     */
2170    private function build_weekly_commercial_report_html(array $regional, array $individual): string
2171    {
2172        // Shared cell styles. Header cells carry the brand red; body cells stay
2173        // transparent so the per-row zebra background (set on <tr>) shows through.
2174        $th = "style='padding:9px 12px;background:#dc3545;color:#ffffff;text-align:right;font-size:12px;font-weight:600;'";
2175        $thL = "style='padding:9px 12px;background:#dc3545;color:#ffffff;text-align:left;font-size:12px;font-weight:600;'";
2176        $td = "style='padding:8px 12px;border-bottom:1px solid #eee;text-align:right;font-size:12px;color:#333;'";
2177        $tdL = "style='padding:8px 12px;border-bottom:1px solid #eee;text-align:left;font-size:12px;color:#333;'";
2178        $tableOpen = "<table style='width:100%;border-collapse:collapse;font-family:Arial,Helvetica,sans-serif;margin:0 0 22px;border:1px solid #ececec;border-radius:6px;overflow:hidden;'>";
2179        $zebra = fn ($i) => $i % 2 === 0 ? '#ffffff' : '#fbf6f6';
2180        $money = fn ($v) => $this->currency($v, 1, 2);
2181
2182        // ---- Regional performance ----
2183        $html = $this->wcr_section_title('Regional performance');
2184        $html .= $this->wcr_caption('This week');
2185
2186        // Weekly table
2187        $html .= $tableOpen;
2188        $html .= '<tr>'
2189            ."<th {$thL}>Region</th><th {$th}># budgets</th><th {$th}>€</th>"
2190            ."<th {$th}>Preventive (€)</th><th {$th}>Corrective (€)</th><th {$th}>Installation (€)</th></tr>";
2191        $i = 0;
2192        foreach ($regional['regions'] as $row) {
2193            $c = $row['current'];
2194            $html .= "<tr style='background:{$zebra($i)};'>"
2195                ."<td {$tdL}>{$row['label']}</td>"
2196                ."<td {$td}>".number_format($c['week_budgets'], 0, ',', '.').'</td>'
2197                ."<td {$td}>".$money($c['week_amount']).'</td>'
2198                ."<td {$td}>".$money($c['week_preventive']).'</td>'
2199                ."<td {$td}>".$money($c['week_corrective']).'</td>'
2200                ."<td {$td}>".$money($c['week_installation']).'</td></tr>';
2201            $i++;
2202        }
2203        $tc = $regional['totals']['current'];
2204        $html .= "<tr style='font-weight:bold;background:#f1f3f5;border-top:2px solid #dc3545;'>"
2205            ."<td {$tdL}>Total</td>"
2206            ."<td {$td}>".number_format($tc['week_budgets'], 0, ',', '.').'</td>'
2207            ."<td {$td}>".$money($tc['week_amount']).'</td>'
2208            ."<td {$td}>".$money($tc['week_preventive']).'</td>'
2209            ."<td {$td}>".$money($tc['week_corrective']).'</td>'
2210            ."<td {$td}>".$money($tc['week_installation']).'</td></tr>';
2211        $html .= '</table>';
2212
2213        // YTD table (with prior-year comparison rows). This table has 9 columns,
2214        // so it uses table-layout:fixed + compact cells to guarantee it never
2215        // exceeds the card width (otherwise the right columns get clipped by the
2216        // card's overflow:hidden). A colgroup gives the Region column more room.
2217        $thc = "style='padding:7px 6px;background:#dc3545;color:#ffffff;text-align:right;font-size:11px;font-weight:600;word-break:break-word;'";
2218        $thcL = "style='padding:7px 6px;background:#dc3545;color:#ffffff;text-align:left;font-size:11px;font-weight:600;word-break:break-word;'";
2219        $tdc = "style='padding:6px 6px;border-bottom:1px solid #eee;text-align:right;font-size:11px;color:#333;word-break:break-word;'";
2220        $tdcL = "style='padding:6px 6px;border-bottom:1px solid #eee;text-align:left;font-size:11px;color:#333;word-break:break-word;'";
2221
2222        $html .= $this->wcr_caption('Year to date');
2223        $html .= "<table style='width:100%;table-layout:fixed;border-collapse:collapse;font-family:Arial,Helvetica,sans-serif;margin:0 0 22px;border:1px solid #ececec;border-radius:6px;overflow:hidden;'>";
2224        $html .= "<colgroup><col style='width:16%;'>"
2225            ."<col style='width:11%;'><col style='width:11%;'>"
2226            ."<col style='width:11%;'><col style='width:11%;'><col style='width:11%;'>"
2227            ."<col style='width:10%;'><col style='width:10%;'><col style='width:9%;'></colgroup>";
2228        $html .= '<tr>'
2229            ."<th {$thcL}>Region</th><th {$thc}>Month (€)</th><th {$thc}>YTD (€)</th>"
2230            ."<th {$thc}>Prev. (€)</th><th {$thc}>Corr. (€)</th><th {$thc}>Instal. (€)</th>"
2231            ."<th {$thc}>YTD Target</th><th {$thc}>Annual Target</th><th {$thc}>% Diff</th></tr>";
2232        $i = 0;
2233        foreach ($regional['regions'] as $row) {
2234            $html .= $this->wcr_ytd_row($row['label'], $row['current'], $row['annual_target'], $row['ytd_target'], $row['diff_pct'], $tdc, $tdcL, $money, $zebra($i));
2235            $html .= $this->wcr_ytd_prev_row((string) $regional['previousYear'], $row['previous'], $money);
2236            $i++;
2237        }
2238        $t = $regional['totals'];
2239        $html .= $this->wcr_ytd_row('Total', $t['current'], $t['annual_target'], $t['ytd_target'], $t['diff_pct'], $tdc, $tdcL, $money, '#f1f3f5', true);
2240        $html .= $this->wcr_ytd_prev_row('Total '.$regional['previousYear'], $t['previous'], $money);
2241        $html .= '</table>';
2242
2243        // ---- Individual performance ----
2244        $html .= $this->wcr_section_title('Individual performance');
2245
2246        $html .= $this->wcr_individual_full_table('Top 3 — week by value (€)', $individual['topValue'], $th, $thL, $td, $tdL, $money, $zebra);
2247        $html .= $this->wcr_individual_full_table('Top 3 — week by volume (#)', $individual['topVolume'], $th, $thL, $td, $tdL, $money, $zebra);
2248        $html .= $this->wcr_individual_below_table(
2249            'Sales reps below weekly target by value (&lt; '.$money(self::WCR_TARGET_VALUE).')',
2250            $individual['belowValue'], $th, $thL, $td, $tdL, $money, $zebra
2251        );
2252        $html .= $this->wcr_individual_below_table(
2253            'Sales Specialists below weekly target by volume (&lt; '.self::WCR_TARGET_VOLUME.')',
2254            $individual['belowVolume'], $th, $thL, $td, $tdL, $money, $zebra
2255        );
2256
2257        return $html;
2258    }
2259
2260    private function wcr_section_title(string $title): string
2261    {
2262        return "<h3 style='font-family:Arial,Helvetica,sans-serif;font-size:15px;color:#b02a37;margin:26px 0 12px;padding-left:10px;border-left:4px solid #dc3545;'>{$title}</h3>";
2263    }
2264
2265    private function wcr_caption(string $text): string
2266    {
2267        return "<div style='font-family:Arial,Helvetica,sans-serif;font-size:11px;text-transform:uppercase;letter-spacing:.5px;color:#999;margin:0 0 6px;'>{$text}</div>";
2268    }
2269
2270    private function wcr_ytd_row($label, array $c, $annualTarget, $ytdTarget, $diffPct, $td, $tdL, $money, $bg, $bold = false): string
2271    {
2272        $weight = $bold ? 'font-weight:bold;' : '';
2273        $topBorder = $bold ? 'border-top:2px solid #dc3545;' : '';
2274        $diffCell = '—';
2275        if ($diffPct !== null) {
2276            $color = $diffPct >= 0 ? '#1a7f37' : '#dc3545';
2277            $sign = $diffPct >= 0 ? '+' : '';
2278            $diffCell = "<span style='color:{$color};font-weight:bold;'>{$sign}".number_format($diffPct, 1, ',', '.').'%</span>';
2279        }
2280        $ytdTargetCell = $annualTarget > 0 ? $money($ytdTarget) : '—';
2281        $annualCell = $annualTarget > 0 ? $money($annualTarget) : '—';
2282
2283        return "<tr style='background:{$bg};{$weight}{$topBorder}'>"
2284            ."<td {$tdL}>{$label}</td>"
2285            ."<td {$td}>".$money($c['month_amount']).'</td>'
2286            ."<td {$td}>".$money($c['ytd_amount']).'</td>'
2287            ."<td {$td}>".$money($c['ytd_preventive']).'</td>'
2288            ."<td {$td}>".$money($c['ytd_corrective']).'</td>'
2289            ."<td {$td}>".$money($c['ytd_installation']).'</td>'
2290            ."<td {$td}>{$ytdTargetCell}</td>"
2291            ."<td {$td}>{$annualCell}</td>"
2292            ."<td {$td}>{$diffCell}</td></tr>";
2293    }
2294
2295    private function wcr_ytd_prev_row($label, array $p, $money): string
2296    {
2297        $style = "style='padding:5px 6px;border-bottom:1px solid #f0f0f0;text-align:right;font-size:11px;color:#999;font-style:italic;word-break:break-word;'";
2298        $styleL = "style='padding:5px 6px;border-bottom:1px solid #f0f0f0;text-align:left;font-size:11px;color:#999;font-style:italic;word-break:break-word;'";
2299
2300        return "<tr style='background:#f7f8fa;'>"
2301            ."<td {$styleL}>{$label}</td>"
2302            ."<td {$style}>".$money($p['month_amount']).'</td>'
2303            ."<td {$style}>".$money($p['ytd_amount']).'</td>'
2304            ."<td {$style}>".$money($p['ytd_preventive']).'</td>'
2305            ."<td {$style}>".$money($p['ytd_corrective']).'</td>'
2306            ."<td {$style}>".$money($p['ytd_installation']).'</td>'
2307            ."<td {$style}>—</td><td {$style}>—</td><td {$style}>—</td></tr>";
2308    }
2309
2310    private function wcr_individual_full_table($title, array $rows, $th, $thL, $td, $tdL, $money, $zebra): string
2311    {
2312        $html = "<h4 style='font-family:Arial,Helvetica,sans-serif;font-size:13px;color:#444;margin:18px 0 6px;font-weight:600;'>{$title}</h4>";
2313        $html .= "<table style='width:100%;border-collapse:collapse;font-family:Arial,Helvetica,sans-serif;margin:0 0 18px;border:1px solid #ececec;border-radius:6px;overflow:hidden;'>";
2314        $html .= '<tr>'
2315            ."<th {$thL}>Name</th><th {$th}>Total €</th><th {$th}># budgets</th>"
2316            ."<th {$th}>Preventive (€)</th><th {$th}>Corrective (€)</th><th {$th}>Installation (€)</th></tr>";
2317        if (empty($rows)) {
2318            $html .= "<tr><td {$tdL} colspan='6' style='padding:10px 12px;color:#999;font-style:italic;'>Sin datos esta semana</td></tr>";
2319        } else {
2320            $i = 0;
2321            foreach ($rows as $r) {
2322                $html .= "<tr style='background:{$zebra($i)};'>"
2323                    ."<td {$tdL}>{$r['name']}</td>"
2324                    ."<td {$td}>".$money($r['total_amount']).'</td>'
2325                    ."<td {$td}>".number_format($r['n_budgets'], 0, ',', '.').'</td>'
2326                    ."<td {$td}>".$money($r['preventive']).'</td>'
2327                    ."<td {$td}>".$money($r['corrective']).'</td>'
2328                    ."<td {$td}>".$money($r['installation']).'</td></tr>';
2329                $i++;
2330            }
2331        }
2332        $html .= '</table>';
2333
2334        return $html;
2335    }
2336
2337    private function wcr_individual_below_table($title, array $rows, $th, $thL, $td, $tdL, $money, $zebra): string
2338    {
2339        $html = "<h4 style='font-family:Arial,Helvetica,sans-serif;font-size:13px;color:#444;margin:18px 0 6px;font-weight:600;'>{$title}</h4>";
2340        $html .= "<table style='width:100%;border-collapse:collapse;font-family:Arial,Helvetica,sans-serif;margin:0 0 18px;border:1px solid #ececec;border-radius:6px;overflow:hidden;'>";
2341        $html .= "<tr><th {$thL}>Name</th><th {$th}>€</th><th {$th}># budgets (week)</th></tr>";
2342        if (empty($rows)) {
2343            $html .= "<tr><td {$tdL} colspan='3' style='padding:10px 12px;color:#999;font-style:italic;'>Sin datos</td></tr>";
2344        } else {
2345            $i = 0;
2346            foreach ($rows as $r) {
2347                $html .= "<tr style='background:{$zebra($i)};'>"
2348                    ."<td {$tdL}>{$r['name']}</td>"
2349                    ."<td {$td}>".$money($r['total_amount']).'</td>'
2350                    ."<td {$td}>".number_format($r['n_budgets'], 0, ',', '.').'</td></tr>';
2351                $i++;
2352            }
2353        }
2354        $html .= '</table>';
2355
2356        return $html;
2357    }
2358
2359    public function get_cc_bcc_executive(): ResponseFactory|Response
2360    {
2361
2362        try {
2363
2364            $result = TblCcBccExecutiveNotifications::get();
2365
2366            return response(['message' => 'OK', 'data' => $result]);
2367
2368        } catch (\Exception $e) {
2369            report(AppException::fromException($e, 'GET_CC_BCC_EXECUTIVE_EXCEPTION'));
2370
2371            return response(['message' => 'KO', 'error' => $e->getMessage()]);
2372        }
2373    }
2374
2375    public function delete_cc_bcc_executive($id): ResponseFactory|Response
2376    {
2377
2378        try {
2379
2380            $id = addslashes((string) $id);
2381
2382            TblCcBccExecutiveNotifications::where('id', $id)->delete();
2383
2384            return response(['message' => 'OK']);
2385
2386        } catch (\Exception $e) {
2387            report(AppException::fromException($e, 'DELETE_CC_BCC_EXECUTIVE_EXCEPTION'));
2388
2389            return response(['message' => 'KO', 'error' => $e->getMessage()]);
2390        }
2391    }
2392
2393    public function update_cc_bcc_executive(Request $request, $id): ResponseFactory|Response
2394    {
2395
2396        try {
2397
2398            $data = $request->all();
2399            $id = addslashes((string) $id);
2400
2401            $x = $this->isEmailValid($data['email']);
2402
2403            if ($x) {
2404                $result = TblCcBccExecutiveNotifications::where('email', strtolower((string) $data['email']))->count();
2405
2406                if ($result > 0) {
2407                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
2408                } else {
2409                    $data['updated_at'] = date('Y-m-d H:i:s');
2410                    TblCcBccExecutiveNotifications::where('id', $id)->update($data);
2411                }
2412            } else {
2413                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
2414            }
2415
2416            return response(['message' => 'OK']);
2417
2418        } catch (\Exception $e) {
2419            report(AppException::fromException($e, 'UPDATE_CC_BCC_EXECUTIVE_EXCEPTION'));
2420
2421            return response(['message' => 'KO', 'error' => $e->getMessage()]);
2422        }
2423    }
2424
2425    public function create_cc_bcc_executive(Request $request): ResponseFactory|Response
2426    {
2427
2428        try {
2429
2430            $data = $request->all();
2431
2432            $x = $this->isEmailValid($data['email']);
2433
2434            if ($x) {
2435                $result = TblCcBccExecutiveNotifications::where('email', strtolower((string) $data['email']))->count();
2436
2437                if ($result > 0) {
2438                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
2439                } else {
2440                    TblCcBccExecutiveNotifications::create($data);
2441                }
2442            } else {
2443                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
2444            }
2445
2446            return response(['message' => 'OK']);
2447
2448        } catch (\Exception $e) {
2449            report(AppException::fromException($e, 'CREATE_CC_BCC_EXECUTIVE_EXCEPTION'));
2450
2451            return response(['message' => 'KO', 'error' => $e->getMessage()]);
2452        }
2453    }
2454
2455    public function update_notification_settings(Request $request): ResponseFactory|Response
2456    {
2457
2458        try {
2459
2460            $data = $request->all();
2461
2462            $data['updated_at'] = date('Y-m-d H:i:s');
2463            TblNotificationSettings::where('id', 1)->update($data);
2464
2465            return response(['message' => 'OK']);
2466
2467        } catch (\Exception $e) {
2468            report(AppException::fromException($e, 'UPDATE_NOTIFICATION_SETTINGS_EXCEPTION'));
2469
2470            return response(['message' => 'KO', 'error' => $e->getMessage()]);
2471        }
2472
2473    }
2474
2475    public function get_notification_settings(): ResponseFactory|Response
2476    {
2477
2478        try {
2479
2480            $result = TblNotificationSettings::where('id', 1)->first();
2481
2482            return response(['message' => 'OK', 'data' => $result]);
2483
2484        } catch (\Exception $e) {
2485            report(AppException::fromException($e, 'GET_NOTIFICATION_SETTINGS_EXCEPTION'));
2486
2487            return response(['message' => 'KO', 'error' => $e->getMessage()]);
2488        }
2489    }
2490
2491    public function send_report_logs(Request $request): ResponseFactory|Response
2492    {
2493
2494        try {
2495
2496            $data = $request->all();
2497
2498            $toEmail = config('services.sendgrid.to_email');
2499
2500            $imgpath = file_get_contents(public_path('fireservicetitan.png'));
2501
2502            $body = $data['logs'];
2503
2504            $body .= '<br><br><p>Fire Service Titan</p>';
2505            $body .= "<img src='cid:fireservicetitan' style='height: 45px;' />";
2506
2507            $html = '<!DOCTYPE html>';
2508            $html .= '<html>';
2509            $html .= '<head>';
2510            $html .= '<meta charset="UTF-8">';
2511            $html .= '<meta name="viewport" content="width=device-width, initial-scale=1.0">';
2512            $html .= '</head>';
2513            $html .= '<body>';
2514            $html .= $body;
2515            $html .= '</body>';
2516            $html .= '</html>';
2517
2518            $email = new Mail;
2519            $subject = 'FST Error report '.date('Y-m-d H:i:s');
2520
2521            $email->setFrom('fire@fire.es', 'Fire Service Titan');
2522            $email->setSubject($subject);
2523
2524            $email->addTo($toEmail);
2525            $email->addContent('text/html', $html);
2526
2527            $email->addAttachment(
2528                $imgpath,
2529                'image/png',
2530                'fireservicetitan.png',
2531                'inline',
2532                'fireservicetitan'
2533            );
2534
2535            $sendgrid = new \SendGrid(config('services.sendgrid.api_key'));
2536
2537            try {
2538                $response = $sendgrid->send($email);
2539                SendgridLogger::log($email, $response);
2540            } catch (\Throwable $sendException) {
2541                SendgridLogger::logException($email, $sendException);
2542                throw $sendException;
2543            }
2544
2545            if ($response->statusCode() == 202) {
2546                Log::channel('email_log')->info('ID:'.$toEmail.' - REPORT LOG EMAIL SENT');
2547            }
2548
2549            return response(['message' => 'OK']);
2550
2551        } catch (\Exception $e) {
2552            report(AppException::fromException($e, 'SEND_REPORT_LOGS_EXCEPTION'));
2553
2554            return response(['message' => 'KO', 'error' => $e->getMessage()]);
2555        }
2556    }
2557
2558    public function get_emails_acceptance_notifications($companyId): ResponseFactory|Response
2559    {
2560
2561        try {
2562
2563            $companyId = addslashes((string) $companyId);
2564
2565            $cc = TblCcAcceptanceNotifications::where('company_id', $companyId)->get();
2566            $to = TblToAcceptanceNotifications::where('company_id', $companyId)->get();
2567
2568            return response(['message' => 'OK', 'cc' => $cc, 'to' => $to]);
2569
2570        } catch (\Exception $e) {
2571            report(AppException::fromException($e, 'GET_EMAILS_ACCEPTANCE_NOTIFICATIONS_EXCEPTION'));
2572
2573            return response(['message' => 'KO', 'error' => $e->getMessage()]);
2574        }
2575    }
2576
2577    public function create_to_email_acceptance(Request $request): ResponseFactory|Response
2578    {
2579
2580        try {
2581
2582            $data = $request->all();
2583
2584            $x = $this->isEmailValid($data['email']);
2585
2586            if ($x) {
2587                $result = TblToAcceptanceNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
2588
2589                if ($result > 0) {
2590                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
2591                } else {
2592                    TblToAcceptanceNotifications::create($data);
2593                }
2594            } else {
2595                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
2596            }
2597
2598            return response(['message' => 'OK']);
2599
2600        } catch (\Exception $e) {
2601            report(AppException::fromException($e, 'CREATE_TO_EMAIL_ACCEPTANCE_EXCEPTION'));
2602
2603            return response(['message' => 'KO', 'error' => $e->getMessage()]);
2604        }
2605    }
2606
2607    public function delete_to_email_acceptance($id): ResponseFactory|Response
2608    {
2609
2610        try {
2611
2612            $id = addslashes((string) $id);
2613
2614            TblToAcceptanceNotifications::where('id', $id)->delete();
2615
2616            return response(['message' => 'OK']);
2617
2618        } catch (\Exception $e) {
2619            report(AppException::fromException($e, 'DELETE_TO_EMAIL_ACCEPTANCE_EXCEPTION'));
2620
2621            return response(['message' => 'KO', 'error' => $e->getMessage()]);
2622        }
2623    }
2624
2625    public function update_to_email_acceptance(Request $request, $id): ResponseFactory|Response
2626    {
2627
2628        try {
2629
2630            $data = $request->all();
2631            $id = addslashes((string) $id);
2632
2633            $x = $this->isEmailValid($data['email']);
2634
2635            if ($x) {
2636                $to = TblToAcceptanceNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
2637                $cc = TblCcAcceptanceNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
2638
2639                if ($to > 0 || $cc > 0) {
2640                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
2641                } else {
2642                    $data['updated_at'] = date('Y-m-d H:i:s');
2643                    TblToAcceptanceNotifications::where('id', $id)->update($data);
2644                }
2645            } else {
2646                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
2647            }
2648
2649            return response(['message' => 'OK']);
2650
2651        } catch (\Exception $e) {
2652            report(AppException::fromException($e, 'UPDATE_TO_EMAIL_ACCEPTANCE_EXCEPTION'));
2653
2654            return response(['message' => 'KO', 'error' => $e->getMessage()]);
2655        }
2656    }
2657
2658    public function create_cc_email_acceptance(Request $request): ResponseFactory|Response
2659    {
2660
2661        try {
2662
2663            $data = $request->all();
2664
2665            $x = $this->isEmailValid($data['email']);
2666
2667            if ($x) {
2668                $result = TblCcAcceptanceNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
2669
2670                if ($result > 0) {
2671                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
2672                } else {
2673                    TblCcAcceptanceNotifications::create($data);
2674                }
2675            } else {
2676                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
2677            }
2678
2679            return response(['message' => 'OK']);
2680
2681        } catch (\Exception $e) {
2682            report(AppException::fromException($e, 'CREATE_CC_EMAIL_ACCEPTANCE_EXCEPTION'));
2683
2684            return response(['message' => 'KO', 'error' => $e->getMessage()]);
2685        }
2686    }
2687
2688    public function delete_cc_email_acceptance($id): ResponseFactory|Response
2689    {
2690
2691        try {
2692
2693            $id = addslashes((string) $id);
2694
2695            TblCcAcceptanceNotifications::where('id', $id)->delete();
2696
2697            return response(['message' => 'OK']);
2698
2699        } catch (\Exception $e) {
2700            report(AppException::fromException($e, 'DELETE_CC_EMAIL_ACCEPTANCE_EXCEPTION'));
2701
2702            return response(['message' => 'KO', 'error' => $e->getMessage()]);
2703        }
2704    }
2705
2706    public function update_cc_email_acceptance(Request $request, $id): ResponseFactory|Response
2707    {
2708
2709        try {
2710
2711            $data = $request->all();
2712            $id = addslashes((string) $id);
2713
2714            $x = $this->isEmailValid($data['email']);
2715
2716            if ($x) {
2717                $cc = TblCcAcceptanceNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
2718                $to = TblToAcceptanceNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
2719
2720                if ($cc > 0 || $to > 0) {
2721                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
2722                } else {
2723                    $data['updated_at'] = date('Y-m-d H:i:s');
2724                    TblCcAcceptanceNotifications::where('id', $id)->update($data);
2725                }
2726            } else {
2727                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
2728            }
2729
2730            return response(['message' => 'OK']);
2731
2732        } catch (\Exception $e) {
2733            report(AppException::fromException($e, 'UPDATE_CC_EMAIL_ACCEPTANCE_EXCEPTION'));
2734
2735            return response(['message' => 'KO', 'error' => $e->getMessage()]);
2736        }
2737    }
2738
2739    public function list_last_follow_up_date($companyId): ResponseFactory|Response
2740    {
2741
2742        try {
2743
2744            $companyId = addslashes((string) $companyId);
2745
2746            $result = TblLastFollowUpDate::where('company_id', $companyId)->orderBy('row_id', 'ASC')->get();
2747
2748            $data = [];
2749
2750            for ($i = 0; $i < count($result); $i++) {
2751                $data[$result[$i]->row_id]['row_id'] = $result[$i]->row_id;
2752                $data[$result[$i]->row_id]['last_follow_up_date'] = $result[$i]->last_follow_up_date;
2753                if ($result[$i]->budget_type_id != null) {
2754                    $data[$result[$i]->row_id]['budget_types'][] = $result[$i]->budget_type_id;
2755                }
2756            }
2757
2758            return response(['message' => 'OK', 'data' => array_values($data)]);
2759
2760        } catch (\Exception $e) {
2761            report(AppException::fromException($e, 'LIST_LAST_FOLLOW_UP_DATE_EXCEPTION'));
2762
2763            return response(['message' => 'KO', 'error' => $e->getMessage()]);
2764        }
2765
2766    }
2767
2768    public function update_last_follow_up_date_working_days(Request $request)
2769    {
2770
2771        try {
2772
2773            $data = $request->all();
2774            $companyId = addslashes((string) $data['company_id']);
2775            $rowId = addslashes((string) $data['row_id']);
2776
2777            unset($data['company_id']);
2778            unset($data['row_id']);
2779
2780            $data['updated_at'] = date('Y-m-d H:i:s');
2781
2782            TblLastFollowUpDate::where('company_id', $companyId)->where('row_id', $rowId)->update($data);
2783
2784            return $this->list_last_follow_up_date($companyId);
2785
2786        } catch (\Exception $e) {
2787            report(AppException::fromException($e, 'UPDATE_LAST_FOLLOW_UP_DATE_WORKING_DAYS_EXCEPTION'));
2788
2789            return response(['message' => 'KO', 'error' => $e->getMessage()]);
2790        }
2791
2792    }
2793
2794    public function update_last_follow_up_date(Request $request)
2795    {
2796
2797        try {
2798
2799            $data = $request->all();
2800            $companyId = addslashes((string) $data['company_id']);
2801            $rowId = addslashes((string) $data['row_id']);
2802            unset($data['company_id']);
2803            unset($data['row_id']);
2804
2805            $previousData = TblLastFollowUpDate::where('company_id', $companyId)->where('row_id', $rowId)->first();
2806
2807            $lastBudgetTypeId = end($data['budget_type_id']);
2808            $lastFollowUpDate = TblLastFollowUpDate::where('company_id', $companyId)->where('budget_type_id', $lastBudgetTypeId)->first();
2809
2810            TblLastFollowUpDate::where('company_id', $companyId)->whereIn('budget_type_id', $data['budget_type_id'])->delete();
2811            TblLastFollowUpDate::where('company_id', $companyId)->where('row_id', $rowId)->delete();
2812
2813            foreach ($data['budget_type_id'] as $budgetTypeId) {
2814                TblLastFollowUpDate::create(
2815                    [
2816                        'company_id' => $companyId,
2817                        'row_id' => $rowId,
2818                        'budget_type_id' => $budgetTypeId,
2819                        'last_follow_up_date' => $previousData->last_follow_up_date,
2820                        'updated_by' => $data['updated_by'],
2821                        'updated_at' => date('Y-m-d H:i:s'),
2822                    ]
2823                );
2824            }
2825
2826            if ($lastFollowUpDate != null) {
2827                if ($lastFollowUpDate->budget_type_id != null) {
2828                    TblLastFollowUpDate::create(
2829                        [
2830                            'company_id' => $companyId,
2831                            'row_id' => $lastFollowUpDate->row_id,
2832                            'budget_type_id' => null,
2833                            'last_follow_up_date' => $lastFollowUpDate->last_follow_up_date,
2834                            'created_by' => $data['updated_by'],
2835                        ]
2836                    );
2837                }
2838            }
2839
2840            return $this->list_last_follow_up_date($companyId);
2841
2842        } catch (\Exception $e) {
2843            report(AppException::fromException($e, 'UPDATE_LAST_FOLLOW_UP_DATE_EXCEPTION'));
2844
2845            return response(['message' => 'KO', 'error' => $e->getMessage()]);
2846        }
2847
2848    }
2849
2850    public function delete_last_follow_up_date(Request $request)
2851    {
2852
2853        try {
2854
2855            $data = $request->all();
2856            $companyId = addslashes((string) $data['company_id']);
2857            $rowId = addslashes((string) $data['row_id']);
2858            unset($data['company_id']);
2859            unset($data['row_id']);
2860
2861            $count = TblLastFollowUpDate::where('company_id', $companyId)->where('row_id', $rowId)->count();
2862
2863            if ($count > 1) {
2864                TblLastFollowUpDate::where('company_id', $companyId)->where('budget_type_id', $data['budget_type_id'])->delete();
2865            } else {
2866                TblLastFollowUpDate::where('company_id', $companyId)->where('row_id', $rowId)->update(['budget_type_id' => null]);
2867            }
2868
2869            return $this->list_last_follow_up_date($companyId);
2870
2871        } catch (\Exception $e) {
2872            report(AppException::fromException($e, 'DELETE_LAST_FOLLOW_UP_DATE_EXCEPTION'));
2873
2874            return response(['message' => 'KO', 'error' => $e->getMessage()]);
2875        }
2876
2877    }
2878}