Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
| Total | |
72.04% |
237 / 329 |
|
25.00% |
5 / 20 |
CRAP | |
0.00% |
0 / 1 |
| AnalyticsRedesign | |
72.04% |
237 / 329 |
|
25.00% |
5 / 20 |
282.12 | |
0.00% |
0 / 1 |
| __construct | |
0.00% |
0 / 15 |
|
0.00% |
0 / 1 |
20 | |||
| normalizeRegion | |
100.00% |
4 / 4 |
|
100.00% |
1 / 1 |
1 | |||
| scopeCompanyIds | |
88.89% |
8 / 9 |
|
0.00% |
0 / 1 |
5.03 | |||
| companyIdsForRegion | |
0.00% |
0 / 14 |
|
0.00% |
0 / 1 |
30 | |||
| companyInSql | |
100.00% |
2 / 2 |
|
100.00% |
1 / 1 |
2 | |||
| standardFilters | |
27.27% |
12 / 44 |
|
0.00% |
0 / 1 |
142.63 | |||
| excludeReplacedQuotesSql | |
50.00% |
3 / 6 |
|
0.00% |
0 / 1 |
4.12 | |||
| excludeSystemCommercialsSql | |
86.67% |
13 / 15 |
|
0.00% |
0 / 1 |
4.04 | |||
| yearWindow | |
69.23% |
9 / 13 |
|
0.00% |
0 / 1 |
8.43 | |||
| windowRange | |
100.00% |
7 / 7 |
|
100.00% |
1 / 1 |
3 | |||
| ok | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| ko | |
0.00% |
0 / 2 |
|
0.00% |
0 / 1 |
2 | |||
| list_productivity_region | |
83.33% |
10 / 12 |
|
0.00% |
0 / 1 |
2.02 | |||
| list_productivity_commercial | |
84.62% |
11 / 13 |
|
0.00% |
0 / 1 |
2.01 | |||
| list_mix_group | |
84.62% |
11 / 13 |
|
0.00% |
0 / 1 |
3.03 | |||
| mixForWindow | |
85.71% |
42 / 49 |
|
0.00% |
0 / 1 |
11.35 | |||
| mixDelta | |
100.00% |
15 / 15 |
|
100.00% |
1 / 1 |
3 | |||
| list_effectiveness | |
92.00% |
23 / 25 |
|
0.00% |
0 / 1 |
7.03 | |||
| list_backlog | |
92.59% |
25 / 27 |
|
0.00% |
0 / 1 |
6.01 | |||
| analytics_filters | |
95.35% |
41 / 43 |
|
0.00% |
0 / 1 |
5 | |||
| 1 | <?php |
| 2 | |
| 3 | namespace App\Http\Controllers; |
| 4 | |
| 5 | use App\Exceptions\AppException; |
| 6 | use App\Services\ResultCache; |
| 7 | use App\Services\UserCompanies; |
| 8 | use Illuminate\Contracts\Routing\ResponseFactory; |
| 9 | use Illuminate\Http\Request; |
| 10 | use Illuminate\Http\Response as HttpResponse; |
| 11 | use Illuminate\Support\Facades\App; |
| 12 | use Illuminate\Support\Facades\DB; |
| 13 | use Illuminate\Support\Facades\Schema; |
| 14 | |
| 15 | /** |
| 16 | * Analytics redesign (2026-07 proposal): the 5-table / 5-filter rewrite of the |
| 17 | * quotations analytics page. Lives in its own controller so the legacy |
| 18 | * endpoints in Quotations.php keep working during an incremental rollout. |
| 19 | * |
| 20 | * Endpoints (all POST, same auth group as the legacy analytics routes): |
| 21 | * 1. list_productivity_region — Table 1 "Productividad por región" |
| 22 | * 2. list_productivity_commercial — Table 2 "Productividad por comercial" |
| 23 | * 3. list_mix_group — Table 3 "Mix por grupo" (period compare) |
| 24 | * 4. list_effectiveness — Table 4 "Efectividad / Velocidad" |
| 25 | * 5. list_backlog — Table 5 "Backlog de solicitudes" (NEW) |
| 26 | * + analytics_filters — populate all filter dropdowns in one call |
| 27 | * |
| 28 | * Cross-cutting rules (match the weekly report): |
| 29 | * - issued → always by sent_date (last send; issue_date = ERP budget date) |
| 30 | * - accepted → always by acceptance_date |
| 31 | * - every table returns BOTH # (count) and € (amount); the frontend "# / €" |
| 32 | * toggle just picks which to show — no re-query needed. |
| 33 | * - tables 3/4/5 accept an optional base/current period window (PBI-style). |
| 34 | * |
| 35 | * All mappings (regions, budget-type groups, system accounts, AI origin, |
| 36 | * backlog buckets) come from config/analytics.php — see the PHASE-0 notes there. |
| 37 | */ |
| 38 | class AnalyticsRedesign extends Controller |
| 39 | { |
| 40 | private $locale; |
| 41 | |
| 42 | private $userId; |
| 43 | |
| 44 | private $region; |
| 45 | |
| 46 | /** @var array<int,int> */ |
| 47 | private array $companyIds; |
| 48 | |
| 49 | public function __construct() |
| 50 | { |
| 51 | $this->locale = request()->header('Locale-Id'); |
| 52 | $this->userId = request()->header('User-Id'); |
| 53 | $this->region = request()->header('Region'); |
| 54 | |
| 55 | App::setLocale($this->locale); |
| 56 | |
| 57 | $this->companyIds = []; |
| 58 | |
| 59 | if ($this->region !== null && $this->region !== '' && $this->region !== 'All') { |
| 60 | $this->region = urldecode((string) $this->region); |
| 61 | |
| 62 | $query = 'SELECT b.company_id |
| 63 | FROM tbl_company_users a |
| 64 | LEFT JOIN tbl_companies b ON a.company_id = b.company_id |
| 65 | WHERE a.user_id = ? AND b.region = ?'; |
| 66 | |
| 67 | $rows = DB::select($query, [intval($this->userId), $this->region]); |
| 68 | $this->companyIds = collect($rows)->pluck('company_id')->toArray(); |
| 69 | } else { |
| 70 | $this->companyIds = request()->attributes->get( |
| 71 | 'user_company_ids', |
| 72 | UserCompanies::forUser((int) $this->userId) |
| 73 | ); |
| 74 | } |
| 75 | } |
| 76 | |
| 77 | /* ===================================================================== |
| 78 | | Shared helpers |
| 79 | * ===================================================================== */ |
| 80 | |
| 81 | /** Accent/space/case-insensitive normalisation for region name matching. */ |
| 82 | private function normalizeRegion(string $value): string |
| 83 | { |
| 84 | $value = mb_strtolower(trim($value)); |
| 85 | $from = ['á', 'à', 'ä', 'â', 'é', 'è', 'ë', 'ê', 'í', 'ì', 'ï', 'î', 'ó', 'ò', 'ö', 'ô', 'ú', 'ù', 'ü', 'û', 'ñ']; |
| 86 | $to = ['a', 'a', 'a', 'a', 'e', 'e', 'e', 'e', 'i', 'i', 'i', 'i', 'o', 'o', 'o', 'o', 'u', 'u', 'u', 'u', 'n']; |
| 87 | |
| 88 | return str_replace($from, $to, $value); |
| 89 | } |
| 90 | |
| 91 | /** |
| 92 | * Effective company-id list for a request, honouring an optional |
| 93 | * company_id param and an optional region code filter. Always intersected |
| 94 | * with the caller's accessible companies (never widens scope). |
| 95 | * |
| 96 | * @return array<int,int> |
| 97 | */ |
| 98 | private function scopeCompanyIds(array $data): array |
| 99 | { |
| 100 | $ids = $this->companyIds; |
| 101 | |
| 102 | $companyId = isset($data['company_id']) ? (int) $data['company_id'] : 0; |
| 103 | if ($companyId !== 0) { |
| 104 | $ids = array_values(array_filter($ids, fn ($id) => (int) $id === $companyId)); |
| 105 | if (empty($ids)) { |
| 106 | $ids = [$companyId]; // caller explicitly asked; scope query still guards |
| 107 | } |
| 108 | } |
| 109 | |
| 110 | if (! empty($data['region'])) { |
| 111 | $ids = array_values(array_intersect($ids, $this->companyIdsForRegion((string) $data['region'], $ids))); |
| 112 | } |
| 113 | |
| 114 | return array_map('intval', $ids); |
| 115 | } |
| 116 | |
| 117 | /** |
| 118 | * Company ids (within $within) whose tbl_companies.region maps to $regionCode. |
| 119 | * |
| 120 | * @param array<int,int> $within |
| 121 | * @return array<int,int> |
| 122 | */ |
| 123 | private function companyIdsForRegion(string $regionCode, array $within): array |
| 124 | { |
| 125 | if (empty($within)) { |
| 126 | return []; |
| 127 | } |
| 128 | |
| 129 | $config = config("analytics.regions.{$regionCode}"); |
| 130 | if (! $config) { |
| 131 | return $within; // unknown code → don't filter (fail open, logged upstream) |
| 132 | } |
| 133 | |
| 134 | $aliases = array_map(fn ($a) => $this->normalizeRegion((string) $a), $config['aliases'] ?? []); |
| 135 | $aliases[] = $this->normalizeRegion((string) ($config['label'] ?? '')); |
| 136 | |
| 137 | $inIds = implode(',', array_map('intval', $within)); |
| 138 | $rows = DB::select("SELECT company_id, region FROM tbl_companies WHERE company_id IN ({$inIds})"); |
| 139 | |
| 140 | $matched = []; |
| 141 | foreach ($rows as $r) { |
| 142 | if (in_array($this->normalizeRegion((string) $r->region), $aliases, true)) { |
| 143 | $matched[] = (int) $r->company_id; |
| 144 | } |
| 145 | } |
| 146 | |
| 147 | return $matched; |
| 148 | } |
| 149 | |
| 150 | /** Comma-safe company-id SQL fragment for a scoped id list. */ |
| 151 | private function companyInSql(array $ids): string |
| 152 | { |
| 153 | $ids = array_filter(array_map('intval', $ids)); |
| 154 | |
| 155 | return empty($ids) ? '0' : implode(',', $ids); |
| 156 | } |
| 157 | |
| 158 | /** tbl_budget_status 20 = "Rechazado-duplicado" - where a replaced quote lands. */ |
| 159 | private const STATUS_REJECTED_DUPLICATE = 20; |
| 160 | |
| 161 | /** |
| 162 | * Standard filter WHERE fragment shared by all tables. Assumes the query |
| 163 | * aliases quotations as `q` and joins bt (budget_types), bs (budget_status), |
| 164 | * ct (customer_types), s (sources). |
| 165 | */ |
| 166 | private function standardFilters(array $data): string |
| 167 | { |
| 168 | $where = ''; |
| 169 | |
| 170 | if (! empty($data['source'])) { |
| 171 | $where .= " AND s.name = '".addslashes((string) $data['source'])."'"; |
| 172 | } |
| 173 | if (! empty($data['commercial'])) { |
| 174 | $where .= " AND q.commercial = '".addslashes((string) $data['commercial'])."'"; |
| 175 | } |
| 176 | if (! empty($data['budget_type'])) { |
| 177 | $where .= ' AND bt.budget_type_id = '.(int) $data['budget_type']; |
| 178 | } |
| 179 | if (! empty($data['budget_type_group'])) { |
| 180 | // FIRE-XXXX (2026-09-08): FE sends the group KEY (e.g. "instalacion"), |
| 181 | // not the DB id. Resolve via config/analytics.budget_type_groups to |
| 182 | // the list of budget_type_group_id values, then IN(...) filter. |
| 183 | // Previous code cast the string to int → 0, which never matched, so |
| 184 | // all five analytics tables returned "Sin datos" whenever this |
| 185 | // filter was set. Reported by Duarte 2026-09-07. |
| 186 | $groupKey = (string) $data['budget_type_group']; |
| 187 | $groups = config('analytics.budget_type_groups', []); |
| 188 | $selected = collect($groups)->firstWhere('key', $groupKey); |
| 189 | |
| 190 | if ($selected && ! empty($selected['ids'])) { |
| 191 | $ids = array_map('intval', (array) $selected['ids']); |
| 192 | $otherKey = config('analytics.other_group_key', 'otros'); |
| 193 | |
| 194 | if ($groupKey === $otherKey) { |
| 195 | // "Otros" is a catch-all: the enumerated ids AND anything |
| 196 | // else not bucketed elsewhere (so new groups added to |
| 197 | // tbl_budget_type_groups don't silently disappear). |
| 198 | $enumerated = collect($groups) |
| 199 | ->where('key', '!=', $otherKey) |
| 200 | ->pluck('ids') |
| 201 | ->flatten() |
| 202 | ->map(fn ($v) => (int) $v) |
| 203 | ->all(); |
| 204 | $inList = implode(',', $ids); |
| 205 | $notInList = $enumerated ? implode(',', $enumerated) : '0'; |
| 206 | $where .= " AND (bt.budget_type_group_id IN ($inList)" |
| 207 | ." OR bt.budget_type_group_id NOT IN ($notInList))"; |
| 208 | } else { |
| 209 | $where .= ' AND bt.budget_type_group_id IN ('.implode(',', $ids).')'; |
| 210 | } |
| 211 | } |
| 212 | } |
| 213 | if (! empty($data['budget_status'])) { |
| 214 | $where .= ' AND bs.budget_status_id = '.(int) $data['budget_status']; |
| 215 | } |
| 216 | if (! empty($data['client_type'])) { |
| 217 | $where .= ' AND ct.customer_type_id = '.(int) $data['client_type']; |
| 218 | } |
| 219 | |
| 220 | // Advanced: amount range (absorbs the legacy "order size" table). |
| 221 | if (isset($data['min_amount']) && $data['min_amount'] !== '') { |
| 222 | $where .= ' AND q.amount >= '.(float) $data['min_amount']; |
| 223 | } |
| 224 | if (isset($data['max_amount']) && $data['max_amount'] !== '') { |
| 225 | $where .= ' AND q.amount < '.(float) $data['max_amount']; |
| 226 | } |
| 227 | |
| 228 | // Origin toggle: Humano / IA. |
| 229 | if (! empty($data['origin'])) { |
| 230 | $aiValues = collect(config('analytics.ai_created_by_values', ['IA'])) |
| 231 | ->map(fn ($v) => "'".addslashes(mb_strtolower((string) $v))."'") |
| 232 | ->implode(','); |
| 233 | if (mb_strtolower((string) $data['origin']) === 'ia') { |
| 234 | $where .= " AND LOWER(q.created_by) IN ({$aiValues})"; |
| 235 | } elseif (mb_strtolower((string) $data['origin']) === 'human') { |
| 236 | $where .= " AND (q.created_by IS NULL OR LOWER(q.created_by) NOT IN ({$aiValues}))"; |
| 237 | } |
| 238 | } |
| 239 | |
| 240 | $where .= $this->excludeReplacedQuotesSql(); |
| 241 | |
| 242 | return $where; |
| 243 | } |
| 244 | |
| 245 | /** |
| 246 | * Drop quotes that were superseded via "Replace quotation". |
| 247 | * |
| 248 | * Both halves are required: status 20 alone is also set by the AI |
| 249 | * duplicate detector on quotes nobody replaced, and a replaced_quotes row |
| 250 | * alone would hide a quote whose status was later moved back by hand. |
| 251 | * Excluding only the intersection keeps this to the sustituto case the |
| 252 | * reports care about - a renegotiated job counted once, not twice. |
| 253 | * |
| 254 | * Table existence is checked because this also runs where the migration |
| 255 | * has not been applied yet; without the guard every analytics table would |
| 256 | * fail with a missing-table error instead of showing data. |
| 257 | */ |
| 258 | private function excludeReplacedQuotesSql(): string |
| 259 | { |
| 260 | try { |
| 261 | if (! Schema::hasTable('replaced_quotes')) { |
| 262 | return ''; |
| 263 | } |
| 264 | } catch (\Throwable $e) { |
| 265 | return ''; |
| 266 | } |
| 267 | |
| 268 | return ' AND NOT (q.budget_status_id = '.self::STATUS_REJECTED_DUPLICATE |
| 269 | .' AND EXISTS (SELECT 1 FROM replaced_quotes rq WHERE rq.old_quotation_id = q.id)) '; |
| 270 | } |
| 271 | |
| 272 | /** Exclude bot/admin/API commercials (denylist + is_system_account flag). */ |
| 273 | private function excludeSystemCommercialsSql(): string |
| 274 | { |
| 275 | $names = collect(config('analytics.system_account_names', [])) |
| 276 | ->map(fn ($n) => "'".addslashes(trim(mb_strtolower((string) $n)))."'") |
| 277 | ->unique() |
| 278 | ->implode(','); |
| 279 | |
| 280 | $sql = " AND q.commercial IS NOT NULL AND TRIM(q.commercial) <> '' "; |
| 281 | if ($names !== '') { |
| 282 | $sql .= " AND TRIM(LOWER(q.commercial)) NOT IN ({$names}) "; |
| 283 | } |
| 284 | |
| 285 | // The is_system_account flag is the authoritative exclusion, but only |
| 286 | // apply it when the migration has actually run — otherwise the whole |
| 287 | // query would fail with "Unknown column" and the table shows no data. |
| 288 | // Guard the subquery against NULL/'' names too: a NULL inside a |
| 289 | // NOT IN (...) list makes the predicate reject EVERY row (SQL trap). |
| 290 | $hasFlag = false; |
| 291 | try { |
| 292 | $hasFlag = Schema::hasColumn('tbl_users', 'is_system_account'); |
| 293 | } catch (\Throwable $e) { |
| 294 | $hasFlag = false; |
| 295 | } |
| 296 | |
| 297 | if ($hasFlag) { |
| 298 | $sql .= " AND q.commercial NOT IN ( |
| 299 | SELECT name FROM tbl_users |
| 300 | WHERE is_system_account = 1 AND name IS NOT NULL AND name <> '' |
| 301 | ) "; |
| 302 | } |
| 303 | |
| 304 | return $sql; |
| 305 | } |
| 306 | |
| 307 | /** |
| 308 | * Year / month / week filter on a given date column. Returns [whereSql]. |
| 309 | * Falls back to the default (current + prior year) when nothing is set. |
| 310 | */ |
| 311 | private function yearWindow(array $data, string $col): string |
| 312 | { |
| 313 | $where = ''; |
| 314 | |
| 315 | if (! empty($data['years']) && is_array($data['years'])) { |
| 316 | $years = implode(',', array_map('intval', $data['years'])); |
| 317 | $where .= " AND YEAR({$col}) IN ({$years})"; |
| 318 | } elseif (empty($data['current_start']) && empty($data['base_start'])) { |
| 319 | // Default: kill the "206"/2021/2022 junk — current + prior year only. |
| 320 | $current = (int) date('Y'); |
| 321 | $prior = $current - 1; |
| 322 | $where .= " AND YEAR({$col}) IN ({$current},{$prior})"; |
| 323 | } |
| 324 | |
| 325 | if (! empty($data['month'])) { |
| 326 | $where .= ' AND MONTH('.$col.') = '.(int) $data['month']; |
| 327 | } |
| 328 | if (! empty($data['week'])) { |
| 329 | $where .= ' AND WEEK('.$col.', 1) = '.(int) $data['week']; |
| 330 | } |
| 331 | |
| 332 | return $where; |
| 333 | } |
| 334 | |
| 335 | /** Date-range fragment for a named window ('current' | 'base'). */ |
| 336 | private function windowRange(array $data, string $prefix, string $col): ?string |
| 337 | { |
| 338 | $start = $data["{$prefix}_start"] ?? null; |
| 339 | $end = $data["{$prefix}_end"] ?? null; |
| 340 | if (empty($start) || empty($end)) { |
| 341 | return null; |
| 342 | } |
| 343 | $start = addslashes((string) $start); |
| 344 | $end = addslashes((string) $end); |
| 345 | |
| 346 | return " AND DATE({$col}) BETWEEN '{$start}' AND '{$end}' "; |
| 347 | } |
| 348 | |
| 349 | private function ok($data, array $extra = []): ResponseFactory|HttpResponse |
| 350 | { |
| 351 | return response(array_merge(['message' => 'OK', 'data' => $data], $extra)); |
| 352 | } |
| 353 | |
| 354 | private function ko(\Throwable $e, string $tag): ResponseFactory|HttpResponse |
| 355 | { |
| 356 | report(AppException::fromException($e, $tag)); |
| 357 | |
| 358 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 359 | } |
| 360 | |
| 361 | /* ===================================================================== |
| 362 | | Table 1 — Productividad por región |
| 363 | * ===================================================================== */ |
| 364 | |
| 365 | public function list_productivity_region(Request $request): ResponseFactory|HttpResponse |
| 366 | { |
| 367 | try { |
| 368 | $data = $request->all(); |
| 369 | $ids = $this->companyInSql($this->scopeCompanyIds($data)); |
| 370 | |
| 371 | $where = $this->standardFilters($data); |
| 372 | $where .= $this->yearWindow($data, 'COALESCE(q.sent_date, q.issue_date)'); |
| 373 | |
| 374 | $query = "SELECT |
| 375 | YEAR(DATE_ADD(COALESCE(q.sent_date, q.issue_date), INTERVAL - WEEKDAY(COALESCE(q.sent_date, q.issue_date)) DAY)) AS 'year', |
| 376 | LPAD(MONTH(DATE_ADD(COALESCE(q.sent_date, q.issue_date), INTERVAL - WEEKDAY(COALESCE(q.sent_date, q.issue_date)) DAY)), 2, 0) AS 'month', |
| 377 | LPAD(WEEK(DATE_ADD(COALESCE(q.sent_date, q.issue_date), INTERVAL - WEEKDAY(COALESCE(q.sent_date, q.issue_date)) DAY), 1), 2, 0) AS 'week', |
| 378 | COUNT(CASE WHEN COALESCE(q.sent_date, q.issue_date) IS NOT NULL THEN 1 END) AS issued_count, |
| 379 | SUM(CASE WHEN COALESCE(q.sent_date, q.issue_date) IS NOT NULL THEN q.amount END) AS issued_amount, |
| 380 | COUNT(CASE WHEN q.acceptance_date IS NOT NULL AND bs.name = 'Aceptado' THEN 1 END) AS accepted_count, |
| 381 | SUM(CASE WHEN q.acceptance_date IS NOT NULL AND bs.name = 'Aceptado' THEN q.amount END) AS accepted_amount, |
| 382 | COUNT(CASE WHEN q.acceptance_date IS NOT NULL AND bs.name = 'Aceptado' THEN 1 END) |
| 383 | / NULLIF(COUNT(CASE WHEN COALESCE(q.sent_date, q.issue_date) IS NOT NULL THEN 1 END), 0) * 100 AS acceptance_rate_count, |
| 384 | SUM(CASE WHEN q.acceptance_date IS NOT NULL AND bs.name = 'Aceptado' THEN q.amount END) |
| 385 | / NULLIF(SUM(CASE WHEN COALESCE(q.sent_date, q.issue_date) IS NOT NULL THEN q.amount END), 0) * 100 AS acceptance_rate_amount, |
| 386 | GROUP_CONCAT(CASE WHEN COALESCE(q.sent_date, q.issue_date) IS NOT NULL THEN q.id END) AS groupConcatIds |
| 387 | FROM tbl_quotations q |
| 388 | LEFT JOIN tbl_sources s ON s.source_id = q.source_id |
| 389 | LEFT JOIN tbl_budget_status bs ON bs.budget_status_id = q.budget_status_id |
| 390 | LEFT JOIN tbl_budget_types bt ON q.budget_type_id = bt.budget_type_id |
| 391 | LEFT JOIN tbl_customer_types ct ON q.customer_type_id = ct.customer_type_id |
| 392 | WHERE COALESCE(q.sent_date, q.issue_date) IS NOT NULL |
| 393 | AND q.budget_type_id IS NOT NULL |
| 394 | AND q.for_add = 0 |
| 395 | AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1 |
| 396 | AND q.company_id IN ({$ids}) |
| 397 | {$where} |
| 398 | GROUP BY |
| 399 | YEAR(DATE_ADD(COALESCE(q.sent_date, q.issue_date), INTERVAL - WEEKDAY(COALESCE(q.sent_date, q.issue_date)) DAY)), |
| 400 | MONTH(DATE_ADD(COALESCE(q.sent_date, q.issue_date), INTERVAL - WEEKDAY(COALESCE(q.sent_date, q.issue_date)) DAY)), |
| 401 | WEEK(DATE_ADD(COALESCE(q.sent_date, q.issue_date), INTERVAL - WEEKDAY(COALESCE(q.sent_date, q.issue_date)) DAY), 1) WITH ROLLUP |
| 402 | ORDER BY year DESC, month ASC, week ASC"; |
| 403 | |
| 404 | $result = ResultCache::remember('quotations', $query, 600, fn () => DB::select($query)); |
| 405 | |
| 406 | return $this->ok($result); |
| 407 | } catch (\Throwable $e) { |
| 408 | return $this->ko($e, 'ANALYTICS_PRODUCTIVITY_REGION_EXCEPTION'); |
| 409 | } |
| 410 | } |
| 411 | |
| 412 | /* ===================================================================== |
| 413 | | Table 2 — Productividad por comercial |
| 414 | * ===================================================================== */ |
| 415 | |
| 416 | public function list_productivity_commercial(Request $request): ResponseFactory|HttpResponse |
| 417 | { |
| 418 | try { |
| 419 | $data = $request->all(); |
| 420 | $ids = $this->companyInSql($this->scopeCompanyIds($data)); |
| 421 | |
| 422 | $where = $this->standardFilters($data); |
| 423 | $where .= $this->yearWindow($data, 'COALESCE(q.sent_date, q.issue_date)'); |
| 424 | $where .= $this->excludeSystemCommercialsSql(); |
| 425 | |
| 426 | $query = "SELECT |
| 427 | q.commercial AS commercial, |
| 428 | COUNT(CASE WHEN COALESCE(q.sent_date, q.issue_date) IS NOT NULL THEN 1 END) AS issued_count, |
| 429 | SUM(CASE WHEN COALESCE(q.sent_date, q.issue_date) IS NOT NULL THEN q.amount END) AS issued_amount, |
| 430 | COUNT(CASE WHEN q.acceptance_date IS NOT NULL AND bs.name = 'Aceptado' THEN 1 END) AS accepted_count, |
| 431 | SUM(CASE WHEN q.acceptance_date IS NOT NULL AND bs.name = 'Aceptado' THEN q.amount END) AS accepted_amount, |
| 432 | COUNT(CASE WHEN q.acceptance_date IS NOT NULL AND bs.name = 'Aceptado' THEN 1 END) |
| 433 | / NULLIF(COUNT(CASE WHEN COALESCE(q.sent_date, q.issue_date) IS NOT NULL THEN 1 END), 0) * 100 AS acceptance_rate_count, |
| 434 | AVG(CASE WHEN q.acceptance_date IS NOT NULL AND bs.name = 'Aceptado' THEN q.amount END) AS avg_ticket |
| 435 | FROM tbl_quotations q |
| 436 | LEFT JOIN tbl_sources s ON s.source_id = q.source_id |
| 437 | LEFT JOIN tbl_budget_status bs ON bs.budget_status_id = q.budget_status_id |
| 438 | LEFT JOIN tbl_budget_types bt ON q.budget_type_id = bt.budget_type_id |
| 439 | LEFT JOIN tbl_customer_types ct ON q.customer_type_id = ct.customer_type_id |
| 440 | WHERE COALESCE(q.sent_date, q.issue_date) IS NOT NULL |
| 441 | AND q.budget_type_id IS NOT NULL |
| 442 | AND q.for_add = 0 |
| 443 | AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1 |
| 444 | AND q.company_id IN ({$ids}) |
| 445 | {$where} |
| 446 | GROUP BY q.commercial |
| 447 | ORDER BY issued_count DESC"; |
| 448 | |
| 449 | $result = ResultCache::remember('quotations', $query, 600, fn () => DB::select($query)); |
| 450 | |
| 451 | return $this->ok($result); |
| 452 | } catch (\Throwable $e) { |
| 453 | return $this->ko($e, 'ANALYTICS_PRODUCTIVITY_COMMERCIAL_EXCEPTION'); |
| 454 | } |
| 455 | } |
| 456 | |
| 457 | /* ===================================================================== |
| 458 | | Table 3 — Mix por grupo (with optional base/current period compare) |
| 459 | * ===================================================================== */ |
| 460 | |
| 461 | public function list_mix_group(Request $request): ResponseFactory|HttpResponse |
| 462 | { |
| 463 | try { |
| 464 | $data = $request->all(); |
| 465 | $ids = $this->companyInSql($this->scopeCompanyIds($data)); |
| 466 | $groups = config('analytics.budget_type_groups', []); |
| 467 | |
| 468 | $baseFilters = $this->standardFilters($data); |
| 469 | |
| 470 | $current = $this->mixForWindow($data, $ids, $baseFilters, 'current', $groups); |
| 471 | $result = ['groups' => $groups, 'current' => $current]; |
| 472 | |
| 473 | // Period compare (IMPORTANTE 2) — only when a base window is supplied. |
| 474 | if ($this->windowRange($data, 'base', 'COALESCE(q.sent_date, q.issue_date)') !== null) { |
| 475 | $base = $this->mixForWindow($data, $ids, $baseFilters, 'base', $groups); |
| 476 | $result['base'] = $base; |
| 477 | $result['delta'] = $this->mixDelta($current, $base); |
| 478 | } |
| 479 | |
| 480 | return $this->ok($result); |
| 481 | } catch (\Throwable $e) { |
| 482 | return $this->ko($e, 'ANALYTICS_MIX_GROUP_EXCEPTION'); |
| 483 | } |
| 484 | } |
| 485 | |
| 486 | /** |
| 487 | * One mix snapshot: per-group issued/accepted in # and €, plus % of total. |
| 488 | * Window = the named date range if provided, else the year window. |
| 489 | */ |
| 490 | private function mixForWindow(array $data, string $ids, string $baseFilters, string $prefix, array $groups): array |
| 491 | { |
| 492 | $range = $this->windowRange($data, $prefix, 'COALESCE(q.sent_date, q.issue_date)'); |
| 493 | $issuedWindow = $range ?? $this->yearWindow($data, 'COALESCE(q.sent_date, q.issue_date)'); |
| 494 | |
| 495 | // Accepted uses acceptance_date over the same calendar window. |
| 496 | $acceptedWindow = $range |
| 497 | ? str_replace('COALESCE(q.sent_date, q.issue_date)', 'q.acceptance_date', $range) |
| 498 | : $this->yearWindow($data, 'q.acceptance_date'); |
| 499 | |
| 500 | $query = "SELECT |
| 501 | bt.budget_type_group_id AS group_id, |
| 502 | COUNT(CASE WHEN COALESCE(q.sent_date, q.issue_date) IS NOT NULL {$issuedWindow} THEN 1 END) AS issued_count, |
| 503 | SUM(CASE WHEN COALESCE(q.sent_date, q.issue_date) IS NOT NULL {$issuedWindow} THEN q.amount END) AS issued_amount, |
| 504 | COUNT(CASE WHEN q.acceptance_date IS NOT NULL AND bs.name = 'Aceptado' {$acceptedWindow} THEN 1 END) AS accepted_count, |
| 505 | SUM(CASE WHEN q.acceptance_date IS NOT NULL AND bs.name = 'Aceptado' {$acceptedWindow} THEN q.amount END) AS accepted_amount |
| 506 | FROM tbl_quotations q |
| 507 | LEFT JOIN tbl_sources s ON s.source_id = q.source_id |
| 508 | LEFT JOIN tbl_budget_status bs ON bs.budget_status_id = q.budget_status_id |
| 509 | LEFT JOIN tbl_budget_types bt ON q.budget_type_id = bt.budget_type_id |
| 510 | LEFT JOIN tbl_customer_types ct ON q.customer_type_id = ct.customer_type_id |
| 511 | WHERE q.budget_type_id IS NOT NULL |
| 512 | AND q.for_add = 0 |
| 513 | AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1 |
| 514 | AND q.company_id IN ({$ids}) |
| 515 | {$baseFilters} |
| 516 | GROUP BY bt.budget_type_group_id"; |
| 517 | |
| 518 | $rows = ResultCache::remember('quotations', $query, 600, fn () => DB::select($query)); |
| 519 | |
| 520 | // Fold raw group_id rows into the configured 6 buckets. |
| 521 | $byId = []; |
| 522 | foreach ($rows as $r) { |
| 523 | $byId[(int) $r->group_id] = $r; |
| 524 | } |
| 525 | |
| 526 | $otherKey = config('analytics.other_group_key', 'otros'); |
| 527 | $out = []; |
| 528 | $totIssuedC = $totIssuedA = 0; |
| 529 | foreach ($groups as $g) { |
| 530 | $issuedC = $issuedA = $accC = $accA = 0; |
| 531 | foreach ($g['ids'] as $gid) { |
| 532 | if (isset($byId[$gid])) { |
| 533 | $issuedC += (int) $byId[$gid]->issued_count; |
| 534 | $issuedA += (float) $byId[$gid]->issued_amount; |
| 535 | $accC += (int) $byId[$gid]->accepted_count; |
| 536 | $accA += (float) $byId[$gid]->accepted_amount; |
| 537 | unset($byId[$gid]); |
| 538 | } |
| 539 | } |
| 540 | $out[$g['key']] = [ |
| 541 | 'key' => $g['key'], 'label' => $g['label'], |
| 542 | 'issued_count' => $issuedC, 'issued_amount' => $issuedA, |
| 543 | 'accepted_count' => $accC, 'accepted_amount' => $accA, |
| 544 | ]; |
| 545 | $totIssuedC += $issuedC; |
| 546 | $totIssuedA += $issuedA; |
| 547 | } |
| 548 | |
| 549 | // Any leftover group ids (not enumerated in config) → the "Otros" bucket. |
| 550 | foreach ($byId as $r) { |
| 551 | if (isset($out[$otherKey])) { |
| 552 | $out[$otherKey]['issued_count'] += (int) $r->issued_count; |
| 553 | $out[$otherKey]['issued_amount'] += (float) $r->issued_amount; |
| 554 | $out[$otherKey]['accepted_count'] += (int) $r->accepted_count; |
| 555 | $out[$otherKey]['accepted_amount'] += (float) $r->accepted_amount; |
| 556 | $totIssuedC += (int) $r->issued_count; |
| 557 | $totIssuedA += (float) $r->issued_amount; |
| 558 | } |
| 559 | } |
| 560 | |
| 561 | // % of total issued (# and €). |
| 562 | foreach ($out as &$row) { |
| 563 | $row['pct_count'] = $totIssuedC > 0 ? round($row['issued_count'] / $totIssuedC * 100, 2) : 0; |
| 564 | $row['pct_amount'] = $totIssuedA > 0 ? round($row['issued_amount'] / $totIssuedA * 100, 2) : 0; |
| 565 | } |
| 566 | unset($row); |
| 567 | |
| 568 | return array_values($out); |
| 569 | } |
| 570 | |
| 571 | private function mixDelta(array $current, array $base): array |
| 572 | { |
| 573 | $baseByKey = []; |
| 574 | foreach ($base as $b) { |
| 575 | $baseByKey[$b['key']] = $b; |
| 576 | } |
| 577 | $delta = []; |
| 578 | foreach ($current as $c) { |
| 579 | $b = $baseByKey[$c['key']] ?? null; |
| 580 | $delta[] = [ |
| 581 | 'key' => $c['key'], |
| 582 | 'label' => $c['label'], |
| 583 | 'issued_count' => $c['issued_count'] - ($b['issued_count'] ?? 0), |
| 584 | 'issued_amount' => $c['issued_amount'] - ($b['issued_amount'] ?? 0), |
| 585 | 'accepted_count' => $c['accepted_count'] - ($b['accepted_count'] ?? 0), |
| 586 | 'accepted_amount' => $c['accepted_amount'] - ($b['accepted_amount'] ?? 0), |
| 587 | ]; |
| 588 | } |
| 589 | |
| 590 | return $delta; |
| 591 | } |
| 592 | |
| 593 | /* ===================================================================== |
| 594 | | Table 4 — Efectividad / Velocidad |
| 595 | * ===================================================================== */ |
| 596 | |
| 597 | public function list_effectiveness(Request $request): ResponseFactory|HttpResponse |
| 598 | { |
| 599 | try { |
| 600 | $data = $request->all(); |
| 601 | $ids = $this->companyInSql($this->scopeCompanyIds($data)); |
| 602 | [$t1, $t2] = config('analytics.effectiveness_thresholds', [10, 30]); |
| 603 | $t1 = (int) $t1; |
| 604 | $t2 = (int) $t2; |
| 605 | |
| 606 | $where = $this->standardFilters($data); |
| 607 | $range = $this->windowRange($data, 'current', 'COALESCE(q.sent_date, q.issue_date)'); |
| 608 | $where .= $range ?? $this->yearWindow($data, 'COALESCE(q.sent_date, q.issue_date)'); |
| 609 | |
| 610 | // Response-time metrics keyed by send year (drill by year like today); |
| 611 | // the durations below stay on issue_date (ERP budget date). |
| 612 | $query = "SELECT |
| 613 | YEAR(COALESCE(q.sent_date, q.issue_date)) AS 'year', |
| 614 | AVG(CASE WHEN q.request_date IS NOT NULL AND q.issue_date > q.request_date |
| 615 | THEN DATEDIFF(q.issue_date, q.request_date) END) AS avg_days_request_to_issue, |
| 616 | AVG(CASE WHEN q.acceptance_date IS NOT NULL AND q.acceptance_date >= q.issue_date |
| 617 | THEN DATEDIFF(q.acceptance_date, q.issue_date) END) AS avg_days_issue_to_accept, |
| 618 | COUNT(CASE WHEN q.acceptance_date IS NOT NULL AND bs.name = 'Aceptado' THEN 1 END) AS accepted_total, |
| 619 | COUNT(CASE WHEN q.acceptance_date IS NOT NULL AND bs.name = 'Aceptado' |
| 620 | AND DATEDIFF(q.acceptance_date, q.issue_date) < {$t1} THEN 1 END) AS accepted_lt_t1, |
| 621 | COUNT(CASE WHEN q.acceptance_date IS NOT NULL AND bs.name = 'Aceptado' |
| 622 | AND DATEDIFF(q.acceptance_date, q.issue_date) >= {$t1} |
| 623 | AND DATEDIFF(q.acceptance_date, q.issue_date) < {$t2} THEN 1 END) AS accepted_t1_t2, |
| 624 | COUNT(CASE WHEN q.acceptance_date IS NOT NULL AND bs.name = 'Aceptado' |
| 625 | AND DATEDIFF(q.acceptance_date, q.issue_date) >= {$t2} THEN 1 END) AS accepted_gt_t2 |
| 626 | FROM tbl_quotations q |
| 627 | LEFT JOIN tbl_sources s ON s.source_id = q.source_id |
| 628 | LEFT JOIN tbl_budget_status bs ON bs.budget_status_id = q.budget_status_id |
| 629 | LEFT JOIN tbl_budget_types bt ON q.budget_type_id = bt.budget_type_id |
| 630 | LEFT JOIN tbl_customer_types ct ON q.customer_type_id = ct.customer_type_id |
| 631 | WHERE COALESCE(q.sent_date, q.issue_date) IS NOT NULL |
| 632 | AND q.for_add = 0 |
| 633 | AND q.company_id IN ({$ids}) |
| 634 | {$where} |
| 635 | GROUP BY YEAR(COALESCE(q.sent_date, q.issue_date)) WITH ROLLUP |
| 636 | ORDER BY year DESC"; |
| 637 | |
| 638 | $rows = ResultCache::remember('quotations', $query, 600, fn () => DB::select($query)); |
| 639 | |
| 640 | foreach ($rows as $r) { |
| 641 | $tot = (int) $r->accepted_total ?: 0; |
| 642 | $r->pct_lt_t1 = $tot > 0 ? round($r->accepted_lt_t1 / $tot * 100, 2) : 0; |
| 643 | $r->pct_t1_t2 = $tot > 0 ? round($r->accepted_t1_t2 / $tot * 100, 2) : 0; |
| 644 | $r->pct_gt_t2 = $tot > 0 ? round($r->accepted_gt_t2 / $tot * 100, 2) : 0; |
| 645 | } |
| 646 | |
| 647 | return $this->ok($rows, ['thresholds' => [$t1, $t2]]); |
| 648 | } catch (\Throwable $e) { |
| 649 | return $this->ko($e, 'ANALYTICS_EFFECTIVENESS_EXCEPTION'); |
| 650 | } |
| 651 | } |
| 652 | |
| 653 | /* ===================================================================== |
| 654 | | Table 5 — Backlog de solicitudes (NEW) |
| 655 | * ===================================================================== */ |
| 656 | |
| 657 | public function list_backlog(Request $request): ResponseFactory|HttpResponse |
| 658 | { |
| 659 | try { |
| 660 | $data = $request->all(); |
| 661 | $ids = $this->companyInSql($this->scopeCompanyIds($data)); |
| 662 | |
| 663 | $buckets = config('analytics.backlog_buckets', []); |
| 664 | $pending = collect(config('analytics.backlog_pending_status_ids', [1, 6, 11, 12])) |
| 665 | ->map(fn ($v) => (int) $v)->implode(',') ?: '0'; |
| 666 | |
| 667 | // Snapshot date: as-of a supplied date, else today. |
| 668 | $asOf = ! empty($data['as_of']) ? addslashes((string) $data['as_of']) : date('Y-m-d'); |
| 669 | |
| 670 | $where = $this->standardFilters($data); |
| 671 | |
| 672 | // Build one COUNT column per aging bucket. |
| 673 | $bucketCols = []; |
| 674 | foreach ($buckets as $b) { |
| 675 | $min = (int) $b['min']; |
| 676 | $cond = "DATEDIFF('{$asOf}', q.request_date) >= {$min}"; |
| 677 | if ($b['max'] !== null) { |
| 678 | $cond .= ' AND DATEDIFF(\''.$asOf.'\', q.request_date) < '.(int) $b['max']; |
| 679 | } |
| 680 | $key = addslashes((string) $b['key']); |
| 681 | $bucketCols[] = "COUNT(CASE WHEN {$cond} THEN 1 END) AS `bucket_{$key}`"; |
| 682 | } |
| 683 | $bucketSql = implode(",\n ", $bucketCols); |
| 684 | |
| 685 | $query = "SELECT |
| 686 | c.region AS region, |
| 687 | COUNT(*) AS pending_total, |
| 688 | {$bucketSql}, |
| 689 | ROUND(AVG(DATEDIFF('{$asOf}', q.request_date)) / 7, 1) AS backlog_weeks_avg |
| 690 | FROM tbl_quotations q |
| 691 | LEFT JOIN tbl_companies c ON c.company_id = q.company_id |
| 692 | LEFT JOIN tbl_sources s ON s.source_id = q.source_id |
| 693 | LEFT JOIN tbl_budget_status bs ON bs.budget_status_id = q.budget_status_id |
| 694 | LEFT JOIN tbl_budget_types bt ON q.budget_type_id = bt.budget_type_id |
| 695 | LEFT JOIN tbl_customer_types ct ON q.customer_type_id = ct.customer_type_id |
| 696 | WHERE q.for_add = 0 |
| 697 | AND q.request_date IS NOT NULL |
| 698 | AND q.acceptance_date IS NULL |
| 699 | AND q.budget_status_id IN ({$pending}) |
| 700 | AND q.company_id IN ({$ids}) |
| 701 | {$where} |
| 702 | GROUP BY c.region WITH ROLLUP |
| 703 | ORDER BY pending_total DESC"; |
| 704 | |
| 705 | $rows = ResultCache::remember('quotations', $query, 600, fn () => DB::select($query)); |
| 706 | |
| 707 | return $this->ok($rows, ['buckets' => $buckets, 'as_of' => $asOf]); |
| 708 | } catch (\Throwable $e) { |
| 709 | return $this->ko($e, 'ANALYTICS_BACKLOG_EXCEPTION'); |
| 710 | } |
| 711 | } |
| 712 | |
| 713 | /* ===================================================================== |
| 714 | | Filter population — one call fills every dropdown on the page |
| 715 | * ===================================================================== */ |
| 716 | |
| 717 | public function analytics_filters(Request $request): ResponseFactory|HttpResponse |
| 718 | { |
| 719 | try { |
| 720 | $data = $request->all(); |
| 721 | $ids = $this->companyInSql($this->scopeCompanyIds($data)); |
| 722 | |
| 723 | // Regions actually present for these companies, mapped to codes. |
| 724 | $regionRows = DB::select("SELECT DISTINCT region FROM tbl_companies WHERE company_id IN ({$ids}) AND region IS NOT NULL AND region <> ''"); |
| 725 | $configRegions = config('analytics.regions', []); |
| 726 | $regions = []; |
| 727 | foreach ($regionRows as $rr) { |
| 728 | $norm = $this->normalizeRegion((string) $rr->region); |
| 729 | foreach ($configRegions as $code => $cfg) { |
| 730 | $aliases = array_map(fn ($a) => $this->normalizeRegion((string) $a), $cfg['aliases'] ?? []); |
| 731 | $aliases[] = $this->normalizeRegion((string) ($cfg['label'] ?? '')); |
| 732 | if (in_array($norm, $aliases, true)) { |
| 733 | $regions[$code] = ['code' => $code, 'label' => $cfg['label']]; |
| 734 | break; |
| 735 | } |
| 736 | } |
| 737 | } |
| 738 | |
| 739 | // Clean commercial list — active humans only. |
| 740 | $exclude = $this->excludeSystemCommercialsSql(); |
| 741 | $commercials = DB::select("SELECT DISTINCT q.commercial |
| 742 | FROM tbl_quotations q |
| 743 | WHERE q.company_id IN ({$ids}) AND q.for_add = 0 {$exclude} |
| 744 | ORDER BY q.commercial ASC"); |
| 745 | |
| 746 | // Years present, with default = current + prior year. |
| 747 | $years = DB::select("SELECT DISTINCT YEAR(COALESCE(sent_date, issue_date)) AS year |
| 748 | FROM tbl_quotations WHERE COALESCE(sent_date, issue_date) IS NOT NULL AND company_id IN ({$ids}) |
| 749 | AND YEAR(COALESCE(sent_date, issue_date)) >= 2023 |
| 750 | ORDER BY year DESC"); |
| 751 | $currentY = (int) date('Y'); |
| 752 | |
| 753 | // Reference lists. |
| 754 | // These reference tables are global (no company_id column) — mirror |
| 755 | // the legacy Configurations lists, which query them unscoped. |
| 756 | $budgetStatus = DB::select("SELECT budget_status_id, name FROM tbl_budget_status WHERE name <> '' ORDER BY ISNULL(priority), priority ASC, name ASC"); |
| 757 | $budgetTypes = DB::select("SELECT budget_type_id, name, budget_type_group_id FROM tbl_budget_types WHERE name <> '' ORDER BY ISNULL(priority), priority ASC, name ASC"); |
| 758 | $customerTypes = DB::select("SELECT customer_type_id, name FROM tbl_customer_types WHERE name <> '' ORDER BY ISNULL(priority), priority ASC, name ASC"); |
| 759 | $sources = DB::select("SELECT source_id, name FROM tbl_sources WHERE name <> '' ORDER BY ISNULL(priority), priority ASC, name ASC"); |
| 760 | |
| 761 | return response([ |
| 762 | 'message' => 'OK', |
| 763 | 'regions' => array_values($regions), |
| 764 | 'commercials' => collect($commercials)->pluck('commercial')->filter()->values(), |
| 765 | 'years' => collect($years)->pluck('year')->values(), |
| 766 | 'default_years' => [$currentY, $currentY - 1], |
| 767 | 'budget_type_groups' => config('analytics.budget_type_groups', []), |
| 768 | 'budget_types' => $budgetTypes, |
| 769 | 'budget_status' => $budgetStatus, |
| 770 | 'client_types' => $customerTypes, |
| 771 | 'sources' => $sources, |
| 772 | 'origins' => [ |
| 773 | ['key' => 'human', 'label' => 'Humano'], |
| 774 | ['key' => 'ia', 'label' => 'IA'], |
| 775 | ], |
| 776 | ]); |
| 777 | } catch (\Throwable $e) { |
| 778 | return $this->ko($e, 'ANALYTICS_FILTERS_EXCEPTION'); |
| 779 | } |
| 780 | } |
| 781 | } |