]> Raphaël G. Git Repositories - airbundle/blob - Repository/GoogleTokenRepository.php
Add created field 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.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 }