ExerciseLib::get_exam_results_data()

Complexity

Conditions

94

Size

Total Lines	725
Code Lines	456

Duplication

Lines	0
Ratio	0 %

Importance

Changes	2
Bugs	0	Features	0

<?php
     */
    public static function get_exam_results_data(
        $from,
        $number_of_items,
        $column,
        $direction,
        $exercise_id,
        $extra_where_conditions = null,
        $get_count = false,
        $courseCode = null,
        $showSessionField = false,
        $showExerciseCategories = false,
        $userExtraFieldsToAdd = [],
        $useCommaAsDecimalPoint = false,
        $roundValues = false,
        $getOnyIds = false
    ) {
        //@todo replace all this globals
        global $filter;
        $courseCode = empty($courseCode) ? api_get_course_id() : $courseCode;
        $courseInfo = api_get_course_info($courseCode);

        if (empty($courseInfo)) {
            return [];
        }

        //$documentPath = api_get_path(SYS_COURSE_PATH).$courseInfo['path'].'/document';

        $course_id = $courseInfo['real_id'];
        $sessionId = api_get_session_id();
        $exercise_id = (int) $exercise_id;

        $is_allowedToEdit =
            api_is_allowed_to_edit(null, true) ||
            api_is_allowed_to_edit(true) ||
            api_is_drh() ||
            api_is_student_boss() ||
            api_is_session_admin();
        $TBL_USER = Database::get_main_table(TABLE_MAIN_USER);
        $TBL_EXERCICES = Database::get_course_table(TABLE_QUIZ_TEST);
        $TBL_GROUP_REL_USER = Database::get_course_table(TABLE_GROUP_USER);
        $TBL_GROUP = Database::get_course_table(TABLE_GROUP);
        $TBL_TRACK_EXERCICES = Database::get_main_table(TABLE_STATISTIC_TRACK_E_EXERCISES);
        $TBL_TRACK_ATTEMPT_RECORDING = Database::get_main_table(TABLE_STATISTIC_TRACK_E_ATTEMPT_RECORDING);

        $session_id_and = '';
        $sessionCondition = '';
        if (!$showSessionField) {
            $session_id_and = " AND te.session_id = $sessionId ";
            $sessionCondition = " AND ttte.session_id = $sessionId";
        }

        $exercise_where = '';
        if (!empty($exercise_id)) {
            $exercise_where .= ' AND te.exe_exo_id = '.$exercise_id.'  ';
        }

        // sql for chamilo-type tests for teacher / tutor view
        $sql_inner_join_tbl_track_exercices = "
        (
            SELECT DISTINCT ttte.*, if(tr.exe_id,1, 0) as revised
            FROM $TBL_TRACK_EXERCICES ttte
            LEFT JOIN $TBL_TRACK_ATTEMPT_RECORDING tr
            ON (ttte.exe_id = tr.exe_id)
            WHERE
                c_id = $course_id AND
                exe_exo_id = $exercise_id
                $sessionCondition
        )";

        if ($is_allowedToEdit) {
            //@todo fix to work with COURSE_RELATION_TYPE_RRHH in both queries
            // Hack in order to filter groups
            $sql_inner_join_tbl_user = '';
            if (strpos($extra_where_conditions, 'group_id')) {
                $sql_inner_join_tbl_user = "
                (
                    SELECT
                        u.user_id,
                        firstname,
                        lastname,
                        official_code,
                        email,
                        username,
                        g.name as group_name,
                        g.id as group_id
                    FROM $TBL_USER u
                    INNER JOIN $TBL_GROUP_REL_USER gru
                    ON (gru.user_id = u.user_id AND gru.c_id= $course_id )
                    INNER JOIN $TBL_GROUP g
                    ON (gru.group_id = g.id AND g.c_id= $course_id )
                )";
            }

            if (strpos($extra_where_conditions, 'group_all')) {
                $extra_where_conditions = str_replace(
                    "AND (  group_id = 'group_all'  )",
                    '',
                    $extra_where_conditions
                );
                $extra_where_conditions = str_replace(
                    "AND group_id = 'group_all'",
                    '',
                    $extra_where_conditions
                );
                $extra_where_conditions = str_replace(
                    "group_id = 'group_all' AND",
                    '',
                    $extra_where_conditions
                );

                $sql_inner_join_tbl_user = "
                (
                    SELECT
                        u.user_id,
                        firstname,
                        lastname,
                        official_code,
                        email,
                        username,
                        '' as group_name,
                        '' as group_id
                    FROM $TBL_USER u
                )";
                $sql_inner_join_tbl_user = null;
            }

            if (strpos($extra_where_conditions, 'group_none')) {
                $extra_where_conditions = str_replace(
                    "AND (  group_id = 'group_none'  )",
                    "AND (  group_id is null  )",
                    $extra_where_conditions
                );
                $extra_where_conditions = str_replace(
                    "AND group_id = 'group_none'",
                    "AND (  group_id is null  )",
                    $extra_where_conditions
                );
                $sql_inner_join_tbl_user = "
            (
                SELECT
                    u.user_id,
                    firstname,
                    lastname,
                    official_code,
                    email,
                    username,
                    g.name as group_name,
                    g.id as group_id
                FROM $TBL_USER u
                LEFT OUTER JOIN $TBL_GROUP_REL_USER gru
                ON ( gru.user_id = u.user_id AND gru.c_id= $course_id )
                LEFT OUTER JOIN $TBL_GROUP g
                ON (gru.group_id = g.id AND g.c_id = $course_id )
            )";
            }

            // All
            $is_empty_sql_inner_join_tbl_user = false;
            if (empty($sql_inner_join_tbl_user)) {
                $is_empty_sql_inner_join_tbl_user = true;
                $sql_inner_join_tbl_user = "
            (
                SELECT u.user_id, firstname, lastname, email, username, ' ' as group_name, '' as group_id, official_code
                FROM $TBL_USER u
                WHERE u.status NOT IN(".api_get_users_status_ignored_in_reports('string').")
            )";
            }

            $sqlFromOption = " , $TBL_GROUP_REL_USER AS gru ";
            $sqlWhereOption = "  AND gru.c_id = $course_id AND gru.user_id = user.user_id ";
            $first_and_last_name = api_is_western_name_order() ? "firstname, lastname" : "lastname, firstname";

            if ($get_count) {
                $sql_select = 'SELECT count(te.exe_id) ';
            } else {
                $sql_select = "SELECT DISTINCT
                    user_id,
                    $first_and_last_name,
                    official_code,
                    ce.title,
                    username,
                    te.score,
                    te.max_score,
                    te.exe_date,
                    te.exe_id,
                    te.session_id,
                    email as exemail,
                    te.start_date,
                    ce.expired_time,
                    steps_counter,
                    exe_user_id,
                    te.exe_duration,
                    te.status as completion_status,
                    propagate_neg,
                    revised,
                    group_name,
                    group_id,
                    orig_lp_id,
                    te.user_ip";
            }

            $sql = " $sql_select
                FROM $TBL_EXERCICES AS ce
                INNER JOIN $sql_inner_join_tbl_track_exercices AS te
                ON (te.exe_exo_id = ce.id)
                INNER JOIN $sql_inner_join_tbl_user AS user
                ON (user.user_id = exe_user_id)
                WHERE
                    te.c_id = $course_id $session_id_and AND
                    ce.active <> -1 AND
                    ce.c_id = $course_id
                    $exercise_where
                    $extra_where_conditions
                ";
        }

        if (empty($sql)) {
            return false;
        }

        if ($get_count) {
            $resx = Database::query($sql);
            $rowx = Database::fetch_row($resx, 'ASSOC');

            return $rowx[0];
        }

        $teacher_list = CourseManager::get_teacher_list_from_course_code($courseCode);
        $teacher_id_list = [];
        if (!empty($teacher_list)) {
            foreach ($teacher_list as $teacher) {
                $teacher_id_list[] = $teacher['user_id'];
            }
        }

        $scoreDisplay = new ScoreDisplay();
        $decimalSeparator = '.';
        $thousandSeparator = ',';

        if ($useCommaAsDecimalPoint) {
            $decimalSeparator = ',';
            $thousandSeparator = '';
        }

        $listInfo = [];
        $column = !empty($column) ? Database::escape_string($column) : null;
        $from = (int) $from;
        $number_of_items = (int) $number_of_items;

        if (!empty($column)) {
            $sql .= " ORDER BY $column $direction ";
        }

        if (!$getOnyIds) {
            $sql .= " LIMIT $from, $number_of_items";
        }

        $results = [];
        $resx = Database::query($sql);
        while ($rowx = Database::fetch_array($resx, 'ASSOC')) {
            $results[] = $rowx;
        }

        $group_list = GroupManager::get_group_list(null, $courseInfo);
        $clean_group_list = [];
        if (!empty($group_list)) {
            foreach ($group_list as $group) {
                $clean_group_list[$group['id']] = $group['name'];
            }
        }

        $lp_list_obj = new LearnpathList(api_get_user_id());
        $lp_list = $lp_list_obj->get_flat_list();
        $oldIds = array_column($lp_list, 'lp_old_id', 'iid');

        if (is_array($results)) {
            $users_array_id = [];
            $from_gradebook = false;
            if (isset($_GET['gradebook']) && 'view' == $_GET['gradebook']) {
                $from_gradebook = true;
            }
            $sizeof = count($results);
            $locked = api_resource_is_locked_by_gradebook(
                $exercise_id,
                LINK_EXERCISE
            );

            $timeNow = strtotime(api_get_utc_datetime());
            // Looping results
            for ($i = 0; $i < $sizeof; $i++) {
                $revised = $results[$i]['revised'];
                if ('incomplete' == $results[$i]['completion_status']) {
                    // If the exercise was incomplete, we need to determine
                    // if it is still into the time allowed, or if its
                    // allowed time has expired and it can be closed
                    // (it's "unclosed")
                    $minutes = $results[$i]['expired_time'];
                    if (0 == $minutes) {
                        // There's no time limit, so obviously the attempt
                        // can still be "ongoing", but the teacher should
                        // be able to choose to close it, so mark it as
                        // "unclosed" instead of "ongoing"
                        $revised = 2;
                    } else {
                        $allowedSeconds = $minutes * 60;
                        $timeAttemptStarted = strtotime($results[$i]['start_date']);
                        $secondsSinceStart = $timeNow - $timeAttemptStarted;
                        if ($secondsSinceStart > $allowedSeconds) {
                            $revised = 2; // mark as "unclosed"
                        } else {
                            $revised = 3; // mark as "ongoing"
                        }
                    }
                }

                if ($from_gradebook && ($is_allowedToEdit)) {
                    if (in_array(
                        $results[$i]['username'].$results[$i]['firstname'].$results[$i]['lastname'],
                        $users_array_id
                    )) {
                        continue;
                    }
                    $users_array_id[] = $results[$i]['username'].$results[$i]['firstname'].$results[$i]['lastname'];
                }

                $lp_obj = isset($results[$i]['orig_lp_id']) && isset($lp_list[$results[$i]['orig_lp_id']]) ? $lp_list[$results[$i]['orig_lp_id']] : null;
                if (empty($lp_obj)) {
                    // Try to get the old id (id instead of iid)
                    $lpNewId = isset($results[$i]['orig_lp_id']) && isset($oldIds[$results[$i]['orig_lp_id']]) ? $oldIds[$results[$i]['orig_lp_id']] : null;
                    if ($lpNewId) {
                        $lp_obj = isset($lp_list[$lpNewId]) ? $lp_list[$lpNewId] : null;
                    }
                }
                $lp_name = null;
                if ($lp_obj) {
                    $url = api_get_path(WEB_CODE_PATH).'lp/lp_controller.php?'.api_get_cidreq().'&action=view&lp_id='.$results[$i]['orig_lp_id'];
                    $lp_name = Display::url(
                        $lp_obj['lp_name'],
                        $url,
                        ['target' => '_blank']
                    );
                }

                // Add all groups by user
                $group_name_list = '';
                if ($is_empty_sql_inner_join_tbl_user) {
                    $group_list = GroupManager::get_group_ids(
                        api_get_course_int_id(),
                        $results[$i]['user_id']
                    );

                    foreach ($group_list as $id) {
                        if (isset($clean_group_list[$id])) {
                            $group_name_list .= $clean_group_list[$id].'<br/>';
                        }
                    }
                    $results[$i]['group_name'] = $group_name_list;
                }

                $results[$i]['exe_duration'] = !empty($results[$i]['exe_duration']) ? round($results[$i]['exe_duration'] / 60) : 0;
                $id = $results[$i]['exe_id'];
                $dt = api_convert_and_format_date($results[$i]['max_score']);

                // we filter the results if we have the permission to
                $result_disabled = 0;
                if (isset($results[$i]['results_disabled'])) {
                    $result_disabled = (int) $results[$i]['results_disabled'];
                }
                if (0 == $result_disabled) {
                    $my_res = $results[$i]['score'];
                    $my_total = $results[$i]['max_score'];
                    $results[$i]['start_date'] = api_get_local_time($results[$i]['start_date']);
                    $results[$i]['exe_date'] = api_get_local_time($results[$i]['exe_date']);

                    if (!$results[$i]['propagate_neg'] && $my_res < 0) {
                        $my_res = 0;
                    }

                    $score = self::show_score(
                        $my_res,
                        $my_total,
                        true,
                        true,
                        false,
                        false,
                        $decimalSeparator,
                        $thousandSeparator,
                        $roundValues
                    );

                    $actions = '<div class="pull-right">';
                    if ($is_allowedToEdit) {
                        if (isset($teacher_id_list)) {
                            if (in_array(
                                $results[$i]['exe_user_id'],
                                $teacher_id_list
                            )) {
                                $actions .= Display::return_icon('teacher.png', get_lang('Trainer'));
                            }
                        }
                        $revisedLabel = '';
                        switch ($revised) {
                            case 0:
                                $actions .= "<a href='exercise_show.php?".api_get_cidreq()."&action=qualify&id=$id'>".
                                    Display:: return_icon(
                                        'quiz.png',
                                        get_lang('Grade activity')
                                    );
                                $actions .= '</a>';
                                $revisedLabel = Display::label(
                                    get_lang('Not validated'),
                                    'info'
                                );
                                break;
                            case 1:
                                $actions .= "<a href='exercise_show.php?".api_get_cidreq()."&action=edit&id=$id'>".
                                    Display:: return_icon(
                                        'edit.png',
                                        get_lang('Edit'),
                                        [],
                                        ICON_SIZE_SMALL
                                    );
                                $actions .= '</a>';
                                $revisedLabel = Display::label(
                                    get_lang('Validated'),
                                    'success'
                                );
                                break;
                            case 2: //finished but not marked as such
                                $actions .= '<a href="exercise_report.php?'
                                    .api_get_cidreq()
                                    .'&exerciseId='
                                    .$exercise_id
                                    .'&a=close&id='
                                    .$id
                                    .'">'.
                                    Display:: return_icon(
                                        'lock.png',
                                        get_lang('Mark attempt as closed'),
                                        [],
                                        ICON_SIZE_SMALL
                                    );
                                $actions .= '</a>';
                                $revisedLabel = Display::label(
                                    get_lang('Unclosed'),
                                    'warning'
                                );
                                break;
                            case 3: //still ongoing
                                $actions .= Display:: return_icon(
                                    'clock.png',
                                    get_lang('Attempt still going on. Please wait.'),
                                    [],
                                    ICON_SIZE_SMALL
                                );
                                $actions .= '';
                                $revisedLabel = Display::label(
                                    get_lang('Ongoing'),
                                    'danger'
                                );
                                break;
                        }

                        if (2 == $filter) {
                            $actions .= ' <a href="exercise_history.php?'.api_get_cidreq().'&exe_id='.$id.'">'.
                                Display:: return_icon(
                                    'history.png',
                                    get_lang('View changes history')
                                ).'</a>';
                        }

                        // Admin can always delete the attempt
                        if ((false == $locked || api_is_platform_admin()) && !api_is_student_boss()) {
                            $ip = Tracking::get_ip_from_user_event(
                                $results[$i]['exe_user_id'],
                                api_get_utc_datetime(),
                                false
                            );
                            $actions .= '<a href="http://www.whatsmyip.org/ip-geo-location/?ip='.$ip.'" target="_blank">'
                                .Display::return_icon('info.png', $ip)
                                .'</a>';

                            $recalculateUrl = api_get_path(WEB_CODE_PATH).'exercise/recalculate.php?'.
                                api_get_cidreq().'&'.
                                http_build_query([
                                    'id' => $id,
                                    'exercise' => $exercise_id,
                                    'user' => $results[$i]['exe_user_id'],
                                ]);
                            $actions .= Display::url(
                                Display::return_icon('reload.png', get_lang('Recalculate results')),
                                $recalculateUrl,
                                [
                                    'data-exercise' => $exercise_id,
                                    'data-user' => $results[$i]['exe_user_id'],
                                    'data-id' => $id,
                                    'class' => 'exercise-recalculate',
                                ]
                            );

                            $filterByUser = isset($_GET['filter_by_user']) ? (int) $_GET['filter_by_user'] : 0;
                            $delete_link = '<a href="exercise_report.php?'.api_get_cidreq().'&filter_by_user='.$filterByUser.'&filter='.$filter.'&id='.$exercise_id.'&delete=delete&did='.$id.'"
                            onclick="javascript:if(!confirm(\''.sprintf(
                                addslashes(get_lang('Delete attempt?')),
                                $results[$i]['username'],
                                $dt
                            ).'\')) return false;">';
                            $delete_link .= Display::return_icon(
                                'delete.png',
                                    addslashes(get_lang('Delete'))
                            ).'</a>';

                            if (api_is_drh() && !api_is_platform_admin()) {
                                $delete_link = null;
                            }
                            if (api_is_session_admin()) {
                                $delete_link = '';
                            }
                            if (3 == $revised) {
                                $delete_link = null;
                            }
                            $actions .= $delete_link;
                        }
                    } else {
                        $attempt_url = api_get_path(WEB_CODE_PATH).'exercise/result.php?'.api_get_cidreq().'&id='.$results[$i]['exe_id'].'&sid='.$sessionId;
                        $attempt_link = Display::url(
                            get_lang('Show'),
                            $attempt_url,
                            [
                                'class' => 'ajax btn btn-default',
                                'data-title' => get_lang('Show'),
                            ]
                        );
                        $actions .= $attempt_link;
                    }
                    $actions .= '</div>';

                    if (!empty($userExtraFieldsToAdd)) {
                        foreach ($userExtraFieldsToAdd as $variable) {
                            $extraFieldValue = new ExtraFieldValue('user');
                            $values = $extraFieldValue->get_values_by_handler_and_field_variable(
                                $results[$i]['user_id'],
                                $variable
                            );
                            if (isset($values['value'])) {
                                $results[$i][$variable] = $values['value'];
                            }
                        }
                    }

                    $exeId = $results[$i]['exe_id'];
                    $results[$i]['id'] = $exeId;
                    $category_list = [];
                    if ($is_allowedToEdit) {
                        $sessionName = '';
                        $sessionStartAccessDate = '';
                        if (!empty($results[$i]['session_id'])) {
                            $sessionInfo = api_get_session_info($results[$i]['session_id']);
                            if (!empty($sessionInfo)) {
                                $sessionName = $sessionInfo['name'];
                                $sessionStartAccessDate = api_get_local_time($sessionInfo['access_start_date']);
                            }
                        }

                        $objExercise = new Exercise($course_id);
                        if ($showExerciseCategories) {
                            // Getting attempt info
                            $exercise_stat_info = $objExercise->get_stat_track_exercise_info_by_exe_id($exeId);
                            if (!empty($exercise_stat_info['data_tracking'])) {
                                $question_list = explode(',', $exercise_stat_info['data_tracking']);
                                if (!empty($question_list)) {
                                    foreach ($question_list as $questionId) {
                                        $objQuestionTmp = Question::read($questionId, $objExercise->course);
                                        // We're inside *one* question. Go through each possible answer for this question
                                        $result = $objExercise->manage_answer(
                                            $exeId,
                                            $questionId,
                                            null,
                                            'exercise_result',
                                            false,
                                            false,
                                            true,
                                            false,
                                            $objExercise->selectPropagateNeg(),
                                            null,
                                            true
                                        );

                                        $my_total_score = $result['score'];
                                        $my_total_weight = $result['weight'];

                                        // Category report
                                        $category_was_added_for_this_test = false;
                                        if (isset($objQuestionTmp->category) && !empty($objQuestionTmp->category)) {
                                            if (!isset($category_list[$objQuestionTmp->category]['score'])) {
                                                $category_list[$objQuestionTmp->category]['score'] = 0;
                                            }
                                            if (!isset($category_list[$objQuestionTmp->category]['total'])) {
                                                $category_list[$objQuestionTmp->category]['total'] = 0;
                                            }
                                            $category_list[$objQuestionTmp->category]['score'] += $my_total_score;
                                            $category_list[$objQuestionTmp->category]['total'] += $my_total_weight;
                                            $category_was_added_for_this_test = true;
                                        }

                                        if (isset($objQuestionTmp->category_list) &&
                                            !empty($objQuestionTmp->category_list)
                                        ) {
                                            foreach ($objQuestionTmp->category_list as $category_id) {
                                                $category_list[$category_id]['score'] += $my_total_score;
                                                $category_list[$category_id]['total'] += $my_total_weight;
                                                $category_was_added_for_this_test = true;
                                            }
                                        }

                                        // No category for this question!
                                        if (false == $category_was_added_for_this_test) {
                                            if (!isset($category_list['none']['score'])) {
                                                $category_list['none']['score'] = 0;
                                            }
                                            if (!isset($category_list['none']['total'])) {
                                                $category_list['none']['total'] = 0;
                                            }

                                            $category_list['none']['score'] += $my_total_score;
                                            $category_list['none']['total'] += $my_total_weight;
                                        }
                                    }
                                }
                            }
                        }

                        foreach ($category_list as $categoryId => $result) {
                            $scoreToDisplay = self::show_score(
                                $result['score'],
                                $result['total'],
                                true,
                                true,
                                false,
                                false,
                                $decimalSeparator,
                                $thousandSeparator,
                                $roundValues
                            );
                            $results[$i]['category_'.$categoryId] = $scoreToDisplay;
                            $results[$i]['category_'.$categoryId.'_score_percentage'] = self::show_score(
                                $result['score'],
                                $result['total'],
                                true,
                                true,
                                true, // $show_only_percentage = false
                                true, // hide % sign
                                $decimalSeparator,
                                $thousandSeparator,
                                $roundValues
                            );
                            $results[$i]['category_'.$categoryId.'_only_score'] = $result['score'];
                            $results[$i]['category_'.$categoryId.'_total'] = $result['total'];
                        }
                        $results[$i]['session'] = $sessionName;
                        $results[$i]['session_access_start_date'] = $sessionStartAccessDate;
                        $results[$i]['status'] = $revisedLabel;
                        $results[$i]['score'] = $score;
                        $results[$i]['score_percentage'] = self::show_score(
                            $my_res,
                            $my_total,
                            true,
                            true,
                            true,
                            true,
                            $decimalSeparator,
                            $thousandSeparator,
                            $roundValues
                        );

                        if ($roundValues) {
                            $whole = floor($my_res); // 1
                            $fraction = $my_res - $whole; // .25
                            if ($fraction >= 0.5) {
                                $onlyScore = ceil($my_res);
                            } else {
                                $onlyScore = round($my_res);
                            }
                        } else {
                            $onlyScore = $scoreDisplay->format_score(
                                $my_res,
                                false,
                                $decimalSeparator,
                                $thousandSeparator
                            );
                        }

                        $results[$i]['only_score'] = $onlyScore;

                        if ($roundValues) {
                            $whole = floor($my_total); // 1
                            $fraction = $my_total - $whole; // .25
                            if ($fraction >= 0.5) {
                                $onlyTotal = ceil($my_total);
                            } else {
                                $onlyTotal = round($my_total);
                            }
                        } else {
                            $onlyTotal = $scoreDisplay->format_score(
                                $my_total,
                                false,
                                $decimalSeparator,
                                $thousandSeparator
                            );
                        }
                        $results[$i]['total'] = $onlyTotal;
                        $results[$i]['lp'] = $lp_name;
                        $results[$i]['actions'] = $actions;
                        $listInfo[] = $results[$i];
                    } else {
                        $results[$i]['status'] = $revisedLabel;
                        $results[$i]['score'] = $score;
                        $results[$i]['actions'] = $actions;
                        $listInfo[] = $results[$i];
                    }
                }
            }
        }

        return $listInfo;