/**
* This function exports the table that we see in display_tracking_course_overview()
*
*/
public static function export_tracking_course_overview()
{
// database table definition
$tbl_course_rel_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
$tbl_user = Database::get_main_table(TABLE_MAIN_USER);
// the values of the sortable table
if ($_GET['tracking_course_overview_page_nr']) {
$from = $_GET['tracking_course_overview_page_nr'];
} else {
$from = 0;
}
if ($_GET['tracking_course_overview_column']) {
$orderby = $_GET['tracking_course_overview_column'];
} else {
$orderby = 0;
}
if ($_GET['tracking_course_overview_direction']) {
$direction = $_GET['tracking_course_overview_direction'];
} else {
$direction = 'ASC';
}
$course_data = MySpace::get_course_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('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 ($course_data as $key => $course) {
$course_code = $course[0];
$courseInfo = api_get_course_info($course_code);
$course_title = $courseInfo['title'];
$courseId = $courseInfo['real_id'];
$csv_row = array();
$csv_row[] = $course_title;
// getting all the courses of the session
$sql = "SELECT *\n FROM {$tbl_user} AS u\n INNER JOIN {$tbl_course_rel_user} AS cu\n ON cu.user_id = u.user_id\n WHERE cu.c_id = '" . $courseId . "'";
$result = 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 = Database::fetch_object($result)) {
// get time spent in the course and session
$time_spent += Tracking::get_time_spent_on_the_course($row->user_id, $courseId);
$progress_tmp = Tracking::get_avg_student_progress($row->user_id, $course_code, array(), null, true);
$progress += $progress_tmp[0];
$nb_progress_lp += $progress_tmp[1];
$score_tmp = Tracking::get_avg_student_score($row->user_id, $course_code, array(), null, true);
if (is_array($score_tmp)) {
$score += $score_tmp[0];
$nb_score_lp += $score_tmp[1];
}
$nb_messages += Tracking::count_student_messages($row->user_id, $course_code);
$nb_assignments += Tracking::count_student_assignments($row->user_id, $course_code);
$last_login_date_tmp = Tracking::get_last_connection_date_on_the_course($row->user_id, $courseInfo, null, 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_id, $course_code);
$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;
}
// 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_course_overview');
exit;
}
