<?php
namespace EADPlataforma\Repository;
use EADPlataforma\Entity\Exam;
use EADPlataforma\Entity\ExamUser;
use EADPlataforma\Entity\ExamUserAnswer;
use EADPlataforma\Entity\Enrollment;
use EADPlataforma\Entity\Course;
use EADPlataforma\Entity\Library;
use EADPlataforma\Entity\Lesson;
use EADPlataforma\Entity\LessonModule;
use EADPlataforma\Entity\CourseCertificate;
use EADPlataforma\Entity\User;
use EADPlataforma\Entity\Question;
use EADPlataforma\Entity\Trash;
use EADPlataforma\Enum\ExamEnum;
use EADPlataforma\Enum\ExamUserEnum;
use EADPlataforma\Enum\CourseEnum;
use EADPlataforma\Enum\LessonEnum;
use EADPlataforma\Enum\LessonModuleEnum;
use EADPlataforma\Enum\QuestionEnum;
use EADPlataforma\Enum\TagsMarketingEnum;
use EADPlataforma\Enum\NotificationEnum;
use EADPlataforma\Enum\UserEnum;
use \PDO;
/**
* @method ExamUser|null find($id, $lockMode = null, $lockVersion = null)
* @method ExamUser|null findOneBy(array $criteria, array $orderBy = null)
* @method ExamUser[] findAll()
* @method ExamUser[] findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
*/
class ExamUserRepository extends AbstractRepository
{
public function getEntityClass(){
return ExamUser::class;
}
public function getConnectionName(){
return "school";
}
public function findItems()
{
return $this->createQueryBuilder('eu')->getQuery()->getResult();
}
public function getLastContentByExam(Exam $exam, User $user)
{
$examRepository = $this->em->getRepository(Exam::class);
$lessonRepository = $this->em->getRepository(Lesson::class);
$libraryRepository = $this->em->getRepository(Library::class);
$examType = $exam->getType();
$course = $exam->getCourse();
$lessonModule = $exam->getLessonModule();
$lesson = $exam->getLesson();
$contentPagesNumber = null;
$contentDuration = null;
$contentType = null;
$contentThumb = null;
$lessonId = null;
$moduleId = null;
$courseId = $course->getId();
$lessonRef = null;
$type = "lesson";
if($examType == ExamEnum::QUIZ){
$lessonRef = $lesson;
}else if($examType == ExamEnum::LESSON){
$lessonRef = $lesson;
}else if($examType == ExamEnum::MODULE){
$lessonRef = $lessonRepository->getLastLesson($course, $lessonModule);
}else if($examType == ExamEnum::COURSE){
$lessonRef = $lessonRepository->getLastLesson($course);
}
if(empty($lessonRef)){
return;
}
$lessonId = $lessonRef->getId();
$moduleRef = $lessonRef->getLessonModule();
$moduleId = $moduleRef->getId();
$examLast = null;
if($examType == ExamEnum::COURSE){
$filter = [
"course" => $courseId,
"lessonModule" => $moduleId,
"type" => ExamEnum::MODULE,
"status" => ExamEnum::PUBLISHED,
"deleted" => ExamEnum::ITEM_NO_DELETED
];
$type = "exam";
$examLast = $examRepository->findOneBy($filter);
}
if(empty($examLast) && ($examType == ExamEnum::COURSE || $examType == ExamEnum::MODULE)){
$filter = [
"course" => $courseId,
"lessonModule" => $moduleId,
"lesson" => $lessonId,
"type" => ExamEnum::LESSON,
"status" => ExamEnum::PUBLISHED,
"deleted" => ExamEnum::ITEM_NO_DELETED
];
$type = "exam";
$examLast = $examRepository->findOneBy($filter);
}
if(empty($examLast)){
$filter = [
"course" => $courseId,
"lessonModule" => $moduleId,
"lesson" => $lessonId,
"type" => ExamEnum::QUIZ,
"status" => ExamEnum::PUBLISHED,
"deleted" => ExamEnum::ITEM_NO_DELETED
];
$type = "quiz";
$examLast = $examRepository->findOneBy($filter);
}
if(empty($examLast)){
$type = "lesson";
$library = $lessonRef->getLibrary();
if($library){
$contentPagesNumber = $library->getPagesNumber();
$contentDuration = $library->getDuration();
$contentType = $library->getType();
$contentThumb = $libraryRepository->getCover($library);
}
}
$timeUtil = $this->generalService->getUtil('DateTimeUtil');
return (object)[
"id" => (int)($examLast ? $examLast->getId() : $lessonRef->getId()),
"lessonId" => $lessonId,
"moduleId" => $moduleId,
"courseId" => $courseId,
"title" => ($examLast ? $examLast->getTitle() : $lessonRef->getTitle()),
"type" => $type,
"contentPagesNumber" => $contentPagesNumber,
"contentDuration" => $contentDuration ? $timeUtil->timeToSec($contentDuration) : null,
"contentType" => $contentType,
"contentThumb" => $contentThumb,
"isAccessible" => true,
"acessMessage" => null,
];
}
public function getNextContentByExam(Exam $exam, User $user)
{
$examRepository = $this->em->getRepository(Exam::class);
$enrollmentRepository = $this->em->getRepository(Enrollment::class);
$lessonRepository = $this->em->getRepository(Lesson::class);
$libraryRepository = $this->em->getRepository(Library::class);
$courseRepository = $this->em->getRepository(Course::class);
$examType = $exam->getType();
$course = $exam->getCourse();
$lessonModule = $exam->getLessonModule();
$lesson = $exam->getLesson();
$contentPagesNumber = null;
$contentDuration = null;
$contentType = null;
$contentThumb = null;
$lessonId = null;
$moduleId = null;
$courseId = $course->getId();
$lessonRef = null;
$type = "exam";
$examNext = null;
$isAccessible = false;
$acessMessage = null;
if($examType == ExamEnum::COURSE){
return;
}
if($examType == ExamEnum::QUIZ){
$lessonRef = $lesson;
$filter = [
"course" => $courseId,
"lessonModule" => $moduleId,
"lesson" => $lessonId,
"type" => ExamEnum::LESSON,
"status" => ExamEnum::PUBLISHED,
"deleted" => ExamEnum::ITEM_NO_DELETED
];
$examNext = $examRepository->findOneBy($filter);
}else if($examType == ExamEnum::LESSON){
$lessonRef = $lesson;
}else if($examType == ExamEnum::MODULE){
$lessonRef = $lessonRepository->getLastLesson($course, $lessonModule);
}
if(!$examNext && $lessonRepository->isLastModuleLesson($lessonRef)){
$filter = [
"course" => $courseId,
"lessonModule" => $moduleId,
"type" => ExamEnum::MODULE,
"status" => ExamEnum::PUBLISHED,
"deleted" => ExamEnum::ITEM_NO_DELETED
];
$examNext = $examRepository->findOneBy($filter);
}
if(!$examNext && $lessonRepository->isLastCourseLesson($lessonRef)){
$filter = [
"course" => $courseId,
"type" => ExamEnum::COURSE,
"status" => ExamEnum::PUBLISHED,
"deleted" => ExamEnum::ITEM_NO_DELETED
];
$examNext = $examRepository->findOneBy($filter);
}
if(!$examNext){
if(!$lessonRef){
return;
}
$type = "lesson";
$nextLessonId = $lessonRepository->getLessonIdAfterThat($lessonRef);
if(empty($nextLessonId)){
return;
}
$lessonRef = $lessonRepository->find($nextLessonId);
if(!$lessonRef){
return;
}
$enrollment = $enrollmentRepository->findOneBy([
"user" => $user->getId(),
"course" => $courseId,
]);
if($enrollment){
$isStudent = $courseRepository->isStudent($course);
$infoAccess = $lessonRepository->checkLessonIsAccessibleToUser(
$lessonRef,
$enrollment,
null,
$isStudent
);
$isAccessible = $infoAccess->isAccessible;
$acessMessage = $infoAccess->message;
}
$library = $lessonRef->getLibrary();
if($library){
$contentPagesNumber = $library->getPagesNumber();
$contentDuration = $library->getDuration();
$contentType = $library->getType();
$contentThumb = $libraryRepository->getCover($library);
}
}else{
$infoAccess = $examRepository->checkExamIsAccessible($examNext);
$isAccessible = $infoAccess->isAccessible;
$acessMessage = $infoAccess->message;
}
$timeUtil = $this->generalService->getUtil('DateTimeUtil');
return (object)[
"id" => (int)($examNext ? $examNext->getId() : $lessonRef->getId()),
"lessonId" => $lessonId,
"moduleId" => $moduleId,
"courseId" => $courseId,
"title" => ($examNext ? $examNext->getTitle() : $lessonRef->getTitle()),
"type" => $type,
"contentPagesNumber" => $contentPagesNumber,
"contentDuration" => (
$contentDuration ?
$timeUtil->timeToSec($contentDuration) :
null
),
"contentType" => $contentType,
"contentThumb" => $contentThumb,
"isAccessible" => $isAccessible,
"acessMessage" => $acessMessage,
];
}
public function getDataIndexNew(Exam $exam, User $user)
{
$examUser = $this->findOneBy([
"user" => $user->getId(),
"exam" => $exam->getId(),
"inactive" => ExamUserEnum::NO,
"deleted" => ExamUserEnum::ITEM_NO_DELETED
]);
$attemptsInfo = $this->getAttemptsInfo($exam, $examUser);
$dateTimeUtil = $this->generalService->getUtil('DateTimeUtil');
$timeSeconds = $dateTimeUtil->timeToSec($exam->getExamTime());
$timeRestToEndSeconds = $timeSeconds;
$progress = 0;
$withTemplate = false;
$onlyAnswer = false;
$questions = null;
if($examUser && $exam->getType() != ExamEnum::QUIZ){
$expired = $this->examUserIsExpired($examUser);
if($expired){
$examUser = $this->findOneBy([
"id" => $examUser->getId()
]);
}
$todaySeconds = strtotime(date('Y-m-d H:i:s'));
$dateStartSeconds = strtotime($examUser->getDateStart());
$dateExamEndSecods = $dateStartSeconds + $timeSeconds;
if($examUser->getStatus() == ExamUserEnum::IN_PROGRESS){
$timeRestToEndSeconds = $dateExamEndSecods - $todaySeconds;
if($timeRestToEndSeconds < 0){
$timeRestToEndSeconds = 0;
}
}
$progress = $this->getProgress($examUser);
$onlyAnswer = ($examUser->getStatus() != ExamUserEnum::IN_PROGRESS);
if($exam->getShowTemplate() == QuestionEnum::YES){
$withTemplate = $onlyAnswer;
if($attemptsInfo->attempts == ExamEnum::YES){
$withTemplate = false;
}
}
$questionRepository = $this->em->getRepository(Question::class);
$questions = $questionRepository->getQuestionsByExamUser(
$examUser,
$withTemplate,
$onlyAnswer
);
}else if($exam->getType() == ExamEnum::QUIZ){
$questions = $exam->getQuestionsArray(true);
}
$data = (object)[
"lastContent" => $this->getLastContentByExam($exam, $user),
"nextContent" => $this->getNextContentByExam($exam, $user),
"examUser" => ($examUser ? $examUser->toReturn() : null),
"examConfig" => (object)[
"id" => $exam->getId(),
"title" => $exam->getTitle(),
"questionNumber" => $exam->getQuestionNumber(),
"examWeight" => $exam->getExamWeight(),
"examTime" => $exam->getExamTime(),
"course" => ($exam->getCourse() ? $exam->getCourse()->getId() : null),
"lessonModule" => (
$exam->getLessonModule() ?
$exam->getLessonModule()->getId() :
null
),
"lesson" => ($exam->getLesson() ? $exam->getLesson()->getId() : null),
"controlTime" => $exam->getControlTime(),
],
"timeRestToEnd" => $dateTimeUtil->secToTime($timeRestToEndSeconds),
"timeRestToEndSeconds" => $timeRestToEndSeconds,
"progress" => $progress,
"attemptDateRelease" => $attemptsInfo->attemptDateRelease,
"allowAttempt" => $attemptsInfo->attempts,
"hasAttempts" => $attemptsInfo->hasAttempts,
"isLastAttempt" => $attemptsInfo->isLastAttempt,
"questions" => $questions,
];
return $data;
}
public function getProgress(ExamUser $examUser)
{
$user = $examUser->getUser();
$exam = $examUser->getExam();
$progress = 100;
if(
$examUser->getStatus() == ExamUserEnum::IN_PROGRESS ||
$examUser->getStatus() == ExamUserEnum::TIMEOUT
){
$examUserAnswerRepository = $this->em->getRepository(ExamUserAnswer::class);
$questionAnswerNumber = $examUserAnswerRepository->countAsnweredByExamUser(
$examUser
);
$questionNumber = $exam->getQuestionNumber();
if(empty($exam->getQuestionNumber())){
$questionNumber = 1;
}
$progress = round($questionAnswerNumber * 100 / $questionNumber);
$progress = ($progress >= 100 ? 100 : $progress);
}
return $progress;
}
public function getAttemptsInfo(Exam $exam, ?ExamUser $examUser = null)
{
$isLastAttempt = ExamEnum::YES;
$attempts = $exam->getAttempts();
$hasAttempts = $exam->getAttempts();
$attemptDateRelease = null;
$dateTimeUtil = $this->generalService->getUtil('DateTimeUtil');
if($examUser){
$user = $examUser->getUser();
if(
$attempts == ExamEnum::YES &&
$examUser->getStatus() != ExamUserEnum::APPROVED
){
$attemptsUserNumber = $this->count([
"deleted" => ExamUserEnum::ITEM_NO_DELETED,
"user" => $user->getId(),
"exam" => $exam->getId(),
]);
if($attemptsUserNumber >= $exam->getAttemptsNumber()){
$attempts = ExamEnum::NO;
}else{
$isLastAttempt = ExamEnum::NO;
if($exam->getAttemptsAutoRelease() == ExamEnum::NO){
$typeRelease = $exam->getAttemptsTypeRelease();
$dateEnd = $examUser->getDateEnd();
$examTimeTry = 0;
if($typeRelease == ExamEnum::DAYS){
$examTimeTry = strtotime(
"{$dateEnd} + {$exam->getAttemptsPeriod()} day"
);
}else if($typeRelease == ExamEnum::HOURS){
$attemptTimeSeconds = $dateTimeUtil->timeToSec(
$exam->getAttemptsTime()
);
$examTimeTry = strtotime($dateEnd) + $attemptTimeSeconds;
}
$todaySeconds = strtotime(date('Y-m-d H:i:s'));
if($examTimeTry > $todaySeconds){
$attempts = ExamEnum::NO;
$attemptDateRelease = date('Y-m-d H:i:s', $examTimeTry);
}
}
}
}else{
$attempts = ExamEnum::NO;
}
}
return (object)[
"isLastAttempt" => $isLastAttempt,
"attempts" => $attempts,
"hasAttempts" => $hasAttempts,
"attemptDateRelease" => $attemptDateRelease,
];
}
public function countValidExamUserByCourse(int $courseId, int $userId)
{
$query = $this->createQueryBuilder('eu');
$query->innerJoin('EADPlataforma:Exam', 'e', 'WITH', 'e.id = eu.exam');
$query->innerJoin('EADPlataforma:Course', 'c', 'WITH', 'c.id = e.course');
$query->leftJoin('EADPlataforma:LessonModule', 'lm', 'WITH', 'lm.id = e.lessonModule');
$query->leftJoin('EADPlataforma:Lesson', 'l', 'WITH', 'l.id = e.lesson');
$query->select('COUNT(e.id) AS total');
$query->andWhere('eu.deleted = :examUserDeleted');
$query->andWhere('e.deleted = :examDeleted');
$query->andWhere('e.status = :examStatus');
$query->andWhere('eu.releaseAccess = :yes');
$query->andWhere('eu.inactive = :no');
$query->andWhere('e.examWeight > 0');
//$query->andWhere('e.requirement = :yes');
$query->andWhere('e.course = :course');
$query->andWhere('eu.user = :user');
$query->andWhere('
(
(e.type = :typeCourse AND c.deleted = :courseDeleted)
OR (
e.type = :typeLessonModule
AND lm.status = :lessonModuleStatus
AND lm.deleted = :lessonModuleDeleted
)
OR (
e.type = :typeLesson
AND l.status = :lessonStatus
AND lm.status = :lessonModuleStatus
AND l.deleted = :lessonDeleted
)
)
');
$query->setParameter('typeCourse', ExamEnum::COURSE);
$query->setParameter('typeLessonModule', ExamEnum::MODULE);
$query->setParameter('typeLesson', ExamEnum::LESSON);
$query->setParameter('examDeleted', ExamEnum::ITEM_NO_DELETED);
$query->setParameter('examUserDeleted', ExamUserEnum::ITEM_NO_DELETED);
$query->setParameter('courseDeleted', CourseEnum::ITEM_NO_DELETED);
$query->setParameter('lessonModuleDeleted', LessonModuleEnum::ITEM_NO_DELETED);
$query->setParameter('lessonDeleted', LessonEnum::ITEM_NO_DELETED);
$query->setParameter('lessonModuleStatus', LessonModuleEnum::PUBLISHED);
$query->setParameter('lessonStatus', LessonEnum::PUBLISHED);
$query->setParameter('examStatus', ExamEnum::PUBLISHED);
$query->setParameter('yes', ExamEnum::YES);
$query->setParameter('no', ExamEnum::NO);
$query->setParameter('course', $courseId);
$query->setParameter('user', $userId);
$result = (object)$query->getQuery()->getOneOrNullResult();
return $result->total;
}
public function countExamUserStatus(?int $status = null)
{
$query = $this->createQueryBuilder('eu');
$query->select('COUNT(eu.id) AS total');
$query->andWhere('eu.deleted = :deleted');
$query->setParameter('deleted', ExamUserEnum::ITEM_NO_DELETED);
if(!is_null($status)){
$query->andWhere('eu.status = :status');
$query->setParameter('status', $status);
}
$result = (object)$query->getQuery()->getOneOrNullResult();
return $result->total;
}
public function getCurrentStartedExamUser(Course $course, User $user){
$q = $this->createQueryBuilder('eu');
$q->innerJoin(
'EADPlataforma:Exam',
'e',
'WITH',
'e.id = eu.exam AND e.deleted = :examDeleted'
);
$q->leftJoin(
'EADPlataforma:LessonModule',
'lm',
'WITH',
'lm.id = e.lessonModule'
);
$q->leftJoin(
'EADPlataforma:Lesson',
'l',
'WITH',
'l.id = e.lesson'
);
$q->andWhere('eu.deleted = :deleted');
$q->andWhere('eu.user = :user');
$q->andWhere('eu.course = :course');
$q->andWhere('eu.status != :statusExamUser');
$q->andWhere('eu.inactive = :inactive');
$q->andWhere('eu.releaseAccess = :releaseAccess');
$q->andWhere('e.requirement = :requirement');
$q->andWhere('e.status = :statusExam');
$q->andWhere('(
(
e.type = :typeLesson
AND l.deleted = :lessonDeleted
AND l.status = :statusL
AND lm.status = :statusLM
)
OR
(
e.type = :typeModule
AND lm.deleted = :lessonModuleDeleted
AND lm.status = :statusLM
)
)');
$q->setParameter('examDeleted', ExamEnum::ITEM_NO_DELETED);
$q->setParameter('deleted', ExamUserEnum::ITEM_NO_DELETED);
$q->setParameter('lessonDeleted', LessonEnum::ITEM_NO_DELETED);
$q->setParameter('lessonModuleDeleted', LessonModuleEnum::ITEM_NO_DELETED);
$q->setParameter('statusLM', LessonModuleEnum::PUBLISHED);
$q->setParameter('statusL', LessonEnum::PUBLISHED);
$q->setParameter('user', $user->getId());
$q->setParameter('course', $course->getId());
$q->setParameter('statusExamUser', ExamUserEnum::APPROVED);
$q->setParameter('inactive', ExamUserEnum::NO);
$q->setParameter('releaseAccess', ExamUserEnum::NO);
$q->setParameter('requirement', ExamEnum::YES);
$q->setParameter('statusExam', ExamEnum::PUBLISHED);
$q->setParameter('typeModule', ExamEnum::MODULE);
$q->setParameter('typeLesson', ExamEnum::LESSON);
$q->setMaxResults(1);
return $q->getQuery()->getOneOrNullResult();
}
public function getCurrentExamOnCourseToUserByLesson(Lesson $lessonBase, User $user)
{
$course = $lessonBase->getCourse();
$lessonModule = $lessonBase->getLessonModule();
$examUser = $this->getCurrentStartedExamUser($course, $user);
if($examUser){
return $examUser->getExam();
}
$examRepository = $this->em->getRepository(Exam::class);
$exam = $examRepository->getCurrentNotStartedExamUser($lessonBase, $user);
if($exam){
return $exam;
}
return;
}
public function examUserIsExpired(ExamUser $examUser)
{
if($examUser->getStatus() != ExamUserEnum::IN_PROGRESS){
return true;
}
//valid exam type
$dateTimeUtil = $this->generalService->getUtil('DateTimeUtil');
$dateStartSeconds = strtotime($examUser->getDateStart());
$exam = $examUser->getExam();
$course = $exam->getCourse();
$timeSeconds = $dateTimeUtil->timeToSec($exam->getExamTime());
$todaySeconds = strtotime(date('Y-m-d H:i:s'));
$dateExamEndSecods = $dateStartSeconds + $timeSeconds;
if($todaySeconds >= $dateExamEndSecods){
$examUser->setStatus(ExamUserEnum::TIMEOUT);
$examUser->setGrade(0);
$examUser->setDateEnd(date("Y-m-d H:i:s", $dateExamEndSecods));
//notify
$notificationService = $this->generalService->getService('NotificationService');
$notificationService->create(
$examUser->getUser(),
$course->getUser(),
NotificationEnum::ORIGIN_EXAM_USER_TIMEOUT,
$examUser->getId()
);
$notificationService->create(
$course->getUser(),
$examUser->getUser(),
NotificationEnum::ORIGIN_EXAM_USER_TIMEOUT,
$examUser->getId()
);
$this->em->flush();
$data = $examUser->toReturn();
$userLogService = $this->generalService->getService('LogService');
$userLogService->logUpdate("exam_user", $examUser->getId(), $data);
return true;
}
return false;
}
public function getValidExamUserById(?int $examUserId = null,
?bool $onlyUserInExam = false,
?ExamUser $examUser = null)
{
if(!$examUser){
$examUser = $this->findOneBy([
"id" => $examUserId,
"deleted" => ExamUserEnum::ITEM_NO_DELETED
]);
}
if(!$examUser) {
return;
}
if($onlyUserInExam){
if($this->getUser()->getId() != $examUser->getUser()->getId()){
return;
}
}
if($examUser->getInactive() == ExamUserEnum::YES){
return;
}
$exam = $examUser->getExam();
if($examUser->getCourse()->isDeleted()) {
return;
}
if($exam->getLessonModule()){
if($exam->getLessonModule()->isDeleted()) {
return;
}
}
if($exam->getLesson()){
if($exam->getLesson()->isDeleted()) {
return;
}
}
return $examUser;
}
public function updateExamUser(ExamUser $examUser, ?bool $ignoreTemplates = false)
{
$grade = $this->getGrade($examUser);
$exam = $examUser->getExam();
$examUser->setGrade($grade);
if(empty($examUser->getDateEnd())){
$examUser->setDateEnd(date("Y-m-d H:i:s"));
}
$examUserAnswerRepository = $this->em->getRepository(ExamUserAnswer::class);
$countQuestionNotEvalueted = $examUserAnswerRepository->countQuestionNotEvalueted($examUser);
$examUser->setStatus(ExamUserEnum::WAITING_CORRECTION);
if(empty($countQuestionNotEvalueted) || $ignoreTemplates){
if($grade >= $exam->getExamAverage()){
$examUser->setReleaseAccess(ExamUserEnum::YES);
$examUser->setStatus(ExamUserEnum::APPROVED);
}else{
$examUser->setStatus(ExamUserEnum::DISAPPROVED);
}
}
$this->em->flush();
$this->processExamUser($examUser);
}
public function processExamUser(ExamUser $examUser)
{
$course = $examUser->getCourse();
$user = $examUser->getUser();
$enrollment = $this->em->getRepository(Enrollment::class)->findOneBy([
"deleted" => ExamUserEnum::ITEM_NO_DELETED,
"user" => $user->getId(),
"course" => $course->getId(),
]);
if(!$enrollment){
return;
}
//notify
$notificationService = $this->generalService->getService('NotificationService');
$exam = $examUser->getExam();
if($exam->getType() == ExamEnum::COURSE){
$finalAverage = $this->getFinalAvarege($user->getId(), $course->getId());
$enrollment->setFinalAverage($finalAverage);
if($this->configuration->isModuleActive('course_certificate_module')){
if($enrollment->getCertificate() == ExamEnum::YES){
$certificateAverage = $course->getCertificateAverage();
$courseCertificateTemplate = $course->getCourseCertificateTemplate();
if(!empty($finalAverage)){
if($finalAverage >= $certificateAverage){
//issue certificate
$courseCertificateRepository = $this->em->getRepository(
CourseCertificate::class
);
$courseCertificateRepository->createCertificate(
$enrollment,
(
$courseCertificateTemplate ?
$courseCertificateTemplate :
$enrollment->getCourseCertificateTemplate()
),
$finalAverage
);
}
}
}
}
if($examUser->getStatus() == ExamUserEnum::APPROVED){
$marketingService = $this->generalService->getService(
'Marketing\\MarketingService'
);
$marketingService->setTag(TagsMarketingEnum::TAG_FINISH_COURSE);
$marketingService->setTextComplement($exam->getCourse()->getTitle());
$marketingService->setUser($examUser->getUser());
$marketingService->send();
}
}
if($examUser->getStatus() == ExamUserEnum::APPROVED){
$notificationService->create(
$course->getUser(),
$user,
NotificationEnum::ORIGIN_EXAM_USER_APPROVED,
$examUser->getId()
);
$notificationService->create(
$user,
$course->getUser(),
NotificationEnum::ORIGIN_EXAM_USER_APPROVED,
$examUser->getId()
);
}else if($examUser->getStatus() == ExamUserEnum::DISAPPROVED){
$notificationService->create(
$course->getUser(),
$user,
NotificationEnum::ORIGIN_EXAM_USER_DISAPPROVED,
$examUser->getId()
);
$notificationService->create(
$user,
$course->getUser(),
NotificationEnum::ORIGIN_EXAM_USER_DISAPPROVED,
$examUser->getId()
);
}else if($examUser->getStatus() == ExamUserEnum::WAITING_CORRECTION){
$notificationService->create(
$user,
$course->getUser(),
NotificationEnum::ORIGIN_EXAM_USER_WAITING_CORRECTION,
$examUser->getId()
);
}
}
public function export($courseId = null, $userId = null, $groupId = null,
$teacherId = null, $status = null, $dateStart = null,
$dateEnd = null)
{
$query = $this->createQueryBuilder('eu');
$query->innerJoin('EADPlataforma:Exam', 'e', 'WITH', 'e.id = eu.exam AND e.deleted = 0');
$query->innerJoin('EADPlataforma:User', 'u', 'WITH', 'u.id = eu.user AND u.deleted = 0');
$query->innerJoin('EADPlataforma:Course', 'c', 'WITH', 'c.id = eu.course AND c.deleted = 0');
$query->leftJoin('EADPlataforma:LessonModule', 'lm', 'WITH', 'lm.id = e.lessonModule AND lm.deleted = 0');
$query->leftJoin('EADPlataforma:Lesson', 'l', 'WITH', 'l.id = e.lesson AND l.deleted = 0');
$query->andWhere('eu.deleted = :examUserDeleted');
$query->setParameter('examUserDeleted', ExamUserEnum::ITEM_NO_DELETED);
$query->andWhere('u.status != :userStatus');
$query->setParameter('userStatus', UserEnum::INACTIVE);
if(!is_null($dateStart) && !is_null($dateEnd)){
$query->andWhere('DATE(eu.dateStart) BETWEEN :dateStart AND :dateEnd');
$query->setParameter('dateStart', $dateStart);
$query->setParameter('dateEnd', $dateEnd);
}
if($courseId > 0){
$query->andWhere('eu.course = :course')->setParameter('course', $courseId);
}
if($userId > 0){
$query->andWhere('eu.user = :user')->setParameter('user', $userId);
}
if($teacherId > 0){
$query->andWhere('(
c.user = :teacherId
OR
( lm.id > 0 AND lm.user = :teacherId )
OR
( l.id > 0 AND l.user = :teacherId )
)')->setParameter('teacherId', $teacherId);
}
if($groupId > 0){
$query->innerJoin('EADPlataforma:Group', 'gu', 'WITH', 'u MEMBER OF gu.user');
$query->andWhere('gu.id = :groupId')->setParameter('groupId', $groupId);
}
if(!is_null($status)){
$query->andWhere('eu.status = :status')->setParameter('status', $status);
}
$query->addOrderBy('eu.course', 'ASC');
$query->addOrderBy('eu.user', 'ASC');
$query->addOrderBy('eu.id', 'ASC');
return $query->getQuery()->execute();
}
public function getGrade(ExamUser $examUser)
{
$courseRepository = $this->em->getRepository(Course::class);
$isStudent = $courseRepository->isStudent($examUser->getCourse());
$query = $this->em->createQueryBuilder();
$query->from('EADPlataforma:ExamUserAnswer', 'eua');
$exam = $examUser->getExam();
$questionNumber = $exam->getQuestionNumberReal();
$query->select("ROUND((ROUND(SUM(eua.grade), 2) * 10) / {$questionNumber}, 2) AS finalGrade");
$query->innerJoin(
'EADPlataforma:ExamUser',
'eu',
'WITH',
'eu.id = eua.examUser AND eu.deleted = 0'
);
$query->innerJoin('EADPlataforma:Exam', 'e', 'WITH', 'e.id = eu.exam AND e.deleted = 0');
$query->innerJoin('EADPlataforma:Course', 'c', 'WITH', 'c.id = e.course');
$query->leftJoin('EADPlataforma:LessonModule', 'lm', 'WITH', 'lm.id = e.lessonModule');
$query->leftJoin('EADPlataforma:Lesson', 'l', 'WITH', 'l.id = e.lesson');
$statusModule = "";
$statusLesson = "";
if($isStudent){
$statusModule .= "AND lm.status = :moduleStatus ";
$statusLesson .= "AND l.status = :lessonStatus ";
$query->setParameter('moduleStatus', ExamEnum::PUBLISHED);
$query->setParameter('lessonStatus', ExamEnum::PUBLISHED);
}
$query->andWhere("(
(
e.type = :examModuleType
{$statusModule}
)
OR (
e.type = :examLessonType
{$statusLesson}
{$statusModule}
)
OR ( e.type = :examCourseType )
)");
$query->setParameter('examModuleType', ExamEnum::MODULE);
$query->setParameter('examLessonType', ExamEnum::LESSON);
$query->setParameter('examCourseType', ExamEnum::COURSE);
$query->andWhere('eua.deleted = 0');
$query->andWhere('eua.examUser = :examUser')->setParameter('examUser', $examUser->getId());
$result = (object)$query->getQuery()->getOneOrNullResult();
return $result->finalGrade;
}
public function getFinalAvarege($userId, $courseId)
{
$query = $this->em->createQueryBuilder();
$query->from('EADPlataforma:Exam', 'e');
$query->innerJoin('EADPlataforma:Course', 'c', 'WITH', 'c.id = e.course');
$query->leftJoin('EADPlataforma:LessonModule', 'lm', 'WITH', 'lm.id = e.lessonModule');
$query->leftJoin('EADPlataforma:Lesson', 'l', 'WITH', 'l.id = e.lesson');
$query->leftJoin(
'EADPlataforma:ExamUser',
'eu',
'WITH',
'
eu.exam = e.id
AND eu.status IN (:statusApproved, :statusDisapproved)
AND eu.user = :user
AND eu.inactive = :incative
AND eu.deleted = 0
'
);
$query->select('
ROUND(
SUM(
e.examWeight * (
CASE
WHEN eu.grade IS NOT NULL
THEN eu.grade
ELSE 0
END
)
) / SUM(e.examWeight)
, 2) AS finalAverage
');
$query->andWhere('(
(
e.type = :examModuleType
AND lm.status = :moduleStatus
)
OR (
e.type = :examLessonType
AND l.status = :lessonStatus
AND lm.status = :moduleStatus
)
OR ( e.type = :examCourseType )
)');
$query->setParameter('examModuleType', ExamEnum::MODULE);
$query->setParameter('examLessonType', ExamEnum::LESSON);
$query->setParameter('examCourseType', ExamEnum::COURSE);
$query->setParameter('moduleStatus', ExamEnum::PUBLISHED);
$query->setParameter('lessonStatus', ExamEnum::PUBLISHED);
$query->setParameter('statusApproved', ExamUserEnum::APPROVED);
$query->setParameter('statusDisapproved', ExamUserEnum::DISAPPROVED);
$query->setParameter('incative', ExamUserEnum::NO);
$query->setParameter('user', $userId);
//$query->andWhere('e.requirement = :requirement');
//$query->setParameter('requirement', ExamEnum::YES);
$query->andWhere('e.deleted = 0');
$query->andWhere('e.status = :status')->setParameter('status', ExamEnum::PUBLISHED);
$query->andWhere('e.course = :course')->setParameter('course', $courseId);
$query->andWhere('e.examWeight > 0');
$result = (object)$query->getQuery()->getOneOrNullResult();
return $result->finalAverage;
}
public function getStudentReportCard($userId, $courseId)
{
$examWeight = (float)$this->em->getRepository(Exam::class)->sumCourseWeight($courseId);
$query = $this->em->createQueryBuilder();
$query->from('EADPlataforma:Exam', 'e');
$query->innerJoin('EADPlataforma:Course', 'c', 'WITH', 'c.id = e.course');
$query->leftJoin('EADPlataforma:LessonModule', 'lm', 'WITH', 'lm.id = e.lessonModule');
$query->leftJoin('EADPlataforma:Lesson', 'l', 'WITH', 'l.id = e.lesson');
$query->leftJoin(
'EADPlataforma:ExamUser',
'eu',
'WITH',
'
eu.exam = e.id
AND eu.status IN (:statusApproved, :statusDisapproved)
AND eu.user = :user
AND eu.inactive = :incative
AND eu.deleted = 0
'
);
$query->select("
e.id,
c.id AS courseId,
lm.id AS moduleId,
l.id AS lessonId,
e.title AS examTitle,
e.type as examType,
lm.title AS moduleTitle,
l.title AS lessonTitle,
ROUND(eu.grade, 2) AS grade,
e.examWeight,
ROUND((eu.grade * (e.examWeight/:examWeight)), 2) AS parcial,
DATE_FORMAT(eu.dateEnd, '%Y-%m-%d %H:%i:%s') AS dateEnd
");
//$query->andWhere('e.requirement = :requirement');
$query->andWhere('e.course = :course');
$query->andWhere('e.deleted = 0');
$query->andWhere('e.examWeight > 0');
$query->andWhere('(
(
e.type = :examModuleType
AND lm.status = :moduleStatus
)
OR (
e.type = :examLessonType
AND l.status = :lessonStatus
AND lm.status = :moduleStatus
)
OR ( e.type = :examCourseType )
)');
$query->andWhere('( e.status = :status OR eu.id > 0 )');
$query->setParameter('examWeight', $examWeight);
$query->setParameter('examModuleType', ExamEnum::MODULE);
$query->setParameter('examLessonType', ExamEnum::LESSON);
$query->setParameter('examCourseType', ExamEnum::COURSE);
$query->setParameter('moduleStatus', ExamEnum::PUBLISHED);
$query->setParameter('lessonStatus', ExamEnum::PUBLISHED);
$query->setParameter('status', ExamEnum::PUBLISHED);
//$query->setParameter('requirement', ExamUserEnum::YES);
$query->setParameter('statusApproved', ExamUserEnum::APPROVED);
$query->setParameter('statusDisapproved', ExamUserEnum::DISAPPROVED);
$query->setParameter('incative', ExamUserEnum::NO);
$query->setParameter('user', $userId);
$query->setParameter('course', $courseId);
$query->addOrderBy('c.order', 'ASC');
$query->addOrderBy('lm.order', 'ASC');
$query->addOrderBy('l.order', 'ASC');
$query->addOrderBy('e.type', 'DESC');
return $query->getQuery()->execute();
}
public function getStudentReportCardApi($userId, $courseId)
{
$examWeight = (int)$this->em->getRepository(Exam::class)->sumCourseWeight($courseId);
$examApproved = ExamUserEnum::APPROVED;
$examDisapproved = ExamUserEnum::DISAPPROVED;
$examModule = ExamEnum::MODULE;
$examLesson = ExamEnum::LESSON;
$examCourse = ExamEnum::COURSE;
$dql = "SELECT
eu.id AS prova_id,
pc.title AS titulo,
pc.type AS tipo,
lm.id AS modulo_id,
lm.title AS modulo,
l.id AS aula_id,
l.title AS aula,
ROUND(eu.grade, 2) AS nota_final,
pc.examWeight AS pontos,
ROUND((eu.grade * (pc.examWeight/:examWeight)), 2) AS parcial
FROM EADPlataforma:Exam AS pc
INNER JOIN EADPlataforma:Course AS c WITH (c.id = pc.course)
LEFT JOIN EADPlataforma:LessonModule AS lm WITH (lm.id = pc.lessonModule)
LEFT JOIN EADPlataforma:Lesson AS l WITH (l.id = pc.lesson)
LEFT JOIN EADPlataforma:ExamUser AS eu WITH (
eu.exam = pc.id
AND (
eu.status = {$examApproved}
OR eu.status = {$examDisapproved}
)
AND eu.user = :user
AND eu.inactive = 0
)
WHERE pc.status = 1
AND pc.examWeight > 0
AND (
( pc.type = {$examModule} AND lm.status = 1 )
OR ( pc.type = {$examLesson} AND l.status = 1 AND lm.status = 1 )
OR ( pc.type = {$examCourse} )
)
AND (
( pc.status = 1 )
OR
(
pc.id IN (
SELECT
p.id
FROM EADPlataforma:ExamUser AS p
WHERE p.user = :user
AND p.inactive = 0
AND p.deleted = 0
)
)
)
AND pc.course = :course
AND eu.deleted = 0
AND pc.deleted = 0
ORDER BY c.order ASC, lm.order ASC, l.order ASC, pc.type DESC ";
$query = $this->em->createQuery($dql);
$query->setParameter('course', $courseId);
$query->setParameter('user', $userId);
$query->setParameter('examWeight', $examWeight);
return $query->execute();
}
public function getStudentReportCardExport($courseId = null, $userId = null, $groupId = null,
$teacherId = null, $dateStart = null, $dateEnd = null)
{
$examModuleType = ExamEnum::MODULE;
$examLessonType = ExamEnum::LESSON;
$examCourseType = ExamEnum::COURSE;
$moduleStatus = ExamEnum::PUBLISHED;
$lessonStatus = ExamEnum::PUBLISHED;
$status = ExamEnum::PUBLISHED;
$statusApproved = ExamUserEnum::APPROVED;
$statusDisapproved = ExamUserEnum::DISAPPROVED;
$incative = ExamEnum::NO;
$filterCourse = "";
if($courseId > 0){
$filterCourse .= "AND e.course_id = {$courseId} ";
}
$filterUser = "";
if($userId > 0){
$filterUser .= "AND eu.user_id = {$userId} ";
}
$filterGroup = "";
if($groupId > 0){
$filterGroup .= "
INNER JOIN group_x_user AS gu
ON (
u.id = gu.user_id AND
gu.group_id = {$groupId}
)";
}
$filterTeacher = "";
if($teacherId > 0){
$filterTeacher .= "AND c.user_id = {$teacherId}
OR
( lm.id > 0 AND lm.user_id = {$teacherId} )
OR
( l.id > 0 AND l.user_id = {$teacherId} ) ";
}
$filterPeriod = "";
if(!is_null($dateStart) && !is_null($dateEnd)){
$filterPeriod .= "AND (
DATE(eu.date_start) BETWEEN '{$dateStart}' AND '{$dateEnd}'
) ";
}
$sqlExamWeight = "(
SELECT
SUM(ex.exam_weight)
FROM exam AS ex
WHERE ex.status = 1
AND ex.requirement = 1
AND ex.deleted = 0
AND ex.course_id = e.course_id
LIMIT 1
)";
$sqlDefault = "SELECT
u.id AS userId,
u.name AS userName,
u.email AS userEmail,
c.id AS courseId,
c.title AS courseTitle,
c.certificate_average AS mediaCourse,
e.title AS examTitle,
e.type as examType,
DATE_FORMAT(eu.date_start, '%Y-%m-%d %H:%i:%s') AS dateStart,
DATE_FORMAT(eu.date_end, '%Y-%m-%d %H:%i:%s') AS dateEnd,
ROUND(eu.grade, 2) AS grade,
eu.status AS userExamStatus,
eu.annotation AS userExamAnnotation,
ROUND((eu.grade * (e.exam_weight/{$sqlExamWeight})), 2) AS examPartial
FROM exam AS e
LEFT JOIN lesson_module AS lm
ON (lm.id = e.lesson_module_id AND lm.deleted = 0)
LEFT JOIN lesson AS l
ON (l.id = e.lesson_id AND l.deleted = 0)
INNER JOIN course AS c
ON (c.id = e.course_id AND c.deleted = 0 {$filterTeacher})
LEFT JOIN exam_user AS eu
ON (
eu.exam_id = e.id AND eu.deleted = 0 AND
(
eu.status = {$statusApproved}
OR eu.status = {$statusDisapproved}
)
AND eu.inactive = {$incative} {$filterUser}
)
INNER JOIN user AS u
ON (u.id = eu.user_id AND u.deleted = 0)
{$filterGroup}
WHERE e.deleted = 0 AND e.exam_weight > 0 {$filterCourse}
AND (
(
e.type = {$examModuleType}
AND lm.status = {$moduleStatus}
)
OR (
e.type = {$examLessonType}
AND l.status = {$lessonStatus}
AND lm.status = {$moduleStatus}
)
OR ( e.type = {$examCourseType} )
)
AND ( e.status = {$status} OR eu.id > 0 ) {$filterPeriod}
ORDER BY c.order ASC, u.id ASC, lm.order ASC, l.order ASC, e.type DESC";
$stmt = $this->em->getConnection()->prepare($sqlDefault);
$stmt->execute();
$data = $stmt->fetchAll(PDO::FETCH_CLASS);
return $data;
}
public function getStringStatus($status){
$string = '';
switch ($status) {
case ExamUserEnum::IN_PROGRESS:
$string = $this->configuration->getLanguage('in_progress', 'export');
break;
case ExamUserEnum::WAITING_CORRECTION:
$string = $this->configuration->getLanguage('waiting_correction', 'export');
break;
case ExamUserEnum::TIMEOUT:
$string = $this->configuration->getLanguage('time_out', 'export');
break;
case ExamUserEnum::APPROVED:
$string = $this->configuration->getLanguage('approved', 'export');
break;
case ExamUserEnum::DISAPPROVED:
$string = $this->configuration->getLanguage('disapproved', 'export');
break;
}
return $string;
}
public function restore(ExamUser $examUser, $typeItem)
{
$itemId = $examUser->getId();
$examUser->setUserDelete($this->getUser());
$examUser->setDateDelete(date('Y-m-d H:i:s'));
$examUser->restore();
$this->em->getRepository(Trash::class)->deleteTrash($itemId, $typeItem);
}
public function delete(ExamUser $examUser, $typeItem, $permission, $isTrash)
{
$userPermissionUtil = $this->generalService->getUtil('UserPermissionUtil');
if($isTrash == 1){
$permission = $userPermissionUtil->getPermission("trash", "delete");
}
$isExamTeacher = false;
if($this->getUser()){
$isExamTeacher = $this->em->getRepository(Exam::class)->isExamTeacher(
$examUser->getExam(),
$this->getUser()
);
}
if($isExamTeacher || $userPermissionUtil->isHigh($permission)){
$examUser->setUserDelete($this->getUser());
$examUser->setDateDelete(date('Y-m-d H:i:s'));
$examUser->individual();
if($examUser->isOnTrash() || $examUser->isDeleted()){
$examUser->delete();
$itemId = $examUser->getId();
$this->em->getRepository(Trash::class)->deleteTrash($itemId, $typeItem);
}
if(!$examUser->isOnTrash() && !$examUser->isDeleted()){
$examUser->trash();
$this->em->getRepository(Trash::class)->insertTrash(
$examUser,
$typeItem,
"Aluno: {$examUser->getUser()->getName()} | Exame: {$examUser->getExam()->getTitle()}"
);
}
}
$this->em->flush();
}
public function restoreByCourse($courseId, $deleted, $typeDelete, $userDelete, $dateNow){
$sql = "UPDATE EADPlataforma:ExamUser AS eu
SET eu.deleted = :deleted, eu.userDelete = :userDelete,
eu.dateDelete = :dateNow
WHERE eu.deleted = (1 - :deleted)
AND eu.typeDelete = :typeDelete
AND eu.course = :courseId";
$query = $this->em->createQuery($sql);
$query->setParameter('courseId', $courseId);
$query->setParameter('deleted', $deleted);
$query->setParameter('typeDelete', $typeDelete);
$query->setParameter('userDelete', $userDelete);
$query->setParameter('dateNow', $dateNow);
$query->execute();
return true;
}
public function deleteByCourse($courseId, $deleted, $typeDelete, $userDelete, $dateNow){
$sql = "UPDATE EADPlataforma:ExamUser AS eu
SET eu.deleted = :deleted, eu.typeDelete = :typeDelete,
eu.userDelete = :userDelete, eu.dateDelete = :dateNow
WHERE eu.deleted = (:deleted - 1)
AND eu.course = :courseId";
$query = $this->em->createQuery($sql);
$query->setParameter('courseId', $courseId);
$query->setParameter('deleted', $deleted);
$query->setParameter('typeDelete', $typeDelete);
$query->setParameter('userDelete', $userDelete);
$query->setParameter('dateNow', $dateNow);
$query->execute();
return true;
}
public function restoreByUser($userId, $deleted, $typeDelete, $userDelete, $dateNow){
$sql = "UPDATE EADPlataforma:ExamUser AS eu
SET eu.deleted = :deleted, eu.userDelete = :userDelete,
eu.dateDelete = :dateNow
WHERE eu.deleted = (1 - :deleted)
AND eu.typeDelete = :typeDelete
AND eu.user = :userId";
$query = $this->em->createQuery($sql);
$query->setParameter('userId', $userId);
$query->setParameter('deleted', $deleted);
$query->setParameter('typeDelete', $typeDelete);
$query->setParameter('userDelete', $userDelete);
$query->setParameter('dateNow', $dateNow);
$query->execute();
return true;
}
public function deleteByUser($userId, $deleted, $typeDelete, $userDelete, $dateNow){
$sql = "UPDATE EADPlataforma:ExamUser AS eu
SET eu.deleted = :deleted, eu.typeDelete = :typeDelete,
eu.userDelete = :userDelete, eu.dateDelete = :dateNow
WHERE eu.deleted = (:deleted - 1)
AND eu.user = :userId";
$query = $this->em->createQuery($sql);
$query->setParameter('userId', $userId);
$query->setParameter('deleted', $deleted);
$query->setParameter('typeDelete', $typeDelete);
$query->setParameter('userDelete', $userDelete);
$query->setParameter('dateNow', $dateNow);
$query->execute();
return true;
}
public function restoreByLessonModule($lessonModuleId, $deleted, $typeDelete, $userDelete, $dateNow){
$sql = "UPDATE EADPlataforma:ExamUser AS eu
SET eu.deleted = :deleted, eu.userDelete = :userDelete,
eu.dateDelete = :dateNow
WHERE IDENTITY(eu.exam) IN (SELECT e.id
FROM EADPlataforma:Exam AS e
WHERE e.lessonModule = :lessonModuleId )
AND eu.deleted = (1 - :deleted)
AND eu.typeDelete = :typeDelete";
$query = $this->em->createQuery($sql);
$query->setParameter('lessonModuleId', $lessonModuleId);
$query->setParameter('deleted', $deleted);
$query->setParameter('typeDelete', $typeDelete);
$query->setParameter('userDelete', $userDelete);
$query->setParameter('dateNow', $dateNow);
$query->execute();
return true;
}
public function deleteByLessonModule($lessonModuleId, $deleted, $typeDelete, $userDelete, $dateNow){
$sql = "UPDATE EADPlataforma:ExamUser AS eu
SET eu.deleted = :deleted, eu.typeDelete = :typeDelete,
eu.userDelete = :userDelete, eu.dateDelete = :dateNow
WHERE IDENTITY(eu.exam) IN (SELECT e.id
FROM EADPlataforma:Exam AS e
WHERE e.lessonModule = :lessonModuleId )
AND eu.deleted = (:deleted - 1)";
$query = $this->em->createQuery($sql);
$query->setParameter('lessonModuleId', $lessonModuleId);
$query->setParameter('deleted', $deleted);
$query->setParameter('typeDelete', $typeDelete);
$query->setParameter('userDelete', $userDelete);
$query->setParameter('dateNow', $dateNow);
$query->execute();
return true;
}
public function restoreByExamUser($examUserId, $deleted, $typeDelete, $userDelete, $dateNow){
$sql = "UPDATE EADPlataforma:ExamUser AS eu
SET eu.deleted = :deleted, eu.userDelete = :userDelete,
eu.dateDelete = :dateNow
WHERE eu.deleted = (1 + :deleted)
AND eu.typeDelete = :typeDelete
AND eu.id = :examUserId";
$query = $this->em->createQuery($sql);
$query->setParameter('examUserId', $examUserId);
$query->setParameter('deleted', $deleted);
$query->setParameter('typeDelete', $typeDelete);
$query->setParameter('userDelete', $userDelete);
$query->setParameter('dateNow', $dateNow);
$query->execute();
return true;
}
public function deleteByExamUser($examUserId, $deleted, $typeDelete, $userDelete, $dateNow){
$sql = "
UPDATE EADPlataforma:ExamUser AS eu
SET
eu.deleted = :deleted,
eu.typeDelete = :typeDelete,
eu.userDelete = :userDelete,
eu.dateDelete = :dateNow
WHERE eu.deleted = (:deleted - 1)
AND eu.id = :examUserId
";
$query = $this->em->createQuery($sql);
$query->setParameter('examUserId', $examUserId);
$query->setParameter('deleted', $deleted);
$query->setParameter('typeDelete', $typeDelete);
$query->setParameter('userDelete', $userDelete);
$query->setParameter('dateNow', $dateNow);
$query->execute();
return true;
}
public function restoreByLesson($lessonId, $deleted, $typeDelete, $userDelete, $dateNow){
$sql = "UPDATE EADPlataforma:ExamUser AS eu
SET eu.deleted = :deleted, eu.userDelete = :userDelete,
eu.dateDelete = :dateNow
WHERE IDENTITY(eu.exam) IN (SELECT e.id
FROM EADPlataforma:Exam AS e
WHERE e.lesson = :lessonId )
AND eu.deleted = (1 - :deleted)
AND eu.typeDelete = :typeDelete";
$query = $this->em->createQuery($sql);
$query->setParameter('lessonId', $lessonId);
$query->setParameter('deleted', $deleted);
$query->setParameter('typeDelete', $typeDelete);
$query->setParameter('userDelete', $userDelete);
$query->setParameter('dateNow', $dateNow);
$query->execute();
return true;
}
public function deleteByLesson($lessonId, $deleted, $typeDelete, $userDelete, $dateNow){
$sql = "UPDATE EADPlataforma:ExamUser AS eu
SET eu.deleted = :deleted, eu.typeDelete = :typeDelete,
eu.userDelete = :userDelete, eu.dateDelete = :dateNow
WHERE IDENTITY(eu.exam) IN (SELECT e.id
FROM EADPlataforma:Exam AS e
WHERE e.lesson = :lessonId )
AND eu.deleted = (:deleted - 1)";
$query = $this->em->createQuery($sql);
$query->setParameter('lessonId', $lessonId);
$query->setParameter('deleted', $deleted);
$query->setParameter('typeDelete', $typeDelete);
$query->setParameter('userDelete', $userDelete);
$query->setParameter('dateNow', $dateNow);
$query->execute();
return true;
}
public function deleteTrashCron()
{
$sql = "UPDATE EADPlataforma:ExamUser AS eu SET eu.deleted = 2
WHERE eu.dateDelete <= :date AND eu.deleted = 1 ";
$query = $this->em->createQuery($sql);
$query->setParameter('date', date('Y-m-d H:i:s', strtotime('-90 days')));
$query->execute();
}
public function findExamUserExpired()
{
$query = $this->createQueryBuilder('eu');
$query->andWhere('eu.status = :status');
$query->andWhere('DATE(:currentDate) > DATE(eu.dateStart)');
$query->andWhere('eu.inactive = :inactive');
$query->setParameter('status', ExamUserEnum::IN_PROGRESS);
$query->setParameter('currentDate', date('Y-m-d H:i:s'));
$query->setParameter('inactive', ExamUserEnum::NO);
return $query->getQuery()->getResult();
}
public function expiredExams()
{
$examsUsers = $this->findExamUserExpired();
foreach ($examsUsers as $examUser) {
$this->examUserIsExpired($examUser);
}
}
}