/** * @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."); } /** @var array $select */ $select = isset($params['select']) && is_array($params['select']) ? $params['select'] : array(); $name = ''; if (!empty($select)) { $selectItem = $select[0]; if (isset($selectItem['name'])) { $name = $selectItem['name']; } } if ($name === '') { $name = 'COUNT'; } $group = isset($params['group']) ? strtoupper($params['group']) : ''; if ($group !== '' && $group !== self::GROUP_BY_DATE) { $group = ''; } $enableGroupByDate = $group !== ''; $period = $filter->getPeriod(); $periodStartDate = $period['START']; $periodEndDate = $period['END']; $query = new Query(DealStageHistoryTable::getEntity()); $query->registerRuntimeField('', new ExpressionField($name, "COUNT(*)")); $query->addSelect($name); $query->addFilter('=TYPE_ID', HistoryEntryType::CREATION); $query->addFilter('>=START_DATE', $periodStartDate); $query->addFilter('<=START_DATE', $periodEndDate); if ($enableGroupByDate) { $query->addSelect('START_DATE', 'DATE'); $query->addGroup('START_DATE'); $query->addOrder('START_DATE', 'ASC'); } $query->registerRuntimeField('', new ExpressionField('E1', '(CASE WHEN NOT EXISTS(' . self::prepareHistoryQuery($periodStartDate, $periodEndDate, HistoryEntryType::MODIFICATION, '%s', '_h')->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', '_h')->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', '_a')->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', '_i')->getQuery() . ') THEN 1 ELSE 0 END)', 'OWNER_ID')); $query->addFilter('=E4', 1); $results = array(); $dbResult = $query->exec(); while ($ary = $dbResult->fetch()) { if ($enableGroupByDate) { /** @var Date $date */ $date = $ary['DATE']; $ary['DATE'] = $date->format('Y-m-d'); } $results[] = $ary; } return $results; }
/** * @return array */ public static function getAll($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(DealSumStatisticsTable::getEntity()); $query->addSelect('*'); $query->addFilter('=OWNER_ID', $ownerID); $query->addOrder('CREATED_DATE', 'ASC'); $dbResult = $query->exec(); $results = array(); while ($fields = $dbResult->fetch()) { $results[] = $fields; } return $results; }
public static function getMismatches($entityTypeID, $entityID, $typeID, $matchHash, $userID, $limit = 0) { if (!is_int($limit)) { $limit = (int) $limit; } $results = array(); $query = new Main\Entity\Query(Entity\DuplicateIndexMismatchTable::getEntity()); $query->addSelect('R_ENTITY_ID', 'ENTITY_ID'); $query->addFilter('=USER_ID', $userID); $query->addFilter('=ENTITY_TYPE_ID', $entityTypeID); $query->addFilter('=TYPE_ID', $typeID); $query->addFilter('=MATCH_HASH', $matchHash); $query->addFilter('=L_ENTITY_ID', $entityID); if ($limit > 0) { $query->addOrder('R_ENTITY_ID', 'ASC'); $query->setLimit($limit); } $dbResult = $query->exec(); while ($fields = $dbResult->fetch()) { $results[] = (int) $fields['ENTITY_ID']; } $query = new Main\Entity\Query(Entity\DuplicateIndexMismatchTable::getEntity()); $query->addSelect('L_ENTITY_ID', 'ENTITY_ID'); $query->addFilter('=USER_ID', $userID); $query->addFilter('=ENTITY_TYPE_ID', $entityTypeID); $query->addFilter('=TYPE_ID', $typeID); $query->addFilter('=MATCH_HASH', $matchHash); $query->addFilter('=R_ENTITY_ID', $entityID); if ($limit > 0) { $query->addOrder('L_ENTITY_ID', 'ASC'); $query->setLimit($limit); } $dbResult = $query->exec(); while ($fields = $dbResult->fetch()) { $results[] = (int) $fields['ENTITY_ID']; } return $results; }
/** @return array */ public function getList(array $params) { $group = isset($params['group']) ? strtoupper($params['group']) : ''; if ($group !== '' && $group !== self::GROUP_BY_USER && $group !== self::GROUP_BY_DATE) { $group = ''; } /** @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."); } $semanticID = $filter->getExtraParam('semanticID', PhaseSemantics::UNDEFINED); $isFinalSemantics = PhaseSemantics::isFinal($semanticID); $group = isset($params['group']) ? strtoupper($params['group']) : ''; if ($group !== '' && $group !== self::GROUP_BY_USER && $group !== self::GROUP_BY_DATE) { $group = ''; } /** @var array $select */ $select = isset($params['select']) && is_array($params['select']) ? $params['select'] : array(); $name = ''; $aggregate = ''; if (!empty($select)) { $selectItem = $select[0]; if (isset($selectItem['name'])) { $name = $selectItem['name']; } if (isset($selectItem['aggregate'])) { $aggregate = strtoupper($selectItem['aggregate']); } } if ($name === '') { $name = 'INVOICE_SUM'; } if ($aggregate !== '' && !in_array($aggregate, array('SUM', 'COUNT', 'MAX', 'MIN'))) { $aggregate = ''; } $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(DealInvoiceStatisticsTable::getEntity()); $query->addSelect($name); if ($aggregate !== '') { if ($aggregate === 'COUNT') { $query->registerRuntimeField('', new ExpressionField($name, "COUNT(*)")); } else { $query->registerRuntimeField('', new ExpressionField($name, "{$aggregate}({$name})")); } } $query->setTableAliasPostfix('_s2'); $subQuery = new Query(DealInvoiceStatisticsTable::getEntity()); $subQuery->setTableAliasPostfix('_s1'); $subQuery->addSelect('OWNER_ID'); $subQuery->addFilter('>=END_DATE', $periodStartDate); $subQuery->addFilter('<=START_DATE', $periodEndDate); //$subQuery->addFilter('<=CREATED_DATE', $periodEndDate); if ($semanticID !== PhaseSemantics::UNDEFINED) { $subQuery->addFilter('=STAGE_SEMANTIC_ID', $semanticID); } if ($this->enablePermissionCheck && is_string($permissionSql) && $permissionSql !== '') { $subQuery->addFilter('@OWNER_ID', new SqlExpression($permissionSql)); } $responsibleIDs = $filter->getResponsibleIDs(); if (!empty($responsibleIDs)) { $subQuery->addFilter('@RESPONSIBLE_ID', $responsibleIDs); } $subQuery->addGroup('OWNER_ID'); $subQuery->addSelect('MAX_CREATED_DATE'); $subQuery->registerRuntimeField('', new ExpressionField('MAX_CREATED_DATE', 'MAX(CREATED_DATE)')); $query->registerRuntimeField('', new ReferenceField('M', Base::getInstanceByQuery($subQuery), array('=this.OWNER_ID' => 'ref.OWNER_ID', '=this.CREATED_DATE' => 'ref.MAX_CREATED_DATE'), array('join_type' => 'INNER'))); $sort = isset($params['sort']) && is_array($params['sort']) && !empty($params['sort']) ? $params['sort'] : null; if ($sort) { foreach ($sort as $sortItem) { if (isset($sortItem['name'])) { $query->addOrder($sortItem['name'], isset($sortItem['order']) ? $sortItem['order'] : 'asc'); } } } if ($group !== '') { if ($group === self::GROUP_BY_USER) { $query->addSelect('RESPONSIBLE_ID'); $query->addGroup('RESPONSIBLE_ID'); } else { if ($group === self::GROUP_BY_DATE) { if ($isFinalSemantics) { $query->addSelect('END_DATE', 'D'); $query->addGroup('END_DATE'); if (!$sort) { $query->addOrder('END_DATE', 'ASC'); } } else { $query->addSelect('CREATED_DATE', 'D'); $query->addGroup('CREATED_DATE'); if (!$sort) { $query->addOrder('CREATED_DATE', 'ASC'); } } } } } $dbResult = $query->exec(); //Trace('sql', Query::getLastQuery(), 1); $result = array(); if ($group === self::GROUP_BY_DATE) { while ($ary = $dbResult->fetch()) { $ary['DATE'] = $ary['D']->format('Y-m-d'); unset($ary['D']); if ($ary['DATE'] === '9999-12-31') { //Skip empty dates continue; } $result[] = $ary; } } elseif ($group === self::GROUP_BY_USER) { $userIDs = array(); while ($ary = $dbResult->fetch()) { $userID = $ary['RESPONSIBLE_ID'] = (int) $ary['RESPONSIBLE_ID']; if ($userID > 0 && !isset($userNames[$userID])) { $userIDs[] = $userID; } $result[] = $ary; } $userNames = self::prepareUserNames($userIDs); foreach ($result as &$item) { $userID = $item['RESPONSIBLE_ID']; $item['USER_ID'] = $userID; $item['USER'] = isset($userNames[$userID]) ? $userNames[$userID] : "[{$userID}]"; unset($item['RESPONSIBLE_ID']); } unset($item); } else { while ($ary = $dbResult->fetch()) { $result[] = $ary; } } return $result; }
/** * @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; }
public static function getUsersTop($userId, $departmentId, Type\DateTime $dateFrom, Type\DateTime $dateTo, $interval, $section = null, $nonInvolvedOnly = false, $from = 0, $limit = 100) { if (!in_array($interval, array('hour', 'day', 'month'), true)) { throw new Main\ArgumentException('Interval should be the "hour", or "day", or "month".'); } $data = array(); // rating for TOTAL activity or for an instrument $sumField = $section === null ? 'TOTAL' : $section; if ($interval === 'hour') { $query = new Entity\Query(UserHourTable::getEntity()); $query->setSelect(array('USER_ID', new Entity\ExpressionField('SUM_' . $sumField, 'SUM(%s)', $sumField))); $query->setFilter(array('><HOUR' => array(ConvertTimeStamp($dateFrom->getTimestamp(), 'FULL'), ConvertTimeStamp($dateTo->getTimestamp(), 'FULL')))); } else { $query = new Entity\Query(UserDayTable::getEntity()); $query->setSelect(array('USER_ID', new Entity\ExpressionField('SUM_' . $sumField, 'SUM(%s)', $sumField))); $query->setFilter(array('><DAY' => array(ConvertTimeStamp($dateFrom->getTimestamp()), ConvertTimeStamp($dateTo->getTimestamp())))); } if ($sumField == 'TOTAL') { // count number of used services $names = UserHourTable::getSectionNames(); $fieldExpressions = array_fill(0, count($names), 'CASE WHEN SUM(%s) > 0 THEN 1 ELSE 0 END'); $serviceCountExpression = join(' + ', $fieldExpressions); $query->addSelect(new Entity\ExpressionField('SERVICES_COUNT', $serviceCountExpression, $names)); if ($nonInvolvedOnly) { // who didn't use 4 or more instruments $query->addFilter('<SERVICES_COUNT', static::INVOLVEMENT_SERVICE_COUNT); } } else { if ($nonInvolvedOnly) { // who didn't use instrument $query->addFilter('=SUM_' . $sumField, 0); } else { // who used it $query->addFilter('>SUM_' . $sumField, 0); } } $query->addOrder('SUM_' . $sumField, 'DESC'); if (!$nonInvolvedOnly) { // we don't need this for non-involved users $query->registerRuntimeField('MYSELF', array('data_type' => 'integer', 'expression' => array('CASE WHEN %s = ' . (int) $userId . ' THEN 1 ELSE 0 END', 'USER_ID'))); $query->addOrder('MYSELF', 'DESC'); } $query->setOffset($from); $query->setLimit($limit); $result = $query->exec(); while ($row = $result->fetch()) { $_data = array('USER_ID' => $row['USER_ID'], 'ACTIVITY' => $row['SUM_' . $sumField]); if ($sumField == 'TOTAL') { $_data['SERVICES_COUNT'] = $row['SERVICES_COUNT']; $_data['IS_INVOLVED'] = $row['SERVICES_COUNT'] >= static::INVOLVEMENT_SERVICE_COUNT; } else { $_data['SERVICES_COUNT'] = null; $_data['IS_INVOLVED'] = $row['SUM_' . $sumField] > 0; } $data[] = $_data; } return $data; }
/** * @return Main\Entity\Query */ private function createQuery($offset = 0, $limit = 0) { if (!is_int($offset)) { $offset = intval($offset); } if (!is_int($limit)) { $limit = intval($limit); } $typeIDs = $this->getTypeIDs(); if (empty($typeIDs)) { throw new Main\NotSupportedException("Criterion types are required."); } $query = new Main\Entity\Query(Entity\DuplicateIndexTable::getEntity()); $query->addSelect('ROOT_ENTITY_ID'); $query->addSelect('ROOT_ENTITY_NAME'); $query->addSelect('ROOT_ENTITY_TITLE'); $query->addSelect('QUANTITY'); $query->addSelect('TYPE_ID'); $query->addSelect('MATCHES'); $query->addSelect('IS_JUNK'); $permissionSql = ''; if ($this->enablePermissionCheck) { $permissions = \CCrmPerms::GetUserPermissions($this->userID); $permissionSql = \CCrmPerms::BuildSql(\CCrmOwnerType::ResolveName($this->entityTypeID), '', 'READ', array('RAW_QUERY' => true, 'PERMS' => $permissions)); if ($permissionSql === false) { //Access denied; return null; } } $query->addFilter('=USER_ID', $this->userID); $query->addFilter('=ENTITY_TYPE_ID', $this->entityTypeID); $query->addFilter('@TYPE_ID', $typeIDs); if ($this->enablePermissionCheck && $permissionSql !== '') { $query->addFilter('@ROOT_ENTITY_ID', new Main\DB\SqlExpression($permissionSql)); } if ($offset > 0) { $query->setOffset($offset); } if ($limit > 0) { $query->setLimit($limit); } $enableSorting = $this->sortTypeID !== DuplicateIndexType::UNDEFINED; if ($enableSorting) { $order = $this->sortOrder === SORT_DESC ? 'DESC' : 'ASC'; if ($this->sortTypeID === DuplicateIndexType::COMMUNICATION_EMAIL) { $query->addOrder('ROOT_ENTITY_EMAIL_FLAG', $order); $query->addOrder('ROOT_ENTITY_EMAIL', $order); } elseif ($this->sortTypeID === DuplicateIndexType::COMMUNICATION_PHONE) { $query->addOrder('ROOT_ENTITY_PHONE_FLAG', $order); $query->addOrder('ROOT_ENTITY_PHONE', $order); } elseif ($this->sortTypeID === DuplicateIndexType::PERSON) { $query->addOrder('ROOT_ENTITY_NAME_FLAG', $order); $query->addOrder('ROOT_ENTITY_NAME', $order); } elseif ($this->sortTypeID === DuplicateIndexType::ORGANIZATION) { $query->addOrder('ROOT_ENTITY_TITLE_FLAG', $order); $query->addOrder('ROOT_ENTITY_TITLE', $order); } } return $query; }
/** * @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 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; }