Examples of it.eng.spagobi.tools.dataset.common.query.GroupByQueryTransformer

      
      UserProfile userProfile = (UserProfile)getEnv().get(EngineConstants.ENV_USER_PROFILE);
      
      
      // STEP 3: transform the sql query
      GroupByQueryTransformer transformer = new GroupByQueryTransformer();
      List selectFields = SqlUtils.getSelectFields(sqlQuery);
      
      for(int i = 0; i < groupFields.length(); i++) {
        String groupByField = groupFields.getString(i);
        int fieldIndex = query.getSelectFieldIndex(groupByField);    
        Assert.assertTrue(fieldIndex >= 0 && fieldIndex <selectFields.size(), "Impossible to group on field [" + groupByField + "]");
        String[] f = (String[])selectFields.get(fieldIndex);        
        transformer.addGrouByColumn(f[1]!=null? f[1]:f[0], query.getSelectFieldByIndex(fieldIndex).getAlias());
      }
      
      // count column
      transformer.addAggregateColumn("*"/*f[1]!=null? f[1]:f[0]*/, "COUNT", "Records");
      
      // aggregate measures
      List dataMartSelectFields = query.getDataMartSelectFields(true);
      Iterator it = dataMartSelectFields.iterator();
      while (it.hasNext()) {
        DataMartSelectField field = (DataMartSelectField) it.next();
        int fieldIndex = query.getSelectFieldIndex(field.getUniqueName());        
        String[] f = (String[])selectFields.get(fieldIndex);
        IAggregationFunction aggregationFunction = field.getFunction();
        if (aggregationFunction != null && aggregationFunction != AggregationFunctions.NONE_FUNCTION) {
          transformer.addAggregateColumn(f[1]!=null? f[1]:f[0], aggregationFunction.getName(), field.getAlias());
        }
      }
      
      sqlQuery = (String)transformer.transformQuery(sqlQuery);
      
      // STEP 4: execute the query
      
      try {
        logger.debug("Executing query: [" + sqlQuery + "]");

        //Custom hierarchy: 
        //Group by the column associated to the selected level into the custom hierarchy.
        //Apply to each measure column the aggregation function specified in the metadata 
        //configuration block if any. Otherways use SUM.
        
        GroupByQueryTransformer queryGroupByTransformer;
        
        queryGroupByTransformer = new GroupByQueryTransformer();
        
        // group on the column associated to the selected level into the selected hierarchy
        String aggregationColumnName = datamartProvider.getSelectedLevel().getColumnId(); 
        queryGroupByTransformer.addGrouByColumn( aggregationColumnName );
        
        // aggregate on the column defined as measure in the metadata configuration block
        it = measureColumnNames.iterator();
        while(it.hasNext()) {
          String measureColumnName= (String)it.next();
          String aggregationFunction = null;
          
          aggregationFunction = datamartProvider.getMetaData().getAggregationFunction(measureColumnName);
          if( StringUtilities.isEmpty(aggregationFunction) ) {
            aggregationFunction = "SUM";
          }
          
          queryGroupByTransformer.addAggregateColumn(measureColumnName, aggregationFunction);
        }
        
        queryGroupByTransformer.setPreviousTransformer( this.getPreviousTransformer() );
          
        queryDrillTransformer = queryGroupByTransformer;
      } else {
 
        // ==============================================================
        // STEP1: Normalize the given query in order to have only one row 
        // in the results set for each member in the selected level
        // --------------------------------------------------------------
        
        GroupByQueryTransformer normalizationQueryTransformer;
        
        normalizationQueryTransformer = new GroupByQueryTransformer();
        
        // group on the column in the given query that refers to the geographical dimension table
        String aggregationColumnName = datamartProvider.getMetaData().getGeoIdColumnName( datamartProvider.getSelectedHierarchy().getName() ); 
        normalizationQueryTransformer.addGrouByColumn( aggregationColumnName );
        
        // aggregate on the column defined as measure in the metadata configuration block
        it = measureColumnNames.iterator();
        while(it.hasNext()) {
          String measureColumnName= (String)it.next();
          String aggregationFunction = null;
          
          aggregationFunction = datamartProvider.getMetaData().getAggregationFunction(measureColumnName);
          if( StringUtilities.isEmpty(aggregationFunction) ) {
            aggregationFunction = "SUM";
          }
          
          normalizationQueryTransformer.addAggregateColumn(measureColumnName, aggregationFunction);
        }
                  
        //normalizedSubQuery = (String)normalizationQueryTransformer.transformStatment(query);
        // ==========================================================
        
        // ==============================================================
        // STEP2: get the query used to retrive data from the geographical 
        // dimension table
        // --------------------------------------------------------------
        String dimGeoQuery = getDimGeoQuery();
        // ==========================================================
        
        
        // ==============================================================
        // STEP3: join the two query generated at setp 1 & 2
        // --------------------------------------------------------------
        JoinQueryTransformer joinQueryTransformer;
        
        joinQueryTransformer = new JoinQueryTransformer();
        
        // select the column associated to the selected level into the selected hierarchy
        String aggColumnName = datamartProvider.getSelectedLevel().getColumnId();        
        joinQueryTransformer.addSelectColumn(aggColumnName);
        
        // select all columns defined as measure in the metadata configuration block
        it = measureColumnNames.iterator();
        while(it.hasNext()) {
          String measureColumnName= (String)it.next();
          joinQueryTransformer.addSelectColumn( measureColumnName);
        }
        
        //  set as joining query the one generated during step 2        
        joinQueryTransformer.setStatmentToJoin( dimGeoQuery );
        
        // define the join condition
        joinQueryTransformer.setJoinCondition(
            datamartProvider.getMetaData().getGeoIdColumnName( datamartProvider.getSelectedHierarchy().getName() ), 
            baseLevel.getColumnId()
        );
        
        //joinedQuery = joinQueryTransformer.transformStatment( normalizedSubQuery );
        // ==========================================================
        
        
        // ==============================================================
        // STEP4:
        // --------------------------------------------------------------
        GroupByQueryTransformer normalizeJoinedQueryTransformer;
        
        normalizeJoinedQueryTransformer = new GroupByQueryTransformer();
        
        normalizeJoinedQueryTransformer.addGrouByColumn( datamartProvider.getSelectedLevel().getColumnId() );
        
        // aggregate on the column defined as measure in the metadata configuration block
        it = measureColumnNames.iterator();
        while(it.hasNext()) {
          String measureColumnName= (String)it.next();
          String aggregationFunction = null;
          
          aggregationFunction = datamartProvider.getMetaData().getAggregationFunction(measureColumnName);
          if( StringUtilities.isEmpty(aggregationFunction) ) {
            aggregationFunction = "SUM";
          }
          
          normalizeJoinedQueryTransformer.addAggregateColumn(measureColumnName, aggregationFunction);
        }
        
        //aggragateQuery = (String)normalizeJoinedQueryTransformer.transformStatment(joinedQuery);
        // ==========================================================     
        
        normalizeJoinedQueryTransformer.setPreviousTransformer(joinQueryTransformer);
        joinQueryTransformer.setPreviousTransformer(normalizationQueryTransformer);
        normalizationQueryTransformer.setPreviousTransformer( this.getPreviousTransformer() );
          
          queryDrillTransformer = normalizeJoinedQueryTransformer;
      }

Examples of it.eng.spagobi.tools.dataset.common.query.GroupByQueryTransformer

Related Classes of it.eng.spagobi.tools.dataset.common.query.GroupByQueryTransformer