← All changes | Inc/Core/Analytics/Metrics/ConversionRate.php +164 -43 trunk → 2.1.14 View file →

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