Raphaël G. Git Repositories - airbundle/blob - 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 }