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