CourseRepository::getSubscribedUsersByStatus() - Code Metrics - Inspection of "Fix space available widget - ref #6719" - chamilo/chamilo-lms - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Pull Request — master (#6896)

unknown

created 2025-10-10 14:06 UTC

CourseRepository::getSubscribedUsersByStatus() A

↳ Parent: CourseRepository

Complexity

Conditions	1
Paths	1

Size

Total Lines	10
Code Lines	5

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	1
eloc	5
c	0
b	0
f	0
nc	1
nop	2
dl	0
loc	10
rs	10

<?php

declare(strict_types=1);

/* For licensing terms, see /license.txt */

namespace Chamilo\CoreBundle\Repository\Node;

use Chamilo\CoreBundle\Entity\AccessUrl;
use Chamilo\CoreBundle\Entity\Course;
use Chamilo\CoreBundle\Entity\CourseRelUser;
use Chamilo\CoreBundle\Entity\Session;
use Chamilo\CoreBundle\Entity\SessionRelCourse;
use Chamilo\CoreBundle\Entity\SessionRelCourseRelUser;
use Chamilo\CoreBundle\Entity\User;
use Chamilo\CoreBundle\Helpers\ChamiloHelper;
use Chamilo\CoreBundle\Repository\ResourceRepository;
use Chamilo\CoreBundle\Repository\SessionRepository;
use Doctrine\Common\Collections\Criteria;
use Doctrine\ORM\AbstractQuery;
use Doctrine\ORM\Query\Expr\Join;
use Doctrine\ORM\QueryBuilder;
use Doctrine\Persistence\ManagerRegistry;

/**
 * Class CourseRepository.
 *
 * The functions inside this class should return an instance of QueryBuilder.
 */
class CourseRepository extends ResourceRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, Course::class);
    }

    public function deleteCourse(Course $course): void
    {
        $em = $this->getEntityManager();

        // Deleting all nodes connected to the course:
        // $node = $course->getResourceNode();
        // $children = $node->getChildren();
        // /* var ResourceNode $child
        /*foreach ($children as $child) {
            var_dump($child->getId().'-'.$child->getTitle().'<br />');
            var_dump(get_class($child));
            $em->remove($child);
        }*/

        $em->remove($course);
        $em->flush();
        $em->clear();
    }

    public function findOneByCode(string $code): ?Course
    {
        return $this->findOneBy([
            'code' => $code,
        ]);
    }

    /**
     * Get course user relationship based in the course_rel_user table.
     *
     * @return array<int, CourseRelUser>
     */
    public function getCoursesByUser(User $user, AccessUrl $url): array
    {
        $qb = $this->getEntityManager()->createQueryBuilder();

        $qb
            ->select('courseRelUser')
            ->from(Course::class, 'c')
            ->innerJoin(CourseRelUser::class, 'courseRelUser')
            // ->innerJoin('c.users', 'courseRelUser')
            ->innerJoin('c.urls', 'accessUrlRelCourse')
            ->where('courseRelUser.user = :user')
            ->andWhere('accessUrlRelCourse.url = :url')
            ->setParameters([
                'user' => $user,
                'url' => $url,
            ])
        ;

        $query = $qb->getQuery();

        return $query->getResult();
    }

    /**
     * Get info from courses where the user has the given role.
     *
     * @return Course[]
     */
    public function getCoursesInfoByUser(User $user, AccessUrl $url, int $status, string $keyword = ''): array
    {
        $qb = $this->getEntityManager()->createQueryBuilder();

        $qb->select('DISTINCT c.id, c.title, c.code')
            ->from(Course::class, 'c')
            ->innerJoin(CourseRelUser::class, 'courseRelUser')
            ->innerJoin('c.urls', 'accessUrlRelCourse')
            ->where('accessUrlRelCourse.url = :url')
            ->andWhere('courseRelUser.user = :user')
            ->andWhere('courseRelUser.status = :status')
            ->setParameters([
                'user' => $user,
                'url' => $url,
                'status' => $status,
            ])
        ;

        if (!empty($keyword)) {
            $qb->andWhere($qb->expr()->orX(
                $qb->expr()->like('c.title', ':keyword'),
                $qb->expr()->like('c.code', ':keyword')
            ))
                ->setParameter('keyword', '%'.$keyword.'%')
            ;
        }

        $query = $qb->getQuery();

        return $query->getArrayResult();
    }

    /**
     * Get all users that are registered in the course. No matter the status.
     *
     * @return QueryBuilder
     */
    public function getSubscribedUsers(Course $course)
    {
        // Course builder
        $queryBuilder = $this->createQueryBuilder('c');

        // Selecting user info.
        $queryBuilder->select('DISTINCT user');

        // Selecting courses for users.
        $queryBuilder->innerJoin('c.users', 'subscriptions');
        $queryBuilder->innerJoin(
            User::class,
            'user',
            Join::WITH,
            'subscriptions.user = user.id'
        );

        if (api_is_western_name_order()) {
            $queryBuilder->orderBy('user.firstname', Criteria::ASC);
        } else {
            $queryBuilder->orderBy('user.lastname', Criteria::ASC);
        }

        $wherePart = $queryBuilder->expr()->andx();

        // Get only users subscribed to this course
        $wherePart->add($queryBuilder->expr()->eq('c.id', $course->getId()));

        // $wherePart->add($queryBuilder->expr()->eq('c.status', $status));

        $queryBuilder->where($wherePart);

        return $queryBuilder;
    }

    /**
     * Gets students subscribed in the course.
     *
     * @return QueryBuilder
     */
    public function getSubscribedStudents(Course $course)
    {
        return $this->getSubscribedUsersByStatus($course, STUDENT);
    }

    /**
     * Gets the students subscribed in the course.
     *
     * @return QueryBuilder
     */
    public function getSubscribedCoaches(Course $course)
    {
        return $this->getSubscribedUsers($course);
    }

    /**
     * Gets the teachers subscribed in the course.
     *
     * @return QueryBuilder
     */
    public function getSubscribedTeachers(Course $course)
    {
        return $this->getSubscribedUsersByStatus($course, ChamiloHelper::COURSE_MANAGER);
    }

    /**
     * @param int $status use legacy chamilo constants COURSEMANAGER|STUDENT
     *
     * @return QueryBuilder
     */
    public function getSubscribedUsersByStatus(Course $course, int $status)
    {
        $queryBuilder = $this->getSubscribedUsers($course);
        $queryBuilder
            ->andWhere(
                $queryBuilder->expr()->eq('subscriptions.status', $status)
            )
        ;

        return $queryBuilder;
    }

    public function courseCodeExists(string $code): bool
    {
        $qb = $this->createQueryBuilder('c')
            ->select('count(c.id)')
            ->where('c.code = :code OR c.visualCode = :code')
            ->setParameter('code', $code)
            ->getQuery()
        ;

        return (int) $qb->getSingleScalarResult() > 0;
    }

    public function findCourseAsArray($id)
    {
        $qb = $this->createQueryBuilder('c')
            ->select('c.id, c.code, c.title, c.visualCode, c.courseLanguage, c.departmentUrl, c.departmentName')
            ->where('c.id = :id')
            ->setParameter('id', $id)
        ;

        $query = $qb->getQuery();

        return $query->getOneOrNullResult(AbstractQuery::HYDRATE_ARRAY);
    }

    public function getPersonalSessionCourses(
        User $user,
        AccessUrl $url,
        bool $isAllowedToCreateCourse,
        ?int $sessionLimit = null,
    ): array {
        $em = $this->getEntityManager();

        /** @var SessionRepository $sessionRepo */
        $sessionRepo = $em->getRepository(Session::class);
        $sessionCourseUserRepo = $em->getRepository(SessionRelCourseRelUser::class);

        $qb = $em->createQueryBuilder();

        $courseListSqlResult = $qb
            ->select('c.id AS cid')
            ->from(CourseRelUser::class, 'cru')
            ->leftJoin('cru.course', 'c')
            ->leftJoin('c.urls', 'urc')
            ->where($qb->expr()->eq('cru.user', ':user'))
            ->andWhere($qb->expr()->neq('cru.relationType', ':relationType'))
            ->andWhere($qb->expr()->eq('urc.url', ':url'))
            ->setParameters([
                'user' => $user->getId(),
                'relationType' => COURSE_RELATION_TYPE_RRHH,
                'url' => $url->getId(),
            ])
            ->getQuery()
            ->getResult()
        ;

        $personalCourseList = $courseListSqlResult;

        $sessionListFromCourseCoach = [];
        // Getting sessions that are related to a coach in the session_rel_course_rel_user table
        if ($isAllowedToCreateCourse) {
            $sessionListFromCourseCoach = array_map(
                fn (SessionRelCourseRelUser $srcru) => $srcru->getSession()->getId(),
                $sessionCourseUserRepo->findBy(['user' => $user->getId(), 'status' => Session::COURSE_COACH])
            );
        }

        // Get the list of sessions where the user is subscribed
        // This is divided into two different queries
        /** @var array<int, Session> $sessions */
        $sessions = [];

        $qb = $sessionRepo->createQueryBuilder('s');
        $qbParams = [
            'user' => $user->getId(),
            'relationType' => Session::STUDENT,
        ];

        $qb
            ->innerJoin('s.users', 'su')
            ->where(
                $qb->expr()->andX(
                    $qb->expr()->eq('su.user', ':user'),
                    $qb->expr()->neq('su.relationType', ':relationType')
                )
            )
        ;

        if ($sessionListFromCourseCoach) {
            $qb->orWhere($qb->expr()->in('s.id', ':sessionListFromCourseCoach'));

            $qbParams['coachCourseConditions'] = $sessionListFromCourseCoach;
        }

        $result = $qb
            ->orderBy('s.accessStartDate')
            ->addOrderBy('s.accessEndDate')
            ->addOrderBy('s.title')
            ->setMaxResults($sessionLimit)
            ->setParameters($qbParams)
            ->getQuery()
            ->getResult()
        ;

        /** @var Session $row */
        foreach ($result as $row) {
            $row->setAccessVisibilityByUser($user);

            $sessions[$row->getId()] = $row;
        }

        $qb = $sessionRepo->createQueryBuilder('s');
        $qbParams = [
            'user' => $user->getId(),
            'relationType' => Session::GENERAL_COACH,
        ];

        $qb
            ->innerJoin('s.users', 'sru')
            ->where(
                $qb->expr()->andX(
                    $qb->expr()->eq('sru.user', ':user'),
                    $qb->expr()->neq('sru.relationType', ':relationType')
                )
            )
        ;

        if ($sessionListFromCourseCoach) {
            $qb->orWhere($qb->expr()->in('s.id', ':sessionListFromCourseCoach'));

            $qbParams['coachCourseConditions'] = $sessionListFromCourseCoach;
        }

        $result = $qb
            ->orderBy('s.accessStartDate')
            ->addOrderBy('s.accessEndDate')
            ->addOrderBy('s.title')
            ->setParameters($qbParams)
            ->getQuery()
            ->getResult()
        ;

        /** @var Session $row */
        foreach ($result as $row) {
            $row->setAccessVisibilityByUser($user);

            $sessions[$row->getId()] = $row;
        }

        if ($isAllowedToCreateCourse) {
            foreach ($sessions as $enreg) {
                if (Session::INVISIBLE == $enreg->getAccessVisibility()) {
                    continue;
                }

                $coursesAsGeneralCoach = $sessionRepo->getSessionCoursesByStatusInUserSubscription(
                    $user,
                    $enreg,
                    Session::GENERAL_COACH,
                    $url
                );
                $coursesAsCourseCoach = $sessionRepo->getSessionCoursesByStatusInCourseSubscription(
                    $user,
                    $enreg,
                    Session::COURSE_COACH,
                    $url
                );

                // This query is horribly slow when more than a few thousand
                // users and just a few sessions to which they are subscribed
                $coursesInSession = array_merge($coursesAsGeneralCoach, $coursesAsCourseCoach);

                /** @var SessionRelCourse $resultRow */
                foreach ($coursesInSession as $resultRow) {
                    $sid = $resultRow->getSession()->getId();
                    $cid = $resultRow->getCourse()->getId();

                    $personalCourseList["$sid - $cid"] = [
                        'cid' => $cid,
                        'sid' => $sid,
                    ];
                }
            }
        }

        foreach ($sessions as $enreg) {
            if (Session::INVISIBLE == $enreg->getAccessVisibility()) {
                continue;
            }

            // This query is very similar to the above query,
            // but it will check the session_rel_course_user table if there are courses registered to our user or not */
            $qb = $sessionCourseUserRepo->createQueryBuilder('scu');

            $result = $qb
                ->select('c.id as cid', 's.id AS sid')
                ->innerJoin('scu.course', 'c', Join::WITH, 'scu.session = :session')
                ->innerJoin('scu.session', 's')
                ->leftJoin('scu.user', 'u')
                ->where($qb->expr()->eq('scu.user', ':user'))
                ->orderBy('c.title')
                ->setParameters([
                    'session' => $enreg->getId(),
                    'user' => $user->getId(),
                ])
                ->getQuery()
                ->getResult()
            ;

            foreach ($result as $resultRow) {
                $key = $resultRow['sid'].' - '.$resultRow['cid'];

                if (!isset($personalCourseList[$key])) {
                    $personalCourseList[$key] = $resultRow;
                }
            }
        }

        return $personalCourseList;
    }

    /**
     * Returns all courses assigned to a specific AccessUrl.
     *
     * @return Course[]
     */
    public function getCoursesByAccessUrl(AccessUrl $url): array
    {
        return $this->createQueryBuilder('c')
            ->innerJoin('c.urls', 'u')
            ->where('u.url = :url')
            ->setParameter('url', $url)
            ->orderBy('c.title', 'ASC')
            ->getQuery()
            ->getResult()
        ;
    }

    public function getUsersByCourse(Course $course): array
    {
        $qb = $this->getEntityManager()->createQueryBuilder();

        $qb
            ->select('DISTINCT user')
            ->from(User::class, 'user')
            ->innerJoin(CourseRelUser::class, 'courseRelUser', Join::WITH, 'courseRelUser.user = user.id')
            ->where('courseRelUser.course = :course')
            ->setParameter('course', $course)
            ->orderBy('user.lastname', 'ASC')
            ->addOrderBy('user.firstname', 'ASC')
        ;

        $query = $qb->getQuery();

        return $query->getResult();
    }
}


1			<?php
2
3			declare(strict_types=1);
4
5			/* For licensing terms, see /license.txt */
6
7			namespace Chamilo\CoreBundle\Repository\Node;
8
9			use Chamilo\CoreBundle\Entity\AccessUrl;
10			use Chamilo\CoreBundle\Entity\Course;
11			use Chamilo\CoreBundle\Entity\CourseRelUser;
12			use Chamilo\CoreBundle\Entity\Session;
13			use Chamilo\CoreBundle\Entity\SessionRelCourse;
14			use Chamilo\CoreBundle\Entity\SessionRelCourseRelUser;
15			use Chamilo\CoreBundle\Entity\User;
16			use Chamilo\CoreBundle\Helpers\ChamiloHelper;
17			use Chamilo\CoreBundle\Repository\ResourceRepository;
18			use Chamilo\CoreBundle\Repository\SessionRepository;
19			use Doctrine\Common\Collections\Criteria;
20			use Doctrine\ORM\AbstractQuery;
21			use Doctrine\ORM\Query\Expr\Join;
22			use Doctrine\ORM\QueryBuilder;
23			use Doctrine\Persistence\ManagerRegistry;
24
25			/**
26			* Class CourseRepository.
27			*
28			* The functions inside this class should return an instance of QueryBuilder.
29			*/
30			class CourseRepository extends ResourceRepository
31			{
32			public function __construct(ManagerRegistry $registry)
33			{
34			parent::__construct($registry, Course::class);
35			}
36
37			public function deleteCourse(Course $course): void
38			{
39			$em = $this->getEntityManager();
40
41			// Deleting all nodes connected to the course:
42			// $node = $course->getResourceNode();
43			// $children = $node->getChildren();
44			// /* var ResourceNode $child
45			/*foreach ($children as $child) {
46			var_dump($child->getId().'-'.$child->getTitle().'<br />');
47			var_dump(get_class($child));
48			$em->remove($child);
49			}*/
50
51			$em->remove($course);
52			$em->flush();
53			$em->clear();
54			}
55
56			public function findOneByCode(string $code): ?Course
57			{
58			return $this->findOneBy([
59			'code' => $code,
60			]);
61			}
62
63			/**
64			* Get course user relationship based in the course_rel_user table.
65			*
66			* @return array<int, CourseRelUser>
67			*/
68			public function getCoursesByUser(User $user, AccessUrl $url): array
69			{
70			$qb = $this->getEntityManager()->createQueryBuilder();
71
72			$qb
73			->select('courseRelUser')
74			->from(Course::class, 'c')
75			->innerJoin(CourseRelUser::class, 'courseRelUser')
76			// ->innerJoin('c.users', 'courseRelUser')
77			->innerJoin('c.urls', 'accessUrlRelCourse')
78			->where('courseRelUser.user = :user')
79			->andWhere('accessUrlRelCourse.url = :url')
80			->setParameters([
81			'user' => $user,
82			'url' => $url,
83			])
84			;
85
86			$query = $qb->getQuery();
87
88			return $query->getResult();
89			}
90
91			/**
92			* Get info from courses where the user has the given role.
93			*
94			* @return Course[]
95			*/
96			public function getCoursesInfoByUser(User $user, AccessUrl $url, int $status, string $keyword = ''): array
97			{
98			$qb = $this->getEntityManager()->createQueryBuilder();
99
100			$qb->select('DISTINCT c.id, c.title, c.code')
101			->from(Course::class, 'c')
102			->innerJoin(CourseRelUser::class, 'courseRelUser')
103			->innerJoin('c.urls', 'accessUrlRelCourse')
104			->where('accessUrlRelCourse.url = :url')
105			->andWhere('courseRelUser.user = :user')
106			->andWhere('courseRelUser.status = :status')
107			->setParameters([
108			'user' => $user,
109			'url' => $url,
110			'status' => $status,
111			])
112			;
113
114			if (!empty($keyword)) {
115			$qb->andWhere($qb->expr()->orX(
116			$qb->expr()->like('c.title', ':keyword'),
117			$qb->expr()->like('c.code', ':keyword')
118			))
119			->setParameter('keyword', '%'.$keyword.'%')
120			;
121			}
122
123			$query = $qb->getQuery();
124
125			return $query->getArrayResult();
126			}
127
128			/**
129			* Get all users that are registered in the course. No matter the status.
130			*
131			* @return QueryBuilder
132			*/
133			public function getSubscribedUsers(Course $course)
134			{
135			// Course builder
136			$queryBuilder = $this->createQueryBuilder('c');
137
138			// Selecting user info.
139			$queryBuilder->select('DISTINCT user');
140
141			// Selecting courses for users.
142			$queryBuilder->innerJoin('c.users', 'subscriptions');
143			$queryBuilder->innerJoin(
144			User::class,
145			'user',
146			Join::WITH,
147			'subscriptions.user = user.id'
148			);
149
150			if (api_is_western_name_order()) {
151			$queryBuilder->orderBy('user.firstname', Criteria::ASC);
152			} else {
153			$queryBuilder->orderBy('user.lastname', Criteria::ASC);
154			}
155
156			$wherePart = $queryBuilder->expr()->andx();
157
158			// Get only users subscribed to this course
159			$wherePart->add($queryBuilder->expr()->eq('c.id', $course->getId()));
160
161			// $wherePart->add($queryBuilder->expr()->eq('c.status', $status));
162
163			$queryBuilder->where($wherePart);
164
165			return $queryBuilder;
166			}
167
168			/**
169			* Gets students subscribed in the course.
170			*
171			* @return QueryBuilder
172			*/
173			public function getSubscribedStudents(Course $course)
174			{
175			return $this->getSubscribedUsersByStatus($course, STUDENT);
176			}
177
178			/**
179			* Gets the students subscribed in the course.
180			*
181			* @return QueryBuilder
182			*/
183			public function getSubscribedCoaches(Course $course)
184			{
185			return $this->getSubscribedUsers($course);
186			}
187
188			/**
189			* Gets the teachers subscribed in the course.
190			*
191			* @return QueryBuilder
192			*/
193			public function getSubscribedTeachers(Course $course)
194			{
195			return $this->getSubscribedUsersByStatus($course, ChamiloHelper::COURSE_MANAGER);
196			}
197
198			/**
199			* @param int $status use legacy chamilo constants COURSEMANAGER\|STUDENT
200			*
201			* @return QueryBuilder
202			*/
203			public function getSubscribedUsersByStatus(Course $course, int $status)
204			{
205			$queryBuilder = $this->getSubscribedUsers($course);
206			$queryBuilder
207			->andWhere(
208			$queryBuilder->expr()->eq('subscriptions.status', $status)
209			)
210			;
211
212			return $queryBuilder;
213			}
214
215			public function courseCodeExists(string $code): bool
216			{
217			$qb = $this->createQueryBuilder('c')
218			->select('count(c.id)')
219			->where('c.code = :code OR c.visualCode = :code')
220			->setParameter('code', $code)
221			->getQuery()
222			;
223
224			return (int) $qb->getSingleScalarResult() > 0;
225			}
226
227			public function findCourseAsArray($id)
228			{
229			$qb = $this->createQueryBuilder('c')
230			->select('c.id, c.code, c.title, c.visualCode, c.courseLanguage, c.departmentUrl, c.departmentName')
231			->where('c.id = :id')
232			->setParameter('id', $id)
233			;
234
235			$query = $qb->getQuery();
236
237			return $query->getOneOrNullResult(AbstractQuery::HYDRATE_ARRAY);
238			}
239
240			public function getPersonalSessionCourses(
241			User $user,
242			AccessUrl $url,
243			bool $isAllowedToCreateCourse,
244			?int $sessionLimit = null,
245			): array {
246			$em = $this->getEntityManager();
247
248			/** @var SessionRepository $sessionRepo */
249			$sessionRepo = $em->getRepository(Session::class);
250			$sessionCourseUserRepo = $em->getRepository(SessionRelCourseRelUser::class);
251
252			$qb = $em->createQueryBuilder();
253
254			$courseListSqlResult = $qb
255			->select('c.id AS cid')
256			->from(CourseRelUser::class, 'cru')
257			->leftJoin('cru.course', 'c')
258			->leftJoin('c.urls', 'urc')
259			->where($qb->expr()->eq('cru.user', ':user'))
260			->andWhere($qb->expr()->neq('cru.relationType', ':relationType'))
261			->andWhere($qb->expr()->eq('urc.url', ':url'))
262			->setParameters([
263			'user' => $user->getId(),
264			'relationType' => COURSE_RELATION_TYPE_RRHH,
265			'url' => $url->getId(),
266			])
267			->getQuery()
268			->getResult()
269			;
270
271			$personalCourseList = $courseListSqlResult;
272
273			$sessionListFromCourseCoach = [];
274			// Getting sessions that are related to a coach in the session_rel_course_rel_user table
275			if ($isAllowedToCreateCourse) {
276			$sessionListFromCourseCoach = array_map(
277			fn (SessionRelCourseRelUser $srcru) => $srcru->getSession()->getId(),
278			$sessionCourseUserRepo->findBy(['user' => $user->getId(), 'status' => Session::COURSE_COACH])
279			);
280			}
281
282			// Get the list of sessions where the user is subscribed
283			// This is divided into two different queries
284			/** @var array<int, Session> $sessions */
285			$sessions = [];
286
287			$qb = $sessionRepo->createQueryBuilder('s');
288			$qbParams = [
289			'user' => $user->getId(),
290			'relationType' => Session::STUDENT,
291			];
292
293			$qb
294			->innerJoin('s.users', 'su')
295			->where(
296			$qb->expr()->andX(
297			$qb->expr()->eq('su.user', ':user'),
298			$qb->expr()->neq('su.relationType', ':relationType')
299			)
300			)
301			;
302
303			if ($sessionListFromCourseCoach) {
304			$qb->orWhere($qb->expr()->in('s.id', ':sessionListFromCourseCoach'));
305
306			$qbParams['coachCourseConditions'] = $sessionListFromCourseCoach;
307			}
308
309			$result = $qb
310			->orderBy('s.accessStartDate')
311			->addOrderBy('s.accessEndDate')
312			->addOrderBy('s.title')
313			->setMaxResults($sessionLimit)
314			->setParameters($qbParams)
315			->getQuery()
316			->getResult()
317			;
318
319			/** @var Session $row */
320			foreach ($result as $row) {
321			$row->setAccessVisibilityByUser($user);
322
323			$sessions[$row->getId()] = $row;
324			}
325
326			$qb = $sessionRepo->createQueryBuilder('s');
327			$qbParams = [
328			'user' => $user->getId(),
329			'relationType' => Session::GENERAL_COACH,
330			];
331
332			$qb
333			->innerJoin('s.users', 'sru')
334			->where(
335			$qb->expr()->andX(
336			$qb->expr()->eq('sru.user', ':user'),
337			$qb->expr()->neq('sru.relationType', ':relationType')
338			)
339			)
340			;
341
342			if ($sessionListFromCourseCoach) {
343			$qb->orWhere($qb->expr()->in('s.id', ':sessionListFromCourseCoach'));
344
345			$qbParams['coachCourseConditions'] = $sessionListFromCourseCoach;
346			}
347
348			$result = $qb
349			->orderBy('s.accessStartDate')
350			->addOrderBy('s.accessEndDate')
351			->addOrderBy('s.title')
352			->setParameters($qbParams)
353			->getQuery()
354			->getResult()
355			;
356
357			/** @var Session $row */
358			foreach ($result as $row) {
359			$row->setAccessVisibilityByUser($user);
360
361			$sessions[$row->getId()] = $row;
362			}
363
364			if ($isAllowedToCreateCourse) {
365			foreach ($sessions as $enreg) {
366			if (Session::INVISIBLE == $enreg->getAccessVisibility()) {
367			continue;
368			}
369
370			$coursesAsGeneralCoach = $sessionRepo->getSessionCoursesByStatusInUserSubscription(
371			$user,
372			$enreg,
373			Session::GENERAL_COACH,
374			$url
375			);
376			$coursesAsCourseCoach = $sessionRepo->getSessionCoursesByStatusInCourseSubscription(
377			$user,
378			$enreg,
379			Session::COURSE_COACH,
380			$url
381			);
382
383			// This query is horribly slow when more than a few thousand
384			// users and just a few sessions to which they are subscribed
385			$coursesInSession = array_merge($coursesAsGeneralCoach, $coursesAsCourseCoach);
386
387			/** @var SessionRelCourse $resultRow */
388			foreach ($coursesInSession as $resultRow) {
389			$sid = $resultRow->getSession()->getId();
390			$cid = $resultRow->getCourse()->getId();
391
392			$personalCourseList["$sid - $cid"] = [
393			'cid' => $cid,
394			'sid' => $sid,
395			];
396			}
397			}
398			}
399
400			foreach ($sessions as $enreg) {
401			if (Session::INVISIBLE == $enreg->getAccessVisibility()) {
402			continue;
403			}
404
405			// This query is very similar to the above query,
406			// but it will check the session_rel_course_user table if there are courses registered to our user or not */
407			$qb = $sessionCourseUserRepo->createQueryBuilder('scu');
408
409			$result = $qb
410			->select('c.id as cid', 's.id AS sid')
411			->innerJoin('scu.course', 'c', Join::WITH, 'scu.session = :session')
412			->innerJoin('scu.session', 's')
413			->leftJoin('scu.user', 'u')
414			->where($qb->expr()->eq('scu.user', ':user'))
415			->orderBy('c.title')
416			->setParameters([
417			'session' => $enreg->getId(),
418			'user' => $user->getId(),
419			])
420			->getQuery()
421			->getResult()
422			;
423
424			foreach ($result as $resultRow) {
425			$key = $resultRow['sid'].' - '.$resultRow['cid'];
426
427			if (!isset($personalCourseList[$key])) {
428			$personalCourseList[$key] = $resultRow;
429			}
430			}
431			}
432
433			return $personalCourseList;
434			}
435
436			/**
437			* Returns all courses assigned to a specific AccessUrl.
438			*
439			* @return Course[]
440			*/
441			public function getCoursesByAccessUrl(AccessUrl $url): array
442			{
443			return $this->createQueryBuilder('c')
444			->innerJoin('c.urls', 'u')
445			->where('u.url = :url')
446			->setParameter('url', $url)
447			->orderBy('c.title', 'ASC')
448			->getQuery()
449			->getResult()
450			;
451			}
452
453			public function getUsersByCourse(Course $course): array
454			{
455			$qb = $this->getEntityManager()->createQueryBuilder();
456
457			$qb
458			->select('DISTINCT user')
459			->from(User::class, 'user')
460			->innerJoin(CourseRelUser::class, 'courseRelUser', Join::WITH, 'courseRelUser.user = user.id')
461			->where('courseRelUser.course = :course')
462			->setParameter('course', $course)
463			->orderBy('user.lastname', 'ASC')
464			->addOrderBy('user.firstname', 'ASC')
465			;
466
467			$query = $qb->getQuery();
468
469			return $query->getResult();
470			}
471			}
472

chamilo / chamilo-lms

Pull Request — master (#6896)

CourseRepository::getSubscribedUsersByStatus() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like