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