Symfony Profiler

<?php
namespace App\Controller\Api\Prive;
use App\Entity\Appel;
use App\Entity\Appelstatut;
use App\Entity\Campagne;
use App\Entity\Campagneprospect;
use App\Entity\Client;
use App\Entity\Codenaf;
use App\Entity\Contact;
use App\Entity\Prospect;
use App\Entity\Rdv;
use App\Entity\Rdvstatut;
use App\Entity\Utilisateur;
use App\Entity\Ville;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\Routing\Annotation\Route;
/**
* @Route("/statistique")
*/
class ApiStatistiqueController extends ApiController
{
/**
* @Route("/appelparstatut", name="api_Statistique_appelparstatut", methods={"POST"})
* @param Request $request
* @return JsonResponse
*/
public function appelparstatut(Request $request)
{
$data = json_decode($request->getContent(), true);
$date = date("Ymd", strtotime("-30 days", strtotime(date("Ymd"))));
$where = " WHERE appel.dateappel >= ".$date;
if (isset($data['params']['collaborateurId'])) {
$where .= ' AND appel.utilisateur='.$data['params']['collaborateurId'];
}else {
if (isset($data['params']['espaceagent']) && $data['params']['espaceagent']) {
$where .= ' AND appel.utilisateur='.$this->getUtilisateurConnecte()->getId();
}
}
$sql = "
SELECT
DATE_FORMAT(appel.dateappel, '%d/%m/%Y') AS jour,
appelstatut.libelle AS statutappel,
appelstatut.couleur AS couleurstatut,
count(appel.id) AS nombreappel
FROM ".Appel::class." AS appel
LEFT JOIN ".Appelstatut::class." as appelstatut WITH appel.appelstatut = appelstatut.id
LEFT JOIN ".Campagneprospect::class." AS campagneprospect WITH appel.campagneprospect = campagneprospect.id
LEFT JOIN ".Campagne::class." AS campagne WITH campagneprospect.campagne = campagne.id
LEFT JOIN ".Client::class." AS client WITH campagne.client = client.id
".$where."
AND (appel.sourceouverture IS NULL OR appel.sourceouverture <> '".Appel::SOURCE_REQUALIFICATION_RESPONSABLE."')
GROUP BY jour, statutappel
ORDER BY appel.dateappel asc
";
$query = $this->em->createQuery($sql);
$reponse = $query->getResult();
return $this->apiOk($reponse);
}
/**
* @Route("/campagneparstatut/{id}", name="api_Statistique_campagneparstatut", methods={"GET"})
* @param int $id
* @return JsonResponse
*/
public function campagneparstatut(int $id)
{
if ($id == null || $id <= 0) {
return $this->apiKo("Campagne non-trouvée !");
}
$where = "WHERE campagneprospect.campagne = ".$id ;
$sql = "
SELECT
(case when appelstatut.couleur IS NULL then '#7c65ad' ELSE appelstatut.couleur END) AS couleurstatut ,
(case when appelstatut.libelle IS NULL then 'Pas encore contacté' ELSE appelstatut.libelle END) AS statutappel ,
COUNT(campagneprospect.id) AS nombreprospect
FROM
".Campagneprospect::class." AS campagneprospect
LEFT join ".Appelstatut::class." AS appelstatut WITH appelstatut.id = campagneprospect.appelstatut
".$where."
GROUP BY appelstatut.libelle, appelstatut.couleur
";
$query = $this->em->createQuery($sql);
$reponse = $query->getResult();
return $this->apiOk($reponse);
}
/**
* @Route("/avancementcampagneappel", name="api_Statistique_avancementcampagneappel", methods={"POST"})
* @param Request $request
* @return JsonResponse
*/
public function avancementcampagneappel(Request $request)
{
$data = json_decode($request->getContent(), true);
$where = $this->paramstowhere($data);
$sql = "SELECT
(case when campagneprospect.traitementpar IS NULL then 'NON' ELSE 'OUI' END) AS estcontacte,
COUNT(campagneprospect.id) AS nombreprospect
FROM
".Campagneprospect::class." AS campagneprospect
".$where."
GROUP BY estcontacte
";
$query = $this->em->createQuery($sql);
$reponse = $query->getResult();
return $this->apiOk($reponse);
}
/**
* @Route("/avancementcampagneobjectif", name="api_Statistique_avancementcampagneobjectif", methods={"POST"})
* @param Request $request
* @return JsonResponse
*/
public function avancementcampagneobjectif(Request $request)
{
$data = json_decode($request->getContent(), true);
$where = $this->paramstowhere($data);
$sql = "SELECT
campagne.id AS idcampagne,
campagne.objectif AS totalarealiser,
COUNT(rdv.id) AS totalrealise
FROM
".Rdv::class." AS rdv
LEFT JOIN ".Rdvstatut::class." AS rdvstatut WITH rdv.rdvstatut = rdvstatut.id
LEFT JOIN ".Campagne::class." AS campagne WITH rdv.campagne = campagne.id
".$where."
GROUP BY campagne.id";
$query = $this->em->createQuery($sql);
$reponse = $query->getResult();
return $this->apiOk($reponse);
}
/**
* @Route("/collaborateurappelstatut", name="api_Statistique_collaborateurappelstatut", methods={"POST"})
* @param Request $request
* @return JsonResponse
*/
public function collaborateurappelstatut(Request $request)
{
$data = json_decode($request->getContent(), true);
$where = $this->paramstowhere($data);
$sql = "SELECT
CASE WHEN utilisateur.id IS NULL then 0 ELSE utilisateur.id end AS idcollaborateur ,
CASE WHEN utilisateur.id IS NULL then 'PAS ENCORE AFFECTE' ELSE UPPER(concat(utilisateur.nom,' ',utilisateur.prenom)) end AS collaborateur ,
CASE WHEN appelstatut.id IS NULL then 0 ELSE appelstatut.id end AS idappelstatut ,
CASE WHEN appelstatut.id IS NULL then 'PAS ENCORE APPELE' ELSE UPPER(appelstatut.libelle) end AS statut ,
COUNT(campagneprospect.id) AS nombreprospect
FROM
".Campagneprospect::class." AS campagneprospect
LEFT JOIN ".Utilisateur::class." AS utilisateur WITH campagneprospect.traitementpar = utilisateur.id
LEFT JOIN ".Appelstatut::class." AS appelstatut WITH campagneprospect.appelstatut = appelstatut.id
".$where."
GROUP BY utilisateur.id,appelstatut.id";
$query = $this->em->createQuery($sql);
$reponse = $query->getResult();
return $this->apiOk($reponse);
}
/**
* @Route("/previsionrappel", name="api_Statistique_previsionrappel", methods={"POST"})
* @param Request $request
* @return JsonResponse
*/
public function previsionrappel(Request $request)
{
$data = json_decode($request->getContent(), true);
$where = $this->paramstowhere($data);
// Rappels passés
$sql0 = "SELECT
'Rappels ratés' AS periode,
COUNT(campagneprospect.id) AS nombre
FROM
".Campagneprospect::class." AS campagneprospect
LEFT JOIN ".Appelstatut::class." AS appelstatut WITH campagneprospect.appelstatut = appelstatut.id
".$where."
AND appelstatut.rappel=TRUE AND campagneprospect.daterappel < now()
GROUP BY periode
";
$sql0_ids = "SELECT distinct
campagneprospect.id AS ids
FROM
".Campagneprospect::class." AS campagneprospect
LEFT JOIN ".Appelstatut::class." AS appelstatut WITH campagneprospect.appelstatut = appelstatut.id
".$where."
AND appelstatut.rappel=TRUE AND campagneprospect.daterappel < now()
";
$query0 = $this->em->createQuery($sql0);
$query0_ids = $this->em->createQuery($sql0_ids);
// Prochaine 5jours
$sql1 = "SELECT
DATE(campagneprospect.daterappel) AS periode,
COUNT(campagneprospect.id) AS nombre
FROM
".Campagneprospect::class." AS campagneprospect
LEFT JOIN ".Appelstatut::class." AS appelstatut WITH campagneprospect.appelstatut = appelstatut.id
".$where."
AND appelstatut.rappel=TRUE AND campagneprospect.daterappel >= now()
GROUP BY periode
ORDER BY periode ASC
";
$query1 = $this->em->createQuery($sql1);
$query1->setFirstResult(0);
$query1->setMaxResults(5);
// Par mois
$sql2 = "SELECT
date_format(campagneprospect.daterappel,'%Y-%m') AS periode,
COUNT(campagneprospect.id) AS nombre
FROM
".Campagneprospect::class." AS campagneprospect
LEFT JOIN ".Appelstatut::class." AS appelstatut WITH campagneprospect.appelstatut = appelstatut.id
".$where."
AND appelstatut.rappel=TRUE AND campagneprospect.daterappel >= now()
GROUP BY periode
ORDER BY periode ASC
";
$query2 = $this->em->createQuery($sql2);
$reponse0 = $query0->getResult();
$reponse0_ids = $query0_ids->getResult();
$reponse1 = $query1->getResult();
$reponse2 = $query2->getResult();
$reponse[] = [
"id" => 1,
"data"=> $reponse0,
"ids" => array_column($reponse0_ids, 'ids')
];
$reponse[] = [
"id" => 2,
"data"=> $reponse1,
"ids" => []
];
$reponse[] = [
"id" => 3,
"data"=> $reponse2,
"ids" => []
];
return $this->apiOk($reponse);
}
/**
* @Route("/collaborateurappels", name="api_Statistique_collaborateurappels", methods={"POST"})
* @param Request $request
* @return JsonResponse
*/
public function collaborateurappels(Request $request)
{
$data = json_decode($request->getContent(), true);
$where = $this->paramstowhere($data);
$sql = "SELECT
utilisateur.id AS idutilisateur,
CONCAT(utilisateur.nom,' ',utilisateur.prenom) AS nomutilisateur,
DATE_FORMAT(appel.dateappel, '%Y-%m-%d') AS datetraitement,
COUNT(appel.id) AS nombreappel,
SUM(appel.dureeappel) dureetraitement
FROM ".Appel::class." as appel
LEFT JOIN ".Campagneprospect::class." as campagneprospect WITH campagneprospect.id = appel.campagneprospect
LEFT JOIN ".Utilisateur::class." as utilisateur WITH utilisateur.id = appel.utilisateur
".$where."
AND (appel.sourceouverture IS NULL OR appel.sourceouverture <> '".Appel::SOURCE_REQUALIFICATION_RESPONSABLE."')
GROUP BY idutilisateur,nomutilisateur, datetraitement
ORDER BY appel.dateappel asc";
$query = $this->em->createQuery($sql);
$reponse = $query->getResult();
return $this->apiOk($reponse);
}
////////////////////////////STATISTIQUE GLOBAL ////////////////////////
/**
* @Route("/prospectsglobal", name="api_Statistique_prospectsglobal", methods={"GET"})
* @return JsonResponse
*/
public function prospectsglobal()
{
$sqlprospects = "SELECT
COUNT(prospect.id) AS total,
SUM(CASE WHEN prospect.email IS NOT NULL AND prospect.email <> '' THEN 1 ELSE 0 END) AS avecEmail,
SUM(CASE WHEN prospect.standard IS NOT NULL AND prospect.standard <> '' THEN 1 ELSE 0 END) AS avecStandard,
SUM(CASE WHEN complement.statutSynchronisation = 'SUCCES' THEN 1 ELSE 0 END) AS enrichis,
SUM(CASE WHEN complement.statutSynchronisation = 'SIREN_INVALIDE' THEN 1 ELSE 0 END) AS sirenInvalides
FROM ".Prospect::class." prospect
LEFT JOIN prospect.prospectcomplement complement
WHERE prospect.active = 1";
$sqlRepartitionStructures = "SELECT
CASE
WHEN complementStructure.statutSynchronisation = 'SUCCES'
AND (complementStructure.estAdministration = true
OR complementStructure.natureJuridique LIKE '7%')
THEN 'PUBLIC'
WHEN complementStructure.statutSynchronisation = 'SUCCES'
AND complementStructure.estAdministration = false
AND complementStructure.natureJuridique IS NOT NULL
THEN 'PRIVE'
WHEN prospectStructure.typeStructure IN ('PUBLIC', 'PRIVE')
THEN prospectStructure.typeStructure
WHEN codenafStructure.classificationStructure IN ('PUBLIC', 'PRIVE')
THEN codenafStructure.classificationStructure
ELSE 'INDETERMINE'
END AS typeStructureEffectif,
COUNT(prospectStructure.id) AS nombre
FROM ".Prospect::class." prospectStructure
LEFT JOIN prospectStructure.prospectcomplement complementStructure
LEFT JOIN prospectStructure.codenaf codenafStructure
WHERE prospectStructure.active = 1
GROUP BY typeStructureEffectif";
$sqlprospectsInactifs = "SELECT COUNT(prospect.id)
FROM ".Prospect::class." prospect
WHERE prospect.active = 0";
$sqlcontacts = "SELECT
COUNT(contact.id) AS total,
SUM(CASE WHEN contact.email IS NOT NULL AND contact.email <> '' THEN 1 ELSE 0 END) AS avecEmail,
SUM(CASE WHEN contact.fixe IS NOT NULL AND contact.fixe <> '' THEN 1 ELSE 0 END) AS avecFixe,
SUM(CASE WHEN contact.mobile IS NOT NULL AND contact.mobile <> '' THEN 1 ELSE 0 END) AS avecMobile
FROM ".Contact::class." contact
INNER JOIN contact.prospect prospect
WHERE contact.active = 1
AND prospect.active = 1";
$sqlcontactsInactifs = "SELECT COUNT(contact.id)
FROM ".Contact::class." contact
INNER JOIN contact.prospect prospect
WHERE contact.active <> 2
AND prospect.active <> 2
AND (contact.active = 0 OR prospect.active = 0)";
$prospects = $this->normaliserCompteurs(
$this->em->createQuery($sqlprospects)->getSingleResult()
);
$prospects['publics'] = 0;
$prospects['prives'] = 0;
$prospects['indetermines'] = 0;
foreach ($this->em->createQuery($sqlRepartitionStructures)->getArrayResult() as $repartition) {
$type = strtoupper((string)($repartition['typeStructureEffectif'] ?? 'INDETERMINE'));
$cle = $type === 'PUBLIC' ? 'publics' : ($type === 'PRIVE' ? 'prives' : 'indetermines');
$prospects[$cle] += (int)($repartition['nombre'] ?? 0);
}
$contacts = $this->normaliserCompteurs(
$this->em->createQuery($sqlcontacts)->getSingleResult()
);
$prospects['inactifs'] = (int)$this->em->createQuery($sqlprospectsInactifs)->getSingleScalarResult();
$contacts['inactifs'] = (int)$this->em->createQuery($sqlcontactsInactifs)->getSingleScalarResult();
$prospects['nonEnrichis'] = max(0, $prospects['total'] - $prospects['enrichis']);
return $this->apiOk([
"prospects" => $prospects,
"contacts" => $contacts,
]);
}
private function normaliserCompteurs(array $compteurs): array
{
foreach ($compteurs as $cle => $valeur) {
$compteurs[$cle] = (int)$valeur;
}
return $compteurs;
}
/**
* @Route("/prospectsevolution", name="api_Statistique_prospectsevolution", methods={"POST"})
* @param Request $request
* @return JsonResponse
*/
public function prospectsevolution(Request $request)
{
$date = date("Ymd", strtotime("-365 days", strtotime(date("Ymd"))));
$where = " WHERE appel.dateappel >= ".$date;
$sqlcreation = "SELECT
COUNT(prospect.id) AS total,
year(prospect.datecreation) AS annee,
month(prospect.datecreation) as mois
FROM ".Prospect::class." as prospect
WHERE prospect.datecreation >= ".$date."
GROUP BY annee,mois
ORDER BY annee, mois asc";
$sqlmodification = "SELECT
COUNT(prospect.id) AS total,
year(prospect.datemodification) AS annee,
month(prospect.datemodification) as mois
FROM ".Prospect::class." as prospect
WHERE prospect.datemodification >= ".$date."
GROUP BY annee,mois
ORDER BY annee, mois asc";
$querycreation = $this->em->createQuery($sqlcreation);
$querymodification = $this->em->createQuery($sqlmodification);
$creation = $querycreation->getResult();
$modification = $querymodification->getResult();
$reponse = [
"creation" => $creation,
"modification" => $modification,
];
return $this->apiOk($reponse);
}
/**
* @Route("/prospectscp", name="api_Statistique_prospectscp", methods={"GET"})
* Query string:
* - axe=cp (default): regroupement par code postal + ville
* - axe=naf: regroupement par code NAF
* - all=0 (default): top 10; all=1: tous les resultats
* @return JsonResponse
*/
public function prospectscp(Request $request)
{
$axe = strtolower(trim((string)$request->query->get('axe', 'cp')));
if (!in_array($axe, ['cp', 'naf'], true)) {
$axe = 'cp';
}
$afficherTout = $request->query->getBoolean('all', false);
// Keep "cp" key in response for backward compatibility with existing frontend contract.
$regroupementCodeExpression = "ville.codepostal";
$regroupementLibelleExpression = "ville.libelle";
$jointureRegroupement = "LEFT JOIN ".Ville::class." as ville WITH ville.id = prospect.ville";
if ($axe === 'naf') {
$regroupementCodeExpression = "codenaf.code";
$regroupementLibelleExpression = "codenaf.libelle";
$jointureRegroupement = "LEFT JOIN ".Codenaf::class." as codenaf WITH codenaf.id = prospect.codenaf";
}
$sqltotal = "SELECT
COUNT(prospect.id) as total,
SUM(CASE WHEN prospect.email <> '' THEN 1 ELSE 0 END) as totalavecemail,
SUM(CASE WHEN prospect.standard <> '' THEN 1 ELSE 0 END) as totalavecstandard,
".$regroupementCodeExpression." as cp,
".$regroupementLibelleExpression." as libelle
FROM ".Prospect::class." as prospect
".$jointureRegroupement."
GROUP BY cp, libelle
ORDER BY total DESC, cp ASC, libelle ASC";
$querytotal = $this->em->createQuery($sqltotal);
if (!$afficherTout) {
// Retrieve one extra row to tell the frontend whether "Afficher plus" is useful.
$querytotal->setMaxResults(11);
}
$resultats = $querytotal->getResult();
$hasMore = !$afficherTout && count($resultats) > 10;
if ($hasMore) {
$resultats = array_slice($resultats, 0, 10);
}
$total = [];
$totalavecemail = [];
$totalavecstandard = [];
foreach ($resultats as $resultat) {
$groupe = [
'cp' => $resultat['cp'],
'libelle' => $resultat['libelle'],
];
$total[] = $groupe + ['total' => (int)$resultat['total']];
$totalavecemail[] = $groupe + ['total' => (int)$resultat['totalavecemail']];
$totalavecstandard[] = $groupe + ['total' => (int)$resultat['totalavecstandard']];
}
$reponse = [
"total" => $total,
"totalavecemail" => $totalavecemail,
"totalavecstandard" => $totalavecstandard,
"hasMore" => $hasMore,
];
return $this->apiOk($reponse);
}
/***************************** HELPERS *****************/
/**
* @param array $data
* @return string
*/
public function paramstowhere(array $data) : string{
//AND
$and = " 1=1";
if (isset($data['filter']['and'])) {
$filtres = $data['filter']['and'];
if (sizeof($filtres) != 0) {
$and = "( 1=1";
foreach ($filtres as $key => $value) {
$and .= " AND " . $key . " " . $value;
}
$and .= " )";
}
}
//OR
$or = "";
if (isset($data['filter']['or'])) {
$filtres = $data['filter']['or'];
if (sizeof($filtres) != 0) {
$or = " ( 1=0";
foreach ($filtres as $key => $value) {
$or .= " OR " . $key . " " . $value;
}
$or .= " )";
}
}
$where = " WHERE ".$and;
if ($or != "") {
$where .= " AND " . $or;
}
return $where;
}
}

src/Controller/Api/Prive/ApiStatistiqueController.php line 42