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