Symfony Profiler

<?php
namespace App\Repository;
use App\Entity\CourseUserEnrollment;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;
/**
* @method CourseUserEnrollment|null find($id, $lockMode = null, $lockVersion = null)
* @method CourseUserEnrollment|null findOneBy(array $criteria, array $orderBy = null)
* @method CourseUserEnrollment[] findAll()
* @method CourseUserEnrollment[] findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
*/
class CourseUserEnrollmentRepository extends ServiceEntityRepository
{
public function __construct(ManagerRegistry $registry)
{
parent::__construct($registry, CourseUserEnrollment::class);
}
/**
* @throws ORMException
* @throws OptimisticLockException
*/
public function remove(CourseUserEnrollment $entity, bool $flush = true): void
{
$this->_em->remove($entity);
if ($flush) {
$this->_em->flush();
}
}
public function findByPage($page, $resultsPerPage, $order = 'ASC', $orderBy = "", $keyword = "", $user, array $statuses = ['enrolled', 'missed', 'failed'], $course = "", $level = "", $showDeleted = false)
{
try {
$enrollments = $this->createQueryBuilder('cue')
->leftJoin('cue.course', 'c')
->leftJoin('cue.user','u')
->leftJoin('u.personalInfo', 'pi')
->andWhere('u.isActive = :ia')
->setParameter('ia', 1)
->setMaxResults($resultsPerPage)
->setFirstResult(($page - 1) * $resultsPerPage);
if ($showDeleted) {
$enrollments->andWhere('cue.deletedAt IS not NULL');
} else {
$enrollments->andWhere('cue.deletedAt IS NULL');
}
if ($keyword !== "") {
$enrollments->andWhere('c.title LIKE :k OR pi.firstName LIKE :k OR pi.middleName LIKE :k OR pi.lastName LIKE :k OR CONCAT(pi.firstName, \' \',pi.lastName) LIKE :k OR CONCAT(pi.firstName, \' \',pi.middleName, \' \',pi.lastName) LIKE :k')->setParameter('k', '%' . $keyword . '%');
// $enrollments->andWhere('c.title LIKE :val')->setParameter('val', '%' . $keyword . '%');
}
if ($course != "") {
$enrollments->andWhere('c.id = :cid')->setParameter('cid', $course);
}
if ($level != "") {
$enrollments->andWhere('c.level = :level')->setParameter('level', $level);
}
if (!empty($user)){
$enrollments->andWhere('cue.user = :user')->setParameter('user', $user);
}
if (!empty($statuses)){
$enrollments->andWhere('cue.status IN (:statuses)')->setParameter('statuses', $statuses);
}
if ($orderBy === "") {
$enrollments->addSelect("(CASE WHEN cue.enrolledAt <= :now THEN 0 ELSE 1 END) AS HIDDEN order ")
->setParameter('now', new \DateTimeImmutable())
->addOrderBy('order', 'ASC')->addOrderBy('cue.createdAt', 'DESC');
} elseif($orderBy == "level"){
$enrollments->addSelect("(CASE WHEN c.level = 'foundation' THEN 1 WHEN c.level = 'intermediate' THEN 2 WHEN c.level = 'advanced' THEN 3 ELSE 0 END) as HIDDEN courseLevel");
$enrollments->groupBy('cue.id');
$enrollments->orderBy('courseLevel', $order);
} elseif($orderBy == 'user') {
$enrollments->orderBy("pi.firstName", $order);
} else {
$enrollments->orderBy("cue." . $orderBy, $order);
}
return $enrollments->getQuery()->getResult();
} catch (\Exception $exception) {
return [];
}
}
public function countByPage($keyword = "", $user, array $statuses = ['enrolled', 'missed', 'failed'], $course = "",$level = '', $showDeleted = false)
{
try {
$enrollments = $this->createQueryBuilder('cue')
->leftJoin('cue.course', 'c')
->leftJoin('cue.user','u')
->leftJoin('u.personalInfo', 'pi')
->andWhere('u.isActive = :ia')
->setParameter('ia', 1)
->select('count(distinct(cue.id))');
if ($showDeleted) {
$enrollments->andWhere('cue.deletedAt IS not NULL');
} else {
$enrollments->andWhere('cue.deletedAt IS NULL');
}
if ($keyword !== "") {
$enrollments->andWhere('c.title LIKE :k OR pi.firstName LIKE :k OR pi.middleName LIKE :k OR pi.lastName LIKE :k OR CONCAT(pi.firstName, \' \',pi.lastName) LIKE :k OR CONCAT(pi.firstName, \' \',pi.middleName, \' \',pi.lastName) LIKE :k')->setParameter('k', '%' . $keyword . '%');
// $enrollments->andWhere('c.title LIKE :val')->setParameter('val', '%' . $keyword . '%');
}
if ($course != "") {
$enrollments->andWhere('c.id = :cid')->setParameter('cid', $course);
}
if ($level != "") {
$enrollments->andWhere('c.level = :level')->setParameter('level', $level);
}
if (!empty($user)){
$enrollments->andWhere('cue.user = :user')->setParameter('user', $user);
}
if (!empty($statuses)){
$enrollments->andWhere('cue.status IN (:statuses)')->setParameter('statuses', $statuses);
}
return $enrollments->getQuery()->getSingleScalarResult();
} catch (\Exception $exception) {
return [];
}
}
public function findExpireInDays($days){
$dt = new \DateTime();
$targetDay = $dt->modify('+'.$days.' days')->format('Y-m-d');
$statuses = ['failed', 'enrolled'];
$enrollments = $this->createQueryBuilder('cue')
->andWhere('cue.status IN (:statuses)')
->setParameter('statuses', $statuses)
->andWhere('cue.dueAt LIKE :day')
->setParameter('day', $targetDay.'%')
->andWhere('cue.deletedAt IS NULL');
return $enrollments->getQuery()->getResult();
}
// /**
// * @return CourseUserEnrollment[] Returns an array of CourseUserEnrollment objects
// */
/*
public function findByExampleField($value)
{
return $this->createQueryBuilder('c')
->andWhere('c.exampleField = :val')
->setParameter('val', $value)
->orderBy('c.id', 'ASC')
->setMaxResults(10)
->getQuery()
->getResult()
;
}
*/
/*
public function findOneBySomeField($value): ?CourseUserEnrollment
{
return $this->createQueryBuilder('c')
->andWhere('c.exampleField = :val')
->setParameter('val', $value)
->getQuery()
->getOneOrNullResult()
;
}
*/
}

src/Repository/CourseUserEnrollmentRepository.php line 34