Symfony Profiler

<?php
namespace App\Controller\Api\Production;
use App\Entity\User;
use App\Repository\EtatProductionRepository;
use App\Repository\ProductionRepository;
use App\Service\HierarchyService;
use DateTime;
use App\Entity\Production;
use App\Repository\UserRepository;
use App\Repository\PointOfSaleRepository;
use App\Repository\SatisfactionClientRepository;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Annotation\Route;
use Symfony\Component\HttpKernel\Exception\BadRequestHttpException;
use Symfony\Component\Serializer\SerializerInterface;
use Doctrine\ORM\EntityManagerInterface;
class ProductionAnalyticsController extends AbstractController
{
public function __invoke(
Request $request,
EtatProductionRepository $etatProductionRepository,
ProductionRepository $productionRepository,
PointOfSaleRepository $pointOfSaleRepository,
SatisfactionClientRepository $satisfactionClientRepository,
UserRepository $userRepository,
EntityManagerInterface $entityManager,
HierarchyService $hierarchyService
) {
$pointOfSaleId = $request->query->get('pointOfSaleId', null);
$pointsOfSale = [];
if ($pointOfSaleId !== null) {
$organisationId = null;
$pointOfSale = $pointOfSaleRepository->findOneBy(["id" => $pointOfSaleId]);
if ($pointOfSale === null) {
throw new BadRequestHttpException('Le point de vente avec cet ID n\'existe pas.');
}
$pointsOfSale = [$pointOfSale];
} else {
$organisationId = $request->query->get('organisationId', null);
$pointsOfSale = $pointOfSaleRepository->findAll();
}
$codeCluster = $request->query->get('codeCluster', null);
$codeInsee = $request->query->get('codeInsee', null);
$optionSelect = $request->query->get('optionSelect', 'V');
$category = $request->query->get('categoryId');
$nbrMonth = (int) $request->query->get('nbrMonth', 1);
$currentYear = (int) date('Y');
$currentMonth = (int) date('m');
$queryMonth = $request->query->get('month');
$queryYear = $request->query->get('year');
$perid = $request->query->get('perid');
$sellerId = $request->query->get('sellerId');
$departement = $request->query->get('departement');
$childs = [];
$user = null;
if ($request->query->get('idUser') != null) {
$user = $entityManager->getRepository(User::class)->find((int)$request->query->get('idUser'));
if (!$user) {
throw new BadRequestHttpException('L\'utilisateur avec cet ID n\'existe pas.');
}
// Récupérer l'hierarchie descendante
if (in_array('ROLE_MANAGER', $user->getRoles()) || in_array('ROLE_DIRECTOR', $user->getRoles())) {
$childs = array_map(fn($p) => (int) $p['id'], $hierarchyService->getHierarchyDescendante($user->getId()));
}
}
$baseMonth = $queryMonth !== null ? (int) $queryMonth : $currentMonth;
$baseYear = $queryYear !== null ? (int) $queryYear : $currentYear;
$etatKO = $etatProductionRepository->findOneBy(["nom" => 'Racco KO']);
$etatRaccorde = $etatProductionRepository->findOneBy(["nom" => 'Raccorde']);
$response = [];
for ($i = 0; $i < $nbrMonth; $i++) {
$date = (new \DateTimeImmutable("$baseYear-$baseMonth-01"))->modify("-$i months");
$mois = (int) $date->format('m');
$annee = (int) $date->format('Y');
// Initialisation des agrégats
$totalVentes = 0;
$totalSatisfaction = 0.0;
$nombreVendeurs = 0;
$etp = 0;
$totalVentesKO = 0;
$totalObjectif = 0;
$projection = 0;
$jours_restants = 0;
$moyenneJournaliere = 0;
$clients4P = 0;
$clients4PC = 0;
$j30 = 0;
$j30_mobile = 0;
$titulaire_mobile = 0;
$titulaire_mobile_chainage = 0;
$clients_hors_mig = 0;
$total_clients_unique = 0;
$total_ventes_raccordes_fixe_conquete = 0;
$total_ventes_raccordes_fixe = 0;
$total_ventes_raccordes_mobile = 0;
$j30_raccorde_fixe_conquete = 0;
$j30_raccorde_fixe = 0;
$j30_raccorde_mobile = 0;
$rio = 0;
$pto_non_saisie = 0;
$pto_saisie = 0;
$pto_non_existante = 0;
$pto_existante = 0;
$total_vente_vla_conquette = 0;
$total_ventes_box_5G = 0;
$mrz = [];
$clients_4PC_par_semaine = [];
$clients_4P_par_semaine = [];
foreach ($pointsOfSale as $pointOfSale) {
$satisfactionData = $satisfactionClientRepository->getSatisfactionClientAverageForSpecificMonthAndYear(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
$category,
$childs,
$organisationId,
$perid,
$sellerId,
$departement
);
$note = !empty($satisfactionData) && isset($satisfactionData[0]['moyenneSatisfaction'])
? (float) $satisfactionData[0]['moyenneSatisfaction'] : 0.0;
$totalSatisfaction += $note;
$totalVentes += $productionRepository->getProductionsAnalyticsVentes(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
$optionSelect,
$etatKO,
$category,
$childs,
$organisationId,
$perid,
$sellerId,
$departement
)[0]['total_ventes'] ?? 0;
$total_ventes_box_5G += $productionRepository->getProductionsAnalyticsVentesBox5G(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
"V",
$etatKO,
$category,
$childs,
$organisationId,
$perid,
$sellerId,
$departement
)[0]['total_ventes'] ?? 0;
$total_ventes_raccordes_fixe_conquete += $productionRepository->getProductionsAnalyticsVentesForChurn(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
'B',
"Raccorde",
null,
$childs,
$organisationId,
$perid,
$sellerId,
$departement
)[0]['total_ventes'] ?? 0;
$total_ventes_raccordes_fixe += $productionRepository->getProductionsAnalyticsVentes(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
'B',
"Raccorde",
null,
$childs,
$organisationId,
$perid,
$sellerId,
$departement
)[0]['total_ventes'] ?? 0;
$total_ventes_raccordes_mobile += $productionRepository->getProductionsAnalyticsVentes(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
'B',
"Raccorde_mobile",
"31,32,33",
$childs,
$organisationId,
$perid,
$sellerId,
$departement
)[0]['total_ventes'] ?? 0;
$nombreVendeurs += $productionRepository->getProductionsAnalyticsVendeurs(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
$optionSelect,
$etatKO,
$category,
$childs,
$organisationId,
$perid,
$sellerId,
$departement
)[0]['nombre_vendeurs'] ?? 0;
$etp += (int)($productionRepository->getProductionsAnalyticsETP(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
$optionSelect,
$etatKO,
$category,
$childs,
$organisationId,
$perid,
$sellerId,
$departement
)[0]['etp'] ?? 0);
$totalVentesKO += $productionRepository->getProductionsAnalyticsKO(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
$optionSelect,
$etatKO,
$category,
$childs,
$organisationId,
$perid,
$sellerId,
$departement
)[0]['total_ventes_ko'] ?? 0;
$totalObjectif += (int)($productionRepository->getProductionsAnalyticsObjectifs(
$pointOfSale,
$codeCluster,
$mois,
$annee,
$optionSelect,
$etatKO,
$category,
$childs,
$organisationId,
$departement
)[0]['total_objectif'] ?? 0);
$projection += (int)$productionRepository->getProductionsAnalyticsProjection(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
$optionSelect,
$etatKO,
$category,
$childs,
$organisationId,
$perid,
$sellerId,
$departement
)['projection_ventes'] ?? 0;
$moyenneJournaliere += (float)$productionRepository->getProductionsAnalyticsProjection(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
$optionSelect,
$etatKO,
$category,
$childs,
$organisationId,
$perid,
$sellerId,
$departement
)['ventes_par_jour'] ?? 0;
$jours_restants += (float)$productionRepository->getProductionsAnalyticsProjection(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
$optionSelect,
$etatKO,
$category,
$childs,
$organisationId,
$perid,
$sellerId,
$departement
)['jours_restants'] ?? 0;
$clients4P += (int)$productionRepository->getProductionsAnalyticsTotal4P(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
$optionSelect,
$etatKO,
"",
$childs,
$organisationId,
$perid,
$sellerId,
$departement
)['total_vente'] ?? 0;
$clients4PC += (int)$productionRepository->getProductionsAnalyticsTitulaireVentes(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
$optionSelect,
$etatKO,
null,
"chainage",
false,
$childs,
$organisationId,
$perid,
$sellerId,
$departement
)[0]['nombre_titulaire_email'] ?? 0;
$j30 += (int)$productionRepository->getProductionsAnalyticsTotalVentesByDateResiliationInf30(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
$optionSelect,
$etatKO,
$category,
$childs,
$organisationId,
$perid,
$sellerId,
$departement
)['total_vente'] ?? 0;
$j30_raccorde_fixe += (int)$productionRepository->getProductionsAnalyticsTotalVentesByDateResiliationInf30(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
"B",
$etatKO,
"1,3",
$childs,
$organisationId,
$perid,
$sellerId,
$departement
)['total_vente'] ?? 0;
$j30_raccorde_fixe_conquete += (int)$productionRepository->getProductionsAnalyticsTotalVentesByDateResiliationInf30ForChurn(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
"B",
$etatKO,
"1,3",
$childs,
$organisationId,
$perid,
$sellerId,
$departement
)['total_vente'] ?? 0;
$j30_mobile += (int)$productionRepository->getProductionsAnalyticsTotalVentesByDateResiliationInf30Mobile(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
"B",
$etatKO,
"31,32,33",
$childs,
$organisationId,
$perid,
$sellerId,
$departement
)['total_vente'] ?? 0;
$j30_raccorde_mobile += (int)$productionRepository->getProductionsAnalyticsTotalVentesByDateResiliationInf30Mobile(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
"B",
"Raccorde_mobile",
"31,32,33,48",
$childs,
$organisationId,
$perid,
$sellerId,
$departement
)['total_vente'] ?? 0;
$titulaire_mobile += (int)$productionRepository->getProductionsAnalyticsTotalMobileV2(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
$optionSelect,
$etatKO,
"",
$childs,
$organisationId,
$perid,
$sellerId,
$departement
)['total_vente'] ?? 0;
$titulaire_mobile_chainage += (int)$productionRepository->getProductionsAnalyticsTotalMobileChainage(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
$optionSelect,
$etatKO,
"chainage",
$childs,
$organisationId,
$perid,
$sellerId,
$departement
)['total_vente'] ?? 0;
$clients_hors_mig += (int)$productionRepository->getProductionsAnalyticsTotalClientsHosting(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
$optionSelect,
"",
$childs,
$organisationId,
$perid,
$sellerId,
$departement
)['total_vente'] ?? 0;
$mrz[] = $productionRepository->getProductionsAnalyticsTotalByIdentityCtrl(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
$optionSelect,
"",
$childs,
$organisationId,
$perid,
$sellerId,
$departement
);
$total_clients_unique += (int)$productionRepository->getProductionsAnalyticsTotalClientsUnique(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
$optionSelect,
$etatKO,
$category,
"",
$childs,
$organisationId,
$perid,
$sellerId,
$departement
)[0]['total_client_unique'] ?? 0;
$clients_4PC_par_semaine[] = $productionRepository->get4PCByWeek(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
$optionSelect,
$etatKO,
"31,32,33",
"chainage",
true,
$childs,
$organisationId,
$perid,
$sellerId,
$departement
);
$clients_4P_par_semaine[] = $productionRepository->get4PByWeek(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
$optionSelect,
$etatKO,
"",
$childs,
$organisationId,
$perid,
$sellerId,
$departement
);
$rio += (int)$productionRepository->getProductionsAnalyticsTotalVenteByOption(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
$childs,
$organisationId,
"RIO",
$perid,
$sellerId,
$departement
)['total_vente'] ?? 0;
$pto_non_saisie += (int)$productionRepository->getProductionsAnalyticsTotalVenteByOption(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
$childs,
$organisationId,
"PTO_NON_SAISIE",
$perid,
$sellerId,
$departement
)['total_vente'] ?? 0;
$pto_saisie += (int)$productionRepository->getProductionsAnalyticsTotalVenteByOption(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
$childs,
$organisationId,
"PTO_SAISIE",
$perid,
$sellerId,
$departement
)['total_vente'] ?? 0;
$pto_non_existante += (int)$productionRepository->getProductionsAnalyticsTotalVenteByOption(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
$childs,
$organisationId,
"PTO_NON_EXISTANTE",
$perid,
$sellerId,
$departement
)['total_vente'] ?? 0;
$pto_existante += (int)$productionRepository->getProductionsAnalyticsTotalVenteByOption(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
$childs,
$organisationId,
"PTO_EXISTANTE",
$perid,
$sellerId,
$departement
)['total_vente'] ?? 0;
$total_vente_vla_conquette += $productionRepository->getProductionsAnalyticsVentesVlaConquete(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
"B",
"1,3",
$childs,
$organisationId,
$perid,
$sellerId,
$departement
)[0]['total_ventes'] ?? 0;
$total_churn_fixe = 0;
$total_churn_mobile = 0;
$total_rio = 0;
$total_pto_saisie = 0;
$total_pto_non_saisie = 0;
$total_pto_non_existante = 0;
$total_potentiel = 0;
$total_pto_existante = 0;
if ($total_ventes_raccordes_fixe_conquete > 0 && $j30_raccorde_fixe_conquete > 0) {
$ratio_fixe = ($j30_raccorde_fixe_conquete / $total_ventes_raccordes_fixe_conquete) * 100;
$total_churn_fixe = number_format($ratio_fixe, 1);
}
if ($total_ventes_raccordes_mobile > 0 && $j30_raccorde_mobile > 0) {
$ratio_mobile = ($j30_raccorde_mobile / $total_ventes_raccordes_mobile) * 100;
$total_churn_mobile = number_format($ratio_mobile, 1);
}
if ($total_vente_vla_conquette > 0 && $rio > 0) {
$ratio_rio = ($rio / $total_vente_vla_conquette) * 100;
$total_rio = round($ratio_rio, 2);
}
if ($total_vente_vla_conquette > 0 && $pto_saisie > 0) {
$ratio_pto_saisie = ($pto_saisie / $total_vente_vla_conquette) * 100;
$total_pto_saisie = round($ratio_pto_saisie, 2);
}
if ($total_vente_vla_conquette > 0 && $pto_non_saisie > 0) {
$ratio_pto_non_saisie = ($pto_non_saisie / $total_vente_vla_conquette) * 100;
$total_pto_non_saisie = round($ratio_pto_non_saisie, 2);
}
if ($total_vente_vla_conquette > 0 && $pto_non_existante > 0) {
$ratio_pto_non_existante = ($pto_non_existante / $total_vente_vla_conquette) * 100;
$total_pto_non_existante = round($ratio_pto_non_existante, 2);
}
if ($pto_saisie > 0 && $pto_existante > 0) {
$ratio_potentiel = ($pto_saisie / $pto_existante) * 100;
$total_potentiel = round($ratio_potentiel, 2);
}
if ($total_vente_vla_conquette > 0 && $pto_existante > 0) {
$ratio_pto_existante = ($pto_existante / $total_vente_vla_conquette) * 100;
$total_pto_existante = round($ratio_pto_existante, 2);
}
}
$nbPos = count($pointsOfSale);
$response[$mois] = [
'total_ventes' => $totalVentes,
'note_satisfaction' => round($nbPos ? $totalSatisfaction / $nbPos : 0.0, 2),
'nombre_vendeurs' => $nombreVendeurs,
'etp' => $etp,
'total_ventes_ko' => $totalVentesKO,
'total_objectif' => $totalObjectif,
'projection' => $projection,
'moyenne_journalliere' => round($moyenneJournaliere, 2),
'jours_restants' => $jours_restants,
'clients4P' => $clients4P,
'clients4PC' => $clients4PC,
'j+30' => $j30,
'j+30_mobile' => $j30_mobile,
'nombre_titulaire_email_mobile' => $titulaire_mobile,
'nombre_titulaire_email_mobile_chainage' => $titulaire_mobile_chainage,
'clients_hors_mig' => $clients_hors_mig,
'mrz' => $this->fixMrzTotals($mrz),
'total_client_unique' => $total_clients_unique,
'j30_raccorde_fixe' => $j30_raccorde_fixe,
'j30_raccorde_mobile' => $j30_raccorde_mobile,
'total_ventes_raccordes_fixe' => $total_ventes_raccordes_fixe,
'total_ventes_raccordes_mobile' => $total_ventes_raccordes_mobile,
'clients_churn_fixe' => $total_churn_fixe,
'clients_churn_mobile' => $total_churn_mobile,
'clients_4PC_par_semaine' => $this->fixTaux4PCParSemaine($clients_4PC_par_semaine) ?? [],
'clients_4P_par_semaine' => $this->fixTaux4PCParSemaine($clients_4P_par_semaine) ?? [],
'RIO' => $total_rio . "%",
'Potentiel' => $total_potentiel . "%",
'PTO_saisie' => $total_pto_saisie . "%",
'PTO_non_saisie' => $total_pto_non_saisie . "%",
'PTO_sans' => $total_pto_non_existante . "%",
'PTO_existante' => $total_pto_existante . "%",
'total_ventes_box_5G' => $total_ventes_box_5G
];
}
return new JsonResponse($nbrMonth > 1 ? $response : $response[$baseMonth], 200);
}
/**
* @Route("/api/productions_details_canceled", name="api_get_productions_details_canceled", methods={"GET"})
*/
public function getProductionsDetailsResilies(
Request $request,
EtatProductionRepository $etatProductionRepository,
ProductionRepository $productionRepository,
PointOfSaleRepository $pointOfSaleRepository,
SatisfactionClientRepository $satisfactionClientRepository
) {
$pointOfSale = null;
$pointOfSaleId = $request->query->get('pointOfSaleId', null);
if ($pointOfSaleId) {
$pointOfSale = $pointOfSaleRepository->findOneBy(["id" => $pointOfSaleId]);
}
$codeCluster = $request->query->get('codeCluster', null);
// Récupérer les paramètres mois et année
$currentYear = (int) date('Y');
$currentMonth = (int) date('m');
$mois = $request->query->get('month', $currentMonth);
$annee = $request->query->get('year', $currentYear);
$page = $request->query->get('page', 1);
$optionSelect = $request->query->get('optionSelect', 'V');
return $this->json($productionRepository->getProductionsReselies($pointOfSale, $codeCluster, $mois, $annee, $optionSelect, $page) ?? []);
}
/**
* @Route("/api/productions_details_canceled_mobile", name="api_get_productions_details_canceled_mobile", methods={"GET"})
*/
public function getProductionsDetailsResiliesMobile(
Request $request,
EtatProductionRepository $etatProductionRepository,
ProductionRepository $productionRepository,
PointOfSaleRepository $pointOfSaleRepository,
SatisfactionClientRepository $satisfactionClientRepository
) {
$pointOfSale = null;
$pointOfSaleId = $request->query->get('pointOfSaleId', null);
if ($pointOfSaleId) {
$pointOfSale = $pointOfSaleRepository->findOneBy(["id" => $pointOfSaleId]);
}
$codeCluster = $request->query->get('codeCluster', null);
// Récupérer les paramètres mois et année
$currentYear = (int) date('Y');
$currentMonth = (int) date('m');
$mois = $request->query->get('month', $currentMonth);
$annee = $request->query->get('year', $currentYear);
$page = $request->query->get('page', 1);
$optionSelect = $request->query->get('optionSelect', 'V');
return $this->json($productionRepository->getProductionsReseliesMobile($pointOfSale, $codeCluster, $mois, $annee, $optionSelect, $page) ?? []);
}
/**
* @Route("/api/productions_details_mrz/{pointOfSaleId}", name="api_get_productions_details_mrz", methods={"GET"})
*/
public function getProductionsDetailsByIdentityCtrl(
$pointOfSaleId,
Request $request,
EtatProductionRepository $etatProductionRepository,
ProductionRepository $productionRepository,
PointOfSaleRepository $pointOfSaleRepository,
SatisfactionClientRepository $satisfactionClientRepository
) {
$pointOfSale = $pointOfSaleRepository->findOneBy(["id" => $pointOfSaleId]);
if ($pointOfSale == null) {
throw new BadRequestHttpException('Le point de vente avec cet ID n\'existe pas.');
}
$codeCluster = $request->query->get('codeCluster', null);
$codeInsee = $request->query->get('codeInsee', null);
// Récupérer les paramètres mois et année
$currentYear = (int) date('Y');
$currentMonth = (int) date('m');
$mois = $request->query->get('month', $currentMonth);
$annee = $request->query->get('year', $currentYear);
$identityCtrl = $request->query->get('identityCtrl', null);
$page = $request->query->get('page', 1);
$optionSelect = $request->query->get('optionSelect', 'V');
return $this->json($productionRepository->getProductionsDetailsByIdentityCtrl($pointOfSale, $codeCluster, $codeInsee, $mois, $annee, $optionSelect, $identityCtrl, $page) ?? []);
}
public function fixTaux4PcParSemaine($clients_4PC_par_semaine)
{
$toclientsParSemaine = [];
foreach ($clients_4PC_par_semaine as $source) {
foreach ($source as $item) {
if (!isset($item['semaine']) || !isset($item['total'])) {
// On ignore les éléments incomplets
continue;
}
$semaine = $item['semaine'];
$total = (int) $item['total'];
if (!isset($toclientsParSemaine[$semaine])) {
$toclientsParSemaine[$semaine] = 0;
}
$toclientsParSemaine[$semaine] += $total;
}
}
$resultat = [];
foreach ($toclientsParSemaine as $semaine => $total) {
$resultat[] = [
'semaine' => $semaine,
'total' => $total
];
}
// Tri par ordre croissant de semaine (au cas où)
usort($resultat, fn($a, $b) => $a['semaine'] <=> $b['semaine']);
return $resultat;
}
public function fixMrzTotals(array $mrz): array
{
$toclientsParType = [];
foreach ($mrz as $groupe) {
foreach ($groupe as $item) {
if (!isset($item['identity_ctrl']) || !isset($item['total_vente'])) {
continue;
}
$type = $item['identity_ctrl'];
$vente = (int) $item['total_vente'];
if (!isset($toclientsParType[$type])) {
$toclientsParType[$type] = 0;
}
$toclientsParType[$type] += $vente;
}
}
$resultat = [];
foreach ($toclientsParType as $type => $total) {
$resultat[] = [
'identity_ctrl' => $type,
'total_vente' => $total
];
}
return $resultat;
}
public function getAnalyticsByClusters(
Request $request,
ProductionRepository $productionRepository,
PointOfSaleRepository $pointOfSaleRepository,
EntityManagerInterface $entityManager,
HierarchyService $hierarchyService,
EtatProductionRepository $etatProductionRepository,
SatisfactionClientRepository $satisfactionClientRepository
) {
// -----------------------------------------
// 🔹 Paramètres
// -----------------------------------------
$pointOfSaleId = $request->query->get('pointOfSaleId');
$organisationId = $request->query->get('organisationId');
$pointOfSale = null;
if ($pointOfSaleId) {
$pointOfSale = $pointOfSaleRepository->find($pointOfSaleId);
if (!$pointOfSale) {
throw new BadRequestHttpException("Le point de vente avec cet ID n'existe pas.");
}
$organisationId = null; // Ignorer organisation si point de vente défini
}
$codeCluster = $request->query->get('codeCluster');
$codeInsee = $request->query->get('codeInsee');
$departement = $request->query->get('departement');
$mois = (int)($request->query->get('month') ?? date('m'));
$annee = (int)($request->query->get('year') ?? date('Y'));
$perid = $request->query->get('perid');
$sellerId = $request->query->get('sellerId');
$category = $request->query->get('categoryId');
$optionSelect = $request->query->get('optionSelect', 'V');
$etatKO = $etatProductionRepository->findOneBy(["nom" => 'Racco KO']);
// -----------------------------------------
// 👥 Gestion hiérarchie
// -----------------------------------------
$childs = [];
if ($idUser = $request->query->get('idUser')) {
$user = $entityManager->getRepository(User::class)->find((int)$idUser);
if ($user && (in_array('ROLE_MANAGER', $user->getRoles()) || in_array('ROLE_DIRECTOR', $user->getRoles()))) {
$childs = array_map(fn($u) => (int)$u['id'], $hierarchyService->getHierarchyDescendante($user->getId()));
}
}
// -----------------------------------------
// 📊 Récupération des données
// -----------------------------------------
$ventesData = $productionRepository->getProductionsAnalyticsVentesOptimized(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
$optionSelect,
$category,
$etatKO,
$childs,
$organisationId,
$perid,
$sellerId,
$departement
);
$ventesConqVlaData = $productionRepository->getTotalVenteCnqVla(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
"B",
$childs,
$organisationId,
$perid,
$sellerId,
$departement
);
$objectifsData = $productionRepository->getProductionsAnalyticsObjectifsOptimized(
$pointOfSale,
$codeCluster,
$mois,
$annee,
$optionSelect,
null,
$category,
$childs,
$organisationId,
$departement
);
$mrzData = $productionRepository->getMrzList(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
$optionSelect,
$childs,
$organisationId,
$perid,
$sellerId,
$departement
);
// $fourPData = $productionRepository->get4P(
// $pointOfSale,
// $codeCluster,
// $codeInsee,
// $mois,
// $annee,
// $childs,
// $organisationId,
// $perid,
// $sellerId,
// $departement
// );
$fourPCData = $productionRepository->get4PC(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
$childs,
$organisationId,
$perid,
$sellerId,
$departement
);
$j30Data = $productionRepository->getJ30(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
$childs,
$organisationId,
$perid,
$sellerId,
$departement
);
$ptoData = $productionRepository->getPto(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
$childs,
$organisationId,
$perid,
$sellerId,
$departement
);
// Appel à la fonction de projection
$projection = $productionRepository->getProjection(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
$optionSelect,
$etatKO,
$category,
$childs,
$organisationId,
$perid,
$sellerId,
$departement
);
// -----------------------------------------
// 📊 Satisfaction client par cluster
// -----------------------------------------
$satisfactionMap = $satisfactionClientRepository->getSatisfaction(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
$category,
$childs,
$organisationId,
$perid,
$sellerId,
$departement
);
// -----------------------------------------
// 📊 Création des maps pour accès rapide
// -----------------------------------------
$objectifsMap = [];
foreach ($objectifsData as $obj) {
$key = $obj['codeCluster'] ?? null;
if ($key) $objectifsMap[$key] = (float)($obj['total_objectif'] ?? 0);
}
$mrzMap = [];
foreach ($mrzData as $obj) {
$cluster = $obj['codeCluster'] ?? null;
$identity = $obj['identity_ctrl'] ?? 'INCONNU';
$ventes = (float)($obj['total_ventes'] ?? 0);
if (!$cluster) continue;
if (!isset($mrzMap[$cluster])) $mrzMap[$cluster] = [];
if (!isset($mrzMap[$cluster][$identity])) {
$mrzMap[$cluster][$identity] = ['identity_ctrl' => $identity, 'total_ventes' => 0];
}
$mrzMap[$cluster][$identity]['total_ventes'] += $ventes;
}
foreach ($mrzMap as $cluster => $entries) $mrzMap[$cluster] = array_values($entries);
// $fourPMap = [];
// foreach ($fourPData as $f) {
// $key = $f['departement'] ?? null;
// if ($key) $fourPMap[$key] = (float)($f['total_vente'] ?? 0);
// }
$fourPCMap = [];
foreach ($fourPCData as $f) {
$key = $f['codeCluster'] ?? null;
if ($key) $fourPCMap[$key] = (float)($f['nombre_titulaire_email'] ?? 0);
}
$j30Map = [];
foreach ($j30Data as $row) {
$key = $row['codeCluster'] ?? null;
if ($key) {
$j30Map[$key] = [
'j30' => (int)($row['total_vente'] ?? 0),
'j30_raccorde_fixe' => (int)($row['total_vente_fixe_raccorde'] ?? 0),
'j30_mobile' => (int)($row['total_vente_mobile'] ?? 0),
'j30_raccorde_mobile' => (int)($row['total_vente_mobile_raccorde'] ?? 0),
];
}
}
$ventesConqVlaMap = [];
foreach ($ventesConqVlaData as $f) {
$key = $f['codeCluster'] ?? null;
if ($key) $ventesConqVlaMap[$key] = (int)($f['total_ventes_conq_vla'] ?? 0);
}
$ptoMap = [];
foreach ($ptoData as $ptoRow) {
$cluster = $ptoRow['codeCluster'] ?? null;
if ($cluster) {
$ptoMap[$cluster] = [
'rio' => (int)($ptoRow['total_rio'] ?? 0),
'pto_saisie' => (int)($ptoRow['total_pto_saisie'] ?? 0),
'pto_non_saisie' => (int)($ptoRow['total_pto_non_saisie'] ?? 0),
'pto_existante' => (int)($ptoRow['total_pto_existante'] ?? 0),
'pto_non_existante' => (int)($ptoRow['total_pto_non_existante'] ?? 0),
];
}
}
// -----------------------------------------
// 📈 Calcul des taux + enrichissement
// -----------------------------------------
foreach ($ventesData as &$row) {
$key = $row['codeCluster'] ?? null;
$key2 = $row['departement'] ?? null;
$totalObj = $objectifsMap[$key] ?? 0;
$ventes = (float)($row['total_ventes'] ?? 0);
$row['total_objectifs'] = $totalObj;
$row['R/O'] = $totalObj > 0 ? round(($ventes / $totalObj) * 100, 2) : 0;
$row['taux_ventes_raccordes_fixe'] = $ventes > 0 ? round(($row['total_ventes_raccordes_fixe'] / $ventes) * 100, 2) : 0;
$row['taux_ventes_raccorde_mobile'] = $ventes > 0 ? round(($row['total_ventes_raccorde_mobile'] / $ventes) * 100, 2) : 0;
$row['taux_ventes_box_5G'] = $ventes > 0 ? round(($row['total_ventes_box_5G'] / $ventes) * 100, 2) : 0;
$row['mrz'] = $mrzMap[$key] ?? [];
$row['4P'] = 0;
$row['4PC'] = $fourPCMap[$key] ?? 0;
$row['j30'] = $j30Map[$key]['j30'] ?? 0;
$row['j30_raccorde_fixe'] = $j30Map[$key]['j30_raccorde_fixe'] ?? 0;
$row['j30_mobile'] = $j30Map[$key]['j30_mobile'] ?? 0;
$row['j30_raccorde_mobile'] = $j30Map[$key]['j30_raccorde_mobile'] ?? 0;
// Churn
$total_ventes_raccordes_fixe = $row['total_ventes_raccordes_fixe'] ?? 0;
$j30_raccorde_fixe = $row['j30_raccorde_fixe'] ?? 0;
$row['churn_fixe'] = ($total_ventes_raccordes_fixe && $j30_raccorde_fixe)
? (float)number_format(($j30_raccorde_fixe / $total_ventes_raccordes_fixe) * 100, 1)
: 0;
$total_ventes_raccordes_mobile = $row['total_ventes_raccorde_mobile'] ?? 0;
$j30_raccorde_mobile = $row['j30_raccorde_mobile'] ?? 0;
$row['churn_mobile'] = ($total_ventes_raccordes_mobile && $j30_raccorde_mobile)
? (float)number_format(($j30_raccorde_mobile / $total_ventes_raccordes_mobile) * 100, 1)
: 0;
// PTO / RIO
$total_vente_vla_conquette = $ventesConqVlaMap[$key] ?? 0;
$ptoValues = $ptoMap[$key] ?? [
'rio' => 0,
'pto_saisie' => 0,
'pto_non_saisie' => 0,
'pto_existante' => 0,
'pto_non_existante' => 0
];
$rio = $ptoValues['rio'];
$pto_saisie = $ptoValues['pto_saisie'];
$pto_non_saisie = $ptoValues['pto_non_saisie'];
$pto_existante = $ptoValues['pto_existante'];
$pto_non_existante = $ptoValues['pto_non_existante'];
$row['RIO'] = ($total_vente_vla_conquette && $rio) ? round(($rio / $total_vente_vla_conquette) * 100, 2) : 0;
$row['PTO_SAISIE'] = ($total_vente_vla_conquette && $pto_saisie) ? round(($pto_saisie / $total_vente_vla_conquette) * 100, 2) : 0;
$row['PTO_NON_SAISIE'] = ($total_vente_vla_conquette && $pto_non_saisie) ? round(($pto_non_saisie / $total_vente_vla_conquette) * 100, 2) : 0;
$row['PTO_NON_EXISTANTE'] = ($total_vente_vla_conquette && $pto_non_existante) ? round(($pto_non_existante / $total_vente_vla_conquette) * 100, 2) : 0;
$row['Potentiel'] = ($pto_saisie && $pto_existante) ? round(($pto_saisie / $pto_existante) * 100, 2) : 0;
$row['PTO_EXISTANTE'] = ($total_vente_vla_conquette && $pto_existante) ? round(($pto_existante / $total_vente_vla_conquette) * 100, 2) : 0;
$row['satisfaction'] = $satisfactionMap[$key] ?? null;
$row['projection'] = $projection[$key];
}
return $this->json($ventesData, 200);
}
/**
* @Route("/api/productions-analytics-by-clusters", name="api_get_productions_analytics_by_clusters", methods={"GET"})
*/
public function getAnalyticsByClustersTest(
Request $request,
ProductionRepository $productionRepository,
PointOfSaleRepository $pointOfSaleRepository,
EntityManagerInterface $entityManager,
HierarchyService $hierarchyService,
EtatProductionRepository $etatProductionRepository,
SatisfactionClientRepository $satisfactionClientRepository
) {
// -----------------------------------------
// 🔹 Paramètres
// -----------------------------------------
$pointOfSaleId = $request->query->get('pointOfSaleId');
$organisationId = $request->query->get('organisationId');
$pointOfSale = null;
if ($pointOfSaleId) {
$pointOfSale = $pointOfSaleRepository->find($pointOfSaleId);
if (!$pointOfSale) {
throw new BadRequestHttpException("Le point de vente avec cet ID n'existe pas.");
}
$organisationId = null; // Ignorer organisation si point de vente défini
}
$codeCluster = $request->query->get('codeCluster');
$codeInsee = $request->query->get('codeInsee');
$departement = $request->query->get('departement');
$mois = (int)($request->query->get('month') ?? date('m'));
$annee = (int)($request->query->get('year') ?? date('Y'));
$perid = $request->query->get('perid');
$sellerId = $request->query->get('sellerId');
$category = $request->query->get('categoryId');
$optionSelect = $request->query->get('optionSelect', 'V');
$etatKO = $etatProductionRepository->findOneBy(["nom" => 'Racco KO']);
// -----------------------------------------
// 👥 Gestion hiérarchie
// -----------------------------------------
$childs = [];
if ($idUser = $request->query->get('idUser')) {
$user = $entityManager->getRepository(User::class)->find((int)$idUser);
if ($user && (in_array('ROLE_MANAGER', $user->getRoles()) || in_array('ROLE_DIRECTOR', $user->getRoles()))) {
$childs = array_map(fn($u) => (int)$u['id'], $hierarchyService->getHierarchyDescendante($user->getId()));
}
}
// -----------------------------------------
// 📊 Récupération des données
// -----------------------------------------
$ventesData = $productionRepository->getProductionsAnalyticsVentesOptimized2(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
$optionSelect,
$category,
$etatKO,
$childs,
$organisationId,
$perid,
$sellerId,
$departement
);
$ventesMobileData = $productionRepository->getProductionsAnalyticsVentesMobileOptimized(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
"B",
$childs,
$organisationId,
$perid,
$sellerId,
$departement
);
$ventesConqVlaData = $productionRepository->getTotalVenteCnqVla(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
"B",
$childs,
$organisationId,
$perid,
$sellerId,
$departement
);
$objectifsData = $productionRepository->getProductionsAnalyticsObjectifsOptimized(
$pointOfSale,
$codeCluster,
$mois,
$annee,
$optionSelect,
null,
$category,
$childs,
$organisationId,
$departement
);
$mrzData = $productionRepository->getMrzList(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
$optionSelect,
$childs,
$organisationId,
$perid,
$sellerId,
$departement
);
$fourPData = $productionRepository->get4P(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
$childs,
$organisationId,
$perid,
$sellerId,
$departement
);
$fourPCData = $productionRepository->get4PC(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
$childs,
$organisationId,
$perid,
$sellerId,
$departement
);
$j30Data = $productionRepository->getJ30(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
$childs,
$organisationId,
$perid,
$sellerId,
$departement
);
$j30MobileData = $productionRepository->getJ30Mobile(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
$childs,
$organisationId,
$perid,
$sellerId,
$departement
);
// dd($ventesMobileData, $j30MobileData);
$ptoData = $productionRepository->getPto(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
$childs,
$organisationId,
$perid,
$sellerId,
$departement
);
// Appel à la fonction de projection
$projection = $productionRepository->getProjection(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
$optionSelect,
$etatKO,
$category,
$childs,
$organisationId,
$perid,
$sellerId,
$departement
);
// -----------------------------------------
// 📊 Satisfaction client par cluster
// -----------------------------------------
$satisfactionMap = $satisfactionClientRepository->getSatisfaction(
$pointOfSale,
$codeCluster,
$codeInsee,
$mois,
$annee,
$category,
$childs,
$organisationId,
$perid,
$sellerId,
$departement
);
// -----------------------------------------
// 📊 Création des maps pour accès rapide
// -----------------------------------------
$ventesMobileMap = [];
foreach ($ventesMobileData as $row) {
$key = $row['departement'] ?? null;
if ($key) {
$ventesMobileMap[$key] = [
'total_ventes_box_5G' => (int)($row['total_ventes_box_5G'] ?? 0),
'total_ventes_raccorde_mobile' => (int)($row['total_ventes_raccorde_mobile'] ?? 0),
];
}
}
$objectifsMap = [];
foreach ($objectifsData as $obj) {
$key = $obj['codeCluster'] ?? null;
if ($key) $objectifsMap[$key] = (float)($obj['total_objectif'] ?? 0);
}
$mrzMap = [];
foreach ($mrzData as $obj) {
$cluster = $obj['codeCluster'] ?? null;
$identity = $obj['identity_ctrl'] ?? 'INCONNU';
$ventes = (float)($obj['total_ventes'] ?? 0);
if (!$cluster) continue;
if (!isset($mrzMap[$cluster])) $mrzMap[$cluster] = [];
if (!isset($mrzMap[$cluster][$identity])) {
$mrzMap[$cluster][$identity] = ['identity_ctrl' => $identity, 'total_ventes' => 0];
}
$mrzMap[$cluster][$identity]['total_ventes'] += $ventes;
}
foreach ($mrzMap as $cluster => $entries) $mrzMap[$cluster] = array_values($entries);
$fourPMap = [];
foreach ($fourPData as $f) {
$key = $f['departement'] ?? null;
if ($key) $fourPMap[$key] = (float)($f['total_vente'] ?? 0);
}
$fourPCMap = [];
foreach ($fourPCData as $f) {
$key = $f['departement'] ?? null;
if ($key) $fourPCMap[$key] = (float)($f['nombre_titulaire_email'] ?? 0);
}
$j30Map = [];
foreach ($j30Data as $row) {
$key = $row['codeCluster'] ?? null;
if ($key) {
$j30Map[$key] = [
'j30' => (int)($row['total_vente'] ?? 0),
'j30_raccorde_fixe' => (int)($row['total_vente_fixe_raccorde'] ?? 0),
];
}
}
$j30MobileMap = [];
foreach ($j30MobileData as $row) {
$key = $row['departement'] ?? null;
if ($key) {
$j30MobileMap[$key] = [
'j30_mobile' => (int)($row['total_vente_mobile'] ?? 0),
'j30_raccorde_mobile' => (int)($row['total_vente_mobile_raccorde'] ?? 0),
];
}
}
$ventesConqVlaMap = [];
foreach ($ventesConqVlaData as $f) {
$key = $f['codeCluster'] ?? null;
if ($key) $ventesConqVlaMap[$key] = (int)($f['total_ventes_conq_vla'] ?? 0);
}
$ptoMap = [];
foreach ($ptoData as $ptoRow) {
$cluster = $ptoRow['codeCluster'] ?? null;
if ($cluster) {
$ptoMap[$cluster] = [
'rio' => (int)($ptoRow['total_rio'] ?? 0),
'pto_saisie' => (int)($ptoRow['total_pto_saisie'] ?? 0),
'pto_non_saisie' => (int)($ptoRow['total_pto_non_saisie'] ?? 0),
'pto_existante' => (int)($ptoRow['total_pto_existante'] ?? 0),
'pto_non_existante' => (int)($ptoRow['total_pto_non_existante'] ?? 0),
];
}
}
// -----------------------------------------
// 📈 Calcul des taux + enrichissement
// -----------------------------------------
foreach ($ventesData as &$row) {
$key = $row['codeCluster'] ?? null;
$key_dep = $row['departement'] ?? null;
$totalObj = $objectifsMap[$key] ?? 0;
$ventes = (float)($row['total_ventes'] ?? 0);
$row['total_ventes_raccorde_mobile']=$ventesMobileMap[$key_dep]['total_ventes_raccorde_mobile']??0;
$row['total_ventes_box_5G']=$ventesMobileMap[$key_dep]['total_ventes_box_5G']??0;
$row['total_objectifs'] = $totalObj;
$row['R/O'] = $totalObj > 0 ? round(($ventes / $totalObj) * 100, 2) : 0;
$row['taux_ventes_raccordes_fixe'] = $ventes > 0 ? round(($row['total_ventes_raccordes_fixe'] / $ventes) * 100, 2) : 0;
$row['mrz'] = $mrzMap[$key] ?? [];
$row['4P'] = $fourPMap[$key_dep] ?? 0;
$row['4PC'] = $fourPCMap[$key_dep] ?? 0;
$row['j30'] = $j30Map[$key]['j30'] ?? 0;
$row['j30_raccorde_fixe'] = $j30Map[$key]['j30_raccorde_fixe'] ?? 0;
$row['j30_mobile'] = $j30MobileMap[$key_dep]['j30_mobile'] ?? 0;
$row['j30_raccorde_mobile'] = $j30MobileMap[$key_dep]['j30_raccorde_mobile'] ?? 0;
// Churn
$total_ventes_raccordes_fixe = $row['total_ventes_raccordes_fixe'] ?? 0;
$j30_raccorde_fixe = $row['j30_raccorde_fixe'] ?? 0;
$row['churn_fixe'] = ($total_ventes_raccordes_fixe && $j30_raccorde_fixe)
? (float)number_format(($j30_raccorde_fixe / $total_ventes_raccordes_fixe) * 100, 1)
: 0;
$total_ventes_raccordes_mobile = $row['total_ventes_raccorde_mobile'] ?? 0;
$j30_raccorde_mobile = $row['j30_raccorde_mobile'] ?? 0;
$row['churn_mobile'] = ($total_ventes_raccordes_mobile && $j30_raccorde_mobile)
? (float)number_format(($j30_raccorde_mobile / $total_ventes_raccordes_mobile) * 100, 1)
: 0;
// PTO / RIO
$total_vente_vla_conquette = $ventesConqVlaMap[$key] ?? 0;
$ptoValues = $ptoMap[$key] ?? [
'rio' => 0,
'pto_saisie' => 0,
'pto_non_saisie' => 0,
'pto_existante' => 0,
'pto_non_existante' => 0
];
$rio = $ptoValues['rio'];
$pto_saisie = $ptoValues['pto_saisie'];
$pto_non_saisie = $ptoValues['pto_non_saisie'];
$pto_existante = $ptoValues['pto_existante'];
$pto_non_existante = $ptoValues['pto_non_existante'];
$row['RIO'] = ($total_vente_vla_conquette && $rio) ? round(($rio / $total_vente_vla_conquette) * 100, 2) : 0;
$row['PTO_SAISIE'] = ($total_vente_vla_conquette && $pto_saisie) ? round(($pto_saisie / $total_vente_vla_conquette) * 100, 2) : 0;
$row['PTO_NON_SAISIE'] = ($total_vente_vla_conquette && $pto_non_saisie) ? round(($pto_non_saisie / $total_vente_vla_conquette) * 100, 2) : 0;
$row['PTO_NON_EXISTANTE'] = ($total_vente_vla_conquette && $pto_non_existante) ? round(($pto_non_existante / $total_vente_vla_conquette) * 100, 2) : 0;
$row['Potentiel'] = ($pto_saisie && $pto_existante) ? round(($pto_saisie / $pto_existante) * 100, 2) : 0;
$row['PTO_EXISTANTE'] = ($total_vente_vla_conquette && $pto_existante) ? round(($pto_existante / $total_vente_vla_conquette) * 100, 2) : 0;
// dd($row,$total_vente_vla_conquette,$rio, $row['RIO']);
$row['satisfaction'] = $satisfactionMap[$key] ?? null;
$row['projection'] = $projection[$key];
}
return $this->json($ventesData, 200);
}
}

src/Controller/Api/Production/ProductionAnalyticsController.php line 126