protected function assembleConnectedDatasetSourceStatement(DataControllerCallContext $callcontext, ReferenceLink $link, array $columnNames, array $linkExecutionStack = NULL)
{
$TABLE_ALIAS__LINK = 'l';
$nestedLinkExecutionStack = $linkExecutionStack;
$nestedLinkExecutionStack[] = $link;
$selectedColumnNames = ReferenceLinkBuilder::selectReferencedColumnNames4ReferenceLink($nestedLinkExecutionStack, $columnNames);
$linkTableAliasPrefix = $TABLE_ALIAS__LINK . $link->linkId;
$statement = $this->assembleDatasetSourceStatement($callcontext, $link->dataset, $selectedColumnNames);
$statement->addTableAliasPrefix($linkTableAliasPrefix);
// adding columns which we use to join with parent dataset
if (!$link->isRoot()) {
foreach ($link->columnNames as $columnName) {
$joinColumnAlias = ReferencePathHelper::assembleDatabaseColumnName($this->getMaximumEntityNameLength(), ReferencePathHelper::assembleReference($link->dataset->name, $columnName));
$joinTable = $statement->findColumnTable($columnName);
if (!isset($joinTable)) {
$joinTable = $statement->tables[0];
}
$joinColumn = $joinTable->findColumnByAlias($joinColumnAlias);
if (!isset($joinColumn)) {
$joinTable->columns[] = new ColumnSection($columnName, $joinColumnAlias);
}
}
}
// adding columns which we use to join with nested datasets
if (isset($link->nestedLinks)) {
foreach ($link->nestedLinks as $nestedLink) {
foreach ($nestedLink->parentColumnNames as $parentColumnName) {
$parentColumnAlias = ReferencePathHelper::assembleDatabaseColumnName($this->getMaximumEntityNameLength(), ReferencePathHelper::assembleReference($link->dataset->name, $parentColumnName));
$joinTable = $statement->getColumnTable($parentColumnName);
$joinColumn = $joinTable->findColumnByAlias($parentColumnAlias);
if (!isset($joinColumn)) {
$joinTable->columns[] = new ColumnSection($parentColumnName, $parentColumnAlias);
}
}
}
}
// collecting columns which we need to mark as invisible
$shouldBeInvisibleColumns = NULL;
foreach ($statement->tables as $table) {
if (isset($table->columns)) {
foreach ($table->columns as $column) {
$shouldBeInvisibleColumns[$table->alias][$column->alias] = $column;
}
} else {
$table->columns = array();
// We do not need any columns
}
}
// adding or making as visible columns which we need to return
if (isset($selectedColumnNames)) {
foreach ($selectedColumnNames as $originalColumnName => $selectedColumnName) {
// we need to show only those columns which are requested.
// All intermediate columns (which are used to link with nested datasets) will not be shown
if (array_search($originalColumnName, $columnNames) !== FALSE) {
$databaseColumnName = ReferencePathHelper::assembleDatabaseColumnName($this->getMaximumEntityNameLength(), $originalColumnName);
$table = $statement->getColumnTable($selectedColumnName, TRUE);
$column = $table->findColumnByAlias($databaseColumnName);
if (isset($column)) {
$column->visible = TRUE;
unset($shouldBeInvisibleColumns[$table->alias][$column->alias]);
} else {
$column = $table->findColumnByAlias($selectedColumnName);
if (isset($column)) {
// adding clone of the same column with another alias
$column = clone $column;
$column->visible = TRUE;
$column->alias = $databaseColumnName;
} else {
$column = new ColumnSection($selectedColumnName, $databaseColumnName);
}
$table->columns[] = $column;
}
$callcontext->columnMapping[$databaseColumnName] = $originalColumnName;
}
}
}
if (isset($shouldBeInvisibleColumns)) {
foreach ($shouldBeInvisibleColumns as $tableColumns) {
foreach ($tableColumns as $column) {
$column->visible = FALSE;
}
}
}
// supporting nested links
if (isset($link->nestedLinks)) {
foreach ($link->nestedLinks as $nestedLink) {
$nestedStatement = $this->assembleConnectedDatasetSourceStatement($callcontext, $nestedLink, $columnNames, $nestedLinkExecutionStack);
foreach ($nestedLink->parentColumnNames as $referencePointColumnIndex => $parentColumnName) {
// preparing parent table alias
$parentColumnAlias = ReferencePathHelper::assembleDatabaseColumnName($this->getMaximumEntityNameLength(), ReferencePathHelper::assembleReference($link->dataset->name, $parentColumnName));
$parentTableAlias = $statement->getColumnTable($parentColumnAlias)->alias;
// linking with parent
$nestedColumnName = $nestedLink->columnNames[$referencePointColumnIndex];
$nestedColumnAlias = ReferencePathHelper::assembleDatabaseColumnName($this->getMaximumEntityNameLength(), ReferencePathHelper::assembleReference($nestedLink->dataset->name, $nestedColumnName));
$nestedStatement->getColumnTable($nestedColumnAlias)->conditions[] = new JoinConditionSection($nestedColumnName, new TableColumnConditionSectionValue($parentTableAlias, $parentColumnName));
}
$statement->merge($nestedStatement);
}
}
return $statement;
}
public function formatPropertyNameAsDatabaseColumnName($maximumLength)
{
return ReferencePathHelper::assembleDatabaseColumnName($maximumLength, $this->propertyName);
}
protected function prepareReferencedCubeQueryStatement(AbstractSQLDataSourceQueryHandler $datasourceHandler, DataControllerCallContext $callcontext, Statement $combinedStatement, array $datasetMappedCubeRequests, ReferenceLink $link)
{
if (!isset($link->nestedLinks)) {
return;
}
$metamodel = data_controller_get_metamodel();
foreach ($link->nestedLinks as $referencedLink) {
$referencedRequest = NULL;
if (isset($datasetMappedCubeRequests[$referencedLink->dataset->name])) {
$referencedRequest = clone $datasetMappedCubeRequests[$referencedLink->dataset->name];
} else {
// checking if there is corresponding cube for the referenced dataset
$possibleReferencedCube = $metamodel->findCubeByDatasetName($referencedLink->dataset->name);
if (isset($possibleReferencedCube)) {
$referencedRequest = new CubeQueryRequest($possibleReferencedCube->name);
$referencedRequest->referenced = TRUE;
$datasetMappedCubeRequests[$possibleReferencedCube->sourceDatasetName] = $referencedRequest;
}
}
if (isset($referencedRequest)) {
// preparing parent cube
$parentRequest = $datasetMappedCubeRequests[$link->dataset->name];
$parentCubeName = $parentRequest->getCubeName();
$parentCube = $metamodel->getCube($parentCubeName);
// preparing referenced cube
$referencedCubeName = $referencedRequest->getCubeName();
$referencedCube = $metamodel->getCube($referencedCubeName);
// adding required dimensions
$joinConditions = NULL;
foreach ($referencedLink->parentColumnNames as $columnIndex => $parentColumnName) {
// looking for a dimension in parent cube
list($parentDimension, ) = $parentCube->getDimensionAndLevelIndexBySourceColumnName($parentColumnName);
// looking for a dimension in referenced cube
$referencedColumnName = $referencedLink->columnNames[$columnIndex];
list($referencedDimension, ) = $referencedCube->getDimensionAndLevelIndexBySourceColumnName($referencedColumnName);
// checking if this dimension is part of query portion of parent request
$parentRequestDimensionQuery = $parentRequest->findDimensionQuery($parentDimension->name);
if (isset($parentRequestDimensionQuery)) {
// preparing level used in this query
$parentRequestDimensionLevelIndex4Query = $parentDimension->getLevelIndex($parentRequestDimensionQuery->levelName);
// preparing level to be used for the query in referenced request (using the same level index as for parent dimension)
$referencedDimensionLevel4Query = $referencedDimension->levels[$parentRequestDimensionLevelIndex4Query];
// copying the query request to referenced cube
$referencedRequestDimensionQuery = new __CubeQueryRequest_DimensionQuery($referencedDimension->name, $referencedDimensionLevel4Query->name);
$referencedRequestDimensionQuery->values = $parentRequestDimensionQuery->values;
$referencedRequest->importDimensionQueryFrom($referencedRequestDimensionQuery);
}
// checking if there is a related query for parent column name
$parentRequestSourceDatasetQuery = $parentRequest->findSourceDatasetPropertyQuery($parentColumnName);
if (isset($parentRequestSourceDatasetQuery)) {
// copying the query request to referenced cube
$referencedRequest->addSourceDatasetPropertyQueryValues($referencedColumnName, $parentRequestSourceDatasetQuery->values);
}
// checking if this dimension is part of parent request
$parentRequestDimension = $parentRequest->findDimension($parentDimension->name);
if (!isset($parentRequestDimension)) {
// because this dimension is not in list of returned columns we should not use it to link with referenced cube
continue;
}
$selectedParentDimensionLevelIndex = $parentDimension->getLevelIndex($parentRequestDimension->levelName);
$selectedParentDimensionLevel = $parentDimension->levels[$selectedParentDimensionLevelIndex];
// preparing level from referenced cube (using the same level index as for parent dimension)
$selectedReferencedDimensionLevel = $referencedDimension->levels[$selectedParentDimensionLevelIndex];
$referencedRequest->addDimensionLevel(NULL, $referencedDimension->name, $selectedReferencedDimensionLevel->name);
$parentDatabaseColumnName = ParameterHelper::assembleDatabaseColumnName($datasourceHandler->getMaximumEntityNameLength(), $parentRequest->referenced ? ReferencePathHelper::assembleReference($parentCube->sourceDatasetName, $parentDimension->name) : $parentDimension->name, $selectedParentDimensionLevel->name);
$referencedDatabaseColumnName = ParameterHelper::assembleDatabaseColumnName($datasourceHandler->getMaximumEntityNameLength(), ReferencePathHelper::assembleReference($referencedCube->sourceDatasetName, $referencedDimension->name), $selectedReferencedDimensionLevel->name);
$joinConditions[] = new JoinConditionSection($referencedDatabaseColumnName, new TableColumnConditionSectionValue(self::$TABLE_ALIAS__REFERENCED . $link->linkId, $parentDatabaseColumnName));
}
if (!isset($joinConditions)) {
throw new IllegalArgumentException(t("There is no common columns to join '@datasetNameA' and '@datasetNameB' datasets", array('@datasetNameA' => $parentCube->publicName, '@datasetNameB' => $referencedCube->publicName)));
}
// preparing aggregation statement for referenced cube
$referencedAggregationStatement = $this->prepareSelectedCubeQueryStatement($datasourceHandler, $callcontext, $referencedRequest);
list($isSubqueryRequired, $assembledReferencedCubeSections) = $referencedAggregationStatement->prepareSections(NULL);
$referencedCubeSubquerySection = new SubquerySection(Statement::assemble($isSubqueryRequired, NULL, $assembledReferencedCubeSections, Statement::$INDENT_LEFT_OUTER_JOIN_SUBQUERY, FALSE), self::$TABLE_ALIAS__REFERENCED . $referencedLink->linkId);
// preparing columns which are returned by referenced aggregation
foreach ($referencedAggregationStatement->tables as $table) {
if (!isset($table->columns)) {
continue;
}
foreach ($table->columns as $column) {
if (!$column->visible) {
continue;
}
$referencedCubeSubquerySection->columns[] = new ColumnSection($column->alias);
}
}
// linking with parent cube
foreach ($joinConditions as $joinCondition) {
// we do not need to return columns which are used to join with parent cube
$referencedCubeSubquerySection->getColumn($joinCondition->subjectColumnName)->visible = FALSE;
$referencedCubeSubquerySection->conditions[] = $joinCondition;
}
// adding to resulting statement
$combinedStatement->tables[] = $referencedCubeSubquerySection;
// applying referenced cubes measure conditions on resulting statement as well
$measureQueries = $referencedRequest->findMeasureQueries();
if (isset($measureQueries)) {
foreach ($measureQueries as $measureQuery) {
$measureName = ReferencePathHelper::assembleReference($referencedCube->sourceDatasetName, $measureQuery->measureName);
$measureDatabaseColumnName = ReferencePathHelper::assembleDatabaseColumnName($datasourceHandler->getMaximumEntityNameLength(), $measureName);
foreach ($measureQuery->values as $measureValue) {
$combinedStatement->conditions[] = new WhereConditionSection(self::$TABLE_ALIAS__REFERENCED . $referencedLink->linkId, $measureDatabaseColumnName, new ExactConditionSectionValue($datasourceHandler->formatOperatorValue($callcontext, $referencedRequest, $referencedCube->sourceDatasetName, NULL, $measureValue)));
}
}
}
} else {
throw new UnsupportedOperationException(t('Cube joins using intermediate dataset is not supported yet'));
// preparing statement for intermediate dataset
$requiredColumnNames = $referencedLink->columnNames;
$referencedIntermediateDatasetStatement = $datasourceHandler->prepareDatasetSourceStatement($callcontext, $referencedLink->dataset, $requiredColumnNames);
// adding condition to join with parent statement
$referencedIntermediateDatasetTableSection = $referencedIntermediateDatasetStatement->tables[0];
foreach ($referencedLink->columnNames as $columnIndex => $referencedColumnName) {
$referencedDatabaseColumnName = $referencedColumnName;
$parentColumnName = $referencedLink->parentColumnNames[$columnIndex];
$parentDatabaseColumnName = $parentColumnName;
$referencedIntermediateDatasetTableSection->conditions[] = new JoinConditionSection($referencedDatabaseColumnName, new TableColumnConditionSectionValue(self::$TABLE_ALIAS__REFERENCED . $link->linkId, $parentDatabaseColumnName));
}
$combinedStatement->merge($referencedIntermediateDatasetStatement);
}
// recursively check nested levels
$this->prepareReferencedCubeQueryStatement($datasourceHandler, $callcontext, $combinedStatement, $datasetMappedCubeRequests, $referencedLink);
}
}
public function assemble(AbstractSQLDataSourceQueryHandler $datasourceHandler, DataControllerCallContext $callcontext, DatasetMetaData $dataset, array $columnNames = NULL)
{
$statement = new Statement();
$TABLE_ALIAS__FACTS = 'dsf';
$TABLE_ALIAS__COLUMN_JOIN = 'cj';
$metamodel = data_controller_get_metamodel();
$cubeName = $dataset->name;
$cube = $metamodel->getCube($cubeName);
$factsDataset = $metamodel->getDataset($cube->sourceDatasetName);
self::$TABLE_ALIAS_SUFFIX__SEQUENCE++;
$factsTable = new DatasetSection($factsDataset, $TABLE_ALIAS__FACTS . self::$TABLE_ALIAS_SUFFIX__SEQUENCE);
$statement->tables[] = $factsTable;
foreach ($dataset->getColumns() as $column) {
$columnName = $column->name;
$columnDefaultAlias = NULL;
if (isset($columnNames)) {
$columnDefaultAlias = array_search($columnName, $columnNames);
if ($columnDefaultAlias === FALSE) {
continue;
}
// fixing the alias if the list of columns is not associative array
if (is_int($columnDefaultAlias)) {
$columnDefaultAlias = NULL;
}
}
// FIXME the following code does not work in the following situation:
// 1) we need to query a dataset which is defined in GovDashboard using file upload
// 2) column names are not provided
// 3) for columns which are references to lookup, value from the primary key from lookup table is returned
// even if in definition of the dataset we have columns with the following application type: name@lookup, code @lookup and etc
$isReferenceUsed = FALSE;
$handler = DimensionLookupFactory::getInstance()->findHandler($column->type->applicationType);
if (isset($handler)) {
// FIXME remove this implementation when we implement 'Executable Tree' functionality
list($connectedDatasetName, $connectedColumnName) = $handler->adjustReferencePointColumn($metamodel, $factsDataset->name, $columnName);
if ($connectedDatasetName != $factsDataset->name || $connectedColumnName != $columnName) {
self::$TABLE_ALIAS_SUFFIX__SEQUENCE++;
$columnJoinAliasPrefix = $TABLE_ALIAS__COLUMN_JOIN . self::$TABLE_ALIAS_SUFFIX__SEQUENCE;
$connectedDataset = $metamodel->getDataset($connectedDatasetName);
$connectedDatasetKeyColumnName = $connectedDataset->getKeyColumn()->name;
// preparing list of columns we want to get from connected dataset
$connectedDatasetColumnNames = NULL;
ArrayHelper::addUniqueValue($connectedDatasetColumnNames, $connectedDatasetKeyColumnName);
ArrayHelper::addUniqueValue($connectedDatasetColumnNames, $connectedColumnName);
$connectedStatement = $datasourceHandler->assembleDatasetSourceStatement($callcontext, $connectedDataset, $connectedDatasetColumnNames);
$connectedStatement->addTableAliasPrefix($columnJoinAliasPrefix);
$connectedTable = $connectedStatement->getColumnTable($connectedColumnName);
// registering the column for facts table
$factsTableColumn = new ColumnSection($columnName, 'fact_' . $columnName);
$factsTableColumn->visible = FALSE;
$factsTable->columns[] = $factsTableColumn;
// adjusting key column from lookup
$connectedDatasetKeyColumn = $connectedTable->getColumn($connectedDatasetKeyColumnName);
$connectedDatasetKeyColumn->alias = 'lookup_' . $connectedDatasetKeyColumnName;
$connectedDatasetKeyColumn->visible = FALSE;
// adjusting value column from lookup
$connectedDatasetValueColumn = $connectedTable->getColumn($connectedColumnName);
$connectedDatasetValueColumn->alias = $columnName;
// the key column could be the same as value column
$connectedDatasetValueColumn->visible = TRUE;
// new value column which uses composite name as column alias
if (isset($columnDefaultAlias)) {
$connectedDatasetValueColumn2 = new ColumnSection($connectedColumnName, ReferencePathHelper::assembleDatabaseColumnName($datasourceHandler->getMaximumEntityNameLength(), $columnDefaultAlias));
$connectedDatasetValueColumn2->visible = FALSE;
$connectedTable->columns[] = $connectedDatasetValueColumn2;
}
// linking the lookup table with the facts table
$connectedTable->conditions[] = new JoinConditionSection($connectedDatasetKeyColumn->alias, new TableColumnConditionSectionValue($factsTable->alias, $columnName));
$statement->merge($connectedStatement);
$isReferenceUsed = TRUE;
}
}
if (!$isReferenceUsed) {
$factsTable->columns[] = new ColumnSection($columnName);
}
}
return $statement;
}
