wpfunnels / includes / core / AdvancedReporting / Reporting.php

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

324 lines 10.2 KB

Raw Download Zip

1	<?php
2	/**
3	* Class Reporting
4	*
5	* This class handles the reporting functionalities for WP Funnels.
6	*/
7
8	namespace WPFunnels\Report;
9
10	use WPFunnels\Wpfnl_functions;
11
12	class Reporting {
13
14	/**
15	* The single instance of the class.
16	*
17	* @var Reporting\|null
18	* @since 2.4.1
19	*/
20	private static $instance = null;
21
22
23	/**
24	* Retrieves the single instance of the Reporting class.
25	*
26	* This method ensures that only one instance of the class is created (singleton pattern).
27	* If the instance doesn't exist, it initializes the class and calls the `init` method.
28	*
29	* @return Reporting The single instance of the Reporting class.
30	* @since 2.4.1
31	*/
32	public static function get_instance() {
33	if ( null === self::$instance ) {
34	self::$instance = new Reporting();
35	self::$instance->init();
36	}
37	return self::$instance;
38	}
39
40
41	/**
42	* Initializes the Reporting class by registering actions and filters.
43	*
44	* @return void
45	* @since 2.4.1
46	*/
47	public function init() {
48	add_action( 'wpfunnels/offer_accepted', array( $this, 'update_offer_data' ), 10, 2 );
49	add_filter( 'wpfunnels/funnels-overview-data', array($this, 'get_upsell_data'), 10, 3 );
50	add_filter( 'wpfunnels/stat-interval-data', array($this, 'get_upsell_stat_data'), 10, 3 );
51	add_filter( 'wpfunnels/top-performing-funnels-data', array($this, 'get_conversion_data'), 10, 3 );
52	}
53
54
55	/**
56	* Update reporting data from order
57	*
58	* @param $order
59	* @param $offer_product
60	*
61	* @since 2.4.1
62	*/
63	public function update_offer_data( \WC_Order $order, $offer_product ) {
64	if ( !$order ) {
65	return;
66	}
67
68	global $wpdb;
69	$table = $wpdb->prefix . 'wpfnl_stats';
70	$step_id = $offer_product['step_id'];
71	$step_type = get_post_meta($step_id, '_step_type', true);
72	$column_key = 'upsell' === $step_type ? 'upsell_sales' : 'downsell_sales';
73	$offer_settings = Wpfnl_functions::get_offer_settings();
74
75	if ( $offer_settings['offer_orders'] == 'main-order' ) {
76	$order_id = $order->get_id();
77	$status = $order->get_status();
78	} else {
79	$order_id = $order->get_meta('_wpfunnels_offer_parent_id');
80	$status = $order->get_status();
81
82	}
83	$paid_date_time = current_time( 'mysql' );
84
85	$offer_total = $this->get_offer_sales( $order, $offer_product );
86	$total = $wpdb->get_var( $wpdb->prepare("SELECT total_sales FROM {$table} WHERE order_id = %d", $order_id ));
87
88	$wpdb->update(
89	$table,
90	array(
91	$column_key => $offer_total,
92	'total_sales' => round(floatval( $total ) + $offer_total, 2 ),
93	'status' => $order->get_status(),
94	'paid_date' => $paid_date_time,
95	),
96	array(
97	'order_id' => $order_id,
98	)
99	);
100	}
101
102
103	/**
104	* Get offer total sales data
105	*
106	* @param $order
107	* @param $offer_product
108	* @return int\|string\|null
109	*
110	* @since 2.4.1
111	*/
112	public function get_offer_sales ( $order, $offer_product ) {
113	global $wpdb;
114	$table = $wpdb->prefix . 'wpfnl_stats';
115	$step_id = $offer_product['step_id'];
116	$step_type = get_post_meta($step_id, '_step_type', true);
117	$offer_product_data = 'upsell' === $step_type ? get_post_meta($step_id, '_wpfnl_upsell_products', true) : get_post_meta($step_id, '_wpfnl_downsell_products', true);
118	$column_key = 'upsell' === $step_type ? 'upsell_sales' : 'downsell_sales';
119	$offer_products = array();
120	$total = 0;
121
122	foreach ( $offer_product_data as $data ) {
123	$offer_products[] = $data['id'];
124	}
125
126	foreach ( $order->get_items() as $item ) {
127	$product_id = $item->get_product_id();
128
129	// If the product is a variation, get its variation ID
130	if ( $item->get_variation_id() ) {
131	$product_id = $item->get_variation_id();
132	}
133
134	if ( in_array( $product_id, $offer_products ) ) {
135	$total += $item->get_total();
136	}
137	}
138
139	$_total = $wpdb->get_var( $wpdb->prepare("SELECT %s FROM {$table} WHERE order_id = %s", $column_key, $order->get_id() ) );
140	$total += floatval($_total);
141	return round( $total, 2 );
142	}
143
144
145	/**
146	* Attach offer overview data with report data
147	*
148	* @param $result
149	* @param $start_date
150	* @param $end_date
151	* @return mixed
152	* @since 2.4.1
153	*/
154	public function get_upsell_data( $result, $start_date, $end_date ) {
155	$total_upsell_revenue = $this->get_total_offer_sales( $start_date, $end_date );
156	$result['total_upsell_revenue'] = floatval(number_format(floatval($total_upsell_revenue), 2, '.', '' ));
157	return $result;
158	}
159
160
161	/**
162	* Get offer sale data
163	*
164	* @param $start_date
165	* @param $end_date
166	* @return mixed
167	* @since 2.4.1
168	*/
169	public function get_total_offer_sales( $start_date, $end_date ) {
170	global $wpdb;
171	$table = $wpdb->prefix. 'wpfnl_stats' ;
172	$sql = "SELECT SUM(upsell_sales + downsell_sales) as offer_sales FROM $table";
173	$sql = $this->include_where_clause($sql);
174
175	$result = $wpdb->get_var( $wpdb->prepare( $sql, $start_date, $end_date ) );
176	return $result;
177	}
178
179
180	/**
181	* Get upsell stat datt
182	*
183	* @param $result
184	* @param $start_date
185	* @param $end_date
186	* @return mixed
187	*
188	* @since 2.4.1
189	*/
190	public function get_upsell_stat_data( $result, $start_date, $end_date ) {
191	$total_upsell_revenue = $this->get_total_offer_sales($start_date, $end_date);
192	$result['total_upsell_revenue'] = floatval(number_format(floatval($total_upsell_revenue), 2, '.', '' ));
193	return $result;
194	}
195
196
197	/**
198	* Get conversion data
199	*
200	* @param $funnel_data
201	* @param $funnel_id
202	* @return mixed
203	*
204	* @since 2.4.1
205	*/
206	public function get_conversion_data( $funnel_data, $start_date = null, $end_date = null ) {
207	foreach ( $funnel_data as $key => $data ) {
208	$funnel_id = $data['id'];
209	$views = $this->get_views($funnel_id, $start_date, $end_date);
210	$conversion = $this->get_conversion($funnel_id, $start_date, $end_date);
211	$conversion_rate = $this->get_conversion_rate( $views, $conversion );
212	$funnel_data[$key]['views'] = $views;
213	$funnel_data[$key]['conversion'] = $conversion;
214	$funnel_data[$key]['conversion_rate'] = $conversion_rate;
215	}
216	return $funnel_data;
217	}
218
219
220	/**
221	* Build a date range clause for a table's date_created column.
222	*
223	* Returns an empty clause when either bound is missing, so callers keep
224	* their previous unscoped behaviour instead of returning nothing.
225	*
226	* @param string $column Fully qualified date column.
227	* @param string $start_date
228	* @param string $end_date
229	* @return array{0:string,1:array} Clause and its bind parameters.
230	*
231	* @since 3.13.0
232	*/
233	private function get_date_clause( $column, $start_date, $end_date ) {
234	if ( empty( $start_date ) \|\| empty( $end_date ) ) {
235	return array( '', array() );
236	}
237	return array( " AND $column >= %s AND $column <= %s ", array( $start_date, $end_date ) );
238	}
239
240
241	/**
242	* Retrieves the views for a specific funnel.
243	*
244	* @param int $funnel_id The ID of the funnel.
245	* @return array An array containing the views for the specified funnel.
246	*
247	* @since 2.4.1
248	*/
249	public function get_views( $funnel_id, $start_date = null, $end_date = null ) {
250	global $wpdb;
251	$table = $wpdb->prefix.'wpfnl_analytics';
252
253	list( $date_clause, $date_params ) = $this->get_date_clause( 'date_created', $start_date, $end_date );
254
255	$sql = "SELECT count(id) as total from $table WHERE funnel_id=%d AND visitor_type='new'" . $date_clause;
256	$count = $wpdb->get_var($wpdb->prepare($sql, array_merge( array( $funnel_id ), $date_params )));
257	return $count;
258	}
259
260
261	/**
262	* Get total number of conversion
263	*
264	* @param $funnel_id
265	* @return mixed
266	*
267	* @since 2.4.1
268	*/
269	public function get_conversion($funnel_id, $start_date = null, $end_date = null) {
270	global $wpdb;
271	$table = $wpdb->prefix.'wpfnl_stats';
272
273	list( $date_clause, $date_params ) = $this->get_date_clause( 'o1.paid_date', $start_date, $end_date );
274
275	$sql = "SELECT COUNT(DISTINCT o1.order_id) AS total_conversion
276	FROM $table o1
277	LEFT JOIN $table o2 ON o1.parent_id = o2.order_id AND o1.funnel_id = o2.funnel_id
278	WHERE o2.order_id IS NULL
279	AND o1.status='completed'
280	AND o1.funnel_id='%d'" . $date_clause;
281	$count = $wpdb->get_var($wpdb->prepare($sql, array_merge( array( $funnel_id ), $date_params )));
282
283	if ( !$count ) {
284	$optin_entries_table = $wpdb->prefix.'wpfnl_optin_entries';
285
286	list( $optin_date_clause, $optin_date_params ) = $this->get_date_clause( 'date_created', $start_date, $end_date );
287
288	$optin_conversion_sql = "SELECT COUNT(DISTINCT email) AS total_distinct_emails FROM $optin_entries_table WHERE funnel_id = %d" . $optin_date_clause;
289	$count = $wpdb->get_var($wpdb->prepare($optin_conversion_sql, array_merge( array( $funnel_id ), $optin_date_params )));
290	}
291	return $count;
292	}
293
294
295	/**
296	* Get conversion rate
297	*
298	* @param $views
299	* @param $conversion
300	* @return float\|int
301	*
302	* @since 2.4.1
303	*/
304	public function get_conversion_rate( $views, $conversion ) {
305	if ( $views > 0 ) {
306	return round( $conversion / ( $views / 100 ), 2 );
307	} else {
308	return 0;
309	}
310	}
311
312
313	/**
314	* Include where clause
315	*
316	* @param $sql
317	* @return string
318	*
319	* @since 2.4.1
320	*/
321	public function include_where_clause( $sql ) {
322	return $sql." WHERE paid_date >= %s AND paid_date <= %s AND status = 'completed' ";
323	}
324	}