Code Coverage for /var/www/html/fire-service/server/app/Http/Controllers/DigitalCampaignAnalytics.php

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