get_course_data()

Complexity

Conditions	29
Paths	14592

Size

Total Lines	262
Code Lines	164

Duplication

Lines	0
Ratio	0 %

Importance

Changes

0

<?php
function get_course_data(
    int $from,
    int $number_of_items,
    int $column,
    string $direction,
    array $dataFunctions = [],
    bool $getCount = false
): int|array {
    $course_table = Database::get_main_table(TABLE_MAIN_COURSE);

    if (!in_array(strtolower($direction), ['asc', 'desc'])) {
        $direction = 'desc';
    }

    $tblCourseCategory = Database::get_main_table(TABLE_MAIN_CATEGORY);
    $tblCourseRelCategory = Database::get_main_table(TABLE_MAIN_COURSE_REL_CATEGORY);

    $select = 'SELECT
                course.code AS col0,
                course.title AS col1,
                course.code AS col2,
                course_language AS col3,
                subscribe AS col5,
                unsubscribe AS col6,
                course.code AS col7,
                course.visibility AS col8,
                directory as col9,
                visual_code,
                directory,
                course.id';

    if ($getCount) {
        $select = 'SELECT COUNT(DISTINCT(course.id)) as count ';
    }

    $sql = "$select FROM $course_table course ";

    if (!empty($_GET['keyword_category'])) {
        $sql .= "INNER JOIN $tblCourseRelCategory course_rel_category ON course.id = course_rel_category.course_id
            INNER JOIN $tblCourseCategory category ON course_rel_category.course_category_id = category.id ";
    }

    if ((api_is_platform_admin() || api_is_session_admin())
        && api_is_multiple_url_enabled() && -1 != api_get_current_access_url_id()

The function api_is_multiple_url_enabled() has been deprecated: Use AccessUrlUtil::isMultiple

    ) {
        $access_url_rel_course_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
        $sql .= " INNER JOIN $access_url_rel_course_table url_rel_course
                 ON (course.id = url_rel_course.c_id)";
    }

    if (!empty($_GET['session_id'])) {
        $session_rel_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
        $session = Database::get_main_table(TABLE_MAIN_SESSION);

        $sql .= " INNER JOIN $session_rel_course r ON course.id = r.c_id
            INNER JOIN $session s ON r.session_id = s.id ";
    }

    if (isset($_GET['keyword'])) {
        $keyword = Database::escape_string('%'.trim($_GET['keyword']).'%');
        $sql .= " WHERE (
            course.title LIKE '".$keyword."' OR
            course.code LIKE '".$keyword."' OR
            visual_code LIKE '".$keyword."'
        )
        ";
    } elseif (isset($_GET['keyword_code'])) {
        $keyword_code = Database::escape_string('%'.$_GET['keyword_code'].'%');
        $keyword_title = Database::escape_string('%'.$_GET['keyword_title'].'%');
        $keyword_category = isset($_GET['keyword_category'])
            ? Database::escape_string($_GET['keyword_category'])
            : null;
        $keyword_language = Database::escape_string('%'.$_GET['keyword_language'].'%');
        $keyword_visibility = Database::escape_string('%'.$_GET['keyword_visibility'].'%');
        $keyword_subscribe = Database::escape_string($_GET['keyword_subscribe']);
        $keyword_unsubscribe = Database::escape_string($_GET['keyword_unsubscribe']);

        $sql .= " WHERE
                (course.code LIKE '".$keyword_code."' OR visual_code LIKE '".$keyword_code."') AND
                course.title LIKE '".$keyword_title."' AND
                course_language LIKE '".$keyword_language."' AND
                visibility LIKE '".$keyword_visibility."' AND
                subscribe LIKE '".$keyword_subscribe."' AND
                unsubscribe LIKE '".$keyword_unsubscribe."'";

        if (!empty($keyword_category)) {
            $sql .= ' AND category.id = '.$keyword_category.' ';
        }
    }

    // Adding the filter to see the user's only of the current access_url.
    if ((api_is_platform_admin() || api_is_session_admin())
        && api_is_multiple_url_enabled() && -1 != api_get_current_access_url_id()

The function api_is_multiple_url_enabled() has been deprecated: Use AccessUrlUtil::isMultiple

    ) {
        $sql .= ' AND url_rel_course.access_url_id='.api_get_current_access_url_id();
    }

    if (!empty($_GET['session_id'])) {
        $sessionId = (int) $_GET['session_id'];
        $sql .= ' AND s.id = '.$sessionId.' ';
    }

    if ($getCount) {
        $res = Database::query($sql);
        $row = Database::fetch_array($res);
        if ($row) {
            return (int) $row['count'];
        }

        return 0;
    }

    $sql .= ' GROUP BY course.code';
    $sql .= " ORDER BY col$column $direction ";
    $sql .= " LIMIT $from, $number_of_items";

    $res = Database::query($sql);
    $courses = [];
    $languages = api_get_languages();

    $path = api_get_path(WEB_CODE_PATH);

    while ($course = Database::fetch_array($res)) {
        $courseInfo = api_get_course_info_by_id($course['id']);

        // get categories
        $sqlCategoriesByCourseId = "SELECT category.title FROM $tblCourseCategory category
            INNER JOIN $tblCourseRelCategory course_rel_category ON category.id = course_rel_category.course_category_id
            WHERE course_rel_category.course_id = ".$course['id'];
        $resultCategories = Database::query($sqlCategoriesByCourseId);
        $categories = [];

        while ($category = Database::fetch_array($resultCategories)) {
            $categories[] = $category['title'];
        }

        // Place colour icons in front of courses.
        $show_visual_code = $course['visual_code'] != $course['col2'] ? Display::label($course['visual_code'], 'info') : null;
        $course['col1'] = get_course_visibility_icon($courseInfo['visibility']).\PHP_EOL
            .Display::url(Security::remove_XSS($course['col1']), $courseInfo['course_public_url']).\PHP_EOL
            .$show_visual_code;
        $course['col5'] = SUBSCRIBE_ALLOWED == $course['col5'] ? get_lang('Yes') : get_lang('No');
        $course['col6'] = UNSUBSCRIBE_ALLOWED == $course['col6'] ? get_lang('Yes') : get_lang('No');

        $courseId = $course['id'];

        $actions = [];
        $actions[] = Display::url(
            Display::getMdiIcon(
                ActionIcon::INFORMATION,
                'ch-tool-icon',
                null,
                ICON_SIZE_SMALL,
                get_lang('Information')
            ),
            "course_information.php?id=$courseId"
        );
        $actions[] = Display::url(
            Display::getMdiIcon(
                ToolIcon::COURSE_HOME,
                'ch-tool-icon',
                null,
                ICON_SIZE_SMALL,
                get_lang('Course home')
            ),
            $courseInfo['course_public_url']
        );
        $actions[] = Display::url(
            Display::getMdiIcon(
                ToolIcon::TRACKING,
                'ch-tool-icon',
                null,
                ICON_SIZE_SMALL,
                get_lang('Reporting')
            ),
            $path.'tracking/courseLog.php?'.api_get_cidreq_params($courseId)
        );
        $actions[] = Display::url(
            Display::getMdiIcon(
                ActionIcon::EDIT,
                'ch-tool-icon',
                null,
                ICON_SIZE_SMALL,
                get_lang('Edit')
            ),
            $path.'admin/course_edit.php?id='.$courseId
        );
        $actions[] = Display::url(
            Display::getMdiIcon(
                ActionIcon::TAKE_BACKUP,
                'ch-tool-icon',
                null,
                ICON_SIZE_SMALL,
                get_lang('Create a backup')
            ),
            $path.'course_copy/create_backup.php?'.api_get_cidreq_params($courseId)
        );

        // Single course delete: ask if exclusive documents should also be removed.
        $actions[] = Display::url(
            Display::getMdiIcon(
                ActionIcon::DELETE,
                'ch-tool-icon',
                null,
                ICON_SIZE_SMALL,
                get_lang('Delete')
            ),
            $path.'admin/course_list.php?'
            .http_build_query([
                'delete_course' => $course['col0'],
                // Default: keep documents; JS will toggle this param to 1 if admin agrees.
                'delete_docs' => 0,
                'sec_token' => Security::getTokenFromSession(),
            ]),
            [
                'onclick' => 'return confirmDeleteCourseWithDocs(this);',
            ]
        );

        $em = Database::getManager();
        /** @var CatalogueCourseRelAccessUrlRelUsergroupRepository $repo */
        $repo = $em->getRepository(CatalogueCourseRelAccessUrlRelUsergroup::class);
        $record = $repo->findOneBy([
            'course' => $courseId,
            'accessUrl' => api_get_current_access_url_id(),
            'usergroup' => null,
        ]);

        $isInCatalogue = null !== $record;
        $catalogueUrl = api_get_self().'?toggle_catalogue='.$course['id'].'&sec_token='.Security::getTokenFromSession();

        $actions[] = Display::url(
            Display::getMdiIcon(
                $isInCatalogue ? StateIcon::CATALOGUE_OFF : StateIcon::CATALOGUE_ON,
                'ch-tool-icon',
                null,
                ICON_SIZE_SMALL,
                $isInCatalogue ? get_lang('Remove from catalogue') : get_lang('Add to catalogue'),
                [
                    'class' => $isInCatalogue ? 'text-warning' : 'text-muted',
                ]
            ),
            $catalogueUrl,
            [
                'title' => $isInCatalogue ? get_lang('Remove from catalogue') : get_lang('Add to catalogue'),
            ]
        );

        $courseItem = [
            $course['col0'],
            $course['col1'],
            $course['col2'],
            $languages[$course['col3']] ?? $course['col3'],
            implode(', ', $categories),
            $course['col5'],
            $course['col6'],
            implode(\PHP_EOL, $actions),
        ];
        $courses[] = $courseItem;
    }

    return $courses;
}