prepare(). // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching /** * CSV downloads from the Enrollments and Reports screens. * * Two exports, each for one course or all of them: enrolments (with each student's progress) and * quiz attempts. Rows are streamed in batches, and a cell that a spreadsheet would read as a * formula is prefixed with an apostrophe. */ class Csv_Export { const ACTION = 'accessnow_slms_export_csv'; /** Rows read per query. */ const BATCH = 500; /** Export type => capability. */ const TYPES = array( 'enrollments' => 'manage_slms_enrollments', 'quiz_attempts' => 'manage_slms_reports', ); public static function init() { add_action( 'admin_post_' . self::ACTION, array( __CLASS__, 'handle' ) ); } /** * The signed link to an export. * * @param string $type 'enrollments' or 'quiz_attempts'. * @param int $course_id Course ID, or 0 for all courses. * @param string $status Enrolment status filter ('' for all). * @return string */ public static function url( $type, $course_id = 0, $status = '' ) { // Not wp_nonce_url(): it returns an HTML-escaped URL (&), and this one is also used raw. return add_query_arg( array_filter( array( 'action' => self::ACTION, 'type' => $type, 'course_id' => (int) $course_id, 'status' => $status, '_wpnonce' => wp_create_nonce( self::ACTION ), ) ), admin_url( 'admin-post.php' ) ); } /** * A button that downloads an export. * * @param string $type Export type. * @param string $label Button text. * @param int $course_id Course ID, or 0. * @param string $status Enrolment status filter. */ public static function button( $type, $label, $course_id = 0, $status = '' ) { printf( '%s ', esc_url( self::url( $type, $course_id, $status ) ), esc_html( $label ) ); } public static function handle() { check_admin_referer( self::ACTION ); $type = isset( $_GET['type'] ) ? sanitize_key( wp_unslash( $_GET['type'] ) ) : ''; if ( ! isset( self::TYPES[ $type ] ) ) { wp_die( esc_html__( 'Invalid request.', 'accessnow-lms' ), 400 ); } if ( ! current_user_can( self::TYPES[ $type ] ) ) { wp_die( esc_html__( 'Unauthorized.', 'accessnow-lms' ), 403 ); } $course_id = isset( $_GET['course_id'] ) ? absint( $_GET['course_id'] ) : 0; $status = isset( $_GET['status'] ) ? sanitize_key( wp_unslash( $_GET['status'] ) ) : ''; if ( $course_id && 'slms_course' !== get_post_type( $course_id ) ) { wp_die( esc_html__( 'Course not found.', 'accessnow-lms' ), 404 ); } if ( ! in_array( $status, array( '', 'active', 'completed', 'dropped' ), true ) ) { $status = ''; } $slug = $course_id ? get_post_field( 'post_name', $course_id ) : 'all-courses'; $filename = sanitize_file_name( 'accessnow-lms-' . str_replace( '_', '-', $type ) . '-' . $slug . '-' . wp_date( 'Y-m-d' ) . '.csv' ); nocache_headers(); header( 'Content-Type: text/csv; charset=utf-8' ); header( 'Content-Disposition: attachment; filename="' . $filename . '"' ); header( 'X-Content-Type-Options: nosniff' ); $out = fopen( 'php://output', 'w' ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fopen -- streaming a download, not a file. self::write( $out, $type, $course_id, $status ); fclose( $out ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose -- see above. exit; } /** * Write an export to a stream (also used by the tests). * * @param resource $out Stream. * @param string $type Export type. * @param int $course_id Course ID, or 0. * @param string $status Enrolment status filter. */ public static function write( $out, $type, $course_id = 0, $status = '' ) { fwrite( $out, "\xEF\xBB\xBF" ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fwrite -- UTF-8 marker, so spreadsheets read names correctly. if ( 'quiz_attempts' === $type ) { self::write_quiz_attempts( $out, $course_id ); } else { self::write_enrollments( $out, $course_id, $status ); } } private static function write_enrollments( $out, $course_id, $status ) { self::row( $out, array( __( 'Enrollment ID', 'accessnow-lms' ), __( 'Student', 'accessnow-lms' ), __( 'Username', 'accessnow-lms' ), __( 'Email', 'accessnow-lms' ), __( 'Course', 'accessnow-lms' ), __( 'Status', 'accessnow-lms' ), __( 'Progress (%)', 'accessnow-lms' ), __( 'Enrolled (UTC)', 'accessnow-lms' ), __( 'Completed (UTC)', 'accessnow-lms' ), __( 'Enrolled By', 'accessnow-lms' ), __( 'Access ends (UTC)', 'accessnow-lms' ), ) ); $titles = array(); for ( $offset = 0; ; $offset += self::BATCH ) { $rows = Enrollment::get_all_enrollments( array( 'course_id' => $course_id, 'status' => $status, 'per_page' => self::BATCH, 'offset' => $offset, 'orderby' => 'id', 'order' => 'ASC', ) ); foreach ( $rows as $e ) { $user = get_userdata( $e->user_id ); $by = $e->enrolled_by ? get_userdata( $e->enrolled_by ) : null; if ( ! isset( $titles[ $e->course_id ] ) ) { $titles[ $e->course_id ] = self::title( $e->course_id ); } self::row( $out, array( $e->id, $user ? $user->display_name : '#' . $e->user_id, $user ? $user->user_login : '', $user ? $user->user_email : '', $titles[ $e->course_id ], $e->status, Progress::get_course_progress( $e->user_id, $e->course_id, $e->id ), $e->enrolled_at, $e->completed_at, $e->enrolled_by ? ( $by ? $by->display_name : '#' . $e->enrolled_by ) : __( 'Self', 'accessnow-lms' ), isset( $e->expires_at ) ? $e->expires_at : '', ) ); } if ( count( $rows ) < self::BATCH ) { break; } } } private static function write_quiz_attempts( $out, $course_id ) { global $wpdb; self::row( $out, array( __( 'Student', 'accessnow-lms' ), __( 'Username', 'accessnow-lms' ), __( 'Email', 'accessnow-lms' ), __( 'Course', 'accessnow-lms' ), __( 'Quiz', 'accessnow-lms' ), __( 'Attempt', 'accessnow-lms' ), __( 'Score (%)', 'accessnow-lms' ), __( 'Result', 'accessnow-lms' ), __( 'Started (UTC)', 'accessnow-lms' ), __( 'Completed (UTC)', 'accessnow-lms' ), __( 'Enrollment ID', 'accessnow-lms' ), ) ); $titles = array(); for ( $offset = 0; ; $offset += self::BATCH ) { $rows = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}slms_quiz_attempts WHERE ( 0 = %d OR course_id = %d ) ORDER BY id ASC LIMIT %d OFFSET %d", $course_id, $course_id, self::BATCH, $offset ) ); foreach ( $rows as $a ) { $user = get_userdata( $a->user_id ); foreach ( array( $a->course_id, $a->quiz_id ) as $post_id ) { if ( ! isset( $titles[ $post_id ] ) ) { $titles[ $post_id ] = self::title( $post_id ); } } self::row( $out, array( $user ? $user->display_name : '#' . $a->user_id, $user ? $user->user_login : '', $user ? $user->user_email : '', $titles[ $a->course_id ], $titles[ $a->quiz_id ], $a->attempt_number, $a->score, Quiz_Engine::attempt_status_label( $a ), $a->started_at, $a->completed_at, $a->enrollment_id, ) ); } if ( count( $rows ) < self::BATCH ) { break; } } } /** * A post title as plain text. get_the_title() is HTML-escaped (& becomes &), and a stored * title can itself hold entities (&) when it was saved through kses. * * @param int $post_id Post ID. * @return string */ private static function title( $post_id ) { return html_entity_decode( (string) get_post_field( 'post_title', $post_id, 'raw' ), ENT_QUOTES | ENT_HTML5, 'UTF-8' ); } /** * One CSV row. A cell starting with = + - @ or a tab/return is prefixed with an apostrophe, so a * student named "=HYPERLINK(...)" stays text in a spreadsheet. * * @param resource $out Stream. * @param array $cells Cells. */ public static function row( $out, array $cells ) { $safe = array(); foreach ( $cells as $cell ) { $cell = (string) $cell; $safe[] = ( '' !== $cell && false !== strpos( "=+-@\t\r", $cell[0] ) && ! is_numeric( $cell ) ) ? "'" . $cell : $cell; } fputcsv( $out, $safe, ',', '"', '\\' ); } }