chamilo / chamilo-lms

     * @param string $keyword

     * @return array|null

     */

    public static function searchCategoryByKeyword($keyword)

    {

        if (empty($keyword)) {

            return null;

        }

        $tableCategory = Database::get_main_table(TABLE_MAIN_CATEGORY);

        $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE_CATEGORY);

        $conditions = " INNER JOIN $table a ON (c.id = a.course_category_id)";

        $whereCondition = " AND a.access_url_id = ".api_get_current_access_url_id();

        $keyword = Database::escape_string($keyword);

        $sql = "SELECT c.*, c.name as text

            FROM $tableCategory c $conditions

            WHERE

                (

                    c.code LIKE '%$keyword%' OR name LIKE '%$keyword%'

                ) AND

                auth_course_child = 'TRUE'

                $whereCondition ";

        $result = Database::query($sql);

        return Database::store_result($result, 'ASSOC');

    }

    /**

     * @param array $list

    * @param int  access url id

    * @return array   Database::store_result of the result

    **/

    public static function get_url_rel_course_data($access_url_id = null)

    {

        $where = '';

        $table_url_rel_course = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);

        $tbl_course = Database :: get_main_table(TABLE_MAIN_COURSE);

        if (!empty($access_url_id)) {

            $where = " WHERE uc.access_url_id = ".intval($access_url_id);

        }

        $sql = "SELECT u.id, c_id, title, uc.access_url_id

                FROM $tbl_course u

                INNER JOIN $table_url_rel_course uc

                ON uc.c_id = u.id

                $where

                ORDER BY title, code";

        $result = Database::query($sql);

        $courses = Database::store_result($result);

        return $courses;

    }

    /**

     * Gets the number of rows with a specific course_code in access_url_rel_course table

     * @return array   Database::store_result of the result

     *

     **/

    public static function get_url_rel_session_data($access_url_id = null)

    {

        $where ='';

        $table_url_rel_session = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);

        $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);

        if (!empty($access_url_id))

            $where ="WHERE $table_url_rel_session.access_url_id = ".intval($access_url_id);

        $sql = "SELECT id, name, access_url_id

                FROM $tbl_session u

                INNER JOIN $table_url_rel_session

                ON $table_url_rel_session.session_id = id

                $where

                ORDER BY name, id";

        $result = Database::query($sql);

        $sessions = Database::store_result($result);

        return $sessions;

    }

    /**

     * Gets the inner join of access_url and the usergroup table

     *

     * @return array   Database::store_result of the result

     **/

    public static function get_url_rel_usergroup_data($access_url_id = null)

    {

        $where = '';

        $table_url_rel_usergroup = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USERGROUP);

        $table_user_group = Database::get_main_table(TABLE_USERGROUP);

        if (!empty($access_url_id)) {

            $where ="WHERE $table_url_rel_usergroup.access_url_id = ".intval($access_url_id);

        }

        $sql = "SELECT id, name, access_url_id

				FROM $table_user_group u

				INNER JOIN $table_url_rel_usergroup

				ON $table_url_rel_usergroup.usergroup_id = u.id

				$where

				ORDER BY name";

        $result = Database::query($sql);

        $courses = Database::store_result($result);

        return $courses;

    }

    /**

     * Gets the inner join of access_url and the usergroup table

     * @param int $exe_id

     * @return array

     */

    public static function get_exercise_track_exercise_info($exe_id)

    {

        $TBL_EXERCICES = Database::get_course_table(TABLE_QUIZ_TEST);

        $TBL_TRACK_EXERCICES = Database::get_main_table(

            TABLE_STATISTIC_TRACK_E_EXERCISES

        );

        $TBL_COURSE = Database::get_main_table(TABLE_MAIN_COURSE);

        $exe_id = intval($exe_id);

        $result = array();

        if (!empty($exe_id)) {

            $sql = " SELECT q.*, tee.*

                FROM $TBL_EXERCICES as q

                INNER JOIN $TBL_TRACK_EXERCICES as tee

                ON q.id = tee.exe_exo_id

                INNER JOIN $TBL_COURSE c

                ON c.id = tee.c_id

                WHERE tee.exe_id = $exe_id

                AND q.c_id = c.id";

            $res_fb_type = Database::query($sql);

            $result = Database::fetch_array($res_fb_type, 'ASSOC');

        }

        return $result;

    }

    /**

     * Validates the time control key