CoursesAndSessionsCatalog::countAvailableCoursesToShowInCatalog() - Code Metrics - Inspection of "Minor - merge with 1.11.x" - chamilo/chamilo-lms - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( ae5621...ef667c )

by Julito

created 2018-05-02 09:01 UTC

countAvailableCoursesToShowInCatalog() B

↳ Parent: CoursesAndSessionsCatalog

Complexity

Conditions	2
Paths	2

Size

Total Lines	29
Code Lines	16

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	2
eloc	16
nc	2
nop	1
dl	0
loc	29
rs	8.8571
c	0
b	0
f	0

<?php
/* For licensing terms, see /license.txt */

use Chamilo\CoreBundle\Entity\ExtraField;
use Doctrine\Common\Collections\Criteria;
use Doctrine\ORM\Query\Expr\Join;

/**
 * @todo change class name
 * Class CoursesAndSessionsCatalog
 */
class CoursesAndSessionsCatalog
{
    const PAGE_LENGTH = 12;

    /**
     * Check the configuration for the courses and sessions catalog.
     *
     * @global array $_configuration Configuration
     *
     * @param int $value The value to check
     *
     * @return bool Whether the configuration is $value
     */
    public static function is($value = CATALOG_COURSES)
    {
        $showCoursesSessions = intval(api_get_setting('catalog_show_courses_sessions'));
        if ($showCoursesSessions == $value) {
            return true;
        }

        return false;
    }

    /**
     * Check whether to display the sessions list.
     *
     * @global array $_configuration Configuration
     *
     * @return bool whether to display
     */
    public static function showSessions()
    {
        $catalogShow = intval(api_get_setting('catalog_show_courses_sessions'));

        if ($catalogShow == CATALOG_SESSIONS || $catalogShow == CATALOG_COURSES_SESSIONS) {
            return true;
        }

        return false;
    }

    /**
     * Check whether to display the courses list.
     *
     * @global array $_configuration Configuration
     *
     * @return bool whether to display
     */
    public static function showCourses()
    {
        $catalogShow = intval(api_get_setting('catalog_show_courses_sessions'));

        if ($catalogShow == CATALOG_COURSES || $catalogShow == CATALOG_COURSES_SESSIONS) {
            return true;
        }

        return false;
    }

    /**
     * @return array
     */
    public static function getCoursesToAvoid()
    {
        $TABLE_COURSE_FIELD = Database::get_main_table(TABLE_EXTRA_FIELD);
        $TABLE_COURSE_FIELD_VALUE = Database::get_main_table(TABLE_EXTRA_FIELD_VALUES);

        // Check special courses
        $courseListToAvoid = CourseManager::get_special_course_list();

        // Checks "hide_from_catalog" extra field
        $extraFieldType = ExtraField::COURSE_FIELD_TYPE;

        $sql = "SELECT item_id FROM $TABLE_COURSE_FIELD_VALUE tcfv
                INNER JOIN $TABLE_COURSE_FIELD tcf
                ON tcfv.field_id =  tcf.id
                WHERE
                    tcf.extra_field_type = $extraFieldType AND
                    tcf.variable = 'hide_from_catalog' AND
                    tcfv.value = 1
                ";

        $result = Database::query($sql);
        if (Database::num_rows($result) > 0) {
            while ($row = Database::fetch_array($result)) {
                $courseListToAvoid[] = $row['item_id'];
            }
        }

        return $courseListToAvoid;
    }

    /**
     * @return string
     */
    public static function getAvoidCourseCondition()
    {
        $courseListToAvoid = self::getCoursesToAvoid();
        $condition = '';
        if (!empty($courseListToAvoid)) {
            $courses = [];
            foreach ($courseListToAvoid as $courseId) {
                $courses[] = '"'.$courseId.'"';
            }
            $condition = ' AND course.id NOT IN ('.implode(',', $courses).')';
        }

        return $condition;
    }

    /**
     * Get available le courses count.
     *
     * @param int $accessUrlId (optional)
     *
     * @return int Number of courses
     */
    public static function countAvailableCoursesToShowInCatalog($accessUrlId = 1)
    {
        $tableCourse = Database::get_main_table(TABLE_MAIN_COURSE);
        $tableCourseRelAccessUrl = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
        $courseToAvoidCondition = self::getAvoidCourseCondition();

        $visibilityCondition = CourseManager::getCourseVisibilitySQLCondition('course', true);

        $accessUrlId = (int) $accessUrlId;
        if (empty($accessUrlId)) {
            $accessUrlId = 1;
        }

        $sql = "SELECT count(course.id) 
                FROM $tableCourse course
                INNER JOIN $tableCourseRelAccessUrl u
                ON (course.id = u.c_id)
                WHERE
                    u.access_url_id = $accessUrlId AND
                    course.visibility != 0 AND
                    course.visibility != 4
                    $courseToAvoidCondition
                    $visibilityCondition
                ";

        $res = Database::query($sql);
        $row = Database::fetch_row($res);

        return $row[0];
    }

    /**
     * @return array
     */
    public static function getCourseCategories()
    {
        $urlId = 1;
        if (api_is_multiple_url_enabled()) {
            $urlId = api_get_current_access_url_id();
        }

        $countCourses = self::countAvailableCoursesToShowInCatalog($urlId);

        $categories = [];
        $categories[0][0] = [
            'id' => 0,
            'name' => get_lang('DisplayAll'),
            'code' => 'ALL',
            'parent_id' => null,
            'tree_pos' => 0,
            'count_courses' => $countCourses,
        ];

        $categoriesFromDatabase = CourseCategory::getCategories();
        foreach ($categoriesFromDatabase as $row) {
            $count_courses = CourseCategory::countCoursesInCategory($row['code']);
            $row['count_courses'] = $count_courses;
            if (empty($row['parent_id'])) {
                $categories[0][$row['tree_pos']] = $row;
            } else {
                $categories[$row['parent_id']][$row['tree_pos']] = $row;
            }
        }
        $count_courses = CourseCategory::countCoursesInCategory();
        $categories[0][count($categories[0]) + 1] = [
            'id' => 0,
            'name' => get_lang('None'),
            'code' => 'NONE',
            'parent_id' => null,
            'tree_pos' => $row['tree_pos'] + 1,
            'children_count' => 0,
            'auth_course_child' => true,
            'auth_cat_child' => true,
            'count_courses' => $count_courses,
        ];

        return $categories;
    }

    /**
     * Return LIMIT to filter SQL query.
     *
     * @param array $limit
     *
     * @return string
     */
    public static function getLimitFilterFromArray($limit)
    {
        $limitFilter = '';
        if (!empty($limit) && is_array($limit)) {
            $limitStart = isset($limit['start']) ? $limit['start'] : 0;
            $limitLength = isset($limit['length']) ? $limit['length'] : 12;
            $limitFilter = 'LIMIT '.$limitStart.', '.$limitLength;
        }

        return $limitFilter;
    }

    /**
     * @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
     */
    public static function getCoursesInCategory($category_code, $random_value = null, $limit = [])
    {
        $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
        $avoidCoursesCondition = self::getAvoidCourseCondition();
        $visibilityCondition = CourseManager::getCourseVisibilitySQLCondition(
            'course',
            true
        );

        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
                        INNER JOIN $tbl_url_rel_course as url_rel_course
                        ON (url_rel_course.c_id = course.id)
                        WHERE access_url_id = $url_access_id ";
                $result = Database::query($sql);
                list($num_records) = Database::fetch_row($result);

                $sql = "SELECT course.id, course.id as real_id 
                        FROM $tbl_course course
                        INNER JOIN $tbl_url_rel_course as url_rel_course
                        ON (url_rel_course.c_id = course.id)
                        WHERE
                            access_url_id = $url_access_id AND
                            RAND()*$num_records< $random_value
                            $avoidCoursesCondition 
                            $visibilityCondition
                        ORDER BY RAND()
                        LIMIT 0, $random_value";
            } else {
                $sql = "SELECT id, id as real_id FROM $tbl_course course
                        WHERE 
                            RAND()*$num_records< $random_value 
                            $avoidCoursesCondition 
                            $visibilityCondition
                        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";
                }
            }
            if ($id_in === null) {
                return [];
            }
            $sql = "SELECT *, id as real_id 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 *, id as real_id 
                        FROM $tbl_course course
                        WHERE
                          1=1
                          $avoidCoursesCondition
                          $visibilityCondition
                    ORDER BY title $limitFilter ";
            } else {
                if ($category_code == 'NONE') {
                    $category_code = '';
                }
                $sql = "SELECT *, id as real_id FROM $tbl_course course
                        WHERE
                            category_code='$category_code'
                            $avoidCoursesCondition
                            $visibilityCondition
                        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 *, course.id real_id FROM $tbl_course as course
                            INNER JOIN $tbl_url_rel_course as url_rel_course
                            ON (url_rel_course.c_id = course.id)
                            WHERE
                                access_url_id = $url_access_id AND
                                category_code='$category_code'
                                $avoidCoursesCondition
                                $visibilityCondition
                            ORDER BY title $limitFilter";
                } else {
                    $sql = "SELECT *, course.id real_id FROM $tbl_course as course
                            INNER JOIN $tbl_url_rel_course as url_rel_course
                            ON (url_rel_course.c_id = course.id)
                            WHERE
                                access_url_id = $url_access_id
                                $avoidCoursesCondition
                                $visibilityCondition
                            ORDER BY title $limitFilter";
                }
            }
        }

        $result = Database::query($sql);
        $courses = [];
        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[] = [
                'real_id' => $row['real_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'],
                'category' => $row['category_code'],
                '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
     * @param bool   $justVisible search only on visible courses in the catalogue
     *
     * @return array an array containing a list of all the courses matching the the search term
     */
    public static function search_courses($search_term, $limit, $justVisible = false)
    {
        $courseTable = Database::get_main_table(TABLE_MAIN_COURSE);
        $limitFilter = self::getLimitFilterFromArray($limit);
        $avoidCoursesCondition = self::getAvoidCourseCondition();
        $visibilityCondition = $justVisible ? CourseManager::getCourseVisibilitySQLCondition('course', true) : '';

        $search_term_safe = Database::escape_string($search_term);
        $sql_find = "SELECT * FROM $courseTable course
                      WHERE (
                            course.code LIKE '%".$search_term_safe."%' OR
                            course.title LIKE '%".$search_term_safe."%' OR
                            course.tutor_name LIKE '%".$search_term_safe."%'
                        )
                        $avoidCoursesCondition
                        $visibilityCondition
                    ORDER BY title, visual_code ASC
                    $limitFilter
                    ";

        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 $courseTable as course
                            INNER JOIN $tbl_url_rel_course as url_rel_course
                            ON (url_rel_course.c_id = course.id)
                            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."%'
                                )
                                $avoidCoursesCondition
                                $visibilityCondition
                            ORDER BY title, visual_code ASC
                            $limitFilter
                            ";
            }
        }
        $result_find = Database::query($sql_find);
        $courses = [];
        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[] = [
                '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 static function browseSessions($date = null, $limit = [])
    {
        $em = Database::getManager();
        $urlId = api_get_current_access_url_id();

        $sql = "SELECT s.id FROM session s ";
        $sql .= "
            INNER JOIN access_url_rel_session ars
            ON s.id = ars.session_id
        ";

        $sql .= "
            WHERE s.nbr_courses > 0
                AND ars.access_url_id = $urlId
        ";

        if (!is_null($date)) {
            $sql .= "
                AND (
                    ('$date' BETWEEN DATE(s.access_start_date) AND DATE(s.access_end_date))
                    OR (s.access_end_date IS NULL)
                    OR (
                        s.access_start_date IS NULL
                        AND s.access_end_date IS NOT NULL
                        AND DATE(s.access_end_date) >= '$date'
                    )
                )
            ";
        }

        if (!empty($limit)) {
            $sql .= "LIMIT {$limit['start']}, {$limit['length']} ";
        }

        $ids = Database::store_result(Database::query($sql));
        $sessions = [];
        foreach ($ids as $id) {
            $sessions[] = $em->find('ChamiloCoreBundle:Session', $id);
        }

        return $sessions;
    }

    /**
     * Search sessions by searched term by session name.
     *
     * @param string $queryTerm Term for search
     * @param array  $limit     Limit info
     *
     * @return array The sessions
     */
    public static function browseSessionsBySearch($queryTerm, array $limit)
    {
        $sessionsToBrowse = [];

        $criteria = Criteria::create()
            ->where(
                Criteria::expr()->contains('name', $queryTerm)
            )
            ->setFirstResult($limit['start'])
            ->setMaxResults($limit['length']);

        $sessions = Database::getManager()
            ->getRepository('ChamiloCoreBundle:Session')
            ->matching($criteria);

        foreach ($sessions as $session) {
            if ($session->getNbrCourses() === 0) {
                continue;
            }

            $sessionsToBrowse[] = $session;
        }

        return $sessionsToBrowse;
    }

    /**
     * Search sessions by the tags in their courses.
     *
     * @param string $termTag Term for search in tags
     * @param array  $limit   Limit info
     *
     * @return array The sessions
     */
    public static function browseSessionsByTags($termTag, array $limit)
    {
        $em = Database::getManager();
        $qb = $em->createQueryBuilder();

        $sessions = $qb->select('s')
            ->distinct(true)
            ->from('ChamiloCoreBundle:Session', 's')
            ->innerJoin(
                'ChamiloCoreBundle:SessionRelCourse',
                'src',
                Join::WITH,
                's.id = src.session'
            )
            ->innerJoin(
                'ChamiloCoreBundle:ExtraFieldRelTag',
                'frt',
                Join::WITH,
                'src.course = frt.itemId'
            )
            ->innerJoin(
                'ChamiloCoreBundle:Tag',
                't',
                Join::WITH,
                'frt.tagId = t.id'
            )
            ->innerJoin(
                'ChamiloCoreBundle:ExtraField',
                'f',
                Join::WITH,
                'frt.fieldId = f.id'
            )
            ->where(
                $qb->expr()->like('t.tag', ":tag")
            )
            ->andWhere(
                $qb->expr()->eq('f.extraFieldType', ExtraField::COURSE_FIELD_TYPE)
            )
            ->setFirstResult($limit['start'])
            ->setMaxResults($limit['length'])
            ->setParameter('tag', "$termTag%")
            ->getQuery()
            ->getResult();

        $sessionsToBrowse = [];
        foreach ($sessions as $session) {
            if ($session->getNbrCourses() === 0) {
                continue;
            }
            $sessionsToBrowse[] = $session;
        }

        return $sessionsToBrowse;
    }
}


1			<?php
2			/* For licensing terms, see /license.txt */
3
4			use Chamilo\CoreBundle\Entity\ExtraField;
5			use Doctrine\Common\Collections\Criteria;
6			use Doctrine\ORM\Query\Expr\Join;
7
8			/**
9			* @todo change class name
10			* Class CoursesAndSessionsCatalog
11			*/
12			class CoursesAndSessionsCatalog
13			{
14			const PAGE_LENGTH = 12;
15
16			/**
17			* Check the configuration for the courses and sessions catalog.
18			*
19			* @global array $_configuration Configuration
20			*
21			* @param int $value The value to check
22			*
23			* @return bool Whether the configuration is $value
24			*/
25			public static function is($value = CATALOG_COURSES)
26			{
27			$showCoursesSessions = intval(api_get_setting('catalog_show_courses_sessions'));
28			if ($showCoursesSessions == $value) {
29			return true;
30			}
31
32			return false;
33			}
34
35			/**
36			* Check whether to display the sessions list.
37			*
38			* @global array $_configuration Configuration
39			*
40			* @return bool whether to display
41			*/
42			public static function showSessions()
43			{
44			$catalogShow = intval(api_get_setting('catalog_show_courses_sessions'));
45
46			if ($catalogShow == CATALOG_SESSIONS \|\| $catalogShow == CATALOG_COURSES_SESSIONS) {
47			return true;
48			}
49
50			return false;
51			}
52
53			/**
54			* Check whether to display the courses list.
55			*
56			* @global array $_configuration Configuration
57			*
58			* @return bool whether to display
59			*/
60			public static function showCourses()
61			{
62			$catalogShow = intval(api_get_setting('catalog_show_courses_sessions'));
63
64			if ($catalogShow == CATALOG_COURSES \|\| $catalogShow == CATALOG_COURSES_SESSIONS) {
65			return true;
66			}
67
68			return false;
69			}
70
71			/**
72			* @return array
73			*/
74			public static function getCoursesToAvoid()
75			{
76			$TABLE_COURSE_FIELD = Database::get_main_table(TABLE_EXTRA_FIELD);
77			$TABLE_COURSE_FIELD_VALUE = Database::get_main_table(TABLE_EXTRA_FIELD_VALUES);
78
79			// Check special courses
80			$courseListToAvoid = CourseManager::get_special_course_list();
81
82			// Checks "hide_from_catalog" extra field
83			$extraFieldType = ExtraField::COURSE_FIELD_TYPE;
84
85			$sql = "SELECT item_id FROM $TABLE_COURSE_FIELD_VALUE tcfv
86			INNER JOIN $TABLE_COURSE_FIELD tcf
87			ON tcfv.field_id = tcf.id
88			WHERE
89			tcf.extra_field_type = $extraFieldType AND
90			tcf.variable = 'hide_from_catalog' AND
91			tcfv.value = 1
92			";
93
94			$result = Database::query($sql);
95			if (Database::num_rows($result) > 0) {
96			while ($row = Database::fetch_array($result)) {
97			$courseListToAvoid[] = $row['item_id'];
98			}
99			}
100
101			return $courseListToAvoid;
102			}
103
104			/**
105			* @return string
106			*/
107			public static function getAvoidCourseCondition()
108			{
109			$courseListToAvoid = self::getCoursesToAvoid();
110			$condition = '';
111			if (!empty($courseListToAvoid)) {
112			$courses = [];
113			foreach ($courseListToAvoid as $courseId) {
114			$courses[] = '"'.$courseId.'"';
115			}
116			$condition = ' AND course.id NOT IN ('.implode(',', $courses).')';
117			}
118
119			return $condition;
120			}
121
122			/**
123			* Get available le courses count.
124			*
125			* @param int $accessUrlId (optional)
126			*
127			* @return int Number of courses
128			*/
129			public static function countAvailableCoursesToShowInCatalog($accessUrlId = 1)
130			{
131			$tableCourse = Database::get_main_table(TABLE_MAIN_COURSE);
132			$tableCourseRelAccessUrl = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
133			$courseToAvoidCondition = self::getAvoidCourseCondition();
134
135			$visibilityCondition = CourseManager::getCourseVisibilitySQLCondition('course', true);
136
137			$accessUrlId = (int) $accessUrlId;
138			if (empty($accessUrlId)) {
139			$accessUrlId = 1;
140			}
141
142			$sql = "SELECT count(course.id)
143			FROM $tableCourse course
144			INNER JOIN $tableCourseRelAccessUrl u
145			ON (course.id = u.c_id)
146			WHERE
147			u.access_url_id = $accessUrlId AND
148			course.visibility != 0 AND
149			course.visibility != 4
150			$courseToAvoidCondition
151			$visibilityCondition
152			";
153
154			$res = Database::query($sql);
155			$row = Database::fetch_row($res);
156
157			return $row[0];
158			}
159
160			/**
161			* @return array
162			*/
163			public static function getCourseCategories()
164			{
165			$urlId = 1;
166			if (api_is_multiple_url_enabled()) {
167			$urlId = api_get_current_access_url_id();
168			}
169
170			$countCourses = self::countAvailableCoursesToShowInCatalog($urlId);
171
172			$categories = [];
173			$categories[0][0] = [
174			'id' => 0,
175			'name' => get_lang('DisplayAll'),
176			'code' => 'ALL',
177			'parent_id' => null,
178			'tree_pos' => 0,
179			'count_courses' => $countCourses,
180			];
181
182			$categoriesFromDatabase = CourseCategory::getCategories();
183			foreach ($categoriesFromDatabase as $row) {
184			$count_courses = CourseCategory::countCoursesInCategory($row['code']);
185			$row['count_courses'] = $count_courses;
186			if (empty($row['parent_id'])) {
187			$categories[0][$row['tree_pos']] = $row;
188			} else {
189			$categories[$row['parent_id']][$row['tree_pos']] = $row;
190			}
191			}
192			$count_courses = CourseCategory::countCoursesInCategory();
193			$categories[0][count($categories[0]) + 1] = [
194			'id' => 0,
195			'name' => get_lang('None'),
196			'code' => 'NONE',
197			'parent_id' => null,
198			'tree_pos' => $row['tree_pos'] + 1,
199			'children_count' => 0,
200			'auth_course_child' => true,
201			'auth_cat_child' => true,
202			'count_courses' => $count_courses,
203			];
204
205			return $categories;
206			}
207
208			/**
209			* Return LIMIT to filter SQL query.
210			*
211			* @param array $limit
212			*
213			* @return string
214			*/
215			public static function getLimitFilterFromArray($limit)
216			{
217			$limitFilter = '';
218			if (!empty($limit) && is_array($limit)) {
219			$limitStart = isset($limit['start']) ? $limit['start'] : 0;
220			$limitLength = isset($limit['length']) ? $limit['length'] : 12;
221			$limitFilter = 'LIMIT '.$limitStart.', '.$limitLength;
222			}
223
224			return $limitFilter;
225			}
226
227			/**
228			* @param string $category_code
229			* @param int $random_value
230			* @param array $limit will be used if $random_value is not set.
231			* This array should contains 'start' and 'length' keys
232			*
233			* @return array
234			*/
235			public static function getCoursesInCategory($category_code, $random_value = null, $limit = [])
236			{
237			$tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
238			$avoidCoursesCondition = self::getAvoidCourseCondition();
239			$visibilityCondition = CourseManager::getCourseVisibilitySQLCondition(
240			'course',
241			true
242			);
243
244			if (!empty($random_value)) {
245			$random_value = intval($random_value);
246
247			$sql = "SELECT COUNT(*) FROM $tbl_course";
248			$result = Database::query($sql);
249			list($num_records) = Database::fetch_row($result);
250
251			if (api_is_multiple_url_enabled()) {
252			$url_access_id = api_get_current_access_url_id();
253			$tbl_url_rel_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
254
255			$sql = "SELECT COUNT(*) FROM $tbl_course course
256			INNER JOIN $tbl_url_rel_course as url_rel_course
257			ON (url_rel_course.c_id = course.id)
258			WHERE access_url_id = $url_access_id ";
259			$result = Database::query($sql);
260			list($num_records) = Database::fetch_row($result);
261
262			$sql = "SELECT course.id, course.id as real_id
263			FROM $tbl_course course
264			INNER JOIN $tbl_url_rel_course as url_rel_course
265			ON (url_rel_course.c_id = course.id)
266			WHERE
267			access_url_id = $url_access_id AND
268			RAND()*$num_records< $random_value
269			$avoidCoursesCondition
270			$visibilityCondition
271			ORDER BY RAND()
272			LIMIT 0, $random_value";
273			} else {
274			$sql = "SELECT id, id as real_id FROM $tbl_course course
275			WHERE
276			RAND()*$num_records< $random_value
277			$avoidCoursesCondition
278			$visibilityCondition
279			ORDER BY RAND()
280			LIMIT 0, $random_value";
281			}
282
283			$result = Database::query($sql);
284			$id_in = null;
285			while (list($id) = Database::fetch_row($result)) {
286			if ($id_in) {
287			$id_in .= ",$id";
288			} else {
289			$id_in = "$id";
290			}
291			}
292			if ($id_in === null) {
293			return [];
294			}
295			$sql = "SELECT *, id as real_id FROM $tbl_course WHERE id IN($id_in)";
296			} else {
297			$limitFilter = self::getLimitFilterFromArray($limit);
298			$category_code = Database::escape_string($category_code);
299			if (empty($category_code) \|\| $category_code == "ALL") {
300			$sql = "SELECT *, id as real_id
301			FROM $tbl_course course
302			WHERE
303			1=1
304			$avoidCoursesCondition
305			$visibilityCondition
306			ORDER BY title $limitFilter ";
307			} else {
308			if ($category_code == 'NONE') {
309			$category_code = '';
310			}
311			$sql = "SELECT *, id as real_id FROM $tbl_course course
312			WHERE
313			category_code='$category_code'
314			$avoidCoursesCondition
315			$visibilityCondition
316			ORDER BY title $limitFilter ";
317			}
318
319			// Showing only the courses of the current Chamilo access_url_id
320			if (api_is_multiple_url_enabled()) {
321			$url_access_id = api_get_current_access_url_id();
322			$tbl_url_rel_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
323			if ($category_code != "ALL") {
324			$sql = "SELECT *, course.id real_id FROM $tbl_course as course
325			INNER JOIN $tbl_url_rel_course as url_rel_course
326			ON (url_rel_course.c_id = course.id)
327			WHERE
328			access_url_id = $url_access_id AND
329			category_code='$category_code'
330			$avoidCoursesCondition
331			$visibilityCondition
332			ORDER BY title $limitFilter";
333			} else {
334			$sql = "SELECT *, course.id real_id FROM $tbl_course as course
335			INNER JOIN $tbl_url_rel_course as url_rel_course
336			ON (url_rel_course.c_id = course.id)
337			WHERE
338			access_url_id = $url_access_id
339			$avoidCoursesCondition
340			$visibilityCondition
341			ORDER BY title $limitFilter";
342			}
343			}
344			}
345
346			$result = Database::query($sql);
347			$courses = [];
348			while ($row = Database::fetch_array($result)) {
349			$row['registration_code'] = !empty($row['registration_code']);
350			$count_users = CourseManager::get_users_count_in_course($row['code']);
351			$count_connections_last_month = Tracking::get_course_connections_count(
352			$row['id'],
353			0,
354			api_get_utc_datetime(time() - (30 * 86400))
355			);
356
357			if ($row['tutor_name'] == '0') {
358			$row['tutor_name'] = get_lang('NoManager');
359			}
360			$point_info = CourseManager::get_course_ranking($row['id'], 0);
361			$courses[] = [
362			'real_id' => $row['real_id'],
363			'point_info' => $point_info,
364			'code' => $row['code'],
365			'directory' => $row['directory'],
366			'visual_code' => $row['visual_code'],
367			'title' => $row['title'],
368			'tutor' => $row['tutor_name'],
369			'subscribe' => $row['subscribe'],
370			'unsubscribe' => $row['unsubscribe'],
371			'registration_code' => $row['registration_code'],
372			'creation_date' => $row['creation_date'],
373			'visibility' => $row['visibility'],
374			'category' => $row['category_code'],
375			'count_users' => $count_users,
376			'count_connections' => $count_connections_last_month,
377			];
378			}
379
380			return $courses;
381			}
382
383			/**
384			* Search the courses database for a course that matches the search term.
385			* The search is done on the code, title and tutor field of the course table.
386			*
387			* @param string $search_term The string that the user submitted, what we are looking for
388			* @param array $limit
389			* @param bool $justVisible search only on visible courses in the catalogue
390			*
391			* @return array an array containing a list of all the courses matching the the search term
392			*/
393			public static function search_courses($search_term, $limit, $justVisible = false)
394			{
395			$courseTable = Database::get_main_table(TABLE_MAIN_COURSE);
396			$limitFilter = self::getLimitFilterFromArray($limit);
397			$avoidCoursesCondition = self::getAvoidCourseCondition();
398			$visibilityCondition = $justVisible ? CourseManager::getCourseVisibilitySQLCondition('course', true) : '';
399
400			$search_term_safe = Database::escape_string($search_term);
401			$sql_find = "SELECT * FROM $courseTable course
402			WHERE (
403			course.code LIKE '%".$search_term_safe."%' OR
404			course.title LIKE '%".$search_term_safe."%' OR
405			course.tutor_name LIKE '%".$search_term_safe."%'
406			)
407			$avoidCoursesCondition
408			$visibilityCondition
409			ORDER BY title, visual_code ASC
410			$limitFilter
411			";
412
413			if (api_is_multiple_url_enabled()) {
414			$url_access_id = api_get_current_access_url_id();
415			if ($url_access_id != -1) {
416			$tbl_url_rel_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
417			$sql_find = "SELECT *
418			FROM $courseTable as course
419			INNER JOIN $tbl_url_rel_course as url_rel_course
420			ON (url_rel_course.c_id = course.id)
421			WHERE
422			access_url_id = $url_access_id AND (
423			code LIKE '%".$search_term_safe."%' OR
424			title LIKE '%".$search_term_safe."%' OR
425			tutor_name LIKE '%".$search_term_safe."%'
426			)
427			$avoidCoursesCondition
428			$visibilityCondition
429			ORDER BY title, visual_code ASC
430			$limitFilter
431			";
432			}
433			}
434			$result_find = Database::query($sql_find);
435			$courses = [];
436			while ($row = Database::fetch_array($result_find)) {
437			$row['registration_code'] = !empty($row['registration_code']);
438			$count_users = count(CourseManager::get_user_list_from_course_code($row['code']));
439			$count_connections_last_month = Tracking::get_course_connections_count(
440			$row['id'],
441			0,
442			api_get_utc_datetime(time() - (30 * 86400))
443			);
444
445			$point_info = CourseManager::get_course_ranking($row['id'], 0);
446
447			$courses[] = [
448			'real_id' => $row['id'],
449			'point_info' => $point_info,
450			'code' => $row['code'],
451			'directory' => $row['directory'],
452			'visual_code' => $row['visual_code'],
453			'title' => $row['title'],
454			'tutor' => $row['tutor_name'],
455			'subscribe' => $row['subscribe'],
456			'unsubscribe' => $row['unsubscribe'],
457			'registration_code' => $row['registration_code'],
458			'creation_date' => $row['creation_date'],
459			'visibility' => $row['visibility'],
460			'count_users' => $count_users,
461			'count_connections' => $count_connections_last_month,
462			];
463			}
464
465			return $courses;
466			}
467
468			/**
469			* List the sessions.
470			*
471			* @param string $date (optional) The date of sessions
472			* @param array $limit
473			*
474			* @return array The session list
475			*/
476			public static function browseSessions($date = null, $limit = [])
477			{
478			$em = Database::getManager();
479			$urlId = api_get_current_access_url_id();
480
481			$sql = "SELECT s.id FROM session s ";
482			$sql .= "
483			INNER JOIN access_url_rel_session ars
484			ON s.id = ars.session_id
485			";
486
487			$sql .= "
488			WHERE s.nbr_courses > 0
489			AND ars.access_url_id = $urlId
490			";
491
492			if (!is_null($date)) {
493			$sql .= "
494			AND (
495			('$date' BETWEEN DATE(s.access_start_date) AND DATE(s.access_end_date))
496			OR (s.access_end_date IS NULL)
497			OR (
498			s.access_start_date IS NULL
499			AND s.access_end_date IS NOT NULL
500			AND DATE(s.access_end_date) >= '$date'
501			)
502			)
503			";
504			}
505
506			if (!empty($limit)) {
507			$sql .= "LIMIT {$limit['start']}, {$limit['length']} ";
508			}
509
510			$ids = Database::store_result(Database::query($sql));
511			$sessions = [];
512			foreach ($ids as $id) {
513			$sessions[] = $em->find('ChamiloCoreBundle:Session', $id);
514			}
515
516			return $sessions;
517			}
518
519			/**
520			* Search sessions by searched term by session name.
521			*
522			* @param string $queryTerm Term for search
523			* @param array $limit Limit info
524			*
525			* @return array The sessions
526			*/
527			public static function browseSessionsBySearch($queryTerm, array $limit)
528			{
529			$sessionsToBrowse = [];
530
531			$criteria = Criteria::create()
532			->where(
533			Criteria::expr()->contains('name', $queryTerm)
534			)
535			->setFirstResult($limit['start'])
536			->setMaxResults($limit['length']);
537
538			$sessions = Database::getManager()
539			->getRepository('ChamiloCoreBundle:Session')
540			->matching($criteria);
541
542			foreach ($sessions as $session) {
543			if ($session->getNbrCourses() === 0) {
544			continue;
545			}
546
547			$sessionsToBrowse[] = $session;
548			}
549
550			return $sessionsToBrowse;
551			}
552
553			/**
554			* Search sessions by the tags in their courses.
555			*
556			* @param string $termTag Term for search in tags
557			* @param array $limit Limit info
558			*
559			* @return array The sessions
560			*/
561			public static function browseSessionsByTags($termTag, array $limit)
562			{
563			$em = Database::getManager();
564			$qb = $em->createQueryBuilder();
565
566			$sessions = $qb->select('s')
567			->distinct(true)
568			->from('ChamiloCoreBundle:Session', 's')
569			->innerJoin(
570			'ChamiloCoreBundle:SessionRelCourse',
571			'src',
572			Join::WITH,
573			's.id = src.session'
574			)
575			->innerJoin(
576			'ChamiloCoreBundle:ExtraFieldRelTag',
577			'frt',
578			Join::WITH,
579			'src.course = frt.itemId'
580			)
581			->innerJoin(
582			'ChamiloCoreBundle:Tag',
583			't',
584			Join::WITH,
585			'frt.tagId = t.id'
586			)
587			->innerJoin(
588			'ChamiloCoreBundle:ExtraField',
589			'f',
590			Join::WITH,
591			'frt.fieldId = f.id'
592			)
593			->where(
594			$qb->expr()->like('t.tag', ":tag")
595			)
596			->andWhere(
597			$qb->expr()->eq('f.extraFieldType', ExtraField::COURSE_FIELD_TYPE)
598			)
599			->setFirstResult($limit['start'])
600			->setMaxResults($limit['length'])
601			->setParameter('tag', "$termTag%")
602			->getQuery()
603			->getResult();
604
605			$sessionsToBrowse = [];
606			foreach ($sessions as $session) {
607			if ($session->getNbrCourses() === 0) {
608			continue;
609			}
610			$sessionsToBrowse[] = $session;
611			}
612
613			return $sessionsToBrowse;
614			}
615			}
616

chamilo / chamilo-lms

Push — master ( ae5621...ef667c )

countAvailableCoursesToShowInCatalog() B

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like