src/Services/MysqlQueries.php line 38

Open in your IDE?
  1. <?php
  3. namespace App\Services;
  5. use App\Config;
  6. use App\Entity\AdvertiserOfferCount;
  7. use App\Entity\AffiliateOfferCapping;
  8. use App\Entity\AutomaticDisableLinkParams;
  9. use App\Entity\BulkCap;
  10. use App\Entity\BulkCapByAffiliate;
  11. use App\Entity\DisableLinks;
  12. use App\Entity\DisableLinksDump;
  13. use App\Entity\GoalDisableLinkAlertSent;
  14. use App\Entity\GoalDisableLinkParams;
  15. use App\Entity\RetentionOptimisation;
  16. use App\Entity\ScheduledDisableLinks;
  17. use App\Entity\SourceBlock;
  18. use Doctrine\ORM\EntityManagerInterface;
  19. use App\Entity\ImpressionOptimisation;
  21. /**
  22.  *
  23.  * Mysql queries were written here before Mysql quries were written in Repository corresponding to Entity
  24.  *
  25.  * Class MysqlQueries
  26.  * @package App\Services
  27.  */
  28. class MysqlQueries
  29. {
  31.     private $em;
  33.     /**
  34.      * MysqlQueries constructor.
  35.      *
  36.      * @param $em
  37.      */
  38.     public function __construct(EntityManagerInterface $em)
  39.     {
  40.         $this->em = $em;
  41.         $this->em->getConnection()->getConfiguration()->setSQLLogger(null);
  42.     }
  44.     public function insertToSourceBlock($affiliateId, $source, $advertiserId)
  45.     {
  47.         $combinationExist = $this->checkSourceBlockExist($affiliateId, $source, $advertiserId);
  48.         if ($combinationExist != null) {
  49.             return;
  50.         }
  52.         $entity = new SourceBlock();
  54.         $entity->setAffiliateId($affiliateId);
  55.         $entity->setSource($source);
  56.         $entity->setAdvertiserId($advertiserId);
  57.         $entity->setDateInserted(new \DateTime('now'));
  58.         $entity->setDateUpdated(new \DateTime('now'));
  60.         $this->em->persist($entity);
  61.         $this->em->flush();
  62.     }
  64.     public function checkSourceBlockExist($affiliateId, $source, $advertiserId)
  65.     {
  66.         return $this->em->getRepository('App\Entity\SourceBlock')->findOneBy([
  67.             'advertiserId' => $advertiserId,
  68.             'affiliateId'  => $affiliateId,
  69.             'source'       => $source
  70.         ]);
  71.     }
  73.     public function getSourceBlock()
  74.     {
  76.         return $this->em->getRepository('App\Entity\SourceBlock')->findAll();
  77.     }
  79.     public function deleteSourceBlockById($id)
  80.     {
  81.         $entity = $this->em->getRepository('App\Entity\SourceBlock')->findOneBy(array('id' => $id));
  83.         if ($entity != null) {
  84.             $this->em->remove($entity);
  85.             $this->em->flush();
  86.         }
  87.     }
  89.     public function deleteAutomaticDisableLink($id)
  90.     {
  91.         $entity = $this->em->getRepository('App\Entity\AutomaticDisableLinkParams')->findOneBy(array('id' => $id));
  93.         if ($entity != null) {
  94.             $this->em->remove($entity);
  95.             $this->em->flush();
  96.         }
  97.     }
  99.     public function deleteGoalDisableLink($id)
  100.     {
  101.         $entity = $this->em->getRepository('App\Entity\GoalDisableLinkParams')->findOneBy(array('id' => $id));
  103.         if ($entity != null) {
  104.             $this->em->remove($entity);
  105.             $this->em->flush();
  106.         }
  107.     }
  109.     public function getSourceBlockData()
  110.     {
  112.         $repository = $this->em->getRepository('App\Entity\SourceBlock');
  114.         $result = $repository->createQueryBuilder('a')->select('a.affiliateId', 'a.source', 'a.advertiserId')->getQuery()->getResult();
  116.         return $result;
  117.     }
  119.     public function getDisableLinkByMd5($md5)
  120.     {
  121.         return $this->em->getRepository('App\Entity\DisableLinks')->findOneBy(['md5' => $md5]);
  122.     }
  124.     public function getDisableLinksByMd5Arr($md5Arr)
  125.     {
  127.         $repo = $this->em->getRepository('App\Entity\DisableLinks');
  129.         $entity = $repo->createQueryBuilder('a')
  130.             ->select('a.offerId', 'a.affiliateId', 'a.source', 'a.addedFrom', 'a.md5', 'a.meta', 'a.trackingAccount')
  131.             ->where('a.md5 IN (:md5Arr)')
  132.             ->setParameters(['md5Arr' => $md5Arr])
  133.             ->getQuery()
  134.             ->getResult();
  136.         return $entity;
  137.     }
  139.     public function insertToDisableLink($md5, $offerId, $affiliateId, $source, $affsub2, $affSub3, $affSub5, $addedFrom, $advertiserId, $metaData, $trackingAccount = Config::TUNE_ACCOUNT_DEFAULT)
  140.     {
  141.         $entity = new DisableLinks();
  143.         $entity->setMd5($md5);
  144.         $entity->setOfferId($offerId);
  145.         $entity->setAffiliateId($affiliateId);
  146.         $entity->setSource($source);
  147.         $entity->setAffsub2($affsub2);
  148.         $entity->setAffsub3($affSub3);
  149.         $entity->setAffsub5($affSub5);
  150.         $entity->setTrackingAccount($trackingAccount);
  151.         $entity->setDateInserted(new \DateTime('now'));
  152.         $entity->setAddedFrom($addedFrom);
  153.         $entity->setAdvertiserId($advertiserId);
  154.         $entity->setmeta($metaData);
  156.         $this->em->persist($entity);
  157.         $this->em->flush();
  158.     }
  161.     public function insertToDisableLinkDump($md5, $offerId, $affiliateId, $source, $affsub2, $addedFrom, $advertiserId, $metaData, $wasAddedOn, $affsub3, $affsub5)
  162.     {
  163.         $entity = new DisableLinksDump();
  165.         $entity->setMd5($md5);
  166.         $entity->setOfferId($offerId);
  167.         $entity->setAffiliateId($affiliateId);
  168.         $entity->setSource($source);
  169.         $entity->setAffsub2($affsub2);
  170.         $entity->setAffsub3($affsub3);
  171.         $entity->setAffsub5($affsub5);
  172.         $entity->setDateInserted(new \DateTime('now'));
  173.         $entity->setAddedFrom($addedFrom);
  174.         $entity->setAdvertiserId($advertiserId);
  175.         $entity->setMeta($metaData);
  176.         $entity->setWasAddedOn($wasAddedOn);
  178.         $this->em->persist($entity);
  179.         $this->em->flush();
  180.     }
  182.     public function checkAutomaticDisableLinkByAdvertiser($advertiserId, $tuneAccount)
  183.     {
  184.         return $this->em->getRepository('App\Entity\AutomaticDisableLinkParams')->findOneBy(['advertiserId' => $advertiserId, 'tuneAccount' => $tuneAccount]);
  185.     }
  187.     public function checkAutomaticDisableLinkByAffiliate($affiliateId, $tuneAccount)
  188.     {
  189.         return $this->em->getRepository('App\Entity\AutomaticDisableLinkParams')->findOneBy(['affiliateId' => $affiliateId, 'tuneAccount' => $tuneAccount]);
  190.     }
  192.     public function checkAutomaticDisableLinkByOffer($offerId, $tuneAccount)
  193.     {
  194.         return $this->em->getRepository('App\Entity\AutomaticDisableLinkParams')->findOneBy(['offerId' => $offerId, 'tuneAccount' => $tuneAccount]);
  195.     }
  197.     public function saveAutomaticDisableLinkByAdvertiser($advertiserId, $advertiserName, $tuneAccount)
  198.     {
  200.         $entity = new AutomaticDisableLinkParams();
  201.         $entity->setAdvertiserId($advertiserId);
  202.         $entity->setAdvertiserName($advertiserName);
  203.         $entity->setDateUpdated(new \DateTime('now'));
  204.         $entity->setDateInserted(new \DateTime('now'));
  205.         $entity->setAddedFrom(Config::AUTOMATIC_DISABLE_LINK_ADDED_BY_ADVERTISER);
  206.         $entity->setTuneAccount($tuneAccount);
  208.         $this->em->persist($entity);
  209.         $this->em->flush();
  210.     }
  212.     public function saveAutomaticDisableLinkByAffiliate($affiliateId, $affiliateName, $tuneAccount)
  213.     {
  215.         $entity = new AutomaticDisableLinkParams();
  216.         $entity->setAffiliateId($affiliateId);
  217.         $entity->setAffiliateName($affiliateName);
  218.         $entity->setDateUpdated(new \DateTime('now'));
  219.         $entity->setDateInserted(new \DateTime('now'));
  220.         $entity->setAddedFrom(Config::AUTOMATIC_DISABLE_LINK_ADDED_BY_AFFILIATE);
  221.         $entity->setTuneAccount($tuneAccount);
  222.         $this->em->persist($entity);
  223.         $this->em->flush();
  224.     }
  226.     public function saveAutomaticDisableLinkByOffer($offerId, $offerName, $tuneAccount)
  227.     {
  229.         $entity = new AutomaticDisableLinkParams();
  230.         $entity->setOfferId($offerId);
  231.         $entity->setOfferName($offerName);
  232.         $entity->setDateUpdated(new \DateTime('now'));
  233.         $entity->setDateInserted(new \DateTime('now'));
  234.         $entity->setAddedFrom(Config::AUTOMATIC_DISABLE_LINK_ADDED_BY_OFFER);
  235.         $entity->setTuneAccount($tuneAccount);
  236.         $this->em->persist($entity);
  237.         $this->em->flush();
  238.     }
  240.     public function getAutomaticDisableLinkData($tuneAccount = Config::MAFO_SYSTEM_IDENTIFIER_TUNE_MOBILE)
  241.     {
  243.         $repository = $this->em->getRepository('App\Entity\AutomaticDisableLinkParams');
  245.         $result = $repository
  246.         ->createQueryBuilder('a')
  247.         ->select('a.affiliateId', 'a.offerId', 'a.advertiserId', 'a.advertiserName',
  248.          'a.affiliateName', 'a.offerName', 'a.dateInserted', 'a.id', 'a.tuneAccount');
  250.         if ($tuneAccount) {
  251.             $result->where('a.tuneAccount = :tuneAccount');
  252.             $result->setParameter('tuneAccount', $tuneAccount);
  253.         }
  255.         return $result->getQuery()->getResult();
  257.     }
  259.     public function getDisableLinkData()
  260.     {
  262.         $repository = $this->em->getRepository('App\Entity\DisableLinks');
  264.         $result = $repository->createQueryBuilder('a')->select('a.affiliateId', 'a.offerId', 'a.advertiserId', 'a.addedFrom', 'a.dateInserted', 'a.source', 'a.meta')->orderBy('a.id', 'DESC')->getQuery()->getResult();
  266.         return $result;
  267.     }
  269.     public function getGoalDisableLinkByGoalAndOffer($offerId, $goalId)
  270.     {
  272.         return $this->em->getRepository('App\Entity\GoalDisableLinkParams')->findOneBy([
  273.             'goalId'  => $goalId,
  274.             'offerId' => $offerId
  275.         ]);
  276.     }
  278.     public function insertToGoalDisableLinkByOffer($offerId, $offerName, $advertiserId, $advertiserName, $goalId, $goalName, $budget, $sendAlert, $setAutoblock, $cr, $relationshipType)
  279.     {
  281.         $entity = new GoalDisableLinkParams();
  282.         $entity->setOfferName($offerName);
  283.         $entity->setOfferId($offerId);
  284.         $entity->setAdvertiserId($advertiserId);
  285.         $entity->setAdvertiserName($advertiserName);
  286.         $entity->setGoalId($goalId);
  287.         $entity->setGoalName($goalName);
  288.         $entity->setAddedFrom(Config::GOAL_DISABLE_LINK_ADDED_FROM_OFFER);
  289.         $entity->setBudget($budget);
  290.         $entity->setSendAlert($sendAlert);
  291.         $entity->setAutoBlock($setAutoblock);
  292.         $entity->setRelationshipType($relationshipType);
  293.         $entity->setCr($cr);
  294.         $entity->setDateUpdated(new \DateTime('now'));
  295.         $entity->setDateInserted(new \DateTime('now'));
  297.         $this->em->persist($entity);
  298.         $this->em->flush();
  299.     }
  301.     public function getGoalDisableLinkData($offerId = null)
  302.     {
  304.         $repository = $this->em->getRepository('App\Entity\GoalDisableLinkParams');
  306.         $entity = $repository->createQueryBuilder('a')
  307.             ->select('a.id', 'a.goalId', 'a.goalName', 'a.offerId', 'a.offerName', 'a.advertiserId', 'a.advertiserName', 'a.budget', 'a.addedFrom', 'a.sendAlert', 'a.autoBlock', 'a.relationshipType', 'a.cr')
  308.             ->where('a.offerId > 0');
  310.         if ($offerId) {
  311.             $entity->andWhere('a.offerId = :offerId');
  312.             $entity->setParameter('offerId', $offerId);
  313.         }
  315.         return $entity
  316.             ->getQuery()
  317.             ->getResult();
  318.     }
  320.     public function updateGoalDisableLinks($id, $sendAlert, $autoBlock)
  321.     {
  322.         $entity = $this->em->getRepository('App\Entity\GoalDisableLinkParams')->findOneBy(['id' => $id]);
  324.         if ($entity) {
  325.             $entity->setSendAlert($sendAlert);
  326.             $entity->setAutoBlock($autoBlock);
  327.             $this->em->persist($entity);
  328.             $this->em->flush();
  329.         }
  330.     }
  332.     public function getDisableLinkLogs($affiliateArr, $offerArr, $advertiserArr, $sourceArr, $addedFrom, $startDate, $endDate)
  333.     {
  335.         $repository = $this->em->getRepository('App\Entity\DisableLinks');
  337.         $result = $repository
  338.             ->createQueryBuilder('a')
  339.             ->select(
  340.                 'a.affiliateId',
  341.                 'a.offerId',
  342.                 'a.source',
  343.                 'a.advertiserId',
  344.                 'a.dateInserted',
  345.                 'a.addedFrom',
  346.                 'a.source',
  347.                 'a.meta',
  348.                 'a.id',
  349.                 'a.md5',
  350.                 'a.trackingAccount'
  351.             )
  352.             ->where('a.dateInserted >= :startDate')
  353.             ->andWhere('a.dateInserted <= :endDate')
  354.             ->setParameter('startDate', $startDate)
  355.             ->setParameter('endDate', $endDate);
  357.         if (! empty($affiliateArr)) {
  358.             $result->andWhere('a.affiliateId IN (:affiliateId)');
  359.             $result->setParameter('affiliateId', $affiliateArr);
  360.         }
  362.         if (! empty($offerArr)) {
  363.             $result->andWhere('a.offerId IN (:offerId)');
  364.             $result->setParameter('offerId', $offerArr);
  365.         }
  367.         if (! empty($addedFrom)) {
  368.             $result->andWhere('a.addedFrom IN (:addedFrom)');
  369.             $result->setParameter('addedFrom', $addedFrom);
  370.         }
  372.         if (! empty($advertiserArr)) {
  373.             $result->andWhere('a.advertiserId IN (:advertiserId)');
  374.             $result->setParameter('advertiserId', $advertiserArr);
  375.         }
  377.         if (! empty($sourceArr)) {
  378.             $result->andWhere('a.source IN (:source)');
  379.             $result->setParameter('source', $sourceArr);
  380.         }
  382.         return $result->orderBy('a.id', 'DESC')->getQuery()->getResult();
  383.     }
  385.     public function getDisableLinkDataByAddedFromAndNotOfAdvertiser($addedFrom, $advertiserIdArr)
  386.     {
  388.         $repo = $this->em->getRepository('App\Entity\DisableLinks');
  390.         $entity = $repo->createQueryBuilder('a')->select('a.md5', 'a.offerId', 'a.affiliateId', 'a.source', 'a.affsub2', 'a.dateInserted', 'a.addedFrom', 'a.advertiserId', 'a.meta')->where('a.addedFrom = :addedFrom')->andWhere('a.advertiserId NOT IN (:advertiserIdArr)')->setParameters([
  391.             'addedFrom'       => $addedFrom,
  392.             'advertiserIdArr' => $advertiserIdArr
  393.         ])->getQuery()->getResult();
  395.         return $entity;
  396.     }
  398.     public function getDisableLinkDataByAddedFrom($addedFrom)
  399.     {
  401.         $repo = $this->em->getRepository('App\Entity\DisableLinks');
  403.         $entity = $repo->createQueryBuilder('a')->select('a.md5', 'a.offerId', 'a.affiliateId', 'a.source', 'a.affsub2', 'a.dateInserted', 'a.addedFrom', 'a.advertiserId', 'a.meta', 'a.id')->where('a.addedFrom = :addedFrom')->setParameters(['addedFrom' => $addedFrom])->getQuery()->getResult();
  405.         return $entity;
  406.     }
  408.     public function getDisableLinkDataByAddedFromAndNotOfOffer($addedFrom, $offerIdArr)
  409.     {
  411.         $repo = $this->em->getRepository('App\Entity\DisableLinks');
  413.         $entity = $repo->createQueryBuilder('a')->select('a.md5', 'a.offerId', 'a.affiliateId', 'a.source', 'a.affsub2', 'a.dateInserted', 'a.addedFrom', 'a.advertiserId', 'a.meta', 'a.id')->where('a.addedFrom = :addedFrom')->andWhere('a.offerId NOT IN (:offerIdArr)')->setParameters([
  414.             'addedFrom'  => $addedFrom,
  415.             'offerIdArr' => $offerIdArr
  416.         ])->getQuery()->getResult();
  418.         return $entity;
  419.     }
  421.     public function getDisableLinkDataByAddedFromAndNotOfAffiliate($addedFrom, $affiliateIdArr)
  422.     {
  424.         $repo = $this->em->getRepository('App\Entity\DisableLinks');
  426.         $entity = $repo->createQueryBuilder('a')->select('a.md5', 'a.offerId', 'a.affiliateId', 'a.source', 'a.affsub2', 'a.dateInserted', 'a.addedFrom', 'a.advertiserId', 'a.meta', 'a.id')->where('a.addedFrom = :addedFrom')->andWhere('a.affiliateId NOT IN (:affiliateIdArr)')->setParameters([
  427.             'addedFrom'      => $addedFrom,
  428.             'affiliateIdArr' => $affiliateIdArr
  429.         ])->getQuery()->getResult();
  431.         return $entity;
  432.     }
  434.     public function deleteDisableLinkByMd5($md5)
  435.     {
  436.         $entity = $this->em->getRepository('App\Entity\DisableLinks')->findOneBy(array('md5' => $md5));
  438.         if ($entity != null) {
  439.             $this->em->remove($entity);
  440.             $this->em->flush();
  441.         }
  442.     }
  444.     public function insertToBulkCap($affiliateTagId, $affiliateTagName, $affiliateOfferCapType, $offerId, $offerName, $capValue, $addedBy)
  445.     {
  447.         $entity = new BulkCap();
  448.         $entity->setAffiliateTagId($affiliateTagId);
  449.         $entity->setAffiliateTagName($affiliateTagName);
  450.         $entity->setAffiliateOfferCapType($affiliateOfferCapType);
  451.         $entity->setOfferId($offerId);
  452.         $entity->setOfferName($offerName);
  453.         $entity->setCapValue($capValue);
  454.         $entity->setDateInserted(new \DateTime('now'));
  455.         $entity->setAddedBy($addedBy);
  457.         $this->em->persist($entity);
  458.         $this->em->flush();
  459.     }
  461.     public function deleteFromBulkCapById($id)
  462.     {
  463.         $entity = $this->em->getRepository('App\Entity\BulkCap')->findOneBy(['id' => $id]);
  465.         if ($entity != null) {
  466.             $this->em->remove($entity);
  467.             $this->em->flush();
  468.         }
  469.     }
  471.     public function getOfferIdAffiliateTagIdFromBulkCap($offerId, $affiliateTagId, $capType)
  472.     {
  473.         return $this->em->getRepository('App\Entity\BulkCap')->findOneBy([
  474.             'offerId'               => $offerId,
  475.             'affiliateTagId'        => $affiliateTagId,
  476.             'affiliateOfferCapType' => $capType
  477.         ]);
  478.     }
  480.     public function getBulkCapData($hourRange = 4380)
  481.     {
  483.         $dataAfterDatetime = date('Y-m-d H:s:i', strtotime("-$hourRange hours"));
  484.         $repo              = $this->em->getRepository('App\Entity\BulkCap');
  486.         $entity = $repo
  487.             ->createQueryBuilder('a')
  488.             ->select('a.affiliateTagId', 'a.affiliateTagName', 'a.affiliateOfferCapType', 'a.offerId', 'a.offerName', 'a.dateInserted', 'a.id', 'a.capValue', 'a.addedBy')
  489.             ->where('a.dateInserted > :dateTime')
  490.             ->setParameters(['dateTime' => $dataAfterDatetime])
  491.             ->orderBy('a.id', 'DESC')
  492.             ->getQuery()->getResult();
  494.         return $entity;
  495.     }
  497.     public function getBulkCapByAffiliateData($hourRange = 48)
  498.     {
  500.         $dataAfterDatetime = date('Y-m-d H:s:i', strtotime("-$hourRange hours"));
  501.         $repo              = $this->em->getRepository('App\Entity\BulkCapByAffiliate');
  503.         $entity = $repo
  504.             ->createQueryBuilder('a')
  505.             ->select('a.affiliateId', 'a.affiliateName', 'a.affiliateOfferCapType', 'a.offerId', 'a.offerName', 'a.dateInserted', 'a.id', 'a.capValue', 'a.addedBy', 'a.tuneAccount')
  506.             ->where('a.dateInserted > :dateTime')
  507.             ->setParameters(['dateTime' => $dataAfterDatetime])
  508.             ->orderBy('a.id', 'DESC')
  509.             ->getQuery()
  510.             ->getResult();
  512.         return $entity;
  513.     }
  515.     public function getCombinationFromAffiliateOfferCapping($affiliateId, $offerId, $capType, $tuneAccount)
  516.     {
  517.         return $this->em->getRepository('App\Entity\AffiliateOfferCapping')->findOneBy([
  518.             'offerId'     => $offerId,
  519.             'affiliateId' => $affiliateId,
  520.             'capType'     => $capType,
  521.             'tuneAccount' => $tuneAccount
  522.         ]);
  523.     }
  525.     public function deleteAffiliateOfferCapById($id)
  526.     {
  527.         $entity = $this->em->getRepository('App\Entity\AffiliateOfferCapping')->findOneBy(['id' => $id]);
  529.         if ($entity != null) {
  530.             $this->em->remove($entity);
  531.             $this->em->flush();
  532.         }
  533.     }
  535.     public function insertToAffiliateOfferCap($affiliateId, $offerId, $capType, $capValue, $tuneAccount)
  536.     {
  538.         $entity = new AffiliateOfferCapping();
  540.         $entity->setAffiliateId($affiliateId);
  541.         $entity->setOfferId($offerId);
  542.         $entity->setCapType($capType);
  543.         $entity->setCapValue($capValue);
  544.         $entity->setDateInserted(new \DateTime('now'));
  545.         $entity->setDateUpdated(new \DateTime('now'));
  546.         $entity->setTuneAccount($tuneAccount);
  548.         $this->em->persist($entity);
  549.         $this->em->flush();
  550.     }
  552.     public function insertToGoalDisableLinkAlertSent($goalId, $offerId, $affiliateId, $source)
  553.     {
  555.         $entity = new GoalDisableLinkAlertSent();
  557.         $entity->setGoalId($goalId);
  558.         $entity->setOfferId($offerId);
  559.         $entity->setAffiliateId($affiliateId);
  560.         $entity->setDateInserted(new \DateTime('now'));
  561.         $entity->setSource($source);
  563.         $this->em->persist($entity);
  564.         $this->em->flush();
  565.     }
  567.     public function checkGoalDisableLinkAlertSent($goalId, $offerId, $affiliateId, $source)
  568.     {
  570.         return $this->em->getRepository('App\Entity\GoalDisableLinkAlertSent')->findOneBy([
  571.             'offerId'     => $offerId,
  572.             'goalId'      => $goalId,
  573.             'affiliateId' => $affiliateId,
  574.             'source'      => $source
  575.         ]);
  576.     }
  578.     public function getRetentionOptimisationByParams($offerId, $goalId, $tuneAccount)
  579.     {
  580.         return $this->em->getRepository('App\Entity\RetentionOptimisation')->findOneBy([
  581.             'offerId' => $offerId,
  582.             'goalId'  => $goalId,
  583.             'tuneAccount' => $tuneAccount
  584.         ]);
  585.     }
  587.     public function updateRetentionOptimisation($offerId, $goalId, $retentionRate, $minimumBudget, $sendAlert, $autoBlock, $tuneAccount)
  588.     {
  589.         $entity = $this->em->getRepository('App\Entity\RetentionOptimisation')->findOneBy([
  590.             'offerId' => $offerId,
  591.             'goalId'  => $goalId,
  592.             'tuneAccount' => $tuneAccount
  593.         ]);
  595.         if ($entity != null) {
  597.             $entity->setRetentionRate($retentionRate);
  598.             $entity->setMinimumBudget($minimumBudget);
  599.             $entity->setSendAlert($sendAlert);
  600.             $entity->setDateUpdated(new \DateTime('now'));
  601.             $entity->setAutoBlock($autoBlock);
  602.             $entity->setTuneAccount($tuneAccount);
  604.             $this->em->persist($entity);
  605.             $this->em->flush();
  606.         }
  607.     }
  609.     public function insertRetentionOptimisation($offerId, $goalId, $retentionRate, $minimumBudget, $sendAlert, $autoBlock, $tuneAccount)
  610.     {
  611.         $entity = new RetentionOptimisation();
  613.         $entity->setOfferId($offerId);
  614.         $entity->setGoalId($goalId);
  615.         $entity->setRetentionRate($retentionRate);
  616.         $entity->setMinimumBudget($minimumBudget);
  617.         $entity->setSendAlert($sendAlert);
  618.         $entity->setAutoBlock($autoBlock);
  619.         $entity->setDateInserted(new \DateTime('now'));
  620.         $entity->setDateUpdated(new \DateTime('now'));
  621.         $entity->setTuneAccount($tuneAccount);
  623.         $this->em->persist($entity);
  624.         $this->em->flush();
  625.     }
  627.     public function getRetentionOptimisation($offerId, $tuneAccount = Config::MAFO_SYSTEM_IDENTIFIER_TUNE_MOBILE)
  628.     {
  630.         $repo = $this->em->getRepository('App\Entity\RetentionOptimisation');
  632.         $entity = $repo->createQueryBuilder('a')
  633.             ->select('a.offerId', 'a.goalId', 'a.retentionRate', 'a.minimumBudget', 'a.sendAlert',
  634.              'a.autoBlock', 'a.dateUpdated as dateUpdated', 'a.dateInserted as dateInserted', 'a.id as id',
  635.               'b.tuneAccount as tuneAccount', 'c.company as advertiserName', 'b.name as offerName', 'b.advertiserId as advertiserId',
  636.               'd.name as goalName'
  637.               )
  638.             ->innerJoin('App\Entity\OfferInfo', 'b', 'WITH', 'a.offerId = b.offerId AND a.tuneAccount = b.tuneAccount')
  639.             ->leftJoin('App\Entity\AdvertiserInfo', 'c', 'WITH', 'b.advertiserId = c.advertiserId AND b.tuneAccount = c.tuneAccount')
  640.             ->leftJoin('App\Entity\OfferGoalsInfo', 'd', 'WITH', 'a.goalId = d.goalId AND a.tuneAccount = d.tuneAccount')
  641.             ->where('a.offerId > 0');
  643.         if ($tuneAccount == Config::MAFO_SYSTEM_IDENTIFIER_TUNE_MOBILE) {
  644.             $entity->andWhere('a.tuneAccount = :tuneAccount');
  645.             $entity->setParameter('tuneAccount', $tuneAccount    );
  646.         }
  648.         if ($offerId) {
  649.             $entity->andWhere('a.offerId = :offerId');
  650.             $entity->setParameter('offerId', $offerId);
  651.         }
  653.         return $entity
  654.             ->orderBy('dateUpdated', 'DESC')
  655.             ->getQuery()
  656.             ->getResult();
  657.     }
  659.     public function deleteRetentionOptimisation($id)
  660.     {
  661.         $entity = $this->em->getRepository('App\Entity\RetentionOptimisation')->findOneBy(['id' => $id]);
  663.         if ($entity != null) {
  664.             $this->em->remove($entity);
  665.             $this->em->flush();
  666.         }
  667.     }
  669.     public function checkScheduledDisableLinksByParam($source, $affiliateId, $offerId)
  670.     {
  672.         return $this->em->getRepository('App\Entity\ScheduledDisableLinks')->findOneBy([
  673.             'source'      => $source,
  674.             'affiliateId' => $affiliateId,
  675.             'offerId'     => $offerId
  676.         ]);
  677.     }
  679.     public function insertToScheduledDisableLinks($source, $affiliate, $offer)
  680.     {
  681.         $entity = new ScheduledDisableLinks();
  683.         $entity->setAffiliateId($affiliate);
  684.         $entity->setOfferId($offer);
  685.         $entity->setSource($source);
  686.         $entity->setIsScheduled(1);
  687.         $entity->setDateInserted(new \DateTime('now'));
  688.         $entity->setDateUpdated(new \DateTime('now'));
  690.         $this->em->persist($entity);
  691.         $this->em->flush();
  692.     }
  694.     public function getScheduledDisableLinkByIsScheduled($isScheduled)
  695.     {
  697.         $repo = $this->em->getRepository('App\Entity\ScheduledDisableLinks');
  699.         $entity = $repo->createQueryBuilder('a')->select('a.offerId', 'a.affiliateId', 'a.source', 'a.isScheduled', 'a.id')->where('a.isScheduled = :isScheduled')->setParameters(['isScheduled' => $isScheduled])->getQuery()->getResult();
  701.         return $entity;
  702.     }
  704.     public function setIsScheduledForScheduledOfferDisableLink($offerId, $affiliateId, $source, $isScheduled)
  705.     {
  707.         $entity = $this->em->getRepository('App\Entity\ScheduledDisableLinks')->findOneBy([
  708.             'offerId'     => $offerId,
  709.             'affiliateId' => $affiliateId,
  710.             'source'      => $source
  711.         ]);
  713.         if ($entity) {
  714.             $entity->setIsScheduled($isScheduled);
  715.             $this->em->persist($entity);
  716.             $this->em->flush();
  717.         }
  718.     }
  720.     public function setIsScheduledForScheduledOfferDisableLinkById($id, $isScheduled)
  721.     {
  723.         $entity = $this->em->getRepository('App\Entity\ScheduledDisableLinks')->findOneBy(['id' => $id]);
  725.         if ($entity) {
  726.             $entity->setIsScheduled($isScheduled);
  727.             $this->em->persist($entity);
  728.             $this->em->flush();
  729.         }
  730.     }
  732.     public function insertToBulkCapByAffiliate($affiliateId, $affiliateName, $affiliateOfferCapType, $offerId, $offerName, $capValue, $addedBy)
  733.     {
  735.         $entity = new BulkCapByAffiliate();
  737.         $entity->setAffiliateId($affiliateId);
  738.         $entity->setAffiliateName($affiliateName);
  739.         $entity->setAffiliateOfferCapType($affiliateOfferCapType);
  740.         $entity->setOfferId($offerId);
  741.         $entity->setOfferName($offerName);
  742.         $entity->setCapValue($capValue);
  743.         $entity->setAddedBy($addedBy);
  744.         $entity->setDateInserted(new \DateTime('now'));
  746.         $this->em->persist($entity);
  747.         $this->em->flush();
  748.     }
  750.     public function getBulkCapByAffiliate($offerId, $affiliateId, $capType)
  751.     {
  753.         return $this->em->getRepository('App\Entity\BulkCapByAffiliate')->findOneBy([
  754.             'offerId'               => $offerId,
  755.             'affiliateId'           => $affiliateId,
  756.             'affiliateOfferCapType' => $capType
  757.         ]);
  758.     }
  760.     public function deleteBulkCapByAffiliateById($id)
  761.     {
  763.         $entity = $this->em->getRepository('App\Entity\BulkCapByAffiliate')->findOneBy(['id' => $id]);
  765.         if ($entity) {
  766.             $this->em->remove($entity);
  767.             $this->em->flush();
  768.         }
  769.     }
  771.     public function insertToAdvertiserOfferCount($advertiserId, $offerCount, $status)
  772.     {
  774.         $entity = new AdvertiserOfferCount();
  776.         $entity->setAdvertiserId($advertiserId);
  777.         $entity->setOfferCount($offerCount);
  778.         $entity->setOfferStatus($status);
  779.         $entity->setDateInserted(new \DateTime('now'));
  781.         $this->em->persist($entity);
  782.         $this->em->flush();
  783.     }
  785.     public function getDataForAdvertiserOfferCountForChart($advertiserIdArr, $dateStart, $dateEnd, $chooseBy, $status)
  786.     {
  788.         $repo = $this->em->getRepository('App\Entity\AdvertiserOfferCount');
  790.         $entity = $repo->createQueryBuilder('a');
  792.         if ($chooseBy == Config::ADVERTISER_INFO_CHOOSE_BY_HOUR) {
  793.             $entity->select('a.advertiserId', 'AVG(a.offerCount) as average', 'HOUR(a.dateInserted) as by_hour', 'DAY(a.dateInserted) as by_day', 'WEEK(a.dateInserted) as by_week', 'MONTH(a.dateInserted) as by_month', 'YEAR(a.dateInserted) as by_year');
  794.         } elseif ($chooseBy == Config::ADVERTISER_INFO_CHOOSE_BY_DAY) {
  795.             $entity->select('a.advertiserId', 'AVG(a.offerCount) as average', 'DAY(a.dateInserted) as by_day', 'WEEK(a.dateInserted) as by_week', 'MONTH(a.dateInserted) as by_month', 'YEAR(a.dateInserted) as by_year');
  796.         } elseif ($chooseBy == Config::ADVERTISER_INFO_CHOOSE_BY_WEEK) {
  797.             $entity->select('a.advertiserId', 'AVG(a.offerCount) as average', 'WEEK(a.dateInserted) as by_week', 'MONTH(a.dateInserted) as by_month', 'YEAR(a.dateInserted) as by_year');
  798.         } elseif ($chooseBy == Config::ADVERTISER_INFO_CHOOSE_BY_MONTH) {
  799.             $entity->select('a.advertiserId', 'AVG(a.offerCount) as average', 'MONTH(a.dateInserted) as by_month', 'YEAR(a.dateInserted) as by_year');
  800.         }
  802.         $entity->where('a.advertiserId IN (:advertiserIds)')->andWhere('a.offerStatus = :status')->andWhere('a.dateInserted >= :dateStart')->andWhere('a.dateInserted <= :dateEnd')->setParameters([
  803.             'advertiserIds' => $advertiserIdArr,
  804.             'status'        => $status,
  805.             'dateStart'     => $dateStart,
  806.             'dateEnd'       => $dateEnd
  807.         ]);
  809.         $entity->groupBy('a.advertiserId');
  811.         if ($chooseBy == Config::ADVERTISER_INFO_CHOOSE_BY_HOUR) {
  812.             $entity->addGroupBy('by_hour', 'by_day', 'by_week', 'by_month', 'by_year');
  813.         } elseif ($chooseBy == Config::ADVERTISER_INFO_CHOOSE_BY_DAY) {
  814.             $entity->addGroupBy('by_day', 'by_week', 'by_month', 'by_year');
  815.         } elseif ($chooseBy == Config::ADVERTISER_INFO_CHOOSE_BY_WEEK) {
  816.             $entity->addGroupBy('by_week', 'by_month', 'by_year');
  817.         } elseif ($chooseBy == Config::ADVERTISER_INFO_CHOOSE_BY_MONTH) {
  818.             $entity->addGroupBy('by_month', 'by_year');
  819.         }
  821.         return $entity->getQuery()->getResult();
  822.     }
  824.     public function getDataForAffiliateOfferCountForChart($affiliateIdArr, $dateStart, $dateEnd, $chooseBy, $offerCountBy)
  825.     {
  827.         $repo = $this->em->getRepository('App\Entity\AffiliateOfferCount');
  829.         $entity = $repo->createQueryBuilder('a');
  830.         if ($offerCountBy == Config::AFFILIATE_INFO_CHOOSE_BY_APPROVED_ACTIVE_OFFER_COUNT) {
  831.             if ($chooseBy == Config::AFFILIATE_INFO_CHOOSE_BY_HOUR) {
  832.                 $entity->select('a.affiliateId', 'AVG(a.approvedActiveOfferCount) as average', 'HOUR(a.dateInserted) as by_hour', 'DAY(a.dateInserted) as by_day', 'WEEK(a.dateInserted) as by_week', 'MONTH(a.dateInserted) as by_month', 'YEAR(a.dateInserted) as by_year');
  833.             } elseif ($chooseBy == Config::AFFILIATE_INFO_CHOOSE_BY_DAY) {
  834.                 $entity->select('a.affiliateId', 'AVG(a.approvedActiveOfferCount) as average', 'DAY(a.dateInserted) as by_day', 'WEEK(a.dateInserted) as by_week', 'MONTH(a.dateInserted) as by_month', 'YEAR(a.dateInserted) as by_year');
  835.             } elseif ($chooseBy == Config::AFFILIATE_INFO_CHOOSE_BY_WEEK) {
  836.                 $entity->select('a.affiliateId', 'AVG(a.approvedActiveOfferCount) as average', 'WEEK(a.dateInserted) as by_week', 'MONTH(a.dateInserted) as by_month', 'YEAR(a.dateInserted) as by_year');
  837.             } elseif ($chooseBy == Config::AFFILIATE_INFO_CHOOSE_BY_MONTH) {
  838.                 $entity->select('a.affiliateId', 'AVG(a.approvedActiveOfferCount) as average', 'MONTH(a.dateInserted) as by_month', 'YEAR(a.dateInserted) as by_year');
  839.             }
  840.         } elseif ($offerCountBy == Config::AFFILIATE_INFO_CHOOSE_BY_APPROVED_OFFER_COUNT) {
  841.             if ($chooseBy == Config::AFFILIATE_INFO_CHOOSE_BY_HOUR) {
  842.                 $entity->select('a.affiliateId', 'AVG(a.approvedOfferCount) as average', 'HOUR(a.dateInserted) as by_hour', 'DAY(a.dateInserted) as by_day', 'WEEK(a.dateInserted) as by_week', 'MONTH(a.dateInserted) as by_month', 'YEAR(a.dateInserted) as by_year');
  843.             } elseif ($chooseBy == Config::AFFILIATE_INFO_CHOOSE_BY_DAY) {
  844.                 $entity->select('a.affiliateId', 'AVG(a.approvedOfferCount) as average', 'DAY(a.dateInserted) as by_day', 'WEEK(a.dateInserted) as by_week', 'MONTH(a.dateInserted) as by_month', 'YEAR(a.dateInserted) as by_year');
  845.             } elseif ($chooseBy == Config::AFFILIATE_INFO_CHOOSE_BY_WEEK) {
  846.                 $entity->select('a.affiliateId', 'AVG(a.approvedOfferCount) as average', 'WEEK(a.dateInserted) as by_week', 'MONTH(a.dateInserted) as by_month', 'YEAR(a.dateInserted) as by_year');
  847.             } elseif ($chooseBy == Config::AFFILIATE_INFO_CHOOSE_BY_MONTH) {
  848.                 $entity->select('a.affiliateId', 'AVG(a.approvedOfferCount) as average', 'MONTH(a.dateInserted) as by_month', 'YEAR(a.dateInserted) as by_year');
  849.             }
  850.         }
  852.         $entity->where('a.affiliateId IN (:affiliateIds)') #->andWhere('a.offerStatus = :status')
  853.             ->andWhere('a.dateInserted >= :dateStart')->andWhere('a.dateInserted <= :dateEnd')->setParameters([
  854.                 'affiliateIds' => $affiliateIdArr,
  855.                 'dateStart'    => $dateStart,
  856.                 'dateEnd'      => $dateEnd
  857.             ]);
  859.         $entity->groupBy('a.affiliateId');
  861.         if ($chooseBy == Config::AFFILIATE_INFO_CHOOSE_BY_HOUR) {
  862.             $entity->addGroupBy('by_hour', 'by_day', 'by_week', 'by_month', 'by_year');
  863.         } elseif ($chooseBy == Config::AFFILIATE_INFO_CHOOSE_BY_DAY) {
  864.             $entity->addGroupBy('by_day', 'by_week', 'by_month', 'by_year');
  865.         } elseif ($chooseBy == Config::AFFILIATE_INFO_CHOOSE_BY_WEEK) {
  866.             $entity->addGroupBy('by_week', 'by_month', 'by_year');
  867.         } elseif ($chooseBy == Config::AFFILIATE_INFO_CHOOSE_BY_MONTH) {
  868.             $entity->addGroupBy('by_month', 'by_year');
  869.         }
  871.         return $entity->getQuery()->getResult();
  872.     }
  874.     public function getImpressionOptimisationByParams($offerId, $affiliateId)
  875.     {
  876.         return $this->em->getRepository('App\Entity\ImpressionOptimisation')->findOneBy([
  877.             'offerId'     => $offerId,
  878.             'affiliateId' => $affiliateId
  879.         ]);
  880.     }
  882.     public function deleteImpressionOptimisation($id, $deletedBy)
  883.     {
  884.         $entity = $this->em->getRepository('App\Entity\ImpressionOptimisation')->findOneBy(['id' => $id]);
  886.         if ($entity != null) {
  887.             $entity->setAddedBy($deletedBy);
  888.             $entity->setDateUpdated(new \DateTime('now'));
  889.             $entity->setIsDeleted(true);
  890.             $this->em->persist($entity);
  891.             $this->em->flush();
  892.         }
  894.         return $entity;
  895.     }
  897.     public function getActiveImpressionOptimisation()
  898.     {
  900.         $repo   = $this->em->getRepository('App\Entity\ImpressionOptimisation');
  901.         $entity = $repo->createQueryBuilder('imp')->select('imp.id', 'imp.offerId', 'imp.affiliateId', 'imp.ctr', 'imp.is_deleted', 'imp.dateInserted', 'imp.dateUpdated', 'imp.addedBy')->where('imp.is_deleted = :isDeleted')->setParameters(['isDeleted' => false])->orderBy('imp.dateUpdated', 'DESC')->getQuery()->getResult();
  903.         return $entity;
  904.     }
  906.     public function createUpdateImpression($offerId, $affiliateId, $ctr, $addedBy)
  907.     {
  908.         $entity = $this->getImpressionOptimisationByParams($offerId, $affiliateId);
  909.         if ($entity != null) {
  910.             $entity->setCtr($ctr);
  911.             $entity->setAddedBy($addedBy);
  912.             $entity->setDateUpdated(new \DateTime('now'));
  913.             $this->em->persist($entity);
  914.             $this->em->flush();
  915.         } else {
  916.             $entity = $this->insertImpressionOptimisation($offerId, $affiliateId, $ctr, $addedBy);
  917.         }
  919.         return $entity;
  920.     }
  922.     public function insertImpressionOptimisation($offerId, $affiliateId, $ctr, $addedBy)
  923.     {
  925.         $entity = new ImpressionOptimisation();
  926.         $entity->setAddedBy($addedBy);
  927.         $entity->setCtr($ctr);
  928.         $entity->setAffiliateId($affiliateId);
  929.         $entity->setOfferId($offerId);
  930.         $entity->setDateUpdated(new \DateTime('now'));
  931.         $entity->setDateInserted(new \DateTime('now'));
  932.         $entity->setIsDeleted(false);
  933.         $this->em->persist($entity);
  934.         $this->em->flush();
  936.         return $entity;
  937.     }
  938.     public function getFraudFlagLogs($affiliateArr, $offerArr, $advertiserArr, $startDate, $endDate)
  939.     {
  941.         $repository = $this->em->getRepository('App\Entity\FraudFlagLogs');
  943.         $result = $repository->createQueryBuilder('a')
  944.             ->select('a.affiliateId', 'a.offerId', 'a.source', 'a.advertiserId', 'a.dateInserted', 'a.advSub1', 'a.source', 'a.id', 'a.transactionId')
  945.             ->where('a.dateInserted >= :startDate')
  946.             ->andWhere('a.dateInserted <= :endDate')
  947.             ->andWhere('a.newStatus <= :newStatus')
  948.             ->setParameter('startDate', $startDate)
  949.             ->setParameter('endDate', $endDate)
  950.             ->setParameter('newStatus', Config::REJECTED_STATUS);
  952.         if (! empty($affiliateArr)) {
  953.             $result->andWhere('a.affiliateId IN (:affiliateId)');
  954.             $result->setParameter('affiliateId', $affiliateArr);
  955.         }
  957.         if (! empty($offerArr)) {
  958.             $result->andWhere('a.offerId IN (:offerId)');
  959.             $result->setParameter('offerId', $offerArr);
  960.         }
  963.         if (! empty($advertiserArr)) {
  964.             $result->andWhere('a.advertiserId IN (:advertiserId)');
  965.             $result->setParameter('advertiserId', $advertiserArr);
  966.         }
  968.         return $result->orderBy('a.id', 'DESC')->getQuery()->getResult();
  969.     }
  971.     public function deleteFraudFlagLogs($id)
  972.     {
  973.         $entity = $this->em->getRepository('App\Entity\FraudFlagLogs')->findOneBy(array('id' => $id));
  975.         if ($entity != null) {
  976.             $this->em->remove($entity);
  977.             $this->em->flush();
  978.         }
  979.     }
  980. }