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