src/Utils/MediaHelper.php line 35

Open in your IDE?
  1. <?php
  3. namespace App\Utils;
  5. use Doctrine\ORM\EntityManagerInterface;
  7. use Symfony\Component\HttpFoundation\RequestStack;
  9. use App\Exception\ResourceNotFoundException;
  10. use App\Entity\Media;
  11. use App\Entity\MediaGroup;
  12. use App\Entity\MediaGroupItem;
  13. use App\Entity\AdAnalyticsDaily;
  15. class MediaHelper 
  16. {
  17.     
  18.     protected $entityManager;
  19.     
  20.     public function __construct (
  21.         EntityManagerInterface $entityManager,
  22.         RequestStack $requestStack
  23.     ) {
  24.         $this->entityManager = $entityManager;
  25.         $this->requestStack = $requestStack;
  26.     }
  27.     
  28.     //Should move this to a separate file like "SiteHelper"
  29.     public function getSiteCode() {
  30.         $site_code = null;
  31.         $request = $this->requestStack->getCurrentRequest();
  32.         if($request) {
  33.             $session = $request->getSession();
  34.             if($session) {
  35.                 $site_code = $session->get("site");
  36.             }
  37.         }
  38.         return $site_code;
  39.     }
  40.     
  41.     public function getMediaById ($id = 0)
  42.     {
  43.         $id = (int) $id;
  44.         $media = $this->entityManager
  45.             ->getRepository(Media::class)
  46.             ->findOneBy([
  47.                 "id" => $id
  48.             ]);
  49.             
  50.         if ($media) {
  51.             return $media;
  52.         }
  53.         
  54.         throw new ResourceNotFoundException ("Media with Id {$id} - Not Found");
  55.     }
  56.     
  57.     public function getMediaByOldId ($id = 0)
  58.     {
  59.         
  60.         $id = (int) $id;
  61.         $media = $this->entityManager
  62.             ->getRepository(Media::class)
  63.             ->findOneBy([
  64.                 "old_id" => $id
  65.             ]);
  66.             
  67.         if ($media) {
  68.             return $media;
  69.         }
  70.         
  71.         throw new ResourceNotFoundException ("Media with old_id {$id} - Not Found");
  72.     }
  73.     
  74.     public function getMediaByName ($name = "")
  75.     {
  76.         $media = $this->entityManager
  77.             ->getRepository(Media::class)
  78.             ->findOneBy([
  79.                 "name" => $name
  80.             ]);
  81.             
  82.         if ($media) {
  83.             return $media;
  84.         }
  85.         
  86.         throw new ResourceNotFoundException ("Media with name {$name} - Not Found");
  87.     }
  88.     
  89.     public function getMediaGroupItemById ($id = 0)
  90.     {
  91.         $id = (int) $id;
  92.         $mediaItem = $this->entityManager
  93.             ->getRepository(MediaGroupItem::class)
  94.             ->findOneBy([
  95.                 "id" => $id
  96.             ]);
  97.             
  98.         if ($mediaItem) {
  99.             return $mediaItem;
  100.         }
  101.         
  102.         throw new ResourceNotFoundException ("Media Group Item with Id {$id} - Not Found");
  103.     }
  104.     
  105.     public function getMediaGroupItemByIdOrNull ($id = 0)
  106.     {
  107.         $id = (int) $id;
  108.         $mediaItem = $this->entityManager
  109.             ->getRepository(MediaGroupItem::class)
  110.             ->findOneBy([
  111.                 "id" => $id
  112.             ]);
  113.             
  114.         if ($mediaItem) {
  115.             return $mediaItem;
  116.         }
  117.         
  118.         return null;
  119.     }
  120.     
  121.     public function getMediaGroupByPosition ($position = "")
  122.     {
  123.         $mediaGroup = $this->entityManager
  124.             ->getRepository(MediaGroup::class)
  125.             ->findOneBy([
  126.                 "position" => $position
  127.             ]);
  128.             
  129.         if ($mediaGroup) {
  130.             
  131.             //Causing issues, see the soryBy function for details
  132.             //$mediaGroup = $mediaGroup->sortBy("random");
  133.             
  134.             return $mediaGroup;
  135.         }
  136.         
  137.         throw new ResourceNotFoundException ("Media group position: {$position} - Not Found");
  138.     }
  139.     
  140.     //Get X random active media group items (used for ads)
  141.     public function getRandomMediaGroupItems($mediaGroup, $limit, $site_code) {
  142.         
  143.         /*
  144.         $qb = $this->entityManager->createQueryBuilder();
  145.         
  146.         $qb->select('items')
  147.             ->from("App\Entity\MediaGroupItem", "items")
  148.             ->join("items.mediaGroup", "mg", "WITH", "mg.id = :mgId");
  149.         if($site_code) {
  150.             $qb->join("items.site", "s", "WITH", "s.id = :site")
  151.                 ->setParameter("site", $site_code);
  152.         }
  153.         $qb->setParameter("mgId", $mediaGroup->getId());
  154.         $qb->addSelect('RAND() as HIDDEN rand')->orderBy('rand()');
  155.         if($limit) {
  156.             $qb->setMaxResults($limit);
  157.         }
  158.         $results = $qb->getQuery()->getResult();
  159.         
  160.         return $results;
  161.         */
  162.         
  163.         $query = implode(" ", array (
  164.                 "SELECT items",
  165.                 "FROM",
  166.                     "App\Entity\MediaGroupItem items",
  167.                 "JOIN",
  168.                     "items.mediaGroup mg",
  169.                 "JOIN",
  170.                     "items.site site",
  171.                 "WHERE",
  172.                     "mg.id = :mgId",
  173.                     "AND site.id = :site_code",
  174.                     "AND items.status = 1",
  175.                 //"Select RAND() as HIDDEN rand",
  176.                 "ORDER BY rand()",
  177.                 //"ORDER BY items.published_at DESC",
  178.             ));
  179.         
  180.         $query = $this->entityManager
  181.             ->createQuery($query)
  182.             ->setParameter("mgId", $mediaGroup->getId())
  183.             ->setParameter("site_code", $site_code);
  184.             
  185.         if($limit) {
  186.             $query = $query->setMaxResults($limit);
  187.         }
  188.         
  189.         $results = $query->getResult();
  190.         
  191.         return $results;    
  192.     }
  194.     public function getRandomUnpriorityAds($mediaGroup, $limit, $site_code) {
  196.         $query = implode(" ", array (
  197.                 "SELECT items",
  198.                 "FROM",
  199.                     "App\Entity\MediaGroupItem items",
  200.                 "JOIN",
  201.                     "items.mediaGroup mg",
  202.                 "JOIN",
  203.                     "items.site site",
  204.                 "WHERE",
  205.                     "mg.id = :mgId",
  206.                     "AND site.id = :site_code",
  207.                     "AND items.status = 1",
  208.                     "AND items.id NOT IN (SELECT item.id FROM App\Entity\MediaGroupItem item JOIN items.priorities priority WHERE priority.ad_type = :mgId AND priority.site_id = :site_code)",
  209.                 "ORDER BY rand()",
  210.             ));
  211.         
  212.         $query = $this->entityManager
  213.             ->createQuery($query)
  214.             ->setParameter("mgId", $mediaGroup->getId())
  215.             ->setParameter("site_code", $site_code);
  216.             
  217.         if($limit) {
  218.             $query = $query->setMaxResults($limit);
  219.         }
  220.         
  221.         $results = $query->getResult();
  222.         
  223.         return $results;    
  224.     }
  225.     
  226.     public function getRandomPriorityAds($mediaGroup, $limit, $site_code) {
  228.         $query = implode(" ", array (
  229.                 "SELECT items",
  230.                 "FROM",
  231.                     "App\Entity\MediaGroupItem items",
  232.                 "JOIN",
  233.                     "items.mediaGroup mg",
  234.                 "JOIN",
  235.                     "items.site site",
  236.                 "JOIN",
  237.                     "items.priorities priority",
  238.                 "WHERE",
  239.                     "mg.id = :mgId",
  240.                     "AND site.id = :site_code",
  241.                     "AND items.status = 1",
  242.                     "AND priority.ad_type = :mgId",
  243.                     "AND priority.site_id = :site_code",
  244.                 "ORDER BY rand()",
  245.             ));
  246.         
  247.         $query = $this->entityManager
  248.             ->createQuery($query)
  249.             ->setParameter("mgId", $mediaGroup->getId())
  250.             ->setParameter("site_code", $site_code);
  251.             
  252.         if($limit) {
  253.             $query = $query->setMaxResults($limit);
  254.         }
  255.         
  256.         $results = $query->getResult();
  257.         
  258.         return $results;
  259.     }
  260.     
  261.     public function getFirstItemByPosition($position = "")
  262.     {
  263.         if(!$position) {
  264.             return null;
  265.         }
  266.         
  267.         try {
  268.             $mediaGroup = $this->getMediaGroupByPosition($position);
  269.         } catch (\Exception $e) {
  270.             return null;
  271.         }
  272.         
  273.         return $mediaGroup->getFirstPublishedActiveMediaGroupItem($this->getSiteCode());
  274.         
  275.     }
  277.     public function getPopoutAdByCustomer($customer) {
  278.         
  279.         if(!$customer) {
  280.             return "";
  281.         }
  283.         $site_code = $this->getSiteCode();
  284.         
  285.         $qb = $this->entityManager->createQueryBuilder();
  286.         
  287.         $result = $qb->select('item')
  288.             ->from("App\Entity\MediaGroupItem", "item")
  289.             ->join("item.mediaGroup", "mg", "WITH", "mg.position = 'ad-popout'")
  290.             ->join("item.customers", "cu");
  291.         if($site_code) {
  292.             $qb->join("item.site", "s", "WITH", "s.id = :site")
  293.                 ->setParameter("site", $site_code);
  294.         }
  295.         $result = $qb->where("item.status = 1")
  296.             ->andWhere("cu.id = :cuid")
  297.             ->setMaxResults(1)
  298.             ->addOrderBy("item.published_at", "DESC")
  299.             ->getQuery()
  300.             ->setParameter("cuid", $customer->getId())
  301.             ->getResult();
  302.             
  303.         if($result) {
  304.             return $result[0];
  305.         }
  306.         else {
  307.             return "";
  308.         }
  309.     }
  311.     public function getDefaultPopoutAd() {
  312.         
  313.         $site_code = $this->getSiteCode();
  315.         if(!$site_code) {
  316.             return "";
  317.         }
  318.         
  319.         $qb = $this->entityManager->createQueryBuilder();
  320.         
  321.         $result = $qb->select('item')
  322.             ->from("App\Entity\MediaGroupItem", "item")
  323.             ->join("item.mediaGroup", "mg", "WITH", "mg.position = 'ad-popout'")
  324.             ->join("item.site", "s", "WITH", "s.id = :site")
  325.             ->setParameter("site", $site_code)
  326.             ->where("item.status = 1")
  327.             ->andWhere("item.site_default = :site_default")
  328.             ->setParameter("site_default", $site_code)
  329.             ->setMaxResults(1)
  330.             ->addOrderBy("item.published_at", "DESC")
  331.             ->getQuery()
  332.             ->getResult();
  333.             
  334.         if($result) {
  335.             return $result[0];
  336.         }
  337.         else {
  338.             return "";
  339.         }
  340.     }
  343.     //Exceptions for mega menu pinned ads
  344.     public function uriForPinned($uri, $current_route) {
  345.         $site_code = $this->getSiteCode();
  346.         $uri = rtrim($uri,"/");
  348.         if(in_array($current_route, ['forum', 'forums', 'forum_topic', 'forum_topic_edit'])) {
  349.             $uri = "/forum";
  350.         }
  351.         else if(in_array($current_route, ['gallery_master'])) {
  352.             $uri = "/roofing-galleries";
  353.         }
  354.         else if(in_array($current_route, ['events_master', 'event_master', 'events_month_master', 'events_day_master'])) {
  355.             $uri = "/events";
  356.         }
  357.         else if(in_array($current_route, ['contests_master'])) {
  358.             $uri = "/contests-and-games";
  359.         }
  360.         else if(in_array($current_route, ['promos_master'])) {
  361.             $uri = "/promos-rebates";
  362.         }
  363.         else if(in_array($current_route, ['ebooks', 'ebooks_master', 'ebook', 'ebook_master'])) {
  364.             $uri = "/ebooks";
  365.         }
  366.         else if(in_array($current_route, ['customerEbooks_master'])) {
  367.             $uri = "/partner-ebooks";
  368.         }
  369.         else if(in_array($current_route, ['rlw-posts_master'])) {
  370.             if($site_code == 1) {
  371.                 $uri = "/read-listen-watch-rcs";
  372.             }
  373.             else {
  374.                 $uri = "/read-listen-watch";
  375.             }
  376.         }
  377.         else if(in_array($current_route, ['video_master'])) {
  378.             $uri = "/videos";
  379.         }
  380.         else if(in_array($current_route, ['webinar_master'])) {
  381.             $uri = "/partner-webinars";
  382.         }
  383.         else if(in_array($current_route, ['podcast_master']) || strpos($uri, "/podcasts/") !== false) {
  384.             $uri = "/podcasts";
  385.         }
  386.         
  387.         // || strpos($uri, "/events/") !== false
  388.         return $uri;
  389.     }
  391.     public function getPinnedAdByPosition($base_url, $uri, $current_route, $ad_position) {
  392.         $site_code = $this->getSiteCode();
  393.         
  394.         if(!$site_code) {
  395.             return "";
  396.         }
  398.         $uri = $this->uriForPinned($uri, $current_route);
  399.         
  400.         $qb = $this->entityManager->createQueryBuilder();
  401.         
  402.         //TODO: look into how efficient this is and if it can be optimized
  403.         $result = $qb->select('item')
  404.             ->from("App\Entity\MediaGroupItem", "item")
  405.             ->join("item.mediaGroup", "mg", "WITH", "mg.position = '{$ad_position}'")
  406.             ->join("item.site", "s", "WITH", "s.id = :site")
  407.             ->setParameter("site", $site_code)
  408.             ->join("item.pins", "p", "WITH", "p.site_id = :site")
  409.             ->setParameter("site", $site_code)
  410.             ->join("App\Entity\MegaMenu", "mm", "WITH", "mm.uuid = p.mega_menu_uuid AND mm.status = 1")
  411.             ->leftJoin("App\Entity\MegaMenu", "mm2", "WITH", "mm2.parent = mm.uuid and mm2.status = 1")
  412.             ->leftJoin("App\Entity\MegaMenu", "mm3", "WITH", "mm3.parent = mm2.uuid and mm3.status = 1")
  413.             ->where("item.status = 1")
  414.             //->andWhere("mm.link = :link OR mm2.link = :link OR mm3.link = :link")
  415.             ->andWhere("mm.link = :link OR mm2.link = :link OR mm3.link = :link    OR mm.link = :link2 OR mm2.link = :link2 OR mm3.link = :link2")
  416.             ->setParameter("link", $base_url . $uri)
  417.             ->setParameter("link2", $base_url . $uri . "/") //Some have trailing slashes. Should clean this up on save.
  418.             //->andWhere("item.site_default = :site_default")
  419.             ->setMaxResults(1)
  420.             ->addOrderBy("mm.position", "ASC")
  421.             ->addOrderBy("mm2.position", "ASC")
  422.             ->addOrderBy("mm3.position", "ASC")
  423.             ->addOrderBy("item.published_at", "DESC")
  424.             ->getQuery()
  425.             ->getResult();
  426.             
  427.         if($result) {
  428.             return $result[0];
  429.         }
  430.         else {
  431.             return "";
  432.         }
  433.     }
  435.     public function getContentByType ($type = "") {
  436.         $site_code = $this->getSiteCode();
  437.         $qb = $this->entityManager->createQueryBuilder();
  438.         
  439.         $qb->select('c')
  440.             ->from("App\Entity\Content", "c")
  441.             ->where("c.type = :type")
  442.             ->andWhere("c.status = 1 ");
  443.         if($site_code) {
  444.             $qb->join("c.site", "s", "WITH", "s.id = :site")
  445.                 ->setParameter("site", $site_code);
  446.         }
  447.         $content = $qb
  448.             ->setParameter("type", $type)
  449.             ->getQuery()->getResult();
  450.         
  451.         if($content) {
  452.             return $content;
  453.         }
  454.         return [];
  455.     }
  457.     
  458.     public function getAdsForAnalyticsAggregated($customer, $startDate, $endDate)
  459.     {
  460.         $query = implode(" ", array (
  461.                 "SELECT DISTINCT item",
  462.                 "FROM",
  463.                     "App\Entity\MediaGroupItem item",
  464.                 "JOIN",
  465.                     "item.customers customer",
  466.                 "LEFT JOIN",
  467.                     "customer.prnt prnt",
  468.                 "JOIN",
  469.                     "item.ad_analytics_daily ad_analytics_daily",
  470.                 "WHERE",
  471.                     "(customer.id = :id OR prnt.id = :id)",
  472.                     "AND ad_analytics_daily.day BETWEEN :start AND :end",
  473.             ));
  474.         
  475.         $results = $this->entityManager
  476.             ->createQuery($query)
  477.             ->setParameter("id", $customer->getId())
  478.             ->setParameter("start", $startDate)
  479.             ->setParameter("end", $endDate)
  480.         ->getResult();
  481.         
  482.         return $results;
  483.     }
  484.     
  485.     public function countAdImpressions($item, $startDate, $endDate, $site_id="")
  486.     {
  487.         $query = implode(" ", array (
  488.                 "SELECT COUNT(impression.traffic_data)",
  489.                 "FROM",
  490.                     "App\Entity\AdImpression impression",
  491.                 "WHERE",
  492.                     "impression.media_group_item = :mg",
  493.                     "AND (impression.requested_at BETWEEN :start AND :end)",
  494.                     
  495.                     ($site_id ? implode(" ", array (
  496.                         "AND impression.site_id = {$site_id}",
  497.                     )) : ""),
  498.                     
  499.             ));
  500.         
  501.         $count = $this->entityManager
  502.             ->createQuery($query)
  503.             ->setParameter("mg", $item)
  504.             ->setParameter("start", $startDate)
  505.             ->setParameter("end", $endDate)
  506.         ->getResult();
  507.         
  508.         if (!empty($count)) {
  509.             return (int) $count[0][1];
  510.         }
  511.         
  512.         return 0;
  513.     }
  514.     
  515.     public function countAdImpressionsGroupedBySite($item, $startDate, $endDate)
  516.     {
  517.         $query = implode(" ", array (
  518.                 "SELECT impression.site_id, COUNT(impression.traffic_data) as impressions",
  519.                 "FROM",
  520.                     "App\Entity\AdImpression impression",
  521.                 "JOIN",
  522.                     "impression.media_group_item item",
  523.                 "WHERE",
  524.                     "item.id = :id",
  525.                     "AND (impression.requested_at BETWEEN :start AND :end)",
  526.                 "GROUP BY impression.site_id",
  527.             ));
  528.         
  529.         $results = $this->entityManager
  530.             ->createQuery($query)
  531.             ->setParameter("id", $item->getId())
  532.             ->setParameter("start", $startDate)
  533.             ->setParameter("end", $endDate)
  534.         ->getResult();
  535.         
  536.         return $results;
  537.     }
  538.     
  539.     public function countAdImpressionsAggregated($item, $startDate, $endDate, $site_id="")
  540.     {
  541.         $query = implode(" ", array (
  542.                 "SELECT SUM(ad_analytics_daily.impressions)",
  543.                 "FROM",
  544.                     "App\Entity\AdAnalyticsDaily ad_analytics_daily",
  545.                 "JOIN",
  546.                     "ad_analytics_daily.media_group_item item",
  547.                 "WHERE",
  548.                     "item.id = :id",
  549.                     "AND ad_analytics_daily.day BETWEEN :start AND :end",
  550.                     ($site_id ? implode(" ", [
  551.                         "AND ad_analytics_daily.site_id = {$site_id}",
  552.                     ]) : ""),
  553.             ));
  554.         
  555.         $count = $this->entityManager
  556.             ->createQuery($query)
  557.             ->setParameter("id", $item)
  558.             ->setParameter("start", $startDate)
  559.             ->setParameter("end", $endDate)
  560.         ->getResult();
  561.         
  562.         if (!empty($count)) {
  563.             return (int) $count[0][1];
  564.         }
  565.         
  566.         return 0;
  567.     }
  568.     
  569.     public function countAdClicks($item, $startDate, $endDate, $site_id="")
  570.     {
  571.         $query = implode(" ", array (
  572.                 "SELECT COUNT(click.traffic_data)",
  573.                 "FROM",
  574.                     "App\Entity\AdClick click",
  575.                 "JOIN",
  576.                     "click.media_group_item item",
  577.                 "WHERE",
  578.                     "item.id = :id",
  579.                     "AND (click.requested_at BETWEEN :start AND :end)",
  580.                     
  581.                     ($site_id ? implode(" ", array (
  582.                         "AND click.site_id = {$site_id}",
  583.                     )) : ""),
  584.                     
  585.             ));
  586.         
  587.         $count = $this->entityManager
  588.             ->createQuery($query)
  589.             ->setParameter("id", $item->getId())
  590.             ->setParameter("start", $startDate)
  591.             ->setParameter("end", $endDate)
  592.         ->getResult();
  593.         
  594.         if (!empty($count)) {
  595.             return (int) $count[0][1];
  596.         }
  597.         
  598.         return 0;
  599.     }
  600.     
  601.     public function countAdClicksGroupedBySite($item, $startDate, $endDate)
  602.     {
  603.         $query = implode(" ", array (
  604.                 "SELECT click.site_id, COUNT(click.traffic_data) as views",
  605.                 "FROM",
  606.                     "App\Entity\AdClick click",
  607.                 "JOIN",
  608.                     "click.media_group_item item",
  609.                 "WHERE",
  610.                     "item.id = :id",
  611.                     "AND (click.requested_at BETWEEN :start AND :end)",
  612.                 "GROUP BY click.site_id",
  613.             ));
  614.         
  615.         $results = $this->entityManager
  616.             ->createQuery($query)
  617.             ->setParameter("id", $item->getId())
  618.             ->setParameter("start", $startDate)
  619.             ->setParameter("end", $endDate)
  620.         ->getResult();
  621.         
  622.         return $results;
  623.     }
  624.     
  625.     public function countAdClicksAggregated($item, $startDate, $endDate, $site_id="")
  626.     {
  627.         $query = implode(" ", array (
  628.                 "SELECT SUM(ad_analytics_daily.views)",
  629.                 "FROM",
  630.                     "App\Entity\AdAnalyticsDaily ad_analytics_daily",
  631.                 "JOIN",
  632.                     "ad_analytics_daily.media_group_item media_group_item",
  633.                 "WHERE",
  634.                     "media_group_item.id = :id",
  635.                     "AND ad_analytics_daily.day BETWEEN :start AND :end",
  636.                     ($site_id ? implode(" ", [
  637.                         "AND ad_analytics_daily.site_id = {$site_id}",
  638.                     ]) : ""),
  639.             ));
  640.         
  641.         $sum = $this->entityManager
  642.             ->createQuery($query)
  643.             ->setParameter("id", $item->getId())
  644.             ->setParameter("start", $startDate)
  645.             ->setParameter("end", $endDate)
  646.         ->getResult();
  647.         
  648.         if (!empty($sum)) {
  649.             return (int) $sum[0][1];
  650.         }
  651.         
  652.         return 0;
  653.     }
  654.     
  655.     public function countTotalAdImpressions($customer, $startDate, $endDate)
  656.     {
  657.         $query = implode(" ", array (
  658.                 "SELECT COUNT(impression.traffic_data)",
  659.                 "FROM",
  660.                     "App\Entity\AdImpression impression",
  661.                 "JOIN",
  662.                     "impression.media_group_item item",
  663.                 "JOIN",
  664.                     "item.customers customer",
  665.                 "WHERE",
  666.                     "customer.id = :id",
  667.                     "AND (impression.requested_at BETWEEN :start AND :end)",
  668.             ));
  669.         
  670.         $count = $this->entityManager
  671.             ->createQuery($query)
  672.             ->setParameter("id", $customer->getId())
  673.             ->setParameter("start", $startDate)
  674.             ->setParameter("end", $endDate)
  675.         ->getResult();
  676.         
  677.         if (!empty($count)) {
  678.             return (int) $count[0][1];
  679.         }
  680.         
  681.         return 0;
  682.     }
  683.     
  684.     public function countTotalAdImpressionsAggregated($customer, $startDate, $endDate)
  685.     {
  686.         $query = implode(" ", array (
  687.                 "SELECT SUM(ad_analytics_daily.impressions)",
  688.                 "FROM",
  689.                     "App\Entity\AdAnalyticsDaily ad_analytics_daily",
  690.                 "JOIN",
  691.                     "ad_analytics_daily.media_group_item item",
  692.                 "JOIN",
  693.                     "item.customers customer",
  694.                 "WHERE",
  695.                     "customer.id = :id",
  696.                     "AND (ad_analytics_daily.day BETWEEN :start AND :end)",
  697.             ));
  698.         
  699.         $count = $this->entityManager
  700.             ->createQuery($query)
  701.             ->setParameter("id", $customer->getId())
  702.             ->setParameter("start", $startDate)
  703.             ->setParameter("end", $endDate)
  704.         ->getResult();
  705.         
  706.         if (!empty($count)) {
  707.             return (int) $count[0][1];
  708.         }
  709.         
  710.         return 0;
  711.     }
  712.     
  713.     public function countTotalAdClicks($customer, $startDate, $endDate)
  714.     {
  715.         $query = implode(" ", array (
  716.                 "SELECT COUNT(click.traffic_data)",
  717.                 "FROM",
  718.                     "App\Entity\AdClick click",
  719.                 "JOIN",
  720.                     "click.media_group_item item",
  721.                 "JOIN",
  722.                     "item.customers customer",
  723.                 "WHERE",
  724.                     "customer.id = :id",
  725.                     "AND (click.requested_at BETWEEN :start AND :end)",
  726.             ));
  727.         
  728.         $count = $this->entityManager
  729.             ->createQuery($query)
  730.             ->setParameter("id", $customer->getId())
  731.             ->setParameter("start", $startDate)
  732.             ->setParameter("end", $endDate)
  733.         ->getResult();
  734.         
  735.         if (!empty($count)) {
  736.             return (int) $count[0][1];
  737.         }
  738.         
  739.         return 0;
  740.     }
  741.     
  742.     public function countTotalAdClicksAggregated($customer, $startDate, $endDate)
  743.     {
  744.         $query = implode(" ", array (
  745.                 "SELECT SUM(ad_analytics_daily.views)",
  746.                 "FROM",
  747.                     "App\Entity\AdAnalyticsDaily ad_analytics_daily",
  748.                 "JOIN",
  749.                     "ad_analytics_daily.media_group_item item",
  750.                 "JOIN",
  751.                     "item.customers customer",
  752.                 "WHERE",
  753.                     "customer.id = :id",
  754.                     "AND (ad_analytics_daily.day BETWEEN :start AND :end)",
  755.             ));
  756.         
  757.         $count = $this->entityManager
  758.             ->createQuery($query)
  759.             ->setParameter("id", $customer->getId())
  760.             ->setParameter("start", $startDate)
  761.             ->setParameter("end", $endDate)
  762.         ->getResult();
  763.         
  764.         if (!empty($count)) {
  765.             return (int) $count[0][1];
  766.         }
  767.         
  768.         return 0;
  769.     }
  771. }