/** * @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; }
/** * @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; }
/** * 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 * @return array an array containing a list of all the courses (the code, directory, dabase, visual_code, title, ... ) matching the the search term. */ public function search_courses($search_term) { $TABLECOURS = 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 FROM {$TABLE_COURSE_FIELD_VALUE} tcfv 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) . ')'; } $search_term_safe = Database::escape_string($search_term); $sql_find = "SELECT * FROM {$TABLECOURS} WHERE (code LIKE '%" . $search_term_safe . "%' OR title LIKE '%" . $search_term_safe . "%' OR tutor_name LIKE '%" . $search_term_safe . "%') {$without_special_courses} ORDER BY title, visual_code ASC"; 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 * FROM {$TABLECOURS} 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 (code LIKE '%" . $search_term_safe . "%' OR title LIKE '%" . $search_term_safe . "%' OR tutor_name LIKE '%" . $search_term_safe . "%' ) {$without_special_courses} ORDER BY title, visual_code ASC "; } } $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)); $courses[] = array('code' => $row['code'], 'real_id' => $row['id'], '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; }