+ ->addScalarResult('updated', 'updated', 'datetime')
+ ->addScalarResult('zipcode', 'zipcode', 'string')
+ ->addScalarResult('c_id', 'c_id', 'integer')
+ ->addScalarResult('c_title', 'c_title', 'string')
+ ->addScalarResult('o_id', 'o_id', 'integer')
+ ->addScalarResult('o_title', 'o_title', 'string')
+ //XXX: is a string because of \n separator
+ ->addScalarResult('ids', 'ids', 'string')
+ //XXX: is a string because of \n separator
+ ->addScalarResult('titles', 'titles', 'string')
+ ->addScalarResult('modified', 'modified', 'datetime')
+ ->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_user_view';
+
+ //Set route params
+ $routeParams = ['id' => $id, 'user' => $this->slugger->slug($result['pseudonym'])];
+
+ //Milonga Raphaël exception
+ if ($routeParams['id'] == 1 && $routeParams['user'] == 'milonga-raphael') {
+ //Set route
+ $route = 'rapsys_air_user_milongaraphael';
+ //Set route params
+ $routeParams = [];
+ }
+
+ //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;
+
+ //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)
+ ];
+ }
+ }
+
+ //Set titles
+ $titles = explode("\n", $result['titles']);
+
+ //Set groups and roles
+ $groups = $roles = [];
+
+ //Iterate on each location
+ foreach(explode("\n", $result['ids']) as $k => $id) {
+ //Add role
+ $roles[$role = 'ROLE_'.strtoupper($titles[$k])] = $role;
+
+ //Add group
+ $groups[$id] = $this->translator->trans($titles[$k]);
+ }
+
+ //Return result
+ return [
+ 'id' => $result['id'],
+ 'mail' => $result['mail'],
+ 'pseudonym' => $result['pseudonym'],
+ 'forename' => $result['forename'],
+ 'surname' => $result['surname'],
+ 'phone' => $result['phone'],
+ 'zipcode' => $result['zipcode'],
+ 'city' => $result['city'],
+ 'civility' => [
+ 'id' => $result['c_id'],
+ 'title' => $this->translator->trans($result['c_title'])
+ ],
+ 'country' => [
+ 'id' => $result['o_id'],
+ //XXX: without country, o_title is empty
+ 'title' => $this->translator->trans($result['o_title'])
+ ],
+ 'updated' => $result['updated'],
+ 'roles' => $roles,
+ 'groups' => $groups,
+ 'modified' => $result['modified'],
+ 'multimap' => $this->translator->trans('%pseudonym% sector map', ['%pseudonym%' => $result['pseudonym']]),
+ 'slug' => $this->slugger->slug($result['pseudonym']),
+ 'link' => $this->router->generate($route, ['_locale' => $locale]+$routeParams),
+ 'alternates' => $result['alternates']
+ ];
+ }
+
+ /**
+ * Find all users grouped by translated group
+ *
+ * @return array The user mail and pseudonym keyed by group and id
+ */
+ public function findIndexByGroupId(): array {
+ //Set the request
+ $req = <<<SQL
+SELECT
+ t.id,
+ t.mail,
+ t.forename,
+ t.surname,
+ t.pseudonym,
+ t.g_id,
+ t.g_title,
+ GROUP_CONCAT(t.d_id ORDER BY t.d_id SEPARATOR "\\n") AS d_ids,
+ GROUP_CONCAT(t.d_name ORDER BY t.d_id SEPARATOR "\\n") AS d_names,
+ GROUP_CONCAT(t.d_type ORDER BY t.d_id SEPARATOR "\\n") AS d_types
+FROM (
+ SELECT
+ c.id,
+ c.mail,
+ c.forename,
+ c.surname,
+ c.pseudonym,
+ c.g_id,
+ c.g_title,
+ d.id AS d_id,
+ d.name AS d_name,
+ d.type AS d_type
+ FROM (
+ SELECT
+ u.id,
+ u.mail,
+ u.forename,
+ u.surname,
+ u.pseudonym,
+ g.id AS g_id,
+ g.title AS g_title
+ FROM RapsysAirBundle:User AS u
+ JOIN RapsysAirBundle:UserGroup AS gu ON (gu.user_id = u.id)
+ JOIN RapsysAirBundle:Group AS g ON (g.id = gu.group_id)
+ ORDER BY NULL
+ LIMIT 0, :limit
+ ) AS c
+ LEFT JOIN RapsysAirBundle:Application AS a ON (a.user_id = c.id)
+ LEFT JOIN RapsysAirBundle:Dance AS d ON (d.id = a.dance_id)
+ GROUP BY d.id
+ ORDER BY NULL
+ LIMIT 0, :limit
+) AS t
+GROUP BY t.g_id, t.id
+ORDER BY t.g_id DESC, t.id ASC
+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('mail', 'mail', 'string')
+ ->addScalarResult('forename', 'forename', 'string')
+ ->addScalarResult('surname', 'surname', 'string')
+ ->addScalarResult('pseudonym', 'pseudonym', 'string')
Rapsys Git / airbundle / blobdiff