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);
}
}
}
}
}
}
}
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;
}
/** @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 static function getMismatches($entityTypeID, $entityID, $typeID, $matchHash, $userID, $limit = 0)
{
if (!is_int($limit)) {
$limit = (int) $limit;
}
$results = array();
$query = new Main\Entity\Query(Entity\DuplicateIndexMismatchTable::getEntity());
$query->addSelect('R_ENTITY_ID', 'ENTITY_ID');
$query->addFilter('=USER_ID', $userID);
$query->addFilter('=ENTITY_TYPE_ID', $entityTypeID);
$query->addFilter('=TYPE_ID', $typeID);
$query->addFilter('=MATCH_HASH', $matchHash);
$query->addFilter('=L_ENTITY_ID', $entityID);
if ($limit > 0) {
$query->addOrder('R_ENTITY_ID', 'ASC');
$query->setLimit($limit);
}
$dbResult = $query->exec();
while ($fields = $dbResult->fetch()) {
$results[] = (int) $fields['ENTITY_ID'];
}
$query = new Main\Entity\Query(Entity\DuplicateIndexMismatchTable::getEntity());
$query->addSelect('L_ENTITY_ID', 'ENTITY_ID');
$query->addFilter('=USER_ID', $userID);
$query->addFilter('=ENTITY_TYPE_ID', $entityTypeID);
$query->addFilter('=TYPE_ID', $typeID);
$query->addFilter('=MATCH_HASH', $matchHash);
$query->addFilter('=R_ENTITY_ID', $entityID);
if ($limit > 0) {
$query->addOrder('L_ENTITY_ID', 'ASC');
$query->setLimit($limit);
}
$dbResult = $query->exec();
while ($fields = $dbResult->fetch()) {
$results[] = (int) $fields['ENTITY_ID'];
}
return $results;
}
/**
* @return boolean
*/
public static function isRegistered($ownerID)
{
if (!is_int($ownerID)) {
$ownerID = (int) $ownerID;
}
if ($ownerID <= 0) {
throw new Main\ArgumentException('Owner ID must be greater than zero.', 'ownerID');
}
$query = new Query(DealInvoiceStatisticsTable::getEntity());
$query->addSelect('CREATED_DATE');
$query->addFilter('=OWNER_ID', $ownerID);
$query->setLimit(1);
$dbResult = $query->exec();
$result = $dbResult->fetch();
return is_array($result);
}
/**
* @return boolean
*/
public static function synchronize($ownerID, array $entityFields = null)
{
if (!is_int($ownerID)) {
$ownerID = (int) $ownerID;
}
if ($ownerID <= 0) {
throw new Main\ArgumentException('Owner ID must be greater than zero.', 'ownerID');
}
$query = new Query(DealActivityStatisticsTable::getEntity());
$query->addSelect('RESPONSIBLE_ID');
$query->addFilter('=OWNER_ID', $ownerID);
$query->setLimit(1);
$dbResult = $query->exec();
$first = $dbResult->fetch();
if (!is_array($first)) {
return false;
}
if (!is_array($entityFields)) {
$dbResult = \CCrmDeal::GetListEx(array(), array('=ID' => $ownerID, 'CHECK_PERMISSIONS' => 'N'), false, false, array('ASSIGNED_BY_ID'));
$entityFields = is_object($dbResult) ? $dbResult->Fetch() : null;
if (!is_array($entityFields)) {
return false;
}
}
$responsibleID = isset($entityFields['ASSIGNED_BY_ID']) ? (int) $entityFields['ASSIGNED_BY_ID'] : 0;
if ($responsibleID === (int) $first['RESPONSIBLE_ID']) {
return false;
}
DealActivityStatisticsTable::synchronize($ownerID, array('RESPONSIBLE_ID' => $responsibleID));
return true;
}
protected function getRootEntityID($matchHash)
{
$query = new Main\Entity\Query(Entity\DuplicateIndexTable::getEntity());
$query->addSelect('ROOT_ENTITY_ID');
$query->addFilter('=USER_ID', $this->getUserID());
$query->addFilter('=ENTITY_TYPE_ID', $this->getEntityTypeID());
$query->addFilter('=TYPE_ID', $this->typeID);
$query->addFilter('=MATCH_HASH', $matchHash);
$fields = $query->exec()->fetch();
return is_array($fields) ? (int) $fields['ROOT_ENTITY_ID'] : 0;
}
/**
* @return Query
*/
protected static function prepareInvoiceQuery($startDate, $endDate, $groupByDate = true)
{
$query = new Query(DealInvoiceStatisticsTable::getEntity());
$query->addSelect('OWNER_ID');
$query->addFilter('=IS_LOST', false);
$query->addFilter('>=CREATED_DATE', $startDate);
$query->addFilter('<=CREATED_DATE', $endDate);
$query->addGroup('OWNER_ID');
if ($groupByDate) {
$query->addSelect('CREATED_DATE', 'DATE');
$query->addGroup('CREATED_DATE');
$query->addOrder('CREATED_DATE', 'ASC');
}
return $query;
}
/**
* @return Query
*/
protected static function prepareInvoiceQuery($startDate, $endDate, $responsibleIDs = null, $groupByDate = true)
{
$query = new Query(DealInvoiceStatisticsTable::getEntity());
$query->addSelect('OWNER_ID');
$query->addFilter('=IS_LOST', false);
$query->addFilter('>=CREATED_DATE', $startDate);
$query->addFilter('<=CREATED_DATE', $endDate);
$query->addGroup('OWNER_ID');
if (is_array($responsibleIDs) && !empty($responsibleIDs)) {
$query->addFilter('@RESPONSIBLE_ID', $responsibleIDs);
}
if ($groupByDate) {
$query->addSelect('CREATED_DATE', 'DATE');
$query->addGroup('CREATED_DATE');
$query->addOrder('CREATED_DATE', 'ASC');
}
return $query;
}
public static function synchronize($ownerID, array $entityFields = null)
{
if (!is_int($ownerID)) {
$ownerID = (int) $ownerID;
}
if ($ownerID <= 0) {
throw new Main\ArgumentException('Owner ID must be greater than zero.', 'ownerID');
}
$query = new Query(DealStageHistoryTable::getEntity());
$query->addSelect('START_DATE');
$query->addSelect('END_DATE');
$query->addSelect('RESPONSIBLE_ID');
$query->addFilter('=OWNER_ID', $ownerID);
$query->setLimit(1);
$dbResult = $query->exec();
$first = $dbResult->fetch();
if (!is_array($first)) {
return false;
}
if (!is_array($entityFields)) {
$dbResult = \CCrmDeal::GetListEx(array(), array('=ID' => $ownerID, 'CHECK_PERMISSIONS' => 'N'), false, false, array('ASSIGNED_BY_ID', 'BEGINDATE', 'CLOSEDATE'));
$entityFields = is_object($dbResult) ? $dbResult->Fetch() : null;
if (!is_array($entityFields)) {
return false;
}
}
$responsibleID = isset($entityFields['ASSIGNED_BY_ID']) ? (int) $entityFields['ASSIGNED_BY_ID'] : 0;
$beginDate = isset($entityFields['BEGINDATE']) ? $entityFields['BEGINDATE'] : '';
/** @var Date $startDate */
$startDate = new Date($beginDate);
$closeDate = isset($entityFields['CLOSEDATE']) ? $entityFields['CLOSEDATE'] : '';
/** @var Date $endDate */
$endDate = $closeDate !== '' ? new Date($closeDate) : new Date('9999-12-31', 'Y-m-d');
if ($startDate->getTimestamp() === $first['START_DATE']->getTimestamp() && $endDate->getTimestamp() === $first['END_DATE']->getTimestamp() && $responsibleID === (int) $first['RESPONSIBLE_ID']) {
return false;
}
DealStageHistoryTable::synchronize($ownerID, array('START_DATE' => $startDate, 'END_DATE' => $endDate, 'RESPONSIBLE_ID' => $responsibleID));
return true;
}
/**
* @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 getRegisteredCodes($entityTypeID, $entityID, $enablePermissionCheck = false, $userID = 0, $limit = 50)
{
if (!is_int($entityTypeID)) {
throw new Main\ArgumentTypeException('entityTypeID', 'integer');
}
if (!is_int($entityID)) {
throw new Main\ArgumentTypeException('entityID', 'integer');
}
if (!is_int($userID)) {
throw new Main\ArgumentTypeException('userID', 'integer');
}
if (!is_bool($enablePermissionCheck)) {
throw new Main\ArgumentTypeException('enablePermissionCheck', 'boolean');
}
if (!is_int($limit)) {
throw new Main\ArgumentTypeException('limit', 'integer');
}
$query = new Main\Entity\Query(DuplicateCommunicationMatchCodeTable::getEntity());
$query->addSelect('TYPE');
$query->addSelect('VALUE');
$query->addFilter('=ENTITY_TYPE_ID', $entityTypeID);
$query->addFilter('=ENTITY_ID', $entityID);
if ($enablePermissionCheck && $userID > 0) {
$permissions = isset($params['PERMISSIONS']) ? $params['PERMISSIONS'] : null;
if ($permissions === null) {
$permissions = \CCrmPerms::GetUserPermissions($userID);
}
$permissionSql = \CCrmPerms::BuildSql(\CCrmOwnerType::ResolveName($entityTypeID), '', 'READ', array('RAW_QUERY' => true, 'PERMS' => $permissions));
if ($permissionSql === false) {
//Access denied;
return array();
} elseif ($permissionSql !== '') {
$query->addFilter('@ENTITY_ID', new Main\DB\SqlExpression($permissionSql));
}
}
if ($limit > 0) {
$query->setLimit($limit);
}
$dbResult = $query->exec();
$results = array();
while ($fields = $dbResult->fetch()) {
$type = isset($fields['TYPE']) ? $fields['TYPE'] : '';
$value = isset($fields['VALUE']) ? $fields['VALUE'] : '';
if (!isset($results[$type])) {
$results[$type] = array();
}
$results[$type][] = $value;
}
return $results;
}
public static function getUsersTop($userId, $departmentId, Type\DateTime $dateFrom, Type\DateTime $dateTo, $interval, $section = null, $nonInvolvedOnly = false, $from = 0, $limit = 100)
{
if (!in_array($interval, array('hour', 'day', 'month'), true)) {
throw new Main\ArgumentException('Interval should be the "hour", or "day", or "month".');
}
$data = array();
// rating for TOTAL activity or for an instrument
$sumField = $section === null ? 'TOTAL' : $section;
if ($interval === 'hour') {
$query = new Entity\Query(UserHourTable::getEntity());
$query->setSelect(array('USER_ID', new Entity\ExpressionField('SUM_' . $sumField, 'SUM(%s)', $sumField)));
$query->setFilter(array('><HOUR' => array(ConvertTimeStamp($dateFrom->getTimestamp(), 'FULL'), ConvertTimeStamp($dateTo->getTimestamp(), 'FULL'))));
} else {
$query = new Entity\Query(UserDayTable::getEntity());
$query->setSelect(array('USER_ID', new Entity\ExpressionField('SUM_' . $sumField, 'SUM(%s)', $sumField)));
$query->setFilter(array('><DAY' => array(ConvertTimeStamp($dateFrom->getTimestamp()), ConvertTimeStamp($dateTo->getTimestamp()))));
}
if ($sumField == 'TOTAL') {
// count number of used services
$names = UserHourTable::getSectionNames();
$fieldExpressions = array_fill(0, count($names), 'CASE WHEN SUM(%s) > 0 THEN 1 ELSE 0 END');
$serviceCountExpression = join(' + ', $fieldExpressions);
$query->addSelect(new Entity\ExpressionField('SERVICES_COUNT', $serviceCountExpression, $names));
if ($nonInvolvedOnly) {
// who didn't use 4 or more instruments
$query->addFilter('<SERVICES_COUNT', static::INVOLVEMENT_SERVICE_COUNT);
}
} else {
if ($nonInvolvedOnly) {
// who didn't use instrument
$query->addFilter('=SUM_' . $sumField, 0);
} else {
// who used it
$query->addFilter('>SUM_' . $sumField, 0);
}
}
$query->addOrder('SUM_' . $sumField, 'DESC');
if (!$nonInvolvedOnly) {
// we don't need this for non-involved users
$query->registerRuntimeField('MYSELF', array('data_type' => 'integer', 'expression' => array('CASE WHEN %s = ' . (int) $userId . ' THEN 1 ELSE 0 END', 'USER_ID')));
$query->addOrder('MYSELF', 'DESC');
}
$query->setOffset($from);
$query->setLimit($limit);
$result = $query->exec();
while ($row = $result->fetch()) {
$_data = array('USER_ID' => $row['USER_ID'], 'ACTIVITY' => $row['SUM_' . $sumField]);
if ($sumField == 'TOTAL') {
$_data['SERVICES_COUNT'] = $row['SERVICES_COUNT'];
$_data['IS_INVOLVED'] = $row['SERVICES_COUNT'] >= static::INVOLVEMENT_SERVICE_COUNT;
} else {
$_data['SERVICES_COUNT'] = null;
$_data['IS_INVOLVED'] = $row['SUM_' . $sumField] > 0;
}
$data[] = $_data;
}
return $data;
}
/**
* @return Main\Entity\Query
*/
private function createQuery($offset = 0, $limit = 0)
{
if (!is_int($offset)) {
$offset = intval($offset);
}
if (!is_int($limit)) {
$limit = intval($limit);
}
$typeIDs = $this->getTypeIDs();
if (empty($typeIDs)) {
throw new Main\NotSupportedException("Criterion types are required.");
}
$query = new Main\Entity\Query(Entity\DuplicateIndexTable::getEntity());
$query->addSelect('ROOT_ENTITY_ID');
$query->addSelect('ROOT_ENTITY_NAME');
$query->addSelect('ROOT_ENTITY_TITLE');
$query->addSelect('QUANTITY');
$query->addSelect('TYPE_ID');
$query->addSelect('MATCHES');
$query->addSelect('IS_JUNK');
$permissionSql = '';
if ($this->enablePermissionCheck) {
$permissions = \CCrmPerms::GetUserPermissions($this->userID);
$permissionSql = \CCrmPerms::BuildSql(\CCrmOwnerType::ResolveName($this->entityTypeID), '', 'READ', array('RAW_QUERY' => true, 'PERMS' => $permissions));
if ($permissionSql === false) {
//Access denied;
return null;
}
}
$query->addFilter('=USER_ID', $this->userID);
$query->addFilter('=ENTITY_TYPE_ID', $this->entityTypeID);
$query->addFilter('@TYPE_ID', $typeIDs);
if ($this->enablePermissionCheck && $permissionSql !== '') {
$query->addFilter('@ROOT_ENTITY_ID', new Main\DB\SqlExpression($permissionSql));
}
if ($offset > 0) {
$query->setOffset($offset);
}
if ($limit > 0) {
$query->setLimit($limit);
}
$enableSorting = $this->sortTypeID !== DuplicateIndexType::UNDEFINED;
if ($enableSorting) {
$order = $this->sortOrder === SORT_DESC ? 'DESC' : 'ASC';
if ($this->sortTypeID === DuplicateIndexType::COMMUNICATION_EMAIL) {
$query->addOrder('ROOT_ENTITY_EMAIL_FLAG', $order);
$query->addOrder('ROOT_ENTITY_EMAIL', $order);
} elseif ($this->sortTypeID === DuplicateIndexType::COMMUNICATION_PHONE) {
$query->addOrder('ROOT_ENTITY_PHONE_FLAG', $order);
$query->addOrder('ROOT_ENTITY_PHONE', $order);
} elseif ($this->sortTypeID === DuplicateIndexType::PERSON) {
$query->addOrder('ROOT_ENTITY_NAME_FLAG', $order);
$query->addOrder('ROOT_ENTITY_NAME', $order);
} elseif ($this->sortTypeID === DuplicateIndexType::ORGANIZATION) {
$query->addOrder('ROOT_ENTITY_TITLE_FLAG', $order);
$query->addOrder('ROOT_ENTITY_TITLE', $order);
}
}
return $query;
}
protected function getFilterCswFields(&$filter)
{
$fields = array();
foreach ($filter as $filter_def => &$filter_match) {
if ($filter_def === 'LOGIC') {
continue;
}
if (!is_numeric($filter_def)) {
$csw_result = \CSQLWhere::makeOperation($filter_def);
list($definition, ) = array_values($csw_result);
$chain = $this->filter_chains[$definition];
$last = $chain->getLastElement();
// need to create an alternative of CSQLWhere in D7.Entity
$field_type = $last->getValue()->getDataType();
// rewrite type & value for CSQLWhere
if ($field_type == 'integer') {
$field_type = 'int';
} elseif ($field_type == 'boolean') {
$field_type = 'string';
/** @var BooleanField $field */
$field = $last->getValue();
$values = $field->getValues();
if (is_numeric($values[0]) && is_numeric($values[1])) {
$field_type = 'int';
}
if (is_scalar($filter_match)) {
$filter_match = $field->normalizeValue($filter_match);
}
} elseif ($field_type == 'float') {
$field_type = 'double';
} elseif ($field_type == 'enum' || $field_type == 'text') {
$field_type = 'string';
}
$sqlDefinition = $chain->getSqlDefinition();
$callback = null;
// data-doubling-off mode
/** @see disableDataDoubling */
if ($chain->forcesDataDoublingOff() || $this->data_doubling_off && $chain->hasBackReference()) {
$primaryName = $this->init_entity->getPrimary();
$uniquePostfix = '_TMP' . rand();
// build subquery
$subQuery = new Query($this->init_entity);
$subQuery->addSelect($primaryName);
$subQuery->addFilter($filter_def, $filter_match);
$subQuery->setTableAliasPostfix(strtolower($uniquePostfix));
$subQuerySql = $subQuery->getQuery();
// proxying subquery as value to callback
$filter_match = $subQuerySql;
$callback = array($this, 'dataDoublingCallback');
$field_type = 'callback';
// change sql definition
$idChain = $this->getRegisteredChain($primaryName);
$sqlDefinition = $idChain->getSqlDefinition();
}
//$is_having = $last->getValue() instanceof ExpressionField && $last->getValue()->isAggregated();
// if back-reference found (Entity:REF)
// if NO_DOUBLING mode enabled, then change getSQLDefinition to subquery exists(...)
// and those chains should not be in joins if it is possible
/*if (!$this->data_doubling && $chain->hasBackReference())
{
$field_type = 'callback';
$init_query = $this;
$callback = function ($field, $operation, $value) use ($init_query, $chain)
{
$init_entity = $init_query->getEntity();
$init_table_alias = CBaseEntity::camel2snake($init_entity->getName()).$init_query->getTableAliasPostfix();
$filter = array();
// add primary linking with main query
foreach ($init_entity->getPrimaryArray() as $primary)
{
$filter['='.$primary] = new CSQLWhereExpression('?#', $init_table_alias.'.'.$primary);
}
// add value filter
$filter[CSQLWhere::getOperationByCode($operation).$chain->getDefinition()] = $value;
// build subquery
$query_class = __CLASS__;
$sub_query = new $query_class($init_entity);
$sub_query->setFilter($filter);
$sub_query->setTableAliasPostfix('_sub');
return 'EXISTS(' . $sub_query->getQuery() . ')';
};
}*/
$fields[$definition] = array('TABLE_ALIAS' => 'table', 'FIELD_NAME' => $sqlDefinition, 'FIELD_TYPE' => $field_type, 'MULTIPLE' => '', 'JOIN' => '', 'CALLBACK' => $callback);
}
if (is_array($filter_match)) {
$fields = array_merge($fields, $this->getFilterCswFields($filter_match));
}
}
return $fields;
}
public static function loadEntitiesMatches($entityTypeID, array $entityIDs)
{
$query = new Main\Entity\Query(DuplicateOrganizationMatchCodeTable::getEntity());
$query->addFilter('=ENTITY_TYPE_ID', $entityTypeID);
$query->addFilter('@ENTITY_ID', $entityIDs);
$query->addSelect('ENTITY_ID');
$query->addSelect('TITLE');
$dbResult = $query->exec();
$results = array();
while ($fields = $dbResult->fetch()) {
$entityID = isset($fields['ENTITY_ID']) ? (int) $fields['ENTITY_ID'] : 0;
if ($entityID <= 0) {
continue;
}
$results[$entityID] = array('TITLE' => isset($fields['TITLE']) ? $fields['TITLE'] : '');
}
return $results;
}
/** @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')));
}
protected function onAfterDuplicateCreated(Duplicate $dup, $entityTypeID, $userID, $enablePermissionCheck, $enableRanking, array &$rankings)
{
$name = $this->name;
$lastName = $this->lastName;
$secondName = $this->secondName;
$rootEntityID = $dup->getRootEntityID();
if ($secondName === '' && $name === '') {
return;
}
$permissionSql = '';
if ($enablePermissionCheck) {
$permissions = isset($params['PERMISSIONS']) ? $params['PERMISSIONS'] : null;
if ($permissions === null) {
$permissions = \CCrmPerms::GetUserPermissions($userID);
}
$permissionSql = \CCrmPerms::BuildSql(\CCrmOwnerType::ResolveName($entityTypeID), '', 'READ', array('RAW_QUERY' => true, 'PERMS' => $permissions));
if ($permissionSql === false) {
//Access denied;
return;
}
}
if ($secondName !== '') {
$query = new Main\Entity\Query(DuplicatePersonMatchCodeTable::getEntity());
$query->addSelect('ENTITY_ID');
$query->addFilter('=ENTITY_TYPE_ID', $entityTypeID);
$query->addFilter('=LAST_NAME', $lastName);
$query->addFilter('=NAME', $name);
$query->addFilter('=SECOND_NAME', '');
if ($rootEntityID) {
$query->addFilter('!ENTITY_ID', $rootEntityID);
$query->addFilter('!@ENTITY_ID', DuplicateIndexMismatch::prepareQueryField(self::createFromMatches(array('LAST_NAME' => $lastName, 'NAME' => $name)), $entityTypeID, $rootEntityID, $userID));
}
if ($enablePermissionCheck && $permissionSql !== '') {
$query->addFilter('@ENTITY_ID', new Main\DB\SqlExpression($permissionSql));
}
$dbResult = $query->exec();
while ($fields = $dbResult->fetch()) {
$entityID = isset($fields['ENTITY_ID']) ? intval($fields['ENTITY_ID']) : 0;
if ($entityID <= 0) {
continue;
}
$entity = new DuplicateEntity($entityTypeID, $entityID);
$entity->setCriterion(self::createFromMatches(array('LAST_NAME' => $lastName, 'NAME' => $name)));
if ($enableRanking) {
$rankings[] = $entity->getRanking();
}
$dup->addEntity($entity);
}
}
if ($name !== '') {
$query = new Main\Entity\Query(DuplicatePersonMatchCodeTable::getEntity());
$query->addSelect('ENTITY_ID');
$query->addFilter('=ENTITY_TYPE_ID', $entityTypeID);
$query->addFilter('=LAST_NAME', $lastName);
$query->addFilter('=NAME', '');
$query->addFilter('=SECOND_NAME', '');
if ($rootEntityID) {
$query->addFilter('!ENTITY_ID', $rootEntityID);
$query->addFilter('!@ENTITY_ID', DuplicateIndexMismatch::prepareQueryField(self::createFromMatches(array('LAST_NAME' => $lastName)), $entityTypeID, $rootEntityID, $userID));
}
if ($enablePermissionCheck && $permissionSql !== '') {
$query->addFilter('@ENTITY_ID', new Main\DB\SqlExpression($permissionSql));
}
$dbResult = $query->exec();
while ($fields = $dbResult->fetch()) {
$entityID = isset($fields['ENTITY_ID']) ? intval($fields['ENTITY_ID']) : 0;
if ($entityID <= 0) {
continue;
}
$entity = new DuplicateEntity($entityTypeID, $entityID);
$entity->setCriterion(self::createFromMatches(array('LAST_NAME' => $lastName)));
if ($enableRanking) {
$rankings[] = $entity->getRanking();
}
$dup->addEntity($entity);
}
}
}
/** @return array */
public function prepareEntityListFilter(array $filterParams)
{
$filter = self::internalizeFilter($filterParams);
$query = new Query(DealInvoiceStatisticsTable::getEntity());
$query->addSelect('OWNER_ID');
$query->addGroup('OWNER_ID');
$period = $filter->getPeriod();
$periodStartDate = $period['START'];
$periodEndDate = $period['END'];
$query->addFilter('>=END_DATE', $periodStartDate);
$query->addFilter('<=START_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);
}
return array('__JOINS' => array(array('TYPE' => 'INNER', 'SQL' => 'INNER JOIN(' . $query->getQuery() . ') DS ON DS.OWNER_ID = L.ID')));
}
/**
* Load conversion map
* @static
* @param int $srcEntityTypeID Source Entity Type ID
* @param int $dstEntityTypeID Destination Entity Type ID
* @return EntityConversionMap
*/
public static function load($srcEntityTypeID, $dstEntityTypeID)
{
$query = new Query(EntityConversionMapTable::getEntity());
$query->addSelect('DATA');
$query->addFilter('=SRC_TYPE_ID', $srcEntityTypeID);
$query->addFilter('=DST_TYPE_ID', $dstEntityTypeID);
$dbResult = $query->exec();
$result = $dbResult->fetch();
if (!is_array($result)) {
return null;
}
$params = isset($result['DATA']) ? unserialize($result['DATA']) : null;
if (!is_array($params)) {
return null;
}
$item = new EntityConversionMap();
$item->internalize($params);
return $item;
}
