Raphaël G. Git Repositories - airbundle/blob - Repository/LocationRepository.php

   1 <?php
   2
   3 namespace Rapsys\AirBundle\Repository;
   4
   5 use Symfony\Component\Translation\TranslatorInterface;
   6
   7 /**
   8  * LocationRepository
   9  */
  10 class LocationRepository extends \Doctrine\ORM\EntityRepository {
  11         /**
  12          * Find complementary locations by session id
  13          *
  14          * @param $id The session id
  15          * @return array The other locations
  16          */
  17         public function findComplementBySessionId($id) {
  18                 //Fetch complement locations
  19                 $ret = $this->getEntityManager()
  20                           ->createQuery('SELECT l.id, l.title FROM RapsysAirBundle:Session s LEFT JOIN RapsysAirBundle:Session s2 WITH s2.id != s.id AND s2.slot = s.slot AND s2.date = s.date LEFT JOIN RapsysAirBundle:Location l WITH l.id != s.location AND (l.id != s2.location OR s2.location IS NULL) WHERE s.id = :sid GROUP BY l.id ORDER BY l.id')
  21                         ->setParameter('sid', $id)
  22                         ->getArrayResult();
  23
  24                 //Rekey array
  25                 $ret = array_column($ret, 'id', 'title');
  26
  27                 return $ret;
  28         }
  29
  30         /**
  31          * Fetch translated location with session by date period
  32          *
  33          * @param $translator The TranslatorInterface instance
  34          * @param $period The date period
  35          * @param $granted The session is granted
  36          */
  37         public function fetchTranslatedLocationByDatePeriod(TranslatorInterface $translator, $period, $granted = false) {
  38                 //Fetch sessions
  39                 $ret = $this->getEntityManager()
  40                         ->createQuery('SELECT l.id, l.title FROM RapsysAirBundle:Session s JOIN RapsysAirBundle:Location l WHERE '.($granted?'s.application IS NOT NULL AND ':'').'l.id = s.location AND s.date BETWEEN :begin AND :end GROUP BY l.id ORDER BY l.id')
  41                         ->setParameter('begin', $period->getStartDate())
  42                         ->setParameter('end', $period->getEndDate())
  43                         ->getResult();
  44
  45                 //Rekey array
  46                 $ret = array_column($ret, 'title', 'id');
  47
  48                 //Filter array
  49                 foreach($ret as $k => $v) {
  50                         $ret[$k] = $translator->trans($v);
  51                 }
  52
  53                 //Send result
  54                 return $ret;
  55         }
  56
  57         /**
  58          * Fetch translated user location with session by date period
  59          *
  60          * @param $translator The TranslatorInterface instance
  61          * @param $period The date period
  62          * @param $userId The user uid
  63          */
  64         public function fetchTranslatedUserLocationByDatePeriod(TranslatorInterface $translator, $period, $userId) {
  65                 //Fetch sessions
  66                 $ret = $this->getEntityManager()
  67                           ->createQuery('SELECT l.id, l.title FROM RapsysAirBundle:Application a JOIN RapsysAirBundle:Session s JOIN RapsysAirBundle:Location l WHERE a.user = :uid AND a.session = s.id AND s.date BETWEEN :begin AND :end AND s.location = l.id GROUP BY l.id ORDER BY l.id')
  68 #SELECT l.id, l.title FROM RapsysAirBundle:Session s JOIN RapsysAirBundle:Application a JOIN RapsysAirBundle:Location l WHERE s.date BETWEEN :begin AND :end AND s.id = a.session AND l.id = s.location GROUP BY l.id ORDER BY l.id
  69                         ->setParameter('begin', $period->getStartDate())
  70                         ->setParameter('end', $period->getEndDate())
  71                         ->setParameter('uid', $userId)
  72                         ->getResult();
  73
  74                 //Rekey array
  75                 $ret = array_column($ret, 'title', 'id');
  76
  77                 //Filter array
  78                 foreach($ret as $k => $v) {
  79                         $ret[$k] = $translator->trans($v);
  80                 }
  81
  82                 //Send result
  83                 return $ret;
  84         }
  85 }