/**
* @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.");
}
/** @var array $select */
$select = isset($params['select']) && is_array($params['select']) ? $params['select'] : array();
$name = '';
if (!empty($select)) {
$selectItem = $select[0];
if (isset($selectItem['name'])) {
$name = $selectItem['name'];
}
}
if ($name === '') {
$name = 'COUNT';
}
$group = isset($params['group']) ? strtoupper($params['group']) : '';
if ($group !== '' && $group !== self::GROUP_BY_DATE) {
$group = '';
}
$enableGroupByDate = $group !== '';
$period = $filter->getPeriod();
$periodStartDate = $period['START'];
$periodEndDate = $period['END'];
$query = new Query(DealStageHistoryTable::getEntity());
$query->registerRuntimeField('', new ExpressionField($name, "COUNT(*)"));
$query->addSelect($name);
$query->addFilter('=TYPE_ID', HistoryEntryType::CREATION);
$query->addFilter('>=START_DATE', $periodStartDate);
$query->addFilter('<=START_DATE', $periodEndDate);
if ($enableGroupByDate) {
$query->addSelect('START_DATE', 'DATE');
$query->addGroup('START_DATE');
$query->addOrder('START_DATE', 'ASC');
}
$query->registerRuntimeField('', new ExpressionField('E1', '(CASE WHEN NOT EXISTS(' . self::prepareHistoryQuery($periodStartDate, $periodEndDate, HistoryEntryType::MODIFICATION, '%s', '_h')->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', '_h')->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', '_a')->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', '_i')->getQuery() . ') THEN 1 ELSE 0 END)', 'OWNER_ID'));
$query->addFilter('=E4', 1);
$results = array();
$dbResult = $query->exec();
while ($ary = $dbResult->fetch()) {
if ($enableGroupByDate) {
/** @var Date $date */
$date = $ary['DATE'];
$ary['DATE'] = $date->format('Y-m-d');
}
$results[] = $ary;
}
return $results;
}
/**
* @return array
*/
public static function getAll($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(DealSumStatisticsTable::getEntity());
$query->addSelect('*');
$query->addFilter('=OWNER_ID', $ownerID);
$query->addOrder('CREATED_DATE', 'ASC');
$dbResult = $query->exec();
$results = array();
while ($fields = $dbResult->fetch()) {
$results[] = $fields;
}
return $results;
}
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 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 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 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;
}
/**
* @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 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;
}
