/** Subscribes courses to the given session and optionally (default) unsubscribes previous users
* @author Carlos Vargas from existing code
* @param int Session ID
* @param array List of courses IDs
* @param bool Whether to unsubscribe existing users (true, default) or not (false)
* @return void Nothing, or false on error
**/
public static function add_courses_to_session($id_session, $course_list, $empty_courses = true)
{
// security checks
if ($id_session != strval(intval($id_session))) {
return false;
}
// initialisation
$tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
$tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
$tbl_session_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_USER);
$tbl_session_rel_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
// get general coach ID
$id_coach = Database::query("SELECT id_coach FROM {$tbl_session} WHERE id={$id_session}");
$id_coach = Database::fetch_array($id_coach);
$id_coach = $id_coach[0];
// get list of courses subscribed to this session
$rs = Database::query("SELECT c_id FROM {$tbl_session_rel_course} WHERE id_session={$id_session}");
$existingCourses = Database::store_result($rs, 'ASSOC');
$nbr_courses = count($existingCourses);
// get list of users subscribed to this session
$sql = "SELECT id_user\n FROM {$tbl_session_rel_user}\n WHERE id_session = {$id_session} AND relation_type<>" . SESSION_RELATION_TYPE_RRHH . "";
$result = Database::query($sql);
$user_list = Database::store_result($result, 'ASSOC');
// Remove existing courses from the session
if ($empty_courses === true) {
foreach ($existingCourses as $existingCourse) {
if (!in_array($existingCourse['c_id'], $course_list)) {
Database::query("DELETE FROM {$tbl_session_rel_course} WHERE c_id='" . $existingCourse['c_id'] . "' AND id_session={$id_session}");
Database::query("DELETE FROM {$tbl_session_rel_course_rel_user} WHERE c_id='" . $existingCourse['c_id'] . "' AND id_session={$id_session}");
}
}
$nbr_courses = 0;
}
// Pass through the courses list we want to add to the session
foreach ($course_list as $courseId) {
$courseId = Database::escape_string($courseId);
$exists = false;
// check if the course we want to add is already subscribed
foreach ($existingCourses as $existingCourse) {
if ($courseId == $existingCourse['c_id']) {
$exists = true;
}
}
if ($exists == false) {
CourseManager::update_course_ranking($courseId, $id_session);
//if the course isn't subscribed yet
$sql_insert_rel_course = "INSERT INTO {$tbl_session_rel_course} (id_session, c_id) VALUES ('{$id_session}','{$courseId}')";
Database::query($sql_insert_rel_course);
//We add the current course in the existing courses array, to avoid adding another time the current course
$existingCourses[] = array('c_id' => $courseId);
$nbr_courses++;
// subscribe all the users from the session to this course inside the session
$nbr_users = 0;
foreach ($user_list as $enreg_user) {
$enreg_user_id = Database::escape_string($enreg_user['id_user']);
$sql_insert = "INSERT IGNORE INTO {$tbl_session_rel_course_rel_user} (id_session, c_id, id_user) VALUES ('{$id_session}','{$courseId}','{$enreg_user_id}')";
$result = Database::query($sql_insert);
if (Database::affected_rows($result)) {
$nbr_users++;
}
}
SessionManager::subscribe_users_to_session_course($user_list, $id_session, $courseId);
Database::query("UPDATE {$tbl_session_rel_course} SET nbr_users={$nbr_users} WHERE id_session='{$id_session}' AND c_id = '{$courseId}'");
}
}
Database::query("UPDATE {$tbl_session} SET nbr_courses={$nbr_courses} WHERE id='{$id_session}'");
}
/**
* User logs in for the first time to a course
* @param int $courseId
* @param int $user_id
* @param int $session_id
*/
public static function event_course_login($courseId, $user_id, $session_id)
{
$course_tracking_table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
$time = api_get_utc_datetime();
$courseId = intval($courseId);
$user_id = intval($user_id);
$session_id = intval($session_id);
$sql = "INSERT INTO {$course_tracking_table}(c_id, user_id, login_course_date, logout_course_date, counter, session_id)\n VALUES('" . $courseId . "', '" . $user_id . "', '{$time}', '{$time}', '1', '" . $session_id . "')";
Database::query($sql);
// Course catalog stats modifications see #4191
CourseManager::update_course_ranking(null, null, null, null, true, false);
}
/**
* User logs in for the first time to a course
* @param string $course_code
* @param int $user_id
* @param int $session_id
*/
function event_course_login($course_code, $user_id, $session_id)
{
$course_tracking_table = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
//@todo use api_get_utc_datetime
$time = api_get_utc_datetime();
$course_code = Database::escape_string($course_code);
$user_id = intval($user_id);
$session_id = intval($session_id);
$session_lifetime = 3600;
//We select the last record for the current course in the course tracking table
$sql = "SELECT course_access_id\n FROM {$course_tracking_table}\n WHERE\n user_id = {$user_id} AND\n course_code = '{$course_code}' AND\n session_id = {$session_id} AND\n login_course_date > '{$time}' - 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} SET logout_course_date = '{$time}', counter = counter+1\n WHERE course_access_id = " . intval($i_course_access_id) . " AND session_id = " . $session_id;
Database::query($sql);
} else {
$sql = "INSERT INTO {$course_tracking_table} (course_code, user_id, login_course_date, logout_course_date, counter, session_id)" . "VALUES('" . $course_code . "', '" . $user_id . "', '{$time}', '{$time}', '1','" . $session_id . "')";
Database::query($sql);
}
// Course catalog stats modifications see #4191
CourseManager::update_course_ranking(null, null, null, null, true, false);
}
/**
* Updates the access_url_rel_course table with a given user list
* @author Julio Montoya
* @param array $course_list
* @param int access_url_id
* */
public static function update_urls_rel_course($course_list, $access_url_id)
{
$table_url_rel_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
$sql = "SELECT c_id FROM {$table_url_rel_course}\n WHERE access_url_id = " . intval($access_url_id);
$result = Database::query($sql);
$existing_courses = array();
while ($row = Database::fetch_array($result)) {
$existing_courses[] = $row['c_id'];
}
// Adding courses
foreach ($course_list as $course_code) {
if (!in_array($course_code, $courseId)) {
UrlManager::add_course_to_url($courseId, $access_url_id);
CourseManager::update_course_ranking($courseId, 0, $access_url_id);
}
}
// Deleting old courses
foreach ($existing_courses as $courseId) {
if (!in_array($courseId, $course_list)) {
UrlManager::delete_url_rel_course($courseId, $access_url_id);
CourseManager::update_course_ranking($courseId, 0, $access_url_id);
}
}
}
$sql = "SELECT id_user FROM {$tbl_session_rel_user} WHERE id_session = {$id_session} ";
$result = Database::query($sql);
$UserList = Database::store_result($result);
foreach ($CourseList as $enreg_course) {
$enreg_course = Database::escape_string($enreg_course);
$exists = false;
foreach ($existingCourses as $existingCourse) {
if ($enreg_course == $existingCourse['course_code']) {
$exists = true;
}
}
if (!$exists) {
$sql_insert_rel_course = "INSERT INTO {$tbl_session_rel_course}(id_session,course_code) VALUES('{$id_session}','{$enreg_course}')";
Database::query($sql_insert_rel_course);
$course_info = api_get_course_info($enreg_course);
CourseManager::update_course_ranking($course_info['real_id'], $id_session);
//We add in the existing courses table the current course, to not try to add another time the current course
$existingCourses[] = array('course_code' => $enreg_course);
$nbr_users = 0;
foreach ($UserList as $enreg_user) {
$enreg_user = Database::escape_string($enreg_user['id_user']);
$sql_insert = "INSERT IGNORE INTO {$tbl_session_rel_course_rel_user}(id_session,course_code,id_user)\n VALUES('{$id_session}','{$enreg_course}','{$enreg_user}')";
Database::query($sql_insert);
if (Database::affected_rows()) {
$nbr_users++;
}
}
SessionManager::installCourse($id_session, $course_info['real_id']);
Database::query("UPDATE {$tbl_session_rel_course} SET nbr_users={$nbr_users} WHERE id_session='{$id_session}' AND course_code='{$enreg_course}'");
}
}
