Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
| Total | |
77.48% |
117 / 151 |
|
20.00% |
1 / 5 |
CRAP | |
0.00% |
0 / 1 |
| BackfillQuotationSends | |
77.48% |
117 / 151 |
|
20.00% |
1 / 5 |
48.98 | |
0.00% |
0 / 1 |
| handle | |
83.33% |
75 / 90 |
|
0.00% |
0 / 1 |
23.04 | |||
| bulkDays | |
100.00% |
18 / 18 |
|
100.00% |
1 / 1 |
5 | |||
| alreadyRecordedLive | |
75.00% |
6 / 8 |
|
0.00% |
0 / 1 |
2.06 | |||
| legacyFallback | |
62.50% |
15 / 24 |
|
0.00% |
0 / 1 |
4.84 | |||
| sentWithoutSendFallback | |
27.27% |
3 / 11 |
|
0.00% |
0 / 1 |
6.46 | |||
| 1 | <?php |
| 2 | |
| 3 | namespace App\Console\Commands; |
| 4 | |
| 5 | use App\Services\QuotationSendHistory; |
| 6 | use App\Services\QuotationSendRecorder; |
| 7 | use Illuminate\Console\Command; |
| 8 | use Illuminate\Support\Facades\DB; |
| 9 | |
| 10 | /** |
| 11 | * Rebuilds tbl_quotation_sends and tbl_quotations.sent_date from |
| 12 | * tbl_quotations_log. The rules live in App\Services\QuotationSendHistory. |
| 13 | * |
| 14 | * Safe to re-run: every send carries a source_ref ("log:<id>", "import:<qid>") |
| 15 | * that is unique, so a second run adds only what is new. sent_date only ever |
| 16 | * moves forward, to the latest send. |
| 17 | * |
| 18 | * Legacy fallback: tbl_quotations_log starts on 2025-10-27, so quotations |
| 19 | * issued before it (2023 to 2025) have no send to rebuild. Those still without |
| 20 | * a sent_date after the log pass get sent_date = issue_date, so their "issued" |
| 21 | * figures stay exactly as they were under the old issue_date rule. It is not a |
| 22 | * real send, so nothing is written to tbl_quotation_sends for them. |
| 23 | * |
| 24 | * php artisan quotations:backfill-sends --dry-run |
| 25 | * php artisan quotations:backfill-sends --since=2025-10-01 |
| 26 | */ |
| 27 | class BackfillQuotationSends extends Command |
| 28 | { |
| 29 | protected $signature = 'quotations:backfill-sends |
| 30 | {--since=2025-10-01 : Only log rows from this date} |
| 31 | {--quotation= : Only this tbl_quotations.id (to check one case)} |
| 32 | {--legacy-before= : Legacy cutoff for sent_date = issue_date (default: date of the first tbl_quotations_log row)} |
| 33 | {--legacy-only : Skip the log pass and run only the legacy sent_date = issue_date fallback} |
| 34 | {--bulk-factor=5 : A company-day with more sync events than this many times the median day of that company is a bulk event (migration/resync), not real sends} |
| 35 | {--bulk-floor=300 : ...and with more than this many sync events (small regions have tiny medians)} |
| 36 | {--reset : First empty tbl_quotation_sends and every sent_date, then rebuild (local re-runs only)} |
| 37 | {--dry-run : Count what would be written, write nothing}'; |
| 38 | |
| 39 | protected $description = 'Backfill the quotation send history and sent_date from tbl_quotations_log'; |
| 40 | |
| 41 | public function handle(): int |
| 42 | { |
| 43 | // One-off over ~70k quotations: the id list alone is ~35 MB, and a batch |
| 44 | // of quotations with long histories went past the default 128 MB |
| 45 | // (crashed locally 2026-10-08 before reaching the legacy fallback). |
| 46 | if ((int) ini_get('memory_limit') > 0 && (int) ini_get('memory_limit') < 512) { |
| 47 | ini_set('memory_limit', '512M'); |
| 48 | } |
| 49 | |
| 50 | $since = (string) $this->option('since'); |
| 51 | $dry = (bool) $this->option('dry-run'); |
| 52 | $only = $this->option('quotation'); |
| 53 | |
| 54 | if ($this->option('legacy-only')) { |
| 55 | $this->legacyFallback($dry, $only); |
| 56 | |
| 57 | return self::SUCCESS; |
| 58 | } |
| 59 | |
| 60 | if ($this->option('reset') && ! $dry) { |
| 61 | if (! $this->confirm('This empties tbl_quotation_sends and sets every sent_date to NULL before rebuilding. Continue?', true)) { |
| 62 | return self::FAILURE; |
| 63 | } |
| 64 | DB::table('tbl_quotation_sends')->delete(); |
| 65 | DB::table('tbl_quotations')->select('id')->whereNotNull('sent_date')->chunkById(5000, function ($rows): void { |
| 66 | DB::table('tbl_quotations')->whereIn('id', $rows->pluck('id'))->update(['sent_date' => null]); |
| 67 | }); |
| 68 | $this->info('Reset done: send history emptied, sent_date cleared.'); |
| 69 | } |
| 70 | |
| 71 | $ids = DB::table('tbl_quotations_log') |
| 72 | ->where('created_at', '>=', $since) |
| 73 | ->where(function ($w): void { |
| 74 | $w->where(function ($s): void { |
| 75 | $s->where('field', 'budget_status_id')->whereIn('new_value', array_merge(QuotationSendHistory::SENT, ['Aceptado'])); |
| 76 | })->orWhere('field', 'send_email_to_client') |
| 77 | ->orWhereIn('category', ['Crear solicitud', 'Creacion presupuesto']); |
| 78 | }) |
| 79 | ->when($only, fn ($q) => $q->where('quotation_id', (int) $only)) |
| 80 | ->distinct()->orderBy('quotation_id')->pluck('quotation_id'); |
| 81 | |
| 82 | $this->info(sprintf('%d quotations to check (log rows since %s)%s', count($ids), $since, $dry ? ', DRY RUN' : '')); |
| 83 | $byChannel = []; |
| 84 | $written = 0; |
| 85 | $candidates = []; // pass 1: every send found, written in pass 2 |
| 86 | $bar = $this->output->createProgressBar(count($ids)); |
| 87 | |
| 88 | foreach ($ids->chunk(100) as $chunk) { |
| 89 | $quotes = DB::table('tbl_quotations as q') |
| 90 | ->leftJoin('tbl_budget_status as s', 's.budget_status_id', '=', 'q.budget_status_id') |
| 91 | ->whereIn('q.id', $chunk) |
| 92 | ->get(['q.id', 'q.company_id', 'q.amount', 'q.send_channel', 's.name as status_name', 'q.status_by_g3w']) |
| 93 | ->keyBy('id'); |
| 94 | // The WHOLE history, not just since --since: whether a quotation was |
| 95 | // already sent, and whether a sync change is a revert, depend on what |
| 96 | // came before the window. Only sends inside the window are written. |
| 97 | $logs = DB::table('tbl_quotations_log') |
| 98 | ->whereIn('quotation_id', $chunk) |
| 99 | ->where(function ($w): void { |
| 100 | $w->whereIn('field', ['budget_status_id', 'send_email_to_client', 'amount']) |
| 101 | ->orWhereIn('category', ['Crear solicitud', 'Creacion presupuesto']); |
| 102 | }) |
| 103 | ->get(['id', 'quotation_id', 'created_at', 'user', 'field', 'old_value', 'new_value', 'category']) |
| 104 | ->groupBy('quotation_id'); |
| 105 | |
| 106 | foreach ($chunk as $qid) { |
| 107 | $bar->advance(); |
| 108 | $q = $quotes[$qid] ?? null; |
| 109 | if (! $q) { |
| 110 | continue; // deleted since |
| 111 | } |
| 112 | $rows = array_map(fn ($r) => (array) $r, ($logs[$qid] ?? collect())->all()); |
| 113 | foreach (QuotationSendHistory::sends((int) $qid, $rows, $q->send_channel, $q->amount, $q->status_name, $q->status_by_g3w) as $s) { |
| 114 | if ($s['sent_at'] < $since) { |
| 115 | continue; |
| 116 | } |
| 117 | // Imported already sent counts as a send at the import |
| 118 | // moment, exactly as live recording does |
| 119 | // (PresupuestosService: QuotationSendRecorder::IMPORT). |
| 120 | // Skipping them left every budget sent in Gestiona before |
| 121 | // the sync first saw it without a sent_date (FIRE-2034 KO, |
| 122 | // 13/10: 206 quotes of 06-09/10). A resync of old budgets |
| 123 | // "imports" hundreds per company on one day; the bulk-day |
| 124 | // guard below drops those. |
| 125 | $candidates[] = ['qid' => (int) $qid, 'company' => (int) $q->company_id, 'send' => $s]; |
| 126 | } |
| 127 | } |
| 128 | unset($quotes, $logs); |
| 129 | gc_collect_cycles(); |
| 130 | } |
| 131 | $bar->finish(); |
| 132 | $this->newLine(2); |
| 133 | |
| 134 | // Bulk events: a migration or mass resync writes hundreds of sync-driven |
| 135 | // status changes, or imports, for one company in one day (2026-05-21: |
| 136 | // ~11,700; the 20-27 April migration week: 600-1,450 a day). Those are |
| 137 | // not sends. Titan emails and manual changes are never bulk. |
| 138 | $syncChannels = [QuotationSendRecorder::G3W_STATUS, QuotationSendRecorder::ACCEPTANCE, QuotationSendRecorder::IMPORT]; |
| 139 | $bulkDays = $this->bulkDays(); |
| 140 | $skippedBulk = 0; |
| 141 | $skippedLive = 0; |
| 142 | |
| 143 | foreach ($candidates as $c) { |
| 144 | $s = $c['send']; |
| 145 | if (in_array($s['channel'], $syncChannels, true) |
| 146 | && isset($bulkDays[$c['company'].'|'.substr($s['sent_at'], 0, 10)])) { |
| 147 | $skippedBulk++; |
| 148 | |
| 149 | continue; |
| 150 | } |
| 151 | if ($this->alreadyRecordedLive($c['qid'], $s)) { |
| 152 | $skippedLive++; |
| 153 | |
| 154 | continue; |
| 155 | } |
| 156 | $byChannel[$s['channel']] = ($byChannel[$s['channel']] ?? 0) + 1; |
| 157 | if ($dry) { |
| 158 | continue; |
| 159 | } |
| 160 | if (QuotationSendRecorder::record($c['qid'], $s['channel'], $s['user'], $s['sent_at'], |
| 161 | $s['amount'], 'backfill', $s['source_ref'], $s['status'])) { |
| 162 | $written++; |
| 163 | } |
| 164 | } |
| 165 | unset($candidates); |
| 166 | |
| 167 | $this->info(sprintf('Bulk days (> %sx the median day of the company and > %d sync events): %d days, %d sends skipped.', |
| 168 | $this->option('bulk-factor'), (int) $this->option('bulk-floor'), count($bulkDays), $skippedBulk)); |
| 169 | foreach ($bulkDays as $key => [$n, $limit]) { |
| 170 | [$company, $day] = explode('|', $key); |
| 171 | $this->line(" company {$company} {$day} {$n} sync events (limit {$limit})"); |
| 172 | } |
| 173 | $this->info("Already recorded live (skipped): {$skippedLive}."); |
| 174 | |
| 175 | ksort($byChannel); |
| 176 | $this->table(['channel', 'sends found'], array_map(fn ($c, $n) => [$c, $n], array_keys($byChannel), $byChannel)); |
| 177 | $this->info($dry ? 'Dry run: nothing written.' : sprintf('%d sends written (already-present ones skipped).', $written)); |
| 178 | |
| 179 | $this->legacyFallback($dry, $only); |
| 180 | |
| 181 | return self::SUCCESS; |
| 182 | } |
| 183 | |
| 184 | /** |
| 185 | * Company-days that were a migration or mass resync, keyed "company|Y-m-d" |
| 186 | * => [events, limit]. Measured on the whole log, not just the --since |
| 187 | * window, against each company's own normal volume: a fixed limit of 100 |
| 188 | * flagged a normal busy Madrid day (09/10: 153 events, median 87) and |
| 189 | * dropped its 105 real sends (FIRE-2034 KO, 14/10). |
| 190 | */ |
| 191 | private function bulkDays(): array |
| 192 | { |
| 193 | $rows = DB::select(" |
| 194 | SELECT q.company_id AS c, DATE(l.created_at) AS d, COUNT(*) AS n |
| 195 | FROM tbl_quotations_log l |
| 196 | JOIN tbl_quotations q ON q.id = l.quotation_id |
| 197 | WHERE l.user IN ('G3W', 'Gestiona', 'Cofrai') |
| 198 | AND (l.category = 'Creacion presupuesto' |
| 199 | OR (l.field = 'budget_status_id' AND l.new_value IN ('Enviado', 'Enviado - IA', 'Aceptado'))) |
| 200 | GROUP BY q.company_id, DATE(l.created_at)"); |
| 201 | |
| 202 | $byCompany = []; |
| 203 | foreach ($rows as $r) { |
| 204 | $byCompany[$r->c][$r->d] = (int) $r->n; |
| 205 | } |
| 206 | |
| 207 | $factor = max(1.0, (float) $this->option('bulk-factor')); |
| 208 | $floor = max(1, (int) $this->option('bulk-floor')); |
| 209 | $bulk = []; |
| 210 | foreach ($byCompany as $company => $days) { |
| 211 | $counts = array_values($days); |
| 212 | sort($counts); |
| 213 | $median = $counts[intdiv(count($counts) - 1, 2)]; |
| 214 | $limit = (int) max($floor, ceil($factor * $median)); |
| 215 | foreach ($days as $day => $n) { |
| 216 | if ($n > $limit) { |
| 217 | $bulk[$company.'|'.$day] = [$n, $limit]; |
| 218 | } |
| 219 | } |
| 220 | } |
| 221 | ksort($bulk); |
| 222 | |
| 223 | return $bulk; |
| 224 | } |
| 225 | |
| 226 | /** |
| 227 | * A send live recording already wrote. Live rows have no source_ref, so the |
| 228 | * source_ref check in QuotationSendRecorder::record() can't see them and a |
| 229 | * re-run would add the same send twice. Match on channel and time instead; |
| 230 | * a quotation is imported only once, so any live import row is the one. |
| 231 | */ |
| 232 | private function alreadyRecordedLive(int $quotationId, array $send): bool |
| 233 | { |
| 234 | $query = DB::table('tbl_quotation_sends') |
| 235 | ->where('quotation_id', $quotationId) |
| 236 | ->where('channel', $send['channel']) |
| 237 | ->whereNull('source_ref'); |
| 238 | |
| 239 | if ($send['channel'] !== QuotationSendRecorder::IMPORT) { |
| 240 | $at = strtotime($send['sent_at']); |
| 241 | $query->whereBetween('sent_at', [date('Y-m-d H:i:s', $at - 900), date('Y-m-d H:i:s', $at + 900)]); |
| 242 | } |
| 243 | |
| 244 | return $query->exists(); |
| 245 | } |
| 246 | |
| 247 | /** |
| 248 | * sent_date = issue_date for quotations issued before the log existed that |
| 249 | * the log pass could not date. Runs after it, so a real send always wins. |
| 250 | */ |
| 251 | private function legacyFallback(bool $dry, $only): void |
| 252 | { |
| 253 | $cutoff = $this->option('legacy-before') |
| 254 | ?: DB::table('tbl_quotations_log')->min(DB::raw('DATE(created_at)')); |
| 255 | |
| 256 | if (! $cutoff) { |
| 257 | $this->warn('Legacy fallback skipped: tbl_quotations_log is empty and no --legacy-before given.'); |
| 258 | |
| 259 | return; |
| 260 | } |
| 261 | |
| 262 | $base = fn () => DB::table('tbl_quotations') |
| 263 | ->whereNull('sent_date') |
| 264 | ->whereNotNull('issue_date') |
| 265 | ->where('issue_date', '<', $cutoff) |
| 266 | ->when($only, fn ($q) => $q->where('id', (int) $only)); |
| 267 | |
| 268 | $count = $base()->count(); |
| 269 | if ($dry) { |
| 270 | $this->info(sprintf('Legacy fallback: %d quotations issued before %s would get sent_date = issue_date.', $count, $cutoff)); |
| 271 | $this->sentWithoutSendFallback($dry, $only); |
| 272 | |
| 273 | return; |
| 274 | } |
| 275 | |
| 276 | // Chunked by id so each UPDATE locks few rows while the syncs run. |
| 277 | $updated = 0; |
| 278 | $base()->select('id')->chunkById(5000, function ($rows) use (&$updated): void { |
| 279 | $updated += DB::table('tbl_quotations') |
| 280 | ->whereIn('id', $rows->pluck('id')) |
| 281 | ->whereNull('sent_date') |
| 282 | ->update(['sent_date' => DB::raw('issue_date')]); |
| 283 | }); |
| 284 | |
| 285 | $this->info(sprintf('Legacy fallback: %d quotations issued before %s got sent_date = issue_date.', $updated, $cutoff)); |
| 286 | |
| 287 | $this->sentWithoutSendFallback($dry, $only); |
| 288 | } |
| 289 | |
| 290 | /** |
| 291 | * Sent, but no send on record anywhere (e.g. an old budget re-dated by |
| 292 | * hand): sent_date = issue_date. Runs last, after every real send. |
| 293 | */ |
| 294 | private function sentWithoutSendFallback(bool $dry, $only): void |
| 295 | { |
| 296 | if ($dry) { |
| 297 | $count = DB::table('tbl_quotations as q') |
| 298 | ->whereIn('q.budget_status_id', QuotationSendRecorder::SENT_STATUS_IDS) |
| 299 | ->whereNull('q.sent_date')->whereNotNull('q.issue_date')->whereNull('q.deleted_at') |
| 300 | ->whereNotExists(fn ($w) => $w->from('tbl_quotation_sends as s')->whereColumn('s.quotation_id', 'q.id')) |
| 301 | ->when($only, fn ($q) => $q->where('q.id', (int) $only)) |
| 302 | ->count(); |
| 303 | $this->info(sprintf('Sent without any recorded send: %d quotations would get sent_date = issue_date.', $count)); |
| 304 | |
| 305 | return; |
| 306 | } |
| 307 | |
| 308 | $updated = QuotationSendRecorder::fallbackToIssueDate($only ? (int) $only : null); |
| 309 | $this->info(sprintf('Sent without any recorded send: %d quotations got sent_date = issue_date.', $updated)); |
| 310 | } |
| 311 | } |