public function generateStatement(AbstractSQLDataSourceQueryHandler $datasourceHandler, DataControllerCallContext $callcontext, AbstractCubeQueryRequest $request) { $statement = $this->prepareSelectedCubeQueryStatement($datasourceHandler, $callcontext, $request); if (!isset($request->referencedRequests)) { return $statement; } $combinedStatement = new Statement(); $metamodel = data_controller_get_metamodel(); $cubeName = $request->getCubeName(); $cube = $metamodel->getCube($cubeName); $datasetMappedCubeRequests = array($cube->factsDatasetName => $request); // preparing list of reference paths $referencePaths = NULL; foreach ($request->referencedRequests as $referencedRequest) { $referencedCubeName = $referencedRequest->getCubeName(); $referencedCube = $metamodel->getCube($referencedCubeName); $referencedDatasetName = $referencedCube->factsDatasetName; $referencePath = ReferencePathHelper::assembleReference($referencedDatasetName, NULL); $referencePaths[$referencePath] = TRUE; // TRUE - required reference $datasetMappedCubeRequests[$referencedDatasetName] = $referencedRequest; } // finding ways to link the referenced cubes $linkBuilder = new ReferenceLinkBuilder(); $link = $linkBuilder->prepareReferenceBranches($cube->factsDatasetName, $referencePaths); // preparing primary cube aggregation statement list($isSubqueryRequired, $assembledPrimaryCubeAggregationSections) = $statement->prepareSections(NULL); $primaryCubeAggregationTableSection = new SubquerySection( Statement::assemble($isSubqueryRequired, NULL, $assembledPrimaryCubeAggregationSections, SelectStatementPrint::INDENT__SUBQUERY, FALSE), self::$TABLE_ALIAS__REFERENCED . $link->linkId); // adding columns which are returned by primary aggregation foreach ($statement->tables as $table) { if (!isset($table->columns)) { continue; } foreach ($table->columns as $column) { if (!$column->visible) { continue; } $primaryCubeAggregationTableSection->columns[] = new ColumnSection($column->alias); } } // registering primary cube statement in resulting statement $combinedStatement->tables[] = $primaryCubeAggregationTableSection; $this->prepareReferencedCubeQueryStatement($datasourceHandler, $callcontext, $combinedStatement, $datasetMappedCubeRequests, $link); return $combinedStatement; }
protected function countRecords(DataControllerCallContext $callcontext, DataSourceMetaData $datasource, array $statements) { $countIdentifier = 'record_count'; $TABLE_ALIAS__COUNT = 'c'; $count = count($statements); $requestedColumnNames = array(); // No columns are requested $sql = ''; for ($i = 0; $i < $count; $i++) { $statement = $statements[$i]; list($isSubqueryRequired, $assembledSections) = $statement->prepareSections($requestedColumnNames); if ($i > 0) { $sql .= "\n UNION\n"; } $sql .= $isSubqueryRequired ? "SELECT COUNT(*) AS {$countIdentifier}\n FROM (" . Statement::assemble(FALSE, NULL, $assembledSections, Statement::$INDENT_SUBQUERY, FALSE) . ') ' . $TABLE_ALIAS__COUNT : Statement::assemble(FALSE, NULL, new AssembledSections("COUNT(*) AS {$countIdentifier}", $assembledSections->from, $assembledSections->where, $assembledSections->groupBy, $assembledSections->having)); } if ($count > 1) { $tableAlias = $TABLE_ALIAS__COUNT . '_sum'; $sql = "SELECT SUM({$tableAlias}.{$countIdentifier}) AS {$countIdentifier}\n FROM (" . StringHelper::indent($sql, Statement::$INDENT_SUBQUERY, TRUE) . ") {$tableAlias}"; } LogHelper::log_info(new StatementLogMessage('*.count', $sql)); $records = $this->executeQuery($callcontext, $datasource, $sql, new PassthroughResultFormatter()); return $records[0][$countIdentifier]; }
public static function prepareTableSections4Statement(AbstractSQLDataSourceQueryHandler $datasourceHandler, DataControllerCallContext $callcontext, array &$requestedColumnNames = NULL, $alwaysJoin = FALSE) { $tableSections = NULL; if (DateDimensionConfiguration::$FISCAL_YEAR_FIRST_MONTH == 1) { // we do not need any specific support for fiscal year $statement = self::prepareStatement_Jan2Nnn($datasourceHandler, $callcontext, 12, $requestedColumnNames, $alwaysJoin); // it is expected that only 'tables' section is populated in the statement ArrayHelper::mergeArrays($tableSections, $statement->tables); } else { // we need another copy of requested column names for second request $copyOfRequestedColumnNames = $requestedColumnNames; // preparing configurations .. $statements = array(self::prepareStatement_Jan2Nnn($datasourceHandler, $callcontext, DateDimensionConfiguration::$FISCAL_YEAR_FIRST_MONTH - 1, $requestedColumnNames, $alwaysJoin), self::prepareStatement_Nnn2Dec($datasourceHandler, $callcontext, DateDimensionConfiguration::$FISCAL_YEAR_FIRST_MONTH, $copyOfRequestedColumnNames, $alwaysJoin)); // we need to prepare list of column names to assemble SQL $assemblableColumnNames = NULL; // combining configurations $sql = ''; for ($i = 0, $count = count($statements); $i < $count; $i++) { $statement = $statements[$i]; // checking if any tables were selected to provide support for this request's columns if (!isset($statement->tables)) { continue; } // we need to add columns only from first eligible statement $addAssemblableColumnNames = !isset($assemblableColumnNames); foreach ($statement->tables as $table) { if (isset($table->columns)) { if ($addAssemblableColumnNames) { foreach ($table->columns as $column) { if ($column->visible) { $assemblableColumnNames[] = $column->alias; } } } } else { $table->columns = array(); // We do not need any columns } } list($isSubqueryRequired, $assembledSections) = $statement->prepareSections($assemblableColumnNames); if (strlen($sql) > 0) { $sql .= "\n" . str_pad('', self::$INDENT_FISCAL_SUBQUERY) . " UNION\n"; } $sql .= Statement::assemble($isSubqueryRequired, $assemblableColumnNames, $assembledSections, self::$INDENT_FISCAL_SUBQUERY, $i > 0); } if (strlen($sql) > 0) { $subquerySection = new SubquerySection($sql, DateDimensionYearDatasetAssembler::$TABLE_ALIAS_PREFIX__FISCAL . self::$TABLE_ALIAS_SUFFIX__MONTHS); // adding visible columns from first statement as invisible column in this subquery foreach ($assemblableColumnNames as $assemblableColumnName) { $subqueryColumn = new ColumnSection($assemblableColumnName, $assemblableColumnName); // FIXME do not use subquery. Use a list of statements as one section. Assemble when it is time // $subqueryColumn->visible = FALSE; $subquerySection->columns[] = $subqueryColumn; } $tableSections[] = $subquerySection; } } return $tableSections; }