]> RaphaΓ«l G. Git Repositories - airbundle/blob - Repository/SessionRepository.php
Add support for bundle named entity
[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 sessions by date period
147 *
148 * @param $period The date period
149 * @param $locale The locale
150 */
151 public function fetchAllByDatePeriod($period, $locale = null) {
152 //Get entity manager
153 $em = $this->getEntityManager();
154
155 //Get quote strategy
156 $qs = $em->getConfiguration()->getQuoteStrategy();
157 $dp = $em->getConnection()->getDatabasePlatform();
158
159 //Get quoted table names
160 //XXX: this allow to make this code table name independent
161 $tables = [
162 'RapsysAirBundle:Application' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Application'), $dp),
163 'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp),
164 'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
165 'RapsysAirBundle:Snippet' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Snippet'), $dp),
166 'RapsysAirBundle:User' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:User'), $dp),
167 ':afterid' => 4,
168 "\t" => '',
169 "\n" => ' '
170 ];
171
172 //Set the request
173 //TODO: exclude opera and others ?
174 $req = <<<SQL
175 SELECT
176 s.id,
177 s.date,
178 s.locked,
179 s.updated,
180 ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS start,
181 ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS stop,
182 s.location_id AS l_id,
183 l.address AS l_address,
184 l.zipcode AS l_zipcode,
185 l.city AS l_city,
186 l.title AS l_title,
187 l.latitude AS l_latitude,
188 l.longitude AS l_longitude,
189 s.application_id AS a_id,
190 a.canceled AS a_canceled,
191 a.user_id AS au_id,
192 au.forename AS au_forename,
193 au.pseudonym AS au_pseudonym,
194 p.id AS p_id,
195 p.description AS p_description,
196 p.class AS p_class,
197 p.short AS p_short,
198 p.rate AS p_rate,
199 p.contact AS p_contact,
200 p.donate AS p_donate,
201 p.link AS p_link,
202 p.profile AS p_profile
203 FROM RapsysAirBundle:Session AS s
204 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
205 JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id)
206 JOIN RapsysAirBundle:User AS au ON (au.id = a.user_id)
207 LEFT JOIN RapsysAirBundle:Snippet AS p ON (p.location_id = s.location_id AND p.user_id = a.user_id AND p.locale = :locale)
208 WHERE s.date BETWEEN :begin AND :end
209 ORDER BY NULL
210 SQL;
211
212 //Replace bundle entity name by table name
213 $req = str_replace(array_keys($tables), array_values($tables), $req);
214
215 //Get result set mapping instance
216 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
217 $rsm = new ResultSetMapping();
218
219 //Declare all fields
220 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
221 //addScalarResult($sqlColName, $resColName, $type = 'string');
222 $rsm->addScalarResult('id', 'id', 'integer')
223 ->addScalarResult('date', 'date', 'date')
224 ->addScalarResult('locked', 'locked', 'datetime')
225 ->addScalarResult('updated', 'updated', 'datetime')
226 ->addScalarResult('start', 'start', 'datetime')
227 ->addScalarResult('stop', 'stop', 'datetime')
228 ->addScalarResult('l_id', 'l_id', 'integer')
229 ->addScalarResult('l_address', 'l_address', 'string')
230 ->addScalarResult('l_zipcode', 'l_zipcode', 'string')
231 ->addScalarResult('l_city', 'l_city', 'string')
232 ->addScalarResult('l_latitude', 'l_latitude', 'float')
233 ->addScalarResult('l_longitude', 'l_longitude', 'float')
234 ->addScalarResult('l_title', 'l_title', 'string')
235 ->addScalarResult('t_id', 't_id', 'integer')
236 ->addScalarResult('t_title', 't_title', 'string')
237 ->addScalarResult('a_id', 'a_id', 'integer')
238 ->addScalarResult('a_canceled', 'a_canceled', 'datetime')
239 ->addScalarResult('au_id', 'au_id', 'integer')
240 ->addScalarResult('au_forename', 'au_forename', 'string')
241 ->addScalarResult('au_pseudonym', 'au_pseudonym', 'string')
242 ->addScalarResult('p_id', 'p_id', 'integer')
243 ->addScalarResult('p_description', 'p_description', 'string')
244 ->addScalarResult('p_class', 'p_class', 'string')
245 ->addScalarResult('p_short', 'p_short', 'string')
246 ->addScalarResult('p_rate', 'p_rate', 'integer')
247 ->addScalarResult('p_contact', 'p_contact', 'string')
248 ->addScalarResult('p_donate', 'p_donate', 'string')
249 ->addScalarResult('p_link', 'p_link', 'string')
250 ->addScalarResult('p_profile', 'p_profile', 'string')
251 ->addIndexByScalar('id');
252
253 //Fetch result
254 $res = $em
255 ->createNativeQuery($req, $rsm)
256 ->setParameter('begin', $period->getStartDate())
257 ->setParameter('end', $period->getEndDate())
258 ->setParameter('locale', $locale);
259
260 //Return result
261 return $res->getResult();
262 }
263
264 /**
265 * Fetch session by id
266 *
267 * @param $id The session id
268 * @param $locale The locale
269 * @return array The session data
270 */
271 public function fetchOneById($id, $locale = null) {
272 //Get entity manager
273 $em = $this->getEntityManager();
274
275 //Get quote strategy
276 $qs = $em->getConfiguration()->getQuoteStrategy();
277 $dp = $em->getConnection()->getDatabasePlatform();
278
279 //Get quoted table names
280 //XXX: this allow to make this code table name independent
281 $tables = [
282 'RapsysAirBundle:Application' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Application'), $dp),
283 'RapsysAirBundle:Group' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Group'), $dp),
284 'RapsysAirBundle:GroupUser' => $qs->getJoinTableName($em->getClassMetadata('RapsysAirBundle:User')->getAssociationMapping('groups'), $em->getClassMetadata('RapsysAirBundle:User'), $dp),
285 'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp),
286 'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
287 'RapsysAirBundle:Snippet' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Snippet'), $dp),
288 'RapsysAirBundle:Slot' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Slot'), $dp),
289 'RapsysAirBundle:User' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:User'), $dp),
290 ':afterid' => 4,
291 "\t" => '',
292 "\n" => ' '
293 ];
294
295 //Set the request
296 //TODO: compute scores ?
297 //TODO: compute delivery date ? (J-3/J-4 ?)
298 $req =<<<SQL
299 SELECT
300 s.id,
301 s.date,
302 s.begin,
303 ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS start,
304 s.length,
305 ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS stop,
306 s.rainfall,
307 s.rainrisk,
308 s.realfeel,
309 s.realfeelmin,
310 s.realfeelmax,
311 s.temperature,
312 s.temperaturemin,
313 s.temperaturemax,
314 s.locked,
315 s.created,
316 s.updated,
317 s.location_id AS l_id,
318 l.title AS l_title,
319 l.address AS l_address,
320 l.zipcode AS l_zipcode,
321 l.city AS l_city,
322 l.latitude AS l_latitude,
323 l.longitude AS l_longitude,
324 l.updated AS l_updated,
325 s.slot_id AS t_id,
326 t.title AS t_title,
327 t.updated AS t_updated,
328 s.application_id AS a_id,
329 a.canceled AS a_canceled,
330 a.user_id AS au_id,
331 au.pseudonym AS au_pseudonym,
332 p.id AS p_id,
333 p.description AS p_description,
334 p.class AS p_class,
335 p.contact AS p_contact,
336 p.donate AS p_donate,
337 p.link AS p_link,
338 p.profile AS p_profile,
339 p.rate AS p_rate,
340 p.hat AS p_hat,
341 p.updated AS p_updated,
342 GROUP_CONCAT(sa.id ORDER BY sa.user_id SEPARATOR "\\n") AS sa_id,
343 GROUP_CONCAT(IFNULL(sa.score, 'NULL') ORDER BY sa.user_id SEPARATOR "\\n") AS sa_score,
344 GROUP_CONCAT(sa.created ORDER BY sa.user_id SEPARATOR "\\n") AS sa_created,
345 GROUP_CONCAT(sa.updated ORDER BY sa.user_id SEPARATOR "\\n") AS sa_updated,
346 GROUP_CONCAT(IFNULL(sa.canceled, 'NULL') ORDER BY sa.user_id SEPARATOR "\\n") AS sa_canceled,
347 GROUP_CONCAT(sa.user_id ORDER BY sa.user_id SEPARATOR "\\n") AS sau_id,
348 GROUP_CONCAT(sau.pseudonym ORDER BY sa.user_id SEPARATOR "\\n") AS sau_pseudonym
349 FROM RapsysAirBundle:Session AS s
350 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
351 JOIN RapsysAirBundle:Slot AS t ON (t.id = s.slot_id)
352 LEFT JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id)
353 LEFT JOIN RapsysAirBundle:User AS au ON (au.id = a.user_id)
354 LEFT JOIN RapsysAirBundle:Snippet AS p ON (p.location_id = s.location_id AND p.user_id = a.user_id AND p.locale = :locale)
355 LEFT JOIN RapsysAirBundle:Application AS sa ON (sa.session_id = s.id)
356 LEFT JOIN RapsysAirBundle:User AS sau ON (sau.id = sa.user_id)
357 WHERE s.id = :sid
358 GROUP BY s.id
359 ORDER BY NULL
360 SQL;
361
362 //Replace bundle entity name by table name
363 $req = str_replace(array_keys($tables), array_values($tables), $req);
364
365 //Get result set mapping instance
366 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
367 $rsm = new ResultSetMapping();
368
369 //Declare all fields
370 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
371 $rsm->addScalarResult('id', 'id', 'integer')
372 ->addScalarResult('date', 'date', 'date')
373 ->addScalarResult('begin', 'begin', 'time')
374 ->addScalarResult('start', 'start', 'datetime')
375 ->addScalarResult('length', 'length', 'time')
376 ->addScalarResult('stop', 'stop', 'datetime')
377 ->addScalarResult('rainfall', 'rainfall', 'float')
378 ->addScalarResult('rainrisk', 'rainrisk', 'float')
379 ->addScalarResult('realfeel', 'realfeel', 'float')
380 ->addScalarResult('realfeelmin', 'realfeelmin', 'float')
381 ->addScalarResult('realfeelmax', 'realfeelmax', 'float')
382 ->addScalarResult('temperature', 'temperature', 'float')
383 ->addScalarResult('temperaturemin', 'temperaturemin', 'float')
384 ->addScalarResult('temperaturemax', 'temperaturemax', 'float')
385 ->addScalarResult('locked', 'locked', 'datetime')
386 ->addScalarResult('created', 'created', 'datetime')
387 ->addScalarResult('updated', 'updated', 'datetime')
388 ->addScalarResult('l_id', 'l_id', 'integer')
389 ->addScalarResult('l_title', 'l_title', 'string')
390 ->addScalarResult('l_address', 'l_address', 'string')
391 ->addScalarResult('l_zipcode', 'l_zipcode', 'string')
392 ->addScalarResult('l_city', 'l_city', 'string')
393 ->addScalarResult('l_latitude', 'l_latitude', 'float')
394 ->addScalarResult('l_longitude', 'l_longitude', 'float')
395 ->addScalarResult('l_updated', 'l_updated', 'datetime')
396 ->addScalarResult('t_id', 't_id', 'integer')
397 ->addScalarResult('t_title', 't_title', 'string')
398 ->addScalarResult('t_updated', 't_updated', 'datetime')
399 ->addScalarResult('a_id', 'a_id', 'integer')
400 ->addScalarResult('a_canceled', 'a_canceled', 'datetime')
401 ->addScalarResult('au_id', 'au_id', 'integer')
402 ->addScalarResult('au_pseudonym', 'au_pseudonym', 'string')
403 ->addScalarResult('p_id', 'p_id', 'integer')
404 ->addScalarResult('p_description', 'p_description', 'text')
405 ->addScalarResult('p_class', 'p_class', 'text')
406 ->addScalarResult('p_contact', 'p_contact', 'text')
407 ->addScalarResult('p_donate', 'p_donate', 'text')
408 ->addScalarResult('p_link', 'p_link', 'text')
409 ->addScalarResult('p_profile', 'p_profile', 'text')
410 ->addScalarResult('p_rate', 'p_rate', 'integer')
411 ->addScalarResult('p_hat', 'p_hat', 'boolean')
412 ->addScalarResult('p_updated', 'p_updated', 'datetime')
413 //XXX: is a string because of \n separator
414 ->addScalarResult('sa_id', 'sa_id', 'string')
415 //XXX: is a string because of \n separator
416 ->addScalarResult('sa_score', 'sa_score', 'string')
417 //XXX: is a string because of \n separator
418 ->addScalarResult('sa_created', 'sa_created', 'string')
419 //XXX: is a string because of \n separator
420 ->addScalarResult('sa_updated', 'sa_updated', 'string')
421 //XXX: is a string because of \n separator
422 ->addScalarResult('sa_canceled', 'sa_canceled', '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 //Return result
430 return $em
431 ->createNativeQuery($req, $rsm)
432 ->setParameter('sid', $id)
433 ->setParameter('locale', $locale)
434 ->getOneOrNullResult();
435 }
436
437 /**
438 * Fetch sessions calendar with translated location by date period
439 *
440 * @param $translator The TranslatorInterface instance
441 * @param $period The date period
442 * @param $locationId The location id
443 * @param $sessionId The session id
444 * @param $granted The session is granted
445 */
446 public function fetchCalendarByDatePeriod(TranslatorInterface $translator, $period, $locationId = null, $sessionId = null, $granted = false, $locale = null) {
447 //Get entity manager
448 $em = $this->getEntityManager();
449
450 //Get quote strategy
451 $qs = $em->getConfiguration()->getQuoteStrategy();
452 $dp = $em->getConnection()->getDatabasePlatform();
453
454 //Get quoted table names
455 //XXX: this allow to make this code table name independent
456 $tables = [
457 'RapsysAirBundle:GroupUser' => $qs->getJoinTableName($em->getClassMetadata('RapsysAirBundle:User')->getAssociationMapping('groups'), $em->getClassMetadata('RapsysAirBundle:User'), $dp),
458 'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
459 'RapsysAirBundle:Application' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Application'), $dp),
460 'RapsysAirBundle:Group' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Group'), $dp),
461 'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp),
462 'RapsysAirBundle:Slot' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Slot'), $dp),
463 'RapsysAirBundle:Snippet' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Snippet'), $dp),
464 'RapsysAirBundle:User' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:User'), $dp),
465 ':afterid' => 4,
466 "\t" => '',
467 "\n" => ' '
468 ];
469
470 //Init granted sql
471 $grantSql = '';
472
473 //When granted is set
474 if (empty($granted)) {
475 //Set application and user as optional
476 $grantSql = 'LEFT ';
477 }
478
479 //Init location sql
480 $locationSql = '';
481
482 //When location id is set
483 if (!empty($locationId)) {
484 //Add location id clause
485 $locationSql = "\n\t".'AND s.location_id = :lid';
486 }
487
488 //Set the request
489 $req = <<<SQL
490
491 SELECT
492 s.id,
493 s.date,
494 s.rainrisk,
495 s.rainfall,
496 s.realfeel,
497 s.temperature,
498 s.locked,
499 ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS start,
500 ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS stop,
501 s.location_id AS l_id,
502 l.title AS l_title,
503 s.slot_id AS t_id,
504 t.title AS t_title,
505 s.application_id AS a_id,
506 a.canceled AS a_canceled,
507 a.user_id AS au_id,
508 au.pseudonym AS au_pseudonym,
509 p.rate AS p_rate,
510 p.hat AS p_hat,
511 GROUP_CONCAT(sa.user_id ORDER BY sa.user_id SEPARATOR "\\n") AS sau_id,
512 GROUP_CONCAT(sau.pseudonym ORDER BY sa.user_id SEPARATOR "\\n") AS sau_pseudonym
513 FROM RapsysAirBundle:Session AS s
514 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
515 JOIN RapsysAirBundle:Slot AS t ON (t.id = s.slot_id)
516 ${grantSql}JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id)
517 ${grantSql}JOIN RapsysAirBundle:User AS au ON (au.id = a.user_id)
518 LEFT JOIN RapsysAirBundle:Application AS sa ON (sa.session_id = s.id)
519 LEFT JOIN RapsysAirBundle:Snippet AS p ON (p.location_id = s.location_id AND p.user_id = a.user_id AND p.locale = :locale)
520 LEFT JOIN RapsysAirBundle:User AS sau ON (sau.id = sa.user_id)
521 WHERE s.date BETWEEN :begin AND :end${locationSql}
522 GROUP BY s.id
523 ORDER BY NULL
524 SQL;
525
526 //Replace bundle entity name by table name
527 $req = str_replace(array_keys($tables), array_values($tables), $req);
528
529 //Get result set mapping instance
530 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
531 $rsm = new ResultSetMapping();
532
533 //Declare all fields
534 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
535 //addScalarResult($sqlColName, $resColName, $type = 'string');
536 $rsm->addScalarResult('id', 'id', 'integer')
537 ->addScalarResult('date', 'date', 'date')
538 ->addScalarResult('rainrisk', 'rainrisk', 'float')
539 ->addScalarResult('rainfall', 'rainfall', 'float')
540 ->addScalarResult('realfeel', 'realfeel', 'float')
541 ->addScalarResult('temperature', 'temperature', 'float')
542 ->addScalarResult('locked', 'locked', 'datetime')
543 ->addScalarResult('start', 'start', 'datetime')
544 ->addScalarResult('stop', 'stop', 'datetime')
545 ->addScalarResult('t_id', 't_id', 'integer')
546 ->addScalarResult('t_title', 't_title', 'string')
547 ->addScalarResult('l_id', 'l_id', 'integer')
548 ->addScalarResult('l_title', 'l_title', 'string')
549 ->addScalarResult('a_id', 'a_id', 'integer')
550 ->addScalarResult('a_canceled', 'a_canceled', 'datetime')
551 ->addScalarResult('au_id', 'au_id', 'integer')
552 ->addScalarResult('au_pseudonym', 'au_pseudonym', 'string')
553 ->addScalarResult('p_rate', 'p_rate', 'integer')
554 ->addScalarResult('p_hat', 'p_hat', 'boolean')
555 //XXX: is a string because of \n separator
556 ->addScalarResult('sau_id', 'sau_id', 'string')
557 //XXX: is a string because of \n separator
558 ->addScalarResult('sau_pseudonym', 'sau_pseudonym', 'string')
559 ->addIndexByScalar('id');
560
561 //Fetch result
562 $res = $em
563 ->createNativeQuery($req, $rsm)
564 ->setParameter('begin', $period->getStartDate())
565 ->setParameter('end', $period->getEndDate())
566 ->setParameter('locale', $locale);
567
568 //Add optional location id
569 if (!empty($locationId)) {
570 $res->setParameter('lid', $locationId);
571 }
572
573 //Get result
574 $res = $res->getResult();
575
576 //Init calendar
577 $calendar = [];
578
579 //Init month
580 $month = null;
581
582 //Iterate on each day
583 foreach($period as $date) {
584 //Init day in calendar
585 $calendar[$Ymd = $date->format('Ymd')] = [
586 'title' => $translator->trans($date->format('l')).' '.$date->format('d'),
587 'class' => [],
588 'sessions' => []
589 ];
590
591 //Detect month change
592 if ($month != $date->format('m')) {
593 $month = $date->format('m');
594 //Append month for first day of month
595 //XXX: except if today to avoid double add
596 if ($date->format('U') != strtotime('today')) {
597 $calendar[$Ymd]['title'] .= '/'.$month;
598 }
599 }
600 //Deal with today
601 if ($date->format('U') == ($today = strtotime('today'))) {
602 $calendar[$Ymd]['title'] .= '/'.$month;
603 $calendar[$Ymd]['current'] = true;
604 $calendar[$Ymd]['class'][] = 'current';
605 }
606 //Disable passed days
607 if ($date->format('U') < $today) {
608 $calendar[$Ymd]['disabled'] = true;
609 $calendar[$Ymd]['class'][] = 'disabled';
610 }
611 //Set next month days
612 if ($date->format('m') > date('m')) {
613 $calendar[$Ymd]['next'] = true;
614 #$calendar[$Ymd]['class'][] = 'next';
615 }
616
617 //Detect sunday
618 if ($date->format('w') == 0) {
619 $calendar[$Ymd]['class'][] = 'sunday';
620 }
621
622 //Iterate on each session to find the one of the day
623 foreach($res as $session) {
624 if (($sessionYmd = $session['date']->format('Ymd')) == $Ymd) {
625 //Count number of application
626 $count = count(explode("\n", $session['sau_id']));
627
628 //Compute classes
629 $class = [];
630 if (!empty($session['a_id'])) {
631 $applications = [ $session['au_id'] => $session['au_pseudonym'] ];
632 if (!empty($session['a_canceled'])) {
633 $class[] = 'canceled';
634 } else {
635 $class[] = 'granted';
636 }
637 } elseif ($count > 1) {
638 $class[] = 'disputed';
639 } elseif (!empty($session['locked'])) {
640 $class[] = 'locked';
641 } else {
642 $class[] = 'pending';
643 }
644
645 if ($sessionId == $session['id']) {
646 $class[] = 'highlight';
647 }
648
649 //Set temperature
650 //XXX: realfeel may be null, temperature should not
651 $temperature = $session['realfeel'] !== null ? $session['realfeel'] : $session['temperature'];
652
653 //Compute weather
654 //XXX: rainfall may be null
655 if ($session['rainrisk'] > 0.50 || $session['rainfall'] > 2) {
656 $weather = self::GLYPHS['Stormy'];
657 } elseif ($session['rainrisk'] > 0.40 || $session['rainfall'] > 1) {
658 $weather = self::GLYPHS['Rainy'];
659 } elseif ($temperature > 24) {
660 $weather = self::GLYPHS['Cleary'];
661 } elseif ($temperature > 17) {
662 $weather = self::GLYPHS['Sunny'];
663 } elseif ($temperature > 10) {
664 $weather = self::GLYPHS['Cloudy'];
665 } elseif ($temperature !== null) {
666 $weather = self::GLYPHS['Winty'];
667 } else {
668 $weather = null;
669 }
670
671 //Init weathertitle
672 $weathertitle = [];
673
674 //Check if realfeel is available
675 if ($session['realfeel'] !== null) {
676 $weathertitle[] = $session['realfeel'].'Β°R';
677 }
678
679 //Check if temperature is available
680 if ($session['temperature'] !== null) {
681 $weathertitle[] = $session['temperature'].'Β°C';
682 }
683
684 //Check if rainrisk is available
685 if ($session['rainrisk'] !== null) {
686 $weathertitle[] = ($session['rainrisk']*100).'%';
687 }
688
689 //Check if rainfall is available
690 if ($session['rainfall'] !== null) {
691 $weathertitle[] = $session['rainfall'].'mm';
692 }
693
694 //Set applications
695 $applications = [
696 0 => $translator->trans($session['t_title']).' '.$translator->trans('at '.$session['l_title']).$translator->trans(':')
697 ];
698
699 //Fetch pseudonyms from session applications
700 $applications += array_combine(explode("\n", $session['sau_id']), array_map(function ($v) {return '- '.$v;}, explode("\n", $session['sau_pseudonym'])));
701
702 //Set pseudonym
703 $pseudonym = null;
704
705 //Check that session is not granted
706 if (empty($session['a_id'])) {
707 //With location id and unique application
708 if ($count == 1) {
709 //Set unique application pseudonym
710 $pseudonym = $session['sau_pseudonym'];
711 }
712 //Session is granted
713 } else {
714 //Replace granted application
715 $applications[$session['au_id']] = '* '.$session['au_pseudonym'];
716
717 //Set pseudonym
718 $pseudonym = $session['au_pseudonym'].($count > 1 ? ' ['.$count.']':'');
719 }
720
721 //Add the session
722 $calendar[$Ymd]['sessions'][$session['t_id'].sprintf('%05d', $session['id'])] = [
723 'id' => $session['id'],
724 'start' => $session['start'],
725 'stop' => $session['stop'],
726 'location' => $translator->trans($session['l_title']),
727 'pseudonym' => $pseudonym,
728 'class' => $class,
729 'slot' => self::GLYPHS[$session['t_title']],
730 'slottitle' => $translator->trans($session['t_title']),
731 'weather' => $weather,
732 'weathertitle' => implode(' ', $weathertitle),
733 'applications' => $applications,
734 'rate' => $session['p_rate'],
735 'hat' => $session['p_hat']
736 ];
737 }
738 }
739
740 //Sort sessions
741 ksort($calendar[$Ymd]['sessions']);
742 }
743
744 //Send result
745 return $calendar;
746 }
747
748 /**
749 * Fetch sessions calendar with translated location by date period and user
750 *
751 * @param $translator The TranslatorInterface instance
752 * @param $period The date period
753 * @param $userId The user id
754 * @param $sessionId The session id
755 */
756 public function fetchUserCalendarByDatePeriod(TranslatorInterface $translator, $period, $userId = null, $sessionId = null, $locale = null) {
757 //Get entity manager
758 $em = $this->getEntityManager();
759
760 //Get quote strategy
761 $qs = $em->getConfiguration()->getQuoteStrategy();
762 $dp = $em->getConnection()->getDatabasePlatform();
763
764 //Get quoted table names
765 //XXX: this allow to make this code table name independent
766 $tables = [
767 'RapsysAirBundle:GroupUser' => $qs->getJoinTableName($em->getClassMetadata('RapsysAirBundle:User')->getAssociationMapping('groups'), $em->getClassMetadata('RapsysAirBundle:User'), $dp),
768 'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
769 'RapsysAirBundle:Application' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Application'), $dp),
770 'RapsysAirBundle:Group' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Group'), $dp),
771 'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp),
772 'RapsysAirBundle:Slot' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Slot'), $dp),
773 'RapsysAirBundle:Snippet' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Snippet'), $dp),
774 'RapsysAirBundle:User' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:User'), $dp),
775 ':afterid' => 4,
776 "\t" => '',
777 "\n" => ' '
778 ];
779
780 //Init user sql
781 $userJoinSql = $userWhereSql = '';
782
783 //When user id is set
784 if (!empty($userId)) {
785 //Add user join
786 $userJoinSql = 'JOIN RapsysAirBundle:Application AS sua ON (sua.session_id = s.id)'."\n";
787 //Add user id clause
788 $userWhereSql = "\n\t".'AND sua.user_id = :uid';
789 }
790
791 //Set the request
792 //TODO: change as_u_* in sau_*, a_u_* in au_*, etc, see request up
793 $req = <<<SQL
794 SELECT
795 s.id,
796 s.date,
797 s.rainrisk,
798 s.rainfall,
799 s.realfeel,
800 s.temperature,
801 s.locked,
802 ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS start,
803 ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS stop,
804 s.location_id AS l_id,
805 l.title AS l_title,
806 s.slot_id AS t_id,
807 t.title AS t_title,
808 s.application_id AS a_id,
809 a.user_id AS au_id,
810 au.pseudonym AS au_pseudonym,
811 p.rate AS p_rate,
812 p.hat AS p_hat,
813 GROUP_CONCAT(sa.user_id ORDER BY sa.user_id SEPARATOR "\\n") AS sau_id,
814 GROUP_CONCAT(CONCAT("- ", sau.pseudonym) ORDER BY sa.user_id SEPARATOR "\\n") AS sau_pseudonym
815 FROM RapsysAirBundle:Session AS s
816 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
817 JOIN RapsysAirBundle:Slot AS t ON (t.id = s.slot_id)
818 ${userJoinSql}LEFT JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id)
819 LEFT JOIN RapsysAirBundle:Snippet AS p ON (p.location_id = s.location_id AND p.user_id = a.user_id AND p.locale = :locale)
820 LEFT JOIN RapsysAirBundle:User AS au ON (au.id = a.user_id)
821 LEFT JOIN RapsysAirBundle:Application AS sa ON (sa.session_id = s.id)
822 LEFT JOIN RapsysAirBundle:User AS sau ON (sau.id = sa.user_id)
823 WHERE s.date BETWEEN :begin AND :end${userWhereSql}
824 GROUP BY s.id
825 ORDER BY NULL
826 SQL;
827
828 //Replace bundle entity name by table name
829 $req = str_replace(array_keys($tables), array_values($tables), $req);
830
831 //Get result set mapping instance
832 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
833 $rsm = new ResultSetMapping();
834
835 //Declare all fields
836 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
837 //addScalarResult($sqlColName, $resColName, $type = 'string');
838 $rsm->addScalarResult('id', 'id', 'integer')
839 ->addScalarResult('date', 'date', 'date')
840 ->addScalarResult('rainrisk', 'rainrisk', 'float')
841 ->addScalarResult('rainfall', 'rainfall', 'float')
842 ->addScalarResult('realfeel', 'realfeel', 'float')
843 ->addScalarResult('temperature', 'temperature', 'float')
844 ->addScalarResult('locked', 'locked', 'datetime')
845 ->addScalarResult('start', 'start', 'datetime')
846 ->addScalarResult('stop', 'stop', 'datetime')
847 ->addScalarResult('t_id', 't_id', 'integer')
848 ->addScalarResult('t_title', 't_title', 'string')
849 ->addScalarResult('l_id', 'l_id', 'integer')
850 ->addScalarResult('l_title', 'l_title', 'string')
851 ->addScalarResult('a_id', 'a_id', 'integer')
852 ->addScalarResult('au_id', 'au_id', 'integer')
853 ->addScalarResult('au_pseudonym', 'au_pseudonym', 'string')
854 ->addScalarResult('p_rate', 'p_rate', 'integer')
855 ->addScalarResult('p_hat', 'p_hat', 'boolean')
856 //XXX: is a string because of \n separator
857 ->addScalarResult('sau_id', 'sau_id', 'string')
858 //XXX: is a string because of \n separator
859 ->addScalarResult('sau_pseudonym', 'sau_pseudonym', 'string')
860 ->addIndexByScalar('id');
861
862 //Fetch result
863 $res = $em
864 ->createNativeQuery($req, $rsm)
865 ->setParameter('begin', $period->getStartDate())
866 ->setParameter('end', $period->getEndDate())
867 ->setParameter('uid', $userId)
868 ->setParameter('locale', $locale)
869 ->getResult();
870
871 //Init calendar
872 $calendar = [];
873
874 //Init month
875 $month = null;
876
877 //Iterate on each day
878 foreach($period as $date) {
879 //Init day in calendar
880 $calendar[$Ymd = $date->format('Ymd')] = [
881 'title' => $translator->trans($date->format('l')).' '.$date->format('d'),
882 'class' => [],
883 'sessions' => []
884 ];
885
886 //Detect month change
887 if ($month != $date->format('m')) {
888 $month = $date->format('m');
889 //Append month for first day of month
890 //XXX: except if today to avoid double add
891 if ($date->format('U') != strtotime('today')) {
892 $calendar[$Ymd]['title'] .= '/'.$month;
893 }
894 }
895 //Deal with today
896 if ($date->format('U') == ($today = strtotime('today'))) {
897 $calendar[$Ymd]['title'] .= '/'.$month;
898 $calendar[$Ymd]['current'] = true;
899 $calendar[$Ymd]['class'][] = 'current';
900 }
901 //Disable passed days
902 if ($date->format('U') < $today) {
903 $calendar[$Ymd]['disabled'] = true;
904 $calendar[$Ymd]['class'][] = 'disabled';
905 }
906 //Set next month days
907 if ($date->format('m') > date('m')) {
908 $calendar[$Ymd]['next'] = true;
909 #$calendar[$Ymd]['class'][] = 'next';
910 }
911
912 //Detect sunday
913 if ($date->format('w') == 0) {
914 $calendar[$Ymd]['class'][] = 'sunday';
915 }
916
917 //Iterate on each session to find the one of the day
918 foreach($res as $session) {
919 if (($sessionYmd = $session['date']->format('Ymd')) == $Ymd) {
920 //Count number of application
921 $count = count(explode("\n", $session['sau_id']));
922
923 //Compute classes
924 $class = [];
925 if (!empty($session['a_id'])) {
926 $applications = [ $session['au_id'] => $session['au_pseudonym'] ];
927 if ($session['au_id'] == $userId) {
928 $class[] = 'granted';
929 } else {
930 $class[] = 'disputed';
931 }
932 } elseif ($count > 1) {
933 $class[] = 'disputed';
934 } elseif (!empty($session['locked'])) {
935 $class[] = 'locked';
936 } else {
937 $class[] = 'pending';
938 }
939
940 if ($sessionId == $session['id']) {
941 $class[] = 'highlight';
942 }
943
944 //Set temperature
945 //XXX: realfeel may be null, temperature should not
946 $temperature = $session['realfeel'] !== null ? $session['realfeel'] : $session['temperature'];
947
948 //Compute weather
949 //XXX: rainfall may be null
950 if ($session['rainrisk'] > 0.50 || $session['rainfall'] > 2) {
951 $weather = self::GLYPHS['Stormy'];
952 } elseif ($session['rainrisk'] > 0.40 || $session['rainfall'] > 1) {
953 $weather = self::GLYPHS['Rainy'];
954 } elseif ($temperature > 24) {
955 $weather = self::GLYPHS['Cleary'];
956 } elseif ($temperature > 17) {
957 $weather = self::GLYPHS['Sunny'];
958 } elseif ($temperature > 10) {
959 $weather = self::GLYPHS['Cloudy'];
960 } elseif ($temperature !== null) {
961 $weather = self::GLYPHS['Winty'];
962 } else {
963 $weather = null;
964 }
965
966 //Init weathertitle
967 $weathertitle = [];
968
969 //Check if realfeel is available
970 if ($session['realfeel'] !== null) {
971 $weathertitle[] = $session['realfeel'].'Β°R';
972 }
973
974 //Check if temperature is available
975 if ($session['temperature'] !== null) {
976 $weathertitle[] = $session['temperature'].'Β°C';
977 }
978
979 //Check if rainrisk is available
980 if ($session['rainrisk'] !== null) {
981 $weathertitle[] = ($session['rainrisk']*100).'%';
982 }
983
984 //Check if rainfall is available
985 if ($session['rainfall'] !== null) {
986 $weathertitle[] = $session['rainfall'].'mm';
987 }
988
989 //Set applications
990 $applications = [
991 0 => $translator->trans($session['t_title']).' '.$translator->trans('at '.$session['l_title']).$translator->trans(':')
992 ];
993
994 //Fetch pseudonyms from session applications
995 $applications += array_combine(explode("\n", $session['sau_id']), array_map(function ($v) {return '- '.$v;}, explode("\n", $session['sau_pseudonym'])));
996
997 //Set pseudonym
998 $pseudonym = null;
999
1000 //Check that session is not granted
1001 if (empty($session['a_id'])) {
1002 //With location id and unique application
1003 if ($count == 1) {
1004 //Set unique application pseudonym
1005 $pseudonym = $session['sau_pseudonym'];
1006 }
1007 //Session is granted
1008 } else {
1009 //Replace granted application
1010 $applications[$session['au_id']] = '* '.$session['au_pseudonym'];
1011
1012 //Set pseudonym
1013 $pseudonym = $session['au_pseudonym'].($count > 1 ? ' ['.$count.']':'');
1014 }
1015
1016 //Set title
1017 $title = $translator->trans($session['l_title']).($count > 1 ? ' ['.$count.']':'');
1018
1019 //Add the session
1020 $calendar[$Ymd]['sessions'][$session['t_id'].sprintf('%02d', $session['l_id'])] = [
1021 'id' => $session['id'],
1022 'start' => $session['start'],
1023 'stop' => $session['stop'],
1024 'location' => $translator->trans($session['l_title']),
1025 'pseudonym' => $pseudonym,
1026 'class' => $class,
1027 'slot' => self::GLYPHS[$session['t_title']],
1028 'slottitle' => $translator->trans($session['t_title']),
1029 'weather' => $weather,
1030 'weathertitle' => implode(' ', $weathertitle),
1031 'applications' => $applications,
1032 'rate' => $session['p_rate'],
1033 'hat' => $session['p_hat']
1034 ];
1035 }
1036 }
1037
1038 //Sort sessions
1039 ksort($calendar[$Ymd]['sessions']);
1040 }
1041
1042 //Send result
1043 return $calendar;
1044 }
1045
1046 /**
1047 * Find all session pending hourly weather
1048 *
1049 * @return array<Session> The sessions to update
1050 */
1051 public function findAllPendingHourlyWeather() {
1052 //Get entity manager
1053 $em = $this->getEntityManager();
1054
1055 //Get quote strategy
1056 $qs = $em->getConfiguration()->getQuoteStrategy();
1057 $dp = $em->getConnection()->getDatabasePlatform();
1058
1059 //Get quoted table names
1060 //XXX: this allow to make this code table name independent
1061 $tables = [
1062 'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
1063 'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp),
1064 //Accuweather
1065 ':accuhourly' => self::ACCUWEATHER_HOURLY,
1066 //Delay
1067 ':afterid' => 4,
1068 "\t" => '',
1069 "\n" => ' '
1070 ];
1071
1072 //Select all sessions starting and stopping in the next 3 days
1073 //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)
1074 $req = <<<SQL
1075 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
1076 FROM RapsysAirBundle:Session AS s
1077 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
1078 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))
1079 SQL;
1080
1081 //Replace bundle entity name by table name
1082 $req = str_replace(array_keys($tables), array_values($tables), $req);
1083
1084 //Get result set mapping instance
1085 $rsm = new ResultSetMapping();
1086
1087 //Declare all fields
1088 $rsm
1089 ->addEntityResult('RapsysAirBundle:Session', 's')
1090 ->addFieldResult('s', 'id', 'id')
1091 ->addFieldResult('s', 'date', 'date')
1092 ->addFieldResult('s', 'begin', 'begin')
1093 ->addFieldResult('s', 'length', 'length')
1094 ->addFieldResult('s', 'rainfall', 'rainfall')
1095 ->addFieldResult('s', 'rainrisk', 'rainrisk')
1096 ->addFieldResult('s', 'realfeel', 'realfeel')
1097 ->addFieldResult('s', 'realfeelmin', 'realfeelmin')
1098 ->addFieldResult('s', 'realfeelmax', 'realfeelmax')
1099 ->addFieldResult('s', 'temperature', 'temperature')
1100 ->addFieldResult('s', 'temperaturemin', 'temperaturemin')
1101 ->addFieldResult('s', 'temperaturemax', 'temperaturemax')
1102 ->addJoinedEntityResult('RapsysAirBundle:Slot', 'o', 's', 'slot')
1103 ->addFieldResult('o', 'slot_id', 'id')
1104 ->addJoinedEntityResult('RapsysAirBundle:Location', 'l', 's', 'location')
1105 ->addFieldResult('l', 'location_id', 'id')
1106 ->addFieldResult('l', 'zipcode', 'zipcode')
1107 ->addIndexBy('s', 'id');
1108
1109 //Send result
1110 return $em
1111 ->createNativeQuery($req, $rsm)
1112 ->getResult();
1113 }
1114
1115 /**
1116 * Find all session pending daily weather
1117 *
1118 * @return array<Session> The sessions to update
1119 */
1120 public function findAllPendingDailyWeather() {
1121 //Get entity manager
1122 $em = $this->getEntityManager();
1123
1124 //Get quote strategy
1125 $qs = $em->getConfiguration()->getQuoteStrategy();
1126 $dp = $em->getConnection()->getDatabasePlatform();
1127
1128 //Get quoted table names
1129 //XXX: this allow to make this code table name independent
1130 $tables = [
1131 'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
1132 'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp),
1133 //Accuweather
1134 ':accudaily' => self::ACCUWEATHER_DAILY,
1135 ':accuhourly' => self::ACCUWEATHER_HOURLY,
1136 //Delay
1137 ':afterid' => 4,
1138 "\t" => '',
1139 "\n" => ' '
1140 ];
1141
1142 //Select all sessions stopping after next 3 days
1143 //XXX: select session stopping after or equal date(now)+3d as accuweather only provide hourly data for the next 3 days (INTERVAL 3 DAY)
1144 $req = <<<SQL
1145 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
1146 FROM RapsysAirBundle:Session AS s
1147 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
1148 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))
1149 SQL;
1150
1151 //Replace bundle entity name by table name
1152 $req = str_replace(array_keys($tables), array_values($tables), $req);
1153
1154 //Get result set mapping instance
1155 $rsm = new ResultSetMapping();
1156
1157 //Declare all fields
1158 $rsm
1159 ->addEntityResult('RapsysAirBundle:Session', 's')
1160 ->addFieldResult('s', 'id', 'id')
1161 ->addFieldResult('s', 'date', 'date')
1162 ->addFieldResult('s', 'begin', 'begin')
1163 ->addFieldResult('s', 'length', 'length')
1164 ->addFieldResult('s', 'rainfall', 'rainfall')
1165 ->addFieldResult('s', 'rainrisk', 'rainrisk')
1166 ->addFieldResult('s', 'realfeel', 'realfeel')
1167 ->addFieldResult('s', 'realfeelmin', 'realfeelmin')
1168 ->addFieldResult('s', 'realfeelmax', 'realfeelmax')
1169 ->addFieldResult('s', 'temperature', 'temperature')
1170 ->addFieldResult('s', 'temperaturemin', 'temperaturemin')
1171 ->addFieldResult('s', 'temperaturemax', 'temperaturemax')
1172 ->addJoinedEntityResult('RapsysAirBundle:Slot', 'o', 's', 'slot')
1173 ->addFieldResult('o', 'slot_id', 'id')
1174 ->addJoinedEntityResult('RapsysAirBundle:Location', 'l', 's', 'location')
1175 ->addFieldResult('l', 'location_id', 'id')
1176 ->addFieldResult('l', 'zipcode', 'zipcode')
1177 ->addIndexBy('s', 'id');
1178
1179 //Send result
1180 return $em
1181 ->createNativeQuery($req, $rsm)
1182 ->getResult();
1183 }
1184
1185 /**
1186 * Find every session pending application
1187 *
1188 * @return array<Session> The sessions to update
1189 */
1190 public function findAllPendingApplication() {
1191 //Get entity manager
1192 $em = $this->getEntityManager();
1193
1194 //Get quote strategy
1195 $qs = $em->getConfiguration()->getQuoteStrategy();
1196 $dp = $em->getConnection()->getDatabasePlatform();
1197
1198 //Get quoted table names
1199 //XXX: this allow to make this code table name independent
1200 $tables = [
1201 'RapsysAirBundle:Application' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Application'), $dp),
1202 'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
1203 //Delay
1204 ':regulardelay' => self::REGULAR_DELAY * 24 * 3600,
1205 ':seniordelay' => self::SENIOR_DELAY * 24 * 3600,
1206 //Slot
1207 ':afterid' => 4,
1208 "\t" => '',
1209 "\n" => ' '
1210 ];
1211
1212 //Select all sessions not locked without application or canceled application within attribution period
1213 //XXX: DIFF(start, now) <= IF(DIFF(start, created) <= SENIOR_DELAY in DAY, DIFF(start, created) * 3 / 4, SENIOR_DELAY)
1214 //TODO: remonter les donnΓ©es pour le mail ?
1215 $req =<<<SQL
1216 SELECT s.id
1217 FROM RapsysAirBundle:Session as s
1218 LEFT JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id AND a.canceled IS NULL)
1219 JOIN RapsysAirBundle:Application AS a2 ON (a2.session_id = s.id AND a2.canceled IS NULL)
1220 WHERE s.locked IS NULL AND a.id IS NULL AND
1221 TIME_TO_SEC(TIMEDIFF(@dt_start := ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY), NOW())) <= IF(
1222 TIME_TO_SEC(@td_sc := TIMEDIFF(@dt_start, s.created)) <= :seniordelay,
1223 ROUND(TIME_TO_SEC(@td_sc) * :regulardelay / :seniordelay),
1224 :seniordelay
1225 )
1226 GROUP BY s.id
1227 ORDER BY @dt_start ASC, s.created ASC
1228 SQL;
1229
1230
1231 //Replace bundle entity name by table name
1232 $req = str_replace(array_keys($tables), array_values($tables), $req);
1233
1234 //Get result set mapping instance
1235 $rsm = new ResultSetMapping();
1236
1237 //Declare all fields
1238 $rsm
1239 ->addEntityResult('RapsysAirBundle:Session', 's')
1240 ->addFieldResult('s', 'id', 'id')
1241 ->addIndexBy('s', 'id');
1242
1243 //Send result
1244 return $em
1245 ->createNativeQuery($req, $rsm)
1246 ->getResult();
1247 }
1248
1249 /**
1250 * Fetch session best application by session id
1251 *
1252 * @param int $id The session id
1253 * @return Application|null The application or null
1254 */
1255 public function findBestApplicationById($id) {
1256 //Get entity manager
1257 $em = $this->getEntityManager();
1258
1259 //Get quote strategy
1260 $qs = $em->getConfiguration()->getQuoteStrategy();
1261 $dp = $em->getConnection()->getDatabasePlatform();
1262
1263 //Get quoted table names
1264 //XXX: this allow to make this code table name independent
1265 $tables = [
1266 'RapsysAirBundle:Application' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Application'), $dp),
1267 'RapsysAirBundle:GroupUser' => $qs->getJoinTableName($em->getClassMetadata('RapsysAirBundle:User')->getAssociationMapping('groups'), $em->getClassMetadata('RapsysAirBundle:User'), $dp),
1268 'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp),
1269 'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
1270 //XXX: Set limit used to workaround mariadb subselect optimization
1271 ':limit' => PHP_INT_MAX,
1272 //Delay
1273 ':guestdelay' => self::GUEST_DELAY * 24 * 3600,
1274 ':regulardelay' => self::REGULAR_DELAY * 24 * 3600,
1275 ':seniordelay' => self::SENIOR_DELAY * 24 * 3600,
1276 //Group
1277 ':guestid' => 2,
1278 ':regularid' => 3,
1279 ':seniorid' => 4,
1280 //Slot
1281 ':afternoonid' => 2,
1282 ':eveningid' => 3,
1283 ':afterid' => 4,
1284 //XXX: days since last session after which guest regain normal priority
1285 ':guestwait' => 30,
1286 //XXX: session count until considered at regular delay
1287 ':scount' => 5,
1288 //XXX: pn_ratio over which considered at regular delay
1289 ':pnratio' => 1,
1290 //XXX: tr_ratio diff over which considered at regular delay
1291 ':trdiff' => 5,
1292 "\t" => '',
1293 "\n" => ' '
1294 ];
1295
1296 /**
1297 * Query session applications ranked by location score, global score, created and user_id
1298 *
1299 * @xxx guest (or less) with application on location within 30 day are only considered within guestdelay
1300 *
1301 * @xxx regular (or less) premium application on hotspot are only considered within regulardelay
1302 *
1303 * @xxx senior (or less) with 5 or less session on location are only considered within seniordelay
1304 *
1305 * @xxx senior (or less) with l_pn_ratio >= 1 are only considered within seniordelay
1306 *
1307 * @xxx senior (or less) with l_tr_ratio >= (o_tr_ratio + 5) are only considered within seniordelay
1308 *
1309 * @xxx only consider session within one year (may be unaccurate by the day with after session)
1310 *
1311 * @xxx rainfall may not be accessible for previous session and other session at d-4 (only at d-2)
1312 *
1313 * @todo ??? feedback the data to inform the rejected users ???
1314 */
1315 $req = <<<SQL
1316 SELECT e.id, e.l_score AS score
1317 FROM (
1318 SELECT
1319 d.id,
1320 d.user_id,
1321 d.l_count,
1322 d.l_score,
1323 d.l_tr_ratio,
1324 d.l_pn_ratio,
1325 d.l_previous,
1326 d.g_score,
1327 d.o_tr_ratio,
1328 MAX(gu.group_id) AS group_id,
1329 d.remaining,
1330 d.premium,
1331 d.hotspot,
1332 d.created
1333 FROM (
1334 SELECT
1335 c.id,
1336 c.user_id,
1337 c.l_count,
1338 c.l_score,
1339 c.l_tr_ratio,
1340 c.l_pn_ratio,
1341 c.l_previous,
1342 c.g_score,
1343 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,
1344 c.remaining,
1345 c.premium,
1346 c.hotspot,
1347 c.created
1348 FROM (
1349 SELECT
1350 b.id,
1351 b.user_id,
1352 b.session_id,
1353 b.date,
1354 b.location_id,
1355 b.l_count,
1356 b.l_score,
1357 b.l_tr_ratio,
1358 b.l_pn_ratio,
1359 b.l_previous,
1360 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,
1361 b.remaining,
1362 b.premium,
1363 b.hotspot,
1364 b.created
1365 FROM (
1366 SELECT
1367 a.id,
1368 a.user_id,
1369 s.id AS session_id,
1370 s.date AS date,
1371 s.slot_id,
1372 s.location_id,
1373 COUNT(a2.id) AS l_count,
1374 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,
1375 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,
1376 (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,
1377 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,
1378 TIME_TO_SEC(TIMEDIFF(ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY), NOW())) AS remaining,
1379 s.premium,
1380 l.hotspot,
1381 a.created
1382 FROM RapsysAirBundle:Session AS s
1383 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
1384 JOIN RapsysAirBundle:Application AS a ON (a.session_id = s.id AND a.canceled IS NULL)
1385 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)
1386 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))
1387 WHERE s.id = :sid
1388 GROUP BY a.id
1389 ORDER BY NULL
1390 LIMIT 0, :limit
1391 ) AS b
1392 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)
1393 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))
1394 GROUP BY b.id
1395 ORDER BY NULL
1396 LIMIT 0, :limit
1397 ) AS c
1398 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)
1399 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))
1400 GROUP BY c.id
1401 ORDER BY NULL
1402 LIMIT 0, :limit
1403 ) AS d
1404 LEFT JOIN RapsysAirBundle:GroupUser AS gu ON (gu.user_id = d.user_id)
1405 GROUP BY d.id
1406 LIMIT 0, :limit
1407 ) AS e
1408 WHERE
1409 IF(e.group_id <= :guestid AND e.l_previous <= :guestwait, e.remaining <= :guestdelay, 1) AND
1410 IF(e.group_id <= :regularid AND e.premium = 1 AND e.hotspot = 1, e.remaining <= :regulardelay, 1) AND
1411 IF(e.group_id <= :seniorid AND e.l_count <= :scount, e.remaining <= :regulardelay, 1) AND
1412 IF(e.group_id <= :seniorid AND e.l_pn_ratio >= :pnratio, e.remaining <= :regulardelay, 1) AND
1413 IF(e.group_id <= :seniorid AND e.l_tr_ratio >= (e.o_tr_ratio + :trdiff), e.remaining <= :regulardelay, 1)
1414 ORDER BY e.l_score ASC, e.g_score ASC, e.created ASC, e.user_id ASC
1415 SQL;
1416
1417 //Replace bundle entity name by table name
1418 $req = str_replace(array_keys($tables), array_values($tables), $req);
1419
1420 //Set update request
1421 $upreq = 'UPDATE RapsysAirBundle:Application SET score = :score, updated = NOW() WHERE id = :id';
1422
1423 //Replace bundle entity name by table name
1424 $upreq = str_replace(array_keys($tables), array_values($tables), $upreq);
1425
1426 //Get result set mapping instance
1427 $rsm = new ResultSetMapping();
1428
1429 //Declare all fields
1430 $rsm
1431 ->addEntityResult('RapsysAirBundle:Application', 'a')
1432 ->addFieldResult('a', 'id', 'id')
1433 ->addFieldResult('a', 'score', 'score')
1434 ->addIndexBy('a', 'id');
1435
1436 //Get result
1437 //XXX: setting limit in subqueries is required to prevent mariadb optimisation
1438 $applications = $em
1439 ->createNativeQuery($req, $rsm)
1440 ->setParameter('sid', $id)
1441 //XXX: removed, we update score before returning best candidate
1442 //->getOneOrNullResult(Query::HYDRATE_SINGLE_SCALAR);
1443 ->getResult();
1444
1445 //Init ret
1446 $ret = null;
1447
1448 //Update score
1449 foreach($applications as $application) {
1450 //Check if we already saved best candidate
1451 if ($ret === null) {
1452 //Return first application
1453 $ret = $application;
1454 }
1455
1456 //Update application updated field
1457 //XXX: updated field is not modified for user with bad behaviour as application is not retrieved until delay is reached
1458 $em->getConnection()->executeUpdate($upreq, ['id' => $application->getId(), 'score' => $application->getScore()], ['id' => Type::INTEGER, 'score' => Type::FLOAT]);
1459 }
1460
1461 //Return best ranked application
1462 return $ret;
1463 }
1464
1465
1466 /**
1467 * Rekey sessions and applications by chronological session id
1468 *
1469 * @return bool The rekey success or failure
1470 */
1471 function rekey(): bool {
1472 //Get entity manager
1473 $em = $this->getEntityManager();
1474
1475 //Get connection
1476 $cnx = $em->getConnection();
1477
1478 //Get quote strategy
1479 $qs = $em->getConfiguration()->getQuoteStrategy();
1480 $dp = $em->getConnection()->getDatabasePlatform();
1481
1482 //Get quoted table names
1483 //XXX: this allow to make this code table name independent
1484 $tables = [
1485 'RapsysAirBundle:Application' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Application'), $dp),
1486 'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
1487 ':afterid' => 4,
1488 "\t" => '',
1489 "\n" => ' '
1490 ];
1491
1492 //Set the request
1493 $req = <<<SQL
1494 SELECT
1495 a.id,
1496 a.sa_id
1497 FROM (
1498 SELECT
1499 s.id,
1500 s.date,
1501 s.begin,
1502 s.slot_id,
1503 GROUP_CONCAT(sa.id ORDER BY sa.id SEPARATOR "\\n") AS sa_id
1504 FROM RapsysAirBundle:Session AS s
1505 LEFT JOIN RapsysAirBundle:Application AS sa ON (sa.session_id = s.id)
1506 GROUP BY s.id
1507 ORDER BY NULL
1508 ) AS a
1509 ORDER BY ADDDATE(ADDTIME(a.date, a.begin), INTERVAL IF(a.slot_id = :afterid, 1, 0) DAY) ASC
1510 SQL;
1511
1512 //Replace bundle entity name by table name
1513 $req = str_replace(array_keys($tables), array_values($tables), $req);
1514
1515 //Get result set mapping instance
1516 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
1517 $rsm = new ResultSetMapping();
1518
1519 //Declare all fields
1520 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
1521 //addScalarResult($sqlColName, $resColName, $type = 'string');
1522 $rsm->addScalarResult('id', 'id', 'integer')
1523 ->addScalarResult('sa_id', 'sa_id', 'string');
1524 #->addIndexByScalar('id');
1525
1526 //Fetch result
1527 $rnq = $em->createNativeQuery($req, $rsm);
1528
1529 //Get result set
1530 $res = $rnq->getResult();
1531
1532 //Start transaction
1533 $cnx->beginTransaction();
1534
1535 //Set update session request
1536 $sreq = <<<SQL
1537 UPDATE RapsysAirBundle:Session
1538 SET id = :nid, updated = NOW()
1539 WHERE id = :id
1540 SQL;
1541
1542 //Replace bundle entity name by table name
1543 $sreq = str_replace(array_keys($tables), array_values($tables), $sreq);
1544
1545 //Set update application request
1546 $areq = <<<SQL
1547 UPDATE RapsysAirBundle:Application
1548 SET session_id = :nid, updated = NOW()
1549 WHERE session_id = :id
1550 SQL;
1551
1552 //Replace bundle entity name by table name
1553 $areq = str_replace(array_keys($tables), array_values($tables), $areq);
1554
1555 //Set max value
1556 $max = max(array_keys($res));
1557
1558 try {
1559 //Prepare session to update
1560 foreach($res as $id => $data) {
1561 //Set temp id
1562 $res[$id]['t_id'] = $max + $id + 1;
1563
1564 //Set new id
1565 $res[$id]['n_id'] = $id + 1;
1566
1567 //Explode application ids
1568 $res[$id]['sa_id'] = explode("\n", $data['sa_id']);
1569
1570 //Without change
1571 if ($res[$id]['n_id'] == $res[$id]['id']) {
1572 //Remove unchanged session
1573 unset($res[$id]);
1574 }
1575 }
1576
1577 //With changes
1578 if (!empty($res)) {
1579 //Disable foreign key checks
1580 $cnx->prepare('SET foreign_key_checks = 0')->execute();
1581
1582 //Update to temp id
1583 foreach($res as $id => $data) {
1584 //Run session update
1585 $cnx->executeUpdate($sreq, ['nid' => $res[$id]['t_id'], 'id' => $res[$id]['id']]);
1586
1587 //Run applications update
1588 $cnx->executeUpdate($areq, ['nid' => $res[$id]['t_id'], 'id' => $res[$id]['id']]);
1589 }
1590
1591 //Update to new id
1592 foreach($res as $id => $data) {
1593 //Run session update
1594 $cnx->executeUpdate($sreq, ['nid' => $res[$id]['n_id'], 'id' => $res[$id]['t_id']]);
1595
1596 //Run applications update
1597 $cnx->executeUpdate($areq, ['nid' => $res[$id]['n_id'], 'id' => $res[$id]['t_id']]);
1598 }
1599
1600 //Restore foreign key checks
1601 $cnx->prepare('SET foreign_key_checks = 1')->execute();
1602
1603 //Commit transaction
1604 $cnx->commit();
1605
1606 //Set update auto_increment request
1607 $ireq = <<<SQL
1608 ALTER TABLE RapsysAirBundle:Session
1609 auto_increment = 1
1610 SQL;
1611
1612 //Replace bundle entity name by table name
1613 $ireq = str_replace(array_keys($tables), array_values($tables), $ireq);
1614
1615 //Reset auto_increment
1616 $cnx->exec($ireq);
1617 //Without changes
1618 } else {
1619 //Rollback transaction
1620 $cnx->rollback();
1621 }
1622 } catch(\Exception $e) {
1623 //Rollback transaction
1624 $cnx->rollback();
1625
1626 //Throw exception
1627 throw $e;
1628 }
1629
1630 //Return success
1631 return true;
1632 }
1633 }