Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
0.09%
1 / 1123
4.00%
1 / 25
 CRAP
0.00%
0 / 1
PresupuestosService
0.09%
1 / 1123
4.00%
1 / 25
 114953.27
0.00%
0 / 1
 __construct
100.00%
1 / 1
100.00%
1 / 1
 1
 syncByDate
0.00%
0 / 95
0.00%
0 / 1
 756
 fetchFromGestiona
0.00%
0 / 23
0.00%
0 / 1
 42
 syncById
0.00%
0 / 276
0.00%
0 / 1
 8372
 syncModifiedBudgetById
0.00%
0 / 183
0.00%
0 / 1
 2862
 calculateBudgetMargin
0.00%
0 / 54
0.00%
0 / 1
 380
 syncErrorBudgets
0.00%
0 / 55
0.00%
0 / 1
 182
 syncBudgetsWorks
0.00%
0 / 57
0.00%
0 / 1
 156
 notifyErrors
0.00%
0 / 3
0.00%
0 / 1
 2
 generateQuoteId
0.00%
0 / 33
0.00%
0 / 1
 90
 normalizeStatus
0.00%
0 / 10
0.00%
0 / 1
 12
 normalizeSegment
0.00%
0 / 8
0.00%
0 / 1
 6
 normalizeType
0.00%
0 / 16
0.00%
0 / 1
 20
 normalizeSource
0.00%
0 / 21
0.00%
0 / 1
 56
 saveDocument
0.00%
0 / 47
0.00%
0 / 1
 90
 formatBytes
0.00%
0 / 6
0.00%
0 / 1
 6
 updateLogs
0.00%
0 / 34
0.00%
0 / 1
 20
 checkEmailInvalid
0.00%
0 / 8
0.00%
0 / 1
 12
 checkRequiredFields
0.00%
0 / 18
0.00%
0 / 1
 420
 isBlacklistedEmail
0.00%
0 / 8
0.00%
0 / 1
 30
 syncExistingDataWithWarnings
0.00%
0 / 14
0.00%
0 / 1
 30
 syncByIds
0.00%
0 / 65
0.00%
0 / 1
 240
 syncNullBudget
0.00%
0 / 21
0.00%
0 / 1
 30
 getAlternativeClientData
0.00%
0 / 26
0.00%
0 / 1
 132
 checkAproval
0.00%
0 / 41
0.00%
0 / 1
 156
1<?php
2
3namespace App\Services;
4
5use App\Http\Controllers\Quotations;
6use App\Models\TblBudgetStatus;
7use App\Models\TblBudgetTypes;
8use App\Models\TblCompanies;
9use App\Models\TblFiles;
10use App\Models\TblG3wLastUpdate;
11use App\Models\TblG3WOrdersUpdateLogs;
12use App\Models\TblInvoicesExceptions;
13use App\Models\TblLastFollowUpDate;
14use App\Models\TblProjectTypes;
15use App\Models\TblQuotations;
16use App\Models\TblSegmentG3wMapping;
17use App\Models\TblSegments;
18use App\Models\TblSourceG3wMapping;
19use App\Models\TblSources;
20use App\Models\TblStatusG3wMapping;
21use App\Models\TblTypeG3wMapping;
22use App\Models\TblUserG3wMapping;
23use App\Models\TblUsers;
24use App\Models\TblVipClients;
25use Google\Service\AdMob\Date;
26use Illuminate\Support\Carbon;
27use Illuminate\Support\Facades\DB;
28use Illuminate\Support\Facades\Log;
29use Illuminate\Support\Facades\Storage;
30use Mockery\Exception;
31use SendGrid\Mail\Mail;
32use Illuminate\Contracts\Routing\ResponseFactory;
33use Illuminate\Http\JsonResponse;
34use Illuminate\Http\Response;
35
36class PresupuestosService extends GestionaService
37{
38    public function __construct(private readonly Quotations $quotationsController, private readonly WorkService $workSevice)
39    {
40        parent::__construct();
41    }
42
43    /**
44     * Synchronize budgets as of a specific date.
45     * It also manages the last synchronized ID.
46     *
47     * @param string $date Default today's date
48     * @param string $name Who's launch the function
49     */
50    public function syncByDate($date = null, $name = null, $region = "Cataluña"): array
51    {
52        try {
53            if ($region === 'Catalunya') {
54                $region = 'Cataluña';
55            }
56
57            $g3wActive = TblCompanies::where('region', $region)->first()->g3W_active;
58
59            if (! $g3wActive) {
60                $startCronDateTime = date('Y-m-d H:i:s');
61                $this->updateLogs(['id' => 0, 'error' => 'La sincronización esta desactivada en la region '.$region.'.'], 0, [], $startCronDateTime, 'System', $region);
62                throw new Exception("La sincronización con G3W debe estar desactivada en la region '$region'.");
63            }
64
65            $this->setSyncStatus(1, $region);
66            $this->syncExistingDataWithWarnings();
67
68            $successfulSyncs = 0;
69            $failedSyncs = [];
70            $successIdSyncs = [];
71
72            $startCronDateTime = date('Y-m-d H:i:s');
73
74            $date ??= date('Y-m-d');
75
76            $budgets = $this->request('get', 'presupuesto?fecha='.$date, $region, []);
77
78            if (! $budgets) {
79                TblG3wLastUpdate::where('region', $region)->first()?->update(['updatingNow' => 0]);
80                $startCronDateTime = date('Y-m-d H:i:s');
81                $this->updateLogs(['id' => 0, 'error' => 'No hay presupuestos que sincronizar para la region '.$region.'.'], 0, [], $startCronDateTime, 'System', $region);
82
83                return [
84                    'success' => true,
85                    'message' => 'No budgets to upload.',
86                ];
87            }
88
89            if (is_string($budgets)) {
90                $budgets = json_decode($budgets, true);
91            }
92
93            if (! is_array($budgets) || empty($budgets)) {
94                throw new \Exception('No budgets to process.');
95            }
96
97            $company = TblCompanies::where('region', $region)->first();
98
99            foreach ($budgets as $budget) {
100                if (! is_array($budget) || ! isset($budget['ID'])) {
101                    continue;
102                }
103
104                if (
105                    TblQuotations::where('internal_quote_id', $budget['ID'])
106                        ->where('company_id', $company->company_id)
107                        ->exists()
108                ) {
109                    continue;
110                }
111
112                if (
113                    in_array($company->company_id, [18, 22])
114                &&
115                    TblQuotations::where('internal_quote_id', $budget['ID'])
116                        ->whereIn('company_id', [18, 22])
117                        ->exists()
118                ) {
119                    continue;
120                }
121
122                $result = \DB::transaction(fn() => $this->syncById($budget['ID'], $region));
123
124                if ($result['success']) {
125                    $successfulSyncs++;
126                    $successIdSyncs[] = [
127                        'id' => $budget['ID'],
128                    ];
129                } else {
130                    if (!str_contains((string) $result['error'], 'No se ha encontrado el presupuesto')) {
131                        $failedSyncs[] = [
132                            'id' => $budget['ID'],
133                            'error' => $result['error'] ?? 'Unknown error',
134                        ];
135                    }
136                }
137            }
138
139            $ids = array_filter(array_column($budgets, 'ID'), is_numeric(...));
140            if (empty($ids)) {
141                throw new \Exception('No valid IDs found in budgets.');
142            }
143            $lastId = max($ids);
144
145            TblG3wLastUpdate::where('region', $region)->first()->update([
146                'g3w_id' => $lastId,
147                'updatingNow' => 0,
148            ]);
149
150            $lastUpdate = TblG3wLastUpdate::where('region', $region)->first();
151
152            $time = '';
153
154            if ($lastUpdate) {
155                $updatedAt = Carbon::parse($lastUpdate->updated_at)->subHour();
156                $time = $updatedAt->format('H:i');
157            }
158
159            $modifiedBudgets = (str_contains($time, ':')) ?
160                $this->request('get', 'presupuesto/fechamodificacion/' . $date . "/" . $time, $region, []) :
161                $this->request('get', 'presupuesto/fechamodificacion/' . $date, $region, []);
162
163            if (is_array($modifiedBudgets) && ! empty($modifiedBudgets)) {
164                foreach ($modifiedBudgets as $budget) {
165                    $result = \DB::transaction(fn() => $this->syncModifiedBudgetById($budget['ID'], $region));
166
167                    if ($result['success']) {
168                        $successfulSyncs++;
169                        $successIdSyncs[] = [
170                            'id' => $budget['ID'],
171                        ];
172                    } else {
173                        if ($result['error']) {
174                            if (!str_contains((string) $result['error'], 'No se encuentra el presupuesto con ID en G3W') &&
175                                !str_contains((string) $result['error'], 'No se ha encontrado el presupuesto')) {
176                                $failedSyncs[] = [
177                                    'id' => $budget['ID'],
178                                    'error' => $result['error'] ?? 'Unknown error',
179                                ];
180                            }
181                        }
182                    }
183                }
184            }
185
186            TblQuotations::where('acceptance_date', '0000-00-00 00:00:00')->update(["acceptance_date"=>null]);
187
188            $this->updateLogs($failedSyncs, $successfulSyncs, $successIdSyncs, $startCronDateTime, $name, $region);
189
190            return [
191                'success' => true,
192                'message' => 'Synchronization completed.',
193            ];
194
195        } catch (\Exception $e) {
196            Log::channel('g3w')->error('Error sincronizando los presupuestos: '.$e->getMessage());
197
198            if (TblG3wLastUpdate::where('region', $region)->first()->updatingNow === 1) {
199                TblG3wLastUpdate::where('region', $region)->first()->update(['updatingNow' => 0]);
200
201            }
202
203            $startCronDateTime = date('Y-m-d H:i:s');
204            $this->updateLogs(['id' => 0, 'error' => $e->getMessage()], 0, [], $startCronDateTime, 'System', $region);
205
206            return ['success' => false, 'error' => $e->getMessage()];
207        }
208    }
209
210    /**
211     * FIRE-976: Fetch a budget from Gestiona and return normalized status + amount
212     * without creating/updating anything in Titan.
213     *
214     * @return array{success: bool, data?: array, error?: string}
215     */
216    public function fetchFromGestiona($id, string $region): array
217    {
218        try {
219            $budget = $this->request('get', "presupuesto/{$id}", $region, []);
220
221            if (!isset($budget['presupuesto']) || !is_array($budget['presupuesto'])) {
222                return ['success' => false, 'error' => 'No se ha encontrado el presupuesto en Gestiona'];
223            }
224
225            $presupuesto = $budget['presupuesto'];
226
227            // Resolve status name from Gestiona status list
228            $statusList = $this->request('get', 'presupuesto/tiposestado', $region, []);
229            $nameStatus = collect($statusList)->firstWhere('ID', $presupuesto['estado'])['nombre'] ?? null;
230
231            // Normalize to FST status
232            if (!$nameStatus) {
233                $statusId = TblBudgetStatus::where('name', 'Sin estado en G3W')->first()->budget_status_id;
234            } else {
235                $statusId = $this->normalizeStatus($nameStatus);
236                if ($statusId === TblBudgetStatus::where('name', 'Sin estado en G3W')->first()->budget_status_id) {
237                    $statusId = TblBudgetStatus::where('name', 'Estado no reconocido en FST')->first()->budget_status_id;
238                }
239            }
240
241            $statusName = TblBudgetStatus::where('budget_status_id', $statusId)->value('name');
242
243            return [
244                'success' => true,
245                'data' => [
246                    'internal_quote_id' => $presupuesto['cod_presupuesto'],
247                    'amount' => $presupuesto['importe'] ?? null,
248                    'budget_status_id' => $statusId,
249                    'budget_status' => $statusName,
250                ],
251            ];
252        } catch (\Exception $e) {
253            return ['success' => false, 'error' => $e->getMessage()];
254        }
255    }
256
257    /**
258     * Synchronizes a budget by its ID.
259     *
260     * @param $id int ID to search in G3W
261     */
262    public function syncById($id, $region): array
263    {
264        try {
265            Log::channel('allInfoQuotationsG3w')->info("Sincronizando presupuesto {$id}");
266            $g3wWarning = 0;
267            $g3wWarningFields = null;
268            $budget = $this->request('get', "presupuesto/{$id}", $region, []);
269
270            if (! isset($budget['presupuesto']) || ! is_array($budget['presupuesto'])) {
271                Log::channel('allInfoQuotationsG3w')->info('El presupuesto no contiene los datos esperados.');
272                throw new \Exception('El presupuesto no contiene los datos esperados.');
273            }
274
275            $companyId = TblCompanies::where('region', $region)->first()->company_id;
276
277            $existsQuery = TblQuotations::where('internal_quote_id', $budget['presupuesto']['cod_presupuesto']);
278            if (in_array($companyId, [18, 22])) {
279                $existsQuery->whereIn('company_id', [18, 22]);
280            } else {
281                $existsQuery->where('company_id', $companyId);
282            }
283            if ($existsQuery->exists()) {
284                Log::channel('allInfoQuotationsG3w')->info('El presupuesto ya existe, procedemos a modificarlo.');
285                $resultEdit = $this->syncModifiedBudgetById($id, $region);
286
287                return $resultEdit;
288                Log::channel('allInfoQuotationsG3w')->info("El presupuesto ya existe, procedemos a modificarlo.");
289                return $this->syncModifiedBudgetById($id, $region);
290            }
291
292            $statusList = $this->request('get', 'presupuesto/tiposestado', $region, []);
293
294            $collection = collect($statusList);
295
296            Log::channel('allInfoQuotationsG3w')->info('Estado del presupuesto: '.$budget['presupuesto']['estado']);
297
298            $nameStatus = $collection->firstWhere('ID', $budget['presupuesto']['estado'])['nombre'] ?? null;
299
300            Log::channel('allInfoQuotationsG3w')->info('Estado del presupuesto en FST: '.$nameStatus);
301
302            if (! $nameStatus) {
303                $statusID = TblBudgetStatus::where('name', 'Sin estado en G3W')->first()->budget_status_id;
304            } else {
305                $statusID = $this->normalizeStatus($nameStatus);
306
307                if ($statusID === TblBudgetStatus::where('name', 'Sin estado en G3W')->first()->budget_status_id) {
308                    $statusID = TblBudgetStatus::where('name', 'Estado no reconocido en FST')->first()->budget_status_id;
309                }
310            }
311
312            Log::channel('allInfoQuotationsG3w')->info('ID del estado del presupuesto en FST: '.$statusID);
313
314            $company = $this->request('get', "servicio/{$budget['presupuesto']['cod_servicio']}", $region, []);
315
316            if (! $company || (! isset($company['servicio']) && ! isset($company['cliente']))) {
317                $company = $this->request('get', "cliente/{$budget['presupuesto']['cod_cliente']}", $region, []);
318            }
319
320            $companyName = $company['servicio']['nombre_servicio'] ?? $company['cliente']['empresa'] ?? null;
321            $companyTelephone = $company['servicio']['telefono'] ?? $company['cliente']['telefono'] ?? null;
322            $companyEmail = $company['servicio']['email'] ?? $company['cliente']['email'] ?? null;
323
324            Log::channel('allInfoQuotationsG3w')->info('Nombre de la empresa: '.$companyName);
325
326            if($companyEmail) {
327                $companyEmail = trim((string) $companyEmail);
328
329                if (str_contains($companyEmail, ';')) {
330                    $companyEmail = str_replace(';', ',', $companyEmail);
331                }
332            }
333
334            Log::channel('allInfoQuotationsG3w')->info('Email de la empresa: '.$companyEmail);
335
336            if (! $companyName || ($budget['presupuesto']['cod_cliente'] === 9818 && in_array($companyId, [18, 22]))) {
337                $companyName = $this->getAlternativeClientData($budget['presupuesto']['datos_cliente_alternativo'])['name'];
338            }
339
340            if (! $companyTelephone || ($budget['presupuesto']['cod_cliente'] === 9818 && in_array($companyId, [18, 22]))) {
341                $companyTelephone = $this->getAlternativeClientData($budget['presupuesto']['datos_cliente_alternativo'])['number'];
342            }
343
344            if (! $companyEmail || ($budget['presupuesto']['cod_cliente'] === 9818 && in_array($companyId, [18, 22]))) {
345                $companyEmail = $this->getAlternativeClientData($budget['presupuesto']['datos_cliente_alternativo'])['email'];
346            }
347
348            Log::channel('allInfoQuotationsG3w')->info('Nombre de la empresa tras alternative client data: '.$companyName);
349
350            $segmentID = $this->normalizeSegment($company['servicio']['tipo_servicio'] ?? 'Otro');
351
352            Log::channel('allInfoQuotationsG3w')->info('Segmento de la empresa: '.$segmentID);
353
354            $companyId = TblCompanies::where('region', $region)->first()->company_id;
355
356            if (! isset($budget['presupuesto']['documento']) || empty($budget['presupuesto']['documento'])) {
357                Log::channel('allInfoQuotationsG3w')->info('El presupuesto no tiene documento asociado.');
358                throw new \Exception('El presupuesto no tiene documento asociado. Creelo y vuelva a intentarlo.');
359            }
360
361            $companyNameFormatted = '';
362
363            if (! empty($companyName)) {
364                $companyNameFormatted = str_replace(' ', '_', $companyName);
365            }
366
367            $nameDocument = $companyNameFormatted
368                ? $budget['presupuesto']['cod_presupuesto'].'_'.$companyNameFormatted
369                : $budget['presupuesto']['cod_presupuesto'];
370
371            Log::channel('allInfoQuotationsG3w')->info('Nombre del documento: '.$nameDocument);
372
373            $typeId = $this->normalizeType($budget['presupuesto']['origen_presupuesto'] ?? null, $region);
374
375            Log::channel('allInfoQuotationsG3w')->info('Tipo de presupuesto: '.$typeId);
376
377            if (empty($budget['presupuesto']['fecha_creacion'])) {
378                $budget['presupuesto']['fecha_creacion'] = Carbon::now()->format('Y-m-d H:i:s');
379            }
380
381            $work = $this->request('get', "presupuesto/trabajos/{$id}", $region, []);
382
383            $workIds = [];
384
385            if (is_array($work)) {
386                foreach ($work as $item) {
387                    if (isset($item['ID'])) {
388                        $workIds[] = $item['ID'];
389                    }
390                }
391            }
392
393            $idsConcatenados = implode('/', $workIds);
394
395            Log::channel('allInfoQuotationsG3w')->info('Trabajos: '.$idsConcatenados);
396
397            $createdByUser = TblUserG3wMapping::where('name_g3w', $budget['presupuesto']['usuario'])->first();
398            $createdBy = null;
399            $comercial = null;
400
401            if(!$createdByUser){
402                TblUserG3wMapping::create([
403                    "name_g3w" => $budget["presupuesto"]["usuario"]?? null
404                ]);
405                $comercial = $budget["presupuesto"]["usuario"];
406                $g3wWarning = 1;
407            } else {
408                $createdBy = TblUsers::where('id', $createdByUser->id_fst)->value('name')
409                    ?? null;
410
411                $comercial = $createdBy;
412            }
413
414            Log::channel('allInfoQuotationsG3w')->info('Comercial: '.$comercial);
415
416            $comercialUser = TblUserG3wMapping::where('name_g3w', $budget['presupuesto']['cod_comercial_presupuesto'])->first();
417
418            if(!$comercialUser && $budget["presupuesto"]["cod_comercial_presupuesto"]){
419                TblUserG3wMapping::create([
420                    "name_g3w" => $budget["presupuesto"]["cod_comercial_presupuesto"]?? null
421                ]);
422            } else if($comercialUser){
423                $comercial = TblUsers::where("id", $comercialUser->id_fst)->value('name')
424                    ?? null;
425            }
426
427            Log::channel('allInfoQuotationsG3w')->info('Comercial limpio: '.$comercial);
428
429            $source = $budget['presupuesto']['cod_empresa_presupuesto'] ?? null;
430            $sourceId = ($source) ? $this->normalizeSource($source, $region) : null;
431
432            Log::channel('allInfoQuotationsG3w')->info('Source: '.$source);
433
434            if ($statusID == 12) {
435                $companyLimit = TblCompanies::where('company_id', $companyId)->first();
436                $inProgressCount = TblQuotations::where('budget_status_id', 12)->where('company_id', $companyId)->where('commercial', $comercial)->count();
437                if ($companyLimit->process_limit <= $inProgressCount) {
438                    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.");
439                    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.");
440                }
441            }
442
443            // Jomar
444            if ($companyId == 18 && $source == 9) {
445                $companyId = 22;
446            }
447
448            $lastFollowUp = TblLastFollowUpDate::where('company_id', $companyId)->where('budget_type_id', $typeId)->first();
449            $workingDays = 10;
450            if ($lastFollowUp && $lastFollowUp->last_follow_up_date) {
451                $workingDays = $lastFollowUp->last_follow_up_date;
452            }
453
454            $date = Carbon::now();
455
456            $daysAdded = 0;
457            while ($daysAdded < $workingDays) {
458                $date->addDay();
459                if ($date->isWeekday()) {
460                    $daysAdded++;
461                }
462            }
463
464            $lastFollowUpDate = $date;
465
466            if (
467                in_array($statusID, [13, 14]) ||
468                !$sourceId ||
469                (!$typeId || $typeId == 0 || $typeId == 16) ||
470                empty(trim((string) $companyName)) ||
471                empty(trim((string) $companyEmail))
472                // || $this->checkEmailInvalid($companyEmail)
473            ) {
474                $g3wWarning = 1;
475            }
476
477            $row = (object) [
478                'client' => $companyName,
479                'email' => $companyEmail,
480                'budget_status_id' => $statusID,
481                'commercial' => $comercial,
482                'source_id' => $sourceId,
483                'budget_type_id' => $typeId,
484                "amount" => $budget["presupuesto"]["importe"] ?? null
485            ];
486
487            $g3wWarningFields = $this->checkRequiredFields($row);
488
489            if ($g3wWarningFields != null && $g3wWarningFields != '') {
490                $g3wWarning = 1;
491            }
492
493            if ($g3wWarningFields !== null && str_contains($g3wWarningFields, 'Email')) {
494                $statusID = 22;
495            }
496
497            Log::channel('allInfoQuotationsG3w')->info('G3W warning: '.$g3wWarning);
498
499            $reasonForNotFollowingUp = null;
500
501            // iker chacori and juan carlos
502            $excludingUsers = ['igc', '030', '031', 'fcl', '018', '021', 'jcsp', '026', '029', 'EXMOF', 'MGF'];
503
504            $facilityUser = false;
505
506            if (
507                (in_array($budget['presupuesto']['usuario'], $excludingUsers) || in_array($budget['presupuesto']['cod_comercial_presupuesto'], $excludingUsers))
508                && $region == 'Cataluña'
509            ) {
510                $reasonForNotFollowingUp = 1;
511                $facilityUser = true;
512            }
513
514            if ($segmentID == 3) {
515                $reasonForNotFollowingUp = 2;
516            } elseif ($segmentID == 2) {
517                $reasonForNotFollowingUp = 1;
518            }
519
520            $client = $this->request('get', "cliente/{$budget['presupuesto']['cod_cliente']}", $region, []);
521
522            if (
523                $client['cliente']['tipo_cliente'] === 'Grandes Cuentas' ||
524                $client['cliente']['tipo_cliente'] === 'Grandes Clientes' ||
525                TblInvoicesExceptions::where('cif', $client['cliente']['cliente_cif'])->exists()
526            ) {
527                $reasonForNotFollowingUp = 2;
528            }
529
530            $isVip = false;
531
532            $cData = $client['cliente'] ?? [];
533            $isVip = TblVipClients::whereIn('company_id', [$companyId, 0])
534                ->where(function ($query) use ($cData) {
535                    $query->whereRaw('1 = 0');
536                    if (! empty($cData['cod_cliente'])) {
537                        $query->orWhere('id_client', $cData['cod_cliente']);
538                    }
539                    if (! empty($cData['empresa'])) {
540                        $query->orWhere('name', $cData['empresa']);
541                    }
542                    if (! empty($cData['cliente_cif'])) {
543                        $query->orWhere('cif', $cData['cliente_cif']);
544                    }
545                    if (! empty($cData['email'])) {
546                        $query->orWhere('email', $cData['email']);
547                    }
548                    if (! empty($cData['telefono'])) {
549                        $query->orWhere('phone', $cData['telefono']);
550                    }
551                })
552                ->exists();
553
554            if (! $isVip) {
555                $sData = $company['servicio'] ?? [];
556                $isVip = TblVipClients::whereIn('company_id', [$companyId, 0])
557                    ->where(function ($query) use ($sData) {
558                        $query->whereRaw('1 = 0');
559                        if (! empty($sData['cod_servicio'])) {
560                            $query->orWhere('id_client', $sData['cod_servicio']);
561                        }
562                        if (! empty($sData['nombre_servicio'])) {
563                            $query->orWhere('name', $sData['nombre_servicio']);
564                        }
565                        if (! empty($sData['servicio_cif'])) {
566                            $query->orWhere('cif', $sData['servicio_cif']);
567                        }
568                        if (! empty($sData['email'])) {
569                            $query->orWhere('email', $sData['email']);
570                        }
571                        if (! empty($sData['telefono'])) {
572                            $query->orWhere('phone', $sData['telefono']);
573                        }
574                    })
575                    ->exists();
576            }
577
578            if ($isVip) {
579                $reasonForNotFollowingUp = 2;
580            }
581
582            Log::channel('allInfoQuotationsG3w')->info('Reason for not following up: '.$reasonForNotFollowingUp);
583
584            $defaultUser = 0;
585
586            if (! $comercialUser) {
587                Log::channel('allInfoQuotationsG3w')->info('Default user');
588                if ($companyId == 18) {
589                    $defaultUser = 94;
590                }
591
592                if ($companyId == 19) {
593                    $defaultUser = 68;
594                }
595
596                if ($companyId == 22) {
597                    $defaultUser = 153;
598                }
599
600                if ($companyId == 30) {
601                    $defaultUser = 124;
602                }
603                Log::channel('allInfoQuotationsG3w')->info('Default user: '.$defaultUser);
604            }
605
606            if(config("app.env") === "local"){
607                if($defaultUser == 0){
608                    $defaultUser = 92;
609                }
610            }
611
612            $generateNumber = $this->generateQuoteId($companyId);
613            Log::channel('allInfoQuotationsG3w')->info('Generate number: '.$generateNumber['id']);
614
615            $g3wNewId = $generateNumber['id'];
616            $newQuoteId = $generateNumber['number'];
617
618            $forApproval = self::checkAproval($companyId, $typeId, 2, $budget['presupuesto']['importe'], $newQuoteId, $g3wNewId, $comercialUser->id_fst ?? $defaultUser, $comercial);
619
620            Log::channel('allInfoQuotationsG3w')->info('For approval: '.$forApproval);
621
622            $g3wArray = [
623                'internal_quote_id' => $budget["presupuesto"]["cod_presupuesto"],
624                'quote_id' => $newQuoteId,
625                'company_id' => $companyId,
626                'customer_type_id' => 2,
627                'segment_id' => $segmentID,
628                'budget_type_id' => $typeId == 16 ? null : $typeId,
629                'budget_status_id' => $statusID,
630                'source_id' => $sourceId,
631                'client' => $companyName,
632                'phone_number' => $companyTelephone,
633                'email' => $companyEmail,
634                'issue_date' => ($budget['presupuesto']['fecha_creacion'] ?? null) === '0000/00/00' ? null : $budget['presupuesto']['fecha_creacion'],
635                'request_date' => ($budget['presupuesto']['fecha_creacion'] ?? null) === '0000/00/00' ? null : $budget['presupuesto']['fecha_creacion'],
636                'acceptance_date' => ($budget['presupuesto']['aceptacion'] ?? null) === '0000/00/00' ? null : $budget['presupuesto']['aceptacion'],
637                'amount' => $budget['presupuesto']['importe'] ?? null,
638                'last_follow_up_date' => $facilityUser ? null : $lastFollowUpDate,
639                'commercial' => $comercial,
640                'created_at' => $budget['presupuesto']['fecha_creacion'] ?? null,
641                'created_by' => $createdBy,
642                'has_attachment' => 1,
643                'cost_of_labor' => 0,
644                'total_cost_of_job' => 0,
645                'invoice_margin' => 0,
646                'margin_for_the_company' => 0,
647                'revenue_per_date_per_worked' => 0,
648                'gross_margin' => 100,
649                'labor_percentage' => 0,
650                'sync_import' => 1,
651                'box_work_g3w' => $idsConcatenados,
652                'segment_by_g3w' => $company['servicio']['tipo_servicio'] ?? null,
653                'source_by_g3w' => $budget['presupuesto']['cod_empresa_presupuesto'] ?? null,
654                'status_by_g3w' => $nameStatus,
655                'type_by_g3w' => $budget['presupuesto']['origen_presupuesto'] ?? null,
656                'user_create_by_g3w' => $budget['presupuesto']['usuario'],
657                'user_commercial_by_g3w' => ! empty($budget['presupuesto']['cod_comercial_presupuesto']) ? $budget['presupuesto']['cod_comercial_presupuesto'] : null,
658                'g3w_warning' => $g3wWarning,
659                'g3w_warning_fields' => $g3wWarningFields,
660                'reason_for_not_following_up_id' => $reasonForNotFollowingUp,
661                'for_add' => 0,
662                'for_approval' => $forApproval,
663            ];
664
665            TblQuotations::where('id', $g3wNewId)->update($g3wArray);
666
667            $this->quotationsController->addUpdateLog($g3wNewId, 'G3W', null, null, null, 2);
668
669            Log::channel('allInfoQuotationsG3w')->info('Pasamos el insert.');
670
671            $companyNameFormatted = '';
672
673            if (! empty($companyName)) {
674                $companyNameFormatted = str_replace(' ', '_', $companyName);
675            }
676
677            $nameDocument = $companyNameFormatted
678                ? $budget['presupuesto']['cod_presupuesto'].'_'.$companyNameFormatted
679                : $budget['presupuesto']['cod_presupuesto'];
680
681            Log::channel('allInfoQuotationsG3w')->info('Nombre del documento: '.$nameDocument);
682
683            $responseSaveDocument = $this->saveDocument($budget['presupuesto']['documento'], $nameDocument, $g3wNewId, $newQuoteId, 'G3W');
684            $responseDataSaveDocument = $responseSaveDocument->getData();
685
686            if (! isset($responseDataSaveDocument->success) || ! $responseDataSaveDocument->success) {
687                Log::channel('allInfoQuotationsG3w')->info("No se pudo guardar el documento del presupuesto {$id} al crearlo. Error: ".($responseDataSaveDocument->error ?? 'Desconocido'));
688                throw new \Exception("No se pudo guardar el documento del presupuesto {$id} al crearlo. Error: ".($responseDataSaveDocument->error ?? 'Desconocido'));
689            }
690
691            /** @phpstan-ignore-next-line */
692            $documentName = $responseDataSaveDocument->documentName;
693
694            TblQuotations::where('acceptance_date', '0000-00-00 00:00:00')->update(["acceptance_date"=>null]);
695
696            return [
697                'success' => true,
698                'id' => $g3wNewId,
699            ];
700
701        } catch (\Exception $e) {
702            $errorMessage = $e->getMessage();
703
704            return ['success' => false, 'error' => "Error sincronizando el presupuesto {$id}: ".$errorMessage];
705        }
706    }
707
708    /**
709     * @return array
710     */
711    public function syncModifiedBudgetById(string $id, $region = null): array{
712        try {
713            Log::channel('allInfoQuotationsG3w')->info('Modificando presupuesto: '.$id);
714            $statusID = null;
715            $materialId = [
716                'Cataluña' => [102561, 102562, 102568, 102569],
717                'Madrid' => [562, 100026, 100027],
718                'Comunidad Valenciana' => [562],
719            ];
720
721            $statusToChange = [
722                'Enviado',
723                'Aceptado',
724                'Rechazado',
725            ];
726
727            $g3wWarning = 0;
728            $g3wWarningFields = null;
729            $companyId = TblCompanies::where('region', $region)->first()->company_id;
730
731            $budget = $this->request('get', "presupuesto/{$id}", $region, []);
732
733            $existsQuery = TblQuotations::where('internal_quote_id', $budget['presupuesto']['cod_presupuesto']);
734            if (in_array($companyId, [18, 22])) {
735                $existsQuery->whereIn('company_id', [18, 22]);
736            } else {
737                $existsQuery->where('company_id', $companyId);
738            }
739            if (! $existsQuery->exists()) {
740                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.");
741                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.");
742            }
743
744            $company = $this->request('get', "servicio/{$budget['presupuesto']['cod_servicio']}", $region, []);
745
746            $companyName = $company['servicio']['nombre_servicio'] ?? $company['cliente']['empresa'] ?? null;
747
748            // $company = $this->request('get', "servicio/{$budget["presupuesto"]["cod_servicio"]}", $region, []);
749
750            /*if(!$company || !isset($company['servicio'])){
751                $company = $this->request('get', "cliente/{$budget["presupuesto"]["cod_cliente"]}", $region, []);
752            }*/
753
754            // $companyName = (isset($company['servicio'])) ? $company["servicio"]["nombre_servicio"] : $company["cliente"]["empresa"];
755            // $companyTelephone = (isset($company['servicio'])) ? $company["servicio"]["telefono"] : $company["cliente"]["telefono"] ;
756
757            // $segmentID = $this->normalizeSegment($company["servicio"]["tipo_servicio"]?? "Otro");
758
759            if (! isset($budget['presupuesto']['documento']) || ! $budget['presupuesto']['documento']) {
760                Log::channel('allInfoQuotationsG3w')->info('El presupuesto no tiene documento asociado. Creelo y vuelva a intentarlo.');
761                throw new \Exception('El presupuesto no tiene documento asociado. Creelo y vuelva a intentarlo.');
762            }
763
764            $typeId = $this->normalizeType($budget['presupuesto']['origen_presupuesto'], $region);
765
766            Log::channel('allInfoQuotationsG3w')->info('Tipo de presupuesto: '.$typeId);
767
768            $statusList = $this->request('get', 'presupuesto/tiposestado', $region, []);
769
770            $collection = collect($statusList);
771
772            $nameStatus = $collection->firstWhere('ID', $budget['presupuesto']['estado'])['nombre'] ?? null;
773
774            Log::channel('allInfoQuotationsG3w')->info('Estado de presupuesto: '.$nameStatus);
775
776            if (! $nameStatus) {
777                $statusID = TblBudgetStatus::where('name', 'Sin estado en G3W')->first()->budget_status_id;
778            } else {
779                $statusID = $this->normalizeStatus($nameStatus);
780
781                if ($statusID === TblBudgetStatus::where('name', 'Sin estado en G3W')->first()->budget_status_id) {
782                    $statusID = TblBudgetStatus::where('name', 'Estado no reconocido en FST')->first()->budget_status_id;
783                }
784            }
785
786            Log::channel('allInfoQuotationsG3w')->info('ID de estado: '.$statusID);
787
788            $rowQuery = TblQuotations::where('internal_quote_id', $budget['presupuesto']['cod_presupuesto']);
789            if (in_array($companyId, [18, 22])) {
790                $rowQuery->whereIn('company_id', [18, 22]);
791            } else {
792                $rowQuery->where('company_id', $companyId);
793            }
794            $row = $rowQuery->first();
795
796            if ($row) {
797                if (
798                    in_array($row->budget_status_id, [13, 14]) ||
799                    !$row->source_id ||
800                    (!$typeId || $typeId == 0 || $typeId == 16) ||
801                    empty(trim((string) $row->client)) || // change for $companyName when we implement
802                    empty(trim((string) $row->email))
803                    // || $this->checkEmailInvalid($row->email)
804                ) {
805                    $g3wWarning = 1;
806                }
807
808                $g3wWarningFields = $this->checkRequiredFields($row);
809
810                if ($g3wWarningFields != null && $g3wWarningFields != '') {
811                    $g3wWarning = 1;
812                }
813
814                Log::channel('allInfoQuotationsG3w')->info('G3W Warning: '.$g3wWarning);
815
816                if ($row->budget_type_id !== $typeId) {
817                    $this->quotationsController->addUpdateLog($row->id, 'G3W', 'budget_type_id', $row->budget_type_id, $typeId, 4);
818                }
819
820                if (in_array($nameStatus, $statusToChange) || $row->budget_status_id == 6) {
821                    $this->quotationsController->addUpdateLog($row->id, 'G3W', 'budget_status_id', $row->budget_status_id, $statusID, 4);
822                }
823
824                if ($row->amount !== $budget['presupuesto']['importe']) {
825                    $this->quotationsController->addUpdateLog($row->id, 'G3W', 'amount', $row->amount, $budget['presupuesto']['importe'], 4);
826                }
827
828                if ($row->updated_by !== 'System') {
829                    $this->quotationsController->addUpdateLog($row->id, 'G3W', 'updated_by', $row->updated_by, 'G3W', 4);
830                }
831
832                if ($row->has_attachment !== 1) {
833                    $this->quotationsController->addUpdateLog($row->id, 'G3W', 'has_attachment', $row->has_attachment, 1, 4);
834                }
835
836                if ($row->sync_import_edited !== 1) {
837                    $this->quotationsController->addUpdateLog($row->id, 'G3W', 'sync_import_edited', $row->sync_import_edited, 1, 4);
838                }
839
840                if ($nameStatus === 'Aceptado') {
841                    $this->quotationsController->addUpdateLog($row->id, 'G3W', null, $row->acceptance_date, Carbon::now()->format('Y-m-d H:i:s'), 4);
842                    $this->quotationsController->addUpdateLog($row->id, 'G3W', null, $row->accepted_at, Carbon::now()->format('Y-m-d H:i:s'), 4);
843                    $this->quotationsController->addUpdateLog($row->id, 'G3W', null, $row->accepted_by, 'G3W', 4);
844                }
845
846                Log::channel('allInfoQuotationsG3w')->info('Pasamos el add update log');
847
848                $totalCostOfMaterial = 0;
849                $totalLabor = 0;
850                foreach ($budget['presupuesto']['lineas'] as $linea) {
851                    $precioCompra = (float) ($linea['precio_compra'] ?? 0);
852                    $unidades = (float) ($linea['unidades'] ?? 0);
853
854                    if (in_array($linea['cod_material'], $materialId[$region])) {
855                        $totalLabor += $precioCompra * $unidades;
856                    } else {
857                        $totalCostOfMaterial += $precioCompra * $unidades;
858                    }
859                }
860
861                $companyInfo = TblCompanies::where('region', $region)->first();
862
863                $numberOfDays = (float) $totalLabor / ((float) $companyInfo->hours_per_worker_per_day * (float) $companyInfo->cost_of_hour);
864
865                $resultMargin = $this->calculateBudgetMargin(
866                    $budget['presupuesto']['importe'] ?? 0,
867                    $row->commission_pct ?? 0,
868                    1,
869                    $numberOfDays,
870                    $totalCostOfMaterial,
871                    $companyInfo->hours_per_worker_per_day ?? 0,
872                    $companyInfo->cost_of_hour ?? 0,
873                    $row->segment_id ?? 0,
874                    $companyInfo->general_costs ?? 0
875                );
876
877                Log::channel('allInfoQuotationsG3w')->info('Pasamos el calculo de margen.');
878
879                $forApproval = $row->for_approval;
880
881                $work = $this->request('get', "presupuesto/trabajos/{$id}", $region, []);
882
883                $isWorkAccepted = false;
884
885                if (! empty($work)) {
886                    $dataToSend = [
887                        'ids' => array_column($work, 'ID'),
888                    ];
889
890                    $worksStatus = $this->request('post', 'trabajo/estados', $region, $dataToSend);
891
892                    foreach ($worksStatus as $item) {
893                        if ($item['estado'] === 'Aceptado') {
894                            $isWorkAccepted = true;
895                            break;
896                        }
897                    }
898                }
899
900                if (
901                    ($row->budget_type_id !== $typeId ||
902                    // $row->customer_type_id !== $customerTypeId ||
903                    $row->amount !== $budget['presupuesto']['importe']) &&
904                    ! $isWorkAccepted
905                ) {
906                    $comercialUser = TblUsers::where('name', $row->commercial)->first();
907                    $forApproval = self::checkAproval($companyId, $typeId, $row->customer_type_id, $budget['presupuesto']['importe'], $row->quote_id, $row->id, $comercialUser->id, $row->commercial);
908                }
909
910                $row->update(
911                    [
912                        //'segment_id' => $segmentID,
913                        'budget_type_id' => $typeId == 16 ? null : $typeId,
914                        // 'client' => $companyName?? null,
915                        // 'phone_number' => $companyTelephone?? null,
916                        'budget_status_id' => (in_array($nameStatus, $statusToChange) || $row->budget_status_id == 6) ? $statusID : $row->budget_status_id,
917                        'acceptance_date' => ($nameStatus === 'Aceptado') ? Carbon::now()->format('Y-m-d H:i:s') : $row->acceptance_date,
918                        'accepted_at' => ($nameStatus === 'Aceptado') ? Carbon::now()->format('Y-m-d H:i:s') : $row->accepted_at,
919                        'accepted_by' => ($nameStatus === 'Aceptado') ? 'System' : $row->accepted_by,
920                        'amount' => $budget['presupuesto']['importe'] ?? null,
921                        'updated_by' => 'System',
922                        'updated_at' => Carbon::now()->format('Y-m-d H:i:s'),
923                        'has_attachment' => 1,
924                        'sync_import_edited' => 1,
925                        'segment_by_g3w' => $company['servicio']['tipo_servicio'] ?? null,
926                        'source_by_g3w' => $budget['presupuesto']['cod_empresa_presupuesto'] ?? null,
927                        'status_by_g3w' => $nameStatus,
928                        'type_by_g3w' => $budget['presupuesto']['origen_presupuesto'] ?? null,
929                        'user_create_by_g3w' => $budget['presupuesto']['usuario'] ?? null,
930                        'user_commercial_by_g3w' => ! empty($budget['presupuesto']['cod_comercial_presupuesto']) ? $budget['presupuesto']['cod_comercial_presupuesto'] : null,
931                        'g3w_warning' => $g3wWarning,
932                        'g3w_warning_fields' => $g3wWarningFields,
933                        'cost_of_labor' => $resultMargin['cost_of_labor'],
934                        'total_cost_of_job' => $resultMargin['total_cost_of_job'],
935                        'invoice_margin' => $resultMargin['invoice_margin'],
936                        'margin_for_the_company' => $resultMargin['margin_for_the_company'],
937                        'margin_on_invoice_per_day_per_worker' => $resultMargin['margin_on_invoice_per_day_per_worker'],
938                        'commission_cost' => $resultMargin['commission_cost'],
939                        'revenue_per_date_per_worked' => $resultMargin['revenue_per_date_per_worked'],
940                        'gross_margin' => $resultMargin['gross_margin'],
941                        'labor_percentage' => $resultMargin['labor_percentage'],
942                        'estimated_cost_of_materials' => $totalCostOfMaterial,
943                        'people_assigned_to_the_job' => 1,
944                        'duration_of_job_in_days' => $numberOfDays,
945                        'for_approval' => $forApproval,
946                    ]
947                );
948            }
949
950            Log::channel('allInfoQuotationsG3w')->info('Pasamos el update');
951
952            $companyNameFormatted = '';
953
954            if (! empty($companyName)) {
955                $companyNameFormatted = str_replace(' ', '_', $companyName);
956            }
957
958            $nameDocument = $companyNameFormatted
959                ? $budget['presupuesto']['cod_presupuesto'].'_'.$companyNameFormatted
960                : $budget['presupuesto']['cod_presupuesto'];
961
962            Log::channel('allInfoQuotationsG3w')->info('Nombre del documento: '.$nameDocument);
963
964            if (! $row) {
965                Log::channel('allInfoQuotationsG3w')->info('No se encontró la cotización con internal_quote_id: '.$budget['presupuesto']['cod_presupuesto']);
966                throw new \Exception("No se encontró la cotización con internal_quote_id: {$budget['presupuesto']['cod_presupuesto']}");
967            }
968
969            $response = $this->saveDocument($budget['presupuesto']['documento'], $nameDocument, $row->id, $row->quote_id, 'G3W');
970            $responseData = $response->getData();
971
972            Log::info('Response data: '.json_encode($responseData));
973
974            if (! isset($responseData->success) || ! $responseData->success) {
975                Log::channel('allInfoQuotationsG3w')->info("No se pudo guardar el documento del presupuesto {$id} al editar. Error: ".($responseData->error ?? 'Desconocido'));
976                throw new \Exception("No se pudo guardar el documento del presupuesto {$id} al editar. Error: ".($responseData->error ?? 'Desconocido'));
977            }
978
979            $work = $this->request('get', "presupuesto/trabajos/{$id}", $region, []);
980
981            if (! empty($work) && isset($work[0]['ID'])) {
982                $workId = $work[0]['ID'];
983                $albaran = $this->request('get', "albaran/{$workId}", $region, []);
984
985                if (isset($albaran['albaran']) && isset($albaran['albaran']['certificado'])) {
986                    $certificado = $albaran['albaran']['certificado'];
987                    $nameCertificado = $id.'_certificado';
988
989                    $response = $this->saveDocument($certificado, $nameCertificado, $row->id, $row->quote_id, 'G3W');
990                    $responseData = $response->getData();
991
992                    if (! isset($responseData->success) || ! $responseData->success) {
993                        Log::channel('allInfoQuotationsG3w')->info("No se pudo guardar el certificado del presupuesto {$id} al editar. Error: ".($responseData->error ?? 'Desconocido'));
994                        throw new \Exception("No se pudo guardar el certificado del presupuesto {$id} al editar. Error: ".($responseData->error ?? 'Desconocido'));
995                    }
996
997                }
998            }
999
1000            Log::channel('allInfoQuotationsG3w')->info('Pasamos la subida de albaran.');
1001
1002            TblQuotations::where('acceptance_date', '0000-00-00 00:00:00')->update(["acceptance_date"=>null]);
1003
1004            return [
1005                'success' => true,
1006            ];
1007
1008        } catch (\Exception $e) {
1009            return ['success' => false, 'error' => "Error actualizando el presupuesto {$id}: ".$e->getMessage()];
1010        }
1011    }
1012
1013    /**
1014     * @return float[]|int[]|null[]
1015     */
1016    function calculateBudgetMargin($amount, $commission_pct, $people_assigned_to_the_job, $duration_of_job_in_days, $estimated_cost_of_materials, $hours_per_worker_per_day, $cost_of_hour, $segmentId, $generalCosts): array {
1017        $results = [
1018            'cost_of_labor' => 0,
1019            'total_cost_of_job' => 0,
1020            'invoice_margin' => null,
1021            'margin_for_the_company' => null,
1022            'margin_on_invoice_per_day_per_worker' => null,
1023            'commission_cost' => null,
1024            'revenue_per_date_per_worked' => 0,
1025            'gross_margin' => 0,
1026            'labor_percentage' => 0,
1027        ];
1028
1029        $parseFloat = function($value): int|float {
1030            if ($value === null || $value === '') return 0;
1031            $cleanValue = str_replace(',', '.', (string)$value);
1032            return is_numeric($cleanValue) ? (float)$cleanValue : 0;
1033        };
1034
1035        $amount = $parseFloat($amount ?? 0);
1036        $commissionPct = $parseFloat($commission_pct ?? 0);
1037        $peopleAssigned = $parseFloat($people_assigned_to_the_job ?? 0);
1038        $durationInDays = $parseFloat($duration_of_job_in_days ?? 0);
1039        $estimatedMaterials = $parseFloat($estimated_cost_of_materials ?? 0);
1040
1041        if ($amount >= 0 && $peopleAssigned >= 0 && $durationInDays >= 0) {
1042
1043            $costOfLabor = $durationInDays * $peopleAssigned * ($hours_per_worker_per_day ?? 0) * ($cost_of_hour ?? 0);
1044
1045            $totalCostOfJob = $costOfLabor + $estimatedMaterials;
1046
1047            if ($commissionPct > 0 && $amount > 0) {
1048                $commissionCost = ($commissionPct / 100) * $amount;
1049            } else {
1050                $commissionCost = 0;
1051            }
1052
1053            if ($totalCostOfJob > 0 && $amount > 0) {
1054                if ($segmentId == 7) {
1055                    $invoiceMargin = (($amount - $totalCostOfJob - $commissionCost) / $amount) * 100;
1056                } else {
1057                    $invoiceMargin = (($amount - $totalCostOfJob) / $amount) * 100;
1058                }
1059
1060                $marginForTheCompany = $invoiceMargin - $generalCosts;
1061
1062                $marginOnInvoicePerDayPerWorker = ($amount - $estimatedMaterials - $costOfLabor) /
1063                                                ($durationInDays ?: 1) /
1064                                                ($peopleAssigned ?: 1);
1065            } else {
1066                $invoiceMargin = 0;
1067                $marginForTheCompany = 0;
1068                $marginOnInvoicePerDayPerWorker = 0;
1069            }
1070
1071            if ($costOfLabor == 0) {
1072                $revenuePerDayWorked = 0;
1073                $laborPercentage = 0;
1074            } else {
1075                $revenuePerDayWorked = ($amount / ($peopleAssigned ?: 1) / ($durationInDays ?: 1));
1076                $laborPercentage = ($costOfLabor / $amount) * 100;
1077            }
1078
1079            $grossMargin = $amount > 0 ? (($amount - $estimatedMaterials) / $amount) * 100 : 0;
1080
1081            $results['cost_of_labor'] = $costOfLabor;
1082            $results['total_cost_of_job'] = $totalCostOfJob;
1083            $results['invoice_margin'] = $invoiceMargin;
1084            $results['margin_for_the_company'] = $marginForTheCompany;
1085            $results['margin_on_invoice_per_day_per_worker'] = $marginOnInvoicePerDayPerWorker;
1086            $results['commission_cost'] = $commissionCost;
1087            $results['revenue_per_date_per_worked'] = $revenuePerDayWorked;
1088            $results['gross_margin'] = $grossMargin;
1089            $results['labor_percentage'] = $laborPercentage;
1090
1091        } else {
1092            foreach ($results as $key => $val) {
1093                $results[$key] = null;
1094            }
1095        }
1096
1097        return $results;
1098    }
1099
1100    /**
1101     * Synchronize budgets that gave us an error.
1102     *
1103     * @param string $name Who's launch the function
1104     */
1105    public function syncErrorBudgets($name = null, $region = null): array
1106    {
1107        try {
1108            if ($region === 'Catalunya') {
1109                $region = 'Cataluña';
1110            }
1111
1112            $g3wActive = TblCompanies::where('region', $region)->first()->g3W_active;
1113
1114            if (! $g3wActive) {
1115                throw new Exception("La sincronización con G3W debe estar desactivada en la region '$region'.");
1116            }
1117
1118            $this->setSyncStatus(1, $region);
1119
1120            $successfulSyncs = 0;
1121            $failedSyncs = [];
1122            $successIdSyncs = [];
1123
1124            $startCronDateTime = date('Y-m-d H:i:s');
1125
1126            $company = TblCompanies::where('region', $region)->first();
1127
1128            if (! $company) {
1129                throw new \Exception('No company found for region: '.$region);
1130            }
1131            $company_id = $company->company_id;
1132            $logs = TblG3WOrdersUpdateLogs::whereNotNull('sync_error_ids')
1133                ->where('company_id', $company_id)
1134                ->get(['sync_error_ids']);
1135
1136            $allSyncErrorIds = [];
1137
1138            foreach ($logs as $log) {
1139                if (is_string($log->sync_error_ids)) {
1140                    $decodedIds = json_decode($log->sync_error_ids, true);
1141                    if (is_array($decodedIds)) {
1142                        $log->sync_error_ids = json_encode($decodedIds); // Fixed: Encode array back to JSON string
1143                        $allSyncErrorIds = array_merge($allSyncErrorIds, $decodedIds); // Fixed: Merge the decoded array
1144                    }
1145                }
1146            }
1147
1148            $allSyncErrorIds = array_unique($allSyncErrorIds);
1149
1150            foreach ($allSyncErrorIds as $idSyncError) {
1151                $result = \DB::transaction(fn() => $this->syncById($idSyncError, $region));
1152
1153                if ($result['success']) {
1154                    $successfulSyncs++;
1155                    $successIdSyncs[] = [
1156                        'id' => $idSyncError,
1157                    ];
1158                } else {
1159                    if (!str_contains((string) $result['error'], 'No se ha encontrado el presupuesto')) {
1160                        $failedSyncs[] = [
1161                            'id' => $idSyncError,
1162                            'error' => $result['error'] ?? 'Unknown error',
1163                        ];
1164                    }
1165                }
1166            }
1167
1168            TblG3wLastUpdate::where('region', $region)->first()->update(['updatingNow' => 0]);
1169
1170            $company = TblCompanies::where('region', $region)->first();
1171            if (! $company) {
1172                throw new \Exception('No company found for region: '.$region);
1173            }
1174            $company_id = $company->company_id;
1175
1176            $logs = TblG3WOrdersUpdateLogs::whereNotNull('sync_error_ids')
1177                ->where('company_id', $company_id)
1178                ->update(['sync_error_ids' => []]);
1179
1180            $this->updateLogs($failedSyncs, $successfulSyncs, $successIdSyncs, $startCronDateTime, $name, $region);
1181
1182            return [
1183                'success' => true,
1184                'message' => 'Synchronization of failed budgets completed.',
1185            ];
1186
1187        } catch (\Exception $e) {
1188            Log::channel('g3w')->error('Error when synchronizing error budgets: '.$e->getMessage());
1189
1190            if (TblG3wLastUpdate::where('region', $region)->first()->updatingNow === 1) {
1191                TblG3wLastUpdate::where('region', $region)->first()->update(['updatingNow' => 0]);
1192            }
1193
1194            return ['success' => false, 'error' => $e->getMessage()];
1195        }
1196    }
1197
1198    public function syncBudgetsWorks($name = null, $region = null): array{
1199        try {
1200            if ($region === 'Catalunya') {
1201                $region = 'Cataluña';
1202            }
1203
1204            $company = TblCompanies::where('region', $region)->first();
1205
1206            if (! $company) {
1207                throw new \Exception("No se encontró la compañía para la región '$region'.");
1208            }
1209            $g3wActive = $company->g3W_active;
1210
1211            if (! $g3wActive) {
1212                throw new Exception("La sincronización con G3W debe estar desactivada en la region '$region'.");
1213            }
1214
1215            $this->workSevice->getG3wTasksExecuted($region, 1);
1216
1217            $this->setSyncStatus(1, $region);
1218
1219            $successfulSyncs = 0;
1220            $failedSyncs = [];
1221            $successIdSyncs = [];
1222
1223            $startCronDateTime = date('Y-m-d H:i:s');
1224
1225            $quotesIds = TblQuotations::where(function ($query): void {
1226                $query->where('sync_import', 1)
1227                    ->orWhere('sync_import_edited', 1);
1228            })
1229                ->where('budget_type_id', 1)
1230                ->where(function ($query): void {
1231                    $query->whereNull('box_work_g3w')
1232                        ->orWhere('box_work_g3w', '0');
1233                })
1234                ->get();
1235
1236            foreach ($quotesIds as $quoteId) {
1237                $work = $this->request('get', "presupuesto/trabajos/{$quoteId->internal_quote_id}", $region, []);
1238
1239                sleep(2);
1240
1241                $workIds = [];
1242
1243                if (is_array($work)) {
1244                    foreach ($work as $item) {
1245                        if (isset($item['ID'])) {
1246                            $workIds[] = $item['ID'];
1247                        }
1248                    }
1249                }
1250
1251                $idsConcatenados = implode('/', $workIds);
1252
1253                $wasUpdated = $quoteId->update(['box_work_g3w' => $idsConcatenados]);
1254
1255                if ($wasUpdated) {
1256                    $successfulSyncs++;
1257                    $successIdSyncs[] = [
1258                        'id' => $quoteId->internal_quote_id,
1259                    ];
1260                } else {
1261                    $failedSyncs[] = [
1262                        'id' => $quoteId->internal_quote_id,
1263                        'error' => "Error updating the internal quote id $quoteId, work $idsConcatenados.",
1264                    ];
1265
1266                }
1267            }
1268
1269            $g3wUpdate = TblG3wLastUpdate::where('region', $region)->first();
1270            if ($g3wUpdate) {
1271                $g3wUpdate->update(['updatingNow' => 0]);
1272            }
1273
1274            TblQuotations::where('acceptance_date', '0000-00-00 00:00:00')->update(["acceptance_date"=>null]);
1275
1276            $this->updateLogs($failedSyncs, $successfulSyncs, $successIdSyncs, $startCronDateTime, $name, $region, 'Orders Works');
1277
1278            return [
1279                'success' => true,
1280                'message' => 'Synchronization of budgets works completed.',
1281            ];
1282
1283        } catch (\Exception $e) {
1284            Log::channel('g3w')->error('Error when synchronizing budgets works: '.$e->getMessage());
1285
1286            if (TblG3wLastUpdate::where('region', $region)->first()->updatingNow === 1) {
1287                TblG3wLastUpdate::where('region', $region)->first()->update(['updatingNow' => 0]);
1288            }
1289
1290            return ['success' => false, 'error' => $e->getMessage()];
1291        }
1292    }
1293
1294    /**
1295     * @param $failedSyncs
1296     */
1297    public function notifyErrors($failedSyncs): void
1298    {
1299        $errorDetails = array_map(fn(array $failure) => "Budget ID: {$failure['id']}, Error: {$failure['error']}", $failedSyncs);
1300        $message = implode("\n", $errorDetails);
1301        /*Mail::luis, rick & chris, tech@fire.es*/
1302
1303        Log::channel('g3w')->error('Error notification sent to ricardo.alemany@fire.es');
1304    }
1305
1306    /**
1307     * Function to generate the next QuoteID
1308     *
1309     * @return array{id: int, number: string}
1310     */
1311    /*public function generateQuoteId($companyId)
1312    {
1313        if ($companyId == 0) {
1314            $latestQuoteId = TblQuotations::orderBy('id', 'DESC')->first()->quote_id ?? null;
1315        } else {
1316            $latestQuoteId = TblQuotations::where('company_id', $companyId)
1317                ->orderBy('id', 'DESC')
1318                ->first()
1319                ->quote_id ?? null;
1320        }
1321
1322        if (!$latestQuoteId) {
1323            throw new \Exception("Error generando el # en titan");
1324        }
1325
1326        if (is_numeric($latestQuoteId)) {
1327            return (string)((int)$latestQuoteId + 1);
1328        }
1329
1330        preg_match('/([A-Z]+)(\d+)/', $latestQuoteId, $matches);
1331
1332        if (count($matches) < 3) {
1333            throw new \Exception("El formato del Ãºltimo Quote ID no es válido: {$latestQuoteId}");
1334        }
1335
1336        $prefix = $matches[1];
1337        $numericPart = $matches[2];
1338
1339        $incrementedNumber = str_pad((int)$numericPart + 1, strlen($numericPart), '0', STR_PAD_LEFT);
1340
1341        $newQuoteId = $prefix . $incrementedNumber;
1342
1343        return $newQuoteId;
1344    }*/
1345    public function generateQuoteId($companyId): array|ResponseFactory|Response
1346    {
1347        try {
1348
1349            $companyId = addslashes((string) $companyId);
1350            $latestBudget = [];
1351            $number = 0;
1352            $beforeLastId = null;
1353
1354            $x = true;
1355
1356            if ($companyId == 0) {
1357                $latestBudget = TblQuotations::orderByRaw('CAST(quote_id AS DOUBLE) DESC')->value('quote_id');
1358            } else {
1359                $latestBudget = TblCompanies::where('company_id', $companyId)->value('last_id');
1360
1361                if ($latestBudget == null) {
1362                    $latestBudget = TblQuotations::where('company_id', $companyId)->orderByRaw('id DESC')->value('quote_id');
1363                    $beforeLastId = $latestBudget;
1364                }
1365            }
1366
1367            $number = $latestBudget;
1368
1369            while ($x) {
1370
1371                if(is_numeric(substr((string) $number, -1))) {
1372                    $number++;
1373                } else {
1374                    $number .= '1';
1375                }
1376
1377                $check = 0;
1378
1379                if ($companyId == 0) {
1380                    $check = TblQuotations::where('quote_id', (string) $number)->count();
1381                } else {
1382                    $check = TblQuotations::where('company_id', $companyId)->where('quote_id', (string) $number)->count();
1383                }
1384
1385                if ($check == 0) {
1386                    $x = false;
1387                }
1388            }
1389
1390            $result = TblQuotations::create(['quote_id' => $number, 'company_id' => $companyId, 'for_add' => 1]);
1391
1392            if ($beforeLastId == null) {
1393                $beforeLastId = $number;
1394            }
1395
1396            $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}";
1397            DB::select($query);
1398
1399            return [
1400                'id' => $result->id,
1401                'number' => $number
1402            ];
1403
1404        } catch (\Exception $e) {
1405            throw $e;
1406        }
1407    }
1408
1409    /**
1410     * Function to normalice the status provided by G3W.
1411     *
1412     * @param  $status  String Row status of G3W
1413     * @return int ID normalized in FST
1414     *
1415     * @throws \Exception
1416     */
1417    private function normalizeStatus($status)
1418    {
1419        if (! $status) {
1420            return TblBudgetStatus::where('name', 'Sin estado en G3W')->first()->budget_status_id;
1421        }
1422
1423        $statusMapping = TblStatusG3wMapping::where('name_g3w', $status)->first();
1424
1425        if (!$statusMapping) {
1426            TblStatusG3wMapping::create([
1427                "name_g3w" => $status,
1428                "budget_status_id" => 0
1429            ]);
1430            return TblBudgetStatus::where('name', "Estado no reconocido en FST")->first()->budget_status_id;
1431        }
1432
1433        return $statusMapping->budget_status_id;
1434
1435    }
1436
1437    /**
1438     * Function to normalice the segment provided by G3W.
1439     *
1440     * @param  $status  String Row status of G3W
1441     * @return int ID normalized in FST
1442     *
1443     * @throws \Exception
1444     */
1445    private function normalizeSegment($segment)
1446    {
1447        $segmentMapping = TblSegmentG3wMapping::where("name_g3w", $segment)->first();
1448
1449        if(!$segmentMapping){
1450            TblSegmentG3wMapping::create([
1451                "name_g3w" => $segment,
1452                "segment_id" => 0
1453            ]);
1454            return TblSegments::where('name', "Otro")->first()->segment_id?? 9;
1455        }
1456
1457        return $segmentMapping->segment_id;
1458    }
1459
1460    /**
1461     * Function to normalize the budget type provided by G3W.
1462     *
1463     * @param  int  $type  ID del tipo proporcionado por la API.
1464     * @param  string  $region  Región (Madrid o Cataluña).
1465     * @return int ID normalizado del presupuesto en el sistema.
1466     *
1467     * @throws \Exception
1468     */
1469    private function normalizeType($type, $region)
1470    {
1471        if ($region === 'Catalunya') {
1472            $region = 'Cataluña';
1473        }
1474
1475        $budgetTypeMapping = TblTypeG3wMapping::where('id_g3w', $type)
1476            ->where('region', $region)
1477            ->first();
1478
1479        if (!$budgetTypeMapping) {
1480            TblTypeG3wMapping::create([
1481                "id_g3w" => $type?? null,
1482                "budget_type_name" => null,
1483                "region" => $region
1484            ]);
1485            throw new \Exception("El estado '$type' no existe en la base de datos.");
1486        }
1487
1488        $budgetType = TblBudgetTypes::where('name', $budgetTypeMapping->budget_type_name)->first();
1489
1490        if (! $budgetType) {
1491            return 16;
1492        }
1493
1494        return $budgetType->budget_type_id;
1495    }
1496
1497    private function normalizeSource($id_call, $region)
1498    {
1499        $regionTxt = '';
1500        $region = $region == 'Catalunya' ? 'Cataluña' : $region;
1501
1502        if (! $id_call) {
1503            $sourceDefault = TblSources::where('name', 'G3W/Gestiona')->first();
1504
1505            return $sourceDefault->source_id ?? 20;
1506        }
1507
1508        $sourceMapping = TblSourceG3wMapping::where('id_g3w', $id_call)
1509            ->where('region', $region)
1510            ->first();
1511
1512        if(!$sourceMapping){
1513            TblSourceG3wMapping::create([
1514                "id_g3w" => $id_call,
1515                "source_name" => null,
1516                "region" => $region
1517            ]);
1518            return null;
1519        }
1520
1521        $sourceId = TblSources::where('name', $sourceMapping->source_name)->first();
1522
1523        if (! $sourceId && $region == 'Andalucía') {
1524            return 68;
1525        }
1526
1527        if (! $sourceId) {
1528            return null;
1529        }
1530
1531        return $sourceId->source_id;
1532
1533    }
1534
1535    /**
1536     * @param $document
1537     * @return JsonResponse
1538     */
1539    public function saveDocument($document, $nameDocument = null, $quotationId = null, $quoteId = null, $uploadedBy = null, $isInternal = null)
1540    {
1541
1542        try {
1543
1544            $binaryData = base64_decode((string) $document);
1545
1546            if (! $binaryData) {
1547                throw new \Exception('Los datos Base64 no son válidos.');
1548            }
1549
1550            $documentName = $nameDocument
1551                ? preg_replace('/[^A-Za-z0-9_\-.]/', '', (string) $nameDocument)
1552                : 'document_' . time() . '.pdf';
1553
1554            if (! preg_match('/\.pdf$/i', $documentName)) {
1555                $documentName .= '.pdf';
1556            }
1557
1558            $filename = pathinfo($documentName, PATHINFO_FILENAME).'_'.time().'.pdf';
1559
1560            $fileSize = strlen($binaryData);
1561            $mimeType = 'application/pdf';
1562
1563            $fileDataBase = TblFiles::where('quotation_id', $quotationId)->get();
1564
1565            if ($fileDataBase->isNotEmpty()) {
1566                foreach ($fileDataBase as $fileData) {
1567                    if ($fileData->original_name == $documentName) {
1568                        $s3FilePath = 'uploads/'.$fileData->filename;
1569
1570                        if (Storage::disk('s3')->exists($s3FilePath)) {
1571                            // Storage::disk('s3')->delete($s3FilePath);
1572                        }
1573
1574                        $fileData->delete();
1575
1576                    }
1577                }
1578            }
1579
1580            $s3path = Storage::disk('s3')->put(
1581                'uploads/'.$filename,
1582                $binaryData,
1583                [
1584                    'ContentType' => $mimeType,
1585                ]
1586            );
1587
1588            $file = TblFiles::create([
1589                'quotation_id' => $quotationId,
1590                'original_name' => $documentName,
1591                'filename' => $filename,
1592                'uploaded_by' => $uploadedBy,
1593                'file_size' => $fileSize,
1594                'mime_type' => $mimeType,
1595                'uploaded_at' => date('Y-m-d H:i:s'),
1596            ]);
1597
1598            $this->quotationsController->addUpdateLog($quotationId, $uploadedBy, 'upload_attachment', null, $filename, 4);
1599
1600            return response()->json([
1601                'success' => true,
1602                'message' => 'Documento guardado correctamente en la base de datos.',
1603                'filename' => $filename,
1604                'fileId' => $file->file_id,
1605                'documentName' => $documentName,
1606            ], 200);
1607
1608        } catch (\Exception $e) {
1609            return response()->json([
1610                'success' => false,
1611                'error' => $e->getMessage(),
1612            ], 500);
1613        }
1614    }
1615
1616    /**
1617     * Formatear bytes a formato legible
1618     */
1619    private function formatBytes($bytes, $precision = 2)
1620    {
1621        $units = ['B', 'KB', 'MB', 'GB', 'TB'];
1622
1623        $bytes = max($bytes, 0);
1624        $pow = floor(($bytes ? log($bytes) : 0) / log(1024));
1625        $pow = min($pow, count($units) - 1);
1626
1627        $bytes /= pow(1024, $pow);
1628
1629        return round($bytes, $precision) . ' ' . $units[$pow];
1630    }
1631
1632    /**
1633     * @param $failedSyncs
1634     * @param $successfulSyncs
1635     * @param $startCronDateTime
1636     * @param $name
1637     */
1638    public function updateLogs($failedSyncs, $successfulSyncs, $successIdSyncs, $startCronDateTime, $name, $region, $process = null): void {
1639        Log::channel('g3w')->error($failedSyncs);
1640
1641        if ($region === 'Catalunya') {
1642            $region = 'Cataluña';
1643        }
1644
1645        $companyId = TblCompanies::where('region', $region)->first()->company_id;
1646        $syncStatus = 'Success';
1647        $failedSyncsTxt = '';
1648
1649        if (! empty($failedSyncs)) {
1650            $syncStatus = ($successfulSyncs > 0) ? 'Partially failed' : 'Failed';
1651            $this->notifyErrors($failedSyncs);
1652            $errorsArray = array_column($failedSyncs, 'error');
1653            $failedSyncsTxt = implode(', ', $errorsArray);
1654        }
1655
1656        $idsError = array_map(intval(...), array_column($failedSyncs, 'id'));
1657        $idsError = array_unique($idsError);
1658        $idsErrorCount = count($idsError);
1659        $idsErrorJson = json_encode($idsError);
1660
1661        $idsSuccess = array_map(intval(...), array_column($successIdSyncs, 'id'));
1662        $idsSuccess = array_unique($idsSuccess);
1663        $idsSuccessCount = count($idsSuccess);
1664        $idsSuccessJson = json_encode($idsSuccess);
1665
1666        TblG3WOrdersUpdateLogs::create(
1667            [
1668                "company_id" => $companyId,
1669                "to_process" => $process?? "Orders",
1670                "status" => $syncStatus,
1671                "sync_succesfull" => $idsSuccessCount,
1672                "sync_error" => $idsErrorCount,
1673                "sync_error_message" => $failedSyncsTxt,
1674                "sync_error_ids" => $idsErrorJson,
1675                "sync_success_ids" => $idsSuccessJson,
1676                "processed_by" => $name,
1677                "started_at" => $startCronDateTime,
1678                "ended_at" => TblG3wLastUpdate::where("region", $region)->first()->updated_at->format('Y-m-d H:i:s'),
1679            ]
1680        );
1681    }
1682
1683    private function checkEmailInvalid($email)
1684    {
1685        $emailPattern = "/^[\w\.\-]+@([\w\-]+\.)+[a-zA-Z]{2,}$/";
1686
1687        $emails = explode(',', $email);
1688
1689        $emailInvalid = false;
1690
1691        foreach ($emails as $email) {
1692            if (! preg_match($emailPattern, $email)) {
1693                $emailInvalid = true;
1694                break;
1695            }
1696        }
1697
1698        return $emailInvalid;
1699    }
1700
1701    private function checkRequiredFields($data): ?string{
1702
1703        $g3wWarningFields = [];
1704
1705        if ($data->budget_status_id == 13 || $data->budget_status_id == 14) {
1706            array_push($g3wWarningFields, 'Estado');
1707        }
1708
1709        if ($data->budget_type_id == null || $data->budget_type_id == '' || $data->budget_type_id == 0 || $data->budget_type_id == 16) {
1710            array_push($g3wWarningFields, 'Tipo');
1711        }
1712
1713        if ($data->commercial == null || $data->commercial == '') {
1714            array_push($g3wWarningFields, 'Comercial');
1715        }
1716
1717        if ($data->source_id == null || $data->source_id == '') {
1718            array_push($g3wWarningFields, 'Fuente');
1719        }
1720
1721        if ($data->email == null || $data->email == '' || $this->isBlacklistedEmail($data->email)) {
1722            array_push($g3wWarningFields, 'Email');
1723        }
1724
1725        if ($data->client == null || $data->client == '') {
1726            array_push($g3wWarningFields, 'Datos cliente');
1727        }
1728
1729        if (($data->amount == null || $data->amount == 0) && in_array($data->budget_status_id, [1, 2, 3, 11, 17])) {
1730            array_push($g3wWarningFields, 'Importe');
1731        }
1732
1733        if (! empty($g3wWarningFields)) {
1734            return implode(', ', $g3wWarningFields);
1735        } else {
1736            return null;
1737        }
1738    }
1739
1740    private function isBlacklistedEmail(?string $email): bool
1741    {
1742        if (! $email || trim($email) === '') {
1743            return true;
1744        }
1745
1746        $pattern = '/^no@|^nomail@nomail|^notiene@notiene|tiene\.email|test\.com|prueba\.com/i';
1747
1748        $emails = explode(',', $email);
1749        foreach ($emails as $e) {
1750            if (preg_match($pattern, trim($e))) {
1751                return true;
1752            }
1753        }
1754
1755        return false;
1756    }
1757
1758    public function syncExistingDataWithWarnings(): void
1759    {
1760
1761        $budgets = TblQuotations::where('g3w_warning', 1)->get();
1762
1763        if (count($budgets) > 0) {
1764            foreach ($budgets as $item) {
1765                $g3wWarning = 0;
1766                $g3wWarningFields = $this->checkRequiredFields($item);
1767
1768                if ($g3wWarningFields != null && $g3wWarningFields != '') {
1769                    $g3wWarning = 1;
1770                }
1771
1772                TblQuotations::where('id', $item->id)->update(
1773                    [
1774                        'g3w_warning' => $g3wWarning,
1775                        'g3w_warning_fields' => $g3wWarningFields
1776                    ]
1777                );
1778
1779                $g3wWarningFields = null;
1780            }
1781        }
1782    }
1783
1784    public function syncByIds($ids, $region, $date, $user="System"): array
1785    {
1786        try {
1787            if (! $ids) {
1788                throw new \Exception('No ids provided');
1789            }
1790
1791            $arrayIds = explode(",", (string) $ids);
1792
1793            $g3wActive = TblCompanies::where('region', $region)->first()->g3W_active;
1794
1795            if (! $g3wActive) {
1796                throw new Exception("La sincronización con G3W debe estar desactivada en la region '$region'.");
1797            }
1798
1799            $this->setSyncStatus(1, $region);
1800            $this->syncExistingDataWithWarnings();
1801
1802            $successfulSyncs = 0;
1803            $failedSyncs = [];
1804            $successIdSyncs = [];
1805
1806            $startCronDateTime = date('Y-m-d H:i:s');
1807
1808            $company = TblCompanies::where('region', $region)->first();
1809
1810            $isNullInDate = TblQuotations::where("company_id", $company->company_id)
1811                ->where(function ($query): void {
1812                    $query->where("sync_import", 1)
1813                        ->orWhere("sync_import_edited", 1);
1814                })
1815                ->whereDate('created_at', $date)
1816                ->where('internal_quote_id', null)
1817                ->exists();
1818
1819            foreach ($arrayIds as $id) {
1820                $result['success'] = false;
1821                $result['error'] = "Error en sync by Ids";
1822                if($isNullInDate){
1823                    $result['success'] = \DB::transaction(fn() => $this->syncNullBudget($id, $region, $company->company_id, $date));
1824                }
1825
1826                if(!$result['success']){
1827                    $result = \DB::transaction(fn() => $this->syncById($id, $region));
1828                }
1829
1830                if ($result['success']) {
1831                    $successfulSyncs++;
1832                    $successIdSyncs[] = [
1833                        'id' => $id,
1834                    ];
1835
1836                    $quote = TblQuotations::where('company_id', $company->company_id)
1837                        ->where('internal_quote_id', $id)
1838                        ->first();
1839
1840                    if (! $quote && $company->company_id == 18) {
1841                        $quote = TblQuotations::where('company_id', 22)
1842                            ->where('internal_quote_id', $id)
1843                            ->first();
1844                    }
1845
1846                    if (! $quote && $company->company_id == 22) {
1847                        $quote = TblQuotations::where('company_id', 18)
1848                            ->where('internal_quote_id', $id)
1849                            ->first();
1850                    }
1851
1852                    if($quote){
1853                        $quote->update([
1854                            "created_at" => Carbon::parse($date)
1855                        ]);
1856                    }
1857
1858                } else {
1859                    if (!str_contains((string) $result['error'], 'No se ha encontrado el presupuesto')) {
1860                        $failedSyncs[] = [
1861                            'id' => $id,
1862                            'error' => $result['error'] ?? 'Unknown error',
1863                        ];
1864                    }
1865                }
1866            }
1867
1868            $this->setSyncStatus(0, $region);
1869
1870            TblQuotations::where('acceptance_date', '0000-00-00 00:00:00')->update(["acceptance_date"=>null]);
1871
1872            $this->updateLogs($failedSyncs, $successfulSyncs, $successIdSyncs, $startCronDateTime, $user, $region);
1873
1874            return [
1875                'success' => true,
1876                'message' => 'Synchronization completed.',
1877            ];
1878        } catch (\Exception $e) {
1879            Log::channel('g3w')->error('Error sincronizando los presupuestos: '.$e->getMessage());
1880
1881            if (TblG3wLastUpdate::where('region', $region)->first()->updatingNow === 1) {
1882                TblG3wLastUpdate::where('region', $region)->first()->update(['updatingNow' => 0]);
1883            }
1884
1885            return ['success' => false, 'error' => $e->getMessage()];
1886        }
1887
1888    }
1889
1890    private function syncNullBudget(string $id, $region, $companyId, $date): bool{
1891        $budget = $this->request('get', "presupuesto/{$id}", $region, []);
1892
1893        if (! isset($budget['presupuesto']) || ! is_array($budget['presupuesto'])) {
1894            throw new \Exception('El presupuesto no contiene los datos esperados.');
1895        }
1896
1897        $quote = TblQuotations::where("company_id", $companyId)
1898            ->where(function ($query): void {
1899                $query->where("sync_import", 1)
1900                    ->orWhere("sync_import_edited", 1);
1901            })
1902            ->whereDate('created_at', $date)
1903            ->where('internal_quote_id', null)
1904            ->where('source_by_g3w', $budget['presupuesto']['cod_empresa_presupuesto'] ?? null)
1905            ->where('type_by_g3w', $budget['presupuesto']['origen_presupuesto'] ?? null)
1906            ->where('user_create_by_g3w', $budget['presupuesto']['usuario'])
1907            ->where('user_commercial_by_g3w', ! empty($budget['presupuesto']['cod_comercial_presupuesto']) ? $budget['presupuesto']['cod_comercial_presupuesto'] : null)
1908            ->first();
1909
1910        if (! $quote) {
1911            return false;
1912        }
1913
1914        $quote->update([
1915            "internal_quote_id" => $id
1916        ]);
1917
1918        return true;
1919    }
1920
1921    function getAlternativeClientData($texto): array {
1922        preg_match('/[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}/', (string) $texto, $email);
1923        preg_match('/\b\d{9}\b/', (string) $texto, $number);
1924
1925        $nombre = null;
1926        $lineas = explode("\n", (string) $texto);
1927        foreach ($lineas as $linea) {
1928
1929            $linea = trim($linea);
1930
1931            if (empty($linea) ||
1932                preg_match('/@|\b\d{9}\b|C\/|CALLE|AVENIDA|PLAZA|CARRER|\d{5}/i', $linea)) {
1933                continue;
1934            }
1935
1936            if (preg_match('/^[A-Za-zÁÉÍÓÚáéíóúÑñ\s\.]+$/', $linea) &&
1937                ! preg_match('/\d/', $linea) &&
1938                substr_count($linea, ' ') >= 1 &&
1939                strlen($linea) > 5) {
1940
1941                $candidatos[] = $linea;
1942            }
1943        }
1944
1945        if (!empty($candidatos)) {
1946            usort($candidatos, function($a, $b): int {
1947                $scoreA = (!str_contains($a, '.') ? 2 : 0) + strlen($a);
1948                $scoreB = (!str_contains($b, '.') ? 2 : 0) + strlen($b);
1949                return $scoreB - $scoreA;
1950            });
1951            $nombre = rtrim($candidatos[0], '.');
1952        }
1953
1954        return [
1955            'email' => $email[0] ?? null,
1956            'number' => $number[0] ?? null,
1957            'name' => $nombre,
1958        ];
1959    }
1960
1961    private static function checkAproval($companyId, $budgetTypeId, $customerTypeId, $amount, $quoteId, $id, $commercialId, $comercial): ?int{
1962        $forApproval = null;
1963
1964        $company = TblCompanies::where('company_id', $companyId)->first();
1965        $project = TblProjectTypes::where('company_id', $companyId)->where('budget_type_id', $budgetTypeId)->first();
1966        $customerTypeIds = [];
1967
1968        if($project){
1969            if(!empty($project->customer_type_ids)){
1970                $customerTypeIds = array_map(intval(...), explode(',', (string) $project->customer_type_ids));
1971            }
1972            if ($project->minimum_order_size != null && in_array($customerTypeId, $customerTypeIds)) {
1973                if ($amount >= $project->minimum_order_size) {
1974                    $forApproval = 1;
1975                }
1976            }
1977            $minimumOrderSize = $project->minimum_order_size;
1978        }else{
1979            if(!empty($company->customer_type_ids)){
1980                $customerTypeIds = array_map(intval(...), explode(',', (string) $company->customer_type_ids));
1981            }
1982            if ($company->minimum_order_size != null && in_array($customerTypeId, $customerTypeIds)) {
1983                if ($amount >= $company->minimum_order_size) {
1984                    $forApproval = 1;
1985                }
1986            }
1987            $minimumOrderSize = $company->minimum_order_size;
1988        }
1989
1990        if ($forApproval === 1) {
1991            $quotations = new Quotations;
1992
1993            if (! $commercialId) {
1994                $commercialId = TblUsers::where('name', $comercial)->first()->id;
1995            }
1996
1997            $quotations->send_approval_notification(
1998                $amount,
1999                $budgetTypeId,
2000                $customerTypeId,
2001                $minimumOrderSize,
2002                $quoteId,
2003                $id,
2004                $company->name,
2005                'System',
2006                $commercialId,
2007                0,
2008                null,
2009                $company->company_id,
2010                'orders',
2011                0,
2012                0,
2013                null,
2014                'es'
2015            );
2016        }
2017
2018        return $forApproval;
2019    }
2020}