X-Git-Url: https://git.rapsys.eu/.gitweb.cgi/airbundle/blobdiff_plain/2a3cc761b2af51a241ffebd8d57b186f6b25849c..HEAD:/Repository/SessionRepository.php diff --git a/Repository/SessionRepository.php b/Repository/SessionRepository.php index 971b227..c310b54 100644 --- a/Repository/SessionRepository.php +++ b/Repository/SessionRepository.php @@ -106,7 +106,7 @@ SELECT p.profile AS p_profile, p.rate AS p_rate, p.hat AS p_hat, - GREATEST(COALESCE(s.updated, 0), COALESCE(l.updated, 0), COALESCE(t.updated, 0), COALESCE(p.updated, 0), COALESCE(MAX(sa.updated), 0), COALESCE(MAX(sau.updated), 0), COALESCE(MAX(sad.updated), 0)) AS modified, + GREATEST(s.created, s.updated, l.created, l.updated, t.created, t.updated, COALESCE(a.created, '1970-01-01'), COALESCE(a.updated, '1970-01-01'), COALESCE(ad.created, '1970-01-01'), COALESCE(ad.updated, '1970-01-01'), COALESCE(au.created, '1970-01-01'), COALESCE(au.updated, '1970-01-01'), COALESCE(p.created, '1970-01-01'), COALESCE(p.updated, '1970-01-01'), MAX(GREATEST(COALESCE(sa.created, '1970-01-01'), COALESCE(sa.updated, '1970-01-01'), COALESCE(sad.created, '1970-01-01'), COALESCE(sad.updated, '1970-01-01'), COALESCE(sau.created, '1970-01-01'), COALESCE(sau.updated, '1970-01-01')))) AS modified, GROUP_CONCAT(sa.id ORDER BY sa.user_id SEPARATOR "\\n") AS sa_id, GROUP_CONCAT(IFNULL(sa.score, 'NULL') ORDER BY sa.user_id SEPARATOR "\\n") AS sa_score, GROUP_CONCAT(sa.created ORDER BY sa.user_id SEPARATOR "\\n") AS sa_created, @@ -114,16 +114,16 @@ SELECT GROUP_CONCAT(IFNULL(sa.canceled, 'NULL') ORDER BY sa.user_id SEPARATOR "\\n") AS sa_canceled, GROUP_CONCAT(sa.user_id ORDER BY sa.user_id SEPARATOR "\\n") AS sau_id, GROUP_CONCAT(sau.pseudonym ORDER BY sa.user_id SEPARATOR "\\n") AS sau_pseudonym -FROM RapsysAirBundle:Session AS s -JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id) -JOIN RapsysAirBundle:Slot AS t ON (t.id = s.slot_id) -LEFT JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id) -LEFT JOIN RapsysAirBundle:Dance AS ad ON (ad.id = a.dance_id) -LEFT JOIN RapsysAirBundle:User AS au ON (au.id = a.user_id) -LEFT JOIN RapsysAirBundle:Snippet AS p ON (p.location_id = s.location_id AND p.user_id = a.user_id AND p.locale = :locale) -LEFT JOIN RapsysAirBundle:Application AS sa ON (sa.session_id = s.id) -LEFT JOIN RapsysAirBundle:User AS sau ON (sau.id = sa.user_id) -LEFT JOIN RapsysAirBundle:Dance AS sad ON (sad.id = sa.dance_id) +FROM Rapsys\AirBundle\Entity\Session AS s +JOIN Rapsys\AirBundle\Entity\Location AS l ON (l.id = s.location_id) +JOIN Rapsys\AirBundle\Entity\Slot AS t ON (t.id = s.slot_id) +LEFT JOIN Rapsys\AirBundle\Entity\Application AS a ON (a.id = s.application_id) +LEFT JOIN Rapsys\AirBundle\Entity\Dance AS ad ON (ad.id = a.dance_id) +LEFT JOIN Rapsys\AirBundle\Entity\User AS au ON (au.id = a.user_id) +LEFT JOIN Rapsys\AirBundle\Entity\Snippet AS p ON (p.locale = :locale AND p.location_id = s.location_id AND p.user_id = a.user_id) +LEFT JOIN Rapsys\AirBundle\Entity\Application AS sa ON (sa.session_id = s.id) +LEFT JOIN Rapsys\AirBundle\Entity\Dance AS sad ON (sad.id = sa.dance_id) +LEFT JOIN Rapsys\AirBundle\Entity\User AS sau ON (sau.id = sa.user_id) WHERE s.id = :id GROUP BY s.id ORDER BY NULL @@ -213,7 +213,7 @@ SQL; } //Set route - $route = 'rapsys_air_session_view'; + $route = 'rapsysair_session_view'; //Set route params $routeParams = ['id' => $id, 'location' => $this->slugger->slug($this->translator->trans($result['l_title']))]; @@ -255,7 +255,7 @@ SQL; 'longitude' => $result['l_longitude'], 'indoor' => $result['l_indoor'], 'slug' => $routeParams['location'], - 'link' => $this->router->generate('rapsys_air_location_view', ['id' => $result['l_id'], 'location' => $routeParams['location']]) + 'link' => $this->router->generate('rapsysair_location_view', ['id' => $result['l_id'], 'location' => $routeParams['location']]) ], 'slot' => [ 'id' => $result['t_id'], @@ -275,15 +275,15 @@ SQL; 'name' => $this->translator->trans($result['ad_name']), 'type' => $this->translator->trans($result['ad_type']), 'slug' => $routeParams['dance'] = $this->slugger->slug($this->translator->trans($result['ad_name'].' '.lcfirst($result['ad_type']))), - 'link' => $this->router->generate('rapsys_air_dance_view', ['id' => $result['ad_id'], 'name' => $this->slugger->slug($this->translator->trans($result['ad_name'])), 'type' => $this->slugger->slug($this->translator->trans($result['ad_type']))]) + 'link' => $this->router->generate('rapsysair_dance_view', ['id' => $result['ad_id'], 'name' => $this->slugger->slug($this->translator->trans($result['ad_name'])), 'type' => $this->slugger->slug($this->translator->trans($result['ad_type']))]) ], 'user' => [ 'id' => $result['au_id'], 'by' => $this->translator->trans('by %pseudonym%', [ '%pseudonym%' => $result['au_pseudonym'] ]), 'title' => $result['au_pseudonym'], 'slug' => $routeParams['user'] = $this->slugger->slug($result['au_pseudonym']), - 'link' => $result['au_id'] == 1 && $routeParams['user'] == 'milonga-raphael' ? $this->router->generate('rapsys_air_user_milongaraphael') : $this->router->generate('rapsys_air_user_view', ['id' => $result['au_id'], 'user' => $routeParams['user']]), - 'contact' => $this->router->generate('rapsys_air_contact', ['id' => $result['au_id'], 'user' => $routeParams['user']]) + 'link' => $result['au_id'] == 1 && $routeParams['user'] == 'milonga-raphael' ? $this->router->generate('rapsysair_user_milongaraphael') : $this->router->generate('rapsysair_user_view', ['id' => $result['au_id'], 'user' => $routeParams['user']]), + 'contact' => $this->router->generate('rapsysair_contact', ['id' => $result['au_id'], 'user' => $routeParams['user']]) ], 'id' => $result['a_id'], 'canceled' => $result['a_canceled'] @@ -440,7 +440,7 @@ SQL; //XXX: get every location between 0 and 15 km of latitude and longitude $req = <<addScalarResult('p_rate', 'p_rate', 'integer') ->addScalarResult('p_short', 'p_short', 'string') //XXX: is a string because of \n separator - ->addScalarResult('sau_id', 'sau_id', 'string') - //XXX: is a string because of \n separator - ->addScalarResult('sau_pseudonym', 'sau_pseudonym', 'string') - //XXX: is a string because of \n separator ->addScalarResult('sad_id', 'sad_id', 'string') //XXX: is a string because of \n separator ->addScalarResult('sad_name', 'sad_name', 'string') //XXX: is a string because of \n separator ->addScalarResult('sad_type', 'sad_type', 'string') + //XXX: is a string because of \n separator + ->addScalarResult('sau_id', 'sau_id', 'string') + //XXX: is a string because of \n separator + ->addScalarResult('sau_pseudonym', 'sau_pseudonym', 'string') ->addIndexByScalar('id'); //Fetch result @@ -647,7 +647,7 @@ SQL; $month = null; //Set route - $route = 'rapsys_air_session_view'; + $route = 'rapsysair_session_view'; //Iterate on each day foreach($period as $date) { @@ -943,11 +943,11 @@ SQL; * @param DateTime $synchronized The synchronized datetime * @return array The session data */ - public function findAllByUserIdSynchronized(int $userId, \DateTime $synchronized): array { + public function findAllByUserIdSynchronized(int $userId, \DateTime $synchronized = new \DateTime('1970-01-01')): array { //Set the request $req = <<addScalarResult('user_id', 'user_id', 'integer'); + $rsm->addScalarResult('subscribed_id', 'subscribed_id', 'integer'); //Set subscription sql part $subscriptionSql = ''; @@ -1010,7 +1010,7 @@ SELECT s.id, s.date, s.locked, - s.updated, + GREATEST(s.created, s.updated, l.created, l.updated, a.created, a.updated, ad.created, ad.updated, au.created, au.updated, COALESCE(p.created, '1970-01-01'), COALESCE(p.updated, '1970-01-01')) AS modified, ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS start, ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS stop, s.location_id AS l_id, @@ -1038,13 +1038,13 @@ SELECT p.donate AS p_donate, p.link AS p_link, p.profile AS p_profile -FROM RapsysAirBundle:Session AS s -JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id) -JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id${danceSql}${subscriptionSql}) -JOIN RapsysAirBundle:Dance AS ad ON (ad.id = a.dance_id) -JOIN RapsysAirBundle:User AS au ON (au.id = a.user_id) -LEFT JOIN RapsysAirBundle:Snippet AS p ON (p.location_id = s.location_id AND p.user_id = a.user_id AND p.locale = :locale) -WHERE GREATEST(s.created, s.updated, a.created, a.updated, ADDTIME(ADDTIME(s.date, s.begin), s.length)) >= :synchronized +FROM Rapsys\AirBundle\Entity\Session AS s +JOIN Rapsys\AirBundle\Entity\Location AS l ON (l.id = s.location_id) +JOIN Rapsys\AirBundle\Entity\Application AS a ON (a.id = s.application_id{$danceSql}{$subscriptionSql}) +JOIN Rapsys\AirBundle\Entity\Dance AS ad ON (ad.id = a.dance_id) +JOIN Rapsys\AirBundle\Entity\User AS au ON (au.id = a.user_id) +LEFT JOIN Rapsys\AirBundle\Entity\Snippet AS p ON (p.locale = :locale AND p.location_id = s.location_id AND p.user_id = a.user_id) +WHERE GREATEST(GREATEST(s.created, s.updated, l.created, l.updated, a.created, a.updated, ad.created, ad.updated, au.created, au.updated, COALESCE(p.created, '1970-01-01'), COALESCE(p.updated, '1970-01-01')), ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY)) >= :synchronized SQL; //Replace bundle entity name by table name @@ -1060,7 +1060,7 @@ SQL; ->addScalarResult('id', 'id', 'integer') ->addScalarResult('date', 'date', 'date') ->addScalarResult('locked', 'locked', 'datetime') - ->addScalarResult('updated', 'updated', 'datetime') + ->addScalarResult('modified', 'modified', 'datetime') ->addScalarResult('start', 'start', 'datetime') ->addScalarResult('stop', 'stop', 'datetime') ->addScalarResult('l_id', 'l_id', 'integer') @@ -1099,7 +1099,7 @@ SQL; ->setParameter('dids', $userDances) ->setParameter('uids', $userSubscriptions) ->setParameter('synchronized', $synchronized) - ->getArrayResult(); + ->getResult(AbstractQuery::HYDRATE_ARRAY); } /** @@ -1113,7 +1113,7 @@ SQL; public function findOneByLocationSlotDate(Location $location, Slot $slot, \DateTime $date): ?Session { //Return sessions return $this->getEntityManager() - ->createQuery('SELECT s FROM RapsysAirBundle:Session s WHERE (s.location = :location AND s.slot = :slot AND s.date = :date)') + ->createQuery('SELECT s FROM Rapsys\AirBundle\Entity\Session s WHERE (s.location = :location AND s.slot = :slot AND s.date = :date)') ->setParameter('location', $location) ->setParameter('slot', $slot) ->setParameter('date', $date) @@ -1164,12 +1164,12 @@ SELECT p.donate AS p_donate, p.link AS p_link, p.profile AS p_profile -FROM RapsysAirBundle:Session AS s -JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id) -JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id) -JOIN RapsysAirBundle:Dance AS ad ON (ad.id = a.dance_id) -JOIN RapsysAirBundle:User AS au ON (au.id = a.user_id) -LEFT JOIN RapsysAirBundle:Snippet AS p ON (p.location_id = s.location_id AND p.user_id = a.user_id AND p.locale = :locale) +FROM Rapsys\AirBundle\Entity\Session AS s +JOIN Rapsys\AirBundle\Entity\Location AS l ON (l.id = s.location_id) +JOIN Rapsys\AirBundle\Entity\Application AS a ON (a.id = s.application_id) +JOIN Rapsys\AirBundle\Entity\Dance AS ad ON (ad.id = a.dance_id) +JOIN Rapsys\AirBundle\Entity\User AS au ON (au.id = a.user_id) +LEFT JOIN Rapsys\AirBundle\Entity\Snippet AS p ON (p.locale = :locale AND p.location_id = s.location_id AND p.user_id = a.user_id) WHERE s.date BETWEEN :begin AND :end ORDER BY NULL SQL; @@ -1239,8 +1239,8 @@ SQL; //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) $req = <<= 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)) SQL; @@ -1252,7 +1252,7 @@ SQL; //Declare all fields $rsm - ->addEntityResult('RapsysAirBundle:Session', 's') + ->addEntityResult('Rapsys\AirBundle\Entity\Session', 's') ->addFieldResult('s', 'id', 'id') ->addFieldResult('s', 'date', 'date') ->addFieldResult('s', 'begin', 'begin') @@ -1265,9 +1265,9 @@ SQL; ->addFieldResult('s', 'temperature', 'temperature') ->addFieldResult('s', 'temperaturemin', 'temperaturemin') ->addFieldResult('s', 'temperaturemax', 'temperaturemax') - ->addJoinedEntityResult('RapsysAirBundle:Slot', 'o', 's', 'slot') + ->addJoinedEntityResult('Rapsys\AirBundle\Entity\Slot', 'o', 's', 'slot') ->addFieldResult('o', 'slot_id', 'id') - ->addJoinedEntityResult('RapsysAirBundle:Location', 'l', 's', 'location') + ->addJoinedEntityResult('Rapsys\AirBundle\Entity\Location', 'l', 's', 'location') ->addFieldResult('l', 'location_id', 'id') ->addFieldResult('l', 'zipcode', 'zipcode') ->addIndexBy('s', 'id'); @@ -1288,8 +1288,8 @@ SQL; //XXX: select session stopping after or equal date(now)+3d as accuweather only provide hourly data for the next 3 days (INTERVAL 3 DAY) $req = <<= 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)) SQL; @@ -1301,7 +1301,7 @@ SQL; //Declare all fields $rsm - ->addEntityResult('RapsysAirBundle:Session', 's') + ->addEntityResult('Rapsys\AirBundle\Entity\Session', 's') ->addFieldResult('s', 'id', 'id') ->addFieldResult('s', 'date', 'date') ->addFieldResult('s', 'begin', 'begin') @@ -1314,9 +1314,9 @@ SQL; ->addFieldResult('s', 'temperature', 'temperature') ->addFieldResult('s', 'temperaturemin', 'temperaturemin') ->addFieldResult('s', 'temperaturemax', 'temperaturemax') - ->addJoinedEntityResult('RapsysAirBundle:Slot', 'o', 's', 'slot') + ->addJoinedEntityResult('Rapsys\AirBundle\Entity\Slot', 'o', 's', 'slot') ->addFieldResult('o', 'slot_id', 'id') - ->addJoinedEntityResult('RapsysAirBundle:Location', 'l', 's', 'location') + ->addJoinedEntityResult('Rapsys\AirBundle\Entity\Location', 'l', 's', 'location') ->addFieldResult('l', 'location_id', 'id') ->addFieldResult('l', 'zipcode', 'zipcode') ->addIndexBy('s', 'id'); @@ -1338,9 +1338,9 @@ SQL; //TODO: remonter les données pour le mail ? $req =<<addEntityResult('RapsysAirBundle:Session', 's') + ->addEntityResult('Rapsys\AirBundle\Entity\Session', 's') ->addFieldResult('s', 'id', 'id') ->addIndexBy('s', 'id'); @@ -1462,29 +1462,29 @@ FROM ( s.premium, l.hotspot, a.created - FROM RapsysAirBundle:Session AS s - JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id) - JOIN RapsysAirBundle:Application AS a ON (a.session_id = s.id AND a.canceled IS NULL) - 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) - 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)) + FROM Rapsys\AirBundle\Entity\Session AS s + JOIN Rapsys\AirBundle\Entity\Location AS l ON (l.id = s.location_id) + JOIN Rapsys\AirBundle\Entity\Application AS a ON (a.session_id = s.id AND a.canceled IS NULL) + LEFT JOIN Rapsys\AirBundle\Entity\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) + LEFT JOIN Rapsys\AirBundle\Entity\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)) WHERE s.id = :sid GROUP BY a.id ORDER BY NULL LIMIT 0, :limit ) AS b - 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) - 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)) + LEFT JOIN Rapsys\AirBundle\Entity\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) + LEFT JOIN Rapsys\AirBundle\Entity\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)) GROUP BY b.id ORDER BY NULL LIMIT 0, :limit ) AS c - 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) - 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)) + LEFT JOIN Rapsys\AirBundle\Entity\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) + LEFT JOIN Rapsys\AirBundle\Entity\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)) GROUP BY c.id ORDER BY NULL LIMIT 0, :limit ) AS d - LEFT JOIN RapsysAirBundle:UserGroup AS ug ON (ug.user_id = d.user_id) + LEFT JOIN Rapsys\AirBundle\Entity\UserGroup AS ug ON (ug.user_id = d.user_id) GROUP BY d.id LIMIT 0, :limit ) AS e @@ -1501,7 +1501,7 @@ SQL; $req = str_replace($this->tableKeys, $this->tableValues, $req); //Set update request - $upreq = 'UPDATE RapsysAirBundle:Application SET score = :score, updated = NOW() WHERE id = :id'; + $upreq = 'UPDATE Rapsys\AirBundle\Entity\Application SET score = :score, updated = NOW() WHERE id = :id'; //Replace bundle entity name by table name $upreq = str_replace($this->tableKeys, $this->tableValues, $upreq); @@ -1511,7 +1511,7 @@ SQL; //Declare all fields $rsm - ->addEntityResult('RapsysAirBundle:Application', 'a') + ->addEntityResult('Rapsys\AirBundle\Entity\Application', 'a') ->addFieldResult('a', 'id', 'id') ->addFieldResult('a', 'score', 'score') ->addIndexBy('a', 'id'); @@ -1564,8 +1564,8 @@ FROM ( s.begin, s.slot_id, GROUP_CONCAT(sa.id ORDER BY sa.id SEPARATOR "\\n") AS sa_id - FROM RapsysAirBundle:Session AS s - LEFT JOIN RapsysAirBundle:Application AS sa ON (sa.session_id = s.id) + FROM Rapsys\AirBundle\Entity\Session AS s + LEFT JOIN Rapsys\AirBundle\Entity\Application AS sa ON (sa.session_id = s.id) GROUP BY s.id ORDER BY NULL ) AS a @@ -1597,7 +1597,7 @@ SQL; //Set update session request $sreq = <<