]> Raphaël G. Git Repositories - airbundle/blob - Repository/GoogleTokenRepository.php
Append dances and subscriptions to findAllIndexed results
[airbundle] / 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 }