Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
0.00%
0 / 491
0.00%
0 / 24
 CRAP
0.00%
0 / 1
Users
0.00%
0 / 491
0.00%
0 / 24
 11342
0.00%
0 / 1
 __construct
0.00%
0 / 13
0.00%
0 / 1
 20
 create_users
0.00%
0 / 45
0.00%
0 / 1
 110
 get_users
0.00%
0 / 11
0.00%
0 / 1
 12
 get_user
0.00%
0 / 23
0.00%
0 / 1
 30
 getUserByName
0.00%
0 / 18
0.00%
0 / 1
 12
 getAllUserNames
0.00%
0 / 12
0.00%
0 / 1
 6
 get_user_by_email
0.00%
0 / 25
0.00%
0 / 1
 56
 add_company_user
0.00%
0 / 22
0.00%
0 / 1
 30
 delete_company_user
0.00%
0 / 8
0.00%
0 / 1
 6
 update_users
0.00%
0 / 96
0.00%
0 / 1
 272
 delete_users
0.00%
0 / 24
0.00%
0 / 1
 42
 get_roles
0.00%
0 / 5
0.00%
0 / 1
 6
 get_all_commercials
0.00%
0 / 11
0.00%
0 / 1
 6
 get_g3w_warning_fields
0.00%
0 / 21
0.00%
0 / 1
 20
 get_created_by
0.00%
0 / 21
0.00%
0 / 1
 20
 get_commercial_with_pendings
0.00%
0 / 20
0.00%
0 / 1
 20
 get_responsible_for_work
0.00%
0 / 21
0.00%
0 / 1
 20
 get_job_created_by
0.00%
0 / 21
0.00%
0 / 1
 20
 get_accepted_by
0.00%
0 / 21
0.00%
0 / 1
 20
 get_commercials
0.00%
0 / 21
0.00%
0 / 1
 20
 update_users_itv
0.00%
0 / 9
0.00%
0 / 1
 20
 list_roles
0.00%
0 / 5
0.00%
0 / 1
 6
 update_role
0.00%
0 / 11
0.00%
0 / 1
 12
 delete_role
0.00%
0 / 7
0.00%
0 / 1
 6
1<?php
2
3namespace App\Http\Controllers;
4
5use App\Models\TblCompanyUsers;
6use App\Models\TblOngoingJobs;
7use App\Models\TblQuotations;
8use App\Models\TblRoles;
9use App\Models\TblUsers;
10use Carbon\Carbon;
11use Illuminate\Http\Request;
12use Illuminate\Support\Facades\App;
13use Illuminate\Support\Facades\Cache;
14use Illuminate\Support\Facades\DB;
15use Illuminate\Support\Facades\Http;
16use Illuminate\Support\Facades\Log;
17use Illuminate\Support\Str;
18use Illuminate\Contracts\Routing\ResponseFactory;
19use Illuminate\Http\Response;
20use App\Exceptions\AppException;
21
22class Users extends Controller
23{
24    private $locale;
25
26    private $userId;
27
28    private $region;
29
30    private $companyIds;
31    private readonly string $companyId;
32
33    public function __construct(){
34        $this->locale = request()->header('Locale-Id');
35        $this->userId = request()->header('User-Id');
36        $this->region = request()->header('Region');
37
38        App::setLocale($this->locale);
39
40        $this->companyIds = [];
41
42        if($this->region != null && $this->region != "" && $this->region != "All"){
43            $this->region = urldecode((string) $this->region);
44
45            $query = 'SELECT
46                        b.company_id
47                    FROM
48                        tbl_company_users a
49                        LEFT JOIN tbl_companies b ON a.company_id = b.company_id
50                    WHERE
51                        a.user_id = ?
52                        AND b.region = ?';
53
54            $this->companyIds = DB::select($query, [intval($this->userId), $this->region]);
55
56            $this->companyIds = collect($this->companyIds)->pluck('company_id')->toArray();
57        } else {
58            $this->companyIds = TblCompanyUsers::where('user_id', $this->userId)->pluck('company_id')->all();
59        }
60
61        $this->companyId = implode(',', $this->companyIds);
62    }
63
64    public function create_users(Request $request): ResponseFactory|Response
65    {
66
67        try {
68        $data = $request->all();
69        $data['role_id'] = 2;
70
71        $sData = [
72            'name' => $data['name'],
73            'email' => $data['email'],
74            'created_by' => $data['created_by'],
75            'role_id' => 2,
76            'sender_email' => $data['sender_email'],
77            'is_commercial' => $data['is_commercial'] ?? null,
78            'sender_enabled' => @$data['sender_enabled'],
79            'G3W_code' => $data['G3W_code'],
80        ];
81
82            $email = TblUsers::where('email', $data['email'])->count();
83
84            if($email > 0){
85                return response(['message' => 'KO', 'error' => __('language.email_already_exist')]);
86            }
87
88            $name = TblUsers::where('name', $data['name'])->count();
89
90            if ($name > 0) {
91                return response(['message' => 'KO', 'error' => __('language.name_already_exist')]);
92            }
93
94            $sendgrid = new \SendGrid(config('services.sendgrid.api_key'));
95            $data['address'] = "Madrid";
96            $data['city'] = "Madrid";
97            $data['country'] = "Spain";
98            $data['nickname'] = $data['name'] . "-" . base64_encode($data['name'] . date('ymdhis'));
99            $data['from_name'] = $data['name'];
100            $data['from_email'] = $data['sender_email'];
101            $data['reply_to'] = $data['sender_email'];
102            $data['reply_to_name'] = $data['name'];
103            $requestBody = $data;
104            $error  = false;
105
106            $response = $sendgrid->client->verified_senders()->post($requestBody);
107            $x = json_decode((string) $response->body());
108
109            if ($error) {
110                if ($response->errors[0]->message == 'already exists' && $response->errors[0]->field == 'from_email') {
111                    $result = TblUsers::create($sData);
112
113                    $response = json_decode((string) $response->body());
114
115                    $errMessage = @$response->errors[0]->field.': '.@$response->errors[0]->message;
116
117                    return response(['message' => 'KO', 'error' => $errMessage]);
118                }
119            } else {
120                $isVerified = 'no';
121
122                if (isset($result)) {
123                    $u = TblUsers::where('id', $result->id)->first();
124
125                    if ($u && $u->verified == 1) {
126                        $isVerified = 'yes';
127                    }
128                }
129
130                return response(['message' => 'OK', 'data' => $response, 'is_verified' => $isVerified]);
131            }
132
133        } catch (\Exception $e) {
134            return response(['message' => 'KO', 'error' => $e->getMessage()]);
135        }
136    }
137
138    public function get_users($companyId = null): ResponseFactory|Response{
139
140        try {
141
142            $column = '';
143
144            if ($companyId != null) {
145                $column = "(SELECT id FROM tbl_company_users WHERE user_id = a.id AND company_id = {$companyId}) user,";
146            }
147
148            $query = "SELECT
149                        a.id,
150                        a.name,
151                        a.email,
152                        DATE_FORMAT(a.created_at, '%b %d, %Y %l:%i%p') 'created_at',
153                        a.created_by,
154                        DATE_FORMAT(a.created_at, '%b %d, %Y %l:%i%p') 'updated_at',
155                        a.updated_by,
156                        a.sender_email,
157                        a.sender_enabled,
158                        a.is_itv,
159                        a.verified,
160                        a.is_commercial,
161                        {$column}
162                        a.default
163                    FROM tbl_users a
164                    ORDER BY a.name ASC";
165
166            $result = DB::select($query);
167
168            return response(['message' => 'OK', 'data' => $result]);
169
170        } catch (\Exception $e) {
171            report(AppException::fromException($e, 'GET_USERS_EXCEPTION'));
172            return response(['message' => 'KO', 'error' => $e->getMessage()]);
173        }
174    }
175
176    public function get_user($id): ResponseFactory|Response{
177
178        try {
179
180            $id = addslashes((string) $id);
181
182            $e = TblUsers::where('id', $id)->first();
183
184            $sendgrid = new \SendGrid(config('services.sendgrid.api_key'));
185
186            $response = $sendgrid->client->verified_senders()->get(null, [
187                'limit' => 1000,
188                'offset' => 0,
189            ]);
190
191            if ($response->statusCode() == 200) {
192                $x = json_decode((string) $response->body())->results;
193
194                foreach ($x as $item) {
195                    if($item->from_email == $e->sender_email){
196                        TblUsers::where('sender_email', $item->from_email)->update([
197                            'verified' => $item->verified,
198                            'response_id' => $item->id
199                        ]);
200                        break;
201                    }
202                }
203            }
204
205            $query = "SELECT
206                        a.id,
207                        a.name,
208                        a.email,
209                        a.created_at,
210                        a.created_by,
211                        a.updated_at,
212                        a.updated_by,
213                        a.default,
214                        a.role_id,
215                        b.name 'role',
216                        a.verified,
217                        a.is_itv,
218                        a.sender_email,
219                        a.is_commercial,
220                        a.sender_enabled,
221                        a.G3W_code
222                    FROM tbl_users a
223                    LEFT JOIN tbl_roles b
224                        ON a.role_id = b.role_id
225                    WHERE a.id = {$id}";
226
227            $result = DB::select($query);
228
229            return response(['message' => 'OK', 'data' => $result]);
230
231        } catch (\Exception $e) {
232            report(AppException::fromException($e, 'GET_USER_EXCEPTION'));
233            return response(['message' => 'KO', 'error' => $e->getMessage()]);
234        }
235    }
236
237    public function getUserByName($name)
238    {
239        try {
240            $formattedName = str_replace('_', ' ', $name);
241            $user = TblUsers::where('name', $formattedName)->first();
242
243            if (! $user) {
244                return response()->json([
245                    'success' => false,
246                    'message' => 'User not found',
247                ], 404);
248            }
249
250            return response()->json([
251                'success' => true,
252                'data' => $user,
253            ], 200);
254        } catch (\Exception $e) {
255            report(AppException::fromException($e, 'GET_USER_BY_NAME_EXCEPTION'));
256            return response()->json([
257                'success' => false,
258                'message' => 'An error occurred',
259                'error' => $e->getMessage(),
260            ], 500);
261        }
262    }
263
264    public function getAllUserNames()
265    {
266        try {
267            $users = TblUsers::pluck('name');
268
269            return response()->json([
270                'success' => true,
271                'data' => $users,
272            ]);
273        } catch (\Exception $e) {
274            report(AppException::fromException($e, 'GET_ALL_USER_NAMES_EXCEPTION'));
275            return response()->json([
276                'success' => false,
277                'message' => 'Error fetching users',
278                'error' => $e->getMessage(),
279            ], 500);
280        }
281    }
282
283
284    public function get_user_by_email(Request $request): ResponseFactory|Response{
285
286        try {
287
288            // Cloudflare Turnstile verification (only when token is provided, i.e. from login page)
289            $turnstileToken = $request->input('cf_turnstile_response');
290            if ($turnstileToken) {
291                $turnstileResponse = Http::asForm()->post('https://challenges.cloudflare.com/turnstile/v0/siteverify', [
292                    'secret' => config('services.cloudflare.turnstile_secret'),
293                    'response' => $turnstileToken,
294                    'remoteip' => $request->ip(),
295                ]);
296
297                if (! $turnstileResponse->json('success')) {
298                    return response(['message' => 'KO', 'error' => 'Turnstile verification failed'], 403);
299                }
300            }
301
302            $data = $request->all();
303            $bindings = ['email' => $data['email']];
304            $where = '';
305
306            if (isset($data['company_id'])) {
307                $where = ' AND c.company_id = :company_id ';
308                $bindings['company_id'] = $data['company_id'];
309            }
310
311            $query = "SELECT
312                        a.id,
313                        a.name,
314                        a.email,
315                        a.created_at,
316                        a.created_by,
317                        a.updated_at,
318                        a.updated_by,
319                        c.filename,
320                        a.default,
321                        a.role_id,
322                        d.name role,
323                        b.company_id,
324                        c.logo,
325                        c.name company_name,
326                        c.default_page,
327                        b.can_read,
328                        b.can_write,
329                        a.is_loggedin,
330                        c.is_send,
331                        c.limit_send,
332                        c.is_send_follow_up,
333                        c.is_send_request,
334                        c.is_send_g3w,
335                        c.revenue_per_employee_per_day,
336                        c.minimum_margin,
337                        c.general_costs,
338                        c.hours_per_worker_per_day,
339                        c.cost_of_hour,
340                        c.hours_per_worker_per_day_percentage,
341                        c.convert_to_job_amount_limit,
342                        c.last_follow_up_date,
343                        c.limit_reminder_emails,
344                        c.workflow_budget_size,
345                        c.region,
346                        COALESCE(f.approver_id, e.approver_id) AS approver_id,
347                        a.is_itv,
348                        a.G3W_code,
349                        a.api_token
350                    FROM tbl_users a
351                    LEFT JOIN tbl_company_users b
352                        ON a.id = b.user_id AND (b.is_selected = 1 OR b.can_read = 1)
353                    LEFT JOIN tbl_companies c
354                        ON b.company_id = c.company_id
355                    LEFT JOIN tbl_roles d
356                        ON a.role_id = d.role_id
357                    LEFT JOIN tbl_approvers e
358                        ON a.id = e.user_id
359                    LEFT JOIN tbl_approvers_v2 f
360                        ON a.id = f.user_id
361                    WHERE a.email = :email
362                    {$where}
363                    ORDER BY b.is_selected DESC
364                    LIMIT 1";
365
366            $result = DB::select($query, $bindings);
367
368            if (isset($data['switch']) && $data['switch'] == 1) {
369                $result[0]->is_loggedin = 1;
370            }
371
372            return response(['message' => 'OK', 'data' => $result]);
373
374        } catch (\Exception $e) {
375            report(AppException::fromException($e, 'GET_USER_BY_EMAIL_EXCEPTION'));
376            return response(['message' => 'KO', 'error' => $e->getMessage()]);
377        }
378    }
379
380    function add_company_user(Request $request): ResponseFactory|Response{
381
382        try {
383
384            $data = $request->all();
385            $id = addslashes((string) $data['user_id']);
386
387            if (isset($data['company_ids'])) {
388
389                $ids = TblCompanyUsers::where('user_id', $id)->pluck('company_id')->toArray();
390                $companyUsers = [];
391
392                for ($i = 0; $i < count($data['company_ids']); $i++) {
393                    if (! in_array($data['company_ids'][$i], $ids)) {
394                        array_push(
395                            $companyUsers,
396                            [
397                                'user_id' => $id,
398                                'company_id' => $data['company_ids'][$i],
399                                'can_read' => 1,
400                                'can_write' => 0,
401                                'created_by' => $data['created_by']
402                            ]
403                        );
404                    }
405                }
406
407                TblCompanyUsers::insert($companyUsers);
408            }
409
410            return response(['message' => 'OK']);
411
412        } catch (\Exception $e) {
413            report(AppException::fromException($e, 'ADD_COMPANY_USER_EXCEPTION'));
414            return response(['message' => 'KO', 'error' => $e->getMessage()]);
415        }
416
417    }
418
419    function delete_company_user(Request $request): ResponseFactory|Response{
420
421        try {
422
423            $data = $request->all();
424            $id = addslashes((string) $data['user_id']);
425            $companyId = addslashes((string) $data['company_id']);
426
427            TblCompanyUsers::where('company_id', $companyId)->where('user_id', $id)->delete();
428
429            return response(['message' => 'OK']);
430
431        } catch (\Exception $e) {
432            report(AppException::fromException($e, 'DELETE_COMPANY_USER_EXCEPTION'));
433            return response(['message' => 'KO', 'error' => $e->getMessage()]);
434        }
435
436    }
437
438    public function update_users(Request $request, $id): ResponseFactory|Response{
439
440        try {
441
442            $data = $request->all();
443            $id = addslashes((string) $id);
444
445            if (! isset($data['is_loggedin'])) {
446                $data['is_loggedin'] = 0;
447            } else {
448
449                if ($data['is_loggedin'] == 1) {
450                    $token = Str::random(60);
451                    $data['api_token'] = $token;
452                    $data['token_expires_at'] = Carbon::now()->addHours(72);
453                } else {
454                    $data['api_token'] = null;
455                    $data['token_expires_at'] = null;
456                }
457
458                $result = TblUsers::where('id', $id)->update($data);
459
460                return response(['message' => 'OK', 'data' => $data]);
461            }
462
463            $sData = [
464                'name' => $data['name'],
465                'email' => $data['email'],
466                'role_id' => $data['role_id'],
467                'updated_by' => $data['updated_by'],
468                'sender_email' => $data['sender_email'],
469                'G3W_code' => $data['G3W_code'],
470                'sender_enabled' => @$data['sender_enabled'],
471                'is_commercial' => $data['is_commercial'],
472            ];
473
474            if (isset($data['email'])) {
475                $email = TblUsers::where('email', $data['email'])->first();
476
477                if ($email != null && $id != $email->id) {
478                    return response(['message' => 'KO', 'error' => __('language.email_already_exist')]);
479                }
480            }
481
482            $sData['updated_at'] = date('Y-m-d H:i:s');
483
484            $u = TblUsers::where('id', $id)->first();
485
486            if (isset($data['name'])) {
487                TblQuotations::where('commercial', $u->name)->update(
488                    [
489                        'commercial' => $data['name'],
490                    ]
491                );
492
493                TblQuotations::where('created_by', $u->name)->update(
494                    [
495                        'created_by' => $data['name'],
496                    ]
497                );
498
499                TblQuotations::where('updated_by', $u->name)->update(
500                    [
501                        'updated_by' => $data['name'],
502                    ]
503                );
504
505                TblOngoingJobs::where('responsible_for_work', $u->name)->update(
506                    [
507                        'responsible_for_work' => $data['name'],
508                    ]
509                );
510
511                TblOngoingJobs::where('created_by', $u->name)->update(
512                    [
513                        'created_by' => $data['name'],
514                    ]
515                );
516
517                TblOngoingJobs::where('updated_by', $u->name)->update(
518                    [
519                        'updated_by' => $data['name'],
520                    ]
521                );
522            }
523
524            $sendgrid = new \SendGrid(config('services.sendgrid.api_key'));
525            $data['address'] = "Madrid";
526            $data['city'] = "Madrid";
527            $data['country'] = "Spain";
528            $data['nickname'] = $data['name'] . "-" . base64_encode($data['name'] . date('ymdhis'));;
529            $data['from_name'] = $data['name'];
530            $data['from_email'] = $data['sender_email'];
531            $data['reply_to'] = $data['sender_email'];
532            $data['reply_to_name'] = $data['name'];
533            $requestBody = $data;
534            $error  = false;
535            $response = [];
536
537            if ($u->response_id && $u->sender_email == $data['sender_email']) {
538                $response = $sendgrid->client->verified_senders()->_($u->response_id)->patch($requestBody);
539            } else {
540                $requestBody['nickname'] = $data['name'].'-'.base64_encode($data['name'].date('ymdhis'));
541                $response = $sendgrid->client->verified_senders()->post($requestBody);
542            }
543
544            $x = json_decode((string) $response->body());
545
546            if ($response->statusCode() == 200 || is_numeric(@$x->id)) {
547                $sData['response_id'] = $x->id;
548                $result = TblUsers::where('id', $id)->update($sData);
549                Log::channel('email_log')->info('USER EMAIL: '.$data['sender_email'].' - VERIFICATION SENT');
550            } else {
551                $error = true;
552                Log::channel('email_log')->error('REQUEST BODY USER: - '.$response->body());
553            }
554
555            $response = json_decode((string) $response->body());
556
557            if ($error) {
558                if ($response->errors[0]->message == 'already exists' && $response->errors[0]->field == 'from_email') {
559                    TblUsers::where('id', $id)->update($sData);
560
561                    return response(['message' => 'OK', 'data' => $data, 'is_verified' => 'yes']);
562                }
563
564                $errMessage = @$response->errors[0]->field.': '.@$response->errors[0]->message;
565
566                return response(['message' => 'KO', 'error' => $errMessage]);
567            } else {
568                $isVerified = 'no';
569
570                if ($response->verified) {
571                    $isVerified = 'yes';
572                }
573
574                return response(['message' => 'OK', 'data' => $response, 'is_verified' => $isVerified]);
575            }
576
577        } catch (\Exception $e) {
578            report(AppException::fromException($e, 'UPDATE_USER_EXCEPTION'));
579            return response(['message' => 'KO', 'error' => $e->getMessage()]);
580        }
581    }
582
583    public function delete_users($id): ResponseFactory|Response{
584
585        try {
586
587            $id = addslashes((string) $id);
588
589            $user = TblUsers::where('id', $id)->first();
590
591            $count = TblQuotations::where('commercial', $user->name)->where('company_id', '>', 0)->count();
592            $countQ = TblOngoingJobs::where('responsible_for_work', $user->name)->where('company_id', '>', 0)->count();
593
594            if($count > 0 || $countQ > 0){
595                $urlOrder = config('app.frontend_url') . "orders?commercial={$user->name}&company_id=0";
596                $urlJob = config('app.frontend_url') . "ongoing-jobs?responsible_for_work={$user->name}&company_id=0";
597                $urlOrder = "<a href='{$urlOrder}' target='_blank'>{$count}</a>";
598                $urlJob = "<a href='{$urlJob}' target='_blank'>{$countQ}</a>";
599
600                return response(['message' => 'user_cannot_be_deleted', 'error' => __('language.user_cannot_be_deleted'), 'total_job' => $urlJob, 'total_order' => $urlOrder]);
601            }
602
603            $count = TblQuotations::where('created_by', $user->name)->count();
604
605            if ($count > 0) {
606                $query = "UPDATE tbl_quotations SET created_by = 'Fire Service Titan' WHERE created_by = '{$user->name}'";
607                DB::select($query);
608            }
609
610            $count = TblOngoingJobs::where('created_by', $user->name)->count();
611
612            if ($count > 0) {
613                $query = "UPDATE tbl_ongoing_jobs SET created_by = 'Fire Service Titan' WHERE created_by = '{$user->name}'";
614                DB::select($query);
615            }
616
617            $result = TblUsers::where('id', $id)->delete();
618            TblCompanyUsers::where('user_id', $id)->delete();
619
620            return response(['message' => 'OK', 'data' => $result]);
621
622        } catch (\Exception $e) {
623            report(AppException::fromException($e, 'DELETE_USER_EXCEPTION'));
624            return response(['message' => 'KO', 'error' => $e->getMessage()]);
625        }
626    }
627
628    function get_roles(): ResponseFactory|Response{
629
630        try {
631
632            $result = TblRoles::get();
633
634            return response(['message' => 'OK', 'data' => $result]);
635
636        } catch (\Exception $e) {
637            report(AppException::fromException($e, 'GET_ROLES_EXCEPTION'));
638            return response(['message' => 'KO', 'error' => $e->getMessage()]);
639        }
640    }
641
642    function get_all_commercials(Request $request): ResponseFactory|Response{
643
644        try {
645
646            $data = $request->all();
647
648            $companyIds = implode(',', $data['company_ids']);
649
650            $query = "SELECT
651                    b.name
652                FROM
653                    tbl_company_users a
654                    LEFT JOIN tbl_users b ON a.user_id = b.id
655                WHERE
656                    a.company_id IN ({$companyIds})
657                GROUP BY
658                    a.user_id
659                HAVING
660                    COUNT(DISTINCT a.company_id) = (
661                    SELECT
662                        COUNT(DISTINCT company_id)
663                    FROM
664                        tbl_company_users
665                    WHERE
666                        company_id IN ({$companyIds})
667                    )";
668
669            $result = DB::select($query);
670
671            return response(['message' => 'OK', 'data' => $result]);
672
673        } catch (\Exception $e) {
674            report(AppException::fromException($e, 'GET_ALL_COMMERCIALS_EXCEPTION'));
675            return response(['message' => 'KO', 'error' => $e->getMessage()]);
676        }
677
678    }
679
680    function get_g3w_warning_fields($companyId): ResponseFactory|Response{
681
682        try {
683
684            $companyId = addslashes((string) $companyId);
685
686            $where = '';
687
688            if ($companyId != 0) {
689                $where = "WHERE company_id = {$companyId} ";
690            } else {
691                $where = "WHERE company_id IN ({$this->companyId}) ";
692            }
693
694            $query = "SELECT
695                        DISTINCT g3w_warning_fields
696                    FROM
697                        tbl_quotations
698                    {$where}
699                    ORDER BY
700                        g3w_warning_fields ASC";
701
702            $value = Cache::get(base64_encode($query));
703
704            if (! $value) {
705                $result = DB::select($query);
706
707                $g3wWarningFields = array_map(fn($row) => $row->g3w_warning_fields, $result);
708
709                Cache::put(base64_encode($query), $g3wWarningFields, 600);
710            } else {
711                $g3wWarningFields = $value;
712            }
713
714            return response([
715                'message' => 'OK',
716                'g3wWarningFields' => $g3wWarningFields,
717            ]);
718
719        } catch (\Exception $e) {
720            report(AppException::fromException($e, 'GET_G3W_WARNING_FIELDS_EXCEPTION'));
721            return response(['message' => 'KO', 'error' => $e->getMessage()]);
722        }
723    }
724
725    function get_created_by($companyId): ResponseFactory|Response{
726
727        try {
728
729            $companyId = addslashes((string) $companyId);
730
731            $where = '';
732
733            if ($companyId != 0) {
734                $where = "WHERE company_id = {$companyId} ";
735            } else {
736                $where = "WHERE company_id IN ({$this->companyId}) ";
737            }
738
739            $query = "SELECT
740                        DISTINCT created_by
741                    FROM
742                        tbl_quotations
743                    {$where}
744                    ORDER BY
745                        created_by ASC";
746
747            $value = Cache::get(base64_encode($query));
748
749            if (! $value) {
750                $result = DB::select($query);
751
752                $createdBy = array_map(fn($row) => $row->created_by, $result);
753
754                Cache::put(base64_encode($query), $createdBy, 600);
755            } else {
756                $createdBy = $value;
757            }
758
759            return response([
760                'message' => 'OK',
761                'createdBy' => $createdBy,
762            ]);
763
764        } catch (\Exception $e) {
765            report(AppException::fromException($e, 'GET_CREATED_BY_EXCEPTION'));
766            return response(['message' => 'KO', 'error' => $e->getMessage()]);
767        }
768    }
769
770    function get_commercial_with_pendings($companyId): ResponseFactory|Response{
771
772        try {
773
774            $companyId = addslashes((string) $companyId);
775
776            $where = '';
777
778            if ($companyId != 0) {
779                $where = "WHERE company_id = {$companyId} ";
780            } else {
781                $where = "WHERE company_id IN ({$this->companyId}) ";
782            }
783
784            $query = "SELECT
785                        b.id userId,
786                        b.name commercial,
787                        a.total_error totalError,
788                        a.total_pending_follow_ups totalPendingFollowUps,
789                        a.total_request_and_visits totalRequestAndVisit,
790                        a.total_g3w_error totalG3WError
791                    FROM
792                        tbl_company_users a
793                    LEFT JOIN tbl_users b
794                        ON a.user_id = b.id
795                    {$where}
796                    ORDER BY
797                        b.name ASC";
798
799            $value = Cache::get(base64_encode($query));
800
801            if (! $value) {
802                $commercial = DB::select($query);
803
804                Cache::put(base64_encode($query), $commercial, 600);
805            } else {
806                $commercial = $value;
807            }
808
809            return response([
810                'message' => 'OK',
811                'commercialWithPendings' => $commercial,
812            ]);
813
814        } catch (\Exception $e) {
815            report(AppException::fromException($e, 'GET_COMMERCIAL_WITH_PENDINGS_EXCEPTION'));
816            return response(['message' => 'KO', 'error' => $e->getMessage()]);
817        }
818    }
819
820    function get_responsible_for_work($companyId): ResponseFactory|Response{
821
822        try {
823
824            $companyId = addslashes((string) $companyId);
825
826            $where = '';
827
828            if ($companyId != 0) {
829                $where = "WHERE company_id = {$companyId} ";
830            } else {
831                $where = "WHERE company_id IN ({$this->companyId}) ";
832            }
833
834            $query = "SELECT
835                        DISTINCT responsible_for_work
836                    FROM
837                        tbl_ongoing_jobs
838                    {$where}
839                    ORDER BY
840                    responsible_for_work ASC";
841
842            $value = Cache::get(base64_encode($query));
843
844            if (! $value) {
845                $result = DB::select($query);
846
847                $responsibleForWork = array_map(fn($row) => $row->responsible_for_work, $result);
848
849                Cache::put(base64_encode($query), $responsibleForWork, 600);
850            } else {
851                $responsibleForWork = $value;
852            }
853
854            return response([
855                'message' => 'OK',
856                'responsibleForWork' => $responsibleForWork,
857            ]);
858
859        } catch (\Exception $e) {
860            report(AppException::fromException($e, 'GET_RESPONSIBLE_FOR_WORK_EXCEPTION'));
861            return response(['message' => 'KO', 'error' => $e->getMessage()]);
862        }
863
864    }
865
866    function get_job_created_by($companyId): ResponseFactory|Response{
867
868        try {
869
870            $companyId = addslashes((string) $companyId);
871
872            $where = '';
873
874            if ($companyId != 0) {
875                $where = "WHERE company_id = {$companyId} ";
876            } else {
877                $where = "WHERE company_id IN ({$this->companyId}) ";
878            }
879
880            $query = "SELECT
881                        DISTINCT created_by
882                    FROM
883                        tbl_ongoing_jobs
884                    {$where}
885                    ORDER BY
886                    created_by ASC";
887
888            $value = Cache::get(base64_encode($query));
889
890            if (! $value) {
891                $result = DB::select($query);
892
893                $jobCreatedBy = array_map(fn($row) => $row->created_by, $result);
894
895                Cache::put(base64_encode($query), $jobCreatedBy, 600);
896            } else {
897                $jobCreatedBy = $value;
898            }
899
900            return response([
901                'message' => 'OK',
902                'jobCreatedBy' => $jobCreatedBy,
903            ]);
904
905        } catch (\Exception $e) {
906            report(AppException::fromException($e, 'GET_JOB_CREATED_BY_EXCEPTION'));
907            return response(['message' => 'KO', 'error' => $e->getMessage()]);
908        }
909    }
910
911    function get_accepted_by($companyId): ResponseFactory|Response{
912
913        try {
914
915            $companyId = addslashes((string) $companyId);
916
917            $where = '';
918
919            if ($companyId != 0) {
920                $where = "WHERE company_id = {$companyId} ";
921            } else {
922                $where = "WHERE company_id IN ({$this->companyId}) ";
923            }
924
925            $query = "SELECT
926                        DISTINCT accepted_by
927                    FROM
928                        tbl_quotations
929                    {$where}
930                    ORDER BY
931                        accepted_by ASC";
932
933            $value = Cache::get(base64_encode($query));
934
935            if (! $value) {
936                $result = DB::select($query);
937
938                $acceptedBy = array_map(fn($row) => $row->accepted_by, $result);
939
940                Cache::put(base64_encode($query), $acceptedBy, 600);
941            } else {
942                $acceptedBy = $value;
943            }
944
945            return response([
946                'message' => 'OK',
947                'acceptedBy' => $acceptedBy,
948            ]);
949
950        } catch (\Exception $e) {
951            report(AppException::fromException($e, 'GET_ACCEPTED_BY_EXCEPTION'));
952            return response(['message' => 'KO', 'error' => $e->getMessage()]);
953        }
954    }
955
956
957    function get_commercials($companyId): ResponseFactory|Response{
958
959        try {
960
961            $companyId = addslashes((string) $companyId);
962
963            $where = '';
964
965            if ($companyId != 0) {
966                $where = "WHERE company_id = {$companyId} ";
967            } else {
968                $where = "WHERE company_id IN ({$this->companyId}) ";
969            }
970
971            $query = "SELECT
972                        DISTINCT commercial
973                    FROM
974                        tbl_quotations
975                    {$where}
976                    ORDER BY
977                        commercial ASC";
978
979            $value = Cache::get(base64_encode($query));
980
981            if (! $value) {
982                $result = DB::select($query);
983
984                $commercials = array_map(fn($row) => $row->commercial, $result);
985
986                Cache::put(base64_encode($query), $commercials, 600);
987            } else {
988                $commercials = $value;
989            }
990
991            return response([
992                'message' => 'OK',
993                'commercials' => $commercials,
994            ]);
995
996        } catch (\Exception $e) {
997            report(AppException::fromException($e, 'GET_COMMERCIALS_EXCEPTION'));
998            return response(['message' => 'KO', 'error' => $e->getMessage()]);
999        }
1000    }
1001
1002    function update_users_itv(Request $request): ResponseFactory|Response{
1003
1004        try {
1005
1006            $data = $request->all();
1007
1008            if (isset($data['user_ids'])) {
1009                TblUsers::query()->update(['is_itv' => null]);
1010                for ($i = 0; $i < count($data['user_ids']); $i++) {
1011                    TblUsers::where('id', $data['user_ids'][$i])->update(['is_itv' => 1]);
1012                }
1013            }
1014
1015            return response(['message' => 'OK']);
1016
1017        } catch (\Exception $e) {
1018            report(AppException::fromException($e, 'UPDATE_USERS_ITV_EXCEPTION'));
1019            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1020        }
1021
1022    }
1023
1024    function list_roles(): ResponseFactory|Response{
1025
1026        try {
1027
1028            $result = TblRoles::get();
1029
1030            return response(['message' => 'OK', 'data' => $result]);
1031
1032        } catch (\Exception $e) {
1033            report(AppException::fromException($e, 'LIST_ROLES_EXCEPTION'));
1034            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1035        }
1036
1037    }
1038
1039    function update_role(Request $request): ResponseFactory|Response{
1040
1041        try {
1042
1043            $data = $request->all();
1044
1045            foreach ($data as $item) {
1046                $id = $item['role_id'];
1047                unset($item['role_id']);
1048
1049                $item['updated_at'] = date('Y-m-d H:i:s');
1050                TblRoles::where('role_id', $id)->update($item);
1051            }
1052
1053            $result = TblRoles::get();
1054
1055            return response(['message' => 'OK', 'data' => $result]);
1056
1057        } catch (\Exception $e) {
1058            report(AppException::fromException($e, 'UPDATE_ROLE_EXCEPTION'));
1059            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1060        }
1061    }
1062
1063    function delete_role($id): ResponseFactory|Response{
1064
1065        try {
1066
1067            $id = addslashes((string) $id);
1068
1069            TblRoles::where('role_id', $id)->delete();
1070
1071            $result = TblRoles::get();
1072
1073            return response(['message' => 'OK', 'data' => $result]);
1074
1075        } catch (\Exception $e) {
1076            report(AppException::fromException($e, 'DELETE_ROLE_EXCEPTION'));
1077            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1078        }
1079    }
1080}