]> Raphaël G. Git Repositories - airbundle/blob - Repository/UserRepository.php
Rename Command/Command in Command
[airbundle] / Repository / UserRepository.php
1 <?php declare(strict_types=1);
2
3 /*
4 * This file is part of the Rapsys AirBundle package.
5 *
6 * (c) Raphaël Gertz <symfony@rapsys.eu>
7 *
8 * For the full copyright and license information, please view the LICENSE
9 * file that was distributed with this source code.
10 */
11
12 namespace Rapsys\AirBundle\Repository;
13
14 use Doctrine\ORM\AbstractQuery;
15 use Doctrine\ORM\Query\ResultSetMapping;
16
17 use Symfony\Component\Routing\Generator\UrlGeneratorInterface;
18
19 use Rapsys\AirBundle\Repository;
20
21 /**
22 * UserRepository
23 */
24 class UserRepository extends Repository {
25 /**
26 * Find users with translated highest group and civility
27 *
28 * @return array The user ids keyed by group and pseudonym
29 */
30 public function findChoicesAsArray(): array {
31 //Set the request
32 $req =<<<SQL
33 SELECT
34 a.id,
35 a.pseudonym,
36 a.g_id,
37 a.g_title
38 FROM (
39 SELECT
40 u.id,
41 u.pseudonym,
42 g.id AS g_id,
43 g.title AS g_title
44 FROM RapsysAirBundle:User AS u
45 JOIN RapsysAirBundle:UserGroup AS gu ON (gu.user_id = u.id)
46 JOIN RapsysAirBundle:Group AS g ON (g.id = gu.group_id)
47 WHERE g.title <> 'User'
48 ORDER BY g.id DESC, u.pseudonym ASC
49 LIMIT 0, :limit
50 ) AS a
51 GROUP BY a.id
52 ORDER BY NULL
53 SQL;
54
55 //Replace bundle entity name by table name
56 $req = str_replace($this->tableKeys, $this->tableValues, $req);
57
58 //Get result set mapping instance
59 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
60 $rsm = new ResultSetMapping();
61
62 //Declare all fields
63 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
64 //XXX: we don't use a result set as we want to translate group and civility
65 $rsm->addScalarResult('id', 'id', 'integer')
66 ->addScalarResult('pseudonym', 'pseudonym', 'string')
67 ->addScalarResult('g_id', 'g_id', 'integer')
68 ->addScalarResult('g_title', 'g_title', 'string')
69 ->addIndexByScalar('id');
70
71 //Fetch result
72 $res = $this->_em
73 ->createNativeQuery($req, $rsm)
74 ->getResult();
75
76 //Init return
77 $ret = [];
78
79 //Process result
80 foreach($res as $data) {
81 //Without group or simple user
82 #XXX: moved in sql by removing LEFT JOIN and excluding user group
83 #if (empty($data['g_title']) || $data['g_title'] == 'User') {
84 # //Skip it
85 # continue;
86 #}
87
88 //Get translated group
89 $group = $this->translator->trans($data['g_title']);
90
91 //Init group subarray
92 if (!isset($ret[$group])) {
93 $ret[$group] = [];
94 }
95
96 //Set data
97 //XXX: ChoiceType use display string as key
98 $ret[$group][trim($data['pseudonym'].' ('.$data['id'].')')] = intval($data['id']);
99 }
100
101 //Send result
102 return $ret;
103 }
104
105 /**
106 * Find user ids by pseudonym
107 *
108 * @param array $pseudonym The pseudonym filter
109 * @return array The user ids
110 */
111 public function findIdByPseudonymAsArray(array $pseudonym): array {
112 //Set the request
113 $req =<<<SQL
114 SELECT
115 a.id
116 FROM (
117 SELECT
118 u.id
119 FROM RapsysAirBundle:User AS u
120 LEFT JOIN RapsysAirBundle:UserGroup AS gu ON (gu.user_id = u.id)
121 WHERE u.pseudonym IN (:pseudonym)
122 ORDER BY gu.group_id DESC, u.pseudonym ASC
123 LIMIT 0, :limit
124 ) AS a
125 GROUP BY a.id
126 ORDER BY NULL
127 SQL;
128
129 //Replace bundle entity name by table name
130 $req = str_replace($this->tableKeys, $this->tableValues, $req);
131
132 //Get result set mapping instance
133 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
134 $rsm = new ResultSetMapping();
135
136 //Declare all fields
137 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
138 //XXX: we don't use a result set as we want to translate group and civility
139 $rsm->addScalarResult('id', 'id', 'integer');
140
141 //Return result
142 return $this->_em
143 ->createNativeQuery($req, $rsm)
144 ->setParameter('pseudonym', $pseudonym)
145 //XXX: instead of array_column on the result
146 ->getResult(AbstractQuery::HYDRATE_SCALAR_COLUMN);
147 }
148
149 /**
150 * Find applicant by session id
151 *
152 * @param int $sessionId The Session id
153 * @return array The pseudonym array keyed by id
154 */
155 public function findBySessionId(int $sessionId): array {
156 //Set the request
157 $req =<<<SQL
158 SELECT u.id, u.pseudonym
159 FROM RapsysAirBundle:Application AS a
160 JOIN RapsysAirBundle:User AS u ON (u.id = a.user_id)
161 WHERE a.session_id = :id
162 SQL;
163
164 //Replace bundle entity name by table name
165 $req = str_replace($this->tableKeys, $this->tableValues, $req);
166
167 //Get result set mapping instance
168 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
169 $rsm = new ResultSetMapping();
170
171 //Declare all fields
172 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
173 //XXX: we don't use a result set as we want to translate group and civility
174 $rsm->addScalarResult('id', 'id', 'integer')
175 ->addIndexByScalar('pseudonym');
176
177 //Get result
178 $result = $this->_em
179 ->createNativeQuery($req, $rsm)
180 ->setParameter('id', $sessionId)
181 ->getArrayResult();
182
183 //Set return
184 $return = [];
185
186 //Iterate on each result
187 foreach($result as $id => $data) {
188 //Add to return
189 $return[$id] = $data['id'];
190 }
191
192 //Return return
193 return $return;
194 }
195
196 /**
197 * Find user as array by id
198 *
199 * @param int $id The location id
200 * @param string $locale The locale
201 * @return array The location data
202 */
203 public function findOneByIdAsArray(int $id, string $locale): ?array {
204 //Set the request
205 //TODO: zipcode/city/country (on pourra matcher les locations avec ça ?)
206 $req =<<<SQL
207 SELECT
208 u.id,
209 u.city,
210 u.forename,
211 u.mail,
212 u.phone,
213 u.pseudonym,
214 u.surname,
215 u.updated,
216 u.zipcode,
217 u.civility_id AS c_id,
218 c.title AS c_title,
219 u.country_id AS o_id,
220 o.title AS o_title,
221 GROUP_CONCAT(g.id ORDER BY g.id SEPARATOR "\\n") AS ids,
222 GROUP_CONCAT(g.title ORDER BY g.id SEPARATOR "\\n") AS titles,
223 GREATEST(COALESCE(u.updated, 0), COALESCE(c.updated, 0), COALESCE(o.updated, 0)) AS modified
224 FROM RapsysAirBundle:User AS u
225 LEFT JOIN RapsysAirBundle:Civility AS c ON (c.id = u.civility_id)
226 LEFT JOIN RapsysAirBundle:Country AS o ON (o.id = u.country_id)
227 LEFT JOIN RapsysAirBundle:UserGroup AS gu ON (gu.user_id = u.id)
228 LEFT JOIN RapsysAirBundle:Group AS g ON (g.id = gu.group_id)
229 WHERE u.id = :id
230 SQL;
231
232 //Replace bundle entity name by table name
233 $req = str_replace($this->tableKeys, $this->tableValues, $req);
234
235 //Get result set mapping instance
236 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
237 $rsm = new ResultSetMapping();
238
239 //Declare all fields
240 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
241 //addScalarResult($sqlColName, $resColName, $type = 'string');
242 $rsm->addScalarResult('id', 'id', 'integer')
243 ->addScalarResult('city', 'city', 'string')
244 ->addScalarResult('forename', 'forename', 'string')
245 ->addScalarResult('mail', 'mail', 'string')
246 ->addScalarResult('phone', 'phone', 'string')
247 ->addScalarResult('pseudonym', 'pseudonym', 'string')
248 ->addScalarResult('surname', 'surname', 'string')
249 ->addScalarResult('updated', 'updated', 'datetime')
250 ->addScalarResult('zipcode', 'zipcode', 'string')
251 ->addScalarResult('c_id', 'c_id', 'integer')
252 ->addScalarResult('c_title', 'c_title', 'string')
253 ->addScalarResult('o_id', 'o_id', 'integer')
254 ->addScalarResult('o_title', 'o_title', 'string')
255 //XXX: is a string because of \n separator
256 ->addScalarResult('ids', 'ids', 'string')
257 //XXX: is a string because of \n separator
258 ->addScalarResult('titles', 'titles', 'string')
259 ->addScalarResult('modified', 'modified', 'datetime')
260 ->addIndexByScalar('id');
261
262 //Get result
263 $result = $this->_em
264 ->createNativeQuery($req, $rsm)
265 ->setParameter('id', $id)
266 ->getOneOrNullResult();
267
268 //Without result
269 if ($result === null) {
270 //Return result
271 return $result;
272 }
273
274 //Set alternates
275 $result['alternates'] = [];
276
277 //Set route
278 $route = 'rapsys_air_user_view';
279
280 //Set route params
281 $routeParams = ['id' => $id, 'user' => $this->slugger->slug($result['pseudonym'])];
282
283 //Milonga Raphaël exception
284 if ($routeParams['id'] == 1 && $routeParams['user'] == 'milonga-raphael') {
285 //Set route
286 $route = 'rapsys_air_user_milongaraphael';
287 //Set route params
288 $routeParams = [];
289 }
290
291 //Iterate on each languages
292 foreach($this->languages as $languageId => $language) {
293 //Without current locale
294 if ($languageId !== $locale) {
295 //Set titles
296 $titles = [];
297
298 //Set route params locale
299 $routeParams['_locale'] = $languageId;
300
301 //Iterate on each locales
302 foreach(array_keys($this->languages) as $other) {
303 //Without other locale
304 if ($other !== $languageId) {
305 //Set other locale title
306 $titles[$other] = $this->translator->trans($language, [], null, $other);
307 }
308 }
309
310 //Add alternates locale
311 $result['alternates'][substr($languageId, 0, 2)] = $result['alternates'][str_replace('_', '-', $languageId)] = [
312 'absolute' => $this->router->generate($route, $routeParams, UrlGeneratorInterface::ABSOLUTE_URL),
313 'relative' => $this->router->generate($route, $routeParams),
314 'title' => implode('/', $titles),
315 'translated' => $this->translator->trans($language, [], null, $languageId)
316 ];
317 }
318 }
319
320 //Set titles
321 $titles = explode("\n", $result['titles']);
322
323 //Set groups and roles
324 $groups = $roles = [];
325
326 //Iterate on each location
327 foreach(explode("\n", $result['ids']) as $k => $id) {
328 //Add role
329 $roles[$role = 'ROLE_'.strtoupper($titles[$k])] = $role;
330
331 //Add group
332 $groups[$id] = $this->translator->trans($titles[$k]);
333 }
334
335 //Return result
336 return [
337 'id' => $result['id'],
338 'mail' => $result['mail'],
339 'pseudonym' => $result['pseudonym'],
340 'forename' => $result['forename'],
341 'surname' => $result['surname'],
342 'phone' => $result['phone'],
343 'zipcode' => $result['zipcode'],
344 'city' => $result['city'],
345 'civility' => [
346 'id' => $result['c_id'],
347 'title' => $this->translator->trans($result['c_title'])
348 ],
349 'country' => [
350 'id' => $result['o_id'],
351 //XXX: without country, o_title is empty
352 'title' => $this->translator->trans($result['o_title'])
353 ],
354 'updated' => $result['updated'],
355 'roles' => $roles,
356 'groups' => $groups,
357 'modified' => $result['modified'],
358 'multimap' => $this->translator->trans('%pseudonym% sector map', ['%pseudonym%' => $result['pseudonym']]),
359 'slug' => $this->slugger->slug($result['pseudonym']),
360 'link' => $this->router->generate($route, ['_locale' => $locale]+$routeParams),
361 'alternates' => $result['alternates']
362 ];
363 }
364
365 /**
366 * Find all users grouped by translated group
367 *
368 * @return array The user mail and pseudonym keyed by group and id
369 */
370 public function findIndexByGroupId(): array {
371 //Set the request
372 $req = <<<SQL
373 SELECT
374 t.id,
375 t.mail,
376 t.forename,
377 t.surname,
378 t.pseudonym,
379 t.g_id,
380 t.g_title,
381 GROUP_CONCAT(t.d_id ORDER BY t.d_id SEPARATOR "\\n") AS d_ids,
382 GROUP_CONCAT(t.d_name ORDER BY t.d_id SEPARATOR "\\n") AS d_names,
383 GROUP_CONCAT(t.d_type ORDER BY t.d_id SEPARATOR "\\n") AS d_types
384 FROM (
385 SELECT
386 c.id,
387 c.mail,
388 c.forename,
389 c.surname,
390 c.pseudonym,
391 c.g_id,
392 c.g_title,
393 d.id AS d_id,
394 d.name AS d_name,
395 d.type AS d_type
396 FROM (
397 SELECT
398 u.id,
399 u.mail,
400 u.forename,
401 u.surname,
402 u.pseudonym,
403 g.id AS g_id,
404 g.title AS g_title
405 FROM RapsysAirBundle:User AS u
406 JOIN RapsysAirBundle:UserGroup AS gu ON (gu.user_id = u.id)
407 JOIN RapsysAirBundle:Group AS g ON (g.id = gu.group_id)
408 ORDER BY NULL
409 LIMIT 0, :limit
410 ) AS c
411 LEFT JOIN RapsysAirBundle:Application AS a ON (a.user_id = c.id)
412 LEFT JOIN RapsysAirBundle:Dance AS d ON (d.id = a.dance_id)
413 GROUP BY d.id
414 ORDER BY NULL
415 LIMIT 0, :limit
416 ) AS t
417 GROUP BY t.g_id, t.id
418 ORDER BY t.g_id DESC, t.id ASC
419 SQL;
420
421 //Replace bundle entity name by table name
422 $req = str_replace($this->tableKeys, $this->tableValues, $req);
423
424 //Get result set mapping instance
425 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
426 $rsm = new ResultSetMapping();
427
428 //Declare all fields
429 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
430 //addScalarResult($sqlColName, $resColName, $type = 'string');
431 $rsm->addScalarResult('id', 'id', 'integer')
432 ->addScalarResult('mail', 'mail', 'string')
433 ->addScalarResult('forename', 'forename', 'string')
434 ->addScalarResult('surname', 'surname', 'string')
435 ->addScalarResult('pseudonym', 'pseudonym', 'string')
436 ->addScalarResult('g_id', 'g_id', 'integer')
437 ->addScalarResult('g_title', 'g_title', 'string')
438 //XXX: is a string because of \n separator
439 ->addScalarResult('d_ids', 'd_ids', 'string')
440 //XXX: is a string because of \n separator
441 ->addScalarResult('d_names', 'd_names', 'string')
442 //XXX: is a string because of \n separator
443 ->addScalarResult('d_types', 'd_types', 'string');
444
445 //Fetch result
446 $res = $this->_em
447 ->createNativeQuery($req, $rsm)
448 ->getResult();
449
450 //Init return
451 $ret = [];
452
453 //Process result
454 foreach($res as $data) {
455 //Get translated group
456 $group = $this->translator->trans($data['g_title']);
457
458 //Init group subarray
459 if (!isset($ret[$group])) {
460 $ret[$group] = [];
461 }
462
463 //Set dances
464 $dances = [];
465
466 //Set data
467 $ret[$group][$data['id']] = [
468 'mail' => $data['mail'],
469 'forename' => $data['forename'],
470 'surname' => $data['surname'],
471 'pseudonym' => $data['pseudonym'],
472 'dances' => [],
473 'slug' => $slug = $this->slugger->slug($data['pseudonym']),
474 //Milonga Raphaël exception
475 '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]),
476 'edit' => $this->router->generate('rapsys_user_edit', ['mail' => $short = $this->slugger->short($data['mail']), 'hash' => $this->slugger->hash($short)])
477 ];
478
479 //With dances
480 if (!empty($data['d_ids'])) {
481 //Set names
482 $names = explode("\n", $data['d_names']);
483
484 //Set types
485 $types = explode("\n", $data['d_types']);
486
487 //Iterate on each dance
488 foreach(explode("\n", $data['d_ids']) as $k => $id) {
489 //Init dance when missing
490 if (!isset($ret[$group][$data['id']]['dances'][$name = $this->translator->trans($names[$k])])) {
491 $ret[$group][$data['id']]['dances'][$name] = [
492 'link' => $this->router->generate('rapsys_air_dance_name', ['name' => $this->slugger->short($names[$k]), 'dance' => $this->slugger->slug($name)]),
493 'types' => []
494 ];
495 }
496
497 //Set type
498 $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)]);
499 }
500 }
501 }
502
503 //Send result
504 return $ret;
505 }
506 }