public static function prepareQueryField(DuplicateCriterion $criterion, $entityTypeID, $entityID, $userID) { $typeID = $criterion->getIndexTypeID(); $matchHash = $criterion->getMatchHash(); $sql = 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); $sql[] = $query->getQuery(); $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); $sql[] = $query->getQuery(); return new Main\DB\SqlExpression(implode(' UNION ALL ', $sql)); }
/** @return array */ public function prepareEntityListFilter(array $filterParams) { $filter = self::internalizeFilter($filterParams); $query = new Query(DealInvoiceStatisticsTable::getEntity()); $query->addSelect('OWNER_ID'); $query->addGroup('OWNER_ID'); $period = $filter->getPeriod(); $periodStartDate = $period['START']; $periodEndDate = $period['END']; $query->addFilter('>=END_DATE', $periodStartDate); $query->addFilter('<=START_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 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'))); }
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; }
/** * This method is for internal use only. It may be changed without any notification further, or even mystically disappear. * * @access private */ public static function getSubtreeRangeSqlForNode($primary, $node = array()) { $primary = Assert::expectIntegerPositive($primary, '$primary'); if (empty($node)) { $node = self::getNodeInfo($primary); if (!intval($node['ID'])) { throw new Main\SystemException('Node being checked not found'); } } if (isset($node['LEFT_MARGIN']) && isset($node['RIGHT_MARGIN'])) { $query = new Main\Entity\Query(static::getEntity()); $query->setSelect(array('ID')); $query->setFilter(array('>LEFT_MARGIN' => $node['LEFT_MARGIN'], '<RIGHT_MARGIN' => $node['RIGHT_MARGIN'])); return $query->getQuery(); } else { throw new Main\SystemException('Node not found or incorrect node info passed'); } }
$_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); unset($fElem);
public static function getInstanceByQuery(Query $query, &$entity_name = null) { if ($entity_name === null) { $entity_name = 'Tmp' . randString(); } elseif (!preg_match('/^[a-z0-9_]+$/i', $entity_name)) { throw new Main\ArgumentException(sprintf('Invalid entity name `%s`.', $entity_name)); } $query_string = '(' . $query->getQuery() . ')'; $query_chains = $query->getChains(); $replaced_aliases = array_flip($query->getReplacedAliases()); // generate fieldsMap $fieldsMap = array('TMP_ID' => array('data_type' => 'integer', 'primary' => true)); foreach ($query->getSelect() as $k => $v) { if (is_array($v)) { // expression $fieldsMap[$k] = array('data_type' => $v['data_type']); } else { if ($v instanceof ExpressionField) { $fieldDefinition = $v->getName(); } else { $fieldDefinition = is_numeric($k) ? $v : $k; } // better to initialize fields as objects after entity is created $dataType = Field::getOldDataTypeByField($query_chains[$fieldDefinition]->getLastElement()->getValue()); $fieldsMap[$fieldDefinition] = array('data_type' => $dataType); } if (isset($replaced_aliases[$k])) { $fieldsMap[$k]['column_name'] = $replaced_aliases[$k]; } } // generate class content $eval = 'class ' . $entity_name . 'Table extends ' . __NAMESPACE__ . '\\DataManager {' . PHP_EOL; $eval .= 'public static function getMap() {' . PHP_EOL; $eval .= 'return ' . var_export($fieldsMap, true) . ';' . PHP_EOL; $eval .= '}'; $eval .= 'public static function getTableName() {' . PHP_EOL; $eval .= 'return ' . var_export($query_string, true) . ';' . PHP_EOL; $eval .= '}'; $eval .= '}'; eval($eval); return self::getInstance($entity_name); }
/** * This method is for internal use only. It may be changed without any notification further, or even mystically disappear. * * @access private */ public static function getSubtreeRangeSqlForNode($primary, $node = array()) { $primary = Assert::expectIntegerPositive($primary, '$primary'); if (empty($node)) { $node = self::getNodeInfo($primary); if (!intval($node['ID'])) { throw new Tree\NodeNotFoundException(false, array('INFO' => array('ID' => $primary))); } } static::checkNodeThrowException($node); $query = new Main\Entity\Query(static::getEntity()); $query->setSelect(array('ID')); $query->setFilter(array('>LEFT_MARGIN' => $node['LEFT_MARGIN'], '<RIGHT_MARGIN' => $node['RIGHT_MARGIN'])); return $query->getQuery(); }
public static function getConnectedEntitiesQuery($locationPrimary, $linkType = 'id', $parameters = array()) { if ($linkType == 'id') { $locationPrimary = Assert::expectIntegerPositive($locationPrimary, Loc::getMessage('SALE_LOCATION_CONNECTOR_ENTITY_LOCATION_PRIMARY_FLD_NAME')); } else { $locationPrimary = Assert::expectStringNotNull($locationPrimary, Loc::getMessage('SALE_LOCATION_CONNECTOR_ENTITY_LOCATION_PRIMARY_FLD_NAME')); } $useGroups = GroupTable::checkGroupUsage() && static::getUseGroups(); // check if we have groups in project and entity uses groups $useCodes = static::getUseCodes(); // this entity uses codes $groupUseCodes = GroupLocationTable::getUseCodes(); // group entity uses codes $typeFld = static::getTypeField(); /*LOCATION_TYPE*/ $linkFld = static::getLinkField(); /*DELIVERY_ID*/ $locationLinkFld = static::getLocationLinkField(); /*LOCATION_ID*/ $targetPrimaryFld = static::getTargetEntityPrimaryField(); /*ID*/ $groupLocationLinkFld = GroupLocationTable::getLocationLinkField(); /*LOCATION_ID*/ $groupLinkFld = GroupLocationTable::getLinkField(); /*LOCATION_GROUP_ID*/ $seachById = $linkType == 'id'; $dbConnection = Main\HttpApplication::getConnection(); if (!is_array($parameters)) { $parameters = array(); } if (is_array($parameters['runtime'])) { Assert::announceNotImplemented('Sorry, runtime clause is not implemented currently.'); } $order = array(); if (is_array($parameters['order'])) { Assert::announceNotImplemented('Sorry, order-over-union clause is not implemented currently.'); } $filter = array(); if (is_array($parameters['filter']) && !empty($parameters['filter'])) { $filter = $parameters['filter']; } $select = array('*'); if (is_array($parameters['select']) && !empty($parameters['select'])) { $select = $parameters['select']; } /* query example when working with delivery: select distinct D.* from b_sale_delivery D inner join b_sale_delivery2location DL on D.ID = DL.DELIVERY_ID and DL.LOCATION_TYPE = 'L' inner join b_sale_location L1 on L1.CODE = DL.LOCATION_ID inner join b_sale_location L2 on L2.ID(there will be CODE, if we search by code) = 65683 and L2.LEFT_MARGIN >= L1.LEFT_MARGIN and L2.RIGHT_MARGIN <= L1.RIGHT_MARGIN; */ $query = new Entity\Query(static::getTargetEntityName()); $DLCondition = array('=this.' . $targetPrimaryFld => 'ref.' . $linkFld); if ($useGroups) { $DLCondition['=ref.' . $typeFld] = array('?', self::DB_LOCATION_FLAG); } $query->registerRuntimeField('DL', array('data_type' => get_called_class(), 'reference' => $DLCondition, 'join_type' => 'inner'))->registerRuntimeField('L1', array('data_type' => '\\Bitrix\\Sale\\Location\\Location', 'reference' => array('=this.DL.' . $locationLinkFld => 'ref.' . ($useCodes ? 'CODE' : 'ID')), 'join_type' => 'inner'))->registerRuntimeField('L2', array('data_type' => '\\Bitrix\\Sale\\Location\\Location', 'reference' => array('=ref.' . ($seachById ? 'ID' : 'CODE') => array('?', $locationPrimary), '>=ref.LEFT_MARGIN' => 'this.L1.LEFT_MARGIN', '<=ref.RIGHT_MARGIN' => 'this.L1.RIGHT_MARGIN'), 'join_type' => 'inner'))->setSelect($select)->setFilter($filter)->setOrder($order); if (!$useGroups) { // emulate "select distinct" $query->setGroup($select); return $query->getQuery(); } else { $sqls = array($query->getQuery()); $query = new Entity\Query(static::getTargetEntityName()); /* query example when working with delivery: select D.* from b_sale_delivery D inner join b_sale_delivery2location DL on D.ID = DL.DELIVERY_ID and DL.LOCATION_TYPE = 'G' inner join b_sale_location_group G on G.CODE = DL.LOCATION_ID (if this entity uses ID, skip this join) inner join b_sale_grouplocation GL on GL.LOCATION_GROUP_ID = G.ID (if this entity uses ID, there will be DL.LOCATION_ID) inner join b_sale_location L1 on L1.ID (there will be CODE, if grouplocation entity uses CODE) = GL.LOCATION_ID inner join b_sale_location L2 on L2.ID (there will be CODE, if we seach by code) = 65683 and L2.LEFT_MARGIN >= L1.LEFT_MARGIN and L2.RIGHT_MARGIN <= L1.RIGHT_MARGIN; */ $query->registerRuntimeField('DL', array('data_type' => get_called_class(), 'reference' => array('=this.' . $targetPrimaryFld => 'ref.' . $linkFld, '=ref.' . $typeFld => array('?', self::DB_GROUP_FLAG)), 'join_type' => 'inner')); if ($useCodes) { $query->registerRuntimeField('G', array('data_type' => '\\Bitrix\\Sale\\Location\\Group', 'reference' => array('=this.DL.' . $locationLinkFld => 'ref.CODE'), 'join_type' => 'inner')); } $query->registerRuntimeField('GL', array('data_type' => '\\Bitrix\\Sale\\Location\\GroupLocation', 'reference' => array($useCodes ? '=this.G.ID' : '=this.DL.' . $locationLinkFld => 'ref.' . $groupLinkFld), 'join_type' => 'inner'))->registerRuntimeField('L1', array('data_type' => '\\Bitrix\\Sale\\Location\\Location', 'reference' => array('=this.GL.' . $groupLocationLinkFld => 'ref.' . ($groupUseCodes ? 'CODE' : 'ID')), 'join_type' => 'inner'))->registerRuntimeField('L2', array('data_type' => '\\Bitrix\\Sale\\Location\\Location', 'reference' => array('=ref.' . ($seachById ? 'ID' : 'CODE') => array('?', $locationPrimary), '>=ref.LEFT_MARGIN' => 'this.L1.LEFT_MARGIN', '<=ref.RIGHT_MARGIN' => 'this.L1.RIGHT_MARGIN'), 'join_type' => 'inner'))->setSelect($select)->setFilter($filter)->setOrder($order); $sqls[] = $query->getQuery(); return static::unionize($sqls); } }
/** * @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); }
/** @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'))); }