learnpress / inc / Databases / class-lp-statistics-db.php

class-lp-statistics-db.php in LearnPress – WordPress LMS Plugin for Create and Sell Online Courses 4.3.9, at inc/Databases/class-lp-statistics-db.php

1,043 lines 42.8 KB

Raw Download Zip

1	<?php
2	/**
3	* Class LP_Statistics_DB
4	*
5	* @author thimpress
6	* @since 4.2.6
7	*/
8
9	defined( 'ABSPATH' ) \|\| exit();
10
11	class LP_Statistics_DB extends LP_Database {
12	private static $_instance;
13
14	protected function __construct() {
15	parent::__construct();
16	}
17
18	public static function getInstance() {
19	if ( is_null( self::$_instance ) ) {
20	self::$_instance = new self();
21	}
22
23	return self::$_instance;
24	}
25
26	/**
27	* filter to get data for chart of a day.
28	* @param LP_Filter $filter
29	* @param string $time_field the column use to filter time
30	* @return LP_Filter
31	*/
32	public function chart_filter_date_group_by( LP_Filter $filter, string $time_field ) {
33	$filter->only_fields[] = "HOUR($time_field) as x_data_label";
34	$filter->group_by = "x_data_label";
35	return $filter;
36	}
37	/**
38	* filter to get data for chart of last some days. ex: last 7 days, last 30 days,...
39	* @param LP_Filter $filter
40	* @param string $time_field the column use to filter time
41	* @return LP_Filter
42	*/
43	public function chart_filter_previous_days_group_by( LP_Filter $filter, string $time_field ) {
44	$filter->only_fields[] = "CAST($time_field AS DATE) as x_data_label";
45	$filter->group_by = "x_data_label";
46	return $filter;
47	}
48	/**
49	* filter to get data for chart of a month
50	* @param LP_Filter $filter
51	* @param string $time_field the column use to filter time
52	* @return LP_Filter
53	*/
54	public function chart_filter_month_group_by( LP_Filter $filter, string $time_field ) {
55	$filter->only_fields[] = "DAY($time_field) as x_data_label";
56	$filter->group_by = "x_data_label";
57	return $filter;
58	}
59	/**
60	* filter to get data for chart of months. ex: last 3 months, 6 months, 9 months,...
61	* @param LP_Filter $filter
62	* @param string $time_field the column use to filter time
63	* @return LP_Filter
64	*/
65	public function chart_filter_previous_months_group_by( LP_Filter $filter, string $time_field ) {
66	$filter->only_fields[] = "DATE_FORMAT( $time_field , '%m-%Y') as x_data_label";
67	$filter->group_by = "x_data_label";
68	return $filter;
69	}
70	/**
71	* filter to get data for chart of a year
72	* @param LP_Filter $filter
73	* @param string $time_field the column use to filter time. ex: post_date with posts table, user_registered on users table
74	* @return LP_Filter
75	*/
76	public function chart_filter_year_group_by( LP_Filter $filter, string $time_field ) {
77	$filter->only_fields[] = "MONTH($time_field) as x_data_label";
78	$filter->group_by = "x_data_label";
79	return $filter;
80	}
81	/**
82	* filter to get data for chart of a custom date ranges
83	* @param LP_Filter $filter
84	* @param array $dates array of date range use to filer
85	* @param string $time_field the column use to filter time. ex: post_date with posts table, user_registered on users table
86	* @return LP_Filter
87	*/
88	public function chart_filter_custom_group_by( LP_Filter $filter, array $dates, string $time_field ) {
89	$diff1 = date_create( $dates[0] );
90	$diff2 = date_create( $dates[1] );
91	if ( ! $diff1 \|\| ! $diff2 ) {
92	throw new Exception( 'Custom filter date is invalid.', 'learnpress' );
93	}
94	$diff = date_diff( $diff1, $diff2, true );
95	$y = $diff->y;
96	$m = $diff->m;
97	$d = $diff->d;
98	if ( $y < 1 ) {
99	if ( $m <= 1 ) {
100	if ( $d < 1 ) {
101	$filter = $this->chart_filter_date_group_by( $filter, $time_field );
102	} else {
103	// more thans 2 days return data of days
104	$filter = $this->chart_filter_previous_days_group_by( $filter, $time_field );
105	}
106	} else {
107	// more thans 2 months return data of months
108	$filter = $this->chart_filter_previous_months_group_by( $filter, $time_field );
109	}
110	} elseif ( $y < 2 ) {
111	// less thans 2 years return data of year months
112	$filter = $this->chart_filter_previous_months_group_by( $filter, $time_field );
113	} elseif ( $y < 5 ) {
114	// from 2-5years return data of year quarters
115	$filter->only_fields[] = $this->wpdb->prepare( "CONCAT( %s, QUARTER($time_field) ,%s, Year($time_field)) as x_data_label", [ 'q', '-' ] );
116	$filter->group_by = "x_data_label";
117	} else {
118	// more than 5 years, return data of years
119	$filter->only_fields[] = "YEAR($time_field) as x_data_label";
120	$filter->group_by = "x_data_label";
121	}
122	return $filter;
123	}
124	/**
125	* @param LP_Filter $filter
126	* @param string $date choose a date to query, format Y-m-d
127	* @param string $time_field $time_field the column use to filter time. ex: post_date with posts table, user_registered on
128	* @return LP_Filter
129	*/
130	public function date_filter( LP_Filter $filter, string $date, string $time_field, $is_until = false ) {
131	if ( $is_until ) {
132	$filter->where[] = $this->wpdb->prepare( "AND cast( $time_field as DATE)<= cast(%s as DATE)", $date );
133	} else {
134	$filter->where[] = $this->wpdb->prepare( "AND cast( $time_field as DATE)= cast(%s as DATE)", $date );
135	}
136	return $filter;
137	}
138	/**
139	* @param LP_Filter $filter
140	* @param int $value ex: 7 - last 7 days, 10 - last 10 days, ...
141	* @param string $time_field $time_field the column use to filter time. ex: post_date with posts table, user_registered on
142	* @return LP_Filter
143	*/
144	public function previous_days_filter( LP_Filter $filter, int $value, string $time_field, $is_until = false ) {
145	if ( $value < 2 ) {
146	throw new Exception( 'Day must be greater than 2 days.', 'learnpress' );
147	}
148	if ( $is_until ) {
149	$filter->where[] = "AND $time_field <= CURDATE()";
150	} else {
151	$filter->where[] = $this->wpdb->prepare( "AND $time_field >= DATE_ADD(CURDATE(), INTERVAL -%d DAY)", $value );
152	}
153
154	return $filter;
155	}
156	/**
157	* @param LP_Filter $filter
158	* @param string $date choose a date to query, format Y-m-d
159	* @param string $time_field $time_field the column use to filter time. ex: post_date with posts table, user_registered on
160	* @return LP_Filter
161	*/
162	public function month_filter( LP_Filter $filter, string $date, string $time_field, $is_until = false ) {
163	if ( $is_until ) {
164	$filter->where[] = $this->wpdb->prepare( "AND cast( $time_field as DATE)<= cast(%s as DATE)", $date );
165	} else {
166	$filter->where[] = $this->wpdb->prepare( "AND EXTRACT(YEAR_MONTH FROM $time_field)= EXTRACT(YEAR_MONTH FROM %s)", $date );
167	}
168	return $filter;
169	}
170	/**
171	* @param LP_Filter $filter
172	* @param int $value ex: 3 - last 3 months, 10 - last 10 months, ...
173	* @param string $time_field $time_field the column use to filter time. ex: post_date with posts table, user_registered on
174	* @return LP_Filter
175	*/
176	public function previous_months_filter( LP_Filter $filter, int $value, string $time_field, $is_until = false ) {
177	if ( $value < 2 ) {
178	throw new Exception( 'Values must be greater than 2 months.', 'learnpress' );
179	}
180	if ( $is_until ) {
181	$filter->where[] = "AND $time_field <= CURDATE()";
182	} else {
183	$filter->where[] = $this->wpdb->prepare( "AND EXTRACT(YEAR_MONTH FROM $time_field) >= EXTRACT(YEAR_MONTH FROM DATE_ADD(CURDATE(), INTERVAL -%d MONTH))", $value );
184	}
185	return $filter;
186	}
187	/**
188	* get data for each month in year
189	* @param LP_Filter $filter
190	* @param string $date choose a date to query, format Y-m-d
191	* @param string $time_field $time_field the column use to filter time. ex: post_date with posts table, user_registered on
192	* @return LP_Filter
193	*/
194	public function year_filter( LP_Filter $filter, string $date, string $time_field, $is_until = false ) {
195	if ( $is_until ) {
196	$filter->where[] = $this->wpdb->prepare( "AND cast( $time_field as DATE) <= cast(%s as DATE)", $date );
197	} else {
198	$filter->where[] = $this->wpdb->prepare( "AND YEAR($time_field)= YEAR(%s)", $date );
199	}
200	return $filter;
201	}
202	/**
203	* custom query with data range
204	* @param LP_Filter $filter
205	* @param array $dates date ranges, array of 2 dates.
206	* @param string $time_field $time_field the column use to filter time. ex: post_date with posts table, user_registered on
207	* @return LP_Filter
208	*/
209	public function custom_time_filter( LP_Filter $filter, array $dates, string $time_field, $is_until = false ) {
210	if ( empty( $dates ) ) {
211	throw new Exception( 'Select date', 'learnpress' );
212	}
213	sort( $dates );
214	if ( $is_until ) {
215	$filter->where[] = $this->wpdb->prepare( "AND cast( $time_field as DATE) <= cast(%s as DATE)", $dates[1] );
216	} else {
217	$filter->where[] = $this->wpdb->prepare(
218	"AND (DATE($time_field) BETWEEN %s AND %s)",
219	date( 'Y-m-d', strtotime( $dates[0] ) ),
220	date( 'Y-m-d', strtotime( $dates[1] ) )
221	);
222	}
223
224	return $filter;
225	}
226
227	/**
228	* choose filter type foreach filter time
229	* @param LP_Filter $filter
230	* @param string $type date\|month\|year\|previous_days\|custom
231	* @param string $time_field datetime colummn
232	* @param boolean $value value to query datetimes
233	* @param boolean $is_until filter time by the last date
234	* @return LP_Filter
235	*/
236	public function filter_time( LP_Filter $filter, string $type, string $time_field, $value = false, $is_until = false ) {
237	if ( ! $value ) {
238	throw new Exception( 'Empty statistic time', 'learnpress' );
239	}
240	switch ( $type ) {
241	case 'date':
242	$filter = $this->date_filter( $filter, $value, $time_field, $is_until );
243	break;
244	case 'month':
245	$filter = $this->month_filter( $filter, $value, $time_field, $is_until );
246	break;
247	case 'year':
248	$filter = $this->year_filter( $filter, $value, $time_field, $is_until );
249	break;
250	case 'previous_days':
251	$filter = $this->previous_days_filter( $filter, (int) $value, $time_field, $is_until );
252	break;
253	case 'previous_months':
254	$filter = $this->previous_months_filter( $filter, (int) $value, $time_field, $is_until );
255	break;
256	case 'custom':
257	$value = explode( '+', $value );
258	if ( count( $value ) !== 2 ) {
259	throw new Exception( 'Invalid custom time', 'learnpress' );
260	}
261	$filter = $this->custom_time_filter( $filter, $value, $time_field, $is_until );
262	default:
263	// code...
264	break;
265	}
266	return $filter;
267	}
268	/**
269	* format return data foreach type of filter
270	* @param LP_Filter $filter
271	* @param string $type date\|month\|year\|previous_days\|custom
272	* @param string $time_field datetime colummn
273	* @param boolean $value value to query datetimes
274	* @return LP_Filter
275	*/
276	public function chart_filter_group_by( LP_Filter $filter, string $type, string $time_field, $value = false ) {
277	switch ( $type ) {
278	case 'date':
279	$filter = $this->chart_filter_date_group_by( $filter, $time_field );
280	break;
281	case 'month':
282	$filter = $this->chart_filter_month_group_by( $filter, $time_field );
283	break;
284	case 'year':
285	$filter = $this->chart_filter_year_group_by( $filter, $time_field );
286	break;
287	case 'previous_days':
288	$filter = $this->chart_filter_previous_days_group_by( $filter, $time_field );
289	break;
290	case 'previous_months':
291	$filter = $this->chart_filter_previous_months_group_by( $filter, $time_field );
292	break;
293	case 'custom':
294	if ( empty( $value ) ) {
295	throw new Exception( 'Empty statistic time', 'learnpress' );
296	}
297	$value = explode( '+', $value );
298	if ( count( $value ) !== 2 ) {
299	throw new Exception( 'Invalid custom time', 'learnpress' );
300	}
301	$filter = $this->chart_filter_custom_group_by( $filter, $value, $time_field );
302	default:
303	// code...
304	break;
305	}
306	return $filter;
307	}
308
309	/**
310	* get_completed_order_data use this for complete order report chart
311	* @param string $type time type filter: date\|month\|year\|previous_days\|custom
312	* @param string $value time value string "Y-m-d" for date\|month\|year, int for previous_days, string "Y-m-d+Y-m-d" for custom
313	* @return array completed order data
314	*/
315	public function get_completed_order_data( string $type, string $value ) {
316	if ( ! $type \|\| ! $value ) {
317	return [];
318	}
319	$filter = new LP_Order_Filter();
320	$filter->collection = $this->tb_posts;
321	$filter->collection_alias = "p";
322	$time_field = "p.post_date";
323
324	// count completed orders
325	$filter->only_fields[] = "count( p.ID) as x_data";
326	$filter = $this->filter_time( $filter, $type, $time_field, $value );
327	$filter = $this->chart_filter_group_by( $filter, $type, $time_field, $value );
328	$filter->where[] = $this->wpdb->prepare( "AND p.post_status=%s", LP_ORDER_COMPLETED_DB );
329	$filter->where[] = $this->wpdb->prepare( "AND p.post_type=%s", $filter->post_type );
330	$filter->limit = -1;
331	$filter->order_by = $time_field;
332	$filter->order = "asc";
333
334	$filter->run_query_count = false;
335	$result = $this->execute( $filter );
336
337	return $result;
338	}
339
340	/**
341	* query to count LP Orders with all statuses
342	* @param LP_Order_Filter $filter
343	* @return LP_Order_Filter
344	*/
345	public function filter_order_count_statics( LP_Order_Filter $filter ) {
346	// $filter->query_count = true;
347	$filter->only_fields[] = "count( p.ID) as count_order";
348	$filter->only_fields[] = "REPLACE(p.post_status,'lp-','') as order_status";
349	$filter->group_by = "p.post_status";
350	$filter->where[] = $this->wpdb->prepare( "AND p.post_status LIKE CONCAT(%s,'%')", 'lp-' );
351	$filter->where[] = $this->wpdb->prepare( "AND p.post_type=%s", $filter->post_type );
352	$filter->run_query_count = false;
353
354	return $filter;
355	}
356	/**
357	* get LP Order count of a filter time
358	* @param string $type date\|month\|year\|previous_days\|custom
359	* @param string $value time value string "Y-m-d" for date\|month\|year, int for previous_days, string "Y-m-d+Y-m-d" for custom
360	* @return array result of LP Order count foreach status
361	*/
362	public function get_order_statics( string $type, string $value ) {
363	if ( ! $type \|\| ! $value ) {
364	return;
365	}
366	$filter = new LP_Order_Filter();
367	$filter->collection = $this->tb_posts;
368	$filter->collection_alias = "p";
369	$time_field = "p.post_date";
370	$filter = $this->filter_time( $filter, $type, $time_field, $value );
371	$filter = $this->filter_order_count_statics( $filter );
372	$filter->limit = -1;
373	$result = $this->execute( $filter );
374
375	return $result;
376	}
377	/Overviews statistics/
378	/**
379	* get sales amount of complete order
380	* @param string $type [time type filter: date\|month\|year\|previous_days\|custom]
381	* @param string $value [time value string "Y-m-d" for date\|month\|year, int for previous_days, string "Y-m-d+Y-m-d" for custom ]
382	* @return array completed order data
383	*/
384	public function get_net_sales_data( string $type, string $value ) {
385	if ( ! $type \|\| ! $value ) {
386	return [];
387	}
388	$filter = new LP_Order_Filter();
389	$filter->collection = $this->tb_posts;
390	$filter->collection_alias = "p";
391	$oi_table = $this->tb_lp_order_items;
392	$oim_table = $this->tb_lp_order_itemmeta;
393	// net sales summary
394	$filter->only_fields[] = "SUM(CAST(oim.meta_value AS DECIMAL(10,2))) as x_data";
395	$time_field = "p.post_date";
396	$filter->join = [
397	"INNER JOIN $oi_table AS oi ON p.ID = oi.order_id",
398	"INNER JOIN $oim_table AS oim ON oi.order_item_id = oim.learnpress_order_item_id",
399	];
400	$filter->limit = -1;
401	$filter->where = [
402	$this->wpdb->prepare( "AND p.post_type=%s", $filter->post_type ),
403	$this->wpdb->prepare( "AND p.post_status=%s", LP_ORDER_COMPLETED_DB ),
404	$this->wpdb->prepare( "AND oim.meta_key=%s", '_total' ),
405	];
406	$filter = $this->filter_time( $filter, $type, $time_field, $value );
407	$filter = $this->chart_filter_group_by( $filter, $type, $time_field, $value );
408	$filter->order_by = $time_field;
409	$filter->order = "asc";
410	$filter->run_query_count = false;
411
412	$result = $this->execute( $filter );
413	// error_log( $this->check_execute_has_error() );
414	return $result;
415	}
416
417	/**
418	* get top categories of sold course
419	* @param string $type date\|month\|year\|previous_days\|custom
420	* @param string $value time value string "Y-m-d" for date\|month\|year, int for previous_days, string "Y-m-d+Y-m-d" for custom
421	* @param integer $limit limit of query, default is 10
422	* @param boolean $exclude_free_course exclude free course
423	* @return array return term_id and term_count
424	*/
425	public function get_top_sold_categories( string $type, string $value, $limit = 0, $exclude_free_course = false ) {
426	if ( ! $type \|\| ! $value ) {
427	return;
428	}
429	$filter = new LP_Order_Filter();
430	$filter->collection = $this->tb_posts;
431	$filter->collection_alias = "p";
432	$filter->only_fields[] = "r_term.term_taxonomy_id as term_id";
433	$filter->only_fields[] = "SUM(CAST(oim_qty.meta_value AS UNSIGNED)) as term_count";
434	$filter->only_fields[] = "terms.name as term_name";
435	$filter->limit = $limit > 0 ? $limit : 10;
436	$time_field = "p.post_date";
437	$tb_term_relationships = $this->tb_term_relationships;
438	$tb_term_taxonomy = $this->tb_term_taxonomy;
439	$tb_terms = $this->tb_terms;
440	$oi_table = $this->tb_lp_order_items;
441	$oim_table = $this->tb_lp_order_itemmeta;
442
443	$filter->join = [
444	"INNER JOIN $oi_table AS oi ON p.ID = oi.order_id",
445	"INNER JOIN $tb_term_relationships AS r_term ON oi.item_id = r_term.object_id",
446	"INNER JOIN $tb_term_taxonomy AS tax_term ON tax_term.term_taxonomy_id = r_term.term_taxonomy_id",
447	"INNER JOIN $tb_terms AS terms ON terms.term_id = r_term.term_taxonomy_id",
448	"INNER JOIN $oim_table AS oim_qty ON oi.order_item_id = oim_qty.learnpress_order_item_id AND oim_qty.meta_key = '_quantity'",
449	];
450	if ( $exclude_free_course ) {
451	$filter->join[] = "INNER JOIN $oim_table AS oim_total ON oi.order_item_id = oim_total.learnpress_order_item_id AND oim_total.meta_key = '_total' AND CAST(oim_total.meta_value AS DECIMAL(10,2)) > 0";
452	}
453
454	$filter->where = array(
455	$this->wpdb->prepare( "AND p.post_type=%s", $filter->post_type ),
456	$this->wpdb->prepare( "AND p.post_status=%s", LP_ORDER_COMPLETED_DB ),
457	$this->wpdb->prepare( "AND oi.item_type=%s", LP_COURSE_CPT ),
458	$this->wpdb->prepare( "AND tax_term.taxonomy=%s", LP_COURSE_CATEGORY_TAX ),
459	);
460	$filter = $this->filter_time( $filter, $type, $time_field, $value );
461	$filter->group_by = "term_id";
462	$filter->order_by = "term_count";
463	$filter->order = "DESC";
464	$filter->run_query_count = false;
465	$result = $this->execute( $filter );
466
467	return $result;
468	}
469
470	/**
471	* get top courses was sold in the filter
472	* @param string $type date\|month\|year\|previous_days\|custom
473	* @param string $value time value string "Y-m-d" for date\|month\|year, int for previous_days, string "Y-m-d+Y-m-d" for custom
474	* @param integer $limit limit of query, default 10
475	* @param boolean $exclude_free_course exclude free course, get result only purchase course
476	* @return array $result
477	*/
478	public function get_top_sold_courses( string $type, string $value, $limit = 0, $exclude_free_course = false ) {
479	if ( ! $type \|\| ! $value ) {
480	return;
481	}
482	$filter = new LP_Order_Filter();
483	$tb_posts = $this->tb_posts;
484	$filter->collection = $tb_posts;
485	$filter->collection_alias = "p";
486	$filter->only_fields[] = "oi.item_id as course_id";
487	$filter->only_fields[] = "SUM(CAST(oim_qty.meta_value AS UNSIGNED)) as course_count";
488	$filter->only_fields[] = "p2.post_title as course_name";
489	$filter->limit = $limit > 0 ? $limit : 10;
490	$time_field = "p.post_date";
491	$oi_table = $this->tb_lp_order_items;
492	$oim_table = $this->tb_lp_order_itemmeta;
493
494	$filter->join = [
495	"INNER JOIN $oi_table AS oi ON p.ID = oi.order_id",
496	"INNER JOIN $tb_posts AS p2 ON p2.ID = oi.item_id",
497	"INNER JOIN $oim_table AS oim_qty ON oi.order_item_id = oim_qty.learnpress_order_item_id AND oim_qty.meta_key = '_quantity'",
498	];
499
500	if ( $exclude_free_course ) {
501	$filter->join[] = "INNER JOIN $oim_table AS oim_total ON oi.order_item_id = oim_total.learnpress_order_item_id AND oim_total.meta_key = '_total' AND CAST(oim_total.meta_value AS DECIMAL(10,2)) > 0";
502	}
503
504	$filter->where = array(
505	$this->wpdb->prepare( "AND p.post_type=%s", $filter->post_type ),
506	$this->wpdb->prepare( "AND p.post_status=%s", LP_ORDER_COMPLETED_DB ),
507	$this->wpdb->prepare( "AND oi.item_type=%s", LP_COURSE_CPT ),
508	);
509
510	$filter = $this->filter_time( $filter, $type, $time_field, $value );
511	$filter->group_by = "course_id";
512	$filter->order_by = "course_count";
513	$filter->order = "DESC";
514	$filter->run_query_count = false;
515	$result = $this->execute( $filter );
516
517	return $result;
518	}
519	/**
520	* Overviews get total courses was created ( all statuses )
521	* @param string $type date\|month\|year\|previous_days\|custom
522	* @param string $value time value string "Y-m-d" for date\|month\|year, int for previous_days, string
523	* @return int $result course count
524	*/
525	public function get_total_course_created( string $type, string $value ) {
526	if ( ! $type \|\| ! $value ) {
527	return;
528	}
529	$filter = new LP_Course_Filter();
530	$filter->collection = $this->tb_posts;
531	$filter->collection_alias = "p";
532	$filter->only_fields[] = "p.ID";
533	$time_field = "p.post_date";
534
535	$filter->where[] = $this->wpdb->prepare( "AND p.post_type=%s", LP_COURSE_CPT );
536	$filter = $this->filter_time( $filter, $type, $time_field, $value );
537	$filter->query_count = true;
538	$result = $this->execute( $filter );
539	return $result;
540	}
541	/**
542	* Overviews get total orders was created ( all statuses )
543	* @param string $type date\|month\|year\|previous_days\|custom
544	* @param string $value time value string "Y-m-d" for date\|month\|year, int for previous_days, string
545	* @return int $result order count
546	*/
547	public function get_total_order_created( string $type, string $value ) {
548	if ( ! $type \|\| ! $value ) {
549	return;
550	}
551	$filter = new LP_Course_Filter();
552	$filter->collection = $this->tb_posts;
553	$filter->collection_alias = "p";
554	$filter->only_fields[] = "p.ID";
555	$time_field = "p.post_date";
556
557	$filter->where[] = $this->wpdb->prepare( "AND p.post_type = %s", LP_ORDER_CPT );
558	$filter->where[] = $this->wpdb->prepare( "AND p.post_status != %s", 'auto-draft' );
559	$filter = $this->filter_time( $filter, $type, $time_field, $value );
560	$filter->query_count = true;
561	$result = $this->execute( $filter );
562	return $result;
563	}
564	/**
565	* Overviews get total instructors was created ( administrator and lp_teacher )
566	* @param string $type date\|month\|year\|previous_days\|custom
567	* @param string $value time value string "Y-m-d" for date\|month\|year, int for previous_days, string
568	* @return int $result user count
569	*/
570	public function get_total_instructor_created( string $type, string $value ) {
571	if ( ! $type \|\| ! $value ) {
572	return;
573	}
574	$filter = new LP_Filter();
575	$filter->collection = $this->wpdb->users;
576	$filter->collection_alias = "u";
577	$filter->only_fields[] = "u.ID";
578	$usermeta_table = $this->wpdb->usermeta;
579	$filter->join[] = "INNER JOIN $usermeta_table AS um ON um.user_id = u.ID";
580	$time_field = "u.user_registered";
581	$filter->where[] = $this->wpdb->prepare( "AND um.meta_key=%s", $this->wpdb->prefix . 'capabilities' );
582	$filter->where[] = $this->wpdb->prepare( "AND um.meta_value LIKE %s", '%' . ADMIN_ROLE . '%' );
583	$filter->where[] = $this->wpdb->prepare( "OR um.meta_value LIKE %s", '%' . LP_TEACHER_ROLE . '%' );
584	$filter = $this->filter_time( $filter, $type, $time_field, $value, true );
585	$filter->query_count = true;
586	$result = $this->execute( $filter );
587	return $result;
588	}
589	/**
590	* Overviews get total student was created ( subscriber )
591	* @param string $type date\|month\|year\|previous_days\|custom
592	* @param string $value time value string "Y-m-d" for date\|month\|year, int for previous_days, string
593	* @return int $result user count
594	*/
595	public function get_total_student_created( string $type, string $value ) {
596	if ( ! $type \|\| ! $value ) {
597	return;
598	}
599	$filter = new LP_Filter();
600	$filter->collection = $this->wpdb->users;
601	$filter->collection_alias = "u";
602	$filter->only_fields[] = "u.ID";
603	$usermeta_table = $this->wpdb->usermeta;
604	$filter->join[] = "INNER JOIN $usermeta_table AS um ON um.user_id = u.ID";
605	$time_field = "u.user_registered";
606	$filter->where[] = $this->wpdb->prepare( "AND um.meta_key=%s", $this->wpdb->prefix . 'capabilities' );
607	$filter->where[] = $this->wpdb->prepare( "AND um.meta_value LIKE %s", '%subscriber%' );
608	$filter = $this->filter_time( $filter, $type, $time_field, $value, true );
609	$filter->query_count = true;
610	$result = $this->execute( $filter );
611	return $result;
612	}
613	/Course statistics/
614	/**
615	* Gets the published course data.
616	*
617	* @param string $type date\|month\|year\|previous_days\|custom
618	* @param string $value time value string "Y-m-d" for date\|month\|year, int for previous_days, string "Y-m-d+Y-m-d" for custom
619	*
620	* @return array The published course data.
621	*/
622	public function get_published_course_data( string $type, string $value ) {
623	if ( ! $type \|\| ! $value ) {
624	return [];
625	}
626	$filter = new LP_Course_Filter();
627	$filter->collection = $this->tb_posts;
628	$filter->collection_alias = "p";
629	$time_field = "p.post_date";
630	// count published course
631	$filter->only_fields[] = "count( p.ID) as x_data";
632	$filter = $this->filter_time( $filter, $type, $time_field, $value );
633	$filter = $this->chart_filter_group_by( $filter, $type, $time_field, $value );
634	$filter->where[] = $this->wpdb->prepare( "AND p.post_status=%s", 'publish' );
635	$filter->where[] = $this->wpdb->prepare( "AND p.post_type=%s", $filter->post_type );
636	$filter->limit = -1;
637	$filter->order_by = $time_field;
638	$filter->order = 'asc';
639
640	$filter->run_query_count = false;
641	$result = $this->execute( $filter );
642
643	return $result;
644	}
645	/**
646	* Gets the course count by statuses.
647	*
648	* @param string $type date\|month\|year\|previous_days\|custom
649	* @param string $value time value string "Y-m-d" for date\|month\|year, int for previous_days, string "Y-m-d+Y-m-d" for custom
650	*
651	* @return array $result The course count by statuses.
652	*/
653	public function get_course_count_by_statuses( string $type, string $value ) {
654	if ( ! $type \|\| ! $value ) {
655	return [];
656	}
657	$filter = new LP_Course_Filter();
658	$filter->collection = $this->tb_posts;
659	$filter->collection_alias = "p";
660	$filter->only_fields[] = "COUNT(p.ID) as course_count";
661	$filter->only_fields[] = "p.post_status as course_status";
662	$time_field = "p.post_date";
663	$filter = $this->filter_time( $filter, $type, $time_field, $value );
664	$filter->where[] = $this->wpdb->prepare( "AND p.post_type=%s", $filter->post_type );
665	$filter->where[] = $this->wpdb->prepare( 'AND p.post_status IN (%s, %s, %s)', 'publish', 'pending', 'future' );
666	$filter->limit = -1;
667	$filter->group_by = 'p.post_status';
668	$filter->run_query_count = false;
669	$result = $this->execute( $filter );
670	return $result;
671	}
672	/**
673	* Gets the course items count.
674	*
675	* @param string $type date\|month\|year\|previous_days\|custom
676	* @param string $value time value string "Y-m-d" for date\|month\|year, int for previous_days, string "Y-m-d+Y-m-d" for custom
677	*
678	* @return int $result The course items count.
679	*/
680	public function get_course_items_count( string $type, string $value ) {
681	if ( ! $type \|\| ! $value ) {
682	return;
683	}
684	$filter = new LP_Filter();
685	$filter->collection = $this->tb_posts;
686	$filter->collection_alias = "p";
687	$filter->only_fields[] = "COUNT(p.ID) as item_count";
688	$filter->only_fields[] = "p.post_type as item_type";
689	$time_field = "p.post_date";
690	$filter = $this->filter_time( $filter, $type, $time_field, $value );
691	if ( class_exists( 'LP_Assignment' ) ) {
692	$filter->where[] = $this->wpdb->prepare( 'AND p.post_type IN (%s, %s, %s)', LP_LESSON_CPT, LP_QUIZ_CPT, LP_ASSIGNMENT_CPT );
693	} else {
694	$filter->where[] = $this->wpdb->prepare( 'AND p.post_type IN (%s, %s)', LP_LESSON_CPT, LP_QUIZ_CPT );
695	}
696	$filter->where[] = $this->wpdb->prepare( 'AND p.post_status IN(%s,%s,%s)', 'publish', 'pending', 'future' );
697	$filter->group_by = 'p.post_type';
698	$filter->limit = -1;
699	$filter->run_query_count = false;
700	$result = $this->execute( $filter );
701	return $result;
702	}
703	/User Statistics/
704	/**
705	* Gets the user registered data.
706	*
707	* @param string $type date\|month\|year\|previous_days\|custom
708	* @param string $value time value string "Y-m-d" for date\|month\|year, int for previous_days, string "Y-m-d+Y-m-d" for custom
709	*
710	* @return array $result The user registered data.
711	*/
712	public function get_user_registered_data( string $type, string $value ) {
713	if ( ! $type \|\| ! $value ) {
714	return [];
715	}
716	$filter = new LP_Filter();
717	$filter->collection = $this->tb_users;
718	$filter->collection_alias = "u";
719	$time_field = "u.user_registered";
720	// count user_registered
721	$filter->only_fields[] = "count( u.ID) as x_data";
722	$filter = $this->filter_time( $filter, $type, $time_field, $value );
723	$filter = $this->chart_filter_group_by( $filter, $type, $time_field, $value );
724	$filter->limit = -1;
725	$filter->order_by = $time_field;
726	$filter->order = "asc";
727
728	$filter->run_query_count = false;
729	$result = $this->execute( $filter );
730	return $result;
731	}
732
733	/**
734	* Gets the users by user item graduation statuses.
735	* @param string $type date\|month\|year\|previous_days\|custom
736	* @param string $value time value string "Y-m-d" for date\|month\|year, int for previous_days, string "Y-m-d+Y-m-d" for custom
737	* @return int $result count users by graduation statuses.
738	*/
739	public function get_users_by_user_item_graduation_statuses( string $type, string $value ) {
740	if ( ! $type \|\| ! $value ) {
741	return;
742	}
743	$filter = new LP_Filter();
744	$filter->collection = $this->tb_lp_user_items;
745	$filter->collection_alias = "ui";
746	$filter->only_fields[] = "ui.graduation as graduation_status";
747	$filter->only_fields[] = "COUNT(distinct(ui.user_id)) as user_count";
748	$time_field = "ui.start_time";
749	$filter->limit = -1;
750	$filter->where[] = $this->wpdb->prepare( "AND ui.item_type=%s", LP_COURSE_CPT );
751	$filter = $this->filter_time( $filter, $type, $time_field, $value );
752	$filter->group_by = "graduation_status";
753	$filter->run_query_count = false;
754	$result = $this->execute( $filter );
755	return $result;
756	}
757	/**
758	* filter user dont study any course in the filter time
759	* @param string $type date\|month\|year\|previous_days\|custom
760	* @param string $value time value string "Y-m-d" for date\|month\|year, int for previous_days, string "Y-m-d+Y-m-d" for custom
761	* @return int $result count users
762	*/
763	public function get_users_not_started_any_course( string $type, string $value ) {
764	if ( ! $type \|\| ! $value ) {
765	return;
766	}
767	$filter = new LP_Filter();
768	$table_useritems = $this->tb_lp_user_items;
769	$table_user = $this->tb_users;
770	$time_filter = $this->filter_time( $filter, $type, "ui.start_time", $value );
771	// get time_filter condition SQL
772	$time_condition = $time_filter->where[0];
773	// reset where
774	$filter->where = array();
775	$filter->collection = $table_user;
776	$filter->collection_alias = "u";
777	$filter->only_fields[] = "u.ID";
778	$filter->where[] = $this->wpdb->prepare( "AND NOT EXISTS (SELECT * FROM $table_useritems as ui WHERE ui.user_id = u.ID $time_condition)" );
779	$filter->limit = -1;
780	$filter->query_count = true;
781	// use this to see the sql query
782	// $filter->return_string_query= true;
783	$result = $this->execute( $filter );
784	return $result;
785	}
786	/**
787	* get top courses was enrolled by users
788	* @param string $type date\|month\|year\|previous_days\|custom
789	* @param string $value time value string "Y-m-d" for date\|month\|year, int for previous_days, string "Y-m-d+Y-m-d" for custom
790	* @param integer $limit limit of query, default 10
791	* @param boolean $exclude_free_course exclude free course, get result only purchase course
792	* @return array $result
793	*/
794	public function get_top_enrolled_courses( string $type, string $value, $limit = 0, $exclude_free_course = false ) {
795	if ( ! $type \|\| ! $value ) {
796	return;
797	}
798	$filter = new LP_Filter();
799	$filter->collection = $this->tb_lp_user_items;
800	$filter->collection_alias = "ui";
801	$filter->only_fields[] = "ui.item_id as course_id";
802	$filter->only_fields[] = "COUNT(ui.user_item_id) as enrolled_user";
803	$filter->only_fields[] = "p.post_author as instructor_id";
804	$filter->only_fields[] = "p.post_title as course_name";
805	$filter->only_fields[] = "u.display_name as instructor_name";
806	$filter->limit = ! $limit ? 10 : $limit;
807	$time_field = "ui.start_time";
808	$filter->join[] = "INNER JOIN $this->tb_posts AS p ON p.ID = ui.item_id";
809	$filter->join[] = "INNER JOIN $this->tb_users AS u ON u.ID = p.post_author";
810	$filter->where[] = $this->wpdb->prepare( "AND ui.item_type=%s", LP_COURSE_CPT );
811	$filter = $this->filter_time( $filter, $type, $time_field, $value );
812	$filter->group_by = "course_id";
813	$filter->order_by = "enrolled_user";
814	$filter->order = "DESC";
815	$filter->run_query_count = false;
816	$result = $this->execute( $filter );
817	return $result;
818	}
819
820	/**
821	* Get top sold courses by a specific instructor.
822	*
823	* @param int $instructor_id The instructor user ID.
824	* @param int $limit Number of courses to return, default 5.
825	*
826	* @return array Top sold courses for the instructor.
827	* @since 4.3.0
828	*/
829	public function get_top_sold_courses_by_instructor( int $instructor_id, int $limit = 5 ): array {
830	$tb_posts = $this->tb_posts;
831	$oi_table = $this->tb_lp_order_items;
832	$oim_table = $this->tb_lp_order_itemmeta;
833
834	$filter = new \LP_Order_Filter();
835	$filter->collection = $tb_posts;
836	$filter->collection_alias = 'p';
837	$filter->only_fields[] = 'oi.item_id as course_id';
838	$filter->only_fields[] = 'SUM(CAST(oim_qty.meta_value AS UNSIGNED)) as course_count';
839	$filter->only_fields[] = 'p2.post_title as course_name';
840	$filter->only_fields[] = 'u.display_name as instructor_name';
841	$filter->only_fields[] = 'SUM(CAST(oim_total.meta_value AS DECIMAL(10,2))) as total_revenue';
842	$filter->limit = $limit > 0 ? $limit : 5;
843
844	$filter->join = [
845	"INNER JOIN $oi_table AS oi ON p.ID = oi.order_id",
846	"INNER JOIN $tb_posts AS p2 ON p2.ID = oi.item_id",
847	"INNER JOIN {$this->tb_users} AS u ON u.ID = p2.post_author",
848	"INNER JOIN $oim_table AS oim_qty ON oi.order_item_id = oim_qty.learnpress_order_item_id AND oim_qty.meta_key = '_quantity'",
849	"INNER JOIN $oim_table AS oim_total ON oi.order_item_id = oim_total.learnpress_order_item_id AND oim_total.meta_key = '_total'",
850	];
851
852	$filter->where = array(
853	$this->wpdb->prepare( 'AND p.post_type=%s', $filter->post_type ),
854	$this->wpdb->prepare( 'AND p.post_status=%s', LP_ORDER_COMPLETED_DB ),
855	$this->wpdb->prepare( 'AND oi.item_type=%s', LP_COURSE_CPT ),
856	);
857
858	if ( $instructor_id > 0 ) {
859	$filter->where[] = $this->wpdb->prepare( 'AND p2.post_author=%d', $instructor_id );
860	}
861
862	$filter->group_by = 'course_id';
863	$filter->order_by = 'course_count';
864	$filter->order = 'DESC';
865	$filter->run_query_count = false;
866	$result = $this->execute( $filter );
867
868	return is_array( $result ) ? $result : [];
869	}
870
871	/**
872	* Get top enrolled courses by a specific instructor.
873	*
874	* @param int $instructor_id The instructor user ID.
875	* @param int $limit Number of courses to return, default 5.
876	*
877	* @return array Top enrolled courses for the instructor.
878	* @since 4.3.0
879	*/
880	public function get_top_enrolled_courses_by_instructor( int $instructor_id, int $limit = 5 ): array {
881	$filter = new \LP_Filter();
882	$filter->collection = $this->tb_lp_user_items;
883	$filter->collection_alias = 'ui';
884	$filter->only_fields[] = 'ui.item_id as course_id';
885	$filter->only_fields[] = 'COUNT(ui.user_item_id) as enrollment_count';
886	$filter->only_fields[] = 'p.post_title as course_name';
887	$filter->only_fields[] = 'u.display_name as instructor_name';
888	$filter->limit = $limit > 0 ? $limit : 5;
889
890	$filter->join[] = "INNER JOIN {$this->tb_posts} AS p ON p.ID = ui.item_id";
891	$filter->join[] = "INNER JOIN {$this->tb_users} AS u ON u.ID = p.post_author";
892
893	$filter->where[] = $this->wpdb->prepare( 'AND ui.item_type=%s', LP_COURSE_CPT );
894
895	if ( $instructor_id > 0 ) {
896	$filter->where[] = $this->wpdb->prepare( 'AND p.post_author=%d', $instructor_id );
897	}
898
899	$filter->group_by = 'course_id';
900	$filter->order_by = 'enrollment_count';
901	$filter->order = 'DESC';
902	$filter->run_query_count = false;
903	$result = $this->execute( $filter );
904
905	return is_array( $result ) ? $result : [];
906	}
907
908	/**
909	* Get top instructors by course count and student count.
910	*
911	* @param int $limit Number of instructors to return.
912	*
913	* @return array Top instructors data.
914	* @since 4.3.0
915	*/
916	public function get_top_instructors( int $limit = 4 ): array {
917	$tb_posts = $this->tb_posts;
918	$tb_users = $this->tb_users;
919	$tb_user_items = $this->tb_lp_user_items;
920	$tb_usermeta = $this->wpdb->usermeta;
921
922	$sql = $this->wpdb->prepare(
923	"SELECT u.ID as instructor_id,
924	u.display_name as instructor_name,
925	COUNT(DISTINCT p.ID) as course_count,
926	(SELECT COUNT(DISTINCT ui.user_id)
927	FROM {$tb_user_items} AS ui
928	WHERE ui.item_id IN (SELECT p2.ID FROM {$tb_posts} AS p2 WHERE p2.post_author = u.ID AND p2.post_type = %s AND p2.post_status = 'publish')
929	AND ui.item_type = %s
930	) as student_count
931	FROM {$tb_users} AS u
932	INNER JOIN {$tb_usermeta} AS um ON um.user_id = u.ID
933	INNER JOIN {$tb_posts} AS p ON p.post_author = u.ID AND p.post_type = %s AND p.post_status = 'publish'
934	WHERE um.meta_key = %s
935	AND (um.meta_value LIKE %s OR um.meta_value LIKE %s)
936	GROUP BY u.ID
937	ORDER BY course_count DESC
938	LIMIT %d",
939	LP_COURSE_CPT,
940	LP_COURSE_CPT,
941	LP_COURSE_CPT,
942	$this->wpdb->prefix . 'capabilities',
943	'%administrator%',
944	'%' . LP_TEACHER_ROLE . '%',
945	$limit
946	);
947
948	$results = $this->wpdb->get_results( $sql );
949
950	return is_array( $results ) ? $results : [];
951	}
952
953	/**
954	* Get net sales chart data scoped by instructor.
955	*
956	* @param string $type Time filter type.
957	* @param string $value Time filter value.
958	* @param int $instructor_id Instructor ID (0 for all).
959	*
960	* @return array Net sales data.
961	* @since 4.3.0
962	*/
963	public function get_net_sales_data_scoped( string $type, string $value, int $instructor_id = 0 ): array {
964	if ( ! $type \|\| ! $value ) {
965	return [];
966	}
967
968	$filter = new \LP_Order_Filter();
969	$filter->collection = $this->tb_posts;
970	$filter->collection_alias = 'p';
971	$oi_table = $this->tb_lp_order_items;
972	$oim_table = $this->tb_lp_order_itemmeta;
973
974	$filter->only_fields[] = "SUM(CAST(oim.meta_value AS DECIMAL(10,2))) as x_data";
975	$time_field = "p.post_date";
976
977	$filter->join = [
978	"INNER JOIN $oi_table AS oi ON p.ID = oi.order_id",
979	"INNER JOIN $oim_table AS oim ON oi.order_item_id = oim.learnpress_order_item_id",
980	];
981
982	$filter->where = [
983	$this->wpdb->prepare( "AND p.post_type=%s", $filter->post_type ),
984	$this->wpdb->prepare( "AND p.post_status=%s", LP_ORDER_COMPLETED_DB ),
985	$this->wpdb->prepare( "AND oim.meta_key=%s", '_total' ),
986	];
987
988	if ( $instructor_id > 0 ) {
989	$filter->join[] = "INNER JOIN {$this->tb_posts} AS p2 ON p2.ID = oi.item_id";
990	$filter->where[] = $this->wpdb->prepare( "AND p2.post_author=%d", $instructor_id );
991	}
992
993	$filter->limit = -1;
994	$filter = $this->filter_time( $filter, $type, $time_field, $value );
995	$filter = $this->chart_filter_group_by( $filter, $type, $time_field, $value );
996	$filter->order_by = $time_field;
997	$filter->order = "asc";
998	$filter->run_query_count = false;
999
1000	$result = $this->execute( $filter );
1001	return is_array( $result ) ? $result : [];
1002	}
1003
1004	/**
1005	* Get enrollment chart data scoped by instructor.
1006	*
1007	* @param string $type Time filter type.
1008	* @param string $value Time filter value.
1009	* @param int $instructor_id Instructor ID (0 for all).
1010	*
1011	* @return array Enrollment chart data.
1012	* @since 4.3.0
1013	*/
1014	public function get_enrollment_chart_data( string $type, string $value, int $instructor_id = 0 ): array {
1015	if ( ! $type \|\| ! $value ) {
1016	return [];
1017	}
1018
1019	$filter = new \LP_Filter();
1020	$filter->collection = $this->tb_lp_user_items;
1021	$filter->collection_alias = 'ui';
1022	$time_field = 'ui.start_time';
1023
1024	$filter->only_fields[] = 'COUNT(ui.user_item_id) as x_data';
1025	$filter->where[] = $this->wpdb->prepare( 'AND ui.item_type=%s', LP_COURSE_CPT );
1026
1027	if ( $instructor_id > 0 ) {
1028	$filter->join[] = "INNER JOIN {$this->tb_posts} AS p ON p.ID = ui.item_id";
1029	$filter->where[] = $this->wpdb->prepare( 'AND p.post_author=%d', $instructor_id );
1030	}
1031
1032	$filter->limit = -1;
1033	$filter = $this->filter_time( $filter, $type, $time_field, $value );
1034	$filter = $this->chart_filter_group_by( $filter, $type, $time_field, $value );
1035	$filter->order_by = $time_field;
1036	$filter->order = 'asc';
1037	$filter->run_query_count = false;
1038
1039	$result = $this->execute( $filter );
1040	return is_array( $result ) ? $result : [];
1041	}
1042	}
1043