Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
| Total | |
20.20% |
352 / 1743 |
|
10.20% |
5 / 49 |
CRAP | |
0.00% |
0 / 1 |
| PresupuestosService | |
20.20% |
352 / 1743 |
|
10.20% |
5 / 49 |
163968.24 | |
0.00% |
0 / 1 |
| __construct | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| normalizeG3wAcceptance | |
0.00% |
0 / 10 |
|
0.00% |
0 / 1 |
30 | |||
| resolveCompanyForRegion | |
83.33% |
5 / 6 |
|
0.00% |
0 / 1 |
3.04 | |||
| syncByDate | |
84.26% |
91 / 108 |
|
0.00% |
0 / 1 |
35.99 | |||
| fetchFromGestiona | |
0.00% |
0 / 23 |
|
0.00% |
0 / 1 |
42 | |||
| syncById | |
0.00% |
0 / 337 |
|
0.00% |
0 / 1 |
11772 | |||
| syncModifiedBudgetById | |
0.00% |
0 / 344 |
|
0.00% |
0 / 1 |
11556 | |||
| classifyLineAsLabor | |
0.00% |
0 / 10 |
|
0.00% |
0 / 1 |
56 | |||
| isBlankClientField | |
0.00% |
0 / 4 |
|
0.00% |
0 / 1 |
12 | |||
| calculateBudgetMargin | |
0.00% |
0 / 55 |
|
0.00% |
0 / 1 |
380 | |||
| syncErrorBudgets | |
96.23% |
51 / 53 |
|
0.00% |
0 / 1 |
11 | |||
| syncBudgetsWorks | |
69.35% |
43 / 62 |
|
0.00% |
0 / 1 |
16.14 | |||
| normalizeFailures | |
0.00% |
0 / 5 |
|
0.00% |
0 / 1 |
30 | |||
| notifyErrors | |
0.00% |
0 / 9 |
|
0.00% |
0 / 1 |
6 | |||
| generateQuoteId | |
0.00% |
0 / 33 |
|
0.00% |
0 / 1 |
90 | |||
| resolveUserMapping | |
0.00% |
0 / 11 |
|
0.00% |
0 / 1 |
42 | |||
| budgetStatusIdByName | |
0.00% |
0 / 1 |
|
0.00% |
0 / 1 |
2 | |||
| normalizeStatus | |
0.00% |
0 / 10 |
|
0.00% |
0 / 1 |
12 | |||
| normalizeSegment | |
0.00% |
0 / 8 |
|
0.00% |
0 / 1 |
6 | |||
| normalizeType | |
0.00% |
0 / 19 |
|
0.00% |
0 / 1 |
30 | |||
| normalizeSource | |
0.00% |
0 / 21 |
|
0.00% |
0 / 1 |
56 | |||
| saveDocument | |
0.00% |
0 / 53 |
|
0.00% |
0 / 1 |
110 | |||
| formatBytes | |
0.00% |
0 / 6 |
|
0.00% |
0 / 1 |
6 | |||
| updateLogs | |
0.00% |
0 / 35 |
|
0.00% |
0 / 1 |
20 | |||
| acceptanceDateFallback | |
0.00% |
0 / 8 |
|
0.00% |
0 / 1 |
42 | |||
| modifiedBudgetWindows | |
18.75% |
3 / 16 |
|
0.00% |
0 / 1 |
25.31 | |||
| hasMarginData | |
0.00% |
0 / 4 |
|
0.00% |
0 / 1 |
20 | |||
| uniqueBudgetsById | |
90.00% |
9 / 10 |
|
0.00% |
0 / 1 |
6.04 | |||
| changedSyncFields | |
0.00% |
0 / 12 |
|
0.00% |
0 / 1 |
72 | |||
| sameStoredValue | |
0.00% |
0 / 18 |
|
0.00% |
0 / 1 |
132 | |||
| checkEmailInvalid | |
0.00% |
0 / 8 |
|
0.00% |
0 / 1 |
12 | |||
| checkRequiredFields | |
95.45% |
21 / 22 |
|
0.00% |
0 / 1 |
22 | |||
| hasNonEmailWarning | |
100.00% |
4 / 4 |
|
100.00% |
1 / 1 |
3 | |||
| computeG3wWarning | |
100.00% |
5 / 5 |
|
100.00% |
1 / 1 |
2 | |||
| isBlacklistedEmail | |
75.00% |
6 / 8 |
|
0.00% |
0 / 1 |
5.39 | |||
| syncExistingDataWithWarnings | |
100.00% |
16 / 16 |
|
100.00% |
1 / 1 |
6 | |||
| syncManualBudgetEmailStatus | |
0.00% |
0 / 34 |
|
0.00% |
0 / 1 |
210 | |||
| isManualEmailInvalid | |
0.00% |
0 / 3 |
|
0.00% |
0 / 1 |
20 | |||
| parseSyncIds | |
85.71% |
6 / 7 |
|
0.00% |
0 / 1 |
5.07 | |||
| syncByIds | |
97.22% |
70 / 72 |
|
0.00% |
0 / 1 |
14 | |||
| syncNullBudget | |
100.00% |
21 / 21 |
|
100.00% |
1 / 1 |
5 | |||
| getAlternativeClientData | |
0.00% |
0 / 26 |
|
0.00% |
0 / 1 |
132 | |||
| checkAproval | |
0.00% |
0 / 4 |
|
0.00% |
0 / 1 |
6 | |||
| suppressFinancialLevel | |
0.00% |
0 / 7 |
|
0.00% |
0 / 1 |
42 | |||
| computeForApprovalLevel | |
0.00% |
0 / 36 |
|
0.00% |
0 / 1 |
210 | |||
| sendApprovalNotificationOnly | |
0.00% |
0 / 39 |
|
0.00% |
0 / 1 |
110 | |||
| listBulkSyncRegions | |
0.00% |
0 / 9 |
|
0.00% |
0 / 1 |
2 | |||
| syncSpecificFieldsForId | |
0.00% |
0 / 74 |
|
0.00% |
0 / 1 |
380 | |||
| bulkSyncByFilters | |
0.00% |
0 / 56 |
|
0.00% |
0 / 1 |
110 | |||
| 1 | <?php |
| 2 | |
| 3 | namespace App\Services; |
| 4 | |
| 5 | use App\Http\Controllers\Quotations; |
| 6 | use App\Models\TblBudgetStatus; |
| 7 | use App\Models\TblBudgetTypes; |
| 8 | use App\Models\TblCcAcceptanceNotifications; |
| 9 | use App\Models\TblCompanies; |
| 10 | use App\Models\TblFiles; |
| 11 | use App\Models\TblG3WBulkSyncRuns; |
| 12 | use App\Models\TblG3wLastUpdate; |
| 13 | use App\Models\TblG3WOrdersUpdateLogs; |
| 14 | use App\Models\TblInvoicesExceptions; |
| 15 | use App\Models\TblLastFollowUpDate; |
| 16 | use App\Models\TblProjectTypes; |
| 17 | use App\Models\TblQuotations; |
| 18 | use App\Models\TblSegmentG3wMapping; |
| 19 | use App\Models\TblSegments; |
| 20 | use App\Models\TblSourceG3wMapping; |
| 21 | use App\Models\TblSources; |
| 22 | use App\Models\TblStatusG3wMapping; |
| 23 | use App\Models\TblToAcceptanceNotifications; |
| 24 | use App\Models\TblTypeG3wMapping; |
| 25 | use App\Models\TblUserG3wMapping; |
| 26 | use App\Models\TblUsers; |
| 27 | use App\Models\TblVipClients; |
| 28 | use Google\Service\AdMob\Date; |
| 29 | use Illuminate\Contracts\Routing\ResponseFactory; |
| 30 | use Illuminate\Database\Eloquent\Model; |
| 31 | use Illuminate\Database\QueryException; |
| 32 | use Illuminate\Http\JsonResponse; |
| 33 | use Illuminate\Http\Response; |
| 34 | use Illuminate\Support\Carbon; |
| 35 | use Illuminate\Support\Facades\DB; |
| 36 | use Illuminate\Support\Facades\Log; |
| 37 | use Illuminate\Support\Facades\Storage; |
| 38 | use Mockery\Exception; |
| 39 | use SendGrid\Mail\Mail; |
| 40 | |
| 41 | class PresupuestosService extends GestionaService |
| 42 | { |
| 43 | public function __construct(private readonly Quotations $quotationsController, private readonly WorkService $workSevice) |
| 44 | { |
| 45 | parent::__construct(); |
| 46 | } |
| 47 | |
| 48 | // FIRE-1170: per-job context for bulk-sync. When non-null, `syncModifiedBudgetById` |
| 49 | // suppresses the approval email (when `suppressOps` is true) and clamps the |
| 50 | // recomputed `for_approval` to a max of 1 (when `suppressFin` is true). The |
| 51 | // contract is "set before calling the sync, reset in finally" — see |
| 52 | // `syncSpecificFieldsForId`. |
| 53 | public ?array $bulkSyncContext = null; |
| 54 | |
| 55 | // Sentry perf (G3W sync N+1): per-instance (one job / one request) memos |
| 56 | // for lookups that return the same answer for every budget in a run. |
| 57 | // Hits only — a miss re-queries, so placeholder-on-miss paths behave as |
| 58 | // before. Edits to these rows mid-run apply from the next job (~5 min). |
| 59 | private array $companyByRegion = []; |
| 60 | |
| 61 | private array $budgetStatusIdByNameCache = []; |
| 62 | |
| 63 | private array $statusMappingCache = []; |
| 64 | |
| 65 | private array $typeIdCache = []; |
| 66 | |
| 67 | private array $userMappingCache = []; |
| 68 | |
| 69 | private array $statusListByRegion = []; |
| 70 | |
| 71 | // FIRE-1170: columns that bulk-sync exposes as user-selectable. Anything not |
| 72 | // in this list (sync_import, *_by_g3w, for_add, has_attachment, etc.) is |
| 73 | // sync-managed and always written. Order does not matter; we use array_diff |
| 74 | // against the field-allowlist to compute the revert payload. |
| 75 | private const BULK_SYNC_USER_COLUMNS = [ |
| 76 | 'client', 'customer_type_id', 'segment_id', 'phone_number', 'email', |
| 77 | 'budget_type_id', 'budget_status_id', 'source_id', 'commercial', 'amount', |
| 78 | 'issue_date', 'request_date', 'acceptance_date', 'accepted_at', 'accepted_by', |
| 79 | 'last_follow_up_date', |
| 80 | 'cost_of_labor', 'total_cost_of_job', 'invoice_margin', 'margin_for_the_company', |
| 81 | 'revenue_per_date_per_worked', 'gross_margin', 'labor_percentage', |
| 82 | ]; |
| 83 | |
| 84 | // FIRE-1170: maps a UI checkbox name to the DB columns it controls. The |
| 85 | // `margins` checkbox is a bundle — picking it toggles all 7 margin columns |
| 86 | // together because they're computed from amount + project type and would |
| 87 | // produce inconsistent state if split. The `acceptance_date` checkbox also |
| 88 | // covers `accepted_at` + `accepted_by` because the three are derived from |
| 89 | // the same G3W payload field (`presupuesto.aceptacion`). |
| 90 | public const BULK_SYNC_FIELD_MAP = [ |
| 91 | 'client' => ['client'], |
| 92 | 'customer_type_id' => ['customer_type_id'], |
| 93 | 'segment_id' => ['segment_id'], |
| 94 | 'phone_number' => ['phone_number'], |
| 95 | 'email' => ['email'], |
| 96 | 'budget_type_id' => ['budget_type_id'], |
| 97 | 'budget_status_id' => ['budget_status_id'], |
| 98 | 'source_id' => ['source_id'], |
| 99 | 'commercial' => ['commercial'], |
| 100 | 'amount' => ['amount'], |
| 101 | 'issue_date' => ['issue_date'], |
| 102 | 'request_date' => ['request_date'], |
| 103 | 'acceptance_date' => ['acceptance_date', 'accepted_at', 'accepted_by'], |
| 104 | 'last_follow_up_date' => ['last_follow_up_date'], |
| 105 | 'margins' => [ |
| 106 | 'cost_of_labor', 'total_cost_of_job', 'invoice_margin', |
| 107 | 'margin_for_the_company', 'revenue_per_date_per_worked', |
| 108 | 'gross_margin', 'labor_percentage', |
| 109 | ], |
| 110 | ]; |
| 111 | |
| 112 | // FIRE-1001 + FIRE-83: per-region G3W `cod_material` allow-list for the |
| 113 | // labor classifier (`classifyLineAsLabor`). Lifted to a class constant so |
| 114 | // both `syncById` (INSERT) and `syncModifiedBudgetById` (UPDATE) read the |
| 115 | // same source; previously only the UPDATE path classified lines, leaving |
| 116 | // freshly-imported rows with `cost_of_labor=0` / no `estimated_cost_of_materials`. |
| 117 | private const MATERIAL_CODES_BY_REGION = [ |
| 118 | 'Cataluña' => [102561, 102562, 102568, 102569], |
| 119 | 'Madrid' => [562, 100026, 100027], |
| 120 | 'Comunidad Valenciana' => [562], |
| 121 | ]; |
| 122 | |
| 123 | /** |
| 124 | * FIRE-1024 (2026-04-30): G3W returns acceptance dates in a half-dozen |
| 125 | * empty representations — null, empty string, '0000-00-00', '0000/00/00', |
| 126 | * '0000-00-00 00:00:00', sometimes whitespace. Normalise all of them to |
| 127 | * PHP null so MySQL stores NULL, regardless of strict mode. |
| 128 | * |
| 129 | * Pre-fix the creation path at line 656 only caught two of these forms; |
| 130 | * the modification path at line 864 already used `empty()` + array but |
| 131 | * had its own logic bug (see FIRE-1024 investigation Bug 2). Centralise |
| 132 | * here so every site reads the same source of truth. |
| 133 | */ |
| 134 | private function normalizeG3wAcceptance(mixed $raw): ?string |
| 135 | { |
| 136 | if ($raw === null) { |
| 137 | return null; |
| 138 | } |
| 139 | if (! is_string($raw)) { |
| 140 | return null; |
| 141 | } |
| 142 | $trimmed = trim($raw); |
| 143 | if ($trimmed === '') { |
| 144 | return null; |
| 145 | } |
| 146 | if (in_array($trimmed, ['0000-00-00', '0000/00/00', '0000-00-00 00:00:00'], true)) { |
| 147 | return null; |
| 148 | } |
| 149 | |
| 150 | return $trimmed; |
| 151 | } |
| 152 | |
| 153 | /** |
| 154 | * Single source of truth for "which company does a G3W sync belong to". |
| 155 | * |
| 156 | * The company is derived from the region the sync was launched for and |
| 157 | * nothing else — no per-source, per-client or per-user overrides. A budget |
| 158 | * pulled for Madrid is filed under the Madrid company, full stop. |
| 159 | * |
| 160 | * FIRE-1476 merged Castilla-La Mancha into Grupo Fire Madrid, so both |
| 161 | * region labels resolve to company 18; every other region resolves through |
| 162 | * `tbl_companies.region`. The explicit company_id also keeps us off |
| 163 | * `.first()` row order when more than one row carries region='Madrid'. |
| 164 | * |
| 165 | * Historical note (FIRE-1571): a "Jomar" branch in `syncById` used to |
| 166 | * re-file Madrid budgets whose `cod_empresa_presupuesto` was 9 under |
| 167 | * company 22. After the CLM merge, 22 is the inactive Castilla-La Mancha |
| 168 | * company, so that override silently dropped Madrid budgets (e.g. G3W |
| 169 | * 22742) into a region the operator never selected. Deriving the company |
| 170 | * from the region only — here, once — is what prevents that class of bug. |
| 171 | */ |
| 172 | private function resolveCompanyForRegion(?string $region): ?TblCompanies |
| 173 | { |
| 174 | // normalizeRegion(), not a bare Catalunya check: the region can still |
| 175 | // be percent-encoded here when it came from a client header, and |
| 176 | // "Castell%C3%B3n" matches no company row. |
| 177 | $region = $this->normalizeRegion($region); |
| 178 | |
| 179 | if (isset($this->companyByRegion[$region])) { |
| 180 | return $this->companyByRegion[$region]; |
| 181 | } |
| 182 | |
| 183 | if (in_array($region, ['Madrid', 'Castilla-La Mancha'], true)) { |
| 184 | return $this->companyByRegion[$region] = TblCompanies::where('company_id', 18)->first(); |
| 185 | } |
| 186 | |
| 187 | return $this->companyByRegion[$region] = TblCompanies::where('region', $region)->first(); |
| 188 | } |
| 189 | |
| 190 | /** |
| 191 | * Synchronize budgets as of a specific date. |
| 192 | * It also manages the last synchronized ID. |
| 193 | * |
| 194 | * @param string $date Default today's date |
| 195 | * @param string $name Who's launch the function |
| 196 | */ |
| 197 | public function syncByDate($date = null, $name = null, $region = 'Cataluña'): array |
| 198 | { |
| 199 | try { |
| 200 | $region = $this->normalizeRegion($region); |
| 201 | |
| 202 | $company = $this->resolveCompanyForRegion($region); |
| 203 | |
| 204 | // These two conditions are reported separately on purpose. The |
| 205 | // `?->g3W_active` shorthand this replaces collapsed them into one |
| 206 | // message, so a region whose company row is missing, renamed or |
| 207 | // re-normalised was indistinguishable from one deliberately |
| 208 | // switched off — and the deliberate case is treated downstream as a |
| 209 | // benign skip, logged at warning and not reported. A genuinely |
| 210 | // broken region was therefore being filed as "working as intended". |
| 211 | // syncByIds already makes this split; syncByDate now matches it. |
| 212 | if (! $company) { |
| 213 | $startCronDateTime = date('Y-m-d H:i:s'); |
| 214 | $this->updateLogs([['id' => 0, 'error' => 'No se encontró la compañía para la región '.$region.'.']], 0, [], $startCronDateTime, 'System', $region); |
| 215 | throw new Exception("No se encontró la compañía para la región '$region'."); |
| 216 | } |
| 217 | |
| 218 | if (! $company->g3W_active) { |
| 219 | $startCronDateTime = date('Y-m-d H:i:s'); |
| 220 | $this->updateLogs([['id' => 0, 'error' => 'La sincronización esta desactivada en la region '.$region.'.']], 0, [], $startCronDateTime, 'System', $region); |
| 221 | throw new Exception("La sincronización con G3W debe estar desactivada en la region '$region'."); |
| 222 | } |
| 223 | |
| 224 | $this->setSyncStatus(1, $region); |
| 225 | $this->syncExistingDataWithWarnings(); |
| 226 | |
| 227 | $successfulSyncs = 0; |
| 228 | $failedSyncs = []; |
| 229 | $successIdSyncs = []; |
| 230 | |
| 231 | $startCronDateTime = date('Y-m-d H:i:s'); |
| 232 | // Captured before talking to G3W: anything modified after this |
| 233 | // instant is left for the next run to pick up. |
| 234 | $runStartedAt = now(); |
| 235 | |
| 236 | $date ??= date('Y-m-d'); |
| 237 | |
| 238 | $budgets = $this->request('get', 'presupuesto?fecha='.$date, $region, []); |
| 239 | |
| 240 | if (is_string($budgets)) { |
| 241 | $budgets = json_decode($budgets, true); |
| 242 | } |
| 243 | |
| 244 | // No NEW budgets today is normal (weekends, early morning) and |
| 245 | // must not end the run: the modified-budgets step below still |
| 246 | // has to run. Before, an empty list returned here, so on a Sunday |
| 247 | // with nothing created no acceptance in G3W ever reached Titan. |
| 248 | if (! is_array($budgets)) { |
| 249 | $budgets = []; |
| 250 | } |
| 251 | |
| 252 | $company = $this->resolveCompanyForRegion($region); |
| 253 | |
| 254 | // Sentry perf (G3W sync N+1): almost every listed budget already |
| 255 | // exists on each 5-minute run, so fetch those ids in one whereIn |
| 256 | // instead of one EXISTS per budget. A prefetch hit skips; a miss |
| 257 | // still runs the original EXISTS — so a budget created earlier in |
| 258 | // this loop, or an id MySQL would coerce differently than PHP, |
| 259 | // is handled exactly as before. |
| 260 | $listedIds = []; |
| 261 | foreach ($budgets as $budget) { |
| 262 | if (is_array($budget) && isset($budget['ID'])) { |
| 263 | $listedIds[] = $budget['ID']; |
| 264 | } |
| 265 | } |
| 266 | $existingIds = []; |
| 267 | foreach (array_chunk(array_values(array_unique($listedIds)), 1000) as $chunk) { |
| 268 | foreach (TblQuotations::where('company_id', $company->company_id) |
| 269 | ->whereIn('internal_quote_id', $chunk) |
| 270 | ->pluck('internal_quote_id') as $existingId) { |
| 271 | $existingIds[(string) $existingId] = true; |
| 272 | } |
| 273 | } |
| 274 | |
| 275 | foreach ($budgets as $budget) { |
| 276 | if (! is_array($budget) || ! isset($budget['ID'])) { |
| 277 | continue; |
| 278 | } |
| 279 | |
| 280 | if ( |
| 281 | isset($existingIds[(string) $budget['ID']]) |
| 282 | || TblQuotations::where('internal_quote_id', $budget['ID']) |
| 283 | ->where('company_id', $company->company_id) |
| 284 | ->exists() |
| 285 | ) { |
| 286 | continue; |
| 287 | } |
| 288 | |
| 289 | $result = DB::transaction(fn () => $this->syncById($budget['ID'], $region)); |
| 290 | |
| 291 | if ($result['success']) { |
| 292 | $successfulSyncs++; |
| 293 | $successIdSyncs[] = [ |
| 294 | 'id' => $budget['ID'], |
| 295 | ]; |
| 296 | } else { |
| 297 | if (! str_contains((string) $result['error'], 'No se ha encontrado el presupuesto')) { |
| 298 | $failedSyncs[] = [ |
| 299 | 'id' => $budget['ID'], |
| 300 | 'error' => $result['error'] ?? 'Unknown error', |
| 301 | ]; |
| 302 | } |
| 303 | } |
| 304 | } |
| 305 | |
| 306 | $ids = array_filter(array_column(array_filter($budgets, is_array(...)), 'ID'), is_numeric(...)); |
| 307 | $this->markG3wImport($region, 0, $ids === [] ? null : (int) max($ids)); |
| 308 | |
| 309 | // Modified budgets: ask from where the last completed run left |
| 310 | // off, day by day up to today. The old window was "today, since |
| 311 | // one hour ago", which dropped anything changed earlier than the |
| 312 | // previous hour, and anything changed on a previous day. |
| 313 | $isLiveRun = $date === $runStartedAt->toDateString(); |
| 314 | $windows = self::modifiedBudgetWindows( |
| 315 | $isLiveRun ? $this->lastUpdateRow($region)?->modified_synced_until : null, |
| 316 | $date, |
| 317 | $runStartedAt, |
| 318 | ); |
| 319 | |
| 320 | $modifiedBudgets = []; |
| 321 | foreach ($windows as [$windowDate, $windowTime]) { |
| 322 | $endpoint = 'presupuesto/fechamodificacion/'.$windowDate.($windowTime !== null ? '/'.$windowTime : ''); |
| 323 | $page = $this->request('get', $endpoint, $region, []); |
| 324 | if (is_array($page)) { |
| 325 | $modifiedBudgets = array_merge($modifiedBudgets, $page); |
| 326 | } |
| 327 | } |
| 328 | |
| 329 | if (! empty($modifiedBudgets)) { |
| 330 | // Gestiona can list the same budget several times (once per |
| 331 | // modification). Sync each one once per run, or every entry |
| 332 | // re-fetches and rewrites the same quote seconds apart. |
| 333 | $modifiedBudgets = self::uniqueBudgetsById($modifiedBudgets); |
| 334 | |
| 335 | foreach ($modifiedBudgets as $budget) { |
| 336 | // FIRE-1029: catch QueryException at the loop level so |
| 337 | // a single deadlock victim rolls back its own |
| 338 | // transaction (now that syncModifiedBudgetById rethrows |
| 339 | // DB faults) without aborting the whole sync run. |
| 340 | try { |
| 341 | $result = DB::transaction(fn () => $this->syncModifiedBudgetById($budget['ID'], $region)); |
| 342 | } catch (QueryException $qe) { |
| 343 | $result = ['success' => false, 'error' => "Error actualizando el presupuesto {$budget['ID']}: ".$qe->getMessage()]; |
| 344 | } |
| 345 | |
| 346 | if ($result['success']) { |
| 347 | $successfulSyncs++; |
| 348 | $successIdSyncs[] = [ |
| 349 | 'id' => $budget['ID'], |
| 350 | ]; |
| 351 | } else { |
| 352 | if ($result['error']) { |
| 353 | if (! str_contains((string) $result['error'], 'No se encuentra el presupuesto con ID en G3W') && |
| 354 | ! str_contains((string) $result['error'], 'No se ha encontrado el presupuesto')) { |
| 355 | $failedSyncs[] = [ |
| 356 | 'id' => $budget['ID'], |
| 357 | 'error' => $result['error'] ?? 'Unknown error', |
| 358 | ]; |
| 359 | } |
| 360 | } |
| 361 | } |
| 362 | } |
| 363 | } |
| 364 | |
| 365 | // FIRE-1025: zero-date sweep moved to the daily |
| 366 | // `quotations:cleanup-zero-dates` artisan command. Pre-fix this |
| 367 | // UPDATE ran at the end of every G3W sync cycle (5 call sites |
| 368 | // here) and deadlocked on `idx_acceptance_date` when concurrent |
| 369 | // syncs piled up (see InnoDB MONITOR OUTPUT on 2026-04-27 with |
| 370 | // multiple "WE ROLL BACK TRANSACTION (1)" entries on this exact |
| 371 | // statement). FIRE-1001 normalises `aceptacion` on write so no |
| 372 | // new zero-dates can be created — the daily cleanup handles |
| 373 | // legacy rows. |
| 374 | |
| 375 | // The modified pass completed (a G3W error throws before here), so |
| 376 | // the next live run can start from this run's start time. Budgets |
| 377 | // that failed individually are in $failedSyncs for the retry job. |
| 378 | // Catch-up runs for a past date don't move the cursor. |
| 379 | if ($isLiveRun) { |
| 380 | DB::table('tbl_g3w_last_update') |
| 381 | ->where('region', $this->normalizeRegion($region)) |
| 382 | ->update(['modified_synced_until' => $runStartedAt->format('Y-m-d H:i:s')]); |
| 383 | } |
| 384 | |
| 385 | // Re-stamp after the modified-budgets pass so the footer shows when |
| 386 | // the cycle *finished*, not when the new-budget loop ended. |
| 387 | $this->markG3wImport($region, 0); |
| 388 | |
| 389 | $this->updateLogs($failedSyncs, $successfulSyncs, $successIdSyncs, $startCronDateTime, $name, $region); |
| 390 | |
| 391 | // FIRE-1XXX: Refresh per-commercial counters after the batch sync. |
| 392 | // Manual acceptances trigger this via Quotations::update_quotation, |
| 393 | // but G3W-driven acceptances bypass that path — without this call, |
| 394 | // dashboards lag until the next manual touch on each commercial. |
| 395 | // Called once per region (per syncByDate run), not per row. |
| 396 | try { |
| 397 | $this->quotationsController->update_commercial_numbers($company->company_id); |
| 398 | } catch (\Throwable $countException) { |
| 399 | Log::channel('allInfoQuotationsG3w')->error( |
| 400 | 'G3W sync: update_commercial_numbers failed for company '. |
| 401 | $company->company_id.': '.$countException->getMessage() |
| 402 | ); |
| 403 | } |
| 404 | |
| 405 | // FIRE-1145 follow-up: the sync just rewrote tbl_quotations rows. |
| 406 | // Without this, list_quotations + analytics caches stay stale for |
| 407 | // up to the 10-min TTL — commercials don't see freshly-synced data. |
| 408 | ResultCache::forgetDomain('quotations'); |
| 409 | |
| 410 | return [ |
| 411 | 'success' => true, |
| 412 | 'message' => 'Synchronization completed.', |
| 413 | ]; |
| 414 | |
| 415 | } catch (\Exception $e) { |
| 416 | Log::channel('g3w')->error('Error sincronizando los presupuestos: '.$e->getMessage()); |
| 417 | |
| 418 | $this->releaseSyncFlag($region); |
| 419 | |
| 420 | $startCronDateTime = date('Y-m-d H:i:s'); |
| 421 | $this->updateLogs([['id' => 0, 'error' => $e->getMessage()]], 0, [], $startCronDateTime, 'System', $region); |
| 422 | |
| 423 | return ['success' => false, 'error' => $e->getMessage()]; |
| 424 | } |
| 425 | } |
| 426 | |
| 427 | /** |
| 428 | * FIRE-976: Fetch a budget from Gestiona and return normalized status + amount |
| 429 | * without creating/updating anything in Titan. |
| 430 | * |
| 431 | * @return array{success: bool, data?: array, error?: string} |
| 432 | */ |
| 433 | public function fetchFromGestiona($id, string $region): array |
| 434 | { |
| 435 | try { |
| 436 | $budget = $this->request('get', "presupuesto/{$id}", $region, []); |
| 437 | |
| 438 | if (! isset($budget['presupuesto']) || ! is_array($budget['presupuesto'])) { |
| 439 | return ['success' => false, 'error' => 'No se ha encontrado el presupuesto en Gestiona']; |
| 440 | } |
| 441 | |
| 442 | $presupuesto = $budget['presupuesto']; |
| 443 | |
| 444 | // Resolve status name from Gestiona status list |
| 445 | $statusList = $this->statusListByRegion[$region] ??= $this->request('get', 'presupuesto/tiposestado', $region, []); |
| 446 | $nameStatus = collect($statusList)->firstWhere('ID', $presupuesto['estado'])['nombre'] ?? null; |
| 447 | |
| 448 | // Normalize to FST status |
| 449 | if (! $nameStatus) { |
| 450 | $statusId = $this->budgetStatusIdByName('Sin estado en G3W'); |
| 451 | } else { |
| 452 | $statusId = $this->normalizeStatus($nameStatus); |
| 453 | if ($statusId === $this->budgetStatusIdByName('Sin estado en G3W')) { |
| 454 | $statusId = $this->budgetStatusIdByName('Estado no reconocido en FST'); |
| 455 | } |
| 456 | } |
| 457 | |
| 458 | $statusName = TblBudgetStatus::where('budget_status_id', $statusId)->value('name'); |
| 459 | |
| 460 | return [ |
| 461 | 'success' => true, |
| 462 | 'data' => [ |
| 463 | 'internal_quote_id' => $presupuesto['cod_presupuesto'], |
| 464 | 'amount' => $presupuesto['importe'] ?? null, |
| 465 | 'budget_status_id' => $statusId, |
| 466 | 'budget_status' => $statusName, |
| 467 | ], |
| 468 | ]; |
| 469 | } catch (\Exception $e) { |
| 470 | return ['success' => false, 'error' => $e->getMessage()]; |
| 471 | } |
| 472 | } |
| 473 | |
| 474 | /** |
| 475 | * Synchronizes a budget by its ID. |
| 476 | * |
| 477 | * @param $id int ID to search in G3W |
| 478 | */ |
| 479 | public function syncById($id, $region): array |
| 480 | { |
| 481 | try { |
| 482 | // Region is stored platform-wide as "Cataluña" (see |
| 483 | // QuotationsSync::DEFAULT_REGIONS + tbl_companies.region). Normalise |
| 484 | // Catalunya, percent-encoded and shell-quoted callers alike so the |
| 485 | // region-scoped mapping lookups below match. |
| 486 | $region = $this->normalizeRegion($region); |
| 487 | |
| 488 | Log::channel('allInfoQuotationsG3w')->info("Sincronizando presupuesto {$id}"); |
| 489 | $g3wWarning = 0; |
| 490 | $g3wWarningFields = null; |
| 491 | $budget = $this->request('get', "presupuesto/{$id}", $region, []); |
| 492 | |
| 493 | if (! isset($budget['presupuesto']) || ! is_array($budget['presupuesto'])) { |
| 494 | Log::channel('allInfoQuotationsG3w')->info('El presupuesto no contiene los datos esperados.'); |
| 495 | throw new \Exception('El presupuesto no contiene los datos esperados.'); |
| 496 | } |
| 497 | |
| 498 | // The company comes from the selected region and is never |
| 499 | // reassigned below — see resolveCompanyForRegion(). |
| 500 | $companyId = $this->resolveCompanyForRegion($region)?->company_id; |
| 501 | |
| 502 | Log::channel('allInfoQuotationsG3w')->info("Región: {$region} → company_id: {$companyId}"); |
| 503 | |
| 504 | $existsQuery = TblQuotations::where('internal_quote_id', $budget['presupuesto']['cod_presupuesto']) |
| 505 | ->where('company_id', $companyId); |
| 506 | if ($existsQuery->exists()) { |
| 507 | Log::channel('allInfoQuotationsG3w')->info('El presupuesto ya existe, procedemos a modificarlo.'); |
| 508 | $resultEdit = $this->syncModifiedBudgetById($id, $region); |
| 509 | |
| 510 | return $resultEdit; |
| 511 | Log::channel('allInfoQuotationsG3w')->info('El presupuesto ya existe, procedemos a modificarlo.'); |
| 512 | |
| 513 | return $this->syncModifiedBudgetById($id, $region); |
| 514 | } |
| 515 | |
| 516 | $statusList = $this->statusListByRegion[$region] ??= $this->request('get', 'presupuesto/tiposestado', $region, []); |
| 517 | |
| 518 | $collection = collect($statusList); |
| 519 | |
| 520 | Log::channel('allInfoQuotationsG3w')->info('Estado del presupuesto: '.$budget['presupuesto']['estado']); |
| 521 | |
| 522 | $nameStatus = $collection->firstWhere('ID', $budget['presupuesto']['estado'])['nombre'] ?? null; |
| 523 | |
| 524 | Log::channel('allInfoQuotationsG3w')->info('Estado del presupuesto en FST: '.$nameStatus); |
| 525 | |
| 526 | if (! $nameStatus) { |
| 527 | $statusID = $this->budgetStatusIdByName('Sin estado en G3W'); |
| 528 | } else { |
| 529 | $statusID = $this->normalizeStatus($nameStatus); |
| 530 | |
| 531 | if ($statusID === $this->budgetStatusIdByName('Sin estado en G3W')) { |
| 532 | $statusID = $this->budgetStatusIdByName('Estado no reconocido en FST'); |
| 533 | } |
| 534 | } |
| 535 | |
| 536 | Log::channel('allInfoQuotationsG3w')->info('ID del estado del presupuesto en FST: '.$statusID); |
| 537 | |
| 538 | $company = $this->request('get', "servicio/{$budget['presupuesto']['cod_servicio']}", $region, []); |
| 539 | |
| 540 | if (! $company || (! isset($company['servicio']) && ! isset($company['cliente']))) { |
| 541 | $company = $this->request('get', "cliente/{$budget['presupuesto']['cod_cliente']}", $region, []); |
| 542 | } |
| 543 | |
| 544 | $companyName = $company['servicio']['nombre_servicio'] ?? $company['cliente']['empresa'] ?? null; |
| 545 | $companyTelephone = $company['servicio']['telefono'] ?? $company['cliente']['telefono'] ?? null; |
| 546 | $companyEmail = $company['servicio']['email'] ?? $company['cliente']['email'] ?? null; |
| 547 | |
| 548 | Log::channel('allInfoQuotationsG3w')->info('Nombre de la empresa: '.$companyName); |
| 549 | |
| 550 | if ($companyEmail) { |
| 551 | // Gestiona returns multiple addresses joined by ";" (and |
| 552 | // sometimes a trailing one with nothing after it). Normalize |
| 553 | // to comma-separated AND drop empty fragments — a trailing |
| 554 | // ";" was leaving us with "foo@bar.com," which downstream |
| 555 | // explode(',', email) turned into an empty slot, flagging |
| 556 | // the quote in "Emails con error" with no real bounce. |
| 557 | $parts = preg_split('/\s*[;,]\s*/', (string) $companyEmail, -1, PREG_SPLIT_NO_EMPTY); |
| 558 | $companyEmail = $parts ? implode(',', $parts) : null; |
| 559 | } |
| 560 | |
| 561 | Log::channel('allInfoQuotationsG3w')->info('Email de la empresa: '.$companyEmail); |
| 562 | |
| 563 | // FIRE-1476: cod_cliente 9818 override — only Madrid (18) after merge. |
| 564 | if (! $companyName || ($budget['presupuesto']['cod_cliente'] === 9818 && $companyId === 18)) { |
| 565 | $companyName = $this->getAlternativeClientData($budget['presupuesto']['datos_cliente_alternativo'])['name']; |
| 566 | } |
| 567 | |
| 568 | if (! $companyTelephone || ($budget['presupuesto']['cod_cliente'] === 9818 && $companyId === 18)) { |
| 569 | $companyTelephone = $this->getAlternativeClientData($budget['presupuesto']['datos_cliente_alternativo'])['number']; |
| 570 | } |
| 571 | |
| 572 | if (! $companyEmail || ($budget['presupuesto']['cod_cliente'] === 9818 && $companyId === 18)) { |
| 573 | $companyEmail = $this->getAlternativeClientData($budget['presupuesto']['datos_cliente_alternativo'])['email']; |
| 574 | } |
| 575 | |
| 576 | if ($statusID === 2 && $this->checkEmailInvalid((string) $companyEmail)) { |
| 577 | Log::channel('allInfoQuotationsG3w')->info('FIRE-864: G3W "Enviado" en creación con email inválido ('.$companyEmail.') → Correo erroneo (22).'); |
| 578 | $statusID = 22; |
| 579 | } |
| 580 | |
| 581 | Log::channel('allInfoQuotationsG3w')->info('Nombre de la empresa tras alternative client data: '.$companyName); |
| 582 | |
| 583 | $segmentID = $this->normalizeSegment($company['servicio']['tipo_servicio'] ?? 'Otro'); |
| 584 | |
| 585 | Log::channel('allInfoQuotationsG3w')->info('Segmento de la empresa: '.$segmentID); |
| 586 | |
| 587 | if (! isset($budget['presupuesto']['documento']) || empty($budget['presupuesto']['documento'])) { |
| 588 | Log::channel('allInfoQuotationsG3w')->info('El presupuesto no tiene documento asociado.'); |
| 589 | throw new \Exception('El presupuesto no tiene documento asociado. Creelo y vuelva a intentarlo.'); |
| 590 | } |
| 591 | |
| 592 | $companyNameFormatted = ''; |
| 593 | |
| 594 | if (! empty($companyName)) { |
| 595 | $companyNameFormatted = str_replace(' ', '_', $companyName); |
| 596 | } |
| 597 | |
| 598 | $nameDocument = $companyNameFormatted |
| 599 | ? $budget['presupuesto']['cod_presupuesto'].'_'.$companyNameFormatted |
| 600 | : $budget['presupuesto']['cod_presupuesto']; |
| 601 | |
| 602 | Log::channel('allInfoQuotationsG3w')->info('Nombre del documento: '.$nameDocument); |
| 603 | |
| 604 | $typeId = $this->normalizeType($budget['presupuesto']['origen_presupuesto'] ?? null, $region); |
| 605 | |
| 606 | Log::channel('allInfoQuotationsG3w')->info('Tipo de presupuesto: '.$typeId); |
| 607 | |
| 608 | if (empty($budget['presupuesto']['fecha_creacion'])) { |
| 609 | $budget['presupuesto']['fecha_creacion'] = Carbon::now()->format('Y-m-d H:i:s'); |
| 610 | } |
| 611 | |
| 612 | $work = $this->request('get', "presupuesto/trabajos/{$id}", $region, []); |
| 613 | |
| 614 | $workIds = []; |
| 615 | |
| 616 | if (is_array($work)) { |
| 617 | foreach ($work as $item) { |
| 618 | if (isset($item['ID'])) { |
| 619 | $workIds[] = $item['ID']; |
| 620 | } |
| 621 | } |
| 622 | } |
| 623 | |
| 624 | $idsConcatenados = implode('/', $workIds); |
| 625 | |
| 626 | Log::channel('allInfoQuotationsG3w')->info('Trabajos: '.$idsConcatenados); |
| 627 | |
| 628 | $createdByUser = $this->resolveUserMapping($budget['presupuesto']['usuario'] ?? null, $region); |
| 629 | $createdBy = null; |
| 630 | $comercial = null; |
| 631 | |
| 632 | if (! $createdByUser) { |
| 633 | TblUserG3wMapping::create([ |
| 634 | 'name_g3w' => $budget['presupuesto']['usuario'] ?? null, |
| 635 | 'region' => $region, |
| 636 | ]); |
| 637 | // FIRE-1110: do NOT store the raw G3W username as the |
| 638 | // commercial. The frontend ng-select can't bind to a |
| 639 | // value that isn't in userNamesList, so the form control |
| 640 | // resolves to JS `undefined`, and a save round-trip |
| 641 | // corrupts the row to the literal string "undefined". |
| 642 | // Leaving commercial NULL keeps it clean — the order |
| 643 | // form falls back to user_create_by_g3w / user_commercial_by_g3w |
| 644 | // for display, and saves don't overwrite an already-NULL |
| 645 | // commercial with a broken value. The raw G3W username is |
| 646 | // preserved in user_create_by_g3w / user_commercial_by_g3w |
| 647 | // for traceability and for the future mapping that resolves it. |
| 648 | $g3wWarning = 1; |
| 649 | } else { |
| 650 | $createdBy = TblUsers::where('id', $createdByUser->id_fst)->value('name') |
| 651 | ?? null; |
| 652 | |
| 653 | // Wrong-commercial-on-sync fix: do NOT default the commercial to |
| 654 | // the G3W creator (`usuario`). The creator is usually a |
| 655 | // back-office account, not the salesperson. The commercial is |
| 656 | // resolved ONLY from `cod_comercial_presupuesto` below; when that |
| 657 | // is absent/unmapped we leave it NULL and flag the row |
| 658 | // (checkRequiredFields adds 'Comercial'), mirroring the FIRE-1110 |
| 659 | // treatment of an unmapped creator. |
| 660 | } |
| 661 | |
| 662 | Log::channel('allInfoQuotationsG3w')->info('Comercial: '.$comercial); |
| 663 | |
| 664 | $codComercial = $budget['presupuesto']['cod_comercial_presupuesto'] ?? null; |
| 665 | |
| 666 | // A single G3W code can have multiple mapping rows within a region |
| 667 | // — a placeholder (id_fst NULL, created by an earlier sync) AND a |
| 668 | // real match added later once a human linked it. A plain ->first() |
| 669 | // can return the placeholder and silently skip the resolvable row, |
| 670 | // so resolveUserMapping() prefers a mapping that has an id_fst |
| 671 | // (newest wins if more than one), scoped to this region. |
| 672 | $comercialUser = $this->resolveUserMapping($codComercial, $region, true); |
| 673 | |
| 674 | if ($comercialUser) { |
| 675 | // Resolve the commercial from the cod_comercial_presupuesto |
| 676 | // mapping, but ONLY when it points to a real FST user. A |
| 677 | // placeholder mapping (id_fst IS NULL — created by a previous |
| 678 | // sync, not yet matched by a human) leaves $comercial NULL so |
| 679 | // the row is flagged for manual assignment instead of guessing. |
| 680 | $resolvedCommercial = TblUsers::where('id', $comercialUser->id_fst)->value('name'); |
| 681 | if ($resolvedCommercial !== null) { |
| 682 | $comercial = $resolvedCommercial; |
| 683 | } |
| 684 | } elseif ($codComercial && ! TblUserG3wMapping::where('name_g3w', $codComercial)->where('region', $region)->exists()) { |
| 685 | // No mapping at all yet in this region — create a placeholder so |
| 686 | // a human can match it later. $comercial stays NULL and the row |
| 687 | // gets flagged by checkRequiredFields() below. |
| 688 | TblUserG3wMapping::create(['name_g3w' => $codComercial, 'region' => $region]); |
| 689 | } |
| 690 | |
| 691 | // FIRE-1559: si el sync no pudo resolver un comercial pero SÍ |
| 692 | // resolvió al creador a un FST user real, el creador actúa como |
| 693 | // comercial. Es seguro porque $createdBy ya viene del canónico |
| 694 | // TblUsers::value('name') — nunca el string G3W crudo (que fue |
| 695 | // el bug de FIRE-1110). El corrupt-on-save de ng-select no se |
| 696 | // puede reproducir porque el valor existe en userNamesList. |
| 697 | // |
| 698 | // Solo aplica en el INSERT path (syncById). El UPDATE path |
| 699 | // (syncModifiedBudgetById) se deja intacto por decisión explícita |
| 700 | // de Berta/Ricardo: re-syncs desde G3W no deben tocar commercial |
| 701 | // ni created_by una vez la fila existe en Titan. |
| 702 | if ($comercial === null && $createdBy !== null) { |
| 703 | $comercial = $createdBy; |
| 704 | Log::channel('allInfoQuotationsG3w')->info( |
| 705 | "FIRE-1559 fallback: comercial vacío en G3W → asignado desde creador normalizado: {$comercial}" |
| 706 | ); |
| 707 | } |
| 708 | |
| 709 | Log::channel('allInfoQuotationsG3w')->info('Comercial limpio: '.$comercial); |
| 710 | |
| 711 | $source = $budget['presupuesto']['cod_empresa_presupuesto'] ?? null; |
| 712 | $sourceId = ($source) ? $this->normalizeSource($source, $region) : null; |
| 713 | |
| 714 | // Per-user source overrides: certain G3W users aren't real |
| 715 | // commercials but mark the budget's source. "dig" routes to |
| 716 | // Diggening (source 73) and "flu" routes to Fluency Labs |
| 717 | // (source 72). The check runs after normalizeSource() so it |
| 718 | // wins over whatever cod_empresa_presupuesto resolved to. |
| 719 | $usuario = strtolower(trim((string) ($budget['presupuesto']['usuario'] ?? ''))); |
| 720 | if ($usuario === 'dig') { |
| 721 | $sourceId = 73; |
| 722 | } elseif ($usuario === 'flu') { |
| 723 | $sourceId = 72; |
| 724 | } |
| 725 | |
| 726 | Log::channel('allInfoQuotationsG3w')->info('Source: '.$source.' (sourceId='.$sourceId.', usuario='.$usuario.')'); |
| 727 | |
| 728 | if ($statusID == 12) { |
| 729 | $companyLimit = TblCompanies::where('company_id', $companyId)->first(); |
| 730 | $inProgressCount = TblQuotations::where('budget_status_id', 12)->where('company_id', $companyId)->where('commercial', $comercial)->count(); |
| 731 | if ($companyLimit->process_limit <= $inProgressCount) { |
| 732 | Log::channel('allInfoQuotationsG3w')->info("Se ha alcanzado el número máximo de pedidos en curso ({$companyLimit->process_limit}) permitido por usuario para esta empresa."); |
| 733 | throw new \Exception("No se pudo guardar el documento del presupuesto {$id} al crearlo. Error: Se ha alcanzado el número máximo de pedidos en curso (5) permitido por usuario para esta empresa."); |
| 734 | } |
| 735 | } |
| 736 | |
| 737 | // FIRE-1571: the "Jomar" override (source 9 → company 22) is gone. |
| 738 | // The company stays whatever the region resolved to; a Madrid sync |
| 739 | // is filed under Madrid no matter what cod_empresa_presupuesto says. |
| 740 | // `source_id` still records the origin, so Jomar budgets remain |
| 741 | // identifiable without moving them to another company/region. |
| 742 | |
| 743 | $lastFollowUp = TblLastFollowUpDate::where('company_id', $companyId)->where('budget_type_id', $typeId)->first(); |
| 744 | $workingDays = 10; |
| 745 | if ($lastFollowUp && $lastFollowUp->last_follow_up_date) { |
| 746 | $workingDays = $lastFollowUp->last_follow_up_date; |
| 747 | } |
| 748 | |
| 749 | $date = Carbon::now(); |
| 750 | |
| 751 | $daysAdded = 0; |
| 752 | while ($daysAdded < $workingDays) { |
| 753 | $date->addDay(); |
| 754 | if ($date->isWeekday()) { |
| 755 | $daysAdded++; |
| 756 | } |
| 757 | } |
| 758 | |
| 759 | $lastFollowUpDate = $date; |
| 760 | |
| 761 | // FIRE-1278: email problems must NOT flag a budget as a G3W |
| 762 | // warning. A budget whose only issue is the email belongs in |
| 763 | // "Emails con error" (routed to the Correo erroneo bucket below), |
| 764 | // not "Fallos en G3W". Only non-email required-field problems |
| 765 | // raise the G3W warning. |
| 766 | if ( |
| 767 | in_array($statusID, [13, 14]) || |
| 768 | ! $sourceId || |
| 769 | (! $typeId || $typeId == 0 || $typeId == 16) || |
| 770 | empty(trim((string) $companyName)) |
| 771 | ) { |
| 772 | $g3wWarning = 1; |
| 773 | } |
| 774 | |
| 775 | $row = (object) [ |
| 776 | 'client' => $companyName, |
| 777 | 'email' => $companyEmail, |
| 778 | 'budget_status_id' => $statusID, |
| 779 | 'commercial' => $comercial, |
| 780 | 'source_id' => $sourceId, |
| 781 | 'budget_type_id' => $typeId, |
| 782 | 'amount' => $budget['presupuesto']['importe'] ?? null, |
| 783 | ]; |
| 784 | |
| 785 | $g3wWarningFields = $this->checkRequiredFields($row); |
| 786 | |
| 787 | if ($this->hasNonEmailWarning($g3wWarningFields)) { |
| 788 | $g3wWarning = 1; |
| 789 | } |
| 790 | |
| 791 | if ($g3wWarningFields !== null && str_contains($g3wWarningFields, 'Email')) { |
| 792 | $statusID = 22; |
| 793 | } |
| 794 | |
| 795 | Log::channel('allInfoQuotationsG3w')->info('G3W warning: '.$g3wWarning); |
| 796 | |
| 797 | $reasonForNotFollowingUp = null; |
| 798 | |
| 799 | // iker chacori and juan carlos |
| 800 | $excludingUsers = ['igc', '030', '031', 'fcl', '018', '021', 'jcsp', '026', '029', 'EXMOF', 'MGF']; |
| 801 | |
| 802 | $facilityUser = false; |
| 803 | |
| 804 | if ( |
| 805 | (in_array($budget['presupuesto']['usuario'], $excludingUsers) || in_array($budget['presupuesto']['cod_comercial_presupuesto'], $excludingUsers)) |
| 806 | && $region == 'Cataluña' |
| 807 | ) { |
| 808 | $reasonForNotFollowingUp = 1; |
| 809 | $facilityUser = true; |
| 810 | } |
| 811 | |
| 812 | if ($segmentID == 3) { |
| 813 | $reasonForNotFollowingUp = 2; |
| 814 | } elseif ($segmentID == 2) { |
| 815 | $reasonForNotFollowingUp = 1; |
| 816 | } |
| 817 | |
| 818 | $client = $this->request('get', "cliente/{$budget['presupuesto']['cod_cliente']}", $region, []); |
| 819 | |
| 820 | // Guard against missing `cliente` key — G3W sometimes returns a |
| 821 | // response without it (empty cod_cliente, deleted client, or |
| 822 | // unexpected error shape). Pre-fix this threw |
| 823 | // "Undefined array key 'cliente'" and the whole sync 500'd. |
| 824 | $tipoCliente = $client['cliente']['tipo_cliente'] ?? null; |
| 825 | $clienteCif = $client['cliente']['cliente_cif'] ?? null; |
| 826 | |
| 827 | if ( |
| 828 | $tipoCliente === 'Grandes Cuentas' || |
| 829 | $tipoCliente === 'Grandes Clientes' || |
| 830 | ($clienteCif !== null && TblInvoicesExceptions::where('cif', $clienteCif)->exists()) |
| 831 | ) { |
| 832 | $reasonForNotFollowingUp = 2; |
| 833 | } |
| 834 | |
| 835 | $isVip = false; |
| 836 | |
| 837 | $cData = $client['cliente'] ?? []; |
| 838 | $isVip = TblVipClients::whereIn('company_id', [$companyId, 0]) |
| 839 | ->where(function ($query) use ($cData) { |
| 840 | $query->whereRaw('1 = 0'); |
| 841 | if (! empty($cData['cod_cliente'])) { |
| 842 | $query->orWhere('id_client', $cData['cod_cliente']); |
| 843 | } |
| 844 | if (! empty($cData['empresa'])) { |
| 845 | $query->orWhere('name', $cData['empresa']); |
| 846 | } |
| 847 | if (! empty($cData['cliente_cif'])) { |
| 848 | $query->orWhere('cif', $cData['cliente_cif']); |
| 849 | } |
| 850 | if (! empty($cData['email'])) { |
| 851 | $query->orWhere('email', $cData['email']); |
| 852 | } |
| 853 | if (! empty($cData['telefono'])) { |
| 854 | $query->orWhere('phone', $cData['telefono']); |
| 855 | } |
| 856 | }) |
| 857 | ->exists(); |
| 858 | |
| 859 | if (! $isVip) { |
| 860 | $sData = $company['servicio'] ?? []; |
| 861 | $isVip = TblVipClients::whereIn('company_id', [$companyId, 0]) |
| 862 | ->where(function ($query) use ($sData) { |
| 863 | $query->whereRaw('1 = 0'); |
| 864 | if (! empty($sData['cod_servicio'])) { |
| 865 | $query->orWhere('id_client', $sData['cod_servicio']); |
| 866 | } |
| 867 | if (! empty($sData['nombre_servicio'])) { |
| 868 | $query->orWhere('name', $sData['nombre_servicio']); |
| 869 | } |
| 870 | if (! empty($sData['servicio_cif'])) { |
| 871 | $query->orWhere('cif', $sData['servicio_cif']); |
| 872 | } |
| 873 | if (! empty($sData['email'])) { |
| 874 | $query->orWhere('email', $sData['email']); |
| 875 | } |
| 876 | if (! empty($sData['telefono'])) { |
| 877 | $query->orWhere('phone', $sData['telefono']); |
| 878 | } |
| 879 | }) |
| 880 | ->exists(); |
| 881 | } |
| 882 | |
| 883 | if ($isVip) { |
| 884 | $reasonForNotFollowingUp = 2; |
| 885 | } |
| 886 | |
| 887 | Log::channel('allInfoQuotationsG3w')->info('Reason for not following up: '.$reasonForNotFollowingUp); |
| 888 | |
| 889 | $defaultUser = 0; |
| 890 | |
| 891 | if (! $comercialUser) { |
| 892 | Log::channel('allInfoQuotationsG3w')->info('Default user'); |
| 893 | if ($companyId == 18) { |
| 894 | $defaultUser = 94; |
| 895 | } |
| 896 | |
| 897 | if ($companyId == 19) { |
| 898 | $defaultUser = 68; |
| 899 | } |
| 900 | |
| 901 | // FIRE-1476: CLM (22) merged into Madrid — no separate default user. |
| 902 | |
| 903 | if ($companyId == 30) { |
| 904 | $defaultUser = 124; |
| 905 | } |
| 906 | Log::channel('allInfoQuotationsG3w')->info('Default user: '.$defaultUser); |
| 907 | } |
| 908 | |
| 909 | if (config('app.env') === 'local') { |
| 910 | if ($defaultUser == 0) { |
| 911 | $defaultUser = 92; |
| 912 | } |
| 913 | } |
| 914 | |
| 915 | $generateNumber = $this->generateQuoteId($companyId); |
| 916 | Log::channel('allInfoQuotationsG3w')->info('Generate number: '.$generateNumber['id']); |
| 917 | |
| 918 | $g3wNewId = $generateNumber['id']; |
| 919 | $newQuoteId = $generateNumber['number']; |
| 920 | |
| 921 | // FIRE-1170: bulk-sync context honored on the INSERT path too. |
| 922 | // checkAproval() always fires the email when for_approval !== null; |
| 923 | // we inline its two-step logic so the bulk-sync toggles can suppress |
| 924 | // the email and/or clamp the financial level without modifying the |
| 925 | // shared helper (which is also called from non-bulk paths). |
| 926 | $forApproval = self::computeForApprovalLevel($companyId, $typeId, 2, $budget['presupuesto']['importe']); |
| 927 | $forApproval = $this->suppressFinancialLevel($forApproval); |
| 928 | if ($forApproval !== null && ! ($this->bulkSyncContext['suppressOps'] ?? false)) { |
| 929 | // FIRE-1229: pass the computed level as 9th arg so the email |
| 930 | // routes to `tbl_approvers_v2` when dual-level (3). Mirror |
| 931 | // of the checkAproval() wrapper at line 2625. |
| 932 | // |
| 933 | // Wrapped in try/catch because the email send happens BEFORE |
| 934 | // the row's full UPDATE at line ~886 — an unhandled SendGrid |
| 935 | // failure here would strand the placeholder row created by |
| 936 | // generateQuoteId() with no `internal_quote_id`. The DB write |
| 937 | // is what the operator cares about; the email is a courtesy. |
| 938 | // Log the failure so it's recoverable. |
| 939 | try { |
| 940 | self::sendApprovalNotificationOnly($companyId, $typeId, 2, $budget['presupuesto']['importe'], $newQuoteId, $g3wNewId, $comercialUser?->id_fst ?? $defaultUser, $comercial, $forApproval); |
| 941 | } catch (\Throwable $emailEx) { |
| 942 | Log::channel('allInfoQuotationsG3w')->warning( |
| 943 | "syncById: approval email failed for budget {$id} (level={$forApproval}); continuing with DB write. Error: {$emailEx->getMessage()}" |
| 944 | ); |
| 945 | } |
| 946 | } |
| 947 | |
| 948 | Log::channel('allInfoQuotationsG3w')->info('For approval: '.$forApproval); |
| 949 | |
| 950 | // FIRE-1024: normalise once and reuse across acceptance_date / |
| 951 | // accepted_at / accepted_by. The pre-fix inline ternary only |
| 952 | // caught '0000-00-00' and '0000/00/00' — empty string and the |
| 953 | // datetime form '0000-00-00 00:00:00' both leaked through. |
| 954 | $g3wAcceptance = $this->normalizeG3wAcceptance($budget['presupuesto']['aceptacion'] ?? null); |
| 955 | if ($nameStatus === 'Aceptado' && $g3wAcceptance === null) { |
| 956 | $g3wAcceptance = self::acceptanceDateFallback($budget['presupuesto']['fecha_modificacion'] ?? null, now()); |
| 957 | Log::channel('g3w')->warning("G3W budget {$id} is Aceptado with no acceptance date in Gestiona; using {$g3wAcceptance}."); |
| 958 | } |
| 959 | |
| 960 | // If G3W returns the "unmapped" sentinel (16), fall back to any |
| 961 | // existing tbl_quotations row that already carries a |
| 962 | // budget_type_id for this internal_quote_id (defensive against |
| 963 | // TOCTOU / re-import races where the existsQuery check above |
| 964 | // missed a sibling row). Normal create path: returns null when |
| 965 | // no prior row exists, which is the correct default. |
| 966 | $preservedTypeId = $typeId == 16 |
| 967 | ? TblQuotations::where('internal_quote_id', $budget['presupuesto']['cod_presupuesto'])->value('budget_type_id') |
| 968 | : $typeId; |
| 969 | |
| 970 | // FIRE-83 (final spec): see `syncModifiedBudgetById` for the |
| 971 | // rationale. Sum labor lines into cost_of_labor, sum non-labor |
| 972 | // lines into estimated_cost_of_materials. Partition, no overlap. |
| 973 | $totalCostOfMaterial = 0; |
| 974 | $totalLabor = 0; |
| 975 | foreach (($budget['presupuesto']['lineas'] ?? []) as $linea) { |
| 976 | $precioCompra = (float) ($linea['precio_compra'] ?? 0); |
| 977 | $unidades = (float) ($linea['unidades'] ?? 0); |
| 978 | if ($precioCompra <= 0 || $unidades <= 0) { |
| 979 | continue; |
| 980 | } |
| 981 | if (self::classifyLineAsLabor($linea, self::MATERIAL_CODES_BY_REGION[$region] ?? [])) { |
| 982 | $totalLabor += $precioCompra * $unidades; |
| 983 | } else { |
| 984 | $totalCostOfMaterial += $precioCompra * $unidades; |
| 985 | } |
| 986 | } |
| 987 | |
| 988 | $companyInfo = $this->resolveCompanyForRegion($region); |
| 989 | $hoursPerDay = (float) ($companyInfo->hours_per_worker_per_day ?? 0); |
| 990 | $costOfHour = (float) ($companyInfo->cost_of_hour ?? 0); |
| 991 | $dailyRate = $hoursPerDay * $costOfHour; |
| 992 | $peopleAssignedSeed = 1; |
| 993 | $durationInDaysSeed = $dailyRate > 0 ? $totalLabor / $dailyRate : 0; |
| 994 | |
| 995 | $resultMargin = $this->calculateBudgetMargin( |
| 996 | $budget['presupuesto']['importe'] ?? 0, |
| 997 | 0, // commission_pct — not known on initial import |
| 998 | $peopleAssignedSeed, |
| 999 | $durationInDaysSeed, |
| 1000 | $totalLabor, |
| 1001 | $totalCostOfMaterial, |
| 1002 | $segmentID, |
| 1003 | $companyInfo->general_costs ?? 0 |
| 1004 | ); |
| 1005 | |
| 1006 | $g3wArray = [ |
| 1007 | 'internal_quote_id' => $budget['presupuesto']['cod_presupuesto'], |
| 1008 | 'quote_id' => $newQuoteId, |
| 1009 | 'company_id' => $companyId, |
| 1010 | 'customer_type_id' => 2, |
| 1011 | 'segment_id' => $segmentID, |
| 1012 | 'budget_type_id' => $preservedTypeId, |
| 1013 | 'budget_status_id' => $statusID, |
| 1014 | 'source_id' => $sourceId, |
| 1015 | 'client' => $companyName, |
| 1016 | 'phone_number' => $companyTelephone, |
| 1017 | 'email' => $companyEmail, |
| 1018 | // FIRE-1001: G3W returns '0000-00-00' (dashes) for an absent |
| 1019 | // date; older payloads used slashes — accept both. Same |
| 1020 | // rationale as the update branch. |
| 1021 | 'issue_date' => in_array(($budget['presupuesto']['fecha_creacion'] ?? null), ['0000-00-00', '0000/00/00'], true) ? null : ($budget['presupuesto']['fecha_creacion'] ?? null), |
| 1022 | 'request_date' => in_array(($budget['presupuesto']['fecha_creacion'] ?? null), ['0000-00-00', '0000/00/00'], true) ? null : ($budget['presupuesto']['fecha_creacion'] ?? null), |
| 1023 | // FIRE-1001 + FIRE-1024: pull acceptance date from G3W's |
| 1024 | // `aceptacion` field, normalised via |
| 1025 | // `normalizeG3wAcceptance()` so every empty representation |
| 1026 | // (null, '', '0000-00-00', '0000/00/00', |
| 1027 | // '0000-00-00 00:00:00', whitespace) lands as PHP null. |
| 1028 | // accepted_by is only stamped when there's a real date — |
| 1029 | // "System accepted on (no date)" is meaningless. |
| 1030 | 'acceptance_date' => $g3wAcceptance, |
| 1031 | 'accepted_at' => ($nameStatus === 'Aceptado') ? $g3wAcceptance : null, |
| 1032 | 'accepted_by' => ($nameStatus === 'Aceptado' && $g3wAcceptance !== null) ? 'System' : null, |
| 1033 | 'amount' => $budget['presupuesto']['importe'] ?? null, |
| 1034 | 'last_follow_up_date' => $facilityUser ? null : $lastFollowUpDate, |
| 1035 | 'commercial' => $comercial, |
| 1036 | 'created_at' => $budget['presupuesto']['fecha_creacion'] ?? null, |
| 1037 | 'created_by' => $createdBy, |
| 1038 | 'has_attachment' => 1, |
| 1039 | // The "Presupuesto y Margen" block is in use whenever the sync |
| 1040 | // writes real costs/margins; without the flag the quotes list |
| 1041 | // hid these margins (FIRE-1387 only fixed it in the form). |
| 1042 | 'budget_margin_enabled' => self::hasMarginData($resultMargin) ? 1 : 0, |
| 1043 | // FIRE-83: margins now seeded from G3W labor/material lines. |
| 1044 | 'cost_of_labor' => $resultMargin['cost_of_labor'], |
| 1045 | 'total_cost_of_job' => $resultMargin['total_cost_of_job'], |
| 1046 | 'invoice_margin' => $resultMargin['invoice_margin'], |
| 1047 | 'margin_for_the_company' => $resultMargin['margin_for_the_company'], |
| 1048 | 'margin_on_invoice_per_day_per_worker' => $resultMargin['margin_on_invoice_per_day_per_worker'], |
| 1049 | 'revenue_per_date_per_worked' => $resultMargin['revenue_per_date_per_worked'], |
| 1050 | 'gross_margin' => $resultMargin['gross_margin'], |
| 1051 | 'labor_percentage' => $resultMargin['labor_percentage'], |
| 1052 | 'estimated_cost_of_materials' => $totalCostOfMaterial, |
| 1053 | 'people_assigned_to_the_job' => $peopleAssignedSeed, |
| 1054 | 'duration_of_job_in_days' => $durationInDaysSeed, |
| 1055 | 'sync_import' => 1, |
| 1056 | 'box_work_g3w' => $idsConcatenados, |
| 1057 | 'segment_by_g3w' => $company['servicio']['tipo_servicio'] ?? null, |
| 1058 | 'source_by_g3w' => $budget['presupuesto']['cod_empresa_presupuesto'] ?? null, |
| 1059 | 'status_by_g3w' => $nameStatus, |
| 1060 | 'type_by_g3w' => $budget['presupuesto']['origen_presupuesto'] ?? null, |
| 1061 | 'user_create_by_g3w' => $budget['presupuesto']['usuario'], |
| 1062 | 'user_commercial_by_g3w' => ! empty($budget['presupuesto']['cod_comercial_presupuesto']) ? $budget['presupuesto']['cod_comercial_presupuesto'] : null, |
| 1063 | 'g3w_warning' => $g3wWarning, |
| 1064 | 'g3w_warning_fields' => $g3wWarningFields, |
| 1065 | 'reason_for_not_following_up_id' => $reasonForNotFollowingUp, |
| 1066 | 'for_add' => 0, |
| 1067 | 'for_approval' => $forApproval, |
| 1068 | // `approval_type` mirrors `for_approval` (1 technical, 2 financial, |
| 1069 | // 3 both, null none) so the orders grid can show the type. |
| 1070 | // |
| 1071 | // The comment previously here claimed the level was "always |
| 1072 | // technical … never the financial (2) or dual (3) levels". That |
| 1073 | // stopped being true when the matrix engine landed, and acting on |
| 1074 | // it is what let a financial-only budget be filed as technical. |
| 1075 | 'approval_type' => $forApproval, |
| 1076 | ]; |
| 1077 | |
| 1078 | TblQuotations::where('id', $g3wNewId)->update($g3wArray); |
| 1079 | |
| 1080 | $this->quotationsController->addUpdateLog($g3wNewId, 'G3W', null, null, null, 2); |
| 1081 | |
| 1082 | // Imported already Enviado: the send is the import itself (now), not |
| 1083 | // the Gestiona budget date, which is only its creation date. Imported |
| 1084 | // already Aceptado without a send: the acceptance stands in for it. |
| 1085 | if ($nameStatus === 'Enviado') { |
| 1086 | QuotationSendRecorder::record((int) $g3wNewId, QuotationSendRecorder::IMPORT, 'G3W', |
| 1087 | null, $budget['presupuesto']['importe'] ?? null); |
| 1088 | } elseif ($nameStatus === 'Aceptado') { |
| 1089 | QuotationSendRecorder::record((int) $g3wNewId, QuotationSendRecorder::ACCEPTANCE, 'G3W', |
| 1090 | $g3wAcceptance ?? null, $budget['presupuesto']['importe'] ?? null); |
| 1091 | } |
| 1092 | |
| 1093 | Log::channel('allInfoQuotationsG3w')->info('Pasamos el insert.'); |
| 1094 | |
| 1095 | $companyNameFormatted = ''; |
| 1096 | |
| 1097 | if (! empty($companyName)) { |
| 1098 | $companyNameFormatted = str_replace(' ', '_', $companyName); |
| 1099 | } |
| 1100 | |
| 1101 | $nameDocument = $companyNameFormatted |
| 1102 | ? $budget['presupuesto']['cod_presupuesto'].'_'.$companyNameFormatted |
| 1103 | : $budget['presupuesto']['cod_presupuesto']; |
| 1104 | |
| 1105 | Log::channel('allInfoQuotationsG3w')->info('Nombre del documento: '.$nameDocument); |
| 1106 | |
| 1107 | $responseSaveDocument = $this->saveDocument($budget['presupuesto']['documento'], $nameDocument, $g3wNewId, $newQuoteId, 'G3W'); |
| 1108 | $responseDataSaveDocument = $responseSaveDocument->getData(); |
| 1109 | |
| 1110 | if (! isset($responseDataSaveDocument->success) || ! $responseDataSaveDocument->success) { |
| 1111 | Log::channel('allInfoQuotationsG3w')->info("No se pudo guardar el documento del presupuesto {$id} al crearlo. Error: ".($responseDataSaveDocument->error ?? 'Desconocido')); |
| 1112 | throw new \Exception("No se pudo guardar el documento del presupuesto {$id} al crearlo. Error: ".($responseDataSaveDocument->error ?? 'Desconocido')); |
| 1113 | } |
| 1114 | |
| 1115 | /** @phpstan-ignore-next-line */ |
| 1116 | $documentName = $responseDataSaveDocument->documentName; |
| 1117 | |
| 1118 | // FIRE-1025: zero-date sweep moved to the daily |
| 1119 | // `quotations:cleanup-zero-dates` artisan command. Pre-fix this |
| 1120 | // UPDATE ran at the end of every G3W sync cycle (5 call sites |
| 1121 | // here) and deadlocked on `idx_acceptance_date` when concurrent |
| 1122 | // syncs piled up (see InnoDB MONITOR OUTPUT on 2026-04-27 with |
| 1123 | // multiple "WE ROLL BACK TRANSACTION (1)" entries on this exact |
| 1124 | // statement). FIRE-1001 normalises `aceptacion` on write so no |
| 1125 | // new zero-dates can be created — the daily cleanup handles |
| 1126 | // legacy rows. |
| 1127 | |
| 1128 | // FIRE-1145 follow-up: syncById just inserted/updated a quotation |
| 1129 | // row in the DB — forget the quotations bucket so the next read |
| 1130 | // doesn't serve a 10-min-stale list. |
| 1131 | ResultCache::forgetDomain('quotations'); |
| 1132 | |
| 1133 | return [ |
| 1134 | 'success' => true, |
| 1135 | 'id' => $g3wNewId, |
| 1136 | ]; |
| 1137 | |
| 1138 | } catch (\Exception $e) { |
| 1139 | $errorMessage = $e->getMessage(); |
| 1140 | |
| 1141 | return ['success' => false, 'error' => "Error sincronizando el presupuesto {$id}: ".$errorMessage]; |
| 1142 | } |
| 1143 | } |
| 1144 | |
| 1145 | public function syncModifiedBudgetById(string $id, $region = null): array |
| 1146 | { |
| 1147 | try { |
| 1148 | // Region is stored platform-wide as "Cataluña" (see |
| 1149 | // QuotationsSync::DEFAULT_REGIONS + tbl_companies.region). Normalise |
| 1150 | // Catalunya, percent-encoded and shell-quoted callers alike so the |
| 1151 | // region-scoped mapping lookups below match. |
| 1152 | $region = $this->normalizeRegion($region); |
| 1153 | |
| 1154 | Log::channel('allInfoQuotationsG3w')->info('Modificando presupuesto: '.$id); |
| 1155 | $statusID = null; |
| 1156 | // FIRE-83: source moved to self::MATERIAL_CODES_BY_REGION so the |
| 1157 | // INSERT path (`syncById`) can classify lines with the same rules. |
| 1158 | $materialId = self::MATERIAL_CODES_BY_REGION; |
| 1159 | |
| 1160 | $statusToChange = [ |
| 1161 | 'Enviado', |
| 1162 | 'Aceptado', |
| 1163 | 'Rechazado', |
| 1164 | 'Anulado', |
| 1165 | ]; |
| 1166 | |
| 1167 | $g3wWarning = 0; |
| 1168 | $g3wWarningFields = null; |
| 1169 | $companyId = $this->resolveCompanyForRegion($region)?->company_id; |
| 1170 | |
| 1171 | $budget = $this->request('get', "presupuesto/{$id}", $region, []); |
| 1172 | |
| 1173 | $existsQuery = TblQuotations::where('internal_quote_id', $budget['presupuesto']['cod_presupuesto']) |
| 1174 | ->where('company_id', $companyId); |
| 1175 | if (! $existsQuery->exists()) { |
| 1176 | Log::channel('allInfoQuotationsG3w')->info("No se encuentra el presupuesto con ID en G3W $id. Es posible que sea debido a que se creo antes de la integracion de FST con G3W."); |
| 1177 | throw new \Exception("No se encuentra el presupuesto con ID en G3W $id. Es posible que sea debido a que se creo antes de la integracion de FST con G3W."); |
| 1178 | } |
| 1179 | |
| 1180 | $company = $this->request('get', "servicio/{$budget['presupuesto']['cod_servicio']}", $region, []); |
| 1181 | |
| 1182 | $companyName = $company['servicio']['nombre_servicio'] ?? $company['cliente']['empresa'] ?? null; |
| 1183 | |
| 1184 | // $company = $this->request('get', "servicio/{$budget["presupuesto"]["cod_servicio"]}", $region, []); |
| 1185 | |
| 1186 | /*if(!$company || !isset($company['servicio'])){ |
| 1187 | $company = $this->request('get', "cliente/{$budget["presupuesto"]["cod_cliente"]}", $region, []); |
| 1188 | }*/ |
| 1189 | |
| 1190 | // $companyName = (isset($company['servicio'])) ? $company["servicio"]["nombre_servicio"] : $company["cliente"]["empresa"]; |
| 1191 | // $companyTelephone = (isset($company['servicio'])) ? $company["servicio"]["telefono"] : $company["cliente"]["telefono"] ; |
| 1192 | |
| 1193 | // $segmentID = $this->normalizeSegment($company["servicio"]["tipo_servicio"]?? "Otro"); |
| 1194 | |
| 1195 | if (! isset($budget['presupuesto']['documento']) || ! $budget['presupuesto']['documento']) { |
| 1196 | Log::channel('allInfoQuotationsG3w')->info('El presupuesto no tiene documento asociado. Creelo y vuelva a intentarlo.'); |
| 1197 | throw new \Exception('El presupuesto no tiene documento asociado. Creelo y vuelva a intentarlo.'); |
| 1198 | } |
| 1199 | |
| 1200 | $typeId = $this->normalizeType($budget['presupuesto']['origen_presupuesto'], $region); |
| 1201 | |
| 1202 | Log::channel('allInfoQuotationsG3w')->info('Tipo de presupuesto: '.$typeId); |
| 1203 | |
| 1204 | $statusList = $this->statusListByRegion[$region] ??= $this->request('get', 'presupuesto/tiposestado', $region, []); |
| 1205 | |
| 1206 | $collection = collect($statusList); |
| 1207 | |
| 1208 | $nameStatus = $collection->firstWhere('ID', $budget['presupuesto']['estado'])['nombre'] ?? null; |
| 1209 | |
| 1210 | Log::channel('allInfoQuotationsG3w')->info('Estado de presupuesto: '.$nameStatus); |
| 1211 | |
| 1212 | if (! $nameStatus) { |
| 1213 | $statusID = $this->budgetStatusIdByName('Sin estado en G3W'); |
| 1214 | } else { |
| 1215 | $statusID = $this->normalizeStatus($nameStatus); |
| 1216 | |
| 1217 | if ($statusID === $this->budgetStatusIdByName('Sin estado en G3W')) { |
| 1218 | $statusID = $this->budgetStatusIdByName('Estado no reconocido en FST'); |
| 1219 | } |
| 1220 | } |
| 1221 | |
| 1222 | Log::channel('allInfoQuotationsG3w')->info('ID de estado: '.$statusID); |
| 1223 | |
| 1224 | $row = TblQuotations::where('internal_quote_id', $budget['presupuesto']['cod_presupuesto']) |
| 1225 | ->where('company_id', $companyId) |
| 1226 | ->first(); |
| 1227 | |
| 1228 | if ($row) { |
| 1229 | // FIRE-864 (c): G3W says "Enviado" (2) but Titan has no record |
| 1230 | // of sending (no x_message_id). Decision by email validity: |
| 1231 | // - email valid → keep "Enviado" (2). We can't verify |
| 1232 | // Titan-side, but the address is plausible. |
| 1233 | // - email invalid → "Correo erroneo" (22). G3W claims sent |
| 1234 | // but the address can't actually receive. |
| 1235 | // If Titan HAS sent it (x_message_id set), leave 2 — the |
| 1236 | // SendGrid webhook/backfill will flip to 22 on bounce anyway. |
| 1237 | if ($statusID === 2 && empty($row->x_message_id) && $this->checkEmailInvalid((string) $row->email)) { |
| 1238 | Log::channel('allInfoQuotationsG3w')->info("FIRE-864: G3W Enviado en edición sin envío Titan y email inválido (quote {$row->id}, email {$row->email}) → Correo erroneo (22)."); |
| 1239 | $statusID = 22; |
| 1240 | } |
| 1241 | |
| 1242 | // FIRE-1278: email problems belong in "Emails con error", not |
| 1243 | // "Fallos en G3W" (see the creation path). Only non-email |
| 1244 | // required-field problems raise the G3W warning. |
| 1245 | if ( |
| 1246 | in_array($row->budget_status_id, [13, 14]) || |
| 1247 | ! $row->source_id || |
| 1248 | (! $typeId || $typeId == 0 || $typeId == 16) || |
| 1249 | empty(trim((string) $row->client)) // change for $companyName when we implement |
| 1250 | ) { |
| 1251 | $g3wWarning = 1; |
| 1252 | } |
| 1253 | |
| 1254 | $g3wWarningFields = $this->checkRequiredFields($row); |
| 1255 | |
| 1256 | if ($this->hasNonEmailWarning($g3wWarningFields)) { |
| 1257 | $g3wWarning = 1; |
| 1258 | } |
| 1259 | |
| 1260 | // FIRE-1001 + FIRE-1024: G3W is the source of truth for the |
| 1261 | // acceptance date on G3W-imported quotations. The original |
| 1262 | // FIRE-1001 logic preserved Titan's previous value when G3W |
| 1263 | // came back empty ("Titan remembers what G3W forgot"), but |
| 1264 | // Berta filed FIRE-1024 because that hides intentional |
| 1265 | // un-acceptances done in G3W: the user clears `aceptacion` |
| 1266 | // upstream and Titan keeps showing the old date. |
| 1267 | // |
| 1268 | // Reversal: always mirror G3W. If G3W is empty, Titan goes |
| 1269 | // null. If status moved away from Aceptado, accepted_at also |
| 1270 | // goes null (the row is no longer accepted, so the audit |
| 1271 | // metadata shouldn't claim otherwise). |
| 1272 | // |
| 1273 | // The audit log entries at addUpdateLog() below (and the |
| 1274 | // FIRE-982 audit infra) capture the before/after, so any |
| 1275 | // unintended clearing is recoverable. |
| 1276 | $g3wAcceptanceDate = $this->normalizeG3wAcceptance($budget['presupuesto']['aceptacion'] ?? null); |
| 1277 | |
| 1278 | // Gestiona lets a budget be "Aceptado" with no acceptance date |
| 1279 | // (2026-10-04: G3W 22066 was accepted at night and dated only |
| 1280 | // the next afternoon). Mirroring that empty value left accepted |
| 1281 | // quotes out of every "accepted in period X" figure. An accepted |
| 1282 | // quote always gets a date: keep the one Titan already has (a |
| 1283 | // salesperson may have set it), otherwise fall back to when |
| 1284 | // Gestiona last changed the budget. A real date from Gestiona, |
| 1285 | // when it arrives, still replaces it. |
| 1286 | if ($nameStatus === 'Aceptado' && $g3wAcceptanceDate === null) { |
| 1287 | $g3wAcceptanceDate = $row->acceptance_date |
| 1288 | ? Carbon::parse($row->acceptance_date)->format('Y-m-d H:i:s') |
| 1289 | : self::acceptanceDateFallback($budget['presupuesto']['fecha_modificacion'] ?? null, now()); |
| 1290 | if (! $row->acceptance_date) { |
| 1291 | Log::channel('g3w')->warning("G3W budget {$id} (quote {$row->id}) is Aceptado with no acceptance date in Gestiona; using {$g3wAcceptanceDate}."); |
| 1292 | } |
| 1293 | } |
| 1294 | |
| 1295 | $newAcceptanceDate = $g3wAcceptanceDate; |
| 1296 | $newAcceptedAt = ($nameStatus === 'Aceptado') ? $g3wAcceptanceDate : null; |
| 1297 | |
| 1298 | Log::channel('allInfoQuotationsG3w')->info('G3W Warning: '.$g3wWarning); |
| 1299 | |
| 1300 | if ($row->budget_type_id !== $typeId) { |
| 1301 | $this->quotationsController->addUpdateLog($row->id, 'G3W', 'budget_type_id', $row->budget_type_id, $typeId, 4); |
| 1302 | } |
| 1303 | |
| 1304 | if (in_array($nameStatus, $statusToChange) || $row->budget_status_id == 6) { |
| 1305 | $this->quotationsController->addUpdateLog($row->id, 'G3W', 'budget_status_id', $row->budget_status_id, $statusID, 4); |
| 1306 | } |
| 1307 | |
| 1308 | if ($row->amount !== $budget['presupuesto']['importe']) { |
| 1309 | $this->quotationsController->addUpdateLog($row->id, 'G3W', 'amount', $row->amount, $budget['presupuesto']['importe'], 4); |
| 1310 | } |
| 1311 | |
| 1312 | if ($row->updated_by !== 'System') { |
| 1313 | $this->quotationsController->addUpdateLog($row->id, 'G3W', 'updated_by', $row->updated_by, 'G3W', 4); |
| 1314 | } |
| 1315 | |
| 1316 | if ($row->has_attachment !== 1) { |
| 1317 | $this->quotationsController->addUpdateLog($row->id, 'G3W', 'has_attachment', $row->has_attachment, 1, 4); |
| 1318 | } |
| 1319 | |
| 1320 | if ($row->sync_import_edited !== 1) { |
| 1321 | $this->quotationsController->addUpdateLog($row->id, 'G3W', 'sync_import_edited', $row->sync_import_edited, 1, 4); |
| 1322 | } |
| 1323 | |
| 1324 | if ($nameStatus === 'Aceptado') { |
| 1325 | $this->quotationsController->addUpdateLog($row->id, 'G3W', null, $row->acceptance_date, $newAcceptanceDate, 4); |
| 1326 | $this->quotationsController->addUpdateLog($row->id, 'G3W', null, $row->accepted_at, $newAcceptedAt, 4); |
| 1327 | $this->quotationsController->addUpdateLog($row->id, 'G3W', null, $row->accepted_by, 'G3W', 4); |
| 1328 | } |
| 1329 | |
| 1330 | Log::channel('allInfoQuotationsG3w')->info('Pasamos el add update log'); |
| 1331 | |
| 1332 | // FIRE-1001: classify each G3W line as labor or material. |
| 1333 | // Logic lives in a static helper so it can be unit-tested |
| 1334 | // without spinning up the full sync. See |
| 1335 | // `classifyLineAsLabor` below for the rationale. |
| 1336 | // FIRE-83 (final spec, Berta confirmed 2026-06-25): |
| 1337 | // - cost_of_labor = sum of `precio_compra × unidades` for |
| 1338 | // ALL labor-classified lines (cod_material in the per- |
| 1339 | // region labor allow-list, or description matches the |
| 1340 | // labor regex). |
| 1341 | // - estimated_cost_of_materials = sum of `precio_compra × |
| 1342 | // unidades` for ALL OTHER (non-labor) lines. |
| 1343 | // - total_cost_of_job = labor + materials = G3W's Total |
| 1344 | // Coste. Clean partition, no double counting. |
| 1345 | $totalCostOfMaterial = 0; |
| 1346 | $totalLabor = 0; |
| 1347 | foreach ($budget['presupuesto']['lineas'] as $linea) { |
| 1348 | $precioCompra = (float) ($linea['precio_compra'] ?? 0); |
| 1349 | $unidades = (float) ($linea['unidades'] ?? 0); |
| 1350 | if ($precioCompra <= 0 || $unidades <= 0) { |
| 1351 | continue; |
| 1352 | } |
| 1353 | if (self::classifyLineAsLabor($linea, $materialId[$region] ?? [])) { |
| 1354 | $totalLabor += $precioCompra * $unidades; |
| 1355 | } else { |
| 1356 | $totalCostOfMaterial += $precioCompra * $unidades; |
| 1357 | } |
| 1358 | } |
| 1359 | |
| 1360 | $companyInfo = $this->resolveCompanyForRegion($region); |
| 1361 | |
| 1362 | $hoursPerDay = (float) ($companyInfo->hours_per_worker_per_day ?? 0); |
| 1363 | $costOfHour = (float) ($companyInfo->cost_of_hour ?? 0); |
| 1364 | $dailyRate = $hoursPerDay * $costOfHour; |
| 1365 | // Seed default for `duration_of_job_in_days` only — back- |
| 1366 | // derive from labor cost when the row has no operator value. |
| 1367 | $numberOfDays = $dailyRate > 0 ? $totalLabor / $dailyRate : 0; |
| 1368 | |
| 1369 | // FIRE-1001: don't clobber user-edited people/days on every |
| 1370 | // re-sync. Sync seeds defaults only when the row hasn't been |
| 1371 | // configured yet — once Berta has set "2 técnicos × 20 días" |
| 1372 | // we keep them. |
| 1373 | // |
| 1374 | // FIRE-83: people/days are now informational. They still feed |
| 1375 | // per-worker-per-day metrics (`margin_on_invoice_per_day_per_worker`, |
| 1376 | // `revenue_per_date_per_worked`) but no longer drive `cost_of_labor`. |
| 1377 | // `cost_of_labor` is taken directly from G3W (`$totalLabor`). |
| 1378 | // |
| 1379 | $peopleAssigned = (int) ($row->people_assigned_to_the_job ?? 0) > 0 |
| 1380 | ? (int) $row->people_assigned_to_the_job |
| 1381 | : 1; |
| 1382 | $durationInDays = (float) ($row->duration_of_job_in_days ?? 0) > 0 |
| 1383 | ? (float) $row->duration_of_job_in_days |
| 1384 | : $numberOfDays; |
| 1385 | |
| 1386 | $resultMargin = $this->calculateBudgetMargin( |
| 1387 | $budget['presupuesto']['importe'] ?? 0, |
| 1388 | $row->commission_pct ?? 0, |
| 1389 | $peopleAssigned, |
| 1390 | $durationInDays, |
| 1391 | $totalLabor, |
| 1392 | $totalCostOfMaterial, |
| 1393 | $row->segment_id ?? 0, |
| 1394 | $companyInfo->general_costs ?? 0 |
| 1395 | ); |
| 1396 | |
| 1397 | Log::channel('allInfoQuotationsG3w')->info('Pasamos el calculo de margen.'); |
| 1398 | |
| 1399 | $forApproval = $row->for_approval; |
| 1400 | // Preserve the existing approval_type by default so a sync |
| 1401 | // that doesn't genuinely re-flag (no amount/type change) |
| 1402 | // can't clobber a manually-set financial (2) or dual (3) |
| 1403 | // type back to technical. Only reassigned on a real re-flag. |
| 1404 | $approvalType = $row->approval_type; |
| 1405 | $clearPriorApproval = false; |
| 1406 | |
| 1407 | $isWorkAccepted = false; |
| 1408 | |
| 1409 | try { |
| 1410 | $work = $this->request('get', "presupuesto/trabajos/{$id}", $region, []); |
| 1411 | |
| 1412 | if (! empty($work)) { |
| 1413 | $dataToSend = [ |
| 1414 | 'ids' => array_column($work, 'ID'), |
| 1415 | ]; |
| 1416 | |
| 1417 | $worksStatus = $this->request('post', 'trabajo/estados', $region, $dataToSend); |
| 1418 | |
| 1419 | if (is_array($worksStatus)) { |
| 1420 | foreach ($worksStatus as $item) { |
| 1421 | if (($item['estado'] ?? null) === 'Aceptado') { |
| 1422 | $isWorkAccepted = true; |
| 1423 | break; |
| 1424 | } |
| 1425 | } |
| 1426 | } |
| 1427 | } |
| 1428 | } catch (\Exception $worksErr) { |
| 1429 | // G3W's trabajo/estados endpoint is known to return raw SQL |
| 1430 | // in the error body for some quotes (their internal query |
| 1431 | // is malformed). Treat as "no accepted works" and continue |
| 1432 | // the sync so the quotation still updates. |
| 1433 | Log::channel('allInfoQuotationsG3w')->warning( |
| 1434 | "G3W trabajos/estados falló para presupuesto {$id} (region {$region}). Se continúa con isWorkAccepted=false. Error: ".$worksErr->getMessage() |
| 1435 | ); |
| 1436 | } |
| 1437 | |
| 1438 | // FIRE-1092: detect approval-input changes reliably and |
| 1439 | // only re-flag when the recomputed level differs from the |
| 1440 | // current one. Two defects fixed here vs. the old code: |
| 1441 | // 1. The change-check used strict `!==` between an |
| 1442 | // Eloquent decimal (string "1500.0000") and the G3W |
| 1443 | // JSON value (float 1500.0), so it tripped on every |
| 1444 | // sync — re-flagging approval and re-sending the |
| 1445 | // approval email even when nothing changed. |
| 1446 | // 2. Inside the block, `for_approval` was overwritten |
| 1447 | // unconditionally — including with the same value — |
| 1448 | // and `approved_at`/`approved_by` were never cleared |
| 1449 | // on a genuine re-flag, leaving inconsistent state. |
| 1450 | // |
| 1451 | // Note on `customer_type_id`: the original code had a |
| 1452 | // commented-out comparison for it. We leave it out: G3W |
| 1453 | // does not push customer_type_id (it's an FST-owned |
| 1454 | // field set by the user), so it never changes via this |
| 1455 | // sync path. Manual edits to customer_type_id flow |
| 1456 | // through `update_quotation` which has its own approval |
| 1457 | // recompute at :1320-1422. |
| 1458 | // |
| 1459 | // The right shape lives in `Quotations::update_quotation` |
| 1460 | // at :1422 (`$result->for_approval != $n` guard). We |
| 1461 | // mirror it here. |
| 1462 | $g3wAmount = (float) ($budget['presupuesto']['importe'] ?? 0); |
| 1463 | $amountChanged = abs((float) $row->amount - $g3wAmount) > 0.005; |
| 1464 | // Compare against the type this sync will actually STORE. An |
| 1465 | // unmapped G3W origin (16) keeps the existing budget_type_id |
| 1466 | // (see the update payload below), so the type hasn't changed. |
| 1467 | // Comparing the raw 16 made $typeChanged true on every sync, |
| 1468 | // which re-requested approval (email + wiped sign-off) for an |
| 1469 | // already-approved budget whose details never changed. |
| 1470 | $effectiveTypeId = $typeId == 16 ? (int) $row->budget_type_id : (int) $typeId; |
| 1471 | $typeChanged = (int) $row->budget_type_id !== $effectiveTypeId; |
| 1472 | |
| 1473 | // FIRE-1092: budgets in a terminal FST status (Aceptado=3, |
| 1474 | // Rechazado=4, Rechazado automático=7, Rechazado-duplicado=20) |
| 1475 | // are closed deals. Don't drag them back into the approval |
| 1476 | // queue from a G3W sync, even if G3W updates the amount. |
| 1477 | // `$isWorkAccepted` already catches G3W-side accepted work |
| 1478 | // (a related but distinct concept), but this guard also |
| 1479 | // catches budgets accepted/rejected directly in FST. |
| 1480 | $terminalStatuses = [3, 4, 7, 20]; |
| 1481 | $isTerminalStatus = in_array((int) $row->budget_status_id, $terminalStatuses, true); |
| 1482 | |
| 1483 | if ( |
| 1484 | ($amountChanged || $typeChanged) |
| 1485 | && ! $isWorkAccepted |
| 1486 | && ! $isTerminalStatus |
| 1487 | ) { |
| 1488 | // Commercial lookup tolerates a missing user (G3W name not in |
| 1489 | // tbl_users). sendApprovalNotificationOnly handles a null |
| 1490 | // commercialId by warning + notifying just the approver list, |
| 1491 | // not crashing the sync. |
| 1492 | $comercialUser = TblUsers::where('name', $row->commercial)->first(); |
| 1493 | if (! $comercialUser) { |
| 1494 | $mapping = $this->resolveUserMapping($row->commercial, $region); |
| 1495 | if ($mapping && $mapping->id_fst) { |
| 1496 | $comercialUser = TblUsers::where('id', $mapping->id_fst)->first(); |
| 1497 | } |
| 1498 | } |
| 1499 | |
| 1500 | $newForApproval = self::computeForApprovalLevel( |
| 1501 | $companyId, $effectiveTypeId, $row->customer_type_id, $g3wAmount |
| 1502 | ); |
| 1503 | |
| 1504 | $newForApproval = $this->suppressFinancialLevel($newForApproval); |
| 1505 | |
| 1506 | if ($newForApproval != $row->for_approval) { |
| 1507 | $forApproval = $newForApproval; |
| 1508 | // FIRE-1229: G3W is order-size based — the recomputed |
| 1509 | // level (1 or 3) is the approval type. Tier-2 promotion |
| 1510 | // now lands here when amount crosses minimum_order_size_v2. |
| 1511 | $approvalType = $newForApproval; |
| 1512 | |
| 1513 | if ($newForApproval !== null) { |
| 1514 | // Genuine re-flag: send the new notification |
| 1515 | // and clear prior approval columns so the audit |
| 1516 | // trail reflects that the previous sign-off is |
| 1517 | // no longer valid for the new amount/type. |
| 1518 | // |
| 1519 | // FIRE-1170: skip the email if bulk-sync requested |
| 1520 | // "responsable de operaciones" suppression. The DB |
| 1521 | // write of `for_approval` still happens (dashboard |
| 1522 | // stays accurate); only the email side-effect is |
| 1523 | // silenced for this run. |
| 1524 | if (! ($this->bulkSyncContext['suppressOps'] ?? false)) { |
| 1525 | self::sendApprovalNotificationOnly( |
| 1526 | $companyId, $effectiveTypeId, $row->customer_type_id, |
| 1527 | $g3wAmount, $row->quote_id, $row->id, |
| 1528 | $comercialUser?->id, $row->commercial, |
| 1529 | $newForApproval, |
| 1530 | ); |
| 1531 | } |
| 1532 | $clearPriorApproval = true; |
| 1533 | } |
| 1534 | // else (newForApproval === null): the new amount |
| 1535 | // dropped below the threshold. Leave approved_at / |
| 1536 | // approved_by intact — past approvals remain |
| 1537 | // historically true; the row simply leaves the |
| 1538 | // "Requiere aprobación" bucket via for_approval=null. |
| 1539 | } |
| 1540 | } |
| 1541 | |
| 1542 | // FIRE-1063: when an existing solicitud row is upgraded |
| 1543 | // by G3W sync, prioritise the solicitud's existing |
| 1544 | // client info but fill empties from G3W. The |
| 1545 | // `companyName/Telephone/Email` resolved at :366-368 |
| 1546 | // already follow the `servicio → cliente` order, so the |
| 1547 | // resulting per-field cascade is solicitud → G3W servicio |
| 1548 | // → G3W cliente. Pre-fix the three fields were silently |
| 1549 | // omitted (commented out below), which preserved |
| 1550 | // populated solicitudes correctly but stranded empty |
| 1551 | // ones with no fallback even though G3W had the data. |
| 1552 | $clientFallbacks = []; |
| 1553 | if (self::isBlankClientField($row->client) && ! empty($companyName)) { |
| 1554 | $clientFallbacks['client'] = $companyName; |
| 1555 | } |
| 1556 | if (self::isBlankClientField($row->phone_number) && ! empty($companyTelephone)) { |
| 1557 | $clientFallbacks['phone_number'] = $companyTelephone; |
| 1558 | } |
| 1559 | if (self::isBlankClientField($row->email) && ! empty($companyEmail)) { |
| 1560 | $clientFallbacks['email'] = $companyEmail; |
| 1561 | } |
| 1562 | |
| 1563 | // Resolve the commercial from G3W's cod_comercial_presupuesto |
| 1564 | // and keep the row in sync when it's reassigned upstream, |
| 1565 | // mirroring the INSERT path (syncById at :569-587). Guard |
| 1566 | // rails match that path's "wrong-commercial-on-sync" fix and |
| 1567 | // FIRE-1110: only overwrite when G3W points at a real FST user |
| 1568 | // with a name that actually differs from the current one. An |
| 1569 | // empty/unmapped code leaves the existing commercial untouched |
| 1570 | // so a manual assignment isn't clobbered with NULL on re-sync. |
| 1571 | $commercialUpdate = []; |
| 1572 | $codComercial = $budget['presupuesto']['cod_comercial_presupuesto'] ?? null; |
| 1573 | if (! empty($codComercial)) { |
| 1574 | // A single G3W code can have multiple mapping rows within a |
| 1575 | // region — a placeholder (id_fst NULL, created by an earlier |
| 1576 | // sync) AND a real match added later once a human linked it. |
| 1577 | // A plain ->first() can return the placeholder and silently |
| 1578 | // skip the resolvable row, so resolveUserMapping() prefers a |
| 1579 | // mapping that has an id_fst (newest wins), scoped to region. |
| 1580 | $g3wCommercialMapping = $this->resolveUserMapping($codComercial, $region, true); |
| 1581 | if ($g3wCommercialMapping) { |
| 1582 | $resolvedCommercial = TblUsers::where('id', $g3wCommercialMapping->id_fst)->value('name'); |
| 1583 | if ($resolvedCommercial !== null && $resolvedCommercial !== $row->commercial) { |
| 1584 | $commercialUpdate['commercial'] = $resolvedCommercial; |
| 1585 | $this->quotationsController->addUpdateLog($row->id, 'G3W', 'commercial', $row->commercial, $resolvedCommercial, 4); |
| 1586 | } |
| 1587 | } elseif (! TblUserG3wMapping::where('name_g3w', $codComercial)->where('region', $region)->exists()) { |
| 1588 | // No mapping at all yet in this region — create a |
| 1589 | // placeholder so a human can match it later, same as the |
| 1590 | // INSERT path. Commercial is left as-is this run. |
| 1591 | TblUserG3wMapping::create(['name_g3w' => $codComercial, 'region' => $region]); |
| 1592 | } |
| 1593 | } |
| 1594 | |
| 1595 | $approvalReset = $clearPriorApproval ? [ |
| 1596 | // FIRE-1092: re-flag is a genuine change of approval |
| 1597 | // state — invalidate the prior sign-off. The audit row |
| 1598 | // in tbl_quotations_log preserves who/when before this |
| 1599 | // wipe. |
| 1600 | 'approved_at' => null, |
| 1601 | 'approved_by' => null, |
| 1602 | 'approved_at_v2' => null, |
| 1603 | 'approved_by_v2' => null, |
| 1604 | 'rejected_at' => null, |
| 1605 | 'rejected_by' => null, |
| 1606 | 'rejected_at_v2' => null, |
| 1607 | 'rejected_by_v2' => null, |
| 1608 | ] : []; |
| 1609 | |
| 1610 | $updatePayload = array_merge($clientFallbacks, $approvalReset, $commercialUpdate, [ |
| 1611 | // 'segment_id' => $segmentID, |
| 1612 | // If G3W returns the "unmapped" sentinel (16), |
| 1613 | // preserve the existing row's budget_type_id |
| 1614 | // instead of wiping it. Same protective intent as |
| 1615 | // the IfexController fix — a transient mapping |
| 1616 | // gap shouldn't destroy known-good data. |
| 1617 | 'budget_type_id' => $typeId == 16 ? $row->budget_type_id : $typeId, |
| 1618 | // 'client' / 'phone_number' / 'email' filled |
| 1619 | // above only when the existing row is empty — |
| 1620 | // see FIRE-1063 cascade comment. |
| 1621 | 'budget_status_id' => (in_array($nameStatus, $statusToChange) || $row->budget_status_id == 6) ? $statusID : $row->budget_status_id, |
| 1622 | 'acceptance_date' => $newAcceptanceDate, |
| 1623 | 'accepted_at' => $newAcceptedAt, |
| 1624 | // FIRE-1024: don't claim "System" accepted the quote |
| 1625 | // when there's no real acceptance date. If status |
| 1626 | // moved away from Aceptado, also clear accepted_by. |
| 1627 | 'accepted_by' => ($nameStatus === 'Aceptado' && $newAcceptanceDate !== null) ? 'System' : null, |
| 1628 | 'amount' => $budget['presupuesto']['importe'] ?? null, |
| 1629 | 'updated_by' => 'System', |
| 1630 | 'updated_at' => Carbon::now()->format('Y-m-d H:i:s'), |
| 1631 | 'has_attachment' => 1, |
| 1632 | 'sync_import_edited' => 1, |
| 1633 | 'segment_by_g3w' => $company['servicio']['tipo_servicio'] ?? null, |
| 1634 | 'source_by_g3w' => $budget['presupuesto']['cod_empresa_presupuesto'] ?? null, |
| 1635 | 'status_by_g3w' => $nameStatus, |
| 1636 | 'type_by_g3w' => $budget['presupuesto']['origen_presupuesto'] ?? null, |
| 1637 | 'user_create_by_g3w' => $budget['presupuesto']['usuario'] ?? null, |
| 1638 | 'user_commercial_by_g3w' => ! empty($budget['presupuesto']['cod_comercial_presupuesto']) ? $budget['presupuesto']['cod_comercial_presupuesto'] : null, |
| 1639 | 'g3w_warning' => $g3wWarning, |
| 1640 | 'g3w_warning_fields' => $g3wWarningFields, |
| 1641 | // Switch the margin block on when the sync writes real |
| 1642 | // costs/margins; never switch it off (a user's choice). |
| 1643 | 'budget_margin_enabled' => ((int) $row->budget_margin_enabled > 0 || self::hasMarginData($resultMargin)) ? 1 : (int) $row->budget_margin_enabled, |
| 1644 | 'cost_of_labor' => $resultMargin['cost_of_labor'], |
| 1645 | 'total_cost_of_job' => $resultMargin['total_cost_of_job'], |
| 1646 | 'invoice_margin' => $resultMargin['invoice_margin'], |
| 1647 | 'margin_for_the_company' => $resultMargin['margin_for_the_company'], |
| 1648 | 'margin_on_invoice_per_day_per_worker' => $resultMargin['margin_on_invoice_per_day_per_worker'], |
| 1649 | 'commission_cost' => $resultMargin['commission_cost'], |
| 1650 | 'revenue_per_date_per_worked' => $resultMargin['revenue_per_date_per_worked'], |
| 1651 | 'gross_margin' => $resultMargin['gross_margin'], |
| 1652 | 'labor_percentage' => $resultMargin['labor_percentage'], |
| 1653 | 'estimated_cost_of_materials' => $totalCostOfMaterial, |
| 1654 | 'people_assigned_to_the_job' => $peopleAssigned, |
| 1655 | 'duration_of_job_in_days' => $durationInDays, |
| 1656 | 'for_approval' => $forApproval, |
| 1657 | 'approval_type' => $approvalType, |
| 1658 | ]); |
| 1659 | |
| 1660 | // FIRE-1387 (Berta 2026-07-10): once the operator has typed |
| 1661 | // anything into the Presupuesto y Margen block, their labor |
| 1662 | // inputs stop syncing from G3W. The FE flips |
| 1663 | // `labor_manual_override` to 1 on any keystroke in the labor |
| 1664 | // inputs (no popup). |
| 1665 | // |
| 1666 | // 2026-07-31 incident (PC24056076): this used to strip the whole |
| 1667 | // Presupuesto y Margen block, including the DERIVED fields. But |
| 1668 | // `amount` and `estimated_cost_of_materials` are not stripped — |
| 1669 | // they keep flowing from G3W — so the margins froze at whatever |
| 1670 | // the numbers were the instant the operator hit save. Every |
| 1671 | // subsequent sync wrote a new amount against stale margins: 37 of |
| 1672 | // 37 override syncs recalculated nothing, 21 quotes ended up |
| 1673 | // carrying margins that did not match their own amount, and 4 had |
| 1674 | // the sign of the margin inverted (quotes reading profitable while |
| 1675 | // actually losing money). |
| 1676 | // |
| 1677 | // The override protects the operator's INPUTS |
| 1678 | // (`cost_of_labor`, `people_assigned_to_the_job`, |
| 1679 | // `duration_of_job_in_days`) — never the figures derived from |
| 1680 | // them. So recompute the derived block against the incoming |
| 1681 | // amount/material using the operator's own labor inputs, and strip |
| 1682 | // only those three. |
| 1683 | if ((int) ($row->labor_manual_override ?? 0) === 1) { |
| 1684 | $overrideMargin = $this->calculateBudgetMargin( |
| 1685 | $budget['presupuesto']['importe'] ?? 0, |
| 1686 | $row->commission_pct ?? 0, |
| 1687 | $peopleAssigned, |
| 1688 | $durationInDays, |
| 1689 | // The operator's labor, not G3W's ($totalLabor) — that is |
| 1690 | // precisely what the override exists to preserve. |
| 1691 | $row->cost_of_labor ?? 0, |
| 1692 | $totalCostOfMaterial, |
| 1693 | $row->segment_id ?? 0, |
| 1694 | $companyInfo->general_costs ?? 0 |
| 1695 | ); |
| 1696 | |
| 1697 | $updatePayload['total_cost_of_job'] = $overrideMargin['total_cost_of_job']; |
| 1698 | $updatePayload['invoice_margin'] = $overrideMargin['invoice_margin']; |
| 1699 | $updatePayload['margin_for_the_company'] = $overrideMargin['margin_for_the_company']; |
| 1700 | $updatePayload['margin_on_invoice_per_day_per_worker'] = $overrideMargin['margin_on_invoice_per_day_per_worker']; |
| 1701 | // commission_cost is a percentage OF the amount, so it is just |
| 1702 | // as stale as the margins when the amount moves. |
| 1703 | $updatePayload['commission_cost'] = $overrideMargin['commission_cost']; |
| 1704 | $updatePayload['revenue_per_date_per_worked'] = $overrideMargin['revenue_per_date_per_worked']; |
| 1705 | $updatePayload['gross_margin'] = $overrideMargin['gross_margin']; |
| 1706 | $updatePayload['labor_percentage'] = $overrideMargin['labor_percentage']; |
| 1707 | |
| 1708 | unset( |
| 1709 | $updatePayload['cost_of_labor'], |
| 1710 | $updatePayload['people_assigned_to_the_job'], |
| 1711 | $updatePayload['duration_of_job_in_days'], |
| 1712 | ); |
| 1713 | } |
| 1714 | |
| 1715 | // 2026-07-31 incident, second finding: the sync writes |
| 1716 | // `estimated_cost_of_materials` but never logged it, while it DID |
| 1717 | // log `amount`. In the record history the material change |
| 1718 | // therefore appeared to be the sales rep's own edit — the full |
| 1719 | // trail existed only in `tbl_audit_logs`, which is what made the |
| 1720 | // frozen-margin bug undiagnosable from inside the app. |
| 1721 | // |
| 1722 | // Logged here rather than with the other addUpdateLog calls above |
| 1723 | // because `$totalCostOfMaterial` is only computed further down. |
| 1724 | if (abs((float) ($row->estimated_cost_of_materials ?? 0) - (float) $totalCostOfMaterial) > 0.005) { |
| 1725 | $this->quotationsController->addUpdateLog( |
| 1726 | $row->id, |
| 1727 | 'G3W', |
| 1728 | 'estimated_cost_of_materials', |
| 1729 | $row->estimated_cost_of_materials, |
| 1730 | $totalCostOfMaterial, |
| 1731 | 4 |
| 1732 | ); |
| 1733 | } |
| 1734 | |
| 1735 | // Gestiona's own status before this sync. $row->update() below |
| 1736 | // overwrites it, so it has to be read first. |
| 1737 | $prevStatusByG3w = $row->status_by_g3w; |
| 1738 | |
| 1739 | // Only write when Gestiona actually changed something. The |
| 1740 | // payload always stamps updated_at/updated_by, so an unconditional |
| 1741 | // update rewrote every re-listed quote with no real change, |
| 1742 | // moving updated_at and adding a no-op row to tbl_audit_logs |
| 1743 | // on every 5-minute run. |
| 1744 | if (self::changedSyncFields($row, $updatePayload) !== []) { |
| 1745 | $row->update($updatePayload); |
| 1746 | } else { |
| 1747 | Log::channel('allInfoQuotationsG3w')->debug("Presupuesto {$id} sin cambios en G3W: no se escribe."); |
| 1748 | } |
| 1749 | |
| 1750 | // A send = Gestiona's OWN status becoming Enviado. Keyed on |
| 1751 | // status_by_g3w, not budget_status_id: when Titan moved the |
| 1752 | // quotation away from Enviado (auto-rejection, email bounce, |
| 1753 | // acceptance) while Gestiona still says Enviado, this sync puts |
| 1754 | // Titan's status back to Enviado, and that is a revert, not a send. |
| 1755 | // An edit that re-dates the budget keeps Gestiona at Enviado, so |
| 1756 | // it is not a send either. status_by_g3w is always written (never |
| 1757 | // reverted by a bulk field sync), so each send is seen exactly once. |
| 1758 | if (QuotationSendRecorder::isErpSend($prevStatusByG3w, $nameStatus)) { |
| 1759 | QuotationSendRecorder::record((int) $row->id, QuotationSendRecorder::G3W_STATUS, 'G3W', |
| 1760 | null, $budget['presupuesto']['importe'] ?? null); |
| 1761 | } elseif ($nameStatus === 'Aceptado' && $prevStatusByG3w !== 'Aceptado' |
| 1762 | && QuotationSendRecorder::neverSent((int) $row->id)) { |
| 1763 | // Accepted without ever being sent: the acceptance is the send. |
| 1764 | QuotationSendRecorder::record((int) $row->id, QuotationSendRecorder::ACCEPTANCE, 'G3W', |
| 1765 | $g3wAcceptanceDate ?? null, $budget['presupuesto']['importe'] ?? null); |
| 1766 | } |
| 1767 | } |
| 1768 | |
| 1769 | Log::channel('allInfoQuotationsG3w')->info('Pasamos el update'); |
| 1770 | |
| 1771 | // FIRE-1XXX: Fire send_acceptance_notification when a G3W sync |
| 1772 | // transitions a budget into "Aceptado" status (3). Three gates: |
| 1773 | // 1. The row was NOT already Aceptado (avoid re-emails on |
| 1774 | // re-syncs of already-accepted budgets — would flood the |
| 1775 | // inbox of every commercial on the first deploy otherwise). |
| 1776 | // 2. G3W is reporting the new status as Aceptado. |
| 1777 | // 3. The status field was actually written (in_array check + |
| 1778 | // not blocked by the terminal-status guard at line 1149). |
| 1779 | // The notification function itself routes to the company's |
| 1780 | // "Destinatarios" (tbl_to_acceptance_notifications) + CC list |
| 1781 | // (tbl_cc_acceptance_notifications) + the assigned commercial |
| 1782 | // — see Quotations::send_acceptance_notification:8366. |
| 1783 | $statusWillUpdate = in_array($nameStatus, $statusToChange) || $row->budget_status_id == 6; |
| 1784 | $isTransitionToAccepted = (int) $row->budget_status_id !== 3 |
| 1785 | && $nameStatus === 'Aceptado' |
| 1786 | && $statusWillUpdate |
| 1787 | && ! $isTerminalStatus; |
| 1788 | |
| 1789 | if ($isTransitionToAccepted) { |
| 1790 | // Resolve the commercial's user_id so the notification can |
| 1791 | // CC them. Reuse the same name-then-mapping pattern as the |
| 1792 | // approval-notification path above (FIRE-1063 / FIRE-1092). |
| 1793 | $acceptanceCommercialUser = TblUsers::where('name', $row->commercial)->first(); |
| 1794 | if (! $acceptanceCommercialUser) { |
| 1795 | $mapping = $this->resolveUserMapping($row->commercial, $region); |
| 1796 | if ($mapping && $mapping->id_fst) { |
| 1797 | $acceptanceCommercialUser = TblUsers::where('id', $mapping->id_fst)->first(); |
| 1798 | } |
| 1799 | } |
| 1800 | |
| 1801 | if ($acceptanceCommercialUser) { |
| 1802 | // Defensive: company must have both TO and CC lists |
| 1803 | // configured (the notification function aborts silently |
| 1804 | // otherwise). Surface the gap so we can fix it instead |
| 1805 | // of losing emails quietly. |
| 1806 | $hasTo = TblToAcceptanceNotifications::where('company_id', $companyId)->exists(); |
| 1807 | $hasCc = TblCcAcceptanceNotifications::where('company_id', $companyId)->exists(); |
| 1808 | if (! $hasTo || ! $hasCc) { |
| 1809 | Log::channel('allInfoQuotationsG3w')->warning( |
| 1810 | 'G3W-driven Aceptado transition for budget '.$row->id. |
| 1811 | ' (company '.$companyId.') skipped acceptance notification: '. |
| 1812 | 'tbl_to_acceptance_notifications='.($hasTo ? 'ok' : 'EMPTY'). |
| 1813 | ' tbl_cc_acceptance_notifications='.($hasCc ? 'ok' : 'EMPTY'). |
| 1814 | '. Configure both lists in /notification-settings.' |
| 1815 | ); |
| 1816 | } else { |
| 1817 | try { |
| 1818 | $this->quotationsController->send_acceptance_notification( |
| 1819 | $row->id, |
| 1820 | $companyId, |
| 1821 | $acceptanceCommercialUser->id, |
| 1822 | 'G3W' |
| 1823 | ); |
| 1824 | } catch (\Throwable $notifyException) { |
| 1825 | // Notification failure must not abort the sync. |
| 1826 | Log::channel('allInfoQuotationsG3w')->error( |
| 1827 | 'G3W-driven Aceptado transition: notification failed for budget '. |
| 1828 | $row->id.': '.$notifyException->getMessage() |
| 1829 | ); |
| 1830 | } |
| 1831 | } |
| 1832 | } else { |
| 1833 | Log::channel('allInfoQuotationsG3w')->warning( |
| 1834 | 'G3W-driven Aceptado transition for budget '.$row->id. |
| 1835 | ' has no resolvable commercial user (commercial="'.($row->commercial ?? 'null').'"). '. |
| 1836 | 'Acceptance notification skipped.' |
| 1837 | ); |
| 1838 | } |
| 1839 | } |
| 1840 | |
| 1841 | $companyNameFormatted = ''; |
| 1842 | |
| 1843 | if (! empty($companyName)) { |
| 1844 | $companyNameFormatted = str_replace(' ', '_', $companyName); |
| 1845 | } |
| 1846 | |
| 1847 | $nameDocument = $companyNameFormatted |
| 1848 | ? $budget['presupuesto']['cod_presupuesto'].'_'.$companyNameFormatted |
| 1849 | : $budget['presupuesto']['cod_presupuesto']; |
| 1850 | |
| 1851 | // FIRE-1449: these four log lines used to fire per-budget during a |
| 1852 | // G3W bulk sync (FIRE-1170) and pushed laravel.log + the |
| 1853 | // allInfoQuotationsG3w channel to ~2 GB in a single afternoon, |
| 1854 | // triggering the 2026-07-13 disk-fill outage. Downgraded to |
| 1855 | // debug so a local `LOG_LEVEL=debug` still surfaces them for |
| 1856 | // troubleshooting, but production (`LOG_LEVEL=warning`) doesn't |
| 1857 | // eat them. The `throw` below on the not-found path preserves |
| 1858 | // the error trail via the exception handler. |
| 1859 | Log::channel('allInfoQuotationsG3w')->debug('Nombre del documento: '.$nameDocument); |
| 1860 | |
| 1861 | if (! $row) { |
| 1862 | Log::channel('allInfoQuotationsG3w')->debug('No se encontró la cotización con internal_quote_id: '.$budget['presupuesto']['cod_presupuesto']); |
| 1863 | throw new \Exception("No se encontró la cotización con internal_quote_id: {$budget['presupuesto']['cod_presupuesto']}"); |
| 1864 | } |
| 1865 | |
| 1866 | $response = $this->saveDocument($budget['presupuesto']['documento'], $nameDocument, $row->id, $row->quote_id, 'G3W'); |
| 1867 | $responseData = $response->getData(); |
| 1868 | |
| 1869 | Log::debug('Response data: '.json_encode($responseData)); |
| 1870 | |
| 1871 | if (! isset($responseData->success) || ! $responseData->success) { |
| 1872 | Log::channel('allInfoQuotationsG3w')->debug("No se pudo guardar el documento del presupuesto {$id} al editar. Error: ".($responseData->error ?? 'Desconocido')); |
| 1873 | throw new \Exception("No se pudo guardar el documento del presupuesto {$id} al editar. Error: ".($responseData->error ?? 'Desconocido')); |
| 1874 | } |
| 1875 | |
| 1876 | $work = $this->request('get', "presupuesto/trabajos/{$id}", $region, []); |
| 1877 | |
| 1878 | if (! empty($work) && isset($work[0]['ID'])) { |
| 1879 | $workId = $work[0]['ID']; |
| 1880 | $albaran = $this->request('get', "albaran/{$workId}", $region, []); |
| 1881 | |
| 1882 | if (isset($albaran['albaran']) && isset($albaran['albaran']['certificado'])) { |
| 1883 | $certificado = $albaran['albaran']['certificado']; |
| 1884 | $nameCertificado = $id.'_certificado'; |
| 1885 | |
| 1886 | $response = $this->saveDocument($certificado, $nameCertificado, $row->id, $row->quote_id, 'G3W'); |
| 1887 | $responseData = $response->getData(); |
| 1888 | |
| 1889 | if (! isset($responseData->success) || ! $responseData->success) { |
| 1890 | Log::channel('allInfoQuotationsG3w')->info("No se pudo guardar el certificado del presupuesto {$id} al editar. Error: ".($responseData->error ?? 'Desconocido')); |
| 1891 | throw new \Exception("No se pudo guardar el certificado del presupuesto {$id} al editar. Error: ".($responseData->error ?? 'Desconocido')); |
| 1892 | } |
| 1893 | |
| 1894 | } |
| 1895 | } |
| 1896 | |
| 1897 | Log::channel('allInfoQuotationsG3w')->info('Pasamos la subida de albaran.'); |
| 1898 | |
| 1899 | // FIRE-1229: surface the FST row id so the frontend (sync-by-id |
| 1900 | // page, etc.) can build a "Ver presupuesto" link after an update |
| 1901 | // sync the same way it does after an insert sync via syncById. |
| 1902 | // Without this the response was `{success: true}` and the FE |
| 1903 | // had no way to construct the orders/{id} URL. |
| 1904 | $resultRowId = $row->id; |
| 1905 | |
| 1906 | // FIRE-1025: zero-date sweep moved to the daily |
| 1907 | // `quotations:cleanup-zero-dates` artisan command. Pre-fix this |
| 1908 | // UPDATE ran at the end of every G3W sync cycle (5 call sites |
| 1909 | // here) and deadlocked on `idx_acceptance_date` when concurrent |
| 1910 | // syncs piled up (see InnoDB MONITOR OUTPUT on 2026-04-27 with |
| 1911 | // multiple "WE ROLL BACK TRANSACTION (1)" entries on this exact |
| 1912 | // statement). FIRE-1001 normalises `aceptacion` on write so no |
| 1913 | // new zero-dates can be created — the daily cleanup handles |
| 1914 | // legacy rows. |
| 1915 | |
| 1916 | // FIRE-1145 follow-up: this method rewrote at least one tbl_quotations |
| 1917 | // row. Without forgetting the quotations bucket, list_quotations + |
| 1918 | // analytics caches return stale data for up to the 10-min TTL. |
| 1919 | ResultCache::forgetDomain('quotations'); |
| 1920 | |
| 1921 | return [ |
| 1922 | 'success' => true, |
| 1923 | 'id' => $resultRowId, |
| 1924 | ]; |
| 1925 | |
| 1926 | } catch (QueryException $e) { |
| 1927 | // FIRE-1029 (quote 160286 / 17939, 26-27/04): let DB-layer |
| 1928 | // failures bubble up to the caller's `DB::transaction(...)` |
| 1929 | // wrapper so the whole sync — including the `addUpdateLog` |
| 1930 | // INSERTs earlier in this method — rolls back atomically. |
| 1931 | // |
| 1932 | // Pre-fix this catch swallowed every QueryException and |
| 1933 | // returned `success: false`. From DB::transaction's view the |
| 1934 | // closure returned normally, so it issued COMMIT instead of |
| 1935 | // ROLLBACK, which during the FIRE-1025 deadlock storm |
| 1936 | // produced 100+ ghost change-log rows on quote 17939 while |
| 1937 | // `$row->update([...])` never landed (row's `updated_at` |
| 1938 | // stayed frozen at 2026-04-14 throughout). syncByDate also |
| 1939 | // saw `success: false` and routed the error correctly into |
| 1940 | // tbl_g3w_orders_update_logs — but only when the txn was |
| 1941 | // already dead, so the error trace was incomplete. |
| 1942 | // |
| 1943 | // Logical errors (G3W returned no documento, status not in |
| 1944 | // map, etc.) still throw plain `\Exception` and continue to |
| 1945 | // be returned gracefully via the catch below. |
| 1946 | throw $e; |
| 1947 | } catch (\Exception $e) { |
| 1948 | return ['success' => false, 'error' => "Error actualizando el presupuesto {$id}: ".$e->getMessage()]; |
| 1949 | } |
| 1950 | } |
| 1951 | |
| 1952 | /** |
| 1953 | * FIRE-1001: classify a G3W presupuesto line as labor (true) or |
| 1954 | * material (false). The original sync code only checked the |
| 1955 | * per-region `cod_material` allow-list (`$materialId` in |
| 1956 | * `syncModifiedBudgetById`). That list is incomplete — it only |
| 1957 | * covers Cataluña / Madrid / Comunidad Valenciana, and even there |
| 1958 | * it doesn't enumerate every labor code G3W actually emits. Labor |
| 1959 | * lines that fell through were summed into materials, and when the |
| 1960 | * frontend later recomputed `cost_of_labor = people × days × hours |
| 1961 | * × cost`, the labor hiding inside materials got added a second |
| 1962 | * time → "se duplica la mano de obra". |
| 1963 | * |
| 1964 | * Description-based detection covers the gap: any line whose |
| 1965 | * descripcion mentions "mano de obra" / "horas de trabajo" / |
| 1966 | * "horas técnico|oficial|peón" is treated as labor regardless of |
| 1967 | * its `cod_material`. |
| 1968 | * |
| 1969 | * @param array<string,mixed> $linea Single line from $budget['presupuesto']['lineas'] |
| 1970 | * @param list<int|string> $laborCodes Per-region cod_material allow-list (may be empty) |
| 1971 | */ |
| 1972 | public static function classifyLineAsLabor(array $linea, array $laborCodes): bool |
| 1973 | { |
| 1974 | $code = $linea['cod_material'] ?? null; |
| 1975 | if ($code !== null && in_array($code, $laborCodes, true)) { |
| 1976 | return true; |
| 1977 | } |
| 1978 | $desc = mb_strtolower((string) ($linea['descripcion'] ?? '')); |
| 1979 | if ($desc === '') { |
| 1980 | return false; |
| 1981 | } |
| 1982 | |
| 1983 | return str_contains($desc, 'mano de obra') |
| 1984 | || str_contains($desc, 'mano obra') |
| 1985 | || preg_match('/\bhoras?\s+(de\s+)?trabajo\b/u', $desc) === 1 |
| 1986 | || preg_match('/\bhoras?\s+(de\s+)?(t[eé]cnico|oficial|peon|peón)\b/u', $desc) === 1; |
| 1987 | } |
| 1988 | |
| 1989 | /** |
| 1990 | * FIRE-1063 — treat NULL, empty string, and the literal string |
| 1991 | * 'null' (lowercase or otherwise) all as "blank" when deciding |
| 1992 | * whether to fall back to G3W servicio/cliente data on sync. The |
| 1993 | * 'null' literal shows up in legacy rows from a pre-FIRE-864 import |
| 1994 | * path that string-cast NULL on its way into the column. |
| 1995 | */ |
| 1996 | private static function isBlankClientField(?string $value): bool |
| 1997 | { |
| 1998 | if ($value === null) { |
| 1999 | return true; |
| 2000 | } |
| 2001 | $trimmed = trim($value); |
| 2002 | |
| 2003 | return $trimmed === '' || strtolower($trimmed) === 'null'; |
| 2004 | } |
| 2005 | |
| 2006 | /** |
| 2007 | * @return float[]|int[]|null[] |
| 2008 | */ |
| 2009 | /** |
| 2010 | * FIRE-83: `$cost_of_labor` replaces the old `$hours_per_worker_per_day` and |
| 2011 | * `$cost_of_hour` parameters. Labor cost now comes from the caller (G3W's |
| 2012 | * `Mano de obra` total on sync, or the operator's manual override on |
| 2013 | * update) instead of being derived from people × days × rate, which drifted |
| 2014 | * on every user edit to people/days. |
| 2015 | * |
| 2016 | * `$people_assigned_to_the_job` and `$duration_of_job_in_days` are kept on |
| 2017 | * the signature because per-worker-per-day metrics |
| 2018 | * (`margin_on_invoice_per_day_per_worker`, `revenue_per_date_per_worked`) |
| 2019 | * still need them as denominators — they are informational drivers, not |
| 2020 | * cost-of-labor drivers. |
| 2021 | */ |
| 2022 | public function calculateBudgetMargin($amount, $commission_pct, $people_assigned_to_the_job, $duration_of_job_in_days, $cost_of_labor, $estimated_cost_of_materials, $segmentId, $generalCosts): array |
| 2023 | { |
| 2024 | $results = [ |
| 2025 | 'cost_of_labor' => 0, |
| 2026 | 'total_cost_of_job' => 0, |
| 2027 | 'invoice_margin' => null, |
| 2028 | 'margin_for_the_company' => null, |
| 2029 | 'margin_on_invoice_per_day_per_worker' => null, |
| 2030 | 'commission_cost' => null, |
| 2031 | 'revenue_per_date_per_worked' => 0, |
| 2032 | 'gross_margin' => 0, |
| 2033 | 'labor_percentage' => 0, |
| 2034 | ]; |
| 2035 | |
| 2036 | $parseFloat = function ($value): int|float { |
| 2037 | if ($value === null || $value === '') { |
| 2038 | return 0; |
| 2039 | } |
| 2040 | $cleanValue = str_replace(',', '.', (string) $value); |
| 2041 | |
| 2042 | return is_numeric($cleanValue) ? (float) $cleanValue : 0; |
| 2043 | }; |
| 2044 | |
| 2045 | $amount = $parseFloat($amount ?? 0); |
| 2046 | $commissionPct = $parseFloat($commission_pct ?? 0); |
| 2047 | $peopleAssigned = $parseFloat($people_assigned_to_the_job ?? 0); |
| 2048 | $durationInDays = $parseFloat($duration_of_job_in_days ?? 0); |
| 2049 | $estimatedMaterials = $parseFloat($estimated_cost_of_materials ?? 0); |
| 2050 | $costOfLabor = $parseFloat($cost_of_labor ?? 0); |
| 2051 | |
| 2052 | if ($amount >= 0 && $peopleAssigned >= 0 && $durationInDays >= 0) { |
| 2053 | |
| 2054 | // FIRE-83: total_cost_of_job sums materials + labor — Berta's |
| 2055 | // model is "the two are independent allocations" (the labor line's |
| 2056 | // sale price is treated as a separate service charge on top of the |
| 2057 | // materials cost). Don't try to be clever about double-counting; |
| 2058 | // the FST display matches what G3W's UI shows side-by-side. |
| 2059 | $totalCostOfJob = $costOfLabor + $estimatedMaterials; |
| 2060 | |
| 2061 | if ($commissionPct > 0 && $amount > 0) { |
| 2062 | $commissionCost = ($commissionPct / 100) * $amount; |
| 2063 | } else { |
| 2064 | $commissionCost = 0; |
| 2065 | } |
| 2066 | |
| 2067 | if ($totalCostOfJob > 0 && $amount > 0) { |
| 2068 | if ($segmentId == 7) { |
| 2069 | $invoiceMargin = (($amount - $totalCostOfJob - $commissionCost) / $amount) * 100; |
| 2070 | } else { |
| 2071 | $invoiceMargin = (($amount - $totalCostOfJob) / $amount) * 100; |
| 2072 | } |
| 2073 | |
| 2074 | $marginForTheCompany = $invoiceMargin - $generalCosts; |
| 2075 | |
| 2076 | $marginOnInvoicePerDayPerWorker = ($amount - $estimatedMaterials - $costOfLabor) / |
| 2077 | ($durationInDays ?: 1) / |
| 2078 | ($peopleAssigned ?: 1); |
| 2079 | } else { |
| 2080 | $invoiceMargin = 0; |
| 2081 | $marginForTheCompany = 0; |
| 2082 | $marginOnInvoicePerDayPerWorker = 0; |
| 2083 | } |
| 2084 | |
| 2085 | if ($costOfLabor == 0) { |
| 2086 | $revenuePerDayWorked = 0; |
| 2087 | $laborPercentage = 0; |
| 2088 | } else { |
| 2089 | $revenuePerDayWorked = ($amount / ($peopleAssigned ?: 1) / ($durationInDays ?: 1)); |
| 2090 | $laborPercentage = ($costOfLabor / $amount) * 100; |
| 2091 | } |
| 2092 | |
| 2093 | $grossMargin = $amount > 0 ? (($amount - $estimatedMaterials) / $amount) * 100 : 0; |
| 2094 | |
| 2095 | $results['cost_of_labor'] = $costOfLabor; |
| 2096 | $results['total_cost_of_job'] = $totalCostOfJob; |
| 2097 | $results['invoice_margin'] = $invoiceMargin; |
| 2098 | $results['margin_for_the_company'] = $marginForTheCompany; |
| 2099 | $results['margin_on_invoice_per_day_per_worker'] = $marginOnInvoicePerDayPerWorker; |
| 2100 | $results['commission_cost'] = $commissionCost; |
| 2101 | $results['revenue_per_date_per_worked'] = $revenuePerDayWorked; |
| 2102 | $results['gross_margin'] = $grossMargin; |
| 2103 | $results['labor_percentage'] = $laborPercentage; |
| 2104 | |
| 2105 | } else { |
| 2106 | foreach ($results as $key => $val) { |
| 2107 | $results[$key] = null; |
| 2108 | } |
| 2109 | } |
| 2110 | |
| 2111 | return $results; |
| 2112 | } |
| 2113 | |
| 2114 | /** |
| 2115 | * Synchronize budgets that gave us an error. |
| 2116 | * |
| 2117 | * @param string $name Who's launch the function |
| 2118 | */ |
| 2119 | public function syncErrorBudgets($name = null, $region = null): array |
| 2120 | { |
| 2121 | try { |
| 2122 | $region = $this->normalizeRegion($region); |
| 2123 | |
| 2124 | $g3wActive = $this->resolveCompanyForRegion($region)?->g3W_active; |
| 2125 | |
| 2126 | if (! $g3wActive) { |
| 2127 | throw new Exception("La sincronización con G3W debe estar desactivada en la region '$region'."); |
| 2128 | } |
| 2129 | |
| 2130 | $this->setSyncStatus(1, $region); |
| 2131 | |
| 2132 | $successfulSyncs = 0; |
| 2133 | $failedSyncs = []; |
| 2134 | $successIdSyncs = []; |
| 2135 | |
| 2136 | $startCronDateTime = date('Y-m-d H:i:s'); |
| 2137 | |
| 2138 | $company = $this->resolveCompanyForRegion($region); |
| 2139 | |
| 2140 | if (! $company) { |
| 2141 | throw new \Exception('No company found for region: '.$region); |
| 2142 | } |
| 2143 | $company_id = $company->company_id; |
| 2144 | $logs = TblG3WOrdersUpdateLogs::whereNotNull('sync_error_ids') |
| 2145 | ->where('company_id', $company_id) |
| 2146 | ->get(['sync_error_ids']); |
| 2147 | |
| 2148 | $allSyncErrorIds = []; |
| 2149 | |
| 2150 | foreach ($logs as $log) { |
| 2151 | if (is_string($log->sync_error_ids)) { |
| 2152 | $decodedIds = json_decode($log->sync_error_ids, true); |
| 2153 | if (is_array($decodedIds)) { |
| 2154 | $log->sync_error_ids = json_encode($decodedIds); // Fixed: Encode array back to JSON string |
| 2155 | $allSyncErrorIds = array_merge($allSyncErrorIds, $decodedIds); // Fixed: Merge the decoded array |
| 2156 | } |
| 2157 | } |
| 2158 | } |
| 2159 | |
| 2160 | $allSyncErrorIds = array_unique($allSyncErrorIds); |
| 2161 | |
| 2162 | foreach ($allSyncErrorIds as $idSyncError) { |
| 2163 | $result = DB::transaction(fn () => $this->syncById($idSyncError, $region)); |
| 2164 | |
| 2165 | if ($result['success']) { |
| 2166 | $successfulSyncs++; |
| 2167 | $successIdSyncs[] = [ |
| 2168 | 'id' => $idSyncError, |
| 2169 | ]; |
| 2170 | } else { |
| 2171 | if (! str_contains((string) $result['error'], 'No se ha encontrado el presupuesto')) { |
| 2172 | $failedSyncs[] = [ |
| 2173 | 'id' => $idSyncError, |
| 2174 | 'error' => $result['error'] ?? 'Unknown error', |
| 2175 | ]; |
| 2176 | } |
| 2177 | } |
| 2178 | } |
| 2179 | |
| 2180 | $this->markG3wImport($region, 0); |
| 2181 | |
| 2182 | $company = $this->resolveCompanyForRegion($region); |
| 2183 | if (! $company) { |
| 2184 | throw new \Exception('No company found for region: '.$region); |
| 2185 | } |
| 2186 | $company_id = $company->company_id; |
| 2187 | |
| 2188 | $logs = TblG3WOrdersUpdateLogs::whereNotNull('sync_error_ids') |
| 2189 | ->where('company_id', $company_id) |
| 2190 | ->update(['sync_error_ids' => []]); |
| 2191 | |
| 2192 | $this->updateLogs($failedSyncs, $successfulSyncs, $successIdSyncs, $startCronDateTime, $name, $region); |
| 2193 | |
| 2194 | return [ |
| 2195 | 'success' => true, |
| 2196 | 'message' => 'Synchronization of failed budgets completed.', |
| 2197 | ]; |
| 2198 | |
| 2199 | } catch (\Exception $e) { |
| 2200 | Log::channel('g3w')->error('Error when synchronizing error budgets: '.$e->getMessage()); |
| 2201 | |
| 2202 | $this->releaseSyncFlag($region); |
| 2203 | |
| 2204 | return ['success' => false, 'error' => $e->getMessage()]; |
| 2205 | } |
| 2206 | } |
| 2207 | |
| 2208 | public function syncBudgetsWorks($name = null, $region = null, ?int $limit = null): array |
| 2209 | { |
| 2210 | try { |
| 2211 | $region = $this->normalizeRegion($region); |
| 2212 | |
| 2213 | $company = $this->resolveCompanyForRegion($region); |
| 2214 | |
| 2215 | if (! $company) { |
| 2216 | throw new \Exception("No se encontró la compañÃa para la región '$region'."); |
| 2217 | } |
| 2218 | $g3wActive = $company->g3W_active; |
| 2219 | |
| 2220 | if (! $g3wActive) { |
| 2221 | throw new Exception("La sincronización con G3W debe estar activada en la región '$region'."); |
| 2222 | } |
| 2223 | |
| 2224 | $this->workSevice->getG3wTasksExecuted($region, 1); |
| 2225 | |
| 2226 | $this->setSyncStatus(1, $region); |
| 2227 | |
| 2228 | $successfulSyncs = 0; |
| 2229 | $failedSyncs = []; |
| 2230 | $successIdSyncs = []; |
| 2231 | |
| 2232 | $startCronDateTime = date('Y-m-d H:i:s'); |
| 2233 | |
| 2234 | $quotesIdsQuery = TblQuotations::where(function ($query): void { |
| 2235 | $query->where('sync_import', 1) |
| 2236 | ->orWhere('sync_import_edited', 1); |
| 2237 | }) |
| 2238 | ->where('budget_type_id', 1) |
| 2239 | ->where(function ($query): void { |
| 2240 | $query->whereNull('box_work_g3w') |
| 2241 | ->orWhereIn('box_work_g3w', ['', '0']); |
| 2242 | }); |
| 2243 | |
| 2244 | if ($limit !== null) { |
| 2245 | $quotesIdsQuery->limit($limit); |
| 2246 | } |
| 2247 | |
| 2248 | $quotesIds = $quotesIdsQuery->get(); |
| 2249 | |
| 2250 | foreach ($quotesIds as $quoteId) { |
| 2251 | try { |
| 2252 | $work = $this->request('get', "presupuesto/trabajos/{$quoteId->internal_quote_id}", $region, []); |
| 2253 | |
| 2254 | sleep(2); |
| 2255 | |
| 2256 | $workIds = []; |
| 2257 | |
| 2258 | if (is_array($work)) { |
| 2259 | foreach ($work as $item) { |
| 2260 | if (isset($item['ID'])) { |
| 2261 | $workIds[] = $item['ID']; |
| 2262 | } |
| 2263 | } |
| 2264 | } |
| 2265 | |
| 2266 | // FIRE-1097: skip the write when G3W has no trabajo yet. |
| 2267 | // Pre-fix, implode('/', []) wrote '' and locked the row out |
| 2268 | // of future runs (the WHERE only matched NULL/'0'). |
| 2269 | if (empty($workIds)) { |
| 2270 | continue; |
| 2271 | } |
| 2272 | |
| 2273 | $idsConcatenados = implode('/', $workIds); |
| 2274 | |
| 2275 | $wasUpdated = $quoteId->update(['box_work_g3w' => $idsConcatenados]); |
| 2276 | |
| 2277 | if ($wasUpdated) { |
| 2278 | $successfulSyncs++; |
| 2279 | $successIdSyncs[] = [ |
| 2280 | 'id' => $quoteId->internal_quote_id, |
| 2281 | ]; |
| 2282 | } else { |
| 2283 | $failedSyncs[] = [ |
| 2284 | 'id' => $quoteId->internal_quote_id, |
| 2285 | 'error' => "Error updating the internal quote id $quoteId, work $idsConcatenados.", |
| 2286 | ]; |
| 2287 | } |
| 2288 | } catch (\Throwable $e) { |
| 2289 | $failedSyncs[] = [ |
| 2290 | 'id' => $quoteId->internal_quote_id, |
| 2291 | 'error' => $e->getMessage(), |
| 2292 | ]; |
| 2293 | Log::channel('g3w')->warning("sync-work failed for {$quoteId->internal_quote_id} in {$region}: ".$e->getMessage()); |
| 2294 | } |
| 2295 | } |
| 2296 | |
| 2297 | $this->markG3wImport($region, 0); |
| 2298 | |
| 2299 | // FIRE-1025: zero-date sweep moved to the daily |
| 2300 | // `quotations:cleanup-zero-dates` artisan command. Pre-fix this |
| 2301 | // UPDATE ran at the end of every G3W sync cycle (5 call sites |
| 2302 | // here) and deadlocked on `idx_acceptance_date` when concurrent |
| 2303 | // syncs piled up (see InnoDB MONITOR OUTPUT on 2026-04-27 with |
| 2304 | // multiple "WE ROLL BACK TRANSACTION (1)" entries on this exact |
| 2305 | // statement). FIRE-1001 normalises `aceptacion` on write so no |
| 2306 | // new zero-dates can be created — the daily cleanup handles |
| 2307 | // legacy rows. |
| 2308 | |
| 2309 | $this->updateLogs($failedSyncs, $successfulSyncs, $successIdSyncs, $startCronDateTime, $name, $region, 'Orders Works'); |
| 2310 | |
| 2311 | return [ |
| 2312 | 'success' => true, |
| 2313 | 'message' => 'Synchronization of budgets works completed.', |
| 2314 | ]; |
| 2315 | |
| 2316 | } catch (\Exception $e) { |
| 2317 | Log::channel('g3w')->error('Error when synchronizing budgets works: '.$e->getMessage()); |
| 2318 | |
| 2319 | $this->releaseSyncFlag($region); |
| 2320 | |
| 2321 | return ['success' => false, 'error' => $e->getMessage()]; |
| 2322 | } |
| 2323 | } |
| 2324 | |
| 2325 | /** |
| 2326 | * Coerce a failure payload into the list-of-maps shape the rest of the |
| 2327 | * logging path assumes: [['id' => int, 'error' => string], ...]. |
| 2328 | * |
| 2329 | * Accepts a single failure map and wraps it, because that is precisely the |
| 2330 | * mistake that broke `quotations:sync` (PHP-LARAVEL-1M). Anything that |
| 2331 | * isn't a map is dropped rather than passed on — `array_column()` and |
| 2332 | * `notifyErrors()` both assume every element has 'id' and 'error'. |
| 2333 | * |
| 2334 | * @return list<array<string, mixed>> |
| 2335 | */ |
| 2336 | private function normalizeFailures($failures): array |
| 2337 | { |
| 2338 | if (! is_array($failures) || $failures === []) { |
| 2339 | return []; |
| 2340 | } |
| 2341 | |
| 2342 | // A single ['id' => .., 'error' => ..] map rather than a list of them. |
| 2343 | if (array_key_exists('id', $failures) || array_key_exists('error', $failures)) { |
| 2344 | return [$failures]; |
| 2345 | } |
| 2346 | |
| 2347 | return array_values(array_filter($failures, is_array(...))); |
| 2348 | } |
| 2349 | |
| 2350 | public function notifyErrors($failedSyncs): void |
| 2351 | { |
| 2352 | $failedSyncs = $this->normalizeFailures($failedSyncs); |
| 2353 | |
| 2354 | if ($failedSyncs === []) { |
| 2355 | return; |
| 2356 | } |
| 2357 | |
| 2358 | // Null-coalesced: a failure built by a caller that omitted either key |
| 2359 | // should still produce a readable line instead of an undefined-index |
| 2360 | // warning that Laravel escalates to an ErrorException. |
| 2361 | $errorDetails = array_map( |
| 2362 | fn (array $failure) => 'Budget ID: '.($failure['id'] ?? 0).', Error: '.($failure['error'] ?? 'unknown'), |
| 2363 | $failedSyncs |
| 2364 | ); |
| 2365 | $message = implode("\n", $errorDetails); |
| 2366 | /* Mail::luis, rick & chris, tech@fire.es */ |
| 2367 | |
| 2368 | Log::channel('g3w')->error('Error notification sent to ricardo.alemany@fire.es'); |
| 2369 | } |
| 2370 | |
| 2371 | /** |
| 2372 | * Function to generate the next QuoteID |
| 2373 | * |
| 2374 | * @return array{id: int, number: string} |
| 2375 | */ |
| 2376 | /*public function generateQuoteId($companyId) |
| 2377 | { |
| 2378 | if ($companyId == 0) { |
| 2379 | $latestQuoteId = TblQuotations::orderBy('id', 'DESC')->first()->quote_id ?? null; |
| 2380 | } else { |
| 2381 | $latestQuoteId = TblQuotations::where('company_id', $companyId) |
| 2382 | ->orderBy('id', 'DESC') |
| 2383 | ->first() |
| 2384 | ->quote_id ?? null; |
| 2385 | } |
| 2386 | |
| 2387 | if (!$latestQuoteId) { |
| 2388 | throw new \Exception("Error generando el # en titan"); |
| 2389 | } |
| 2390 | |
| 2391 | if (is_numeric($latestQuoteId)) { |
| 2392 | return (string)((int)$latestQuoteId + 1); |
| 2393 | } |
| 2394 | |
| 2395 | preg_match('/([A-Z]+)(\d+)/', $latestQuoteId, $matches); |
| 2396 | |
| 2397 | if (count($matches) < 3) { |
| 2398 | throw new \Exception("El formato del último Quote ID no es válido: {$latestQuoteId}"); |
| 2399 | } |
| 2400 | |
| 2401 | $prefix = $matches[1]; |
| 2402 | $numericPart = $matches[2]; |
| 2403 | |
| 2404 | $incrementedNumber = str_pad((int)$numericPart + 1, strlen($numericPart), '0', STR_PAD_LEFT); |
| 2405 | |
| 2406 | $newQuoteId = $prefix . $incrementedNumber; |
| 2407 | |
| 2408 | return $newQuoteId; |
| 2409 | }*/ |
| 2410 | public function generateQuoteId($companyId): array|ResponseFactory|Response |
| 2411 | { |
| 2412 | try { |
| 2413 | |
| 2414 | $companyId = addslashes((string) $companyId); |
| 2415 | $latestBudget = []; |
| 2416 | $number = 0; |
| 2417 | $beforeLastId = null; |
| 2418 | |
| 2419 | $x = true; |
| 2420 | |
| 2421 | if ($companyId == 0) { |
| 2422 | $latestBudget = TblQuotations::orderByRaw('CAST(quote_id AS DOUBLE) DESC')->value('quote_id'); |
| 2423 | } else { |
| 2424 | $latestBudget = TblCompanies::where('company_id', $companyId)->value('last_id'); |
| 2425 | |
| 2426 | if ($latestBudget == null) { |
| 2427 | $latestBudget = TblQuotations::where('company_id', $companyId)->orderByRaw('id DESC')->value('quote_id'); |
| 2428 | $beforeLastId = $latestBudget; |
| 2429 | } |
| 2430 | } |
| 2431 | |
| 2432 | $number = $latestBudget; |
| 2433 | |
| 2434 | while ($x) { |
| 2435 | |
| 2436 | if (is_numeric(substr((string) $number, -1))) { |
| 2437 | $number++; |
| 2438 | } else { |
| 2439 | $number .= '1'; |
| 2440 | } |
| 2441 | |
| 2442 | $check = 0; |
| 2443 | |
| 2444 | if ($companyId == 0) { |
| 2445 | $check = TblQuotations::where('quote_id', (string) $number)->count(); |
| 2446 | } else { |
| 2447 | $check = TblQuotations::where('company_id', $companyId)->where('quote_id', (string) $number)->count(); |
| 2448 | } |
| 2449 | |
| 2450 | if ($check == 0) { |
| 2451 | $x = false; |
| 2452 | } |
| 2453 | } |
| 2454 | |
| 2455 | $result = TblQuotations::create(['quote_id' => $number, 'company_id' => $companyId, 'for_add' => 1]); |
| 2456 | |
| 2457 | if ($beforeLastId == null) { |
| 2458 | $beforeLastId = $number; |
| 2459 | } |
| 2460 | |
| 2461 | $query = "UPDATE tbl_companies SET last_id = '{$number}', before_last_id = CASE WHEN before_last_id IS NULL THEN '{$beforeLastId}' ELSE before_last_id END WHERE company_id = {$companyId}"; |
| 2462 | DB::select($query); |
| 2463 | |
| 2464 | return [ |
| 2465 | 'id' => $result->id, |
| 2466 | 'number' => $number, |
| 2467 | ]; |
| 2468 | |
| 2469 | } catch (\Exception $e) { |
| 2470 | throw $e; |
| 2471 | } |
| 2472 | } |
| 2473 | |
| 2474 | /** |
| 2475 | * Resolve a G3W user-mapping row scoped to a region. |
| 2476 | * |
| 2477 | * User mappings are region-scoped (each region is an independent G3W |
| 2478 | * instance, so the same code can point at different FST users). A single |
| 2479 | * code can also have multiple rows within one region — a placeholder |
| 2480 | * (id_fst NULL, created by an earlier sync) plus a real match added later |
| 2481 | * once a human linked it. When $requireFst is true we prefer a row that |
| 2482 | * actually points at an FST user (newest wins), so a lingering placeholder |
| 2483 | * never shadows a resolvable mapping. |
| 2484 | */ |
| 2485 | private function resolveUserMapping(?string $nameG3w, string $region, bool $requireFst = false): ?TblUserG3wMapping |
| 2486 | { |
| 2487 | if ($nameG3w === null || $nameG3w === '') { |
| 2488 | return null; |
| 2489 | } |
| 2490 | |
| 2491 | $region = $region === 'Catalunya' ? 'Cataluña' : $region; |
| 2492 | |
| 2493 | $cacheKey = $region."\0".$nameG3w."\0".(int) $requireFst; |
| 2494 | if (isset($this->userMappingCache[$cacheKey])) { |
| 2495 | return $this->userMappingCache[$cacheKey]; |
| 2496 | } |
| 2497 | |
| 2498 | $query = TblUserG3wMapping::where('name_g3w', $nameG3w) |
| 2499 | ->where('region', $region); |
| 2500 | |
| 2501 | if ($requireFst) { |
| 2502 | $query->whereNotNull('id_fst')->orderByDesc('id'); |
| 2503 | } |
| 2504 | |
| 2505 | return $this->userMappingCache[$cacheKey] = $query->first(); |
| 2506 | } |
| 2507 | |
| 2508 | /** Memoised TblBudgetStatus id by name — the same 2 names are read for every budget. */ |
| 2509 | private function budgetStatusIdByName(string $name) |
| 2510 | { |
| 2511 | return $this->budgetStatusIdByNameCache[$name] ??= TblBudgetStatus::where('name', $name)->first()->budget_status_id; |
| 2512 | } |
| 2513 | |
| 2514 | /** |
| 2515 | * Function to normalice the status provided by G3W. |
| 2516 | * |
| 2517 | * @param $status String Row status of G3W |
| 2518 | * @return int ID normalized in FST |
| 2519 | * |
| 2520 | * @throws \Exception |
| 2521 | */ |
| 2522 | private function normalizeStatus($status) |
| 2523 | { |
| 2524 | if (! $status) { |
| 2525 | return $this->budgetStatusIdByName('Sin estado en G3W'); |
| 2526 | } |
| 2527 | |
| 2528 | $statusMapping = $this->statusMappingCache[$status] ??= TblStatusG3wMapping::where('name_g3w', $status)->first(); |
| 2529 | |
| 2530 | if (! $statusMapping) { |
| 2531 | TblStatusG3wMapping::create([ |
| 2532 | 'name_g3w' => $status, |
| 2533 | 'budget_status_id' => 0, |
| 2534 | ]); |
| 2535 | |
| 2536 | return $this->budgetStatusIdByName('Estado no reconocido en FST'); |
| 2537 | } |
| 2538 | |
| 2539 | return $statusMapping->budget_status_id; |
| 2540 | |
| 2541 | } |
| 2542 | |
| 2543 | /** |
| 2544 | * Function to normalice the segment provided by G3W. |
| 2545 | * |
| 2546 | * @param $status String Row status of G3W |
| 2547 | * @return int ID normalized in FST |
| 2548 | * |
| 2549 | * @throws \Exception |
| 2550 | */ |
| 2551 | private function normalizeSegment($segment) |
| 2552 | { |
| 2553 | $segmentMapping = TblSegmentG3wMapping::where('name_g3w', $segment)->first(); |
| 2554 | |
| 2555 | if (! $segmentMapping) { |
| 2556 | TblSegmentG3wMapping::create([ |
| 2557 | 'name_g3w' => $segment, |
| 2558 | 'segment_id' => 0, |
| 2559 | ]); |
| 2560 | |
| 2561 | return TblSegments::where('name', 'Otro')->first()->segment_id ?? 9; |
| 2562 | } |
| 2563 | |
| 2564 | return $segmentMapping->segment_id; |
| 2565 | } |
| 2566 | |
| 2567 | /** |
| 2568 | * Function to normalize the budget type provided by G3W. |
| 2569 | * |
| 2570 | * @param int $type ID del tipo proporcionado por la API. |
| 2571 | * @param string $region Región (Madrid o Cataluña). |
| 2572 | * @return int ID normalizado del presupuesto en el sistema. |
| 2573 | * |
| 2574 | * @throws \Exception |
| 2575 | */ |
| 2576 | private function normalizeType($type, $region) |
| 2577 | { |
| 2578 | if ($region === 'Catalunya') { |
| 2579 | $region = 'Cataluña'; |
| 2580 | } |
| 2581 | |
| 2582 | // Hits only: the unknown-type path below (placeholder + throw) is |
| 2583 | // never cached, so it behaves exactly as before. |
| 2584 | $cacheKey = json_encode([$region, $type]); |
| 2585 | if (array_key_exists($cacheKey, $this->typeIdCache)) { |
| 2586 | return $this->typeIdCache[$cacheKey]; |
| 2587 | } |
| 2588 | |
| 2589 | $budgetTypeMapping = TblTypeG3wMapping::where('id_g3w', $type) |
| 2590 | ->where('region', $region) |
| 2591 | ->first(); |
| 2592 | |
| 2593 | if (! $budgetTypeMapping) { |
| 2594 | TblTypeG3wMapping::create([ |
| 2595 | 'id_g3w' => $type ?? null, |
| 2596 | 'budget_type_name' => null, |
| 2597 | 'region' => $region, |
| 2598 | ]); |
| 2599 | throw new \Exception("El estado '$type' no existe en la base de datos."); |
| 2600 | } |
| 2601 | |
| 2602 | $budgetType = TblBudgetTypes::where('name', $budgetTypeMapping->budget_type_name)->first(); |
| 2603 | |
| 2604 | if (! $budgetType) { |
| 2605 | return $this->typeIdCache[$cacheKey] = 16; |
| 2606 | } |
| 2607 | |
| 2608 | return $this->typeIdCache[$cacheKey] = $budgetType->budget_type_id; |
| 2609 | } |
| 2610 | |
| 2611 | private function normalizeSource($id_call, $region) |
| 2612 | { |
| 2613 | $regionTxt = ''; |
| 2614 | $region = $region == 'Catalunya' ? 'Cataluña' : $region; |
| 2615 | |
| 2616 | if (! $id_call) { |
| 2617 | $sourceDefault = TblSources::where('name', 'G3W/Gestiona')->first(); |
| 2618 | |
| 2619 | return $sourceDefault->source_id ?? 20; |
| 2620 | } |
| 2621 | |
| 2622 | $sourceMapping = TblSourceG3wMapping::where('id_g3w', $id_call) |
| 2623 | ->where('region', $region) |
| 2624 | ->first(); |
| 2625 | |
| 2626 | if (! $sourceMapping) { |
| 2627 | TblSourceG3wMapping::create([ |
| 2628 | 'id_g3w' => $id_call, |
| 2629 | 'source_name' => null, |
| 2630 | 'region' => $region, |
| 2631 | ]); |
| 2632 | |
| 2633 | return null; |
| 2634 | } |
| 2635 | |
| 2636 | $sourceId = TblSources::where('name', $sourceMapping->source_name)->first(); |
| 2637 | |
| 2638 | if (! $sourceId && $region == 'Andalucía') { |
| 2639 | return 68; |
| 2640 | } |
| 2641 | |
| 2642 | if (! $sourceId) { |
| 2643 | return null; |
| 2644 | } |
| 2645 | |
| 2646 | return $sourceId->source_id; |
| 2647 | |
| 2648 | } |
| 2649 | |
| 2650 | /** |
| 2651 | * @return JsonResponse |
| 2652 | */ |
| 2653 | public function saveDocument($document, $nameDocument = null, $quotationId = null, $quoteId = null, $uploadedBy = null, $isInternal = null) |
| 2654 | { |
| 2655 | |
| 2656 | try { |
| 2657 | |
| 2658 | $binaryData = base64_decode((string) $document); |
| 2659 | |
| 2660 | if (! $binaryData) { |
| 2661 | throw new \Exception('Los datos Base64 no son válidos.'); |
| 2662 | } |
| 2663 | |
| 2664 | $documentName = $nameDocument |
| 2665 | ? preg_replace('/[^A-Za-z0-9_\-.]/', '', (string) $nameDocument) |
| 2666 | : 'document_'.time().'.pdf'; |
| 2667 | |
| 2668 | if (! preg_match('/\.pdf$/i', $documentName)) { |
| 2669 | $documentName .= '.pdf'; |
| 2670 | } |
| 2671 | |
| 2672 | // FIRE-1110: the S3 key must be unique across regions and across |
| 2673 | // concurrent syncs. The old `time()` suffix was second-precision, |
| 2674 | // and $documentName collides across Gestiona instances because |
| 2675 | // each region issues its own cod_presupuesto sequence. Madrid's |
| 2676 | // budget 6815 and Valencia's budget 6815 would land on the same |
| 2677 | // S3 path and overwrite each other (confirmed in prod for order |
| 2678 | // 50783: S3 LastModified was 5 months after the DB uploaded_at). |
| 2679 | // |
| 2680 | // We prepend the quotation's company_id and replace time() with |
| 2681 | // uniqid('', true) — microsecond + random — so two writes can |
| 2682 | // never produce the same key. |
| 2683 | $companyPrefix = ''; |
| 2684 | if ($quotationId !== null) { |
| 2685 | $companyId = TblQuotations::where('id', $quotationId)->value('company_id'); |
| 2686 | if ($companyId !== null) { |
| 2687 | $companyPrefix = 'c'.$companyId.'-'; |
| 2688 | } |
| 2689 | } |
| 2690 | $filename = $companyPrefix |
| 2691 | .pathinfo($documentName, PATHINFO_FILENAME) |
| 2692 | .'_'.uniqid('', true) |
| 2693 | .'.pdf'; |
| 2694 | |
| 2695 | $fileSize = strlen($binaryData); |
| 2696 | $mimeType = 'application/pdf'; |
| 2697 | |
| 2698 | $fileDataBase = TblFiles::where('quotation_id', $quotationId)->get(); |
| 2699 | |
| 2700 | if ($fileDataBase->isNotEmpty()) { |
| 2701 | foreach ($fileDataBase as $fileData) { |
| 2702 | if ($fileData->original_name == $documentName) { |
| 2703 | $fileData->delete(); |
| 2704 | } |
| 2705 | } |
| 2706 | } |
| 2707 | |
| 2708 | $s3path = Storage::disk('s3')->put( |
| 2709 | 'uploads/'.$filename, |
| 2710 | $binaryData, |
| 2711 | [ |
| 2712 | 'ContentType' => $mimeType, |
| 2713 | ] |
| 2714 | ); |
| 2715 | |
| 2716 | $file = TblFiles::create([ |
| 2717 | 'quotation_id' => $quotationId, |
| 2718 | 'original_name' => $documentName, |
| 2719 | 'filename' => $filename, |
| 2720 | 'uploaded_by' => $uploadedBy, |
| 2721 | 'file_size' => $fileSize, |
| 2722 | 'mime_type' => $mimeType, |
| 2723 | 'uploaded_at' => date('Y-m-d H:i:s'), |
| 2724 | ]); |
| 2725 | |
| 2726 | $this->quotationsController->addUpdateLog($quotationId, $uploadedBy, 'upload_attachment', null, $filename, 4); |
| 2727 | |
| 2728 | return response()->json([ |
| 2729 | 'success' => true, |
| 2730 | 'message' => 'Documento guardado correctamente en la base de datos.', |
| 2731 | 'filename' => $filename, |
| 2732 | 'fileId' => $file->file_id, |
| 2733 | 'documentName' => $documentName, |
| 2734 | ], 200); |
| 2735 | |
| 2736 | } catch (\Exception $e) { |
| 2737 | return response()->json([ |
| 2738 | 'success' => false, |
| 2739 | 'error' => $e->getMessage(), |
| 2740 | ], 500); |
| 2741 | } |
| 2742 | } |
| 2743 | |
| 2744 | /** |
| 2745 | * Formatear bytes a formato legible |
| 2746 | */ |
| 2747 | private function formatBytes($bytes, $precision = 2) |
| 2748 | { |
| 2749 | $units = ['B', 'KB', 'MB', 'GB', 'TB']; |
| 2750 | |
| 2751 | $bytes = max($bytes, 0); |
| 2752 | $pow = floor(($bytes ? log($bytes) : 0) / log(1024)); |
| 2753 | $pow = min($pow, count($units) - 1); |
| 2754 | |
| 2755 | $bytes /= pow(1024, $pow); |
| 2756 | |
| 2757 | return round($bytes, $precision).' '.$units[$pow]; |
| 2758 | } |
| 2759 | |
| 2760 | public function updateLogs($failedSyncs, $successfulSyncs, $successIdSyncs, $startCronDateTime, $name, $region, $process = null): void |
| 2761 | { |
| 2762 | // PHP-LARAVEL-1M: every caller must pass a LIST of failures. Four call |
| 2763 | // sites passed a single ['id' => .., 'error' => ..] map instead, so the |
| 2764 | // array_map() in notifyErrors() iterated that map's VALUES and handed |
| 2765 | // the int id to a closure typed `array $failure`. That TypeError killed |
| 2766 | // every `quotations:sync` run — 288 times a day from 2026-06-22 to |
| 2767 | // 2026-09-01. The call sites are fixed; normalising here means a future |
| 2768 | // one can only produce a wrong log row, never a dead cron. |
| 2769 | $failedSyncs = $this->normalizeFailures($failedSyncs); |
| 2770 | |
| 2771 | // Was `Log::error($failedSyncs)` — an array where Monolog wants a |
| 2772 | // string, and it fired on every successful sync too. Per the FIRE-1449 |
| 2773 | // log-volume convention, only say something when there is something. |
| 2774 | if ($failedSyncs !== []) { |
| 2775 | Log::channel('g3w')->error('G3W sync failures', ['failures' => $failedSyncs]); |
| 2776 | } |
| 2777 | |
| 2778 | $region = $this->normalizeRegion($region); |
| 2779 | |
| 2780 | $companyId = $this->resolveCompanyForRegion($region)?->company_id; |
| 2781 | $syncStatus = 'Success'; |
| 2782 | $failedSyncsTxt = ''; |
| 2783 | |
| 2784 | if (! empty($failedSyncs)) { |
| 2785 | $syncStatus = ($successfulSyncs > 0) ? 'Partially failed' : 'Failed'; |
| 2786 | $this->notifyErrors($failedSyncs); |
| 2787 | $errorsArray = array_column($failedSyncs, 'error'); |
| 2788 | $failedSyncsTxt = implode(', ', $errorsArray); |
| 2789 | } |
| 2790 | |
| 2791 | $idsError = array_map(intval(...), array_column($failedSyncs, 'id')); |
| 2792 | $idsError = array_unique($idsError); |
| 2793 | $idsErrorCount = count($idsError); |
| 2794 | $idsErrorJson = json_encode($idsError); |
| 2795 | |
| 2796 | $idsSuccess = array_map(intval(...), array_column($successIdSyncs, 'id')); |
| 2797 | $idsSuccess = array_unique($idsSuccess); |
| 2798 | $idsSuccessCount = count($idsSuccess); |
| 2799 | $idsSuccessJson = json_encode($idsSuccess); |
| 2800 | |
| 2801 | TblG3WOrdersUpdateLogs::create( |
| 2802 | [ |
| 2803 | 'company_id' => $companyId, |
| 2804 | 'to_process' => $process ?? 'Orders', |
| 2805 | 'status' => $syncStatus, |
| 2806 | 'sync_succesfull' => $idsSuccessCount, |
| 2807 | 'sync_error' => $idsErrorCount, |
| 2808 | 'sync_error_message' => $failedSyncsTxt, |
| 2809 | 'sync_error_ids' => $idsErrorJson, |
| 2810 | 'sync_success_ids' => $idsSuccessJson, |
| 2811 | 'processed_by' => $name, |
| 2812 | 'started_at' => $startCronDateTime, |
| 2813 | // Falls back to now() when the region has no row yet (or has |
| 2814 | // never synced): the log entry is being written *because* a |
| 2815 | // cycle just ended, so it must not fatal on a missing stamp. |
| 2816 | 'ended_at' => (TblG3wLastUpdate::where('region', $region)->first()?->updated_at ?? now())->format('Y-m-d H:i:s'), |
| 2817 | ] |
| 2818 | ); |
| 2819 | } |
| 2820 | |
| 2821 | /** |
| 2822 | * Acceptance date for a budget Gestiona reports as "Aceptado" without one: |
| 2823 | * the day Gestiona last modified it (that change is normally the |
| 2824 | * acceptance), or today when that is missing, invalid or in the future. |
| 2825 | */ |
| 2826 | public static function acceptanceDateFallback(?string $g3wModifiedAt, \DateTimeInterface $now): string |
| 2827 | { |
| 2828 | $now = Carbon::instance($now); |
| 2829 | $raw = is_string($g3wModifiedAt) ? trim($g3wModifiedAt) : ''; |
| 2830 | |
| 2831 | if (preg_match('/^\d{4}-\d{2}-\d{2}/', $raw) && ! str_starts_with($raw, '0000')) { |
| 2832 | try { |
| 2833 | $modified = Carbon::parse(substr($raw, 0, 10)); |
| 2834 | if ($modified->lte($now)) { |
| 2835 | return $modified->format('Y-m-d'); |
| 2836 | } |
| 2837 | } catch (\Throwable) { |
| 2838 | // fall through to today |
| 2839 | } |
| 2840 | } |
| 2841 | |
| 2842 | return $now->format('Y-m-d'); |
| 2843 | } |
| 2844 | |
| 2845 | /** Overlap with the previous run, against clock skew between Titan and G3W. */ |
| 2846 | private const MODIFIED_WINDOW_MARGIN_MINUTES = 15; |
| 2847 | |
| 2848 | /** Never look further back than this in one run (cursor lost or stale). */ |
| 2849 | private const MODIFIED_WINDOW_MAX_DAYS = 7; |
| 2850 | |
| 2851 | /** |
| 2852 | * The `presupuesto/fechamodificacion` calls a run must make, as |
| 2853 | * [date, time|null] pairs (null = the whole day). |
| 2854 | * |
| 2855 | * - Catch-up for a past date ($date not today): that whole day. |
| 2856 | * - Live run with a cursor: from the cursor minus a margin, then every |
| 2857 | * following day in full, up to today. |
| 2858 | * - Live run, no cursor yet: the last hour (the old behaviour), once. |
| 2859 | * |
| 2860 | * @return list<array{0:string,1:?string}> |
| 2861 | */ |
| 2862 | public static function modifiedBudgetWindows($syncedUntil, string $date, \DateTimeInterface $now): array |
| 2863 | { |
| 2864 | $now = Carbon::instance($now); |
| 2865 | if ($date !== $now->toDateString()) { |
| 2866 | return [[$date, null]]; |
| 2867 | } |
| 2868 | |
| 2869 | $from = $syncedUntil |
| 2870 | ? Carbon::parse($syncedUntil)->subMinutes(self::MODIFIED_WINDOW_MARGIN_MINUTES) |
| 2871 | : $now->copy()->subHour(); |
| 2872 | |
| 2873 | $floor = $now->copy()->subDays(self::MODIFIED_WINDOW_MAX_DAYS)->startOfDay(); |
| 2874 | if ($from->lt($floor)) { |
| 2875 | Log::channel('g3w')->warning('G3W modified window capped at '.self::MODIFIED_WINDOW_MAX_DAYS.' days (cursor '.$from->toDateTimeString().').'); |
| 2876 | $from = $floor; |
| 2877 | } |
| 2878 | if ($from->gt($now)) { |
| 2879 | $from = $now->copy(); |
| 2880 | } |
| 2881 | |
| 2882 | $windows = [[$from->toDateString(), $from->format('H:i')]]; |
| 2883 | for ($day = $from->copy()->addDay()->startOfDay(); $day->lte($now); $day->addDay()) { |
| 2884 | $windows[] = [$day->toDateString(), null]; |
| 2885 | } |
| 2886 | |
| 2887 | return $windows; |
| 2888 | } |
| 2889 | |
| 2890 | /** |
| 2891 | * True when calculateBudgetMargin() produced real figures: a non-zero |
| 2892 | * invoice margin or a job cost above zero. Same rule as the |
| 2893 | * budget_margin_enabled backfill. |
| 2894 | */ |
| 2895 | public static function hasMarginData(array $resultMargin): bool |
| 2896 | { |
| 2897 | $margin = $resultMargin['invoice_margin'] ?? null; |
| 2898 | $cost = $resultMargin['total_cost_of_job'] ?? null; |
| 2899 | |
| 2900 | return (is_numeric($margin) && (float) $margin != 0.0) |
| 2901 | || (is_numeric($cost) && (float) $cost > 0); |
| 2902 | } |
| 2903 | |
| 2904 | /** |
| 2905 | * Drop repeated budget IDs from a Gestiona list, keeping the first entry. |
| 2906 | * |
| 2907 | * @param array<int,mixed> $budgets |
| 2908 | * @return array<int,mixed> |
| 2909 | */ |
| 2910 | public static function uniqueBudgetsById(array $budgets): array |
| 2911 | { |
| 2912 | $seen = []; |
| 2913 | $unique = []; |
| 2914 | foreach ($budgets as $budget) { |
| 2915 | $key = is_array($budget) && isset($budget['ID']) ? (string) $budget['ID'] : null; |
| 2916 | if ($key !== null) { |
| 2917 | if (isset($seen[$key])) { |
| 2918 | continue; |
| 2919 | } |
| 2920 | $seen[$key] = true; |
| 2921 | } |
| 2922 | $unique[] = $budget; |
| 2923 | } |
| 2924 | |
| 2925 | return $unique; |
| 2926 | } |
| 2927 | |
| 2928 | /** |
| 2929 | * Fields of the sync payload whose value differs from what the row |
| 2930 | * already stores. updated_at / updated_by are bookkeeping and never |
| 2931 | * count as a change on their own. |
| 2932 | * |
| 2933 | * Values are compared the way MySQL stores them: the numeric columns |
| 2934 | * are varchar, so "1500.0000" and 1500.0 are the same value; datetimes |
| 2935 | * compare by instant, so G3W's "2026-04-01" equals the stored |
| 2936 | * "2026-04-01 00:00:00". |
| 2937 | * |
| 2938 | * @param array<string,mixed> $payload |
| 2939 | * @return list<string> |
| 2940 | */ |
| 2941 | public static function changedSyncFields(object $row, array $payload): array |
| 2942 | { |
| 2943 | $changed = []; |
| 2944 | foreach ($payload as $field => $new) { |
| 2945 | if ($field === 'updated_at' || $field === 'updated_by') { |
| 2946 | continue; |
| 2947 | } |
| 2948 | // update() silently drops non-fillable keys (e.g. has_attachment |
| 2949 | // on TblQuotations), so they can never differ after a write. |
| 2950 | if ($row instanceof Model && ! $row->isFillable($field)) { |
| 2951 | continue; |
| 2952 | } |
| 2953 | $old = $row instanceof Model |
| 2954 | ? $row->getRawOriginal($field) |
| 2955 | : ($row->{$field} ?? null); |
| 2956 | |
| 2957 | if (! self::sameStoredValue($old, $new)) { |
| 2958 | $changed[] = $field; |
| 2959 | } |
| 2960 | } |
| 2961 | |
| 2962 | return $changed; |
| 2963 | } |
| 2964 | |
| 2965 | private static function sameStoredValue($old, $new): bool |
| 2966 | { |
| 2967 | if ($old === null || $new === null) { |
| 2968 | return $old === $new; |
| 2969 | } |
| 2970 | if ($old instanceof \DateTimeInterface) { |
| 2971 | $old = $old->format('Y-m-d H:i:s'); |
| 2972 | } |
| 2973 | if ($new instanceof \DateTimeInterface) { |
| 2974 | $new = $new->format('Y-m-d H:i:s'); |
| 2975 | } |
| 2976 | if (is_bool($new)) { |
| 2977 | $new = (int) $new; |
| 2978 | } |
| 2979 | $old = (string) $old; |
| 2980 | $new = (string) $new; |
| 2981 | if ($old === $new) { |
| 2982 | return true; |
| 2983 | } |
| 2984 | if (is_numeric($old) && is_numeric($new)) { |
| 2985 | return abs((float) $old - (float) $new) <= 1e-9 * max(1.0, abs((float) $old)); |
| 2986 | } |
| 2987 | $dateLike = '/^\d{4}-\d{2}-\d{2}( \d{2}:\d{2}(:\d{2})?)?$/'; |
| 2988 | if (preg_match($dateLike, $old) && preg_match($dateLike, $new)) { |
| 2989 | return strtotime($old) === strtotime($new); |
| 2990 | } |
| 2991 | |
| 2992 | return false; |
| 2993 | } |
| 2994 | |
| 2995 | private function checkEmailInvalid($email) |
| 2996 | { |
| 2997 | $emailPattern = "/^[\w\.\-]+@([\w\-]+\.)+[a-zA-Z]{2,}$/"; |
| 2998 | |
| 2999 | $emails = explode(',', $email); |
| 3000 | |
| 3001 | $emailInvalid = false; |
| 3002 | |
| 3003 | foreach ($emails as $email) { |
| 3004 | if (! preg_match($emailPattern, $email)) { |
| 3005 | $emailInvalid = true; |
| 3006 | break; |
| 3007 | } |
| 3008 | } |
| 3009 | |
| 3010 | return $emailInvalid; |
| 3011 | } |
| 3012 | |
| 3013 | private function checkRequiredFields($data): ?string |
| 3014 | { |
| 3015 | // FIRE-1741: a missing required field is only actionable in a narrow |
| 3016 | // set of statuses. Everywhere else the row legitimately arrives |
| 3017 | // without a full dataset (Solicitud drafts, En proceso work-in-flight, |
| 3018 | // closed Aceptado / Rechazado / Anulado / Visita, etc.) and firing |
| 3019 | // the warning there just fills "Fallos en G3W" with noise — measured |
| 3020 | // in prod on 2026-08-27 as 1,806 of 1,901 flagged rows (~94 %). |
| 3021 | // |
| 3022 | // Whitelist confirmed with Ricardo (2026-08-27): |
| 3023 | // 2 Enviado — already out at the client, must have data |
| 3024 | // 11 Listo para enviar — about to send, must have data |
| 3025 | // 13 Sin estado en G3W — state itself is the defect ("Estado") |
| 3026 | // 14 Estado no reconocido en FST — same |
| 3027 | // 17 Enviado IA — semantically equivalent to Enviado |
| 3028 | // |
| 3029 | // Supersedes the FIRE-1560 display-side blacklist (which stayed in |
| 3030 | // place as a redundant safety net) and the 2026-08-26 single-status |
| 3031 | // early return for Solicitud (6). 13/14 are kept in the whitelist |
| 3032 | // because they're the only statuses where "Estado" is a legitimate |
| 3033 | // missing-field entry; dropping them would silence a real problem. |
| 3034 | if (! in_array($data->budget_status_id, [2, 11, 13, 14, 17], true)) { |
| 3035 | return null; |
| 3036 | } |
| 3037 | |
| 3038 | $g3wWarningFields = []; |
| 3039 | |
| 3040 | if ($data->budget_status_id == 13 || $data->budget_status_id == 14) { |
| 3041 | array_push($g3wWarningFields, 'Estado'); |
| 3042 | } |
| 3043 | |
| 3044 | if ($data->budget_type_id == null || $data->budget_type_id == '' || $data->budget_type_id == 0 || $data->budget_type_id == 16) { |
| 3045 | array_push($g3wWarningFields, 'Tipo'); |
| 3046 | } |
| 3047 | |
| 3048 | if ($data->commercial == null || $data->commercial == '') { |
| 3049 | array_push($g3wWarningFields, 'Comercial'); |
| 3050 | } |
| 3051 | |
| 3052 | if ($data->source_id == null || $data->source_id == '') { |
| 3053 | array_push($g3wWarningFields, 'Fuente'); |
| 3054 | } |
| 3055 | |
| 3056 | // FIRE-1377: only treat a missing/invalid email as a warning when Email |
| 3057 | // is the selected send channel. For 'correo'/'whatsapp' the email is |
| 3058 | // irrelevant, so it must not appear in g3w_warning_fields (and a row |
| 3059 | // whose only "issue" was the email then resolves to no warnings, which |
| 3060 | // clears the field on the next sync). NULL send_channel = legacy rows, |
| 3061 | // treated as email → unchanged behaviour. |
| 3062 | $sendChannel = $data->send_channel ?? 'email'; |
| 3063 | if ($sendChannel === 'email' |
| 3064 | && ($data->email == null || $data->email == '' || $this->isBlacklistedEmail($data->email))) { |
| 3065 | array_push($g3wWarningFields, 'Email'); |
| 3066 | } |
| 3067 | |
| 3068 | if ($data->client == null || $data->client == '') { |
| 3069 | array_push($g3wWarningFields, 'Datos cliente'); |
| 3070 | } |
| 3071 | |
| 3072 | // FIRE-1741: statuses 1 and 3 were in the historical list here but the |
| 3073 | // whitelist above blocks them before this point — the surviving set |
| 3074 | // is exactly the send-critical statuses `2`, `11`, `17`. Kept explicit |
| 3075 | // instead of dropping the guard entirely so the invariant reads at |
| 3076 | // this line without hunting through the earlier early return. |
| 3077 | if (($data->amount == null || $data->amount == 0) && in_array($data->budget_status_id, [2, 11, 17])) { |
| 3078 | array_push($g3wWarningFields, 'Importe'); |
| 3079 | } |
| 3080 | |
| 3081 | if (! empty($g3wWarningFields)) { |
| 3082 | return implode(', ', $g3wWarningFields); |
| 3083 | } else { |
| 3084 | return null; |
| 3085 | } |
| 3086 | } |
| 3087 | |
| 3088 | /** |
| 3089 | * FIRE-1278: true when the G3W required-field warning list contains a |
| 3090 | * problem OTHER than the email. A budget whose only issue is the email |
| 3091 | * belongs in the "Emails con error" section (it is routed to the |
| 3092 | * "Correo erroneo" status bucket and caught by the email-error query), |
| 3093 | * so it must not raise the "Fallos en G3W" warning flag. |
| 3094 | */ |
| 3095 | private function hasNonEmailWarning(?string $g3wWarningFields): bool |
| 3096 | { |
| 3097 | if ($g3wWarningFields === null || $g3wWarningFields === '') { |
| 3098 | return false; |
| 3099 | } |
| 3100 | |
| 3101 | $fields = array_map('trim', explode(',', $g3wWarningFields)); |
| 3102 | |
| 3103 | return ! empty(array_filter($fields, fn ($field) => $field !== 'Email')); |
| 3104 | } |
| 3105 | |
| 3106 | /** |
| 3107 | * FIRE-1551: fuente única de verdad para el flag `g3w_warning` y la |
| 3108 | * columna `g3w_warning_fields`. Combina checkRequiredFields() + |
| 3109 | * hasNonEmailWarning() para que los callers (sync G3W, update_quotation, |
| 3110 | * create_quotation, comandos de backfill) obtengan ambos valores en |
| 3111 | * una sola llamada y siempre alineados entre sí. |
| 3112 | * |
| 3113 | * El `$row` debe traer al menos: budget_status_id, budget_type_id, |
| 3114 | * commercial, source_id, send_channel, email, client, amount. Los |
| 3115 | * callers pueden pasar un objeto Eloquent, un stdClass o un array |
| 3116 | * casteado a objeto — solo lee propiedades públicas. |
| 3117 | * |
| 3118 | * Retorna: |
| 3119 | * [ |
| 3120 | * 'warning' => 0|1, // valor listo para persistir en g3w_warning |
| 3121 | * 'fields' => ?string, // CSV listo para persistir en g3w_warning_fields |
| 3122 | * ] |
| 3123 | */ |
| 3124 | public function computeG3wWarning(object $row): array |
| 3125 | { |
| 3126 | $fields = $this->checkRequiredFields($row); |
| 3127 | |
| 3128 | return [ |
| 3129 | 'warning' => $this->hasNonEmailWarning($fields) ? 1 : 0, |
| 3130 | 'fields' => $fields, |
| 3131 | ]; |
| 3132 | } |
| 3133 | |
| 3134 | private function isBlacklistedEmail(?string $email): bool |
| 3135 | { |
| 3136 | if (! $email || trim($email) === '') { |
| 3137 | return true; |
| 3138 | } |
| 3139 | |
| 3140 | $pattern = '/^no@|^nomail@nomail|^notiene@notiene|tiene\.email|test\.com|prueba\.com/i'; |
| 3141 | |
| 3142 | $emails = explode(',', $email); |
| 3143 | foreach ($emails as $e) { |
| 3144 | if (preg_match($pattern, trim($e))) { |
| 3145 | return true; |
| 3146 | } |
| 3147 | } |
| 3148 | |
| 3149 | return false; |
| 3150 | } |
| 3151 | |
| 3152 | public function syncExistingDataWithWarnings(): void |
| 3153 | { |
| 3154 | |
| 3155 | $budgets = TblQuotations::where('g3w_warning', 1)->get(); |
| 3156 | |
| 3157 | if (count($budgets) > 0) { |
| 3158 | foreach ($budgets as $item) { |
| 3159 | $g3wWarning = 0; |
| 3160 | $g3wWarningFields = $this->checkRequiredFields($item); |
| 3161 | |
| 3162 | // FIRE-1278: email-only problems don't belong in "Fallos en |
| 3163 | // G3W" — see hasNonEmailWarning(). Re-syncing existing rows |
| 3164 | // clears the flag for budgets whose only issue is the email. |
| 3165 | if ($this->hasNonEmailWarning($g3wWarningFields)) { |
| 3166 | $g3wWarning = 1; |
| 3167 | } |
| 3168 | |
| 3169 | // FIRE-2115: this runs at the start of EVERY region's sync |
| 3170 | // job and covers every warning quote in every company. The |
| 3171 | // unconditional update (Eloquent stamps updated_at) rewrote |
| 3172 | // each of them ~6 times per 5-minute run, ~13,000 audit rows |
| 3173 | // an hour that changed nothing but updated_at. Only write |
| 3174 | // when the warning itself changed. |
| 3175 | if ((int) $item->g3w_warning !== $g3wWarning |
| 3176 | || (string) $item->g3w_warning_fields !== (string) $g3wWarningFields) { |
| 3177 | TblQuotations::where('id', $item->id)->update( |
| 3178 | [ |
| 3179 | 'g3w_warning' => $g3wWarning, |
| 3180 | 'g3w_warning_fields' => $g3wWarningFields, |
| 3181 | ] |
| 3182 | ); |
| 3183 | } |
| 3184 | |
| 3185 | $g3wWarningFields = null; |
| 3186 | } |
| 3187 | } |
| 3188 | } |
| 3189 | |
| 3190 | /** |
| 3191 | * FIRE-1279 follow-up: the G3W sync reclassifies imported budgets whose |
| 3192 | * only problem is a bad email into status 22 (Correo erroneo), but |
| 3193 | * manually-created ("non-gestiona") budgets are never in that loop — |
| 3194 | * they only get reclassified when a human opens and saves them via |
| 3195 | * Quotations::update_quotation. This sweep applies the same rule to |
| 3196 | * manual budgets in bulk so they land in the "Emails con error" section |
| 3197 | * with the correct status, without waiting for a manual edit. |
| 3198 | * |
| 3199 | * A "manual" budget is one that did not come from G3W: |
| 3200 | * `sync_import <> 1 OR sync_import IS NULL` — the same definition the |
| 3201 | * `manual` filter uses in Quotations::list_quotations (:4294). |
| 3202 | * |
| 3203 | * Two transitions, both mirroring the manual-save path so behaviour is |
| 3204 | * identical whether a budget is touched by a human or by this cron: |
| 3205 | * - promote: invalid email + status IN (1,2,11,17) -> 22 (Correo |
| 3206 | * erroneo). Same rule as update_quotation:1959. |
| 3207 | * - demote: status 22 + no SendGrid send on record (x_message_id |
| 3208 | * NULL) + now-valid email -> 11 (Listo para enviar). Same rule as |
| 3209 | * the FIRE-1063 demotion at update_quotation:1831 — without it a row |
| 3210 | * fixed offline would sit in "Correo erroneo" forever. |
| 3211 | * |
| 3212 | * "Invalid email" = empty, malformed, or blacklisted — the same union |
| 3213 | * the "Emails con error" list query checks (Quotations.php:3420). |
| 3214 | * |
| 3215 | * @return array{success: bool, promoted: int, demoted: int} |
| 3216 | */ |
| 3217 | public function syncManualBudgetEmailStatus(): array |
| 3218 | { |
| 3219 | $promoted = 0; |
| 3220 | $demoted = 0; |
| 3221 | $affectedCompanies = []; |
| 3222 | |
| 3223 | // Only scan statuses the transitions can act on, so we don't walk |
| 3224 | // the whole table: 1,2,11,17 can promote to 22; 22 can demote to 11. |
| 3225 | $budgets = TblQuotations::query() |
| 3226 | ->where(function ($q) { |
| 3227 | $q->where('sync_import', '!=', 1)->orWhereNull('sync_import'); |
| 3228 | }) |
| 3229 | ->whereIn('budget_status_id', [1, 2, 11, 17, 22]) |
| 3230 | ->get(['id', 'company_id', 'email', 'budget_status_id', 'x_message_id']); |
| 3231 | |
| 3232 | foreach ($budgets as $budget) { |
| 3233 | $emailInvalid = $this->isManualEmailInvalid($budget->email); |
| 3234 | $currentStatus = (int) $budget->budget_status_id; |
| 3235 | $newStatus = null; |
| 3236 | |
| 3237 | if ($emailInvalid && in_array($currentStatus, [1, 2, 11, 17], true)) { |
| 3238 | $newStatus = 22; |
| 3239 | } elseif ($currentStatus === 22 && $budget->x_message_id === null && ! $emailInvalid) { |
| 3240 | $newStatus = 11; |
| 3241 | } |
| 3242 | |
| 3243 | if ($newStatus === null || $newStatus === $currentStatus) { |
| 3244 | continue; |
| 3245 | } |
| 3246 | |
| 3247 | TblQuotations::where('id', $budget->id)->update(['budget_status_id' => $newStatus]); |
| 3248 | $this->quotationsController->addUpdateLog($budget->id, 'System', 'budget_status_id', $budget->budget_status_id, $newStatus, 4); |
| 3249 | |
| 3250 | if ($newStatus === 22) { |
| 3251 | $promoted++; |
| 3252 | } else { |
| 3253 | $demoted++; |
| 3254 | } |
| 3255 | $affectedCompanies[$budget->company_id] = true; |
| 3256 | } |
| 3257 | |
| 3258 | if ($promoted > 0 || $demoted > 0) { |
| 3259 | // Status changes move rows in/out of the Correo erroneo bucket and |
| 3260 | // shift per-commercial counters — mirror the invalidation the G3W |
| 3261 | // sync and clear:email-errors already do. |
| 3262 | ResultCache::forgetDomain(['quotations', 'users']); |
| 3263 | foreach (array_keys($affectedCompanies) as $companyId) { |
| 3264 | try { |
| 3265 | $this->quotationsController->update_commercial_numbers($companyId); |
| 3266 | } catch (\Throwable $e) { |
| 3267 | Log::channel('allInfoQuotationsG3w')->warning( |
| 3268 | "syncManualBudgetEmailStatus: update_commercial_numbers failed for company {$companyId}: ".$e->getMessage() |
| 3269 | ); |
| 3270 | } |
| 3271 | } |
| 3272 | } |
| 3273 | |
| 3274 | return ['success' => true, 'promoted' => $promoted, 'demoted' => $demoted]; |
| 3275 | } |
| 3276 | |
| 3277 | /** |
| 3278 | * FIRE-1279 follow-up: union of the three "bad email" checks the |
| 3279 | * "Emails con error" list query applies (empty / malformed / blacklisted), |
| 3280 | * expressed via the service's existing helpers so the cron and the list |
| 3281 | * agree on what counts as invalid. |
| 3282 | */ |
| 3283 | private function isManualEmailInvalid(?string $email): bool |
| 3284 | { |
| 3285 | if ($email === null || trim($email) === '') { |
| 3286 | return true; |
| 3287 | } |
| 3288 | |
| 3289 | return $this->checkEmailInvalid($email) || $this->isBlacklistedEmail($email); |
| 3290 | } |
| 3291 | |
| 3292 | /** |
| 3293 | * Split the comma-separated id list accepted by `/api/sync-by-ids`. |
| 3294 | * |
| 3295 | * Trims each entry and drops empties, so "123, 456," yields ['123','456']. |
| 3296 | * A bare explode() left the surrounding spaces on every id after the first, |
| 3297 | * which G3W then answered with "no se ha encontrado el presupuesto" — and |
| 3298 | * those errors are deliberately not counted as failures, so the ids |
| 3299 | * vanished without trace. Same idiom as BulkSyncRun. |
| 3300 | * |
| 3301 | * @return list<string> |
| 3302 | */ |
| 3303 | public static function parseSyncIds($ids): array |
| 3304 | { |
| 3305 | if ($ids === null || $ids === '' || $ids === []) { |
| 3306 | return []; |
| 3307 | } |
| 3308 | |
| 3309 | $raw = is_array($ids) ? $ids : explode(',', (string) $ids); |
| 3310 | |
| 3311 | return array_values(array_filter( |
| 3312 | array_map(static fn ($id): string => trim((string) $id), $raw), |
| 3313 | static fn (string $id): bool => $id !== '', |
| 3314 | )); |
| 3315 | } |
| 3316 | |
| 3317 | public function syncByIds($ids, $region, $date, $user = 'System'): array |
| 3318 | { |
| 3319 | try { |
| 3320 | if (! $ids) { |
| 3321 | throw new \Exception('No ids provided'); |
| 3322 | } |
| 3323 | |
| 3324 | $arrayIds = self::parseSyncIds($ids); |
| 3325 | |
| 3326 | if ($arrayIds === []) { |
| 3327 | throw new \Exception('No ids provided'); |
| 3328 | } |
| 3329 | |
| 3330 | $company = $this->resolveCompanyForRegion($region); |
| 3331 | |
| 3332 | if (! $company) { |
| 3333 | throw new \Exception("No se encontró la compañía para la región '$region'."); |
| 3334 | } |
| 3335 | |
| 3336 | if (! $company->g3W_active) { |
| 3337 | throw new Exception("La sincronización con G3W debe estar desactivada en la region '$region'."); |
| 3338 | } |
| 3339 | |
| 3340 | $this->setSyncStatus(1, $region); |
| 3341 | $this->syncExistingDataWithWarnings(); |
| 3342 | |
| 3343 | $successfulSyncs = 0; |
| 3344 | $failedSyncs = []; |
| 3345 | $successIdSyncs = []; |
| 3346 | $notFoundIds = []; |
| 3347 | |
| 3348 | $startCronDateTime = date('Y-m-d H:i:s'); |
| 3349 | |
| 3350 | $isNullInDate = TblQuotations::where('company_id', $company->company_id) |
| 3351 | ->where(function ($query): void { |
| 3352 | $query->where('sync_import', 1) |
| 3353 | ->orWhere('sync_import_edited', 1); |
| 3354 | }) |
| 3355 | ->whereDate('created_at', $date) |
| 3356 | ->where('internal_quote_id', null) |
| 3357 | ->exists(); |
| 3358 | |
| 3359 | foreach ($arrayIds as $id) { |
| 3360 | $result['success'] = false; |
| 3361 | $result['error'] = 'Error en sync by Ids'; |
| 3362 | if ($isNullInDate) { |
| 3363 | $result['success'] = DB::transaction(fn () => $this->syncNullBudget($id, $region, $company->company_id, $date)); |
| 3364 | } |
| 3365 | |
| 3366 | if (! $result['success']) { |
| 3367 | $result = DB::transaction(fn () => $this->syncById($id, $region)); |
| 3368 | } |
| 3369 | |
| 3370 | if ($result['success']) { |
| 3371 | $successfulSyncs++; |
| 3372 | $successIdSyncs[] = [ |
| 3373 | 'id' => $id, |
| 3374 | ]; |
| 3375 | |
| 3376 | // FIRE-1476: after CLM→Madrid merge, the historic 18/22 |
| 3377 | // dual-lookup fallback is dead — quotations only live under |
| 3378 | // 18. Single query is enough. |
| 3379 | $quote = TblQuotations::where('company_id', $company->company_id) |
| 3380 | ->where('internal_quote_id', $id) |
| 3381 | ->first(); |
| 3382 | |
| 3383 | if ($quote) { |
| 3384 | $quote->update([ |
| 3385 | 'created_at' => Carbon::parse($date), |
| 3386 | ]); |
| 3387 | } |
| 3388 | |
| 3389 | } elseif (str_contains((string) $result['error'], 'No se ha encontrado el presupuesto')) { |
| 3390 | // Deliberately kept OUT of $failedSyncs: updateLogs() feeds |
| 3391 | // sync_error_ids, and getResyncSummary() treats a G3W 404 as |
| 3392 | // terminal rather than actionable work (FIRE-982). Counting |
| 3393 | // it there would inflate the resync monitor indefinitely. |
| 3394 | // |
| 3395 | // It is still reported to the caller, though. Silently |
| 3396 | // dropping it was why an import of ids that do not exist |
| 3397 | // came back indistinguishable from a successful one. |
| 3398 | $notFoundIds[] = $id; |
| 3399 | } else { |
| 3400 | $failedSyncs[] = [ |
| 3401 | 'id' => $id, |
| 3402 | 'error' => $result['error'] ?? 'Unknown error', |
| 3403 | ]; |
| 3404 | } |
| 3405 | } |
| 3406 | |
| 3407 | $this->markG3wImport($region, 0); |
| 3408 | |
| 3409 | // FIRE-1025: zero-date sweep moved to the daily |
| 3410 | // `quotations:cleanup-zero-dates` artisan command. Pre-fix this |
| 3411 | // UPDATE ran at the end of every G3W sync cycle (5 call sites |
| 3412 | // here) and deadlocked on `idx_acceptance_date` when concurrent |
| 3413 | // syncs piled up (see InnoDB MONITOR OUTPUT on 2026-04-27 with |
| 3414 | // multiple "WE ROLL BACK TRANSACTION (1)" entries on this exact |
| 3415 | // statement). FIRE-1001 normalises `aceptacion` on write so no |
| 3416 | // new zero-dates can be created — the daily cleanup handles |
| 3417 | // legacy rows. |
| 3418 | |
| 3419 | $this->updateLogs($failedSyncs, $successfulSyncs, $successIdSyncs, $startCronDateTime, $user, $region); |
| 3420 | |
| 3421 | // `success` keeps its original meaning — the request ran without a |
| 3422 | // fatal error — so existing callers are unaffected. What was missing |
| 3423 | // is any statement of WHAT happened: the old fixed |
| 3424 | // "Synchronization completed." was returned whether six budgets |
| 3425 | // imported or none did, which left the UI guessing and guessing |
| 3426 | // wrong. The counts below are the authoritative outcome. |
| 3427 | $requested = count($arrayIds); |
| 3428 | |
| 3429 | return [ |
| 3430 | 'success' => true, |
| 3431 | 'message' => sprintf( |
| 3432 | '%d/%d synchronized%s%s.', |
| 3433 | $successfulSyncs, |
| 3434 | $requested, |
| 3435 | $notFoundIds === [] ? '' : ', '.count($notFoundIds).' not found in G3W', |
| 3436 | $failedSyncs === [] ? '' : ', '.count($failedSyncs).' failed', |
| 3437 | ), |
| 3438 | 'requested' => $requested, |
| 3439 | 'synced' => $successfulSyncs, |
| 3440 | 'synced_ids' => array_column($successIdSyncs, 'id'), |
| 3441 | 'not_found_ids' => $notFoundIds, |
| 3442 | 'failed' => $failedSyncs, |
| 3443 | ]; |
| 3444 | } catch (\Exception $e) { |
| 3445 | Log::channel('g3w')->error('Error sincronizando los presupuestos: '.$e->getMessage()); |
| 3446 | |
| 3447 | $this->releaseSyncFlag($region); |
| 3448 | |
| 3449 | return ['success' => false, 'error' => $e->getMessage()]; |
| 3450 | } |
| 3451 | |
| 3452 | } |
| 3453 | |
| 3454 | private function syncNullBudget(string $id, $region, $companyId, $date): bool |
| 3455 | { |
| 3456 | $budget = $this->request('get', "presupuesto/{$id}", $region, []); |
| 3457 | |
| 3458 | if (! isset($budget['presupuesto']) || ! is_array($budget['presupuesto'])) { |
| 3459 | throw new \Exception('El presupuesto no contiene los datos esperados.'); |
| 3460 | } |
| 3461 | |
| 3462 | $quote = TblQuotations::where('company_id', $companyId) |
| 3463 | ->where(function ($query): void { |
| 3464 | $query->where('sync_import', 1) |
| 3465 | ->orWhere('sync_import_edited', 1); |
| 3466 | }) |
| 3467 | ->whereDate('created_at', $date) |
| 3468 | ->where('internal_quote_id', null) |
| 3469 | ->where('source_by_g3w', $budget['presupuesto']['cod_empresa_presupuesto'] ?? null) |
| 3470 | ->where('type_by_g3w', $budget['presupuesto']['origen_presupuesto'] ?? null) |
| 3471 | ->where('user_create_by_g3w', $budget['presupuesto']['usuario']) |
| 3472 | ->where('user_commercial_by_g3w', ! empty($budget['presupuesto']['cod_comercial_presupuesto']) ? $budget['presupuesto']['cod_comercial_presupuesto'] : null) |
| 3473 | ->first(); |
| 3474 | |
| 3475 | if (! $quote) { |
| 3476 | return false; |
| 3477 | } |
| 3478 | |
| 3479 | $quote->update([ |
| 3480 | 'internal_quote_id' => $id, |
| 3481 | ]); |
| 3482 | |
| 3483 | return true; |
| 3484 | } |
| 3485 | |
| 3486 | public function getAlternativeClientData($texto): array |
| 3487 | { |
| 3488 | preg_match('/[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}/', (string) $texto, $email); |
| 3489 | preg_match('/\b\d{9}\b/', (string) $texto, $number); |
| 3490 | |
| 3491 | $nombre = null; |
| 3492 | $lineas = explode("\n", (string) $texto); |
| 3493 | foreach ($lineas as $linea) { |
| 3494 | |
| 3495 | $linea = trim($linea); |
| 3496 | |
| 3497 | if (empty($linea) || |
| 3498 | preg_match('/@|\b\d{9}\b|C\/|CALLE|AVENIDA|PLAZA|CARRER|\d{5}/i', $linea)) { |
| 3499 | continue; |
| 3500 | } |
| 3501 | |
| 3502 | if (preg_match('/^[A-Za-zÃÉÃÓÚáéÃóúÑñ\s\.]+$/', $linea) && |
| 3503 | ! preg_match('/\d/', $linea) && |
| 3504 | substr_count($linea, ' ') >= 1 && |
| 3505 | strlen($linea) > 5) { |
| 3506 | |
| 3507 | $candidatos[] = $linea; |
| 3508 | } |
| 3509 | } |
| 3510 | |
| 3511 | if (! empty($candidatos)) { |
| 3512 | usort($candidatos, function ($a, $b): int { |
| 3513 | $scoreA = (! str_contains($a, '.') ? 2 : 0) + strlen($a); |
| 3514 | $scoreB = (! str_contains($b, '.') ? 2 : 0) + strlen($b); |
| 3515 | |
| 3516 | return $scoreB - $scoreA; |
| 3517 | }); |
| 3518 | $nombre = rtrim($candidatos[0], '.'); |
| 3519 | } |
| 3520 | |
| 3521 | return [ |
| 3522 | 'email' => $email[0] ?? null, |
| 3523 | 'number' => $number[0] ?? null, |
| 3524 | 'name' => $nombre, |
| 3525 | ]; |
| 3526 | } |
| 3527 | |
| 3528 | /** |
| 3529 | * Backwards-compatible wrapper around the split compute/notify methods. |
| 3530 | * Old callers that just want "tell me the level and send the email" |
| 3531 | * keep working unchanged. `syncModifiedBudgetById` (the FIRE-1092 |
| 3532 | * caller) now uses the split methods directly so it can decide whether |
| 3533 | * the recomputed level differs from the current one before deciding |
| 3534 | * to send a fresh notification. |
| 3535 | */ |
| 3536 | private static function checkAproval($companyId, $budgetTypeId, $customerTypeId, $amount, $quoteId, $id, $commercialId, $comercial): ?int |
| 3537 | { |
| 3538 | $forApproval = self::computeForApprovalLevel($companyId, $budgetTypeId, $customerTypeId, $amount); |
| 3539 | |
| 3540 | if ($forApproval !== null) { |
| 3541 | // FIRE-1229: pass the computed level through so the email routes |
| 3542 | // to `tbl_approvers_v2` when dual-level (3) — same value that |
| 3543 | // gets written to `for_approval` / `approval_type` on the row. |
| 3544 | self::sendApprovalNotificationOnly($companyId, $budgetTypeId, $customerTypeId, $amount, $quoteId, $id, $commercialId, $comercial, $forApproval); |
| 3545 | } |
| 3546 | |
| 3547 | return $forApproval; |
| 3548 | } |
| 3549 | |
| 3550 | /** |
| 3551 | * FIRE-1170 bulk-sync "responsable financiero" toggle: drop the financial |
| 3552 | * requirement from a computed level when the operator unticked it. |
| 3553 | * |
| 3554 | * 3 (both) degrades to 1 (technical only) — the technical requirement stands |
| 3555 | * on its own. 2 (financial only) degrades to `null`, NOT to 1: there was no |
| 3556 | * technical requirement to fall back on, so clamping it to 1 would invent one |
| 3557 | * and send the technical approvers a request that no threshold ever asked for. |
| 3558 | * That is what the previous `min($level, 1)` did once level 2 became |
| 3559 | * reachable. |
| 3560 | */ |
| 3561 | private function suppressFinancialLevel(?int $level): ?int |
| 3562 | { |
| 3563 | if ($level === null || ! ($this->bulkSyncContext['suppressFin'] ?? false)) { |
| 3564 | return $level; |
| 3565 | } |
| 3566 | |
| 3567 | return match ((int) $level) { |
| 3568 | 3 => 1, |
| 3569 | 2 => null, |
| 3570 | default => $level, |
| 3571 | }; |
| 3572 | } |
| 3573 | |
| 3574 | /** |
| 3575 | * FIRE-1092 / FIRE-1229: pure computation of the approval level required |
| 3576 | * for a quotation given amount + project/company thresholds. |
| 3577 | * |
| 3578 | * Returns `null` (no approval needed), `1` (technical only — `tbl_approvers`), |
| 3579 | * `2` (financial only — `tbl_approvers_v2`), or `3` (both must sign off, |
| 3580 | * sequentially: Operations first, then Finance). |
| 3581 | * |
| 3582 | * Level 2 is only reachable through the matrix engine, where the technical and |
| 3583 | * financial thresholds are independent. The legacy threshold path below cannot |
| 3584 | * produce it by construction — it returns early unless `minimum_order_size` |
| 3585 | * (the technical threshold) is crossed, so financial is always additive there. |
| 3586 | * |
| 3587 | * Mirrors the canonical pattern at `Quotations::update_quotation:1530-1585` |
| 3588 | * including the FIRE-1092 `> 0` guard for the v2 threshold — |
| 3589 | * `minimum_order_size_v2` is varchar(50) and stores "" / "0" / "0.0000" |
| 3590 | * as the "no tier-2 threshold" sentinel; `!= null` would fire false |
| 3591 | * promotions and strand budgets in companies without `tbl_approvers_v2` |
| 3592 | * rows. |
| 3593 | */ |
| 3594 | private static function computeForApprovalLevel($companyId, $budgetTypeId, $customerTypeId, $amount): ?int |
| 3595 | { |
| 3596 | // AUTHORITY (SOP v2): when the flag is on and the approval matrix has a |
| 3597 | // row for this budget, the engine governs the G3W sync too. G3W has no |
| 3598 | // margin/revenue at import, so this is a threshold-only evaluation; the |
| 3599 | // customer type is still the syncById default (2 — G3W does not push a |
| 3600 | // tipo_cliente), which all matrix rows include. Companies without a |
| 3601 | // matrix row fall through to the legacy thresholds below. |
| 3602 | if (config('approvals.use_matrix_engine')) { |
| 3603 | $engine = new ApprovalThresholdEngine; |
| 3604 | $row = $engine->resolveRow( |
| 3605 | (int) $companyId, |
| 3606 | $budgetTypeId !== null ? (int) $budgetTypeId : null, |
| 3607 | $customerTypeId !== null ? (int) $customerTypeId : null, |
| 3608 | ); |
| 3609 | if ($row !== null) { |
| 3610 | $eval = $engine->evaluateForRow($row, (float) $amount, null, null); |
| 3611 | |
| 3612 | // Use the engine's own `approvalType` (1 technical / 2 financial / |
| 3613 | // 3 both / null none) instead of re-deriving it. The previous |
| 3614 | // `($reqTechnical && $reqFinancial) ? 3 : 1` collapsed a |
| 3615 | // FINANCIAL-ONLY outcome to 1, because the two flags are computed |
| 3616 | // independently: a row with `technical_threshold` disabled but |
| 3617 | // `financial_threshold` crossed yields (false, true). That wrote |
| 3618 | // approval_type = 1 and routed the request to `tbl_approvers`, so |
| 3619 | // the technical approvers were asked to sign off on something that |
| 3620 | // was never theirs and Finance was never told at all. |
| 3621 | return $eval['approvalType']; |
| 3622 | } |
| 3623 | } |
| 3624 | |
| 3625 | $company = TblCompanies::where('company_id', $companyId)->first(); |
| 3626 | $project = TblProjectTypes::where('company_id', $companyId) |
| 3627 | ->where('budget_type_id', $budgetTypeId) |
| 3628 | ->first(); |
| 3629 | |
| 3630 | $minimumOrderSize = null; |
| 3631 | $minimumOrderSizeV2 = null; |
| 3632 | $customerTypeIds = []; |
| 3633 | |
| 3634 | if ($project) { |
| 3635 | if (! empty($project->customer_type_ids)) { |
| 3636 | $customerTypeIds = array_map(intval(...), explode(',', (string) $project->customer_type_ids)); |
| 3637 | } |
| 3638 | $minimumOrderSize = $project->minimum_order_size; |
| 3639 | $minimumOrderSizeV2 = $project->minimum_order_size_v2; |
| 3640 | } elseif ($company) { |
| 3641 | if (! empty($company->customer_type_ids)) { |
| 3642 | $customerTypeIds = array_map(intval(...), explode(',', (string) $company->customer_type_ids)); |
| 3643 | } |
| 3644 | $minimumOrderSize = $company->minimum_order_size; |
| 3645 | $minimumOrderSizeV2 = $company->minimum_order_size_v2; |
| 3646 | } |
| 3647 | |
| 3648 | // A threshold of 0 / "" / "0.0000" means the dimension is DISABLED, not |
| 3649 | // "everything needs approval" — the same rule `minimum_order_size_v2` |
| 3650 | // already applies below via its `> 0` guard, and the same rule |
| 3651 | // ApprovalThresholdEngine::enabled() applies to the matrix. Guarding only |
| 3652 | // against `null` let the varchar sentinels through, and since |
| 3653 | // `amount < 0` is never true for a real budget, every quotation of such a |
| 3654 | // company would come back requiring approval. |
| 3655 | if ((float) $minimumOrderSize <= 0) { |
| 3656 | return null; |
| 3657 | } |
| 3658 | if (! in_array((int) $customerTypeId, $customerTypeIds, true)) { |
| 3659 | return null; |
| 3660 | } |
| 3661 | if ((float) $amount < (float) $minimumOrderSize) { |
| 3662 | return null; |
| 3663 | } |
| 3664 | |
| 3665 | // FIRE-1229: promote to dual-level when amount also crosses the |
| 3666 | // tier-2 threshold. `> 0` guard matches the FIRE-1092 pattern in |
| 3667 | // update_quotation — the column accepts string sentinels like |
| 3668 | // "0.0000" that should NOT trigger promotion. |
| 3669 | if ((float) $minimumOrderSizeV2 > 0 && (float) $amount >= (float) $minimumOrderSizeV2) { |
| 3670 | return 3; |
| 3671 | } |
| 3672 | |
| 3673 | return 1; |
| 3674 | } |
| 3675 | |
| 3676 | /** |
| 3677 | * FIRE-1092 / FIRE-1229: emit the approval-required email. Extracted |
| 3678 | * from the old `checkAproval` so the caller can decide whether the |
| 3679 | * recomputed level actually represents a change — `update_quotation:1422` |
| 3680 | * already does this, the G3W sync did not. |
| 3681 | * |
| 3682 | * The optional `$level` argument is the computed approval level (1 technical, |
| 3683 | * 2 financial, 3 both). The caller already knows it — pass it through; |
| 3684 | * otherwise we recompute from the same inputs so we don't silently fall back |
| 3685 | * to tier-1. |
| 3686 | * |
| 3687 | * Emits the SAME email the in-app flow sends (`sendApprovalEmailV2`). It used |
| 3688 | * to call the legacy `send_approval_notification`, which is why a budget |
| 3689 | * arriving from G3W produced the old-format notification while an identical |
| 3690 | * budget created in Titan produced the redesigned one. |
| 3691 | * |
| 3692 | * Routing follows the implemented circuit, not the level verbatim: the first |
| 3693 | * request goes to exactly ONE dimension. Technical for 1 and for 3, financial |
| 3694 | * for 2. Dual-level advances to Finance only once Operations approves (see |
| 3695 | * `Quotations::approve_quotation`). The legacy email instead unioned |
| 3696 | * `tbl_approvers` with `tbl_approvers_v2` whenever the level was 3, so a |
| 3697 | * dual-level budget from G3W asked both approvers at once and short-circuited |
| 3698 | * the sequential flow. |
| 3699 | */ |
| 3700 | private static function sendApprovalNotificationOnly($companyId, $budgetTypeId, $customerTypeId, $amount, $quoteId, $id, $commercialId, $comercial, ?int $level = null): void |
| 3701 | { |
| 3702 | $company = TblCompanies::where('company_id', $companyId)->first(); |
| 3703 | if (! $company) { |
| 3704 | return; |
| 3705 | } |
| 3706 | |
| 3707 | if (! $commercialId) { |
| 3708 | // value('id') returns null if no row matches — avoids crashing |
| 3709 | // the whole sync when G3W's commercial name doesn't map to a user. |
| 3710 | $commercialId = TblUsers::where('name', $comercial)->value('id'); |
| 3711 | } |
| 3712 | |
| 3713 | // If we still can't resolve the commercial, DO NOT skip the |
| 3714 | // notification — the approver still needs to know a quote requires |
| 3715 | // review. The email addresses approvers, not the commercial, so a |
| 3716 | // missing mapping costs nothing but the audit note. |
| 3717 | if (! $commercialId) { |
| 3718 | Log::channel('allInfoQuotationsG3w')->warning( |
| 3719 | "checkAproval: no se encontró usuario para el comercial '{$comercial}' (quote_id {$quoteId}, id {$id}). Se notifica solo al aprobador." |
| 3720 | ); |
| 3721 | } |
| 3722 | |
| 3723 | $n = $level ?? self::computeForApprovalLevel($companyId, $budgetTypeId, $customerTypeId, $amount); |
| 3724 | if ($n === null) { |
| 3725 | return; |
| 3726 | } |
| 3727 | |
| 3728 | // Sequential circuit: financial-only goes straight to Finance, everything |
| 3729 | // else starts with Operations. |
| 3730 | $dimensions = ((int) $n === 2) ? ['financial'] : ['technical']; |
| 3731 | |
| 3732 | // The "why" block. When the matrix engine governs this budget we can show |
| 3733 | // the thresholds that triggered the request; on the legacy threshold path |
| 3734 | // there is no matrix row, and null renders the generic wording. |
| 3735 | $conditions = null; |
| 3736 | if (config('approvals.use_matrix_engine')) { |
| 3737 | $row = (new ApprovalThresholdEngine)->resolveRow( |
| 3738 | (int) $companyId, |
| 3739 | $budgetTypeId !== null ? (int) $budgetTypeId : null, |
| 3740 | $customerTypeId !== null ? (int) $customerTypeId : null, |
| 3741 | ); |
| 3742 | if ($row !== null) { |
| 3743 | $conditions = [ |
| 3744 | 'technical' => $row->technical_threshold, |
| 3745 | 'financial' => $row->financial_threshold, |
| 3746 | 'minMargin' => $row->min_invoice_margin, |
| 3747 | 'minRevenue' => $row->min_revenue_per_employee_day, |
| 3748 | 'generalCosts' => $row->general_costs, |
| 3749 | ]; |
| 3750 | } |
| 3751 | } |
| 3752 | |
| 3753 | // On the syncById insert path this runs BEFORE the row's full UPDATE, so |
| 3754 | // the persisted row is still a bare placeholder from generateQuoteId(). |
| 3755 | // Hand the mailer the values we already hold rather than re-reading a row |
| 3756 | // that has no amount or budget type yet. |
| 3757 | $quotation = (object) [ |
| 3758 | 'id' => $id, |
| 3759 | 'quote_id' => $quoteId, |
| 3760 | 'company_id' => $company->company_id, |
| 3761 | 'budget_type_id' => $budgetTypeId, |
| 3762 | 'customer_type_id' => $customerTypeId, |
| 3763 | 'amount' => $amount, |
| 3764 | 'created_by' => 'G3W', |
| 3765 | 'updated_by' => null, |
| 3766 | ]; |
| 3767 | |
| 3768 | (new Quotations)->sendApprovalEmailV2($quotation, $company, $dimensions, false, null, $conditions); |
| 3769 | } |
| 3770 | |
| 3771 | /** |
| 3772 | * FIRE-1170: list regions that bulk-sync can target. Returns the regions of |
| 3773 | * every company with G3W sync turned on. The "Todas las regiones" sticky |
| 3774 | * option in the UI is purely client-side — we never return that here. |
| 3775 | * |
| 3776 | * @return array<int,string> |
| 3777 | */ |
| 3778 | public function listBulkSyncRegions(): array |
| 3779 | { |
| 3780 | return TblCompanies::query() |
| 3781 | ->where('g3W_active', 1) |
| 3782 | ->whereNotNull('region') |
| 3783 | ->where('region', '!=', '') |
| 3784 | ->orderBy('region') |
| 3785 | ->pluck('region') |
| 3786 | ->unique() |
| 3787 | ->values() |
| 3788 | ->all(); |
| 3789 | } |
| 3790 | |
| 3791 | /** |
| 3792 | * FIRE-1170: per-budget bulk-sync worker. **Upsert** — updates existing |
| 3793 | * rows with the field allowlist applied; creates missing rows in full |
| 3794 | * (allowlist doesn't apply because there's nothing to preserve on insert). |
| 3795 | * |
| 3796 | * Must run inside a DB transaction so the write-then-revert window never |
| 3797 | * exposes wrong values to other readers. |
| 3798 | * |
| 3799 | * @param array<int,string> $fields UI checkbox names — see BULK_SYNC_FIELD_MAP. |
| 3800 | * @return array{status: string, columns?: array<int,string>, reason?: string} |
| 3801 | */ |
| 3802 | public function syncSpecificFieldsForId( |
| 3803 | string $g3wId, |
| 3804 | string $region, |
| 3805 | array $fields, |
| 3806 | bool $suppressOps, |
| 3807 | bool $suppressFin, |
| 3808 | int $bulkSyncRunId, |
| 3809 | ?string $dateFrom = null, |
| 3810 | ?string $dateTo = null |
| 3811 | ): array { |
| 3812 | // 1. Fetch the G3W payload upfront so we can do an accurate existence |
| 3813 | // check via `internal_quote_id == cod_presupuesto` (matching |
| 3814 | // syncById's own lookup). The pre-fetch costs one extra G3W call |
| 3815 | // per budget; that's acceptable for an admin-triggered bulk run |
| 3816 | // and the `g3w` rate-limiter caps the total load anyway. |
| 3817 | $budget = $this->request('get', 'presupuesto/'.$g3wId, $region, []); |
| 3818 | $codPresupuesto = $budget['presupuesto']['cod_presupuesto'] ?? null; |
| 3819 | if ($codPresupuesto === null) { |
| 3820 | return ['status' => 'skipped', 'reason' => 'bad_g3w_payload']; |
| 3821 | } |
| 3822 | |
| 3823 | // FIRE-1170: G3W's `presupuesto?fecha=X` returns every budget that |
| 3824 | // touched that date (created, modified, accepted, etc.) — not just |
| 3825 | // ones created on it. So a single-day fetch typically returns 10-100x |
| 3826 | // more rows than the user expects. Match the sync-budget-monitor |
| 3827 | // semantics by skipping budgets whose actual `fecha_creacion` falls |
| 3828 | // outside the requested range. Without this filter, syncing "30/05" |
| 3829 | // would also create 47 budgets that were really created on other |
| 3830 | // days (the user's report: expected 4, got 50). |
| 3831 | if ($dateFrom !== null && $dateTo !== null) { |
| 3832 | $rawCreated = $budget['presupuesto']['fecha_creacion'] ?? null; |
| 3833 | $creationDate = $rawCreated ? substr((string) $rawCreated, 0, 10) : null; |
| 3834 | if (! $creationDate || $creationDate < $dateFrom || $creationDate > $dateTo) { |
| 3835 | return ['status' => 'skipped', 'reason' => 'outside_creation_date_range']; |
| 3836 | } |
| 3837 | } |
| 3838 | |
| 3839 | // Existence check only (decides update-vs-insert), so it stays broad: |
| 3840 | // every company in the region PLUS the one the region resolves to. |
| 3841 | // The resolved company can sit outside its own region label (CLM → |
| 3842 | // Madrid, FIRE-1476); without it a Madrid bulk-sync would re-insert |
| 3843 | // budgets that already exist under the merged company. |
| 3844 | $companyIds = TblCompanies::where('region', $region)->pluck('company_id')->all(); |
| 3845 | $resolvedCompanyId = $this->resolveCompanyForRegion($region)?->company_id; |
| 3846 | if ($resolvedCompanyId !== null && ! in_array($resolvedCompanyId, $companyIds, true)) { |
| 3847 | $companyIds[] = $resolvedCompanyId; |
| 3848 | } |
| 3849 | |
| 3850 | $existing = TblQuotations::query() |
| 3851 | ->where('internal_quote_id', $codPresupuesto) |
| 3852 | ->whereIn('company_id', $companyIds) |
| 3853 | ->first(); |
| 3854 | |
| 3855 | $this->bulkSyncContext = [ |
| 3856 | 'suppressOps' => $suppressOps, |
| 3857 | 'suppressFin' => $suppressFin, |
| 3858 | 'bulkSyncRunId' => $bulkSyncRunId, |
| 3859 | ]; |
| 3860 | |
| 3861 | try { |
| 3862 | if (! $existing) { |
| 3863 | // INSERT path — delegate to syncById which hits the create |
| 3864 | // branch. The field allowlist doesn't apply on insert (nothing |
| 3865 | // to preserve) so we write all columns the same as a regular |
| 3866 | // sync would. The bulk-sync context still silences the ops |
| 3867 | // email and clamps the financial level if requested. |
| 3868 | $this->syncById($g3wId, $region); |
| 3869 | |
| 3870 | $created = TblQuotations::query() |
| 3871 | ->where('internal_quote_id', $codPresupuesto) |
| 3872 | ->whereIn('company_id', $companyIds) |
| 3873 | ->first(); |
| 3874 | |
| 3875 | if ($created) { |
| 3876 | DB::table('tbl_quotations_log') |
| 3877 | ->where('quotation_id', $created->id) |
| 3878 | ->whereNull('bulk_sync_run_id') |
| 3879 | ->update(['bulk_sync_run_id' => $bulkSyncRunId]); |
| 3880 | } |
| 3881 | |
| 3882 | return ['status' => 'created']; |
| 3883 | } |
| 3884 | |
| 3885 | // UPDATE path — snapshot, sync, then revert columns outside the |
| 3886 | // allowlist. This is what implements the "only the checked fields |
| 3887 | // are overwritten" contract from the ticket. |
| 3888 | $before = collect(self::BULK_SYNC_USER_COLUMNS) |
| 3889 | ->mapWithKeys(fn ($col) => [$col => $existing->{$col} ?? null]) |
| 3890 | ->all(); |
| 3891 | |
| 3892 | $allowedCols = []; |
| 3893 | foreach ($fields as $field) { |
| 3894 | foreach (self::BULK_SYNC_FIELD_MAP[$field] ?? [] as $col) { |
| 3895 | $allowedCols[$col] = true; |
| 3896 | } |
| 3897 | } |
| 3898 | $allowedCols = array_keys($allowedCols); |
| 3899 | |
| 3900 | $maxLogIdBefore = (int) DB::table('tbl_quotations_log') |
| 3901 | ->where('quotation_id', $existing->id) |
| 3902 | ->max('id') ?: 0; |
| 3903 | |
| 3904 | $this->syncModifiedBudgetById($g3wId, $region); |
| 3905 | |
| 3906 | $existing->refresh(); |
| 3907 | $revertPayload = []; |
| 3908 | $revertedColumns = []; |
| 3909 | foreach (self::BULK_SYNC_USER_COLUMNS as $col) { |
| 3910 | if (in_array($col, $allowedCols, true)) { |
| 3911 | continue; |
| 3912 | } |
| 3913 | $beforeVal = $before[$col] ?? null; |
| 3914 | $afterVal = $existing->{$col} ?? null; |
| 3915 | if ($beforeVal != $afterVal) { |
| 3916 | $revertPayload[$col] = $beforeVal; |
| 3917 | $revertedColumns[] = $col; |
| 3918 | } |
| 3919 | } |
| 3920 | |
| 3921 | if (! empty($revertPayload)) { |
| 3922 | TblQuotations::where('id', $existing->id)->update($revertPayload); |
| 3923 | DB::table('tbl_quotations_log') |
| 3924 | ->where('quotation_id', $existing->id) |
| 3925 | ->where('id', '>', $maxLogIdBefore) |
| 3926 | ->whereIn('field', $revertedColumns) |
| 3927 | ->delete(); |
| 3928 | } |
| 3929 | |
| 3930 | DB::table('tbl_quotations_log') |
| 3931 | ->where('quotation_id', $existing->id) |
| 3932 | ->where('id', '>', $maxLogIdBefore) |
| 3933 | ->whereNull('bulk_sync_run_id') |
| 3934 | ->update(['bulk_sync_run_id' => $bulkSyncRunId]); |
| 3935 | |
| 3936 | return [ |
| 3937 | 'status' => 'updated', |
| 3938 | 'columns' => array_values(array_diff($allowedCols, $revertedColumns)), |
| 3939 | ]; |
| 3940 | } finally { |
| 3941 | $this->bulkSyncContext = null; |
| 3942 | } |
| 3943 | } |
| 3944 | |
| 3945 | /** |
| 3946 | * FIRE-1170: orchestrate one region of a bulk-sync run. Iterates the date |
| 3947 | * range, asks G3W for the budgets per day, and runs each through the |
| 3948 | * field-allowlist worker inside its own transaction. Each row's success / |
| 3949 | * skip / error gets accounted against the `tbl_g3w_bulk_sync_runs` row. |
| 3950 | * |
| 3951 | * Designed to be called from a queued job (see BulkSyncG3WRun). The job |
| 3952 | * holds the `g3w` rate-limiter + `WithoutOverlapping("g3w:bulk:{region}:{runId}")` |
| 3953 | * lock — there is no need to add re-entrancy guards here. |
| 3954 | * |
| 3955 | * @param array<int,string> $fields |
| 3956 | */ |
| 3957 | public function bulkSyncByFilters( |
| 3958 | int $runId, |
| 3959 | string $region, |
| 3960 | string $dateFrom, |
| 3961 | string $dateTo, |
| 3962 | array $fields, |
| 3963 | bool $suppressOps, |
| 3964 | bool $suppressFin |
| 3965 | ): void { |
| 3966 | $run = TblG3WBulkSyncRuns::find($runId); |
| 3967 | if (! $run) { |
| 3968 | Log::channel('g3w')->warning("bulkSyncByFilters: run {$runId} not found"); |
| 3969 | |
| 3970 | return; |
| 3971 | } |
| 3972 | |
| 3973 | $start = microtime(true); |
| 3974 | |
| 3975 | try { |
| 3976 | $period = Carbon::parse($dateFrom)->daysUntil(Carbon::parse($dateTo)); |
| 3977 | |
| 3978 | foreach ($period as $day) { |
| 3979 | $dayStr = $day->format('Y-m-d'); |
| 3980 | $budgets = $this->request('get', 'presupuesto?fecha='.$dayStr, $region, []); |
| 3981 | if (! is_array($budgets)) { |
| 3982 | continue; |
| 3983 | } |
| 3984 | |
| 3985 | foreach ($budgets as $budget) { |
| 3986 | $g3wId = $budget['ID'] ?? $budget['id'] ?? null; |
| 3987 | if (! $g3wId) { |
| 3988 | continue; |
| 3989 | } |
| 3990 | |
| 3991 | $run->increment('total_g3w_budgets'); |
| 3992 | |
| 3993 | try { |
| 3994 | // attempts=3 retries MySQL deadlocks transparently; the |
| 3995 | // bulk-sync writes to tbl_quotations_log can collide |
| 3996 | // when two runs (or a run + the daily cron) touch the |
| 3997 | // same rows. The retry happens inside the same job |
| 3998 | // tick — no double-counting. |
| 3999 | $result = DB::transaction(fn () => $this->syncSpecificFieldsForId( |
| 4000 | (string) $g3wId, |
| 4001 | $region, |
| 4002 | $fields, |
| 4003 | $suppressOps, |
| 4004 | $suppressFin, |
| 4005 | $runId, |
| 4006 | $dateFrom, |
| 4007 | $dateTo |
| 4008 | ), 3); |
| 4009 | |
| 4010 | $status = $result['status'] ?? null; |
| 4011 | if ($status === 'updated') { |
| 4012 | $run->increment('updated_count'); |
| 4013 | } elseif ($status === 'created') { |
| 4014 | $run->increment('created_count'); |
| 4015 | } else { |
| 4016 | $run->increment('skipped_count'); |
| 4017 | } |
| 4018 | } catch (\Throwable $e) { |
| 4019 | // Atomic UPDATE so concurrent regions' error reports |
| 4020 | // don't clobber each other via stale `$run->save()`. |
| 4021 | $message = mb_substr($e->getMessage(), 0, 500); |
| 4022 | DB::statement( |
| 4023 | 'UPDATE tbl_g3w_bulk_sync_runs |
| 4024 | SET errors_json = JSON_SET(COALESCE(errors_json, JSON_OBJECT()), ?, ?), |
| 4025 | error_count = error_count + 1 |
| 4026 | WHERE id = ?', |
| 4027 | ['$."'.$g3wId.'"', $message, $runId] |
| 4028 | ); |
| 4029 | Log::channel('g3w')->error("bulkSyncByFilters error: {$g3wId} — {$e->getMessage()}"); |
| 4030 | } |
| 4031 | } |
| 4032 | } |
| 4033 | } finally { |
| 4034 | $run->refresh(); |
| 4035 | $run->ended_at = now(); |
| 4036 | $run->save(); |
| 4037 | |
| 4038 | // A bulk re-sync is a G3W → Titan import like any other; without |
| 4039 | // this the footer would still claim the region hasn't synced. |
| 4040 | if (($run->updated_count + $run->created_count) > 0) { |
| 4041 | $this->markG3wImport($region); |
| 4042 | } |
| 4043 | |
| 4044 | Log::channel('g3w')->info('bulkSyncByFilters finished', [ |
| 4045 | 'run_id' => $runId, |
| 4046 | 'region' => $region, |
| 4047 | 'date_from' => $dateFrom, |
| 4048 | 'date_to' => $dateTo, |
| 4049 | 'wall_ms' => (int) round((microtime(true) - $start) * 1000), |
| 4050 | 'updated' => $run->updated_count, |
| 4051 | 'created' => $run->created_count, |
| 4052 | 'skipped' => $run->skipped_count, |
| 4053 | 'errors' => $run->error_count, |
| 4054 | ]); |
| 4055 | } |
| 4056 | } |
| 4057 | } |