PHP Query::setOffset方法代码示例

 public static function getList($parameters = array())
 {
     $query = new Query(static::getEntity());
     if (!isset($parameters['select'])) {
         $query->setSelect(array('*'));
     }
     foreach ($parameters as $param => $value) {
         switch ($param) {
             case 'select':
                 $query->setSelect($value);
                 break;
             case 'filter':
                 $query->setFilter($value);
                 break;
             case 'group':
                 $query->setGroup($value);
                 break;
             case 'order':
                 $query->setOrder($value);
                 break;
             case 'limit':
                 $query->setLimit($value);
                 break;
             case 'offset':
                 $query->setOffset($value);
                 break;
             case 'count_total':
                 $query->countTotal($value);
                 break;
             case 'options':
                 $query->setOptions($value);
                 break;
             case 'runtime':
                 foreach ($value as $name => $fieldInfo) {
                     $query->registerRuntimeField($name, $fieldInfo);
                 }
                 break;
             case 'data_doubling':
                 if ($value) {
                     $query->enableDataDoubling();
                 } else {
                     $query->disableDataDoubling();
                 }
                 break;
             default:
                 throw new Main\ArgumentException("Unknown parameter: " . $param, $param);
         }
     }
     return $query->exec();
 }

 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;
 }

 function compatibleNavQuery(Query $query, array $arNavStartParams)
 {
     $cnt = $query->exec()->getSelectedRowsCount();
     // TODO check groups
     global $DB;
     if (isset($arNavStartParams["SubstitutionFunction"])) {
         $arNavStartParams["SubstitutionFunction"]($this, $query->getLastQuery(), $cnt, $arNavStartParams);
         return null;
     }
     if (isset($arNavStartParams["bDescPageNumbering"])) {
         $bDescPageNumbering = $arNavStartParams["bDescPageNumbering"];
     } else {
         $bDescPageNumbering = false;
     }
     $this->InitNavStartVars($arNavStartParams);
     $this->NavRecordCount = $cnt;
     if ($this->NavShowAll) {
         $this->NavPageSize = $this->NavRecordCount;
     }
     //calculate total pages depend on rows count. start with 1
     $this->NavPageCount = $this->NavPageSize > 0 ? floor($this->NavRecordCount / $this->NavPageSize) : 0;
     if ($bDescPageNumbering) {
         $makeweight = 0;
         if ($this->NavPageSize > 0) {
             $makeweight = $this->NavRecordCount % $this->NavPageSize;
         }
         if ($this->NavPageCount == 0 && $makeweight > 0) {
             $this->NavPageCount = 1;
         }
         //page number to display
         $this->NavPageNomer = $this->PAGEN < 1 || $this->PAGEN > $this->NavPageCount ? $_SESSION[$this->SESS_PAGEN] < 1 || $_SESSION[$this->SESS_PAGEN] > $this->NavPageCount ? $this->NavPageCount : $_SESSION[$this->SESS_PAGEN] : $this->PAGEN;
         //rows to skip
         $NavFirstRecordShow = 0;
         if ($this->NavPageNomer != $this->NavPageCount) {
             $NavFirstRecordShow += $makeweight;
         }
         $NavFirstRecordShow += ($this->NavPageCount - $this->NavPageNomer) * $this->NavPageSize;
         $NavLastRecordShow = $makeweight + ($this->NavPageCount - $this->NavPageNomer + 1) * $this->NavPageSize;
     } else {
         if ($this->NavPageSize > 0 && $this->NavRecordCount % $this->NavPageSize > 0) {
             $this->NavPageCount++;
         }
         //calculate total pages depend on rows count. start with 1
         if ($this->PAGEN >= 1 && $this->PAGEN <= $this->NavPageCount) {
             $this->NavPageNomer = $this->PAGEN;
         } elseif ($_SESSION[$this->SESS_PAGEN] >= 1 && $_SESSION[$this->SESS_PAGEN] <= $this->NavPageCount) {
             $this->NavPageNomer = $_SESSION[$this->SESS_PAGEN];
         } elseif ($arNavStartParams["checkOutOfRange"] !== true) {
             $this->NavPageNomer = 1;
         } else {
             return null;
         }
         //rows to skip
         $NavFirstRecordShow = $this->NavPageSize * ($this->NavPageNomer - 1);
         $NavLastRecordShow = $this->NavPageSize * $this->NavPageNomer;
     }
     $NavAdditionalRecords = 0;
     if (is_set($arNavStartParams, "iNavAddRecords")) {
         $NavAdditionalRecords = $arNavStartParams["iNavAddRecords"];
     }
     if (!$this->NavShowAll) {
         $query->setOffset($NavFirstRecordShow);
         $query->setLimit($NavLastRecordShow - $NavFirstRecordShow + $NavAdditionalRecords);
     }
     $res_tmp = $query->exec();
     //, $bIgnoreErrors);
     //		// Return false on sql errors (if $bIgnoreErrors == true)
     //		if ($bIgnoreErrors && ($res_tmp === false))
     //			return false;
     //		$this->result = $res_tmp->result;
     $this->DB = DB;
     if ($this->SqlTraceIndex) {
         $start_time = microtime(true);
     }
     $temp_arrray = array();
     $temp_arrray_add = array();
     $tmp_cnt = 0;
     while ($ar = $res_tmp->fetch()) {
         $tmp_cnt++;
         if (intval($NavLastRecordShow - $NavFirstRecordShow) > 0 && $tmp_cnt > $NavLastRecordShow - $NavFirstRecordShow) {
             $temp_arrray_add[] = $ar;
         } else {
             $temp_arrray[] = $ar;
         }
     }
     if ($this->SqlTraceIndex) {
         /** @noinspection PhpUndefinedVariableInspection */
         $exec_time = round(microtime(true) - $start_time, 10);
         $DB->addDebugTime($this->SqlTraceIndex, $exec_time);
         $DB->timeQuery += $exec_time;
     }
     $this->arResult = !empty($temp_arrray) ? $temp_arrray : false;
     $this->arResultAdd = !empty($temp_arrray_add) ? $temp_arrray_add : false;
     $this->nSelectedCount = $cnt;
     $this->bDescPageNumbering = $bDescPageNumbering;
     $this->bFromLimited = true;
     return null;
 }

 /**
  * @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());
//.........这里部分代码省略.........

// limit
$limit = array('nPageSize' => $arParams['ROWS_PER_PAGE'], 'iNumPage' => is_set($_GET['PAGEN_1']) ? $_GET['PAGEN_1'] : 1, 'bShowAll' => true);
// execute query
$main_query = new Entity\Query($entity);
$main_query->setSelect(array('*'));
$main_query->setOrder(array($sort_id => $sort_type));
//$main_query->setSelect($select)
//	->setFilter($filter)
//	->setGroup($group)
//	->setOrder($order)
//	->setOptions($options);
if (isset($limit['nPageTop'])) {
    $main_query->setLimit($limit['nPageTop']);
} else {
    $main_query->setLimit($limit['nPageSize']);
    $main_query->setOffset(($limit['iNumPage'] - 1) * $limit['nPageSize']);
}
//$main_query->setLimit($limit['nPageSize']);
//$main_query->setOffset(($limit['iNumPage']-1) * $limit['nPageSize']);
$result = $main_query->exec();
$result = new CDBResult($result);
// build results
$rows = array();
$tableColumns = array();
while ($row = $result->Fetch()) {
    foreach ($row as $k => $v) {
        if ($k == 'ID') {
            $tableColumns['ID'] = true;
            continue;
        }
        $arUserField = $fields[$k];