]> Raphaël G. Git Repositories - airbundle/blob - Repository/UserRepository.php
Fix coalesce warning
[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 Rapsys\AirBundle\Entity\User AS u
45 JOIN Rapsys\AirBundle\Entity\UserGroup AS gu ON (gu.user_id = u.id)
46 JOIN Rapsys\AirBundle\Entity\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 Rapsys\AirBundle\Entity\User AS u
120 LEFT JOIN Rapsys\AirBundle\Entity\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 Rapsys\AirBundle\Entity\Application AS a
160 JOIN Rapsys\AirBundle\Entity\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 Rapsys\AirBundle\Entity\User AS u
225 LEFT JOIN Rapsys\AirBundle\Entity\Civility AS c ON (c.id = u.civility_id)
226 LEFT JOIN Rapsys\AirBundle\Entity\Country AS o ON (o.id = u.country_id)
227 LEFT JOIN Rapsys\AirBundle\Entity\UserGroup AS gu ON (gu.user_id = u.id)
228 LEFT JOIN Rapsys\AirBundle\Entity\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 = 'rapsysair_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 = 'rapsysair_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 //XXX: roles are keyes by id
330 $roles[$id] = 'ROLE_'.strtoupper($titles[$k]);
331
332 //Add group
333 $groups[$id] = $this->translator->trans($titles[$k]);
334 }
335
336 //Return result
337 return [
338 'id' => $result['id'],
339 'mail' => $result['mail'],
340 'pseudonym' => $result['pseudonym'],
341 'forename' => $result['forename'],
342 'surname' => $result['surname'],
343 'phone' => $result['phone'],
344 'zipcode' => $result['zipcode'],
345 'city' => $result['city'],
346 'civility' => [
347 'id' => $result['c_id'],
348 'title' => $this->translator->trans($result['c_title'])
349 ],
350 'country' => [
351 'id' => $result['o_id'],
352 //XXX: without country, o_title is empty
353 'title' => $this->translator->trans($result['o_title'])
354 ],
355 'updated' => $result['updated'],
356 'roles' => $roles,
357 'groups' => $groups,
358 'modified' => $result['modified'],
359 'multimap' => $this->translator->trans('%pseudonym% sector map', ['%pseudonym%' => $result['pseudonym']]),
360 'slug' => $this->slugger->slug($result['pseudonym']),
361 'link' => $this->router->generate($route, ['_locale' => $locale]+$routeParams),
362 'alternates' => $result['alternates']
363 ];
364 }
365
366 /**
367 * Find all users grouped by translated group
368 *
369 * @return array The user mail and pseudonym keyed by group and id
370 */
371 public function findIndexByGroupId(): array {
372 //Set the request
373 $req = <<<SQL
374 SELECT
375 t.id,
376 t.mail,
377 t.forename,
378 t.surname,
379 t.pseudonym,
380 t.g_id,
381 t.g_title,
382 GROUP_CONCAT(t.d_id ORDER BY t.d_id SEPARATOR "\\n") AS d_ids,
383 GROUP_CONCAT(t.d_name ORDER BY t.d_id SEPARATOR "\\n") AS d_names,
384 GROUP_CONCAT(t.d_type ORDER BY t.d_id SEPARATOR "\\n") AS d_types
385 FROM (
386 SELECT
387 c.id,
388 c.mail,
389 c.forename,
390 c.surname,
391 c.pseudonym,
392 c.g_id,
393 c.g_title,
394 d.id AS d_id,
395 d.name AS d_name,
396 d.type AS d_type
397 FROM (
398 SELECT
399 u.id,
400 u.mail,
401 u.forename,
402 u.surname,
403 u.pseudonym,
404 g.id AS g_id,
405 g.title AS g_title
406 FROM Rapsys\AirBundle\Entity\User AS u
407 JOIN Rapsys\AirBundle\Entity\UserGroup AS gu ON (gu.user_id = u.id)
408 JOIN Rapsys\AirBundle\Entity\Group AS g ON (g.id = gu.group_id)
409 ORDER BY NULL
410 LIMIT 0, :limit
411 ) AS c
412 LEFT JOIN Rapsys\AirBundle\Entity\Application AS a ON (a.user_id = c.id)
413 LEFT JOIN Rapsys\AirBundle\Entity\Dance AS d ON (d.id = a.dance_id)
414 GROUP BY d.id
415 ORDER BY NULL
416 LIMIT 0, :limit
417 ) AS t
418 GROUP BY t.g_id, t.id
419 ORDER BY t.g_id DESC, t.id ASC
420 SQL;
421
422 //Replace bundle entity name by table name
423 $req = str_replace($this->tableKeys, $this->tableValues, $req);
424
425 //Get result set mapping instance
426 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
427 $rsm = new ResultSetMapping();
428
429 //Declare all fields
430 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
431 //addScalarResult($sqlColName, $resColName, $type = 'string');
432 $rsm->addScalarResult('id', 'id', 'integer')
433 ->addScalarResult('mail', 'mail', 'string')
434 ->addScalarResult('forename', 'forename', 'string')
435 ->addScalarResult('surname', 'surname', 'string')
436 ->addScalarResult('pseudonym', 'pseudonym', 'string')
437 ->addScalarResult('g_id', 'g_id', 'integer')
438 ->addScalarResult('g_title', 'g_title', 'string')
439 //XXX: is a string because of \n separator
440 ->addScalarResult('d_ids', 'd_ids', 'string')
441 //XXX: is a string because of \n separator
442 ->addScalarResult('d_names', 'd_names', 'string')
443 //XXX: is a string because of \n separator
444 ->addScalarResult('d_types', 'd_types', 'string');
445
446 //Fetch result
447 $res = $this->_em
448 ->createNativeQuery($req, $rsm)
449 ->getResult();
450
451 //Init return
452 $ret = [];
453
454 //Process result
455 foreach($res as $data) {
456 //Get translated group
457 $group = $this->translator->trans($data['g_title']);
458
459 //Init group subarray
460 if (!isset($ret[$group])) {
461 $ret[$group] = [];
462 }
463
464 //Set dances
465 $dances = [];
466
467 //Set data
468 $ret[$group][$data['id']] = [
469 'mail' => $data['mail'],
470 'forename' => $data['forename'],
471 'surname' => $data['surname'],
472 'pseudonym' => $data['pseudonym'],
473 'dances' => [],
474 'slug' => $slug = $this->slugger->slug($data['pseudonym']),
475 //Milonga Raphaël exception
476 'link' => $data['id'] == 1 && $slug == 'milonga-raphael' ? $this->router->generate('rapsysair_user_milongaraphael', []) : $this->router->generate('rapsysair_user_view', ['id' => $data['id'], 'user' => $slug]),
477 'edit' => $this->router->generate('rapsysuser_edit', ['mail' => $short = $this->slugger->short($data['mail']), 'hash' => $this->slugger->hash($short)])
478 ];
479
480 //With dances
481 if (!empty($data['d_ids'])) {
482 //Set names
483 $names = explode("\n", $data['d_names']);
484
485 //Set types
486 $types = explode("\n", $data['d_types']);
487
488 //Iterate on each dance
489 foreach(explode("\n", $data['d_ids']) as $k => $id) {
490 //Init dance when missing
491 if (!isset($ret[$group][$data['id']]['dances'][$name = $this->translator->trans($names[$k])])) {
492 $ret[$group][$data['id']]['dances'][$name] = [
493 'link' => $this->router->generate('rapsysair_dance_name', ['name' => $this->slugger->short($names[$k]), 'dance' => $this->slugger->slug($name)]),
494 'types' => []
495 ];
496 }
497
498 //Set type
499 $ret[$group][$data['id']]['dances'][$name]['types'][$type = $this->translator->trans($types[$k])] = $this->router->generate('rapsysair_dance_view', ['id' => $id, 'name' => $this->slugger->slug($name), 'type' => $this->slugger->slug($type)]);
500 }
501 }
502 }
503
504 //Send result
505 return $ret;
506 }
507 }