/**
* @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())
{
global $_configuration;
$tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
$TABLE_COURSE_FIELD = Database::get_main_table(TABLE_MAIN_COURSE_FIELD);
$TABLE_COURSE_FIELD_VALUE = Database::get_main_table(TABLE_MAIN_COURSE_FIELD_VALUES);
// Get course list auto-register
$sql = "SELECT course_code\n FROM {$TABLE_COURSE_FIELD_VALUE} tcfv\n INNER JOIN {$TABLE_COURSE_FIELD} tcf ON tcfv.field_id = tcf.id\n WHERE tcf.field_variable = 'special_course' AND tcfv.field_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['course_code'] . '"';
}
}
$without_special_courses = '';
if (!empty($special_course_list)) {
$without_special_courses = ' AND course.code NOT IN (' . implode(',', $special_course_list) . ')';
}
$visibilityCondition = null;
if (isset($_configuration['course_catalog_hide_private'])) {
if ($_configuration['course_catalog_hide_private'] == 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 ON (url_rel_course.course_code=course.code)\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 INNER JOIN {$tbl_url_rel_course} as url_rel_course\n ON (url_rel_course.course_code=course.code)\n WHERE access_url_id = {$url_access_id} AND\n RAND()*{$num_records}< {$random_value}\n {$without_special_courses} {$visibilityCondition}\n ORDER BY RAND() 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() 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}";
}
}
$sql = "SELECT * FROM {$tbl_course} WHERE id IN({$id_in})";
} else {
$limitFilter = getLimitFilterFromArray($limit);
$category_code = Database::escape_string($category_code);
if (empty($category_code) || $category_code == "ALL") {
$sql = "SELECT * FROM {$tbl_course}\n WHERE 1=1 {$without_special_courses} {$visibilityCondition}\n ORDER BY title {$limitFilter} ";
} else {
if ($category_code == 'NONE') {
$category_code = '';
}
$sql = "SELECT * FROM {$tbl_course}\n WHERE category_code='{$category_code}' {$without_special_courses} {$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 INNER JOIN {$tbl_url_rel_course} as url_rel_course\n ON (url_rel_course.course_code=course.code)\n WHERE access_url_id = {$url_access_id} AND category_code='{$category_code}' {$without_special_courses} {$visibilityCondition}\n ORDER BY title {$limitFilter}";
} else {
$sql = "SELECT * FROM {$tbl_course} as course INNER JOIN {$tbl_url_rel_course} as url_rel_course\n ON (url_rel_course.course_code=course.code)\n WHERE access_url_id = {$url_access_id} {$without_special_courses} {$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['code'], 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'], 'db' => $row['db_name'], '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;
}
/**
* 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;
}
/**
* List the sessions
* @param string $date (optional) The date of sessions
* @param array $limit
* @return array The session list
*/
public function browseSessions($date = null, $limit = array())
{
require_once api_get_path(LIBRARY_PATH) . 'sessionmanager.lib.php';
$userTable = Database::get_main_table(TABLE_MAIN_USER);
$sessionTable = Database::get_main_table(TABLE_MAIN_SESSION);
$sessionsToBrowse = array();
$userId = api_get_user_id();
$limitFilter = getLimitFilterFromArray($limit);
$sql = "SELECT s.id, s.name, s.nbr_courses, s.nbr_users, s.date_start, s.date_end, u.lastname, u.firstname, u.username " . "FROM {$sessionTable} AS s " . "INNER JOIN {$userTable} AS u " . "ON s.id_coach = u.user_id " . "WHERE 1 = 1 ";
if (!is_null($date)) {
$date = Database::escape_string($date);
$sql .= "AND ('{$date}' BETWEEN s.date_start AND s.date_end) " . "OR (s.date_end = '0000-00-00') " . "OR (s.date_start = '0000-00-00' AND s.date_end != '0000-00-00' AND s.date_end > '{$date}')";
}
// Add limit filter to do pagination
$sql .= $limitFilter;
$sessionResult = Database::query($sql);
if ($sessionResult != false) {
while ($session = Database::fetch_assoc($sessionResult)) {
if ($session['nbr_courses'] > 0) {
$session['coach_name'] = api_get_person_name($session['firstname'], $session['lastname']);
$session['coach_name'] .= " ({$session['username']})";
$session['is_subscribed'] = SessionManager::isUserSusbcribedAsStudent($session['id'], $userId);
$sessionsToBrowse[] = $session;
}
}
}
return $sessionsToBrowse;
}