/**
* @return Query
*/
protected static function prepareActivityQuery($startDate, $endDate, $responsibleIDs = null, $groupByDate = true)
{
$query = new Query(DealActivityStatisticsTable::getEntity());
$query->addSelect('OWNER_ID');
$query->addFilter('=IS_LOST', false);
$query->addFilter('>=DEADLINE_DATE', $startDate);
$query->addFilter('<=DEADLINE_DATE', $endDate);
$query->addGroup('OWNER_ID');
if (is_array($responsibleIDs) && !empty($responsibleIDs)) {
$query->addFilter('@RESPONSIBLE_ID', $responsibleIDs);
}
if ($groupByDate) {
$query->addSelect('DEADLINE_DATE', 'DATE');
$query->addGroup('DEADLINE_DATE');
$query->addOrder('DEADLINE_DATE', 'ASC');
}
return $query;
}
/**
* @return Query
*/
protected static function prepareActivityQuery($startDate, $endDate, $ownerFieldReference = '%s', $postfix = '')
{
$query = new Query(DealActivityStatisticsTable::getEntity());
if ($postfix !== '') {
$query->setTableAliasPostfix($postfix);
}
$query->addFilter('=IS_LOST', false);
$query->addFilter('>=DEADLINE_DATE', $startDate);
$query->addFilter('<=DEADLINE_DATE', $endDate);
$query->addFilter('=OWNER_ID', new SqlExpression($ownerFieldReference));
if (!Main\Application::getConnection() instanceof Main\DB\OracleConnection) {
$query->setLimit(1);
}
return $query;
}
/**
* @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;
}
/** @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')));
}
/** @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);
}
return array('__JOINS' => array(array('TYPE' => 'INNER', 'SQL' => 'INNER JOIN(' . $query->getQuery() . ') DS ON DS.OWNER_ID = L.ID')));
}
/**
* @return Query
*/
protected static function prepareActivityQuery($startDate, $endDate, $groupByDate = true)
{
$query = new Query(DealActivityStatisticsTable::getEntity());
$query->addSelect('OWNER_ID');
//$query->addFilter('=IS_LOST', false);
$query->addFilter('>=DEADLINE_DATE', $startDate);
$query->addFilter('<=DEADLINE_DATE', $endDate);
$query->addGroup('OWNER_ID');
if ($groupByDate) {
$query->addSelect('DEADLINE_DATE', 'DATE');
$query->addGroup('DEADLINE_DATE');
$query->addOrder('DEADLINE_DATE', 'ASC');
}
return $query;
}