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          * Find translated location title sorted 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 findTranslatedSortedByPeriod(TranslatorInterface $translator, $period, $userId = null) {
  38                 //Fetch sessions
  39                 $ret = $this->getEntityManager()
  40                         ->createQuery(
  41 'SELECT l.id, l.title
  42 FROM RapsysAirBundle:Location l
  43 LEFT JOIN RapsysAirBundle:Session s WITH s.location = l.id AND s.date BETWEEN :begin AND :end
  44 LEFT JOIN RapsysAirBundle:Application a WITH a.id = s.application'.(!empty($userId)?' AND a.user = :uid':'').'
  45 GROUP BY l.id
  46 ORDER BY '.(!empty($userId)?'COUNT(a.id) DESC, ':'').'COUNT(s.id) DESC, l.id'
  47                         )
  48                         ->setParameter('begin', $period->getStartDate())
  49                         ->setParameter('end', $period->getEndDate());
  50
  51                 //Set optional user id
  52                 if (!empty($userId)) {
  53                         $ret->setParameter('uid', $userId);
  54                 }
  55
  56                 //Get Result
  57                 $ret = $ret->getResult();
  58
  59                 //Rekey array
  60                 $ret = array_column($ret, 'title', 'id');
  61
  62                 //Filter array
  63                 foreach($ret as $k => $v) {
  64                         $ret[$k] = $translator->trans($v);
  65                 }
  66
  67                 //Send result
  68                 return $ret;
  69         }
  70
  71         /**
  72          * Fetch translated location title with session by date period
  73          *
  74          * @param $translator The TranslatorInterface instance
  75          * @param $period The date period
  76          * @param $granted The session is granted
  77          * TODO: a dropper
  78          */
  79         public function fetchTranslatedLocationByDatePeriod(TranslatorInterface $translator, $period, $granted = false) {
  80                 //Fetch sessions
  81                 $ret = $this->getEntityManager()
  82                         ->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')
  83                         ->setParameter('begin', $period->getStartDate())
  84                         ->setParameter('end', $period->getEndDate())
  85                         ->getResult();
  86
  87                 //Rekey array
  88                 $ret = array_column($ret, 'title', 'id');
  89
  90                 //Filter array
  91                 foreach($ret as $k => $v) {
  92                         $ret[$k] = $translator->trans($v);
  93                 }
  94
  95                 //Send result
  96                 return $ret;
  97         }
  98
  99         /**
 100          * Fetch translated location title with user session by date period
 101          *
 102          * @param $translator The TranslatorInterface instance
 103          * @param $period The date period
 104          * @param $userId The user uid
 105          * TODO: a dropper
 106          */
 107         public function fetchTranslatedUserLocationByDatePeriod(TranslatorInterface $translator, $period, $userId) {
 108                 //Fetch sessions
 109                 $ret = $this->getEntityManager()
 110                           ->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')
 111 #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
 112                         ->setParameter('begin', $period->getStartDate())
 113                         ->setParameter('end', $period->getEndDate())
 114                         ->setParameter('uid', $userId)
 115                         ->getResult();
 116
 117                 //Rekey array
 118                 $ret = array_column($ret, 'title', 'id');
 119
 120                 //Filter array
 121                 foreach($ret as $k => $v) {
 122                         $ret[$k] = $translator->trans($v);
 123                 }
 124
 125                 //Send result
 126                 return $ret;
 127         }
 128 }