private static function loadBulk($entityTypeID, array &$entityIDs, array &$itemMap, array $options = null) { /** @var DuplicateEntityRanking[] $itemMap */ if ($entityTypeID !== \CCrmOwnerType::Contact && $entityTypeID !== \CCrmOwnerType::Company && $entityTypeID !== \CCrmOwnerType::Lead) { return; } if (!is_array($options)) { $options = array(); } $checkPermissions = isset($options['CHECK_PERMISSIONS']) ? (bool) $options['CHECK_PERMISSIONS'] : false; $userID = isset($options['USER_ID']) ? (int) $options['USER_ID'] : 0; $permissions = $checkPermissions ? \CCrmPerms::GetUserPermissions($userID) : null; $limit = isset($options['LIMIT']) ? (int) $options['LIMIT'] : 3000; if ($limit <= 0) { $limit = 3000; } $length = count($entityIDs); if ($length === 0) { return; } while ($length > 0) { if ($length <= $limit) { $ids = $entityIDs; unset($entityIDs); $entityIDs = array(); } else { $ids = array_splice($entityIDs, 0, $limit); } $length = count($entityIDs); if (empty($ids)) { continue; } if ($entityTypeID === \CCrmOwnerType::Lead) { $dbResult = Entity\DuplicateEntityStatisticsTable::getList(array('select' => array('ENTITY_ID', 'RANKING_DATA'), 'filter' => array('ENTITY_TYPE_ID' => \CCrmOwnerType::Lead, 'ENTITY_ID' => $ids))); while ($fields = $dbResult->fetch()) { $entityID = intval($fields['ENTITY_ID']); $key = "{$entityTypeID}_{$entityID}"; if (!isset($itemMap[$key])) { continue; } if (isset($fields['RANKING_DATA']) && $fields['RANKING_DATA'] !== '') { $data = unserialize($fields['RANKING_DATA']); /** @var DuplicateEntityRanking $ranking */ $ranking = $itemMap[$key]; $ranking->lastChanged = isset($data['LAST_CHANGED']) ? $data['LAST_CHANGED'] : 0; $ranking->completeness = isset($data['COMPLETENESS']) ? $data['COMPLETENESS'] : 0; if ($checkPermissions) { $ranking->editable = \CCrmLead::CheckUpdatePermission($entityID, $permissions); $ranking->deleteable = \CCrmLead::CheckDeletePermission($entityID, $permissions); } } } } else { $query = new Main\Entity\Query(Entity\DuplicateEntityStatisticsTable::getEntity()); $query->addSelect('ENTITY_ID'); $query->addSelect('RANKING_DATA'); $query->addFilter('ENTITY_ID', $ids); $query->addFilter('ENTITY_TYPE_ID', $entityTypeID); if ($entityTypeID === \CCrmOwnerType::Contact) { $subQuery = new Main\Entity\Query(DealTable::getEntity()); $subQuery->addSelect('CONTACT_ID'); $subQuery->addFilter('CONTACT_ID', $ids); $subQuery->addSelect('QTY'); $subQuery->registerRuntimeField('', new Main\Entity\ExpressionField('QTY', 'COUNT(*)')); $referenceField = new Main\Entity\ReferenceField('D', Main\Entity\Base::getInstanceByQuery($subQuery), array('=this.ENTITY_ID' => 'ref.CONTACT_ID'), array('join_type' => 'LEFT')); } else { $subQuery = new Main\Entity\Query(DealTable::getEntity()); $subQuery->addSelect('COMPANY_ID'); $subQuery->addFilter('COMPANY_ID', $ids); $subQuery->addSelect('QTY'); $subQuery->registerRuntimeField('', new Main\Entity\ExpressionField('QTY', 'COUNT(*)')); $referenceField = new Main\Entity\ReferenceField('D', Main\Entity\Base::getInstanceByQuery($subQuery), array('=this.ENTITY_ID' => 'ref.COMPANY_ID'), array('join_type' => 'LEFT')); } $query->registerRuntimeField('', $referenceField); $query->addSelect('D.QTY', 'QTY'); $dbResult = $query->exec(); while ($fields = $dbResult->fetch()) { $entityID = intval($fields['ENTITY_ID']); $key = "{$entityTypeID}_{$entityID}"; if (!isset($itemMap[$key])) { continue; } $itemMap[$key]->referenceCount = isset($fields['QTY']) ? intval($fields['QTY']) : 0; if (isset($fields['RANKING_DATA']) && $fields['RANKING_DATA'] !== '') { $data = unserialize($fields['RANKING_DATA']); /** @var DuplicateEntityRanking $ranking */ $ranking = $itemMap[$key]; $ranking->lastChanged = isset($data['LAST_CHANGED']) ? $data['LAST_CHANGED'] : 0; $ranking->completeness = isset($data['COMPLETENESS']) ? $data['COMPLETENESS'] : 0; if ($checkPermissions) { if ($entityTypeID === \CCrmOwnerType::Contact) { $ranking->editable = \CCrmContact::CheckUpdatePermission($entityID, $permissions); $ranking->deleteable = \CCrmContact::CheckDeletePermission($entityID, $permissions); } else { $ranking->editable = \CCrmCompany::CheckUpdatePermission($entityID, $permissions); $ranking->deleteable = \CCrmCompany::CheckDeletePermission($entityID, $permissions); } } } } } } }
/** @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."); } $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(DealStageHistoryTable::getEntity()); $query->addSelect('STAGE_ID'); $query->addSelect('QTY'); $query->registerRuntimeField('', new ExpressionField('QTY', 'COUNT(DISTINCT OWNER_ID)')); $typeID = $filter->getExtraParam('typeID', HistoryEntryType::UNDEFINED); if ($typeID !== HistoryEntryType::UNDEFINED) { $query->addFilter('=TYPE_ID', $typeID); if ($typeID === HistoryEntryType::CREATION) { $query->addFilter('>=START_DATE', $periodStartDate); $query->addFilter('<=START_DATE', $periodEndDate); } elseif ($typeID === HistoryEntryType::MODIFICATION) { $query->addFilter('>=CREATED_TIME', $periodStartDate); $query->addFilter('<=CREATED_TIME', $periodEndDate); } elseif ($typeID === HistoryEntryType::FINALIZATION) { $query->addFilter('>=END_DATE', $periodStartDate); $query->addFilter('<=END_DATE', $periodEndDate); } } if ($this->enablePermissionCheck && is_string($permissionSql) && $permissionSql !== '') { $query->addFilter('@OWNER_ID', new SqlExpression($permissionSql)); } $responsibleIDs = $filter->getResponsibleIDs(); if (!empty($responsibleIDs)) { $query->addFilter('@RESPONSIBLE_ID', $responsibleIDs); } $query->addGroup('STAGE_ID'); $dbResult = $query->exec(); //Trace('sql', Query::getLastQuery(), 1); $result = array(); while ($ary = $dbResult->fetch()) { $result[] = $ary; } return $result; }
public function calculateEntityCount(DuplicateCriterion $criterion, array $options = null) { $entityTypeID = $this->getEntityTypeID(); $enablePermissionCheck = $this->isPermissionCheckEnabled(); $userID = $this->getUserID(); $query = new Main\Entity\Query(DuplicateCommunicationMatchCodeTable::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(); $type = isset($matches['TYPE']) ? $matches['TYPE'] : ''; if ($type === '') { throw new Main\ArgumentException("Parameter 'TYPE' is required.", 'matches'); } $value = isset($matches['VALUE']) ? $matches['VALUE'] : ''; if ($type === '') { throw new Main\ArgumentException("Parameter 'VALUE' is required.", 'matches'); } $query->addFilter('=TYPE', $type); $query->addFilter('=VALUE', $value); $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 static function getLatest($ownerID) { if (!is_int($ownerID)) { $ownerID = (int) $ownerID; } if ($ownerID <= 0) { throw new Main\ArgumentException('Owner ID must be greater than zero.', 'ownerID'); } $subQuery = new Query(DealStageHistoryTable::getEntity()); $subQuery->registerRuntimeField('', new ExpressionField('MAX_ID', 'MAX(ID)')); $subQuery->addSelect('MAX_ID'); $subQuery->addFilter('=OWNER_ID', $ownerID); $query = new Query(DealStageHistoryTable::getEntity()); $query->addSelect('*'); $query->registerRuntimeField('', new ReferenceField('M', Base::getInstanceByQuery($subQuery), array('=this.ID' => 'ref.MAX_ID'), array('join_type' => 'INNER'))); $dbResult = $query->exec(); $result = $dbResult->fetch(); return is_array($result) ? $result : null; }
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 array */ public static function prepareTimeline($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(Crm\ActivityTable::getEntity()); $query->addFilter('=COMPLETED', 'Y'); $connection = Main\Application::getConnection(); if ($connection instanceof Main\DB\MysqlCommonConnection) { $query->registerRuntimeField('', new ExpressionField('DEADLINE_DATE', 'DATE(DEADLINE)')); } elseif ($connection instanceof Main\DB\MssqlConnection) { $query->registerRuntimeField('', new ExpressionField('DEADLINE_DATE', 'CAST(FLOOR(CAST(DEADLINE AS FLOAT)) AS DATETIME)')); } elseif ($connection instanceof Main\DB\OracleConnection) { $query->registerRuntimeField('', new ExpressionField('DEADLINE_DATE', 'TRUNC(DEADLINE)')); } $query->addSelect('DEADLINE_DATE'); $query->addGroup('DEADLINE_DATE'); $subQuery = new Query(Crm\ActivityBindingTable::getEntity()); $subQuery->addSelect('ACTIVITY_ID'); $subQuery->addFilter('=OWNER_TYPE_ID', \CCrmOwnerType::Deal); $subQuery->addFilter('=OWNER_ID', $ownerID); $query->registerRuntimeField('', new ReferenceField('B', Base::getInstanceByQuery($subQuery), array('=this.ID' => 'ref.ACTIVITY_ID'), array('join_type' => 'INNER'))); $dbResult = $query->exec(); $dates = array(); while ($fieilds = $dbResult->fetch()) { $dates[] = $fieilds['DEADLINE_DATE']; } return $dates; }
$grc_field = $grcChain->getLastElement()->getValue(); if (is_array($grc_field)) { $grc_field = end($grc_field); } $grc_primary = end($grc_field->getEntity()->getPrimaryArray()); $grc_marker = substr($elem['name'], 0, strrpos($elem['name'], '.')) . '.' . $grc_primary; $grc_marker_alias = Entity\QueryChain::getAliasByDefinition($entity, $grc_marker); $grcSelect[$grc_marker_alias] = $grc_marker; // select $resultName = $viewColumns[$num]['resultName']; $grcData[$resultName] = array(); $grc_query = new Entity\Query($entity); $grc_query->setSelect($grcSelect); $grc_query->setFilter($grcFilter); foreach ($runtime as $k => $v) { $grc_query->registerRuntimeField($k, $v); } $result = $grc_query->exec(); while ($row = $result->fetch()) { if (empty($row[$grc_marker_alias])) { continue; } $grcData[$resultName][] = $row; } // add empty values to data foreach ($data as $k => $v) { $data[$k][$alias] = null; } // add values to data foreach ($grcData[$resultName] as $grcIndex => &$row) { $grc_primary_string = '';
use Citfact\FilterSubscribe\Model\SubscribeUserTable; Loc::loadMessages(__FILE__); Loader::includeModule('citfact.filter.subscribe'); $app = Application::getInstance(); $request = $app->getContext()->getRequest(); if (!$GLOBALS['USER']->IsAuthorized()) { return; } $isAjax = getenv('HTTP_X_REQUESTED_WITH') == 'XMLHttpRequest'; $subscribeManager = new \Citfact\FilterSubscribe\SubscribeManager(); // Check whether there is a user filter if ($request->getQuery('CHECK_FILTER') && $isAjax) { $filterData = $GLOBALS[$arParams['FILTER_NAME']]; $filterData = $subscribeManager->normalizeFilter($filterData); $queryBuilder = new Entity\Query(SubscribeUserTable::getEntity()); $filter = $queryBuilder->registerRuntimeField('filter', array('data_type' => 'Citfact\\FilterSubscribe\\Model\\SubscribeTable', 'reference' => array('=this.FILTER_ID' => 'ref.ID')))->setSelect(array('*', 'filter'))->setFilter(array('USER_ID' => $GLOBALS['USER']->GetId(), 'filter.FILTER' => $filterData))->setLimit(1)->exec()->fetch(); $GLOBALS['APPLICATION']->RestartBuffer(); header('Content-Type: application/json'); exit(json_encode(array('data' => $filter))); } // Saves the current filter, // If such a filter not already exists if ($request->getQuery('SAVE_FILTER') && $isAjax) { $filterData = $GLOBALS[$arParams['FILTER_NAME']]; $filterResult = $subscribeManager->addFilter(array('FILTER' => $filterData, 'IBLOCK_ID' => $arParams['IBLOCK_ID'], 'SECTION_ID' => $arParams['SECTION_ID'])); $filterUserResult = $subscribeManager->addFilterUser(array('USER_ID' => $GLOBALS['USER']->GetId(), 'FILTER_ID' => $filterResult->getId())); $GLOBALS['APPLICATION']->RestartBuffer(); header('Content-Type: application/json'); exit(json_encode(array('success' => $filterResult->isSuccess() && $filterUserResult->isSuccess()))); } $this->IncludeComponentTemplate();
/** @return array */ public function prepareEntityListFilter(array $filterParams) { $filter = self::internalizeFilter($filterParams); $query = new Query(DealStageHistoryTable::getEntity()); $query->addSelect('OWNER_ID'); $query->addGroup('OWNER_ID'); $period = $filter->getPeriod(); $periodStartDate = $period['START']; $periodEndDate = $period['END']; $query->addFilter('=TYPE_ID', HistoryEntryType::CREATION); $query->addFilter('>=START_DATE', $periodStartDate); $query->addFilter('<=START_DATE', $periodEndDate); $query->registerRuntimeField('', new ExpressionField('E1', '(CASE WHEN NOT EXISTS(' . self::prepareHistoryQuery($periodStartDate, $periodEndDate, HistoryEntryType::MODIFICATION, '%s', '_i')->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', '_i')->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')->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')->getQuery() . ') THEN 1 ELSE 0 END)', 'OWNER_ID')); $query->addFilter('=E4', 1); $responsibleIDs = $filter->getResponsibleIDs(); if (!empty($responsibleIDs)) { $query->addFilter('@RESPONSIBLE_ID', $responsibleIDs); } return array('__JOINS' => array(array('TYPE' => 'INNER', 'SQL' => 'INNER JOIN(' . $query->getQuery() . ') DS ON DS.OWNER_ID = L.ID'))); }
die; } use Bitrix\Main\Application; use Bitrix\Main\Localization\Loc; use Bitrix\Main\Entity; use Bitrix\Main\Loader; use Bitrix\IBlock; use Citfact\FilterSubscribe\Model\SubscribeUserTable; Loc::loadMessages(__FILE__); Loader::includeModule('citfact.filter.subscribe'); $app = Application::getInstance(); $request = $app->getContext()->getRequest(); $arResult['COMPONENT_ID'] = CAjax::GetComponentID($this->getName(), $this->getTemplateName(), array()); $userId = $arParams['USER_ID']; $queryBuilder = new Entity\Query(SubscribeUserTable::getEntity()); $queryBuilder->registerRuntimeField('filter', array('data_type' => 'Citfact\\FilterSubscribe\\Model\\SubscribeTable', 'reference' => array('=this.FILTER_ID' => 'ref.ID')))->setSelect(array('*', 'filter'))->setOrder(array('ID' => 'DESC'))->setFilter(array('USER_ID' => $userId)); $filterResult = $queryBuilder->exec(); while ($filter = $filterResult->fetch()) { $arResult['ITEMS'][] = $filter; $arResult['IBLOCKS_ID'][] = $filter['IBLOCK_ID']; if ($filter['SECTION_ID'] > 0) { $arResult['SECTION_ID'][] = $filter['SECTION_ID']; } } if (array_key_exists('ITEMS', $arResult)) { $filterLexer = new \Citfact\FilterSubscribe\FilterLexer(); foreach ($arResult['ITEMS'] as $key => $filter) { $arResult['ITEMS'][$key]['FILTER_LINK'] = $filterLexer->getFilterUniqId(unserialize($filter['FILTER'])); $filterLexer->addFilter($filter['FILTER'], true); } $filterLexer->parse();
public static function getConnectedEntitiesQuery($locationPrimary, $linkType = 'id', $parameters = array()) { if ($linkType == 'id') { $locationPrimary = Assert::expectIntegerPositive($locationPrimary, Loc::getMessage('SALE_LOCATION_CONNECTOR_ENTITY_LOCATION_PRIMARY_FLD_NAME')); } else { $locationPrimary = Assert::expectStringNotNull($locationPrimary, Loc::getMessage('SALE_LOCATION_CONNECTOR_ENTITY_LOCATION_PRIMARY_FLD_NAME')); } $useGroups = GroupTable::checkGroupUsage() && static::getUseGroups(); // check if we have groups in project and entity uses groups $useCodes = static::getUseCodes(); // this entity uses codes $groupUseCodes = GroupLocationTable::getUseCodes(); // group entity uses codes $typeFld = static::getTypeField(); /*LOCATION_TYPE*/ $linkFld = static::getLinkField(); /*DELIVERY_ID*/ $locationLinkFld = static::getLocationLinkField(); /*LOCATION_ID*/ $targetPrimaryFld = static::getTargetEntityPrimaryField(); /*ID*/ $groupLocationLinkFld = GroupLocationTable::getLocationLinkField(); /*LOCATION_ID*/ $groupLinkFld = GroupLocationTable::getLinkField(); /*LOCATION_GROUP_ID*/ $seachById = $linkType == 'id'; $dbConnection = Main\HttpApplication::getConnection(); if (!is_array($parameters)) { $parameters = array(); } if (is_array($parameters['runtime'])) { Assert::announceNotImplemented('Sorry, runtime clause is not implemented currently.'); } $order = array(); if (is_array($parameters['order'])) { Assert::announceNotImplemented('Sorry, order-over-union clause is not implemented currently.'); } $filter = array(); if (is_array($parameters['filter']) && !empty($parameters['filter'])) { $filter = $parameters['filter']; } $select = array('*'); if (is_array($parameters['select']) && !empty($parameters['select'])) { $select = $parameters['select']; } /* query example when working with delivery: select distinct D.* from b_sale_delivery D inner join b_sale_delivery2location DL on D.ID = DL.DELIVERY_ID and DL.LOCATION_TYPE = 'L' inner join b_sale_location L1 on L1.CODE = DL.LOCATION_ID inner join b_sale_location L2 on L2.ID(there will be CODE, if we search by code) = 65683 and L2.LEFT_MARGIN >= L1.LEFT_MARGIN and L2.RIGHT_MARGIN <= L1.RIGHT_MARGIN; */ $query = new Entity\Query(static::getTargetEntityName()); $DLCondition = array('=this.' . $targetPrimaryFld => 'ref.' . $linkFld); if ($useGroups) { $DLCondition['=ref.' . $typeFld] = array('?', self::DB_LOCATION_FLAG); } $query->registerRuntimeField('DL', array('data_type' => get_called_class(), 'reference' => $DLCondition, 'join_type' => 'inner'))->registerRuntimeField('L1', array('data_type' => '\\Bitrix\\Sale\\Location\\Location', 'reference' => array('=this.DL.' . $locationLinkFld => 'ref.' . ($useCodes ? 'CODE' : 'ID')), 'join_type' => 'inner'))->registerRuntimeField('L2', array('data_type' => '\\Bitrix\\Sale\\Location\\Location', 'reference' => array('=ref.' . ($seachById ? 'ID' : 'CODE') => array('?', $locationPrimary), '>=ref.LEFT_MARGIN' => 'this.L1.LEFT_MARGIN', '<=ref.RIGHT_MARGIN' => 'this.L1.RIGHT_MARGIN'), 'join_type' => 'inner'))->setSelect($select)->setFilter($filter)->setOrder($order); if (!$useGroups) { // emulate "select distinct" $query->setGroup($select); return $query->getQuery(); } else { $sqls = array($query->getQuery()); $query = new Entity\Query(static::getTargetEntityName()); /* query example when working with delivery: select D.* from b_sale_delivery D inner join b_sale_delivery2location DL on D.ID = DL.DELIVERY_ID and DL.LOCATION_TYPE = 'G' inner join b_sale_location_group G on G.CODE = DL.LOCATION_ID (if this entity uses ID, skip this join) inner join b_sale_grouplocation GL on GL.LOCATION_GROUP_ID = G.ID (if this entity uses ID, there will be DL.LOCATION_ID) inner join b_sale_location L1 on L1.ID (there will be CODE, if grouplocation entity uses CODE) = GL.LOCATION_ID inner join b_sale_location L2 on L2.ID (there will be CODE, if we seach by code) = 65683 and L2.LEFT_MARGIN >= L1.LEFT_MARGIN and L2.RIGHT_MARGIN <= L1.RIGHT_MARGIN; */ $query->registerRuntimeField('DL', array('data_type' => get_called_class(), 'reference' => array('=this.' . $targetPrimaryFld => 'ref.' . $linkFld, '=ref.' . $typeFld => array('?', self::DB_GROUP_FLAG)), 'join_type' => 'inner')); if ($useCodes) { $query->registerRuntimeField('G', array('data_type' => '\\Bitrix\\Sale\\Location\\Group', 'reference' => array('=this.DL.' . $locationLinkFld => 'ref.CODE'), 'join_type' => 'inner')); } $query->registerRuntimeField('GL', array('data_type' => '\\Bitrix\\Sale\\Location\\GroupLocation', 'reference' => array($useCodes ? '=this.G.ID' : '=this.DL.' . $locationLinkFld => 'ref.' . $groupLinkFld), 'join_type' => 'inner'))->registerRuntimeField('L1', array('data_type' => '\\Bitrix\\Sale\\Location\\Location', 'reference' => array('=this.GL.' . $groupLocationLinkFld => 'ref.' . ($groupUseCodes ? 'CODE' : 'ID')), 'join_type' => 'inner'))->registerRuntimeField('L2', array('data_type' => '\\Bitrix\\Sale\\Location\\Location', 'reference' => array('=ref.' . ($seachById ? 'ID' : 'CODE') => array('?', $locationPrimary), '>=ref.LEFT_MARGIN' => 'this.L1.LEFT_MARGIN', '<=ref.RIGHT_MARGIN' => 'this.L1.RIGHT_MARGIN'), 'join_type' => 'inner'))->setSelect($select)->setFilter($filter)->setOrder($order); $sqls[] = $query->getQuery(); return static::unionize($sqls); } }
/** * @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; }
/** @return array */ public function prepareEntityListFilter(array $filterParams) { $filter = self::internalizeFilter($filterParams); $query = new Query(DealActivityStatisticsTable::getEntity()); $query->addSelect('OWNER_ID'); $query->addGroup('OWNER_ID'); $period = $filter->getPeriod(); $periodStartDate = $period['START']; $periodEndDate = $period['END']; $query->addFilter('>=DEADLINE_DATE', $periodStartDate); $query->addFilter('<=DEADLINE_DATE', $periodEndDate); $responsibleIDs = $filter->getResponsibleIDs(); if (!empty($responsibleIDs)) { $query->addFilter('@RESPONSIBLE_ID', $responsibleIDs); } $semanticID = $filter->getExtraParam('semanticID', PhaseSemantics::UNDEFINED); if ($semanticID !== PhaseSemantics::UNDEFINED) { $query->addFilter('=STAGE_SEMANTIC_ID', $semanticID); } $field = isset($filterParams['FIELD']) ? $filterParams['FIELD'] : ''; if ($field === 'CALL_QTY' || $field === 'MEETING_QTY' || $field === 'EMAIL_QTY') { $query->addFilter(">{$field}", 0); } elseif ($field === 'TOTAL') { $query->registerRuntimeField(null, new ExpressionField('TOTAL', '(%s + %s + %s)', array('CALL_QTY', 'MEETING_QTY', 'EMAIL_QTY'))); $query->addFilter('>TOTAL', 0); } return array('__JOINS' => array(array('TYPE' => 'INNER', 'SQL' => 'INNER JOIN(' . $query->getQuery() . ') DS ON DS.OWNER_ID = L.ID'))); }
private static function setAttributeFilter(Query $query, $field, $name, $value = null) { $query->registerRuntimeField(null, new ReferenceField($field, Internals\ContextAttributeTable::getEntity(), array('=this.CONTEXT_ID' => 'ref.CONTEXT_ID'), array('join_type' => 'INNER'))); $query->addFilter("={$field}.NAME", $name); if ($value !== null) { $query->addFilter("={$field}.VALUE", $value); } }
public static function prepareSortParams($entityTypeID, array &$entityIDs, $type = '') { if (empty($entityIDs)) { return array(); } if (!is_string($type)) { $type = ''; } $query = new Main\Entity\Query(DuplicateCommunicationMatchCodeTable::getEntity()); $query->addSelect('ENTITY_ID'); $query->addSelect('TYPE'); $query->addSelect('VALUE'); $subQuery = new Main\Entity\Query(DuplicateCommunicationMatchCodeTable::getEntity()); $subQuery->registerRuntimeField('', new Main\Entity\ExpressionField('MIN_ID', 'MIN(ID)')); $subQuery->addSelect('MIN_ID'); $subQuery->addFilter('=ENTITY_TYPE_ID', $entityTypeID); $subQuery->addFilter('@ENTITY_ID', $entityIDs); if ($type !== '') { $subQuery->addFilter('=TYPE', $type); } $subQuery->addGroup('ENTITY_ID'); $subQuery->addGroup('TYPE'); $query->registerRuntimeField('', new Main\Entity\ReferenceField('M', Main\Entity\Base::getInstanceByQuery($subQuery), array('=this.ID' => 'ref.MIN_ID'), array('join_type' => 'INNER'))); $result = array(); $dbResult = $query->exec(); while ($fields = $dbResult->fetch()) { $entityID = intval($fields['ENTITY_ID']); if (!isset($result[$entityID])) { $result[$entityID] = array(); } $type = isset($fields['TYPE']) ? $fields['TYPE'] : ''; $value = isset($fields['VALUE']) ? $fields['VALUE'] : ''; $result[$entityID][$type] = $value; } return $result; }
/** * <p>Метод возвращает набор местоположений, связанных с группами местоположений, удовлетворяющих фильтру arFilter. Метод динамичный.</p> * * * @param array $arrayarFilter = Array() Фильтр представляет собой ассоциативный массив, в котором * ключами являются названия параметров записи, а значениями - * условия на значения<br><br> Допустимые ключи: <ul> <li> <b>LOCATION_ID</b> - код * местоположения;</li> <li> <b>LOCATION_GROUP_ID</b> - код группы * местоположений.</li> </ul> * * @return CDBResult <p>Возвращается объект класса CDBResult, содержащий ассоциативные * массивы с ключами:</p> <table class="tnormal" width="100%"> <tr> <th width="15%">Ключ</th> * <th>Описание</th> </tr> <tr> <td>LOCATION_ID</td> <td>Код местоположения.</td> </tr> <tr> * <td>LOCATION_GROUP_ID</td> <td>Код группы местоположений.</td> </tr> </table> <a * name="examples"></a> * * <h4>Example</h4> * <pre> * <? * // Выберем все местоположения группы 2 * $db_res = CSaleLocationGroup::GetLocationList(array("LOCATION_GROUP_ID"=>2)); * while ($ar_res = $db_res->Fetch()) * { * echo $ar_res["LOCATION_ID"].", "; * } * ?> * </pre> * * * @static * @link http://dev.1c-bitrix.ru/api_help/sale/classes/csalelocationgroup/csalelocationgroup__getlocationlist.56a02620.php * @author Bitrix */ public static function GetLocationList($arFilter = array()) { if (CSaleLocation::isLocationProMigrated()) { try { $query = new Entity\Query(self::CONN_ENTITY_NAME); $fieldMap = array('D_SPIKE' => 'D_SPIKE', 'LLOCATION_ID' => 'C.ID', 'LOCATION_GROUP_ID' => 'LOCATION_GROUP_ID'); $fieldProxy = array('LLOCATION_ID' => 'LOCATION_ID'); $query->registerRuntimeField('D_SPIKE', array('data_type' => 'integer', 'expression' => array('distinct %s', 'LOCATION_GROUP_ID'))); $query->registerRuntimeField('L', array('data_type' => self::LOCATION_ENTITY_NAME, 'reference' => array('=this.LOCATION_ID' => 'ref.ID'), 'join_type' => 'inner')); $query->registerRuntimeField('C', array('data_type' => self::LOCATION_ENTITY_NAME, 'reference' => array('LOGIC' => 'OR', array('>=ref.LEFT_MARGIN' => 'this.L.LEFT_MARGIN', '<=ref.RIGHT_MARGIN' => 'this.L.RIGHT_MARGIN'), array('=ref.ID' => 'this.L.ID')), 'join_type' => 'inner')); // select $selectFields = CSaleLocation::processSelectForGetList(array('*'), $fieldMap); // filter list($filterFields, $filterClean) = CSaleLocation::processFilterForGetList($arFilter, $fieldMap, $fieldProxy); $query->setSelect($selectFields); $query->setFilter($filterFields); $res = $query->exec(); $res->addReplacedAliases(array('LLOCATION_ID' => 'LOCATION_ID')); return $res; } catch (Exception $e) { return new DB\ArrayResult(array()); } } else { global $DB; $arSqlSearch = array(); if (!is_array($arFilter)) { $filter_keys = array(); } else { $filter_keys = array_keys($arFilter); } $countFieldKey = count($filter_keys); for ($i = 0; $i < $countFieldKey; $i++) { $val = $DB->ForSql($arFilter[$filter_keys[$i]]); if (strlen($val) <= 0) { continue; } $key = $filter_keys[$i]; if ($key[0] == "!") { $key = substr($key, 1); $bInvert = true; } else { $bInvert = false; } switch (ToUpper($key)) { case "LOCATION_ID": $arSqlSearch[] = "LOCATION_ID " . ($bInvert ? "<>" : "=") . " " . IntVal($val) . " "; break; case "LOCATION_GROUP_ID": $arSqlSearch[] = "LOCATION_GROUP_ID " . ($bInvert ? "<>" : "=") . " " . IntVal($val) . " "; break; } } $strSqlSearch = ""; $countSqlSearch = count($arSqlSearch); for ($i = 0; $i < $countSqlSearch; $i++) { $strSqlSearch .= " AND "; $strSqlSearch .= " (" . $arSqlSearch[$i] . ") "; } $strSql = "SELECT LOCATION_ID, LOCATION_GROUP_ID " . "FROM b_sale_location2location_group " . "WHERE 1 = 1 " . "\t" . $strSqlSearch . " "; $res = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); return $res; } }
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 GetRegionsIdsByNames($arRegNames, $countryId = false) { if (self::isLocationProMigrated()) { try { $types = self::getTypes(); $query = new Entity\Query(self::SELF_ENTITY_NAME); $fieldMap = array('RID' => 'REGION_ID', 'RNAME' => 'NAME.NAME', 'RSHORT_NAME' => 'NAME.SHORT_NAME'); $selectFields = $fieldMap; $filterFields = array(array('LOGIC' => 'OR', 'RNAME' => $arRegNames, 'RSHORT_NAME' => $arRegNames), '=TYPE_ID' => $types['REGION'], '!=REGION_ID' => '0'); if ($countryId = intval($countryId)) { $filterFields['=COUNTRY_ID'] = $countryId; } // order $orderFields = array('RNAME' => 'asc', 'RSHORT_NAME' => 'asc'); // group $groupFields = array('RID'); $nameJoinCondition = array('=this.ID' => 'ref.LOCATION_ID'); if (strlen($strLang)) { $nameJoinCondition['=ref.LANGUAGE_ID'] = array('?', $strLang); } $query->registerRuntimeField('NAME', array('data_type' => self::NAME_ENTITY_NAME, 'reference' => $nameJoinCondition, 'join_type' => 'left')); $query->setSelect($selectFields); $query->setFilter($filterFields); $query->setOrder($orderFields); $query->setGroup($groupFields); $result = array(); $res = $query->exec(); while ($item = $res->fetch()) { $result[strlen($item['RNAME']) ? $item['RNAME'] : $item['RSHORT_NAME']] = $item['RID']; } return $result; } catch (Exception $e) { return array(); } } else { global $DB; $arResult = array(); $arWhere = array(); $arQueryFields = array('RL.NAME', 'RL.SHORT_NAME'); if (is_array($arRegNames)) { foreach ($arRegNames as $regName) { $regName = $DB->ForSql($regName); foreach ($arQueryFields as $field) { $arWhere[] = $field . " LIKE '" . $regName . "'"; } } if (count($arWhere) > 0) { $strWhere = implode(' OR ', $arWhere); $query = "\tSELECT RL.REGION_ID, RL.NAME, RL.SHORT_NAME\n\t\t\t\t\t\t\t\tFROM b_sale_location_region_lang RL "; if ($countryId) { $strWhere = 'L.COUNTRY_ID=\'' . intval($countryId) . '\' AND (' . $strWhere . ')'; $query .= "LEFT JOIN b_sale_location L ON L.REGION_ID=RL.REGION_ID "; } $query .= "WHERE " . $strWhere; $query .= " GROUP BY RL.REGION_ID"; $query .= " ORDER BY RL.NAME, RL.SHORT_NAME"; $dbList = $DB->Query($query); $arRegionsLang = array(); while ($arRegion = $dbList->Fetch()) { if (strlen($arRegion["NAME"]) > 0) { $idx = $arRegion["NAME"]; } else { $idx = $arRegion["SHORT_NAME"]; } $arResult[$idx] = $arRegion["REGION_ID"]; } } } return $arResult; } }
/** @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'); $query->addGroup('CREATED_DATE'); if (!$sort) { $query->addOrder('CREATED_DATE', 'ASC'); } } } } } $dbResult = $query->exec(); //Trace('sql', Query::getLastQuery(), 1); $result = array(); if ($group === self::GROUP_BY_DATE) { while ($ary = $dbResult->fetch()) { $ary['DATE'] = $ary['D']->format('Y-m-d'); unset($ary['D']); if ($ary['DATE'] === '9999-12-31') { //Skip empty dates continue; } $result[] = $ary; } } elseif ($group === self::GROUP_BY_USER) { $userIDs = array(); while ($ary = $dbResult->fetch()) { $userID = $ary['RESPONSIBLE_ID'] = (int) $ary['RESPONSIBLE_ID']; if ($userID > 0 && !isset($userNames[$userID])) { $userIDs[] = $userID; } $result[] = $ary; } $userNames = self::prepareUserNames($userIDs); foreach ($result as &$item) { $userID = $item['RESPONSIBLE_ID']; $item['USER_ID'] = $userID; $item['USER'] = isset($userNames[$userID]) ? $userNames[$userID] : "[{$userID}]"; unset($item['RESPONSIBLE_ID']); } unset($item); } else { while ($ary = $dbResult->fetch()) { $result[] = $ary; } } return $result; }
/** * Remove filter user and relationships * * @param int $id * @return \Bitrix\Main\Entity\DeleteResult * @throws \InvalidArgumentException */ public function removeFilterUser($id) { $filterUser = Model\SubscribeUserTable::getById($id)->fetch(); if ($this->isEmptyResult($filterUser)) { throw new \InvalidArgumentException('Invalid filter user id'); } $queryBuilder = new Entity\Query(Model\SubscribeStackTable::getEntity()); $subscribeStack = $queryBuilder->setSelect(array('ID'))->setFilter(array('FILTER_USER_ID' => $filterUser['ID']))->exec(); while ($row = $subscribeStack->fetch()) { Model\SubscribeStackTable::delete(array('ID' => $row['ID'])); } $result = Model\SubscribeUserTable::delete(array('ID' => $filterUser['ID'])); $queryBuilder = new Entity\Query(Model\SubscribeUserTable::getEntity()); $filterUserResult = $queryBuilder->registerRuntimeField('cnt', array('data_type' => 'integer', 'expression' => array('count(%s)', 'ID')))->setSelect(array('ID', 'cnt'))->setFilter(array('FILTER_ID' => $filterUser['FILTER_ID']))->exec()->fetch(); if ($filterUserResult['cnt'] <= 0) { Model\SubscribeTable::delete(array('ID' => $filterUser['FILTER_ID'])); } return $result; }