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
  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         GREATEST(u.created, u.updated, COALESCE(c.created, '1970-01-01'), COALESCE(c.updated, '1970-01-01'), COALESCE(o.created, '1970-01-01'), COALESCE(o.updated, '1970-01-01'), COALESCE(g.created, '1970-01-01'), COALESCE(g.updated, '1970-01-01')) AS modified,
 222         GROUP_CONCAT(g.id ORDER BY g.id SEPARATOR "\\n") AS ids,
 223         GROUP_CONCAT(g.title ORDER BY g.id SEPARATOR "\\n") AS titles
 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                         ->addScalarResult('modified', 'modified', 'datetime')
 256                         //XXX: is a string because of \n separator
 257                         ->addScalarResult('ids', 'ids', 'string')
 258                         //XXX: is a string because of \n separator
 259                         ->addScalarResult('titles', 'titles', 'string')
 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 }