/** * Executes the query * * @param string $filterField * @param mixed $filterFieldValue * @param string $method * * @return \Bitrix\Main\DB\MysqlResult */ private function execute($filterField, $filterFieldValue, $method) { $queryBuilder = new Entity\Query(Model\VarsGroupTable::getEntity()); $queryBuilder->setSelect(array('ID', 'NAME', 'CODE'))->setOrder(array('ID' => 'ASC'))->setFilter(array($filterField => $filterFieldValue)); if ($method == 'findOneBy') { $queryBuilder->setLimit(1); } return $queryBuilder->exec(); }
public function validate($value, $primary, array $row, Entity\Field $field) { $query = new Entity\Query($this->reference->getEntity()); $query->setFilter(array('=' . $this->reference->getName() => $value) + $this->filter); $query->setLimit(1); $result = $query->exec(); if ($result->fetch()) { return true; } return new Entity\FieldError($field, $this->getErrorMessage($value, $field), self::NOT_EXISTS); }
protected function processActionShowObjectInGrid() { if (!$this->checkRequiredGetParams(array('objectId'))) { $this->sendJsonErrorResponse(); } /** @var Folder|File $object */ $object = BaseObject::loadById((int) $this->request->getQuery('objectId'), array('STORAGE')); if (!$object) { $this->errorCollection->addOne(new Error('Could not find file or folder', self::ERROR_COULD_NOT_FIND_FILE)); $this->sendJsonErrorResponse(); } $storage = $object->getStorage(); $securityContext = $storage->getCurrentUserSecurityContext(); if (!$object->canRead($securityContext)) { $this->errorCollection->addOne(new Error('Could not find file or folder', self::ERROR_COULD_NOT_READ_FILE)); $this->sendJsonErrorResponse(); } $gridOptions = new Internals\Grid\FolderListOptions($storage); $pageSize = $gridOptions->getPageSize(); $parameters = array('select' => array('ID'), 'filter' => array('PARENT_ID' => $object->getParentId(), 'DELETED_TYPE' => ObjectTable::DELETED_TYPE_NONE), 'order' => $gridOptions->getOrderForOrm(), 'limit' => $pageSize); $countQuery = new Query(ObjectTable::getEntity()); $countQuery->addSelect(new ExpressionField('CNT', 'COUNT(1)')); $countQuery->setFilter($parameters['filter']); $totalCount = $countQuery->setLimit(null)->setOffset(null)->exec()->fetch(); $totalCount = $totalCount['CNT']; $pageCount = ceil($totalCount / $pageSize); $driver = Driver::getInstance(); $finalPage = null; for ($pageNumber = 1; $pageNumber <= $pageCount; $pageNumber++) { $fullParameters = $driver->getRightsManager()->addRightsCheck($securityContext, $parameters, array('ID', 'CREATED_BY')); $fullParameters['offset'] = $pageSize * ($pageNumber - 1); $query = ObjectTable::getList($fullParameters); while ($row = $query->fetch()) { if ($row['ID'] == $object->getId()) { $finalPage = $pageNumber; break; } } if ($finalPage !== null) { break; } } $finalPage = $finalPage ?: 1; $command = $this->request->getQuery('cmd') ?: ''; if ($command) { $command = '!' . $command; } LocalRedirect($driver->getUrlManager()->getPathInListing($object) . "?&pageNumber={$finalPage}#hl-" . $object->getId() . $command); }
/** * @return boolean */ public static function isRegistered($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(DealInvoiceStatisticsTable::getEntity()); $query->addSelect('CREATED_DATE'); $query->addFilter('=OWNER_ID', $ownerID); $query->setLimit(1); $dbResult = $query->exec(); $result = $dbResult->fetch(); return is_array($result); }
public function calculateEntityCount(DuplicateCriterion $criterion, array $options = null) { $entityTypeID = $this->getEntityTypeID(); $enablePermissionCheck = $this->isPermissionCheckEnabled(); $userID = $this->getUserID(); $query = new Main\Entity\Query(DuplicateOrganizationMatchCodeTable::getEntity()); $query->addSelect('QTY'); $query->registerRuntimeField('', new Main\Entity\ExpressionField('QTY', 'COUNT(*)')); $query->addFilter('=ENTITY_TYPE_ID', $entityTypeID); if ($enablePermissionCheck) { $permissionSql = $this->preparePermissionSql(); if ($permissionSql === false) { //Access denied; return 0; } if (is_string($permissionSql) && $permissionSql !== '') { $query->addFilter('@ENTITY_ID', new Main\DB\SqlExpression($permissionSql)); } } $matches = $criterion->getMatches(); $title = isset($matches['TITLE']) ? $matches['TITLE'] : ''; if ($title === '') { throw new Main\ArgumentException("Parameter 'TITLE' is required.", 'matches'); } $query->addFilter('=TITLE', $title); $rootEntityID = 0; if (is_array($options) && isset($options['ROOT_ENTITY_ID'])) { $rootEntityID = (int) $options['ROOT_ENTITY_ID']; } if ($rootEntityID > 0) { $query->addFilter('!ENTITY_ID', $rootEntityID); $query->addFilter('!@ENTITY_ID', DuplicateIndexMismatch::prepareQueryField($criterion, $entityTypeID, $rootEntityID, $userID)); } $limit = 0; if (is_array($options) && isset($options['LIMIT'])) { $limit = (int) $options['LIMIT']; } if ($limit > 0) { $query->setLimit($limit); } $dbResult = $query->exec(); $fields = $dbResult->fetch(); return is_array($fields) && isset($fields['QTY']) ? intval($fields['QTY']) : 0; }
public function validate($value, $primary, array $row, Entity\Field $field) { $entity = $field->getEntity(); $primaryNames = $entity->getPrimaryArray(); $query = new Entity\Query($entity); $query->setSelect($primaryNames); $query->setFilter(array('=' . $field->getName() => $value)); $query->setLimit(2); $result = $query->exec(); while ($existing = $result->fetch()) { // check primary foreach ($existing as $k => $v) { if (!isset($primary[$k]) || $primary[$k] != $existing[$k]) { return $this->getErrorMessage($value, $field); } } } return true; }
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 boolean */ public static function synchronize($ownerID, array $entityFields = null) { 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(DealActivityStatisticsTable::getEntity()); $query->addSelect('RESPONSIBLE_ID'); $query->addFilter('=OWNER_ID', $ownerID); $query->setLimit(1); $dbResult = $query->exec(); $first = $dbResult->fetch(); if (!is_array($first)) { return false; } if (!is_array($entityFields)) { $dbResult = \CCrmDeal::GetListEx(array(), array('=ID' => $ownerID, 'CHECK_PERMISSIONS' => 'N'), false, false, array('ASSIGNED_BY_ID')); $entityFields = is_object($dbResult) ? $dbResult->Fetch() : null; if (!is_array($entityFields)) { return false; } } $responsibleID = isset($entityFields['ASSIGNED_BY_ID']) ? (int) $entityFields['ASSIGNED_BY_ID'] : 0; if ($responsibleID === (int) $first['RESPONSIBLE_ID']) { return false; } DealActivityStatisticsTable::synchronize($ownerID, array('RESPONSIBLE_ID' => $responsibleID)); return true; }
$newNum = max(array_keys($viewColumns)) + 1; $queryChains = $main_query->getChains(); if (isset($queryChains[$k])) { $runtimeField = $queryChains[$k]->getLastElement()->getValue(); if (is_array($runtimeField)) { $runtimeField = end($runtimeField); } /*$arUF = CReport::detectUserField($runtimeField, $arUFInfo);*/ $viewColumns[$newNum] = array('field' => $runtimeField, 'fieldName' => $k, 'resultName' => $k, 'humanTitle' => empty($runtimeColumnInfo['humanTitle']) ? '' : $runtimeColumnInfo['humanTitle'], 'defaultSort' => '', 'aggr' => '', 'prcnt' => '', 'href' => empty($runtimeColumnInfo['href']) ? '' : $runtimeColumnInfo['href'], 'grouping' => false, 'grouping_subtotal' => $runtimeColumnInfo['grouping_subtotal'] === true ? true : false, 'runtime' => true); /*unset($arUF);*/ $viewColumnsByResultName[$k] =& $viewColumns[$newNum]; } } } if (isset($limit['nPageTop'])) { $main_query->setLimit($limit['nPageTop']); } $result = $main_query->exec(); $result = new CDBResult($result); if (!$bGroupingMode) { if (isset($limit['nPageTop'])) { $result->NavStart($limit['nPageTop']); } else { $result->NavStart($limit['nPageSize']); } } $data = array(); $grcDataPrimaryValues = array(); $grcDataPrimaryPointers = array(); while ($row = $result->Fetch()) { // rewrite UF values
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(); }
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()); $query->addSelect('ENTITY_ID'); $query->addSelect('MATCH_HASH'); $query->addSelect('IS_PRIMARY'); $query->addFilter('=ENTITY_TYPE_ID', $entityTypeID); $query->addFilter('=TYPE_ID', $typeID); $query->addFilter('@MATCH_HASH', $lightHashes); if ($enablePermissionCheck && $permissionSql !== '') { $query->addFilter('@ENTITY_ID', new Main\DB\SqlExpression($permissionSql)); } $dbResult = $query->exec(); while ($fields = $dbResult->fetch()) { $entityID = isset($fields['ENTITY_ID']) ? (int) $fields['ENTITY_ID'] : 0; if ($entityID <= 0) { continue; } $matchHash = isset($fields['MATCH_HASH']) ? $fields['MATCH_HASH'] : ''; if ($matchHash === '') { 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; } } } $this->prepareResult($map, $result); return $result; }
public static function getRegisteredCodes($entityTypeID, $entityID, $enablePermissionCheck = false, $userID = 0, $limit = 50) { if (!is_int($entityTypeID)) { throw new Main\ArgumentTypeException('entityTypeID', 'integer'); } if (!is_int($entityID)) { throw new Main\ArgumentTypeException('entityID', 'integer'); } if (!is_int($userID)) { throw new Main\ArgumentTypeException('userID', 'integer'); } if (!is_bool($enablePermissionCheck)) { throw new Main\ArgumentTypeException('enablePermissionCheck', 'boolean'); } if (!is_int($limit)) { throw new Main\ArgumentTypeException('limit', 'integer'); } $query = new Main\Entity\Query(DuplicateCommunicationMatchCodeTable::getEntity()); $query->addSelect('TYPE'); $query->addSelect('VALUE'); $query->addFilter('=ENTITY_TYPE_ID', $entityTypeID); $query->addFilter('=ENTITY_ID', $entityID); if ($enablePermissionCheck && $userID > 0) { $permissions = isset($params['PERMISSIONS']) ? $params['PERMISSIONS'] : null; if ($permissions === null) { $permissions = \CCrmPerms::GetUserPermissions($userID); } $permissionSql = \CCrmPerms::BuildSql(\CCrmOwnerType::ResolveName($entityTypeID), '', 'READ', array('RAW_QUERY' => true, 'PERMS' => $permissions)); if ($permissionSql === false) { //Access denied; return array(); } elseif ($permissionSql !== '') { $query->addFilter('@ENTITY_ID', new Main\DB\SqlExpression($permissionSql)); } } if ($limit > 0) { $query->setLimit($limit); } $dbResult = $query->exec(); $results = array(); while ($fields = $dbResult->fetch()) { $type = isset($fields['TYPE']) ? $fields['TYPE'] : ''; $value = isset($fields['VALUE']) ? $fields['VALUE'] : ''; if (!isset($results[$type])) { $results[$type] = array(); } $results[$type][] = $value; } return $results; }
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; }
public static function synchronize($ownerID, array $entityFields = null) { 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(DealStageHistoryTable::getEntity()); $query->addSelect('START_DATE'); $query->addSelect('END_DATE'); $query->addSelect('RESPONSIBLE_ID'); $query->addFilter('=OWNER_ID', $ownerID); $query->setLimit(1); $dbResult = $query->exec(); $first = $dbResult->fetch(); if (!is_array($first)) { return false; } if (!is_array($entityFields)) { $dbResult = \CCrmDeal::GetListEx(array(), array('=ID' => $ownerID, 'CHECK_PERMISSIONS' => 'N'), false, false, array('ASSIGNED_BY_ID', 'BEGINDATE', 'CLOSEDATE')); $entityFields = is_object($dbResult) ? $dbResult->Fetch() : null; if (!is_array($entityFields)) { return false; } } $responsibleID = isset($entityFields['ASSIGNED_BY_ID']) ? (int) $entityFields['ASSIGNED_BY_ID'] : 0; $beginDate = isset($entityFields['BEGINDATE']) ? $entityFields['BEGINDATE'] : ''; /** @var Date $startDate */ $startDate = new Date($beginDate); $closeDate = isset($entityFields['CLOSEDATE']) ? $entityFields['CLOSEDATE'] : ''; /** @var Date $endDate */ $endDate = $closeDate !== '' ? new Date($closeDate) : new Date('9999-12-31', 'Y-m-d'); if ($startDate->getTimestamp() === $first['START_DATE']->getTimestamp() && $endDate->getTimestamp() === $first['END_DATE']->getTimestamp() && $responsibleID === (int) $first['RESPONSIBLE_ID']) { return false; } DealStageHistoryTable::synchronize($ownerID, array('START_DATE' => $startDate, 'END_DATE' => $endDate, 'RESPONSIBLE_ID' => $responsibleID)); return true; }
private function getListUsers(array $filter) { $limit = !isset($this->params['limit']) ? self::USERS_ON_PAGE : (int) $this->params['limit']; $page = empty($this->params['page']) ? 1 : (int) $this->params['page']; $offset = ($page - 1) * $limit; $query = \Bitrix\Webdav\FolderInviteTable::getList(array('select' => array('*', 'INVITE_USER'), 'filter' => $filter, 'limit' => $limit, 'offset' => $offset, 'order' => array('ID' => 'ASC'))); $users = array(); while ($row = $query->fetch()) { $row = $this->reformatInviteRow($row); $users[] = $row; } $countQuery = new Query(\Bitrix\Webdav\FolderInviteTable::getEntity()); $countQuery->addSelect(new ExpressionField('CNT', 'COUNT(1)')); $countQuery->setFilter($filter); $totalCount = $countQuery->setLimit(null)->setOffset(null)->exec()->fetch(); $totalCount = $totalCount['CNT']; return array('USERS' => $users, 'COUNT' => count($users), 'PAGE' => $page, 'ON_PAGE' => self::USERS_ON_PAGE, 'TOTAL_COUNT' => intval($totalCount), 'TOTAL_PAGE' => ceil($totalCount / $limit)); }
} if ($selectFields['PROPERTY_TYPE']) { $selectFields['USER_TYPE'] = true; } $selectFields = array_keys($selectFields); $getListParams = array('select' => $selectFields, 'filter' => $arFilter, 'order' => $propertyOrder); if ($usePageNavigation) { $getListParams['limit'] = $navyParams['SIZEN']; $getListParams['offset'] = $navyParams['SIZEN'] * ($navyParams['PAGEN'] - 1); } $totalPages = 0; if ($usePageNavigation) { $countQuery = new Main\Entity\Query(Iblock\PropertyTable::getEntity()); $countQuery->addSelect(new Main\Entity\ExpressionField('CNT', 'COUNT(1)')); $countQuery->setFilter($getListParams['filter']); $totalCount = $countQuery->setLimit(null)->setOffset(null)->exec()->fetch(); unset($countQuery); $totalCount = (int) $totalCount['CNT']; if ($totalCount > 0) { $totalPages = ceil($totalCount / $navyParams['SIZEN']); if ($navyParams['PAGEN'] > $totalPages) { $navyParams['PAGEN'] = $totalPages; } $getListParams['limit'] = $navyParams['SIZEN']; $getListParams['offset'] = $navyParams['SIZEN'] * ($navyParams['PAGEN'] - 1); } else { $navyParams['PAGEN'] = 1; $getListParams['limit'] = $navyParams['SIZEN']; $getListParams['offset'] = 0; } }
public static function loadEntityMatches($entityTypeID, $entityID) { $query = new Main\Entity\Query(DuplicatePersonMatchCodeTable::getEntity()); $query->addFilter('=ENTITY_TYPE_ID', $entityTypeID); $query->addFilter('=ENTITY_ID', $entityID); $query->addSelect('LAST_NAME'); $query->addSelect('NAME'); $query->addSelect('SECOND_NAME'); $query->setLimit(1); $dbResult = $query->exec(); $fields = $dbResult->fetch(); return is_array($fields) ? $fields : null; }
/** * @return Query */ protected static function prepareInvoiceQuery($startDate, $endDate, $ownerFieldReference = '%s', $postfix = '') { $query = new Query(DealInvoiceStatisticsTable::getEntity()); if ($postfix !== '') { $query->setTableAliasPostfix($postfix); } $query->addFilter('=IS_LOST', false); $query->addFilter('>=CREATED_DATE', $startDate); $query->addFilter('<=CREATED_DATE', $endDate); $query->addFilter('=OWNER_ID', new SqlExpression($ownerFieldReference)); if (!Main\Application::getConnection() instanceof Main\DB\OracleConnection) { $query->setLimit(1); } return $query; }
public static function getAllCounter() { $countQuery = new Main\Entity\Query(Sale\Internals\DiscountTable::getEntity()); $countQuery->addSelect(new Main\Entity\ExpressionField('CNT', 'COUNT(1)')); $countQuery->setFilter(array('=VERSION' => Sale\Internals\DiscountTable::VERSION_NEW)); $totalCount = $countQuery->setLimit(null)->setOffset(null)->exec()->fetch(); return (int) $totalCount['CNT']; }