/** @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, $ownerFieldReference = '%s', $postfix = '') { $query = new Query(DealInvoiceStatisticsTable::getEntity()); if ($postfix !== '') { $query->setTableAliasPostfix($postfix); } $query->addFilter('=IS_LOST', false); $query->addFilter('>=CREATED_DATE', $startDate); $query->addFilter('<=CREATED_DATE', $endDate); $query->addFilter('=OWNER_ID', new SqlExpression($ownerFieldReference)); if (!Main\Application::getConnection() instanceof Main\DB\OracleConnection) { $query->setLimit(1); } return $query; }
foreach ($entity->GetPrimaryArray() as $_primary) { $_sub_filter['=' . $_primary] = new CSQLWhereExpression('?#', $_sub_init_table_alias . '.' . $_primary); } // add value filter $filterCompare = CReport::$iBlockCompareVariations[$fElem['compare']]; $filterName = $fElem['name']; $filterValue = $fElem['value']; if ($filterCompare === '>%') { $filterCompare = ''; $filterValue = $filterValue . '%'; } $_sub_filter[$filterCompare . $filterName] = $filterValue; // build subquery $_sub_query = new Entity\Query($entity); $_sub_query->setFilter($_sub_filter); $_sub_query->setTableAliasPostfix('_sub'); $_sub_sql = 'EXISTS(' . $_sub_query->getQuery() . ')'; $_sub_sql = '(CASE WHEN ' . $_sub_sql . ' THEN 1 ELSE 0 END)'; // expression escaping as sprintf requires $_sub_sql = str_replace('%', '%%', $_sub_sql); $_runtime_field = array('data_type' => 'integer', 'expression' => array($_sub_sql)); $f_filter_alias = 'F_FILTER_ALIAS_' . ++$f_filter_alias_count; $runtime[$f_filter_alias] = $_runtime_field; $fElem['name'] = $f_filter_alias; $fElem['compare'] = 'EQUAL'; $fElem['value'] = 1; } } } } unset($fInfo);
protected function getFilterCswFields(&$filter) { $fields = array(); foreach ($filter as $filter_def => &$filter_match) { if ($filter_def === 'LOGIC') { continue; } if (!is_numeric($filter_def)) { $csw_result = \CSQLWhere::makeOperation($filter_def); list($definition, ) = array_values($csw_result); $chain = $this->filter_chains[$definition]; $last = $chain->getLastElement(); // need to create an alternative of CSQLWhere in D7.Entity $field_type = $last->getValue()->getDataType(); // rewrite type & value for CSQLWhere if ($field_type == 'integer') { $field_type = 'int'; } elseif ($field_type == 'boolean') { $field_type = 'string'; /** @var BooleanField $field */ $field = $last->getValue(); $values = $field->getValues(); if (is_numeric($values[0]) && is_numeric($values[1])) { $field_type = 'int'; } if (is_scalar($filter_match)) { $filter_match = $field->normalizeValue($filter_match); } } elseif ($field_type == 'float') { $field_type = 'double'; } elseif ($field_type == 'enum' || $field_type == 'text') { $field_type = 'string'; } $sqlDefinition = $chain->getSqlDefinition(); $callback = null; // data-doubling-off mode /** @see disableDataDoubling */ if ($chain->forcesDataDoublingOff() || $this->data_doubling_off && $chain->hasBackReference()) { $primaryName = $this->init_entity->getPrimary(); $uniquePostfix = '_TMP' . rand(); // build subquery $subQuery = new Query($this->init_entity); $subQuery->addSelect($primaryName); $subQuery->addFilter($filter_def, $filter_match); $subQuery->setTableAliasPostfix(strtolower($uniquePostfix)); $subQuerySql = $subQuery->getQuery(); // proxying subquery as value to callback $filter_match = $subQuerySql; $callback = array($this, 'dataDoublingCallback'); $field_type = 'callback'; // change sql definition $idChain = $this->getRegisteredChain($primaryName); $sqlDefinition = $idChain->getSqlDefinition(); } //$is_having = $last->getValue() instanceof ExpressionField && $last->getValue()->isAggregated(); // if back-reference found (Entity:REF) // if NO_DOUBLING mode enabled, then change getSQLDefinition to subquery exists(...) // and those chains should not be in joins if it is possible /*if (!$this->data_doubling && $chain->hasBackReference()) { $field_type = 'callback'; $init_query = $this; $callback = function ($field, $operation, $value) use ($init_query, $chain) { $init_entity = $init_query->getEntity(); $init_table_alias = CBaseEntity::camel2snake($init_entity->getName()).$init_query->getTableAliasPostfix(); $filter = array(); // add primary linking with main query foreach ($init_entity->getPrimaryArray() as $primary) { $filter['='.$primary] = new CSQLWhereExpression('?#', $init_table_alias.'.'.$primary); } // add value filter $filter[CSQLWhere::getOperationByCode($operation).$chain->getDefinition()] = $value; // build subquery $query_class = __CLASS__; $sub_query = new $query_class($init_entity); $sub_query->setFilter($filter); $sub_query->setTableAliasPostfix('_sub'); return 'EXISTS(' . $sub_query->getQuery() . ')'; }; }*/ $fields[$definition] = array('TABLE_ALIAS' => 'table', 'FIELD_NAME' => $sqlDefinition, 'FIELD_TYPE' => $field_type, 'MULTIPLE' => '', 'JOIN' => '', 'CALLBACK' => $callback); } if (is_array($filter_match)) { $fields = array_merge($fields, $this->getFilterCswFields($filter_match)); } } return $fields; }
/** * @param $elem * @param $select * @param $is_init_entity_aggregated * @param $fList * @param Entity\QueryChain[] $fChainList * @param $helper_class * @param Entity\Base $entity * * @return array */ public static function prepareSelectViewElement($elem, $select, $is_init_entity_aggregated, $fList, $fChainList, $helper_class, Entity\Base $entity) { $result = null; $alias = null; if (empty($elem['aggr']) && !strlen($elem['prcnt'])) { $result = $elem['name']; } else { $expression = ''; /** @var Entity\Field $field */ $field = $fList[$elem['name']]; $chain = $fChainList[$elem['name']]; $alias = $chain->getAlias(); $dataType = call_user_func(array($helper_class, 'getFieldDataType'), $field); if (!empty($elem['aggr'])) { $alias = $elem['aggr'] . '_' . $alias; if ($dataType == 'boolean') { // sum int for boolean global $DB; /** @var Entity\BooleanField $field */ $trueValue = $field->normalizeValue(true); $localDef = 'CASE WHEN %s = \'' . $DB->ForSql($trueValue) . '\' THEN 1 ELSE 0 END'; } else { $localDef = '%s'; } if ($elem['aggr'] == 'COUNT_DISTINCT') { $dataType = 'integer'; $expression = array('COUNT(DISTINCT ' . $localDef . ')', $elem['name']); } else { if ($dataType == 'boolean') { $dataType = 'integer'; } if ($elem['aggr'] == 'GROUP_CONCAT') { $expression = array($localDef, $elem['name']); } else { $expression = array($elem['aggr'] . '(' . $localDef . ')', $elem['name']); } } // pack 1:N aggregations into subquery if ($chain->hasBackReference() && $elem['aggr'] != 'GROUP_CONCAT') { $confirm = call_user_func_array(array($helper_class, 'confirmSelectBackReferenceRewrite'), array(&$elem, $chain)); if ($confirm) { $filter = array(); foreach ($entity->GetPrimaryArray() as $primary) { $filter['=' . $primary] = new CSQLWhereExpression('?#', ToLower($entity->getCode()) . '.' . $primary); } $query = new Entity\Query($entity); $query->addSelect(new Entity\ExpressionField('X', $expression[0], $elem['name'])); $query->setFilter($filter); $query->setTableAliasPostfix('_sub'); $expression = array('(' . $query->getQuery() . ')'); // double aggregation if init entity aggregated if ($is_init_entity_aggregated) { if ($elem['aggr'] == 'COUNT_DISTINCT') { $expression[0] = 'SUM(' . $expression[0] . ')'; } else { $expression[0] = $elem['aggr'] . '(' . $expression[0] . ')'; } } } // confirmed } } if (strlen($elem['prcnt'])) { $alias = $alias . '_PRCNT'; $dataType = 'integer'; if ($elem['prcnt'] == 'self_column') { if (empty($expression)) { $expression = array('%s', $elem['name']); } } else { if (empty($expression)) { $localDef = '%s'; $localMembers = array($elem['name']); } else { $localDef = $expression[0]; $localMembers = array_slice($expression, 1); } list($remoteAlias, $remoteSelect) = self::prepareSelectViewElement($select[$elem['prcnt']], $select, $is_init_entity_aggregated, $fList, $fChainList, $helper_class, $entity); if (is_array($remoteSelect) && !empty($remoteSelect['expression'])) { // remote field is expression $remoteDef = $remoteSelect['expression'][0]; $remoteMembers = array_slice($remoteSelect['expression'], 1); $alias = $alias . '_FROM_' . $remoteAlias; } else { // remote field is usual field $remoteDef = '%s'; $remoteMembers = array($remoteSelect); $remoteAlias = Entity\QueryChain::getAliasByDefinition($entity, $remoteSelect); $alias = $alias . '_FROM_' . $remoteAlias; } $exprDef = '(' . $localDef . ') / (' . $remoteDef . ') * 100'; $expression = array_merge(array($exprDef), $localMembers, $remoteMembers); // 'ROUND(STATUS / ID * 100)' // 'ROUND( (EX1(F1, F2)) / (EX2(F3, F1)) * 100)', // F1, F2, F3, F1 } } $result = array('data_type' => $dataType, 'expression' => $expression); } return array($alias, $result); }