Tracking::get_sessions_coached_by_user()

Complexity

Conditions	19
Paths	336

Size

Total Lines	138
Code Lines	77

Duplication

Lines	0
Ratio	0 %

Importance

Changes

0

<?php
    public static function get_sessions_coached_by_user(
        $coach_id,
        $start = 0,
        $limit = 0,
        $getCount = false,
        $keyword = '',
        $description = '',
        $orderByName = '',
        $orderByDirection = '',
        $options = []
    ) {
        // table definition
        $tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
        $tbl_session_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
        $coach_id = (int) $coach_id;

        $select = ' SELECT * FROM ';
        if ($getCount) {
            $select = ' SELECT count(DISTINCT id) as count FROM ';
        }

        $limitCondition = null;
        if (!empty($start) && !empty($limit)) {
            $limitCondition = " LIMIT ".intval($start).", ".intval($limit);
        }

        $keywordCondition = null;
        if (!empty($keyword)) {
            $keyword = Database::escape_string($keyword);
            $keywordCondition = " AND (name LIKE '%$keyword%' ) ";

            if (!empty($description)) {
                $description = Database::escape_string($description);
                $keywordCondition = " AND (name LIKE '%$keyword%' OR description LIKE '%$description%' ) ";
            }
        }

        $extraFieldModel = new ExtraFieldModel('session');
        $conditions = $extraFieldModel->parseConditions($options);
        $sqlInjectJoins = $conditions['inject_joins'];
        $extraFieldsConditions = $conditions['where'];
        $sqlInjectWhere = $conditions['inject_where'];
        $injectExtraFields = $conditions['inject_extra_fields'];

        $tbl_session_rel_access_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
        $access_url_id = api_get_current_access_url_id();

        $orderBy = '';
        if (!empty($orderByName)) {
            if (in_array($orderByName, ['name', 'access_start_date'])) {
                $orderByDirection = in_array(strtolower($orderByDirection), ['asc', 'desc']) ? $orderByDirection : 'asc';
                $orderByName = Database::escape_string($orderByName);
                $orderBy .= " ORDER BY `$orderByName` $orderByDirection";
            }
        }

        $sql = "
            $select
            (
                SELECT DISTINCT
                    s.id,
                    name,
                    $injectExtraFields
                    access_start_date,
                    access_end_date
                FROM $tbl_session s
                INNER JOIN $tbl_session_rel_access_url session_rel_url
                ON (s.id = session_rel_url.session_id)
                $sqlInjectJoins
                WHERE
                    id_coach = $coach_id AND
                    access_url_id = $access_url_id
                    $keywordCondition
                    $extraFieldsConditions
                    $sqlInjectWhere
            UNION
                SELECT DISTINCT
                    s.id,
                    s.name,
                    $injectExtraFields
                    s.access_start_date,
                    s.access_end_date
                FROM $tbl_session as s
                INNER JOIN $tbl_session_course_user as session_course_user
                ON
                    s.id = session_course_user.session_id AND
                    session_course_user.user_id = $coach_id AND
                    session_course_user.status = 2
                INNER JOIN $tbl_session_rel_access_url session_rel_url
                ON (s.id = session_rel_url.session_id)
                $sqlInjectJoins
                WHERE
                    access_url_id = $access_url_id
                    $keywordCondition
                    $extraFieldsConditions
                    $sqlInjectWhere
            ) as sessions $limitCondition $orderBy
            ";

        $rs = Database::query($sql);
        if ($getCount) {
            $row = Database::fetch_array($rs);

            return $row['count'];
        }

        $sessions = [];
        while ($row = Database::fetch_array($rs)) {
            if ($row['access_start_date'] === '0000-00-00 00:00:00') {
                $row['access_start_date'] = null;
            }

            $sessions[$row['id']] = $row;
        }

        if (!empty($sessions)) {
            foreach ($sessions as &$session) {
                if (empty($session['access_start_date'])) {
                    $session['status'] = get_lang('SessionActive');
                } else {
                    $time_start = api_strtotime($session['access_start_date'], 'UTC');
                    $time_end = api_strtotime($session['access_end_date'], 'UTC');
                    if ($time_start < time() && time() < $time_end) {
                        $session['status'] = get_lang('SessionActive');
                    } else {
                        if (time() < $time_start) {
                            $session['status'] = get_lang('SessionFuture');
                        } else {
                            if (time() > $time_end) {
                                $session['status'] = get_lang('SessionPast');
                            }
                        }
                    }
                }
            }
        }

        return $sessions;
    }