<?php
namespace EADPlataforma\Repository;
use EADPlataforma\Entity\Course;
use EADPlataforma\Entity\CourseCertificate;
use EADPlataforma\Entity\CourseTeam;
use EADPlataforma\Entity\LessonModule;
use EADPlataforma\Entity\Enrollment;
use EADPlataforma\Entity\CourseCertificateTemplate;
use EADPlataforma\Entity\User;
use EADPlataforma\Entity\UserCustomField;
use EADPlataforma\Entity\Trash;
use EADPlataforma\Enum\CourseCertificateEnum;
use EADPlataforma\Enum\CourseTeamEnum;
use EADPlataforma\Enum\EnrollmentEnum;
use EADPlataforma\Enum\ServicesEnum;
use EADPlataforma\Enum\UserCustomFieldEnum;
use EADPlataforma\Enum\LessonModuleEnum;
use EADPlataforma\Enum\TrashEnum;
use EADPlataforma\Enum\TagsMarketingEnum;
use EADPlataforma\Enum\WebhookEnum;
use EADPlataforma\Enum\NotificationEnum;
use EADPlataforma\Enum\UserEnum;
use \PDO;
/**
* @method CourseCertificate|null find($id, $lockMode = null, $lockVersion = null)
* @method CourseCertificate|null findOneBy(array $criteria, array $orderBy = null)
* @method CourseCertificate[] findAll()
* @method CourseCertificate[] findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
*/
class CourseCertificateRepository extends AbstractRepository
{
public function getEntityClass(){
return CourseCertificate::class;
}
public function getConnectionName(){
return "school";
}
public function findItems()
{
return $this->createQueryBuilder('cc')->getQuery()->getResult();
}
public function getCourseCertificateByCode(?string $code = null, ?string $date = null)
{
if(empty($code) || empty($date)){
return;
}
$courseCertificate = $this->getCourseCertificateByNewCode($code, $date);
if(!$courseCertificate){
$courseCertificate = $this->getCourseCertificateByOldCode($code, $date);
}
return $courseCertificate;
}
public function getCourseCertificateByOldCode(?string $code = null, ?string $date = null)
{
if(empty($code) || empty($date)){
return;
}
$query = $this->createQueryBuilder('cc');
$query->andWhere('cc.deleted = :deleted');
$query->andWhere('DATE(cc.dateEnd) = :date');
$codeInt = (int)$code;
if(empty($codeInt) || !ctype_digit($code)){
$query->setParameter('code', $code);
$query->andWhere('cc.code = :code');
}else{
$query->setParameter('code', $code);
$query->setParameter('codeInt', (int)$code);
$query->andWhere('(cc.code = :code OR cc.code = :codeInt)');
}
$userPermissionUtil = $this->generalService->getUtil('UserPermissionUtil');
$isAdm = $userPermissionUtil->canAccessAdm();
if($this->getUser() && !$isAdm){
$query->setParameter('user', $this->getUser()->getId());
$query->andWhere('cc.user = :user');
}
$query->setParameter('date', $date);
$query->setParameter('deleted', CourseCertificateEnum::ITEM_NO_DELETED);
$query->setMaxResults(1);
return $query->getQuery()->getOneOrNullResult();
}
public function getCourseCertificateByNewCode(?string $code = null, ?string $date = null)
{
if(empty($code) || empty($date)){
return;
}
$query = $this->createQueryBuilder('cc');
$query->andWhere('cc.deleted = :deleted');
$query->andWhere('DATE(cc.dateEnd) = :date');
$codeInt = (int)$code;
if(empty($codeInt) || !ctype_digit($code)){
$query->setParameter('code', $code);
$query->andWhere('cc.newCode = :code');
}else{
$query->setParameter('code', $code);
$query->setParameter('codeInt', (int)$code);
$query->andWhere('(cc.newCode = :code OR cc.newCode = :codeInt)');
}
$userPermissionUtil = $this->generalService->getUtil('UserPermissionUtil');
$isAdm = $userPermissionUtil->canAccessAdm();
if($this->getUser() && !$isAdm){
$query->setParameter('user', $this->getUser()->getId());
$query->andWhere('cc.user = :user');
}
$query->setParameter('date', $date);
$query->setParameter('deleted', CourseCertificateEnum::ITEM_NO_DELETED);
$query->setMaxResults(1);
return $query->getQuery()->getOneOrNullResult();
}
public function getCourseCertificateNumber(
?string $dateStart = null,
?string $dateEnd = null
)
{
$query = $this->createQueryBuilder('cc');
$query->select("COUNT(0) AS total");
$query->andWhere('cc.deleted = :deleted');
if(!is_null($dateStart) && !is_null($dateEnd)){
$query->andWhere('DATE(cc.dateIssue) BETWEEN :dateStart AND :dateEnd');
$query->setParameter('dateStart', $dateStart);
$query->setParameter('dateEnd', $dateEnd);
}
$query->setParameter('deleted', CourseCertificateEnum::ITEM_NO_DELETED);
$query->setMaxResults(1);
$result = (object)$query->getQuery()->getOneOrNullResult();
return $result->total;
}
public function getCourseCertificateMaxCode()
{
$sql = "
SELECT
MAX(cc.id) AS max
FROM course_certificate AS cc
";
$stmt = $this->em->getConnection()->prepare($sql);
$stmt->execute();
$data = $stmt->fetchAll(PDO::FETCH_CLASS);
if(!empty($data[0])){
return $data[0]->max;
}
return $this->count([]);
}
public function generateCertificateCode(?int $certificateNumber = null)
{
$code = null;
if($this->configuration->get('certificate_random_code') == CourseCertificateEnum::YES){
$stringUtil = $this->generalService->getUtil('StringUtil');
$code = $stringUtil->randomText(6);
}else{
if(empty($certificateNumber)){
$certificateNumber = $this->getCourseCertificateMaxCode() + 1;
}
$code = str_pad($certificateNumber, 7, '0', STR_PAD_LEFT);
}
return $code;
}
public function createCertificate(
Enrollment $enrollment,
CourseCertificateTemplate $courseCertificateTemplate,
float $finalGrade,
?string $dateStart = null,
?string $dateEnd = null
)
{
$course = $enrollment->getCourse();
$user = $enrollment->getUser();
$courseCertificate = $this->findOneBy([
"course" => $course->getId(),
"user" => $user->getId(),
]);
if($courseCertificate && !$courseCertificate->isDeleted()){
return (object)[
"errors" => false,
"courseCertificate" => $courseCertificate,
];
}
$courseCertificate = new CourseCertificate();
if($finalGrade > 10){
$finalGrade = 10;
}else if($finalGrade < 0){
$finalGrade = 0;
}
if(empty($dateEnd)){
$dateEnd = date('Y-m-d H:i:s');
}
if(empty($dateStart)){
$dateStart = $enrollment->getDateStart();
$minPeriod = $course->getConfigPeriodMinConclusion();
if(!empty($minPeriod)){
$timeStart = strtotime("{$dateStart} + {$minPeriod} days ");
$timeEnd = strtotime($dateEnd);
if($timeStart > $timeEnd){
$dateStart = date(
'Y-m-d H:i:s',
strtotime("{$dateEnd} - {$minPeriod} days ")
);
}
}
}
$dateExpired = null;
if(!empty($course->getCertificatePeriod())){
$dateExpired = date("Y-m-d", strtotime("{$dateEnd} + {$course->getCertificatePeriod()} days"));
}
$courseCertificate->setNewCode($this->generateCertificateCode());
$courseCertificate->setFinalAverage($finalGrade);
$courseCertificate->setDateStart($dateStart);
$courseCertificate->setDateEnd($dateEnd);
$courseCertificate->setWorkload($course->getWorkload());
$courseCertificate->setUserName($user->getName());
$courseCertificate->setUserEmail($user->getEmail());
$courseCertificate->setUserDocument($user->getDocument());
$courseCertificate->setDateExpired($dateExpired);
$frontJson = null;
$backJson = null;
if($courseCertificateTemplate)
{
$courseCertificate->setCourseCertificateTemplate($courseCertificateTemplate);
$frontJson = $courseCertificateTemplate->getFrontDefaultJson();
$backJson = $courseCertificateTemplate->getBackDefaultJson();
}else{
$courseCertificate->setCourseCertificateTemplate(
$course->getCourseCertificateTemplate()
);
$frontJson = $course->getCourseCertificateTemplate()->getFrontDefaultJson();
$backJson = $course->getCourseCertificateTemplate()->getBackDefaultJson();
}
$stringUtil = $this->generalService->getUtil('StringUtil');
$frontJson = $stringUtil->characterCorrector($frontJson);
$backJson = $stringUtil->characterCorrector($backJson);
$courseCertificate->setFrontJson($frontJson);
$courseCertificate->setBackJson($backJson);
$courseCertificate->setCourse($course);
$courseCertificate->setUser($user);
$entityUtil = $this->generalService->getUtil('EntityUtil');
$errors = $entityUtil->setEntity($courseCertificate)->validateEntity();
if($errors){
return (object)[
"errors" => $errors,
"courseCertificate" => null,
];
}
$this->em->persist($courseCertificate);
$this->em->flush();
$this->processCertificate($courseCertificate, true);
return (object)[
"errors" => false,
"courseCertificate" => $courseCertificate,
];
}
public function processCertificate(
CourseCertificate $courseCertificate,
?bool $sendEmail = false
)
{
$course = $courseCertificate->getCourse();
$userFrom = $course->getUser();
$userTo = $courseCertificate->getUser();
$marketingService = $this->generalService->getService('Marketing\\MarketingService');
$marketingService->setTag(TagsMarketingEnum::TAG_ISSUE_CERTIFICATE);
$marketingService->setTextComplement($course->getTitle());
$marketingService->setUser($userTo);
$marketingService->send();
$notificationService = $this->generalService->getService('NotificationService');
$notificationService->create(
$userFrom,
$userTo,
NotificationEnum::ORIGIN_COURSE_CERTIFICATE_ISSUE,
$courseCertificate->getId()
);
$code = $courseCertificate->getNewCode() ?? $courseCertificate->getCode();
if($sendEmail){
$client = $this->configuration->getClient();
$emailService = $this->generalService->getService('EmailService');
if($emailService->checkUserToSend($userTo)){
$emailService->setToEmail($userTo->getEmail());
$emailService->setToName($userTo->getName());
$subText = $this->configuration->getLanguage('certificate.subject', 'email');
$subject = "{$subText} - {$client->getBrand()}";
$emailService->setSubject($subject);
$domain = "https://{$client->getDomainPrimary()}";
$dateEnd = $courseCertificate->getDateEnd('Y-m-d');
$emailService->setData([
"userName" => $userTo->getName(),
"courseTitle" => $course->getTitle(),
"certificateCode" => $code,
"btnLink" => "{$domain}/certificate/view/{$code}/{$dateEnd}",
]);
$emailService->setTemplateBody("certificate");
$emailService->send();
}
}
$enrollmentRepository = $this->em->getRepository(Enrollment::class);
$enrollment = $enrollmentRepository->findOneBy([
"user" => $userTo,
"course" => $course,
]);
$userWebhook = $this->em->getRepository(User::class)->getToWebhook($userTo);
$dataObj = (object)[
"user" => $userWebhook,
"course" => (object)[
"id" => (string)$course->getId(),
"name" => $course->getTitle(),
"certificate_average" => $course->getCertificateAverage(),
],
"certificate" => (object)[
"id" => (string)$courseCertificate->getId(),
"code" => $code,
"final_average" => $courseCertificate->getFinalAverage(),
"workload" => $courseCertificate->getWorkload(),
"dates" => (object)[
"start" => $courseCertificate->getDateStart(),
"end" => $courseCertificate->getDateEnd(),
],
],
];
if($enrollment){
$dataObj->enrollment = $enrollment->toWebhook();
}
$webhookService = $this->generalService->getService('WebhookService');
$webhookService->addItemList(WebhookEnum::CERTIFICATE, $dataObj);
}
public function generateCertificate(
CourseCertificate $courseCertificate,
bool $download = false
){
// $courseCertificateTemplate = $courseCertificate->getCourseCertificateTemplate();
$dataNames = [
"aluno_nome",
"aluno_email",
"aluno_cpf",
"aluno_telefone",
"aluno_data_nascimento",
"aluno_cidade",
"aluno_uf",
"aluno_frequencia",
"aluno_progresso",
"cod",
"certificado_nota",
"certificado_dia_inicio",
"certificado_dia_fim",
"certificado_data_validade",
"matricula_dia_cadastro",
"certificado_carga_horaria",
"qrcode_frente",
"qrcode_verso",
"curso_titulo",
"curso_professor",
"curso_modulos",
"curso_modulos_carga_horaria",
"nome_responsavel",
"cargo",
"atribuicao",
"ead_nome",
"ead_cnpj",
"ead_dominio",
];
if($this->configuration->checkModuleIsAbleOnPlan('customFormModule')){
$userCustomFieldRepository = $this->em->getRepository(UserCustomField::class);
$customFields = $userCustomFieldRepository->getUserCustomFieldsToShow();
foreach ($customFields as $key => $field) {
$dataNames[] = "campo_{$field->getId()}";
}
}
$frontJson = $courseCertificate->getFrontJson();
$backJson = $courseCertificate->getBackJson();
foreach ($dataNames as $key => $value) {
$frontJson = str_replace("[[ {$value} ]]", "{{ {$value}|raw }}", $frontJson);
$backJson = str_replace("[[ {$value} ]]", "{{ {$value}|raw }}", $backJson);
$frontJson = str_replace("[[{$value}]]", "{{ {$value}|raw }}", $frontJson);
$backJson = str_replace("[[{$value}]]", "{{ {$value}|raw }}", $backJson);
}
$frontObj = json_decode($frontJson);
$backObj = json_decode($backJson);
if(!empty($frontObj->objects)){
$auxObj = [];
foreach ($frontObj->objects as $key => $objCanvas) {
if(!empty($objCanvas)){
$obj = $this->prepareCanvasObj($objCanvas);
if(!empty($obj)){
$auxObj[] = $obj;
}
}
}
$frontObj->objects = $auxObj;
}
if(!empty($backObj->objects)){
$auxObj = [];
foreach ($backObj->objects as $key => $objCanvas) {
if(!empty($objCanvas)){
$obj = $this->prepareCanvasObj($objCanvas);
if(!empty($obj)){
$auxObj[] = $obj;
}
}
}
$backObj->objects = $auxObj;
}
$frontImage = null;
if(!empty($frontObj->backgroundImage)){
$frontImage = $this->prepareCanvasImage($frontObj->backgroundImage->src);
}
$backImage = null;
if(!empty($backObj->backgroundImage)){
$backImage = $this->prepareCanvasImage($backObj->backgroundImage->src);
}
$data = [
"frontColor" => (!empty($frontObj->background) ? $frontObj->background : null),
"backColor" => (!empty($backObj->background) ? $backObj->background : null),
"frontObjects" => (!empty($frontObj->objects) ? $frontObj->objects : []),
"backObjects" => (!empty($backObj->objects) ? $backObj->objects : []),
"frontImage" => $frontImage,
"backImage" => $backImage,
];
$dataInfo = $this->prepareDataToPdf($courseCertificate);
$data = array_merge($data, $dataInfo);
$pdfService = $this->generalService->getService('PdfService');
$pdfService->setFileName($courseCertificate->getNewCode() ?? $courseCertificate->getCode());
$pdfService->setTemplateBody("certificate");
$pdfService->setData($data);
return $pdfService->generate(true, $download);
}
public function prepareDataToPdf(CourseCertificate $courseCertificate)
{
$user = $courseCertificate->getUser();
$course = $courseCertificate->getCourse();
$cityName = ( $user->getCity() ? $user->getCity()->getName() : '' );
$stateUf = ( $user->getCity() ? $user->getCity()->getState()->getName() : '' );
$client = $this->configuration->getClient();
$schoolName = $client->getBrand();
$schoolDocument = $client->getDocument();
$schoolDomain = $this->configuration->getActiveDomain();
$filter = [
"course" => $course,
"deleted" => CourseCertificateEnum::ITEM_NO_DELETED,
"status" => LessonModuleEnum::PUBLISHED
];
$lessonModules = $this->em->getRepository(LessonModule::class)->findBy(
$filter, [ "order" => "ASC" ]
);
$lessonModulesText = '';
$lessonModulesWorkload = '';
foreach ($lessonModules as $key => $lessonModule) {
$lessonModulesText .= "{$lessonModule->getOrder()}. {$lessonModule->getTitle()}</br>";
$lessonModulesWorkload .= "{$lessonModule->getOrder()}. {$lessonModule->getWorkload()}</br>";
}
$dateTimeUtil = $this->generalService->getUtil('DateTimeUtil');
$configuration = $this->generalService->getService('ConfigurationService');
$urlCertView = $this->generalService->generateUrl('certificatePage', [
"code" => $courseCertificate->getNewCode() ?? $courseCertificate->getCode(),
"date" => $courseCertificate->getDateEnd('Y-m-d')
]);
$urlView = "https://{$schoolDomain}{$urlCertView}";
$qrCodeFrente = "https://api.ead.dev/qrcode?data={$urlView}&w=130&h=130";
$qrCodeVerso = "https://api.ead.dev/qrcode?data={$urlView}&w=300&h=300";
$enrollment = $this->em->getRepository(Enrollment::class)->findOneBy([
"deleted" => EnrollmentEnum::ITEM_NO_DELETED,
"user" => $user->getId(),
"course" => $course->getId(),
]);
$data = [
"aluno_nome" => mb_strtoupper($courseCertificate->getUserName()),
"aluno_email" => $courseCertificate->getUserEmail(),
"aluno_cpf" => $courseCertificate->getUserDocument(),
"aluno_telefone" => $user->getPhone(),
"aluno_data_nascimento" => $user->getBirthdate('d/m/Y'),
"aluno_cidade" => $cityName,
"aluno_uf" => $stateUf,
"aluno_frequencia" => '100%',
"aluno_progresso" => '100%',
"cod" => $courseCertificate->getNewCode() ?? $courseCertificate->getCode(),
"certificado_nota" => $courseCertificate->getFinalAverage(),
"certificado_dia_inicio" => $courseCertificate->getDateStart('d/m/Y'),
"certificado_dia_fim" => $courseCertificate->getDateEnd('d/m/Y'),
"certificado_data_validade" => $courseCertificate->getDateExpired('d/m/Y'),
"matricula_dia_cadastro" => ($enrollment ? $enrollment->getDateRegister('d/m/Y') : ''),
"certificado_carga_horaria" => ($courseCertificate->getWorkload() ? $dateTimeUtil->workloadFormat(
$courseCertificate->getWorkload(),
true,
$configuration->getLanguageModule('util')
) : ''),
"qrcode_frente" => $qrCodeFrente,
"qrcode_verso" => $qrCodeVerso,
"curso_titulo" => $course->getTitle(),
"curso_professor" => $course->getUser()->getName(),
"curso_modulos" => $lessonModulesText,
"curso_modulos_carga_horaria" => $lessonModulesWorkload,
"nome_responsavel" => $course->getUser()->getName(),
"cargo" => $course->getUser()->getUserProfile()->getName(),
"atribuicao" => $schoolName,
"ead_nome" => $schoolName,
"ead_cnpj" => $schoolDocument,
"ead_dominio" => $schoolDomain,
];
if($this->configuration->checkModuleIsAbleOnPlan('customFormModule')){
$userCustomFieldRepository = $this->em->getRepository(UserCustomField::class);
$customFields = $userCustomFieldRepository->getUserCustomFieldsToShow();
foreach ($customFields as $key => $field) {
$data["campo_{$field->getId()}"] = "";
}
$userCustomField = $user->getCustomField();
if(!empty($userCustomField)){
$userCustomField = json_decode($userCustomField);
foreach ($userCustomField as $key => $field) {
if(!empty($field->fieldId)){
if($field->type != UserCustomFieldEnum::FIELD_MULTIPLE){
if($field->type != UserCustomFieldEnum::FIELD_FILE){
if(isset($data["campo_{$field->fieldId}"])){
$data["campo_{$field->fieldId}"] = null;
if(isset($field->value)){
$data["campo_{$field->fieldId}"] = $field->value;
}
}
}
}
}
}
}
}
return $data;
}
public function prepareCanvasObj($obj)
{
$obj = (object)$obj;
if(empty($obj)){
return;
}
if(isset($obj->type)){
if($obj->type == 'textbox'){
if(strpos($obj->text, 'qrcode_frente')){
$obj->src = $obj->text;
$obj->type = "image";
$obj->width = 100;
$obj->height = 100;
}else if(strpos($obj->text, 'qrcode_verso')){
$obj->src = $obj->text;
$obj->type = "image";
$obj->width = 200;
$obj->height = 200;
}
$obj->text = htmlspecialchars_decode($obj->text);
$obj->text = str_replace("\n", "</br>", $obj->text);
}else if($obj->type == 'image'){
$obj->src = $this->prepareCanvasImage($obj->src);
}
}else{
$obj->type = 'textbox';
}
$obj->top = (isset($obj->top) ? str_replace(',', '.', $obj->top) : 0);
$obj->left = (isset($obj->left) ? str_replace(',', '.', $obj->left) : 0);
$obj->width = (isset($obj->width) ? str_replace(',', '.', $obj->width) : 0);
$obj->height = (isset($obj->height) ? str_replace(',', '.', $obj->height) : 0);
$obj->scaleX = (isset($obj->scaleX) ? str_replace(',', '.', $obj->scaleX) : 0);
$obj->scaleY = (isset($obj->scaleY) ? str_replace(',', '.', $obj->scaleY) : 0);
if(isset($obj->fontSize)){
$obj->fontSize = str_replace(',', '.', $obj->fontSize);
$obj->fontSize = (int)$obj->fontSize * $obj->scaleY;
}else{
$obj->fontSize = 12 * $obj->scaleY;
}
$obj->width = $obj->width * $obj->scaleX;
$obj->height = $obj->height * $obj->scaleY;
if(empty($obj->flipX)){
$obj->flipX = 1;
}else{
$obj->flipX = -1;
}
if(empty($obj->flipY)){
$obj->flipX = 1;
}else{
$obj->flipY = -1;
}
return $obj;
}
public function prepareCanvasImage($image)
{
$info = $this->generalService->getServiceAccess(ServicesEnum::AWS_S3);
$clientConnection = $this->configuration->getClientConnection();
$serverUser = $clientConnection->getServerUser();
$image = explode('upload/', $image);
$image = end($image);
$imageUrl = "https:{$info->cdnLink}client/{$serverUser}/upload/{$image}";
return $imageUrl;
}
public function export(?int $courseId = null, ?int $userId = null,
$dateStart = null, $dateEnd = null, $isPermissionMiddle)
{
if(is_null($userId)){
$userId = $this->getUser()->getId();
}
$query = $this->createQueryBuilder('cc');
$query->innerJoin('EADPlataforma:User', 'u', 'WITH', 'u.id = cc.user');
$query->innerJoin('EADPlataforma:Course', 'c', 'WITH', 'c.id = cc.course');
$courseTeamClass = CourseTeam::class;
if($isPermissionMiddle){
$subQuery = "SELECT (cTeam.course) FROM $courseTeamClass cTeam WHERE cTeam.deleted = 0 AND cTeam.user = $userId";
$query->andWhere("c.id IN ($subQuery)");
} else {
}
$query->andWhere('cc.deleted = :deleted');
$query->setParameter('deleted', CourseCertificateEnum::NO);
$query->andWhere('u.status != :userStatus');
$query->setParameter('userStatus', UserEnum::INACTIVE);
if(!is_null($dateStart) && !is_null($dateEnd)){
$query->andWhere('DATE(cc.dateIssue) BETWEEN :dateStart AND :dateEnd');
$query->setParameter('dateStart', $dateStart);
$query->setParameter('dateEnd', $dateEnd);
}
if(!empty($courseId)){
$query->andWhere('cc.course = :course')->setParameter('course', $courseId);
}
return $query->getQuery()->execute();
}
public function getResumeCourseCertificate($userId, $offset = 0, $searchText = null,
?string $categories = null)
{
$query = $this->createQueryBuilder('cc');
$query->innerJoin('EADPlataforma:Course', 'c', 'WITH', 'cc.course = c');
$query->andWhere('cc.user = :userId');
$query->andWhere('cc.deleted = :deleted');
if(!empty($searchText)){
$query->andWhere('c.title LIKE :searchText OR cc.code LIKE :searchText');
$query->setParameter('searchText', '%'.$searchText.'%');
}
if(!empty($categories)){
$query->andWhere('c.category IN (:categories) ');
$query->setParameter('categories', $categories);
}
$query->setParameter('userId', $userId);
$query->setParameter('deleted', CourseCertificateEnum::NO);
$query->setFirstResult($offset);
$query->setMaxResults(10);
$query->addOrderBy('cc.dateEnd', 'DESC');
$data = $query->getQuery()->execute();
return $data;
}
public function getCountResumeCourseCertificate($userId, $searchText = null,
?string $categories = null)
{
$query = $this->createQueryBuilder('cc');
$query->select("count(cc.id) AS total");
$query->innerJoin('EADPlataforma:Course', 'c', 'WITH', 'cc.course = c');
$query->andWhere('cc.user = :userId');
$query->andWhere('cc.deleted = :deleted');
if(!empty($searchText)){
$query->andWhere('c.title LIKE :searchText OR cc.code LIKE :searchText');
$query->setParameter('searchText', '%'.$searchText.'%');
}
if(!empty($categories)){
$query->andWhere('c.category IN (:categories) ');
$query->setParameter('categories', $categories);
}
$query->setParameter('userId', $userId);
$query->setParameter('deleted', CourseCertificateEnum::NO);
$query->addOrderBy('cc.dateEnd', 'DESC');
$result = (object)$query->getQuery()->getOneOrNullResult();
return $result->total;
}
public function restore(CourseCertificate $courseCertificate, $typeItem)
{
$itemId = $courseCertificate->getId();
$courseCertificate->setUserDelete($this->getUser());
$courseCertificate->setDateDelete(date('Y-m-d H:i:s'));
$courseCertificate->restore();
$this->em->getRepository(Trash::class)->deleteTrash($itemId, $typeItem);
}
public function delete(CourseCertificate $courseCertificate, $typeItem, $permission, $isTrash, $isApi = false)
{
$userPermissionUtil = $this->generalService->getUtil('UserPermissionUtil');
if($isTrash == 1){
$permission = $userPermissionUtil->getPermission("trash", "delete");
}
$isInTeam = false;
$course = $courseCertificate->getCourse();
if($this->getUser()){
$isInTeam = $this->em->getRepository(CourseTeam::class)->userExistInCourseTeam(
$course,
$this->getUser()
);
}
if($isInTeam || $userPermissionUtil->isHigh($permission)){
$courseCertificate->setUserDelete($this->getUser());
$courseCertificate->setDateDelete(date('Y-m-d H:i:s'));
$courseCertificate->individual();
if($courseCertificate->isOnTrash() || $courseCertificate->isDeleted() || $isApi){
$courseCertificate->delete();
$itemId = $courseCertificate->getId();
$this->em->getRepository(Trash::class)->deleteTrash($itemId, $typeItem);
}
if(!$courseCertificate->isOnTrash() && !$courseCertificate->isDeleted()){
$courseCertificate->trash();
$this->em->getRepository(Trash::class)->insertTrash(
$courseCertificate,
$typeItem,
'COD: '.$courseCertificate->getNewCode().' - '.$courseCertificate->getUserName() ??
$courseCertificate->getCode().' - '.$courseCertificate->getUserName()
);
}
}
$this->em->flush();
}
public function restoreByUser($userId, $deleted, $typeDelete, $userDelete, $dateNow){
$sql = "UPDATE EADPlataforma:CourseCertificate AS cc
SET cc.deleted = :deleted, cc.userDelete = :userDelete,
cc.dateDelete = :dateNow
WHERE cc.deleted = (1 - :deleted)
AND cc.typeDelete = :typeDelete
AND cc.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:CourseCertificate AS cc
SET cc.deleted = :deleted, cc.typeDelete = :typeDelete,
cc.userDelete = :userDelete, cc.dateDelete = :dateNow
WHERE cc.deleted = (:deleted - 1)
AND cc.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 deleteTrashCron()
{
$sql = "UPDATE EADPlataforma:CourseCertificate AS cc SET cc.deleted = 2
WHERE cc.dateDelete <= :date AND cc.deleted = 1 ";
$query = $this->em->createQuery($sql);
$query->setParameter('date', date('Y-m-d H:i:s', strtotime('-90 days')));
$query->execute();
}
public function updateTemplateOnCertificate(CourseCertificateTemplate $courseCertificateTemplate)
{
$sql = "UPDATE EADPlataforma:CourseCertificate AS cc
SET cc.frontJson = :frontJson, cc.backJson = :backJson
WHERE cc.deleted = :deleted AND cc.courseCertificateTemplate = :id";
$query = $this->em->createQuery($sql);
$query->setParameter('frontJson', $courseCertificateTemplate->getFrontDefaultJson());
$query->setParameter('backJson', $courseCertificateTemplate->getBackDefaultJson());
$query->setParameter('deleted', CourseCertificateEnum::ITEM_NO_DELETED);
$query->setParameter('id', $courseCertificateTemplate->getId());
$query->execute();
}
public function setCourseCertificate(array $params)
{
if (!empty($params['id'])){
$courseCertificate = $this->findOneBy(["id" => $params['id']]);
}
(isset($params['courseCertificateTemplate']) && $courseCertificate->setCourseCertificateTemplate($params['courseCertificateTemplate']));
(isset($params['course']) && $courseCertificate->setCourse($params['course']));
(isset($params['user']) && $courseCertificate->setUser($params['user']));
(isset($params['userName']) && $courseCertificate->setUserName($params['userName']));
(isset($params['userEmail']) && $courseCertificate->setUserEmail($params['userEmail']));
(isset($params['userDocument']) && $courseCertificate->setUserDocument($params['userDocument']));
(isset($params['frontJson']) && $courseCertificate->setFrontJson($params['frontJson']));
(isset($params['backJson']) && $courseCertificate->setBackJson($params['backJson']));
(isset($params['newCode']) && $courseCertificate->setNewCode($params['newCode']));
(isset($params['finalAverage']) && $courseCertificate->setFinalAverage($params['finalAverage']));
(isset($params['dateStart']) && $courseCertificate->setDateStart($params['dateStart']));
(isset($params['dateEnd']) && $courseCertificate->setDateEnd($params['dateEnd']));
(isset($params['dateExpired']) && $courseCertificate->setDateExpired($params['dateExpired']));
(isset($params['workload']) && $courseCertificate->setWorkload($params['workload']));
(isset($params['dateIssue']) && $courseCertificate->setDateIssue($params['dateIssue']));
return $courseCertificate;
}
public function setNewCourseCertificate(array $params)
{
$courseCertificate = new CourseCertificate();
(isset($params['courseCertificateTemplate']) && $courseCertificate->setCourseCertificateTemplate($params['courseCertificateTemplate']));
(isset($params['course']) && $courseCertificate->setCourse($params['course']));
(isset($params['user']) && $courseCertificate->setUser($params['user']));
(isset($params['userName']) && $courseCertificate->setUserName($params['userName']));
(isset($params['userEmail']) && $courseCertificate->setUserEmail($params['userEmail']));
(isset($params['userDocument']) && $courseCertificate->setUserDocument($params['userDocument']));
(isset($params['frontJson']) && $courseCertificate->setFrontJson($params['frontJson']));
(isset($params['backJson']) && $courseCertificate->setBackJson($params['backJson']));
(isset($params['newCode']) && $courseCertificate->setNewCode($params['newCode']));
(isset($params['finalAverage']) && $courseCertificate->setFinalAverage($params['finalAverage']));
(isset($params['dateStart']) && $courseCertificate->setDateStart($params['dateStart']));
(isset($params['dateEnd']) && $courseCertificate->setDateEnd($params['dateEnd']));
(isset($params['dateExpired']) && $courseCertificate->setDateExpired($params['dateExpired']));
(isset($params['workload']) && $courseCertificate->setWorkload($params['workload']));
(isset($params['dateIssue']) && $courseCertificate->setDateIssue($params['dateIssue']));
return $courseCertificate;
}
public function paginateCertificates(array $dataFilters)
{
$filter = [];
$userClass = User::class;
$courseClass = Course::class;
$courseTeamClass = CourseTeam::class;
$filter["whereText"] = " cc.id > 0 ";
$columnNotUseInFilter = [
"(
CASE
WHEN cc.newCode IS NOT NULL THEN cc.newCode
ELSE cc.code
END
) AS code",
];
$columns = [
"cc.id",
"cc.code AS oldCode",
"cc.newCode",
"u.name",
"u.email",
"c.title",
"DATE_FORMAT(cc.dateIssue, '%Y-%m-%d %H:%i:%s') AS dateIssue",
"DATE_FORMAT(cc.dateStart, '%Y-%m-%d %H:%i:%s') AS dateStart",
"DATE_FORMAT(cc.dateEnd, '%Y-%m-%d %H:%i:%s') AS dateEnd",
"DATE_FORMAT(cc.dateDelete, '%Y-%m-%d %H:%i:%s') AS dateDelete",
"ud.name AS userDelete"
];
$joins = [
"{$userClass} AS u" => "u.id = cc.user AND u.deleted = 0",
"{$courseClass} AS c" => "c.id = cc.course",
"{$userClass} AS ud" => ["LEFT", "ud.id = cc.userDelete"]
];
if(!empty($dataFilters['courseId'])){
$filter["cc.course"] = $dataFilters['courseId'];
}
if(!empty($dataFilters['userManager'])){
$filter["whereText"] .= "
AND c.id IN (
SELECT
IDENTITY(ct.course)
FROM {$courseTeamClass} AS ct
WHERE ct.deleted = 0
AND ct.user = '" . $dataFilters['userManager'] . "'
)
";
}
if(!empty($dataFilters['dateStart']) && !empty($dataFilters['dateEnd'])){
$filter["whereText"] .= " AND DATE_FORMAT(cc.dateStart, '%Y-%m-%d') BETWEEN '" . $dataFilters['dateStart'] . "' AND '" . $dataFilters['dateEnd'] . "'";
}
$order = [ "cc.id" => "DESC" ];
if(!empty($dataFilters['orderParam'])){
$order = json_decode($dataFilters['orderParam'], true);
}
return $this->paginate(
"cc",
$dataFilters['searchText'],
$columns,
$joins,
$filter,
$order,
$dataFilters['limit'],
$dataFilters['offset'],
$dataFilters['filterDelete'],
false,
$columnNotUseInFilter
);
}
public function getCourseCertificateReturn($courseCertificateId)
{
$courseCertificate = $this->findOneBy([
"id" => $courseCertificateId,
"deleted" => CourseCertificateEnum::ITEM_NO_DELETED
]);
return empty($courseCertificate) ? false : $courseCertificate->toReturn();
}
}