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

Passed

Push — master ( 6f8cb0...cccadf )

by Julito

created 2019-10-21 09:04 UTC

CoursesAndSessionsCatalog::showCourses() A

↳ Parent: CoursesAndSessionsCatalog

Complexity

Conditions	3
Paths	2

Size

Total Lines	9
Code Lines	4

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	3
eloc	4
nc	2
nop	0
dl	0
loc	9
rs	10
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 = (int) 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 = (int) 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 = (int) 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];
    }

    public static function getCourseCategoriesTree()
    {
        $urlId = 1;
        if (api_is_multiple_url_enabled()) {
            $urlId = api_get_current_access_url_id();
        }

        $countCourses = self::countAvailableCoursesToShowInCatalog($urlId);
        $categories = [];
        $list = [];

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

        $allCategories = CourseCategory::getAllCategories();

        foreach ($allCategories as $category) {
            if (empty($category['parent_id'])) {
                $list[$category['code']] = $category;
                $list[$category['code']]['level'] = 0;
                list($subList, $childrenCount) = self::buildCourseCategoryTree($allCategories, $category['code'], 0);
                foreach ($subList as $item) {
                    $list[$item['code']] = $item;
                }
                // Real course count
                $countCourses = CourseCategory::countCoursesInCategory($category['code']);
                $list[$category['code']]['number_courses'] = $childrenCount + $countCourses;
            }
        }

        // count courses that are in no category
        $countCourses = CourseCategory::countCoursesInCategory();
        $categories['NONE'] = [
            'id' => 0,
            'name' => get_lang('WithoutCategory'),
            'code' => 'NONE',
            'parent_id' => null,
            'tree_pos' => 0,
            'children_count' => 0,
            'auth_course_child' => true,
            'auth_cat_child' => true,
            'number_courses' => $countCourses,
            'level' => 0,
        ];

        $result = array_merge($list, $categories);

        return $result;
    }

    /**
     * @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) {
            $countCourses = CourseCategory::countCoursesInCategory($row['code']);
            $row['count_courses'] = $countCourses;
            if (empty($row['parent_id'])) {
                $categories[0][$row['tree_pos']] = $row;
            } else {
                $categories[$row['parent_id']][$row['tree_pos']] = $row;
            }
        }

        // count courses that are in no category
        $countCourses = 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' => $countCourses,
        ];

        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']) ? (int) $limit['start'] : 0;
            $limitLength = isset($limit['length']) ? (int) $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 = (int) $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()) {
                $urlId = api_get_current_access_url_id();
                $tbl_url_rel_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);

                $urlCondition = ' access_url_id = '.$urlId.' ';
                $allowBaseCategories = api_get_configuration_value('allow_base_course_category');
                if ($allowBaseCategories) {
                    $urlCondition = ' (access_url_id = '.$urlId.' OR access_url_id = 1)  ';
                }

                $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 = '.$urlId.' ";
                $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
                            $urlCondition 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);
            $listCode = self::childrenCategories($category_code);
            $conditionCode = ' ';

            if (empty($listCode)) {
                if ($category_code === 'NONE') {
                    $conditionCode .= " category_code='' ";
                } else {
                    $conditionCode .= " category_code='$category_code' ";
                }
            } else {
                foreach ($listCode as $code) {
                    $conditionCode .= " category_code='$code' OR ";
                }
                $conditionCode .= " category_code='$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 {
                $sql = "SELECT *, id as real_id FROM $tbl_course course
                        WHERE
                            $conditionCode 
                            $avoidCoursesCondition
                            $visibilityCondition
                        ORDER BY title $limitFilter ";
            }

            // Showing only the courses of the current Chamilo access_url_id
            if (api_is_multiple_url_enabled()) {
                $urlId = api_get_current_access_url_id();
                $tbl_url_rel_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);

                $urlCondition = ' access_url_id = '.$urlId.' ';
                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
                                $urlCondition AND
                                $conditionCode
                                $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
                                $urlCondition
                                $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 = "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()) {
            $urlId = api_get_current_access_url_id();
            if ($urlId != -1) {
                $tbl_url_rel_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);

                $urlCondition = ' access_url_id = '.$urlId.' AND';
                $allowBaseCategories = api_get_configuration_value('allow_base_course_category');
                if ($allowBaseCategories) {
                    $urlCondition = ' (access_url_id = '.$urlId.' OR access_url_id = 1) AND ';
                }

                $sql = "SELECT course.*
                        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 = $urlId 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);
        $courses = [];
        while ($row = Database::fetch_array($result_find)) {
            $row['registration_code'] = !empty($row['registration_code']);
            $countUsers = CourseManager::get_user_list_from_course_code(
                $row['code'],
                0,
                null,
                null,
                null,
                true
            );
            $connectionsLastMonth = 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' => $countUsers,
                'count_connections' => $connectionsLastMonth,
            ];
        }

        return $courses;
    }

    /**
     * List the sessions.
     *
     * @param string $date
     * @param array  $limit
     * @param bool   $returnQueryBuilder
     * @param bool   $getCount
     *
     * @return array|\Doctrine\ORM\Query The session list
     */
    public static function browseSessions($date = null, $limit = [], $returnQueryBuilder = false, $getCount = false)
    {
        $urlId = api_get_current_access_url_id();

        $select = 's';
        if ($getCount) {
            $select = 'count(s) ';
        }

        $dql = "SELECT $select
                FROM ChamiloCoreBundle:Session s
                WHERE EXISTS 
                    (
                        SELECT url.sessionId FROM ChamiloCoreBundle:AccessUrlRelSession url 
                        WHERE url.sessionId = s.id AND url.accessUrlId = $urlId
                    ) AND
                    s.nbrCourses > 0
                ";
        if (!is_null($date)) {
            $date = Database::escape_string($date);
            $dql .= "
                AND (
                    (s.accessEndDate IS NULL) 
                    OR
                    ( 
                    s.accessStartDate IS NOT NULL AND  
                    s.accessEndDate IS NOT NULL AND
                    s.accessStartDate >= '$date' AND s.accessEndDate <= '$date')                    
                    OR 
                    (
                        s.accessStartDate IS NULL AND 
                        s.accessEndDate IS NOT NULL AND 
                        s.accessStartDate >= '$date'
                    )
                )
            ";
        }

        $qb = Database::getManager()->createQuery($dql);

        if (!empty($limit)) {
            $qb
                ->setFirstResult($limit['start'])
                ->setMaxResults($limit['length'])
            ;
        }

        if ($returnQueryBuilder) {
            return $qb;
        }

        if ($getCount) {
            return $qb->getSingleScalarResult();
    }

        return $qb->getResult();
    }

    /**
     * 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();

        $urlId = api_get_current_access_url_id();

        $sessions = $qb->select('s')
            ->distinct()
            ->from('ChamiloCoreBundle:Session', 's')
            ->innerJoin(
                'ChamiloCoreBundle:SessionRelCourse',
                'src',
                Join::WITH,
                's.id = src.session'
            )
            ->innerJoin(
                'ChamiloCoreBundle:AccessUrlRelSession',
                'url',
                Join::WITH,
                'url.sessionId = s.id'
            )
            ->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)
            )->andWhere(
                $qb->expr()->eq('url.accessUrlId', $urlId)
            )
            ->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;
    }

    /**
     * Build a recursive tree of course categories.
     *
     * @param array $categories
     * @param int   $parentId
     * @param int   $level
     *
     * @return array
     */
    public static function buildCourseCategoryTree($categories, $parentId = 0, $level = 0)
    {
        $list = [];
        $count = 0;
        $level++;
        foreach ($categories as $category) {
            if (empty($category['parent_id'])) {
                continue;
            }
            if ($category['parent_id'] == $parentId) {
                $list[$category['code']] = $category;
                $count += $category['number_courses'];
                $list[$category['code']]['level'] = $level;
                list($subList, $childrenCount) = self::buildCourseCategoryTree(
                    $categories,
                    $category['code'],
                    $level
                );
                $list[$category['code']]['number_courses'] += $childrenCount;
                foreach ($subList as $item) {
                    $list[$item['code']] = $item;
                }
                $count += $childrenCount;
            }
        }

        return [$list, $count];
    }

    /**
     * List Code Search Category.
     *
     * @param string $code
     *
     * @return array
     */
    public static function childrenCategories($code)
    {
        $allCategories = CourseCategory::getAllCategories();
        $list = [];
        $row = [];

        if ($code != 'ALL' and $code != 'NONE') {
            foreach ($allCategories as $category) {
                if ($category['code'] === $code) {
                    $list = self::buildCourseCategoryTree($allCategories, $category['code'], 0);
                }
            }
            foreach ($list[0] as $item) {
                $row[] = $item['code'];
            }
        }

        return $row;
    }
}


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 = (int) 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 = (int) 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 = (int) 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			$visibilityCondition = CourseManager::getCourseVisibilitySQLCondition('course', true);
135
136			$accessUrlId = (int) $accessUrlId;
137			if (empty($accessUrlId)) {
138			$accessUrlId = 1;
139			}
140
141			$sql = "SELECT count(course.id)
142			FROM $tableCourse course
143			INNER JOIN $tableCourseRelAccessUrl u
144			ON (course.id = u.c_id)
145			WHERE
146			u.access_url_id = $accessUrlId AND
147			course.visibility != 0 AND
148			course.visibility != 4
149			$courseToAvoidCondition
150			$visibilityCondition
151			";
152
153			$res = Database::query($sql);
154			$row = Database::fetch_row($res);
155
156			return $row[0];
157			}
158
159			public static function getCourseCategoriesTree()
160			{
161			$urlId = 1;
162			if (api_is_multiple_url_enabled()) {
163			$urlId = api_get_current_access_url_id();
164			}
165
166			$countCourses = self::countAvailableCoursesToShowInCatalog($urlId);
167			$categories = [];
168			$list = [];
169
170			$categories['ALL'] = [
171			'id' => 0,
172			'name' => get_lang('DisplayAll'),
173			'code' => 'ALL',
174			'parent_id' => null,
175			'tree_pos' => 0,
176			'number_courses' => $countCourses,
177			'level' => 0,
178			];
179
180			$allCategories = CourseCategory::getAllCategories();
181
182			foreach ($allCategories as $category) {
183			if (empty($category['parent_id'])) {
184			$list[$category['code']] = $category;
185			$list[$category['code']]['level'] = 0;
186			list($subList, $childrenCount) = self::buildCourseCategoryTree($allCategories, $category['code'], 0);
187			foreach ($subList as $item) {
188			$list[$item['code']] = $item;
189			}
190			// Real course count
191			$countCourses = CourseCategory::countCoursesInCategory($category['code']);
192			$list[$category['code']]['number_courses'] = $childrenCount + $countCourses;
193			}
194			}
195
196			// count courses that are in no category
197			$countCourses = CourseCategory::countCoursesInCategory();
198			$categories['NONE'] = [
199			'id' => 0,
200			'name' => get_lang('WithoutCategory'),
201			'code' => 'NONE',
202			'parent_id' => null,
203			'tree_pos' => 0,
204			'children_count' => 0,
205			'auth_course_child' => true,
206			'auth_cat_child' => true,
207			'number_courses' => $countCourses,
208			'level' => 0,
209			];
210
211			$result = array_merge($list, $categories);
212
213			return $result;
214			}
215
216			/**
217			* @return array
218			*/
219			public static function getCourseCategories()
220			{
221			$urlId = 1;
222			if (api_is_multiple_url_enabled()) {
223			$urlId = api_get_current_access_url_id();
224			}
225
226			$countCourses = self::countAvailableCoursesToShowInCatalog($urlId);
227
228			$categories = [];
229			$categories[0][0] = [
230			'id' => 0,
231			'name' => get_lang('DisplayAll'),
232			'code' => 'ALL',
233			'parent_id' => null,
234			'tree_pos' => 0,
235			'count_courses' => $countCourses,
236			];
237
238			$categoriesFromDatabase = CourseCategory::getCategories();
239
240			foreach ($categoriesFromDatabase as $row) {
241			$countCourses = CourseCategory::countCoursesInCategory($row['code']);
242			$row['count_courses'] = $countCourses;
243			if (empty($row['parent_id'])) {
244			$categories[0][$row['tree_pos']] = $row;
245			} else {
246			$categories[$row['parent_id']][$row['tree_pos']] = $row;
247			}
248			}
249
250			// count courses that are in no category
251			$countCourses = CourseCategory::countCoursesInCategory();
252			$categories[0][count($categories[0]) + 1] = [
253			'id' => 0,
254			'name' => get_lang('None'),
255			'code' => 'NONE',
256			'parent_id' => null,
257			'tree_pos' => $row['tree_pos'] + 1,
258			'children_count' => 0,
259			'auth_course_child' => true,
260			'auth_cat_child' => true,
261			'count_courses' => $countCourses,
262			];
263
264			return $categories;
265			}
266
267			/**
268			* Return LIMIT to filter SQL query.
269			*
270			* @param array $limit
271			*
272			* @return string
273			*/
274			public static function getLimitFilterFromArray($limit)
275			{
276			$limitFilter = '';
277			if (!empty($limit) && is_array($limit)) {
278			$limitStart = isset($limit['start']) ? (int) $limit['start'] : 0;
279			$limitLength = isset($limit['length']) ? (int) $limit['length'] : 12;
280			$limitFilter = 'LIMIT '.$limitStart.', '.$limitLength;
281			}
282
283			return $limitFilter;
284			}
285
286			/**
287			* @param string $category_code
288			* @param int $random_value
289			* @param array $limit will be used if $random_value is not set.
290			* This array should contains 'start' and 'length' keys
291			*
292			* @return array
293			*/
294			public static function getCoursesInCategory($category_code, $random_value = null, $limit = [])
295			{
296			$tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
297			$avoidCoursesCondition = self::getAvoidCourseCondition();
298			$visibilityCondition = CourseManager::getCourseVisibilitySQLCondition('course', true);
299
300			if (!empty($random_value)) {
301			$random_value = (int) $random_value;
302
303			$sql = "SELECT COUNT(*) FROM $tbl_course";
304			$result = Database::query($sql);
305			list($num_records) = Database::fetch_row($result);
306
307			if (api_is_multiple_url_enabled()) {
308			$urlId = api_get_current_access_url_id();
309			$tbl_url_rel_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
310
311			$urlCondition = ' access_url_id = '.$urlId.' ';
312			$allowBaseCategories = api_get_configuration_value('allow_base_course_category');
313			if ($allowBaseCategories) {
314			$urlCondition = ' (access_url_id = '.$urlId.' OR access_url_id = 1) ';
315			}
316
317			$sql = "SELECT COUNT(*) FROM $tbl_course course
318			INNER JOIN $tbl_url_rel_course as url_rel_course
319			ON (url_rel_course.c_id = course.id)
320			WHERE access_url_id = '.$urlId.' ";
321			$result = Database::query($sql);
322			list($num_records) = Database::fetch_row($result);
323
324			$sql = "SELECT course.id, course.id as real_id
325			FROM $tbl_course course
326			INNER JOIN $tbl_url_rel_course as url_rel_course
327			ON (url_rel_course.c_id = course.id)
328			WHERE
329			$urlCondition AND
330			RAND()*$num_records< $random_value
331			$avoidCoursesCondition
332			$visibilityCondition
333			ORDER BY RAND()
334			LIMIT 0, $random_value";
335			} else {
336			$sql = "SELECT id, id as real_id FROM $tbl_course course
337			WHERE
338			RAND()*$num_records< $random_value
339			$avoidCoursesCondition
340			$visibilityCondition
341			ORDER BY RAND()
342			LIMIT 0, $random_value";
343			}
344
345			$result = Database::query($sql);
346			$id_in = null;
347			while (list($id) = Database::fetch_row($result)) {
348			if ($id_in) {
349			$id_in .= ",$id";
350			} else {
351			$id_in = "$id";
352			}
353			}
354			if ($id_in === null) {
355			return [];
356			}
357			$sql = "SELECT *, id as real_id FROM $tbl_course WHERE id IN($id_in)";
358			} else {
359			$limitFilter = self::getLimitFilterFromArray($limit);
360			$category_code = Database::escape_string($category_code);
361			$listCode = self::childrenCategories($category_code);
362			$conditionCode = ' ';
363
364			if (empty($listCode)) {
365			if ($category_code === 'NONE') {
366			$conditionCode .= " category_code='' ";
367			} else {
368			$conditionCode .= " category_code='$category_code' ";
369			}
370			} else {
371			foreach ($listCode as $code) {
372			$conditionCode .= " category_code='$code' OR ";
373			}
374			$conditionCode .= " category_code='$category_code' ";
375			}
376
377			if (empty($category_code) \|\| $category_code == 'ALL') {
378			$sql = "SELECT *, id as real_id
379			FROM $tbl_course course
380			WHERE
381			1=1
382			$avoidCoursesCondition
383			$visibilityCondition
384			ORDER BY title $limitFilter ";
385			} else {
386			$sql = "SELECT *, id as real_id FROM $tbl_course course
387			WHERE
388			$conditionCode
389			$avoidCoursesCondition
390			$visibilityCondition
391			ORDER BY title $limitFilter ";
392			}
393
394			// Showing only the courses of the current Chamilo access_url_id
395			if (api_is_multiple_url_enabled()) {
396			$urlId = api_get_current_access_url_id();
397			$tbl_url_rel_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
398
399			$urlCondition = ' access_url_id = '.$urlId.' ';
400			if ($category_code != 'ALL') {
401			$sql = "SELECT *, course.id real_id FROM $tbl_course as course
402			INNER JOIN $tbl_url_rel_course as url_rel_course
403			ON (url_rel_course.c_id = course.id)
404			WHERE
405			$urlCondition AND
406			$conditionCode
407			$avoidCoursesCondition
408			$visibilityCondition
409			ORDER BY title $limitFilter";
410			} else {
411			$sql = "SELECT *, course.id real_id FROM $tbl_course as course
412			INNER JOIN $tbl_url_rel_course as url_rel_course
413			ON (url_rel_course.c_id = course.id)
414			WHERE
415			$urlCondition
416			$avoidCoursesCondition
417			$visibilityCondition
418			ORDER BY title $limitFilter";
419			}
420			}
421			}
422
423			$result = Database::query($sql);
424			$courses = [];
425			while ($row = Database::fetch_array($result)) {
426			$row['registration_code'] = !empty($row['registration_code']);
427			$count_users = CourseManager::get_users_count_in_course($row['code']);
428			$count_connections_last_month = Tracking::get_course_connections_count(
429			$row['id'],
430			0,
431			api_get_utc_datetime(time() - (30 * 86400))
432			);
433
434			if ($row['tutor_name'] == '0') {
435			$row['tutor_name'] = get_lang('NoManager');
436			}
437			$point_info = CourseManager::get_course_ranking($row['id'], 0);
438			$courses[] = [
439			'real_id' => $row['real_id'],
440			'point_info' => $point_info,
441			'code' => $row['code'],
442			'directory' => $row['directory'],
443			'visual_code' => $row['visual_code'],
444			'title' => $row['title'],
445			'tutor' => $row['tutor_name'],
446			'subscribe' => $row['subscribe'],
447			'unsubscribe' => $row['unsubscribe'],
448			'registration_code' => $row['registration_code'],
449			'creation_date' => $row['creation_date'],
450			'visibility' => $row['visibility'],
451			'category' => $row['category_code'],
452			'count_users' => $count_users,
453			'count_connections' => $count_connections_last_month,
454			];
455			}
456
457			return $courses;
458			}
459
460			/**
461			* Search the courses database for a course that matches the search term.
462			* The search is done on the code, title and tutor field of the course table.
463			*
464			* @param string $search_term The string that the user submitted, what we are looking for
465			* @param array $limit
466			* @param bool $justVisible search only on visible courses in the catalogue
467			*
468			* @return array an array containing a list of all the courses matching the the search term
469			*/
470			public static function search_courses($search_term, $limit, $justVisible = false)
471			{
472			$courseTable = Database::get_main_table(TABLE_MAIN_COURSE);
473			$limitFilter = self::getLimitFilterFromArray($limit);
474			$avoidCoursesCondition = self::getAvoidCourseCondition();
475			$visibilityCondition = $justVisible ? CourseManager::getCourseVisibilitySQLCondition('course', true) : '';
476			$search_term_safe = Database::escape_string($search_term);
477			$sql = "SELECT * FROM $courseTable course
478			WHERE (
479			course.code LIKE '%".$search_term_safe."%' OR
480			course.title LIKE '%".$search_term_safe."%' OR
481			course.tutor_name LIKE '%".$search_term_safe."%'
482			)
483			$avoidCoursesCondition
484			$visibilityCondition
485			ORDER BY title, visual_code ASC
486			$limitFilter
487			";
488
489			if (api_is_multiple_url_enabled()) {
490			$urlId = api_get_current_access_url_id();
491			if ($urlId != -1) {
492			$tbl_url_rel_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
493
494			$urlCondition = ' access_url_id = '.$urlId.' AND';
495			$allowBaseCategories = api_get_configuration_value('allow_base_course_category');
496			if ($allowBaseCategories) {
497			$urlCondition = ' (access_url_id = '.$urlId.' OR access_url_id = 1) AND ';
498			}
499
500			$sql = "SELECT course.*
501			FROM $courseTable as course
502			INNER JOIN $tbl_url_rel_course as url_rel_course
503			ON (url_rel_course.c_id = course.id)
504			WHERE
505			access_url_id = $urlId AND
506			(
507			code LIKE '%".$search_term_safe."%' OR
508			title LIKE '%".$search_term_safe."%' OR
509			tutor_name LIKE '%".$search_term_safe."%'
510			)
511			$avoidCoursesCondition
512			$visibilityCondition
513			ORDER BY title, visual_code ASC
514			$limitFilter
515			";
516			}
517			}
518			$result_find = Database::query($sql);
519			$courses = [];
520			while ($row = Database::fetch_array($result_find)) {
521			$row['registration_code'] = !empty($row['registration_code']);
522			$countUsers = CourseManager::get_user_list_from_course_code(
523			$row['code'],
524			0,
525			null,
526			null,
527			null,
528			true
529			);
530			$connectionsLastMonth = Tracking::get_course_connections_count(
531			$row['id'],
532			0,
533			api_get_utc_datetime(time() - (30 * 86400))
534			);
535
536			$point_info = CourseManager::get_course_ranking($row['id'], 0);
537
538			$courses[] = [
539			'real_id' => $row['id'],
540			'point_info' => $point_info,
541			'code' => $row['code'],
542			'directory' => $row['directory'],
543			'visual_code' => $row['visual_code'],
544			'title' => $row['title'],
545			'tutor' => $row['tutor_name'],
546			'subscribe' => $row['subscribe'],
547			'unsubscribe' => $row['unsubscribe'],
548			'registration_code' => $row['registration_code'],
549			'creation_date' => $row['creation_date'],
550			'visibility' => $row['visibility'],
551			'count_users' => $countUsers,
552			'count_connections' => $connectionsLastMonth,
553			];
554			}
555
556			return $courses;
557			}
558
559			/**
560			* List the sessions.
561			*
562			* @param string $date
563			* @param array $limit
564			* @param bool $returnQueryBuilder
565			* @param bool $getCount
566			*
567			* @return array\|\Doctrine\ORM\Query The session list
568			*/
569			public static function browseSessions($date = null, $limit = [], $returnQueryBuilder = false, $getCount = false)
570			{
571			$urlId = api_get_current_access_url_id();
572
573			$select = 's';
574			if ($getCount) {
575			$select = 'count(s) ';
576			}
577
578			$dql = "SELECT $select
579			FROM ChamiloCoreBundle:Session s
580			WHERE EXISTS
581			(
582			SELECT url.sessionId FROM ChamiloCoreBundle:AccessUrlRelSession url
583			WHERE url.sessionId = s.id AND url.accessUrlId = $urlId
584			) AND
585			s.nbrCourses > 0
586			";
587			if (!is_null($date)) {
588			$date = Database::escape_string($date);
589			$dql .= "
590			AND (
591			(s.accessEndDate IS NULL)
592			OR
593			(
594			s.accessStartDate IS NOT NULL AND
595			s.accessEndDate IS NOT NULL AND
596			s.accessStartDate >= '$date' AND s.accessEndDate <= '$date')
597			OR
598			(
599			s.accessStartDate IS NULL AND
600			s.accessEndDate IS NOT NULL AND
601			s.accessStartDate >= '$date'
602			)
603			)
604			";
605			}
606
607			$qb = Database::getManager()->createQuery($dql);
608
609			if (!empty($limit)) {
610			$qb
611			->setFirstResult($limit['start'])
612			->setMaxResults($limit['length'])
613			;
614			}
615
616			if ($returnQueryBuilder) {
617			return $qb;
618			}
619
620			if ($getCount) {
621			return $qb->getSingleScalarResult();
622			}
623
624			return $qb->getResult();
625			}
626
627			/**
628			* Search sessions by the tags in their courses.
629			*
630			* @param string $termTag Term for search in tags
631			* @param array $limit Limit info
632			*
633			* @return array The sessions
634			*/
635			public static function browseSessionsByTags($termTag, array $limit)
636			{
637			$em = Database::getManager();
638			$qb = $em->createQueryBuilder();
639
640			$urlId = api_get_current_access_url_id();
641
642			$sessions = $qb->select('s')
643			->distinct()
644			->from('ChamiloCoreBundle:Session', 's')
645			->innerJoin(
646			'ChamiloCoreBundle:SessionRelCourse',
647			'src',
648			Join::WITH,
649			's.id = src.session'
650			)
651			->innerJoin(
652			'ChamiloCoreBundle:AccessUrlRelSession',
653			'url',
654			Join::WITH,
655			'url.sessionId = s.id'
656			)
657			->innerJoin(
658			'ChamiloCoreBundle:ExtraFieldRelTag',
659			'frt',
660			Join::WITH,
661			'src.course = frt.itemId'
662			)
663			->innerJoin(
664			'ChamiloCoreBundle:Tag',
665			't',
666			Join::WITH,
667			'frt.tagId = t.id'
668			)
669			->innerJoin(
670			'ChamiloCoreBundle:ExtraField',
671			'f',
672			Join::WITH,
673			'frt.fieldId = f.id'
674			)
675			->where(
676			$qb->expr()->like('t.tag', ':tag')
677			)
678			->andWhere(
679			$qb->expr()->eq('f.extraFieldType', ExtraField::COURSE_FIELD_TYPE)
680			)->andWhere(
681			$qb->expr()->eq('url.accessUrlId', $urlId)
682			)
683			->setFirstResult($limit['start'])
684			->setMaxResults($limit['length'])
685			->setParameter('tag', "$termTag%")
686			->getQuery()
687			->getResult();
688
689			$sessionsToBrowse = [];
690			foreach ($sessions as $session) {
691			if ($session->getNbrCourses() === 0) {
692			continue;
693			}
694			$sessionsToBrowse[] = $session;
695			}
696
697			return $sessionsToBrowse;
698			}
699
700			/**
701			* Build a recursive tree of course categories.
702			*
703			* @param array $categories
704			* @param int $parentId
705			* @param int $level
706			*
707			* @return array
708			*/
709			public static function buildCourseCategoryTree($categories, $parentId = 0, $level = 0)
710			{
711			$list = [];
712			$count = 0;
713			$level++;
714			foreach ($categories as $category) {
715			if (empty($category['parent_id'])) {
716			continue;
717			}
718			if ($category['parent_id'] == $parentId) {
719			$list[$category['code']] = $category;
720			$count += $category['number_courses'];
721			$list[$category['code']]['level'] = $level;
722			list($subList, $childrenCount) = self::buildCourseCategoryTree(
723			$categories,
724			$category['code'],
725			$level
726			);
727			$list[$category['code']]['number_courses'] += $childrenCount;
728			foreach ($subList as $item) {
729			$list[$item['code']] = $item;
730			}
731			$count += $childrenCount;
732			}
733			}
734
735			return [$list, $count];
736			}
737
738			/**
739			* List Code Search Category.
740			*
741			* @param string $code
742			*
743			* @return array
744			*/
745			public static function childrenCategories($code)
746			{
747			$allCategories = CourseCategory::getAllCategories();
748			$list = [];
749			$row = [];
750
751			if ($code != 'ALL' and $code != 'NONE') {
752			foreach ($allCategories as $category) {
753			if ($category['code'] === $code) {
754			$list = self::buildCourseCategoryTree($allCategories, $category['code'], 0);
755			}
756			}
757			foreach ($list[0] as $item) {
758			$row[] = $item['code'];
759			}
760			}
761
762			return $row;
763			}
764			}
765

chamilo / chamilo-lms

Push — master ( 6f8cb0...cccadf )

CoursesAndSessionsCatalog::showCourses() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like