Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
0.00% covered (danger)
0.00%
0 / 1045
0.00% covered (danger)
0.00%
0 / 15
CRAP
0.00% covered (danger)
0.00%
0 / 1
DigitalCampaignAnalytics
0.00% covered (danger)
0.00%
0 / 1045
0.00% covered (danger)
0.00%
0 / 15
58806
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
 list_campaign_channel_and_landing
0.00% covered (danger)
0.00%
0 / 35
0.00% covered (danger)
0.00%
0 / 1
342
 list_orders_ia
0.00% covered (danger)
0.00%
0 / 31
0.00% covered (danger)
0.00%
0 / 1
240
 list_lead_status
0.00% covered (danger)
0.00%
0 / 38
0.00% covered (danger)
0.00%
0 / 1
306
 toCamelCase
0.00% covered (danger)
0.00%
0 / 7
0.00% covered (danger)
0.00%
0 / 1
12
 list_time_between_status
0.00% covered (danger)
0.00%
0 / 122
0.00% covered (danger)
0.00%
0 / 1
110
 list_g3w_order_status
0.00% covered (danger)
0.00%
0 / 105
0.00% covered (danger)
0.00%
0 / 1
600
 get_sources_digital_campaign_analytics
0.00% covered (danger)
0.00%
0 / 16
0.00% covered (danger)
0.00%
0 / 1
6
 update_final_summary
0.00% covered (danger)
0.00%
0 / 10
0.00% covered (danger)
0.00%
0 / 1
6
 get_final_summary
0.00% covered (danger)
0.00%
0 / 47
0.00% covered (danger)
0.00%
0 / 1
90
 get_main_kpis
0.00% covered (danger)
0.00%
0 / 163
0.00% covered (danger)
0.00%
0 / 1
992
 list_type_of_order
0.00% covered (danger)
0.00%
0 / 149
0.00% covered (danger)
0.00%
0 / 1
1260
 getG3wTasksExecuted
0.00% covered (danger)
0.00%
0 / 12
0.00% covered (danger)
0.00%
0 / 1
6
 list_performance_metrics
0.00% covered (danger)
0.00%
0 / 73
0.00% covered (danger)
0.00%
0 / 1
342
 get_kpi_big_query
0.00% covered (danger)
0.00%
0 / 224
0.00% covered (danger)
0.00%
0 / 1
2756
1<?php
2
3namespace App\Http\Controllers;
4
5use App\Exceptions\AppException;
6use App\Models\TblBudgetTypeGroups;
7use App\Models\TblBudgetTypes;
8use App\Models\TblCompanies;
9use App\Models\TblFinalSummary;
10use App\Models\TblSources;
11use App\Services\BigQueryService;
12use App\Services\UserCompanies;
13use App\Services\WorkService;
14use Illuminate\Contracts\Routing\ResponseFactory;
15use Illuminate\Http\Request;
16use Illuminate\Http\Response;
17use Illuminate\Support\Facades\App;
18use Illuminate\Support\Facades\Cache;
19use Illuminate\Support\Facades\DB;
20use Illuminate\Support\Facades\Log;
21
22class DigitalCampaignAnalytics extends Controller
23{
24    private $locale;
25
26    private $userId;
27
28    private $region;
29
30    private $companyIds;
31
32    private readonly string $companyId;
33
34    public function __construct(private readonly WorkService $workService)
35    {
36        $this->locale = request()->header('Locale-Id');
37        $this->userId = request()->header('User-Id');
38        $this->region = request()->header('Region');
39
40        App::setLocale($this->locale);
41
42        $this->companyIds = [];
43
44        if ($this->region != null && $this->region != '' && $this->region != 'All') {
45            $this->region = urldecode((string) $this->region);
46
47            $query = 'SELECT
48                        b.company_id
49                    FROM
50                        tbl_company_users a
51                        LEFT JOIN tbl_companies b ON a.company_id = b.company_id
52                    WHERE
53                        a.user_id = ?
54                        AND b.region = ?';
55
56            $this->companyIds = DB::select($query, [intval($this->userId), $this->region]);
57
58            $this->companyIds = collect($this->companyIds)->pluck('company_id')->toArray();
59        } else {
60            // FIRE-1146: prefer the middleware-resolved attribute; fall back to a fresh fetch only on unattached paths.
61            $this->companyIds = request()->attributes->get('user_company_ids', UserCompanies::forUser((int) $this->userId));
62        }
63
64        $this->companyId = implode(',', $this->companyIds);
65    }
66
67    public function list_campaign_channel_and_landing(Request $request): ResponseFactory|Response
68    {
69
70        try {
71
72            $data = $request->all();
73
74            $companyId = addslashes((string) $data['company_id']);
75            $where = '';
76
77            if ($companyId != 0) {
78                $where .= " AND q.company_id = {$companyId} ";
79            } else {
80                $where .= " AND q.company_id IN ({$this->companyId}) ";
81            }
82
83            if (isset($data['commercial']) && $data['commercial'] != null) {
84                $commercial = implode("','", $data['commercial']);
85                if (count($data['commercial']) > 0) {
86                    $where .= " AND q.commercial IN ('{$commercial}') ";
87                }
88            }
89
90            if (isset($data['source_id']) && $data['source_id'] != null) {
91                $sourceId = implode("','", $data['source_id']);
92                if (count($data['source_id']) > 0) {
93                    $where .= " AND q.source_id IN ('{$sourceId}') ";
94                }
95            }
96
97            if ((isset($data['start_date']) && $data['start_date'] != null) && (isset($data['end_date']) && $data['end_date'] != null)) {
98                $where .= " AND q.request_date BETWEEN '{$data['start_date']}' AND '{$data['end_date']}' ";
99            }
100
101            $sortBy = '';
102
103            if (isset($data['sort_by']) && $data['sort_by'] != null) {
104                if (isset($data['column']) && $data['column'] != null) {
105                    $sortBy = ", {$data['column']} {$data['sort_by']} ";
106                }
107            }
108
109            $query = "SELECT q.* FROM
110                        (
111                            SELECT
112                                c.region,
113                                s.source_id,
114                                s.name source,
115                                COUNT(1) totalOrders,
116                                COUNT(CASE WHEN q.acceptance_date IS NOT NULL AND q.acceptance_date != '0000-00-00 00:00:00' THEN 1 END) totalOrdersAcceptance,
117                                COUNT(CASE WHEN q.acceptance_date IS NOT NULL AND q.acceptance_date != '0000-00-00 00:00:00' THEN 1 END) / COUNT(1) * 100 percentageOrders,
118                                SUM(q.amount) totalOrdersAmount,
119                                AVG(q.amount) averageOrdersAmount,
120                                SUM(CASE WHEN q.acceptance_date IS NOT NULL AND q.acceptance_date != '0000-00-00 00:00:00' THEN q.amount END) totalOrdersAcceptanceAmount,
121                                AVG(CASE WHEN q.acceptance_date IS NOT NULL AND q.acceptance_date != '0000-00-00 00:00:00' THEN q.amount END) averageOrdersAcceptanceAmount,
122                                SUM(CASE WHEN q.acceptance_date IS NOT NULL AND q.acceptance_date != '0000-00-00 00:00:00' THEN q.amount END) / SUM(q.amount) * 100 percentageOrdersAmount,
123                                GROUP_CONCAT(q.id) groupConcatIdsTotalOrders,
124                                GROUP_CONCAT(CASE WHEN q.acceptance_date IS NOT NULL AND q.acceptance_date != '0000-00-00 00:00:00' THEN q.id END) groupConcatIdsOrdersAcceptance,
125                                GROUP_CONCAT(CASE WHEN q.acceptance_date IS NOT NULL AND q.acceptance_date != '0000-00-00 00:00:00' THEN q.amount END) groupConcatIdsOrdersAcceptanceAmount
126                            FROM tbl_quotations q
127                            LEFT JOIN tbl_sources s
128                                ON q.source_id = s.source_id
129                            LEFT JOIN tbl_companies c
130                                ON q.company_id = c.company_id
131                            WHERE
132                                q.for_add = 0
133                                AND (q.commercial IS NOT NULL AND q.commercial != '')
134                                AND q.source_id IS NOT NULL
135                                AND s.digital_campaign_source > 0
136                                AND q.budget_status_id != 18
137                                {$where}
138                            GROUP BY c.region, s.source_id WITH ROLLUP
139                            ORDER BY
140                                (c.region IS NULL AND s.source_id IS NULL) ASC,
141                                c.region IS NULL ASC,
142                                c.region ASC,
143                                (s.source_id IS NULL) DESC,
144                                CASE WHEN s.source_id IS NOT NULL THEN COUNT(1) ELSE NULL END DESC
145                        ) q ORDER BY (q.region IS NULL AND q.source_id IS NULL) ASC, q.region ASC, (q.source_id IS NULL) DESC {$sortBy}";
146
147            $value = Cache::get(base64_encode($query));
148
149            if (! $value) {
150                $result = DB::select($query);
151
152                Cache::put(base64_encode($query), $result, 600);
153            } else {
154                $result = $value;
155            }
156
157            return response([
158                'message' => 'OK',
159                'data' => $result,
160            ]);
161
162        } catch (\Exception $e) {
163            report(AppException::fromException($e, 'DIGITAL_CAMPAIGN_CHANNEL_LANDING_EXCEPTION'));
164
165            return response(['message' => 'KO', 'error' => $e->getMessage()]);
166        }
167    }
168
169    public function list_orders_ia(Request $request): ResponseFactory|Response
170    {
171
172        try {
173
174            $data = $request->all();
175
176            $companyId = addslashes((string) $data['company_id']);
177            $where = '';
178
179            if ($companyId != 0) {
180                $where .= " AND q.company_id = {$companyId} ";
181            } else {
182                $where .= " AND q.company_id IN ({$this->companyId}) ";
183            }
184
185            if (isset($data['commercial']) && $data['commercial'] != null) {
186                $commercial = implode("','", $data['commercial']);
187                if (count($data['commercial']) > 0) {
188                    $where .= " AND q.commercial IN ('{$commercial}') ";
189                }
190            }
191
192            if ((isset($data['start_date']) && $data['start_date'] != null) && (isset($data['end_date']) && $data['end_date'] != null)) {
193                $where .= " AND q.request_date BETWEEN '{$data['start_date']}' AND '{$data['end_date']}' ";
194            }
195
196            $sortBy = '';
197
198            if (isset($data['sort_by']) && $data['sort_by'] != null) {
199                if (isset($data['column']) && $data['column'] != null) {
200                    $sortBy = ", {$data['column']} {$data['sort_by']} ";
201                }
202            }
203
204            $query = "SELECT q.* FROM
205                        (
206                            SELECT
207                                c.region,
208                                s.source_id,
209                                s.name source,
210                                COUNT(1) totalOrders,
211                                COUNT(CASE WHEN q.acceptance_date IS NOT NULL AND q.acceptance_date != '0000-00-00 00:00:00' THEN 1 END) totalOrdersAcceptance,
212                                COUNT(CASE WHEN q.acceptance_date IS NOT NULL AND q.acceptance_date != '0000-00-00 00:00:00' THEN 1 END) / COUNT(1) * 100 percentageOrders,
213                                SUM(q.amount) totalOrdersAmount,
214                                AVG(q.amount) averageOrdersAmount,
215                                SUM(CASE WHEN q.acceptance_date IS NOT NULL AND q.acceptance_date != '0000-00-00 00:00:00' THEN q.amount END) totalOrdersAcceptanceAmount,
216                                AVG(CASE WHEN q.acceptance_date IS NOT NULL AND q.acceptance_date != '0000-00-00 00:00:00' THEN q.amount END) averageOrdersAcceptanceAmount,
217                                SUM(CASE WHEN q.acceptance_date IS NOT NULL AND q.acceptance_date != '0000-00-00 00:00:00' THEN q.amount END) / SUM(q.amount) * 100 percentageOrdersAmount,
218                                GROUP_CONCAT(q.id) groupConcatIdsTotalOrders,
219                                GROUP_CONCAT(CASE WHEN q.acceptance_date IS NOT NULL AND q.acceptance_date != '0000-00-00 00:00:00' THEN q.id END) groupConcatIdsOrdersAcceptance,
220                                GROUP_CONCAT(CASE WHEN q.acceptance_date IS NOT NULL AND q.acceptance_date != '0000-00-00 00:00:00' THEN q.amount END) groupConcatIdsOrdersAcceptanceAmount
221                            FROM tbl_quotations q
222                            LEFT JOIN tbl_sources s
223                                ON q.source_id = s.source_id
224                            LEFT JOIN tbl_companies c
225                                ON q.company_id = c.company_id
226                            WHERE
227                                q.for_add = 0
228                                AND q.created_by = 'IA'
229                                {$where}
230                            GROUP BY c.region, s.source_id WITH ROLLUP
231                            ORDER BY
232                                (c.region IS NULL AND s.source_id IS NULL) ASC,
233                                c.region IS NULL ASC,
234                                c.region ASC,
235                                (s.source_id IS NULL) DESC,
236                                CASE WHEN s.source_id IS NOT NULL THEN COUNT(1) ELSE NULL END DESC
237                        ) q ORDER BY (q.region IS NULL AND q.source_id IS NULL) ASC, q.region ASC, (q.source_id IS NULL) DESC {$sortBy}";
238
239            $value = Cache::get(base64_encode($query));
240
241            if (! $value) {
242                $result = DB::select($query);
243
244                Cache::put(base64_encode($query), $result, 600);
245            } else {
246                $result = $value;
247            }
248
249            return response([
250                'message' => 'OK',
251                'data' => $result,
252            ]);
253
254        } catch (\Exception $e) {
255            report(AppException::fromException($e, 'DIGITAL_CAMPAIGN_CHANNEL_LANDING_EXCEPTION'));
256
257            return response(['message' => 'KO', 'error' => $e->getMessage()]);
258        }
259    }
260
261    public function list_lead_status(Request $request): ResponseFactory|Response
262    {
263
264        try {
265
266            $data = $request->all();
267
268            $companyId = addslashes((string) $data['company_id']);
269            $where = '';
270
271            if (isset($data['commercial']) && $data['commercial'] != null) {
272                $commercial = implode("','", $data['commercial']);
273                if (count($data['commercial']) > 0) {
274                    $where .= " AND q.commercial IN ('{$commercial}') ";
275                }
276            }
277
278            if (isset($data['source_id']) && $data['source_id'] != null) {
279                $sourceId = implode("','", $data['source_id']);
280                if (count($data['source_id']) > 0) {
281                    $where .= " AND q.source_id IN ('{$sourceId}') ";
282                }
283            }
284
285            if (isset($data['region']) && $data['region'] != null) {
286                $c = TblCompanies::whereIn('region', $data['region'])->pluck('company_id')->toArray();
287
288                if ($c) {
289                    $companyIds = implode(',', $c);
290                    $where .= " AND q.company_id IN ({$companyIds})";
291                }
292            } else {
293                if ($companyId != 0) {
294                    $where .= " AND q.company_id = {$companyId} ";
295                } else {
296                    $where .= " AND q.company_id IN ({$this->companyId}) ";
297                }
298            }
299
300            if ((isset($data['start_date']) && $data['start_date'] != null) && (isset($data['end_date']) && $data['end_date'] != null)) {
301                $where .= " AND q.created_at BETWEEN '{$data['start_date']}' AND '{$data['end_date']}' ";
302            }
303
304            $query = "SELECT
305                        bs.name AS 'status',
306                        s.name AS 'source',
307                        grouped.groupConcatIds,
308                        grouped.totalOrders,
309                        CAST(grouped.totalOrders / (case when s.name is NOT null then status_subtotals.totalOrders else overall.totalOrders END) * 100 AS DOUBLE) averageTotalOrders,
310                        grouped.totalAmount,
311                        CAST(grouped.totalAmount / (CASE WHEN s.name IS NOT NULL THEN status_subtotals.totalAmount ELSE overall.totalAmount END) * 100 AS DOUBLE) averageTotalAmount
312                    FROM (
313                        SELECT
314                            GROUP_CONCAT(q.id) groupConcatIds,
315                            q.budget_status_id,
316                            q.source_id,
317                            q.issue_date,
318                            COUNT(1) AS totalOrders,
319                            SUM(q.amount) AS totalAmount
320                        FROM tbl_quotations q
321                        LEFT JOIN tbl_sources s ON q.source_id = s.source_id                        
322                        WHERE
323                            q.for_add = 0
324                            AND (q.commercial IS NOT NULL AND q.commercial != '')
325                            AND q.source_id > 0
326                            AND q.budget_status_id > 0
327                            AND q.budget_status_id != 18
328                            AND s.digital_campaign_source > 0
329                            {$where}
330                        GROUP BY q.budget_status_id, q.source_id WITH ROLLUP
331                    ) AS grouped
332                    LEFT JOIN tbl_budget_status bs ON grouped.budget_status_id = bs.budget_status_id
333                    LEFT JOIN tbl_sources s ON grouped.source_id = s.source_id
334                    LEFT JOIN (
335                        SELECT
336                            q.budget_status_id,
337                            COUNT(1) AS totalOrders,
338                            SUM(q.amount) AS totalAmount
339                        FROM tbl_quotations q
340                            LEFT JOIN tbl_sources s ON q.source_id = s.source_id
341                        WHERE
342                            q.for_add = 0
343                            AND q.commercial IS NOT NULL AND q.commercial != ''
344                            AND q.budget_status_id > 0
345                            AND q.budget_status_id != 18
346                            AND s.digital_campaign_source > 0
347                            {$where}
348                        GROUP BY q.budget_status_id
349                    ) AS status_subtotals ON grouped.budget_status_id = status_subtotals.budget_status_id
350                    CROSS JOIN (
351                        SELECT
352                            COUNT(1) AS totalOrders,
353                            SUM(q.amount) AS totalAmount
354                        FROM tbl_quotations q
355                        LEFT JOIN tbl_sources s ON q.source_id = s.source_id
356                        WHERE
357                            q.for_add = 0
358                            AND (q.commercial IS NOT NULL AND q.commercial != '')
359                            AND q.source_id > 0
360                            AND q.budget_status_id > 0
361                            AND q.budget_status_id != 18
362                            AND s.digital_campaign_source > 0
363                            {$where}
364                    ) AS overall
365                    ORDER BY
366                        CASE
367                            WHEN grouped.budget_status_id IS NULL AND grouped.source_id IS NULL THEN 2
368                            ELSE 1
369                        END,
370                        bs.priority ASC,
371                        CASE
372                            WHEN grouped.source_id IS NULL THEN 0
373                            ELSE 1
374                        END,
375                        s.priority ASC";
376
377            $value = Cache::get(base64_encode($query));
378
379            if (! $value) {
380                $result = DB::select($query);
381
382                Cache::put(base64_encode($query), $result, 600);
383            } else {
384                $result = $value;
385            }
386
387            return response([
388                'message' => 'OK',
389                'data' => $result,
390            ]);
391
392        } catch (\Exception $e) {
393            report(AppException::fromException($e, 'LIST_LEAD_STATUS_EXCEPTION'));
394
395            return response(['message' => 'KO', 'error' => $e->getMessage()]);
396        }
397    }
398
399    public function toCamelCase($string): string
400    {
401        $cleaned = preg_replace('/[^a-zA-Z0-9]+/', ' ', (string) $string);
402        $words = explode(' ', trim((string) $cleaned));
403
404        $camelCased = '';
405        foreach ($words as $word) {
406            if ($word !== '') {
407                $camelCased .= ucfirst(strtolower($word));
408            }
409        }
410
411        return $camelCased;
412    }
413
414    public function list_time_between_status(Request $request): ResponseFactory|Response
415    {
416
417        try {
418
419            $data = $request->all();
420
421            $companyId = addslashes((string) $data['company_id']);
422            $where = '';
423
424            if ($companyId != 0) {
425                $where .= " AND q.company_id = {$companyId} ";
426            } else {
427                $where .= " AND q.company_id IN ({$this->companyId}) ";
428            }
429
430            if (isset($data['commercial']) && $data['commercial'] != null) {
431                $commercial = implode("','", $data['commercial']);
432                if (count($data['commercial']) > 0) {
433                    $where .= " AND q.commercial IN ('{$commercial}') ";
434                }
435            }
436
437            $averageDateCol = '';
438            $averageDate = 'q.created_at'; // Fallback to avoid undefined variable in SQL if conditional is unmet
439
440            if (isset($data['average_date']) && count($data['average_date']) == 2) {
441                foreach ($data['average_date'] as $k => $v) {
442                    $data['average_date'][$k] = "q.{$v}";
443                }
444
445                $averageDate = implode(',', $data['average_date']);
446            }
447
448            $query = "SELECT
449                        -- Less than 1 day
450                        COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) < 1 THEN 1 END) AS totalRowsLt1,
451                        SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) < 1 THEN amount ELSE 0 END) AS totalAmountLt1,
452                        GROUP_CONCAT(CASE WHEN ABS(DATEDIFF({$averageDate})) < 1 THEN id END) AS groupConcatIdsLt1,
453
454                        -- 1 to 3 days
455                        COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) >= 1
456                                AND ABS(DATEDIFF({$averageDate})) <= 3 THEN 1 END) AS totalRows1To3,
457                        SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) >= 1
458                                AND ABS(DATEDIFF({$averageDate})) <= 3 THEN amount ELSE 0 END) AS totalAmount1To3,
459                        GROUP_CONCAT(CASE WHEN ABS(DATEDIFF({$averageDate})) >= 1 AND ABS(DATEDIFF({$averageDate})) <= 3 THEN id END) AS groupConcatIds1To3,
460
461                        -- More than 3 to 7 days
462                        COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 3
463                                AND ABS(DATEDIFF({$averageDate})) <= 7 THEN 1 END) AS totalRows3To7,
464                        SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) > 3
465                                AND ABS(DATEDIFF({$averageDate})) <= 7 THEN amount ELSE 0 END) AS totalAmount3To7,
466                        GROUP_CONCAT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 3 AND ABS(DATEDIFF({$averageDate})) <= 7 THEN id END) AS groupConcatIds3To7,
467
468                        -- More than 7 to 15 days
469                        COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 7
470                                AND ABS(DATEDIFF({$averageDate})) <= 15 THEN 1 END) AS totalRows7To15,
471                        SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) > 7
472                                AND ABS(DATEDIFF({$averageDate})) <= 15 THEN amount ELSE 0 END) AS totalAmount7To15,
473                        GROUP_CONCAT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 7 AND ABS(DATEDIFF({$averageDate})) <= 15 THEN id END) AS groupConcatIds7To15,
474
475                        -- More than 15 to 30 days
476                        COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 15
477                                AND ABS(DATEDIFF({$averageDate})) <= 30 THEN 1 END) AS totalRows15To30,
478                        SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) > 15
479                                AND ABS(DATEDIFF({$averageDate})) <= 30 THEN amount ELSE 0 END) AS totalAmount15To30,
480                        GROUP_CONCAT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 15 AND ABS(DATEDIFF({$averageDate})) <= 30 THEN id END) AS groupConcatIds15To30,
481
482                        -- More than 30 days
483                        COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 30 THEN 1 END) AS totalRowsGt30,
484                        SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) > 30 THEN amount ELSE 0 END) AS totalAmountGt30,
485                        GROUP_CONCAT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 30 THEN id END) AS groupConcatIdsGt30,
486
487
488
489                        -- Less than 1 day Acceptance
490                        COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) < 1 AND q.budget_status_id = 3 THEN 1 END) AS totalRowsLt1Acceptance,
491                        SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) < 1 AND q.budget_status_id = 3 THEN amount ELSE 0 END) AS totalAmountLt1Acceptance,
492                        GROUP_CONCAT(CASE WHEN ABS(DATEDIFF({$averageDate})) < 1 AND q.budget_status_id = 3 THEN id END) AS groupConcatIdsLt1Acceptance,
493
494                        -- 1 to 3 days Acceptance
495                        COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) >= 1
496                                AND ABS(DATEDIFF({$averageDate})) <= 3 AND q.budget_status_id = 3 THEN 1 END) AS totalRows1To3Acceptance,
497                        SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) >= 1
498                                AND ABS(DATEDIFF({$averageDate})) <= 3 AND q.budget_status_id = 3 THEN amount ELSE 0 END) AS totalAmount1To3Acceptance,
499                        GROUP_CONCAT(CASE WHEN ABS(DATEDIFF({$averageDate})) >= 1 AND ABS(DATEDIFF({$averageDate})) <= 3 AND q.budget_status_id = 3 THEN id END) AS groupConcatIds1To3Acceptance,
500
501                        -- More than 3 to 7 days Acceptance
502                        COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 3
503                                AND ABS(DATEDIFF({$averageDate})) <= 7 AND q.budget_status_id = 3 THEN 1 END) AS totalRows3To7Acceptance,
504                        SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) > 3
505                                AND ABS(DATEDIFF({$averageDate})) <= 7 AND q.budget_status_id = 3 THEN amount ELSE 0 END) AS totalAmount3To7Acceptance,
506                        GROUP_CONCAT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 3 AND ABS(DATEDIFF({$averageDate})) <= 7 AND q.budget_status_id = 3 THEN id END) AS groupConcatIds3To7Acceptance,
507
508                        -- More than 7 to 15 days Acceptance
509                        COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 7
510                                AND ABS(DATEDIFF({$averageDate})) <= 15 AND q.budget_status_id = 3 THEN 1 END) AS totalRows7To15Acceptance,
511                        SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) > 7
512                                AND ABS(DATEDIFF({$averageDate})) <= 15 AND q.budget_status_id = 3 THEN amount ELSE 0 END) AS totalAmount7To15Acceptance,
513                        GROUP_CONCAT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 7 AND ABS(DATEDIFF({$averageDate})) <= 15 AND q.budget_status_id = 3 THEN id END) AS groupConcatIds7To15Acceptance,
514
515                        -- More than 15 to 30 days Acceptance
516                        COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 15
517                                AND ABS(DATEDIFF({$averageDate})) <= 30 AND q.budget_status_id = 3 THEN 1 END) AS totalRows15To30Acceptance,
518                        SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) > 15
519                                AND ABS(DATEDIFF({$averageDate})) <= 30 AND q.budget_status_id = 3 THEN amount ELSE 0 END) AS totalAmount15To30Acceptance,
520                        GROUP_CONCAT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 15 AND ABS(DATEDIFF({$averageDate})) <= 30 AND q.budget_status_id = 3 THEN id END) AS groupConcatIds15To30Acceptance,
521
522                        -- More than 30 days Acceptance
523                        COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 30 AND q.budget_status_id = 3 THEN 1 END) AS totalRowsGt30Acceptance,
524                        SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) > 30 AND q.budget_status_id = 3 THEN amount ELSE 0 END) AS totalAmountGt30Acceptance,
525                        GROUP_CONCAT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 30 AND q.budget_status_id = 3 THEN id END) AS groupConcatIdsGt30Acceptance,
526
527
528
529                        -- Less than 1 day Acceptance / Issue
530                        COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) < 1 AND q.budget_status_id = 3 THEN 1 END) /
531                        COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) < 1 THEN 1 END) * 100 AS totalRowsLt1AcceptanceIssue,
532                        SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) < 1 AND q.budget_status_id = 3 THEN amount ELSE 0 END) /
533                        SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) < 1 THEN amount ELSE 0 END) * 100 AS totalAmountLt1AcceptanceIssue,
534
535                        -- 1 to 3 days Acceptance / Issue
536                        COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) >= 1
537                                AND ABS(DATEDIFF({$averageDate})) <= 3 AND q.budget_status_id = 3 THEN 1 END) /
538                        COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) >= 1
539                                AND ABS(DATEDIFF({$averageDate})) <= 3 THEN 1 END) * 100 AS totalRows1To3AcceptanceIssue,
540                        SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) >= 1
541                                AND ABS(DATEDIFF({$averageDate})) <= 3 AND q.budget_status_id = 3 THEN amount ELSE 0 END) /
542                        SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) >= 1
543                                AND ABS(DATEDIFF({$averageDate})) <= 3 THEN amount ELSE 0 END) * 100 AS totalAmount1To3AcceptanceIssue,
544
545                        -- More than 3 to 7 days Acceptance / Issue
546                        COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 3
547                                AND ABS(DATEDIFF({$averageDate})) <= 7 AND q.budget_status_id = 3 THEN 1 END) /
548                        COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 3
549                                AND ABS(DATEDIFF({$averageDate})) <= 7 THEN 1 END) * 100 AS totalRows3To7AcceptanceIssue,
550                        SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) > 3
551                                AND ABS(DATEDIFF({$averageDate})) <= 7 AND q.budget_status_id = 3 THEN amount ELSE 0 END) /
552                        SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) > 3
553                                AND ABS(DATEDIFF({$averageDate})) <= 7 THEN amount ELSE 0 END) * 100 AS totalAmount3To7AcceptanceIssue,
554
555                        -- More than 7 to 15 days Acceptance / Issue
556                        COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 7
557                                AND ABS(DATEDIFF({$averageDate})) <= 15 AND q.budget_status_id = 3 THEN 1 END) /
558                        COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 7
559                                AND ABS(DATEDIFF({$averageDate})) <= 15 THEN 1 END) * 100 AS totalRows7To15AcceptanceIssue,
560                        SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) > 7
561                                AND ABS(DATEDIFF({$averageDate})) <= 15 AND q.budget_status_id = 3 THEN amount ELSE 0 END) /
562                        SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) > 7
563                                AND ABS(DATEDIFF({$averageDate})) <= 15 THEN amount ELSE 0 END) * 100 AS totalAmount7To15AcceptanceIssue,
564
565
566                        -- More than 15 to 30 days Acceptance / Issue
567                        COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 15
568                                AND ABS(DATEDIFF({$averageDate})) <= 30 AND q.budget_status_id = 3 THEN 1 END) /
569                        COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 15
570                                AND ABS(DATEDIFF({$averageDate})) <= 30 THEN 1 END) * 100 AS totalRows15To30AcceptanceIssue,
571                        SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) > 15
572                                AND ABS(DATEDIFF({$averageDate})) <= 30 AND q.budget_status_id = 3 THEN amount ELSE 0 END) /
573                        SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) > 15
574                                AND ABS(DATEDIFF({$averageDate})) <= 30 THEN amount ELSE 0 END) * 100 AS totalAmount15To30AcceptanceIssue,
575
576                        -- More than 30 days Acceptance / Issue
577                        COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 30 AND q.budget_status_id = 3 THEN 1 END) /
578                        COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 30 THEN 1 END) * 100 AS totalRowsGt30AcceptanceIssueIssue,
579                        SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) > 30 AND q.budget_status_id = 3 THEN amount ELSE 0 END) /
580                        SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) > 30 THEN amount ELSE 0 END) * 100 AS totalAmountGt30AcceptanceIssue
581                    FROM tbl_quotations q
582                        LEFT JOIN tbl_sources s ON q.source_id = s.source_id
583                    WHERE
584                        q.for_add = 0
585                        AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
586                        AND (q.commercial IS NOT NULL AND q.commercial != '')
587                        AND q.budget_type_id != 7
588                        AND q.budget_type_id IS NOT NULL
589                        AND q.source_id > 0
590                        AND q.budget_status_id > 0
591                        AND q.budget_status_id != 18
592                        AND s.digital_campaign_source > 0
593                        {$where}";
594
595            $value = Cache::get(base64_encode($query));
596
597            if (! $value) {
598                $result = DB::select($query);
599
600                Cache::put(base64_encode($query), $result, 600);
601            } else {
602                $result = $value;
603            }
604
605            return response([
606                'message' => 'OK',
607                'data' => $result,
608            ]);
609
610        } catch (\Exception $e) {
611            report(AppException::fromException($e, 'LIST_TIME_BETWEEN_STATUS_EXCEPTION'));
612
613            return response(['message' => 'KO', 'error' => $e->getMessage()]);
614        }
615    }
616
617    public function list_g3w_order_status(Request $request): ResponseFactory|Response
618    {
619
620        try {
621
622            $data = $request->all();
623
624            $companyId = addslashes((string) $data['company_id']);
625            $where = '';
626
627            if ($companyId != 0) {
628                $where .= " AND q.company_id = {$companyId} ";
629            } else {
630                $where .= " AND q.company_id IN ({$this->companyId}) ";
631            }
632
633            if (isset($data['commercial']) && $data['commercial'] != null) {
634                $commercial = implode("','", $data['commercial']);
635                if (count($data['commercial']) > 0) {
636                    $where .= " AND q.commercial IN ('{$commercial}') ";
637                }
638            }
639
640            $dataToDisplay = $data['data_to_display'] ?? 1;
641
642            $col = '1';
643
644            if ($dataToDisplay == 2) {
645                $col = 'q.amount';
646            }
647
648            $columns = '';
649            $groupedColumns = '';
650
651            $statusByG3w = [];
652
653            $workStatusAndAttributes = ['Finalizado', 'Facturado', 'Cerrado', 'Verificado'];
654
655            if (isset($data['status_by_g3w']) && count($data['status_by_g3w']) > 0) {
656                $statusByG3w = $data['status_by_g3w'];
657
658                foreach ($statusByG3w as $b) {
659                    $z = $this->toCamelCase($b);
660
661                    if (! in_array($b, $workStatusAndAttributes)) {
662                        $columns .= " COALESCE(SUM(CASE WHEN q.status_by_g3w = '{$b}' THEN {$col} END), 0) AS 'total{$z}', ";
663                        $columns .= " GROUP_CONCAT(CASE WHEN q.status_by_g3w = '{$b}' THEN q.id END) AS 'groupConcatIds{$z}', ";
664                    } else {
665                        if ($b == 'Finalizado') {
666                            $columns .= " COALESCE(SUM(CASE WHEN bw.work_status = 'Finalizado' THEN {$col} END), 0) AS 'total{$z}', ";
667                            $columns .= " GROUP_CONCAT(CASE WHEN bw.work_status = 'Finalizado' THEN q.id END) AS 'groupConcatIds{$z}', ";
668                        }
669
670                        if ($b == 'Facturado') {
671                            $columns .= " COALESCE(SUM(CASE WHEN bw.work_status = 'Facturado' THEN {$col} END), 0) AS 'total{$z}', ";
672                            $columns .= " GROUP_CONCAT(CASE WHEN bw.work_status = 'Facturado' THEN q.id END) AS 'groupConcatIds{$z}', ";
673                        }
674
675                        if ($b == 'Cerrado') {
676                            $columns .= " COALESCE(SUM(CASE WHEN bw.closed_attribute = '1' THEN {$col} END), 0) AS 'total{$z}', ";
677                            $columns .= " GROUP_CONCAT(CASE WHEN bw.closed_attribute = '1' THEN q.id END) AS 'groupConcatIds{$z}', ";
678                        }
679
680                        if ($b == 'Verificado') {
681                            $columns .= " COALESCE(SUM(CASE WHEN bw.verified_attribute = '1' THEN {$col} END), 0) AS 'total{$z}', ";
682                            $columns .= " GROUP_CONCAT(CASE WHEN bw.verified_attribute = '1' THEN q.id END) AS 'groupConcatIds{$z}', ";
683                        }
684                    }
685
686                    if ($dataToDisplay == 3) {
687                        $groupedColumns .= " CAST(grouped.total{$z} / (CASE WHEN s.name IS NOT NULL THEN subtotals.total{$z} ELSE overall.total{$z} END) * 100 AS DOUBLE) 'total{$z}', ";
688                    } else {
689                        $groupedColumns .= " grouped.total{$z}, ";
690                    }
691
692                    $groupedColumns .= " grouped.groupConcatIds{$z}, ";
693                }
694            }
695
696            $query = "SELECT
697                            c.name AS company_name,
698                            s.name AS source,
699                            {$groupedColumns}
700                            grouped.company_id
701                        FROM (
702                            SELECT
703                            q.company_id,
704                            {$columns}
705                            q.source_id
706                            FROM tbl_quotations q
707                            LEFT JOIN tbl_sources s ON q.source_id = s.source_id
708                            INNER JOIN tbl_box_work_g3w_mapping bw ON q.box_work_g3w = bw.box_work_g3w
709                            WHERE
710                                q.for_add = 0
711                                AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
712                                AND (q.commercial IS NOT NULL AND q.commercial != '')
713                                AND q.budget_type_id != 7
714                                AND q.budget_type_id IS NOT NULL
715                                AND q.source_id > 0
716                                AND q.budget_status_id > 0
717                                AND q.budget_status_id != 18
718                                AND s.digital_campaign_source > 0
719                                {$where}
720                            GROUP BY q.company_id, q.source_id WITH ROLLUP
721                        ) AS grouped
722                        LEFT JOIN tbl_sources s ON grouped.source_id = s.source_id
723                        LEFT JOIN tbl_companies c ON grouped.company_id = c.company_id
724                        LEFT JOIN (
725                            SELECT
726                            q.company_id,
727                            {$columns}
728                            q.source_id
729                            FROM tbl_quotations q
730                            LEFT JOIN tbl_sources s ON q.source_id = s.source_id
731                            INNER JOIN tbl_box_work_g3w_mapping bw ON q.box_work_g3w = bw.box_work_g3w
732                            WHERE
733                                q.for_add = 0
734                                AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
735                                AND (q.commercial IS NOT NULL AND q.commercial != '')
736                                AND q.budget_type_id != 7
737                                AND q.budget_type_id IS NOT NULL
738                                AND q.source_id > 0
739                                AND q.budget_status_id > 0
740                                AND q.budget_status_id != 18
741                                AND s.digital_campaign_source > 0
742                                {$where}
743                            GROUP BY q.company_id
744                        ) AS subtotals ON grouped.company_id = subtotals.company_id
745                        CROSS JOIN (
746                            SELECT
747                            q.company_id,
748                            {$columns}
749                            q.source_id
750                            FROM tbl_quotations q
751                            LEFT JOIN tbl_sources s ON q.source_id = s.source_id
752                            INNER JOIN tbl_box_work_g3w_mapping bw ON q.box_work_g3w = bw.box_work_g3w
753                            WHERE
754                                q.for_add = 0
755                                AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
756                                AND (q.commercial IS NOT NULL AND q.commercial != '')
757                                AND q.budget_type_id != 7
758                                AND q.budget_type_id IS NOT NULL
759                                AND q.source_id > 0
760                                AND q.budget_status_id > 0
761                                AND q.budget_status_id != 18
762                                AND s.digital_campaign_source > 0
763                                {$where}
764                        ) overall
765                        ORDER BY
766                            CASE
767                                WHEN grouped.company_id IS NULL AND grouped.source_id IS NULL THEN 2
768                                ELSE 1
769                            END,
770                            c.name ASC,
771                            CASE
772                                WHEN grouped.source_id IS NULL THEN 0
773                                ELSE 1
774                            END,
775                            s.priority ASC";
776
777            $value = Cache::get(base64_encode($query));
778
779            if (! $value) {
780                $result = DB::select($query);
781
782                Cache::put(base64_encode($query), $result, 600);
783            } else {
784                $result = $value;
785            }
786
787            $labelMap = [
788                'totalAceptado' => 'Aceptado',
789                'totalEnviado' => 'Enviado',
790                'totalAnulado' => 'Anulado',
791                'totalRechazado' => 'Rechazado',
792                'totalNuevo' => 'Nuevo',
793                'totalListoParaEnviar' => 'Listo para enviar',
794                'totalRechazadoAutomTicamente' => 'Rechazado - automáticamente',
795                'totalEnProceso' => 'En proceso',
796                'totalFinalizado' => 'Finalizado',
797                'totalFacturado' => 'Facturado',
798                'totalCerrado' => 'Cerrado',
799                'totalVerificado' => 'Verificado',
800            ];
801
802            $headers = [];
803
804            foreach ($result as &$row) {
805                $rowArr = (array) $row;
806
807                if ($rowArr['company_name'] == null) {
808                    $totals = [];
809                    foreach ($rowArr as $key => $value) {
810                        if (str_starts_with((string) $key, 'total')) {
811                            $groupKey = str_replace('total', 'groupConcatIds', $key);
812                            $totals[] = [
813                                'totalKey' => $key,
814                                'totalValue' => (int) $value,
815                                'groupKey' => $groupKey,
816                                'groupValue' => $rowArr[$groupKey] ?? null,
817                            ];
818                        }
819                    }
820
821                    usort($totals, fn (array $a, array $b): int => $b['totalValue'] <=> $a['totalValue']);
822
823                    if (empty($headers)) {
824                        foreach ($totals as $t) {
825                            $headers[] = $labelMap[$t['totalKey']] ?? $t['totalKey'];
826                        }
827                    }
828
829                    $sortedRow = [
830                        'company_name' => $rowArr['company_name'],
831                        'source' => $rowArr['source'],
832                        'company_id' => $rowArr['company_id'],
833                    ];
834
835                    foreach ($totals as $t) {
836                        $sortedRow[$t['totalKey']] = (string) $t['totalValue'];
837                        $sortedRow[$t['groupKey']] = $t['groupValue'];
838                    }
839
840                    $row = (object) $sortedRow;
841                }
842            }
843            unset($row);
844
845            return response([
846                'message' => 'OK',
847                'data' => $result,
848                'headers' => $headers,
849            ]);
850
851        } catch (\Exception $e) {
852            report(AppException::fromException($e, 'LIST_G3W_ORDER_STATUS_EXCEPTION'));
853
854            return response(['message' => 'KO', 'error' => $e->getMessage()]);
855        }
856
857    }
858
859    public function get_sources_digital_campaign_analytics(): ResponseFactory|Response
860    {
861
862        try {
863
864            $sources = TblSources::where('digital_campaign_source', 1)->orderByRaw('ISNULL(priority), priority ASC')->get();
865
866            $query = "SELECT
867                        bt.budget_type_id,
868                        bt.name
869                    FROM tbl_budget_types bt
870                    LEFT JOIN tbl_budget_type_groups btg
871                        ON bt.budget_type_group_id = btg.budget_type_group_id
872                    WHERE bt.name != '' AND bt.budget_type_id != 7
873                    ORDER BY ISNULL(bt.priority), bt.priority ASC";
874
875            $budgetTypes = DB::select($query);
876
877            $query = 'SELECT
878                        DISTINCT q.status_by_g3w
879                    FROM tbl_quotations q
880                    WHERE q.status_by_g3w IS NOT NULL';
881
882            $g3wStatus = DB::select($query);
883
884            return response([
885                'message' => 'OK',
886                'sources' => $sources,
887                'budgetTypes' => $budgetTypes,
888                'g3wStatus' => $g3wStatus,
889            ]);
890
891        } catch (\Exception $e) {
892            report(AppException::fromException($e, 'GET_SOURCES_DIGITAL_CAMPAIGN_ANALYTICS_EXCEPTION'));
893
894            return response(['message' => 'KO', 'error' => $e->getMessage()]);
895        }
896
897    }
898
899    public function update_final_summary(Request $request, $companyId): ResponseFactory|Response
900    {
901
902        try {
903
904            $data = $request->all();
905            $companyId = addslashes((string) $companyId);
906
907            $data['updated_at'] = date('Y-m-d H:i:s');
908            TblFinalSummary::where('company_id', $companyId)->update($data);
909
910            return response([
911                'message' => 'OK',
912            ]);
913
914        } catch (\Exception $e) {
915            report(AppException::fromException($e, 'UPDATE_FINAL_SUMMARY_EXCEPTION'));
916
917            return response(['message' => 'KO', 'error' => $e->getMessage()]);
918        }
919
920    }
921
922    public function get_final_summary(Request $request): ResponseFactory|Response
923    {
924
925        try {
926
927            $data = $request->all();
928
929            $companyId = addslashes((string) $data['company_id']);
930            $where = '';
931            $whereBQ = '';
932
933            if ($companyId != 0) {
934                $where .= " AND q.company_id = {$companyId} ";
935
936                $region = $this->region;
937
938                if ($region != 'All') {
939                    if ($region == 'Cataluña') {
940                        $whereBQ = "WHERE ad_group_name LIKE '%Barcelona%'";
941                    } elseif ($region == 'Comunidad Valenciana') {
942                        $whereBQ = "WHERE ad_group_name LIKE '%Valencia%'";
943                    } else {
944                        $whereBQ = "WHERE ad_group_name LIKE '%{$region}%'";
945                    }
946                }
947            } else {
948                $where .= " AND q.company_id IN ({$this->companyId}) ";
949            }
950
951            $query = "SELECT
952                        CAST(SUM(q.impressions_total) AS UNSIGNED) AS impressions_total,
953                        CAST(SUM(q.impressions_mdm) AS UNSIGNED) AS impressions_mdm,
954                        CAST(SUM(q.impressions_mdg) AS UNSIGNED) AS impressions_mdg,
955                        CAST(SUM(q.clicks) AS UNSIGNED) AS clicks,
956                        CAST(SUM(q.clicks_mdm) AS UNSIGNED) AS clicks_mdm,
957                        CAST(SUM(q.clicks_mdg) AS UNSIGNED) AS clicks_mdg,
958                        CAST(SUM(q.conversions) AS UNSIGNED) AS conversions,
959                        CAST(SUM(q.conversions_mdm) AS UNSIGNED) AS conversions_mdm,
960                        CAST(SUM(q.leads_total) AS UNSIGNED) AS leads_total,
961                        CAST(SUM(q.leads_total_mdm) AS UNSIGNED) AS leads_total_mdm,
962                        CAST(SUM(q.leads_total_mdg) AS UNSIGNED) AS leads_total_mdg,
963                        CAST(SUM(q.calls) AS UNSIGNED) AS calls,
964                        CAST(SUM(q.forms_and_emails) AS UNSIGNED) AS forms_and_emails,
965                        CAST(SUM(q.whats_app) AS UNSIGNED) AS whats_app,
966                        CAST(SUM(q.campaign_cost_meta) AS UNSIGNED) AS campaign_cost_meta,
967                        CAST(SUM(q.campaign_cost_google) AS UNSIGNED) AS campaign_cost_google,
968                        CAST(SUM(q.invested_p1) AS UNSIGNED) AS invested_p1,
969                        CAST(SUM(q.invested_p2) AS UNSIGNED) AS invested_p2,
970                        CAST(SUM(q.invested_p3) AS UNSIGNED) AS invested_p3,
971                        CAST(SUM(q.avg_cost_per_lead_obj_v1) AS UNSIGNED) AS avg_cost_per_lead_obj_v1,
972                        CAST(SUM(q.cac_per_budget_accepted_obj_v1) AS UNSIGNED) AS cac_per_budget_accepted_obj_v1,
973                        CAST(SUM(q.cac_per_amount_accepted_obj_v1) AS UNSIGNED) AS cac_per_amount_accepted_obj_v1,
974                        CAST(SUM(q.avg_cost_per_lead_obj_v2) AS UNSIGNED) AS avg_cost_per_lead_obj_v2,
975                        CAST(SUM(q.cac_per_budget_accepted_obj_v2) AS UNSIGNED) AS cac_per_budget_accepted_obj_v2,
976                        CAST(SUM(q.cac_per_amount_accepted_obj_v2) AS UNSIGNED) AS cac_per_amount_accepted_obj_v2,
977                        CAST(SUM(q.g3w_total) AS UNSIGNED) AS g3w_total,
978                        CAST(SUM(q.g3w_total_amount) AS UNSIGNED) AS g3w_total_amount,
979                        q.created_at,
980                        q.updated_at
981                    FROM tbl_final_summary q
982                    WHERE
983                        q.company_id != 0
984                        {$where}";
985
986            $result = DB::select($query);
987
988            if (isset($data['source_ids']) && $data['source_ids'] != null) {
989                $sourceIds = implode(',', $data['source_ids']);
990                if (count($data['source_ids']) > 0) {
991                    $where .= " AND q.source_id IN ({$sourceIds}) ";
992                }
993            }
994
995            $query = "SELECT
996                            COUNT(q.created_at) totalOrders,
997                            SUM(q.amount) amount,
998                            GROUP_CONCAT(q.id) groupConcatIds,
999                            COUNT(CASE WHEN s.name LIKE '%MDM%' THEN 1 END) leadsMDM,
1000                            COUNT(CASE WHEN s.name LIKE '%MDG%' THEN 1 END) leadsMDG
1001                        FROM
1002                        tbl_quotations q
1003                        LEFT JOIN tbl_sources s
1004                            ON q.source_id = s.source_id
1005                        WHERE
1006                            q.for_add = 0
1007                            AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1008                            AND (q.commercial IS NOT NULL AND q.commercial != '')
1009                            AND q.budget_type_id != 7
1010                            AND q.budget_type_id IS NOT NULL
1011                            AND (s.name LIKE '%MDM%' OR s.name LIKE '%MDG%')
1012                            {$where}";
1013
1014            $totalOrders = DB::select($query);
1015
1016            $query = "SELECT
1017                            COUNT(COALESCE(q.sent_date, q.issue_date)) issuedOrders,
1018                            SUM(q.amount) amount,
1019                            GROUP_CONCAT(q.id) groupConcatIds,
1020                            COUNT(CASE WHEN s.name LIKE '%MDM%' THEN 1 END) issuedMDM,
1021                            COUNT(CASE WHEN s.name LIKE '%MDG%' THEN 1 END) issuedMDG,
1022                            SUM(
1023                                CASE
1024                                    WHEN DATEDIFF(q.issue_date, q.created_at) <= 3 THEN 1
1025                                    ELSE 0
1026                                END
1027                            ) AS onTimeOrders,
1028                            GROUP_CONCAT(
1029                                CASE
1030                                    WHEN DATEDIFF(q.issue_date, q.created_at) <= 3 THEN q.id
1031                                END
1032                            ) AS groupConcatIdsonTimeOrders,
1033                            SUM(
1034                                CASE
1035                                    WHEN DATEDIFF(q.issue_date, q.created_at) > 3 THEN 1
1036                                    ELSE 0
1037                                END
1038                            ) AS delayedOrders,
1039                            GROUP_CONCAT(
1040                                CASE
1041                                    WHEN DATEDIFF(q.issue_date, q.created_at) > 3 THEN q.id
1042                                END
1043                            ) AS groupConcatIdsdelayedOrders
1044                        FROM
1045                        tbl_quotations q
1046                        LEFT JOIN tbl_sources s
1047                            ON q.source_id = s.source_id
1048                        WHERE
1049                            COALESCE(q.sent_date, q.issue_date) IS NOT NULL
1050                            AND q.for_add = 0
1051                            AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1052                            AND (q.commercial IS NOT NULL AND q.commercial != '')
1053                            AND q.budget_type_id != 7
1054                            AND q.budget_type_id IS NOT NULL
1055                            AND (s.name LIKE '%MDM%' OR s.name LIKE '%MDG%')
1056                            {$where}";
1057
1058            $issuedOrders = DB::select($query);
1059
1060            $query = "SELECT
1061                            COUNT(q.acceptance_date) acceptanceOrders,
1062                            SUM(q.amount) amount,
1063                            GROUP_CONCAT(q.id) groupConcatIds,
1064                            COUNT(CASE WHEN s.name LIKE '%MDM%' THEN 1 END) acceptanceMDM,
1065                            COUNT(CASE WHEN s.name LIKE '%MDG%' THEN 1 END) acceptanceMDG,
1066                            SUM(
1067                                CASE
1068                                    WHEN DATEDIFF(q.issue_date, q.created_at) <= 3 THEN 1
1069                                    ELSE 0
1070                                END
1071                            ) AS onTimeOrders,
1072                            GROUP_CONCAT(
1073                                CASE
1074                                    WHEN DATEDIFF(q.issue_date, q.created_at) <= 3 THEN q.id
1075                                END
1076                            ) AS groupConcatIdsonTimeOrders,
1077                            SUM(
1078                                CASE
1079                                    WHEN DATEDIFF(q.issue_date, q.created_at) > 3 THEN 1
1080                                    ELSE 0
1081                                END
1082                            ) AS delayedOrders,
1083                            GROUP_CONCAT(
1084                                CASE
1085                                    WHEN DATEDIFF(q.issue_date, q.created_at) > 3 THEN q.id
1086                                END
1087                            ) AS groupConcatIdsdelayedOrders
1088                        FROM
1089                        tbl_quotations q
1090                        LEFT JOIN tbl_sources s
1091                            ON q.source_id = s.source_id
1092                        WHERE
1093                            q.acceptance_date IS NOT NULL
1094                            AND q.for_add = 0
1095                            AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1096                            AND (q.commercial IS NOT NULL AND q.commercial != '')
1097                            AND q.budget_type_id != 7
1098                            AND q.budget_type_id IS NOT NULL
1099                            AND (s.name LIKE '%MDM%' OR s.name LIKE '%MDG%')
1100                            {$where}";
1101
1102            $acceptanceOrders = DB::select($query);
1103
1104            $allRegions = TblFinalSummary::where('company_id', 0)->first();
1105
1106            $bigQuery = new BigQueryService;
1107
1108            $query = "SELECT                        
1109                        SUM(metrics_clicks) clicks,
1110                        SUM(metrics_impressions) impressions,                        
1111                        SUM(metrics_conversions) AS conversions
1112                    FROM `ivb-ai.google_ads_data.ads_GeoStats_8104988947`
1113                    {$whereBQ}";
1114
1115            $resultInvestments = $bigQuery->query($query);
1116
1117            return response([
1118                'message' => 'OK',
1119                'data' => $result[0],
1120                'resultInvestments' => $resultInvestments[0],
1121                'objectivesForAllRegions' => $allRegions,
1122                'acceptanceOrders' => $acceptanceOrders,
1123                'issuedOrders' => $issuedOrders,
1124                'totalOrders' => $totalOrders,
1125            ]);
1126
1127        } catch (\Exception $e) {
1128            report(AppException::fromException($e, 'GET_FINAL_SUMMARY_EXCEPTION'));
1129
1130            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1131        }
1132
1133    }
1134
1135    public function get_main_kpis(Request $request)
1136    {
1137
1138        try {
1139
1140            $data = $request->all();
1141
1142            $companyId = addslashes($data['company_id']);
1143            $where = '';
1144
1145            foreach ($data as $key => $value) {
1146                if (is_array($value)) {
1147                    if (! empty($value['start_date'])) {
1148                        $startDates[] = $value['start_date'];
1149                    }
1150                    if (! empty($value['end_date'])) {
1151                        $endDates[] = $value['end_date'];
1152                    }
1153                }
1154            }
1155
1156            $minDate = ! empty($startDates) ? min($startDates) : null;
1157            $maxDate = ! empty($endDates) ? max($endDates) : null;
1158
1159            $region = null;
1160            $whereBQ = '';
1161
1162            if ($companyId != 0) {
1163                $where .= " AND q.company_id = {$companyId} ";
1164                $region = $this->region;
1165
1166                if ($region != 'All') {
1167                    if ($region == 'Cataluña') {
1168                        $whereBQ = " AND ad_group_name LIKE '%Barcelona%'";
1169                    } elseif ($region == 'Comunidad Valenciana') {
1170                        $whereBQ = " AND ad_group_name LIKE '%Valencia%'";
1171                    } else {
1172                        $whereBQ = " AND ad_group_name LIKE '%{$region}%'";
1173                    }
1174                }
1175            } else {
1176                $where .= " AND q.company_id IN ({$this->companyId}) ";
1177            }
1178
1179            $whereTotalAcceptance = '';
1180            $whereTotalIssue = '';
1181            $resultTotal = [];
1182
1183            $bigQuery = new BigQueryService;
1184
1185            if (isset($minDate) && $minDate != null) {
1186                if (isset($maxDate) && $maxDate != null) {
1187                    $whereTotalAcceptance .= " AND q.request_date BETWEEN '{$minDate}' AND '{$maxDate}' ";
1188                    $whereTotalIssue .= " AND q.request_date BETWEEN '{$minDate}' AND '{$maxDate}' ";
1189
1190                    $query = "SELECT                                                                
1191                                SUM(metrics_cost_micros) / 1000000 AS investments                                
1192                            FROM `ivb-ai.google_ads_data.ads_GeoStats_8104988947`
1193                            WHERE segments_date BETWEEN '{$minDate}' AND '{$maxDate}'
1194                            {$whereBQ}";
1195
1196                    $resultInvestments = $bigQuery->query($query);
1197
1198                    $query = "SELECT
1199                                COUNT(CASE WHEN q.budget_status_id = 3 THEN 1 END) totalOrdersAceptance,
1200                                COUNT(CASE WHEN q.budget_status_id = 2 THEN 1 END) acceptanceOrdersEnviado,
1201                                GROUP_CONCAT(CASE WHEN q.budget_status_id = 2 THEN q.id END) groupconcatIdsAcceptanceOrdersEnviado,
1202                                AVG(CASE WHEN q.budget_status_id = 3 THEN q.amount END) averageAcceptanceAmount,
1203                                SUM(CASE WHEN q.budget_status_id = 3 THEN q.amount END) amount,
1204                                GROUP_CONCAT(CASE WHEN q.budget_status_id = 3 THEN q.id END) groupConcatIds,
1205                                GROUP_CONCAT(CASE WHEN bt.budget_type_group_id = 4 THEN q.id END) groupConcatIdsFacilities,
1206                                SUM(CASE WHEN bt.budget_type_group_id = 4 THEN q.amount END) facilitiesTotalAmount,
1207                                GROUP_CONCAT(CASE WHEN bt.budget_type_group_id = 8 THEN q.id END) groupConcatIdsNew,
1208                                SUM(CASE WHEN bt.budget_type_group_id = 8 THEN q.amount END) newTotalAmount,
1209                                GROUP_CONCAT(CASE WHEN bt.budget_type_group_id = 5 THEN q.id END) groupConcatIdsCorrectives,
1210                                SUM(CASE WHEN bt.budget_type_group_id = 5 THEN q.amount END) correctivesTotalAmount,
1211                                GROUP_CONCAT(CASE WHEN bt.budget_type_group_id = 3 THEN q.id END) groupConcatIdsPreventive,
1212                                SUM(CASE WHEN bt.budget_type_group_id = 3 THEN q.amount END) preventiveTotalAmount,
1213
1214                                COUNT(CASE WHEN q.budget_status_id = 2 AND bt.budget_type_group_id IN (3, 8) THEN 1 END) acceptanceOrdersEnviado38,
1215                                GROUP_CONCAT(CASE WHEN q.budget_status_id = 2 AND bt.budget_type_group_id IN (3, 8) THEN q.id END) groupConcatIdsAcceptanceOrdersEnviado38,
1216                                COUNT(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN 1 END) totalOrdersAceptance38,
1217                                GROUP_CONCAT(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN q.id END) groupConcatIdsAcceptanceOrders38,
1218                                AVG(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN q.amount END) averageAcceptanceAmount38,
1219                                SUM(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN q.amount END) amount38
1220                            FROM
1221                            tbl_quotations q
1222                            LEFT JOIN tbl_sources s
1223                                ON q.source_id = s.source_id
1224                            LEFT JOIN tbl_budget_types bt
1225                                ON bt.budget_type_id = q.budget_type_id
1226                            WHERE
1227                                q.request_date IS NOT NULL
1228                                AND q.for_add = 0
1229                                AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1230                                AND (q.commercial IS NOT NULL AND q.commercial != '')                                
1231                                AND q.budget_type_id != 7
1232                                AND q.budget_type_id IS NOT NULL
1233                                AND (s.name LIKE '%MDM%' OR s.name LIKE '%MDG%')
1234                                {$where}
1235                                {$whereTotalAcceptance}";
1236
1237                    $resultTotalAcceptance = DB::select($query);
1238
1239                    $query = "SELECT
1240                                SUM(CASE WHEN COALESCE(q.sent_date, q.issue_date) IS NOT NULL THEN q.amount END) amount,
1241                                COUNT(CASE WHEN COALESCE(q.sent_date, q.issue_date) IS NOT NULL THEN 1 END) totalOrdersIssue,
1242                                GROUP_CONCAT(CASE WHEN COALESCE(q.sent_date, q.issue_date) IS NOT NULL THEN q.id END) groupConcatIds,
1243                                SUM(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN q.amount END) amount38,
1244                                COUNT(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN 1 END) totalOrdersIssue38,
1245                                GROUP_CONCAT(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN q.id END) groupConcatIds38
1246                            FROM
1247                                tbl_quotations q
1248                                LEFT JOIN tbl_sources s
1249                                    ON q.source_id = s.source_id
1250                                LEFT JOIN tbl_budget_types bt
1251                                    ON bt.budget_type_id = q.budget_type_id
1252                            WHERE
1253                                q.request_date IS NOT NULL
1254                                AND q.for_add = 0
1255                                AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1256                                AND (q.commercial IS NOT NULL AND q.commercial != '')
1257                                AND q.budget_type_id != 7
1258                                AND q.budget_type_id IS NOT NULL
1259                                AND (s.name LIKE '%MDM%' OR s.name LIKE '%MDG%')
1260                                {$where}
1261                                {$whereTotalIssue}";
1262
1263                    $resultTotalIssue = DB::select($query);
1264
1265                    $resultTotal = [
1266                        'result' => $resultTotalAcceptance[0],
1267                        'otherStatus' => $resultTotalIssue[0],
1268                        'groupConcatIds' => implode(',', array_merge(...array_map(fn ($v) => explode(',', $v), [
1269                            $resultTotalAcceptance[0]->groupConcatIdsFacilities,
1270                            $resultTotalAcceptance[0]->groupConcatIdsNew,
1271                            $resultTotalAcceptance[0]->groupConcatIdsCorrectives,
1272                            $resultTotalAcceptance[0]->groupConcatIdsPreventive]))
1273                        ),
1274                        'resultInvestments' => $resultInvestments[0],
1275                    ];
1276                }
1277            }
1278
1279            $whereP1Acceptance = '';
1280            $whereP1Issue = '';
1281            $resultP1 = [];
1282
1283            if (isset($data['p1'])) {
1284                $p1 = $data['p1'];
1285
1286                if (isset($p1['start_date']) && $p1['start_date'] != null) {
1287                    if (isset($p1['end_date']) && $p1['end_date'] != null) {
1288                        $whereP1Acceptance .= " AND q.request_date BETWEEN '{$p1['start_date']}' AND '{$p1['end_date']}' ";
1289                        $whereP1Issue .= " AND q.request_date BETWEEN '{$p1['start_date']}' AND '{$p1['end_date']}' ";
1290
1291                        $query = "SELECT                                                                
1292                                    SUM(metrics_cost_micros) / 1000000 AS investments                                
1293                                FROM `ivb-ai.google_ads_data.ads_GeoStats_8104988947`
1294                                WHERE segments_date BETWEEN '{$p1['start_date']}' AND '{$p1['end_date']}'
1295                                {$whereBQ}";
1296
1297                        $resultInvestments = $bigQuery->query($query);
1298
1299                        $query = "SELECT
1300                                    COUNT(CASE WHEN q.budget_status_id = 3 THEN 1 END) totalOrdersAceptance,
1301                                    COUNT(CASE WHEN q.budget_status_id = 2 THEN 1 END) acceptanceOrdersEnviado,
1302                                    GROUP_CONCAT(CASE WHEN q.budget_status_id = 2 THEN q.id END) groupconcatIdsAcceptanceOrdersEnviado,
1303                                    AVG(CASE WHEN q.budget_status_id = 3 THEN q.amount END) averageAcceptanceAmount,
1304                                    SUM(CASE WHEN q.budget_status_id = 3 THEN q.amount END) amount,
1305                                    GROUP_CONCAT(CASE WHEN q.budget_status_id = 3 THEN q.id END) groupConcatIds,
1306                                    GROUP_CONCAT(CASE WHEN bt.budget_type_group_id = 4 THEN q.id END) groupConcatIdsFacilities,
1307                                    SUM(CASE WHEN bt.budget_type_group_id = 4 THEN q.amount END) facilitiesTotalAmount,
1308                                    GROUP_CONCAT(CASE WHEN bt.budget_type_group_id = 8 THEN q.id END) groupConcatIdsNew,
1309                                    SUM(CASE WHEN bt.budget_type_group_id = 8 THEN q.amount END) newTotalAmount,
1310                                    GROUP_CONCAT(CASE WHEN bt.budget_type_group_id = 5 THEN q.id END) groupConcatIdsCorrectives,
1311                                    SUM(CASE WHEN bt.budget_type_group_id = 5 THEN q.amount END) correctivesTotalAmount,
1312                                    GROUP_CONCAT(CASE WHEN bt.budget_type_group_id = 3 THEN q.id END) groupConcatIdsPreventive,
1313                                    SUM(CASE WHEN bt.budget_type_group_id = 3 THEN q.amount END) preventiveTotalAmount,
1314
1315                                    COUNT(CASE WHEN q.budget_status_id = 2 AND bt.budget_type_group_id IN (3, 8) THEN 1 END) acceptanceOrdersEnviado38,
1316                                    GROUP_CONCAT(CASE WHEN q.budget_status_id = 2 AND bt.budget_type_group_id IN (3, 8) THEN q.id END) groupConcatIdsAcceptanceOrdersEnviado38,
1317                                    COUNT(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN 1 END) totalOrdersAceptance38,
1318                                    GROUP_CONCAT(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN q.id END) groupConcatIdsAcceptanceOrders38,
1319                                    AVG(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN q.amount END) averageAcceptanceAmount38,
1320                                    SUM(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN q.amount END) amount38
1321                                FROM
1322                                tbl_quotations q
1323                                LEFT JOIN tbl_sources s
1324                                    ON q.source_id = s.source_id
1325                                LEFT JOIN tbl_budget_types bt
1326                                    ON bt.budget_type_id = q.budget_type_id
1327                                WHERE
1328                                    q.request_date IS NOT NULL
1329                                    AND q.for_add = 0
1330                                    AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1331                                    AND (q.commercial IS NOT NULL AND q.commercial != '')
1332                                    AND q.budget_type_id != 7
1333                                    AND q.budget_type_id IS NOT NULL
1334                                    AND (s.name LIKE '%MDM%' OR s.name LIKE '%MDG%')
1335                                    {$where}
1336                                    {$whereP1Acceptance}";
1337
1338                        $resultAcceptance = DB::select($query);
1339
1340                        $query = "SELECT
1341                                    SUM(CASE WHEN COALESCE(q.sent_date, q.issue_date) IS NOT NULL THEN q.amount END) amount,
1342                                    COUNT(CASE WHEN COALESCE(q.sent_date, q.issue_date) IS NOT NULL THEN 1 END) totalOrdersIssue,
1343                                    GROUP_CONCAT(CASE WHEN COALESCE(q.sent_date, q.issue_date) IS NOT NULL THEN q.id END) groupConcatIds,
1344                                    SUM(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN q.amount END) amount38,
1345                                    COUNT(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN 1 END) totalOrdersIssue38,
1346                                    GROUP_CONCAT(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN q.id END) groupConcatIds38
1347                                FROM
1348                                    tbl_quotations q
1349                                    LEFT JOIN tbl_sources s
1350                                        ON q.source_id = s.source_id
1351                                    LEFT JOIN tbl_budget_types bt
1352                                        ON bt.budget_type_id = q.budget_type_id
1353                                WHERE
1354                                    q.request_date IS NOT NULL
1355                                    AND q.for_add = 0
1356                                    AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1357                                    AND (q.commercial IS NOT NULL AND q.commercial != '')
1358                                    AND q.budget_type_id != 7
1359                                    AND q.budget_type_id IS NOT NULL
1360                                    AND (s.name LIKE '%MDM%' OR s.name LIKE '%MDG%')
1361                                    {$where}
1362                                    {$whereP1Issue}";
1363
1364                        $resultIssue = DB::select($query);
1365
1366                        $resultP1 = [
1367                            'result' => $resultAcceptance[0],
1368                            'otherStatus' => $resultIssue[0],
1369                            'groupConcatIds' => implode(',', array_merge(...array_map(fn ($v) => explode(',', $v), [
1370                                $resultAcceptance[0]->groupConcatIdsFacilities,
1371                                $resultAcceptance[0]->groupConcatIdsNew,
1372                                $resultAcceptance[0]->groupConcatIdsCorrectives,
1373                                $resultAcceptance[0]->groupConcatIdsPreventive]))
1374                            ),
1375                            'resultInvestments' => $resultInvestments[0],
1376                        ];
1377                    }
1378                }
1379            }
1380
1381            $whereP2Acceptance = '';
1382            $whereP2Issue = '';
1383            $resultP2 = [];
1384
1385            if (isset($data['p2'])) {
1386                $p2 = $data['p2'];
1387
1388                if (isset($p2['start_date']) && $p2['start_date'] != null) {
1389                    if (isset($p2['end_date']) && $p2['end_date'] != null) {
1390                        $whereP2Acceptance .= " AND q.request_date BETWEEN '{$p2['start_date']}' AND '{$p2['end_date']}' ";
1391                        $whereP2Issue .= " AND q.request_date BETWEEN '{$p2['start_date']}' AND '{$p2['end_date']}' ";
1392
1393                        $query = "SELECT                                                                
1394                                    SUM(metrics_cost_micros) / 1000000 AS investments                                
1395                                FROM `ivb-ai.google_ads_data.ads_GeoStats_8104988947`
1396                                WHERE segments_date BETWEEN '{$p2['start_date']}' AND '{$p2['end_date']}'
1397                                {$whereBQ}";
1398
1399                        $resultInvestments = $bigQuery->query($query);
1400
1401                        $query = "SELECT
1402                                    COUNT(CASE WHEN q.budget_status_id = 3 THEN 1 END) totalOrdersAceptance,
1403                                    COUNT(CASE WHEN q.budget_status_id = 2 THEN 1 END) acceptanceOrdersEnviado,
1404                                    GROUP_CONCAT(CASE WHEN q.budget_status_id = 2 THEN q.id END) groupconcatIdsAcceptanceOrdersEnviado,
1405                                    AVG(CASE WHEN q.budget_status_id = 3 THEN q.amount END) averageAcceptanceAmount,
1406                                    SUM(CASE WHEN q.budget_status_id = 3 THEN q.amount END) amount,
1407                                    GROUP_CONCAT(CASE WHEN q.budget_status_id = 3 THEN q.id END) groupConcatIds,
1408                                    GROUP_CONCAT(CASE WHEN bt.budget_type_group_id = 4 THEN q.id END) groupConcatIdsFacilities,
1409                                    SUM(CASE WHEN bt.budget_type_group_id = 4 THEN q.amount END) facilitiesTotalAmount,
1410                                    GROUP_CONCAT(CASE WHEN bt.budget_type_group_id = 8 THEN q.id END) groupConcatIdsNew,
1411                                    SUM(CASE WHEN bt.budget_type_group_id = 8 THEN q.amount END) newTotalAmount,
1412                                    GROUP_CONCAT(CASE WHEN bt.budget_type_group_id = 5 THEN q.id END) groupConcatIdsCorrectives,
1413                                    SUM(CASE WHEN bt.budget_type_group_id = 5 THEN q.amount END) correctivesTotalAmount,
1414                                    GROUP_CONCAT(CASE WHEN bt.budget_type_group_id = 3 THEN q.id END) groupConcatIdsPreventive,
1415                                    SUM(CASE WHEN bt.budget_type_group_id = 3 THEN q.amount END) preventiveTotalAmount,
1416
1417                                    COUNT(CASE WHEN q.budget_status_id = 2 AND bt.budget_type_group_id IN (3, 8) THEN 1 END) acceptanceOrdersEnviado38,
1418                                    GROUP_CONCAT(CASE WHEN q.budget_status_id = 2 AND bt.budget_type_group_id IN (3, 8) THEN q.id END) groupConcatIdsAcceptanceOrdersEnviado38,
1419                                    COUNT(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN 1 END) totalOrdersAceptance38,
1420                                    GROUP_CONCAT(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN q.id END) groupConcatIdsAcceptanceOrders38,
1421                                    AVG(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN q.amount END) averageAcceptanceAmount38,
1422                                    SUM(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN q.amount END) amount38
1423                                FROM
1424                                tbl_quotations q
1425                                LEFT JOIN tbl_sources s
1426                                    ON q.source_id = s.source_id
1427                                LEFT JOIN tbl_budget_types bt
1428                                    ON bt.budget_type_id = q.budget_type_id
1429                                WHERE
1430                                    q.acceptance_date IS NOT NULL
1431                                    AND q.for_add = 0
1432                                    AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1433                                    AND (q.commercial IS NOT NULL AND q.commercial != '')
1434                                    AND q.budget_type_id != 7
1435                                    AND q.budget_type_id IS NOT NULL
1436                                    AND (s.name LIKE '%MDM%' OR s.name LIKE '%MDG%')
1437                                    {$where}
1438                                    {$whereP2Acceptance}";
1439
1440                        $resultAcceptance = DB::select($query);
1441
1442                        $query = "SELECT
1443                                    SUM(CASE WHEN COALESCE(q.sent_date, q.issue_date) IS NOT NULL THEN q.amount END) amount,
1444                                    COUNT(CASE WHEN COALESCE(q.sent_date, q.issue_date) IS NOT NULL THEN 1 END) totalOrdersIssue,
1445                                    GROUP_CONCAT(CASE WHEN COALESCE(q.sent_date, q.issue_date) IS NOT NULL THEN q.id END) groupConcatIds,
1446                                    SUM(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN q.amount END) amount38,
1447                                    COUNT(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN 1 END) totalOrdersIssue38,
1448                                    GROUP_CONCAT(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN q.id END) groupConcatIds38
1449                                FROM
1450                                    tbl_quotations q
1451                                    LEFT JOIN tbl_sources s
1452                                        ON q.source_id = s.source_id
1453                                    LEFT JOIN tbl_budget_types bt
1454                                        ON bt.budget_type_id = q.budget_type_id
1455                                WHERE
1456                                    COALESCE(q.sent_date, q.issue_date) IS NOT NULL
1457                                    AND q.for_add = 0
1458                                    AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1459                                    AND (q.commercial IS NOT NULL AND q.commercial != '')
1460                                    AND q.budget_type_id != 7
1461                                    AND q.budget_type_id IS NOT NULL
1462                                    AND (s.name LIKE '%MDM%' OR s.name LIKE '%MDG%')
1463                                    {$where}
1464                                    {$whereP2Issue}";
1465
1466                        $resultIssue = DB::select($query);
1467
1468                        $resultP2 = [
1469                            'result' => $resultAcceptance[0],
1470                            'otherStatus' => $resultIssue[0],
1471                            'groupConcatIds' => implode(',', array_merge(...array_map(fn ($v) => explode(',', $v), [
1472                                $resultAcceptance[0]->groupConcatIdsFacilities,
1473                                $resultAcceptance[0]->groupConcatIdsNew,
1474                                $resultAcceptance[0]->groupConcatIdsCorrectives,
1475                                $resultAcceptance[0]->groupConcatIdsPreventive]))
1476                            ),
1477                            'resultInvestments' => $resultInvestments[0],
1478                        ];
1479                    }
1480                }
1481            }
1482
1483            $whereP3Acceptance = '';
1484            $whereP3Issue = '';
1485            $resultP3 = [];
1486
1487            if (isset($data['p3'])) {
1488                $p3 = $data['p3'];
1489
1490                if (isset($p3['start_date']) && $p3['start_date'] != null) {
1491                    if (isset($p3['end_date']) && $p3['end_date'] != null) {
1492                        $whereP3Acceptance .= " AND q.request_date BETWEEN '{$p3['start_date']}' AND '{$p3['end_date']}' ";
1493                        $whereP3Issue .= " AND q.request_date BETWEEN '{$p3['start_date']}' AND '{$p3['end_date']}' ";
1494
1495                        $query = "SELECT                                                                
1496                                    SUM(metrics_cost_micros) / 1000000 AS investments                                
1497                                FROM `ivb-ai.google_ads_data.ads_GeoStats_8104988947`
1498                                WHERE segments_date BETWEEN '{$p3['start_date']}' AND '{$p3['end_date']}'
1499                                {$whereBQ}";
1500
1501                        $resultInvestments = $bigQuery->query($query);
1502
1503                        $query = "SELECT
1504                                    COUNT(CASE WHEN q.budget_status_id = 3 THEN 1 END) totalOrdersAceptance,
1505                                    COUNT(CASE WHEN q.budget_status_id = 2 THEN 1 END) acceptanceOrdersEnviado,
1506                                    GROUP_CONCAT(CASE WHEN q.budget_status_id = 2 THEN q.id END) groupconcatIdsAcceptanceOrdersEnviado,
1507                                    AVG(CASE WHEN q.budget_status_id = 3 THEN q.amount END) averageAcceptanceAmount,
1508                                    SUM(CASE WHEN q.budget_status_id = 3 THEN q.amount END) amount,
1509                                    GROUP_CONCAT(CASE WHEN q.budget_status_id = 3 THEN q.id END) groupConcatIds,
1510                                    GROUP_CONCAT(CASE WHEN bt.budget_type_group_id = 4 THEN q.id END) groupConcatIdsFacilities,
1511                                    SUM(CASE WHEN bt.budget_type_group_id = 4 THEN q.amount END) facilitiesTotalAmount,
1512                                    GROUP_CONCAT(CASE WHEN bt.budget_type_group_id = 8 THEN q.id END) groupConcatIdsNew,
1513                                    SUM(CASE WHEN bt.budget_type_group_id = 8 THEN q.amount END) newTotalAmount,
1514                                    GROUP_CONCAT(CASE WHEN bt.budget_type_group_id = 5 THEN q.id END) groupConcatIdsCorrectives,
1515                                    SUM(CASE WHEN bt.budget_type_group_id = 5 THEN q.amount END) correctivesTotalAmount,
1516                                    GROUP_CONCAT(CASE WHEN bt.budget_type_group_id = 3 THEN q.id END) groupConcatIdsPreventive,
1517                                    SUM(CASE WHEN bt.budget_type_group_id = 3 THEN q.amount END) preventiveTotalAmount,
1518
1519                                    COUNT(CASE WHEN q.budget_status_id = 2 AND bt.budget_type_group_id IN (3, 8) THEN 1 END) acceptanceOrdersEnviado38,
1520                                    GROUP_CONCAT(CASE WHEN q.budget_status_id = 2 AND bt.budget_type_group_id IN (3, 8) THEN q.id END) groupConcatIdsAcceptanceOrdersEnviado38,
1521                                    COUNT(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN 1 END) totalOrdersAceptance38,
1522                                    GROUP_CONCAT(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN q.id END) groupConcatIdsAcceptanceOrders38,
1523                                    AVG(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN q.amount END) averageAcceptanceAmount38,
1524                                    SUM(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN q.amount END) amount38
1525                                FROM
1526                                tbl_quotations q
1527                                LEFT JOIN tbl_sources s
1528                                    ON q.source_id = s.source_id
1529                                LEFT JOIN tbl_budget_types bt
1530                                    ON bt.budget_type_id = q.budget_type_id
1531                                WHERE
1532                                    q.acceptance_date IS NOT NULL
1533                                    AND q.for_add = 0
1534                                    AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1535                                    AND (q.commercial IS NOT NULL AND q.commercial != '')
1536                                    AND q.budget_type_id != 7
1537                                    AND q.budget_type_id IS NOT NULL
1538                                    AND (s.name LIKE '%MDM%' OR s.name LIKE '%MDG%')
1539                                    {$where}
1540                                    {$whereP3Acceptance}";
1541
1542                        $resultAcceptance = DB::select($query);
1543
1544                        $query = "SELECT
1545                                    SUM(CASE WHEN COALESCE(q.sent_date, q.issue_date) IS NOT NULL THEN q.amount END) amount,
1546                                    COUNT(CASE WHEN COALESCE(q.sent_date, q.issue_date) IS NOT NULL THEN 1 END) totalOrdersIssue,
1547                                    GROUP_CONCAT(CASE WHEN COALESCE(q.sent_date, q.issue_date) IS NOT NULL THEN q.id END) groupConcatIds,
1548                                    SUM(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN q.amount END) amount38,
1549                                    COUNT(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN 1 END) totalOrdersIssue38,
1550                                    GROUP_CONCAT(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN q.id END) groupConcatIds38
1551                                FROM
1552                                    tbl_quotations q
1553                                    LEFT JOIN tbl_sources s
1554                                        ON q.source_id = s.source_id
1555                                    LEFT JOIN tbl_budget_types bt
1556                                        ON bt.budget_type_id = q.budget_type_id
1557                                WHERE
1558                                    COALESCE(q.sent_date, q.issue_date) IS NOT NULL
1559                                    AND q.for_add = 0
1560                                    AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1561                                    AND (q.commercial IS NOT NULL AND q.commercial != '')
1562                                    AND q.budget_type_id != 7
1563                                    AND q.budget_type_id IS NOT NULL
1564                                    AND (s.name LIKE '%MDM%' OR s.name LIKE '%MDG%')
1565                                    {$where}
1566                                    {$whereP3Issue}";
1567
1568                        $resultIssue = DB::select($query);
1569
1570                        $resultP3 = [
1571                            'result' => $resultAcceptance[0],
1572                            'otherStatus' => $resultIssue[0],
1573                            'groupConcatIds' => implode(',', array_merge(...array_map(fn ($v) => explode(',', $v), [
1574                                $resultAcceptance[0]->groupConcatIdsFacilities,
1575                                $resultAcceptance[0]->groupConcatIdsNew,
1576                                $resultAcceptance[0]->groupConcatIdsCorrectives,
1577                                $resultAcceptance[0]->groupConcatIdsPreventive]))
1578                            ),
1579                            'resultInvestments' => $resultInvestments[0],
1580                        ];
1581                    }
1582                }
1583            }
1584
1585            return response([
1586                'message' => 'OK',
1587                'totals' => $resultTotal,
1588                'data' => [
1589                    'p1' => $resultP1,
1590                    'p2' => $resultP2,
1591                    'p3' => $resultP3,
1592                ],
1593            ]);
1594
1595        } catch (\Exception $e) {
1596            /** @disregard P1014 */
1597            $e->exceptionCode = 'GET_MAIN_KPIS_EXCEPTION';
1598            report($e);
1599
1600            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1601        }
1602
1603    }
1604
1605    public function list_type_of_order(Request $request): ResponseFactory|Response
1606    {
1607
1608        try {
1609
1610            $data = $request->all();
1611            $companyId = addslashes((string) $data['company_id']);
1612            $where = '';
1613
1614            if ($companyId != 0) {
1615                $where .= " AND q.company_id = {$companyId} ";
1616            } else {
1617                $where .= " AND q.company_id IN ({$this->companyId}) ";
1618            }
1619
1620            if (isset($data['commercial']) && $data['commercial'] != null) {
1621                $where .= " AND q.commercial = '{$data['commercial']}'";
1622            }
1623
1624            if (isset($data['budget_status_id']) && count($data['budget_status_id']) > 0) {
1625                $budgetStatusIds = implode(',', $data['budget_status_id']);
1626                $where .= " AND q.budget_status_id IN ({$budgetStatusIds})";
1627            }
1628
1629            if ((isset($data['start_date']) && $data['start_date'] != null) && (isset($data['end_date']) && $data['end_date'] != null)) {
1630                $where .= " AND q.created_at BETWEEN '{$data['start_date']}' AND '{$data['end_date']}' ";
1631            }
1632
1633            $col = '1';
1634
1635            if (isset($data['data_to_display']) && $data['data_to_display'] != null) {
1636                if ($data['data_to_display'] == 1) {
1637                    $col = '1';
1638                }
1639
1640                if ($data['data_to_display'] == 2) {
1641                    $col = 'q.amount';
1642                }
1643            }
1644
1645            $totalCols = '';
1646
1647            $budgetTypes = TblBudgetTypes::orderByRaw('ISNULL(priority), priority ASC')->get();
1648            $cols = '';
1649            foreach ($budgetTypes as $item) {
1650                $item->name = preg_replace('/\s+/', ' ', (string) $item->name);
1651                $ranAlias = substr(bin2hex(random_bytes(6)), 0, 6);
1652                if ($item->name == '' || $item->name == null) {
1653                    $cols .= ",COALESCE(SUM(CASE WHEN bt.name IS NULL THEN {$col} ELSE 0 END), 0) AS 'Otros'";
1654                    $totalCols .= ",totals.`Otros` AS '{$ranAlias}'";
1655                } else {
1656                    $cols .= ",COALESCE(SUM(CASE WHEN bt.name = '{$item->name}' THEN {$col} ELSE 0 END), 0) AS '{$item->name}'";
1657                    $totalCols .= ",totals.`{$item->name}` AS '{$ranAlias}'";
1658                }
1659            }
1660
1661            $budgetTypeGroups = TblBudgetTypeGroups::orderByRaw('ISNULL(priority), priority ASC')->get();
1662
1663            $colsGroups = '';
1664            $totalColGroups = '';
1665            $totalColIndex = [
1666                20 => 59,
1667                21 => 43,
1668                22 => 44,
1669                23 => 45,
1670                9 => 37,
1671                25 => 47,
1672                26 => 48,
1673                27 => 49,
1674                11 => 38,
1675                29 => 51,
1676                30 => 52,
1677                13 => 39,
1678                28 => 50,
1679                15 => 40,
1680                24 => 46,
1681                31 => 53,
1682                17 => 41,
1683                32 => 54,
1684                33 => 55,
1685                34 => 56,
1686                35 => 57,
1687                36 => 58,
1688                19 => 42,
1689            ];
1690
1691            if (@$data['data_to_display'] != 4 && @$data['data_to_display'] != 3) {
1692                foreach ($budgetTypeGroups as $item) {
1693                    $budgetTypeGroupName = str_replace(' ', '', $item->name).$item->budget_type_group_id;
1694                    $ranAlias = substr(bin2hex(random_bytes(6)), 0, 6);
1695                    $totalColGroups .= ',totals.'.preg_replace('/\s+/', ' ', $budgetTypeGroupName)." AS '{$ranAlias}'";
1696                    $colsGroups .= ",GROUP_CONCAT(CASE WHEN (bt.budget_type_group_id = {$item->budget_type_group_id} OR bt.name IS NULL) THEN q.id END) AS 'groupConcatIds{$budgetTypeGroupName}'";
1697                    $colsGroups .= ",COALESCE(SUM(CASE WHEN (bt.budget_type_group_id = {$item->budget_type_group_id} OR bt.name IS NULL) THEN {$col} END), 0) AS '{$budgetTypeGroupName}'";
1698                }
1699
1700                $colsGroups .= ",COALESCE(SUM(CASE WHEN (bt.budget_type_group_id IS NOT NULL OR bt.name IS NULL) THEN {$col} END), 0) AS 'total'";
1701                $ranAlias = substr(bin2hex(random_bytes(6)), 0, 6);
1702                $totalCols .= ",totals.`total` AS '{$ranAlias}'";
1703
1704                $col = $colsGroups.$cols;
1705            }
1706
1707            if (@$data['data_to_display'] == 4) {
1708
1709                foreach ($budgetTypeGroups as $item) {
1710                    $budgetTypeGroupName = str_replace(' ', '', $item->name).$item->budget_type_group_id;
1711                    $ranAlias = substr(bin2hex(random_bytes(6)), 0, 6);
1712                    $totalColGroups .= ',totals.'.preg_replace('/\s+/', ' ', $budgetTypeGroupName)." AS '{$ranAlias}'";
1713                    $colsGroups .= ",GROUP_CONCAT(CASE WHEN (bt.budget_type_group_id = {$item->budget_type_group_id} OR bt.name IS NULL) THEN q.id END) AS 'groupConcatIds{$budgetTypeGroupName}'";
1714                    $colsGroups .= ",COALESCE(
1715                                        SUM(CASE WHEN (bt.budget_type_group_id = {$item->budget_type_group_id} OR bt.name IS NULL) THEN {$col} END) /
1716                                        SUM(CASE WHEN (bt.budget_type_group_id IS NOT NULL OR bt.name IS NULL) THEN {$col} END)
1717                                    , 0) AS '{$budgetTypeGroupName}'";
1718                }
1719
1720                $colsGroups .= ",COALESCE(SUM(CASE WHEN (bt.budget_type_group_id IS NOT NULL OR bt.name IS NULL) THEN {$col} END), 0) AS 'total'";
1721                $ranAlias = substr(bin2hex(random_bytes(6)), 0, 6);
1722                $totalCols .= ",totals.`total` AS '{$ranAlias}'";
1723
1724                $col = $colsGroups.$cols;
1725            }
1726
1727            if (@$data['data_to_display'] == 3) {
1728
1729                $cols = '';
1730                foreach ($budgetTypes as $item) {
1731                    $item->name = preg_replace('/\s+/', ' ', (string) $item->name);
1732                    if ($item->name == '' || $item->name == null) {
1733                        $ranAlias = substr(bin2hex(random_bytes(6)), 0, 6);
1734                        $totalCols .= ",totals.`Otros` AS '{$ranAlias}'";
1735                        $cols .= ",COALESCE(
1736                                        SUM(CASE WHEN bt.name IS NULL THEN q.amount ELSE 0 END) /
1737                                        SUM(CASE WHEN bt.name IS NULL THEN 1 ELSE 0 END) * 100 , 0
1738                                    ) AS 'Otros'";
1739                    } else {
1740                        $ranAlias = substr(bin2hex(random_bytes(6)), 0, 6);
1741                        $totalCols .= ",totals.`{$item->name}` AS '{$ranAlias}'";
1742                        $cols .= ",COALESCE(
1743                                        SUM(CASE WHEN bt.name = '{$item->name}' THEN q.amount ELSE 0 END) /
1744                                        SUM(CASE WHEN bt.name = '{$item->name}' THEN 1 ELSE 0 END), 0
1745                                    ) AS '{$item->name}'";
1746                    }
1747                }
1748
1749                $ranAlias = substr(bin2hex(random_bytes(6)), 0, 6);
1750                $totalColGroups .= ",totals.Otros AS '{$ranAlias}'";
1751                foreach ($budgetTypeGroups as $item) {
1752                    $budgetTypeGroupName = str_replace(' ', '', $item->name).$item->budget_type_group_id;
1753                    $ranAlias = substr(bin2hex(random_bytes(6)), 0, 6);
1754                    $totalColGroups .= ',totals.'.preg_replace('/\s+/', ' ', $budgetTypeGroupName)." AS '{$ranAlias}'";
1755                    $colsGroups .= ",GROUP_CONCAT(CASE WHEN (bt.budget_type_group_id = {$item->budget_type_group_id} OR bt.name IS NULL) THEN q.id END) AS 'groupConcatIds{$budgetTypeGroupName}'";
1756                    $colsGroups .= ",COALESCE(
1757                                        (SUM(CASE WHEN (bt.budget_type_group_id = {$item->budget_type_group_id} OR bt.name IS NULL) THEN q.amount END)) /
1758                                        (SUM(CASE WHEN (bt.budget_type_group_id = {$item->budget_type_group_id} OR bt.name IS NULL) THEN 1 END))
1759                                    , 0) '{$budgetTypeGroupName}'";
1760                }
1761
1762                $colsGroups .= ",COALESCE(
1763                                    (SUM(CASE WHEN (bt.budget_type_group_id IS NOT NULL OR bt.name IS NULL) THEN q.amount END)) /
1764                                    (SUM(CASE WHEN (bt.budget_type_group_id IS NOT NULL OR bt.name IS NULL) THEN 1 END))
1765                                , 0) 'total'";
1766
1767                $ranAlias = substr(bin2hex(random_bytes(6)), 0, 6);
1768                $totalColGroups .= ",totals.`total` AS '{$ranAlias}'";
1769                $col = $colsGroups.$cols;
1770
1771            }
1772
1773            $totalCols = $totalColGroups.$totalCols;
1774
1775            if (isset($data['budget_type_id']) && count($data['budget_type_id']) > 0) {
1776                $budgetTypeIds = implode(',', $data['budget_type_id']);
1777                $where .= " AND q.budget_type_id IN ({$budgetTypeIds})";
1778            }
1779
1780            $sortByFirst = '';
1781            $sortBySecond = '';
1782
1783            if (isset($data['sort_by']) && $data['sort_by'] != null) {
1784                if (isset($data['column']) && $data['column'] != null) {
1785                    $orderCol = $totalColIndex[$data['column']];
1786                    $sortByFirst = "{$orderCol} {$data['sort_by']},";
1787                    $sortBySecond = "{$data['column']} {$data['sort_by']},";
1788                }
1789            }
1790
1791            $query = "SELECT
1792                            s.name AS 'source',
1793                            bs.name AS 'status',
1794                            q.source_id,
1795                            q.budget_status_id,
1796                            GROUP_CONCAT(q.id) groupConcatIds,
1797                            COUNT(1) AS totalOrders,
1798                            SUM(q.amount) AS totalAmount
1799                            {$col}
1800                            {$totalCols}
1801                        FROM
1802                            tbl_quotations q
1803                            LEFT JOIN tbl_sources s ON s.source_id = q.source_id
1804                            LEFT JOIN tbl_budget_status bs ON bs.budget_status_id = q.budget_status_id
1805                            LEFT JOIN tbl_budget_types bt ON q.budget_type_id = bt.budget_type_id
1806                            LEFT JOIN tbl_budget_type_groups btg ON bt.budget_type_group_id = btg.budget_type_group_id
1807                            LEFT JOIN tbl_customer_types ct ON q.customer_type_id = ct.customer_type_id
1808                        JOIN
1809                            (
1810                            SELECT
1811                                q.source_id,
1812                                NULL a,
1813                                NULL b,
1814                                NULL c,
1815                                NULL d,
1816                                NULL e,
1817                                NULL f
1818                                {$col}
1819                            FROM
1820                                tbl_quotations q
1821                                LEFT JOIN tbl_sources s ON s.source_id = q.source_id
1822                                LEFT JOIN tbl_budget_status bs ON bs.budget_status_id = q.budget_status_id
1823                                LEFT JOIN tbl_budget_types bt ON q.budget_type_id = bt.budget_type_id
1824                                LEFT JOIN tbl_budget_type_groups btg ON bt.budget_type_group_id = btg.budget_type_group_id
1825                                LEFT JOIN tbl_customer_types ct ON q.customer_type_id = ct.customer_type_id
1826                            WHERE
1827                                q.for_add = 0
1828                                AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1829                                AND (q.commercial IS NOT NULL AND q.commercial != '')
1830                                AND q.budget_type_id != 7
1831                                AND q.budget_type_id IS NOT NULL
1832                                AND q.source_id > 0
1833                                AND q.budget_status_id > 0
1834                                AND q.budget_status_id != 18
1835                                AND s.digital_campaign_source > 0
1836                                {$where}
1837                            GROUP BY q.source_id
1838                            ) AS totals
1839                                ON q.source_id = totals.source_id
1840                        WHERE
1841                            q.for_add = 0
1842                            AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1843                            AND (q.commercial IS NOT NULL AND q.commercial != '')
1844                            AND q.budget_type_id != 7
1845                            AND q.budget_type_id IS NOT NULL
1846                            AND q.source_id > 0
1847                            AND q.budget_status_id > 0
1848                            AND q.budget_status_id != 18
1849                            AND s.digital_campaign_source > 0
1850                            {$where}
1851                        GROUP BY
1852                            q.source_id,
1853                            q.budget_status_id WITH ROLLUP
1854                        ORDER BY
1855                            CASE WHEN q.source_id IS NULL THEN 1 ELSE 0 END,
1856                            {$sortByFirst}
1857                            q.source_id,
1858                            CASE WHEN q.budget_status_id IS NULL THEN 0 ELSE 1 END,
1859                            {$sortBySecond}
1860                            q.budget_status_id";
1861            // return $query;
1862            $result = DB::select($query);
1863
1864            $query = "SELECT
1865                        btg.budget_type_group_id,
1866                        btg.name,
1867                        (
1868                            SELECT
1869                                GROUP_CONCAT(COALESCE(bt.name, '') ORDER BY ISNULL(bt.priority), bt.priority ASC SEPARATOR '|')
1870                            FROM
1871                                tbl_budget_types bt
1872                            WHERE
1873                                bt.budget_type_group_id = btg.budget_type_group_id
1874                        ) budget_types
1875                        FROM
1876                            tbl_budget_type_groups btg
1877                        ORDER BY
1878                            ISNULL(btg.priority),
1879                            btg.priority ASC";
1880
1881            $budgetTypeGroups = DB::select($query);
1882
1883            foreach ($budgetTypeGroups as $item) {
1884                $item->group_key_name = str_replace(' ', '', $item->name).$item->budget_type_group_id;
1885                $item->budget_types = explode('|', (string) $item->budget_types);
1886            }
1887
1888            return response([
1889                'message' => 'OK',
1890                'data' => $result,
1891                'budgetTypeGroups' => $budgetTypeGroups,
1892            ]);
1893
1894        } catch (\Exception $e) {
1895            report(AppException::fromException($e, 'LIST_TYPE_OF_ORDER_EXCEPTION'));
1896
1897            return response(['message' => 'KO', 'error' => $e->getMessage()]);
1898        }
1899    }
1900
1901    public function getG3wTasksExecuted(Request $request)
1902    {
1903        $region = urldecode((string) request()->header('Region'));
1904        try {
1905            session()->save();
1906            $result = $this->workService->getG3wTasksExecuted($region);
1907
1908            return response()->json([
1909                'data' => $result,
1910            ]);
1911
1912        } catch (\Exception $e) {
1913            report(AppException::fromException($e, 'GET_G3W_TASKS_EXECUTED_EXCEPTION'));
1914            Log::channel('g3w_invoices')->error('Failed to get g3w tasks executed: '.$e->getMessage());
1915
1916            return response()->json([
1917                'message' => $e->getMessage(),
1918            ], 500);
1919        }
1920
1921    }
1922
1923    public function list_performance_metrics(Request $request): ResponseFactory|Response
1924    {
1925
1926        // try {
1927
1928        $data = $request->all();
1929
1930        $companyId = addslashes((string) $data['company_id']);
1931        $where = '';
1932        $whereBQ = '1=1';
1933
1934        if ($companyId != 0) {
1935            $where .= " AND q.company_id = {$companyId} ";
1936
1937            $region = $this->region;
1938
1939            if ($region != 'All') {
1940                if ($region == 'Cataluña') {
1941                    $whereBQ .= " AND ad_group_name LIKE '%Barcelona%'";
1942                } elseif ($region == 'Comunidad Valenciana') {
1943                    $whereBQ .= " AND ad_group_name LIKE '%Valencia%'";
1944                } else {
1945                    $whereBQ .= " AND ad_group_name LIKE '%{$region}%'";
1946                }
1947            }
1948        } else {
1949            $where .= " AND q.company_id IN ({$this->companyId}) ";
1950        }
1951
1952        $ticketMedium = 1;
1953
1954        if (isset($data['medium_ticket_index']) && $data['medium_ticket_index']) {
1955            $ticketMedium = $data['medium_ticket_index'];
1956        }
1957
1958        $ratioAcceptance = 1;
1959
1960        if (isset($data['ratio_acceptance']) && $data['ratio_acceptance']) {
1961            $ratioAcceptance = $data['ratio_acceptance'];
1962        }
1963
1964        $acceptance = 1;
1965
1966        $createdRange = '';
1967        $issuedRange = '';
1968        $acceptanceRange = '';
1969        $requestRange = '';
1970
1971        $issuedWhere = $where;
1972
1973        if ((isset($data['start_date']) && $data['start_date'] != null) && (isset($data['end_date']) && $data['end_date'] != null)) {
1974            $createdRange .= " AND q.created_at BETWEEN '{$data['start_date']}' AND '{$data['end_date']}' ";
1975            $issuedRange .= " AND DATE(COALESCE(q.sent_date, q.issue_date)) BETWEEN '{$data['start_date']}' AND '{$data['end_date']}' ";
1976            $acceptanceRange .= " AND q.acceptance_date BETWEEN '{$data['start_date']}' AND '{$data['end_date']}' ";
1977            $requestRange .= " AND q.request_date BETWEEN '{$data['start_date']}' AND '{$data['end_date']}' ";
1978            // BigQuery table has no `q` alias (that's the MySQL query above) —
1979            // "q.segments_date" made every date-filtered run fail with
1980            // "Unrecognized name: q".
1981            $whereBQ .= " AND segments_date BETWEEN '{$data['start_date']}' AND '{$data['end_date']}' ";
1982        }
1983
1984        $query = "SELECT 
1985                        q.company_name,
1986                        q.company_id,
1987                        SUM(q.total_investment) totalInvestment,
1988                        SUM(q.totalOrders) totalOrders,
1989                        GROUP_CONCAT(q.groupConcatIdsTotalOrders) groupConcatIdsTotalOrders,                           
1990                        COALESCE(SUM(q.total_investment) / SUM(q.totalOrders), 0) CPL,
1991                        COALESCE(SUM(q.total_investment) / SUM(q.totalOrdersAcceptanceAmount), 0) CAC1,
1992                        COALESCE(SUM(q.total_investment) / SUM(q.totalOrdersAcceptanceAmountAnyMonth), 0) CAC2,
1993                        COALESCE(SUM(q.total_investment) / SUM(q.totalOrdersAcceptanceAmountSent), 0) CAC3,
1994                        SUM(q.totalOrdersSent) totalOrdersSent,
1995                        SUM(q.totalOrdersAmountSent) totalOrdersAmountSent,
1996                        GROUP_CONCAT(q.groupConcatIdsTotalOrdersSent) groupConcatIdsTotalOrdersSent,
1997                        SUM(q.totalOrdersAmount) totalOrdersAmount,
1998                        SUM(q.totalOrdersSentAnyMonth) totalOrdersSentAnyMonth,
1999                        SUM(q.totalOrdersAmountSentAnyMonth) totalOrdersAmountSentAnyMonth,
2000                        GROUP_CONCAT(q.groupConcatIdsTotalOrdersSentAnyMonth) groupConcatIdsTotalOrdersSentAnyMonth,
2001                        COALESCE(SUM(q.totalOrdersSent) / SUM(q.totalOrders) * 100, 0) percentageOfLeadsSentMonthlyOutOfLeadsMonthly,
2002                        SUM(q.totalOrdersAcceptance) totalOrdersAcceptance,
2003                        GROUP_CONCAT(q.groupConcatIdsTotalOrdersAcceptance) groupConcatIdsTotalOrdersAcceptance,
2004                        SUM(q.totalOrdersAcceptanceAmount) totalOrdersAcceptanceAmount,
2005                        SUM(q.totalOrdersAcceptanceAnyMonth) totalOrdersAcceptanceAnyMonth,
2006                        GROUP_CONCAT(q.groupConcatIdsTotalOrdersAcceptanceAnyMonth) groupConcatIdsTotalOrdersAcceptanceAnyMonth,
2007                        SUM(q.totalOrdersAcceptanceAmountAnyMonth) totalOrdersAcceptanceAmountAnyMonth,
2008                        SUM(q.totalOrdersAcceptanceSent) totalOrdersAcceptanceSent,
2009                        GROUP_CONCAT(q.groupConcatIdsTotalOrdersAcceptanceSent) groupConcatIdsTotalOrdersAcceptanceSent,
2010                        SUM(q.totalOrdersAcceptanceAmountSent) totalOrdersAcceptanceAmountSent,
2011                        CASE {$ticketMedium}
2012                            WHEN 1 THEN COALESCE(SUM(q.totalOrdersAmountSent) / SUM(q.totalOrdersSent), 0)
2013                            WHEN 2 THEN COALESCE(SUM(q.totalOrdersAmountSentAnyMonth) / SUM(q.totalOrdersSentAnyMonth), 0)
2014                            WHEN 3 THEN COALESCE(SUM(q.totalOrdersAcceptanceAmount) / SUM(q.totalOrdersAcceptance), 0)    
2015                            WHEN 4 THEN COALESCE(SUM(q.totalOrdersAcceptanceAmountAnyMonth) / SUM(q.totalOrdersAcceptanceAnyMonth), 0)
2016                            WHEN 5 THEN COALESCE(SUM(q.totalOrdersAcceptanceAmountSent) / SUM(q.totalOrdersAcceptanceSent), 0)
2017                        END AS ticketMedio,
2018                        CASE {$ratioAcceptance}
2019                            WHEN 1 THEN COALESCE(SUM(q.totalOrdersAcceptanceSent) / SUM(q.totalOrdersSent), 0)
2020                            WHEN 2 THEN COALESCE(SUM(q.totalOrdersAcceptanceAmount) / SUM(q.totalOrdersAmountSent), 0)
2021                        END AS ratioAcceptance,
2022                        COALESCE(
2023                        CASE {$acceptance}
2024                            WHEN 1 THEN
2025                                CASE {$ticketMedium}
2026                                WHEN 1 THEN SUM(q.totalOrdersAmountSent)
2027                                / NULLIF(SUM(q.totalOrdersSent), 0)
2028                                WHEN 2 THEN SUM(q.totalOrdersAmountSentAnyMonth)
2029                                / NULLIF(SUM(q.totalOrdersSentAnyMonth), 0)
2030                                WHEN 3 THEN SUM(q.totalOrdersAcceptanceAmount)
2031                                / NULLIF(SUM(q.totalOrdersAcceptance), 0)
2032                                WHEN 4 THEN SUM(q.totalOrdersAcceptanceAmountAnyMonth)
2033                                / NULLIF(SUM(q.totalOrdersAcceptanceAnyMonth), 0)
2034                                WHEN 5 THEN SUM(q.totalOrdersAcceptanceAmountSent)
2035                                / NULLIF(SUM(q.totalOrdersAcceptanceSent), 0)
2036                                END
2037                                / NULLIF(SUM(q.totalOrdersSentAnyMonth), 0)
2038
2039
2040                            WHEN 2 THEN
2041                            SUM(q.totalOrdersAmountSentAnyMonth)
2042                            / NULLIF(SUM(q.totalOrdersSentAnyMonth), 0)
2043                            END,
2044                            0
2045                        )  AS acceptance,
2046                        COALESCE(SUM(q.totalOrdersAcceptance) / SUM(q.totalOrdersSent) * 100, 0) percentageAcceptanceOneN,
2047                        COALESCE(SUM(q.totalOrdersAcceptanceAmount) / SUM(q.totalOrdersAmountSent) * 100, 0) percentageAcceptanceOneC,
2048                        COALESCE(SUM(q.totalOrdersAcceptanceSent) / SUM(q.totalOrdersSentAnyMonth) * 100, 0) percentageAcceptanceTwoN,
2049                        COALESCE(SUM(q.totalOrdersAcceptanceAmount) / SUM(q.totalOrdersAmountSentAnyMonth) * 100, 0) percentageAcceptanceTwoC,                            
2050                        COALESCE(SUM(q.totalOrdersAcceptanceAmountSent) - SUM(q.total_investment), 0) leadsBenefitMonth,
2051                        COALESCE(SUM(q.totalOrdersAcceptance1) - SUM(q.total_investment), 0) realBenefit,
2052                        SUM(q.totalOrdersSent1) totalOrdersSent1,
2053                        SUM(q.totalOrdersAmountSent1) totalOrdersAmountSent1,
2054                        GROUP_CONCAT(q.groupConcatIdsTotalOrdersSent1) groupConcatIdsTotalOrdersSent1,
2055                        SUM(q.totalOrdersAcceptance1) totalOrdersAcceptance1,
2056                        SUM(q.totalOrdersAcceptanceAmount1) totalOrdersAcceptanceAmount1,
2057                        GROUP_CONCAT(q.groupConcatIdsTotalOrdersAcceptance1) groupConcatIdsTotalOrdersAcceptance1
2058                    FROM
2059                    (
2060                    SELECT
2061                        c.region company_name,
2062                        c.company_id,
2063                        c.total_investment,
2064                        COUNT(1) totalOrders,                            
2065                        SUM(q.amount) totalOrdersAmount,
2066                        GROUP_CONCAT(q.id) groupConcatIdsTotalOrders,
2067                        0 totalOrdersSent,                            
2068                        0 totalOrdersAmountSent,
2069                        NULL groupConcatIdsTotalOrdersSent,
2070                        0 totalOrdersSentAnyMonth,
2071                        0 totalOrdersAmountSentAnyMonth,
2072                        NULL groupConcatIdsTotalOrdersSentAnyMonth,    
2073                        0 totalOrdersAcceptance,                            
2074                        0 totalOrdersAcceptanceAmount,
2075                        NULL groupConcatIdsTotalOrdersAcceptance,
2076                        0 totalOrdersAcceptanceAnyMonth,                            
2077                        0 totalOrdersAcceptanceAmountAnyMonth,
2078                        NULL groupConcatIdsTotalOrdersAcceptanceAnyMonth,
2079                        COUNT(CASE WHEN q.acceptance_date IS NOT NULL AND q.acceptance_date != '0000-00-00 00:00:00' AND q.budget_status_id = 3 THEN 1 END) totalOrdersAcceptanceSent,                            
2080                        SUM(CASE WHEN q.acceptance_date IS NOT NULL AND q.acceptance_date != '0000-00-00 00:00:00' AND q.budget_status_id = 3 THEN q.amount END) totalOrdersAcceptanceAmountSent,
2081                        GROUP_CONCAT(CASE WHEN q.acceptance_date IS NOT NULL AND q.acceptance_date != '0000-00-00 00:00:00' AND q.budget_status_id = 3 THEN q.id END) groupConcatIdsTotalOrdersAcceptanceSent,
2082                        0 totalOrdersSent1,
2083                        0 totalOrdersAmountSent1,
2084                        NULL groupConcatIdsTotalOrdersSent1,
2085                        0 totalOrdersAcceptance1,
2086                        0 totalOrdersAcceptanceAmount1,
2087                        NULL groupConcatIdsTotalOrdersAcceptance1
2088                    FROM tbl_quotations q
2089                    LEFT JOIN tbl_sources s
2090                        ON q.source_id = s.source_id
2091                    LEFT JOIN tbl_companies c
2092                        ON q.company_id = c.company_id
2093                    WHERE
2094                        q.for_add = 0                              
2095                        AND COALESCE(q.sent_date, q.issue_date) IS NOT NULL
2096                        AND (q.commercial IS NOT NULL AND q.commercial != '')
2097                        AND q.source_id IS NOT NULL
2098                        AND s.digital_campaign_source > 0
2099                        AND q.budget_status_id != 18
2100                        {$where}
2101                        {$issuedRange}
2102                    GROUP BY q.company_id
2103
2104                    UNION ALL
2105
2106                    SELECT
2107                        c.region company_name,
2108                        c.company_id,
2109                        0 total_investment,
2110                        0 totalOrders,
2111                        0 totalOrdersAmount,                            
2112                        NULL groupConcatIdsTotalOrders,                            
2113                        0 totalOrdersSent,                            
2114                        0 totalOrdersAmountSent,
2115                        NULL groupConcatIdsTotalOrdersSent,
2116                        0 totalOrdersSentAnyMonth,
2117                        0 totalOrdersAmountSentAnyMonth,
2118                        NULL groupConcatIdsTotalOrdersSentAnyMonth,    
2119                        COUNT(1) totalOrdersAcceptance,
2120                        SUM(q.amount) totalOrdersAcceptanceAmount,
2121                        GROUP_CONCAT(q.id) groupConcatIdsTotalOrdersAcceptance,
2122                        0 totalOrdersAcceptanceAnyMonth,                            
2123                        0 totalOrdersAcceptanceAmountAnyMonth,
2124                        NULL groupConcatIdsTotalOrdersAcceptanceAnyMonth,
2125                        0 totalOrdersAcceptanceSent,                            
2126                        0 totalOrdersAcceptanceAmountSent,
2127                        NULL groupConcatIdsTotalOrdersAcceptanceSent,
2128                        0 totalOrdersSent1,
2129                        0 totalOrdersAmountSent1,
2130                        NULL groupConcatIdsTotalOrdersSent1,
2131                        0 totalOrdersAcceptance1,
2132                        0 totalOrdersAcceptanceAmount1,
2133                        NULL groupConcatIdsTotalOrdersAcceptance1
2134                    FROM tbl_quotations q
2135                    LEFT JOIN tbl_sources s
2136                        ON q.source_id = s.source_id
2137                    LEFT JOIN tbl_companies c
2138                        ON q.company_id = c.company_id
2139                    WHERE
2140                        q.for_add = 0
2141                        AND q.acceptance_date IS NOT NULL 
2142                        AND q.acceptance_date != '0000-00-00 00:00:00'
2143                        AND (q.commercial IS NOT NULL AND q.commercial != '')
2144                        AND q.source_id IS NOT NULL
2145                        AND s.digital_campaign_source > 0
2146                        AND q.budget_status_id = 3
2147                        {$where}
2148                        {$createdRange}
2149                    GROUP BY q.company_id
2150
2151                    UNION ALL
2152
2153                    SELECT
2154                        c.region company_name,
2155                        c.company_id,
2156                        0 total_investment,
2157                        0 totalOrders,
2158                        0 totalOrdersAmount,                            
2159                        NULL groupConcatIdsTotalOrders,                            
2160                        COUNT(1) totalOrdersSent,                            
2161                        SUM(q.amount) totalOrdersAmountSent,
2162                        GROUP_CONCAT(q.id) groupConcatIdsTotalOrdersSent,
2163                        0 totalOrdersSentAnyMonth,
2164                        0 totalOrdersAmountSentAnyMonth,
2165                        NULL groupConcatIdsTotalOrdersSentAnyMonth,    
2166                        0 totalOrdersAcceptance,
2167                        0 totalOrdersAcceptanceAmount,
2168                        NULL groupConcatIdsTotalOrdersAcceptance,
2169                        0 totalOrdersAcceptanceAnyMonth,                            
2170                        0 totalOrdersAcceptanceAmountAnyMonth,
2171                        NULL groupConcatIdsTotalOrdersAcceptanceAnyMonth,
2172                        0 totalOrdersAcceptanceSent,                            
2173                        0 totalOrdersAcceptanceAmountSent,
2174                        NULL groupConcatIdsTotalOrdersAcceptanceSent,
2175                        0 totalOrdersSent1,
2176                        0 totalOrdersAmountSent1,
2177                        NULL groupConcatIdsTotalOrdersSent1,
2178                        0 totalOrdersAcceptance1,
2179                        0 totalOrdersAcceptanceAmount1,
2180                        NULL groupConcatIdsTotalOrdersAcceptance1
2181                    FROM tbl_quotations q
2182                    LEFT JOIN tbl_sources s
2183                        ON q.source_id = s.source_id
2184                    LEFT JOIN tbl_companies c
2185                        ON q.company_id = c.company_id
2186                    WHERE
2187                        q.for_add = 0
2188                        AND COALESCE(q.sent_date, q.issue_date) IS NOT NULL 
2189                        AND (q.commercial IS NOT NULL AND q.commercial != '')
2190                        AND q.source_id IS NOT NULL
2191                        AND s.digital_campaign_source > 0
2192                        {$where}
2193                        {$createdRange}
2194                    GROUP BY q.company_id
2195
2196                    UNION ALL
2197
2198                    SELECT
2199                        c.region company_name,
2200                        c.company_id,
2201                        0 total_investment,
2202                        0 totalOrders,
2203                        0 totalOrdersAmount,                            
2204                        NULL groupConcatIdsTotalOrders,
2205                        0 totalOrdersSent,                            
2206                        0 totalOrdersAmountSent,
2207                        NULL groupConcatIdsTotalOrdersSent,
2208                        0 totalOrdersSentAnyMonth,
2209                        0 totalOrdersAmountSentAnyMonth,
2210                        NULL groupConcatIdsTotalOrdersSentAnyMonth,    
2211                        0 totalOrdersAcceptance,
2212                        0 totalOrdersAcceptanceAmount,
2213                        NULL groupConcatIdsTotalOrdersAcceptance,
2214                        0 totalOrdersAcceptanceAnyMonth,                            
2215                        0 totalOrdersAcceptanceAmountAnyMonth,
2216                        NULL groupConcatIdsTotalOrdersAcceptanceAnyMonth,
2217                        0 totalOrdersAcceptanceSent,                            
2218                        0 totalOrdersAcceptanceAmountSent,
2219                        NULL groupConcatIdsTotalOrdersAcceptanceSent,
2220                        0 totalOrdersSent1,
2221                        0 totalOrdersAmountSent1,
2222                        NULL groupConcatIdsTotalOrdersSent1,
2223                        0 totalOrdersAcceptance1,
2224                        0 totalOrdersAcceptanceAmount1,
2225                        NULL groupConcatIdsTotalOrdersAcceptance1
2226                    FROM tbl_quotations q
2227                    LEFT JOIN tbl_sources s
2228                        ON q.source_id = s.source_id
2229                    LEFT JOIN tbl_companies c
2230                        ON q.company_id = c.company_id
2231                    WHERE
2232                        q.for_add = 0                         
2233                        AND COALESCE(q.sent_date, q.issue_date) IS NOT NULL 
2234                        AND (q.commercial IS NOT NULL AND q.commercial != '')
2235                        AND q.source_id IS NOT NULL
2236                        AND s.digital_campaign_source > 0
2237                        AND q.budget_status_id = 2
2238                        AND q.budget_status_id != 18
2239                        {$where}
2240                        {$requestRange}
2241                    GROUP BY q.company_id
2242
2243                    UNION ALL
2244
2245                    SELECT
2246                        c.region company_name,
2247                        c.company_id,
2248                        0 total_investment,
2249                        0 totalOrders,
2250                        0 totalOrdersAmount,
2251                        0 totalOrdersSent,                            
2252                        0 totalOrdersAmountSent,
2253                        NULL groupConcatIdsTotalOrdersSent,
2254                        NULL groupConcatIdsTotalOrders,
2255                        COUNT(1) totalOrdersSentAnyMonth,
2256                        SUM(q.amount) totalOrdersAmountSentAnyMonth,
2257                        GROUP_CONCAT(q.id) groupConcatIdsTotalOrdersSentAnyMonth,    
2258                        0 totalOrdersAcceptance,
2259                        0 totalOrdersAcceptanceAmount,
2260                        NULL groupConcatIdsTotalOrdersAcceptance,
2261                        0 totalOrdersAcceptanceAnyMonth,                            
2262                        0 totalOrdersAcceptanceAmountAnyMonth,
2263                        NULL groupConcatIdsTotalOrdersAcceptanceAnyMonth,
2264                        0 totalOrdersAcceptanceSent,                            
2265                        0 totalOrdersAcceptanceAmountSent,
2266                        NULL groupConcatIdsTotalOrdersAcceptanceSent,
2267                        0 totalOrdersSent1,
2268                        0 totalOrdersAmountSent1,
2269                        NULL groupConcatIdsTotalOrdersSent1,
2270                        0 totalOrdersAcceptance1,
2271                        0 totalOrdersAcceptanceAmount1,
2272                        NULL groupConcatIdsTotalOrdersAcceptance1
2273                    FROM tbl_quotations q
2274                    LEFT JOIN tbl_sources s
2275                        ON q.source_id = s.source_id
2276                    LEFT JOIN tbl_companies c
2277                        ON q.company_id = c.company_id
2278                    WHERE
2279                        q.for_add = 0                          
2280                        AND q.request_date IS NOT NULL 
2281                        AND (q.commercial IS NOT NULL AND q.commercial != '')
2282                        AND q.source_id IS NOT NULL
2283                        AND s.digital_campaign_source > 0
2284                        AND q.budget_status_id != 18
2285                        {$where}
2286                        {$issuedRange}
2287                    GROUP BY q.company_id
2288
2289                    UNION ALL
2290
2291                    SELECT
2292                        c.region company_name,
2293                        c.company_id,
2294                        0 total_investment,
2295                        0 totalOrders,
2296                        0 totalOrdersAmount,
2297                        0 totalOrdersSent,                            
2298                        0 totalOrdersAmountSent,
2299                        NULL groupConcatIdsTotalOrdersSent,
2300                        NULL groupConcatIdsTotalOrders,
2301                        0 totalOrdersSentAnyMonth,
2302                        0 totalOrdersAmountSentAnyMonth,
2303                        NULL groupConcatIdsTotalOrdersSentAnyMonth,    
2304                        0 totalOrdersAcceptance,
2305                        0 totalOrdersAcceptanceAmount,
2306                        NULL groupConcatIdsTotalOrdersAcceptance,
2307                        0 totalOrdersAcceptanceAnyMonth,                            
2308                        0 totalOrdersAcceptanceAmountAnyMonth,
2309                        NULL groupConcatIdsTotalOrdersAcceptanceAnyMonth,
2310                        0 totalOrdersAcceptanceSent,                            
2311                        0 totalOrdersAcceptanceAmountSent,
2312                        NULL groupConcatIdsTotalOrdersAcceptanceSent,
2313                        COUNT(1) totalOrdersSent1,
2314                        SUM(q.amount) totalOrdersAmountSent1,
2315                        GROUP_CONCAT(q.id) groupConcatIdsTotalOrdersSent1,
2316                        0 totalOrdersAcceptance1,
2317                        0 totalOrdersAcceptanceAmount1,
2318                        NULL groupConcatIdsTotalOrdersAcceptance1
2319                    FROM tbl_quotations q
2320                    LEFT JOIN tbl_sources s
2321                        ON q.source_id = s.source_id
2322                    LEFT JOIN tbl_companies c
2323                        ON q.company_id = c.company_id
2324                    WHERE
2325                        q.for_add = 0                          
2326                        AND COALESCE(q.sent_date, q.issue_date) IS NOT NULL 
2327                        AND (q.commercial IS NOT NULL AND q.commercial != '')
2328                        AND q.source_id IS NOT NULL
2329                        AND s.digital_campaign_source > 0
2330                        AND q.budget_status_id = 2
2331                        AND q.budget_status_id != 18
2332                        {$where}
2333                        {$issuedRange}
2334                    GROUP BY q.company_id                                                                           
2335
2336                    UNION ALL
2337
2338                    SELECT
2339                        c.region company_name,
2340                        c.company_id,
2341                        0 total_investment,
2342                        0 totalOrders,
2343                        0 totalOrdersAmount,
2344                        NULL groupConcatIdsTotalOrders,
2345                        0 totalOrdersSent,                            
2346                        0 totalOrdersAmountSent,
2347                        NULL groupConcatIdsTotalOrdersSent,
2348                        0 totalOrdersSentAnyMonth,
2349                        0 totalOrdersAmountSentAnyMonth,
2350                        NULL groupConcatIdsTotalOrdersSentAnyMonth,     
2351                        0 totalOrdersAcceptance,
2352                        0 totalOrdersAcceptanceAmount,
2353                        NULL groupConcatIdsTotalOrdersAcceptance,
2354                        0 totalOrdersAcceptanceAnyMonth,                            
2355                        0 totalOrdersAcceptanceAmountAnyMonth,
2356                        NULL groupConcatIdsTotalOrdersAcceptanceAnyMonth,
2357                        0 totalOrdersAcceptanceSent,                            
2358                        0 totalOrdersAcceptanceAmountSent,
2359                        NULL groupConcatIdsTotalOrdersAcceptanceSent,
2360                        0 totalOrdersSent1,
2361                        0 totalOrdersAmountSent1,
2362                        NULL groupConcatIdsTotalOrdersSent1,
2363                        COUNT(1) totalOrdersAcceptance1,
2364                        SUM(q.amount) totalOrdersAcceptanceAmount1,
2365                        GROUP_CONCAT(q.id) groupConcatIdsTotalOrdersAcceptance1
2366                    FROM tbl_quotations q
2367                    LEFT JOIN tbl_sources s
2368                        ON q.source_id = s.source_id
2369                    LEFT JOIN tbl_companies c
2370                        ON q.company_id = c.company_id
2371                    WHERE
2372                        q.for_add = 0
2373                        AND q.acceptance_date IS NOT NULL
2374                        AND q.acceptance_date != '0000-00-00 00:00:00'                            
2375                        AND (q.commercial IS NOT NULL AND q.commercial != '')
2376                        AND q.source_id IS NOT NULL
2377                        AND s.digital_campaign_source > 0
2378                        AND q.budget_status_id != 18
2379                        {$where}
2380                        {$acceptanceRange}
2381                    GROUP BY q.company_id
2382                ) q
2383                GROUP BY q.company_name WITH ROLLUP";
2384
2385        // $value = Cache::get(base64_encode($query));
2386
2387        // if(!$value){
2388        $result = DB::select($query);
2389
2390        // Cache::put(base64_encode($query), $result, 600);
2391        // }else{
2392        //     $result = $value;
2393        // }
2394
2395        $bigQuery = new BigQueryService;
2396
2397        $query = "SELECT             
2398                        SPLIT(ad_group_name, '_')[OFFSET(1)] AS regions,                                                   
2399                        SUM(metrics_cost_micros) / 1000000 AS investments                                
2400                    FROM `ivb-ai.google_ads_data.ads_GeoStats_8104988947`
2401                    WHERE {$whereBQ}
2402                    GROUP BY  SPLIT(ad_group_name, '_')[OFFSET(1)] 
2403                    ORDER BY  SPLIT(ad_group_name, '_')[OFFSET(1)]";
2404
2405        $resultInvestments = $bigQuery->query($query);
2406
2407        $totalResult = count($result);
2408        $totalResultInvestments = count($resultInvestments);
2409
2410        for ($i = 0; $i < $totalResult; $i++) {
2411
2412            $region = $result[$i]->company_name;
2413
2414            if ($result[$i]->company_name == 'Cataluña') {
2415                $region = 'Barcelona';
2416            }
2417
2418            if ($result[$i]->company_name == 'Valencia') {
2419                $region = 'Comunidad Valenciana';
2420            }
2421
2422            for ($j = 0; $j < $totalResultInvestments; $j++) {
2423
2424                if ($resultInvestments[$j]['regions'] == $region) {
2425                    $result[$i]->totalInvestment = $resultInvestments[$j]['investments'];
2426                }
2427            }
2428        }
2429
2430        return response([
2431            'message' => 'OK',
2432            'data' => $result,
2433        ]);
2434
2435        // } catch (\Exception $e) {
2436        //     report(AppException::fromException($e, 'LIST_PERFORMANCE_METRICS_EXCEPTION'));
2437        //     return response(['message' => 'KO', 'error' => $e->getMessage()]);
2438        // }
2439
2440    }
2441
2442    public function get_kpi_big_query(Request $request)
2443    {
2444
2445        try {
2446
2447            $data = $request->all();
2448
2449            $companyId = addslashes($data['company_id']);
2450            $where = '';
2451
2452            foreach ($data as $key => $value) {
2453                if (is_array($value)) {
2454                    if (! empty($value['start_date'])) {
2455                        $startDates[] = $value['start_date'];
2456                    }
2457                    if (! empty($value['end_date'])) {
2458                        $endDates[] = $value['end_date'];
2459                    }
2460                }
2461            }
2462
2463            $minDate = ! empty($startDates) ? min($startDates) : null;
2464            $maxDate = ! empty($endDates) ? max($endDates) : null;
2465
2466            $region = null;
2467            $where = '';
2468            $whereBQ = '';
2469
2470            if ($companyId != 0) {
2471                $where .= " AND q.company_id = {$companyId} ";
2472                $region = $this->region;
2473
2474                if ($region != 'All') {
2475                    if ($region == 'Cataluña') {
2476                        $whereBQ = " AND ad_group_name LIKE '%Barcelona%'";
2477                    } elseif ($region == 'Comunidad Valenciana') {
2478                        $whereBQ = " AND ad_group_name LIKE '%Valencia%'";
2479                    } else {
2480                        $whereBQ = " AND ad_group_name LIKE '%{$region}%'";
2481                    }
2482                }
2483            }
2484
2485            $regions = [];
2486            $bigQuery = new BigQueryService;
2487            $resultTotal = [];
2488
2489            if (isset($minDate) && $minDate != null) {
2490                if (isset($maxDate) && $maxDate != null) {
2491
2492                    $query = "SELECT
2493                                    CASE
2494                                        WHEN s.name IN ('MDG - MAD - GEN', 'MDG - GUA', 'MDG - MAD M2') THEN 'madrid'
2495                                        WHEN s.name IN ('MDG - BCN - GEN', 'MDG - BCN M2') THEN 'cataluña'
2496                                        WHEN s.name IN ('MDG - VAL - GEN') THEN 'comunidad valenciana'
2497                                        WHEN s.name = 'MDG - MANTENIMIENTO' THEN LOWER(c.region)
2498                                        ELSE LOWER(c.region)
2499                                    END AS region,
2500                                    COUNT(1) totalLeads,
2501                                    SUM(q.amount) amountLeads,                                
2502                                    GROUP_CONCAT(q.id) groupConcatIdsLeads,
2503                                    COUNT(1) totalOrdersIssue,
2504                                    SUM(q.amount) amount,                                
2505                                    GROUP_CONCAT(q.id) groupConcatIds,
2506                                    COUNT(CASE WHEN q.budget_status_id = 3 THEN 1 END) totalOrdersAceptance,                                                                
2507                                    SUM(CASE WHEN q.budget_status_id = 3 THEN q.amount END) amountAcceptance,
2508                                    GROUP_CONCAT(CASE WHEN q.budget_status_id = 3 THEN q.id END) groupConcatIdsAcceptance
2509                                FROM
2510                                    tbl_quotations q
2511                                    LEFT JOIN tbl_sources s
2512                                        ON q.source_id = s.source_id
2513                                    LEFT JOIN tbl_budget_types bt
2514                                        ON bt.budget_type_id = q.budget_type_id
2515                                    LEFT JOIN tbl_companies c
2516                                        ON c.company_id = q.company_id
2517                                WHERE
2518                                    q.request_date IS NOT NULL
2519                                    AND q.for_add = 0
2520                                    AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
2521                                    AND (q.commercial IS NOT NULL AND q.commercial != '')
2522                                    AND s.name IN (
2523                                        'MDG - MAD - GEN', 'MDG - GUA', 'MDG - MAD M2',
2524                                        'MDG - BCN - GEN', 'MDG - BCN M2',
2525                                        'MDG - VAL - GEN',
2526                                        'MDG - MANTENIMIENTO'
2527                                    )
2528                                    AND q.request_date BETWEEN '{$minDate}' AND '{$maxDate}'
2529                                    {$where}
2530                                GROUP BY 1 WITH ROLLUP
2531                                ORDER BY 1";
2532
2533                    $result = DB::select($query);
2534
2535                    $resultTotalIssueAcceptance = [];
2536
2537                    foreach ($result as $row) {
2538                        $region = $row->region ?? 'total';
2539
2540                        $resultTotalIssueAcceptance[$region] = [
2541                            'totalLeads' => (int) $row->totalLeads,
2542                            'amountLeads' => (float) $row->amountLeads,
2543                            'groupConcatIdsLeads' => $row->groupConcatIdsLeads,
2544
2545                            'totalOrdersIssue' => (int) $row->totalOrdersIssue,
2546                            'amount' => (float) $row->amount,
2547                            'groupConcatIds' => $row->groupConcatIds,
2548
2549                            'totalOrdersAceptance' => (int) $row->totalOrdersAceptance,
2550                            'amountAcceptance' => (float) $row->amountAcceptance,
2551                            'groupConcatIdsAcceptance' => $row->groupConcatIdsAcceptance,
2552                        ];
2553                    }
2554
2555                    $query = "WITH base AS (
2556                                    SELECT
2557                                        CASE 
2558                                            WHEN LOWER(SPLIT(ad_group_name, '_')[OFFSET(1)]) = 'barcelona' 
2559                                                THEN 'Cataluña'
2560                                            WHEN LOWER(SPLIT(ad_group_name, '_')[OFFSET(1)]) = 'valencia' 
2561                                                THEN 'Comunidad Valenciana'
2562                                            ELSE SPLIT(ad_group_name, '_')[OFFSET(1)]
2563                                        END AS region,
2564                                        metrics_cost_micros,
2565                                        metrics_clicks,
2566                                        metrics_impressions,
2567                                        metrics_conversions
2568                                    FROM `ivb-ai.google_ads_data.ads_GeoStats_8104988947`
2569                                    WHERE segments_date BETWEEN '{$minDate}' AND '{$maxDate}' 
2570                                    {$whereBQ}
2571                                ),
2572
2573                                agg AS (
2574                                    SELECT
2575                                        region,
2576                                        SUM(metrics_cost_micros) / 1000000 AS investments,
2577                                        SUM(metrics_clicks) AS clicks,
2578                                        SUM(metrics_impressions) AS impressions,
2579                                        SUM(metrics_conversions) AS conversions
2580                                    FROM base
2581                                    GROUP BY region
2582                                ),
2583
2584                                metrics AS (
2585                                    SELECT 'investments' AS metric, region, investments AS value FROM agg
2586                                    UNION ALL
2587                                    SELECT 'clicks', region, clicks FROM agg
2588                                    UNION ALL
2589                                    SELECT 'impressions', region, impressions FROM agg
2590                                    UNION ALL
2591                                    SELECT 'conversions', region, conversions FROM agg
2592                                ),
2593
2594                                totals AS (
2595                                    SELECT metric, 'TOTAL' AS region, SUM(value) AS value
2596                                    FROM metrics
2597                                    GROUP BY metric
2598                                )
2599
2600                                SELECT * FROM metrics
2601                                UNION ALL
2602                                SELECT * FROM totals
2603
2604                                ORDER BY
2605                                    CASE metric
2606                                        WHEN 'investments' THEN 1
2607                                        WHEN 'clicks' THEN 2
2608                                        WHEN 'impressions' THEN 3
2609                                        WHEN 'conversions' THEN 4
2610                                    END,
2611                                    CASE region
2612                                        WHEN 'Cataluña' THEN 1
2613                                        WHEN 'Madrid' THEN 2
2614                                        WHEN 'Comunidad Valenciana' THEN 3
2615                                        WHEN 'Total' THEN 99
2616                                        ELSE 4
2617                                    END,
2618                                    region";
2619
2620                    $rows = $bigQuery->query($query);
2621
2622                    $result = [];
2623
2624                    foreach ($rows as $row) {
2625
2626                        $metricKey = strtolower($row['metric']);
2627
2628                        $regionKey = $row['region'] ? strtolower($row['region']) : 'total';
2629                        $value = (float) $row['value'];
2630
2631                        if (! isset($result[$metricKey])) {
2632                            $result[$metricKey] = [
2633                                'total' => 0,
2634                            ];
2635                        }
2636
2637                        if ($regionKey !== 'total') {
2638                            $result[$metricKey][$regionKey] = $value;
2639                            $result[$metricKey]['total'] += $value;
2640
2641                            if (! in_array($regionKey, $regions)) {
2642                                array_push($regions, $regionKey);
2643                            }
2644                        } else {
2645                            $result[$metricKey]['total'] = $value;
2646                        }
2647
2648                    }
2649
2650                    $resultTotal = [
2651                        'result' => $result,
2652                        'resultTotalIssueAcceptance' => $resultTotalIssueAcceptance,
2653                    ];
2654                }
2655            }
2656
2657            $resultP1 = [];
2658
2659            if (isset($data['p1'])) {
2660                $p1 = $data['p1'];
2661
2662                if (isset($p1['start_date']) && $p1['start_date'] != null) {
2663                    if (isset($p1['end_date']) && $p1['end_date'] != null) {
2664
2665                        $query = "SELECT
2666                                    CASE
2667                                        WHEN s.name IN ('MDG - MAD - GEN', 'MDG - GUA', 'MDG - MAD M2') THEN 'madrid'
2668                                        WHEN s.name IN ('MDG - BCN - GEN', 'MDG - BCN M2') THEN 'cataluña'
2669                                        WHEN s.name IN ('MDG - VAL - GEN') THEN 'comunidad valenciana'
2670                                        WHEN s.name = 'MDG - MANTENIMIENTO' THEN LOWER(c.region)
2671                                        ELSE LOWER(c.region)
2672                                    END AS region,
2673                                    COUNT(1) totalLeads,
2674                                    SUM(q.amount) amountLeads,                                
2675                                    GROUP_CONCAT(q.id) groupConcatIdsLeads,
2676                                    COUNT(1) totalOrdersIssue,
2677                                    SUM(q.amount) amount,                                
2678                                    GROUP_CONCAT(q.id) groupConcatIds,
2679                                    COUNT(CASE WHEN q.budget_status_id = 3 THEN 1 END) totalOrdersAceptance,                                                                
2680                                    SUM(CASE WHEN q.budget_status_id = 3 THEN q.amount END) amountAcceptance,
2681                                    GROUP_CONCAT(CASE WHEN q.budget_status_id = 3 THEN q.id END) groupConcatIdsAcceptance
2682                                FROM
2683                                    tbl_quotations q
2684                                    LEFT JOIN tbl_sources s
2685                                        ON q.source_id = s.source_id
2686                                    LEFT JOIN tbl_budget_types bt
2687                                        ON bt.budget_type_id = q.budget_type_id
2688                                    LEFT JOIN tbl_companies c
2689                                        ON c.company_id = q.company_id
2690                                WHERE
2691                                    q.request_date IS NOT NULL
2692                                    AND q.for_add = 0
2693                                    AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
2694                                    AND (q.commercial IS NOT NULL AND q.commercial != '')
2695                                    AND s.name IN (
2696                                        'MDG - MAD - GEN', 'MDG - GUA', 'MDG - MAD M2',
2697                                        'MDG - BCN - GEN', 'MDG - BCN M2',
2698                                        'MDG - VAL - GEN',
2699                                        'MDG - MANTENIMIENTO'
2700                                    )
2701                                    AND q.request_date BETWEEN '{$p1['start_date']}' AND '{$p1['end_date']}'
2702                                    {$where}
2703                                GROUP BY 1 WITH ROLLUP
2704                                ORDER BY 1";
2705
2706                        $result = DB::select($query);
2707
2708                        $resultTotalIssueAcceptance = [];
2709
2710                        foreach ($result as $row) {
2711                            $region = $row->region ?? 'total';
2712
2713                            $resultTotalIssueAcceptance[$region] = [
2714                                'totalLeads' => (int) $row->totalLeads,
2715                                'amountLeads' => (float) $row->amountLeads,
2716                                'groupConcatIdsLeads' => $row->groupConcatIdsLeads,
2717
2718                                'totalOrdersIssue' => (int) $row->totalOrdersIssue,
2719                                'amount' => (float) $row->amount,
2720                                'groupConcatIds' => $row->groupConcatIds,
2721
2722                                'totalOrdersAceptance' => (int) $row->totalOrdersAceptance,
2723                                'amountAcceptance' => (float) $row->amountAcceptance,
2724                                'groupConcatIdsAcceptance' => $row->groupConcatIdsAcceptance,
2725                            ];
2726                        }
2727
2728                        $query = "WITH base AS (
2729                                    SELECT
2730                                        CASE 
2731                                            WHEN LOWER(SPLIT(ad_group_name, '_')[OFFSET(1)]) = 'barcelona' 
2732                                                THEN 'Cataluña'
2733                                            WHEN LOWER(SPLIT(ad_group_name, '_')[OFFSET(1)]) = 'valencia' 
2734                                                THEN 'Comunidad Valenciana'
2735                                            ELSE SPLIT(ad_group_name, '_')[OFFSET(1)]
2736                                        END AS region,
2737                                        metrics_cost_micros,
2738                                        metrics_clicks,
2739                                        metrics_impressions,
2740                                        metrics_conversions
2741                                    FROM `ivb-ai.google_ads_data.ads_GeoStats_8104988947`
2742                                    WHERE segments_date BETWEEN '{$p1['start_date']}' AND '{$p1['end_date']}' 
2743                                    {$whereBQ}
2744                                ),
2745
2746                                agg AS (
2747                                    SELECT
2748                                        region,
2749                                        SUM(metrics_cost_micros) / 1000000 AS investments,
2750                                        SUM(metrics_clicks) AS clicks,
2751                                        SUM(metrics_impressions) AS impressions,
2752                                        SUM(metrics_conversions) AS conversions
2753                                    FROM base
2754                                    GROUP BY region
2755                                ),
2756
2757                                metrics AS (
2758                                    SELECT 'investments' AS metric, region, investments AS value FROM agg
2759                                    UNION ALL
2760                                    SELECT 'clicks', region, clicks FROM agg
2761                                    UNION ALL
2762                                    SELECT 'impressions', region, impressions FROM agg
2763                                    UNION ALL
2764                                    SELECT 'conversions', region, conversions FROM agg
2765                                ),
2766
2767                                totals AS (
2768                                    SELECT metric, 'TOTAL' AS region, SUM(value) AS value
2769                                    FROM metrics
2770                                    GROUP BY metric
2771                                )
2772
2773                                SELECT * FROM metrics
2774                                UNION ALL
2775                                SELECT * FROM totals
2776
2777                                ORDER BY
2778                                    CASE metric
2779                                        WHEN 'investments' THEN 1
2780                                        WHEN 'clicks' THEN 2
2781                                        WHEN 'impressions' THEN 3
2782                                        WHEN 'conversions' THEN 4
2783                                    END,
2784                                    CASE region
2785                                        WHEN 'Cataluña' THEN 1
2786                                        WHEN 'Madrid' THEN 2
2787                                        WHEN 'Comunidad Valenciana' THEN 3
2788                                        WHEN 'Total' THEN 99
2789                                        ELSE 4
2790                                    END,
2791                                    region";
2792
2793                        $rows = $bigQuery->query($query);
2794
2795                        $result = [];
2796
2797                        foreach ($rows as $row) {
2798
2799                            $metricKey = strtolower($row['metric']);
2800
2801                            $regionKey = $row['region'] ? strtolower($row['region']) : 'total';
2802                            $value = (float) $row['value'];
2803
2804                            if (! isset($result[$metricKey])) {
2805                                $result[$metricKey] = [
2806                                    'total' => 0,
2807                                ];
2808                            }
2809
2810                            if ($regionKey !== 'total') {
2811                                $result[$metricKey][$regionKey] = $value;
2812                                $result[$metricKey]['total'] += $value;
2813                            } else {
2814                                $result[$metricKey]['total'] = $value;
2815                            }
2816                        }
2817
2818                        $resultP1 = [
2819                            'result' => $result,
2820                            'resultTotalIssueAcceptance' => $resultTotalIssueAcceptance,
2821                        ];
2822
2823                    }
2824                }
2825            }
2826
2827            $resultP2 = [];
2828            if (isset($data['p2'])) {
2829                $p2 = $data['p2'];
2830
2831                if (isset($p2['start_date']) && $p2['start_date'] != null) {
2832                    if (isset($p2['end_date']) && $p2['end_date'] != null) {
2833
2834                        $query = "SELECT
2835                                    CASE
2836                                        WHEN s.name IN ('MDG - MAD - GEN', 'MDG - GUA', 'MDG - MAD M2') THEN 'madrid'
2837                                        WHEN s.name IN ('MDG - BCN - GEN', 'MDG - BCN M2') THEN 'cataluña'
2838                                        WHEN s.name IN ('MDG - VAL - GEN') THEN 'comunidad valenciana'
2839                                        WHEN s.name = 'MDG - MANTENIMIENTO' THEN LOWER(c.region)
2840                                        ELSE LOWER(c.region)
2841                                    END AS region,
2842                                    COUNT(1) totalLeads,
2843                                    SUM(q.amount) amountLeads,                                
2844                                    GROUP_CONCAT(q.id) groupConcatIdsLeads,
2845                                    COUNT(1) totalOrdersIssue,
2846                                    SUM(q.amount) amount,                                
2847                                    GROUP_CONCAT(q.id) groupConcatIds,
2848                                    COUNT(CASE WHEN q.budget_status_id = 3 THEN 1 END) totalOrdersAceptance,                                                                
2849                                    SUM(CASE WHEN q.budget_status_id = 3 THEN q.amount END) amountAcceptance,
2850                                    GROUP_CONCAT(CASE WHEN q.budget_status_id = 3 THEN q.id END) groupConcatIdsAcceptance
2851                                FROM
2852                                    tbl_quotations q
2853                                    LEFT JOIN tbl_sources s
2854                                        ON q.source_id = s.source_id
2855                                    LEFT JOIN tbl_budget_types bt
2856                                        ON bt.budget_type_id = q.budget_type_id
2857                                    LEFT JOIN tbl_companies c
2858                                        ON c.company_id = q.company_id
2859                                WHERE
2860                                    q.request_date IS NOT NULL
2861                                    AND q.for_add = 0
2862                                    AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
2863                                    AND (q.commercial IS NOT NULL AND q.commercial != '')
2864                                    AND s.name IN (
2865                                        'MDG - MAD - GEN', 'MDG - GUA', 'MDG - MAD M2',
2866                                        'MDG - BCN - GEN', 'MDG - BCN M2',
2867                                        'MDG - VAL - GEN',
2868                                        'MDG - MANTENIMIENTO'
2869                                    )
2870                                    AND q.request_date BETWEEN '{$p2['start_date']}' AND '{$p2['end_date']}'
2871                                    {$where}
2872                                GROUP BY 1 WITH ROLLUP
2873                                ORDER BY 1";
2874
2875                        $result = DB::select($query);
2876
2877                        $resultTotalIssueAcceptance = [];
2878
2879                        foreach ($result as $row) {
2880                            $region = $row->region ?? 'total';
2881
2882                            $resultTotalIssueAcceptance[$region] = [
2883                                'totalLeads' => (int) $row->totalLeads,
2884                                'amountLeads' => (float) $row->amountLeads,
2885                                'groupConcatIdsLeads' => $row->groupConcatIdsLeads,
2886
2887                                'totalOrdersIssue' => (int) $row->totalOrdersIssue,
2888                                'amount' => (float) $row->amount,
2889                                'groupConcatIds' => $row->groupConcatIds,
2890
2891                                'totalOrdersAceptance' => (int) $row->totalOrdersAceptance,
2892                                'amountAcceptance' => (float) $row->amountAcceptance,
2893                                'groupConcatIdsAcceptance' => $row->groupConcatIdsAcceptance,
2894                            ];
2895                        }
2896
2897                        $query = "WITH base AS (
2898                                    SELECT
2899                                        CASE 
2900                                            WHEN LOWER(SPLIT(ad_group_name, '_')[OFFSET(1)]) = 'barcelona' 
2901                                                THEN 'Cataluña'
2902                                            WHEN LOWER(SPLIT(ad_group_name, '_')[OFFSET(1)]) = 'valencia' 
2903                                                THEN 'Comunidad Valenciana'
2904                                            ELSE SPLIT(ad_group_name, '_')[OFFSET(1)]
2905                                        END AS region,
2906                                        metrics_cost_micros,
2907                                        metrics_clicks,
2908                                        metrics_impressions,
2909                                        metrics_conversions
2910                                    FROM `ivb-ai.google_ads_data.ads_GeoStats_8104988947`
2911                                    WHERE segments_date BETWEEN '{$p2['start_date']}' AND '{$p2['end_date']}' 
2912                                    {$whereBQ}
2913                                ),
2914
2915                                agg AS (
2916                                    SELECT
2917                                        region,
2918                                        SUM(metrics_cost_micros) / 1000000 AS investments,
2919                                        SUM(metrics_clicks) AS clicks,
2920                                        SUM(metrics_impressions) AS impressions,
2921                                        SUM(metrics_conversions) AS conversions
2922                                    FROM base
2923                                    GROUP BY region
2924                                ),
2925
2926                                metrics AS (
2927                                    SELECT 'investments' AS metric, region, investments AS value FROM agg
2928                                    UNION ALL
2929                                    SELECT 'clicks', region, clicks FROM agg
2930                                    UNION ALL
2931                                    SELECT 'impressions', region, impressions FROM agg
2932                                    UNION ALL
2933                                    SELECT 'conversions', region, conversions FROM agg
2934                                ),
2935
2936                                totals AS (
2937                                    SELECT metric, 'TOTAL' AS region, SUM(value) AS value
2938                                    FROM metrics
2939                                    GROUP BY metric
2940                                )
2941
2942                                SELECT * FROM metrics
2943                                UNION ALL
2944                                SELECT * FROM totals
2945
2946                                ORDER BY
2947                                    CASE metric
2948                                        WHEN 'investments' THEN 1
2949                                        WHEN 'clicks' THEN 2
2950                                        WHEN 'impressions' THEN 3
2951                                        WHEN 'conversions' THEN 4
2952                                    END,
2953                                    CASE region
2954                                        WHEN 'Cataluña' THEN 1
2955                                        WHEN 'Madrid' THEN 2
2956                                        WHEN 'Comunidad Valenciana' THEN 3
2957                                        WHEN 'Total' THEN 99
2958                                        ELSE 4
2959                                    END,
2960                                    region";
2961
2962                        $rows = $bigQuery->query($query);
2963
2964                        $result = [];
2965
2966                        foreach ($rows as $row) {
2967
2968                            $metricKey = strtolower($row['metric']);
2969
2970                            $regionKey = $row['region'] ? strtolower($row['region']) : 'total';
2971                            $value = (float) $row['value'];
2972
2973                            if (! isset($result[$metricKey])) {
2974                                $result[$metricKey] = [
2975                                    'total' => 0,
2976                                ];
2977                            }
2978
2979                            if ($regionKey !== 'total') {
2980                                $result[$metricKey][$regionKey] = $value;
2981                                $result[$metricKey]['total'] += $value;
2982                            } else {
2983                                $result[$metricKey]['total'] = $value;
2984                            }
2985                        }
2986
2987                        $resultP2 = [
2988                            'result' => $result,
2989                            'resultTotalIssueAcceptance' => $resultTotalIssueAcceptance,
2990                        ];
2991
2992                    }
2993                }
2994            }
2995
2996            $resultP3 = [];
2997
2998            if (isset($data['p3'])) {
2999                $p3 = $data['p3'];
3000
3001                if (isset($p3['start_date']) && $p3['start_date'] != null) {
3002                    if (isset($p3['end_date']) && $p3['end_date'] != null) {
3003
3004                        $query = "SELECT
3005                                    CASE
3006                                        WHEN s.name IN ('MDG - MAD - GEN', 'MDG - GUA', 'MDG - MAD M2') THEN 'madrid'
3007                                        WHEN s.name IN ('MDG - BCN - GEN', 'MDG - BCN M2') THEN 'cataluña'
3008                                        WHEN s.name IN ('MDG - VAL - GEN') THEN 'comunidad valenciana'
3009                                        WHEN s.name = 'MDG - MANTENIMIENTO' THEN LOWER(c.region)
3010                                        ELSE LOWER(c.region)
3011                                    END AS region,
3012                                    COUNT(1) totalLeads,
3013                                    SUM(q.amount) amountLeads,                                
3014                                    GROUP_CONCAT(q.id) groupConcatIdsLeads,
3015                                    COUNT(1) totalOrdersIssue,
3016                                    SUM(q.amount) amount,                                
3017                                    GROUP_CONCAT(q.id) groupConcatIds,
3018                                    COUNT(CASE WHEN q.budget_status_id = 3 THEN 1 END) totalOrdersAceptance,                                                                
3019                                    SUM(CASE WHEN q.budget_status_id = 3 THEN q.amount END) amountAcceptance,
3020                                    GROUP_CONCAT(CASE WHEN q.budget_status_id = 3 THEN q.id END) groupConcatIdsAcceptance
3021                                FROM
3022                                    tbl_quotations q
3023                                    LEFT JOIN tbl_sources s
3024                                        ON q.source_id = s.source_id
3025                                    LEFT JOIN tbl_budget_types bt
3026                                        ON bt.budget_type_id = q.budget_type_id
3027                                    LEFT JOIN tbl_companies c
3028                                        ON c.company_id = q.company_id
3029                                WHERE
3030                                    q.request_date IS NOT NULL
3031                                    AND q.for_add = 0
3032                                    AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
3033                                    AND (q.commercial IS NOT NULL AND q.commercial != '')
3034                                    AND s.name IN (
3035                                        'MDG - MAD - GEN', 'MDG - GUA', 'MDG - MAD M2',
3036                                        'MDG - BCN - GEN', 'MDG - BCN M2',
3037                                        'MDG - VAL - GEN',
3038                                        'MDG - MANTENIMIENTO'
3039                                    )
3040                                    AND q.request_date BETWEEN '{$p3['start_date']}' AND '{$p3['end_date']}'
3041                                    {$where}
3042                                GROUP BY 1 WITH ROLLUP
3043                                ORDER BY 1";
3044
3045                        $result = DB::select($query);
3046
3047                        $resultTotalIssueAcceptance = [];
3048
3049                        foreach ($result as $row) {
3050                            $region = $row->region ?? 'total';
3051
3052                            $resultTotalIssueAcceptance[$region] = [
3053                                'totalLeads' => (int) $row->totalLeads,
3054                                'amountLeads' => (float) $row->amountLeads,
3055                                'groupConcatIdsLeads' => $row->groupConcatIdsLeads,
3056
3057                                'totalOrdersIssue' => (int) $row->totalOrdersIssue,
3058                                'amount' => (float) $row->amount,
3059                                'groupConcatIds' => $row->groupConcatIds,
3060
3061                                'totalOrdersAceptance' => (int) $row->totalOrdersAceptance,
3062                                'amountAcceptance' => (float) $row->amountAcceptance,
3063                                'groupConcatIdsAcceptance' => $row->groupConcatIdsAcceptance,
3064                            ];
3065                        }
3066
3067                        $query = "WITH base AS (
3068                                    SELECT
3069                                        CASE 
3070                                            WHEN LOWER(SPLIT(ad_group_name, '_')[OFFSET(1)]) = 'barcelona' 
3071                                                THEN 'Cataluña'
3072                                            WHEN LOWER(SPLIT(ad_group_name, '_')[OFFSET(1)]) = 'valencia' 
3073                                                THEN 'Comunidad Valenciana'
3074                                            ELSE SPLIT(ad_group_name, '_')[OFFSET(1)]
3075                                        END AS region,
3076                                        metrics_cost_micros,
3077                                        metrics_clicks,
3078                                        metrics_impressions,
3079                                        metrics_conversions
3080                                    FROM `ivb-ai.google_ads_data.ads_GeoStats_8104988947`
3081                                    WHERE segments_date BETWEEN '{$p3['start_date']}' AND '{$p3['end_date']}' 
3082                                    {$whereBQ}
3083                                ),
3084
3085                                agg AS (
3086                                    SELECT
3087                                        region,
3088                                        SUM(metrics_cost_micros) / 1000000 AS investments,
3089                                        SUM(metrics_clicks) AS clicks,
3090                                        SUM(metrics_impressions) AS impressions,
3091                                        SUM(metrics_conversions) AS conversions
3092                                    FROM base
3093                                    GROUP BY region
3094                                ),
3095
3096                                metrics AS (
3097                                    SELECT 'investments' AS metric, region, investments AS value FROM agg
3098                                    UNION ALL
3099                                    SELECT 'clicks', region, clicks FROM agg
3100                                    UNION ALL
3101                                    SELECT 'impressions', region, impressions FROM agg
3102                                    UNION ALL
3103                                    SELECT 'conversions', region, conversions FROM agg
3104                                ),
3105
3106                                totals AS (
3107                                    SELECT metric, 'TOTAL' AS region, SUM(value) AS value
3108                                    FROM metrics
3109                                    GROUP BY metric
3110                                )
3111
3112                                SELECT * FROM metrics
3113                                UNION ALL
3114                                SELECT * FROM totals
3115
3116                                ORDER BY
3117                                    CASE metric
3118                                        WHEN 'investments' THEN 1
3119                                        WHEN 'clicks' THEN 2
3120                                        WHEN 'impressions' THEN 3
3121                                        WHEN 'conversions' THEN 4
3122                                    END,
3123                                    CASE region
3124                                        WHEN 'Cataluña' THEN 1
3125                                        WHEN 'Madrid' THEN 2
3126                                        WHEN 'Comunidad Valenciana' THEN 3
3127                                        WHEN 'Total' THEN 99
3128                                        ELSE 4
3129                                    END,
3130                                    region";
3131
3132                        $rows = $bigQuery->query($query);
3133
3134                        $result = [];
3135
3136                        foreach ($rows as $row) {
3137
3138                            $metricKey = strtolower($row['metric']);
3139
3140                            $regionKey = $row['region'] ? strtolower($row['region']) : 'total';
3141                            $value = (float) $row['value'];
3142
3143                            if (! isset($result[$metricKey])) {
3144                                $result[$metricKey] = [
3145                                    'total' => 0,
3146                                ];
3147                            }
3148
3149                            if ($regionKey !== 'total') {
3150                                $result[$metricKey][$regionKey] = $value;
3151                                $result[$metricKey]['total'] += $value;
3152                            } else {
3153                                $result[$metricKey]['total'] = $value;
3154                            }
3155                        }
3156
3157                        $resultP3 = [
3158                            'result' => $result,
3159                            'resultTotalIssueAcceptance' => $resultTotalIssueAcceptance,
3160                        ];
3161
3162                    }
3163                }
3164            }
3165
3166            array_push($regions, 'total');
3167
3168            return response([
3169                'message' => 'OK',
3170                'totals' => $resultTotal,
3171                'data' => [
3172                    'p1' => $resultP1,
3173                    'p2' => $resultP2,
3174                    'p3' => $resultP3,
3175                ],
3176                'regions' => $regions,
3177            ]);
3178
3179        } catch (\Exception $e) {
3180            /** @disregard P1014 */
3181            $e->exceptionCode = 'GET_KPI_BIG_QUERY_EXCEPTION';
3182            report($e);
3183
3184            return response(['message' => 'KO', 'error' => $e->getMessage()]);
3185        }
3186
3187    }
3188}