/** * 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; }
/** * This function exports the table that we see in display_tracking_user_overview() * * @author Patrick Cool <*****@*****.**>, Ghent University, Belgium * @version Dokeos 1.8.6 * @since October 2008 */ public static function export_tracking_user_overview() { // database table definitions $tbl_course_user = Database::get_main_table(TABLE_MAIN_COURSE_USER); $is_western_name_order = api_is_western_name_order(PERSON_NAME_DATA_EXPORT); $sort_by_first_name = api_sort_by_first_name(); // the values of the sortable table if ($_GET['tracking_user_overview_page_nr']) { $from = $_GET['tracking_user_overview_page_nr']; } else { $from = 0; } if ($_GET['tracking_user_overview_column']) { $orderby = $_GET['tracking_user_overview_column']; } else { $orderby = 0; } if ($is_western_name_order != api_is_western_name_order() && ($orderby == 1 || $orderby == 2)) { // Swapping the sorting column if name order for export is different than the common name order. $orderby = 3 - $orderby; } if ($_GET['tracking_user_overview_direction']) { $direction = $_GET['tracking_user_overview_direction']; } else { $direction = 'ASC'; } $user_data = MySpace::get_user_data_tracking_overview($from, 1000, $orderby, $direction); // the first line of the csv file with the column headers $csv_row = array(); $csv_row[] = get_lang('OfficialCode'); if ($is_western_name_order) { $csv_row[] = get_lang('FirstName', ''); $csv_row[] = get_lang('LastName', ''); } else { $csv_row[] = get_lang('LastName', ''); $csv_row[] = get_lang('FirstName', ''); } $csv_row[] = get_lang('LoginName'); $csv_row[] = get_lang('CourseCode'); // the additional user defined fields (only those that were selected to be exported) $fields = UserManager::get_extra_fields(0, 50, 5, 'ASC'); if (is_array($_SESSION['additional_export_fields'])) { foreach ($_SESSION['additional_export_fields'] as $key => $extra_field_export) { $csv_row[] = $fields[$extra_field_export][3]; $field_names_to_be_exported[] = 'extra_' . $fields[$extra_field_export][1]; } } $csv_row[] = get_lang('AvgTimeSpentInTheCourse', ''); $csv_row[] = get_lang('AvgStudentsProgress', ''); $csv_row[] = get_lang('AvgCourseScore', ''); $csv_row[] = get_lang('AvgExercisesScore', ''); $csv_row[] = get_lang('AvgMessages', ''); $csv_row[] = get_lang('AvgAssignments', ''); $csv_row[] = get_lang('TotalExercisesScoreObtained', ''); $csv_row[] = get_lang('TotalExercisesScorePossible', ''); $csv_row[] = get_lang('TotalExercisesAnswered', ''); $csv_row[] = get_lang('TotalExercisesScorePercentage', ''); $csv_row[] = get_lang('FirstLogin', ''); $csv_row[] = get_lang('LatestLogin', ''); $csv_content[] = $csv_row; // the other lines (the data) foreach ($user_data as $key => $user) { // getting all the courses of the user $sql = "SELECT * FROM {$tbl_course_user}\n WHERE user_id = '" . intval($user[4]) . "' AND relation_type<>" . COURSE_RELATION_TYPE_RRHH . " "; $result = Database::query($sql); while ($row = Database::fetch_row($result)) { $courseInfo = api_get_course_info($row['course_code']); $courseId = $courseInfo['real_id']; $csv_row = array(); // user official code $csv_row[] = $user[0]; // user first|last name $csv_row[] = $user[1]; // user last|first name $csv_row[] = $user[2]; // user login name $csv_row[] = $user[3]; // course code $csv_row[] = $row[0]; // the additional defined user fields $extra_fields = MySpace::get_user_overview_export_extra_fields($user[4]); if (is_array($field_names_to_be_exported)) { foreach ($field_names_to_be_exported as $key => $extra_field_export) { $csv_row[] = $extra_fields[$extra_field_export]; } } // time spent in the course $csv_row[] = api_time_to_hms(Tracking::get_time_spent_on_the_course($user[4], $courseId)); // student progress in course $csv_row[] = round(Tracking::get_avg_student_progress($user[4], $row[0]), 2); // student score $csv_row[] = round(Tracking::get_avg_student_score($user[4], $row[0]), 2); // student tes score $csv_row[] = round(Tracking::get_avg_student_exercise_score($user[4], $row[0]), 2); // student messages $csv_row[] = Tracking::count_student_messages($user[4], $row[0]); // student assignments $csv_row[] = Tracking::count_student_assignments($user[4], $row[0]); // student exercises results $exercises_results = MySpace::exercises_results($user[4], $row[0]); $csv_row[] = $exercises_results['score_obtained']; $csv_row[] = $exercises_results['score_possible']; $csv_row[] = $exercises_results['questions_answered']; $csv_row[] = $exercises_results['percentage']; // first connection $csv_row[] = Tracking::get_first_connection_date_on_the_course($user[4], $courseId); // last connection $csv_row[] = strip_tags(Tracking::get_last_connection_date_on_the_course($user[4], $courseInfo)); $csv_content[] = $csv_row; } } Export::arrayToCsv($csv_content, 'reporting_user_overview'); exit; }
$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 . ' %'; $row[] = $total_assignments; $row[] = $total_messages; $row[] = Tracking::get_first_connection_date_on_the_course($student_id, $course_code); $row[] = Tracking::get_last_connection_date_on_the_course($student_id, $course_code); if ($export_csv) { $row[8] = strip_tags($row[8]); $csv_content[] = $row; } $row[] = '<center><a href="../mySpace/myStudents.php?student=' . $student_id . '&details=true&course=' . $course_code . '&origin=tracking_course"><img src="' . api_get_path(WEB_IMG_PATH) . '2rightarrow.gif" border="0" /></a></center>'; $all_datas[] = $row; } usort($all_datas, 'sort_users'); $page = $table->get_pager()->getCurrentPageID(); $all_datas = array_slice($all_datas, ($page - 1) * $table->per_page, $table->per_page); if ($export_csv) { usort($csv_content, 'sort_users'); } foreach ($all_datas as $row) { $table->addRow($row, 'align="right"');
$count = 0; $temp = []; foreach ($course_list as $item) { if (isset($course_id) && !empty($course_id)) { if ($course_id != $item['id']) { continue; } } $list = new LearnpathList(api_get_user_id(), $item['code'], $session_id); $flat_list = $list->get_flat_list(); $lps[$item['code']] = $flat_list; $item['title'] = Display::url($item['title'], api_get_path(WEB_COURSE_PATH) . $item['directory'] . '/?id_session=' . $session_id, array('target' => SESSION_LINK_TARGET)); foreach ($flat_list as $lp_id => $lp_item) { $temp[$count]['id'] = $lp_id; $lp_url = api_get_path(WEB_CODE_PATH) . 'newscorm/lp_controller.php?cidReq=' . $item['code'] . '&id_session=' . $session_id . '&lp_id=' . $lp_id . '&action=view'; $last_date = Tracking::get_last_connection_date_on_the_course(api_get_user_id(), $item, $session_id, false); if ($lp_item['modified_on'] == '0000-00-00 00:00:00' || empty($lp_item['modified_on'])) { $lp_date = api_get_local_time($lp_item['created_on']); $image = 'new.gif'; $label = get_lang('LearnpathAdded'); } else { $lp_date = api_get_local_time($lp_item['modified_on']); $image = 'moderator_star.png'; $label = get_lang('LearnpathUpdated'); } $icons = ''; if (strtotime($last_date) < strtotime($lp_date)) { $icons = Display::return_icon($image, get_lang('TitleNotification') . ': ' . $label . ' - ' . $lp_date); } if (!empty($lp_item['publicated_on'])) { $date = substr($lp_item['publicated_on'], 0, 10);
$course_info = api_get_course_info($course_data['code']); $exercise_count = count( get_all_exercises( $course_info, $session_id, true, null, false, 1 ) ); $max_mutation_date = ''; $last_date = Tracking::get_last_connection_date_on_the_course( api_get_user_id(), $course_data['code'], $session_id, false ); $icons = ''; foreach ($lp_list as $item) { if ($item['modified_on'] == '0000-00-00 00:00:00' || empty($item['modified_on'])) { $lp_date_original = $item['created_on']; $image = 'new.gif'; $label = get_lang('LearnpathAdded'); } else { $lp_date_original = $item['modified_on']; $image = 'moderator_star.png'; $label = get_lang('LearnpathUpdated'); }
/** * 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; }