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