/** @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; }