src/Repository/Management/SubscriptionRepository.php line 27

Open in your IDE?
  1. <?php
  3. namespace App\Repository\Management;
  5. use App\Entity\Management\Subscription\Formule;
  6. use App\Entity\Management\Subscription\Offer;
  7. use App\Entity\Management\Subscription\Subscription;
  8. use App\Entity\Users\Child;
  9. use App\Entity\Users\Guardiant;
  10. use App\Entity\Users\StudentsParents;
  11. use Bo\StudentBundle\Entity\OfferStudent;
  12. use Bo\StudentBundle\Entity\Student;
  14. use App\Entity\Users\Parents;
  15. use Bo\UserBundle\Entity\User;
  16. use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
  17. use Doctrine\ORM\EntityRepository;
  18. use Doctrine\ORM\NonUniqueResultException;
  19. use Doctrine\ORM\Query\ResultSetMapping;
  20. use Doctrine\Persistence\ManagerRegistry;
  22. /**
  23.  * StudentRepository
  24.  */
  25. class SubscriptionRepository extends ServiceEntityRepository
  26. {
  27.     public function __construct(ManagerRegistry $registry)
  28.     {
  29.         parent::__construct($registry, Subscription::class);
  30.     }
  33.     public function getLastSubscriptionsByParents(Parents $user)
  34.     {
  36.         $q = $this->_em
  37.             ->createQueryBuilder()
  38.             ->select('sp')
  39.             ->from(StudentsParents::class, 'sp')
  40.             ->join('sp.student','child')
  41.             ->where('sp.parents=:pid')
  42.             ->orderBy('child.firstname','asc')
  43.             ->setParameter('pid', $user->getId());
  44.         $list = $q->getQuery()->getResult();
  46.         $sub = [];
  47.         foreach ($list as $sp){
  48.             $sub [] = $this->createQueryBuilder('s')
  49.                 ->where('s.student =:sid and s.guardiant =:gid')
  50.                 ->setParameters(['sid'=>$sp->getStudent()->getId(),'gid'=>$user->getId()])
  51.                 ->orderBy('s.id','desc')
  52.                 ->setMaxResults(1)
  53.                 ->getQuery()->getOneOrNullResult();
  54.         }
  56.         return $sub;
  57.     }
  59.     public function getStudents()
  60.     {
  61.         $date = new \DateTime("now");
  63.         $q = $this->_em->createQueryBuilder()
  64.             ->select('os')
  65.             ->from('BoStudentBundle:OfferStudent', 'os')
  66.             ->andWhere("os.unSubscripted >= '" . $date->format('Y-m-d') . "' or os.unSubscripted is null");
  67.         return $q->getQuery()->getResult();
  68.     }
  70.     public function getAllCurrentSubscription(Parents $parents){
  71.         $now = new \DateTime();
  72.         return $this->createQueryBuilder('os')
  73.             ->select('count(os)')
  74.             ->andWhere('((os.subscripted <=:now and :now<= os.unSubscripted ) or os.unSubscripted is null ) and os.guardiant =:id')
  75.             ->setParameters([
  76.                 'now'=>$now->format('Y-m-d H:i:s'),
  77.                 'id'=>$parents->getId()])
  78.             ->getQuery()->getSingleScalarResult();
  79.     }
  81.     public function getListStudentToDoBilan()
  82.     {
  83.         $q = $this->_em->createQueryBuilder()
  84.             ->select('os')
  85.             ->from('BoStudentBundle:OfferStudent', 'os')
  86.             ->join('os.student', 's')
  87.             ->Where('s.isBilan = 1');
  88.         return $q->getQuery()->getResult();
  89.     }
  91.     public function registrationExamAlert()
  92.     {
  93.         $rsm = new ResultSetMapping();
  94.         $rsm->addEntityResult('Bo\StudentBundle\Entity\ReviewProgram', 'u');
  95.         $rsm->addFieldResult('u', 'id', 'id');
  96.         $rsm->addJoinedEntityResult('Bo\StudentBundle\Entity\Student', 's', 'u', 'student', array('id' => 'student_id'));
  97.         $rsm->addFieldResult('s', 'student_id', 'id');
  99.         $query = $this->_em->createNativeQuery('select s.id,s.student_id from (select r.day,r.student_id,r.`started`,r.id from aba_user a left join `ReviewProgram` r on r.`student_id`=a.id where a.`guardiant_id` is not null and r.`started` is not null and r.`started_playlist` is null and DATEDIFF(now(),r.started)=2 order by r.`started` desc) s group by s.student_id having min(s.`started`)', $rsm);
  101.         return $query->getResult();
  103.     }
  105.     public function registrationExamAlertNoPLaylist()
  106.     {
  107.         $rsm = new ResultSetMapping();
  108.         $rsm->addEntityResult('Bo\StudentBundle\Entity\ReviewProgram', 'u');
  109.         $rsm->addFieldResult('u', 'id', 'id');
  110.         $rsm->addJoinedEntityResult('Bo\StudentBundle\Entity\Student', 's', 'u', 'student', array('id' => 'student_id'));
  111.         $rsm->addFieldResult('s', 'student_id', 'id');
  113.         $query = $this->_em->createNativeQuery('select s.id , s.student_id from (select r.day,r.student_id,r.`created`,r.id from aba_user a left join `ReviewProgram` r on r.`student_id`=a.id where a.`guardiant_id` is not null  and DATEDIFF(now(),r.created)=5 order by r.`created` desc) s group by s.student_id having min(s.`created`)', $rsm);
  115.         return $query->getResult();
  117.     }
  119.     public function getPreviousSubscriptionByStudentId($studentId)
  120.     {
  121.         $stmt = $this
  122.             ->_em
  123.             ->getConnection()
  124.             ->prepare("
  125.                         SELECT *
  126.                         FROM lbp_prod.lnk_Student_Offer AS so
  127.                         WHERE so.student_id = :studentId
  128.                         ORDER BY so.id DESC LIMIT 1,1;
  129.                         ");
  131.         $stmt->execute(['studentId' => $studentId]);
  133.         $previousOs = $stmt->fetchAll(\PDO::FETCH_COLUMN);
  135.         if (empty($previousOs)) {
  136.             return [];
  137.         }
  139.         $q = $this->createQueryBuilder('os');
  141.         return $q
  142.             ->where('os.id = :previousOs')
  143.             ->setParameter('previousOs', $previousOs)
  144.             ->getQuery()
  145.             ->getOneOrNullResult();
  147.     }
  149.     public function getLastSubscriptionByParentChild(Parents $parent, Child $student)
  150.     {
  151.         $q = $this->_em->createQueryBuilder('os')
  152.             ->select('os')
  153.             ->from(Subscription::class, 'os')
  154.             ->where("os.student=:studentId and os.guardiant=:guardiantId and ((os.unSubscripted is not null and :now<=os.unSubscripted) or os.unSubscripted is null )")
  155.             ->setParameters([
  156.                 'guardiantId' => $parent->getId(),
  157.                 'studentId' => $student->getId(),
  158.                 'now'=>(new \DateTime('now'))->format('Y-m-d H:i:s')
  159.             ])
  160.             ->setMaxResults(1)
  161.             ->orderBy('os.id', 'desc');
  163.         return $q->getQuery()->getOneOrNullResult();
  164.     }
  166.     public function getLastSubscriptionByChild(Child $student)
  167.     {
  168.         $q = $this->_em->createQueryBuilder('os')
  169.             ->select('os')
  170.             ->from(Subscription::class, 'os')
  171.             ->where("os.student=:studentId and ((os.unSubscripted is not null and :now<=os.unSubscripted) or os.unSubscripted is null )")
  172.             ->setParameters([
  173.                 'now'=>(new \DateTime('now'))->format('Y-m-d H:i:s'),
  174.                 'studentId' => $student->getId()
  175.             ])
  176.             ->setMaxResults(1)
  177.             ->orderBy('os.id', 'desc');
  179.         return $q->getQuery()->getOneOrNullResult();
  180.     }
  182.     public function getLastSubscriptionByStudent(Child $user)
  183.     {
  185.         $q = $this->createQueryBuilder('os')
  186.             ->where("os.student=:studentId")
  187.             ->setParameters(['studentId' => $user->getId()])
  188.             ->setMaxResults(1)
  189.             ->orderBy('os.id', 'desc');
  191.         return $q->getQuery()->getOneOrNullResult();
  192.     }
  194.     /*
  195.     * Find students unsubcribed with role
  196.     */
  197.     public function findSubscriptionsInactifByParents(Parents $parents)
  198.     {
  199.         $q = $this->createQueryBuilder('os')
  200.             ->leftjoin('os.guardiant', 'g')
  201.             ->where('( os.unSubscripted is null or (os.unSubscripted is not null and os.unSubscripted >= CURRENT_TIMESTAMP()))')
  202.             ->andWhere('os.inactif = 1 and g.id=:id')
  203.             ->setParameters(['id'=>$parents->getId()])
  204.         ;
  205.         return $q->getQuery()->getResult();
  206.     }
  208.     public function listInactifNoUnsubcripted()
  209.     {
  211.         return $this->createQueryBuilder('os')
  212.             ->where("os.inactif=1 and os.unSubscripted is null and os.stripeId is not null")
  213.            ->getQuery()->getResult();
  214.     }
  216.     public function listSubOffer(Parents $guardiant)
  217.     {
  218.         return $this->createQueryBuilder('os')
  219.             ->select('count(os.id) as nb')
  220.             ->join('os.offer', 'o')
  221.             ->join(Formule::class, 'f', 'WITH', 'f.id = o.id')
  222.             ->where('os.guardiant=:guardiant')
  223.             ->setParameters([ 'guardiant'=>$guardiant->getId()])
  224.             ->getQuery()->getSingleScalarResult();
  226.     }
  229.     public function invoice()
  230.     {
  231.         $q = $this->_em
  232.             ->createQueryBuilder()
  233.             ->select('os')
  234.             ->from('BoStudentBundle:OfferStudent', 'os')
  235.             ->join('os.student', 'st')
  236.             ->where('os.unSubscripted is null or CURRENT_TIMESTAMP() < os.unSubscripted')
  237.             ->andWhere('os.offer INSTANCEOF FORMULE');
  238.         return $q->getQuery()->getResult();
  239.     }
  242.     /**
  243.      * return OfferStudent Objects where today = 15 days prior to credit card month expiration
  244.      *
  245.      * @return array
  246.      * @throws \Doctrine\DBAL\DBALException
  247.      */
  248.     public function getSoonExpiredCard()
  249.     {
  250.         $stmt = $this
  251.             ->_em
  252.             ->getConnection()
  253.             ->prepare('
  254.                 SELECT so.id, date_add(so.card_expiration_date,interval -DAY(so.card_expiration_date)+1 DAY) AS first_day
  255.                 FROM lnk_Student_Offer AS so
  256.                 WHERE so.card_expiration_date IS NOT NULL
  257.                 GROUP BY so.guardiant_id
  258.                 HAVING CURRENT_DATE() = DATE_SUB(first_day, INTERVAL 15 DAY);
  259.             ');
  261.         $stmt->execute();
  262.         $studentOffers = $stmt->fetchAll(\PDO::FETCH_ASSOC);
  264.         $ids = [];
  265.         foreach ($studentOffers as $row) {
  266.             $ids[] = $row['id'];
  267.         }
  269.         if (empty($ids)) {
  270.             return [];
  271.         }
  273.         $q = $this->createQueryBuilder('os');
  275.         return $q
  276.             ->where($q->expr()->in('os.id', $ids))
  277.             ->getQuery()
  278.             ->getResult();
  279.     }
  281.     /**
  282.      * return OfferStudent Objects where today = 16th day of credit card current month expiration
  283.      *
  284.      * @return array
  285.      * @throws \Doctrine\DBAL\DBALException
  286.      */
  287.     public function getExpiredCardFirstReminder()
  288.     {
  289.         $stmt = $this
  290.             ->_em
  291.             ->getConnection()
  292.             ->prepare("
  293.               SELECT so.id, CONCAT(YEAR(so.card_expiration_date), '-', MONTH(so.card_expiration_date), '-16') AS given_date
  294.               FROM lnk_Student_Offer AS so
  295.               WHERE so.card_expiration_date IS NOT NULL
  296.               GROUP BY so.guardiant_id
  297.               HAVING CURRENT_DATE() = given_date;
  298.               ");
  300.         $stmt->execute();
  301.         $studentOffers = $stmt->fetchAll(\PDO::FETCH_ASSOC);
  303.         $ids = [];
  304.         foreach ($studentOffers as $row) {
  305.             $ids[] = $row['id'];
  306.         }
  308.         if (empty($ids)) {
  309.             return [];
  310.         }
  312.         $q = $this->createQueryBuilder('os');
  314.         return $q
  315.             ->where($q->expr()->in('os.id', $ids))
  316.             ->getQuery()
  317.             ->getResult();
  318.     }
  320.     /**
  321.      * return OfferStudent Objects where today = 25th day of credit card current month expiration
  322.      *
  323.      * @return array
  324.      * @throws \Doctrine\DBAL\DBALException
  325.      */
  326.     public function getExpiredCardLastReminder()
  327.     {
  328.         $stmt = $this
  329.             ->_em
  330.             ->getConnection()
  331.             ->prepare("
  332.               SELECT so.id, CONCAT(YEAR(so.card_expiration_date), '-', MONTH(so.card_expiration_date), '-25') AS given_date
  333.               FROM lnk_Student_Offer AS so
  334.               WHERE so.card_expiration_date IS NOT NULL
  335.               GROUP BY so.guardiant_id
  336.               HAVING CURRENT_DATE() = given_date;
  337.               ");
  339.         $stmt->execute();
  340.         $studentOffers = $stmt->fetchAll(\PDO::FETCH_ASSOC);
  342.         $ids = [];
  343.         foreach ($studentOffers as $row) {
  344.             $ids[] = $row['id'];
  345.         }
  347.         if (empty($ids)) {
  348.             return [];
  349.         }
  351.         $q = $this->createQueryBuilder('os');
  353.         return $q
  354.             ->where($q->expr()->in('os.id', $ids))
  355.             ->getQuery()
  356.             ->getResult();
  357.     }
  359.     /**
  360.      * return OfferStudent Objects where today = credit card expiration date
  361.      *
  362.      * @return array
  363.      * @throws \Doctrine\DBAL\DBALException
  364.      */
  365.     public function getExpiredCardAlert()
  366.     {
  367.         return $this
  368.             ->createQueryBuilder('os')
  369.             ->where('os.cardExpirationDate = CURRENT_DATE()')
  370.             ->groupBy('os.guardiant')
  371.             ->getQuery()
  372.             ->getResult();
  373.     }
  375.     public function getBankCollectionByUser($user, Student $student = null)
  376.     {
  377.         $q = $this
  378.             ->createQueryBuilder('os');
  380.         if ($user instanceof Parents) {
  381.             $q = $q->where('os.guardiant =:user')->setParameter('user', $user->getId());
  382.         } elseif ($user instanceof Student) {
  383.             $q = $q->where('os.student =:user')->setParameter('user', $user->getId());
  384.         }
  386.         if (!is_null($student)) {
  387.             $q = $q->andwhere('os.student =:student')->setParameter('student', $student->getId());
  388.         }
  390.         $q = $q->andWhere('os.isBankCollectionProcess = 1')->orderBy('os.id', 'desc');
  391.         return $q->getQuery()->getResult();
  392.     }
  394.     public function isSuspendedSubscription($user)
  395.     {
  397.         $q = $this->_em
  398.             ->createQueryBuilder()
  399.             ->select('os')
  400.             ->from('BoStudentBundle:OfferStudent', 'os')
  401.             ->where('os.unSubscripted is null')
  402.             ->andWhere('os.isSuspended = 1')
  403.             ->andWhere('os.guardiant =:user')
  404.             ->setParameter('user', $user->getId());
  405.         return $q->getQuery()->getResult();
  407.     }
  410.     public function getSuspendedSubscription()
  411.     {
  413.         $q = $this->_em
  414.             ->createQueryBuilder()
  415.             ->select('os')
  416.             ->from('BoStudentBundle:OfferStudent', 'os')
  417.             ->where('os.unSubscripted is null')
  418.             ->andWhere('os.isSuspended = 1')
  419.             ->andWhere('os.dateSuspended is not null');
  420.         return $q->getQuery()->getResult();
  422.     }
  424.     public function getCurrentSubscriptionByChild(Child $child){
  425.         $now = new \DateTime();
  426.         $parameters = ['now' => $now->format('Y-m-d H:i:s'),'student'=>$child->getId()];
  428.         $q = $this->createQueryBuilder('s')
  429.             ->select('s')
  430.             ->join('s.offer', 'offers')
  431.             ->where('
  432.                 (os.unSubscripted is null AND os.subscripted <= :now ) OR 
  433.                 (os.unSubscripted is not null AND os.subscripted <= :now AND :now <= os.unSubscripted) OR
  434.                 (:now < os.trialDate)')
  435.             ->andWhere('offers INSTANCE OF BoTrainningBundle:Formule')
  436.             ->andWhere('s.student =:student')
  437.             ->setParameters($parameters);
  438.         return $q->getQuery()->getResult();
  439.     }
  441.     public function findOfferFreeByChild(?Child $child){
  442.         if(is_null($child))
  443.             return null;
  445.         $parameters = ['student'=>$child->getId(),'offer'=>Offer::OFFER_FREE];
  446.         $q = $this->createQueryBuilder('s')
  447.             ->select('s')
  448.             ->join('s.offer', 'offers')
  449.             ->Where('s.student =:student and offers.slug=:offer')
  450.             ->orderBy('s.id','desc')
  451.             ->setMaxResults(1)
  452.             ->setParameters($parameters);
  453.         return $q->getQuery()->getOneOrNullResult();
  454.     }
  459.     public function getCurrentSubscriptionByUser($user, $student = null, $guardiantIsNull = true, $returnAll = false)
  460.     {
  461.         $now = new \DateTime('now');
  463.         $q = $this->_em
  464.             ->createQueryBuilder('os')
  465.             ->select('os')
  466.             ->from('BoStudentBundle:OfferStudent', 'os')
  467.             ->join('os.offer', 'offers')
  468.             ->where('
  469.                 (os.unSubscripted is null AND os.subscripted <= :now ) OR 
  470.                 (os.unSubscripted is not null AND os.subscripted <= :now AND :now <= os.unSubscripted) OR
  471.                 (:now < os.trialDate)')
  472.             ->andWhere('offers INSTANCE OF BoTrainningBundle:Formule');
  474.         $parameters = ['now' => $now->format('Y-m-d H:i:s')];
  476.         if ($user instanceof Parents) {
  477.             $q->andWhere('os.guardiant =:guardiant');
  478.             $parameters['guardiant'] = $user->getId();
  480.             if($student !== null) {
  481.                 $q->andWhere('os.student=:student');
  482.                 $parameters['student'] = $student->getId();
  484.                 if($returnAll === true) {
  485.                     $q->groupBy('os.student');
  486.                 }
  487.             } else {
  488.                 // NOTE : to avoid exception of getOneOrNullResult() for multiple results
  489.                 $q->setParameters($parameters);
  490.                 if($returnAll === true) {
  491.                     $q->groupBy('os.student');
  492.                 }
  493.                 $studentOffers = $q->getQuery()->getResult();
  495.                 if(empty($studentOffers) === true) {
  496.                     return null;
  497.                 } else {
  498.                     if($returnAll === true) {
  499.                         return $studentOffers;
  500.                     }
  501.                     return current($studentOffers);
  502.                 }
  503.             }
  504.         } else {
  506.             if($guardiantIsNull === true) {
  507.                 $q->andWhere('os.guardiant is null');
  508.             }
  510.             $q->andWhere('os.student=:student');
  511.             $parameters['student'] = $user->getId();
  513.             if($returnAll === true) {
  514.                 $q->groupBy('os.student');
  515.             }
  516.         }
  518.         $q->setParameters($parameters);
  519.         $result = $q->getQuery()->getResult();
  521.         // NOTE : it seems that getOneOrNullResult() generate an error
  522.         if(empty($result)) {
  523.             return null;
  524.         } else {
  525.             if($returnAll === true) {
  526.                 return $result;
  527.             }
  528.             return current($result);
  529.         }
  530.     }
  532.     public function getCurrentSubscriptionsByParent(Parents $parents)
  533.     {
  535.         $now = new \DateTime('now');
  536.         $q = $this->_em
  537.             ->createQueryBuilder('os')
  538.             ->select('os')
  539.             ->from(Subscription::class, 'os')
  540.             ->join('os.student','s')
  541.             ->where('os.unSubscripted is null or :now <= os.unSubscripted')
  542.             ->andWhere('os.guardiant =:guardiant')
  543.             ->orderBy('s.firstname','asc')
  544.             ->setParameters([
  545.                 'guardiant' => $parents->getId(),
  546.                 'now'       => $now->format('Y-m-d H:i:s')
  547.             ]);
  549.         return $q->getQuery()->getResult();
  550.     }
  553.     public function getChangeOfferSubscriptionsByParentId(Parents $parents)
  554.     {
  556.         $now = new \DateTime('now');
  557.         $q = $this->_em
  558.             ->createQueryBuilder('os')
  559.             ->select('os')
  560.             ->from('BoStudentBundle:OfferStudent', 'os')
  561.             ->join('os.offer', 'offers')
  562.             ->where('offers INSTANCE OF BoTrainningBundle:Formule')
  563.             ->andWhere('os.guardiant =:guardiant')
  564.             ->andWhere('offers.stripeId != :newlbp')
  565.             ->setParameters([
  566.                 'guardiant' => $parents->getId(),
  567.                 'newlbp'=> 'new-lbp',
  568.                 //'now' => $now->format('Y-m-d H:i:s')
  569.             ]);
  571.         return $q->getQuery()->getResult();
  572.     }
  574.     public function getCurrentSubscriptionsByUserId(Child $student)
  575.     {
  577.         $now = new \DateTime('now');
  578.         $q = $this->_em
  579.             ->createQueryBuilder('os')
  580.             ->select('os')
  581.             ->from(Subscription::class, 'os')
  582.             ->join('os.offer', 'offers')
  583.             ->where('os.unSubscripted is null or :now <= os.unSubscripted')
  584.             ->andWhere('os.student =:guardiant')
  585.             ->setParameters(['guardiant' => $student->getId(), 'now' => $now->format('Y-m-d H:i:s')])
  586.             ->setMaxResults(1);
  588.         try {
  589.             return $q->getQuery()->getOneOrNullResult();
  590.         } catch (NonUniqueResultException $e) {
  591.             return null;
  592.         }
  593.     }
  595.     public function findAllCurrent()
  596.     {
  598.         $q = $this->_em
  599.             ->createQueryBuilder('os')
  600.             ->select('os')
  601.             ->from('BoStudentBundle:OfferStudent', 'os')
  602.             ->where('os.unSubscripted is null')
  603.             ->andWhere('os.stripeId is not null')
  604.             ->andWhere('os.offer = 40');
  605.         return $q->getQuery()->getResult();
  606.     }
  608.     public function updateCardExpiration()
  609.     {
  610.         $query = $this->_em
  611.             ->createQueryBuilder('os')
  612.             ->select('os')
  613.             ->from('BoStudentBundle:OfferStudent', 'os')
  614.             ->join('os.guardiant', 'guardiant')
  615.             ->where('os.unSubscripted is null')
  616.             ->andWhere('os.stripeId is not null')
  617.             ->andWhere('os.offer = 40')
  618.             ->andWhere('os.guardiant = guardiant.id')
  619.             ->orderBy('os.id', 'desc')
  620.         ;
  622.         $stripeIds = [];
  624.         $query = $query->getQuery()->getResult();
  625.         foreach ($query as $q ){
  627.                 $stripeIds[] = [
  628.                     'id' => $q->getGuardiant()->getId(),
  629.                     'stripeId' =>  $q->getGuardiant()->getStripeId(),
  630.                     'dateCardExpiration' => $q->getCardExpirationDate()
  631.                 ];
  632.         }
  633.         return $stripeIds;
  635.     }
  638.     public function sendWarningCardExpiration($dateCardExpiration)
  639.     {
  640.         $query = $this->_em
  641.             ->createQueryBuilder('os')
  642.             ->select('os')
  643.             ->from('BoStudentBundle:OfferStudent', 'os')
  644.             ->join('os.guardiant', 'guardiant')
  645.             ->where('os.unSubscripted is null')
  646.             ->andWhere('os.cardExpirationDate = :dateCardExpiration')
  647.             ->andWhere('os.stripeId is not null')
  648.             ->andWhere('os.offer = 40')
  649.             ->andWhere('os.guardiant = guardiant.id')
  650.             ->setParameter('dateCardExpiration', $dateCardExpiration)
  651.             ->orderBy('os.id', 'desc')
  652.         ;
  654.         return $query->getQuery()->getResult();
  656.     }
  659.     public function SubscritptionAndInvoicesByGuardiant($guardiantId)
  660.     {
  661.         $q = $this->_em
  662.             ->createQueryBuilder('os')
  663.             ->select('os', 'inv')
  664.             ->from('BoStudentBundle:OfferStudent', 'os')
  665.             ->join('os.invoices', 'inv')
  666.             ->where('os.guardiant =:guardiant')
  667.             ->andWhere('inv INSTANCE OF BoSubscriptionBundle:Invoice')
  668.             ->setParameter('guardiant', $guardiantId);
  669.         return $q->getQuery()->getResult();
  670.     }
  672.     public function allSubscription(User $user)
  673.     {
  674.         $q = $this->_em
  675.             ->createQueryBuilder('max(os.id)')
  676.             ->select('max(os.id)')
  677.             ->from('BoStudentBundle:OfferStudent', 'os')
  678.             ->join('os.offer', 'offers')
  679.             ->where('os.guardiant =:guardiant')
  680.             ->andWhere('offers INSTANCE OF BoTrainningBundle:Formule')
  681.             ->groupBy('os.guardiant,os.student')
  682.             ->setParameter('guardiant', $user->getId());
  685.         $q = $q->getQuery()->getArrayResult();
  687.         foreach ($q as $it) {
  688.             $ids[] = $it[1];
  689.         }
  691.         if (empty($ids)) {
  692.             return [];
  693.         }
  694.         $q2 = $this->_em
  695.             ->createQueryBuilder('os');
  697.         return $this->_em
  698.             ->createQueryBuilder('os')
  699.             ->select('os')
  700.             ->from('BoStudentBundle:OfferStudent', 'os')
  701.             ->where($q2->expr()->in('os.id', $ids))
  702.             ->orderBy('os.unSubscripted', 'asc')
  703.             ->getQuery()->getResult();
  704.     }
  706.     public function justOnceTrial(User $user, OfferStudent $offerStudent = null)
  707.     {
  708.         if (is_null($user) || is_null($offerStudent))
  709.             return null;
  711.         $q = $this->_em
  712.             ->createQueryBuilder('os')
  713.             ->select('os')
  714.             ->from('BoStudentBundle:OfferStudent', 'os')
  715.             ->join('os.offer', 'offers')
  716.             ->where('os.guardiant =:guardiant')
  717.             ->andWhere('offers INSTANCE OF BoTrainningBundle:Formule')
  718.             ->andWhere('os.id <> :current')
  719.             ->andWhere('offers.id=:offer_id')
  720.             ->setParameters(['guardiant' => $user->getId(), 'current' => $offerStudent->getId(), 'offer_id' => Offer::ID_OFFER_NEW_OFFER]);
  721.         $q = $q->getQuery()->getResult();
  722.         return count($q) == 0 ? null : true;
  723.     }
  725.     public function checkIfCurrentTrialSubscription($parentId)
  726.     {
  727.         if (is_null($parentId))
  728.             return null;
  730.         $q = $this->_em
  731.             ->createQueryBuilder('os')
  732.             ->select('os')
  733.             ->from('BoStudentBundle:OfferStudent', 'os')
  734.             ->join('os.offer', 'offers')
  735.             ->where('os.guardiant =:guardiant')
  736.             ->andWhere('offers INSTANCE OF BoTrainningBundle:Formule')
  737.             ->andWhere('os.unSubscripted is null')
  738.             ->andWhere('os.trialDate is not null')
  739.             ->andWhere('offers.id=:offer_id')
  740.             ->andwhere(' CURRENT_DATE() < os.subscripted')
  741.             ->orderBy('os.id', 'desc')
  742.             ->setParameters([
  743.                 'guardiant' => $parentId,
  744.                 'offer_id' => Offer::ID_OFFER_NEW_OFFER
  745.             ]);
  746.         $q = $q->getQuery()->getResult();
  747.         return $q ? $q[0] : false;
  748.     }
  750.     public function getSponsoredPaid(User $user)
  751.     {
  753.         $q = $this->_em
  754.             ->createQueryBuilder('os')
  755.             ->select('os')
  756.             ->from('BoStudentBundle:OfferStudent', 'os')
  757.             ->where('os.sponsor =:guardiant')
  758.             ->andWhere('os.unSubscripted is null')
  759.             ->andWhere('os.trialDate is null or os.trialDate < CURRENT_DATE()')
  760.             ->setParameters(['guardiant' => $user->getId()]);
  761.         return $q->getQuery()->getResult();
  762.     }
  764.     public function sendEmailAlertSubscription()
  765.     {
  766.         $stmt = $this
  767.             ->_em
  768.             ->getConnection()
  769.             ->prepare('select DATEDIFF(subscripted, now())=-1 as "s+1",DATEDIFF(subscripted, now())=-3 as "s+3",DATEDIFF(l.created, now())=-2 as "pe+2",DATEDIFF(l.created, now())=-7 as "pe+7",l.id from lnk_Student_Offer l  where offer_id = 40 and `un_subscripted`is null and ( DATEDIFF(subscripted, l.created)=7 or DATEDIFF(subscripted, l.created) = 8 ) and
  770. (DATEDIFF(subscripted, now())=-1 or DATEDIFF(subscripted, now())=-3 or DATEDIFF(l.created, now())=-2 or DATEDIFF(l.created, now())=-7)');
  772.         $stmt->execute();
  773.         return $stmt->fetchAll(\PDO::FETCH_ASSOC);
  774.     }
  777.     public function sendEmailAlertLastTrial()
  778.     {
  779.         $dateNow = new \DateTime();
  780.         $dateNow->modify('+ 1 day');
  782.         $q = $this->_em->createQueryBuilder()
  783.             ->select('os')
  784.             ->from('BoStudentBundle:OfferStudent', 'os')
  785.             ->where("os.trialDate BETWEEN :date1 AND :date2 ")
  786.             ->andWhere("os.unSubscripted is null")
  787.             ->setParameter('date1', $dateNow->format('Y-m-d 00:00:00'))
  788.             ->setParameter('date2', $dateNow->format('Y-m-d 23:59:59'))
  790.         ;
  792.         return $q->getQuery()->getResult();
  793.     }
  795.     public function buttonSubscription(User $user, $mode)
  796.     {
  798.         $q = $this->_em
  799.             ->createQueryBuilder('os')
  800.             ->select('os')
  801.             ->from('BoStudentBundle:OfferStudent', 'os')
  802.             ->join('os.offer', 'offers')
  803.             ->where('offers INSTANCE OF BoTrainningBundle:Formule');
  804.         if ($user INSTANCEOF Parents) {
  805.             $q = $q->andWhere('os.guardiant=:guardiant')->setParameter('guardiant', $user->getId());
  807.         } else {
  808.             $q = $q->andWhere('os.student=:student')->setParameter('student', $user->getId());
  809.         }
  811.         if ($mode) {
  812.             switch ($mode) {
  813.                 case 1:
  814.                     $q = $q->andWhere('offers.slug=:offer_slug')->setParameter('offer_slug', 'abonnement-assistance');
  815.                     break;
  816.                 case 2:
  817.                     $q = $q->andWhere('os.unSubscripted is null or os.unSubscripted > CURRENT_TIMESTAMP()');
  818.                     break;
  819.             }
  820.         }
  821.         $q = $q->orderBy('os.id', 'desc');
  823.         return $q->getQuery()->getResult();
  824.     }
  826.     public function hadOnePackExam(Parents $parents)
  827.     {
  829.         $q = $this->_em->createQueryBuilder('os')
  830.             ->select('os')
  831.             ->from('BoStudentBundle:OfferStudent', 'os')
  832.             ->join('os.offer', 'offer')
  833.             ->where('offer.slug =:slug ')
  834.             ->andwhere("os.guardiant=:guardiantId")
  835.             ->andwhere('os.unSubscripted is null or CURRENT_DATE() <= os.unSubscripted')
  836.             ->setParameters([
  837.                 'guardiantId' => $parents->getId(),
  838.                 'slug' => 'packexam-2018'
  839.             ])
  840.             ->orderBy('os.id', 'desc');
  842.         return $q->getQuery()->getResult();
  843.     }
  845.     /**
  846.      * Reactive offer student
  847.      *
  848.      * @param OfferStudent $offerStudent
  849.      * @return int
  850.      */
  851.     public function reactivate(OfferStudent $offerStudent)
  852.     {
  853.         $queryBuilder = $this->_em->createQueryBuilder('os');
  854.         $queryBuilder->update('BoStudentBundle:OfferStudent','os')
  855.             ->set('os.unSubscripted', ':unSubscripted')
  856.             ->setParameter('unSubscripted', null)
  857.             ->where('os.id = :offerStudentId')
  858.             ->andWhere('os.unSubscripted IS NOT NULL')
  859.             ->setParameter('offerStudentId', $offerStudent->getId());
  861.         return $queryBuilder
  862.             ->getQuery()
  863.             ->execute();
  864.     }
  866.     public function getSubscriberFormule($formule)
  867.     {
  868.         $q = $this->_em->createQueryBuilder()
  869.             ->select('os')
  870.             ->from('BoStudentBundle:OfferStudent', 'os')
  871.             ->join('os.offer', 'offer')
  872.             ->where('offer.slug = :slug')
  873.             ->setParameter('slug', $formule )
  874.             ;
  875.         return $q->getQuery()->getResult();
  877.     }
  879.     /**
  880.      * Return offerStudent statement by course/serie
  881.      *
  882.      * @param $courseId
  883.      * @param $serieId
  884.      * @param \DateTime $startDate
  885.      * @param array $studentIds
  886.      * @return \Doctrine\DBAL\Driver\Statement
  887.      * @throws \Doctrine\DBAL\DBALException
  888.      */
  889.     public function getStatementAllByCourseAndSerie($courseId, $serieId, \DateTime $startDate,\DateTime $endDate, $studentIds = [])
  890.     {
  891.         if(empty($studentIds) === false) {
  892.             $queryComplement = "AND os.student_id IN (".implode(", ", $studentIds).")";
  893.         } else {
  894.             $queryComplement = "";
  895.         }
  897.         $stmt = $this
  898.             ->_em
  899.             ->getConnection()
  900.             ->prepare(
  901.                 "
  902.                         SELECT os.*, s.firstname AS student_firstname, g.firstname AS parent_firstname, g.email AS parent_email, s.serie AS student_serie
  903.                         FROM ".$this->_em->getClassMetadata('BoStudentBundle:OfferStudent')->getTableName()." AS os
  904.                         INNER JOIN ".$this->_em->getClassMetadata('BoStudentBundle:Student')->getTableName()." AS s ON s.id = os.student_id
  905.                         INNER JOIN ".$this->_em->getClassMetadata('BoUserBundle:Parents')->getTableName()." AS g ON g.id = os.guardiant_id
  906.                         WHERE  os.stripe_id is not null
  907.                         AND (os.un_subscripted is null or '".$endDate->format('Y-m-d')."' < os.un_subscripted)
  908.                         AND os.offer_id = 40
  909.                         AND os.created  < '".$endDate->format('Y-m-d')."'
  910.                         ".$queryComplement."
  911.                         ;"
  912.             );
  914.         $stmt->execute();
  915.         return $stmt;
  916.     }
  918.     /**
  919.      * Reactive offer student
  920.      *
  921.      * @param OfferStudent $offerStudent
  922.      */
  923.     public function getExistOfferStudent( $parentID,  $studentID)
  924.     {
  925.         $q = $this->_em->createQueryBuilder('os')
  926.             ->select('os')
  927.             ->from('BoStudentBundle:OfferStudent', 'os')
  928.             ->join('os.guardiant', 'parent')
  929.             ->join('os.student', 'student')
  930.             ->where('os.unSubscripted is null')
  931.             ->andWhere('os.guardiant = :parentID')
  932.             ->andWhere('os.student = :studentID')
  933.             ->setParameter('parentID', $parentID )
  934.             ->setParameter('studentID', $studentID)
  935.             ->orderBy('os.id', 'DESC')
  936.         ;
  938.         try {
  939.            return $q->getQuery()->getSingleResult();
  940.         } catch (\Exception $e) {
  941.            return false;
  942.         }
  943.     }
  946.     /**
  947.      * Active Offer Student
  948.      *
  949.      * @param OfferStudent $offerStudent
  950.      */
  951.     public function getAllSubscriptionActive()
  952.     {
  953.         $q = $this->_em
  954.             ->createQueryBuilder('os')
  955.             ->select('os')
  956.             ->from('BoStudentBundle:OfferStudent', 'os')
  957.             ->join('os.guardiant', 'guardiant')
  958.             ->join('os.discount', 'discount')
  960.             ->where('os.unSubscripted is null')
  961.             ->andWhere('discount.stripeId != :condition1 ')
  962.             ->andWhere('discount.stripeId != :condition2 ')
  963.             ->andWhere('CURRENT_DATE() > discount.endValid  ')
  964.             ->setParameter('condition1', 'lbp-rf-2')
  965.             ->setParameter('condition2', 'lbp-rf-3')
  966.             ->getQuery()->getResult();
  969.         return $q;
  970.     }
  974.     /**
  975.      * Reactive offer student
  976.      *
  977.      * @param OfferStudent $offerStudent
  978.      */
  979.     public function getSuspendUser()
  980.     {
  981.         $date = new \DateTime("now");
  983.         $q = $this->_em->createQueryBuilder('os')
  984.             ->select('os')
  985.             ->from('BoStudentBundle:OfferStudent', 'os')
  986.             ->where('os.unSubscripted is null')
  987.             ->andWhere('os.isSuspended = 1')
  988.             ->andWhere('os.nextInvoiceSuspended <= :datePast')
  989.             ->setParameter('datePast', $date->modify('2019-10-01') )
  990.             ->orderBy('os.id', 'DESC')
  991.         ;
  993.         return $q->getQuery()->getResult();
  994.     }
  996.     /**
  997.      * Reactive offer student
  998.      *
  999.      * @param OfferStudent $offerStudent
  1000.      */
  1001.     public function getOfferWithEngagementSoonEnd($jday)
  1002.     {
  1003.         $date = new \DateTime("now");
  1005.         $dayDiff = $date->modify("+$jday days");
  1007.         $q = $this->_em->createQueryBuilder('os')
  1008.             ->select('os')
  1009.             ->from('BoStudentBundle:OfferStudent', 'os')
  1010.             ->join('os.offer', 'offer')
  1011.             ->where('os.unSubscripted is not null')
  1012.             ->andWhere("offer.slug IN ( 'offre-3-mois', 'offre-6-mois-1', 'offre-6-mois', 'offre-12-mois', 'offre-9-mois' )")
  1013.             ->andWhere('os.isStopTrial = 0')
  1014.             ->andWhere('os.renew = 0')
  1015.             ->andWhere('os.unSubscripted  BETWEEN :day1  AND :day2')
  1016.             ->setParameter('day1', $dayDiff->format("Y-m-d 00:00:00") )
  1017.             ->setParameter('day2', $dayDiff->format("Y-m-d 23:59:59") )
  1018.             ->orderBy('os.id', 'DESC')
  1019.         ;
  1021.         return $q->getQuery()->getResult();
  1022.     }
  1024. }