/** @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 Query
*/
protected static function prepareHistoryQuery($startDate, $endDate, $responsibleIDs = null, $groupByDate = true)
{
$query = new Query(DealStageHistoryTable::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')));
}
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 Query
*/
protected static function prepareHistoryQuery($startDate, $endDate, $groupByDate = true)
{
$query = new Query(DealStageHistoryTable::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;
}
