PHP Query::addOrder方法代码示例

 /**
  * @return array
  */
 public function getList(array $params)
 {
     /** @var Filter $filter */
     $filter = isset($params['filter']) ? $params['filter'] : null;
     if (!$filter instanceof Filter) {
         throw new Main\ObjectNotFoundException("The 'filter' is not found in params.");
     }
     /** @var array $select */
     $select = isset($params['select']) && is_array($params['select']) ? $params['select'] : array();
     $name = '';
     if (!empty($select)) {
         $selectItem = $select[0];
         if (isset($selectItem['name'])) {
             $name = $selectItem['name'];
         }
     }
     if ($name === '') {
         $name = 'COUNT';
     }
     $group = isset($params['group']) ? strtoupper($params['group']) : '';
     if ($group !== '' && $group !== self::GROUP_BY_DATE) {
         $group = '';
     }
     $enableGroupByDate = $group !== '';
     $period = $filter->getPeriod();
     $periodStartDate = $period['START'];
     $periodEndDate = $period['END'];
     $query = new Query(DealStageHistoryTable::getEntity());
     $query->registerRuntimeField('', new ExpressionField($name, "COUNT(*)"));
     $query->addSelect($name);
     $query->addFilter('=TYPE_ID', HistoryEntryType::CREATION);
     $query->addFilter('>=START_DATE', $periodStartDate);
     $query->addFilter('<=START_DATE', $periodEndDate);
     if ($enableGroupByDate) {
         $query->addSelect('START_DATE', 'DATE');
         $query->addGroup('START_DATE');
         $query->addOrder('START_DATE', 'ASC');
     }
     $query->registerRuntimeField('', new ExpressionField('E1', '(CASE WHEN NOT EXISTS(' . self::prepareHistoryQuery($periodStartDate, $periodEndDate, HistoryEntryType::MODIFICATION, '%s', '_h')->getQuery() . ') THEN 1 ELSE 0 END)', 'OWNER_ID'));
     $query->addFilter('=E1', 1);
     $query->registerRuntimeField('', new ExpressionField('E2', '(CASE WHEN NOT EXISTS(' . self::prepareHistoryQuery($periodStartDate, $periodEndDate, HistoryEntryType::FINALIZATION, '%s', '_h')->getQuery() . ') THEN 1 ELSE 0 END)', 'OWNER_ID'));
     $query->addFilter('=E2', 1);
     $query->registerRuntimeField('', new ExpressionField('E3', '(CASE WHEN NOT EXISTS(' . self::prepareActivityQuery($periodStartDate, $periodEndDate, '%s', '_a')->getQuery() . ') THEN 1 ELSE 0 END)', 'OWNER_ID'));
     $query->addFilter('=E3', 1);
     $query->registerRuntimeField('', new ExpressionField('E4', '(CASE WHEN NOT EXISTS(' . self::prepareInvoiceQuery($periodStartDate, $periodEndDate, '%s', '_i')->getQuery() . ') THEN 1 ELSE 0 END)', 'OWNER_ID'));
     $query->addFilter('=E4', 1);
     $results = array();
     $dbResult = $query->exec();
     while ($ary = $dbResult->fetch()) {
         if ($enableGroupByDate) {
             /** @var Date $date */
             $date = $ary['DATE'];
             $ary['DATE'] = $date->format('Y-m-d');
         }
         $results[] = $ary;
     }
     return $results;
 }

 /**
  * @return array
  */
 public static function getAll($ownerID)
 {
     if (!is_int($ownerID)) {
         $ownerID = (int) $ownerID;
     }
     if ($ownerID <= 0) {
         throw new Main\ArgumentException('Owner ID must be greater than zero.', 'ownerID');
     }
     $query = new Query(DealSumStatisticsTable::getEntity());
     $query->addSelect('*');
     $query->addFilter('=OWNER_ID', $ownerID);
     $query->addOrder('CREATED_DATE', 'ASC');
     $dbResult = $query->exec();
     $results = array();
     while ($fields = $dbResult->fetch()) {
         $results[] = $fields;
     }
     return $results;
 }

 public static function getMismatches($entityTypeID, $entityID, $typeID, $matchHash, $userID, $limit = 0)
 {
     if (!is_int($limit)) {
         $limit = (int) $limit;
     }
     $results = array();
     $query = new Main\Entity\Query(Entity\DuplicateIndexMismatchTable::getEntity());
     $query->addSelect('R_ENTITY_ID', 'ENTITY_ID');
     $query->addFilter('=USER_ID', $userID);
     $query->addFilter('=ENTITY_TYPE_ID', $entityTypeID);
     $query->addFilter('=TYPE_ID', $typeID);
     $query->addFilter('=MATCH_HASH', $matchHash);
     $query->addFilter('=L_ENTITY_ID', $entityID);
     if ($limit > 0) {
         $query->addOrder('R_ENTITY_ID', 'ASC');
         $query->setLimit($limit);
     }
     $dbResult = $query->exec();
     while ($fields = $dbResult->fetch()) {
         $results[] = (int) $fields['ENTITY_ID'];
     }
     $query = new Main\Entity\Query(Entity\DuplicateIndexMismatchTable::getEntity());
     $query->addSelect('L_ENTITY_ID', 'ENTITY_ID');
     $query->addFilter('=USER_ID', $userID);
     $query->addFilter('=ENTITY_TYPE_ID', $entityTypeID);
     $query->addFilter('=TYPE_ID', $typeID);
     $query->addFilter('=MATCH_HASH', $matchHash);
     $query->addFilter('=R_ENTITY_ID', $entityID);
     if ($limit > 0) {
         $query->addOrder('L_ENTITY_ID', 'ASC');
         $query->setLimit($limit);
     }
     $dbResult = $query->exec();
     while ($fields = $dbResult->fetch()) {
         $results[] = (int) $fields['ENTITY_ID'];
     }
     return $results;
 }

 /** @return array */
 public function getList(array $params)
 {
     $group = isset($params['group']) ? strtoupper($params['group']) : '';
     if ($group !== '' && $group !== self::GROUP_BY_USER && $group !== self::GROUP_BY_DATE) {
         $group = '';
     }
     /** @var Filter $filter */
     $filter = isset($params['filter']) ? $params['filter'] : null;
     if (!$filter instanceof Filter) {
         throw new Main\ObjectNotFoundException("The 'filter' is not found in params.");
     }
     $semanticID = $filter->getExtraParam('semanticID', PhaseSemantics::UNDEFINED);
     $isFinalSemantics = PhaseSemantics::isFinal($semanticID);
     $group = isset($params['group']) ? strtoupper($params['group']) : '';
     if ($group !== '' && $group !== self::GROUP_BY_USER && $group !== self::GROUP_BY_DATE) {
         $group = '';
     }
     /** @var array $select */
     $select = isset($params['select']) && is_array($params['select']) ? $params['select'] : array();
     $name = '';
     $aggregate = '';
     if (!empty($select)) {
         $selectItem = $select[0];
         if (isset($selectItem['name'])) {
             $name = $selectItem['name'];
         }
         if (isset($selectItem['aggregate'])) {
             $aggregate = strtoupper($selectItem['aggregate']);
         }
     }
     if ($name === '') {
         $name = 'INVOICE_SUM';
     }
     if ($aggregate !== '' && !in_array($aggregate, array('SUM', 'COUNT', 'MAX', 'MIN'))) {
         $aggregate = '';
     }
     $permissionSql = '';
     if ($this->enablePermissionCheck) {
         $permissionSql = $this->preparePermissionSql();
         if ($permissionSql === false) {
             //Access denied;
             return array();
         }
     }
     $period = $filter->getPeriod();
     $periodStartDate = $period['START'];
     $periodEndDate = $period['END'];
     $query = new Query(DealInvoiceStatisticsTable::getEntity());
     $query->addSelect($name);
     if ($aggregate !== '') {
         if ($aggregate === 'COUNT') {
             $query->registerRuntimeField('', new ExpressionField($name, "COUNT(*)"));
         } else {
             $query->registerRuntimeField('', new ExpressionField($name, "{$aggregate}({$name})"));
         }
     }
     $query->setTableAliasPostfix('_s2');
     $subQuery = new Query(DealInvoiceStatisticsTable::getEntity());
     $subQuery->setTableAliasPostfix('_s1');
     $subQuery->addSelect('OWNER_ID');
     $subQuery->addFilter('>=END_DATE', $periodStartDate);
     $subQuery->addFilter('<=START_DATE', $periodEndDate);
     //$subQuery->addFilter('<=CREATED_DATE', $periodEndDate);
     if ($semanticID !== PhaseSemantics::UNDEFINED) {
         $subQuery->addFilter('=STAGE_SEMANTIC_ID', $semanticID);
     }
     if ($this->enablePermissionCheck && is_string($permissionSql) && $permissionSql !== '') {
         $subQuery->addFilter('@OWNER_ID', new SqlExpression($permissionSql));
     }
     $responsibleIDs = $filter->getResponsibleIDs();
     if (!empty($responsibleIDs)) {
         $subQuery->addFilter('@RESPONSIBLE_ID', $responsibleIDs);
     }
     $subQuery->addGroup('OWNER_ID');
     $subQuery->addSelect('MAX_CREATED_DATE');
     $subQuery->registerRuntimeField('', new ExpressionField('MAX_CREATED_DATE', 'MAX(CREATED_DATE)'));
     $query->registerRuntimeField('', new ReferenceField('M', Base::getInstanceByQuery($subQuery), array('=this.OWNER_ID' => 'ref.OWNER_ID', '=this.CREATED_DATE' => 'ref.MAX_CREATED_DATE'), array('join_type' => 'INNER')));
     $sort = isset($params['sort']) && is_array($params['sort']) && !empty($params['sort']) ? $params['sort'] : null;
     if ($sort) {
         foreach ($sort as $sortItem) {
             if (isset($sortItem['name'])) {
                 $query->addOrder($sortItem['name'], isset($sortItem['order']) ? $sortItem['order'] : 'asc');
             }
         }
     }
     if ($group !== '') {
         if ($group === self::GROUP_BY_USER) {
             $query->addSelect('RESPONSIBLE_ID');
             $query->addGroup('RESPONSIBLE_ID');
         } else {
             if ($group === self::GROUP_BY_DATE) {
                 if ($isFinalSemantics) {
                     $query->addSelect('END_DATE', 'D');
                     $query->addGroup('END_DATE');
                     if (!$sort) {
                         $query->addOrder('END_DATE', 'ASC');
                     }
                 } else {
                     $query->addSelect('CREATED_DATE', 'D');
//.........这里部分代码省略.........

 /**
  * @return Query
  */
 protected static function prepareInvoiceQuery($startDate, $endDate, $responsibleIDs = null, $groupByDate = true)
 {
     $query = new Query(DealInvoiceStatisticsTable::getEntity());
     $query->addSelect('OWNER_ID');
     $query->addFilter('=IS_LOST', false);
     $query->addFilter('>=CREATED_DATE', $startDate);
     $query->addFilter('<=CREATED_DATE', $endDate);
     $query->addGroup('OWNER_ID');
     if (is_array($responsibleIDs) && !empty($responsibleIDs)) {
         $query->addFilter('@RESPONSIBLE_ID', $responsibleIDs);
     }
     if ($groupByDate) {
         $query->addSelect('CREATED_DATE', 'DATE');
         $query->addGroup('CREATED_DATE');
         $query->addOrder('CREATED_DATE', 'ASC');
     }
     return $query;
 }

 public static function getUsersTop($userId, $departmentId, Type\DateTime $dateFrom, Type\DateTime $dateTo, $interval, $section = null, $nonInvolvedOnly = false, $from = 0, $limit = 100)
 {
     if (!in_array($interval, array('hour', 'day', 'month'), true)) {
         throw new Main\ArgumentException('Interval should be the "hour", or "day", or "month".');
     }
     $data = array();
     // rating for TOTAL activity or for an instrument
     $sumField = $section === null ? 'TOTAL' : $section;
     if ($interval === 'hour') {
         $query = new Entity\Query(UserHourTable::getEntity());
         $query->setSelect(array('USER_ID', new Entity\ExpressionField('SUM_' . $sumField, 'SUM(%s)', $sumField)));
         $query->setFilter(array('><HOUR' => array(ConvertTimeStamp($dateFrom->getTimestamp(), 'FULL'), ConvertTimeStamp($dateTo->getTimestamp(), 'FULL'))));
     } else {
         $query = new Entity\Query(UserDayTable::getEntity());
         $query->setSelect(array('USER_ID', new Entity\ExpressionField('SUM_' . $sumField, 'SUM(%s)', $sumField)));
         $query->setFilter(array('><DAY' => array(ConvertTimeStamp($dateFrom->getTimestamp()), ConvertTimeStamp($dateTo->getTimestamp()))));
     }
     if ($sumField == 'TOTAL') {
         // count number of used services
         $names = UserHourTable::getSectionNames();
         $fieldExpressions = array_fill(0, count($names), 'CASE WHEN SUM(%s) > 0 THEN 1 ELSE 0 END');
         $serviceCountExpression = join(' + ', $fieldExpressions);
         $query->addSelect(new Entity\ExpressionField('SERVICES_COUNT', $serviceCountExpression, $names));
         if ($nonInvolvedOnly) {
             // who didn't use 4 or more instruments
             $query->addFilter('<SERVICES_COUNT', static::INVOLVEMENT_SERVICE_COUNT);
         }
     } else {
         if ($nonInvolvedOnly) {
             // who didn't use instrument
             $query->addFilter('=SUM_' . $sumField, 0);
         } else {
             // who used it
             $query->addFilter('>SUM_' . $sumField, 0);
         }
     }
     $query->addOrder('SUM_' . $sumField, 'DESC');
     if (!$nonInvolvedOnly) {
         // we don't need this for non-involved users
         $query->registerRuntimeField('MYSELF', array('data_type' => 'integer', 'expression' => array('CASE WHEN %s = ' . (int) $userId . ' THEN 1 ELSE 0 END', 'USER_ID')));
         $query->addOrder('MYSELF', 'DESC');
     }
     $query->setOffset($from);
     $query->setLimit($limit);
     $result = $query->exec();
     while ($row = $result->fetch()) {
         $_data = array('USER_ID' => $row['USER_ID'], 'ACTIVITY' => $row['SUM_' . $sumField]);
         if ($sumField == 'TOTAL') {
             $_data['SERVICES_COUNT'] = $row['SERVICES_COUNT'];
             $_data['IS_INVOLVED'] = $row['SERVICES_COUNT'] >= static::INVOLVEMENT_SERVICE_COUNT;
         } else {
             $_data['SERVICES_COUNT'] = null;
             $_data['IS_INVOLVED'] = $row['SUM_' . $sumField] > 0;
         }
         $data[] = $_data;
     }
     return $data;
 }

 /**
  * @return Main\Entity\Query
  */
 private function createQuery($offset = 0, $limit = 0)
 {
     if (!is_int($offset)) {
         $offset = intval($offset);
     }
     if (!is_int($limit)) {
         $limit = intval($limit);
     }
     $typeIDs = $this->getTypeIDs();
     if (empty($typeIDs)) {
         throw new Main\NotSupportedException("Criterion types are required.");
     }
     $query = new Main\Entity\Query(Entity\DuplicateIndexTable::getEntity());
     $query->addSelect('ROOT_ENTITY_ID');
     $query->addSelect('ROOT_ENTITY_NAME');
     $query->addSelect('ROOT_ENTITY_TITLE');
     $query->addSelect('QUANTITY');
     $query->addSelect('TYPE_ID');
     $query->addSelect('MATCHES');
     $query->addSelect('IS_JUNK');
     $permissionSql = '';
     if ($this->enablePermissionCheck) {
         $permissions = \CCrmPerms::GetUserPermissions($this->userID);
         $permissionSql = \CCrmPerms::BuildSql(\CCrmOwnerType::ResolveName($this->entityTypeID), '', 'READ', array('RAW_QUERY' => true, 'PERMS' => $permissions));
         if ($permissionSql === false) {
             //Access denied;
             return null;
         }
     }
     $query->addFilter('=USER_ID', $this->userID);
     $query->addFilter('=ENTITY_TYPE_ID', $this->entityTypeID);
     $query->addFilter('@TYPE_ID', $typeIDs);
     if ($this->enablePermissionCheck && $permissionSql !== '') {
         $query->addFilter('@ROOT_ENTITY_ID', new Main\DB\SqlExpression($permissionSql));
     }
     if ($offset > 0) {
         $query->setOffset($offset);
     }
     if ($limit > 0) {
         $query->setLimit($limit);
     }
     $enableSorting = $this->sortTypeID !== DuplicateIndexType::UNDEFINED;
     if ($enableSorting) {
         $order = $this->sortOrder === SORT_DESC ? 'DESC' : 'ASC';
         if ($this->sortTypeID === DuplicateIndexType::COMMUNICATION_EMAIL) {
             $query->addOrder('ROOT_ENTITY_EMAIL_FLAG', $order);
             $query->addOrder('ROOT_ENTITY_EMAIL', $order);
         } elseif ($this->sortTypeID === DuplicateIndexType::COMMUNICATION_PHONE) {
             $query->addOrder('ROOT_ENTITY_PHONE_FLAG', $order);
             $query->addOrder('ROOT_ENTITY_PHONE', $order);
         } elseif ($this->sortTypeID === DuplicateIndexType::PERSON) {
             $query->addOrder('ROOT_ENTITY_NAME_FLAG', $order);
             $query->addOrder('ROOT_ENTITY_NAME', $order);
         } elseif ($this->sortTypeID === DuplicateIndexType::ORGANIZATION) {
             $query->addOrder('ROOT_ENTITY_TITLE_FLAG', $order);
             $query->addOrder('ROOT_ENTITY_TITLE', $order);
         }
     }
     return $query;
 }

 /**
  * @return DedupeDataSourceResult
  */
 public function getList($offset, $limit)
 {
     $result = new DedupeDataSourceResult();
     $typeID = $this->typeID;
     $entityTypeID = $this->getEntityTypeID();
     $enablePermissionCheck = $this->isPermissionCheckEnabled();
     //$userID = $this->getUserID();
     $query = new Main\Entity\Query(Entity\DuplicateEntityMatchHashTable::getEntity());
     $query->addSelect('MATCH_HASH');
     $query->addGroup('MATCH_HASH');
     $query->addOrder('MATCH_HASH', 'ASC');
     $query->registerRuntimeField('', new Main\Entity\ExpressionField('QTY', 'COUNT(*)'));
     $query->addSelect('QTY');
     $query->addFilter('>QTY', 1);
     $query->addFilter('=ENTITY_TYPE_ID', $entityTypeID);
     $query->addFilter('=TYPE_ID', $typeID);
     $permissionSql = '';
     if ($enablePermissionCheck) {
         $permissionSql = $this->preparePermissionSql();
         if ($permissionSql === false) {
             //Access denied;
             return $result;
         }
         if ($permissionSql !== '') {
             $query->addFilter('@ENTITY_ID', new Main\DB\SqlExpression($permissionSql));
         }
     }
     if (!is_int($offset)) {
         $offset = (int) $offset;
     }
     if ($offset > 0) {
         $query->setOffset($offset);
     }
     if (!is_int($limit)) {
         $limit = (int) $limit;
     }
     if ($limit > 0) {
         $query->setLimit($limit);
     }
     $dbResult = $query->exec();
     $processedItemCount = 0;
     $lightHashes = array();
     $heavyHashes = array();
     while ($fields = $dbResult->fetch()) {
         $processedItemCount++;
         $quantity = isset($fields['QTY']) ? (int) $fields['QTY'] : 0;
         $matchHash = isset($fields['MATCH_HASH']) ? $fields['MATCH_HASH'] : '';
         if ($matchHash === '' || $quantity < 2) {
             continue;
         }
         if ($quantity <= 100) {
             $lightHashes[] = $matchHash;
         } else {
             $heavyHashes[] = $matchHash;
         }
     }
     $result->setProcessedItemCount($processedItemCount);
     $map = array();
     if (!empty($heavyHashes)) {
         foreach ($heavyHashes as $matchHash) {
             $query = new Main\Entity\Query(Entity\DuplicateEntityMatchHashTable::getEntity());
             $query->addSelect('ENTITY_ID');
             $query->addSelect('IS_PRIMARY');
             $query->addFilter('=ENTITY_TYPE_ID', $entityTypeID);
             $query->addFilter('=TYPE_ID', $typeID);
             $query->addFilter('=MATCH_HASH', $matchHash);
             if ($enablePermissionCheck && $permissionSql !== '') {
                 $query->addFilter('@ENTITY_ID', new Main\DB\SqlExpression($permissionSql));
             }
             $query->setOffset(0);
             $query->setLimit(100);
             $dbResult = $query->exec();
             while ($fields = $dbResult->fetch()) {
                 $entityID = isset($fields['ENTITY_ID']) ? (int) $fields['ENTITY_ID'] : 0;
                 if ($entityID <= 0) {
                     continue;
                 }
                 if (!isset($map[$matchHash])) {
                     $map[$matchHash] = array();
                 }
                 $isPrimary = isset($fields['IS_PRIMARY']) && $fields['IS_PRIMARY'] === 'Y';
                 if ($isPrimary) {
                     if (!isset($map[$matchHash]['PRIMARY'])) {
                         $map[$matchHash]['PRIMARY'] = array();
                     }
                     $map[$matchHash]['PRIMARY'][] = $entityID;
                 } else {
                     if (!isset($map[$matchHash]['SECONDARY'])) {
                         $map[$matchHash]['SECONDARY'] = array();
                     }
                     $map[$matchHash]['SECONDARY'][] = $entityID;
                 }
             }
         }
     }
     if (!empty($lightHashes)) {
         $query = new Main\Entity\Query(Entity\DuplicateEntityMatchHashTable::getEntity());
//.........这里部分代码省略.........

 /**
  * @return Query
  */
 protected static function prepareInvoiceQuery($startDate, $endDate, $groupByDate = true)
 {
     $query = new Query(DealInvoiceStatisticsTable::getEntity());
     $query->addSelect('OWNER_ID');
     $query->addFilter('=IS_LOST', false);
     $query->addFilter('>=CREATED_DATE', $startDate);
     $query->addFilter('<=CREATED_DATE', $endDate);
     $query->addGroup('OWNER_ID');
     if ($groupByDate) {
         $query->addSelect('CREATED_DATE', 'DATE');
         $query->addGroup('CREATED_DATE');
         $query->addOrder('CREATED_DATE', 'ASC');
     }
     return $query;
 }