Raphaël G. Git Repositories - airbundle/blob - Repository/SessionRepository.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  * SessionRepository
  10  */
  11 class SessionRepository extends \Doctrine\ORM\EntityRepository {
  12         /**
  13          * Find session by location, slot and date
  14          *
  15          * @param $location The location
  16          * @param $slot The slot
  17          * @param $date The datetime
  18          */
  19         public function findOneByLocationSlotDate($location, $slot, $date) {
  20                 //Fetch session
  21                 $ret = $this->getEntityManager()
  22                         ->createQuery('SELECT s FROM RapsysAirBundle:Session s WHERE (s.location = :location AND s.slot = :slot AND s.date = :date)')
  23                         ->setParameter('location', $location)
  24                         ->setParameter('slot', $slot)
  25                         ->setParameter('date', $date)
  26                         ->getSingleResult();
  27
  28                 //Send result
  29                 return $ret;
  30         }
  31
  32         /**
  33          * Find sessions by date period
  34          *
  35          * @param $period The date period
  36          */
  37         public function findAllByDatePeriod($period) {
  38                 //Fetch sessions
  39                 $ret = $this->getEntityManager()
  40                         ->createQuery('SELECT s FROM RapsysAirBundle:Session s WHERE s.date BETWEEN :begin AND :end')
  41                         ->setParameter('begin', $period->getStartDate())
  42                         ->setParameter('end', $period->getEndDate())
  43                         ->getResult();
  44
  45                 //Send result
  46                 return $ret;
  47         }
  48
  49         /**
  50          * Find sessions by location and date period
  51          *
  52          * @param $location The location
  53          * @param $period The date period
  54          */
  55         public function findAllByLocationDatePeriod($location, $period) {
  56                 //Fetch sessions
  57                 $ret = $this->getEntityManager()
  58                         ->createQuery('SELECT s FROM RapsysAirBundle:Session s WHERE (s.location = :location AND s.date BETWEEN :begin AND :end)')
  59                         ->setParameter('location', $location)
  60                         ->setParameter('begin', $period->getStartDate())
  61                         ->setParameter('end', $period->getEndDate())
  62                         ->getResult();
  63
  64                 //Send result
  65                 return $ret;
  66         }
  67
  68         /**
  69          * Fetch sessions calendar with translated location by date period
  70          *
  71          * @param $translator The TranslatorInterface instance
  72          * @param $period The date period
  73          * @param $locationId The location id
  74          * @param $sessionId The session id
  75          * @param $granted The session is granted
  76          */
  77         public function fetchCalendarByDatePeriod(TranslatorInterface $translator, $period, $locationId = null, $sessionId = null, $granted = false) {
  78                 //Get entity manager
  79                 $em = $this->getEntityManager();
  80
  81                 //Get quote strategy
  82                 $qs = $em->getConfiguration()->getQuoteStrategy();
  83                 $dp = $em->getConnection()->getDatabasePlatform();
  84
  85                 //Get quoted table names
  86                 //XXX: this allow to make this code table name independent
  87                 $tables = [
  88                         'RapsysAirBundle:GroupUser' => $qs->getJoinTableName($em->getClassMetadata('RapsysAirBundle:User')->getAssociationMapping('groups'), $em->getClassMetadata('RapsysAirBundle:User'), $dp),
  89                         'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
  90                         'RapsysAirBundle:Application' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Application'), $dp),
  91                         'RapsysAirBundle:Group' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Group'), $dp),
  92                         'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp),
  93                         'RapsysAirBundle:Slot' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Slot'), $dp),
  94                         'RapsysAirBundle:User' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:User'), $dp)
  95                 ];
  96
  97                 //Set the request
  98                 $req = 'SELECT s.id, s.date, s.location_id AS l_id, l.title AS l_title, s.slot_id AS t_id, t.title AS t_title, s.application_id AS a_id, a.user_id AS a_u_id, au.pseudonym AS a_u_pseudonym, GROUP_CONCAT(sa.user_id ORDER BY sa.user_id SEPARATOR "\n") AS as_u_id, GROUP_CONCAT(sau.pseudonym ORDER BY sa.user_id SEPARATOR "\n") AS as_u_pseudonym
  99                         FROM RapsysAirBundle:Session AS s
 100                         JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
 101                         JOIN RapsysAirBundle:Slot AS t ON (t.id = s.slot_id)
 102                         '.($granted?'':'LEFT ').'JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id)
 103                         '.($granted?'':'LEFT ').'JOIN RapsysAirBundle:User AS au ON (au.id = a.user_id)
 104                         LEFT JOIN RapsysAirBundle:Application AS sa ON (sa.session_id = s.id)
 105                         LEFT JOIN RapsysAirBundle:User AS sau ON (sau.id = sa.user_id)
 106                         WHERE s.date BETWEEN :begin AND :end
 107                         '.($locationId?'AND s.location_id = :lid':'').'
 108                         GROUP BY s.id
 109                         ORDER BY NULL';
 110
 111                 //Replace bundle entity name by table name
 112                 $req = str_replace(array_keys($tables), array_values($tables), $req);
 113
 114                 //Get result set mapping instance
 115                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
 116                 $rsm = new ResultSetMapping();
 117
 118                 //Declare all fields
 119                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
 120                 //addScalarResult($sqlColName, $resColName, $type = 'string');
 121                 $rsm->addScalarResult('id', 'id', 'integer')
 122                         ->addScalarResult('date', 'date', 'date')
 123                         ->addScalarResult('t_id', 't_id', 'integer')
 124                         ->addScalarResult('t_title', 't_title', 'string')
 125                         ->addScalarResult('l_id', 'l_id', 'integer')
 126                         ->addScalarResult('l_title', 'l_title', 'string')
 127                         ->addScalarResult('a_id', 'a_id', 'integer')
 128                         ->addScalarResult('a_u_id', 'a_u_id', 'integer')
 129                         ->addScalarResult('a_u_pseudonym', 'a_u_pseudonym', 'string')
 130                         //XXX: is a string because of \n separator
 131                         ->addScalarResult('as_u_id', 'as_u_id', 'string')
 132                         //XXX: is a string because of \n separator
 133                         ->addScalarResult('as_u_pseudonym', 'as_u_pseudonym', 'string')
 134                         ->addIndexByScalar('id');
 135
 136                 //Fetch result
 137                 $res = $em
 138                         ->createNativeQuery($req, $rsm)
 139                         ->setParameter('begin', $period->getStartDate())
 140                         ->setParameter('end', $period->getEndDate())
 141                         ->setParameter('lid', $locationId)
 142                         ->getResult();
 143
 144                 //Init calendar
 145                 $calendar = [];
 146
 147                 //Init month
 148                 $month = null;
 149
 150                 //Iterate on each day
 151                 foreach($period as $date) {
 152                         //Init day in calendar
 153                         $calendar[$Ymd = $date->format('Ymd')] = [
 154                                 'title' => $date->format('d'),
 155                                 'class' => [],
 156                                 'sessions' => []
 157                         ];
 158
 159                         //Append month for first day of month
 160                         if ($month != $date->format('m')) {
 161                                 $month = $date->format('m');
 162                                 $calendar[$Ymd]['title'] .= '/'.$month;
 163                         }
 164                         //Deal with today
 165                         if ($date->format('U') == ($today = strtotime('today'))) {
 166                                 $calendar[$Ymd]['title'] .= '/'.$month;
 167                                 $calendar[$Ymd]['current'] = true;
 168                                 $calendar[$Ymd]['class'][] =  'current';
 169                         }
 170                         //Disable passed days
 171                         if ($date->format('U') < $today) {
 172                                 $calendar[$Ymd]['disabled'] = true;
 173                                 $calendar[$Ymd]['class'][] =  'disabled';
 174                         }
 175                         //Set next month days
 176                         if ($date->format('m') > date('m')) {
 177                                 $calendar[$Ymd]['next'] = true;
 178                                 $calendar[$Ymd]['class'][] =  'next';
 179                         }
 180
 181                         //Iterate on each session to find the one of the day
 182                         foreach($res as $session) {
 183                                 if (($sessionYmd = $session['date']->format('Ymd')) == $Ymd) {
 184                                         //Count number of application
 185                                         $count = count(explode("\n", $session['as_u_id']));
 186
 187                                         //Compute classes
 188                                         $class = [];
 189                                         if (!empty($session['a_id'])) {
 190                                                 $applications = [ $session['a_u_id'] => $session['a_u_pseudonym'] ];
 191                                                 $class[] = 'granted';
 192                                         } elseif ($count == 0) {
 193                                                 $class[] = 'orphaned';
 194                                         } elseif ($count > 1) {
 195                                                 $class[] = 'disputed';
 196                                         } else {
 197                                                 $class[] = 'pending';
 198                                         }
 199
 200                                         if ($sessionId == $session['id']) {
 201                                                 $class[] = 'highlight';
 202                                         }
 203
 204                                         //Check that session is not granted
 205                                         if (empty($session['a_id'])) {
 206                                                 //Fetch pseudonyms from session applications
 207                                                 $applications = array_combine(explode("\n", $session['as_u_id']), explode("\n", $session['as_u_pseudonym']));
 208                                         }
 209
 210                                         //Add the session
 211                                         //XXX: see if we shouldn't prepend with 0 the slot and location to avoid collision ???
 212                                         $calendar[$Ymd]['sessions'][$session['t_id'].$session['l_id']] = [
 213                                                 'id' => $session['id'],
 214                                                 'title' => $translator->trans($session['l_title']).' ('.$translator->trans($session['t_title']).')',
 215                                                 'class' => $class,
 216                                                 'applications' => [ 0 => $translator->trans($session['t_title']).' '.$translator->trans('at '.$session['l_title']).($count > 1?' ['.$count.']':'') ]+$applications
 217                                         ];
 218                                 }
 219                         }
 220
 221                         //Sort sessions
 222                         ksort($calendar[$Ymd]['sessions']);
 223                 }
 224
 225                 //Send result
 226                 return $calendar;
 227         }
 228
 229         /**
 230          * Fetch sessions calendar with translated location by date period and user
 231          *
 232          * @param $translator The TranslatorInterface instance
 233          * @param $period The date period
 234          * @param $userId The user id
 235          * @param $sessionId The session id
 236          */
 237         public function fetchUserCalendarByDatePeriod(TranslatorInterface $translator, $period, $userId = null, $sessionId = null) {
 238                 //Get entity manager
 239                 $em = $this->getEntityManager();
 240
 241                 //Get quote strategy
 242                 $qs = $em->getConfiguration()->getQuoteStrategy();
 243                 $dp = $em->getConnection()->getDatabasePlatform();
 244
 245                 //Get quoted table names
 246                 //XXX: this allow to make this code table name independent
 247                 $tables = [
 248                         'RapsysAirBundle:GroupUser' => $qs->getJoinTableName($em->getClassMetadata('RapsysAirBundle:User')->getAssociationMapping('groups'), $em->getClassMetadata('RapsysAirBundle:User'), $dp),
 249                         'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
 250                         'RapsysAirBundle:Application' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Application'), $dp),
 251                         'RapsysAirBundle:Group' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Group'), $dp),
 252                         'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp),
 253                         'RapsysAirBundle:Slot' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Slot'), $dp),
 254                         'RapsysAirBundle:User' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:User'), $dp)
 255                 ];
 256
 257                 //Set the request
 258                 $req = 'SELECT s.id, s.date, s.location_id AS l_id, l.title AS l_title, s.slot_id AS t_id, t.title AS t_title, s.application_id AS a_id, a.user_id AS a_u_id, au.pseudonym AS a_u_pseudonym, GROUP_CONCAT(sa.user_id ORDER BY sa.user_id SEPARATOR "\n") AS as_u_id, GROUP_CONCAT(sau.pseudonym ORDER BY sa.user_id SEPARATOR "\n") AS as_u_pseudonym
 259                         FROM RapsysAirBundle:Session AS s
 260                         JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
 261                         JOIN RapsysAirBundle:Slot AS t ON (t.id = s.slot_id)
 262                         LEFT JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id)
 263                         LEFT JOIN RapsysAirBundle:User AS au ON (au.id = a.user_id)
 264                         LEFT JOIN RapsysAirBundle:Application AS sa ON (sa.session_id = s.id)
 265                         LEFT JOIN RapsysAirBundle:User AS sau ON (sau.id = sa.user_id)
 266                         WHERE s.date BETWEEN :begin AND :end
 267                         '.($userId?' AND sa.user_id = :uid':'').'
 268                         GROUP BY s.id
 269                         ORDER BY NULL';
 270
 271                 //Replace bundle entity name by table name
 272                 $req = str_replace(array_keys($tables), array_values($tables), $req);
 273
 274                 //Get result set mapping instance
 275                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
 276                 $rsm = new ResultSetMapping();
 277
 278                 //Declare all fields
 279                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
 280                 //addScalarResult($sqlColName, $resColName, $type = 'string');
 281                 $rsm->addScalarResult('id', 'id', 'integer')
 282                         ->addScalarResult('date', 'date', 'date')
 283                         ->addScalarResult('t_id', 't_id', 'integer')
 284                         ->addScalarResult('t_title', 't_title', 'string')
 285                         ->addScalarResult('l_id', 'l_id', 'integer')
 286                         ->addScalarResult('l_title', 'l_title', 'string')
 287                         ->addScalarResult('a_id', 'a_id', 'integer')
 288                         ->addScalarResult('a_u_id', 'a_u_id', 'integer')
 289                         ->addScalarResult('a_u_pseudonym', 'a_u_pseudonym', 'string')
 290                         //XXX: is a string because of \n separator
 291                         ->addScalarResult('as_u_id', 'as_u_id', 'string')
 292                         //XXX: is a string because of \n separator
 293                         ->addScalarResult('as_u_pseudonym', 'as_u_pseudonym', 'string')
 294                         ->addIndexByScalar('id');
 295
 296                 //Fetch result
 297                 $res = $em
 298                         ->createNativeQuery($req, $rsm)
 299                         ->setParameter('begin', $period->getStartDate())
 300                         ->setParameter('end', $period->getEndDate())
 301                         ->setParameter('uid', $userId)
 302                         ->getResult();
 303
 304                 //Init calendar
 305                 $calendar = [];
 306
 307                 //Init month
 308                 $month = null;
 309
 310                 //Iterate on each day
 311                 foreach($period as $date) {
 312                         //Init day in calendar
 313                         $calendar[$Ymd = $date->format('Ymd')] = [
 314                                 'title' => $date->format('d'),
 315                                 'class' => [],
 316                                 'sessions' => []
 317                         ];
 318
 319                         //Append month for first day of month
 320                         if ($month != $date->format('m')) {
 321                                 $month = $date->format('m');
 322                                 $calendar[$Ymd]['title'] .= '/'.$month;
 323                         }
 324                         //Deal with today
 325                         if ($date->format('U') == ($today = strtotime('today'))) {
 326                                 $calendar[$Ymd]['title'] .= '/'.$month;
 327                                 $calendar[$Ymd]['current'] = true;
 328                                 $calendar[$Ymd]['class'][] =  'current';
 329                         }
 330                         //Disable passed days
 331                         if ($date->format('U') < $today) {
 332                                 $calendar[$Ymd]['disabled'] = true;
 333                                 $calendar[$Ymd]['class'][] =  'disabled';
 334                         }
 335                         //Set next month days
 336                         if ($date->format('m') > date('m')) {
 337                                 $calendar[$Ymd]['next'] = true;
 338                                 $calendar[$Ymd]['class'][] =  'next';
 339                         }
 340
 341                         //Iterate on each session to find the one of the day
 342                         foreach($res as $session) {
 343                                 if (($sessionYmd = $session['date']->format('Ymd')) == $Ymd) {
 344                                         //Count number of application
 345                                         $count = count(explode("\n", $session['as_u_id']));
 346
 347                                         //Compute classes
 348                                         $class = [];
 349                                         if (!empty($session['a_id'])) {
 350                                                 $applications = [ $session['a_u_id'] => $session['a_u_pseudonym'] ];
 351                                                 if ($session['a_u_id'] == $userId) {
 352                                                         $class[] = 'granted';
 353                                                 } else {
 354                                                         $class[] = 'disputed';
 355                                                 }
 356                                         } elseif ($count == 0) {
 357                                                 $class[] = 'orphaned';
 358                                         } elseif ($count > 1) {
 359                                                 $class[] = 'disputed';
 360                                         } else {
 361                                                 $class[] = 'pending';
 362                                         }
 363
 364                                         if ($sessionId == $session['id']) {
 365                                                 $class[] = 'highlight';
 366                                         }
 367
 368                                         //Check that session is not granted
 369                                         if (empty($session['a_id'])) {
 370                                                 //Fetch pseudonyms from session applications
 371                                                 $applications = array_combine(explode("\n", $session['as_u_id']), explode("\n", $session['as_u_pseudonym']));
 372                                         }
 373
 374                                         //Add the session
 375                                         //XXX: see if we shouldn't prepend with 0 the slot and location to avoid collision ???
 376                                         $calendar[$Ymd]['sessions'][$session['t_id'].$session['l_id']] = [
 377                                                 'id' => $session['id'],
 378                                                 'title' => $translator->trans($session['l_title']).' ('.$translator->trans($session['t_title']).')',
 379                                                 'class' => $class,
 380                                                 'applications' => [ 0 => $translator->trans($session['t_title']).' '.$translator->trans('at '.$session['l_title']).($count > 1?' ['.$count.']':'') ]+$applications
 381                                         ];
 382                                 }
 383                         }
 384
 385                         //Sort sessions
 386                         ksort($calendar[$Ymd]['sessions']);
 387                 }
 388
 389                 //Send result
 390                 return $calendar;
 391         }
 392 }