/** * Get data for users list in sortable with pagination * @param $from * @param $number_of_items * @param $column * @param $direction * @return array */ public static function get_user_data($from, $number_of_items, $column, $direction) { 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 ( user.firstname LIKE '%".$keyword."%' OR user.lastname LIKE '%".$keyword."%' OR user.username LIKE '%".$keyword."%' OR user.email LIKE '%".$keyword."%' ) "; } $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'"; } $sql = "SELECT user.user_id as user_id, user.official_code as col0, user.lastname as col1, user.firstname as col2, user.username as col3 FROM $tbl_user as user $url_table $condition_user $url_condition"; 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 = get_all_exercises( $course_info, $session_id, false, null, false, 3 ); if (empty($session_id)) { $survey_user_list = array(); $survey_list = survey_manager::get_surveys($course_code, $session_id); $total_surveys = count($survey_list); foreach ($survey_list as $survey) { $user_list = survey_manager::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')) { $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'], $course_code, $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'], $course_code, $session_id ); $user['exercise_progress'] = $total_user_exercise; $total_user_exercise = Tracking::get_exercise_student_average_best_attempt( $total_exercises, $user['user_id'], $course_code, $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'], $course_code, $session_id); $user['last_connection'] = Tracking::get_last_connection_date_on_the_course($user['user_id'], $course_code, $session_id); // we need to display an additional profile field $user['additional'] = ''; if (isset($_GET['additional_profile_field']) AND 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).'2rightarrow.gif" 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']) AND 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; }
echo '<td>'; $all_attempt_url = "../exercice/exercise_report.php?exerciseId={$exercise_id}&cidReq=" . $courseInfo['code'] . "&filter_by_user={$student_id}&id_session={$session_id}"; echo Display::url(Display::return_icon('test_results.png', get_lang('AllAttempts'), array(), ICON_SIZE_SMALL), $all_attempt_url); echo '</td></tr>'; $data_exercices[$i][] = $exercices['title']; $data_exercices[$i][] = $score_percentage . '%'; $data_exercices[$i][] = $count_attempts; $i++; } } else { echo '<tr><td colspan="6">' . get_lang('NoExercise') . '</td></tr>'; } echo '</table>'; //@when using sessions we do not show the survey list if (empty($session_id)) { $survey_list = survey_manager::get_surveys($courseInfo['code'], $session_id); $survey_data = array(); foreach ($survey_list as $survey) { $user_list = survey_manager::get_people_who_filled_survey($survey['survey_id'], false, $courseId); $survey_done = Display::return_icon("accept_na.png", get_lang('NoAnswer'), array(), ICON_SIZE_SMALL); if (in_array($student_id, $user_list)) { $survey_done = Display::return_icon("accept.png", get_lang('Answered'), array(), ICON_SIZE_SMALL); } $data = array('title' => $survey['title'], 'done' => $survey_done); $survey_data[] = $data; } if (!empty($survey_list)) { $table = new HTML_Table(array('class' => 'data_table')); $header_names = array(get_lang('Survey'), get_lang('Answered')); $row = 0; $column = 0;
/** * 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); $course_rel_user = Database::get_main_table(TABLE_MAIN_COURSE_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_statistic_table(TABLE_STATISTIC_TRACK_E_DEFAULT); $table_stats_access = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ACCESS); $course = api_get_course_info_by_id($courseId); //getting all the students of the course //we are not using this because it only returns user ids /* if (empty($sessionId) { // Registered students in a course outside session. $users = CourseManager :: get_student_list_from_course_code($course_code); } else { // Registered students in session. $users = CourseManager :: get_student_list_from_course_code($course_code, true, $sessionId); } */ $where = " WHERE course_code = '%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['code']); if (!empty($sessionId)) { $where .= ' AND id_session = %s'; $queryVariables[] = $sessionId; $sql = "SELECT u.user_id, u.lastname, u.firstname, u.username, u.email, s.course_code, s.id_session FROM $session_course_user s INNER JOIN $user u ON u.user_id = s.id_user $where $order $limit"; } else { $sql = "SELECT u.user_id, u.lastname, u.firstname, u.username, u.email, s.course_code, s.id_session FROM $session_course_user s INNER JOIN $user u ON u.user_id = s.id_user $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 (api_get_item_visibility(api_get_course_info($course['code']), 'learnpath', $row['id'], $row['session_id'])) // { if (empty($arrLesson[$row['session_id']]['lessons_total'])) { $arrLesson[$row['session_id']]['lessons_total'] = 1; } else { $arrLesson[$row['session_id']]['lessons_total'] ++; } // } } /** * Exercises */ require_once api_get_path(SYS_CODE_PATH) . 'exercice/exercise.lib.php'; $exercises = get_all_exercises($course, $sessionId, false, '', $getAllSessions); $exercises_total = count($exercises); /** * Assignments */ //total if ($getAllSessions) { $sql = "SELECT count(w.id) as count FROM $workTable w LEFT JOIN $workTableAssignment a ON (a.publication_id = w.id AND a.c_id = w.c_id) WHERE w.c_id = %s AND parent_id = 0 AND active IN (1, 0)"; } else { $sql = "SELECT count(w.id) as count FROM $workTable w LEFT JOIN $workTableAssignment a ON (a.publication_id = w.id AND a.c_id = w.c_id) WHERE w.c_id = %s AND parent_id = 0 AND active IN (1, 0) 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 WHERE c_id = %s"; } else { $sql = "SELECT count(distinct page_id) as count FROM $wiki 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 = survey_manager::get_surveys($course['code'], $sessionId); $surveys_total = count($survey_list); foreach ($survey_list as $survey) { $user_list = survey_manager::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 FROM $table_stats_access WHERE access_tool = 'course_description' AND access_cours_code = '%s' AND access_session_id = %s AND access_user_id = %s "; $sql_query = sprintf($sql, $course['code'], $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['code'], $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 FROM $wiki 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 FROM $table_stats_default WHERE default_user_id = %s AND default_cours_code = '%s' AND default_event_type = 'wiki_page_view' AND default_value_type = 'wiki_page_id' AND c_id = %s"; $sql_query = sprintf($sql, $user['user_id'], $course['code'], $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], #'profile' => '', '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 'course_description_progress' => $course_description_progress . '%', //lessons '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 '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 '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 '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 '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 . '%'), //survey '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; }
$lp_name, $score_percentage, $count_attempts ); $i++; } } else { echo '<tr><td colspan="6">'.get_lang('NoExercise').'</td></tr>'; } echo '</table>'; //@when using sessions we do not show the survey list if (empty($session_id)) { $survey_list = survey_manager::get_surveys($course_code, $session_id); $survey_data = array(); foreach($survey_list as $survey) { $user_list = survey_manager::get_people_who_filled_survey($survey['survey_id'], false, $info_course['real_id']); $survey_done = Display::return_icon("accept_na.png", get_lang('NoAnswer'), array(), ICON_SIZE_SMALL); if (in_array($student_id, $user_list)) { $survey_done = Display::return_icon("accept.png", get_lang('Answered'), array(), ICON_SIZE_SMALL); } $data = array('title' => $survey['title'], 'done' => $survey_done); $survey_data[] = $data; } if (!empty($survey_list)) { $table = new HTML_Table(array('class' => 'data_table')); $header_names = array(get_lang('Survey'), get_lang('Answered'));