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