/**
* Return array of Category objects where a student is subscribed to.
* @param int student id
* @param string Course code
* @param int Session id
*/
public function get_root_categories_for_student($stud_id, $course_code = null, $session_id = null)
{
// courses
$main_course_user_table = Database::get_main_table(TABLE_MAIN_COURSE_USER);
$course_table = Database::get_main_table(TABLE_MAIN_COURSE);
$tbl_grade_categories = Database::get_main_table(TABLE_MAIN_GRADEBOOK_CATEGORY);
$sql = 'SELECT * FROM ' . $tbl_grade_categories . ' WHERE parent_id = 0';
if (!api_is_allowed_to_edit()) {
$sql .= ' AND visible = 1';
//proceed with checks on optional parameters course & session
if (!empty($course_code)) {
// TODO: considering it highly improbable that a user would get here
// if he doesn't have the rights to view this course and this
// session, we don't check his registration to these, but this
// could be an improvement
if (!empty($session_id)) {
$sql .= " AND course_code = '" . Database::escape_string($course_code) . "'" . " AND session_id = " . (int) $session_id;
} else {
$sql .= " AND course_code = '" . Database::escape_string($course_code) . "' AND session_id is null OR session_id=0";
}
} else {
//no optional parameter, proceed as usual
$sql .= ' AND course_code IN
(
SELECT course.code
FROM ' . $main_course_user_table . ' course_user
INNER JOIN ' . $course_table . ' course ON (course.id AND course_user.c_id)
WHERE user_id = ' . intval($stud_id) . ' AND status = ' . STUDENT . ')';
}
} elseif (api_is_allowed_to_edit() && !api_is_platform_admin()) {
//proceed with checks on optional parameters course & session
if (!empty($course_code)) {
// TODO: considering it highly improbable that a user would get here
// if he doesn't have the rights to view this course and this
// session, we don't check his registration to these, but this
// could be an improvement
$sql .= " AND course_code = '" . Database::escape_string($course_code) . "'";
if (!empty($session_id)) {
$sql .= " AND session_id = " . (int) $session_id;
} else {
$sql .= "AND session_id IS NULL OR session_id=0";
}
} else {
$sql .= ' AND course_code in' . ' (SELECT course_code FROM ' . $main_course_user_table . ' WHERE user_id = ' . api_get_user_id() . ' AND status = ' . COURSEMANAGER . ')';
}
} elseif (api_is_platform_admin()) {
if (isset($session_id) && $session_id != 0) {
$sql .= ' AND session_id=' . intval($session_id);
} else {
$sql .= ' AND coalesce(session_id,0)=0';
}
}
$result = Database::query($sql);
$cats = Category::create_category_objects_from_sql_result($result);
// Course independent categories
if (empty($course_code)) {
$cats = Category::get_independent_categories_with_result_for_student(0, $stud_id, $cats);
}
return $cats;
}
/**
* Return array of Category objects where a student is subscribed to.
* @param int student id
* @param string Course code
* @param int Session id
*/
public function get_root_categories_for_student($stud_id, $course_code = null, $session_id = null)
{
$courseId = api_get_course_int_id($course_code);
$em = Database::getManager();
$query = $em->createQuery();
$queryParams = [];
$dql = 'SELECT gc FROM ChamiloCoreBundle:GradebookCategory gc WHERE gc.parentId = 0 ';
if (!api_is_allowed_to_edit()) {
$dql .= 'AND gc.visible = 1 ';
//proceed with checks on optional parameters course & session
if (!empty($course_code)) {
// TODO: considering it highly improbable that a user would get here
// if he doesn't have the rights to view this course and this
// session, we don't check his registration to these, but this
// could be an improvement
$dql .= 'AND gc.course = :course ';
$queryParams['course'] = $courseId;
if (!empty($session_id)) {
$dql .= 'AND gc.sessionId = :session ';
$queryParams['session'] = $session_id;
} else {
$dql .= 'AND gc.sessionId IS NULL OR sessionId = 0 ';
}
} else {
//no optional parameter, proceed as usual
$dql .= '
AND gc.course IN (
SELECT c FROM ChamiloCourseBundle:CourseRelUser cu
JOIN ChamiloCoreBudle:Course c WITH cu.course = c
WHERE cu.user = :user AND cu.status = :status
)
';
$queryParams['user'] = intval($stud_id);
$queryParams['status'] = STUDENT;
}
} elseif (api_is_allowed_to_edit() && !api_is_platform_admin()) {
//proceed with checks on optional parameters course & session
if (!empty($course_code)) {
// TODO: considering it highly improbable that a user would get here
// if he doesn't have the rights to view this course and this
// session, we don't check his registration to these, but this
// could be an improvement
$dql .= 'AND gc.course = :course ';
$queryParams['course'] = $courseId;
if (!empty($session_id)) {
$dql .= 'AND gc.sessionId = :session ';
$queryParams['session'] = intval($session_id);
} else {
$dql .= 'AND gc.sessionId IS NULL or gc.sessionId = 0 ';
}
} else {
$dql .= '
AND gc.course IN (
SELECT c FROM ChamiloCoreBundle:CourseRelUser cu
JOIN ChamiloCoreBundle:Course c WITH cu.course = c
WHERE cu.user = :user AND cu.status = :status
)
';
$queryParams['user'] = api_get_user_id();
$queryParams['status'] = COURSEMANAGER;
}
} elseif (api_is_platform_admin()) {
if (isset($session_id) && $session_id != 0) {
$dql .= 'AND gc.sessionId = :session ';
$queryParams['session'] = intval($session_id);
} else {
$dql .= 'AND COALESCE (session_id, 0) = 0';
}
}
$result = $query->setDQL($dql)->execute($queryParams);
$cats = Category::createCategoryObjectsFromEntities($result);
// course independent categories
if (empty($course_code)) {
$cats = Category::get_independent_categories_with_result_for_student(0, $stud_id, $cats);
}
return $cats;
}
