/**
* Shows the user progress (when clicking in the Progress tab)
*
* @param int $user_id
* @param int $session_id
* @param string $extra_params
* @param bool $show_courses
* @param bool $showAllSessions
*
* @return string
*/
public static function show_user_progress($user_id, $session_id = 0, $extra_params = '', $show_courses = true, $showAllSessions = true)
{
$tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
$tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
$tbl_course_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
$tbl_access_rel_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
$tbl_session_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
$tbl_access_rel_session = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
$user_id = intval($user_id);
$session_id = intval($session_id);
if (api_is_multiple_url_enabled()) {
$sql = "SELECT c.code, title\n FROM {$tbl_course_user} cu\n INNER JOIN {$tbl_course} c\n ON (cu.c_id = c.id)\n INNER JOIN {$tbl_access_rel_course} a\n ON (a.c_id = c.id)\n WHERE\n cu.user_id = {$user_id} AND\n relation_type<> " . COURSE_RELATION_TYPE_RRHH . " AND\n access_url_id = " . api_get_current_access_url_id() . "\n ORDER BY title";
} else {
$sql = "SELECT c.code, title\n FROM {$tbl_course_user} u\n INNER JOIN {$tbl_course} c ON (c_id = c.id)\n WHERE\n u.user_id= {$user_id} AND\n relation_type<>" . COURSE_RELATION_TYPE_RRHH . "\n ORDER BY title";
}
$rs = Database::query($sql);
$courses = $course_in_session = $temp_course_in_session = array();
while ($row = Database::fetch_array($rs, 'ASSOC')) {
$courses[$row['code']] = $row['title'];
}
$orderBy = " ORDER BY name ";
$extraInnerJoin = null;
if (SessionManager::orderCourseIsEnabled() && !empty($session_id)) {
$orderBy = " ORDER BY s.id, position ";
$tableSessionRelCourse = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
$extraInnerJoin = " INNER JOIN {$tableSessionRelCourse} src\n ON (cu.c_id = src.c_id AND src.session_id = {$session_id}) ";
}
$sessionCondition = '';
if (!empty($session_id)) {
$sessionCondition = " AND s.id = {$session_id}";
}
// Get the list of sessions where the user is subscribed as student
if (api_is_multiple_url_enabled()) {
$sql = "SELECT DISTINCT c.code, s.id as session_id, name\n FROM {$tbl_session_course_user} cu\n INNER JOIN {$tbl_access_rel_session} a\n ON (a.session_id = cu.session_id)\n INNER JOIN {$tbl_session} s\n ON (s.id = a.session_id)\n INNER JOIN {$tbl_course} c\n ON (c.id = cu.c_id)\n {$extraInnerJoin}\n WHERE\n cu.user_id = {$user_id} AND\n access_url_id = " . api_get_current_access_url_id() . "\n {$sessionCondition}\n {$orderBy} ";
} else {
$sql = "SELECT DISTINCT c.code, s.id as session_id, name\n FROM {$tbl_session_course_user} cu\n INNER JOIN {$tbl_session} s\n ON (s.id = cu.session_id)\n INNER JOIN {$tbl_course} c\n ON (c.id = cu.c_id)\n {$extraInnerJoin}\n WHERE\n cu.user_id = {$user_id}\n {$sessionCondition}\n {$orderBy} ";
}
$rs = Database::query($sql);
$simple_session_array = array();
while ($row = Database::fetch_array($rs)) {
$course_info = CourseManager::get_course_information($row['code']);
$temp_course_in_session[$row['session_id']]['course_list'][$course_info['real_id']] = $course_info;
$temp_course_in_session[$row['session_id']]['name'] = $row['name'];
$simple_session_array[$row['session_id']] = $row['name'];
}
foreach ($simple_session_array as $my_session_id => $session_name) {
$course_list = $temp_course_in_session[$my_session_id]['course_list'];
$my_course_data = array();
foreach ($course_list as $course_data) {
$my_course_data[$course_data['id']] = $course_data['title'];
}
if (empty($session_id)) {
$my_course_data = ArrayClass::utf8_sort($my_course_data);
}
$final_course_data = array();
foreach ($my_course_data as $course_id => $value) {
$final_course_data[$course_id] = $course_list[$course_id];
}
$course_in_session[$my_session_id]['course_list'] = $final_course_data;
$course_in_session[$my_session_id]['name'] = $session_name;
}
$html = '';
// Course list
if ($show_courses) {
if (!empty($courses)) {
$html .= Display::page_subheader(Display::return_icon('course.png', get_lang('MyCourses'), array(), ICON_SIZE_SMALL) . ' ' . get_lang('MyCourses'));
$html .= '<table class="data_table" width="100%">';
$html .= '<tr>
' . Display::tag('th', get_lang('Course'), array('width' => '300px')) . '
' . Display::tag('th', get_lang('TimeSpentInTheCourse'), array('class' => 'head')) . '
' . Display::tag('th', get_lang('Progress'), array('class' => 'head')) . '
' . Display::tag('th', get_lang('Score') . Display::return_icon('info3.gif', get_lang('ScormAndLPTestTotalAverage'), array('align' => 'absmiddle', 'hspace' => '3px')), array('class' => 'head')) . '
' . Display::tag('th', get_lang('LastConnexion'), array('class' => 'head')) . '
' . Display::tag('th', get_lang('Details'), array('class' => 'head')) . '
</tr>';
foreach ($courses as $course_code => $course_title) {
$courseInfo = api_get_course_info($course_code);
$courseId = $courseInfo['real_id'];
$total_time_login = Tracking::get_time_spent_on_the_course($user_id, $courseId);
$time = api_time_to_hms($total_time_login);
$progress = Tracking::get_avg_student_progress($user_id, $course_code);
$percentage_score = Tracking::get_avg_student_score($user_id, $course_code, array());
$last_connection = Tracking::get_last_connection_date_on_the_course($user_id, $courseInfo);
if (is_null($progress)) {
$progress = '0%';
} else {
$progress = $progress . '%';
}
if (isset($_GET['course']) && $course_code == $_GET['course'] && empty($_GET['session_id'])) {
$html .= '<tr class="row_odd" style="background-color:#FBF09D">';
} else {
$html .= '<tr class="row_even">';
}
$url = api_get_course_url($course_code, $session_id);
$course_url = Display::url($course_title, $url, array('target' => SESSION_LINK_TARGET));
$html .= '<td>' . $course_url . '</td>';
$html .= '<td align="center">' . $time . '</td>';
$html .= '<td align="center">' . $progress . '</td>';
$html .= '<td align="center">';
if (is_numeric($percentage_score)) {
$html .= $percentage_score . '%';
} else {
$html .= '0%';
}
$html .= '</td>';
$html .= '<td align="center">' . $last_connection . '</td>';
$html .= '<td align="center">';
if (isset($_GET['course']) && $course_code == $_GET['course'] && empty($_GET['session_id'])) {
$html .= '<a href="#">';
$html .= Display::return_icon('2rightarrow_na.png', get_lang('Details'));
} else {
$html .= '<a href="' . api_get_self() . '?course=' . $course_code . $extra_params . '">';
$html .= Display::return_icon('2rightarrow.png', get_lang('Details'));
}
$html .= '</a>';
$html .= '</td></tr>';
}
$html .= '</table>';
}
}
// Session list
if (!empty($course_in_session)) {
$main_session_graph = '';
//Load graphics only when calling to an specific session
$session_graph = array();
$all_exercise_graph_name_list = array();
$my_results = array();
$all_exercise_graph_list = array();
$all_exercise_start_time = array();
foreach ($course_in_session as $my_session_id => $session_data) {
$course_list = $session_data['course_list'];
$session_name = $session_data['name'];
$user_count = count(SessionManager::get_users_by_session($my_session_id));
$exercise_graph_name_list = array();
//$user_results = array();
$exercise_graph_list = array();
foreach ($course_list as $course_data) {
$exercise_list = ExerciseLib::get_all_exercises($course_data, $my_session_id, false, null, false, 1);
foreach ($exercise_list as $exercise_data) {
$exercise_obj = new Exercise($course_data['id']);
$exercise_obj->read($exercise_data['id']);
//Exercise is not necessary to be visible to show results check the result_disable configuration instead
//$visible_return = $exercise_obj->is_visible();
if ($exercise_data['results_disabled'] == 0 || $exercise_data['results_disabled'] == 2) {
$best_average = intval(ExerciseLib::get_best_average_score_by_exercise($exercise_data['id'], $course_data['id'], $my_session_id, $user_count));
$exercise_graph_list[] = $best_average;
$all_exercise_graph_list[] = $best_average;
$user_result_data = ExerciseLib::get_best_attempt_by_user(api_get_user_id(), $exercise_data['id'], $course_data['real_id'], $my_session_id);
$score = 0;
if (!empty($user_result_data['exe_weighting']) && intval($user_result_data['exe_weighting']) != 0) {
$score = intval($user_result_data['exe_result'] / $user_result_data['exe_weighting'] * 100);
}
$time = api_strtotime($exercise_data['start_time']) ? api_strtotime($exercise_data['start_time'], 'UTC') : 0;
$all_exercise_start_time[] = $time;
$my_results[] = $score;
if (count($exercise_list) <= 10) {
$title = cut($course_data['title'], 30) . " \n " . cut($exercise_data['title'], 30);
$exercise_graph_name_list[] = $title;
$all_exercise_graph_name_list[] = $title;
} else {
// if there are more than 10 results, space becomes difficult to find, so only show the title of the exercise, not the tool
$title = cut($exercise_data['title'], 30);
$exercise_graph_name_list[] = $title;
$all_exercise_graph_name_list[] = $title;
}
}
}
}
}
// Complete graph
if (!empty($my_results) && !empty($all_exercise_graph_list)) {
asort($all_exercise_start_time);
//Fix exams order
$final_all_exercise_graph_name_list = array();
$my_results_final = array();
$final_all_exercise_graph_list = array();
foreach ($all_exercise_start_time as $key => $time) {
$label_time = '';
if (!empty($time)) {
$label_time = date('d-m-y', $time);
}
$final_all_exercise_graph_name_list[] = $all_exercise_graph_name_list[$key] . ' ' . $label_time;
$my_results_final[] = $my_results[$key];
$final_all_exercise_graph_list[] = $all_exercise_graph_list[$key];
}
$main_session_graph = self::generate_session_exercise_graph($final_all_exercise_graph_name_list, $my_results_final, $final_all_exercise_graph_list);
}
$html .= Display::page_subheader(Display::return_icon('session.png', get_lang('Sessions'), array(), ICON_SIZE_SMALL) . ' ' . get_lang('Sessions'));
$html .= '<table class="data_table" width="100%">';
$html .= '<tr>
' . Display::tag('th', get_lang('Session'), array('width' => '300px')) . '
' . Display::tag('th', get_lang('PublishedExercises'), array('width' => '300px')) . '
' . Display::tag('th', get_lang('NewExercises'), array('class' => 'head')) . '
' . Display::tag('th', get_lang('AverageExerciseResult'), array('class' => 'head')) . '
' . Display::tag('th', get_lang('Details'), array('class' => 'head')) . '
</tr>';
foreach ($course_in_session as $my_session_id => $session_data) {
$course_list = $session_data['course_list'];
$session_name = $session_data['name'];
if ($showAllSessions == false) {
if (isset($session_id) && !empty($session_id)) {
if ($session_id != $my_session_id) {
continue;
}
}
}
$all_exercises = 0;
$all_unanswered_exercises_by_user = 0;
$all_average = 0;
$stats_array = array();
foreach ($course_list as $course_data) {
//All exercises in the course @todo change for a real count
$exercises = ExerciseLib::get_all_exercises($course_data, $my_session_id);
$count_exercises = 0;
if (is_array($exercises) && !empty($exercises)) {
$count_exercises = count($exercises);
}
// Count of user results
$done_exercises = null;
$courseInfo = api_get_course_info($course_data['code']);
$answered_exercises = 0;
if (!empty($exercises)) {
foreach ($exercises as $exercise_item) {
$attempts = Event::count_exercise_attempts_by_user(api_get_user_id(), $exercise_item['id'], $courseInfo['real_id'], $my_session_id);
if ($attempts > 1) {
$answered_exercises++;
}
}
}
// Average
$average = ExerciseLib::get_average_score_by_course($courseInfo['real_id'], $my_session_id);
$all_exercises += $count_exercises;
$all_unanswered_exercises_by_user += $count_exercises - $answered_exercises;
$all_average += $average;
}
$all_average = $all_average / count($course_list);
if (isset($_GET['session_id']) && $my_session_id == $_GET['session_id']) {
$html .= '<tr style="background-color:#FBF09D">';
} else {
$html .= '<tr>';
}
$url = api_get_path(WEB_CODE_PATH) . "session/index.php?session_id={$my_session_id}";
$html .= Display::tag('td', Display::url($session_name, $url, array('target' => SESSION_LINK_TARGET)));
$html .= Display::tag('td', $all_exercises);
$html .= Display::tag('td', $all_unanswered_exercises_by_user);
//$html .= Display::tag('td', $all_done_exercise);
$html .= Display::tag('td', ExerciseLib::convert_to_percentage($all_average));
if (isset($_GET['session_id']) && $my_session_id == $_GET['session_id']) {
$icon = Display::url(Display::return_icon('2rightarrow_na.png', get_lang('Details')), '?session_id=' . $my_session_id);
} else {
$icon = Display::url(Display::return_icon('2rightarrow.png', get_lang('Details')), '?session_id=' . $my_session_id);
}
$html .= Display::tag('td', $icon);
$html .= '</tr>';
}
$html .= '</table><br />';
$html .= Display::div($main_session_graph, array('id' => 'session_graph', 'class' => 'chart-session', 'style' => 'position:relative; text-align: center;'));
// Checking selected session.
if (isset($_GET['session_id'])) {
$session_id_from_get = intval($_GET['session_id']);
$session_data = $course_in_session[$session_id_from_get];
$course_list = $session_data['course_list'];
$html .= Display::tag('h3', $session_data['name'] . ' - ' . get_lang('CourseList'));
$html .= '<table class="data_table" width="100%">';
//'.Display::tag('th', get_lang('DoneExercises'), array('class'=>'head')).'
$html .= '
<tr>
<th width="300px">' . get_lang('Course') . '</th>
' . Display::tag('th', get_lang('PublishedExercises'), array('class' => 'head')) . '
' . Display::tag('th', get_lang('NewExercises'), array('class' => 'head')) . '
' . Display::tag('th', get_lang('MyAverage'), array('class' => 'head')) . '
' . Display::tag('th', get_lang('AverageExerciseResult'), array('class' => 'head')) . '
' . Display::tag('th', get_lang('TimeSpentInTheCourse'), array('class' => 'head')) . '
' . Display::tag('th', get_lang('LPProgress'), array('class' => 'head')) . '
' . Display::tag('th', get_lang('Score') . Display::return_icon('info3.gif', get_lang('ScormAndLPTestTotalAverage'), array('align' => 'absmiddle', 'hspace' => '3px')), array('class' => 'head')) . '
' . Display::tag('th', get_lang('LastConnexion'), array('class' => 'head')) . '
' . Display::tag('th', get_lang('Details'), array('class' => 'head')) . '
</tr>';
foreach ($course_list as $course_data) {
$course_code = $course_data['code'];
$course_title = $course_data['title'];
$courseInfo = api_get_course_info($course_code);
$courseId = $courseInfo['real_id'];
// All exercises in the course @todo change for a real count
$exercises = ExerciseLib::get_all_exercises($course_data, $session_id_from_get);
$count_exercises = 0;
if (!empty($exercises)) {
$count_exercises = count($exercises);
}
$answered_exercises = 0;
foreach ($exercises as $exercise_item) {
$attempts = Event::count_exercise_attempts_by_user(api_get_user_id(), $exercise_item['id'], $courseId, $session_id_from_get);
if ($attempts > 1) {
$answered_exercises++;
}
}
$unanswered_exercises = $count_exercises - $answered_exercises;
// Average
$average = ExerciseLib::get_average_score_by_course($courseId, $session_id_from_get);
$my_average = ExerciseLib::get_average_score_by_course_by_user(api_get_user_id(), $courseId, $session_id_from_get);
$stats_array[$course_code] = array('exercises' => $count_exercises, 'unanswered_exercises_by_user' => $unanswered_exercises, 'done_exercises' => $done_exercises, 'average' => $average, 'my_average' => $my_average);
$weighting = 0;
$last_connection = Tracking::get_last_connection_date_on_the_course($user_id, $courseInfo, $session_id_from_get);
$progress = Tracking::get_avg_student_progress($user_id, $course_code, array(), $session_id_from_get);
$total_time_login = Tracking::get_time_spent_on_the_course($user_id, $courseId, $session_id_from_get);
$time = api_time_to_hms($total_time_login);
$percentage_score = Tracking::get_avg_student_score($user_id, $course_code, array(), $session_id_from_get);
$courseCodeFromGet = isset($_GET['course']) ? $_GET['course'] : null;
if ($course_code == $courseCodeFromGet && $_GET['session_id'] == $session_id_from_get) {
$html .= '<tr class="row_odd" style="background-color:#FBF09D" >';
} else {
$html .= '<tr class="row_even">';
}
$url = api_get_course_url($course_code, $session_id_from_get);
$course_url = Display::url($course_title, $url, array('target' => SESSION_LINK_TARGET));
$html .= Display::tag('td', $course_url);
$html .= Display::tag('td', $stats_array[$course_code]['exercises']);
$html .= Display::tag('td', $stats_array[$course_code]['unanswered_exercises_by_user']);
//$html .= Display::tag('td', $stats_array[$course_code]['done_exercises']);
$html .= Display::tag('td', ExerciseLib::convert_to_percentage($stats_array[$course_code]['my_average']));
$html .= Display::tag('td', $stats_array[$course_code]['average'] == 0 ? '-' : '(' . ExerciseLib::convert_to_percentage($stats_array[$course_code]['average']) . ')');
$html .= Display::tag('td', $time, array('align' => 'center'));
if (is_numeric($progress)) {
$progress = $progress . '%';
} else {
$progress = '0%';
}
//Progress
$html .= Display::tag('td', $progress, array('align' => 'center'));
if (is_numeric($percentage_score)) {
$percentage_score = $percentage_score . '%';
} else {
$percentage_score = '0%';
}
//Score
$html .= Display::tag('td', $percentage_score, array('align' => 'center'));
$html .= Display::tag('td', $last_connection, array('align' => 'center'));
if ($course_code == $courseCodeFromGet && $_GET['session_id'] == $session_id_from_get) {
$details = '<a href="#">';
$details .= Display::return_icon('2rightarrow_na.png', get_lang('Details'));
} else {
$details = '<a href="' . api_get_self() . '?course=' . $course_code . '&session_id=' . $session_id_from_get . $extra_params . '">';
$details .= Display::return_icon('2rightarrow.png', get_lang('Details'));
}
$details .= '</a>';
$html .= Display::tag('td', $details, array('align' => 'center'));
$html .= '</tr>';
}
$html .= '</table>';
}
}
return $html;
}
$orderButtons = null;
$upIcon = 'up.png';
$urlUp = api_get_self() . '?id_session=' . $sessionId . '&course_id=' . $course->getId() . '&action=move_up';
if ($count == 0) {
$upIcon = 'up_na.png';
$urlUp = '#';
}
$orderButtons = Display::url(Display::return_icon($upIcon, get_lang('MoveUp')), $urlUp);
$downIcon = 'down.png';
$downUrl = api_get_self() . '?id_session=' . $sessionId . '&course_id=' . $course->getId() . '&action=move_down';
if ($count + 1 == count($courses)) {
$downIcon = 'down_na.png';
$downUrl = '#';
}
$orderButtons .= Display::url(Display::return_icon($downIcon, get_lang('MoveDown')), $downUrl);
if (!SessionManager::orderCourseIsEnabled()) {
$orderButtons = '';
}
$courseUrl = api_get_course_url($course->getCode(), $sessionId);
// hide_course_breadcrumb the parameter has been added to hide the name
// of the course, that appeared in the default $interbreadcrumb
$courseItem .= '
<tr>
<td class="title">' . Display::url($course->getTitle() . ' (' . $course->getVisualCode() . ')', $courseUrl) . '</td>
<td>' . $coach . '</td>
<td>' . $numberOfUsers . '</td>
<td>
<a href="' . $courseUrl . '">' . Display::return_icon('course_home.gif', get_lang('Course')) . '</a>
' . $orderButtons . '
<a href="session_course_user_list.php?id_session=' . $sessionId . '&course_code=' . $course->getCode() . '">' . Display::return_icon('user.png', get_lang('Edit'), '', ICON_SIZE_SMALL) . '</a>
<a href="' . api_get_path(WEB_CODE_PATH) . '/user/user_import.php?action=import&cidReq=' . $course->getCode() . '&id_session=' . $sessionId . '">' . Display::return_icon('import_csv.png', get_lang('ImportUsersToACourse'), null, ICON_SIZE_SMALL) . '</a>
/**
* Gets the list (or the count) of courses by session filtered by access_url
* @param int $session_id The session id
* @param string $course_name The course code
* @param string $orderBy Field to order the data
* @param boolean $getCount Optional. Count the session courses
* @return array|int List of courses. Whether $getCount is true, return the count
*/
public static function get_course_list_by_session_id($session_id, $course_name = '', $orderBy = null, $getCount = false)
{
$tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
$tbl_session_rel_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
$session_id = intval($session_id);
$sqlSelect = "SELECT *";
if ($getCount) {
$sqlSelect = "SELECT COUNT(1)";
}
// select the courses
$sql = "SELECT *, c.id, c.id as real_id\n FROM {$tbl_course} c\n INNER JOIN {$tbl_session_rel_course} src\n ON c.id = src.c_id\n\t\t WHERE src.session_id = '{$session_id}' ";
if (!empty($course_name)) {
$course_name = Database::escape_string($course_name);
$sql .= " AND c.title LIKE '%{$course_name}%' ";
}
if (!empty($orderBy)) {
$orderBy = Database::escape_string($orderBy);
$orderBy = " ORDER BY {$orderBy}";
} else {
if (SessionManager::orderCourseIsEnabled()) {
$orderBy .= " ORDER BY position ";
} else {
$orderBy .= " ORDER BY title ";
}
}
$sql .= Database::escape_string($orderBy);
$result = Database::query($sql);
$num_rows = Database::num_rows($result);
$courses = array();
if ($num_rows > 0) {
if ($getCount) {
$count = Database::fetch_array($result);
return intval($count[0]);
}
while ($row = Database::fetch_array($result, 'ASSOC')) {
$courses[$row['real_id']] = $row;
}
}
return $courses;
}
/**
* Gets the list of courses by session filtered by access_url
* @param int $session_id
* @param string $course_name
* @return array list of courses
*/
public static function get_course_list_by_session_id(
$session_id,
$course_name = '',
$orderBy = null
) {
$tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
$tbl_session_rel_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
$session_id = intval($session_id);
// select the courses
$sql = "SELECT * FROM $tbl_course c
INNER JOIN $tbl_session_rel_course src
ON c.code = src.course_code
WHERE src.id_session = '$session_id' ";
if (!empty($course_name)) {
$course_name = Database::escape_string($course_name);
$sql .= " AND c.title LIKE '%$course_name%' ";
}
if (!empty($orderBy)) {
$orderBy = Database::escape_string($orderBy);
$orderBy = " ORDER BY $orderBy";
} else {
if (SessionManager::orderCourseIsEnabled()) {
$orderBy .= " ORDER BY position ";
} else {
$orderBy .= " ORDER BY title ";
}
}
$sql .= Database::escape_string($orderBy);
$result = Database::query($sql);
$num_rows = Database::num_rows($result);
$courses = array();
if ($num_rows > 0) {
while ($row = Database::fetch_array($result,'ASSOC')) {
$courses[$row['id']] = $row;
}
}
return $courses;
}
/**
* get courses followed by user
* @param int $user_id
* @param int $status
* @param int $from
* @param int $limit
* @param string $column
* @param string $direction
* @param boolean $getCount
* @param string $keyword
* @param int $sessionId
* @param boolean $showAllAssignedCourses
* @return array courses
*/
public static function getCoursesFollowedByUser($user_id, $status = null, $from = null, $limit = null, $column = null, $direction = null, $getCount = false, $keyword = null, $sessionId = null, $showAllAssignedCourses = false)
{
// Database Table Definitions
$tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
$tbl_course_rel_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
$tbl_course_rel_access_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
$sessionId = intval($sessionId);
$user_id = intval($user_id);
$select = "SELECT DISTINCT *, id as real_id ";
if ($getCount) {
$select = "SELECT COUNT(DISTINCT id) as count";
}
$whereConditions = null;
switch ($status) {
case COURSEMANAGER:
$whereConditions .= " AND cru.user_id = '{$user_id}'";
if (!$showAllAssignedCourses) {
$whereConditions .= " AND status = " . COURSEMANAGER;
} else {
$whereConditions .= " AND relation_type = " . COURSE_RELATION_TYPE_COURSE_MANAGER;
}
break;
case DRH:
$whereConditions .= " AND\n cru.user_id = '{$user_id}' AND\n status = " . DRH . " AND\n relation_type = '" . COURSE_RELATION_TYPE_RRHH . "'\n ";
break;
}
$keywordCondition = null;
if (!empty($keyword)) {
$keyword = Database::escape_string($keyword);
$keywordCondition = " AND (c.code LIKE '%{$keyword}%' OR c.title LIKE '%{$keyword}%' ) ";
}
$orderBy = null;
$extraInnerJoin = null;
if (!empty($sessionId)) {
if (!empty($sessionId)) {
$courseList = SessionManager::get_course_list_by_session_id($sessionId);
if (!empty($courseList)) {
$courseListToString = implode("','", array_keys($courseList));
$whereConditions .= " AND c.id IN ('" . $courseListToString . "')";
}
}
if (SessionManager::orderCourseIsEnabled() && !empty($sessionId)) {
$tableSessionRelCourse = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
$orderBy = ' ORDER BY position';
$extraInnerJoin = " INNER JOIN {$tableSessionRelCourse} src\n ON (c.code = src.course_code AND id_session = {$sessionId}) ";
}
}
$whereConditions .= $keywordCondition;
$sql = "{$select}\n FROM {$tbl_course} c\n INNER JOIN {$tbl_course_rel_user} cru ON (cru.course_code = c.code)\n INNER JOIN {$tbl_course_rel_access_url} a ON (a.course_code = c.code)\n {$extraInnerJoin}\n WHERE\n access_url_id = " . api_get_current_access_url_id() . "\n {$whereConditions}\n {$orderBy}\n ";
if (isset($from) && isset($limit)) {
$from = intval($from);
$limit = intval($limit);
$sql .= " LIMIT {$from}, {$limit}";
}
$result = Database::query($sql);
if ($getCount) {
$row = Database::fetch_array($result);
return $row['count'];
}
$courses = array();
if (Database::num_rows($result) > 0) {
while ($row = Database::fetch_array($result)) {
$courses[$row['code']] = $row;
}
}
return $courses;
}
/**
* Gives a list of courses for the given user in the given session
* @param integer $user_id
* @param integer $session_id
* @return array list of statuses (session_id-course_code => status)
*/
public static function get_courses_list_by_session($user_id, $session_id)
{
// Database Table Definitions
$tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
$tableCourse = Database :: get_main_table(TABLE_MAIN_COURSE);
$tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
$tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
$user_id = intval($user_id);
$session_id = intval($session_id);
//we filter the courses from the URL
$join_access_url = $where_access_url = '';
if (api_get_multiple_access_url()) {
$access_url_id = api_get_current_access_url_id();
if ($access_url_id != -1) {
$tbl_url_session = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
$join_access_url = " , $tbl_url_session url_rel_session ";
$where_access_url = " AND access_url_id = $access_url_id AND url_rel_session.session_id = $session_id ";
}
}
$personal_course_list = array();
$courses = array();
/* This query is very similar to the above query,but it will check the
session_rel_course_user table if there are courses registered
to our user or not*/
$sql = "SELECT DISTINCT
scu.course_code as code, c.visibility, c.id as real_id
FROM $tbl_session_course_user as scu
INNER JOIN $tbl_session_course sc
ON (scu.id_session = sc.id_session AND scu.course_code = sc.course_code)
INNER JOIN $tableCourse as c
ON (scu.course_code = c.code)
$join_access_url
WHERE
scu.id_user = $user_id AND
scu.id_session = $session_id
$where_access_url";
$orderBy = " ORDER BY code ";
if (SessionManager::orderCourseIsEnabled()) {
$orderBy = ' ORDER BY position';
}
$sql .= $orderBy;
$result = Database::query($sql);
if (Database::num_rows($result) > 0) {
while ($result_row = Database::fetch_array($result)) {
$result_row['status'] = 5;
if (!in_array($result_row['code'], $courses)) {
$personal_course_list[] = $result_row;
$courses[] = $result_row['code'];
}
}
}
if (api_is_allowed_to_create_course()) {
$sql = "SELECT DISTINCT
scu.course_code as code, c.visibility, c.id as real_id
FROM $tbl_session_course_user as scu
INNER JOIN $tbl_session as s
ON (scu.id_session = s.id)
INNER JOIN $tbl_session_course sc
ON (scu.id_session = sc.id_session AND scu.course_code = sc.course_code)
INNER JOIN $tableCourse as c
ON (scu.course_code = c.code)
$join_access_url
WHERE
s.id = $session_id AND
(
(scu.id_user=$user_id AND scu.status=2) OR
s.id_coach = $user_id
)
$where_access_url
$orderBy";
$result = Database::query($sql);
if (Database::num_rows($result) > 0) {
while ($result_row = Database::fetch_array($result)) {
$result_row['status'] = 2;
if (!in_array($result_row['code'], $courses)) {
$personal_course_list[] = $result_row;
$courses[] = $result_row['code'];
}
}
}
}
if (api_is_drh()) {
$session_list = SessionManager::get_sessions_followed_by_drh($user_id);
$session_list = array_keys($session_list);
if (in_array($session_id, $session_list)) {
$course_list = SessionManager::get_course_list_by_session_id($session_id);
if (!empty($course_list)) {
foreach ($course_list as $course) {
$personal_course_list[] = $course;
}
}
}
} else {
//check if user is general coach for this session
$s = api_get_session_info($session_id);
if ($s['id_coach'] == $user_id) {
$course_list = SessionManager::get_course_list_by_session_id($session_id);
if (!empty($course_list)) {
foreach ($course_list as $course) {
if (!in_array($course['code'], $courses)) {
$personal_course_list[] = $course;
}
}
}
}
}
return $personal_course_list;
}
