X-Git-Url: https://git.rapsys.eu/airbundle/blobdiff_plain/ce807dcc44fa487f5b4adcc0840a1a5b58debede..672a568871fb5f23f6653d94226b98efbbe93ef5:/Repository/SessionRepository.php diff --git a/Repository/SessionRepository.php b/Repository/SessionRepository.php index 5d3522d..cf79a18 100644 --- a/Repository/SessionRepository.php +++ b/Repository/SessionRepository.php @@ -190,10 +190,13 @@ SELECT s.application_id AS a_id, a.canceled AS a_canceled, a.user_id AS au_id, + au.forename AS au_forename, au.pseudonym AS au_pseudonym, p.id AS p_id, p.description AS p_description, p.class AS p_class, + p.short AS p_short, + p.rate AS p_rate, p.contact AS p_contact, p.donate AS p_donate, p.link AS p_link, @@ -236,10 +239,13 @@ SQL; ->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') @@ -318,8 +324,10 @@ 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, @@ -331,6 +339,9 @@ SELECT p.donate AS p_donate, p.link AS p_link, p.profile AS p_profile, + p.rate AS p_rate, + p.hat AS p_hat, + 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, @@ -385,8 +396,10 @@ 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') @@ -398,6 +411,9 @@ SQL; ->addScalarResult('p_donate', 'p_donate', 'text') ->addScalarResult('p_link', 'p_link', 'text') ->addScalarResult('p_profile', 'p_profile', 'text') + ->addScalarResult('p_rate', 'p_rate', 'integer') + ->addScalarResult('p_hat', 'p_hat', 'boolean') + ->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 @@ -431,7 +447,7 @@ SQL; * @param $sessionId The session id * @param $granted The session is granted */ - public function fetchCalendarByDatePeriod(TranslatorInterface $translator, $period, $locationId = null, $sessionId = null, $granted = false) { + public function fetchCalendarByDatePeriod(TranslatorInterface $translator, $period, $locationId = null, $sessionId = null, $granted = false, $locale = null) { //Get entity manager $em = $this->getEntityManager(); @@ -448,6 +464,7 @@ SQL; 'RapsysAirBundle:Group' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Group'), $dp), 'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp), 'RapsysAirBundle:Slot' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Slot'), $dp), + 'RapsysAirBundle:Snippet' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Snippet'), $dp), 'RapsysAirBundle:User' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:User'), $dp), ':afterid' => 4, "\t" => '', @@ -474,6 +491,7 @@ SQL; //Set the request $req = <<addScalarResult('a_canceled', 'a_canceled', 'datetime') ->addScalarResult('au_id', 'au_id', 'integer') ->addScalarResult('au_pseudonym', 'au_pseudonym', 'string') + ->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 @@ -545,7 +568,8 @@ SQL; $res = $em ->createNativeQuery($req, $rsm) ->setParameter('begin', $period->getStartDate()) - ->setParameter('end', $period->getEndDate()); + ->setParameter('end', $period->getEndDate()) + ->setParameter('locale', $locale); //Add optional location id if (!empty($locationId)) { @@ -712,7 +736,9 @@ SQL; 'slottitle' => $translator->trans($session['t_title']), 'weather' => $weather, 'weathertitle' => implode(' ', $weathertitle), - 'applications' => $applications + 'applications' => $applications, + 'rate' => $session['p_rate'], + 'hat' => $session['p_hat'] ]; } } @@ -733,7 +759,7 @@ SQL; * @param $userId The user id * @param $sessionId The session id */ - public function fetchUserCalendarByDatePeriod(TranslatorInterface $translator, $period, $userId = null, $sessionId = null) { + public function fetchUserCalendarByDatePeriod(TranslatorInterface $translator, $period, $userId = null, $sessionId = null, $locale = null) { //Get entity manager $em = $this->getEntityManager(); @@ -750,6 +776,7 @@ SQL; 'RapsysAirBundle:Group' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Group'), $dp), 'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp), 'RapsysAirBundle:Slot' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Slot'), $dp), + 'RapsysAirBundle:Snippet' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Snippet'), $dp), 'RapsysAirBundle:User' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:User'), $dp), ':afterid' => 4, "\t" => '', @@ -788,12 +815,15 @@ SELECT s.application_id AS a_id, a.user_id AS au_id, au.pseudonym AS au_pseudonym, + p.rate AS p_rate, + p.hat AS p_hat, GROUP_CONCAT(sa.user_id ORDER BY sa.user_id SEPARATOR "\\n") AS sau_id, GROUP_CONCAT(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) ${userJoinSql}LEFT JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_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: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) @@ -829,6 +859,8 @@ SQL; ->addScalarResult('a_id', 'a_id', 'integer') ->addScalarResult('au_id', 'au_id', 'integer') ->addScalarResult('au_pseudonym', 'au_pseudonym', 'string') + ->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 @@ -841,6 +873,7 @@ SQL; ->setParameter('begin', $period->getStartDate()) ->setParameter('end', $period->getEndDate()) ->setParameter('uid', $userId) + ->setParameter('locale', $locale) ->getResult(); //Init calendar @@ -1003,7 +1036,9 @@ SQL; 'slottitle' => $translator->trans($session['t_title']), 'weather' => $weather, 'weathertitle' => implode(' ', $weathertitle), - 'applications' => $applications + 'applications' => $applications, + 'rate' => $session['p_rate'], + 'hat' => $session['p_hat'] ]; } } @@ -1434,4 +1469,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; + } }