wpfunnels / includes / core / Reporting / ReportGenerator.php

ReportGenerator.php in WPFunnels – Funnel Builder for WooCommerce with Checkout & One Click Upsell 3.10.9, at includes/core/Reporting/ReportGenerator.php

341 lines 10.2 KB

Raw Download Zip

1	<?php
2
3	namespace WPFunnels\Report;
4
5	use WPFunnels\Wpfnl_functions;
6
7	class ReportGenerator {
8
9
10	/**
11	* Format a monetary value using WooCommerce's decimal settings.
12	*
13	* Uses wc_format_decimal() + wc_get_price_decimals() when WooCommerce
14	* is active, otherwise falls back to rounding to 2 decimal places.
15	*
16	* @param mixed $value Raw numeric value.
17	* @return float Formatted decimal value.
18	*
19	* @since 3.6.0
20	*/
21	private static function format_price( $value ) {
22	if ( function_exists( 'wc_format_decimal' ) ) {
23	return (float) wc_format_decimal( $value, wc_get_price_decimals() );
24	}
25	return round( (float) $value, 2 );
26	}
27
28
29	/**
30	* Get overview data of all funnels
31	*
32	* @param $start_date
33	* @param $end_date
34	* @return array
35	*
36	* @since 3.2.0
37	*/
38	public static function get_overview( $start_date, $end_date ) {
39	$total_orders = self::get_total_orders( $start_date, $end_date );
40	$total_customers = self::get_total_customers( $start_date, $end_date );
41	$total_sales = self::get_total_sales( $start_date, $end_date );
42	$total_ob_revenue = self::get_total_ob_sales( $start_date, $end_date );
43
44	$result = array(
45	'total_orders' => (int) $total_orders,
46	'total_customers' => (int) $total_customers,
47	'total_sales' => self::format_price( $total_sales ),
48	'total_ob_revenue' => self::format_price( $total_ob_revenue ),
49	);
50
51	$response['status'] = true;
52	$response['data'] = apply_filters( 'wpfunnels/funnels-overview-data', $result, $start_date, $end_date );
53	return $response;
54	}
55
56
57	public static function get_stats( $start_date, $end_date, $interval ) {
58	$intervals = self::get_intervals( $start_date, $end_date, $interval );
59	$response = array();
60	foreach ( $intervals as $interval ) {
61	$start_date = isset($interval['start_date']) ? $interval['start_date'] : (new \DateTime('monday last week'))->format('Y-m-d H:i:s');
62	$end_date = isset($interval['end_date']) ? $interval['end_date'] : (new \DateTime('sunday last week'))->format('Y-m-d H:i:s');
63	$total_orders = self::get_total_orders( $start_date, $end_date );
64	$total_customers = self::get_total_customers( $start_date, $end_date );
65	$total_sales = self::get_total_sales( $start_date, $end_date );
66	$total_checkout_sales = self::get_total_checkout_sales( $start_date, $end_date );
67	$total_ob_revenue = self::get_total_ob_sales( $start_date, $end_date );
68	$total_leads = self::get_total_leads( $start_date, $end_date );
69	$response['sales']['interval'][] = apply_filters( 'wpfunnels/stat-interval-data', array(
70	'total_orders' => (int) $total_orders,
71	'total_customers' => (int) $total_customers,
72	'total_sales' => self::format_price( $total_checkout_sales ),
73	'total_ob_revenue' => self::format_price( $total_ob_revenue ),
74	), $start_date, $end_date );
75
76	$response['lead']['interval'][] = apply_filters( 'wpfunnels/stat-interval-data-leads', array(
77	'total' => (int) $total_leads,
78	), $start_date, $end_date );
79	}
80	$response['status'] = true;
81	return apply_filters( 'wpfunnels/funnels-stats-data', $response );
82	}
83
84
85	/**
86	* Get the top performing funnels.
87	*
88	* This method retrieves the top three performing funnels based on total revenue,
89	* which is calculated as the sum of total sales, upsell sales, downsell sales, and orderbump sales.
90	*
91	* @global wpdb $wpdb WordPress database abstraction object.
92	* @return array An array of top performing funnels, where each funnel is represented as an associative array
93	* containing 'id', 'link', 'title', 'views', 'conversion', 'revenue', and 'conversion_rate'.
94	* @throws Exception If there is an issue with the database query.
95	* @since 3.5.0
96	*/
97	public static function get_top_funnels() {
98	global $wpdb;
99	$table = $wpdb->prefix. 'wpfnl_stats';
100	$sql = "SELECT funnel_id, SUM(
101	CASE
102	WHEN status = 'completed' THEN ( total_sales )
103	ELSE 0
104	END
105	) AS total_revenue
106	FROM $table
107	GROUP BY funnel_id
108	ORDER BY total_revenue DESC
109	LIMIT 3";
110	$top_funnels = $wpdb->get_results($sql);
111	$funnel_data = array();
112	foreach ( $top_funnels as $top_funnel ) {
113	$funnel_id = $top_funnel->funnel_id;
114	if ( 'publish' !== get_post_status($funnel_id) ) {
115	continue;
116	}
117	$revenue = $top_funnel->total_revenue;
118	$funnel_data[] = array(
119	'id' => $funnel_id,
120	'link' => admin_url("/admin.php?page=edit_funnel&id={$funnel_id}&step_id=0"),
121	'title' => get_the_title( $funnel_id ),
122	'views' => 0,
123	'conversion' => 0,
124	'revenue' => self::format_price( $revenue ),
125	'conversion_rate' => 0,
126	);
127	}
128	return apply_filters( 'wpfunnels/top-performing-funnels-data', $funnel_data );
129	}
130
131
132	/**
133	* Get total number of orders
134	*
135	* @param $start_date
136	* @param $end_date
137	* @return array\|object\|null
138	*
139	* @since 3.2.0
140	*/
141	public static function get_total_orders( $start_date, $end_date ) {
142	global $wpdb;
143	$table = $wpdb->prefix. 'wpfnl_stats';
144	$sql = "SELECT count(id) FROM $table";
145	$sql = self::include_where_clause($sql);
146	$result = $wpdb->get_var($wpdb->prepare($sql, $start_date, $end_date ));
147	return $result;
148	}
149
150
151	/**
152	* Get total number of customers
153	*
154	* @param $start_date
155	* @param $end_date
156	* @return string\|null
157	*
158	* @since 3.2.0
159	*/
160	public static function get_total_customers( $start_date, $end_date ) {
161	global $wpdb;
162	$table = $wpdb->prefix. 'wpfnl_stats';
163	$sql = "SELECT count(DISTINCT customer_id) as count FROM $table" ;
164	$sql = self::include_where_clause($sql);
165	$result = $wpdb->get_var( $wpdb->prepare($sql, $start_date, $end_date ) );
166	return $result;
167	}
168
169
170	/**
171	* Get total sales
172	*
173	* @param $start_date
174	* @param $end_date
175	* @return mixed
176	*
177	* @since 3.2.0
178	*/
179	public static function get_total_sales( $start_date, $end_date ) {
180	global $wpdb;
181	$table = $wpdb->prefix. 'wpfnl_stats' ;
182	$sql = "SELECT SUM(total_sales) as total_sales FROM $table";
183	$sql = self::include_where_clause($sql);
184	$result = $wpdb->get_var( $wpdb->prepare( $sql, $start_date, $end_date ) );
185	return $result;
186	}
187
188
189	public static function get_total_checkout_sales( $start_date, $end_date ){
190	global $wpdb;
191	$table = $wpdb->prefix. 'wpfnl_stats' ;
192	$sql = "
193	SELECT
194	SUM(total_sales - ( orderbump_sales + upsell_sales + downsell_sales )) AS checkout_sales
195	FROM
196	{$table}
197	";
198	$sql = self::include_where_clause($sql);
199	$result = $wpdb->get_var( $wpdb->prepare( $sql, $start_date, $end_date ) );
200
201	return $result;
202	}
203
204
205	/**
206	* Get total order bump sales
207	*
208	* @param $start_date
209	* @param $end_date
210	* @return mixed
211	*
212	* @since 3.2.0
213	*/
214	public static function get_total_ob_sales( $start_date, $end_date ) {
215	global $wpdb;
216	$table = $wpdb->prefix. 'wpfnl_stats' ;
217	$sql = "SELECT SUM(orderbump_sales) as orderbump_sales FROM $table";
218	$sql = self::include_where_clause($sql);
219	$result = $wpdb->get_var( $wpdb->prepare( $sql, $start_date, $end_date ) );
220	return $result;
221	}
222
223
224	/**
225	* Get total number of leads
226	*
227	* @param $start_date
228	* @param $end_date
229	* @return string\|null
230	*
231	* @since 3.2.0
232	*/
233	public static function get_total_leads( $start_date, $end_date ) {
234	global $wpdb;
235	$table = $wpdb->prefix. 'wpfnl_optin_entries' ;
236	$sql = "SELECT COUNT(id) as total FROM $table";
237	$sql = self::include_where_clause_leads($sql);
238	$result = $wpdb->get_var( $wpdb->prepare( $sql, $start_date, $end_date ) );
239	return $result;
240	}
241
242
243	/**
244	* Include where clause
245	*
246	* @param $sql
247	* @return string
248	*
249	* @since 3.2.0
250	*/
251	public static function include_where_clause( $sql ) {
252	return $sql." WHERE paid_date >= %s AND paid_date <= %s AND status = 'completed' ";
253	}
254
255
256	/**
257	* Include where clause for leads fetching query
258	*
259	* @param $sql
260	* @return string
261	*
262	* @since 3.2.0
263	*/
264	public static function include_where_clause_leads( $sql ) {
265	return $sql." WHERE date_created >= %s AND date_created <= %s ";
266	}
267
268
269	/**
270	* Get interval data
271	*
272	* This method generates an array of date intervals between the given start and end dates,
273	* based on the specified interval type (day, month, quarter, hour).
274	*
275	* @param string $start_date The start date in 'Y-m-d H:i:s' format.
276	* @param string $end_date The end date in 'Y-m-d H:i:s' format.
277	* @param string $interval_type The type of interval ('day', 'month', 'quarter', 'hour').
278	* @return array An array of intervals, where each interval is an associative array
279	* with 'start_date' and 'end_date' keys in 'Y-m-d H:i:s' format.
280	* @throws \Exception If an invalid interval type is provided.
281	*
282	* @since 3.2.0
283	*/
284	public static function get_intervals( $start_date, $end_date, $interval_type ) {
285	$intervals = array();
286
287	// Convert start and end dates to DateTime objects
288	$start = new \DateTime($start_date);
289	$end = new \DateTime($end_date);
290
291	// Create interval based on interval type
292	switch ($interval_type) {
293	case 'day':
294	$interval = new \DateInterval('P1D');
295	break;
296	case 'month':
297	$interval = new \DateInterval('P1M');
298	$end->modify('last day of this month')->setTime(23, 59, 59);
299	break;
300	case 'quarter':
301	$interval = new \DateInterval('P3M');
302	$end->modify('last day of this quarter')->setTime(23, 59, 59);
303	break;
304	case 'hour':
305	$interval = new \DateInterval('PT1H');
306	break;
307	default:
308	throw new \Exception("Invalid interval type: $interval_type");
309	}
310
311	// Iterate over the date range
312	$period = new \DatePeriod($start, $interval, $end);
313	foreach ($period as $dt) {
314	$interval_end = clone $dt;
315	if ($interval_type === 'day') {
316	$interval_end->setTime(23, 59, 59);
317	} elseif ($interval_type === 'month') {
318	$interval_end->modify('last day of this month')->setTime(23, 59, 59);
319	} elseif ($interval_type === 'hour') {
320	$interval_end->setTime($interval_end->format('H'), 59, 59);
321	}
322	$intervals[] = array(
323	'start_date' => $dt->format('Y-m-d H:i:s'),
324	'end_date' => $interval_end->format('Y-m-d H:i:s')
325	);
326	}
327
328	// Handle the last interval for month type
329	if ($interval_type === 'month' && $end > $interval_end) {
330	$last_month_end = new \DateTime($end->format('Y-m-t').' 23:59:59');
331	$intervals[] = array(
332	'start_date' => $interval_end->format('Y-m-d H:i:s'),
333	'end_date' => $last_month_end->format('Y-m-d H:i:s')
334	);
335	}
336
337	return $intervals;
338	}
339
340	}
341