Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
0.00%
0 / 1034
0.00%
0 / 20
 CRAP
0.00%
0 / 1
OngoingJobs
0.00%
0 / 1034
0.00%
0 / 20
 56406
0.00%
0 / 1
 __construct
0.00%
0 / 13
0.00%
0 / 1
 20
 list_ongoing_jobs
0.00%
0 / 245
0.00%
0 / 1
 4830
 approve_job
0.00%
0 / 42
0.00%
0 / 1
 42
 reject_job
0.00%
0 / 42
0.00%
0 / 1
 42
 move_to_ongoing_jobs
0.00%
0 / 68
0.00%
0 / 1
 42
 send_mail
0.00%
0 / 89
0.00%
0 / 1
 1056
 bulk_update_ongoing_job
0.00%
0 / 33
0.00%
0 / 1
 20
 delete_ongoing_job
0.00%
0 / 25
0.00%
0 / 1
 20
 download_ongoing_jobs
0.00%
0 / 125
0.00%
0 / 1
 210
 update_ongoing_job
0.00%
0 / 36
0.00%
0 / 1
 90
 get_dates
0.00%
0 / 16
0.00%
0 / 1
 12
 get_job
0.00%
0 / 9
0.00%
0 / 1
 6
 list_job_analytics
0.00%
0 / 64
0.00%
0 / 1
 600
 get_years
0.00%
0 / 21
0.00%
0 / 1
 12
 list_margin_jobs_analytics
0.00%
0 / 135
0.00%
0 / 1
 1560
 get_total_jobs_in_red
0.00%
0 / 31
0.00%
0 / 1
 12
 get_active_job_dates
0.00%
0 / 25
0.00%
0 / 1
 12
 update_ongoing_jobs_month_change
0.00%
0 / 1
0.00%
0 / 1
 2
 update_ongoing_jobs_month_change_manual
0.00%
0 / 8
0.00%
0 / 1
 12
 get_revenue_per_month
0.00%
0 / 6
0.00%
0 / 1
 6
1<?php
2
3namespace App\Http\Controllers;
4
5use App\Models\TblBudgetTypes;
6use App\Models\TblCompanies;
7use App\Models\TblCompanyUsers;
8use App\Models\Client;
9use App\Models\TblOngoingJobs;
10use App\Models\TblProjectTypes;
11use App\Models\TblQuotations;
12use App\Models\TblRevenuePerMonth;
13use App\Models\TblUsers;
14use Illuminate\Http\Request;
15use Illuminate\Support\Facades\App;
16use Illuminate\Support\Facades\Cache;
17use Illuminate\Support\Facades\DB;
18use PhpOffice\PhpSpreadsheet\IOFactory;
19use PhpOffice\PhpSpreadsheet\Spreadsheet;
20use PhpOffice\PhpSpreadsheet\Style;
21use Illuminate\Support\Facades\Log;
22use Illuminate\Contracts\Routing\ResponseFactory;
23use Illuminate\Http\Response;
24use App\Exceptions\AppException;
25
26class OngoingJobs extends Controller
27{
28    private $locale;
29    private $userId;
30    private $region;
31    private $companyIds;
32    private $companyId;
33
34    public function __construct(private readonly Quotations $quotationsController){
35        $this->locale = request()->header('Locale-Id');
36        $this->userId = request()->header('User-Id');
37        $this->region = request()->header('Region');
38
39        App::setLocale($this->locale);
40
41        $this->companyIds = [];
42
43        if($this->region != null && $this->region != "" && $this->region != "All"){
44            $this->region = urldecode((string) $this->region);
45
46            $query = 'SELECT 
47                        b.company_id
48                    FROM 
49                        tbl_company_users a 
50                        LEFT JOIN tbl_companies b ON a.company_id = b.company_id 
51                    WHERE 
52                        a.user_id = ?
53                        AND b.region = ?';
54
55            $this->companyIds = DB::select($query, [intval($this->userId), $this->region]);
56
57            $this->companyIds = collect($this->companyIds)->pluck('company_id')->toArray();
58        } else {
59            $this->companyIds = TblCompanyUsers::where('user_id', $this->userId)->pluck('company_id')->all();
60        }
61
62        $this->companyId = implode(',', $this->companyIds);
63    }
64
65    function list_ongoing_jobs(Request $request): ResponseFactory|Response{
66        
67        try {
68
69            $data = $request->all();
70            $companyId = addslashes((string) $data['company_id']);
71            $userId = addslashes((string) $data['user_id']);
72            $filter = $data['filterModel'];
73            $sort = $data['sortModel'];
74            $result = [];
75            $subquery = "";
76            $where = "";
77            $having = "";
78            $orderBy = "";
79            $start = addslashes((string) $data['start']);
80            $end = addslashes((string) $data['end']);
81            $totalRowCount = 0;
82
83            $filterType = [
84                'contains' => "LIKE '%[value]%'",
85                'notContains' => "NOT LIKE '%[value]%'",
86                'equals' => "= '[value]'",
87                'notEqual' => "<> '[value]'",
88                'startsWith' => "LIKE '[value]%'",
89                'endsWith' => "LIKE '%[value]'",
90                'blank' => "IS NULL",
91                'notBlank' => "IS NOT NULL",
92                'lessThan' => "< [value]",
93                'lessThanOrEqual' => "<= [value]",
94                'greaterThan' => "> [value]",
95                'greaterThanOrEqual' => ">= [value]",
96                'inRange' => "BETWEEN [value1] AND [value2]"
97            ];
98
99            if(isset($data['ids']) && count($data['ids']) > 0){
100                $quoteIds = implode(",", $data['ids']);
101                $where = " a.id IN ({$quoteIds}) ";
102            }
103
104            if (isset($data['ids_not_in']) && count($data['ids_not_in']) > 0) {
105                $quoteIds = implode(',', $data['ids_not_in']);
106                $where = " a.id NOT IN ({$quoteIds}) ";
107            }
108
109            if ($companyId != 0) {
110                if ($where != '') {
111                    $where .= " AND a.company_id = {$companyId} ";
112                } else {
113                    $where .= " a.company_id = {$companyId} ";
114                }
115            } else {
116                if ($where != '') {
117                    $where .= " AND a.company_id IN ({$this->companyId}) ";
118                } else {
119                    $where .= " a.company_id IN ({$this->companyId}) ";
120                }
121            }
122
123            $where = "WHERE {$where}";
124
125            $matchScoreCol = '';
126            $matchScoreOrderBy = '';
127
128            if (isset($data['searchText']) && $data['searchText'] != null) {
129
130                $availableParameters = [
131                    'a.quotation_id',
132                    'c.name',
133                    's.name',
134                    'd.name',
135                    'a.quote_id',
136                    'a.company_id',
137                    'a.customer_type_id',
138                    'a.budget_type_id',
139                    'a.order_number',
140                    'a.client',
141                    'a.issue_date',
142                    'a.acceptance_date',
143                    'a.amount',
144                    'a.created_by',
145                    'a.created_at',
146                    'a.updated_by',
147                    'a.updated_at',
148                    'a.invoice_amount',
149                    'a.responsible_for_work',
150                    'a.expected_start_date_of_work',
151                    'a.actual_job_start_date',
152                    'a.actual_end_date',
153                    'a.expected_completion_date',
154                    'a.work_status_id',
155                    'e.name',
156                ];
157
158                $searchText = addslashes((string) $data['searchText']);
159                $searchTextArray = explode(" ", $searchText);
160
161                $searchArray = [];
162                $splitSearchArray = [];
163                $matchScoreArray = [];
164                $sc = 1;
165                foreach ($availableParameters as $field) {
166                    if ($field == 'a.client' || $field == 'a.amount' || $field == 'a.created_at') {
167                        $sc = 3;
168                    } elseif ($field == 'a.acceptance_date') {
169                        $sc = 2;
170                    } else {
171                        $sc = 1;
172                    }
173
174                    $l = "{$field} LIKE '%{$searchText}%'";
175                    $d = "IFNULL((LENGTH(LOWER({$field})) - LENGTH(REPLACE(LOWER({$field}), LOWER('{$searchText}'), ''))) / LENGTH(LOWER('{$searchText}')), 0) * {$sc}";
176
177                    if (count($searchTextArray) > 1) {
178                        foreach ($searchTextArray as $word) {
179                            if (! is_numeric($word)) {
180                                $d .= " + IFNULL((LENGTH(LOWER({$field})) - LENGTH(REPLACE(LOWER({$field}), LOWER('{$word}'), ''))) / LENGTH(LOWER('{$word}')), 0) * {$sc}";
181                            }
182                        }
183                    }
184
185                    array_push($matchScoreArray, $d);
186
187                    if (is_numeric($searchText)) {
188                        array_push($searchArray, "({$l} OR {$field} = CAST('{$searchText}' AS UNSIGNED))");
189                    } else {
190                        array_push($searchArray, "({$l} OR DATE_FORMAT({$field}, '%d/%m/%Y') = DATE_FORMAT(STR_TO_DATE('{$searchText}', '%d/%m/%Y'), '%d/%m/%Y'))");
191                    }
192
193                    if (count($searchTextArray) > 1) {
194                        foreach ($searchTextArray as $word) {
195
196                            $l = "{$field} LIKE '%{$word}%'";
197
198                            if (is_numeric($word)) {
199                                array_push($splitSearchArray, "{$l} OR {$field} = CAST('{$word}' AS UNSIGNED)");
200                            } else {
201                                array_push($splitSearchArray, "{$l} OR DATE_FORMAT({$field}, '%d/%m/%Y') = DATE_FORMAT(STR_TO_DATE('{$word}', '%d/%m/%Y'), '%d/%m/%Y')");
202                            }
203                        }
204                    }
205
206                    $sc = 1;
207                }
208
209                if (count($splitSearchArray) > 0) {
210                    $splitSearchArray = implode(' OR ', $splitSearchArray);
211                    $splitSearchArray = " OR ({$splitSearchArray}) ";
212                } else {
213                    $splitSearchArray = '';
214                }
215
216                $searchArray = implode(' OR ', $searchArray);
217                $matchScoreArray = implode(',', $matchScoreArray);
218                $matchScoreCol = ", GREATEST({$matchScoreArray}) match_score";
219                $matchScoreOrderBy = 'match_score DESC,';
220                $where .= " AND ({$searchArray} {$splitSearchArray})";
221            }
222
223            if (count($sort) > 0) {
224                $field = $sort[0]['colId'];
225                $sortBy = $sort[0]['sort'];
226                
227                if(str_contains((string) $field, "translate")){
228                    $field = str_replace("_translate", "", $field);
229                }else{
230                    if($field == "client_type"){
231                        $field = "c.name";
232                    }elseif($field == "segment"){
233                        $field = "s.name";
234                    }elseif($field == "type"){
235                        $field = "d.name";
236                    }elseif($field == "amount"){
237                        $field = "CAST(a.amount AS DOUBLE)";
238                    }elseif($field == "invoice_amount"){
239                        $field = "CAST(a.invoice_amount AS DOUBLE)";
240                    }elseif($field == "quote_id"){
241                        $field = "CAST(a.quote_id AS DOUBLE)";
242                    }
243                }
244
245                if ($matchScoreOrderBy) {
246                    $matchScoreOrderBy = ', match_score DESC';
247                }
248
249                $orderBy = " ORDER BY {$field} {$sortBy} {$matchScoreOrderBy}";
250            } else {
251                $orderBy = " ORDER BY {$matchScoreOrderBy} a.id DESC ";
252            }
253
254            foreach ($filter as $key => $data) {                
255                if(str_contains((string) $key, "translate")){
256
257                    $field = str_replace('_translate', '', $key);
258
259                    if ($field == 'created_at') {
260                        $field = 'a.created_at';
261                    }
262
263                    $whereDates = '';
264                    $z = 0;
265                    foreach ($data['filters'] as $yearKey => $yearData) {
266                        $yearsMonths = [];
267                        $yearsWeeks = [];
268                        
269                        if($z > 0){
270                            $whereDates .= " OR (YEAR($field) = {$yearKey} ";
271                        } else {
272                            $whereDates .= " (YEAR($field) = {$yearKey} ";
273                        }
274
275                        for ($i = 0; $i < count($yearData['months']); $i++) {
276                            if ($yearData['months'][$i]['isChecked']) {
277                                array_push($yearsMonths, $yearData['months'][$i]['value']);
278                            }
279                        }
280
281                        $yearsMonths = implode("','", $yearsMonths);
282                        $whereDates .= " AND (MONTH({$field}) IN ('{$yearsMonths}')";
283
284                        for ($i = 0; $i < count($yearData['weeks']); $i++) {
285                            if ($yearData['weeks'][$i]['isChecked']) {
286                                array_push($yearsWeeks, $yearData['weeks'][$i]['value']);
287                            }
288                        }
289
290                        $yearsWeeks = implode("','", $yearsWeeks);
291                        if ($yearsWeeks != '') {
292                            $whereDates .= " OR WEEK({$field}) IN ('{$yearsWeeks}') ";
293                        }
294
295                        $whereDates .= ')) ';
296                        $z++;
297                    }
298
299                    $where .= " AND ({$whereDates}) ";
300                }else{
301                    if($data['filterType'] == 'number'){
302                        if(array_key_exists('operator', $data)){
303                            if($data['condition1']['type'] != 'blank' && $data['condition2']['type'] != 'notBlank'){
304                                $data['condition1']['filter'] = addslashes((string) $data['condition1']['filter']);
305                                $data['condition2']['filter'] = addslashes((string) $data['condition2']['filter']);
306
307                                if($data['condition1']['type'] == 'inRange'){
308                                    $data['condition1']['filterTo'] = addslashes((string) $data['condition1']['filterTo']);
309                                    $inRange = str_replace("[value1]", $data['condition1']['filter'], $filterType['inRange']);
310                                    $val1 = str_replace("[value2]", $data['condition1']['filterTo'], $inRange);
311                                }else{
312                                    $val1 = str_replace("[value]", $data['condition1']['filter'], $filterType[$data['condition1']['type']]);
313                                }
314    
315                                if($data['condition2']['type'] == 'inRange'){
316                                    $data['condition2']['filterTo'] = addslashes((string) $data['condition2']['filterTo']);
317                                    $inRange = str_replace("[value1]", $data['condition2']['filter'], $filterType['inRange']);
318                                    $val2 = str_replace("[value2]", $data['condition2']['filterTo'], $inRange);
319                                }else{
320                                    $val2 = str_replace("[value]", $data['condition2']['filter'], $filterType[$data['condition2']['type']]);
321                                }
322
323                            } else {
324                                $val1 = $filterType[$data['condition1']['type']];
325                                $val2 = $filterType[$data['condition2']['type']];
326                            }
327
328                            $where .= " AND a.{$key} {$val1} {$data['operator']} a.{$key} {$val2} ";
329                        }else{
330                            if($data['type'] != 'blank' && $data['type'] != 'notBlank'){
331                                $data['filter'] = addslashes((string) $data['filter']);
332                                
333                                if($data['type'] == 'inRange'){
334                                    $data['filterTo'] = addslashes((string) $data['filterTo']);
335                                    $inRange = str_replace("[value1]", $data['filter'], $filterType['inRange']);
336                                    $val = str_replace("[value2]", $data['filterTo'], $inRange);
337                                }else{
338                                    $val = str_replace("[value]", $data['filter'], $filterType[$data['type']]);
339                                }
340                            } else {
341                                $val = $filterType[$data['type']];
342                            }
343
344                            $where .= " AND a.{$key} {$val} ";
345                        }
346                    }
347
348                    if($data['filterType'] == 'text'){
349                        if(array_key_exists('operator', $data)){
350                            if($data['condition1']['type'] != 'blank' && $data['condition2']['type'] != 'notBlank'){
351                                $data['condition1']['filter'] = addslashes((string) $data['condition1']['filter']);
352                                $val1 = str_replace("[value]", $data['condition1']['filter'], $filterType[$data['condition1']['type']]);
353                            }
354
355                            if($data['condition2']['type'] != 'blank' && $data['condition2']['type'] != 'notBlank'){
356                                $data['condition2']['filter'] = addslashes((string) $data['condition2']['filter']);
357                                $val2 = str_replace("[value]", $data['condition2']['filter'], $filterType[$data['condition2']['type']]);
358                            }
359
360                            $where .= " AND {$key} {$val1} {$data['operator']} {$key} {$val2} ";
361                        }else{
362                            if($data['type'] != 'blank' && $data['type'] != 'notBlank'){
363                                $data['filter'] = addslashes((string) $data['filter']);
364                                $val = str_replace("[value]", $data['filter'], $filterType[$data['type']]);
365                            }else{
366                                $val = $filterType[$data['type']];
367                            }
368
369                            $where .= " AND {$key} {$val} ";
370                        }
371                    }
372
373                    if ($data['filterType'] == 'set') {
374                        $statusName = $key;
375
376                        if ($key == 'client_type') {
377                            $statusName = 'c.name';
378                        } elseif ($key == 'segment') {
379                            $statusName = 's.name';
380                        } elseif ($key == 'type') {
381                            $statusName = 'd.name';
382                        } elseif ($key == 'work_status') {
383                            $statusName = 'e.name';
384                        } elseif ($key == 'created_by') {
385                            $statusName = 'a.created_by';
386                        }
387
388                        $val = implode("','", $data['values']);
389
390                        if (in_array(null, $data['values'], true)) {
391                            $where .= " AND ({$statusName} IN ('{$val}') OR {$statusName} IS NULL) ";
392                        } else {
393                            $where .= " AND {$statusName} IN ('{$val}') ";
394                        }
395                    }
396                }
397            }
398
399            $offset = $start;
400            $limit = $end - $start;
401
402            $subquery = ",(SELECT can_write FROM tbl_company_users WHERE company_id = a.company_id AND user_id = {$userId}) can_write";
403
404            $query = "SELECT 
405                        a.id,
406                        a.quotation_id, 
407                        b.name company_name, 
408                        c.name client_type,
409                        s.name segment,
410                        s.segment_id,
411                        d.name 'type',
412                        a.quote_id, 
413                        a.company_id,
414                        a.customer_type_id,
415                        a.budget_type_id,
416                        a.order_number,
417                        a.client,
418                        a.issue_date,
419                        a.acceptance_date, 
420                        DATE_FORMAT(a.issue_date, '%d/%m/%Y') issue_date_translate,
421                        DATE_FORMAT(a.acceptance_date, '%d/%m/%Y') acceptance_date_translate,
422                        a.amount, 
423                        a.created_by, 
424                        a.created_at,
425                        a.updated_by,
426                        a.updated_at,
427                        a.invoice_amount,
428                        a.responsible_for_work,
429                        a.expected_start_date_of_work,
430                        a.actual_job_start_date, 
431                        a.actual_end_date, 
432                        a.expected_completion_date, 
433                        a.comments,
434                        DATE_FORMAT(a.expected_start_date_of_work, '%d/%m/%Y') expected_start_date_of_work_translate,
435                        DATE_FORMAT(a.actual_job_start_date, '%d/%m/%Y') actual_job_start_date_translate,
436                        DATE_FORMAT(a.actual_end_date, '%d/%m/%Y') actual_end_date_translate,
437                        DATE_FORMAT(a.expected_completion_date, '%d/%m/%Y') expected_completion_date_translate,
438                        DATE_FORMAT(a.created_at, '%d/%m/%Y') created_at_translate,
439                        CASE 
440                            WHEN 
441                                DATE_FORMAT(a.expected_completion_date, '%Y-%m-%d') < DATE_FORMAT(NOW(), '%Y-%m-%d') AND a.work_status_id NOT IN (3, 5)
442                            THEN 1
443                            ELSE 0
444                        END is_red_expected_completion_date,
445                        CASE 
446                            WHEN 
447                                DATE_FORMAT(a.expected_start_date_of_work, '%Y-%m-%d') < DATE_FORMAT(NOW(), '%Y-%m-%d') AND a.work_status_id = 2
448                            THEN 1
449                            ELSE 0
450                        END is_red_expected_start_date_of_work,
451                        CASE 
452                            WHEN 
453                                DATE_FORMAT(a.expected_completion_date, '%Y-%m-%d') < DATE_FORMAT(NOW(), '%Y-%m-%d')
454                                AND a.work_status_id NOT IN (3, 5) 
455                            THEN 1
456                            ELSE 0
457                        END is_red,
458                        CASE 
459                            WHEN 
460                                (g.amount IS NULL OR g.amount = '')
461                                AND (a.pending_to_be_invoiced IS NOT NULL AND a.pending_to_be_invoiced <> '' AND a.pending_to_be_invoiced <> 0)
462                                AND DATE_FORMAT(NOW(), '%Y-%m-%d') BETWEEN DATE_FORMAT(CASE WHEN a.work_status_id = 3 OR a.work_status_id = 5 THEN a.actual_job_start_date ELSE a.expected_start_date_of_work END, '%Y-%m-%d') 
463                                AND DATE_FORMAT(CASE WHEN a.work_status_id = 3 OR a.work_status_id = 5 THEN a.actual_end_date ELSE a.expected_completion_date END , '%Y-%m-%d') 
464                            THEN 1
465                            ELSE 0
466                        END is_red_invoiced,
467                        a.work_status_id,
468                        e.name work_status,
469                        a.cost_for_client,
470                        a.people_assigned_to_the_job,
471                        a.duration_of_job_in_days,
472                        a.estimated_cost_of_materials,
473                        a.for_approval,
474                        a.cost_of_labor,
475                        a.total_cost_of_job,
476                        a.invoice_margin,
477                        a.margin_for_the_company,
478                        a.margin_on_invoice_per_day_per_worker,
479                        a.revenue_per_date_per_worked,
480                        a.gross_margin,
481                        a.labor_percentage,
482                        a.pending_to_be_invoiced,
483                        g.amount to_be_invoiced_this_month,
484                        a.to_be_invoiced_after_this_month,
485                        a.approved_at,
486                        a.approved_by
487                        {$matchScoreCol}
488                        {$subquery}
489                    FROM 
490                        tbl_ongoing_jobs a 
491                        LEFT JOIN tbl_companies b ON a.company_id = b.company_id
492                        LEFT JOIN tbl_customer_types c ON a.customer_type_id = c.customer_type_id
493                        LEFT JOIN tbl_segments s ON a.segment_id = s.segment_id
494                        LEFT JOIN tbl_budget_types d ON a.budget_type_id = d.budget_type_id
495                        LEFT JOIN tbl_work_status e ON a.work_status_id = e.work_status_id
496                        LEFT JOIN tbl_revenue_per_month g ON a.quotation_id = g.quotation_id AND g.month = MONTH(NOW()) AND g.year = YEAR(NOW())
497                    {$where}
498                    {$orderBy}
499                    LIMIT {$offset}, {$limit}
500                    ";
501
502            $value = Cache::get(base64_encode($query));
503
504            if (! $value) {
505                $result = DB::select($query);
506
507                Cache::put(base64_encode($query), $result, 600);
508            } else {
509                $result = $value;
510            }
511
512            $totalQuery = "SELECT 
513                            COUNT(1) totalRowCount
514                        FROM 
515                            tbl_ongoing_jobs a 
516                            LEFT JOIN tbl_companies b ON a.company_id = b.company_id 
517                            LEFT JOIN tbl_customer_types c ON a.customer_type_id = c.customer_type_id 
518                            LEFT JOIN tbl_segments s ON a.segment_id = s.segment_id
519                            LEFT JOIN tbl_budget_types d ON a.budget_type_id = d.budget_type_id 
520                            LEFT JOIN tbl_work_status e ON a.work_status_id = e.work_status_id
521                        {$where}";
522
523            $value = Cache::get(base64_encode($totalQuery));
524
525            if (! $value) {
526                $countQuery = DB::select($totalQuery);
527
528                Cache::put(base64_encode($totalQuery), $countQuery, 600);
529            } else {
530                $countQuery = $value;
531            }
532
533            return response([
534                'message' => 'OK',
535                'data' => $result,
536                'totalRowCount' => $countQuery[0]->totalRowCount,
537            ]);
538
539        } catch (\Exception $e) {
540            report(AppException::fromException($e, 'LIST_ONGOING_JOBS_EXCEPTION'));
541            return response(['message' => 'KO', 'error' => $e->getMessage()]);
542        }
543    }
544
545    function approve_job($id): ResponseFactory|Response{
546
547        try {
548            
549            $id = addslashes((string) $id);
550
551            $result = TblOngoingJobs::where('id', $id)->first();
552            $company = TblCompanies::where('company_id', $result->company_id)->first();
553            $budgetType = TblBudgetTypes::where('budget_type_id', $result->budget_type_id)->first();
554
555            $order = new Quotations;
556
557            if($result->created_by != $result->responsible_for_work){
558                $creatorAndResponsibleForWork = [$result->created_by, $result->responsible_for_work];
559                foreach ($creatorAndResponsibleForWork as $name) {
560                    $user = TblUsers::where('name', $name)->first();
561                    if ($user) {
562                        $order->send_approved_notification(
563                            $user->id,
564                            $user->name,
565                            $user->email,
566                            $company->name,
567                            $budgetType->name,
568                            $result->amount,
569                            $id,
570                            $result->quote_id,
571                            $company->company_id,
572                            'ongoing-jobs'
573                        );
574                    }
575                }
576            } else {
577                $user = TblUsers::where('name', $result->created_by)->first();
578                if ($user) {
579                    $order->send_approved_notification(
580                        $user->id,
581                        $user->name,
582                        $user->email,
583                        $company->name,
584                        $budgetType->name,
585                        $result->amount,
586                        $id,
587                        $result->quote_id,
588                        $company->company_id,
589                        'ongoing-jobs'
590                    );
591                }
592            }
593
594            TblOngoingJobs::where('id', $id)->update(['for_approval' => null]);
595
596            Cache::flush();
597
598            return response(['message' => 'OK']);
599
600        } catch (\Exception $e) {
601            report(AppException::fromException($e, 'APROVE_JOB_EXCEPTION'));
602            return response(['message' => 'KO', 'error' => $e->getMessage()]);
603        }
604
605    }
606
607    function reject_job($id): ResponseFactory|Response{
608
609        try {
610            
611            $id = addslashes((string) $id);
612
613            $result = TblOngoingJobs::where('id', $id)->first();
614            $company = TblCompanies::where('company_id', $result->company_id)->first();
615            $budgetType = TblBudgetTypes::where('budget_type_id', $result->budget_type_id)->first();
616
617            $order = new Quotations;
618
619            if($result->created_by != $result->commercial){
620                $creatorAndResponsibleForWork = [$result->created_by, $result->responsible_for_work];
621                foreach ($creatorAndResponsibleForWork as $name) {
622                    $user = TblUsers::where('name', $name)->first();
623                    if ($user) {
624                        $order->send_rejected_notification(
625                            $user->id,
626                            $user->name,
627                            $user->email,
628                            $company->name,
629                            $budgetType->name,
630                            $result->amount,
631                            $id,
632                            $result->quote_id,
633                            $company->company_id,
634                            'ongoing-jobs'
635                        );
636                    }
637
638                }
639            } else {
640                $user = TblUsers::where('name', $result->created_by)->first();
641                if ($user) {
642                    $order->send_rejected_notification(
643                        $user->id,
644                        $user->name,
645                        $user->email,
646                        $company->name,
647                        $budgetType->name,
648                        $result->amount,
649                        $id,
650                        $result->quote_id,
651                        $company->company_id,
652                        'ongoing-jobs'
653                    );
654                }
655            }
656
657            TblOngoingJobs::where('id', $id)->update(['for_approval' => 2]);
658
659            Cache::flush();
660
661            return response(['message' => 'OK']);
662
663        } catch (\Exception $e) {
664            report(AppException::fromException($e, 'REJECCT_JOB_EXCEPTION'));
665            return response(['message' => 'KO', 'error' => $e->getMessage()]);
666        }
667
668    }
669
670    function move_to_ongoing_jobs(Request $request, $id): ResponseFactory|Response{
671
672        try {
673            
674            $id = addslashes((string) $id);
675            $data = $request->all();
676            $userId = addslashes((string) $data['user_id']);
677            unset($data['user_id']);
678
679            $jobId = null;
680
681            $ongoingJob = TblOngoingJobs::where('quotation_id', $id)->first();
682            $quotation = TblQuotations::where('id', $id)->first();
683
684            if (empty($quotation->acceptance_date)) {
685                $quotation->acceptance_date = date('Y-m-d H:i:s');
686
687                TblQuotations::where('id', $id)->update(
688                    [
689                        'acceptance_date' => $quotation->acceptance_date,
690                        'budget_status_id' => 3,
691                        'updated_at' => date('Y-m-d H:i:s'),
692                        'updated_by' => $data['created_by']
693                    ]
694                );
695
696                // Promote lead to general when quotation is accepted
697                if ($quotation->client_id) {
698                    Client::where('id', $quotation->client_id)
699                        ->where('client_type_id', Client::TYPE_LEAD)
700                        ->update(['client_type_id' => Client::TYPE_GENERAL]);
701                }
702            }
703
704            $job = [
705                'quotation_id' => $id,
706                'quote_id' => $quotation->quote_id,
707                'company_id' => $quotation->company_id,
708                'customer_type_id' => $quotation->customer_type_id,
709                'segment_id' => $quotation->segment_id,
710                'budget_type_id' => $quotation->budget_type_id,
711                'order_number' => $quotation->order_number,
712                'client' => $quotation->client,
713                'issue_date' => $quotation->issue_date,
714                'acceptance_date' => $quotation->acceptance_date,
715                'amount' => $quotation->amount,
716                'cost_for_client' => $quotation->amount,
717                'comments' => $quotation->last_follow_up_comment
718            ];
719
720            if ($quotation->budget_margin_enabled > 0) {
721                $job['people_assigned_to_the_job'] = $quotation->people_assigned_to_the_job;
722                $job['duration_of_job_in_days'] = $quotation->duration_of_job_in_days;
723                $job['estimated_cost_of_materials'] = $quotation->estimated_cost_of_materials;
724                $job['cost_of_labor'] = $quotation->cost_of_labor;
725                $job['total_cost_of_job'] = $quotation->total_cost_of_job;
726                $job['invoice_margin'] = $quotation->invoice_margin;
727                $job['margin_for_the_company'] = $quotation->margin_for_the_company;
728                $job['margin_on_invoice_per_day_per_worker'] = $quotation->margin_on_invoice_per_day_per_worker;
729                $job['revenue_per_date_per_worked'] = $quotation->revenue_per_date_per_worked;
730                $job['gross_margin'] = $quotation->gross_margin;
731                $job['labor_percentage'] = $quotation->labor_percentage;
732            }
733
734            $username = null;
735            $isAdd = 0;
736            if (empty($ongoingJob)) {
737                $isAdd = 1;
738                $job['created_by'] = $data['created_by'];
739                $job['to_be_invoiced_this_month'] = $quotation->amount;
740                $job['pending_to_be_invoiced'] = 0;
741                $job['to_be_invoiced_after_this_month'] = 0;
742
743                $result = TblOngoingJobs::create($job);
744                $jobId = $result->id;
745
746                // if(empty($quotation->approved_at)){
747                //     $this->send_mail($quotation, $job, $jobId, $userId, $isAdd, $data['created_by']);
748                // }
749            } else {
750                $jobId = $ongoingJob->id;
751                $job['updated_by'] = $data['created_by'];
752                $job['updated_at'] = date('Y-m-d H:i:s');
753                $result = TblOngoingJobs::where('id', $jobId)->update($job);
754
755                // if(empty($quotation->approved_at) || $ongoingJob->invoice_margin != $quotation->invoice_margin){
756                //     $this->send_mail($quotation, $job, $jobId, $userId, $isAdd, $data['created_by']);
757                // }
758            }
759
760            $this->quotationsController->addUpdateLog($id, $userId, 'move_to_ongoing_job', null, null, 4);
761
762            Cache::flush();
763
764            return response(['message' => 'OK', 'id' => $jobId]);
765
766        } catch (\Exception $e) {
767            report(AppException::fromException($e, 'MOVE_TO_ONGOING_JOB_EXCEPTION'));
768            return response(['message' => 'KO', 'error' => $e->getMessage()]);
769        }
770    }
771
772    function send_mail($quotation, array $data, $jobId, $userId, $isAdd, $createdBy): void{
773
774        $g = false;
775        if ($isAdd == 0) {
776            if (isset($data['cost_for_client']) || isset($data['budget_type_id']) || isset($data['customer_type_id']) || isset($data['invoice_margin'])) {
777                if ($data['cost_for_client'] != $quotation->cost_for_client || $data['budget_type_id'] != $quotation->budget_type_id
778                || $data['customer_type_id'] != $quotation->customer_type_id || $data['invoice_margin'] != $quotation->invoice_margin) {
779                    $g = true;
780                }
781            }
782        } else {
783            $g = true;
784        }
785
786        if ($g) {
787            $company = TblCompanies::where('company_id', $quotation->company_id)->first();
788
789            if ($company) {
790
791                $n = 0;
792                $invoiceMargin = 0;
793                $minimumMargin = 0;
794
795                $project = TblProjectTypes::where('company_id', $company->company_id)->where('budget_type_id', $data['budget_type_id'])->first();
796                $customerTypeIds = [];
797
798                if($project){
799                    if(!empty($project->customer_type_ids)){
800                        $customerTypeIds = array_map(intval(...), explode(',', (string) $project->customer_type_ids));
801                    }
802                    if ($project->minimum_order_size != null && in_array($data['customer_type_id'], $customerTypeIds)) {
803                        if ($data['cost_for_client'] >= $project->minimum_order_size) {
804                            $data['for_approval'] = 1;
805                            $n = 1;
806                        }
807                    }    
808                }else{
809                    if(!empty($company->customer_type_ids)){
810                        $customerTypeIds = array_map(intval(...), explode(',', (string) $company->customer_type_ids));
811                    }
812                    if ($company->minimum_order_size != null && in_array($data['customer_type_id'], $customerTypeIds)) {
813                        if ($data['cost_for_client'] >= $company->minimum_order_size) {
814                            $data['for_approval'] = 1;
815                            $n = 1;
816                        }
817                    }
818                }
819
820                $costOfLabor = $data['cost_of_labor'] ?? 0;
821                $totalCostOfJob = $data['total_cost_of_job'] ?? 0;
822
823                if ($totalCostOfJob > 0) {
824                    $invoiceMargin = $data['invoice_margin'] ?? 0;
825                }
826
827                $project = TblProjectTypes::where('company_id', $quotation->company_id)->where('budget_type_id', $quotation->budget_type_id)->first();
828                $minimumMargin = $company->minimum_margin;
829                $minimumOrderSize = $company->minimum_order_size;
830
831                if(!empty($company->customer_type_ids)){
832                    $customerTypeIds = array_map(intval(...), explode(',', (string) $company->customer_type_ids));    
833                }
834
835                if ($project) {
836                    $minimumMargin = $project->minimum_margin;
837                    $minimumOrderSize = $project->minimum_order_size;
838                    if(!empty($project->customer_type_ids)){
839                        $customerTypeIds = array_map(intval(...), explode(',', (string) $project->customer_type_ids));
840                    }
841                }
842
843                if ($invoiceMargin < $minimumMargin && $invoiceMargin != null && $invoiceMargin != 0) {
844                    if (in_array($data['customer_type_id'], $customerTypeIds)) {
845                        $data['for_approval'] = 1;
846                        $n = 2;
847                    }
848                }
849
850                $order = new Quotations;
851
852                if ($n == 1) {
853                    $order->send_approval_notification(
854                        $data['cost_for_client'],
855                        $quotation->budget_type_id,
856                        $quotation->customer_type_id,
857                        $minimumOrderSize,
858                        $quotation->quote_id,
859                        $jobId,
860                        $quotation->name,
861                        $createdBy,
862                        $userId,
863                        $isAdd,
864                        null,
865                        $company->company_id,
866                        'ongoing-jobs',
867                        0,
868                        null,
869                        $n
870                    );
871                }
872
873                if ($n == 2) {
874                    $order->send_approval_margin_notification(
875                        $data['cost_for_client'],
876                        $quotation->budget_type_id,
877                        $quotation->customer_type_id,
878                        $minimumMargin,
879                        $quotation->quote_id,
880                        $jobId,
881                        $company->name,
882                        $createdBy,
883                        $userId,
884                        $isAdd,
885                        null,
886                        $invoiceMargin,
887                        $company->company_id,
888                        'ongoing-jobs'
889                    );
890                }
891
892                if (isset($data['for_approval'])) {
893                    TblOngoingJobs::where('id', $jobId)->update(
894                        [
895                            'for_approval' => $data['for_approval']
896                        ]
897                    );
898                }
899            }
900        }
901    }
902
903    function bulk_update_ongoing_job(Request $request): ResponseFactory|Response{
904
905        try {
906
907            $data = $request->all();
908
909            $r = new Request([
910                'filterModel' => $data['filterModel'],
911                'sortModel' => $data['sortModel'],
912                'start' => 0,
913                'end' => 999999999,
914                'company_id' => $data['company_id'],
915                'user_id' => $data['user_id'],
916                'ids' => $data['ids'],
917                'searchText' => $data['searchText'],
918                'ids_not_in' => $data['ids_not_in'],
919            ]);
920
921            $listOngoingJobs = $this->list_ongoing_jobs($r);
922            $d = $listOngoingJobs->original['data'];
923
924            if (count($d) > 0) {
925                unset($data['filterModel']);
926                unset($data['sortModel']);
927                unset($data['start']);
928                unset($data['end']);
929                unset($data['company_id']);
930                unset($data['user_id']);
931                unset($data['ids']);
932                unset($data['searchText']);
933                unset($data['ids_not_in']);
934    
935                $result = [];
936                for ($i = 0; $i < count($d); $i++) { 
937                    array_push($result, $d[$i]->id);
938                }
939
940                TblOngoingJobs::whereIn('id', $result)->update($data);
941
942                Cache::flush();
943            }
944
945            return response(['message' => 'OK', $data]);
946
947        } catch (\Exception $e) {
948            report(AppException::fromException($e, 'BULK_UPDATE_ONGOING_JOB_EXCEPTION'));
949            return response(['message' => 'KO', 'error' => $e->getMessage()]);
950        }
951
952    }
953
954    public function delete_ongoing_job(Request $request): ResponseFactory|Response{
955
956        try {
957
958            $data = $request->all();
959            $result = [];
960
961            $r = new Request([
962                'filterModel' => $data['filterModel'],
963                'sortModel' => $data['sortModel'],
964                'start' => 0,
965                'end' => 999999999,
966                'company_id' => $data['company_id'],
967                'user_id' => $data['user_id'],
968                'ids' => $data['ids'],
969                'searchText' => $data['searchText'],
970                'ids_not_in' => $data['ids_not_in'],
971            ]);
972
973            $result = $this->list_ongoing_jobs($r);
974            $result = $result->original['data'];
975
976            $outputArray = [];
977
978            foreach ($result as $item) {
979                if (isset($item->id)) {
980                    $outputArray[] = $item->id;
981                }
982            }
983
984            TblOngoingJobs::whereIn('id', $outputArray)->delete();
985
986            Cache::flush();
987
988            return response(['message' => 'OK', 'data' => $result]);
989
990        } catch (\Exception $e) {
991            report(AppException::fromException($e, 'DELETE_ONGOING_JOB_EXCEPTION'));
992            return response(['message' => 'KO', 'error' => $e->getMessage()]);
993        }
994
995    }
996
997    function download_ongoing_jobs(Request $request): ResponseFactory|Response{
998        ini_set('max_execution_time', 123456);
999        $data = $request->all();
1000        $companyId = addslashes((string) $data['company_id']);
1001        $userId = addslashes($data['user_id']);
1002
1003        $where = '';
1004        $n = 17;
1005
1006        if ($companyId) {
1007            $n = 16;
1008        }
1009
1010        $r = new Request([
1011            'filterModel' => $data['filterModel'],
1012            'sortModel' => $data['sortModel'],
1013            'start' => 0,
1014            'end' => 999999999,
1015            'company_id' => $data['company_id'],
1016            'user_id' => $data['user_id'],
1017            'ids' => $data['ids'],
1018            'searchText' => $data['searchText'],
1019            'ids_not_in' => $data['ids_not_in'],
1020        ]);
1021
1022        $result = $this->list_ongoing_jobs($r);
1023
1024        $spreadsheet = new Spreadsheet;
1025        $worksheet = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, 'Inputs');
1026        $spreadsheet->addSheet($worksheet, 0);
1027        $col = range('A', 'Z');
1028
1029        for ($i = 0; $i < 20; $i++) {
1030            $worksheet->getColumnDimension($col[$i])->setAutoSize(true);
1031            if ($i != 1) {
1032                $worksheet->getStyle($col[$i])
1033                    ->getAlignment()
1034                    ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
1035            }
1036        }
1037
1038        $l = 1;
1039        $worksheet->setCellValue('A'.$l, __('language.ID'));
1040        $worksheet->setCellValue('B'.$l, __('language.CLIENT'));
1041        $worksheet->setCellValue('C'.$l, __('language.ISSUE_DATE'));
1042        $worksheet->setCellValue('D'.$l, __('language.ACCEPTANCE_DATE'));
1043        $worksheet->setCellValue('E'.$l, __('language.ORDER_NUMBER'));
1044        $worksheet->setCellValue('F'.$l, __('language.TYPE'));
1045        $worksheet->setCellValue('G'.$l, __('language.AMOUNT'));
1046        $worksheet->setCellValue('H'.$l, __('language.CREATED_BY'));
1047        $worksheet->setCellValue('I'.$l, __('language.RESPONSIBLE_FOR_WORK'));
1048        $worksheet->setCellValue('J'.$l, __('language.CLIENT_TYPE'));
1049        $worksheet->setCellValue('K'.$l, __('language.SEGMENT'));
1050        $worksheet->setCellValue('L'.$l, __('language.INVOICE_AMOUNT'));
1051        $worksheet->setCellValue('M'.$l, __('language.EXPECTED_START_DATE_OF_WORK'));
1052        $worksheet->setCellValue('N'.$l, __('language.ACTUAL_JOB_START_DATE'));
1053        $worksheet->setCellValue('O'.$l, __('language.ACTUAL_END_DATE'));
1054        $worksheet->setCellValue('P'.$l, __('language.EXPECTED_COMPLETION_DATE'));
1055        $worksheet->setCellValue('Q'.$l, __('language.CREATED_AT'));
1056
1057        $styleArray = [
1058            'font' => [
1059                'bold' => true,
1060            ],
1061        ];
1062
1063        $worksheet->getStyle('A1:'.(($n == 17) ? 'R1' : 'Q1'))
1064            ->getFill()
1065            ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
1066            ->getStartColor()
1067            ->setARGB('523779');
1068
1069        $worksheet->getStyle('A1:'.(($n == 17) ? 'R1' : 'Q1'))
1070            ->getFont()
1071            ->getColor()
1072            ->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
1073
1074        $worksheet->getStyle('A1:Q1')->applyFromArray($styleArray);
1075
1076        $l = 2;
1077        $result = $result->original['data'];
1078
1079        for ($i = 0; $i < count($result); $i++) {
1080
1081            if ($result[$i]->issue_date) {
1082                $result[$i]->issue_date = \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($result[$i]->issue_date);
1083            }
1084
1085            if ($result[$i]->acceptance_date) {
1086                $result[$i]->acceptance_date = \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($result[$i]->acceptance_date);
1087            }
1088
1089            if ($result[$i]->expected_start_date_of_work) {
1090                $result[$i]->expected_start_date_of_work = \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($result[$i]->expected_start_date_of_work);
1091            }
1092
1093            if ($result[$i]->actual_job_start_date) {
1094                $result[$i]->actual_job_start_date = \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($result[$i]->actual_job_start_date);
1095            }
1096
1097            if ($result[$i]->actual_end_date) {
1098                $result[$i]->actual_end_date = \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($result[$i]->actual_end_date);
1099            }
1100
1101            if ($result[$i]->expected_completion_date) {
1102                $result[$i]->expected_completion_date = \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($result[$i]->expected_completion_date);
1103            }
1104
1105            if ($result[$i]->created_at) {
1106                $result[$i]->created_at = \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($result[$i]->created_at);
1107            }
1108
1109            $worksheet->setCellValue('A'.$l, $result[$i]->quote_id);
1110            $worksheet->setCellValue('B'.$l, $result[$i]->client);
1111            $worksheet->setCellValue('C'.$l, $result[$i]->issue_date);
1112
1113            $worksheet->getStyle('C'.$l)
1114                ->getNumberFormat()
1115                ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);
1116
1117            $worksheet->setCellValue('D'.$l, $result[$i]->acceptance_date);
1118
1119            $worksheet->getStyle('D'.$l)
1120                ->getNumberFormat()
1121                ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);
1122
1123            $worksheet->setCellValue('E'.$l, $result[$i]->order_number);
1124            $worksheet->setCellValue('F'.$l, $result[$i]->type);
1125            $worksheet->setCellValue('G'.$l, $result[$i]->amount);
1126            $worksheet->setCellValue('H'.$l, $result[$i]->created_by);
1127            $worksheet->setCellValue('I'.$l, $result[$i]->responsible_for_work);
1128            $worksheet->setCellValue('J'.$l, $result[$i]->client_type);
1129            $worksheet->setCellValue('K'.$l, $result[$i]->segment);
1130            $worksheet->setCellValue('L'.$l, $result[$i]->invoice_amount);
1131            $worksheet->setCellValue('M'.$l, $result[$i]->expected_start_date_of_work);
1132
1133            $worksheet->getStyle('M'.$l)
1134                ->getNumberFormat()
1135                ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);
1136
1137            $worksheet->setCellValue('N'.$l, $result[$i]->actual_job_start_date);
1138
1139            $worksheet->getStyle('N'.$l)
1140                ->getNumberFormat()
1141                ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);
1142
1143            $worksheet->setCellValue('O'.$l, $result[$i]->actual_end_date);
1144
1145            $worksheet->getStyle('O'.$l)
1146                ->getNumberFormat()
1147                ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);
1148
1149            $worksheet->setCellValue('P'.$l, $result[$i]->expected_completion_date);
1150
1151            $worksheet->getStyle('P'.$l)
1152                ->getNumberFormat()
1153                ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);
1154
1155            $worksheet->setCellValue('Q'.$l, $result[$i]->created_at);
1156
1157            $worksheet->getStyle('Q'.$l)
1158                ->getNumberFormat()
1159                ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);
1160
1161            $l++;
1162
1163        }
1164
1165        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
1166        ob_start();
1167        $writer->save('php://output');
1168        $file = ob_get_contents();
1169        ob_end_clean();
1170
1171        return response($file);
1172    }
1173
1174    function update_ongoing_job(Request $request, $id): ResponseFactory|Response{
1175
1176        try {
1177
1178            $data = $request->all();
1179            $id = addslashes((string) $id);
1180            $userId = addslashes((string) $data['user_id']);
1181            unset($data['user_id']);
1182
1183            $r = ['amount', 'order_number', 'budget_type_id', 'acceptance_date'];
1184            $order = [];
1185
1186            foreach ($data as $key => $value) {
1187                if ($value == 'null') {
1188                    $data[$key] = null;
1189                }
1190
1191                if (in_array($key, $r)) {
1192                    $order[$key] = $value;
1193                }
1194            }
1195
1196            $data['updated_at'] = date('Y-m-d H:i:s');
1197            $order['updated_at'] = $data['updated_at'];
1198
1199            $revenuePerMonth = [];
1200            if(isset($data['revenue_per_month'])){
1201                $revenuePerMonth = $data['revenue_per_month'];
1202                unset($data['revenue_per_month']);
1203            }
1204
1205            $quotation = TblOngoingJobs::where('id', $id)->first();
1206
1207            if (count($revenuePerMonth) > 0) {
1208                TblRevenuePerMonth::where('quotation_id', $quotation->quotation_id)->delete();
1209                for ($i = 0; $i < count($revenuePerMonth); $i++) {
1210                    unset($revenuePerMonth[$i]['duplicate']);
1211                    unset($revenuePerMonth[$i]['invoice_date']);
1212
1213                    $revenuePerMonth[$i]['created_by'] = $data['updated_by'];
1214                    $revenuePerMonth[$i]['quotation_id'] = $quotation->quotation_id;
1215
1216                    if (date('Yn') == $revenuePerMonth[$i]['year'].$revenuePerMonth[$i]['month']) {
1217                        $data['to_be_invoiced_this_month'] = $revenuePerMonth[$i]['amount'];
1218                    }
1219                }
1220                TblRevenuePerMonth::insert($revenuePerMonth);
1221            }
1222
1223            $result = TblOngoingJobs::where('id', $id)->update($data);
1224            TblQuotations::where('id', $quotation->quotation_id)->update($order);
1225
1226            $this->send_mail($quotation, $data, $id, $userId, 0, $data['updated_by']);
1227
1228            Cache::flush();
1229
1230            return response(['message' => 'OK', 'data' => $result]);
1231
1232        } catch (\Exception $e) {
1233            report(AppException::fromException($e, 'UPDATE_ONGOING_JOB_EXCEPTION'));
1234            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1235        }
1236    }
1237
1238    public function get_dates(Request $request): ResponseFactory|Response{
1239
1240        try {
1241
1242            $data = $request->all();
1243            $companyId = addslashes((string) $data['company_id']);
1244
1245            $where = '';
1246            if ($companyId != 0) {
1247                $where = " a.company_id = {$companyId} ";
1248            } else {
1249                $where = " a.company_id IN ({$this->companyId}) ";
1250            }
1251
1252            $query = "SELECT
1253                        DATE_FORMAT(a.issue_date, '%d/%m/%Y') issue_date_translate,
1254                        DATE_FORMAT(a.acceptance_date, '%d/%m/%Y') acceptance_date_translate,
1255                        DATE_FORMAT(a.expected_start_date_of_work, '%d/%m/%Y') expected_start_date_of_work_translate,
1256                        DATE_FORMAT(a.actual_job_start_date, '%d/%m/%Y') actual_job_start_date_translate,
1257                        DATE_FORMAT(a.actual_end_date, '%d/%m/%Y') actual_end_date_translate,
1258                        DATE_FORMAT(a.expected_completion_date, '%d/%m/%Y') expected_completion_date_translate,
1259                        DATE_FORMAT(a.created_at, '%d/%m/%Y') created_at_translate
1260                    FROM tbl_ongoing_jobs a
1261                    WHERE {$where}";
1262
1263            $result = DB::select($query);
1264
1265            return response([
1266                'message' => 'OK',
1267                'data' => $result,
1268            ]);
1269
1270        } catch (\Exception $e) {
1271            report(AppException::fromException($e, 'GET_DATES_EXCEPTION'));
1272            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1273        }
1274
1275    }
1276
1277    function get_job($jobId): ResponseFactory|Response{
1278
1279        try {
1280
1281            $jobId = addslashes((string) $jobId);
1282
1283            $query = "SELECT 
1284                        a.id,
1285                        a.quotation_id, 
1286                        b.name company_name, 
1287                        c.name client_type,
1288                        s.name segment,
1289                        s.segment_id,
1290                        d.name 'type',
1291                        a.quote_id, 
1292                        a.company_id,
1293                        a.customer_type_id,
1294                        a.budget_type_id,
1295                        a.order_number,
1296                        a.client,
1297                        a.issue_date,
1298                        a.acceptance_date, 
1299                        DATE_FORMAT(a.issue_date, '%d/%m/%Y') issue_date_translate,
1300                        DATE_FORMAT(a.acceptance_date, '%d/%m/%Y') acceptance_date_translate,
1301                        a.amount, 
1302                        a.created_by, 
1303                        a.created_at,
1304                        a.updated_by,
1305                        a.updated_at,
1306                        a.invoice_amount,
1307                        a.responsible_for_work,
1308                        a.expected_start_date_of_work,
1309                        a.actual_job_start_date, 
1310                        a.actual_end_date, 
1311                        a.expected_completion_date, 
1312                        DATE_FORMAT(a.expected_start_date_of_work, '%d/%m/%Y') expected_start_date_of_work_translate,
1313                        DATE_FORMAT(a.actual_job_start_date, '%d/%m/%Y') actual_job_start_date_translate,
1314                        DATE_FORMAT(a.actual_end_date, '%d/%m/%Y') actual_end_date_translate,
1315                        DATE_FORMAT(a.expected_completion_date, '%d/%m/%Y') expected_completion_date_translate,
1316                        DATE_FORMAT(a.created_at, '%d/%m/%Y') created_at_translate,
1317                        a.work_status_id,
1318                        e.name work_status,
1319                        a.cost_for_client,
1320                        a.people_assigned_to_the_job,
1321                        a.duration_of_job_in_days,
1322                        a.estimated_cost_of_materials,
1323                        a.for_approval,
1324                        a.cost_of_labor,
1325                        a.total_cost_of_job,
1326                        a.invoice_margin,
1327                        a.margin_for_the_company,
1328                        a.margin_on_invoice_per_day_per_worker,
1329                        a.revenue_per_date_per_worked,
1330                        a.gross_margin,
1331                        a.labor_percentage,
1332                        a.pending_to_be_invoiced,
1333                        g.amount to_be_invoiced_this_month,
1334                        a.to_be_invoiced_after_this_month,
1335                        a.approved_at,
1336                        a.approved_by,
1337                        a.rejected_at,
1338                        a.rejected_by,
1339                        CASE 
1340                            WHEN 
1341                                (g.amount IS NULL OR g.amount = '')
1342                                AND (a.pending_to_be_invoiced IS NOT NULL AND a.pending_to_be_invoiced <> '' AND a.pending_to_be_invoiced <> 0)
1343                                AND DATE_FORMAT(NOW(), '%Y-%m-%d') BETWEEN DATE_FORMAT(CASE WHEN a.work_status_id = 3 OR a.work_status_id = 5 THEN a.actual_job_start_date ELSE a.expected_start_date_of_work END, '%Y-%m-%d') 
1344                                AND DATE_FORMAT(CASE WHEN a.work_status_id = 3 OR a.work_status_id = 5 THEN a.actual_end_date ELSE a.expected_completion_date END , '%Y-%m-%d') 
1345                            THEN 1
1346                            ELSE 0
1347                        END is_red_invoiced
1348                    FROM 
1349                        tbl_ongoing_jobs a 
1350                        LEFT JOIN tbl_companies b ON a.company_id = b.company_id
1351                        LEFT JOIN tbl_customer_types c ON a.customer_type_id = c.customer_type_id
1352                        LEFT JOIN tbl_segments s ON a.segment_id = s.segment_id
1353                        LEFT JOIN tbl_budget_types d ON a.budget_type_id = d.budget_type_id
1354                        LEFT JOIN tbl_work_status e ON a.work_status_id = e.work_status_id
1355                        LEFT JOIN tbl_revenue_per_month g ON a.quotation_id = g.quotation_id AND g.month = MONTH(NOW()) AND g.year = YEAR(NOW())
1356                    WHERE a.id = {$jobId}";
1357
1358            $result = DB::select($query);
1359
1360            Cache::flush();
1361
1362            return response(['message' => 'OK', 'data' => $result]);
1363
1364        } catch (\Exception $e) {
1365            report(AppException::fromException($e, 'GET_JOB_EXCEPTION'));
1366            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1367        }
1368    }
1369
1370    function list_job_analytics(Request $request): ResponseFactory|Response{
1371
1372        try {
1373
1374            $data = $request->all();
1375            $companyId = addslashes((string) $data['company_id']);
1376
1377            $where = '';
1378            $whereYears = '';
1379            $whereMonths = '';
1380
1381            if ($companyId != 0) {
1382                $where .= " AND company_id = {$companyId} ";
1383            } else {
1384                $where .= " AND company_id IN ({$this->companyId}) ";
1385            }
1386
1387            if (isset($data['start_years']) && $data['start_years'] != null) {
1388                $years = implode(',', $data['start_years']);
1389                if (count($data['start_years']) > 0) {
1390                    $whereYears = " AND YEAR(expected_start_date_of_work) IN ({$years})";
1391                }
1392            }
1393
1394            if (isset($data['start_months']) && $data['start_months'] != null) {
1395                $months = implode(',', $data['start_months']);
1396                if (count($data['start_months']) > 0) {
1397                    $whereMonths = " AND MONTH(expected_start_date_of_work) IN ({$months})";
1398                }
1399            }
1400
1401            if (isset($data['end_years']) && $data['end_years'] != null) {
1402                $years = implode(',', $data['end_years']);
1403                if (count($data['end_years']) > 0) {
1404                    $whereYears .= " AND YEAR(expected_completion_date) IN ({$years})";
1405                }
1406            }
1407
1408            if (isset($data['end_months']) && $data['end_months'] != null) {
1409                $months = implode(',', $data['end_months']);
1410                if (count($data['end_months']) > 0) {
1411                    $whereMonths .= " AND MONTH(expected_completion_date) IN ({$months})";
1412                }
1413            }
1414
1415            if (isset($data['responsible_for_work']) && $data['responsible_for_work'] != null) {
1416                $responsible = implode("','", $data['responsible_for_work']);
1417                if (count($data['responsible_for_work']) > 0) {
1418                    $where .= " AND a.responsible_for_work IN ('{$responsible}')";
1419                }
1420            }
1421
1422            if (isset($data['work_status_id']) && $data['work_status_id'] != null) {
1423                $work = implode(',', $data['work_status_id']);
1424                if (count($data['work_status_id']) > 0) {
1425                    $where .= " AND a.work_status_id IN ({$work})";
1426                }
1427            }
1428
1429            if (isset($data['budget_type_id']) && $data['budget_type_id'] != null) {
1430                $budget = implode(',', $data['budget_type_id']);
1431                if (count($data['budget_type_id']) > 0) {
1432                    $where .= " AND a.budget_type_id IN ({$budget})";
1433                }
1434            }
1435
1436            $query = "SELECT
1437                        YEAR 'year',
1438                        MONTH 'month',
1439                        WEEK 'week',
1440                        SUM(acceptance_date) AS totalApproved,
1441                        SUM(expected_start_date_of_work) AS totalJobsExpectedToStart,
1442                        SUM(expected_completion_date) AS totalJobsExpectedToFinalized,
1443                        SUM(actual_job_start_date) AS totalJobsStarted,
1444                        SUM(actual_end_date) AS totalJobsFinalized,
1445                        SUM(revenueExpected) AS revenueExpected,
1446                        SUM(realRevenue) AS realRevenue
1447                    FROM (
1448                        SELECT
1449                            YEAR(acceptance_date) AS YEAR,
1450                            MONTH(acceptance_date) AS MONTH,
1451                            WEEK(acceptance_date) AS WEEK,
1452                            COUNT(*) AS acceptance_date,
1453                            0 AS expected_start_date_of_work,
1454                            0 AS expected_completion_date,
1455                            0 AS actual_job_start_date,
1456                            0 AS actual_end_date,
1457                            0 AS revenueExpected,
1458                            0 AS realRevenue
1459                        FROM
1460                            tbl_ongoing_jobs
1461                        WHERE 
1462                            issue_date IS NOT NULL
1463                            {$where}
1464                            {$whereYears}
1465                            {$whereMonths}
1466                        GROUP BY
1467                            YEAR(acceptance_date),
1468                            MONTH(acceptance_date),
1469                            WEEK(acceptance_date)
1470                    
1471                        UNION ALL
1472
1473                        SELECT
1474                            YEAR(expected_start_date_of_work) AS YEAR,
1475                            MONTH(expected_start_date_of_work) AS MONTH,
1476                            WEEK(expected_start_date_of_work) AS WEEK,
1477                            0 AS acceptance_date,
1478                            COUNT(*) AS expected_start_date_of_work,
1479                            0 AS expected_completion_date,
1480                            0 AS actual_job_start_date,
1481                            0 AS actual_end_date,
1482                            0 AS revenueExpected,
1483                            0 AS realRevenue
1484                        FROM
1485                            tbl_ongoing_jobs
1486                        WHERE 
1487                            issue_date IS NOT NULL
1488                            {$where}
1489                            {$whereYears}
1490                            {$whereMonths}
1491                        GROUP BY
1492                            YEAR(expected_start_date_of_work),
1493                            MONTH(expected_start_date_of_work),
1494                            WEEK(expected_start_date_of_work)
1495                    
1496                        UNION ALL
1497                    
1498                        SELECT
1499                            YEAR(expected_completion_date) AS YEAR,
1500                            MONTH(expected_completion_date) AS MONTH,
1501                            WEEK(expected_completion_date) AS WEEK,
1502                            0 AS acceptance_date,
1503                            0 AS expected_start_date_of_work,
1504                            COUNT(*) AS expected_completion_date,
1505                            0 AS actual_job_start_date,
1506                            0 AS actual_end_date,
1507                            0 AS revenueExpected,
1508                            0 AS realRevenue
1509                        FROM
1510                            tbl_ongoing_jobs
1511                        WHERE 
1512                            issue_date IS NOT NULL
1513                            {$where}
1514                            {$whereYears}
1515                            {$whereMonths}
1516                        GROUP BY
1517                            YEAR(expected_completion_date),
1518                            MONTH(expected_completion_date),
1519                            WEEK(expected_completion_date)
1520                    
1521                        UNION ALL
1522                    
1523                        SELECT
1524                            YEAR(actual_job_start_date) AS YEAR,
1525                            MONTH(actual_job_start_date) AS MONTH,
1526                            WEEK(actual_job_start_date) AS WEEK,
1527                            0 AS acceptance_date,
1528                            0 AS expected_start_date_of_work,
1529                            0 AS expected_completion_date,
1530                            COUNT(*) AS actual_job_start_date,
1531                            0 AS actual_end_date,
1532                            0 AS revenueExpected,
1533                            0 AS realRevenue
1534                        FROM
1535                            tbl_ongoing_jobs
1536                        WHERE 
1537                            issue_date IS NOT NULL
1538                            {$where}
1539                            {$whereYears}
1540                            {$whereMonths}
1541                        GROUP BY
1542                            YEAR(actual_job_start_date),
1543                            MONTH(actual_job_start_date),
1544                            WEEK(actual_job_start_date)
1545                    
1546                        UNION ALL
1547                    
1548                        SELECT
1549                            YEAR(actual_end_date) AS YEAR,
1550                            MONTH(actual_end_date) AS MONTH,
1551                            WEEK(actual_end_date) AS WEEK,
1552                            0 AS acceptance_date,
1553                            0 AS expected_start_date_of_work,
1554                            0 AS expected_completion_date,
1555                            0 AS actual_job_start_date,
1556                            COUNT(*) AS actual_end_date,
1557                            0 AS revenueExpected,
1558                            0 AS realRevenue
1559                        FROM
1560                            tbl_ongoing_jobs
1561                        WHERE 
1562                            issue_date IS NOT NULL
1563                            {$where}
1564                            {$whereYears}
1565                            {$whereMonths}
1566                        GROUP BY
1567                            YEAR(actual_end_date),
1568                            MONTH(actual_end_date),
1569                            WEEK(actual_end_date)
1570                    
1571                        UNION ALL
1572                    
1573                        SELECT
1574                            YEAR(expected_completion_date) AS YEAR,
1575                            MONTH(expected_completion_date) AS MONTH,
1576                            WEEK(expected_completion_date) AS WEEK,
1577                            0 AS acceptance_date,
1578                            0 AS expected_start_date_of_work,
1579                            0 AS expected_completion_date,
1580                            0 AS actual_job_start_date,
1581                            0 AS actual_end_date,
1582                            SUM(CASE WHEN cost_for_client REGEXP '^[0-9]+\\.?[0-9]*$' = 1 THEN cost_for_client END) AS revenueExpected,
1583                            SUM(CASE WHEN invoice_amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1 THEN invoice_amount END) AS realRevenue
1584                        FROM
1585                            tbl_ongoing_jobs
1586                        WHERE 
1587                            issue_date IS NOT NULL
1588                            {$where}
1589                            {$whereYears}
1590                            {$whereMonths}
1591                        GROUP BY
1592                            YEAR(expected_completion_date),
1593                            MONTH(expected_completion_date),
1594                            WEEK(expected_completion_date)
1595                    ) AS subquery
1596                    GROUP BY 
1597                        YEAR, 
1598                        MONTH, 
1599                        WEEK WITH ROLLUP
1600                    HAVING 
1601                        (YEAR IS NOT NULL OR MONTH IS NOT NULL OR WEEK IS NOT NULL)
1602                    ORDER BY
1603                        YEAR, MONTH, WEEK;
1604                    ";
1605
1606            $result = DB::select($query);
1607
1608            return response([
1609                'message' => 'OK',
1610                'data' => $result,
1611            ]);
1612
1613        } catch (\Exception $e) {
1614            report(AppException::fromException($e, 'LIST_JOB_ANALYTICS_EXCEPTION'));
1615            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1616        }
1617
1618    }
1619
1620    function get_years(Request $request): ResponseFactory|Response{
1621
1622        try {
1623
1624            $data = $request->all();
1625            $companyId = addslashes((string) $data['company_id']);
1626            $where = "";
1627
1628            if ($companyId != 0) {
1629                $where = " AND company_id = {$companyId} ";
1630            } else {
1631                $where = " AND company_id IN ({$this->companyId}) ";
1632            }
1633
1634            $query = "SELECT 
1635                            DISTINCT YEAR(acceptance_date) AS 'year' 
1636                        FROM 
1637                            tbl_ongoing_jobs 
1638                        WHERE 
1639                            acceptance_date IS NOT NULL 
1640                            {$where}
1641                        UNION 
1642                        SELECT 
1643                            DISTINCT YEAR(actual_job_start_date) AS 'year' 
1644                        FROM 
1645                            tbl_ongoing_jobs 
1646                        WHERE 
1647                            actual_job_start_date IS NOT NULL 
1648                            {$where}
1649                        UNION 
1650                        SELECT 
1651                            DISTINCT YEAR(expected_start_date_of_work) AS 'year' 
1652                        FROM 
1653                            tbl_ongoing_jobs 
1654                        WHERE 
1655                            expected_start_date_of_work IS NOT NULL 
1656                            {$where}
1657                        UNION 
1658                        SELECT 
1659                            DISTINCT YEAR(actual_end_date) AS 'year' 
1660                        FROM 
1661                            tbl_ongoing_jobs 
1662                        WHERE 
1663                            actual_end_date IS NOT NULL 
1664                            {$where}
1665                        UNION 
1666                        SELECT 
1667                            DISTINCT YEAR(expected_completion_date) AS 'year' 
1668                        FROM 
1669                            tbl_ongoing_jobs 
1670                        WHERE 
1671                            expected_completion_date IS NOT NULL 
1672                            {$where}
1673                        ORDER BY 
1674                            YEAR
1675                            ";
1676
1677            $result = DB::select($query);
1678
1679            return response([
1680                'message' => 'OK',
1681                'data' => $result,
1682            ]);
1683
1684        } catch (\Exception $e) {
1685            report(AppException::fromException($e, 'GET_YEARS_EXCEPTION'));
1686            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1687        }
1688
1689    }
1690
1691    public function list_margin_jobs_analytics(Request $request): ResponseFactory|Response{
1692
1693        try {
1694
1695            $data = $request->all();
1696            $companyId = addslashes((string) $data['company_id']);
1697            $responsibleForWork = $data['user'];
1698            $where = '';
1699            $whereYear = '';
1700            $whereMonths = '';
1701
1702            if ($companyId != 0) {
1703                $where .= " AND a.company_id = {$companyId} ";
1704            } else {
1705                $where .= " AND a.company_id IN ({$this->companyId}) ";
1706            }
1707
1708            if (isset($data['start_years']) && $data['start_years'] != null) {
1709                $years = implode(',', $data['start_years']);
1710                if (count($data['start_years']) > 0) {
1711                    $whereYear = " AND YEAR(a.expected_start_date_of_work) IN ({$years})";
1712                }
1713            }
1714
1715            if (isset($data['start_months']) && $data['start_months'] != null) {
1716                $months = implode(',', $data['start_months']);
1717                if (count($data['start_months']) > 0) {
1718                    $whereMonths = " AND MONTH(a.expected_start_date_of_work) IN ({$months})";
1719                }
1720            }
1721
1722            if (isset($data['end_years']) && $data['end_years'] != null) {
1723                $years = implode(',', $data['end_years']);
1724                if (count($data['end_years']) > 0) {
1725                    $whereYear .= " AND YEAR(a.expected_completion_date) IN ({$years})";
1726                }
1727            }
1728
1729            if (isset($data['end_months']) && $data['end_months'] != null) {
1730                $months = implode(',', $data['end_months']);
1731                if (count($data['end_months']) > 0) {
1732                    $whereMonths .= " AND MONTH(a.expected_completion_date) IN ({$months})";
1733                }
1734            }
1735
1736            if (isset($data['responsible_for_work']) && $data['responsible_for_work'] != null) {
1737                $responsible = implode("','", $data['responsible_for_work']);
1738                if (count($data['responsible_for_work']) > 0) {
1739                    $where .= " AND a.responsible_for_work IN ('{$responsible}')";
1740                }
1741            }
1742
1743            if (isset($data['work_status_id']) && $data['work_status_id'] != null) {
1744                $work = implode(',', $data['work_status_id']);
1745                if (count($data['work_status_id']) > 0) {
1746                    $where .= " AND a.work_status_id IN ({$work})";
1747                }
1748            }
1749
1750            if (isset($data['budget_type_id']) && $data['budget_type_id'] != null) {
1751                $budget = implode(',', $data['budget_type_id']);
1752                if (count($data['budget_type_id']) > 0) {
1753                    $where .= " AND a.budget_type_id IN ({$budget})";
1754                }
1755            }
1756
1757            $whereInconsistent = '';
1758            if (isset($data['status']) && $data['status'] != null) {
1759                if ($data['status'] == 1) {
1760                    $whereInconsistent = " AND ((DATE_FORMAT(a.expected_start_date_of_work, '%Y-%m-%d') < DATE_FORMAT(NOW(), '%Y-%m-%d') AND a.work_status_id = 2
1761                        OR DATE_FORMAT(a.expected_completion_date, '%Y-%m-%d') < DATE_FORMAT(NOW(), '%Y-%m-%d') AND a.work_status_id NOT IN (3, 5))
1762                        ) AND (CASE WHEN a.responsible_for_work IS NULL THEN a.created_by ELSE a.responsible_for_work END) = '{$responsibleForWork}'";
1763                } elseif ($data['status'] == 2) {
1764                    $whereInconsistent = " AND ((g.amount IS NULL OR g.amount = '')
1765                        AND (CASE WHEN a.responsible_for_work IS NULL THEN a.created_by ELSE a.responsible_for_work END) = '{$responsibleForWork}'
1766                        AND DATE_FORMAT(NOW(), '%Y-%m-%d') BETWEEN DATE_FORMAT(CASE WHEN a.work_status_id = 3 OR a.work_status_id = 5 THEN a.actual_job_start_date ELSE a.expected_start_date_of_work END, '%Y-%m-%d') 
1767                        AND DATE_FORMAT(CASE WHEN a.work_status_id = 3 OR a.work_status_id = 5 THEN a.actual_end_date ELSE a.expected_completion_date END , '%Y-%m-%d') ) ";
1768                }
1769            }
1770
1771            $activeJobWhere = '';
1772            $activeJob = 0;
1773
1774            $activeStartDateColumn = 'CASE WHEN a.work_status_id IN (1,2,4) THEN expected_start_date_of_work ELSE actual_job_start_date END';
1775            $activeEndDateColumn = 'CASE WHEN a.work_status_id IN (1,2,4) THEN expected_completion_date ELSE actual_end_date END';
1776
1777            if (isset($data['active_year']) && $data['active_year'] != null) {
1778                $activeJobWhere = " AND {$data['active_year']} 
1779                    BETWEEN YEAR({$activeStartDateColumn}) 
1780                    AND YEAR({$activeEndDateColumn}) ";
1781                $activeJob++;
1782            }
1783
1784            $gMonth = 'MONTH(NOW())';
1785            $gYear = 'YEAR(NOW())';
1786
1787            if (isset($data['active_month']) && $data['active_month'] != null) {
1788                $gMonth = $data['active_month'];
1789                if (empty($data['active_year'])) {
1790                    $r = new Request([
1791                        'company_id' => $companyId,
1792                    ]);
1793                    $getYears = $this->get_years($r);
1794                    $years = $getYears->original['data'];
1795
1796                    $activeJobWhereArray = [];
1797                    foreach ($years as $k => $v) {
1798                        $activeJobWhere = " CONCAT('{$years[$k]->year}', '-', LPAD('{$data['active_month']}', 2, 0)) BETWEEN DATE_FORMAT({$activeStartDateColumn}, '%Y-%m') AND DATE_FORMAT({$activeEndDateColumn} , '%Y-%m') ";
1799                        array_push($activeJobWhereArray, $activeJobWhere);
1800                    }
1801
1802                    $activeJobWhere = implode(' OR ', $activeJobWhereArray);
1803                    $activeJobWhere = " AND ({$activeJobWhere}) ";
1804                }
1805
1806                $activeJob++;
1807            }
1808
1809            if ($activeJob == 2) {
1810                $gYear = $data['active_year'];
1811                $activeJobWhere = " AND CONCAT('{$data['active_year']}', '-', LPAD('{$data['active_month']}', 2, 0)) BETWEEN DATE_FORMAT({$activeStartDateColumn}, '%Y-%m') AND DATE_FORMAT({$activeEndDateColumn} , '%Y-%m') ";
1812            }
1813
1814            $where .= $activeJobWhere;
1815
1816            $query = "SELECT
1817                            a.id,
1818                            a.quotation_id, 
1819                            b.name company_name, 
1820                            c.name client_type,
1821                            s.name segment,
1822                            s.segment_id,
1823                            d.name 'type',
1824                            a.quote_id, 
1825                            a.company_id,
1826                            a.customer_type_id,
1827                            a.budget_type_id,
1828                            a.order_number,
1829                            a.client,
1830                            a.issue_date,
1831                            a.acceptance_date, 
1832                            DATE_FORMAT(a.issue_date, '%d/%m/%Y') issue_date_translate,
1833                            DATE_FORMAT(a.acceptance_date, '%d/%m/%Y') acceptance_date_translate,
1834                            a.amount, 
1835                            a.created_by, 
1836                            a.created_at,
1837                            a.updated_by,
1838                            a.updated_at,
1839                            a.invoice_amount,
1840                            a.responsible_for_work,
1841                            a.expected_start_date_of_work,
1842                            a.actual_job_start_date, 
1843                            a.actual_end_date, 
1844                            a.expected_completion_date, 
1845                            DATE_FORMAT(a.expected_start_date_of_work, '%d/%m/%Y') expected_start_date_of_work_translate,
1846                            DATE_FORMAT(a.actual_job_start_date, '%d/%m/%Y') actual_job_start_date_translate,
1847                            DATE_FORMAT(a.actual_end_date, '%d/%m/%Y') actual_end_date_translate,
1848                            DATE_FORMAT(a.expected_completion_date, '%d/%m/%Y') expected_completion_date_translate,
1849                            DATE_FORMAT(a.created_at, '%d/%m/%Y') created_at_translate,
1850                            a.work_status_id,
1851                            e.name work_status,
1852                            CASE 
1853                                WHEN 
1854                                DATE_FORMAT(a.expected_completion_date, '%Y-%m-%d') < DATE_FORMAT(NOW(), '%Y-%m-%d') AND a.work_status_id NOT IN (3, 5)
1855                                THEN 1
1856                                ELSE 0
1857                            END is_red_expected_completion_date,
1858                            CASE 
1859                                WHEN 
1860                                DATE_FORMAT(a.expected_start_date_of_work, '%Y-%m-%d') < DATE_FORMAT(NOW(), '%Y-%m-%d') AND a.work_status_id = 2
1861                                THEN 1
1862                                ELSE 0
1863                            END is_red_expected_start_date_of_work,
1864                            CASE 
1865                                WHEN 
1866                                    (g.amount IS NULL OR g.amount = '')
1867                                    AND (a.pending_to_be_invoiced IS NOT NULL AND a.pending_to_be_invoiced <> '' AND a.pending_to_be_invoiced <> 0)
1868                                    AND DATE_FORMAT(NOW(), '%Y-%m-%d') BETWEEN DATE_FORMAT(CASE WHEN a.work_status_id = 3 OR a.work_status_id = 5 THEN a.actual_job_start_date ELSE a.expected_start_date_of_work END, '%Y-%m-%d') 
1869                                    AND DATE_FORMAT(CASE WHEN a.work_status_id = 3 OR a.work_status_id = 5 THEN a.actual_end_date ELSE a.expected_completion_date END , '%Y-%m-%d') 
1870                                THEN 1
1871                                ELSE 0
1872                            END is_red_invoiced,
1873                            a.cost_for_client,
1874                            a.people_assigned_to_the_job,
1875                            a.duration_of_job_in_days,
1876                            a.estimated_cost_of_materials,
1877                            a.cost_of_labor,
1878                            a.total_cost_of_job,
1879                            a.invoice_margin,
1880                            a.margin_for_the_company,
1881                            a.margin_on_invoice_per_day_per_worker,
1882                            a.revenue_per_date_per_worked,
1883                            a.gross_margin,
1884                            a.labor_percentage,
1885                            a.pending_to_be_invoiced,
1886                            (SELECT amount FROM tbl_revenue_per_month WHERE quotation_id = a.quotation_id AND month = {$gMonth} AND year = {$gYear}) to_be_invoiced_this_month,
1887                            a.to_be_invoiced_after_this_month,
1888                            a.for_approval,
1889                            a.approved_at,
1890                            a.approved_by,
1891                            a.rejected_at,
1892                            a.rejected_by,
1893                            d.color,
1894                            e.color color_work_status,
1895                            CASE 
1896                                WHEN f.budget_type_id IS NULL THEN b.revenue_per_employee_per_day
1897                                ELSE f.revenue_per_employee_per_day
1898                            END revenue_per_employee_per_day,
1899                            CASE 
1900                                WHEN CAST(f.revenue_per_employee_per_day AS DOUBLE) > CAST(a.revenue_per_date_per_worked AS DOUBLE) AND f.budget_type_id IS NOT NULL THEN 1
1901                                WHEN CAST(b.revenue_per_employee_per_day AS DOUBLE) > CAST(a.revenue_per_date_per_worked AS DOUBLE) AND f.budget_type_id IS NULL THEN 1
1902                                ELSE 0
1903                            END is_below_benchmark
1904                        FROM tbl_ongoing_jobs a
1905                            LEFT JOIN tbl_companies b ON a.company_id = b.company_id
1906                            LEFT JOIN tbl_customer_types c ON a.customer_type_id = c.customer_type_id
1907                            LEFT JOIN tbl_segments s ON a.segment_id = s.segment_id
1908                            LEFT JOIN tbl_budget_types d ON a.budget_type_id = d.budget_type_id
1909                            LEFT JOIN tbl_work_status e ON a.work_status_id = e.work_status_id
1910                            LEFT JOIN tbl_project_types f ON a.budget_type_id = f.budget_type_id AND a.company_id = f.company_id
1911                            LEFT JOIN tbl_revenue_per_month g ON a.quotation_id = g.quotation_id AND g.month = MONTH(NOW()) AND g.year = YEAR(NOW())
1912                        WHERE a.issue_date 
1913                        AND a.expected_start_date_of_work IS NOT NULL
1914                        {$where}
1915                        {$whereYear}
1916                        {$whereMonths}
1917                        {$whereInconsistent}
1918                        ORDER BY a.client ASC";
1919
1920            $result = DB::select($query);
1921
1922            $durationOfJobInDays = 0;
1923            $peopleAssignedToTheJob = 0;
1924            $totaCostForClient = 0;
1925            $totalCostOfLabor = 0;
1926            $totalMaterialCosts = 0;
1927            $revenuePerDayPerWorked = 0;
1928            $toBeInvoiceIn = 0;
1929            $pAndD = 0;
1930
1931            foreach ($result as $item) {
1932                $durationOfJobInDays = $durationOfJobInDays + $item->duration_of_job_in_days;
1933                $peopleAssignedToTheJob = $peopleAssignedToTheJob + $item->people_assigned_to_the_job;
1934                $totaCostForClient = $totaCostForClient + $item->cost_for_client;
1935                $totalCostOfLabor = $totalCostOfLabor + $item->cost_of_labor;
1936                $totalMaterialCosts = $totalMaterialCosts + $item->estimated_cost_of_materials;
1937                $toBeInvoiceIn = $toBeInvoiceIn + $item->to_be_invoiced_this_month;
1938                $pAndD = $pAndD + ($item->people_assigned_to_the_job * $item->duration_of_job_in_days);
1939            }
1940
1941            $totalCostOfJob = $totalCostOfLabor + $totalMaterialCosts;
1942
1943            $invoiceMargin = 0;
1944            $grossMargin = 0;
1945            $laborPercentage = 0;
1946            $marginForTheCompany = 0;
1947            if ($totalCostOfJob > 0) {
1948                $invoiceMargin = ($totaCostForClient - $totalCostOfJob) / $totaCostForClient * 100;
1949                $grossMargin = ($totaCostForClient - $totalMaterialCosts) / $totaCostForClient * 100;
1950                $laborPercentage = ($totalCostOfLabor / $totaCostForClient) * 100;
1951
1952                $company = TblCompanies::where('company_id', $companyId)->first();
1953                if ($company) {
1954                    $marginForTheCompany = $invoiceMargin - $company->hours_per_worker_per_day_percentage;
1955                }
1956            }
1957
1958            if ($pAndD != 0) {
1959                $revenuePerDayPerWorked = $totaCostForClient / $pAndD;
1960            }
1961
1962            $resultTotal = [
1963                'durationOfJobInDays' => $durationOfJobInDays,
1964                'peopleAssignedToTheJob' => $peopleAssignedToTheJob,
1965                'totalCostForClient' => $totaCostForClient,
1966                'totalCostOfLabor' => $totalCostOfLabor,
1967                'totalMaterialCosts' => $totalMaterialCosts,
1968                'invoiceMargin' => $invoiceMargin,
1969                'marginForTheCompany' => $marginForTheCompany,
1970                'revenuePerDayPerWorked' => $revenuePerDayPerWorked,
1971                'toBeInvoiceIn' => $toBeInvoiceIn,
1972                'grossMargin' => $grossMargin,
1973                'laborPercentage' => $laborPercentage
1974            ];
1975
1976            return response([
1977                'message' => 'OK',
1978                'data' => $result,
1979                'totals' => $resultTotal,
1980            ]);
1981
1982        } catch (\Exception $e) {
1983            report(AppException::fromException($e, 'LIST_MARGIN_JOB_ANALYTICS_EXCEPTION'));
1984            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1985        }
1986    }
1987
1988    function get_total_jobs_in_red(Request $request): ResponseFactory|Response{
1989
1990        try {
1991
1992            $data = $request->all();
1993            $companyId = addslashes((string) $data['company_id']);
1994            $responsibleForWork = $data['responsible_for_work'];
1995            $where = '';
1996
1997            if ($companyId != 0) {
1998                $where .= " AND a.company_id = {$companyId} ";
1999            } else {
2000                $where .= " AND a.company_id IN ({$this->companyId}) ";
2001            }
2002
2003            $query = "SELECT
2004                        COUNT(1) total
2005                    FROM tbl_ongoing_jobs a
2006                    WHERE 
2007                        a.expected_start_date_of_work IS NOT NULL
2008                        AND (
2009                            DATE_FORMAT(a.expected_start_date_of_work, '%Y-%m-%d') < DATE_FORMAT(NOW(), '%Y-%m-%d') AND a.work_status_id = 2
2010                            OR DATE_FORMAT(a.expected_completion_date, '%Y-%m-%d') < DATE_FORMAT(NOW(), '%Y-%m-%d') AND a.work_status_id NOT IN (3, 5)                            
2011                        )
2012                        AND (CASE WHEN responsible_for_work IS NULL THEN created_by ELSE responsible_for_work END) = '{$responsibleForWork}'
2013                        {$where}";
2014
2015            $result = DB::select($query);
2016
2017            $query = "SELECT
2018                        COUNT(1) total
2019                    FROM tbl_ongoing_jobs a
2020                    LEFT JOIN tbl_revenue_per_month g ON a.quotation_id = g.quotation_id AND g.month = MONTH(NOW()) AND g.year = YEAR(NOW())
2021                    WHERE 
2022                        a.expected_start_date_of_work IS NOT NULL
2023                        AND ((g.amount IS NULL OR g.amount = '')
2024                        AND (CASE WHEN responsible_for_work IS NULL THEN a.created_by ELSE responsible_for_work END) = '{$responsibleForWork}')
2025                        AND DATE_FORMAT(NOW(), '%Y-%m-%d') BETWEEN DATE_FORMAT(CASE WHEN a.work_status_id = 3 OR a.work_status_id = 5 THEN a.actual_job_start_date ELSE a.expected_start_date_of_work END, '%Y-%m-%d') 
2026                        AND DATE_FORMAT(CASE WHEN a.work_status_id = 3 OR a.work_status_id = 5 THEN a.actual_end_date ELSE a.expected_completion_date END , '%Y-%m-%d') 
2027                        {$where}";
2028
2029            $invoiced = DB::select($query);
2030
2031            $query = "SELECT COUNT(1) total FROM tbl_quotations a WHERE DATE_FORMAT(a.visit_date, '%Y-%m-%d') < DATE_FORMAT(NOW(), '%Y-%m-%d') AND a.commercial = '{$responsibleForWork}' AND a.budget_status_id = 8 {$where}";
2032            $countPastVisitDate = DB::select($query);
2033
2034            $query = "SELECT 
2035                            COUNT(1) total
2036                        FROM tbl_quotations a 
2037                        LEFT JOIN tbl_companies b ON a.company_id = b.company_id
2038                        LEFT JOIN (
2039                                    SELECT
2040                                    a.id,
2041                                    SUBSTRING_INDEX(
2042                                        SUBSTRING_INDEX(a.email, ',', n.digit + 1),
2043                                        ',',
2044                                        -1
2045                                    ) AS email_domain
2046                                    FROM
2047                                    tbl_quotations a
2048                                    INNER JOIN (
2049                                        SELECT
2050                                        0 AS digit
2051                                        UNION ALL
2052                                        SELECT
2053                                        1
2054                                        UNION ALL
2055                                        SELECT
2056                                        2
2057                                        UNION ALL
2058                                        SELECT
2059                                        3
2060                                        UNION ALL
2061                                        SELECT
2062                                        4
2063                                        UNION ALL
2064                                        SELECT
2065                                        5
2066                                        UNION ALL
2067                                        SELECT
2068                                        6
2069                                        UNION ALL
2070                                        SELECT
2071                                        7
2072                                        UNION ALL
2073                                        SELECT
2074                                        8
2075                                        UNION ALL
2076                                        SELECT
2077                                        9
2078                                    ) n ON LENGTH(
2079                                        REPLACE(a.email, ',', '')
2080                                    ) <= LENGTH(a.email)- n.digit
2081                                    GROUP BY a.id
2082                                ) temp ON a.id = temp.id
2083                        WHERE
2084                            a.last_follow_up_date < DATE_SUB(NOW(), INTERVAL 2 DAY)
2085                            AND a.budget_status_id IN (2)
2086                            AND a.email IS NOT NULL
2087                            AND a.email <> ''
2088                            AND NOT EXISTS (
2089                                SELECT
2090                                1
2091                                FROM
2092                                tbl_blocked_domains bd
2093                                WHERE
2094                                temp.email_domain LIKE CONCAT('%', bd.domain, '%')
2095                                AND bd.company_id = a.company_id
2096                            )
2097                            AND a.last_follow_up_date IS NOT NULL
2098                            AND a.reason_for_not_following_up_id IS NULL
2099                            AND a.last_follow_up_date > 0
2100                            AND a.total_sent < b.limit_reminder_emails
2101                            AND a.for_add = 0
2102                            AND a.commercial = '{$responsibleForWork}'
2103                            {$where}";
2104
2105            $countReminders = DB::select($query);
2106
2107            return response([
2108                'message' => 'OK',
2109                'data' => $result[0]->total,
2110                'invoiced' => $invoiced[0]->total,
2111                'totalPastVisitDate' => $countPastVisitDate[0]->total,
2112                'totalReminders' => $countReminders[0]->total,
2113            ]);
2114
2115        } catch (\Exception $e) {
2116            report(AppException::fromException($e, 'GET_TOTAL_JOBS_IN_RED_EXCEPTION'));
2117            return response(['message' => 'KO', 'error' => $e->getMessage()]);
2118        }
2119    }
2120
2121    public function get_active_job_dates($companyId): ResponseFactory|Response{
2122
2123        try {
2124            
2125            $companyId = addslashes((string) $companyId);
2126            $where = "";
2127
2128            if ($companyId != 0) {
2129                $where .= " AND company_id = {$companyId} ";
2130            } else {
2131                $where .= " AND company_id IN ({$this->companyId}) ";
2132            }
2133
2134            $query = "SELECT 
2135                        DISTINCT years 
2136                    FROM 
2137                        (
2138                        SELECT 
2139                            YEAR(expected_start_date_of_work) AS years 
2140                        FROM 
2141                            tbl_ongoing_jobs 
2142                        WHERE 
2143                            expected_start_date_of_work IS NOT NULL 
2144                            {$where}
2145                        UNION 
2146                        SELECT 
2147                            YEAR(expected_completion_date) AS years 
2148                        FROM 
2149                            tbl_ongoing_jobs 
2150                        WHERE 
2151                            expected_completion_date IS NOT NULL
2152                            {$where}
2153                        ) AS combined_years 
2154                    ORDER BY 
2155                        years DESC
2156                    ";
2157
2158            $activeYears = DB::select($query);
2159
2160            $query = "SELECT 
2161                        DISTINCT months 
2162                    FROM 
2163                        (
2164                        SELECT 
2165                            MONTH(expected_start_date_of_work) AS months 
2166                        FROM 
2167                            tbl_ongoing_jobs 
2168                        WHERE 
2169                            expected_start_date_of_work IS NOT NULL 
2170                            {$where}
2171                        UNION 
2172                        SELECT 
2173                            MONTH(expected_completion_date) AS months 
2174                        FROM 
2175                            tbl_ongoing_jobs 
2176                        WHERE 
2177                            expected_completion_date IS NOT NULL
2178                            {$where}
2179                        ) AS combined_months
2180                    ORDER BY 
2181                        months ASC
2182                    ";
2183
2184            $activeMonths = DB::select($query);
2185
2186            return response([
2187                'message' => 'OK', 
2188                'data' => [
2189                    'years' => $activeYears,
2190                    'months' => $activeMonths
2191                ],
2192            ]);
2193
2194        } catch (\Exception $e) {
2195            report(AppException::fromException($e, 'GET_ACTIVE_JOB_DATES_EXCEPTION'));
2196            return response(['message' => 'KO', 'error' => $e->getMessage()]);
2197        }
2198
2199    }
2200
2201    function update_ongoing_jobs_month_change($companyId = null, $processedBy = "System"): ResponseFactory|Response{
2202
2203        return response(['message' => 'OK']);
2204
2205    }
2206
2207    function update_ongoing_jobs_month_change_manual(Request $request): ResponseFactory|Response{
2208
2209        try {
2210
2211            $data = $request->all();
2212            
2213            $update = $this->update_ongoing_jobs_month_change(addslashes((string) $data['company_id']), $data['processed_by']);
2214
2215            if ($update) {
2216                return response(['message' => 'OK']);
2217            } else {
2218                return response(['message' => 'KO']);
2219            }
2220
2221        } catch (\Exception $e) {
2222            report(AppException::fromException($e, 'UPDATE_ONGOING_JOB_MONTH_CHANGE_MANUAL_EXCEPTION'));
2223            return response(['message' => 'KO', 'error' => $e->getMessage()]);
2224        }
2225
2226    }
2227
2228    function get_revenue_per_month($quotationId): ResponseFactory|Response{
2229        
2230        try {
2231
2232            $quotationId = addslashes((string) $quotationId);
2233
2234            $result = TblRevenuePerMonth::where('quotation_id', $quotationId)->orderBy('year', 'ASC')->orderBy('month', 'ASC')->get();
2235
2236            return response(['message' => 'OK', 'data' => $result]);
2237
2238        } catch (\Exception $e) {
2239            report(AppException::fromException($e, 'GET_REVENUE_PER_MONTH_EXCEPTION'));
2240            return response(['message' => 'KO', 'error' => $e->getMessage()]);
2241        }
2242    }
2243}