src/Repository/TeacherRepository.php line 24

Open in your IDE?
  1. <?php
  3. namespace App\Repository;
  5. use App\Entity\Subject;
  6. use App\Entity\SubjectLevel;
  7. use App\Entity\Teacher;
  8. use App\Entity\Category;
  9. use Doctrine\ORM\Query\Expr\Join;
  10. use Doctrine\Persistence\ManagerRegistry;
  11. use Symfony\Bridge\Doctrine\RegistryInterface;
  12. use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
  14. /**
  15.  * @method Teacher|null find($id, $lockMode = null, $lockVersion = null)
  16.  * @method Teacher|null findOneBy(array $criteria, array $orderBy = null)
  17.  * @method Teacher[]    findAll()
  18.  * @method Teacher[]    findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
  19.  */
  20. class TeacherRepository extends ServiceEntityRepository
  21. {
  22.     public function __construct(ManagerRegistry $registry)
  23.     {
  24.         parent::__construct($registry, Teacher::class);
  25.     }
  27.     // /**
  28.     //  * @return Teacher[] Returns an array of Teacher objects
  29.     //  */
  30.     /*
  31.     public function findByExampleField($value)
  32.     {
  33.         return $this->createQueryBuilder('t')
  34.             ->andWhere('t.exampleField = :val')
  35.             ->setParameter('val', $value)
  36.             ->orderBy('t.id', 'ASC')
  37.             ->setMaxResults(10)
  38.             ->getQuery()
  39.             ->getResult()
  40.         ;
  41.     }
  42.     */
  44.     public function findAllForAdminList()
  45.     {
  46.         return $this->createQueryBuilder('t')
  47.                     ->leftJoin('t.userId', 't_u')
  48.                     ->addSelect('t_u')
  49.                     ->leftJoin('t.categories', 't_c')
  50.                     ->addSelect('t_c')
  51.                     ->leftJoin('t.teacherSubjects', 't_ts')
  52.                     ->addSelect('t_ts')
  53.                     ->leftJoin('t_ts.subject', 't_ts_s')
  54.                     ->addSelect('t_ts_s')
  55.                     ->getQuery()
  56.                     ->getResult();
  57.     }
  60.     //Optimisé (par moi)
  61.     public function teachersList()
  62.     {
  63.         return $this->createQueryBuilder('t')
  64.                     ->addSelect('user_id')
  65.                     ->leftJoin('t.userId', 'user_id')
  66.                     ->getQuery()
  67.                     ->getResult();
  69.     }
  71.     public function getActifTeachers()
  72.     {
  73.         return $this->createQueryBuilder('t')
  74.                     ->leftJoin('t.userId', 'userId')
  75.                     ->addSelect('userId')
  76.                     ->andWhere('t.isActif = :true')
  77.                     ->setParameter('true', true)
  78.                     ->orderBy('userId.firstname')
  79.                     ->getQuery()
  80.                     ->getResult();
  82.     }
  84.     public function getActifTeachersForSubjectLevel(SubjectLevel $subjectLevel, ?bool $forGroup = null)
  85.     {
  86.         $query = $this->createQueryBuilder('t')
  87.                       ->leftJoin('t.userId', 'userId')
  88.                       ->innerJoin('t.teacherSubjects', 'teacherSubjects')
  89.                       ->andWhere('teacherSubjects.subject = :subject')
  90.                       ->setParameter('subject', $subjectLevel->getSubject())
  91.                       ->innerJoin('teacherSubjects.fromLevel', 'fromLevel')
  92.                       ->innerJoin('teacherSubjects.toLevel', 'toLevel')
  93.                       ->innerJoin(SubjectLevel::class, 'subjectlevel', Join::WITH, 'subjectlevel.order >= fromLevel.order AND subjectlevel.order <= toLevel.order AND subjectlevel.id = :subjectLevelId')
  94.                       ->setParameter('subjectLevelId', $subjectLevel->getId())
  95.                       ->addSelect('userId')
  96.                       ->andWhere('t.isActif = :true')
  97.                       ->setParameter('true', true)
  98.                       ->orderBy('userId.firstname');
  100.         if ($forGroup === true) {
  101.             $query->andWhere('teacherSubjects.isGroup = 1');
  102.         }
  104.         return $query->getQuery()
  105.                      ->getResult();
  107.     }
  109.     public function getInactiveTeachers()
  110.     {
  111.         return $this->createQueryBuilder('t')
  112.                     ->leftJoin('t.userId', 'userId')
  113.                     ->addSelect('userId')
  114.                     ->andWhere('t.isActif = :false')
  115.                     ->setParameter('false', false)
  116.                     ->orderBy('userId.firstname')
  117.                     ->getQuery()
  118.                     ->getResult();
  120.     }
  122.     //Optimisé (par moi)
  123.     public function findOneTeacherById($id)
  124.     {
  125.         return $this->createQueryBuilder('t')
  126.                     ->addSelect('userId')
  127.                     ->leftJoin('t.userId', 'userId')
  128.                     ->andWhere('t.id = :id')
  129.                     ->setParameter('id', $id)
  130.                     ->getQuery()
  131.                     ->getOneOrNullResult();
  133.     }
  136.     public function findTeachersHasVideoCourses()
  137.     {
  138.         return $this->createQueryBuilder('t')
  139.                     ->leftJoin('t.videoPrograms', 'v')
  140.                     ->addSelect('v')
  141.                     ->andWhere('v.teacher = t')
  142.                     ->getQuery()
  143.                     ->getResult();
  145.     }
  147.     public function findTeachersWithFreeSchedules()
  148.     {
  149.         $currentDate = new \DateTimeImmutable();
  150.         $tomorrow    = $currentDate->modify('+1 day');
  151.         $limiteDate  = $currentDate->modify('+7 days');
  153.         return $this->createQueryBuilder('t')
  154.                     ->leftJoin('t.schedules', 's')
  155.                     ->addSelect('s')
  156.                     ->andWhere('s.isFree = :true')
  157.                     ->andWhere('s.forCatchUp = :false')
  158.                     ->setParameter('false', false)
  159.                     ->andWhere('s.isBlocked = :false')
  160.                     ->setParameter('false', false)
  161.                     ->andWhere('s.freeDate BETWEEN :tomorrow AND :limiteDate')
  162.                     ->setParameter('true', true)
  163.                     ->setParameter('tomorrow', $tomorrow)
  164.                     ->setParameter('limiteDate', $limiteDate)
  165.                     ->orderBy('s.freeDate', 'ASC')
  166.                     ->getQuery()
  167.                     ->getResult();
  168.     }
  170.     public function findTeachersWithFreeSchedulesByCategoryAndGender($categoryNameId, $genderId, $level, $isChild)
  171.     {
  172.         //reception possible $level : beginner, intermediate, advanced, null
  173.         //reception possible isChild : "0", "1", null
  174.         //UTILISÉ DANS L'INDEX DES HORAIRES LIBRES AVANT LE CHECKOUT
  176.         $currentDate = new \DateTimeImmutable('03:00');
  177.         $tomorrow    = $currentDate->modify('+1 day');
  178.         $limitDate   = $currentDate->modify('+8 days');
  180.         $qb = $this->createQueryBuilder('teacher')
  181.                    ->leftJoin('teacher.schedules', 's')
  182.                    ->leftJoin('s.scheduleInstitute', 's_si')
  183.                    ->leftJoin('teacher.teacherSubjects', 'ts')
  184.                    ->leftJoin('ts.subject', 'ts_s')
  185.                    ->leftJoin('ts_s.category', 'ts_s_c')
  186.                    ->leftJoin('teacher.categories', 'c')
  187.                    ->leftJoin('teacher.userId', 'userId')
  188.                    ->leftJoin('userId.gender', 'g')
  189.                    ->addSelect('s')
  190.                    ->addSelect('s_si')
  191.                    ->addSelect('ts')
  192.                    ->addSelect('ts_s', 'ts_s_c')
  193.                    ->addSelect('c')
  194.                    ->addSelect('userId')
  195.                    ->addSelect('g')
  196.                    ->andWhere('s.isFree = :true')
  197.                    ->andWhere('s.forCatchUp = :false')
  198.                    ->setParameter('false', false)
  199.                    ->andWhere('s.isBlocked = :false')
  200.                    ->setParameter('false', false)
  201.                    ->andWhere('s.forVideo = :false')
  202.                    ->setParameter('false', false)
  203.                    ->andWhere('s.isForLevelTest = :false')
  204.                    ->setParameter('false', false)
  205.                    ->andWhere('c.id = :categoryNameId')
  206.                    ->setParameter('categoryNameId', $categoryNameId)
  207.                    ->andWhere('s.freeDate BETWEEN :tomorrow AND :limitDate')
  208.                    ->setParameter('true', true)
  209.                    ->setParameter('tomorrow', $tomorrow)
  210.                    ->setParameter('limitDate', $limitDate);
  213.         if ($level) {
  214.             if ($level == "beginner") {
  215.                 $qb->andWhere('teacher.isBeginner = :true')
  216.                    ->setParameter('true', true);
  218.             }
  220.             if ($level == "intermediate") {
  221.                 $qb->andWhere('teacher.isIntermediate = :true')
  222.                    ->setParameter('true', true);
  224.             }
  226.             if ($level == "advanced") {
  227.                 $qb->andWhere('teacher.isAdvanced = :true')
  228.                    ->setParameter('true', true);
  230.             }
  231.         }
  233.         if ($isChild) {
  235.             if ($isChild == "1") {
  236.                 $qb->andWhere('teacher.forChildren = :true')
  237.                    ->setParameter('true', true)
  238.                    ->andWhere('g.id = :feminin OR g.id= :masculin')
  239.                    ->setParameter('feminin', 2)
  240.                    ->setParameter('masculin', 1);
  242.             }
  243.         } else {
  244.             $qb->andWhere('g.id = :genderId')
  245.                ->setParameter('genderId', $genderId);
  247.         }
  250.         $qb->orderBy('s.freeDate', 'ASC');
  252.         return $qb->getQuery()->getResult();
  254.     }
  257.     public function findTeachersWithFreeSchedulesByCategoryAndGenderForTickets($categoryNameId, $genderId)
  258.     {
  259.         $currentDate = new \DateTimeImmutable('03:00');
  260.         $tomorrow    = $currentDate->modify('+1 day');
  261.         $limitDate   = $currentDate->modify('+8 days');
  263.         return $this->createQueryBuilder('t')
  264.                     ->leftJoin('t.schedules', 's')
  265.                     ->leftJoin('s.scheduleInstitute', 's_si')
  266.                     ->leftJoin('t.teacherSubjects', 'ts')
  267.                     ->leftJoin('ts.subject', 'ts_s')
  268.                     ->leftJoin('ts_s.category', 'ts_s_c')
  269.                     ->leftJoin('t.categories', 'c')
  270.                     ->leftJoin('t.userId', 'userId')
  271.                     ->leftJoin('userId.gender', 'g')
  272.                     ->addSelect('s')
  273.                     ->addSelect('s_si')
  274.                     ->addSelect('ts')
  275.                     ->addSelect('ts_s', 'ts_s_c')
  276.                     ->addSelect('c')
  277.                     ->addSelect('userId')
  278.                     ->addSelect('g')
  279.                     ->andWhere('s.isFree = :true')
  280.                     ->andWhere('s.isBlocked = :false')
  281.                     ->setParameter('false', false)
  282.                     ->andWhere('s.isForLevelTest = :false')
  283.                     ->setParameter('false', false)
  284.                     ->andWhere('c.id = :categoryNameId')
  285.                     ->setParameter('categoryNameId', $categoryNameId)
  286.                     ->andWhere('g.id = :genderId')
  287.                     ->setParameter('genderId', $genderId)
  288.                     ->andWhere('s.freeDate BETWEEN :tomorrow AND :limitDate')
  289.                     ->setParameter('true', true)
  290.                     ->setParameter('tomorrow', $tomorrow)
  291.                     ->setParameter('limitDate', $limitDate)
  292.                     ->orderBy('s.freeDate', 'ASC')
  293.                     ->getQuery()
  294.                     ->getResult();
  295.     }
  297.     //For Level Test
  298.     public function findTeachersWithFreeSchedulesBySubjectAndGenderForTest($subjectId, $genderId, $limitDateRegistration)
  299.     {
  300.         $currentDate = new \DateTimeImmutable('03:00');
  301.         $tomorrow    = $currentDate->modify('+1 day');
  302.         //$limitDate = $limitDateRegistration;
  303.         $limitDate = $currentDate->modify('+2 weeks');
  305.         return $this->createQueryBuilder('t')
  306.                     ->leftJoin('t.schedules', 's')
  307.                     ->leftJoin('s.scheduleInstitute', 's_si')
  308.                     ->leftJoin('t.teacherSubjects', 'ts')
  309.                     ->leftJoin('ts.subject', 'ts_s')
  310.                     ->leftJoin('ts_s.category', 'ts_s_c')
  311.                     ->leftJoin('t.categories', 'c')
  312.                     ->leftJoin('t.userId', 'userId')
  313.                     ->leftJoin('userId.gender', 'g')
  314.                     ->leftJoin('t.teacherLeveltests', 'teacherLeveltests')
  315.                     ->addSelect('s')
  316.                     ->addSelect('s_si')
  317.                     ->addSelect('ts')
  318.                     ->addSelect('ts_s', 'ts_s_c')
  319.                     ->addSelect('c')
  320.                     ->addSelect('userId')
  321.                     ->addSelect('g')
  322.                     ->addSelect('teacherLeveltests')
  323.                     ->andWhere('s.isFree = :true')
  324.                     ->andWhere('s.isBlocked = :false')
  325.                     ->setParameter('false', false)
  326.                     ->andWhere('teacherLeveltests.subject = :subjectId')
  327.                     ->setParameter('subjectId', $subjectId)
  328.                     ->andWhere('g.id = :genderId')
  329.                     ->setParameter('genderId', $genderId)
  330.                     ->andWhere('s.freeDate BETWEEN :tomorrow AND :limitDate')
  331.                     ->setParameter('true', true)
  332.                     ->setParameter('tomorrow', $tomorrow)
  333.                     ->setParameter('limitDate', $limitDate)
  334.                     ->orderBy('s.freeDate', 'ASC')
  335.                     ->getQuery()
  336.                     ->getResult();
  337.     }
  339.     public function findTeachersWithFreeSchedulesBySubjectAndGenderForTestForRamadan($subjectId, $genderId)
  340.     {
  341.         $currentDate = new \DateTimeImmutable('03:00');
  342.         $tomorrow    = $currentDate->modify('+1 day');
  343.         $limitDate   = $currentDate->modify('+2 weeks');
  345.         return $this->createQueryBuilder('t')
  346.                     ->leftJoin('t.schedules', 's')
  347.                     ->leftJoin('s.scheduleInstitute', 's_si')
  348.                     ->leftJoin('t.teacherSubjects', 'ts')
  349.                     ->leftJoin('ts.subject', 'ts_s')
  350.                     ->leftJoin('ts_s.category', 'ts_s_c')
  351.                     ->leftJoin('t.categories', 'c')
  352.                     ->leftJoin('t.userId', 'userId')
  353.                     ->leftJoin('userId.gender', 'g')
  354.                     ->leftJoin('t.teacherLeveltests', 'teacherLeveltests')
  355.                     ->addSelect('s')
  356.                     ->addSelect('s_si')
  357.                     ->addSelect('ts')
  358.                     ->addSelect('ts_s', 'ts_s_c')
  359.                     ->addSelect('c')
  360.                     ->addSelect('userId')
  361.                     ->addSelect('g')
  362.                     ->addSelect('teacherLeveltests')
  363.                     ->andWhere('s.isForLevelTest = :true')
  364.                     ->setParameter('true', true)
  365.                     ->andWhere('teacherLeveltests.subject = :subjectId')
  366.                     ->setParameter('subjectId', $subjectId)
  367.                     ->andWhere('g.id = :genderId')
  368.                     ->setParameter('genderId', $genderId)
  369.                     ->andWhere('s.freeDate BETWEEN :tomorrow AND :limitDate')
  370.                     ->setParameter('tomorrow', $tomorrow)
  371.                     ->setParameter('limitDate', $limitDate)
  372.                     ->orderBy('s.freeDate', 'ASC')
  373.                     ->getQuery()
  374.                     ->getResult();
  375.     }
  377.     public function findTeachersWithFreeSchedulesByCategoryAndGenderWithoutCurrentTeacher($categoryNameId, $genderId, $currentTeacher)
  378.     {
  379.         $currentDate = new \DateTimeImmutable('03:00');
  380.         $tomorrow    = $currentDate->modify('+1 day');
  381.         $limitDate   = $currentDate->modify('+8 days');
  383.         return $this->createQueryBuilder('teacher')
  384.                     ->leftJoin('teacher.schedules', 'schedules')
  385.                     ->leftJoin('teacher.teacherSubjects', 'teacher_subjects')
  386.                     ->leftJoin('teacher.categories', 'categories')
  387.                     ->leftJoin('teacher.userId', 'userId')
  388.                     ->leftJoin('userId.gender', 'gender')
  389.                     ->addSelect('schedules')
  390.                     ->addSelect('teacher_subjects')
  391.                     ->addSelect('categories')
  392.                     ->addSelect('userId')
  393.                     ->addSelect('gender')
  394.                     ->andWhere('schedules.isFree = :true')
  395.                     ->andWhere('schedules.forCatchUp = :false')
  396.                     ->setParameter('false', false)
  397.                     ->andWhere('schedules.isBlocked = :false')
  398.                     ->setParameter('false', false)
  399.                     ->andWhere('schedules.forVideo = :false')
  400.                     ->setParameter('false', false)
  401.                     ->andWhere('schedules.isForLevelTest = :false')
  402.                     ->setParameter('false', false)
  403.                     ->andWhere('categories.id = :categoryNameId')
  404.                     ->setParameter('categoryNameId', $categoryNameId)
  405.                     ->andWhere('gender.id = :genderId')
  406.                     ->setParameter('genderId', $genderId)
  407.                     ->andWhere('schedules.freeDate BETWEEN :tomorrow AND :limitDate')
  408.                     ->setParameter('true', true)
  409.                     ->andWhere('teacher != :currentTeacher')
  410.                     ->setParameter('currentTeacher', $currentTeacher)
  411.                     ->setParameter('tomorrow', $tomorrow)
  412.                     ->setParameter('limitDate', $limitDate)
  413.                     ->orderBy('schedules.freeDate', 'ASC')
  414.                     ->getQuery()
  415.                     ->getResult();
  416.     }
  417.     /*
  418.     public function findOneBySomeField($value): ?Teacher
  419.     {
  420.         return $this->createQueryBuilder('t')
  421.             ->andWhere('t.exampleField = :val')
  422.             ->setParameter('val', $value)
  423.             ->getQuery()
  424.             ->getOneOrNullResult()
  425.         ;
  426.     }
  427.     */
  428. }