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 / 765	0.00% covered (danger)	0.00%	0 / 14	CRAP	0.00% covered (danger)	0.00%	0 / 1
DigitalCampaignAnalytics	0.00% covered (danger)	0.00%	0 / 765	0.00% covered (danger)	0.00%	0 / 14	32220	0.00% covered (danger)	0.00%	0 / 1
__construct	0.00% covered (danger)	0.00%	0 / 14	0.00% covered (danger)	0.00%	0 / 1	20
list_campaign_channel_and_landing	0.00% covered (danger)	0.00%	0 / 36	0.00% covered (danger)	0.00%	0 / 1	342
list_orders_ia	0.00% covered (danger)	0.00%	0 / 32	0.00% covered (danger)	0.00%	0 / 1	240
list_lead_status	0.00% covered (danger)	0.00%	0 / 39	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 / 123	0.00% covered (danger)	0.00%	0 / 1	110
list_g3w_order_status	0.00% covered (danger)	0.00%	0 / 106	0.00% covered (danger)	0.00%	0 / 1	650
get_sources_digital_campaign_analytics	0.00% covered (danger)	0.00%	0 / 17	0.00% covered (danger)	0.00%	0 / 1	6
update_final_summary	0.00% covered (danger)	0.00%	0 / 11	0.00% covered (danger)	0.00%	0 / 1	6
get_final_summary	0.00% covered (danger)	0.00%	0 / 35	0.00% covered (danger)	0.00%	0 / 1	42
get_main_kpis	0.00% covered (danger)	0.00%	0 / 133	0.00% covered (danger)	0.00%	0 / 1	812
list_type_of_order	0.00% covered (danger)	0.00%	0 / 150	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 / 50	0.00% covered (danger)	0.00%	0 / 1	156

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\Support\Facades\DB;
17	use Illuminate\Support\Facades\Log;
18
19	class DigitalCampaignAnalytics extends Controller
20	{
21	private $locale;
22
23	private $userId;
24
25	private $region;
26
27	private $companyIds;
28
29	private $companyId;
30
31	private $workService;
32
33	public function __construct(WorkService $workService)
34	{
35	$this->workService = $workService;
36	$this->locale = @getallheaders()['Locale-ID'];
37	$this->userId = @getallheaders()['User-ID'];
38	$this->region = @getallheaders()['Region'];
39
40	App::setLocale($this->locale);
41
42	$this->companyIds = [];
43
44	if ($this->region != null && $this->region != '' && $this->region != 'All') {
45	$this->region = urldecode($this->region);
46
47	$query = 'SELECT
48	b.company_id
49	FROM
50	tbl_company_users a
51	LEFT JOIN tbl_companies b ON a.company_id = b.company_id
52	WHERE
53	a.user_id = ?
54	AND b.region = ?';
55
56	$this->companyIds = DB::select($query, [intval($this->userId), $this->region]);
57
58	$this->companyIds = collect($this->companyIds)->pluck('company_id')->toArray();
59	} else {
60	$this->companyIds = TblCompanyUsers::where('user_id', $this->userId)->pluck('company_id')->all();
61	}
62
63	$this->companyId = implode(',', $this->companyIds);
64	}
65
66	public function list_campaign_channel_and_landing(Request $request)
67	{
68
69	try {
70
71	$data = $request->all();
72
73	$companyId = intval($data['company_id']);
74	$where = '';
75
76	if ($companyId != 0) {
77	$where .= " AND q.company_id = {$companyId} ";
78	} else {
79	$where .= " AND q.company_id IN ({$this->companyId}) ";
80	}
81
82	if (isset($data['commercial']) && $data['commercial'] != null) {
83	$commercial = implode("','", $data['commercial']);
84	if (count($data['commercial']) > 0) {
85	$where .= " AND q.commercial IN ('{$commercial}') ";
86	}
87	}
88
89	if (isset($data['source_id']) && $data['source_id'] != null) {
90	$sourceId = implode("','", $data['source_id']);
91	if (count($data['source_id']) > 0) {
92	$where .= " AND q.source_id IN ('{$sourceId}') ";
93	}
94	}
95
96	if ((isset($data['start_date']) && $data['start_date'] != null) && (isset($data['end_date']) && $data['end_date'] != null)) {
97	$where .= " AND q.request_date BETWEEN '{$data['start_date']}' AND '{$data['end_date']}' ";
98	}
99
100	$sortBy = '';
101
102	if (isset($data['sort_by']) && $data['sort_by'] != null) {
103	if (isset($data['column']) && $data['column'] != null) {
104	$sortBy = ", {$data['column']} {$data['sort_by']} ";
105	}
106	}
107
108	$query = "SELECT q.* FROM
109	(
110	SELECT
111	c.region,
112	s.source_id,
113	s.name source,
114	COUNT(1) totalOrders,
115	COUNT(CASE WHEN q.acceptance_date IS NOT NULL AND q.acceptance_date != '0000-00-00 00:00:00' THEN 1 END) totalOrdersAcceptance,
116	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,
117	SUM(q.amount) totalOrdersAmount,
118	AVG(q.amount) averageOrdersAmount,
119	SUM(CASE WHEN q.acceptance_date IS NOT NULL AND q.acceptance_date != '0000-00-00 00:00:00' THEN q.amount END) totalOrdersAcceptanceAmount,
120	AVG(CASE WHEN q.acceptance_date IS NOT NULL AND q.acceptance_date != '0000-00-00 00:00:00' THEN q.amount END) averageOrdersAcceptanceAmount,
121	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,
122	GROUP_CONCAT(q.id) groupConcatIdsTotalOrders,
123	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,
124	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
125	FROM tbl_quotations q
126	LEFT JOIN tbl_sources s
127	ON q.source_id = s.source_id
128	LEFT JOIN tbl_companies c
129	ON q.company_id = c.company_id
130	WHERE
131	q.for_add = 0
132	AND (q.commercial IS NOT NULL AND q.commercial != '')
133	AND q.source_id IS NOT NULL
134	AND s.digital_campaign_source > 0
135	AND q.budget_status_id != 18
136	{$where}
137	GROUP BY c.region, s.source_id WITH ROLLUP
138	ORDER BY
139	(c.region IS NULL AND s.source_id IS NULL) ASC,
140	c.region IS NULL ASC,
141	c.region ASC,
142	(s.source_id IS NULL) DESC,
143	CASE WHEN s.source_id IS NOT NULL THEN COUNT(1) ELSE NULL END DESC
144	) q ORDER BY (q.region IS NULL AND q.source_id IS NULL) ASC, q.region ASC, (q.source_id IS NULL) DESC {$sortBy}";
145
146	$value = Cache::get(base64_encode($query));
147
148	if (! $value) {
149	$result = DB::select($query);
150
151	Cache::put(base64_encode($query), $result, 600);
152	} else {
153	$result = $value;
154	}
155
156	return response([
157	'message' => 'OK',
158	'data' => $result,
159	]);
160
161	} catch (\Exception $e) {
162	/** @disregard P1014 */
163	$e->exceptionCode = 'DIGITAL_CAMPAIGN_CHANNEL_LANDING_EXCEPTION';
164	report($e);
165
166	return response(['message' => 'KO', 'error' => $e->getMessage()]);
167	}
168	}
169
170	public function list_orders_ia(Request $request)
171	{
172
173	try {
174
175	$data = $request->all();
176
177	$companyId = intval($data['company_id']);
178	$where = '';
179
180	if ($companyId != 0) {
181	$where .= " AND q.company_id = {$companyId} ";
182	} else {
183	$where .= " AND q.company_id IN ({$this->companyId}) ";
184	}
185
186	if (isset($data['commercial']) && $data['commercial'] != null) {
187	$commercial = implode("','", $data['commercial']);
188	if (count($data['commercial']) > 0) {
189	$where .= " AND q.commercial IN ('{$commercial}') ";
190	}
191	}
192
193	if ((isset($data['start_date']) && $data['start_date'] != null) && (isset($data['end_date']) && $data['end_date'] != null)) {
194	$where .= " AND q.request_date BETWEEN '{$data['start_date']}' AND '{$data['end_date']}' ";
195	}
196
197	$sortBy = '';
198
199	if (isset($data['sort_by']) && $data['sort_by'] != null) {
200	if (isset($data['column']) && $data['column'] != null) {
201	$sortBy = ", {$data['column']} {$data['sort_by']} ";
202	}
203	}
204
205	$query = "SELECT q.* FROM
206	(
207	SELECT
208	c.region,
209	s.source_id,
210	s.name source,
211	COUNT(1) totalOrders,
212	COUNT(CASE WHEN q.acceptance_date IS NOT NULL AND q.acceptance_date != '0000-00-00 00:00:00' THEN 1 END) totalOrdersAcceptance,
213	COUNT(CASE WHEN q.acceptance_date IS NOT NULL AND q.acceptance_date != '0000-00-00 00:00:00' THEN 1 END) / COUNT(1) * 100 percentageOrders,
214	SUM(q.amount) totalOrdersAmount,
215	AVG(q.amount) averageOrdersAmount,
216	SUM(CASE WHEN q.acceptance_date IS NOT NULL AND q.acceptance_date != '0000-00-00 00:00:00' THEN q.amount END) totalOrdersAcceptanceAmount,
217	AVG(CASE WHEN q.acceptance_date IS NOT NULL AND q.acceptance_date != '0000-00-00 00:00:00' THEN q.amount END) averageOrdersAcceptanceAmount,
218	SUM(CASE WHEN q.acceptance_date IS NOT NULL AND q.acceptance_date != '0000-00-00 00:00:00' THEN q.amount END) / SUM(q.amount) * 100 percentageOrdersAmount,
219	GROUP_CONCAT(q.id) groupConcatIdsTotalOrders,
220	GROUP_CONCAT(CASE WHEN q.acceptance_date IS NOT NULL AND q.acceptance_date != '0000-00-00 00:00:00' THEN q.id END) groupConcatIdsOrdersAcceptance,
221	GROUP_CONCAT(CASE WHEN q.acceptance_date IS NOT NULL AND q.acceptance_date != '0000-00-00 00:00:00' THEN q.amount END) groupConcatIdsOrdersAcceptanceAmount
222	FROM tbl_quotations q
223	LEFT JOIN tbl_sources s
224	ON q.source_id = s.source_id
225	LEFT JOIN tbl_companies c
226	ON q.company_id = c.company_id
227	WHERE
228	q.for_add = 0
229	AND q.created_by = 'IA'
230	{$where}
231	GROUP BY c.region, s.source_id WITH ROLLUP
232	ORDER BY
233	(c.region IS NULL AND s.source_id IS NULL) ASC,
234	c.region IS NULL ASC,
235	c.region ASC,
236	(s.source_id IS NULL) DESC,
237	CASE WHEN s.source_id IS NOT NULL THEN COUNT(1) ELSE NULL END DESC
238	) q ORDER BY (q.region IS NULL AND q.source_id IS NULL) ASC, q.region ASC, (q.source_id IS NULL) DESC {$sortBy}";
239
240	$value = Cache::get(base64_encode($query));
241
242	if (! $value) {
243	$result = DB::select($query);
244
245	Cache::put(base64_encode($query), $result, 600);
246	} else {
247	$result = $value;
248	}
249
250	return response([
251	'message' => 'OK',
252	'data' => $result,
253	]);
254
255	} catch (\Exception $e) {
256	/** @disregard P1014 */
257	$e->exceptionCode = 'DIGITAL_CAMPAIGN_CHANNEL_LANDING_EXCEPTION';
258	report($e);
259
260	return response(['message' => 'KO', 'error' => $e->getMessage()]);
261	}
262	}
263
264	public function list_lead_status(Request $request)
265	{
266
267	try {
268
269	$data = $request->all();
270
271	$companyId = intval($data['company_id']);
272	$where = '';
273
274	if (isset($data['commercial']) && $data['commercial'] != null) {
275	$commercial = implode("','", $data['commercial']);
276	if (count($data['commercial']) > 0) {
277	$where .= " AND q.commercial IN ('{$commercial}') ";
278	}
279	}
280
281	if (isset($data['source_id']) && $data['source_id'] != null) {
282	$sourceId = implode("','", $data['source_id']);
283	if (count($data['source_id']) > 0) {
284	$where .= " AND q.source_id IN ('{$sourceId}') ";
285	}
286	}
287
288	if (isset($data['region']) && $data['region'] != null) {
289	$c = TblCompanies::whereIn('region', $data['region'])->pluck('company_id')->toArray();
290
291	if ($c) {
292	$companyIds = implode(',', $c);
293	$where .= " AND q.company_id IN ({$companyIds})";
294	}
295	} else {
296	if ($companyId != 0) {
297	$where .= " AND q.company_id = {$companyId} ";
298	} else {
299	$where .= " AND q.company_id IN ({$this->companyId}) ";
300	}
301	}
302
303	if ((isset($data['start_date']) && $data['start_date'] != null) && (isset($data['end_date']) && $data['end_date'] != null)) {
304	$where .= " AND q.created_at BETWEEN '{$data['start_date']}' AND '{$data['end_date']}' ";
305	}
306
307	$query = "SELECT
308	bs.name AS 'status',
309	s.name AS 'source',
310	grouped.groupConcatIds,
311	grouped.totalOrders,
312	CAST(grouped.totalOrders / (case when s.name is NOT null then status_subtotals.totalOrders else overall.totalOrders END) * 100 AS DOUBLE) averageTotalOrders,
313	grouped.totalAmount,
314	CAST(grouped.totalAmount / (CASE WHEN s.name IS NOT NULL THEN status_subtotals.totalAmount ELSE overall.totalAmount END) * 100 AS DOUBLE) averageTotalAmount
315	FROM (
316	SELECT
317	GROUP_CONCAT(q.id) groupConcatIds,
318	q.budget_status_id,
319	q.source_id,
320	q.issue_date,
321	COUNT(1) AS totalOrders,
322	SUM(q.amount) AS totalAmount
323	FROM tbl_quotations q
324	LEFT JOIN tbl_sources s ON q.source_id = s.source_id
325	WHERE
326	q.for_add = 0
327	AND (q.commercial IS NOT NULL AND q.commercial != '')
328	AND q.source_id > 0
329	AND q.budget_status_id > 0
330	AND q.budget_status_id != 18
331	AND s.digital_campaign_source > 0
332	{$where}
333	GROUP BY q.budget_status_id, q.source_id WITH ROLLUP
334	) AS grouped
335	LEFT JOIN tbl_budget_status bs ON grouped.budget_status_id = bs.budget_status_id
336	LEFT JOIN tbl_sources s ON grouped.source_id = s.source_id
337	LEFT JOIN (
338	SELECT
339	q.budget_status_id,
340	COUNT(1) AS totalOrders,
341	SUM(q.amount) AS totalAmount
342	FROM tbl_quotations q
343	LEFT JOIN tbl_sources s ON q.source_id = s.source_id
344	WHERE
345	q.for_add = 0
346	AND q.commercial IS NOT NULL AND q.commercial != ''
347	AND q.budget_status_id > 0
348	AND q.budget_status_id != 18
349	AND s.digital_campaign_source > 0
350	{$where}
351	GROUP BY q.budget_status_id
352	) AS status_subtotals ON grouped.budget_status_id = status_subtotals.budget_status_id
353	CROSS JOIN (
354	SELECT
355	COUNT(1) AS totalOrders,
356	SUM(q.amount) AS totalAmount
357	FROM tbl_quotations q
358	LEFT JOIN tbl_sources s ON q.source_id = s.source_id
359	WHERE
360	q.for_add = 0
361	AND (q.commercial IS NOT NULL AND q.commercial != '')
362	AND q.source_id > 0
363	AND q.budget_status_id > 0
364	AND q.budget_status_id != 18
365	AND s.digital_campaign_source > 0
366	{$where}
367	) AS overall
368	ORDER BY
369	CASE
370	WHEN grouped.budget_status_id IS NULL AND grouped.source_id IS NULL THEN 2
371	ELSE 1
372	END,
373	bs.priority ASC,
374	CASE
375	WHEN grouped.source_id IS NULL THEN 0
376	ELSE 1
377	END,
378	s.priority ASC";
379
380	$value = Cache::get(base64_encode($query));
381
382	if (! $value) {
383	$result = DB::select($query);
384
385	Cache::put(base64_encode($query), $result, 600);
386	} else {
387	$result = $value;
388	}
389
390	return response([
391	'message' => 'OK',
392	'data' => $result,
393	]);
394
395	} catch (\Exception $e) {
396	/** @disregard P1014 */
397	$e->exceptionCode = 'LIST_LEAD_STATUS_EXCEPTION';
398	report($e);
399
400	return response(['message' => 'KO', 'error' => $e->getMessage()]);
401	}
402	}
403
404	public function toCamelCase($string)
405	{
406	$cleaned = preg_replace('/[^a-zA-Z0-9]+/', ' ', $string);
407	$words = explode(' ', trim($cleaned));
408
409	$camelCased = '';
410	foreach ($words as $word) {
411	if ($word !== '') {
412	$camelCased .= ucfirst(strtolower($word));
413	}
414	}
415
416	return $camelCased;
417	}
418
419	public function list_time_between_status(Request $request)
420	{
421
422	try {
423
424	$data = $request->all();
425
426	$companyId = intval($data['company_id']);
427	$where = '';
428
429	if ($companyId != 0) {
430	$where .= " AND q.company_id = {$companyId} ";
431	} else {
432	$where .= " AND q.company_id IN ({$this->companyId}) ";
433	}
434
435	if (isset($data['commercial']) && $data['commercial'] != null) {
436	$commercial = implode("','", $data['commercial']);
437	if (count($data['commercial']) > 0) {
438	$where .= " AND q.commercial IN ('{$commercial}') ";
439	}
440	}
441
442	$averageDateCol = '';
443	$averageDate = 'q.created_at'; // Fallback to avoid undefined variable in SQL if conditional is unmet
444
445	if (isset($data['average_date']) && count($data['average_date']) == 2) {
446	foreach ($data['average_date'] as $k => $v) {
447	$data['average_date'][$k] = "q.{$v}";
448	}
449
450	$averageDate = implode(',', $data['average_date']);
451	}
452
453	$query = "SELECT
454	-- Less than 1 day
455	COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) < 1 THEN 1 END) AS totalRowsLt1,
456	SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) < 1 THEN amount ELSE 0 END) AS totalAmountLt1,
457	GROUP_CONCAT(CASE WHEN ABS(DATEDIFF({$averageDate})) < 1 THEN id END) AS groupConcatIdsLt1,
458
459	-- 1 to 3 days
460	COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) >= 1
461	AND ABS(DATEDIFF({$averageDate})) <= 3 THEN 1 END) AS totalRows1To3,
462	SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) >= 1
463	AND ABS(DATEDIFF({$averageDate})) <= 3 THEN amount ELSE 0 END) AS totalAmount1To3,
464	GROUP_CONCAT(CASE WHEN ABS(DATEDIFF({$averageDate})) >= 1 AND ABS(DATEDIFF({$averageDate})) <= 3 THEN id END) AS groupConcatIds1To3,
465
466	-- More than 3 to 7 days
467	COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 3
468	AND ABS(DATEDIFF({$averageDate})) <= 7 THEN 1 END) AS totalRows3To7,
469	SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) > 3
470	AND ABS(DATEDIFF({$averageDate})) <= 7 THEN amount ELSE 0 END) AS totalAmount3To7,
471	GROUP_CONCAT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 3 AND ABS(DATEDIFF({$averageDate})) <= 7 THEN id END) AS groupConcatIds3To7,
472
473	-- More than 7 to 15 days
474	COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 7
475	AND ABS(DATEDIFF({$averageDate})) <= 15 THEN 1 END) AS totalRows7To15,
476	SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) > 7
477	AND ABS(DATEDIFF({$averageDate})) <= 15 THEN amount ELSE 0 END) AS totalAmount7To15,
478	GROUP_CONCAT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 7 AND ABS(DATEDIFF({$averageDate})) <= 15 THEN id END) AS groupConcatIds7To15,
479
480	-- More than 15 to 30 days
481	COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 15
482	AND ABS(DATEDIFF({$averageDate})) <= 30 THEN 1 END) AS totalRows15To30,
483	SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) > 15
484	AND ABS(DATEDIFF({$averageDate})) <= 30 THEN amount ELSE 0 END) AS totalAmount15To30,
485	GROUP_CONCAT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 15 AND ABS(DATEDIFF({$averageDate})) <= 30 THEN id END) AS groupConcatIds15To30,
486
487	-- More than 30 days
488	COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 30 THEN 1 END) AS totalRowsGt30,
489	SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) > 30 THEN amount ELSE 0 END) AS totalAmountGt30,
490	GROUP_CONCAT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 30 THEN id END) AS groupConcatIdsGt30,
491
492
493
494	-- Less than 1 day Acceptance
495	COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) < 1 AND q.budget_status_id = 3 THEN 1 END) AS totalRowsLt1Acceptance,
496	SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) < 1 AND q.budget_status_id = 3 THEN amount ELSE 0 END) AS totalAmountLt1Acceptance,
497	GROUP_CONCAT(CASE WHEN ABS(DATEDIFF({$averageDate})) < 1 AND q.budget_status_id = 3 THEN id END) AS groupConcatIdsLt1Acceptance,
498
499	-- 1 to 3 days Acceptance
500	COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) >= 1
501	AND ABS(DATEDIFF({$averageDate})) <= 3 AND q.budget_status_id = 3 THEN 1 END) AS totalRows1To3Acceptance,
502	SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) >= 1
503	AND ABS(DATEDIFF({$averageDate})) <= 3 AND q.budget_status_id = 3 THEN amount ELSE 0 END) AS totalAmount1To3Acceptance,
504	GROUP_CONCAT(CASE WHEN ABS(DATEDIFF({$averageDate})) >= 1 AND ABS(DATEDIFF({$averageDate})) <= 3 AND q.budget_status_id = 3 THEN id END) AS groupConcatIds1To3Acceptance,
505
506	-- More than 3 to 7 days Acceptance
507	COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 3
508	AND ABS(DATEDIFF({$averageDate})) <= 7 AND q.budget_status_id = 3 THEN 1 END) AS totalRows3To7Acceptance,
509	SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) > 3
510	AND ABS(DATEDIFF({$averageDate})) <= 7 AND q.budget_status_id = 3 THEN amount ELSE 0 END) AS totalAmount3To7Acceptance,
511	GROUP_CONCAT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 3 AND ABS(DATEDIFF({$averageDate})) <= 7 AND q.budget_status_id = 3 THEN id END) AS groupConcatIds3To7Acceptance,
512
513	-- More than 7 to 15 days Acceptance
514	COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 7
515	AND ABS(DATEDIFF({$averageDate})) <= 15 AND q.budget_status_id = 3 THEN 1 END) AS totalRows7To15Acceptance,
516	SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) > 7
517	AND ABS(DATEDIFF({$averageDate})) <= 15 AND q.budget_status_id = 3 THEN amount ELSE 0 END) AS totalAmount7To15Acceptance,
518	GROUP_CONCAT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 7 AND ABS(DATEDIFF({$averageDate})) <= 15 AND q.budget_status_id = 3 THEN id END) AS groupConcatIds7To15Acceptance,
519
520	-- More than 15 to 30 days Acceptance
521	COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 15
522	AND ABS(DATEDIFF({$averageDate})) <= 30 AND q.budget_status_id = 3 THEN 1 END) AS totalRows15To30Acceptance,
523	SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) > 15
524	AND ABS(DATEDIFF({$averageDate})) <= 30 AND q.budget_status_id = 3 THEN amount ELSE 0 END) AS totalAmount15To30Acceptance,
525	GROUP_CONCAT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 15 AND ABS(DATEDIFF({$averageDate})) <= 30 AND q.budget_status_id = 3 THEN id END) AS groupConcatIds15To30Acceptance,
526
527	-- More than 30 days Acceptance
528	COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 30 AND q.budget_status_id = 3 THEN 1 END) AS totalRowsGt30Acceptance,
529	SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) > 30 AND q.budget_status_id = 3 THEN amount ELSE 0 END) AS totalAmountGt30Acceptance,
530	GROUP_CONCAT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 30 AND q.budget_status_id = 3 THEN id END) AS groupConcatIdsGt30Acceptance,
531
532
533
534	-- Less than 1 day Acceptance / Issue
535	COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) < 1 AND q.budget_status_id = 3 THEN 1 END) /
536	COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) < 1 THEN 1 END) * 100 AS totalRowsLt1AcceptanceIssue,
537	SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) < 1 AND q.budget_status_id = 3 THEN amount ELSE 0 END) /
538	SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) < 1 THEN amount ELSE 0 END) * 100 AS totalAmountLt1AcceptanceIssue,
539
540	-- 1 to 3 days Acceptance / Issue
541	COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) >= 1
542	AND ABS(DATEDIFF({$averageDate})) <= 3 AND q.budget_status_id = 3 THEN 1 END) /
543	COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) >= 1
544	AND ABS(DATEDIFF({$averageDate})) <= 3 THEN 1 END) * 100 AS totalRows1To3AcceptanceIssue,
545	SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) >= 1
546	AND ABS(DATEDIFF({$averageDate})) <= 3 AND q.budget_status_id = 3 THEN amount ELSE 0 END) /
547	SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) >= 1
548	AND ABS(DATEDIFF({$averageDate})) <= 3 THEN amount ELSE 0 END) * 100 AS totalAmount1To3AcceptanceIssue,
549
550	-- More than 3 to 7 days Acceptance / Issue
551	COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 3
552	AND ABS(DATEDIFF({$averageDate})) <= 7 AND q.budget_status_id = 3 THEN 1 END) /
553	COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 3
554	AND ABS(DATEDIFF({$averageDate})) <= 7 THEN 1 END) * 100 AS totalRows3To7AcceptanceIssue,
555	SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) > 3
556	AND ABS(DATEDIFF({$averageDate})) <= 7 AND q.budget_status_id = 3 THEN amount ELSE 0 END) /
557	SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) > 3
558	AND ABS(DATEDIFF({$averageDate})) <= 7 THEN amount ELSE 0 END) * 100 AS totalAmount3To7AcceptanceIssue,
559
560	-- More than 7 to 15 days Acceptance / Issue
561	COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 7
562	AND ABS(DATEDIFF({$averageDate})) <= 15 AND q.budget_status_id = 3 THEN 1 END) /
563	COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 7
564	AND ABS(DATEDIFF({$averageDate})) <= 15 THEN 1 END) * 100 AS totalRows7To15AcceptanceIssue,
565	SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) > 7
566	AND ABS(DATEDIFF({$averageDate})) <= 15 AND q.budget_status_id = 3 THEN amount ELSE 0 END) /
567	SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) > 7
568	AND ABS(DATEDIFF({$averageDate})) <= 15 THEN amount ELSE 0 END) * 100 AS totalAmount7To15AcceptanceIssue,
569
570
571	-- More than 15 to 30 days Acceptance / Issue
572	COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 15
573	AND ABS(DATEDIFF({$averageDate})) <= 30 AND q.budget_status_id = 3 THEN 1 END) /
574	COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 15
575	AND ABS(DATEDIFF({$averageDate})) <= 30 THEN 1 END) * 100 AS totalRows15To30AcceptanceIssue,
576	SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) > 15
577	AND ABS(DATEDIFF({$averageDate})) <= 30 AND q.budget_status_id = 3 THEN amount ELSE 0 END) /
578	SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) > 15
579	AND ABS(DATEDIFF({$averageDate})) <= 30 THEN amount ELSE 0 END) * 100 AS totalAmount15To30AcceptanceIssue,
580
581	-- More than 30 days Acceptance / Issue
582	COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 30 AND q.budget_status_id = 3 THEN 1 END) /
583	COUNT(CASE WHEN ABS(DATEDIFF({$averageDate})) > 30 THEN 1 END) * 100 AS totalRowsGt30AcceptanceIssueIssue,
584	SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) > 30 AND q.budget_status_id = 3 THEN amount ELSE 0 END) /
585	SUM(CASE WHEN ABS(DATEDIFF({$averageDate})) > 30 THEN amount ELSE 0 END) * 100 AS totalAmountGt30AcceptanceIssue
586	FROM tbl_quotations q
587	LEFT JOIN tbl_sources s ON q.source_id = s.source_id
588	WHERE
589	q.for_add = 0
590	AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
591	AND (q.commercial IS NOT NULL AND q.commercial != '')
592	AND q.budget_type_id != 7
593	AND q.budget_type_id IS NOT NULL
594	AND q.source_id > 0
595	AND q.budget_status_id > 0
596	AND q.budget_status_id != 18
597	AND s.digital_campaign_source > 0
598	{$where}";
599
600	$value = Cache::get(base64_encode($query));
601
602	if (! $value) {
603	$result = DB::select($query);
604
605	Cache::put(base64_encode($query), $result, 600);
606	} else {
607	$result = $value;
608	}
609
610	return response([
611	'message' => 'OK',
612	'data' => $result,
613	]);
614
615	} catch (\Exception $e) {
616	/** @disregard P1014 */
617	$e->exceptionCode = 'LIST_TIME_BETWEEN_STATUS_EXCEPTION';
618	report($e);
619
620	return response(['message' => 'KO', 'error' => $e->getMessage()]);
621	}
622	}
623
624	public function list_g3w_order_status(Request $request)
625	{
626
627	try {
628
629	$data = $request->all();
630
631	$companyId = intval($data['company_id']);
632	$where = '';
633
634	if ($companyId != 0) {
635	$where .= " AND q.company_id = {$companyId} ";
636	} else {
637	$where .= " AND q.company_id IN ({$this->companyId}) ";
638	}
639
640	if (isset($data['commercial']) && $data['commercial'] != null) {
641	$commercial = implode("','", $data['commercial']);
642	if (count($data['commercial']) > 0) {
643	$where .= " AND q.commercial IN ('{$commercial}') ";
644	}
645	}
646
647	$dataToDisplay = isset($data['data_to_display']) ? $data['data_to_display'] : 1;
648
649	$col = '1';
650
651	if ($dataToDisplay == 2) {
652	$col = 'q.amount';
653	}
654
655	$columns = '';
656	$groupedColumns = '';
657
658	$statusByG3w = [];
659
660	$workStatusAndAttributes = ['Finalizado', 'Facturado', 'Cerrado', 'Verificado'];
661
662	if (isset($data['status_by_g3w']) && count($data['status_by_g3w']) > 0) {
663	$statusByG3w = $data['status_by_g3w'];
664
665	foreach ($statusByG3w as $b) {
666	$z = $this->toCamelCase($b);
667
668	if (! in_array($b, $workStatusAndAttributes)) {
669	$columns .= " COALESCE(SUM(CASE WHEN q.status_by_g3w = '{$b}' THEN {$col} END), 0) AS 'total{$z}', ";
670	$columns .= " GROUP_CONCAT(CASE WHEN q.status_by_g3w = '{$b}' THEN q.id END) AS 'groupConcatIds{$z}', ";
671	} else {
672	if ($b == 'Finalizado') {
673	$columns .= " COALESCE(SUM(CASE WHEN bw.work_status = 'Finalizado' THEN {$col} END), 0) AS 'total{$z}', ";
674	$columns .= " GROUP_CONCAT(CASE WHEN bw.work_status = 'Finalizado' THEN q.id END) AS 'groupConcatIds{$z}', ";
675	}
676
677	if ($b == 'Facturado') {
678	$columns .= " COALESCE(SUM(CASE WHEN bw.work_status = 'Facturado' THEN {$col} END), 0) AS 'total{$z}', ";
679	$columns .= " GROUP_CONCAT(CASE WHEN bw.work_status = 'Facturado' THEN q.id END) AS 'groupConcatIds{$z}', ";
680	}
681
682	if ($b == 'Cerrado') {
683	$columns .= " COALESCE(SUM(CASE WHEN bw.closed_attribute = '1' THEN {$col} END), 0) AS 'total{$z}', ";
684	$columns .= " GROUP_CONCAT(CASE WHEN bw.closed_attribute = '1' THEN q.id END) AS 'groupConcatIds{$z}', ";
685	}
686
687	if ($b == 'Verificado') {
688	$columns .= " COALESCE(SUM(CASE WHEN bw.verified_attribute = '1' THEN {$col} END), 0) AS 'total{$z}', ";
689	$columns .= " GROUP_CONCAT(CASE WHEN bw.verified_attribute = '1' THEN q.id END) AS 'groupConcatIds{$z}', ";
690	}
691	}
692
693	if ($dataToDisplay == 3) {
694	$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}', ";
695	} else {
696	$groupedColumns .= " grouped.total{$z}, ";
697	}
698
699	$groupedColumns .= " grouped.groupConcatIds{$z}, ";
700	}
701	}
702
703	$query = "SELECT
704	c.name AS company_name,
705	s.name AS source,
706	{$groupedColumns}
707	grouped.company_id
708	FROM (
709	SELECT
710	q.company_id,
711	{$columns}
712	q.source_id
713	FROM tbl_quotations q
714	LEFT JOIN tbl_sources s ON q.source_id = s.source_id
715	INNER JOIN tbl_box_work_g3w_mapping bw ON q.box_work_g3w = bw.box_work_g3w
716	WHERE
717	q.for_add = 0
718	AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
719	AND (q.commercial IS NOT NULL AND q.commercial != '')
720	AND q.budget_type_id != 7
721	AND q.budget_type_id IS NOT NULL
722	AND q.source_id > 0
723	AND q.budget_status_id > 0
724	AND q.budget_status_id != 18
725	AND s.digital_campaign_source > 0
726	{$where}
727	GROUP BY q.company_id, q.source_id WITH ROLLUP
728	) AS grouped
729	LEFT JOIN tbl_sources s ON grouped.source_id = s.source_id
730	LEFT JOIN tbl_companies c ON grouped.company_id = c.company_id
731	LEFT JOIN (
732	SELECT
733	q.company_id,
734	{$columns}
735	q.source_id
736	FROM tbl_quotations q
737	LEFT JOIN tbl_sources s ON q.source_id = s.source_id
738	INNER JOIN tbl_box_work_g3w_mapping bw ON q.box_work_g3w = bw.box_work_g3w
739	WHERE
740	q.for_add = 0
741	AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
742	AND (q.commercial IS NOT NULL AND q.commercial != '')
743	AND q.budget_type_id != 7
744	AND q.budget_type_id IS NOT NULL
745	AND q.source_id > 0
746	AND q.budget_status_id > 0
747	AND q.budget_status_id != 18
748	AND s.digital_campaign_source > 0
749	{$where}
750	GROUP BY q.company_id
751	) AS subtotals ON grouped.company_id = subtotals.company_id
752	CROSS JOIN (
753	SELECT
754	q.company_id,
755	{$columns}
756	q.source_id
757	FROM tbl_quotations q
758	LEFT JOIN tbl_sources s ON q.source_id = s.source_id
759	INNER JOIN tbl_box_work_g3w_mapping bw ON q.box_work_g3w = bw.box_work_g3w
760	WHERE
761	q.for_add = 0
762	AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
763	AND (q.commercial IS NOT NULL AND q.commercial != '')
764	AND q.budget_type_id != 7
765	AND q.budget_type_id IS NOT NULL
766	AND q.source_id > 0
767	AND q.budget_status_id > 0
768	AND q.budget_status_id != 18
769	AND s.digital_campaign_source > 0
770	{$where}
771	) overall
772	ORDER BY
773	CASE
774	WHEN grouped.company_id IS NULL AND grouped.source_id IS NULL THEN 2
775	ELSE 1
776	END,
777	c.name ASC,
778	CASE
779	WHEN grouped.source_id IS NULL THEN 0
780	ELSE 1
781	END,
782	s.priority ASC";
783
784	$value = Cache::get(base64_encode($query));
785
786	if (! $value) {
787	$result = DB::select($query);
788
789	Cache::put(base64_encode($query), $result, 600);
790	} else {
791	$result = $value;
792	}
793
794	$labelMap = [
795	'totalAceptado' => 'Aceptado',
796	'totalEnviado' => 'Enviado',
797	'totalAnulado' => 'Anulado',
798	'totalRechazado' => 'Rechazado',
799	'totalNuevo' => 'Nuevo',
800	'totalListoParaEnviar' => 'Listo para enviar',
801	'totalRechazadoAutomTicamente' => 'Rechazado - automáticamente',
802	'totalEnProceso' => 'En proceso',
803	'totalFinalizado' => 'Finalizado',
804	'totalFacturado' => 'Facturado',
805	'totalCerrado' => 'Cerrado',
806	'totalVerificado' => 'Verificado',
807	];
808
809	$headers = [];
810
811	foreach ($result as &$row) {
812	$rowArr = (array) $row;
813
814	if ($rowArr['company_name'] == null) {
815	$totals = [];
816	foreach ($rowArr as $key => $value) {
817	if (str_starts_with($key, 'total')) {
818	$groupKey = str_replace('total', 'groupConcatIds', $key);
819	$totals[] = [
820	'totalKey' => $key,
821	'totalValue' => (int) $value,
822	'groupKey' => $groupKey,
823	'groupValue' => $rowArr[$groupKey] ?? null,
824	];
825	}
826	}
827
828	usort($totals, fn ($a, $b) => $b['totalValue'] <=> $a['totalValue']);
829
830	if (empty($headers)) {
831	foreach ($totals as $t) {
832	$headers[] = $labelMap[$t['totalKey']] ?? $t['totalKey'];
833	}
834	}
835
836	$sortedRow = [
837	'company_name' => $rowArr['company_name'],
838	'source' => $rowArr['source'],
839	'company_id' => $rowArr['company_id'],
840	];
841
842	foreach ($totals as $t) {
843	$sortedRow[$t['totalKey']] = (string) $t['totalValue'];
844	$sortedRow[$t['groupKey']] = $t['groupValue'];
845	}
846
847	$row = (object) $sortedRow;
848	}
849	}
850	unset($row);
851
852	return response([
853	'message' => 'OK',
854	'data' => $result,
855	'headers' => $headers,
856	]);
857
858	} catch (\Exception $e) {
859	/** @disregard P1014 */
860	$e->exceptionCode = 'LIST_G3W_ORDER_STATUS_EXCEPTION';
861	report($e);
862
863	return response(['message' => 'KO', 'error' => $e->getMessage()]);
864	}
865
866	}
867
868	public function get_sources_digital_campaign_analytics()
869	{
870
871	try {
872
873	$sources = TblSources::where('digital_campaign_source', 1)->orderByRaw('ISNULL(priority), priority ASC')->get();
874
875	$query = "SELECT
876	bt.budget_type_id,
877	bt.name
878	FROM tbl_budget_types bt
879	LEFT JOIN tbl_budget_type_groups btg
880	ON bt.budget_type_group_id = btg.budget_type_group_id
881	WHERE bt.name != '' AND bt.budget_type_id != 7
882	ORDER BY ISNULL(bt.priority), bt.priority ASC";
883
884	$budgetTypes = DB::select($query);
885
886	$query = 'SELECT
887	DISTINCT q.status_by_g3w
888	FROM tbl_quotations q
889	WHERE q.status_by_g3w IS NOT NULL';
890
891	$g3wStatus = DB::select($query);
892
893	return response([
894	'message' => 'OK',
895	'sources' => $sources,
896	'budgetTypes' => $budgetTypes,
897	'g3wStatus' => $g3wStatus,
898	]);
899
900	} catch (\Exception $e) {
901	/** @disregard P1014 */
902	$e->exceptionCode = 'GET_SOURCES_DIGITAL_CAMPAIGN_ANALYTICS_EXCEPTION';
903	report($e);
904
905	return response(['message' => 'KO', 'error' => $e->getMessage()]);
906	}
907
908	}
909
910	public function update_final_summary(Request $request, $companyId)
911	{
912
913	try {
914
915	$data = $request->all();
916	$companyId = intval($companyId);
917
918	$data['updated_at'] = date('Y-m-d H:i:s');
919	TblFinalSummary::where('company_id', $companyId)->update($data);
920
921	return response([
922	'message' => 'OK',
923	]);
924
925	} catch (\Exception $e) {
926	/** @disregard P1014 */
927	$e->exceptionCode = 'UPDATE_FINAL_SUMMARY_EXCEPTION';
928	report($e);
929
930	return response(['message' => 'KO', 'error' => $e->getMessage()]);
931	}
932
933	}
934
935	public function get_final_summary(Request $request)
936	{
937
938	try {
939
940	$data = $request->all();
941
942	$companyId = intval($data['company_id']);
943	$where = '';
944
945	if ($companyId != 0) {
946	$where .= " AND q.company_id = {$companyId} ";
947	} else {
948	$where .= " AND q.company_id IN ({$this->companyId}) ";
949	}
950
951	$query = "SELECT
952	CAST(SUM(q.impressions_total) AS UNSIGNED) AS impressions_total,
953	CAST(SUM(q.impressions_mdm) AS UNSIGNED) AS impressions_mdm,
954	CAST(SUM(q.impressions_mdg) AS UNSIGNED) AS impressions_mdg,
955	CAST(SUM(q.clicks) AS UNSIGNED) AS clicks,
956	CAST(SUM(q.clicks_mdm) AS UNSIGNED) AS clicks_mdm,
957	CAST(SUM(q.clicks_mdg) AS UNSIGNED) AS clicks_mdg,
958	CAST(SUM(q.conversions) AS UNSIGNED) AS conversions,
959	CAST(SUM(q.conversions_mdm) AS UNSIGNED) AS conversions_mdm,
960	CAST(SUM(q.leads_total) AS UNSIGNED) AS leads_total,
961	CAST(SUM(q.leads_total_mdm) AS UNSIGNED) AS leads_total_mdm,
962	CAST(SUM(q.leads_total_mdg) AS UNSIGNED) AS leads_total_mdg,
963	CAST(SUM(q.calls) AS UNSIGNED) AS calls,
964	CAST(SUM(q.forms_and_emails) AS UNSIGNED) AS forms_and_emails,
965	CAST(SUM(q.whats_app) AS UNSIGNED) AS whats_app,
966	CAST(SUM(q.campaign_cost_meta) AS UNSIGNED) AS campaign_cost_meta,
967	CAST(SUM(q.campaign_cost_google) AS UNSIGNED) AS campaign_cost_google,
968	CAST(SUM(q.invested_p1) AS UNSIGNED) AS invested_p1,
969	CAST(SUM(q.invested_p2) AS UNSIGNED) AS invested_p2,
970	CAST(SUM(q.invested_p3) AS UNSIGNED) AS invested_p3,
971	CAST(SUM(q.avg_cost_per_lead_obj_v1) AS UNSIGNED) AS avg_cost_per_lead_obj_v1,
972	CAST(SUM(q.cac_per_budget_accepted_obj_v1) AS UNSIGNED) AS cac_per_budget_accepted_obj_v1,
973	CAST(SUM(q.cac_per_amount_accepted_obj_v1) AS UNSIGNED) AS cac_per_amount_accepted_obj_v1,
974	CAST(SUM(q.avg_cost_per_lead_obj_v2) AS UNSIGNED) AS avg_cost_per_lead_obj_v2,
975	CAST(SUM(q.cac_per_budget_accepted_obj_v2) AS UNSIGNED) AS cac_per_budget_accepted_obj_v2,
976	CAST(SUM(q.cac_per_amount_accepted_obj_v2) AS UNSIGNED) AS cac_per_amount_accepted_obj_v2,
977	CAST(SUM(q.g3w_total) AS UNSIGNED) AS g3w_total,
978	CAST(SUM(q.g3w_total_amount) AS UNSIGNED) AS g3w_total_amount,
979	q.created_at,
980	q.updated_at
981	FROM tbl_final_summary q
982	WHERE
983	q.company_id != 0
984	{$where}";
985
986	$result = DB::select($query);
987
988	if (isset($data['source_ids']) && $data['source_ids'] != null) {
989	$sourceIds = implode(',', $data['source_ids']);
990	if (count($data['source_ids']) > 0) {
991	$where .= " AND q.source_id IN ({$sourceIds}) ";
992	}
993	}
994
995	$query = "SELECT
996	COUNT(q.created_at) totalOrders,
997	SUM(q.amount) amount,
998	GROUP_CONCAT(q.id) groupConcatIds,
999	COUNT(CASE WHEN s.name LIKE '%MDM%' THEN 1 END) leadsMDM,
1000	COUNT(CASE WHEN s.name LIKE '%MDG%' THEN 1 END) leadsMDG
1001	FROM
1002	tbl_quotations q
1003	LEFT JOIN tbl_sources s
1004	ON q.source_id = s.source_id
1005	WHERE
1006	q.for_add = 0
1007	AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1008	AND (q.commercial IS NOT NULL AND q.commercial != '')
1009	AND q.budget_type_id != 7
1010	AND q.budget_type_id IS NOT NULL
1011	AND (s.name LIKE '%MDM%' OR s.name LIKE '%MDG%')
1012	{$where}";
1013
1014	$totalOrders = DB::select($query);
1015
1016	$query = "SELECT
1017	COUNT(q.issue_date) issuedOrders,
1018	SUM(q.amount) amount,
1019	GROUP_CONCAT(q.id) groupConcatIds,
1020	COUNT(CASE WHEN s.name LIKE '%MDM%' THEN 1 END) issuedMDM,
1021	COUNT(CASE WHEN s.name LIKE '%MDG%' THEN 1 END) issuedMDG,
1022	SUM(
1023	CASE
1024	WHEN DATEDIFF(q.issue_date, q.created_at) <= 3 THEN 1
1025	ELSE 0
1026	END
1027	) AS onTimeOrders,
1028	GROUP_CONCAT(
1029	CASE
1030	WHEN DATEDIFF(q.issue_date, q.created_at) <= 3 THEN q.id
1031	END
1032	) AS groupConcatIdsonTimeOrders,
1033	SUM(
1034	CASE
1035	WHEN DATEDIFF(q.issue_date, q.created_at) > 3 THEN 1
1036	ELSE 0
1037	END
1038	) AS delayedOrders,
1039	GROUP_CONCAT(
1040	CASE
1041	WHEN DATEDIFF(q.issue_date, q.created_at) > 3 THEN q.id
1042	END
1043	) AS groupConcatIdsdelayedOrders
1044	FROM
1045	tbl_quotations q
1046	LEFT JOIN tbl_sources s
1047	ON q.source_id = s.source_id
1048	WHERE
1049	q.issue_date IS NOT NULL
1050	AND q.for_add = 0
1051	AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1052	AND (q.commercial IS NOT NULL AND q.commercial != '')
1053	AND q.budget_type_id != 7
1054	AND q.budget_type_id IS NOT NULL
1055	AND (s.name LIKE '%MDM%' OR s.name LIKE '%MDG%')
1056	{$where}";
1057
1058	$issuedOrders = DB::select($query);
1059
1060	$query = "SELECT
1061	COUNT(q.acceptance_date) acceptanceOrders,
1062	SUM(q.amount) amount,
1063	GROUP_CONCAT(q.id) groupConcatIds,
1064	COUNT(CASE WHEN s.name LIKE '%MDM%' THEN 1 END) acceptanceMDM,
1065	COUNT(CASE WHEN s.name LIKE '%MDG%' THEN 1 END) acceptanceMDG,
1066	SUM(
1067	CASE
1068	WHEN DATEDIFF(q.issue_date, q.created_at) <= 3 THEN 1
1069	ELSE 0
1070	END
1071	) AS onTimeOrders,
1072	GROUP_CONCAT(
1073	CASE
1074	WHEN DATEDIFF(q.issue_date, q.created_at) <= 3 THEN q.id
1075	END
1076	) AS groupConcatIdsonTimeOrders,
1077	SUM(
1078	CASE
1079	WHEN DATEDIFF(q.issue_date, q.created_at) > 3 THEN 1
1080	ELSE 0
1081	END
1082	) AS delayedOrders,
1083	GROUP_CONCAT(
1084	CASE
1085	WHEN DATEDIFF(q.issue_date, q.created_at) > 3 THEN q.id
1086	END
1087	) AS groupConcatIdsdelayedOrders
1088	FROM
1089	tbl_quotations q
1090	LEFT JOIN tbl_sources s
1091	ON q.source_id = s.source_id
1092	WHERE
1093	q.acceptance_date IS NOT NULL
1094	AND q.for_add = 0
1095	AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1096	AND (q.commercial IS NOT NULL AND q.commercial != '')
1097	AND q.budget_type_id != 7
1098	AND q.budget_type_id IS NOT NULL
1099	AND (s.name LIKE '%MDM%' OR s.name LIKE '%MDG%')
1100	{$where}";
1101
1102	$acceptanceOrders = DB::select($query);
1103
1104	$allRegions = TblFinalSummary::where('company_id', 0)->first();
1105
1106	return response([
1107	'message' => 'OK',
1108	'data' => $result[0],
1109	'objectivesForAllRegions' => $allRegions,
1110	'acceptanceOrders' => $acceptanceOrders,
1111	'issuedOrders' => $issuedOrders,
1112	'totalOrders' => $totalOrders,
1113	]);
1114
1115	} catch (\Exception $e) {
1116	/** @disregard P1014 */
1117	$e->exceptionCode = 'GET_FINAL_SUMMARY_EXCEPTION';
1118	report($e);
1119
1120	return response(['message' => 'KO', 'error' => $e->getMessage()]);
1121	}
1122
1123	}
1124
1125	function get_main_kpis(Request $request){
1126
1127	try {
1128
1129	$data = $request->all();
1130
1131	$companyId = addslashes($data['company_id']);
1132	$where = "";
1133
1134
1135	foreach ($data as $key => $value) {
1136	if (is_array($value)) {
1137	if (!empty($value['start_date'])) {
1138	$startDates[] = $value['start_date'];
1139	}
1140	if (!empty($value['end_date'])) {
1141	$endDates[] = $value['end_date'];
1142	}
1143	}
1144	}
1145
1146	$minDate = !empty($startDates) ? min($startDates) : null;
1147	$maxDate = !empty($endDates) ? max($endDates) : null;
1148
1149	if($companyId != 0){
1150	$where .= " AND q.company_id = {$companyId} ";
1151	}else{
1152	$where .= " AND q.company_id IN ({$this->companyId}) ";
1153	}
1154
1155	$whereTotalAcceptance = "";
1156	$whereTotalIssue = "";
1157	$resultTotal = array();
1158
1159	if(isset($minDate) && $minDate != null){
1160	if(isset($maxDate) && $maxDate != null){
1161	$whereTotalAcceptance .= " AND q.request_date BETWEEN '{$minDate}' AND '{$maxDate}' ";
1162	$whereTotalIssue .= " AND q.request_date BETWEEN '{$minDate}' AND '{$maxDate}' ";
1163
1164	$query = "SELECT
1165	COUNT(CASE WHEN q.budget_status_id = 3 THEN 1 END) totalOrdersAceptance,
1166	COUNT(CASE WHEN q.budget_status_id = 2 THEN 1 END) acceptanceOrdersEnviado,
1167	GROUP_CONCAT(CASE WHEN q.budget_status_id = 2 THEN q.id END) groupconcatIdsAcceptanceOrdersEnviado,
1168	AVG(CASE WHEN q.budget_status_id = 3 THEN q.amount END) averageAcceptanceAmount,
1169	SUM(CASE WHEN q.budget_status_id = 3 THEN q.amount END) amount,
1170	GROUP_CONCAT(CASE WHEN q.budget_status_id = 3 THEN q.id END) groupConcatIds,
1171	GROUP_CONCAT(CASE WHEN bt.budget_type_group_id = 4 THEN q.id END) groupConcatIdsFacilities,
1172	SUM(CASE WHEN bt.budget_type_group_id = 4 THEN q.amount END) facilitiesTotalAmount,
1173	GROUP_CONCAT(CASE WHEN bt.budget_type_group_id = 8 THEN q.id END) groupConcatIdsNew,
1174	SUM(CASE WHEN bt.budget_type_group_id = 8 THEN q.amount END) newTotalAmount,
1175	GROUP_CONCAT(CASE WHEN bt.budget_type_group_id = 5 THEN q.id END) groupConcatIdsCorrectives,
1176	SUM(CASE WHEN bt.budget_type_group_id = 5 THEN q.amount END) correctivesTotalAmount,
1177	GROUP_CONCAT(CASE WHEN bt.budget_type_group_id = 3 THEN q.id END) groupConcatIdsPreventive,
1178	SUM(CASE WHEN bt.budget_type_group_id = 3 THEN q.amount END) preventiveTotalAmount,
1179
1180	COUNT(CASE WHEN q.budget_status_id = 2 AND bt.budget_type_group_id IN (3, 8) THEN 1 END) acceptanceOrdersEnviado38,
1181	GROUP_CONCAT(CASE WHEN q.budget_status_id = 2 AND bt.budget_type_group_id IN (3, 8) THEN q.id END) groupConcatIdsAcceptanceOrdersEnviado38,
1182	COUNT(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN 1 END) totalOrdersAceptance38,
1183	GROUP_CONCAT(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN q.id END) groupConcatIdsAcceptanceOrders38,
1184	AVG(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN q.amount END) averageAcceptanceAmount38,
1185	SUM(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN q.amount END) amount38
1186	FROM
1187	tbl_quotations q
1188	LEFT JOIN tbl_sources s
1189	ON q.source_id = s.source_id
1190	LEFT JOIN tbl_budget_types bt
1191	ON bt.budget_type_id = q.budget_type_id
1192	WHERE
1193	q.request_date IS NOT NULL
1194	AND q.for_add = 0
1195	AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1196	AND (q.commercial IS NOT NULL AND q.commercial != '')
1197	AND q.budget_type_id != 7
1198	AND q.budget_type_id IS NOT NULL
1199	AND (s.name LIKE '%MDM%' OR s.name LIKE '%MDG%')
1200	{$where}
1201	{$whereTotalAcceptance}";
1202
1203	$resultTotalAcceptance = DB::select($query);
1204
1205	$query = "SELECT
1206	SUM(CASE WHEN q.issue_date IS NOT NULL THEN q.amount END) amount,
1207	COUNT(CASE WHEN q.issue_date IS NOT NULL THEN 1 END) totalOrdersIssue,
1208	GROUP_CONCAT(CASE WHEN q.issue_date IS NOT NULL THEN q.id END) groupConcatIds,
1209	SUM(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN q.amount END) amount38,
1210	COUNT(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN 1 END) totalOrdersIssue38,
1211	GROUP_CONCAT(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN q.id END) groupConcatIds38
1212	FROM
1213	tbl_quotations q
1214	LEFT JOIN tbl_sources s
1215	ON q.source_id = s.source_id
1216	LEFT JOIN tbl_budget_types bt
1217	ON bt.budget_type_id = q.budget_type_id
1218	WHERE
1219	q.request_date IS NOT NULL
1220	AND q.for_add = 0
1221	AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1222	AND (q.commercial IS NOT NULL AND q.commercial != '')
1223	AND q.budget_type_id != 7
1224	AND q.budget_type_id IS NOT NULL
1225	AND (s.name LIKE '%MDM%' OR s.name LIKE '%MDG%')
1226	{$where}
1227	{$whereTotalIssue}";
1228
1229	$resultTotalIssue = DB::select($query);
1230
1231	$resultTotal = array(
1232	"result" => $resultTotalAcceptance[0],
1233	"otherStatus" => $resultTotalIssue[0],
1234	"groupConcatIds" => implode(",", array_merge(...array_map(fn($v) => explode(",", $v), [
1235	$resultTotalAcceptance[0]->groupConcatIdsFacilities,
1236	$resultTotalAcceptance[0]->groupConcatIdsNew,
1237	$resultTotalAcceptance[0]->groupConcatIdsCorrectives,
1238	$resultTotalAcceptance[0]->groupConcatIdsPreventive]))
1239	)
1240	);
1241	}
1242	}
1243
1244	$whereP1Acceptance = "";
1245	$whereP1Issue = "";
1246	$resultP1 = array();
1247
1248	if(isset($data['p1'])){
1249	$p1 = $data['p1'];
1250
1251	if(isset($p1['start_date']) && $p1['start_date'] != null){
1252	if(isset($p1['end_date']) && $p1['end_date'] != null){
1253	$whereP1Acceptance .= " AND q.request_date BETWEEN '{$p1['start_date']}' AND '{$p1['end_date']}' ";
1254	$whereP1Issue .= " AND q.request_date BETWEEN '{$p1['start_date']}' AND '{$p1['end_date']}' ";
1255
1256	$query = "SELECT
1257	COUNT(CASE WHEN q.budget_status_id = 3 THEN 1 END) totalOrdersAceptance,
1258	COUNT(CASE WHEN q.budget_status_id = 2 THEN 1 END) acceptanceOrdersEnviado,
1259	GROUP_CONCAT(CASE WHEN q.budget_status_id = 2 THEN q.id END) groupconcatIdsAcceptanceOrdersEnviado,
1260	AVG(CASE WHEN q.budget_status_id = 3 THEN q.amount END) averageAcceptanceAmount,
1261	SUM(CASE WHEN q.budget_status_id = 3 THEN q.amount END) amount,
1262	GROUP_CONCAT(CASE WHEN q.budget_status_id = 3 THEN q.id END) groupConcatIds,
1263	GROUP_CONCAT(CASE WHEN bt.budget_type_group_id = 4 THEN q.id END) groupConcatIdsFacilities,
1264	SUM(CASE WHEN bt.budget_type_group_id = 4 THEN q.amount END) facilitiesTotalAmount,
1265	GROUP_CONCAT(CASE WHEN bt.budget_type_group_id = 8 THEN q.id END) groupConcatIdsNew,
1266	SUM(CASE WHEN bt.budget_type_group_id = 8 THEN q.amount END) newTotalAmount,
1267	GROUP_CONCAT(CASE WHEN bt.budget_type_group_id = 5 THEN q.id END) groupConcatIdsCorrectives,
1268	SUM(CASE WHEN bt.budget_type_group_id = 5 THEN q.amount END) correctivesTotalAmount,
1269	GROUP_CONCAT(CASE WHEN bt.budget_type_group_id = 3 THEN q.id END) groupConcatIdsPreventive,
1270	SUM(CASE WHEN bt.budget_type_group_id = 3 THEN q.amount END) preventiveTotalAmount,
1271
1272	COUNT(CASE WHEN q.budget_status_id = 2 AND bt.budget_type_group_id IN (3, 8) THEN 1 END) acceptanceOrdersEnviado38,
1273	GROUP_CONCAT(CASE WHEN q.budget_status_id = 2 AND bt.budget_type_group_id IN (3, 8) THEN q.id END) groupConcatIdsAcceptanceOrdersEnviado38,
1274	COUNT(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN 1 END) totalOrdersAceptance38,
1275	GROUP_CONCAT(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN q.id END) groupConcatIdsAcceptanceOrders38,
1276	AVG(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN q.amount END) averageAcceptanceAmount38,
1277	SUM(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN q.amount END) amount38
1278	FROM
1279	tbl_quotations q
1280	LEFT JOIN tbl_sources s
1281	ON q.source_id = s.source_id
1282	LEFT JOIN tbl_budget_types bt
1283	ON bt.budget_type_id = q.budget_type_id
1284	WHERE
1285	q.request_date IS NOT NULL
1286	AND q.for_add = 0
1287	AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1288	AND (q.commercial IS NOT NULL AND q.commercial != '')
1289	AND q.budget_type_id != 7
1290	AND q.budget_type_id IS NOT NULL
1291	AND (s.name LIKE '%MDM%' OR s.name LIKE '%MDG%')
1292	{$where}
1293	{$whereP1Acceptance}";
1294
1295	$resultAcceptance = DB::select($query);
1296
1297	$query = "SELECT
1298	SUM(CASE WHEN q.issue_date IS NOT NULL THEN q.amount END) amount,
1299	COUNT(CASE WHEN q.issue_date IS NOT NULL THEN 1 END) totalOrdersIssue,
1300	GROUP_CONCAT(CASE WHEN q.issue_date IS NOT NULL THEN q.id END) groupConcatIds,
1301	SUM(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN q.amount END) amount38,
1302	COUNT(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN 1 END) totalOrdersIssue38,
1303	GROUP_CONCAT(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN q.id END) groupConcatIds38
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	{$whereP1Issue}";
1320
1321	$resultIssue = DB::select($query);
1322
1323	$resultP1 = array(
1324	"result" => $resultAcceptance[0],
1325	"otherStatus" => $resultIssue[0],
1326	"groupConcatIds" => implode(",", array_merge(...array_map(fn($v) => explode(",", $v), [
1327	$resultAcceptance[0]->groupConcatIdsFacilities,
1328	$resultAcceptance[0]->groupConcatIdsNew,
1329	$resultAcceptance[0]->groupConcatIdsCorrectives,
1330	$resultAcceptance[0]->groupConcatIdsPreventive]))
1331	)
1332	);
1333	}
1334	}
1335	}
1336
1337	$whereP2Acceptance = "";
1338	$whereP2Issue = "";
1339	$resultP2 = array();
1340
1341	if(isset($data['p2'])){
1342	$p2 = $data['p2'];
1343
1344	if(isset($p2['start_date']) && $p2['start_date'] != null){
1345	if(isset($p2['end_date']) && $p2['end_date'] != null){
1346	$whereP2Acceptance .= " AND q.request_date BETWEEN '{$p2['start_date']}' AND '{$p2['end_date']}' ";
1347	$whereP2Issue .= " AND q.request_date BETWEEN '{$p2['start_date']}' AND '{$p2['end_date']}' ";
1348
1349	$query = "SELECT
1350	COUNT(CASE WHEN q.budget_status_id = 3 THEN 1 END) totalOrdersAceptance,
1351	COUNT(CASE WHEN q.budget_status_id = 2 THEN 1 END) acceptanceOrdersEnviado,
1352	GROUP_CONCAT(CASE WHEN q.budget_status_id = 2 THEN q.id END) groupconcatIdsAcceptanceOrdersEnviado,
1353	AVG(CASE WHEN q.budget_status_id = 3 THEN q.amount END) averageAcceptanceAmount,
1354	SUM(CASE WHEN q.budget_status_id = 3 THEN q.amount END) amount,
1355	GROUP_CONCAT(CASE WHEN q.budget_status_id = 3 THEN q.id END) groupConcatIds,
1356	GROUP_CONCAT(CASE WHEN bt.budget_type_group_id = 4 THEN q.id END) groupConcatIdsFacilities,
1357	SUM(CASE WHEN bt.budget_type_group_id = 4 THEN q.amount END) facilitiesTotalAmount,
1358	GROUP_CONCAT(CASE WHEN bt.budget_type_group_id = 8 THEN q.id END) groupConcatIdsNew,
1359	SUM(CASE WHEN bt.budget_type_group_id = 8 THEN q.amount END) newTotalAmount,
1360	GROUP_CONCAT(CASE WHEN bt.budget_type_group_id = 5 THEN q.id END) groupConcatIdsCorrectives,
1361	SUM(CASE WHEN bt.budget_type_group_id = 5 THEN q.amount END) correctivesTotalAmount,
1362	GROUP_CONCAT(CASE WHEN bt.budget_type_group_id = 3 THEN q.id END) groupConcatIdsPreventive,
1363	SUM(CASE WHEN bt.budget_type_group_id = 3 THEN q.amount END) preventiveTotalAmount,
1364
1365	COUNT(CASE WHEN q.budget_status_id = 2 AND bt.budget_type_group_id IN (3, 8) THEN 1 END) acceptanceOrdersEnviado38,
1366	GROUP_CONCAT(CASE WHEN q.budget_status_id = 2 AND bt.budget_type_group_id IN (3, 8) THEN q.id END) groupConcatIdsAcceptanceOrdersEnviado38,
1367	COUNT(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN 1 END) totalOrdersAceptance38,
1368	GROUP_CONCAT(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN q.id END) groupConcatIdsAcceptanceOrders38,
1369	AVG(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN q.amount END) averageAcceptanceAmount38,
1370	SUM(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN q.amount END) amount38
1371	FROM
1372	tbl_quotations q
1373	LEFT JOIN tbl_sources s
1374	ON q.source_id = s.source_id
1375	LEFT JOIN tbl_budget_types bt
1376	ON bt.budget_type_id = q.budget_type_id
1377	WHERE
1378	q.acceptance_date IS NOT NULL
1379	AND q.for_add = 0
1380	AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1381	AND (q.commercial IS NOT NULL AND q.commercial != '')
1382	AND q.budget_type_id != 7
1383	AND q.budget_type_id IS NOT NULL
1384	AND (s.name LIKE '%MDM%' OR s.name LIKE '%MDG%')
1385	{$where}
1386	{$whereP2Acceptance}";
1387
1388	$resultAcceptance = DB::select($query);
1389
1390	$query = "SELECT
1391	SUM(CASE WHEN q.issue_date IS NOT NULL THEN q.amount END) amount,
1392	COUNT(CASE WHEN q.issue_date IS NOT NULL THEN 1 END) totalOrdersIssue,
1393	GROUP_CONCAT(CASE WHEN q.issue_date IS NOT NULL THEN q.id END) groupConcatIds,
1394	SUM(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN q.amount END) amount38,
1395	COUNT(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN 1 END) totalOrdersIssue38,
1396	GROUP_CONCAT(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN q.id END) groupConcatIds38
1397	FROM
1398	tbl_quotations q
1399	LEFT JOIN tbl_sources s
1400	ON q.source_id = s.source_id
1401	LEFT JOIN tbl_budget_types bt
1402	ON bt.budget_type_id = q.budget_type_id
1403	WHERE
1404	q.issue_date IS NOT NULL
1405	AND q.for_add = 0
1406	AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1407	AND (q.commercial IS NOT NULL AND q.commercial != '')
1408	AND q.budget_type_id != 7
1409	AND q.budget_type_id IS NOT NULL
1410	AND (s.name LIKE '%MDM%' OR s.name LIKE '%MDG%')
1411	{$where}
1412	{$whereP2Issue}";
1413
1414	$resultIssue = DB::select($query);
1415
1416	$resultP2 = array(
1417	"result" => $resultAcceptance[0],
1418	"otherStatus" => $resultIssue[0],
1419	"groupConcatIds" => implode(",", array_merge(...array_map(fn($v) => explode(",", $v), [
1420	$resultAcceptance[0]->groupConcatIdsFacilities,
1421	$resultAcceptance[0]->groupConcatIdsNew,
1422	$resultAcceptance[0]->groupConcatIdsCorrectives,
1423	$resultAcceptance[0]->groupConcatIdsPreventive]))
1424	)
1425	);
1426	}
1427	}
1428	}
1429
1430
1431	$whereP3Acceptance = "";
1432	$whereP3Issue = "";
1433	$resultP3 = array();
1434
1435	if(isset($data['p3'])){
1436	$p3 = $data['p3'];
1437
1438	if(isset($p3['start_date']) && $p3['start_date'] != null){
1439	if(isset($p3['end_date']) && $p3['end_date'] != null){
1440	$whereP3Acceptance .= " AND q.request_date BETWEEN '{$p3['start_date']}' AND '{$p3['end_date']}' ";
1441	$whereP3Issue .= " AND q.request_date BETWEEN '{$p3['start_date']}' AND '{$p3['end_date']}' ";
1442
1443	$query = "SELECT
1444	COUNT(CASE WHEN q.budget_status_id = 3 THEN 1 END) totalOrdersAceptance,
1445	COUNT(CASE WHEN q.budget_status_id = 2 THEN 1 END) acceptanceOrdersEnviado,
1446	GROUP_CONCAT(CASE WHEN q.budget_status_id = 2 THEN q.id END) groupconcatIdsAcceptanceOrdersEnviado,
1447	AVG(CASE WHEN q.budget_status_id = 3 THEN q.amount END) averageAcceptanceAmount,
1448	SUM(CASE WHEN q.budget_status_id = 3 THEN q.amount END) amount,
1449	GROUP_CONCAT(CASE WHEN q.budget_status_id = 3 THEN q.id END) groupConcatIds,
1450	GROUP_CONCAT(CASE WHEN bt.budget_type_group_id = 4 THEN q.id END) groupConcatIdsFacilities,
1451	SUM(CASE WHEN bt.budget_type_group_id = 4 THEN q.amount END) facilitiesTotalAmount,
1452	GROUP_CONCAT(CASE WHEN bt.budget_type_group_id = 8 THEN q.id END) groupConcatIdsNew,
1453	SUM(CASE WHEN bt.budget_type_group_id = 8 THEN q.amount END) newTotalAmount,
1454	GROUP_CONCAT(CASE WHEN bt.budget_type_group_id = 5 THEN q.id END) groupConcatIdsCorrectives,
1455	SUM(CASE WHEN bt.budget_type_group_id = 5 THEN q.amount END) correctivesTotalAmount,
1456	GROUP_CONCAT(CASE WHEN bt.budget_type_group_id = 3 THEN q.id END) groupConcatIdsPreventive,
1457	SUM(CASE WHEN bt.budget_type_group_id = 3 THEN q.amount END) preventiveTotalAmount,
1458
1459	COUNT(CASE WHEN q.budget_status_id = 2 AND bt.budget_type_group_id IN (3, 8) THEN 1 END) acceptanceOrdersEnviado38,
1460	GROUP_CONCAT(CASE WHEN q.budget_status_id = 2 AND bt.budget_type_group_id IN (3, 8) THEN q.id END) groupConcatIdsAcceptanceOrdersEnviado38,
1461	COUNT(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN 1 END) totalOrdersAceptance38,
1462	GROUP_CONCAT(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN q.id END) groupConcatIdsAcceptanceOrders38,
1463	AVG(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN q.amount END) averageAcceptanceAmount38,
1464	SUM(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN q.amount END) amount38
1465	FROM
1466	tbl_quotations q
1467	LEFT JOIN tbl_sources s
1468	ON q.source_id = s.source_id
1469	LEFT JOIN tbl_budget_types bt
1470	ON bt.budget_type_id = q.budget_type_id
1471	WHERE
1472	q.acceptance_date IS NOT NULL
1473	AND q.for_add = 0
1474	AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1475	AND (q.commercial IS NOT NULL AND q.commercial != '')
1476	AND q.budget_type_id != 7
1477	AND q.budget_type_id IS NOT NULL
1478	AND (s.name LIKE '%MDM%' OR s.name LIKE '%MDG%')
1479	{$where}
1480	{$whereP3Acceptance}";
1481
1482	$resultAcceptance = DB::select($query);
1483
1484	$query = "SELECT
1485	SUM(CASE WHEN q.issue_date IS NOT NULL THEN q.amount END) amount,
1486	COUNT(CASE WHEN q.issue_date IS NOT NULL THEN 1 END) totalOrdersIssue,
1487	GROUP_CONCAT(CASE WHEN q.issue_date IS NOT NULL THEN q.id END) groupConcatIds,
1488	SUM(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN q.amount END) amount38,
1489	COUNT(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN 1 END) totalOrdersIssue38,
1490	GROUP_CONCAT(CASE WHEN bt.budget_type_group_id IN (3, 8) THEN q.id END) groupConcatIds38
1491	FROM
1492	tbl_quotations q
1493	LEFT JOIN tbl_sources s
1494	ON q.source_id = s.source_id
1495	LEFT JOIN tbl_budget_types bt
1496	ON bt.budget_type_id = q.budget_type_id
1497	WHERE
1498	q.issue_date IS NOT NULL
1499	AND q.for_add = 0
1500	AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1501	AND (q.commercial IS NOT NULL AND q.commercial != '')
1502	AND q.budget_type_id != 7
1503	AND q.budget_type_id IS NOT NULL
1504	AND (s.name LIKE '%MDM%' OR s.name LIKE '%MDG%')
1505	{$where}
1506	{$whereP3Issue}";
1507
1508	$resultIssue = DB::select($query);
1509
1510	$resultP3 = array(
1511	"result" => $resultAcceptance[0],
1512	"otherStatus" => $resultIssue[0],
1513	"groupConcatIds" => implode(",", array_merge(...array_map(fn($v) => explode(",", $v), [
1514	$resultAcceptance[0]->groupConcatIdsFacilities,
1515	$resultAcceptance[0]->groupConcatIdsNew,
1516	$resultAcceptance[0]->groupConcatIdsCorrectives,
1517	$resultAcceptance[0]->groupConcatIdsPreventive]))
1518	)
1519	);
1520	}
1521	}
1522	}
1523
1524	// $bigQuery = new BigQueryService();
1525
1526	return response([
1527	'message' => 'OK',
1528	"totals" => $resultTotal,
1529	'data' => array(
1530	'p1' => $resultP1,
1531	'p2' => $resultP2,
1532	'p3' => $resultP3
1533	),
1534	]);
1535
1536
1537	} catch (\Exception $e) {
1538	/** @disregard P1014 */
1539	$e->exceptionCode = 'GET_MAIN_KPIS_EXCEPTION';
1540	report($e);
1541	return response(['message' => 'KO', 'error' => $e->getMessage()]);
1542	}
1543
1544	}
1545
1546	public function list_type_of_order(Request $request)
1547	{
1548
1549	try {
1550
1551	$data = $request->all();
1552	$companyId = intval($data['company_id']);
1553	$where = '';
1554
1555	if ($companyId != 0) {
1556	$where .= " AND q.company_id = {$companyId} ";
1557	} else {
1558	$where .= " AND q.company_id IN ({$this->companyId}) ";
1559	}
1560
1561	if (isset($data['commercial']) && $data['commercial'] != null) {
1562	$where .= " AND q.commercial = '{$data['commercial']}'";
1563	}
1564
1565	if (isset($data['budget_status_id']) && count($data['budget_status_id']) > 0) {
1566	$budgetStatusIds = implode(',', $data['budget_status_id']);
1567	$where .= " AND q.budget_status_id IN ({$budgetStatusIds})";
1568	}
1569
1570	if ((isset($data['start_date']) && $data['start_date'] != null) && (isset($data['end_date']) && $data['end_date'] != null)) {
1571	$where .= " AND q.created_at BETWEEN '{$data['start_date']}' AND '{$data['end_date']}' ";
1572	}
1573
1574	$col = '1';
1575
1576	if (isset($data['data_to_display']) && $data['data_to_display'] != null) {
1577	if ($data['data_to_display'] == 1) {
1578	$col = '1';
1579	}
1580
1581	if ($data['data_to_display'] == 2) {
1582	$col = 'q.amount';
1583	}
1584	}
1585
1586	$totalCols = '';
1587
1588	$budgetTypes = TblBudgetTypes::orderByRaw('ISNULL(priority), priority ASC')->get();
1589	$cols = '';
1590	foreach ($budgetTypes as $item) {
1591	$item->name = preg_replace('/\s+/', ' ', $item->name);
1592	$ranAlias = substr(bin2hex(random_bytes(6)), 0, 6);
1593	if ($item->name == '' \|\| $item->name == null) {
1594	$cols .= ",COALESCE(SUM(CASE WHEN bt.name IS NULL THEN {$col} ELSE 0 END), 0) AS 'Otros'";
1595	$totalCols .= ",totals.`Otros` AS '{$ranAlias}'";
1596	} else {
1597	$cols .= ",COALESCE(SUM(CASE WHEN bt.name = '{$item->name}' THEN {$col} ELSE 0 END), 0) AS '{$item->name}'";
1598	$totalCols .= ",totals.`{$item->name}` AS '{$ranAlias}'";
1599	}
1600	}
1601
1602	$budgetTypeGroups = TblBudgetTypeGroups::orderByRaw('ISNULL(priority), priority ASC')->get();
1603
1604	$colsGroups = '';
1605	$totalColGroups = '';
1606	$totalColIndex = [
1607	20 => 59,
1608	21 => 43,
1609	22 => 44,
1610	23 => 45,
1611	9 => 37,
1612	25 => 47,
1613	26 => 48,
1614	27 => 49,
1615	11 => 38,
1616	29 => 51,
1617	30 => 52,
1618	13 => 39,
1619	28 => 50,
1620	15 => 40,
1621	24 => 46,
1622	31 => 53,
1623	17 => 41,
1624	32 => 54,
1625	33 => 55,
1626	34 => 56,
1627	35 => 57,
1628	36 => 58,
1629	19 => 42,
1630	];
1631
1632	if (@$data['data_to_display'] != 4 && @$data['data_to_display'] != 3) {
1633	foreach ($budgetTypeGroups as $item) {
1634	$budgetTypeGroupName = str_replace(' ', '', $item->name).$item->budget_type_group_id;
1635	$ranAlias = substr(bin2hex(random_bytes(6)), 0, 6);
1636	$totalColGroups .= ',totals.'.preg_replace('/\s+/', ' ', $budgetTypeGroupName)." AS '{$ranAlias}'";
1637	$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}'";
1638	$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}'";
1639	}
1640
1641	$colsGroups .= ",COALESCE(SUM(CASE WHEN (bt.budget_type_group_id IS NOT NULL OR bt.name IS NULL) THEN {$col} END), 0) AS 'total'";
1642	$ranAlias = substr(bin2hex(random_bytes(6)), 0, 6);
1643	$totalCols .= ",totals.`total` AS '{$ranAlias}'";
1644
1645	$col = $colsGroups.$cols;
1646	}
1647
1648	if (@$data['data_to_display'] == 4) {
1649
1650	foreach ($budgetTypeGroups as $item) {
1651	$budgetTypeGroupName = str_replace(' ', '', $item->name).$item->budget_type_group_id;
1652	$ranAlias = substr(bin2hex(random_bytes(6)), 0, 6);
1653	$totalColGroups .= ',totals.'.preg_replace('/\s+/', ' ', $budgetTypeGroupName)." AS '{$ranAlias}'";
1654	$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}'";
1655	$colsGroups .= ",COALESCE(
1656	SUM(CASE WHEN (bt.budget_type_group_id = {$item->budget_type_group_id} OR bt.name IS NULL) THEN {$col} END) /
1657	SUM(CASE WHEN (bt.budget_type_group_id IS NOT NULL OR bt.name IS NULL) THEN {$col} END)
1658	, 0) AS '{$budgetTypeGroupName}'";
1659	}
1660
1661	$colsGroups .= ",COALESCE(SUM(CASE WHEN (bt.budget_type_group_id IS NOT NULL OR bt.name IS NULL) THEN {$col} END), 0) AS 'total'";
1662	$ranAlias = substr(bin2hex(random_bytes(6)), 0, 6);
1663	$totalCols .= ",totals.`total` AS '{$ranAlias}'";
1664
1665	$col = $colsGroups.$cols;
1666	}
1667
1668	if (@$data['data_to_display'] == 3) {
1669
1670	$cols = '';
1671	foreach ($budgetTypes as $item) {
1672	$item->name = preg_replace('/\s+/', ' ', $item->name);
1673	if ($item->name == '' \|\| $item->name == null) {
1674	$ranAlias = substr(bin2hex(random_bytes(6)), 0, 6);
1675	$totalCols .= ",totals.`Otros` AS '{$ranAlias}'";
1676	$cols .= ",COALESCE(
1677	SUM(CASE WHEN bt.name IS NULL THEN q.amount ELSE 0 END) /
1678	SUM(CASE WHEN bt.name IS NULL THEN 1 ELSE 0 END) * 100 , 0
1679	) AS 'Otros'";
1680	} else {
1681	$ranAlias = substr(bin2hex(random_bytes(6)), 0, 6);
1682	$totalCols .= ",totals.`{$item->name}` AS '{$ranAlias}'";
1683	$cols .= ",COALESCE(
1684	SUM(CASE WHEN bt.name = '{$item->name}' THEN q.amount ELSE 0 END) /
1685	SUM(CASE WHEN bt.name = '{$item->name}' THEN 1 ELSE 0 END), 0
1686	) AS '{$item->name}'";
1687	}
1688	}
1689
1690	$ranAlias = substr(bin2hex(random_bytes(6)), 0, 6);
1691	$totalColGroups .= ",totals.Otros AS '{$ranAlias}'";
1692	foreach ($budgetTypeGroups as $item) {
1693	$budgetTypeGroupName = str_replace(' ', '', $item->name).$item->budget_type_group_id;
1694	$ranAlias = substr(bin2hex(random_bytes(6)), 0, 6);
1695	$totalColGroups .= ',totals.'.preg_replace('/\s+/', ' ', $budgetTypeGroupName)." AS '{$ranAlias}'";
1696	$colsGroups .= ",GROUP_CONCAT(CASE WHEN (bt.budget_type_group_id = {$item->budget_type_group_id} OR bt.name IS NULL) THEN q.id END) AS 'groupConcatIds{$budgetTypeGroupName}'";
1697	$colsGroups .= ",COALESCE(
1698	(SUM(CASE WHEN (bt.budget_type_group_id = {$item->budget_type_group_id} OR bt.name IS NULL) THEN q.amount END)) /
1699	(SUM(CASE WHEN (bt.budget_type_group_id = {$item->budget_type_group_id} OR bt.name IS NULL) THEN 1 END))
1700	, 0) '{$budgetTypeGroupName}'";
1701	}
1702
1703	$colsGroups .= ",COALESCE(
1704	(SUM(CASE WHEN (bt.budget_type_group_id IS NOT NULL OR bt.name IS NULL) THEN q.amount END)) /
1705	(SUM(CASE WHEN (bt.budget_type_group_id IS NOT NULL OR bt.name IS NULL) THEN 1 END))
1706	, 0) 'total'";
1707
1708	$ranAlias = substr(bin2hex(random_bytes(6)), 0, 6);
1709	$totalColGroups .= ",totals.`total` AS '{$ranAlias}'";
1710	$col = $colsGroups.$cols;
1711
1712	}
1713
1714	$totalCols = $totalColGroups.$totalCols;
1715
1716	if (isset($data['budget_type_id']) && count($data['budget_type_id']) > 0) {
1717	$budgetTypeIds = implode(',', $data['budget_type_id']);
1718	$where .= " AND q.budget_type_id IN ({$budgetTypeIds})";
1719	}
1720
1721	$sortByFirst = '';
1722	$sortBySecond = '';
1723
1724	if (isset($data['sort_by']) && $data['sort_by'] != null) {
1725	if (isset($data['column']) && $data['column'] != null) {
1726	$orderCol = $totalColIndex[$data['column']];
1727	$sortByFirst = "{$orderCol} {$data['sort_by']},";
1728	$sortBySecond = "{$data['column']} {$data['sort_by']},";
1729	}
1730	}
1731
1732	$query = "SELECT
1733	s.name AS 'source',
1734	bs.name AS 'status',
1735	q.source_id,
1736	q.budget_status_id,
1737	GROUP_CONCAT(q.id) groupConcatIds,
1738	COUNT(1) AS totalOrders,
1739	SUM(q.amount) AS totalAmount
1740	{$col}
1741	{$totalCols}
1742	FROM
1743	tbl_quotations q
1744	LEFT JOIN tbl_sources s ON s.source_id = q.source_id
1745	LEFT JOIN tbl_budget_status bs ON bs.budget_status_id = q.budget_status_id
1746	LEFT JOIN tbl_budget_types bt ON q.budget_type_id = bt.budget_type_id
1747	LEFT JOIN tbl_budget_type_groups btg ON bt.budget_type_group_id = btg.budget_type_group_id
1748	LEFT JOIN tbl_customer_types ct ON q.customer_type_id = ct.customer_type_id
1749	JOIN
1750	(
1751	SELECT
1752	q.source_id,
1753	NULL a,
1754	NULL b,
1755	NULL c,
1756	NULL d,
1757	NULL e,
1758	NULL f
1759	{$col}
1760	FROM
1761	tbl_quotations q
1762	LEFT JOIN tbl_sources s ON s.source_id = q.source_id
1763	LEFT JOIN tbl_budget_status bs ON bs.budget_status_id = q.budget_status_id
1764	LEFT JOIN tbl_budget_types bt ON q.budget_type_id = bt.budget_type_id
1765	LEFT JOIN tbl_budget_type_groups btg ON bt.budget_type_group_id = btg.budget_type_group_id
1766	LEFT JOIN tbl_customer_types ct ON q.customer_type_id = ct.customer_type_id
1767	WHERE
1768	q.for_add = 0
1769	AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1770	AND (q.commercial IS NOT NULL AND q.commercial != '')
1771	AND q.budget_type_id != 7
1772	AND q.budget_type_id IS NOT NULL
1773	AND q.source_id > 0
1774	AND q.budget_status_id > 0
1775	AND q.budget_status_id != 18
1776	AND s.digital_campaign_source > 0
1777	{$where}
1778	GROUP BY q.source_id
1779	) AS totals
1780	ON q.source_id = totals.source_id
1781	WHERE
1782	q.for_add = 0
1783	AND q.amount REGEXP '^[0-9]+\\.?[0-9]*$' = 1
1784	AND (q.commercial IS NOT NULL AND q.commercial != '')
1785	AND q.budget_type_id != 7
1786	AND q.budget_type_id IS NOT NULL
1787	AND q.source_id > 0
1788	AND q.budget_status_id > 0
1789	AND q.budget_status_id != 18
1790	AND s.digital_campaign_source > 0
1791	{$where}
1792	GROUP BY
1793	q.source_id,
1794	q.budget_status_id WITH ROLLUP
1795	ORDER BY
1796	CASE WHEN q.source_id IS NULL THEN 1 ELSE 0 END,
1797	{$sortByFirst}
1798	q.source_id,
1799	CASE WHEN q.budget_status_id IS NULL THEN 0 ELSE 1 END,
1800	{$sortBySecond}
1801	q.budget_status_id";
1802	// return $query;
1803	$result = DB::select($query);
1804
1805	$query = "SELECT
1806	btg.budget_type_group_id,
1807	btg.name,
1808	(
1809	SELECT
1810	GROUP_CONCAT(COALESCE(bt.name, '') ORDER BY ISNULL(bt.priority), bt.priority ASC SEPARATOR '\|')
1811	FROM
1812	tbl_budget_types bt
1813	WHERE
1814	bt.budget_type_group_id = btg.budget_type_group_id
1815	) budget_types
1816	FROM
1817	tbl_budget_type_groups btg
1818	ORDER BY
1819	ISNULL(btg.priority),
1820	btg.priority ASC";
1821
1822	$budgetTypeGroups = DB::select($query);
1823
1824	foreach ($budgetTypeGroups as $item) {
1825	$item->group_key_name = str_replace(' ', '', $item->name).$item->budget_type_group_id;
1826	$item->budget_types = explode('\|', $item->budget_types);
1827	}
1828
1829	return response([
1830	'message' => 'OK',
1831	'data' => $result,
1832	'budgetTypeGroups' => $budgetTypeGroups,
1833	]);
1834
1835	} catch (\Exception $e) {
1836	/** @disregard P1014 */
1837	$e->exceptionCode = 'LIST_TYPE_OF_ORDER_EXCEPTION';
1838	report($e);
1839
1840	return response(['message' => 'KO', 'error' => $e->getMessage()]);
1841	}
1842	}
1843
1844	public function getG3wTasksExecuted(Request $request)
1845	{
1846	$region = urldecode(@getallheaders()['Region']);
1847	try {
1848	$result = $this->workService->getG3wTasksExecuted($region);
1849
1850	return response()->json([
1851	'data' => $result,
1852	]);
1853
1854	} catch (\Exception $e) {
1855	/** @disregard P1014 */
1856	$e->exceptionCode = 'GET_G3W_TASKS_EXECUTED_EXCEPTION';
1857	report($e);
1858	Log::channel('g3w_invoices')->error('Failed to get g3w tasks executed: '.$e->getMessage());
1859
1860	return response()->json([
1861	'message' => $e->getMessage(),
1862	], 500);
1863	}
1864
1865	}
1866
1867	public function list_performance_metrics(Request $request)
1868	{
1869
1870	// try {
1871
1872	$data = $request->all();
1873
1874	$companyId = intval($data['company_id']);
1875	$where = '';
1876
1877	if ($companyId != 0) {
1878	$where .= " AND q.company_id = {$companyId} ";
1879	} else {
1880	$where .= " AND q.company_id IN ({$this->companyId}) ";
1881	}
1882
1883	$ticketMedium = 1;
1884
1885	if (isset($data['medium_ticket_index']) && $data['medium_ticket_index']) {
1886	$ticketMedium = $data['medium_ticket_index'];
1887	}
1888
1889	$ratioAcceptance = 1;
1890
1891	if (isset($data['ratio_acceptance']) && $data['ratio_acceptance']) {
1892	$ratioAcceptance = $data['ratio_acceptance'];
1893	}
1894
1895	$acceptance = 1;
1896
1897	if (isset($data['acceptance']) && $data['acceptance']) {
1898	$acceptance = $data['acceptance'];
1899	}
1900
1901	$createdRange = '';
1902	$issuedRange = '';
1903	$acceptanceRange = '';
1904	$requestRange = '';
1905
1906	$issuedWhere = $where;
1907
1908	if ((isset($data['start_date']) && $data['start_date'] != null) && (isset($data['end_date']) && $data['end_date'] != null)) {
1909	$createdRange .= " AND q.created_at BETWEEN '{$data['start_date']}' AND '{$data['end_date']}' ";
1910	$issuedRange .= " AND q.issue_date BETWEEN '{$data['start_date']}' AND '{$data['end_date']}' ";
1911	$acceptanceRange .= " AND q.acceptance_date BETWEEN '{$data['start_date']}' AND '{$data['end_date']}' ";
1912	$requestRange .= " AND q.request_date BETWEEN '{$data['start_date']}' AND '{$data['end_date']}' ";
1913	}
1914
1915	$query = "SELECT
1916	q.company_name,
1917	q.company_id,
1918	SUM(q.total_investment) totalInvestment,
1919	SUM(q.totalOrders) totalOrders,
1920	GROUP_CONCAT(q.groupConcatIdsTotalOrders) groupConcatIdsTotalOrders,
1921	COALESCE(SUM(q.total_investment) / SUM(q.totalOrders), 0) CPL,
1922	COALESCE(SUM(q.total_investment) / SUM(q.totalOrdersAcceptanceAmount), 0) CAC1,
1923	COALESCE(SUM(q.total_investment) / SUM(q.totalOrdersAcceptanceAmountAnyMonth), 0) CAC2,
1924	COALESCE(SUM(q.total_investment) / SUM(q.totalOrdersAcceptanceAmountSent), 0) CAC3,
1925	SUM(q.totalOrdersSent) totalOrdersSent,
1926	SUM(q.totalOrdersAmountSent) totalOrdersAmountSent,
1927	GROUP_CONCAT(q.groupConcatIdsTotalOrdersSent) groupConcatIdsTotalOrdersSent,
1928	SUM(q.totalOrdersAmount) totalOrdersAmount,
1929	SUM(q.totalOrdersSentAnyMonth) totalOrdersSentAnyMonth,
1930	SUM(q.totalOrdersAmountSentAnyMonth) totalOrdersAmountSentAnyMonth,
1931	GROUP_CONCAT(q.groupConcatIdsTotalOrdersSentAnyMonth) groupConcatIdsTotalOrdersSentAnyMonth,
1932	COALESCE(SUM(q.totalOrdersSent) / SUM(q.totalOrders) * 100, 0) percentageOfLeadsSentMonthlyOutOfLeadsMonthly,
1933	SUM(q.totalOrdersAcceptance) totalOrdersAcceptance,
1934	GROUP_CONCAT(q.groupConcatIdsTotalOrdersAcceptance) groupConcatIdsTotalOrdersAcceptance,
1935	SUM(q.totalOrdersAcceptanceAmount) totalOrdersAcceptanceAmount,
1936	SUM(q.totalOrdersAcceptanceAnyMonth) totalOrdersAcceptanceAnyMonth,
1937	GROUP_CONCAT(q.groupConcatIdsTotalOrdersAcceptanceAnyMonth) groupConcatIdsTotalOrdersAcceptanceAnyMonth,
1938	SUM(q.totalOrdersAcceptanceAmountAnyMonth) totalOrdersAcceptanceAmountAnyMonth,
1939	SUM(q.totalOrdersAcceptanceSent) totalOrdersAcceptanceSent,
1940	GROUP_CONCAT(q.groupConcatIdsTotalOrdersAcceptanceSent) groupConcatIdsTotalOrdersAcceptanceSent,
1941	SUM(q.totalOrdersAcceptanceAmountSent) totalOrdersAcceptanceAmountSent,
1942	CASE {$ticketMedium}
1943	WHEN 1 THEN COALESCE(SUM(q.totalOrdersAmountSent) / SUM(q.totalOrdersSent), 0)
1944	WHEN 2 THEN COALESCE(SUM(q.totalOrdersAmountSentAnyMonth) / SUM(q.totalOrdersSentAnyMonth), 0)
1945	WHEN 3 THEN COALESCE(SUM(q.totalOrdersAcceptanceAmount) / SUM(q.totalOrdersAcceptance), 0)
1946	WHEN 4 THEN COALESCE(SUM(q.totalOrdersAcceptanceAmountAnyMonth) / SUM(q.totalOrdersAcceptanceAnyMonth), 0)
1947	WHEN 5 THEN COALESCE(SUM(q.totalOrdersAcceptanceAmountSent) / SUM(q.totalOrdersAcceptanceSent), 0)
1948	END AS ticketMedio,
1949	CASE {$ratioAcceptance}
1950	WHEN 1 THEN COALESCE(SUM(q.totalOrdersAcceptanceSent) / SUM(q.totalOrdersSent), 0)
1951	WHEN 2 THEN COALESCE(SUM(q.totalOrdersAcceptanceAmount) / SUM(q.totalOrdersAmountSent), 0)
1952	END AS ratioAcceptance,
1953	COALESCE(
1954	CASE {$acceptance}
1955	WHEN 1 THEN
1956	CASE {$ticketMedium}
1957	WHEN 1 THEN SUM(q.totalOrdersAmountSent)
1958	/ NULLIF(SUM(q.totalOrdersSent), 0)
1959	WHEN 2 THEN SUM(q.totalOrdersAmountSentAnyMonth)
1960	/ NULLIF(SUM(q.totalOrdersSentAnyMonth), 0)
1961	WHEN 3 THEN SUM(q.totalOrdersAcceptanceAmount)
1962	/ NULLIF(SUM(q.totalOrdersAcceptance), 0)
1963	WHEN 4 THEN SUM(q.totalOrdersAcceptanceAmountAnyMonth)
1964	/ NULLIF(SUM(q.totalOrdersAcceptanceAnyMonth), 0)
1965	WHEN 5 THEN SUM(q.totalOrdersAcceptanceAmountSent)
1966	/ NULLIF(SUM(q.totalOrdersAcceptanceSent), 0)
1967	END
1968	/ NULLIF(SUM(q.totalOrdersSentAnyMonth), 0)
1969
1970
1971	WHEN 2 THEN
1972	SUM(q.totalOrdersAmountSentAnyMonth)
1973	/ NULLIF(SUM(q.totalOrdersSentAnyMonth), 0)
1974	END,
1975	0
1976	) AS acceptance,
1977	COALESCE(SUM(q.totalOrdersAcceptance) / SUM(q.totalOrdersSent) * 100, 0) percentageAcceptanceOneN,
1978	COALESCE(SUM(q.totalOrdersAcceptanceAmount) / SUM(q.totalOrdersAmountSent) * 100, 0) percentageAcceptanceOneC,
1979	COALESCE(SUM(q.totalOrdersAcceptanceSent) / SUM(q.totalOrdersSentAnyMonth) * 100, 0) percentageAcceptanceTwoN,
1980	COALESCE(SUM(q.totalOrdersAcceptanceAmount) / SUM(q.totalOrdersAmountSentAnyMonth) * 100, 0) percentageAcceptanceTwoC,
1981	COALESCE(SUM(q.totalOrdersAcceptanceAmountSent) - SUM(q.total_investment), 0) leadsBenefitMonth,
1982	COALESCE(SUM(q.totalOrdersAcceptance1) - SUM(q.total_investment), 0) realBenefit,
1983	SUM(q.totalOrdersSent1) totalOrdersSent1,
1984	SUM(q.totalOrdersAmountSent1) totalOrdersAmountSent1,
1985	GROUP_CONCAT(q.groupConcatIdsTotalOrdersSent1) groupConcatIdsTotalOrdersSent1,
1986	SUM(q.totalOrdersAcceptance1) totalOrdersAcceptance1,
1987	SUM(q.totalOrdersAcceptanceAmount1) totalOrdersAcceptanceAmount1,
1988	GROUP_CONCAT(q.groupConcatIdsTotalOrdersAcceptance1) groupConcatIdsTotalOrdersAcceptance1
1989	FROM
1990	(
1991	SELECT
1992	c.region company_name,
1993	c.company_id,
1994	c.total_investment,
1995	COUNT(1) totalOrders,
1996	SUM(q.amount) totalOrdersAmount,
1997	GROUP_CONCAT(q.id) groupConcatIdsTotalOrders,
1998	0 totalOrdersSent,
1999	0 totalOrdersAmountSent,
2000	NULL groupConcatIdsTotalOrdersSent,
2001	0 totalOrdersSentAnyMonth,
2002	0 totalOrdersAmountSentAnyMonth,
2003	NULL groupConcatIdsTotalOrdersSentAnyMonth,
2004	0 totalOrdersAcceptance,
2005	0 totalOrdersAcceptanceAmount,
2006	NULL groupConcatIdsTotalOrdersAcceptance,
2007	0 totalOrdersAcceptanceAnyMonth,
2008	0 totalOrdersAcceptanceAmountAnyMonth,
2009	NULL groupConcatIdsTotalOrdersAcceptanceAnyMonth,
2010	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,
2011	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,
2012	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,
2013	0 totalOrdersSent1,
2014	0 totalOrdersAmountSent1,
2015	NULL groupConcatIdsTotalOrdersSent1,
2016	0 totalOrdersAcceptance1,
2017	0 totalOrdersAcceptanceAmount1,
2018	NULL groupConcatIdsTotalOrdersAcceptance1
2019	FROM tbl_quotations q
2020	LEFT JOIN tbl_sources s
2021	ON q.source_id = s.source_id
2022	LEFT JOIN tbl_companies c
2023	ON q.company_id = c.company_id
2024	WHERE
2025	q.for_add = 0
2026	AND q.issue_date IS NOT NULL
2027	AND (q.commercial IS NOT NULL AND q.commercial != '')
2028	AND q.source_id IS NOT NULL
2029	AND s.digital_campaign_source > 0
2030	AND q.budget_status_id != 18
2031	{$where}
2032	{$issuedRange}
2033	GROUP BY q.company_id
2034
2035	UNION ALL
2036
2037	SELECT
2038	c.region company_name,
2039	c.company_id,
2040	0 total_investment,
2041	0 totalOrders,
2042	0 totalOrdersAmount,
2043	NULL groupConcatIdsTotalOrders,
2044	0 totalOrdersSent,
2045	0 totalOrdersAmountSent,
2046	NULL groupConcatIdsTotalOrdersSent,
2047	0 totalOrdersSentAnyMonth,
2048	0 totalOrdersAmountSentAnyMonth,
2049	NULL groupConcatIdsTotalOrdersSentAnyMonth,
2050	COUNT(1) totalOrdersAcceptance,
2051	SUM(q.amount) totalOrdersAcceptanceAmount,
2052	GROUP_CONCAT(q.id) groupConcatIdsTotalOrdersAcceptance,
2053	0 totalOrdersAcceptanceAnyMonth,
2054	0 totalOrdersAcceptanceAmountAnyMonth,
2055	NULL groupConcatIdsTotalOrdersAcceptanceAnyMonth,
2056	0 totalOrdersAcceptanceSent,
2057	0 totalOrdersAcceptanceAmountSent,
2058	NULL 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.acceptance_date IS NOT NULL
2073	AND q.acceptance_date != '0000-00-00 00:00:00'
2074	AND (q.commercial IS NOT NULL AND q.commercial != '')
2075	AND q.source_id IS NOT NULL
2076	AND s.digital_campaign_source > 0
2077	AND q.budget_status_id = 3
2078	{$where}
2079	{$createdRange}
2080	GROUP BY q.company_id
2081
2082	UNION ALL
2083
2084	SELECT
2085	c.region company_name,
2086	c.company_id,
2087	0 total_investment,
2088	0 totalOrders,
2089	0 totalOrdersAmount,
2090	NULL groupConcatIdsTotalOrders,
2091	COUNT(1) totalOrdersSent,
2092	SUM(q.amount) totalOrdersAmountSent,
2093	GROUP_CONCAT(q.id) groupConcatIdsTotalOrdersSent,
2094	0 totalOrdersSentAnyMonth,
2095	0 totalOrdersAmountSentAnyMonth,
2096	NULL groupConcatIdsTotalOrdersSentAnyMonth,
2097	0 totalOrdersAcceptance,
2098	0 totalOrdersAcceptanceAmount,
2099	NULL groupConcatIdsTotalOrdersAcceptance,
2100	0 totalOrdersAcceptanceAnyMonth,
2101	0 totalOrdersAcceptanceAmountAnyMonth,
2102	NULL groupConcatIdsTotalOrdersAcceptanceAnyMonth,
2103	0 totalOrdersAcceptanceSent,
2104	0 totalOrdersAcceptanceAmountSent,
2105	NULL groupConcatIdsTotalOrdersAcceptanceSent,
2106	0 totalOrdersSent1,
2107	0 totalOrdersAmountSent1,
2108	NULL groupConcatIdsTotalOrdersSent1,
2109	0 totalOrdersAcceptance1,
2110	0 totalOrdersAcceptanceAmount1,
2111	NULL groupConcatIdsTotalOrdersAcceptance1
2112	FROM tbl_quotations q
2113	LEFT JOIN tbl_sources s
2114	ON q.source_id = s.source_id
2115	LEFT JOIN tbl_companies c
2116	ON q.company_id = c.company_id
2117	WHERE
2118	q.for_add = 0
2119	AND q.issue_date IS NOT NULL
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	{$where}
2124	{$createdRange}
2125	GROUP BY q.company_id
2126
2127	UNION ALL
2128
2129	SELECT
2130	c.region company_name,
2131	c.company_id,
2132	0 total_investment,
2133	0 totalOrders,
2134	0 totalOrdersAmount,
2135	NULL groupConcatIdsTotalOrders,
2136	0 totalOrdersSent,
2137	0 totalOrdersAmountSent,
2138	NULL groupConcatIdsTotalOrdersSent,
2139	0 totalOrdersSentAnyMonth,
2140	0 totalOrdersAmountSentAnyMonth,
2141	NULL groupConcatIdsTotalOrdersSentAnyMonth,
2142	0 totalOrdersAcceptance,
2143	0 totalOrdersAcceptanceAmount,
2144	NULL groupConcatIdsTotalOrdersAcceptance,
2145	0 totalOrdersAcceptanceAnyMonth,
2146	0 totalOrdersAcceptanceAmountAnyMonth,
2147	NULL groupConcatIdsTotalOrdersAcceptanceAnyMonth,
2148	0 totalOrdersAcceptanceSent,
2149	0 totalOrdersAcceptanceAmountSent,
2150	NULL groupConcatIdsTotalOrdersAcceptanceSent,
2151	0 totalOrdersSent1,
2152	0 totalOrdersAmountSent1,
2153	NULL groupConcatIdsTotalOrdersSent1,
2154	0 totalOrdersAcceptance1,
2155	0 totalOrdersAcceptanceAmount1,
2156	NULL groupConcatIdsTotalOrdersAcceptance1
2157	FROM tbl_quotations q
2158	LEFT JOIN tbl_sources s
2159	ON q.source_id = s.source_id
2160	LEFT JOIN tbl_companies c
2161	ON q.company_id = c.company_id
2162	WHERE
2163	q.for_add = 0
2164	AND q.issue_date IS NOT NULL
2165	AND (q.commercial IS NOT NULL AND q.commercial != '')
2166	AND q.source_id IS NOT NULL
2167	AND s.digital_campaign_source > 0
2168	AND q.budget_status_id = 2
2169	AND q.budget_status_id != 18
2170	{$where}
2171	{$requestRange}
2172	GROUP BY q.company_id
2173
2174	UNION ALL
2175
2176	SELECT
2177	c.region company_name,
2178	c.company_id,
2179	0 total_investment,
2180	0 totalOrders,
2181	0 totalOrdersAmount,
2182	0 totalOrdersSent,
2183	0 totalOrdersAmountSent,
2184	NULL groupConcatIdsTotalOrdersSent,
2185	NULL groupConcatIdsTotalOrders,
2186	COUNT(1) totalOrdersSentAnyMonth,
2187	SUM(q.amount) totalOrdersAmountSentAnyMonth,
2188	GROUP_CONCAT(q.id) groupConcatIdsTotalOrdersSentAnyMonth,
2189	0 totalOrdersAcceptance,
2190	0 totalOrdersAcceptanceAmount,
2191	NULL groupConcatIdsTotalOrdersAcceptance,
2192	0 totalOrdersAcceptanceAnyMonth,
2193	0 totalOrdersAcceptanceAmountAnyMonth,
2194	NULL groupConcatIdsTotalOrdersAcceptanceAnyMonth,
2195	0 totalOrdersAcceptanceSent,
2196	0 totalOrdersAcceptanceAmountSent,
2197	NULL groupConcatIdsTotalOrdersAcceptanceSent,
2198	0 totalOrdersSent1,
2199	0 totalOrdersAmountSent1,
2200	NULL groupConcatIdsTotalOrdersSent1,
2201	0 totalOrdersAcceptance1,
2202	0 totalOrdersAcceptanceAmount1,
2203	NULL groupConcatIdsTotalOrdersAcceptance1
2204	FROM tbl_quotations q
2205	LEFT JOIN tbl_sources s
2206	ON q.source_id = s.source_id
2207	LEFT JOIN tbl_companies c
2208	ON q.company_id = c.company_id
2209	WHERE
2210	q.for_add = 0
2211	AND q.request_date IS NOT NULL
2212	AND (q.commercial IS NOT NULL AND q.commercial != '')
2213	AND q.source_id IS NOT NULL
2214	AND s.digital_campaign_source > 0
2215	AND q.budget_status_id != 18
2216	{$where}
2217	{$issuedRange}
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	0 totalOrdersSentAnyMonth,
2233	0 totalOrdersAmountSentAnyMonth,
2234	NULL 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	COUNT(1) totalOrdersSent1,
2245	SUM(q.amount) totalOrdersAmountSent1,
2246	GROUP_CONCAT(q.id) 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.issue_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 = 2
2262	AND q.budget_status_id != 18
2263	{$where}
2264	{$issuedRange}
2265	GROUP BY q.company_id
2266
2267	UNION ALL
2268
2269	SELECT
2270	c.region company_name,
2271	c.company_id,
2272	0 total_investment,
2273	0 totalOrders,
2274	0 totalOrdersAmount,
2275	NULL groupConcatIdsTotalOrders,
2276	0 totalOrdersSent,
2277	0 totalOrdersAmountSent,
2278	NULL groupConcatIdsTotalOrdersSent,
2279	0 totalOrdersSentAnyMonth,
2280	0 totalOrdersAmountSentAnyMonth,
2281	NULL groupConcatIdsTotalOrdersSentAnyMonth,
2282	0 totalOrdersAcceptance,
2283	0 totalOrdersAcceptanceAmount,
2284	NULL groupConcatIdsTotalOrdersAcceptance,
2285	0 totalOrdersAcceptanceAnyMonth,
2286	0 totalOrdersAcceptanceAmountAnyMonth,
2287	NULL groupConcatIdsTotalOrdersAcceptanceAnyMonth,
2288	0 totalOrdersAcceptanceSent,
2289	0 totalOrdersAcceptanceAmountSent,
2290	NULL groupConcatIdsTotalOrdersAcceptanceSent,
2291	0 totalOrdersSent1,
2292	0 totalOrdersAmountSent1,
2293	NULL groupConcatIdsTotalOrdersSent1,
2294	COUNT(1) totalOrdersAcceptance1,
2295	SUM(q.amount) totalOrdersAcceptanceAmount1,
2296	GROUP_CONCAT(q.id) groupConcatIdsTotalOrdersAcceptance1
2297	FROM tbl_quotations q
2298	LEFT JOIN tbl_sources s
2299	ON q.source_id = s.source_id
2300	LEFT JOIN tbl_companies c
2301	ON q.company_id = c.company_id
2302	WHERE
2303	q.for_add = 0
2304	AND q.acceptance_date IS NOT NULL
2305	AND q.acceptance_date != '0000-00-00 00:00:00'
2306	AND (q.commercial IS NOT NULL AND q.commercial != '')
2307	AND q.source_id IS NOT NULL
2308	AND s.digital_campaign_source > 0
2309	AND q.budget_status_id != 18
2310	{$where}
2311	{$acceptanceRange}
2312	GROUP BY q.company_id
2313	) q
2314	GROUP BY q.company_name WITH ROLLUP";
2315
2316	// $value = Cache::get(base64_encode($query));
2317
2318	// if(!$value){
2319	$result = DB::select($query);
2320
2321	// Cache::put(base64_encode($query), $result, 600);
2322	// }else{
2323	// $result = $value;
2324	// }
2325
2326	return response([
2327	'message' => 'OK',
2328	'data' => $result,
2329	]);
2330
2331	// } catch (\Exception $e) {
2332	// /** @disregard P1014 */
2333	// $e->exceptionCode = 'LIST_PERFORMANCE_METRICS_EXCEPTION';
2334	// report($e);
2335	// return response(['message' => 'KO', 'error' => $e->getMessage()]);
2336	// }
2337
2338	}
2339	}