TrackingCourseLog::getUserData()

Complexity

Conditions

66

Size

Total Lines	473
Code Lines	305

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

<?php
    public static function getUserData(
        $from,
        $numberOfItems,
        $column,
        $direction,
        array $conditions = [],
        bool $exerciseToCheckConfig = true,
        bool $displaySessionInfo = false,
        ?string $courseCode = null,
        ?int $sessionId = null,
        bool $exportCsv = false,
        array $userIds = []
    ) {
        $includeInvitedUsers = $conditions['include_invited_users'] ?? false;
        $getCount = $conditions['get_count'] ?? false;

        $csvContent = [];
        $tblUser = Database::get_main_table(TABLE_MAIN_USER);
        $tblUrlRelUser = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
        $accessUrlId = api_get_current_access_url_id();

        if (!empty($userIds) && is_array($userIds)) {
            $userIds = array_map('intval', $userIds);
            $conditionUser = " WHERE user.id IN (".implode(',', $userIds).") ";
        } else {
            $conditionUser = " WHERE user.id = " . (int) $userIds;
        }

        if (!empty($_GET['user_keyword'])) {
            $keyword = trim(Database::escape_string($_GET['user_keyword']));
            $conditionUser .= " AND (
            user.firstname LIKE '%".$keyword."%' OR
            user.lastname LIKE '%".$keyword."%'  OR
            user.username LIKE '%".$keyword."%'  OR
            user.email LIKE '%".$keyword."%'
         ) ";
        }

        $urlTable = '';
        $urlCondition = '';
        if (api_is_multiple_url_enabled()) {
            $urlTable = " INNER JOIN $tblUrlRelUser as url_users ON (user.id = url_users.user_id)";
            $urlCondition = " AND access_url_id = '$accessUrlId'";
        }

        $invitedUsersCondition = '';
        if (!$includeInvitedUsers) {
            $invitedUsersCondition = " AND user.status != ".INVITEE;
        }

        $select = '
            SELECT user.id as user_id,
                user.official_code  as col0,
                user.lastname       as col1,
                user.firstname      as col2,
                user.username       as col3,
                user.email          as col4';

        if ($getCount) {
            $select = ' SELECT COUNT(distinct(user.id)) as count ';
        }

        $sqlInjectJoins = '';
        $where = 'AND 1 = 1 ';
        $sqlInjectWhere = '';
        if (!empty($conditions)) {
            if (isset($conditions['inject_joins'])) {
                $sqlInjectJoins = $conditions['inject_joins'];
            }
            if (isset($conditions['where'])) {
                $where = $conditions['where'];
            }
            if (isset($conditions['inject_where'])) {
                $sqlInjectWhere = $conditions['inject_where'];
            }
            $injectExtraFields = !empty($conditions['inject_extra_fields']) ? $conditions['inject_extra_fields'] : 1;
            $injectExtraFields = rtrim($injectExtraFields, ', ');
            if (false === $getCount) {
                $select .= " , $injectExtraFields";
            }
        }

        $sql = "$select
            FROM $tblUser as user
            $urlTable
            $sqlInjectJoins
            $conditionUser
            $urlCondition
            $invitedUsersCondition
            $where
            $sqlInjectWhere
            ";

        if (!in_array($direction, ['ASC', 'DESC'])) {
            $direction = 'ASC';
        }

        $column = $column <= 2 ? (int) $column : 0;
        $from = (int) $from;
        $numberOfItems = (int) $numberOfItems;

        if ($getCount) {
            $res = Database::query($sql);
            $row = Database::fetch_array($res);

            return $row['count'];
        }

        $sortByFirstName = api_sort_by_first_name();

        if ($sortByFirstName) {
            if ($column == 1) {
                $column = 2;
            } elseif ($column == 2) {
                $column = 1;
            }
        }

        $sql .= " ORDER BY col$column $direction ";
        $sql .= " LIMIT $from, $numberOfItems";

        $res = Database::query($sql);
        $users = [];

        $courseInfo = api_get_course_info($courseCode);
        $courseId = $courseInfo['real_id'];

        $totalSurveys = 0;
        $totalExercises = ExerciseLib::get_all_exercises(
            $courseInfo,
            $sessionId
        );

        if (empty($sessionId)) {
            $surveyUserList = [];
            $surveyList = SurveyManager::get_surveys($courseCode);
            if ($surveyList) {
                $totalSurveys = count($surveyList);
                foreach ($surveyList as $survey) {
                    $userList = SurveyManager::get_people_who_filled_survey(
                        $survey['survey_id'],
                        false,
                        $courseId
                    );

                    foreach ($userList as $user_id) {
                        isset($surveyUserList[$user_id]) ? $surveyUserList[$user_id]++ : $surveyUserList[$user_id] = 1;
                    }
                }
            }
        }

        $urlBase = api_get_path(WEB_CODE_PATH).'my_space/myStudents.php?details=true&cid='.$courseId.
            '&course='.$courseCode.'&origin=tracking_course&sid='.$sessionId;

        Session::write('user_id_list', []);
        $userIdList = [];

        if ($exerciseToCheckConfig) {
            $addExerciseOption = api_get_setting('exercise.add_exercise_best_attempt_in_report', true);
            $exerciseResultsToCheck = [];
            if (!empty($addExerciseOption) && isset($addExerciseOption['courses']) &&
                isset($addExerciseOption['courses'][$courseCode])
            ) {
                foreach ($addExerciseOption['courses'][$courseCode] as $exerciseId) {
                    $exercise = new Exercise();
                    $exercise->read($exerciseId);
                    if ($exercise->iid) {
                        $exerciseResultsToCheck[] = $exercise;
                    }
                }
            }
        }

        $lpShowMaxProgress = 'true' === api_get_setting('lp.lp_show_max_progress_instead_of_average');
        if ('true' === api_get_setting('lp.lp_show_max_progress_or_average_enable_course_level_redefinition')) {
            $lpShowProgressCourseSetting = api_get_course_setting('lp_show_max_or_average_progress', $courseInfo, true);
            if (in_array($lpShowProgressCourseSetting, ['max', 'average'])) {
                $lpShowMaxProgress = ('max' === $lpShowProgressCourseSetting);
            }
        }

        while ($user = Database::fetch_array($res, 'ASSOC')) {
            $userIdList[] = $user['user_id'];
            $user['official_code'] = $user['col0'];
            $user['username'] = $user['col3'];
            $user['time'] = api_time_to_hms(
                Tracking::get_time_spent_on_the_course(
                    $user['user_id'],
                    $courseId,
                    $sessionId
                )
            );

            $avgStudentScore = Tracking::get_avg_student_score(
                $user['user_id'],
                api_get_course_entity($courseId),
                [],
                api_get_session_entity($sessionId)
            );

            $averageBestScore = Tracking::get_avg_student_score(
                $user['user_id'],
                api_get_course_entity($courseId),
                [],
                api_get_session_entity($sessionId),
                false,
                false,
                true
            );

            $avgStudentProgress = Tracking::get_avg_student_progress(
                $user['user_id'],
                api_get_course_entity($courseId),
                [],
                api_get_session_entity($sessionId)
            );

            if (empty($avgStudentProgress)) {
                $avgStudentProgress = 0;
            }
            $user['average_progress'] = $avgStudentProgress.'%';

            $totalUserExercise = Tracking::get_exercise_student_progress(
                $totalExercises,
                $user['user_id'],
                $courseId,
                $sessionId
            );

            $user['exercise_progress'] = $totalUserExercise;

            $totalUserExercise = Tracking::get_exercise_student_average_best_attempt(
                $totalExercises,
                $user['user_id'],
                $courseId,
                $sessionId
            );

            $user['exercise_average_best_attempt'] = $totalUserExercise;

            if (is_numeric($avgStudentScore)) {
                $user['student_score'] = $avgStudentScore.'%';
            } else {
                $user['student_score'] = $avgStudentScore;
            }

            if (is_numeric($averageBestScore)) {
                $user['student_score_best'] = $averageBestScore.'%';
            } else {
                $user['student_score_best'] = $averageBestScore;
            }

            $exerciseResults = [];
            if (!empty($exerciseResultsToCheck)) {
                foreach ($exerciseResultsToCheck as $exercise) {
                    $bestExerciseResult = Event::get_best_attempt_exercise_results_per_user(
                        $user['user_id'],
                        $exercise->iid,
                        $courseId,
                        $sessionId,
                        false
                    );

                    $best = null;
                    if ($bestExerciseResult) {
                        $best = $bestExerciseResult['exe_result'] / $bestExerciseResult['exe_weighting'];
                        $best = round($best, 2) * 100;
                        $best .= '%';
                    }
                    $exerciseResults['exercise_'.$exercise->iid] = $best;
                }
            }

            $user['first_connection'] = Tracking::get_first_connection_date_on_the_course(
                $user['user_id'],
                $courseId,
                $sessionId,
                !$exportCsv
            );

            $user['last_connection'] = Tracking::get_last_connection_date_on_the_course(
                $user['user_id'],
                $courseInfo,
                $sessionId,
                !$exportCsv
            );


            $user['count_assignments'] = Tracking::countStudentPublications(
                $courseId,
                $sessionId
            );

            $user['count_messages'] = Tracking::countStudentMessages(
                $courseId,
                $sessionId
            );


            $user['lp_finalization_date'] = Tracking::getCourseLpFinalizationDate(
                $user['user_id'],
                $courseId,
                $sessionId,
                !$exportCsv
            );

            $user['quiz_finalization_date'] = Tracking::getCourseQuizLastFinalizationDate(
                $user['user_id'],
                $courseId,
                $sessionId,
                !$exportCsv
            );

            if ($exportCsv) {
                if (!empty($user['first_connection'])) {
                    $user['first_connection'] = api_get_local_time($user['first_connection']);
                } else {
                    $user['first_connection'] = '-';
                }
                if (!empty($user['last_connection'])) {
                    $user['last_connection'] = api_get_local_time($user['last_connection']);
                } else {
                    $user['last_connection'] = '-';
                }
                if (!empty($user['lp_finalization_date'])) {
                    $user['lp_finalization_date'] = api_get_local_time($user['lp_finalization_date']);
                } else {
                    $user['lp_finalization_date'] = '-';
                }
                if (!empty($user['quiz_finalization_date'])) {
                    $user['quiz_finalization_date'] = api_get_local_time($user['quiz_finalization_date']);
                } else {
                    $user['quiz_finalization_date'] = '-';
                }
            }

            if (empty($sessionId)) {
                $user['survey'] = ($surveyUserList[$user['user_id']] ?? 0).' / '.$totalSurveys;
            }

            $url = $urlBase.'&student='.$user['user_id'];

            $user['link'] = '<a href="'.$url.'">
                        '.Display::return_icon('2rightarrow.png', get_lang('Details')).'
                         </a>';

            // store columns in array $users
            $userRow = [];
            if ($displaySessionInfo && !empty($sessionId)) {
                $sessionInfo = api_get_session_info($sessionId);
                $userRow['session_name'] = $sessionInfo['name'];
                $userRow['session_startdate'] = $sessionInfo['access_start_date'];
                $userRow['session_enddate'] = $sessionInfo['access_end_date'];
                $userRow['course_name'] = $courseInfo['name'];
            }
            $userRow['official_code'] = $user['official_code'];
            if ($sortByFirstName) {
                $userRow['firstname'] = $user['col2'];
                $userRow['lastname'] = $user['col1'];
            } else {
                $userRow['lastname'] = $user['col1'];
                $userRow['firstname'] = $user['col2'];
            }
            $userRow['username'] = $user['username'];
            $userRow['time'] = $user['time'];
            $userRow['average_progress'] = $user['average_progress'];
            $userRow['exercise_progress'] = $user['exercise_progress'];
            $userRow['exercise_average_best_attempt'] = $user['exercise_average_best_attempt'];
            $userRow['student_score'] = $user['student_score'];
            $userRow['student_score_best'] = $user['student_score_best'];
            if (!empty($exerciseResults)) {
                foreach ($exerciseResults as $exerciseId => $bestResult) {
                    $userRow[$exerciseId] = $bestResult;
                }
            }

            $userRow['count_assignments'] = $user['count_assignments'];
            $userRow['count_messages'] = $user['count_messages'];

            $userGroupManager = new UserGroupModel();
            if ($exportCsv) {
                $userRow['classes'] = implode(
                    ',',
                    $userGroupManager->getNameListByUser($user['user_id'], UserGroupModel::NORMAL_CLASS)
                );
            } else {
                $userRow['classes'] = $userGroupManager->getLabelsFromNameList(
                    $user['user_id'],
                    UserGroupModel::NORMAL_CLASS
                );
            }

            if (empty($sessionId)) {
                $userRow['survey'] = $user['survey'];
            } else {
                $userSession = SessionManager::getUserSession($user['user_id'], $sessionId);
                $userRow['registered_at'] = '';
                if ($userSession) {
                    $userRow['registered_at'] = api_get_local_time($userSession['registered_at']);
                }
            }

            $userRow['first_connection'] = $user['first_connection'];
            $userRow['last_connection'] = $user['last_connection'];

            $userRow['lp_finalization_date'] = $user['lp_finalization_date'];
            $userRow['quiz_finalization_date'] = $user['quiz_finalization_date'];

            // we need to display an additional profile field
            if (isset($_GET['additional_profile_field'])) {
                $data = Session::read('additional_user_profile_info');

                $extraFieldInfo = Session::read('extra_field_info');
                foreach ($_GET['additional_profile_field'] as $fieldId) {
                    if (isset($data[$fieldId]) && isset($data[$fieldId][$user['user_id']])) {
                        if (is_array($data[$fieldId][$user['user_id']])) {
                            $userRow[$extraFieldInfo[$fieldId]['variable']] = implode(
                                ', ',
                                $data[$fieldId][$user['user_id']]
                            );
                        } else {
                            $userRow[$extraFieldInfo[$fieldId]['variable']] = $data[$fieldId][$user['user_id']];
                        }
                    } else {
                        $userRow[$extraFieldInfo[$fieldId]['variable']] = '';
                    }
                }
            }

            $data = Session::read('default_additional_user_profile_info');
            $defaultExtraFieldInfo = Session::read('default_extra_field_info');
            if (isset($defaultExtraFieldInfo) && isset($data)) {
                foreach ($data as $key => $val) {
                    if (isset($val[$user['user_id']])) {
                        if (is_array($val[$user['user_id']])) {
                            $userRow[$defaultExtraFieldInfo[$key]['variable']] = implode(
                                ', ',
                                $val[$user['user_id']]
                            );
                        } else {
                            $userRow[$defaultExtraFieldInfo[$key]['variable']] = $val[$user['user_id']];
                        }
                    } else {
                        $userRow[$defaultExtraFieldInfo[$key]['variable']] = '';
                    }
                }
            }

            if (api_get_setting('show_email_addresses') === 'true') {
                $userRow['email'] = $user['col4'];
            }

            $userRow['link'] = $user['link'];

            if ($exportCsv) {
                unset($userRow['link']);
                $csvContent[] = $userRow;
            }
            $users[] = array_values($userRow);
        }

        if ($exportCsv) {
            Session::write('csv_content', $csvContent);
        }

        Session::erase('additional_user_profile_info');
        Session::erase('extra_field_info');
        Session::erase('default_additional_user_profile_info');
        Session::erase('default_extra_field_info');
        Session::write('user_id_list', $userIdList);

        return $users;
    }