/**
* Gives a list of [session_id-course_code] => [status] for the current user.
* @param integer $user_id
* @return array list of statuses (session_id-course_code => status)
*/
public static function get_personal_session_course_list($user_id)
{
// Database Table Definitions
$tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
$tbl_user = Database::get_main_table(TABLE_MAIN_USER);
$tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
$tbl_session_user = Database::get_main_table(TABLE_MAIN_SESSION_USER);
$tbl_course_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
$tbl_session_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
if ($user_id != strval(intval($user_id))) {
return array();
}
// 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_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
$join_access_url = "LEFT JOIN {$tbl_url_course} url_rel_course ON url_rel_course.c_id = course.id";
$where_access_url = " AND access_url_id = {$access_url_id} ";
}
}
// Courses in which we subscribed out of any session
$tbl_user_course_category = Database::get_main_table(TABLE_USER_COURSE_CATEGORY);
$sql = "SELECT\n course.code,\n course_rel_user.status course_rel_status,\n course_rel_user.sort sort,\n course_rel_user.user_course_cat user_course_cat\n FROM " . $tbl_course_user . " course_rel_user\n LEFT JOIN " . $tbl_course . " course\n ON course.id = course_rel_user.c_id\n LEFT JOIN " . $tbl_user_course_category . " user_course_category\n ON course_rel_user.user_course_cat = user_course_category.id\n {$join_access_url}\n WHERE\n course_rel_user.user_id = '" . $user_id . "' AND\n course_rel_user.relation_type <> " . COURSE_RELATION_TYPE_RRHH . "\n {$where_access_url}\n ORDER BY user_course_category.sort, course_rel_user.sort, course.title ASC";
$course_list_sql_result = Database::query($sql);
$personal_course_list = array();
if (Database::num_rows($course_list_sql_result) > 0) {
while ($result_row = Database::fetch_array($course_list_sql_result, 'ASSOC')) {
$course_info = api_get_course_info($result_row['code']);
$result_row['course_info'] = $course_info;
$personal_course_list[] = $result_row;
}
}
$coachCourseConditions = null;
// Getting sessions that are related to a coach in the session_rel_course_rel_user table
if (api_is_allowed_to_create_course()) {
$sessionListFromCourseCoach = array();
$sql = " SELECT DISTINCT session_id\n FROM {$tbl_session_course_user}\n WHERE user_id = {$user_id} AND status = 2 ";
$result = Database::query($sql);
if (Database::num_rows($result)) {
$result = Database::store_result($result);
foreach ($result as $session) {
$sessionListFromCourseCoach[] = $session['session_id'];
}
}
if (!empty($sessionListFromCourseCoach)) {
$condition = implode("','", $sessionListFromCourseCoach);
$coachCourseConditions = " OR ( s.id IN ('{$condition}'))";
}
}
// Get the list of sessions where the user is subscribed
// This is divided into two different queries
$sessions = array();
$sql = "SELECT DISTINCT s.id, name, access_start_date, access_end_date\n FROM {$tbl_session_user}, {$tbl_session} s\n WHERE (\n session_id = s.id AND\n user_id = {$user_id} AND\n relation_type <> " . SESSION_RELATION_TYPE_RRHH . "\n )\n {$coachCourseConditions}\n ORDER BY access_start_date, access_end_date, name";
$result = Database::query($sql);
if (Database::num_rows($result) > 0) {
while ($row = Database::fetch_assoc($result)) {
$sessions[$row['id']] = $row;
}
}
$sql = "SELECT DISTINCT\n id, name, access_start_date, access_end_date\n FROM {$tbl_session} s\n WHERE (\n id_coach = {$user_id}\n )\n {$coachCourseConditions}\n ORDER BY access_start_date, access_end_date, name";
$result = Database::query($sql);
if (Database::num_rows($result) > 0) {
while ($row = Database::fetch_assoc($result)) {
if (empty($sessions[$row['id']])) {
$sessions[$row['id']] = $row;
}
}
}
if (api_is_allowed_to_create_course()) {
foreach ($sessions as $enreg) {
$session_id = $enreg['id'];
$session_visibility = api_get_session_visibility($session_id);
if ($session_visibility == SESSION_INVISIBLE) {
continue;
}
// This query is horribly slow when more than a few thousand
// users and just a few sessions to which they are subscribed
$id_session = $enreg['id'];
$personal_course_list_sql = "SELECT DISTINCT\n course.code code,\n course.title i,\n " . (api_is_western_name_order() ? "CONCAT(user.firstname,' ',user.lastname)" : "CONCAT(user.lastname,' ',user.firstname)") . " t,\n email, course.course_language l,\n 1 sort,\n category_code user_course_cat,\n access_start_date,\n access_end_date,\n session.id as session_id,\n session.name as session_name\n FROM {$tbl_session_course_user} as session_course_user\n INNER JOIN {$tbl_course} AS course\n ON course.id = session_course_user.c_id\n INNER JOIN {$tbl_session} as session\n ON session.id = session_course_user.session_id\n LEFT JOIN {$tbl_user} as user\n ON user.id = session_course_user.user_id OR session.id_coach = user.id\n WHERE\n session_course_user.session_id = {$id_session} AND (\n (session_course_user.user_id = {$user_id} AND session_course_user.status = 2)\n OR session.id_coach = {$user_id}\n )\n ORDER BY i";
$course_list_sql_result = Database::query($personal_course_list_sql);
while ($result_row = Database::fetch_array($course_list_sql_result, 'ASSOC')) {
$result_row['course_info'] = api_get_course_info($result_row['code']);
$key = $result_row['session_id'] . ' - ' . $result_row['code'];
$personal_course_list[$key] = $result_row;
}
}
}
foreach ($sessions as $enreg) {
$session_id = $enreg['id'];
$session_visibility = api_get_session_visibility($session_id);
if ($session_visibility == SESSION_INVISIBLE) {
continue;
}
/* 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 */
$personal_course_list_sql = "SELECT DISTINCT\n course.code code,\n course.title i, CONCAT(user.lastname,' ',user.firstname) t,\n email,\n course.course_language l,\n 1 sort,\n category_code user_course_cat,\n access_start_date,\n access_end_date,\n session.id as session_id,\n session.name as session_name,\n IF((session_course_user.user_id = 3 AND session_course_user.status=2),'2', '5')\n FROM {$tbl_session_course_user} as session_course_user\n INNER JOIN {$tbl_course} AS course\n ON course.id = session_course_user.c_id AND session_course_user.session_id = {$session_id}\n INNER JOIN {$tbl_session} as session ON session_course_user.session_id = session.id\n LEFT JOIN {$tbl_user} as user ON user.id = session_course_user.user_id\n WHERE session_course_user.user_id = {$user_id}\n ORDER BY i";
$course_list_sql_result = Database::query($personal_course_list_sql);
while ($result_row = Database::fetch_array($course_list_sql_result, 'ASSOC')) {
$result_row['course_info'] = api_get_course_info($result_row['code']);
$key = $result_row['session_id'] . ' - ' . $result_row['code'];
if (!isset($personal_course_list[$key])) {
$personal_course_list[$key] = $result_row;
}
}
}
return $personal_course_list;
}
public static function get_sessions_coached_by_user($coach_id, $check_session_rel_user_visibility = false)
{
$sessions = self::get_sessions_by_general_coach($coach_id);
$sessions_by_coach = self::get_sessions_by_coach($coach_id);
if (!empty($sessions_by_coach)) {
$sessions = array_merge($sessions, $sessions_by_coach);
}
//Remove repeated sessions
if (!empty($sessions)) {
$clean_sessions = array();
foreach ($sessions as $session) {
$clean_sessions[$session['id']] = $session;
}
$sessions = $clean_sessions;
}
if ($check_session_rel_user_visibility) {
if (!empty($sessions)) {
$new_session = array();
foreach ($sessions as $session) {
$visibility = api_get_session_visibility($session['id']);
if ($visibility == SESSION_INVISIBLE) {
continue;
}
$new_session[] = $session;
}
$sessions = $new_session;
}
}
return $sessions;
}
/**
* Display code for one specific course a logged in user is subscribed to.
* Shows a link to the course, what's new icons...
*
* $my_course['d'] - course directory
* $my_course['i'] - course title
* $my_course['c'] - visual course code
* $my_course['k'] - system course code
*
* @param array Course details
* @param integer Session ID
* @param string CSS class to apply to course entry
* @param boolean Whether the session is supposedly accessible now
* (not in the case it has passed and is in invisible/unaccessible mode)
* @param bool Whether to show the document quick-loader or not
* @return string The HTML to be printed for the course entry
*
* @version 1.0.3
* @todo refactor into different functions for database calls | logic | display
* @todo replace single-character $my_course['d'] indices
* @todo move code for what's new icons to a separate function to clear things up
* @todo add a parameter user_id so that it is possible to show the
* courselist of other users (=generalisation).
* This will prevent having to write a new function for this.
*/
public static function get_logged_user_course_html($course, $session_id = 0, $class = 'courses', $session_accessible = true, $load_dirs = false)
{
$entityManager = Database::getManager();
$user_id = api_get_user_id();
$course_info = api_get_course_info_by_id($course['real_id']);
$status_course = CourseManager::get_user_in_course_status($user_id, $course_info['code']);
$course_info['status'] = empty($session_id) ? $status_course : STUDENT;
$course_info['id_session'] = $session_id;
$objUser = $entityManager->find('ChamiloUserBundle:User', $user_id);
$objCourse = $entityManager->find('ChamiloCoreBundle:Course', $course['real_id']);
$objSession = $entityManager->find('ChamiloCoreBundle:Session', $session_id);
/*$date_start = $sess[$course_info['id_session']]['access_start_date'];
$date_end = $sess[$course_info['id_session']]['access_end_date'];*/
$now = date('Y-m-d h:i:s');
// Table definitions
$main_user_table = Database::get_main_table(TABLE_MAIN_USER);
$tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
$tbl_session_category = Database::get_main_table(TABLE_MAIN_SESSION_CATEGORY);
$course_access_settings = CourseManager::get_access_settings($course_info['code']);
$course_visibility = $course_access_settings['visibility'];
if ($course_visibility == COURSE_VISIBILITY_HIDDEN) {
return '';
}
$user_in_course_status = CourseManager::get_user_in_course_status(api_get_user_id(), $course_info['code']);
$is_coach = api_is_coach($course_info['id_session'], $course_info['real_id']);
// Display course entry.
// Show a hyperlink to the course, unless the course is closed and user is not course admin.
$session_url = '';
$session_title = '';
$params = array();
$params['icon'] = Display::return_icon('blackboard_blue.png', api_htmlentities($course_info['name']), array(), ICON_SIZE_LARGE);
// Display the "what's new" icons
$notifications = '';
if ($course_visibility != COURSE_VISIBILITY_CLOSED && $course_visibility != COURSE_VISIBILITY_HIDDEN) {
$notifications .= Display::show_notification($course_info);
}
if ($session_accessible) {
if ($course_visibility != COURSE_VISIBILITY_CLOSED || $user_in_course_status == COURSEMANAGER) {
if (empty($course_info['id_session'])) {
$course_info['id_session'] = 0;
}
$sessionCourseAvailable = false;
$sessionCourseStatus = api_get_session_visibility($session_id, $course_info['real_id']);
if (in_array($sessionCourseStatus, array(SESSION_VISIBLE_READ_ONLY, SESSION_VISIBLE, SESSION_AVAILABLE))) {
$sessionCourseAvailable = true;
}
if ($user_in_course_status == COURSEMANAGER || $sessionCourseAvailable) {
$session_url = $course_info['course_public_url'] . '?id_session=' . $course_info['id_session'];
$session_title = '<h4><a href="' . $session_url . '">' . $course_info['name'] . '</a>' . $notifications . '</h4>';
} else {
$session_title = $course_info['name'];
}
} else {
$session_title = $course_info['name'] . ' ' . Display::tag('span', get_lang('CourseClosed'), array('class' => 'item_closed'));
}
} else {
$session_title = $course_info['name'];
}
$showCustomIcon = api_get_setting('course.course_images_in_courses_list');
$iconName = basename($course_info['course_image']);
if ($showCustomIcon === 'true' && $iconName != 'course.png') {
$params['icon'] = Display::img($course_info['course_image'], api_htmlentities($course_info['name']), array());
}
$params['link'] = $session_url;
$params['title'] = $session_title;
$params['right_actions'] = '';
if ($course_visibility != COURSE_VISIBILITY_CLOSED && $course_visibility != COURSE_VISIBILITY_HIDDEN) {
if ($load_dirs) {
$params['right_actions'] .= '<a id="document_preview_' . $course_info['real_id'] . '_' . $course_info['id_session'] . '" class="document_preview" href="javascript:void(0);">' . Display::return_icon('folder.png', get_lang('Documents'), array('align' => 'absmiddle'), ICON_SIZE_SMALL) . '</a>';
$params['right_actions'] .= Display::div('', array('id' => 'document_result_' . $course_info['real_id'] . '_' . $course_info['id_session'], 'class' => 'document_preview_container'));
}
}
if (api_get_setting('course.display_coursecode_in_courselist') == 'true') {
$session_title .= ' (' . $course_info['visual_code'] . ') ';
}
if (api_get_setting('course.display_teacher_in_courselist') == 'true') {
$teacher_list = CourseManager::getTeacherListFromCourseToString($course_info['real_id'], self::USER_SEPARATOR, true);
$course_coachs = self::get_coachs_from_course($course_info['id_session'], $course_info['real_id']);
if ($course_info['status'] == COURSEMANAGER || $course_info['status'] == STUDENT && empty($course_info['id_session']) || empty($course_info['status'])) {
$params['teachers'] = $teacher_list;
}
if ($course_info['status'] == STUDENT && !empty($course_info['id_session']) || $is_coach && $course_info['status'] != COURSEMANAGER) {
$params['coaches'] = $course_coachs;
}
}
$session_title .= isset($course['special_course']) ? ' ' . Display::return_icon('klipper.png', get_lang('CourseAutoRegister')) : '';
$params['title'] = $session_title;
$params['extra'] = '';
$html = $params;
$session_category_id = null;
if (1) {
$session = '';
$active = false;
if (!empty($course_info['session_name'])) {
// Request for the name of the general coach
$sql = 'SELECT lastname, firstname,sc.name
FROM ' . $tbl_session . ' ts
LEFT JOIN ' . $main_user_table . ' tu
ON ts.id_coach = tu.user_id
INNER JOIN ' . $tbl_session_category . ' sc
ON ts.session_category_id = sc.id
WHERE ts.id=' . (int) $course_info['id_session'] . '
LIMIT 1';
$rs = Database::query($sql);
$sessioncoach = Database::store_result($rs);
$sessioncoach = $sessioncoach[0];
$session = array();
$session['title'] = $course_info['session_name'];
$session_category_id = CourseManager::get_session_category_id_by_session_id($course_info['id_session']);
$session['category'] = $sessioncoach['name'];
if ($course_info['access_start_date'] == '0000-00-00') {
//$session['dates'] = get_lang('WithoutTimeLimits');
$session['dates'] = '';
if (api_get_setting('session.show_session_coach') === 'true') {
$session['coach'] = get_lang('GeneralCoach') . ': ' . api_get_person_name($sessioncoach['firstname'], $sessioncoach['lastname']);
}
$active = true;
} else {
$session['dates'] = ' - ' . get_lang('From') . ' ' . $course_info['access_start_date'] . ' ' . get_lang('To') . ' ' . $course_info['access_end_date'];
if (api_get_setting('session.show_session_coach') === 'true') {
$session['coach'] = get_lang('GeneralCoach') . ': ' . api_get_person_name($sessioncoach['firstname'], $sessioncoach['lastname']);
}
$active = $date_start <= $now && $date_end >= $now;
}
}
$user_course_category = '';
if (isset($course_info['user_course_cat'])) {
$user_course_category = $course_info['user_course_cat'];
}
$output = array($user_course_category, $html, $course_info['id_session'], $session, 'active' => $active, 'session_category_id' => $session_category_id);
if (api_get_setting('skill.allow_skills_tool') === 'true') {
$skill = $entityManager->getRepository('ChamiloCoreBundle:Skill')->getLastByUser($objUser, $objCourse, $objSession);
$output['skill'] = null;
if ($skill) {
$output['skill']['name'] = $skill->getName();
$output['skill']['icon'] = $skill->getIcon();
}
}
} else {
$output = array($course_info['user_course_cat'], $html);
}
return $output;
}
/**
*
* @global bool $is_platformAdmin
* @global bool $is_allowedCreateCourse
* @global object $_user
* @global int $_cid
* @global array $_course
* @global int $_real_cid
* @global type $_courseUser
* @global type $is_courseAdmin
* @global type $is_courseTutor
* @global type $is_courseCoach
* @global type $is_courseMember
* @global type $is_sessionAdmin
* @global type $is_allowed_in_course
*
* @param type $course_id
* @param type $reset
*/
static function init_course($course_id, $reset)
{
global $_configuration;
global $is_platformAdmin;
global $is_allowedCreateCourse;
global $_user;
global $_cid;
global $_course;
global $_real_cid;
global $is_courseAdmin;
//course teacher
global $is_courseTutor;
//course teacher - some rights
global $is_courseCoach;
//course coach
global $is_courseMember;
//course student
global $is_sessionAdmin;
global $is_allowed_in_course;
if ($reset) {
// Course session data refresh requested or empty data
if ($course_id) {
$course_table = Database::get_main_table(TABLE_MAIN_COURSE);
$course_cat_table = Database::get_main_table(TABLE_MAIN_CATEGORY);
$sql = "SELECT course.*, course_category.code faCode, course_category.name faName\n FROM {$course_table}\n LEFT JOIN {$course_cat_table}\n ON course.category_code = course_category.code\n WHERE course.code = '{$course_id}'";
$result = Database::query($sql);
if (Database::num_rows($result) > 0) {
$course_data = Database::fetch_array($result);
//@TODO real_cid should be cid, for working with numeric course id
$_real_cid = $course_data['id'];
$_cid = $course_data['code'];
$_course = array();
$_course['real_id'] = $course_data['id'];
$_course['id'] = $course_data['code'];
//auto-assigned integer
$_course['code'] = $course_data['code'];
$_course['name'] = $course_data['title'];
$_course['title'] = $course_data['title'];
$_course['official_code'] = $course_data['visual_code'];
// use in echo
$_course['sysCode'] = $course_data['code'];
// use as key in db
$_course['path'] = $course_data['directory'];
// use as key in path
$_course['titular'] = $course_data['tutor_name'];
// this should be deprecated and use the table course_rel_user
$_course['language'] = $course_data['course_language'];
$_course['extLink']['url'] = $course_data['department_url'];
$_course['extLink']['name'] = $course_data['department_name'];
$_course['categoryCode'] = $course_data['faCode'];
$_course['categoryName'] = $course_data['faName'];
$_course['visibility'] = $course_data['visibility'];
$_course['subscribe_allowed'] = $course_data['subscribe'];
$_course['unsubscribe'] = $course_data['unsubscribe'];
$_course['activate_legal'] = $course_data['activate_legal'];
$_course['show_score'] = $course_data['show_score'];
//used in the work tool
Session::write('_cid', $_cid);
Session::write('_course', $_course);
//@TODO real_cid should be cid, for working with numeric course id
Session::write('_real_cid', $_real_cid);
// if a session id has been given in url, we store the session
// Database Table Definitions
$tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
if (!empty($_GET['id_session'])) {
$_SESSION['id_session'] = intval($_GET['id_session']);
$sql = 'SELECT name FROM ' . $tbl_session . ' WHERE id="' . intval($_SESSION['id_session']) . '"';
$rs = Database::query($sql);
list($_SESSION['session_name']) = Database::fetch_array($rs);
} else {
Session::erase('session_name');
Session::erase('id_session');
}
if (!isset($_SESSION['login_as'])) {
//Course login
if (isset($_user['user_id'])) {
Event::event_course_login(api_get_course_int_id(), $_user['user_id'], api_get_session_id());
}
}
} else {
//exit("WARNING UNDEFINED CID !! ");
header('location:' . api_get_path(WEB_PATH));
}
} else {
Session::erase('_cid');
Session::erase('_real_cid');
Session::erase('_course');
if (!empty($_SESSION)) {
foreach ($_SESSION as $key => $session_item) {
if (strpos($key, 'lp_autolaunch_') === false) {
continue;
} else {
if (isset($_SESSION[$key])) {
Session::erase($key);
}
}
}
}
//Deleting session info
if (api_get_session_id()) {
Session::erase('id_session');
Session::erase('session_name');
}
}
} else {
// Continue with the previous values
if (empty($_SESSION['_course']) or empty($_SESSION['_cid'])) {
//no previous values...
$_cid = -1;
//set default values that will be caracteristic of being unset
$_course = -1;
} else {
$_cid = $_SESSION['_cid'];
$_course = $_SESSION['_course'];
// these lines are usefull for tracking. Indeed we can have lost the id_session and not the cid.
// Moreover, if we want to track a course with another session it can be usefull
if (!empty($_GET['id_session'])) {
$tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
$sql = 'SELECT name FROM ' . $tbl_session . ' WHERE id="' . intval($_SESSION['id_session']) . '"';
$rs = Database::query($sql);
list($_SESSION['session_name']) = Database::fetch_array($rs);
$_SESSION['id_session'] = intval($_GET['id_session']);
}
if (!isset($_SESSION['login_as'])) {
$save_course_access = true;
//The value $_dont_save_user_course_access should be added before the call of global.inc.php see the main/inc/chat.ajax.php file
//Disables the updates in the TRACK_E_COURSE_ACCESS table
if (isset($_dont_save_user_course_access) && $_dont_save_user_course_access == true) {
$save_course_access = false;
}
if ($save_course_access) {
$course_tracking_table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
/*
* When $_configuration['session_lifetime'] is too big 100 hours (in order to let users take exercises with no problems)
* the function Tracking::get_time_spent_on_the_course() returns big values (200h) due the condition:
* login_course_date > now() - INTERVAL $session_lifetime SECOND
*
*/
/*
if (isset($_configuration['session_lifetime'])) {
$session_lifetime = $_configuration['session_lifetime'];
} else {
$session_lifetime = 3600; // 1 hour
} */
$session_lifetime = 3600;
// 1 hour
$time = api_get_utc_datetime();
if (isset($_user['user_id']) && !empty($_user['user_id'])) {
//We select the last record for the current course in the course tracking table
//But only if the login date is < than now + max_life_time
$sql = "SELECT course_access_id FROM {$course_tracking_table}\n WHERE\n user_id = " . intval($_user['user_id']) . " AND\n c_id = '" . api_get_course_int_id() . "' AND\n session_id = " . api_get_session_id() . " AND\n login_course_date > now() - INTERVAL {$session_lifetime} SECOND\n ORDER BY login_course_date DESC LIMIT 0,1";
$result = Database::query($sql);
if (Database::num_rows($result) > 0) {
$i_course_access_id = Database::result($result, 0, 0);
//We update the course tracking table
$sql = "UPDATE {$course_tracking_table}\n SET logout_course_date = '{$time}', counter = counter+1\n WHERE course_access_id = " . intval($i_course_access_id) . " AND session_id = " . api_get_session_id();
Database::query($sql);
} else {
$sql = "INSERT INTO {$course_tracking_table} (c_id, user_id, login_course_date, logout_course_date, counter, session_id)" . "VALUES('" . api_get_course_int_id() . "', '" . $_user['user_id'] . "', '{$time}', '{$time}', '1','" . api_get_session_id() . "')";
Database::query($sql);
}
}
}
}
}
}
/* COURSE / USER REL. INIT */
$session_id = api_get_session_id();
$user_id = isset($_user['user_id']) ? $_user['user_id'] : null;
//Course permissions
$is_courseAdmin = false;
//course teacher
$is_courseTutor = false;
//course teacher - some rights
$is_courseMember = false;
//course student
//Course - User permissions
$is_sessionAdmin = false;
if ($reset) {
if (isset($user_id) && $user_id && isset($_cid) && $_cid) {
//Check if user is subscribed in a course
$course_user_table = Database::get_main_table(TABLE_MAIN_COURSE_USER);
$sql = "SELECT * FROM {$course_user_table}\n WHERE\n user_id = '" . $user_id . "' AND\n relation_type <> " . COURSE_RELATION_TYPE_RRHH . " AND\n course_code = '{$course_id}'";
$result = Database::query($sql);
$cuData = null;
if (Database::num_rows($result) > 0) {
// this user have a recorded state for this course
$cuData = Database::fetch_array($result, 'ASSOC');
$is_courseAdmin = (bool) $cuData['status'] == 1;
$is_courseTutor = (bool) $cuData['is_tutor'] == 1;
$is_courseMember = true;
// Checking if the user filled the course legal agreement
if ($_course['activate_legal'] == 1 && !api_is_platform_admin()) {
$user_is_subscribed = CourseManager::is_user_accepted_legal($user_id, $_course['id'], $session_id);
if (!$user_is_subscribed) {
$url = api_get_path(WEB_CODE_PATH) . 'course_info/legal.php?course_code=' . $_course['code'] . '&session_id=' . $session_id;
header('Location: ' . $url);
exit;
}
}
}
//We are in a session course? Check session permissions
if (!empty($session_id)) {
//I'm not the teacher of the course
if ($is_courseAdmin == false) {
// this user has no status related to this course
// The user is subscribed in a session? The user is a Session coach a Session admin ?
$tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
$tbl_session_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
$tbl_session_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
//Session coach, session admin, course coach admin
$sql = "SELECT session.id_coach, session_admin_id, session_rcru.user_id\n FROM {$tbl_session} session, {$tbl_session_course_user} session_rcru\n WHERE\n session_rcru.session_id = session.id AND\n session_rcru.c_id = '{$_real_cid}' AND\n session_rcru.user_id = '{$user_id}' AND\n session_rcru.session_id = {$session_id} AND\n session_rcru.status = 2";
$result = Database::query($sql);
$row = Database::store_result($result);
//I'm a session admin?
if (isset($row) && isset($row[0]) && $row[0]['session_admin_id'] == $user_id) {
$is_courseMember = false;
$is_courseTutor = false;
$is_courseAdmin = false;
$is_courseCoach = false;
$is_sessionAdmin = true;
} else {
//Im a coach or a student?
$sql = "SELECT user_id, status\n FROM " . $tbl_session_course_user . "\n WHERE\n c_id = '{$_cid}' AND\n user_id = '" . $user_id . "' AND\n session_id = '" . $session_id . "'\n LIMIT 1";
$result = Database::query($sql);
if (Database::num_rows($result)) {
$row = Database::fetch_array($result, 'ASSOC');
$session_course_status = $row['status'];
switch ($session_course_status) {
case '2':
// coach - teacher
$is_courseMember = true;
$is_courseTutor = true;
$is_courseCoach = true;
$is_sessionAdmin = false;
if (api_get_setting('extend_rights_for_coach') == 'true') {
$is_courseAdmin = true;
} else {
$is_courseAdmin = false;
}
break;
case '0':
//student
$is_courseMember = true;
$is_courseTutor = false;
$is_courseAdmin = false;
$is_sessionAdmin = false;
break;
default:
//unregister user
$is_courseMember = false;
$is_courseTutor = false;
$is_courseAdmin = false;
$is_sessionAdmin = false;
break;
}
} else {
//unregister user
$is_courseMember = false;
$is_courseTutor = false;
$is_courseAdmin = false;
$is_sessionAdmin = false;
}
}
}
//If I'm the admin platform i'm a teacher of the course
if ($is_platformAdmin) {
$is_courseAdmin = true;
}
}
} else {
// keys missing => not anymore in the course - user relation
// course
$is_courseMember = false;
$is_courseAdmin = false;
$is_courseTutor = false;
$is_courseCoach = false;
$is_sessionAdmin = false;
}
//Checking the course access
$is_allowed_in_course = false;
if (isset($_course)) {
switch ($_course['visibility']) {
case COURSE_VISIBILITY_OPEN_WORLD:
//3
$is_allowed_in_course = true;
break;
case COURSE_VISIBILITY_OPEN_PLATFORM:
//2
if (isset($user_id) && !api_is_anonymous($user_id)) {
$is_allowed_in_course = true;
}
break;
case COURSE_VISIBILITY_REGISTERED:
//1
if ($is_platformAdmin || $is_courseMember) {
$is_allowed_in_course = true;
}
break;
case COURSE_VISIBILITY_CLOSED:
//0
if ($is_platformAdmin || $is_courseAdmin) {
$is_allowed_in_course = true;
}
break;
case COURSE_VISIBILITY_HIDDEN:
//4
if ($is_platformAdmin) {
$is_allowed_in_course = true;
}
break;
}
}
// check the session visibility
if ($is_allowed_in_course == true) {
//if I'm in a session
if ($session_id != 0) {
if (!$is_platformAdmin) {
// admin and session coach are *not* affected to the invisible session mode
// the coach is not affected because he can log in some days after the end date of a session
$session_visibility = api_get_session_visibility($session_id);
switch ($session_visibility) {
case SESSION_INVISIBLE:
$is_allowed_in_course = false;
break;
}
//checking date
}
}
}
// save the states
Session::write('is_courseAdmin', $is_courseAdmin);
Session::write('is_courseMember', $is_courseMember);
Session::write('is_courseTutor', $is_courseTutor);
Session::write('is_courseCoach', $is_courseCoach);
Session::write('is_allowed_in_course', $is_allowed_in_course);
Session::write('is_sessionAdmin', $is_sessionAdmin);
} else {
// continue with the previous values
$is_courseAdmin = $_SESSION['is_courseAdmin'];
$is_courseTutor = $_SESSION['is_courseTutor'];
$is_courseCoach = $_SESSION['is_courseCoach'];
$is_courseMember = $_SESSION['is_courseMember'];
$is_allowed_in_course = $_SESSION['is_allowed_in_course'];
}
}
/**
* Checks if a student can edit contents in a session depending
* on the session visibility
* @param bool $tutor Whether to check if the user has the tutor role
* @param bool $coach Whether to check if the user has the coach role
* @return boolean, true: the user has the rights to edit, false: he does not
*/
function api_is_allowed_to_session_edit($tutor = false, $coach = false)
{
if (api_is_allowed_to_edit($tutor, $coach)) {
// If I'm a teacher, I will return true in order to not affect the normal behaviour of Chamilo tools.
return true;
} else {
if (api_get_session_id() == 0) {
// I'm not in a session so i will return true to not affect the normal behaviour of Chamilo tools.
return true;
} else {
// I'm in a session and I'm a student
$session_id = api_get_session_id();
// Get the session visibility
$session_visibility = api_get_session_visibility($session_id);
// if 5 the session is still available
//@todo We could load the session_rel_course_rel_user permission to increase the level of detail.
//echo api_get_user_id();
//echo api_get_course_id();
switch ($session_visibility) {
case SESSION_VISIBLE_READ_ONLY:
// 1
return false;
case SESSION_VISIBLE:
// 2
return true;
case SESSION_INVISIBLE:
// 3
return false;
case SESSION_AVAILABLE:
//5
return true;
}
}
}
}
$row['display_start_date'] = api_get_local_time($session['display_start_date'], null, null, true);
$row['display_end_date'] = api_get_local_time($session['display_end_date'], null, null, true);
$row['number_student_per_session'] = $count_courses_in_session;
$row['courses_per_session'] = $count_users_in_session;
if ($visibility == SESSION_INVISIBLE) {
$row['details'] = '<img src="' . api_get_path(WEB_IMG_PATH) . '2rightarrow_na.gif" />';
} else {
$row['details'] = '<a href="' . api_get_self() . '?session_id=' . $session['id'] . '"><img src="' . api_get_path(WEB_IMG_PATH) . '2rightarrow.gif" border="0" /></a>';
}
$all_data[] = $row;
}
echo "<script>\n \$(function() {\n " . Display::grid_js('sessions', null, $columns, $column_model, $extra_params, $all_data) . "\n });\n </script>";
$nb_sessions_past = $nb_sessions_current = 0;
$courses = array();
foreach ($sessions as $session) {
$visibility = api_get_session_visibility($session['id']);
if ($visibility == SESSION_AVAILABLE) {
$nb_sessions_current++;
} else {
$nb_sessions_past++;
}
$courses = array_merge($courses, Tracking::get_courses_list_from_session($session['id']));
}
$nb_courses_per_session = null;
$nb_students_per_session = null;
if ($count_sessions > 0) {
$nb_courses_per_session = round(count($courses) / $count_sessions, 2);
$nb_students_per_session = round($nb_students / $count_sessions, 2);
}
if ($export_csv) {
//csv part
/**
* Get the session coached by a user (general coach and course-session coach)
* @param int $coachId The coach id
* @param boolean $checkSessionRelUserVisibility Check the session visibility
* @return array The session list
*/
public static function getSessionsCoachedByUser($coachId, $checkSessionRelUserVisibility = false)
{
// Get all sessions where $coachId is the general coach
$sessions = self::get_sessions_by_general_coach($coachId);
// Get all sessions where $coachId is the course - session coach
$courseSessionList = self::getCoursesListByCourseCoach($coachId);
$sessionsByCoach = array();
if (!empty($courseSessionList)) {
foreach ($courseSessionList as $courseSession) {
$sessionsByCoach[$courseSession['id_session']] = api_get_session_info($courseSession['id_session']);
}
}
if (!empty($sessionsByCoach)) {
$sessions = array_merge($sessions, $sessionsByCoach);
}
// Remove repeated sessions
if (!empty($sessions)) {
$cleanSessions = array();
foreach ($sessions as $session) {
$cleanSessions[$session['id']] = $session;
}
$sessions = $cleanSessions;
}
if ($checkSessionRelUserVisibility) {
if (!empty($sessions)) {
$newSessions = array();
foreach ($sessions as $session) {
$visibility = api_get_session_visibility($session['id']);
if ($visibility == SESSION_INVISIBLE) {
continue;
}
$newSessions[] = $session;
}
$sessions = $newSessions;
}
}
return $sessions;
}
/**
* Return true if coach is allowed to access this session
* @param int $sessionId
* @return bool
*/
public static function isSessionDateOkForCoach($sessionId)
{
return api_get_session_visibility($sessionId);
$listSessionInfo = api_get_session_info($sessionId);
$dateStart = $listSessionInfo['date_start'];
$dateEnd = $listSessionInfo['date_end'];
$nbDaysAccessBeforeBeginning = $listSessionInfo['nb_days_access_before_beginning'];
$nbDaysAccessAfterEnd = $listSessionInfo['nb_days_access_after_end'];
// no start date
if ($dateStart == '0000-00-00') {
return true;
}
$now = time();
$dateStartForCoach = api_strtotime($dateStart . ' 00:00:00') - $nbDaysAccessBeforeBeginning * 86400;
$dateEndForCoach = api_strtotime($dateEnd . ' 00:00:00') + $nbDaysAccessAfterEnd * 86400;
if ($dateEnd == '0000-00-00') {
// start date but no end date
if ($dateStartForCoach <= $now) {
return true;
}
} else {
// start date and end date
if ($dateStartForCoach <= $now && $now <= $dateEndForCoach) {
return true;
}
}
return false;
}
/**
* Complete sale process. Update sale status to completed
* @param int $saleId The sale ID
* @return boolean
*/
public function completeSale($saleId)
{
$sale = $this->getSale($saleId);
if ($sale['status'] == self::SALE_STATUS_COMPLETED) {
return true;
}
$saleIsCompleted = false;
switch ($sale['product_type']) {
case self::PRODUCT_TYPE_COURSE:
$course = api_get_course_info_by_id($sale['product_id']);
$saleIsCompleted = CourseManager::subscribe_user($sale['user_id'], $course['code']);
break;
case self::PRODUCT_TYPE_SESSION:
SessionManager::suscribe_users_to_session($sale['product_id'], [$sale['user_id']], api_get_session_visibility($sale['product_id']), false);
$saleIsCompleted = true;
break;
}
if ($saleIsCompleted) {
$this->updateSaleStatus($sale['id'], self::SALE_STATUS_COMPLETED);
}
return $saleIsCompleted;
}
/**
* Checks if a student can edit contents in a session depending
* on the session visibility
* @param bool Whether to check if the user has the tutor role
* @param bool Whether to check if the user has the coach role
* @return boolean, true: the user has the rights to edit, false: he does not
*/
function api_is_allowed_to_session_edit($tutor = false, $coach = false)
{
if (api_is_allowed_to_edit($tutor, $coach)) {
// If I'm a teacher, I will return true in order to not affect the normal behaviour of Chamilo tools.
return true;
} else {
if (api_get_session_id() == 0) {
// I'm not in a session so i will return true to not affect the normal behaviour of Chamilo tools.
return true;
} else {
// I'm in a session and I'm a student
$session_id = api_get_session_id();
// Get the session visibility
$session_visibility = api_get_session_visibility($session_id);
// if 5 the session is still available
switch ($session_visibility) {
case SESSION_VISIBLE_READ_ONLY:
// 1
return false;
case SESSION_VISIBLE:
// 2
return true;
case SESSION_INVISIBLE:
// 3
return false;
case SESSION_AVAILABLE:
//4
return true;
}
}
}
}
$is_courseMember = false;
$is_courseAdmin = false;
$is_courseTutor = false;
$is_courseCoach = false;
$is_sessionAdmin = false;
$is_allowed_in_course = false;
}
}
}
// check the session visibility
if ($is_allowed_in_course == true) {
//if I'm in a session
if ($session_id != 0) {
if (!$is_platformAdmin) {
// admin is not affected to the invisible session mode
$session_visibility = api_get_session_visibility($session_id);
switch ($session_visibility) {
case SESSION_INVISIBLE:
$is_allowed_in_course = false;
break;
}
//checking date
}
}
}
// save the states
if (isset($is_courseAdmin)) {
Session::write('is_courseAdmin', $is_courseAdmin);
if ($is_courseAdmin) {
$is_allowed_in_course = true;
}
* refund: A reversal has occurred on this transaction because you have given the customer a refund.
* other: A reversal has occurred on this transaction due to a reason not listed above.
*/
$reasonCode = $resArray["PAYMENTINFO_0_REASONCODE"];
// Insert the user information to activate the user
if ($paymentStatus == "Completed") {
$userId = $_SESSION['bc_user_id'];
if ($_SESSION['bc_codetext'] === 'THIS_IS_A_SESSION') {
$sessionId = $_SESSION['bc_code'];
$all_session_information = SessionManager::fetch($sessionId);
SessionManager::suscribe_users_to_session(
$sessionId,
array($userId),
api_get_session_visibility($sessionId),
false
);
$url = Display::url(
$all_session_information['name'],
api_get_path(WEB_CODE_PATH).'session/index.php?session_id='.$sessionId
);
$_SESSION['bc_message'] = 'EnrollToSessionXSuccessful';
$_SESSION['bc_url'] = $url;
$_SESSION['bc_success'] = true;
} else {
$course_code = $_SESSION['bc_codetext'];
$all_course_information = CourseManager::get_course_information($course_code);
if (CourseManager::subscribe_user($user_id, $course_code)) {
$send = api_get_course_setting('email_alert_to_teacher_on_new_user_in_course', $course_code);
if ($send == 1) {
* The reason for a reversal if TransactionType is reversal:
* none: No reason code
* chargeback: A reversal has occurred on this transaction due to a chargeback by your customer.
* guarantee: A reversal has occurred on this transaction due to your customer triggering a money-back guarantee.
* buyer-complaint: A reversal has occurred on this transaction due to a complaint about the transaction from your customer.
* refund: A reversal has occurred on this transaction because you have given the customer a refund.
* other: A reversal has occurred on this transaction due to a reason not listed above.
*/
$reasonCode = $resArray["PAYMENTINFO_0_REASONCODE"];
// Insert the user information to activate the user
if ($paymentStatus == "Completed") {
$userId = $_SESSION['bc_user_id'];
if ($_SESSION['bc_codetext'] === 'THIS_IS_A_SESSION') {
$sessionId = $_SESSION['bc_code'];
$all_session_information = SessionManager::fetch($sessionId);
SessionManager::suscribe_users_to_session($sessionId, array($userId), api_get_session_visibility($sessionId), false);
$url = Display::url($all_session_information['name'], api_get_path(WEB_CODE_PATH) . 'session/index.php?session_id=' . $sessionId);
$_SESSION['bc_message'] = 'EnrollToSessionXSuccessful';
$_SESSION['bc_url'] = $url;
$_SESSION['bc_success'] = true;
} else {
$course_code = $_SESSION['bc_codetext'];
$all_course_information = CourseManager::get_course_information($course_code);
if (CourseManager::subscribe_user($user_id, $course_code)) {
$send = api_get_course_setting('email_alert_to_teacher_on_new_user_in_course', $course_code);
if ($send == 1) {
CourseManager::email_to_tutor($user_id, $course_code, $send_to_tutor_also = false);
} else {
if ($send == 2) {
CourseManager::email_to_tutor($user_id, $course_code, $send_to_tutor_also = true);
}
/**
* Gives a list of [session_id-course_code] => [status] for the current user.
* @param integer $user_id
* @return array list of statuses (session_id-course_code => status)
*/
public static function get_personal_session_course_list($user_id)
{
// Database Table Definitions
$tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
$tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
$tbl_session_user = Database::get_main_table(TABLE_MAIN_SESSION_USER);
$tbl_course_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
$tbl_session_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
if ($user_id != strval(intval($user_id))) {
return array();
}
//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_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
$join_access_url = "INNER JOIN {$tbl_url_course} url_rel_course ON url_rel_course.c_id = course.id";
$where_access_url = " AND access_url_id = {$access_url_id} ";
}
}
//Courses in which we are subscribed out of any session
$tbl_user_course_category = Database::get_main_table(TABLE_USER_COURSE_CATEGORY);
//INNER JOIN $tbl_user_course_category user_course_category
$personal_course_list_sql = "SELECT course.code,\n course_rel_user.status course_rel_status,\n course_rel_user.sort sort,\n course_rel_user.user_course_cat user_course_cat\n FROM {$tbl_course_user} course_rel_user\n INNER JOIN {$tbl_course} course\n ON course.id = course_rel_user.c_id\n LEFT JOIN {$tbl_user_course_category} user_course_category\n ON course_rel_user.user_course_cat = user_course_category.id\n {$join_access_url}\n WHERE course_rel_user.user_id = '" . $user_id . "' AND\n course_rel_user.relation_type <> " . COURSE_RELATION_TYPE_RRHH . " {$where_access_url}\n ORDER BY user_course_category.sort, course_rel_user.sort, course.title ASC";
$course_list_sql_result = Database::query($personal_course_list_sql);
$personal_course_list = array();
if (Database::num_rows($course_list_sql_result) > 0) {
while ($result_row = Database::fetch_array($course_list_sql_result, 'ASSOC')) {
$course_info = api_get_course_info($result_row['code']);
$result_row['course_info'] = $course_info;
$personal_course_list[] = $result_row;
}
}
// Get the list of sessions where the user is subscribed as student, course coach or session admin
$sessions_sql = "(\n SELECT DISTINCT s.id, s.name\n FROM {$tbl_session_user} su INNER JOIN {$tbl_session} s\n ON (su.id_session = s.id)\n WHERE su.id_user = {$user_id} AND su.relation_type <> " . SESSION_RELATION_TYPE_RRHH . "\n )\n UNION (\n SELECT DISTINCT s.id, s.name\n FROM {$tbl_session} s\n WHERE id_coach = {$user_id}\n )\n UNION (\n SELECT DISTINCT s.id, s.name\n FROM {$tbl_session_user} su INNER JOIN {$tbl_session} s\n ON (su.id_session = s.id)\n INNER JOIN {$tbl_session_course_user} scu\n ON (scu.id_session = s.id)\n WHERE (scu.id_user = {$user_id})\n )\n ORDER BY name ";
//AND scu.status = 2
$result = Database::query($sessions_sql);
$sessions = Database::store_result($result, 'ASSOC');
if (!empty($sessions)) {
foreach ($sessions as $enreg) {
$session_id = $enreg['id'];
$courseList = SessionManager::get_course_list_by_session_id($session_id);
foreach ($courseList as $course) {
$sessionVisibility = api_get_session_visibility($session_id, $course['id']);
if ($sessionVisibility == SESSION_INVISIBLE) {
continue;
}
$course['course_info'] = $course;
$course['session_id'] = $session_id;
$key = $session_id . ' - ' . $course['id'];
$personal_course_list[$key] = $course;
}
}
}
return $personal_course_list;
}
/**
* Gives a list of [session_id-course_code] => [status] for the current user.
* @param integer $user_id
* @return array list of statuses (session_id-course_code => status)
*/
public static function get_personal_session_course_list($user_id)
{
// Database Table Definitions
$tbl_course = Database :: get_main_table(TABLE_MAIN_COURSE);
$tbl_user = Database :: get_main_table(TABLE_MAIN_USER);
$tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
$tbl_session_user = Database :: get_main_table(TABLE_MAIN_SESSION_USER);
$tbl_course_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
$tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
if ($user_id != strval(intval($user_id))) {
return array();
}
// 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_course = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
$join_access_url = "LEFT JOIN $tbl_url_course url_rel_course ON url_rel_course.course_code= course.code";
$where_access_url = " AND access_url_id = $access_url_id ";
}
}
// Courses in which we subscribed out of any session
$tbl_user_course_category = Database :: get_user_personal_table(TABLE_USER_COURSE_CATEGORY);
$sql = "SELECT
course.code,
course_rel_user.status course_rel_status,
course_rel_user.sort sort,
course_rel_user.user_course_cat user_course_cat
FROM ".$tbl_course_user." course_rel_user
LEFT JOIN ".$tbl_course." course
ON course.code = course_rel_user.course_code
LEFT JOIN ".$tbl_user_course_category." user_course_category
ON course_rel_user.user_course_cat = user_course_category.id
$join_access_url
WHERE
course_rel_user.user_id = '".$user_id."' AND
course_rel_user.relation_type <> ".COURSE_RELATION_TYPE_RRHH."
$where_access_url
ORDER BY user_course_category.sort, course_rel_user.sort, course.title ASC";
$course_list_sql_result = Database::query($sql);
$personal_course_list = array();
if (Database::num_rows($course_list_sql_result) > 0) {
while ($result_row = Database::fetch_array($course_list_sql_result, 'ASSOC')) {
$course_info = api_get_course_info($result_row['code']);
$result_row['course_info'] = $course_info;
$personal_course_list[] = $result_row;
}
}
$coachCourseConditions = null;
// Getting sessions that are related to a coach in the session_rel_course_rel_user table
if (api_is_allowed_to_create_course()) {
$sessionListFromCourseCoach = array();
$sql =" SELECT DISTINCT id_session FROM $tbl_session_course_user
WHERE id_user = $user_id AND status = 2 ";
if (SessionManager::orderCourseIsEnabled()) {
//$sql .= "ORDER BY position";
}
$result = Database::query($sql);
if (Database::num_rows($result)) {
$result = Database::store_result($result);
foreach ($result as $session) {
$sessionListFromCourseCoach[]= $session['id_session'];
}
}
if (!empty($sessionListFromCourseCoach)) {
$condition = implode("','", $sessionListFromCourseCoach);
$coachCourseConditions = " OR ( id IN ('$condition'))";
}
}
// Get the list of sessions where the user is subscribed
// This is divided into two different queries
$sessions = array();
$sql = "SELECT DISTINCT id, name, date_start, date_end
FROM $tbl_session_user, $tbl_session
WHERE (
id_session = id AND
id_user = $user_id AND
relation_type <> ".SESSION_RELATION_TYPE_RRHH."
)
$coachCourseConditions
ORDER BY date_start, date_end, name";
$result = Database::query($sql);
if (Database::num_rows($result)>0) {
while ($row = Database::fetch_assoc($result)) {
$sessions[$row['id']] = $row;
}
}
$sql = "SELECT DISTINCT id, name, date_start, date_end
FROM $tbl_session_user, $tbl_session
WHERE (
id_coach = $user_id
)
$coachCourseConditions
ORDER BY date_start, date_end, name";
$result = Database::query($sql);
if (Database::num_rows($result)>0) {
while ($row = Database::fetch_assoc($result)) {
if (empty($sessions[$row['id']])) {
$sessions[$row['id']] = $row;
}
}
}
if (api_is_allowed_to_create_course()) {
foreach ($sessions as $enreg) {
$session_id = $enreg['id'];
$session_visibility = api_get_session_visibility($session_id);
if ($session_visibility == SESSION_INVISIBLE) {
continue;
}
$id_session = $enreg['id'];
$personal_course_list_sql = "SELECT DISTINCT
course.code code,
course.title i,
".(api_is_western_name_order() ? "CONCAT(user.firstname,' ',user.lastname)" : "CONCAT(user.lastname,' ',user.firstname)")." t,
email, course.course_language l,
1 sort,
category_code user_course_cat,
date_start,
date_end,
session.id as id_session,
session.name as session_name
FROM $tbl_session_course_user as session_course_user
INNER JOIN $tbl_course AS course
ON course.code = session_course_user.course_code
INNER JOIN $tbl_session as session
ON session.id = session_course_user.id_session
LEFT JOIN $tbl_user as user
ON user.user_id = session_course_user.id_user OR session.id_coach = user.user_id
WHERE
session_course_user.id_session = $id_session AND (
(session_course_user.id_user = $user_id AND session_course_user.status = 2)
OR session.id_coach = $user_id
)
ORDER BY i";
$course_list_sql_result = Database::query($personal_course_list_sql);
while ($result_row = Database::fetch_array($course_list_sql_result, 'ASSOC')) {
$result_row['course_info'] = api_get_course_info($result_row['code']);
$key = $result_row['id_session'].' - '.$result_row['code'];
$personal_course_list[$key] = $result_row;
}
}
}
foreach ($sessions as $enreg) {
$session_id = $enreg['id'];
$session_visibility = api_get_session_visibility($session_id);
if ($session_visibility == SESSION_INVISIBLE) {
continue;
}
/* 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 */
$personal_course_list_sql = "SELECT DISTINCT
course.code code,
course.title i, CONCAT(user.lastname,' ',user.firstname) t,
email,
course.course_language l,
1 sort,
category_code user_course_cat,
date_start,
date_end,
session.id as id_session,
session.name as session_name,
IF((session_course_user.id_user = 3 AND session_course_user.status=2),'2', '5')
FROM $tbl_session_course_user as session_course_user
INNER JOIN $tbl_course AS course
ON course.code = session_course_user.course_code AND session_course_user.id_session = $session_id
INNER JOIN $tbl_session as session ON session_course_user.id_session = session.id
LEFT JOIN $tbl_user as user ON user.user_id = session_course_user.id_user
WHERE session_course_user.id_user = $user_id
ORDER BY i";
$course_list_sql_result = Database::query($personal_course_list_sql);
while ($result_row = Database::fetch_array($course_list_sql_result, 'ASSOC')) {
$result_row['course_info'] = api_get_course_info($result_row['code']);
$key = $result_row['id_session'].' - '.$result_row['code'];
if (!isset($personal_course_list[$key])) {
$personal_course_list[$key] = $result_row;
}
}
}
return $personal_course_list;
}