Symfony Profiler

<?php
namespace App\Controller\Homepage;
use App\Entity\Main\Client\Advertisers;
use App\Service\AppnexusService;
use App\Entity\Main\Xandr\NetworkAnalytics;
use App\Entity\Main\Xandr\NetworkAnalyticsBasic;
use App\Entity\BackofficeProjet\Trafficking\Implementation;
use App\Entity\Main\SelfService\PurchasedCampaign;
use App\Entity\Main\Xandr\SegmentLoad;
use Symfony\Component\Mercure\Update;
use App\Entity\Main\Client\AdvertisersThemed;
use Symfony\Component\Mercure\Publisher;
use Symfony\Component\HttpFoundation\Request;
use App\Entity\BackofficeProjet\Administration\ProjetsGestionAcces;
use App\Service\NotificationService;
use App\Entity\Main\Administration\User;
use App\Entity\Main\Activation\ProjetsOldActivation;
use App\Entity\DashboardDbm\ReportingNetwork;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Annotation\Route;
use Symfony\Component\Mercure\Jwt\StaticJwtProvider;
use Symfony\Component\HttpFoundation\Session\Session;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Doctrine\Persistence\ManagerRegistry;
use Symfony\Contracts\Translation\TranslatorInterface;
use \App\Entity\Main\Administration\WhitelistAuthorizationUsersBackofficeAccessControl;
use App\Service\RssService;
use App\Entity\DashboardSystem\Client\Clients;
use App\Entity\Main\SelfService\Abonnement;
use App\Entity\Main\SelfService\Wallet;
use App\Entity\Main\SelfService\GrandsComptes;
use Symfony\Component\Security\Core\Authentication\Token\Storage\TokenStorageInterface;
use App\Service\MercureCookieGenerator;
class IndexController extends AbstractController
{
private $userRule = "";
private $notificationService;
private $em;
private $emDashboardSystem;
private $emDashboardFacebook;
private $emBackofficeProjet;
private $emDashboardDbm;
private $userActive;
private $rssService;
public function __construct(ManagerRegistry $em, TokenStorageInterface $tokenStorage, NotificationService $notificationService, RssService $rssService)
{
$this->userRule = $tokenStorage->getToken()->getUser()->getRules();
$this->userActive = $tokenStorage->getToken()->getUser()->getIsActive();
if ($this->userRule == "ROLE_CLIENT") {
session_destroy();
header('Location: https://laplateforme.tradespotting.com');
die();
}
if ($this->userRule == "ROLE_PIMS") {
session_destroy();
header('Location: https://laplateforme.tradespotting.com');
die();
}
if ($this->userActive == 0) {
session_destroy();
header('Location: https://tradespotting.com');
die();
}
$this->rssService = $rssService;
$this->em = $em->getManager();
$this->emDashboardSystem = $em->getManager('dashboard_system');
$this->emDashboardFacebook = $em->getManager('dashboard_facebook');
$this->emBackofficeProjet = $em->getManager('backoffice_projet');
$this->emDashboardDbm = $em->getManager('dashboard_dbm');
}
//Regarde les accès que l'utilisateur possède, puis l'enregistre sur le navigateur via JWT
private function convertUserAccessToJWT()
{
$entityManager = $this->em;
$accesscontrol = $entityManager->getRepository(WhitelistAuthorizationUsersBackofficeAccessControl::class)->findBy(array("role" => $this->userRule));
$soleil = (array) $accesscontrol[0];
$accessControlSimplified = array();
foreach ($soleil as $key => $value) {
if ($value) {
$accessControlSimplified[substr($key, 82)] = $value;
}
}
$accesscontrol = $this->emBackofficeProjet->getRepository(ProjetsGestionAcces::class)->findBy(array("rule" => $this->userRule));
if (!empty($accesscontrol)) {
foreach ((array) $accesscontrol[0] as $key => $value) {
if ($value) {
$accessControlSimplified['GP' . substr($key, 44)] = $value;
}
}
} else {
$accessControlSimplified[0] = 0;
}
$session = new Session();
$session->set('userId', $this->get('security.token_storage')->getToken()->getUser()->getId());
$cancer = str_replace('\u0000', "", json_encode($accessControlSimplified));
// Create token header as a JSON string
$header = json_encode(['typ' => 'JWT', 'alg' => 'HS256']);
// Create token payload as a JSON string
$payload = $cancer;
// Encode Header to Base64Url String
$base64UrlHeader = str_replace(['+', '/', '='], ['-', '_', ''], base64_encode($header));
// Encode Payload to Base64Url String
$base64UrlPayload = str_replace(['+', '/', '='], ['-', '_', ''], base64_encode($payload));
// Create Signature Hash
$signature = hash_hmac('sha256', $base64UrlHeader . "." . $base64UrlPayload, 'pano561zef@déédéramanesaitpasimplemlespixels!', true);
// Encode Signature to Base64Url String
$base64UrlSignature = str_replace(['+', '/', '='], ['-', '_', ''], base64_encode($signature));
// Create JWT
$jwt = $base64UrlHeader . "." . $base64UrlPayload . "." . $base64UrlSignature;
setcookie('token', $jwt, time() + 36000);
return $jwt;
}
/**
* @Route("/isConnected", name="isConnected")
*/
public function isConnected()
{
return new Response(
1,
Response::HTTP_OK,
['content-type' => 'application/json']
);
}
/**
* @Route("/", name="redirectionBackup")
*/
public function redirectionBackup()
{
return $this->redirect($this->generateUrl('index'));
}
/**
* @Route("/logoff", name="logoff")
*/
public function logoff()
{
if (isset($_SERVER['HTTP_COOKIE'])) {
$cookies = explode(';', $_SERVER['HTTP_COOKIE']);
foreach ($cookies as $cookie) {
$parts = explode('=', $cookie);
$name = trim($parts[0]);
setcookie($name, '', time() - 1000);
setcookie($name, '', time() - 1000, '/');
}
}
session_destroy();
return new Response(
1,
Response::HTTP_OK,
['content-type' => 'application/json']
);
}
/**
* @Route("/lazyload/index", name="indexLazyload")
*/
public function indexLazyload(TranslatorInterface $translator)
{
$entityManager = $this->em;
//Declaration pour le QueryBuilder
$dm = $this->em;
$qb = $dm->createQueryBuilder();
//Le nombre total d'imp
$nbImpsTotal = $this->em->getRepository(NetworkAnalyticsBasic::class)->totalImps();
//Le nombre total de campagne effectuée
$nbCampaignTotal = $this->em->getRepository(NetworkAnalyticsBasic::class)->totalCampaignDistinct();
//Le nombre de cookie en base
$nbCookieTotal = $this->em->getRepository(SegmentLoad::class)->totalCookie();
//Le nombre de pixel en base
$nbPixelTotal = $this->em->getRepository(SegmentLoad::class)->nbPixelDistinct();
//Le nombre de client
$nbClient = $this->em->getRepository(Advertisers::class)->nombreTotalClientDistinct();
//Le nombre de campagne self-service
$nbCampaignSelfService = $this->em->getRepository(PurchasedCampaign::class)->nbCampaignDistinct();
//Le nombre d'utilisateur activé
$nbActiveUser = $this->em->getRepository(User::class)->countUsersByActive(1);
//Le nombre de projet activation en cours
$nbProjetsEnCours = $this->em->getRepository(ProjetsOldActivation::class)->countActivationActive();
//Le nombre de projet activation a activé
$nbProjetsAActiver = $this->em->getRepository(ProjetsOldActivation::class)->countActivationAActiver();
//Le nombre de projet activation en attente
$nbProjetsEnAttente = $this->em->getRepository(ProjetsOldActivation::class)->countActivationEnAttente();
//Le nombre de go tech
$nbGoTech = $this->emBackofficeProjet->getRepository(Implementation::class)->countGoTech();
//Le nombre de sujet implémentation en cours
$nbImplementationEnCours = $this->emBackofficeProjet->getRepository(Implementation::class)->countEnCours();
//Le nombre de Grands Compte Actif
$nbGcActif = $this->em->getRepository(GrandsComptes::class)->countActiveGC();
//Le nombre de wallets credités
$nbWalletsCredites = $this->em->getRepository(Wallet::class)->countWalletsCredites();
//Le nombre d'abonnement actif
$nbAbonnementActif = $this->em->getRepository(Abonnement::class)->countActiveAbonnement();
//Le nombre de key client actif
$nbKeyClientActif = $this->emDashboardSystem->getRepository(Clients::class)->countActiveKeyClients();
//Le nombre de client actif
$nbClientActif = $this->emDashboardSystem->getRepository(Clients::class)->countActive();
//Le nombre de client actif
$nbCampagneSelfClient = $this->em->getRepository(PurchasedCampaign::class)->countAllPaidCampaignWithoutTest();
return new Response(
json_encode(array(
'nbImpsTotal' => $nbImpsTotal,
'nbCampaignTotal' => $nbCampaignTotal,
'nbCookieTotal' => $nbCookieTotal,
'nbPixelTotal' => $nbPixelTotal,
'nbClient' => $nbClient,
'nbProjetsEnCours' => $nbProjetsEnCours,
'nbProjetsAActiver' => $nbProjetsAActiver,
'nbActiveUser' => $nbActiveUser,
'nbCampaignSelfService' => $nbCampaignSelfService,
'nbGoTech' => $nbGoTech,
'nbGcActif' => $nbGcActif,
'nbCampagneSelfClient' => $nbCampagneSelfClient,
'nbWalletsCredites' => $nbWalletsCredites,
'nbAbonnementActif' => $nbAbonnementActif,
'nbKeyClientActif' => $nbKeyClientActif,
'nbImplementationEnCours' => $nbImplementationEnCours,
'nbClientActif' => $nbClientActif,
'nbProjetsEnAttente' => $nbProjetsEnAttente
)),
Response::HTTP_OK,
['content-type' => 'application/json']
);
}
/**
* @Route("/old/lazyload/index", name="indexOldLazyload")
*/
public function indexOldLazyload(TranslatorInterface $translator)
{
$entityManager = $this->em;
//Declaration pour le QueryBuilder
$dm = $this->em;
$qb = $dm->createQueryBuilder();
//Le nombre total d'imp
$nbImpsTotal = $this->em->getRepository(NetworkAnalyticsBasic::class)->totalImps();
//Le nombre total de campagne effectuée
$nbCampaignTotal = $this->em->getRepository(NetworkAnalyticsBasic::class)->totalCampaignDistinct();
//Le nombre de cookie en base
$nbCookieTotal = $this->em->getRepository(SegmentLoad::class)->totalCookie();
//Le nombre de pixel en base
$nbPixelTotal = $this->em->getRepository(SegmentLoad::class)->nbPixelDistinct();
//Le nombre de client
$nbClient = $this->em->getRepository(Advertisers::class)->nombreTotalClientDistinct();
//Le nombre de campagne self-service
$nbCampaignSelfService = $this->em->getRepository(PurchasedCampaign::class)->nbCampaignDistinct();
//Le nombre d'utilisateur activé
$nbActiveUser = $this->em->getRepository(User::class)->countUsersByActive(1);
//Le nombre de projet activation en cours
$nbProjetsEnCours = $this->em->getRepository(ProjetsOldActivation::class)->countActivationActive();
//Le nombre de projet activation a activé
$nbProjetsAActiver = $this->em->getRepository(ProjetsOldActivation::class)->countActivationAActiver();
//Le nombre de projet activation en attente
$nbProjetsEnAttente = $this->em->getRepository(ProjetsOldActivation::class)->countActivationEnAttente();
//Le nombre de go tech
$nbGoTech = $this->emBackofficeProjet->getRepository(Implementation::class)->countGoTech();
//Le nombre de sujet implémentation en cours
$nbImplementationEnCours = $this->emBackofficeProjet->getRepository(Implementation::class)->countEnCours();
//Le nombre de Grands Compte Actif
$nbGcActif = $this->em->getRepository(GrandsComptes::class)->countActiveGC();
//Le nombre de wallets credités
$nbWalletsCredites = $this->em->getRepository(Wallet::class)->countWalletsCredites();
//Le nombre d'abonnement actif
$nbAbonnementActif = $this->em->getRepository(Abonnement::class)->countActiveAbonnement();
//Le nombre de key client actif
$nbKeyClientActif = $this->emDashboardSystem->getRepository(Clients::class)->countActiveKeyClients();
//Le nombre de client actif
$nbClientActif = $this->emDashboardSystem->getRepository(Clients::class)->countActive();
//Le nombre de client actif
$nbCampagneSelfClient = $this->em->getRepository(PurchasedCampaign::class)->countAllPaidCampaignWithoutTest();
//Nombre de campagne active
//$parameters = array(
// 'date' => date('Y-m-d', time()- 60 * 60 * 48),
// 'seconddate' => date('Y-m-d', time())
//);
//$query = $entityManager->createQuery(
// 'SELECT DISTINCT p.insertionOrderName
// FROM App\Entity\Main\Xandr\NetworkAnalyticsBasic p
// WHERE p.date BETWEEN :date AND :seconddate AND p.imps > 1500
// ORDER BY p.convsRate ASC'
//)->setParameters($parameters);
//try{
//$nbCampagneActiveQuery = $query->execute();
//}catch (\Throwable $th) {
// //throw $th;
// $nbCampagneActiveQuery[0] = 0;
//}
//$nbCampagneActive = count($nbCampagneActiveQuery);
//$nbCampagneActive = $this->em->getRepository(NetworkAnalyticsBasic::class)->totalCampaignActive();
//Nombre d'imp last 1 years
$parameters = array(
'firstdate' => date('Y-m-d', strtotime('today - 1 years')),
'seconddate' => date('Y-m-d', strtotime('today'))
);
$query = $entityManager->createQuery(
'SELECT p.date, SUM(p.imps), SUM(p.clicks), SUM(p.totalConvs)
FROM App\Entity\Main\Xandr\NetworkAnalytics p
WHERE p.date BETWEEN :firstdate AND :seconddate
GROUP BY p.date
ORDER BY p.date ASC'
)->setParameters($parameters);
$impsLast2Years = $query->execute();
$DBMdm = $this->emDashboardDbm;
//$DBMqb = $dm->createQueryBuilder();
$query = $DBMdm->createQuery(
'SELECT p.date, SUM(p.impressions), SUM(p.clicks), SUM(p.totalConversions)
FROM \App\Entity\DashboardDbm\ReportingNetwork p
WHERE p.date BETWEEN :firstdate AND :seconddate
GROUP BY p.date
ORDER BY p.date ASC'
)->setParameters($parameters);
$DbmImpsLast2Years = $query->execute();
$fbdm = $this->emDashboardFacebook;
//$DBMqb = $dm->createQueryBuilder();
$query = $fbdm->createQuery(
'SELECT p.date, SUM(p.impressions), SUM(p.clicks), SUM(p.actions_purchase)
FROM \App\Entity\DashboardFacebook\Campaign p
WHERE p.date BETWEEN :firstdate AND :seconddate
GROUP BY p.date
ORDER BY p.date ASC'
)->setParameters($parameters);
$FbImpsLast2Years = $query->execute();
$testDbmDate = array();
$testAppnexusDate = array();
$testFbDate = array();
//Get the list of date of DBM and put it in an array
foreach ($DbmImpsLast2Years as $item) {
array_push($testDbmDate, $item['date']->format('Y-m-d'));
}
//Get the list of date of Appnexus and put it in an array
foreach ($impsLast2Years as $item) {
array_push($testAppnexusDate, $item['date']->format('Y-m-d'));
}
//Get the list of date of fb and put it in an array
foreach ($FbImpsLast2Years as $item) {
array_push($testFbDate, $item['date']->format('Y-m-d'));
}
//Merge les tables de date DBM & Appnexus et enleve les doublons
$testUnique = array_unique(array_merge($testDbmDate, $testAppnexusDate, $testFbDate), SORT_REGULAR);
//Met la table dans l'ordre croissant
usort($testUnique, function ($time1, $time2) {
if (strtotime($time1) > strtotime($time2))
return 1;
else if (strtotime($time1) < strtotime($time2))
return -1;
else
return 0;
});
//DBM DATA MERGE
$networkMerged = array();
foreach ($DbmImpsLast2Years as $item) {
$tempDate = $item["date"]->format('Y-m-d');
$networkMerged[$tempDate][0] = $item;
}
//XANDR DATA MERGE
foreach ($impsLast2Years as $item) {
$tempDate = $item["date"]->format('Y-m-d');
if (empty($networkMerged[$item["date"]->format('Y-m-d')])) {
$networkMerged[$tempDate][0] = null;
$networkMerged[$tempDate][1] = $item;
} else {
$temp = $networkMerged[$tempDate];
$arrayTemp = array();
$arrayTemp[0] = $temp[0];
$arrayTemp[1] = $item;
$networkMerged[$tempDate] = $arrayTemp;
}
}
//FACEBOOK DATA MERGE
foreach ($FbImpsLast2Years as $item) {
$tempDate = $item["date"]->format('Y-m-d');
if (empty($networkMerged[$tempDate])) {
$networkMerged[$tempDate][0] = null;
$networkMerged[$tempDate][1] = null;
$networkMerged[$tempDate][2] = $item;
} else {
$temp = $networkMerged[$tempDate];
$arrayTemp = array();
$arrayTemp[0] = $temp[0];
$arrayTemp[1] = $temp[1];
$arrayTemp[2] = $item;
$networkMerged[$tempDate] = $arrayTemp;
}
}
$mainChartDate = "[";
$mainChartImps = "[";
$mainChartClick = "[";
$mainChartTotalConv = "[";
$mainChartDBMImps = "[";
$mainChartDBMClick = "[";
$mainChartDBMTotalConv = "[";
$mainChartFbImps = "[";
$mainChartFbClick = "[";
$mainChartFbTotalConv = "[";
$mainChartQSTotalImps = 0;
$mainChartQSTotalClick = 0;
$mainChartQSTotalConv = 0;
$i = -1;
foreach ($testUnique as $item) {
$i++;
$mainChartDate .= "'" . $item . "'" . ",";
$tempStateAppnexus = empty($networkMerged[$item][1]);
$tempStateDBM = empty($networkMerged[$item][0]);
$tempStateFB = empty($networkMerged[$item][2]);
//Build JSON table with DBM data
if ($tempStateDBM) {
$mainChartDBMImps .= '0,';
$mainChartDBMClick .= "0,";
$mainChartDBMTotalConv .= "0,";
} else {
$mainChartDBMImps .= $networkMerged[$item][0][1] . ",";
$mainChartDBMClick .= $networkMerged[$item][0][2] . ",";
$mainChartDBMTotalConv .= $networkMerged[$item][0][3] . ",";
}
$mainChartQSTotalImps += $item[1];
$mainChartQSTotalClick += $item[2];
$mainChartQSTotalConv += $item[3];
//Build JSON table with Appnexus data
if ($tempStateAppnexus) {
$mainChartImps .= "0,";
$mainChartClick .= "0,";
$mainChartTotalConv .= "0,";
} else {
$mainChartImps .= $networkMerged[$item][1][1] . ",";
$mainChartClick .= $networkMerged[$item][1][2] . ",";
$mainChartTotalConv .= $networkMerged[$item][1][3] . ",";
$mainChartQSTotalImps += $networkMerged[$item][1][1];
$mainChartQSTotalClick += $networkMerged[$item][1][2];
$mainChartQSTotalConv += $networkMerged[$item][1][3];
}
//Build JSON table with Facebook data
if ($tempStateFB) {
$mainChartFbImps .= "0,";
$mainChartFbClick .= "0,";
} else {
$mainChartFbImps .= $networkMerged[$item][2][1] . ",";
$mainChartFbClick .= $networkMerged[$item][2][2] . ",";
if ($networkMerged[$item][2][3] == "") {
$mainChartFbTotalConv .= "0,";
} else {
$mainChartFbTotalConv .= $networkMerged[$item][2][3] . ",";
}
$mainChartQSTotalImps += $networkMerged[$item][2][1];
$mainChartQSTotalClick += $networkMerged[$item][2][2];
}
}
$mainChartDate = substr($mainChartDate, 0, -1) . ']';
$mainChartImps = substr($mainChartImps, 0, -1) . ']';
$mainChartClick = substr($mainChartClick, 0, -1) . ']';
$mainChartTotalConv = substr($mainChartTotalConv, 0, -1) . ']';
$mainChartDBMImps = substr($mainChartDBMImps, 0, -1) . ']';
$mainChartDBMClick = substr($mainChartDBMClick, 0, -1) . ']';
$mainChartDBMTotalConv = substr($mainChartDBMTotalConv, 0, -1) . ']';
$mainChartFbImps = substr($mainChartFbImps, 0, -1) . ']';
$mainChartFbClick = substr($mainChartFbClick, 0, -1) . ']';
$mainChartFbTotalConv = substr($mainChartFbTotalConv, 0, -1) . ']';
if ($mainChartQSTotalImps == 0) {
$mainChartCtr = 0;
} else {
$mainChartCtr = $mainChartQSTotalClick * 100 / $mainChartQSTotalImps;
}
if ($mainChartFbTotalConv == ']') {
$mainChartFbTotalConv = '[]';
}
//Liste de theme pour les advertisers
$advTheme = $this->em
->getRepository(AdvertisersThemed::class)
->findAll();
$chart3Name = '[';
$chart3Data = '[';
$chart3BackgroundColor = '[';
foreach ($advTheme as $theme) {
$chart3Name .= '"' . $theme->getName() . '",';
$advTheme = $this->em
->getRepository(Advertisers::class)
->findBy(array("themeId" => $theme->getId()));
$chart3Data .= "" . count($advTheme) . ",";
$chart3BackgroundColor .= "'" . $theme->getColor() . "',";
}
$advTheme = $this->em
->getRepository(Advertisers::class)
->findBy(array("themeId" => null));
count($advTheme);
$chart3Name .= '"' . $translator->trans('Sans catégorie') . '",';
$chart3Data .= "" . count($advTheme) . ",";
$chart3Name = substr($chart3Name, 0, -1) . ']';
$chart3BackgroundColor = substr($chart3BackgroundColor, 0, -1) . ']';
$chart3Data = substr($chart3Data, 0, -1) . ']';
$chart5Label = "[";
$chart5Data = "[";
/*
Line Item qui ne respecte pas la nomenclature
*/
$parameters = array(
'lineitemname1' => '%IAB%',
'lineitemname2' => '%habillage%',
'lineitemname3' => '%natif%',
'lineitemname4' => '%preroll%',
'lineitemname5' => '%inread%',
'lineitemname6' => '%IPTV%',
'date' => date('Y-m-d', time() - 60 * 60 * 48),
'seconddate' => date('Y-m-d', time() - 60 * 60 * 24)
);
$query = $entityManager->createQuery(
'SELECT p
FROM App\Entity\Main\Xandr\NetworkAnalytics p
WHERE p.date BETWEEN :date AND :seconddate AND p.lineItemName NOT LIKE :lineitemname1 AND p.lineItemName NOT LIKE :lineitemname2 AND p.lineItemName NOT LIKE :lineitemname3 AND p.lineItemName NOT LIKE :lineitemname4 AND p.lineItemName NOT LIKE :lineitemname5 AND p.lineItemName NOT LIKE :lineitemname6'
)->setParameters($parameters);
$liBadName = $query->execute();
$liBadNameCount = 0;
if (count($liBadName) != 0) {
$liBadNameCount = count($liBadName);
$chart5Label .= "'" . $translator->trans('Mauvais nommage') . "',";
$chart5Data .= $liBadNameCount . ",";
}
/*
Line Item ayant le nom IAB
*/
$parameters = array(
'lineitemname' => '%IAB%',
'date' => date('Y-m-d', time() - 60 * 60 * 48),
'seconddate' => date('Y-m-d', time() - 60 * 60 * 24)
);
$query = $entityManager->createQuery(
'SELECT p
FROM App\Entity\Main\Xandr\NetworkAnalytics p
WHERE p.date BETWEEN :date AND :seconddate AND p.lineItemName LIKE :lineitemname'
)->setParameters($parameters);
$liIab = $query->execute();
$liIabCount = 0;
if (count($liIab) != 0) {
$liIabCount = count($liIab);
$chart5Label .= "'" . $translator->trans('Iab') . "',";
$chart5Data .= $liIabCount . ",";
}
/*
Line Item ayant le nom habillage
*/
$parameters = array(
'lineitemname' => '%habillage%',
'date' => date('Y-m-d', time() - 60 * 60 * 48),
'seconddate' => date('Y-m-d', time() - 60 * 60 * 24)
);
$query = $entityManager->createQuery(
'SELECT p
FROM App\Entity\Main\Xandr\NetworkAnalytics p
WHERE p.date BETWEEN :date AND :seconddate AND p.lineItemName LIKE :lineitemname'
)->setParameters($parameters);
$liHabillage = $query->execute();
$liHabillageCount = 0;
if (count($liHabillage) != 0) {
$liHabillageCount = count($liHabillage);
$chart5Label .= "'" . $translator->trans('Habillage') . "',";
$chart5Data .= $liHabillageCount . ",";
}
/*
Line Item ayant le nom natif
*/
$parameters = array(
'lineitemname' => '%natif%',
'date' => date('Y-m-d', time() - 60 * 60 * 48),
'seconddate' => date('Y-m-d', time() - 60 * 60 * 24)
);
$query = $entityManager->createQuery(
'SELECT p
FROM App\Entity\Main\Xandr\NetworkAnalytics p
WHERE p.date BETWEEN :date AND :seconddate AND p.lineItemName LIKE :lineitemname'
)->setParameters($parameters);
$liNatif = $query->execute();
$liNatifCount = 0;
if (count($liNatif) != 0) {
$liNatifCount = count($liNatif);
$chart5Label .= "'" . $translator->trans('Natif') . "',";
$chart5Data .= $liNatifCount . ",";
}
/*
Line Item ayant le nom preroll
*/
$parameters = array(
'lineitemname' => '%preroll%',
'date' => date('Y-m-d', time() - 60 * 60 * 48),
'seconddate' => date('Y-m-d', time() - 60 * 60 * 24)
);
$query = $entityManager->createQuery(
'SELECT p
FROM App\Entity\Main\Xandr\NetworkAnalytics p
WHERE p.date BETWEEN :date AND :seconddate AND p.lineItemName LIKE :lineitemname'
)->setParameters($parameters);
$liPreroll = $query->execute();
$liPrerollCount = 0;
if (count($liPreroll) != 0) {
$liPrerollCount = count($liPreroll);
$chart5Label .= "'" . $translator->trans('Pre-roll') . "',";
$chart5Data .= $liPrerollCount . ",";
}
/*
Line Item ayant le nom inread
*/
$parameters = array(
'lineitemname' => '%inread%',
'date' => date('Y-m-d', time() - 60 * 60 * 48),
'seconddate' => date('Y-m-d', time() - 60 * 60 * 24)
);
$query = $entityManager->createQuery(
'SELECT p
FROM App\Entity\Main\Xandr\NetworkAnalytics p
WHERE p.date BETWEEN :date AND :seconddate AND p.lineItemName LIKE :lineitemname'
)->setParameters($parameters);
$liInread = $query->execute();
$liInreadCount = 0;
if (count($liInread) != 0) {
$liInreadCount = count($liInread);
$chart5Label .= "'" . $translator->trans("In-Read") . "',";
$chart5Data .= $liInreadCount . ",";
}
/*
Line Item ayant le nom IPTV
*/
$parameters = array(
'lineitemname' => '%IPTV%',
'date' => date('Y-m-d', time() - 60 * 60 * 48),
'seconddate' => date('Y-m-d', time() - 60 * 60 * 24)
);
$query = $entityManager->createQuery(
'SELECT p
FROM App\Entity\Main\Xandr\NetworkAnalytics p
WHERE p.date BETWEEN :date AND :seconddate AND p.lineItemName LIKE :lineitemname'
)->setParameters($parameters);
$liIPTV = $query->execute();
$liIPTVCount = 0;
if (count($liIPTV) != 0) {
$liIPTVCount = count($liIPTV);
$chart5Label .= "'" . $translator->trans('IPTV') . "',";
$chart5Data .= $liIPTVCount . ",";
}
$chart5Label = substr($chart5Label, 0, -1) . ']';
$chart5Data = substr($chart5Data, 0, -1) . ']';
$parameters = array(
'firstdate' => date('Y-m-d', strtotime('today - 30 days')),
'seconddate' => date('Y-m-d', strtotime('today'))
);
$query = $entityManager->createQuery(
'SELECT p.date, SUM(p.imps), SUM(p.clicks), SUM(p.totalConvs)
FROM App\Entity\Main\Xandr\NetworkAnalytics p
WHERE p.date BETWEEN :firstdate AND :seconddate
GROUP BY p.date
ORDER BY p.date ASC'
)->setParameters($parameters);
$impsLast30Days = $query->execute();
//$DBMqb = $dm->createQueryBuilder();
$query = $DBMdm->createQuery(
'SELECT p.date, SUM(p.impressions), SUM(p.clicks), SUM(p.totalConversions)
FROM \App\Entity\DashboardDbm\ReportingNetwork p
WHERE p.date BETWEEN :firstdate AND :seconddate
GROUP BY p.date
ORDER BY p.date ASC'
)->setParameters($parameters);
$dbmImpsLast30Days = $query->execute();
$testDbmDate = array();
$testAppnexusDate = array();
//Get the list of date of DBM and put it in an array
foreach ($dbmImpsLast30Days as $item) {
array_push($testDbmDate, $item['date']->format('Y-m-d'));
}
//Get the list of date of Appnexus and put it in an array
foreach ($impsLast30Days as $item) {
array_push($testAppnexusDate, $item['date']->format('Y-m-d'));
}
//Merge les tables de date DBM & Appnexus et enleve les doublons
$testUnique = array_unique(array_merge($testDbmDate, $testAppnexusDate), SORT_REGULAR);
//Met la table dans l'ordre croissant
usort($testUnique, function ($time1, $time2) {
if (strtotime($time1) > strtotime($time2))
return 1;
else if (strtotime($time1) < strtotime($time2))
return -1;
else
return 0;
});
$networkMerged = array();
foreach ($dbmImpsLast30Days as $item) {
$tempDate = $item["date"]->format('Y-m-d');
$networkMerged[$tempDate] = $item;
}
foreach ($impsLast30Days as $item) {
$tempDate = $item["date"]->format('Y-m-d');
if (empty($networkMerged[$item["date"]->format('Y-m-d')])) {
$networkMerged[$tempDate][0] = null;
$networkMerged[$tempDate][1] = $item;
} else {
$temp = $networkMerged[$tempDate];
$arrayTemp = array();
$arrayTemp[0] = $temp;
$arrayTemp[1] = $item;
$networkMerged[$tempDate] = $arrayTemp;
}
}
$chart1Date = "[";
$chart1Imps = "[";
$chart1Click = "[";
$chart1TotalConv = "[";
$chart1DBMImps = "[";
$chart1DBMClick = "[";
$chart1DBMTotalConv = "[";
foreach ($testUnique as $item) {
$chart1Date .= "'" . $item . "'" . ",";
$tempStateAppnexus = empty($networkMerged[$item][1]);
$tempStateDBM = empty($networkMerged[$item][0]);
//Build JSON table with DBM data
if ($tempStateDBM) {
$chart1DBMImps .= '0,';
$chart1DBMClick .= "0,";
$chart1DBMTotalConv .= "0,";
} else {
$chart1DBMImps .= $networkMerged[$item][0][1] . ",";
$chart1DBMClick .= $networkMerged[$item][0][2] * 100 . ",";
$chart1DBMTotalConv .= $networkMerged[$item][0][3] * 100 . ",";
}
//Build JSON table with Appnexus data
if ($tempStateAppnexus) {
$chart1Imps .= "0,";
$chart1Click .= "0,";
$chart1TotalConv .= "0,";
} else {
$chart1Imps .= $networkMerged[$item][1][1] . ",";
$chart1Click .= $networkMerged[$item][1][2] * 100 . ",";
$chart1TotalConv .= $networkMerged[$item][1][3] * 100 . ",";
}
}
$chart1Date = substr($chart1Date, 0, -1) . ']';
$chart1Imps = substr($chart1Imps, 0, -1) . ']';
$chart1Click = substr($chart1Click, 0, -1) . ']';
$chart1TotalConv = substr($chart1TotalConv, 0, -1) . ']';
$chart1DBMImps = substr($chart1DBMImps, 0, -1) . ']';
$chart1DBMClick = substr($chart1DBMClick, 0, -1) . ']';
$chart1DBMTotalConv = substr($chart1DBMTotalConv, 0, -1) . ']';
//$chartcard3Label = "[";
//$chartcard3Data = "[";
//$query = $entityManager->createQuery(
// 'SELECT COUNT(p.id) as nbCampaign, SUBSTRING(p.purchasedDate, 6, 2) as month, SUBSTRING(p.purchasedDate, 1, 4) as year
// FROM App\Entity\Main\SelfService\PurchasedCampaign p
// GROUP BY month, year
// ORDER BY p.purchasedDate ASC'
// );
// try {
// $selfServiceCampaign = $query->execute();
// foreach ($selfServiceCampaign as $item) {
// $chartcard3Label .= "'".$item['month']."-".$item['year']."',";
// $chartcard3Data .= $item['nbCampaign'].",";
// }
// } catch (\Throwable $th) {
// $chartcard3Label .= "'".$translator->trans("Pas de data")."''";
// $chartcard3Data .= 0;
// }
//
//$chartcard3Label = substr($chartcard3Label, 0, -1).']';
//$chartcard3Data = substr($chartcard3Data, 0, -1).']';
//$chartcard2Label = "[";
//$chartcard2Data = "[";
////Nombre de campagne active par jour
//$parameters = array(
// 'date' => date('Y-m-d', time()- 60 * 60 * 168),
// 'seconddate' => date('Y-m-d', time())
//);
//$query = $entityManager->createQuery(
// 'SELECT COUNT(DISTINCT p.insertionOrderName) as ioname, SUBSTRING(p.date, 6, 2) as month, SUBSTRING(p.date, 1, 4) as year, SUBSTRING(p.date, 9, 2) as day
// FROM App\Entity\Main\Xandr\NetworkAnalyticsBasic p
// WHERE p.date BETWEEN :date AND :seconddate AND p.imps > 1500
// GROUP BY day, month, year
// ORDER BY p.date ASC'
//)->setParameters($parameters);
//try {
// $nbCampagneActiveQuery = $query->execute();
// foreach ($nbCampagneActiveQuery as $item) {
// $chartcard2Label .= "'".$item['day']."-".$item['month']."-".$item['year']."',";
// $chartcard2Data .= $item['ioname'].",";
// }
//} catch (\Throwable $th) {
// $chartcard2Label .= "'".$translator->trans("Pas de data")."''";
// $chartcard2Data .= "0";
//}
//$chartcard2Label = substr($chartcard2Label, 0, -1).']';
//$chartcard2Data = substr($chartcard2Data, 0, -1).']';
//Historique client
//$chartcard1Label = "[";
//$chartcard1Data = "[";
//$parameters = array(
// 'date' => date('Y-m-d', time()- 60 * 60 * 24 * 730),
// 'seconddate' => date('Y-m-d', time())
//);
//$query = $entityManager->createQuery(
// 'SELECT COUNT(DISTINCT p.advertiserName) as nbClient, SUBSTRING(p.date, 6, 2) as month, SUBSTRING(p.date, 1, 4) as year
// FROM App\Entity\Main\Xandr\NetworkAnalyticsBasic p
// WHERE p.date BETWEEN :date AND :seconddate
// GROUP BY month, year
// ORDER BY p.date ASC'
//)->setParameters($parameters);
//
//try {
// $nbClientPerDay = $query->execute();
// foreach ($nbClientPerDay as $item) {
// $chartcard1Label .= "'".$item['month']."-".$item['year']."',";
// $chartcard1Data .= $item['nbClient'].",";
// }
//} catch (\Throwable $th) {
// $chartcard1Label .= "'".$translator->trans("Pas de data")."''";
// $chartcard1Data .= "0";
//}
//$chartcard1Label = substr($chartcard1Label, 0, -1).']';
//$chartcard1Data = substr($chartcard1Data, 0, -1).']';
if ($chart5Label == ']') {
$chart5Label = '["no data"]';
}
if ($chart5Data == ']') {
$chart5Data = '[100]';
}
return new Response(
json_encode(array(
'nbImpsTotal' => $nbImpsTotal,
'nbCampaignTotal' => $nbCampaignTotal,
'nbCookieTotal' => $nbCookieTotal,
'nbPixelTotal' => $nbPixelTotal,
'nbClient' => $nbClient,
'nbProjetsEnCours' => $nbProjetsEnCours,
'nbProjetsAActiver' => $nbProjetsAActiver,
'nbActiveUser' => $nbActiveUser,
//'nbCampagneActive' => $nbCampagneActive,
'nbCampaignSelfService' => $nbCampaignSelfService,
//'chartcard1Data' => $chartcard1Data,
//'chartcard1Label' => $chartcard1Label,
//'chartcard2Label' => $chartcard2Label,
//'chartcard2Data' => $chartcard2Data,
//'chartcard3Label' => $chartcard3Label,
//'chartcard3Data' => $chartcard3Data,
'chart1Date' => $chart1Date,
'chart1Imps' => $chart1Imps,
'chart1Click' => $chart1Click,
'chart1TotalConv' => $chart1TotalConv,
'chart1DBMImps' => $chart1DBMImps,
'chart1DBMClick' => $chart1DBMClick,
'chart1DBMTotalConv' => $chart1DBMTotalConv,
'chart5Label' => $chart5Label,
'chart5Data' => $chart5Data,
'mainChartCtr' => $mainChartCtr,
'nbGoTech' => $nbGoTech,
'nbGcActif' => $nbGcActif,
'nbCampagneSelfClient' => $nbCampagneSelfClient,
'nbWalletsCredites' => $nbWalletsCredites,
'nbAbonnementActif' => $nbAbonnementActif,
'nbKeyClientActif' => $nbKeyClientActif,
'nbImplementationEnCours' => $nbImplementationEnCours,
'mainChartQSTotalImps' => $mainChartQSTotalImps,
'mainChartQSTotalClick' => $mainChartQSTotalClick,
'mainChartQSTotalConv' => $mainChartQSTotalConv,
'chart3BackgroundColor' => $chart3BackgroundColor,
'mainChartDate' => $mainChartDate,
'mainChartImps' => $mainChartImps,
'mainChartClick' => $mainChartClick,
'mainChartTotalConv' => $mainChartTotalConv,
'mainChartDBMImps' => $mainChartDBMImps,
'mainChartDBMClick' => $mainChartDBMClick,
'mainChartDBMTotalConv' => $mainChartDBMTotalConv,
'mainChartFbImps' => $mainChartFbImps,
'mainChartFbClick' => $mainChartFbClick,
'mainChartFbTotalConv' => $mainChartFbTotalConv,
'chart3Name' => $chart3Name,
'chart3Data' => $chart3Data,
'nbClientActif' => $nbClientActif,
'nbProjetsEnAttente' => $nbProjetsEnAttente
)),
Response::HTTP_OK,
['content-type' => 'application/json']
);
}
/**
* @Route("/homepage", name="index")
*/
public function index(MercureCookieGenerator $cookieGenerator)
{
$session = new Session();
$session->set('userRule', $this->userRule);
$jwt = $this->convertUserAccessToJWT();
$notificationsUser = $this->emDashboardSystem
->getRepository(\App\Entity\DashboardSystem\Notification::class)
->findBy(
array(
'userId' => $this->get('security.token_storage')->getToken()->getUser()->getId(),
'isRead' => 0
),
array(
"id" => "DESC"
),
5
);
$notificationsGeneral = $this->emDashboardSystem
->getRepository(\App\Entity\DashboardSystem\Notification::class)
->findBy(
array(
'userId' => 0
),
array(
"id" => "DESC"
),
5
);
$response = $this->render('index/index.html.twig', [
'notificationsUser' => $notificationsUser,
'notificationsGeneral' => $notificationsGeneral,
'controller_name' => 'IndexController',
'jwt' => $jwt,
'rss' => array()
]);
$response->headers->set('set-cookie', $cookieGenerator->generate("*"));
return $response;
}
/**
* @Route("/accessrefused", name="accessrefused")
*/
public function accessrefused()
{
return $this->render('error/error.html.twig', [
'message' => "Eh! Oh! Tu n'as pas le droit de venir ici !!! :'(",
]);
}
/**
* @Route("/colors/", name="colors")
*/
public function colors()
{
return $this->render('index/template/colors.html.twig', [
'controller_name' => 'IndexController',
]);
}
/**
* @Route("/typography/", name="typography")
*/
public function typography()
{
return $this->render('index/template/typography.html.twig', [
'controller_name' => 'IndexController',
]);
}
/**
* @Route("/base-breadcrumb/", name="basebreadcrumb")
*/
public function basebreadcrumb()
{
return $this->render('index/template/base-breadcrumb.html.twig', [
'controller_name' => 'IndexController',
]);
}
/**
* @Route("/base-cards/", name="base-cards")
*/
public function basecards()
{
return $this->render('index/template/base-cards.html.twig', [
'controller_name' => 'IndexController',
]);
}
/**
* @Route("/base-carousel/", name="base-carousel")
*/
public function basecarousel()
{
return $this->render('index/template/base-carousel.html.twig', [
'controller_name' => 'IndexController',
]);
}
/**
* @Route("/base-collapse/", name="base-collapse")
*/
public function basecollapse()
{
return $this->render('index/template/base-collapse.html.twig', [
'controller_name' => 'IndexController',
]);
}
/**
* @Route("/base-forms/", name="base-forms")
*/
public function baseforms()
{
return $this->render('index/template/base-forms.html.twig', [
'controller_name' => 'IndexController',
]);
}
/**
* @Route("/base-jumbotron/", name="base-jumbotron")
*/
public function basejumbotron()
{
return $this->render('index/template/base-jumbotron.html.twig', [
'controller_name' => 'IndexController',
]);
}
/**
* @Route("/base-list-group/", name="base-list-group")
*/
public function baselistgroup()
{
return $this->render('index/template/base-list-group.html.twig', [
'controller_name' => 'IndexController',
]);
}
/**
* @Route("/base-navs/", name="base-navs")
*/
public function basenavs()
{
return $this->render('index/template/base-navs.html.twig', [
'controller_name' => 'IndexController',
]);
}
/**
* @Route("/base-pagination/", name="base-pagination")
*/
public function basepagination()
{
return $this->render('index/template/base-pagination.html.twig', [
'controller_name' => 'IndexController',
]);
}
/**
* @Route("/base-popovers/", name="base-popovers")
*/
public function basepopovers()
{
return $this->render('index/template/base-popovers.html.twig', [
'controller_name' => 'IndexController',
]);
}
/**
* @Route("/base-progress/", name="base-progress")
*/
public function baseprogress()
{
return $this->render('index/template/base-progress.html.twig', [
'controller_name' => 'IndexController',
]);
}
/**
* @Route("/base-scrollspy/", name="base-scrollspy")
*/
public function basescrollspy()
{
return $this->render('index/template/base-scrollspy.html.twig', [
'controller_name' => 'IndexController',
]);
}
/**
* @Route("/base-switches/", name="base-switches")
*/
public function baseswitches()
{
return $this->render('index/template/base-switches.html.twig', [
'controller_name' => 'IndexController',
]);
}
/**
* @Route("/base-tables/", name="base-tables")
*/
public function basetables()
{
return $this->render('index/template/base-tables.html.twig', [
'controller_name' => 'IndexController',
]);
}
/**
* @Route("/base-tabs/", name="base-tabs")
*/
public function basetabs()
{
return $this->render('index/template/base-tabs.html.twig', [
'controller_name' => 'IndexController',
]);
}
/**
* @Route("/base-tooltips/", name="base-tooltips")
*/
public function basetooltips()
{
return $this->render('index/template/base-tooltips.html.twig', [
'controller_name' => 'IndexController',
]);
}
/**
* @Route("/buttons-buttons/", name="buttons-buttons")
*/
public function buttonsbuttons()
{
return $this->render('index/template/buttons-buttons.html.twig', [
'controller_name' => 'IndexController',
]);
}
/**
* @Route("/buttons-button-group/", name="buttons-button-group")
*/
public function buttonsbuttongroup()
{
return $this->render('index/template/buttons-button-group.html.twig', [
'controller_name' => 'IndexController',
]);
}
/**
* @Route("/buttons-dropdowns/", name="buttons-dropdowns")
*/
public function buttonsdropdowns()
{
return $this->render('index/template/buttons-dropdowns.html.twig', [
'controller_name' => 'IndexController',
]);
}
/**
* @Route("/buttons-brand-buttons/", name="buttons-brand-buttons")
*/
public function buttonsbrandbuttons()
{
return $this->render('index/template/buttons-brand-buttons.html.twig', [
'controller_name' => 'IndexController',
]);
}
/**
* @Route("/charts/", name="charts")
*/
public function charts()
{
return $this->render('index/template/charts.html.twig', [
'controller_name' => 'IndexController',
]);
}
/**
* @Route("/icons-coreui-icons/", name="icons-coreui-icons")
*/
public function iconscoreuiicons()
{
return $this->render('index/template/icons-coreui-icons.html.twig', [
'controller_name' => 'IndexController',
]);
}
/**
* @Route("/icons-flags/", name="icons-flags")
*/
public function iconsflags()
{
return $this->render('index/template/icons-flags.html.twig', [
'controller_name' => 'IndexController',
]);
}
/**
* @Route("/icons-font-awesome/", name="icons-font-awesome")
*/
public function iconsfontawesome()
{
return $this->render('index/template/icons-font-awesome.html.twig', [
'controller_name' => 'IndexController',
]);
}
/**
* @Route("/icons-simple-line-icons/", name="icons-simple-line-icons")
*/
public function iconssimplelineicons()
{
return $this->render('index/template/icons-simple-line-icons.html.twig', [
'controller_name' => 'IndexController',
]);
}
/**
* @Route("/notifications-alerts/", name="notifications-alerts")
*/
public function notificationsalerts()
{
return $this->render('index/template/notifications-alerts.html.twig', [
'controller_name' => 'IndexController',
]);
}
/**
* @Route("/notifications-badge/", name="notifications-badge")
*/
public function notificationsbadge()
{
return $this->render('index/template/notifications-badge.html.twig', [
'controller_name' => 'IndexController',
]);
}
/**
* @Route("/notifications-modals/", name="notifications-modals")
*/
public function notificationsmodals()
{
return $this->render('index/template/notifications-modals.html.twig', [
'controller_name' => 'IndexController',
]);
}
/**
* @Route("/widgets/", name="widgets")
*/
public function widgets()
{
return $this->render('index/template/widgets.html.twig', [
'controller_name' => 'IndexController',
]);
}
/**
* @Route("/404/", name="404")
*/
public function notFound()
{
return $this->render('index/404.html.twig', [
'controller_name' => 'IndexController',
]);
}
/**
* @Route("/500/", name="500")
*/
public function serverErrorpage()
{
return $this->render('index/500.html.twig', [
'controller_name' => 'IndexController',
]);
}
}

src/Controller/Homepage/IndexController.php line 150