function search_courses($needle, $type) { global $tbl_course, $tbl_course_rel_access_url, $user_id; $xajax_response = new xajaxResponse(); $return = ''; if (!empty($needle) && !empty($type)) { // xajax send utf8 datas... datas in db can be non-utf8 datas $needle = Database::escape_string($needle); $assigned_courses_to_hrm = CourseManager::get_courses_followed_by_drh($user_id); $assigned_courses_code = array_keys($assigned_courses_to_hrm); foreach ($assigned_courses_code as &$value) { $value = "'" . $value . "'"; } $without_assigned_courses = ''; if (count($assigned_courses_code) > 0) { $without_assigned_courses = " AND c.code NOT IN(" . implode(',', $assigned_courses_code) . ")"; } if (api_is_multiple_url_enabled()) { $sql = "SELECT c.code, c.title\n FROM {$tbl_course} c\n\t\t\t\t\tLEFT JOIN {$tbl_course_rel_access_url} a\n ON (a.c_id = c.id)\n \tWHERE\n \t\tc.code LIKE '{$needle}%' {$without_assigned_courses} AND\n \t\taccess_url_id = " . api_get_current_access_url_id(); } else { $sql = "SELECT c.code, c.title\n \t\tFROM {$tbl_course} c\n \tWHERE\n \t\tc.code LIKE '{$needle}%'\n \t\t{$without_assigned_courses} "; } $rs = Database::query($sql); $return .= '<select id="origin" name="NoAssignedCoursesList[]" multiple="multiple" size="20" style="width:340px;">'; while ($course = Database::fetch_array($rs)) { $return .= '<option value="' . $course['code'] . '" title="' . htmlspecialchars($course['title'], ENT_QUOTES) . '">' . $course['title'] . ' (' . $course['code'] . ')</option>'; } $return .= '</select>'; $xajax_response->addAssign('ajax_list_courses_multiple', 'innerHTML', api_utf8_encode($return)); } return $xajax_response; }
function search_sessions($needle, $type) { global $tbl_session_rel_access_url, $tbl_session, $user_id; $xajax_response = new xajaxResponse(); $return = ''; if (!empty($needle) && !empty($type)) { $needle = Database::escape_string($needle); $assigned_sessions_to_hrm = SessionManager::get_sessions_followed_by_drh($user_id); $assigned_sessions_id = array_keys($assigned_sessions_to_hrm); $without_assigned_sessions = ''; if (count($assigned_sessions_id) > 0) { $without_assigned_sessions = " AND s.id NOT IN(" . implode(',', $assigned_sessions_id) . ")"; } if (api_is_multiple_url_enabled()) { $sql = " SELECT s.id, s.name FROM {$tbl_session} s\n LEFT JOIN {$tbl_session_rel_access_url} a ON (s.id = a.session_id)\n WHERE s.name LIKE '{$needle}%' {$without_assigned_sessions} AND access_url_id = " . api_get_current_access_url_id() . ""; } else { $sql = "SELECT s.id, s.name FROM {$tbl_session} s\n WHERE s.name LIKE '{$needle}%' {$without_assigned_sessions} "; } $rs = Database::query($sql); $return .= '<select class="form-control" id="origin" name="NoAssignedSessionsList[]" multiple="multiple" size="20">'; while ($session = Database::fetch_array($rs)) { $return .= '<option value="' . $session['id'] . '" title="' . htmlspecialchars($session['name'], ENT_QUOTES) . '">' . $session['name'] . '</option>'; } $return .= '</select>'; $xajax_response->addAssign('ajax_list_sessions_multiple', 'innerHTML', api_utf8_encode($return)); } return $xajax_response; }
/** * Get course data to display * @param int $from * @param int $number_of_items * @param int $column * @param string $direction * * @return array */ function get_course_data($from, $number_of_items, $column, $direction) { $course_table = Database::get_main_table(TABLE_MAIN_COURSE); $sql = "SELECT code AS col0,\n title AS col1,\n code AS col2,\n course_language AS col3,\n category_code AS col4,\n subscribe AS col5,\n unsubscribe AS col6,\n code AS col7,\n visibility AS col8,\n directory as col9,\n visual_code\n \t\tFROM {$course_table}"; if ((api_is_platform_admin() || api_is_session_admin()) && api_is_multiple_url_enabled() && api_get_current_access_url_id() != -1) { $access_url_rel_course_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE); $sql .= " INNER JOIN {$access_url_rel_course_table} url_rel_course ON (id = url_rel_course.c_id)"; } if (isset($_GET['keyword'])) { $keyword = Database::escape_string("%" . trim($_GET['keyword']) . "%"); $sql .= " WHERE (\n title LIKE '" . $keyword . "' OR\n code LIKE '" . $keyword . "' OR\n visual_code LIKE '" . $keyword . "'\n )\n "; } elseif (isset($_GET['keyword_code'])) { $keyword_code = Database::escape_string("%" . $_GET['keyword_code'] . "%"); $keyword_title = Database::escape_string("%" . $_GET['keyword_title'] . "%"); $keyword_category = Database::escape_string("%" . $_GET['keyword_category'] . "%"); $keyword_language = Database::escape_string("%" . $_GET['keyword_language'] . "%"); $keyword_visibility = Database::escape_string("%" . $_GET['keyword_visibility'] . "%"); $keyword_subscribe = Database::escape_string($_GET['keyword_subscribe']); $keyword_unsubscribe = Database::escape_string($_GET['keyword_unsubscribe']); $sql .= " WHERE\n (code LIKE '" . $keyword_code . "' OR visual_code LIKE '" . $keyword_code . "') AND\n title LIKE '" . $keyword_title . "' AND\n category_code LIKE '" . $keyword_category . "' AND\n course_language LIKE '" . $keyword_language . "' AND\n visibility LIKE '" . $keyword_visibility . "' AND\n subscribe LIKE '" . $keyword_subscribe . "' AND\n unsubscribe LIKE '" . $keyword_unsubscribe . "'"; } // Adding the filter to see the user's only of the current access_url. if ((api_is_platform_admin() || api_is_session_admin()) && api_is_multiple_url_enabled() && api_get_current_access_url_id() != -1) { $sql .= " AND url_rel_course.access_url_id=" . api_get_current_access_url_id(); } $sql .= " ORDER BY col{$column} {$direction} "; $sql .= " LIMIT {$from}, {$number_of_items}"; $res = Database::query($sql); $courses = array(); $languages = api_get_languages_to_array(); while ($course = Database::fetch_array($res)) { // Place colour icons in front of courses. $show_visual_code = $course['visual_code'] != $course[2] ? Display::label($course['visual_code'], 'info') : null; $course[1] = get_course_visibility_icon($course[8]) . '<a href="' . api_get_path(WEB_COURSE_PATH) . $course[9] . '/index.php">' . $course[1] . '</a> ' . $show_visual_code; $course[5] = $course[5] == SUBSCRIBE_ALLOWED ? get_lang('Yes') : get_lang('No'); $course[6] = $course[6] == UNSUBSCRIBE_ALLOWED ? get_lang('Yes') : get_lang('No'); $language = isset($languages[$course[3]]) ? $languages[$course[3]] : $course[3]; $course_rem = array($course[0], $course[1], $course[2], $language, $course[4], $course[5], $course[6], $course[7]); $courses[] = $course_rem; } return $courses; }
/** * Get user account list * * @param array $user array with keys: email, password, uid, loginName * @param boolean $reset * @param boolean $by_username * @return unknown */ public static function get_user_account_list($user, $reset = false, $by_username = false) { $portal_url = api_get_path(WEB_PATH); if (api_is_multiple_url_enabled()) { $url = api_get_current_access_url_info(); $portal_url = $url['url']; } if ($reset) { if ($by_username) { $secret_word = self::get_secret_word($user['email']); if ($reset) { $reset_link = $portal_url . "main/auth/lostPassword.php?reset=" . $secret_word . "&id=" . $user['uid']; } else { $reset_link = get_lang('Pass') . " : {$user['password']}"; } $user_account_list = get_lang('YourRegistrationData') . " : \n" . get_lang('UserName') . ' : ' . $user['loginName'] . "\n" . get_lang('ResetLink') . ' : ' . $reset_link . ''; if ($user_account_list) { $user_account_list = "\n-----------------------------------------------\n" . $user_account_list; } } else { foreach ($user as $this_user) { $secret_word = self::get_secret_word($this_user['email']); if ($reset) { $reset_link = $portal_url . "main/auth/lostPassword.php?reset=" . $secret_word . "&id=" . $this_user['uid']; } else { $reset_link = get_lang('Pass') . " : {$this_user['password']}"; } $user_account_list[] = get_lang('YourRegistrationData') . " : \n" . get_lang('UserName') . ' : ' . $this_user['loginName'] . "\n" . get_lang('ResetLink') . ' : ' . $reset_link . ''; } if ($user_account_list) { $user_account_list = implode("\n-----------------------------------------------\n", $user_account_list); } } } else { if (!$by_username) { $user = $user[0]; } $reset_link = get_lang('Pass') . " : {$user['password']}"; $user_account_list = get_lang('YourRegistrationData') . " : \n" . get_lang('UserName') . ' : ' . $user['loginName'] . "\n" . $reset_link . ''; } return $user_account_list; }
function search_coachs($needle) { $tbl_user = Database::get_main_table(TABLE_MAIN_USER); $xajax_response = new xajaxResponse(); $return = ''; if (!empty($needle)) { $order_clause = api_sort_by_first_name() ? ' ORDER BY firstname, lastname, username' : ' ORDER BY lastname, firstname, username'; // search users where username or firstname or lastname begins likes $needle $sql = 'SELECT username, lastname, firstname FROM ' . $tbl_user . ' user WHERE (username LIKE "' . $needle . '%" OR firstname LIKE "' . $needle . '%" OR lastname LIKE "' . $needle . '%") AND status=1' . $order_clause . ' LIMIT 10'; 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) { $sql = 'SELECT username, lastname, firstname 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 status=1' . $order_clause . ' LIMIT 10'; } } $rs = Database::query($sql); while ($user = Database::fetch_array($rs)) { $return .= '<a href="javascript: void(0);" onclick="javascript: fill_coach_field(\'' . $user['username'] . '\')">' . api_get_person_name($user['firstname'], $user['lastname']) . ' (' . $user['username'] . ')</a><br />'; } } $xajax_response->addAssign('ajax_list_coachs', 'innerHTML', api_utf8_encode($return)); return $xajax_response; }
/** * Returns users whose last login is prior from $ceiling * * @param int|string $ceiling last login date * @param bool $active_only if true returns only active users. Otherwise returns all users. * @return ResultSet */ static function listZombies($ceiling, $active_only = true, $count = 0, $from = 10, $column = 'user.firstname', $direction = 'desc') { if (empty($column)) { $column = 'user.firstname'; } $ceiling = is_numeric($ceiling) ? (int) $ceiling : strtotime($ceiling); $ceiling = date('Y-m-d H:i:s', $ceiling); $user_table = Database::get_main_table(TABLE_MAIN_USER); $login_table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LOGIN); $sql = 'SELECT user.user_id, user.firstname, user.lastname, user.username, user.auth_source, user.email, user.status, user.registration_date, user.active, access.login_date'; if (api_is_multiple_url_enabled()) { $access_url_rel_user_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER); $current_url_id = api_get_current_access_url_id(); $sql .= " FROM {$user_table} as user, {$login_table} as access, {$access_url_rel_user_table} as url\n WHERE\n access.login_date = (SELECT MAX(a.login_date)\n FROM {$login_table} as a\n WHERE a.login_user_id = user.user_id\n ) AND\n access.login_date <= '{$ceiling}' AND\n user.user_id = access.login_user_id AND\n url.user_id = user.user_id AND url.access_url_id={$current_url_id}"; } else { $sql .= " FROM {$user_table} as user, {$login_table} as access\n WHERE\n access.login_date = (SELECT MAX(a.login_date)\n FROM {$login_table} as a\n WHERE a.login_user_id = user.user_id\n ) AND\n access.login_date <= '{$ceiling}' AND\n user.user_id = access.login_user_id"; } if ($active_only) { $sql .= ' AND user.active = 1'; } $count = intval($count); $from = intval($from); $sql .= " ORDER BY {$column} {$direction}"; $sql .= " LIMIT {$count}, {$from} "; $result = Database::query($sql); return Database::store_result($result, 'ASSOC'); }
/** * @param $visible * @param null $id * @param string $type * @param bool $getCount * @param int $cutSize * @return string */ public static function getAnnouncements($visible, $id = null, $type = 'resumed', $getCount = false, $cutSize = 800) { $user_selected_language = Database::escape_string(api_get_interface_language()); $table = Database::get_main_table(TABLE_MAIN_SYSTEM_ANNOUNCEMENTS); $now = api_get_utc_datetime(); $select = '*'; if ($getCount) { $select = 'count(*) as count'; } $sql = "SELECT {$select}\n FROM {$table}\n\t\t\t\tWHERE\n\t\t\t\t( lang = '{$user_selected_language}' OR lang IS NULL) AND\n\t\t\t\t( '{$now}' >= date_start AND '{$now}' <= date_end) "; switch ($visible) { case self::VISIBLE_GUEST: $sql .= " AND visible_guest = 1 "; break; case self::VISIBLE_STUDENT: $sql .= " AND visible_student = 1 "; break; case self::VISIBLE_TEACHER: $sql .= " AND visible_teacher = 1 "; break; } if (isset($id) && !empty($id)) { $id = intval($id); $sql .= " AND id = {$id} "; } if (api_is_multiple_url_enabled()) { $current_url_id = api_get_current_access_url_id(); $sql .= " AND access_url_id IN ('1', '{$current_url_id}') "; } $sql .= " ORDER BY date_start DESC"; $announcements = Database::query($sql); if ($getCount) { $announcement = Database::fetch_array($announcements); return $announcement['count']; } $options = array(); if (Database::num_rows($announcements) > 0) { while ($announcement = Database::fetch_object($announcements)) { $content = $announcement->content; $url = api_get_path(WEB_PUBLIC_PATH) . 'news/' . $announcement->id; if (empty($id)) { if ($type == 'resumed') { if (api_strlen(strip_tags($content)) > $cutSize) { $content = Security::remove_XSS(Text::cut($announcement->content, $cutSize)) . ' ' . Display::url(get_lang('More'), $url); } } else { $content = $announcement->content; } } $announcement->title = Text::cut($announcement->title, $cutSize); $options[] = array('title' => $announcement->title, 'content' => $content); } } return $options; }
/** * Set header parameters * @param bool $sendHeaders send headers */ private function set_header_parameters($sendHeaders) { global $httpHeadXtra, $interbreadcrumb, $language_file, $_configuration, $this_section; $_course = api_get_course_info(); $help = $this->help; $nameTools = $this->title; $navigation = return_navigation_array(); $this->menu_navigation = $navigation['menu_navigation']; $this->assign('system_charset', api_get_system_encoding()); if (isset($httpHeadXtra) && $httpHeadXtra) { foreach ($httpHeadXtra as &$thisHttpHead) { header($thisHttpHead); } } $this->assign('online_button', Display::return_icon('statusonline.png', null, null, ICON_SIZE_ATOM)); $this->assign('offline_button', Display::return_icon('statusoffline.png', null, null, ICON_SIZE_ATOM)); // Get language iso-code for this page - ignore errors $this->assign('document_language', api_get_language_isocode()); $course_title = isset($_course['name']) ? $_course['name'] : null; $title_list = array(); $title_list[] = api_get_setting('platform.institution'); $title_list[] = api_get_setting('platform.site_name'); if (!empty($course_title)) { $title_list[] = $course_title; } if ($nameTools != '') { $title_list[] = $nameTools; } $title_string = ''; for ($i = 0; $i < count($title_list); $i++) { $title_string .= $title_list[$i]; if (isset($title_list[$i + 1])) { $item = trim($title_list[$i + 1]); if (!empty($item)) { $title_string .= ' - '; } } } $this->assign('title_string', $title_string); //Setting the theme and CSS files $css = $this->setCssFiles(); $this->set_js_files(); $this->setCssCustomFiles($css); //$this->set_js_files_post(); $browser = api_browser_support('check_browser'); if ($browser[0] == 'Internet Explorer' && $browser[1] >= '11') { $browser_head = '<meta http-equiv="X-UA-Compatible" content="IE=EmulateIE9" />'; $this->assign('browser_specific_head', $browser_head); } // Implementation of prefetch. // See http://cdn.chamilo.org/main/img/online.png for details $prefetch = ''; if (!empty($_configuration['cdn_enable'])) { $prefetch .= '<meta http-equiv="x-dns-prefetch-control" content="on">'; foreach ($_configuration['cdn'] as $host => $exts) { $prefetch .= '<link rel="dns-prefetch" href="' . $host . '">'; } } $this->assign('prefetch', $prefetch); $this->assign('text_direction', api_get_text_direction()); $this->assign('section_name', 'section-' . $this_section); //Defaul root chamilo favicon $favico = '<link rel="shortcut icon" href="' . api_get_path(WEB_PATH) . 'favicon.ico" type="image/x-icon" />'; //Added to verify if in the current Chamilo Theme exist a favicon $favicoThemeUrl = api_get_path(SYS_CSS_PATH) . 'themes/' . $this->theme . '/images/'; //If exist pick the current chamilo theme favicon if (is_file($favicoThemeUrl . 'favicon.ico')) { $favico = '<link rel="shortcut icon" href="' . api_get_path(WEB_CSS_PATH) . 'themes/' . $this->theme . '/images/favicon.ico" type="image/x-icon" />'; } if (api_is_multiple_url_enabled()) { $access_url_id = api_get_current_access_url_id(); if ($access_url_id != -1) { $url_info = api_get_access_url($access_url_id); $url = api_remove_trailing_slash(preg_replace('/https?:\\/\\//i', '', $url_info['url'])); $clean_url = api_replace_dangerous_char($url); $clean_url = str_replace('/', '-', $clean_url); $clean_url .= '/'; $homep = api_get_path(REL_PATH) . 'home/' . $clean_url; //homep for Home Path $icon_real_homep = api_get_path(SYS_APP_PATH) . 'home/' . $clean_url; //we create the new dir for the new sites if (is_file($icon_real_homep . 'favicon.ico')) { $favico = '<link rel="shortcut icon" href="' . $homep . 'favicon.ico" type="image/x-icon" />'; } } } $this->assign('favico', $favico); $this->setHelp(); //@todo move this in the template $bug_notification_link = ''; if (api_get_setting('show_link_bug_notification') == 'true' && $this->user_is_logged_in) { $bug_notification_link = '<li class="report"> <a href="http://support.chamilo.org/projects/chamilo-18/wiki/How_to_report_bugs" target="_blank"> <img src="' . api_get_path(WEB_IMG_PATH) . 'bug.large.png" style="vertical-align: middle;" alt="' . get_lang('ReportABug') . '" title="' . get_lang('ReportABug') . '"/></a> </li>'; } $this->assign('bug_notification_link', $bug_notification_link); $notification = return_notification_menu(); $this->assign('notification_menu', $notification); $resize = ''; if (api_get_setting('accessibility_font_resize') == 'true') { $resize .= '<div class="resize_font">'; $resize .= '<div class="btn-group">'; $resize .= '<a title="' . get_lang('DecreaseFontSize') . '" href="#" class="decrease_font btn btn-default"><em class="fa fa-font"></em></a>'; $resize .= '<a title="' . get_lang('ResetFontSize') . '" href="#" class="reset_font btn btn-default"><em class="fa fa-font"></em></a>'; $resize .= '<a title="' . get_lang('IncreaseFontSize') . '" href="#" class="increase_font btn btn-default"><em class="fa fa-font"></em></a>'; $resize .= '</div>'; $resize .= '</div>'; } $this->assign('accessibility', $resize); // Preparing values for the menu // Logout link $hideLogout = api_get_setting('hide_logout_button'); if ($hideLogout === 'true') { $this->assign('logout_link', null); } else { $this->assign('logout_link', api_get_path(WEB_PATH) . 'index.php?logout=logout&uid=' . api_get_user_id()); } //Profile link if (api_get_setting('social.allow_social_tool') == 'true') { $profile_url = api_get_path(WEB_CODE_PATH) . 'social/home.php'; $profile_link = Display::url(get_lang('Profile'), $profile_url); } else { $profile_url = api_get_path(WEB_CODE_PATH) . 'auth/profile.php'; $profile_link = Display::url(get_lang('Profile'), $profile_url); } $this->assign('profile_link', $profile_link); $this->assign('profile_url', $profile_url); //Message link $message_link = null; $message_url = null; if (api_get_setting('message.allow_message_tool') == 'true') { $message_url = api_get_path(WEB_CODE_PATH) . 'messages/inbox.php'; $message_link = '<a href="' . api_get_path(WEB_CODE_PATH) . 'messages/inbox.php">' . get_lang('Inbox') . '</a>'; } $this->assign('message_link', $message_link); $this->assign('message_url', $message_url); $institution = api_get_setting('platform.institution'); $portal_name = empty($institution) ? api_get_setting('platform.site_name') : $institution; $this->assign('portal_name', $portal_name); //Menu $menu = return_menu(); $this->assign('menu', $menu); // Setting notifications $count_unread_message = 0; if (api_get_setting('message.allow_message_tool') == 'true') { // get count unread message and total invitations $count_unread_message = MessageManager::get_number_of_messages(true); } $total_invitations = 0; if (api_get_setting('social.allow_social_tool') == 'true') { $number_of_new_messages_of_friend = SocialManager::get_message_number_invitation_by_user_id(api_get_user_id()); $usergroup = new UserGroup(); $group_pending_invitations = $usergroup->get_groups_by_user(api_get_user_id(), GROUP_USER_PERMISSION_PENDING_INVITATION, false); if (!empty($group_pending_invitations)) { $group_pending_invitations = count($group_pending_invitations); } else { $group_pending_invitations = 0; } $total_invitations = intval($number_of_new_messages_of_friend) + $group_pending_invitations + intval($count_unread_message); } $total_invitations = !empty($total_invitations) ? Display::badge($total_invitations) : null; $this->assign('user_notifications', $total_invitations); // Block Breadcrumb $breadcrumb = return_breadcrumb($interbreadcrumb, $language_file, $nameTools); $this->assign('breadcrumb', $breadcrumb); //Extra content $extra_header = null; if (!api_is_platform_admin()) { $extra_header = trim(api_get_setting('header_extra_content')); } $this->assign('header_extra_content', $extra_header); if ($sendHeaders) { header('Content-Type: text/html; charset=' . api_get_system_encoding()); header('X-Powered-By: ' . $_configuration['software_name'] . ' ' . substr($_configuration['system_version'], 0, 1)); } $socialMeta = ''; $metaTitle = api_get_setting('meta_title'); if (!empty($metaTitle)) { $socialMeta .= '<meta name="twitter:card" content="summary" />' . "\n"; $socialMeta .= '<meta property="og:title" content="' . $metaTitle . '" />' . "\n"; $socialMeta .= '<meta property="og:url" content="' . api_get_path(WEB_PATH) . '" />' . "\n"; $metaDescription = api_get_setting('meta_description'); if (!empty($metaDescription)) { $socialMeta .= '<meta property="og:description" content="' . $metaDescription . '" />' . "\n"; } $metaSite = api_get_setting('meta_twitter_site'); if (!empty($metaSite)) { $socialMeta .= '<meta name="twitter:site" content="' . $metaSite . '" />' . "\n"; $metaCreator = api_get_setting('meta_twitter_creator'); if (!empty($metaCreator)) { $socialMeta .= '<meta name="twitter:creator" content="' . $metaCreator . '" />' . "\n"; } } $metaImage = api_get_setting('meta_image_path'); if (!empty($metaImage)) { if (is_file(api_get_path(SYS_PATH) . $metaImage)) { $path = api_get_path(WEB_PATH) . $metaImage; $socialMeta .= '<meta property="og:image" content="' . $path . '" />' . "\n"; } } } $this->assign('social_meta', $socialMeta); }
} $result = Database::query($sql); $db_users = Database::store_result($result); unset($result); $sql = "SELECT code,visual_code,title\n FROM {$tbl_course}\n WHERE visual_code LIKE '" . $first_letter_course . "%'\n ORDER BY " . (count($courses) > 0 ? "(code IN('" . implode("','", $courses) . "')) DESC," : "") . " visual_code"; if (api_is_multiple_url_enabled()) { $tbl_course_rel_access_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE); $access_url_id = api_get_current_access_url_id(); if ($access_url_id != -1) { $sql = "SELECT code, visual_code, title\n FROM {$tbl_course} as course\n INNER JOIN {$tbl_course_rel_access_url} course_rel_url\n ON (course_rel_url.course_code= course.code)\n WHERE\n access_url_id = {$access_url_id} AND\n (visual_code LIKE '" . $first_letter_course . "%' )\n ORDER BY " . (count($courses) > 0 ? "(code IN('" . implode("','", $courses) . "')) DESC," : "") . " visual_code"; } } $result = Database::query($sql); $db_courses = Database::store_result($result); unset($result); if (api_is_multiple_url_enabled()) { $tbl_course_rel_access_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE); $tbl_course_user = Database::get_main_table(TABLE_MAIN_COURSE_USER); $access_url_id = api_get_current_access_url_id(); if ($access_url_id != -1) { $sqlNbCours = "\tSELECT course_rel_user.course_code, course.title\n FROM {$tbl_course_user} as course_rel_user\n INNER JOIN {$tbl_course} as course\n ON course.code = course_rel_user.course_code\n INNER JOIN {$tbl_course_rel_access_url} course_rel_url\n ON (course_rel_url.course_code= course.code)\n WHERE\n access_url_id = {$access_url_id} AND\n course_rel_user.user_id='" . $_user['user_id'] . "' AND\n course_rel_user.status='1'\n ORDER BY course.title"; } } ?> <form name="formulaire" method="post" action="<?php echo api_get_self(); ?> " style="margin:0px;"> <?php if (is_array($extra_field_list)) { if (is_array($new_field_list) && count($new_field_list) > 0) {
function search_users($needle, $type) { global $tbl_access_url_rel_user, $tbl_user, $user_anonymous, $current_user_id, $user_id, $userStatus; $xajax_response = new xajaxResponse(); $return = ''; if (!empty($needle) && !empty($type)) { $assigned_users_to_hrm = array(); switch ($userStatus) { case DRH: //no break; //no break; case PLATFORM_ADMIN: $assigned_users_to_hrm = UserManager::get_users_followed_by_drh($user_id); break; case STUDENT_BOSS: $assigned_users_to_hrm = UserManager::getUsersFollowedByStudentBoss($user_id); break; } $assigned_users_id = array_keys($assigned_users_to_hrm); $without_assigned_users = ''; $westernOrder = api_is_western_name_order(); if ($westernOrder) { $order_clause = " ORDER BY firstname, lastname"; } else { $order_clause = " ORDER BY lastname, firstname"; } if (count($assigned_users_id) > 0) { $without_assigned_users = " AND user.user_id NOT IN(" . implode(',', $assigned_users_id) . ")"; } if (api_is_multiple_url_enabled()) { $sql = "SELECT user.user_id, username, lastname, firstname\n FROM {$tbl_user} user\n LEFT JOIN {$tbl_access_url_rel_user} au ON (au.user_id = user.user_id)\n WHERE\n " . (api_sort_by_first_name() ? 'firstname' : 'lastname') . " LIKE '{$needle}%' AND\n status NOT IN(" . DRH . ", " . SESSIONADMIN . ", " . STUDENT_BOSS . ") AND\n user.user_id NOT IN ({$user_anonymous}, {$current_user_id}, {$user_id})\n {$without_assigned_users} AND\n access_url_id = " . api_get_current_access_url_id() . "\n {$order_clause}\n "; } else { $sql = "SELECT user_id, username, lastname, firstname\n FROM {$tbl_user} user\n WHERE\n " . (api_sort_by_first_name() ? 'firstname' : 'lastname') . " LIKE '{$needle}%' AND\n status NOT IN(" . DRH . ", " . SESSIONADMIN . ", " . STUDENT_BOSS . ") AND\n user_id NOT IN ({$user_anonymous}, {$current_user_id}, {$user_id})\n {$without_assigned_users}\n {$order_clause}\n "; } $rs = Database::query($sql); $xajax_response->addAssign('ajax_list_users_multiple', 'innerHTML', api_utf8_encode($return)); if ($type == 'single') { $tbl_user_rel_access_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER); $access_url_id = api_get_current_access_url_id(); $sql = 'SELECT user.user_id, username, lastname, firstname 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 '; switch ($userStatus) { case DRH: $sql .= " user.status <> 6 AND user.status <> " . DRH; break; case STUDENT_BOSS: $sql .= " user.status <> 6 AND user.status <> " . STUDENT_BOSS; break; } $sql .= " {$order_clause} LIMIT 11"; $rs = Database::query($sql); $i = 0; while ($user = Database::fetch_array($rs)) { $i++; if ($i <= 10) { $person_name = api_get_person_name($user['firstname'], $user['lastname']); $return .= '<a href="javascript: void(0);" onclick="javascript: add_user_to_user(\'' . $user['user_id'] . '\',\'' . $person_name . ' (' . $user['username'] . ')' . '\')">' . $person_name . ' (' . $user['username'] . ')</a><br />'; } else { $return .= '...<br />'; } } $xajax_response->addAssign('ajax_list_users_single', 'innerHTML', api_utf8_encode($return)); } else { $return .= '<select id="origin" class="form-control" name="NoAssignedUsersList[]" multiple="multiple" size="15" ">'; while ($user = Database::fetch_array($rs)) { $person_name = api_get_person_name($user['firstname'], $user['lastname']); $return .= '<option value="' . $user['user_id'] . '" title="' . htmlspecialchars($person_name, ENT_QUOTES) . '">' . $person_name . ' (' . $user['username'] . ')</option>'; } $return .= '</select>'; $xajax_response->addAssign('ajax_list_users_multiple', 'innerHTML', api_utf8_encode($return)); } } return $xajax_response; }
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; }
/** * Update user information with all the parameters passed to this function * @param int The ID of the user to be updated * @param string The user's firstname * @param string The user's lastname * @param string The user's username (login) * @param string The user's password * @param string The authentication source (default: "platform") * @param string The user's e-mail address * @param int The user's status * @param string The user's official code (usually just an internal institutional code) * @param string The user's phone number * @param string The user's picture URL (internal to the Chamilo directory) * @param int The user ID of the person who registered this user (optional, defaults to null) * @param int The department of HR in which the user is registered (optional, defaults to 0) * @param array A series of additional fields to add to this user as extra fields (optional, defaults to null) * @return boolean true if the user information was updated * @assert (false, false, false, false, false, false, false, false, false, false, false, false, false) === false */ public static function update_user($user_id, $firstname, $lastname, $username, $password = null, $auth_source = null, $email, $status, $official_code, $phone, $picture_uri, $expiration_date, $active, $creator_id = null, $hr_dept_id = 0, $extra = null, $language = 'english', $encrypt_method = '', $send_email = false, $reset_password = 0) { $hook = HookUpdateUser::create(); if (!empty($hook)) { $hook->notifyUpdateUser(HOOK_EVENT_TYPE_PRE); } global $_configuration; $original_password = $password; if (empty($user_id)) { return false; } $user_info = api_get_user_info($user_id, false, true); if ($reset_password == 0) { $password = null; $auth_source = $user_info['auth_source']; } elseif ($reset_password == 1) { $original_password = $password = api_generate_password(); $auth_source = PLATFORM_AUTH_SOURCE; } elseif ($reset_password == 2) { $password = $password; $auth_source = PLATFORM_AUTH_SOURCE; } elseif ($reset_password == 3) { $password = $password; $auth_source = $auth_source; } if ($user_id != strval(intval($user_id))) { return false; } if ($user_id === false) { return false; } //Checking the user language $languages = api_get_languages(); if (!in_array($language, $languages['folder'])) { $language = api_get_setting('platformLanguage'); } $change_active = 0; if ($user_info['active'] != $active) { $change_active = 1; } $userManager = self::getManager(); /** @var Chamilo\UserBundle\Entity\User $user */ $user = self::getRepository()->find($user_id); if (empty($user)) { return false; } if (!empty($expiration_date)) { $expiration_date = api_get_utc_datetime($expiration_date); $expiration_date = new \DateTime($expiration_date, new DateTimeZone('UTC')); } $user->setLastname($lastname)->setFirstname($firstname)->setUsername($username)->setStatus($status)->setAuthSource($auth_source)->setLanguage($language)->setEmail($email)->setOfficialCode($official_code)->setPhone($phone)->setPictureUri($picture_uri)->setExpirationDate($expiration_date)->setActive($active)->setHrDeptId($hr_dept_id); if (!is_null($password)) { $user->setPlainPassword($password); } $userManager->updateUser($user, true); if ($change_active == 1) { if ($active == 1) { $event_title = LOG_USER_ENABLE; } else { $event_title = LOG_USER_DISABLE; } Event::addEvent($event_title, LOG_USER_ID, $user_id); } if (is_array($extra) && count($extra) > 0) { $res = true; foreach ($extra as $fname => $fvalue) { $res = $res && self::update_extra_field_value($user_id, $fname, $fvalue); } } if (!empty($email) && $send_email) { $recipient_name = api_get_person_name($firstname, $lastname, null, PERSON_NAME_EMAIL_ADDRESS); $emailsubject = '[' . api_get_setting('siteName') . '] ' . get_lang('YourReg') . ' ' . api_get_setting('siteName'); $sender_name = api_get_person_name(api_get_setting('administratorName'), api_get_setting('administratorSurname'), null, PERSON_NAME_EMAIL_ADDRESS); $email_admin = api_get_setting('emailAdministrator'); if (api_is_multiple_url_enabled()) { $access_url_id = api_get_current_access_url_id(); if ($access_url_id != -1) { $url = api_get_access_url($access_url_id); $emailbody = get_lang('Dear') . " " . stripslashes(api_get_person_name($firstname, $lastname)) . ",\n\n" . get_lang('YouAreReg') . " " . api_get_setting('siteName') . " " . get_lang('WithTheFollowingSettings') . "\n\n" . get_lang('Username') . " : " . $username . ($reset_password > 0 ? "\n" . get_lang('Pass') . " : " . stripslashes($original_password) : "") . "\n\n" . get_lang('Address') . " " . api_get_setting('siteName') . " " . get_lang('Is') . " : " . $url['url'] . "\n\n" . get_lang('Problem') . "\n\n" . get_lang('SignatureFormula') . ",\n\n" . api_get_person_name(api_get_setting('administratorName'), api_get_setting('administratorSurname')) . "\n" . get_lang('Manager') . " " . api_get_setting('siteName') . "\nT. " . api_get_setting('administratorTelephone') . "\n" . get_lang('Email') . " : " . api_get_setting('emailAdministrator'); } } else { $emailbody = get_lang('Dear') . " " . stripslashes(api_get_person_name($firstname, $lastname)) . ",\n\n" . get_lang('YouAreReg') . " " . api_get_setting('siteName') . " " . get_lang('WithTheFollowingSettings') . "\n\n" . get_lang('Username') . " : " . $username . ($reset_password > 0 ? "\n" . get_lang('Pass') . " : " . stripslashes($original_password) : "") . "\n\n" . get_lang('Address') . " " . api_get_setting('siteName') . " " . get_lang('Is') . " : " . $_configuration['root_web'] . "\n\n" . get_lang('Problem') . "\n\n" . get_lang('SignatureFormula') . ",\n\n" . api_get_person_name(api_get_setting('administratorName'), api_get_setting('administratorSurname')) . "\n" . get_lang('Manager') . " " . api_get_setting('siteName') . "\nT. " . api_get_setting('administratorTelephone') . "\n" . get_lang('Email') . " : " . api_get_setting('emailAdministrator'); } api_mail_html($recipient_name, $email, $emailsubject, $emailbody, $sender_name, $email_admin); } if (!empty($hook)) { $hook->notifyUpdateUser(HOOK_EVENT_TYPE_POST); } return $user->getId(); }
/** * Get data for users list in sortable with pagination * @param $from * @param $number_of_items * @param $column * @param $direction * @param $includeInvitedUsers boolean Whether include the invited users * @return array */ public static function get_user_data($from, $number_of_items, $column, $direction, $includeInvitedUsers = false) { global $user_ids, $course_code, $additional_user_profile_info, $export_csv, $is_western_name_order, $csv_content, $session_id; $course_code = Database::escape_string($course_code); $tbl_user = Database::get_main_table(TABLE_MAIN_USER); $tbl_url_rel_user = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER); $access_url_id = api_get_current_access_url_id(); // get all users data from a course for sortable with limit if (is_array($user_ids)) { $user_ids = array_map('intval', $user_ids); $condition_user = "******" . implode(',', $user_ids) . ") "; } else { $user_ids = intval($user_ids); $condition_user = "******"; } if (!empty($_GET['user_keyword'])) { $keyword = trim(Database::escape_string($_GET['user_keyword'])); $condition_user .= " AND (\n user.firstname LIKE '%" . $keyword . "%' OR\n user.lastname LIKE '%" . $keyword . "%' OR\n user.username LIKE '%" . $keyword . "%' OR\n user.email LIKE '%" . $keyword . "%'\n ) "; } $url_table = null; $url_condition = null; if (api_is_multiple_url_enabled()) { $url_table = ", " . $tbl_url_rel_user . "as url_users"; $url_condition = " AND user.user_id = url_users.user_id AND access_url_id='{$access_url_id}'"; } $invitedUsersCondition = ''; if (!$includeInvitedUsers) { $invitedUsersCondition = " AND user.status != " . INVITEE; } $sql = "SELECT user.user_id as user_id,\n user.official_code as col0,\n user.lastname as col1,\n user.firstname as col2,\n user.username as col3\n FROM {$tbl_user} as user {$url_table}\n \t {$condition_user} {$url_condition} {$invitedUsersCondition}"; if (!in_array($direction, array('ASC', 'DESC'))) { $direction = 'ASC'; } $column = intval($column); $from = intval($from); $number_of_items = intval($number_of_items); $sql .= " ORDER BY col{$column} {$direction} "; $sql .= " LIMIT {$from},{$number_of_items}"; $res = Database::query($sql); $users = array(); $course_info = api_get_course_info($course_code); $total_surveys = 0; $total_exercises = ExerciseLib::get_all_exercises($course_info, $session_id, false, null, false, 3); if (empty($session_id)) { $survey_user_list = array(); $survey_list = SurveyManager::get_surveys($course_code, $session_id); $total_surveys = count($survey_list); if (!empty($survey_list)) { foreach ($survey_list as $survey) { $user_list = SurveyManager::get_people_who_filled_survey($survey['survey_id'], false, $course_info['real_id']); foreach ($user_list as $user_id) { isset($survey_user_list[$user_id]) ? $survey_user_list[$user_id]++ : ($survey_user_list[$user_id] = 1); } } } } while ($user = Database::fetch_array($res, 'ASSOC')) { $courseInfo = api_get_course_info($course_code); $courseId = $courseInfo['real_id']; $user['official_code'] = $user['col0']; $user['lastname'] = $user['col1']; $user['firstname'] = $user['col2']; $user['username'] = $user['col3']; $user['time'] = api_time_to_hms(Tracking::get_time_spent_on_the_course($user['user_id'], $courseId, $session_id)); $avg_student_score = Tracking::get_avg_student_score($user['user_id'], $course_code, array(), $session_id); $avg_student_progress = Tracking::get_avg_student_progress($user['user_id'], $course_code, array(), $session_id); if (empty($avg_student_progress)) { $avg_student_progress = 0; } $user['average_progress'] = $avg_student_progress . '%'; $total_user_exercise = Tracking::get_exercise_student_progress($total_exercises, $user['user_id'], $courseId, $session_id); $user['exercise_progress'] = $total_user_exercise; $total_user_exercise = Tracking::get_exercise_student_average_best_attempt($total_exercises, $user['user_id'], $courseId, $session_id); $user['exercise_average_best_attempt'] = $total_user_exercise; if (is_numeric($avg_student_score)) { $user['student_score'] = $avg_student_score . '%'; } else { $user['student_score'] = $avg_student_score; } $user['count_assignments'] = Tracking::count_student_assignments($user['user_id'], $course_code, $session_id); $user['count_messages'] = Tracking::count_student_messages($user['user_id'], $course_code, $session_id); $user['first_connection'] = Tracking::get_first_connection_date_on_the_course($user['user_id'], $courseId, $session_id); $user['last_connection'] = Tracking::get_last_connection_date_on_the_course($user['user_id'], $courseInfo, $session_id); // we need to display an additional profile field $user['additional'] = ''; if (isset($_GET['additional_profile_field']) && is_numeric($_GET['additional_profile_field'])) { if (isset($additional_user_profile_info[$user['user_id']]) && is_array($additional_user_profile_info[$user['user_id']])) { $user['additional'] = implode(', ', $additional_user_profile_info[$user['user_id']]); } } if (empty($session_id)) { $user['survey'] = (isset($survey_user_list[$user['user_id']]) ? $survey_user_list[$user['user_id']] : 0) . ' / ' . $total_surveys; } $user['link'] = '<center><a href="../mySpace/myStudents.php?student=' . $user['user_id'] . '&details=true&course=' . $course_code . '&origin=tracking_course&id_session=' . $session_id . '"><img src="' . api_get_path(WEB_IMG_PATH) . 'icons/22/2rightarrow.png" border="0" /></a></center>'; // store columns in array $users $is_western_name_order = api_is_western_name_order(); $user_row = array(); $user_row[] = $user['official_code']; //0 if ($is_western_name_order) { $user_row[] = $user['firstname']; $user_row[] = $user['lastname']; } else { $user_row[] = $user['lastname']; $user_row[] = $user['firstname']; } $user_row[] = $user['username']; $user_row[] = $user['time']; $user_row[] = $user['average_progress']; $user_row[] = $user['exercise_progress']; $user_row[] = $user['exercise_average_best_attempt']; $user_row[] = $user['student_score']; $user_row[] = $user['count_assignments']; $user_row[] = $user['count_messages']; if (empty($session_id)) { $user_row[] = $user['survey']; } $user_row[] = $user['first_connection']; $user_row[] = $user['last_connection']; if (isset($_GET['additional_profile_field']) && is_numeric($_GET['additional_profile_field'])) { $user_row[] = $user['additional']; } $user_row[] = $user['link']; $users[] = $user_row; if ($export_csv) { if (empty($session_id)) { $user_row = array_map('strip_tags', $user_row); unset($user_row[14]); unset($user_row[15]); } else { $user_row = array_map('strip_tags', $user_row); unset($user_row[13]); unset($user_row[14]); } $csv_content[] = $user_row; } } return $users; }
/** * Search the courses database for a course that matches the search term. * The search is done on the code, title and tutor field of the course table. * @param string $search_term The string that the user submitted, what we are looking for * @param array $limit * @return array An array containing a list of all the courses matching the the search term. */ public function search_courses($search_term, $limit) { $courseTable = Database::get_main_table(TABLE_MAIN_COURSE); $extraFieldTable = Database::get_main_table(TABLE_EXTRA_FIELD); $extraFieldValuesTable = Database::get_main_table(TABLE_EXTRA_FIELD_VALUES); $limitFilter = getLimitFilterFromArray($limit); // get course list auto-register $sql = "SELECT item_id\n FROM {$extraFieldValuesTable} tcfv\n INNER JOIN {$extraFieldTable} tcf ON tcfv.field_id = tcf.id\n WHERE\n tcf.variable = 'special_course' AND\n tcfv.value = 1 "; $special_course_result = Database::query($sql); if (Database::num_rows($special_course_result) > 0) { $special_course_list = array(); while ($result_row = Database::fetch_array($special_course_result)) { $special_course_list[] = '"' . $result_row['item_id'] . '"'; } } $without_special_courses = ''; if (!empty($special_course_list)) { $without_special_courses = ' AND course.code NOT IN (' . implode(',', $special_course_list) . ')'; } $search_term_safe = Database::escape_string($search_term); $sql_find = "SELECT * FROM {$courseTable}\n WHERE (\n code LIKE '%" . $search_term_safe . "%' OR\n title LIKE '%" . $search_term_safe . "%' OR\n tutor_name LIKE '%" . $search_term_safe . "%'\n )\n {$without_special_courses}\n ORDER BY title, visual_code ASC\n {$limitFilter}\n "; if (api_is_multiple_url_enabled()) { $url_access_id = api_get_current_access_url_id(); if ($url_access_id != -1) { $tbl_url_rel_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE); $sql_find = "SELECT *\n FROM {$courseTable} 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 code LIKE '%" . $search_term_safe . "%' OR\n title LIKE '%" . $search_term_safe . "%' OR\n tutor_name LIKE '%" . $search_term_safe . "%'\n )\n {$without_special_courses}\n ORDER BY title, visual_code ASC\n {$limitFilter}\n "; } } $result_find = Database::query($sql_find); $courses = array(); while ($row = Database::fetch_array($result_find)) { $row['registration_code'] = !empty($row['registration_code']); $count_users = count(CourseManager::get_user_list_from_course_code($row['code'])); $count_connections_last_month = Tracking::get_course_connections_count($row['id'], 0, api_get_utc_datetime(time() - 30 * 86400)); $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; }
/** * Searches a course, given a search string and a type of search box * @param string $needle Search string * @param string $type Type of search box ('single' or anything else) * @return string XajaxResponse * @assert ('abc', 'single') !== null * @assert ('abc', 'multiple') !== null */ public static function search_courses($needle, $type) { global $tbl_course, $tbl_session_rel_course, $id_session; $course_title = null; $xajax_response = new xajaxResponse(); $return = ''; if (!empty($needle) && !empty($type)) { // xajax send utf8 datas... datas in db can be non-utf8 datas $charset = api_get_system_encoding(); $needle = api_convert_encoding($needle, $charset, 'utf-8'); $cond_course_code = ''; if (!empty($id_session)) { $id_session = intval($id_session); // check course_code from session_rel_course table $sql = 'SELECT c_id FROM ' . $tbl_session_rel_course . ' WHERE session_id = ' . $id_session; $res = Database::query($sql); $course_codes = ''; if (Database::num_rows($res) > 0) { while ($row = Database::fetch_row($res)) { $course_codes .= '\'' . $row[0] . '\','; } $course_codes = substr($course_codes, 0, strlen($course_codes) - 1); $cond_course_code = ' AND course.id NOT IN(' . $course_codes . ') '; } } if ($type == 'single') { // search users where username or firstname or lastname begins likes $needle $sql = 'SELECT course.code, course.visual_code, course.title, session_rel_course.session_id FROM ' . $tbl_course . ' course LEFT JOIN ' . $tbl_session_rel_course . ' session_rel_course ON course.id = session_rel_course.c_id AND session_rel_course.session_id = ' . intval($id_session) . ' WHERE course.visual_code LIKE "' . $needle . '%" OR course.title LIKE "' . $needle . '%"'; } else { $sql = 'SELECT course.code, course.visual_code, course.title FROM ' . $tbl_course . ' course WHERE course.visual_code LIKE "' . $needle . '%" ' . $cond_course_code . ' ORDER BY course.code '; } if (api_is_multiple_url_enabled()) { $tbl_course_rel_access_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE); $access_url_id = api_get_current_access_url_id(); if ($access_url_id != -1) { if ($type == 'single') { $sql = 'SELECT course.code, course.visual_code, course.title, session_rel_course.session_id FROM ' . $tbl_course . ' course LEFT JOIN ' . $tbl_session_rel_course . ' session_rel_course ON course.id = session_rel_course.c_id AND session_rel_course.session_id = ' . intval($id_session) . ' INNER JOIN ' . $tbl_course_rel_access_url . ' url_course ON (url_course.c_id = course.id) WHERE access_url_id = ' . $access_url_id . ' AND (course.visual_code LIKE "' . $needle . '%" OR course.title LIKE "' . $needle . '%" )'; } else { $sql = 'SELECT course.code, course.visual_code, course.title FROM ' . $tbl_course . ' course, ' . $tbl_course_rel_access_url . ' url_course WHERE url_course.c_id = course.id AND access_url_id = ' . $access_url_id . ' AND course.visual_code LIKE "' . $needle . '%" ' . $cond_course_code . ' ORDER BY course.code '; } } } $rs = Database::query($sql); $course_list = array(); if ($type == 'single') { while ($course = Database::fetch_array($rs)) { $course_list[] = $course['code']; $course_title = str_replace("'", "\\'", $course_title); $return .= '<a href="javascript: void(0);" onclick="javascript: add_course_to_session(\'' . $course['code'] . '\',\'' . $course_title . ' (' . $course['visual_code'] . ')' . '\')">' . $course['title'] . ' (' . $course['visual_code'] . ')</a><br />'; } $xajax_response->addAssign('ajax_list_courses_single', 'innerHTML', api_utf8_encode($return)); } else { $return .= '<select id="origin" name="NoSessionCoursesList[]" multiple="multiple" size="20" style="width:340px;">'; while ($course = Database::fetch_array($rs)) { $course_list[] = $course['code']; $course_title = str_replace("'", "\\'", $course_title); $return .= '<option value="' . $course['code'] . '" title="' . htmlspecialchars($course['title'] . ' (' . $course['visual_code'] . ')', ENT_QUOTES) . '">' . $course['title'] . ' (' . $course['visual_code'] . ')</option>'; } $return .= '</select>'; $xajax_response->addAssign('ajax_list_courses_multiple', 'innerHTML', api_utf8_encode($return)); } } $_SESSION['course_list'] = $course_list; return $xajax_response; }
/** * Gets the admin session list callback of the session/session_list.php * page with all user/details in the right fomat * @param array * @result array Array of rows results * @asset ('a') === false */ public static function get_sessions_admin_complete($options = array()) { if (!is_array($options)) { return false; } $tbl_session = Database::get_main_table(TABLE_MAIN_SESSION); $tbl_session_category = Database::get_main_table(TABLE_MAIN_SESSION_CATEGORY); $tbl_user = Database::get_main_table(TABLE_MAIN_USER); $tbl_session_rel_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE); $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE); $extraFieldTable = Database::get_main_table(TABLE_EXTRA_FIELD); $tbl_session_field_values = Database::get_main_table(TABLE_EXTRA_FIELD_VALUES); $tbl_session_field_options = Database::get_main_table(TABLE_EXTRA_FIELD_OPTIONS); $where = 'WHERE 1 = 1 '; $user_id = api_get_user_id(); if (!api_is_platform_admin()) { if (api_is_session_admin() && api_get_setting('allow_session_admins_to_manage_all_sessions') == 'false') { $where .= " AND s.session_admin_id = {$user_id} "; } } $coach_name = " CONCAT(u.lastname , ' ', u.firstname) as coach_name "; if (api_is_western_name_order()) { $coach_name = " CONCAT(u.firstname, ' ', u.lastname) as coach_name "; } $today = api_get_utc_datetime(); $inject_extra_fields = null; $extra_fields = array(); $extra_fields_info = array(); //for now only sessions $extra_field = new ExtraField('session'); $double_fields = array(); $extra_field_option = new ExtraFieldOption('session'); if (isset($options['extra'])) { $extra_fields = $options['extra']; if (!empty($extra_fields)) { foreach ($extra_fields as $extra) { $inject_extra_fields .= " IF (fv.field_id = {$extra['id']}, fvo.option_display_text, NULL ) as {$extra['field']} , "; if (isset($extra_fields_info[$extra['id']])) { $info = $extra_fields_info[$extra['id']]; } else { $info = $extra_field->get($extra['id']); $extra_fields_info[$extra['id']] = $info; } if ($info['field_type'] == ExtraField::FIELD_TYPE_DOUBLE_SELECT) { $double_fields[$info['id']] = $info; } } } } $options_by_double = array(); foreach ($double_fields as $double) { $my_options = $extra_field_option->get_field_options_by_field($double['id'], true); $options_by_double['extra_' . $double['field_variable']] = $my_options; } //sc.name as category_name, $select = "\n SELECT * FROM (\n SELECT DISTINCT\n IF (\n (s.access_start_date <= '{$today}' AND '{$today}' < s.access_end_date) OR\n (s.access_start_date = '0000-00-00 00:00:00' AND s.access_end_date = '0000-00-00 00:00:00' ) OR\n (s.access_start_date IS NULL AND s.access_end_date IS NULL) OR\n (s.access_start_date <= '{$today}' AND ('0000-00-00 00:00:00' = s.access_end_date OR s.access_end_date IS NULL )) OR\n ('{$today}' < s.access_end_date AND ('0000-00-00 00:00:00' = s.access_start_date OR s.access_start_date IS NULL) )\n , 1, 0) as session_active,\n s.name,\n s.nbr_courses,\n s.nbr_users,\n s.display_start_date,\n s.display_end_date,\n {$coach_name},\n access_start_date,\n access_end_date,\n s.visibility,\n u.user_id,\n {$inject_extra_fields}\n c.title as course_title,\n s.id "; if (!empty($options['where'])) { if (!empty($options['extra'])) { $options['where'] = str_replace(' 1 = 1 AND', '', $options['where']); $options['where'] = str_replace('AND', 'OR', $options['where']); foreach ($options['extra'] as $extra) { $options['where'] = str_replace($extra['field'], 'fv.field_id = ' . $extra['id'] . ' AND fvo.option_value', $options['where']); } } $options['where'] = str_replace('course_title', 'c.title', $options['where']); $options['where'] = str_replace("( session_active = '0' )", '1=1', $options['where']); $options['where'] = str_replace(array("AND session_active = '1' )", " AND ( session_active = '1' )"), array(') GROUP BY s.name HAVING session_active = 1 ', " GROUP BY s.name HAVING session_active = 1 "), $options['where']); $options['where'] = str_replace(array("AND session_active = '0' )", " AND ( session_active = '0' )"), array(') GROUP BY s.name HAVING session_active = 0 ', " GROUP BY s.name HAVING session_active = '0' "), $options['where']); $where .= ' AND ' . $options['where']; } if (!empty($options['limit'])) { $where .= " LIMIT " . $options['limit']; } $query = "{$select} FROM {$tbl_session} s\n LEFT JOIN {$tbl_session_field_values} fv\n ON (fv.item_id = s.id)\n LEFT JOIN {$extraFieldTable} f\n ON f.id = fv.field_id\n LEFT JOIN {$tbl_session_field_options} fvo\n ON (fv.field_id = fvo.field_id)\n LEFT JOIN {$tbl_session_rel_course} src\n ON (src.session_id = s.id)\n LEFT JOIN {$tbl_course} c\n ON (src.c_id = c.id)\n LEFT JOIN {$tbl_session_category} sc\n ON (s.session_category_id = sc.id)\n INNER JOIN {$tbl_user} u\n ON (s.id_coach = u.user_id) " . $where; if (api_is_multiple_url_enabled()) { $table_access_url_rel_session = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION); $access_url_id = api_get_current_access_url_id(); if ($access_url_id != -1) { $where .= " AND ar.access_url_id = {$access_url_id} "; $query = "{$select}\n FROM {$tbl_session} s\n LEFT JOIN {$tbl_session_field_values} fv ON (fv.session_id = s.id)\n LEFT JOIN {$tbl_session_field_options} fvo ON (fv.field_id = fvo.field_id)\n LEFT JOIN {$tbl_session_rel_course} src ON (src.id_session = s.id)\n LEFT JOIN {$tbl_course} c ON (src.c_id = c.id)\n LEFT JOIN {$tbl_session_category} sc ON (s.session_category_id = sc.id)\n INNER JOIN {$tbl_user} u ON (s.id_coach = u.user_id)\n INNER JOIN {$table_access_url_rel_session} ar ON (ar.session_id = s.id)\n {$where}"; } } $query .= ") AS session_table"; if (!empty($options['order'])) { $query .= " ORDER BY " . $options['order']; } //error_log($query); //echo $query; $result = Database::query($query); $formatted_sessions = array(); if (Database::num_rows($result)) { $sessions = Database::store_result($result, 'ASSOC'); foreach ($sessions as $session) { $session_id = $session['id']; $session['name'] = Display::url($session['name'], "resume_session.php?id_session=" . $session['id']); $session['coach_name'] = Display::url($session['coach_name'], "user_information.php?user_id=" . $session['user_id']); if ($session['session_active'] == 1) { $session['session_active'] = Display::return_icon('accept.png', get_lang('Active'), array(), ICON_SIZE_SMALL); } else { $session['session_active'] = Display::return_icon('error.png', get_lang('Inactive'), array(), ICON_SIZE_SMALL); } $session = self::convert_dates_to_local($session); switch ($session['visibility']) { case SESSION_VISIBLE_READ_ONLY: //1 $session['visibility'] = get_lang('ReadOnly'); break; case SESSION_VISIBLE: //2 //2 case SESSION_AVAILABLE: //4 $session['visibility'] = get_lang('Visible'); break; case SESSION_INVISIBLE: //3 $session['visibility'] = api_ucfirst(get_lang('Invisible')); break; } // Cleaning double selects foreach ($session as $key => &$value) { if (isset($options_by_double[$key]) || isset($options_by_double[$key . '_second'])) { $options = explode('::', $value); } $original_key = $key; if (strpos($key, '_second') === false) { } else { $key = str_replace('_second', '', $key); } if (isset($options_by_double[$key])) { if (isset($options[0])) { if (isset($options_by_double[$key][$options[0]])) { if (strpos($original_key, '_second') === false) { $value = $options_by_double[$key][$options[0]]['option_display_text']; } else { $value = $options_by_double[$key][$options[1]]['option_display_text']; } } } } } // Magic filter if (isset($formatted_sessions[$session_id])) { $formatted_sessions[$session_id] = self::compareArraysToMerge($formatted_sessions[$session_id], $session); } else { $formatted_sessions[$session_id] = $session; } } } return $formatted_sessions; }
/** * get all the data for the sortable table of the reporting progress of all users and all the courses the user is subscribed to. * * @author Patrick Cool <*****@*****.**>, Ghent University, Belgium * @version Dokeos 1.8.6 * @since October 2008 */ public static function get_user_data_tracking_overview($from, $number_of_items, $column, $direction) { // database table definition $access_url_id = api_get_current_access_url_id(); $tbl_url_rel_user = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER); $main_user_table = Database::get_main_table(TABLE_MAIN_USER); $condition_multi_url = null; if (api_is_multiple_url_enabled()) { $condition_multi_url = ", {$tbl_url_rel_user} as url_user\n WHERE user.user_id=url_user.user_id AND access_url_id='{$access_url_id}'"; } global $export_csv; if ($export_csv) { $is_western_name_order = api_is_western_name_order(PERSON_NAME_DATA_EXPORT); } else { $is_western_name_order = api_is_western_name_order(); } $sql = "SELECT\n official_code AS col0,\n " . ($is_western_name_order ? "\n firstname AS col1,\n lastname AS col2,\n " : "\n lastname AS col1,\n firstname AS col2,\n ") . "username AS col3,\n user.user_id AS col4\n FROM\n {$main_user_table} as user {$condition_multi_url}\n "; $sql .= " ORDER BY col{$column} {$direction} "; $sql .= " LIMIT {$from},{$number_of_items}"; $result = Database::query($sql); $return = array(); while ($user = Database::fetch_row($result)) { $return[] = $user; } return $return; }
/** * The general coach (field: session.id_coach) * @param int $user_id user id * @return array */ public static function get_sessions_by_general_coach($user_id) { $session_table = Database::get_main_table(TABLE_MAIN_SESSION); $user_id = intval($user_id); // Session where we are general coach $sql = "SELECT DISTINCT * FROM $session_table WHERE id_coach = $user_id"; if (api_is_multiple_url_enabled()) { $tbl_session_rel_access_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION); $access_url_id = api_get_current_access_url_id(); if ($access_url_id != -1) { $sql = 'SELECT DISTINCT session.* FROM ' . $session_table . ' session INNER JOIN ' . $tbl_session_rel_access_url . ' session_rel_url ON (session.id = session_rel_url.session_id) WHERE id_coach = ' . $user_id . ' AND access_url_id = ' . $access_url_id; } } $sql .= ' ORDER by name'; $result = Database::query($sql); return Database::store_result($result, 'ASSOC'); }
/** * This function sends the actual password to the user * * @param int $user * @author Olivier Cauberghe <*****@*****.**>, Ghent University */ public static function send_password_to_user($user, $by_username = false) { $email_subject = "[" . api_get_setting('platform.site_name') . "] " . get_lang('LoginRequest'); // SUBJECT if ($by_username) { // Show only for lost password $user_account_list = self::get_user_account_list($user, false, $by_username); // BODY $email_to = $user['email']; } else { $user_account_list = self::get_user_account_list($user); // BODY $email_to = $user[0]['email']; } $portal_url = api_get_path(WEB_PATH); if (api_is_multiple_url_enabled()) { $access_url_id = api_get_current_access_url_id(); if ($access_url_id != -1) { $url = api_get_access_url($access_url_id); $portal_url = $url['url']; } } $email_body = get_lang('YourAccountParam') . " " . $portal_url . "\n\n{$user_account_list}"; // SEND MESSAGE $sender_name = api_get_person_name(api_get_setting('admin.administrator_name'), api_get_setting('admin.administrator_surname'), null, PERSON_NAME_EMAIL_ADDRESS); $email_admin = api_get_setting('admin.administrator_email'); if (api_mail_html('', $email_to, $email_subject, $email_body, $sender_name, $email_admin) == 1) { return get_lang('YourPasswordHasBeenReset'); } else { $admin_email = Display::encrypted_mailto_link(api_get_setting('admin.administrator_email'), api_get_person_name(api_get_setting('admin.administrator_name'), api_get_setting('admin.administrator_surname'))); return sprintf(get_lang('ThisPlatformWasUnableToSendTheEmailPleaseContactXForMoreInformation'), $admin_email); } }
/** * Function register_course to create a record in the course table of the main database * @param array Course details (see code for details) * @return int Created course ID * @todo use an array called $params instead of lots of params * @assert (null) === false */ public static function register_course($params) { global $error_msg, $firstExpirationDelay; $title = $params['title']; $code = $params['code']; $visual_code = $params['visual_code']; $directory = $params['directory']; $tutor_name = isset($params['tutor_name']) ? $params['tutor_name'] : null; //$description = $params['description']; $category_code = isset($params['course_category']) ? $params['course_category'] : ''; $course_language = isset($params['course_language']) && !empty($params['course_language']) ? $params['course_language'] : api_get_setting('language.platform_language'); $user_id = empty($params['user_id']) ? api_get_user_id() : intval($params['user_id']); $department_name = isset($params['department_name']) ? $params['department_name'] : null; $department_url = isset($params['department_url']) ? $params['department_url'] : null; $disk_quota = isset($params['disk_quota']) ? $params['disk_quota'] : null; if (!isset($params['visibility'])) { $default_course_visibility = api_get_setting('course.courses_default_creation_visibility'); if ($default_course_visibility != '') { $visibility = $default_course_visibility; } else { $visibility = COURSE_VISIBILITY_OPEN_PLATFORM; } } else { $visibility = $params['visibility']; } $subscribe = isset($params['subscribe']) ? intval($params['subscribe']) : ($visibility == COURSE_VISIBILITY_OPEN_PLATFORM ? 1 : 0); $unsubscribe = isset($params['unsubscribe']) ? intval($params['unsubscribe']) : 0; $expiration_date = isset($params['expiration_date']) ? $params['expiration_date'] : null; $teachers = isset($params['teachers']) ? $params['teachers'] : null; $status = isset($params['status']) ? $params['status'] : null; $TABLECOURSE = Database::get_main_table(TABLE_MAIN_COURSE); $TABLECOURSUSER = Database::get_main_table(TABLE_MAIN_COURSE_USER); $ok_to_register_course = true; // Check whether all the needed parameters are present. if (empty($code)) { $error_msg[] = 'courseSysCode is missing'; $ok_to_register_course = false; } if (empty($visual_code)) { $error_msg[] = 'courseScreenCode is missing'; $ok_to_register_course = false; } if (empty($directory)) { $error_msg[] = 'courseRepository is missing'; $ok_to_register_course = false; } if (empty($title)) { $error_msg[] = 'title is missing'; $ok_to_register_course = false; } if (empty($expiration_date)) { $expiration_date = api_get_utc_datetime(time() + $firstExpirationDelay); } else { $expiration_date = api_get_utc_datetime($expiration_date); } if ($visibility < 0 || $visibility > 4) { $error_msg[] = 'visibility is invalid'; $ok_to_register_course = false; } if (empty($disk_quota)) { $disk_quota = api_get_setting('document.default_document_quotum'); } $time = api_get_utc_datetime(); if (stripos($department_url, 'http://') === false && stripos($department_url, 'https://') === false) { $department_url = 'http://' . $department_url; } //just in case if ($department_url == 'http://') { $department_url = ''; } $course_id = 0; if ($ok_to_register_course) { // Here we must add 2 fields. $sql = "INSERT INTO " . $TABLECOURSE . " SET\n code = '" . Database::escape_string($code) . "',\n directory = '" . Database::escape_string($directory) . "',\n course_language = '" . Database::escape_string($course_language) . "',\n title = '" . Database::escape_string($title) . "',\n description = '" . self::lang2db(get_lang('CourseDescription')) . "',\n category_code = '" . Database::escape_string($category_code) . "',\n visibility = '" . $visibility . "',\n show_score = '1',\n disk_quota = '" . intval($disk_quota) . "',\n creation_date = '{$time}',\n expiration_date = '" . $expiration_date . "',\n last_edit = '{$time}',\n last_visit = NULL,\n tutor_name = '" . Database::escape_string($tutor_name) . "',\n department_name = '" . Database::escape_string($department_name) . "',\n department_url = '" . Database::escape_string($department_url) . "',\n subscribe = '" . intval($subscribe) . "',\n unsubscribe = '" . intval($unsubscribe) . "',\n visual_code = '" . Database::escape_string($visual_code) . "'"; Database::query($sql); $course_id = Database::insert_id(); if ($course_id) { $sort = api_max_sort_value('0', api_get_user_id()); // Default true $addTeacher = isset($params['add_user_as_teacher']) ? $params['add_user_as_teacher'] : true; if ($addTeacher) { $i_course_sort = CourseManager::userCourseSort($user_id, $code); if (!empty($user_id)) { $sql = "INSERT INTO " . $TABLECOURSUSER . " SET\n c_id = '" . $course_id . "',\n user_id = '" . intval($user_id) . "',\n status = '1',\n is_tutor = '0',\n sort = '" . $i_course_sort . "',\n user_course_cat = '0'"; Database::query($sql); } } if (!empty($teachers)) { if (!is_array($teachers)) { $teachers = array($teachers); } foreach ($teachers as $key) { //just in case if ($key == $user_id) { continue; } if (empty($key)) { continue; } $sql = "INSERT INTO " . $TABLECOURSUSER . " SET\n c_id = '" . Database::escape_string($course_id) . "',\n user_id = '" . Database::escape_string($key) . "',\n status = '1',\n is_tutor = '0',\n sort = '" . ($sort + 1) . "',\n user_course_cat = '0'"; Database::query($sql); } } // Adding the course to an URL. if (api_is_multiple_url_enabled()) { $url_id = 1; if (api_get_current_access_url_id() != -1) { $url_id = api_get_current_access_url_id(); } UrlManager::add_course_to_url($course_id, $url_id); } else { UrlManager::add_course_to_url($course_id, 1); } // Add event to the system log. $user_id = api_get_user_id(); Event::addEvent(LOG_COURSE_CREATE, LOG_COURSE_CODE, $code, api_get_utc_datetime(), $user_id, $course_id); $send_mail_to_admin = api_get_setting('course.send_email_to_admin_when_create_course'); // @todo Improve code to send to all current portal administrators. if ($send_mail_to_admin == 'true') { $siteName = api_get_setting('platform.site_name'); $recipient_email = api_get_setting('admin.administrator_email'); $recipient_name = api_get_person_name(api_get_setting('admin.administrator_name'), api_get_setting('admin.administrator_surname')); $iname = api_get_setting('platform.institution'); $subject = get_lang('NewCourseCreatedIn') . ' ' . $siteName . ' - ' . $iname; $message = get_lang('Dear') . ' ' . $recipient_name . ",\n\n" . get_lang('MessageOfNewCourseToAdmin') . ' ' . $siteName . ' - ' . $iname . "\n"; $message .= get_lang('CourseName') . ' ' . $title . "\n"; $message .= get_lang('Category') . ' ' . $category_code . "\n"; $message .= get_lang('Tutor') . ' ' . $tutor_name . "\n"; $message .= get_lang('Language') . ' ' . $course_language; $userInfo = api_get_user_info($user_id); $additionalParameters = array('smsType' => SmsPlugin::NEW_COURSE_BEEN_CREATED, 'userId' => $user_id, 'courseName' => $title, 'creatorUsername' => $userInfo['username']); api_mail_html($recipient_name, $recipient_email, $subject, $message, $siteName, $recipient_email, null, null, null, $additionalParameters); } } } return $course_id; }
static function get_coaches_by_keyword($tag) { $tbl_user = Database::get_main_table(TABLE_MAIN_USER); $order_clause = api_sort_by_first_name() ? ' ORDER BY firstname, lastname, username' : ' ORDER BY lastname, firstname, username'; $select = "SELECT user.user_id, lastname, firstname, username "; $sql = " {$select} FROM {$tbl_user} user WHERE status='1'"; $tag = Database::escape_string($tag); $where_condition = array(); if (!empty($tag)) { $condition = ' LIKE "%' . $tag . '%"'; $where_condition = array("firstname {$condition}", "lastname {$condition}", "username {$condition}"); $where_condition = ' AND (' . implode(' OR ', $where_condition) . ') '; } 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) { $sql = $select . ' 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 status = 1'; } } $sql .= $where_condition . $order_clause; $result = Database::query($sql); return Database::store_result($result, 'ASSOC'); }
/** * Get list of courses based on users of a group for a group admin * @param int $userId The user id * @return array */ public static function getCoursesFollowedByGroupAdmin($userId) { $coursesList = []; $courseTable = Database::get_main_table(TABLE_MAIN_COURSE); $courseUserTable = Database::get_main_table(TABLE_MAIN_COURSE_USER); $userGroup = new UserGroup(); $userIdList = $userGroup->getGroupUsersByUser($userId); if (empty($userIdList)) { return []; } $sql = "SELECT DISTINCT(c.id), c.title\n FROM {$courseTable} c\n INNER JOIN {$courseUserTable} cru ON c.id = cru.c_id\n WHERE (\n cru.user_id IN (" . implode(', ', $userIdList) . ")\n AND cru.relation_type = 0\n )"; if (api_is_multiple_url_enabled()) { $courseAccessUrlTable = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE); $accessUrlId = api_get_current_access_url_id(); if ($accessUrlId != -1) { $sql = "SELECT DISTINCT(c.id), c.title\n FROM {$courseTable} c\n INNER JOIN {$courseUserTable} cru ON c.id = cru.c_id\n INNER JOIN {$courseAccessUrlTable} crau ON c.id = crau.c_id\n WHERE crau.access_url_id = {$accessUrlId}\n AND (\n cru.id_user IN (" . implode(', ', $userIdList) . ") AND\n cru.relation_type = 0\n )"; } } $result = Database::query($sql); while ($row = Database::fetch_assoc($result)) { $coursesList[] = $row; } return $coursesList; }
/** * Get the HTML code for an announcement * @param int $announcementId The announcement ID * @param int $visibility The announcement visibility * @return string The HTML code */ public static function displayAnnouncement($announcementId, $visibility) { $selectedUserLanguage = Database::escape_string(api_get_interface_language()); $announcementTable = Database::get_main_table(TABLE_MAIN_SYSTEM_ANNOUNCEMENTS); $now = api_get_utc_datetime(); $whereConditions = ["(lang = ? OR lang IS NULL) " => $selectedUserLanguage, "AND (? >= date_start AND ? <= date_end) " => [$now, $now], "AND id = ? " => intval($announcementId)]; switch ($visibility) { case self::VISIBLE_GUEST: $whereConditions["AND visible_guest = ? "] = 1; break; case self::VISIBLE_STUDENT: $whereConditions["AND visible_student = ? "] = 1; break; case self::VISIBLE_TEACHER: $whereConditions["AND visible_teacher = ? "] = 1; break; } if (api_is_multiple_url_enabled()) { $whereConditions["AND access_url_id IN (1, ?) "] = api_get_current_access_url_id(); } $announcement = Database::select("*", $announcementTable, ["where" => $whereConditions, "order" => "date_start"], 'first'); $template = new Template(null, false, false); $template->assign('announcement', $announcement); return $template->fetch('default/announcement/view.tpl'); }
/** * Displays announcements as an slideshow * @param int $visible VISIBLE_GUEST, VISIBLE_STUDENT or VISIBLE_TEACHER * @param int $id The identifier of the announcement to display */ public static function display_announcements_slider($visible, $id = null) { $user_selected_language = Database::escape_string(api_get_interface_language()); $table = Database::get_main_table(TABLE_MAIN_SYSTEM_ANNOUNCEMENTS); $cut_size = 500; $now = api_get_utc_datetime(); $sql = "SELECT * FROM " . $table . "\n\t\t\t\tWHERE ( lang = '{$user_selected_language}' OR lang IS NULL) AND ( '{$now}' >= date_start AND '{$now}' <= date_end) "; switch ($visible) { case self::VISIBLE_GUEST: $sql .= " AND visible_guest = 1 "; break; case self::VISIBLE_STUDENT: $sql .= " AND visible_student = 1 "; break; case self::VISIBLE_TEACHER: $sql .= " AND visible_teacher = 1 "; break; } if (isset($id) && !empty($id)) { $id = intval($id); $sql .= " AND id = {$id} "; } if (api_is_multiple_url_enabled()) { $current_url_id = api_get_current_access_url_id(); $sql .= " AND access_url_id IN ('1', '{$current_url_id}') "; } $sql .= " ORDER BY date_start DESC"; $announcements = Database::query($sql); $html = ''; if (Database::num_rows($announcements) > 0) { $html .= Display::page_header(get_lang('SystemAnnouncements')); $html .= '<div id="container-slider" class="span6"><ul id="slider">'; while ($announcement = Database::fetch_object($announcements)) { $content = $announcement->content; $url = api_get_path(WEB_PUBLIC_PATH) . 'news/' . $announcement->id; if (empty($id)) { if (api_strlen(strip_tags($content)) > $cut_size) { $content = Text::cut($announcement->content, $cut_size) . ' ' . Display::url(get_lang('More'), $url); } } $html .= '<li><h2>' . $announcement->title . '</h2>' . $content . '</li>'; } $html .= '</ul></div>'; } return $html; }
/** * @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; }
/** * Send the invitation by mail. * * @param invitedUser - the userId (course user) or emailaddress of additional user * $param $invitation_code - the unique invitation code for the URL * @return void */ static function send_invitation_mail($invitedUser, $invitation_code, $invitation_title, $invitation_text) { global $_user, $_course, $_configuration; $portal_url = api_get_path(WEB_CODE_PATH); if (api_is_multiple_url_enabled()) { $access_url_id = api_get_current_access_url_id(); if ($access_url_id != -1) { $url = api_get_access_url($access_url_id); $portal_url = $url['url']; } } // Replacing the **link** part with a valid link for the user $survey_link = api_get_path(WEB_CODE_PATH) . 'survey/fillsurvey.php?course=' . $_course['code'] . '&invitationcode=' . $invitation_code; $text_link = '<a href="' . $survey_link . '">' . get_lang('ClickHereToAnswerTheSurvey') . "</a><br />\r\n<br />\r\n" . get_lang('OrCopyPasteTheFollowingUrl') . " <br />\r\n " . $survey_link; $replace_count = 0; $full_invitation_text = api_str_ireplace('**link**', $text_link, $invitation_text, $replace_count); if ($replace_count < 1) { $full_invitation_text = $full_invitation_text . "<br />\r\n<br />\r\n" . $text_link; } // Sending the mail $sender_name = api_get_person_name($_user['firstName'], $_user['lastName'], null, PERSON_NAME_EMAIL_ADDRESS); $sender_email = $_user['mail']; $sender_user_id = api_get_user_id(); $replyto = array(); if (api_get_setting('survey_email_sender_noreply') == 'noreply') { $noReply = api_get_setting('noreply_email_address'); if (!empty($noReply)) { $sender_name = $noReply; $sender_email = $noReply; $sender_user_id = null; } } // Optionally: finding the e-mail of the user if (is_numeric($invitedUser)) { MessageManager::send_message($invitedUser, $invitation_title, $full_invitation_text, null, null, null, null, null, null, $sender_user_id); } else { /** @todo check if the address is a valid email */ $recipient_email = $invitedUser; @api_mail_html(null, $recipient_email, $invitation_title, $full_invitation_text, $sender_name, $sender_email); } }
function WSCreateUsersPasswordCrypted($params) { global $_user, $_configuration; if (!WSHelperVerifyKey($params)) { return return_error(WS_ERROR_SECRET_KEY); } // database table definition $table_user = Database::get_main_table(TABLE_MAIN_USER); $t_uf = Database::get_main_table(TABLE_EXTRA_FIELD); $t_ufv = Database::get_main_table(TABLE_EXTRA_FIELD_VALUES); $users_params = $params['users']; $results = array(); $orig_user_id_value = array(); foreach ($users_params as $user_param) { $password = $user_param['password']; $encrypt_method = $user_param['encrypt_method']; $firstName = $user_param['firstname']; $lastName = $user_param['lastname']; $status = $user_param['status']; $email = $user_param['email']; $loginName = $user_param['loginname']; $official_code = $user_param['official_code']; $language = ''; $phone = ''; $picture_uri = ''; $auth_source = PLATFORM_AUTH_SOURCE; $expiration_date = ''; $active = 1; $hr_dept_id = 0; $extra = null; $original_user_id_name = $user_param['original_user_id_name']; $original_user_id_value = $user_param['original_user_id_value']; $orig_user_id_value[] = $user_param['original_user_id_value']; $extra_list = $user_param['extra']; $salt = ''; if (!empty($_configuration['password_encryption'])) { if ($_configuration['password_encryption'] === $encrypt_method) { if ($encrypt_method == 'md5' && !preg_match('/^[A-Fa-f0-9]{32}$/', $password)) { $msg = "Encryption {$encrypt_method} is invalid"; $results[] = $msg; continue; } else { if ($encrypt_method == 'sha1' && !preg_match('/^[A-Fa-f0-9]{40}$/', $password)) { $msg = "Encryption {$encrypt_method} is invalid"; $results[] = $msg; continue; } } } else { $msg = "This encryption {$encrypt_method} is not configured"; $results[] = $msg; continue; } } else { $msg = 'The chamilo setting $_configuration["password_encryption"] is not configured'; $results[] = $msg; continue; } if (is_array($extra_list) && count($extra_list) > 0) { foreach ($extra_list as $extra) { if ($extra['field_name'] == 'salt') { $salt = $extra['field_value']; break; } } } if (!empty($user_param['language'])) { $language = $user_param['language']; } if (!empty($user_param['phone'])) { $phone = $user_param['phone']; } if (!empty($user_param['expiration_date'])) { $expiration_date = $user_param['expiration_date']; } $extraFieldType = EntityExtraField::USER_FIELD_TYPE; // Check whether x_user_id exists into user_field_values table. $sql = "SELECT value as field_value,item_id as user_id\n FROM {$t_uf} uf, {$t_ufv} ufv\n WHERE\n uf.extra_field_type = {$extraFieldType}\n ufv.field_id=uf.id AND\n variable='{$original_user_id_name}' AND\n value ='{$original_user_id_value}'"; $res = Database::query($sql); $row = Database::fetch_row($res); $count_row = Database::num_rows($res); if ($count_row > 0) { // Check if user is not active. $sql = "SELECT user_id FROM {$table_user} WHERE user_id ='" . $row[1] . "' AND active= '0'"; $resu = Database::query($sql); $r_check_user = Database::fetch_row($resu); $count_check_user = Database::num_rows($resu); if ($count_check_user > 0) { $sql = "UPDATE {$table_user} SET\n lastname='" . Database::escape_string($lastName) . "',\n firstname='" . Database::escape_string($firstName) . "',\n username='******',"; if (!is_null($auth_source)) { $sql .= " auth_source='" . Database::escape_string($auth_source) . "',"; } $sql .= "\n password='******',\n email='" . Database::escape_string($email) . "',\n status='" . Database::escape_string($status) . "',\n official_code='" . Database::escape_string($official_code) . "',\n phone='" . Database::escape_string($phone) . "',\n expiration_date='" . Database::escape_string($expiration_date) . "',\n active='1',\n hr_dept_id=" . intval($hr_dept_id); $sql .= " WHERE user_id='" . $r_check_user[0] . "'"; Database::query($sql); if (is_array($extra_list) && count($extra_list) > 0) { foreach ($extra_list as $extra) { $extra_field_name = $extra['field_name']; $extra_field_value = $extra['field_value']; // Save the external system's id into user_field_value table. $res = UserManager::update_extra_field_value($r_check_user[0], $extra_field_name, $extra_field_value); } } $results[] = $r_check_user[0]; continue; } else { $results[] = 0; continue; // User id already exits. } } // Default language. if (empty($language)) { $language = api_get_setting('platformLanguage'); } if (!empty($_user['user_id'])) { $creator_id = $_user['user_id']; } else { $creator_id = ''; } // First check wether the login already exists if (!UserManager::is_username_available($loginName)) { if (api_set_failure('login-pass already taken')) { $results[] = 0; continue; } } $sql = "INSERT INTO {$table_user} SET\n lastname = '" . Database::escape_string(trim($lastName)) . "',\n firstname = '" . Database::escape_string(trim($firstName)) . "',\n username = '******',\n status = '" . Database::escape_string($status) . "',\n password = '******',\n email = '" . Database::escape_string($email) . "',\n official_code = '" . Database::escape_string($official_code) . "',\n picture_uri = '" . Database::escape_string($picture_uri) . "',\n creator_id = '" . Database::escape_string($creator_id) . "',\n auth_source = '" . Database::escape_string($auth_source) . "',\n phone = '" . Database::escape_string($phone) . "',\n language = '" . Database::escape_string($language) . "',\n registration_date = now(),\n expiration_date = '" . Database::escape_string($expiration_date) . "',\n hr_dept_id = '" . Database::escape_string($hr_dept_id) . "',\n active = '" . Database::escape_string($active) . "'"; $result = Database::query($sql); if ($result) { //echo "id returned"; $return = Database::insert_id(); $sql = "UPDATE {$table_user} SET user_id = id WHERE id = {$return}"; Database::query($sql); if (api_is_multiple_url_enabled()) { if (api_get_current_access_url_id() != -1) { UrlManager::add_user_to_url($return, api_get_current_access_url_id()); } else { UrlManager::add_user_to_url($return, 1); } } else { // We add by default the access_url_user table with access_url_id = 1 UrlManager::add_user_to_url($return, 1); } // Save new fieldlabel into user_field table. $field_id = UserManager::create_extra_field($original_user_id_name, 1, $original_user_id_name, ''); // Save the remote system's id into user_field_value table. UserManager::update_extra_field_value($return, $original_user_id_name, $original_user_id_value); if (is_array($extra_list) && count($extra_list) > 0) { foreach ($extra_list as $extra) { $extra_field_name = $extra['field_name']; $extra_field_value = $extra['field_value']; // Save new fieldlabel into user_field table. $field_id = UserManager::create_extra_field($extra_field_name, 1, $extra_field_name, ''); // Save the external system's id into user_field_value table. UserManager::update_extra_field_value($return, $extra_field_name, $extra_field_value); } } } else { $results[] = 0; continue; } $results[] = $return; } // end principal foreach $count_results = count($results); $output = array(); for ($i = 0; $i < $count_results; $i++) { $output[] = array('original_user_id_value' => $orig_user_id_value[$i], 'result' => $results[$i]); } return $output; }
/** * Get the users to display on the current page. */ function get_user_data($from, $number_of_items, $column, $direction) { global $_configuration; $url_access_id = api_get_current_access_url_id(); $course_code = api_get_course_id(); $courseId = api_get_course_int_id(); $session_id = api_get_session_id(); // Database table definitions $user_table = Database::get_main_table(TABLE_MAIN_USER); $course_user_table = Database::get_main_table(TABLE_MAIN_COURSE_USER); $tbl_session_rel_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER); $table_user_field_values = Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES); $tbl_url_rel_user = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER); // adding teachers $is_western_name_order = api_is_western_name_order(); if (api_get_setting('show_email_addresses') == 'true') { $select_fields = "u.user_id AS col0,\n u.official_code AS col1,\n " . ($is_western_name_order ? "u.firstname AS col2,\n u.lastname AS col3," : "u.lastname AS col2,\n u.firstname AS col3,") . "\n u.email \t AS col4,\n u.active AS col5,\n u.user_id AS col6"; } else { $select_fields = "u.user_id AS col0,\n u.official_code AS col1,\n " . ($is_western_name_order ? "u.firstname AS col2,\n u.lastname AS col3," : "u.lastname AS col2,\n u.firstname AS col3,") . "\n u.active AS col4,\n u.user_id AS col5"; } if (isset($_REQUEST['type']) && $_REQUEST['type'] == 'teacher') { // adding a teacher through a session if (!empty($session_id)) { $sql = "SELECT {$select_fields}\n\t\t\t\t\tFROM {$user_table} u\n\t\t\t\t\tLEFT JOIN {$tbl_session_rel_course_user} cu on u.user_id = cu.id_user AND cu.c_id ='" . $courseId . "' AND id_session ='" . $session_id . "'\n INNER JOIN {$tbl_url_rel_user} as url_rel_user ON (url_rel_user.user_id = u.user_id) "; // applying the filter of the additional user profile fields if (isset($_GET['subscribe_user_filter_value']) and !empty($_GET['subscribe_user_filter_value']) and api_get_setting('ProfilingFilterAddingUsers') == 'true') { $field_identification = explode('*', $_GET['subscribe_user_filter_value']); $sql .= "\n\t\t\t\t\tLEFT JOIN {$table_user_field_values} field_values\n\t\t\t\t\t\tON field_values.user_id = u.user_id\n\t\t\t\t\tWHERE cu.id_user IS NULL AND u.status=1 AND (u.official_code <> 'ADMIN' OR u.official_code IS NULL)\n\t\t\t\t\t\tAND field_values.field_id = '" . Database::escape_string($field_identification[0]) . "'\n\t\t\t\t\t\tAND field_values.field_value = '" . Database::escape_string($field_identification[1]) . "'"; } else { $sql .= "WHERE cu.id_user IS NULL AND u.status=1 AND (u.official_code <> 'ADMIN' OR u.official_code IS NULL) "; } $sql .= " AND access_url_id= {$url_access_id}"; } else { // adding a teacher NOT through a session $sql = "SELECT {$select_fields}\n FROM {$user_table} u\n LEFT JOIN {$course_user_table} cu on u.user_id = cu.user_id and cu.c_id = '" . $courseId . "'"; // applying the filter of the additional user profile fields if (isset($_GET['subscribe_user_filter_value']) and !empty($_GET['subscribe_user_filter_value']) and api_get_setting('ProfilingFilterAddingUsers') == 'true') { $field_identification = explode('*', $_GET['subscribe_user_filter_value']); $sql .= "\n\t\t\t\t\t\tLEFT JOIN {$table_user_field_values} field_values\n\t\t\t\t\t\t\tON field_values.user_id = u.user_id\n\t\t\t\t\t\tWHERE cu.user_id IS NULL AND u.status<>" . DRH . "\n\t\t\t\t\t\t\tAND field_values.field_id = '" . Database::escape_string($field_identification[0]) . "'\n\t\t\t\t\t\t\tAND field_values.field_value = '" . Database::escape_string($field_identification[1]) . "'"; } else { $sql .= "WHERE cu.user_id IS NULL AND u.status<>" . DRH . " "; } // adding a teacher NOT trough a session on a portal with multiple URLs if ($_configuration['multiple_access_urls']) { if ($url_access_id != -1) { $sql = "SELECT {$select_fields}\n\t\t\t\t\t\tFROM {$user_table} u\n\t\t\t\t\t\tLEFT JOIN {$course_user_table} cu on u.user_id = cu.user_id and cu.c_id ='" . $courseId . "'\n\t\t\t\t\t\tINNER JOIN {$tbl_url_rel_user} as url_rel_user ON (url_rel_user.user_id = u.user_id) "; // applying the filter of the additional user profile fields if (isset($_GET['subscribe_user_filter_value']) and !empty($_GET['subscribe_user_filter_value']) and api_get_setting('ProfilingFilterAddingUsers') == 'true') { $field_identification = explode('*', $_GET['subscribe_user_filter_value']); $sql .= "\n\t\t\t\t\t\t\tLEFT JOIN {$table_user_field_values} field_values\n\t\t\t\t\t\t\t\tON field_values.user_id = u.user_id\n\t\t\t\t\t\t\tWHERE cu.user_id IS NULL AND u.status<>" . DRH . "\n\t\t\t\t\t\t\t\tAND field_values.field_id = '" . Database::escape_string($field_identification[0]) . "'\n\t\t\t\t\t\t\t\tAND field_values.field_value = '" . Database::escape_string($field_identification[1]) . "'"; } else { $sql .= "WHERE cu.user_id IS NULL AND u.status<>" . DRH . " AND access_url_id= {$url_access_id} "; } } } } } else { // adding a student if (!empty($session_id)) { $sql = "SELECT {$select_fields}\n FROM {$user_table} u\n LEFT JOIN {$tbl_session_rel_course_user} cu ON u.user_id = cu.id_user AND cu.c_id ='" . $courseId . "' AND id_session ='" . $session_id . "' "; if (api_is_multiple_url_enabled()) { $sql .= " INNER JOIN {$tbl_url_rel_user} as url_rel_user ON (url_rel_user.user_id = u.user_id) "; } // applying the filter of the additional user profile fields if (isset($_GET['subscribe_user_filter_value']) and !empty($_GET['subscribe_user_filter_value'])) { $field_identification = explode('*', $_GET['subscribe_user_filter_value']); $sql .= "\n LEFT JOIN {$table_user_field_values} field_values\n ON field_values.user_id = u.user_id\n WHERE cu.id_user IS NULL AND u.status<>" . DRH . " AND (u.official_code <> 'ADMIN' OR u.official_code IS NULL)\n AND field_values.field_id = '" . Database::escape_string($field_identification[0]) . "'\n AND field_values.field_value = '" . Database::escape_string($field_identification[1]) . "'"; } else { $sql .= "WHERE cu.id_user IS NULL AND u.status<>" . DRH . " AND (u.official_code <> 'ADMIN' OR u.official_code IS NULL) "; } if (api_is_multiple_url_enabled()) { $sql .= "AND access_url_id = {$url_access_id}"; } } else { $sql = "SELECT {$select_fields}\n FROM {$user_table} u\n LEFT JOIN {$course_user_table} cu on u.user_id = cu.user_id and cu.c_id ='" . $courseId . "'"; // applying the filter of the additional user profile fields if (isset($_GET['subscribe_user_filter_value']) and !empty($_GET['subscribe_user_filter_value'])) { $field_identification = explode('*', $_GET['subscribe_user_filter_value']); $sql .= "\n\t\t\t\t\tLEFT JOIN {$table_user_field_values} field_values\n\t\t\t\t\t\tON field_values.user_id = u.user_id\n\t\t\t\t\tWHERE cu.user_id IS NULL AND u.status<>" . DRH . "\n\t\t\t\t\t\tAND field_values.field_id = '" . Database::escape_string($field_identification[0]) . "'\n\t\t\t\t\t\tAND field_values.field_value = '" . Database::escape_string($field_identification[1]) . "'"; } else { $sql .= "WHERE cu.user_id IS NULL AND u.status<>" . DRH . " "; } //showing only the courses of the current Chamilo access_url_id if (api_is_multiple_url_enabled()) { if ($url_access_id != -1) { $sql = "SELECT {$select_fields}\n\t\t\t\t\t\tFROM {$user_table} u\n\t\t\t\t\t\tLEFT JOIN {$course_user_table} cu on u.user_id = cu.user_id and cu.c_id ='" . $courseId . "'\n\t\t\t\t\t\tINNER JOIN {$tbl_url_rel_user} as url_rel_user\n\t\t\t\t\t\tON (url_rel_user.user_id = u.user_id) "; // applying the filter of the additional user profile fields if (isset($_GET['subscribe_user_filter_value']) and !empty($_GET['subscribe_user_filter_value']) and api_get_setting('ProfilingFilterAddingUsers') == 'true') { $field_identification = explode('*', $_GET['subscribe_user_filter_value']); $sql .= "\n\t\t\t\t\t\t\tLEFT JOIN {$table_user_field_values} field_values\n\t\t\t\t\t\t\t\tON field_values.user_id = u.user_id\n\t\t\t\t\t\t\tWHERE cu.user_id IS NULL AND u.status<>" . DRH . "\n\t\t\t\t\t\t\t\tAND field_values.field_id = '" . Database::escape_string($field_identification[0]) . "'\n\t\t\t\t\t\t\t\tAND field_values.field_value = '" . Database::escape_string($field_identification[1]) . "' AND access_url_id= {$url_access_id} "; } else { $sql .= "WHERE cu.user_id IS NULL AND u.status<>" . DRH . " AND access_url_id= {$url_access_id} "; } } } } } // adding additional WHERE statements to the SQL for the search functionality if (isset($_REQUEST['keyword'])) { $keyword = Database::escape_string(trim($_REQUEST['keyword'])); $sql .= " AND (firstname LIKE '%" . $keyword . "%' OR lastname LIKE '%" . $keyword . "%' OR email LIKE '%" . $keyword . "%' OR username LIKE '%" . $keyword . "%' OR official_code LIKE '%" . $keyword . "%')"; if (api_get_setting('ProfilingFilterAddingUsers') == 'true') { // we also want to search for users who have something in their profile fields that matches the keyword $additional_users = search_additional_profile_fields($keyword); } // getting all the users of the course (to make sure that we do not display users that are already in the course) if (!empty($session_id)) { $a_course_users = CourseManager::get_user_list_from_course_code($course_code, $session_id); } else { $a_course_users = CourseManager::get_user_list_from_course_code($course_code, 0); } foreach ($a_course_users as $user_id => $course_user) { $users_of_course[] = $course_user['user_id']; } } $sql .= " AND u.status != " . ANONYMOUS . " "; // Sorting and pagination (used by the sortable table) $sql .= " ORDER BY col{$column} {$direction} "; $sql .= " LIMIT {$from},{$number_of_items}"; $res = Database::query($sql); $users = array(); while ($user = Database::fetch_row($res)) { $users[] = $user; $_SESSION['session_user_id'][] = $user[0]; if ($is_western_name_order) { $_SESSION['session_user_name'][] = api_get_person_name($user[2], $user[3]); } else { $_SESSION['session_user_name'][] = api_get_person_name($user[3], $user[2]); } } // adding additional users based on the search on the additional profile fields if (isset($_REQUEST['keyword'])) { if (isset($additional_users) && is_array($additional_users)) { foreach ($additional_users as $additional_user_key => $additional_user_value) { if (!in_array($additional_user_key, $_SESSION['session_user_id']) and !in_array($additional_user_key, $users_of_course)) { $users[] = array($additional_user_value['col0'], $additional_user_value['col1'], $additional_user_value['col2'] . '*', $additional_user_value['col3'] . '*', $additional_user_value['col4'], $additional_user_value['col5'], $additional_user_value['col6']); } } } } return $users; }
/** * Print the number of users that didn't login for a certain period of time */ static function print_users_not_logged_in_stats() { $total_logins = array(); $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LOGIN); $access_url_rel_user_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER); $current_url_id = api_get_current_access_url_id(); $total = self::count_users(); if (api_is_multiple_url_enabled()) { $table_url = ", {$access_url_rel_user_table}"; $where_url = " AND login_user_id=user_id AND access_url_id='" . $current_url_id . "'"; } else { $table_url = ''; $where_url = ''; } $sql[get_lang('Thisday')] = "SELECT count(distinct(login_user_id)) AS number " . " FROM {$table} {$table_url} " . " WHERE DATE_ADD(login_date, INTERVAL 1 DAY) >= NOW() {$where_url}"; $sql[get_lang('Last7days')] = "SELECT count(distinct(login_user_id)) AS number " . " FROM {$table} {$table_url} " . " WHERE DATE_ADD(login_date, INTERVAL 7 DAY) >= NOW() {$where_url}"; $sql[get_lang('Last31days')] = "SELECT count(distinct(login_user_id)) AS number " . " FROM {$table} {$table_url} " . " WHERE DATE_ADD(login_date, INTERVAL 31 DAY) >= NOW() {$where_url}"; $sql[sprintf(get_lang('LastXMonths'), 6)] = "SELECT count(distinct(login_user_id)) AS number " . " FROM {$table} {$table_url} " . " WHERE DATE_ADD(login_date, INTERVAL 6 MONTH) >= NOW() {$where_url}"; $sql[get_lang('NeverConnected')] = "SELECT count(distinct(login_user_id)) AS number " . " FROM {$table} {$table_url} WHERE 1=1 {$where_url}"; foreach ($sql as $index => $query) { $res = Database::query($query); $obj = Database::fetch_object($res); $r = $total - $obj->number; $total_logins[$index] = $r < 0 ? 0 : $r; } Statistics::print_stats(get_lang('StatsUsersDidNotLoginInLastPeriods'), $total_logins, false); }
/** * Display list of courses in a category. * (for anonymous users) * * @version 1.1 * @author Patrick Cool <*****@*****.**>, Ghent University - refactoring and code cleaning * @author Julio Montoya <*****@*****.**>, Beeznest template modifs * @assert () !== 0 */ public function return_courses_in_categories() { $result = ''; $stok = Security::get_token(); // Initialization. $user_identified = api_get_user_id() > 0 && !api_is_anonymous(); $web_course_path = api_get_path(WEB_COURSE_PATH); $category = Database::escape_string($_GET['category']); $setting_show_also_closed_courses = api_get_setting('show_closed_courses') == 'true'; // Database table definitions. $main_course_table = Database::get_main_table(TABLE_MAIN_COURSE); $main_category_table = Database::get_main_table(TABLE_MAIN_CATEGORY); // Get list of courses in category $category. $sql_get_course_list = "SELECT * FROM {$main_course_table} cours\n WHERE category_code = '" . Database::escape_string($_GET['category']) . "'\n ORDER BY title, UPPER(visual_code)"; // Showing only the courses of the current access_url_id. if (api_is_multiple_url_enabled()) { $url_access_id = api_get_current_access_url_id(); if ($url_access_id != -1) { $tbl_url_rel_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE); $sql_get_course_list = "SELECT * FROM {$main_course_table} as course 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} AND category_code = '" . Database::escape_string($_GET['category']) . "' ORDER BY title, UPPER(visual_code)"; } } // Removed: AND cours.visibility='".COURSE_VISIBILITY_OPEN_WORLD."' $sql_result_courses = Database::query($sql_get_course_list); while ($course_result = Database::fetch_array($sql_result_courses)) { $course_list[] = $course_result; } $platform_visible_courses = ''; // $setting_show_also_closed_courses if ($user_identified) { if ($setting_show_also_closed_courses) { $platform_visible_courses = ''; } else { $platform_visible_courses = " AND (t3.visibility='" . COURSE_VISIBILITY_OPEN_WORLD . "' OR t3.visibility='" . COURSE_VISIBILITY_OPEN_PLATFORM . "' )"; } } else { if ($setting_show_also_closed_courses) { $platform_visible_courses = ''; } else { $platform_visible_courses = " AND (t3.visibility='" . COURSE_VISIBILITY_OPEN_WORLD . "' )"; } } $sqlGetSubCatList = "\n SELECT t1.name,t1.code,t1.parent_id,t1.children_count,COUNT(DISTINCT t3.code) AS nbCourse\n FROM {$main_category_table} t1\n LEFT JOIN {$main_category_table} t2 ON t1.code=t2.parent_id\n LEFT JOIN {$main_course_table} t3 ON (t3.category_code=t1.code {$platform_visible_courses})\n WHERE t1.parent_id " . (empty($category) ? "IS NULL" : "='{$category}'") . "\n GROUP BY t1.name,t1.code,t1.parent_id,t1.children_count ORDER BY t1.tree_pos, t1.name"; // Showing only the category of courses of the current access_url_id if (api_is_multiple_url_enabled()) { $url_access_id = api_get_current_access_url_id(); if ($url_access_id != -1) { $tbl_url_rel_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE); $sqlGetSubCatList = "\n SELECT t1.name,t1.code,t1.parent_id,t1.children_count,COUNT(DISTINCT t3.code) AS nbCourse\n FROM {$main_category_table} t1\n LEFT JOIN {$main_category_table} t2 ON t1.code=t2.parent_id\n LEFT JOIN {$main_course_table} t3 ON (t3.category_code=t1.code {$platform_visible_courses})\n INNER JOIN {$tbl_url_rel_course} as url_rel_course\n ON (url_rel_course.c_id = t3.id)\n WHERE access_url_id = {$url_access_id} AND t1.parent_id " . (empty($category) ? "IS NULL" : "='{$category}'") . "\n GROUP BY t1.name,t1.code,t1.parent_id,t1.children_count ORDER BY t1.tree_pos, t1.name"; } } $resCats = Database::query($sqlGetSubCatList); $thereIsSubCat = false; if (Database::num_rows($resCats) > 0) { $htmlListCat = Display::page_header(get_lang('CatList')); $htmlListCat .= '<ul>'; while ($catLine = Database::fetch_array($resCats)) { if ($catLine['code'] != $category) { $category_has_open_courses = $this->category_has_open_courses($catLine['code']); if ($category_has_open_courses) { // The category contains courses accessible to anonymous visitors. $htmlListCat .= '<li>'; $htmlListCat .= '<a href="' . api_get_self() . '?category=' . $catLine['code'] . '">' . $catLine['name'] . '</a>'; if (api_get_setting('show_number_of_courses') == 'true') { $htmlListCat .= ' (' . $catLine['nbCourse'] . ' ' . get_lang('Courses') . ')'; } $htmlListCat .= "</li>"; $thereIsSubCat = true; } elseif ($catLine['children_count'] > 0) { // The category has children, subcategories. $htmlListCat .= '<li>'; $htmlListCat .= '<a href="' . api_get_self() . '?category=' . $catLine['code'] . '">' . $catLine['name'] . '</a>'; $htmlListCat .= "</li>"; $thereIsSubCat = true; } elseif (api_get_setting('show_empty_course_categories') == 'true') { $htmlListCat .= '<li>'; $htmlListCat .= $catLine['name']; $htmlListCat .= "</li>"; $thereIsSubCat = true; } // Else don't set thereIsSubCat to true to avoid printing things if not requested. } else { $htmlTitre = '<p>'; if (api_get_setting('show_back_link_on_top_of_tree') == 'true') { $htmlTitre .= '<a href="' . api_get_self() . '"><< ' . get_lang('BackToHomePage') . '</a>'; } if (!is_null($catLine['parent_id']) || api_get_setting('show_back_link_on_top_of_tree') != 'true' && !is_null($catLine['code'])) { $htmlTitre .= '<a href="' . api_get_self() . '?category=' . $catLine['parent_id'] . '"><< ' . get_lang('Up') . '</a>'; } $htmlTitre .= "</p>"; if ($category != "" && !is_null($catLine['code'])) { $htmlTitre .= '<h3>' . $catLine['name'] . "</h3>"; } else { $htmlTitre .= '<h3>' . get_lang('Categories') . "</h3>"; } } } $htmlListCat .= "</ul>"; } $result .= $htmlTitre; if ($thereIsSubCat) { $result .= $htmlListCat; } while ($categoryName = Database::fetch_array($resCats)) { $result .= '<h3>' . $categoryName['name'] . "</h3>\n"; } $numrows = Database::num_rows($sql_result_courses); $courses_list_string = ''; $courses_shown = 0; if ($numrows > 0) { $courses_list_string .= Display::page_header(get_lang('CourseList')); $courses_list_string .= "<ul>"; if (api_get_user_id()) { $courses_of_user = $this->get_courses_of_user(api_get_user_id()); } foreach ($course_list as $course) { // $setting_show_also_closed_courses if (!$setting_show_also_closed_courses) { // If we do not show the closed courses // we only show the courses that are open to the world (to everybody) // and the courses that are open to the platform (if the current user is a registered user. if ($user_identified && $course['visibility'] == COURSE_VISIBILITY_OPEN_PLATFORM || $course['visibility'] == COURSE_VISIBILITY_OPEN_WORLD) { $courses_shown++; $courses_list_string .= "<li>\n"; $courses_list_string .= '<a href="' . $web_course_path . $course['directory'] . '/">' . $course['title'] . '</a><br />'; $course_details = array(); if (api_get_setting('display_coursecode_in_courselist') == 'true') { $course_details[] = $course['visual_code']; } if (api_get_setting('display_teacher_in_courselist') == 'true') { $course_details[] = $course['tutor_name']; } if (api_get_setting('show_different_course_language') == 'true' && $course['course_language'] != api_get_setting('platformLanguage')) { $course_details[] = $course['course_language']; } $courses_list_string .= implode(' - ', $course_details); $courses_list_string .= "</li>\n"; } } else { // We DO show the closed courses. // The course is accessible if (link to the course homepage): // 1. the course is open to the world (doesn't matter if the user is logged in or not): $course['visibility'] == COURSE_VISIBILITY_OPEN_WORLD); // 2. the user is logged in and the course is open to the world or open to the platform: ($user_identified && $course['visibility'] == COURSE_VISIBILITY_OPEN_PLATFORM); // 3. the user is logged in and the user is subscribed to the course and the course visibility is not COURSE_VISIBILITY_CLOSED; // 4. the user is logged in and the user is course admin of te course (regardless of the course visibility setting); // 5. the user is the platform admin api_is_platform_admin(). // $courses_shown++; $courses_list_string .= "<li>\n"; if ($course['visibility'] == COURSE_VISIBILITY_OPEN_WORLD || $user_identified && $course['visibility'] == COURSE_VISIBILITY_OPEN_PLATFORM || $user_identified && key_exists($course['code'], $courses_of_user) && $course['visibility'] != COURSE_VISIBILITY_CLOSED || $courses_of_user[$course['code']]['status'] == '1' || api_is_platform_admin()) { $courses_list_string .= '<a href="' . $web_course_path . $course['directory'] . '/">'; } $courses_list_string .= $course['title']; if ($course['visibility'] == COURSE_VISIBILITY_OPEN_WORLD || $user_identified && $course['visibility'] == COURSE_VISIBILITY_OPEN_PLATFORM || $user_identified && key_exists($course['code'], $courses_of_user) && $course['visibility'] != COURSE_VISIBILITY_CLOSED || $courses_of_user[$course['code']]['status'] == '1' || api_is_platform_admin()) { $courses_list_string .= '</a><br />'; } $course_details = array(); if (api_get_setting('display_coursecode_in_courselist') == 'true') { $course_details[] = $course['visual_code']; } // if (api_get_setting('display_coursecode_in_courselist') == 'true' && api_get_setting('display_teacher_in_courselist') == 'true') { // $courses_list_string .= ' - '; // } if (api_get_setting('display_teacher_in_courselist') == 'true') { $course_details[] = $course['tutor_name']; } if (api_get_setting('show_different_course_language') == 'true' && $course['course_language'] != api_get_setting('platformLanguage')) { $course_details[] = $course['course_language']; } if (api_get_setting('show_different_course_language') == 'true' && $course['course_language'] != api_get_setting('platformLanguage')) { $course_details[] = $course['course_language']; } $courses_list_string .= implode(' - ', $course_details); // We display a subscription link if: // 1. it is allowed to register for the course and if the course is not already in the courselist of the user and if the user is identiefied // 2. if ($user_identified && !key_exists($course['code'], $courses_of_user)) { if ($course['subscribe'] == '1') { $courses_list_string .= '<form action="main/auth/courses.php?action=subscribe&category=' . Security::remove_XSS($_GET['category']) . '" method="post">'; $courses_list_string .= '<input type="hidden" name="sec_token" value="' . $stok . '">'; $courses_list_string .= '<input type="hidden" name="subscribe" value="' . $course['code'] . '" />'; $courses_list_string .= '<input type="image" name="unsub" src="main/img/enroll.gif" alt="' . get_lang('Subscribe') . '" />' . get_lang('Subscribe') . '</form>'; } else { $courses_list_string .= '<br />' . get_lang('SubscribingNotAllowed'); } } $courses_list_string .= "</li>"; } //end else } // end foreach $courses_list_string .= "</ul>"; } if ($courses_shown > 0) { // Only display the list of courses and categories if there was more than // 0 courses visible to the world (we're in the anonymous list here). $result .= $courses_list_string; } if ($category != '') { $result .= '<p><a href="' . api_get_self() . '"> ' . Display::return_icon('back.png', get_lang('BackToHomePage')) . get_lang('BackToHomePage') . '</a></p>'; } return $result; }