Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
| Total | |
0.00% |
0 / 1246 |
|
0.00% |
0 / 41 |
CRAP | |
0.00% |
0 / 1 |
| FinanceController | |
0.00% |
0 / 1246 |
|
0.00% |
0 / 41 |
117306 | |
0.00% |
0 / 1 |
| __construct | |
0.00% |
0 / 1 |
|
0.00% |
0 / 1 |
2 | |||
| getCompany | |
0.00% |
0 / 2 |
|
0.00% |
0 / 1 |
2 | |||
| getCompanyIds | |
0.00% |
0 / 8 |
|
0.00% |
0 / 1 |
20 | |||
| getZenitalSucursalesMap | |
0.00% |
0 / 1 |
|
0.00% |
0 / 1 |
2 | |||
| getZenitalRegionMap | |
0.00% |
0 / 1 |
|
0.00% |
0 / 1 |
2 | |||
| zenitalIdToSedeId | |
0.00% |
0 / 2 |
|
0.00% |
0 / 1 |
2 | |||
| sedeIdToZenitalId | |
0.00% |
0 / 4 |
|
0.00% |
0 / 1 |
12 | |||
| isZenitalSede | |
0.00% |
0 / 7 |
|
0.00% |
0 / 1 |
30 | |||
| getCompanyIdBySedeId | |
0.00% |
0 / 7 |
|
0.00% |
0 / 1 |
20 | |||
| buildZenitalSedeMap | |
0.00% |
0 / 5 |
|
0.00% |
0 / 1 |
12 | |||
| resolveZenitalSedeMap | |
0.00% |
0 / 21 |
|
0.00% |
0 / 1 |
42 | |||
| ensureSedesExist | |
0.00% |
0 / 51 |
|
0.00% |
0 / 1 |
56 | |||
| list_regions | |
0.00% |
0 / 10 |
|
0.00% |
0 / 1 |
6 | |||
| list_sedes | |
0.00% |
0 / 10 |
|
0.00% |
0 / 1 |
6 | |||
| list_budget | |
0.00% |
0 / 12 |
|
0.00% |
0 / 1 |
6 | |||
| upsert_budget | |
0.00% |
0 / 19 |
|
0.00% |
0 / 1 |
12 | |||
| bulk_upsert_budget | |
0.00% |
0 / 25 |
|
0.00% |
0 / 1 |
20 | |||
| delete_budget | |
0.00% |
0 / 17 |
|
0.00% |
0 / 1 |
12 | |||
| list_prevision | |
0.00% |
0 / 12 |
|
0.00% |
0 / 1 |
6 | |||
| upsert_prevision | |
0.00% |
0 / 19 |
|
0.00% |
0 / 1 |
12 | |||
| bulk_upsert_prevision | |
0.00% |
0 / 25 |
|
0.00% |
0 / 1 |
20 | |||
| delete_prevision | |
0.00% |
0 / 17 |
|
0.00% |
0 / 1 |
12 | |||
| list_resumen | |
0.00% |
0 / 228 |
|
0.00% |
0 / 1 |
3782 | |||
| load_resumen | |
0.00% |
0 / 50 |
|
0.00% |
0 / 1 |
132 | |||
| list_report_semanal | |
0.00% |
0 / 256 |
|
0.00% |
0 / 1 |
9312 | |||
| load_report_semanal | |
0.00% |
0 / 47 |
|
0.00% |
0 / 1 |
210 | |||
| upsert_resumen_cell | |
0.00% |
0 / 34 |
|
0.00% |
0 / 1 |
182 | |||
| upsert_report_semanal_cell | |
0.00% |
0 / 53 |
|
0.00% |
0 / 1 |
552 | |||
| list_recipients | |
0.00% |
0 / 12 |
|
0.00% |
0 / 1 |
6 | |||
| create_recipient | |
0.00% |
0 / 16 |
|
0.00% |
0 / 1 |
20 | |||
| update_recipient | |
0.00% |
0 / 13 |
|
0.00% |
0 / 1 |
30 | |||
| delete_recipient | |
0.00% |
0 / 6 |
|
0.00% |
0 / 1 |
6 | |||
| send_report | |
0.00% |
0 / 15 |
|
0.00% |
0 / 1 |
20 | |||
| test_report | |
0.00% |
0 / 12 |
|
0.00% |
0 / 1 |
20 | |||
| import_from_drive | |
0.00% |
0 / 15 |
|
0.00% |
0 / 1 |
6 | |||
| create_sede | |
0.00% |
0 / 13 |
|
0.00% |
0 / 1 |
6 | |||
| delete_sede | |
0.00% |
0 / 7 |
|
0.00% |
0 / 1 |
6 | |||
| list_month_config | |
0.00% |
0 / 9 |
|
0.00% |
0 / 1 |
6 | |||
| update_month_config | |
0.00% |
0 / 7 |
|
0.00% |
0 / 1 |
6 | |||
| getAccessibleRegions | |
0.00% |
0 / 9 |
|
0.00% |
0 / 1 |
6 | |||
| payment_reminders_kpi | |
0.00% |
0 / 168 |
|
0.00% |
0 / 1 |
702 | |||
| 1 | <?php |
| 2 | |
| 3 | namespace App\Http\Controllers; |
| 4 | |
| 5 | use App\Console\Commands\SendFinanceReport; |
| 6 | use App\Models\TblCompanies; |
| 7 | use App\Models\TblCompanyUsers; |
| 8 | use App\Models\TblFinanceBudgetAnual; |
| 9 | use App\Models\TblFinanceMonthConfig; |
| 10 | use App\Models\TblFinancePrevisionAnual; |
| 11 | use App\Models\TblFinanceRegions; |
| 12 | use App\Models\TblFinanceReportRecipients; |
| 13 | use App\Models\TblFinanceReportSemanal; |
| 14 | use App\Models\TblFinanceResumenAnual; |
| 15 | use App\Models\TblFinanceSedes; |
| 16 | use App\Services\FinanceReportRenderer; |
| 17 | use App\Services\ZenitalCatalog; |
| 18 | use Carbon\Carbon; |
| 19 | use Illuminate\Http\Request; |
| 20 | use Illuminate\Support\Facades\App; |
| 21 | use Illuminate\Support\Facades\Artisan; |
| 22 | use Illuminate\Support\Facades\DB; |
| 23 | use Illuminate\Support\Facades\Log; |
| 24 | |
| 25 | class FinanceController extends Controller |
| 26 | { |
| 27 | public function __construct(private ZenitalCatalog $zenital, private FinanceReportRenderer $renderer) |
| 28 | { |
| 29 | App::setLocale(request()->header('Locale-Id')); |
| 30 | } |
| 31 | |
| 32 | // ------------------------------------------------------- |
| 33 | // Helpers |
| 34 | // ------------------------------------------------------- |
| 35 | |
| 36 | /** Para escritura: devuelve la empresa de la región activa */ |
| 37 | private function getCompany(Request $request) |
| 38 | { |
| 39 | $region = urldecode($request->header('Region')); |
| 40 | |
| 41 | return TblCompanies::where('region', $region)->firstOrFail(); |
| 42 | } |
| 43 | |
| 44 | /** |
| 45 | * Para lectura: devuelve los company_ids del usuario. |
| 46 | * Si region = "All" o vacío → todas las empresas del usuario. |
| 47 | */ |
| 48 | private function getCompanyIds(Request $request): array |
| 49 | { |
| 50 | $region = urldecode($request->header('Region')); |
| 51 | $userId = intval($request->header('User-ID')); |
| 52 | |
| 53 | if ($region === 'All' || empty($region)) { |
| 54 | return TblCompanyUsers::where('user_id', $userId) |
| 55 | ->pluck('company_id') |
| 56 | ->toArray(); |
| 57 | } |
| 58 | |
| 59 | $company = TblCompanies::where('region', $region)->first(); |
| 60 | |
| 61 | return $company ? [$company->company_id] : []; |
| 62 | } |
| 63 | |
| 64 | // ------------------------------------------------------- |
| 65 | // Zenital: mapa estático de sucursales |
| 66 | // ------------------------------------------------------- |
| 67 | |
| 68 | /** |
| 69 | * Mapa hardcodeado de sucursales de Zenital. |
| 70 | * Fuente: tabla sucursales de Zenital + normalización de región a company_id interno. |
| 71 | * [sk_sucursal => ['nombre' => ..., 'company_id' => ...]] |
| 72 | */ |
| 73 | /** |
| 74 | * Mapa de sucursales Zenital DWH (sk_sucursal) → sede FST. |
| 75 | * Cada entrada: zenital_id => [nombre, company_id, sede_id] |
| 76 | * |
| 77 | * Construido en runtime a partir de un JOIN nombre↔nombre entre |
| 78 | * `tbl_finance_sedes` y `zenital.dim_sucursal`. La versión anterior |
| 79 | * tenía los sk_sucursal hardcoded a un esquema viejo del DWH; Zenital |
| 80 | * los renumeró (el rango actual es 91+, los IDs viejos se conservan |
| 81 | * en `cod_sucursal`) y como nadie tocó este código las semanas |
| 82 | * siguientes a la migración, los WHERE IN matcheaban 0 filas y todas |
| 83 | * las regiones aparecían con actuals en 0 — el síntoma que Berta |
| 84 | * reportó en STG: "Catalunya empty actuals". Resolver por nombre nos |
| 85 | * deja inmunes a la próxima renumeración del DWH. |
| 86 | */ |
| 87 | private function getZenitalSucursalesMap(): array |
| 88 | { |
| 89 | // FIRE-1148: delegates to the shared ZenitalCatalog service, which |
| 90 | // caches the result via ResultCache (1h TTL, 'finance' domain) so |
| 91 | // every PHP-FPM worker doesn't pay the WAN roundtrip on its first |
| 92 | // request. Invalidation flows through ensureSedesExist below. |
| 93 | return $this->zenital->sucursalesMap(); |
| 94 | } |
| 95 | |
| 96 | /** |
| 97 | * Runtime sk_region (Zenital) → region_id (Titan) lookup. |
| 98 | * |
| 99 | * Mirror of `SendFinanceReport::getZenitalRegionMap`. GESTIONA-sourced |
| 100 | * invoices in Zenital often arrive with sk_sucursal = -1 ("Desconocido") |
| 101 | * and only sk_region populated — Catalunya's entire April 2026 |
| 102 | * facturación arrived this way (≈ 605 K€ under sk_region 21). |
| 103 | * list_report_semanal needs the same lookup to surface that pile in the |
| 104 | * UI; otherwise per-region totals diverge from the email preview. |
| 105 | * |
| 106 | * Most rows match by region name; the small alias map handles Zenital's |
| 107 | * sub-entity entries (Oasys / Crespo / Togasa / Extinfuego / Almería) |
| 108 | * and the Cataluña/Catalunya accent variance. Cached per-process. |
| 109 | */ |
| 110 | private function getZenitalRegionMap(): array |
| 111 | { |
| 112 | // FIRE-1148: same delegation pattern as getZenitalSucursalesMap. |
| 113 | return $this->zenital->regionMap(); |
| 114 | } |
| 115 | |
| 116 | /** |
| 117 | * Convierte el sk_sucursal de Zenital a sede_id en tbl_finance_sedes. |
| 118 | * Usa el mapa explícito; si no existe, devuelve el ID tal cual. |
| 119 | */ |
| 120 | private function zenitalIdToSedeId(int $zenitalId): int |
| 121 | { |
| 122 | $map = $this->getZenitalSucursalesMap(); |
| 123 | |
| 124 | return $map[$zenitalId]['sede_id'] ?? $zenitalId; |
| 125 | } |
| 126 | |
| 127 | /** |
| 128 | * Inverso: dado un sede_id, devuelve el zenital sk_sucursal. |
| 129 | */ |
| 130 | private function sedeIdToZenitalId(int $sedeId): int |
| 131 | { |
| 132 | foreach ($this->getZenitalSucursalesMap() as $zenitalId => $info) { |
| 133 | if ($info['sede_id'] === $sedeId) { |
| 134 | return $zenitalId; |
| 135 | } |
| 136 | } |
| 137 | |
| 138 | return $sedeId; |
| 139 | } |
| 140 | |
| 141 | /** |
| 142 | * FIRE-1027: true if the given sede_id is mapped to a Zenital sucursal, |
| 143 | * which means its data flows from the DWH and is owned by Zenital. The |
| 144 | * UI must not be able to write to such sedes — `list_resumen` pass 1 |
| 145 | * covers them and never reads from `tbl_finance_resumen_anual`, so any |
| 146 | * row we create for them would be a stray write. |
| 147 | * |
| 148 | * Also resolves the local sede_id mapping the same way `resolveZenital |
| 149 | * SedeMap` does (the Zenital map stores hardcoded sede_ids that may not |
| 150 | * match the local PK on every install). |
| 151 | */ |
| 152 | private function isZenitalSede(int $sedeId): bool |
| 153 | { |
| 154 | $localSedeName = TblFinanceSedes::where('id', $sedeId)->value('name'); |
| 155 | |
| 156 | foreach ($this->getZenitalSucursalesMap() as $info) { |
| 157 | if ($info['sede_id'] === $sedeId) { |
| 158 | return true; |
| 159 | } |
| 160 | if ($localSedeName && $info['nombre'] === $localSedeName) { |
| 161 | return true; |
| 162 | } |
| 163 | } |
| 164 | |
| 165 | return false; |
| 166 | } |
| 167 | |
| 168 | /** |
| 169 | * Devuelve el company_id correspondiente a un sede_id. |
| 170 | * |
| 171 | * Berta audit follow-up: trust the sede row's actual company_id over |
| 172 | * the Zenital map. The map was inheriting `company_id` from |
| 173 | * `getZenitalSucursalesMap`, but historical imports occasionally |
| 174 | * inserted budget rows with a different company_id (e.g. Lluis_Moff |
| 175 | * sede_id=4 has budget rows split between company_id=18 and 19, |
| 176 | * because an old Excel import used a different resolver). When the |
| 177 | * UI upsert chose 19 (from the map) but the real data sat at |
| 178 | * company_id=18, the new write fell into a parallel "shadow" row and |
| 179 | * looked like the save was a no-op. By trusting the sede row we |
| 180 | * write where the data already lives. |
| 181 | */ |
| 182 | private function getCompanyIdBySedeId(int $sedeId): ?int |
| 183 | { |
| 184 | $fromSede = TblFinanceSedes::where('id', $sedeId)->value('company_id'); |
| 185 | if ($fromSede !== null) { |
| 186 | return (int) $fromSede; |
| 187 | } |
| 188 | |
| 189 | // Sede missing from DB — fall back to the map so a brand-new |
| 190 | // sede that ensureSedesExist hasn't inserted yet still resolves. |
| 191 | foreach ($this->getZenitalSucursalesMap() as $info) { |
| 192 | if ($info['sede_id'] === $sedeId) { |
| 193 | return $info['company_id']; |
| 194 | } |
| 195 | } |
| 196 | |
| 197 | return null; |
| 198 | } |
| 199 | |
| 200 | /** |
| 201 | * Devuelve el mapa [sk_sucursal_zenital => sede_id] filtrado |
| 202 | * por los company_ids accesibles. |
| 203 | */ |
| 204 | private function buildZenitalSedeMap(array $companyIds): array |
| 205 | { |
| 206 | $result = []; |
| 207 | foreach ($this->getZenitalSucursalesMap() as $zenitalId => $info) { |
| 208 | if (in_array($info['company_id'], $companyIds)) { |
| 209 | $result[$zenitalId] = $info['sede_id']; |
| 210 | } |
| 211 | } |
| 212 | |
| 213 | return $result; |
| 214 | } |
| 215 | |
| 216 | /** |
| 217 | * Same as buildZenitalSedeMap but translates each hardcoded sede_id to |
| 218 | * the actual tbl_finance_sedes.id by matching company_id + name. The |
| 219 | * frontend identifies sedes by the local PK (not the Zenital hardcoded |
| 220 | * id) — without this translation, resumen/semanal responses come back |
| 221 | * with sede_ids the frontend can't link to any sede row. |
| 222 | */ |
| 223 | private function resolveZenitalSedeMap(array $companyIds): array |
| 224 | { |
| 225 | $rawMap = $this->buildZenitalSedeMap($companyIds); |
| 226 | if (empty($rawMap)) { |
| 227 | return []; |
| 228 | } |
| 229 | |
| 230 | // ordenamos por id ASC para que, si hay duplicados activos por |
| 231 | // (company_id, name) — caso histórico en stg donde el operador |
| 232 | // creó la sede manualmente (id bajo, p.ej. 32 Cisemex) y luego |
| 233 | // el viejo ensureSedesExist añadió la versión hardcoded del |
| 234 | // mapa (id 116) — ganen los ids bajos. Esos son los que tienen |
| 235 | // el budget editado por el usuario; los hardcoded suelen estar |
| 236 | // vacíos. La migración cleanup_finance_sede_duplicates los |
| 237 | // desactiva, pero esta ordenación deja el resolver robusto si |
| 238 | // por la razón que sea persisten duplicados en producción. |
| 239 | $localSedes = TblFinanceSedes::whereIn('company_id', $companyIds) |
| 240 | ->where('is_active', 1) |
| 241 | ->orderBy('id', 'asc') |
| 242 | ->get(['id', 'name', 'company_id']); |
| 243 | |
| 244 | // [company_id][lowercased name] => local tbl_finance_sedes.id |
| 245 | // "First wins": no sobreescribimos si ya hay una entrada para |
| 246 | // esa (company_id, name) — preserva el id más bajo. |
| 247 | $byCompanyName = []; |
| 248 | foreach ($localSedes as $s) { |
| 249 | $key = mb_strtolower((string) $s->name); |
| 250 | if (! isset($byCompanyName[$s->company_id][$key])) { |
| 251 | $byCompanyName[$s->company_id][$key] = $s->id; |
| 252 | } |
| 253 | } |
| 254 | |
| 255 | $zenitalMap = $this->getZenitalSucursalesMap(); |
| 256 | $result = []; |
| 257 | foreach ($rawMap as $zenitalId => $fallbackSedeId) { |
| 258 | $info = $zenitalMap[$zenitalId] ?? null; |
| 259 | if ($info) { |
| 260 | $localId = $byCompanyName[$info['company_id']][mb_strtolower((string) $info['nombre'])] ?? null; |
| 261 | $result[$zenitalId] = $localId ?? $fallbackSedeId; |
| 262 | } else { |
| 263 | $result[$zenitalId] = $fallbackSedeId; |
| 264 | } |
| 265 | } |
| 266 | |
| 267 | return $result; |
| 268 | } |
| 269 | |
| 270 | /** |
| 271 | * Garantiza que las regiones y sedes del mapa Zenital existan en |
| 272 | * tbl_finance_regions / tbl_finance_sedes antes de escribir budget o |
| 273 | * previsión. Estrictamente aditivo: nunca toca filas existentes. |
| 274 | * |
| 275 | * El comportamiento anterior reescribía `region_id = company_id` para |
| 276 | * cada sede del mapa y creaba una región paralela `id = company_id`. |
| 277 | * Eso rompía instalaciones donde un mismo company_id ya tenía varias |
| 278 | * regiones activas con ids distintos (p.ej. Valencia/Alicante/Castellón |
| 279 | * todas bajo company_id=30, o Catalunya id=1 vs Cataluña id=19) y |
| 280 | * dejaba sedes huérfanas bajo regiones duplicadas. Ahora: |
| 281 | * - si ya existe cualquier región activa para el company_id, no se |
| 282 | * crea otra; las sedes nuevas se asocian a la primera región |
| 283 | * existente para ese company_id. |
| 284 | * - si la sede ya existe (por id en el mapa), no se toca: ni nombre, |
| 285 | * ni region_id, ni is_active. Esto también deja de renombrar |
| 286 | * "Robles" → "Robles Legacy" cuando dos entradas del mapa apuntan |
| 287 | * al mismo sede_id. |
| 288 | */ |
| 289 | private function ensureSedesExist(): void |
| 290 | { |
| 291 | $map = $this->getZenitalSucursalesMap(); |
| 292 | |
| 293 | // FIRE-1148: track whether we mutated tbl_finance_regions or |
| 294 | // tbl_finance_sedes so we can invalidate the catalog cache only |
| 295 | // when something actually changed (avoids unnecessary cache flushes |
| 296 | // on every upsert_budget / upsert_prevision call). |
| 297 | $mutated = false; |
| 298 | |
| 299 | $companyIds = array_unique(array_column($map, 'company_id')); |
| 300 | $companies = TblCompanies::whereIn('company_id', $companyIds) |
| 301 | ->get(['company_id', 'region']) |
| 302 | ->keyBy('company_id'); |
| 303 | |
| 304 | // Paso 1: solo crear una región nueva cuando el company_id no |
| 305 | // tenga ya ninguna región activa. No reactivar ni renombrar las |
| 306 | // existentes — el operador puede haberlas dividido a propósito. |
| 307 | $regionByCompany = []; |
| 308 | foreach ($companyIds as $companyId) { |
| 309 | $existingRegionId = DB::table('tbl_finance_regions') |
| 310 | ->where('company_id', $companyId) |
| 311 | ->where('is_active', 1) |
| 312 | ->orderBy('id') |
| 313 | ->value('id'); |
| 314 | |
| 315 | if ($existingRegionId !== null) { |
| 316 | $regionByCompany[$companyId] = $existingRegionId; |
| 317 | |
| 318 | continue; |
| 319 | } |
| 320 | |
| 321 | $regionName = $companies[$companyId]->region ?? "Region $companyId"; |
| 322 | $newId = DB::table('tbl_finance_regions')->insertGetId([ |
| 323 | 'company_id' => $companyId, |
| 324 | 'name' => $regionName, |
| 325 | 'code' => (string) $companyId, |
| 326 | 'is_active' => 1, |
| 327 | 'created_at' => now(), |
| 328 | 'updated_at' => now(), |
| 329 | ]); |
| 330 | $regionByCompany[$companyId] = $newId; |
| 331 | $mutated = true; |
| 332 | } |
| 333 | |
| 334 | // Paso 2: insertar las sedes del mapa que aún no existan. |
| 335 | // |
| 336 | // Dos checks complementarios: |
| 337 | // - por NOMBRE (company_id + nombre): si el operador ya tiene una |
| 338 | // sede activa con ese mismo nombre creada manualmente vía UI |
| 339 | // (típicamente con id auto-increment bajo, p.ej. id=32 para |
| 340 | // "Cisemex"), NO insertamos la versión hardcoded del mapa |
| 341 | // (sede_id=116). Antes esto creaba pares duplicados en stg |
| 342 | // (id=32 con datos de budget + id=116 con datos de Zenital), |
| 343 | // que se renderizaban como dos filas "Cisemex" idénticas. |
| 344 | // - por ID (sede_id hardcoded del mapa): defensa adicional por |
| 345 | // si el operador tiene una sede inactiva con ese id (la |
| 346 | // migración cleanup_finance_sede_duplicates desactiva las |
| 347 | // duplicadas; respetamos esa desactivación). |
| 348 | foreach ($map as $zenitalId => $info) { |
| 349 | $sedeId = $this->zenitalIdToSedeId($zenitalId); |
| 350 | |
| 351 | $existsByName = DB::table('tbl_finance_sedes') |
| 352 | ->where('company_id', $info['company_id']) |
| 353 | ->where('name', $info['nombre']) |
| 354 | ->where('is_active', 1) |
| 355 | ->exists(); |
| 356 | if ($existsByName) { |
| 357 | continue; |
| 358 | } |
| 359 | |
| 360 | if (DB::table('tbl_finance_sedes')->where('id', $sedeId)->exists()) { |
| 361 | continue; |
| 362 | } |
| 363 | |
| 364 | DB::table('tbl_finance_sedes')->insert([ |
| 365 | 'id' => $sedeId, |
| 366 | 'company_id' => $info['company_id'], |
| 367 | 'region_id' => $regionByCompany[$info['company_id']] ?? $info['company_id'], |
| 368 | 'name' => $info['nombre'], |
| 369 | 'code' => (string) $zenitalId, |
| 370 | 'is_active' => 1, |
| 371 | 'created_at' => now(), |
| 372 | 'updated_at' => now(), |
| 373 | ]); |
| 374 | $mutated = true; |
| 375 | } |
| 376 | |
| 377 | // FIRE-1148: blow away the cached Zenital catalog so the new |
| 378 | // region/sede surface immediately. Skipped when nothing changed |
| 379 | // — the common upsert_budget / upsert_prevision path hits this |
| 380 | // method on every write but rarely actually inserts. |
| 381 | if ($mutated) { |
| 382 | $this->zenital->forget(); |
| 383 | } |
| 384 | } |
| 385 | |
| 386 | // ------------------------------------------------------- |
| 387 | // SEDES & REGIONES (derivadas dinámicamente del mapa Zenital) |
| 388 | // ------------------------------------------------------- |
| 389 | |
| 390 | /** |
| 391 | * GET /finance/regions |
| 392 | * Las regiones son los company_ids únicos del mapa Zenital que el usuario |
| 393 | * tiene acceso. El nombre se obtiene de TblCompanies.region. |
| 394 | */ |
| 395 | public function list_regions(Request $request) |
| 396 | { |
| 397 | try { |
| 398 | $companyIds = $this->getCompanyIds($request); |
| 399 | |
| 400 | // Return the real tbl_finance_regions.id so regions that share a |
| 401 | // company_id (e.g. Valencia, Alicante, Castellón all under |
| 402 | // company_id=30) don't collapse into a single frontend bucket. |
| 403 | // tbl_finance_sedes.region_id now points to this PK for all rows. |
| 404 | $data = TblFinanceRegions::whereIn('company_id', $companyIds) |
| 405 | ->where('is_active', 1) |
| 406 | ->orderBy('name') |
| 407 | ->get(['id', 'name', 'company_id']); |
| 408 | |
| 409 | return response(['message' => 'OK', 'data' => $data]); |
| 410 | } catch (\Exception $e) { |
| 411 | /** @disregard P1014 */ |
| 412 | $e->exceptionCode = 'LIST_FINANCE_REGIONS_EXCEPTION'; |
| 413 | report($e); |
| 414 | |
| 415 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 416 | } |
| 417 | } |
| 418 | |
| 419 | /** |
| 420 | * GET /finance/sedes |
| 421 | * Las sedes son las sucursales del mapa Zenital accesibles por el usuario. |
| 422 | * El id es el sk_sucursal de Zenital (convertido a positivo si es negativo). |
| 423 | * region_id = company_id (coincide con el id de list_regions). |
| 424 | */ |
| 425 | public function list_sedes(Request $request) |
| 426 | { |
| 427 | try { |
| 428 | $companyIds = $this->getCompanyIds($request); |
| 429 | |
| 430 | $data = TblFinanceSedes::whereIn('company_id', $companyIds) |
| 431 | ->where('is_active', 1) |
| 432 | ->orderBy('name') |
| 433 | ->get(['id', 'name', 'company_id', 'region_id']); |
| 434 | |
| 435 | return response(['message' => 'OK', 'data' => $data]); |
| 436 | } catch (\Exception $e) { |
| 437 | /** @disregard P1014 */ |
| 438 | $e->exceptionCode = 'LIST_FINANCE_SEDES_EXCEPTION'; |
| 439 | report($e); |
| 440 | |
| 441 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 442 | } |
| 443 | } |
| 444 | |
| 445 | // ------------------------------------------------------- |
| 446 | // BUDGET ANUAL |
| 447 | // ------------------------------------------------------- |
| 448 | |
| 449 | /** |
| 450 | * GET /finance/budget?year=2025 |
| 451 | * Devuelve todas las filas de Budget para el año dado, |
| 452 | * agrupadas por sede y región. |
| 453 | */ |
| 454 | public function list_budget(Request $request) |
| 455 | { |
| 456 | try { |
| 457 | $companyIds = $this->getCompanyIds($request); |
| 458 | $year = $request->query('year', date('Y')); |
| 459 | |
| 460 | $data = TblFinanceBudgetAnual::whereIn('company_id', $companyIds) |
| 461 | ->where('year', $year) |
| 462 | ->orderBy('sede_id') |
| 463 | ->orderBy('month') |
| 464 | ->get(['company_id', 'sede_id', 'year', 'month', 'amount']); |
| 465 | |
| 466 | return response(['message' => 'OK', 'data' => $data]); |
| 467 | } catch (\Exception $e) { |
| 468 | /** @disregard P1014 */ |
| 469 | $e->exceptionCode = 'LIST_BUDGET_EXCEPTION'; |
| 470 | report($e); |
| 471 | |
| 472 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 473 | } |
| 474 | } |
| 475 | |
| 476 | /** |
| 477 | * POST /finance/budget |
| 478 | * Body: { sede_id, year, month, amount } |
| 479 | * sede_id = zenitalIdToSedeId(sk_sucursal) |
| 480 | */ |
| 481 | public function upsert_budget(Request $request) |
| 482 | { |
| 483 | try { |
| 484 | $this->ensureSedesExist(); |
| 485 | |
| 486 | $data = $request->all(); |
| 487 | $companyId = $this->getCompanyIdBySedeId((int) $data['sede_id']); |
| 488 | |
| 489 | if (! $companyId) { |
| 490 | return response(['message' => 'KO', 'error' => 'Sede no encontrada en el mapa de Zenital']); |
| 491 | } |
| 492 | |
| 493 | TblFinanceBudgetAnual::updateOrCreate( |
| 494 | [ |
| 495 | 'company_id' => $companyId, |
| 496 | 'sede_id' => $data['sede_id'], |
| 497 | 'year' => $data['year'], |
| 498 | 'month' => $data['month'], |
| 499 | ], |
| 500 | ['amount' => $data['amount']] |
| 501 | ); |
| 502 | |
| 503 | return response(['message' => 'OK']); |
| 504 | } catch (\Exception $e) { |
| 505 | /** @disregard P1014 */ |
| 506 | $e->exceptionCode = 'UPSERT_BUDGET_EXCEPTION'; |
| 507 | report($e); |
| 508 | |
| 509 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 510 | } |
| 511 | } |
| 512 | |
| 513 | /** |
| 514 | * POST /finance/budget/bulk |
| 515 | * Body: { year, rows: [{ sede_id, month, amount }, ...] } |
| 516 | */ |
| 517 | public function bulk_upsert_budget(Request $request) |
| 518 | { |
| 519 | try { |
| 520 | $this->ensureSedesExist(); |
| 521 | |
| 522 | $year = $request->input('year'); |
| 523 | $rows = $request->input('rows', []); |
| 524 | |
| 525 | // FIRE-1148: prefetch sede→company_id in one query so the foreach |
| 526 | // doesn't fire getCompanyIdBySedeId (1 SQL) per row. ensureSedesExist |
| 527 | // above guarantees every sede in the payload now exists, so the map |
| 528 | // covers all rows; rows whose sede_id isn't in the map are skipped |
| 529 | // the same way the previous getCompanyIdBySedeId === null branch did. |
| 530 | $sedeIds = array_unique(array_map(fn ($r) => (int) $r['sede_id'], $rows)); |
| 531 | $sedeToCompany = TblFinanceSedes::whereIn('id', $sedeIds)->pluck('company_id', 'id'); |
| 532 | |
| 533 | DB::transaction(function () use ($year, $rows, $sedeToCompany) { |
| 534 | foreach ($rows as $row) { |
| 535 | $companyId = $sedeToCompany[$row['sede_id']] ?? null; |
| 536 | if (! $companyId) { |
| 537 | continue; |
| 538 | } |
| 539 | |
| 540 | TblFinanceBudgetAnual::updateOrCreate( |
| 541 | [ |
| 542 | 'company_id' => $companyId, |
| 543 | 'sede_id' => $row['sede_id'], |
| 544 | 'year' => $year, |
| 545 | 'month' => $row['month'], |
| 546 | ], |
| 547 | ['amount' => $row['amount']] |
| 548 | ); |
| 549 | } |
| 550 | }); |
| 551 | |
| 552 | return response(['message' => 'OK']); |
| 553 | } catch (\Exception $e) { |
| 554 | /** @disregard P1014 */ |
| 555 | $e->exceptionCode = 'BULK_UPSERT_BUDGET_EXCEPTION'; |
| 556 | report($e); |
| 557 | |
| 558 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 559 | } |
| 560 | } |
| 561 | |
| 562 | /** |
| 563 | * DELETE /finance-budget?sede_id=X&year=Y&month=Z |
| 564 | * |
| 565 | * Berta audit follow-up — Item 2: the Budget tab had no way to clear |
| 566 | * a cell. The operator's only option was to type "0" in the modal, |
| 567 | * which left a row with amount=0 that still rendered as "0,00 €". |
| 568 | * They wanted "blank means no budget for this month, not zero". This |
| 569 | * endpoint actually removes the row so the cell renders empty again. |
| 570 | */ |
| 571 | public function delete_budget(Request $request) |
| 572 | { |
| 573 | try { |
| 574 | $data = $request->validate([ |
| 575 | 'sede_id' => 'required|integer', |
| 576 | 'year' => 'required|integer', |
| 577 | 'month' => 'required|integer|between:1,12', |
| 578 | ]); |
| 579 | |
| 580 | $companyId = $this->getCompanyIdBySedeId((int) $data['sede_id']); |
| 581 | |
| 582 | $query = TblFinanceBudgetAnual::where('sede_id', $data['sede_id']) |
| 583 | ->where('year', $data['year']) |
| 584 | ->where('month', $data['month']); |
| 585 | if ($companyId) { |
| 586 | $query->where('company_id', $companyId); |
| 587 | } |
| 588 | $query->delete(); |
| 589 | |
| 590 | return response(['message' => 'OK']); |
| 591 | } catch (\Exception $e) { |
| 592 | /** @disregard P1014 */ |
| 593 | $e->exceptionCode = 'DELETE_BUDGET_EXCEPTION'; |
| 594 | report($e); |
| 595 | |
| 596 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 597 | } |
| 598 | } |
| 599 | |
| 600 | // ------------------------------------------------------- |
| 601 | // PREVISIÓN ANUAL |
| 602 | // ------------------------------------------------------- |
| 603 | |
| 604 | public function list_prevision(Request $request) |
| 605 | { |
| 606 | try { |
| 607 | $companyIds = $this->getCompanyIds($request); |
| 608 | $year = $request->query('year', date('Y')); |
| 609 | |
| 610 | $data = TblFinancePrevisionAnual::whereIn('company_id', $companyIds) |
| 611 | ->where('year', $year) |
| 612 | ->orderBy('sede_id') |
| 613 | ->orderBy('month') |
| 614 | ->get(['company_id', 'sede_id', 'year', 'month', 'amount']); |
| 615 | |
| 616 | return response(['message' => 'OK', 'data' => $data]); |
| 617 | } catch (\Exception $e) { |
| 618 | /** @disregard P1014 */ |
| 619 | $e->exceptionCode = 'LIST_PREVISION_EXCEPTION'; |
| 620 | report($e); |
| 621 | |
| 622 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 623 | } |
| 624 | } |
| 625 | |
| 626 | public function upsert_prevision(Request $request) |
| 627 | { |
| 628 | try { |
| 629 | $this->ensureSedesExist(); |
| 630 | |
| 631 | $data = $request->all(); |
| 632 | $companyId = $this->getCompanyIdBySedeId((int) $data['sede_id']); |
| 633 | |
| 634 | if (! $companyId) { |
| 635 | return response(['message' => 'KO', 'error' => 'Sede no encontrada en el mapa de Zenital']); |
| 636 | } |
| 637 | |
| 638 | TblFinancePrevisionAnual::updateOrCreate( |
| 639 | [ |
| 640 | 'company_id' => $companyId, |
| 641 | 'sede_id' => $data['sede_id'], |
| 642 | 'year' => $data['year'], |
| 643 | 'month' => $data['month'], |
| 644 | ], |
| 645 | ['amount' => $data['amount']] |
| 646 | ); |
| 647 | |
| 648 | return response(['message' => 'OK']); |
| 649 | } catch (\Exception $e) { |
| 650 | /** @disregard P1014 */ |
| 651 | $e->exceptionCode = 'UPSERT_PREVISION_EXCEPTION'; |
| 652 | report($e); |
| 653 | |
| 654 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 655 | } |
| 656 | } |
| 657 | |
| 658 | public function bulk_upsert_prevision(Request $request) |
| 659 | { |
| 660 | try { |
| 661 | $this->ensureSedesExist(); |
| 662 | |
| 663 | $year = $request->input('year'); |
| 664 | $rows = $request->input('rows', []); |
| 665 | |
| 666 | // FIRE-1148: prefetch sede→company_id — same pattern as |
| 667 | // bulk_upsert_budget above. See that method for the rationale. |
| 668 | $sedeIds = array_unique(array_map(fn ($r) => (int) $r['sede_id'], $rows)); |
| 669 | $sedeToCompany = TblFinanceSedes::whereIn('id', $sedeIds)->pluck('company_id', 'id'); |
| 670 | |
| 671 | DB::transaction(function () use ($year, $rows, $sedeToCompany) { |
| 672 | foreach ($rows as $row) { |
| 673 | $companyId = $sedeToCompany[$row['sede_id']] ?? null; |
| 674 | if (! $companyId) { |
| 675 | continue; |
| 676 | } |
| 677 | |
| 678 | TblFinancePrevisionAnual::updateOrCreate( |
| 679 | [ |
| 680 | 'company_id' => $companyId, |
| 681 | 'sede_id' => $row['sede_id'], |
| 682 | 'year' => $year, |
| 683 | 'month' => $row['month'], |
| 684 | ], |
| 685 | ['amount' => $row['amount']] |
| 686 | ); |
| 687 | } |
| 688 | }); |
| 689 | |
| 690 | return response(['message' => 'OK']); |
| 691 | } catch (\Exception $e) { |
| 692 | /** @disregard P1014 */ |
| 693 | $e->exceptionCode = 'BULK_UPSERT_PREVISION_EXCEPTION'; |
| 694 | report($e); |
| 695 | |
| 696 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 697 | } |
| 698 | } |
| 699 | |
| 700 | /** |
| 701 | * DELETE /finance-prevision?sede_id=X&year=Y&month=Z — same shape and |
| 702 | * rationale as `delete_budget`. |
| 703 | */ |
| 704 | public function delete_prevision(Request $request) |
| 705 | { |
| 706 | try { |
| 707 | $data = $request->validate([ |
| 708 | 'sede_id' => 'required|integer', |
| 709 | 'year' => 'required|integer', |
| 710 | 'month' => 'required|integer|between:1,12', |
| 711 | ]); |
| 712 | |
| 713 | $companyId = $this->getCompanyIdBySedeId((int) $data['sede_id']); |
| 714 | |
| 715 | $query = TblFinancePrevisionAnual::where('sede_id', $data['sede_id']) |
| 716 | ->where('year', $data['year']) |
| 717 | ->where('month', $data['month']); |
| 718 | if ($companyId) { |
| 719 | $query->where('company_id', $companyId); |
| 720 | } |
| 721 | $query->delete(); |
| 722 | |
| 723 | return response(['message' => 'OK']); |
| 724 | } catch (\Exception $e) { |
| 725 | /** @disregard P1014 */ |
| 726 | $e->exceptionCode = 'DELETE_PREVISION_EXCEPTION'; |
| 727 | report($e); |
| 728 | |
| 729 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 730 | } |
| 731 | } |
| 732 | |
| 733 | // ------------------------------------------------------- |
| 734 | // RESUMEN ANUAL (solo lectura desde front, escritura automática) |
| 735 | // ------------------------------------------------------- |
| 736 | |
| 737 | /** |
| 738 | * GET /finance/resumen?year=2025 |
| 739 | * Actuals, n-1 y n-2 se calculan en tiempo real desde Zenital (facturacion). |
| 740 | * Budget se lee de tbl_finance_budget_anual (MySQL). |
| 741 | * sede_id = zenitalIdToSedeId(sk_sucursal). |
| 742 | */ |
| 743 | public function list_resumen(Request $request) |
| 744 | { |
| 745 | try { |
| 746 | $companyIds = $this->getCompanyIds($request); |
| 747 | $year = (int) $request->query('year', date('Y')); |
| 748 | // "Hasta {mes}" filter — when the Resumen Anual tab shows months |
| 749 | // 1..N, the region_ytd_totals must sum the same range so the |
| 750 | // tab's "Total Region" matches what the user sees. Defaults to |
| 751 | // 12 (full year) for backwards compatibility with callers that |
| 752 | // don't pass the param. |
| 753 | $monthCutoff = (int) $request->query('month', 12); |
| 754 | if ($monthCutoff < 1 || $monthCutoff > 12) { |
| 755 | $monthCutoff = 12; |
| 756 | } |
| 757 | |
| 758 | // Map [sk_sucursal_zenital => sede_id]. FIX: the static map |
| 759 | // returns the hardcoded sede_id from getZenitalSucursalesMap |
| 760 | // (e.g. 111 for "Extintores Clemente"), but the frontend keys |
| 761 | // sedes by tbl_finance_sedes.id (e.g. 32). Translate by name |
| 762 | // so the resumen data's sede_id matches the list_sedes output. |
| 763 | $zenitalToSede = $this->resolveZenitalSedeMap($companyIds); |
| 764 | |
| 765 | // Berta audit follow-up: do NOT short-circuit when a region has |
| 766 | // no Zenital-mapped sedes. UI-created sedes (Cano Lopera, Cuenfa, |
| 767 | // Extincas, Aeroextinción, and any future ones the operator adds |
| 768 | // from the Crear Sede form) live entirely in MySQL — their budget |
| 769 | // and report rows must still surface even when the company has |
| 770 | // zero Zenital sucursales. The local merges below already handle |
| 771 | // the zenital-empty case correctly as long as we let execution |
| 772 | // continue past this point. |
| 773 | $zenitalIds = array_keys($zenitalToSede); |
| 774 | |
| 775 | // Facturación desde data warehouse para año actual, n-1 y n-2. |
| 776 | // |
| 777 | // El conector pgsql de Laravel no expone `connect_timeout` ni |
| 778 | // `statement_timeout` por config, así que los fijamos en runtime: |
| 779 | // - PGCONNECT_TIMEOUT: libpq por defecto bloquea hasta ~75s si |
| 780 | // el DWH está caído; el hilo PHP se queda colgado y el |
| 781 | // navegador ve la pestaña "sin info". |
| 782 | // - statement_timeout: aborta consultas que se vayan de tiempo |
| 783 | // (latencia transatlántica + planes lentos en el DWH). El |
| 784 | // catch ya cae a los datos locales (budget MySQL, facturación |
| 785 | // semanal subida desde Drive) — el caller no se queda en |
| 786 | // blanco, simplemente sin "actuals" Zenital. |
| 787 | putenv('PGCONNECT_TIMEOUT='.env('ZENITAL_DB_CONNECT_TIMEOUT', 5)); |
| 788 | $idx = []; |
| 789 | try { |
| 790 | DB::connection('zenital')->statement('SET statement_timeout = '.(int) env('ZENITAL_DB_STATEMENT_TIMEOUT_MS', 15000)); |
| 791 | |
| 792 | // Per-invoice dedup before summing. fact_facturacion can |
| 793 | // carry duplicate rows per num_factura (Zenital ETL bug |
| 794 | // surfaced on STG: every Alcarrena invoice was recorded |
| 795 | // twice). Collapse to one row per (num_factura, |
| 796 | // sk_sucursal, ano, num_mes) by MAX-ing the amount, then |
| 797 | // SUM. Mirror of SendFinanceReport::getResumenData. |
| 798 | $skPlaceholders = implode(',', array_fill(0, count($zenitalIds), '?')); |
| 799 | $bindings = array_merge( |
| 800 | array_map('intval', $zenitalIds), |
| 801 | [$year, $year - 1, $year - 2], |
| 802 | ); |
| 803 | |
| 804 | $facturacion = DB::connection('zenital')->select(" |
| 805 | SELECT sk_sucursal, ano, mes, SUM(invoice_amount) AS total |
| 806 | FROM ( |
| 807 | SELECT f.num_factura, f.sk_sucursal, d.ano, |
| 808 | d.num_mes AS mes, |
| 809 | MAX(f.base_imponible) AS invoice_amount |
| 810 | FROM fact_facturacion f |
| 811 | JOIN dim_fecha d ON d.sk_fecha = f.sk_fecha_emision |
| 812 | WHERE f.sk_sucursal IN ({$skPlaceholders}) |
| 813 | AND d.ano IN (?, ?, ?) |
| 814 | GROUP BY f.num_factura, f.sk_sucursal, d.ano, d.num_mes |
| 815 | ) per_invoice |
| 816 | GROUP BY sk_sucursal, ano, mes |
| 817 | ", $bindings); |
| 818 | |
| 819 | foreach ($facturacion as $row) { |
| 820 | $sedeId = $zenitalToSede[$row->sk_sucursal] ?? $row->sk_sucursal; |
| 821 | $idx[$sedeId][$row->ano][$row->mes] = ($idx[$sedeId][$row->ano][$row->mes] ?? 0) + (float) $row->total; |
| 822 | } |
| 823 | } catch (\Exception $e) { |
| 824 | Log::channel('third-party')->warning('Zenital connection failed in list_resumen, using local data only: '.$e->getMessage()); |
| 825 | } |
| 826 | |
| 827 | // Mirror of list_report_semanal lines 1248-1342 and |
| 828 | // SendFinanceReport::getResumenData. GESTIONA-sourced invoices |
| 829 | // with sk_sucursal = -1 are re-attributed via |
| 830 | // dim_cliente_facturacion.sucursal when possible; the residual |
| 831 | // stays in a per-region bucket so the frontend can render a |
| 832 | // "Sin asignar a sede" row matching the Reporte Semanal tab. |
| 833 | // Pre-fix Resumen Anual was missing ~1.65 M€ YTD of Catalunya / |
| 834 | // Castellón / Valencia / etc. Zenital invoices because the |
| 835 | // Desconocido pass simply didn't exist here. |
| 836 | $unattributedByRegion = []; |
| 837 | $skRegionToTitan = $this->getZenitalRegionMap(); |
| 838 | if (! empty($skRegionToTitan)) { |
| 839 | try { |
| 840 | DB::connection('zenital')->statement('SET statement_timeout = '.(int) env('ZENITAL_DB_STATEMENT_TIMEOUT_MS', 15000)); |
| 841 | |
| 842 | $localByName = TblFinanceSedes::where('is_active', 1) |
| 843 | ->whereNotNull('company_id') |
| 844 | ->get() |
| 845 | ->keyBy(fn ($s) => mb_strtolower(trim((string) $s->name))); |
| 846 | $clientSucursalAliases = [ |
| 847 | 'jomar alcarreña' => 'alcarrena', |
| 848 | ]; |
| 849 | |
| 850 | $regionKeys = array_keys($skRegionToTitan); |
| 851 | $regionPlaceholders = implode(',', array_fill(0, count($regionKeys), '?')); |
| 852 | $bindings = array_merge( |
| 853 | array_map('intval', $regionKeys), |
| 854 | [$year, $year - 1, $year - 2], |
| 855 | ); |
| 856 | |
| 857 | // BI series-letter rules — see |
| 858 | // \App\Console\Commands\SendFinanceReport::seriesAttributionMap. |
| 859 | $descRows = DB::connection('zenital')->select(" |
| 860 | SELECT sk_region, serie, client_sucursal, ano, mes, |
| 861 | SUM(invoice_amount) AS total |
| 862 | FROM ( |
| 863 | SELECT f.num_factura, f.sk_region, |
| 864 | UPPER(SUBSTRING(f.num_factura FROM 1 FOR 1)) AS serie, |
| 865 | c.sucursal AS client_sucursal, d.ano, |
| 866 | d.num_mes AS mes, |
| 867 | MAX(f.base_imponible) AS invoice_amount |
| 868 | FROM fact_facturacion f |
| 869 | JOIN dim_fecha d ON d.sk_fecha = f.sk_fecha_emision |
| 870 | LEFT JOIN dim_cliente_facturacion c |
| 871 | ON c.sk_cliente_facturacion = f.sk_cliente_facturacion |
| 872 | WHERE f.sk_sucursal = -1 |
| 873 | AND f.fuente_datos = 'GESTIONA' |
| 874 | AND f.sk_region IN ({$regionPlaceholders}) |
| 875 | AND d.ano IN (?, ?, ?) |
| 876 | GROUP BY f.num_factura, f.sk_region, serie, c.sucursal, d.ano, d.num_mes |
| 877 | ) per_invoice |
| 878 | GROUP BY sk_region, serie, client_sucursal, ano, mes |
| 879 | ", $bindings); |
| 880 | |
| 881 | $seriesMap = SendFinanceReport::seriesAttributionMap(); |
| 882 | $simplification = SendFinanceReport::sedeSimplificationMap(); |
| 883 | |
| 884 | foreach ($descRows as $row) { |
| 885 | $ano = (int) $row->ano; |
| 886 | $mes = (int) $row->mes; |
| 887 | $total = (float) $row->total; |
| 888 | $skRegion = (int) $row->sk_region; |
| 889 | $serie = mb_strtoupper(trim((string) ($row->serie ?? ''))); |
| 890 | |
| 891 | $bySerieTarget = $seriesMap[$skRegion][$serie] |
| 892 | ?? $seriesMap[$skRegion]['*'] |
| 893 | ?? null; |
| 894 | |
| 895 | $sede = null; |
| 896 | if ($bySerieTarget !== null && $bySerieTarget !== '__DROP__') { |
| 897 | $canonical = $simplification[$bySerieTarget] ?? $bySerieTarget; |
| 898 | $sede = $localByName[$canonical] ?? null; |
| 899 | } |
| 900 | // No client_sucursal fallback — see getResumenData. |
| 901 | |
| 902 | if ($sede !== null) { |
| 903 | $idx[(int) $sede->id][$ano][$mes] = ($idx[(int) $sede->id][$ano][$mes] ?? 0) + $total; |
| 904 | |
| 905 | continue; |
| 906 | } |
| 907 | |
| 908 | $titanRegionId = $skRegionToTitan[$skRegion] ?? null; |
| 909 | if ($titanRegionId === null) { |
| 910 | continue; |
| 911 | } |
| 912 | $field = $ano === $year ? 'actuals' |
| 913 | : ($ano === $year - 1 ? 'actuals_n1' |
| 914 | : ($ano === $year - 2 ? 'actuals_n2' : null)); |
| 915 | if ($field === null) { |
| 916 | continue; |
| 917 | } |
| 918 | $unattributedByRegion[$titanRegionId][$mes][$field] |
| 919 | = ($unattributedByRegion[$titanRegionId][$mes][$field] ?? 0.0) + $total; |
| 920 | } |
| 921 | } catch (\Exception $e) { |
| 922 | Log::channel('third-party')->warning('Zenital Desconocido query failed in list_resumen: '.$e->getMessage()); |
| 923 | } |
| 924 | } |
| 925 | |
| 926 | // Budget de MySQL: [sede_id][mes] = amount |
| 927 | $budgets = TblFinanceBudgetAnual::whereIn('company_id', $companyIds) |
| 928 | ->where('year', $year) |
| 929 | ->get(['sede_id', 'month', 'amount']); |
| 930 | $budgetIdx = []; |
| 931 | foreach ($budgets as $b) { |
| 932 | $budgetIdx[$b->sede_id][$b->month] = (float) $b->amount; |
| 933 | } |
| 934 | |
| 935 | // Construir resultado (keyed by sede_id, handles multiple sk_sucursals per sede) |
| 936 | // Also include any sede that picked up Desconocido re-attributed |
| 937 | // invoices through the pass above — those sedes don't appear in |
| 938 | // $zenitalToSede (they're not in dim_sucursal) but they DO have |
| 939 | // values in $idx and should emit a row. |
| 940 | $data = []; |
| 941 | $processedSedes = []; |
| 942 | $emittableSedeIds = array_unique(array_merge( |
| 943 | array_values($zenitalToSede), |
| 944 | array_filter(array_keys($idx), 'is_numeric') |
| 945 | )); |
| 946 | foreach ($emittableSedeIds as $sedeId) { |
| 947 | if (isset($processedSedes[$sedeId])) { |
| 948 | continue; |
| 949 | } |
| 950 | $processedSedes[$sedeId] = true; |
| 951 | |
| 952 | for ($month = 1; $month <= 12; $month++) { |
| 953 | $actuals = $idx[$sedeId][$year][$month] ?? null; |
| 954 | $n1 = $idx[$sedeId][$year - 1][$month] ?? null; |
| 955 | $n2 = $idx[$sedeId][$year - 2][$month] ?? null; |
| 956 | $budget = $budgetIdx[$sedeId][$month] ?? null; |
| 957 | |
| 958 | if ($actuals === null && $n1 === null && $n2 === null && $budget === null) { |
| 959 | continue; |
| 960 | } |
| 961 | |
| 962 | $data[] = [ |
| 963 | 'sede_id' => $sedeId, |
| 964 | 'month' => $month, |
| 965 | 'actuals' => $actuals, |
| 966 | 'actuals_n1' => $n1, |
| 967 | 'actuals_n2' => $n2, |
| 968 | 'budget' => $budget, |
| 969 | 'deviation_vs_n1' => ($n1 && $actuals !== null) ? (($actuals - $n1) / $n1) : null, |
| 970 | 'deviation_vs_n2' => ($n2 && $actuals !== null) ? (($actuals - $n2) / $n2) : null, |
| 971 | 'deviation_vs_budget' => ($budget && $actuals !== null) ? (($actuals - $budget) / $budget) : null, |
| 972 | // FIRE-1027: tag the row source so the frontend knows |
| 973 | // whether the cells are editable. 'zenital' rows come |
| 974 | // from the DWH and stay read-only. |
| 975 | 'source' => 'zenital', |
| 976 | ]; |
| 977 | } |
| 978 | } |
| 979 | |
| 980 | // Merge locally stored data for sedes not already covered by Zenital |
| 981 | $localData = TblFinanceResumenAnual::whereIn('company_id', $companyIds) |
| 982 | ->where('year', $year) |
| 983 | ->get(); |
| 984 | |
| 985 | $coveredSedes = []; |
| 986 | foreach ($data as $d) { |
| 987 | $coveredSedes[$d['sede_id'].'-'.$d['month']] = true; |
| 988 | } |
| 989 | foreach ($localData as $row) { |
| 990 | if (isset($coveredSedes[$row->sede_id.'-'.$row->month])) { |
| 991 | continue; |
| 992 | } |
| 993 | |
| 994 | $data[] = [ |
| 995 | 'sede_id' => $row->sede_id, |
| 996 | 'month' => $row->month, |
| 997 | 'actuals' => $row->actuals, |
| 998 | 'actuals_n1' => $row->actuals_n1, |
| 999 | 'actuals_n2' => $row->actuals_n2, |
| 1000 | 'budget' => $row->budget, |
| 1001 | 'deviation_vs_n1' => $row->deviation_vs_n1, |
| 1002 | 'deviation_vs_n2' => $row->deviation_vs_n2, |
| 1003 | 'deviation_vs_budget' => $row->deviation_vs_budget, |
| 1004 | // FIRE-1027: surface the stored source so the frontend |
| 1005 | // can tell apart zenital / google_drive / manual_override |
| 1006 | // rows and gate editability accordingly. |
| 1007 | 'source' => $row->source, |
| 1008 | ]; |
| 1009 | $coveredSedes[$row->sede_id.'-'.$row->month] = true; |
| 1010 | } |
| 1011 | |
| 1012 | // Jorge's request: the Reporte Semanal tab picks up Google-Drive |
| 1013 | // imported actuals (Aeroextinción, Cano Lopera, etc.) but Resumen |
| 1014 | // Anual was ignoring them. Aggregate tbl_finance_report_semanal |
| 1015 | // by (sede, year, month) for year / year-1 / year-2 and fold in |
| 1016 | // any sede+month the loops above didn't already cover. |
| 1017 | $weeklyAgg = TblFinanceReportSemanal::whereIn('company_id', $companyIds) |
| 1018 | ->whereIn('year', [$year, $year - 1, $year - 2]) |
| 1019 | ->selectRaw('sede_id, year, month, SUM(actuals) as total_actuals') |
| 1020 | ->groupBy('sede_id', 'year', 'month') |
| 1021 | ->get(); |
| 1022 | |
| 1023 | $weeklyIdx = []; |
| 1024 | foreach ($weeklyAgg as $row) { |
| 1025 | if ($row->total_actuals === null) { |
| 1026 | continue; |
| 1027 | } |
| 1028 | $weeklyIdx[$row->sede_id][(int) $row->year][(int) $row->month] = (float) $row->total_actuals; |
| 1029 | } |
| 1030 | |
| 1031 | foreach ($weeklyIdx as $sedeId => $perYear) { |
| 1032 | for ($month = 1; $month <= 12; $month++) { |
| 1033 | $key = $sedeId.'-'.$month; |
| 1034 | if (isset($coveredSedes[$key])) { |
| 1035 | continue; |
| 1036 | } |
| 1037 | $actuals = $perYear[$year][$month] ?? null; |
| 1038 | $n1 = $perYear[$year - 1][$month] ?? null; |
| 1039 | $n2 = $perYear[$year - 2][$month] ?? null; |
| 1040 | $budget = $budgetIdx[$sedeId][$month] ?? null; |
| 1041 | |
| 1042 | if ($actuals === null && $n1 === null && $n2 === null && $budget === null) { |
| 1043 | continue; |
| 1044 | } |
| 1045 | |
| 1046 | $data[] = [ |
| 1047 | 'sede_id' => $sedeId, |
| 1048 | 'month' => $month, |
| 1049 | 'actuals' => $actuals, |
| 1050 | 'actuals_n1' => $n1, |
| 1051 | 'actuals_n2' => $n2, |
| 1052 | 'budget' => $budget, |
| 1053 | 'deviation_vs_n1' => ($n1 && $actuals !== null) ? (($actuals - $n1) / $n1) : null, |
| 1054 | 'deviation_vs_n2' => ($n2 && $actuals !== null) ? (($actuals - $n2) / $n2) : null, |
| 1055 | 'deviation_vs_budget' => ($budget && $actuals !== null) ? (($actuals - $budget) / $budget) : null, |
| 1056 | // FIRE-1027: this pass synthesises rows from the |
| 1057 | // weekly Drive imports — flag them so the UI shows |
| 1058 | // them as editable even though no resumen row exists |
| 1059 | // yet (one will be created on the user's first edit). |
| 1060 | 'source' => 'google_drive', |
| 1061 | ]; |
| 1062 | $coveredSedes[$key] = true; |
| 1063 | } |
| 1064 | } |
| 1065 | |
| 1066 | // Berta audit follow-up: a sede with ONLY budget data (no Zenital |
| 1067 | // facturación, no Drive-imported actuals, no manually-edited |
| 1068 | // resumen row) was invisible in Resumen Anual. UI-created sedes |
| 1069 | // typically land here when the operator first adds them — they |
| 1070 | // have budget rows in tbl_finance_budget_anual but nothing else |
| 1071 | // until Zenital or Drive starts feeding them. Walk the budget |
| 1072 | // index and add entries for any (sede, month) not already |
| 1073 | // covered, so the operator can see the rows they just typed. |
| 1074 | foreach ($budgetIdx as $sedeId => $perMonth) { |
| 1075 | foreach ($perMonth as $month => $budget) { |
| 1076 | $key = $sedeId.'-'.$month; |
| 1077 | if (isset($coveredSedes[$key])) { |
| 1078 | continue; |
| 1079 | } |
| 1080 | // Skip null AND zero — zero-amount rows are placeholders |
| 1081 | // (created by past upserts where the operator typed 0 |
| 1082 | // instead of using the new delete button). They'd just |
| 1083 | // add visual noise in Resumen with no data to look at. |
| 1084 | if (! $budget) { |
| 1085 | continue; |
| 1086 | } |
| 1087 | $data[] = [ |
| 1088 | 'sede_id' => $sedeId, |
| 1089 | 'month' => (int) $month, |
| 1090 | 'actuals' => null, |
| 1091 | 'actuals_n1' => null, |
| 1092 | 'actuals_n2' => null, |
| 1093 | 'budget' => $budget, |
| 1094 | 'deviation_vs_n1' => null, |
| 1095 | 'deviation_vs_n2' => null, |
| 1096 | 'deviation_vs_budget' => null, |
| 1097 | // No actuals source yet — treat as manually editable |
| 1098 | // so Berta can type N-1/N-2 directly if she wants. |
| 1099 | 'source' => 'manual_override', |
| 1100 | ]; |
| 1101 | $coveredSedes[$key] = true; |
| 1102 | } |
| 1103 | } |
| 1104 | |
| 1105 | // Per-region YTD totals — share the email's aggregator so the |
| 1106 | // Resumen Anual "Total Region" YTD column reads the SAME numbers |
| 1107 | // the Cierre Mensual preview shows. We instantiate |
| 1108 | // SendFinanceReport, call its getResumenData per company with |
| 1109 | // the user-chosen "Hasta {mes}" cutoff, and run the result |
| 1110 | // through aggregateAllReports — same code path as the email. |
| 1111 | // |
| 1112 | // IMPORTANT: the per-sede `data` array above is scoped to the |
| 1113 | // logged-in user's companies via $companyIds. For the region |
| 1114 | // grand totals we expand to ALL active finance companies — the |
| 1115 | // email's SendFinanceReport does the same when a recipient |
| 1116 | // covers "all regions". Without this expansion, region totals |
| 1117 | // silently lose any sede whose home company isn't in the user's |
| 1118 | // TblCompanyUsers mapping, even though Jorge's whitelist would |
| 1119 | // attribute that sede to a visible region. |
| 1120 | $regionYtdTotals = []; |
| 1121 | try { |
| 1122 | $aggregatorCompanyIds = TblFinanceSedes::where('is_active', 1) |
| 1123 | ->pluck('company_id') |
| 1124 | ->unique() |
| 1125 | ->filter() |
| 1126 | ->values() |
| 1127 | ->toArray(); |
| 1128 | $cmd = new SendFinanceReport; |
| 1129 | $reportsByCompany = []; |
| 1130 | foreach ($aggregatorCompanyIds as $cid) { |
| 1131 | $company = TblCompanies::find($cid); |
| 1132 | if (! $company) { |
| 1133 | continue; |
| 1134 | } |
| 1135 | $monthlyData = $cmd->getResumenData((int) $cid, (int) $year, (int) $monthCutoff, 'monthly'); |
| 1136 | if (! empty($monthlyData['actuals_ytd']) || ! empty($monthlyData['by_sede'])) { |
| 1137 | $reportsByCompany[$cid] = [ |
| 1138 | 'company' => $company, |
| 1139 | // weekly is not used by the aggregateAllReports |
| 1140 | // 'monthly' section but the method iterates both, |
| 1141 | // so pass the same payload to keep it happy. |
| 1142 | 'weekly' => $monthlyData, |
| 1143 | 'monthly' => $monthlyData, |
| 1144 | ]; |
| 1145 | } |
| 1146 | } |
| 1147 | if (! empty($reportsByCompany)) { |
| 1148 | $aggregated = $cmd->aggregateAllReports($reportsByCompany, 'monthly'); |
| 1149 | $byRegion = $aggregated['monthly']['by_region'] ?? []; |
| 1150 | foreach ($byRegion as $regionId => $vals) { |
| 1151 | if (! is_numeric($regionId)) { |
| 1152 | continue; // skip 'company-X' fallback buckets |
| 1153 | } |
| 1154 | // FIRE-1094 follow-up (n-2 total bug): the email's |
| 1155 | // aggregateAllReports() only tracks actuals + actuals_n1 |
| 1156 | // + budget. We deliberately do NOT publish an n-2 key |
| 1157 | // here — the frontend keeps the per-sede fallback for |
| 1158 | // n-2, which already respects the "Hasta {mes}" cutoff |
| 1159 | // via sumResumenForSede(). Previously this slot was |
| 1160 | // hardcoded to 0.0, which the frontend then displayed |
| 1161 | // as the Total n-2 column even when per-sede data |
| 1162 | // existed. |
| 1163 | $regionYtdTotals[(int) $regionId] = [ |
| 1164 | 'actuals' => (float) ($vals['actuals'] ?? 0), |
| 1165 | 'actuals_n1' => (float) ($vals['actuals_n1'] ?? 0), |
| 1166 | 'budget' => (float) ($vals['budget'] ?? 0), |
| 1167 | ]; |
| 1168 | } |
| 1169 | } |
| 1170 | } catch (\Throwable $e) { |
| 1171 | // If the email aggregator fails for any reason, fall back to |
| 1172 | // an empty totals map — the frontend will sum per-sede on |
| 1173 | // its own. Don't break Resumen Anual just because the |
| 1174 | // alignment call hiccuped. |
| 1175 | Log::channel('third-party')->warning('list_resumen — shared aggregator failed: '.$e->getMessage()); |
| 1176 | $regionYtdTotals = []; |
| 1177 | } |
| 1178 | |
| 1179 | return response([ |
| 1180 | 'message' => 'OK', |
| 1181 | 'data' => $data, |
| 1182 | 'unattributed_by_region' => $unattributedByRegion, |
| 1183 | 'region_ytd_totals' => $regionYtdTotals, |
| 1184 | ]); |
| 1185 | } catch (\Exception $e) { |
| 1186 | /** @disregard P1014 */ |
| 1187 | $e->exceptionCode = 'LIST_RESUMEN_EXCEPTION'; |
| 1188 | report($e); |
| 1189 | |
| 1190 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 1191 | } |
| 1192 | } |
| 1193 | |
| 1194 | /** |
| 1195 | * POST /finance/resumen/load |
| 1196 | * Llamado desde el job automático del día 1 de cada mes. |
| 1197 | * Body: { year, month, rows: [{ sede_id, actuals, actuals_n1, actuals_n2 }] } |
| 1198 | */ |
| 1199 | public function load_resumen(Request $request) |
| 1200 | { |
| 1201 | try { |
| 1202 | $company = $this->getCompany($request); |
| 1203 | $year = $request->input('year'); |
| 1204 | $month = $request->input('month'); |
| 1205 | $rows = $request->input('rows', []); |
| 1206 | |
| 1207 | // FIRE-1148: prefetch both the existing resumen rows and the |
| 1208 | // matching budget rows in two queries before the foreach, then |
| 1209 | // do in-memory lookups inside. Pre-fix this method fired |
| 1210 | // 3 queries per row (existing-check, budget-lookup, updateOrCreate) |
| 1211 | // × N rows from the cron payload. The two prefetches hit |
| 1212 | // idx_finresumen_company_sede_year / idx_finbudget_company_sede_year |
| 1213 | // (added by FIRE-1149) directly. |
| 1214 | $sedeIds = array_unique(array_map(fn ($r) => (int) $r['sede_id'], $rows)); |
| 1215 | |
| 1216 | $existingBySedeId = TblFinanceResumenAnual::where('company_id', $company->company_id) |
| 1217 | ->where('year', $year) |
| 1218 | ->where('month', $month) |
| 1219 | ->whereIn('sede_id', $sedeIds) |
| 1220 | ->get() |
| 1221 | ->keyBy('sede_id'); |
| 1222 | |
| 1223 | $budgetBySedeId = TblFinanceBudgetAnual::where('company_id', $company->company_id) |
| 1224 | ->where('year', $year) |
| 1225 | ->where('month', $month) |
| 1226 | ->whereIn('sede_id', $sedeIds) |
| 1227 | ->pluck('amount', 'sede_id'); |
| 1228 | |
| 1229 | DB::transaction(function () use ($company, $year, $month, $rows, $existingBySedeId, $budgetBySedeId) { |
| 1230 | foreach ($rows as $row) { |
| 1231 | // FIRE-1027: don't clobber rows the user has manually |
| 1232 | // edited. The Zenital cron only owns rows whose source |
| 1233 | // is 'zenital' or NULL. 'google_drive' (Drive imports) |
| 1234 | // and 'manual_override' (UI edits) are user-managed. |
| 1235 | $existing = $existingBySedeId[$row['sede_id']] ?? null; |
| 1236 | if ($existing && in_array($existing->source, ['google_drive', 'manual_override'], true)) { |
| 1237 | continue; |
| 1238 | } |
| 1239 | |
| 1240 | $budget = $budgetBySedeId[$row['sede_id']] ?? null; |
| 1241 | |
| 1242 | $actuals = $row['actuals']; |
| 1243 | $n1 = $row['actuals_n1'] ?? null; |
| 1244 | $n2 = $row['actuals_n2'] ?? null; |
| 1245 | |
| 1246 | TblFinanceResumenAnual::updateOrCreate( |
| 1247 | [ |
| 1248 | 'company_id' => $company->company_id, |
| 1249 | 'sede_id' => $row['sede_id'], |
| 1250 | 'year' => $year, |
| 1251 | 'month' => $month, |
| 1252 | ], |
| 1253 | [ |
| 1254 | 'actuals' => $actuals, |
| 1255 | 'actuals_n1' => $n1, |
| 1256 | 'actuals_n2' => $n2, |
| 1257 | 'budget' => $budget, |
| 1258 | 'deviation_vs_n1' => ($n1 && $n1 != 0) ? (($actuals - $n1) / $n1) : null, |
| 1259 | 'deviation_vs_n2' => ($n2 && $n2 != 0) ? (($actuals - $n2) / $n2) : null, |
| 1260 | 'deviation_vs_budget' => ($budget && $budget != 0) ? (($actuals - $budget) / $budget) : null, |
| 1261 | 'source' => 'zenital', |
| 1262 | 'loaded_at' => now(), |
| 1263 | ] |
| 1264 | ); |
| 1265 | } |
| 1266 | }); |
| 1267 | |
| 1268 | return response(['message' => 'OK']); |
| 1269 | } catch (\Exception $e) { |
| 1270 | /** @disregard P1014 */ |
| 1271 | $e->exceptionCode = 'LOAD_RESUMEN_EXCEPTION'; |
| 1272 | report($e); |
| 1273 | |
| 1274 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 1275 | } |
| 1276 | } |
| 1277 | |
| 1278 | // ------------------------------------------------------- |
| 1279 | // REPORT SEMANAL (solo lectura desde front, escritura automática) |
| 1280 | // ------------------------------------------------------- |
| 1281 | |
| 1282 | /** |
| 1283 | * GET /finance/report-semanal?year=2025&month=10 |
| 1284 | * YTD (meses 1..month) calculado en tiempo real desde Zenital. |
| 1285 | * Budget y Previsión YTD se leen de MySQL. |
| 1286 | * sede_id = zenitalIdToSedeId(sk_sucursal). |
| 1287 | */ |
| 1288 | public function list_report_semanal(Request $request) |
| 1289 | { |
| 1290 | try { |
| 1291 | $companyIds = $this->getCompanyIds($request); |
| 1292 | $year = (int) $request->query('year', date('Y')); |
| 1293 | $month = (int) $request->query('month', date('n')); |
| 1294 | |
| 1295 | // Map [sk_sucursal_zenital => tbl_finance_sedes.id]. See the note |
| 1296 | // in list_resumen — buildZenitalSedeMap returns the hardcoded id, |
| 1297 | // we translate to the local sede id so the frontend can match. |
| 1298 | $zenitalToSede = $this->resolveZenitalSedeMap($companyIds); |
| 1299 | |
| 1300 | // Berta audit follow-up: same as list_resumen — do not short- |
| 1301 | // circuit when the company has no Zenital-mapped sedes. The |
| 1302 | // local merges below cover UI-created sedes correctly when we |
| 1303 | // let execution fall through to them. |
| 1304 | $zenitalIds = array_keys($zenitalToSede); |
| 1305 | |
| 1306 | // Datos mensuales desde data warehouse (año actual y año |
| 1307 | // anterior, solo el mes seleccionado). Timeouts explicados |
| 1308 | // en list_resumen — mismo motivo aquí: si el DWH no contesta |
| 1309 | // o las consultas se cuelgan, caer rápido a lo que tengamos |
| 1310 | // en tbl_finance_report_semanal local. |
| 1311 | putenv('PGCONNECT_TIMEOUT='.env('ZENITAL_DB_CONNECT_TIMEOUT', 5)); |
| 1312 | $ytdIdx = []; |
| 1313 | $latestDates = collect(); |
| 1314 | try { |
| 1315 | DB::connection('zenital')->statement('SET statement_timeout = '.(int) env('ZENITAL_DB_STATEMENT_TIMEOUT_MS', 15000)); |
| 1316 | |
| 1317 | // Per-invoice dedup before summing — collapse duplicate |
| 1318 | // fact_facturacion rows (same num_factura, same sucursal) |
| 1319 | // before aggregating. See SendFinanceReport::getResumenData |
| 1320 | // for the full rationale; tl;dr STG had every Alcarrena |
| 1321 | // invoice recorded twice and the YTD email reported 2x. |
| 1322 | $skPlaceholders = implode(',', array_fill(0, count($zenitalIds), '?')); |
| 1323 | $bindings = array_merge( |
| 1324 | array_map('intval', $zenitalIds), |
| 1325 | [$year, $year - 1, $month], |
| 1326 | ); |
| 1327 | |
| 1328 | $facturacion = DB::connection('zenital')->select(" |
| 1329 | SELECT sk_sucursal, ano, SUM(invoice_amount) AS total |
| 1330 | FROM ( |
| 1331 | SELECT f.num_factura, f.sk_sucursal, d.ano, |
| 1332 | MAX(f.base_imponible) AS invoice_amount |
| 1333 | FROM fact_facturacion f |
| 1334 | JOIN dim_fecha d ON d.sk_fecha = f.sk_fecha_emision |
| 1335 | WHERE f.sk_sucursal IN ({$skPlaceholders}) |
| 1336 | AND d.ano IN (?, ?) |
| 1337 | AND d.num_mes = ? |
| 1338 | GROUP BY f.num_factura, f.sk_sucursal, d.ano |
| 1339 | ) per_invoice |
| 1340 | GROUP BY sk_sucursal, ano |
| 1341 | ", $bindings); |
| 1342 | |
| 1343 | // Catalunya empty-actuals follow-up — same pattern list_resumen |
| 1344 | // already uses on line ~851. The runtime sucursal map can route |
| 1345 | // more than one sk_sucursal to the same local sede (Zenital has |
| 1346 | // both "AirFeu" sk=91 cod=24 and "Airfeu" sk=92 cod=19 — both |
| 1347 | // resolve to local "AirFeu" sede id 24; same for Vivo/Vivó, |
| 1348 | // Robles/Robles Legacy, SeguCor/SeguCor Legacy). Key the index |
| 1349 | // by sede_id and SUM across sk_sucursals, otherwise the per-row |
| 1350 | // emission below produces two entries per sede and the |
| 1351 | // frontend's getReportBySede[0] sometimes picks the empty one |
| 1352 | // (Valencia lost 544 K€ of AirFeu this way on stg April 2026). |
| 1353 | foreach ($facturacion as $row) { |
| 1354 | $sedeId = $zenitalToSede[$row->sk_sucursal] ?? $row->sk_sucursal; |
| 1355 | $ytdIdx[$sedeId][$row->ano] = ($ytdIdx[$sedeId][$row->ano] ?? 0) + (float) $row->total; |
| 1356 | } |
| 1357 | |
| 1358 | // Fecha más reciente de emisión por sede (keep the MAX across |
| 1359 | // every sk_sucursal that maps to the same sede). Same dedup |
| 1360 | // rationale as the index above. |
| 1361 | $latestRows = DB::connection('zenital') |
| 1362 | ->table('fact_facturacion as f') |
| 1363 | ->join('dim_fecha as d', 'f.sk_fecha_emision', '=', 'd.sk_fecha') |
| 1364 | ->whereIn('f.sk_sucursal', $zenitalIds) |
| 1365 | ->where('d.ano', $year) |
| 1366 | ->where('d.num_mes', $month) |
| 1367 | ->selectRaw('f.sk_sucursal, MAX(d.fecha) as latest_date') |
| 1368 | ->groupBy('f.sk_sucursal') |
| 1369 | ->get(); |
| 1370 | foreach ($latestRows as $r) { |
| 1371 | $sedeId = $zenitalToSede[$r->sk_sucursal] ?? $r->sk_sucursal; |
| 1372 | $current = $latestDates->get($sedeId); |
| 1373 | if ($current === null || (string) $r->latest_date > (string) $current->latest_date) { |
| 1374 | $latestDates->put($sedeId, $r); |
| 1375 | } |
| 1376 | } |
| 1377 | } catch (\Exception $e) { |
| 1378 | Log::channel('third-party')->warning('Zenital connection failed in list_report_semanal, using local data only: '.$e->getMessage()); |
| 1379 | } |
| 1380 | |
| 1381 | // Catalunya empty-actuals follow-up: GESTIONA invoices in Zenital |
| 1382 | // often arrive with sk_sucursal = -1 ("Desconocido") at the |
| 1383 | // *invoice* level, but the *client* record they belong to does |
| 1384 | // carry a sucursal label in `dim_cliente_facturacion.sucursal` |
| 1385 | // (this is what Power BI groups by). For April 2026 Catalunya |
| 1386 | // alone, that re-attribution reclaims ≈ 459 K€ from the |
| 1387 | // unattributed pile and routes it to the real sedes |
| 1388 | // (Extintores Clemente, Gallex, Ingesfoc…). The remaining |
| 1389 | // ≈ 146 K€ where the client also reads "Desconocido" / NULL |
| 1390 | // stays in the regional bucket. |
| 1391 | // |
| 1392 | // Build the same lowercase-name-to-active-sede lookup the |
| 1393 | // sucursal map uses internally — same aliases for accent / |
| 1394 | // sub-entity quirks (Jomar Alcarreña → Alcarrena). |
| 1395 | $localByName = TblFinanceSedes::where('is_active', 1) |
| 1396 | ->whereNotNull('company_id') |
| 1397 | ->get() |
| 1398 | ->keyBy(fn ($s) => mb_strtolower(trim((string) $s->name))); |
| 1399 | $clientSucursalAliases = [ |
| 1400 | 'jomar alcarreña' => 'alcarrena', |
| 1401 | ]; |
| 1402 | $unattributedByRegion = []; |
| 1403 | $skRegionToTitan = $this->getZenitalRegionMap(); |
| 1404 | if (! empty($skRegionToTitan) && ! $localByName->isEmpty()) { |
| 1405 | try { |
| 1406 | DB::connection('zenital')->statement('SET statement_timeout = '.(int) env('ZENITAL_DB_STATEMENT_TIMEOUT_MS', 15000)); |
| 1407 | |
| 1408 | // Per-invoice dedup — same rationale as the sucursal |
| 1409 | // pass above; fact_facturacion can carry duplicate |
| 1410 | // rows per num_factura even for Desconocido invoices. |
| 1411 | $regionKeys = array_keys($skRegionToTitan); |
| 1412 | $regionPlaceholders = implode(',', array_fill(0, count($regionKeys), '?')); |
| 1413 | $bindings = array_merge( |
| 1414 | array_map('intval', $regionKeys), |
| 1415 | [$year, $year - 1, $month], |
| 1416 | ); |
| 1417 | |
| 1418 | // BI department rules — see |
| 1419 | // \App\Console\Commands\SendFinanceReport::seriesAttributionMap |
| 1420 | // for the source of these mappings. |
| 1421 | $descRows = DB::connection('zenital')->select(" |
| 1422 | SELECT sk_region, serie, client_sucursal, ano, |
| 1423 | SUM(invoice_amount) AS total |
| 1424 | FROM ( |
| 1425 | SELECT f.num_factura, f.sk_region, |
| 1426 | UPPER(SUBSTRING(f.num_factura FROM 1 FOR 1)) AS serie, |
| 1427 | c.sucursal AS client_sucursal, d.ano, |
| 1428 | MAX(f.base_imponible) AS invoice_amount |
| 1429 | FROM fact_facturacion f |
| 1430 | JOIN dim_fecha d ON d.sk_fecha = f.sk_fecha_emision |
| 1431 | LEFT JOIN dim_cliente_facturacion c |
| 1432 | ON c.sk_cliente_facturacion = f.sk_cliente_facturacion |
| 1433 | WHERE f.sk_sucursal = -1 |
| 1434 | AND f.fuente_datos = 'GESTIONA' |
| 1435 | AND f.sk_region IN ({$regionPlaceholders}) |
| 1436 | AND d.ano IN (?, ?) |
| 1437 | AND d.num_mes = ? |
| 1438 | GROUP BY f.num_factura, f.sk_region, serie, c.sucursal, d.ano |
| 1439 | ) per_invoice |
| 1440 | GROUP BY sk_region, serie, client_sucursal, ano |
| 1441 | ", $bindings); |
| 1442 | |
| 1443 | $seriesMap = SendFinanceReport::seriesAttributionMap(); |
| 1444 | $simplification = SendFinanceReport::sedeSimplificationMap(); |
| 1445 | |
| 1446 | foreach ($descRows as $row) { |
| 1447 | $ano = (int) $row->ano; |
| 1448 | $total = (float) $row->total; |
| 1449 | $skRegion = (int) $row->sk_region; |
| 1450 | $serie = mb_strtoupper(trim((string) ($row->serie ?? ''))); |
| 1451 | |
| 1452 | $bySerieTarget = $seriesMap[$skRegion][$serie] |
| 1453 | ?? $seriesMap[$skRegion]['*'] |
| 1454 | ?? null; |
| 1455 | |
| 1456 | $sede = null; |
| 1457 | if ($bySerieTarget !== null && $bySerieTarget !== '__DROP__') { |
| 1458 | $canonical = $simplification[$bySerieTarget] ?? $bySerieTarget; |
| 1459 | $sede = $localByName[$canonical] ?? null; |
| 1460 | } |
| 1461 | // No client_sucursal fallback — see getResumenData. |
| 1462 | |
| 1463 | if ($sede !== null) { |
| 1464 | // Re-attribute to the real sede so the existing |
| 1465 | // output loop picks it up alongside any |
| 1466 | // sk_sucursal-keyed Zenital totals. |
| 1467 | $ytdIdx[(int) $sede->id][$ano] = ($ytdIdx[(int) $sede->id][$ano] ?? 0) + $total; |
| 1468 | |
| 1469 | continue; |
| 1470 | } |
| 1471 | |
| 1472 | // Couldn't match — stays in the regional bucket. |
| 1473 | $titanRegionId = $skRegionToTitan[$skRegion] ?? null; |
| 1474 | if ($titanRegionId === null) { |
| 1475 | continue; |
| 1476 | } |
| 1477 | if (! isset($unattributedByRegion[$titanRegionId])) { |
| 1478 | $unattributedByRegion[$titanRegionId] = ['actuals' => 0.0, 'actuals_n1' => 0.0]; |
| 1479 | } |
| 1480 | $field = $ano === $year ? 'actuals' : 'actuals_n1'; |
| 1481 | $unattributedByRegion[$titanRegionId][$field] += $total; |
| 1482 | } |
| 1483 | } catch (\Exception $e) { |
| 1484 | Log::channel('third-party')->warning('Zenital Desconocido query failed in list_report_semanal: '.$e->getMessage()); |
| 1485 | } |
| 1486 | } |
| 1487 | |
| 1488 | // Budget mensual de MySQL: solo el mes seleccionado |
| 1489 | $budgets = TblFinanceBudgetAnual::whereIn('company_id', $companyIds) |
| 1490 | ->where('year', $year) |
| 1491 | ->where('month', $month) |
| 1492 | ->get(['sede_id', 'amount']); |
| 1493 | $budgetYtd = []; |
| 1494 | foreach ($budgets as $b) { |
| 1495 | $budgetYtd[$b->sede_id] = (float) $b->amount; |
| 1496 | } |
| 1497 | |
| 1498 | // Previsión mensual de MySQL: solo el mes seleccionado |
| 1499 | $previsions = TblFinancePrevisionAnual::whereIn('company_id', $companyIds) |
| 1500 | ->where('year', $year) |
| 1501 | ->where('month', $month) |
| 1502 | ->get(['sede_id', 'amount']); |
| 1503 | $previsionYtd = []; |
| 1504 | foreach ($previsions as $p) { |
| 1505 | $previsionYtd[$p->sede_id] = (float) $p->amount; |
| 1506 | } |
| 1507 | |
| 1508 | // Berta audit follow-up — Item 3: build an index of manual |
| 1509 | // Resumen Anual entries up front so the Drive aggregation pass |
| 1510 | // below can override its own values when the operator has typed |
| 1511 | // an actuals/N-1 on the Resumen tab. This is the natural |
| 1512 | // hand-off: Resumen Anual is the "source of truth" the operator |
| 1513 | // edits, the weekly report should reflect those edits. |
| 1514 | $resumenOverride = TblFinanceResumenAnual::whereIn('company_id', $companyIds) |
| 1515 | ->where('year', $year) |
| 1516 | ->where('month', $month) |
| 1517 | ->get(['sede_id', 'actuals', 'actuals_n1']); |
| 1518 | $resumenIdx = []; |
| 1519 | foreach ($resumenOverride as $row) { |
| 1520 | $resumenIdx[$row->sede_id] = [ |
| 1521 | 'actuals' => $row->actuals !== null ? (float) $row->actuals : null, |
| 1522 | 'actuals_n1' => $row->actuals_n1 !== null ? (float) $row->actuals_n1 : null, |
| 1523 | ]; |
| 1524 | } |
| 1525 | |
| 1526 | // Construir resultado — keyed by sede_id (handles the multiple |
| 1527 | // sk_sucursals per sede case via $processedSedes; mirrors |
| 1528 | // list_resumen line ~868). |
| 1529 | $data = []; |
| 1530 | $processedSedes = []; |
| 1531 | foreach ($zenitalToSede as $zenitalId => $sedeId) { |
| 1532 | if (isset($processedSedes[$sedeId])) { |
| 1533 | continue; |
| 1534 | } |
| 1535 | $processedSedes[$sedeId] = true; |
| 1536 | |
| 1537 | $zenitalActuals = $ytdIdx[$sedeId][$year] ?? null; |
| 1538 | $zenitalN1 = $ytdIdx[$sedeId][$year - 1] ?? null; |
| 1539 | |
| 1540 | // Catalunya empty-actuals follow-up #2: now that the |
| 1541 | // sucursal map resolves at runtime by name, sedes that |
| 1542 | // exist in `dim_sucursal` but never get invoiced through |
| 1543 | // Zenital (Cuenfa, Cano Lopera, Aeroextinción, …) show |
| 1544 | // up in $zenitalToSede. If we emit a row for them from |
| 1545 | // the Zenital pass with all-null actuals, the Drive pass |
| 1546 | // below sees them as "covered" and skips — losing the |
| 1547 | // Drive-imported figures (sede 59 / Cuenfa lost 31 815 € |
| 1548 | // this way on stg April 2026). Hand-off back to Drive by |
| 1549 | // skipping the Zenital row when there's no Zenital data |
| 1550 | // *and* no manual Resumen override. Budget / Previsión |
| 1551 | // for those sedes still get picked up by the final |
| 1552 | // budget-only pass further down. |
| 1553 | $hasOverride = isset($resumenIdx[$sedeId]); |
| 1554 | $hasZenitalData = $zenitalActuals !== null || $zenitalN1 !== null; |
| 1555 | if (! $hasZenitalData && ! $hasOverride) { |
| 1556 | continue; |
| 1557 | } |
| 1558 | |
| 1559 | // Resumen Anual manual edits override Zenital actuals/N-1 |
| 1560 | // for the same sede+month — that's the contract from |
| 1561 | // FIRE-1027 / Item 3. |
| 1562 | $actuals = $hasOverride && $resumenIdx[$sedeId]['actuals'] !== null |
| 1563 | ? $resumenIdx[$sedeId]['actuals'] |
| 1564 | : $zenitalActuals; |
| 1565 | $n1 = $hasOverride && $resumenIdx[$sedeId]['actuals_n1'] !== null |
| 1566 | ? $resumenIdx[$sedeId]['actuals_n1'] |
| 1567 | : $zenitalN1; |
| 1568 | $budget = $budgetYtd[$sedeId] ?? null; |
| 1569 | $prevision = $previsionYtd[$sedeId] ?? null; |
| 1570 | $weekDate = $latestDates->get($sedeId)?->latest_date ?? null; |
| 1571 | |
| 1572 | if ($actuals === null && $n1 === null && $budget === null && $prevision === null) { |
| 1573 | continue; |
| 1574 | } |
| 1575 | |
| 1576 | $data[] = [ |
| 1577 | 'sede_id' => $sedeId, |
| 1578 | 'year' => $year, |
| 1579 | 'month' => $month, |
| 1580 | 'week_date' => $weekDate, |
| 1581 | 'actuals' => $actuals, |
| 1582 | 'actuals_n1' => $n1, |
| 1583 | 'budget' => $budget, |
| 1584 | 'prevision' => $prevision, |
| 1585 | 'deviation_vs_n1' => ($n1 !== null && $actuals !== null) ? ($actuals - $n1) : null, |
| 1586 | 'deviation_pct_vs_n1' => ($n1 && $actuals !== null) ? (($actuals - $n1) / $n1) : null, |
| 1587 | 'deviation_vs_budget' => ($budget !== null && $actuals !== null) ? ($actuals - $budget) : null, |
| 1588 | 'deviation_pct_vs_budget' => ($budget && $actuals !== null) ? (($actuals - $budget) / $budget) : null, |
| 1589 | 'deviation_vs_prevision' => ($prevision !== null && $actuals !== null) ? ($actuals - $prevision) : null, |
| 1590 | 'deviation_pct_vs_prevision' => ($prevision && $actuals !== null) ? (($actuals - $prevision) / $prevision) : null, |
| 1591 | 'source' => $hasOverride ? 'manual_override' : 'zenital', |
| 1592 | ]; |
| 1593 | } |
| 1594 | |
| 1595 | // Merge Drive-imported data for sedes not already covered by Zenital. |
| 1596 | // The import stores one row per week; aggregate to a single row per |
| 1597 | // sede (summing actuals) so the UI renders exactly like a Zenital |
| 1598 | // sede. n-1 comes from the same table for the prior year. |
| 1599 | $coveredSedes = []; |
| 1600 | foreach ($data as $d) { |
| 1601 | $coveredSedes[$d['sede_id']] = true; |
| 1602 | } |
| 1603 | |
| 1604 | $localAgg = TblFinanceReportSemanal::whereIn('company_id', $companyIds) |
| 1605 | ->where('month', $month) |
| 1606 | ->whereIn('year', [$year, $year - 1]) |
| 1607 | ->selectRaw('sede_id, year, SUM(actuals) as total_actuals, MAX(week_date) as latest_week') |
| 1608 | ->groupBy('sede_id', 'year') |
| 1609 | ->get(); |
| 1610 | |
| 1611 | $localIdx = []; |
| 1612 | foreach ($localAgg as $row) { |
| 1613 | $localIdx[$row->sede_id][(int) $row->year] = [ |
| 1614 | 'total' => $row->total_actuals !== null ? (float) $row->total_actuals : null, |
| 1615 | 'latest_week' => $row->latest_week, |
| 1616 | ]; |
| 1617 | } |
| 1618 | |
| 1619 | foreach ($localIdx as $sedeId => $perYear) { |
| 1620 | if (isset($coveredSedes[$sedeId])) { |
| 1621 | continue; |
| 1622 | } |
| 1623 | $driveActuals = $perYear[$year]['total'] ?? null; |
| 1624 | $driveN1 = $perYear[$year - 1]['total'] ?? null; |
| 1625 | // Same precedence as the Zenital pass — manual Resumen |
| 1626 | // edits override Drive-imported actuals/N-1 (Item 3). |
| 1627 | $hasOverride = isset($resumenIdx[$sedeId]); |
| 1628 | $actuals = $hasOverride && $resumenIdx[$sedeId]['actuals'] !== null |
| 1629 | ? $resumenIdx[$sedeId]['actuals'] |
| 1630 | : $driveActuals; |
| 1631 | $n1 = $hasOverride && $resumenIdx[$sedeId]['actuals_n1'] !== null |
| 1632 | ? $resumenIdx[$sedeId]['actuals_n1'] |
| 1633 | : $driveN1; |
| 1634 | $budget = $budgetYtd[$sedeId] ?? null; |
| 1635 | $prevision = $previsionYtd[$sedeId] ?? null; |
| 1636 | |
| 1637 | if ($actuals === null && $n1 === null && $budget === null && $prevision === null) { |
| 1638 | continue; |
| 1639 | } |
| 1640 | |
| 1641 | $data[] = [ |
| 1642 | 'sede_id' => $sedeId, |
| 1643 | 'year' => $year, |
| 1644 | 'month' => $month, |
| 1645 | 'week_date' => $perYear[$year]['latest_week'] ?? null, |
| 1646 | 'actuals' => $actuals, |
| 1647 | 'actuals_n1' => $n1, |
| 1648 | 'budget' => $budget, |
| 1649 | 'prevision' => $prevision, |
| 1650 | 'deviation_vs_n1' => ($n1 !== null && $actuals !== null) ? ($actuals - $n1) : null, |
| 1651 | 'deviation_pct_vs_n1' => ($n1 && $actuals !== null) ? (($actuals - $n1) / $n1) : null, |
| 1652 | 'deviation_vs_budget' => ($budget !== null && $actuals !== null) ? ($actuals - $budget) : null, |
| 1653 | 'deviation_pct_vs_budget' => ($budget && $actuals !== null) ? (($actuals - $budget) / $budget) : null, |
| 1654 | 'deviation_vs_prevision' => ($prevision !== null && $actuals !== null) ? ($actuals - $prevision) : null, |
| 1655 | 'deviation_pct_vs_prevision' => ($prevision && $actuals !== null) ? (($actuals - $prevision) / $prevision) : null, |
| 1656 | 'source' => $hasOverride ? 'manual_override' : 'google_drive', |
| 1657 | ]; |
| 1658 | $coveredSedes[$sedeId] = true; |
| 1659 | } |
| 1660 | |
| 1661 | // Resumen Anual rows for sedes NOT yet covered above (a sede |
| 1662 | // whose only data is a manual Resumen edit — no Zenital map, |
| 1663 | // no Drive import). Bring those in too so Berta sees the |
| 1664 | // values she typed. |
| 1665 | foreach ($resumenIdx as $sedeId => $vals) { |
| 1666 | if (isset($coveredSedes[$sedeId])) { |
| 1667 | continue; |
| 1668 | } |
| 1669 | $actuals = $vals['actuals']; |
| 1670 | $n1 = $vals['actuals_n1']; |
| 1671 | $budget = $budgetYtd[$sedeId] ?? null; |
| 1672 | $prevision = $previsionYtd[$sedeId] ?? null; |
| 1673 | |
| 1674 | if ($actuals === null && $n1 === null && $budget === null && $prevision === null) { |
| 1675 | continue; |
| 1676 | } |
| 1677 | |
| 1678 | $data[] = [ |
| 1679 | 'sede_id' => $sedeId, |
| 1680 | 'year' => $year, |
| 1681 | 'month' => $month, |
| 1682 | 'week_date' => null, |
| 1683 | 'actuals' => $actuals, |
| 1684 | 'actuals_n1' => $n1, |
| 1685 | 'budget' => $budget, |
| 1686 | 'prevision' => $prevision, |
| 1687 | 'deviation_vs_n1' => ($n1 !== null && $actuals !== null) ? ($actuals - $n1) : null, |
| 1688 | 'deviation_pct_vs_n1' => ($n1 && $actuals !== null) ? (($actuals - $n1) / $n1) : null, |
| 1689 | 'deviation_vs_budget' => ($budget !== null && $actuals !== null) ? ($actuals - $budget) : null, |
| 1690 | 'deviation_pct_vs_budget' => ($budget && $actuals !== null) ? (($actuals - $budget) / $budget) : null, |
| 1691 | 'deviation_vs_prevision' => ($prevision !== null && $actuals !== null) ? ($actuals - $prevision) : null, |
| 1692 | 'deviation_pct_vs_prevision' => ($prevision && $actuals !== null) ? (($actuals - $prevision) / $prevision) : null, |
| 1693 | 'source' => 'manual_override', |
| 1694 | ]; |
| 1695 | $coveredSedes[$sedeId] = true; |
| 1696 | } |
| 1697 | |
| 1698 | // Berta audit follow-up: surface sedes that have budget and/or |
| 1699 | // previsión rows but no actuals yet (Zenital silent, no Drive |
| 1700 | // import, no manual entry). Without this pass a freshly-created |
| 1701 | // sede with only a budget typed in stays invisible on the |
| 1702 | // weekly report. We union budget + prevision keys so either |
| 1703 | // alone is enough to render a row. |
| 1704 | foreach (array_unique(array_merge(array_keys($budgetYtd), array_keys($previsionYtd))) as $sedeId) { |
| 1705 | if (isset($coveredSedes[$sedeId])) { |
| 1706 | continue; |
| 1707 | } |
| 1708 | $budget = $budgetYtd[$sedeId] ?? null; |
| 1709 | $prevision = $previsionYtd[$sedeId] ?? null; |
| 1710 | |
| 1711 | // Skip placeholder zero rows — same rationale as list_resumen. |
| 1712 | if (! $budget && ! $prevision) { |
| 1713 | continue; |
| 1714 | } |
| 1715 | |
| 1716 | $data[] = [ |
| 1717 | 'sede_id' => $sedeId, |
| 1718 | 'year' => $year, |
| 1719 | 'month' => $month, |
| 1720 | 'week_date' => null, |
| 1721 | 'actuals' => null, |
| 1722 | 'actuals_n1' => null, |
| 1723 | 'budget' => $budget, |
| 1724 | 'prevision' => $prevision, |
| 1725 | 'deviation_vs_n1' => null, |
| 1726 | 'deviation_pct_vs_n1' => null, |
| 1727 | 'deviation_vs_budget' => null, |
| 1728 | 'deviation_pct_vs_budget' => null, |
| 1729 | 'deviation_vs_prevision' => null, |
| 1730 | 'deviation_pct_vs_prevision' => null, |
| 1731 | // No actuals source yet — manual-override so Berta can |
| 1732 | // type actuals/N-1 directly on the report tab. |
| 1733 | 'source' => 'manual_override', |
| 1734 | ]; |
| 1735 | $coveredSedes[$sedeId] = true; |
| 1736 | } |
| 1737 | |
| 1738 | // $unattributedByRegion was populated by the Desconocido |
| 1739 | // attribution pass earlier (right after the main Zenital |
| 1740 | // facturación query). What's left in it here is only the |
| 1741 | // money whose client record also reads "Desconocido" — the |
| 1742 | // truly unattributable residual that surfaces as the |
| 1743 | // "Sin asignar a sede" row in the UI. |
| 1744 | return response([ |
| 1745 | 'message' => 'OK', |
| 1746 | 'data' => $data, |
| 1747 | 'unattributed_by_region' => $unattributedByRegion, |
| 1748 | ]); |
| 1749 | } catch (\Exception $e) { |
| 1750 | /** @disregard P1014 */ |
| 1751 | $e->exceptionCode = 'LIST_REPORT_SEMANAL_EXCEPTION'; |
| 1752 | report($e); |
| 1753 | |
| 1754 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 1755 | } |
| 1756 | } |
| 1757 | |
| 1758 | /** |
| 1759 | * POST /finance/report-semanal/load |
| 1760 | * Llamado desde el job automático de cada sábado. |
| 1761 | * Body: { week_date, year, month, rows: [{ sede_id, actuals, actuals_n1, budget, prevision }] } |
| 1762 | */ |
| 1763 | public function load_report_semanal(Request $request) |
| 1764 | { |
| 1765 | try { |
| 1766 | $company = $this->getCompany($request); |
| 1767 | $weekDate = $request->input('week_date'); |
| 1768 | $year = $request->input('year'); |
| 1769 | $month = $request->input('month'); |
| 1770 | $rows = $request->input('rows', []); |
| 1771 | |
| 1772 | DB::transaction(function () use ($company, $weekDate, $year, $month, $rows) { |
| 1773 | foreach ($rows as $row) { |
| 1774 | // FIRE-1027: skip rows the user has manually edited |
| 1775 | // (source='manual_override') or that came from the Drive |
| 1776 | // weekly Excel (source='google_drive') — same guard as |
| 1777 | // load_resumen above. |
| 1778 | $existing = TblFinanceReportSemanal::where([ |
| 1779 | 'company_id' => $company->company_id, |
| 1780 | 'sede_id' => $row['sede_id'], |
| 1781 | 'week_date' => $weekDate, |
| 1782 | ])->first(); |
| 1783 | if ($existing && in_array($existing->source, ['google_drive', 'manual_override'], true)) { |
| 1784 | continue; |
| 1785 | } |
| 1786 | |
| 1787 | $actuals = $row['actuals']; |
| 1788 | $n1 = $row['actuals_n1'] ?? null; |
| 1789 | $budget = $row['budget'] ?? null; |
| 1790 | $prevision = $row['prevision'] ?? null; |
| 1791 | |
| 1792 | TblFinanceReportSemanal::updateOrCreate( |
| 1793 | [ |
| 1794 | 'company_id' => $company->company_id, |
| 1795 | 'sede_id' => $row['sede_id'], |
| 1796 | 'week_date' => $weekDate, |
| 1797 | ], |
| 1798 | [ |
| 1799 | 'year' => $year, |
| 1800 | 'month' => $month, |
| 1801 | 'actuals' => $actuals, |
| 1802 | 'actuals_n1' => $n1, |
| 1803 | 'budget' => $budget, |
| 1804 | 'prevision' => $prevision, |
| 1805 | 'deviation_vs_n1' => ($n1 !== null) ? ($actuals - $n1) : null, |
| 1806 | 'deviation_pct_vs_n1' => ($n1 && $n1 != 0) ? (($actuals - $n1) / $n1) : null, |
| 1807 | 'deviation_vs_budget' => ($budget !== null) ? ($actuals - $budget) : null, |
| 1808 | 'deviation_pct_vs_budget' => ($budget && $budget != 0) ? (($actuals - $budget) / $budget) : null, |
| 1809 | 'deviation_vs_prevision' => ($prevision !== null) ? ($actuals - $prevision) : null, |
| 1810 | 'deviation_pct_vs_prevision' => ($prevision && $prevision != 0) ? (($actuals - $prevision) / $prevision) : null, |
| 1811 | 'source' => 'zenital', |
| 1812 | 'loaded_at' => now(), |
| 1813 | ] |
| 1814 | ); |
| 1815 | } |
| 1816 | }); |
| 1817 | |
| 1818 | return response(['message' => 'OK']); |
| 1819 | } catch (\Exception $e) { |
| 1820 | /** @disregard P1014 */ |
| 1821 | $e->exceptionCode = 'LOAD_REPORT_SEMANAL_EXCEPTION'; |
| 1822 | report($e); |
| 1823 | |
| 1824 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 1825 | } |
| 1826 | } |
| 1827 | |
| 1828 | // ------------------------------------------------------- |
| 1829 | // EDICIÓN CELDA-A-CELDA (FIRE-1027) |
| 1830 | // ------------------------------------------------------- |
| 1831 | |
| 1832 | /** |
| 1833 | * POST /finance/resumen/upsert-cell |
| 1834 | * |
| 1835 | * FIRE-1027: per-cell write for sedes whose data flows from the manual |
| 1836 | * Excel rather than Zenital. Berta needs to be able to type the N-1 / |
| 1837 | * N-2 numbers directly for those sedes. Refuses to clobber a Zenital |
| 1838 | * row — that data is owned by the DWH and should never be edited from |
| 1839 | * the UI. |
| 1840 | * |
| 1841 | * Body: { sede_id, year, month, field, value } |
| 1842 | * field ∈ {actuals, actuals_n1, actuals_n2, budget} |
| 1843 | * value: number | null (null clears the cell) |
| 1844 | */ |
| 1845 | public function upsert_resumen_cell(Request $request) |
| 1846 | { |
| 1847 | try { |
| 1848 | $data = $request->validate([ |
| 1849 | 'sede_id' => 'required|integer', |
| 1850 | 'year' => 'required|integer', |
| 1851 | 'month' => 'required|integer|between:1,12', |
| 1852 | 'field' => 'required|in:actuals,actuals_n1,actuals_n2,budget', |
| 1853 | 'value' => 'nullable|numeric', |
| 1854 | ]); |
| 1855 | |
| 1856 | // FIRE-1027: resolve company from the sede directly. We can't use |
| 1857 | // `getCompany($request)` because the user often has "Todas las |
| 1858 | // regiones" selected (Region header = "All"), and Drive-imported |
| 1859 | // sedes don't even live in the currently-selected region — every |
| 1860 | // sede already knows its own company_id. |
| 1861 | $sede = TblFinanceSedes::find($data['sede_id']); |
| 1862 | if (! $sede) { |
| 1863 | return response(['message' => 'KO', 'error' => 'sede_not_found', 'sede_id' => $data['sede_id']], 404); |
| 1864 | } |
| 1865 | |
| 1866 | // FIRE-1027: hard-refuse writes to Zenital-mapped sedes. Their |
| 1867 | // data is synthesised from the DWH at list time so a row in |
| 1868 | // tbl_finance_resumen_anual is never read for them — but we |
| 1869 | // shouldn't allow a stray manual_override row to accumulate |
| 1870 | // either. The frontend should never send these, but enforce |
| 1871 | // server-side too. |
| 1872 | if ($this->isZenitalSede($data['sede_id'])) { |
| 1873 | return response(['message' => 'KO', 'error' => 'cannot_edit_zenital_row'], 403); |
| 1874 | } |
| 1875 | |
| 1876 | $row = TblFinanceResumenAnual::firstOrNew([ |
| 1877 | 'company_id' => $sede->company_id, |
| 1878 | 'sede_id' => $data['sede_id'], |
| 1879 | 'year' => $data['year'], |
| 1880 | 'month' => $data['month'], |
| 1881 | ]); |
| 1882 | |
| 1883 | $row->{$data['field']} = $data['value']; |
| 1884 | $row->source = 'manual_override'; |
| 1885 | $row->loaded_at = now(); |
| 1886 | |
| 1887 | // Recompute deviations against the (possibly newly set) values. |
| 1888 | $actuals = $row->actuals; |
| 1889 | $n1 = $row->actuals_n1; |
| 1890 | $n2 = $row->actuals_n2; |
| 1891 | $budget = $row->budget; |
| 1892 | $row->deviation_vs_n1 = ($n1 && $n1 != 0 && $actuals !== null) ? (($actuals - $n1) / $n1) : null; |
| 1893 | $row->deviation_vs_n2 = ($n2 && $n2 != 0 && $actuals !== null) ? (($actuals - $n2) / $n2) : null; |
| 1894 | $row->deviation_vs_budget = ($budget && $budget != 0 && $actuals !== null) ? (($actuals - $budget) / $budget) : null; |
| 1895 | |
| 1896 | $row->save(); |
| 1897 | |
| 1898 | return response(['message' => 'OK', 'data' => $row]); |
| 1899 | } catch (\Exception $e) { |
| 1900 | /** @disregard P1014 */ |
| 1901 | $e->exceptionCode = 'UPSERT_RESUMEN_CELL_EXCEPTION'; |
| 1902 | report($e); |
| 1903 | |
| 1904 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 1905 | } |
| 1906 | } |
| 1907 | |
| 1908 | /** |
| 1909 | * POST /finance/report-semanal/upsert-cell |
| 1910 | * |
| 1911 | * FIRE-1027: per-cell write for the weekly report. Same shape as |
| 1912 | * upsert_resumen_cell but for tbl_finance_report_semanal, which is |
| 1913 | * keyed by (company_id, sede_id, week_date). |
| 1914 | * |
| 1915 | * The Drive importer stores one row per week; this endpoint edits |
| 1916 | * whichever week the caller specifies. If `week_date` isn't supplied, |
| 1917 | * it falls back to the most recent week_date for that sede in the |
| 1918 | * given year/month — which matches what list_report_semanal renders. |
| 1919 | * |
| 1920 | * Body: { sede_id, year, month, week_date?, field, value } |
| 1921 | * field ∈ {actuals, actuals_n1, budget, prevision} |
| 1922 | */ |
| 1923 | public function upsert_report_semanal_cell(Request $request) |
| 1924 | { |
| 1925 | try { |
| 1926 | $data = $request->validate([ |
| 1927 | 'sede_id' => 'required|integer', |
| 1928 | 'year' => 'required|integer', |
| 1929 | 'month' => 'required|integer|between:1,12', |
| 1930 | 'week_date' => 'nullable|date', |
| 1931 | 'field' => 'required|in:actuals,actuals_n1,budget,prevision', |
| 1932 | 'value' => 'nullable|numeric', |
| 1933 | ]); |
| 1934 | |
| 1935 | // FIRE-1027: resolve company from the sede (not the Region |
| 1936 | // header) — same reason as upsert_resumen_cell. |
| 1937 | $sede = TblFinanceSedes::find($data['sede_id']); |
| 1938 | if (! $sede) { |
| 1939 | return response(['message' => 'KO', 'error' => 'sede_not_found', 'sede_id' => $data['sede_id']], 404); |
| 1940 | } |
| 1941 | |
| 1942 | // FIRE-1027: same hard guard as upsert_resumen_cell. |
| 1943 | if ($this->isZenitalSede($data['sede_id'])) { |
| 1944 | return response(['message' => 'KO', 'error' => 'cannot_edit_zenital_row'], 403); |
| 1945 | } |
| 1946 | |
| 1947 | // Resolve week_date if the caller didn't supply one. The list |
| 1948 | // view aggregates SUM(actuals) per (sede, year, month) and tags |
| 1949 | // the response with the latest week_date — match that. |
| 1950 | $weekDate = $data['week_date'] ?? null; |
| 1951 | if (! $weekDate) { |
| 1952 | $weekDate = TblFinanceReportSemanal::where([ |
| 1953 | 'company_id' => $sede->company_id, |
| 1954 | 'sede_id' => $data['sede_id'], |
| 1955 | 'year' => $data['year'], |
| 1956 | 'month' => $data['month'], |
| 1957 | ]) |
| 1958 | ->orderByDesc('week_date') |
| 1959 | ->value('week_date'); |
| 1960 | } |
| 1961 | if (! $weekDate) { |
| 1962 | // No row for this sede/month yet — anchor to the first day |
| 1963 | // of the month so the upsert has a deterministic key. |
| 1964 | $weekDate = sprintf('%04d-%02d-01', $data['year'], $data['month']); |
| 1965 | } |
| 1966 | |
| 1967 | $row = TblFinanceReportSemanal::firstOrNew([ |
| 1968 | 'company_id' => $sede->company_id, |
| 1969 | 'sede_id' => $data['sede_id'], |
| 1970 | 'week_date' => $weekDate, |
| 1971 | ]); |
| 1972 | |
| 1973 | if ($row->exists && $row->source === 'zenital') { |
| 1974 | return response(['message' => 'KO', 'error' => 'cannot_edit_zenital_row'], 403); |
| 1975 | } |
| 1976 | |
| 1977 | $row->year = $data['year']; |
| 1978 | $row->month = $data['month']; |
| 1979 | $row->{$data['field']} = $data['value']; |
| 1980 | $row->source = 'manual_override'; |
| 1981 | $row->loaded_at = now(); |
| 1982 | |
| 1983 | $actuals = $row->actuals; |
| 1984 | $n1 = $row->actuals_n1; |
| 1985 | $budget = $row->budget; |
| 1986 | $prevision = $row->prevision; |
| 1987 | $row->deviation_vs_n1 = ($n1 !== null && $actuals !== null) ? ($actuals - $n1) : null; |
| 1988 | $row->deviation_pct_vs_n1 = ($n1 && $n1 != 0 && $actuals !== null) ? (($actuals - $n1) / $n1) : null; |
| 1989 | $row->deviation_vs_budget = ($budget !== null && $actuals !== null) ? ($actuals - $budget) : null; |
| 1990 | $row->deviation_pct_vs_budget = ($budget && $budget != 0 && $actuals !== null) ? (($actuals - $budget) / $budget) : null; |
| 1991 | $row->deviation_vs_prevision = ($prevision !== null && $actuals !== null) ? ($actuals - $prevision) : null; |
| 1992 | $row->deviation_pct_vs_prevision = ($prevision && $prevision != 0 && $actuals !== null) ? (($actuals - $prevision) / $prevision) : null; |
| 1993 | |
| 1994 | $row->save(); |
| 1995 | |
| 1996 | return response(['message' => 'OK', 'data' => $row]); |
| 1997 | } catch (\Exception $e) { |
| 1998 | /** @disregard P1014 */ |
| 1999 | $e->exceptionCode = 'UPSERT_REPORT_SEMANAL_CELL_EXCEPTION'; |
| 2000 | report($e); |
| 2001 | |
| 2002 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 2003 | } |
| 2004 | } |
| 2005 | |
| 2006 | // ------------------------------------------------------- |
| 2007 | // DESTINATARIOS REPORTE SEMANAL |
| 2008 | // ------------------------------------------------------- |
| 2009 | |
| 2010 | public function list_recipients(Request $request) |
| 2011 | { |
| 2012 | try { |
| 2013 | $companyIds = $this->getCompanyIds($request); |
| 2014 | // "Todas las regiones" recipients are stored with company_id = NULL |
| 2015 | // and must appear in every region's listing — otherwise adding one |
| 2016 | // looks like the save failed silently (it actually saved but the |
| 2017 | // reload couldn't find it). |
| 2018 | $data = TblFinanceReportRecipients::where(function ($q) use ($companyIds) { |
| 2019 | $q->whereIn('company_id', $companyIds) |
| 2020 | ->orWhereNull('company_id'); |
| 2021 | }) |
| 2022 | ->orderBy('name') |
| 2023 | ->get(); |
| 2024 | |
| 2025 | return response(['message' => 'OK', 'data' => $data]); |
| 2026 | } catch (\Exception $e) { |
| 2027 | /** @disregard P1014 */ |
| 2028 | $e->exceptionCode = 'LIST_RECIPIENTS_EXCEPTION'; |
| 2029 | report($e); |
| 2030 | |
| 2031 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 2032 | } |
| 2033 | } |
| 2034 | |
| 2035 | public function create_recipient(Request $request) |
| 2036 | { |
| 2037 | try { |
| 2038 | $data = $request->all(); |
| 2039 | |
| 2040 | // company_id comes from the modal's region selector. 'all' = NULL (todas |
| 2041 | // las regiones). The old implementation forced the header region, which |
| 2042 | // broke when the user was viewing "Todas las regiones" or picked a |
| 2043 | // different region in the modal. |
| 2044 | $companyId = $data['company_id'] ?? null; |
| 2045 | if ($companyId === 'all' || $companyId === '') { |
| 2046 | $companyId = null; |
| 2047 | } |
| 2048 | |
| 2049 | $recipient = TblFinanceReportRecipients::create([ |
| 2050 | 'company_id' => $companyId, |
| 2051 | 'name' => $data['name'], |
| 2052 | 'email' => $data['email'], |
| 2053 | 'is_active' => $data['is_active'] ?? 1, |
| 2054 | 'days_before_delete_errors' => $data['days_before_delete_errors'] ?? 7, |
| 2055 | ]); |
| 2056 | |
| 2057 | return response(['message' => 'OK', 'data' => $recipient]); |
| 2058 | } catch (\Exception $e) { |
| 2059 | /** @disregard P1014 */ |
| 2060 | $e->exceptionCode = 'CREATE_RECIPIENT_EXCEPTION'; |
| 2061 | report($e); |
| 2062 | |
| 2063 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 2064 | } |
| 2065 | } |
| 2066 | |
| 2067 | public function update_recipient(Request $request, $id) |
| 2068 | { |
| 2069 | try { |
| 2070 | $data = $request->all(); |
| 2071 | |
| 2072 | if (array_key_exists('company_id', $data) |
| 2073 | && ($data['company_id'] === 'all' || $data['company_id'] === '')) { |
| 2074 | $data['company_id'] = null; |
| 2075 | } |
| 2076 | |
| 2077 | // Whitelist updatable fields so unrelated payload keys (e.g. zone) |
| 2078 | // don't leak into the update. |
| 2079 | $update = array_intersect_key($data, array_flip([ |
| 2080 | 'company_id', 'name', 'email', 'is_active', 'days_before_delete_errors', |
| 2081 | ])); |
| 2082 | |
| 2083 | TblFinanceReportRecipients::where('id', $id)->update($update); |
| 2084 | |
| 2085 | return response(['message' => 'OK']); |
| 2086 | } catch (\Exception $e) { |
| 2087 | /** @disregard P1014 */ |
| 2088 | $e->exceptionCode = 'UPDATE_RECIPIENT_EXCEPTION'; |
| 2089 | report($e); |
| 2090 | |
| 2091 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 2092 | } |
| 2093 | } |
| 2094 | |
| 2095 | public function delete_recipient($id) |
| 2096 | { |
| 2097 | try { |
| 2098 | TblFinanceReportRecipients::where('id', $id)->delete(); |
| 2099 | |
| 2100 | return response(['message' => 'OK']); |
| 2101 | } catch (\Exception $e) { |
| 2102 | /** @disregard P1014 */ |
| 2103 | $e->exceptionCode = 'DELETE_RECIPIENT_EXCEPTION'; |
| 2104 | report($e); |
| 2105 | |
| 2106 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 2107 | } |
| 2108 | } |
| 2109 | |
| 2110 | public function send_report(Request $request) |
| 2111 | { |
| 2112 | try { |
| 2113 | $params = []; |
| 2114 | if ($request->input('month')) { |
| 2115 | $params['--month'] = (int) $request->input('month'); |
| 2116 | } |
| 2117 | if ($request->input('type')) { |
| 2118 | $params['--type'] = $request->input('type'); |
| 2119 | } |
| 2120 | $exitCode = Artisan::call('finance:send-report', $params); |
| 2121 | |
| 2122 | return response([ |
| 2123 | 'message' => 'OK', |
| 2124 | 'output' => Artisan::output(), |
| 2125 | 'exit_code' => $exitCode, |
| 2126 | ]); |
| 2127 | } catch (\Exception $e) { |
| 2128 | /** @disregard P1014 */ |
| 2129 | $e->exceptionCode = 'SEND_FINANCE_REPORT_EXCEPTION'; |
| 2130 | report($e); |
| 2131 | |
| 2132 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 2133 | } |
| 2134 | } |
| 2135 | |
| 2136 | public function test_report(Request $request) |
| 2137 | { |
| 2138 | try { |
| 2139 | // FIRE-1148: Vista previa used to call Artisan::call('finance:send-report' |
| 2140 | // --test) and scrape Artisan::output() — that ran the FULL pipeline |
| 2141 | // (every company, every recipient) just to surface the aggregated HTML, |
| 2142 | // taking tens of seconds and racing on the per-worker stdout buffer. |
| 2143 | // Now we call the renderer service directly and return the HTML. |
| 2144 | // |
| 2145 | // Response shape preserved: the frontend regex-extracts |
| 2146 | // /<div[\s\S]*<\/div>/ from `output` (see finance.component.ts:1302), |
| 2147 | // so we keep the `output` key populated with the HTML string. |
| 2148 | $month = $request->query('month') ? (int) $request->query('month') : null; |
| 2149 | $type = (string) ($request->query('type') ?: 'weekly'); |
| 2150 | |
| 2151 | $html = $this->renderer->renderPreview($month, $type); |
| 2152 | |
| 2153 | return response([ |
| 2154 | 'message' => 'OK', |
| 2155 | 'output' => $html, |
| 2156 | 'exit_code' => 0, |
| 2157 | ]); |
| 2158 | } catch (\Exception $e) { |
| 2159 | /** @disregard P1014 */ |
| 2160 | $e->exceptionCode = 'TEST_FINANCE_REPORT_EXCEPTION'; |
| 2161 | report($e); |
| 2162 | |
| 2163 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 2164 | } |
| 2165 | } |
| 2166 | |
| 2167 | // ------------------------------------------------------- |
| 2168 | // GOOGLE DRIVE IMPORT |
| 2169 | // ------------------------------------------------------- |
| 2170 | |
| 2171 | public function import_from_drive() |
| 2172 | { |
| 2173 | try { |
| 2174 | // The import takes ~3 minutes (Google Drive downloads + PhpSpreadsheet parsing). |
| 2175 | // Running synchronously through Cloudflare hits the 100s proxy timeout (504). |
| 2176 | // Fire-and-forget: spawn the artisan command as a detached process so the |
| 2177 | // HTTP request returns immediately. Progress lands in storage/logs/third-party/*.log. |
| 2178 | $cmd = sprintf( |
| 2179 | '%s %s finance:import-drive > /dev/null 2>&1 &', |
| 2180 | escapeshellarg(PHP_BINARY), |
| 2181 | escapeshellarg(base_path('artisan')) |
| 2182 | ); |
| 2183 | exec($cmd); |
| 2184 | |
| 2185 | return response([ |
| 2186 | 'message' => 'OK', |
| 2187 | 'status' => 'started', |
| 2188 | 'note' => 'Import running in background. Check storage/logs/third-party/ for completion.', |
| 2189 | ]); |
| 2190 | } catch (\Exception $e) { |
| 2191 | $e->exceptionCode = 'IMPORT_FINANCE_DRIVE_EXCEPTION'; |
| 2192 | report($e); |
| 2193 | |
| 2194 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 2195 | } |
| 2196 | } |
| 2197 | |
| 2198 | // ------------------------------------------------------- |
| 2199 | // SEDES CRUD |
| 2200 | // ------------------------------------------------------- |
| 2201 | |
| 2202 | public function create_sede(Request $request) |
| 2203 | { |
| 2204 | try { |
| 2205 | $data = $request->all(); |
| 2206 | |
| 2207 | $sede = TblFinanceSedes::create([ |
| 2208 | 'name' => $data['name'], |
| 2209 | 'company_id' => $data['company_id'], |
| 2210 | 'region_id' => $data['region_id'], |
| 2211 | 'code' => $data['code'] ?? null, |
| 2212 | 'is_active' => 1, |
| 2213 | ]); |
| 2214 | |
| 2215 | return response(['message' => 'OK', 'data' => $sede]); |
| 2216 | } catch (\Exception $e) { |
| 2217 | /** @disregard P1014 */ |
| 2218 | $e->exceptionCode = 'CREATE_SEDE_EXCEPTION'; |
| 2219 | report($e); |
| 2220 | |
| 2221 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 2222 | } |
| 2223 | } |
| 2224 | |
| 2225 | public function delete_sede(Request $request, $id) |
| 2226 | { |
| 2227 | try { |
| 2228 | $sede = TblFinanceSedes::findOrFail($id); |
| 2229 | $sede->update(['is_active' => 0]); |
| 2230 | |
| 2231 | return response(['message' => 'OK']); |
| 2232 | } catch (\Exception $e) { |
| 2233 | /** @disregard P1014 */ |
| 2234 | $e->exceptionCode = 'DELETE_SEDE_EXCEPTION'; |
| 2235 | report($e); |
| 2236 | |
| 2237 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 2238 | } |
| 2239 | } |
| 2240 | |
| 2241 | // ------------------------------------------------------- |
| 2242 | // Month Config (billing close dates) |
| 2243 | // ------------------------------------------------------- |
| 2244 | |
| 2245 | /** |
| 2246 | * GET /finance-month-config?year=2026 |
| 2247 | * Returns all month config rows for the given year. |
| 2248 | */ |
| 2249 | public function list_month_config(Request $request) |
| 2250 | { |
| 2251 | try { |
| 2252 | $year = (int) $request->query('year', date('Y')); |
| 2253 | |
| 2254 | $data = TblFinanceMonthConfig::where('year', $year) |
| 2255 | ->orderBy('month') |
| 2256 | ->get(); |
| 2257 | |
| 2258 | return response(['message' => 'OK', 'data' => $data]); |
| 2259 | } catch (\Exception $e) { |
| 2260 | /** @disregard P1014 */ |
| 2261 | $e->exceptionCode = 'LIST_MONTH_CONFIG_EXCEPTION'; |
| 2262 | report($e); |
| 2263 | |
| 2264 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 2265 | } |
| 2266 | } |
| 2267 | |
| 2268 | /** |
| 2269 | * PUT /finance-month-config/{id} |
| 2270 | * Updates close_date and/or force_report_month for a row. |
| 2271 | */ |
| 2272 | public function update_month_config(Request $request, $id) |
| 2273 | { |
| 2274 | try { |
| 2275 | $config = TblFinanceMonthConfig::findOrFail($id); |
| 2276 | |
| 2277 | $config->update($request->only(['close_date', 'force_report_month'])); |
| 2278 | |
| 2279 | return response(['message' => 'OK', 'data' => $config->fresh()]); |
| 2280 | } catch (\Exception $e) { |
| 2281 | /** @disregard P1014 */ |
| 2282 | $e->exceptionCode = 'UPDATE_MONTH_CONFIG_EXCEPTION'; |
| 2283 | report($e); |
| 2284 | |
| 2285 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 2286 | } |
| 2287 | } |
| 2288 | |
| 2289 | /** |
| 2290 | * FIRE-1293 — list of region names the current user is allowed to see, |
| 2291 | * derived from their `tbl_company_users` memberships joined to |
| 2292 | * `tbl_companies.region`. Used to scope the payment-reminder KPI so a |
| 2293 | * Cataluña operator never sees Madrid data. |
| 2294 | * |
| 2295 | * @return array<int,string> |
| 2296 | */ |
| 2297 | private function getAccessibleRegions(Request $request): array |
| 2298 | { |
| 2299 | $companyIds = $this->getCompanyIds($request); |
| 2300 | |
| 2301 | if (empty($companyIds)) { |
| 2302 | return []; |
| 2303 | } |
| 2304 | |
| 2305 | return TblCompanies::whereIn('company_id', $companyIds) |
| 2306 | ->whereNotNull('region') |
| 2307 | ->pluck('region') |
| 2308 | ->unique() |
| 2309 | ->values() |
| 2310 | ->toArray(); |
| 2311 | } |
| 2312 | |
| 2313 | /** |
| 2314 | * FIRE-1293 — GET /finance-payment-reminders-kpi |
| 2315 | * |
| 2316 | * Aggregates the payment-reminder pipeline's conversion rate. For every |
| 2317 | * distinct (invoice_number, region) reminded within the window, picks |
| 2318 | * the earliest `send_datetime` as the attribution anchor, then bucketing |
| 2319 | * the invoice as: |
| 2320 | * - cobros_exitosos: paid within `window_days` of first reminder |
| 2321 | * - pagos_fuera_ventana: paid, but later than the window (excluded |
| 2322 | * from the success count per Berta's rule) |
| 2323 | * - pendientes: still unpaid as of the last G3W poll |
| 2324 | * |
| 2325 | * Always scoped to the regions the user can access (Region header + |
| 2326 | * User-ID → tbl_company_users). The `region` query param can further |
| 2327 | * narrow this down but cannot widen it: a Cataluña user requesting |
| 2328 | * `region=Madrid` gets an empty result, not Madrid data. |
| 2329 | * |
| 2330 | * Response also includes: |
| 2331 | * - `daily`: one row per (day, region) shaped like |
| 2332 | * `docs/info/invoice_reminders_report_v3_by_day.csv` — sent, |
| 2333 | * not_sent (from skip telemetry), would_send, gap_pct. |
| 2334 | * - `accessible_regions`: drives the frontend's region dropdown |
| 2335 | * so it only lists regions the user can pick. |
| 2336 | * |
| 2337 | * Data depends on `invoices:sync-payment-dates` having populated |
| 2338 | * `tbl_invoice_reminders.collection_date` from G3W. Until that cron |
| 2339 | * has its first successful run, every row will read as `pendiente`. |
| 2340 | * |
| 2341 | * Query params (all optional): |
| 2342 | * - from YYYY-MM-DD, default = first day of current month |
| 2343 | * - to YYYY-MM-DD, default = today |
| 2344 | * - region filter; "All"/empty = every accessible region |
| 2345 | * - window_days override the 20-day rule (1..90) |
| 2346 | */ |
| 2347 | public function payment_reminders_kpi(Request $request) |
| 2348 | { |
| 2349 | try { |
| 2350 | $from = $request->query('from') ?: Carbon::now()->startOfMonth()->toDateString(); |
| 2351 | $to = $request->query('to') ?: Carbon::now()->toDateString(); |
| 2352 | $regionFilter = $request->query('region'); |
| 2353 | $windowDays = (int) ($request->query('window_days') ?: 20); |
| 2354 | |
| 2355 | if ($windowDays < 1 || $windowDays > 90) { |
| 2356 | return response([ |
| 2357 | 'message' => 'KO', |
| 2358 | 'error' => 'window_days must be between 1 and 90', |
| 2359 | ], 422); |
| 2360 | } |
| 2361 | |
| 2362 | $accessibleRegions = $this->getAccessibleRegions($request); |
| 2363 | |
| 2364 | // Effective filter list: intersect requested region with what the |
| 2365 | // user is allowed to see. Empty list short-circuits to empty data |
| 2366 | // (which is correct behaviour: requested-but-not-allowed = nothing). |
| 2367 | $regionFilterList = $accessibleRegions; |
| 2368 | if ($regionFilter && $regionFilter !== 'All') { |
| 2369 | $regionFilterList = in_array($regionFilter, $accessibleRegions, true) |
| 2370 | ? [$regionFilter] |
| 2371 | : []; |
| 2372 | } |
| 2373 | |
| 2374 | // FIRE-1293 (Berta feedback 2, 2026-07): a sendgrid_send_failed |
| 2375 | // skip row IS a sent attempt — pre-deploy those only existed in |
| 2376 | // the skips table (the reminder row was never created). Union |
| 2377 | // them in so the dashboard shows historical failures as sent. |
| 2378 | // |
| 2379 | // Post-deploy every failed attempt lives in BOTH tables: the |
| 2380 | // reminders row (created before SendGrid) + the skip row (recorded |
| 2381 | // after the failure). The NOT EXISTS clause dedupes those so the |
| 2382 | // union only adds rows for historical failures. |
| 2383 | $regionsForBinding = $regionFilterList ?: ['__none__']; |
| 2384 | $buildSentUnion = static function () use ($from, $to, $regionsForBinding) { |
| 2385 | return DB::table('tbl_invoice_reminders') |
| 2386 | ->selectRaw('region, invoice_number, send_datetime, collection_date') |
| 2387 | ->whereBetween('send_datetime', [$from.' 00:00:00', $to.' 23:59:59']) |
| 2388 | ->whereNotNull('invoice_number') |
| 2389 | ->whereIn('region', $regionsForBinding) |
| 2390 | ->unionAll( |
| 2391 | DB::table('tbl_invoice_reminder_skips') |
| 2392 | ->selectRaw('region, invoice_number, created_at AS send_datetime, NULL AS collection_date') |
| 2393 | ->where('skip_reason', 'sendgrid_send_failed') |
| 2394 | ->whereBetween('created_at', [$from.' 00:00:00', $to.' 23:59:59']) |
| 2395 | ->whereNotNull('invoice_number') |
| 2396 | ->whereIn('region', $regionsForBinding) |
| 2397 | ->whereNotExists(function ($q) use ($from, $to) { |
| 2398 | $q->select(DB::raw(1)) |
| 2399 | ->from('tbl_invoice_reminders as r') |
| 2400 | ->whereColumn('r.invoice_number', 'tbl_invoice_reminder_skips.invoice_number') |
| 2401 | ->whereColumn('r.region', 'tbl_invoice_reminder_skips.region') |
| 2402 | ->whereBetween('r.send_datetime', [$from.' 00:00:00', $to.' 23:59:59']); |
| 2403 | }) |
| 2404 | ); |
| 2405 | }; |
| 2406 | |
| 2407 | // Per-invoice aggregation: one row per (invoice_number, region) |
| 2408 | // with the earliest send_datetime and the (single) collection_date. |
| 2409 | $perInvoice = DB::query() |
| 2410 | ->fromSub($buildSentUnion(), 'u') |
| 2411 | ->selectRaw('region, invoice_number, |
| 2412 | MIN(send_datetime) AS first_send, |
| 2413 | MAX(collection_date) AS collection_date, |
| 2414 | COUNT(*) AS reminder_count') |
| 2415 | ->groupBy('region', 'invoice_number'); |
| 2416 | |
| 2417 | // Region-level rollup. We do the outer aggregation in SQL too so |
| 2418 | // the API stays cheap even when production has tens of thousands |
| 2419 | // of invoices in the window. |
| 2420 | // |
| 2421 | // Two parallel metric families are exposed: |
| 2422 | // • per-factura (reminders_sent / cobros_exitosos / tasa_exito_pct) |
| 2423 | // — dedupes by (region, invoice_number). Historical primary metric. |
| 2424 | // • per-envío (reminders_sent_total / cobros_exitosos_por_envio / |
| 2425 | // tasa_exito_pct_por_envio) — counts every reminder row. Matches |
| 2426 | // Victor's "Panel de Impacto IA" so the two dashboards can be |
| 2427 | // read side by side. See docs/info/KPI_recordatorios_diff_titan_vs_panel_ia.md |
| 2428 | // for the semantic diff. |
| 2429 | $rows = DB::query() |
| 2430 | ->fromSub($perInvoice, 'inv') |
| 2431 | ->selectRaw(' |
| 2432 | region, |
| 2433 | COUNT(*) AS reminders_sent, |
| 2434 | SUM(CASE |
| 2435 | WHEN collection_date IS NOT NULL |
| 2436 | AND DATEDIFF(collection_date, DATE(first_send)) <= ? |
| 2437 | THEN 1 ELSE 0 |
| 2438 | END) AS cobros_exitosos, |
| 2439 | SUM(CASE |
| 2440 | WHEN collection_date IS NOT NULL |
| 2441 | AND DATEDIFF(collection_date, DATE(first_send)) > ? |
| 2442 | THEN 1 ELSE 0 |
| 2443 | END) AS pagos_fuera_ventana, |
| 2444 | SUM(CASE WHEN collection_date IS NULL THEN 1 ELSE 0 END) AS pendientes, |
| 2445 | SUM(reminder_count) AS reminders_sent_total, |
| 2446 | SUM(CASE |
| 2447 | WHEN collection_date IS NOT NULL |
| 2448 | AND DATEDIFF(collection_date, DATE(first_send)) <= ? |
| 2449 | THEN reminder_count ELSE 0 |
| 2450 | END) AS cobros_exitosos_por_envio |
| 2451 | ', [$windowDays, $windowDays, $windowDays]) |
| 2452 | ->groupBy('region') |
| 2453 | ->orderBy('region') |
| 2454 | ->get(); |
| 2455 | |
| 2456 | $regions = []; |
| 2457 | $totals = [ |
| 2458 | 'reminders_sent' => 0, |
| 2459 | 'cobros_exitosos' => 0, |
| 2460 | 'pagos_fuera_ventana' => 0, |
| 2461 | 'pendientes' => 0, |
| 2462 | 'reminders_sent_total' => 0, |
| 2463 | 'cobros_exitosos_por_envio' => 0, |
| 2464 | ]; |
| 2465 | |
| 2466 | foreach ($rows as $r) { |
| 2467 | $remindersSent = (int) $r->reminders_sent; |
| 2468 | $cobrosExitosos = (int) $r->cobros_exitosos; |
| 2469 | $remindersSentTotal = (int) $r->reminders_sent_total; |
| 2470 | $cobrosExitososPorEnvio = (int) $r->cobros_exitosos_por_envio; |
| 2471 | |
| 2472 | $regions[] = [ |
| 2473 | 'region' => $r->region, |
| 2474 | 'reminders_sent' => $remindersSent, |
| 2475 | 'cobros_exitosos' => $cobrosExitosos, |
| 2476 | 'pagos_fuera_ventana' => (int) $r->pagos_fuera_ventana, |
| 2477 | 'pendientes' => (int) $r->pendientes, |
| 2478 | 'reminders_sent_total' => $remindersSentTotal, |
| 2479 | 'cobros_exitosos_por_envio' => $cobrosExitososPorEnvio, |
| 2480 | 'tasa_exito_pct' => $remindersSent > 0 |
| 2481 | ? round($cobrosExitosos * 100 / $remindersSent, 2) |
| 2482 | : 0.0, |
| 2483 | 'tasa_exito_pct_por_envio' => $remindersSentTotal > 0 |
| 2484 | ? round($cobrosExitososPorEnvio * 100 / $remindersSentTotal, 2) |
| 2485 | : 0.0, |
| 2486 | ]; |
| 2487 | |
| 2488 | $totals['reminders_sent'] += $remindersSent; |
| 2489 | $totals['cobros_exitosos'] += $cobrosExitosos; |
| 2490 | $totals['pagos_fuera_ventana'] += (int) $r->pagos_fuera_ventana; |
| 2491 | $totals['pendientes'] += (int) $r->pendientes; |
| 2492 | $totals['reminders_sent_total'] += $remindersSentTotal; |
| 2493 | $totals['cobros_exitosos_por_envio'] += $cobrosExitososPorEnvio; |
| 2494 | } |
| 2495 | |
| 2496 | $totals['tasa_exito_pct'] = $totals['reminders_sent'] > 0 |
| 2497 | ? round($totals['cobros_exitosos'] * 100 / $totals['reminders_sent'], 2) |
| 2498 | : 0.0; |
| 2499 | $totals['tasa_exito_pct_por_envio'] = $totals['reminders_sent_total'] > 0 |
| 2500 | ? round($totals['cobros_exitosos_por_envio'] * 100 / $totals['reminders_sent_total'], 2) |
| 2501 | : 0.0; |
| 2502 | |
| 2503 | // FIRE-1293 (Berta, 2026-07): whitelist of skip reasons that |
| 2504 | // qualify as "filters" — i.e. invoices we could have chased but |
| 2505 | // didn't because of one of these business rules. This drives |
| 2506 | // BOTH the "Recordatorios no enviados, por motivo" panel AND |
| 2507 | // the `not_sent` count of the daily breakdown, so: |
| 2508 | // would_send = sent + count(skips WHERE reason IN this list) |
| 2509 | // |
| 2510 | // Deliberately excluded from the panel and from `not_sent`: |
| 2511 | // • sendgrid_send_failed / partial_invalid_emails — counted |
| 2512 | // as sent, so they cannot also be "no enviados". |
| 2513 | // • already_paid — the invoice was already paid, so it is |
| 2514 | // not a "potential recordatorio" at all — it disappears |
| 2515 | // from the panel and from `would_send` entirely. |
| 2516 | $filterReasons = [ |
| 2517 | 'payment_method_not_transfer', |
| 2518 | 'freshdesk_task_active', |
| 2519 | 'in_exceptions_table', |
| 2520 | 'region_filter_cv_m_prefix', |
| 2521 | 'region_filter_and_c_prefix', |
| 2522 | 'g3w_factura_missing', |
| 2523 | 'missing_mandatory_field', |
| 2524 | 'region_inactive', |
| 2525 | 'no_valid_email', |
| 2526 | ]; |
| 2527 | |
| 2528 | // Skip telemetry — populated by FacturasService::recordSkip() at |
| 2529 | // every continue; site in the reminder loops. Whitelisted to the |
| 2530 | // 8 filter reasons above so the "no enviados por motivo" panel |
| 2531 | // matches Berta's definition exactly. |
| 2532 | $skipRows = DB::table('tbl_invoice_reminder_skips') |
| 2533 | ->selectRaw('region, skip_reason, COUNT(*) AS skip_count') |
| 2534 | ->whereBetween('created_at', [$from.' 00:00:00', $to.' 23:59:59']) |
| 2535 | ->whereIn('region', $regionFilterList ?: ['__none__']) |
| 2536 | ->whereIn('skip_reason', $filterReasons) |
| 2537 | ->groupBy('region', 'skip_reason') |
| 2538 | ->get(); |
| 2539 | |
| 2540 | $skippedByRegion = []; |
| 2541 | $skippedTotals = []; |
| 2542 | foreach ($skipRows as $r) { |
| 2543 | $skippedByRegion[$r->region][$r->skip_reason] = (int) $r->skip_count; |
| 2544 | $skippedTotals[$r->skip_reason] = ($skippedTotals[$r->skip_reason] ?? 0) + (int) $r->skip_count; |
| 2545 | } |
| 2546 | |
| 2547 | // Daily breakdown — one row per (day, region) matching the shape |
| 2548 | // of docs/info/invoice_reminders_report_v3_by_day.csv. `sent` is |
| 2549 | // the count of distinct reminded invoices that day (now includes |
| 2550 | // SendGrid failures per FIRE-1293 — both those written straight |
| 2551 | // into tbl_invoice_reminders after the deploy and the historical |
| 2552 | // ones that only live in the skips table); `not_sent` is the |
| 2553 | // count of skip rows excluding the reasons above. |
| 2554 | $sentByDay = DB::query() |
| 2555 | ->fromSub($buildSentUnion(), 'u') |
| 2556 | ->selectRaw('DATE(send_datetime) AS day, region, |
| 2557 | COUNT(DISTINCT invoice_number) AS sent') |
| 2558 | ->groupBy('day', 'region') |
| 2559 | ->get(); |
| 2560 | |
| 2561 | $skipsByDay = DB::table('tbl_invoice_reminder_skips') |
| 2562 | ->selectRaw('DATE(created_at) AS day, region, COUNT(*) AS not_sent') |
| 2563 | ->whereBetween('created_at', [$from.' 00:00:00', $to.' 23:59:59']) |
| 2564 | ->whereIn('region', $regionFilterList ?: ['__none__']) |
| 2565 | ->whereIn('skip_reason', $filterReasons) |
| 2566 | ->groupBy('day', 'region') |
| 2567 | ->get(); |
| 2568 | |
| 2569 | // Merge the two by-day series. Use the key "$day|$region" so we |
| 2570 | // can carry rows that appear only in one of the two sources. |
| 2571 | $dailyMap = []; |
| 2572 | foreach ($sentByDay as $r) { |
| 2573 | $key = $r->day.'|'.$r->region; |
| 2574 | $dailyMap[$key] = ['day' => $r->day, 'region' => $r->region, 'sent' => (int) $r->sent, 'not_sent' => 0]; |
| 2575 | } |
| 2576 | foreach ($skipsByDay as $r) { |
| 2577 | $key = $r->day.'|'.$r->region; |
| 2578 | if (! isset($dailyMap[$key])) { |
| 2579 | $dailyMap[$key] = ['day' => $r->day, 'region' => $r->region, 'sent' => 0, 'not_sent' => 0]; |
| 2580 | } |
| 2581 | $dailyMap[$key]['not_sent'] = (int) $r->not_sent; |
| 2582 | } |
| 2583 | $daily = array_values($dailyMap); |
| 2584 | usort($daily, fn ($a, $b) => $a['day'] === $b['day'] |
| 2585 | ? strcmp($a['region'], $b['region']) |
| 2586 | : strcmp($a['day'], $b['day'])); |
| 2587 | foreach ($daily as &$row) { |
| 2588 | $would = $row['sent'] + $row['not_sent']; |
| 2589 | $row['would_send'] = $would; |
| 2590 | $row['gap_pct'] = $would > 0 ? round($row['not_sent'] * 100 / $would, 1) : 0.0; |
| 2591 | } |
| 2592 | unset($row); |
| 2593 | |
| 2594 | return response([ |
| 2595 | 'message' => 'OK', |
| 2596 | 'data' => [ |
| 2597 | 'window' => [ |
| 2598 | 'from' => $from, |
| 2599 | 'to' => $to, |
| 2600 | 'window_days' => $windowDays, |
| 2601 | 'region' => $regionFilter ?: 'All', |
| 2602 | ], |
| 2603 | 'accessible_regions' => $accessibleRegions, |
| 2604 | 'regions' => $regions, |
| 2605 | 'totals' => $totals, |
| 2606 | 'skipped' => [ |
| 2607 | 'by_region' => $skippedByRegion, |
| 2608 | 'totals' => $skippedTotals, |
| 2609 | ], |
| 2610 | 'daily' => $daily, |
| 2611 | ], |
| 2612 | ]); |
| 2613 | } catch (\Exception $e) { |
| 2614 | /** @disregard P1014 */ |
| 2615 | $e->exceptionCode = 'PAYMENT_REMINDERS_KPI_EXCEPTION'; |
| 2616 | report($e); |
| 2617 | |
| 2618 | return response(['message' => 'KO', 'error' => $e->getMessage()]); |
| 2619 | } |
| 2620 | } |
| 2621 | } |