/** * This function exports the table that we see in display_tracking_session_overview() * */ public static function export_tracking_session_overview() { // database table definition $tbl_session_rel_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE); $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE); $tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER); $tbl_user = Database::get_main_table(TABLE_MAIN_USER); // the values of the sortable table if ($_GET['tracking_session_overview_page_nr']) { $from = $_GET['tracking_session_overview_page_nr']; } else { $from = 0; } if ($_GET['tracking_session_overview_column']) { $orderby = $_GET['tracking_session_overview_column']; } else { $orderby = 0; } if ($_GET['tracking_session_overview_direction']) { $direction = $_GET['tracking_session_overview_direction']; } else { $direction = 'ASC'; } $session_data = MySpace::get_session_data_tracking_overview($from, 1000, $orderby, $direction); $csv_content = array(); // the first line of the csv file with the column headers $csv_row = array(); $csv_row[] = get_lang('Session'); $csv_row[] = get_lang('Course', ''); $csv_row[] = get_lang('AvgTimeSpentInTheCourse', ''); $csv_row[] = get_lang('AvgStudentsProgress', ''); $csv_row[] = get_lang('AvgCourseScore', ''); $csv_row[] = get_lang('TotalNumberOfMessages', ''); $csv_row[] = get_lang('TotalNumberOfAssignments', ''); $csv_row[] = get_lang('TotalExercisesScoreObtained', ''); $csv_row[] = get_lang('TotalExercisesScorePossible', ''); $csv_row[] = get_lang('TotalExercisesAnswered', ''); $csv_row[] = get_lang('TotalExercisesScorePercentage', ''); $csv_row[] = get_lang('LatestLogin', ''); $csv_content[] = $csv_row; // the other lines (the data) foreach ($session_data as $key => $session) { $session_id = $session[0]; $session_title = $session[1]; // getting all the courses of the session $sql = "SELECT * FROM {$tbl_course} AS c\n INNER JOIN {$tbl_session_rel_course} AS sc\n ON sc.c_id = c.id\n WHERE sc.session_id = '" . $session_id . "';"; $result = Database::query($sql); while ($row = Database::fetch_object($result)) { $courseId = $row->c_id; $courseInfo = api_get_course_info_by_id($courseId); $csv_row = array(); $csv_row[] = $session_title; $csv_row[] = $row->title; // get the users in the course $sql = "SELECT scu.user_id\n FROM {$tbl_user} AS u\n INNER JOIN {$tbl_session_rel_course_rel_user} AS scu\n ON u.user_id = scu.user_id\n WHERE scu.session_id = '" . $session_id . "' AND scu.c_id = '" . $courseId . "'"; $result_users = Database::query($sql); $time_spent = 0; $progress = 0; $nb_progress_lp = 0; $score = 0; $nb_score_lp = 0; $nb_messages = 0; $nb_assignments = 0; $last_login_date = false; $total_score_obtained = 0; $total_score_possible = 0; $total_questions_answered = 0; while ($row_user = Database::fetch_object($result_users)) { // get time spent in the course and session $time_spent += Tracking::get_time_spent_on_the_course($row_user->user_id, $courseId, $session_id); $progress_tmp = Tracking::get_avg_student_progress($row_user->user_id, $row->code, array(), $session_id, true); $progress += $progress_tmp[0]; $nb_progress_lp += $progress_tmp[1]; $score_tmp = Tracking::get_avg_student_score($row_user->user_id, $row->code, array(), $session_id, true); if (is_array($score_tmp)) { $score += $score_tmp[0]; $nb_score_lp += $score_tmp[1]; } $nb_messages += Tracking::count_student_messages($row_user->user_id, $row->code, $session_id); $nb_assignments += Tracking::count_student_assignments($row_user->user_id, $row->code, $session_id); $last_login_date_tmp = Tracking::get_last_connection_date_on_the_course($row_user->user_id, $courseInfo, $session_id, false); if ($last_login_date_tmp != false && $last_login_date == false) { // TODO: To be cleaned. $last_login_date = $last_login_date_tmp; } else { if ($last_login_date_tmp != false && $last_login_date == false) { // TODO: Repeated previous condition. To be cleaned. // Find the max and assign it to first_login_date if (strtotime($last_login_date_tmp) > strtotime($last_login_date)) { $last_login_date = $last_login_date_tmp; } } } $exercise_results_tmp = MySpace::exercises_results($row_user->user_id, $row->code, $session_id); $total_score_obtained += $exercise_results_tmp['score_obtained']; $total_score_possible += $exercise_results_tmp['score_possible']; $total_questions_answered += $exercise_results_tmp['questions_answered']; } if ($nb_progress_lp > 0) { $avg_progress = round($progress / $nb_progress_lp, 2); } else { $avg_progress = 0; } if ($nb_score_lp > 0) { $avg_score = round($score / $nb_score_lp, 2); } else { $avg_score = '-'; } if ($last_login_date) { $last_login_date = api_convert_and_format_date($last_login_date, DATE_FORMAT_SHORT, date_default_timezone_get()); } else { $last_login_date = '-'; } if ($total_score_possible > 0) { $total_score_percentage = round($total_score_obtained / $total_score_possible * 100, 2); } else { $total_score_percentage = 0; } if ($total_score_percentage > 0) { $total_score = $total_score_obtained . '/' . $total_score_possible . ' (' . $total_score_percentage . ' %)'; } else { $total_score = '-'; } // time spent in the course $csv_row[] = api_time_to_hms($time_spent); // student progress in course $csv_row[] = $avg_progress; // student score $csv_row[] = $avg_score; // student messages $csv_row[] = $nb_messages; // student assignments $csv_row[] = $nb_assignments; // student exercises results (obtained score, maximum score, number of exercises answered, score percentage) $csv_row[] = $total_score_obtained; $csv_row[] = $total_score_possible; $csv_row[] = $total_questions_answered; $csv_row[] = $total_score_percentage; // last connection $csv_row[] = $last_login_date; $csv_content[] = $csv_row; } } Export::arrayToCsv($csv_content, 'reporting_session_overview'); exit; }