Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
0.00%
0 / 417
0.00%
0 / 8
 CRAP
0.00%
0 / 1
Leave
0.00%
0 / 417
0.00%
0 / 8
 11342
0.00%
0 / 1
 __construct
0.00%
0 / 13
0.00%
0 / 1
 20
 list_leave
0.00%
0 / 315
0.00%
0 / 1
 7832
 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 / 10
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 Illuminate\Http\Request;
8use Illuminate\Support\Facades\App;
9use Illuminate\Support\Facades\Cache;
10use Illuminate\Support\Facades\DB;
11
12class Leave extends Controller
13{
14    private $locale;
15
16    private $userId;
17
18    private $region;
19
20    private $companyIds;
21
22    private $companyId;
23
24        public function __construct(){
25        $this->locale = request()->header('Locale-Id');
26        $this->userId = request()->header('User-Id');
27        $this->region = request()->header('Region');
28
29        App::setLocale($this->locale);
30
31        $this->companyIds = [];
32
33        if($this->region != null && $this->region != "" && $this->region != "All"){
34            $this->region = urldecode((string) $this->region);
35
36            $query = 'SELECT
37                        b.company_id
38                    FROM
39                        tbl_company_users a
40                        LEFT JOIN tbl_companies b ON a.company_id = b.company_id
41                    WHERE
42                        a.user_id = ?
43                        AND b.region = ?';
44
45            $this->companyIds = DB::select($query, [intval($this->userId), $this->region]);
46
47            $this->companyIds = collect($this->companyIds)->pluck('company_id')->toArray();
48        } else {
49            $this->companyIds = TblCompanyUsers::where('user_id', $this->userId)->pluck('company_id')->all();
50        }
51
52        $this->companyId = implode(',', $this->companyIds);
53    }
54
55    public function list_leave(Request $request)
56    {
57
58        try {
59
60            $data = $request->all();
61            $companyId = intval($data['company_id']);
62            $userId = intval($data['user_id']);
63            $filter = $data['filterModel'];
64            $sort = $data['sortModel'];
65            $result = [];
66            $subquery = '';
67            $where = '';
68            $having = '';
69            $orderBy = '';
70            $start = intval($data['start']);
71            $end = intval($data['end']);
72            $totalRowCount = 0;
73            $withFilters = '';
74
75            $pdo = DB::connection()->getPdo();
76
77            // Allowlisted columns for sorting and filtering to prevent SQL injection
78            $allowedColumns = [
79                'a.id', 'a.date_of_receipt', 'a.branch', 'a.freshdesk_ticket_id',
80                'a.gestiona_customer_id', 'a.customer_name', 'a.customer_email',
81                'a.customer_telephone_number', 'a.existing_teams',
82                'a.reason_for_cancellation', 'a.reason_details',
83                'a.change_of_maintenance_provider', 'a.action',
84                'a.customer_recovery', 'a.comment', 'a.private_comment',
85                'a.comment_for_administration', 'a.description',
86                'a.created_by', 'a.created_at', 'a.updated_by', 'a.updated_at',
87                'a.company_id', 'a.visit_date', 'a.freshdesk_ticket_link',
88                'b.company_id', 'b.region', 'b.name',
89                'company_name', 'id', 'date_of_receipt', 'branch',
90                'freshdesk_ticket_id', 'gestiona_customer_id', 'customer_name',
91                'customer_email', 'customer_telephone_number', 'existing_teams',
92                'reason_for_cancellation', 'reason_details',
93                'change_of_maintenance_provider', 'action',
94                'customer_recovery', 'comment', 'private_comment',
95                'comment_for_administration', 'description',
96                'created_by', 'created_at', 'updated_by', 'updated_at',
97                'visit_date',
98            ];
99
100            $allowedFilterTypeKeys = [
101                'contains', 'notContains', 'equals', 'notEqual',
102                'startsWith', 'endsWith', 'blank', 'notBlank',
103                'lessThan', 'lessThanOrEqual', 'greaterThan',
104                'greaterThanOrEqual', 'inRange',
105            ];
106
107            $filterType = [
108                'contains' => "LIKE '%[value]%'",
109                'notContains' => "NOT LIKE '%[value]%'",
110                'equals' => "= '[value]'",
111                'notEqual' => "<> '[value]'",
112                'startsWith' => "LIKE '[value]%'",
113                'endsWith' => "LIKE '%[value]'",
114                'blank' => 'IS NULL',
115                'notBlank' => 'IS NOT NULL',
116                'lessThan' => '< [value]',
117                'lessThanOrEqual' => '<= [value]',
118                'greaterThan' => '> [value]',
119                'greaterThanOrEqual' => '>= [value]',
120                'inRange' => 'BETWEEN [value1] AND [value2]',
121            ];
122
123            if (isset($data['ids']) && count($data['ids']) > 0) {
124                $ids = implode(',', array_map('intval', $data['ids']));
125                $where = " AND a.id IN ({$ids}) ";
126            }
127
128            if (isset($data['ids_not_in']) && count($data['ids_not_in']) > 0) {
129                $ids = implode(',', array_map('intval', $data['ids_not_in']));
130                $where = " AND a.id NOT IN ({$ids}) ";
131            }
132
133            if ($companyId != 0) {
134                $where .= " AND a.company_id = {$companyId} ";
135            } else {
136                $where .= " AND a.company_id IN ({$this->companyId}) ";
137            }
138
139            $matchScoreCol = '';
140            $matchScoreOrderBy = '';
141
142            if (isset($data['searchText']) && $data['searchText'] != null) {
143
144                $availableParameters = [
145                    'a.date_of_receipt',
146                    'a.branch',
147                    'a.freshdesk_ticket_id',
148                    'a.gestiona_customer_id',
149                    'a.customer_name',
150                    'a.customer_email',
151                    'a.customer_telephone_number',
152                    'a.existing_teams',
153                    'a.reason_for_cancellation',
154                    'a.reason_details',
155                    'a.change_of_maintenance_provider',
156                    'a.action',
157                    'a.customer_recovery',
158                    'a.comment',
159                    'a.private_comment',
160                    'a.comment_for_administration',
161                    'a.created_by',
162                    'a.created_at',
163                    'a.updated_by',
164                    'a.updated_at',
165                ];
166
167                // Use PDO::quote() for charset-aware escaping instead of addslashes()
168                $searchText = substr($pdo->quote($data['searchText']), 1, -1);
169                $rawWords = explode(' ', $data['searchText']);
170                $searchTextArray = array_map(function ($word) use ($pdo) {
171                    return substr($pdo->quote($word), 1, -1);
172                }, $rawWords);
173
174                $searchArray = [];
175                $splitSearchArray = [];
176                $matchScoreArray = [];
177                $sc = 1;
178                foreach ($availableParameters as $field) {
179                    if ($field == 'a.customer_name' || $field == 'a.created_at') {
180                        $sc = 3;
181                    } elseif ($field == 'a.date_of_receipt') {
182                        $sc = 2;
183                    } else {
184                        $sc = 1;
185                    }
186
187                    $l = "{$field} LIKE '%{$searchText}%'";
188
189                    $d = "IFNULL((LENGTH(LOWER({$field})) - LENGTH(REPLACE(LOWER({$field}), LOWER('{$searchText}'), ''))) / LENGTH(LOWER('{$searchText}')), 0) * {$sc}";
190
191                    if (count($searchTextArray) > 1) {
192                        foreach ($searchTextArray as $word) {
193                            if (! is_numeric($word)) {
194                                $d .= " + IFNULL((LENGTH(LOWER({$field})) - LENGTH(REPLACE(LOWER({$field}), LOWER('{$word}'), ''))) / LENGTH(LOWER('{$word}')), 0) * {$sc}";
195                            }
196                        }
197                    }
198
199                    array_push($matchScoreArray, $d);
200
201                    if (is_numeric($searchText)) {
202                        array_push($searchArray, "({$l} OR {$field} = CAST('{$searchText}' AS UNSIGNED))");
203                    } else {
204                        array_push($searchArray, "({$l} OR DATE_FORMAT({$field}, '%d/%m/%Y') = DATE_FORMAT(STR_TO_DATE('{$searchText}', '%d/%m/%Y'), '%d/%m/%Y'))");
205                    }
206
207                    if (count($searchTextArray) > 1) {
208                        foreach ($searchTextArray as $word) {
209
210                            $l = "{$field} LIKE '%{$word}%'";
211
212                            if (is_numeric($word)) {
213                                array_push($splitSearchArray, "{$l} OR {$field} = CAST('{$word}' AS UNSIGNED)");
214                            } else {
215                                array_push($splitSearchArray, "{$l} OR DATE_FORMAT({$field}, '%d/%m/%Y') = DATE_FORMAT(STR_TO_DATE('{$word}', '%d/%m/%Y'), '%d/%m/%Y')");
216                            }
217                        }
218                    }
219
220                    $sc = 1;
221                }
222
223                if (count($splitSearchArray) > 0) {
224                    $splitSearchArray = implode(' OR ', $splitSearchArray);
225                    $splitSearchArray = " OR ({$splitSearchArray}) ";
226                } else {
227                    $splitSearchArray = '';
228                }
229
230                $searchArray = implode(' OR ', $searchArray);
231                $matchScoreArray = implode(',', $matchScoreArray);
232                $matchScoreCol = ", GREATEST({$matchScoreArray}) match_score";
233                $matchScoreOrderBy = 'match_score DESC,';
234                $where .= " AND ({$searchArray} {$splitSearchArray})";
235            }
236
237            if (count($sort) > 0) {
238                $field = $sort[0]['colId'];
239                $sortBy = $sort[0]['sort'];
240
241                if (strpos($field, 'translate') !== false) {
242                    $field = str_replace('_translate', '', $field);
243                }
244
245                // Validate sort column and direction against allowlists
246                if (! in_array($field, $allowedColumns)) {
247                    $field = 'a.id';
248                }
249                if (! in_array(strtoupper($sortBy), ['ASC', 'DESC'])) {
250                    $sortBy = 'DESC';
251                }
252
253                if ($matchScoreOrderBy) {
254                    $matchScoreOrderBy = ', match_score DESC';
255                }
256
257                $orderBy = " ORDER BY {$field} {$sortBy} {$matchScoreOrderBy}";
258            } else {
259                $orderBy = " ORDER BY {$matchScoreOrderBy} a.id DESC";
260            }
261
262            foreach ($filter as $key => $data) {
263                if (strpos($key, 'translate') !== false) {
264
265                    $field = str_replace('_translate', '', $key);
266
267                    if ($field == 'created_at') {
268                        $field = 'a.created_at';
269                    } elseif ($field == 'updated_at') {
270                        $field = 'a.updated_at';
271                    } elseif ($field == 'visit_date') {
272                        $field = 'a.visit_date';
273                    }
274
275                    // Validate date field against allowlist
276                    if (! in_array($field, $allowedColumns)) {
277                        continue;
278                    }
279
280                    $whereDates = '';
281                    $z = 0;
282
283                    if (isset($data['filters']) && ! empty($data['filters'])) {
284                        foreach ($data['filters'] as $yearKey => $yearData) {
285                            $yearsMonths = [];
286                            $yearsWeeks = [];
287
288                            $yearKey = intval($yearKey);
289
290                            if ($z > 0) {
291                                $whereDates .= " OR (YEAR($field) = {$yearKey} ";
292                            } else {
293                                $whereDates .= " (YEAR($field) = {$yearKey} ";
294                            }
295
296                            for ($i = 0; $i < count($yearData['months']); $i++) {
297                                if ($yearData['months'][$i]['isChecked']) {
298                                    array_push($yearsMonths, intval($yearData['months'][$i]['value']));
299                                }
300                            }
301
302                            $yearsMonths = implode("','", $yearsMonths);
303                            $whereDates .= " AND (MONTH({$field}) IN ('{$yearsMonths}')";
304
305                            for ($i = 0; $i < count($yearData['weeks']); $i++) {
306                                if ($yearData['weeks'][$i]['isChecked']) {
307                                    array_push($yearsWeeks, intval($yearData['weeks'][$i]['value']));
308                                }
309                            }
310
311                            $yearsWeeks = implode("','", $yearsWeeks);
312                            if ($yearsWeeks != '') {
313                                $whereDates .= " OR WEEK({$field}) IN ('{$yearsWeeks}') ";
314                            }
315
316                            $whereDates .= ')) ';
317                            $z++;
318                        }
319                    }
320
321                    $whereBlanks = '';
322                    if (isset($data['isBlanks'])) {
323                        if ($data['isBlanks']) {
324                            $conj = 'OR';
325                            if ($whereDates == '') {
326                                $conj = '';
327                            }
328                            $whereBlanks .= " {$conj} {$field} IS NULL ";
329                        } else {
330                            $conj = 'AND';
331                            if ($whereDates == '') {
332                                $conj = '';
333                            }
334                            $whereBlanks .= " {$conj} {$field} IS NOT NULL ";
335                        }
336                    }
337
338                    $where .= " AND ({$whereDates} {$whereBlanks}) ";
339                } else {
340                    // Validate filter column against allowlist
341                    if (! in_array($key, $allowedColumns)) {
342                        continue;
343                    }
344
345                    if ($data['filterType'] == 'number') {
346                        if (array_key_exists('operator', $data)) {
347                            // Validate operator against allowlist
348                            $operator = strtoupper($data['operator']);
349                            if (! in_array($operator, ['AND', 'OR'])) {
350                                $operator = 'AND';
351                            }
352
353                            if ($data['condition1']['type'] != 'blank' && $data['condition2']['type'] != 'notBlank') {
354                                if (! in_array($data['condition1']['type'], $allowedFilterTypeKeys) || ! in_array($data['condition2']['type'], $allowedFilterTypeKeys)) {
355                                    continue;
356                                }
357
358                                $data['condition1']['filter'] = substr($pdo->quote($data['condition1']['filter']), 1, -1);
359                                $data['condition2']['filter'] = substr($pdo->quote($data['condition2']['filter']), 1, -1);
360
361                                if ($data['condition1']['type'] == 'inRange') {
362                                    $data['condition1']['filterTo'] = substr($pdo->quote($data['condition1']['filterTo']), 1, -1);
363                                    $inRange = str_replace('[value1]', $data['condition1']['filter'], $filterType['inRange']);
364                                    $val1 = str_replace('[value2]', $data['condition1']['filterTo'], $inRange);
365                                } else {
366                                    $val1 = str_replace('[value]', $data['condition1']['filter'], $filterType[$data['condition1']['type']]);
367                                }
368
369                                if ($data['condition2']['type'] == 'inRange') {
370                                    $data['condition2']['filterTo'] = substr($pdo->quote($data['condition2']['filterTo']), 1, -1);
371                                    $inRange = str_replace('[value1]', $data['condition2']['filter'], $filterType['inRange']);
372                                    $val2 = str_replace('[value2]', $data['condition2']['filterTo'], $inRange);
373                                } else {
374                                    $val2 = str_replace('[value]', $data['condition2']['filter'], $filterType[$data['condition2']['type']]);
375                                }
376
377                            } else {
378                                if (! in_array($data['condition1']['type'], $allowedFilterTypeKeys) || ! in_array($data['condition2']['type'], $allowedFilterTypeKeys)) {
379                                    continue;
380                                }
381                                $val1 = $filterType[$data['condition1']['type']];
382                                $val2 = $filterType[$data['condition2']['type']];
383                            }
384
385                            $where .= " AND a.{$key} {$val1} {$operator} a.{$key} {$val2} ";
386                        } else {
387                            if ($data['type'] != 'blank' && $data['type'] != 'notBlank') {
388                                if (! in_array($data['type'], $allowedFilterTypeKeys)) {
389                                    continue;
390                                }
391
392                                $data['filter'] = substr($pdo->quote($data['filter']), 1, -1);
393
394                                if ($data['type'] == 'inRange') {
395                                    $data['filterTo'] = substr($pdo->quote($data['filterTo']), 1, -1);
396                                    $inRange = str_replace('[value1]', $data['filter'], $filterType['inRange']);
397                                    $val = str_replace('[value2]', $data['filterTo'], $inRange);
398                                } else {
399                                    $val = str_replace('[value]', $data['filter'], $filterType[$data['type']]);
400                                }
401                            } else {
402                                if (! in_array($data['type'], $allowedFilterTypeKeys)) {
403                                    continue;
404                                }
405                                $val = $filterType[$data['type']];
406                            }
407
408                            $where .= " AND a.{$key} {$val} ";
409                        }
410                    }
411
412                    if ($data['filterType'] == 'text') {
413                        if (array_key_exists('operator', $data)) {
414                            // Validate operator against allowlist
415                            $operator = strtoupper($data['operator']);
416                            if (! in_array($operator, ['AND', 'OR'])) {
417                                $operator = 'AND';
418                            }
419
420                            $val1 = '';
421                            $val2 = '';
422                            if ($data['condition1']['type'] != 'blank' && $data['condition2']['type'] != 'notBlank') {
423                                if (! in_array($data['condition1']['type'], $allowedFilterTypeKeys)) {
424                                    continue;
425                                }
426                                $data['condition1']['filter'] = substr($pdo->quote($data['condition1']['filter']), 1, -1);
427                                $val1 = str_replace('[value]', $data['condition1']['filter'], $filterType[$data['condition1']['type']]);
428                            }
429
430                            if ($data['condition2']['type'] != 'blank' && $data['condition2']['type'] != 'notBlank') {
431                                if (! in_array($data['condition2']['type'], $allowedFilterTypeKeys)) {
432                                    continue;
433                                }
434                                $data['condition2']['filter'] = substr($pdo->quote($data['condition2']['filter']), 1, -1);
435                                $val2 = str_replace('[value]', $data['condition2']['filter'], $filterType[$data['condition2']['type']]);
436                            }
437
438                            $where .= " AND {$key} {$val1} {$operator} {$key} {$val2} ";
439                        } else {
440                            if ($data['type'] != 'blank' && $data['type'] != 'notBlank') {
441                                if (! in_array($data['type'], $allowedFilterTypeKeys)) {
442                                    continue;
443                                }
444                                $data['filter'] = substr($pdo->quote($data['filter']), 1, -1);
445                                $val = str_replace('[value]', $data['filter'], $filterType[$data['type']]);
446                            } else {
447                                if (! in_array($data['type'], $allowedFilterTypeKeys)) {
448                                    continue;
449                                }
450                                $val = $filterType[$data['type']];
451                            }
452
453                            $where .= " AND {$key} {$val} ";
454                        }
455                    }
456
457                    if ($data['filterType'] == 'set') {
458                        $statusName = $key;
459
460                        if ($key == 'updated_by') {
461                            $statusName = 'a.updated_by';
462                        } elseif ($key == 'company_name') {
463                            $statusName = 'b.name';
464                        } elseif ($key == 'reason_for_cancellation') {
465                            $statusName = 'a.reason_for_cancellation';
466                        } elseif ($key == 'created_by') {
467                            $statusName = 'a.created_by';
468                        } elseif ($key == 'reason_details') {
469                            $statusName = 'a.reason_details';
470                        } elseif ($key == 'change_of_maintenance_provider') {
471                            $statusName = 'a.change_of_maintenance_provider';
472                        } elseif ($key == 'action') {
473                            $statusName = 'a.action';
474                        } elseif ($key == 'customer_recovery') {
475                            $statusName = 'a.customer_recovery';
476                        }
477
478                        $escapedValues = array_map(function ($v) use ($pdo) {
479                            return $v === null ? null : substr($pdo->quote($v), 1, -1);
480                        }, $data['values']);
481                        $nonNullValues = array_filter($escapedValues, function ($v) {
482                            return $v !== null;
483                        });
484                        $val = implode("','", $nonNullValues);
485
486                        if (in_array(null, $data['values'], true)) {
487                            $where .= " AND ({$statusName} IN ('{$val}') OR {$statusName} IS NULL) ";
488                        } else {
489                            $where .= " AND {$statusName} IN ('{$val}') ";
490                        }
491                    }
492                }
493            }
494
495            $offset = $start;
496            $limit = $end - $start;
497
498            $query = "SELECT 
499                        a.id,
500                        b.company_id,
501                        b.region,
502                        b.name company_name,
503                        a.date_of_receipt, 
504                        a.branch,
505                        a.freshdesk_ticket_id,
506                        a.freshdesk_ticket_link,
507                        a.gestiona_customer_id,
508                        a.customer_name,
509                        a.customer_email,
510                        a.customer_telephone_number, 
511                        a.existing_teams,
512                        DATE_FORMAT(a.date_of_receipt, '%d/%m/%Y') date_of_receipt_translate,
513                        a.reason_for_cancellation,
514                        a.reason_details,
515                        a.change_of_maintenance_provider,
516                        a.action,
517                        a.customer_recovery,
518                        a.comment,
519                        a.private_comment,
520                        a.comment_for_administration,
521                        a.description,
522                        a.created_by,
523                        a.created_at,
524                        a.updated_by,
525                        a.updated_at
526                        {$matchScoreCol}
527                    FROM 
528                        tbl_leave a 
529                        LEFT JOIN tbl_companies b ON a.company_id = b.company_id                        
530                    WHERE a.id > 0 
531                    {$where}
532                    {$orderBy}
533                    LIMIT {$offset}, {$limit}
534                    ";
535
536            $value = Cache::get(base64_encode($query));
537
538            if (! $value) {
539                $result = DB::select($query);
540
541                Cache::put(base64_encode($query), $result, 600);
542            } else {
543                $result = $value;
544            }
545
546            $totalQuery = "SELECT 
547                            COUNT(a.id) totalRowCount
548                        FROM 
549                            tbl_leave a 
550                        WHERE a.id > 0
551                        {$where}";
552
553            $value = Cache::get(base64_encode($totalQuery));
554
555            if (! $value) {
556                $countQuery = DB::select($totalQuery);
557
558                Cache::put(base64_encode($totalQuery), $countQuery, 600);
559            } else {
560                $countQuery = $value;
561            }
562
563            return response([
564                'message' => 'OK',
565                'data' => $result,
566                'totalRowCount' => $countQuery[0]->totalRowCount,
567            ]);
568
569        } catch (\Exception $e) {
570            /** @disregard P1014 */
571            $e->exceptionCode = 'LIST_LEAVE_EXCEPTION';
572            report($e);
573
574            return response(['message' => 'KO', 'error' => $e->getMessage()]);
575        }
576
577    }
578
579    public function create_leave(Request $request)
580    {
581
582        try {
583
584            $data = $request->all();
585
586            $result = TblLeave::create($data);
587
588            Cache::flush();
589
590            return response(['message' => 'OK', 'data' => $result]);
591
592        } catch (\Exception $e) {
593            /** @disregard P1014 */
594            $e->exceptionCode = 'CREATE_LEAVE_EXCEPTION';
595            report($e);
596
597            return response(['message' => 'KO', 'error' => $e->getMessage()]);
598        }
599    }
600
601    public function update_leave(Request $request, $id)
602    {
603
604        try {
605
606            $data = $request->all();
607            $id = intval($id);
608
609            $data['updated_at'] = date('Y-m-d H:i:s');
610            TblLeave::where('id', $id)->update($data);
611
612            Cache::flush();
613
614            return response([
615                'message' => 'OK',
616            ]);
617
618        } catch (\Exception $e) {
619            /** @disregard P1014 */
620            $e->exceptionCode = 'UPDATE_LEAVE_EXCEPTION';
621            report($e);
622
623            return response(['message' => 'KO', 'error' => $e->getMessage()]);
624        }
625    }
626
627    public function delete_leave(Request $request)
628    {
629
630        try {
631
632            $data = $request->all();
633            $result = [];
634
635            $r = new Request([
636                'filterModel' => $data['filterModel'],
637                'sortModel' => $data['sortModel'],
638                'start' => 0,
639                'end' => 999999999,
640                'company_id' => @$data['company_id'],
641                'user_id' => $data['user_id'],
642                'ids' => $data['ids'],
643                'searchText' => $data['searchText'],
644                'ids_not_in' => $data['ids_not_in'],
645            ]);
646
647            $result = $this->list_leave($r);
648            $result = $result->original['data'];
649
650            $outputArray = [];
651
652            foreach ($result as $item) {
653                if (isset($item->id)) {
654                    $outputArray[] = $item->id;
655                }
656            }
657
658            TblLeave::whereIn('id', $outputArray)->delete();
659
660            Cache::flush();
661
662            return response(['message' => 'OK', 'data' => $result]);
663
664        } catch (\Exception $e) {
665            /** @disregard P1014 */
666            $e->exceptionCode = 'DELETE_LEAVE_EXCEPTION';
667            report($e);
668
669            return response(['message' => 'KO', 'error' => $e->getMessage()]);
670        }
671
672    }
673
674    public function get_leave($id)
675    {
676
677        try {
678
679            $id = intval($id);
680
681            $query = "SELECT 
682                        a.id,
683                        b.company_id,
684                        b.region,
685                        b.name company_name,
686                        a.date_of_receipt, 
687                        a.branch,
688                        a.freshdesk_ticket_id,
689                        a.freshdesk_ticket_link,
690                        a.gestiona_customer_id,
691                        a.customer_name,
692                        a.customer_email,
693                        a.customer_telephone_number, 
694                        a.existing_teams,
695                        DATE_FORMAT(a.date_of_receipt, '%d/%m/%Y') date_of_receipt_translate,
696                        a.reason_for_cancellation,
697                        a.reason_details,
698                        a.change_of_maintenance_provider,
699                        a.action,
700                        a.customer_recovery,
701                        a.comment,
702                        a.private_comment,
703                        a.comment_for_administration,
704                        a.description,
705                        a.created_by,
706                        a.created_at,
707                        a.updated_by,
708                        a.updated_at
709                    FROM 
710                        tbl_leave a 
711                    WHERE a.id = {$id}";
712
713            $result = DB::select($query);
714
715            Cache::flush();
716
717            return response(['message' => 'OK', 'data' => $result]);
718
719        } catch (\Exception $e) {
720            /** @disregard P1014 */
721            $e->exceptionCode = 'GET_LEAVE_EXCEPTION';
722            report($e);
723
724            return response(['message' => 'KO', 'error' => $e->getMessage()]);
725        }
726    }
727
728    public function get_dates($companyId)
729    {
730
731        try {
732
733            $where = '';
734
735            if ($companyId != 0) {
736                $where .= " a.company_id = {$companyId} ";
737            } else {
738                $where .= " a.company_id IN ({$this->companyId}) ";
739            }
740
741            $query = "SELECT
742                        DATE_FORMAT(a.date_of_receipt, '%d/%m/%Y') date_of_receipt_translate,
743                        DATE_FORMAT(a.created_at, '%d/%m/%Y') created_at_translate,
744                        DATE_FORMAT(a.updated_at, '%d/%m/%Y') updated_at_translate
745                    FROM tbl_pipelines a
746                    WHERE {$where}";
747
748            $result = DB::select($query);
749
750            return response([
751                'message' => 'OK',
752                'data' => $result,
753            ]);
754
755        } catch (\Exception $e) {
756            /** @disregard P1014 */
757            $e->exceptionCode = 'GET_DATES_EXCEPTION';
758            report($e);
759
760            return response(['message' => 'KO', 'error' => $e->getMessage()]);
761        }
762
763    }
764
765    function download_leave(Request $request)
766    {
767        ini_set('max_execution_time', 123456);
768        $data = $request->all();
769        $companyId = intval($data['company_id']);
770        $userId = intval($data['user_id']);
771
772        $r = new Request([
773            'filterModel' => $data['filterModel'],
774            'sortModel' => $data['sortModel'],
775            'start' => 0,
776            'end' => 999999999,
777            'company_id' => $data['company_id'],
778            'user_id' => $data['user_id'],
779            'ids' => $data['ids'],
780            'searchText' => $data['searchText'],
781            'ids_not_in' => $data['ids_not_in'],
782        ]);
783
784        $result = $this->list_leave($r);
785        $result = $result->original['data'];
786        return response(['data' => $result]);
787    }
788}