Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
0.00%
0 / 575
0.00%
0 / 10
 CRAP
0.00%
0 / 1
Leave
0.00%
0 / 575
0.00%
0 / 10
 23256
0.00%
0 / 1
 __construct
0.00%
0 / 13
0.00%
0 / 1
 20
 list_leave
0.00%
0 / 329
0.00%
0 / 1
 8190
 build_leave_table_filters
0.00%
0 / 62
0.00%
0 / 1
 420
 list_leave_table
0.00%
0 / 83
0.00%
0 / 1
 600
 create_leave
0.00%
0 / 8
0.00%
0 / 1
 6
 update_leave
0.00%
0 / 12
0.00%
0 / 1
 6
 delete_leave
0.00%
0 / 26
0.00%
0 / 1
 20
 get_leave
0.00%
0 / 9
0.00%
0 / 1
 6
 get_dates
0.00%
0 / 15
0.00%
0 / 1
 12
 download_leave
0.00%
0 / 18
0.00%
0 / 1
 2
1<?php
2
3namespace App\Http\Controllers;
4
5use App\Models\TblCompanyUsers;
6use App\Models\TblLeave;
7use App\Services\ResultCache;
8use App\Services\UserCompanies;
9use Illuminate\Http\Request;
10use Illuminate\Support\Facades\App;
11use Illuminate\Support\Facades\Cache;
12use Illuminate\Support\Facades\DB;
13
14class Leave extends Controller
15{
16    private $locale;
17
18    private $userId;
19
20    private $region;
21
22    private $companyIds;
23
24    private $companyId;
25
26    public function __construct()
27    {
28        $this->locale = request()->header('Locale-Id');
29        $this->userId = request()->header('User-Id');
30        $this->region = request()->header('Region');
31
32        App::setLocale($this->locale);
33
34        $this->companyIds = [];
35
36        if ($this->region != null && $this->region != '' && $this->region != 'All') {
37            $this->region = urldecode((string) $this->region);
38
39            $query = 'SELECT
40                        b.company_id
41                    FROM
42                        tbl_company_users a
43                        LEFT JOIN tbl_companies b ON a.company_id = b.company_id
44                    WHERE
45                        a.user_id = ?
46                        AND b.region = ?';
47
48            $this->companyIds = DB::select($query, [intval($this->userId), $this->region]);
49
50            $this->companyIds = collect($this->companyIds)->pluck('company_id')->toArray();
51        } else {
52            // FIRE-1146: prefer the middleware-resolved attribute; fall back to a fresh fetch only on unattached paths.
53            $this->companyIds = request()->attributes->get('user_company_ids', UserCompanies::forUser((int) $this->userId));
54        }
55
56        $this->companyId = implode(',', $this->companyIds);
57    }
58
59    public function list_leave(Request $request)
60    {
61
62        try {
63
64            $data = $request->all();
65            $companyId = intval($data['company_id']);
66            $userId = intval($data['user_id']);
67            $filter = $data['filterModel'];
68            $sort = $data['sortModel'];
69            $result = [];
70            $subquery = '';
71            $where = '';
72            $having = '';
73            $orderBy = '';
74            $start = intval($data['start']);
75            $end = intval($data['end']);
76            $totalRowCount = 0;
77            $withFilters = '';
78
79            $pdo = DB::connection()->getPdo();
80
81            // Allowlisted columns for sorting and filtering to prevent SQL injection
82            $allowedColumns = [
83                'a.id', 'a.date_of_receipt', 'a.branch', 'a.freshdesk_ticket_id',
84                'a.gestiona_customer_id', 'a.customer_name', 'a.customer_email',
85                'a.customer_telephone_number', 'a.existing_teams',
86                'a.reason_for_cancellation', 'a.reason_details',
87                'a.reason_details_2',
88                'a.change_of_maintenance_provider', 'a.action',
89                'a.customer_recovery', 'a.comment', 'a.private_comment',
90                'a.comment_for_administration', 'a.description',
91                'a.created_by', 'a.created_at', 'a.updated_by', 'a.updated_at',
92                'a.company_id', 'a.visit_date', 'a.freshdesk_ticket_link',
93                'a.region', 'a.cif_nif', 'a.billing_client_code',
94                'a.service_type', 'a.postal_code',
95                'b.company_id', 'b.region', 'b.name',
96                'company_name', 'id', 'date_of_receipt', 'branch',
97                'freshdesk_ticket_id', 'gestiona_customer_id', 'customer_name',
98                'customer_email', 'customer_telephone_number', 'existing_teams',
99                'reason_for_cancellation', 'reason_details', 'reason_details_2',
100                'change_of_maintenance_provider', 'action',
101                'customer_recovery', 'comment', 'private_comment',
102                'comment_for_administration', 'description',
103                'created_by', 'created_at', 'updated_by', 'updated_at',
104                'visit_date', 'region', 'cif_nif', 'billing_client_code',
105                'service_type', 'postal_code', 'semana',
106            ];
107
108            $allowedFilterTypeKeys = [
109                'contains', 'notContains', 'equals', 'notEqual',
110                'startsWith', 'endsWith', 'blank', 'notBlank',
111                'lessThan', 'lessThanOrEqual', 'greaterThan',
112                'greaterThanOrEqual', 'inRange',
113            ];
114
115            $filterType = [
116                'contains' => "LIKE '%[value]%'",
117                'notContains' => "NOT LIKE '%[value]%'",
118                'equals' => "= '[value]'",
119                'notEqual' => "<> '[value]'",
120                'startsWith' => "LIKE '[value]%'",
121                'endsWith' => "LIKE '%[value]'",
122                'blank' => 'IS NULL',
123                'notBlank' => 'IS NOT NULL',
124                'lessThan' => '< [value]',
125                'lessThanOrEqual' => '<= [value]',
126                'greaterThan' => '> [value]',
127                'greaterThanOrEqual' => '>= [value]',
128                'inRange' => 'BETWEEN [value1] AND [value2]',
129            ];
130
131            if (isset($data['ids']) && count($data['ids']) > 0) {
132                $ids = implode(',', array_map('intval', $data['ids']));
133                $where = " AND a.id IN ({$ids}) ";
134            }
135
136            if (isset($data['ids_not_in']) && count($data['ids_not_in']) > 0) {
137                $ids = implode(',', array_map('intval', $data['ids_not_in']));
138                $where = " AND a.id NOT IN ({$ids}) ";
139            }
140
141            if ($companyId != 0) {
142                $where .= " AND a.company_id = {$companyId} ";
143            } else {
144                $where .= " AND a.company_id IN ({$this->companyId}) ";
145            }
146
147            $matchScoreCol = '';
148            $matchScoreOrderBy = '';
149
150            if (isset($data['searchText']) && $data['searchText'] != null) {
151
152                $availableParameters = [
153                    'a.date_of_receipt',
154                    'a.region',
155                    'a.branch',
156                    'a.freshdesk_ticket_id',
157                    'a.cif_nif',
158                    'a.gestiona_customer_id',
159                    'a.billing_client_code',
160                    'a.customer_name',
161                    'a.customer_email',
162                    'a.customer_telephone_number',
163                    'a.existing_teams',
164                    'a.service_type',
165                    'a.postal_code',
166                    'a.reason_for_cancellation',
167                    'a.reason_details',
168                    'a.reason_details_2',
169                    'a.change_of_maintenance_provider',
170                    'a.action',
171                    'a.customer_recovery',
172                    'a.comment',
173                    'a.private_comment',
174                    'a.comment_for_administration',
175                    'a.created_by',
176                    'a.created_at',
177                    'a.updated_by',
178                    'a.updated_at',
179                ];
180
181                // Use PDO::quote() for charset-aware escaping instead of addslashes()
182                $searchText = substr($pdo->quote($data['searchText']), 1, -1);
183                $rawWords = explode(' ', $data['searchText']);
184                $searchTextArray = array_map(function ($word) use ($pdo) {
185                    return substr($pdo->quote($word), 1, -1);
186                }, $rawWords);
187
188                $searchArray = [];
189                $splitSearchArray = [];
190                $matchScoreArray = [];
191                $sc = 1;
192                foreach ($availableParameters as $field) {
193                    if ($field == 'a.customer_name' || $field == 'a.created_at') {
194                        $sc = 3;
195                    } elseif ($field == 'a.date_of_receipt') {
196                        $sc = 2;
197                    } else {
198                        $sc = 1;
199                    }
200
201                    $l = "{$field} LIKE '%{$searchText}%'";
202
203                    $d = "IFNULL((LENGTH(LOWER({$field})) - LENGTH(REPLACE(LOWER({$field}), LOWER('{$searchText}'), ''))) / LENGTH(LOWER('{$searchText}')), 0) * {$sc}";
204
205                    if (count($searchTextArray) > 1) {
206                        foreach ($searchTextArray as $word) {
207                            if (! is_numeric($word)) {
208                                $d .= " + IFNULL((LENGTH(LOWER({$field})) - LENGTH(REPLACE(LOWER({$field}), LOWER('{$word}'), ''))) / LENGTH(LOWER('{$word}')), 0) * {$sc}";
209                            }
210                        }
211                    }
212
213                    array_push($matchScoreArray, $d);
214
215                    if (is_numeric($searchText)) {
216                        array_push($searchArray, "({$l} OR {$field} = CAST('{$searchText}' AS UNSIGNED))");
217                    } else {
218                        array_push($searchArray, "({$l} OR DATE_FORMAT({$field}, '%d/%m/%Y') = DATE_FORMAT(STR_TO_DATE('{$searchText}', '%d/%m/%Y'), '%d/%m/%Y'))");
219                    }
220
221                    if (count($searchTextArray) > 1) {
222                        foreach ($searchTextArray as $word) {
223
224                            $l = "{$field} LIKE '%{$word}%'";
225
226                            if (is_numeric($word)) {
227                                array_push($splitSearchArray, "{$l} OR {$field} = CAST('{$word}' AS UNSIGNED)");
228                            } else {
229                                array_push($splitSearchArray, "{$l} OR DATE_FORMAT({$field}, '%d/%m/%Y') = DATE_FORMAT(STR_TO_DATE('{$word}', '%d/%m/%Y'), '%d/%m/%Y')");
230                            }
231                        }
232                    }
233
234                    $sc = 1;
235                }
236
237                if (count($splitSearchArray) > 0) {
238                    $splitSearchArray = implode(' OR ', $splitSearchArray);
239                    $splitSearchArray = " OR ({$splitSearchArray}) ";
240                } else {
241                    $splitSearchArray = '';
242                }
243
244                $searchArray = implode(' OR ', $searchArray);
245                $matchScoreArray = implode(',', $matchScoreArray);
246                $matchScoreCol = ", GREATEST({$matchScoreArray}) match_score";
247                $matchScoreOrderBy = 'match_score DESC,';
248                $where .= " AND ({$searchArray} {$splitSearchArray})";
249            }
250
251            if (count($sort) > 0) {
252                $field = $sort[0]['colId'];
253                $sortBy = $sort[0]['sort'];
254
255                if (strpos($field, 'translate') !== false) {
256                    $field = str_replace('_translate', '', $field);
257                }
258
259                // Validate sort column and direction against allowlists
260                if (! in_array($field, $allowedColumns)) {
261                    $field = 'a.id';
262                }
263                if (! in_array(strtoupper($sortBy), ['ASC', 'DESC'])) {
264                    $sortBy = 'DESC';
265                }
266
267                if ($matchScoreOrderBy) {
268                    $matchScoreOrderBy = ', match_score DESC';
269                }
270
271                $orderBy = " ORDER BY {$field} {$sortBy} {$matchScoreOrderBy}";
272            } else {
273                $orderBy = " ORDER BY {$matchScoreOrderBy} a.id DESC";
274            }
275
276            foreach ($filter as $key => $data) {
277                if (strpos($key, 'translate') !== false) {
278
279                    $field = str_replace('_translate', '', $key);
280
281                    if ($field == 'created_at') {
282                        $field = 'a.created_at';
283                    } elseif ($field == 'updated_at') {
284                        $field = 'a.updated_at';
285                    } elseif ($field == 'visit_date') {
286                        $field = 'a.visit_date';
287                    }
288
289                    // Validate date field against allowlist
290                    if (! in_array($field, $allowedColumns)) {
291                        continue;
292                    }
293
294                    $whereDates = '';
295                    $z = 0;
296
297                    if (isset($data['filters']) && ! empty($data['filters'])) {
298                        foreach ($data['filters'] as $yearKey => $yearData) {
299                            $yearsMonths = [];
300                            $yearsWeeks = [];
301
302                            $yearKey = intval($yearKey);
303
304                            if ($z > 0) {
305                                $whereDates .= " OR (YEAR($field) = {$yearKey} ";
306                            } else {
307                                $whereDates .= " (YEAR($field) = {$yearKey} ";
308                            }
309
310                            for ($i = 0; $i < count($yearData['months']); $i++) {
311                                if ($yearData['months'][$i]['isChecked']) {
312                                    array_push($yearsMonths, intval($yearData['months'][$i]['value']));
313                                }
314                            }
315
316                            $yearsMonths = implode("','", $yearsMonths);
317                            $whereDates .= " AND (MONTH({$field}) IN ('{$yearsMonths}')";
318
319                            for ($i = 0; $i < count($yearData['weeks']); $i++) {
320                                if ($yearData['weeks'][$i]['isChecked']) {
321                                    array_push($yearsWeeks, intval($yearData['weeks'][$i]['value']));
322                                }
323                            }
324
325                            $yearsWeeks = implode("','", $yearsWeeks);
326                            if ($yearsWeeks != '') {
327                                $whereDates .= " OR WEEK({$field}) IN ('{$yearsWeeks}') ";
328                            }
329
330                            $whereDates .= ')) ';
331                            $z++;
332                        }
333                    }
334
335                    $whereBlanks = '';
336                    if (isset($data['isBlanks'])) {
337                        if ($data['isBlanks']) {
338                            $conj = 'OR';
339                            if ($whereDates == '') {
340                                $conj = '';
341                            }
342                            $whereBlanks .= " {$conj} {$field} IS NULL ";
343                        } else {
344                            $conj = 'AND';
345                            if ($whereDates == '') {
346                                $conj = '';
347                            }
348                            $whereBlanks .= " {$conj} {$field} IS NOT NULL ";
349                        }
350                    }
351
352                    $where .= " AND ({$whereDates} {$whereBlanks}) ";
353                } else {
354                    // Validate filter column against allowlist
355                    if (! in_array($key, $allowedColumns)) {
356                        continue;
357                    }
358
359                    if ($data['filterType'] == 'number') {
360                        if (array_key_exists('operator', $data)) {
361                            // Validate operator against allowlist
362                            $operator = strtoupper($data['operator']);
363                            if (! in_array($operator, ['AND', 'OR'])) {
364                                $operator = 'AND';
365                            }
366
367                            if ($data['condition1']['type'] != 'blank' && $data['condition2']['type'] != 'notBlank') {
368                                if (! in_array($data['condition1']['type'], $allowedFilterTypeKeys) || ! in_array($data['condition2']['type'], $allowedFilterTypeKeys)) {
369                                    continue;
370                                }
371
372                                $data['condition1']['filter'] = substr($pdo->quote($data['condition1']['filter']), 1, -1);
373                                $data['condition2']['filter'] = substr($pdo->quote($data['condition2']['filter']), 1, -1);
374
375                                if ($data['condition1']['type'] == 'inRange') {
376                                    $data['condition1']['filterTo'] = substr($pdo->quote($data['condition1']['filterTo']), 1, -1);
377                                    $inRange = str_replace('[value1]', $data['condition1']['filter'], $filterType['inRange']);
378                                    $val1 = str_replace('[value2]', $data['condition1']['filterTo'], $inRange);
379                                } else {
380                                    $val1 = str_replace('[value]', $data['condition1']['filter'], $filterType[$data['condition1']['type']]);
381                                }
382
383                                if ($data['condition2']['type'] == 'inRange') {
384                                    $data['condition2']['filterTo'] = substr($pdo->quote($data['condition2']['filterTo']), 1, -1);
385                                    $inRange = str_replace('[value1]', $data['condition2']['filter'], $filterType['inRange']);
386                                    $val2 = str_replace('[value2]', $data['condition2']['filterTo'], $inRange);
387                                } else {
388                                    $val2 = str_replace('[value]', $data['condition2']['filter'], $filterType[$data['condition2']['type']]);
389                                }
390
391                            } else {
392                                if (! in_array($data['condition1']['type'], $allowedFilterTypeKeys) || ! in_array($data['condition2']['type'], $allowedFilterTypeKeys)) {
393                                    continue;
394                                }
395                                $val1 = $filterType[$data['condition1']['type']];
396                                $val2 = $filterType[$data['condition2']['type']];
397                            }
398
399                            $where .= " AND a.{$key} {$val1} {$operator} a.{$key} {$val2} ";
400                        } else {
401                            if ($data['type'] != 'blank' && $data['type'] != 'notBlank') {
402                                if (! in_array($data['type'], $allowedFilterTypeKeys)) {
403                                    continue;
404                                }
405
406                                $data['filter'] = substr($pdo->quote($data['filter']), 1, -1);
407
408                                if ($data['type'] == 'inRange') {
409                                    $data['filterTo'] = substr($pdo->quote($data['filterTo']), 1, -1);
410                                    $inRange = str_replace('[value1]', $data['filter'], $filterType['inRange']);
411                                    $val = str_replace('[value2]', $data['filterTo'], $inRange);
412                                } else {
413                                    $val = str_replace('[value]', $data['filter'], $filterType[$data['type']]);
414                                }
415                            } else {
416                                if (! in_array($data['type'], $allowedFilterTypeKeys)) {
417                                    continue;
418                                }
419                                $val = $filterType[$data['type']];
420                            }
421
422                            $where .= " AND a.{$key} {$val} ";
423                        }
424                    }
425
426                    if ($data['filterType'] == 'text') {
427                        if (array_key_exists('operator', $data)) {
428                            // Validate operator against allowlist
429                            $operator = strtoupper($data['operator']);
430                            if (! in_array($operator, ['AND', 'OR'])) {
431                                $operator = 'AND';
432                            }
433
434                            $val1 = '';
435                            $val2 = '';
436                            if ($data['condition1']['type'] != 'blank' && $data['condition2']['type'] != 'notBlank') {
437                                if (! in_array($data['condition1']['type'], $allowedFilterTypeKeys)) {
438                                    continue;
439                                }
440                                $data['condition1']['filter'] = substr($pdo->quote($data['condition1']['filter']), 1, -1);
441                                $val1 = str_replace('[value]', $data['condition1']['filter'], $filterType[$data['condition1']['type']]);
442                            }
443
444                            if ($data['condition2']['type'] != 'blank' && $data['condition2']['type'] != 'notBlank') {
445                                if (! in_array($data['condition2']['type'], $allowedFilterTypeKeys)) {
446                                    continue;
447                                }
448                                $data['condition2']['filter'] = substr($pdo->quote($data['condition2']['filter']), 1, -1);
449                                $val2 = str_replace('[value]', $data['condition2']['filter'], $filterType[$data['condition2']['type']]);
450                            }
451
452                            $where .= " AND {$key} {$val1} {$operator} {$key} {$val2} ";
453                        } else {
454                            if ($data['type'] != 'blank' && $data['type'] != 'notBlank') {
455                                if (! in_array($data['type'], $allowedFilterTypeKeys)) {
456                                    continue;
457                                }
458                                $data['filter'] = substr($pdo->quote($data['filter']), 1, -1);
459                                $val = str_replace('[value]', $data['filter'], $filterType[$data['type']]);
460                            } else {
461                                if (! in_array($data['type'], $allowedFilterTypeKeys)) {
462                                    continue;
463                                }
464                                $val = $filterType[$data['type']];
465                            }
466
467                            $where .= " AND {$key} {$val} ";
468                        }
469                    }
470
471                    if ($data['filterType'] == 'set') {
472                        $statusName = $key;
473
474                        if ($key == 'updated_by') {
475                            $statusName = 'a.updated_by';
476                        } elseif ($key == 'company_name') {
477                            $statusName = 'b.name';
478                        } elseif ($key == 'region') {
479                            $statusName = 'COALESCE(a.region, b.region)';
480                        } elseif ($key == 'reason_for_cancellation') {
481                            $statusName = 'a.reason_for_cancellation';
482                        } elseif ($key == 'created_by') {
483                            $statusName = 'a.created_by';
484                        } elseif ($key == 'reason_details') {
485                            $statusName = 'a.reason_details';
486                        } elseif ($key == 'reason_details_2') {
487                            $statusName = 'a.reason_details_2';
488                        } elseif ($key == 'change_of_maintenance_provider') {
489                            $statusName = 'a.change_of_maintenance_provider';
490                        } elseif ($key == 'action') {
491                            $statusName = 'a.action';
492                        } elseif ($key == 'customer_recovery') {
493                            $statusName = 'a.customer_recovery';
494                        }
495
496                        $escapedValues = array_map(function ($v) use ($pdo) {
497                            return $v === null ? null : substr($pdo->quote($v), 1, -1);
498                        }, $data['values']);
499                        $nonNullValues = array_filter($escapedValues, function ($v) {
500                            return $v !== null;
501                        });
502                        $val = implode("','", $nonNullValues);
503
504                        if (in_array(null, $data['values'], true)) {
505                            $where .= " AND ({$statusName} IN ('{$val}') OR {$statusName} IS NULL) ";
506                        } else {
507                            $where .= " AND {$statusName} IN ('{$val}') ";
508                        }
509                    }
510                }
511            }
512
513            $offset = $start;
514            $limit = $end - $start;
515
516            $query = "SELECT
517                        a.id,
518                        b.company_id,
519                        COALESCE(a.region, b.region) region,
520                        b.name company_name,
521                        a.date_of_receipt,
522                        a.branch,
523                        a.freshdesk_ticket_id,
524                        a.freshdesk_ticket_link,
525                        a.cif_nif,
526                        a.gestiona_customer_id,
527                        a.billing_client_code,
528                        a.customer_name,
529                        a.customer_email,
530                        a.customer_telephone_number,
531                        a.existing_teams,
532                        a.service_type,
533                        a.postal_code,
534                        DATE_FORMAT(a.date_of_receipt, '%d/%m/%Y') date_of_receipt_translate,
535                        WEEK(a.date_of_receipt, 3) semana,
536                        a.reason_for_cancellation,
537                        a.reason_details,
538                        a.reason_details_2,
539                        a.change_of_maintenance_provider,
540                        a.action,
541                        a.customer_recovery,
542                        a.comment,
543                        a.private_comment,
544                        a.comment_for_administration,
545                        a.description,
546                        a.created_by,
547                        a.created_at,
548                        a.updated_by,
549                        a.updated_at
550                        {$matchScoreCol}
551                    FROM
552                        tbl_leave a
553                        LEFT JOIN tbl_companies b ON a.company_id = b.company_id
554                    WHERE a.id > 0
555                    {$where}
556                    {$orderBy}
557                    LIMIT {$offset}, {$limit}
558                    ";
559
560            $value = Cache::get(base64_encode($query));
561
562            if (! $value) {
563                $result = DB::select($query);
564
565                Cache::put(base64_encode($query), $result, 600);
566            } else {
567                $result = $value;
568            }
569
570            $totalQuery = "SELECT 
571                            COUNT(a.id) totalRowCount
572                        FROM 
573                            tbl_leave a 
574                        WHERE a.id > 0
575                        {$where}";
576
577            $value = Cache::get(base64_encode($totalQuery));
578
579            if (! $value) {
580                $countQuery = DB::select($totalQuery);
581
582                Cache::put(base64_encode($totalQuery), $countQuery, 600);
583            } else {
584                $countQuery = $value;
585            }
586
587            return response([
588                'message' => 'OK',
589                'data' => $result,
590                'totalRowCount' => $countQuery[0]->totalRowCount,
591            ]);
592
593        } catch (\Exception $e) {
594            /** @disregard P1014 */
595            $e->exceptionCode = 'LIST_LEAVE_EXCEPTION';
596            report($e);
597
598            return response(['message' => 'KO', 'error' => $e->getMessage()]);
599        }
600
601    }
602
603    /**
604     * Advanced Search → SQL WHERE for the leave table. Mirrors
605     * Quotations::build_orders_table_filters: one AND-ed clause per field, by
606     * data type (set / text / date). Field => SQL expression matches the
607     * list_leave SELECT aliases (a = tbl_leave, b = tbl_companies).
608     */
609    private function build_leave_table_filters($filterModel): string
610    {
611        if (! is_array($filterModel) || empty($filterModel)) {
612            return '';
613        }
614
615        $colMap = [
616            'id' => 'a.id',
617            'company_name' => 'b.name',
618            'region' => 'COALESCE(a.region, b.region)',
619            'billing_client_code' => 'a.billing_client_code',
620            'cif_nif' => 'a.cif_nif',
621            'branch' => 'a.branch',
622            'freshdesk_ticket_id' => 'a.freshdesk_ticket_id',
623            'freshdesk_ticket_link' => 'a.freshdesk_ticket_link',
624            'gestiona_customer_id' => 'a.gestiona_customer_id',
625            'customer_name' => 'a.customer_name',
626            'customer_email' => 'a.customer_email',
627            'customer_telephone_number' => 'a.customer_telephone_number',
628            'existing_teams' => 'a.existing_teams',
629            'service_type' => 'a.service_type',
630            'postal_code' => 'a.postal_code',
631            'reason_for_cancellation' => 'a.reason_for_cancellation',
632            'reason_details' => 'a.reason_details',
633            'reason_details_2' => 'a.reason_details_2',
634            'change_of_maintenance_provider' => 'a.change_of_maintenance_provider',
635            'action' => 'a.action',
636            'customer_recovery' => 'a.customer_recovery',
637            'comment' => 'a.comment',
638            'private_comment' => 'a.private_comment',
639            'comment_for_administration' => 'a.comment_for_administration',
640            'date_of_receipt' => 'a.date_of_receipt',
641        ];
642
643        $w = '';
644
645        foreach ($filterModel as $field => $f) {
646            if (! is_array($f) || empty($f['type']) || ! isset($colMap[$field])) {
647                continue;
648            }
649            $type = $f['type'];
650            $col = $colMap[$field];
651
652            if ($type === 'set') {
653                $nonBlank = [];
654                $hasBlank = false;
655                foreach ((array) ($f['values'] ?? []) as $v) {
656                    if ($v === null || $v === '' || $v === '__BLANK__') {
657                        $hasBlank = true;
658                    } else {
659                        $nonBlank[] = addslashes((string) $v);
660                    }
661                }
662                $clauses = [];
663                if ($nonBlank) {
664                    $clauses[] = "{$col} IN ('".implode("','", $nonBlank)."')";
665                }
666                if ($hasBlank) {
667                    $clauses[] = "({$col} IS NULL OR {$col} = '')";
668                }
669                if ($clauses) {
670                    $w .= ' AND ('.implode(' OR ', $clauses).')';
671                }
672            } elseif ($type === 'text') {
673                $val = trim((string) ($f['contains'] ?? ''));
674                if ($val !== '') {
675                    $val = addslashes($val);
676                    $w .= " AND {$col} LIKE '%{$val}%'";
677                }
678            } elseif ($type === 'date') {
679                $from = trim((string) ($f['from'] ?? ''));
680                $to = trim((string) ($f['to'] ?? ''));
681                if ($from !== '') {
682                    $w .= " AND DATE({$col}) >= '".addslashes($from)."'";
683                }
684                if ($to !== '') {
685                    $w .= " AND DATE({$col}) <= '".addslashes($to)."'";
686                }
687            }
688        }
689
690        return $w;
691    }
692
693    /**
694     * Data-table (Advanced Search) listing for Leave — mirrors
695     * Quotations::list_orders_table. Server-side pagination, sort, OR-LIKE quick
696     * search, explicit ids / ids_not_in, and the orders-style filter model
697     * parsed by build_leave_table_filters. Pass ids_only=1 to return just the
698     * matching id list. Reads through the domain-tagged ResultCache ('leave').
699     * New endpoint — list_leave (the ag-grid one) is left untouched.
700     */
701    public function list_leave_table(Request $request)
702    {
703        try {
704            $data = $request->all();
705
706            $companyId = (int) ($data['company_id'] ?? 0);
707            $start = max(0, (int) ($data['start'] ?? 0));
708            $limit = (int) ($data['end'] ?? 300) - $start;
709            $limit = $limit > 0 ? $limit : 300;
710
711            // FIRE-1174: gate the cache to avoid per-keystroke churn.
712            $cacheSearch = trim((string) ($data['searchText'] ?? ''));
713            $shouldCacheList = $cacheSearch === '' || strlen($cacheSearch) >= 3;
714
715            $where = '';
716            if ($companyId != 0) {
717                $where .= " AND a.company_id = {$companyId} ";
718            } else {
719                $where .= " AND a.company_id IN ({$this->companyId}) ";
720            }
721
722            if (isset($data['ids']) && is_array($data['ids']) && count($data['ids']) > 0) {
723                $numericIds = array_values(array_filter(array_map('intval', $data['ids']), fn ($n) => $n > 0));
724                if (count($numericIds) > 0) {
725                    $where .= ' AND a.id IN ('.implode(',', $numericIds).') ';
726                }
727            }
728            if (isset($data['ids_not_in']) && is_array($data['ids_not_in']) && count($data['ids_not_in']) > 0) {
729                $numericIds = array_values(array_filter(array_map('intval', $data['ids_not_in']), fn ($n) => $n > 0));
730                if (count($numericIds) > 0) {
731                    $where .= ' AND a.id NOT IN ('.implode(',', $numericIds).') ';
732                }
733            }
734
735            if (isset($data['searchText']) && trim((string) $data['searchText']) !== '') {
736                $searchText = addslashes(trim((string) $data['searchText']));
737                $searchFields = [
738                    'a.id', 'b.name', 'COALESCE(a.region, b.region)', 'a.branch',
739                    'a.cif_nif', 'a.billing_client_code', 'a.customer_name',
740                    'a.customer_email', 'a.customer_telephone_number', 'a.existing_teams',
741                    'a.service_type', 'a.postal_code', 'a.reason_for_cancellation',
742                    'a.reason_details', 'a.action', 'a.customer_recovery', 'a.comment',
743                    'a.freshdesk_ticket_id', 'a.gestiona_customer_id',
744                ];
745                $likes = array_map(fn ($f) => "{$f} LIKE '%{$searchText}%'", $searchFields);
746                $where .= ' AND ('.implode(' OR ', $likes).') ';
747            }
748
749            $where .= $this->build_leave_table_filters($data['filterModel'] ?? []);
750
751            $fromJoins = '
752                FROM tbl_leave a
753                    LEFT JOIN tbl_companies b ON a.company_id = b.company_id';
754
755            if (! empty($data['ids_only'])) {
756                $idsQuery = "SELECT a.id {$fromJoins} WHERE a.id > 0 {$where} ORDER BY a.id DESC";
757                $idRows = $shouldCacheList
758                    ? ResultCache::remember('leave', $idsQuery, 600, fn () => DB::select($idsQuery))
759                    : DB::select($idsQuery);
760                $ids = array_map(fn ($r) => (int) $r->id, $idRows);
761
762                return response([
763                    'message' => 'OK',
764                    'ids' => $ids,
765                    'totalRowCount' => count($ids),
766                ]);
767            }
768
769            $orderBy = ' ORDER BY a.id DESC';
770            if (isset($data['sortModel']) && is_array($data['sortModel']) && count($data['sortModel']) > 0) {
771                $field = str_replace('_translate', '', (string) ($data['sortModel'][0]['colId'] ?? ''));
772                $dir = strtolower((string) ($data['sortModel'][0]['sort'] ?? 'asc')) === 'desc' ? 'DESC' : 'ASC';
773                $sortMap = [
774                    'id' => 'a.id', 'company_name' => 'b.name', 'region' => 'COALESCE(a.region, b.region)',
775                    'date_of_receipt' => 'a.date_of_receipt', 'branch' => 'a.branch',
776                    'cif_nif' => 'a.cif_nif', 'billing_client_code' => 'a.billing_client_code',
777                    'customer_name' => 'a.customer_name', 'customer_email' => 'a.customer_email',
778                    'service_type' => 'a.service_type', 'postal_code' => 'a.postal_code',
779                    'reason_for_cancellation' => 'a.reason_for_cancellation',
780                    'reason_details' => 'a.reason_details', 'reason_details_2' => 'a.reason_details_2',
781                    'change_of_maintenance_provider' => 'a.change_of_maintenance_provider',
782                    'action' => 'a.action', 'customer_recovery' => 'a.customer_recovery',
783                ];
784                if (isset($sortMap[$field])) {
785                    $orderBy = " ORDER BY {$sortMap[$field]} {$dir}";
786                }
787            }
788
789            $query = "SELECT
790                        a.id,
791                        b.company_id,
792                        COALESCE(a.region, b.region) region,
793                        b.name company_name,
794                        a.date_of_receipt,
795                        a.branch,
796                        a.freshdesk_ticket_id,
797                        a.freshdesk_ticket_link,
798                        a.cif_nif,
799                        a.gestiona_customer_id,
800                        a.billing_client_code,
801                        a.customer_name,
802                        a.customer_email,
803                        a.customer_telephone_number,
804                        a.existing_teams,
805                        a.service_type,
806                        a.postal_code,
807                        DATE_FORMAT(a.date_of_receipt, '%d/%m/%Y') date_of_receipt_translate,
808                        WEEK(a.date_of_receipt, 3) semana,
809                        a.reason_for_cancellation,
810                        a.reason_details,
811                        a.reason_details_2,
812                        a.change_of_maintenance_provider,
813                        a.action,
814                        a.customer_recovery,
815                        a.comment,
816                        a.private_comment,
817                        a.comment_for_administration,
818                        a.description,
819                        a.created_by,
820                        a.created_at,
821                        a.updated_by,
822                        a.updated_at
823                    {$fromJoins}
824                    WHERE a.id > 0
825                    {$where}
826                    {$orderBy}
827                    LIMIT {$start}, {$limit}";
828
829            $result = $shouldCacheList
830                ? ResultCache::remember('leave', $query, 600, fn () => DB::select($query))
831                : DB::select($query);
832
833            $totalQuery = "SELECT COUNT(a.id) totalRowCount {$fromJoins} WHERE a.id > 0 {$where}";
834            $countQuery = $shouldCacheList
835                ? ResultCache::remember('leave', $totalQuery, 600, fn () => DB::select($totalQuery))
836                : DB::select($totalQuery);
837
838            return response([
839                'message' => 'OK',
840                'data' => $result,
841                'totalRowCount' => $countQuery[0]->totalRowCount,
842            ]);
843
844        } catch (\Exception $e) {
845            /** @disregard P1014 */
846            $e->exceptionCode = 'LIST_LEAVE_TABLE_EXCEPTION';
847            report($e);
848
849            return response(['message' => 'KO', 'error' => $e->getMessage()]);
850        }
851    }
852
853    public function create_leave(Request $request)
854    {
855
856        try {
857
858            $data = $request->all();
859
860            $result = TblLeave::create($data);
861
862            // FIRE-1145: was Cache::flush() — create_leave affects list_leave.
863            ResultCache::forgetDomain('leave');
864
865            return response(['message' => 'OK', 'data' => $result]);
866
867        } catch (\Exception $e) {
868            /** @disregard P1014 */
869            $e->exceptionCode = 'CREATE_LEAVE_EXCEPTION';
870            report($e);
871
872            return response(['message' => 'KO', 'error' => $e->getMessage()]);
873        }
874    }
875
876    public function update_leave(Request $request, $id)
877    {
878
879        try {
880
881            $data = $request->all();
882            $id = intval($id);
883
884            $data['updated_at'] = date('Y-m-d H:i:s');
885            TblLeave::where('id', $id)->update($data);
886
887            // FIRE-1145: was Cache::flush() — update_leave affects list_leave.
888            ResultCache::forgetDomain('leave');
889
890            return response([
891                'message' => 'OK',
892            ]);
893
894        } catch (\Exception $e) {
895            /** @disregard P1014 */
896            $e->exceptionCode = 'UPDATE_LEAVE_EXCEPTION';
897            report($e);
898
899            return response(['message' => 'KO', 'error' => $e->getMessage()]);
900        }
901    }
902
903    public function delete_leave(Request $request)
904    {
905
906        try {
907
908            $data = $request->all();
909            $result = [];
910
911            $r = new Request([
912                'filterModel' => $data['filterModel'],
913                'sortModel' => $data['sortModel'],
914                'start' => 0,
915                'end' => 999999999,
916                'company_id' => @$data['company_id'],
917                'user_id' => $data['user_id'],
918                'ids' => $data['ids'],
919                'searchText' => $data['searchText'],
920                'ids_not_in' => $data['ids_not_in'],
921            ]);
922
923            $result = $this->list_leave_table($r);
924            $result = $result->original['data'];
925
926            $outputArray = [];
927
928            foreach ($result as $item) {
929                if (isset($item->id)) {
930                    $outputArray[] = $item->id;
931                }
932            }
933
934            TblLeave::whereIn('id', $outputArray)->delete();
935
936            // FIRE-1145: was Cache::flush() — delete_leave affects list_leave.
937            ResultCache::forgetDomain('leave');
938
939            return response(['message' => 'OK', 'data' => $result]);
940
941        } catch (\Exception $e) {
942            /** @disregard P1014 */
943            $e->exceptionCode = 'DELETE_LEAVE_EXCEPTION';
944            report($e);
945
946            return response(['message' => 'KO', 'error' => $e->getMessage()]);
947        }
948
949    }
950
951    public function get_leave($id)
952    {
953
954        try {
955
956            $id = intval($id);
957
958            $query = "SELECT
959                        a.id,
960                        a.company_id,
961                        COALESCE(a.region, b.region) region,
962                        b.name company_name,
963                        a.date_of_receipt,
964                        a.branch,
965                        a.freshdesk_ticket_id,
966                        a.freshdesk_ticket_link,
967                        a.cif_nif,
968                        a.gestiona_customer_id,
969                        a.billing_client_code,
970                        a.customer_name,
971                        a.customer_email,
972                        a.customer_telephone_number,
973                        a.existing_teams,
974                        a.service_type,
975                        a.postal_code,
976                        DATE_FORMAT(a.date_of_receipt, '%d/%m/%Y') date_of_receipt_translate,
977                        WEEK(a.date_of_receipt, 3) semana,
978                        a.reason_for_cancellation,
979                        a.reason_details,
980                        a.reason_details_2,
981                        a.change_of_maintenance_provider,
982                        a.action,
983                        a.customer_recovery,
984                        a.comment,
985                        a.private_comment,
986                        a.comment_for_administration,
987                        a.description,
988                        a.created_by,
989                        a.created_at,
990                        a.updated_by,
991                        a.updated_at
992                    FROM
993                        tbl_leave a
994                        LEFT JOIN tbl_companies b ON a.company_id = b.company_id
995                    WHERE a.id = {$id}";
996
997            $result = DB::select($query);
998
999            // FIRE-1145: was Cache::flush() — read-only path, zero benefit. Removed.
1000
1001            return response(['message' => 'OK', 'data' => $result]);
1002
1003        } catch (\Exception $e) {
1004            /** @disregard P1014 */
1005            $e->exceptionCode = 'GET_LEAVE_EXCEPTION';
1006            report($e);
1007
1008            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1009        }
1010    }
1011
1012    public function get_dates($companyId)
1013    {
1014
1015        try {
1016
1017            $where = '';
1018
1019            if ($companyId != 0) {
1020                $where .= " a.company_id = {$companyId} ";
1021            } else {
1022                $where .= " a.company_id IN ({$this->companyId}) ";
1023            }
1024
1025            $query = "SELECT
1026                        DATE_FORMAT(a.date_of_receipt, '%d/%m/%Y') date_of_receipt_translate,
1027                        DATE_FORMAT(a.created_at, '%d/%m/%Y') created_at_translate,
1028                        DATE_FORMAT(a.updated_at, '%d/%m/%Y') updated_at_translate
1029                    FROM tbl_pipelines a
1030                    WHERE {$where}";
1031
1032            $result = DB::select($query);
1033
1034            return response([
1035                'message' => 'OK',
1036                'data' => $result,
1037            ]);
1038
1039        } catch (\Exception $e) {
1040            /** @disregard P1014 */
1041            $e->exceptionCode = 'GET_DATES_EXCEPTION';
1042            report($e);
1043
1044            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1045        }
1046
1047    }
1048
1049    public function download_leave(Request $request)
1050    {
1051        ini_set('max_execution_time', 123456);
1052        $data = $request->all();
1053        $companyId = intval($data['company_id']);
1054        $userId = intval($data['user_id']);
1055
1056        $r = new Request([
1057            'filterModel' => $data['filterModel'],
1058            'sortModel' => $data['sortModel'],
1059            'start' => 0,
1060            'end' => 999999999,
1061            'company_id' => $data['company_id'],
1062            'user_id' => $data['user_id'],
1063            'ids' => $data['ids'],
1064            'searchText' => $data['searchText'],
1065            'ids_not_in' => $data['ids_not_in'],
1066        ]);
1067
1068        $result = $this->list_leave_table($r);
1069        $result = $result->original['data'];
1070
1071        return response(['data' => $result]);
1072    }
1073}