/**
* Get data for courses list in sortable with pagination
* @return array
*/
public static function get_course_data($from, $number_of_items, $column, $direction)
{
global $courses, $csv_content, $charset, $session_id;
// definition database tables
$tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
$tbl_course_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
$tbl_session_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
$course_data = array();
$courses_code = array_keys($courses);
foreach ($courses_code as &$code) {
$code = "'{$code}'";
}
// get all courses with limit
$sql = "SELECT course.code as col1, course.title as col2\n FROM {$tbl_course} course\n WHERE course.code IN (" . implode(',', $courses_code) . ")";
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);
while ($row_course = Database::fetch_row($res)) {
$course_code = $row_course[0];
$courseInfo = api_get_course_info($course_code);
$courseId = $courseInfo['real_id'];
$avg_assignments_in_course = $avg_messages_in_course = $nb_students_in_course = $avg_progress_in_course = $avg_score_in_course = $avg_time_spent_in_course = $avg_score_in_exercise = 0;
// students directly subscribed to the course
if (empty($session_id)) {
$sql = "SELECT user_id\n FROM {$tbl_course_user} as course_rel_user\n WHERE\n course_rel_user.status='5' AND\n course_rel_user.c_id = '{$courseId}'";
} else {
$sql = "SELECT user_id FROM {$tbl_session_course_user} srcu\n WHERE\n c_id = '{$courseId}' AND\n session_id = '{$session_id}' AND\n status<>2";
}
$rs = Database::query($sql);
$users = array();
while ($row = Database::fetch_array($rs)) {
$users[] = $row['user_id'];
}
if (count($users) > 0) {
$nb_students_in_course = count($users);
$avg_assignments_in_course = Tracking::count_student_assignments($users, $course_code, $session_id);
$avg_messages_in_course = Tracking::count_student_messages($users, $course_code, $session_id);
$avg_progress_in_course = Tracking::get_avg_student_progress($users, $course_code, array(), $session_id);
$avg_score_in_course = Tracking::get_avg_student_score($users, $course_code, array(), $session_id);
$avg_score_in_exercise = Tracking::get_avg_student_exercise_score($users, $course_code, 0, $session_id);
$avg_time_spent_in_course = Tracking::get_time_spent_on_the_course($users, $courseInfo['real_id'], $session_id);
$avg_progress_in_course = round($avg_progress_in_course / $nb_students_in_course, 2);
if (is_numeric($avg_score_in_course)) {
$avg_score_in_course = round($avg_score_in_course / $nb_students_in_course, 2);
}
$avg_time_spent_in_course = api_time_to_hms($avg_time_spent_in_course / $nb_students_in_course);
} else {
$avg_time_spent_in_course = null;
$avg_progress_in_course = null;
$avg_score_in_course = null;
$avg_score_in_exercise = null;
$avg_messages_in_course = null;
$avg_assignments_in_course = null;
}
$table_row = array();
$table_row[] = $row_course[1];
$table_row[] = $nb_students_in_course;
$table_row[] = $avg_time_spent_in_course;
$table_row[] = is_null($avg_progress_in_course) ? '' : $avg_progress_in_course . '%';
$table_row[] = is_null($avg_score_in_course) ? '' : $avg_score_in_course . '%';
$table_row[] = is_null($avg_score_in_exercise) ? '' : $avg_score_in_exercise . '%';
$table_row[] = $avg_messages_in_course;
$table_row[] = $avg_assignments_in_course;
//set the "from" value to know if I access the Reporting by the chamilo tab or the course link
$table_row[] = '<center><a href="../../tracking/courseLog.php?cidReq=' . $course_code . '&from=myspace&id_session=' . $session_id . '">
<img src="' . api_get_path(WEB_IMG_PATH) . 'icons/22/2rightarrow.png" border="0" /></a>
</center>';
$csv_content[] = array(api_html_entity_decode($row_course[1], ENT_QUOTES, $charset), $nb_students_in_course, $avg_time_spent_in_course, is_null($avg_progress_in_course) ? null : $avg_progress_in_course . '%', is_null($avg_score_in_course) ? null : is_numeric($avg_score_in_course) ? $avg_score_in_course . '%' : $avg_score_in_course, is_null($avg_score_in_exercise) ? null : $avg_score_in_exercise . '%', $avg_messages_in_course, $avg_assignments_in_course);
$course_data[] = $table_row;
}
return $course_data;
}
</th>
</tr>
<?php
$csv_content[] = array();
$csv_content[] = array(get_lang('Exercises'), get_lang('LearningPath'), get_lang('AvgCourseScore'), get_lang('Attempts'));
$t_quiz = Database::get_course_table(TABLE_QUIZ_TEST);
$sql = "SELECT quiz.title, id FROM " . $t_quiz . " AS quiz\n WHERE\n quiz.c_id = " . $info_course['real_id'] . " AND\n (quiz.session_id = {$sessionId} OR quiz.session_id = 0) AND\n active IN (0, 1)\n ORDER BY quiz.title ASC ";
$result_exercices = Database::query($sql);
$i = 0;
if (Database::num_rows($result_exercices) > 0) {
while ($exercices = Database::fetch_array($result_exercices)) {
$exercise_id = intval($exercices['id']);
$count_attempts = Tracking::count_student_exercise_attempts($student_id, $courseInfo['real_id'], $exercise_id, 0, 0, $sessionId, 2);
$score_percentage = Tracking::get_avg_student_exercise_score($student_id, $course_code, $exercise_id, $sessionId, 1, 0);
if (!isset($score_percentage) && $count_attempts > 0) {
$scores_lp = Tracking::get_avg_student_exercise_score($student_id, $course_code, $exercise_id, $sessionId, 2, 1);
$score_percentage = $scores_lp[0];
$lp_name = $scores_lp[1];
} else {
$lp_name = '-';
}
$lp_name = !empty($lp_name) ? $lp_name : get_lang('NoLearnpath');
if ($i % 2) {
$css_class = 'row_odd';
} else {
$css_class = 'row_even';
}
echo '<tr class="' . $css_class . '"><td>' . $exercices['title'] . '</td>';
echo '<td>';
if (!empty($lp_name)) {
echo $lp_name;
echo get_lang('AllAttempts');
?>
</th>
</tr>
<?php
$csv_content[] = array();
$csv_content[] = array(get_lang('Exercices'), get_lang('Score'), get_lang('Attempts'));
$t_quiz = Database::get_course_table(TABLE_QUIZ_TEST);
$sql_exercices = "SELECT quiz.title, iid FROM " . $t_quiz . " AS quiz\n\t\t\t\t\t\t WHERE quiz.c_id = " . $courseId . " AND\n\t\t\t\t\t\t \t\tactive='1' AND\n\t\t\t\t\t\t\t\t(quiz.session_id = {$session_id} OR quiz.session_id = 0)\n ORDER BY quiz.title ASC ";
$result_exercices = Database::query($sql_exercices);
$i = 0;
if (Database::num_rows($result_exercices) > 0) {
while ($exercices = Database::fetch_array($result_exercices)) {
$exercise_id = intval($exercices['iid']);
$count_attempts = Tracking::count_student_exercise_attempts($student_id, $courseId, $exercise_id, 0, 0, $session_id);
$score_percentage = Tracking::get_avg_student_exercise_score($student_id, $courseId, $exercise_id, $session_id);
$csv_content[] = array($exercices['title'], $score_percentage . '%', $count_attempts);
if ($i % 2) {
$css_class = 'row_odd';
} else {
$css_class = 'row_even';
}
echo '<tr class="' . $css_class . '"><td>' . $exercices['title'] . '</td>';
echo '<td>';
if ($count_attempts > 0) {
echo $score_percentage . '%';
} else {
echo '-';
$score_percentage = 0;
}
echo '</td>';
$sql = "SELECT id, title FROM {$TABLEQUIZ}\n WHERE c_id = {$course_id} AND active <> -1 AND session_id = {$session_id}";
$rs = Database::query($sql);
if ($export_csv) {
$temp = array(get_lang('AverageProgressInLearnpath'), '');
$csv_content[] = array('', '');
$csv_content[] = $temp;
}
$course_path_params = '&cidReq=' . $course_code . '&id_session=' . $session_id;
if (Database::num_rows($rs) > 0) {
$student_ids = array_keys($a_students);
$count_students = count($student_ids);
while ($quiz = Database::fetch_array($rs)) {
$quiz_avg_score = 0;
if ($count_students > 0) {
foreach ($student_ids as $student_id) {
$avg_student_score = Tracking::get_avg_student_exercise_score($student_id, $course_id, $quiz['id'], $session_id);
$quiz_avg_score += $avg_student_score;
}
}
$count_students = $count_students == 0 || is_null($count_students) || $count_students == '' ? 1 : $count_students;
$quiz_avg_score = round($quiz_avg_score / $count_students, 2) . '%';
$url = api_get_path(WEB_CODE_PATH) . 'exercice/overview.php?exerciseId=' . $quiz['id'] . $course_path_params;
echo '<tr><td>' . Display::url($quiz['title'], $url) . '</td><td align="right">' . $quiz_avg_score . '</td></tr>';
if ($export_csv) {
$temp = array($quiz['title'], $quiz_avg_score);
$csv_content[] = $temp;
}
}
} else {
echo '<tr><td>' . get_lang('NoExercises') . '</td></tr>';
if ($export_csv) {
/**
* 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
*/
function export_tracking_user_overview()
{
// database table definitions
$tbl_course_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
// 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 ($_GET['tracking_user_overview_direction']) {
$direction = $_GET['tracking_user_overview_direction'];
} else {
$direction = 'ASC';
}
$user_data = 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');
$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)
require_once api_get_path(LIBRARY_PATH) . 'usermanager.lib.php';
$fields = UserManager::get_extra_fields(0, 50, 5, 'ASC');
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} WHERE user_id = '" . Database::escape_string($user[4]) . "'";
$result = api_sql_query($sql, __FILE__, __LINE__);
while ($row = Database::fetch_row($result)) {
$csv_row = array();
// user official code
$csv_row[] = $user[0];
// user last name
$csv_row[] = $user[1];
// user 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 = get_user_overview_export_extra_fields($user[4]);
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], $row[0]));
// 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 = 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], $row[0]);
// last connection
$csv_row[] = strip_tags(Tracking::get_last_connection_date_on_the_course($user[4], $row[0]));
$csv_content[] = $csv_row;
}
}
Export::export_table_csv($csv_content, 'reporting_user_overview');
}
