Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
51.28% covered (warning)
51.28%
100 / 195
47.37% covered (danger)
47.37%
9 / 19
CRAP
0.00% covered (danger)
0.00%
0 / 1
GestionaService
51.28% covered (warning)
51.28%
100 / 195
47.37% covered (danger)
47.37%
9 / 19
619.51
0.00% covered (danger)
0.00%
0 / 1
 __construct
100.00% covered (success)
100.00%
5 / 5
100.00% covered (success)
100.00%
1 / 1
2
 getCredentials
92.86% covered (success)
92.86%
13 / 14
0.00% covered (danger)
0.00%
0 / 1
7.02
 encrypt
100.00% covered (success)
100.00%
2 / 2
100.00% covered (success)
100.00%
1 / 1
1
 decrypt
100.00% covered (success)
100.00%
2 / 2
100.00% covered (success)
100.00%
1 / 1
1
 auth
0.00% covered (danger)
0.00%
0 / 33
0.00% covered (danger)
0.00%
0 / 1
72
 request
0.00% covered (danger)
0.00%
0 / 43
0.00% covered (danger)
0.00%
0 / 1
210
 updateApiCredentials
82.35% covered (warning)
82.35%
14 / 17
0.00% covered (danger)
0.00%
0 / 1
6.20
 getApiDetails
85.71% covered (warning)
85.71%
6 / 7
0.00% covered (danger)
0.00%
0 / 1
2.01
 getLastUpdate
100.00% covered (success)
100.00%
12 / 12
100.00% covered (success)
100.00%
1 / 1
4
 normalizeRegion
100.00% covered (success)
100.00%
5 / 5
100.00% covered (success)
100.00%
1 / 1
5
 lastUpdateRow
100.00% covered (success)
100.00%
18 / 18
100.00% covered (success)
100.00%
1 / 1
3
 markG3wImport
66.67% covered (warning)
66.67%
8 / 12
0.00% covered (danger)
0.00%
0 / 1
5.93
 releaseSyncFlag
66.67% covered (warning)
66.67%
4 / 6
0.00% covered (danger)
0.00%
0 / 1
2.15
 getSyncStatus
100.00% covered (success)
100.00%
1 / 1
100.00% covered (success)
100.00%
1 / 1
1
 setSyncStatus
85.71% covered (warning)
85.71%
6 / 7
0.00% covered (danger)
0.00%
0 / 1
2.01
 getG3wActive
100.00% covered (success)
100.00%
1 / 1
100.00% covered (success)
100.00%
1 / 1
1
 updateG3wActive
100.00% covered (success)
100.00%
3 / 3
100.00% covered (success)
100.00%
1 / 1
1
 getBudgetsByDay
0.00% covered (danger)
0.00%
0 / 1
0.00% covered (danger)
0.00%
0 / 1
2
 checkDeleted
0.00% covered (danger)
0.00%
0 / 6
0.00% covered (danger)
0.00%
0 / 1
12
1<?php
2
3namespace App\Services;
4
5use App\Models\TblCompanies;
6use App\Models\TblG3WCredentials;
7use App\Models\TblG3wLastUpdate;
8use Illuminate\Http\JsonResponse;
9use Illuminate\Support\Facades\DB;
10use Illuminate\Support\Facades\Log;
11
12class GestionaService
13{
14    /**
15     * Stand-in for "this region has never imported from G3W", used only when
16     * `tbl_g3w_last_update.updated_at` refuses NULL. Deliberately just past the
17     * MySQL TIMESTAMP floor so it survives any server timezone.
18     */
19    protected const NEVER_SYNCED_AT = '1970-01-02 00:00:00';
20
21    protected $apiUrl;
22
23    protected $user;
24
25    protected $password;
26
27    protected $accessToken;
28
29    protected $region;
30
31    protected $credentials;
32
33    protected $appUser;
34
35    protected $encryptKey;
36
37    /** @var array<string, TblG3WCredentials|null> region → credentials row, see getCredentials() */
38    private array $credentialsCache = [];
39
40    protected string $cipher;
41
42    public function __construct()
43    {
44        $this->encryptKey = config('app.encrypt_key');
45        if (! $this->encryptKey) {
46            throw new \Exception('Encryption key not set in environment variables');
47        }
48
49        $this->cipher = 'AES-256-CBC';
50
51        $this->appUser = request()->header('Name') ?? 'System';
52    }
53
54    public function getCredentials($region): void
55    {
56
57        if ($region === 'Catalunya') {
58            $region = 'Cataluña';
59        }
60
61        // Sentry perf (G3W sync N+1): request() calls this on every G3W HTTP
62        // call — 5-7 times per budget. Read the row once per region per
63        // instance (one job); auth() and updateApiCredentials() clear it.
64        if (isset($this->credentialsCache[$region])) {
65            $this->credentials = $this->credentialsCache[$region];
66        } else {
67            $this->credentials = TblG3WCredentials::where('region', $region)->first();
68
69            if (! $this->credentials) {
70                TblG3WCredentials::create([
71                    'region' => $region,
72                ]);
73                $this->credentials = TblG3WCredentials::where('region', $region)->first();
74            }
75
76            $this->credentialsCache[$region] = $this->credentials;
77        }
78
79        $this->user = $this->credentials->user ? $this->decrypt($this->credentials->user) : null;
80        $this->password = $this->credentials->password ? $this->decrypt($this->credentials->password) : null;
81        $this->apiUrl = $this->credentials->url ? $this->decrypt($this->credentials->url) : null;
82    }
83
84    private function encrypt($data): string|false
85    {
86        $iv = substr(hash('sha256', (string) $this->encryptKey), 0, 16);
87
88        return openssl_encrypt($data, $this->cipher, $this->encryptKey, 0, $iv);
89    }
90
91    private function decrypt($encryptedData): string|false
92    {
93        $iv = substr(hash('sha256', (string) $this->encryptKey), 0, 16);
94
95        return openssl_decrypt($encryptedData, $this->cipher, $this->encryptKey, 0, $iv);
96    }
97
98    /**
99     * @return void
100     *
101     * @throws \Exception
102     */
103    protected function auth($region)
104    {
105        try {
106            // A (re-)login always re-reads the row, so credentials rotated
107            // mid-job are still picked up on the 401 → auth() path.
108            $this->credentialsCache = [];
109            $this->getCredentials($region);
110            if (! $this->apiUrl || ! $this->user || ! $this->password) {
111                throw new \Exception('Incomplete credentials: URL, user, or password missing.');
112            }
113
114            $url = "{$this->apiUrl}login";
115
116            $curl = curl_init();
117            curl_setopt_array($curl, [
118                CURLOPT_URL => $url,
119                CURLOPT_RETURNTRANSFER => true,
120                CURLOPT_POST => true,
121                CURLOPT_POSTFIELDS => json_encode([
122                    'email' => $this->user,
123                    'password' => $this->password,
124                ]),
125                CURLOPT_HTTPHEADER => [
126                    'Accept: application/json',
127                    'Content-Type: application/json',
128                ],
129            ]);
130
131            $response = curl_exec($curl);
132
133            if (curl_errno($curl)) {
134                throw new \Exception('cURL error: '.curl_error($curl));
135            }
136
137            $httpCode = curl_getinfo($curl, CURLINFO_HTTP_CODE);
138            curl_close($curl);
139
140            if ($httpCode !== 200) {
141                throw new \Exception('Authentication error: '.$response);
142            }
143
144            $responseData = json_decode($response, true);
145            $this->accessToken = $responseData['accessToken'] ?? null;
146
147            if (! $this->accessToken) {
148                throw new \Exception('No access token returned.');
149            }
150        } catch (\Exception $e) {
151            Log::channel('g3w')->error('Error in auth: '.$e->getMessage());
152            throw new \Exception('Authentication failed: '.$e->getMessage());
153        }
154    }
155
156    /**
157     * @return mixed
158     *
159     * @throws \Exception
160     */
161    public function request($method, $endpoint, $region, array $data = [])
162    {
163        try {
164            $this->getCredentials($region);
165
166            if (! $this->apiUrl) {
167                throw new \Exception('API URL is not defined.');
168            }
169
170            if (! $this->accessToken) {
171                $this->auth($region);
172            }
173
174            $url = "{$this->apiUrl}{$endpoint}";
175            $curl = curl_init();
176
177            curl_setopt_array($curl, [
178                CURLOPT_URL => $url,
179                CURLOPT_RETURNTRANSFER => true,
180                CURLOPT_CUSTOMREQUEST => strtoupper((string) $method),
181                CURLOPT_HTTPHEADER => [
182                    'Authorization: Bearer '.$this->accessToken,
183                    'Accept: application/json',
184                    'Content-Type: application/json',
185                ],
186            ]);
187
188            if (in_array(strtoupper((string) $method), ['POST', 'PUT', 'PATCH']) && ! empty($data)) {
189                if (! is_array($data)) {
190                    throw new \Exception('The $data parameter must be an array.');
191                }
192                curl_setopt($curl, CURLOPT_POSTFIELDS, json_encode($data));
193            }
194
195            $response = curl_exec($curl);
196
197            if (curl_errno($curl)) {
198                throw new \Exception('cURL error: '.curl_error($curl));
199            }
200
201            $httpCode = curl_getinfo($curl, CURLINFO_HTTP_CODE);
202            curl_close($curl);
203
204            if ($httpCode === 401) {
205                $this->auth($region);
206
207                return $this->request($method, $endpoint, $region, $data);
208            }
209
210            if ($httpCode < 200 || $httpCode >= 300) {
211                Log::channel('g3w')->error('API Response Error: '.$response);
212
213                $errorResponse = json_decode($response, true);
214                $errorMessage = is_array($errorResponse) && isset($errorResponse['message'])
215                    ? $errorResponse['message']
216                    : 'Request error: '.$response;
217
218                // The HTTP status as the exception code, so callers can tell a
219                // budget that no longer exists (404) from an API failure.
220                throw new \Exception($errorMessage, $httpCode);
221            }
222
223            $responseData = json_decode($response, true);
224
225            if (json_last_error() !== JSON_ERROR_NONE) {
226                throw new \Exception('Invalid JSON response: '.$response);
227            }
228
229            return $responseData;
230        } catch (\Exception $e) {
231            Log::channel('g3w')->error('Error in request: '.$e->getMessage());
232            throw $e;
233        }
234    }
235
236    /**
237     * @return true
238     *
239     * @throws \Exception
240     */
241    public function updateApiCredentials(array $params, $region = null): bool
242    {
243        try {
244            $this->getCredentials($region);
245
246            $updateData = [];
247            if (isset($params['url'])) {
248                if (! str_ends_with($params['url'], '/')) {
249                    $params['url'] .= '/';
250                }
251                $updateData['url'] = $this->encrypt($params['url']);
252            }
253
254            if (isset($params['user'])) {
255                $updateData['user'] = $this->encrypt($params['user']);
256            }
257
258            if (isset($params['password'])) {
259                $updateData['password'] = $this->encrypt($params['password']);
260            }
261
262            $updateData['user_id'] = $params['user_id'] ?? null;
263
264            TblG3WCredentials::where('region', $region)->update($updateData);
265            $this->credentialsCache = [];
266
267            return true;
268        } catch (\Exception $e) {
269            Log::channel('g3w')->error('Error updating credentials for region '.$this->region.': '.$e->getMessage());
270            throw $e;
271        }
272    }
273
274    /**
275     * @return JsonResponse
276     */
277    public function getApiDetails($region = null)
278    {
279        $this->getCredentials($region);
280        if (! $this->credentials) {
281            return response()->json(['message' => 'No credentials found'], 404);
282        }
283
284        return response()->json([
285            'url' => $this->decrypt($this->credentials->url),
286            'user' => $this->decrypt($this->credentials->user),
287        ]);
288    }
289
290    /**
291     * @return JsonResponse
292     */
293    public function getLastUpdate($region)
294    {
295        $lastUpdate = TblG3wLastUpdate::where('region', $this->normalizeRegion($region))->first();
296
297        if (! $lastUpdate || ! $lastUpdate->updated_at || $lastUpdate->updated_at->year < 2000) {
298            // No row (region never synced / never seeded), a NULL stamp, or the
299            // epoch sentinel used when the column rejects NULL: the footer
300            // renders "sin sincronización previa" from these nulls, so report
301            // it as a 200 with empty values rather than an error the client has
302            // to guess the meaning of.
303            return response()->json([
304                'lastUpdate' => null,
305                'lastUpdateIso' => null,
306                'updatingNow' => $lastUpdate->updatingNow ?? 0,
307            ]);
308        }
309
310        return response()->json([
311            'lastUpdate' => $lastUpdate->updated_at->format('Y-m-d H:i:s'),
312            // `lastUpdate` is a bare wall-clock string with no offset, so a
313            // client parsing it has to assume the server's timezone. Ship an ISO-8601
314            // value with the offset too so the footer's "hace X" stays correct for a
315            // browser that isn't on Europe/Madrid. Additive — `lastUpdate` is unchanged.
316            'lastUpdateIso' => $lastUpdate->updated_at->toIso8601String(),
317            'updatingNow' => $lastUpdate->updatingNow,
318        ]);
319    }
320
321    /**
322     * Region as stored platform-wide (`tbl_companies.region`): "Cataluña", not
323     * "Catalunya". The value reaches us in three shapes that all have to land
324     * on the same row:
325     *
326     *  - percent-encoded, because the client sets the header with
327     *    `encodeURIComponent()` — so every accented region ("Castellón",
328     *    "Aragón", "Andalucía") arrives as `Castell%C3%B3n` and matches
329     *    nothing unless it is decoded. Cataluña only escapes this because the
330     *    client rewrites it to the ASCII "Catalunya" first;
331     *  - wrapped in single quotes, because callers that spawn an artisan
332     *    command run the same variable through `escapeshellarg()`;
333     *  - already clean, from the cron and internal callers.
334     */
335    protected function normalizeRegion($region): string
336    {
337        $region = trim((string) $region);
338
339        if (strlen($region) > 1 && $region[0] === "'" && substr($region, -1) === "'") {
340            $region = substr($region, 1, -1);
341        }
342
343        // urldecode (not rawurldecode) to also accept a form-encoded '+' for
344        // the space in "Comunidad Valenciana". No region name contains a
345        // literal '+', so nothing is lost.
346        $region = urldecode($region);
347
348        return $region === 'Catalunya' ? 'Cataluña' : $region;
349    }
350
351    /**
352     * The `tbl_g3w_last_update` row backing the footer's "Última sincronización
353     * con G3W". The table predates the migrations folder and its rows were
354     * seeded by hand, so any region added since simply had none — which both
355     * fatalled every `->first()->update(...)` call site in PresupuestosService
356     * and left the footer permanently on "sin sincronización previa". Create
357     * the row on demand instead of assuming someone inserted it.
358     */
359    public function lastUpdateRow($region): ?TblG3wLastUpdate
360    {
361        $region = $this->normalizeRegion($region);
362
363        $row = TblG3wLastUpdate::where('region', $region)->first();
364
365        if ($row) {
366            return $row;
367        }
368
369        // Query builder rather than the model, and an explicit NULL stamp: a
370        // freshly created row must read as "never synced" rather than claim a
371        // sync happened at row-creation time. If the legacy column turns out to
372        // be NOT NULL, fall back to an epoch date — still unmistakably "never",
373        // whereas letting MySQL default it to CURRENT_TIMESTAMP would put a
374        // fake fresh sync on the footer.
375        try {
376            DB::table('tbl_g3w_last_update')->insert([
377                'region' => $region,
378                'g3w_id' => 0,
379                'updatingNow' => 0,
380                'updated_at' => null,
381            ]);
382        } catch (\Throwable $e) {
383            DB::table('tbl_g3w_last_update')->insert([
384                'region' => $region,
385                'g3w_id' => 0,
386                'updatingNow' => 0,
387                'updated_at' => self::NEVER_SYNCED_AT,
388            ]);
389        }
390
391        return TblG3wLastUpdate::where('region', $region)->first();
392    }
393
394    /**
395     * Record that a G3W → Titan import actually ran for this region. This is
396     * the single write that feeds the footer timestamp, so every import path
397     * (daily cron, retry-failed, works sync, sync-by-ids, bulk sync and the
398     * manual single-budget import) must end here — otherwise an import that
399     * works looks, from the footer, like one that never happened.
400     *
401     * @param  int|null  $updatingNow  New sync-in-progress flag, or null to leave it.
402     * @param  int|null  $lastG3wId  Highest presupuesto ID seen, or null to leave it.
403     */
404    public function markG3wImport($region, ?int $updatingNow = null, ?int $lastG3wId = null): void
405    {
406        try {
407            $row = $this->lastUpdateRow($region);
408
409            if (! $row) {
410                Log::channel('g3w')->warning('markG3wImport: no last-update row for region '.$this->normalizeRegion($region));
411
412                return;
413            }
414
415            if ($updatingNow !== null) {
416                $row->updatingNow = $updatingNow;
417            }
418
419            if ($lastG3wId !== null) {
420                $row->g3w_id = $lastG3wId;
421            }
422
423            // Explicit: a save with no other dirty attribute must still move
424            // the timestamp — an import that changed nothing still ran.
425            $row->updated_at = now();
426            $row->save();
427        } catch (\Throwable $e) {
428            // Bookkeeping must never take a sync down with it.
429            Log::channel('g3w')->warning('markG3wImport failed for region '.$region.': '.$e->getMessage());
430        }
431    }
432
433    /**
434     * Clear the sync-in-progress flag *without* touching `updated_at`. Used on
435     * the failure paths: a sync that blew up has to release the lock, but it
436     * must not leave the footer claiming a successful import.
437     */
438    public function releaseSyncFlag($region): void
439    {
440        try {
441            $region = $this->normalizeRegion($region);
442
443            DB::table('tbl_g3w_last_update')
444                ->where('region', $region)
445                ->update(['updatingNow' => 0]);
446        } catch (\Throwable $e) {
447            Log::channel('g3w')->warning('releaseSyncFlag failed for region '.$region.': '.$e->getMessage());
448        }
449    }
450
451    public function getSyncStatus($region)
452    {
453        return (int) (TblG3wLastUpdate::where('region', $this->normalizeRegion($region))->first()->updatingNow ?? 0);
454    }
455
456    public function setSyncStatus($status, $region): void
457    {
458        $record = $this->lastUpdateRow($region);
459
460        if (! $record) {
461            return;
462        }
463
464        $record->updatingNow = $status;
465        // Taking/releasing the lock is not an import. Only markG3wImport()
466        // moves `updated_at`, so the footer means "last data came in", not
467        // "last time a sync was attempted".
468        $record->timestamps = false;
469        $record->save();
470        $record->timestamps = true;
471    }
472
473    public function getG3wActive($region)
474    {
475        return TblCompanies::where('region', $region)->first()->g3W_active ?? 0;
476    }
477
478    public function updateG3wActive($active, $region): void
479    {
480        $record = TblCompanies::where('region', $region)->first();
481
482        $record->g3W_active = $active;
483        $record->save();
484    }
485
486    public function getBudgetsByDay(string $date, $region)
487    {
488        return $this->request('get', 'presupuesto?fecha='.$date, $region, []);
489    }
490
491    public function checkDeleted($id, $region): bool
492    {
493        try {
494            $this->request('get', "presupuesto/{$id}", $region, []);
495
496            return false;
497        } catch (\Exception $e) {
498            if (str_contains($e->getMessage(), 'No se ha encontrado el presupuesto')) {
499                return true;
500            }
501
502            return false;
503        }
504
505    }
506}