Symfony Profiler

<?php
namespace App\Service;
use App\Entity\Campaign;
use App\Repository\BidRulesRepository;
use App\Repository\CampaignRepository;
use App\Repository\MediaRepository;
use Doctrine\ORM\EntityManagerInterface;
use Symfony\Component\Cache\Adapter\RedisAdapter;
use Symfony\Contracts\Cache\ItemInterface;
class TourCpcBanner
{
private $mediaRepository;
private $campaignRepository;
private $bid;
private $em;
public function __construct(MediaRepository $mediaRepository, CampaignRepository $campaignRepository, BidRulesRepository $bid,EntityManagerInterface $em)
{
$this->mediaRepository = $mediaRepository;
$this->campaignRepository = $campaignRepository;
$this->bid = $bid;
$this->em = $em;
}
private function getTimeMatrix()
{
$weekDays = ['Saturday' => 'a', 'Sunday' => 'b', 'Monday' => 'c', 'Tuesday' => 'd', 'Wednesday' => 'e', 'Thursday' => 'f', 'Friday' => 'g'];
return $weekDays[date('l')] . date('G');;
}
public function getBanner(string $dimension, string $zone, string $origin, string $destination, string $device)
{
$cache = new RedisAdapter(
RedisAdapter::createConnection('redis://localhost'),
'tour-cpc-view',
$defaultLifetime = 60
);
$cacheKey = "{$dimension}-{$zone}-{$origin}-{$destination}-{$device}";
$cachedMedia = $cache->get($cacheKey,function (ItemInterface $item) use ($origin,$destination,$zone,$dimension,$device){
$item->expiresAfter(120);
$origin = strtoupper($origin);
$destination = strtoupper($destination);
/** Get minimum possible bid according to DB */
$bids = $this->bid->findBy(['type' => 'tour'], ['bid' => 'DESC']);
$minBid = 0;
foreach ($bids as $bid) {
if (!empty($bid->getOrigin()) && !empty($bid->getDestination())) {
if ($bid->getOrigin() == $origin && $bid->getDestination() == $destination) {
$minBid = $bid->getBid();
break;
}
} elseif (!empty($bid->getOrigin()) && empty($bid->getDestination())) {
if ($bid->getOrigin() == $origin) {
$minBid = $bid->getBid();
break;
}
} elseif (empty($bid->getOrigin()) && !empty($bid->getDestination())) {
if ($bid->getDestination() == $destination) {
$minBid = $bid->getBid();
break;
}
} else {
if ($bid->getZone() == $zone) {
$minBid = $bid->getBid();
}
}
}
/** find availbale campaigns */
$currentTime = $this->getTimeMatrix();
$campaigns = $this->campaignRepository->createQueryBuilder('c');
$campaigns->select(['c.id', 'c.bid']);
$campaigns->where('c.type = :type')->setParameter('type', 'tour_cpc');
$campaigns->andWhere('c.device = :all or c.device = :device')->setParameter('all', 'all')->setParameter('device', $device);
$campaigns->andWhere('c.start_date < :startdate or c.start_date is null')->setParameter('startdate', new \DateTime());
$campaigns->andWhere('c.end_date > :enddate or c.end_date is null')->setParameter('enddate', new \DateTime());
$campaigns->andWhere('c.time_matrix like :currentTime or c.time_matrix is null')->setParameter('currentTime', "%$currentTime%");
$campaigns->andWhere('c.origin = :origin or c.origin is null')->setParameter('origin', $origin);
$campaigns->andWhere('c.destination = :destination or c.destination is null')->setParameter('destination', $destination);
$campaigns->andWhere('c.bid >= :minbid')->setParameter('minbid', $minBid);
$campaigns->andWhere('c.daily_budget > c.daily_spent');
if(!empty($zone)) {
$campaigns->andWhere('c.zone = :zone or c.zone=:all')->setParameter('zone', $zone)->setParameter('all', 'all');
}
$campaigns->andWhere('c.has_fund = true');
$campaigns->andWhere('c.status = :active')->setParameter('active', 'active');
$campaignsOBJ = $campaigns->getQuery()->getResult();
$campaignsId = array_map(function ($c) {
return $c['id'];
}, $campaignsOBJ);
/** Get available assosiataed medias */
$mediasQuery = $this->mediaRepository->createQueryBuilder('m');
$mediasQuery->select(['m.id','m.uid', 'm.dimension', 'm.title', 'm.file', 'm.url', 'm.impression', 'm.click', 'c.ctr', 'identity(m.created_by) as user', 'c.bid']);
$mediasQuery->addSelect(['c.utm_source', 'c.utm_medium', 'c.utm_campaign', 'c.id as campaign']);
$mediasQuery->where('m.type = :type')->setParameter('type', 'tour_cpc');
$mediasQuery->andWhere('m.campaign in (:campaigns)')->setParameter('campaigns', $campaignsId);
$mediasQuery->andWhere('m.dimension = :dimension')->setParameter('dimension', $dimension);
$mediasQuery->andWhere('m.status = :active')->setParameter('active', 'active');
$mediasQuery->innerJoin(Campaign::class, 'c', 'WITH', 'c.id = m.campaign');
$mediasQuery->orderBy('c.bid', 'DESC');
$medias = $mediasQuery->getQuery()->getResult();
/** categorize medias to virgin and not virgin medias */
$virginMedias = $notVirginMedia = [];
foreach ($medias as $media) {
if ($media['impression'] < 10000) {
$virginMedias[] = $media;
} else {
$notVirginMedia[] = array_merge($media, [
'score' => (($media['click'] / $media['impression']) + 0.0001) * $media['bid']
]);
}
}
usort($notVirginMedia, function ($item1, $item2) {
return $item2['score'] <=> $item1['score'];
});
/** avialable banners */
$outPutmedia = !empty($notVirginMedia) ? array_merge([$notVirginMedia[0]], $virginMedias) : $virginMedias;
return $outPutmedia;
},1.1);
/** not to be cached */
if(!empty($cachedMedia)) {
$pickedBannerKey = array_rand($cachedMedia);
$pickedBanner = $cachedMedia[$pickedBannerKey];
$con = $this->em->getConnection();
$con->executeQuery('update campaign set impression = impression + 3, ctr=(cast(click as float)/cast(GREATEST(impression,1) as float )) * 100 where id= ?', [$pickedBanner['campaign']]);
$con->executeQuery('update media set impression = impression + 3, ctr=(cast(click as float)/cast(GREATEST(impression,1) as float )) * 100 where id= ?', [$pickedBanner['id']]);
$con->executeQuery("insert into campaign_stat (id,campaign_id,date,stat_id,impression,ctr,created_at) values (nextval('campaign_stat_id_seq'),?,?,?,?,?,current_timestamp) on conflict (stat_id) do update set impression = campaign_stat.impression + 3 ,ctr = (cast(campaign_stat.click as float)/cast(campaign_stat.impression as float )) * 100", [
$pickedBanner['campaign'],
date('Y-m-d'),
$pickedBanner['campaign'] . "-" . date('Ymd'),
1,
0
]);
}else{
$pickedBanner = [];
}
if(empty($pickedBanner)){
return null;
}else{
return [
'id'=>$pickedBanner['id'],
'uid'=>$pickedBanner['uid'],
'file'=>$_ENV['BASE_URL'].$pickedBanner['file'],
'link'=>$pickedBanner['url'],
'dim'=>$pickedBanner['dimension']
];
}
}
}

src/Service/TourCpcBanner.php line 39