src/Services/MysqlQueries.php line 41

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(
  248.                 'a.affiliateId',
  249.                 'a.offerId',
  250.                 'a.advertiserId',
  251.                 'a.advertiserName',
  252.                 'a.affiliateName',
  253.                 'a.offerName',
  254.                 'a.dateInserted',
  255.                 'a.id',
  256.                 'a.tuneAccount'
  257.             );
  259.         if ($tuneAccount) {
  260.             $result->where('a.tuneAccount = :tuneAccount');
  261.             $result->setParameter('tuneAccount', $tuneAccount);
  262.         }
  264.         return $result->getQuery()->getResult();
  265.     }
  267.     public function getDisableLinkData()
  268.     {
  270.         $repository = $this->em->getRepository('App\Entity\DisableLinks');
  272.         $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();
  274.         return $result;
  275.     }
  277.     public function getGoalDisableLinkByGoalAndOffer($offerId, $goalId)
  278.     {
  280.         return $this->em->getRepository('App\Entity\GoalDisableLinkParams')->findOneBy([
  281.             'goalId'  => $goalId,
  282.             'offerId' => $offerId
  283.         ]);
  284.     }
  286.     public function insertToGoalDisableLinkByOffer($offerId, $offerName, $advertiserId, $advertiserName, $goalId, $goalName, $budget, $sendAlert, $setAutoblock, $cr, $relationshipType)
  287.     {
  289.         $entity = new GoalDisableLinkParams();
  290.         $entity->setOfferName($offerName);
  291.         $entity->setOfferId($offerId);
  292.         $entity->setAdvertiserId($advertiserId);
  293.         $entity->setAdvertiserName($advertiserName);
  294.         $entity->setGoalId($goalId);
  295.         $entity->setGoalName($goalName);
  296.         $entity->setAddedFrom(Config::GOAL_DISABLE_LINK_ADDED_FROM_OFFER);
  297.         $entity->setBudget($budget);
  298.         $entity->setSendAlert($sendAlert);
  299.         $entity->setAutoBlock($setAutoblock);
  300.         $entity->setRelationshipType($relationshipType);
  301.         $entity->setCr($cr);
  302.         $entity->setDateUpdated(new \DateTime('now'));
  303.         $entity->setDateInserted(new \DateTime('now'));
  305.         $this->em->persist($entity);
  306.         $this->em->flush();
  307.     }
  309.     public function getGoalDisableLinkData($offerId = null)
  310.     {
  312.         $repository = $this->em->getRepository('App\Entity\GoalDisableLinkParams');
  314.         $entity = $repository->createQueryBuilder('a')
  315.             ->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')
  316.             ->where('a.offerId > 0');
  318.         if ($offerId) {
  319.             $entity->andWhere('a.offerId = :offerId');
  320.             $entity->setParameter('offerId', $offerId);
  321.         }
  323.         return $entity
  324.             ->getQuery()
  325.             ->getResult();
  326.     }
  328.     public function updateGoalDisableLinks($id, $sendAlert, $autoBlock)
  329.     {
  330.         $entity = $this->em->getRepository('App\Entity\GoalDisableLinkParams')->findOneBy(['id' => $id]);
  332.         if ($entity) {
  333.             $entity->setSendAlert($sendAlert);
  334.             $entity->setAutoBlock($autoBlock);
  335.             $this->em->persist($entity);
  336.             $this->em->flush();
  337.         }
  338.     }
  340.     public function getDisableLinkLogs($affiliateArr, $offerArr, $advertiserArr, $sourceArr, $addedFrom, $startDate, $endDate)
  341.     {
  343.         $repository = $this->em->getRepository('App\Entity\DisableLinks');
  345.         $result = $repository
  346.             ->createQueryBuilder('a')
  347.             ->select(
  348.                 'a.affiliateId',
  349.                 'a.offerId',
  350.                 'a.source',
  351.                 'a.advertiserId',
  352.                 'a.dateInserted',
  353.                 'a.addedFrom',
  354.                 'a.source',
  355.                 'a.meta',
  356.                 'a.id',
  357.                 'a.md5',
  358.                 'a.trackingAccount'
  359.             )
  360.             ->where('a.dateInserted >= :startDate')
  361.             ->andWhere('a.dateInserted <= :endDate')
  362.             ->setParameter('startDate', $startDate)
  363.             ->setParameter('endDate', $endDate);
  365.         if (! empty($affiliateArr)) {
  366.             $result->andWhere('a.affiliateId IN (:affiliateId)');
  367.             $result->setParameter('affiliateId', $affiliateArr);
  368.         }
  370.         if (! empty($offerArr)) {
  371.             $result->andWhere('a.offerId IN (:offerId)');
  372.             $result->setParameter('offerId', $offerArr);
  373.         }
  375.         if (! empty($addedFrom)) {
  376.             $result->andWhere('a.addedFrom IN (:addedFrom)');
  377.             $result->setParameter('addedFrom', $addedFrom);
  378.         }
  380.         if (! empty($advertiserArr)) {
  381.             $result->andWhere('a.advertiserId IN (:advertiserId)');
  382.             $result->setParameter('advertiserId', $advertiserArr);
  383.         }
  385.         if (! empty($sourceArr)) {
  386.             $result->andWhere('a.source IN (:source)');
  387.             $result->setParameter('source', $sourceArr);
  388.         }
  390.         return $result->orderBy('a.id', 'DESC')->getQuery()->getResult();
  391.     }
  393.     public function getDisableLinkDataByAddedFromAndNotOfAdvertiser($addedFrom, $advertiserIdArr)
  394.     {
  396.         $repo = $this->em->getRepository('App\Entity\DisableLinks');
  398.         $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([
  399.             'addedFrom'       => $addedFrom,
  400.             'advertiserIdArr' => $advertiserIdArr
  401.         ])->getQuery()->getResult();
  403.         return $entity;
  404.     }
  406.     public function getDisableLinkDataByAddedFrom($addedFrom)
  407.     {
  409.         $repo = $this->em->getRepository('App\Entity\DisableLinks');
  411.         $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();
  413.         return $entity;
  414.     }
  416.     public function getDisableLinkDataByAddedFromAndNotOfOffer($addedFrom, $offerIdArr)
  417.     {
  419.         $repo = $this->em->getRepository('App\Entity\DisableLinks');
  421.         $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([
  422.             'addedFrom'  => $addedFrom,
  423.             'offerIdArr' => $offerIdArr
  424.         ])->getQuery()->getResult();
  426.         return $entity;
  427.     }
  429.     public function getDisableLinkDataByAddedFromAndNotOfAffiliate($addedFrom, $affiliateIdArr)
  430.     {
  432.         $repo = $this->em->getRepository('App\Entity\DisableLinks');
  434.         $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([
  435.             'addedFrom'      => $addedFrom,
  436.             'affiliateIdArr' => $affiliateIdArr
  437.         ])->getQuery()->getResult();
  439.         return $entity;
  440.     }
  442.     public function deleteDisableLinkByMd5($md5)
  443.     {
  444.         $entity = $this->em->getRepository('App\Entity\DisableLinks')->findOneBy(array('md5' => $md5));
  446.         if ($entity != null) {
  447.             $this->em->remove($entity);
  448.             $this->em->flush();
  449.         }
  450.     }
  452.     public function insertToBulkCap($affiliateTagId, $affiliateTagName, $affiliateOfferCapType, $offerId, $offerName, $capValue, $addedBy)
  453.     {
  455.         $entity = new BulkCap();
  456.         $entity->setAffiliateTagId($affiliateTagId);
  457.         $entity->setAffiliateTagName($affiliateTagName);
  458.         $entity->setAffiliateOfferCapType($affiliateOfferCapType);
  459.         $entity->setOfferId($offerId);
  460.         $entity->setOfferName($offerName);
  461.         $entity->setCapValue($capValue);
  462.         $entity->setDateInserted(new \DateTime('now'));
  463.         $entity->setAddedBy($addedBy);
  465.         $this->em->persist($entity);
  466.         $this->em->flush();
  467.     }
  469.     public function deleteFromBulkCapById($id)
  470.     {
  471.         $entity = $this->em->getRepository('App\Entity\BulkCap')->findOneBy(['id' => $id]);
  473.         if ($entity != null) {
  474.             $this->em->remove($entity);
  475.             $this->em->flush();
  476.         }
  477.     }
  479.     public function getOfferIdAffiliateTagIdFromBulkCap($offerId, $affiliateTagId, $capType)
  480.     {
  481.         return $this->em->getRepository('App\Entity\BulkCap')->findOneBy([
  482.             'offerId'               => $offerId,
  483.             'affiliateTagId'        => $affiliateTagId,
  484.             'affiliateOfferCapType' => $capType
  485.         ]);
  486.     }
  488.     public function getBulkCapData($hourRange = 4380)
  489.     {
  491.         $dataAfterDatetime = date('Y-m-d H:s:i', strtotime("-$hourRange hours"));
  492.         $repo              = $this->em->getRepository('App\Entity\BulkCap');
  494.         $entity = $repo
  495.             ->createQueryBuilder('a')
  496.             ->select('a.affiliateTagId', 'a.affiliateTagName', 'a.affiliateOfferCapType', 'a.offerId', 'a.offerName', 'a.dateInserted', 'a.id', 'a.capValue', 'a.addedBy')
  497.             ->where('a.dateInserted > :dateTime')
  498.             ->setParameters(['dateTime' => $dataAfterDatetime])
  499.             ->orderBy('a.id', 'DESC')
  500.             ->getQuery()->getResult();
  502.         return $entity;
  503.     }
  505.     public function getBulkCapByAffiliateData($hourRange = 48)
  506.     {
  508.         $dataAfterDatetime = date('Y-m-d H:s:i', strtotime("-$hourRange hours"));
  509.         $repo              = $this->em->getRepository('App\Entity\BulkCapByAffiliate');
  511.         $entity = $repo
  512.             ->createQueryBuilder('a')
  513.             ->select('a.affiliateId', 'a.affiliateName', 'a.affiliateOfferCapType', 'a.offerId', 'a.offerName', 'a.dateInserted', 'a.id', 'a.capValue', 'a.addedBy')
  514.             ->where('a.dateInserted > :dateTime')
  515.             ->setParameters(['dateTime' => $dataAfterDatetime])
  516.             ->orderBy('a.id', 'DESC')
  517.             ->getQuery()
  518.             ->getResult();
  520.         return $entity;
  521.     }
  523.     public function getCombinationFromAffiliateOfferCapping($affiliateId, $offerId, $capType, $tuneAccount)
  524.     {
  525.         return $this->em->getRepository('App\Entity\AffiliateOfferCapping')->findOneBy([
  526.             'offerId'     => $offerId,
  527.             'affiliateId' => $affiliateId,
  528.             'capType'     => $capType,
  529.             'tuneAccount' => $tuneAccount
  530.         ]);
  531.     }
  533.     public function deleteAffiliateOfferCapById($id)
  534.     {
  535.         $entity = $this->em->getRepository('App\Entity\AffiliateOfferCapping')->findOneBy(['id' => $id]);
  537.         if ($entity != null) {
  538.             $this->em->remove($entity);
  539.             $this->em->flush();
  540.         }
  541.     }
  543.     public function insertToAffiliateOfferCap($affiliateId, $offerId, $capType, $capValue, $tuneAccount)
  544.     {
  546.         $entity = new AffiliateOfferCapping();
  548.         $entity->setAffiliateId($affiliateId);
  549.         $entity->setOfferId($offerId);
  550.         $entity->setCapType($capType);
  551.         $entity->setCapValue($capValue);
  552.         $entity->setDateInserted(new \DateTime('now'));
  553.         $entity->setDateUpdated(new \DateTime('now'));
  554.         $entity->setTuneAccount($tuneAccount);
  556.         $this->em->persist($entity);
  557.         $this->em->flush();
  558.     }
  560.     public function insertToGoalDisableLinkAlertSent($goalId, $offerId, $affiliateId, $source)
  561.     {
  563.         $entity = new GoalDisableLinkAlertSent();
  565.         $entity->setGoalId($goalId);
  566.         $entity->setOfferId($offerId);
  567.         $entity->setAffiliateId($affiliateId);
  568.         $entity->setDateInserted(new \DateTime('now'));
  569.         $entity->setSource($source);
  571.         $this->em->persist($entity);
  572.         $this->em->flush();
  573.     }
  575.     public function checkGoalDisableLinkAlertSent($goalId, $offerId, $affiliateId, $source)
  576.     {
  578.         return $this->em->getRepository('App\Entity\GoalDisableLinkAlertSent')->findOneBy([
  579.             'offerId'     => $offerId,
  580.             'goalId'      => $goalId,
  581.             'affiliateId' => $affiliateId,
  582.             'source'      => $source
  583.         ]);
  584.     }
  586.     public function getRetentionOptimisationByParams($offerId, $goalId, $tuneAccount)
  587.     {
  588.         return $this->em->getRepository('App\Entity\RetentionOptimisation')->findOneBy([
  589.             'offerId' => $offerId,
  590.             'goalId'  => $goalId,
  591.             'tuneAccount' => $tuneAccount
  592.         ]);
  593.     }
  595.     public function updateRetentionOptimisation($offerId, $goalId, $retentionRate, $minimumBudget, $sendAlert, $autoBlock, $tuneAccount)
  596.     {
  597.         $entity = $this->em->getRepository('App\Entity\RetentionOptimisation')->findOneBy([
  598.             'offerId' => $offerId,
  599.             'goalId'  => $goalId,
  600.             'tuneAccount' => $tuneAccount
  601.         ]);
  603.         if ($entity != null) {
  605.             $entity->setRetentionRate($retentionRate);
  606.             $entity->setMinimumBudget($minimumBudget);
  607.             $entity->setSendAlert($sendAlert);
  608.             $entity->setDateUpdated(new \DateTime('now'));
  609.             $entity->setAutoBlock($autoBlock);
  610.             $entity->setTuneAccount($tuneAccount);
  612.             $this->em->persist($entity);
  613.             $this->em->flush();
  614.         }
  615.     }
  617.     public function insertRetentionOptimisation($offerId, $goalId, $retentionRate, $minimumBudget, $sendAlert, $autoBlock, $tuneAccount)
  618.     {
  619.         $entity = new RetentionOptimisation();
  621.         $entity->setOfferId($offerId);
  622.         $entity->setGoalId($goalId);
  623.         $entity->setRetentionRate($retentionRate);
  624.         $entity->setMinimumBudget($minimumBudget);
  625.         $entity->setSendAlert($sendAlert);
  626.         $entity->setAutoBlock($autoBlock);
  627.         $entity->setDateInserted(new \DateTime('now'));
  628.         $entity->setDateUpdated(new \DateTime('now'));
  629.         $entity->setTuneAccount($tuneAccount);
  631.         $this->em->persist($entity);
  632.         $this->em->flush();
  633.     }
  635.     public function getRetentionOptimisation($offerId, $tuneAccount = Config::MAFO_SYSTEM_IDENTIFIER_TUNE_MOBILE)
  636.     {
  638.         $repo = $this->em->getRepository('App\Entity\RetentionOptimisation');
  640.         $entity = $repo->createQueryBuilder('a')
  641.             ->select(
  642.                 'a.offerId',
  643.                 'a.goalId',
  644.                 'a.retentionRate',
  645.                 'a.minimumBudget',
  646.                 'a.sendAlert',
  647.                 'a.autoBlock',
  648.                 'a.dateUpdated as dateUpdated',
  649.                 'a.dateInserted as dateInserted',
  650.                 'a.id as id',
  651.                 'b.tuneAccount as tuneAccount',
  652.                 'c.company as advertiserName',
  653.                 'b.name as offerName',
  654.                 'b.advertiserId as advertiserId',
  655.                 'd.name as goalName'
  656.             )
  657.             ->innerJoin('App\Entity\OfferInfo', 'b', 'WITH', 'a.offerId = b.offerId AND a.tuneAccount = b.tuneAccount')
  658.             ->leftJoin('App\Entity\AdvertiserInfo', 'c', 'WITH', 'b.advertiserId = c.advertiserId AND b.tuneAccount = c.tuneAccount')
  659.             ->leftJoin('App\Entity\OfferGoalsInfo', 'd', 'WITH', 'a.goalId = d.goalId AND a.tuneAccount = d.tuneAccount')
  660.             ->where('a.offerId > 0');
  662.         if ($tuneAccount == Config::MAFO_SYSTEM_IDENTIFIER_TUNE_MOBILE) {
  663.             $entity->andWhere('a.tuneAccount = :tuneAccount');
  664.             $entity->setParameter('tuneAccount', $tuneAccount);
  665.         }
  667.         if ($offerId) {
  668.             $entity->andWhere('a.offerId = :offerId');
  669.             $entity->setParameter('offerId', $offerId);
  670.         }
  672.         return $entity
  673.             ->orderBy('dateUpdated', 'DESC')
  674.             ->getQuery()
  675.             ->getResult();
  676.     }
  678.     public function deleteRetentionOptimisation($id)
  679.     {
  680.         $entity = $this->em->getRepository('App\Entity\RetentionOptimisation')->findOneBy(['id' => $id]);
  682.         if ($entity != null) {
  683.             $this->em->remove($entity);
  684.             $this->em->flush();
  685.         }
  686.     }
  688.     public function checkScheduledDisableLinksByParam($source, $affiliateId, $offerId)
  689.     {
  691.         return $this->em->getRepository('App\Entity\ScheduledDisableLinks')->findOneBy([
  692.             'source'      => $source,
  693.             'affiliateId' => $affiliateId,
  694.             'offerId'     => $offerId
  695.         ]);
  696.     }
  698.     public function insertToScheduledDisableLinks($source, $affiliate, $offer)
  699.     {
  700.         $entity = new ScheduledDisableLinks();
  702.         $entity->setAffiliateId($affiliate);
  703.         $entity->setOfferId($offer);
  704.         $entity->setSource($source);
  705.         $entity->setIsScheduled(1);
  706.         $entity->setDateInserted(new \DateTime('now'));
  707.         $entity->setDateUpdated(new \DateTime('now'));
  709.         $this->em->persist($entity);
  710.         $this->em->flush();
  711.     }
  713.     public function getScheduledDisableLinkByIsScheduled($isScheduled)
  714.     {
  716.         $repo = $this->em->getRepository('App\Entity\ScheduledDisableLinks');
  718.         $entity = $repo->createQueryBuilder('a')->select('a.offerId', 'a.affiliateId', 'a.source', 'a.isScheduled', 'a.id')->where('a.isScheduled = :isScheduled')->setParameters(['isScheduled' => $isScheduled])->getQuery()->getResult();
  720.         return $entity;
  721.     }
  723.     public function setIsScheduledForScheduledOfferDisableLink($offerId, $affiliateId, $source, $isScheduled)
  724.     {
  726.         $entity = $this->em->getRepository('App\Entity\ScheduledDisableLinks')->findOneBy([
  727.             'offerId'     => $offerId,
  728.             'affiliateId' => $affiliateId,
  729.             'source'      => $source
  730.         ]);
  732.         if ($entity) {
  733.             $entity->setIsScheduled($isScheduled);
  734.             $this->em->persist($entity);
  735.             $this->em->flush();
  736.         }
  737.     }
  739.     public function setIsScheduledForScheduledOfferDisableLinkById($id, $isScheduled)
  740.     {
  742.         $entity = $this->em->getRepository('App\Entity\ScheduledDisableLinks')->findOneBy(['id' => $id]);
  744.         if ($entity) {
  745.             $entity->setIsScheduled($isScheduled);
  746.             $this->em->persist($entity);
  747.             $this->em->flush();
  748.         }
  749.     }
  751.     public function insertToBulkCapByAffiliate($affiliateId, $affiliateName, $affiliateOfferCapType, $offerId, $offerName, $capValue, $addedBy)
  752.     {
  754.         $entity = new BulkCapByAffiliate();
  756.         $entity->setAffiliateId($affiliateId);
  757.         $entity->setAffiliateName($affiliateName);
  758.         $entity->setAffiliateOfferCapType($affiliateOfferCapType);
  759.         $entity->setOfferId($offerId);
  760.         $entity->setOfferName($offerName);
  761.         $entity->setCapValue($capValue);
  762.         $entity->setAddedBy($addedBy);
  763.         $entity->setDateInserted(new \DateTime('now'));
  765.         $this->em->persist($entity);
  766.         $this->em->flush();
  767.     }
  769.     public function getBulkCapByAffiliate($offerId, $affiliateId, $capType)
  770.     {
  772.         return $this->em->getRepository('App\Entity\BulkCapByAffiliate')->findOneBy([
  773.             'offerId'               => $offerId,
  774.             'affiliateId'           => $affiliateId,
  775.             'affiliateOfferCapType' => $capType
  776.         ]);
  777.     }
  779.     public function deleteBulkCapByAffiliateById($id)
  780.     {
  782.         $entity = $this->em->getRepository('App\Entity\BulkCapByAffiliate')->findOneBy(['id' => $id]);
  784.         if ($entity) {
  785.             $this->em->remove($entity);
  786.             $this->em->flush();
  787.         }
  788.     }
  790.     public function insertToAdvertiserOfferCount($advertiserId, $offerCount, $status)
  791.     {
  793.         $entity = new AdvertiserOfferCount();
  795.         $entity->setAdvertiserId($advertiserId);
  796.         $entity->setOfferCount($offerCount);
  797.         $entity->setOfferStatus($status);
  798.         $entity->setDateInserted(new \DateTime('now'));
  800.         $this->em->persist($entity);
  801.         $this->em->flush();
  802.     }
  804.     public function getDataForAdvertiserOfferCountForChart($advertiserIdArr, $dateStart, $dateEnd, $chooseBy, $status)
  805.     {
  807.         $repo = $this->em->getRepository('App\Entity\AdvertiserOfferCount');
  809.         $entity = $repo->createQueryBuilder('a');
  811.         if ($chooseBy == Config::ADVERTISER_INFO_CHOOSE_BY_HOUR) {
  812.             $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');
  813.         } elseif ($chooseBy == Config::ADVERTISER_INFO_CHOOSE_BY_DAY) {
  814.             $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');
  815.         } elseif ($chooseBy == Config::ADVERTISER_INFO_CHOOSE_BY_WEEK) {
  816.             $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');
  817.         } elseif ($chooseBy == Config::ADVERTISER_INFO_CHOOSE_BY_MONTH) {
  818.             $entity->select('a.advertiserId', 'AVG(a.offerCount) as average', 'MONTH(a.dateInserted) as by_month', 'YEAR(a.dateInserted) as by_year');
  819.         }
  821.         $entity->where('a.advertiserId IN (:advertiserIds)')->andWhere('a.offerStatus = :status')->andWhere('a.dateInserted >= :dateStart')->andWhere('a.dateInserted <= :dateEnd')->setParameters([
  822.             'advertiserIds' => $advertiserIdArr,
  823.             'status'        => $status,
  824.             'dateStart'     => $dateStart,
  825.             'dateEnd'       => $dateEnd
  826.         ]);
  828.         $entity->groupBy('a.advertiserId');
  830.         if ($chooseBy == Config::ADVERTISER_INFO_CHOOSE_BY_HOUR) {
  831.             $entity->addGroupBy('by_hour', 'by_day', 'by_week', 'by_month', 'by_year');
  832.         } elseif ($chooseBy == Config::ADVERTISER_INFO_CHOOSE_BY_DAY) {
  833.             $entity->addGroupBy('by_day', 'by_week', 'by_month', 'by_year');
  834.         } elseif ($chooseBy == Config::ADVERTISER_INFO_CHOOSE_BY_WEEK) {
  835.             $entity->addGroupBy('by_week', 'by_month', 'by_year');
  836.         } elseif ($chooseBy == Config::ADVERTISER_INFO_CHOOSE_BY_MONTH) {
  837.             $entity->addGroupBy('by_month', 'by_year');
  838.         }
  840.         return $entity->getQuery()->getResult();
  841.     }
  843.     public function getDataForAffiliateOfferCountForChart($affiliateIdArr, $dateStart, $dateEnd, $chooseBy, $offerCountBy)
  844.     {
  846.         $repo = $this->em->getRepository('App\Entity\AffiliateOfferCount');
  848.         $entity = $repo->createQueryBuilder('a');
  849.         if ($offerCountBy == Config::AFFILIATE_INFO_CHOOSE_BY_APPROVED_ACTIVE_OFFER_COUNT) {
  850.             if ($chooseBy == Config::AFFILIATE_INFO_CHOOSE_BY_HOUR) {
  851.                 $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');
  852.             } elseif ($chooseBy == Config::AFFILIATE_INFO_CHOOSE_BY_DAY) {
  853.                 $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');
  854.             } elseif ($chooseBy == Config::AFFILIATE_INFO_CHOOSE_BY_WEEK) {
  855.                 $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');
  856.             } elseif ($chooseBy == Config::AFFILIATE_INFO_CHOOSE_BY_MONTH) {
  857.                 $entity->select('a.affiliateId', 'AVG(a.approvedActiveOfferCount) as average', 'MONTH(a.dateInserted) as by_month', 'YEAR(a.dateInserted) as by_year');
  858.             }
  859.         } elseif ($offerCountBy == Config::AFFILIATE_INFO_CHOOSE_BY_APPROVED_OFFER_COUNT) {
  860.             if ($chooseBy == Config::AFFILIATE_INFO_CHOOSE_BY_HOUR) {
  861.                 $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');
  862.             } elseif ($chooseBy == Config::AFFILIATE_INFO_CHOOSE_BY_DAY) {
  863.                 $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');
  864.             } elseif ($chooseBy == Config::AFFILIATE_INFO_CHOOSE_BY_WEEK) {
  865.                 $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');
  866.             } elseif ($chooseBy == Config::AFFILIATE_INFO_CHOOSE_BY_MONTH) {
  867.                 $entity->select('a.affiliateId', 'AVG(a.approvedOfferCount) as average', 'MONTH(a.dateInserted) as by_month', 'YEAR(a.dateInserted) as by_year');
  868.             }
  869.         }
  871.         $entity->where('a.affiliateId IN (:affiliateIds)') #->andWhere('a.offerStatus = :status')
  872.             ->andWhere('a.dateInserted >= :dateStart')->andWhere('a.dateInserted <= :dateEnd')->setParameters([
  873.                 'affiliateIds' => $affiliateIdArr,
  874.                 'dateStart'    => $dateStart,
  875.                 'dateEnd'      => $dateEnd
  876.             ]);
  878.         $entity->groupBy('a.affiliateId');
  880.         if ($chooseBy == Config::AFFILIATE_INFO_CHOOSE_BY_HOUR) {
  881.             $entity->addGroupBy('by_hour', 'by_day', 'by_week', 'by_month', 'by_year');
  882.         } elseif ($chooseBy == Config::AFFILIATE_INFO_CHOOSE_BY_DAY) {
  883.             $entity->addGroupBy('by_day', 'by_week', 'by_month', 'by_year');
  884.         } elseif ($chooseBy == Config::AFFILIATE_INFO_CHOOSE_BY_WEEK) {
  885.             $entity->addGroupBy('by_week', 'by_month', 'by_year');
  886.         } elseif ($chooseBy == Config::AFFILIATE_INFO_CHOOSE_BY_MONTH) {
  887.             $entity->addGroupBy('by_month', 'by_year');
  888.         }
  890.         return $entity->getQuery()->getResult();
  891.     }
  893.     public function getImpressionOptimisationByParams($offerId, $affiliateId)
  894.     {
  895.         return $this->em->getRepository('App\Entity\ImpressionOptimisation')->findOneBy([
  896.             'offerId'     => $offerId,
  897.             'affiliateId' => $affiliateId
  898.         ]);
  899.     }
  901.     public function deleteImpressionOptimisation($id, $deletedBy)
  902.     {
  903.         $entity = $this->em->getRepository('App\Entity\ImpressionOptimisation')->findOneBy(['id' => $id]);
  905.         if ($entity != null) {
  906.             $entity->setAddedBy($deletedBy);
  907.             $entity->setDateUpdated(new \DateTime('now'));
  908.             $entity->setIsDeleted(true);
  909.             $this->em->persist($entity);
  910.             $this->em->flush();
  911.         }
  913.         return $entity;
  914.     }
  916.     public function getActiveImpressionOptimisation()
  917.     {
  919.         $repo   = $this->em->getRepository('App\Entity\ImpressionOptimisation');
  920.         $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();
  922.         return $entity;
  923.     }
  925.     public function createUpdateImpression($offerId, $affiliateId, $ctr, $addedBy)
  926.     {
  927.         $entity = $this->getImpressionOptimisationByParams($offerId, $affiliateId);
  928.         if ($entity != null) {
  929.             $entity->setCtr($ctr);
  930.             $entity->setAddedBy($addedBy);
  931.             $entity->setDateUpdated(new \DateTime('now'));
  932.             $this->em->persist($entity);
  933.             $this->em->flush();
  934.         } else {
  935.             $entity = $this->insertImpressionOptimisation($offerId, $affiliateId, $ctr, $addedBy);
  936.         }
  938.         return $entity;
  939.     }
  941.     public function insertImpressionOptimisation($offerId, $affiliateId, $ctr, $addedBy)
  942.     {
  944.         $entity = new ImpressionOptimisation();
  945.         $entity->setAddedBy($addedBy);
  946.         $entity->setCtr($ctr);
  947.         $entity->setAffiliateId($affiliateId);
  948.         $entity->setOfferId($offerId);
  949.         $entity->setDateUpdated(new \DateTime('now'));
  950.         $entity->setDateInserted(new \DateTime('now'));
  951.         $entity->setIsDeleted(false);
  952.         $this->em->persist($entity);
  953.         $this->em->flush();
  955.         return $entity;
  956.     }
  957.     public function getFraudFlagLogs($affiliateArr, $offerArr, $advertiserArr, $startDate, $endDate)
  958.     {
  960.         $repository = $this->em->getRepository('App\Entity\FraudFlagLogs');
  962.         $result = $repository->createQueryBuilder('a')
  963.             ->select('a.affiliateId', 'a.offerId', 'a.source', 'a.advertiserId', 'a.dateInserted', 'a.advSub1', 'a.source', 'a.id', 'a.transactionId')
  964.             ->where('a.dateInserted >= :startDate')
  965.             ->andWhere('a.dateInserted <= :endDate')
  966.             ->andWhere('a.newStatus <= :newStatus')
  967.             ->setParameter('startDate', $startDate)
  968.             ->setParameter('endDate', $endDate)
  969.             ->setParameter('newStatus', Config::REJECTED_STATUS);
  971.         if (! empty($affiliateArr)) {
  972.             $result->andWhere('a.affiliateId IN (:affiliateId)');
  973.             $result->setParameter('affiliateId', $affiliateArr);
  974.         }
  976.         if (! empty($offerArr)) {
  977.             $result->andWhere('a.offerId IN (:offerId)');
  978.             $result->setParameter('offerId', $offerArr);
  979.         }
  982.         if (! empty($advertiserArr)) {
  983.             $result->andWhere('a.advertiserId IN (:advertiserId)');
  984.             $result->setParameter('advertiserId', $advertiserArr);
  985.         }
  987.         return $result->orderBy('a.id', 'DESC')->getQuery()->getResult();
  988.     }
  990.     public function deleteFraudFlagLogs($id)
  991.     {
  992.         $entity = $this->em->getRepository('App\Entity\FraudFlagLogs')->findOneBy(array('id' => $id));
  994.         if ($entity != null) {
  995.             $this->em->remove($entity);
  996.             $this->em->flush();
  997.         }
  998.     }
  999. }