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