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\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 }