X-Git-Url: https://git.rapsys.eu/airbundle/blobdiff_plain/c25bf77052a8e1988af960f961fec2ece8c1c8c3..d2cc96771dfe99250fc381de504c374c0a4e4e49:/Repository/SessionRepository.php?ds=inline diff --git a/Repository/SessionRepository.php b/Repository/SessionRepository.php index 4444b95..f6ecb6d 100644 --- a/Repository/SessionRepository.php +++ b/Repository/SessionRepository.php @@ -142,6 +142,127 @@ SQL; ->getOneOrNullResult(); } + /** + * Fetch sessions by date period + * + * @param $period The date period + * @param $locale The locale + */ + public function fetchAllByDatePeriod($period, $locale = null) { + //Get entity manager + $em = $this->getEntityManager(); + + //Get quote strategy + $qs = $em->getConfiguration()->getQuoteStrategy(); + $dp = $em->getConnection()->getDatabasePlatform(); + + //Get quoted table names + //XXX: this allow to make this code table name independent + $tables = [ + 'RapsysAirBundle:Application' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Application'), $dp), + 'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp), + 'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp), + 'RapsysAirBundle:Snippet' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Snippet'), $dp), + 'RapsysAirBundle:User' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:User'), $dp), + ':afterid' => 4, + "\t" => '', + "\n" => ' ' + ]; + + //Set the request + //TODO: exclude opera and others ? + $req = <<addScalarResult('id', 'id', 'integer') + ->addScalarResult('date', 'date', 'date') + ->addScalarResult('locked', 'locked', 'datetime') + ->addScalarResult('updated', 'updated', 'datetime') + ->addScalarResult('start', 'start', 'datetime') + ->addScalarResult('stop', 'stop', 'datetime') + ->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_short', 'l_short', 'string') + ->addScalarResult('l_title', 'l_title', '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('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_rate', 'p_rate', 'integer') + ->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 = $em + ->createNativeQuery($req, $rsm) + ->setParameter('begin', $period->getStartDate()) + ->setParameter('end', $period->getEndDate()) + ->setParameter('locale', $locale); + + //Return result + return $res->getResult(); + } + /** * Fetch session by id * @@ -168,7 +289,6 @@ SQL; 'RapsysAirBundle:Snippet' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Snippet'), $dp), 'RapsysAirBundle:Slot' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Slot'), $dp), 'RapsysAirBundle:User' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:User'), $dp), - //Delay ':afterid' => 4, "\t" => '', "\n" => ' ' @@ -204,15 +324,22 @@ SELECT l.city AS l_city, l.latitude AS l_latitude, l.longitude AS l_longitude, + l.updated AS l_updated, s.slot_id AS t_id, t.title AS t_title, + t.updated AS t_updated, s.application_id AS a_id, + a.canceled AS a_canceled, a.user_id AS au_id, au.pseudonym AS au_pseudonym, - au.donation AS au_donation, - au.site AS au_site, p.id AS p_id, p.description AS p_description, + p.class AS p_class, + p.contact AS p_contact, + p.donate AS p_donate, + p.link AS p_link, + p.profile AS p_profile, + p.updated AS p_updated, 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, @@ -267,15 +394,22 @@ SQL; ->addScalarResult('l_city', 'l_city', 'string') ->addScalarResult('l_latitude', 'l_latitude', 'float') ->addScalarResult('l_longitude', 'l_longitude', 'float') + ->addScalarResult('l_updated', 'l_updated', 'datetime') ->addScalarResult('t_id', 't_id', 'integer') ->addScalarResult('t_title', 't_title', 'string') + ->addScalarResult('t_updated', 't_updated', 'datetime') ->addScalarResult('a_id', 'a_id', 'integer') + ->addScalarResult('a_canceled', 'a_canceled', 'datetime') ->addScalarResult('au_id', 'au_id', 'integer') ->addScalarResult('au_pseudonym', 'au_pseudonym', 'string') - ->addScalarResult('au_donation', 'au_donation', 'string') - ->addScalarResult('au_site', 'au_site', 'string') ->addScalarResult('p_id', 'p_id', 'integer') ->addScalarResult('p_description', 'p_description', 'text') + ->addScalarResult('p_class', 'p_class', 'text') + ->addScalarResult('p_contact', 'p_contact', 'text') + ->addScalarResult('p_donate', 'p_donate', 'text') + ->addScalarResult('p_link', 'p_link', 'text') + ->addScalarResult('p_profile', 'p_profile', 'text') + ->addScalarResult('p_updated', 'p_updated', 'datetime') //XXX: is a string because of \n separator ->addScalarResult('sa_id', 'sa_id', 'string') //XXX: is a string because of \n separator @@ -327,24 +461,63 @@ SQL; 'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp), 'RapsysAirBundle:Slot' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Slot'), $dp), 'RapsysAirBundle:User' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:User'), $dp), + ':afterid' => 4, "\t" => '', "\n" => ' ' ]; + //Init granted sql + $grantSql = ''; + + //When granted is set + if (empty($granted)) { + //Set application and user as optional + $grantSql = 'LEFT '; + } + + //Init location sql + $locationSql = ''; + + //When location id is set + if (!empty($locationId)) { + //Add location id clause + $locationSql = "\n\t".'AND s.location_id = :lid'; + } + //Set the request - //TODO: change as_u_* in sau_*, a_u_* in au_*, etc, see request up - $req = 'SELECT s.id, s.date, s.rainrisk, s.rainfall, s.realfeel, s.temperature, s.location_id AS l_id, l.short AS l_short, l.title AS l_title, s.slot_id AS t_id, t.title AS t_title, s.application_id AS a_id, a.user_id AS a_u_id, au.pseudonym AS a_u_pseudonym, GROUP_CONCAT(sa.user_id ORDER BY sa.user_id SEPARATOR "\\n") AS as_u_id, GROUP_CONCAT(sau.pseudonym ORDER BY sa.user_id SEPARATOR "\\n") AS as_u_pseudonym - 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) - '.($granted?'':'LEFT ').'JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id) - '.($granted?'':'LEFT ').'JOIN RapsysAirBundle:User AS au ON (au.id = a.user_id) - LEFT JOIN RapsysAirBundle:Application AS sa ON (sa.session_id = s.id) - LEFT JOIN RapsysAirBundle:User AS sau ON (sau.id = sa.user_id) - WHERE s.date BETWEEN :begin AND :end - '.(!empty($locationId)?'AND s.location_id = :lid':'').' - GROUP BY s.id - ORDER BY NULL'; + $req = <<addScalarResult('rainfall', 'rainfall', 'float') ->addScalarResult('realfeel', 'realfeel', 'float') ->addScalarResult('temperature', 'temperature', 'float') + ->addScalarResult('locked', 'locked', '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_short', 'l_short', 'string') ->addScalarResult('l_title', 'l_title', 'string') ->addScalarResult('a_id', 'a_id', 'integer') - ->addScalarResult('a_u_id', 'a_u_id', 'integer') - ->addScalarResult('a_u_pseudonym', 'a_u_pseudonym', 'string') + ->addScalarResult('a_canceled', 'a_canceled', 'datetime') + ->addScalarResult('au_id', 'au_id', 'integer') + ->addScalarResult('au_pseudonym', 'au_pseudonym', 'string') //XXX: is a string because of \n separator - ->addScalarResult('as_u_id', 'as_u_id', 'string') + ->addScalarResult('sau_id', 'sau_id', 'string') //XXX: is a string because of \n separator - ->addScalarResult('as_u_pseudonym', 'as_u_pseudonym', 'string') + ->addScalarResult('sau_pseudonym', 'sau_pseudonym', 'string') ->addIndexByScalar('id'); //Fetch result @@ -400,7 +577,7 @@ SQL; foreach($period as $date) { //Init day in calendar $calendar[$Ymd = $date->format('Ymd')] = [ - 'title' => $date->format('d'), + 'title' => $translator->trans($date->format('l')).' '.$date->format('d'), 'class' => [], 'sessions' => [] ]; @@ -428,24 +605,33 @@ SQL; //Set next month days if ($date->format('m') > date('m')) { $calendar[$Ymd]['next'] = true; - $calendar[$Ymd]['class'][] = 'next'; + #$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['as_u_id'])); + $count = count(explode("\n", $session['sau_id'])); //Compute classes $class = []; if (!empty($session['a_id'])) { - $applications = [ $session['a_u_id'] => $session['a_u_pseudonym'] ]; - $class[] = 'granted'; - } elseif ($count == 0) { - $class[] = 'orphaned'; + $applications = [ $session['au_id'] => $session['au_pseudonym'] ]; + if (!empty($session['a_canceled'])) { + $class[] = 'canceled'; + } else { + $class[] = 'granted'; + } } elseif ($count > 1) { $class[] = 'disputed'; + } elseif (!empty($session['locked'])) { + $class[] = 'locked'; } else { $class[] = 'pending'; } @@ -505,31 +691,34 @@ SQL; ]; //Fetch pseudonyms from session applications - $applications += array_combine(explode("\n", $session['as_u_id']), array_map(function ($v) {return '- '.$v;}, explode("\n", $session['as_u_pseudonym']))); + $applications += array_combine(explode("\n", $session['sau_id']), array_map(function ($v) {return '- '.$v;}, explode("\n", $session['sau_pseudonym']))); + + //Set pseudonym + $pseudonym = null; //Check that session is not granted if (empty($session['a_id'])) { //With location id and unique application - if ($locationId && $count == 1) { - //Set unique application pseudonym as title - $title = $session['as_u_pseudonym']; - //Without location id or multiple application - } else { - //Set location title with optional count - $title = $translator->trans($session['l_title']).($count > 1 ? ' ['.$count.']':''); + if ($count == 1) { + //Set unique application pseudonym + $pseudonym = $session['sau_pseudonym']; } //Session is granted } else { //Replace granted application - $applications[$session['a_u_id']] = '* '.$session['a_u_pseudonym']; - //Set pseudonym with optional location title and count - $title = $session['a_u_pseudonym'].($locationId?'':' '.$translator->trans('at '.$session['l_short'])).($count > 1 ? ' ['.$count.']':''); + $applications[$session['au_id']] = '* '.$session['au_pseudonym']; + + //Set pseudonym + $pseudonym = $session['au_pseudonym'].($count > 1 ? ' ['.$count.']':''); } //Add the session - $calendar[$Ymd]['sessions'][$session['t_id'].sprintf('%02d', $session['l_id'])] = [ + $calendar[$Ymd]['sessions'][$session['t_id'].sprintf('%05d', $session['id'])] = [ 'id' => $session['id'], - 'title' => $title, + 'start' => $session['start'], + 'stop' => $session['stop'], + 'location' => $translator->trans($session['l_short']), + 'pseudonym' => $pseudonym, 'class' => $class, 'slot' => self::GLYPHS[$session['t_title']], 'slottitle' => $translator->trans($session['t_title']), @@ -574,24 +763,56 @@ SQL; 'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp), 'RapsysAirBundle:Slot' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Slot'), $dp), 'RapsysAirBundle:User' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:User'), $dp), + ':afterid' => 4, "\t" => '', "\n" => ' ' ]; + //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'; + } + //Set the request - $req = 'SELECT s.id, s.date, s.rainrisk, s.rainfall, s.realfeel, s.temperature, s.location_id AS l_id, l.short AS l_short, l.title AS l_title, s.slot_id AS t_id, t.title AS t_title, s.application_id AS a_id, a.user_id AS a_u_id, au.pseudonym AS a_u_pseudonym, GROUP_CONCAT(sa.user_id ORDER BY sa.user_id SEPARATOR "\\n") AS as_u_id, GROUP_CONCAT(CONCAT("- ", sau.pseudonym) ORDER BY sa.user_id SEPARATOR "\\n") AS as_u_pseudonym - 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) - '.($userId?'JOIN RapsysAirBundle:Application AS sua ON (sua.session_id = s.id)':'').' - LEFT JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id) - LEFT JOIN RapsysAirBundle:User AS au ON (au.id = a.user_id) - LEFT JOIN RapsysAirBundle:Application AS sa ON (sa.session_id = s.id) - LEFT JOIN RapsysAirBundle:User AS sau ON (sau.id = sa.user_id) - WHERE s.date BETWEEN :begin AND :end - '.($userId?'AND sua.user_id = :uid':'').' - GROUP BY s.id - ORDER BY NULL'; + //TODO: change as_u_* in sau_*, a_u_* in au_*, etc, see request up + $req = <<addScalarResult('rainfall', 'rainfall', 'float') ->addScalarResult('realfeel', 'realfeel', 'float') ->addScalarResult('temperature', 'temperature', 'float') + ->addScalarResult('locked', 'locked', '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_short', 'l_short', 'string') ->addScalarResult('l_title', 'l_title', 'string') ->addScalarResult('a_id', 'a_id', 'integer') - ->addScalarResult('a_u_id', 'a_u_id', 'integer') - ->addScalarResult('a_u_pseudonym', 'a_u_pseudonym', 'string') + ->addScalarResult('au_id', 'au_id', 'integer') + ->addScalarResult('au_pseudonym', 'au_pseudonym', 'string') //XXX: is a string because of \n separator - ->addScalarResult('as_u_id', 'as_u_id', 'string') + ->addScalarResult('sau_id', 'sau_id', 'string') //XXX: is a string because of \n separator - ->addScalarResult('as_u_pseudonym', 'as_u_pseudonym', 'string') + ->addScalarResult('sau_pseudonym', 'sau_pseudonym', 'string') ->addIndexByScalar('id'); //Fetch result @@ -641,7 +865,7 @@ SQL; foreach($period as $date) { //Init day in calendar $calendar[$Ymd = $date->format('Ymd')] = [ - 'title' => $date->format('d'), + 'title' => $translator->trans($date->format('l')).' '.$date->format('d'), 'class' => [], 'sessions' => [] ]; @@ -669,28 +893,33 @@ SQL; //Set next month days if ($date->format('m') > date('m')) { $calendar[$Ymd]['next'] = true; - $calendar[$Ymd]['class'][] = 'next'; + #$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['as_u_id'])); + $count = count(explode("\n", $session['sau_id'])); //Compute classes $class = []; if (!empty($session['a_id'])) { - $applications = [ $session['a_u_id'] => $session['a_u_pseudonym'] ]; - if ($session['a_u_id'] == $userId) { + $applications = [ $session['au_id'] => $session['au_pseudonym'] ]; + if ($session['au_id'] == $userId) { $class[] = 'granted'; } else { $class[] = 'disputed'; } - } elseif ($count == 0) { - $class[] = 'orphaned'; } elseif ($count > 1) { $class[] = 'disputed'; + } elseif (!empty($session['locked'])) { + $class[] = 'locked'; } else { $class[] = 'pending'; } @@ -749,16 +978,26 @@ SQL; 0 => $translator->trans($session['t_title']).' '.$translator->trans('at '.$session['l_title']).$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 pseudonym + $pseudonym = null; + //Check that session is not granted if (empty($session['a_id'])) { - //Fetch pseudonyms from session applications - $applications += array_combine(explode("\n", $session['as_u_id']), explode("\n", $session['as_u_pseudonym'])); + //With location id and unique application + if ($count == 1) { + //Set unique application pseudonym + $pseudonym = $session['sau_pseudonym']; + } //Session is granted } else { - //Fetch pseudonyms from session applications - $applications += array_combine(explode("\n", $session['as_u_id']), explode("\n", $session['as_u_pseudonym'])); //Replace granted application - $applications[$session['a_u_id']] = '* '.$session['a_u_pseudonym']; + $applications[$session['au_id']] = '* '.$session['au_pseudonym']; + + //Set pseudonym + $pseudonym = $session['au_pseudonym'].($count > 1 ? ' ['.$count.']':''); } //Set title @@ -767,7 +1006,10 @@ SQL; //Add the session $calendar[$Ymd]['sessions'][$session['t_id'].sprintf('%02d', $session['l_id'])] = [ 'id' => $session['id'], - 'title' => $title, + 'start' => $session['start'], + 'stop' => $session['stop'], + 'location' => $translator->trans($session['l_short']), + 'pseudonym' => $pseudonym, 'class' => $class, 'slot' => self::GLYPHS[$session['t_title']], 'slottitle' => $translator->trans($session['t_title']), @@ -1204,4 +1446,173 @@ SQL; //Return best ranked application return $ret; } + + + /** + * Rekey sessions and applications by chronological session id + * + * @return bool The rekey success or failure + */ + function rekey(): bool { + //Get entity manager + $em = $this->getEntityManager(); + + //Get connection + $cnx = $em->getConnection(); + + //Get quote strategy + $qs = $em->getConfiguration()->getQuoteStrategy(); + $dp = $em->getConnection()->getDatabasePlatform(); + + //Get quoted table names + //XXX: this allow to make this code table name independent + $tables = [ + 'RapsysAirBundle:Application' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Application'), $dp), + 'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp), + ':afterid' => 4, + "\t" => '', + "\n" => ' ' + ]; + + //Set the request + $req = <<addScalarResult('id', 'id', 'integer') + ->addScalarResult('sa_id', 'sa_id', 'string'); + #->addIndexByScalar('id'); + + //Fetch result + $rnq = $em->createNativeQuery($req, $rsm); + + //Get result set + $res = $rnq->getResult(); + + //Start transaction + $cnx->beginTransaction(); + + //Set update session request + $sreq = << $data) { + //Set temp id + $res[$id]['t_id'] = $max + $id + 1; + + //Set new id + $res[$id]['n_id'] = $id + 1; + + //Explode application ids + $res[$id]['sa_id'] = explode("\n", $data['sa_id']); + + //Without change + if ($res[$id]['n_id'] == $res[$id]['id']) { + //Remove unchanged session + unset($res[$id]); + } + } + + //With changes + if (!empty($res)) { + //Disable foreign key checks + $cnx->prepare('SET foreign_key_checks = 0')->execute(); + + //Update to temp id + foreach($res as $id => $data) { + //Run session update + $cnx->executeUpdate($sreq, ['nid' => $res[$id]['t_id'], 'id' => $res[$id]['id']]); + + //Run applications update + $cnx->executeUpdate($areq, ['nid' => $res[$id]['t_id'], 'id' => $res[$id]['id']]); + } + + //Update to new id + foreach($res as $id => $data) { + //Run session update + $cnx->executeUpdate($sreq, ['nid' => $res[$id]['n_id'], 'id' => $res[$id]['t_id']]); + + //Run applications update + $cnx->executeUpdate($areq, ['nid' => $res[$id]['n_id'], 'id' => $res[$id]['t_id']]); + } + + //Restore foreign key checks + $cnx->prepare('SET foreign_key_checks = 1')->execute(); + + //Commit transaction + $cnx->commit(); + + //Set update auto_increment request + $ireq = <<exec($ireq); + //Without changes + } else { + //Rollback transaction + $cnx->rollback(); + } + } catch(\Exception $e) { + //Rollback transaction + $cnx->rollback(); + + //Throw exception + throw $e; + } + + //Return success + return true; + } }