firebox / Inc / Core / Analytics / Metrics / Conversions.php

Conversions.php in FireBox – WooCommerce Popup Builder, Exit Intent Popup, Email Optin & Cart Abandonment 2.1.14, at Inc/Core/Analytics/Metrics/Conversions.php

205 lines 4.3 KB

Raw Download Zip

1	<?php
2	/**
3	* @package FireBox
4	* @version 2.1.14 Free
5	*
6	* @author FirePlugins <info@fireplugins.com>
7	* @link https://www.fireplugins.com
8	* @copyright Copyright © 2024 FirePlugins All Rights Reserved
9	* @license GNU GPLv3 <http://www.gnu.org/licenses/gpl.html> or later
10	*/
11
12	namespace FireBox\Core\Analytics\Metrics;
13
14	if (!defined('ABSPATH'))
15	{
16	exit; // Exit if accessed directly.
17	}
18
19	class Conversions extends Metric
20	{
21	public function getData()
22	{
23	$this->applyFilters();
24
25	$sql = "SELECT
26	{$this->getSelect()}
27	FROM
28	{$this->wpdb->prefix}firebox_logs_details as bld
29	LEFT JOIN {$this->wpdb->prefix}firebox_logs as bl ON bl.id = bld.log_id
30	{$this->getJOINs()}
31	CROSS JOIN (
32	SELECT '%s' AS start_date, '%s' AS end_date
33	) AS outer_query
34	WHERE
35	1
36	{$this->getWherePeriod('bld')}
37	AND bld.event = 'conversion'
38	{$this->sql_filters}
39	{$this->getGroupBy()}
40	{$this->getHaving()}
41	{$this->getOrderBy()}
42	{$this->getLimit()}
43	{$this->getOffset()}";
44
45	$sql = $this->wpdb->prepare($sql, $this->query_placeholders); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
46
47	$data = [];
48
49	if ($this->type === 'count')
50	{
51	$column_value = $this->wpdb->get_col($sql); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
52	$data = array_sum(array_map('intval', $column_value));
53	}
54	else
55	{
56	$data = $this->wpdb->get_results($sql); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
57	}
58
59	return $data;
60	}
61
62	private function getSelect()
63	{
64	$select = '';
65
66	switch ($this->type)
67	{
68	case 'top_campaign':
69	$select = 'bl.box as id, (select p.post_title from ' . $this->wpdb->prefix . 'posts as p WHERE p.ID = bl.box) as label, COUNT(bld.id) as total';
70	break;
71
72	case 'countries':
73	$select = 'bl.country as label, COUNT(bld.id) as total';
74	break;
75
76	case 'referrers':
77	$select = 'bl.referrer as label, COUNT(bld.id) as total';
78	break;
79
80	case 'devices':
81	$select = 'bl.device as label, COUNT(bld.id) as total';
82	break;
83
84	case 'pages':
85	$select = 'bl.page as label, COUNT(bld.id) as total';
86	break;
87
88	case 'weekly':
89	$select = 'DATE_FORMAT(STR_TO_DATE(CONCAT(yearweek(bld.date), " ' . firebox()->_('FB_MONDAY') . '"), \'%%X%%V %%W\'), \'%%d %%b %%y\') as label, COUNT(bld.id) as total';
90	break;
91
92	case 'monthly':
93	$select = 'DATE_FORMAT(bld.date, \'%%b %%Y\') as label, COUNT(bld.id) as total';
94	break;
95
96	case 'day_of_week':
97	$select = 'DAYNAME(bld.date) as label, COUNT(bld.id) as total';
98	break;
99
100	case 'list':
101	default:
102	$partA = 'date(bld.date) as label';
103
104	if ($this->isSingleDay())
105	{
106	$partA = 'CONCAT(DATE_FORMAT(bld.date, \'%H\'), \':00\') as label';
107	}
108
109	$select = $partA . ', COUNT(bld.id) as total';
110	break;
111
112	case 'count':
113	$select = 'COUNT(bld.id) as total';
114	break;
115	}
116
117	return $select;
118	}
119
120	private function getGroupBy()
121	{
122	if ($this->type === 'count')
123	{
124	return;
125	}
126
127	$groupby = 'date(bld.date)';
128
129	if ($this->isSingleDay())
130	{
131	$groupby = 'CONCAT(DATE_FORMAT(bld.date, \'%H\'), \':00\')';
132	}
133
134	if ($this->type === 'top_campaign')
135	{
136	$groupby = 'bl.box';
137	}
138	else if ($this->type === 'countries')
139	{
140	$groupby = 'bl.country';
141	}
142	else if ($this->type === 'referrers')
143	{
144	$groupby = 'bl.referrer';
145	}
146	else if ($this->type === 'devices')
147	{
148	$groupby = 'bl.device';
149	}
150	else if ($this->type === 'pages')
151	{
152	$groupby = 'bl.page';
153	}
154	else if ($this->type === 'weekly')
155	{
156	$groupby = 'yearweek(bld.date)';
157	}
158	else if ($this->type === 'monthly')
159	{
160	$groupby = 'YEAR(bld.date), MONTH(bld.date)';
161	}
162	else if ($this->type === 'day_of_week')
163	{
164	$groupby = 'label';
165	}
166
167	return 'GROUP BY ' . $groupby;
168	}
169
170	private function getOrderBy()
171	{
172	if ($this->type === 'count')
173	{
174	return;
175	}
176
177	$orderby = 'bld.date DESC';
178
179	if (in_array($this->type, ['top_campaign', 'countries', 'referrers', 'devices', 'pages', 'day_of_week']))
180	{
181	$orderby = 'total DESC';
182	}
183
184	if (isset($this->options['orderby']))
185	{
186	$orderby = $this->options['orderby'];
187	}
188
189	return 'ORDER BY ' . $orderby;
190	}
191
192	private function getHaving()
193	{
194	$having = '';
195
196	if ($this->type === 'countries')
197	{
198	$having = 'bl.country IS NOT NULL';
199	}
200
201	$having = $having ? 'HAVING ' . $having : '';
202
203	return $having;
204	}
205	}