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