From 2a3cc761b2af51a241ffebd8d57b186f6b25849c Mon Sep 17 00:00:00 2001 From: =?utf8?q?Rapha=C3=ABl=20Gertz?= Date: Wed, 28 Feb 2024 12:33:10 +0100 Subject: [PATCH 1/1] Drop fetchUserCalendarByDatePeriod member function Add findAllByUserIdSynchronized early implementation member function Reorder functions --- Repository/SessionRepository.php | 409 +++++++++++-------------------- 1 file changed, 147 insertions(+), 262 deletions(-) diff --git a/Repository/SessionRepository.php b/Repository/SessionRepository.php index 4e9f6ff..971b227 100644 --- a/Repository/SessionRepository.php +++ b/Repository/SessionRepository.php @@ -12,13 +12,16 @@ namespace Rapsys\AirBundle\Repository; use Doctrine\DBAL\Types\Types; +use Doctrine\ORM\AbstractQuery; use Doctrine\ORM\Query\ResultSetMapping; + use Symfony\Component\Routing\Generator\UrlGeneratorInterface; use Rapsys\AirBundle\Entity\Application; use Rapsys\AirBundle\Entity\Location; use Rapsys\AirBundle\Entity\Session; use Rapsys\AirBundle\Entity\Slot; +use Rapsys\AirBundle\Repository; /** * SessionRepository @@ -932,35 +935,76 @@ SQL; return $calendar; } - /** - * Find session by location, slot and date - * - * @param Location $location The location - * @param Slot $slot The slot - * @param DateTime $date The datetime - * @return ?Session The found session - */ - 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)') - ->setParameter('location', $location) - ->setParameter('slot', $slot) - ->setParameter('date', $date) - ->getSingleResult(); - } /** - * Fetch sessions by date period - * - * @XXX: used in calendar command + * Find sessions by user id and synchronized date time * - * @param DatePeriod $period The date period - * @return array The session array + * @param int $userId The user id + * @param DateTime $synchronized The synchronized datetime + * @return array The session data */ - public function fetchAllByDatePeriod(\DatePeriod $period): array { + public function findAllByUserIdSynchronized(int $userId, \DateTime $synchronized): array { + //Set the request + $req = <<tableKeys, $this->tableValues, $req); + + //Get result set mapping instance + $rsm = new ResultSetMapping(); + + //Declare dance id field + $rsm->addScalarResult('dance_id', 'dance_id', 'integer'); + + //Set dance sql part + $danceSql = ''; + + //With user dance + if ( + $userDances = $this->_em + ->createNativeQuery($req, $rsm) + ->setParameter('uid', $userId) + ->getResult(AbstractQuery::HYDRATE_SCALAR_COLUMN) + ) { + //Set dance sql part + $danceSql = ' AND a.dance_id IN (:dids)'; + } + + //Set the request + $req = <<tableKeys, $this->tableValues, $req); + + //Get result set mapping instance + $rsm = new ResultSetMapping(); + + //Declare user id field + $rsm->addScalarResult('user_id', 'user_id', 'integer'); + + //Set subscription sql part + $subscriptionSql = ''; + + //With user subscription + if ( + $userSubscriptions = $this->_em + ->createNativeQuery($req, $rsm) + ->setParameter('uid', $userId) + ->getResult(AbstractQuery::HYDRATE_SCALAR_COLUMN) + ) { + //Set subscription sql part + $subscriptionSql = ' AND a.user_id IN (:uids)'; + } + //Set the request - //TODO: exclude opera and others ? $req = <<= :synchronized 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') + $rsm + ->addScalarResult('id', 'id', 'integer') ->addScalarResult('date', 'date', 'date') ->addScalarResult('locked', 'locked', 'datetime') ->addScalarResult('updated', 'updated', 'datetime') @@ -1049,72 +1092,85 @@ SQL; ->addScalarResult('p_profile', 'p_profile', 'string') ->addIndexByScalar('id'); - //Fetch result - $res = $this->_em + //Return sessions + //TODO: XXX: finish here + return $this->_em ->createNativeQuery($req, $rsm) - ->setParameter('begin', $period->getStartDate()) - ->setParameter('end', $period->getEndDate()); + ->setParameter('dids', $userDances) + ->setParameter('uids', $userSubscriptions) + ->setParameter('synchronized', $synchronized) + ->getArrayResult(); + } - //Return result - return $res->getResult(); + /** + * Find session by location, slot and date + * + * @param Location $location The location + * @param Slot $slot The slot + * @param DateTime $date The datetime + * @return ?Session The found session + */ + 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)') + ->setParameter('location', $location) + ->setParameter('slot', $slot) + ->setParameter('date', $date) + ->getSingleResult(); } /** - * Fetch sessions calendar with translated location by date period and user + * Fetch sessions by date period + * + * @XXX: used in calendar command * * @param DatePeriod $period The date period - * @param ?int $userId The user id - * @param ?int $sessionId The session id + * @return array The session array */ - public function fetchUserCalendarByDatePeriod(\DatePeriod $period, ?int $userId = null, ?int $sessionId = null): array { - //Init user sql - $userJoinSql = $userWhereSql = ''; - - //When user id is set - if (!empty($userId)) { - //Add user join - $userJoinSql = 'JOIN RapsysAirBundle:Application AS sua ON (sua.session_id = s.id)'."\n"; - //Add user id clause - $userWhereSql = "\n\t".'AND sua.user_id = :uid'; - } - + public function fetchAllByDatePeriod(\DatePeriod $period): array { //Set the request - //TODO: change as_u_* in sau_*, a_u_* in au_*, etc, see request up + //TODO: exclude opera and others ? $req = <<addScalarResult('id', 'id', 'integer') ->addScalarResult('date', 'date', 'date') - ->addScalarResult('rainrisk', 'rainrisk', 'float') - ->addScalarResult('rainfall', 'rainfall', 'float') - ->addScalarResult('realfeel', 'realfeel', 'float') - ->addScalarResult('temperature', 'temperature', 'float') ->addScalarResult('locked', 'locked', 'datetime') + ->addScalarResult('updated', 'updated', 'datetime') ->addScalarResult('start', 'start', 'datetime') ->addScalarResult('stop', 'stop', 'datetime') - ->addScalarResult('t_id', 't_id', 'integer') - ->addScalarResult('t_title', 't_title', 'string') ->addScalarResult('l_id', 'l_id', 'integer') + ->addScalarResult('l_address', 'l_address', 'string') + ->addScalarResult('l_zipcode', 'l_zipcode', 'string') + ->addScalarResult('l_city', 'l_city', 'string') + ->addScalarResult('l_latitude', 'l_latitude', 'float') + ->addScalarResult('l_longitude', 'l_longitude', 'float') ->addScalarResult('l_title', 'l_title', 'string') + ->addScalarResult('l_description', 'l_description', 'string') + ->addScalarResult('t_id', 't_id', 'integer') + ->addScalarResult('t_title', 't_title', 'string') ->addScalarResult('a_id', 'a_id', 'integer') + ->addScalarResult('a_canceled', 'a_canceled', 'datetime') ->addScalarResult('ad_name', 'ad_name', 'string') ->addScalarResult('ad_type', 'ad_type', 'string') ->addScalarResult('au_id', 'au_id', 'integer') + ->addScalarResult('au_forename', 'au_forename', 'string') ->addScalarResult('au_pseudonym', 'au_pseudonym', 'string') + ->addScalarResult('p_id', 'p_id', 'integer') + ->addScalarResult('p_description', 'p_description', 'string') + ->addScalarResult('p_class', 'p_class', 'string') + ->addScalarResult('p_short', 'p_short', 'string') + ->addScalarResult('p_hat', 'p_hat', 'integer') ->addScalarResult('p_rate', 'p_rate', 'integer') - ->addScalarResult('p_hat', 'p_hat', 'boolean') - //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') + ->addScalarResult('p_contact', 'p_contact', 'string') + ->addScalarResult('p_donate', 'p_donate', 'string') + ->addScalarResult('p_link', 'p_link', 'string') + ->addScalarResult('p_profile', 'p_profile', 'string') ->addIndexByScalar('id'); //Fetch result $res = $this->_em ->createNativeQuery($req, $rsm) ->setParameter('begin', $period->getStartDate()) - ->setParameter('end', $period->getEndDate()) - ->setParameter('uid', $userId) - ->getResult(); - - //Init calendar - $calendar = []; - - //Init month - $month = null; - - //Iterate on each day - foreach($period as $date) { - //Init day in calendar - $calendar[$Ymd = $date->format('Ymd')] = [ - 'title' => $this->translator->trans($date->format('l')).' '.$date->format('d'), - 'class' => [], - 'sessions' => [] - ]; - - //Detect month change - if ($month != $date->format('m')) { - $month = $date->format('m'); - //Append month for first day of month - //XXX: except if today to avoid double add - if ($date->format('U') != strtotime('today')) { - $calendar[$Ymd]['title'] .= '/'.$month; - } - } - //Deal with today - if ($date->format('U') == ($today = strtotime('today'))) { - $calendar[$Ymd]['title'] .= '/'.$month; - $calendar[$Ymd]['current'] = true; - $calendar[$Ymd]['class'][] = 'current'; - } - //Disable passed days - if ($date->format('U') < $today) { - $calendar[$Ymd]['disabled'] = true; - $calendar[$Ymd]['class'][] = 'disabled'; - } - //Set next month days - if ($date->format('m') > date('m')) { - $calendar[$Ymd]['next'] = true; - #$calendar[$Ymd]['class'][] = 'next'; - } - - //Detect sunday - if ($date->format('w') == 0) { - $calendar[$Ymd]['class'][] = 'sunday'; - } - - //Iterate on each session to find the one of the day - foreach($res as $session) { - if (($sessionYmd = $session['date']->format('Ymd')) == $Ymd) { - //Count number of application - $count = count(explode("\n", $session['sau_id'])); - - //Compute classes - $class = []; - if (!empty($session['a_id'])) { - $applications = [ $session['au_id'] => $session['au_pseudonym'] ]; - if ($session['au_id'] == $userId) { - $class[] = 'granted'; - } else { - $class[] = 'disputed'; - } - } elseif ($count > 1) { - $class[] = 'disputed'; - } elseif (!empty($session['locked'])) { - $class[] = 'locked'; - } else { - $class[] = 'pending'; - } - - if ($sessionId == $session['id']) { - $class[] = 'highlight'; - } - - //Set temperature - //XXX: realfeel may be null, temperature should not - $temperature = $session['realfeel'] !== null ? $session['realfeel'] : $session['temperature']; - - //Compute weather - //XXX: rainfall may be null - if ($session['rainrisk'] > 0.50 || $session['rainfall'] > 2) { - $weather = self::GLYPHS['Stormy']; - } elseif ($session['rainrisk'] > 0.40 || $session['rainfall'] > 1) { - $weather = self::GLYPHS['Rainy']; - } elseif ($temperature > 24) { - $weather = self::GLYPHS['Cleary']; - } elseif ($temperature > 17) { - $weather = self::GLYPHS['Sunny']; - } elseif ($temperature > 10) { - $weather = self::GLYPHS['Cloudy']; - } elseif ($temperature !== null) { - $weather = self::GLYPHS['Winty']; - } else { - $weather = null; - } - - //Init weathertitle - $weathertitle = []; - - //Check if realfeel is available - if ($session['realfeel'] !== null) { - $weathertitle[] = $session['realfeel'].'°R'; - } - - //Check if temperature is available - if ($session['temperature'] !== null) { - $weathertitle[] = $session['temperature'].'°C'; - } - - //Check if rainrisk is available - if ($session['rainrisk'] !== null) { - $weathertitle[] = ($session['rainrisk']*100).'%'; - } - - //Check if rainfall is available - if ($session['rainfall'] !== null) { - $weathertitle[] = $session['rainfall'].'mm'; - } - - //Set applications - $applications = [ - 0 => $this->translator->trans($session['t_title']).' '.$this->translator->trans('at '.$session['l_title']).$this->translator->trans(':') - ]; - - //Fetch pseudonyms from session applications - $applications += array_combine(explode("\n", $session['sau_id']), array_map(function ($v) {return '- '.$v;}, explode("\n", $session['sau_pseudonym']))); - - //Set dance - $dance = null; - - //Set pseudonym - $pseudonym = null; - - //Check that session is not granted - if (empty($session['a_id'])) { - //With location id and unique application - if ($count == 1) { - //Set unique application pseudonym - $pseudonym = $session['sau_pseudonym']; - } - //Session is granted - } else { - //Replace granted application - $applications[$session['au_id']] = '* '.$session['au_pseudonym']; - - //Set dance - $dance = $this->translator->trans($session['ad_name'].' '.lcfirst($session['ad_type'])); - - //Set pseudonym - $pseudonym = $session['au_pseudonym'].($count > 1 ? ' ['.$count.']':''); - } - - //Set title - $title = $this->translator->trans($session['l_title']).($count > 1 ? ' ['.$count.']':''); - - //Add the session - $calendar[$Ymd]['sessions'][$session['t_id'].sprintf('%02d', $session['l_id'])] = [ - 'id' => $session['id'], - 'start' => $session['start'], - 'stop' => $session['stop'], - 'location' => $this->translator->trans($session['l_title']), - 'dance' => $dance, - 'pseudonym' => $pseudonym, - 'class' => $class, - 'slot' => self::GLYPHS[$session['t_title']], - 'slottitle' => $this->translator->trans($session['t_title']), - 'weather' => $weather, - 'weathertitle' => implode(' ', $weathertitle), - 'applications' => $applications, - 'rate' => $session['p_rate'], - 'hat' => $session['p_hat'] - ]; - } - } - - //Sort sessions - ksort($calendar[$Ymd]['sessions']); - } + ->setParameter('end', $period->getEndDate()); - //Send result - return $calendar; + //Return result + return $res->getResult(); } /** -- 2.41.1