* $wanted_code = 'curse' if there are in the DB codes like curse1 curse2 the function will return: course3
* if the course code doest not exist in the DB the same course code will be returned
*
* @return string wanted unused code
*/
public static function generate_nice_next_session_name($session_name)
{
$session_name_ok = !self::session_name_exists($session_name);
if (!$session_name_ok) {
$table = Database::get_main_table(TABLE_MAIN_SESSION);
$session_name = Database::escape_string($session_name);
$sql = "SELECT count(*) as count FROM $table
WHERE name LIKE '$session_name%'";
$result = Database::query($sql);
if (Database::num_rows($result) > 0) {
$row = Database::fetch_array($result);
$count = $row['count'] + 1;
$session_name = $session_name . '_' . $count;
$result = self::session_name_exists($session_name);
if (!$result) {
return $session_name;
}
}
return false;
}
return $session_name;
}
/**
* Edit a session
* @author Carlos Vargas from existing code
* @param integer id
* @param string name
* @param integer year_start
* @param integer month_start
* @param integer day_start
* @param integer year_end
* @param integer month_end
* @param integer day_end
* @param integer nb_days_acess_before
* @param integer nb_days_acess_after
* @param integer nolimit
* @param integer id_coach
* @param integer id_session_category
* @param int $id_visibility
* @param bool
* @param bool
* @param string $description
* @param int $showDescription
* @return $id;
* The parameter id is a primary key
* */
public static function edit_session(
$id,
$name,
$year_start,
$month_start,
$day_start,
$year_end,
$month_end,
$day_end,
$nb_days_acess_before,
$nb_days_acess_after,
$nolimit,
$id_coach,
$id_session_category,
$id_visibility,
$start_limit = true,
$end_limit = true,
$description = null,
$showDescription = null,
$duration = null,
$calendarStartDate = ''
) {
$name = trim(stripslashes($name));
$year_start = intval($year_start);
$month_start = intval($month_start);
$day_start = intval($day_start);
$year_end = intval($year_end);
$month_end = intval($month_end);
$day_end = intval($day_end);
$id_coach = intval($id_coach);
$nb_days_acess_before = intval($nb_days_acess_before);
$nb_days_acess_after = intval($nb_days_acess_after);
$id_session_category = intval($id_session_category);
$id_visibility = intval($id_visibility);
$tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
if (empty($nolimit)) {
$date_start = "$year_start-" . (($month_start < 10) ? "0$month_start" : $month_start) . "-" . (($day_start < 10) ? "0$day_start" : $day_start);
$date_end = "$year_end-" . (($month_end < 10) ? "0$month_end" : $month_end) . "-" . (($day_end < 10) ? "0$day_end" : $day_end);
} else {
$date_start = "0000-00-00";
$date_end = "0000-00-00";
$id_visibility = 1; //force read only
}
if (!empty($no_end_limit)) {
$date_end = "0000-00-00";
}
if (empty($end_limit)) {
$date_end = "0000-00-00";
$id_visibility = 1; //force read only
}
if (empty($start_limit)) {
$date_start = "0000-00-00";
}
if (empty($name)) {
$msg = get_lang('SessionNameIsRequired');
return $msg;
} elseif (empty($id_coach)) {
$msg = get_lang('CoachIsRequired');
return $msg;
} elseif (!empty($start_limit) && empty($nolimit) && (!$month_start || !$day_start || !$year_start || !checkdate($month_start, $day_start, $year_start))) {
$msg = get_lang('InvalidStartDate');
return $msg;
} elseif (!empty($end_limit) && empty($nolimit) && (!$month_end || !$day_end || !$year_end || !checkdate($month_end, $day_end, $year_end))) {
$msg = get_lang('InvalidEndDate');
return $msg;
} elseif (!empty($start_limit) && !empty($end_limit) && empty($nolimit) && $date_start >= $date_end) {
$msg = get_lang('StartDateShouldBeBeforeEndDate');
return $msg;
} else {
$rs = Database::query("SELECT id FROM $tbl_session WHERE name='" . Database::escape_string($name) . "'");
$exists = false;
while ($row = Database::fetch_array($rs)) {
if ($row['id'] != $id) {
$exists = true;
}
}
if ($exists) {
$msg = get_lang('SessionNameAlreadyExists');
return $msg;
} else {
$sessionInfo = SessionManager::fetch($id);
$descriptionCondition = null;
if (array_key_exists('description', $sessionInfo)) {
$descriptionCondition = ' description = "' . Database::escape_string($description) . '" ,';
}
$showDescriptionCondition = null;
if (array_key_exists('show_description', $sessionInfo)) {
$showDescriptionCondition = ' show_description = "' . Database::escape_string($showDescription) . '" ,';
}
$durationCondition = null;
if (self::durationPerUserIsEnabled()) {
if (empty($duration)) {
$duration = null;
} else {
$date_start = '0000-00-00';
$date_end = "0000-00-00";
$duration = intval($duration);
}
$durationCondition = ' duration = "' . $duration . '" ,';
}
$calendarStartDateCondition = '';
if (array_key_exists('calendar_start_date', $sessionInfo)) {
if (empty($calendarStartDate)) {
$calendarStartDateCondition = " calendar_start_date = NULL, ";
} else {
$calendarStartDateCondition = " calendar_start_date = '".api_get_utc_datetime($calendarStartDate)."', ";
}
}
$sql = "UPDATE $tbl_session SET
name='" . Database::escape_string($name) . "',
date_start='" . $date_start . "',
date_end='" . $date_end . "',
id_coach='" . $id_coach . "',
nb_days_access_before_beginning = " . $nb_days_acess_before . ",
nb_days_access_after_end = " . $nb_days_acess_after . ",
session_category_id = " . $id_session_category . " ,
$descriptionCondition
$showDescriptionCondition
$durationCondition
$calendarStartDateCondition
visibility= " . $id_visibility . "
WHERE id='$id'";
Database::query($sql);
return $id;
}
}
}
/**
* Delete session
* @author Carlos Vargas from existing code
* @param array id_checked an array to delete sessions
* @param boolean optional, true if the function is called by a webservice, false otherwise.
* @return void Nothing, or false on error
* */
public static function delete_session($id_checked, $from_ws = false)
{
$tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
$tbl_session_rel_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
$tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
$tbl_session_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_USER);
$tbl_url_session = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
$t_sfv = Database::get_main_table(TABLE_MAIN_SESSION_FIELD_VALUES);
$userId = api_get_user_id();
if (is_array($id_checked)) {
$id_checked = Database::escape_string(implode(',', $id_checked));
} else {
$id_checked = intval($id_checked);
}
if (!api_is_platform_admin() && !$from_ws) {
$sql = 'SELECT session_admin_id FROM ' . Database :: get_main_table(TABLE_MAIN_SESSION) . ' WHERE id IN (' . $id_checked.')';
$rs = Database::query($sql);
if (Database::result($rs, 0, 0) != $userId) {
api_not_allowed(true);
}
}
Database::query("DELETE FROM $tbl_session WHERE id IN($id_checked)");
Database::query("DELETE FROM $tbl_session_rel_course WHERE id_session IN($id_checked)");
Database::query("DELETE FROM $tbl_session_rel_course_rel_user WHERE id_session IN($id_checked)");
Database::query("DELETE FROM $tbl_session_rel_user WHERE id_session IN($id_checked)");
Database::query("DELETE FROM $tbl_url_session WHERE session_id IN($id_checked)");
$sql_delete_sfv = "DELETE FROM $t_sfv WHERE session_id = '$id_checked'";
Database::query($sql_delete_sfv);
// Add event to system log
event_system(
LOG_SESSION_DELETE,
LOG_SESSION_ID,
$id_checked,
api_get_utc_datetime(),
$userId
);
}
/**
* @param int $id_promotion
* @return bool
*/
public static function clear_session_ref_promotion($id_promotion)
{
$tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
$id_promotion = intval($id_promotion);
$sql = "UPDATE $tbl_session SET promotion_id=0
WHERE promotion_id='$id_promotion'";
if (Database::query($sql)) {
return true;
} else {
return false;
}
}
/**
* Subscribes students to the given session and optionally (default) unsubscribes previous users
*
* @author Carlos Vargas from existing code
* @author Julio Montoya. Cleaning code.
* @param int $id_session
* @param array $user_list
* @param int $session_visibility
* @param bool $empty_users
* @param bool $send_email
* @return bool
*/
public static function suscribe_users_to_session(
$id_session,
$user_list,
$session_visibility = SESSION_VISIBLE_READ_ONLY,
$empty_users = true,
$send_email = false
) {
if ($id_session != strval(intval($id_session))) {
return false;
}
foreach ($user_list as $intUser) {
if ($intUser != strval(intval($intUser))) {
return false;
}
}
$tbl_session_rel_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
$tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
$tbl_session_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_USER);
$tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
$session_info = api_get_session_info($id_session);
$session_name = $session_info['name'];
// from function parameter
if (empty($session_visibility)) {
$session_visibility = $session_info['visibility']; //loaded from DB
//default status loaded if empty
if (empty($session_visibility))
$session_visibility = SESSION_VISIBLE_READ_ONLY; // by default readonly 1
} else {
if (!in_array($session_visibility, array(SESSION_VISIBLE_READ_ONLY, SESSION_VISIBLE, SESSION_INVISIBLE))) {
$session_visibility = SESSION_VISIBLE_READ_ONLY;
}
}
$sql = "SELECT id_user FROM $tbl_session_rel_course_rel_user
WHERE id_session = '$id_session' AND status = 0";
$result = Database::query($sql);
$existingUsers = array();
while ($row = Database::fetch_array($result)) {
$existingUsers[] = $row['id_user'];
}
$sql = "SELECT course_code FROM $tbl_session_rel_course
WHERE id_session = '$id_session'";
$result = Database::query($sql);
$course_list = array();
while ($row = Database::fetch_array($result)) {
$course_list[] = $row['course_code'];
}
if ($send_email) {
// Sending emails only
if (is_array($user_list) && count($user_list) > 0) {
foreach ($user_list as $user_id) {
if (!in_array($user_id, $existingUsers)) {
$subject = '[' . get_setting('siteName') . '] ' . get_lang('YourReg') . ' ' . get_setting('siteName');
$user_info = api_get_user_info($user_id);
$content = get_lang('Dear') . " " . stripslashes($user_info['complete_name']) . ",\n\n" . sprintf(get_lang('YouAreRegisterToSessionX'), $session_name) . " \n\n" . get_lang('Address') . " " . get_setting('siteName') . " " . get_lang('Is') . " : " . api_get_path(WEB_PATH) . "\n\n" . get_lang('Problem') . "\n\n" . get_lang('SignatureFormula') . ",\n\n" . get_setting('administratorName') . " " . get_setting('administratorSurname') . "\n" . get_lang('Manager') . " " . get_setting('siteName') . "\nT. " . get_setting('administratorTelephone') . "\n" . get_lang('Email') . " : " . get_setting('emailAdministrator');
MessageManager::send_message(
$user_id,
$subject,
$content,
array(),
array(),
null,
null,
null,
null,
null
);
}
}
}
}
foreach ($course_list as $enreg_course) {
// for each course in the session
$nbr_users = 0;
$enreg_course = Database::escape_string($enreg_course);
$sql = "SELECT DISTINCT id_user FROM $tbl_session_rel_course_rel_user
WHERE id_session = '$id_session' AND course_code='$enreg_course' AND status = 0";
$result = Database::query($sql);
$existingUsers = array();
while ($row = Database::fetch_array($result)) {
$existingUsers[] = $row['id_user'];
}
// Delete existing users
if ($empty_users) {
foreach ($existingUsers as $existing_user) {
if (!in_array($existing_user, $user_list)) {
$sql = "DELETE FROM $tbl_session_rel_course_rel_user
WHERE id_session='$id_session' AND course_code='$enreg_course' AND id_user='$existing_user' AND status = 0";
Database::query($sql);
event_system(
LOG_SESSION_DELETE_USER_COURSE,
LOG_USER_ID,
$existing_user,
api_get_utc_datetime(),
api_get_user_id(),
$enreg_course,
$id_session
);
if (Database::affected_rows()) {
$nbr_users--;
}
}
}
}
// Replace with this new function
// insert new users into session_rel_course_rel_user and ignore if they already exist
foreach ($user_list as $enreg_user) {
if (!in_array($enreg_user, $existingUsers)) {
$enreg_user = Database::escape_string($enreg_user);
$sql = "INSERT IGNORE INTO $tbl_session_rel_course_rel_user(id_session, course_code, id_user, visibility, status)
VALUES('$id_session','$enreg_course','$enreg_user','$session_visibility', '0')";
Database::query($sql);
event_system(
LOG_SESSION_ADD_USER_COURSE,
LOG_USER_ID,
$enreg_user,
api_get_utc_datetime(),
api_get_user_id(),
$enreg_course,
$id_session
);
if (Database::affected_rows()) {
$nbr_users++;
}
}
}
// Count users in this session-course relation
$sql = "SELECT COUNT(id_user) as nbUsers FROM $tbl_session_rel_course_rel_user
WHERE id_session='$id_session' AND course_code='$enreg_course' AND status<>2";
$rs = Database::query($sql);
list($nbr_users) = Database::fetch_array($rs);
// update the session-course relation to add the users total
$sql = "UPDATE $tbl_session_rel_course SET nbr_users = $nbr_users
WHERE id_session='$id_session' AND course_code = '$enreg_course'";
Database::query($sql);
}
// Delete users from the session
if ($empty_users === true) {
$sql = "DELETE FROM $tbl_session_rel_user
WHERE id_session = $id_session AND relation_type<>" . SESSION_RELATION_TYPE_RRHH . "";
Database::query($sql);
}
// Insert missing users into session
$nbr_users = 0;
foreach ($user_list as $enreg_user) {
$enreg_user = Database::escape_string($enreg_user);
$nbr_users++;
$sql = "INSERT IGNORE INTO $tbl_session_rel_user (id_session, id_user)
VALUES ('$id_session', '$enreg_user')";
Database::query($sql);
}
// update number of users in the session
$nbr_users = count($user_list);
if ($empty_users) {
// update number of users in the session
$sql = "UPDATE $tbl_session SET nbr_users= $nbr_users
WHERE id='$id_session' ";
Database::query($sql);
} else {
$sql = "UPDATE $tbl_session SET nbr_users= nbr_users + $nbr_users
WHERE id='$id_session'";
Database::query($sql);
}
}
/**
* Returns user list of the current users subscribed in the course-session
* @param int $sessionId
* @param array $courseInfo
* @param int $status
*
* @return array
*/
public static function getUsersByCourseSession(
$sessionId,
$courseInfo,
$status = null
) {
$sessionId = intval($sessionId);
$courseCode = $courseInfo['code'];
if (empty($sessionId) || empty($courseCode)) {
return array();
}
$statusCondition = null;
if (isset($status) && !is_null($status)) {
$status = intval($status);
$statusCondition = " AND status = $status";
}
$table = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
$sql = "SELECT DISTINCT id_user
FROM $table
WHERE
id_session = '$sessionId' AND
course_code='$courseCode'
$statusCondition
";
$result = Database::query($sql);
$existingUsers = array();
while ($row = Database::fetch_array($result)) {
$existingUsers[] = $row['id_user'];
}
return $existingUsers;
}
/**
* Remove a list of users from a course-session
* @param array $userList
* @param int $sessionId
* @param array $courseInfo
* @param int $status
* @param bool $updateTotal
* @return bool
*/
public static function removeUsersFromCourseSession(
$userList,
$sessionId,
$courseInfo,
$status = null,
$updateTotal = true
) {
$table = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
$tableSessionCourse = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
$sessionId = intval($sessionId);
if (empty($sessionId) || empty($userList) || empty($courseInfo)) {
return false;
}
$courses = SessionManager::getCoursesInSession($sessionId);
$courseCode = Database::escape_string($courseInfo['code']);
$statusCondition = null;
if (isset($status) && !is_null($status)) {
$status = intval($status);
$statusCondition = " AND status = $status";
}
foreach ($userList as $userId) {
$userId = intval($userId);
$sql = "DELETE FROM $table
WHERE
id_session='$sessionId' AND
course_code='$courseCode' AND
id_user='$userId'
$statusCondition
";
Database::query($sql);
if (count($courses) == 1) {
SessionManager::unsubscribe_user_from_session($sessionId, $userId);
}
}
if ($updateTotal) {
// Count users in this session-course relation
$sql = "SELECT COUNT(id_user) as nbUsers
FROM $table
WHERE
id_session='$sessionId' AND
course_code='$courseCode' AND
status<>2";
$result = Database::query($sql);
list($userCount) = Database::fetch_array($result);
// update the session-course relation to add the users total
$sql = "UPDATE $tableSessionCourse SET
nbr_users = $userCount
WHERE id_session='$sessionId' AND course_code = '$courseCode'";
Database::query($sql);
}
}
/**
* Subscribe a user to an specific course inside a session.
*
* @param array $user_list
* @param int $session_id
* @param string $course_code
* @param int $session_visibility
* @param bool $removeUsersNotInList
* @return bool
*/
public static function subscribe_users_to_session_course(
$user_list,
$session_id,
$course_code,
$session_visibility = SESSION_VISIBLE_READ_ONLY,
$removeUsersNotInList = false
) {
$tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
$tbl_session_rel_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
$tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
$tbl_session_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_USER);
if (empty($session_id) || empty($course_code)) {
return false;
}
$session_id = intval($session_id);
$course_code = Database::escape_string($course_code);
$session_visibility = intval($session_visibility);
if ($removeUsersNotInList) {
$courseInfo = api_get_course_info($course_code);
$currentUsers = self::getUsersByCourseSession($session_id, $courseInfo, 0);
if (!empty($user_list)) {
$userToDelete = array_diff($currentUsers, $user_list);
} else {
$userToDelete = $currentUsers;
}
if (!empty($userToDelete)) {
self::removeUsersFromCourseSession(
$userToDelete,
$session_id,
$courseInfo,
0,
true
);
}
}
$nbr_users = 0;
foreach ($user_list as $enreg_user) {
$enreg_user = intval($enreg_user);
// Checking if user exists in session - course - user table.
$sql = "SELECT count(id_user) as count
FROM $tbl_session_rel_course_rel_user
WHERE
id_session = $session_id AND
course_code = '$course_code' and
id_user = $enreg_user ";
$result = Database::query($sql);
$count = 0;
if (Database::num_rows($result) > 0) {
$row = Database::fetch_array($result, 'ASSOC');
$count = $row['count'];
}
if ($count == 0) {
$sql = "INSERT IGNORE INTO $tbl_session_rel_course_rel_user (id_session, course_code, id_user, visibility)
VALUES ('$session_id','$course_code','$enreg_user','$session_visibility')";
Database::query($sql);
if (Database::affected_rows()) {
$nbr_users++;
}
}
// Checking if user exists in session - user table.
$sql = "SELECT count(id_user) as count
FROM $tbl_session_rel_user
WHERE id_session = $session_id AND id_user = $enreg_user ";
$result = Database::query($sql);
$count = 0;
if (Database::num_rows($result) > 0) {
$row = Database::fetch_array($result, 'ASSOC');
$count = $row['count'];
}
if (empty($count)) {
// If user is not registered to a session then add it.
$sql = "INSERT IGNORE INTO $tbl_session_rel_user (id_session, id_user)
VALUES ('$session_id', '$enreg_user')";
Database::query($sql);
$sql = "UPDATE $tbl_session SET nbr_users = nbr_users + 1
WHERE id='$session_id' ";
Database::query($sql);
}
}
// count users in this session-course relation
$sql = "SELECT COUNT(id_user) as nbUsers
FROM $tbl_session_rel_course_rel_user
WHERE id_session='$session_id' AND course_code='$course_code' AND status<>2";
$rs = Database::query($sql);
list($nbr_users) = Database::fetch_array($rs);
// update the session-course relation to add the users total
$sql = "UPDATE $tbl_session_rel_course SET nbr_users=$nbr_users
WHERE id_session='$session_id' AND course_code='$course_code'";
Database::query($sql);
}
/**
* Unsubscribe user from session
*
* @param int Session id
* @param int User id
* @return bool True in case of success, false in case of error
*/
public static function unsubscribe_user_from_session($session_id, $user_id)
{
$session_id = (int) $session_id;
$user_id = (int) $user_id;
$tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
$tbl_session_rel_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
$tbl_session_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_USER);
$tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
$delete_sql = "DELETE FROM $tbl_session_rel_user
WHERE
id_session = '$session_id' AND
id_user ='$user_id' AND
relation_type <> " . SESSION_RELATION_TYPE_RRHH . "";
Database::query($delete_sql);
$return = Database::affected_rows();
// Update number of users
$sql = "UPDATE $tbl_session
SET nbr_users = nbr_users - $return
WHERE id='$session_id' ";
Database::query($sql);
// Get the list of courses related to this session
$course_list = SessionManager::get_course_list_by_session_id($session_id);
if (!empty($course_list)) {
foreach ($course_list as $course) {
$course_code = $course['code'];
// Delete user from course
$sql = "DELETE FROM $tbl_session_rel_course_rel_user
WHERE id_session='$session_id' AND course_code='$course_code' AND id_user='$user_id'";
Database::query($sql);
event_system(
LOG_SESSION_DELETE_USER_COURSE,
LOG_USER_ID,
$user_id,
api_get_utc_datetime(),
api_get_user_id(),
$course_code,
$session_id
);
if (Database::affected_rows()) {
// Update number of users in this relation
$sql = "UPDATE $tbl_session_rel_course SET nbr_users = nbr_users - 1
WHERE id_session='$session_id' AND course_code='$course_code'";
Database::query($sql);
}
}
}
return true;
}
/**
* Subscribes courses to the given session and optionally (default)
* unsubscribes previous users
* @author Carlos Vargas from existing code
* @param int $sessionId
* @param array $courseList List of courses IDs
* @param bool $removeExistingCoursesUsers Whether to unsubscribe
* existing courses and users (true, default) or not (false)
* @return void Nothing, or false on error
* */
public static function add_courses_to_session(
$sessionId,
$courseList,
$removeExistingCoursesWithUsers = true
) {
$sessionId = intval($sessionId);
if (empty($sessionId) || empty($courseList)) {
return false;
}
$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 list of courses subscribed to this session
$sql = "SELECT course_code
FROM $tbl_session_rel_course
WHERE id_session=$sessionId";
$rs = Database::query($sql );
$existingCourses = Database::store_result($rs);
$nbr_courses = count($existingCourses);
// get list of users subscribed to this session
$sql = "SELECT id_user
FROM $tbl_session_rel_user
WHERE
id_session = $sessionId AND
relation_type<>" . SESSION_RELATION_TYPE_RRHH;
$result = Database::query($sql);
$user_list = Database::store_result($result);
// Remove existing courses from the session.
if ($removeExistingCoursesWithUsers === true) {
foreach ($existingCourses as $existingCourse) {
if (!in_array($existingCourse['course_code'], $courseList)) {
$courseInfo = api_get_course_info($existingCourse['course_code']);
$sql = "DELETE FROM $tbl_session_rel_course
WHERE course_code='" . $existingCourse['course_code'] . "' AND id_session=$sessionId";
Database::query($sql);
$sql = "DELETE FROM $tbl_session_rel_course_rel_user
WHERE course_code='" . $existingCourse['course_code'] . "' AND id_session=$sessionId";
Database::query($sql);
event_system(
LOG_SESSION_DELETE_COURSE,
LOG_COURSE_ID,
$existingCourse['c_id'],
api_get_utc_datetime(),
api_get_user_id(),
$existingCourse['course_code'],
$sessionId
);
CourseManager::remove_course_ranking(
$courseInfo['real_id'],
$sessionId
);
$nbr_courses--;
}
}
}
// Pass through the courses list we want to add to the session
foreach ($courseList as $enreg_course) {
$enreg_course = Database::escape_string($enreg_course);
$exists = false;
// check if the course we want to add is already subscribed
foreach ($existingCourses as $existingCourse) {
if ($enreg_course == $existingCourse['course_code']) {
$exists = true;
}
}
if (!$exists) {
//if the course isn't subscribed yet
$sql = "INSERT INTO $tbl_session_rel_course (id_session, course_code)
VALUES ('$sessionId','$enreg_course')";
Database::query($sql);
event_system(
LOG_SESSION_ADD_COURSE,
LOG_COURSE_ID,
$enreg_course,
api_get_utc_datetime(),
api_get_user_id(),
$enreg_course,
$sessionId
);
//We add the current course in the existing courses array, to avoid adding another time the current course
$existingCourses[] = array('course_code' => $enreg_course);
$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 = intval($enreg_user['id_user']);
$sql = "INSERT IGNORE INTO $tbl_session_rel_course_rel_user (id_session, course_code, id_user)
VALUES ($sessionId,'$enreg_course',$enreg_user_id)";
Database::query($sql);
event_system(
LOG_SESSION_ADD_USER_COURSE,
LOG_USER_ID,
$enreg_user_id,
api_get_utc_datetime(),
api_get_user_id(),
$enreg_course,
$sessionId
);
if (Database::affected_rows()) {
$nbr_users++;
}
}
$sql = "UPDATE $tbl_session_rel_course
SET nbr_users=$nbr_users
WHERE id_session='$sessionId' AND course_code='$enreg_course'";
Database::query($sql);
}
}
$sql = "UPDATE $tbl_session
SET nbr_courses = $nbr_courses
WHERE id = '$sessionId'";
Database::query($sql);
}
/**
* Unsubscribe course from a session
*
* @param int Session id
* @param int Course id
* @return bool True in case of success, false otherwise
*/
public static function unsubscribe_course_from_session($session_id, $course_id)
{
$session_id = (int) $session_id;
$course_id = (int) $course_id;
$tbl_session_rel_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
$tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
$tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
// Get course code
$course_code = CourseManager::get_course_code_from_course_id($course_id);
if (empty($course_code)) {
return false;
}
// Unsubscribe course
$sql = "DELETE FROM $tbl_session_rel_course
WHERE course_code='$course_code' AND id_session='$session_id'";
Database::query($sql);
$nb_affected = Database::affected_rows();
$sql = "DELETE FROM $tbl_session_rel_course_rel_user
WHERE course_code='$course_code' AND id_session='$session_id'";
Database::query($sql);
event_system(
LOG_SESSION_DELETE_COURSE,
LOG_COURSE_ID,
$course_id,
api_get_utc_datetime(),
api_get_user_id(),
$course_code,
$session_id
);
if ($nb_affected > 0) {
// Update number of courses in the session
$sql = "UPDATE $tbl_session SET nbr_courses= nbr_courses + $nb_affected WHERE id='$session_id' ";
Database::query($sql);
return true;
} else {
return false;
}
}
/**
* Creates a new extra field for a given session
* @param string Field's internal variable name
* @param int Field's type
* @param string Field's language var name
* @return int new extra field id
*/
public static function create_session_extra_field($fieldvarname, $fieldtype, $fieldtitle)
{
// database table definition
$t_sf = Database::get_main_table(TABLE_MAIN_SESSION_FIELD);
$fieldvarname = Database::escape_string($fieldvarname);
$fieldtitle = Database::escape_string($fieldtitle);
$fieldtype = (int) $fieldtype;
$time = time();
$sql_field = "SELECT id FROM $t_sf WHERE field_variable = '$fieldvarname'";
$res_field = Database::query($sql_field);
$r_field = Database::fetch_row($res_field);
if (Database::num_rows($res_field) > 0) {
$field_id = $r_field[0];
} else {
// save new fieldlabel into course_field table
$sql = "SELECT MAX(field_order) FROM $t_sf";
$res = Database::query($sql);
$order = 0;
if (Database::num_rows($res) > 0) {
$row = Database::fetch_row($res);
$order = $row[0] + 1;
}
$sql = "INSERT INTO $t_sf SET
field_type = '$fieldtype',
field_variable = '$fieldvarname',
field_display_text = '$fieldtitle',
field_order = '$order',
tms = FROM_UNIXTIME($time)";
Database::query($sql);
$field_id = Database::insert_id();
}
return $field_id;
}
/**
* Update an extra field value for a given session
* @param integer Course ID
* @param string Field variable name
* @param string Field value
* @return boolean true if field updated, false otherwise
*/
public static function update_session_extra_field_value($session_id, $fname, $fvalue = '')
{
$t_sf = Database::get_main_table(TABLE_MAIN_SESSION_FIELD);
$t_sfv = Database::get_main_table(TABLE_MAIN_SESSION_FIELD_VALUES);
$fname = Database::escape_string($fname);
$session_id = (int) $session_id;
$fvalues = '';
if (is_array($fvalue)) {
foreach ($fvalue as $val) {
$fvalues .= Database::escape_string($val) . ';';
}
if (!empty($fvalues)) {
$fvalues = substr($fvalues, 0, -1);
}
} else {
$fvalues = Database::escape_string($fvalue);
}
$sqlsf = "SELECT * FROM $t_sf WHERE field_variable='$fname'";
$ressf = Database::query($sqlsf);
if (Database::num_rows($ressf) == 1) {
//ok, the field exists
// Check if enumerated field, if the option is available
$rowsf = Database::fetch_array($ressf);
$tms = time();
$sqlsfv = "SELECT * FROM $t_sfv
WHERE session_id = '$session_id' AND field_id = '" . $rowsf['id'] . "' ORDER BY id";
$ressfv = Database::query($sqlsfv);
$n = Database::num_rows($ressfv);
if ($n > 1) {
//problem, we already have to values for this field and user combination - keep last one
while ($rowsfv = Database::fetch_array($ressfv)) {
if ($n > 1) {
$sqld = "DELETE FROM $t_sfv WHERE id = " . $rowsfv['id'];
$resd = Database::query($sqld);
$n--;
}
$rowsfv = Database::fetch_array($ressfv);
if ($rowsfv['field_value'] != $fvalues) {
$sqlu = "UPDATE $t_sfv SET field_value = '$fvalues', tms = FROM_UNIXTIME($tms)
WHERE id = " . $rowsfv['id'];
$resu = Database::query($sqlu);
return($resu ? true : false);
}
return true;
}
} else if ($n == 1) {
//we need to update the current record
$rowsfv = Database::fetch_array($ressfv);
if ($rowsfv['field_value'] != $fvalues) {
$sqlu = "UPDATE $t_sfv SET field_value = '$fvalues', tms = FROM_UNIXTIME($tms)
WHERE id = " . $rowsfv['id'];
//error_log('UM::update_extra_field_value: '.$sqlu);
$resu = Database::query($sqlu);
return($resu ? true : false);
}
return true;
} else {
$sqli = "INSERT INTO $t_sfv (session_id,field_id,field_value,tms) " .
"VALUES ('$session_id'," . $rowsf['id'] . ",'$fvalues',FROM_UNIXTIME($tms))";
$resi = Database::query($sqli);
return $resi ? true : false;
}
} else {
return false; //field not found
}
}
/**
* Checks the relationship between a session and a course.
* @param int $session_id
* @param string $course_id (course code)
* @return bool Returns TRUE if the session and the course are related, FALSE otherwise.
* */
public static function relation_session_course_exist($session_id, $course_id)
{
$tbl_session_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
$return_value = false;
$sql = "SELECT course_code FROM $tbl_session_course
WHERE
id_session = " . intval($session_id) . " AND
course_code = '" . Database::escape_string($course_id) . "'";
$result = Database::query($sql);
$num = Database::num_rows($result);
if ($num > 0) {
$return_value = true;
}
return $return_value;
}
/**
* Get the session information by name
* @param string session name
* @return mixed false if the session does not exist, array if the session exist
* */
public static function get_session_by_name($session_name)
{
$tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
$session_name = trim($session_name);
if (empty($session_name)) {
return false;
}
$sql = 'SELECT *
FROM ' . $tbl_session . '
WHERE name = "' . Database::escape_string($session_name) . '"';
$result = Database::query($sql);
$num = Database::num_rows($result);
if ($num > 0) {
return Database::fetch_array($result);
} else {
return false;
}
}
/**
* Create a session category
* @author Jhon Hinojosa