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