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