]> Raphaël G. Git Repositories - airbundle/blob - Repository/UserRepository.php
Add calendar2 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 use Symfony\Component\Routing\Generator\UrlGeneratorInterface;
17
18 /**
19 * UserRepository
20 */
21 class UserRepository extends Repository {
22 /**
23 * Find users with translated highest group and civility
24 *
25 * @return array The user ids keyed by group and pseudonym
26 */
27 public function findChoicesAsArray(): array {
28 //Set the request
29 $req =<<<SQL
30 SELECT
31 a.id,
32 a.pseudonym,
33 a.g_id,
34 a.g_title
35 FROM (
36 SELECT
37 u.id,
38 u.pseudonym,
39 g.id AS g_id,
40 g.title AS g_title
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
46 LIMIT 0, :limit
47 ) AS a
48 GROUP BY a.id
49 ORDER BY NULL
50 SQL;
51
52 //Replace bundle entity name by table name
53 $req = str_replace($this->tableKeys, $this->tableValues, $req);
54
55 //Get result set mapping instance
56 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
57 $rsm = new ResultSetMapping();
58
59 //Declare all fields
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');
67
68 //Fetch result
69 $res = $this->_em
70 ->createNativeQuery($req, $rsm)
71 ->getResult();
72
73 //Init return
74 $ret = [];
75
76 //Process result
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') {
81 # //Skip it
82 # continue;
83 #}
84
85 //Get translated group
86 $group = $this->translator->trans($data['g_title']);
87
88 //Init group subarray
89 if (!isset($ret[$group])) {
90 $ret[$group] = [];
91 }
92
93 //Set data
94 //XXX: ChoiceType use display string as key
95 $ret[$group][trim($data['pseudonym'].' ('.$data['id'].')')] = intval($data['id']);
96 }
97
98 //Send result
99 return $ret;
100 }
101
102 /**
103 * Find user ids by pseudonym
104 *
105 * @param array $pseudonym The pseudonym filter
106 * @return array The user ids
107 */
108 public function findIdByPseudonymAsArray(array $pseudonym): array {
109 //Set the request
110 $req =<<<SQL
111 SELECT
112 a.id
113 FROM (
114 SELECT
115 u.id
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
120 LIMIT 0, :limit
121 ) AS a
122 GROUP BY a.id
123 ORDER BY NULL
124 SQL;
125
126 //Replace bundle entity name by table name
127 $req = str_replace($this->tableKeys, $this->tableValues, $req);
128
129 //Get result set mapping instance
130 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
131 $rsm = new ResultSetMapping();
132
133 //Declare all fields
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');
137
138 //Return result
139 return $this->_em
140 ->createNativeQuery($req, $rsm)
141 ->setParameter('pseudonym', $pseudonym)
142 //XXX: instead of array_column on the result
143 ->getResult(AbstractQuery::HYDRATE_SCALAR_COLUMN);
144 }
145
146 /**
147 * Find applicant by session id
148 *
149 * @param int $sessionId The Session id
150 * @return array The pseudonym array keyed by id
151 */
152 public function findBySessionId(int $sessionId): array {
153 //Set the request
154 $req =<<<SQL
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
159 SQL;
160
161 //Replace bundle entity name by table name
162 $req = str_replace($this->tableKeys, $this->tableValues, $req);
163
164 //Get result set mapping instance
165 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
166 $rsm = new ResultSetMapping();
167
168 //Declare all fields
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');
173
174 //Get result
175 $result = $this->_em
176 ->createNativeQuery($req, $rsm)
177 ->setParameter('id', $sessionId)
178 ->getArrayResult();
179
180 //Set return
181 $return = [];
182
183 //Iterate on each result
184 foreach($result as $id => $data) {
185 //Add to return
186 $return[$id] = $data['id'];
187 }
188
189 //Return return
190 return $return;
191 }
192
193 /**
194 * Find user as array by id
195 *
196 * @param int $id The location id
197 * @param string $locale The locale
198 * @return array The location data
199 */
200 public function findOneByIdAsArray(int $id, string $locale): ?array {
201 //Set the request
202 //TODO: zipcode/city/country (on pourra matcher les locations avec ça ?)
203 $req =<<<SQL
204 SELECT
205 u.id,
206 u.city,
207 u.forename,
208 u.mail,
209 u.phone,
210 u.pseudonym,
211 u.surname,
212 u.updated,
213 u.zipcode,
214 u.civility_id AS c_id,
215 c.title AS c_title,
216 u.country_id AS o_id,
217 o.title AS o_title,
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)
226 WHERE u.id = :id
227 SQL;
228
229 //Replace bundle entity name by table name
230 $req = str_replace($this->tableKeys, $this->tableValues, $req);
231
232 //Get result set mapping instance
233 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
234 $rsm = new ResultSetMapping();
235
236 //Declare all fields
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');
258
259 //Get result
260 $result = $this->_em
261 ->createNativeQuery($req, $rsm)
262 ->setParameter('id', $id)
263 ->getOneOrNullResult();
264
265 //Without result
266 if ($result === null) {
267 //Return result
268 return $result;
269 }
270
271 //Set alternates
272 $result['alternates'] = [];
273
274 //Set route
275 $route = 'rapsys_air_user_view';
276
277 //Set route params
278 $routeParams = ['id' => $id, 'user' => $this->slugger->slug($result['pseudonym'])];
279
280 //Milonga Raphaël exception
281 if ($routeParams['id'] == 1 && $routeParams['user'] == 'milonga-raphael') {
282 //Set route
283 $route = 'rapsys_air_user_milongaraphael';
284 //Set route params
285 $routeParams = [];
286 }
287
288 //Iterate on each languages
289 foreach($this->languages as $languageId => $language) {
290 //Without current locale
291 if ($languageId !== $locale) {
292 //Set titles
293 $titles = [];
294
295 //Set route params locale
296 $routeParams['_locale'] = $languageId;
297
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);
304 }
305 }
306
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)
313 ];
314 }
315 }
316
317 //Set titles
318 $titles = explode("\n", $result['titles']);
319
320 //Set groups and roles
321 $groups = $roles = [];
322
323 //Iterate on each location
324 foreach(explode("\n", $result['ids']) as $k => $id) {
325 //Add role
326 $roles[$role = 'ROLE_'.strtoupper($titles[$k])] = $role;
327
328 //Add group
329 $groups[$id] = $this->translator->trans($titles[$k]);
330 }
331
332 //Return result
333 return [
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'],
342 'civility' => [
343 'id' => $result['c_id'],
344 'title' => $this->translator->trans($result['c_title'])
345 ],
346 'country' => [
347 'id' => $result['o_id'],
348 //XXX: without country, o_title is empty
349 'title' => $this->translator->trans($result['o_title'])
350 ],
351 'updated' => $result['updated'],
352 'roles' => $roles,
353 'groups' => $groups,
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']
359 ];
360 }
361
362 /**
363 * Find all users grouped by translated group
364 *
365 * @return array The user mail and pseudonym keyed by group and id
366 */
367 public function findIndexByGroupId(): array {
368 //Set the request
369 $req = <<<SQL
370 SELECT
371 t.id,
372 t.mail,
373 t.forename,
374 t.surname,
375 t.pseudonym,
376 t.g_id,
377 t.g_title,
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
381 FROM (
382 SELECT
383 c.id,
384 c.mail,
385 c.forename,
386 c.surname,
387 c.pseudonym,
388 c.g_id,
389 c.g_title,
390 d.id AS d_id,
391 d.name AS d_name,
392 d.type AS d_type
393 FROM (
394 SELECT
395 u.id,
396 u.mail,
397 u.forename,
398 u.surname,
399 u.pseudonym,
400 g.id AS g_id,
401 g.title AS g_title
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)
405 ORDER BY NULL
406 LIMIT 0, :limit
407 ) AS c
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)
410 GROUP BY d.id
411 ORDER BY NULL
412 LIMIT 0, :limit
413 ) AS t
414 GROUP BY t.g_id, t.id
415 ORDER BY t.g_id DESC, t.id ASC
416 SQL;
417
418 //Replace bundle entity name by table name
419 $req = str_replace($this->tableKeys, $this->tableValues, $req);
420
421 //Get result set mapping instance
422 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
423 $rsm = new ResultSetMapping();
424
425 //Declare all fields
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');
441
442 //Fetch result
443 $res = $this->_em
444 ->createNativeQuery($req, $rsm)
445 ->getResult();
446
447 //Init return
448 $ret = [];
449
450 //Process result
451 foreach($res as $data) {
452 //Get translated group
453 $group = $this->translator->trans($data['g_title']);
454
455 //Init group subarray
456 if (!isset($ret[$group])) {
457 $ret[$group] = [];
458 }
459
460 //Set dances
461 $dances = [];
462
463 //Set data
464 $ret[$group][$data['id']] = [
465 'mail' => $data['mail'],
466 'forename' => $data['forename'],
467 'surname' => $data['surname'],
468 'pseudonym' => $data['pseudonym'],
469 'dances' => [],
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)])
474 ];
475
476 //With dances
477 if (!empty($data['d_ids'])) {
478 //Set names
479 $names = explode("\n", $data['d_names']);
480
481 //Set types
482 $types = explode("\n", $data['d_types']);
483
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)]),
490 'types' => []
491 ];
492 }
493
494 //Set type
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)]);
496 }
497 }
498 }
499
500 //Send result
501 return $ret;
502 }
503 }