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