/** @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;
}
/**
* @return array|null
*/
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(DealInvoiceStatisticsTable::getEntity());
$subQuery->registerRuntimeField('', new ExpressionField('MAX_CREATED_DATE', 'MAX(CREATED_DATE)'));
$subQuery->addSelect('MAX_CREATED_DATE');
$subQuery->addFilter('=OWNER_ID', $ownerID);
$subQuery->addGroup('OWNER_ID');
$query = new Query(DealInvoiceStatisticsTable::getEntity());
$query->addSelect('*');
$query->registerRuntimeField('', new ReferenceField('M', Base::getInstanceByQuery($subQuery), array('=this.OWNER_ID' => new SqlExpression($ownerID), '=this.CREATED_DATE' => 'ref.MAX_CREATED_DATE'), array('join_type' => 'INNER')));
$dbResult = $query->exec();
$result = $dbResult->fetch();
return is_array($result) ? $result : null;
}
/** @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')));
}
/**
* @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;
}
/** @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')));
}
/**
* @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;
}
/**
* @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')));
}
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;
}
/**
* @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;
}
