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