/** * Return user info array of all users registered in a course * This only returns the users that are registered in this actual course, not linked courses. * @param string $course_code * @param int $session_id * @param string $limit * @param string $order_by the field to order the users by. * Valid values are 'lastname', 'firstname', 'username', 'email', 'official_code' OR a part of a SQL statement * that starts with ORDER BY ... * @param null $filter_by_status if using the session_id: 0 or 2 (student, coach), * if using session_id = 0 STUDENT or COURSEMANAGER * @param null $return_count * @param bool $add_reports * @param bool $resumed_report * @param array $extra_field * @param array $courseCodeList * @param array $userIdList * @param string $filterByActive * @param array $sessionIdList * @return array|int */ public static function get_user_list_from_course_code($course_code = null, $session_id = 0, $limit = null, $order_by = null, $filter_by_status = null, $return_count = null, $add_reports = false, $resumed_report = false, $extra_field = array(), $courseCodeList = array(), $userIdList = array(), $filterByActive = null, $sessionIdList = array()) { $course_table = Database::get_main_table(TABLE_MAIN_COURSE); $sessionTable = Database::get_main_table(TABLE_MAIN_SESSION); $session_id = intval($session_id); $course_code = Database::escape_string($course_code); $courseInfo = api_get_course_info($course_code); $courseId = 0; if (!empty($courseInfo)) { $courseId = $courseInfo['real_id']; } $where = array(); if (empty($order_by)) { $order_by = 'user.lastname, user.firstname'; if (api_is_western_name_order()) { $order_by = 'user.firstname, user.lastname'; } } // if the $order_by does not contain 'ORDER BY' // we have to check if it is a valid field that can be sorted on if (!strstr($order_by, 'ORDER BY')) { if (!empty($order_by)) { $order_by = 'ORDER BY ' . $order_by; } else { $order_by = ''; } } $filter_by_status_condition = null; if (!empty($session_id) || !empty($sessionIdList)) { $sql = 'SELECT DISTINCT user.user_id, user.email, session_course_user.status as status_session, session_id, user.*, course.*, session.name as session_name '; if ($return_count) { $sql = " SELECT COUNT(user.user_id) as count"; } $sessionCondition = " session_course_user.session_id = {$session_id}"; if (!empty($sessionIdList)) { $sessionIdListTostring = implode("','", array_map('intval', $sessionIdList)); $sessionCondition = " session_course_user.session_id IN ('{$sessionIdListTostring}') "; } $courseCondition = " course.id = {$courseId}"; if (!empty($courseCodeList)) { $courseCodeListForSession = array_map(array('Database', 'escape_string'), $courseCodeList); $courseCodeListForSession = implode('","', $courseCodeListForSession); $courseCondition = ' course.code IN ("' . $courseCodeListForSession . '") '; } $sql .= ' FROM ' . Database::get_main_table(TABLE_MAIN_USER) . ' as user '; $sql .= " LEFT JOIN " . Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER) . " as session_course_user\n ON\n user.user_id = session_course_user.user_id AND\n {$sessionCondition}\n INNER JOIN {$course_table} course ON session_course_user.c_id = course.id AND\n {$courseCondition}\n INNER JOIN {$sessionTable} session ON session_course_user.session_id = session.id\n "; $where[] = ' session_course_user.c_id IS NOT NULL '; // 2 = coach // 0 = student if (isset($filter_by_status)) { $filter_by_status = intval($filter_by_status); $filter_by_status_condition = " session_course_user.status = {$filter_by_status} AND "; } } else { if ($return_count) { $sql = " SELECT COUNT(*) as count"; if ($resumed_report) { //$sql = " SELECT count(field_id) "; } } else { if (empty($course_code)) { $sql = 'SELECT DISTINCT course.title, course.code, course_rel_user.status as status_rel, user.user_id, user.email, course_rel_user.is_tutor, user.* '; } else { $sql = 'SELECT DISTINCT course_rel_user.status as status_rel, user.user_id, user.email, course_rel_user.is_tutor, user.* '; } } $sql .= ' FROM ' . Database::get_main_table(TABLE_MAIN_USER) . ' as user '; $sql .= ' LEFT JOIN ' . Database::get_main_table(TABLE_MAIN_COURSE_USER) . ' as course_rel_user ON user.user_id = course_rel_user.user_id AND course_rel_user.relation_type <> ' . COURSE_RELATION_TYPE_RRHH . ' '; $sql .= " INNER JOIN {$course_table} course ON course_rel_user.c_id = course.id "; if (!empty($course_code)) { $sql .= ' AND course_rel_user.c_id="' . $courseId . '"'; } $where[] = ' course_rel_user.c_id IS NOT NULL '; if (isset($filter_by_status) && is_numeric($filter_by_status)) { $filter_by_status = intval($filter_by_status); $filter_by_status_condition = " course_rel_user.status = {$filter_by_status} AND "; } } $multiple_access_url = api_get_multiple_access_url(); if ($multiple_access_url) { $sql .= ' LEFT JOIN ' . Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER) . ' au ON (au.user_id = user.user_id) '; } $extraFieldWasAdded = false; if ($return_count && $resumed_report) { foreach ($extra_field as $extraField) { $extraFieldInfo = UserManager::get_extra_field_information_by_name($extraField); if (!empty($extraFieldInfo)) { $fieldValuesTable = Database::get_main_table(TABLE_EXTRA_FIELD_VALUES); $sql .= ' LEFT JOIN ' . $fieldValuesTable . ' as ufv ON ( user.user_id = ufv.item_id AND (field_id = ' . $extraFieldInfo['id'] . ' OR field_id IS NULL) )'; $extraFieldWasAdded = true; } } } $sql .= ' WHERE ' . $filter_by_status_condition . ' ' . implode(' OR ', $where); if ($multiple_access_url) { $current_access_url_id = api_get_current_access_url_id(); $sql .= " AND (access_url_id = {$current_access_url_id} ) "; } if ($return_count && $resumed_report && $extraFieldWasAdded) { $sql .= ' AND field_id IS NOT NULL GROUP BY value '; } if (!empty($courseCodeList)) { $courseCodeList = array_map(array('Database', 'escape_string'), $courseCodeList); $courseCodeList = implode('","', $courseCodeList); if (empty($sessionIdList)) { $sql .= ' AND course.code IN ("' . $courseCodeList . '")'; } } if (!empty($userIdList)) { $userIdList = array_map('intval', $userIdList); $userIdList = implode('","', $userIdList); $sql .= ' AND user.user_id IN ("' . $userIdList . '")'; } if (isset($filterByActive)) { $filterByActive = intval($filterByActive); $sql .= ' AND user.active = ' . $filterByActive; } $sql .= ' ' . $order_by . ' ' . $limit; $rs = Database::query($sql); $users = array(); $extra_fields = UserManager::get_extra_fields(0, 100, null, null, true, true); $counter = 1; $count_rows = Database::num_rows($rs); if ($return_count && $resumed_report) { return $count_rows; } $table_user_field_value = Database::get_main_table(TABLE_EXTRA_FIELD_VALUES); $tableExtraField = Database::get_main_table(TABLE_EXTRA_FIELD); if ($count_rows) { while ($user = Database::fetch_array($rs)) { if ($return_count) { return $user['count']; } $report_info = array(); $user_info = $user; $user_info['status'] = $user['status']; if (isset($user['is_tutor'])) { $user_info['is_tutor'] = $user['is_tutor']; } if (!empty($session_id)) { $user_info['status_session'] = $user['status_session']; } $sessionId = isset($user['session_id']) ? $user['session_id'] : 0; $course_code = isset($user['code']) ? $user['code'] : null; if ($add_reports) { if ($resumed_report) { $extra = array(); if (!empty($extra_fields)) { foreach ($extra_fields as $extra) { if (in_array($extra['1'], $extra_field)) { $user_data = UserManager::get_extra_user_data_by_field($user['user_id'], $extra['1']); break; } } } $row_key = '-1'; $name = '-'; if (!empty($extra)) { if (!empty($user_data[$extra['1']])) { $row_key = $user_data[$extra['1']]; $name = $user_data[$extra['1']]; $users[$row_key]['extra_' . $extra['1']] = $name; } } $users[$row_key]['training_hours'] += Tracking::get_time_spent_on_the_course($user['user_id'], $courseId, $sessionId); $users[$row_key]['count_users'] += $counter; $registered_users_with_extra_field = 0; if (!empty($name) && $name != '-') { $extraFieldType = EntityExtraField::COURSE_FIELD_TYPE; $name = Database::escape_string($name); $sql = "SELECT count(v.item_id) as count\n FROM {$table_user_field_value} v INNER JOIN\n {$tableExtraField} f\n ON (f.id = v.field_id)\n WHERE value = '{$name}' AND extra_field_type = {$extraFieldType}"; $result_count = Database::query($sql); if (Database::num_rows($result_count)) { $row_count = Database::fetch_array($result_count); $registered_users_with_extra_field = $row_count['count']; } } $users[$row_key]['count_users_registered'] = $registered_users_with_extra_field; $users[$row_key]['average_hours_per_user'] = $users[$row_key]['training_hours'] / $users[$row_key]['count_users']; $category = Category::load(null, null, $course_code, null, null, $sessionId); if (!isset($users[$row_key]['count_certificates'])) { $users[$row_key]['count_certificates'] = 0; } if (isset($category[0]) && $category[0]->is_certificate_available($user['user_id'])) { $users[$row_key]['count_certificates']++; } foreach ($extra_fields as $extra) { if ($extra['1'] == 'ruc') { continue; } if (!isset($users[$row_key][$extra['1']])) { $user_data = UserManager::get_extra_user_data_by_field($user['user_id'], $extra['1']); if (!empty($user_data[$extra['1']])) { $users[$row_key][$extra['1']] = $user_data[$extra['1']]; } } } } else { $sessionName = !empty($sessionId) ? ' - ' . $user['session_name'] : ''; $report_info['course'] = $user['title'] . $sessionName; $report_info['user'] = api_get_person_name($user['firstname'], $user['lastname']); $report_info['email'] = $user['email']; $report_info['time'] = api_time_to_hms(Tracking::get_time_spent_on_the_course($user['user_id'], $courseId, $sessionId)); $category = Category::load(null, null, $course_code, null, null, $sessionId); $report_info['certificate'] = Display::label(get_lang('No')); if (isset($category[0]) && $category[0]->is_certificate_available($user['user_id'])) { $report_info['certificate'] = Display::label(get_lang('Yes'), 'success'); } $progress = intval(Tracking::get_avg_student_progress($user['user_id'], $course_code, array(), $sessionId)); $report_info['progress_100'] = $progress == 100 ? Display::label(get_lang('Yes'), 'success') : Display::label(get_lang('No')); $report_info['progress'] = $progress . "%"; foreach ($extra_fields as $extra) { $user_data = UserManager::get_extra_user_data_by_field($user['user_id'], $extra['1']); $report_info[$extra['1']] = $user_data[$extra['1']]; } $report_info['user_id'] = $user['user_id']; $users[] = $report_info; } } else { $users[$user['user_id']] = $user_info; } } } return $users; }
/** * Prepares the shared SQL query for the user table. * See get_user_data() and get_number_of_users(). * * @param boolean $is_count Whether to count, or get data * @return string SQL query */ function prepare_user_sql_query($is_count) { $sql = ""; $user_table = Database::get_main_table(TABLE_MAIN_USER); $admin_table = Database::get_main_table(TABLE_MAIN_ADMIN); if ($is_count) { $sql .= "SELECT COUNT(u.id) AS total_number_of_items FROM {$user_table} u"; } else { $sql .= "SELECT u.id AS col0, u.official_code AS col2, "; if (api_is_western_name_order()) { $sql .= "u.firstname AS col3, u.lastname AS col4, "; } else { $sql .= "u.lastname AS col3, u.firstname AS col4, "; } $sql .= " u.username AS col5,\n u.email AS col6,\n u.status AS col7,\n u.active AS col8,\n u.id AS col9,\n u.registration_date AS col10,\n u.expiration_date AS exp,\n u.password\n FROM {$user_table} u"; } // adding the filter to see the user's only of the current access_url if ((api_is_platform_admin() || api_is_session_admin()) && api_get_multiple_access_url()) { $access_url_rel_user_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER); $sql .= " INNER JOIN {$access_url_rel_user_table} url_rel_user ON (u.id=url_rel_user.user_id)"; } $keywordList = array('keyword_firstname', 'keyword_lastname', 'keyword_username', 'keyword_email', 'keyword_officialcode', 'keyword_status', 'keyword_active', 'check_easy_passwords'); $keywordListValues = array(); $atLeastOne = false; foreach ($keywordList as $keyword) { $keywordListValues[$keyword] = null; if (isset($_GET[$keyword]) && !empty($_GET[$keyword])) { $keywordListValues[$keyword] = $_GET[$keyword]; $atLeastOne = true; } } if ($atLeastOne == false) { $keywordListValues = array(); } if (isset($keyword_extra_data) && !empty($keyword_extra_data)) { $extra_info = UserManager::get_extra_field_information_by_name($keyword_extra_data); $field_id = $extra_info['id']; $sql .= " INNER JOIN user_field_values ufv ON u.id=ufv.user_id AND ufv.field_id={$field_id} "; } if (isset($_GET['keyword']) && !empty($_GET['keyword'])) { $keywordFiltered = Database::escape_string("%" . $_GET['keyword'] . "%"); $sql .= " WHERE (\n u.firstname LIKE '{$keywordFiltered}' OR\n u.lastname LIKE '{$keywordFiltered}' OR\n concat(u.firstname, ' ', u.lastname) LIKE '{$keywordFiltered}' OR\n concat(u.lastname,' ',u.firstname) LIKE '{$keywordFiltered}' OR\n u.username LIKE '{$keywordFiltered}' OR\n u.official_code LIKE '{$keywordFiltered}' OR\n u.email LIKE '{$keywordFiltered}'\n )\n "; } elseif (isset($keywordListValues) && !empty($keywordListValues)) { $query_admin_table = ''; $keyword_admin = ''; if (isset($keywordListValues['keyword_status']) && $keywordListValues['keyword_status'] == PLATFORM_ADMIN) { $query_admin_table = " , {$admin_table} a "; $keyword_admin = ' AND a.user_id = u.id '; $keywordListValues['keyword_status'] = '%'; } $keyword_extra_value = ''; if (isset($keyword_extra_data) && !empty($keyword_extra_data) && !empty($keyword_extra_data_text)) { $keyword_extra_value = " AND ufv.field_value LIKE '%" . trim($keyword_extra_data_text) . "%' "; } $sql .= " {$query_admin_table}\n WHERE (\n u.firstname LIKE '" . Database::escape_string("%" . $keywordListValues['keyword_firstname'] . "%") . "' AND\n u.lastname LIKE '" . Database::escape_string("%" . $keywordListValues['keyword_lastname'] . "%") . "' AND\n u.username LIKE '" . Database::escape_string("%" . $keywordListValues['keyword_username'] . "%") . "' AND\n u.email LIKE '" . Database::escape_string("%" . $keywordListValues['keyword_email'] . "%") . "' AND\n u.official_code LIKE '" . Database::escape_string("%" . $keywordListValues['keyword_officialcode'] . "%") . "' AND\n u.status LIKE '" . Database::escape_string($keywordListValues['keyword_status']) . "'\n {$keyword_admin}\n {$keyword_extra_value}\n "; if (isset($keyword_active) && !isset($keyword_inactive)) { $sql .= " AND u.active='1'"; } elseif (isset($keyword_inactive) && !isset($keyword_active)) { $sql .= " AND u.active='0'"; } $sql .= " ) "; } // adding the filter to see the user's only of the current access_url if ((api_is_platform_admin() || api_is_session_admin()) && api_get_multiple_access_url()) { $sql .= " AND url_rel_user.access_url_id=" . api_get_current_access_url_id(); } return $sql; }
/** * Save message notification * @param array message type NOTIFICATION_TYPE_MESSAGE, NOTIFICATION_TYPE_INVITATION, NOTIFICATION_TYPE_GROUP * @param array recipients: user list of ids * @param string title * @param string content of the message * @param array result of api_get_user_info() or UserGroup->get() */ public function save_notification($type, $user_list, $title, $content, $sender_info = array(), $text_content = null) { $this->type = intval($type); $content = $this->format_content($content, $sender_info); $sender_id = 0; if (!empty($sender_info) && isset($sender_info['user_id'])) { $sender_id = $sender_info['user_id']; $this->set_sender_info($sender_id); } $setting_to_check = ''; $avoid_my_self = false; $default_status = self::NOTIFY_MESSAGE_AT_ONCE; switch ($this->type) { case self::NOTIFICATION_TYPE_MESSAGE: $setting_to_check = 'mail_notify_message'; $default_status = self::NOTIFY_MESSAGE_AT_ONCE; break; case self::NOTIFICATION_TYPE_INVITATION: $setting_to_check = 'mail_notify_invitation'; $default_status = self::NOTIFY_INVITATION_AT_ONCE; break; case self::NOTIFICATION_TYPE_GROUP: $setting_to_check = 'mail_notify_group_message'; $default_status = self::NOTIFY_GROUP_AT_ONCE; $avoid_my_self = true; break; } $setting_info = UserManager::get_extra_field_information_by_name($setting_to_check); if (!empty($user_list)) { foreach ($user_list as $user_id) { if ($avoid_my_self) { if ($user_id == api_get_user_id()) { continue; } } $user_info = api_get_user_info($user_id); //Extra field was deleted or removed? Use the default status if (empty($setting_info)) { $user_setting = $default_status; } else { $extra_data = UserManager::get_extra_user_data($user_id); $user_setting = $extra_data[$setting_to_check]; } $params = array(); switch ($user_setting) { //No notifications case self::NOTIFY_MESSAGE_NO: case self::NOTIFY_INVITATION_NO: case self::NOTIFY_GROUP_NO: break; //Send notification right now! //Send notification right now! case self::NOTIFY_MESSAGE_AT_ONCE: case self::NOTIFY_INVITATION_AT_ONCE: case self::NOTIFY_GROUP_AT_ONCE: if (!empty($user_info['mail'])) { $name = api_get_person_name($user_info['firstname'], $user_info['lastname']); if (!empty($sender_info['complete_name']) && !empty($sender_info['email'])) { $extra_headers = array(); $extra_headers['reply_to']['mail'] = $sender_info['email']; $extra_headers['reply_to']['name'] = $sender_info['complete_name']; api_mail_html($name, $user_info['mail'], Security::filter_terms($title), Security::filter_terms($content), $sender_info['complete_name'], $sender_info['email'], $extra_headers, array(), null, $text_content); } else { api_mail_html($name, $user_info['mail'], Security::filter_terms($title), Security::filter_terms($content), $sender_info['complete_name'], $sender_info['email'], array(), null, $text_content); } } $params['sent_at'] = api_get_utc_datetime(); // Saving the notification to be sent some day. // Saving the notification to be sent some day. default: $params['dest_user_id'] = $user_id; $params['dest_mail'] = $user_info['mail']; $params['title'] = $title; $params['content'] = Text::cut($content, $this->max_content_length); $params['send_freq'] = $user_setting; $params['sender_id'] = $sender_id; $this->save($params); break; } } } }
/** * Save message notification * @param int $type message type * NOTIFICATION_TYPE_MESSAGE, * NOTIFICATION_TYPE_INVITATION, * NOTIFICATION_TYPE_GROUP * @param array $user_list recipients: user list of ids * @param string $title * @param string $content * @param array $sender_info * result of api_get_user_info() or GroupPortalManager:get_group_data() */ public function save_notification($type, $user_list, $title, $content, $senderInfo = array()) { $this->type = intval($type); $content = $this->formatContent($content, $senderInfo); $titleToNotification = $this->formatTitle($title, $senderInfo); $settingToCheck = ''; $avoid_my_self = false; switch ($this->type) { case self::NOTIFICATION_TYPE_DIRECT_MESSAGE: case self::NOTIFICATION_TYPE_MESSAGE: $settingToCheck = 'mail_notify_message'; $defaultStatus = self::NOTIFY_MESSAGE_AT_ONCE; break; case self::NOTIFICATION_TYPE_INVITATION: $settingToCheck = 'mail_notify_invitation'; $defaultStatus = self::NOTIFY_INVITATION_AT_ONCE; break; case self::NOTIFICATION_TYPE_GROUP: $settingToCheck = 'mail_notify_group_message'; $defaultStatus = self::NOTIFY_GROUP_AT_ONCE; $avoid_my_self = true; break; default: $defaultStatus = self::NOTIFY_MESSAGE_AT_ONCE; break; } $settingInfo = UserManager::get_extra_field_information_by_name($settingToCheck); if (!empty($user_list)) { foreach ($user_list as $user_id) { if ($avoid_my_self) { if ($user_id == api_get_user_id()) { continue; } } $userInfo = api_get_user_info($user_id); // Extra field was deleted or removed? Use the default status. $userSetting = $defaultStatus; if (!empty($settingInfo)) { $extra_data = UserManager::get_extra_user_data($user_id); if (isset($extra_data[$settingToCheck]) && !empty($extra_data[$settingToCheck])) { $userSetting = $extra_data[$settingToCheck]; } } $sendDate = null; switch ($userSetting) { // No notifications case self::NOTIFY_MESSAGE_NO: case self::NOTIFY_INVITATION_NO: case self::NOTIFY_GROUP_NO: break; // Send notification right now! // Send notification right now! case self::NOTIFY_MESSAGE_AT_ONCE: case self::NOTIFY_INVITATION_AT_ONCE: case self::NOTIFY_GROUP_AT_ONCE: $extraHeaders = []; if (isset($senderInfo['email'])) { $extraHeaders = array('reply_to' => array('name' => $senderInfo['complete_name'], 'mail' => $senderInfo['email'])); } if (!empty($userInfo['email'])) { api_mail_html($userInfo['complete_name'], $userInfo['mail'], Security::filter_terms($titleToNotification), Security::filter_terms($content), $this->adminName, $this->adminEmail, $extraHeaders); } $sendDate = api_get_utc_datetime(); } // Saving the notification to be sent some day. $params = array('sent_at' => $sendDate, 'dest_user_id' => $user_id, 'dest_mail' => $userInfo['email'], 'title' => $title, 'content' => cut($content, $this->max_content_length), 'send_freq' => $userSetting); $this->save($params); } } }
/** * Get the users to display on the current page (fill the sortable-table) * @param int offset of first user to recover * @param int Number of users to get * @param int Column to sort on * @param string Order (ASC,DESC) * @param bool * @see SortableTable#get_table_data($from) */ function get_user_data($from, $number_of_items, $column, $direction, $get_count = false) { $user_table = Database::get_main_table(TABLE_MAIN_USER); $admin_table = Database::get_main_table(TABLE_MAIN_ADMIN); $select = "SELECT\n u.user_id\t\t\t\tAS col0,\n u.official_code\t\tAS col2,\n\t\t\t\t " . (api_is_western_name_order() ? "u.firstname \t\t\tAS col3,\n u.lastname \t\t\tAS col4," : "u.lastname \t\t\tAS col3,\n u.firstname \t\t\tAS col4,") . "\n u.username\t\t\t\tAS col5,\n u.email\t\t\t\tAS col6,\n u.status\t\t\t\tAS col7,\n u.active\t\t\t\tAS col8,\n u.user_id\t\t\t\tAS col9,\n u.registration_date AS col10,\n u.expiration_date AS exp,\n u.password\n "; if ($get_count) { $select = "SELECT count(u.user_id) as total_rows"; } $sql = "{$select} FROM {$user_table} u "; // adding the filter to see the user's only of the current access_url if ((api_is_platform_admin() || api_is_session_admin()) && api_get_multiple_access_url()) { $access_url_rel_user_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER); $sql .= " INNER JOIN {$access_url_rel_user_table} url_rel_user ON (u.user_id=url_rel_user.user_id)"; } if (isset($_GET['keyword_extra_data'])) { $keyword_extra_data = Database::escape_string($_GET['keyword_extra_data']); if (!empty($keyword_extra_data)) { $extra_info = UserManager::get_extra_field_information_by_name($keyword_extra_data); $field_id = $extra_info['id']; $sql .= " INNER JOIN user_field_values ufv ON u.user_id=ufv.user_id AND ufv.field_id={$field_id} "; } } if (isset($_GET['keyword'])) { $keyword = Database::escape_string(trim($_GET['keyword'])); $sql .= " WHERE (u.firstname LIKE '%" . $keyword . "%' OR u.lastname LIKE '%" . $keyword . "%' OR concat(u.firstname,' ',u.lastname) LIKE '%" . $keyword . "%' OR concat(u.lastname,' ',u.firstname) LIKE '%" . $keyword . "%' OR u.username LIKE '%" . $keyword . "%' OR u.official_code LIKE '%" . $keyword . "%' OR u.email LIKE '%" . $keyword . "%' )"; } elseif (isset($_GET['keyword_firstname'])) { $keyword_firstname = Database::escape_string($_GET['keyword_firstname']); $keyword_lastname = Database::escape_string($_GET['keyword_lastname']); $keyword_email = Database::escape_string($_GET['keyword_email']); $keyword_officialcode = Database::escape_string($_GET['keyword_officialcode']); $keyword_username = Database::escape_string($_GET['keyword_username']); $keyword_status = Database::escape_string($_GET['keyword_status']); $query_admin_table = ''; $and_conditions = array(); if ($keyword_status == SESSIONADMIN) { $keyword_status = '%'; $query_admin_table = " , {$admin_table} a "; $and_conditions[] = ' a.user_id = u.user_id '; } if (isset($_GET['keyword_extra_data'])) { if (!empty($_GET['keyword_extra_data']) && !empty($_GET['keyword_extra_data_text'])) { $keyword_extra_data_text = Database::escape_string($_GET['keyword_extra_data_text']); $and_conditions[] = " ufv.field_value LIKE '%" . trim($keyword_extra_data_text) . "%' "; } } $keyword_active = isset($_GET['keyword_active']); $keyword_inactive = isset($_GET['keyword_inactive']); $sql .= $query_admin_table . " WHERE ( "; if (!empty($keyword_firstname)) { $and_conditions[] = "u.firstname LIKE '%" . $keyword_firstname . "%' "; } if (!empty($keyword_lastname)) { $and_conditions[] = "u.lastname LIKE '%" . $keyword_lastname . "%' "; } if (!empty($keyword_username)) { $and_conditions[] = "u.username LIKE '%" . $keyword_username . "%' "; } if (!empty($keyword_email)) { $and_conditions[] = "u.email LIKE '%" . $keyword_email . "%' "; } if (!empty($keyword_officialcode)) { $and_conditions[] = "u.official_code LIKE '%" . $keyword_officialcode . "%' "; } if (!empty($keyword_status)) { $and_conditions[] = "u.status LIKE '" . $keyword_status . "' "; } if ($keyword_active && !$keyword_inactive) { $and_conditions[] = " u.active='1' "; } elseif ($keyword_inactive && !$keyword_active) { $and_conditions[] = " u.active='0' "; } if (!empty($and_conditions)) { $sql .= implode(' AND ', $and_conditions); } $sql .= " ) "; } // adding the filter to see the user's only of the current access_url if ((api_is_platform_admin() || api_is_session_admin()) && api_get_multiple_access_url()) { $sql .= " AND url_rel_user.access_url_id=" . api_get_current_access_url_id(); } $checkPassStrength = isset($_GET['check_easy_passwords']) && $_GET['check_easy_passwords'] == 1 ? true : false; if ($checkPassStrength) { $easyPasswordList = api_get_easy_password_list(); $easyPasswordList = array_map('api_get_encrypted_password', $easyPasswordList); $easyPasswordList = array_map(array('Database', 'escape_string'), $easyPasswordList); $easyPassword = implode("' OR password LIKE '", $easyPasswordList); $sql .= "AND password LIKE '{$easyPassword}' "; } if (!in_array($direction, array('ASC', 'DESC'))) { $direction = 'ASC'; } $column = intval($column); $from = intval($from); $number_of_items = intval($number_of_items); // Returns counts and exits function. if ($get_count) { $res = Database::query($sql); $user = Database::fetch_array($res); return $user['total_rows']; } $sql .= " ORDER BY col{$column} {$direction} "; $sql .= " LIMIT {$from},{$number_of_items}"; $res = Database::query($sql); $users = array(); $t = time(); while ($user = Database::fetch_row($res)) { $userInfo = api_get_user_info($user[0]); $image_path = UserManager::get_user_picture_path_by_id($user[0], 'web', false, true); $user_profile = UserManager::get_picture_user($user[0], $image_path['file'], 22, USER_IMAGE_SIZE_SMALL, ' width="22" height="22" '); if (!api_is_anonymous()) { $photo = '<center><a href="' . $userInfo['profile_url'] . '" title="' . get_lang('Info') . '"> <img src="' . $user_profile['file'] . '" ' . $user_profile['style'] . ' alt="' . $userInfo['complete_name'] . '" title="' . $userInfo['complete_name'] . '" /></a></center>'; } else { $photo = '<center><img src="' . $user_profile['file'] . '" ' . $user_profile['style'] . ' alt="' . $userInfo['complete_name'] . '" title="' . $userInfo['complete_name'] . '" /></center>'; } if ($user[7] == 1 && $user[10] != '0000-00-00 00:00:00') { // check expiration date $expiration_time = api_convert_sql_date($user[10]); // if expiration date is passed, store a special value for active field if ($expiration_time < $t) { $user[7] = '-1'; } } // forget about the expiration date field $users[] = array($user[0], $photo, $user[1], Display::url($user[2], $userInfo['profile_url']), Display::url($user[3], $userInfo['profile_url']), $user[4], $user[5], $user[6], $user[7], api_get_local_time($user[9]), $user[0]); } return $users; }
/** * Return user info array of all users registered in the specified course * This only returns the users that are registered in this actual course, not linked courses. * * @param string $course_code the code of the course * @param boolean $with_session determines if the course is used in a session or not * @param integer $session_id the id of the session * @param string $limit the LIMIT statement of the sql statement * @param string $order_by the field to order the users by. Valid values are 'lastname', 'firstname', 'username', 'email', 'official_code' OR a part of a SQL statement that starts with ORDER BY ... * @param int if using the session_id: 0 or 2 (student, coach), if using session_id = 0 STUDENT or COURSEMANAGER * @return array */ public static function get_user_list_from_course_code($course_code = null, $session_id = 0, $limit = null, $order_by = null, $filter_by_status = null, $return_count = null, $add_reports = false, $resumed_report = false, $extra_field = null) { // variable initialisation $session_id = intval($session_id); $course_code = Database::escape_string($course_code); $where = array(); // if the $order_by does not contain 'ORDER BY' we have to check if it is a valid field that can be sorted on if (!strstr($order_by, 'ORDER BY')) { //if (!empty($order_by) AND in_array($order_by, array('lastname', 'firstname', 'username', 'email', 'official_code'))) { if (!empty($order_by)) { $order_by = 'ORDER BY ' . $order_by; } else { $order_by = ''; } } $courseInfo = api_get_course_info($course_code); $courseId = null; if ($courseInfo) { $courseId = $courseInfo['real_id']; } $filter_by_status_condition = null; if (!empty($session_id) && !empty($courseId)) { $sql = 'SELECT DISTINCT user.user_id, session_course_user.status as status_session, user.* '; $sql .= ' FROM ' . Database::get_main_table(TABLE_MAIN_USER) . ' as user '; $sql .= ' LEFT JOIN ' . Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER) . ' as session_course_user ON user.user_id = session_course_user.id_user AND session_course_user.c_id="' . $courseId . '" AND session_course_user.id_session = ' . $session_id; $where[] = ' session_course_user.c_id IS NOT NULL '; // 2 = coach // 0 = student if (isset($filter_by_status)) { $filter_by_status = intval($filter_by_status); $filter_by_status_condition = " session_course_user.status = {$filter_by_status} AND "; } } else { if ($return_count) { $sql = " SELECT COUNT(*) as count"; if ($resumed_report) { //$sql = " SELECT count(field_id) "; } } else { if (empty($course_code)) { $sql = 'SELECT DISTINCT course.title, course.code, course_rel_user.status as status_rel, user.user_id, course_rel_user.role, course_rel_user.tutor_id, user.* '; } else { $sql = 'SELECT DISTINCT course_rel_user.status as status_rel, user.user_id, course_rel_user.role, course_rel_user.tutor_id, user.* '; } } $sql .= ' FROM ' . Database::get_main_table(TABLE_MAIN_USER) . ' as user '; $sql .= ' LEFT JOIN ' . Database::get_main_table(TABLE_MAIN_COURSE_USER) . ' as course_rel_user ON user.user_id = course_rel_user.user_id AND course_rel_user.relation_type <> ' . COURSE_RELATION_TYPE_RRHH; if (!empty($courseInfo)) { $sql .= " AND course_rel_user.c_id = " . $courseId; } else { $course_table = Database::get_main_table(TABLE_MAIN_COURSE); $sql .= " INNER JOIN {$course_table} course ON course_rel_user.c_id = course.id"; } $where[] = ' course_rel_user.c_id IS NOT NULL '; if (isset($filter_by_status) && $filter_by_status != '') { $filter_by_status = intval($filter_by_status); $filter_by_status_condition = " course_rel_user.status = {$filter_by_status} AND "; } } $multiple_access_url = api_get_multiple_access_url(); if ($multiple_access_url) { $sql .= ' LEFT JOIN ' . Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER) . ' au ON (au.user_id = user.user_id) '; } if ($return_count && $resumed_report) { $extra_field_info = UserManager::get_extra_field_information_by_name($extra_field); $sql .= ' LEFT JOIN ' . Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES) . ' as ufv ON (user.user_id = ufv.user_id AND (field_id = ' . $extra_field_info['id'] . ' OR field_id IS NULL ) )'; } $sql .= ' WHERE ' . $filter_by_status_condition . ' ' . implode(' OR ', $where); if ($multiple_access_url) { $current_access_url_id = api_get_current_access_url_id(); $sql .= " AND (access_url_id = {$current_access_url_id} ) "; } if ($return_count && $resumed_report) { $sql .= ' AND field_id IS NOT NULL GROUP BY field_value '; } $sql .= ' ' . $order_by . ' ' . $limit; $rs = Database::query($sql); $users = array(); if ($add_reports) { $extra_fields = UserManager::get_extra_fields(0, 100, null, null, true, true); } $counter = 1; $count_rows = Database::num_rows($rs); if ($return_count && $resumed_report) { return $count_rows; } $table_user_field_value = Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES); if ($count_rows) { while ($user = Database::fetch_array($rs)) { $report_info = array(); if ($return_count) { return $user['count']; } $user_info = $user; $user_info['status'] = $user['status']; if (isset($user['role'])) { $user_info['role'] = $user['role']; } if (isset($user['tutor_id'])) { $user_info['tutor_id'] = $user['tutor_id']; } if (!empty($session_id)) { $user_info['status_session'] = $user['status_session']; } $user_info['complete_name'] = api_get_person_name($user_info['firstname'], $user_info['lastname']); if ($add_reports) { $course_code = $user['code']; if ($resumed_report) { foreach ($extra_fields as $extra) { if ($extra['1'] == $extra_field) { $user_data = UserManager::get_extra_user_data_by_field($user['user_id'], $extra['1']); break; } } if (empty($user_data[$extra['1']])) { $row_key = '-1'; $name = '-'; } else { $row_key = $user_data[$extra['1']]; $name = $user_data[$extra['1']]; } $users[$row_key]['extra_' . $extra['1']] = $name; $users[$row_key]['training_hours'] += Tracking::get_time_spent_on_the_course($user['user_id'], $courseId, 0); $users[$row_key]['count_users'] += $counter; $registered_users_with_extra_field = 0; if (!empty($name) && $name != '-') { $name = Database::escape_string($name); $sql = "SELECT count(user_id) as count FROM {$table_user_field_value} WHERE field_value = '{$name}'"; $result_count = Database::query($sql); if (Database::num_rows($result_count)) { $row_count = Database::fetch_array($result_count); $registered_users_with_extra_field = $row_count['count']; } } $users[$row_key]['count_users_registered'] = $registered_users_with_extra_field; $users[$row_key]['average_hours_per_user'] = $users[$row_key]['training_hours'] / $users[$row_key]['count_users']; $category = Category::load(null, null, $course_code); if (!isset($users[$row_key]['count_certificates'])) { $users[$row_key]['count_certificates'] = 0; } if (isset($category[0]) && $category[0]->is_certificate_available($user['user_id'])) { $users[$row_key]['count_certificates']++; } } else { $report_info['course'] = $user['title']; $report_info['user'] = api_get_person_name($user['firstname'], $user['lastname']); $report_info['time'] = api_time_to_hms(Tracking::get_time_spent_on_the_course($user['user_id'], $courseId, 0)); $category = Category::load(null, null, $course_code); $report_info['certificate'] = Display::label(get_lang('No')); if (isset($category[0]) && $category[0]->is_certificate_available($user['user_id'])) { $report_info['certificate'] = Display::label(get_lang('Yes'), 'success'); } //$report_info['score'] = Tracking::get_avg_student_score($user['user_id'], $courseId, array(), 0); $progress = intval(Tracking::get_avg_student_progress($user['user_id'], $courseId, array(), 0)); $report_info['progress_100'] = $progress == 100 ? Display::label(get_lang('Yes'), 'success') : Display::label(get_lang('No')); $report_info['progress'] = $progress . "%"; foreach ($extra_fields as $extra) { $user_data = UserManager::get_extra_user_data_by_field($user['user_id'], $extra['1']); $report_info[$extra['1']] = $user_data[$extra['1']]; } $users[] = $report_info; } } else { $users[$user['user_id']] = $user_info; } } $counter++; } return $users; }
/** Used by the widescale plugin */ static function get_user_data($from, $number_of_items, $column, $direction, $get_count = false) { $user_table = Database::get_main_table(TABLE_MAIN_USER); $select = "SELECT\n u.user_id,\n u.username,\n u.firstname,\n u.lastname,\n ufv1.field_value as exam_password\n "; if ($get_count) { $select = "SELECT count(u.user_id) as total_rows"; } $sql = "{$select} FROM {$user_table} u "; // adding the filter to see the user's only of the current access_url if ((api_is_platform_admin() || api_is_session_admin()) && api_get_multiple_access_url()) { $access_url_rel_user_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER); $sql .= " INNER JOIN {$access_url_rel_user_table} url_rel_user ON (u.user_id=url_rel_user.user_id)"; } $extra_fields = array('exam_password', 'exam_room', 'exam_schedule'); $counter = 1; $where_condition = ""; $and_conditions = array(); foreach ($extra_fields as $keyword_extra_data) { $extra_info = UserManager::get_extra_field_information_by_name($keyword_extra_data); $field_id = $extra_info['id']; $table_alias = "ufv{$counter}"; $sql .= " INNER JOIN user_field_values {$table_alias} ON u.user_id = {$table_alias}.user_id AND {$table_alias}.field_id = {$field_id} "; $counter++; if ($keyword_extra_data == 'exam_password') { continue; } $keyword_extra_data_text = UserManager::get_extra_user_data_by_field(api_get_user_id(), $extra_info['field_variable']); $keyword_extra_data_text = $keyword_extra_data_text[$extra_info['field_variable']]; if (!empty($keyword_extra_data_text)) { $and_conditions[] = " {$table_alias}.field_value LIKE '%" . trim($keyword_extra_data_text) . "%' "; } } if (!empty($and_conditions)) { $where_condition = implode(' AND ', $and_conditions); } if (!empty($where_condition)) { $sql .= " WHERE {$where_condition} "; } $sql .= " AND u.user_id <> " . api_get_user_id(); // adding the filter to see the user's only of the current access_url if ((api_is_platform_admin() || api_is_session_admin()) && api_get_multiple_access_url()) { $sql .= " AND url_rel_user.access_url_id=" . api_get_current_access_url_id(); } if (!in_array($direction, array('ASC', 'DESC'))) { $direction = 'ASC'; } if (in_array($column, array('username', 'firstname', 'lastname'))) { $column = $column; } $from = intval($from); $number_of_items = intval($number_of_items); //Returns counts and exits function if ($get_count) { $res = Database::query($sql); $user = Database::fetch_array($res); return $user['total_rows']; } $sql .= " ORDER BY {$column} {$direction} "; $sql .= " LIMIT {$from}, {$number_of_items}"; $res = Database::query($sql); $users = array(); while ($user = Database::fetch_array($res, 'ASSOC')) { $users[] = $user; } return $users; }