Symfony Profiler

<?php
namespace App\Repository;
use App\Entity\SatisfactionClient;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;
/**
* @extends ServiceEntityRepository<SatisfactionClient>
*
* @method SatisfactionClient|null find($id, $lockMode = null, $lockVersion = null)
* @method SatisfactionClient|null findOneBy(array $criteria, array $orderBy = null)
* @method SatisfactionClient[] findAll()
* @method SatisfactionClient[] findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
*/
class SatisfactionClientRepository extends ServiceEntityRepository
{
public function __construct(ManagerRegistry $registry)
{
parent::__construct($registry, SatisfactionClient::class);
}
public function add(SatisfactionClient $entity, bool $flush = false): void
{
$this->getEntityManager()->persist($entity);
if ($flush) {
$this->getEntityManager()->flush();
}
}
public function remove(SatisfactionClient $entity, bool $flush = false): void
{
$this->getEntityManager()->remove($entity);
if ($flush) {
$this->getEntityManager()->flush();
}
}
/**
* @return SatisfactionClient[] Returns an array of SatisfactionClient objects
*/
public function getSatisfactionClientAverageCpvByMonthYear($pointOfSale, $childs, $organisationId, $perid): array
{
// Requête pour obtenir la moyenne totale par mois et année (sans codeCluster)
$moyenneTotaleQuery = $this->createQueryBuilder('s')
->select(
'AVG(s.noteSatisfaction) AS moyenneTotal',
'MONTH(s.dateContrat) AS mois',
'YEAR(s.dateContrat) AS annee'
)
->leftJoin('s.production', 'p')
->where('s.entite = :pointOfSaleName') // Filtrer par pointOfSale
->setParameter('pointOfSaleName', $pointOfSale->getName());
if (is_array($childs) && count($childs) > 0) {
$moyenneTotaleQuery->andWhere('p.seller IN (:childs)')
->leftJoin('p.seller', 'u')
->setParameter('childs', $childs);
}
if ($organisationId) {
$moyenneTotaleQuery->andWhere('p.organisation = :organisationId')
->setParameter('organisationId', $organisationId);
}
if ($perid) {
$moyenneTotaleQuery->andWhere('p.loginVendeurInit = :perid ')
->setParameter('perid', $perid);
}
$moyenneTotaleQuery->groupBy('annee, mois') // Groupement uniquement par année et mois pour la moyenne totale
->getQuery()
->getArrayResult();
// Requête pour obtenir la moyenne par cluster par mois, année et cluster
$moyenneParClusterQuery = $this->createQueryBuilder('sc')
->select(
'MONTH(sc.dateContrat) AS mois',
'YEAR(sc.dateContrat) AS annee',
'sc.codeCluster',
' AVG(sc.noteSatisfaction) AS moyenneParCluster'
)
->where('sc.entite = :pointOfSaleName') // Filtrer par pointOfSale
->setParameter('pointOfSaleName', $pointOfSale->getName())
->groupBy('annee, mois, sc.codeCluster') // Groupement par année, mois et codeCluster pour la moyenne par cluster
->getQuery()
->getArrayResult();
// Retourner les résultats
return [
'moyenneTotale' => $moyenneTotaleQuery,
'moyenneParCluster' => $moyenneParClusterQuery
];
}
public function getSatisfactionClientAverageForSpecificMonthAndYear(
$pointOfSale,
$codeCluster,
$codeInsee,
int $mois,
int $annee,
$category,
$childs,
$organisationId,
$perid,
$sellerId,
$departement
): array {
$qb = $this->createQueryBuilder('s')
->select('AVG(s.noteSatisfaction) AS moyenneSatisfaction')
->leftJoin('s.production', 'p')
->leftJoin('p.cluster', 'c')
->andWhere('MONTH(s.dateContrat) = :mois')
->andWhere('YEAR(s.dateContrat) = :annee')
->setParameter('mois', $mois)
->setParameter('annee', $annee);
// 👉 Si organisationId existe, on filtre dessus, sinon on utilise pointOfSaleName
if ($organisationId) {
$qb->andWhere('p.organisation = :organisationId')
->setParameter('organisationId', $organisationId);
} else {
$qb->andWhere('s.cod_poin_vent = :cpv')
->setParameter('cpv', $pointOfSale->getCode());
}
if ($category) {
$category = array_filter(
array_map('intval', explode(',', $category)),
fn($id) => $id > 0
);
$qb->andWhere('p.category IN (:categories)')
->setParameter('categories', $category);
}
if (is_array($childs) && count($childs) > 0) {
$qb->andWhere('p.seller IN (:childs)')
->leftJoin('p.seller', 'u')
->setParameter('childs', $childs);
}
if ($perid) {
$qb->andWhere('p.loginVendeurInit = :perid')
->setParameter('perid', $perid);
}
if ($sellerId) {
$qb->andWhere('p.seller = :sellerId')
->setParameter('sellerId', $sellerId);
}
if ($codeCluster) {
$qb
->andWhere('c.codeCluster = :codeCluster')
->setParameter('codeCluster', $codeCluster);
}
if ($codeInsee) {
$qb->andWhere('p.codeInsee = :codeInsee')
->setParameter('codeInsee', $codeInsee);
}
if ($departement) {
// Si le département est fourni, on cherche les clusters dont le code commence par "departement-"
$qb->andWhere('c.codeCluster LIKE :prefix')
->setParameter('prefix', $departement . '-%');
}
return $qb->getQuery()->getArrayResult();
}
public function getSatisfactionClientAverageForSpecificMonthAndYearForHistory(
$pointOfSale,
$codeCluster,
$codeInsee,
int $mois,
int $annee,
$category,
$childs,
$organisationId,
$perid,
$sellerId,
$departement
): array {
$qb = $this->createQueryBuilder('s')
->select('AVG(s.noteSatisfaction) AS moyenneSatisfaction')
->leftJoin('s.history', 'p')
->leftJoin('p.cluster', 'c')
->andWhere('MONTH(s.dateContrat) = :mois')
->andWhere('YEAR(s.dateContrat) = :annee')
->setParameter('mois', $mois)
->setParameter('annee', $annee);
// 👉 Si organisationId existe, on filtre dessus, sinon on utilise pointOfSaleName
if ($organisationId) {
$qb->andWhere('p.organisation = :organisationId')
->setParameter('organisationId', $organisationId);
} else {
$qb->andWhere('s.cod_poin_vent = :cpv')
->setParameter('cpv', $pointOfSale->getCode());
}
if ($category) {
$category = array_filter(
array_map('intval', explode(',', $category)),
fn($id) => $id > 0
);
$qb->andWhere('p.category IN (:categories)')
->setParameter('categories', $category);
}
if (is_array($childs) && count($childs) > 0) {
$qb->andWhere('p.seller IN (:childs)')
->leftJoin('p.seller', 'u')
->setParameter('childs', $childs);
}
if ($perid) {
$qb->andWhere('p.loginVendeurInit = :perid')
->setParameter('perid', $perid);
}
if ($sellerId) {
$qb->andWhere('p.seller = :sellerId')
->setParameter('sellerId', $sellerId);
}
if ($codeCluster) {
$qb
->andWhere('c.codeCluster = :codeCluster')
->setParameter('codeCluster', $codeCluster);
}
if ($codeInsee) {
$qb->andWhere('p.codeInsee = :codeInsee')
->setParameter('codeInsee', $codeInsee);
}
if ($departement) {
// Si le département est fourni, on cherche les clusters dont le code commence par "departement-"
$qb->andWhere('c.codeCluster LIKE :prefix')
->setParameter('prefix', $departement . '-%');
}
return $qb->getQuery()->getArrayResult();
}
public function getSatisfaction(
$pointOfSale,
$codeCluster,
$codeInsee,
int $mois,
int $annee,
$category,
$childs,
$organisationId,
$perid,
$sellerId,
$departement
): array {
$qb = $this->createQueryBuilder('s')
->select('c.codeCluster AS codeCluster, AVG(s.noteSatisfaction) AS moyenneSatisfaction')
->leftJoin('s.production', 'p')
->leftJoin('p.cluster', 'c')
->andWhere('MONTH(s.dateContrat) = :mois')
->andWhere('YEAR(s.dateContrat) = :annee')
->setParameter('mois', $mois)
->setParameter('annee', $annee)
->groupBy('c.codeCluster');
if ($organisationId) {
$qb->andWhere('p.organisation = :organisationId')
->setParameter('organisationId', $organisationId);
} else {
$qb->andWhere('s.entite = :pointOfSaleName')
->setParameter('pointOfSaleName', $pointOfSale->getName());
}
if ($category) {
$category = array_filter(
array_map('intval', explode(',', $category)),
fn($id) => $id > 0
);
$qb->andWhere('p.category IN (:categories)')
->setParameter('categories', $category);
}
if (is_array($childs) && count($childs) > 0) {
$qb->andWhere('p.seller IN (:childs)')
->leftJoin('p.seller', 'u')
->setParameter('childs', $childs);
}
if ($perid) {
$qb->andWhere('p.loginVendeurInit = :perid')
->setParameter('perid', $perid);
}
if ($sellerId) {
$qb->andWhere('p.seller = :sellerId')
->setParameter('sellerId', $sellerId);
}
if ($codeCluster) {
$qb->andWhere('c.codeCluster = :codeCluster')
->setParameter('codeCluster', $codeCluster);
}
if ($codeInsee) {
$qb->andWhere('p.codeInsee = :codeInsee')
->setParameter('codeInsee', $codeInsee);
}
if ($departement) {
$qb->andWhere('c.codeCluster LIKE :prefix')
->setParameter('prefix', $departement . '-%');
}
$results = $qb->getQuery()->getArrayResult();
// Créer un map [codeCluster => moyenneSatisfaction]
$satisfactionMap = [];
foreach ($results as $r) {
$satisfactionMap[$r['codeCluster']] = (float)$r['moyenneSatisfaction'];
}
return $satisfactionMap;
}
public function getSatisfactionClientAverageByMonthDayWeek(
$pointOfSale,
$codeCluster,
$departement,
$codeInsee,
int $annee,
$category,
$childs,
$organisationId,
$perid
): array {
$conn = $this->getEntityManager()->getConnection();
$sql = "
SELECT
MONTH(s.date_contrat) AS mois,
WEEK(s.date_contrat, 1) AS semaine,
DATE(s.date_contrat) AS jour,
AVG(s.note_satisfaction) AS moyenne_satisfaction
FROM satisfaction_client s
LEFT JOIN production p ON s.production_id = p.id
LEFT JOIN cluster c ON p.cluster_id = c.id
WHERE YEAR(s.date_contrat) = :annee
AND s.date_contrat IS NOT NULL
";
$params = [
'annee' => $annee,
];
if ($organisationId) {
$sql .= " AND p.organisation_id = :organisationId";
$params['organisationId'] = $organisationId;
} else {
$sql .= " AND s.entite = :pointOfSaleName";
$params['pointOfSaleName'] = $pointOfSale->getName();
}
// Normalisation robuste du paramètre $category
if ($category) {
$categoryArray = [];
if (is_string($category)) {
$categoryArray = array_filter(array_map('intval', explode(',', $category)), fn($id) => $id > 0);
} elseif (is_array($category) || $category instanceof \Traversable) {
foreach ($category as $cat) {
$categoryArray[] = is_object($cat) && method_exists($cat, 'getId') ? $cat->getId() : (int)$cat;
}
$categoryArray = array_filter($categoryArray, fn($id) => $id > 0);
}
if (!empty($categoryArray)) {
$sql .= " AND p.category_id IN (" . implode(',', $categoryArray) . ")";
}
}
if (is_array($childs) && count($childs) > 0) {
$sql .= " AND p.seller_id IN (" . implode(',', $childs) . ")";
}
if ($perid) {
$sql .= " AND p.login_vendeur_init = :perid";
$params['perid'] = $perid;
}
if ($codeCluster) {
$sql .= " AND c.code_cluster = :codeCluster";
$params['codeCluster'] = $codeCluster;
}
if ($departement) {
$sql .= " AND c.code_cluster LIKE :prefix";
$params['prefix'] = $departement . '-%';
}
if ($codeInsee) {
$sql .= " AND p.code_insee = :codeInsee";
$params['codeInsee'] = $codeInsee;
}
$sql .= " GROUP BY s.date_contrat
ORDER BY s.date_contrat ASC";
$stmt = $conn->executeQuery($sql, $params);
$results = $stmt->fetchAllAssociative();
// Déterminer mois courant et précédent
$currentMonth = (int)date('m');
$previousMonth = $currentMonth === 1 ? 12 : $currentMonth - 1;
// Réorganiser côté PHP
$data = [
'parMois' => [],
'parSemaine' => [],
'parJour' => [],
];
foreach ($results as $row) {
$data['parMois'][$row['mois']][] = round((float)$row['moyenne_satisfaction'], 2);
$data['parSemaine'][$row['semaine']][] = round((float)$row['moyenne_satisfaction'], 2);
// Limiter parJour aux deux derniers mois
if ((int)$row['mois'] === $currentMonth || (int)$row['mois'] === $previousMonth) {
$data['parJour'][$row['jour']] = round((float)$row['moyenne_satisfaction'], 2);
}
}
// Calculer la moyenne réelle pour mois et semaine
foreach (['parMois', 'parSemaine'] as $key) {
foreach ($data[$key] as $period => $values) {
$data[$key][$period] = round(array_sum($values) / count($values), 2);
}
}
return $data;
}
public function getSatisfactionClientAverageByPeriod(
$pointOfSale,
$codeCluster,
$departement,
$codeInsee,
int $annee,
$category,
$childs,
$organisationId,
$perid,
string $periode = 'month' // 'day' | 'week' | 'month'
): array {
$conn = $this->getEntityManager()->getConnection();
// Sélection dynamique selon la période
switch ($periode) {
case 'week':
$selectGroup = "WEEK(s.date_contrat, 1) AS periode";
break;
case 'day':
$selectGroup = "DATE(s.date_contrat) AS periode";
break;
case 'month':
default:
$selectGroup = "MONTH(s.date_contrat) AS periode";
break;
}
$sql = "
SELECT
$selectGroup,
AVG(s.note_satisfaction) AS moyenne_satisfaction
";
if ($organisationId) {
$sql .= ",
po.code AS code_point,
po.name AS nom_point
";
}
$sql .= "
FROM satisfaction_client s
LEFT JOIN production p ON s.production_id = p.id
LEFT JOIN cluster c ON p.cluster_id = c.id
LEFT JOIN point_of_sale po ON p.point_of_sale_id = po.id
WHERE YEAR(s.date_contrat) = :annee
AND s.date_contrat IS NOT NULL
";
$params = ['annee' => $annee];
// Filtrage organisation / point de vente
if ($organisationId) {
$sql .= " AND p.organisation_id = :organisationId";
$params['organisationId'] = $organisationId;
} else {
$sql .= " AND s.entite = :pointOfSaleName";
$params['pointOfSaleName'] = $pointOfSale->getName();
}
// Filtrage catégorie
if ($category) {
$categoryArray = [];
if (is_string($category)) {
$categoryArray = array_filter(array_map('intval', explode(',', $category)), fn($id) => $id > 0);
} elseif (is_array($category) || $category instanceof \Traversable) {
foreach ($category as $cat) {
$categoryArray[] = is_object($cat) && method_exists($cat, 'getId') ? $cat->getId() : (int)$cat;
}
$categoryArray = array_filter($categoryArray, fn($id) => $id > 0);
}
if (!empty($categoryArray)) {
$sql .= " AND p.category_id IN (" . implode(',', $categoryArray) . ")";
}
}
if (is_array($childs) && count($childs) > 0) {
$sql .= " AND p.seller_id IN (" . implode(',', $childs) . ")";
}
if ($perid) {
$sql .= " AND p.login_vendeur_init = :perid";
$params['perid'] = $perid;
}
if ($codeCluster) {
$sql .= " AND c.code_cluster = :codeCluster";
$params['codeCluster'] = $codeCluster;
}
if ($departement) {
$sql .= " AND c.code_cluster LIKE :prefix";
$params['prefix'] = $departement . '-%';
}
if ($codeInsee) {
$sql .= " AND p.code_insee = :codeInsee";
$params['codeInsee'] = $codeInsee;
}
// Groupement selon la période choisie
if ($organisationId) {
$sql .= " GROUP BY po.id, periode ORDER BY po.id, periode ASC";
} else {
$sql .= " GROUP BY periode ORDER BY periode ASC";
}
// Exécution
$stmt = $conn->executeQuery($sql, $params);
$results = $stmt->fetchAllAssociative();
// Réorganisation du résultat
$data = [];
foreach ($results as $row) {
$periodeKey = $row['periode'];
$moyenne = round((float)$row['moyenne_satisfaction'], 2);
if ($organisationId) {
$codePoint = $row['code_point'];
if (!isset($data[$codePoint])) {
$data[$codePoint] = [];
}
$data[$codePoint][$periodeKey] = $moyenne;
} else {
$data[$periodeKey] = $moyenne;
}
}
return $data;
}
public function getSatisfactionClientDetails(
$pointOfSale,
$yearMonth,
$codeCluster,
$note
) {
$entityManager = $this->getEntityManager();
$qb = $entityManager->createQueryBuilder()
->select('s.id,p.id AS production_id,s.orderNumber,s.noteSatisfaction,s.verbatim
,s.rencontreConseiller,s.docSynthetique,s.entite,s.codeCluster,c.libelleCluster,s.datReponse, s.dateContrat,u.id AS seller_id,CONCAT(u.nom, \' \', u.prenom) AS nom_prenom')
->from('App\Entity\SatisfactionClient', 's')
->leftJoin('App\Entity\Cluster', 'c', 'WITH', 'c.codeCluster = s.codeCluster')
->leftJoin('App\Entity\Production', 'p', 'WITH', 'p.id = s.production')
->leftJoin('App\Entity\User', 'u', 'WITH', 'u.id = p.seller')
->where('s.entite = :pointOfSaleName')
->setParameter('pointOfSaleName', $pointOfSale->getName());
if ($yearMonth) {
$qb
->andWhere('MONTH(s.dateContrat) = :mois')
->andWhere('YEAR(s.dateContrat) = :annee')
->setParameter('mois', explode('-', $yearMonth)[1])
->setParameter('annee', explode('-', $yearMonth)[0])
;
}
if ($codeCluster) {
$qb->andWhere('s.codeCluster = :codeCluster')
->setParameter('codeCluster', $codeCluster);
}
if ($note) {
$qb->andWhere('s.noteSatisfaction <= :note')
->setParameter('note', $note);
}
return $qb->getQuery()
->getArrayResult();
}
}

src/Repository/SatisfactionClientRepository.php line 170