<?php
namespace App\Utils;
use Doctrine\ORM\EntityManagerInterface;
use Symfony\Component\HttpFoundation\RequestStack;
use App\Exception\ResourceNotFoundException;
use App\Entity\Media;
use App\Entity\MediaGroup;
use App\Entity\MediaGroupItem;
use App\Entity\AdAnalyticsDaily;
class MediaHelper
{
protected $entityManager;
public function __construct (
EntityManagerInterface $entityManager,
RequestStack $requestStack
) {
$this->entityManager = $entityManager;
$this->requestStack = $requestStack;
}
//Should move this to a separate file like "SiteHelper"
public function getSiteCode() {
$site_code = null;
$request = $this->requestStack->getCurrentRequest();
if($request) {
$session = $request->getSession();
if($session) {
$site_code = $session->get("site");
}
}
return $site_code;
}
public function getMediaById ($id = 0)
{
$id = (int) $id;
$media = $this->entityManager
->getRepository(Media::class)
->findOneBy([
"id" => $id
]);
if ($media) {
return $media;
}
throw new ResourceNotFoundException ("Media with Id {$id} - Not Found");
}
public function getMediaByOldId ($id = 0)
{
$id = (int) $id;
$media = $this->entityManager
->getRepository(Media::class)
->findOneBy([
"old_id" => $id
]);
if ($media) {
return $media;
}
throw new ResourceNotFoundException ("Media with old_id {$id} - Not Found");
}
public function getMediaByName ($name = "")
{
$media = $this->entityManager
->getRepository(Media::class)
->findOneBy([
"name" => $name
]);
if ($media) {
return $media;
}
throw new ResourceNotFoundException ("Media with name {$name} - Not Found");
}
public function getMediaGroupItemById ($id = 0)
{
$id = (int) $id;
$mediaItem = $this->entityManager
->getRepository(MediaGroupItem::class)
->findOneBy([
"id" => $id
]);
if ($mediaItem) {
return $mediaItem;
}
throw new ResourceNotFoundException ("Media Group Item with Id {$id} - Not Found");
}
public function getMediaGroupItemByIdOrNull ($id = 0)
{
$id = (int) $id;
$mediaItem = $this->entityManager
->getRepository(MediaGroupItem::class)
->findOneBy([
"id" => $id
]);
if ($mediaItem) {
return $mediaItem;
}
return null;
}
public function getMediaGroupByPosition ($position = "")
{
$mediaGroup = $this->entityManager
->getRepository(MediaGroup::class)
->findOneBy([
"position" => $position
]);
if ($mediaGroup) {
//Causing issues, see the soryBy function for details
//$mediaGroup = $mediaGroup->sortBy("random");
return $mediaGroup;
}
throw new ResourceNotFoundException ("Media group position: {$position} - Not Found");
}
//Get X random active media group items (used for ads)
public function getRandomMediaGroupItems($mediaGroup, $limit, $site_code) {
/*
$qb = $this->entityManager->createQueryBuilder();
$qb->select('items')
->from("App\Entity\MediaGroupItem", "items")
->join("items.mediaGroup", "mg", "WITH", "mg.id = :mgId");
if($site_code) {
$qb->join("items.site", "s", "WITH", "s.id = :site")
->setParameter("site", $site_code);
}
$qb->setParameter("mgId", $mediaGroup->getId());
$qb->addSelect('RAND() as HIDDEN rand')->orderBy('rand()');
if($limit) {
$qb->setMaxResults($limit);
}
$results = $qb->getQuery()->getResult();
return $results;
*/
$query = implode(" ", array (
"SELECT items",
"FROM",
"App\Entity\MediaGroupItem items",
"JOIN",
"items.mediaGroup mg",
"JOIN",
"items.site site",
"WHERE",
"mg.id = :mgId",
"AND site.id = :site_code",
"AND items.status = 1",
//"Select RAND() as HIDDEN rand",
"ORDER BY rand()",
//"ORDER BY items.published_at DESC",
));
$query = $this->entityManager
->createQuery($query)
->setParameter("mgId", $mediaGroup->getId())
->setParameter("site_code", $site_code);
if($limit) {
$query = $query->setMaxResults($limit);
}
$results = $query->getResult();
return $results;
}
public function getFirstItemByPosition($position = "")
{
if(!$position) {
return null;
}
try {
$mediaGroup = $this->getMediaGroupByPosition($position);
} catch (\Exception $e) {
return null;
}
return $mediaGroup->getFirstPublishedActiveMediaGroupItem($this->getSiteCode());
}
public function getPopoutAdByCustomer($customer) {
if(!$customer) {
return "";
}
$site_code = $this->getSiteCode();
$qb = $this->entityManager->createQueryBuilder();
$result = $qb->select('item')
->from("App\Entity\MediaGroupItem", "item")
->join("item.mediaGroup", "mg", "WITH", "mg.position = 'ad-popout'")
->join("item.customers", "cu");
if($site_code) {
$qb->join("item.site", "s", "WITH", "s.id = :site")
->setParameter("site", $site_code);
}
$result = $qb->where("item.status = 1")
->andWhere("cu.id = :cuid")
->setMaxResults(1)
->addOrderBy("item.published_at", "DESC")
->getQuery()
->setParameter("cuid", $customer->getId())
->getResult();
if($result) {
return $result[0];
}
else {
return "";
}
}
public function getDefaultPopoutAd() {
$site_code = $this->getSiteCode();
if(!$site_code) {
return "";
}
$qb = $this->entityManager->createQueryBuilder();
$result = $qb->select('item')
->from("App\Entity\MediaGroupItem", "item")
->join("item.mediaGroup", "mg", "WITH", "mg.position = 'ad-popout'")
->join("item.site", "s", "WITH", "s.id = :site")
->setParameter("site", $site_code)
->where("item.status = 1")
->andWhere("item.site_default = :site_default")
->setParameter("site_default", $site_code)
->setMaxResults(1)
->addOrderBy("item.published_at", "DESC")
->getQuery()
->getResult();
if($result) {
return $result[0];
}
else {
return "";
}
}
//Exceptions for mega menu pinned ads
public function uriForPinned($uri, $current_route) {
$uri = rtrim($uri,"/");
if(in_array($current_route, ['forum', 'forums', 'forum_topic', 'forum_topic_edit'])) {
$uri = "/forum";
}
else if(in_array($current_route, ['gallery_master'])) {
$uri = "/roofing-galleries";
}
else if(in_array($current_route, ['events_master', 'event_master', 'events_month_master', 'events_day_master'])) {
$uri = "/events";
}
else if(in_array($current_route, ['contests_master'])) {
$uri = "/contests-and-games";
}
else if(in_array($current_route, ['promos_master'])) {
$uri = "/promos-rebates";
}
else if(in_array($current_route, ['ebooks', 'ebooks_master', 'ebook', 'ebook_master'])) {
$uri = "/ebooks";
}
else if(in_array($current_route, ['customerEbooks_master'])) {
$uri = "/partner-ebooks";
}
else if(in_array($current_route, ['rlw-posts_master'])) {
if($site_code == 1) {
$uri = "/read-listen-watch-rcs";
}
else {
$uri = "/read-listen-watch";
}
}
else if(in_array($current_route, ['video_master'])) {
$uri = "/videos";
}
else if(in_array($current_route, ['webinar_master'])) {
$uri = "/partner-webinars";
}
else if(in_array($current_route, ['podcast_master']) || strpos($uri, "/podcasts/") !== false) {
$uri = "/podcasts";
}
// || strpos($uri, "/events/") !== false
return $uri;
}
public function getPinnedAdByPosition($base_url, $uri, $current_route, $ad_position) {
$site_code = $this->getSiteCode();
if(!$site_code) {
return "";
}
$uri = $this->uriForPinned($uri, $current_route);
$qb = $this->entityManager->createQueryBuilder();
//TODO: look into how efficient this is and if it can be optimized
$result = $qb->select('item')
->from("App\Entity\MediaGroupItem", "item")
->join("item.mediaGroup", "mg", "WITH", "mg.position = '{$ad_position}'")
->join("item.site", "s", "WITH", "s.id = :site")
->setParameter("site", $site_code)
->join("item.pins", "p", "WITH", "p.site_id = :site")
->setParameter("site", $site_code)
->join("App\Entity\MegaMenu", "mm", "WITH", "mm.uuid = p.mega_menu_uuid AND mm.status = 1")
->leftJoin("App\Entity\MegaMenu", "mm2", "WITH", "mm2.parent = mm.uuid and mm2.status = 1")
->leftJoin("App\Entity\MegaMenu", "mm3", "WITH", "mm3.parent = mm2.uuid and mm3.status = 1")
->where("item.status = 1")
//->andWhere("mm.link = :link OR mm2.link = :link OR mm3.link = :link")
->andWhere("mm.link = :link OR mm2.link = :link OR mm3.link = :link OR mm.link = :link2 OR mm2.link = :link2 OR mm3.link = :link2")
->setParameter("link", $base_url . $uri)
->setParameter("link2", $base_url . $uri . "/") //Some have trailing slashes. Should clean this up on save.
//->andWhere("item.site_default = :site_default")
->setMaxResults(1)
->addOrderBy("mm.position", "ASC")
->addOrderBy("mm2.position", "ASC")
->addOrderBy("mm3.position", "ASC")
->addOrderBy("item.published_at", "DESC")
->getQuery()
->getResult();
if($result) {
return $result[0];
}
else {
return "";
}
}
public function getContentByType ($type = "") {
$site_code = $this->getSiteCode();
$qb = $this->entityManager->createQueryBuilder();
$qb->select('c')
->from("App\Entity\Content", "c")
->where("c.type = :type")
->andWhere("c.status = 1 ");
if($site_code) {
$qb->join("c.site", "s", "WITH", "s.id = :site")
->setParameter("site", $site_code);
}
$content = $qb
->setParameter("type", $type)
->getQuery()->getResult();
if($content) {
return $content;
}
return [];
}
public function getAdsForAnalyticsAggregated($customer, $startDate, $endDate)
{
$query = implode(" ", array (
"SELECT DISTINCT item",
"FROM",
"App\Entity\MediaGroupItem item",
"JOIN",
"item.customers customer",
"LEFT JOIN",
"customer.prnt prnt",
"JOIN",
"item.ad_analytics_daily ad_analytics_daily",
"WHERE",
"(customer.id = :id OR prnt.id = :id)",
"AND ad_analytics_daily.day BETWEEN :start AND :end",
));
$results = $this->entityManager
->createQuery($query)
->setParameter("id", $customer->getId())
->setParameter("start", $startDate)
->setParameter("end", $endDate)
->getResult();
return $results;
}
public function countAdImpressions($item, $startDate, $endDate, $site_id="")
{
$query = implode(" ", array (
"SELECT COUNT(impression.traffic_data)",
"FROM",
"App\Entity\AdImpression impression",
"WHERE",
"impression.media_group_item = :mg",
"AND (impression.requested_at BETWEEN :start AND :end)",
($site_id ? implode(" ", array (
"AND impression.site_id = {$site_id}",
)) : ""),
));
$count = $this->entityManager
->createQuery($query)
->setParameter("mg", $item)
->setParameter("start", $startDate)
->setParameter("end", $endDate)
->getResult();
if (!empty($count)) {
return (int) $count[0][1];
}
return 0;
}
public function countAdImpressionsGroupedBySite($item, $startDate, $endDate)
{
$query = implode(" ", array (
"SELECT impression.site_id, COUNT(impression.traffic_data) as impressions",
"FROM",
"App\Entity\AdImpression impression",
"JOIN",
"impression.media_group_item item",
"WHERE",
"item.id = :id",
"AND (impression.requested_at BETWEEN :start AND :end)",
"GROUP BY impression.site_id",
));
$results = $this->entityManager
->createQuery($query)
->setParameter("id", $item->getId())
->setParameter("start", $startDate)
->setParameter("end", $endDate)
->getResult();
return $results;
}
public function countAdImpressionsAggregated($item, $startDate, $endDate, $site_id="")
{
$query = implode(" ", array (
"SELECT SUM(ad_analytics_daily.impressions)",
"FROM",
"App\Entity\AdAnalyticsDaily ad_analytics_daily",
"JOIN",
"ad_analytics_daily.media_group_item item",
"WHERE",
"item.id = :id",
"AND ad_analytics_daily.day BETWEEN :start AND :end",
($site_id ? implode(" ", [
"AND ad_analytics_daily.site_id = {$site_id}",
]) : ""),
));
$count = $this->entityManager
->createQuery($query)
->setParameter("id", $item)
->setParameter("start", $startDate)
->setParameter("end", $endDate)
->getResult();
if (!empty($count)) {
return (int) $count[0][1];
}
return 0;
}
public function countAdClicks($item, $startDate, $endDate, $site_id="")
{
$query = implode(" ", array (
"SELECT COUNT(click.traffic_data)",
"FROM",
"App\Entity\AdClick click",
"JOIN",
"click.media_group_item item",
"WHERE",
"item.id = :id",
"AND (click.requested_at BETWEEN :start AND :end)",
($site_id ? implode(" ", array (
"AND click.site_id = {$site_id}",
)) : ""),
));
$count = $this->entityManager
->createQuery($query)
->setParameter("id", $item->getId())
->setParameter("start", $startDate)
->setParameter("end", $endDate)
->getResult();
if (!empty($count)) {
return (int) $count[0][1];
}
return 0;
}
public function countAdClicksGroupedBySite($item, $startDate, $endDate)
{
$query = implode(" ", array (
"SELECT click.site_id, COUNT(click.traffic_data) as views",
"FROM",
"App\Entity\AdClick click",
"JOIN",
"click.media_group_item item",
"WHERE",
"item.id = :id",
"AND (click.requested_at BETWEEN :start AND :end)",
"GROUP BY click.site_id",
));
$results = $this->entityManager
->createQuery($query)
->setParameter("id", $item->getId())
->setParameter("start", $startDate)
->setParameter("end", $endDate)
->getResult();
return $results;
}
public function countAdClicksAggregated($item, $startDate, $endDate, $site_id="")
{
$query = implode(" ", array (
"SELECT SUM(ad_analytics_daily.views)",
"FROM",
"App\Entity\AdAnalyticsDaily ad_analytics_daily",
"JOIN",
"ad_analytics_daily.media_group_item media_group_item",
"WHERE",
"media_group_item.id = :id",
"AND ad_analytics_daily.day BETWEEN :start AND :end",
($site_id ? implode(" ", [
"AND ad_analytics_daily.site_id = {$site_id}",
]) : ""),
));
$sum = $this->entityManager
->createQuery($query)
->setParameter("id", $item->getId())
->setParameter("start", $startDate)
->setParameter("end", $endDate)
->getResult();
if (!empty($sum)) {
return (int) $sum[0][1];
}
return 0;
}
public function countTotalAdImpressions($customer, $startDate, $endDate)
{
$query = implode(" ", array (
"SELECT COUNT(impression.traffic_data)",
"FROM",
"App\Entity\AdImpression impression",
"JOIN",
"impression.media_group_item item",
"JOIN",
"item.customers customer",
"WHERE",
"customer.id = :id",
"AND (impression.requested_at BETWEEN :start AND :end)",
));
$count = $this->entityManager
->createQuery($query)
->setParameter("id", $customer->getId())
->setParameter("start", $startDate)
->setParameter("end", $endDate)
->getResult();
if (!empty($count)) {
return (int) $count[0][1];
}
return 0;
}
public function countTotalAdImpressionsAggregated($customer, $startDate, $endDate)
{
$query = implode(" ", array (
"SELECT SUM(ad_analytics_daily.impressions)",
"FROM",
"App\Entity\AdAnalyticsDaily ad_analytics_daily",
"JOIN",
"ad_analytics_daily.media_group_item item",
"JOIN",
"item.customers customer",
"WHERE",
"customer.id = :id",
"AND (ad_analytics_daily.day BETWEEN :start AND :end)",
));
$count = $this->entityManager
->createQuery($query)
->setParameter("id", $customer->getId())
->setParameter("start", $startDate)
->setParameter("end", $endDate)
->getResult();
if (!empty($count)) {
return (int) $count[0][1];
}
return 0;
}
public function countTotalAdClicks($customer, $startDate, $endDate)
{
$query = implode(" ", array (
"SELECT COUNT(click.traffic_data)",
"FROM",
"App\Entity\AdClick click",
"JOIN",
"click.media_group_item item",
"JOIN",
"item.customers customer",
"WHERE",
"customer.id = :id",
"AND (click.requested_at BETWEEN :start AND :end)",
));
$count = $this->entityManager
->createQuery($query)
->setParameter("id", $customer->getId())
->setParameter("start", $startDate)
->setParameter("end", $endDate)
->getResult();
if (!empty($count)) {
return (int) $count[0][1];
}
return 0;
}
public function countTotalAdClicksAggregated($customer, $startDate, $endDate)
{
$query = implode(" ", array (
"SELECT SUM(ad_analytics_daily.views)",
"FROM",
"App\Entity\AdAnalyticsDaily ad_analytics_daily",
"JOIN",
"ad_analytics_daily.media_group_item item",
"JOIN",
"item.customers customer",
"WHERE",
"customer.id = :id",
"AND (ad_analytics_daily.day BETWEEN :start AND :end)",
));
$count = $this->entityManager
->createQuery($query)
->setParameter("id", $customer->getId())
->setParameter("start", $startDate)
->setParameter("end", $endDate)
->getResult();
if (!empty($count)) {
return (int) $count[0][1];
}
return 0;
}
}