PK�yZ�)uuadmin-ui.php<?php
add_action('admin_menu', 'mst_lms_register_scorm_progress_menu');

function mst_lms_register_scorm_progress_menu() {
    add_menu_page(
        'SCORM Progress',
        'SCORM Progress',
        'manage_options',
        'scorm-progress',
        'mst_lms_render_scorm_progress_page',
        'dashicons-welcome-learn-more',
        30
    );
}

function mst_lms_render_scorm_progress_page() {
    if (isset($_GET['download_excel']) && $_GET['download_excel'] == 1) {
        mst_lms_download_scorm_excel();
        exit;
    }

    global $wpdb;

    $scorm_table = $wpdb->prefix . 'stm_lms_user_course_scorm';
    $user_courses_table = $wpdb->prefix . 'stm_lms_user_courses';
    $users_table = $wpdb->prefix . 'users';
    $posts_table = $wpdb->prefix . 'posts';

    $results = $wpdb->get_results("
        SELECT 
            uc.user_course_id,
            uc.user_id,
            uc.course_id,
            uc.progress_percent,
            uc.end_time,
            u.display_name,
            u.user_email,
            p.post_title,
            MAX(CASE WHEN sc.parameter = 'cmi.completion_status' THEN sc.value END) AS completion_status,
            MAX(CASE WHEN sc.parameter = 'cmi.success_status' THEN sc.value END) AS success_status
        FROM {$user_courses_table} uc
        LEFT JOIN {$users_table} u ON uc.user_id = u.ID
        LEFT JOIN {$posts_table} p ON uc.course_id = p.ID
        LEFT JOIN {$scorm_table} sc ON uc.user_course_id = sc.user_course_id
        GROUP BY uc.user_course_id
        HAVING completion_status IS NOT NULL OR success_status IS NOT NULL
    ");

    $export_url = esc_url(admin_url('admin.php?page=scorm-progress&download_excel=1'));

    echo '<div class="wrap"><h1>SCORM Course Progress</h1>';
    echo "<p><a href='{$export_url}' class='button button-primary'>Download as Excel</a></p>";
    echo '<table class="widefat fixed">';
    echo '<thead><tr><th>Student</th><th>Email</th><th>Course</th><th>Status</th><th>Progress %</th><th>Completed On</th><th>Certificate</th></tr></thead><tbody>';

    if ($results) {
        foreach ($results as $row) {
            $status = ucfirst($row->completion_status ?: 'incomplete');
            $percent = intval($row->progress_percent);
            $completed_date = $row->end_time ? date('Y-m-d', $row->end_time) : '-';
            $cert_link = ($row->success_status === 'passed')
                ? home_url("/certificate-page/?user={$row->user_id}&course={$row->course_id}")
                : '';

            echo "<tr>
                <td>{$row->display_name}</td>
                <td>{$row->user_email}</td>
                <td>{$row->post_title}</td>
                <td>{$status}</td>
                <td>{$percent}%</td>
                <td>{$completed_date}</td>
                <td>" . ($cert_link ? "<a href='{$cert_link}' target='_blank'>View Certificate</a>" : '-') . "</td>
            </tr>";
        }
    } else {
        echo '<tr><td colspan="7">No SCORM progress data found.</td></tr>';
    }

    echo '</tbody></table></div>';
}

function mst_lms_download_scorm_excel() {
    global $wpdb;

    require_once ABSPATH . 'wp-admin/includes/class-pclzip.php';
    require_once ABSPATH . 'wp-admin/includes/file.php';
    require_once ABSPATH . 'wp-admin/includes/template.php';

    require_once plugin_dir_path(__FILE__) . '../../../includes/libs/phpspreadsheet/vendor/autoload.php';

    $scorm_table = $wpdb->prefix . 'stm_lms_user_course_scorm';
    $user_courses_table = $wpdb->prefix . 'stm_lms_user_courses';
    $users_table = $wpdb->prefix . 'users';
    $posts_table = $wpdb->prefix . 'posts';

    $results = $wpdb->get_results("
        SELECT 
            uc.user_course_id,
            uc.user_id,
            uc.course_id,
            uc.progress_percent,
            uc.end_time,
            u.display_name,
            u.user_email,
            p.post_title,
            MAX(CASE WHEN sc.parameter = 'cmi.completion_status' THEN sc.value END) AS completion_status,
            MAX(CASE WHEN sc.parameter = 'cmi.success_status' THEN sc.value END) AS success_status
        FROM {$user_courses_table} uc
        LEFT JOIN {$users_table} u ON uc.user_id = u.ID
        LEFT JOIN {$posts_table} p ON uc.course_id = p.ID
        LEFT JOIN {$scorm_table} sc ON uc.user_course_id = sc.user_course_id
        GROUP BY uc.user_course_id
        HAVING completion_status IS NOT NULL OR success_status IS NOT NULL
    ");

    $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();

    $sheet->fromArray(
        ['Student', 'Email', 'Course', 'Status', 'Progress %', 'Completed On', 'Certificate Link'],
        NULL,
        'A1'
    );

    $rowIndex = 2;
    foreach ($results as $row) {
        $cert_link = ($row->success_status === 'passed')
            ? home_url("/certificate-page/?user={$row->user_id}&course={$row->course_id}")
            : '';
        $completed_date = $row->end_time ? date('Y-m-d', $row->end_time) : '-';

        $sheet->fromArray(
            [
                $row->display_name,
                $row->user_email,
                $row->post_title,
                ucfirst($row->completion_status ?: 'incomplete'),
                intval($row->progress_percent) . '%',
                $completed_date,
                $cert_link,
            ],
            NULL,
            'A' . $rowIndex++
        );
    }

    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="scorm_progress.xlsx"');
    header('Cache-Control: max-age=0');

    $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
    $writer->save('php://output');
    exit;
}PK
�yZ�)uu��admin-ui.phpPK

:�