<?php
namespace App\Repository;
use App\Entity\FormationRegistration;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;
/**
* @method FormationRegistration|null find($id, $lockMode = null, $lockVersion = null)
* @method FormationRegistration|null findOneBy(array $criteria, array $orderBy = null)
* @method FormationRegistration[] findAll()
* @method FormationRegistration[] findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
*/
class FormationRegistrationRepository extends ServiceEntityRepository
{
public function __construct(ManagerRegistry $registry)
{
parent::__construct($registry, FormationRegistration::class);
}
/**
* Get one formation by user and formation ID
* @param $user
* @param $formationId
* @return int|mixed|string|null
* @throws \Doctrine\ORM\NonUniqueResultException
*/
public function userIsRegistered($user, $formationId)
{
$queryBuilder = $this->createQueryBuilder('f_r');
$query = $queryBuilder
->leftJoin('f_r.formation' , 'f')
->where('f_r.User = :user')->setParameter('user', $user)
->andWhere('f_r.formation = :formation')->setParameter('formation',$formationId)
->setMaxResults(1)
;
return $query->getQuery()->getOneOrNullResult();
}
/**
* Get all user formation query for datatable
* @param $user
* @return \Doctrine\ORM\QueryBuilder
*/
public function getAllRequiredBetweenDate($start,$end)
{
$queryBuilder = $this->createQueryBuilder('f_r');
$result = $queryBuilder
->select('f.id as formationId')
->addSelect('f.title as title')
->addSelect('f.items as channels')
->addSelect('o.email as authorEmail')
->addSelect('o.firstname as authorFirstname')
->addSelect(['u.lastname','u.firstname','u.email'])
->addSelect(['u.id as userId'])
->addSelect('f_r.id as formationRegistrationId')
->leftJoin('f_r.formation' , 'f')
->leftJoin('f_r.User' , 'u')
->leftJoin('f.owner' , 'o')
->andWhere('f_r.createdAt BETWEEN :start AND :end')
->setParameter('start', $start)
->setParameter('end', $end)
->andWhere('f.published = :published')->setParameter('published', 1)
->andWhere('f.notify = :notify')->setParameter('notify', 1)
->andWhere('f_r.viewed = :viewed')->setParameter('viewed', 0)
->getQuery()
->getResult();
return $result;
}
/**
* Get all user formation query for datatable
* @param $user
* @return \Doctrine\ORM\QueryBuilder
*/
public function getAllFormationsQueryByUser($user)
{
$queryBuilder = $this->createQueryBuilder('f_r');
$query = $queryBuilder
->select('f.id')
->addSelect('f.title as title')
->addSelect('f.notify as required')
->addSelect('f.updatedAt as lastUpdate')
->addSelect('o.firstname as ownerFirstname')
->addSelect('o.lastname as ownerLastname')
->addSelect('r.name as role')
->addSelect('f.slug as slug')
->addSelect('f_r.viewed')
->addSelect('d.name as service')
->leftJoin('f_r.formation' , 'f')
->leftJoin('f.owner' , 'o')
->leftJoin('o.department' , 'd')
->leftJoin('o.roles' , 'r')
->where('f_r.User = :user')->setParameter('user', $user)
->andWhere('f.published = :published')->setParameter('published', 1)
;
return $query;
}
/**
* Get all user registration for one formation
* @param $user
* @return \Doctrine\ORM\QueryBuilder
*/
public function getAllByFormation($formation)
{
$queryBuilder = $this->createQueryBuilder('f_r');
$query = $queryBuilder
->leftJoin('f_r.User', 'u')
->where('f_r.formation = :formation')->setParameter('formation', $formation)
;
return $query->getQuery()->getResult();
}
/**
* Get all user registration for one formation
* @param $user
* @return \Doctrine\ORM\QueryBuilder
*/
public function getAllFormationViewed($user,$onlyRH=false)
{
$queryBuilder = $this->createQueryBuilder('f_r');
$query = $queryBuilder
->select('f.title')
->addSelect('f.id')
->addSelect('f_r.viewedAt')
->addSelect('o.lastname as author_lastname')
->addSelect('o.firstname as author_firstname')
->addSelect('c.name as company_name')
->leftJoin('f_r.formation', 'f')
->leftJoin('f.owner', 'o')
->leftJoin('o.roles', 'r')
->leftJoin('o.company', 'c')
->where('f_r.User = :user')->setParameter('user', $user)
->andWhere('f_r.viewed = :viewed')->setParameter('viewed', true)
;
if($onlyRH){
$query->andWhere('r.name = :role')->setParameter('role','ROLE_RH');
}
$query->orderBy('f_r.viewedAt', 'DESC');
return $query->getQuery()->getResult();
}
public function belongsToUser($user, $id){
$queryBuilder = $this->createQueryBuilder('f_r');
$query = $queryBuilder
->leftJoin('f_r.formation' , 'f')
->where('f.owner = :user')->setParameter('user', $user)
->andWhere('f_r.formation = :formation')->setParameter('formation',$id)
->setMaxResults(1)
;
return $query->getQuery()->getOneOrNullResult();
}
public function findRegistrationByFormation($formation, $count=true){
$queryBuilder = $this->createQueryBuilder('f_r');
if($count){
$q = $queryBuilder
->select('count(f_r.id) as count');
}
else{
$q = $queryBuilder
->select('f_r.createdAt as date,u.email,u.firstname,u.lastname')
->leftJoin('f_r.User','u');
}
$q
->where('f_r.formation = :formation')->setParameter('formation',$formation);
// ->andWhere('f_r.emailSent = :email_sent')->setParameter('email_sent',1);
if($count){
return $q->getQuery()->getSingleScalarResult();
}
return $q->getQuery()->getResult();
}
public function findRegistrationViewedByFormation($formation,$count=true,$viewed=1){
$queryBuilder = $this->createQueryBuilder('f_r');
if($count){
$q = $queryBuilder
->select('count(f_r.id) as count');
}
else{
$q = $queryBuilder
->select('f_r.viewedAt as date,u.email,u.firstname,u.lastname')
->leftJoin('f_r.User','u');
}
$q
->where('f_r.formation = :formation')->setParameter('formation',$formation)
// ->andWhere('f_r.emailSent = :email_sent')->setParameter('email_sent',1)
->andWhere('f_r.viewed = :viewed')->setParameter('viewed',$viewed);
if($count){
return $q->getQuery()->getSingleScalarResult();
}
return $q->getQuery()->getResult();
}
public function findRegistrationOpenedByFormation($formation,$count=true,$opened=1){
$queryBuilder = $this->createQueryBuilder('f_r');
if($count){
$q = $queryBuilder
->select('count(f_r.id) as count');
}
else{
$q = $queryBuilder
->select('f_r.openedAt as date,u.email,u.firstname,u.lastname')
->leftJoin('f_r.User','u');
}
$q
->where('f_r.formation = :formation')->setParameter('formation',$formation)
// ->andWhere('f_r.emailSent = :email_sent')->setParameter('email_sent',1)
->andWhere('f_r.opened = :opened')->setParameter('opened',$opened);
if($count){
return $q->getQuery()->getSingleScalarResult();
}
return $q->getQuery()->getResult();
}
}