/**
  * @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;
}
Ejemplo n.º 3
0
 /**
  * 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;
 }
Ejemplo n.º 4
0
 /**
  * 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;
 }