1 <?php
declare(strict_types
=1);
4 * This file is part of the Rapsys AirBundle package.
6 * (c) Raphaël Gertz <symfony@rapsys.eu>
8 * For the full copyright and license information, please view the LICENSE
9 * file that was distributed with this source code.
12 namespace Rapsys\AirBundle\Repository
;
14 use Doctrine\ORM\AbstractQuery
;
15 use Doctrine\ORM\Query\ResultSetMapping
;
16 use Symfony\Component\Routing\Generator\UrlGeneratorInterface
;
21 class UserRepository
extends Repository
{
23 * Find users with translated highest group and civility
25 * @return array The user ids keyed by group and pseudonym
27 public function findChoicesAsArray(): array {
41 FROM RapsysAirBundle:User AS u
42 JOIN RapsysAirBundle:UserGroup AS gu ON (gu.user_id = u.id)
43 JOIN RapsysAirBundle:Group AS g ON (g.id = gu.group_id)
44 WHERE g.title <> 'User'
45 ORDER BY g.id DESC, u.pseudonym ASC
52 //Replace bundle entity name by table name
53 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
55 //Get result set mapping instance
56 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
57 $rsm = new ResultSetMapping();
60 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
61 //XXX: we don't use a result set as we want to translate group and civility
62 $rsm->addScalarResult('id', 'id', 'integer')
63 ->addScalarResult('pseudonym', 'pseudonym', 'string')
64 ->addScalarResult('g_id', 'g_id', 'integer')
65 ->addScalarResult('g_title', 'g_title', 'string')
66 ->addIndexByScalar('id');
70 ->createNativeQuery($req, $rsm)
77 foreach($res as $data) {
78 //Without group or simple user
79 #XXX: moved in sql by removing LEFT JOIN and excluding user group
80 #if (empty($data['g_title']) || $data['g_title'] == 'User') {
85 //Get translated group
86 $group = $this->translator
->trans($data['g_title']);
89 if (!isset($ret[$group])) {
94 //XXX: ChoiceType use display string as key
95 $ret[$group][trim($data['pseudonym'].' ('.$data['id'].')')] = intval($data['id']);
103 * Find user ids by pseudonym
105 * @param array $pseudonym The pseudonym filter
106 * @return array The user ids
108 public function findIdByPseudonymAsArray(array $pseudonym): array {
116 FROM RapsysAirBundle:User AS u
117 LEFT JOIN RapsysAirBundle:UserGroup AS gu ON (gu.user_id = u.id)
118 WHERE u.pseudonym IN (:pseudonym)
119 ORDER BY gu.group_id DESC, u.pseudonym ASC
126 //Replace bundle entity name by table name
127 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
129 //Get result set mapping instance
130 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
131 $rsm = new ResultSetMapping();
134 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
135 //XXX: we don't use a result set as we want to translate group and civility
136 $rsm->addScalarResult('id', 'id', 'integer');
140 ->createNativeQuery($req, $rsm)
141 ->setParameter('pseudonym', $pseudonym)
142 //XXX: instead of array_column on the result
143 ->getResult(AbstractQuery
::HYDRATE_SCALAR_COLUMN
);
147 * Find applicant by session id
149 * @param int $sessionId The Session id
150 * @return array The pseudonym array keyed by id
152 public function findBySessionId(int $sessionId): array {
155 SELECT u.id, u.pseudonym
156 FROM RapsysAirBundle:Application AS a
157 JOIN RapsysAirBundle:User AS u ON (u.id = a.user_id)
158 WHERE a.session_id = :id
161 //Replace bundle entity name by table name
162 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
164 //Get result set mapping instance
165 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
166 $rsm = new ResultSetMapping();
169 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
170 //XXX: we don't use a result set as we want to translate group and civility
171 $rsm->addScalarResult('id', 'id', 'integer')
172 ->addIndexByScalar('pseudonym');
176 ->createNativeQuery($req, $rsm)
177 ->setParameter('id', $sessionId)
183 //Iterate on each result
184 foreach($result as $id => $data) {
186 $return[$id] = $data['id'];
194 * Find user as array by id
196 * @param int $id The location id
197 * @param string $locale The locale
198 * @return array The location data
200 public function findOneByIdAsArray(int $id, string $locale): ?array {
202 //TODO: zipcode/city/country (on pourra matcher les locations avec ça ?)
214 u.civility_id AS c_id,
216 u.country_id AS o_id,
218 GROUP_CONCAT(g.id ORDER BY g.id SEPARATOR "\\n") AS ids,
219 GROUP_CONCAT(g.title ORDER BY g.id SEPARATOR "\\n") AS titles,
220 GREATEST(COALESCE(u.updated, 0), COALESCE(c.updated, 0), COALESCE(o.updated, 0)) AS modified
221 FROM RapsysAirBundle:User AS u
222 LEFT JOIN RapsysAirBundle:Civility AS c ON (c.id = u.civility_id)
223 LEFT JOIN RapsysAirBundle:Country AS o ON (o.id = u.country_id)
224 LEFT JOIN RapsysAirBundle:UserGroup AS gu ON (gu.user_id = u.id)
225 LEFT JOIN RapsysAirBundle:Group AS g ON (g.id = gu.group_id)
229 //Replace bundle entity name by table name
230 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
232 //Get result set mapping instance
233 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
234 $rsm = new ResultSetMapping();
237 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
238 //addScalarResult($sqlColName, $resColName, $type = 'string');
239 $rsm->addScalarResult('id', 'id', 'integer')
240 ->addScalarResult('city', 'city', 'string')
241 ->addScalarResult('forename', 'forename', 'string')
242 ->addScalarResult('mail', 'mail', 'string')
243 ->addScalarResult('phone', 'phone', 'string')
244 ->addScalarResult('pseudonym', 'pseudonym', 'string')
245 ->addScalarResult('surname', 'surname', 'string')
246 ->addScalarResult('updated', 'updated', 'datetime')
247 ->addScalarResult('zipcode', 'zipcode', 'string')
248 ->addScalarResult('c_id', 'c_id', 'integer')
249 ->addScalarResult('c_title', 'c_title', 'string')
250 ->addScalarResult('o_id', 'o_id', 'integer')
251 ->addScalarResult('o_title', 'o_title', 'string')
252 //XXX: is a string because of \n separator
253 ->addScalarResult('ids', 'ids', 'string')
254 //XXX: is a string because of \n separator
255 ->addScalarResult('titles', 'titles', 'string')
256 ->addScalarResult('modified', 'modified', 'datetime')
257 ->addIndexByScalar('id');
261 ->createNativeQuery($req, $rsm)
262 ->setParameter('id', $id)
263 ->getOneOrNullResult();
266 if ($result === null) {
272 $result['alternates'] = [];
275 $route = 'rapsys_air_user_view';
278 $routeParams = ['id' => $id, 'user' => $this->slugger
->slug($result['pseudonym'])];
280 //Milonga Raphaël exception
281 if ($routeParams['id'] == 1 && $routeParams['user'] == 'milonga-raphael') {
283 $route = 'rapsys_air_user_milongaraphael';
288 //Iterate on each languages
289 foreach($this->languages
as $languageId => $language) {
290 //Without current locale
291 if ($languageId !== $locale) {
295 //Set route params locale
296 $routeParams['_locale'] = $languageId;
298 //Iterate on each locales
299 foreach(array_keys($this->languages
) as $other) {
300 //Without other locale
301 if ($other !== $languageId) {
302 //Set other locale title
303 $titles[$other] = $this->translator
->trans($language, [], null, $other);
307 //Add alternates locale
308 $result['alternates'][substr($languageId, 0, 2)] = $result['alternates'][str_replace('_', '-', $languageId)] = [
309 'absolute' => $this->router
->generate($route, $routeParams, UrlGeneratorInterface
::ABSOLUTE_URL
),
310 'relative' => $this->router
->generate($route, $routeParams),
311 'title' => implode('/', $titles),
312 'translated' => $this->translator
->trans($language, [], null, $languageId)
318 $titles = explode("\n", $result['titles']);
320 //Set groups and roles
321 $groups = $roles = [];
323 //Iterate on each location
324 foreach(explode("\n", $result['ids']) as $k => $id) {
326 $roles[$role = 'ROLE_'.strtoupper($titles[$k])] = $role;
329 $groups[$id] = $this->translator
->trans($titles[$k]);
334 'id' => $result['id'],
335 'mail' => $result['mail'],
336 'pseudonym' => $result['pseudonym'],
337 'forename' => $result['forename'],
338 'surname' => $result['surname'],
339 'phone' => $result['phone'],
340 'zipcode' => $result['zipcode'],
341 'city' => $result['city'],
343 'id' => $result['c_id'],
344 'title' => $this->translator
->trans($result['c_title'])
347 'id' => $result['o_id'],
348 //XXX: without country, o_title is empty
349 'title' => $this->translator
->trans($result['o_title'])
351 'updated' => $result['updated'],
354 'modified' => $result['modified'],
355 'multimap' => $this->translator
->trans('%pseudonym% sector map', ['%pseudonym%' => $result['pseudonym']]),
356 'slug' => $this->slugger
->slug($result['pseudonym']),
357 'link' => $this->router
->generate($route, ['_locale' => $locale]+
$routeParams),
358 'alternates' => $result['alternates']
363 * Find all users grouped by translated group
365 * @return array The user mail and pseudonym keyed by group and id
367 public function findIndexByGroupId(): array {
378 GROUP_CONCAT(t.d_id ORDER BY t.d_id SEPARATOR "\\n") AS d_ids,
379 GROUP_CONCAT(t.d_name ORDER BY t.d_id SEPARATOR "\\n") AS d_names,
380 GROUP_CONCAT(t.d_type ORDER BY t.d_id SEPARATOR "\\n") AS d_types
402 FROM RapsysAirBundle:User AS u
403 JOIN RapsysAirBundle:UserGroup AS gu ON (gu.user_id = u.id)
404 JOIN RapsysAirBundle:Group AS g ON (g.id = gu.group_id)
408 LEFT JOIN RapsysAirBundle:Application AS a ON (a.user_id = c.id)
409 LEFT JOIN RapsysAirBundle:Dance AS d ON (d.id = a.dance_id)
414 GROUP BY t.g_id, t.id
415 ORDER BY t.g_id DESC, t.id ASC
418 //Replace bundle entity name by table name
419 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
421 //Get result set mapping instance
422 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
423 $rsm = new ResultSetMapping();
426 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
427 //addScalarResult($sqlColName, $resColName, $type = 'string');
428 $rsm->addScalarResult('id', 'id', 'integer')
429 ->addScalarResult('mail', 'mail', 'string')
430 ->addScalarResult('forename', 'forename', 'string')
431 ->addScalarResult('surname', 'surname', 'string')
432 ->addScalarResult('pseudonym', 'pseudonym', 'string')
433 ->addScalarResult('g_id', 'g_id', 'integer')
434 ->addScalarResult('g_title', 'g_title', 'string')
435 //XXX: is a string because of \n separator
436 ->addScalarResult('d_ids', 'd_ids', 'string')
437 //XXX: is a string because of \n separator
438 ->addScalarResult('d_names', 'd_names', 'string')
439 //XXX: is a string because of \n separator
440 ->addScalarResult('d_types', 'd_types', 'string');
444 ->createNativeQuery($req, $rsm)
451 foreach($res as $data) {
452 //Get translated group
453 $group = $this->translator
->trans($data['g_title']);
455 //Init group subarray
456 if (!isset($ret[$group])) {
464 $ret[$group][$data['id']] = [
465 'mail' => $data['mail'],
466 'forename' => $data['forename'],
467 'surname' => $data['surname'],
468 'pseudonym' => $data['pseudonym'],
470 'slug' => $slug = $this->slugger
->slug($data['pseudonym']),
471 //Milonga Raphaël exception
472 'link' => $data['id'] == 1 && $slug == 'milonga-raphael' ? $this->router
->generate('rapsys_air_user_milongaraphael', []) : $this->router
->generate('rapsys_air_user_view', ['id' => $data['id'], 'user' => $slug]),
473 'edit' => $this->router
->generate('rapsys_user_edit', ['mail' => $short = $this->slugger
->short($data['mail']), 'hash' => $this->slugger
->hash($short)])
477 if (!empty($data['d_ids'])) {
479 $names = explode("\n", $data['d_names']);
482 $types = explode("\n", $data['d_types']);
484 //Iterate on each dance
485 foreach(explode("\n", $data['d_ids']) as $k => $id) {
486 //Init dance when missing
487 if (!isset($ret[$group][$data['id']]['dances'][$name = $this->translator
->trans($names[$k])])) {
488 $ret[$group][$data['id']]['dances'][$name] = [
489 'link' => $this->router
->generate('rapsys_air_dance_name', ['name' => $this->slugger
->short($names[$k]), 'dance' => $this->slugger
->slug($name)]),
495 $ret[$group][$data['id']]['dances'][$name]['types'][$type = $this->translator
->trans($types[$k])] = $this->router
->generate('rapsys_air_dance_view', ['id' => $id, 'name' => $this->slugger
->slug($name), 'type' => $this->slugger
->slug($type)]);