recaptime.dev
@recaptime-dev's working patches + fork for Phorge, a community fork of Phabricator. (Upstream dev and stable branches are at upstream/main and upstream/stable respectively.)
hq.recaptime.dev/wiki/Phorge
phorge
phabricator
1<?php
2
3/**
4 * @extends PhabricatorCursorPagedPolicyAwareQuery<PhrequentUserTime>
5 */
6final class PhrequentUserTimeQuery
7 extends PhabricatorCursorPagedPolicyAwareQuery {
8
9 const ORDER_ID_ASC = 0;
10 const ORDER_ID_DESC = 1;
11 const ORDER_STARTED_ASC = 2;
12 const ORDER_STARTED_DESC = 3;
13 const ORDER_ENDED_ASC = 4;
14 const ORDER_ENDED_DESC = 5;
15
16 const ENDED_YES = 0;
17 const ENDED_NO = 1;
18 const ENDED_ALL = 2;
19
20 private $ids;
21 private $userPHIDs;
22 private $objectPHIDs;
23 private $ended = self::ENDED_ALL;
24
25 private $needPreemptingEvents;
26
27 public function withIDs(array $ids) {
28 $this->ids = $ids;
29 return $this;
30 }
31
32 public function withUserPHIDs(array $user_phids) {
33 $this->userPHIDs = $user_phids;
34 return $this;
35 }
36
37 public function withObjectPHIDs(array $object_phids) {
38 $this->objectPHIDs = $object_phids;
39 return $this;
40 }
41
42 public function withEnded($ended) {
43 $this->ended = $ended;
44 return $this;
45 }
46
47 public function setOrder($order) {
48 switch ($order) {
49 case self::ORDER_ID_ASC:
50 $this->setOrderVector(array('-id'));
51 break;
52 case self::ORDER_ID_DESC:
53 $this->setOrderVector(array('id'));
54 break;
55 case self::ORDER_STARTED_ASC:
56 $this->setOrderVector(array('-start', '-id'));
57 break;
58 case self::ORDER_STARTED_DESC:
59 $this->setOrderVector(array('start', 'id'));
60 break;
61 case self::ORDER_ENDED_ASC:
62 $this->setOrderVector(array('-end', '-id'));
63 break;
64 case self::ORDER_ENDED_DESC:
65 $this->setOrderVector(array('end', 'id'));
66 break;
67 default:
68 throw new Exception(pht('Unknown order "%s".', $order));
69 }
70
71 return $this;
72 }
73
74 public function needPreemptingEvents($need_events) {
75 $this->needPreemptingEvents = $need_events;
76 return $this;
77 }
78
79 protected function buildWhereClause(AphrontDatabaseConnection $conn) {
80 $where = array();
81
82 if ($this->ids !== null) {
83 $where[] = qsprintf(
84 $conn,
85 'id IN (%Ld)',
86 $this->ids);
87 }
88
89 if ($this->userPHIDs !== null) {
90 $where[] = qsprintf(
91 $conn,
92 'userPHID IN (%Ls)',
93 $this->userPHIDs);
94 }
95
96 if ($this->objectPHIDs !== null) {
97 $where[] = qsprintf(
98 $conn,
99 'objectPHID IN (%Ls)',
100 $this->objectPHIDs);
101 }
102
103 switch ($this->ended) {
104 case self::ENDED_ALL:
105 break;
106 case self::ENDED_YES:
107 $where[] = qsprintf(
108 $conn,
109 'dateEnded IS NOT NULL');
110 break;
111 case self::ENDED_NO:
112 $where[] = qsprintf(
113 $conn,
114 'dateEnded IS NULL');
115 break;
116 default:
117 throw new Exception(pht("Unknown ended '%s'!", $this->ended));
118 }
119
120 $where[] = $this->buildPagingClause($conn);
121
122 return $this->formatWhereClause($conn, $where);
123 }
124
125 public function getOrderableColumns() {
126 return parent::getOrderableColumns() + array(
127 'start' => array(
128 'column' => 'dateStarted',
129 'type' => 'int',
130 ),
131 'end' => array(
132 'column' => 'dateEnded',
133 'type' => 'int',
134 'null' => 'head',
135 ),
136 );
137 }
138
139 protected function newPagingMapFromPartialObject($object) {
140 return array(
141 'id' => (int)$object->getID(),
142 'start' => (int)$object->getDateStarted(),
143 'end' => (int)$object->getDateEnded(),
144 );
145 }
146
147 protected function loadPage() {
148 $usertime = new PhrequentUserTime();
149 $conn = $usertime->establishConnection('r');
150
151 $data = queryfx_all(
152 $conn,
153 'SELECT usertime.* FROM %T usertime %Q %Q %Q',
154 $usertime->getTableName(),
155 $this->buildWhereClause($conn),
156 $this->buildOrderClause($conn),
157 $this->buildLimitClause($conn));
158
159 return $usertime->loadAllFromArray($data);
160 }
161
162 protected function didFilterPage(array $page) {
163 if ($this->needPreemptingEvents) {
164 $usertime = new PhrequentUserTime();
165 $conn_r = $usertime->establishConnection('r');
166
167 $preempt = array();
168 foreach ($page as $event) {
169 $preempt[] = qsprintf(
170 $conn_r,
171 '(userPHID = %s AND
172 (dateStarted BETWEEN %d AND %d) AND
173 (dateEnded IS NULL OR dateEnded > %d))',
174 $event->getUserPHID(),
175 $event->getDateStarted(),
176 nonempty($event->getDateEnded(), PhabricatorTime::getNow()),
177 $event->getDateStarted());
178 }
179
180 $preempting_events = queryfx_all(
181 $conn_r,
182 'SELECT * FROM %T WHERE %LO ORDER BY dateStarted ASC, id ASC',
183 $usertime->getTableName(),
184 $preempt);
185 $preempting_events = $usertime->loadAllFromArray($preempting_events);
186
187 $preempting_events = mgroup($preempting_events, 'getUserPHID');
188
189 foreach ($page as $event) {
190 $e_start = $event->getDateStarted();
191 $e_end = $event->getDateEnded();
192
193 $select = array();
194 $user_events = idx($preempting_events, $event->getUserPHID(), array());
195 foreach ($user_events as $u_event) {
196 if ($u_event->getID() == $event->getID()) {
197 // Don't allow an event to preempt itself.
198 continue;
199 }
200
201 $u_start = $u_event->getDateStarted();
202 $u_end = $u_event->getDateEnded();
203
204 if ($u_start < $e_start) {
205 // This event started before our event started, so it's not
206 // preempting us.
207 continue;
208 }
209
210 if ($u_start == $e_start) {
211 if ($u_event->getID() < $event->getID()) {
212 // This event started at the same time as our event started,
213 // but has a lower ID, so it's not preempting us.
214 continue;
215 }
216 }
217
218 if (($e_end !== null) && ($u_start > $e_end)) {
219 // Our event has ended, and this event started after it ended.
220 continue;
221 }
222
223 if (($u_end !== null) && ($u_end < $e_start)) {
224 // This event ended before our event began.
225 continue;
226 }
227
228 $select[] = $u_event;
229 }
230
231 $event->attachPreemptingEvents($select);
232 }
233 }
234
235 return $page;
236 }
237
238/* -( Helper Functions ) --------------------------------------------------- */
239
240 public static function getEndedSearchOptions() {
241 return array(
242 self::ENDED_ALL => pht('All'),
243 self::ENDED_NO => pht('No'),
244 self::ENDED_YES => pht('Yes'),
245 );
246 }
247
248 public static function getOrderSearchOptions() {
249 return array(
250 self::ORDER_STARTED_ASC => pht('by furthest start date'),
251 self::ORDER_STARTED_DESC => pht('by nearest start date'),
252 self::ORDER_ENDED_ASC => pht('by furthest end date'),
253 self::ORDER_ENDED_DESC => pht('by nearest end date'),
254 );
255 }
256
257 public static function getUserTotalObjectsTracked(
258 PhabricatorUser $user,
259 $limit = PHP_INT_MAX) {
260
261 $usertime_dao = new PhrequentUserTime();
262 $conn = $usertime_dao->establishConnection('r');
263
264 $count = queryfx_one(
265 $conn,
266 'SELECT COUNT(usertime.id) N FROM %T usertime '.
267 'WHERE usertime.userPHID = %s '.
268 'AND usertime.dateEnded IS NULL '.
269 'LIMIT %d',
270 $usertime_dao->getTableName(),
271 $user->getPHID(),
272 $limit);
273 return $count['N'];
274 }
275
276 public static function isUserTrackingObject(
277 PhabricatorUser $user,
278 $phid) {
279
280 $usertime_dao = new PhrequentUserTime();
281 $conn = $usertime_dao->establishConnection('r');
282
283 $count = queryfx_one(
284 $conn,
285 'SELECT COUNT(usertime.id) N FROM %T usertime '.
286 'WHERE usertime.userPHID = %s '.
287 'AND usertime.objectPHID = %s '.
288 'AND usertime.dateEnded IS NULL',
289 $usertime_dao->getTableName(),
290 $user->getPHID(),
291 $phid);
292 return $count['N'] > 0;
293 }
294
295 public static function getUserTimeSpentOnObject(
296 PhabricatorUser $user,
297 $phid) {
298
299 $usertime_dao = new PhrequentUserTime();
300 $conn = $usertime_dao->establishConnection('r');
301
302 // First calculate all the time spent where the
303 // usertime blocks have ended.
304 $sum_ended = queryfx_one(
305 $conn,
306 'SELECT SUM(usertime.dateEnded - usertime.dateStarted) N '.
307 'FROM %T usertime '.
308 'WHERE usertime.userPHID = %s '.
309 'AND usertime.objectPHID = %s '.
310 'AND usertime.dateEnded IS NOT NULL',
311 $usertime_dao->getTableName(),
312 $user->getPHID(),
313 $phid);
314
315 // Now calculate the time spent where the usertime
316 // blocks have not yet ended.
317 $sum_not_ended = queryfx_one(
318 $conn,
319 'SELECT SUM(UNIX_TIMESTAMP() - usertime.dateStarted) N '.
320 'FROM %T usertime '.
321 'WHERE usertime.userPHID = %s '.
322 'AND usertime.objectPHID = %s '.
323 'AND usertime.dateEnded IS NULL',
324 $usertime_dao->getTableName(),
325 $user->getPHID(),
326 $phid);
327
328 return $sum_ended['N'] + $sum_not_ended['N'];
329 }
330
331 public function getQueryApplicationClass() {
332 return PhabricatorPhrequentApplication::class;
333 }
334
335}