public static function prepareTableSections4Statement(AbstractSQLDataSourceQueryHandler $datasourceHandler, DataControllerCallContext $callcontext, array &$requestedColumnNames = NULL, $alwaysJoin = FALSE) { $tableSections = NULL; $tableQuarters = self::prepareTableSection($datasourceHandler, $callcontext, $requestedColumnNames); $isQuarterRequired = isset($tableQuarters->columns); $tableDef = self::prepareDefTableSection($datasourceHandler, $callcontext, $requestedColumnNames); $isQuarterDefRequired = isset($tableDef->columns); $yearTableSections = DateDimensionYearDatasetAssembler::prepareTableSections4Statement($datasourceHandler, $callcontext, $requestedColumnNames, FALSE); $isYearRequired = isset($yearTableSections); if ($alwaysJoin || $isQuarterRequired || $isQuarterDefRequired || $isYearRequired) { $tableSections[] = $tableQuarters; if ($isQuarterDefRequired) { $tableDef->conditions[] = new JoinConditionSection('quarter_def_id', new TableColumnConditionSectionValue(self::$TABLE_ALIAS_SUFFIX__QUARTERS, 'quarter_def_id')); $tableSections[] = $tableDef; } if ($isYearRequired) { $tableYears = $yearTableSections[0]; $tableYears->conditions[] = new JoinConditionSection('year_id', new TableColumnConditionSectionValue(self::$TABLE_ALIAS_SUFFIX__QUARTERS, 'year_id')); ArrayHelper::mergeArrays($tableSections, $yearTableSections); } } return $tableSections; }
protected static function prepareStatement_Nnn2Dec(AbstractSQLDataSourceQueryHandler $datasourceHandler, DataControllerCallContext $callcontext, $fromMonthNumber, array &$requestedColumnNames = NULL, $alwaysJoin = FALSE) { $statement = new Statement(); $tableMonth = self::prepareTableSection($datasourceHandler, $callcontext, $requestedColumnNames); $isMonthRequired = isset($tableMonth->columns); $tableMonthDef = self::prepareDefTableSection($datasourceHandler, $callcontext, $requestedColumnNames); $isMonthDefRequired = isset($tableMonthDef->columns); $tableQuarters = DateDimensionQuarterDatasetAssembler::prepareTableSection($datasourceHandler, $callcontext, $requestedColumnNames); $isQuarterRequired = isset($tableQuarters->columns); $tableQuarterDef = DateDimensionQuarterDatasetAssembler::prepareDefTableSection($datasourceHandler, $callcontext, $requestedColumnNames); $isQuarterDefRequired = isset($tableQuarterDef->columns); // fiscal year $fiscalYearTableSections = DateDimensionYearDatasetAssembler::prepareTableSections4Statement($datasourceHandler, $callcontext, $requestedColumnNames, $isQuarterRequired); $isFiscalYearRequired = isset($fiscalYearTableSections); // calendar year $calendarYearTableSections = DateDimensionYearDatasetAssembler::prepareTableSections4Statement($datasourceHandler, $callcontext, $requestedColumnNames, $isQuarterRequired || $isFiscalYearRequired); $isCalendarYearRequired = isset($calendarYearTableSections); // we cannot link with quarters without querter def $isQuarterDefRequired = $isQuarterDefRequired || $isQuarterRequired; // we do not check for ($alwaysJoin || $isMonthRequired || $isMonthDefRequired) because if only those flags are true // all the work was done in prepareStatement_Jan2Nnn() and this function does not have to do anything in addition if ($isQuarterRequired || $isQuarterDefRequired || $isCalendarYearRequired || $isFiscalYearRequired) { // linking with quarter def is done through month def // Also we need month def for fiscal quarter // Also we need month def to select required months for BETWEEN $isMonthDefRequired = TRUE; if ($isCalendarYearRequired) { $tableMonth->event_updateTableAlias(DateDimensionYearDatasetAssembler::$TABLE_ALIAS_SUFFIX__YEARS, DateDimensionYearDatasetAssembler::$TABLE_ALIAS_PREFIX__CALENDAR . DateDimensionYearDatasetAssembler::$TABLE_ALIAS_SUFFIX__YEARS); } $statement->tables[] = $tableMonth; if ($isMonthDefRequired) { $tableMonthDef->conditions[] = new JoinConditionSection('month_def_id', new TableColumnConditionSectionValue(self::$TABLE_ALIAS_SUFFIX__MONTHS, 'month_def_id')); $statement->tables[] = $tableMonthDef; } if ($isQuarterDefRequired) { $tableQuarterDef->conditions[] = new JoinConditionSection('series', new ExactConditionSectionValue(' = ((' . ColumnStatementCompositeEntityParser::assembleColumnName(self::$TABLE_ALIAS_SUFFIX__MONTH_DEF . '.series') . " - {$fromMonthNumber}) DIV 3) + 1")); $statement->tables[] = $tableQuarterDef; } if ($isCalendarYearRequired) { $tableYears = $calendarYearTableSections[0]; $tableYears->conditions[] = new JoinConditionSection('year_id', new TableColumnConditionSectionValue(self::$TABLE_ALIAS_SUFFIX__MONTHS, 'year_id')); foreach ($calendarYearTableSections as $table) { $table->event_updateTableAlias($table->alias, DateDimensionYearDatasetAssembler::$TABLE_ALIAS_PREFIX__CALENDAR . $table->alias); } ArrayHelper::mergeArrays($statement->tables, $calendarYearTableSections); } if ($isFiscalYearRequired) { // adjusting alias for all year table sections foreach ($fiscalYearTableSections as $table) { $table->event_updateTableAlias($table->alias, DateDimensionYearDatasetAssembler::$TABLE_ALIAS_PREFIX__FISCAL . $table->alias); } $tableYears = $fiscalYearTableSections[0]; $tableYears->conditions[] = new JoinConditionSection('entry_year', new ExactConditionSectionValue(' = ' . ColumnStatementCompositeEntityParser::assembleColumnName(DateDimensionYearDatasetAssembler::$TABLE_ALIAS_PREFIX__CALENDAR . DateDimensionYearDatasetAssembler::$TABLE_ALIAS_SUFFIX__YEARS . '.entry_year') . ' + 1')); ArrayHelper::mergeArrays($statement->tables, $fiscalYearTableSections); } if ($isQuarterRequired) { $tableQuarters->conditions[] = new JoinConditionSection('quarter_def_id', new TableColumnConditionSectionValue(DateDimensionQuarterDatasetAssembler::$TABLE_ALIAS_SUFFIX__QUARTER_DEF, 'quarter_def_id')); $tableQuarters->conditions[] = new JoinConditionSection('year_id', new TableColumnConditionSectionValue(DateDimensionYearDatasetAssembler::$TABLE_ALIAS_SUFFIX__YEARS, 'year_id')); $tableQuarters->event_updateTableAlias(DateDimensionYearDatasetAssembler::$TABLE_ALIAS_SUFFIX__YEARS, DateDimensionYearDatasetAssembler::$TABLE_ALIAS_PREFIX__FISCAL . DateDimensionYearDatasetAssembler::$TABLE_ALIAS_SUFFIX__YEARS); $statement->tables[] = $tableQuarters; } $statement->conditions[] = new WhereConditionSection(self::$TABLE_ALIAS_SUFFIX__MONTH_DEF, 'series', new ExactConditionSectionValue(" BETWEEN {$fromMonthNumber} AND 12")); } return $statement; }