Symfony Profiler

<?php
namespace App\Repository;
use App\Entity\Inscrit;
use App\Entity\Site;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\ORM\Query;
use Doctrine\Persistence\ManagerRegistry;
/**
* @extends ServiceEntityRepository<Inscrit>
*
* @method Inscrit|null find($id, $lockMode = null, $lockVersion = null)
* @method Inscrit|null findOneBy(array $criteria, array $orderBy = null)
* @method Inscrit[] findAll()
* @method Inscrit[] findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
*/
class InscritRepository extends ServiceEntityRepository
{
public function __construct(ManagerRegistry $registry)
{
parent::__construct($registry, Inscrit::class);
}
public function add(Inscrit $entity, bool $flush = false): void
{
$this->getEntityManager()->persist($entity);
if ($flush) {
$this->getEntityManager()->flush();
}
}
public function remove(Inscrit $entity, bool $flush = false): void
{
$this->getEntityManager()->remove($entity);
if ($flush) {
$this->getEntityManager()->flush();
}
}
// /**
// * @return Inscrit[] Returns an array of Inscrit objects
// */
public function findBySessionNotStart($site = null, $statut): array
{
$results= $this->createQueryBuilder('i')
//->andWhere('i.session = :val')
//->setParameter('val', $value)
->andWhere('i.isdelete = false');
if($statut != 'all'){
$results->join('i.session', 's', 'WITH', 's.status = :statut')
->setParameter('statut', $statut);
}
//->setMaxResults(10)
if($site != null){
$results->innerJoin('i.site','ss','with','i.site = :site')
->setParameter('site', $site);
}
$results->orderBy('i.id', 'DESC');
return $results->getQuery()->getResult();
}
public function findBySessionNotStartTier($id): array
{
return $this->createQueryBuilder('i')
->andWhere('i.tier = :val')
->setParameter('val', $id)
->andWhere('i.isdelete = false')
->join('i.session', 's', 'WITH', 's.status = 1')
->orderBy('i.id', 'DESC')
//->setMaxResults(10)
->getQuery()
->getResult()
;
}
public function findInscritWithoutDiplome($session){
$db = $this->getEntityManager(); //WHERE
$sql = $db->createQuery('
SELECT i,t from App\Entity\Inscrit i
INNER JOIN i.tier t
where i.session = :session
and i.statutinscrit = 2
and i.id not in (select identity(d.inscrit) from App\Entity\Diplome d where d.session =:session and d.isdelete = 0 )
')->setParameter('session',$session);
$results = $sql->getResult(Query::HYDRATE_ARRAY);
/*$stmt = $db->prepare($sql);
$stmt = $stmt->executeQuery(array('session' => $session));
$results = $stmt->fetchAllAssociative();*/
return $results;
}
public function findInscritSite($site){
$db = $this->getEntityManager()->getConnection();
$sql = 'Select
(SELECT count(i.id) as enattente
FROM `inscrit` i
inner join tier t on t.id = i.tier_id
WHERE statutinscrit_id=1
AND isdelete = false
and i.session_id
in (select s.id from session s where s.site_id = :site)
) as en_attente,
(SELECT count(i.id) as valide
FROM `inscrit` i
inner join tier t on t.id = i.tier_id
WHERE statutinscrit_id <> 1
AND isdelete = false
and i.session_id
in (select s.id from session s where s.site_id = :site)
) as valide
';
$stmt = $db->prepare($sql);
$stmt = $stmt->executeQuery(['site' => $site]);
return $stmt->fetchAllAssociative();
}
public function findInscritSiteMonth($site){
$db = $this->getEntityManager()->getConnection();
$sql = '
SELECT count(i.id) as valide, month(created_at) as month
FROM `inscrit` i
inner join tier t on t.id = i.tier_id
WHERE statutinscrit_id <> 1
AND isdelete = false
and i.session_id in (select s.id from session s where s.site_id = :site)
GROUP by (month(created_at))
order by month(created_at) asc
';
$stmt = $db->prepare($sql);
$stmt = $stmt->executeQuery(['site' => $site]);
return $stmt->fetchAllAssociative();
}
/**
* //return inscriptions of a tier within user site if not admin or all
*
* @param [type] $tier
* @param [type] $datedebut
* @param [type] $datefin
* @param Site|null $site
* @return array
*/
public function findByFilters($tier, $datedebut, $datefin, ?Site $site = null): array
{
$query = $this->createQueryBuilder('i')
->join('i.uniteinscription', 'u')
->join('u.carte', 'c')
->andWhere('i.tier = :tier')
->andWhere('u.createdat >= :datedebut')
->andWhere('u.createdat <= :datefin')
->setParameter('datedebut', $datedebut)
->setParameter('datefin', $datefin)
->setParameter('tier', $tier)
;
if($site instanceof \App\Entity\Site && $site != null){
$query = $query->andWhere('c.site = :user_site')
->setParameter('user_site', $site);
}
return $query->groupBy('u.id')
->orderBy('i.id', 'DESC')
->getQuery()
->getResult();
}
public function getWeekDigest(?Site $site = null)
{
$datefin = date('Y-m-d');
$datedebut = date('Y-m-d', strtotime('-7 days'));
$filterinscritbysite = $filtersessionbysite = $filtercabysite = "";
if($site instanceof \App\Entity\Site && $site != null){
$filterinscritbysite = " AND inscrit.site_id = '".$site->getId()."' ";
$filtersessionbysite = " AND session.site_id = '".$site->getId()."' ";
$filtercabysite = " session.site_id= '".$site->getId()."' AND ";
}
$db = $this->getEntityManager()->getConnection();
$sql = "
SELECT (
SELECT count(*)
FROM inscrit
WHERE inscrit.created_at >= '".$datedebut."'
AND inscrit.created_at <= '".$datefin."'
".$filterinscritbysite."
) AS nbr_inscrit,
(
SELECT count(*)
FROM session
WHERE session.start_date >='".$datedebut."'
AND session.start_date <= '".$datefin."'
AND session.status_id = 2
".$filtersessionbysite."
) as session_en_cours,
(
SELECT count(*)
FROM session
WHERE session.start_date >='".$datedebut."'
AND session.start_date <= '".$datefin."'
AND session.status_id = 1
".$filtersessionbysite."
) as session_planifie "
;
$stmt = $db->prepare($sql);
$stmt = $stmt->executeQuery();
$infos = $stmt->fetchAllAssociative();
$sql = "(
SELECT CONCAT(tier.firstname, ' ', tier.lastname) as inscrit_name,CONCAT(tier.telephone, ' ', tier.email) as inscrit_contact,inscrit.statutinscrit_id,tier.id as tier_id, financement.id AS financement_id, financement.type_f, financement.montant AS quote_part_montant_apres_rem, inscrit.id AS inscrit_id, type_inscrit.wording as type_inscrit_wording, status_inscrit.wording AS status_inscrit_wording, status_inscrit.position AS status_inscrit_position, COALESCE(SUM(reglement.montant), 0) as montant_regle, COALESCE(inscrit.remise,0) AS remise, (financement.montant - COALESCE(SUM(reglement.montant), 0)) AS restepayer, reglement.id AS reglement_id, session.id as session_id, type_f, '-' AS organisme_wording, session.site_id as site_id
FROM financement
LEFT JOIN reglement ON (financement.id = reglement.financement_id)
JOIN inscrit ON (financement.inscrit_id = inscrit.id)
JOIN type_inscrit ON(inscrit.typeinscrit_id = type_inscrit.id)
JOIN status_inscrit ON(inscrit.statutinscrit_id = status_inscrit.id)
JOIN session ON (inscrit.session_id = session.id)
JOIN tier ON (inscrit.tier_id = tier.id)
GROUP BY (financement.id)
HAVING ".$filtercabysite." type_f=1 AND inscrit.statutinscrit_id =2
ORDER BY financement.inscrit_id DESC
)
UNION
(
SELECT CONCAT(tier.firstname, ' ', tier.lastname) as inscrit_name,CONCAT(tier.telephone, ' ', tier.email) as inscrit_contact,inscrit.statutinscrit_id,tier.id as tier_id, financement.id AS financement_id, financement.type_f, financement.montant AS quote_part_montant_apres_rem, inscrit.id AS inscrit_id, type_inscrit.wording as type_inscrit_wording, status_inscrit.wording AS status_inscrit_wording, status_inscrit.position AS status_inscrit_position, COALESCE(SUM(reglement_facture_inscrit.montantregle), 0) as montant_regle, (COALESCE(inscrit.remise, 0) *(financement.montant/inscrit.montanttotal) ) as remise, (financement.montant - COALESCE(SUM(reglement_facture_inscrit.montantregle),0)) AS restepayer, reglement_facture_inscrit.id AS reglement_id, session.id as session_id, type_f, organisme.wwording AS organisme_wording, session.site_id as site_id
FROM financement
LEFT JOIN organisme ON(financement.organisme_id = organisme.id)
LEFT JOIN reglement_facture_inscrit ON (financement.id = reglement_facture_inscrit.financement_id)
JOIN inscrit ON (financement.inscrit_id = inscrit.id)
JOIN type_inscrit ON(inscrit.typeinscrit_id = type_inscrit.id)
JOIN status_inscrit ON(inscrit.statutinscrit_id = status_inscrit.id)
JOIN session ON (inscrit.session_id = session.id)
JOIN tier ON (inscrit.tier_id = tier.id)
GROUP BY (financement.id)
HAVING ".$filtercabysite." type_f=4 AND inscrit.statutinscrit_id =2
ORDER BY financement.inscrit_id DESC
)
UNION
(
SELECT CONCAT(tier.firstname, ' ', tier.lastname) as inscrit_name,CONCAT(tier.telephone, ' ', tier.email) as inscrit_contact,inscrit.statutinscrit_id,tier.id as tier_id,financement.id AS financement_id, financement.type_f, financement.montant AS quote_part_montant_apres_rem, inscrit.id AS inscrit_id, type_inscrit.wording as type_inscrit_wording, status_inscrit.wording AS status_inscrit_wording, status_inscrit.position AS status_inscrit_position, coalesce(SUM(reglement_facture_inscrit.montantregle), 0) as montant_regle, COALESCE(inscrit.remise, 0) AS remise, (financement.montant - coalesce(SUM(reglement_facture_inscrit.montantregle), 0)) AS restepayer, reglement_facture_inscrit.id AS reglement_id, session.id as session_id, type_f, entreprise.wording AS organisme_wording, session.site_id as site_id
FROM financement
LEFT JOIN entreprise ON (financement.entreprise_id = entreprise.id)
LEFT JOIN reglement_facture_inscrit ON (financement.id = reglement_facture_inscrit.financement_id)
JOIN inscrit ON (financement.inscrit_id = inscrit.id)
JOIN type_inscrit ON(inscrit.typeinscrit_id = type_inscrit.id)
JOIN status_inscrit ON(inscrit.statutinscrit_id = status_inscrit.id)
JOIN session ON (inscrit.session_id = session.id)
JOIN tier ON (inscrit.tier_id = tier.id)
GROUP BY (financement.id)
HAVING ".$filtercabysite." type_f=2 AND inscrit.statutinscrit_id =2
ORDER BY financement.inscrit_id DESC
)
UNION
(
SELECT CONCAT(tier.firstname, ' ', tier.lastname) as inscrit_name,CONCAT(tier.telephone, ' ', tier.email) as inscrit_contact,inscrit.statutinscrit_id,tier.id as tier_id, financement.id AS financement_id, financement.type_f, financement.montant AS quote_part_montant_apres_rem, inscrit.id AS inscrit_id, type_inscrit.wording as type_inscrit_wording, status_inscrit.wording AS status_inscrit_wording, status_inscrit.position AS status_inscrit_position, inscrit.montanttotal as montant_regle, COALESCE(inscrit.remise,0) AS remise, 0 AS restepayer, reglement.id AS reglement_id, session.id as session_id, type_f, 'CPF' AS organisme_wording, session.site_id as site_id
FROM financement
LEFT JOIN reglement ON (financement.id = reglement.financement_id)
JOIN inscrit ON (financement.inscrit_id = inscrit.id)
JOIN type_inscrit ON(inscrit.typeinscrit_id = type_inscrit.id)
JOIN status_inscrit ON(inscrit.statutinscrit_id = status_inscrit.id)
JOIN session ON (inscrit.session_id = session.id)
JOIN tier ON (inscrit.tier_id = tier.id)
GROUP BY (financement.id)
HAVING ".$filtercabysite." type_f=3 AND inscrit.statutinscrit_id =2
ORDER BY financement.inscrit_id DESC
)
";
$stmt = $db->prepare($sql);
$stmt = $stmt->executeQuery();
$ca = $stmt->fetchAllAssociative();
$tca = $apresrem = $rem = $paye = $rest = 0.00;
foreach($ca as $insc)
{
$tca += (float)$insc["quote_part_montant_apres_rem"] + (float)$insc["remise"];
$rem += $insc["remise"];
$apresrem += $insc["quote_part_montant_apres_rem"];
$paye += $insc["montant_regle"];
$rest += $insc["restepayer"];
}
return array("inscrits" => $infos[0]["nbr_inscrit"], "encours" => $infos[0]["session_en_cours"], "planifie" => $infos[0]["session_planifie"], "datedebut" => $datedebut, "datefin" => $datefin, "ca" => $tca, "remise" => $rem, "apresremise" => $apresrem, "paye" => $paye, "reste" => $rest);
}
public function findElearningInscrits($site = null): array
{
$results= $this->createQueryBuilder('i')
->andWhere('i.elearning IS NOT NULL')
->andWhere('i.isdelete = false');
if($site != null){
$results->innerJoin('i.site','ss','with','i.site = :site')
->setParameter('site', $site);
}
$results->orderBy('i.id', 'DESC');
return $results->getQuery()->getResult();
}
public function findExamenInscrits($site = null): array
{
$results= $this->createQueryBuilder('i')
->andWhere('i.examen IS NOT NULL')
->andWhere('i.isdelete = false');
if($site != null){
$results->innerJoin('i.site','ss','with','i.site = :site')
->setParameter('site', $site);
}
$results->orderBy('i.id', 'DESC');
return $results->getQuery()->getResult();
}
public function findConduiteInscrits($site = null): array
{
$results= $this->createQueryBuilder('i')
->andWhere('i.conduiteheure IS NOT NULL')
->andWhere('i.isdelete = false');
if($site != null){
$results->innerJoin('i.site','ss','with','i.site = :site')
->setParameter('site', $site);
}
$results->orderBy('i.id', 'DESC');
return $results->getQuery()->getResult();
}
// public function findOneBySomeField($value): ?Inscrit
// {
// return $this->createQueryBuilder('i')
// ->andWhere('i.exampleField = :val')
// ->setParameter('val', $value)
// ->getQuery()
// ->getOneOrNullResult()
// ;
// }
}

src/Repository/InscritRepository.php line 47