]> RaphaΓ«l G. Git Repositories - airbundle/blob - Repository/SessionRepository.php
Cleanup
[airbundle] / Repository / SessionRepository.php
1 <?php
2
3 namespace Rapsys\AirBundle\Repository;
4
5 use Symfony\Component\Translation\TranslatorInterface;
6 use Doctrine\DBAL\Types\Type;
7 use Doctrine\ORM\Query\ResultSetMapping;
8 use Doctrine\ORM\Query;
9
10 /**
11 * SessionRepository
12 */
13 class SessionRepository extends \Doctrine\ORM\EntityRepository {
14 ///Set accuweather max number of daily pages
15 const ACCUWEATHER_DAILY = 12;
16
17 ///Set accuweather max number of hourly pages
18 const ACCUWEATHER_HOURLY = 3;
19
20 ///Set guest delay
21 const GUEST_DELAY = 2;
22
23 ///Set regular delay
24 const REGULAR_DELAY = 3;
25
26 ///Set senior
27 const SENIOR_DELAY = 4;
28
29 ///Set glyphs
30 //TODO: document utf-8 codes ?
31 const GLYPHS = [
32 //Slots
33 'Morning' => 'πŸŒ…', #0001f305
34 'Afternoon' => 'β˜€οΈ', #2600
35 'Evening' => 'πŸŒ‡', #0001f307
36 'After' => '✨', #2728
37 //Weathers
38 'Cleary' => 'β˜€', #2600
39 'Sunny' => 'β›…', #26c5
40 'Cloudy' => '☁', #2601
41 'Winty' => '❄️', #2744
42 'Rainy' => 'πŸŒ‚', #0001f302
43 'Stormy' => 'β˜”' #2614
44 ];
45
46 /**
47 * Find session by location, slot and date
48 *
49 * @param $location The location
50 * @param $slot The slot
51 * @param $date The datetime
52 */
53 public function findOneByLocationSlotDate($location, $slot, $date) {
54 //Return sessions
55 return $this->getEntityManager()
56 ->createQuery('SELECT s FROM RapsysAirBundle:Session s WHERE (s.location = :location AND s.slot = :slot AND s.date = :date)')
57 ->setParameter('location', $location)
58 ->setParameter('slot', $slot)
59 ->setParameter('date', $date)
60 ->getSingleResult();
61 }
62
63 /**
64 * Find sessions by date period
65 *
66 * @param $period The date period
67 */
68 public function findAllByDatePeriod($period) {
69 //Return sessions
70 return $this->getEntityManager()
71 ->createQuery('SELECT s FROM RapsysAirBundle:Session s WHERE s.date BETWEEN :begin AND :end')
72 ->setParameter('begin', $period->getStartDate())
73 ->setParameter('end', $period->getEndDate())
74 ->getResult();
75 }
76
77 /**
78 * Find sessions by location and date period
79 *
80 * @param $location The location
81 * @param $period The date period
82 */
83 public function findAllByLocationDatePeriod($location, $period) {
84 //Return sessions
85 return $this->getEntityManager()
86 ->createQuery('SELECT s FROM RapsysAirBundle:Session s WHERE (s.location = :location AND s.date BETWEEN :begin AND :end)')
87 ->setParameter('location', $location)
88 ->setParameter('begin', $period->getStartDate())
89 ->setParameter('end', $period->getEndDate())
90 ->getResult();
91 }
92
93 /**
94 * Find one session by location and user id within last month
95 *
96 * @param $location The location id
97 * @param $user The user id
98 */
99 public function findOneWithinLastMonthByLocationUser($location, $user) {
100 //Get entity manager
101 $em = $this->getEntityManager();
102
103 //Get quote strategy
104 $qs = $em->getConfiguration()->getQuoteStrategy();
105 $dp = $em->getConnection()->getDatabasePlatform();
106
107 //Get quoted table names
108 //XXX: this allow to make this code table name independent
109 $tables = [
110 'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
111 'RapsysAirBundle:Application' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Application'), $dp),
112 "\t" => '',
113 "\n" => ' '
114 ];
115
116 //Set the request
117 //XXX: give the gooddelay to guest just in case
118 $req =<<<SQL
119 SELECT s.id
120 FROM RapsysAirBundle:Session s
121 JOIN RapsysAirBundle:Application a ON (a.id = s.application_id AND a.user_id = :uid AND (a.canceled IS NULL OR TIMESTAMPDIFF(DAY, a.canceled, ADDTIME(s.date, s.begin)) < 1))
122 WHERE s.location_id = :lid AND s.date >= DATE_ADD(DATE_SUB(NOW(), INTERVAL 1 MONTH), INTERVAL :gooddelay DAY)
123 SQL;
124
125 //Replace bundle entity name by table name
126 $req = str_replace(array_keys($tables), array_values($tables), $req);
127
128 //Get result set mapping instance
129 $rsm = new ResultSetMapping();
130
131 //Declare all fields
132 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
133 $rsm->addScalarResult('id', 'id', 'integer')
134 ->addIndexByScalar('id');
135
136 //Return result
137 return $em
138 ->createNativeQuery($req, $rsm)
139 ->setParameter('lid', $location)
140 ->setParameter('uid', $user)
141 ->setParameter('gooddelay', self::SENIOR_DELAY)
142 ->getOneOrNullResult();
143 }
144
145 /**
146 * Fetch session by id
147 *
148 * @param $id The session id
149 * @param $locale The locale
150 * @return array The session data
151 */
152 public function fetchOneById($id, $locale = null) {
153 //Get entity manager
154 $em = $this->getEntityManager();
155
156 //Get quote strategy
157 $qs = $em->getConfiguration()->getQuoteStrategy();
158 $dp = $em->getConnection()->getDatabasePlatform();
159
160 //Get quoted table names
161 //XXX: this allow to make this code table name independent
162 $tables = [
163 'RapsysAirBundle:Application' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Application'), $dp),
164 'RapsysAirBundle:Group' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Group'), $dp),
165 'RapsysAirBundle:GroupUser' => $qs->getJoinTableName($em->getClassMetadata('RapsysAirBundle:User')->getAssociationMapping('groups'), $em->getClassMetadata('RapsysAirBundle:User'), $dp),
166 'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp),
167 'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
168 'RapsysAirBundle:Snippet' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Snippet'), $dp),
169 'RapsysAirBundle:Slot' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Slot'), $dp),
170 'RapsysAirBundle:User' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:User'), $dp),
171 ':afterid' => 4,
172 "\t" => '',
173 "\n" => ' '
174 ];
175
176 //Set the request
177 //TODO: compute scores ?
178 //TODO: compute delivery date ? (J-3/J-4 ?)
179 $req =<<<SQL
180 SELECT
181 s.id,
182 s.date,
183 s.begin,
184 ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS start,
185 s.length,
186 ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS stop,
187 s.rainfall,
188 s.rainrisk,
189 s.realfeel,
190 s.realfeelmin,
191 s.realfeelmax,
192 s.temperature,
193 s.temperaturemin,
194 s.temperaturemax,
195 s.locked,
196 s.created,
197 s.updated,
198 s.location_id AS l_id,
199 l.short AS l_short,
200 l.title AS l_title,
201 l.address AS l_address,
202 l.zipcode AS l_zipcode,
203 l.city AS l_city,
204 l.latitude AS l_latitude,
205 l.longitude AS l_longitude,
206 s.slot_id AS t_id,
207 t.title AS t_title,
208 s.application_id AS a_id,
209 a.canceled AS a_canceled,
210 a.user_id AS au_id,
211 au.pseudonym AS au_pseudonym,
212 p.id AS p_id,
213 p.description AS p_description,
214 p.class AS p_class,
215 p.contact AS p_contact,
216 p.donate AS p_donate,
217 p.link AS p_link,
218 p.profile AS p_profile,
219 GROUP_CONCAT(sa.id ORDER BY sa.user_id SEPARATOR "\\n") AS sa_id,
220 GROUP_CONCAT(IFNULL(sa.score, 'NULL') ORDER BY sa.user_id SEPARATOR "\\n") AS sa_score,
221 GROUP_CONCAT(sa.created ORDER BY sa.user_id SEPARATOR "\\n") AS sa_created,
222 GROUP_CONCAT(sa.updated ORDER BY sa.user_id SEPARATOR "\\n") AS sa_updated,
223 GROUP_CONCAT(IFNULL(sa.canceled, 'NULL') ORDER BY sa.user_id SEPARATOR "\\n") AS sa_canceled,
224 GROUP_CONCAT(sa.user_id ORDER BY sa.user_id SEPARATOR "\\n") AS sau_id,
225 GROUP_CONCAT(sau.pseudonym ORDER BY sa.user_id SEPARATOR "\\n") AS sau_pseudonym
226 FROM RapsysAirBundle:Session AS s
227 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
228 JOIN RapsysAirBundle:Slot AS t ON (t.id = s.slot_id)
229 LEFT JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id)
230 LEFT JOIN RapsysAirBundle:User AS au ON (au.id = a.user_id)
231 LEFT JOIN RapsysAirBundle:Snippet AS p ON (p.location_id = s.location_id AND p.user_id = a.user_id AND p.locale = :locale)
232 LEFT JOIN RapsysAirBundle:Application AS sa ON (sa.session_id = s.id)
233 LEFT JOIN RapsysAirBundle:User AS sau ON (sau.id = sa.user_id)
234 WHERE s.id = :sid
235 GROUP BY s.id
236 ORDER BY NULL
237 SQL;
238
239 //Replace bundle entity name by table name
240 $req = str_replace(array_keys($tables), array_values($tables), $req);
241
242 //Get result set mapping instance
243 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
244 $rsm = new ResultSetMapping();
245
246 //Declare all fields
247 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
248 $rsm->addScalarResult('id', 'id', 'integer')
249 ->addScalarResult('date', 'date', 'date')
250 ->addScalarResult('begin', 'begin', 'time')
251 ->addScalarResult('start', 'start', 'datetime')
252 ->addScalarResult('length', 'length', 'time')
253 ->addScalarResult('stop', 'stop', 'datetime')
254 ->addScalarResult('rainfall', 'rainfall', 'float')
255 ->addScalarResult('rainrisk', 'rainrisk', 'float')
256 ->addScalarResult('realfeel', 'realfeel', 'float')
257 ->addScalarResult('realfeelmin', 'realfeelmin', 'float')
258 ->addScalarResult('realfeelmax', 'realfeelmax', 'float')
259 ->addScalarResult('temperature', 'temperature', 'float')
260 ->addScalarResult('temperaturemin', 'temperaturemin', 'float')
261 ->addScalarResult('temperaturemax', 'temperaturemax', 'float')
262 ->addScalarResult('locked', 'locked', 'datetime')
263 ->addScalarResult('created', 'created', 'datetime')
264 ->addScalarResult('updated', 'updated', 'datetime')
265 ->addScalarResult('l_id', 'l_id', 'integer')
266 ->addScalarResult('l_short', 'l_short', 'string')
267 ->addScalarResult('l_title', 'l_title', 'string')
268 ->addScalarResult('l_address', 'l_address', 'string')
269 ->addScalarResult('l_zipcode', 'l_zipcode', 'string')
270 ->addScalarResult('l_city', 'l_city', 'string')
271 ->addScalarResult('l_latitude', 'l_latitude', 'float')
272 ->addScalarResult('l_longitude', 'l_longitude', 'float')
273 ->addScalarResult('t_id', 't_id', 'integer')
274 ->addScalarResult('t_title', 't_title', 'string')
275 ->addScalarResult('a_id', 'a_id', 'integer')
276 ->addScalarResult('a_canceled', 'a_canceled', 'datetime')
277 ->addScalarResult('au_id', 'au_id', 'integer')
278 ->addScalarResult('au_pseudonym', 'au_pseudonym', 'string')
279 ->addScalarResult('p_id', 'p_id', 'integer')
280 ->addScalarResult('p_description', 'p_description', 'text')
281 ->addScalarResult('p_class', 'p_class', 'text')
282 ->addScalarResult('p_contact', 'p_contact', 'text')
283 ->addScalarResult('p_donate', 'p_donate', 'text')
284 ->addScalarResult('p_link', 'p_link', 'text')
285 ->addScalarResult('p_profile', 'p_profile', 'text')
286 //XXX: is a string because of \n separator
287 ->addScalarResult('sa_id', 'sa_id', 'string')
288 //XXX: is a string because of \n separator
289 ->addScalarResult('sa_score', 'sa_score', 'string')
290 //XXX: is a string because of \n separator
291 ->addScalarResult('sa_created', 'sa_created', 'string')
292 //XXX: is a string because of \n separator
293 ->addScalarResult('sa_updated', 'sa_updated', 'string')
294 //XXX: is a string because of \n separator
295 ->addScalarResult('sa_canceled', 'sa_canceled', 'string')
296 //XXX: is a string because of \n separator
297 ->addScalarResult('sau_id', 'sau_id', 'string')
298 //XXX: is a string because of \n separator
299 ->addScalarResult('sau_pseudonym', 'sau_pseudonym', 'string')
300 ->addIndexByScalar('id');
301
302 //Return result
303 return $em
304 ->createNativeQuery($req, $rsm)
305 ->setParameter('sid', $id)
306 ->setParameter('locale', $locale)
307 ->getOneOrNullResult();
308 }
309
310 /**
311 * Fetch sessions calendar with translated location by date period
312 *
313 * @param $translator The TranslatorInterface instance
314 * @param $period The date period
315 * @param $locationId The location id
316 * @param $sessionId The session id
317 * @param $granted The session is granted
318 */
319 public function fetchCalendarByDatePeriod(TranslatorInterface $translator, $period, $locationId = null, $sessionId = null, $granted = false) {
320 //Get entity manager
321 $em = $this->getEntityManager();
322
323 //Get quote strategy
324 $qs = $em->getConfiguration()->getQuoteStrategy();
325 $dp = $em->getConnection()->getDatabasePlatform();
326
327 //Get quoted table names
328 //XXX: this allow to make this code table name independent
329 $tables = [
330 'RapsysAirBundle:GroupUser' => $qs->getJoinTableName($em->getClassMetadata('RapsysAirBundle:User')->getAssociationMapping('groups'), $em->getClassMetadata('RapsysAirBundle:User'), $dp),
331 'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
332 'RapsysAirBundle:Application' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Application'), $dp),
333 'RapsysAirBundle:Group' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Group'), $dp),
334 'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp),
335 'RapsysAirBundle:Slot' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Slot'), $dp),
336 'RapsysAirBundle:User' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:User'), $dp),
337 ':afterid' => 4,
338 "\t" => '',
339 "\n" => ' '
340 ];
341
342 //Init granted sql
343 $grantSql = '';
344
345 //When granted is set
346 if (empty($granted)) {
347 //Set application and user as optional
348 $grantSql = 'LEFT ';
349 }
350
351 //Init location sql
352 $locationSql = '';
353
354 //When location id is set
355 if (!empty($locationId)) {
356 //Add location id clause
357 $locationSql = "\n\t".'AND s.location_id = :lid';
358 }
359
360 //Set the request
361 $req = <<<SQL
362 SELECT
363 s.id,
364 s.date,
365 s.rainrisk,
366 s.rainfall,
367 s.realfeel,
368 s.temperature,
369 s.locked,
370 ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS start,
371 ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS stop,
372 s.location_id AS l_id,
373 l.short AS l_short,
374 l.title AS l_title,
375 s.slot_id AS t_id,
376 t.title AS t_title,
377 s.application_id AS a_id,
378 a.canceled AS a_canceled,
379 a.user_id AS au_id,
380 au.pseudonym AS au_pseudonym,
381 GROUP_CONCAT(sa.user_id ORDER BY sa.user_id SEPARATOR "\\n") AS sau_id,
382 GROUP_CONCAT(sau.pseudonym ORDER BY sa.user_id SEPARATOR "\\n") AS sau_pseudonym
383 FROM RapsysAirBundle:Session AS s
384 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
385 JOIN RapsysAirBundle:Slot AS t ON (t.id = s.slot_id)
386 ${grantSql}JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id)
387 ${grantSql}JOIN RapsysAirBundle:User AS au ON (au.id = a.user_id)
388 LEFT JOIN RapsysAirBundle:Application AS sa ON (sa.session_id = s.id)
389 LEFT JOIN RapsysAirBundle:User AS sau ON (sau.id = sa.user_id)
390 WHERE s.date BETWEEN :begin AND :end${locationSql}
391 GROUP BY s.id
392 ORDER BY NULL
393 SQL;
394
395 //Replace bundle entity name by table name
396 $req = str_replace(array_keys($tables), array_values($tables), $req);
397
398 //Get result set mapping instance
399 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
400 $rsm = new ResultSetMapping();
401
402 //Declare all fields
403 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
404 //addScalarResult($sqlColName, $resColName, $type = 'string');
405 $rsm->addScalarResult('id', 'id', 'integer')
406 ->addScalarResult('date', 'date', 'date')
407 ->addScalarResult('rainrisk', 'rainrisk', 'float')
408 ->addScalarResult('rainfall', 'rainfall', 'float')
409 ->addScalarResult('realfeel', 'realfeel', 'float')
410 ->addScalarResult('temperature', 'temperature', 'float')
411 ->addScalarResult('locked', 'locked', 'datetime')
412 ->addScalarResult('start', 'start', 'datetime')
413 ->addScalarResult('stop', 'stop', 'datetime')
414 ->addScalarResult('t_id', 't_id', 'integer')
415 ->addScalarResult('t_title', 't_title', 'string')
416 ->addScalarResult('l_id', 'l_id', 'integer')
417 ->addScalarResult('l_short', 'l_short', 'string')
418 ->addScalarResult('l_title', 'l_title', 'string')
419 ->addScalarResult('a_id', 'a_id', 'integer')
420 ->addScalarResult('a_canceled', 'a_canceled', 'datetime')
421 ->addScalarResult('au_id', 'au_id', 'integer')
422 ->addScalarResult('au_pseudonym', 'au_pseudonym', 'string')
423 //XXX: is a string because of \n separator
424 ->addScalarResult('sau_id', 'sau_id', 'string')
425 //XXX: is a string because of \n separator
426 ->addScalarResult('sau_pseudonym', 'sau_pseudonym', 'string')
427 ->addIndexByScalar('id');
428
429 //Fetch result
430 $res = $em
431 ->createNativeQuery($req, $rsm)
432 ->setParameter('begin', $period->getStartDate())
433 ->setParameter('end', $period->getEndDate());
434
435 //Add optional location id
436 if (!empty($locationId)) {
437 $res->setParameter('lid', $locationId);
438 }
439
440 //Get result
441 $res = $res->getResult();
442
443 //Init calendar
444 $calendar = [];
445
446 //Init month
447 $month = null;
448
449 //Iterate on each day
450 foreach($period as $date) {
451 //Init day in calendar
452 $calendar[$Ymd = $date->format('Ymd')] = [
453 'title' => $translator->trans($date->format('l')).' '.$date->format('d'),
454 'class' => [],
455 'sessions' => []
456 ];
457
458 //Detect month change
459 if ($month != $date->format('m')) {
460 $month = $date->format('m');
461 //Append month for first day of month
462 //XXX: except if today to avoid double add
463 if ($date->format('U') != strtotime('today')) {
464 $calendar[$Ymd]['title'] .= '/'.$month;
465 }
466 }
467 //Deal with today
468 if ($date->format('U') == ($today = strtotime('today'))) {
469 $calendar[$Ymd]['title'] .= '/'.$month;
470 $calendar[$Ymd]['current'] = true;
471 $calendar[$Ymd]['class'][] = 'current';
472 }
473 //Disable passed days
474 if ($date->format('U') < $today) {
475 $calendar[$Ymd]['disabled'] = true;
476 $calendar[$Ymd]['class'][] = 'disabled';
477 }
478 //Set next month days
479 if ($date->format('m') > date('m')) {
480 $calendar[$Ymd]['next'] = true;
481 #$calendar[$Ymd]['class'][] = 'next';
482 }
483
484 //Detect sunday
485 if ($date->format('w') == 0) {
486 $calendar[$Ymd]['class'][] = 'sunday';
487 }
488
489 //Iterate on each session to find the one of the day
490 foreach($res as $session) {
491 if (($sessionYmd = $session['date']->format('Ymd')) == $Ymd) {
492 //Count number of application
493 $count = count(explode("\n", $session['sau_id']));
494
495 //Compute classes
496 $class = [];
497 if (!empty($session['a_id'])) {
498 $applications = [ $session['au_id'] => $session['au_pseudonym'] ];
499 if (!empty($session['a_canceled'])) {
500 $class[] = 'canceled';
501 } else {
502 $class[] = 'granted';
503 }
504 } elseif ($count > 1) {
505 $class[] = 'disputed';
506 } elseif (!empty($session['locked'])) {
507 $class[] = 'locked';
508 } else {
509 $class[] = 'pending';
510 }
511
512 if ($sessionId == $session['id']) {
513 $class[] = 'highlight';
514 }
515
516 //Set temperature
517 //XXX: realfeel may be null, temperature should not
518 $temperature = $session['realfeel'] !== null ? $session['realfeel'] : $session['temperature'];
519
520 //Compute weather
521 //XXX: rainfall may be null
522 if ($session['rainrisk'] > 0.50 || $session['rainfall'] > 2) {
523 $weather = self::GLYPHS['Stormy'];
524 } elseif ($session['rainrisk'] > 0.40 || $session['rainfall'] > 1) {
525 $weather = self::GLYPHS['Rainy'];
526 } elseif ($temperature > 24) {
527 $weather = self::GLYPHS['Cleary'];
528 } elseif ($temperature > 17) {
529 $weather = self::GLYPHS['Sunny'];
530 } elseif ($temperature > 10) {
531 $weather = self::GLYPHS['Cloudy'];
532 } elseif ($temperature !== null) {
533 $weather = self::GLYPHS['Winty'];
534 } else {
535 $weather = null;
536 }
537
538 //Init weathertitle
539 $weathertitle = [];
540
541 //Check if realfeel is available
542 if ($session['realfeel'] !== null) {
543 $weathertitle[] = $session['realfeel'].'Β°R';
544 }
545
546 //Check if temperature is available
547 if ($session['temperature'] !== null) {
548 $weathertitle[] = $session['temperature'].'Β°C';
549 }
550
551 //Check if rainrisk is available
552 if ($session['rainrisk'] !== null) {
553 $weathertitle[] = ($session['rainrisk']*100).'%';
554 }
555
556 //Check if rainfall is available
557 if ($session['rainfall'] !== null) {
558 $weathertitle[] = $session['rainfall'].'mm';
559 }
560
561 //Set applications
562 $applications = [
563 0 => $translator->trans($session['t_title']).' '.$translator->trans('at '.$session['l_title']).$translator->trans(':')
564 ];
565
566 //Fetch pseudonyms from session applications
567 $applications += array_combine(explode("\n", $session['sau_id']), array_map(function ($v) {return '- '.$v;}, explode("\n", $session['sau_pseudonym'])));
568
569 //Set pseudonym
570 $pseudonym = null;
571
572 //Check that session is not granted
573 if (empty($session['a_id'])) {
574 //With location id and unique application
575 if ($count == 1) {
576 //Set unique application pseudonym
577 $pseudonym = $session['sau_pseudonym'];
578 }
579 //Session is granted
580 } else {
581 //Replace granted application
582 $applications[$session['au_id']] = '* '.$session['au_pseudonym'];
583
584 //Set pseudonym
585 $pseudonym = $session['au_pseudonym'].($count > 1 ? ' ['.$count.']':'');
586 }
587
588 //Add the session
589 $calendar[$Ymd]['sessions'][$session['t_id'].sprintf('%05d', $session['id'])] = [
590 'id' => $session['id'],
591 'start' => $session['start'],
592 'stop' => $session['stop'],
593 'location' => $translator->trans($session['l_short']),
594 'pseudonym' => $pseudonym,
595 'class' => $class,
596 'slot' => self::GLYPHS[$session['t_title']],
597 'slottitle' => $translator->trans($session['t_title']),
598 'weather' => $weather,
599 'weathertitle' => implode(' ', $weathertitle),
600 'applications' => $applications
601 ];
602 }
603 }
604
605 //Sort sessions
606 ksort($calendar[$Ymd]['sessions']);
607 }
608
609 //Send result
610 return $calendar;
611 }
612
613 /**
614 * Fetch sessions calendar with translated location by date period and user
615 *
616 * @param $translator The TranslatorInterface instance
617 * @param $period The date period
618 * @param $userId The user id
619 * @param $sessionId The session id
620 */
621 public function fetchUserCalendarByDatePeriod(TranslatorInterface $translator, $period, $userId = null, $sessionId = null) {
622 //Get entity manager
623 $em = $this->getEntityManager();
624
625 //Get quote strategy
626 $qs = $em->getConfiguration()->getQuoteStrategy();
627 $dp = $em->getConnection()->getDatabasePlatform();
628
629 //Get quoted table names
630 //XXX: this allow to make this code table name independent
631 $tables = [
632 'RapsysAirBundle:GroupUser' => $qs->getJoinTableName($em->getClassMetadata('RapsysAirBundle:User')->getAssociationMapping('groups'), $em->getClassMetadata('RapsysAirBundle:User'), $dp),
633 'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
634 'RapsysAirBundle:Application' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Application'), $dp),
635 'RapsysAirBundle:Group' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Group'), $dp),
636 'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp),
637 'RapsysAirBundle:Slot' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Slot'), $dp),
638 'RapsysAirBundle:User' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:User'), $dp),
639 ':afterid' => 4,
640 "\t" => '',
641 "\n" => ' '
642 ];
643
644 //Init user sql
645 $userJoinSql = $userWhereSql = '';
646
647 //When user id is set
648 if (!empty($userId)) {
649 //Add user join
650 $userJoinSql = 'JOIN RapsysAirBundle:Application AS sua ON (sua.session_id = s.id)'."\n";
651 //Add user id clause
652 $userWhereSql = "\n\t".'AND sua.user_id = :uid';
653 }
654
655 //Set the request
656 //TODO: change as_u_* in sau_*, a_u_* in au_*, etc, see request up
657 $req = <<<SQL
658 SELECT
659 s.id,
660 s.date,
661 s.rainrisk,
662 s.rainfall,
663 s.realfeel,
664 s.temperature,
665 s.locked,
666 ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS start,
667 ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS stop,
668 s.location_id AS l_id,
669 l.short AS l_short,
670 l.title AS l_title,
671 s.slot_id AS t_id,
672 t.title AS t_title,
673 s.application_id AS a_id,
674 a.user_id AS au_id,
675 au.pseudonym AS au_pseudonym,
676 GROUP_CONCAT(sa.user_id ORDER BY sa.user_id SEPARATOR "\\n") AS sau_id,
677 GROUP_CONCAT(CONCAT("- ", sau.pseudonym) ORDER BY sa.user_id SEPARATOR "\\n") AS sau_pseudonym
678 FROM RapsysAirBundle:Session AS s
679 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
680 JOIN RapsysAirBundle:Slot AS t ON (t.id = s.slot_id)
681 ${userJoinSql}LEFT JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id)
682 LEFT JOIN RapsysAirBundle:User AS au ON (au.id = a.user_id)
683 LEFT JOIN RapsysAirBundle:Application AS sa ON (sa.session_id = s.id)
684 LEFT JOIN RapsysAirBundle:User AS sau ON (sau.id = sa.user_id)
685 WHERE s.date BETWEEN :begin AND :end${userWhereSql}
686 GROUP BY s.id
687 ORDER BY NULL
688 SQL;
689
690 //Replace bundle entity name by table name
691 $req = str_replace(array_keys($tables), array_values($tables), $req);
692
693 //Get result set mapping instance
694 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
695 $rsm = new ResultSetMapping();
696
697 //Declare all fields
698 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
699 //addScalarResult($sqlColName, $resColName, $type = 'string');
700 $rsm->addScalarResult('id', 'id', 'integer')
701 ->addScalarResult('date', 'date', 'date')
702 ->addScalarResult('rainrisk', 'rainrisk', 'float')
703 ->addScalarResult('rainfall', 'rainfall', 'float')
704 ->addScalarResult('realfeel', 'realfeel', 'float')
705 ->addScalarResult('temperature', 'temperature', 'float')
706 ->addScalarResult('locked', 'locked', 'datetime')
707 ->addScalarResult('start', 'start', 'datetime')
708 ->addScalarResult('stop', 'stop', 'datetime')
709 ->addScalarResult('t_id', 't_id', 'integer')
710 ->addScalarResult('t_title', 't_title', 'string')
711 ->addScalarResult('l_id', 'l_id', 'integer')
712 ->addScalarResult('l_short', 'l_short', 'string')
713 ->addScalarResult('l_title', 'l_title', 'string')
714 ->addScalarResult('a_id', 'a_id', 'integer')
715 ->addScalarResult('au_id', 'au_id', 'integer')
716 ->addScalarResult('au_pseudonym', 'au_pseudonym', 'string')
717 //XXX: is a string because of \n separator
718 ->addScalarResult('sau_id', 'sau_id', 'string')
719 //XXX: is a string because of \n separator
720 ->addScalarResult('sau_pseudonym', 'sau_pseudonym', 'string')
721 ->addIndexByScalar('id');
722
723 //Fetch result
724 $res = $em
725 ->createNativeQuery($req, $rsm)
726 ->setParameter('begin', $period->getStartDate())
727 ->setParameter('end', $period->getEndDate())
728 ->setParameter('uid', $userId)
729 ->getResult();
730
731 //Init calendar
732 $calendar = [];
733
734 //Init month
735 $month = null;
736
737 //Iterate on each day
738 foreach($period as $date) {
739 //Init day in calendar
740 $calendar[$Ymd = $date->format('Ymd')] = [
741 'title' => $translator->trans($date->format('l')).' '.$date->format('d'),
742 'class' => [],
743 'sessions' => []
744 ];
745
746 //Detect month change
747 if ($month != $date->format('m')) {
748 $month = $date->format('m');
749 //Append month for first day of month
750 //XXX: except if today to avoid double add
751 if ($date->format('U') != strtotime('today')) {
752 $calendar[$Ymd]['title'] .= '/'.$month;
753 }
754 }
755 //Deal with today
756 if ($date->format('U') == ($today = strtotime('today'))) {
757 $calendar[$Ymd]['title'] .= '/'.$month;
758 $calendar[$Ymd]['current'] = true;
759 $calendar[$Ymd]['class'][] = 'current';
760 }
761 //Disable passed days
762 if ($date->format('U') < $today) {
763 $calendar[$Ymd]['disabled'] = true;
764 $calendar[$Ymd]['class'][] = 'disabled';
765 }
766 //Set next month days
767 if ($date->format('m') > date('m')) {
768 $calendar[$Ymd]['next'] = true;
769 #$calendar[$Ymd]['class'][] = 'next';
770 }
771
772 //Detect sunday
773 if ($date->format('w') == 0) {
774 $calendar[$Ymd]['class'][] = 'sunday';
775 }
776
777 //Iterate on each session to find the one of the day
778 foreach($res as $session) {
779 if (($sessionYmd = $session['date']->format('Ymd')) == $Ymd) {
780 //Count number of application
781 $count = count(explode("\n", $session['sau_id']));
782
783 //Compute classes
784 $class = [];
785 if (!empty($session['a_id'])) {
786 $applications = [ $session['au_id'] => $session['au_pseudonym'] ];
787 if ($session['au_id'] == $userId) {
788 $class[] = 'granted';
789 } else {
790 $class[] = 'disputed';
791 }
792 } elseif ($count > 1) {
793 $class[] = 'disputed';
794 } elseif (!empty($session['locked'])) {
795 $class[] = 'locked';
796 } else {
797 $class[] = 'pending';
798 }
799
800 if ($sessionId == $session['id']) {
801 $class[] = 'highlight';
802 }
803
804 //Set temperature
805 //XXX: realfeel may be null, temperature should not
806 $temperature = $session['realfeel'] !== null ? $session['realfeel'] : $session['temperature'];
807
808 //Compute weather
809 //XXX: rainfall may be null
810 if ($session['rainrisk'] > 0.50 || $session['rainfall'] > 2) {
811 $weather = self::GLYPHS['Stormy'];
812 } elseif ($session['rainrisk'] > 0.40 || $session['rainfall'] > 1) {
813 $weather = self::GLYPHS['Rainy'];
814 } elseif ($temperature > 24) {
815 $weather = self::GLYPHS['Cleary'];
816 } elseif ($temperature > 17) {
817 $weather = self::GLYPHS['Sunny'];
818 } elseif ($temperature > 10) {
819 $weather = self::GLYPHS['Cloudy'];
820 } elseif ($temperature !== null) {
821 $weather = self::GLYPHS['Winty'];
822 } else {
823 $weather = null;
824 }
825
826 //Init weathertitle
827 $weathertitle = [];
828
829 //Check if realfeel is available
830 if ($session['realfeel'] !== null) {
831 $weathertitle[] = $session['realfeel'].'Β°R';
832 }
833
834 //Check if temperature is available
835 if ($session['temperature'] !== null) {
836 $weathertitle[] = $session['temperature'].'Β°C';
837 }
838
839 //Check if rainrisk is available
840 if ($session['rainrisk'] !== null) {
841 $weathertitle[] = ($session['rainrisk']*100).'%';
842 }
843
844 //Check if rainfall is available
845 if ($session['rainfall'] !== null) {
846 $weathertitle[] = $session['rainfall'].'mm';
847 }
848
849 //Set applications
850 $applications = [
851 0 => $translator->trans($session['t_title']).' '.$translator->trans('at '.$session['l_title']).$translator->trans(':')
852 ];
853
854 //Fetch pseudonyms from session applications
855 $applications += array_combine(explode("\n", $session['sau_id']), array_map(function ($v) {return '- '.$v;}, explode("\n", $session['sau_pseudonym'])));
856
857 //Set pseudonym
858 $pseudonym = null;
859
860 //Check that session is not granted
861 if (empty($session['a_id'])) {
862 //With location id and unique application
863 if ($count == 1) {
864 //Set unique application pseudonym
865 $pseudonym = $session['sau_pseudonym'];
866 }
867 //Session is granted
868 } else {
869 //Replace granted application
870 $applications[$session['au_id']] = '* '.$session['au_pseudonym'];
871
872 //Set pseudonym
873 $pseudonym = $session['au_pseudonym'].($count > 1 ? ' ['.$count.']':'');
874 }
875
876 //Set title
877 $title = $translator->trans($session['l_title']).($count > 1 ? ' ['.$count.']':'');
878
879 //Add the session
880 $calendar[$Ymd]['sessions'][$session['t_id'].sprintf('%02d', $session['l_id'])] = [
881 'id' => $session['id'],
882 'start' => $session['start'],
883 'stop' => $session['stop'],
884 'location' => $translator->trans($session['l_short']),
885 'pseudonym' => $pseudonym,
886 'class' => $class,
887 'slot' => self::GLYPHS[$session['t_title']],
888 'slottitle' => $translator->trans($session['t_title']),
889 'weather' => $weather,
890 'weathertitle' => implode(' ', $weathertitle),
891 'applications' => $applications
892 ];
893 }
894 }
895
896 //Sort sessions
897 ksort($calendar[$Ymd]['sessions']);
898 }
899
900 //Send result
901 return $calendar;
902 }
903
904 /**
905 * Find all session pending hourly weather
906 *
907 * @return array<Session> The sessions to update
908 */
909 public function findAllPendingHourlyWeather() {
910 //Get entity manager
911 $em = $this->getEntityManager();
912
913 //Get quote strategy
914 $qs = $em->getConfiguration()->getQuoteStrategy();
915 $dp = $em->getConnection()->getDatabasePlatform();
916
917 //Get quoted table names
918 //XXX: this allow to make this code table name independent
919 $tables = [
920 'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
921 'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp),
922 //Accuweather
923 ':accuhourly' => self::ACCUWEATHER_HOURLY,
924 //Delay
925 ':afterid' => 4,
926 "\t" => '',
927 "\n" => ' '
928 ];
929
930 //Select all sessions starting and stopping in the next 3 days
931 //XXX: select session starting after now and stopping before date(now)+3d as accuweather only provide hourly data for the next 3 days (INTERVAL 3 DAY)
932 $req = <<<SQL
933 SELECT s.id, s.slot_id, s.location_id, s.date, s.begin, s.length, s.rainfall, s.rainrisk, s.realfeel, s.realfeelmin, s.realfeelmax, s.temperature, s.temperaturemin, s.temperaturemax, l.zipcode
934 FROM RapsysAirBundle:Session AS s
935 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
936 WHERE ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) >= NOW() AND ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) < DATE(ADDDATE(NOW(), INTERVAL :accuhourly DAY))
937 SQL;
938
939 //Replace bundle entity name by table name
940 $req = str_replace(array_keys($tables), array_values($tables), $req);
941
942 //Get result set mapping instance
943 $rsm = new ResultSetMapping();
944
945 //Declare all fields
946 $rsm
947 ->addEntityResult('RapsysAirBundle:Session', 's')
948 ->addFieldResult('s', 'id', 'id')
949 ->addFieldResult('s', 'date', 'date')
950 ->addFieldResult('s', 'begin', 'begin')
951 ->addFieldResult('s', 'length', 'length')
952 ->addFieldResult('s', 'rainfall', 'rainfall')
953 ->addFieldResult('s', 'rainrisk', 'rainrisk')
954 ->addFieldResult('s', 'realfeel', 'realfeel')
955 ->addFieldResult('s', 'realfeelmin', 'realfeelmin')
956 ->addFieldResult('s', 'realfeelmax', 'realfeelmax')
957 ->addFieldResult('s', 'temperature', 'temperature')
958 ->addFieldResult('s', 'temperaturemin', 'temperaturemin')
959 ->addFieldResult('s', 'temperaturemax', 'temperaturemax')
960 ->addJoinedEntityResult('RapsysAirBundle:Slot', 'o', 's', 'slot')
961 ->addFieldResult('o', 'slot_id', 'id')
962 ->addJoinedEntityResult('RapsysAirBundle:Location', 'l', 's', 'location')
963 ->addFieldResult('l', 'location_id', 'id')
964 ->addFieldResult('l', 'zipcode', 'zipcode')
965 ->addIndexBy('s', 'id');
966
967 //Send result
968 return $em
969 ->createNativeQuery($req, $rsm)
970 ->getResult();
971 }
972
973 /**
974 * Find all session pending daily weather
975 *
976 * @return array<Session> The sessions to update
977 */
978 public function findAllPendingDailyWeather() {
979 //Get entity manager
980 $em = $this->getEntityManager();
981
982 //Get quote strategy
983 $qs = $em->getConfiguration()->getQuoteStrategy();
984 $dp = $em->getConnection()->getDatabasePlatform();
985
986 //Get quoted table names
987 //XXX: this allow to make this code table name independent
988 $tables = [
989 'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
990 'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp),
991 //Accuweather
992 ':accudaily' => self::ACCUWEATHER_DAILY,
993 ':accuhourly' => self::ACCUWEATHER_HOURLY,
994 //Delay
995 ':afterid' => 4,
996 "\t" => '',
997 "\n" => ' '
998 ];
999
1000 //Select all sessions stopping after next 3 days
1001 //XXX: select session stopping after or equal date(now)+3d as accuweather only provide hourly data for the next 3 days (INTERVAL 3 DAY)
1002 $req = <<<SQL
1003 SELECT s.id, s.slot_id, s.location_id, s.date, s.begin, s.length, s.rainfall, s.rainrisk, s.realfeel, s.realfeelmin, s.realfeelmax, s.temperature, s.temperaturemin, s.temperaturemax, l.zipcode
1004 FROM RapsysAirBundle:Session AS s
1005 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
1006 WHERE ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) >= DATE(ADDDATE(NOW(), INTERVAL :accuhourly DAY)) AND ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) < DATE(ADDDATE(NOW(), INTERVAL :accudaily DAY))
1007 SQL;
1008
1009 //Replace bundle entity name by table name
1010 $req = str_replace(array_keys($tables), array_values($tables), $req);
1011
1012 //Get result set mapping instance
1013 $rsm = new ResultSetMapping();
1014
1015 //Declare all fields
1016 $rsm
1017 ->addEntityResult('RapsysAirBundle:Session', 's')
1018 ->addFieldResult('s', 'id', 'id')
1019 ->addFieldResult('s', 'date', 'date')
1020 ->addFieldResult('s', 'begin', 'begin')
1021 ->addFieldResult('s', 'length', 'length')
1022 ->addFieldResult('s', 'rainfall', 'rainfall')
1023 ->addFieldResult('s', 'rainrisk', 'rainrisk')
1024 ->addFieldResult('s', 'realfeel', 'realfeel')
1025 ->addFieldResult('s', 'realfeelmin', 'realfeelmin')
1026 ->addFieldResult('s', 'realfeelmax', 'realfeelmax')
1027 ->addFieldResult('s', 'temperature', 'temperature')
1028 ->addFieldResult('s', 'temperaturemin', 'temperaturemin')
1029 ->addFieldResult('s', 'temperaturemax', 'temperaturemax')
1030 ->addJoinedEntityResult('RapsysAirBundle:Slot', 'o', 's', 'slot')
1031 ->addFieldResult('o', 'slot_id', 'id')
1032 ->addJoinedEntityResult('RapsysAirBundle:Location', 'l', 's', 'location')
1033 ->addFieldResult('l', 'location_id', 'id')
1034 ->addFieldResult('l', 'zipcode', 'zipcode')
1035 ->addIndexBy('s', 'id');
1036
1037 //Send result
1038 return $em
1039 ->createNativeQuery($req, $rsm)
1040 ->getResult();
1041 }
1042
1043 /**
1044 * Find every session pending application
1045 *
1046 * @return array<Session> The sessions to update
1047 */
1048 public function findAllPendingApplication() {
1049 //Get entity manager
1050 $em = $this->getEntityManager();
1051
1052 //Get quote strategy
1053 $qs = $em->getConfiguration()->getQuoteStrategy();
1054 $dp = $em->getConnection()->getDatabasePlatform();
1055
1056 //Get quoted table names
1057 //XXX: this allow to make this code table name independent
1058 $tables = [
1059 'RapsysAirBundle:Application' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Application'), $dp),
1060 'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
1061 //Delay
1062 ':regulardelay' => self::REGULAR_DELAY * 24 * 3600,
1063 ':seniordelay' => self::SENIOR_DELAY * 24 * 3600,
1064 //Slot
1065 ':afterid' => 4,
1066 "\t" => '',
1067 "\n" => ' '
1068 ];
1069
1070 //Select all sessions not locked without application or canceled application within attribution period
1071 //XXX: DIFF(start, now) <= IF(DIFF(start, created) <= SENIOR_DELAY in DAY, DIFF(start, created) * 3 / 4, SENIOR_DELAY)
1072 //TODO: remonter les donnΓ©es pour le mail ?
1073 $req =<<<SQL
1074 SELECT s.id
1075 FROM RapsysAirBundle:Session as s
1076 LEFT JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id AND a.canceled IS NULL)
1077 JOIN RapsysAirBundle:Application AS a2 ON (a2.session_id = s.id AND a2.canceled IS NULL)
1078 WHERE s.locked IS NULL AND a.id IS NULL AND
1079 TIME_TO_SEC(TIMEDIFF(@dt_start := ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY), NOW())) <= IF(
1080 TIME_TO_SEC(@td_sc := TIMEDIFF(@dt_start, s.created)) <= :seniordelay,
1081 ROUND(TIME_TO_SEC(@td_sc) * :regulardelay / :seniordelay),
1082 :seniordelay
1083 )
1084 GROUP BY s.id
1085 ORDER BY @dt_start ASC, s.created ASC
1086 SQL;
1087
1088
1089 //Replace bundle entity name by table name
1090 $req = str_replace(array_keys($tables), array_values($tables), $req);
1091
1092 //Get result set mapping instance
1093 $rsm = new ResultSetMapping();
1094
1095 //Declare all fields
1096 $rsm
1097 ->addEntityResult('RapsysAirBundle:Session', 's')
1098 ->addFieldResult('s', 'id', 'id')
1099 ->addIndexBy('s', 'id');
1100
1101 //Send result
1102 return $em
1103 ->createNativeQuery($req, $rsm)
1104 ->getResult();
1105 }
1106
1107 /**
1108 * Fetch session best application by session id
1109 *
1110 * @param int $id The session id
1111 * @return Application|null The application or null
1112 */
1113 public function findBestApplicationById($id) {
1114 //Get entity manager
1115 $em = $this->getEntityManager();
1116
1117 //Get quote strategy
1118 $qs = $em->getConfiguration()->getQuoteStrategy();
1119 $dp = $em->getConnection()->getDatabasePlatform();
1120
1121 //Get quoted table names
1122 //XXX: this allow to make this code table name independent
1123 $tables = [
1124 'RapsysAirBundle:Application' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Application'), $dp),
1125 'RapsysAirBundle:GroupUser' => $qs->getJoinTableName($em->getClassMetadata('RapsysAirBundle:User')->getAssociationMapping('groups'), $em->getClassMetadata('RapsysAirBundle:User'), $dp),
1126 'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp),
1127 'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
1128 //XXX: Set limit used to workaround mariadb subselect optimization
1129 ':limit' => PHP_INT_MAX,
1130 //Delay
1131 ':guestdelay' => self::GUEST_DELAY * 24 * 3600,
1132 ':regulardelay' => self::REGULAR_DELAY * 24 * 3600,
1133 ':seniordelay' => self::SENIOR_DELAY * 24 * 3600,
1134 //Group
1135 ':guestid' => 2,
1136 ':regularid' => 3,
1137 ':seniorid' => 4,
1138 //Slot
1139 ':afternoonid' => 2,
1140 ':eveningid' => 3,
1141 ':afterid' => 4,
1142 //XXX: days since last session after which guest regain normal priority
1143 ':guestwait' => 30,
1144 //XXX: session count until considered at regular delay
1145 ':scount' => 5,
1146 //XXX: pn_ratio over which considered at regular delay
1147 ':pnratio' => 1,
1148 //XXX: tr_ratio diff over which considered at regular delay
1149 ':trdiff' => 5,
1150 "\t" => '',
1151 "\n" => ' '
1152 ];
1153
1154 /**
1155 * Query session applications ranked by location score, global score, created and user_id
1156 *
1157 * @xxx guest (or less) with application on location within 30 day are only considered within guestdelay
1158 *
1159 * @xxx regular (or less) premium application on hotspot are only considered within regulardelay
1160 *
1161 * @xxx senior (or less) with 5 or less session on location are only considered within seniordelay
1162 *
1163 * @xxx senior (or less) with l_pn_ratio >= 1 are only considered within seniordelay
1164 *
1165 * @xxx senior (or less) with l_tr_ratio >= (o_tr_ratio + 5) are only considered within seniordelay
1166 *
1167 * @xxx only consider session within one year (may be unaccurate by the day with after session)
1168 *
1169 * @xxx rainfall may not be accessible for previous session and other session at d-4 (only at d-2)
1170 *
1171 * @todo ??? feedback the data to inform the rejected users ???
1172 */
1173 $req = <<<SQL
1174 SELECT e.id, e.l_score AS score
1175 FROM (
1176 SELECT
1177 d.id,
1178 d.user_id,
1179 d.l_count,
1180 d.l_score,
1181 d.l_tr_ratio,
1182 d.l_pn_ratio,
1183 d.l_previous,
1184 d.g_score,
1185 d.o_tr_ratio,
1186 MAX(gu.group_id) AS group_id,
1187 d.remaining,
1188 d.premium,
1189 d.hotspot,
1190 d.created
1191 FROM (
1192 SELECT
1193 c.id,
1194 c.user_id,
1195 c.l_count,
1196 c.l_score,
1197 c.l_tr_ratio,
1198 c.l_pn_ratio,
1199 c.l_previous,
1200 c.g_score,
1201 AVG(IF(a4.id IS NOT NULL AND s4.temperature IS NOT NULL AND s4.rainfall IS NOT NULL, s4.temperature/(1+s4.rainfall), NULL)) AS o_tr_ratio,
1202 c.remaining,
1203 c.premium,
1204 c.hotspot,
1205 c.created
1206 FROM (
1207 SELECT
1208 b.id,
1209 b.user_id,
1210 b.session_id,
1211 b.date,
1212 b.location_id,
1213 b.l_count,
1214 b.l_score,
1215 b.l_tr_ratio,
1216 b.l_pn_ratio,
1217 b.l_previous,
1218 SUM(IF(a3.id IS NOT NULL, 1/ABS(DATEDIFF(ADDDATE(b.date, INTERVAL IF(b.slot_id = :afterid, 1, 0) DAY), ADDDATE(s3.date, INTERVAL IF(s3.slot_id = :afterid, 1, 0) DAY))), 0)) AS g_score,
1219 b.remaining,
1220 b.premium,
1221 b.hotspot,
1222 b.created
1223 FROM (
1224 SELECT
1225 a.id,
1226 a.user_id,
1227 s.id AS session_id,
1228 s.date AS date,
1229 s.slot_id,
1230 s.location_id,
1231 COUNT(a2.id) AS l_count,
1232 SUM(IF(a2.id IS NOT NULL, 1/ABS(DATEDIFF(ADDDATE(s.date, INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY), ADDDATE(s2.date, INTERVAL IF(s2.slot_id = :afterid, 1, 0) DAY))), 0)) AS l_score,
1233 AVG(IF(a2.id IS NOT NULL AND s2.temperature IS NOT NULL AND s2.rainfall IS NOT NULL, s2.temperature/(1+s2.rainfall), NULL)) AS l_tr_ratio,
1234 (SUM(IF(a2.id IS NOT NULL AND s2.premium = 1, 1, 0))+1)/(SUM(IF(a2.id IS NOT NULL AND s2.premium = 0, 1, 0))+1) AS l_pn_ratio,
1235 MIN(IF(a2.id IS NOT NULL, DATEDIFF(ADDDATE(s.date, INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY), ADDDATE(s2.date, INTERVAL IF(s2.slot_id = :afterid, 1, 0) DAY)), NULL)) AS l_previous,
1236 TIME_TO_SEC(TIMEDIFF(ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY), NOW())) AS remaining,
1237 s.premium,
1238 l.hotspot,
1239 a.created
1240 FROM RapsysAirBundle:Session AS s
1241 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
1242 JOIN RapsysAirBundle:Application AS a ON (a.session_id = s.id AND a.canceled IS NULL)
1243 LEFT JOIN RapsysAirBundle:Session AS s2 ON (s2.id != s.id AND s2.location_id = s.location_id AND s2.slot_id IN (:afternoonid, :eveningid) AND s2.application_id IS NOT NULL AND s2.locked IS NULL AND s2.date > s.date - INTERVAL 1 YEAR)
1244 LEFT JOIN RapsysAirBundle:Application AS a2 ON (a2.id = s2.application_id AND a2.user_id = a.user_id AND (a2.canceled IS NULL OR TIMESTAMPDIFF(DAY, a2.canceled, ADDDATE(ADDTIME(s2.date, s2.begin), INTERVAL IF(s2.slot_id = :afterid, 1, 0) DAY)) < 1))
1245 WHERE s.id = :sid
1246 GROUP BY a.id
1247 ORDER BY NULL
1248 LIMIT 0, :limit
1249 ) AS b
1250 LEFT JOIN RapsysAirBundle:Session AS s3 ON (s3.id != b.session_id AND s3.application_id IS NOT NULL AND s3.locked IS NULL AND s3.date > b.date - INTERVAL 1 YEAR)
1251 LEFT JOIN RapsysAirBundle:Application AS a3 ON (a3.id = s3.application_id AND a3.user_id = b.user_id AND (a3.canceled IS NULL OR TIMESTAMPDIFF(DAY, a3.canceled, ADDDATE(ADDTIME(s3.date, s3.begin), INTERVAL IF(s3.slot_id = :afterid, 1, 0) DAY)) < 1))
1252 GROUP BY b.id
1253 ORDER BY NULL
1254 LIMIT 0, :limit
1255 ) AS c
1256 LEFT JOIN RapsysAirBundle:Session AS s4 ON (s4.id != c.session_id AND s4.location_id = c.location_id AND s4.application_id IS NOT NULL AND s4.locked IS NULL AND s4.date > c.date - INTERVAL 1 YEAR)
1257 LEFT JOIN RapsysAirBundle:Application AS a4 ON (a4.id = s4.application_id AND a4.user_id != c.user_id AND (a4.canceled IS NULL OR TIMESTAMPDIFF(DAY, a4.canceled, ADDDATE(ADDTIME(s4.date, s4.begin), INTERVAL IF(s4.slot_id = :afterid, 1, 0) DAY)) < 1))
1258 GROUP BY c.id
1259 ORDER BY NULL
1260 LIMIT 0, :limit
1261 ) AS d
1262 LEFT JOIN RapsysAirBundle:GroupUser AS gu ON (gu.user_id = d.user_id)
1263 GROUP BY d.id
1264 LIMIT 0, :limit
1265 ) AS e
1266 WHERE
1267 IF(e.group_id <= :guestid AND e.l_previous <= :guestwait, e.remaining <= :guestdelay, 1) AND
1268 IF(e.group_id <= :regularid AND e.premium = 1 AND e.hotspot = 1, e.remaining <= :regulardelay, 1) AND
1269 IF(e.group_id <= :seniorid AND e.l_count <= :scount, e.remaining <= :regulardelay, 1) AND
1270 IF(e.group_id <= :seniorid AND e.l_pn_ratio >= :pnratio, e.remaining <= :regulardelay, 1) AND
1271 IF(e.group_id <= :seniorid AND e.l_tr_ratio >= (e.o_tr_ratio + :trdiff), e.remaining <= :regulardelay, 1)
1272 ORDER BY e.l_score ASC, e.g_score ASC, e.created ASC, e.user_id ASC
1273 SQL;
1274
1275 //Replace bundle entity name by table name
1276 $req = str_replace(array_keys($tables), array_values($tables), $req);
1277
1278 //Set update request
1279 $upreq = 'UPDATE RapsysAirBundle:Application SET score = :score, updated = NOW() WHERE id = :id';
1280
1281 //Replace bundle entity name by table name
1282 $upreq = str_replace(array_keys($tables), array_values($tables), $upreq);
1283
1284 //Get result set mapping instance
1285 $rsm = new ResultSetMapping();
1286
1287 //Declare all fields
1288 $rsm
1289 ->addEntityResult('RapsysAirBundle:Application', 'a')
1290 ->addFieldResult('a', 'id', 'id')
1291 ->addFieldResult('a', 'score', 'score')
1292 ->addIndexBy('a', 'id');
1293
1294 //Get result
1295 //XXX: setting limit in subqueries is required to prevent mariadb optimisation
1296 $applications = $em
1297 ->createNativeQuery($req, $rsm)
1298 ->setParameter('sid', $id)
1299 //XXX: removed, we update score before returning best candidate
1300 //->getOneOrNullResult(Query::HYDRATE_SINGLE_SCALAR);
1301 ->getResult();
1302
1303 //Init ret
1304 $ret = null;
1305
1306 //Update score
1307 foreach($applications as $application) {
1308 //Check if we already saved best candidate
1309 if ($ret === null) {
1310 //Return first application
1311 $ret = $application;
1312 }
1313
1314 //Update application updated field
1315 //XXX: updated field is not modified for user with bad behaviour as application is not retrieved until delay is reached
1316 $em->getConnection()->executeUpdate($upreq, ['id' => $application->getId(), 'score' => $application->getScore()], ['id' => Type::INTEGER, 'score' => Type::FLOAT]);
1317 }
1318
1319 //Return best ranked application
1320 return $ret;
1321 }
1322 }