<?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\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."
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.utilisateur 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("/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."
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,
CASE prospect.email WHEN '' then 0 ELSE 1 END AS avecemail,
CASE prospect.standard WHEN '' then 0 ELSE 1 END AS avecstandard
FROM ".Prospect::class." as prospect
GROUP BY avecemail,avecstandard";
$sqlcontacts = "SELECT
COUNT(contact.id) AS total,
CASE contact.email WHEN '' then 0 ELSE 1 END AS avecemail,
CASE contact.fixe WHEN '' then 0 ELSE 1 END AS avecfixe,
CASE contact.mobile WHEN '' then 0 ELSE 1 END AS avecmobile
FROM ".Contact::class." as contact
GROUP BY avecemail,avecfixe,avecmobile";
$queryprospects = $this->em->createQuery($sqlprospects);
$querycontacts = $this->em->createQuery($sqlcontacts);
$prospects = $queryprospects->getResult();
$contacts = $querycontacts->getResult();
$reponse = [
"prospects" => $prospects,
"contacts" => $contacts,
];
return $this->apiOk($reponse);
}
/**
* @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"})
* @return JsonResponse
*/
public function prospectscp()
{
$sqltotal = "SELECT
COUNT(prospect.id) as total,
SUBSTRING(ville.codepostal,1,2) as cp
FROM ".Prospect::class." as prospect
LEFT JOIN ".Ville::class." as ville WITH ville.id = prospect.ville
GROUP BY cp";
$sqlavecemail = "SELECT
COUNT(prospect.id) as total,
SUBSTRING(ville.codepostal,1,2) as cp
FROM ".Prospect::class." as prospect
LEFT JOIN ".Ville::class." as ville WITH ville.id = prospect.ville
WHERE prospect.email not like ''
GROUP BY cp";
$sqlavecstandard = "SELECT
COUNT(prospect.id) as total,
SUBSTRING(ville.codepostal,1,2) as cp
FROM ".Prospect::class." as prospect
LEFT JOIN ".Ville::class." as ville WITH ville.id = prospect.ville
WHERE prospect.standard not like ''
GROUP BY cp";
$querytotal = $this->em->createQuery($sqltotal);
$queryavecemail = $this->em->createQuery($sqlavecemail);
$queryavecstandard = $this->em->createQuery($sqlavecstandard);
$total = $querytotal->getResult();
$totalavecemail = $queryavecemail->getResult();
$totalavecstandard = $queryavecstandard->getResult();
$reponse = [
"total" => $total,
"totalavecemail" => $totalavecemail,
"totalavecstandard" => $totalavecstandard,
];
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;
}
}