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