/**
* Get the calculated progress for an user in a session
* @param int $sessionId The session ID
* @param int $userId The user ID
*
* @return float The progress
*/
public static function getSessionProgress($sessionId, $userId)
{
$courses = SessionManager::get_course_list_by_session_id($sessionId);
$progress = 0;
if (empty($courses)) {
return 0;
}
foreach ($courses as $course) {
$courseProgress = Tracking::get_avg_student_progress($userId, $course['code'], [], $sessionId, false, true);
if ($courseProgress === false) {
continue;
}
$progress += $courseProgress;
}
return round($progress / count($courses), 2);
}
function get_users($from, $limit, $column, $direction)
{
$active = isset($_GET['active']) ? $_GET['active'] : 1;
$keyword = isset($_GET['keyword']) ? Security::remove_XSS($_GET['keyword']) : null;
$sleepingDays = isset($_GET['sleeping_days']) ? intval($_GET['sleeping_days']) : null;
$lastConnectionDate = null;
if (!empty($sleepingDays)) {
$lastConnectionDate = api_get_utc_datetime(strtotime($sleepingDays . ' days ago'));
}
$is_western_name_order = api_is_western_name_order();
$coach_id = api_get_user_id();
$drhLoaded = false;
if (api_is_drh()) {
$column = 'u.user_id';
if (api_drh_can_access_all_session_content()) {
$students = SessionManager::getAllUsersFromCoursesFromAllSessionFromStatus('drh_all', api_get_user_id(), false, $from, $limit, $column, $direction, $keyword, $active, $lastConnectionDate, null, null, api_is_student_boss() ? null : STUDENT);
$drhLoaded = true;
}
}
if ($drhLoaded == false) {
$students = UserManager::getUsersFollowedByUser(api_get_user_id(), api_is_student_boss() ? null : STUDENT, false, false, false, $from, $limit, $column, $direction, $active, $lastConnectionDate, api_is_student_boss() ? STUDENT_BOSS : COURSEMANAGER, $keyword);
}
$all_datas = array();
foreach ($students as $student_data) {
$student_id = $student_data['user_id'];
if (isset($_GET['id_session'])) {
$courses = Tracking::get_course_list_in_session_from_student($student_id, $_GET['id_session']);
}
$avg_time_spent = $avg_student_score = $avg_student_progress = $total_assignments = $total_messages = 0;
$nb_courses_student = 0;
if (!empty($courses)) {
foreach ($courses as $course_code) {
$courseInfo = api_get_course_info($course_code);
$courseId = $courseInfo['real_id'];
if (CourseManager::is_user_subscribed_in_course($student_id, $course_code, true)) {
$avg_time_spent += Tracking::get_time_spent_on_the_course($student_id, $courseId, $_GET['id_session']);
$my_average = Tracking::get_avg_student_score($student_id, $course_code);
if (is_numeric($my_average)) {
$avg_student_score += $my_average;
}
$avg_student_progress += Tracking::get_avg_student_progress($student_id, $course_code);
$total_assignments += Tracking::count_student_assignments($student_id, $course_code);
$total_messages += Tracking::count_student_messages($student_id, $course_code);
$nb_courses_student++;
}
}
}
if ($nb_courses_student > 0) {
$avg_time_spent = $avg_time_spent / $nb_courses_student;
$avg_student_score = $avg_student_score / $nb_courses_student;
$avg_student_progress = $avg_student_progress / $nb_courses_student;
} else {
$avg_time_spent = null;
$avg_student_score = null;
$avg_student_progress = null;
}
$row = array();
if ($is_western_name_order) {
$row[] = $student_data['firstname'];
$row[] = $student_data['lastname'];
} else {
$row[] = $student_data['lastname'];
$row[] = $student_data['firstname'];
}
$string_date = Tracking::get_last_connection_date($student_id, true);
$first_date = Tracking::get_first_connection_date($student_id);
$row[] = $first_date;
$row[] = $string_date;
if (isset($_GET['id_coach']) && intval($_GET['id_coach']) != 0) {
$detailsLink = '<a href="myStudents.php?student=' . $student_id . '&id_coach=' . $coach_id . '&id_session=' . $_GET['id_session'] . '">
<img src="' . api_get_path(WEB_IMG_PATH) . '2rightarrow.gif" border="0" /></a>';
} else {
$detailsLink = '<a href="myStudents.php?student=' . $student_id . '">
<img src="' . api_get_path(WEB_IMG_PATH) . '2rightarrow.gif" border="0" /></a>';
}
$row[] = $detailsLink;
$all_datas[] = $row;
}
return $all_datas;
}
/**
* Return user info array of all users registered in a course
* This only returns the users that are registered in this actual course, not linked courses.
* @param string $course_code
* @param int $session_id
* @param string $limit
* @param string $order_by the field to order the users by.
* Valid values are 'lastname', 'firstname', 'username', 'email', 'official_code' OR a part of a SQL statement
* that starts with ORDER BY ...
* @param null $filter_by_status if using the session_id: 0 or 2 (student, coach),
* if using session_id = 0 STUDENT or COURSEMANAGER
* @param null $return_count
* @param bool $add_reports
* @param bool $resumed_report
* @param array $extra_field
* @param array $courseCodeList
* @param array $userIdList
* @param string $filterByActive
* @param array $sessionIdList
* @return array|int
*/
public static function get_user_list_from_course_code($course_code = null, $session_id = 0, $limit = null, $order_by = null, $filter_by_status = null, $return_count = null, $add_reports = false, $resumed_report = false, $extra_field = array(), $courseCodeList = array(), $userIdList = array(), $filterByActive = null, $sessionIdList = array())
{
$course_table = Database::get_main_table(TABLE_MAIN_COURSE);
$sessionTable = Database::get_main_table(TABLE_MAIN_SESSION);
$session_id = intval($session_id);
$course_code = Database::escape_string($course_code);
$courseInfo = api_get_course_info($course_code);
$courseId = 0;
if (!empty($courseInfo)) {
$courseId = $courseInfo['real_id'];
}
$where = array();
if (empty($order_by)) {
$order_by = 'user.lastname, user.firstname';
if (api_is_western_name_order()) {
$order_by = 'user.firstname, user.lastname';
}
}
// if the $order_by does not contain 'ORDER BY'
// we have to check if it is a valid field that can be sorted on
if (!strstr($order_by, 'ORDER BY')) {
if (!empty($order_by)) {
$order_by = 'ORDER BY ' . $order_by;
} else {
$order_by = '';
}
}
$filter_by_status_condition = null;
if (!empty($session_id) || !empty($sessionIdList)) {
$sql = 'SELECT DISTINCT
user.user_id,
user.email,
session_course_user.status as status_session,
session_id,
user.*,
course.*,
session.name as session_name
';
if ($return_count) {
$sql = " SELECT COUNT(user.user_id) as count";
}
$sessionCondition = " session_course_user.session_id = {$session_id}";
if (!empty($sessionIdList)) {
$sessionIdListTostring = implode("','", array_map('intval', $sessionIdList));
$sessionCondition = " session_course_user.session_id IN ('{$sessionIdListTostring}') ";
}
$courseCondition = " course.id = {$courseId}";
if (!empty($courseCodeList)) {
$courseCodeListForSession = array_map(array('Database', 'escape_string'), $courseCodeList);
$courseCodeListForSession = implode('","', $courseCodeListForSession);
$courseCondition = ' course.code IN ("' . $courseCodeListForSession . '") ';
}
$sql .= ' FROM ' . Database::get_main_table(TABLE_MAIN_USER) . ' as user ';
$sql .= " LEFT JOIN " . Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER) . " as session_course_user\n ON\n user.user_id = session_course_user.user_id AND\n {$sessionCondition}\n INNER JOIN {$course_table} course ON session_course_user.c_id = course.id AND\n {$courseCondition}\n INNER JOIN {$sessionTable} session ON session_course_user.session_id = session.id\n ";
$where[] = ' session_course_user.c_id IS NOT NULL ';
// 2 = coach
// 0 = student
if (isset($filter_by_status)) {
$filter_by_status = intval($filter_by_status);
$filter_by_status_condition = " session_course_user.status = {$filter_by_status} AND ";
}
} else {
if ($return_count) {
$sql = " SELECT COUNT(*) as count";
if ($resumed_report) {
//$sql = " SELECT count(field_id) ";
}
} else {
if (empty($course_code)) {
$sql = 'SELECT DISTINCT
course.title,
course.code,
course_rel_user.status as status_rel,
user.user_id,
user.email,
course_rel_user.is_tutor,
user.* ';
} else {
$sql = 'SELECT DISTINCT
course_rel_user.status as status_rel,
user.user_id,
user.email,
course_rel_user.is_tutor,
user.* ';
}
}
$sql .= ' FROM ' . Database::get_main_table(TABLE_MAIN_USER) . ' as user ';
$sql .= ' LEFT JOIN ' . Database::get_main_table(TABLE_MAIN_COURSE_USER) . ' as course_rel_user
ON user.user_id = course_rel_user.user_id AND
course_rel_user.relation_type <> ' . COURSE_RELATION_TYPE_RRHH . ' ';
$sql .= " INNER JOIN {$course_table} course ON course_rel_user.c_id = course.id ";
if (!empty($course_code)) {
$sql .= ' AND course_rel_user.c_id="' . $courseId . '"';
}
$where[] = ' course_rel_user.c_id IS NOT NULL ';
if (isset($filter_by_status) && is_numeric($filter_by_status)) {
$filter_by_status = intval($filter_by_status);
$filter_by_status_condition = " course_rel_user.status = {$filter_by_status} AND ";
}
}
$multiple_access_url = api_get_multiple_access_url();
if ($multiple_access_url) {
$sql .= ' LEFT JOIN ' . Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER) . ' au
ON (au.user_id = user.user_id) ';
}
$extraFieldWasAdded = false;
if ($return_count && $resumed_report) {
foreach ($extra_field as $extraField) {
$extraFieldInfo = UserManager::get_extra_field_information_by_name($extraField);
if (!empty($extraFieldInfo)) {
$fieldValuesTable = Database::get_main_table(TABLE_EXTRA_FIELD_VALUES);
$sql .= ' LEFT JOIN ' . $fieldValuesTable . ' as ufv
ON (
user.user_id = ufv.item_id AND
(field_id = ' . $extraFieldInfo['id'] . ' OR field_id IS NULL)
)';
$extraFieldWasAdded = true;
}
}
}
$sql .= ' WHERE ' . $filter_by_status_condition . ' ' . implode(' OR ', $where);
if ($multiple_access_url) {
$current_access_url_id = api_get_current_access_url_id();
$sql .= " AND (access_url_id = {$current_access_url_id} ) ";
}
if ($return_count && $resumed_report && $extraFieldWasAdded) {
$sql .= ' AND field_id IS NOT NULL GROUP BY value ';
}
if (!empty($courseCodeList)) {
$courseCodeList = array_map(array('Database', 'escape_string'), $courseCodeList);
$courseCodeList = implode('","', $courseCodeList);
if (empty($sessionIdList)) {
$sql .= ' AND course.code IN ("' . $courseCodeList . '")';
}
}
if (!empty($userIdList)) {
$userIdList = array_map('intval', $userIdList);
$userIdList = implode('","', $userIdList);
$sql .= ' AND user.user_id IN ("' . $userIdList . '")';
}
if (isset($filterByActive)) {
$filterByActive = intval($filterByActive);
$sql .= ' AND user.active = ' . $filterByActive;
}
$sql .= ' ' . $order_by . ' ' . $limit;
$rs = Database::query($sql);
$users = array();
$extra_fields = UserManager::get_extra_fields(0, 100, null, null, true, true);
$counter = 1;
$count_rows = Database::num_rows($rs);
if ($return_count && $resumed_report) {
return $count_rows;
}
$table_user_field_value = Database::get_main_table(TABLE_EXTRA_FIELD_VALUES);
$tableExtraField = Database::get_main_table(TABLE_EXTRA_FIELD);
if ($count_rows) {
while ($user = Database::fetch_array($rs)) {
if ($return_count) {
return $user['count'];
}
$report_info = array();
$user_info = $user;
$user_info['status'] = $user['status'];
if (isset($user['is_tutor'])) {
$user_info['is_tutor'] = $user['is_tutor'];
}
if (!empty($session_id)) {
$user_info['status_session'] = $user['status_session'];
}
$sessionId = isset($user['session_id']) ? $user['session_id'] : 0;
$course_code = isset($user['code']) ? $user['code'] : null;
if ($add_reports) {
if ($resumed_report) {
$extra = array();
if (!empty($extra_fields)) {
foreach ($extra_fields as $extra) {
if (in_array($extra['1'], $extra_field)) {
$user_data = UserManager::get_extra_user_data_by_field($user['user_id'], $extra['1']);
break;
}
}
}
$row_key = '-1';
$name = '-';
if (!empty($extra)) {
if (!empty($user_data[$extra['1']])) {
$row_key = $user_data[$extra['1']];
$name = $user_data[$extra['1']];
$users[$row_key]['extra_' . $extra['1']] = $name;
}
}
$users[$row_key]['training_hours'] += Tracking::get_time_spent_on_the_course($user['user_id'], $courseId, $sessionId);
$users[$row_key]['count_users'] += $counter;
$registered_users_with_extra_field = 0;
if (!empty($name) && $name != '-') {
$extraFieldType = EntityExtraField::COURSE_FIELD_TYPE;
$name = Database::escape_string($name);
$sql = "SELECT count(v.item_id) as count\n FROM {$table_user_field_value} v INNER JOIN\n {$tableExtraField} f\n ON (f.id = v.field_id)\n WHERE value = '{$name}' AND extra_field_type = {$extraFieldType}";
$result_count = Database::query($sql);
if (Database::num_rows($result_count)) {
$row_count = Database::fetch_array($result_count);
$registered_users_with_extra_field = $row_count['count'];
}
}
$users[$row_key]['count_users_registered'] = $registered_users_with_extra_field;
$users[$row_key]['average_hours_per_user'] = $users[$row_key]['training_hours'] / $users[$row_key]['count_users'];
$category = Category::load(null, null, $course_code, null, null, $sessionId);
if (!isset($users[$row_key]['count_certificates'])) {
$users[$row_key]['count_certificates'] = 0;
}
if (isset($category[0]) && $category[0]->is_certificate_available($user['user_id'])) {
$users[$row_key]['count_certificates']++;
}
foreach ($extra_fields as $extra) {
if ($extra['1'] == 'ruc') {
continue;
}
if (!isset($users[$row_key][$extra['1']])) {
$user_data = UserManager::get_extra_user_data_by_field($user['user_id'], $extra['1']);
if (!empty($user_data[$extra['1']])) {
$users[$row_key][$extra['1']] = $user_data[$extra['1']];
}
}
}
} else {
$sessionName = !empty($sessionId) ? ' - ' . $user['session_name'] : '';
$report_info['course'] = $user['title'] . $sessionName;
$report_info['user'] = api_get_person_name($user['firstname'], $user['lastname']);
$report_info['email'] = $user['email'];
$report_info['time'] = api_time_to_hms(Tracking::get_time_spent_on_the_course($user['user_id'], $courseId, $sessionId));
$category = Category::load(null, null, $course_code, null, null, $sessionId);
$report_info['certificate'] = Display::label(get_lang('No'));
if (isset($category[0]) && $category[0]->is_certificate_available($user['user_id'])) {
$report_info['certificate'] = Display::label(get_lang('Yes'), 'success');
}
$progress = intval(Tracking::get_avg_student_progress($user['user_id'], $course_code, array(), $sessionId));
$report_info['progress_100'] = $progress == 100 ? Display::label(get_lang('Yes'), 'success') : Display::label(get_lang('No'));
$report_info['progress'] = $progress . "%";
foreach ($extra_fields as $extra) {
$user_data = UserManager::get_extra_user_data_by_field($user['user_id'], $extra['1']);
$report_info[$extra['1']] = $user_data[$extra['1']];
}
$report_info['user_id'] = $user['user_id'];
$users[] = $report_info;
}
} else {
$users[$user['user_id']] = $user_info;
}
}
}
return $users;
}
$avg_assignments_in_course = $avg_messages_in_course = $avg_progress_in_course = $avg_score_in_course = $avg_time_spent_in_course = 0;
// students directly subscribed to the course
if (empty($id_session)) {
$sql = "SELECT user_id FROM {$tbl_user_course} as course_rel_user\n\t\t\t WHERE course_rel_user.status='5' AND course_rel_user.c_id =" . $courseId;
} else {
$sql = "SELECT id_user as user_id FROM {$tbl_session_course_user} srcu\n\t\t\t WHERE srcu.c_id='{$courseId}' AND id_session = '{$id_session}' AND srcu.status<>2";
}
$rs = Database::query($sql);
$users = array();
while ($row = Database::fetch_array($rs)) {
$users[] = $row['user_id'];
}
if (count($users) > 0) {
$nb_students_in_course = count($users);
// tracking datas
$avg_progress_in_course = Tracking::get_avg_student_progress($users, $courseId, array(), $id_session);
$avg_score_in_course = Tracking::get_avg_student_score($users, $courseId, array(), $id_session);
$avg_time_spent_in_course = Tracking::get_time_spent_on_the_course($users, $courseId, $id_session);
$messages_in_course = Tracking::count_student_messages($users, $courseId, $id_session);
$assignments_in_course = Tracking::count_student_assignments($users, $courseId, $id_session);
$avg_time_spent_in_course = api_time_to_hms($avg_time_spent_in_course / $nb_students_in_course);
$avg_progress_in_course = round($avg_progress_in_course / $nb_students_in_course, 2);
if (is_numeric($avg_score_in_course)) {
$avg_score_in_course = round($avg_score_in_course / $nb_students_in_course, 2) . '%';
}
} else {
$avg_time_spent_in_course = null;
$avg_progress_in_course = null;
$avg_score_in_course = null;
$messages_in_course = null;
$assignments_in_course = null;
$nb_inactive_students++;
}
} else {
$nb_inactive_students++;
}
$total_time_spent += Tracking::get_time_spent_on_the_platform($student_id);
$total_courses += Tracking::count_course_per_student($student_id);
$avg_student_progress = 0;
$avg_student_score = 0;
$nb_courses_student = 0;
foreach ($courses as $courseId) {
if (CourseManager::is_user_subscribed_in_course($student_id, $courseId, true)) {
$nb_courses_student++;
$nb_posts += Tracking::count_student_messages($student_id, $courseId);
$nb_assignments += Tracking::count_student_assignments($student_id, $courseId);
$avg_student_progress += Tracking::get_avg_student_progress($student_id, $courseId);
$myavg_temp = Tracking::get_avg_student_score($student_id, $courseId);
if (is_numeric($myavg_temp)) {
$avg_student_score += $myavg_temp;
}
if ($nb_posts !== null && $nb_assignments !== null && $avg_student_progress !== null && $avg_student_score !== null) {
//if one of these scores is null, it means that we had a problem connecting to the right database, so don't count it in
$nb_courses_student++;
}
}
}
// average progress of the student
$avg_student_progress = $nb_courses_student ? $avg_student_progress / $nb_courses_student : 0;
$avg_total_progress += $avg_student_progress;
// average test results of the student
$avg_student_score = $avg_student_score ? $avg_student_score / $nb_courses_student : 0;
$isAllowedToEdit = api_is_allowed_to_edit(null, true);
if (!$isAllowedToEdit) {
api_not_allowed(true);
}
$lpTable = Database::get_course_table(TABLE_LP_MAIN);
$lpId = isset($_GET['lp_id']) ? boolval($_GET['lp_id']) : false;
$sessionId = api_get_session_id();
$courseId = api_get_course_int_id();
$courseCode = api_get_course_id();
$sessionUsers = SessionManager::get_users_by_session($sessionId, 0);
$userList = [];
$lpInfo = Database::select('*', $lpTable, array('where' => array('c_id = ? AND ' => $courseId, 'id = ?' => $lpId)), 'first');
foreach ($sessionUsers as $user) {
$lpTime = Tracking::get_time_spent_in_lp($user['user_id'], $courseCode, array($lpId), $sessionId);
$lpScore = Tracking::get_avg_student_score($user['user_id'], $courseCode, array($lpId), $sessionId);
$lpPogress = Tracking::get_avg_student_progress($user['user_id'], $courseCode, array($lpId), $sessionId);
$lpLastConnection = Tracking::get_last_connection_time_in_lp($user['user_id'], $courseCode, array($lpId), $sessionId);
$lpLastConnection = empty($lpLastConnection) ? '-' : api_convert_and_format_date($lpLastConnection, DATE_TIME_FORMAT_LONG);
$userList[] = ['id' => $user['user_id'], 'first_name' => $user['firstname'], 'last_name' => $user['lastname'], 'lp_time' => api_time_to_hms($lpTime), 'lp_score' => is_numeric($lpScore) ? "{$lpScore}%" : $lpScore, 'lp_progress' => "{$lpPogress}%", 'lp_last_connection' => $lpLastConnection];
}
// View
$interbreadcrumb[] = ['url' => api_get_path(WEB_CODE_PATH) . 'newscorm/lp_controller.php', 'name' => get_lang('LearningPaths')];
$actions = Display::url(Display::return_icon('back.png', get_lang('Back'), array(), ICON_SIZE_MEDIUM), api_get_path(WEB_CODE_PATH) . 'newscorm/lp_controller.php?' . api_get_cidreq());
$template = new Template(get_lang('StudentScore'));
$template->assign('user_list', $userList);
$template->assign('session_id', api_get_session_id());
$template->assign('course_code', api_get_course_id());
$template->assign('lp_id', $lpId);
$layout = $template->get_template('learnpath/report.tpl');
$template->assign('header', $lpInfo['name']);
$template->assign('actions', $actions);
/**
* Gets the progress of the given session
* @param int session id
* @param array options order and limit keys
* @return array table with user name, lp name, progress
*/
public static function get_session_progress($sessionId, $courseId, $date_from, $date_to, $options)
{
$sessionId = intval($sessionId);
$getAllSessions = false;
if (empty($sessionId)) {
$sessionId = 0;
$getAllSessions = true;
}
//tables
$session_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
$user = Database::get_main_table(TABLE_MAIN_USER);
$workTable = Database::get_course_table(TABLE_STUDENT_PUBLICATION);
$workTableAssignment = Database::get_course_table(TABLE_STUDENT_PUBLICATION_ASSIGNMENT);
$forum = Database::get_course_table(TABLE_FORUM);
$forum_post = Database::get_course_table(TABLE_FORUM_POST);
$tbl_course_lp = Database::get_course_table(TABLE_LP_MAIN);
$wiki = Database::get_course_table(TABLE_WIKI);
$table_stats_default = Database::get_main_table(TABLE_STATISTIC_TRACK_E_DEFAULT);
$table_stats_access = Database::get_main_table(TABLE_STATISTIC_TRACK_E_ACCESS);
$course = api_get_course_info_by_id($courseId);
$where = " WHERE c_id = '%s' AND s.status <> 2 ";
$limit = null;
if (!empty($options['limit'])) {
$limit = " LIMIT " . $options['limit'];
}
if (!empty($options['where'])) {
$where .= ' ' . $options['where'];
}
$order = null;
if (!empty($options['order'])) {
$order = " ORDER BY " . $options['order'];
}
//TODO, fix create report without session
$queryVariables = array($course['real_id']);
if (!empty($sessionId)) {
$where .= ' AND session_id = %s';
$queryVariables[] = $sessionId;
$sql = "SELECT\n u.user_id, u.lastname, u.firstname, u.username,\n u.email, s.c_id, s.session_id\n FROM {$session_course_user} s\n INNER JOIN {$user} u\n ON u.user_id = s.user_id\n {$where} {$order} {$limit}";
} else {
$sql = "SELECT\n u.user_id, u.lastname, u.firstname, u.username,\n u.email, s.c_id, s.session_id\n FROM {$session_course_user} s\n INNER JOIN {$user} u ON u.user_id = s.user_id\n {$where} {$order} {$limit}";
}
$sql_query = vsprintf($sql, $queryVariables);
$rs = Database::query($sql_query);
while ($user = Database::fetch_array($rs)) {
$users[$user['user_id']] = $user;
}
/**
* Lessons
*/
$sql = "SELECT * FROM {$tbl_course_lp} WHERE c_id = %s ";
//AND session_id = %s
$sql_query = sprintf($sql, $course['real_id']);
$result = Database::query($sql_query);
$arrLesson = array(array());
while ($row = Database::fetch_array($result)) {
if (empty($arrLesson[$row['session_id']]['lessons_total'])) {
$arrLesson[$row['session_id']]['lessons_total'] = 1;
} else {
$arrLesson[$row['session_id']]['lessons_total']++;
}
}
/**
* Exercises
*/
$exercises = ExerciseLib::get_all_exercises($course, $sessionId, false, '', $getAllSessions);
$exercises_total = count($exercises);
/**
* Assignments
*/
//total
if ($getAllSessions) {
$sql = "SELECT count(w.id) as count\n FROM {$workTable} w\n LEFT JOIN {$workTableAssignment} a\n ON (a.publication_id = w.id AND a.c_id = w.c_id)\n WHERE w.c_id = %s\n AND parent_id = 0\n AND active IN (1, 0)";
} else {
$sql = "SELECT count(w.id) as count\n FROM {$workTable} w\n LEFT JOIN {$workTableAssignment} a\n ON (a.publication_id = w.id AND a.c_id = w.c_id)\n WHERE w.c_id = %s\n AND parent_id = 0\n AND active IN (1, 0)\n AND session_id = %s";
}
$sql_query = sprintf($sql, $course['real_id'], $sessionId);
$result = Database::query($sql_query);
$row = Database::fetch_array($result);
$assignments_total = $row['count'];
/**
* Wiki
*/
if ($getAllSessions) {
$sql = "SELECT count(distinct page_id) as count FROM {$wiki}\n WHERE c_id = %s";
} else {
$sql = "SELECT count(distinct page_id) as count FROM {$wiki}\n WHERE c_id = %s and session_id = %s";
}
$sql_query = sprintf($sql, $course['real_id'], $sessionId);
$result = Database::query($sql_query);
$row = Database::fetch_array($result);
$wiki_total = $row['count'];
/**
* Surveys
*/
$survey_user_list = array();
$survey_list = SurveyManager::get_surveys($course['code'], $sessionId);
$surveys_total = count($survey_list);
foreach ($survey_list as $survey) {
$user_list = SurveyManager::get_people_who_filled_survey($survey['survey_id'], false, $course['real_id']);
foreach ($user_list as $user_id) {
isset($survey_user_list[$user_id]) ? $survey_user_list[$user_id]++ : ($survey_user_list[$user_id] = 1);
}
}
/**
* Forums
*/
$forums_total = CourseManager::getCountForum($course['real_id'], $sessionId, $getAllSessions);
//process table info
foreach ($users as $user) {
//Course description
$sql = "SELECT count(*) as count\n FROM {$table_stats_access}\n WHERE access_tool = 'course_description'\n AND c_id = '%s'\n AND access_session_id = %s\n AND access_user_id = %s ";
$sql_query = sprintf($sql, $course['real_id'], $user['id_session'], $user['user_id']);
$result = Database::query($sql_query);
$row = Database::fetch_array($result);
$course_description_progress = $row['count'] > 0 ? 100 : 0;
if (!empty($arrLesson[$user['id_session']]['lessons_total'])) {
$lessons_total = $arrLesson[$user['id_session']]['lessons_total'];
} else {
$lessons_total = !empty($arrLesson[0]['lessons_total']) ? $arrLesson[0]['lessons_total'] : 0;
}
//Lessons
//TODO: Lessons done and left is calculated by progress per item in lesson, maybe we should calculate it only per completed lesson?
$lessons_progress = Tracking::get_avg_student_progress($user['user_id'], $course['code'], array(), $user['id_session']);
$lessons_done = $lessons_progress * $lessons_total / 100;
$lessons_left = $lessons_total - $lessons_done;
//Exercises
$exercises_progress = str_replace('%', '', Tracking::get_exercise_student_progress($exercises, $user['user_id'], $course['real_id'], $user['id_session']));
$exercises_done = round($exercises_progress * $exercises_total / 100);
$exercises_left = $exercises_total - $exercises_done;
//Assignments
$assignments_done = Tracking::count_student_assignments($user['user_id'], $course['code'], $user['id_session']);
$assignments_left = $assignments_total - $assignments_done;
if (!empty($assignments_total)) {
$assignments_progress = round($assignments_done * 100 / $assignments_total, 2);
} else {
$assignments_progress = 0;
}
//Wiki
//total revisions per user
$sql = "SELECT count(*) as count\n FROM {$wiki}\n WHERE c_id = %s and session_id = %s and user_id = %s";
$sql_query = sprintf($sql, $course['real_id'], $user['id_session'], $user['user_id']);
$result = Database::query($sql_query);
$row = Database::fetch_array($result);
$wiki_revisions = $row['count'];
//count visited wiki pages
$sql = "SELECT count(distinct default_value) as count\n FROM {$table_stats_default}\n WHERE\n default_user_id = %s AND\n default_event_type = 'wiki_page_view' AND\n default_value_type = 'wiki_page_id' AND\n c_id = %s\n ";
$sql_query = sprintf($sql, $user['user_id'], $course['real_id']);
$result = Database::query($sql_query);
$row = Database::fetch_array($result);
$wiki_read = $row['count'];
$wiki_unread = $wiki_total - $wiki_read;
if (!empty($wiki_total)) {
$wiki_progress = round($wiki_read * 100 / $wiki_total, 2);
} else {
$wiki_progress = 0;
}
//Surveys
$surveys_done = isset($survey_user_list[$user['user_id']]) ? $survey_user_list[$user['user_id']] : 0;
$surveys_left = $surveys_total - $surveys_done;
if (!empty($surveys_total)) {
$surveys_progress = round($surveys_done * 100 / $surveys_total, 2);
} else {
$surveys_progress = 0;
}
//Forums
$forums_done = CourseManager::getCountForumPerUser($user['user_id'], $course['real_id'], $user['id_session']);
$forums_left = $forums_total - $forums_done;
if (!empty($forums_total)) {
$forums_progress = round($forums_done * 100 / $forums_total, 2);
} else {
$forums_progress = 0;
}
//Overall Total
$overall_total = ($course_description_progress + $exercises_progress + $forums_progress + $assignments_progress + $wiki_progress + $surveys_progress) / 6;
$link = '<a href="' . api_get_path(WEB_CODE_PATH) . 'mySpace/myStudents.php?student=' . $user[0] . '&details=true&course=' . $course['code'] . '&id_session=' . $user['id_session'] . '"> %s </a>';
$linkForum = '<a href="' . api_get_path(WEB_CODE_PATH) . 'forum/index.php?cidReq=' . $course['code'] . '&id_session=' . $user['id_session'] . '"> %s </a>';
$linkWork = '<a href="' . api_get_path(WEB_CODE_PATH) . 'work/work.php?cidReq=' . $course['code'] . '&id_session=' . $user['id_session'] . '"> %s </a>';
$linkWiki = '<a href="' . api_get_path(WEB_CODE_PATH) . 'wiki/index.php?cidReq=' . $course['code'] . '&session_id=' . $user['id_session'] . '&action=statistics"> %s </a>';
$linkSurvey = '<a href="' . api_get_path(WEB_CODE_PATH) . 'survey/survey_list.php?cidReq=' . $course['code'] . '&id_session=' . $user['id_session'] . '"> %s </a>';
$table[] = array('lastname' => $user[1], 'firstname' => $user[2], 'username' => $user[3], 'total' => round($overall_total, 2) . '%', 'courses' => sprintf($link, $course_description_progress . '%'), 'lessons' => sprintf($link, $lessons_progress . '%'), 'exercises' => sprintf($link, $exercises_progress . '%'), 'forums' => sprintf($link, $forums_progress . '%'), 'homeworks' => sprintf($link, $assignments_progress . '%'), 'wikis' => sprintf($link, $wiki_progress . '%'), 'surveys' => sprintf($link, $surveys_progress . '%'), 'course_description_progress' => $course_description_progress . '%', 'lessons_total' => sprintf($link, $lessons_total), 'lessons_done' => sprintf($link, $lessons_done), 'lessons_left' => sprintf($link, $lessons_left), 'lessons_progress' => sprintf($link, $lessons_progress . '%'), 'exercises_total' => sprintf($link, $exercises_total), 'exercises_done' => sprintf($link, $exercises_done), 'exercises_left' => sprintf($link, $exercises_left), 'exercises_progress' => sprintf($link, $exercises_progress . '%'), 'forums_total' => sprintf($linkForum, $forums_total), 'forums_done' => sprintf($linkForum, $forums_done), 'forums_left' => sprintf($linkForum, $forums_left), 'forums_progress' => sprintf($linkForum, $forums_progress . '%'), 'assignments_total' => sprintf($linkWork, $assignments_total), 'assignments_done' => sprintf($linkWork, $assignments_done), 'assignments_left' => sprintf($linkWork, $assignments_left), 'assignments_progress' => sprintf($linkWork, $assignments_progress . '%'), 'wiki_total' => sprintf($linkWiki, $wiki_total), 'wiki_revisions' => sprintf($linkWiki, $wiki_revisions), 'wiki_read' => sprintf($linkWiki, $wiki_read), 'wiki_unread' => sprintf($linkWiki, $wiki_unread), 'wiki_progress' => sprintf($linkWiki, $wiki_progress . '%'), 'surveys_total' => sprintf($linkSurvey, $surveys_total), 'surveys_done' => sprintf($linkSurvey, $surveys_done), 'surveys_left' => sprintf($linkSurvey, $surveys_left), 'surveys_progress' => sprintf($linkSurvey, $surveys_progress . '%'));
}
return $table;
}
if (!api_is_platform_admin(true) && $_user['status'] != DRH) {
// courses followed by user where we are coach
if (!isset($_GET['id_coach'])) {
$a_courses = Tracking::get_courses_followed_by_coach($_user['user_id']);
} else {
$a_courses = Tracking::get_courses_followed_by_coach(Security::remove_XSS($_GET['id_coach']));
}
}
if (count($a_courses) > 0) {
$csv_content[] = array();
$csv_content[] = array(get_lang('Course'), get_lang('Time'), get_lang('Progress'), get_lang('Score'));
foreach ($a_courses as $course_code) {
if (CourseManager::is_user_subscribed_in_course($student_id, $course_code, true)) {
$course_infos = CourseManager::get_course_information($course_code);
$time_spent_on_course = api_time_to_hms(Tracking::get_time_spent_on_the_course($a_infosUser['user_id'], $course_code));
$progress = Tracking::get_avg_student_progress($a_infosUser['user_id'], $course_code) . ' %';
$score = Tracking::get_avg_student_score($a_infosUser['user_id'], $course_code) . ' %';
$csv_content[] = array($course_infos['title'], $time_spent_on_course, $progress, $score);
echo '
<tr>
<td align="right">
' . $course_infos['title'] . '
</td>
<td align="right">
' . $time_spent_on_course . '
</td>
<td align="right">
' . $progress . '
</td>
<td align="right">
' . $score . '
$table->set_header(7, get_lang('Messages'), false);
$table->set_header(8, get_lang('FirstLogin'), false, 'align="center"');
$table->set_header(9, get_lang('LatestLogin'), false, 'align="center"');
$table->set_header(10, get_lang('Details'), false);
if ($export_csv) {
$csv_content[] = array();
}
$all_datas = array();
$course_code = $_course['id'];
foreach ($a_students as $student_id => $student) {
$student_datas = UserManager::get_user_info_by_id($student_id);
$avg_time_spent = $avg_student_score = $avg_student_progress = $total_assignments = $total_messages = 0;
$nb_courses_student = 0;
$avg_time_spent = Tracking::get_time_spent_on_the_course($student_id, $course_code);
$avg_student_score = Tracking::get_average_test_scorm_and_lp($student_id, $course_code);
$avg_student_progress = Tracking::get_avg_student_progress($student_id, $course_code);
$total_assignments = Tracking::count_student_assignments($student_id, $course_code);
$total_messages = Tracking::count_student_messages($student_id, $course_code);
$row = array();
$row[] = $student_datas['official_code'];
$row[] = $student_datas['lastname'];
$row[] = $student_datas['firstname'];
$row[] = api_time_to_hms($avg_time_spent);
if (is_null($avg_student_score)) {
$avg_student_score = 0;
}
if (is_null($avg_student_progress)) {
$avg_student_progress = 0;
}
$row[] = $avg_student_progress . ' %';
$row[] = $avg_student_score . ' %';
</th>
<?php
echo '<th>' . get_lang('Details') . '</th>';
if (api_is_allowed_to_edit()) {
echo '<th>' . get_lang('ResetLP') . '</th>';
}
?>
</tr>
<?php
$i = 0;
while ($learnpath = Database::fetch_array($rs_lp)) {
$lp_id = intval($learnpath['id']);
$lp_name = $learnpath['name'];
$any_result = false;
// Get progress in lp
$progress = Tracking::get_avg_student_progress($student_id, $course_code, array($lp_id), $sessionId);
if ($progress === null) {
$progress = '0%';
} else {
$any_result = true;
}
// Get time in lp
$total_time = Tracking::get_time_spent_in_lp($student_id, $course_code, array($lp_id), $sessionId);
if (!empty($total_time)) {
$any_result = true;
}
// Get last connection time in lp
$start_time = Tracking::get_last_connection_time_in_lp($student_id, $course_code, $lp_id, $sessionId);
if (!empty($start_time)) {
$start_time = api_convert_and_format_date($start_time, DATE_TIME_FORMAT_LONG);
} else {
$total_time_spent = 0;
$total_courses = 0;
$avg_total_progress = 0;
$avg_results_to_exercises = 0;
$nb_inactive_students = 0;
$nb_posts = $nb_assignments = 0;
$inactiveTime = time() - 3600 * 24 * 7;
$nb_students = 0;
$numberTeachers = 0;
$countHumanResourcesUsers = 0;
$daysAgo = 7;
$studentIds = array();
if (!empty($students)) {
// Students
$nb_students = count($students);
$progress = Tracking::get_avg_student_progress($studentIds);
$countAssignments = Tracking::count_student_assignments($studentIds);
$studentIds = array_values($students);
$countHumanResourcesUsers = count($humanResourcesUsers);
// average progress
$avg_total_progress = $progress / $nb_students;
// average assignments
$nb_assignments = $countAssignments / $nb_students;
$avg_courses_per_student = $count_courses / $nb_students;
}
if (!empty($teachers)) {
$numberTeachers = count($teachers);
}
// Inactive students
//$countInactiveUsers = Tracking::getInactiveUsers($studentIds, $daysAgo);
$totalTimeSpent = Tracking::get_time_spent_on_the_platform($studentIds);
/**
* Return user info array of all users registered in the specified course
* This only returns the users that are registered in this actual course, not linked courses.
*
* @param string $course_code the code of the course
* @param boolean $with_session determines if the course is used in a session or not
* @param integer $session_id the id of the session
* @param string $limit the LIMIT statement of the sql statement
* @param string $order_by the field to order the users by. Valid values are 'lastname', 'firstname', 'username', 'email', 'official_code' OR a part of a SQL statement that starts with ORDER BY ...
* @param int if using the session_id: 0 or 2 (student, coach), if using session_id = 0 STUDENT or COURSEMANAGER
* @return array
*/
public static function get_user_list_from_course_code($course_code = null, $session_id = 0, $limit = null, $order_by = null, $filter_by_status = null, $return_count = null, $add_reports = false, $resumed_report = false, $extra_field = null)
{
// variable initialisation
$session_id = intval($session_id);
$course_code = Database::escape_string($course_code);
$where = array();
// if the $order_by does not contain 'ORDER BY' we have to check if it is a valid field that can be sorted on
if (!strstr($order_by, 'ORDER BY')) {
//if (!empty($order_by) AND in_array($order_by, array('lastname', 'firstname', 'username', 'email', 'official_code'))) {
if (!empty($order_by)) {
$order_by = 'ORDER BY ' . $order_by;
} else {
$order_by = '';
}
}
$courseInfo = api_get_course_info($course_code);
$courseId = null;
if ($courseInfo) {
$courseId = $courseInfo['real_id'];
}
$filter_by_status_condition = null;
if (!empty($session_id) && !empty($courseId)) {
$sql = 'SELECT DISTINCT user.user_id, session_course_user.status as status_session, user.* ';
$sql .= ' FROM ' . Database::get_main_table(TABLE_MAIN_USER) . ' as user ';
$sql .= ' LEFT JOIN ' . Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER) . ' as session_course_user
ON user.user_id = session_course_user.id_user
AND session_course_user.c_id="' . $courseId . '"
AND session_course_user.id_session = ' . $session_id;
$where[] = ' session_course_user.c_id IS NOT NULL ';
// 2 = coach
// 0 = student
if (isset($filter_by_status)) {
$filter_by_status = intval($filter_by_status);
$filter_by_status_condition = " session_course_user.status = {$filter_by_status} AND ";
}
} else {
if ($return_count) {
$sql = " SELECT COUNT(*) as count";
if ($resumed_report) {
//$sql = " SELECT count(field_id) ";
}
} else {
if (empty($course_code)) {
$sql = 'SELECT DISTINCT course.title, course.code, course_rel_user.status as status_rel, user.user_id, course_rel_user.role, course_rel_user.tutor_id, user.* ';
} else {
$sql = 'SELECT DISTINCT course_rel_user.status as status_rel, user.user_id, course_rel_user.role, course_rel_user.tutor_id, user.* ';
}
}
$sql .= ' FROM ' . Database::get_main_table(TABLE_MAIN_USER) . ' as user ';
$sql .= ' LEFT JOIN ' . Database::get_main_table(TABLE_MAIN_COURSE_USER) . ' as course_rel_user
ON user.user_id = course_rel_user.user_id AND
course_rel_user.relation_type <> ' . COURSE_RELATION_TYPE_RRHH;
if (!empty($courseInfo)) {
$sql .= " AND course_rel_user.c_id = " . $courseId;
} else {
$course_table = Database::get_main_table(TABLE_MAIN_COURSE);
$sql .= " INNER JOIN {$course_table} course ON course_rel_user.c_id = course.id";
}
$where[] = ' course_rel_user.c_id IS NOT NULL ';
if (isset($filter_by_status) && $filter_by_status != '') {
$filter_by_status = intval($filter_by_status);
$filter_by_status_condition = " course_rel_user.status = {$filter_by_status} AND ";
}
}
$multiple_access_url = api_get_multiple_access_url();
if ($multiple_access_url) {
$sql .= ' LEFT JOIN ' . Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER) . ' au ON (au.user_id = user.user_id) ';
}
if ($return_count && $resumed_report) {
$extra_field_info = UserManager::get_extra_field_information_by_name($extra_field);
$sql .= ' LEFT JOIN ' . Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES) . ' as ufv ON (user.user_id = ufv.user_id AND (field_id = ' . $extra_field_info['id'] . ' OR field_id IS NULL ) )';
}
$sql .= ' WHERE ' . $filter_by_status_condition . ' ' . implode(' OR ', $where);
if ($multiple_access_url) {
$current_access_url_id = api_get_current_access_url_id();
$sql .= " AND (access_url_id = {$current_access_url_id} ) ";
}
if ($return_count && $resumed_report) {
$sql .= ' AND field_id IS NOT NULL GROUP BY field_value ';
}
$sql .= ' ' . $order_by . ' ' . $limit;
$rs = Database::query($sql);
$users = array();
if ($add_reports) {
$extra_fields = UserManager::get_extra_fields(0, 100, null, null, true, true);
}
$counter = 1;
$count_rows = Database::num_rows($rs);
if ($return_count && $resumed_report) {
return $count_rows;
}
$table_user_field_value = Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES);
if ($count_rows) {
while ($user = Database::fetch_array($rs)) {
$report_info = array();
if ($return_count) {
return $user['count'];
}
$user_info = $user;
$user_info['status'] = $user['status'];
if (isset($user['role'])) {
$user_info['role'] = $user['role'];
}
if (isset($user['tutor_id'])) {
$user_info['tutor_id'] = $user['tutor_id'];
}
if (!empty($session_id)) {
$user_info['status_session'] = $user['status_session'];
}
$user_info['complete_name'] = api_get_person_name($user_info['firstname'], $user_info['lastname']);
if ($add_reports) {
$course_code = $user['code'];
if ($resumed_report) {
foreach ($extra_fields as $extra) {
if ($extra['1'] == $extra_field) {
$user_data = UserManager::get_extra_user_data_by_field($user['user_id'], $extra['1']);
break;
}
}
if (empty($user_data[$extra['1']])) {
$row_key = '-1';
$name = '-';
} else {
$row_key = $user_data[$extra['1']];
$name = $user_data[$extra['1']];
}
$users[$row_key]['extra_' . $extra['1']] = $name;
$users[$row_key]['training_hours'] += Tracking::get_time_spent_on_the_course($user['user_id'], $courseId, 0);
$users[$row_key]['count_users'] += $counter;
$registered_users_with_extra_field = 0;
if (!empty($name) && $name != '-') {
$name = Database::escape_string($name);
$sql = "SELECT count(user_id) as count FROM {$table_user_field_value} WHERE field_value = '{$name}'";
$result_count = Database::query($sql);
if (Database::num_rows($result_count)) {
$row_count = Database::fetch_array($result_count);
$registered_users_with_extra_field = $row_count['count'];
}
}
$users[$row_key]['count_users_registered'] = $registered_users_with_extra_field;
$users[$row_key]['average_hours_per_user'] = $users[$row_key]['training_hours'] / $users[$row_key]['count_users'];
$category = Category::load(null, null, $course_code);
if (!isset($users[$row_key]['count_certificates'])) {
$users[$row_key]['count_certificates'] = 0;
}
if (isset($category[0]) && $category[0]->is_certificate_available($user['user_id'])) {
$users[$row_key]['count_certificates']++;
}
} else {
$report_info['course'] = $user['title'];
$report_info['user'] = api_get_person_name($user['firstname'], $user['lastname']);
$report_info['time'] = api_time_to_hms(Tracking::get_time_spent_on_the_course($user['user_id'], $courseId, 0));
$category = Category::load(null, null, $course_code);
$report_info['certificate'] = Display::label(get_lang('No'));
if (isset($category[0]) && $category[0]->is_certificate_available($user['user_id'])) {
$report_info['certificate'] = Display::label(get_lang('Yes'), 'success');
}
//$report_info['score'] = Tracking::get_avg_student_score($user['user_id'], $courseId, array(), 0);
$progress = intval(Tracking::get_avg_student_progress($user['user_id'], $courseId, array(), 0));
$report_info['progress_100'] = $progress == 100 ? Display::label(get_lang('Yes'), 'success') : Display::label(get_lang('No'));
$report_info['progress'] = $progress . "%";
foreach ($extra_fields as $extra) {
$user_data = UserManager::get_extra_user_data_by_field($user['user_id'], $extra['1']);
$report_info[$extra['1']] = $user_data[$extra['1']];
}
$users[] = $report_info;
}
} else {
$users[$user['user_id']] = $user_info;
}
}
$counter++;
}
return $users;
}
function get_courses($from, $limit, $column, $direction)
{
$userId = api_get_user_id();
$sessionId = isset($_GET['session_id']) ? intval($_GET['session_id']) : 0;
$keyword = isset($_GET['keyword']) ? $_GET['keyword'] : null;
$follow = isset($_GET['follow']) ? true : false;
$drhLoaded = false;
if (api_is_drh()) {
if (api_drh_can_access_all_session_content()) {
$courses = SessionManager::getAllCoursesFollowedByUser($userId, $sessionId, $from, $limit, $column, $direction, false, $keyword);
$drhLoaded = true;
}
}
if ($drhLoaded == false) {
$courses = CourseManager::getCoursesFollowedByUser($userId, COURSEMANAGER, $from, $limit, $column, $direction, false, $keyword, $sessionId, $follow);
}
$courseList = array();
if (!empty($courses)) {
foreach ($courses as $data) {
$courseCode = $data['code'];
$courseInfo = api_get_course_info($courseCode);
$userList = CourseManager::get_user_list_from_course_code($data['code'], $sessionId);
$userIdList = array();
if (!empty($userList)) {
foreach ($userList as $user) {
$userIdList[] = $user['user_id'];
}
}
$messagesInCourse = 0;
$assignmentsInCourse = 0;
$avgTimeSpentInCourse = 0;
$avgProgressInCourse = 0;
if (count($userIdList) > 0) {
$countStudents = count($userIdList);
// tracking data
$avgProgressInCourse = Tracking::get_avg_student_progress($userIdList, $courseCode, array(), $sessionId);
$avgScoreInCourse = Tracking::get_avg_student_score($userIdList, $courseCode, array(), $sessionId);
$avgTimeSpentInCourse = Tracking::get_time_spent_on_the_course($userIdList, $courseInfo['real_id'], $sessionId);
$messagesInCourse = Tracking::count_student_messages($userIdList, $courseCode, $sessionId);
$assignmentsInCourse = Tracking::count_student_assignments($userIdList, $courseCode, $sessionId);
$avgTimeSpentInCourse = api_time_to_hms($avgTimeSpentInCourse / $countStudents);
$avgProgressInCourse = round($avgProgressInCourse / $countStudents, 2);
if (is_numeric($avgScoreInCourse)) {
$avgScoreInCourse = round($avgScoreInCourse / $countStudents, 2) . '%';
}
}
$thematic = new Thematic();
$tematic_advance = $thematic->get_total_average_of_thematic_advances($courseCode, $sessionId);
$tematicAdvanceProgress = '-';
if (!empty($tematic_advance)) {
$tematicAdvanceProgress = '<a title="' . get_lang('GoToThematicAdvance') . '" href="' . api_get_path(WEB_CODE_PATH) . 'course_progress/index.php?cidReq=' . $courseCode . '&id_session=' . $sessionId . '">' . $tematic_advance . '%</a>';
}
$courseIcon = '<a href="' . api_get_path(WEB_CODE_PATH) . 'tracking/courseLog.php?cidReq=' . $courseCode . '&id_session=' . $sessionId . '">
' . Display::return_icon('2rightarrow.png', get_lang('Details')) . '
</a>';
$title = Display::url($data['title'], $courseInfo['course_public_url'] . '?id_session=' . $sessionId);
$attendanceLink = Display::url(Display::return_icon('attendance_list.png', get_lang('Attendance'), array(), ICON_SIZE_MEDIUM), api_get_path(WEB_CODE_PATH) . 'attendance/index.php?cidReq=' . $courseCode . '&id_session=' . $sessionId . '&action=calendar_logins');
$courseList[] = array($title, $countStudents, is_null($avgTimeSpentInCourse) ? '-' : $avgTimeSpentInCourse, $tematicAdvanceProgress, is_null($avgProgressInCourse) ? '-' : $avgProgressInCourse . '%', is_null($avgScoreInCourse) ? '-' : $avgScoreInCourse, is_null($messagesInCourse) ? '-' : $messagesInCourse, is_null($assignmentsInCourse) ? '-' : $assignmentsInCourse, $attendanceLink, $courseIcon);
}
}
return $courseList;
}
$lp_items = $array[$i]['lp'] = '<a href="' . api_get_path(WEB_CODE_PATH) . 'newscorm/lp_controller.php?cidReq=' . $course_code . '&action=view&lp_id=' . $lp_id . '" target="_blank">' . $learnpath['name'] . '</a>';
$array[$i]['teachers'] = '';
if (!empty($teacher_list)) {
$array[$i]['teachers'] = implode(', ', $teacher_list);
}
$array[$i]['course_name'] = $course['title'];
$count_students_accessing = 0;
$count_students_complete_all_activities = 0;
$count_students_complete_all_activities_at_50 = 0;
$total_time_spent = 0;
$total_average_progress = 0;
if (!empty($students)) {
foreach ($students as $student_id) {
$avg_student_progress = Tracking::get_avg_student_progress($student_id, $courseId, array($lp_id), $session_id);
$myavg_temp = Tracking::get_avg_student_score($student_id, $courseId, array($lp_id), $session_id);
$avg_progress_in_course = Tracking::get_avg_student_progress($student_id, $courseId, array($lp_id), $session_id);
if (intval($avg_progress_in_course) == 100) {
$count_students_complete_all_activities++;
}
if (intval($avg_progress_in_course) > 0 && intval($avg_progress_in_course) <= 50) {
$count_students_complete_all_activities_at_50++;
}
$total_average_progress += $avg_progress_in_course;
$time_spent = Tracking::get_time_spent_on_the_course($student_id, $courseId, $session_id);
$total_time_spent += $time_spent;
if (!empty($time_spent)) {
$count_students_accessing++;
}
}
//$total_tools += $nb_assignments + $messages + $links + $chat_last_connection + $documents;
}
/**
* Get data for users list in sortable with pagination
* @param $from
* @param $number_of_items
* @param $column
* @param $direction
* @param $includeInvitedUsers boolean Whether include the invited users
* @return array
*/
public static function get_user_data($from, $number_of_items, $column, $direction, $includeInvitedUsers = false)
{
global $user_ids, $course_code, $additional_user_profile_info, $export_csv, $is_western_name_order, $csv_content, $session_id;
$course_code = Database::escape_string($course_code);
$tbl_user = Database::get_main_table(TABLE_MAIN_USER);
$tbl_url_rel_user = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
$access_url_id = api_get_current_access_url_id();
// get all users data from a course for sortable with limit
if (is_array($user_ids)) {
$user_ids = array_map('intval', $user_ids);
$condition_user = "******" . implode(',', $user_ids) . ") ";
} else {
$user_ids = intval($user_ids);
$condition_user = "******";
}
if (!empty($_GET['user_keyword'])) {
$keyword = trim(Database::escape_string($_GET['user_keyword']));
$condition_user .= " AND (\n user.firstname LIKE '%" . $keyword . "%' OR\n user.lastname LIKE '%" . $keyword . "%' OR\n user.username LIKE '%" . $keyword . "%' OR\n user.email LIKE '%" . $keyword . "%'\n ) ";
}
$url_table = null;
$url_condition = null;
if (api_is_multiple_url_enabled()) {
$url_table = ", " . $tbl_url_rel_user . "as url_users";
$url_condition = " AND user.user_id = url_users.user_id AND access_url_id='{$access_url_id}'";
}
$invitedUsersCondition = '';
if (!$includeInvitedUsers) {
$invitedUsersCondition = " AND user.status != " . INVITEE;
}
$sql = "SELECT user.user_id as user_id,\n user.official_code as col0,\n user.lastname as col1,\n user.firstname as col2,\n user.username as col3\n FROM {$tbl_user} as user {$url_table}\n \t {$condition_user} {$url_condition} {$invitedUsersCondition}";
if (!in_array($direction, array('ASC', 'DESC'))) {
$direction = 'ASC';
}
$column = intval($column);
$from = intval($from);
$number_of_items = intval($number_of_items);
$sql .= " ORDER BY col{$column} {$direction} ";
$sql .= " LIMIT {$from},{$number_of_items}";
$res = Database::query($sql);
$users = array();
$course_info = api_get_course_info($course_code);
$total_surveys = 0;
$total_exercises = ExerciseLib::get_all_exercises($course_info, $session_id, false, null, false, 3);
if (empty($session_id)) {
$survey_user_list = array();
$survey_list = SurveyManager::get_surveys($course_code, $session_id);
$total_surveys = count($survey_list);
if (!empty($survey_list)) {
foreach ($survey_list as $survey) {
$user_list = SurveyManager::get_people_who_filled_survey($survey['survey_id'], false, $course_info['real_id']);
foreach ($user_list as $user_id) {
isset($survey_user_list[$user_id]) ? $survey_user_list[$user_id]++ : ($survey_user_list[$user_id] = 1);
}
}
}
}
while ($user = Database::fetch_array($res, 'ASSOC')) {
$courseInfo = api_get_course_info($course_code);
$courseId = $courseInfo['real_id'];
$user['official_code'] = $user['col0'];
$user['lastname'] = $user['col1'];
$user['firstname'] = $user['col2'];
$user['username'] = $user['col3'];
$user['time'] = api_time_to_hms(Tracking::get_time_spent_on_the_course($user['user_id'], $courseId, $session_id));
$avg_student_score = Tracking::get_avg_student_score($user['user_id'], $course_code, array(), $session_id);
$avg_student_progress = Tracking::get_avg_student_progress($user['user_id'], $course_code, array(), $session_id);
if (empty($avg_student_progress)) {
$avg_student_progress = 0;
}
$user['average_progress'] = $avg_student_progress . '%';
$total_user_exercise = Tracking::get_exercise_student_progress($total_exercises, $user['user_id'], $courseId, $session_id);
$user['exercise_progress'] = $total_user_exercise;
$total_user_exercise = Tracking::get_exercise_student_average_best_attempt($total_exercises, $user['user_id'], $courseId, $session_id);
$user['exercise_average_best_attempt'] = $total_user_exercise;
if (is_numeric($avg_student_score)) {
$user['student_score'] = $avg_student_score . '%';
} else {
$user['student_score'] = $avg_student_score;
}
$user['count_assignments'] = Tracking::count_student_assignments($user['user_id'], $course_code, $session_id);
$user['count_messages'] = Tracking::count_student_messages($user['user_id'], $course_code, $session_id);
$user['first_connection'] = Tracking::get_first_connection_date_on_the_course($user['user_id'], $courseId, $session_id);
$user['last_connection'] = Tracking::get_last_connection_date_on_the_course($user['user_id'], $courseInfo, $session_id);
// we need to display an additional profile field
$user['additional'] = '';
if (isset($_GET['additional_profile_field']) && is_numeric($_GET['additional_profile_field'])) {
if (isset($additional_user_profile_info[$user['user_id']]) && is_array($additional_user_profile_info[$user['user_id']])) {
$user['additional'] = implode(', ', $additional_user_profile_info[$user['user_id']]);
}
}
if (empty($session_id)) {
$user['survey'] = (isset($survey_user_list[$user['user_id']]) ? $survey_user_list[$user['user_id']] : 0) . ' / ' . $total_surveys;
}
$user['link'] = '<center><a href="../mySpace/myStudents.php?student=' . $user['user_id'] . '&details=true&course=' . $course_code . '&origin=tracking_course&id_session=' . $session_id . '"><img src="' . api_get_path(WEB_IMG_PATH) . 'icons/22/2rightarrow.png" border="0" /></a></center>';
// store columns in array $users
$is_western_name_order = api_is_western_name_order();
$user_row = array();
$user_row[] = $user['official_code'];
//0
if ($is_western_name_order) {
$user_row[] = $user['firstname'];
$user_row[] = $user['lastname'];
} else {
$user_row[] = $user['lastname'];
$user_row[] = $user['firstname'];
}
$user_row[] = $user['username'];
$user_row[] = $user['time'];
$user_row[] = $user['average_progress'];
$user_row[] = $user['exercise_progress'];
$user_row[] = $user['exercise_average_best_attempt'];
$user_row[] = $user['student_score'];
$user_row[] = $user['count_assignments'];
$user_row[] = $user['count_messages'];
if (empty($session_id)) {
$user_row[] = $user['survey'];
}
$user_row[] = $user['first_connection'];
$user_row[] = $user['last_connection'];
if (isset($_GET['additional_profile_field']) && is_numeric($_GET['additional_profile_field'])) {
$user_row[] = $user['additional'];
}
$user_row[] = $user['link'];
$users[] = $user_row;
if ($export_csv) {
if (empty($session_id)) {
$user_row = array_map('strip_tags', $user_row);
unset($user_row[14]);
unset($user_row[15]);
} else {
$user_row = array_map('strip_tags', $user_row);
unset($user_row[13]);
unset($user_row[14]);
}
$csv_content[] = $user_row;
}
}
return $users;
}
/**
* Get data for courses list in sortable with pagination
* @return array
*/
public static function get_course_data($from, $number_of_items, $column, $direction)
{
global $courses, $csv_content, $charset, $session_id;
// definition database tables
$tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
$tbl_course_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
$tbl_session_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
$course_data = array();
$courses_code = array_keys($courses);
foreach ($courses_code as &$code) {
$code = "'{$code}'";
}
// get all courses with limit
$sql = "SELECT course.code as col1, course.title as col2\n FROM {$tbl_course} course\n WHERE course.code IN (" . implode(',', $courses_code) . ")";
if (!in_array($direction, array('ASC', 'DESC'))) {
$direction = 'ASC';
}
$column = intval($column);
$from = intval($from);
$number_of_items = intval($number_of_items);
$sql .= " ORDER BY col{$column} {$direction} ";
$sql .= " LIMIT {$from},{$number_of_items}";
$res = Database::query($sql);
while ($row_course = Database::fetch_row($res)) {
$course_code = $row_course[0];
$courseInfo = api_get_course_info($course_code);
$courseId = $courseInfo['real_id'];
$avg_assignments_in_course = $avg_messages_in_course = $nb_students_in_course = $avg_progress_in_course = $avg_score_in_course = $avg_time_spent_in_course = $avg_score_in_exercise = 0;
// students directly subscribed to the course
if (empty($session_id)) {
$sql = "SELECT user_id\n FROM {$tbl_course_user} as course_rel_user\n WHERE\n course_rel_user.status='5' AND\n course_rel_user.c_id = '{$courseId}'";
} else {
$sql = "SELECT user_id FROM {$tbl_session_course_user} srcu\n WHERE\n c_id = '{$courseId}' AND\n session_id = '{$session_id}' AND\n status<>2";
}
$rs = Database::query($sql);
$users = array();
while ($row = Database::fetch_array($rs)) {
$users[] = $row['user_id'];
}
if (count($users) > 0) {
$nb_students_in_course = count($users);
$avg_assignments_in_course = Tracking::count_student_assignments($users, $course_code, $session_id);
$avg_messages_in_course = Tracking::count_student_messages($users, $course_code, $session_id);
$avg_progress_in_course = Tracking::get_avg_student_progress($users, $course_code, array(), $session_id);
$avg_score_in_course = Tracking::get_avg_student_score($users, $course_code, array(), $session_id);
$avg_score_in_exercise = Tracking::get_avg_student_exercise_score($users, $course_code, 0, $session_id);
$avg_time_spent_in_course = Tracking::get_time_spent_on_the_course($users, $courseInfo['real_id'], $session_id);
$avg_progress_in_course = round($avg_progress_in_course / $nb_students_in_course, 2);
if (is_numeric($avg_score_in_course)) {
$avg_score_in_course = round($avg_score_in_course / $nb_students_in_course, 2);
}
$avg_time_spent_in_course = api_time_to_hms($avg_time_spent_in_course / $nb_students_in_course);
} else {
$avg_time_spent_in_course = null;
$avg_progress_in_course = null;
$avg_score_in_course = null;
$avg_score_in_exercise = null;
$avg_messages_in_course = null;
$avg_assignments_in_course = null;
}
$table_row = array();
$table_row[] = $row_course[1];
$table_row[] = $nb_students_in_course;
$table_row[] = $avg_time_spent_in_course;
$table_row[] = is_null($avg_progress_in_course) ? '' : $avg_progress_in_course . '%';
$table_row[] = is_null($avg_score_in_course) ? '' : $avg_score_in_course . '%';
$table_row[] = is_null($avg_score_in_exercise) ? '' : $avg_score_in_exercise . '%';
$table_row[] = $avg_messages_in_course;
$table_row[] = $avg_assignments_in_course;
//set the "from" value to know if I access the Reporting by the chamilo tab or the course link
$table_row[] = '<center><a href="../../tracking/courseLog.php?cidReq=' . $course_code . '&from=myspace&id_session=' . $session_id . '">
<img src="' . api_get_path(WEB_IMG_PATH) . 'icons/22/2rightarrow.png" border="0" /></a>
</center>';
$csv_content[] = array(api_html_entity_decode($row_course[1], ENT_QUOTES, $charset), $nb_students_in_course, $avg_time_spent_in_course, is_null($avg_progress_in_course) ? null : $avg_progress_in_course . '%', is_null($avg_score_in_course) ? null : is_numeric($avg_score_in_course) ? $avg_score_in_course . '%' : $avg_score_in_course, is_null($avg_score_in_exercise) ? null : $avg_score_in_exercise . '%', $avg_messages_in_course, $avg_assignments_in_course);
$course_data[] = $table_row;
}
return $course_data;
}
$res = Database::query($sql);
//Getting the list of LPs in the new session
$lp_list = new LearnpathList($user_id, $origin_course_code, $new_session_id);
$flat_list = $lp_list->get_flat_list();
$list = array();
while ($row = Database::fetch_array($res, 'ASSOC')) {
//Checking if the LP exist in the new session
if (in_array($row['lp_id'], array_keys($flat_list))) {
$list[$row['id']] = $row;
}
}
if (!empty($list)) {
foreach ($list as $id => $data) {
//Getting all information of that lp_item_id
$score = Tracking::get_avg_student_score($user_id, $origin_course_code, array($data['lp_id']), $new_session_id);
$progress = Tracking::get_avg_student_progress($user_id, $origin_course_code, array($data['lp_id']), $new_session_id);
$result_message_compare['LP_VIEW'][$data['lp_id']] = array('score' => $score, 'progress' => $progress);
}
}
}
//6. Agenda
//calendar_event_attachment no problems no session_id
$sql = "SELECT ref FROM {$TBL_ITEM_PROPERTY} WHERE tool = 'calendar_event' AND insert_user_id = {$user_id} AND c_id = {$course_id} ";
$res = Database::query($sql);
while ($row = Database::fetch_array($res, 'ASSOC')) {
$id = $row['ref'];
if ($update_database) {
$sql = "UPDATE {$TBL_AGENDA} SET session_id = {$new_session_id} WHERE c_id = {$course_id} AND id = {$id} ";
if ($debug) {
var_dump($sql);
}
/**
* Creates a small table in the last column of the table with the user overview
*
* @param integer $user_id the id of the user
* @param array $url_params additonal url parameters
* @param array $row the row information (the other columns)
* @return html code
*
* @author Patrick Cool <*****@*****.**>, Ghent University, Belgium
* @version Dokeos 1.8.6
* @since October 2008
*/
function course_info_tracking_filter($user_id, $url_params, $row)
{
// the table header
$return .= '<table class="data_table" style="width: 100%;border:0;padding:0;border-collapse:collapse;table-layout: fixed">';
/*$return .= ' <tr>';
$return .= ' <th>'.get_lang('Course').'</th>';
$return .= ' <th>'.get_lang('AvgTimeSpentInTheCourse').'</th>';
$return .= ' <th>'.get_lang('AvgStudentsProgress').'</th>';
$return .= ' <th>'.get_lang('AvgCourseScore').'</th>';
$return .= ' <th>'.get_lang('AvgExercisesScore').'</th>';
$return .= ' <th>'.get_lang('AvgMessages').'</th>';
$return .= ' <th>'.get_lang('AvgAssignments').'</th>';
$return .= ' <th>'.get_lang('TotalExercisesScoreObtained').'</th>';
$return .= ' <th>'.get_lang('TotalExercisesScorePossible').'</th>';
$return .= ' <th>'.get_lang('TotalExercisesAnswered').'</th>';
$return .= ' <th>'.get_lang('TotalExercisesScorePercentage').'</th>';
$return .= ' <th>'.get_lang('FirstLogin').'</th>';
$return .= ' <th>'.get_lang('LatestLogin').'</th>';
$return .= ' </tr>';*/
// database table definition
$tbl_course_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
// getting all the courses of the user
$sql = "SELECT * FROM {$tbl_course_user} WHERE user_id = '" . Database::escape_string($user_id) . "'";
$result = api_sql_query($sql, __FILE__, __LINE__);
while ($row = Database::fetch_row($result)) {
$return .= '<tr>';
// course code
$return .= ' <td width="157px" >' . cut($row[0], 20, true) . '</td>';
// time spent in the course
$return .= ' <td><div>' . api_time_to_hms(Tracking::get_time_spent_on_the_course($user_id, $row[0])) . '</div></td>';
// student progress in course
$return .= ' <td><div>' . round(Tracking::get_avg_student_progress($user_id, $row[0]), 2) . '</div></td>';
// student score
$return .= ' <td><div>' . round(Tracking::get_avg_student_score($user_id, $row[0]), 2) . '</div></td>';
// student tes score
//$return .= ' <td><div style="width:40px">'.round(Tracking :: get_avg_student_exercise_score ($user_id, $row[0]),2).'%</div></td>';
// student messages
$return .= ' <td><div>' . Tracking::count_student_messages($user_id, $row[0]) . '</div></td>';
// student assignments
$return .= ' <td><div>' . Tracking::count_student_assignments($user_id, $row[0]) . '</div></td>';
// student exercises results (obtained score, maximum score, number of exercises answered, score percentage)
$exercises_results = exercises_results($user_id, $row[0]);
$return .= ' <td width="105px"><div>' . $exercises_results['score_obtained'] . '/' . $exercises_results['score_possible'] . '(' . $exercises_results['percentage'] . '%)</div></td>';
//$return .= ' <td><div>'.$exercises_results['score_possible'].'</div></td>';
$return .= ' <td><div>' . $exercises_results['questions_answered'] . '</div></td>';
//$return .= ' <td><div>'.$exercises_results['percentage'].'% </div></td>';
// first connection
//$return .= ' <td width="60px">'.Tracking :: get_first_connection_date_on_the_course ($user_id, $row[0]).'</td>';
// last connection
$return .= ' <td><div>' . Tracking::get_last_connection_date_on_the_course($user_id, $row[0]) . '</div></td>';
$return .= '<tr>';
}
$return .= '</table>';
return $return;
}
