src/Repository/ExamUserRepository.php line 49

Open in your IDE?
  1. <?php
  3. namespace EADPlataforma\Repository;
  5. use EADPlataforma\Entity\Exam;
  6. use EADPlataforma\Entity\ExamUser;
  7. use EADPlataforma\Entity\ExamUserAnswer;
  8. use EADPlataforma\Entity\Enrollment;
  9. use EADPlataforma\Entity\Course;
  10. use EADPlataforma\Entity\Library;
  11. use EADPlataforma\Entity\Lesson;
  12. use EADPlataforma\Entity\LessonModule;
  13. use EADPlataforma\Entity\CourseCertificate;
  14. use EADPlataforma\Entity\User;
  15. use EADPlataforma\Entity\Question;
  16. use EADPlataforma\Entity\Trash;
  17. use EADPlataforma\Enum\ExamEnum;
  18. use EADPlataforma\Enum\ExamUserEnum;
  19. use EADPlataforma\Enum\CourseEnum;
  20. use EADPlataforma\Enum\LessonEnum;
  21. use EADPlataforma\Enum\LessonModuleEnum;
  22. use EADPlataforma\Enum\QuestionEnum;
  23. use EADPlataforma\Enum\TagsMarketingEnum;
  24. use EADPlataforma\Enum\NotificationEnum;
  25. use EADPlataforma\Enum\UserEnum;
  26. use \PDO;
  28. /**
  29.  * @method ExamUser|null find($id, $lockMode = null, $lockVersion = null)
  30.  * @method ExamUser|null findOneBy(array $criteria, array $orderBy = null)
  31.  * @method ExamUser[]    findAll()
  32.  * @method ExamUser[]    findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
  33.  */
  34. class ExamUserRepository extends AbstractRepository
  35. {
  36.     public function getEntityClass(){
  37.         return ExamUser::class;
  38.     }
  40.     public function getConnectionName(){
  41.         return "school";
  42.     }
  44.     public function findItems()
  45.     {
  46.         return $this->createQueryBuilder('eu')->getQuery()->getResult();
  47.     }
  49.     public function getLastContentByExam(Exam $exam, User $user)
  50.     {
  51.         $examRepository = $this->em->getRepository(Exam::class);
  52.         $lessonRepository = $this->em->getRepository(Lesson::class);
  53.         $libraryRepository = $this->em->getRepository(Library::class);
  55.         $examType = $exam->getType();
  56.         $course = $exam->getCourse();
  57.         $lessonModule = $exam->getLessonModule();
  58.         $lesson = $exam->getLesson();
  60.         $contentPagesNumber = null;
  61.         $contentDuration = null;
  62.         $contentType = null;
  63.         $contentThumb = null;
  64.         $lessonId = null;
  65.         $moduleId = null;
  66.         $courseId = $course->getId();
  67.         $lessonRef = null;
  68.         $type = "lesson";
  70.         if($examType == ExamEnum::QUIZ){
  71.             $lessonRef = $lesson;
  72.         }else if($examType == ExamEnum::LESSON){
  73.             $lessonRef = $lesson;
  74.         }else if($examType == ExamEnum::MODULE){
  75.             $lessonRef = $lessonRepository->getLastLesson($course, $lessonModule);
  76.         }else if($examType == ExamEnum::COURSE){
  77.             $lessonRef = $lessonRepository->getLastLesson($course);
  78.         }
  80.         if(empty($lessonRef)){
  81.             return;
  82.         }
  84.         $lessonId = $lessonRef->getId();
  85.         $moduleRef = $lessonRef->getLessonModule();
  86.         $moduleId = $moduleRef->getId();
  87.         $examLast = null;
  89.         if($examType == ExamEnum::COURSE){
  90.             
  91.             $filter = [
  92.                 "course" => $courseId,
  93.                 "lessonModule" => $moduleId,
  94.                 "type" => ExamEnum::MODULE,
  95.                 "status" => ExamEnum::PUBLISHED,
  96.                 "deleted" => ExamEnum::ITEM_NO_DELETED
  97.             ];
  99.             $type = "exam";
  100.             $examLast = $examRepository->findOneBy($filter);
  101.         }
  103.         if(empty($examLast) && ($examType == ExamEnum::COURSE || $examType == ExamEnum::MODULE)){
  104.             $filter = [
  105.                 "course" => $courseId,
  106.                 "lessonModule" => $moduleId,
  107.                 "lesson" => $lessonId,
  108.                 "type" => ExamEnum::LESSON,
  109.                 "status" => ExamEnum::PUBLISHED,
  110.                 "deleted" => ExamEnum::ITEM_NO_DELETED
  111.             ];
  113.             $type = "exam";
  114.             $examLast = $examRepository->findOneBy($filter);
  115.         }
  117.         if(empty($examLast)){
  118.             $filter = [
  119.                 "course" => $courseId,
  120.                 "lessonModule" => $moduleId,
  121.                 "lesson" => $lessonId,
  122.                 "type" => ExamEnum::QUIZ,
  123.                 "status" => ExamEnum::PUBLISHED,
  124.                 "deleted" => ExamEnum::ITEM_NO_DELETED
  125.             ];
  127.             $type = "quiz";
  128.             $examLast = $examRepository->findOneBy($filter);
  129.         }
  131.         if(empty($examLast)){
  132.             $type = "lesson";
  133.             $library = $lessonRef->getLibrary();
  134.             if($library){
  135.                 $contentPagesNumber = $library->getPagesNumber();
  136.                 $contentDuration = $library->getDuration();
  137.                 $contentType = $library->getType();
  138.                 $contentThumb = $libraryRepository->getCover($library);
  139.             }
  140.         }
  142.         $timeUtil = $this->generalService->getUtil('DateTimeUtil');
  143.         
  144.         return (object)[
  145.             "id" => (int)($examLast ? $examLast->getId() : $lessonRef->getId()),
  146.             "lessonId" => $lessonId,
  147.             "moduleId" => $moduleId,
  148.             "courseId" => $courseId,
  149.             "title" => ($examLast ? $examLast->getTitle() : $lessonRef->getTitle()),
  150.             "type" => $type,
  151.             "contentPagesNumber" => $contentPagesNumber,
  152.             "contentDuration" => $contentDuration ? $timeUtil->timeToSec($contentDuration) : null,
  153.             "contentType" => $contentType,
  154.             "contentThumb" => $contentThumb,
  155.             "isAccessible" => true,
  156.             "acessMessage" => null,
  157.         ];
  158.     }
  160.     public function getNextContentByExam(Exam $exam, User $user)
  161.     {
  162.         $examRepository = $this->em->getRepository(Exam::class);
  163.         $enrollmentRepository = $this->em->getRepository(Enrollment::class);
  164.         $lessonRepository = $this->em->getRepository(Lesson::class);
  165.         $libraryRepository = $this->em->getRepository(Library::class);
  166.         $courseRepository = $this->em->getRepository(Course::class);
  168.         $examType = $exam->getType();
  169.         $course = $exam->getCourse();
  170.         $lessonModule = $exam->getLessonModule();
  171.         $lesson = $exam->getLesson();
  173.         $contentPagesNumber = null;
  174.         $contentDuration = null;
  175.         $contentType = null;
  176.         $contentThumb = null;
  177.         $lessonId = null;
  178.         $moduleId = null;
  179.         $courseId = $course->getId();
  180.         $lessonRef = null;
  181.         $type = "exam";
  182.         $examNext = null;
  183.         $isAccessible = false;
  184.         $acessMessage = null;
  186.         if($examType == ExamEnum::COURSE){
  187.             return;
  188.         }
  190.         if($examType == ExamEnum::QUIZ){
  191.             $lessonRef = $lesson;
  193.             $filter = [
  194.                 "course" => $courseId,
  195.                 "lessonModule" => $moduleId,
  196.                 "lesson" => $lessonId,
  197.                 "type" => ExamEnum::LESSON,
  198.                 "status" => ExamEnum::PUBLISHED,
  199.                 "deleted" => ExamEnum::ITEM_NO_DELETED
  200.             ];
  202.             $examNext = $examRepository->findOneBy($filter);
  203.         }else if($examType == ExamEnum::LESSON){
  204.             $lessonRef = $lesson;
  205.         }else if($examType == ExamEnum::MODULE){
  206.             $lessonRef = $lessonRepository->getLastLesson($course, $lessonModule);
  207.         }
  209.         if(!$examNext && $lessonRepository->isLastModuleLesson($lessonRef)){
  210.             $filter = [
  211.                 "course" => $courseId,
  212.                 "lessonModule" => $moduleId,
  213.                 "type" => ExamEnum::MODULE,
  214.                 "status" => ExamEnum::PUBLISHED,
  215.                 "deleted" => ExamEnum::ITEM_NO_DELETED
  216.             ];
  218.             $examNext = $examRepository->findOneBy($filter);
  219.         }
  221.         if(!$examNext && $lessonRepository->isLastCourseLesson($lessonRef)){
  222.             $filter = [
  223.                 "course" => $courseId,
  224.                 "type" => ExamEnum::COURSE,
  225.                 "status" => ExamEnum::PUBLISHED,
  226.                 "deleted" => ExamEnum::ITEM_NO_DELETED
  227.             ];
  229.             $examNext = $examRepository->findOneBy($filter);
  230.         }
  232.         if(!$examNext){
  233.             if(!$lessonRef){
  234.                 return;
  235.             }
  237.             $type = "lesson";
  238.             $nextLessonId = $lessonRepository->getLessonIdAfterThat($lessonRef);
  239.             if(empty($nextLessonId)){
  240.                 return;
  241.             }
  243.             $lessonRef = $lessonRepository->find($nextLessonId);
  245.             if(!$lessonRef){
  246.                 return;
  247.             }
  249.             $enrollment = $enrollmentRepository->findOneBy([
  250.                 "user" => $user->getId(),
  251.                 "course" => $courseId,
  252.             ]);
  254.             if($enrollment){
  255.                 $isStudent = $courseRepository->isStudent($course);
  256.                 $infoAccess = $lessonRepository->checkLessonIsAccessibleToUser(
  257.                     $lessonRef, 
  258.                     $enrollment, 
  259.                     null, 
  260.                     $isStudent
  261.                 );
  263.                 $isAccessible = $infoAccess->isAccessible;
  264.                 $acessMessage = $infoAccess->message;
  265.             }
  267.             $library = $lessonRef->getLibrary();
  268.             if($library){
  269.                 $contentPagesNumber = $library->getPagesNumber();
  270.                 $contentDuration = $library->getDuration();
  271.                 $contentType = $library->getType();
  272.                 $contentThumb = $libraryRepository->getCover($library);
  273.             }
  274.         }else{
  275.             $infoAccess = $examRepository->checkExamIsAccessible($examNext);
  277.             $isAccessible = $infoAccess->isAccessible;
  278.             $acessMessage = $infoAccess->message;
  279.         }
  281.         $timeUtil = $this->generalService->getUtil('DateTimeUtil');
  283.         return (object)[
  284.             "id" => (int)($examNext ? $examNext->getId() : $lessonRef->getId()),
  285.             "lessonId" => $lessonId,
  286.             "moduleId" => $moduleId,
  287.             "courseId" => $courseId,
  288.             "title" => ($examNext ? $examNext->getTitle() : $lessonRef->getTitle()),
  289.             "type" => $type,
  290.             "contentPagesNumber" => $contentPagesNumber,
  291.             "contentDuration" => (
  292.                 $contentDuration ? 
  293.                 $timeUtil->timeToSec($contentDuration) : 
  294.                 null
  295.             ),
  296.             "contentType" => $contentType,
  297.             "contentThumb" => $contentThumb,
  298.             "isAccessible" => $isAccessible,
  299.             "acessMessage" => $acessMessage,
  300.         ];
  301.     }
  303.     public function getDataIndexNew(Exam $exam, User $user)
  304.     {
  305.         $examUser = $this->findOneBy([
  306.             "user" => $user->getId(),
  307.             "exam" => $exam->getId(),
  308.             "inactive" => ExamUserEnum::NO,
  309.             "deleted" => ExamUserEnum::ITEM_NO_DELETED
  310.         ]);
  312.         $attemptsInfo = $this->getAttemptsInfo($exam, $examUser);
  314.         $dateTimeUtil = $this->generalService->getUtil('DateTimeUtil');
  315.         
  316.         $timeSeconds = $dateTimeUtil->timeToSec($exam->getExamTime());
  317.         $timeRestToEndSeconds = $timeSeconds;
  318.         $progress = 0;
  319.         $withTemplate = false;
  320.         $onlyAnswer = false;
  321.         $questions = null;
  322.         
  323.         if($examUser && $exam->getType() != ExamEnum::QUIZ){
  324.             $expired = $this->examUserIsExpired($examUser);
  325.             if($expired){
  326.                 $examUser = $this->findOneBy([
  327.                     "id" => $examUser->getId()
  328.                 ]);
  329.             }
  331.             $todaySeconds = strtotime(date('Y-m-d H:i:s'));
  332.             $dateStartSeconds = strtotime($examUser->getDateStart());
  333.             $dateExamEndSecods = $dateStartSeconds + $timeSeconds;
  335.             if($examUser->getStatus() == ExamUserEnum::IN_PROGRESS){
  336.                 $timeRestToEndSeconds = $dateExamEndSecods - $todaySeconds;
  337.                 if($timeRestToEndSeconds < 0){
  338.                     $timeRestToEndSeconds = 0;
  339.                 }
  340.             }
  342.             $progress = $this->getProgress($examUser);
  343.             $onlyAnswer = ($examUser->getStatus() != ExamUserEnum::IN_PROGRESS);
  344.             
  345.             if($exam->getShowTemplate() == QuestionEnum::YES){
  346.                 $withTemplate = $onlyAnswer;
  347.                 if($attemptsInfo->attempts == ExamEnum::YES){
  348.                     $withTemplate = false;
  349.                 }
  350.             }
  352.             $questionRepository = $this->em->getRepository(Question::class);
  353.             $questions = $questionRepository->getQuestionsByExamUser(
  354.                 $examUser, 
  355.                 $withTemplate, 
  356.                 $onlyAnswer
  357.             );
  358.         }else if($exam->getType() == ExamEnum::QUIZ){
  359.             $questions = $exam->getQuestionsArray(true);
  360.         }
  362.         $data = (object)[
  363.             "lastContent" => $this->getLastContentByExam($exam, $user),
  364.             "nextContent" => $this->getNextContentByExam($exam, $user),
  365.             "examUser" => ($examUser ? $examUser->toReturn() : null),
  366.             "examConfig" => (object)[
  367.                 "id" => $exam->getId(),
  368.                 "title" => $exam->getTitle(),
  369.                 "questionNumber" => $exam->getQuestionNumber(),
  370.                 "examWeight" => $exam->getExamWeight(),
  371.                 "examTime" => $exam->getExamTime(),
  372.                 "course" => ($exam->getCourse() ? $exam->getCourse()->getId() : null),
  373.                 "lessonModule" => (
  374.                     $exam->getLessonModule() ? 
  375.                     $exam->getLessonModule()->getId() : 
  376.                     null
  377.                 ),
  378.                 "lesson" => ($exam->getLesson() ? $exam->getLesson()->getId() : null),
  379.                 "controlTime" => $exam->getControlTime(),
  380.             ],
  381.             "timeRestToEnd" => $dateTimeUtil->secToTime($timeRestToEndSeconds),
  382.             "timeRestToEndSeconds" => $timeRestToEndSeconds,
  383.             "progress" => $progress,
  384.             "attemptDateRelease" => $attemptsInfo->attemptDateRelease,
  385.             "allowAttempt" => $attemptsInfo->attempts,
  386.             "hasAttempts" => $attemptsInfo->hasAttempts,
  387.             "isLastAttempt" => $attemptsInfo->isLastAttempt,
  388.             "questions" => $questions,
  389.         ];
  391.         return $data;
  392.     }
  394.     public function getProgress(ExamUser $examUser)
  395.     {   
  396.         $user = $examUser->getUser();
  397.         $exam = $examUser->getExam();
  399.         $progress = 100;
  400.         if(
  401.             $examUser->getStatus() == ExamUserEnum::IN_PROGRESS || 
  402.             $examUser->getStatus() == ExamUserEnum::TIMEOUT
  403.         ){
  404.             $examUserAnswerRepository = $this->em->getRepository(ExamUserAnswer::class);
  405.             $questionAnswerNumber = $examUserAnswerRepository->countAsnweredByExamUser(
  406.                 $examUser
  407.             );
  409.             $questionNumber = $exam->getQuestionNumber();
  410.             if(empty($exam->getQuestionNumber())){
  411.                 $questionNumber = 1;
  412.             }
  414.             $progress = round($questionAnswerNumber * 100 / $questionNumber);
  415.             $progress = ($progress >= 100 ? 100 : $progress);
  416.         }
  418.         return $progress;
  419.     }
  421.     public function getAttemptsInfo(Exam $exam, ?ExamUser $examUser = null)
  422.     {
  423.         $isLastAttempt = ExamEnum::YES;
  424.         $attempts = $exam->getAttempts();
  425.         $hasAttempts = $exam->getAttempts();
  426.         $attemptDateRelease = null;
  427.         
  428.         $dateTimeUtil = $this->generalService->getUtil('DateTimeUtil');
  430.         if($examUser){
  431.             $user = $examUser->getUser();
  433.             if(
  434.                 $attempts == ExamEnum::YES && 
  435.                 $examUser->getStatus() != ExamUserEnum::APPROVED
  436.             ){
  437.                 $attemptsUserNumber = $this->count([
  438.                     "deleted" => ExamUserEnum::ITEM_NO_DELETED,
  439.                     "user" => $user->getId(),
  440.                     "exam" => $exam->getId(),
  441.                 ]);
  443.                 if($attemptsUserNumber >= $exam->getAttemptsNumber()){
  444.                     $attempts = ExamEnum::NO;
  445.                 }else{
  446.                     $isLastAttempt = ExamEnum::NO;
  447.                     
  448.                     if($exam->getAttemptsAutoRelease() == ExamEnum::NO){
  449.                         $typeRelease = $exam->getAttemptsTypeRelease();
  450.                         $dateEnd = $examUser->getDateEnd();
  452.                         $examTimeTry = 0;
  453.                         if($typeRelease == ExamEnum::DAYS){
  454.                             $examTimeTry = strtotime(
  455.                                 "{$dateEnd} + {$exam->getAttemptsPeriod()} day"
  456.                             );
  457.                         }else if($typeRelease == ExamEnum::HOURS){
  458.                             $attemptTimeSeconds = $dateTimeUtil->timeToSec(
  459.                                 $exam->getAttemptsTime()
  460.                             );
  461.                             $examTimeTry = strtotime($dateEnd) + $attemptTimeSeconds;
  462.                         }
  464.                         $todaySeconds = strtotime(date('Y-m-d H:i:s'));
  465.                         
  466.                         if($examTimeTry > $todaySeconds){
  467.                             $attempts = ExamEnum::NO;
  468.                             $attemptDateRelease = date('Y-m-d H:i:s', $examTimeTry);
  469.                         }
  470.                     }
  471.                 }
  472.             }else{
  473.                 $attempts = ExamEnum::NO;
  474.             }
  475.         }
  477.         return (object)[
  478.             "isLastAttempt" => $isLastAttempt,
  479.             "attempts" => $attempts,
  480.             "hasAttempts" => $hasAttempts,
  481.             "attemptDateRelease" => $attemptDateRelease,
  482.         ];
  483.     }
  485.     public function countValidExamUserByCourse(int $courseId, int $userId)
  486.     {
  487.         $query = $this->createQueryBuilder('eu');
  489.         $query->innerJoin('EADPlataforma:Exam', 'e', 'WITH', 'e.id = eu.exam');
  490.         $query->innerJoin('EADPlataforma:Course', 'c', 'WITH', 'c.id = e.course');
  491.         $query->leftJoin('EADPlataforma:LessonModule', 'lm', 'WITH', 'lm.id = e.lessonModule');
  492.         $query->leftJoin('EADPlataforma:Lesson', 'l', 'WITH', 'l.id = e.lesson');
  494.         $query->select('COUNT(e.id) AS total');
  496.         $query->andWhere('eu.deleted = :examUserDeleted');
  497.         $query->andWhere('e.deleted = :examDeleted');
  498.         $query->andWhere('e.status = :examStatus');
  500.         $query->andWhere('eu.releaseAccess = :yes');
  501.         $query->andWhere('eu.inactive = :no');
  503.         $query->andWhere('e.examWeight > 0');
  504.         //$query->andWhere('e.requirement = :yes');
  506.         $query->andWhere('e.course = :course');
  507.         $query->andWhere('eu.user = :user');
  509.         $query->andWhere('
  510.             (
  511.                 (e.type = :typeCourse AND c.deleted = :courseDeleted)
  512.                 OR (
  513.                     e.type = :typeLessonModule
  514.                     AND lm.status = :lessonModuleStatus 
  515.                     AND lm.deleted = :lessonModuleDeleted
  516.                 ) 
  517.                 OR (
  518.                     e.type = :typeLesson 
  519.                     AND l.status = :lessonStatus
  520.                     AND lm.status = :lessonModuleStatus
  521.                     AND l.deleted = :lessonDeleted
  522.                 )
  523.             )
  524.         ');
  526.         $query->setParameter('typeCourse', ExamEnum::COURSE);
  527.         $query->setParameter('typeLessonModule', ExamEnum::MODULE);
  528.         $query->setParameter('typeLesson', ExamEnum::LESSON);
  530.         $query->setParameter('examDeleted', ExamEnum::ITEM_NO_DELETED);
  531.         $query->setParameter('examUserDeleted', ExamUserEnum::ITEM_NO_DELETED);
  532.         $query->setParameter('courseDeleted', CourseEnum::ITEM_NO_DELETED);
  533.         $query->setParameter('lessonModuleDeleted', LessonModuleEnum::ITEM_NO_DELETED);
  534.         $query->setParameter('lessonDeleted', LessonEnum::ITEM_NO_DELETED);
  535.         
  536.         $query->setParameter('lessonModuleStatus', LessonModuleEnum::PUBLISHED);
  537.         $query->setParameter('lessonStatus', LessonEnum::PUBLISHED);
  538.         $query->setParameter('examStatus', ExamEnum::PUBLISHED);
  539.         $query->setParameter('yes', ExamEnum::YES);
  540.         $query->setParameter('no', ExamEnum::NO);
  541.         $query->setParameter('course', $courseId);
  542.         $query->setParameter('user', $userId);
  543.         
  544.         $result = (object)$query->getQuery()->getOneOrNullResult();
  546.         return $result->total;
  547.     }
  549.     public function countExamUserStatus(?int $status = null)
  550.     {
  551.         $query = $this->createQueryBuilder('eu');
  552.         $query->select('COUNT(eu.id) AS total');
  554.         $query->andWhere('eu.deleted = :deleted');
  555.         $query->setParameter('deleted', ExamUserEnum::ITEM_NO_DELETED);
  557.         if(!is_null($status)){
  558.             $query->andWhere('eu.status = :status');
  559.             $query->setParameter('status', $status);
  560.         }
  562.         $result = (object)$query->getQuery()->getOneOrNullResult();
  563.         return $result->total;
  564.     }
  566.     public function getCurrentStartedExamUser(Course $course, User $user){
  567.         $q = $this->createQueryBuilder('eu');
  568.         $q->innerJoin(
  569.             'EADPlataforma:Exam', 
  570.             'e', 
  571.             'WITH', 
  572.             'e.id = eu.exam AND e.deleted = :examDeleted'
  573.         );
  574.         
  575.         $q->leftJoin(
  576.             'EADPlataforma:LessonModule', 
  577.             'lm', 
  578.             'WITH', 
  579.             'lm.id = e.lessonModule'
  580.         );
  582.         $q->leftJoin(
  583.             'EADPlataforma:Lesson', 
  584.             'l', 
  585.             'WITH', 
  586.             'l.id = e.lesson'
  587.         );
  589.         $q->andWhere('eu.deleted = :deleted');
  590.         $q->andWhere('eu.user = :user');
  591.         $q->andWhere('eu.course = :course');
  592.         $q->andWhere('eu.status != :statusExamUser');
  593.         $q->andWhere('eu.inactive = :inactive');
  594.         $q->andWhere('eu.releaseAccess = :releaseAccess');
  595.         $q->andWhere('e.requirement = :requirement');
  596.         $q->andWhere('e.status = :statusExam');
  597.         
  598.         $q->andWhere('(
  599.             (
  600.                 e.type = :typeLesson 
  601.                 AND l.deleted = :lessonDeleted 
  602.                 AND l.status = :statusL 
  603.                 AND lm.status = :statusLM
  604.             )
  605.             OR 
  606.             (
  607.                 e.type = :typeModule 
  608.                 AND lm.deleted = :lessonModuleDeleted 
  609.                 AND lm.status = :statusLM
  610.             )
  611.         )');
  613.         $q->setParameter('examDeleted', ExamEnum::ITEM_NO_DELETED);
  614.         $q->setParameter('deleted', ExamUserEnum::ITEM_NO_DELETED);
  615.         $q->setParameter('lessonDeleted', LessonEnum::ITEM_NO_DELETED);
  616.         $q->setParameter('lessonModuleDeleted', LessonModuleEnum::ITEM_NO_DELETED);
  618.         $q->setParameter('statusLM', LessonModuleEnum::PUBLISHED);
  619.         $q->setParameter('statusL', LessonEnum::PUBLISHED);
  620.         $q->setParameter('user', $user->getId());
  621.         $q->setParameter('course', $course->getId());
  622.         $q->setParameter('statusExamUser', ExamUserEnum::APPROVED);
  623.         $q->setParameter('inactive', ExamUserEnum::NO);
  624.         $q->setParameter('releaseAccess', ExamUserEnum::NO);
  625.         $q->setParameter('requirement', ExamEnum::YES);
  626.         $q->setParameter('statusExam', ExamEnum::PUBLISHED);
  627.         $q->setParameter('typeModule', ExamEnum::MODULE);
  628.         $q->setParameter('typeLesson', ExamEnum::LESSON);
  630.         $q->setMaxResults(1);
  632.         return $q->getQuery()->getOneOrNullResult();
  633.     }
  635.     public function getCurrentExamOnCourseToUserByLesson(Lesson $lessonBase, User $user)
  636.     {
  637.         $course = $lessonBase->getCourse();
  638.         $lessonModule = $lessonBase->getLessonModule();
  640.         $examUser = $this->getCurrentStartedExamUser($course, $user);
  641.         if($examUser){
  642.             return $examUser->getExam();
  643.         }
  645.         $examRepository = $this->em->getRepository(Exam::class);
  646.         $exam = $examRepository->getCurrentNotStartedExamUser($lessonBase, $user);
  647.         if($exam){
  648.             return $exam;
  649.         }
  651.         return;
  652.     }
  654.     public function examUserIsExpired(ExamUser $examUser)
  655.     {
  656.         if($examUser->getStatus() != ExamUserEnum::IN_PROGRESS){
  657.             return true;
  658.         }
  660.         //valid exam type
  661.         $dateTimeUtil = $this->generalService->getUtil('DateTimeUtil');
  662.         $dateStartSeconds = strtotime($examUser->getDateStart());
  663.         $exam = $examUser->getExam();
  664.         $course = $exam->getCourse();
  666.         $timeSeconds = $dateTimeUtil->timeToSec($exam->getExamTime());
  667.         $todaySeconds = strtotime(date('Y-m-d H:i:s'));
  668.         $dateExamEndSecods = $dateStartSeconds + $timeSeconds;
  670.         if($todaySeconds >= $dateExamEndSecods){
  672.             $examUser->setStatus(ExamUserEnum::TIMEOUT);
  673.             $examUser->setGrade(0);
  674.             $examUser->setDateEnd(date("Y-m-d H:i:s", $dateExamEndSecods));
  676.             //notify
  677.             $notificationService = $this->generalService->getService('NotificationService');
  678.             $notificationService->create(
  679.                 $examUser->getUser(), 
  680.                 $course->getUser(),
  681.                 NotificationEnum::ORIGIN_EXAM_USER_TIMEOUT, 
  682.                 $examUser->getId()
  683.             );
  685.             $notificationService->create(
  686.                 $course->getUser(), 
  687.                 $examUser->getUser(),
  688.                 NotificationEnum::ORIGIN_EXAM_USER_TIMEOUT, 
  689.                 $examUser->getId()
  690.             );
  692.             $this->em->flush();
  694.             $data = $examUser->toReturn();
  696.             $userLogService = $this->generalService->getService('LogService');
  697.             $userLogService->logUpdate("exam_user", $examUser->getId(), $data);
  698.             return true;
  699.         }
  701.         return false;
  702.     }
  704.     public function getValidExamUserById(?int $examUserId = null, 
  705.                                          ?bool $onlyUserInExam = false, 
  706.                                          ?ExamUser $examUser = null)
  707.     {
  708.         if(!$examUser){
  709.             $examUser = $this->findOneBy([
  710.                 "id" => $examUserId,
  711.                 "deleted" => ExamUserEnum::ITEM_NO_DELETED
  712.             ]);
  713.         }
  715.         if(!$examUser) {
  716.             return;
  717.         }
  719.         if($onlyUserInExam){
  720.             if($this->getUser()->getId() != $examUser->getUser()->getId()){
  721.                 return;
  722.             }
  723.         }
  725.         if($examUser->getInactive() == ExamUserEnum::YES){
  726.             return;
  727.         }
  729.         $exam = $examUser->getExam();
  731.         if($examUser->getCourse()->isDeleted()) {
  732.             return;
  733.         }
  735.         if($exam->getLessonModule()){
  736.             if($exam->getLessonModule()->isDeleted()) {
  737.                 return;
  738.             }
  739.         }
  741.         if($exam->getLesson()){
  742.             if($exam->getLesson()->isDeleted()) {
  743.                 return;
  744.             }
  745.         }
  747.         return $examUser;
  748.     }
  750.     public function updateExamUser(ExamUser $examUser, ?bool $ignoreTemplates = false)
  751.     {
  752.         $grade = $this->getGrade($examUser);
  753.         $exam = $examUser->getExam();
  755.         $examUser->setGrade($grade);
  757.         if(empty($examUser->getDateEnd())){
  758.             $examUser->setDateEnd(date("Y-m-d H:i:s"));
  759.         }
  761.         $examUserAnswerRepository = $this->em->getRepository(ExamUserAnswer::class);
  762.         $countQuestionNotEvalueted = $examUserAnswerRepository->countQuestionNotEvalueted($examUser);
  763.        
  764.         $examUser->setStatus(ExamUserEnum::WAITING_CORRECTION);
  766.         if(empty($countQuestionNotEvalueted) || $ignoreTemplates){
  767.             if($grade >= $exam->getExamAverage()){
  768.                 $examUser->setReleaseAccess(ExamUserEnum::YES);
  769.                 $examUser->setStatus(ExamUserEnum::APPROVED);
  770.             }else{
  771.                 $examUser->setStatus(ExamUserEnum::DISAPPROVED);
  772.             }
  773.         }
  775.         $this->em->flush();
  777.         $this->processExamUser($examUser);
  778.     }
  780.     public function processExamUser(ExamUser $examUser)
  781.     {   
  782.         $course = $examUser->getCourse();
  783.         $user = $examUser->getUser();
  785.         $enrollment = $this->em->getRepository(Enrollment::class)->findOneBy([
  786.             "deleted" => ExamUserEnum::ITEM_NO_DELETED,
  787.             "user" => $user->getId(),
  788.             "course" => $course->getId(),
  789.         ]);
  791.         if(!$enrollment){
  792.             return;
  793.         }
  794.        
  795.         //notify
  796.         $notificationService = $this->generalService->getService('NotificationService');   
  798.         $exam = $examUser->getExam();
  799.         
  800.         if($exam->getType() == ExamEnum::COURSE){
  801.             $finalAverage = $this->getFinalAvarege($user->getId(), $course->getId());
  802.             $enrollment->setFinalAverage($finalAverage);
  804.             if($this->configuration->isModuleActive('course_certificate_module')){
  805.                 if($enrollment->getCertificate() == ExamEnum::YES){
  806.                     $certificateAverage = $course->getCertificateAverage();
  807.                     $courseCertificateTemplate = $course->getCourseCertificateTemplate();
  809.                     if(!empty($finalAverage)){
  810.                         if($finalAverage >= $certificateAverage){
  811.                             //issue certificate
  812.                             $courseCertificateRepository = $this->em->getRepository(
  813.                                 CourseCertificate::class
  814.                             );
  815.                             
  816.                             $courseCertificateRepository->createCertificate(
  817.                                 $enrollment,
  818.                                 (
  819.                                     $courseCertificateTemplate ? 
  820.                                     $courseCertificateTemplate : 
  821.                                     $enrollment->getCourseCertificateTemplate()
  822.                                 ),
  823.                                 $finalAverage
  824.                             );
  825.                         }
  826.                     }
  827.                 }
  828.             }
  830.             if($examUser->getStatus() == ExamUserEnum::APPROVED){
  831.                 $marketingService = $this->generalService->getService(
  832.                     'Marketing\\MarketingService'
  833.                 );
  835.                 $marketingService->setTag(TagsMarketingEnum::TAG_FINISH_COURSE);
  836.                 $marketingService->setTextComplement($exam->getCourse()->getTitle());
  837.                 $marketingService->setUser($examUser->getUser());
  838.                 $marketingService->send();
  839.             }
  840.         }
  842.         if($examUser->getStatus() == ExamUserEnum::APPROVED){
  843.             $notificationService->create(
  844.                 $course->getUser(),
  845.                 $user,
  846.                 NotificationEnum::ORIGIN_EXAM_USER_APPROVED,
  847.                 $examUser->getId()
  848.             );
  850.             $notificationService->create(
  851.                 $user, 
  852.                 $course->getUser(),
  853.                 NotificationEnum::ORIGIN_EXAM_USER_APPROVED,
  854.                 $examUser->getId()
  855.             );
  856.         }else if($examUser->getStatus() == ExamUserEnum::DISAPPROVED){
  857.             $notificationService->create(
  858.                 $course->getUser(),
  859.                 $user,
  860.                 NotificationEnum::ORIGIN_EXAM_USER_DISAPPROVED,
  861.                 $examUser->getId()
  862.             );
  863.             $notificationService->create(
  864.                 $user,
  865.                 $course->getUser(),
  866.                 NotificationEnum::ORIGIN_EXAM_USER_DISAPPROVED,
  867.                 $examUser->getId()
  868.             );
  869.         }else if($examUser->getStatus() == ExamUserEnum::WAITING_CORRECTION){
  870.             $notificationService->create(
  871.                 $user,
  872.                 $course->getUser(),
  873.                 NotificationEnum::ORIGIN_EXAM_USER_WAITING_CORRECTION,
  874.                 $examUser->getId()
  875.             );
  876.         }
  877.     }
  879.     public function export($courseId = null, $userId = null, $groupId = null, 
  880.                            $teacherId = null, $status = null, $dateStart = null, 
  881.                            $dateEnd = null)
  882.     {
  883.         $query = $this->createQueryBuilder('eu');
  884.         $query->innerJoin('EADPlataforma:Exam', 'e', 'WITH', 'e.id = eu.exam AND e.deleted = 0');
  885.         $query->innerJoin('EADPlataforma:User', 'u', 'WITH', 'u.id = eu.user AND u.deleted = 0');
  886.         $query->innerJoin('EADPlataforma:Course', 'c', 'WITH', 'c.id = eu.course AND c.deleted = 0');
  887.         $query->leftJoin('EADPlataforma:LessonModule', 'lm', 'WITH', 'lm.id = e.lessonModule AND lm.deleted = 0');
  888.         $query->leftJoin('EADPlataforma:Lesson', 'l', 'WITH', 'l.id = e.lesson AND l.deleted = 0');
  890.         $query->andWhere('eu.deleted = :examUserDeleted');
  891.         $query->setParameter('examUserDeleted', ExamUserEnum::ITEM_NO_DELETED);
  893.         $query->andWhere('u.status != :userStatus');
  894.         $query->setParameter('userStatus', UserEnum::INACTIVE);
  896.         if(!is_null($dateStart) && !is_null($dateEnd)){
  897.             $query->andWhere('DATE(eu.dateStart) BETWEEN :dateStart AND :dateEnd');
  898.             $query->setParameter('dateStart', $dateStart);
  899.             $query->setParameter('dateEnd', $dateEnd);
  900.         }
  901.         
  902.         if($courseId > 0){
  903.             $query->andWhere('eu.course = :course')->setParameter('course', $courseId);
  904.         }
  906.         if($userId > 0){
  907.             $query->andWhere('eu.user = :user')->setParameter('user', $userId);
  908.         }
  910.         if($teacherId > 0){
  911.             $query->andWhere('( 
  912.                 c.user = :teacherId 
  913.                 OR 
  914.                 ( lm.id > 0 AND lm.user = :teacherId )
  915.                 OR
  916.                 ( l.id > 0 AND l.user = :teacherId ) 
  917.             )')->setParameter('teacherId', $teacherId);
  918.         }
  919.         
  920.         if($groupId > 0){
  921.             $query->innerJoin('EADPlataforma:Group', 'gu', 'WITH', 'u MEMBER OF gu.user');
  922.             $query->andWhere('gu.id = :groupId')->setParameter('groupId', $groupId);
  923.         }
  925.         if(!is_null($status)){
  926.             $query->andWhere('eu.status = :status')->setParameter('status', $status);
  927.         }
  929.         $query->addOrderBy('eu.course', 'ASC');
  930.         $query->addOrderBy('eu.user', 'ASC');
  931.         $query->addOrderBy('eu.id', 'ASC');
  932.         return $query->getQuery()->execute();
  933.     }
  935.     public function getGrade(ExamUser $examUser)
  936.     {
  937.         $courseRepository = $this->em->getRepository(Course::class);
  938.         $isStudent = $courseRepository->isStudent($examUser->getCourse());
  940.         $query = $this->em->createQueryBuilder();
  941.         $query->from('EADPlataforma:ExamUserAnswer', 'eua');
  942.         $exam = $examUser->getExam();
  944.         $questionNumber = $exam->getQuestionNumberReal();
  945.         $query->select("ROUND((ROUND(SUM(eua.grade), 2) * 10) / {$questionNumber}, 2) AS finalGrade");
  946.         
  947.         $query->innerJoin(
  948.             'EADPlataforma:ExamUser', 
  949.             'eu', 
  950.             'WITH', 
  951.             'eu.id = eua.examUser AND eu.deleted = 0'
  952.         );
  954.         $query->innerJoin('EADPlataforma:Exam', 'e', 'WITH', 'e.id = eu.exam AND e.deleted = 0');
  955.         $query->innerJoin('EADPlataforma:Course', 'c', 'WITH', 'c.id = e.course');
  956.         $query->leftJoin('EADPlataforma:LessonModule', 'lm', 'WITH', 'lm.id = e.lessonModule');
  957.         $query->leftJoin('EADPlataforma:Lesson', 'l', 'WITH', 'l.id = e.lesson');
  959.         $statusModule = "";
  960.         $statusLesson = "";
  962.         if($isStudent){
  963.             $statusModule .= "AND lm.status = :moduleStatus ";
  964.             $statusLesson .= "AND l.status = :lessonStatus ";
  966.             $query->setParameter('moduleStatus', ExamEnum::PUBLISHED);
  967.             $query->setParameter('lessonStatus', ExamEnum::PUBLISHED);
  968.         }
  970.         $query->andWhere("(
  971.             ( 
  972.                 e.type = :examModuleType 
  973.                 {$statusModule}
  974.             ) 
  975.             OR ( 
  976.                 e.type = :examLessonType 
  977.                 {$statusLesson}
  978.                 {$statusModule}
  979.             )
  980.             OR ( e.type = :examCourseType )
  981.          )");
  982.         
  983.         $query->setParameter('examModuleType', ExamEnum::MODULE);
  984.         $query->setParameter('examLessonType', ExamEnum::LESSON);
  985.         $query->setParameter('examCourseType', ExamEnum::COURSE);
  986.         
  987.         $query->andWhere('eua.deleted = 0');
  988.         $query->andWhere('eua.examUser = :examUser')->setParameter('examUser', $examUser->getId());
  990.         $result = (object)$query->getQuery()->getOneOrNullResult();
  991.         return $result->finalGrade;
  992.     }
  994.     public function getFinalAvarege($userId, $courseId)
  995.     {
  996.         $query = $this->em->createQueryBuilder();
  997.         $query->from('EADPlataforma:Exam', 'e');
  999.         $query->innerJoin('EADPlataforma:Course', 'c', 'WITH', 'c.id = e.course');
  1000.         $query->leftJoin('EADPlataforma:LessonModule', 'lm', 'WITH', 'lm.id = e.lessonModule');
  1001.         $query->leftJoin('EADPlataforma:Lesson', 'l', 'WITH', 'l.id = e.lesson');
  1003.         $query->leftJoin(
  1004.             'EADPlataforma:ExamUser', 
  1005.             'eu', 
  1006.             'WITH', 
  1007.             '
  1008.                 eu.exam = e.id 
  1009.                 AND eu.status IN (:statusApproved, :statusDisapproved) 
  1010.                 AND eu.user = :user
  1011.                 AND eu.inactive = :incative
  1012.                 AND eu.deleted = 0
  1013.             '
  1014.         );
  1016.         $query->select('
  1017.             ROUND(
  1018.                 SUM(
  1019.                     e.examWeight * (
  1020.                         CASE 
  1021.                             WHEN eu.grade IS NOT NULL 
  1022.                             THEN eu.grade 
  1023.                             ELSE 0 
  1024.                         END 
  1025.                     )
  1026.                 ) / SUM(e.examWeight)
  1027.             , 2) AS finalAverage
  1028.         ');
  1030.         $query->andWhere('(
  1031.             ( 
  1032.                 e.type = :examModuleType 
  1033.                 AND lm.status = :moduleStatus 
  1034.             ) 
  1035.             OR ( 
  1036.                 e.type = :examLessonType 
  1037.                 AND l.status = :lessonStatus 
  1038.                 AND lm.status = :moduleStatus 
  1039.             )
  1040.             OR ( e.type = :examCourseType )
  1041.          )');
  1043.         $query->setParameter('examModuleType', ExamEnum::MODULE);
  1044.         $query->setParameter('examLessonType', ExamEnum::LESSON);
  1045.         $query->setParameter('examCourseType', ExamEnum::COURSE);
  1046.         $query->setParameter('moduleStatus', ExamEnum::PUBLISHED);
  1047.         $query->setParameter('lessonStatus', ExamEnum::PUBLISHED);
  1049.         $query->setParameter('statusApproved', ExamUserEnum::APPROVED);
  1050.         $query->setParameter('statusDisapproved', ExamUserEnum::DISAPPROVED);
  1051.         $query->setParameter('incative', ExamUserEnum::NO);
  1052.         $query->setParameter('user', $userId);
  1054.         //$query->andWhere('e.requirement = :requirement');
  1055.         //$query->setParameter('requirement', ExamEnum::YES);
  1057.         $query->andWhere('e.deleted = 0');
  1058.         $query->andWhere('e.status = :status')->setParameter('status', ExamEnum::PUBLISHED);
  1059.         $query->andWhere('e.course = :course')->setParameter('course', $courseId);
  1060.         $query->andWhere('e.examWeight > 0');
  1061.         $result = (object)$query->getQuery()->getOneOrNullResult();
  1062.         return $result->finalAverage;
  1063.     }
  1065.     public function getStudentReportCard($userId, $courseId)
  1066.     {
  1067.         $examWeight = (float)$this->em->getRepository(Exam::class)->sumCourseWeight($courseId);
  1069.         $query = $this->em->createQueryBuilder();
  1070.         $query->from('EADPlataforma:Exam', 'e');
  1071.         $query->innerJoin('EADPlataforma:Course', 'c', 'WITH', 'c.id = e.course');
  1072.         $query->leftJoin('EADPlataforma:LessonModule', 'lm', 'WITH', 'lm.id = e.lessonModule');
  1073.         $query->leftJoin('EADPlataforma:Lesson', 'l', 'WITH', 'l.id = e.lesson');
  1075.         $query->leftJoin(
  1076.             'EADPlataforma:ExamUser', 
  1077.             'eu', 
  1078.             'WITH', 
  1079.             '
  1080.                 eu.exam = e.id 
  1081.                 AND eu.status IN (:statusApproved, :statusDisapproved) 
  1082.                 AND eu.user = :user
  1083.                 AND eu.inactive = :incative
  1084.                 AND eu.deleted = 0
  1085.             '
  1086.         );
  1088.         $query->select("
  1089.             e.id,
  1090.             c.id AS courseId,
  1091.             lm.id AS moduleId,
  1092.             l.id AS lessonId,
  1093.             e.title AS examTitle,
  1094.             e.type as examType,
  1095.             lm.title AS moduleTitle,
  1096.             l.title AS lessonTitle,
  1097.             ROUND(eu.grade, 2) AS grade,
  1098.             e.examWeight,
  1099.             ROUND((eu.grade * (e.examWeight/:examWeight)), 2) AS parcial,
  1100.             DATE_FORMAT(eu.dateEnd, '%Y-%m-%d %H:%i:%s') AS dateEnd
  1101.         ");
  1103.         //$query->andWhere('e.requirement = :requirement');
  1104.         $query->andWhere('e.course = :course');
  1105.         $query->andWhere('e.deleted = 0');
  1107.         $query->andWhere('e.examWeight > 0');
  1109.         $query->andWhere('(
  1110.             ( 
  1111.                 e.type = :examModuleType 
  1112.                 AND lm.status = :moduleStatus 
  1113.             ) 
  1114.             OR ( 
  1115.                 e.type = :examLessonType 
  1116.                 AND l.status = :lessonStatus 
  1117.                 AND lm.status = :moduleStatus 
  1118.             )
  1119.             OR ( e.type = :examCourseType )
  1120.          )');
  1122.         $query->andWhere('( e.status = :status OR eu.id > 0 )');
  1124.         $query->setParameter('examWeight', $examWeight);
  1125.         $query->setParameter('examModuleType', ExamEnum::MODULE);
  1126.         $query->setParameter('examLessonType', ExamEnum::LESSON);
  1127.         $query->setParameter('examCourseType', ExamEnum::COURSE);
  1128.         $query->setParameter('moduleStatus', ExamEnum::PUBLISHED);
  1129.         $query->setParameter('lessonStatus', ExamEnum::PUBLISHED);
  1131.         $query->setParameter('status', ExamEnum::PUBLISHED);
  1132.         //$query->setParameter('requirement', ExamUserEnum::YES);
  1133.         $query->setParameter('statusApproved', ExamUserEnum::APPROVED);
  1134.         $query->setParameter('statusDisapproved', ExamUserEnum::DISAPPROVED);
  1135.         $query->setParameter('incative', ExamUserEnum::NO);
  1136.         $query->setParameter('user', $userId);
  1137.         $query->setParameter('course', $courseId);
  1138.         
  1139.         $query->addOrderBy('c.order', 'ASC');
  1140.         $query->addOrderBy('lm.order', 'ASC');
  1141.         $query->addOrderBy('l.order', 'ASC');
  1142.         $query->addOrderBy('e.type', 'DESC');
  1143.         
  1144.         return $query->getQuery()->execute();
  1145.     }
  1147.     public function getStudentReportCardApi($userId, $courseId)
  1148.     {
  1149.         $examWeight = (int)$this->em->getRepository(Exam::class)->sumCourseWeight($courseId);
  1150.         
  1151.         $examApproved = ExamUserEnum::APPROVED;
  1152.         $examDisapproved = ExamUserEnum::DISAPPROVED;
  1154.         $examModule = ExamEnum::MODULE;
  1155.         $examLesson = ExamEnum::LESSON;
  1156.         $examCourse = ExamEnum::COURSE;
  1158.         $dql = "SELECT
  1159.                     eu.id AS prova_id,
  1160.                     pc.title AS titulo,
  1161.                     pc.type AS tipo,
  1162.                     lm.id AS modulo_id,
  1163.                     lm.title AS modulo,
  1164.                     l.id AS aula_id,
  1165.                     l.title AS aula,
  1166.                     ROUND(eu.grade, 2) AS nota_final,
  1167.                     pc.examWeight AS pontos,
  1168.                     ROUND((eu.grade * (pc.examWeight/:examWeight)), 2) AS parcial
  1169.                 FROM EADPlataforma:Exam AS pc
  1170.                 INNER JOIN EADPlataforma:Course AS c WITH (c.id = pc.course)
  1171.                 LEFT JOIN EADPlataforma:LessonModule AS lm WITH (lm.id = pc.lessonModule)
  1172.                 LEFT JOIN EADPlataforma:Lesson AS l WITH (l.id = pc.lesson)
  1173.                 LEFT JOIN EADPlataforma:ExamUser AS eu WITH (
  1174.                                         eu.exam = pc.id 
  1175.                                         AND (
  1176.                                                 eu.status = {$examApproved}
  1177.                                                 OR eu.status = {$examDisapproved}
  1178.                                             )
  1179.                                         AND eu.user = :user 
  1180.                                         AND eu.inactive = 0
  1181.                                     )
  1182.                 WHERE pc.status = 1
  1183.                 AND pc.examWeight > 0
  1184.                 AND (
  1185.                         ( pc.type = {$examModule} AND lm.status = 1 ) 
  1186.                         OR ( pc.type = {$examLesson} AND l.status = 1 AND lm.status = 1 )
  1187.                         OR ( pc.type = {$examCourse} )
  1188.                     )
  1189.                 AND ( 
  1190.                         ( pc.status = 1 ) 
  1191.                         OR 
  1192.                         (
  1193.                             pc.id IN (
  1194.                                 SELECT 
  1195.                                     p.id 
  1196.                                 FROM EADPlataforma:ExamUser AS p
  1197.                                 WHERE p.user = :user 
  1198.                                 AND p.inactive = 0
  1199.                                 AND p.deleted = 0
  1200.                             )
  1201.                         ) 
  1202.                     )
  1203.                 AND pc.course = :course
  1204.                 AND eu.deleted = 0
  1205.                 AND pc.deleted = 0
  1206.                 ORDER BY c.order ASC, lm.order ASC, l.order ASC, pc.type DESC ";
  1207.         
  1208.         $query = $this->em->createQuery($dql);
  1209.         $query->setParameter('course', $courseId);
  1210.         $query->setParameter('user', $userId);
  1211.         $query->setParameter('examWeight', $examWeight);
  1213.         return $query->execute();
  1214.     }
  1216.     public function getStudentReportCardExport($courseId = null, $userId = null, $groupId = null, 
  1217.                                                 $teacherId = null, $dateStart = null, $dateEnd = null)
  1218.     {
  1219.         $examModuleType = ExamEnum::MODULE;
  1220.         $examLessonType = ExamEnum::LESSON;
  1221.         $examCourseType = ExamEnum::COURSE;
  1222.         $moduleStatus = ExamEnum::PUBLISHED;
  1223.         $lessonStatus = ExamEnum::PUBLISHED;
  1224.         $status = ExamEnum::PUBLISHED;
  1225.         $statusApproved = ExamUserEnum::APPROVED;
  1226.         $statusDisapproved = ExamUserEnum::DISAPPROVED;
  1227.         $incative = ExamEnum::NO;
  1229.         $filterCourse = "";
  1230.         if($courseId > 0){
  1231.             $filterCourse .= "AND e.course_id = {$courseId} ";
  1232.         }
  1234.         $filterUser = "";
  1235.         if($userId > 0){
  1236.             $filterUser .= "AND eu.user_id = {$userId} ";
  1237.         }
  1239.         $filterGroup = "";
  1240.         if($groupId > 0){
  1241.             $filterGroup .= "
  1242.                 INNER JOIN group_x_user AS gu
  1243.                 ON (
  1244.                     u.id = gu.user_id AND 
  1245.                     gu.group_id = {$groupId}
  1246.                 )";
  1247.         }
  1249.         $filterTeacher = "";
  1250.         if($teacherId > 0){
  1251.             $filterTeacher .= "AND c.user_id = {$teacherId}
  1252.                                 OR 
  1253.                                 ( lm.id > 0 AND lm.user_id = {$teacherId} )
  1254.                                 OR
  1255.                                 ( l.id > 0 AND l.user_id = {$teacherId} ) ";
  1256.         }
  1258.         $filterPeriod = "";
  1259.         if(!is_null($dateStart) && !is_null($dateEnd)){
  1260.             $filterPeriod .= "AND (
  1261.                 DATE(eu.date_start) BETWEEN '{$dateStart}' AND '{$dateEnd}'
  1262.             ) ";
  1263.         }
  1265.         $sqlExamWeight = "(
  1266.             SELECT
  1267.                 SUM(ex.exam_weight)
  1268.             FROM exam AS ex
  1269.             WHERE ex.status = 1 
  1270.             AND ex.requirement = 1
  1271.             AND ex.deleted = 0
  1272.             AND ex.course_id = e.course_id
  1273.             LIMIT 1
  1274.         )";
  1276.         $sqlDefault = "SELECT 
  1277.                             u.id AS userId,
  1278.                             u.name AS userName,
  1279.                             u.email AS userEmail,
  1280.                             c.id AS courseId,
  1281.                             c.title AS courseTitle,
  1282.                             c.certificate_average AS mediaCourse,
  1283.                             e.title AS examTitle,
  1284.                             e.type as examType,
  1285.                             DATE_FORMAT(eu.date_start, '%Y-%m-%d %H:%i:%s') AS dateStart, 
  1286.                             DATE_FORMAT(eu.date_end, '%Y-%m-%d %H:%i:%s') AS dateEnd, 
  1287.                             ROUND(eu.grade, 2) AS grade,
  1288.                             eu.status AS userExamStatus,
  1289.                             eu.annotation AS userExamAnnotation,
  1290.                             ROUND((eu.grade * (e.exam_weight/{$sqlExamWeight})), 2) AS examPartial
  1291.                         FROM exam AS e
  1292.                         LEFT JOIN lesson_module AS lm 
  1293.                         ON (lm.id = e.lesson_module_id AND lm.deleted = 0)
  1294.                         LEFT JOIN lesson AS l 
  1295.                         ON (l.id = e.lesson_id AND l.deleted = 0)
  1296.                         INNER JOIN course AS c
  1297.                         ON (c.id = e.course_id AND c.deleted = 0 {$filterTeacher})
  1298.                         LEFT JOIN exam_user AS eu 
  1299.                         ON (
  1300.                                 eu.exam_id = e.id AND eu.deleted = 0 AND
  1301.                                 (
  1302.                                     eu.status = {$statusApproved}
  1303.                                     OR eu.status = {$statusDisapproved}
  1304.                                 )
  1305.                                 AND eu.inactive = {$incative} {$filterUser}
  1306.                             )
  1307.                         INNER JOIN user AS u
  1308.                         ON (u.id = eu.user_id AND u.deleted = 0)
  1309.                         {$filterGroup}
  1310.                         WHERE e.deleted = 0 AND e.exam_weight > 0 {$filterCourse}
  1311.                         AND (
  1312.                                 ( 
  1313.                                     e.type = {$examModuleType} 
  1314.                                     AND lm.status = {$moduleStatus} 
  1315.                                 ) 
  1316.                                 OR ( 
  1317.                                     e.type = {$examLessonType} 
  1318.                                     AND l.status = {$lessonStatus}
  1319.                                     AND lm.status = {$moduleStatus} 
  1320.                                 )
  1321.                                 OR ( e.type = {$examCourseType} )
  1322.                             )
  1323.                         AND ( e.status = {$status} OR eu.id > 0 ) {$filterPeriod}
  1324.                         ORDER BY c.order ASC, u.id ASC, lm.order ASC, l.order ASC, e.type DESC";
  1328.         $stmt = $this->em->getConnection()->prepare($sqlDefault);
  1329.         $stmt->execute();
  1330.         $data = $stmt->fetchAll(PDO::FETCH_CLASS);
  1332.         return $data;
  1333.     }
  1335.     public function getStringStatus($status){
  1337.         $string = '';
  1338.         switch ($status) {
  1339.             case ExamUserEnum::IN_PROGRESS:
  1340.                 $string = $this->configuration->getLanguage('in_progress', 'export');
  1341.             break;
  1342.             case ExamUserEnum::WAITING_CORRECTION:
  1343.                 $string = $this->configuration->getLanguage('waiting_correction', 'export');
  1344.             break;
  1345.             case ExamUserEnum::TIMEOUT:
  1346.                 $string = $this->configuration->getLanguage('time_out', 'export');
  1347.             break;
  1348.             case ExamUserEnum::APPROVED:
  1349.                 $string = $this->configuration->getLanguage('approved', 'export');
  1350.             break;
  1351.             case ExamUserEnum::DISAPPROVED:
  1352.                 $string = $this->configuration->getLanguage('disapproved', 'export');
  1353.             break;
  1354.         }
  1356.         return $string;
  1357.     }
  1359.     public function restore(ExamUser $examUser, $typeItem)
  1360.     {
  1361.         $itemId = $examUser->getId();
  1363.         $examUser->setUserDelete($this->getUser());
  1364.         $examUser->setDateDelete(date('Y-m-d H:i:s'));
  1365.         $examUser->restore();
  1367.         $this->em->getRepository(Trash::class)->deleteTrash($itemId, $typeItem);
  1368.     }
  1370.     public function delete(ExamUser $examUser, $typeItem, $permission, $isTrash)
  1371.     {
  1372.         $userPermissionUtil = $this->generalService->getUtil('UserPermissionUtil');
  1374.         if($isTrash == 1){
  1375.             $permission = $userPermissionUtil->getPermission("trash", "delete");
  1376.         }
  1378.         $isExamTeacher = false;
  1379.         if($this->getUser()){
  1380.             $isExamTeacher = $this->em->getRepository(Exam::class)->isExamTeacher(
  1381.                 $examUser->getExam(), 
  1382.                 $this->getUser()
  1383.             );
  1384.         }
  1386.         if($isExamTeacher || $userPermissionUtil->isHigh($permission)){
  1387.             $examUser->setUserDelete($this->getUser());
  1388.             $examUser->setDateDelete(date('Y-m-d H:i:s'));
  1389.             $examUser->individual();
  1390.         
  1391.             if($examUser->isOnTrash() || $examUser->isDeleted()){
  1392.                 $examUser->delete();
  1394.                 $itemId = $examUser->getId();
  1395.                 $this->em->getRepository(Trash::class)->deleteTrash($itemId, $typeItem);
  1396.             }
  1398.             if(!$examUser->isOnTrash() && !$examUser->isDeleted()){
  1399.                 $examUser->trash();
  1400.                 $this->em->getRepository(Trash::class)->insertTrash(
  1401.                     $examUser, 
  1402.                     $typeItem, 
  1403.                     "Aluno: {$examUser->getUser()->getName()} | Exame: {$examUser->getExam()->getTitle()}"
  1404.                 );
  1405.             }
  1406.         }
  1407.         
  1408.         $this->em->flush();
  1409.     }
  1411.     public function restoreByCourse($courseId, $deleted, $typeDelete, $userDelete, $dateNow){
  1412.         $sql = "UPDATE EADPlataforma:ExamUser AS eu 
  1413.                 SET eu.deleted = :deleted, eu.userDelete = :userDelete, 
  1414.                     eu.dateDelete = :dateNow
  1415.                 WHERE eu.deleted = (1 - :deleted)
  1416.                 AND eu.typeDelete = :typeDelete
  1417.                 AND eu.course = :courseId";
  1418.         $query = $this->em->createQuery($sql);
  1419.         $query->setParameter('courseId', $courseId);
  1420.         $query->setParameter('deleted', $deleted);
  1421.         $query->setParameter('typeDelete', $typeDelete);
  1422.         $query->setParameter('userDelete', $userDelete);
  1423.         $query->setParameter('dateNow', $dateNow);
  1424.         $query->execute();
  1425.         
  1426.         return true;
  1427.     }
  1429.     public function deleteByCourse($courseId, $deleted, $typeDelete, $userDelete, $dateNow){
  1430.         $sql = "UPDATE EADPlataforma:ExamUser AS eu 
  1431.                 SET eu.deleted = :deleted, eu.typeDelete = :typeDelete,
  1432.                     eu.userDelete = :userDelete, eu.dateDelete = :dateNow
  1433.                 WHERE eu.deleted = (:deleted - 1)
  1434.                 AND eu.course = :courseId";
  1435.         $query = $this->em->createQuery($sql);
  1436.         $query->setParameter('courseId', $courseId);
  1437.         $query->setParameter('deleted', $deleted);
  1438.         $query->setParameter('typeDelete', $typeDelete);
  1439.         $query->setParameter('userDelete', $userDelete);
  1440.         $query->setParameter('dateNow', $dateNow);
  1441.         $query->execute();
  1442.         
  1443.         return true;
  1444.     }
  1446.     public function restoreByUser($userId, $deleted, $typeDelete, $userDelete, $dateNow){
  1447.         $sql = "UPDATE EADPlataforma:ExamUser AS eu 
  1448.                 SET eu.deleted = :deleted, eu.userDelete = :userDelete, 
  1449.                     eu.dateDelete = :dateNow
  1450.                 WHERE eu.deleted = (1 - :deleted)
  1451.                 AND eu.typeDelete = :typeDelete
  1452.                 AND eu.user = :userId";
  1453.         $query = $this->em->createQuery($sql);
  1454.         $query->setParameter('userId', $userId);
  1455.         $query->setParameter('deleted', $deleted);
  1456.         $query->setParameter('typeDelete', $typeDelete);
  1457.         $query->setParameter('userDelete', $userDelete);
  1458.         $query->setParameter('dateNow', $dateNow);
  1459.         $query->execute();
  1460.         
  1461.         return true;
  1462.     }
  1464.     public function deleteByUser($userId, $deleted, $typeDelete, $userDelete, $dateNow){
  1465.         $sql = "UPDATE EADPlataforma:ExamUser AS eu
  1466.                 SET eu.deleted = :deleted, eu.typeDelete = :typeDelete,
  1467.                     eu.userDelete = :userDelete, eu.dateDelete = :dateNow
  1468.                 WHERE eu.deleted = (:deleted - 1)
  1469.                 AND eu.user = :userId";
  1470.         $query = $this->em->createQuery($sql);
  1471.         $query->setParameter('userId', $userId);
  1472.         $query->setParameter('deleted', $deleted);
  1473.         $query->setParameter('typeDelete', $typeDelete);
  1474.         $query->setParameter('userDelete', $userDelete);
  1475.         $query->setParameter('dateNow', $dateNow);
  1476.         $query->execute();
  1477.         
  1478.         return true;
  1479.     }
  1481.     public function restoreByLessonModule($lessonModuleId, $deleted, $typeDelete, $userDelete, $dateNow){
  1482.         $sql = "UPDATE EADPlataforma:ExamUser AS eu
  1483.                 SET eu.deleted = :deleted, eu.userDelete = :userDelete, 
  1484.                     eu.dateDelete = :dateNow
  1485.                 WHERE IDENTITY(eu.exam) IN (SELECT e.id 
  1486.                                             FROM EADPlataforma:Exam AS e 
  1487.                                             WHERE e.lessonModule = :lessonModuleId )
  1488.                 AND eu.deleted = (1 - :deleted)
  1489.                 AND eu.typeDelete = :typeDelete";
  1490.         $query = $this->em->createQuery($sql);
  1491.         $query->setParameter('lessonModuleId', $lessonModuleId);
  1492.         $query->setParameter('deleted', $deleted);
  1493.         $query->setParameter('typeDelete', $typeDelete);
  1494.         $query->setParameter('userDelete', $userDelete);
  1495.         $query->setParameter('dateNow', $dateNow);
  1496.         $query->execute();
  1497.         
  1498.         return true;
  1499.     }
  1501.     public function deleteByLessonModule($lessonModuleId, $deleted, $typeDelete, $userDelete, $dateNow){
  1502.         $sql = "UPDATE EADPlataforma:ExamUser AS eu
  1503.                 SET eu.deleted = :deleted, eu.typeDelete = :typeDelete,
  1504.                     eu.userDelete = :userDelete, eu.dateDelete = :dateNow
  1505.                 WHERE IDENTITY(eu.exam) IN (SELECT e.id 
  1506.                                             FROM EADPlataforma:Exam AS e 
  1507.                                             WHERE e.lessonModule = :lessonModuleId )
  1508.                 AND eu.deleted = (:deleted - 1)";
  1509.         $query = $this->em->createQuery($sql);
  1510.         $query->setParameter('lessonModuleId', $lessonModuleId);
  1511.         $query->setParameter('deleted', $deleted);
  1512.         $query->setParameter('typeDelete', $typeDelete);
  1513.         $query->setParameter('userDelete', $userDelete);
  1514.         $query->setParameter('dateNow', $dateNow);
  1515.         $query->execute();
  1516.         
  1517.         return true;
  1518.     }
  1520.     public function restoreByExamUser($examUserId, $deleted, $typeDelete, $userDelete, $dateNow){
  1521.         $sql = "UPDATE EADPlataforma:ExamUser AS eu 
  1522.                 SET eu.deleted = :deleted, eu.userDelete = :userDelete, 
  1523.                     eu.dateDelete = :dateNow
  1524.                 WHERE eu.deleted = (1 + :deleted)
  1525.                 AND eu.typeDelete = :typeDelete
  1526.                 AND eu.id = :examUserId";
  1527.         $query = $this->em->createQuery($sql);
  1528.         $query->setParameter('examUserId', $examUserId);
  1529.         $query->setParameter('deleted', $deleted);
  1530.         $query->setParameter('typeDelete', $typeDelete);
  1531.         $query->setParameter('userDelete', $userDelete);
  1532.         $query->setParameter('dateNow', $dateNow);
  1533.         $query->execute();
  1534.         
  1535.         return true;
  1536.     }
  1538.     public function deleteByExamUser($examUserId, $deleted, $typeDelete, $userDelete, $dateNow){
  1540.         $sql = "
  1541.             UPDATE EADPlataforma:ExamUser AS eu 
  1542.             SET 
  1543.                 eu.deleted = :deleted, 
  1544.                 eu.typeDelete = :typeDelete,
  1545.                 eu.userDelete = :userDelete, 
  1546.                 eu.dateDelete = :dateNow
  1547.             WHERE eu.deleted = (:deleted - 1)
  1548.             AND eu.id = :examUserId
  1549.         ";
  1551.         $query = $this->em->createQuery($sql);
  1553.         $query->setParameter('examUserId', $examUserId);
  1554.         $query->setParameter('deleted', $deleted);
  1555.         $query->setParameter('typeDelete', $typeDelete);
  1556.         $query->setParameter('userDelete', $userDelete);
  1557.         $query->setParameter('dateNow', $dateNow);
  1558.         
  1559.         $query->execute();
  1560.         
  1561.         return true;
  1562.     }
  1564.     public function restoreByLesson($lessonId, $deleted, $typeDelete, $userDelete, $dateNow){
  1565.         $sql = "UPDATE EADPlataforma:ExamUser AS eu
  1566.                 SET eu.deleted = :deleted, eu.userDelete = :userDelete, 
  1567.                     eu.dateDelete = :dateNow
  1568.                 WHERE IDENTITY(eu.exam) IN (SELECT e.id 
  1569.                                             FROM EADPlataforma:Exam AS e 
  1570.                                             WHERE e.lesson = :lessonId )
  1571.                 AND eu.deleted = (1 - :deleted)
  1572.                 AND eu.typeDelete = :typeDelete";
  1573.         $query = $this->em->createQuery($sql);
  1574.         $query->setParameter('lessonId', $lessonId);
  1575.         $query->setParameter('deleted', $deleted);
  1576.         $query->setParameter('typeDelete', $typeDelete);
  1577.         $query->setParameter('userDelete', $userDelete);
  1578.         $query->setParameter('dateNow', $dateNow);
  1579.         $query->execute();
  1580.         
  1581.         return true;
  1582.     }
  1584.     public function deleteByLesson($lessonId, $deleted, $typeDelete, $userDelete, $dateNow){
  1585.         $sql = "UPDATE EADPlataforma:ExamUser AS eu
  1586.                 SET eu.deleted = :deleted, eu.typeDelete = :typeDelete,
  1587.                     eu.userDelete = :userDelete, eu.dateDelete = :dateNow
  1588.                 WHERE IDENTITY(eu.exam) IN (SELECT e.id 
  1589.                                             FROM EADPlataforma:Exam AS e 
  1590.                                             WHERE e.lesson = :lessonId )
  1591.                 AND eu.deleted = (:deleted - 1)";
  1592.         $query = $this->em->createQuery($sql);
  1593.         $query->setParameter('lessonId', $lessonId);
  1594.         $query->setParameter('deleted', $deleted);
  1595.         $query->setParameter('typeDelete', $typeDelete);
  1596.         $query->setParameter('userDelete', $userDelete);
  1597.         $query->setParameter('dateNow', $dateNow);
  1598.         $query->execute();
  1599.         
  1600.         return true;
  1601.     }
  1603.     public function deleteTrashCron()
  1604.     {
  1605.         $sql = "UPDATE EADPlataforma:ExamUser AS eu SET eu.deleted = 2 
  1606.                 WHERE eu.dateDelete <= :date AND eu.deleted = 1 ";
  1607.         $query = $this->em->createQuery($sql);
  1608.         $query->setParameter('date', date('Y-m-d H:i:s', strtotime('-90 days')));
  1609.         $query->execute();
  1610.     }
  1612.     public function findExamUserExpired()
  1613.     {
  1614.         $query = $this->createQueryBuilder('eu');
  1615.         $query->andWhere('eu.status = :status');
  1616.         $query->andWhere('DATE(:currentDate) > DATE(eu.dateStart)');
  1617.         $query->andWhere('eu.inactive = :inactive');
  1619.         $query->setParameter('status', ExamUserEnum::IN_PROGRESS);
  1620.         $query->setParameter('currentDate', date('Y-m-d H:i:s'));
  1621.         $query->setParameter('inactive', ExamUserEnum::NO);
  1622.         
  1623.         return $query->getQuery()->getResult();
  1624.     }
  1626.     public function expiredExams()
  1627.     {
  1628.         $examsUsers = $this->findExamUserExpired();
  1629.         foreach ($examsUsers as $examUser) {
  1630.             $this->examUserIsExpired($examUser);
  1631.         }
  1632.     }
  1633. }