[Summary view] [Print] [Text view]

   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    const ORDER_DURATION_ASC  = 6;
  13    const ORDER_DURATION_DESC = 7;
  14  
  15    const ENDED_YES = 0;
  16    const ENDED_NO  = 1;
  17    const ENDED_ALL = 2;
  18  
  19    private $userPHIDs;
  20    private $objectPHIDs;
  21    private $order = self::ORDER_ID_ASC;
  22    private $ended = self::ENDED_ALL;
  23  
  24    private $needPreemptingEvents;
  25  
  26    public function withUserPHIDs($user_phids) {
  27      $this->userPHIDs = $user_phids;
  28      return $this;
  29    }
  30  
  31    public function withObjectPHIDs($object_phids) {
  32      $this->objectPHIDs = $object_phids;
  33      return $this;
  34    }
  35  
  36    public function withEnded($ended) {
  37      $this->ended = $ended;
  38      return $this;
  39    }
  40  
  41    public function setOrder($order) {
  42      $this->order = $order;
  43      return $this;
  44    }
  45  
  46    public function needPreemptingEvents($need_events) {
  47      $this->needPreemptingEvents = $need_events;
  48      return $this;
  49    }
  50  
  51    private function buildWhereClause(AphrontDatabaseConnection $conn) {
  52      $where = array();
  53  
  54      if ($this->userPHIDs) {
  55        $where[] = qsprintf(
  56          $conn,
  57          'userPHID IN (%Ls)',
  58          $this->userPHIDs);
  59      }
  60  
  61      if ($this->objectPHIDs) {
  62        $where[] = qsprintf(
  63          $conn,
  64          'objectPHID IN (%Ls)',
  65          $this->objectPHIDs);
  66      }
  67  
  68      switch ($this->ended) {
  69        case self::ENDED_ALL:
  70          break;
  71        case self::ENDED_YES:
  72          $where[] = qsprintf(
  73            $conn,
  74            'dateEnded IS NOT NULL');
  75          break;
  76        case self::ENDED_NO:
  77          $where[] = qsprintf(
  78            $conn,
  79            'dateEnded IS NULL');
  80          break;
  81        default:
  82          throw new Exception("Unknown ended '{$this->ended}'!");
  83      }
  84  
  85      $where[] = $this->buildPagingClause($conn);
  86  
  87      return $this->formatWhereClause($where);
  88    }
  89  
  90    protected function getPagingColumn() {
  91      switch ($this->order) {
  92        case self::ORDER_ID_ASC:
  93        case self::ORDER_ID_DESC:
  94          return 'id';
  95        case self::ORDER_STARTED_ASC:
  96        case self::ORDER_STARTED_DESC:
  97          return 'dateStarted';
  98        case self::ORDER_ENDED_ASC:
  99        case self::ORDER_ENDED_DESC:
 100          return 'dateEnded';
 101        case self::ORDER_DURATION_ASC:
 102        case self::ORDER_DURATION_DESC:
 103          return 'COALESCE(dateEnded, UNIX_TIMESTAMP()) - dateStarted';
 104        default:
 105          throw new Exception("Unknown order '{$this->order}'!");
 106      }
 107    }
 108  
 109    protected function getPagingValue($result) {
 110      switch ($this->order) {
 111        case self::ORDER_ID_ASC:
 112        case self::ORDER_ID_DESC:
 113          return $result->getID();
 114        case self::ORDER_STARTED_ASC:
 115        case self::ORDER_STARTED_DESC:
 116          return $result->getDateStarted();
 117        case self::ORDER_ENDED_ASC:
 118        case self::ORDER_ENDED_DESC:
 119          return $result->getDateEnded();
 120        case self::ORDER_DURATION_ASC:
 121        case self::ORDER_DURATION_DESC:
 122          return ($result->getDateEnded() || time()) - $result->getDateStarted();
 123        default:
 124          throw new Exception("Unknown order '{$this->order}'!");
 125      }
 126    }
 127  
 128    protected function getReversePaging() {
 129      switch ($this->order) {
 130        case self::ORDER_ID_ASC:
 131        case self::ORDER_STARTED_ASC:
 132        case self::ORDER_ENDED_ASC:
 133        case self::ORDER_DURATION_ASC:
 134          return true;
 135        case self::ORDER_ID_DESC:
 136        case self::ORDER_STARTED_DESC:
 137        case self::ORDER_ENDED_DESC:
 138        case self::ORDER_DURATION_DESC:
 139          return false;
 140        default:
 141          throw new Exception("Unknown order '{$this->order}'!");
 142      }
 143    }
 144  
 145    protected function loadPage() {
 146      $usertime = new PhrequentUserTime();
 147      $conn = $usertime->establishConnection('r');
 148  
 149      $data = queryfx_all(
 150        $conn,
 151        'SELECT usertime.* FROM %T usertime %Q %Q %Q',
 152        $usertime->getTableName(),
 153        $this->buildWhereClause($conn),
 154        $this->buildOrderClause($conn),
 155        $this->buildLimitClause($conn));
 156  
 157      return $usertime->loadAllFromArray($data);
 158    }
 159  
 160    protected function didFilterPage(array $page) {
 161      if ($this->needPreemptingEvents) {
 162        $usertime = new PhrequentUserTime();
 163        $conn_r = $usertime->establishConnection('r');
 164  
 165        $preempt = array();
 166        foreach ($page as $event) {
 167          $preempt[] = qsprintf(
 168            $conn_r,
 169            '(userPHID = %s AND
 170              (dateStarted BETWEEN %d AND %d) AND
 171              (dateEnded IS NULL OR dateEnded > %d))',
 172            $event->getUserPHID(),
 173            $event->getDateStarted(),
 174            nonempty($event->getDateEnded(), PhabricatorTime::getNow()),
 175            $event->getDateStarted());
 176        }
 177  
 178        $preempting_events = queryfx_all(
 179          $conn_r,
 180          'SELECT * FROM %T WHERE %Q ORDER BY dateStarted ASC, id ASC',
 181          $usertime->getTableName(),
 182          implode(' OR ', $preempt));
 183        $preempting_events = $usertime->loadAllFromArray($preempting_events);
 184  
 185        $preempting_events = mgroup($preempting_events, 'getUserPHID');
 186  
 187        foreach ($page as $event) {
 188          $e_start = $event->getDateStarted();
 189          $e_end = $event->getDateEnded();
 190  
 191          $select = array();
 192          $user_events = idx($preempting_events, $event->getUserPHID(), array());
 193          foreach ($user_events as $u_event) {
 194            if ($u_event->getID() == $event->getID()) {
 195              // Don't allow an event to preempt itself.
 196              continue;
 197            }
 198  
 199            $u_start = $u_event->getDateStarted();
 200            $u_end = $u_event->getDateEnded();
 201  
 202            if ($u_start < $e_start) {
 203              // This event started before our event started, so it's not
 204              // preempting us.
 205              continue;
 206            }
 207  
 208            if ($u_start == $e_start) {
 209              if ($u_event->getID() < $event->getID()) {
 210                // This event started at the same time as our event started,
 211                // but has a lower ID, so it's not preempting us.
 212                continue;
 213              }
 214            }
 215  
 216            if (($e_end !== null) && ($u_start > $e_end)) {
 217              // Our event has ended, and this event started after it ended.
 218              continue;
 219            }
 220  
 221            if (($u_end !== null) && ($u_end < $e_start)) {
 222              // This event ended before our event began.
 223              continue;
 224            }
 225  
 226            $select[] = $u_event;
 227          }
 228  
 229          $event->attachPreemptingEvents($select);
 230        }
 231      }
 232  
 233      return $page;
 234    }
 235  
 236  /* -(  Helper Functions ) --------------------------------------------------- */
 237  
 238    public static function getEndedSearchOptions() {
 239      return array(
 240        self::ENDED_ALL => pht('All'),
 241        self::ENDED_NO  => pht('No'),
 242        self::ENDED_YES => pht('Yes'),
 243      );
 244    }
 245  
 246    public static function getOrderSearchOptions() {
 247      return array(
 248        self::ORDER_STARTED_ASC   => pht('by furthest start date'),
 249        self::ORDER_STARTED_DESC  => pht('by nearest start date'),
 250        self::ORDER_ENDED_ASC     => pht('by furthest end date'),
 251        self::ORDER_ENDED_DESC    => pht('by nearest end date'),
 252        self::ORDER_DURATION_ASC  => pht('by smallest duration'),
 253        self::ORDER_DURATION_DESC => pht('by largest duration'),
 254      );
 255    }
 256  
 257    public static function getUserTotalObjectsTracked(
 258      PhabricatorUser $user) {
 259  
 260      $usertime_dao = new PhrequentUserTime();
 261      $conn = $usertime_dao->establishConnection('r');
 262  
 263      $count = queryfx_one(
 264        $conn,
 265        'SELECT COUNT(usertime.id) N FROM %T usertime '.
 266        'WHERE usertime.userPHID = %s '.
 267        'AND usertime.dateEnded IS NULL',
 268        $usertime_dao->getTableName(),
 269        $user->getPHID());
 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  }