]> Raphaƫl G. Git Repositories - airbundle/blob - Repository/SessionRepository.php
Display granted sessions in home page
[airbundle] / 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 }