array_sum( (array) $counts ), 'published' => (int) ( $counts->publish ?? 0 ), ); } /** * STUDENTS (ENROLLED / ACTIVE / COMPLETED) */ public static function get_student_stats() { global $wpdb; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table, no WP API alternative. $enrolled = (int) $wpdb->get_var( " SELECT COUNT(DISTINCT user_id) FROM {$wpdb->prefix}aimslms_course_enrollments " ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table, no WP API alternative. $active = (int) $wpdb->get_var( " SELECT COUNT(DISTINCT user_id) FROM {$wpdb->prefix}aimslms_course_enrollments WHERE status = 'active' " ); $completed = (int) self::get_completed_students_count(); return array( 'enrolled' => $enrolled, 'active' => $active, 'completed' => $completed, ); } /** * COMPLETED STUDENTS COUNT * A student is completed if ANY enrolled course is completed */ private static function get_completed_students_count() { global $wpdb; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table, no WP API alternative. return (int) $wpdb->get_var( " SELECT COUNT(DISTINCT p.user_id) FROM {$wpdb->prefix}aimslms_course_progress p INNER JOIN {$wpdb->prefix}aimslms_course_lessons l ON l.id = p.lesson_id GROUP BY p.user_id, l.course_id HAVING SUM(p.completed = 1) = COUNT(l.id) " ); } /** * COURSE COMPLETION STATS * Used for charts + % calculation */ public static function get_course_completion_stats() { global $wpdb; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table, no WP API alternative. $results = $wpdb->get_results( " SELECT l.course_id, COUNT(DISTINCT p.user_id) AS completed_students FROM {$wpdb->prefix}aimslms_course_lessons l INNER JOIN {$wpdb->prefix}aimslms_course_progress p ON p.lesson_id = l.id AND p.completed = 1 GROUP BY l.course_id, p.user_id HAVING COUNT(l.id) = ( SELECT COUNT(*) FROM {$wpdb->prefix}aimslms_course_lessons WHERE course_id = l.course_id ) " ); $completed_by_course = array(); foreach ( $results as $row ) { $completed_by_course[ $row->course_id ][] = $row->completed_students; } return $completed_by_course; } /** * ENROLLED vs COMPLETED (CHART DATA) */ public static function get_enrolled_vs_completed_stats() { global $wpdb; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table, no WP API alternative. $enrolled = (int) $wpdb->get_var( " SELECT COUNT(*) FROM {$wpdb->prefix}aimslms_course_enrollments " ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table, no WP API alternative. $completed = (int) $wpdb->get_var( " SELECT COUNT(*) FROM ( SELECT p.user_id, l.course_id FROM {$wpdb->prefix}aimslms_course_progress p INNER JOIN {$wpdb->prefix}aimslms_course_lessons l ON l.id = p.lesson_id GROUP BY p.user_id, l.course_id HAVING SUM(p.completed = 1) = COUNT(l.id) ) completed_courses " ); return array( 'enrolled' => $enrolled, 'completed' => $completed, ); } /** * Get revenue chart data (labels and totals) for the requested range, cached via transient. * * @param string $range Chart range, either 'monthly' or 'daily'. * @return array Array with 'labels' and 'values' keys. */ public static function get_revenue_chart_data( $range = 'daily' ) { global $wpdb; $cache_key = 'aimslms_revenue_' . $range; $cached = get_transient( $cache_key ); if ( false !== $cached ) { return $cached; } if ( 'monthly' === $range ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table, no WP API alternative. $rows = $wpdb->get_results( " SELECT DATE_FORMAT(transaction_date,'%Y-%m') label, SUM(defult_course_price) total FROM {$wpdb->prefix}aimslms_course_payment WHERE status='completed' GROUP BY label ORDER BY label ASC LIMIT 12 " ); } else { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table, no WP API alternative. $rows = $wpdb->get_results( " SELECT DATE(transaction_date) label, SUM(defult_course_price) total FROM {$wpdb->prefix}aimslms_course_payment WHERE status='completed' AND transaction_date >= DATE_SUB(CURDATE(), INTERVAL 14 DAY) GROUP BY label ORDER BY label ASC " ); } $labels = array(); $values = array(); foreach ( $rows as $row ) { $labels[] = $row->label; $values[] = (float) $row->total; } $data = array( 'labels' => $labels, 'values' => $values, ); set_transient( $cache_key, $data, 10 * MINUTE_IN_SECONDS ); return $data; } /** * REVENUE */ public static function get_revenue_stats() { global $wpdb; return array( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table, no WP API alternative. 'total' => (float) $wpdb->get_var( " SELECT SUM(defult_course_price) FROM {$wpdb->prefix}aimslms_course_payment WHERE status = 'completed' " ), 'today' => (float) $wpdb->get_var( " SELECT SUM(defult_course_price) FROM {$wpdb->prefix}aimslms_course_payment WHERE status = 'completed' AND DATE(transaction_date) = CURDATE() " ), 'month' => (float) $wpdb->get_var( " SELECT SUM(defult_course_price) FROM {$wpdb->prefix}aimslms_course_payment WHERE status = 'completed' AND YEAR(transaction_date) = YEAR(CURDATE()) AND MONTH(transaction_date) = MONTH(CURDATE()) " ), ); } /** * ORDERS */ public static function get_order_stats() { global $wpdb; return array( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table, no WP API alternative. 'total' => (int) $wpdb->get_var( " SELECT COUNT(*) FROM {$wpdb->prefix}aimslms_course_orders WHERE order_status != 'trash' " ), 'completed' => (int) $wpdb->get_var( " SELECT COUNT(*) FROM {$wpdb->prefix}aimslms_course_orders WHERE order_status = 'completed' " ), 'pending' => (int) $wpdb->get_var( " SELECT COUNT(*) FROM {$wpdb->prefix}aimslms_course_orders WHERE order_status = 'pending' " ), ); } /** * TOP SELLING COURSES * * @param int $limit Maximum number of courses to return. */ public static function get_top_courses( $limit = 5 ) { global $wpdb; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table, no WP API alternative. return $wpdb->get_results( $wpdb->prepare( " SELECT p.course_id, COUNT(*) AS total_sales, SUM(p.defult_course_price) AS revenue, post.post_title FROM {$wpdb->prefix}aimslms_course_payment p INNER JOIN {$wpdb->posts} post ON post.ID = p.course_id WHERE p.status = 'completed' GROUP BY p.course_id ORDER BY total_sales DESC LIMIT %d ", $limit ) ); } /** * RECENT PAYMENTS * * @param int $limit Maximum number of payments to return. */ public static function get_recent_payments( $limit = 10 ) { global $wpdb; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table, no WP API alternative. return $wpdb->get_results( $wpdb->prepare( " SELECT p.amount, p.currency, p.payment_gateway, p.payment_brand, p.transaction_id, p.transaction_date, u.display_name, post.post_title FROM {$wpdb->prefix}aimslms_course_payment p LEFT JOIN {$wpdb->users} u ON u.ID = p.user_id LEFT JOIN {$wpdb->posts} post ON post.ID = p.course_id WHERE p.status != 'trash' ORDER BY p.transaction_date DESC LIMIT %d ", $limit ) ); } }