get_course_data()

Complexity

Conditions	34
Paths	> 20000

Size

Total Lines	204
Code Lines	131

Duplication

Lines	0
Ratio	0 %

Importance

Changes

0

<?php
function get_course_data($from, $number_of_items, $column, $direction, $dataFunctions = [], $getCount = false)
{
    $addTeacherColumn = api_get_configuration_value('add_teachers_in_course_list');
    $table = Database::get_main_table(TABLE_MAIN_COURSE);
    $from = (int) $from;
    $number_of_items = (int) $number_of_items;
    $column = (int) $column;

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

    $teachers = '';
    if ($addTeacherColumn) {
        $teachers = " GROUP_CONCAT(cu.user_id SEPARATOR ',') as col7, ";
    }
    $select = "SELECT
                code AS col0,
                title AS col1,
                code AS col2,
                course_language AS col3,
                category_code AS col4,
                subscribe AS col5,
                unsubscribe AS col6,
                $teachers
                visibility,
                directory,
                visual_code,
                course.code,
                course.id ";

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

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

    if ((api_is_platform_admin() || api_is_session_admin()) &&
        api_is_multiple_url_enabled() && api_get_current_access_url_id() != -1
    ) {
        $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 ($addTeacherColumn) {
        $tableCourseRelUser = Database::get_main_table(TABLE_MAIN_COURSE_USER);
        $sql .= "
                LEFT JOIN $tableCourseRelUser cu
                ON (course.id = cu.c_id AND cu.status = ".COURSEMANAGER.")
            ";
    }

    if (isset($_GET['keyword'])) {
        $keyword = Database::escape_string("%".trim($_GET['keyword'])."%");
        $sql .= " WHERE (
            title LIKE '".$keyword."' OR
            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
                (code LIKE '".$keyword_code."' OR visual_code LIKE '".$keyword_code."') AND
                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_code LIKE '".$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() && api_get_current_access_url_id() != -1
    ) {
        $sql .= " AND url_rel_course.access_url_id = ".api_get_current_access_url_id();
    }

    if ($addTeacherColumn) {
        $teachers = isset($_GET['course_teachers']) ? $_GET['course_teachers'] : [];
        if (!empty($teachers)) {
            $teachers = array_map('intval', $teachers);
            $addNull = '';
            foreach ($teachers as $key => $teacherId) {
                if (0 === $teacherId) {
                    $addNull = 'OR cu.user_id IS NULL ';
                    unset($key);
                }
            }
            $sql .= ' AND ( cu.user_id IN ("'.implode('", "', $teachers).'") '.$addNull.' ) ';
        }

        if (false === $getCount) {
            $sql .= " GROUP BY course.id ";
        }
    }

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

        return 0;
    }

    $sql .= " ORDER BY col$column $direction ";
    $sql .= " LIMIT $from, $number_of_items";

    $res = Database::query($sql);
    $courses = [];
    $languages = api_get_languages_to_array();
    $path = api_get_path(WEB_CODE_PATH);
    $coursePath = api_get_path(WEB_COURSE_PATH);

    while ($course = Database::fetch_array($res)) {
        $courseId = $course['id'];
        $courseCode = $course['code'];

        // Place colour icons in front of courses.
        $showVisualCode = $course['visual_code'] != $courseCode ? Display::label($course['visual_code'], 'info') : null;
        $course[1] = get_course_visibility_icon($course['visibility']).PHP_EOL
            .Display::url(Security::remove_XSS($course[1]), $coursePath.$course['directory'].'/index.php').PHP_EOL
            .$showVisualCode;
        $course[5] = $course[5] == SUBSCRIBE_ALLOWED ? get_lang('Yes') : get_lang('No');
        $course[6] = $course[6] == UNSUBSCRIBE_ALLOWED ? get_lang('Yes') : get_lang('No');
        $language = isset($languages[$course[3]]) ? $languages[$course[3]] : $course[3];

        $actions = [];
        $actions[] = Display::url(
            Display::return_icon('info2.png', get_lang('Info')),
            "course_information.php?code=$courseCode"
        );
        $actions[] = Display::url(
            Display::return_icon('course_home.png', get_lang('CourseHomepage')),
            $coursePath.$course['directory'].'/index.php'
        );
        $actions[] = Display::url(
            Display::return_icon('statistics.png', get_lang('Tracking')),
            $path.'tracking/courseLog.php?'.api_get_cidreq_params($courseCode)
        );
        $actions[] = Display::url(
            Display::return_icon('edit.png', get_lang('Edit')),
            $path.'admin/course_edit.php?id='.$courseId
        );
        $actions[] = Display::url(
            Display::return_icon('backup.png', get_lang('CreateBackup')),
            $path.'coursecopy/create_backup.php?'.api_get_cidreq_params($courseCode)
        );
        $actions[] = Display::url(
            Display::return_icon('delete.png', get_lang('Delete')),
            $path.'admin/course_list.php?'
                .http_build_query([
                    'delete_course' => $courseCode,
                    'sec_token' => Security::getTokenFromSession(),
                ]),
            [
                'onclick' => "javascript: if (!confirm('"
                    .addslashes(api_htmlentities(get_lang('ConfirmYourChoice'), ENT_QUOTES))."')) return false;",
            ]
        );
        $courseItem = [
            $course[0],
            $course[1],
            $course[2],
            $language,
            $course[4],
            $course[5],
            $course[6],
        ];

        if ($addTeacherColumn) {
            $teacherIdList = array_filter(explode(',', $course[7]));
            $teacherList = [];
            if (!empty($teacherIdList)) {
                foreach ($teacherIdList as $teacherId) {
                    $userInfo = api_get_user_info($teacherId);
                    if ($userInfo) {
                        $teacherList[] = $userInfo['complete_name'];
                    }
                }
            }
            $courseItem[] = implode(', ', $teacherList);
        }
        $courseItem[] = implode(PHP_EOL, $actions);
        $courses[] = $courseItem;
    }

    return $courses;
}