openschool.uk

Progress Analysis and Frontend Reports

I wrote the function below that returns a table (see above) showing a users Learndash test paper attempts based on their performance in each question category. It provides a way to analyse where the users strengths and weaknesses lie.

/**
 *
 *  Function to output a table of a users course performance based on question categories
 *
*/
get_course_performance($user_id, $course_id) {

    // sanitize input
    $user_id = intval($user_id);
    $course_id = intval($course_id);

    // initiate database
    global $wpdb;

    // retrieve the users incorrect answers from the course
    $incorrect = $wpdb->get_col( $wpdb->prepare( 
        "
        SELECT      category_name
        FROM        wp_wp_pro_quiz_category
        INNER JOIN  wp_wp_pro_quiz_question
        ON          wp_wp_pro_quiz_category.category_id = wp_wp_pro_quiz_question.category_id        
        INNER JOIN  wp_wp_pro_quiz_statistic
        ON          (wp_wp_pro_quiz_question.id = wp_wp_pro_quiz_statistic.question_id AND wp_wp_pro_quiz_statistic.incorrect_count = '1')
        INNER JOIN  wp_wp_pro_quiz_statistic_ref
        ON          (wp_wp_pro_quiz_statistic.statistic_ref_id = wp_wp_pro_quiz_statistic_ref.statistic_ref_id AND wp_wp_pro_quiz_statistic_ref.user_id = %d)
        LEFT JOIN   wp_postmeta AS pm
        ON          wp_wp_pro_quiz_statistic_ref.quiz_id = pm.meta_value
        AND         pm.meta_key = 'quiz_pro_id'
        INNER JOIN  wp_postmeta As pm2
        ON          pm.post_id = pm2.post_id
        AND         pm2.meta_key = 'course_id'
        AND         pm2.meta_value = %s
        ",$user_id,$course_id
    ));

    // retrieve the users total answers from the course
    $all = $wpdb->get_col( $wpdb->prepare( 
        "
        SELECT      category_name
        FROM        wp_wp_pro_quiz_category
        INNER JOIN  wp_wp_pro_quiz_question
        ON          wp_wp_pro_quiz_category.category_id = wp_wp_pro_quiz_question.category_id        
        INNER JOIN  wp_wp_pro_quiz_statistic
        ON          wp_wp_pro_quiz_question.id = wp_wp_pro_quiz_statistic.question_id
        INNER JOIN  wp_wp_pro_quiz_statistic_ref
        ON          wp_wp_pro_quiz_statistic.statistic_ref_id = wp_wp_pro_quiz_statistic_ref.statistic_ref_id
        AND         user_id = %d
        LEFT JOIN   wp_postmeta AS pm
        ON          wp_wp_pro_quiz_statistic_ref.quiz_id = pm.meta_value
        AND         pm.meta_key = 'quiz_pro_id'
        INNER JOIN  wp_postmeta As pm2
        ON          pm.post_id = pm2.post_id
        AND         pm2.meta_key = 'course_id'
        AND         pm2.meta_value = %s
        ",$user_id,$course_id
    ));
    
    // count the occurance of each question category in each array
    $count_all = array_count_values($all);
    $count_incorrect = array_count_values($incorrect);

    // begin results table output
    echo '<table class = "performance_analysis">';
    echo '<tr><td>Topic</td><td>Percentage Correct</td><td>Total Answers</td></tr>';
    
    // loop through the arrays and setup display data
    if (!empty($count_all)) {
        foreach($count_all as $key => $value){
            
            // record the total number of answers in this category
            $count_single = $value;
            
            // see if this category is also in the incorrect array
            if (array_key_exists($key, $count_incorrect)) {
            
                // calculate percentages
                $percentage_incorrect = round(100 * ($count_incorrect[$key] / $count_single));
                $percentage_correct = 100 - $percentage_incorrect;
                
                // set up display data
                if ($percentage_correct < 15) {
                    // set width to 8 on low values to avoid 0 width
                    $width = 8;
                    $bg_color = '#BD3B30';
                } else if ($percentage_correct < 30) {
                    $width = $percentage_correct;
                    $bg_color = '#E64C3C';
                } else if ($percentage_correct < 50) {
                    $width = $percentage_correct;
                    $bg_color = '#F77B1E';
                } else if ($percentage_correct < 70) {
                    $width = $percentage_correct;
                    $bg_color = '#F3B11C';
                } else if ($percentage_correct < 90) {
                    $width = $percentage_correct;
                    $bg_color = '#8DBF3F';
                } else {
                    $width = $percentage_correct;
                    $bg_color = '#04B16A';
                }
            } else {
                // no incorrect answers in this category so set width to 100
                $percentage_correct = 100;
                $width = 100;
                $bg_color = '#04B16A';
            }
            
            // output the user performance information to the table
            echo '<tr>'; 
            echo "<td>", $key, "</td>";
            echo "<td><div class='percentage_bar' style='width:" . $width . "%;background-color:" . $bg_color . "'>", $percentage_correct,"%</div></td>";
            echo "<td>", $count_single,"</td>";
            echo '</tr>';
        }
    } else {
        // if no answers display message
        echo '<tr>';
        echo "<td></td>";
        echo "<td>No questions have been attempted.</td>";
        echo "<td></td>";
        echo '</tr>';
    }
    echo '</table>';
}