]> Raphaƫl G. Git Repositories - airbundle/blob - Repository/SessionRepository.php
Fix location list
[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 //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 }