Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
0.00%
0 / 973
0.00%
0 / 45
 CRAP
0.00%
0 / 1
Companies
0.00%
0 / 973
0.00%
0 / 45
 37056
0.00%
0 / 1
 __construct
0.00%
0 / 13
0.00%
0 / 1
 20
 create_company
0.00%
0 / 328
0.00%
0 / 1
 1980
 list_companies
0.00%
0 / 7
0.00%
0 / 1
 6
 get_company
0.00%
0 / 11
0.00%
0 / 1
 12
 get_company_users
0.00%
0 / 13
0.00%
0 / 1
 12
 get_user_companies
0.00%
0 / 42
0.00%
0 / 1
 42
 update_company_users
0.00%
0 / 11
0.00%
0 / 1
 6
 update_company_selected
0.00%
0 / 11
0.00%
0 / 1
 6
 delete_company_users
0.00%
0 / 17
0.00%
0 / 1
 20
 update_company
0.00%
0 / 14
0.00%
0 / 1
 30
 update_company_blocked
0.00%
0 / 7
0.00%
0 / 1
 6
 upload_file
0.00%
0 / 26
0.00%
0 / 1
 30
 delete_company
0.00%
0 / 14
0.00%
0 / 1
 12
 update_company_users_selected
0.00%
0 / 10
0.00%
0 / 1
 12
 create_company_users
0.00%
0 / 32
0.00%
0 / 1
 42
 get_cc_bcc
0.00%
0 / 6
0.00%
0 / 1
 6
 delete_cc_bcc
0.00%
0 / 6
0.00%
0 / 1
 6
 update_cc_bcc
0.00%
0 / 14
0.00%
0 / 1
 20
 create_cc_bcc
0.00%
0 / 12
0.00%
0 / 1
 20
 isEmailValid
0.00%
0 / 4
0.00%
0 / 1
 6
 get_blocked_domains
0.00%
0 / 9
0.00%
0 / 1
 12
 delete_blocked_domain
0.00%
0 / 7
0.00%
0 / 1
 6
 update_blocked_domain
0.00%
0 / 12
0.00%
0 / 1
 12
 create_blocked_domain
0.00%
0 / 10
0.00%
0 / 1
 12
 list_project_types
0.00%
0 / 20
0.00%
0 / 1
 30
 get_project_types
0.00%
0 / 22
0.00%
0 / 1
 12
 update_project_types
0.00%
0 / 45
0.00%
0 / 1
 30
 update_project_types_benchmarks
0.00%
0 / 12
0.00%
0 / 1
 12
 delete_project_types
0.00%
0 / 13
0.00%
0 / 1
 12
 list_workflow_project_types
0.00%
0 / 6
0.00%
0 / 1
 6
 update_workflow_project_types
0.00%
0 / 14
0.00%
0 / 1
 12
 delete_workflow_project_types
0.00%
0 / 8
0.00%
0 / 1
 6
 list_workflow_customer_types
0.00%
0 / 6
0.00%
0 / 1
 6
 update_workflow_customer_types
0.00%
0 / 14
0.00%
0 / 1
 12
 delete_workflow_customer_types
0.00%
0 / 8
0.00%
0 / 1
 6
 list_workflow_questions
0.00%
0 / 6
0.00%
0 / 1
 6
 update_workflow_questions
0.00%
0 / 10
0.00%
0 / 1
 6
 get_company_executive
0.00%
0 / 5
0.00%
0 / 1
 6
 get_all_details
0.00%
0 / 24
0.00%
0 / 1
 6
 list_all_companies
0.00%
0 / 8
0.00%
0 / 1
 6
 update_company_permission
0.00%
0 / 26
0.00%
0 / 1
 20
 list_facility_settings
0.00%
0 / 11
0.00%
0 / 1
 20
 update_facility_settings
0.00%
0 / 33
0.00%
0 / 1
 90
 list_trade_alignment_settings
0.00%
0 / 12
0.00%
0 / 1
 20
 update_trade_alignment_settings
0.00%
0 / 34
0.00%
0 / 1
 90
1<?php
2
3namespace App\Http\Controllers;
4
5use App\Exceptions\AppException;
6use App\Models\TblApprovers;
7use App\Models\TblApproversV2;
8use App\Models\TblBlockedDomains;
9use App\Models\TblCcAcceptanceNotifications;
10use App\Models\TblCcBcc;
11use App\Models\TblCcBccNotifications;
12use App\Models\TblCcBccRequestNotifications;
13use App\Models\TblCcG3WEmailReminders;
14use App\Models\TblCompanies;
15use App\Models\TblCompanyEmails;
16use App\Models\TblCompanyUsers;
17use App\Models\TblEmailConfiguration;
18use App\Models\TblFacilitySettings;
19use App\Models\TblFinalSummary;
20use App\Models\TblLastFollowUpDate;
21use App\Models\TblOngoingJobs;
22use App\Models\TblProjectTypes;
23use App\Models\TblQuotations;
24use App\Models\TblToAcceptanceNotifications;
25use App\Models\TblTradeAlignmentSettings;
26use App\Models\TblUsers;
27use App\Models\TblWorkflowApprover;
28use App\Models\TblWorkflowCustomerTypes;
29use App\Models\TblWorkflowProjectTypes;
30use App\Models\TblWorkflowQuestions;
31use App\Services\ResultCache;
32use App\Services\UserCompanies;
33use Illuminate\Contracts\Routing\ResponseFactory;
34use Illuminate\Http\Request;
35use Illuminate\Http\Response;
36use Illuminate\Support\Facades\App;
37use Illuminate\Support\Facades\Cache;
38use Illuminate\Support\Facades\DB;
39use Illuminate\Support\Facades\Storage;
40
41class Companies extends Controller
42{
43    private $locale;
44
45    private $userId;
46
47    private $region;
48
49    private $companyIds;
50
51    private $companyId;
52
53    public function __construct()
54    {
55        $this->locale = request()->header('Locale-Id');
56        $this->userId = request()->header('User-Id');
57        $this->region = request()->header('Region');
58
59        App::setLocale($this->locale);
60
61        $this->companyIds = [];
62
63        if ($this->region != null && $this->region != '' && $this->region != 'All') {
64            $this->region = urldecode((string) $this->region);
65
66            $query = 'SELECT 
67                        b.company_id
68                    FROM 
69                        tbl_company_users a 
70                        LEFT JOIN tbl_companies b ON a.company_id = b.company_id 
71                    WHERE 
72                        a.user_id = ?
73                        AND b.region = ?';
74
75            $this->companyIds = DB::select($query, [intval($this->userId), $this->region]);
76
77            $this->companyIds = collect($this->companyIds)->pluck('company_id')->toArray();
78        } else {
79            // FIRE-1146: prefer the middleware-resolved attribute; fall back to a fresh fetch only on unattached paths.
80            $this->companyIds = request()->attributes->get('user_company_ids', UserCompanies::forUser((int) $this->userId));
81        }
82
83        $this->companyId = implode(',', $this->companyIds);
84    }
85
86    public function create_company(Request $request): ResponseFactory|Response
87    {
88
89        try {
90
91            $data = $request->all();
92            $data['updated_at'] = date('Y-m-d H:i:s');
93            $logo = Storage::get('logo-light.png');
94            $base64 = base64_encode((string) $logo);
95            $mimetype = 'image/png';
96            $blob = 'data:'.$mimetype.';base64,'.$base64;
97            $data['logo'] = $blob;
98            $data['filename'] = 'logo-light.png';
99            $data['filesize'] = 6233;
100            $data['filetype'] = $mimetype;
101            $data['default_page'] = 'orders';
102            $userId = $data['user_id'];
103            unset($data['user_id']);
104
105            $result = [];
106            $newCompanyId = 0;
107
108            DB::beginTransaction();
109
110            if (isset($data['template_company_id'])) {
111                $companyId = intval($data['template_company_id']);
112
113                $company = TblCompanies::where('company_id', $companyId)->first();
114
115                $data['is_send'] = $company->is_send;
116                $data['limit_send'] = $company->limit_send;
117                $data['is_send_follow_up'] = $company->is_send_follow_up;
118                $data['is_send_request'] = $company->is_send_request;
119                $data['is_send_g3w'] = $company->is_send_g3w;
120                $data['revenue_per_employee_per_day'] = $company->revenue_per_employee_per_day;
121                $data['minimum_margin'] = $company->minimum_margin;
122                $data['general_costs'] = $company->general_costs;
123                $data['hours_per_worker_per_day'] = $company->hours_per_worker_per_day;
124                $data['cost_of_hour'] = $company->cost_of_hour;
125                $data['hours_per_worker_per_day_percentage'] = $company->hours_per_worker_per_day_percentage;
126                $data['convert_to_job_amount_limit'] = $company->convert_to_job_amount_limit;
127                $data['minimum_order_size'] = $company->minimum_order_size;
128                $data['minimum_order_size_v2'] = $company->minimum_order_size_v2;
129                $data['last_follow_up_date'] = $company->last_follow_up_date;
130                $data['limit_reminder_emails'] = $company->limit_reminder_emails;
131                $data['workflow_budget_size'] = $company->workflow_budget_size;
132                $data['is_send_executive'] = $company->is_send_executive;
133                $data['customer_type_ids'] = $company->customer_type_ids;
134                $data['goal_for_the_year'] = $company->goal_for_the_year;
135                $data['logo'] = $company->logo;
136                $data['filetype'] = $company->filetype;
137                $data['filename'] = $company->filename;
138                $data['filesize'] = $company->filesize;
139
140                $result = TblCompanies::create($data);
141                $newCompanyId = $result->company_id;
142
143                $approvers = TblApprovers::where('company_id', $companyId)->get();
144
145                if (count($approvers) > 0) {
146                    $newApprovers = [];
147                    foreach ($approvers as $item) {
148                        $newApprovers[] = [
149                            'company_id' => $newCompanyId,
150                            'user_id' => $item->user_id,
151                            'created_by' => $data['created_by'],
152                        ];
153                    }
154
155                    TblApprovers::insert($newApprovers);
156                }
157
158                $approversV2 = TblApproversV2::where('company_id', $companyId)->get();
159
160                if (count($approversV2) > 0) {
161                    $newApproversV2 = [];
162                    foreach ($approversV2 as $item) {
163                        $newApproversV2[] = [
164                            'company_id' => $newCompanyId,
165                            'user_id' => $item->user_id,
166                            'created_by' => $data['created_by'],
167                        ];
168                    }
169
170                    TblApproversV2::insert($newApproversV2);
171                }
172
173                $blockedDomains = TblBlockedDomains::where('company_id', $companyId)->get();
174
175                if (count($blockedDomains) > 0) {
176                    $newBlockedDomains = [];
177                    foreach ($blockedDomains as $item) {
178                        $newBlockedDomains[] = [
179                            'company_id' => $newCompanyId,
180                            'domain' => $item->domain,
181                            'created_by' => $data['created_by'],
182                        ];
183                    }
184
185                    TblBlockedDomains::insert($newBlockedDomains);
186                }
187
188                $ccAcceptanceNotifications = TblCcAcceptanceNotifications::where('company_id', $companyId)->get();
189
190                if (count($ccAcceptanceNotifications) > 0) {
191                    $newCcAcceptanceNotifications = [];
192                    foreach ($ccAcceptanceNotifications as $item) {
193                        $newCcAcceptanceNotifications[] = [
194                            'company_id' => $newCompanyId,
195                            'email' => $item->email,
196                            'created_by' => $data['created_by'],
197                        ];
198                    }
199
200                    TblCcAcceptanceNotifications::insert($newCcAcceptanceNotifications);
201                }
202
203                $ccBcc = TblCcBcc::where('company_id', $companyId)->get();
204
205                if (count($ccBcc) > 0) {
206                    $newCcBcc = [];
207                    foreach ($ccBcc as $item) {
208                        $newCcBcc[] = [
209                            'company_id' => $newCompanyId,
210                            'email' => $item->email,
211                            'created_by' => $data['created_by'],
212                        ];
213                    }
214
215                    TblCcBcc::insert($newCcBcc);
216                }
217
218                $ccBccNotifications = TblCcBccNotifications::where('company_id', $companyId)->get();
219
220                if (count($ccBccNotifications) > 0) {
221                    $newCcBccNotifications = [];
222                    foreach ($ccBccNotifications as $item) {
223                        $newCcBccNotifications[] = [
224                            'company_id' => $newCompanyId,
225                            'email' => $item->email,
226                            'created_by' => $data['created_by'],
227                        ];
228                    }
229
230                    TblCcBccNotifications::insert($newCcBccNotifications);
231                }
232
233                $ccBccRequestNotifications = TblCcBccRequestNotifications::where('company_id', $companyId)->get();
234
235                if (count($ccBccRequestNotifications) > 0) {
236                    $newCcBccRequestNotifications = [];
237                    foreach ($ccBccRequestNotifications as $item) {
238                        $newCcBccRequestNotifications[] = [
239                            'company_id' => $newCompanyId,
240                            'email' => $item->email,
241                            'created_by' => $data['created_by'],
242                        ];
243                    }
244
245                    TblCcBccRequestNotifications::insert($newCcBccRequestNotifications);
246                }
247
248                $ccG3WEmailReminders = TblCcG3WEmailReminders::where('company_id', $companyId)->get();
249
250                if (count($ccG3WEmailReminders) > 0) {
251                    $newCcG3WEmailReminders = [];
252                    foreach ($ccG3WEmailReminders as $item) {
253                        $newCcG3WEmailReminders[] = [
254                            'company_id' => $newCompanyId,
255                            'email' => $item->email,
256                            'created_by' => $data['created_by'],
257                        ];
258                    }
259
260                    TblCcG3WEmailReminders::insert($newCcG3WEmailReminders);
261                }
262
263                $companyEmails = TblCompanyEmails::where('company_id', $companyId)->get();
264
265                if (count($companyEmails) > 0) {
266                    $newCompanyEmails = [];
267                    foreach ($companyEmails as $item) {
268                        $newCompanyEmails[] = [
269                            'response_id' => $item->response_id,
270                            'company_id' => $newCompanyId,
271                            'nickname' => $item->nickname,
272                            'from_email' => $item->from_email,
273                            'from_name' => $item->from_name,
274                            'reply_to' => $item->reply_to,
275                            'reply_to_name' => $item->reply_to_name,
276                            'address' => $item->address,
277                            'state' => $item->state,
278                            'city' => $item->city,
279                            'country' => $item->country,
280                            'zip' => $item->zip,
281                            'verified' => $item->verified,
282                            'locked' => $item->locked,
283                            'is_active' => $item->is_active,
284                            'created_by' => $data['created_by'],
285                        ];
286                    }
287
288                    TblCompanyEmails::insert($newCompanyEmails);
289                }
290
291                $companyUsers = TblCompanyUsers::where('company_id', $companyId)->get();
292
293                if (count($companyUsers) > 0) {
294                    $newCompanyUsers = [];
295                    foreach ($companyUsers as $item) {
296                        $newCompanyUsers[] = [
297                            'user_id' => $item->user_id,
298                            'company_id' => $newCompanyId,
299                            'can_read' => $item->can_read,
300                            'can_write' => $item->can_write,
301                            'is_selected' => $item->is_selected,
302                            'created_by' => $data['created_by'],
303                        ];
304                    }
305
306                    TblCompanyUsers::insert($newCompanyUsers);
307
308                    // FIRE-1146: clone_company added rows for every user in the source
309                    // company — flush the whole 'users' domain rather than N forget calls.
310                    ResultCache::forgetDomain('users');
311                }
312
313                $emailConfiguration = TblEmailConfiguration::where('company_id', $companyId)->get();
314
315                if (count($emailConfiguration) > 0) {
316                    $newEmailConfiguration = [];
317                    foreach ($emailConfiguration as $item) {
318                        $newEmailConfiguration[] = [
319                            'company_id' => $newCompanyId,
320                            'from_id' => $item->from_id,
321                            'name' => $item->name,
322                            'subject' => $item->subject,
323                            'html' => $item->html,
324                            'is_active' => $item->is_active,
325                            'type' => $item->type,
326                            'created_by' => $data['created_by'],
327                            'email_template_id_copied' => $item->id,
328                        ];
329                    }
330
331                    TblEmailConfiguration::insert($newEmailConfiguration);
332
333                    $query = "SELECT 
334                                a.id, 
335                                (
336                                    SELECT 
337                                        id 
338                                    FROM 
339                                        tbl_company_emails 
340                                    WHERE 
341                                        from_email = b.from_email 
342                                        AND company_id = {$newCompanyId}
343                                ) from_id 
344                                FROM 
345                                    tbl_email_configuration a 
346                                LEFT JOIN tbl_company_emails b 
347                                    ON a.from_id = b.id 
348                                WHERE 
349                                    a.company_id = {$newCompanyId}";
350
351                    $r = DB::select($query);
352
353                    foreach ($r as $item) {
354                        TblEmailConfiguration::where('id', $item->id)->update(['from_id' => $item->from_id]);
355                    }
356
357                    $query = "INSERT INTO tbl_email_files (
358                                    email_template_id, 
359                                    original_name, 
360                                    filename, 
361                                    uploaded_by, 
362                                    uploaded_at
363                                ) 
364                                SELECT 
365                                    b.id, 
366                                    a.original_name, 
367                                    a.filename, 
368                                    '{$data['created_by']}', 
369                                    NOW() 
370                                FROM 
371                                    tbl_email_files a 
372                                LEFT JOIN tbl_email_configuration b 
373                                    ON a.email_template_id = b.email_template_id_copied 
374                                WHERE 
375                                    b.company_id = {$newCompanyId}";
376
377                    DB::select($query);
378                }
379
380                $projectTypes = TblProjectTypes::where('company_id', $companyId)->get();
381
382                if (count($projectTypes) > 0) {
383                    $newProjectTypes = [];
384                    foreach ($projectTypes as $item) {
385                        $newProjectTypes[] = [
386                            'company_id' => $newCompanyId,
387                            'project_id' => $item->project_id,
388                            'budget_type_id' => $item->budget_type_id,
389                            'customer_type_ids' => $item->customer_type_ids,
390                            'revenue_per_employee_per_day' => $item->revenue_per_employee_per_day,
391                            'minimum_margin' => $item->minimum_margin,
392                            'general_costs' => $item->general_costs,
393                            'minimum_order_size' => $item->minimum_order_size,
394                            'minimum_order_size_v2' => $item->minimum_order_size_v2,
395                            'created_by' => $data['created_by'],
396                        ];
397                    }
398
399                    TblProjectTypes::insert($newProjectTypes);
400                }
401
402                $lastFolloUpDates = TblLastFollowUpDate::where('company_id', $companyId)->get();
403
404                if (count($lastFolloUpDates) > 0) {
405                    $newLastFollowUpDate = [];
406                    foreach ($lastFolloUpDates as $item) {
407                        $newLastFollowUpDate[] = [
408                            'company_id' => $newCompanyId,
409                            'row_id' => $item->row_id,
410                            'budget_type_id' => $item->budget_type_id,
411                            'last_follow_up_date' => $item->last_follow_up_date,
412                            'created_by' => $data['created_by'],
413                        ];
414                    }
415
416                    TblLastFollowUpDate::insert($newLastFollowUpDate);
417                }
418
419                $toAcceptanceNotifications = TblToAcceptanceNotifications::where('company_id', $companyId)->get();
420
421                if (count($toAcceptanceNotifications) > 0) {
422                    $newToAcceptanceNotifications = [];
423                    foreach ($toAcceptanceNotifications as $item) {
424                        $newToAcceptanceNotifications[] = [
425                            'company_id' => $newCompanyId,
426                            'email' => $item->email,
427                            'created_by' => $data['created_by'],
428                        ];
429                    }
430
431                    TblToAcceptanceNotifications::insert($newToAcceptanceNotifications);
432                }
433
434                $workflowApprover = TblWorkflowApprover::where('company_id', $companyId)->get();
435
436                if (count($workflowApprover) > 0) {
437                    $newWorkflowApprover = [];
438                    foreach ($workflowApprover as $item) {
439                        $newWorkflowApprover[] = [
440                            'company_id' => $newCompanyId,
441                            'user_id' => $item->user_id,
442                            'created_by' => $data['created_by'],
443                        ];
444                    }
445
446                    TblWorkflowApprover::insert($newWorkflowApprover);
447                }
448
449                $workflowCustomerTypes = TblWorkflowCustomerTypes::where('company_id', $companyId)->get();
450
451                if (count($workflowCustomerTypes) > 0) {
452                    $newWorkflowCustomerTypes = [];
453                    foreach ($workflowCustomerTypes as $item) {
454                        $newWorkflowCustomerTypes[] = [
455                            'company_id' => $newCompanyId,
456                            'customer_type_id' => $item->customer_type_id,
457                            'created_by' => $data['created_by'],
458                        ];
459                    }
460
461                    TblWorkflowCustomerTypes::insert($newWorkflowCustomerTypes);
462                }
463
464                $workflowProjectTypes = TblWorkflowProjectTypes::where('company_id', $companyId)->get();
465
466                if (count($workflowProjectTypes) > 0) {
467                    $newWorkflowProjectTypes = [];
468                    foreach ($workflowProjectTypes as $item) {
469                        $newWorkflowProjectTypes[] = [
470                            'company_id' => $newCompanyId,
471                            'budget_type_id' => $item->budget_type_id,
472                            'created_by' => $data['created_by'],
473                        ];
474                    }
475
476                    TblWorkflowProjectTypes::insert($newWorkflowProjectTypes);
477                }
478
479                $tradeAlignmentFacility = TblTradeAlignmentSettings::where('company_id', $companyId)->get();
480
481                if (count($tradeAlignmentFacility) > 0) {
482                    $newTradeAlignmentFacility = [];
483                    foreach ($tradeAlignmentFacility as $item) {
484                        $newTradeAlignmentFacility[] = [
485                            'company_id' => $newCompanyId,
486                            'budget_type_id' => $item->budget_type_id,
487                            'minimum_budget_amount' => $item->minimum_budget_amount,
488                            'created_by' => $data['created_by'],
489                        ];
490                    }
491
492                    TblWorkflowProjectTypes::insert($newTradeAlignmentFacility);
493                }
494            } else {
495                $result = TblCompanies::create($data);
496                $newCompanyId = $result->id;
497
498                TblCompanyUsers::create(
499                    [
500                        'company_id' => $newCompanyId,
501                        'user_id' => $userId,
502                        'can_read' => 1,
503                        'can_write' => 1,
504                        'created_by' => $data['created_by'],
505                    ]
506                );
507
508                // FIRE-1146: the creator just got assigned to a new company.
509                UserCompanies::forget((int) $userId);
510
511                for ($i = 1; $i < 7; $i++) {
512                    TblProjectTypes::create(
513                        [
514                            'company_id' => $result->id,
515                            'project_id' => $i,
516                            'created_by' => $data['created_by'],
517                        ]
518                    );
519                }
520
521                for ($i = 1; $i < 3; $i++) {
522                    TblLastFollowUpDate::create(
523                        [
524                            'company_id' => $result->id,
525                            'row_id' => $i,
526                            'created_by' => $data['created_by'],
527                        ]
528                    );
529                }
530
531                for ($i = 1; $i < 7; $i++) {
532                    TblTradeAlignmentSettings::create(
533                        [
534                            'company_id' => $result->id,
535                            'minimum_budget_amount' => 0,
536                            'created_by' => $data['created_by'],
537                        ]
538                    );
539                }
540            }
541
542            TblFinalSummary::create(
543                [
544                    'company_id' => $newCompanyId,
545                    'created_by' => $data['created_by'],
546                ]
547            );
548
549            $preLoadedQuestions[1] = '¿Confirma que hemos ofrecido presupuesto de mantenimiento de PCI activo?';
550            $preLoadedQuestions[2] = '¿El pago inicial cubre el coste de materiales y mano de obra?';
551            $preLoadedQuestions[3] = '¿Has solicitado un análisis de riesgo a la administración de Grupo Fire para no correr riesgo de impagos?';
552            $preLoadedQuestions[4] = null;
553            $preLoadedQuestions[5] = null;
554
555            for ($i = 1; $i < 5; $i++) {
556                TblWorkflowQuestions::create(
557                    [
558                        'company_id' => $result->id,
559                        'question' => $preLoadedQuestions[$i],
560                        'created_by' => $data['created_by'],
561                    ]
562                );
563            }
564
565            DB::commit();
566
567            return response(['message' => 'OK', 'data' => $result]);
568
569        } catch (\Exception $e) {
570            report(AppException::fromException($e, 'CREATE_COMPANY_EXCEPTION'));
571
572            return response(['message' => 'KO', 'error' => $e->getMessage()]);
573        }
574    }
575
576    public function list_companies(): ResponseFactory|Response
577    {
578
579        try {
580
581            $query = "SELECT
582                        c.company_id,
583                        c.name,
584                        c.fiscal_id,
585                        c.address,
586                        c.logo,
587                        c.filetype,
588                        DATE_FORMAT(c.created_at, '%b %d, %Y %l:%i%p') 'created_at',
589                        c.created_by,
590                        DATE_FORMAT(c.updated_at, '%b %d, %Y %l:%i%p') 'updated_at',
591                        c.updated_by,
592                        c.region,
593                        c.is_send_executive,
594                        COALESCE(q.total_quotations, 0) AS total_quotations
595                    FROM tbl_companies c
596                    LEFT JOIN (
597                        SELECT company_id, COUNT(*) AS total_quotations
598                        FROM tbl_quotations
599                        WHERE company_id IS NOT NULL
600                        GROUP BY company_id
601                    ) q ON c.company_id = q.company_id
602                    ORDER BY c.region ASC";
603
604            $result = DB::select($query);
605
606            return response(['message' => 'OK', 'data' => $result]);
607
608        } catch (\Exception $e) {
609            report(AppException::fromException($e, 'LIST_COMPANIES_EXCEPTION'));
610
611            return response(['message' => 'KO', 'error' => $e->getMessage()]);
612        }
613    }
614
615    public function get_company($companyId): ResponseFactory|Response
616    {
617
618        try {
619
620            $companyId = addslashes((string) $companyId);
621
622            $query = "SELECT 
623                        company_id,
624                        name,
625                        fiscal_id,
626                        address,
627                        logo,
628                        filetype,
629                        filename,
630                        filesize,
631                        filetype,
632                        default_page,
633                        is_send,
634                        limit_send,
635                        is_send_follow_up,
636                        is_send_request,
637                        is_send_g3w,
638                        revenue_per_employee_per_day,
639                        minimum_margin,
640                        general_costs,
641                        hours_per_worker_per_day,
642                        cost_of_hour,
643                        hours_per_worker_per_day_percentage,
644                        convert_to_job_amount_limit,
645                        minimum_order_size,
646                        minimum_order_size_v2,
647                        last_follow_up_date,
648                        limit_reminder_emails,
649                        workflow_budget_size,
650                        is_send_executive,
651                        customer_type_ids,
652                        goal_for_the_year,
653                        annual_target,
654                        process_limit,
655                        last_id,
656                        region,
657                        created_at,
658                        created_by,
659                        updated_at,
660                        updated_by,
661                        kpi_filters,
662                        kpi_bq_filters
663                    FROM tbl_companies
664                    WHERE company_id = {$companyId}
665                    ORDER BY created_at DESC";
666
667            $result = DB::select($query);
668
669            if ($result[0]->customer_type_ids != null) {
670                $result[0]->customer_type_ids = array_map(intval(...), explode(',', (string) $result[0]->customer_type_ids));
671            }
672
673            return response(['message' => 'OK', 'data' => $result]);
674
675        } catch (\Exception $e) {
676            report(AppException::fromException($e, 'GET_COMPANY_EXCEPTION'));
677
678            return response(['message' => 'KO', 'error' => $e->getMessage()]);
679        }
680    }
681
682    public function get_company_users($companyId): ResponseFactory|Response
683    {
684
685        try {
686
687            $companyId = addslashes((string) $companyId);
688
689            $where = '';
690            if ($companyId != 0) {
691                $where = " a.company_id = {$companyId} ";
692            } else {
693                $where = " a.company_id IN ({$this->companyId}) ";
694            }
695
696            $query = "SELECT
697                        a.id,
698                        a.user_id,
699                        a.company_id,
700                        b.name,
701                        b.email,    
702                        a.can_read,
703                        a.can_write,
704                        b.role_id,
705                        b.is_commercial
706                    FROM tbl_company_users a
707                    LEFT JOIN tbl_users b
708                        ON a.user_id = b.id
709                    WHERE {$where}
710                    GROUP BY a.user_id
711                    ORDER BY b.name ASC";
712
713            $result = DB::select($query);
714
715            return response(['message' => 'OK', 'data' => $result]);
716
717        } catch (\Exception $e) {
718            report(AppException::fromException($e, 'GET_COMPANY_USERS_EXCEPTION'));
719
720            return response(['message' => 'KO', 'error' => $e->getMessage()]);
721        }
722    }
723
724    public function get_user_companies($userId, $region = null): ResponseFactory|Response
725    {
726
727        try {
728
729            $userId = addslashes((string) $userId);
730
731            $where = '';
732
733            if ($region != null && $region != 'All') {
734                $region = urldecode((string) $region);
735                $where = " AND b.region = '{$region}' ";
736            }
737
738            $query = "SELECT 
739                        a.id,
740                        b.name, 
741                        b.logo, 
742                        b.filename,
743                        b.company_id,
744                        b.region,
745                        a.can_write,
746                        a.can_read,
747                        b.limit_send
748                    FROM 
749                        tbl_company_users a 
750                        LEFT JOIN tbl_companies b ON a.company_id = b.company_id 
751                    WHERE 
752                        a.user_id = {$userId}
753                    {$where}
754                    ORDER BY b.region ASC";
755
756            $result = DB::select($query);
757
758            if ($result) {
759                array_unshift(
760                    $result,
761                    [
762                        'id' => 0,
763                        'name' => 'All',
764                        'logo' => null,
765                        'company_id' => 0,
766                    ]
767                );
768            }
769
770            $query = "SELECT 
771                        DISTINCT
772                        b.region
773                    FROM 
774                        tbl_company_users a 
775                        LEFT JOIN tbl_companies b ON a.company_id = b.company_id 
776                    WHERE 
777                        a.user_id = {$userId}
778                        AND b.region IS NOT NULL
779                    ORDER BY b.region ASC";
780
781            $regions = DB::select($query);
782
783            if ($regions) {
784                array_unshift(
785                    $regions,
786                    [
787                        'region' => 'All',
788                    ]
789                );
790            }
791
792            $query = 'SELECT updated_at FROM tbl_quotations ORDER BY updated_at DESC LIMIT 1';
793            $latedDate = DB::select($query);
794
795            return response([
796                'message' => 'OK',
797                'data' => $result,
798                'regions' => $regions,
799                'latestUpdatedDate' => strtotime((string) $latedDate[0]->updated_at) * 1000,
800            ]);
801
802        } catch (\Exception $e) {
803            report(AppException::fromException($e, 'GET_USER_COMPANIES_EXCEPTION'));
804
805            return response(['message' => 'KO', 'error' => $e->getMessage()]);
806        }
807    }
808
809    public function update_company_users(Request $request): ResponseFactory|Response
810    {
811
812        try {
813
814            $data = $request->all();
815            $companyId = $data['company_id'];
816            $userId = $data['user_id'];
817
818            unset($data['company_id']);
819            unset($data['user_id']);
820
821            $result = [];
822
823            $result = TblCompanyUsers::where('company_id', $companyId)->where('user_id', $userId)->update($data);
824
825            return response(['message' => 'OK', 'data' => $result]);
826
827        } catch (\Exception $e) {
828            report(AppException::fromException($e, 'UPDATE_COMPANY_USERS_EXCEPTION'));
829
830            return response(['message' => 'KO', 'error' => $e->getMessage()]);
831        }
832
833    }
834
835    public function update_company_selected(Request $request): ResponseFactory|Response
836    {
837
838        try {
839
840            $data = $request->all();
841            $userId = $data['user_id'];
842            $id = $data['id'];
843
844            unset($data['user_id']);
845            unset($data['id']);
846
847            TblCompanyUsers::where('user_id', $userId)->update(['is_selected' => 0]);
848            $result = TblCompanyUsers::where('id', $id)->update(['is_selected' => 1]);
849
850            return response(['message' => 'OK', 'data' => $result]);
851
852        } catch (\Exception $e) {
853            report(AppException::fromException($e, 'UPDATE_COMPANY_SELECTED_EXCEPTION'));
854
855            return response(['message' => 'KO', 'error' => $e->getMessage()]);
856        }
857
858    }
859
860    public function delete_company_users(Request $request): ResponseFactory|Response
861    {
862
863        try {
864
865            $data = $request->all();
866
867            $id = addslashes((string) $data['user_id']);
868
869            $user = TblUsers::where('id', $id)->first();
870
871            $count = TblQuotations::where('company_id', $data['company_id'])->where('commercial', $user->name)->count();
872            $countQ = TblOngoingJobs::where('company_id', $data['company_id'])->where('responsible_for_work', $user->name)->count();
873
874            if ($count > 0 || $countQ > 0) {
875                $urlOrder = config('app.frontend_url')."orders?commercial={$user->name}&company_id=0";
876                $urlJob = config('app.frontend_url')."ongoing-jobs?responsible_for_work={$user->name}&company_id=0";
877                $urlOrder = "<a href='{$urlOrder}' target='_blank'>{$count}</a>";
878                $urlJob = "<a href='{$urlJob}' target='_blank'>{$countQ}</a>";
879
880                return response(['message' => 'user_cannot_be_deleted', 'error' => __('language.user_cannot_be_deleted'), 'total_job' => $urlJob, 'total_order' => $urlOrder]);
881            }
882
883            $result = TblCompanyUsers::where('user_id', $id)->where('company_id', $data['company_id'])->delete();
884
885            // FIRE-1146: the user lost access to one company.
886            UserCompanies::forget((int) $id);
887
888            return response(['message' => 'OK', 'data' => $result]);
889
890        } catch (\Exception $e) {
891            report(AppException::fromException($e, 'DELETE_COMPANY_USERS_EXCEPTION'));
892
893            return response(['message' => 'KO', 'error' => $e->getMessage()]);
894        }
895
896    }
897
898    public function update_company(Request $request, $companyId): ResponseFactory|Response
899    {
900
901        try {
902
903            $data = $request->all();
904            $companyId = addslashes((string) $companyId);
905
906            if (@$data['file'] == '') {
907                unset($data['file']);
908            }
909
910            if (isset($data['customer_type_ids'])) {
911                $data['customer_type_ids'] = implode(',', $data['customer_type_ids']);
912            }
913
914            if (isset($data['last_id'])) {
915                $data['before_last_id'] = $data['last_id'];
916            }
917
918            $data['updated_at'] = date('Y-m-d H:i:s');
919
920            $result = TblCompanies::where('company_id', $companyId)->update($data);
921
922            return response(['message' => 'OK', 'data' => $result]);
923
924        } catch (\Exception $e) {
925            report(AppException::fromException($e, 'UPDATE_COMPANY_EXCEPTION'));
926
927            return response(['message' => 'KO', 'error' => $e->getMessage()]);
928        }
929    }
930
931    public function update_company_blocked(Request $request): ResponseFactory|Response
932    {
933
934        try {
935
936            $data = $request->all();
937
938            TblCompanies::query()->update(['is_send_executive' => null]);
939            TblCompanies::whereIn('company_id', $data['company_ids'])->update(['is_send_executive' => 1, 'updated_at' => date('Y-m-d H:i:s')]);
940
941            return response(['message' => 'OK']);
942
943        } catch (\Exception $e) {
944            report(AppException::fromException($e, 'UPDATE_COMPANY_BLOCKED_EXCEPTION'));
945
946            return response(['message' => 'KO', 'error' => $e->getMessage()]);
947        }
948    }
949
950    public function upload_file(Request $request): ResponseFactory|Response
951    {
952
953        try {
954
955            $data = $request->all();
956            $companyId = $data['company_id'];
957
958            $data['updated_at'] = date('Y-m-d H:i:s');
959
960            if ($request->file('file')) {
961                $file = $request->file('file');
962
963                $mimetype = $file->getMimeType();
964
965                if (! str_contains((string) $mimetype, 'image')) {
966                    return response(['message' => 'invalid_file_type', 'error' => __('language.invalid_file_type')]);
967                }
968
969                $filename = $file->getClientOriginalName();
970                $filesize = $file->getSize();
971
972                if ($filesize > 5000000) {
973                    return response(['message' => 'limit_exceeded', 'error' => __('language.limit_exceeded')]);
974                }
975
976                $path = $file->getRealPath();
977
978                $logo = file_get_contents($path);
979                $base64 = base64_encode($logo);
980
981                $blob = 'data:'.$mimetype.';base64,'.$base64;
982
983                $data['logo'] = $blob;
984                $data['filename'] = $filename;
985                $data['filesize'] = $filesize;
986                $data['filetype'] = $mimetype;
987
988                unset($data['file']);
989            }
990
991            $result = TblCompanies::where('company_id', $companyId)->update($data);
992
993            return response(['message' => 'OK', 'data' => $result]);
994
995        } catch (\Exception $e) {
996            report(AppException::fromException($e, 'UPLOAD_FILE_EXCEPTION'));
997
998            return response(['message' => 'KO', 'error' => $e->getMessage()]);
999        }
1000    }
1001
1002    public function delete_company($companyId): ResponseFactory|Response
1003    {
1004
1005        try {
1006
1007            $companyId = addslashes((string) $companyId);
1008
1009            $count = TblCompanyUsers::where('company_id', $companyId)->count();
1010
1011            if ($count > 0) {
1012                return response(['message' => 'cannot_be_deleted', 'error' => __('language.cannot_be_deleted')]);
1013            }
1014
1015            $result = TblCompanies::where('company_id', $companyId)->delete();
1016            TblEmailConfiguration::where('company_id', $companyId)->delete();
1017
1018            $query = "DELETE FROM tbl_files WHERE quotation_id IN (SELECT id FROM tbl_quotations WHERE company_id = {$companyId})";
1019            DB::select($query);
1020
1021            TblQuotations::where('company_id', $companyId)->delete();
1022            TblProjectTypes::where('company_id', $companyId)->delete();
1023
1024            return response(['message' => 'OK', 'data' => $result]);
1025
1026        } catch (\Exception $e) {
1027            report(AppException::fromException($e, 'DELETE_COMPANY_EXCEPTION'));
1028
1029            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1030        }
1031    }
1032
1033    public function update_company_users_selected(Request $request): ResponseFactory|Response
1034    {
1035
1036        try {
1037
1038            $request->all();
1039            $data = $request->all();
1040            $companyId = $data['company_id'];
1041
1042            TblCompanyUsers::where('company_id', $companyId)->update(['is_selected' => null]);
1043            for ($i = 0; $i < count($data['user_ids']); $i++) {
1044                TblCompanyUsers::where('company_id', $companyId)->where('user_id', $data['user_ids'][$i])->update(['is_selected' => 1]);
1045            }
1046
1047            return response(['message' => 'OK']);
1048
1049        } catch (\Exception $e) {
1050            report(AppException::fromException($e, 'UPDATE_COMPANY_USERS_SELECTED_EXCEPTION'));
1051
1052            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1053        }
1054    }
1055
1056    public function create_company_users(Request $request): ResponseFactory|Response
1057    {
1058
1059        try {
1060
1061            $request->all();
1062            $data = $request->all();
1063            $companyId = $data['company_id'];
1064
1065            if ($data['user_ids']) {
1066                $userIds = implode(',', $data['user_ids']);
1067
1068                $query = "DELETE FROM tbl_company_users WHERE user_id NOT IN ({$userIds}) AND company_id = {$companyId}";
1069                DB::select($query);
1070
1071                $companyUsers = [];
1072
1073                $query = "SELECT user_id FROM tbl_company_users WHERE company_id = {$companyId} AND user_id IN ({$userIds})";
1074                $result = DB::select($query);
1075
1076                $ids = [];
1077
1078                for ($i = 0; $i < count($result); $i++) {
1079                    array_push($ids, $result[$i]->user_id);
1080                }
1081
1082                for ($i = 0; $i < count($data['user_ids']); $i++) {
1083                    if (! in_array($data['user_ids'][$i], $ids)) {
1084                        array_push(
1085                            $companyUsers,
1086                            [
1087                                'user_id' => $data['user_ids'][$i],
1088                                'company_id' => $companyId,
1089                                'can_read' => 1,
1090                                'can_write' => 0,
1091                                'created_by' => $data['created_by'],
1092                            ]
1093                        );
1094                    }
1095                }
1096
1097                TblCompanyUsers::insert($companyUsers);
1098            } else {
1099                TblCompanyUsers::where('company_id', $companyId)->delete();
1100            }
1101
1102            // FIRE-1146: bulk reassign — affects many users at once, flush the bucket.
1103            ResultCache::forgetDomain('users');
1104
1105            return response(['message' => 'OK']);
1106
1107        } catch (\Exception $e) {
1108            report(AppException::fromException($e, 'CREATE_COMPANY_USERS_EXCEPTION'));
1109
1110            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1111        }
1112    }
1113
1114    public function get_cc_bcc($id): ResponseFactory|Response
1115    {
1116
1117        try {
1118
1119            $companyId = addslashes((string) $id);
1120
1121            $result = TblCcBcc::where('company_id', $companyId)->get();
1122
1123            return response(['message' => 'OK', 'data' => $result]);
1124
1125        } catch (\Exception $e) {
1126            report(AppException::fromException($e, 'GET_CC_BCC_EXCEPTION'));
1127
1128            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1129        }
1130    }
1131
1132    public function delete_cc_bcc($id): ResponseFactory|Response
1133    {
1134
1135        try {
1136
1137            $id = addslashes((string) $id);
1138
1139            TblCcBcc::where('id', $id)->delete();
1140
1141            return response(['message' => 'OK']);
1142
1143        } catch (\Exception $e) {
1144            report(AppException::fromException($e, 'DELETE_CC_BCC_EXCEPTION'));
1145
1146            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1147        }
1148    }
1149
1150    public function update_cc_bcc(Request $request, $id): ResponseFactory|Response
1151    {
1152
1153        try {
1154
1155            $data = $request->all();
1156            $id = addslashes((string) $id);
1157
1158            $x = $this->isEmailValid($data['email']);
1159
1160            if ($x) {
1161                $result = TblCcBcc::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
1162
1163                if ($result > 0) {
1164                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
1165                } else {
1166                    $data['updated_at'] = date('Y-m-d H:i:s');
1167                    TblCcBcc::where('id', $id)->update($data);
1168                }
1169            } else {
1170                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
1171            }
1172
1173            return response(['message' => 'OK']);
1174
1175        } catch (\Exception $e) {
1176            report(AppException::fromException($e, 'UPDATE_CC_BCC_EXCEPTION'));
1177
1178            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1179        }
1180    }
1181
1182    public function create_cc_bcc(Request $request): ResponseFactory|Response
1183    {
1184
1185        try {
1186
1187            $data = $request->all();
1188
1189            $x = $this->isEmailValid($data['email']);
1190
1191            if ($x) {
1192                $result = TblCcBcc::where('company_id', $data['company_id'])->where('email', strtolower((string) $data['email']))->count();
1193
1194                if ($result > 0) {
1195                    return response(['message' => 'KO', 'error' => __('language.email_exists')]);
1196                } else {
1197                    TblCcBcc::create($data);
1198                }
1199            } else {
1200                return response(['message' => 'KO', 'error' => __('language.email_invalid')]);
1201            }
1202
1203            return response(['message' => 'OK']);
1204
1205        } catch (\Exception $e) {
1206            report(AppException::fromException($e, 'CREATE_CC_BCC_EXCEPTION'));
1207
1208            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1209        }
1210    }
1211
1212    public function isEmailValid($email): bool
1213    {
1214        // Regular expression pattern for email validation
1215        $pattern = '/^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$/';
1216
1217        // Check if the email matches the pattern
1218        if (preg_match($pattern, (string) $email)) {
1219            return true; // Valid email
1220        } else {
1221            return false; // Invalid email
1222        }
1223    }
1224
1225    public function get_blocked_domains($id): ResponseFactory|Response
1226    {
1227
1228        try {
1229
1230            $companyId = addslashes((string) $id);
1231
1232            $result = [];
1233
1234            if ($companyId != 0) {
1235                $result = TblBlockedDomains::where('company_id', $companyId)->get();
1236            } else {
1237                $result = TblBlockedDomains::whereIn('company_id', $this->companyIds)->get();
1238            }
1239
1240            return response(['message' => 'OK', 'data' => $result]);
1241
1242        } catch (\Exception $e) {
1243            report(AppException::fromException($e, 'GET_BLOCKED_DOMAINS_EXCEPTION'));
1244
1245            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1246        }
1247    }
1248
1249    public function delete_blocked_domain($id): ResponseFactory|Response
1250    {
1251
1252        try {
1253
1254            $id = addslashes((string) $id);
1255
1256            TblBlockedDomains::where('id', $id)->delete();
1257
1258            // FIRE-1145: was Cache::flush() — blocked domain joins into list_quotations totalToFollowUpQuery.
1259            ResultCache::forgetDomain('quotations');
1260
1261            return response(['message' => 'OK']);
1262
1263        } catch (\Exception $e) {
1264            report(AppException::fromException($e, 'DELETE_BLOCKED_DOMAIN_EXCEPTION'));
1265
1266            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1267        }
1268    }
1269
1270    public function update_blocked_domain(Request $request, $id): ResponseFactory|Response
1271    {
1272
1273        try {
1274
1275            $data = $request->all();
1276            $id = addslashes((string) $id);
1277
1278            $result = TblBlockedDomains::where('company_id', $data['company_id'])->where('domain', strtolower((string) $data['domain']))->count();
1279
1280            if ($result > 0) {
1281                return response(['message' => 'KO', 'error' => __('language.domain_exists')]);
1282            } else {
1283                $data['updated_at'] = date('Y-m-d H:i:s');
1284                TblBlockedDomains::where('id', $id)->update($data);
1285            }
1286
1287            // FIRE-1145: was Cache::flush() — blocked domain joins into list_quotations.
1288            ResultCache::forgetDomain('quotations');
1289
1290            return response(['message' => 'OK']);
1291
1292        } catch (\Exception $e) {
1293            report(AppException::fromException($e, 'UPDATE_BLOCKED_DOMAIN_EXCEPTION'));
1294
1295            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1296        }
1297    }
1298
1299    public function create_blocked_domain(Request $request): ResponseFactory|Response
1300    {
1301
1302        try {
1303
1304            $data = $request->all();
1305
1306            $result = TblBlockedDomains::where('company_id', $data['company_id'])->where('domain', strtolower((string) $data['domain']))->count();
1307
1308            if ($result > 0) {
1309                return response(['message' => 'KO', 'error' => __('language.domain_exists')]);
1310            } else {
1311                TblBlockedDomains::create($data);
1312            }
1313
1314            // FIRE-1145: was Cache::flush() — blocked domain joins into list_quotations.
1315            ResultCache::forgetDomain('quotations');
1316
1317            return response(['message' => 'OK']);
1318
1319        } catch (\Exception $e) {
1320            report(AppException::fromException($e, 'CREATE_BLOCKED_DOMAIN_EXCEPTION'));
1321
1322            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1323        }
1324    }
1325
1326    public function list_project_types($companyId): ResponseFactory|Response
1327    {
1328
1329        try {
1330
1331            $companyId = addslashes((string) $companyId);
1332
1333            $result = TblProjectTypes::where('company_id', $companyId)->orderBy('project_id', 'ASC')->get();
1334
1335            $data = [];
1336
1337            for ($i = 0; $i < count($result); $i++) {
1338                $data[$result[$i]->project_id]['project_id'] = $result[$i]->project_id;
1339                if ($result[$i]->budget_type_id != null) {
1340                    $data[$result[$i]->project_id]['budget_types'][] = $result[$i]->budget_type_id;
1341                }
1342                if ($result[$i]->customer_type_ids != null) {
1343                    $data[$result[$i]->project_id]['customer_types'] = array_map(intval(...), explode(',', (string) $result[$i]->customer_type_ids));
1344                }
1345                $data[$result[$i]->project_id]['benchmarks'] = [
1346                    'revenue_per_employee_per_day' => $result[$i]->revenue_per_employee_per_day,
1347                    'minimum_margin' => $result[$i]->minimum_margin,
1348                    'general_costs' => $result[$i]->general_costs,
1349                    'minimum_order_size' => $result[$i]->minimum_order_size,
1350                    'minimum_order_size_v2' => $result[$i]->minimum_order_size_v2,
1351                ];
1352            }
1353
1354            return response(['message' => 'OK', 'data' => array_values($data)]);
1355
1356        } catch (\Exception $e) {
1357            report(AppException::fromException($e, 'LIST_PROJECT_TYPES_EXCEPTION'));
1358
1359            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1360        }
1361
1362    }
1363
1364    public function get_project_types($companyId): ResponseFactory|Response
1365    {
1366
1367        try {
1368
1369            $companyId = addslashes((string) $companyId);
1370
1371            $result = TblProjectTypes::where('company_id', $companyId)->where('budget_type_id', '!=', null)->get();
1372
1373            // FIRE-1092: return `user_id` (FK to tbl_users) — pre-fix
1374            // this projected `approver_id` (the auto-increment PK of
1375            // `tbl_approvers`), which the frontend then compared against
1376            // the logged-in user's `tbl_users.id` at
1377            // `orders.component.ts:5454-5458`. Those two id spaces never
1378            // align, so the `approvers.includes(this.userId)` check
1379            // silently never matched and the per-company overwrite was
1380            // dead code. The load-bearing assignment for `isApprover`
1381            // still came from `getUserByEmail`, but this filter was
1382            // intended to narrow it down to the current company; now it
1383            // actually does.
1384            $query = "
1385                SELECT a.user_id, u.name AS approver, 'v1' as type
1386                FROM tbl_approvers a
1387                LEFT JOIN tbl_users u ON a.user_id = u.id
1388                WHERE a.company_id = {$companyId}
1389
1390                UNION ALL
1391
1392                SELECT a.user_id, u.name AS approver, 'v2' as type
1393                FROM tbl_approvers_v2 a
1394                LEFT JOIN tbl_users u ON a.user_id = u.id
1395                WHERE a.company_id = {$companyId}
1396            ";
1397
1398            $approvers = DB::select($query);
1399
1400            $ids = [];
1401            $names = [];
1402
1403            foreach ($approvers as $item) {
1404                $ids[] = $item->user_id;
1405                $names[] = $item->approver;
1406            }
1407
1408            return response([
1409                'message' => 'OK',
1410                'data' => $result,
1411                'approversData' => $approvers,
1412                'approvers' => $ids,
1413                'approverNames' => $names,
1414            ]);
1415
1416        } catch (\Exception $e) {
1417            report(AppException::fromException($e, 'GET_PROJECT_TYPES_EXCEPTION'));
1418
1419            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1420        }
1421    }
1422
1423    public function update_project_types(Request $request)
1424    {
1425
1426        try {
1427
1428            $data = $request->all();
1429            $companyId = addslashes((string) $data['company_id']);
1430            $projectId = addslashes((string) $data['project_id']);
1431            unset($data['company_id']);
1432            unset($data['project_id']);
1433
1434            $previousData = TblProjectTypes::where('company_id', $companyId)->where('project_id', $projectId)->first();
1435
1436            $lastBudgetTypeId = end($data['budget_type_id']);
1437            $project = TblProjectTypes::where('company_id', $companyId)->where('budget_type_id', $lastBudgetTypeId)->first();
1438
1439            TblProjectTypes::where('company_id', $companyId)->whereIn('budget_type_id', $data['budget_type_id'])->delete();
1440            TblProjectTypes::where('company_id', $companyId)->where('project_id', $projectId)->delete();
1441
1442            foreach ($data['budget_type_id'] as $budgetTypeId) {
1443                TblProjectTypes::create(
1444                    [
1445                        'company_id' => $companyId,
1446                        'project_id' => $projectId,
1447                        'customer_type_ids' => $previousData->customer_type_ids,
1448                        'budget_type_id' => $budgetTypeId,
1449                        'revenue_per_employee_per_day' => $previousData->revenue_per_employee_per_day,
1450                        'minimum_margin' => $previousData->minimum_margin,
1451                        'general_costs' => $previousData->general_costs,
1452                        'minimum_order_size' => $previousData->minimum_order_size,
1453                        'minimum_order_size_v2' => $previousData->minimum_order_size_v2,
1454                        'created_by' => $data['created_by'],
1455                    ]
1456                );
1457            }
1458
1459            if ($project != null) {
1460                if ($project->budget_type_id != null) {
1461                    TblProjectTypes::create(
1462                        [
1463                            'company_id' => $companyId,
1464                            'project_id' => $project->project_id,
1465                            'budget_type_id' => null,
1466                            'customer_type_ids' => $project->customer_type_ids,
1467                            'revenue_per_employee_per_day' => $project->revenue_per_employee_per_day,
1468                            'minimum_margin' => $project->minimum_margin,
1469                            'general_costs' => $project->general_costs,
1470                            'minimum_order_size' => $project->minimum_order_size,
1471                            'minimum_order_size_v2' => $project->minimum_order_size_v2,
1472                            'created_by' => $data['created_by'],
1473                        ]
1474                    );
1475                }
1476            }
1477
1478            return $this->list_project_types($companyId);
1479
1480        } catch (\Exception $e) {
1481            report(AppException::fromException($e, 'CREATE_PROJECT_TYPES_EXCEPTION'));
1482
1483            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1484        }
1485
1486    }
1487
1488    public function update_project_types_benchmarks(Request $request)
1489    {
1490
1491        try {
1492
1493            $data = $request->all();
1494            $companyId = addslashes((string) $data['company_id']);
1495            $projectId = addslashes((string) $data['project_id']);
1496
1497            unset($data['company_id']);
1498            unset($data['project_id']);
1499
1500            if (isset($data['customer_type_ids'])) {
1501                $data['customer_type_ids'] = implode(',', $data['customer_type_ids']);
1502            }
1503
1504            TblProjectTypes::where('company_id', $companyId)->where('project_id', $projectId)->update($data);
1505
1506            return $this->list_project_types($companyId);
1507
1508        } catch (\Exception $e) {
1509            report(AppException::fromException($e, 'UPDATE_PROJECT_TYPES_BENCHMARKS_EXCEPTION'));
1510
1511            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1512        }
1513
1514    }
1515
1516    public function delete_project_types(Request $request)
1517    {
1518
1519        try {
1520
1521            $data = $request->all();
1522            $companyId = addslashes((string) $data['company_id']);
1523            $projectId = addslashes((string) $data['project_id']);
1524            unset($data['company_id']);
1525            unset($data['project_id']);
1526
1527            $count = TblProjectTypes::where('company_id', $companyId)->where('project_id', $projectId)->count();
1528
1529            if ($count > 1) {
1530                TblProjectTypes::where('company_id', $companyId)->where('budget_type_id', $data['budget_type_id'])->delete();
1531            } else {
1532                TblProjectTypes::where('company_id', $companyId)->where('project_id', $projectId)->update(['budget_type_id' => null]);
1533            }
1534
1535            return $this->list_project_types($companyId);
1536
1537        } catch (\Exception $e) {
1538            report(AppException::fromException($e, 'DELETE_PROJECT_TYPES_EXCEPTION'));
1539
1540            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1541        }
1542
1543    }
1544
1545    public function list_workflow_project_types($companyId): ResponseFactory|Response
1546    {
1547
1548        try {
1549
1550            $companyId = addslashes((string) $companyId);
1551
1552            $result = TblWorkflowProjectTypes::where('company_id', $companyId)->get();
1553
1554            return response(['message' => 'OK', 'data' => $result]);
1555
1556        } catch (\Exception $e) {
1557            report(AppException::fromException($e, 'LIST_WORKFLOW_PROJECT_TYPES_EXCEPTION'));
1558
1559            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1560        }
1561
1562    }
1563
1564    public function update_workflow_project_types(Request $request)
1565    {
1566
1567        try {
1568
1569            $data = $request->all();
1570
1571            TblWorkflowProjectTypes::where('company_id', $data['company_id'])->delete();
1572
1573            for ($i = 0; $i < count($data['budget_type_id']); $i++) {
1574                TblWorkflowProjectTypes::create(
1575                    [
1576                        'company_id' => $data['company_id'],
1577                        'budget_type_id' => $data['budget_type_id'][$i],
1578                        'created_by' => $data['created_by'],
1579                    ]
1580                );
1581            }
1582
1583            return $this->list_workflow_project_types($data['company_id']);
1584
1585        } catch (\Exception $e) {
1586            report(AppException::fromException($e, 'UPDATE_WORKFLOW_PROJECT_TYPES_EXCEPTION'));
1587
1588            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1589        }
1590
1591    }
1592
1593    public function delete_workflow_project_types(Request $request)
1594    {
1595
1596        try {
1597
1598            $data = $request->all();
1599
1600            $companyId = addslashes((string) $data['company_id']);
1601            $budgetTypeId = addslashes((string) $data['budget_type_id']);
1602
1603            TblWorkflowProjectTypes::where('budget_type_id', $budgetTypeId)->where('company_id', $companyId)->delete();
1604
1605            return $this->list_workflow_project_types($companyId);
1606
1607        } catch (\Exception $e) {
1608            report(AppException::fromException($e, 'DELETE_WORKFLOW_PROJECT_TYPES_EXCEPTION'));
1609
1610            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1611        }
1612
1613    }
1614
1615    public function list_workflow_customer_types($companyId): ResponseFactory|Response
1616    {
1617
1618        try {
1619
1620            $companyId = addslashes((string) $companyId);
1621
1622            $result = TblWorkflowCustomerTypes::where('company_id', $companyId)->get();
1623
1624            return response(['message' => 'OK', 'data' => $result]);
1625
1626        } catch (\Exception $e) {
1627            report(AppException::fromException($e, 'LIST_WORKFLOW_CUSTOMER_TYPES_EXCEPTION'));
1628
1629            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1630        }
1631
1632    }
1633
1634    public function update_workflow_customer_types(Request $request)
1635    {
1636
1637        try {
1638
1639            $data = $request->all();
1640
1641            TblWorkflowCustomerTypes::where('company_id', $data['company_id'])->delete();
1642
1643            for ($i = 0; $i < count($data['customer_type_id']); $i++) {
1644                TblWorkflowCustomerTypes::create(
1645                    [
1646                        'company_id' => $data['company_id'],
1647                        'customer_type_id' => $data['customer_type_id'][$i],
1648                        'created_by' => $data['created_by'],
1649                    ]
1650                );
1651            }
1652
1653            return $this->list_workflow_project_types($data['company_id']);
1654
1655        } catch (\Exception $e) {
1656            report(AppException::fromException($e, 'UPDATE_WORKFLOW_CUSTOMER_TYPES_EXCEPTION'));
1657
1658            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1659        }
1660
1661    }
1662
1663    public function delete_workflow_customer_types(Request $request)
1664    {
1665
1666        try {
1667
1668            $data = $request->all();
1669
1670            $companyId = addslashes((string) $data['company_id']);
1671            $customerTypeId = addslashes((string) $data['customer_type_id']);
1672
1673            TblWorkflowCustomerTypes::where('customer_type_id', $customerTypeId)->where('company_id', $companyId)->delete();
1674
1675            return $this->list_workflow_customer_types($companyId);
1676
1677        } catch (\Exception $e) {
1678            report(AppException::fromException($e, 'DELETE_WORKFLOW_CUSTOMER_TYPES_EXCEPTION'));
1679
1680            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1681        }
1682
1683    }
1684
1685    public function list_workflow_questions($companyId): ResponseFactory|Response
1686    {
1687
1688        try {
1689
1690            $companyId = addslashes((string) $companyId);
1691
1692            $result = TblWorkflowQuestions::where('company_id', $companyId)->get();
1693
1694            return response(['message' => 'OK', 'data' => $result]);
1695
1696        } catch (\Exception $e) {
1697            report(AppException::fromException($e, 'LIST_WORKFLOW_QUESTIONS_EXCEPTION'));
1698
1699            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1700        }
1701
1702    }
1703
1704    public function update_workflow_questions(Request $request, $questionId)
1705    {
1706
1707        try {
1708
1709            $data = $request->all();
1710            $questionId = addslashes((string) $questionId);
1711            $companyId = addslashes((string) $data['company_id']);
1712            unset($data['company_id']);
1713
1714            $data['updated_at'] = date('Y-m-d H:i:s');
1715            TblWorkflowQuestions::where('question_id', $questionId)->update($data);
1716
1717            return $this->list_workflow_questions($companyId);
1718
1719        } catch (\Exception $e) {
1720            report(AppException::fromException($e, 'UPDATE_WORKFLOW_QUESTIONS_EXCEPTION'));
1721
1722            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1723        }
1724
1725    }
1726
1727    public function get_company_executive(): ResponseFactory|Response
1728    {
1729
1730        try {
1731
1732            $result = TblCompanies::where('is_send_executive', 1)->get();
1733
1734            return response(['message' => 'OK', 'data' => $result]);
1735
1736        } catch (\Exception $e) {
1737            report(AppException::fromException($e, 'GET_COMPANY_EXECUTIVE_EXCEPTION'));
1738
1739            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1740        }
1741
1742    }
1743
1744    public function get_all_details($companyId): ResponseFactory|Response
1745    {
1746
1747        try {
1748
1749            $companyId = addslashes((string) $companyId);
1750
1751            $getCompany = $this->get_company($companyId);
1752            $listWorkflowQuestions = $this->list_workflow_questions($companyId);
1753            $listWorkflowCustomerTypes = $this->list_workflow_customer_types($companyId);
1754            $listWorkflowProjectTypes = $this->list_workflow_project_types($companyId);
1755            $getProjectTypes = $this->get_project_types($companyId);
1756            $getCompanyUsers = $this->get_company_users($companyId);
1757            $getBlockedDomains = TblBlockedDomains::where('company_id', $companyId)->get();
1758
1759            return response([
1760                'message' => 'OK',
1761                'getCompany' => $getCompany->original['data'],
1762                'approvers' => $getProjectTypes->original['approvers'],
1763                'approversData' => $getProjectTypes->original['approversData'],
1764                'approverNames' => $getProjectTypes->original['approverNames'],
1765                'listWorkflowQuestions' => $listWorkflowQuestions->original['data'],
1766                'listWorkflowCustomerTypes' => $listWorkflowCustomerTypes->original['data'],
1767                'listWorkflowProjectTypes' => $listWorkflowProjectTypes->original['data'],
1768                'getProjectTypes' => $getProjectTypes->original['data'],
1769                'getBlockedDomains' => $getBlockedDomains,
1770                'getCompanyUsers' => $getCompanyUsers->original['data'],
1771            ]);
1772
1773        } catch (\Exception $e) {
1774            report(AppException::fromException($e, 'GET_ALL_DETAILS_EXCEPTION'));
1775
1776            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1777        }
1778    }
1779
1780    public function list_all_companies(): ResponseFactory|Response
1781    {
1782
1783        try {
1784
1785            $result = TblCompanies::whereIn('company_id', $this->companyIds)->get();
1786
1787            return response([
1788                'message' => 'OK',
1789                'data' => $result,
1790            ]);
1791
1792        } catch (\Exception $e) {
1793            report(AppException::fromException($e, 'LIST_ALL_COMPANIES_EXCEPTION'));
1794
1795            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1796        }
1797
1798    }
1799
1800    public function update_company_permission(Request $request, $userId): ResponseFactory|Response
1801    {
1802
1803        try {
1804
1805            $data = $request->all();
1806            $userId = addslashes((string) $userId);
1807
1808            if (isset($data['read_only'])) {
1809                if ($data['read_only'] == false) {
1810                    TblCompanyUsers::where('user_id', $userId)->update(
1811                        [
1812                            'can_read' => 1,
1813                            'can_write' => 0,
1814                            'updated_at' => date('Y-m-d H:i:s'),
1815                            'updated_by' => $data['updated_by'],
1816                        ]
1817                    );
1818                } else {
1819                    TblCompanyUsers::where('user_id', $userId)->update(
1820                        [
1821                            'can_read' => 1,
1822                            'can_write' => 1,
1823                            'updated_at' => date('Y-m-d H:i:s'),
1824                            'updated_by' => $data['updated_by'],
1825                        ]
1826                    );
1827                }
1828            }
1829
1830            return response([
1831                'message' => 'OK',
1832            ]);
1833
1834        } catch (\Exception $e) {
1835            report(AppException::fromException($e, 'UPDATE_COMPANY_PERMISSION_EXCEPTION'));
1836
1837            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1838        }
1839
1840    }
1841
1842    public function list_facility_settings(): ResponseFactory|Response
1843    {
1844
1845        try {
1846
1847            $result = TblFacilitySettings::get();
1848
1849            for ($i = 0; $i < count($result); $i++) {
1850                if ($result[$i]->budget_type_id != null) {
1851                    $result[$i]->budget_type_id = array_map(intval(...), explode(',', (string) $result[$i]->budget_type_id));
1852                }
1853            }
1854
1855            return response([
1856                'message' => 'OK',
1857                'data' => $result,
1858            ]);
1859
1860        } catch (\Exception $e) {
1861            report(AppException::fromException($e, 'LIST_FACILITY_SETTINGS_EXCEPTION'));
1862
1863            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1864        }
1865
1866    }
1867
1868    public function update_facility_settings(Request $request, $id): ResponseFactory|Response
1869    {
1870        try {
1871
1872            $data = $request->all();
1873            $id = addslashes((string) $id);
1874            $data['updated_at'] = date('Y-m-d H:i:s');
1875
1876            if (isset($data['budget_type_id']) && is_array($data['budget_type_id'])) {
1877                $incomingIds = $data['budget_type_id'];
1878
1879                $rows = TblFacilitySettings::all();
1880
1881                foreach ($rows as $row) {
1882                    $currentIds = $row->budget_type_id ? explode(',', (string) $row->budget_type_id) : [];
1883
1884                    $newIds = array_diff($currentIds, $incomingIds);
1885
1886                    if (! empty($newIds)) {
1887                        TblFacilitySettings::where('id', $row->id)->update([
1888                            'budget_type_id' => implode(',', $newIds),
1889                            'updated_at' => date('Y-m-d H:i:s'),
1890                        ]);
1891                    } else {
1892                        TblFacilitySettings::where('id', $row->id)->update([
1893                            'budget_type_id' => '',
1894                            'updated_at' => date('Y-m-d H:i:s'),
1895                        ]);
1896                    }
1897                }
1898
1899                $data['budget_type_id'] = implode(',', $incomingIds);
1900
1901                $exists = TblFacilitySettings::where('id', $id)->exists();
1902                if ($exists) {
1903                    TblFacilitySettings::where('id', $id)->update($data);
1904                } else {
1905                    TblFacilitySettings::create(array_merge($data, ['id' => $id]));
1906                }
1907            } elseif (isset($data['minimum_budget_amount'])) {
1908                TblFacilitySettings::where('id', $id)->update($data);
1909            }
1910
1911            $result = $this->list_facility_settings();
1912
1913            return response([
1914                'message' => 'OK',
1915                'data' => $result->original['data'],
1916            ]);
1917
1918        } catch (\Exception $e) {
1919            report(AppException::fromException($e, 'UPDATE_FACILITY_SETTINGS_EXCEPTION'));
1920
1921            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1922        }
1923    }
1924
1925    public function list_trade_alignment_settings($companyId): ResponseFactory|Response
1926    {
1927
1928        try {
1929
1930            $companyId = addslashes((string) $companyId);
1931            $result = TblTradeAlignmentSettings::where('company_id', $companyId)->get();
1932
1933            for ($i = 0; $i < count($result); $i++) {
1934                if ($result[$i]->budget_type_id != null) {
1935                    $result[$i]->budget_type_id = array_map(intval(...), explode(',', (string) $result[$i]->budget_type_id));
1936                }
1937            }
1938
1939            return response([
1940                'message' => 'OK',
1941                'data' => $result,
1942            ]);
1943
1944        } catch (\Exception $e) {
1945            report(AppException::fromException($e, 'LIST_TRADE_ALIGNMENT_SETTINGS_EXCEPTION'));
1946
1947            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1948        }
1949
1950    }
1951
1952    public function update_trade_alignment_settings(Request $request, $id): ResponseFactory|Response
1953    {
1954        try {
1955
1956            $data = $request->all();
1957            $id = addslashes((string) $id);
1958            $data['updated_at'] = date('Y-m-d H:i:s');
1959            $companyId = addslashes((string) $data['company_id']);
1960
1961            if (isset($data['budget_type_id']) && is_array($data['budget_type_id'])) {
1962                $incomingIds = $data['budget_type_id'];
1963
1964                $rows = TblTradeAlignmentSettings::where('company_id', $companyId)->get();
1965
1966                foreach ($rows as $row) {
1967                    $currentIds = $row->budget_type_id ? explode(',', (string) $row->budget_type_id) : [];
1968
1969                    $newIds = array_diff($currentIds, $incomingIds);
1970
1971                    if (! empty($newIds)) {
1972                        TblTradeAlignmentSettings::where('id', $row->id)->update([
1973                            'budget_type_id' => implode(',', $newIds),
1974                            'updated_at' => date('Y-m-d H:i:s'),
1975                        ]);
1976                    } else {
1977                        TblTradeAlignmentSettings::where('id', $row->id)->update([
1978                            'budget_type_id' => '',
1979                            'updated_at' => date('Y-m-d H:i:s'),
1980                        ]);
1981                    }
1982                }
1983
1984                $data['budget_type_id'] = implode(',', $incomingIds);
1985
1986                $exists = TblTradeAlignmentSettings::where('id', $id)->exists();
1987                if ($exists) {
1988                    TblTradeAlignmentSettings::where('id', $id)->update($data);
1989                } else {
1990                    TblTradeAlignmentSettings::create(array_merge($data, ['id' => $id]));
1991                }
1992            } elseif (isset($data['minimum_budget_amount'])) {
1993                TblTradeAlignmentSettings::where('id', $id)->update($data);
1994            }
1995
1996            $result = $this->list_trade_alignment_settings($companyId);
1997
1998            return response([
1999                'message' => 'OK',
2000                'data' => $result->original['data'],
2001            ]);
2002
2003        } catch (\Exception $e) {
2004            report(AppException::fromException($e, 'UPDATE_TRADE_ALIGNMENT_SETTINGS_EXCEPTION'));
2005
2006            return response(['message' => 'KO', 'error' => $e->getMessage()]);
2007        }
2008    }
2009}