function UpdateSettings($setting, $val, $type = '')
{
global $server, $user, $pass, $database, $pre;
if (empty($type)) {
$type = 'admin';
}
//Connect to database
require_once "sources/class.database.php";
$db = new Database($server, $user, $pass, $database, $pre);
$db->connect();
//Check if setting is already in DB. If NO then insert, if YES then update.
$data = $db->fetch_row("SELECT COUNT(*) FROM " . $pre . "misc WHERE type='" . $type . "' AND intitule = '" . $setting . "'");
if ($data[0] == 0) {
$db->query_insert("misc", array('valeur' => $val, 'type' => $type, 'intitule' => $setting));
//in case of stats enabled, add the actual time
if ($setting == 'send_stats') {
$db->query_insert("misc", array('valeur' => time(), 'type' => $type, 'intitule' => $setting . '_time'));
}
} else {
$db->query_update("misc", array('valeur' => $val), "type='" . $type . "' AND intitule = '" . $setting . "'");
//in case of stats enabled, update the actual time
if ($setting == 'send_stats') {
//Check if previous time exists, if not them insert this value in DB
$data_time = $db->fetch_row("SELECT COUNT(*) FROM " . $pre . "misc WHERE type='" . $type . "' AND intitule = '" . $setting . "_time'");
if ($data_time[0] == 0) {
$db->query_insert("misc", array('valeur' => 0, 'type' => $type, 'intitule' => $setting . '_time'));
} else {
$db->query_update("misc", array('valeur' => 0), "type='" . $type . "' AND intitule = '" . $setting . "_time'");
}
}
}
//save in variable
if ($type == "admin") {
$_SESSION['settings'][$setting] = $val;
} else {
if ($type == "settings") {
$settings[$setting] = $val;
}
}
}
/**
*
* Returns the URL of a document
* This function is loaded when using a gradebook as a tab (gradebook = -1) see issue #2705
*/
public function get_view_url($stud_id)
{
// find a file uploaded by the given student,
// with the same title as the evaluation name
$eval = $this->get_evaluation();
$sql = 'SELECT filename FROM ' . $this->get_dropbox_table() . ' WHERE c_id = ' . $this->course_id . ' AND uploader_id = ' . intval($stud_id) . " AND title = '" . Database::escape_string($eval->get_name()) . "'";
$result = Database::query($sql);
if ($fileurl = Database::fetch_row($result)) {
return null;
} else {
return null;
}
}
/**
* Get course data to display
*/
function get_request_data($from, $number_of_items, $column, $direction)
{
global $keyword;
$course_request_table = Database::get_main_table(TABLE_MAIN_COURSE_REQUEST);
$sql = "SELECT id AS col0,\n code AS col1,\n title AS col2,\n category_code AS col3,\n tutor_name AS col4,\n request_date AS col5,\n id AS col6\n FROM {$course_request_table}\n WHERE status = " . COURSE_REQUEST_ACCEPTED;
if ($keyword != '') {
$sql .= " AND (title LIKE '%" . $keyword . "%' OR code LIKE '%" . $keyword . "%' OR visual_code LIKE '%" . $keyword . "%')";
}
$sql .= " ORDER BY col{$column} {$direction} ";
$sql .= " LIMIT {$from},{$number_of_items}";
$res = Database::query($sql);
$course_requests = array();
while ($course_request = Database::fetch_row($res)) {
$course_request[5] = api_get_local_time($course_request[5]);
$course_requests[] = $course_request;
}
return $course_requests;
}
/**
* Gets the information about some classes.
* @param int $from
* @param int $number_of_items
* @param string $direction
*/
function get_class_data($from, $number_of_items, $column, $direction)
{
$tbl_class_user = Database::get_main_table(TABLE_MAIN_CLASS_USER);
$tbl_class = Database::get_main_table(TABLE_MAIN_CLASS);
$from = Database::escape_string($from);
$number_of_items = Database::escape_string($number_of_items);
$column = Database::escape_string($column);
$direction = Database::escape_string($direction);
$sql = "SELECT \tid AS col0, name AS col1, COUNT(user_id) AS col2, id AS col3\n FROM {$tbl_class}\n LEFT JOIN {$tbl_class_user} ON id=class_id ";
if (isset($_GET['keyword'])) {
$sql .= " WHERE (name LIKE '%" . Database::escape_string(trim($_GET['keyword'])) . "%')";
}
$sql .= " GROUP BY id,name ORDER BY col{$column} {$direction} LIMIT {$from},{$number_of_items}";
$res = Database::query($sql);
$classes = array();
while ($class = Database::fetch_row($res)) {
$classes[] = $class;
}
return $classes;
}
/**
* Get course data to display
*/
function get_request_data($from, $number_of_items, $column, $direction)
{
$keyword = isset($_GET['keyword']) ? Database::escape_string(trim($_GET['keyword'])) : null;
$course_request_table = Database::get_main_table(TABLE_MAIN_COURSE_REQUEST);
$from = intval($from);
$number_of_items = intval($number_of_items);
$column = intval($column);
$direction = !in_array(strtolower(trim($direction)), ['asc', 'desc']) ? 'asc' : $direction;
$sql = "SELECT\n id AS col0,\n code AS col1,\n title AS col2,\n category_code AS col3,\n tutor_name AS col4,\n request_date AS col5,\n id AS col6\n FROM {$course_request_table}\n WHERE status = " . COURSE_REQUEST_ACCEPTED;
if ($keyword != '') {
$sql .= " AND (\n title LIKE '%" . $keyword . "%' OR\n code LIKE '%" . $keyword . "%' OR\n visual_code LIKE '%" . $keyword . "%'\n )";
}
$sql .= " ORDER BY col{$column} {$direction} ";
$sql .= " LIMIT {$from},{$number_of_items}";
$res = Database::query($sql);
$course_requests = array();
while ($course_request = Database::fetch_row($res)) {
$course_request[5] = api_get_local_time($course_request[5]);
$course_requests[] = $course_request;
}
return $course_requests;
}
/**
* @param string $category_code
* @param int $random_value
* @param array $limit will be used if $random_value is not set.
* This array should contains 'start' and 'length' keys
* @return array
*/
function browseCoursesInCategory($category_code, $random_value = null, $limit = array())
{
$tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
$specialCourseList = CourseManager::get_special_course_list();
$without_special_courses = '';
if (!empty($specialCourseList)) {
$without_special_courses = ' AND course.code NOT IN (' . implode(',', $specialCourseList) . ')';
}
$visibilityCondition = null;
$hidePrivate = api_get_setting('platform.course_catalog_hide_private');
if ($hidePrivate === 'true') {
$courseInfo = api_get_course_info();
$courseVisibility = $courseInfo['visibility'];
$visibilityCondition = ' AND course.visibility <> 1';
}
if (!empty($random_value)) {
$random_value = intval($random_value);
$sql = "SELECT COUNT(*) FROM {$tbl_course}";
$result = Database::query($sql);
list($num_records) = Database::fetch_row($result);
if (api_is_multiple_url_enabled()) {
$url_access_id = api_get_current_access_url_id();
$tbl_url_rel_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
$sql = "SELECT COUNT(*) FROM {$tbl_course} course\n INNER JOIN {$tbl_url_rel_course} as url_rel_course\n ON (url_rel_course.c_id = course.id)\n WHERE access_url_id = {$url_access_id} ";
$result = Database::query($sql);
list($num_records) = Database::fetch_row($result);
$sql = "SELECT course.id FROM {$tbl_course} course\n INNER JOIN {$tbl_url_rel_course} as url_rel_course\n ON (url_rel_course.c_id = course.id)\n WHERE\n access_url_id = {$url_access_id} AND\n RAND()*{$num_records}< {$random_value}\n {$without_special_courses} {$visibilityCondition}\n ORDER BY RAND()\n LIMIT 0, {$random_value}";
} else {
$sql = "SELECT id FROM {$tbl_course} course\n WHERE RAND()*{$num_records}< {$random_value} {$without_special_courses} {$visibilityCondition}\n ORDER BY RAND()\n LIMIT 0, {$random_value}";
}
$result = Database::query($sql);
$id_in = null;
while (list($id) = Database::fetch_row($result)) {
if ($id_in) {
$id_in .= ",{$id}";
} else {
$id_in = "{$id}";
}
}
if ($id_in === null) {
return array();
}
$sql = "SELECT * FROM {$tbl_course} WHERE id IN({$id_in})";
} else {
$limitFilter = self::getLimitFilterFromArray($limit);
$category_code = Database::escape_string($category_code);
if (empty($category_code) || $category_code == "ALL") {
$sql = "SELECT * FROM {$tbl_course}\n WHERE\n 1=1\n {$without_special_courses}\n {$visibilityCondition}\n ORDER BY title {$limitFilter} ";
} else {
if ($category_code == 'NONE') {
$category_code = '';
}
$sql = "SELECT * FROM {$tbl_course}\n WHERE\n category_code='{$category_code}'\n {$without_special_courses}\n {$visibilityCondition}\n ORDER BY title {$limitFilter} ";
}
//showing only the courses of the current Chamilo access_url_id
if (api_is_multiple_url_enabled()) {
$url_access_id = api_get_current_access_url_id();
$tbl_url_rel_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
if ($category_code != "ALL") {
$sql = "SELECT * FROM {$tbl_course} as course\n INNER JOIN {$tbl_url_rel_course} as url_rel_course\n ON (url_rel_course.c_id = course.id)\n WHERE\n access_url_id = {$url_access_id} AND\n category_code='{$category_code}'\n {$without_special_courses}\n {$visibilityCondition}\n ORDER BY title {$limitFilter}";
} else {
$sql = "SELECT * FROM {$tbl_course} as course\n INNER JOIN {$tbl_url_rel_course} as url_rel_course\n ON (url_rel_course.c_id = course.id)\n WHERE\n access_url_id = {$url_access_id}\n {$without_special_courses}\n {$visibilityCondition}\n ORDER BY title {$limitFilter}";
}
}
}
$result = Database::query($sql);
$courses = array();
while ($row = Database::fetch_array($result)) {
$row['registration_code'] = !empty($row['registration_code']);
$count_users = CourseManager::get_users_count_in_course($row['code']);
$count_connections_last_month = Tracking::get_course_connections_count($row['id'], 0, api_get_utc_datetime(time() - 30 * 86400));
if ($row['tutor_name'] == '0') {
$row['tutor_name'] = get_lang('NoManager');
}
$point_info = CourseManager::get_course_ranking($row['id'], 0);
$courses[] = array('real_id' => $row['id'], 'point_info' => $point_info, 'code' => $row['code'], 'directory' => $row['directory'], 'visual_code' => $row['visual_code'], 'title' => $row['title'], 'tutor' => $row['tutor_name'], 'subscribe' => $row['subscribe'], 'unsubscribe' => $row['unsubscribe'], 'registration_code' => $row['registration_code'], 'creation_date' => $row['creation_date'], 'visibility' => $row['visibility'], 'count_users' => $count_users, 'count_connections' => $count_connections_last_month);
}
return $courses;
}
/**
* CSV file import functions
* @author René Haentjens , Ghent University
*/
public static function put_link($url, $cat, $title, $description, $on_homepage, $hidden)
{
$tbl_link = Database::get_course_table(TABLE_LINK);
$course_id = api_get_course_int_id();
$urleq = "url='" . Database::escape_string($url) . "'";
$cateq = "category_id=" . intval($cat);
$result = Database::query("SELECT id FROM {$tbl_link}\n WHERE c_id = {$course_id} AND " . $urleq . ' AND ' . $cateq);
if (Database::num_rows($result) >= 1 && ($row = Database::fetch_array($result))) {
Database::query("UPDATE {$tbl_link} set title='" . Database::escape_string($title) . "', description='" . Database::escape_string($description) . "'\n WHERE c_id = {$course_id} AND id='" . Database::escape_string($row['id']) . "'");
$ipu = 'LinkUpdated';
$rv = 1;
// 1 = upd
} else {
// Add new link
$result = Database::query("SELECT MAX(display_order) FROM {$tbl_link}\n WHERE c_id = {$course_id} AND category_id='" . intval($cat) . "'");
list($max_order) = Database::fetch_row($result);
Database::query("INSERT INTO {$tbl_link} (c_id, url, title, description, category_id, display_order, on_homepage)\n VALUES (" . api_get_course_int_id() . ",\n '" . Database::escape_string($url) . "',\n '" . Database::escape_string($title) . "',\n '" . Database::escape_string($description) . "',\n '" . intval($cat) . "','" . (intval($max_order) + 1) . "',\n '" . intval($on_homepage) . "')");
$id = Database::insert_id();
$ipu = 'LinkAdded';
$rv = 2;
// 2 = new
}
global $_course, $nameTools, $_user;
api_item_property_update($_course, TOOL_LINK, $id, $ipu, $_user['user_id']);
if ($hidden && $ipu == 'LinkAdded') {
api_item_property_update($_course, TOOL_LINK, $id, 'invisible', $_user['user_id']);
}
return $rv;
}
$sql = 'SELECT start_date, exe_date, exe_result, exe_weighting, exe_exo_id
FROM ' . $TBL_TRACK_EXERCICES . '
WHERE exe_id = ' . $safe_exe_id;
$res = Database::query($sql);
$row_dates = Database::fetch_array($res);
$time_start_date = api_strtotime($row_dates['start_date'], 'UTC');
$time_exe_date = api_strtotime($row_dates['exe_date'], 'UTC');
$mytime = (int) $time_exe_date - (int) $time_start_date;
$score = (double) $row_dates['exe_result'];
$max_score = (double) $row_dates['exe_weighting'];
$sql = "UPDATE {$TBL_LP_ITEM} SET\n max_score = '{$max_score}'\n WHERE c_id = {$course_id} AND id = '" . $safe_item_id . "'";
Database::query($sql);
$sql = "SELECT id FROM {$TBL_LP_ITEM_VIEW}\n WHERE\n c_id = {$course_id} AND\n lp_item_id = '{$safe_item_id}' AND\n lp_view_id = '" . $learnPath->lp_view_id . "'\n ORDER BY id DESC\n LIMIT 1";
$res_last_attempt = Database::query($sql);
if (Database::num_rows($res_last_attempt) && !api_is_invitee()) {
$row_last_attempt = Database::fetch_row($res_last_attempt);
$lp_item_view_id = $row_last_attempt[0];
$exercise = new Exercise(api_get_course_int_id());
$exercise->read($row_dates['exe_exo_id']);
$status = 'completed';
if (!empty($exercise->pass_percentage)) {
$status = 'failed';
$success = ExerciseLib::is_success_exercise_result($score, $max_score, $exercise->pass_percentage);
if ($success) {
$status = 'passed';
}
}
$sql = "UPDATE {$TBL_LP_ITEM_VIEW} SET\n status = '{$status}',\n score = {$score},\n total_time = {$mytime}\n WHERE id='" . $lp_item_view_id . "' AND c_id = {$course_id} ";
if ($debug) {
error_log($sql);
}
$extraFieldInfo = $extraField->get_handler_field_info_by_field_variable($field_variable);
if ($extraFieldInfo['visible'] != 1) {
continue;
}
if (is_array($data)) {
$extra_information_value .= '<dt>' . ucfirst($extraFieldInfo['display_text']) . '</dt>' . '<dd> ' . implode(',', $data) . '</dd>';
} else {
switch ($extraFieldInfo['field_type']) {
case ExtraField::FIELD_TYPE_DOUBLE_SELECT:
$id_options = explode(';', $data);
$value_options = array();
// get option display text from user_field_options table
foreach ($id_options as $id_option) {
$sql = "SELECT display_text FROM {$t_ufo} WHERE id = '{$id_option}'";
$res_options = Database::query($sql);
$row_options = Database::fetch_row($res_options);
$value_options[] = $row_options[0];
}
$extra_information_value .= '<dt>' . ucfirst($extraFieldInfo['display_text']) . ':</dt>' . '<dd>' . implode(' ', $value_options) . '</dd>';
break;
case ExtraField::FIELD_TYPE_TAG:
$user_tags = UserManager::get_user_tags($user_id, $extraFieldInfo['id']);
$tag_tmp = array();
foreach ($user_tags as $tags) {
$tag_tmp[] = '<a class="label label_tag"' . ' href="' . api_get_path(WEB_PATH) . 'main/social/search.php?q=' . $tags['tag'] . '">' . $tags['tag'] . '</a>';
}
if (is_array($user_tags) && count($user_tags) > 0) {
$extra_information_value .= '<dt>' . ucfirst($extraFieldInfo['display_text']) . ':</dt>' . '<dd>' . implode('', $tag_tmp) . '</dd>';
}
break;
case ExtraField::FIELD_TYPE_SOCIAL_PROFILE:
/**
* Check if this still links to a learnpath
*/
public function is_valid_link()
{
$sql = 'SELECT count(id) FROM ' . $this->get_learnpath_table() . '
WHERE c_id = ' . $this->course_id . ' AND id = ' . $this->get_ref_id() . ' ';
$result = Database::query($sql);
$number = Database::fetch_row($result, 'NUM');
return $number[0] != 0;
}
function WSUnsuscribeCoursesFromSession($params)
{
if (!WSHelperVerifyKey($params)) {
return return_error(WS_ERROR_SECRET_KEY);
}
// 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_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
$tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
$coursessessions_params = $params['coursessessions'];
$results = array();
$orig_course_id_value = array();
$orig_session_id_value = array();
foreach ($coursessessions_params as $coursesession_param) {
$original_session_id_value = $coursesession_param['original_session_id_value'];
$original_session_id_name = $coursesession_param['original_session_id_name'];
$original_course_id_name = $coursesession_param['original_course_id_name'];
$original_course_id_values = $coursesession_param['original_course_id_values'];
$orig_session_id_value[] = $original_session_id_value;
$id_session = SessionManager::getSessionIdFromOriginalId($original_session_id_value, $original_session_id_name);
if (empty($id_session)) {
$results[] = 0;
continue;
}
// Get courses list from row_original_course_id_values
$course_list = array();
$courseIdList = [];
foreach ($original_course_id_values as $row_original_course_list) {
$course_code = Database::escape_string($row_original_course_list['course_code']);
// Check whether exits $x_course_code into user_field_values table.
$courseInfo = CourseManager::getCourseInfoFromOriginalId($row_original_course_list['course_code'], $original_course_id_name);
if (empty($courseInfo) || isset($courseInfo) && $courseInfo['visibility'] == 0) {
continue;
// Course_code doesn't exist'
}
$course_list[] = $courseInfo['code'];
$courseIdList[] = $courseInfo['real_id'];
}
if (empty($course_list)) {
$results[] = 0;
continue;
}
$orig_course_id_value[] = implode(',', $course_list);
foreach ($courseIdList as $courseId) {
$courseId = intval($courseId);
Database::query("DELETE FROM {$tbl_session_rel_course}\n WHERE c_id ='{$courseId}' AND session_id='{$id_session}'");
$result = Database::query("DELETE FROM {$tbl_session_rel_course_rel_user} WHERE c_id='{$courseId}' AND session_id = '{$id_session}'");
Event::addEvent(LOG_SESSION_DELETE_COURSE, LOG_COURSE_ID, $courseId, api_get_utc_datetime(), api_get_user_id(), $courseId, $id_session);
$return = Database::affected_rows($result);
}
$nbr_courses = 0;
$sql = "SELECT nbr_courses FROM {$tbl_session} WHERE id = '{$id_session}'";
$res_nbr_courses = Database::query($sql);
$row_nbr_courses = Database::fetch_row($res_nbr_courses);
if (Database::num_rows($res_nbr_courses) > 0) {
$nbr_users = $row_nbr_courses[0] - $return;
}
// Update number of users in the session.
$update_sql = "UPDATE {$tbl_session} SET nbr_courses= {$nbr_courses} WHERE id='{$id_session}' ";
Database::query($update_sql);
$results[] = 1;
continue;
}
$count_results = count($results);
$output = array();
for ($i = 0; $i < $count_results; $i++) {
$output[] = array('original_course_id_values' => $orig_course_id_value[$i], 'original_session_id_value' => $orig_session_id_value[$i], 'result' => $results[$i]);
}
return $output;
}
api_block_anonymous_users();
GradebookUtils::block_students();
$interbreadcrumb[] = array('url' => $_SESSION['gradebook_dest'] . '?', 'name' => get_lang('Gradebook'));
$interbreadcrumb[] = array('url' => $_SESSION['gradebook_dest'] . '?selectcat=' . Security::remove_XSS($_GET['selectcat']), 'name' => get_lang('Details'));
$interbreadcrumb[] = array('url' => 'gradebook_showlog_eval.php?visiblelog=' . Security::remove_XSS($_GET['visiblelog']) . '&selectcat=' . Security::remove_XSS($_GET['selectcat']), 'name' => get_lang('GradebookQualifyLog'));
$this_section = SECTION_COURSES;
Display::display_header('');
echo Display::page_header(get_lang('GradebookQualifyLog'));
$t_linkeval_log = Database::get_main_table(TABLE_MAIN_GRADEBOOK_LINKEVAL_LOG);
$t_user = Database::get_main_table(TABLE_MAIN_USER);
$visible_log = Security::remove_XSS($_GET['visiblelog']);
$evaledit = Evaluation::load($visible_log);
$sql = "SELECT le.name,le.description,le.weight,le.visible,le.type,le.created_at,us.username FROM " . $t_linkeval_log . " le INNER JOIN " . $t_user . " us\n ON le.user_id_log=us.user_id where id_linkeval_log=" . $evaledit[0]->get_id() . " and type='evaluation';";
$result = Database::query($sql);
$list_info = array();
while ($row = Database::fetch_row($result)) {
$list_info[] = $row;
}
foreach ($list_info as $key => $info_log) {
$list_info[$key][5] = $info_log[5] ? api_convert_and_format_date($info_log[5]) : 'N/A';
$list_info[$key][3] = $info_log[3] == 1 ? get_lang('GradebookVisible') : get_lang('GradebookInvisible');
}
$parameters = array('visiblelog' => $visible_log, 'selectcat' => intval($_GET['selectcat']));
$table = new SortableTableFromArrayConfig($list_info, 1, 20, 'gradebookeval');
$table->set_additional_parameters($parameters);
$table->set_header(0, get_lang('GradebookNameLog'));
$table->set_header(1, get_lang('GradebookDescriptionLog'));
$table->set_header(2, get_lang('GradebookPreviousWeight'));
$table->set_header(3, get_lang('GradebookVisibilityLog'));
$table->set_header(4, get_lang('ResourceType'));
$table->set_header(5, get_lang('Date'));
/**
* Gets the IP of a given user, using the last login before the given date
* @param int User ID
* @param string Datetime
* @param bool Whether to return the IP as a link or just as an IP
* @param string If defined and return_as_link if true, will be used as the text to be shown as the link
* @return string IP address (or false on error)
* @assert (0,0) === false
*/
public static function get_ip_from_user_event($user_id, $event_date, $return_as_link = false, $body_replace = null)
{
if (empty($user_id) or empty($event_date)) {
return false;
}
$user_id = intval($user_id);
$event_date = Database::escape_string($event_date);
$table_login = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LOGIN);
$sql_ip = "SELECT login_date, user_ip FROM {$table_login}\n WHERE login_user_id = {$user_id} AND login_date < '{$event_date}'\n ORDER BY login_date DESC LIMIT 1";
$ip = '';
$res_ip = Database::query($sql_ip);
if ($res_ip !== false && Database::num_rows($res_ip) > 0) {
$row_ip = Database::fetch_row($res_ip);
if ($return_as_link) {
$ip = Display::url(empty($body_replace) ? $row_ip[1] : $body_replace, 'http://www.whatsmyip.org/ip-geo-location/?ip=' . $row_ip[1], array('title' => get_lang('TraceIP'), 'target' => '_blank'));
} else {
$ip = $row_ip[1];
}
}
return $ip;
}
$sortDirection = "DESC";
}
if (!empty($_GET['up'])) {
$thisAnnouncementId = intval($_GET['up']);
$sortDirection = "ASC";
}
if (!empty($sortDirection)) {
if (!in_array(trim(strtoupper($sortDirection)), array('ASC', 'DESC'))) {
$sortDirection = 'ASC';
}
$announcementInfo = AnnouncementManager::get_by_id($course_id, $thisAnnouncementId);
$sql = "SELECT DISTINCT announcement.id, announcement.display_order\n FROM {$tbl_announcement} announcement,\n\t\t\t\t{$tbl_item_property} itemproperty\n\t\t\t\tWHERE\n\t\t\t\t announcement.c_id = {$course_id} AND\n\t\t\t\t itemproperty.c_id = {$course_id} AND\n\t\t\t\t\titemproperty.ref = announcement.id AND\n itemproperty.tool = '" . TOOL_ANNOUNCEMENT . "' AND\n itemproperty.visibility <> 2\n ORDER BY display_order {$sortDirection}";
$result = Database::query($sql);
$thisAnnouncementOrderFound = false;
$thisAnnouncementOrder = null;
while (list($announcementId, $announcementOrder) = Database::fetch_row($result)) {
if ($thisAnnouncementOrderFound) {
$nextAnnouncementId = $announcementId;
$nextAnnouncementOrder = $announcementOrder;
$sql = "UPDATE {$tbl_announcement} SET display_order = '{$nextAnnouncementOrder}'\n WHERE c_id = {$course_id} AND id = {$thisAnnouncementId}";
Database::query($sql);
$sql = "UPDATE {$tbl_announcement} SET display_order = '{$thisAnnouncementOrder}'\n WHERE c_id = {$course_id} AND id = {$nextAnnouncementId}";
Database::query($sql);
break;
}
// STEP 1 : FIND THE ORDER OF THE ANNOUNCEMENT
if ($announcementId == $thisAnnouncementId) {
$thisAnnouncementOrder = $announcementOrder;
$thisAnnouncementOrderFound = true;
}
}
/**
* Blog admin | Returns table with blogs in this course
*/
public static function display_blog_list()
{
global $charset;
$_user = api_get_user_info();
$course_id = api_get_course_int_id();
// Init
$counter = 0;
$tbl_blogs = Database::get_course_table(TABLE_BLOGS);
//condition for the session
$session_id = api_get_session_id();
$condition_session = api_get_session_condition($session_id, false);
$sql = "SELECT blog_name, blog_subtitle, visibility, blog_id, session_id\n\t\t\t\tFROM {$tbl_blogs} WHERE c_id = {$course_id}\n\t\t\t\tORDER BY date_creation DESC";
$result = Database::query($sql);
$list_info = array();
if (Database::num_rows($result)) {
while ($row_project = Database::fetch_row($result)) {
$list_info[] = $row_project;
}
}
$list_content_blog = array();
$list_body_blog = array();
$_user = api_get_user_info();
if (is_array($list_info)) {
foreach ($list_info as $key => $info_log) {
// Validation when belongs to a session
$session_img = api_get_session_image($info_log[4], $_user['status']);
$url_start_blog = 'blog.php' . "?" . "blog_id=" . $info_log[3] . "&" . api_get_cidreq();
$title = $info_log[0];
$image = '<img src="../img/blog.gif" border="0" align="absmiddle" alt="' . $title . '">';
$list_name = '<div style="float: left; width: 35px; height: 22px;"><a href="' . $url_start_blog . '">' . $image . '</a></div><a href="' . $url_start_blog . '">' . $title . '</a>' . $session_img;
$list_body_blog[] = $list_name;
$list_body_blog[] = $info_log[1];
$visibility_icon = $info_log[2] == 0 ? 'invisible' : 'visible';
$visibility_info = $info_log[2] == 0 ? 'Visible' : 'Invisible';
$my_image = '<a href="' . api_get_self() . '?action=edit&blog_id=' . $info_log[3] . '">';
$my_image .= '<img src="../img/edit.gif" border="0" title="' . get_lang('EditBlog') . '" />';
$my_image .= "</a>\n";
$my_image .= '<a href="' . api_get_self() . '?action=delete&blog_id=' . $info_log[3] . '" ';
$my_image .= 'onclick="javascript:if(!confirm(\'' . addslashes(api_htmlentities(get_lang("ConfirmYourChoice"), ENT_QUOTES, $charset)) . '\')) return false;" >';
$my_image .= '<img src="../img/delete.gif" border="0" title="' . get_lang('DeleteBlog') . '" />';
$my_image .= "</a>\n";
$my_image .= '<a href="' . api_get_self() . '?action=visibility&blog_id=' . $info_log[3] . '">';
$my_image .= '<img src="../img/' . $visibility_icon . '.gif" border="0" title="' . get_lang($visibility_info) . '" />';
$my_image .= "</a>\n";
$list_body_blog[] = $my_image;
$list_content_blog[] = $list_body_blog;
$list_body_blog = array();
}
$parameters = '';
//$parameters=array('action'=>Security::remove_XSS($_GET['action']));
$table = new SortableTableFromArrayConfig($list_content_blog, 1, 20, 'project');
//$table->set_additional_parameters($parameters);
$table->set_header(0, get_lang('Title'));
$table->set_header(1, get_lang('SubTitle'));
$table->set_header(2, get_lang('Modify'));
$table->display();
}
}
/**
* Get the number of sessions
* @param int ID of the URL we want to filter on (optional)
* @return int Number of sessions
*/
public static function count_sessions($access_url_id = null)
{
$session_table = Database::get_main_table(TABLE_MAIN_SESSION);
$access_url_rel_session_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
$sql = "SELECT count(id) FROM {$session_table} s";
if (!empty($access_url_id) && $access_url_id == intval($access_url_id)) {
$sql .= ", {$access_url_rel_session_table} u " . " WHERE s.id = u.session_id AND u.access_url_id = {$access_url_id}";
}
$res = Database::query($sql);
$row = Database::fetch_row($res);
return $row[0];
}
/**
* Gets information about messages sent
* @param integer
* @param integer
* @param string
* @return array
*/
public static function get_message_data_sent($from, $number_of_items, $column, $direction)
{
$from = intval($from);
$number_of_items = intval($number_of_items);
if (!isset($direction)) {
$column = 3;
$direction = 'DESC';
} else {
$column = intval($column);
if (!in_array($direction, array('ASC', 'DESC'))) {
$direction = 'ASC';
}
}
$table_message = Database::get_main_table(TABLE_MESSAGE);
$request = api_is_xml_http_request();
$sql = "SELECT\n id as col0, user_sender_id as col1, title as col2, send_date as col3, user_receiver_id as col4, msg_status as col5\n FROM {$table_message}\n WHERE\n user_sender_id=" . api_get_user_id() . " AND\n msg_status=" . MESSAGE_STATUS_OUTBOX . "\n ORDER BY col{$column} {$direction}\n LIMIT {$from}, {$number_of_items}";
$sql_result = Database::query($sql);
$i = 0;
$message_list = array();
while ($result = Database::fetch_row($sql_result)) {
if ($request === true) {
$message[0] = '<input type="checkbox" value=' . $result[0] . ' name="out[]">';
} else {
$message[0] = $result[0];
}
$class = 'class = "read"';
$result[2] = Security::remove_XSS($result[2]);
if ($request === true) {
$userInfo = api_get_user_info($result[4]);
$message[1] = '<a onclick="show_sent_message(' . $result[0] . ')" href="javascript:void(0)">' . $userInfo['complete_name'] . '</a>';
$message[2] = '<a onclick="show_sent_message(' . $result[0] . ')" href="javascript:void(0)">' . str_replace("\\", "", $result[2]) . '</a>';
$message[3] = api_convert_and_format_date($result[3], DATE_TIME_FORMAT_LONG);
//date stays the same
$message[4] = ' <a onclick="delete_one_message_outbox(' . $result[0] . ')" href="javascript:void(0)" >' . Display::return_icon('delete.png', get_lang('DeleteMessage')) . '</a>';
} else {
$link = '';
if (isset($_GET['f']) && $_GET['f'] == 'social') {
$link = '&f=social';
}
$userInfo = api_get_user_info($result[4]);
$message[1] = '<a ' . $class . ' onclick="show_sent_message (' . $result[0] . ')" href="../messages/view_message.php?id_send=' . $result[0] . $link . '">' . $result[2] . '</a><br />' . $userInfo['complete_name'];
//$message[2] = '<a '.$class.' onclick="show_sent_message ('.$result[0].')" href="../messages/view_message.php?id_send='.$result[0].$link.'">'.$result[2].'</a>';
$message[2] = api_convert_and_format_date($result[3], DATE_TIME_FORMAT_LONG);
//date stays the same
$message[3] = '<a href="outbox.php?action=deleteone&id=' . $result[0] . '&' . $link . '" onclick="javascript:if(!confirm(' . "'" . addslashes(api_htmlentities(get_lang('ConfirmDeleteMessage'))) . "'" . ')) return false;" >' . Display::return_icon('delete.png', get_lang('DeleteMessage')) . '</a>';
}
foreach ($message as $key => $value) {
$message[$key] = $value;
}
$message_list[] = $message;
$i++;
}
return $message_list;
}
/**
* Calculates the total size of all documents in a course
*
* @author Bert vanderkimpen
* @param int $course_id
* @param int $group_id (to calculate group document space)
* @param int $session_id
*
* @return int total size
*/
static function documents_total_space($course_id = null, $group_id = null, $session_id = null)
{
$TABLE_ITEMPROPERTY = Database::get_course_table(TABLE_ITEM_PROPERTY);
$TABLE_DOCUMENT = Database::get_course_table(TABLE_DOCUMENT);
if (isset($course_id)) {
$course_id = intval($course_id);
} else {
$course_id = api_get_course_int_id();
}
$group_condition = null;
if (isset($group_id)) {
$group_id = intval($group_id);
$group_condition = " AND props.to_group_id='" . $group_id . "' ";
}
$session_condition = null;
if (isset($session_id)) {
$session_id = intval($session_id);
$session_condition = " AND props.session_id='" . $session_id . "' ";
}
$sql = "SELECT SUM(size)\n FROM {$TABLE_ITEMPROPERTY} AS props\n INNER JOIN {$TABLE_DOCUMENT} AS docs\n ON (docs.id = props.ref AND props.c_id = docs.c_id)\n WHERE\n props.c_id \t= {$course_id} AND\n docs.c_id \t= {$course_id} AND\n props.tool \t= '" . TOOL_DOCUMENT . "' AND\n props.visibility <> 2\n {$group_condition}\n {$session_condition}\n ";
$result = Database::query($sql);
if ($result && Database::num_rows($result) != 0) {
$row = Database::fetch_row($result);
return $row[0];
} else {
return 0;
}
}
/**
* Gets a link to the resource from the present location, depending on item ID.
* @param string Type of link expected
* @param integer Learnpath item ID
* @return string Link to the lp_item resource
*/
public function get_link($type = 'http', $item_id = null, $provided_toc = false)
{
$course_id = $this->get_course_int_id();
if ($this->debug > 0) {
error_log('New LP - In learnpath::get_link(' . $type . ',' . $item_id . ')', 0);
}
if (empty($item_id)) {
if ($this->debug > 2) {
error_log('New LP - In learnpath::get_link() - no item id given in learnpath::get_link(), using current: ' . $this->get_current_item_id(), 0);
}
$item_id = $this->get_current_item_id();
}
if (empty($item_id)) {
if ($this->debug > 2) {
error_log('New LP - In learnpath::get_link() - no current item id found in learnpath object', 0);
}
//still empty, this means there was no item_id given and we are not in an object context or
//the object property is empty, return empty link
$item_id = $this->first();
return '';
}
$file = '';
$lp_table = Database::get_course_table(TABLE_LP_MAIN);
$lp_item_table = Database::get_course_table(TABLE_LP_ITEM);
$lp_item_view_table = Database::get_course_table(TABLE_LP_ITEM_VIEW);
$item_id = Database::escape_string($item_id);
$sql = "SELECT l.lp_type as ltype, l.path as lpath, li.item_type as litype, li.path as lipath, li.parameters as liparams\n \t\tFROM {$lp_table} l\n INNER JOIN {$lp_item_table} li\n ON (li.lp_id = l.id AND l.c_id = {$course_id} AND li.c_id = {$course_id} )\n \t\tWHERE li.id = {$item_id} ";
if ($this->debug > 2) {
error_log('New LP - In learnpath::get_link() - selecting item ' . $sql, 0);
}
$res = Database::query($sql);
if (Database::num_rows($res) > 0) {
$row = Database::fetch_array($res);
$lp_type = $row['ltype'];
$lp_path = $row['lpath'];
$lp_item_type = $row['litype'];
$lp_item_path = $row['lipath'];
$lp_item_params = $row['liparams'];
if (empty($lp_item_params) && strpos($lp_item_path, '?') !== false) {
list($lp_item_path, $lp_item_params) = explode('?', $lp_item_path);
}
$sys_course_path = api_get_path(SYS_COURSE_PATH) . api_get_course_path();
if ($type == 'http') {
$course_path = api_get_path(WEB_COURSE_PATH) . api_get_course_path();
//web path
} else {
$course_path = $sys_course_path;
//system path
}
// Fixed issue BT#1272 - If the item type is a Chamilo Item (quiz, link, etc), then change the lp type to thread it as a normal Chamilo LP not a SCO.
if (in_array($lp_item_type, array('quiz', 'document', 'link', 'forum', 'thread', 'student_publication'))) {
$lp_type = 1;
}
if ($this->debug > 2) {
error_log('New LP - In learnpath::get_link() - $lp_type ' . $lp_type, 0);
error_log('New LP - In learnpath::get_link() - $lp_item_type ' . $lp_item_type, 0);
}
// Now go through the specific cases to get the end of the path
// @todo Use constants instead of int values.
switch ($lp_type) {
case 1:
if ($lp_item_type == 'dokeos_chapter') {
$file = 'lp_content.php?type=dir';
} else {
require_once 'resourcelinker.inc.php';
$file = rl_get_resource_link_for_learnpath($course_id, $this->get_id(), $item_id);
if ($this->debug > 0) {
error_log('rl_get_resource_link_for_learnpath - file: ' . $file, 0);
}
if ($lp_item_type == 'link') {
require_once api_get_path(LIBRARY_PATH) . 'link.lib.php';
if (is_youtube_link($file)) {
$src = get_youtube_video_id($file);
$file = 'embed.php?type=youtube&src=' . $src;
}
if (isVimeoLink($file)) {
$src = getVimeoLinkId($file);
$file = 'embed.php?type=vimeo&src=' . $src;
}
} else {
// check how much attempts of a exercise exits in lp
$lp_item_id = $this->get_current_item_id();
$lp_view_id = $this->get_view_id();
$prevent_reinit = null;
if (isset($this->items[$this->current])) {
$prevent_reinit = $this->items[$this->current]->get_prevent_reinit();
}
if (empty($provided_toc)) {
if ($this->debug > 0) {
error_log('In learnpath::get_link() Loading get_toc ', 0);
}
$list = $this->get_toc();
} else {
if ($this->debug > 0) {
error_log('In learnpath::get_link() Loading get_toc from "cache" ', 0);
}
$list = $provided_toc;
}
$type_quiz = false;
foreach ($list as $toc) {
if ($toc['id'] == $lp_item_id && $toc['type'] == 'quiz') {
$type_quiz = true;
}
}
if ($type_quiz) {
$lp_item_id = Database::escape_string($lp_item_id);
$lp_view_id = Database::escape_string($lp_view_id);
$sql = "SELECT count(*) FROM {$lp_item_view_table}\n WHERE c_id = {$course_id} AND lp_item_id='" . (int) $lp_item_id . "' AND lp_view_id ='" . (int) $lp_view_id . "' AND status='completed'";
$result = Database::query($sql);
$row_count = Database::fetch_row($result);
$count_item_view = (int) $row_count[0];
$not_multiple_attempt = 0;
if ($prevent_reinit === 1 && $count_item_view > 0) {
$not_multiple_attempt = 1;
}
$file .= '¬_multiple_attempt=' . $not_multiple_attempt;
}
$tmp_array = explode('/', $file);
$document_name = $tmp_array[count($tmp_array) - 1];
if (strpos($document_name, '_DELETED_')) {
$file = 'blank.php?error=document_deleted';
}
}
}
break;
case 2:
if ($this->debug > 2) {
error_log('New LP - In learnpath::get_link() ' . __LINE__ . ' - Item type: ' . $lp_item_type, 0);
}
if ($lp_item_type != 'dir') {
// Quite complex here:
// We want to make sure 'http://' (and similar) links can
// be loaded as is (withouth the Chamilo path in front) but
// some contents use this form: resource.htm?resource=http://blablabla
// which means we have to find a protocol at the path's start, otherwise
// it should not be considered as an external URL.
//if ($this->prerequisites_match($item_id)) {
if (preg_match('#^[a-zA-Z]{2,5}://#', $lp_item_path) != 0) {
if ($this->debug > 2) {
error_log('New LP - In learnpath::get_link() ' . __LINE__ . ' - Found match for protocol in ' . $lp_item_path, 0);
}
// Distant url, return as is.
$file = $lp_item_path;
} else {
if ($this->debug > 2) {
error_log('New LP - In learnpath::get_link() ' . __LINE__ . ' - No starting protocol in ' . $lp_item_path, 0);
}
// Prevent getting untranslatable urls.
$lp_item_path = preg_replace('/%2F/', '/', $lp_item_path);
$lp_item_path = preg_replace('/%3A/', ':', $lp_item_path);
// Prepare the path.
$file = $course_path . '/scorm/' . $lp_path . '/' . $lp_item_path;
// TODO: Fix this for urls with protocol header.
$file = str_replace('//', '/', $file);
$file = str_replace(':/', '://', $file);
if (substr($lp_path, -1) == '/') {
$lp_path = substr($lp_path, 0, -1);
}
if (!is_file(realpath($sys_course_path . '/scorm/' . $lp_path . '/' . $lp_item_path))) {
// if file not found.
$decoded = html_entity_decode($lp_item_path);
list($decoded) = explode('?', $decoded);
if (!is_file(realpath($sys_course_path . '/scorm/' . $lp_path . '/' . $decoded))) {
require_once 'resourcelinker.inc.php';
$file = rl_get_resource_link_for_learnpath($course_id, $this->get_id(), $item_id);
if (empty($file)) {
$file = 'blank.php?error=document_not_found';
} else {
$tmp_array = explode('/', $file);
$document_name = $tmp_array[count($tmp_array) - 1];
if (strpos($document_name, '_DELETED_')) {
$file = 'blank.php?error=document_deleted';
} else {
$file = 'blank.php?error=document_not_found';
}
}
} else {
$file = $course_path . '/scorm/' . $lp_path . '/' . $decoded;
}
}
}
//}else{
//prerequisites did not match
//$file = 'blank.php';
//}
// We want to use parameters if they were defined in the imsmanifest
if (strpos($file, 'blank.php') === false) {
$file .= (strstr($file, '?') === false ? '?' : '') . $lp_item_params;
}
} else {
$file = 'lp_content.php?type=dir';
}
break;
case 3:
if ($this->debug > 2) {
error_log('New LP - In learnpath::get_link() ' . __LINE__ . ' - Item type: ' . $lp_item_type, 0);
}
// Formatting AICC HACP append URL.
$aicc_append = '?aicc_sid=' . urlencode(session_id()) . '&aicc_url=' . urlencode(api_get_path(WEB_CODE_PATH) . 'newscorm/aicc_hacp.php') . '&';
if ($lp_item_type != 'dir') {
// Quite complex here:
// We want to make sure 'http://' (and similar) links can
// be loaded as is (withouth the Chamilo path in front) but
// some contents use this form: resource.htm?resource=http://blablabla
// which means we have to find a protocol at the path's start, otherwise
// it should not be considered as an external URL.
if (preg_match('#^[a-zA-Z]{2,5}://#', $lp_item_path) != 0) {
if ($this->debug > 2) {
error_log('New LP - In learnpath::get_link() ' . __LINE__ . ' - Found match for protocol in ' . $lp_item_path, 0);
}
// Distant url, return as is.
$file = $lp_item_path;
// Enabled and modified by Ivan Tcholakov, 16-OCT-2008.
/*
if (stristr($file,'<servername>') !== false) {
$file = str_replace('<servername>', $course_path.'/scorm/'.$lp_path.'/', $lp_item_path);
}
*/
if (stripos($file, '<servername>') !== false) {
//$file = str_replace('<servername>',$course_path.'/scorm/'.$lp_path.'/',$lp_item_path);
$web_course_path = str_replace('https://', '', str_replace('http://', '', $course_path));
$file = str_replace('<servername>', $web_course_path . '/scorm/' . $lp_path, $lp_item_path);
}
//
$file .= $aicc_append;
} else {
if ($this->debug > 2) {
error_log('New LP - In learnpath::get_link() ' . __LINE__ . ' - No starting protocol in ' . $lp_item_path, 0);
}
// Prevent getting untranslatable urls.
$lp_item_path = preg_replace('/%2F/', '/', $lp_item_path);
$lp_item_path = preg_replace('/%3A/', ':', $lp_item_path);
// Prepare the path - lp_path might be unusable because it includes the "aicc" subdir name.
$file = $course_path . '/scorm/' . $lp_path . '/' . $lp_item_path;
// TODO: Fix this for urls with protocol header.
$file = str_replace('//', '/', $file);
$file = str_replace(':/', '://', $file);
$file .= $aicc_append;
}
} else {
$file = 'lp_content.php?type=dir';
}
break;
case 4:
break;
default:
break;
}
}
if ($this->debug > 2) {
error_log('New LP - In learnpath::get_link() - returning "' . $file . '" from get_link', 0);
}
return $file;
}
/**
* Get activities data to display
* @param int $from
* @param int $numberOfItems
* @param int $column
* @param string $direction
* @return array
*/
public static function getActivitiesData($from, $numberOfItems, $column, $direction)
{
$track_e_default = Database::get_main_table(TABLE_STATISTIC_TRACK_E_DEFAULT);
$table_user = Database::get_main_table(TABLE_MAIN_USER);
$access_url_rel_user_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
$current_url_id = api_get_current_access_url_id();
$column = intval($column);
$from = intval($from);
$numberOfItems = intval($numberOfItems);
if (!in_array($direction, array('ASC', 'DESC'))) {
$direction = 'DESC';
}
if (api_is_multiple_url_enabled()) {
$sql = "SELECT\n default_event_type as col0,\n default_value_type as col1,\n default_value as col2,\n c_id as col3,\n session_id as col4,\n user.username as col5,\n user.user_id as col6,\n default_date as col7\n FROM {$track_e_default} as track_default, {$table_user} as user, {$access_url_rel_user_table} as url\n WHERE\n track_default.default_user_id = user.user_id AND\n url.user_id = user.user_id AND\n access_url_id='" . $current_url_id . "'";
} else {
$sql = "SELECT\n default_event_type as col0,\n default_value_type as col1,\n default_value as col2,\n c_id as col3,\n session_id as col4,\n user.username as col5,\n user.user_id as col6,\n default_date as col7\n FROM {$track_e_default} track_default, {$table_user} user\n WHERE track_default.default_user_id = user.user_id ";
}
if (isset($_GET['keyword'])) {
$keyword = Database::escape_string(trim($_GET['keyword']));
$sql .= " AND (user.username LIKE '%" . $keyword . "%' OR\n default_event_type LIKE '%" . $keyword . "%' OR\n default_value_type LIKE '%" . $keyword . "%' OR\n default_value LIKE '%" . $keyword . "%') ";
}
if (!empty($column) && !empty($direction)) {
$sql .= " ORDER BY col{$column} {$direction}";
} else {
$sql .= " ORDER BY col5 DESC ";
}
$sql .= " LIMIT {$from},{$numberOfItems} ";
$res = Database::query($sql);
$activities = array();
while ($row = Database::fetch_row($res)) {
if (strpos($row[1], '_object') === false && strpos($row[1], '_array') === false) {
$row[2] = $row[2];
} else {
if (!empty($row[2])) {
$originalData = str_replace('\\', '', $row[2]);
$row[2] = unserialize($originalData);
if (is_array($row[2]) && !empty($row[2])) {
$row[2] = implode_with_key(', ', $row[2]);
} else {
$row[2] = $originalData;
}
}
}
if (!empty($row['default_date']) && $row['default_date'] != '0000-00-00 00:00:00') {
$row['default_date'] = api_get_local_time($row['default_date']);
} else {
$row['default_date'] = '-';
}
if (!empty($row[5])) {
//course
if (!empty($row[3])) {
$row[3] = Display::url($row[3], api_get_path(WEB_CODE_PATH) . 'admin/course_edit.php?id=' . $row[3]);
} else {
$row[3] = '-';
}
// session
if (!empty($row[4])) {
$row[4] = Display::url($row[4], api_get_path(WEB_CODE_PATH) . 'session/resume_session.php?id_session=' . $row[4]);
} else {
$row[4] = '-';
}
// User id.
$row[5] = Display::url($row[5], api_get_path(WEB_CODE_PATH) . 'admin/user_information.php?user_id=' . $row[6], array('title' => get_lang('UserInfo')));
$row[6] = TrackingUserLog::get_ip_from_user_event($row[6], $row[7], true);
if (empty($row[6])) {
$row[6] = get_lang('Unknown');
}
}
$activities[] = $row;
}
return $activities;
}
function search_users($needle, $type)
{
global $tbl_user, $tbl_session_rel_user, $id_session;
$xajax_response = new XajaxResponse();
$return = '';
if (!empty($needle) && !empty($type)) {
//normal behaviour
if ($type == 'any_session' && $needle == 'false') {
$type = 'multiple';
$needle = '';
}
// xajax send utf8 datas... datas in db can be non-utf8 datas
$charset = api_get_system_encoding();
$needle = Database::escape_string($needle);
$needle = api_convert_encoding($needle, $charset, 'utf-8');
$order_clause = api_sort_by_first_name() ? ' ORDER BY firstname, lastname, username' : ' ORDER BY lastname, firstname, username';
$cond_user_id = '';
//Only for single & multiple
if (in_array($type, array('single', 'multiple'))) {
if (!empty($id_session)) {
$id_session = intval($id_session);
// check id_user from session_rel_user table
$sql = 'SELECT id_user FROM ' . $tbl_session_rel_user . '
WHERE id_session ="' . $id_session . '" AND relation_type<>' . SESSION_RELATION_TYPE_RRHH . ' ';
$res = Database::query($sql);
$user_ids = array();
if (Database::num_rows($res) > 0) {
while ($row = Database::fetch_row($res)) {
$user_ids[] = (int) $row[0];
}
}
if (count($user_ids) > 0) {
$cond_user_id = ' AND user.user_id NOT IN(' . implode(",", $user_ids) . ')';
}
}
}
switch ($type) {
case 'single':
// search users where username or firstname or lastname begins likes $needle
$sql = 'SELECT user.user_id, username, lastname, firstname, official_code
FROM ' . $tbl_user . ' user
WHERE (username LIKE "' . $needle . '%" OR firstname LIKE "' . $needle . '%"
OR lastname LIKE "' . $needle . '%") AND user.status<>6 AND user.status<>' . DRH . '' . $order_clause . ' LIMIT 11';
break;
case 'multiple':
$sql = 'SELECT user.user_id, username, lastname, firstname, official_code
FROM ' . $tbl_user . ' user
WHERE ' . (api_sort_by_first_name() ? 'firstname' : 'lastname') . ' LIKE "' . $needle . '%" AND user.status<>' . DRH . ' AND user.status<>6 ' . $cond_user_id . $order_clause;
break;
case 'any_session':
$sql = 'SELECT DISTINCT user.user_id, username, lastname, firstname, official_code
FROM ' . $tbl_user . ' user
LEFT OUTER JOIN ' . $tbl_session_rel_user . ' s ON (s.id_user = user.user_id)
WHERE s.id_user IS null AND user.status<>' . DRH . ' AND
user.status<>6 ' . $cond_user_id . $order_clause;
break;
}
if (api_is_multiple_url_enabled()) {
$tbl_user_rel_access_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
$access_url_id = api_get_current_access_url_id();
if ($access_url_id != -1) {
switch ($type) {
case 'single':
$sql = 'SELECT user.user_id, username, lastname, firstname, official_code
FROM ' . $tbl_user . ' user
INNER JOIN ' . $tbl_user_rel_access_url . ' url_user ON (url_user.user_id=user.user_id)
WHERE access_url_id = ' . $access_url_id . ' AND (username LIKE "' . $needle . '%"
OR firstname LIKE "' . $needle . '%"
OR lastname LIKE "' . $needle . '%") AND user.status<>6 AND user.status<>' . DRH . ' ' . $order_clause . ' LIMIT 11';
break;
case 'multiple':
$sql = 'SELECT user.user_id, username, lastname, firstname , official_code
FROM ' . $tbl_user . ' user
INNER JOIN ' . $tbl_user_rel_access_url . ' url_user ON (url_user.user_id=user.user_id)
WHERE access_url_id = ' . $access_url_id . ' AND
' . (api_sort_by_first_name() ? 'firstname' : 'lastname') . ' LIKE "' . $needle . '%" AND
user.status<>' . DRH . ' AND
user.status<>6 ' . $cond_user_id . $order_clause;
break;
case 'any_session':
$sql = 'SELECT DISTINCT user.user_id, username, lastname, firstname, official_code
FROM ' . $tbl_user . ' user
LEFT OUTER JOIN ' . $tbl_session_rel_user . ' s ON (s.id_user = user.user_id)
INNER JOIN ' . $tbl_user_rel_access_url . ' url_user ON (url_user.user_id=user.user_id)
WHERE
access_url_id = ' . $access_url_id . ' AND
s.id_user IS null AND
user.status<>' . DRH . ' AND
user.status<>6 ' . $cond_user_id . $order_clause;
break;
}
}
}
$rs = Database::query($sql);
$i = 0;
if ($type == 'single') {
while ($user = Database::fetch_array($rs)) {
$i++;
if ($i <= 10) {
$person_name = api_get_person_name($user['firstname'], $user['lastname']) . ' (' . $user['username'] . ') ' . $user['official_code'];
$return .= '<a href="javascript: void(0);" onclick="javascript: add_user_to_session(\'' . $user['user_id'] . '\',\'' . $person_name . ' ' . '\')">' . $person_name . ' </a><br />';
} else {
$return .= '...<br />';
}
}
$xajax_response->addAssign('ajax_list_users_single', 'innerHTML', api_utf8_encode($return));
} else {
global $nosessionUsersList;
$return .= '<select id="origin_users" name="nosessionUsersList[]" multiple="multiple" size="15" style="width:360px;">';
while ($user = Database::fetch_array($rs)) {
$person_name = api_get_person_name($user['firstname'], $user['lastname']) . ' (' . $user['username'] . ') ' . $user['official_code'];
$return .= '<option value="' . $user['user_id'] . '">' . $person_name . ' </option>';
}
$return .= '</select>';
$xajax_response->addAssign('ajax_list_users_multiple', 'innerHTML', api_utf8_encode($return));
}
}
return $xajax_response;
}
/**
* @author Sebastien Piraux <*****@*****.**>
* @param sql : a sql query (as a string)
* @return month_array
* @desc Return an assoc array. Keys are the days, values are
* the number of time this hours was found.
* key "total" return the sum of all number of time days
* appear
*/
public static function monthTab($sql)
{
$MonthsLong = api_get_months_long();
$month_array = array('total' => 0);
$res = Database::query($sql);
if ($res !== false) {
// init tab with all months
for ($i = 0; $i < 12; $i++) {
$month_array[$MonthsLong[$i]] = 0;
}
while ($row = Database::fetch_row($res)) {
$date_array = getdate($row[0]);
$month_array[$MonthsLong[$date_array['mon'] - 1]]++;
$month_array['total']++;
}
Database::free_result($res);
}
return $month_array;
}
/**
* Gets a link to the resource from the present location, depending on item ID.
* @param string Type of link expected
* @param integer Learnpath item ID
* @return string Link to the lp_item resource
*/
function get_link($type = 'http', $item_id = null)
{
if ($this->debug > 0) {
error_log('New LP - In learnpath::get_link(' . $type . ',' . $item_id . ')', 0);
}
if (empty($item_id)) {
if ($this->debug > 2) {
error_log('New LP - In learnpath::get_link() - no item id given in learnpath::get_link(), using current: ' . $this->get_current_item_id(), 0);
}
$item_id = $this->get_current_item_id();
}
if (empty($item_id)) {
if ($this->debug > 2) {
error_log('New LP - In learnpath::get_link() - no current item id found in learnpath object', 0);
}
//still empty, this means there was no item_id given and we are not in an object context or
//the object property is empty, return empty link
$item_id = $this->first();
return '';
}
$file = '';
$lp_table = Database::get_course_table(TABLE_LP_MAIN);
$lp_item_table = Database::get_course_table(TABLE_LP_ITEM);
$lp_item_view_table = Database::get_course_table(TABLE_LP_ITEM_VIEW);
$item_id = Database::escape_string($item_id);
$sel = "SELECT l.lp_type as ltype, l.path as lpath, li.item_type as litype, li.path as lipath, li.parameters as liparams " . "FROM {$lp_table} l, {$lp_item_table} li WHERE li.id = {$item_id} AND li.lp_id = l.id";
if ($this->debug > 2) {
error_log('New LP - In learnpath::get_link() - selecting item ' . $sel, 0);
}
$res = Database::query($sel);
if (Database::num_rows($res) > 0) {
$row = Database::fetch_array($res);
//var_dump($row);
$lp_type = $row['ltype'];
$lp_path = $row['lpath'];
$lp_item_type = $row['litype'];
$lp_item_path = $row['lipath'];
$lp_item_params = $row['liparams'];
if (empty($lp_item_params) && strpos($lp_item_path, '?') !== false) {
list($lp_item_path, $lp_item_params) = explode('?', $lp_item_path);
}
//$lp_item_params = '?'.$lp_item_params;
//add ? if none - left commented to give freedom to scorm implementation
//if(substr($lp_item_params,0,1)!='?'){
// $lp_item_params = '?'.$lp_item_params;
//}
$sys_course_path = "";
if ($type == 'http') {
$course_path = "";
//web path
} else {
$course_path = $sys_course_path;
//system path
}
$course_path = "../../upload/scorm/" . $this->parent_dir;
//now go through the specific cases to get the end of the path
switch ($lp_type) {
case 1:
if ($lp_item_type == 'dokeos_chapter') {
$file = 'lp_content.php?type=dir';
} else {
require_once 'resourcelinker.inc.php';
$file = rl_get_resource_link_for_learnpath(api_get_course_id(), $this->get_id(), $item_id);
// check how much attempts of a exercise exits in lp
$lp_item_id = $this->get_current_item_id();
$lp_view_id = $this->get_view_id();
$prevent_reinit = $this->items[$this->current]->get_prevent_reinit();
$list = $this->get_toc();
$type_quiz = false;
foreach ($list as $toc) {
if ($toc['id'] == $lp_item_id && $toc['type'] == 'quiz') {
$type_quiz = true;
}
}
if ($type_quiz) {
$lp_item_id = Database::escape_string($lp_item_id);
$lp_view_id = Database::escape_string($lp_view_id);
$sql = "SELECT count(*) FROM {$lp_item_view_table} WHERE lp_item_id='" . (int) $lp_item_id . "' AND lp_view_id ='" . (int) $lp_view_id . "' AND status='completed'";
$result = Database::query($sql, __FILE__, __LINE__);
$row_count = Database::fetch_row($result);
$count_item_view = (int) $row_count[0];
$not_multiple_attempt = 0;
if ($prevent_reinit === 1 && $count_item_view > 0) {
$not_multiple_attempt = 0;
//See Bug #6751
}
$file .= '¬_multiple_attempt=' . $not_multiple_attempt;
}
$tmp_array = explode("/", $file);
$document_name = $tmp_array[count($tmp_array) - 1];
if (strpos($document_name, '_DELETED_')) {
$file = 'blank.php?error=document_deleted';
}
}
break;
case 2:
if ($this->debug > 2) {
error_log('New LP - In learnpath::get_link() ' . __LINE__ . ' - Item type: ' . $lp_item_type, 0);
}
if ($lp_item_type != 'dir') {
//Quite complex here:
//we want to make sure 'http://' (and similar) links can
//be loaded as is (withouth the Dokeos path in front) but
//some contents use this form: resource.htm?resource=http://blablabla
//which means we have to find a protocol at the path's start, otherwise
//it should not be considered as an external URL
//if($this->prerequisites_match($item_id)){
if (preg_match('#^[a-zA-Z]{2,5}://#', $lp_item_path) != 0) {
if ($this->debug > 2) {
error_log('New LP - In learnpath::get_link() ' . __LINE__ . ' - Found match for protocol in ' . $lp_item_path, 0);
}
//distant url, return as is
$file = $lp_item_path;
} else {
if ($this->debug > 2) {
error_log('New LP - In learnpath::get_link() ' . __LINE__ . ' - No starting protocol in ' . $lp_item_path, 0);
}
//prevent getting untranslatable urls
$lp_item_path = preg_replace('/%2F/', '/', $lp_item_path);
$lp_item_path = preg_replace('/%3A/', ':', $lp_item_path);
//prepare the path
$file = $course_path . $lp_path . '/' . $lp_item_path;
//TODO fix this for urls with protocol header
$file = str_replace('//', '/', $file);
$file = str_replace(':/', '://', $file);
if (substr($lp_path, -1) == '/') {
$lp_path = substr($lp_path, 0, -1);
}
if (is_file(realpath($sys_course_path . '/scorm/' . $lp_path . '/' . $lp_item_path))) {
//if file not found
$decoded = html_entity_decode($lp_item_path);
list($decoded) = explode('?', $decoded);
if (!is_file(realpath($sys_course_path . '/scorm/' . $lp_path . '/' . $decoded))) {
require_once 'resourcelinker.inc.php';
$file = rl_get_resource_link_for_learnpath(api_get_course_id(), $this->get_id(), $item_id);
if (empty($file)) {
$file = 'blank.php?error=document_not_found';
} else {
$tmp_array = explode("/", $file);
$document_name = $tmp_array[count($tmp_array) - 1];
if (strpos($document_name, '_DELETED_')) {
$file = 'blank.php?error=document_deleted';
}
}
} else {
$file = $course_path . '/scorm/' . $lp_path . '/' . $decoded;
}
}
}
//}else{
//prerequisites did not match
//$file = 'blank.php';
//}
//We want to use parameters if they were defined in the imsmanifest
if ($file != 'blank.php') {
$file .= (strstr($file, '?') === false ? '?' : '') . $lp_item_params;
}
} else {
$file = 'lp_content.php?type=dir';
}
break;
case 3:
if ($this->debug > 2) {
error_log('New LP - In learnpath::get_link() ' . __LINE__ . ' - Item type: ' . $lp_item_type, 0);
}
//formatting AICC HACP append URL
$aicc_append = '?aicc_sid=' . urlencode(session_id()) . '&aicc_url=' . urlencode(api_get_path(WEB_CODE_PATH) . 'newscorm/aicc_hacp.php') . '&' . $lp_item_params;
if ($lp_item_type != 'dir') {
//Quite complex here:
//we want to make sure 'http://' (and similar) links can
//be loaded as is (withouth the Dokeos path in front) but
//some contents use this form: resource.htm?resource=http://blablabla
//which means we have to find a protocol at the path's start, otherwise
//it should not be considered as an external URL
if (preg_match('#^[a-zA-Z]{2,5}://#', $lp_item_path) != 0) {
if ($this->debug > 2) {
error_log('New LP - In learnpath::get_link() ' . __LINE__ . ' - Found match for protocol in ' . $lp_item_path, 0);
}
//distant url, return as is
$file = $lp_item_path;
// Enabled and modified by Ivan Tcholakov, 16-OCT-2008.
/*
if(stristr($file,'<servername>')!==false){
$file = str_replace('<servername>',$course_path.'/scorm/'.$lp_path.'/',$lp_item_path);
}
*/
if (stripos($file, '<servername>') !== false) {
//$file = str_replace('<servername>',$course_path.'/scorm/'.$lp_path.'/',$lp_item_path);
$web_course_path = str_replace('https://', '', str_replace('http://', '', $course_path));
$file = str_replace('<servername>', $web_course_path . '/scorm/' . $lp_path, $lp_item_path);
}
//
$file .= $aicc_append;
} else {
if ($this->debug > 2) {
error_log('New LP - In learnpath::get_link() ' . __LINE__ . ' - No starting protocol in ' . $lp_item_path, 0);
}
//prevent getting untranslatable urls
$lp_item_path = preg_replace('/%2F/', '/', $lp_item_path);
$lp_item_path = preg_replace('/%3A/', ':', $lp_item_path);
//prepare the path - lp_path might be unusable because it includes the "aicc" subdir name
$file = $course_path . '/scorm/' . $lp_path . '/' . $lp_item_path;
//TODO fix this for urls with protocol header
$file = str_replace('//', '/', $file);
$file = str_replace(':/', '://', $file);
$file .= $aicc_append;
}
} else {
$file = 'lp_content.php?type=dir';
}
break;
case 4:
break;
default:
break;
}
}
if ($this->debug > 2) {
error_log('New LP - In learnpath::get_link() - returning "' . $file . '" from get_link', 0);
}
return $file;
}
/**
* Get count dates inside attendance calendar by attendance id
* @param int $attendance_id
* @return int count of dates
*/
public static function get_count_dates_inside_attendance_calendar($attendance_id)
{
$tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
$attendance_id = intval($attendance_id);
$course_id = api_get_course_int_id();
$sql = "SELECT count(id) FROM {$tbl_attendance_calendar}\n WHERE\n \tc_id = {$course_id} AND\n \tattendance_id = '{$attendance_id}'";
$rs = Database::query($sql);
$count = 0;
if (Database::num_rows($rs) > 0) {
$row = Database::fetch_row($rs);
$count = $row[0];
}
return $count;
}
/**
* Check if this still links to an exercise
*/
public function is_valid_link()
{
//$sql = 'SELECT count(id) from '.$this->get_exercise_table().' WHERE c_id = '.$this->course_id.' AND id = '.(int)$this->get_ref_id().' AND session_id='.api_get_session_id().'';
$sql = 'SELECT count(id) from ' . $this->get_exercise_table() . ' WHERE c_id = ' . $this->course_id . ' AND id = ' . (int) $this->get_ref_id() . ' ';
$result = Database::query($sql);
$number = Database::fetch_row($result);
return $number[0] != 0;
}
/**
* Returns a list (array) of users who are online and in this course.
* @param int User ID
* @param int Number of minutes
* @param string Course code (could be empty, but then the function returns false)
* @return array Each line gives a user id and a login time
*/
public static function who_is_online_in_this_course($from, $number_of_items, $uid, $time_limit, $course_code)
{
if (empty($course_code)) {
return false;
}
if (empty($time_limit)) {
$time_limit = api_get_setting('time_limit_whosonline');
} else {
$time_limit = intval($time_limit);
}
$online_time = time() - $time_limit * 60;
$current_date = api_get_utc_datetime($online_time);
$track_online_table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_ONLINE);
$course_code = Database::escape_string($course_code);
$from = intval($from);
$number_of_items = intval($number_of_items);
$query = "SELECT login_user_id, login_date FROM {$track_online_table}\n WHERE login_user_id <> 2 AND course='{$course_code}' AND login_date >= '{$current_date}'\n LIMIT {$from}, {$number_of_items} ";
$result = Database::query($query);
if ($result) {
/*$valid_date_time = new DateTime();
$diff = "PT".$time_limit.'M';
$valid_date_time->sub(new DateInterval($diff));*/
$users_online = array();
while (list($login_user_id, $login_date) = Database::fetch_row($result)) {
/*$user_login_date = new DateTime($login_date);
if ($user_login_date > $valid_date_time->format('Y-m-d H:i:s')) {*/
$users_online[] = $login_user_id;
}
return $users_online;
} else {
return false;
}
}
header('Location: session_course_list.php?id_session=' . $id_session);
exit;
}
//$interbreadcrumb[]=array('url' => 'index.php',"name" => get_lang('PlatformAdmin'));
$interbreadcrumb[] = array('url' => "session_list.php", "name" => get_lang("SessionList"));
$interbreadcrumb[] = array('url' => "resume_session.php?id_session=" . $id_session, "name" => get_lang('SessionOverview'));
$interbreadcrumb[] = array('url' => "session_course_list.php?id_session={$id_session}", "name" => api_htmlentities($session_name, ENT_QUOTES, $charset));
$arr_infos = array();
if (isset($_POST['formSent']) && $_POST['formSent']) {
$formSent = 1;
// get all tutor by course_code in the session
$sql = "SELECT user_id\n\t FROM {$tbl_session_rel_course_rel_user}\n\t WHERE session_id = '{$id_session}' AND c_id = '" . $courseId . "' AND status = 2";
$rs_coaches = Database::query($sql);
$coaches_course_session = array();
if (Database::num_rows($rs_coaches) > 0) {
while ($row_coaches = Database::fetch_row($rs_coaches)) {
$coaches_course_session[] = $row_coaches[0];
}
}
$id_coaches = $_POST['id_coach'];
if (is_array($id_coaches) && count($id_coaches) > 0) {
foreach ($id_coaches as $id_coach) {
$id_coach = intval($id_coach);
$rs1 = SessionManager::set_coach_to_course_session($id_coach, $id_session, $courseId);
}
// set status to 0 other tutors from multiple list
$array_intersect = array_diff($coaches_course_session, $id_coaches);
foreach ($array_intersect as $no_coach_user_id) {
$rs2 = SessionManager::set_coach_to_course_session($no_coach_user_id, $id_session, $courseId, true);
}
header('Location: ' . Security::remove_XSS($_GET['page']) . '?id_session=' . $id_session);
/**
* Are there any results for this evaluation yet ?
* The 'max' property should not be changed then.
*/
public function has_results()
{
$tbl_grade_results = Database::get_main_table(TABLE_MAIN_GRADEBOOK_RESULT);
$sql = 'SELECT count(id) AS number
FROM ' . $tbl_grade_results . '
WHERE evaluation_id = ' . intval($this->id);
$result = Database::query($sql);
$number = Database::fetch_row($result);
return $number[0] != 0;
}
/**
* Get activities data to display
*/
static function get_activities_data($from, $number_of_items, $column, $direction)
{
global $dateTimeFormatLong;
$track_e_default = Database::get_main_table(TABLE_STATISTIC_TRACK_E_DEFAULT);
$table_user = Database::get_main_table(TABLE_MAIN_USER);
$access_url_rel_user_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
$current_url_id = api_get_current_access_url_id();
$column = intval($column);
$from = intval($from);
$number_of_items = intval($number_of_items);
if (!in_array($direction, array('ASC', 'DESC'))) {
$direction = 'DESC';
}
if (api_is_multiple_url_enabled()) {
$sql = "SELECT " . "default_event_type as col0, " . "default_value_type as col1, " . "default_value as col2, " . "user.username as col3, " . "user.user_id as col4, " . "default_date as col5 " . "FROM {$track_e_default} as track_default, {$table_user} as user, {$access_url_rel_user_table} as url " . "WHERE track_default.default_user_id = user.user_id AND url.user_id=user.user_id AND access_url_id='" . $current_url_id . "'";
} else {
$sql = "SELECT " . "default_event_type as col0, " . "default_value_type as col1, " . "default_value as col2, " . "user.username as col3, " . "user.user_id as col4, " . "default_date as col5 " . "FROM {$track_e_default} track_default, {$table_user} user " . "WHERE track_default.default_user_id = user.user_id ";
}
if (isset($_GET['keyword'])) {
$keyword = Database::escape_string(trim($_GET['keyword']));
$sql .= " AND (user.username LIKE '%" . $keyword . "%' OR default_event_type LIKE '%" . $keyword . "%' OR default_value_type LIKE '%" . $keyword . "%' OR default_value LIKE '%" . $keyword . "%') ";
}
if (!empty($column) && !empty($direction)) {
$sql .= " ORDER BY col{$column} {$direction}";
} else {
$sql .= " ORDER BY col5 DESC ";
}
$sql .= " LIMIT {$from}, {$number_of_items} ";
$res = Database::query($sql);
$activities = array();
while ($row = Database::fetch_row($res)) {
if (strpos($row[1], '_object') === false) {
$row[2] = $row[2];
} else {
if (!empty($row[2])) {
$row[2] = unserialize($row[2]);
if (is_array($row[2]) && !empty($row[2])) {
$row[2] = Text::implode_with_key(', ', $row[2]);
}
}
}
if (!empty($row['default_date']) && $row['default_date'] != '0000-00-00 00:00:00') {
$row['default_date'] = api_get_local_time($row['default_date']);
} else {
$row['default_date'] = '-';
}
if (!empty($row[4])) {
//user ID
$row[3] = Display::url($row[3], api_get_path(WEB_CODE_PATH) . 'admin/user_information?user_id=' . $row[5], array('title' => get_lang('UserInfo')));
$row[4] = TrackingUserLog::get_ip_from_user_event($row[4], $row[5], true);
if (empty($row[4])) {
$row[4] = get_lang('Unknown');
}
}
$activities[] = $row;
}
return $activities;
}
public function get_max_field_order()
{
$sql = "SELECT MAX(field_order) FROM {$this->table}";
$res = Database::query($sql);
$order = 0;
if (Database::num_rows($res) > 0) {
$row = Database::fetch_row($res);
$order = $row[0] + 1;
}
return $order;
}
