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