Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
| Total | |
7.05% |
150 / 2128 |
|
1.33% |
1 / 75 |
CRAP | |
0.00% |
0 / 1 |
| Notifications | |
7.05% |
150 / 2128 |
|
1.33% |
1 / 75 |
121913.32 | |
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 / 138 |
|
0.00% |
0 / 1 |
272 | |||
| send_request | |
0.00% |
0 / 10 |
|
0.00% |
0 / 1 |
12 | |||
| send_g3w_warning | |
0.00% |
0 / 20 |
|
0.00% |
0 / 1 |
12 | |||
| send_request_notification | |
0.00% |
0 / 149 |
|
0.00% |
0 / 1 |
380 | |||
| 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 | |||
| wcrIssuedDateSql | |
100.00% |
2 / 2 |
|
100.00% |
1 / 1 |
1 | |||
| send_weekly_commercial_report | |
0.00% |
0 / 34 |
|
0.00% |
0 / 1 |
420 | |||
| send_weekly_commercial_report_email | |
0.00% |
0 / 72 |
|
0.00% |
0 / 1 |
210 | |||
| get_weekly_regional_performance | |
0.00% |
0 / 119 |
|
0.00% |
0 / 1 |
272 | |||
| query_regional_metrics | |
42.22% |
19 / 45 |
|
0.00% |
0 / 1 |
2.77 | |||
| query_regional_accepted_metrics | |
45.95% |
17 / 37 |
|
0.00% |
0 / 1 |
2.63 | |||
| blank_region_metrics | |
0.00% |
0 / 14 |
|
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 | |
31.94% |
23 / 72 |
|
0.00% |
0 / 1 |
41.52 | |||
| blank_commercial_metrics | |
0.00% |
0 / 12 |
|
0.00% |
0 / 1 |
2 | |||
| get_weekly_year_series | |
92.86% |
52 / 56 |
|
0.00% |
0 / 1 |
16.09 | |||
| get_week_notes | |
66.67% |
8 / 12 |
|
0.00% |
0 / 1 |
3.33 | |||
| get_weekly_streaks | |
49.15% |
29 / 59 |
|
0.00% |
0 / 1 |
35.22 | |||
| wcr_tally_weeks | |
0.00% |
0 / 14 |
|
0.00% |
0 / 1 |
72 | |||
| build_weekly_commercial_report_html | |
0.00% |
0 / 137 |
|
0.00% |
0 / 1 |
56 | |||
| wcr_section | |
0.00% |
0 / 5 |
|
0.00% |
0 / 1 |
6 | |||
| wcr_two_col | |
0.00% |
0 / 10 |
|
0.00% |
0 / 1 |
6 | |||
| wcr_delta_class | |
0.00% |
0 / 5 |
|
0.00% |
0 / 1 |
20 | |||
| wcr_group_table | |
0.00% |
0 / 82 |
|
0.00% |
0 / 1 |
72 | |||
| wcr_obj_strip | |
0.00% |
0 / 31 |
|
0.00% |
0 / 1 |
56 | |||
| wcr_regional_table | |
0.00% |
0 / 83 |
|
0.00% |
0 / 1 |
210 | |||
| wcr_mix_table | |
0.00% |
0 / 40 |
|
0.00% |
0 / 1 |
110 | |||
| wcr_card_shell | |
0.00% |
0 / 12 |
|
0.00% |
0 / 1 |
20 | |||
| wcr_top_card | |
0.00% |
0 / 26 |
|
0.00% |
0 / 1 |
20 | |||
| wcr_year_charts_section | |
0.00% |
0 / 38 |
|
0.00% |
0 / 1 |
132 | |||
| wcr_streak_badge | |
0.00% |
0 / 17 |
|
0.00% |
0 / 1 |
30 | |||
| wcr_pills | |
0.00% |
0 / 16 |
|
0.00% |
0 / 1 |
20 | |||
| wcr_under_card | |
0.00% |
0 / 26 |
|
0.00% |
0 / 1 |
110 | |||
| wcr_region_label | |
0.00% |
0 / 3 |
|
0.00% |
0 / 1 |
2 | |||
| 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 | |
| 3 | namespace App\Http\Controllers; |
| 4 | |
| 5 | use App\Exceptions\AppException; |
| 6 | use App\Jobs\Email\SendFollowUpDigest; |
| 7 | use App\Models\TblCcAcceptanceNotifications; |
| 8 | use App\Models\TblCcBccExecutiveNotifications; |
| 9 | use App\Models\TblCcBccNotifications; |
| 10 | use App\Models\TblCcBccRequestNotifications; |
| 11 | use App\Models\TblCcG3WEmailReminders; |
| 12 | use App\Models\TblCompanies; |
| 13 | use App\Models\TblCompanyUsers; |
| 14 | use App\Models\TblLastFollowUpDate; |
| 15 | use App\Models\TblNotificationLogs; |
| 16 | use App\Models\TblNotifications; |
| 17 | use App\Models\TblNotificationSettings; |
| 18 | use App\Models\TblToAcceptanceNotifications; |
| 19 | use App\Models\TblUsers; |
| 20 | use App\Services\ExecutiveReportChartService; |
| 21 | use App\Services\SendgridDispatcher; |
| 22 | use App\Services\SendgridLogger; |
| 23 | use App\Services\UserCompanies; |
| 24 | use Illuminate\Contracts\Routing\ResponseFactory; |
| 25 | use Illuminate\Http\Request; |
| 26 | use Illuminate\Http\Response; |
| 27 | use Illuminate\Support\Facades\App; |
| 28 | use Illuminate\Support\Facades\Artisan; |
| 29 | use Illuminate\Support\Facades\DB; |
| 30 | use Illuminate\Support\Facades\Log; |
| 31 | use SendGrid\Mail\Mail; |
| 32 | |
| 33 | class Notifications extends Controller |
| 34 | { |
| 35 | private $locale; |
| 36 | |
| 37 | private $userId; |
| 38 | |
| 39 | private $region; |
| 40 | |
| 41 | private $companyIds; |
| 42 | |
| 43 | private $companyId; |
| 44 | |
| 45 | public function __construct() |
| 46 | { |
| 47 | $this->locale = request()->header('Locale-Id'); |
| 48 | $this->userId = request()->header('User-Id'); |
| 49 | $this->region = request()->header('Region'); |
| 50 | |
| 51 | App::setLocale($this->locale); |
| 52 | |
| 53 | $this->companyIds = []; |
| 54 | |
| 55 | if ($this->region != null && $this->region != '' && $this->region != 'All') { |
| 56 | $this->region = urldecode((string) $this->region); |
| 57 | |
| 58 | $query = 'SELECT |
| 59 | b.company_id |
| 60 | FROM |
| 61 | tbl_company_users a |
| 62 | LEFT JOIN tbl_companies b ON a.company_id = b.company_id |
| 63 | WHERE |
| 64 | a.user_id = ? |
| 65 | AND b.region = ?'; |
| 66 | |
| 67 | $this->companyIds = DB::select($query, [intval($this->userId), $this->region]); |
| 68 | |
| 69 | $this->companyIds = collect($this->companyIds)->pluck('company_id')->toArray(); |
| 70 | } else { |
| 71 | // FIRE-1146: prefer the middleware-resolved attribute; fall back to a fresh fetch only on unattached paths. |
| 72 | $this->companyIds = request()->attributes->get('user_company_ids', UserCompanies::forUser((int) $this->userId)); |
| 73 | } |
| 74 | |
| 75 | $this->companyId = implode(',', $this->companyIds); |
| 76 | } |
| 77 | |
| 78 | public function list_notifications(Request $request): ResponseFactory|Response |
| 79 | { |
| 80 | |
| 81 | try { |
| 82 | // FIRE-1149: paginated rewrite. |
| 83 | // Was: full table scan of all of this user's notifications (51k row table), |
| 84 | // returned the entire history, counted unread in PHP by iterating every row. |
| 85 | // Now: index seek on idx_notifications_user_created for both the unread |
| 86 | // count (filtered) and the paginated page. |
| 87 | $userId = (int) $request->input('user_id'); |
| 88 | $perPage = min(100, max(1, (int) $request->input('per_page', 50))); |
| 89 | $page = max(1, (int) $request->input('page', 1)); |
| 90 | $offset = ($page - 1) * $perPage; |
| 91 | |
| 92 | // Unread count — preserves the topbar badge semantics |
| 93 | // (topbar.component.ts:212 reads response.total as the bell-icon count). |
| 94 | $totalOpenNotification = (int) DB::table('tbl_notifications') |
| 95 | ->where('user_id', $userId) |
| 96 | ->where('is_open', '>', 0) |
| 97 | ->count(); |
| 98 | |
| 99 | // Page query — same projection as before (formatted_created_at relative |
| 100 | // time string), just with LIMIT / OFFSET and parameterised user_id. |
| 101 | $query = "SELECT |
| 102 | notification_id, |
| 103 | user_id, |
| 104 | REPLACE(content, '<br>', '') content, |
| 105 | link, |
| 106 | is_open, |
| 107 | created_by, |
| 108 | created_at, |
| 109 | updated_by, |
| 110 | updated_at, |
| 111 | CASE |
| 112 | WHEN TIMESTAMPDIFF(SECOND, created_at, NOW()) < 60 THEN |
| 113 | CASE |
| 114 | WHEN TIMESTAMPDIFF(SECOND, created_at, NOW()) = 1 THEN '1 second ago' |
| 115 | ELSE CONCAT(TIMESTAMPDIFF(SECOND, created_at, NOW()), ' seconds ago') |
| 116 | END |
| 117 | WHEN TIMESTAMPDIFF(MINUTE, created_at, NOW()) < 60 THEN |
| 118 | CASE |
| 119 | WHEN TIMESTAMPDIFF(MINUTE, created_at, NOW()) = 1 THEN '1 minute ago' |
| 120 | ELSE CONCAT(TIMESTAMPDIFF(MINUTE, created_at, NOW()), ' minutes ago') |
| 121 | END |
| 122 | WHEN TIMESTAMPDIFF(HOUR, created_at, NOW()) < 24 THEN |
| 123 | CASE |
| 124 | WHEN TIMESTAMPDIFF(HOUR, created_at, NOW()) = 1 THEN '1 hour ago' |
| 125 | ELSE CONCAT(TIMESTAMPDIFF(HOUR, created_at, NOW()), ' hours ago') |
| 126 | END |
| 127 | WHEN TIMESTAMPDIFF(DAY, created_at, NOW()) < 7 THEN |
| 128 | CASE |
| 129 | WHEN TIMESTAMPDIFF(DAY, created_at, NOW()) = 1 THEN '1 day ago' |
| 130 | ELSE CONCAT(TIMESTAMPDIFF(DAY, created_at, NOW()), ' days ago') |
| 131 | END |
| 132 | WHEN TIMESTAMPDIFF(WEEK, created_at, NOW()) < 4 THEN |
| 133 | CASE |
| 134 | WHEN TIMESTAMPDIFF(WEEK, created_at, NOW()) = 1 THEN '1 week ago' |
| 135 | ELSE CONCAT(TIMESTAMPDIFF(WEEK, created_at, NOW()), ' weeks ago') |
| 136 | END |
| 137 | WHEN TIMESTAMPDIFF(MONTH, created_at, NOW()) < 12 THEN |
| 138 | CASE |
| 139 | WHEN TIMESTAMPDIFF(MONTH, created_at, NOW()) = 1 THEN '1 month ago' |
| 140 | ELSE CONCAT(TIMESTAMPDIFF(MONTH, created_at, NOW()), ' months ago') |
| 141 | END |
| 142 | ELSE |
| 143 | CASE |
| 144 | WHEN TIMESTAMPDIFF(YEAR, created_at, NOW()) = 1 THEN '1 year ago' |
| 145 | ELSE CONCAT(TIMESTAMPDIFF(YEAR, created_at, NOW()), ' years ago') |
| 146 | END |
| 147 | END AS formatted_created_at |
| 148 | FROM |
| 149 | tbl_notifications |
| 150 | WHERE user_id = ? |
| 151 | ORDER BY |
| 152 | created_at DESC |
| 153 | LIMIT ? OFFSET ?"; |
| 154 | |
| 155 | $result = DB::select($query, [$userId, $perPage, $offset]); |
| 156 | |
| 157 | return response([ |
| 158 | 'message' => 'OK', |
| 159 | 'data' => $result, |
| 160 | 'total' => $totalOpenNotification, |
| 161 | '_pagination' => [ |
| 162 | 'current_page' => $page, |
| 163 | 'per_page' => $perPage, |
| 164 | 'has_more' => count($result) === $perPage, |
| 165 | ], |
| 166 | ]); |
| 167 | |
| 168 | } catch (\Exception $e) { |
| 169 | report(AppException::fromException($e, 'LIST_NOTIFICATIONS_EXCEPTION')); |
| 170 | |
| 171 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 172 | } |
| 173 | |
| 174 | } |
| 175 | |
| 176 | public function update_notifications(Request $request, $notificationId) |
| 177 | { |
| 178 | |
| 179 | try { |
| 180 | |
| 181 | $data = $request->all(); |
| 182 | |
| 183 | if ($notificationId == 999999999) { |
| 184 | TblNotifications::where('user_id', $data['user_id'])->update([ |
| 185 | 'is_open' => null, |
| 186 | ]); |
| 187 | } else { |
| 188 | TblNotifications::where('notification_id', $notificationId)->update([ |
| 189 | 'is_open' => null, |
| 190 | 'updated_at' => date('Y-m-d H:i:s'), |
| 191 | 'updated_by' => $data['updated_by'], |
| 192 | ]); |
| 193 | } |
| 194 | |
| 195 | $r = new Request([ |
| 196 | 'user_id' => $data['user_id'], |
| 197 | ]); |
| 198 | |
| 199 | return $this->list_notifications($r); |
| 200 | |
| 201 | } catch (\Exception $e) { |
| 202 | report(AppException::fromException($e, 'UPDATE_NOTIFICATION_EXCEPTION')); |
| 203 | |
| 204 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 205 | } |
| 206 | } |
| 207 | |
| 208 | public function send_follow_up_notification($userId = null): ResponseFactory|Response |
| 209 | { |
| 210 | |
| 211 | try { |
| 212 | |
| 213 | if ($userId != null) { |
| 214 | $this->userId = $userId; |
| 215 | $this->locale = 'es'; |
| 216 | App::setLocale($this->locale); |
| 217 | } |
| 218 | |
| 219 | $users = TblUsers::get(); |
| 220 | $totalOrders = 0; |
| 221 | $totalCompanies = 0; |
| 222 | foreach ($users as $user) { |
| 223 | // FIRE-1113: $body, $allTotalFollowUps, $companyCcIds must reset per user. |
| 224 | // Pre-fix they were declared above the loop and only appended to (`.=`), |
| 225 | // so user N's email contained the concatenated content + running totals |
| 226 | // of users 1..N-1 — a leak of every commercial's pipeline to the next. |
| 227 | $body = ''; |
| 228 | $allTotalFollowUps = 0; |
| 229 | $userCompanies = TblCompanyUsers::where('user_id', $user->id)->get(); |
| 230 | |
| 231 | $body .= __('language.send_follow_up_notification.body_hello'); |
| 232 | $body = str_replace('{{username}}', $user->name, $body); |
| 233 | |
| 234 | $body .= __('language.send_follow_up_notification.body_message'); |
| 235 | |
| 236 | $hasFollowUp = false; |
| 237 | $companyCcIds = []; |
| 238 | foreach ($userCompanies as $userCompany) { |
| 239 | |
| 240 | $companyId = $userCompany->company_id; |
| 241 | $company = TblCompanies::where('company_id', $companyId)->first(); |
| 242 | |
| 243 | // tbl_company_users can still point at a deleted company; |
| 244 | // reading its settings crashed the whole digest |
| 245 | // (PHP-LARAVEL-6V/6R: is_send_* on null). |
| 246 | if (! $company) { |
| 247 | continue; |
| 248 | } |
| 249 | |
| 250 | if (! SendgridDispatcher::isAllowed( |
| 251 | SendgridDispatcher::BUCKET_FOLLOW_UP, |
| 252 | (int) $companyId, |
| 253 | )) { |
| 254 | // FIRE-1930: skip this company's contribution to |
| 255 | // the digest when its region has the follow-up |
| 256 | // bucket off. Users whose companies span multiple |
| 257 | // regions still get the digest, minus the gated |
| 258 | // regions' rows. |
| 259 | continue; |
| 260 | } |
| 261 | |
| 262 | if ($company->is_send_follow_up > 0) { |
| 263 | $toEmail = $user->email; |
| 264 | |
| 265 | $query = "SELECT |
| 266 | id, |
| 267 | quote_id, |
| 268 | amount, |
| 269 | DATE_FORMAT(last_follow_up_date, '%d/%m/%Y') last_follow_up_date, |
| 270 | commercial |
| 271 | FROM |
| 272 | tbl_quotations |
| 273 | WHERE |
| 274 | last_follow_up_date < NOW() |
| 275 | AND email IS NOT NULL |
| 276 | AND email <> '' |
| 277 | AND NOT EXISTS ( |
| 278 | SELECT |
| 279 | 1 |
| 280 | FROM |
| 281 | tbl_blocked_domains bd |
| 282 | WHERE |
| 283 | email LIKE CONCAT('%', bd.domain, '%') |
| 284 | AND bd.company_id = company_id |
| 285 | ) |
| 286 | AND last_follow_up_date IS NOT NULL |
| 287 | AND reason_for_not_following_up_id IS NULL |
| 288 | AND last_follow_up_date > 0 |
| 289 | AND last_follow_up_date < NOW() |
| 290 | AND total_sent < 3 |
| 291 | AND for_add = 0 |
| 292 | AND company_id = {$companyId} |
| 293 | AND commercial IS NOT NULL |
| 294 | AND commercial = '{$user->name}' |
| 295 | AND budget_status_id = 2 |
| 296 | ORDER BY last_follow_up_date ASC, CAST(amount AS DOUBLE) DESC"; |
| 297 | |
| 298 | $result = DB::select($query); |
| 299 | |
| 300 | $totalFolloUps = count($result); |
| 301 | |
| 302 | if ($totalFolloUps > 0) { |
| 303 | $allTotalFollowUps = $allTotalFollowUps + $totalFolloUps; |
| 304 | array_push($companyCcIds, $companyId); |
| 305 | |
| 306 | $hasFollowUp = true; |
| 307 | $body .= __('language.send_follow_up_notification.body_company_message'); |
| 308 | $body = str_replace('{{total}}', $totalFolloUps, $body); |
| 309 | $body = str_replace('{{company}}', $company->name, $body); |
| 310 | |
| 311 | $clickhRef = config('app.frontend_url')."orders?commercial={$user->name}&status=Enviado&last_follow_up_date=1&company_id={$companyId}"; |
| 312 | $body = str_replace('{{click}}', $clickhRef, $body); |
| 313 | |
| 314 | $ul = '<ul>'; |
| 315 | |
| 316 | $list = ''; |
| 317 | $totalAmount = 0; |
| 318 | for ($i = 0; $i < $totalFolloUps; $i++) { |
| 319 | $li = __('language.send_follow_up_notification.body_due'); |
| 320 | |
| 321 | $id = $result[$i]->id; |
| 322 | $date = $result[$i]->last_follow_up_date; |
| 323 | $quoteId = $result[$i]->quote_id; |
| 324 | $totalAmount = $totalAmount + $result[$i]->amount; |
| 325 | $amount = $this->currency($result[$i]->amount, 1); |
| 326 | |
| 327 | $url = config('app.frontend_url')."orders/{$id}?company_id={$companyId}"; |
| 328 | $href = "<a href='{$url}'>{$quoteId}</a>"; |
| 329 | |
| 330 | $li = str_replace('{{quote_id}}', $href, $li); |
| 331 | $li = str_replace('{{amount}}', $amount, $li); |
| 332 | $list .= str_replace('{{date}}', $date, $li); |
| 333 | } |
| 334 | $ul .= $list; |
| 335 | $ul .= '</ul>'; |
| 336 | |
| 337 | $totalAmount = $this->currency($totalAmount, 1); |
| 338 | $body = str_replace('{{amount}}', $totalAmount, $body); |
| 339 | |
| 340 | $content = $body.$ul; |
| 341 | |
| 342 | $body .= $ul; |
| 343 | |
| 344 | } else { |
| 345 | continue; |
| 346 | } |
| 347 | } else { |
| 348 | continue; |
| 349 | } |
| 350 | } |
| 351 | |
| 352 | if ($hasFollowUp) { |
| 353 | |
| 354 | $body = str_replace('{{totalOrders}}', $allTotalFollowUps, $body); |
| 355 | $body = str_replace('{{totalCompanies}}', count($companyCcIds), $body); |
| 356 | |
| 357 | $clickhRef = config('app.frontend_url')."orders?commercial={$user->name}&status=Enviado&last_follow_up_date=1&company_id=0"; |
| 358 | $body = str_replace('{{clickAll}}', $clickhRef, $body); |
| 359 | |
| 360 | $imgpathS = file_get_contents(public_path('sendfollowups.png')); |
| 361 | $img = "<br><img src='cid:sendfollowups' />"; |
| 362 | $body .= $img; |
| 363 | |
| 364 | $imgpath = file_get_contents(public_path('fireservicetitan.png')); |
| 365 | $body .= '<br><p>Fire Service Titan</p>'; |
| 366 | $body .= "<img src='cid:fireservicetitan' style='height: 45px;' />"; |
| 367 | |
| 368 | $html = '<!DOCTYPE html>'; |
| 369 | $html .= '<html>'; |
| 370 | $html .= '<head>'; |
| 371 | $html .= '<meta charset="UTF-8">'; |
| 372 | $html .= '<meta name="viewport" content="width=device-width, initial-scale=1.0">'; |
| 373 | $html .= '</head>'; |
| 374 | $html .= '<body>'; |
| 375 | $html .= $body; |
| 376 | $html .= '</body>'; |
| 377 | $html .= '</html>'; |
| 378 | |
| 379 | $dateNow = date_create(date('Y-m-d')); |
| 380 | $dateNow = date_format($dateNow, 'd/m/Y'); |
| 381 | |
| 382 | $subject = __('language.send_follow_up_notification.subject'); |
| 383 | $subject = str_replace('{{dateNow}}', $dateNow, $subject); |
| 384 | |
| 385 | if ($toEmail != null) { |
| 386 | $userAdmin = TblUsers::where('id', $this->userId)->first(); |
| 387 | $sentBy = $userAdmin?->name ?? 'System'; |
| 388 | |
| 389 | $cc = []; |
| 390 | if (config('services.sendgrid.staging')) { |
| 391 | $toEmail = $userAdmin?->email ?? $toEmail; |
| 392 | // In staging we don't CC anyone — redirect every |
| 393 | // send to the admin running the cron. |
| 394 | } else { |
| 395 | $companyCcIds = array_values(array_unique($companyCcIds)); |
| 396 | $ccBcc = TblCcBccNotifications::whereIn('company_id', $companyCcIds)->get(); |
| 397 | $inCc = []; |
| 398 | |
| 399 | foreach ($ccBcc as $data) { |
| 400 | if ($data->email != $toEmail && ! in_array($data->email, $inCc)) { |
| 401 | $cc[] = $data->email; |
| 402 | $inCc[] = $data->email; |
| 403 | } |
| 404 | } |
| 405 | } |
| 406 | |
| 407 | // FIRE-1147: synchronous side effects (TblNotifications |
| 408 | // in-app banner + TblNotificationLogs audit row) land |
| 409 | // immediately; the email send is queued so a slow or |
| 410 | // failing SendGrid doesn't bottleneck the cron's |
| 411 | // per-user loop. |
| 412 | TblNotifications::create( |
| 413 | [ |
| 414 | 'user_id' => $user->id, |
| 415 | 'content' => $content, |
| 416 | 'is_open' => 1, |
| 417 | 'created_by' => 'System', |
| 418 | ] |
| 419 | ); |
| 420 | |
| 421 | TblNotificationLogs::create( |
| 422 | [ |
| 423 | 'commercial' => $user->name, |
| 424 | 'total_follow_ups' => $allTotalFollowUps, |
| 425 | 'created_by' => $sentBy, |
| 426 | ] |
| 427 | ); |
| 428 | |
| 429 | SendFollowUpDigest::dispatch( |
| 430 | toEmail: (string) $toEmail, |
| 431 | subject: $subject, |
| 432 | html: $html, |
| 433 | cc: $cc, |
| 434 | userId: $user->id, |
| 435 | )->onQueue('email'); |
| 436 | |
| 437 | $body = ''; |
| 438 | $content = ''; |
| 439 | } |
| 440 | } |
| 441 | |
| 442 | $hasFollowUp = false; |
| 443 | $body = ''; |
| 444 | $content = ''; |
| 445 | $allTotalFollowUps = 0; |
| 446 | } |
| 447 | |
| 448 | return response([ |
| 449 | 'message' => 'OK', |
| 450 | ]); |
| 451 | |
| 452 | } catch (\Exception $e) { |
| 453 | report(AppException::fromException($e, 'SEND_FOLLOW_UP_NOTIFICATION_EXCEPTION')); |
| 454 | |
| 455 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 456 | } |
| 457 | } |
| 458 | |
| 459 | public function send_request($companyId): ResponseFactory|Response |
| 460 | { |
| 461 | |
| 462 | try { |
| 463 | |
| 464 | $companyId = addslashes((string) $companyId); |
| 465 | |
| 466 | $user = ['commercial']; |
| 467 | |
| 468 | foreach ($user as $col) { |
| 469 | // $col is just 'commercial', but send_request_notification takes a $userId which isn't used as such. |
| 470 | // Assuming it was intended to only notify all users when called via CLI command. |
| 471 | $this->send_request_notification(); // Fixed method call |
| 472 | } |
| 473 | |
| 474 | return response([ |
| 475 | 'message' => 'OK', |
| 476 | ]); |
| 477 | |
| 478 | } catch (\Exception $e) { |
| 479 | report(AppException::fromException($e, 'SEND_REQUEST_EXCEPTION')); |
| 480 | |
| 481 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 482 | } |
| 483 | |
| 484 | } |
| 485 | |
| 486 | public function send_g3w_warning($companyId): ResponseFactory|Response |
| 487 | { |
| 488 | |
| 489 | try { |
| 490 | |
| 491 | $companyId = addslashes((string) $companyId); |
| 492 | |
| 493 | $userAdmin = TblUsers::where('id', $this->userId)->first(); |
| 494 | |
| 495 | if (config('services.sendgrid.staging')) { |
| 496 | Artisan::call('send:g3w-email-reminders', [ |
| 497 | 'sent_by' => $userAdmin->name, |
| 498 | 'email' => $userAdmin->email, |
| 499 | 'company_id' => $companyId, |
| 500 | ]); |
| 501 | } else { |
| 502 | Artisan::call('send:g3w-email-reminders', [ |
| 503 | 'sent_by' => $userAdmin->name, |
| 504 | 'email' => null, |
| 505 | 'company_id' => $companyId, |
| 506 | ]); |
| 507 | } |
| 508 | |
| 509 | $output = Artisan::output(); |
| 510 | |
| 511 | return response([ |
| 512 | 'message' => 'OK', |
| 513 | ]); |
| 514 | |
| 515 | } catch (\Exception $e) { |
| 516 | report(AppException::fromException($e, 'SEND_G3W_WARNING_EXCEPTION')); |
| 517 | |
| 518 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 519 | } |
| 520 | |
| 521 | } |
| 522 | |
| 523 | public function send_request_notification($userId = null): ResponseFactory|Response |
| 524 | { |
| 525 | |
| 526 | try { |
| 527 | |
| 528 | if ($userId != null) { |
| 529 | $this->userId = $userId; |
| 530 | $this->locale = 'es'; |
| 531 | App::setLocale($this->locale); |
| 532 | } |
| 533 | |
| 534 | $users = TblUsers::get(); |
| 535 | |
| 536 | foreach ($users as $user) { |
| 537 | // FIRE-1113: $body, $allTotalRequest, $companyCcIds must reset per user. |
| 538 | // Pre-fix they were declared above the loop and only appended to (`.=`), |
| 539 | // so user N's email contained the concatenated content + running totals |
| 540 | // of users 1..N-1 — a leak of every commercial's pipeline to the next. |
| 541 | $body = ''; |
| 542 | $allTotalRequest = 0; |
| 543 | $userCompanies = TblCompanyUsers::where('user_id', $user->id)->get(); |
| 544 | |
| 545 | $body .= __('language.send_request_notification.body_hello'); |
| 546 | $body = str_replace('{{username}}', $user->name, $body); |
| 547 | $body .= __('language.send_request_notification.body_intro'); |
| 548 | |
| 549 | $hasRequest = false; |
| 550 | $companyCcIds = []; |
| 551 | foreach ($userCompanies as $userCompany) { |
| 552 | |
| 553 | $companyId = $userCompany->company_id; |
| 554 | $company = TblCompanies::where('company_id', $companyId)->first(); |
| 555 | |
| 556 | // tbl_company_users can still point at a deleted company; |
| 557 | // reading its settings crashed the whole digest |
| 558 | // (PHP-LARAVEL-6V/6R: is_send_* on null). |
| 559 | if (! $company) { |
| 560 | continue; |
| 561 | } |
| 562 | |
| 563 | if (! SendgridDispatcher::isAllowed( |
| 564 | SendgridDispatcher::BUCKET_COMMERCIAL, |
| 565 | (int) $companyId, |
| 566 | )) { |
| 567 | // FIRE-1930: skip this company from the request |
| 568 | // digest when its region has bucket 3 off. Same |
| 569 | // semantics as the follow-up digest above. |
| 570 | continue; |
| 571 | } |
| 572 | |
| 573 | if ($company->is_send_request > 0) { |
| 574 | $toEmail = $user->email; |
| 575 | |
| 576 | $query = "SELECT |
| 577 | id, |
| 578 | quote_id, |
| 579 | DATE_FORMAT(created_at, '%d/%m/%Y') created_at, |
| 580 | commercial, |
| 581 | created_by |
| 582 | FROM |
| 583 | tbl_quotations |
| 584 | WHERE |
| 585 | company_id = {$companyId} |
| 586 | AND budget_status_id IN (6) |
| 587 | AND commercial = '{$user->name}'"; |
| 588 | |
| 589 | $result = DB::select($query); |
| 590 | |
| 591 | $totalRequest = count($result); |
| 592 | |
| 593 | if ($totalRequest > 0) { |
| 594 | $allTotalRequest = $allTotalRequest + $totalRequest; |
| 595 | $hasRequest = true; |
| 596 | array_push($companyCcIds, $companyId); |
| 597 | |
| 598 | $body .= __('language.send_request_notification.body_message_commercial'); |
| 599 | $body = str_replace('{{total}}', $totalRequest, $body); |
| 600 | $body = str_replace('{{company}}', $company->name, $body); |
| 601 | |
| 602 | $clickhRef = config('app.frontend_url')."orders?commercial={$user->name}&status=Solicitud&company_id={$companyId}"; |
| 603 | $body = str_replace('{{click}}', $clickhRef, $body); |
| 604 | |
| 605 | $ul = '<ul>'; |
| 606 | |
| 607 | $list = ''; |
| 608 | $totalAmount = 0; |
| 609 | for ($i = 0; $i < $totalRequest; $i++) { |
| 610 | $li = __('language.send_request_notification.body_due'); |
| 611 | |
| 612 | $id = $result[$i]->id; |
| 613 | $date = $result[$i]->created_at; |
| 614 | $quoteId = $result[$i]->quote_id; |
| 615 | |
| 616 | $url = config('app.frontend_url')."orders/{$id}?company_id={$companyId}"; |
| 617 | $href = "<a href='{$url}'>{$quoteId}</a>"; |
| 618 | |
| 619 | $li = str_replace('{{quote_id}}', $href, $li); |
| 620 | $list .= str_replace('{{date}}', $date, $li); |
| 621 | } |
| 622 | $ul .= $list; |
| 623 | $ul .= '</ul>'; |
| 624 | |
| 625 | $totalAmount = $this->currency($totalAmount, 1); |
| 626 | $body = str_replace('{{amount}}', $totalAmount, $body); |
| 627 | |
| 628 | $content = $body.$ul; |
| 629 | |
| 630 | $body .= $ul; |
| 631 | |
| 632 | } else { |
| 633 | continue; |
| 634 | } |
| 635 | } else { |
| 636 | continue; |
| 637 | } |
| 638 | } |
| 639 | |
| 640 | if ($hasRequest) { |
| 641 | |
| 642 | $body = str_replace('{{totalOrders}}', $allTotalRequest, $body); |
| 643 | $body = str_replace('{{totalCompanies}}', count($companyCcIds), $body); |
| 644 | |
| 645 | $clickhRef = config('app.frontend_url')."orders?commercial={$user->name}&status=Solicitud&company_id=0"; |
| 646 | $body = str_replace('{{clickAll}}', $clickhRef, $body); |
| 647 | |
| 648 | $imgpath = file_get_contents(public_path('fireservicetitan.png')); |
| 649 | |
| 650 | $body .= '<br><p>Fire Service Titan</p>'; |
| 651 | $body .= "<img src='cid:fireservicetitan' style='height: 45px;' />"; |
| 652 | |
| 653 | $html = '<!DOCTYPE html>'; |
| 654 | $html .= '<html>'; |
| 655 | $html .= '<head>'; |
| 656 | $html .= '<meta charset="UTF-8">'; |
| 657 | $html .= '<meta name="viewport" content="width=device-width, initial-scale=1.0">'; |
| 658 | $html .= '</head>'; |
| 659 | $html .= '<body>'; |
| 660 | $html .= $body; |
| 661 | $html .= '</body>'; |
| 662 | $html .= '</html>'; |
| 663 | |
| 664 | $dateNow = date_create(date('Y-m-d')); |
| 665 | $dateNow = date_format($dateNow, 'd/m/Y'); |
| 666 | |
| 667 | $subject = __('language.send_request_notification.subject'); |
| 668 | $subject = str_replace('{{dateNow}}', $dateNow, $subject); |
| 669 | $subject = str_replace('{{total}}', $allTotalRequest, $subject); |
| 670 | |
| 671 | if ($toEmail != null) { |
| 672 | $email = new Mail; |
| 673 | $sentBy = 'System'; |
| 674 | |
| 675 | $email->setFrom('fire@fire.es', 'Fire Service Titan'); |
| 676 | $email->setSubject($subject); |
| 677 | |
| 678 | $userAdmin = TblUsers::where('id', $this->userId)->first(); |
| 679 | $sentBy = $userAdmin->name; |
| 680 | |
| 681 | if (config('services.sendgrid.staging')) { |
| 682 | $toEmail = $userAdmin->email; |
| 683 | $sentBy = $userAdmin->name; |
| 684 | $user->id = $this->userId; |
| 685 | } else { |
| 686 | $companyCcIds = array_values(array_unique($companyCcIds)); |
| 687 | $ccBcc = TblCcBccRequestNotifications::whereIn('company_id', $companyCcIds)->get(); |
| 688 | |
| 689 | $inCc = []; |
| 690 | |
| 691 | if (count($ccBcc) > 0) { |
| 692 | foreach ($ccBcc as $data) { |
| 693 | if ($data->email != $toEmail && ! in_array($data->email, $inCc)) { |
| 694 | $email->addCc($data->email); |
| 695 | array_push($inCc, $data->email); |
| 696 | } |
| 697 | } |
| 698 | } |
| 699 | } |
| 700 | |
| 701 | $email->addTo($toEmail); |
| 702 | $email->addContent('text/html', $html); |
| 703 | |
| 704 | $email->addAttachment( |
| 705 | $imgpath, |
| 706 | 'image/png', |
| 707 | 'fireservicetitan.png', |
| 708 | 'inline', |
| 709 | 'fireservicetitan' |
| 710 | ); |
| 711 | |
| 712 | $sendgrid = new \SendGrid(config('services.sendgrid.api_key')); |
| 713 | |
| 714 | try { |
| 715 | $response = $sendgrid->send($email); |
| 716 | SendgridLogger::log($email, $response); |
| 717 | } catch (\Throwable $sendException) { |
| 718 | SendgridLogger::logException($email, $sendException); |
| 719 | throw $sendException; |
| 720 | } |
| 721 | |
| 722 | if ($response->statusCode() == 202) { |
| 723 | Log::channel('email_log')->info('ID:'.$toEmail.' - REQUEST EMAIL NOTIFICATION SENT'); |
| 724 | |
| 725 | TblNotifications::create( |
| 726 | [ |
| 727 | 'user_id' => $user->id, |
| 728 | 'content' => $content, |
| 729 | 'is_open' => 1, |
| 730 | 'created_by' => 'System', |
| 731 | ] |
| 732 | ); |
| 733 | |
| 734 | TblNotificationLogs::create( |
| 735 | [ |
| 736 | 'company_id' => $companyId, |
| 737 | 'commercial' => $user->name, |
| 738 | 'total_request' => $allTotalRequest, |
| 739 | 'created_by' => $sentBy, |
| 740 | ] |
| 741 | ); |
| 742 | |
| 743 | } else { |
| 744 | $error = true; |
| 745 | Log::channel('email_log')->error('ID:'.$toEmail.' - '.$response->body()); |
| 746 | } |
| 747 | |
| 748 | $body = ''; |
| 749 | $content = ''; |
| 750 | } |
| 751 | } |
| 752 | |
| 753 | $hasRequest = false; |
| 754 | $body = ''; |
| 755 | $content = ''; |
| 756 | $allTotalRequest = 0; |
| 757 | } |
| 758 | |
| 759 | return response([ |
| 760 | 'message' => 'OK', |
| 761 | ]); |
| 762 | |
| 763 | } catch (\Exception $e) { |
| 764 | report(AppException::fromException($e, 'SEND_REQUEST_EXCEPTION')); |
| 765 | |
| 766 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 767 | } |
| 768 | } |
| 769 | |
| 770 | public function get_cc_bcc($id): ResponseFactory|Response |
| 771 | { |
| 772 | |
| 773 | try { |
| 774 | |
| 775 | $companyId = addslashes((string) $id); |
| 776 | |
| 777 | $result = TblCcBccNotifications::where('company_id', $companyId)->get(); |
| 778 | |
| 779 | return response(['message' => 'OK', 'data' => $result]); |
| 780 | |
| 781 | } catch (\Exception $e) { |
| 782 | report(AppException::fromException($e, 'GET_CC_BCC_EXCEPTION')); |
| 783 | |
| 784 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 785 | } |
| 786 | } |
| 787 | |
| 788 | public function delete_cc_bcc($id): ResponseFactory|Response |
| 789 | { |
| 790 | |
| 791 | try { |
| 792 | |
| 793 | $id = addslashes((string) $id); |
| 794 | |
| 795 | TblCcBccNotifications::where('id', $id)->delete(); |
| 796 | |
| 797 | return response(['message' => 'OK']); |
| 798 | |
| 799 | } catch (\Exception $e) { |
| 800 | report(AppException::fromException($e, 'DELETE_CC_BCC_EXCEPTION')); |
| 801 | |
| 802 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 803 | } |
| 804 | } |
| 805 | |
| 806 | public function update_cc_bcc(Request $request, $id): ResponseFactory|Response |
| 807 | { |
| 808 | |
| 809 | try { |
| 810 | |
| 811 | $data = $request->all(); |
| 812 | $id = addslashes((string) $id); |
| 813 | |
| 814 | $x = $this->isEmailValid($data['email']); |
| 815 | |
| 816 | if ($x) { |
| 817 | $result = TblCcBccNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count(); |
| 818 | |
| 819 | if ($result > 0) { |
| 820 | return response(['message' => 'KO', 'error' => __('language.email_exists')]); |
| 821 | } else { |
| 822 | $data['updated_at'] = date('Y-m-d H:i:s'); |
| 823 | TblCcBccNotifications::where('id', $id)->update($data); |
| 824 | } |
| 825 | } else { |
| 826 | return response(['message' => 'KO', 'error' => __('language.email_invalid')]); |
| 827 | } |
| 828 | |
| 829 | return response(['message' => 'OK']); |
| 830 | |
| 831 | } catch (\Exception $e) { |
| 832 | report(AppException::fromException($e, 'UPDATE_CC_BCC_EXCEPTION')); |
| 833 | |
| 834 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 835 | } |
| 836 | } |
| 837 | |
| 838 | public function create_cc_bcc(Request $request): ResponseFactory|Response |
| 839 | { |
| 840 | |
| 841 | try { |
| 842 | |
| 843 | $data = $request->all(); |
| 844 | |
| 845 | $x = $this->isEmailValid($data['email']); |
| 846 | |
| 847 | if ($x) { |
| 848 | $result = TblCcBccNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count(); |
| 849 | |
| 850 | if ($result > 0) { |
| 851 | return response(['message' => 'KO', 'error' => __('language.email_exists')]); |
| 852 | } else { |
| 853 | TblCcBccNotifications::create($data); |
| 854 | } |
| 855 | } else { |
| 856 | return response(['message' => 'KO', 'error' => __('language.email_invalid')]); |
| 857 | } |
| 858 | |
| 859 | return response(['message' => 'OK']); |
| 860 | |
| 861 | } catch (\Exception $e) { |
| 862 | report(AppException::fromException($e, 'CREATE_CC_BCC_EXCEPTION')); |
| 863 | |
| 864 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 865 | } |
| 866 | } |
| 867 | |
| 868 | public function isEmailValid($email): bool |
| 869 | { |
| 870 | // Regular expression pattern for email validation |
| 871 | $pattern = '/^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$/'; |
| 872 | |
| 873 | // Check if the email matches the pattern |
| 874 | if (preg_match($pattern, (string) $email)) { |
| 875 | return true; // Valid email |
| 876 | } else { |
| 877 | return false; // Invalid email |
| 878 | } |
| 879 | } |
| 880 | |
| 881 | public function delete_notifications($userId) |
| 882 | { |
| 883 | |
| 884 | try { |
| 885 | |
| 886 | $userId = addslashes((string) $userId); |
| 887 | |
| 888 | TblNotifications::where('user_id', $userId)->delete(); |
| 889 | |
| 890 | $r = new Request([ |
| 891 | 'user_id' => $userId, |
| 892 | ]); |
| 893 | |
| 894 | return $this->list_notifications($r); |
| 895 | |
| 896 | } catch (\Exception $e) { |
| 897 | report(AppException::fromException($e, 'DELETE_NOTIFICATIONS_EXCEPTION')); |
| 898 | |
| 899 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 900 | } |
| 901 | |
| 902 | } |
| 903 | |
| 904 | public function list_notification_logs(Request $request, $companyId): ResponseFactory|Response |
| 905 | { |
| 906 | |
| 907 | try { |
| 908 | // FIRE-1149: paginated rewrite. Was a full scan of all 25k+ |
| 909 | // rows on every page load. Now uses idx_notiflogs_company_created |
| 910 | // for an index-only scan of the requested page. |
| 911 | $companyId = (int) $companyId; |
| 912 | $perPage = min(100, max(1, (int) $request->input('per_page', 50))); |
| 913 | $page = max(1, (int) $request->input('page', 1)); |
| 914 | $offset = ($page - 1) * $perPage; |
| 915 | |
| 916 | if ($companyId !== 0) { |
| 917 | $where = ' (company_id = ? OR company_id IS NULL) '; |
| 918 | $bindings = [$companyId, $perPage, $offset]; |
| 919 | } else { |
| 920 | // Multi-company case: $this->companyId is a CSV of ints set |
| 921 | // in the constructor from ResolveUserCompanies middleware, |
| 922 | // safe to interpolate (only used through ResultCache::remember |
| 923 | // and friends elsewhere — same trust model). |
| 924 | // Defensive: if the user has no companies assigned, fall back |
| 925 | // to "company_id IS NULL only" (the IN () form is illegal SQL). |
| 926 | $companyIdsList = trim((string) $this->companyId); |
| 927 | $where = $companyIdsList !== '' |
| 928 | ? " (company_id IN ({$companyIdsList}) OR company_id IS NULL) " |
| 929 | : ' company_id IS NULL '; |
| 930 | $bindings = [$perPage, $offset]; |
| 931 | } |
| 932 | |
| 933 | $query = "SELECT |
| 934 | id, |
| 935 | commercial, |
| 936 | total_follow_ups, |
| 937 | total_request, |
| 938 | total_executive_report, |
| 939 | total_g3w_warning, |
| 940 | CASE |
| 941 | WHEN total_follow_ups IS NOT NULL THEN total_follow_ups |
| 942 | WHEN total_request IS NOT NULL THEN total_request |
| 943 | WHEN total_executive_report IS NOT NULL THEN total_executive_report |
| 944 | WHEN total_g3w_warning IS NOT NULL THEN total_g3w_warning |
| 945 | END totals, |
| 946 | created_by, |
| 947 | created_at |
| 948 | FROM `tbl_notification_logs` |
| 949 | WHERE {$where} |
| 950 | ORDER BY created_at DESC |
| 951 | LIMIT ? OFFSET ?"; |
| 952 | |
| 953 | $result = DB::select($query, $bindings); |
| 954 | |
| 955 | return response([ |
| 956 | 'message' => 'OK', |
| 957 | 'data' => $result, |
| 958 | '_pagination' => [ |
| 959 | 'current_page' => $page, |
| 960 | 'per_page' => $perPage, |
| 961 | 'has_more' => count($result) === $perPage, |
| 962 | ], |
| 963 | ]); |
| 964 | |
| 965 | } catch (\Exception $e) { |
| 966 | report(AppException::fromException($e, 'LIST_NOTIFICATION_LOGS_EXCEPTION')); |
| 967 | |
| 968 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 969 | } |
| 970 | |
| 971 | } |
| 972 | |
| 973 | public function delete_notification_logs($companyId): ResponseFactory|Response |
| 974 | { |
| 975 | |
| 976 | try { |
| 977 | |
| 978 | $companyId = addslashes((string) $companyId); |
| 979 | |
| 980 | TblNotificationLogs::where('company_id', $companyId)->orWhere('company_id', null)->delete(); |
| 981 | |
| 982 | return response(['message' => 'OK']); |
| 983 | |
| 984 | } catch (\Exception $e) { |
| 985 | report(AppException::fromException($e, 'DELETE_NOTIFICATION_LOGS_EXCEPTION')); |
| 986 | |
| 987 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 988 | } |
| 989 | } |
| 990 | |
| 991 | public function get_cc_g3w($id): ResponseFactory|Response |
| 992 | { |
| 993 | |
| 994 | try { |
| 995 | |
| 996 | $companyId = addslashes((string) $id); |
| 997 | |
| 998 | $result = TblCcG3WEmailReminders::where('company_id', $companyId)->get(); |
| 999 | |
| 1000 | return response(['message' => 'OK', 'data' => $result]); |
| 1001 | |
| 1002 | } catch (\Exception $e) { |
| 1003 | report(AppException::fromException($e, 'GET_CC_G3W_EXCEPTION')); |
| 1004 | |
| 1005 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 1006 | } |
| 1007 | } |
| 1008 | |
| 1009 | public function delete_cc_g3w($id): ResponseFactory|Response |
| 1010 | { |
| 1011 | |
| 1012 | try { |
| 1013 | |
| 1014 | $id = addslashes((string) $id); |
| 1015 | |
| 1016 | TblCcG3WEmailReminders::where('id', $id)->delete(); |
| 1017 | |
| 1018 | return response(['message' => 'OK']); |
| 1019 | |
| 1020 | } catch (\Exception $e) { |
| 1021 | report(AppException::fromException($e, 'DELETE_CC_G3W_EXCEPTION')); |
| 1022 | |
| 1023 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 1024 | } |
| 1025 | } |
| 1026 | |
| 1027 | public function update_cc_g3w(Request $request, $id): ResponseFactory|Response |
| 1028 | { |
| 1029 | |
| 1030 | try { |
| 1031 | |
| 1032 | $data = $request->all(); |
| 1033 | $id = addslashes((string) $id); |
| 1034 | |
| 1035 | $x = $this->isEmailValid($data['email']); |
| 1036 | |
| 1037 | if ($x) { |
| 1038 | $result = TblCcG3WEmailReminders::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count(); |
| 1039 | |
| 1040 | if ($result > 0) { |
| 1041 | return response(['message' => 'KO', 'error' => __('language.email_exists')]); |
| 1042 | } else { |
| 1043 | $data['updated_at'] = date('Y-m-d H:i:s'); |
| 1044 | TblCcG3WEmailReminders::where('id', $id)->update($data); |
| 1045 | } |
| 1046 | } else { |
| 1047 | return response(['message' => 'KO', 'error' => __('language.email_invalid')]); |
| 1048 | } |
| 1049 | |
| 1050 | return response(['message' => 'OK']); |
| 1051 | |
| 1052 | } catch (\Exception $e) { |
| 1053 | report(AppException::fromException($e, 'UPDATE_CC_G3W_EXCEPTION')); |
| 1054 | |
| 1055 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 1056 | } |
| 1057 | } |
| 1058 | |
| 1059 | public function create_cc_g3w(Request $request): ResponseFactory|Response |
| 1060 | { |
| 1061 | |
| 1062 | try { |
| 1063 | |
| 1064 | $data = $request->all(); |
| 1065 | |
| 1066 | $x = $this->isEmailValid($data['email']); |
| 1067 | |
| 1068 | if ($x) { |
| 1069 | $result = TblCcG3WEmailReminders::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count(); |
| 1070 | |
| 1071 | if ($result > 0) { |
| 1072 | return response(['message' => 'KO', 'error' => __('language.email_exists')]); |
| 1073 | } else { |
| 1074 | TblCcG3WEmailReminders::create($data); |
| 1075 | } |
| 1076 | } else { |
| 1077 | return response(['message' => 'KO', 'error' => __('language.email_invalid')]); |
| 1078 | } |
| 1079 | |
| 1080 | return response(['message' => 'OK']); |
| 1081 | |
| 1082 | } catch (\Exception $e) { |
| 1083 | report(AppException::fromException($e, 'CREATE_CC_G3W_EXCEPTION')); |
| 1084 | |
| 1085 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 1086 | } |
| 1087 | } |
| 1088 | |
| 1089 | public function get_cc_bcc_request($id): ResponseFactory|Response |
| 1090 | { |
| 1091 | |
| 1092 | try { |
| 1093 | |
| 1094 | $companyId = addslashes((string) $id); |
| 1095 | |
| 1096 | $result = TblCcBccRequestNotifications::where('company_id', $companyId)->get(); |
| 1097 | |
| 1098 | return response(['message' => 'OK', 'data' => $result]); |
| 1099 | |
| 1100 | } catch (\Exception $e) { |
| 1101 | report(AppException::fromException($e, 'GET_CC_BCC_REQUEST_EXCEPTION')); |
| 1102 | |
| 1103 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 1104 | } |
| 1105 | } |
| 1106 | |
| 1107 | public function delete_cc_bcc_request($id): ResponseFactory|Response |
| 1108 | { |
| 1109 | |
| 1110 | try { |
| 1111 | |
| 1112 | $id = addslashes((string) $id); |
| 1113 | |
| 1114 | TblCcBccRequestNotifications::where('id', $id)->delete(); |
| 1115 | |
| 1116 | return response(['message' => 'OK']); |
| 1117 | |
| 1118 | } catch (\Exception $e) { |
| 1119 | report(AppException::fromException($e, 'DELETE_CC_BCC_REQUEST_EXCEPTION')); |
| 1120 | |
| 1121 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 1122 | } |
| 1123 | } |
| 1124 | |
| 1125 | public function update_cc_bcc_request(Request $request, $id): ResponseFactory|Response |
| 1126 | { |
| 1127 | |
| 1128 | try { |
| 1129 | |
| 1130 | $data = $request->all(); |
| 1131 | $id = addslashes((string) $id); |
| 1132 | |
| 1133 | $x = $this->isEmailValid($data['email']); |
| 1134 | |
| 1135 | if ($x) { |
| 1136 | $result = TblCcBccRequestNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count(); |
| 1137 | |
| 1138 | if ($result > 0) { |
| 1139 | return response(['message' => 'KO', 'error' => __('language.email_exists')]); |
| 1140 | } else { |
| 1141 | $data['updated_at'] = date('Y-m-d H:i:s'); |
| 1142 | TblCcBccRequestNotifications::where('id', $id)->update($data); |
| 1143 | } |
| 1144 | } else { |
| 1145 | return response(['message' => 'KO', 'error' => __('language.email_invalid')]); |
| 1146 | } |
| 1147 | |
| 1148 | return response(['message' => 'OK']); |
| 1149 | |
| 1150 | } catch (\Exception $e) { |
| 1151 | report(AppException::fromException($e, 'UPDATE_CC_BCC_REQUEST_EXCEPTION')); |
| 1152 | |
| 1153 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 1154 | } |
| 1155 | } |
| 1156 | |
| 1157 | public function create_cc_bcc_request(Request $request): ResponseFactory|Response |
| 1158 | { |
| 1159 | |
| 1160 | try { |
| 1161 | |
| 1162 | $data = $request->all(); |
| 1163 | |
| 1164 | $x = $this->isEmailValid($data['email']); |
| 1165 | |
| 1166 | if ($x) { |
| 1167 | $result = TblCcBccRequestNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count(); |
| 1168 | |
| 1169 | if ($result > 0) { |
| 1170 | return response(['message' => 'KO', 'error' => __('language.email_exists')]); |
| 1171 | } else { |
| 1172 | TblCcBccRequestNotifications::create($data); |
| 1173 | } |
| 1174 | } else { |
| 1175 | return response(['message' => 'KO', 'error' => __('language.email_invalid')]); |
| 1176 | } |
| 1177 | |
| 1178 | return response(['message' => 'OK']); |
| 1179 | |
| 1180 | } catch (\Exception $e) { |
| 1181 | report(AppException::fromException($e, 'CREATE_CC_BCC_REQUEST_EXCEPTION')); |
| 1182 | |
| 1183 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 1184 | } |
| 1185 | } |
| 1186 | |
| 1187 | public function send_executive(Request $request): ResponseFactory|Response |
| 1188 | { |
| 1189 | |
| 1190 | try { |
| 1191 | |
| 1192 | $data = $request->all(); |
| 1193 | |
| 1194 | $executiveEmails = TblCcBccExecutiveNotifications::get(); |
| 1195 | |
| 1196 | if (isset($data['User-ID'])) { |
| 1197 | $this->userId = $data['User-ID']; |
| 1198 | } |
| 1199 | |
| 1200 | if (isset($data['self']) && $data['self'] == 1) { |
| 1201 | $user = TblUsers::where('id', $this->userId)->first(); |
| 1202 | $this->send_executive_report($user->email, $user->name, $user->id, $data['processed_by']); |
| 1203 | } else { |
| 1204 | foreach ($executiveEmails as $item) { |
| 1205 | $user = TblUsers::where('email', $item->email)->first(); |
| 1206 | |
| 1207 | if ($user) { |
| 1208 | $this->send_executive_report($item->email, $user->name, $user->id, $data['processed_by']); |
| 1209 | |
| 1210 | continue; |
| 1211 | } |
| 1212 | |
| 1213 | // FIRE-1025: don't silently drop recipients whose email |
| 1214 | // isn't in tbl_users. Berta added Duarte on 2026-03-30 |
| 1215 | // expecting him to receive the report; the previous |
| 1216 | // `if ($user) { ... }` guard skipped him (and Albert and |
| 1217 | // Pablo) on every Saturday with no log line. Fall back |
| 1218 | // to a sensible display name + user_id = 1 (System) so |
| 1219 | // the email goes out, and surface the missing-user |
| 1220 | // case in the cron log so admins can clean up |
| 1221 | // tbl_cc_bcc_executive_notifications later. |
| 1222 | Log::channel('cron_send_executive_report')->warning( |
| 1223 | "send:executive-report — recipient {$item->email} has no matching tbl_users row; sending anyway with default name and user_id=1 (System)." |
| 1224 | ); |
| 1225 | $defaultName = strstr($item->email, '@', true) ?: $item->email; |
| 1226 | $this->send_executive_report($item->email, $defaultName, 1, $data['processed_by']); |
| 1227 | } |
| 1228 | } |
| 1229 | |
| 1230 | return response([ |
| 1231 | 'message' => 'OK', |
| 1232 | ]); |
| 1233 | |
| 1234 | } catch (\Exception $e) { |
| 1235 | report(AppException::fromException($e, 'SEND_EXECUTIVE_EXCEPTION')); |
| 1236 | |
| 1237 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 1238 | } |
| 1239 | |
| 1240 | } |
| 1241 | |
| 1242 | public function send_executive_report($toEmail, $toName, $toUserId, $sentBy = 'System') |
| 1243 | { |
| 1244 | |
| 1245 | try { |
| 1246 | |
| 1247 | $result = TblNotificationSettings::where('id', 1)->first(); |
| 1248 | |
| 1249 | $currentYear = date('Y'); |
| 1250 | $previousYear = date('Y', strtotime('-1 year', strtotime(date('Y-m-d')))); |
| 1251 | |
| 1252 | if ($toEmail != null && $result->is_auto_send_executive == 1) { |
| 1253 | |
| 1254 | if (config('services.sendgrid.staging')) { |
| 1255 | $user = TblUsers::where('id', $this->userId)->first(); |
| 1256 | $toEmail = $user->email; |
| 1257 | } |
| 1258 | |
| 1259 | $result = (object) $this->get_executive_reports(); |
| 1260 | |
| 1261 | $weekNumber = $result->weekNumber; |
| 1262 | $monday = $result->monday; |
| 1263 | $totalBudgets = number_format($result->totalBudgets, 0, ',', '.'); |
| 1264 | $totalAmountApproved = $result->totalAmountApproved; |
| 1265 | $totalAmount = $result->totalAmount; |
| 1266 | $dividendMaintenance = $result->dividendMaintenance; |
| 1267 | $dividendCorrectives = $result->dividendCorrectives; |
| 1268 | $percentageMoreThanLastWeek = $result->percentageMoreThanLastWeek; |
| 1269 | $approvalRate = $result->approvalRate; |
| 1270 | |
| 1271 | $subject = "FST executive report week $weekNumber (starting on monday - $monday) - Automated"; |
| 1272 | |
| 1273 | $body = "<p>Hello {$toName},</p>"; |
| 1274 | |
| 1275 | $url = config('app.frontend_url')."analytics?week={$weekNumber}"; |
| 1276 | $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>"; |
| 1277 | $body .= "<p><b>{$totalBudgets} budget(s)</b> ({$percentageMoreThanLastWeek}% compared to last week) created for a total of <b>{$totalAmount}</b> ({$dividendMaintenance}% “Mantenimiento”, {$dividendCorrectives}% “Correctivos”) - "; |
| 1278 | $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>"; |
| 1279 | |
| 1280 | $query = "SELECT |
| 1281 | a.company_id, |
| 1282 | a.name, |
| 1283 | ( |
| 1284 | SELECT |
| 1285 | COUNT(created_at) |
| 1286 | FROM |
| 1287 | tbl_quotations |
| 1288 | WHERE |
| 1289 | company_id = a.company_id |
| 1290 | AND for_add = 0 |
| 1291 | AND created_at IS NOT NULL |
| 1292 | AND issue_date IS NOT NULL |
| 1293 | AND YEAR(created_at) = YEAR(issue_date) |
| 1294 | ) total |
| 1295 | FROM |
| 1296 | tbl_companies a |
| 1297 | WHERE is_send_executive = 1 |
| 1298 | -- AND company_id IN ({$this->companyId}) |
| 1299 | ORDER BY |
| 1300 | 3 DESC |
| 1301 | "; |
| 1302 | |
| 1303 | $companies = DB::select($query); |
| 1304 | |
| 1305 | $li = []; |
| 1306 | |
| 1307 | foreach ($companies as $company) { |
| 1308 | $result = (object) $this->get_executive_reports($company->company_id); |
| 1309 | $result->name = $company->name; |
| 1310 | array_push($li, $result); |
| 1311 | } |
| 1312 | |
| 1313 | usort($li, fn ($a, $b) => $b->totalBudgets - $a->totalBudgets); |
| 1314 | |
| 1315 | $body .= '<ul>'; |
| 1316 | foreach ($li as $result) { |
| 1317 | $totalBudgets = number_format($result->totalBudgets, 0, ',', '.'); |
| 1318 | $totalAmount = $result->totalAmount; |
| 1319 | $dividendMaintenance = $result->dividendMaintenance; |
| 1320 | $dividendCorrectives = $result->dividendCorrectives; |
| 1321 | $percentageMoreThanLastWeek = $result->percentageMoreThanLastWeek; |
| 1322 | $approvalRate = $result->approvalRate; |
| 1323 | |
| 1324 | $body .= "<li>{$totalBudgets} order(s) in “<b>{$result->name}</b>” ({$dividendMaintenance}% “Mantenimiento”, {$dividendCorrectives}% “Correctivos”) for total of <b>{$totalAmount}</b></li>"; |
| 1325 | } |
| 1326 | $body .= '</ul>'; |
| 1327 | |
| 1328 | $result = (object) $this->get_executive_reports(null, true, $currentYear); |
| 1329 | |
| 1330 | $totalBudgets = number_format($result->totalBudgets, 0, ',', '.'); |
| 1331 | $totalAmountApproved = $result->totalAmountApproved; |
| 1332 | $totalAmount = $result->totalAmount; |
| 1333 | $dividendMaintenance = $result->dividendMaintenance; |
| 1334 | $dividendCorrectives = $result->dividendCorrectives; |
| 1335 | $percentageMoreThanLastWeek = $result->percentageMoreThanLastWeek; |
| 1336 | $approvalRate = $result->approvalRate; |
| 1337 | $totalDaysFromStartOfYear = date('z') + 1; |
| 1338 | $totalDaysOfCurrentYear = date('z', mktime(0, 0, 0, 12, 31, date('Y'))) + 1; |
| 1339 | $expected = 0; |
| 1340 | |
| 1341 | $totalGoalForTheYear = 0; |
| 1342 | if ($result->totalGoalForTheYear > 0) { |
| 1343 | $totalGoalForTheYear = number_format(($result->totalBudgets / $result->totalGoalForTheYear) * 100, 0, ',', '.'); |
| 1344 | $expected = number_format(($totalDaysFromStartOfYear / $totalDaysOfCurrentYear) * 100, 0, ',', '.'); |
| 1345 | } |
| 1346 | |
| 1347 | $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”) "; |
| 1348 | $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>"; |
| 1349 | |
| 1350 | $query = "SELECT |
| 1351 | a.company_id, |
| 1352 | a.name, |
| 1353 | ( |
| 1354 | SELECT |
| 1355 | COUNT(created_at) |
| 1356 | FROM |
| 1357 | tbl_quotations |
| 1358 | WHERE |
| 1359 | company_id = a.company_id |
| 1360 | AND for_add != 1 |
| 1361 | AND amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1 |
| 1362 | AND created_at IS NOT NULL |
| 1363 | AND YEAR(created_at) = {$currentYear} |
| 1364 | ) total |
| 1365 | FROM |
| 1366 | tbl_companies a |
| 1367 | WHERE is_send_executive = 1 |
| 1368 | -- AND company_id IN ({$this->companyId}) |
| 1369 | ORDER BY |
| 1370 | 3 DESC |
| 1371 | "; |
| 1372 | |
| 1373 | $companies = DB::select($query); |
| 1374 | |
| 1375 | $li = []; |
| 1376 | |
| 1377 | foreach ($companies as $company) { |
| 1378 | $result = (object) $this->get_executive_reports($company->company_id, true, $currentYear); |
| 1379 | $result->name = $company->name; |
| 1380 | array_push($li, $result); |
| 1381 | } |
| 1382 | |
| 1383 | usort($li, fn ($a, $b) => $b->totalBudgets - $a->totalBudgets); |
| 1384 | |
| 1385 | $body .= '<ul>'; |
| 1386 | foreach ($li as $result) { |
| 1387 | |
| 1388 | $totalBudgets = number_format($result->totalBudgets, 0, ',', '.'); |
| 1389 | $totalAmountApproved = $result->totalAmountApproved; |
| 1390 | $totalAmount = $result->totalAmount; |
| 1391 | $dividendMaintenance = $result->dividendMaintenance; |
| 1392 | $dividendCorrectives = $result->dividendCorrectives; |
| 1393 | $percentageMoreThanLastWeek = $result->percentageMoreThanLastWeek; |
| 1394 | $approvalRate = $result->approvalRate; |
| 1395 | $goalForTheYear = number_format($result->totalGoalForTheYear, 0, ',', '.'); |
| 1396 | $totalDaysFromStartOfYear = date('z') + 1; |
| 1397 | $totalDaysOfCurrentYear = date('z', mktime(0, 0, 0, 12, 31, date('Y'))) + 1; |
| 1398 | $expected = 0; |
| 1399 | |
| 1400 | $totalGoalForTheYear = 0; |
| 1401 | $style = "style='color: green'"; |
| 1402 | if ($result->totalGoalForTheYear > 0) { |
| 1403 | $totalGoalForTheYear = number_format(($result->totalBudgets / $result->totalGoalForTheYear) * 100, 0, ',', '.'); |
| 1404 | $expected = number_format(($totalDaysFromStartOfYear / $totalDaysOfCurrentYear) * 100, 0, ',', '.'); |
| 1405 | |
| 1406 | if ($totalGoalForTheYear < $expected) { |
| 1407 | $style = "style='color: red'"; |
| 1408 | } |
| 1409 | } |
| 1410 | |
| 1411 | $body .= "<li>{$totalBudgets} order(s) in “<b>{$result->name}</b>” ({$dividendMaintenance}% “Mantenimiento”, {$dividendCorrectives}% “Correctivos”) for total of <b>{$totalAmount}</b></li>"; |
| 1412 | $body .= '<ul>'; |
| 1413 | $body .= "<li><i><b>{$goalForTheYear} goal</b> for the year. <b {$style}>{$totalGoalForTheYear}%</b> accomplished to date vs <b>{$expected}% expected</b></i></li>"; |
| 1414 | $body .= "<li><i>Approval rate of <b>{$approvalRate}%</b> (<span style='color: blue;'>{$totalAmountApproved} approved</span>)</i></li>"; |
| 1415 | $body .= '</ul>'; |
| 1416 | } |
| 1417 | |
| 1418 | $body .= '</ul>'; |
| 1419 | |
| 1420 | $result = (object) $this->get_executive_reports(null, true, $previousYear); |
| 1421 | |
| 1422 | $totalBudgets = number_format($result->totalBudgets, 0, ',', '.'); |
| 1423 | $totalAmount = $result->totalAmount; |
| 1424 | $dividendMaintenance = $result->dividendMaintenance; |
| 1425 | $dividendCorrectives = $result->dividendCorrectives; |
| 1426 | $percentageMoreThanLastWeek = $result->percentageMoreThanLastWeek; |
| 1427 | $approvalRate = $result->approvalRate; |
| 1428 | |
| 1429 | $body .= "<p>For your reference, in <b>{$previousYear}</b>, <b>{$totalBudgets} budgets for a total of {$totalAmount}</b> ({$dividendMaintenance}% “Mantenimientos”, {$dividendCorrectives}% “Correctivos”) "; |
| 1430 | $body .= "were created across all companies - <i>we have an approval rate of <b>{$approvalRate}%</b> for the year</i></p>"; |
| 1431 | |
| 1432 | $query = "SELECT |
| 1433 | a.company_id, |
| 1434 | a.name, |
| 1435 | ( |
| 1436 | SELECT |
| 1437 | COUNT(created_at) |
| 1438 | FROM |
| 1439 | tbl_quotations |
| 1440 | WHERE |
| 1441 | company_id = a.company_id |
| 1442 | AND for_add != 1 |
| 1443 | AND amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1 |
| 1444 | AND created_at IS NOT NULL |
| 1445 | AND YEAR(created_at) = {$currentYear} |
| 1446 | ) total |
| 1447 | FROM |
| 1448 | tbl_companies a |
| 1449 | WHERE is_send_executive = 1 |
| 1450 | -- AND company_id IN ({$this->companyId}) |
| 1451 | ORDER BY |
| 1452 | 3 DESC |
| 1453 | "; |
| 1454 | |
| 1455 | $companies = DB::select($query); |
| 1456 | |
| 1457 | $li = []; |
| 1458 | |
| 1459 | foreach ($companies as $company) { |
| 1460 | $result = (object) $this->get_executive_reports($company->company_id, true, $previousYear); |
| 1461 | $result->name = $company->name; |
| 1462 | array_push($li, $result); |
| 1463 | } |
| 1464 | |
| 1465 | usort($li, fn ($a, $b) => $b->totalBudgets - $a->totalBudgets); |
| 1466 | |
| 1467 | $body .= '<ul>'; |
| 1468 | |
| 1469 | foreach ($li as $result) { |
| 1470 | $totalBudgets = number_format($result->totalBudgets, 0, ',', '.'); |
| 1471 | $totalAmount = $result->totalAmount; |
| 1472 | $dividendMaintenance = $result->dividendMaintenance; |
| 1473 | $dividendCorrectives = $result->dividendCorrectives; |
| 1474 | $percentageMoreThanLastWeek = $result->percentageMoreThanLastWeek; |
| 1475 | $approvalRate = $result->approvalRate; |
| 1476 | |
| 1477 | $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>"; |
| 1478 | } |
| 1479 | |
| 1480 | $body .= '</ul><br><br>'; |
| 1481 | |
| 1482 | $imgpath = file_get_contents(public_path('fireservicetitan.png')); |
| 1483 | |
| 1484 | $content = $body; |
| 1485 | |
| 1486 | $body .= '<br><p>Fire Service Titan</p>'; |
| 1487 | $body .= "<img src='cid:fireservicetitan' style='height: 45px;' />"; |
| 1488 | |
| 1489 | $html = '<!DOCTYPE html>'; |
| 1490 | $html .= '<html>'; |
| 1491 | $html .= '<head>'; |
| 1492 | $html .= '<meta charset="UTF-8">'; |
| 1493 | $html .= '<meta name="viewport" content="width=device-width, initial-scale=1.0">'; |
| 1494 | $html .= '</head>'; |
| 1495 | $html .= '<body>'; |
| 1496 | $html .= $body; |
| 1497 | $html .= '</body>'; |
| 1498 | $html .= '</html>'; |
| 1499 | |
| 1500 | $email = new Mail; |
| 1501 | |
| 1502 | $email->setFrom('fire@fire.es', 'Fire Service Titan'); |
| 1503 | $email->setSubject($subject); |
| 1504 | |
| 1505 | $email->addTo($toEmail); |
| 1506 | $email->addContent('text/html', $html); |
| 1507 | |
| 1508 | $email->addAttachment( |
| 1509 | $imgpath, |
| 1510 | 'image/png', |
| 1511 | 'fireservicetitan.png', |
| 1512 | 'inline', |
| 1513 | 'fireservicetitan' |
| 1514 | ); |
| 1515 | |
| 1516 | $sendgrid = new \SendGrid(config('services.sendgrid.api_key')); |
| 1517 | |
| 1518 | try { |
| 1519 | $response = $sendgrid->send($email); |
| 1520 | SendgridLogger::log($email, $response); |
| 1521 | } catch (\Throwable $sendException) { |
| 1522 | SendgridLogger::logException($email, $sendException); |
| 1523 | throw $sendException; |
| 1524 | } |
| 1525 | |
| 1526 | if ($response->statusCode() == 202) { |
| 1527 | Log::channel('email_log')->info('ID:'.$toEmail.' - EXECUTIVE REPORT EMAIL NOTIFICATION SENT'); |
| 1528 | |
| 1529 | TblNotifications::create( |
| 1530 | [ |
| 1531 | 'user_id' => $toUserId, |
| 1532 | 'content' => $content, |
| 1533 | 'is_open' => 1, |
| 1534 | 'created_by' => 'System', |
| 1535 | ] |
| 1536 | ); |
| 1537 | |
| 1538 | TblNotificationLogs::create( |
| 1539 | [ |
| 1540 | 'commercial' => $toName, |
| 1541 | 'total_executive_report' => 1, |
| 1542 | 'created_by' => $sentBy, |
| 1543 | ] |
| 1544 | ); |
| 1545 | } else { |
| 1546 | $error = true; |
| 1547 | Log::channel('email_log')->error('ID:'.$toEmail.' - '.$response->body()); |
| 1548 | } |
| 1549 | |
| 1550 | $body = ''; |
| 1551 | |
| 1552 | return response([ |
| 1553 | 'message' => 'OK', |
| 1554 | ]); |
| 1555 | } |
| 1556 | |
| 1557 | } catch (\Exception $e) { |
| 1558 | report(AppException::fromException($e, 'SEND_EXECUTIVE_REPORT_EXCEPTION')); |
| 1559 | |
| 1560 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 1561 | } |
| 1562 | |
| 1563 | } |
| 1564 | |
| 1565 | public function get_executive_reports($companyId = null, $wholeYear = false, $year = null): array |
| 1566 | { |
| 1567 | |
| 1568 | $where = ''; |
| 1569 | $whereYear = ''; |
| 1570 | $whereMonth = "DATE_FORMAT(q.created_at, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m') "; |
| 1571 | |
| 1572 | if ($companyId != null) { |
| 1573 | $where = " AND company_id = {$companyId} "; |
| 1574 | } |
| 1575 | |
| 1576 | $now = date('Y-m-d'); |
| 1577 | $weekNumber = date('W', strtotime($now)); |
| 1578 | $monday = date('F j, Y', strtotime($now.' - '.(date('N', strtotime($now)) - 1).' days')); |
| 1579 | |
| 1580 | $mondayDate = date('Y-m-d', strtotime($now.' - '.(date('N', strtotime($now)) - 1).' days')); |
| 1581 | $sundayDate = date('Y-m-d', strtotime($mondayDate.' + 6 days')); |
| 1582 | |
| 1583 | $mondayLastWeekDate = date('Y-m-d', strtotime($mondayDate.' - 1 week')); |
| 1584 | $sundayLastWeekDate = date('Y-m-d', strtotime($mondayLastWeekDate.' + 6 days')); |
| 1585 | |
| 1586 | $whereAccMonth = ''; |
| 1587 | if ($wholeYear == false) { |
| 1588 | $whereYear = " AND DATE_FORMAT(q.created_at, '%Y-%m-%d') BETWEEN '{$mondayDate}' AND '{$sundayDate}' "; |
| 1589 | $whereMonth = " AND DATE_FORMAT(q.created_at, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m') "; |
| 1590 | $whereAccMonth = " AND DATE_FORMAT(q.acceptance_date, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m') "; |
| 1591 | } else { |
| 1592 | $whereYear = " AND YEAR(q.created_at) = {$year} "; |
| 1593 | $whereMonth = " AND YEAR(q.created_at) = {$year} "; |
| 1594 | $whereAccMonth = " AND YEAR(q.acceptance_date) = {$year} "; |
| 1595 | } |
| 1596 | |
| 1597 | $query = "SELECT |
| 1598 | COUNT(q.created_at) totalBudgets, |
| 1599 | SUM(q.amount) totalAmount, |
| 1600 | COALESCE( |
| 1601 | SUM(CASE WHEN bt.budget_type_group_id = 3 THEN 1 END) |
| 1602 | , 0 |
| 1603 | ) / |
| 1604 | COUNT(q.created_at) * 100 dividendMaintenance, |
| 1605 | COALESCE( |
| 1606 | SUM(CASE WHEN bt.budget_type_group_id = 5 THEN 1 END) |
| 1607 | , 0 |
| 1608 | ) / |
| 1609 | COUNT(q.created_at) * 100 dividendCorrectives |
| 1610 | FROM |
| 1611 | tbl_quotations q |
| 1612 | LEFT JOIN tbl_budget_types bt ON q.budget_type_id = bt.budget_type_id |
| 1613 | WHERE |
| 1614 | q.for_add != 1 |
| 1615 | AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1 |
| 1616 | AND q.created_at IS NOT NULL |
| 1617 | AND q.company_id IN (SELECT company_id FROM tbl_companies WHERE is_send_executive = 1 {$where}) |
| 1618 | -- FIRE-1202: budget_type_id = 7 corresponds to a deleted/never-created |
| 1619 | -- tbl_budget_types row (verified empty on 2026-06-25). Filter is defensive; |
| 1620 | -- repeated in every executive-report aggregate (sibling filters at lines |
| 1621 | -- 1601, 1620, 1638, 1983, 2060, 2088). Not currently load-bearing — kept |
| 1622 | -- so a future re-insert of budget_type_id=7 doesn't silently appear in the |
| 1623 | -- executive totals before someone re-decides the policy. |
| 1624 | AND q.budget_type_id != 7 |
| 1625 | AND YEAR(q.created_at) = YEAR(q.issue_date) |
| 1626 | {$whereYear} |
| 1627 | "; |
| 1628 | |
| 1629 | $result = DB::select($query)[0]; |
| 1630 | |
| 1631 | $totalBudgets = $result->totalBudgets; |
| 1632 | $totalAmount = $this->currency($result->totalAmount, 1, 0); |
| 1633 | $totalAmountC = $result->totalAmount; |
| 1634 | $dividendMaintenance = number_format($result->dividendMaintenance, 0); |
| 1635 | $dividendCorrectives = number_format($result->dividendCorrectives, 0); |
| 1636 | |
| 1637 | $query = "SELECT |
| 1638 | COUNT(q.created_at) totalBudgetLastWeek |
| 1639 | FROM tbl_quotations q WHERE |
| 1640 | DATE_FORMAT(q.created_at, '%Y-%m-%d') BETWEEN '{$mondayLastWeekDate}' |
| 1641 | AND '{$sundayLastWeekDate}' |
| 1642 | AND q.for_add != 1 |
| 1643 | AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1 |
| 1644 | AND q.created_at IS NOT NULL |
| 1645 | AND q.budget_type_id != 7 |
| 1646 | AND q.budget_type_id IS NOT NULL |
| 1647 | AND YEAR(q.created_at) = YEAR(q.issue_date) |
| 1648 | AND q.company_id IN (SELECT company_id FROM tbl_companies WHERE is_send_executive = 1 {$where})"; |
| 1649 | |
| 1650 | $result = DB::select($query)[0]; |
| 1651 | |
| 1652 | $percentageMoreThanLastWeek = 0; |
| 1653 | |
| 1654 | if ($result->totalBudgetLastWeek > 0) { |
| 1655 | $percentageMoreThanLastWeek = number_format($totalBudgets / $result->totalBudgetLastWeek * 100, 2); |
| 1656 | } |
| 1657 | |
| 1658 | $query = "SELECT |
| 1659 | SUM(q.amount) totalAmount |
| 1660 | FROM tbl_quotations q WHERE |
| 1661 | q.for_add != 1 |
| 1662 | AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1 |
| 1663 | AND q.created_at IS NOT NULL |
| 1664 | AND q.budget_type_id != 7 |
| 1665 | AND q.budget_type_id IS NOT NULL |
| 1666 | AND YEAR(q.created_at) = YEAR(q.issue_date) |
| 1667 | AND q.company_id IN (SELECT company_id FROM tbl_companies WHERE is_send_executive = 1 {$where}) |
| 1668 | {$whereMonth}"; |
| 1669 | |
| 1670 | $result = DB::select($query)[0]; |
| 1671 | |
| 1672 | $totalAmountCC = $result->totalAmount; |
| 1673 | |
| 1674 | $query = "SELECT |
| 1675 | COUNT(CASE WHEN q.created_at IS NOT NULL THEN 1 END) totalBudgets, |
| 1676 | COUNT(CASE WHEN q.acceptance_date IS NOT NULL AND q.budget_status_id = 3 THEN 1 END) totalAcceptance, |
| 1677 | SUM(CASE WHEN q.acceptance_date IS NOT NULL AND q.budget_status_id = 3 THEN q.amount END) totalAmountApproved |
| 1678 | FROM tbl_quotations q WHERE |
| 1679 | q.for_add != 1 |
| 1680 | AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1 |
| 1681 | AND q.created_at IS NOT NULL |
| 1682 | AND q.budget_type_id != 7 |
| 1683 | AND q.budget_type_id IS NOT NULL |
| 1684 | AND YEAR(q.created_at) = YEAR(q.issue_date) |
| 1685 | AND q.company_id IN (SELECT company_id FROM tbl_companies WHERE is_send_executive = 1 {$where}) |
| 1686 | {$whereAccMonth}"; |
| 1687 | |
| 1688 | $result = DB::select($query)[0]; |
| 1689 | |
| 1690 | $approvalRate = 0; |
| 1691 | $totalAmountApproved = $result->totalAmountApproved; |
| 1692 | if ($totalAmountCC > 0) { |
| 1693 | $approvalRate = number_format(($totalAmountApproved / $totalAmountCC) * 100, 0); |
| 1694 | } |
| 1695 | |
| 1696 | $query = "SELECT SUM(goal_for_the_year) goalForTheYear FROM tbl_companies WHERE is_send_executive = 1 {$where}"; |
| 1697 | $result = DB::select($query)[0]; |
| 1698 | |
| 1699 | return [ |
| 1700 | 'weekNumber' => $weekNumber, |
| 1701 | 'monday' => $monday, |
| 1702 | 'totalBudgets' => $totalBudgets, |
| 1703 | 'totalAmount' => $totalAmount, |
| 1704 | 'totalAmountApproved' => $this->currency($totalAmountApproved, 1, 0), |
| 1705 | 'dividendMaintenance' => $dividendMaintenance, |
| 1706 | 'dividendCorrectives' => $dividendCorrectives, |
| 1707 | 'percentageMoreThanLastWeek' => $percentageMoreThanLastWeek, |
| 1708 | 'approvalRate' => $approvalRate, |
| 1709 | 'totalGoalForTheYear' => $result->goalForTheYear, |
| 1710 | ]; |
| 1711 | |
| 1712 | } |
| 1713 | |
| 1714 | public function currency($amount, $withEuro = null, $n = 2) |
| 1715 | { |
| 1716 | |
| 1717 | if ($withEuro != null) { |
| 1718 | $withEuro = ' €'; |
| 1719 | } |
| 1720 | |
| 1721 | return number_format($amount, $n, ',', '.').$withEuro; |
| 1722 | } |
| 1723 | |
| 1724 | // --------------------------------------------------------------------- |
| 1725 | // New weekly commercial performance report (redesigned executive email). |
| 1726 | // |
| 1727 | // Replaces the prose summary built by send_executive / send_executive_report |
| 1728 | // with two tabular parts: |
| 1729 | // PART 1 — Regional performance (per region + Total, with a prior-year |
| 1730 | // comparison): weekly #/€ and Preventive/Corrective/Installation |
| 1731 | // splits, plus YTD progress against the per-region annual € target. |
| 1732 | // PART 2 — Individual performance: top-3 by value and by volume, plus the |
| 1733 | // below-weekly-target lists (by € value and by budget volume). |
| 1734 | // |
| 1735 | // The old send_executive* methods are intentionally left untouched so the |
| 1736 | // previous report stays callable. Service-type € is split by |
| 1737 | // tbl_budget_types.budget_type_group_id (3 = Preventive/Mantenimiento, |
| 1738 | // 5 = Corrective/Correctivos, 4 = Installation/Instalaciones), matching the |
| 1739 | // group ids used across the analytics controllers. |
| 1740 | // --------------------------------------------------------------------- |
| 1741 | |
| 1742 | // Weekly per-person targets (spec: fixed thresholds). |
| 1743 | const WCR_TARGET_VALUE = 50000; // € — "Sales reps" by value |
| 1744 | |
| 1745 | // FIRE-1202 follow-up (Berta, 2026-06-26): bumped from 50 to 100 — the spec |
| 1746 | // originally said 50 by mistake; specialists are now expected to issue at |
| 1747 | // least 100 budgets/week. |
| 1748 | const WCR_TARGET_VOLUME = 100; // # budgets — "Sales Specialists" by volume |
| 1749 | |
| 1750 | // tbl_roles.name that splits the two below-target tables: the volume table |
| 1751 | // lists only this role, the value table lists every other commercial role. |
| 1752 | const WCR_SPECIALIST_ROLE = 'Sales specialist'; |
| 1753 | |
| 1754 | // FIRE-1448 Sub-task 4: Luis pushed back on the per-person section |
| 1755 | // including non-commercial staff (Oasys/Sat Valles partner-company users, |
| 1756 | // ex-employees, Regular-role internal users). The roster is now |
| 1757 | // restricted to the two "real commercial" roles Duarte confirmed, plus |
| 1758 | // any user flagged tbl_users.is_commercial_adyacente=1 (Iker et al. — |
| 1759 | // expected to submit budgets despite a non-commercial primary role). |
| 1760 | const WCR_ALLOWED_ROLES = ['Sales specialist', 'Asesor de clientes']; |
| 1761 | |
| 1762 | /** |
| 1763 | * How many no-activity weeks in a row count as time away. |
| 1764 | * |
| 1765 | * Revised to 1 (Duarte, 2026-08-01): a single week with no budgets and no |
| 1766 | * trace of the person in Titan is treated as a week away, rather than |
| 1767 | * requiring a run of three before it counts. |
| 1768 | * |
| 1769 | * An away week is simply not an eligible week — it is neither a hit nor a |
| 1770 | * miss. Since the figure the report shows is now a tally of weeks met across |
| 1771 | * the year (see wcr_walk_streak), away weeks drop out of it either way; this |
| 1772 | * threshold governs how the away weeks are counted and reported, not whether |
| 1773 | * a run survives. |
| 1774 | */ |
| 1775 | const WCR_STREAK_MAX_AWAY_WEEKS = 1; |
| 1776 | |
| 1777 | /** |
| 1778 | * Statuses whose quotes only count as issued once actually sent (Luis |
| 1779 | * Reyes, 2026-10-19): Nuevo (1), Listo para enviar (11), Fallo envío - |
| 1780 | * correo erróneo (21), Correo erróneo (22). Without a sent_date they were |
| 1781 | * counted on their issue_date fallback — ~2,900 quotes / 23.5 M€ in 2026 |
| 1782 | * that never reached the client. |
| 1783 | */ |
| 1784 | const WCR_NOT_ISSUED_UNTIL_SENT_STATUSES = [1, 11, 21, 22]; |
| 1785 | |
| 1786 | /** |
| 1787 | * The "issued" date of a quote for every section of the weekly report: |
| 1788 | * its last send, falling back to issue_date — except a never-sent quote |
| 1789 | * in one of the statuses above, which gets NULL and so fails every week / |
| 1790 | * month / YTD / series condition. Accepted figures don't use this. |
| 1791 | */ |
| 1792 | private static function wcrIssuedDateSql(): string |
| 1793 | { |
| 1794 | $statuses = implode(', ', self::WCR_NOT_ISSUED_UNTIL_SENT_STATUSES); |
| 1795 | |
| 1796 | return "(CASE WHEN q.sent_date IS NULL AND q.budget_status_id IN ({$statuses}) THEN NULL ELSE COALESCE(q.sent_date, q.issue_date) END)"; |
| 1797 | } |
| 1798 | |
| 1799 | /** |
| 1800 | * The four year-by-week charts, in render order. Titles render in the HTML |
| 1801 | * above each image (not inside the PNG), numbered 5.1 for emitido and 5.2 for |
| 1802 | * aceptado per Duarte's reference. |
| 1803 | */ |
| 1804 | const WCR_CHART_SERIES = [ |
| 1805 | 'emitido_num' => ['title' => '5.1 · EMITIDO por semana — nº de presupuestos', 'kind' => 'num'], |
| 1806 | 'emitido_eur' => ['title' => '5.1 · EMITIDO por semana — € presupuestados', 'kind' => 'eur'], |
| 1807 | 'aceptado_num' => ['title' => '5.2 · ACEPTADO por semana — nº de presupuestos', 'kind' => 'num'], |
| 1808 | 'aceptado_eur' => ['title' => '5.2 · ACEPTADO por semana — € aceptados', 'kind' => 'eur'], |
| 1809 | ]; |
| 1810 | |
| 1811 | /** |
| 1812 | * Per-run cache for the two expensive year-wide aggregations (the weekly |
| 1813 | * series and the per-person streaks). |
| 1814 | * |
| 1815 | * `send_weekly_commercial_report` loops the 9 executive recipients on a |
| 1816 | * single controller instance and rebuilds the whole report each time. The |
| 1817 | * content is identical for every recipient, so without this the streak |
| 1818 | * activity scan alone (~4s over tbl_quotations_log, which has no index on |
| 1819 | * created_at) would run nine times and add well over half a minute to the |
| 1820 | * Saturday cron. |
| 1821 | */ |
| 1822 | private array $wcrYearCache = []; |
| 1823 | |
| 1824 | /** |
| 1825 | * "Ref. 25 €" — full-year 2025 acceptance ratio (%) per region for Tabla 3, |
| 1826 | * computed on AMOUNT (€ accepted / € issued), not on budget count — which is |
| 1827 | * why the column and its footnote are labelled €. |
| 1828 | * Provided manually by Duarte (not yet exposed by the report/CRM). Keyed by |
| 1829 | * the canonical tbl_companies.region string. Value 'nuevo' = region opened |
| 1830 | * in 2026, so it has no 2025 reference. Region name mapping from Duarte's |
| 1831 | * list: Valencia → Comunidad Valenciana, Alicante → Castellón, |
| 1832 | * "Andalucía (Almería)" → Andalucía. |
| 1833 | */ |
| 1834 | const WCR_ACCEPTANCE_REF_2025 = [ |
| 1835 | 'Comunidad Valenciana' => 33, |
| 1836 | 'Cataluña' => 11, |
| 1837 | 'Madrid' => 21, |
| 1838 | 'Castilla y León' => 33, |
| 1839 | 'Castilla-La Mancha' => 36, |
| 1840 | 'Aragón' => 24, |
| 1841 | 'Castellón' => 'nuevo', |
| 1842 | 'Andalucía' => 'nuevo', |
| 1843 | 'Baleares' => 'nuevo', |
| 1844 | ]; |
| 1845 | |
| 1846 | public function send_weekly_commercial_report(Request $request): ResponseFactory|Response |
| 1847 | { |
| 1848 | try { |
| 1849 | $data = $request->all(); |
| 1850 | |
| 1851 | $executiveEmails = TblCcBccExecutiveNotifications::get(); |
| 1852 | |
| 1853 | if (isset($data['User-ID'])) { |
| 1854 | $this->userId = $data['User-ID']; |
| 1855 | } |
| 1856 | |
| 1857 | // For a "send to self" request the recipient is the authenticated |
| 1858 | // user, so take the id from the request header rather than a body |
| 1859 | // param. `backend-user-id` is set from the validated token by the |
| 1860 | // auth.token middleware (so it can't be spoofed); fall back to a |
| 1861 | // literal `User-ID` header, then the body param. |
| 1862 | if (isset($data['self']) && $data['self'] == 1) { |
| 1863 | $headerUserId = $request->header('backend-user-id') ?? $request->header('User-ID'); |
| 1864 | if ($headerUserId !== null && $headerUserId !== '') { |
| 1865 | $this->userId = $headerUserId; |
| 1866 | } |
| 1867 | } |
| 1868 | |
| 1869 | $sentBy = $data['processed_by'] ?? 'System'; |
| 1870 | |
| 1871 | // Optional week/year filter: generate the report "as of" a specific |
| 1872 | // ISO week instead of the current one. Omit for the normal |
| 1873 | // current-week run (cron). e.g. ?week=29&year=2026 |
| 1874 | $week = isset($data['week']) && $data['week'] !== '' ? (int) $data['week'] : null; |
| 1875 | $year = isset($data['year']) && $data['year'] !== '' ? (int) $data['year'] : null; |
| 1876 | |
| 1877 | // Preview mode: render and return the report HTML directly (no email |
| 1878 | // dispatched). Use ?preview=1 — handy for inspecting the layout in |
| 1879 | // Postman or a browser. |
| 1880 | if (isset($data['preview']) && $data['preview'] == 1) { |
| 1881 | $toName = 'Preview'; |
| 1882 | if ($this->userId) { |
| 1883 | $previewUser = TblUsers::where('id', $this->userId)->first(); |
| 1884 | $toName = $previewUser->name ?? 'Preview'; |
| 1885 | } |
| 1886 | |
| 1887 | return $this->send_weekly_commercial_report_email(null, $toName, $this->userId ?: 1, $sentBy, true, $week, $year); |
| 1888 | } |
| 1889 | |
| 1890 | if (isset($data['self']) && $data['self'] == 1) { |
| 1891 | $user = TblUsers::where('id', $this->userId)->first(); |
| 1892 | $this->send_weekly_commercial_report_email($user->email, $user->name, $user->id, $sentBy, false, $week, $year); |
| 1893 | } else { |
| 1894 | foreach ($executiveEmails as $item) { |
| 1895 | $user = TblUsers::where('email', $item->email)->first(); |
| 1896 | |
| 1897 | if ($user) { |
| 1898 | $this->send_weekly_commercial_report_email($item->email, $user->name, $user->id, $sentBy, false, $week, $year); |
| 1899 | |
| 1900 | continue; |
| 1901 | } |
| 1902 | |
| 1903 | // Mirror FIRE-1025: don't silently drop recipients whose |
| 1904 | // email isn't in tbl_users — send anyway with a default |
| 1905 | // name and user_id = 1 (System), and log the gap. |
| 1906 | Log::channel('cron_send_executive_report')->warning( |
| 1907 | "send:executive-report (weekly commercial) — recipient {$item->email} has no matching tbl_users row; sending anyway with default name and user_id=1 (System)." |
| 1908 | ); |
| 1909 | $defaultName = strstr($item->email, '@', true) ?: $item->email; |
| 1910 | $this->send_weekly_commercial_report_email($item->email, $defaultName, 1, $sentBy, false, $week, $year); |
| 1911 | } |
| 1912 | } |
| 1913 | |
| 1914 | return response(['message' => 'OK']); |
| 1915 | |
| 1916 | } catch (\Exception $e) { |
| 1917 | report(AppException::fromException($e, 'SEND_WEEKLY_COMMERCIAL_REPORT_EXCEPTION')); |
| 1918 | |
| 1919 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 1920 | } |
| 1921 | } |
| 1922 | |
| 1923 | public function send_weekly_commercial_report_email($toEmail, $toName, $toUserId, $sentBy = 'System', $preview = false, $week = null, $year = null) |
| 1924 | { |
| 1925 | try { |
| 1926 | $settings = TblNotificationSettings::where('id', 1)->first(); |
| 1927 | |
| 1928 | // Preview mode (used for testing) skips the auto-send gate and the |
| 1929 | // staging redirect, and returns the rendered HTML instead of |
| 1930 | // dispatching the email. |
| 1931 | if (! $preview && ($toEmail == null || $settings->is_auto_send_executive != 1)) { |
| 1932 | return response(['message' => 'OK']); |
| 1933 | } |
| 1934 | |
| 1935 | // Staging safety: never email real recipients — redirect to the |
| 1936 | // logged-in user (same guard as send_executive_report). |
| 1937 | if (! $preview && config('services.sendgrid.staging')) { |
| 1938 | $user = TblUsers::where('id', $this->userId)->first(); |
| 1939 | $toEmail = $user->email; |
| 1940 | } |
| 1941 | |
| 1942 | $regional = $this->get_weekly_regional_performance($week, $year); |
| 1943 | $individual = $this->get_weekly_individual_performance($regional['mondayDate'], $regional['sundayDate']); |
| 1944 | |
| 1945 | $weekNumber = $regional['weekNumber']; |
| 1946 | $subject = "FST weekly commercial report — week {$weekNumber} (week of {$regional['mondayDate']}) - Automated"; |
| 1947 | |
| 1948 | // Section 5 — the year week by week. The reporting week is "open" |
| 1949 | // whenever its Sunday has not passed yet, which is the normal |
| 1950 | // Saturday-dispatch case; a ?week= override of a past week is closed. |
| 1951 | $lastWeekOpen = $regional['sundayDate'] >= date('Y-m-d'); |
| 1952 | $series = $this->get_weekly_year_series( |
| 1953 | (int) $regional['currentYear'], |
| 1954 | (int) $weekNumber, |
| 1955 | (string) $regional['ytdCutoff'], |
| 1956 | $lastWeekOpen |
| 1957 | ); |
| 1958 | |
| 1959 | $chartService = new ExecutiveReportChartService; |
| 1960 | $charts = []; |
| 1961 | $chartCids = []; |
| 1962 | $imgSrc = []; |
| 1963 | foreach ($series as $key => $s) { |
| 1964 | $png = $chartService->renderSeries($s['points'], (float) $s['average'], $s['kind']); |
| 1965 | if ($png === '') { |
| 1966 | // GD missing or nothing to draw — the section falls back to |
| 1967 | // its HTML figure strip rather than showing a broken image. |
| 1968 | continue; |
| 1969 | } |
| 1970 | |
| 1971 | $cid = 'wcrchart'.str_replace('_', '', $key); |
| 1972 | $charts[$key] = $png; |
| 1973 | $chartCids[$key] = $cid; |
| 1974 | |
| 1975 | // Preview renders straight into a browser, where cid: resolves to |
| 1976 | // nothing, so inline the bytes there. Email keeps cid: on purpose: |
| 1977 | // four base64 data URIs would add ~200KB to the body and push it |
| 1978 | // past Gmail's ~102KB clipping threshold, which would truncate the |
| 1979 | // report mid-page. |
| 1980 | $imgSrc[$key] = $preview |
| 1981 | ? 'data:image/png;base64,'.base64_encode($png) |
| 1982 | : 'cid:'.$cid; |
| 1983 | } |
| 1984 | |
| 1985 | // The builder returns the full standalone HTML document (mockup |
| 1986 | // design with its own header/styling), so it is sent as-is — no extra |
| 1987 | // branded shell or greeting. |
| 1988 | $html = $this->build_weekly_commercial_report_html($regional, $individual, $series, $imgSrc); |
| 1989 | $content = $html; |
| 1990 | |
| 1991 | if ($preview) { |
| 1992 | return response($html)->header('Content-Type', 'text/html; charset=UTF-8'); |
| 1993 | } |
| 1994 | |
| 1995 | $email = new Mail; |
| 1996 | $email->setFrom('fire@fire.es', 'Fire Service Titan'); |
| 1997 | $email->setSubject($subject); |
| 1998 | $email->addTo($toEmail); |
| 1999 | $email->addContent('text/html', $html); |
| 2000 | |
| 2001 | // Inline chart images, referenced as cid: by the section-5 markup. |
| 2002 | // SendGrid base64-encodes attachment content itself, so raw PNG bytes |
| 2003 | // are what it expects here (same as the logo attachments elsewhere). |
| 2004 | foreach ($charts as $key => $png) { |
| 2005 | $email->addAttachment( |
| 2006 | $png, |
| 2007 | 'image/png', |
| 2008 | $chartCids[$key].'.png', |
| 2009 | 'inline', |
| 2010 | $chartCids[$key] |
| 2011 | ); |
| 2012 | } |
| 2013 | |
| 2014 | $sendgrid = new \SendGrid(config('services.sendgrid.api_key')); |
| 2015 | |
| 2016 | try { |
| 2017 | $response = $sendgrid->send($email); |
| 2018 | SendgridLogger::log($email, $response); |
| 2019 | } catch (\Throwable $sendException) { |
| 2020 | SendgridLogger::logException($email, $sendException); |
| 2021 | throw $sendException; |
| 2022 | } |
| 2023 | |
| 2024 | if ($response->statusCode() == 202) { |
| 2025 | Log::channel('email_log')->info('ID:'.$toEmail.' - WEEKLY COMMERCIAL REPORT EMAIL NOTIFICATION SENT'); |
| 2026 | |
| 2027 | TblNotifications::create([ |
| 2028 | 'user_id' => $toUserId, |
| 2029 | 'content' => $content, |
| 2030 | 'is_open' => 1, |
| 2031 | 'created_by' => 'System', |
| 2032 | ]); |
| 2033 | |
| 2034 | TblNotificationLogs::create([ |
| 2035 | 'commercial' => $toName, |
| 2036 | 'total_executive_report' => 1, |
| 2037 | 'created_by' => $sentBy, |
| 2038 | ]); |
| 2039 | } else { |
| 2040 | Log::channel('email_log')->error('ID:'.$toEmail.' - '.$response->body()); |
| 2041 | } |
| 2042 | |
| 2043 | return response(['message' => 'OK']); |
| 2044 | |
| 2045 | } catch (\Exception $e) { |
| 2046 | report(AppException::fromException($e, 'SEND_WEEKLY_COMMERCIAL_REPORT_EMAIL_EXCEPTION')); |
| 2047 | |
| 2048 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 2049 | } |
| 2050 | } |
| 2051 | |
| 2052 | /** |
| 2053 | * PART 1 data: per-region weekly + YTD figures for the current year, the |
| 2054 | * prior-year YTD comparison, the per-region annual € target, and Totals. |
| 2055 | */ |
| 2056 | public function get_weekly_regional_performance(?int $weekOverride = null, ?int $yearOverride = null): array |
| 2057 | { |
| 2058 | // Default anchor is today. When a valid ISO week (1..53) is supplied the |
| 2059 | // report is generated "as of" the Sunday of that week instead — so the |
| 2060 | // weekly columns, the month label, and the YTD cutoff all reflect that |
| 2061 | // chosen week rather than the current date. |
| 2062 | if ($weekOverride !== null && $weekOverride >= 1 && $weekOverride <= 53) { |
| 2063 | $anchorYear = $yearOverride ?? (int) date('Y'); |
| 2064 | $isoMonday = date('Y-m-d', strtotime($anchorYear.'-W'.sprintf('%02d', $weekOverride).'-1')); |
| 2065 | $now = date('Y-m-d', strtotime($isoMonday.' + 6 days')); // Sunday of the target week |
| 2066 | } else { |
| 2067 | $now = date('Y-m-d'); |
| 2068 | } |
| 2069 | $weekNumber = (int) date('W', strtotime($now)); |
| 2070 | $mondayDate = date('Y-m-d', strtotime($now.' - '.(date('N', strtotime($now)) - 1).' days')); |
| 2071 | $sundayDate = date('Y-m-d', strtotime($mondayDate.' + 6 days')); |
| 2072 | $currentYear = (int) date('Y', strtotime($now)); |
| 2073 | $previousYear = $currentYear - 1; |
| 2074 | // FIRE-1318 Bug 1: derive $currentMonth from $mondayDate (same source as |
| 2075 | // the header's $monthLabel below), NOT from date('Y-m'). Previously a |
| 2076 | // report sent on a Sunday of a week that spanned month-end (e.g. week 27 |
| 2077 | // = Mon 29/06 → Sun 05/07) labelled the column "Junio" (from the week's |
| 2078 | // Monday) but the SQL summed July's tiny start-of-month rows because |
| 2079 | // date('Y-m') resolved to '2026-07'. Label and data now agree. |
| 2080 | $currentMonth = date('Y-m', strtotime($mondayDate)); |
| 2081 | // Same calendar month one year earlier (e.g. 2025-06 vs 2026-06) so the |
| 2082 | // prior-year reference row's "Month" column compares like-for-like. The |
| 2083 | // previous-year query is filtered to YEAR = $previousYear, so passing |
| 2084 | // $currentMonth ('2026-06') here would never match and always yield 0. |
| 2085 | $previousMonth = $previousYear.'-'.date('m', strtotime($mondayDate)); |
| 2086 | // Same ISO week one year earlier (52 weeks = 364 days keeps the weekday) |
| 2087 | // so the prior-year row's weekly columns compare like-for-like instead of |
| 2088 | // always reading 0 — the current-year week dates never match last year. |
| 2089 | $prevMondayDate = date('Y-m-d', strtotime($mondayDate.' - 364 days')); |
| 2090 | $prevSundayDate = date('Y-m-d', strtotime($prevMondayDate.' + 6 days')); |
| 2091 | |
| 2092 | // Display order + label overrides (DB stores "Comunidad Valenciana"). |
| 2093 | // FIRE-1318 Duarte review (2026-07-10): pin every region that has a |
| 2094 | // target so they render in a stable order rather than falling back |
| 2095 | // to the "discovered from data" ordering below. |
| 2096 | $regionOrder = [ |
| 2097 | 'Madrid' => 'Madrid', |
| 2098 | 'Cataluña' => 'Cataluña', |
| 2099 | 'Comunidad Valenciana' => 'Valencia', |
| 2100 | 'Castellón' => 'Castellón', |
| 2101 | 'Baleares' => 'Baleares', |
| 2102 | 'Aragón' => 'Aragón', |
| 2103 | 'Andalucía' => 'Andalucía', |
| 2104 | 'Castilla-La Mancha' => 'Castilla-La Mancha', |
| 2105 | 'Castilla y León' => 'Castilla y León', |
| 2106 | ]; |
| 2107 | |
| 2108 | // FIRE-1318 Feature 3: YTD cutoffs. Current year caps at today; prior |
| 2109 | // year caps at same-day-last-year so the prev-YTD row compares |
| 2110 | // like-for-like (was a full-year figure before, which made "YoY YTD" |
| 2111 | // meaningless when the report ran mid-year). |
| 2112 | $currentYtdCutoff = $now; |
| 2113 | $previousYtdCutoff = date('Y-m-d', strtotime($now.' - 1 year')); |
| 2114 | |
| 2115 | $currentRows = $this->query_regional_metrics($currentYear, $mondayDate, $sundayDate, $currentMonth, $currentYtdCutoff); |
| 2116 | $previousRows = $this->query_regional_metrics($previousYear, $prevMondayDate, $prevSundayDate, $previousMonth, $previousYtdCutoff); |
| 2117 | |
| 2118 | // FIRE-1202: merge accepted aggregates from a parallel query that filters |
| 2119 | // by acceptance_date instead of issue_date. Without this, the email's |
| 2120 | // 'Aceptado' row would undercount because budgets issued in $prev_year |
| 2121 | // but accepted in $year would be missed. |
| 2122 | // |
| 2123 | // FIRE-1318 Feature 2: also run the accepted query for the prior year so |
| 2124 | // the 2025 row's YTD (% acept.) column has a value (mockup shows it |
| 2125 | // populated with the prior-year acceptance ratio). |
| 2126 | $currentAccepted = $this->query_regional_accepted_metrics($currentYear, $mondayDate, $sundayDate, $currentYtdCutoff, $currentMonth); |
| 2127 | foreach ($currentAccepted as $regionKey => $accRow) { |
| 2128 | if (! isset($currentRows[$regionKey])) { |
| 2129 | $currentRows[$regionKey] = $this->blank_region_metrics(); |
| 2130 | } |
| 2131 | foreach ($accRow as $k => $v) { |
| 2132 | $currentRows[$regionKey][$k] = $v; |
| 2133 | } |
| 2134 | } |
| 2135 | |
| 2136 | $previousAccepted = $this->query_regional_accepted_metrics($previousYear, $prevMondayDate, $prevSundayDate, $previousYtdCutoff, $previousMonth); |
| 2137 | foreach ($previousAccepted as $regionKey => $accRow) { |
| 2138 | if (! isset($previousRows[$regionKey])) { |
| 2139 | $previousRows[$regionKey] = $this->blank_region_metrics(); |
| 2140 | } |
| 2141 | foreach ($accRow as $k => $v) { |
| 2142 | $previousRows[$regionKey][$k] = $v; |
| 2143 | } |
| 2144 | } |
| 2145 | |
| 2146 | // FIRE-1318 Feature 4: per-region cumulative targets sourced from |
| 2147 | // tbl_executive_report_targets (seeded from Berta's Excel). Two lookups |
| 2148 | // per region: current-week cumulative → `Obj. YTD`, MAX(week_number) → |
| 2149 | // `Obj. anual`. Previously `Obj. YTD = annual_target × (week / 52)`, |
| 2150 | // which assumed a linear ramp; the Excel encodes month-weighted |
| 2151 | // cadence, so a mid-March week isn't at 3/12 = 25% of annual. |
| 2152 | $ytdTargets = DB::table('tbl_executive_report_targets') |
| 2153 | ->where('year', $currentYear) |
| 2154 | ->where('week_number', $weekNumber) |
| 2155 | ->where('kind', 'emitidos') |
| 2156 | ->pluck('cumulative_target', 'region'); |
| 2157 | |
| 2158 | $annualTargets = DB::table('tbl_executive_report_targets') |
| 2159 | ->where('year', $currentYear) |
| 2160 | ->where('kind', 'emitidos') |
| 2161 | ->select('region', DB::raw('MAX(cumulative_target) as annual')) |
| 2162 | ->groupBy('region') |
| 2163 | ->pluck('annual', 'region'); |
| 2164 | |
| 2165 | // FIRE-1318 Sergio review (2026-07-10): Aceptado targets from the same |
| 2166 | // Excel workbook's "Semanal - Aceptados" sheet. Seeded with kind='aceptados'; |
| 2167 | // rendered in the accepted row's Obj YTD / Obj anual / % Dif cells. |
| 2168 | $ytdTargetsAcc = DB::table('tbl_executive_report_targets') |
| 2169 | ->where('year', $currentYear) |
| 2170 | ->where('week_number', $weekNumber) |
| 2171 | ->where('kind', 'aceptados') |
| 2172 | ->pluck('cumulative_target', 'region'); |
| 2173 | |
| 2174 | $annualTargetsAcc = DB::table('tbl_executive_report_targets') |
| 2175 | ->where('year', $currentYear) |
| 2176 | ->where('kind', 'aceptados') |
| 2177 | ->select('region', DB::raw('MAX(cumulative_target) as annual')) |
| 2178 | ->groupBy('region') |
| 2179 | ->pluck('annual', 'region'); |
| 2180 | |
| 2181 | // Merge any region present in the data but missing from the display map. |
| 2182 | foreach (array_keys($currentRows + $previousRows + $annualTargets->all()) as $regionKey) { |
| 2183 | if (! isset($regionOrder[$regionKey])) { |
| 2184 | $regionOrder[$regionKey] = $regionKey; |
| 2185 | } |
| 2186 | } |
| 2187 | |
| 2188 | $regions = []; |
| 2189 | $totalsCurrent = $this->blank_region_metrics(); |
| 2190 | $totalsPrevious = $this->blank_region_metrics(); |
| 2191 | $totalAnnualTarget = 0.0; |
| 2192 | $totalYtdTarget = 0.0; |
| 2193 | // FIRE-1318 Sergio review: separate accumulators for the accepted-row |
| 2194 | // targets so the Total row's Aceptado gets its own Obj. YTD / anual / |
| 2195 | // % Dif — independent of the emitidos target totals. |
| 2196 | $totalAnnualTargetAcc = 0.0; |
| 2197 | $totalYtdTargetAcc = 0.0; |
| 2198 | |
| 2199 | foreach ($regionOrder as $regionKey => $label) { |
| 2200 | $cur = $currentRows[$regionKey] ?? $this->blank_region_metrics(); |
| 2201 | $prev = $previousRows[$regionKey] ?? $this->blank_region_metrics(); |
| 2202 | $annualTarget = (float) ($annualTargets[$regionKey] ?? 0.0); |
| 2203 | $ytdTarget = (float) ($ytdTargets[$regionKey] ?? 0.0); |
| 2204 | $annualTargetAcc = (float) ($annualTargetsAcc[$regionKey] ?? 0.0); |
| 2205 | $ytdTargetAcc = (float) ($ytdTargetsAcc[$regionKey] ?? 0.0); |
| 2206 | |
| 2207 | // Skip regions with no target AND no activity at all (keeps the |
| 2208 | // table to the operating regions). |
| 2209 | if ($annualTarget == 0 && $cur['ytd_budgets'] == 0 && $prev['ytd_budgets'] == 0) { |
| 2210 | continue; |
| 2211 | } |
| 2212 | |
| 2213 | $regions[] = [ |
| 2214 | 'label' => $label, |
| 2215 | // Canonical tbl_companies.region key (may differ from the |
| 2216 | // display label — e.g. 'Comunidad Valenciana' vs 'Valencia'). |
| 2217 | // Ref. 25 is keyed by this, not the label. |
| 2218 | 'region' => $regionKey, |
| 2219 | 'current' => $cur, |
| 2220 | 'previous' => $prev, |
| 2221 | 'annual_target' => $annualTarget, |
| 2222 | 'ytd_target' => $ytdTarget, |
| 2223 | 'diff_pct' => $this->ytd_diff_pct($cur['ytd_amount'], $ytdTarget), |
| 2224 | 'annual_target_acc' => $annualTargetAcc, |
| 2225 | 'ytd_target_acc' => $ytdTargetAcc, |
| 2226 | 'diff_pct_acc' => $this->ytd_diff_pct($cur['ytd_accepted_amount'], $ytdTargetAcc), |
| 2227 | ]; |
| 2228 | |
| 2229 | $totalsCurrent = $this->add_region_metrics($totalsCurrent, $cur); |
| 2230 | $totalsPrevious = $this->add_region_metrics($totalsPrevious, $prev); |
| 2231 | $totalAnnualTarget += $annualTarget; |
| 2232 | $totalYtdTarget += $ytdTarget; |
| 2233 | $totalAnnualTargetAcc += $annualTargetAcc; |
| 2234 | $totalYtdTargetAcc += $ytdTargetAcc; |
| 2235 | } |
| 2236 | |
| 2237 | return [ |
| 2238 | 'weekNumber' => $weekNumber, |
| 2239 | 'mondayDate' => $mondayDate, |
| 2240 | 'sundayDate' => $sundayDate, |
| 2241 | 'currentYear' => $currentYear, |
| 2242 | 'previousYear' => $previousYear, |
| 2243 | // Exposed so the year-by-week charts can window on exactly the same |
| 2244 | // cutoff sections 1-4 use. "The figures in the charts agree with the |
| 2245 | // figures shown elsewhere for the same week" is an explicit |
| 2246 | // acceptance criterion, and re-deriving this would let the two drift. |
| 2247 | 'ytdCutoff' => $currentYtdCutoff, |
| 2248 | 'regions' => $regions, |
| 2249 | 'totals' => [ |
| 2250 | 'current' => $totalsCurrent, |
| 2251 | 'previous' => $totalsPrevious, |
| 2252 | 'annual_target' => $totalAnnualTarget, |
| 2253 | 'ytd_target' => $totalYtdTarget, |
| 2254 | 'diff_pct' => $this->ytd_diff_pct($totalsCurrent['ytd_amount'], $totalYtdTarget), |
| 2255 | 'annual_target_acc' => $totalAnnualTargetAcc, |
| 2256 | 'ytd_target_acc' => $totalYtdTargetAcc, |
| 2257 | 'diff_pct_acc' => $this->ytd_diff_pct($totalsCurrent['ytd_accepted_amount'], $totalYtdTargetAcc), |
| 2258 | ], |
| 2259 | ]; |
| 2260 | } |
| 2261 | |
| 2262 | /** |
| 2263 | * Per-region conditional aggregation for one year. Returns a map keyed by |
| 2264 | * region with weekly, current-month and YTD figures (+ service splits). |
| 2265 | */ |
| 2266 | private function query_regional_metrics($year, $mondayDate, $sundayDate, $currentMonth, $ytdCutoff): array |
| 2267 | { |
| 2268 | // Never-sent quotes in WCR_NOT_ISSUED_UNTIL_SENT_STATUSES are not issued. |
| 2269 | $issued = self::wcrIssuedDateSql(); |
| 2270 | |
| 2271 | // FIRE-1202: align with /commercial-productivity (Quotations::list_quotation_analytics_commercial_productivity). |
| 2272 | // The page uses `q.issue_date` as its date axis — every "this week", "this |
| 2273 | // month", "this year" grouping on the page is by issue_date. The email |
| 2274 | // previously used `q.created_at`, so a budget created in Dec but issued |
| 2275 | // in Jan landed in different years on the two sides. Switching the |
| 2276 | // email's axis to issue_date (and dropping the YEAR(created)=YEAR(issue) |
| 2277 | // constraint that hid mixed-year budgets) reconciles the totals. |
| 2278 | // |
| 2279 | // Accepted aggregates live in `query_regional_accepted_metrics()` because |
| 2280 | // the page filters them by `YEAR(q.acceptance_date) = $year`, not by |
| 2281 | // issue year. Keeping them in this query would scope them to budgets |
| 2282 | // also issued in $year, dropping ~5% of acceptances at year boundaries. |
| 2283 | // |
| 2284 | // Issued/sent axis is `q.sent_date` (timestamp of the LAST send), matching |
| 2285 | // the Productividad page; issue_date stays the ERP budget date. |
| 2286 | $week = "DATE_FORMAT({$issued}, '%Y-%m-%d') BETWEEN '{$mondayDate}' AND '{$sundayDate}'"; |
| 2287 | |
| 2288 | $query = "SELECT |
| 2289 | c.region region, |
| 2290 | COUNT(CASE WHEN {$week} THEN 1 END) week_budgets, |
| 2291 | COALESCE(SUM(CASE WHEN {$week} THEN q.amount END), 0) week_amount, |
| 2292 | COALESCE(SUM(CASE WHEN {$week} AND bt.budget_type_group_id = 3 THEN q.amount END), 0) week_preventive, |
| 2293 | COALESCE(SUM(CASE WHEN {$week} AND bt.budget_type_group_id = 5 THEN q.amount END), 0) week_corrective, |
| 2294 | COALESCE(SUM(CASE WHEN {$week} AND bt.budget_type_group_id = 4 THEN q.amount END), 0) week_installation, |
| 2295 | -- FIRE-1202 follow-up (Berta, 2026-06-26): groups 6/7/8 (Pasivo / |
| 2296 | -- Otros / Venta) are collapsed into a single Otros column. |
| 2297 | COALESCE(SUM(CASE WHEN {$week} AND bt.budget_type_group_id IN (6, 7, 8) THEN q.amount END), 0) week_otros, |
| 2298 | COALESCE(SUM(CASE WHEN DATE_FORMAT({$issued}, '%Y-%m') = '{$currentMonth}' THEN q.amount END), 0) month_amount, |
| 2299 | -- FIRE-1448 v2: month_budgets parallel to month_amount so the |
| 2300 | -- Grupo block can render both # and € for the month cell. |
| 2301 | COUNT(CASE WHEN DATE_FORMAT({$issued}, '%Y-%m') = '{$currentMonth}' THEN 1 END) month_budgets, |
| 2302 | COUNT(*) ytd_budgets, |
| 2303 | COALESCE(SUM(q.amount), 0) ytd_amount, |
| 2304 | COALESCE(SUM(CASE WHEN bt.budget_type_group_id = 3 THEN q.amount END), 0) ytd_preventive, |
| 2305 | COALESCE(SUM(CASE WHEN bt.budget_type_group_id = 5 THEN q.amount END), 0) ytd_corrective, |
| 2306 | COALESCE(SUM(CASE WHEN bt.budget_type_group_id = 4 THEN q.amount END), 0) ytd_installation, |
| 2307 | COALESCE(SUM(CASE WHEN bt.budget_type_group_id IN (6, 7, 8) THEN q.amount END), 0) ytd_otros, |
| 2308 | -- Per-type YTD budget counts for Tabla 4 percent-mix-num |
| 2309 | -- (presupuestos del tipo / emitidos YTD). |
| 2310 | COUNT(CASE WHEN bt.budget_type_group_id = 3 THEN 1 END) ytd_preventive_n, |
| 2311 | COUNT(CASE WHEN bt.budget_type_group_id = 5 THEN 1 END) ytd_corrective_n, |
| 2312 | COUNT(CASE WHEN bt.budget_type_group_id = 4 THEN 1 END) ytd_installation_n, |
| 2313 | COUNT(CASE WHEN bt.budget_type_group_id IN (6, 7, 8) THEN 1 END) ytd_otros_n |
| 2314 | FROM tbl_quotations q |
| 2315 | JOIN tbl_companies c ON q.company_id = c.company_id |
| 2316 | LEFT JOIN tbl_budget_types bt ON q.budget_type_id = bt.budget_type_id |
| 2317 | WHERE c.is_send_executive = 1 |
| 2318 | AND c.region IS NOT NULL |
| 2319 | AND q.for_add = 0 |
| 2320 | AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1 |
| 2321 | AND {$issued} IS NOT NULL |
| 2322 | AND q.budget_type_id IS NOT NULL |
| 2323 | AND q.budget_type_id != 7 |
| 2324 | -- FIRE-1202: page (list_quotation_analytics_commercial_productivity) |
| 2325 | -- has `bt.include = 1` and a non-empty `q.commercial` guard plus |
| 2326 | -- the budget_type_id != 7 (Anulado) exclusion above. Mirror all |
| 2327 | -- three so the email's row totals match the page's for the same |
| 2328 | -- ISO week / year. |
| 2329 | AND bt.include = 1 |
| 2330 | AND q.commercial IS NOT NULL AND q.commercial != '' |
| 2331 | -- FIRE-1318 Feature 3: strict YTD windowing. Cap at |
| 2332 | -- `$ytdCutoff` (today for current year, same-day-last-year |
| 2333 | -- for prior year) so the prior-year row is a proper |
| 2334 | -- YTD-vs-YTD comparison. |
| 2335 | -- |
| 2336 | -- FIRE-1318 Sergio review (2026-07-10): year boundary |
| 2337 | -- must be ISO 8601 (Monday-adjusted), matching the |
| 2338 | -- Productividad Comercial page's outer grouping at |
| 2339 | -- Quotations::list_quotation_analytics_commercial_productivity |
| 2340 | -- (YEAR(DATE_ADD(q.issue_date, INTERVAL -WEEKDAY DAY))). |
| 2341 | -- Prior version used calendar `q.issue_date >= '{year}-01-01'` |
| 2342 | -- which counted the 4-5 Jan 1-4 days that ISO groups into |
| 2343 | -- the previous year; that drift explains the # deltas |
| 2344 | -- Sergio flagged (4521 vs 4501 on Madrid, etc.). |
| 2345 | AND YEAR(DATE_ADD({$issued}, INTERVAL - WEEKDAY({$issued}) DAY)) = {$year} |
| 2346 | AND DATE({$issued}) <= '{$ytdCutoff}' |
| 2347 | GROUP BY c.region"; |
| 2348 | |
| 2349 | $rows = []; |
| 2350 | foreach (DB::select($query) as $r) { |
| 2351 | $rows[$r->region] = [ |
| 2352 | 'week_budgets' => (int) $r->week_budgets, |
| 2353 | 'week_amount' => (float) $r->week_amount, |
| 2354 | 'week_preventive' => (float) $r->week_preventive, |
| 2355 | 'week_corrective' => (float) $r->week_corrective, |
| 2356 | 'week_installation' => (float) $r->week_installation, |
| 2357 | 'week_otros' => (float) $r->week_otros, |
| 2358 | 'month_amount' => (float) $r->month_amount, |
| 2359 | 'month_budgets' => (int) $r->month_budgets, |
| 2360 | 'ytd_budgets' => (int) $r->ytd_budgets, |
| 2361 | 'ytd_amount' => (float) $r->ytd_amount, |
| 2362 | 'ytd_preventive' => (float) $r->ytd_preventive, |
| 2363 | 'ytd_corrective' => (float) $r->ytd_corrective, |
| 2364 | 'ytd_installation' => (float) $r->ytd_installation, |
| 2365 | 'ytd_otros' => (float) $r->ytd_otros, |
| 2366 | 'ytd_preventive_n' => (int) $r->ytd_preventive_n, |
| 2367 | 'ytd_corrective_n' => (int) $r->ytd_corrective_n, |
| 2368 | 'ytd_installation_n' => (int) $r->ytd_installation_n, |
| 2369 | 'ytd_otros_n' => (int) $r->ytd_otros_n, |
| 2370 | // Accepted aggregates filled in by query_regional_accepted_metrics |
| 2371 | // (separate query, different WHERE — see method docblock). |
| 2372 | 'week_accepted_budgets' => 0, 'week_accepted_amount' => 0.0, |
| 2373 | 'week_accepted_preventive' => 0.0, 'week_accepted_corrective' => 0.0, 'week_accepted_installation' => 0.0, 'week_accepted_otros' => 0.0, |
| 2374 | 'month_accepted_amount' => 0.0, 'month_accepted_budgets' => 0, |
| 2375 | 'ytd_accepted_budgets' => 0, 'ytd_accepted_amount' => 0.0, |
| 2376 | 'ytd_accepted_preventive' => 0.0, 'ytd_accepted_corrective' => 0.0, 'ytd_accepted_installation' => 0.0, 'ytd_accepted_otros' => 0.0, |
| 2377 | 'ytd_accepted_preventive_n' => 0, 'ytd_accepted_corrective_n' => 0, 'ytd_accepted_installation_n' => 0, 'ytd_accepted_otros_n' => 0, |
| 2378 | ]; |
| 2379 | } |
| 2380 | |
| 2381 | return $rows; |
| 2382 | } |
| 2383 | |
| 2384 | /** |
| 2385 | * FIRE-1202: per-region accepted aggregates, filtered by `YEAR(q.acceptance_date) |
| 2386 | * = $year` instead of `YEAR(q.issue_date)`. The page (commercial-productivity) |
| 2387 | * counts a budget as "accepted in 2026" if its acceptance_date is in 2026, |
| 2388 | * regardless of when it was issued. Keeping this scope separate from |
| 2389 | * `query_regional_metrics` is necessary because the two share no filterable |
| 2390 | * WHERE clause (issue-year vs acceptance-year). |
| 2391 | * |
| 2392 | * Uses `q.acceptance_date` (the G3W canonical column), not `q.accepted_at` |
| 2393 | * (the Titan timestamp) — verified against the page's inner SELECT at |
| 2394 | * Quotations::list_quotation_analytics_commercial_productivity ~line 11806. |
| 2395 | */ |
| 2396 | private function query_regional_accepted_metrics($year, $mondayDate, $sundayDate, $ytdCutoff, $currentMonth = null): array |
| 2397 | { |
| 2398 | $acceptedThisWeek = "DATE(q.acceptance_date) BETWEEN '{$mondayDate}' AND '{$sundayDate}'"; |
| 2399 | // FIRE-1318: accepted-per-month column ("Junio" cell on the aceptado row |
| 2400 | // per Berta's mockup). Falls back to the year-month of $mondayDate when |
| 2401 | // caller omits it so behaviour matches the label logic elsewhere. |
| 2402 | $accMonth = $currentMonth ?? date('Y-m', strtotime($mondayDate)); |
| 2403 | |
| 2404 | $query = "SELECT |
| 2405 | c.region region, |
| 2406 | COUNT(CASE WHEN {$acceptedThisWeek} THEN 1 END) week_accepted_budgets, |
| 2407 | COALESCE(SUM(CASE WHEN {$acceptedThisWeek} THEN q.amount END), 0) week_accepted_amount, |
| 2408 | COALESCE(SUM(CASE WHEN {$acceptedThisWeek} AND bt.budget_type_group_id = 3 THEN q.amount END), 0) week_accepted_preventive, |
| 2409 | COALESCE(SUM(CASE WHEN {$acceptedThisWeek} AND bt.budget_type_group_id = 5 THEN q.amount END), 0) week_accepted_corrective, |
| 2410 | COALESCE(SUM(CASE WHEN {$acceptedThisWeek} AND bt.budget_type_group_id = 4 THEN q.amount END), 0) week_accepted_installation, |
| 2411 | COALESCE(SUM(CASE WHEN {$acceptedThisWeek} AND bt.budget_type_group_id IN (6, 7, 8) THEN q.amount END), 0) week_accepted_otros, |
| 2412 | COALESCE(SUM(CASE WHEN DATE_FORMAT(q.acceptance_date, '%Y-%m') = '{$accMonth}' THEN q.amount END), 0) month_accepted_amount, |
| 2413 | -- FIRE-1448 v2: month_accepted_budgets parallel to month_accepted_amount. |
| 2414 | COUNT(CASE WHEN DATE_FORMAT(q.acceptance_date, '%Y-%m') = '{$accMonth}' THEN 1 END) month_accepted_budgets, |
| 2415 | COUNT(*) ytd_accepted_budgets, |
| 2416 | COALESCE(SUM(q.amount), 0) ytd_accepted_amount, |
| 2417 | COALESCE(SUM(CASE WHEN bt.budget_type_group_id = 3 THEN q.amount END), 0) ytd_accepted_preventive, |
| 2418 | COALESCE(SUM(CASE WHEN bt.budget_type_group_id = 5 THEN q.amount END), 0) ytd_accepted_corrective, |
| 2419 | COALESCE(SUM(CASE WHEN bt.budget_type_group_id = 4 THEN q.amount END), 0) ytd_accepted_installation, |
| 2420 | COALESCE(SUM(CASE WHEN bt.budget_type_group_id IN (6, 7, 8) THEN q.amount END), 0) ytd_accepted_otros, |
| 2421 | -- Per-type YTD accepted counts for the aceptado row percent-mix-num. |
| 2422 | COUNT(CASE WHEN bt.budget_type_group_id = 3 THEN 1 END) ytd_accepted_preventive_n, |
| 2423 | COUNT(CASE WHEN bt.budget_type_group_id = 5 THEN 1 END) ytd_accepted_corrective_n, |
| 2424 | COUNT(CASE WHEN bt.budget_type_group_id = 4 THEN 1 END) ytd_accepted_installation_n, |
| 2425 | COUNT(CASE WHEN bt.budget_type_group_id IN (6, 7, 8) THEN 1 END) ytd_accepted_otros_n |
| 2426 | FROM tbl_quotations q |
| 2427 | JOIN tbl_companies c ON q.company_id = c.company_id |
| 2428 | LEFT JOIN tbl_budget_types bt ON q.budget_type_id = bt.budget_type_id |
| 2429 | WHERE c.is_send_executive = 1 |
| 2430 | AND c.region IS NOT NULL |
| 2431 | AND q.for_add = 0 |
| 2432 | AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1 |
| 2433 | AND q.acceptance_date IS NOT NULL |
| 2434 | AND q.budget_type_id IS NOT NULL |
| 2435 | AND q.budget_type_id != 7 |
| 2436 | AND bt.include = 1 |
| 2437 | AND q.commercial IS NOT NULL AND q.commercial != '' |
| 2438 | -- FIRE-1318 Feature 3 + Sergio review (2026-07-10): ISO 8601 |
| 2439 | -- year boundary on acceptance_date, matching the page's |
| 2440 | -- outer grouping. See query_regional_metrics for rationale. |
| 2441 | AND YEAR(DATE_ADD(q.acceptance_date, INTERVAL - WEEKDAY(q.acceptance_date) DAY)) = {$year} |
| 2442 | AND q.acceptance_date <= '{$ytdCutoff}' |
| 2443 | GROUP BY c.region"; |
| 2444 | |
| 2445 | $rows = []; |
| 2446 | foreach (DB::select($query) as $r) { |
| 2447 | $rows[$r->region] = [ |
| 2448 | 'week_accepted_budgets' => (int) $r->week_accepted_budgets, |
| 2449 | 'week_accepted_amount' => (float) $r->week_accepted_amount, |
| 2450 | 'week_accepted_preventive' => (float) $r->week_accepted_preventive, |
| 2451 | 'week_accepted_corrective' => (float) $r->week_accepted_corrective, |
| 2452 | 'week_accepted_installation' => (float) $r->week_accepted_installation, |
| 2453 | 'week_accepted_otros' => (float) $r->week_accepted_otros, |
| 2454 | 'month_accepted_amount' => (float) $r->month_accepted_amount, |
| 2455 | 'month_accepted_budgets' => (int) $r->month_accepted_budgets, |
| 2456 | 'ytd_accepted_budgets' => (int) $r->ytd_accepted_budgets, |
| 2457 | 'ytd_accepted_amount' => (float) $r->ytd_accepted_amount, |
| 2458 | 'ytd_accepted_preventive' => (float) $r->ytd_accepted_preventive, |
| 2459 | 'ytd_accepted_corrective' => (float) $r->ytd_accepted_corrective, |
| 2460 | 'ytd_accepted_installation' => (float) $r->ytd_accepted_installation, |
| 2461 | 'ytd_accepted_otros' => (float) $r->ytd_accepted_otros, |
| 2462 | 'ytd_accepted_preventive_n' => (int) $r->ytd_accepted_preventive_n, |
| 2463 | 'ytd_accepted_corrective_n' => (int) $r->ytd_accepted_corrective_n, |
| 2464 | 'ytd_accepted_installation_n' => (int) $r->ytd_accepted_installation_n, |
| 2465 | 'ytd_accepted_otros_n' => (int) $r->ytd_accepted_otros_n, |
| 2466 | ]; |
| 2467 | } |
| 2468 | |
| 2469 | return $rows; |
| 2470 | } |
| 2471 | |
| 2472 | private function blank_region_metrics(): array |
| 2473 | { |
| 2474 | return [ |
| 2475 | 'week_budgets' => 0, 'week_amount' => 0.0, |
| 2476 | 'week_preventive' => 0.0, 'week_corrective' => 0.0, 'week_installation' => 0.0, 'week_otros' => 0.0, |
| 2477 | // FIRE-1448 v2: month_budgets is the # parallel to month_amount, so the |
| 2478 | // group block can render both € and # aceptados / emitidos per month. |
| 2479 | 'month_amount' => 0.0, 'month_budgets' => 0, |
| 2480 | 'ytd_budgets' => 0, 'ytd_amount' => 0.0, |
| 2481 | 'ytd_preventive' => 0.0, 'ytd_corrective' => 0.0, 'ytd_installation' => 0.0, 'ytd_otros' => 0.0, |
| 2482 | 'ytd_preventive_n' => 0, 'ytd_corrective_n' => 0, 'ytd_installation_n' => 0, 'ytd_otros_n' => 0, |
| 2483 | 'week_accepted_budgets' => 0, 'week_accepted_amount' => 0.0, |
| 2484 | 'week_accepted_preventive' => 0.0, 'week_accepted_corrective' => 0.0, 'week_accepted_installation' => 0.0, 'week_accepted_otros' => 0.0, |
| 2485 | 'month_accepted_amount' => 0.0, 'month_accepted_budgets' => 0, |
| 2486 | 'ytd_accepted_budgets' => 0, 'ytd_accepted_amount' => 0.0, |
| 2487 | 'ytd_accepted_preventive' => 0.0, 'ytd_accepted_corrective' => 0.0, 'ytd_accepted_installation' => 0.0, 'ytd_accepted_otros' => 0.0, |
| 2488 | 'ytd_accepted_preventive_n' => 0, 'ytd_accepted_corrective_n' => 0, 'ytd_accepted_installation_n' => 0, 'ytd_accepted_otros_n' => 0, |
| 2489 | ]; |
| 2490 | } |
| 2491 | |
| 2492 | private function add_region_metrics(array $acc, array $row): array |
| 2493 | { |
| 2494 | foreach ($acc as $k => $v) { |
| 2495 | $acc[$k] = $v + $row[$k]; |
| 2496 | } |
| 2497 | |
| 2498 | return $acc; |
| 2499 | } |
| 2500 | |
| 2501 | private function ytd_diff_pct($ytdAmount, $ytdTarget) |
| 2502 | { |
| 2503 | if ($ytdTarget <= 0) { |
| 2504 | return null; // no target set → no pace comparison |
| 2505 | } |
| 2506 | |
| 2507 | return (($ytdAmount - $ytdTarget) / $ytdTarget) * 100; |
| 2508 | } |
| 2509 | |
| 2510 | /** |
| 2511 | * PART 2 data: aggregate the current week by commercial (restricted to |
| 2512 | * is_commercial users in "send executive" companies). Returns top-3 by |
| 2513 | * value and by volume, plus the below-weekly-target lists. |
| 2514 | */ |
| 2515 | public function get_weekly_individual_performance($mondayDate, $sundayDate): array |
| 2516 | { |
| 2517 | // Never-sent quotes in WCR_NOT_ISSUED_UNTIL_SENT_STATUSES are not issued. |
| 2518 | $issued = self::wcrIssuedDateSql(); |
| 2519 | |
| 2520 | $currentYear = (int) date('Y'); |
| 2521 | |
| 2522 | // FIRE-1202 follow-up (Berta, 2026-06-26): roster sourced from tbl_users + |
| 2523 | // tbl_company_users instead of tbl_quotations. Every is_commercial=1 user |
| 2524 | // assigned to an is_send_executive=1 company now appears, even with zero |
| 2525 | // budgets this year. Previously the roster required at least one same- |
| 2526 | // year-issued budget, which dropped specialists Berta needed to see |
| 2527 | // (Carlos Alonso, Marta Valderrama, Carlos Javier Ortega, Esmeralda |
| 2528 | // Robles). The downstream weekly aggregates still key off |
| 2529 | // `q.commercial = u.name`, so budget-by-budget attribution is unchanged. |
| 2530 | // FIRE-1448 Sub-task 4 (Luis Reyes review, 2026-07-12): the roster |
| 2531 | // used to accept any tbl_users.is_commercial=1 row, which pulled in |
| 2532 | // partner-company users (Oasys, Sat Valles) and ex-employees whose |
| 2533 | // role was `Regular`. Restrict to the two "real commercial" roles |
| 2534 | // (WCR_ALLOWED_ROLES) plus the "comercial adyacente" flag for |
| 2535 | // Iker-style exceptions Luis wants included. |
| 2536 | $allowedRolesSql = "'".implode("', '", array_map(fn ($r) => addslashes($r), self::WCR_ALLOWED_ROLES))."'"; |
| 2537 | $roster = []; |
| 2538 | $rosterQuery = "SELECT u.name commercial, MAX(c.region) region, MAX(rl.name) role |
| 2539 | FROM tbl_users u |
| 2540 | JOIN tbl_company_users cu ON cu.user_id = u.id |
| 2541 | JOIN tbl_companies c ON cu.company_id = c.company_id |
| 2542 | LEFT JOIN tbl_roles rl ON u.role_id = rl.role_id |
| 2543 | WHERE u.is_commercial = 1 |
| 2544 | AND c.is_send_executive = 1 |
| 2545 | AND c.region IS NOT NULL |
| 2546 | AND u.name IS NOT NULL AND u.name != '' |
| 2547 | AND (rl.name IN ({$allowedRolesSql}) OR u.is_commercial_adyacente = 1) |
| 2548 | GROUP BY u.name"; |
| 2549 | foreach (DB::select($rosterQuery) as $r) { |
| 2550 | $roster[$r->commercial] = $this->blank_commercial_metrics($r->commercial, $r->region, $r->role); |
| 2551 | } |
| 2552 | |
| 2553 | // Current-week aggregates per commercial. |
| 2554 | $weekQuery = "SELECT |
| 2555 | q.commercial commercial, |
| 2556 | MAX(c.region) region, |
| 2557 | MAX(rl.name) role, |
| 2558 | COUNT(*) n_budgets, |
| 2559 | COALESCE(SUM(q.amount), 0) total_amount, |
| 2560 | COALESCE(SUM(CASE WHEN bt.budget_type_group_id = 3 THEN q.amount END), 0) preventive, |
| 2561 | COALESCE(SUM(CASE WHEN bt.budget_type_group_id = 5 THEN q.amount END), 0) corrective, |
| 2562 | COALESCE(SUM(CASE WHEN bt.budget_type_group_id = 4 THEN q.amount END), 0) installation |
| 2563 | FROM tbl_quotations q |
| 2564 | JOIN tbl_companies c ON q.company_id = c.company_id |
| 2565 | LEFT JOIN tbl_budget_types bt ON q.budget_type_id = bt.budget_type_id |
| 2566 | JOIN tbl_users u ON u.name = q.commercial AND u.is_commercial = 1 |
| 2567 | LEFT JOIN tbl_roles rl ON u.role_id = rl.role_id |
| 2568 | WHERE c.is_send_executive = 1 |
| 2569 | AND q.for_add != 1 |
| 2570 | AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1 |
| 2571 | AND DATE({$issued}) BETWEEN '{$mondayDate}' AND '{$sundayDate}' |
| 2572 | AND q.budget_type_id != 7 |
| 2573 | AND q.commercial IS NOT NULL AND q.commercial != '' |
| 2574 | GROUP BY q.commercial"; |
| 2575 | foreach (DB::select($weekQuery) as $r) { |
| 2576 | // FIRE-1448 Sub-task 4: only overwrite roster entries the seed |
| 2577 | // query already produced. A user filtered out by the role |
| 2578 | // allowlist above shouldn't reappear here just because they |
| 2579 | // issued a budget this week — that's exactly the case Luis |
| 2580 | // called out (Oasys/Sat Valles submitting occasional budgets). |
| 2581 | if (! isset($roster[$r->commercial])) { |
| 2582 | continue; |
| 2583 | } |
| 2584 | $roster[$r->commercial] = [ |
| 2585 | 'name' => $r->commercial, |
| 2586 | 'region' => $r->region, |
| 2587 | 'role' => $r->role, |
| 2588 | 'n_budgets' => (int) $r->n_budgets, |
| 2589 | 'total_amount' => (float) $r->total_amount, |
| 2590 | 'preventive' => (float) $r->preventive, |
| 2591 | 'corrective' => (float) $r->corrective, |
| 2592 | 'installation' => (float) $r->installation, |
| 2593 | ]; |
| 2594 | } |
| 2595 | |
| 2596 | // Per-person weekly EMITIDO € objectives (Section 5 "Performance |
| 2597 | // individual"). The FTE sheet stores CUMULATIVE targets per ISO week, so |
| 2598 | // the weekly target is cumulative(week) - cumulative(week-1) (or |
| 2599 | // cumulative(1) in week 1). Year/week are derived from the Monday of the |
| 2600 | // reporting window — the same source the regional method uses — so a |
| 2601 | // report spanning a year boundary uses the week's own ISO year rather |
| 2602 | // than today's calendar year. |
| 2603 | $targetYear = (int) date('Y', strtotime($mondayDate)); |
| 2604 | $weekNumber = (int) date('W', strtotime($mondayDate)); |
| 2605 | $prevWeek = $weekNumber - 1; |
| 2606 | $weeksToPull = $weekNumber === 1 ? [1] : [$weekNumber, $prevWeek]; |
| 2607 | // Single query pulls this week + the prior week for every person, keyed |
| 2608 | // by name → [week => cumulative], so we never fire N per-person queries. |
| 2609 | $cumByName = []; |
| 2610 | $targetRows = DB::table('tbl_executive_report_fte_targets') |
| 2611 | ->select('commercial_name', 'week_number', 'cumulative_target') |
| 2612 | ->where('year', $targetYear) |
| 2613 | ->where('kind', 'emitidos') |
| 2614 | ->whereIn('week_number', $weeksToPull) |
| 2615 | ->get(); |
| 2616 | foreach ($targetRows as $tr) { |
| 2617 | $cumByName[$tr->commercial_name][(int) $tr->week_number] = (float) $tr->cumulative_target; |
| 2618 | } |
| 2619 | foreach ($roster as $name => &$row) { |
| 2620 | // Exact-name match only (MySQL collation is accent-insensitive). No |
| 2621 | // row for this person/week → NULL, and the value table falls back to |
| 2622 | // WCR_TARGET_VALUE. The sheet has NO per-# target, so the volume |
| 2623 | // table stays on the WCR_TARGET_VOLUME constant (see below). |
| 2624 | $target = null; |
| 2625 | if (isset($cumByName[$name][$weekNumber])) { |
| 2626 | $curr = $cumByName[$name][$weekNumber]; |
| 2627 | $target = $weekNumber === 1 |
| 2628 | ? $curr |
| 2629 | : $curr - ($cumByName[$name][$prevWeek] ?? 0.0); |
| 2630 | } |
| 2631 | $row['target_value'] = $target; |
| 2632 | } |
| 2633 | unset($row); |
| 2634 | |
| 2635 | // Consecutive weeks hitting the weekly objective. Counted back from the |
| 2636 | // last *completed* week — the report goes out before the reporting week |
| 2637 | // ends, so including it would break everybody's streak every Saturday. |
| 2638 | // Both polarities: the Top-3 tables show weeks met, the bajo-objetivo |
| 2639 | // tables show weeks NOT met (they are negative tables). |
| 2640 | $streaks = $this->get_weekly_streaks($targetYear, $weekNumber - 1, $roster); |
| 2641 | foreach ($roster as $name => &$row) { |
| 2642 | $row['weeks_met'] = $streaks[$name]['met'] ?? null; |
| 2643 | $row['weeks_missed'] = $streaks[$name]['missed'] ?? null; |
| 2644 | } |
| 2645 | unset($row); |
| 2646 | |
| 2647 | $all = array_values($roster); |
| 2648 | |
| 2649 | // 2026-07-27: all four Section-5 tables are now split by tbl_roles.name — |
| 2650 | // left column = "Asesor de clientes" (judged on € issued), right column = |
| 2651 | // "Sales specialist" (judged on # budgets issued). Before this change only |
| 2652 | // the two bajo-objetivo tables were role-split; the Top 3 cards ranked |
| 2653 | // both roles together, so specialists (many small budgets) and asesores |
| 2654 | // (fewer, larger ones) competed on the same scale and the volume podium |
| 2655 | // was permanently specialist-only anyway. |
| 2656 | // |
| 2657 | // The asesor side is "not a Sales specialist" rather than a strict |
| 2658 | // 'Asesor de clientes' string match so the is_commercial_adyacente |
| 2659 | // exceptions from the roster (Iker et al., whose primary role is neither |
| 2660 | // of WCR_ALLOWED_ROLES) keep showing up — they're judged on € like the |
| 2661 | // asesores. This is the same predicate the old value table used. |
| 2662 | $isSpecialist = fn ($x) => strcasecmp(trim((string) ($x['role'] ?? '')), self::WCR_SPECIALIST_ROLE) === 0; |
| 2663 | $advisors = array_values(array_filter($all, fn ($x) => ! $isSpecialist($x))); |
| 2664 | $specialists = array_values(array_filter($all, $isSpecialist)); |
| 2665 | |
| 2666 | // Top 3 — only people with activity this week, ranked inside their own |
| 2667 | // role: asesores by € issued, specialists by # budgets issued. |
| 2668 | $topAdvisors = array_values(array_filter($advisors, fn ($x) => $x['n_budgets'] > 0)); |
| 2669 | usort($topAdvisors, fn ($a, $b) => $b['total_amount'] <=> $a['total_amount']); |
| 2670 | $topAdvisors = array_slice($topAdvisors, 0, 3); |
| 2671 | |
| 2672 | $topSpecialists = array_values(array_filter($specialists, fn ($x) => $x['n_budgets'] > 0)); |
| 2673 | usort($topSpecialists, fn ($a, $b) => $b['n_budgets'] <=> $a['n_budgets']); |
| 2674 | $topSpecialists = array_slice($topSpecialists, 0, 3); |
| 2675 | |
| 2676 | // Below weekly objective — across the full roster (people with zero |
| 2677 | // budgets this week included, they're the ones furthest below). |
| 2678 | // |
| 2679 | // Asesores: judge each against their per-person weekly € objective when |
| 2680 | // the FTE sheet provides one ($x['target_value']); otherwise fall back to |
| 2681 | // the WCR_TARGET_VALUE constant (unchanged). |
| 2682 | $belowAdvisors = array_values(array_filter($advisors, fn ($x) => $x['total_amount'] < ($x['target_value'] ?? self::WCR_TARGET_VALUE))); |
| 2683 | usort($belowAdvisors, fn ($a, $b) => $a['total_amount'] <=> $b['total_amount']); |
| 2684 | |
| 2685 | // Specialists: left on the WCR_TARGET_VOLUME constant — the objectives |
| 2686 | // sheet only provides per-€ FTE targets, not a per-# budget target. |
| 2687 | $belowSpecialists = array_values(array_filter($specialists, fn ($x) => $x['n_budgets'] < self::WCR_TARGET_VOLUME)); |
| 2688 | usort($belowSpecialists, fn ($a, $b) => $a['n_budgets'] <=> $b['n_budgets']); |
| 2689 | |
| 2690 | return [ |
| 2691 | 'topAdvisors' => $topAdvisors, |
| 2692 | 'topSpecialists' => $topSpecialists, |
| 2693 | 'belowAdvisors' => $belowAdvisors, |
| 2694 | 'belowSpecialists' => $belowSpecialists, |
| 2695 | ]; |
| 2696 | } |
| 2697 | |
| 2698 | private function blank_commercial_metrics($name, $region = null, $role = null): array |
| 2699 | { |
| 2700 | return [ |
| 2701 | 'name' => $name, |
| 2702 | 'region' => $region, |
| 2703 | 'role' => $role, |
| 2704 | 'n_budgets' => 0, |
| 2705 | 'total_amount' => 0.0, |
| 2706 | 'preventive' => 0.0, |
| 2707 | 'corrective' => 0.0, |
| 2708 | 'installation' => 0.0, |
| 2709 | // Consecutive weeks hitting the weekly objective; null = never hit |
| 2710 | // one yet, rendered as a dash rather than a zero. |
| 2711 | // Weeks meeting / not meeting the weekly objective this year. null = |
| 2712 | // no eligible week of that kind yet, rendered as a dash. |
| 2713 | 'weeks_met' => null, |
| 2714 | 'weeks_missed' => null, |
| 2715 | ]; |
| 2716 | } |
| 2717 | |
| 2718 | /** |
| 2719 | * Year-by-week data for the four trend charts: one point per ISO week of |
| 2720 | * $isoYear up to and including $upToWeek. |
| 2721 | * |
| 2722 | * The WHERE clauses are deliberate copies of `query_regional_metrics` and |
| 2723 | * `query_regional_accepted_metrics` — same joins, same exclusions |
| 2724 | * (`for_add`, `budget_type_id != 7`, `bt.include = 1`, non-empty |
| 2725 | * `commercial`), same ISO-8601 year boundary and same YTD cutoff. A week's |
| 2726 | * point therefore equals the sum of that week's per-region figures in |
| 2727 | * sections 1-4, which is an explicit acceptance criterion. Change a filter |
| 2728 | * there and it must change here too. |
| 2729 | * |
| 2730 | * Emitido is keyed on `sent_date`, aceptado on `acceptance_date`, matching |
| 2731 | * the split the two source methods already make (FIRE-1202). |
| 2732 | * |
| 2733 | * @param bool $lastWeekOpen true when $upToWeek has not finished yet |
| 2734 | */ |
| 2735 | public function get_weekly_year_series(int $isoYear, int $upToWeek, string $ytdCutoff, bool $lastWeekOpen): array |
| 2736 | { |
| 2737 | // Never-sent quotes in WCR_NOT_ISSUED_UNTIL_SENT_STATUSES are not issued. |
| 2738 | $issued = self::wcrIssuedDateSql(); |
| 2739 | |
| 2740 | $cacheKey = 'series:'.$isoYear.':'.$upToWeek.':'.$ytdCutoff.':'.($lastWeekOpen ? 1 : 0); |
| 2741 | if (isset($this->wcrYearCache[$cacheKey])) { |
| 2742 | return $this->wcrYearCache[$cacheKey]; |
| 2743 | } |
| 2744 | |
| 2745 | // WEEK(d, 3) is ISO-8601 (Monday-based, 1..53) — the same numbering |
| 2746 | // PHP's date('W') produces, which is what $weekNumber upstream uses. |
| 2747 | $emitidoSql = "SELECT WEEK({$issued}, 3) wk, COUNT(*) n, COALESCE(SUM(q.amount), 0) eur |
| 2748 | FROM tbl_quotations q |
| 2749 | JOIN tbl_companies c ON q.company_id = c.company_id |
| 2750 | LEFT JOIN tbl_budget_types bt ON q.budget_type_id = bt.budget_type_id |
| 2751 | WHERE c.is_send_executive = 1 |
| 2752 | AND c.region IS NOT NULL |
| 2753 | AND q.for_add = 0 |
| 2754 | AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1 |
| 2755 | AND {$issued} IS NOT NULL |
| 2756 | AND q.budget_type_id IS NOT NULL |
| 2757 | AND q.budget_type_id != 7 |
| 2758 | AND bt.include = 1 |
| 2759 | AND q.commercial IS NOT NULL AND q.commercial != '' |
| 2760 | AND YEAR(DATE_ADD({$issued}, INTERVAL - WEEKDAY({$issued}) DAY)) = {$isoYear} |
| 2761 | AND DATE({$issued}) <= '{$ytdCutoff}' |
| 2762 | GROUP BY wk"; |
| 2763 | |
| 2764 | $aceptadoSql = "SELECT WEEK(q.acceptance_date, 3) wk, COUNT(*) n, COALESCE(SUM(q.amount), 0) eur |
| 2765 | FROM tbl_quotations q |
| 2766 | JOIN tbl_companies c ON q.company_id = c.company_id |
| 2767 | LEFT JOIN tbl_budget_types bt ON q.budget_type_id = bt.budget_type_id |
| 2768 | WHERE c.is_send_executive = 1 |
| 2769 | AND c.region IS NOT NULL |
| 2770 | AND q.for_add = 0 |
| 2771 | AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1 |
| 2772 | AND q.acceptance_date IS NOT NULL |
| 2773 | AND q.budget_type_id IS NOT NULL |
| 2774 | AND q.budget_type_id != 7 |
| 2775 | AND bt.include = 1 |
| 2776 | AND q.commercial IS NOT NULL AND q.commercial != '' |
| 2777 | AND YEAR(DATE_ADD(q.acceptance_date, INTERVAL - WEEKDAY(q.acceptance_date) DAY)) = {$isoYear} |
| 2778 | AND q.acceptance_date <= '{$ytdCutoff}' |
| 2779 | GROUP BY wk"; |
| 2780 | |
| 2781 | $byWeek = ['emitido' => [], 'aceptado' => []]; |
| 2782 | foreach (DB::select($emitidoSql) as $r) { |
| 2783 | $byWeek['emitido'][(int) $r->wk] = ['n' => (int) $r->n, 'eur' => (float) $r->eur]; |
| 2784 | } |
| 2785 | foreach (DB::select($aceptadoSql) as $r) { |
| 2786 | $byWeek['aceptado'][(int) $r->wk] = ['n' => (int) $r->n, 'eur' => (float) $r->eur]; |
| 2787 | } |
| 2788 | |
| 2789 | $notes = $this->get_week_notes($isoYear); |
| 2790 | |
| 2791 | // Where the year starts for this report. |
| 2792 | // |
| 2793 | // The WHERE above attributes a week to the year its MONDAY falls in (the |
| 2794 | // FIRE-1318 convention, inherited from the Productividad Comercial page). |
| 2795 | // ISO week 1 of 2026 begins Mon 2025-12-29, so under that rule the whole |
| 2796 | // week belongs to 2025 and the first week of 2026 is the one starting Mon |
| 2797 | // 05/01 — which WEEK(d, 3) numbers as 2. |
| 2798 | // |
| 2799 | // Iterating from a hardcoded week 1 therefore invented an always-empty |
| 2800 | // week that rendered as a plunge to zero and a −100% label. Deriving the |
| 2801 | // first week from the same convention keeps the charts consistent with |
| 2802 | // sections 1-4 instead of mixing two definitions of "week 1". |
| 2803 | $jan1 = strtotime($isoYear.'-01-01'); |
| 2804 | $mondayOfJan1 = strtotime('-'.((int) date('N', $jan1) - 1).' days', $jan1); |
| 2805 | if ((int) date('Y', $mondayOfJan1) < $isoYear) { |
| 2806 | $mondayOfJan1 = strtotime('+7 days', $mondayOfJan1); |
| 2807 | } |
| 2808 | $startWeek = (int) date('W', $mondayOfJan1); |
| 2809 | |
| 2810 | $out = []; |
| 2811 | foreach (self::WCR_CHART_SERIES as $key => $meta) { |
| 2812 | [$source, $field] = explode('_', $key); |
| 2813 | $field = $field === 'eur' ? 'eur' : 'n'; |
| 2814 | |
| 2815 | // Weeks with no rows are real zeros, not gaps — a week where nothing |
| 2816 | // was issued is information, so the line must dip rather than skip. |
| 2817 | $values = []; |
| 2818 | for ($w = $startWeek; $w <= $upToWeek; $w++) { |
| 2819 | $values[$w] = (float) ($byWeek[$source][$w][$field] ?? 0); |
| 2820 | } |
| 2821 | |
| 2822 | // The average is "what normal looks like", so it is taken over |
| 2823 | // completed weeks only. Including a half-finished week would drag the |
| 2824 | // reference line down and make every other week look better than it is. |
| 2825 | $closed = $lastWeekOpen ? array_slice($values, 0, -1, true) : $values; |
| 2826 | $average = count($closed) > 0 ? array_sum($closed) / count($closed) : 0.0; |
| 2827 | |
| 2828 | // A note renders as the caption on that week's shaded band, and again |
| 2829 | // in full underneath the chart (the band has ~16 characters of room). |
| 2830 | $chartNotes = []; |
| 2831 | |
| 2832 | $points = []; |
| 2833 | foreach ($values as $w => $v) { |
| 2834 | $isOpen = $lastWeekOpen && $w === $upToWeek; |
| 2835 | |
| 2836 | $note = $notes[$w][$key] ?? $notes[$w]['all'] ?? null; |
| 2837 | if ($note !== null) { |
| 2838 | $chartNotes[] = ['week' => $w, 'note' => $note]; |
| 2839 | } |
| 2840 | |
| 2841 | $points[] = [ |
| 2842 | 'week' => $w, |
| 2843 | 'value' => $v, |
| 2844 | // No variance label on the open week. It is only part-way |
| 2845 | // through, so a "−48%" there would read as a collapse when it |
| 2846 | // is just an unfinished week — the ticket calls this out |
| 2847 | // explicitly. |
| 2848 | 'pct' => ($isOpen || $average <= 0) ? null : (($v - $average) / $average) * 100, |
| 2849 | 'in_progress' => $isOpen, |
| 2850 | 'note' => $note, |
| 2851 | ]; |
| 2852 | } |
| 2853 | |
| 2854 | $out[$key] = [ |
| 2855 | 'title' => $meta['title'], |
| 2856 | 'kind' => $meta['kind'], |
| 2857 | 'points' => $points, |
| 2858 | 'average' => $average, |
| 2859 | 'notes' => $chartNotes, |
| 2860 | ]; |
| 2861 | } |
| 2862 | |
| 2863 | $this->wcrYearCache[$cacheKey] = $out; |
| 2864 | |
| 2865 | return $out; |
| 2866 | } |
| 2867 | |
| 2868 | /** |
| 2869 | * Active per-week annotations for $year, as [week => [scope => note]]. |
| 2870 | * Missing table (migration not yet run on this environment) degrades to no |
| 2871 | * notes rather than breaking the whole report. |
| 2872 | */ |
| 2873 | private function get_week_notes(int $year): array |
| 2874 | { |
| 2875 | try { |
| 2876 | $rows = DB::table('tbl_executive_report_week_notes') |
| 2877 | ->where('year', $year) |
| 2878 | ->where('is_active', 1) |
| 2879 | ->orderBy('week_number') |
| 2880 | ->get(); |
| 2881 | } catch (\Throwable $e) { |
| 2882 | Log::channel('cron_send_executive_report')->warning('Week notes unavailable: '.$e->getMessage()); |
| 2883 | |
| 2884 | return []; |
| 2885 | } |
| 2886 | |
| 2887 | $notes = []; |
| 2888 | foreach ($rows as $r) { |
| 2889 | $notes[(int) $r->week_number][(string) $r->chart_scope] = (string) $r->note; |
| 2890 | } |
| 2891 | |
| 2892 | return $notes; |
| 2893 | } |
| 2894 | |
| 2895 | /** |
| 2896 | * How many weeks in a row each commercial has hit their weekly objective, |
| 2897 | * counting back from the last completed week. |
| 2898 | * |
| 2899 | * Measured against the objective that already applies to the role: Sales |
| 2900 | * specialists on the number of budgets issued (WCR_TARGET_VOLUME), everyone |
| 2901 | * else on the value issued (their FTE-sheet weekly target, falling back to |
| 2902 | * WCR_TARGET_VALUE). That is the same pairing the bajo-objetivo tables use, |
| 2903 | * so a person's streak and their weekly row cannot disagree about whether |
| 2904 | * this week counted. |
| 2905 | * |
| 2906 | * Weekly figures are keyed on `created_at`, matching |
| 2907 | * `get_weekly_individual_performance` rather than the `sent_date` axis of |
| 2908 | * sections 1-4 — the streak has to agree with the number printed next to the |
| 2909 | * person's name, not with the regional tables. |
| 2910 | * |
| 2911 | * @param array $roster name => metrics row (needs 'role') |
| 2912 | * @return array name => int|null (null = no streak yet → rendered as a dash) |
| 2913 | */ |
| 2914 | private function get_weekly_streaks(int $isoYear, int $throughWeek, array $roster): array |
| 2915 | { |
| 2916 | // Never-sent quotes in WCR_NOT_ISSUED_UNTIL_SENT_STATUSES are not issued. |
| 2917 | $issued = self::wcrIssuedDateSql(); |
| 2918 | |
| 2919 | if ($roster === [] || $throughWeek < 1) { |
| 2920 | return []; |
| 2921 | } |
| 2922 | |
| 2923 | $names = array_keys($roster); |
| 2924 | $cacheKey = 'streaks:'.$isoYear.':'.$throughWeek.':'.md5(implode('|', $names)); |
| 2925 | if (isset($this->wcrYearCache[$cacheKey])) { |
| 2926 | return $this->wcrYearCache[$cacheKey]; |
| 2927 | } |
| 2928 | |
| 2929 | $inNames = "'".implode("', '", array_map(fn ($n) => addslashes((string) $n), $names))."'"; |
| 2930 | |
| 2931 | // Per-person weekly issued figures. Filters mirror the current-week |
| 2932 | // query in get_weekly_individual_performance exactly (sent_date axis: |
| 2933 | // a budget counts in the ISO week of its latest send; for_add != 1, |
| 2934 | // budget_type_id != 7). |
| 2935 | $weeklySql = "SELECT q.commercial nm, WEEK({$issued}, 3) wk, COUNT(*) n, COALESCE(SUM(q.amount), 0) eur |
| 2936 | FROM tbl_quotations q |
| 2937 | JOIN tbl_companies c ON q.company_id = c.company_id |
| 2938 | JOIN tbl_users u ON u.name = q.commercial AND u.is_commercial = 1 |
| 2939 | WHERE c.is_send_executive = 1 |
| 2940 | AND q.for_add != 1 |
| 2941 | AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1 |
| 2942 | AND q.budget_type_id != 7 |
| 2943 | AND q.commercial IN ({$inNames}) |
| 2944 | AND {$issued} IS NOT NULL |
| 2945 | AND YEAR(DATE_ADD({$issued}, INTERVAL - WEEKDAY({$issued}) DAY)) = {$isoYear} |
| 2946 | GROUP BY nm, wk"; |
| 2947 | |
| 2948 | $weekly = []; |
| 2949 | foreach (DB::select($weeklySql) as $r) { |
| 2950 | $weekly[$r->nm][(int) $r->wk] = ['n' => (int) $r->n, 'eur' => (float) $r->eur]; |
| 2951 | } |
| 2952 | |
| 2953 | // "Any sign of them working in Titan" for the away test. tbl_user_activity |
| 2954 | // would be the natural source but only starts 2026-05-04, so it cannot |
| 2955 | // speak for the first ~18 weeks of the year; tbl_quotations_log reaches |
| 2956 | // back to 2025-10-27 and covers the whole reporting year. |
| 2957 | // |
| 2958 | // The window is expressed as a plain date range rather than |
| 2959 | // YEAR(DATE_ADD(created_at, ...)) so the predicate stays sargable — the |
| 2960 | // table has no index on created_at, and wrapping the column in functions |
| 2961 | // removes any chance of using one if it is ever added. |
| 2962 | $isoStart = date('Y-m-d', strtotime($isoYear.'-W01-1')); |
| 2963 | $isoEnd = date('Y-m-d', strtotime(($isoYear + 1).'-W01-1')); |
| 2964 | |
| 2965 | $activitySql = "SELECT l.user nm, WEEK(l.created_at, 3) wk, COUNT(*) n |
| 2966 | FROM tbl_quotations_log l |
| 2967 | WHERE l.user IN ({$inNames}) |
| 2968 | AND l.created_at >= '{$isoStart}' |
| 2969 | AND l.created_at < '{$isoEnd}' |
| 2970 | GROUP BY nm, wk"; |
| 2971 | |
| 2972 | $activity = []; |
| 2973 | foreach (DB::select($activitySql) as $r) { |
| 2974 | $activity[$r->nm][(int) $r->wk] = (int) $r->n; |
| 2975 | } |
| 2976 | |
| 2977 | // Per-person weekly € objectives. The sheet stores cumulative targets, so |
| 2978 | // the weekly figure is cumulative(w) - cumulative(w-1). |
| 2979 | $cumulative = []; |
| 2980 | $targetRows = DB::table('tbl_executive_report_fte_targets') |
| 2981 | ->select('commercial_name', 'week_number', 'cumulative_target') |
| 2982 | ->where('year', $isoYear) |
| 2983 | ->where('kind', 'emitidos') |
| 2984 | ->where('week_number', '<=', $throughWeek) |
| 2985 | ->get(); |
| 2986 | foreach ($targetRows as $tr) { |
| 2987 | $cumulative[$tr->commercial_name][(int) $tr->week_number] = (float) $tr->cumulative_target; |
| 2988 | } |
| 2989 | |
| 2990 | $isSpecialist = fn ($row) => strcasecmp(trim((string) ($row['role'] ?? '')), self::WCR_SPECIALIST_ROLE) === 0; |
| 2991 | |
| 2992 | $streaks = []; |
| 2993 | foreach ($roster as $name => $row) { |
| 2994 | $specialist = $isSpecialist($row); |
| 2995 | |
| 2996 | // Reduce each week to the three facts the walk needs, newest first. |
| 2997 | $weeks = []; |
| 2998 | for ($w = $throughWeek; $w >= 1; $w--) { |
| 2999 | $n = $weekly[$name][$w]['n'] ?? 0; |
| 3000 | $eur = $weekly[$name][$w]['eur'] ?? 0.0; |
| 3001 | |
| 3002 | if ($specialist) { |
| 3003 | $target = (float) self::WCR_TARGET_VOLUME; |
| 3004 | $metric = (float) $n; |
| 3005 | } else { |
| 3006 | $cum = $cumulative[$name][$w] ?? null; |
| 3007 | $target = $cum !== null |
| 3008 | ? $cum - ($cumulative[$name][$w - 1] ?? 0.0) |
| 3009 | : (float) self::WCR_TARGET_VALUE; |
| 3010 | $metric = $eur; |
| 3011 | } |
| 3012 | |
| 3013 | $weeks[] = [ |
| 3014 | // A week the plan itself sets to zero is not measurable |
| 3015 | // (shutdown weeks are encoded that way), so it neither counts |
| 3016 | // as a hit nor breaks the run. |
| 3017 | 'measurable' => $target > 0, |
| 3018 | 'hit' => $target > 0 && $metric >= $target, |
| 3019 | 'worked' => $n > 0 || ($activity[$name][$w] ?? 0) > 0, |
| 3020 | ]; |
| 3021 | } |
| 3022 | |
| 3023 | $streaks[$name] = self::wcr_tally_weeks($weeks); |
| 3024 | } |
| 3025 | |
| 3026 | $this->wcrYearCache[$cacheKey] = $streaks; |
| 3027 | |
| 3028 | return $streaks; |
| 3029 | } |
| 3030 | |
| 3031 | /** |
| 3032 | * How many weeks the person met their weekly objective, kept free of SQL so |
| 3033 | * it can be tested directly. |
| 3034 | * |
| 3035 | * Revised 2026-08-01 (Duarte): this is a TALLY across the year, not a |
| 3036 | * consecutive run. Previously a single miss ended the count, so somebody who |
| 3037 | * hit objective in 20 weeks but missed last week showed nothing. The figure |
| 3038 | * is now "weeks met so far this year": |
| 3039 | * hit → counts |
| 3040 | * missed while working → does not count, and does NOT stop the tally |
| 3041 | * missed while away → not an eligible week; ignored |
| 3042 | * not measurable → ignored (the plan set no objective that week) |
| 3043 | * |
| 3044 | * Order is therefore irrelevant, and no week can cancel another. |
| 3045 | * |
| 3046 | * Returns null rather than 0 when no week was ever met, because the report |
| 3047 | * shows a dash for that and a zero would read as a measured value. |
| 3048 | * |
| 3049 | * Returns BOTH polarities, because the two halves of section 6 need opposite |
| 3050 | * figures: the Top-3 cards report weeks met, while the bajo-objetivo cards are |
| 3051 | * negative tables and must report weeks NOT met. Same eligible-week set for |
| 3052 | * both, so they are always consistent with each other. |
| 3053 | * |
| 3054 | * @param array $weeks list of ['hit'=>bool,'worked'=>bool,'measurable'=>bool] |
| 3055 | * @return array{met: ?int, missed: ?int} |
| 3056 | */ |
| 3057 | public static function wcr_tally_weeks(array $weeks): array |
| 3058 | { |
| 3059 | $met = 0; |
| 3060 | $missed = 0; |
| 3061 | |
| 3062 | foreach ($weeks as $week) { |
| 3063 | // No objective set for the week → nothing to meet or miss. |
| 3064 | if (empty($week['measurable'])) { |
| 3065 | continue; |
| 3066 | } |
| 3067 | |
| 3068 | // Away weeks are not eligible weeks. They cannot be hits, and with a |
| 3069 | // tally they cannot count against anybody either — which is what the |
| 3070 | // holiday rule was always trying to achieve. |
| 3071 | if (empty($week['hit']) && empty($week['worked'])) { |
| 3072 | continue; |
| 3073 | } |
| 3074 | |
| 3075 | if (! empty($week['hit'])) { |
| 3076 | $met++; |
| 3077 | } else { |
| 3078 | $missed++; |
| 3079 | } |
| 3080 | } |
| 3081 | |
| 3082 | return [ |
| 3083 | 'met' => $met > 0 ? $met : null, |
| 3084 | 'missed' => $missed > 0 ? $missed : null, |
| 3085 | ]; |
| 3086 | } |
| 3087 | |
| 3088 | /** |
| 3089 | * FIRE-1448 v2 (Luis Reyes review, 2026-07-12): full layout redesign per |
| 3090 | * Berta's HTML mockup. Four regional blocks replace the previous single |
| 3091 | * wide table: |
| 3092 | * 1 · Grupo — 6 metrics × 3 periods × (2026 / 2025 / Δ) |
| 3093 | * 2 · Objetivos — 2 cards with progress bars (Emitido, Aceptado) |
| 3094 | * 3 · Regional YTD — two tables (€ and #), no weekly, no by-type |
| 3095 | * 4 · Mix por tipo YTD — two tables (Emitido, Aceptado) |
| 3096 | * Part 5 (Performance individual) keeps the top 3 + bajo objetivo cards and |
| 3097 | * the FIRE-1448 BE-2 roster allowlist; since 2026-07-27 its four tables are |
| 3098 | * split by role — left column Asesores de clientes (€), right column Sales |
| 3099 | * specialists (#). |
| 3100 | */ |
| 3101 | private function build_weekly_commercial_report_html(array $regional, array $individual, array $series = [], array $imgSrc = []): string |
| 3102 | { |
| 3103 | // Integer euros with dot-thousands and no space (mockup: "87.400€"). |
| 3104 | // Kept for Section 5 (Performance individual), which is unchanged. |
| 3105 | $money = fn ($v) => number_format((float) $v, 0, ',', '.').'€'; |
| 3106 | // v3 renders every amount in K€ (thousands). $kNum = bare thousands with |
| 3107 | // dot-grouping ("16.440", used inside tables whose header already says |
| 3108 | // K€); $kEuro appends the "K€" suffix ("48.281K€", used in headline cells). |
| 3109 | // Values <500€ round to 0, per the v3 footnote. |
| 3110 | $kNum = fn ($v) => number_format((float) round(((float) $v) / 1000), 0, ',', '.'); |
| 3111 | $kEuro = fn ($v) => $kNum($v).'K€'; |
| 3112 | $numFmt = fn ($v) => number_format((int) $v, 0, ',', '.'); |
| 3113 | $pctFmt = fn ($v, $decimals = 1) => number_format((float) $v, $decimals, ',', '.').'%'; |
| 3114 | // Signed delta between two comparable series. Handles: |
| 3115 | // prev = 0 & cur > 0 → "nuevo" (neutral color) |
| 3116 | // prev = 0 & cur = 0 → "—" |
| 3117 | // ratio ≥ 500 % → "×N,N" so 12x growth stays readable |
| 3118 | // otherwise → "+X,X%" (pos) or "−X,X%" (neg) |
| 3119 | $deltaPct = function ($cur, $prev) { |
| 3120 | $cur = (float) $cur; |
| 3121 | $prev = (float) $prev; |
| 3122 | if ($prev <= 0) { |
| 3123 | return $cur > 0 ? ['label' => 'nuevo', 'sign' => 'neutral'] : ['label' => '—', 'sign' => 'neutral']; |
| 3124 | } |
| 3125 | $ratio = (($cur - $prev) / $prev) * 100; |
| 3126 | if ($ratio >= 500) { |
| 3127 | return ['label' => '×'.number_format(($cur / $prev), 1, ',', '.'), 'sign' => 'pos']; |
| 3128 | } |
| 3129 | $prefix = $ratio >= 0 ? '+' : ''; |
| 3130 | |
| 3131 | return ['label' => $prefix.number_format($ratio, 1, ',', '.').'%', 'sign' => $ratio >= 0 ? 'pos' : 'neg']; |
| 3132 | }; |
| 3133 | |
| 3134 | $font = "'Segoe UI',system-ui,Arial,sans-serif"; |
| 3135 | $week = (int) $regional['weekNumber']; |
| 3136 | $badge = 'Semana '.$week.' · ' |
| 3137 | .date('d/m', strtotime($regional['mondayDate'])).' – '.date('d/m/Y', strtotime($regional['sundayDate'])); |
| 3138 | $monthsEs = [1 => 'Enero', 2 => 'Febrero', 3 => 'Marzo', 4 => 'Abril', 5 => 'Mayo', 6 => 'Junio', |
| 3139 | 7 => 'Julio', 8 => 'Agosto', 9 => 'Septiembre', 10 => 'Octubre', 11 => 'Noviembre', 12 => 'Diciembre']; |
| 3140 | $monthLabel = $monthsEs[(int) date('n', strtotime($regional['mondayDate']))] ?? 'Mes'; |
| 3141 | |
| 3142 | // Sort regions by 2026 YTD amount desc for blocks 3 and 4 (matches Berta's |
| 3143 | // mockup which leads with Cataluña / Madrid / CyL). Total row rendered last. |
| 3144 | $sortedRegions = $regional['regions']; |
| 3145 | usort($sortedRegions, fn ($a, $b) => ($b['current']['ytd_amount'] ?? 0) <=> ($a['current']['ytd_amount'] ?? 0)); |
| 3146 | |
| 3147 | // Build the four v3 regional blocks, bound to the real $regional data. |
| 3148 | $block1 = $this->wcr_group_table($regional['totals'], $week, $monthLabel, $kEuro, $pctFmt, $deltaPct, $numFmt); |
| 3149 | $block2 = $this->wcr_obj_strip($regional['totals'], $kEuro, $pctFmt); |
| 3150 | $block3 = $this->wcr_regional_table($sortedRegions, $regional['totals'], $week, $kEuro, $kNum, $numFmt, $deltaPct); |
| 3151 | $block4 = $this->wcr_mix_table($sortedRegions, $regional['totals'], $kNum); |
| 3152 | |
| 3153 | // Individual cards (Performance individual — Part 5). Split by role: |
| 3154 | // left column = Asesores de clientes (€), right column = Sales |
| 3155 | // specialists (#). Both rows keep that same left/right convention. |
| 3156 | $cards = $this->wcr_two_col([ |
| 3157 | $this->wcr_top_card('Top 3 semana · Asesores de clientes (€)', $individual['topAdvisors'], $money), |
| 3158 | $this->wcr_top_card('Top 3 semana · Sales specialists (#)', $individual['topSpecialists'], $money), |
| 3159 | $this->wcr_under_card('Asesores de clientes bajo objetivo · valor (<'.(int) (self::WCR_TARGET_VALUE / 1000).'K€)', $individual['belowAdvisors'], 'value', $money), |
| 3160 | $this->wcr_under_card('Sales specialists bajo objetivo · volumen (<'.(int) self::WCR_TARGET_VOLUME.' pres.)', $individual['belowSpecialists'], 'volume', $money), |
| 3161 | ]); |
| 3162 | |
| 3163 | // v3 design: the report is a single standalone document whose <style> |
| 3164 | // block (ported from Berta's v3 mockup) drives sections 1–4 via CSS |
| 3165 | // classes. CSS custom properties from the mockup are resolved to literal |
| 3166 | // colours here because Outlook's Word engine can't evaluate var(). The |
| 3167 | // .wcr-col media query for the (unchanged) Section 5 cards is appended. |
| 3168 | $style = '' |
| 3169 | .'*{box-sizing:border-box;}' |
| 3170 | .'body{font-family:-apple-system,"Segoe UI",Helvetica,Arial,sans-serif;color:#1c1c1e;background:#fff;margin:0;padding:0 0 48px 0;}' |
| 3171 | .'.header{background:#d92940;color:#fff;padding:22px 36px;display:flex;justify-content:space-between;align-items:center;}' |
| 3172 | .'.header h1{font-size:19px;margin:0 0 3px 0;}' |
| 3173 | .'.header .sub{font-size:12.5px;opacity:.85;}' |
| 3174 | .'.header .week{background:rgba(0,0,0,.18);padding:7px 14px;border-radius:7px;font-size:13px;font-weight:600;white-space:nowrap;}' |
| 3175 | .'.section{padding:26px 36px 6px 36px;}' |
| 3176 | .'.section h2{font-size:13px;letter-spacing:.8px;text-transform:uppercase;color:#1c1c1e;margin:0 0 4px 0;}' |
| 3177 | .'.section .hint{font-size:12px;color:#8a8a8e;margin:0 0 14px 0;line-height:1.5;}' |
| 3178 | .'table{border-collapse:collapse;width:100%;font-size:13px;}' |
| 3179 | .'th,td{padding:7px 9px;text-align:right;white-space:nowrap;}' |
| 3180 | .'th{font-weight:600;}' |
| 3181 | .'td:first-child,th:first-child{text-align:left;}' |
| 3182 | .'.group-head th{font-size:11.5px;text-transform:uppercase;letter-spacing:.5px;color:#fff;background:#6b6a67;border-left:3px solid #fff;}' |
| 3183 | .'.group-head th:first-child{background:transparent;border:none;}' |
| 3184 | .'.col-head th{font-size:11.5px;color:#8a8a8e;border-bottom:2px solid #1c1c1e;background:#faf9f7;}' |
| 3185 | .'.col-head th.delta{color:#1c1c1e;}' |
| 3186 | .'tbody tr{border-bottom:1px solid #e4e2de;}' |
| 3187 | .'tbody tr:nth-child(even){background:#faf9f7;}' |
| 3188 | .'.metric{font-weight:600;}' |
| 3189 | .'.y25{color:#8a8a8e;}' |
| 3190 | .'.pos{color:#1a8a4a;font-weight:700;}' |
| 3191 | .'.neg{color:#d9482b;font-weight:700;}' |
| 3192 | .'.nd{color:#b5b3af;font-style:italic;font-weight:400;}' |
| 3193 | .'.block-sep{border-left:3px solid #d8d6d2;}' |
| 3194 | .'.rowgroup td{background:#ecebe8;font-size:11.5px;text-transform:uppercase;letter-spacing:.6px;color:#1c1c1e;font-weight:700;padding:6px 12px;box-shadow:inset 4px 0 0 #6b6a67;}' |
| 3195 | .'.obj-strip{display:flex;gap:18px;margin-top:6px;}' |
| 3196 | .'.obj-card{flex:1;border:1px solid #e4e2de;border-radius:10px;padding:16px 18px;background:#faf9f7;}' |
| 3197 | .'.obj-card .label{font-size:11.5px;text-transform:uppercase;letter-spacing:.6px;color:#8a8a8e;font-weight:700;margin-bottom:8px;}' |
| 3198 | .'.obj-card .big{font-size:22px;font-weight:700;}' |
| 3199 | .'.obj-card .big span{font-size:13px;color:#8a8a8e;font-weight:500;}' |
| 3200 | .'.bar{height:8px;background:#e7e5e1;border-radius:4px;margin:10px 0 6px 0;overflow:hidden;}' |
| 3201 | .'.bar .fill{height:100%;border-radius:4px;}' |
| 3202 | .'.obj-card .foot{font-size:12px;color:#8a8a8e;display:flex;justify-content:space-between;}' |
| 3203 | .'.foot-notes{padding:22px 36px 0 36px;font-size:11.5px;color:#8a8a8e;line-height:1.55;border-top:1px solid #e4e2de;margin-top:30px;}' |
| 3204 | .'.regional td.name{font-weight:600;}' |
| 3205 | .'.total-row td{font-weight:700;background:#f4f2ee !important;border-top:2px solid #1c1c1e;}' |
| 3206 | .'.row-25 td{color:#8a8a8e;font-style:italic;font-weight:400 !important;}' |
| 3207 | .'.row-acc td{font-weight:700;background:#f4f2ee !important;border-top:2px solid #1c1c1e;}' |
| 3208 | .'@media only screen and (max-width:640px){.wcr-col{display:block !important;width:100% !important;padding:0 0 14px 0 !important;}}'; |
| 3209 | |
| 3210 | $html = '<!DOCTYPE html><html lang="es"><head><meta charset="UTF-8">' |
| 3211 | .'<meta name="viewport" content="width=device-width, initial-scale=1.0">' |
| 3212 | .'<meta http-equiv="X-UA-Compatible" content="IE=edge">' |
| 3213 | .'<title>Reporte Semanal de Performance Comercial</title>' |
| 3214 | .'<style>'.$style.'</style>' |
| 3215 | .'</head><body>'; |
| 3216 | |
| 3217 | // Header band (v3 .header). |
| 3218 | $html .= '<div class="header">' |
| 3219 | .'<div><h1>Reporte semanal · Performance comercial</h1>' |
| 3220 | .'<div class="sub">Grupo Fire / IBV Group · TITAN</div></div>' |
| 3221 | .'<div class="week">'.$badge.'</div></div>'; |
| 3222 | |
| 3223 | // Section 1 — Grupo. |
| 3224 | $html .= '<div class="section">' |
| 3225 | .'<h2>1 · Grupo — Emitido, aceptado y ratio</h2>' |
| 3226 | .'<p class="hint">Comparativas siempre like-for-like: semana vs misma semana 2025, mes vs mismo mes 2025, YTD vs mismo periodo 2025 — nunca vs total del año. Todos los importes en K€.</p>' |
| 3227 | .$block1.'</div>'; |
| 3228 | |
| 3229 | // Section 2 — Objetivos. |
| 3230 | $html .= '<div class="section">' |
| 3231 | .'<h2>2 · Objetivos — dónde estamos vs plan</h2>' |
| 3232 | .'<p class="hint">Objetivos 2026 por región (modelo vf). Solo YTD y año; el objetivo semanal/mensual añade ruido. El color de la barra indica el <strong>nivel de cumplimiento</strong> del objetivo (rojo <70%, ámbar 70–90%, verde ≥90%), no el tipo de dato.</p>' |
| 3233 | .$block2.'</div>'; |
| 3234 | |
| 3235 | // Section 3 — Regional. |
| 3236 | $html .= '<div class="section">' |
| 3237 | .'<h2>3 · Regional — semana + YTD en una sola tabla</h2>' |
| 3238 | .'<p class="hint">Productividad de la semana por región + acumulado YTD (€ y #, emitido y aceptado, vs 2025 y vs objetivo). Ordenado por emitido YTD. Todos los importes en K€.</p>' |
| 3239 | .$block3.'</div>'; |
| 3240 | |
| 3241 | // Section 4 — Mix por tipo. |
| 3242 | $html .= '<div class="section">' |
| 3243 | .'<h2>4 · Mix por tipo de servicio — semana + YTD</h2>' |
| 3244 | .'<p class="hint">Repartición del importe por categoría de servicio. <strong>Las filas por región y los dos totales del bloque "Emitido" son importe EMITIDO</strong> (K€ de la semana y acumulado YTD, con el peso del tipo sobre el total de la región en euros y en número de presupuestos). El bloque inferior (banda «Aceptado») es lo ACEPTADO 2026 YTD. Igual que en la sección 3, emitido y aceptado se distinguen por las bandas de fila, no por color; todas las cifras van en negro. Otros = Venta + Pasivo + Otros (grupos 6, 7, 8).</p>' |
| 3245 | .$block4.'</div>'; |
| 3246 | |
| 3247 | // Section 5 — Año semana a semana. Sits directly above the individual |
| 3248 | // performance section: the group picture first, the individual picture |
| 3249 | // after it. Nothing above is removed or reordered — the individual |
| 3250 | // section simply becomes 6. |
| 3251 | if ($series !== []) { |
| 3252 | $html .= '<div class="section">' |
| 3253 | .'<h2>5 · El año semana a semana</h2>' |
| 3254 | .'<p class="hint">Cada gráfico recorre todas las semanas del año hasta la semana reportada. ' |
| 3255 | .'La <strong>línea discontinua</strong> es la media del año (solo semanas cerradas), y el porcentaje bajo cada semana ' |
| 3256 | .'indica cuánto se sitúa por encima (verde) o por debajo (rojo) de esa media. ' |
| 3257 | .'La <strong>última semana aparece sombreada y marcada «en curso»</strong>: el reporte sale antes de que termine, ' |
| 3258 | .'así que su cifra es parcial por definición y no debe leerse como una caída. ' |
| 3259 | .'Las semanas con un círculo ámbar llevan una nota explicativa justo debajo del gráfico.</p>' |
| 3260 | .$this->wcr_year_charts_section($series, $imgSrc, $kNum, $numFmt) |
| 3261 | .'</div>'; |
| 3262 | } |
| 3263 | |
| 3264 | // Section 6 — Performance individual. Four tables in two role columns: |
| 3265 | // Asesores de clientes on the left, Sales specialists on the right. |
| 3266 | $html .= '<div class="section">'; |
| 3267 | $html .= $this->wcr_section('6 · Performance individual', '👤'); |
| 3268 | $html .= '<p class="hint">Separado por rol: la columna izquierda son los <strong>Asesores de clientes</strong>, medidos por importe emitido (€); la derecha, los <strong>Sales specialists</strong>, medidos por número de presupuestos (#). Cada rol se ordena y se compara solo contra los de su rol. ' |
| 3269 | .'La etiqueta de la última columna cuenta semanas del año en curso, y su signo depende de la tabla: en los <strong>Top 3</strong> son las ' |
| 3270 | .'<strong>semanas cumpliendo el objetivo</strong> (verde) y en las tablas de <strong>bajo objetivo</strong> son las ' |
| 3271 | .'<strong>semanas sin cumplirlo</strong> (rojo). No tienen que ser consecutivas y una semana no descuenta a otra. ' |
| 3272 | .'Las semanas de ausencia no cuentan en ninguna de las dos direcciones, y la semana en curso todavía no se cuenta. Un guion (—) significa que aún no hay ninguna.</p>'; |
| 3273 | $html .= $cards; |
| 3274 | $html .= '</div>'; |
| 3275 | |
| 3276 | // Foot-notes — the report's own data footnotes (the † / * markers the |
| 3277 | // tables reference, plus methodology). The v3 mockup's proposal-only |
| 3278 | // notes ("Notas de la propuesta v3", "Cambios vs v2", the v1 fix, the |
| 3279 | // pending-model line) are deliberately excluded — they described the |
| 3280 | // proposal, not the live report. |
| 3281 | $html .= '<div class="foot-notes">' |
| 3282 | .'<strong>Notas</strong><br>' |
| 3283 | .'· Todos los importes del reporte en K€, redondeados al millar más cercano (valores <500€ → 0).<br>' |
| 3284 | .'· Comparativas siempre like-for-like: semana vs misma semana 2025, mes vs mismo mes 2025, YTD vs mismo periodo 2025 (01/01 – hoy) — nunca vs total del año.<br>' |
| 3285 | .'· † Ref. 25 € = % de aceptación de referencia del año 2025 completo calculado sobre <strong>importe</strong> (€ aceptados / € emitidos), no sobre número de presupuestos, por región (valores facilitados por Duarte) — más estable que el YTD 25 mes a mes si hay obras grandes presupuestadas o aceptadas. "nuevo" = región abierta en 2026, sin referencia 2025.<br>' |
| 3286 | .'· * n/d = dato no expuesto aún por el reporte actual. Para completarlo, el CRM debe servir: (1) aceptados 2025 (€, # y ratio) por semana, mes y YTD — a nivel grupo y región; (2) # de presupuestos del mes en curso. Todo existe en base de datos — es solo query.<br>' |
| 3287 | .'· Tabla 4: las filas por región y las dos filas "Total emitido" (2026 / 2025) son importe <strong>emitido</strong>; la última fila, "Total aceptado 2026", es importe <strong>aceptado</strong> (2026 YTD).<br>' |
| 3288 | .'· % mix € = peso del tipo en <strong>importe</strong> sobre el total YTD de la región (emitido sobre emitido; en la fila "Total aceptado 2026", aceptado sobre aceptado). % mix # = peso del tipo en <strong>número de presupuestos</strong> (# presupuestos del tipo YTD / # emitidos —o aceptados— YTD). Ambos pueden no sumar 100% por redondeo.<br>' |
| 3289 | .'· Emitido vs aceptado se distingue por las bandas de fila etiquetadas (igual que en la sección 3, donde son columnas etiquetadas), no por color: todas las cifras van en negro. El color se reserva para una sola cosa — verde = variación positiva, rojo = variación negativa; gris = año anterior. Única excepción: las barras de la sección 2, donde el color = nivel de cumplimiento del objetivo (rojo <70%, ámbar 70–90%, verde ≥90%).<br>' |
| 3290 | .'· % aceptación (€) = € aceptados / € emitidos del periodo; (#) = # aceptados / # emitidos. "Aceptado" se cuenta por fecha de aceptación, independiente del año de emisión (mezcla cohortes). Recomendado en drill-down: ratio por cohorte de emisión.<br>' |
| 3291 | .'· Objetivos: modelo 2026 vf por región (emitido y aceptado), acumulado semana a semana; el objetivo YTD es el acumulado hasta la semana en curso y el anual, el total del año.<br>' |
| 3292 | .'· Sección 5 (el año semana a semana): los cuatro gráficos usan exactamente los mismos filtros que las secciones 1–4, de modo que el punto de una semana coincide con la cifra de esa misma semana en el resto del reporte. Emitido se imputa por fecha de emisión y aceptado por fecha de aceptación, igual que arriba. La media del año se calcula <strong>solo sobre semanas cerradas</strong>: incluir la semana en curso, que va a medias, hundiría la referencia y haría parecer mejores todas las demás. Por el mismo motivo la semana en curso no muestra porcentaje de variación.<br>' |
| 3293 | .'· Semanas contadas (sección 6): número <strong>total</strong> de semanas del año en curso, no necesariamente consecutivas, y una semana nunca resta a otra. En los Top 3 se cuentan las semanas <strong>cumpliendo</strong> el objetivo; en las tablas de bajo objetivo, al ser tablas negativas, se cuentan las semanas <strong>sin cumplirlo</strong>. La semana reportada no se cuenta, porque el reporte sale antes de que termine. Cada persona se mide contra el objetivo de su rol: asesores por importe emitido (objetivo individual de la hoja de FTE cuando existe, '.(int) (self::WCR_TARGET_VALUE / 1000).'K€ si no), specialists por número de presupuestos ('.(int) self::WCR_TARGET_VOLUME.'). Una semana sin presupuestos <strong>y</strong> sin actividad en Titan se considera ausencia y no es una semana evaluable: no cuenta ni a favor ni en contra, de forma que las vacaciones no penalizan. Basta '.(int) self::WCR_STREAK_MAX_AWAY_WEEKS.' semana sin actividad para considerarla ausencia.<br>' |
| 3294 | .'· Sección 6 (performance individual): las cuatro tablas se separan por rol de usuario — Asesores de clientes (izquierda, ranking y objetivo en €) y Sales specialists (derecha, ranking y objetivo en # de presupuestos). El objetivo semanal en € es el individual de la hoja de FTE cuando existe, y '.(int) (self::WCR_TARGET_VALUE / 1000).'K€ en caso contrario; el de volumen son '.(int) self::WCR_TARGET_VOLUME.' presupuestos. Los Top 3 solo incluyen a quien ha emitido esta semana; las tablas de bajo objetivo incluyen a todo el roster, también a quien no ha emitido nada.' |
| 3295 | .'</div>'; |
| 3296 | |
| 3297 | $html .= '</body></html>'; |
| 3298 | |
| 3299 | return $html; |
| 3300 | } |
| 3301 | |
| 3302 | /** |
| 3303 | * Section heading: uppercase label with a light underline (matches the mockup). |
| 3304 | * $icon is an email-safe Unicode emoji (SVG can't render in Outlook/Gmail). |
| 3305 | */ |
| 3306 | private function wcr_section(string $title, string $icon = ''): string |
| 3307 | { |
| 3308 | $iconHtml = $icon !== '' ? '<span style="font-size:14px;">'.$icon.'</span> ' : ''; |
| 3309 | |
| 3310 | return '<table role="presentation" width="100%" cellpadding="0" cellspacing="0" border="0" style="margin-bottom:12px;"><tr>' |
| 3311 | .'<td style="font-family:\'Segoe UI\',system-ui,Arial,sans-serif;font-size:13px;font-weight:600;color:#444441;' |
| 3312 | .'text-transform:uppercase;letter-spacing:0.06em;padding-bottom:8px;border-bottom:1px solid #d3d1c7;">'.$iconHtml.$title.'</td>' |
| 3313 | .'</tr></table>'; |
| 3314 | } |
| 3315 | |
| 3316 | /** Lay the individual-performance cards out two-up; .wcr-col stacks them on mobile. */ |
| 3317 | private function wcr_two_col(array $cards): string |
| 3318 | { |
| 3319 | $rows = ''; |
| 3320 | $n = count($cards); |
| 3321 | for ($i = 0; $i < $n; $i += 2) { |
| 3322 | $left = $cards[$i]; |
| 3323 | $right = $cards[$i + 1] ?? ''; |
| 3324 | $rows .= '<tr>' |
| 3325 | .'<td class="wcr-col" width="50%" valign="top" style="width:50%;vertical-align:top;padding:0 7px 14px 0;">'.$left.'</td>' |
| 3326 | .'<td class="wcr-col" width="50%" valign="top" style="width:50%;vertical-align:top;padding:0 0 14px 7px;">'.$right.'</td>' |
| 3327 | .'</tr>'; |
| 3328 | } |
| 3329 | |
| 3330 | return '<table role="presentation" width="100%" cellpadding="0" cellspacing="0" border="0" style="margin-top:6px;">'.$rows.'</table>'; |
| 3331 | } |
| 3332 | |
| 3333 | // --------------------------------------------------------------------- |
| 3334 | // FIRE-1448 v2 helpers — the four regional blocks (Grupo / Objetivos / |
| 3335 | // Regional YTD / Mix por tipo). Each returns a self-contained HTML |
| 3336 | // fragment built exclusively out of tables + inline styles so Outlook and |
| 3337 | // Gmail render them the same way. |
| 3338 | // --------------------------------------------------------------------- |
| 3339 | |
| 3340 | /** |
| 3341 | * Map a $deltaPct closure result's sign to the v3 CSS class used on the |
| 3342 | * cell: green (pos), red (neg) or muted-italic (neutral / "nuevo" / "—"). |
| 3343 | */ |
| 3344 | private function wcr_delta_class(string $sign): string |
| 3345 | { |
| 3346 | return match ($sign) { |
| 3347 | 'pos' => 'pos', |
| 3348 | 'neg' => 'neg', |
| 3349 | default => 'nd', |
| 3350 | }; |
| 3351 | } |
| 3352 | |
| 3353 | /** |
| 3354 | * Section 1 — Grupo table (v3). Six metrics across three periods (Semana / |
| 3355 | * month / YTD), each period with (2026 / 2025 / Δ). Values bind to |
| 3356 | * $totals['current'] / $totals['previous']; a cell renders the v3 "n/d*" |
| 3357 | * treatment only where the underlying value is genuinely unavailable (prev |
| 3358 | * ratio with a zero denominator). |
| 3359 | */ |
| 3360 | private function wcr_group_table(array $totals, int $week, string $monthLabel, $kEuro, $pctFmt, $deltaPct, $numFmt): string |
| 3361 | { |
| 3362 | $c = $totals['current']; |
| 3363 | $p = $totals['previous']; |
| 3364 | |
| 3365 | // One "2026 | 2025 | Δ" triple. $prevValue === null → prev + Δ render n/d*. |
| 3366 | $triple = function ($curValue, $prevValue, $formatter) use ($deltaPct) { |
| 3367 | $cur = '<td class="block-sep">'.$formatter($curValue).'</td>'; |
| 3368 | if ($prevValue === null) { |
| 3369 | return $cur.'<td class="y25 nd">n/d*</td><td class="nd">—</td>'; |
| 3370 | } |
| 3371 | $d = $deltaPct($curValue, $prevValue); |
| 3372 | |
| 3373 | return $cur |
| 3374 | .'<td class="y25">'.$formatter($prevValue).'</td>' |
| 3375 | .'<td class="'.$this->wcr_delta_class($d['sign']).'">'.$d['label'].'</td>'; |
| 3376 | }; |
| 3377 | |
| 3378 | // Ratio rows: percentage-point delta instead of a ratio delta. |
| 3379 | $pctTriple = function ($curValue, $prevValue) use ($pctFmt) { |
| 3380 | if ($curValue === null) { |
| 3381 | $cur = '<td class="block-sep nd">n/d*</td>'; |
| 3382 | } else { |
| 3383 | $cur = '<td class="block-sep">'.$pctFmt($curValue).'</td>'; |
| 3384 | } |
| 3385 | if ($curValue === null || $prevValue === null) { |
| 3386 | return $cur.'<td class="y25 nd">n/d*</td><td class="nd">—</td>'; |
| 3387 | } |
| 3388 | $delta = $curValue - $prevValue; |
| 3389 | $cls = $delta >= 0 ? 'pos' : 'neg'; |
| 3390 | $prefix = $delta >= 0 ? '+' : ''; |
| 3391 | |
| 3392 | return $cur |
| 3393 | .'<td class="y25">'.$pctFmt($prevValue).'</td>' |
| 3394 | .'<td class="'.$cls.'">'.$prefix.number_format($delta, 1, ',', '.').' pp</td>'; |
| 3395 | }; |
| 3396 | |
| 3397 | // Ratios (guarded against div/0). |
| 3398 | $pct = fn ($n, $d) => $d > 0 ? ($n / $d) * 100 : null; |
| 3399 | $curPctEurWeek = $pct($c['week_accepted_amount'], $c['week_amount']); |
| 3400 | $prevPctEurWeek = $pct($p['week_accepted_amount'], $p['week_amount']); |
| 3401 | $curPctEurMonth = $pct($c['month_accepted_amount'], $c['month_amount']); |
| 3402 | $prevPctEurMonth = $pct($p['month_accepted_amount'], $p['month_amount']); |
| 3403 | $curPctEurYtd = $pct($c['ytd_accepted_amount'], $c['ytd_amount']); |
| 3404 | $prevPctEurYtd = $pct($p['ytd_accepted_amount'], $p['ytd_amount']); |
| 3405 | $curPctNumWeek = $pct($c['week_accepted_budgets'], $c['week_budgets']); |
| 3406 | $prevPctNumWeek = $pct($p['week_accepted_budgets'], $p['week_budgets']); |
| 3407 | $curPctNumMonth = $pct($c['month_accepted_budgets'], $c['month_budgets']); |
| 3408 | $prevPctNumMonth = $pct($p['month_accepted_budgets'], $p['month_budgets']); |
| 3409 | $curPctNumYtd = $pct($c['ytd_accepted_budgets'], $c['ytd_budgets']); |
| 3410 | $prevPctNumYtd = $pct($p['ytd_accepted_budgets'], $p['ytd_budgets']); |
| 3411 | |
| 3412 | $thead = '<thead>' |
| 3413 | .'<tr class="group-head">' |
| 3414 | .'<th></th>' |
| 3415 | .'<th colspan="3">Semana '.$week.'</th>' |
| 3416 | .'<th colspan="3">'.htmlspecialchars($monthLabel).'</th>' |
| 3417 | .'<th colspan="3">YTD (01/01 – hoy)</th>' |
| 3418 | .'</tr>' |
| 3419 | .'<tr class="col-head">' |
| 3420 | .'<th></th>' |
| 3421 | .'<th class="block-sep">2026</th><th>2025</th><th class="delta">Δ</th>' |
| 3422 | .'<th class="block-sep">2026</th><th>2025</th><th class="delta">Δ</th>' |
| 3423 | .'<th class="block-sep">2026</th><th>2025</th><th class="delta">Δ</th>' |
| 3424 | .'</tr></thead>'; |
| 3425 | |
| 3426 | $body = '<tbody>' |
| 3427 | .'<tr class="rowgroup"><td colspan="10">Emitido</td></tr>' |
| 3428 | .'<tr><td class="metric">€ emitidos</td>' |
| 3429 | .$triple($c['week_amount'], $p['week_amount'], $kEuro) |
| 3430 | .$triple($c['month_amount'], $p['month_amount'], $kEuro) |
| 3431 | .$triple($c['ytd_amount'], $p['ytd_amount'], $kEuro) |
| 3432 | .'</tr>' |
| 3433 | .'<tr><td class="metric"># presupuestos</td>' |
| 3434 | .$triple($c['week_budgets'], $p['week_budgets'], $numFmt) |
| 3435 | .$triple($c['month_budgets'], $p['month_budgets'], $numFmt) |
| 3436 | .$triple($c['ytd_budgets'], $p['ytd_budgets'], $numFmt) |
| 3437 | .'</tr>' |
| 3438 | .'<tr class="rowgroup"><td colspan="10">Aceptado</td></tr>' |
| 3439 | .'<tr><td class="metric">€ aceptados</td>' |
| 3440 | .$triple($c['week_accepted_amount'], $p['week_accepted_amount'], $kEuro) |
| 3441 | .$triple($c['month_accepted_amount'], $p['month_accepted_amount'], $kEuro) |
| 3442 | .$triple($c['ytd_accepted_amount'], $p['ytd_accepted_amount'], $kEuro) |
| 3443 | .'</tr>' |
| 3444 | .'<tr><td class="metric"># aceptados</td>' |
| 3445 | .$triple($c['week_accepted_budgets'], $p['week_accepted_budgets'], $numFmt) |
| 3446 | .$triple($c['month_accepted_budgets'], $p['month_accepted_budgets'], $numFmt) |
| 3447 | .$triple($c['ytd_accepted_budgets'], $p['ytd_accepted_budgets'], $numFmt) |
| 3448 | .'</tr>' |
| 3449 | .'<tr class="rowgroup"><td colspan="10">Ratio de aceptación</td></tr>' |
| 3450 | .'<tr><td class="metric">% aceptación (€)</td>' |
| 3451 | .$pctTriple($curPctEurWeek, $prevPctEurWeek) |
| 3452 | .$pctTriple($curPctEurMonth, $prevPctEurMonth) |
| 3453 | .$pctTriple($curPctEurYtd, $prevPctEurYtd) |
| 3454 | .'</tr>' |
| 3455 | .'<tr><td class="metric">% aceptación (#)</td>' |
| 3456 | .$pctTriple($curPctNumWeek, $prevPctNumWeek) |
| 3457 | .$pctTriple($curPctNumMonth, $prevPctNumMonth) |
| 3458 | .$pctTriple($curPctNumYtd, $prevPctNumYtd) |
| 3459 | .'</tr>' |
| 3460 | .'</tbody>'; |
| 3461 | |
| 3462 | return '<table>'.$thead.$body.'</table>'; |
| 3463 | } |
| 3464 | |
| 3465 | /** |
| 3466 | * Section 2 — Objetivos strip (v3). Two .obj-card cards (Emitido / Aceptado) |
| 3467 | * with a YTD-vs-Obj-YTD headline, a .bar/.fill progress bar and the annual |
| 3468 | * target as the footer. Bar colour = compliance level (v3 rule: red <70%, |
| 3469 | * amber 70–90%, green ≥90%). Bound to: |
| 3470 | * Emitido : current.ytd_amount / ytd_target / annual_target |
| 3471 | * Aceptado: current.ytd_accepted_amount / ytd_target_acc / annual_target_acc |
| 3472 | */ |
| 3473 | private function wcr_obj_strip(array $totals, $kEuro, $pctFmt): string |
| 3474 | { |
| 3475 | $ytdEm = (float) ($totals['current']['ytd_amount'] ?? 0); |
| 3476 | $ytdTargetEm = (float) ($totals['ytd_target'] ?? 0); |
| 3477 | $annualEm = (float) ($totals['annual_target'] ?? 0); |
| 3478 | $ytdAc = (float) ($totals['current']['ytd_accepted_amount'] ?? 0); |
| 3479 | $ytdTargetAc = (float) ($totals['ytd_target_acc'] ?? 0); |
| 3480 | $annualAc = (float) ($totals['annual_target_acc'] ?? 0); |
| 3481 | |
| 3482 | $card = function (string $label, float $ytdVal, float $ytdTarget, float $annualTarget) use ($kEuro) { |
| 3483 | if ($ytdTarget > 0) { |
| 3484 | $pctVal = ($ytdVal / $ytdTarget) * 100; |
| 3485 | $fillPct = max(0, min(100, (int) round($pctVal))); |
| 3486 | // v3 compliance thresholds: red <70%, amber 70–90%, green ≥90%. |
| 3487 | [$barColor, $footColor] = $pctVal >= 90 |
| 3488 | ? ['#1a8a4a', '#1a8a4a'] |
| 3489 | : ($pctVal >= 70 ? ['#d9a02b', '#b3831f'] : ['#d9482b', '#d9482b']); |
| 3490 | $diffPct = (($ytdVal - $ytdTarget) / $ytdTarget) * 100; |
| 3491 | $footLabel = $fillPct.'% del obj. YTD ('.($diffPct >= 0 ? '+' : '').number_format($diffPct, 1, ',', '.').'%)'; |
| 3492 | $bigSub = 'de '.$kEuro($ytdTarget).' obj. YTD'; |
| 3493 | $fill = '<div class="bar"><div class="fill" style="width:'.$fillPct.'%;background:'.$barColor.';"></div></div>'; |
| 3494 | $foot = '<div class="foot"><span style="color:'.$footColor.';font-weight:700;">'.$footLabel.'</span>' |
| 3495 | .'<span>'.($annualTarget > 0 ? 'Obj. anual: '.$kEuro($annualTarget) : 'Obj. anual: <span class="nd">n/d*</span>').'</span></div>'; |
| 3496 | } else { |
| 3497 | $bigSub = 'obj. YTD <span class="nd">n/d*</span>'; |
| 3498 | $fill = '<div class="bar"></div>'; |
| 3499 | $foot = '<div class="foot"><span class="nd">n/d*</span>' |
| 3500 | .'<span>'.($annualTarget > 0 ? 'Obj. anual: '.$kEuro($annualTarget) : 'Obj. anual: <span class="nd">n/d*</span>').'</span></div>'; |
| 3501 | } |
| 3502 | |
| 3503 | return '<div class="obj-card">' |
| 3504 | .'<div class="label">'.$label.'</div>' |
| 3505 | .'<div class="big">'.$kEuro($ytdVal).' <span>'.$bigSub.'</span></div>' |
| 3506 | .$fill.$foot |
| 3507 | .'</div>'; |
| 3508 | }; |
| 3509 | |
| 3510 | return '<div class="obj-strip">' |
| 3511 | .$card('Emitido vs objetivo', $ytdEm, $ytdTargetEm, $annualEm) |
| 3512 | .$card('Aceptado vs objetivo', $ytdAc, $ytdTargetAc, $annualAc) |
| 3513 | .'</div>'; |
| 3514 | } |
| 3515 | |
| 3516 | /** |
| 3517 | * Section 3 — Regional (v3). One merged table: weekly productivity + YTD |
| 3518 | * (emitido & aceptado, € and #, vs 2025 and vs objetivo) + acceptance ratio. |
| 3519 | * Rows ordered by emitido YTD (via $sortedRegions), Total last. Bindings per |
| 3520 | * region/total row: |
| 3521 | * Semana : week_budgets, week_amount, Δ vs prev week, week_accepted_amount |
| 3522 | * Emitido YTD : ytd_amount, prev ytd_amount, Δ, ytd_budgets, prev ytd_budgets, |
| 3523 | * Δ, vs Obj. (ytd_amount vs ytd_target) |
| 3524 | * Aceptado YTD : ytd_accepted_amount, prev ytd_accepted_amount, ytd_accepted_budgets, |
| 3525 | * vs Obj. (ytd_accepted_amount vs ytd_target_acc) |
| 3526 | * % aceptación : € (acc/emit), # (acc#/emit#), Ref. 25 €† (full-year 2025 |
| 3527 | * by amount, per region — not produced → n/d*; Total uses |
| 3528 | * prior YTD € ratio) |
| 3529 | */ |
| 3530 | private function wcr_regional_table(array $sortedRegions, array $totals, int $week, $kEuro, $kNum, $numFmt, $deltaPct): string |
| 3531 | { |
| 3532 | $deltaTd = function ($cur, $prev) use ($deltaPct) { |
| 3533 | $d = $deltaPct($cur, $prev); |
| 3534 | |
| 3535 | return '<td class="'.$this->wcr_delta_class($d['sign']).'">'.$d['label'].'</td>'; |
| 3536 | }; |
| 3537 | $objTd = function ($cur, $target) use ($deltaPct) { |
| 3538 | if ($target <= 0) { |
| 3539 | return '<td class="nd">n/d*</td>'; |
| 3540 | } |
| 3541 | $d = $deltaPct($cur, $target); |
| 3542 | |
| 3543 | return '<td class="'.$this->wcr_delta_class($d['sign']).'">'.$d['label'].'</td>'; |
| 3544 | }; |
| 3545 | |
| 3546 | $renderRow = function (array $row, bool $isTotal) use ($kEuro, $kNum, $numFmt, $deltaTd, $objTd) { |
| 3547 | $c = $row['current']; |
| 3548 | $p = $row['previous']; |
| 3549 | $curYtd = (float) $c['ytd_amount']; |
| 3550 | $prevYtd = (float) $p['ytd_amount']; |
| 3551 | $ytdTarget = (float) ($row['ytd_target'] ?? 0); |
| 3552 | $curAcc = (float) $c['ytd_accepted_amount']; |
| 3553 | $prevAcc = (float) $p['ytd_accepted_amount']; |
| 3554 | $ytdTargetAcc = (float) ($row['ytd_target_acc'] ?? 0); |
| 3555 | $curNum = (int) $c['ytd_budgets']; |
| 3556 | $prevNum = (int) $p['ytd_budgets']; |
| 3557 | $curAccNum = (int) $c['ytd_accepted_budgets']; |
| 3558 | |
| 3559 | $pctE = $curYtd > 0 ? ($curAcc / $curYtd) * 100 : null; |
| 3560 | $pctN = $curNum > 0 ? ($curAccNum / $curNum) * 100 : null; |
| 3561 | // Ref. 25 = full-year 2025 acceptance ratio by region. Per-region |
| 3562 | // values are provided manually by Duarte (WCR_ACCEPTANCE_REF_2025); |
| 3563 | // 'nuevo' marks regions opened in 2026 (no 2025 reference). The |
| 3564 | // Total row still surfaces the prior-YTD € acceptance ratio the |
| 3565 | // group data produces. |
| 3566 | if ($isTotal) { |
| 3567 | $ref25 = $prevYtd > 0 |
| 3568 | ? '<td class="y25">'.number_format(($prevAcc / $prevYtd) * 100, 1, ',', '.').'%</td>' |
| 3569 | : '<td class="nd">n/d*</td>'; |
| 3570 | } else { |
| 3571 | $ref = self::WCR_ACCEPTANCE_REF_2025[$row['region'] ?? $row['label'] ?? ''] ?? null; |
| 3572 | if ($ref === null) { |
| 3573 | $ref25 = '<td class="nd">n/d*</td>'; |
| 3574 | } elseif ($ref === 'nuevo') { |
| 3575 | $ref25 = '<td class="y25">nuevo</td>'; |
| 3576 | } else { |
| 3577 | $ref25 = '<td class="y25">'.number_format((float) $ref, 0, ',', '.').'%</td>'; |
| 3578 | } |
| 3579 | } |
| 3580 | |
| 3581 | $tr = $isTotal ? '<tr class="total-row">' : '<tr>'; |
| 3582 | $label = $isTotal ? 'Total' : (string) $row['label']; |
| 3583 | |
| 3584 | return $tr |
| 3585 | .'<td class="name">'.htmlspecialchars($label).'</td>' |
| 3586 | // Semana |
| 3587 | .'<td class="block-sep">'.$numFmt($c['week_budgets']).'</td>' |
| 3588 | .'<td>'.$kEuro($c['week_amount']).'</td>' |
| 3589 | .$deltaTd($c['week_amount'], $p['week_amount']) |
| 3590 | .'<td>'.$kEuro($c['week_accepted_amount']).'</td>' |
| 3591 | // Emitido YTD |
| 3592 | .'<td class="block-sep">'.$kNum($curYtd).'</td>' |
| 3593 | .'<td class="y25">'.$kNum($prevYtd).'</td>' |
| 3594 | .$deltaTd($curYtd, $prevYtd) |
| 3595 | .'<td>'.$numFmt($curNum).'</td>' |
| 3596 | .'<td class="y25">'.$numFmt($prevNum).'</td>' |
| 3597 | .$deltaTd($curNum, $prevNum) |
| 3598 | .$objTd($curYtd, $ytdTarget) |
| 3599 | // Aceptado YTD |
| 3600 | .'<td class="block-sep">'.$kNum($curAcc).'</td>' |
| 3601 | .($prevAcc > 0 ? '<td class="y25">'.$kNum($prevAcc).'</td>' : '<td class="y25 nd">n/d*</td>') |
| 3602 | .'<td>'.$numFmt($curAccNum).'</td>' |
| 3603 | .$objTd($curAcc, $ytdTargetAcc) |
| 3604 | // % aceptación YTD |
| 3605 | .'<td class="block-sep">'.($pctE !== null ? number_format($pctE, 0, ',', '.').'%' : '<span class="nd">n/d*</span>').'</td>' |
| 3606 | .'<td>'.($pctN !== null ? number_format($pctN, 1, ',', '.').'%' : '<span class="nd">n/d*</span>').'</td>' |
| 3607 | .$ref25 |
| 3608 | .'</tr>'; |
| 3609 | }; |
| 3610 | |
| 3611 | $thead = '<thead>' |
| 3612 | .'<tr class="group-head">' |
| 3613 | .'<th></th>' |
| 3614 | .'<th colspan="4">Semana '.$week.'</th>' |
| 3615 | .'<th colspan="7">Emitido YTD</th>' |
| 3616 | .'<th colspan="4">Aceptado YTD</th>' |
| 3617 | .'<th colspan="3">% aceptación YTD</th>' |
| 3618 | .'</tr>' |
| 3619 | .'<tr class="col-head">' |
| 3620 | .'<th>Región</th>' |
| 3621 | .'<th class="block-sep">#</th><th>K€ emit.</th><th class="delta">vs s.'.$week.'-25</th><th>K€ acept.</th>' |
| 3622 | .'<th class="block-sep">K€</th><th>2025</th><th class="delta">vs 25</th><th>#</th><th>2025</th><th class="delta">vs 25</th><th class="delta">vs Obj.</th>' |
| 3623 | .'<th class="block-sep">K€</th><th>2025</th><th>#</th><th class="delta">vs Obj.</th>' |
| 3624 | .'<th class="block-sep">€</th><th>#</th><th>Ref. 25 €†</th>' |
| 3625 | .'</tr></thead>'; |
| 3626 | |
| 3627 | $body = '<tbody>'; |
| 3628 | foreach ($sortedRegions as $r) { |
| 3629 | $body .= $renderRow($r, false); |
| 3630 | } |
| 3631 | $totalRowData = [ |
| 3632 | 'label' => 'Total', |
| 3633 | 'current' => $totals['current'], |
| 3634 | 'previous' => $totals['previous'], |
| 3635 | 'ytd_target' => $totals['ytd_target'] ?? 0, |
| 3636 | 'ytd_target_acc' => $totals['ytd_target_acc'] ?? 0, |
| 3637 | ]; |
| 3638 | $body .= $renderRow($totalRowData, true).'</tbody>'; |
| 3639 | |
| 3640 | return '<table class="regional">'.$thead.$body.'</table>'; |
| 3641 | } |
| 3642 | |
| 3643 | /** |
| 3644 | * Section 4 — Mix por tipo (v3). A single compact table: one emitido row per |
| 3645 | * region + "Total emitido 2026" + "Total emitido 2025" + a "Total aceptado |
| 3646 | * 2026" row. Four type groups (Preventivo / Correctivo / Instalación / |
| 3647 | * Otros; Otros = groups 6/7/8), each with Sem. K€ / YTD K€ / % mix € / % mix #. |
| 3648 | * % mix € = ytd_TYPE_amount / row total YTD €. % mix # = ytd_TYPE_count / |
| 3649 | * row total YTD budget count (# presupuestos del tipo / # emitidos —o |
| 3650 | * aceptados— YTD). |
| 3651 | */ |
| 3652 | private function wcr_mix_table(array $sortedRegions, array $totals, $kNum): string |
| 3653 | { |
| 3654 | // label => field suffix (shared by emitido/aceptado via the prefixes below) |
| 3655 | $types = [ |
| 3656 | 'Preventivo' => 'preventive', |
| 3657 | 'Correctivo' => 'corrective', |
| 3658 | 'Instalación' => 'installation', |
| 3659 | 'Otros' => 'otros', |
| 3660 | ]; |
| 3661 | |
| 3662 | $mixRow = function (string $label, string $trClass, array $data, string $weekPrefix, string $ytdPrefix, string $totalField, string $countTotalField, bool $mixMuted) use ($kNum, $types) { |
| 3663 | $rowTotal = (float) ($data[$totalField] ?? 0); |
| 3664 | $rowCountTotal = (int) ($data[$countTotalField] ?? 0); |
| 3665 | $cells = '<td class="name">'.htmlspecialchars($label).'</td>'; |
| 3666 | foreach ($types as $suffix) { |
| 3667 | $weekVal = (float) ($data[$weekPrefix.$suffix] ?? 0); |
| 3668 | $ytdVal = (float) ($data[$ytdPrefix.$suffix] ?? 0); |
| 3669 | $ytdCount = (int) ($data[$ytdPrefix.$suffix.'_n'] ?? 0); |
| 3670 | $mix = $rowTotal > 0 ? number_format(($ytdVal / $rowTotal) * 100, 0, ',', '.').'%' : '0%'; |
| 3671 | // % mix # = # presupuestos YTD del tipo / # emitidos (o aceptados) YTD. |
| 3672 | $mixNum = $rowCountTotal > 0 ? number_format(($ytdCount / $rowCountTotal) * 100, 0, ',', '.').'%' : '0%'; |
| 3673 | $mixCell = $mixMuted ? '<td class="y25">'.$mix.'</td>' : '<td>'.$mix.'</td>'; |
| 3674 | $mixNumCell = $mixMuted ? '<td class="y25">'.$mixNum.'</td>' : '<td>'.$mixNum.'</td>'; |
| 3675 | $cells .= '<td class="block-sep">'.$kNum($weekVal).'</td>' |
| 3676 | .'<td>'.$kNum($ytdVal).'</td>' |
| 3677 | .$mixCell |
| 3678 | .$mixNumCell; |
| 3679 | } |
| 3680 | $tr = $trClass !== '' ? '<tr class="'.$trClass.'">' : '<tr>'; |
| 3681 | |
| 3682 | return $tr.$cells.'</tr>'; |
| 3683 | }; |
| 3684 | |
| 3685 | $thead = '<thead><tr class="group-head"><th></th>'; |
| 3686 | foreach ($types as $name => $_) { |
| 3687 | $thead .= '<th colspan="4">'.$name.'</th>'; |
| 3688 | } |
| 3689 | $thead .= '</tr><tr class="col-head"><th>Región</th>'; |
| 3690 | foreach ($types as $_) { |
| 3691 | $thead .= '<th class="block-sep">Sem. K€</th><th>YTD K€</th><th>% mix €</th><th>% mix #</th>'; |
| 3692 | } |
| 3693 | $thead .= '</tr></thead>'; |
| 3694 | |
| 3695 | $body = '<tbody>'; |
| 3696 | $body .= '<tr class="rowgroup"><td colspan="17">Emitido — por región y total</td></tr>'; |
| 3697 | foreach ($sortedRegions as $r) { |
| 3698 | $body .= $mixRow((string) $r['label'], '', $r['current'], 'week_', 'ytd_', 'ytd_amount', 'ytd_budgets', true); |
| 3699 | } |
| 3700 | $body .= $mixRow('Total emitido 2026', 'total-row', $totals['current'], 'week_', 'ytd_', 'ytd_amount', 'ytd_budgets', true); |
| 3701 | $body .= $mixRow('Total emitido 2025', 'row-25', $totals['previous'], 'week_', 'ytd_', 'ytd_amount', 'ytd_budgets', false); |
| 3702 | $body .= '<tr class="rowgroup"><td colspan="17">Aceptado</td></tr>'; |
| 3703 | $body .= $mixRow('Total aceptado 2026', 'row-acc', $totals['current'], 'week_accepted_', 'ytd_accepted_', 'ytd_accepted_amount', 'ytd_accepted_budgets', false); |
| 3704 | $body .= '</tbody>'; |
| 3705 | |
| 3706 | return '<table class="regional">'.$thead.$body.'</table>'; |
| 3707 | } |
| 3708 | |
| 3709 | /** |
| 3710 | * Card shell (mockup .card): a bordered, rounded block with an uppercase title and |
| 3711 | * optional sub-label, built as a table so it survives Outlook. $danger tints the |
| 3712 | * title red. |
| 3713 | */ |
| 3714 | private function wcr_card_shell(string $title, bool $danger, string $bodyRows, string $sublabel = '', string $icon = ''): string |
| 3715 | { |
| 3716 | $font = "'Segoe UI',system-ui,Arial,sans-serif"; |
| 3717 | $titleColor = $danger ? '#993C1D' : '#888780'; |
| 3718 | $iconHtml = $icon !== '' ? '<span style="font-size:12px;">'.$icon.'</span> ' : ''; |
| 3719 | $sub = $sublabel !== '' |
| 3720 | ? '<div style="font-family:'.$font.';font-size:11px;color:#888780;margin:-4px 0 8px;">'.$sublabel.'</div>' |
| 3721 | : ''; |
| 3722 | |
| 3723 | return '<table role="presentation" width="100%" cellpadding="0" cellspacing="0" border="0" style="border:1px solid #d3d1c7;border-radius:10px;background-color:#ffffff;">' |
| 3724 | .'<tr><td style="padding:14px 16px;">' |
| 3725 | .'<div style="font-family:'.$font.';font-size:10px;font-weight:600;color:'.$titleColor.';text-transform:uppercase;letter-spacing:0.06em;margin-bottom:10px;">'.$iconHtml.$title.'</div>' |
| 3726 | .$sub |
| 3727 | .'<table role="presentation" width="100%" cellpadding="0" cellspacing="0" border="0">'.$bodyRows.'</table>' |
| 3728 | .'</td></tr></table>'; |
| 3729 | } |
| 3730 | |
| 3731 | /** |
| 3732 | * Top-3 card: ranked rows with name, region, total €, # budgets and a |
| 3733 | * Preventive/Corrective/Installation € pill breakdown (largest first). Each row is |
| 3734 | * a nested table (rank + name block on the left, numbers on the right). |
| 3735 | */ |
| 3736 | private function wcr_top_card(string $title, array $rows, $money): string |
| 3737 | { |
| 3738 | $font = "'Segoe UI',system-ui,Arial,sans-serif"; |
| 3739 | |
| 3740 | if (empty($rows)) { |
| 3741 | $body = '<tr><td style="font-family:'.$font.';font-size:12px;color:#888780;font-style:italic;padding:8px 0;">Sin datos esta semana</td></tr>'; |
| 3742 | |
| 3743 | return $this->wcr_card_shell($title, false, $body, '', '🏆'); |
| 3744 | } |
| 3745 | |
| 3746 | $rankColors = ['#BA7517', '#5f5e5a', '#7B3F20']; // gold / silver / bronze |
| 3747 | $body = ''; |
| 3748 | $i = 0; |
| 3749 | foreach ($rows as $r) { |
| 3750 | $rankColor = $rankColors[$i] ?? '#888780'; |
| 3751 | $region = htmlspecialchars($this->wcr_region_label($r['region'] ?? '')); |
| 3752 | // One shared table per card (cells below) keeps the rank / name / numbers |
| 3753 | // columns aligned across every row. |
| 3754 | $cell = 'font-family:'.$font.';vertical-align:top;padding:9px 0;'.($i > 0 ? 'border-top:1px solid #e8e6df;' : ''); |
| 3755 | $body .= '<tr>' |
| 3756 | .'<td width="24" style="'.$cell.'font-size:17px;font-weight:600;color:'.$rankColor.';text-align:center;">'.($i + 1).'</td>' |
| 3757 | .'<td style="'.$cell.'padding-left:8px;">' |
| 3758 | .'<div style="font-size:13px;font-weight:600;color:#1a1a18;">'.htmlspecialchars((string) $r['name']).'</div>' |
| 3759 | .'<div style="font-size:11px;color:#888780;margin-top:1px;">'.$region.'</div></td>' |
| 3760 | .'<td align="right" style="'.$cell.'text-align:right;white-space:nowrap;padding-left:8px;">' |
| 3761 | .'<div style="font-size:14px;font-weight:600;color:#1a1a18;">'.$money($r['total_amount']).'</div>' |
| 3762 | .'<div style="font-size:11px;color:#888780;margin-top:1px;">'.number_format($r['n_budgets'], 0, ',', '.').' presupuestos</div>' |
| 3763 | .$this->wcr_pills($r, $money) |
| 3764 | .'</td>' |
| 3765 | // Racha — last column, per Duarte's review. |
| 3766 | .'<td align="right" width="58" style="'.$cell.'text-align:right;white-space:nowrap;padding-left:8px;">' |
| 3767 | .$this->wcr_streak_badge($r['weeks_met'] ?? null, 'met').'</td></tr>'; |
| 3768 | $i++; |
| 3769 | } |
| 3770 | |
| 3771 | return $this->wcr_card_shell($title, false, $body, '', '🏆'); |
| 3772 | } |
| 3773 | |
| 3774 | /** |
| 3775 | * The year-by-week charts section: four trend images, each followed by a |
| 3776 | * plain-HTML figure strip and any week notes. |
| 3777 | * |
| 3778 | * The strip is not decoration. It is the accessible/degraded path — a client |
| 3779 | * with remote images disabled shows nothing for the PNG, and without the |
| 3780 | * strip the whole section would silently vanish. It also gives the exact |
| 3781 | * per-week numbers the image can only approximate at 17px per column. |
| 3782 | * |
| 3783 | * @param array $series output of get_weekly_year_series() |
| 3784 | * @param array $imgSrc series key => value for the <img src> attribute |
| 3785 | */ |
| 3786 | private function wcr_year_charts_section(array $series, array $imgSrc, $kNum, $numFmt): string |
| 3787 | { |
| 3788 | $font = "'Segoe UI',system-ui,Arial,sans-serif"; |
| 3789 | $html = ''; |
| 3790 | |
| 3791 | foreach ($series as $key => $s) { |
| 3792 | $isEur = $s['kind'] === 'eur'; |
| 3793 | $fmt = fn ($v) => $isEur ? $kNum($v).'K' : $numFmt($v); |
| 3794 | |
| 3795 | $html .= '<div style="margin:0 0 30px 0;">'; |
| 3796 | |
| 3797 | // ---- title, outside the image -------------------------------- |
| 3798 | // Kept in HTML rather than baked into the PNG so it stays selectable, |
| 3799 | // scales with the reader's font size, and survives blocked images. |
| 3800 | $html .= '<div style="font-family:'.$font.';font-size:15px;font-weight:700;color:#1a1a18;margin:0 0 10px 0;">' |
| 3801 | .htmlspecialchars($s['title']).'</div>'; |
| 3802 | |
| 3803 | // ---- the chart image ----------------------------------------- |
| 3804 | if (! empty($imgSrc[$key])) { |
| 3805 | $alt = htmlspecialchars($s['title']); |
| 3806 | $html .= '<img src="'.$imgSrc[$key].'" alt="'.$alt.'" width="1160" ' |
| 3807 | .'style="display:block;width:100%;max-width:1160px;height:auto;" />'; |
| 3808 | } |
| 3809 | |
| 3810 | // ---- per-week figure strip ----------------------------------- |
| 3811 | $chips = ''; |
| 3812 | foreach ($s['points'] as $p) { |
| 3813 | $val = $fmt($p['value']); |
| 3814 | |
| 3815 | if (! empty($p['in_progress'])) { |
| 3816 | $chips .= '<span style="display:inline-block;font-family:'.$font.';font-size:10.5px;color:#6b6a67;' |
| 3817 | .'background-color:#f2f0ec;border-radius:3px;padding:1px 5px;margin:0 4px 4px 0;white-space:nowrap;">' |
| 3818 | .'<strong>'.$p['week'].'</strong> '.$val.' · en curso</span>'; |
| 3819 | |
| 3820 | continue; |
| 3821 | } |
| 3822 | |
| 3823 | $pctTxt = ''; |
| 3824 | if ($p['pct'] !== null) { |
| 3825 | $pct = (float) $p['pct']; |
| 3826 | $col = $pct >= 0 ? '#1a8a4a' : '#d9482b'; |
| 3827 | $pctTxt = ' <span style="color:'.$col.';font-weight:600;">' |
| 3828 | .($pct >= 0 ? '+' : '−').number_format(abs($pct), 0, ',', '.').'%</span>'; |
| 3829 | } |
| 3830 | |
| 3831 | $chips .= '<span style="display:inline-block;font-family:'.$font.';font-size:10.5px;color:#6b6a67;' |
| 3832 | .'margin:0 8px 4px 0;white-space:nowrap;"><strong>'.$p['week'].'</strong> '.$val.$pctTxt.'</span>'; |
| 3833 | } |
| 3834 | |
| 3835 | $html .= '<div style="margin-top:8px;line-height:1.7;">'.$chips.'</div>'; |
| 3836 | |
| 3837 | // ---- week notes ---------------------------------------------- |
| 3838 | if (! empty($s['notes'])) { |
| 3839 | $noteHtml = ''; |
| 3840 | foreach ($s['notes'] as $nt) { |
| 3841 | $noteHtml .= '<div style="font-family:'.$font.';font-size:11px;color:#a53a2a;margin-top:3px;">' |
| 3842 | .'<strong>S'.$nt['week'].'</strong> — '.htmlspecialchars($nt['note']).'</div>'; |
| 3843 | } |
| 3844 | $html .= '<div style="margin-top:6px;padding:8px 10px;background-color:#fdf1ee;border-left:3px solid #a53a2a;border-radius:0 4px 4px 0;">' |
| 3845 | .$noteHtml.'</div>'; |
| 3846 | } |
| 3847 | |
| 3848 | $html .= '</div>'; |
| 3849 | } |
| 3850 | |
| 3851 | return $html; |
| 3852 | } |
| 3853 | |
| 3854 | /** |
| 3855 | * Weeks-count badge — the last column of each performance table. |
| 3856 | * |
| 3857 | * Counts weeks across the year (not a consecutive run), so the label is |
| 3858 | * deliberately "N sem." and never "racha". |
| 3859 | * |
| 3860 | * Polarity matters: the Top-3 cards are positive tables and report weeks MET |
| 3861 | * (green), while the bajo-objetivo cards are negative tables and must report |
| 3862 | * weeks NOT met (red). Showing "cumpliendo objetivo" on a below-objective |
| 3863 | * table read as a contradiction. |
| 3864 | * |
| 3865 | * `null` prints a dash — no eligible week of that kind yet, which is not the |
| 3866 | * same as zero. |
| 3867 | * |
| 3868 | * @param string $polarity 'met' or 'missed' |
| 3869 | */ |
| 3870 | private function wcr_streak_badge($weeks, string $polarity = 'met'): string |
| 3871 | { |
| 3872 | $font = "'Segoe UI',system-ui,Arial,sans-serif"; |
| 3873 | $missed = $polarity === 'missed'; |
| 3874 | |
| 3875 | if ($weeks === null || (int) $weeks < 1) { |
| 3876 | $emptyTitle = $missed |
| 3877 | ? 'Ninguna semana por debajo de objetivo todavía' |
| 3878 | : 'Ninguna semana cumpliendo objetivo todavía'; |
| 3879 | |
| 3880 | return '<span title="'.$emptyTitle.'" style="font-family:'.$font.';font-size:12px;color:#a8a6a0;">—</span>'; |
| 3881 | } |
| 3882 | |
| 3883 | $weeks = (int) $weeks; |
| 3884 | |
| 3885 | if ($missed) { |
| 3886 | // No icon on the negative side — the card is already a danger card and |
| 3887 | // the red pill carries the meaning on its own. |
| 3888 | $title = $weeks.' semanas sin cumplir el objetivo en lo que va de año'; |
| 3889 | [$bg, $fg, $label] = ['#FAECE7', '#993C1D', (string) $weeks]; |
| 3890 | } else { |
| 3891 | $title = $weeks.' semanas cumpliendo el objetivo en lo que va de año'; |
| 3892 | [$bg, $fg, $label] = ['#E1F5EE', '#0F6E56', '🔥 '.$weeks]; |
| 3893 | } |
| 3894 | |
| 3895 | return '<span title="'.$title.'" ' |
| 3896 | .'style="display:inline-block;font-family:'.$font.';font-size:10px;font-weight:600;' |
| 3897 | .'padding:2px 6px;border-radius:3px;background-color:'.$bg.';color:'.$fg.';white-space:nowrap;">' |
| 3898 | .$label.'</span>'; |
| 3899 | } |
| 3900 | |
| 3901 | private function wcr_pills(array $r, $money): string |
| 3902 | { |
| 3903 | $parts = [ |
| 3904 | ['k' => 'installation', 'l' => 'Inst.', 'bg' => '#E6F1FB', 'c' => '#185FA5'], |
| 3905 | ['k' => 'corrective', 'l' => 'Cor.', 'bg' => '#EEEDFE', 'c' => '#534AB7'], |
| 3906 | ['k' => 'preventive', 'l' => 'Prev.', 'bg' => '#E1F5EE', 'c' => '#0F6E56'], |
| 3907 | ]; |
| 3908 | usort($parts, fn ($a, $b) => ($r[$b['k']] ?? 0) <=> ($r[$a['k']] ?? 0)); |
| 3909 | |
| 3910 | $out = ''; |
| 3911 | foreach ($parts as $p) { |
| 3912 | $v = (float) ($r[$p['k']] ?? 0); |
| 3913 | if ($v <= 0) { |
| 3914 | continue; |
| 3915 | } |
| 3916 | $out .= '<span style="display:inline-block;font-family:\'Segoe UI\',system-ui,Arial,sans-serif;font-size:10px;font-weight:600;' |
| 3917 | .'padding:2px 5px;margin:4px 0 0 4px;border-radius:3px;background-color:'.$p['bg'].';color:'.$p['c'].';">'.$p['l'].' '.$money($v).'</span>'; |
| 3918 | } |
| 3919 | |
| 3920 | if ($out === '') { |
| 3921 | return ''; |
| 3922 | } |
| 3923 | |
| 3924 | return '<div style="margin-top:2px;">'.$out.'</div>'; |
| 3925 | } |
| 3926 | |
| 3927 | /** |
| 3928 | * Below-target card (mockup .card.danger): name, €, # budgets and a red badge with |
| 3929 | * the % gap vs the weekly objective (value <30K€ or volume <50 budgets). Each row |
| 3930 | * is a nested table. |
| 3931 | */ |
| 3932 | private function wcr_under_card(string $title, array $rows, string $mode, $money): string |
| 3933 | { |
| 3934 | $font = "'Segoe UI',system-ui,Arial,sans-serif"; |
| 3935 | $target = $mode === 'value' ? self::WCR_TARGET_VALUE : self::WCR_TARGET_VOLUME; |
| 3936 | |
| 3937 | if (empty($rows)) { |
| 3938 | $body = '<tr><td style="font-family:'.$font.';font-size:12px;color:#888780;font-style:italic;padding:8px 0;">Sin datos</td></tr>'; |
| 3939 | |
| 3940 | return $this->wcr_card_shell($title, true, $body, 'Presupuestos creados en la semana', '⚠️'); |
| 3941 | } |
| 3942 | |
| 3943 | $body = ''; |
| 3944 | $i = 0; |
| 3945 | foreach ($rows as $r) { |
| 3946 | $metric = $mode === 'value' ? (float) $r['total_amount'] : (float) $r['n_budgets']; |
| 3947 | // Use the person's per-week € objective when present (value mode |
| 3948 | // only); the constant $target stays the fallback and covers volume |
| 3949 | // mode, which has no per-# target in the FTE sheet. |
| 3950 | $rowTarget = ($mode === 'value' && isset($r['target_value']) && $r['target_value'] !== null) |
| 3951 | ? (float) $r['target_value'] |
| 3952 | : $target; |
| 3953 | $gap = $rowTarget > 0 ? (($metric - $rowTarget) / $rowTarget) * 100 : 0; |
| 3954 | // Fixed widths on the value / count / badge columns keep them aligned in a |
| 3955 | // straight vertical line across every row. |
| 3956 | $cell = 'font-family:'.$font.';vertical-align:middle;padding:8px 0;'.($i > 0 ? 'border-top:1px solid #e8e6df;' : ''); |
| 3957 | $body .= '<tr>' |
| 3958 | .'<td style="'.$cell.'font-size:13px;font-weight:600;color:#1a1a18;">'.htmlspecialchars((string) $r['name']).'</td>' |
| 3959 | .'<td align="right" width="80" style="'.$cell.'font-size:13px;color:#1a1a18;white-space:nowrap;text-align:right;padding-left:8px;">'.$money($r['total_amount']).'</td>' |
| 3960 | .'<td align="right" width="62" style="'.$cell.'font-size:12px;color:#888780;white-space:nowrap;text-align:right;padding-left:8px;">'.number_format($r['n_budgets'], 0, ',', '.').' pres.</td>' |
| 3961 | .'<td align="right" width="48" style="'.$cell.'white-space:nowrap;text-align:right;padding-left:8px;"><span style="display:inline-block;font-family:'.$font.';font-size:10px;font-weight:600;padding:2px 7px;border-radius:4px;background-color:#FAECE7;color:#993C1D;">'.number_format($gap, 0, ',', '.').'%</span></td>' |
| 3962 | // Racha — last column, per Duarte's review. |
| 3963 | .'<td align="right" width="58" style="'.$cell.'white-space:nowrap;text-align:right;padding-left:8px;">' |
| 3964 | // Negative table: weeks NOT meeting objective. |
| 3965 | .$this->wcr_streak_badge($r['weeks_missed'] ?? null, 'missed').'</td>' |
| 3966 | .'</tr>'; |
| 3967 | $i++; |
| 3968 | } |
| 3969 | |
| 3970 | return $this->wcr_card_shell($title, true, $body, 'Presupuestos creados en la semana', '⚠️'); |
| 3971 | } |
| 3972 | |
| 3973 | private function wcr_region_label(?string $region): string |
| 3974 | { |
| 3975 | $region = (string) $region; |
| 3976 | $map = ['Comunidad Valenciana' => 'Valencia']; |
| 3977 | |
| 3978 | return $map[$region] ?? $region; |
| 3979 | } |
| 3980 | |
| 3981 | public function get_cc_bcc_executive(): ResponseFactory|Response |
| 3982 | { |
| 3983 | |
| 3984 | try { |
| 3985 | |
| 3986 | $result = TblCcBccExecutiveNotifications::get(); |
| 3987 | |
| 3988 | return response(['message' => 'OK', 'data' => $result]); |
| 3989 | |
| 3990 | } catch (\Exception $e) { |
| 3991 | report(AppException::fromException($e, 'GET_CC_BCC_EXECUTIVE_EXCEPTION')); |
| 3992 | |
| 3993 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 3994 | } |
| 3995 | } |
| 3996 | |
| 3997 | public function delete_cc_bcc_executive($id): ResponseFactory|Response |
| 3998 | { |
| 3999 | |
| 4000 | try { |
| 4001 | |
| 4002 | $id = addslashes((string) $id); |
| 4003 | |
| 4004 | TblCcBccExecutiveNotifications::where('id', $id)->delete(); |
| 4005 | |
| 4006 | return response(['message' => 'OK']); |
| 4007 | |
| 4008 | } catch (\Exception $e) { |
| 4009 | report(AppException::fromException($e, 'DELETE_CC_BCC_EXECUTIVE_EXCEPTION')); |
| 4010 | |
| 4011 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 4012 | } |
| 4013 | } |
| 4014 | |
| 4015 | public function update_cc_bcc_executive(Request $request, $id): ResponseFactory|Response |
| 4016 | { |
| 4017 | |
| 4018 | try { |
| 4019 | |
| 4020 | $data = $request->all(); |
| 4021 | $id = addslashes((string) $id); |
| 4022 | |
| 4023 | $x = $this->isEmailValid($data['email']); |
| 4024 | |
| 4025 | if ($x) { |
| 4026 | $result = TblCcBccExecutiveNotifications::where('email', strtolower((string) $data['email']))->count(); |
| 4027 | |
| 4028 | if ($result > 0) { |
| 4029 | return response(['message' => 'KO', 'error' => __('language.email_exists')]); |
| 4030 | } else { |
| 4031 | $data['updated_at'] = date('Y-m-d H:i:s'); |
| 4032 | TblCcBccExecutiveNotifications::where('id', $id)->update($data); |
| 4033 | } |
| 4034 | } else { |
| 4035 | return response(['message' => 'KO', 'error' => __('language.email_invalid')]); |
| 4036 | } |
| 4037 | |
| 4038 | return response(['message' => 'OK']); |
| 4039 | |
| 4040 | } catch (\Exception $e) { |
| 4041 | report(AppException::fromException($e, 'UPDATE_CC_BCC_EXECUTIVE_EXCEPTION')); |
| 4042 | |
| 4043 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 4044 | } |
| 4045 | } |
| 4046 | |
| 4047 | public function create_cc_bcc_executive(Request $request): ResponseFactory|Response |
| 4048 | { |
| 4049 | |
| 4050 | try { |
| 4051 | |
| 4052 | $data = $request->all(); |
| 4053 | |
| 4054 | $x = $this->isEmailValid($data['email']); |
| 4055 | |
| 4056 | if ($x) { |
| 4057 | $result = TblCcBccExecutiveNotifications::where('email', strtolower((string) $data['email']))->count(); |
| 4058 | |
| 4059 | if ($result > 0) { |
| 4060 | return response(['message' => 'KO', 'error' => __('language.email_exists')]); |
| 4061 | } else { |
| 4062 | TblCcBccExecutiveNotifications::create($data); |
| 4063 | } |
| 4064 | } else { |
| 4065 | return response(['message' => 'KO', 'error' => __('language.email_invalid')]); |
| 4066 | } |
| 4067 | |
| 4068 | return response(['message' => 'OK']); |
| 4069 | |
| 4070 | } catch (\Exception $e) { |
| 4071 | report(AppException::fromException($e, 'CREATE_CC_BCC_EXECUTIVE_EXCEPTION')); |
| 4072 | |
| 4073 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 4074 | } |
| 4075 | } |
| 4076 | |
| 4077 | public function update_notification_settings(Request $request): ResponseFactory|Response |
| 4078 | { |
| 4079 | |
| 4080 | try { |
| 4081 | |
| 4082 | $data = $request->all(); |
| 4083 | |
| 4084 | $data['updated_at'] = date('Y-m-d H:i:s'); |
| 4085 | TblNotificationSettings::where('id', 1)->update($data); |
| 4086 | |
| 4087 | return response(['message' => 'OK']); |
| 4088 | |
| 4089 | } catch (\Exception $e) { |
| 4090 | report(AppException::fromException($e, 'UPDATE_NOTIFICATION_SETTINGS_EXCEPTION')); |
| 4091 | |
| 4092 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 4093 | } |
| 4094 | |
| 4095 | } |
| 4096 | |
| 4097 | public function get_notification_settings(): ResponseFactory|Response |
| 4098 | { |
| 4099 | |
| 4100 | try { |
| 4101 | |
| 4102 | $result = TblNotificationSettings::where('id', 1)->first(); |
| 4103 | |
| 4104 | return response(['message' => 'OK', 'data' => $result]); |
| 4105 | |
| 4106 | } catch (\Exception $e) { |
| 4107 | report(AppException::fromException($e, 'GET_NOTIFICATION_SETTINGS_EXCEPTION')); |
| 4108 | |
| 4109 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 4110 | } |
| 4111 | } |
| 4112 | |
| 4113 | public function send_report_logs(Request $request): ResponseFactory|Response |
| 4114 | { |
| 4115 | |
| 4116 | try { |
| 4117 | |
| 4118 | $data = $request->all(); |
| 4119 | |
| 4120 | $toEmail = config('services.sendgrid.to_email'); |
| 4121 | |
| 4122 | $imgpath = file_get_contents(public_path('fireservicetitan.png')); |
| 4123 | |
| 4124 | $body = $data['logs']; |
| 4125 | |
| 4126 | $body .= '<br><br><p>Fire Service Titan</p>'; |
| 4127 | $body .= "<img src='cid:fireservicetitan' style='height: 45px;' />"; |
| 4128 | |
| 4129 | $html = '<!DOCTYPE html>'; |
| 4130 | $html .= '<html>'; |
| 4131 | $html .= '<head>'; |
| 4132 | $html .= '<meta charset="UTF-8">'; |
| 4133 | $html .= '<meta name="viewport" content="width=device-width, initial-scale=1.0">'; |
| 4134 | $html .= '</head>'; |
| 4135 | $html .= '<body>'; |
| 4136 | $html .= $body; |
| 4137 | $html .= '</body>'; |
| 4138 | $html .= '</html>'; |
| 4139 | |
| 4140 | $email = new Mail; |
| 4141 | $subject = 'FST Error report '.date('Y-m-d H:i:s'); |
| 4142 | |
| 4143 | $email->setFrom('fire@fire.es', 'Fire Service Titan'); |
| 4144 | $email->setSubject($subject); |
| 4145 | |
| 4146 | $email->addTo($toEmail); |
| 4147 | $email->addContent('text/html', $html); |
| 4148 | |
| 4149 | $email->addAttachment( |
| 4150 | $imgpath, |
| 4151 | 'image/png', |
| 4152 | 'fireservicetitan.png', |
| 4153 | 'inline', |
| 4154 | 'fireservicetitan' |
| 4155 | ); |
| 4156 | |
| 4157 | $sendgrid = new \SendGrid(config('services.sendgrid.api_key')); |
| 4158 | |
| 4159 | try { |
| 4160 | $response = $sendgrid->send($email); |
| 4161 | SendgridLogger::log($email, $response); |
| 4162 | } catch (\Throwable $sendException) { |
| 4163 | SendgridLogger::logException($email, $sendException); |
| 4164 | throw $sendException; |
| 4165 | } |
| 4166 | |
| 4167 | if ($response->statusCode() == 202) { |
| 4168 | Log::channel('email_log')->info('ID:'.$toEmail.' - REPORT LOG EMAIL SENT'); |
| 4169 | } |
| 4170 | |
| 4171 | return response(['message' => 'OK']); |
| 4172 | |
| 4173 | } catch (\Exception $e) { |
| 4174 | report(AppException::fromException($e, 'SEND_REPORT_LOGS_EXCEPTION')); |
| 4175 | |
| 4176 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 4177 | } |
| 4178 | } |
| 4179 | |
| 4180 | public function get_emails_acceptance_notifications($companyId): ResponseFactory|Response |
| 4181 | { |
| 4182 | |
| 4183 | try { |
| 4184 | |
| 4185 | $companyId = addslashes((string) $companyId); |
| 4186 | |
| 4187 | $cc = TblCcAcceptanceNotifications::where('company_id', $companyId)->get(); |
| 4188 | $to = TblToAcceptanceNotifications::where('company_id', $companyId)->get(); |
| 4189 | |
| 4190 | return response(['message' => 'OK', 'cc' => $cc, 'to' => $to]); |
| 4191 | |
| 4192 | } catch (\Exception $e) { |
| 4193 | report(AppException::fromException($e, 'GET_EMAILS_ACCEPTANCE_NOTIFICATIONS_EXCEPTION')); |
| 4194 | |
| 4195 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 4196 | } |
| 4197 | } |
| 4198 | |
| 4199 | public function create_to_email_acceptance(Request $request): ResponseFactory|Response |
| 4200 | { |
| 4201 | |
| 4202 | try { |
| 4203 | |
| 4204 | $data = $request->all(); |
| 4205 | |
| 4206 | $x = $this->isEmailValid($data['email']); |
| 4207 | |
| 4208 | if ($x) { |
| 4209 | $result = TblToAcceptanceNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count(); |
| 4210 | |
| 4211 | if ($result > 0) { |
| 4212 | return response(['message' => 'KO', 'error' => __('language.email_exists')]); |
| 4213 | } else { |
| 4214 | TblToAcceptanceNotifications::create($data); |
| 4215 | } |
| 4216 | } else { |
| 4217 | return response(['message' => 'KO', 'error' => __('language.email_invalid')]); |
| 4218 | } |
| 4219 | |
| 4220 | return response(['message' => 'OK']); |
| 4221 | |
| 4222 | } catch (\Exception $e) { |
| 4223 | report(AppException::fromException($e, 'CREATE_TO_EMAIL_ACCEPTANCE_EXCEPTION')); |
| 4224 | |
| 4225 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 4226 | } |
| 4227 | } |
| 4228 | |
| 4229 | public function delete_to_email_acceptance($id): ResponseFactory|Response |
| 4230 | { |
| 4231 | |
| 4232 | try { |
| 4233 | |
| 4234 | $id = addslashes((string) $id); |
| 4235 | |
| 4236 | TblToAcceptanceNotifications::where('id', $id)->delete(); |
| 4237 | |
| 4238 | return response(['message' => 'OK']); |
| 4239 | |
| 4240 | } catch (\Exception $e) { |
| 4241 | report(AppException::fromException($e, 'DELETE_TO_EMAIL_ACCEPTANCE_EXCEPTION')); |
| 4242 | |
| 4243 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 4244 | } |
| 4245 | } |
| 4246 | |
| 4247 | public function update_to_email_acceptance(Request $request, $id): ResponseFactory|Response |
| 4248 | { |
| 4249 | |
| 4250 | try { |
| 4251 | |
| 4252 | $data = $request->all(); |
| 4253 | $id = addslashes((string) $id); |
| 4254 | |
| 4255 | $x = $this->isEmailValid($data['email']); |
| 4256 | |
| 4257 | if ($x) { |
| 4258 | $to = TblToAcceptanceNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count(); |
| 4259 | $cc = TblCcAcceptanceNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count(); |
| 4260 | |
| 4261 | if ($to > 0 || $cc > 0) { |
| 4262 | return response(['message' => 'KO', 'error' => __('language.email_exists')]); |
| 4263 | } else { |
| 4264 | $data['updated_at'] = date('Y-m-d H:i:s'); |
| 4265 | TblToAcceptanceNotifications::where('id', $id)->update($data); |
| 4266 | } |
| 4267 | } else { |
| 4268 | return response(['message' => 'KO', 'error' => __('language.email_invalid')]); |
| 4269 | } |
| 4270 | |
| 4271 | return response(['message' => 'OK']); |
| 4272 | |
| 4273 | } catch (\Exception $e) { |
| 4274 | report(AppException::fromException($e, 'UPDATE_TO_EMAIL_ACCEPTANCE_EXCEPTION')); |
| 4275 | |
| 4276 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 4277 | } |
| 4278 | } |
| 4279 | |
| 4280 | public function create_cc_email_acceptance(Request $request): ResponseFactory|Response |
| 4281 | { |
| 4282 | |
| 4283 | try { |
| 4284 | |
| 4285 | $data = $request->all(); |
| 4286 | |
| 4287 | $x = $this->isEmailValid($data['email']); |
| 4288 | |
| 4289 | if ($x) { |
| 4290 | $result = TblCcAcceptanceNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count(); |
| 4291 | |
| 4292 | if ($result > 0) { |
| 4293 | return response(['message' => 'KO', 'error' => __('language.email_exists')]); |
| 4294 | } else { |
| 4295 | TblCcAcceptanceNotifications::create($data); |
| 4296 | } |
| 4297 | } else { |
| 4298 | return response(['message' => 'KO', 'error' => __('language.email_invalid')]); |
| 4299 | } |
| 4300 | |
| 4301 | return response(['message' => 'OK']); |
| 4302 | |
| 4303 | } catch (\Exception $e) { |
| 4304 | report(AppException::fromException($e, 'CREATE_CC_EMAIL_ACCEPTANCE_EXCEPTION')); |
| 4305 | |
| 4306 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 4307 | } |
| 4308 | } |
| 4309 | |
| 4310 | public function delete_cc_email_acceptance($id): ResponseFactory|Response |
| 4311 | { |
| 4312 | |
| 4313 | try { |
| 4314 | |
| 4315 | $id = addslashes((string) $id); |
| 4316 | |
| 4317 | TblCcAcceptanceNotifications::where('id', $id)->delete(); |
| 4318 | |
| 4319 | return response(['message' => 'OK']); |
| 4320 | |
| 4321 | } catch (\Exception $e) { |
| 4322 | report(AppException::fromException($e, 'DELETE_CC_EMAIL_ACCEPTANCE_EXCEPTION')); |
| 4323 | |
| 4324 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 4325 | } |
| 4326 | } |
| 4327 | |
| 4328 | public function update_cc_email_acceptance(Request $request, $id): ResponseFactory|Response |
| 4329 | { |
| 4330 | |
| 4331 | try { |
| 4332 | |
| 4333 | $data = $request->all(); |
| 4334 | $id = addslashes((string) $id); |
| 4335 | |
| 4336 | $x = $this->isEmailValid($data['email']); |
| 4337 | |
| 4338 | if ($x) { |
| 4339 | $cc = TblCcAcceptanceNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count(); |
| 4340 | $to = TblToAcceptanceNotifications::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count(); |
| 4341 | |
| 4342 | if ($cc > 0 || $to > 0) { |
| 4343 | return response(['message' => 'KO', 'error' => __('language.email_exists')]); |
| 4344 | } else { |
| 4345 | $data['updated_at'] = date('Y-m-d H:i:s'); |
| 4346 | TblCcAcceptanceNotifications::where('id', $id)->update($data); |
| 4347 | } |
| 4348 | } else { |
| 4349 | return response(['message' => 'KO', 'error' => __('language.email_invalid')]); |
| 4350 | } |
| 4351 | |
| 4352 | return response(['message' => 'OK']); |
| 4353 | |
| 4354 | } catch (\Exception $e) { |
| 4355 | report(AppException::fromException($e, 'UPDATE_CC_EMAIL_ACCEPTANCE_EXCEPTION')); |
| 4356 | |
| 4357 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 4358 | } |
| 4359 | } |
| 4360 | |
| 4361 | public function list_last_follow_up_date($companyId): ResponseFactory|Response |
| 4362 | { |
| 4363 | |
| 4364 | try { |
| 4365 | |
| 4366 | $companyId = addslashes((string) $companyId); |
| 4367 | |
| 4368 | $result = TblLastFollowUpDate::where('company_id', $companyId)->orderBy('row_id', 'ASC')->get(); |
| 4369 | |
| 4370 | $data = []; |
| 4371 | |
| 4372 | for ($i = 0; $i < count($result); $i++) { |
| 4373 | $data[$result[$i]->row_id]['row_id'] = $result[$i]->row_id; |
| 4374 | $data[$result[$i]->row_id]['last_follow_up_date'] = $result[$i]->last_follow_up_date; |
| 4375 | if ($result[$i]->budget_type_id != null) { |
| 4376 | $data[$result[$i]->row_id]['budget_types'][] = $result[$i]->budget_type_id; |
| 4377 | } |
| 4378 | } |
| 4379 | |
| 4380 | return response(['message' => 'OK', 'data' => array_values($data)]); |
| 4381 | |
| 4382 | } catch (\Exception $e) { |
| 4383 | report(AppException::fromException($e, 'LIST_LAST_FOLLOW_UP_DATE_EXCEPTION')); |
| 4384 | |
| 4385 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 4386 | } |
| 4387 | |
| 4388 | } |
| 4389 | |
| 4390 | public function update_last_follow_up_date_working_days(Request $request) |
| 4391 | { |
| 4392 | |
| 4393 | try { |
| 4394 | |
| 4395 | $data = $request->all(); |
| 4396 | $companyId = addslashes((string) $data['company_id']); |
| 4397 | $rowId = addslashes((string) $data['row_id']); |
| 4398 | |
| 4399 | unset($data['company_id']); |
| 4400 | unset($data['row_id']); |
| 4401 | |
| 4402 | $data['updated_at'] = date('Y-m-d H:i:s'); |
| 4403 | |
| 4404 | TblLastFollowUpDate::where('company_id', $companyId)->where('row_id', $rowId)->update($data); |
| 4405 | |
| 4406 | return $this->list_last_follow_up_date($companyId); |
| 4407 | |
| 4408 | } catch (\Exception $e) { |
| 4409 | report(AppException::fromException($e, 'UPDATE_LAST_FOLLOW_UP_DATE_WORKING_DAYS_EXCEPTION')); |
| 4410 | |
| 4411 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 4412 | } |
| 4413 | |
| 4414 | } |
| 4415 | |
| 4416 | public function update_last_follow_up_date(Request $request) |
| 4417 | { |
| 4418 | |
| 4419 | try { |
| 4420 | |
| 4421 | $data = $request->all(); |
| 4422 | $companyId = addslashes((string) $data['company_id']); |
| 4423 | $rowId = addslashes((string) $data['row_id']); |
| 4424 | unset($data['company_id']); |
| 4425 | unset($data['row_id']); |
| 4426 | |
| 4427 | $previousData = TblLastFollowUpDate::where('company_id', $companyId)->where('row_id', $rowId)->first(); |
| 4428 | |
| 4429 | $lastBudgetTypeId = end($data['budget_type_id']); |
| 4430 | $lastFollowUpDate = TblLastFollowUpDate::where('company_id', $companyId)->where('budget_type_id', $lastBudgetTypeId)->first(); |
| 4431 | |
| 4432 | TblLastFollowUpDate::where('company_id', $companyId)->whereIn('budget_type_id', $data['budget_type_id'])->delete(); |
| 4433 | TblLastFollowUpDate::where('company_id', $companyId)->where('row_id', $rowId)->delete(); |
| 4434 | |
| 4435 | foreach ($data['budget_type_id'] as $budgetTypeId) { |
| 4436 | TblLastFollowUpDate::create( |
| 4437 | [ |
| 4438 | 'company_id' => $companyId, |
| 4439 | 'row_id' => $rowId, |
| 4440 | 'budget_type_id' => $budgetTypeId, |
| 4441 | 'last_follow_up_date' => $previousData->last_follow_up_date, |
| 4442 | 'updated_by' => $data['updated_by'], |
| 4443 | 'updated_at' => date('Y-m-d H:i:s'), |
| 4444 | ] |
| 4445 | ); |
| 4446 | } |
| 4447 | |
| 4448 | if ($lastFollowUpDate != null) { |
| 4449 | if ($lastFollowUpDate->budget_type_id != null) { |
| 4450 | TblLastFollowUpDate::create( |
| 4451 | [ |
| 4452 | 'company_id' => $companyId, |
| 4453 | 'row_id' => $lastFollowUpDate->row_id, |
| 4454 | 'budget_type_id' => null, |
| 4455 | 'last_follow_up_date' => $lastFollowUpDate->last_follow_up_date, |
| 4456 | 'created_by' => $data['updated_by'], |
| 4457 | ] |
| 4458 | ); |
| 4459 | } |
| 4460 | } |
| 4461 | |
| 4462 | return $this->list_last_follow_up_date($companyId); |
| 4463 | |
| 4464 | } catch (\Exception $e) { |
| 4465 | report(AppException::fromException($e, 'UPDATE_LAST_FOLLOW_UP_DATE_EXCEPTION')); |
| 4466 | |
| 4467 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 4468 | } |
| 4469 | |
| 4470 | } |
| 4471 | |
| 4472 | public function delete_last_follow_up_date(Request $request) |
| 4473 | { |
| 4474 | |
| 4475 | try { |
| 4476 | |
| 4477 | $data = $request->all(); |
| 4478 | $companyId = addslashes((string) $data['company_id']); |
| 4479 | $rowId = addslashes((string) $data['row_id']); |
| 4480 | unset($data['company_id']); |
| 4481 | unset($data['row_id']); |
| 4482 | |
| 4483 | $count = TblLastFollowUpDate::where('company_id', $companyId)->where('row_id', $rowId)->count(); |
| 4484 | |
| 4485 | if ($count > 1) { |
| 4486 | TblLastFollowUpDate::where('company_id', $companyId)->where('budget_type_id', $data['budget_type_id'])->delete(); |
| 4487 | } else { |
| 4488 | TblLastFollowUpDate::where('company_id', $companyId)->where('row_id', $rowId)->update(['budget_type_id' => null]); |
| 4489 | } |
| 4490 | |
| 4491 | return $this->list_last_follow_up_date($companyId); |
| 4492 | |
| 4493 | } catch (\Exception $e) { |
| 4494 | report(AppException::fromException($e, 'DELETE_LAST_FOLLOW_UP_DATE_EXCEPTION')); |
| 4495 | |
| 4496 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 4497 | } |
| 4498 | |
| 4499 | } |
| 4500 | } |