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