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