<?php declare(strict_types=1); /* * This file is part of the Rapsys AirBundle package. * * (c) Raphaël Gertz <symfony@rapsys.eu> * * For the full copyright and license information, please view the LICENSE * file that was distributed with this source code. */ namespace Rapsys\AirBundle\Repository; use Doctrine\ORM\Query\ResultSetMapping; use Symfony\Component\Routing\Generator\UrlGeneratorInterface; use Symfony\Component\Routing\RouterInterface; /** * LocationRepository * * @TODO: use new window function syntax https://mariadb.com/kb/en/window-functions-overview/ MAX(updated) OVER (PARTITION updated) AS modified ??? */ class LocationRepository extends Repository { /** * Find locations * * @return array */ public function findAll(): array { //Get all locations index by id return $this->createQueryBuilder('location', 'location.id')->getQuery()->getResult(); } /** * Find locations as array * * @param DatePeriod $period The period * @return array The locations array */ public function findAllAsArray(\DatePeriod $period): array { //Set the request //TODO: ajouter pays ??? $req = <<<SQL SELECT l.id, l.title, l.latitude, l.longitude, l.indoor, l.updated FROM RapsysAirBundle:Location AS l LEFT JOIN RapsysAirBundle:Session AS s ON (l.id = s.location_id) GROUP BY l.id ORDER BY COUNT(IF(s.date BETWEEN :begin AND :end, s.id, NULL)) DESC, COUNT(s.id) DESC, l.id SQL; //Replace bundle entity name by table name $req = str_replace($this->tableKeys, $this->tableValues, $req); //Get result set mapping instance //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php $rsm = new ResultSetMapping(); //Declare all fields //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php //addScalarResult($sqlColName, $resColName, $type = 'string'); $rsm->addScalarResult('id', 'id', 'integer') ->addScalarResult('title', 'title', 'string') ->addScalarResult('latitude', 'latitude', 'float') ->addScalarResult('longitude', 'longitude', 'float') ->addScalarResult('indoor', 'indoor', 'boolean') ->addScalarResult('count', 'count', 'integer') ->addScalarResult('updated', 'updated', 'datetime'); //Get result $result = $this->_em ->createNativeQuery($req, $rsm) ->setParameter('begin', $period->getStartDate()) ->setParameter('end', $period->getEndDate()) ->getArrayResult(); //Set return $return = []; //Iterate on each city foreach($result as $data) { //Add to return $return[] = [ 'id' => $data['id'], 'title' => $title = $this->translator->trans($data['title']), 'latitude' => $data['latitude'], 'longitude' => $data['longitude'], 'updated' => $data['updated'], //XXX: Useless ??? 'slug' => $location = $this->slugger->slug($title), 'link' => $this->router->generate('rapsys_air_location_view', ['id' => $data['id'], 'location' => $this->slugger->slug($location)]) ]; } //Return return return $return; } /** * Find cities as array * * @param DatePeriod $period The period * @param int $count The session count * @return array The cities array */ public function findCitiesAsArray(\DatePeriod $period, int $count = 1): array { //Set the request $req = <<<SQL SELECT SUBSTRING(a.zipcode, 1, 2) AS id, a.city AS city, ROUND(AVG(a.latitude), 6) AS latitude, ROUND(AVG(a.longitude), 6) AS longitude, GROUP_CONCAT(a.id ORDER BY a.pcount DESC, a.count DESC, a.id SEPARATOR "\\n") AS ids, GROUP_CONCAT(a.title ORDER BY a.pcount DESC, a.count DESC, a.id SEPARATOR "\\n") AS titles, GROUP_CONCAT(a.latitude ORDER BY a.pcount DESC, a.count DESC, a.id SEPARATOR "\\n") AS latitudes, GROUP_CONCAT(a.longitude ORDER BY a.pcount DESC, a.count DESC, a.id SEPARATOR "\\n") AS longitudes, GROUP_CONCAT(a.indoor ORDER BY a.pcount DESC, a.count DESC, a.id SEPARATOR "\\n") AS indoors, GROUP_CONCAT(a.count ORDER BY a.pcount DESC, a.count DESC, a.id SEPARATOR "\\n") AS counts, MAX(a.updated) AS modified FROM ( SELECT l.id, l.city, l.title, l.latitude, l.longitude, l.indoor, l.updated, l.zipcode, COUNT(s.id) AS count, COUNT(IF(s.date BETWEEN :begin AND :end, s.id, NULL)) AS pcount FROM RapsysAirBundle:Location AS l LEFT JOIN RapsysAirBundle:Session AS s ON (l.id = s.location_id) GROUP BY l.id ORDER BY NULL LIMIT 0, :limit ) AS a GROUP BY a.city, SUBSTRING(a.zipcode, 1, 3) ORDER BY a.city, SUBSTRING(a.zipcode, 1, 3) SQL; //Replace bundle entity name by table name $req = str_replace($this->tableKeys, $this->tableValues, $req); //Get result set mapping instance //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php $rsm = new ResultSetMapping(); //Declare all fields //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php //addScalarResult($sqlColName, $resColName, $type = 'string'); $rsm->addScalarResult('id', 'id', 'integer') ->addScalarResult('city', 'city', 'string') ->addScalarResult('latitude', 'latitude', 'float') ->addScalarResult('longitude', 'longitude', 'float') //XXX: is a string because of \n separator ->addScalarResult('ids', 'ids', 'string') //XXX: is a string because of \n separator ->addScalarResult('titles', 'titles', 'string') //XXX: is a string because of \n separator ->addScalarResult('latitudes', 'latitudes', 'string') //XXX: is a string because of \n separator ->addScalarResult('longitudes', 'longitudes', 'string') //XXX: is a string because of \n separator ->addScalarResult('indoors', 'indoors', 'string') //XXX: is a string because of \n separator ->addScalarResult('counts', 'counts', 'string') ->addScalarResult('modified', 'modified', 'datetime') ->addIndexByScalar('city'); //Get result $result = $this->_em ->createNativeQuery($req, $rsm) ->setParameter('begin', $period->getStartDate()) ->setParameter('end', $period->getEndDate()) ->getArrayResult(); //Set return $return = []; //Iterate on each city foreach($result as $city => $data) { //Set titles $titles = explode("\n", $data['titles']); //Set latitudes $latitudes = explode("\n", $data['latitudes']); //Set longitudes $longitudes = explode("\n", $data['longitudes']); //Set indoors $indoors = explode("\n", $data['indoors']); //Set counts $counts = explode("\n", $data['counts']); //With unsufficient count if ($count && $counts[0] < $count) { //Skip empty city //XXX: count are sorted so only check first continue; } //Set locations $data['locations'] = []; //Iterate on each location foreach(explode("\n", $data['ids']) as $k => $id) { //With unsufficient count if ($count && $counts[$k] < $count) { //Skip empty city //XXX: count are sorted so only check first continue; } //Add location $data['locations'][] = [ 'id' => $id, 'title' => $location = $this->translator->trans($titles[$k]), 'latitude' => floatval($latitudes[$k]), 'longitude' => floatval($longitudes[$k]), 'indoor' => $indoors[$k] == 0 ? $this->translator->trans('outdoor') : $this->translator->trans('indoor'), 'link' => $this->router->generate('rapsys_air_location_view', ['id' => $id, 'location' => $this->slugger->slug($location)]) ]; } //Add to return $return[$city] = [ 'id' => $data['id'], 'city' => $data['city'], 'in' => $this->translator->trans('in '.$data['city']), 'indoors' => array_map(function ($v) { return $v == 0 ? $this->translator->trans('outdoor') : $this->translator->trans('indoor'); }, array_unique($indoors)), 'multimap' => $this->translator->trans($data['city'].' sector map'), 'latitude' => $data['latitude'], 'longitude' => $data['longitude'], 'modified' => $data['modified'], //XXX: Useless ??? 'slug' => $city = $this->slugger->slug($data['city']), 'link' => $this->router->generate('rapsys_air_city_view', ['city' => $city, 'latitude' => $data['latitude'], 'longitude' => $data['longitude']]), 'locations' => $data['locations'] ]; } //Return return return $return; } /** * Find city by latitude and longitude as array * * @param float $latitude The latitude * @param float $longitude The longitude * @return ?array The cities array */ public function findCityByLatitudeLongitudeAsArray(float $latitude, float $longitude): ?array { //Set the request $req = <<<SQL SELECT SUBSTRING(l.zipcode, 1, 2) AS id, l.city AS city, ROUND(AVG(l.latitude), 6) AS latitude, ROUND(AVG(l.longitude), 6) AS longitude, MAX(l.updated) AS updated FROM RapsysAirBundle:Location AS l GROUP BY city, SUBSTRING(l.zipcode, 1, 3) ORDER BY ACOS(SIN(RADIANS(:latitude))*SIN(RADIANS(l.latitude))+COS(RADIANS(:latitude))*COS(RADIANS(l.latitude))*COS(RADIANS(:longitude - l.longitude)))*40030.17/2/PI() LIMIT 0, 1 SQL; //Replace bundle entity name by table name $req = str_replace($this->tableKeys, $this->tableValues, $req); //Get result set mapping instance //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php $rsm = new ResultSetMapping(); //Declare all fields //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php //addScalarResult($sqlColName, $resColName, $type = 'string'); $rsm->addScalarResult('id', 'id', 'integer') ->addScalarResult('city', 'city', 'string') ->addScalarResult('latitude', 'latitude', 'float') ->addScalarResult('longitude', 'longitude', 'float') ->addScalarResult('updated', 'updated', 'datetime') ->addIndexByScalar('city'); //Get result $result = $this->_em ->createNativeQuery($req, $rsm) ->setParameter('latitude', $latitude) ->setParameter('longitude', $longitude) ->getOneOrNullResult(); //Without result if ($result === null) { //Return result return $result; } //Return result return [ 'id' => $result['id'], 'city' => $result['city'], 'latitude' => $result['latitude'], 'longitude' => $result['longitude'], 'updated' => $result['updated'], 'in' => $this->translator->trans('in '.$result['city']), 'multimap' => $this->translator->trans($result['city'].' sector map'), //XXX: Useless ??? 'slug' => $slug = $this->slugger->slug($result['city']), 'link' => $this->router->generate('rapsys_air_city_view', ['city' => $slug, 'latitude' => $result['latitude'], 'longitude' => $result['longitude']]) ]; } /** * Find locations by latitude and longitude sorted by period as array * * @TODO: find all other locations when current one has no sessions ??? * * @param float $latitude The latitude * @param float $longitude The longitude * @param DatePeriod $period The period * @param int $count The session count * @param float $distance The distance * @return array The locations array */ public function findAllByLatitudeLongitudeAsArray(float $latitude, float $longitude, \DatePeriod $period, int $count = 1, float $distance = 15): array { //Set earth radius $radius = 40030.17/2/pi(); //Compute min latitude $minlat = min(rad2deg(asin(sin(deg2rad($latitude))*cos($distance/$radius) + cos(deg2rad($latitude))*sin($distance/$radius)*cos(deg2rad(180)))), $latitude); //Compute max latitude $maxlat = max(rad2deg(asin(sin(deg2rad($latitude))*cos($distance/$radius) + cos(deg2rad($latitude))*sin($distance/$radius)*cos(deg2rad(0)))), $latitude); //Compute min longitude $minlong = fmod((rad2deg((deg2rad($longitude) + atan2(sin(deg2rad(-90))*sin($distance/$radius)*cos(deg2rad($minlat)), cos($distance/$radius) - sin(deg2rad($minlat)) * sin(deg2rad($minlat))))) + 180), 360) - 180; //Compute max longi $maxlong = fmod((rad2deg((deg2rad($longitude) + atan2(sin(deg2rad(90))*sin($distance/$radius)*cos(deg2rad($maxlat)), cos($distance/$radius) - sin(deg2rad($maxlat)) * sin(deg2rad($maxlat))))) + 180), 360) - 180; //Set the request //TODO: see old request before commit to sort session count, distance and then by id ? //TODO: see to sort by future session count, historical session count, distance and then by id ? //TODO: do the same for cities and city ? $req = <<<SQL SELECT a.id, a.title, a.latitude, a.longitude, a.updated, MAX(a.updated) AS modified, COUNT(s.id) AS count FROM ( SELECT l.id, l.title, l.latitude, l.longitude, l.updated FROM RapsysAirBundle:Location AS l WHERE l.latitude BETWEEN :minlat AND :maxlat AND l.longitude BETWEEN :minlong AND :maxlong LIMIT 0, :limit ) AS a LEFT JOIN RapsysAirBundle:Session s ON (s.location_id = a.id) GROUP BY a.id ORDER BY COUNT(IF(s.date BETWEEN :begin AND :end, s.id, NULL)) DESC, count DESC, a.id SQL; //Replace bundle entity name by table name $req = str_replace($this->tableKeys, $this->tableValues, $req); //Get result set mapping instance //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php $rsm = new ResultSetMapping(); //Declare all fields //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php //addScalarResult($sqlColName, $resColName, $type = 'string'); $rsm->addScalarResult('id', 'id', 'integer') ->addScalarResult('title', 'title', 'string') ->addScalarResult('latitude', 'latitude', 'float') ->addScalarResult('longitude', 'longitude', 'float') ->addScalarResult('updated', 'updated', 'datetime') ->addScalarResult('modified', 'modified', 'datetime') ->addScalarResult('count', 'count', 'integer'); //Get result $result = $this->_em ->createNativeQuery($req, $rsm) ->setParameter('begin', $period->getStartDate()) ->setParameter('end', $period->getEndDate()) ->setParameter('minlat', $minlat) ->setParameter('maxlat', $maxlat) ->setParameter('minlong', $minlong) ->setParameter('maxlong', $maxlong) ->getArrayResult(); //Set return $return = []; //Iterate on each location foreach($result as $id => $data) { //With active locations if ($count && $data['count'] < $count) { //Skip unactive locations continue; } //Add location $return[$id] = [ 'id' => $data['id'], 'title' => $title = $this->translator->trans($data['title']), 'latitude' => $data['latitude'], 'longitude' => $data['longitude'], 'updated' => $data['updated'], 'modified' => $data['modified'], 'count' => $data['count'], 'slug' => $slug = $this->slugger->slug($title), 'link' => $this->router->generate('rapsys_air_location_view', ['id' => $data['id'], 'location' => $slug]) ]; } //Return return return $return; } /** * Find locations by user id sorted by period as array * * @param int $userId The user id * @param DatePeriod $period The period * @return array The locations array */ public function findAllByUserIdAsArray(int $userId, \DatePeriod $period, $distance = 15): array { //Set the request //TODO: ajouter pays ??? $req = <<<SQL SELECT a.id, a.title, a.city, a.latitude, a.longitude, a.updated, a.modified, a.pcount, COUNT(s3.id) AS tcount FROM ( SELECT b.id, b.title, b.city, b.latitude, b.longitude, b.updated, COUNT(s2.id) AS pcount, MAX(b.updated) AS modified FROM ( SELECT l2.id, l2.city, l2.title, l2.latitude, l2.longitude, l2.updated FROM ( SELECT l.id, l.latitude, l.longitude FROM applications AS a JOIN sessions AS s ON (s.id = a.session_id) JOIN locations AS l ON (l.id = s.location_id) WHERE a.user_id = :id GROUP BY l.id ORDER BY NULL LIMIT 0, :limit ) AS a JOIN locations AS l2 WHERE ACOS(SIN(RADIANS(a.latitude))*SIN(RADIANS(l2.latitude))+COS(RADIANS(a.latitude))*COS(RADIANS(l2.latitude))*COS(RADIANS(a.longitude - l2.longitude)))*40030.17/2/PI() BETWEEN 0 AND :distance GROUP BY l2.id ORDER BY NULL LIMIT 0, :limit ) AS b LEFT JOIN sessions AS s2 ON (s2.location_id = b.id AND s2.date BETWEEN :begin AND :end) GROUP BY b.id ORDER BY NULL LIMIT 0, :limit ) AS a LEFT JOIN sessions AS s3 ON (s3.location_id = a.id) GROUP BY a.id ORDER BY pcount DESC, tcount DESC, a.id SQL; //Replace bundle entity name by table name $req = str_replace($this->tableKeys, $this->tableValues, $req); //Get result set mapping instance //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php $rsm = new ResultSetMapping(); //Declare all fields //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php //addScalarResult($sqlColName, $resColName, $type = 'string'); $rsm->addScalarResult('id', 'id', 'integer') ->addScalarResult('title', 'title', 'string') ->addScalarResult('city', 'city', 'string') ->addScalarResult('latitude', 'latitude', 'float') ->addScalarResult('longitude', 'longitude', 'float') ->addScalarResult('updated', 'updated', 'datetime') ->addScalarResult('pcount', 'pcount', 'integer') ->addScalarResult('tcount', 'tcount', 'integer') ->addScalarResult('modified', 'modified', 'datetime'); //Get result $result = $this->_em ->createNativeQuery($req, $rsm) ->setParameter('begin', $period->getStartDate()) ->setParameter('end', $period->getEndDate()) ->setParameter('id', $userId) ->setParameter('distance', $distance) ->getArrayResult(); //Set return $return = []; //Iterate on each location foreach($result as $id => $data) { //With active locations if (!empty($result[0]['tcount']) && empty($data['tcount'])) { //Skip unactive locations break; } //Add location $return[$id] = [ 'id' => $data['id'], 'city' => $data['city'], 'title' => $title = $this->translator->trans($data['title']), 'at' => $this->translator->trans('at '.$data['title']), 'miniature' => $this->translator->trans($data['title'].' miniature'), 'latitude' => $data['latitude'], 'longitude' => $data['longitude'], 'updated' => $data['updated'], 'pcount' => $data['pcount'], 'tcount' => $data['tcount'], 'modified' => $data['modified'], 'slug' => $slug = $this->slugger->slug($title), 'link' => $this->router->generate('rapsys_air_location_view', ['id' => $data['id'], 'location' => $slug]) ]; } //Return return return $return; } /** * Find location as array by id * * @param int $id The location id * @param string $locale The locale * @return array The location data */ public function findOneByIdAsArray(int $id, string $locale): ?array { //Set the request $req = <<<SQL SELECT l.id, l.title, l.city, l.latitude, l.longitude, l.indoor, l.zipcode, MAX(l2.updated) AS updated, SUBSTRING(l.zipcode, 1, 2) AS city_id, ROUND(AVG(l2.latitude), 6) AS city_latitude, ROUND(AVG(l2.longitude), 6) AS city_longitude FROM RapsysAirBundle:Location AS l JOIN RapsysAirBundle:Location AS l2 ON (l2.city = l.city AND SUBSTRING(l.zipcode, 1, 3) = SUBSTRING(l.zipcode, 1, 3)) WHERE l.id = :id GROUP BY l.id LIMIT 0, 1 SQL; //Replace bundle entity name by table name $req = str_replace($this->tableKeys, $this->tableValues, $req); //Get result set mapping instance //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php $rsm = new ResultSetMapping(); //Declare all fields //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php //addScalarResult($sqlColName, $resColName, $type = 'string'); $rsm->addScalarResult('id', 'id', 'integer') ->addScalarResult('title', 'title', 'string') ->addScalarResult('city', 'city', 'string') ->addScalarResult('latitude', 'latitude', 'float') ->addScalarResult('longitude', 'longitude', 'float') ->addScalarResult('indoor', 'indoor', 'boolean') ->addScalarResult('zipcode', 'zipcode', 'string') ->addScalarResult('updated', 'updated', 'datetime') ->addScalarResult('city_id', 'city_id', 'integer') ->addScalarResult('city_latitude', 'city_latitude', 'float') ->addScalarResult('city_longitude', 'city_longitude', 'float') ->addIndexByScalar('id'); //Get result $result = $this->_em ->createNativeQuery($req, $rsm) ->setParameter('id', $id) ->getOneOrNullResult(); //Without result if ($result === null) { //Return result return $result; } //Set alternates $result['alternates'] = []; //Set route $route = 'rapsys_air_location_view'; //Set route params $routeParams = ['id' => $id]; //Iterate on each languages foreach($this->languages as $languageId => $language) { //Without current locale if ($languageId !== $locale) { //Set titles $titles = []; //Set route params locale $routeParams['_locale'] = $languageId; //Set route params location $routeParams['location'] = $this->slugger->slug($this->translator->trans($result['title'], [], null, $languageId)); //Iterate on each locales foreach(array_keys($this->languages) as $other) { //Without other locale if ($other !== $languageId) { //Set other locale title $titles[$other] = $this->translator->trans($language, [], null, $other); } } //Add alternates locale $result['alternates'][substr($languageId, 0, 2)] = $result['alternates'][str_replace('_', '-', $languageId)] = [ 'absolute' => $this->router->generate($route, $routeParams, UrlGeneratorInterface::ABSOLUTE_URL), 'relative' => $this->router->generate($route, $routeParams), 'title' => implode('/', $titles), 'translated' => $this->translator->trans($language, [], null, $languageId) ]; } } //Return result return [ 'id' => $result['id'], 'city' => [ 'id' => $result['city_id'], 'title' => $result['city'], 'in' => $this->translator->trans('in '.$result['city']), 'link' => $this->router->generate('rapsys_air_city_view', ['city' => $result['city'], 'latitude' => $result['city_latitude'], 'longitude' => $result['city_longitude']]) ], 'title' => $title = $this->translator->trans($result['title']), 'latitude' => $result['latitude'], 'longitude' => $result['longitude'], 'indoor' => $result['indoor'], 'updated' => $result['updated'], 'around' => $this->translator->trans('around '.$result['title']), 'at' => $this->translator->trans('at '.$result['title']), 'atin' => $this->translator->trans('at '.$result['title']).' '.$this->translator->trans('in '.$result['city']), 'multimap' => $this->translator->trans($result['title'].' sector map'), //XXX: Useless ??? 'slug' => $slug = $this->slugger->slug($title), 'link' => $this->router->generate($route, ['_locale' => $locale, 'location' => $slug]+$routeParams), 'alternates' => $result['alternates'] ]; } /** * Find complementary locations by session id * * @param int $id The session id * @return array The other locations */ public function findComplementBySessionId(int $id): array { //Fetch complement locations $ret = $this->getEntityManager() ->createQuery('SELECT l.id, l.title FROM RapsysAirBundle:Session s LEFT JOIN RapsysAirBundle:Session s2 WITH s2.id != s.id AND s2.slot = s.slot AND s2.date = s.date LEFT JOIN RapsysAirBundle:Location l WITH l.id != s.location AND (l.id != s2.location OR s2.location IS NULL) WHERE s.id = :sid GROUP BY l.id ORDER BY l.id') ->setParameter('sid', $id) ->getArrayResult(); //TODO: try to improve with: #->addIndexByScalar('city'); //Rekey array $ret = array_column($ret, 'id', 'title'); return $ret; } /** * Find locations by user id * * @param int $id The user id * @return array The user locations */ public function findByUserId(int $userId): array { //Set the request $req = 'SELECT l.id, l.title FROM RapsysAirBundle:UserLocation AS ul JOIN RapsysAirBundle:Location AS l ON (l.id = ul.location_id) WHERE ul.user_id = :id'; //Replace bundle entity name by table name $req = str_replace($this->tableKeys, $this->tableValues, $req); //Get result set mapping instance //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php $rsm = new ResultSetMapping(); //Declare result set for our request $rsm->addEntityResult('RapsysAirBundle:Location', 'l') ->addFieldResult('l', 'id', 'id') ->addFieldResult('l', 'title', 'title'); //Send result return $this->_em ->createNativeQuery($req, $rsm) ->setParameter('id', $userId) ->getResult(); } }