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); } } } } } } }
/** * @param Base|Query|string $source * @throws Main\ArgumentException */ public function __construct($source) { if ($source instanceof $this) { $this->init_entity = Base::getInstanceByQuery($source); } elseif ($source instanceof Base) { $this->init_entity = clone $source; } elseif (is_string($source)) { $this->init_entity = clone Base::getInstance($source); } else { throw new Main\ArgumentException(sprintf('Unknown source type "%s" for new %s', gettype($source), __CLASS__)); } }
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; }
/** @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; }
/** * @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; }
public static function beforeViewDataQuery(&$select, &$filter, &$group, &$order, &$limit, &$options, &$runtime) { parent::beforeViewDataQuery($select, $filter, $group, $order, $limit, $options, $runtime); global $USER, $DB, $DBType; $permFilter = array('LOGIC' => 'OR'); // owner permission if (isset($_GET['select_my_tasks']) || !isset($_GET['select_my_tasks']) && !isset($_GET['select_depts_tasks']) && !isset($_GET['select_group_tasks'])) { $runtime['IS_TASK_COWORKER'] = array('data_type' => 'integer', 'expression' => array("(CASE WHEN EXISTS(" . "SELECT 'x' FROM b_tasks_member TM " . "WHERE TM.TASK_ID = " . $DB->escL . (ToUpper($DBType) === "ORACLE" ? "TASKS_TASK" : "tasks_task") . $DB->escR . ".ID AND TM.USER_ID = " . $USER->GetID() . " AND TM.TYPE = 'A'" . ") THEN 1 ELSE 0 END)")); $permFilter[] = array('LOGIC' => 'OR', '=RESPONSIBLE_ID' => $USER->GetID(), '=IS_TASK_COWORKER' => 1); } // own departments permission if (isset($_GET['select_depts_tasks'])) { $permFilterDepts = array('LOGIC' => 'OR', '=CREATED_BY' => $USER->GetID()); $deptsPermSql = CTasks::GetSubordinateSql('__ULTRAUNIQUEPREFIX__'); if (strlen($deptsPermSql)) { $deptsPermSql = "EXISTS(" . $deptsPermSql . ")"; $deptsPermSql = str_replace('__ULTRAUNIQUEPREFIX__T.', $DB->escL . (ToUpper($DBType) === "ORACLE" ? "TASKS_TASK" : "tasks_task") . $DB->escR . '.', $deptsPermSql); $deptsPermSql = str_replace('__ULTRAUNIQUEPREFIX__', '', $deptsPermSql); $runtime['IS_SUBORDINATED_TASK'] = array('data_type' => 'integer', 'expression' => array("(CASE WHEN " . $deptsPermSql . " THEN 1 ELSE 0 END)")); $permFilterDepts[] = array('!RESPONSIBLE_ID' => $USER->GetID(), '=IS_SUBORDINATED_TASK' => 1); } $permFilter[] = $permFilterDepts; } // group permission if (isset($_GET['select_group_tasks'])) { $allowedGroups = CTasks::GetAllowedGroups(); $permFilter[] = array('=GROUP_ID' => $allowedGroups); } // re-aggregate aggregated subquery in DURATION for mssql if (\Bitrix\Main\Application::getConnection() instanceof \Bitrix\Main\DB\MssqlConnection) { foreach ($select as $k => $v) { if (substr($k, -9) == '_DURATION') { // we have aggregated duration $subQuery = new \Bitrix\Main\Entity\Query(\Bitrix\Tasks\ElapsedTimeTable::getEntity()); $subQuery->addSelect('TASK_ID'); $subQuery->addSelect(new \Bitrix\Main\Entity\ExpressionField('DURATION', 'ROUND(SUM(%s)/60, 0)', 'SECONDS')); $subEntity = \Bitrix\Main\Entity\Base::getInstanceByQuery($subQuery); // make reference $subReferenceName = $k . '_REF'; $runtime[$subReferenceName] = array('data_type' => $subEntity, 'reference' => array('=this.ID' => 'ref.TASK_ID')); // rewrite aggregated duration (put it in the end, after refence) $runtimeField = $runtime[$k]; unset($runtime[$k]); $runtimeField['expression'][1] = $subReferenceName . '.DURATION'; $runtime[$k] = $runtimeField; } else { if (substr($k, -20) == '_DURATION_FOR_PERIOD' && isset($options['SQL_TIME_INTERVAL'])) { // we have aggregated DURATION_FOR_PERIOD field $subQuery = new \Bitrix\Main\Entity\Query(\Bitrix\Tasks\ElapsedTimeTable::getEntity()); $subQuery->addSelect('TASK_ID'); $subQuery->addSelect(new \Bitrix\Main\Entity\ExpressionField('DURATION_FOR_PERIOD', 'ROUND((SUM(CASE WHEN CREATED_DATE ' . $options['SQL_TIME_INTERVAL'] . ' THEN %s ELSE 0 END)/60),0)', 'SECONDS')); $subEntity = \Bitrix\Main\Entity\Base::getInstanceByQuery($subQuery); // make reference $subReferenceName = $k . '_REF'; $runtime[$subReferenceName] = array('data_type' => $subEntity, 'reference' => array('=this.ID' => 'ref.TASK_ID')); // rewrite aggregated duration (put it in the end, after refence) $runtimeField = $runtime[$k]; unset($runtime[$k]); $runtimeField['expression'][1] = $subReferenceName . '.DURATION_FOR_PERIOD'; $runtime[$k] = $runtimeField; } } } } // concat permissions with common filter $filter[] = $permFilter; }
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; }
protected static function getRuntimeFieldMixinsCheckRights($parameters) { $result = false; $parameters['USER_ID'] = Assert::expectIntegerPositive($parameters['USER_ID'], '$parameters[USER_ID]'); $rf = $parameters['REF_FIELD']; if (!\CTasksTools::IsAdmin($userId) && !\CTasksTools::IsPortalB24Admin($userId)) { list($conditions, $expression) = \CTasks::getPermissionFilterConditions($parameters, array('USE_PLACEHOLDERS' => true)); $conditions = "(case when (" . implode(' OR ', $conditions) . ") then '1' else '0' end)"; array_unshift($expression, $conditions); $query = new \Bitrix\Main\Entity\Query('Bitrix\\Tasks\\Task'); $query->registerRuntimeField('F', array('data_type' => 'string', 'expression' => $expression)); $query->setFilter(array('=F' => '1')); $query->setSelect(array('TASK_ID' => 'ID')); $result = new Entity\ReferenceField($parameters['NAME'], \Bitrix\Main\Entity\Base::getInstanceByQuery($query), array('=this.' . ((string) $rf != '' ? $rf : 'ID') => 'ref.TASK_ID'), array('join_type' => 'inner')); } return $result; }