prefix . 'aimslms_course_sections'; $lessons_table = $wpdb->prefix . 'aimslms_course_lessons'; // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- $sections_table/$lessons_table are derived from $wpdb->prefix, not user input; custom table, no WP API alternative. return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(l.id) FROM {$lessons_table} l INNER JOIN {$sections_table} s ON s.id = l.section_id WHERE s.course_id = %d", $course_id ) ); // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching } /** * Get completed lessons count for user. * * @param int $user_id User ID. * @param int $course_id Course post ID. * @return int Number of completed lessons. */ function aimslms_get_completed_lessons( $user_id, $course_id ) { global $wpdb; $progress_table = $wpdb->prefix . 'aimslms_course_progress'; // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- $progress_table is derived from $wpdb->prefix, not user input; custom table, no WP API alternative. return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$progress_table} WHERE user_id = %d AND course_id = %d AND completed = 1", $user_id, $course_id ) ); // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching } /** * Check whether a lesson is completed for the current user. * * @param int $course_id Course post ID. * @param int $lesson_id Lesson ID. * @return int Count of matching completed progress rows (0 if not completed). */ function aimslms_check_lesson_status( $course_id, $lesson_id ) { global $wpdb; $user_id = get_current_user_id(); $progress_table = $wpdb->prefix . 'aimslms_course_progress'; // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- $progress_table is derived from $wpdb->prefix, not user input; custom table, no WP API alternative. return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$progress_table} WHERE user_id = %d AND course_id = %d AND lesson_id = %d AND completed = 1", $user_id, $course_id, $lesson_id ) ); // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching } /** * Get course progress data. * * @param int $course_id Course post ID. * @param int|null $user_id User ID. Defaults to the current user when null. * @return array Progress data with 'total', 'completed', 'percentage', and 'completed_at' keys. */ function aimslms_get_course_progress_data( $course_id, $user_id = null ) { global $wpdb; $user_id = $user_id ? $user_id : get_current_user_id(); $total_lessons = aimslms_get_total_lessons_by_course( $course_id ); $completed_lessons = aimslms_get_completed_lessons( $user_id, $course_id ); $percentage = $total_lessons > 0 ? round( ( $completed_lessons / $total_lessons ) * 100 ) : 0; $completed_at = ''; if ( $percentage >= 100 ) { $table = $wpdb->prefix . 'aimslms_course_progress'; // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- $table is derived from $wpdb->prefix, not user input; custom table, no WP API alternative. $row = $wpdb->get_row( $wpdb->prepare( " SELECT last_accessed FROM {$table} WHERE user_id = %d AND course_id = %d AND completed = 1 LIMIT 1 ", $user_id, $course_id ) ); // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching if ( $row && ! empty( $row->last_accessed ) ) { $completed_at = strtotime( $row->last_accessed ); } } return array( 'total' => $total_lessons, 'completed' => $completed_lessons, 'percentage' => $percentage, 'completed_at' => $completed_at, ); } /** * Get course lesson ordered. * * @param int $course_id Course post ID. * @return array List of lesson rows ordered by section and lesson sort order. */ function aimslms_get_course_lessons_ordered( $course_id ) { 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 l.* FROM {$wpdb->prefix}aimslms_course_lessons l INNER JOIN {$wpdb->prefix}aimslms_course_sections s ON s.id = l.section_id WHERE s.course_id = %d ORDER BY s.sort_order ASC, l.sort_order ASC", $course_id ) ); } /** * Get access lesson. * * @param int $user_id User ID. * @param int $course_id Course post ID. * @param int $lesson_id Lesson ID. * @return bool True if the lesson is unlocked for the user. */ function aimslms_is_lesson_unlocked( $user_id, $course_id, $lesson_id ) { global $wpdb; if ( ! aimslms_get_course_enrollment( $course_id, $user_id ) ) { return false; } $lessons = aimslms_get_course_lessons_ordered( $course_id ); if ( empty( $lessons ) ) { return false; } $lesson_ids = array_map( function ( $lesson ) { return (int) ( is_object( $lesson ) ? $lesson->id : $lesson['id'] ); }, $lessons ); $lesson_id = (int) $lesson_id; $index = array_search( $lesson_id, $lesson_ids, true ); if ( false === $index ) { return false; } if ( 0 === $index ) { return true; } $prev_lesson_id = $lesson_ids[ $index - 1 ] ?? 0; if ( ! $prev_lesson_id ) { return false; } // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table, no WP API alternative. $completed = $wpdb->get_var( $wpdb->prepare( "SELECT completed FROM {$wpdb->prefix}aimslms_course_progress WHERE user_id = %d AND lesson_id = %d AND course_id = %d", $user_id, $prev_lesson_id, $course_id ) ); return (bool) $completed; } /** * Get prev next lesson. * * @param int $course_id Course post ID. * @param int $lesson_id Lesson ID to find neighbours for. * @return array Two-element array with the previous and next lesson rows (or null if none). */ function aimslms_get_prev_next_lesson( $course_id, $lesson_id ) { $lessons = aimslms_get_course_lessons_ordered( $course_id ); $ids = array_map( 'intval', array_column( $lessons, 'id' ) ); $index = array_search( (int) $lesson_id, $ids, true ); return array( $lessons[ $index - 1 ] ?? null, $lessons[ $index + 1 ] ?? null, ); } /** * Check whether a course is fully completed by a user. * * @param int $course_id Course post ID. * @param int $user_id User ID. * @return bool True if all lessons in the course are completed. */ function aimslms_is_course_completed( $course_id, $user_id ) { $progress = aimslms_get_course_progress_data( $course_id, $user_id ); return ( $progress['total'] > 0 && $progress['completed'] >= $progress['total'] ); } /** * Get next unlocked lesson url. * * @param int $user_id User ID. * @param int $course_id Course post ID. * @return string|false URL of the next unlocked, not-yet-completed lesson, or false if none. */ function aimslms_get_next_unlocked_lesson_url( $user_id, $course_id ) { $lessons = aimslms_get_course_lessons_ordered( $course_id ); if ( empty( $lessons ) ) { return false; } foreach ( $lessons as $lesson ) { if ( aimslms_is_lesson_unlocked( $user_id, $course_id, $lesson->id ) ) { // Check if lesson is NOT completed. $completed = aimslms_check_lesson_status( $course_id, $lesson->id ); if ( ! $completed ) { return aimslms_get_lesson_url( $course_id, $lesson->id ); } } } return false; } /** * Get all enrolled course ids. * * @param int $user_id User ID. * @return array List of course IDs the user is enrolled in with a completed order. */ function aimslms_get_all_enrolled_course_ids( $user_id ) { global $wpdb; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table, no WP API alternative. return $wpdb->get_col( $wpdb->prepare( " SELECT DISTINCT e.course_id FROM {$wpdb->prefix}aimslms_course_enrollments e INNER JOIN {$wpdb->prefix}aimslms_course_orders o ON o.course_id = e.course_id AND o.user_id = e.user_id WHERE e.user_id = %d AND o.order_status = %s ", $user_id, 'completed' ) ); } /** * Get started course ids. * * @param int $user_id User ID. * @return array List of course IDs the user has started but not completed. */ function aimslms_get_started_course_ids( $user_id ) { $course_ids = aimslms_get_all_enrolled_course_ids( $user_id ); $started = array(); foreach ( $course_ids as $course_id ) { $progress = aimslms_get_course_progress_data( $course_id, $user_id ); if ( $progress['percentage'] > 0 && $progress['percentage'] < 100 ) { $started[] = $course_id; } } return $started; } /** * Get user course stats. * * @param int $user_id User ID. * @return array Stats with 'enrolled', 'started', and 'completed' counts. */ function aimslms_get_user_course_stats( $user_id ) { $enrolled_ids = aimslms_get_all_enrolled_course_ids( $user_id ); $stats = array( 'enrolled' => 0, 'started' => 0, 'completed' => 0, ); if ( empty( $enrolled_ids ) ) { return $stats; } $stats['enrolled'] = count( $enrolled_ids ); foreach ( $enrolled_ids as $course_id ) { $progress = aimslms_get_course_progress_data( $course_id, $user_id ); $percent = (int) ( $progress['percentage'] ?? 0 ); if ( $percent >= 100 ) { ++$stats['completed']; } elseif ( $percent > 0 ) { ++$stats['started']; } } return $stats; } /** * Get course dates. * * @param int $course_id Course post ID. * @param int $user_id User ID. * @return array Array with a 'started' key containing a formatted enrollment date or null. */ function aimslms_get_course_dates( $course_id, $user_id ) { global $wpdb; $table = $wpdb->prefix . 'aimslms_course_enrollments'; // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- $table is derived from $wpdb->prefix, not user input; custom table, no WP API alternative. $row = $wpdb->get_row( $wpdb->prepare( "SELECT enrollment_date, expiry_date, status FROM $table WHERE user_id = %d AND course_id = %d LIMIT 1", $user_id, $course_id ), ARRAY_A ); // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching return array( 'started' => ! empty( $row['enrollment_date'] ) ? date_i18n( get_option( 'date_format' ), strtotime( $row['enrollment_date'] ) ) : null, ); } /** * Get course enrolled users count. * * @param int $course_id Course post ID. * @return int Number of distinct users enrolled in the course. */ function aimslms_get_course_enrolled_users_count( $course_id ) { global $wpdb; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table, no WP API alternative. return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(DISTINCT user_id) FROM {$wpdb->prefix}aimslms_course_enrollments WHERE course_id = %d", $course_id ) ); }