src/applications/phrequent/query/PhrequentUserTimeQuery.php

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