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

	Code Coverage
	Lines			Functions and Methods				Classes and Traits
Total	0.00% covered (danger)	0.00%	0 / 1046	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 / 1046	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 / 225	0.00% covered (danger)	0.00%	0 / 1	2756

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