Tracking::get_avg_student_score()

Complexity

Conditions	57
Paths	18498

Size

Total Lines	373
Code Lines	206

Duplication

Lines	0
Ratio	0 %

Importance

Changes

0

<?php
    public static function get_avg_student_score(
        $student_id,
        $course_code,
        $lp_ids = [],
        $session_id = null,
        $return_array = false,
        $get_only_latest_attempt_results = false,
        $getOnlyBestAttempt = false
    ) {
        $debug = false;
        if ($debug) {
            echo '<h1>Tracking::get_avg_student_score</h1>';
        }
        $tbl_stats_exercices = Database::get_main_table(TABLE_STATISTIC_TRACK_E_EXERCISES);
        $tbl_stats_attempts = Database::get_main_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
        $course = api_get_course_info($course_code);

        if (empty($course)) {
            return null;
        }

        // Get course tables names
        $tbl_quiz_questions = Database::get_course_table(TABLE_QUIZ_QUESTION);
        $lp_table = Database::get_course_table(TABLE_LP_MAIN);
        $lp_item_table = Database::get_course_table(TABLE_LP_ITEM);
        $lp_view_table = Database::get_course_table(TABLE_LP_VIEW);
        $lp_item_view_table = Database::get_course_table(TABLE_LP_ITEM_VIEW);
        $course_id = $course['real_id'];

        // Compose a filter based on optional learning paths list given
        $condition_lp = '';
        if (count($lp_ids) > 0) {
            $condition_lp = " AND id IN(".implode(',', $lp_ids).") ";
        }

        // Compose a filter based on optional session id
        $session_id = intval($session_id);
        if (count($lp_ids) > 0) {
            $condition_session = " AND session_id = $session_id ";
        } else {
            $condition_session = " WHERE session_id = $session_id ";
        }

        // Check the real number of LPs corresponding to the filter in the
        // database (and if no list was given, get them all)
        if (empty($session_id)) {
            $sql = "SELECT DISTINCT(id), use_max_score
                    FROM $lp_table
                    WHERE
                        c_id = $course_id AND
                        (session_id = 0 OR session_id IS NULL) $condition_lp ";
        } else {
            $sql = "SELECT DISTINCT(id), use_max_score
                    FROM $lp_table
                    WHERE c_id = $course_id $condition_lp ";
        }

        $res_row_lp = Database::query($sql);
        $count_row_lp = Database::num_rows($res_row_lp);

        $lp_list = $use_max_score = [];
        while ($row_lp = Database::fetch_array($res_row_lp)) {
            $lp_list[] = $row_lp['id'];
            $use_max_score[$row_lp['id']] = $row_lp['use_max_score'];
        }

        // prepare filter on users
        if (is_array($student_id)) {
            array_walk($student_id, 'intval');
            $condition_user1 = " AND user_id IN (".implode(',', $student_id).") ";
        } else {
            $condition_user1 = " AND user_id = $student_id ";
        }

        if (empty($count_row_lp) || empty($student_id)) {
            return null;
        }

        // Getting latest LP result for a student
        //@todo problem when a  course have more than 1500 users
        $sql = "SELECT MAX(view_count) as vc, id, progress, lp_id, user_id
                FROM $lp_view_table
                WHERE
                    c_id = $course_id AND
                    lp_id IN (".implode(',', $lp_list).")
                    $condition_user1 AND
                    session_id = $session_id
                GROUP BY lp_id, user_id";

        $rs_last_lp_view_id = Database::query($sql);
        $global_result = 0;

        if (Database::num_rows($rs_last_lp_view_id) > 0) {
            // Cycle through each line of the results (grouped by lp_id, user_id)
            while ($row_lp_view = Database::fetch_array($rs_last_lp_view_id)) {
                $count_items = 0;
                $lpPartialTotal = 0;
                $list = [];
                $lp_view_id = $row_lp_view['id'];
                $lp_id = $row_lp_view['lp_id'];
                $user_id = $row_lp_view['user_id'];

                if ($debug) {
                    echo '<h2>LP id '.$lp_id.'</h2>';
                    echo "get_only_latest_attempt_results: $get_only_latest_attempt_results <br />";
                    echo "getOnlyBestAttempt: $getOnlyBestAttempt <br />";
                }

                if ($get_only_latest_attempt_results || $getOnlyBestAttempt) {
                    // Getting lp_items done by the user
                    $sql = "SELECT DISTINCT lp_item_id
                            FROM $lp_item_view_table
                            WHERE
                                c_id = $course_id AND
                                lp_view_id = $lp_view_id
                            ORDER BY lp_item_id";
                    $res_lp_item = Database::query($sql);

                    while ($row_lp_item = Database::fetch_array($res_lp_item, 'ASSOC')) {
                        $my_lp_item_id = $row_lp_item['lp_item_id'];
                        $order = ' view_count DESC';
                        if ($getOnlyBestAttempt) {
                            $order = ' lp_iv.score DESC';
                        }

                        // Getting the most recent attempt
                        $sql = "SELECT
                                    lp_iv.id as lp_item_view_id,
                                    lp_iv.score as score,
                                    lp_i.max_score,
                                    lp_iv.max_score as max_score_item_view,
                                    lp_i.path,
                                    lp_i.item_type,
                                    lp_i.id as iid
                                FROM $lp_item_view_table as lp_iv
                                INNER JOIN $lp_item_table as lp_i
                                ON (
                                    lp_i.id = lp_iv.lp_item_id AND
                                    lp_iv.c_id = lp_i.c_id
                                )
                                WHERE
                                    lp_iv.c_id = $course_id AND
                                    lp_i.c_id  = $course_id AND
                                    lp_item_id = $my_lp_item_id AND
                                    lp_view_id = $lp_view_id AND
                                    (lp_i.item_type='sco' OR lp_i.item_type='".TOOL_QUIZ."')
                                ORDER BY $order
                                LIMIT 1";

                        $res_lp_item_result = Database::query($sql);
                        while ($row_max_score = Database::fetch_array($res_lp_item_result, 'ASSOC')) {
                            $list[] = $row_max_score;
                        }
                    }
                } else {
                    // For the currently analysed view, get the score and
                    // max_score of each item if it is a sco or a TOOL_QUIZ
                    $sql = "SELECT
                                lp_iv.id as lp_item_view_id,
                                lp_iv.score as score,
                                lp_i.max_score,
                                lp_iv.max_score as max_score_item_view,
                                lp_i.path,
                                lp_i.item_type,
                                lp_i.id as iid
                              FROM $lp_item_view_table as lp_iv
                              INNER JOIN $lp_item_table as lp_i
                              ON lp_i.id = lp_iv.lp_item_id AND
                                 lp_iv.c_id = lp_i.c_id
                              WHERE
                                lp_iv.c_id = $course_id AND
                                lp_i.c_id  = $course_id AND
                                lp_view_id = $lp_view_id AND
                                (lp_i.item_type='sco' OR lp_i.item_type='".TOOL_QUIZ."')
                            ";
                    $res_max_score = Database::query($sql);
                    while ($row_max_score = Database::fetch_array($res_max_score, 'ASSOC')) {
                        $list[] = $row_max_score;
                    }
                }

                // Go through each scorable element of this view
                $score_of_scorm_calculate = 0;
                foreach ($list as $row_max_score) {
                    // Came from the original lp_item
                    $max_score = $row_max_score['max_score'];
                    // Came from the lp_item_view
                    $max_score_item_view = $row_max_score['max_score_item_view'];
                    $score = $row_max_score['score'];
                    if ($debug) {
                        echo '<h3>Item Type: '.$row_max_score['item_type'].'</h3>';
                    }

                    if ('sco' == $row_max_score['item_type']) {
                        /* Check if it is sco (easier to get max_score)
                           when there's no max score, we assume 100 as the max score,
                           as the SCORM 1.2 says that the value should always be between 0 and 100.
                        */
                        if (0 == $max_score || is_null($max_score) || '' == $max_score) {
                            // Chamilo style
                            if ($use_max_score[$lp_id]) {
                                $max_score = 100;
                            } else {
                                // Overwrites max score = 100 to use the one that came in the lp_item_view see BT#1613
                                $max_score = $max_score_item_view;
                            }
                        }
                        // Avoid division by zero errors
                        if (!empty($max_score)) {
                            $lpPartialTotal += $score / $max_score;
                        }
                        if ($debug) {
                            var_dump("lpPartialTotal: $lpPartialTotal");

var_dump('lpPartialTotal: '.$lpPartialTotal) looks like debug code. Are you sure you do not want to remove it?

                            var_dump("score: $score");
                            var_dump("max_score: $max_score");
                        }
                    } else {
                        // Case of a TOOL_QUIZ element
                        $item_id = $row_max_score['iid'];
                        $item_path = $row_max_score['path'];
                        $lp_item_view_id = (int) $row_max_score['lp_item_view_id'];

                        $lpItemCondition = '';
                        if (empty($lp_item_view_id)) {
                            $lpItemCondition = ' (orig_lp_item_view_id = 0 OR orig_lp_item_view_id IS NULL) ';
                        } else {
                            $lpItemCondition = " orig_lp_item_view_id = $lp_item_view_id ";
                        }

                        // Get last attempt to this exercise through
                        // the current lp for the current user
                        $order = 'exe_date DESC';
                        if ($getOnlyBestAttempt) {
                            $order = 'score DESC';
                        }
                        $sql = "SELECT exe_id, score
                                FROM $tbl_stats_exercices
                                WHERE
                                    exe_exo_id = '$item_path' AND
                                    exe_user_id = $user_id AND
                                    orig_lp_item_id = $item_id AND
                                    $lpItemCondition AND
                                    c_id = $course_id AND
                                    session_id = $session_id AND
                                    status = ''
                                ORDER BY $order
                                LIMIT 1";

                        $result_last_attempt = Database::query($sql);
                        $num = Database::num_rows($result_last_attempt);
                        if ($num > 0) {
                            $attemptResult = Database::fetch_array($result_last_attempt, 'ASSOC');
                            $id_last_attempt = $attemptResult['exe_id'];
                            // We overwrite the score with the best one not the one saved in the LP (latest)
                            if ($getOnlyBestAttempt && false == $get_only_latest_attempt_results) {
                                if ($debug) {
                                    echo "Following score comes from the track_exercise table not in the LP because the score is the best<br />";
                                }
                                $score = $attemptResult['score'];
                            }

                            if ($debug) {
                                echo "Attempt id: $id_last_attempt with score $score<br />";
                            }
                            // Within the last attempt number tracking, get the sum of
                            // the max_scores of all questions that it was
                            // made of (we need to make this call dynamic because of random questions selection)
                            $sql = "SELECT SUM(t.ponderation) as maxscore FROM
                                            (
                                                SELECT DISTINCT
                                                    question_id,
                                                    marks,
                                                    ponderation
                                                FROM $tbl_stats_attempts AS at
                                                INNER JOIN $tbl_quiz_questions AS q
                                                ON (q.id = at.question_id AND q.c_id = q.c_id)
                                                WHERE
                                                    exe_id ='$id_last_attempt' AND
                                                    q.c_id = $course_id
                                            )
                                            AS t";

                            $res_max_score_bis = Database::query($sql);
                            $row_max_score_bis = Database::fetch_array($res_max_score_bis);

                            if (!empty($row_max_score_bis['maxscore'])) {
                                $max_score = $row_max_score_bis['maxscore'];
                            }
                            if (!empty($max_score) && floatval($max_score) > 0) {
                                $lpPartialTotal += $score / $max_score;
                            }
                            if ($debug) {
                                var_dump("score: $score");
                                var_dump("max_score: $max_score");
                                var_dump("lpPartialTotal: $lpPartialTotal");
                            }
                        }
                    }

                    if (in_array($row_max_score['item_type'], ['quiz', 'sco'])) {
                        // Normal way
                        if ($use_max_score[$lp_id]) {
                            $count_items++;
                        } else {
                            if ('' != $max_score) {
                                $count_items++;
                            }
                        }
                        if ($debug) {
                            echo '$count_items: '.$count_items;
                        }
                    }
                } //end for

                $score_of_scorm_calculate += $count_items ? (($lpPartialTotal / $count_items) * 100) : 0;
                $global_result += $score_of_scorm_calculate;

                if ($debug) {
                    var_dump("count_items: $count_items");
                    var_dump("score_of_scorm_calculate: $score_of_scorm_calculate");
                    var_dump("global_result: $global_result");
                }
            } // end while
        }

        $lp_with_quiz = 0;
        foreach ($lp_list as $lp_id) {
            // Check if LP have a score we assume that all SCO have an score
            $sql = "SELECT count(id) as count
                    FROM $lp_item_table
                    WHERE
                        c_id = $course_id AND
                        (item_type = 'quiz' OR item_type = 'sco') AND
                        lp_id = ".$lp_id;
            $result_have_quiz = Database::query($sql);
            if (Database::num_rows($result_have_quiz) > 0) {
                $row = Database::fetch_array($result_have_quiz, 'ASSOC');
                if (is_numeric($row['count']) && 0 != $row['count']) {
                    $lp_with_quiz++;
                }
            }
        }

        if ($debug) {
            echo '<h3>$lp_with_quiz '.$lp_with_quiz.' </h3>';
        }
        if ($debug) {
            echo '<h3>Final return</h3>';
        }

        if (0 != $lp_with_quiz) {
            if (!$return_array) {
                $score_of_scorm_calculate = round(($global_result / $lp_with_quiz), 2);
                if ($debug) {
                    var_dump($score_of_scorm_calculate);
                }
                if (empty($lp_ids)) {
                    if ($debug) {
                        echo '<h2>All lps fix: '.$score_of_scorm_calculate.'</h2>';
                    }
                }

                return $score_of_scorm_calculate;
            }

            if ($debug) {
                var_dump($global_result, $lp_with_quiz);
            }

            return [$global_result, $lp_with_quiz];
        }

        return '-';
    }