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