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 use Doctrine\ORM\Query\ResultSetMapping;
   7
   8 /**
   9  * LocationRepository
  10  */
  11 class LocationRepository extends \Doctrine\ORM\EntityRepository {
  12         /**
  13          * Find complementary locations by session id
  14          *
  15          * @param $id The session id
  16          * @return array The other locations
  17          */
  18         public function findComplementBySessionId($id) {
  19                 //Fetch complement locations
  20                 $ret = $this->getEntityManager()
  21                           ->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')
  22                         ->setParameter('sid', $id)
  23                         ->getArrayResult();
  24
  25                 //Rekey array
  26                 $ret = array_column($ret, 'id', 'title');
  27
  28                 return $ret;
  29         }
  30
  31         /**
  32          * Find translated location title sorted by date period
  33          *
  34          * @param $translator The TranslatorInterface instance
  35          * @param $period The date period
  36          * @param $granted The session is granted
  37          */
  38         public function findTranslatedSortedByPeriod(TranslatorInterface $translator, $period, $userId = null) {
  39                 //Fetch sessions
  40                 $ret = $this->getEntityManager()
  41                         ->createQuery(
  42 'SELECT l.id, l.title
  43 FROM RapsysAirBundle:Location l
  44 LEFT JOIN RapsysAirBundle:Session s WITH s.location = l.id AND s.date BETWEEN :begin AND :end
  45 LEFT JOIN RapsysAirBundle:Application a WITH a.id = s.application'.(!empty($userId)?' AND a.user = :uid':'').'
  46 GROUP BY l.id
  47 ORDER BY '.(!empty($userId)?'COUNT(a.id) DESC, ':'').'COUNT(s.id) DESC, l.id'
  48                         )
  49                         ->setParameter('begin', $period->getStartDate())
  50                         ->setParameter('end', $period->getEndDate());
  51
  52                 //Set optional user id
  53                 if (!empty($userId)) {
  54                         $ret->setParameter('uid', $userId);
  55                 }
  56
  57                 //Get Result
  58                 $ret = $ret->getResult();
  59
  60                 //Rekey array
  61                 $ret = array_column($ret, 'title', 'id');
  62
  63                 //Filter array
  64                 foreach($ret as $k => $v) {
  65                         $ret[$k] = $translator->trans($v);
  66                 }
  67
  68                 //Send result
  69                 return $ret;
  70         }
  71
  72         /**
  73          * Fetch translated location title with session by date period
  74          *
  75          * @param $translator The TranslatorInterface instance
  76          * @param $period The date period
  77          * @param $granted The session is granted
  78          * TODO: a dropper
  79          */
  80         public function fetchTranslatedLocationByDatePeriod(TranslatorInterface $translator, $period, $granted = false) {
  81                 //Fetch sessions
  82                 $ret = $this->getEntityManager()
  83                         ->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')
  84                         ->setParameter('begin', $period->getStartDate())
  85                         ->setParameter('end', $period->getEndDate())
  86                         ->getResult();
  87
  88                 //Rekey array
  89                 $ret = array_column($ret, 'title', 'id');
  90
  91                 //Filter array
  92                 foreach($ret as $k => $v) {
  93                         $ret[$k] = $translator->trans($v);
  94                 }
  95
  96                 //Send result
  97                 return $ret;
  98         }
  99
 100         /**
 101          * Fetch translated location title with user session by date period
 102          *
 103          * @param $translator The TranslatorInterface instance
 104          * @param $period The date period
 105          * @param $userId The user uid
 106          * TODO: a dropper
 107          */
 108         public function fetchTranslatedUserLocationByDatePeriod(TranslatorInterface $translator, $period, $userId) {
 109                 //Fetch sessions
 110                 $ret = $this->getEntityManager()
 111                         ->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')
 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
 129         /**
 130          * Find locations by user id
 131          *
 132          * @param $id The user id
 133          * @return array The user locations
 134          */
 135         public function findByUserId($userId) {
 136                 //Get entity manager
 137                 $em = $this->getEntityManager();
 138
 139                 //Get quote strategy
 140                 $qs = $em->getConfiguration()->getQuoteStrategy();
 141                 $dp = $em->getConnection()->getDatabasePlatform();
 142
 143                 //Get quoted table names
 144                 //XXX: this allow to make this code table name independent
 145                 $tables = [
 146                         'RapsysAirBundle:UserLocation' => $qs->getJoinTableName($em->getClassMetadata('RapsysAirBundle:User')->getAssociationMapping('locations'), $em->getClassMetadata('RapsysAirBundle:User'), $dp),
 147                         'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp)
 148                 ];
 149
 150                 //Set the request
 151                 $req = 'SELECT l.id, l.title
 152 FROM RapsysAirBundle:UserLocation AS ul
 153 JOIN RapsysAirBundle:Location AS l ON (l.id = ul.location_id)
 154 WHERE ul.user_id = :uid';
 155
 156                 //Replace bundle entity name by table name
 157                 $req = str_replace(array_keys($tables), array_values($tables), $req);
 158
 159                 //Get result set mapping instance
 160                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
 161                 $rsm = new ResultSetMapping();
 162
 163                 //Declare result set for our request
 164                 $rsm->addEntityResult('RapsysAirBundle:Location', 'l');
 165                 $rsm->addFieldResult('l', 'id', 'id');
 166                 $rsm->addFieldResult('l', 'title', 'title');
 167
 168                 //Send result
 169                 return $em
 170                         ->createNativeQuery($req, $rsm)
 171                         ->setParameter('uid', $userId)
 172                         ->getResult();
 173         }
 174 }