wpfunnels / includes / core / AdvancedReporting / Reporting.php

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

294 lines 8.8 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 );
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 ) {
207	foreach ( $funnel_data as $key => $data ) {
208	$funnel_id = $data['id'];
209	$views = $this->get_views($funnel_id);
210	$conversion = $this->get_conversion($funnel_id);
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	* Retrieves the views for a specific funnel.
222	*
223	* @param int $funnel_id The ID of the funnel.
224	* @return array An array containing the views for the specified funnel.
225	*
226	* @since 2.4.1
227	*/
228	public function get_views( $funnel_id ) {
229	global $wpdb;
230	$table = $wpdb->prefix.'wpfnl_analytics';
231	$sql = "SELECT count(id) as total from $table WHERE funnel_id=%d AND visitor_type='new'";
232	$count = $wpdb->get_var($wpdb->prepare($sql, $funnel_id));
233	return $count;
234	}
235
236
237	/**
238	* Get total number of conversion
239	*
240	* @param $funnel_id
241	* @return mixed
242	*
243	* @since 2.4.1
244	*/
245	public function get_conversion($funnel_id) {
246	global $wpdb;
247	$table = $wpdb->prefix.'wpfnl_stats';
248	$sql = "SELECT COUNT(DISTINCT o1.order_id) AS total_conversion
249	FROM $table o1
250	LEFT JOIN $table o2 ON o1.parent_id = o2.order_id AND o1.funnel_id = o2.funnel_id
251	WHERE o2.order_id IS NULL
252	AND o1.status='completed'
253	AND o1.funnel_id='%d'";
254	$count = $wpdb->get_var($wpdb->prepare($sql, $funnel_id));
255
256	if ( !$count ) {
257	$optin_entries_table = $wpdb->prefix.'wpfnl_optin_entries';
258	$optin_conversion_sql = "SELECT COUNT(DISTINCT email) AS total_distinct_emails FROM $optin_entries_table WHERE funnel_id = %d;";
259	$count = $wpdb->get_var($wpdb->prepare($optin_conversion_sql, $funnel_id));
260	}
261	return $count;
262	}
263
264
265	/**
266	* Get conversion rate
267	*
268	* @param $views
269	* @param $conversion
270	* @return float\|int
271	*
272	* @since 2.4.1
273	*/
274	public function get_conversion_rate( $views, $conversion ) {
275	if ( $views > 0 ) {
276	return round( $conversion / ( $views / 100 ), 2 );
277	} else {
278	return 0;
279	}
280	}
281
282
283	/**
284	* Include where clause
285	*
286	* @param $sql
287	* @return string
288	*
289	* @since 2.4.1
290	*/
291	public function include_where_clause( $sql ) {
292	return $sql." WHERE paid_date >= %s AND paid_date <= %s AND status = 'completed' ";
293	}
294	}