/** * 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; }
/** * 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; }