Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
| Total | |
89.18% |
173 / 194 |
|
44.44% |
4 / 9 |
CRAP | |
0.00% |
0 / 1 |
| BackfillAcceptanceDates | |
89.18% |
173 / 194 |
|
44.44% |
4 / 9 |
89.32 | |
0.00% |
0 / 1 |
| handle | |
86.96% |
80 / 92 |
|
0.00% |
0 / 1 |
28.62 | |||
| origin | |
100.00% |
4 / 4 |
|
100.00% |
1 / 1 |
4 | |||
| region | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
2 | |||
| validDate | |
100.00% |
4 / 4 |
|
100.00% |
1 / 1 |
4 | |||
| reason | |
75.00% |
6 / 8 |
|
0.00% |
0 / 1 |
20.00 | |||
| gestionaStatusNames | |
50.00% |
2 / 4 |
|
0.00% |
0 / 1 |
2.50 | |||
| loadIfexBudgets | |
85.71% |
12 / 14 |
|
0.00% |
0 / 1 |
5.07 | |||
| write | |
90.00% |
27 / 30 |
|
0.00% |
0 / 1 |
9.08 | |||
| report | |
100.00% |
37 / 37 |
|
100.00% |
1 / 1 |
12 | |||
| 1 | <?php |
| 2 | |
| 3 | namespace App\Console\Commands; |
| 4 | |
| 5 | use App\Http\Controllers\IfexController; |
| 6 | use App\Http\Controllers\Quotations; |
| 7 | use App\Services\IfexReference; |
| 8 | use App\Services\PresupuestosService; |
| 9 | use App\Services\ResultCache; |
| 10 | use Carbon\Carbon; |
| 11 | use Illuminate\Console\Command; |
| 12 | use Illuminate\Support\Facades\DB; |
| 13 | |
| 14 | /** |
| 15 | * Fill acceptance_date on synced quotes that are "Aceptado" without one |
| 16 | * (FIRE-2117 follow-up: 7,321 quotes on 13/10/2026). They count in no |
| 17 | * "accepted in period X" figure. |
| 18 | * |
| 19 | * Each quote is asked to the ERP it comes from, then Titan's own history: |
| 20 | * 1. gestiona: Gestiona's `aceptacion` for the budget (GET presupuesto/{id}, |
| 21 | * per region). Source of truth; the DWH copy is partial. |
| 22 | * 1. ifex: IFEX's `fechaAceptacion`, for the Valencia budgets imported |
| 23 | * from IFEX (commercial "Ifex", ids like 202626P…, 2026G26…). These are |
| 24 | * not Gestiona budgets and can never be matched to one. |
| 25 | * 2. history: when tbl_quotations_log recorded the quote becoming |
| 26 | * "Aceptado" (the log starts on 2025-10-27). Not used for IFEX budgets |
| 27 | * that IFEX does not consider accepted ("Finalizado" was mapped to |
| 28 | * Aceptado before FIRE-1672): that log date is the old mapping, not an |
| 29 | * acceptance. |
| 30 | * Nothing else: a quote with no trustworthy date keeps acceptance_date empty |
| 31 | * and gets a reason. Statuses are never changed here. |
| 32 | * |
| 33 | * Every quote looked at gets a row in tbl_acceptance_date_backfill (source, |
| 34 | * ERP status, reason) when run with --apply. An API error is not "no date": |
| 35 | * those quotes are left untouched and unrecorded, so a re-run asks again. |
| 36 | * |
| 37 | * php artisan quotations:backfill-acceptance-dates # dry run |
| 38 | * php artisan quotations:backfill-acceptance-dates --limit=20 # dry run on a sample |
| 39 | * php artisan quotations:backfill-acceptance-dates --apply --csv |
| 40 | */ |
| 41 | class BackfillAcceptanceDates extends Command |
| 42 | { |
| 43 | protected $signature = 'quotations:backfill-acceptance-dates |
| 44 | {--apply : Write the dates and the backfill table; without it nothing changes} |
| 45 | {--company= : Only this company_id} |
| 46 | {--limit= : Only the first N quotes (to test)} |
| 47 | {--skip-gestiona : Do not ask Gestiona} |
| 48 | {--skip-ifex : Do not ask IFEX} |
| 49 | {--sleep=100 : Milliseconds between Gestiona requests} |
| 50 | {--ifex-from=2025-12-01 : First day of the IFEX windows} |
| 51 | {--csv : Write every quote looked at, with its result, to storage/app}'; |
| 52 | |
| 53 | protected $description = 'Fill acceptance_date on synced "Aceptado" quotes from Gestiona, IFEX or Titan history'; |
| 54 | |
| 55 | private const UPDATED_BY = 'acceptance-date backfill'; |
| 56 | |
| 57 | /** Gestiona region for each company; Madrid's company row has its own region name. */ |
| 58 | private array $regions = []; |
| 59 | |
| 60 | public function handle(PresupuestosService $gestiona, IfexController $ifex, Quotations $quotations): int |
| 61 | { |
| 62 | $apply = (bool) $this->option('apply'); |
| 63 | $runAt = now()->format('Y-m-d H:i:s'); |
| 64 | |
| 65 | $rows = DB::table('tbl_quotations') |
| 66 | ->where('budget_status_id', 3) |
| 67 | ->whereNull('acceptance_date') |
| 68 | ->where('sync_import', 1) |
| 69 | ->whereNull('deleted_at') |
| 70 | ->when($this->option('company'), fn ($q, $c) => $q->where('company_id', (int) $c)) |
| 71 | ->orderBy('id') |
| 72 | ->when($this->option('limit'), fn ($q, $n) => $q->limit((int) $n)) |
| 73 | ->get(['id', 'quote_id', 'internal_quote_id', 'company_id', 'commercial']); |
| 74 | |
| 75 | $this->info(sprintf('%d accepted synced quotes without acceptance_date%s', $rows->count(), $apply ? '' : ' [DRY RUN]')); |
| 76 | if ($rows->isEmpty()) { |
| 77 | return self::SUCCESS; |
| 78 | } |
| 79 | |
| 80 | $this->regions = DB::table('tbl_companies')->pluck('region', 'company_id')->all(); |
| 81 | |
| 82 | // When Titan saw each quote become Aceptado (latest such change). |
| 83 | $history = DB::table('tbl_quotations_log') |
| 84 | ->whereIn('quotation_id', $rows->pluck('id')) |
| 85 | ->where('field', 'budget_status_id') |
| 86 | ->where('new_value', 'Aceptado') |
| 87 | ->groupBy('quotation_id') |
| 88 | ->selectRaw('quotation_id, MAX(created_at) AS at') |
| 89 | ->pluck('at', 'quotation_id'); |
| 90 | |
| 91 | $ifexBudgets = []; |
| 92 | $hasIfex = $rows->contains(fn ($r) => $this->origin($r) === 'ifex'); |
| 93 | if ($hasIfex && ! $this->option('skip-ifex')) { |
| 94 | try { |
| 95 | $ifexBudgets = $this->loadIfexBudgets($ifex); |
| 96 | } catch (\RuntimeException $e) { |
| 97 | // A missing window would turn real IFEX budgets into "not in IFEX". |
| 98 | $this->error($e->getMessage().' Stopping; nothing written.'); |
| 99 | |
| 100 | return self::FAILURE; |
| 101 | } |
| 102 | } |
| 103 | $gestionaStatuses = []; |
| 104 | |
| 105 | $results = []; |
| 106 | $errors = 0; |
| 107 | $bar = $this->output->createProgressBar($rows->count()); |
| 108 | |
| 109 | foreach ($rows as $row) { |
| 110 | $bar->advance(); |
| 111 | $origin = $this->origin($row); |
| 112 | $r = [ |
| 113 | 'quotation_id' => $row->id, 'company_id' => $row->company_id, 'quote_id' => $row->quote_id, |
| 114 | 'internal_quote_id' => $row->internal_quote_id, 'origin' => $origin, |
| 115 | 'erp_found' => null, 'erp_status' => null, 'erp_acceptance' => null, |
| 116 | 'source' => null, 'acceptance_date' => null, 'reason' => null, |
| 117 | ]; |
| 118 | $historyAllowed = true; |
| 119 | |
| 120 | if ($origin === 'gestiona' && ! $this->option('skip-gestiona')) { |
| 121 | $region = $this->region((int) $row->company_id); |
| 122 | try { |
| 123 | $budget = $gestiona->request('get', 'presupuesto/'.$row->internal_quote_id, $region, [])['presupuesto'] ?? []; |
| 124 | $gestionaStatuses[$region] ??= $this->gestionaStatusNames($gestiona, $region); |
| 125 | $r['erp_found'] = true; |
| 126 | $r['erp_status'] = $gestionaStatuses[$region][(string) ($budget['estado'] ?? '')] ?? (string) ($budget['estado'] ?? ''); |
| 127 | $r['erp_acceptance'] = isset($budget['aceptacion']) ? (string) $budget['aceptacion'] : null; |
| 128 | if ($date = $this->validDate($budget['aceptacion'] ?? null)) { |
| 129 | $r['source'] = 'gestiona'; |
| 130 | $r['acceptance_date'] = $date; |
| 131 | } |
| 132 | } catch (\Throwable $e) { |
| 133 | if ((int) $e->getCode() === 404) { |
| 134 | $r['erp_found'] = false; |
| 135 | } else { |
| 136 | $errors++; // API trouble is not "no date": ask again on the next run |
| 137 | |
| 138 | continue; |
| 139 | } |
| 140 | } finally { |
| 141 | if (($ms = max(0, (int) $this->option('sleep'))) > 0) { |
| 142 | usleep($ms * 1000); |
| 143 | } |
| 144 | } |
| 145 | } elseif ($origin === 'ifex' && $ifexBudgets !== []) { |
| 146 | $budget = $ifexBudgets[IfexReference::normalize($row->internal_quote_id)] ?? null; |
| 147 | $r['erp_found'] = $budget !== null; |
| 148 | if ($budget) { |
| 149 | $r['erp_status'] = $budget['estado'] ?? null; |
| 150 | $r['erp_acceptance'] = $budget['fechaAceptacion'] ?? null; |
| 151 | if ($date = $this->validDate($budget['fechaAceptacion'] ?? null)) { |
| 152 | $r['source'] = 'ifex'; |
| 153 | $r['acceptance_date'] = $date; |
| 154 | } elseif (($budget['estado'] ?? null) !== 'Aceptado') { |
| 155 | // IFEX does not consider it accepted (Finalizado through |
| 156 | // the pre-FIRE-1672 mapping, Anulado, Rechazado…): a log |
| 157 | // date would be that wrong status, not an acceptance. |
| 158 | $historyAllowed = false; |
| 159 | } |
| 160 | } |
| 161 | } |
| 162 | |
| 163 | if ($r['source'] === null && $historyAllowed && isset($history[$row->id])) { |
| 164 | $r['source'] = 'history'; |
| 165 | $r['acceptance_date'] = Carbon::parse($history[$row->id])->toDateString(); |
| 166 | } |
| 167 | |
| 168 | if ($r['source'] === null) { |
| 169 | $r['reason'] = $this->reason($r, $historyAllowed); |
| 170 | } |
| 171 | $results[] = $r; |
| 172 | } |
| 173 | $bar->finish(); |
| 174 | $this->newLine(2); |
| 175 | |
| 176 | if ($apply) { |
| 177 | $this->write($results, $runAt, $quotations); |
| 178 | } |
| 179 | $this->report($results, $errors, $apply); |
| 180 | |
| 181 | if ($this->option('csv')) { |
| 182 | $path = 'acceptance-date-backfill-'.now()->format('Ymd-His').'.csv'; |
| 183 | $cols = ['quotation_id', 'company_id', 'quote_id', 'internal_quote_id', 'origin', 'erp_found', 'erp_status', 'erp_acceptance', 'source', 'acceptance_date', 'reason']; |
| 184 | $lines = [implode(',', $cols)]; |
| 185 | $cell = fn ($v) => '"'.str_replace('"', '""', is_bool($v) ? (string) (int) $v : (string) $v).'"'; |
| 186 | foreach ($results as $r) { |
| 187 | $lines[] = implode(',', array_map(fn ($c) => $cell($r[$c]), $cols)); |
| 188 | } |
| 189 | file_put_contents(storage_path('app/'.$path), implode("\n", $lines)."\n"); |
| 190 | $this->info('Every quote and its result: storage/app/'.$path); |
| 191 | } |
| 192 | |
| 193 | return self::SUCCESS; |
| 194 | } |
| 195 | |
| 196 | /** gestiona: numeric Gestiona id · ifex: an IFEX reference · unknown: anything else. */ |
| 197 | private function origin(object $row): string |
| 198 | { |
| 199 | $id = trim((string) $row->internal_quote_id); |
| 200 | if ($row->commercial === 'Ifex' && IfexReference::isValid(IfexReference::normalize($id))) { |
| 201 | return 'ifex'; |
| 202 | } |
| 203 | |
| 204 | return preg_match('/^\d+$/', $id) ? 'gestiona' : 'unknown'; |
| 205 | } |
| 206 | |
| 207 | private function region(int $companyId): ?string |
| 208 | { |
| 209 | return $companyId === 18 ? 'Madrid' : ($this->regions[$companyId] ?? null); |
| 210 | } |
| 211 | |
| 212 | private function validDate($raw): ?string |
| 213 | { |
| 214 | $raw = is_string($raw) ? trim($raw) : ''; |
| 215 | if (! preg_match('/^\d{4}-\d{2}-\d{2}/', $raw) || str_starts_with($raw, '0000')) { |
| 216 | return null; |
| 217 | } |
| 218 | |
| 219 | return substr($raw, 0, 10); |
| 220 | } |
| 221 | |
| 222 | private function reason(array $r, bool $historyAllowed): string |
| 223 | { |
| 224 | return match (true) { |
| 225 | $r['origin'] === 'unknown' => 'unknown origin (not a Gestiona or IFEX id) and no log', |
| 226 | $r['origin'] === 'gestiona' && $r['erp_found'] === false => 'not in Gestiona and no log', |
| 227 | $r['origin'] === 'gestiona' && $r['erp_found'] === true => 'in Gestiona without date and no log', |
| 228 | $r['origin'] === 'ifex' && $r['erp_found'] === false => 'not in IFEX and no log', |
| 229 | $r['origin'] === 'ifex' && ! $historyAllowed && $r['erp_status'] === 'Finalizado' => 'not accepted in IFEX (Finalizado, no acceptance date): Aceptado in Titan through the pre-FIRE-1672 mapping', |
| 230 | $r['origin'] === 'ifex' && ! $historyAllowed => "not accepted in IFEX (status {$r['erp_status']}, no acceptance date)", |
| 231 | $r['origin'] === 'ifex' && $r['erp_found'] === true => 'in IFEX without date and no log', |
| 232 | default => 'ERP not asked and no log', |
| 233 | }; |
| 234 | } |
| 235 | |
| 236 | /** @return array<string,string> estado id => name */ |
| 237 | private function gestionaStatusNames(PresupuestosService $gestiona, ?string $region): array |
| 238 | { |
| 239 | try { |
| 240 | return collect($gestiona->request('get', 'presupuesto/tiposestado', $region, [])) |
| 241 | ->mapWithKeys(fn ($s) => [(string) $s['ID'] => $s['nombre']])->all(); |
| 242 | } catch (\Throwable) { |
| 243 | return []; |
| 244 | } |
| 245 | } |
| 246 | |
| 247 | /** |
| 248 | * IFEX only lists budgets by window (created or edited), so read weekly |
| 249 | * windows from --ifex-from to today and keep each budget's latest state. |
| 250 | * |
| 251 | * @return array<string, array<string,mixed>> reference => budget |
| 252 | */ |
| 253 | private function loadIfexBudgets(IfexController $ifex): array |
| 254 | { |
| 255 | $budgets = []; |
| 256 | $from = Carbon::parse((string) $this->option('ifex-from'))->startOfDay(); |
| 257 | $end = now()->startOfDay(); |
| 258 | $this->info('Reading IFEX budgets '.$from->toDateString().' → '.$end->toDateString().' …'); |
| 259 | |
| 260 | for ($start = $from->copy(); $start->lte($end); $start->addDays(7)) { |
| 261 | $stop = $start->copy()->addDays(6)->min($end); |
| 262 | try { |
| 263 | foreach ($ifex->budgetsInWindow($start->toDateString(), $stop->toDateString()) as $b) { |
| 264 | $ref = IfexReference::normalize($b['codigo'] ?? null); |
| 265 | if ($ref !== null) { |
| 266 | $budgets[$ref] = $b; // later windows win: latest state |
| 267 | } |
| 268 | } |
| 269 | } catch (\Throwable $e) { |
| 270 | throw new \RuntimeException("IFEX window {$start->toDateString()} failed: {$e->getMessage()}.", 0, $e); |
| 271 | } |
| 272 | } |
| 273 | $this->info(count($budgets).' IFEX budgets read.'); |
| 274 | |
| 275 | return $budgets; |
| 276 | } |
| 277 | |
| 278 | private function write(array $results, string $runAt, Quotations $quotations): void |
| 279 | { |
| 280 | $companies = []; |
| 281 | foreach ($results as $r) { |
| 282 | if ($r['source'] !== null) { |
| 283 | $updated = DB::table('tbl_quotations') |
| 284 | ->where('id', $r['quotation_id']) |
| 285 | ->whereNull('acceptance_date') // never overwrite a date set meanwhile |
| 286 | ->update([ |
| 287 | // Same fields the sync sets when it accepts a quote. |
| 288 | 'acceptance_date' => $r['acceptance_date'], |
| 289 | 'accepted_at' => $r['acceptance_date'], |
| 290 | 'accepted_by' => 'System', |
| 291 | 'updated_by' => self::UPDATED_BY, |
| 292 | 'updated_at' => now(), |
| 293 | ]); |
| 294 | if (! $updated) { |
| 295 | continue; |
| 296 | } |
| 297 | $quotations->addUpdateLog($r['quotation_id'], self::UPDATED_BY.' ('.$r['source'].')', 'acceptance_date', null, $r['acceptance_date'], 4); |
| 298 | $companies[$r['company_id']] = true; |
| 299 | } |
| 300 | |
| 301 | DB::table('tbl_acceptance_date_backfill')->insert([ |
| 302 | 'quotation_id' => $r['quotation_id'], 'company_id' => $r['company_id'], 'quote_id' => $r['quote_id'], |
| 303 | 'internal_quote_id' => $r['internal_quote_id'], 'origin' => $r['origin'], 'erp_found' => $r['erp_found'], |
| 304 | 'erp_status' => $r['erp_status'] !== null ? mb_substr((string) $r['erp_status'], 0, 100) : null, |
| 305 | 'erp_acceptance' => $r['erp_acceptance'] !== null ? mb_substr((string) $r['erp_acceptance'], 0, 30) : null, |
| 306 | 'source' => $r['source'], 'acceptance_date' => $r['acceptance_date'], |
| 307 | 'reason' => $r['reason'] !== null ? mb_substr($r['reason'], 0, 255) : null, 'run_at' => $runAt, |
| 308 | ]); |
| 309 | } |
| 310 | |
| 311 | foreach (array_keys($companies) as $companyId) { |
| 312 | try { |
| 313 | $quotations->update_commercial_numbers($companyId); |
| 314 | } catch (\Throwable $e) { |
| 315 | $this->warn("update_commercial_numbers failed for company {$companyId}: {$e->getMessage()}"); |
| 316 | } |
| 317 | } |
| 318 | ResultCache::forgetDomain('quotations'); |
| 319 | } |
| 320 | |
| 321 | private function report(array $results, int $errors, bool $apply): void |
| 322 | { |
| 323 | $names = DB::table('tbl_companies')->pluck('name', 'company_id'); |
| 324 | $bySource = []; |
| 325 | foreach ($results as $r) { |
| 326 | $key = $r['source'] ?? 'not filled'; |
| 327 | $bySource[$r['company_id']][$key] = ($bySource[$r['company_id']][$key] ?? 0) + 1; |
| 328 | } |
| 329 | ksort($bySource); |
| 330 | $cols = ['gestiona', 'ifex', 'history', 'not filled']; |
| 331 | $rows = []; |
| 332 | $totals = array_fill_keys($cols, 0); |
| 333 | foreach ($bySource as $company => $counts) { |
| 334 | $line = [($names[$company] ?? '?')." ({$company})"]; |
| 335 | foreach ($cols as $c) { |
| 336 | $line[] = $counts[$c] ?? 0; |
| 337 | $totals[$c] += $counts[$c] ?? 0; |
| 338 | } |
| 339 | $line[] = array_sum($counts); |
| 340 | $rows[] = $line; |
| 341 | } |
| 342 | $rows[] = array_merge(['Total'], array_values($totals), [array_sum($totals)]); |
| 343 | $this->info($apply ? 'Filled per region and source:' : 'Would fill, per region and source:'); |
| 344 | $this->table(array_merge(['Region'], $cols, ['Total']), $rows); |
| 345 | |
| 346 | $reasons = []; |
| 347 | foreach ($results as $r) { |
| 348 | if ($r['reason'] !== null) { |
| 349 | $reasons[$r['reason']] = ($reasons[$r['reason']] ?? 0) + 1; |
| 350 | } |
| 351 | } |
| 352 | arsort($reasons); |
| 353 | $this->info('Not filled, by reason:'); |
| 354 | $this->table(['Reason', 'Quotes'], array_map(fn ($k, $v) => [$k, $v], array_keys($reasons), $reasons)); |
| 355 | |
| 356 | $erp = []; |
| 357 | foreach ($results as $r) { |
| 358 | if ($r['erp_found'] === null) { |
| 359 | continue; |
| 360 | } |
| 361 | $key = $r['origin'].' · '.($r['erp_found'] ? 'found, status '.($r['erp_status'] ?? '?').($r['source'] === $r['origin'] ? ', with date' : ', no date') : 'NOT FOUND'); |
| 362 | $erp[$key] = ($erp[$key] ?? 0) + 1; |
| 363 | } |
| 364 | ksort($erp); |
| 365 | $this->info('What the ERPs said:'); |
| 366 | $this->table(['ERP answer', 'Quotes'], array_map(fn ($k, $v) => [$k, $v], array_keys($erp), $erp)); |
| 367 | |
| 368 | if ($errors > 0) { |
| 369 | $this->warn("{$errors} Gestiona lookups failed (API error, not 404): those quotes were skipped; run again to retry them."); |
| 370 | } |
| 371 | } |
| 372 | } |