chamilo / chamilo-lms

global $_configuration;

if (api_is_multiple_url_enabled()) {

	$tbl_course_rel_access_url= Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);

	$access_url_id = api_get_current_access_url_id();

	if ($access_url_id != -1){

	$sql = "SELECT code,visual_code,title

		FROM $course_table as c

		INNER JOIN $tbl_course_rel_access_url as course_rel_url

		ON (c.id = course_rel_url.c_id)

		WHERE access_url_id = $access_url_id

		ORDER BY visual_code";

	}

}

$result = Database::query($sql);

while ($course = Database::fetch_object($result)) {

	$courses[$course->code] = $course->visual_code.' - '.$course->title;

                            scu.user_id = u.id AND scu.status=2 AND login_user_id=u.id

                        GROUP BY user_id ";

        if (api_is_multiple_url_enabled()) {

            $tbl_session_rel_access_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);

            $access_url_id = api_get_current_access_url_id();

            if ($access_url_id != -1) {

                $sqlCoachs = "SELECT DISTINCT

                                    scu.user_id as id_coach,

                                    u.id as user_id,

                                    lastname,

                                    firstname,

                                    MAX(login_date) as login_date

                                FROM $tbl_user u,

                                $tbl_session_course_user scu,

                                $tbl_track_login ,

                                $tbl_session_rel_access_url session_rel_url

                                WHERE

                                    scu.user_id = u.id AND

                                    scu.status = 2 AND

                                    login_user_id = u.id AND

                                    access_url_id = $access_url_id AND

                                    session_rel_url.session_id = scu.session_id

                                GROUP BY u.id";

            }

        }

        if (!empty($order[$tracking_column])) {

            $sqlCoachs .= " ORDER BY ".$order[$tracking_column]." ".$tracking_direction;

        }

        FROM $tbl_user

        WHERE status='1'".$order_clause;

if (api_is_multiple_url_enabled()) {

	$table_access_url_rel_user= Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);

	$access_url_id = api_get_current_access_url_id();

	if ($access_url_id != -1) {

		$sql = "SELECT DISTINCT u.user_id,lastname,firstname,username

		        FROM $tbl_user u

                INNER JOIN $table_access_url_rel_user url_rel_user

                ON (url_rel_user.user_id = u.user_id)

			    WHERE status='1' AND access_url_id = '$access_url_id' $order_clause";

	}

}

$result = Database::query($sql);

$coaches = Database::store_result($result);

				ON $tbl_user.user_id = s.id_coach

				ORDER BY id";

		if (api_is_multiple_url_enabled()) {

			$tbl_session_rel_access_url= Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);

			$access_url_id = api_get_current_access_url_id();

			if ($access_url_id != -1){

			$sql = "SELECT s.id, name,id_coach,username,access_start_date,access_end_date,visibility,session_category_id

					FROM $tbl_session s

					INNER JOIN $tbl_session_rel_access_url as session_rel_url

					ON (s.id= session_rel_url.session_id)

					INNER JOIN $tbl_user u ON (u.user_id = s.id_coach)

					WHERE access_url_id = $access_url_id

					ORDER BY id";

			}

		}

		$result = Database::query($sql);

	} else {

//select of sessions

$sql = "SELECT id, name FROM $tbl_session ORDER BY name";

if (api_is_multiple_url_enabled()) {

	$tbl_session_rel_access_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);

	$access_url_id = api_get_current_access_url_id();

	if ($access_url_id != -1){

	$sql = "SELECT s.id, name FROM $tbl_session s

			INNER JOIN $tbl_session_rel_access_url as session_rel_url

			ON (s.id = session_rel_url.session_id)

			WHERE access_url_id = $access_url_id

			ORDER BY name";

	}

}

$result = Database::query($sql);

$Sessions = Database::store_result($result);

        // We filter the courses from the URL

        $join_access_url = $where_access_url = '';

        if (api_get_multiple_access_url()) {

            $access_url_id = api_get_current_access_url_id();

            if ($access_url_id != -1) {

                $tbl_url_course = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);

                $join_access_url = "LEFT JOIN $tbl_url_course url_rel_course ON url_rel_course.c_id = course.id";

                $where_access_url = " AND access_url_id = $access_url_id ";

            }

        }

        // Courses in which we subscribed out of any session

        $tbl_user_course_category = Database :: get_main_table(TABLE_USER_COURSE_CATEGORY);

        //we filter the courses from the URL

        $join_access_url = $where_access_url = '';

        if (api_get_multiple_access_url()) {

            $urlId = api_get_current_access_url_id();

            if ($urlId != -1) {

                $tbl_url_session = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);

                $join_access_url = " ,  $tbl_url_session url_rel_session ";

                $where_access_url = " AND access_url_id = $urlId AND url_rel_session.session_id = $session_id ";

            }

        }

        /* This query is very similar to the query below, but it will check the

        session_rel_course_user table if there are courses registered

                    course_rel_user.status='1'

        ";

        if (api_get_multiple_access_url()) {

            $tbl_course_rel_access_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);

            $access_url_id = api_get_current_access_url_id();

            if ($access_url_id != -1) {

                $sql = "

                    SELECT

                        course.code,

                        course.title,

                        course.id,

                        course.id as real_id

                    FROM $tbl_course_user as course_rel_user

                    INNER JOIN $tbl_course as course

                    ON course.id = course_rel_user.c_id

                    INNER JOIN $tbl_course_rel_access_url course_rel_url

                    ON (course_rel_url.c_id = course.id)

                    WHERE

                        access_url_id = $access_url_id  AND

                        course_rel_user.user_id = '$user_id' AND

                        course_rel_user.status = '1'

                ";

            }

        }

        if (!empty($startsWith)) {

            $startsWith = Database::escape_string($startsWith);

        //we filter the courses from the URL

        $join_access_url = $where_access_url = '';

        if (api_get_multiple_access_url()) {

            $access_url_id = api_get_current_access_url_id();

            if ($access_url_id != -1) {

                $tbl_url_course = Database:: get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);

                $join_access_url = "LEFT JOIN $tbl_url_course url_rel_course

                                    ON url_rel_course.c_id = tcfv.item_id ";

                $where_access_url = " AND access_url_id = $access_url_id ";

            }

        }

        $extraFieldType = EntityExtraField::COURSE_FIELD_TYPE;

        //we filter the courses from the URL

        $joinAccessUrl = '';

        $whereAccessUrl = '';

        if (api_get_multiple_access_url()) {

            $accessUrlId = api_get_current_access_url_id();

            if ($accessUrlId != -1) {

                $tblUrlCourse = Database:: get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);

                $joinAccessUrl = "LEFT JOIN $tblUrlCourse url_rel_course

                                    ON url_rel_course.c_id = c.id ";

                $whereAccessUrl = " AND access_url_id = $accessUrlId ";

            }

        }

        // get course list auto-register

        $sql = "SELECT DISTINCT(c.code)

                        WHERE status = '1' ".

                        $orderClause;

                if (api_is_multiple_url_enabled()) {

                    $userRelAccessUrlTable = Database::get_main_table(

                        TABLE_MAIN_ACCESS_URL_REL_USER

                    );

                    $accessUrlId = api_get_current_access_url_id();

                    if ($accessUrlId != -1) {

                        $sql = "SELECT user.user_id, username, lastname, firstname

                        FROM $tbl_user user

                        INNER JOIN $userRelAccessUrlTable url_user

                        ON (url_user.user_id = user.user_id)

                        WHERE

                            access_url_id = $accessUrlId AND

                            status = 1 "

                            .$orderClause;

                    }

                }

                $result = Database::query($sql);

                $coachesList = Database::store_result($result);