Raphaël G. Git Repositories - airbundle/blob - Repository/GoogleTokenRepository.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 Rapsys\AirBundle\Repository;
  18
  19 /**
  20  * GoogleTokenRepository
  21  */
  22 class GoogleTokenRepository extends Repository {
  23         /**
  24          * Find google tokens indexed by id
  25          *
  26          * @return array The google tokens array
  27          */
  28         public function findAllIndexed(): array {
  29                 //Set the request
  30                 $req = <<<SQL
  31 SELECT
  32         b.tid,
  33         b.gmail,
  34         b.uid,
  35         b.access,
  36         b.refresh,
  37         b.expired,
  38         b.cids,
  39         b.cmails,
  40         b.csummaries,
  41         b.csynchronizeds,
  42         b.dids,
  43         GROUP_CONCAT(us.subscribed_id ORDER BY us.subscribed_id SEPARATOR "\\n") AS sids
  44 FROM (
  45         SELECT
  46                 a.tid,
  47                 a.gmail,
  48                 a.uid,
  49                 a.access,
  50                 a.refresh,
  51                 a.expired,
  52                 a.cids,
  53                 a.cmails,
  54                 a.csummaries,
  55                 a.csynchronizeds,
  56                 GROUP_CONCAT(ud.dance_id ORDER BY ud.dance_id SEPARATOR "\\n") AS dids
  57         FROM (
  58                 SELECT
  59                         t.id AS tid,
  60                         t.mail AS gmail,
  61                         t.user_id AS uid,
  62                         t.access,
  63                         t.refresh,
  64                         t.expired,
  65                         GROUP_CONCAT(c.id ORDER BY c.id SEPARATOR "\\n") AS cids,
  66                         GROUP_CONCAT(c.mail ORDER BY c.id SEPARATOR "\\n") AS cmails,
  67                         GROUP_CONCAT(c.summary ORDER BY c.id SEPARATOR "\\n") AS csummaries,
  68                         GROUP_CONCAT(IFNULL(c.synchronized, 'NULL') ORDER BY c.id SEPARATOR "\\n") AS csynchronizeds
  69                 FROM Rapsys\AirBundle\Entity\GoogleToken AS t
  70                 JOIN Rapsys\AirBundle\Entity\GoogleCalendar AS c ON (c.google_token_id = t.id)
  71                 GROUP BY t.id
  72                 ORDER BY NULL
  73         ) AS a
  74         LEFT JOIN Rapsys\AirBundle\Entity\UserDance AS ud ON (ud.user_id = a.uid)
  75 ) AS b
  76 LEFT JOIN Rapsys\AirBundle\Entity\UserSubscription AS us ON (us.user_id = b.uid)
  77 SQL;
  78
  79                 //Replace bundle entity name by table name
  80                 $req = str_replace($this->tableKeys, $this->tableValues, $req);
  81
  82                 //Get result set mapping instance
  83                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
  84                 $rsm = new ResultSetMapping();
  85
  86                 //Declare all fields
  87                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
  88                 //addScalarResult($sqlColName, $resColName, $type = 'string');
  89                 $rsm
  90                         ->addScalarResult('tid', 'tid', 'integer')
  91                         ->addScalarResult('gmail', 'gmail', 'string')
  92                         ->addScalarResult('uid', 'uid', 'integer')
  93                         ->addScalarResult('access', 'access', 'string')
  94                         ->addScalarResult('refresh', 'refresh', 'string')
  95                         ->addScalarResult('expired', 'expired', 'datetime')
  96                         ->addScalarResult('cids', 'cids', 'string')
  97                         ->addScalarResult('cmails', 'cmails', 'string')
  98                         ->addScalarResult('csummaries', 'csummaries', 'string')
  99                         ->addScalarResult('csynchronizeds', 'csynchronizeds', 'string')
 100                         ->addScalarResult('dids', 'dids', 'string')
 101                         ->addScalarResult('sids', 'sids', 'string')
 102                         ->addIndexByScalar('tid');
 103
 104                 //Set result array
 105                 $result = [];
 106
 107                 //Get tokens
 108                 $tokens = $this->_em
 109                         ->createNativeQuery($req, $rsm)
 110                         ->getArrayResult();
 111
 112                 //Iterate on tokens
 113                 foreach($tokens as $tid => $token) {
 114                         //Set cids
 115                         $cids = explode("\n", $token['cids']);
 116
 117                         //Set cmails
 118                         $cmails = explode("\n", $token['cmails']);
 119
 120                         //Set csummaries
 121                         $csummaries = explode("\n", $token['csummaries']);
 122
 123                         //Set csynchronizeds
 124                         $csynchronizeds = array_map(function($v){return new \DateTime($v);}, explode("\n", $token['csynchronizeds']));
 125
 126                         //Set result
 127                         $result[$tid] = [
 128                                 'id' => $tid,
 129                                 'mail' => $token['gmail'],
 130                                 'uid' => $token['uid'],
 131                                 'access' => $token['access'],
 132                                 'refresh' => $token['refresh'],
 133                                 'expired' => $token['expired'],
 134                                 'calendars' => [],
 135                                 'dances' => [],
 136                                 'subscriptions' => []
 137                         ];
 138
 139                         //Iterate on calendars
 140                         foreach($cids as $k => $cid) {
 141                                 $result[$tid]['calendars'][$cid] = [
 142                                         'id' => $cid,
 143                                         'mail' => $cmails[$k],
 144                                         'summary' => $csummaries[$k],
 145                                         'synchronized' => $csynchronizeds[$k]
 146                                 ];
 147                         }
 148
 149                         //Set dids
 150                         $dids = explode("\n", $token['dids']);
 151
 152                         //Iterate on dances
 153                         foreach($dids as $k => $did) {
 154                                 $result[$tid]['dances'][$did] = [
 155                                         'id' => $did
 156                                 ];
 157                         }
 158
 159                         //Set sids
 160                         $sids = explode("\n", $token['sids']);
 161
 162                         //Iterate on subscriptions
 163                         foreach($sids as $k => $sid) {
 164                                 $result[$tid]['subscriptions'][$sid] = [
 165                                         'id' => $sid
 166                                 ];
 167                         }
 168                 }
 169
 170                 //Return result
 171                 return $result;
 172         }
 173 }