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                         //Detect month change
 160                         if ($month != $date->format('m')) {
 161                                 $month = $date->format('m');
 162                                 //Append month for first day of month
 163                                 //XXX: except if today to avoid double add
 164                                 if ($date->format('U') != strtotime('today')) {
 165                                         $calendar[$Ymd]['title'] .= '/'.$month;
 166                                 }
 167                         }
 168                         //Deal with today
 169                         if ($date->format('U') == ($today = strtotime('today'))) {
 170                                 $calendar[$Ymd]['title'] .= '/'.$month;
 171                                 $calendar[$Ymd]['current'] = true;
 172                                 $calendar[$Ymd]['class'][] =  'current';
 173                         }
 174                         //Disable passed days
 175                         if ($date->format('U') < $today) {
 176                                 $calendar[$Ymd]['disabled'] = true;
 177                                 $calendar[$Ymd]['class'][] =  'disabled';
 178                         }
 179                         //Set next month days
 180                         if ($date->format('m') > date('m')) {
 181                                 $calendar[$Ymd]['next'] = true;
 182                                 $calendar[$Ymd]['class'][] =  'next';
 183                         }
 184
 185                         //Iterate on each session to find the one of the day
 186                         foreach($res as $session) {
 187                                 if (($sessionYmd = $session['date']->format('Ymd')) == $Ymd) {
 188                                         //Count number of application
 189                                         $count = count(explode("\n", $session['as_u_id']));
 190
 191                                         //Compute classes
 192                                         $class = [];
 193                                         if (!empty($session['a_id'])) {
 194                                                 $applications = [ $session['a_u_id'] => $session['a_u_pseudonym'] ];
 195                                                 $class[] = 'granted';
 196                                         } elseif ($count == 0) {
 197                                                 $class[] = 'orphaned';
 198                                         } elseif ($count > 1) {
 199                                                 $class[] = 'disputed';
 200                                         } else {
 201                                                 $class[] = 'pending';
 202                                         }
 203
 204                                         if ($sessionId == $session['id']) {
 205                                                 $class[] = 'highlight';
 206                                         }
 207
 208                                         //Check that session is not granted
 209                                         if (empty($session['a_id'])) {
 210                                                 //Fetch pseudonyms from session applications
 211                                                 $applications = array_combine(explode("\n", $session['as_u_id']), explode("\n", $session['as_u_pseudonym']));
 212                                         }
 213
 214                                         //Add the session
 215                                         //XXX: see if we shouldn't prepend with 0 the slot and location to avoid collision ???
 216                                         $calendar[$Ymd]['sessions'][$session['t_id'].$session['l_id']] = [
 217                                                 'id' => $session['id'],
 218                                                 'title' => $translator->trans($session['l_title']).' ('.$translator->trans($session['t_title']).')',
 219                                                 'class' => $class,
 220                                                 'applications' => [ 0 => $translator->trans($session['t_title']).' '.$translator->trans('at '.$session['l_title']).($count > 1?' ['.$count.']':'') ]+$applications
 221                                         ];
 222                                 }
 223                         }
 224
 225                         //Sort sessions
 226                         ksort($calendar[$Ymd]['sessions']);
 227                 }
 228
 229                 //Send result
 230                 return $calendar;
 231         }
 232
 233         /**
 234          * Fetch sessions calendar with translated location by date period and user
 235          *
 236          * @param $translator The TranslatorInterface instance
 237          * @param $period The date period
 238          * @param $userId The user id
 239          * @param $sessionId The session id
 240          */
 241         public function fetchUserCalendarByDatePeriod(TranslatorInterface $translator, $period, $userId = null, $sessionId = null) {
 242                 //Get entity manager
 243                 $em = $this->getEntityManager();
 244
 245                 //Get quote strategy
 246                 $qs = $em->getConfiguration()->getQuoteStrategy();
 247                 $dp = $em->getConnection()->getDatabasePlatform();
 248
 249                 //Get quoted table names
 250                 //XXX: this allow to make this code table name independent
 251                 $tables = [
 252                         'RapsysAirBundle:GroupUser' => $qs->getJoinTableName($em->getClassMetadata('RapsysAirBundle:User')->getAssociationMapping('groups'), $em->getClassMetadata('RapsysAirBundle:User'), $dp),
 253                         'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
 254                         'RapsysAirBundle:Application' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Application'), $dp),
 255                         'RapsysAirBundle:Group' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Group'), $dp),
 256                         'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp),
 257                         'RapsysAirBundle:Slot' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Slot'), $dp),
 258                         'RapsysAirBundle:User' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:User'), $dp)
 259                 ];
 260
 261                 //Set the request
 262                 $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
 263                         FROM RapsysAirBundle:Session AS s
 264                         JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
 265                         JOIN RapsysAirBundle:Slot AS t ON (t.id = s.slot_id)
 266                         LEFT JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id)
 267                         LEFT JOIN RapsysAirBundle:User AS au ON (au.id = a.user_id)
 268                         LEFT JOIN RapsysAirBundle:Application AS sa ON (sa.session_id = s.id)
 269                         LEFT JOIN RapsysAirBundle:User AS sau ON (sau.id = sa.user_id)
 270                         WHERE s.date BETWEEN :begin AND :end
 271                         '.($userId?' AND sa.user_id = :uid':'').'
 272                         GROUP BY s.id
 273                         ORDER BY NULL';
 274
 275                 //Replace bundle entity name by table name
 276                 $req = str_replace(array_keys($tables), array_values($tables), $req);
 277
 278                 //Get result set mapping instance
 279                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
 280                 $rsm = new ResultSetMapping();
 281
 282                 //Declare all fields
 283                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
 284                 //addScalarResult($sqlColName, $resColName, $type = 'string');
 285                 $rsm->addScalarResult('id', 'id', 'integer')
 286                         ->addScalarResult('date', 'date', 'date')
 287                         ->addScalarResult('t_id', 't_id', 'integer')
 288                         ->addScalarResult('t_title', 't_title', 'string')
 289                         ->addScalarResult('l_id', 'l_id', 'integer')
 290                         ->addScalarResult('l_title', 'l_title', 'string')
 291                         ->addScalarResult('a_id', 'a_id', 'integer')
 292                         ->addScalarResult('a_u_id', 'a_u_id', 'integer')
 293                         ->addScalarResult('a_u_pseudonym', 'a_u_pseudonym', 'string')
 294                         //XXX: is a string because of \n separator
 295                         ->addScalarResult('as_u_id', 'as_u_id', 'string')
 296                         //XXX: is a string because of \n separator
 297                         ->addScalarResult('as_u_pseudonym', 'as_u_pseudonym', 'string')
 298                         ->addIndexByScalar('id');
 299
 300                 //Fetch result
 301                 $res = $em
 302                         ->createNativeQuery($req, $rsm)
 303                         ->setParameter('begin', $period->getStartDate())
 304                         ->setParameter('end', $period->getEndDate())
 305                         ->setParameter('uid', $userId)
 306                         ->getResult();
 307
 308                 //Init calendar
 309                 $calendar = [];
 310
 311                 //Init month
 312                 $month = null;
 313
 314                 //Iterate on each day
 315                 foreach($period as $date) {
 316                         //Init day in calendar
 317                         $calendar[$Ymd = $date->format('Ymd')] = [
 318                                 'title' => $date->format('d'),
 319                                 'class' => [],
 320                                 'sessions' => []
 321                         ];
 322
 323                         //Detect month change
 324                         if ($month != $date->format('m')) {
 325                                 $month = $date->format('m');
 326                                 //Append month for first day of month
 327                                 //XXX: except if today to avoid double add
 328                                 if ($date->format('U') != strtotime('today')) {
 329                                         $calendar[$Ymd]['title'] .= '/'.$month;
 330                                 }
 331                         }
 332                         //Deal with today
 333                         if ($date->format('U') == ($today = strtotime('today'))) {
 334                                 $calendar[$Ymd]['title'] .= '/'.$month;
 335                                 $calendar[$Ymd]['current'] = true;
 336                                 $calendar[$Ymd]['class'][] =  'current';
 337                         }
 338                         //Disable passed days
 339                         if ($date->format('U') < $today) {
 340                                 $calendar[$Ymd]['disabled'] = true;
 341                                 $calendar[$Ymd]['class'][] =  'disabled';
 342                         }
 343                         //Set next month days
 344                         if ($date->format('m') > date('m')) {
 345                                 $calendar[$Ymd]['next'] = true;
 346                                 $calendar[$Ymd]['class'][] =  'next';
 347                         }
 348
 349                         //Iterate on each session to find the one of the day
 350                         foreach($res as $session) {
 351                                 if (($sessionYmd = $session['date']->format('Ymd')) == $Ymd) {
 352                                         //Count number of application
 353                                         $count = count(explode("\n", $session['as_u_id']));
 354
 355                                         //Compute classes
 356                                         $class = [];
 357                                         if (!empty($session['a_id'])) {
 358                                                 $applications = [ $session['a_u_id'] => $session['a_u_pseudonym'] ];
 359                                                 if ($session['a_u_id'] == $userId) {
 360                                                         $class[] = 'granted';
 361                                                 } else {
 362                                                         $class[] = 'disputed';
 363                                                 }
 364                                         } elseif ($count == 0) {
 365                                                 $class[] = 'orphaned';
 366                                         } elseif ($count > 1) {
 367                                                 $class[] = 'disputed';
 368                                         } else {
 369                                                 $class[] = 'pending';
 370                                         }
 371
 372                                         if ($sessionId == $session['id']) {
 373                                                 $class[] = 'highlight';
 374                                         }
 375
 376                                         //Check that session is not granted
 377                                         if (empty($session['a_id'])) {
 378                                                 //Fetch pseudonyms from session applications
 379                                                 $applications = array_combine(explode("\n", $session['as_u_id']), explode("\n", $session['as_u_pseudonym']));
 380                                         }
 381
 382                                         //Add the session
 383                                         //XXX: see if we shouldn't prepend with 0 the slot and location to avoid collision ???
 384                                         $calendar[$Ymd]['sessions'][$session['t_id'].$session['l_id']] = [
 385                                                 'id' => $session['id'],
 386                                                 'title' => $translator->trans($session['l_title']).' ('.$translator->trans($session['t_title']).')',
 387                                                 'class' => $class,
 388                                                 'applications' => [ 0 => $translator->trans($session['t_title']).' '.$translator->trans('at '.$session['l_title']).($count > 1?' ['.$count.']':'') ]+$applications
 389                                         ];
 390                                 }
 391                         }
 392
 393                         //Sort sessions
 394                         ksort($calendar[$Ymd]['sessions']);
 395                 }
 396
 397                 //Send result
 398                 return $calendar;
 399         }
 400 }