=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/DataQueryParams.java' --- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/DataQueryParams.java 2012-12-12 15:47:03 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/DataQueryParams.java 2012-12-14 15:04:48 +0000 @@ -44,6 +44,7 @@ { public static final String INDICATOR_DIM_ID = "in"; public static final String DATAELEMENT_DIM_ID = "de"; + public static final String CATEGORYOPTIONCOMBO_DIM_ID = "coc"; public static final String PERIOD_DIM_ID = "pe"; public static final String ORGUNIT_DIM_ID = "ou"; @@ -98,8 +99,6 @@ { SortedMap> map = new TreeMap>(); - // TODO convert indicators to data elements - map.put( DATAELEMENT_DIM_ID, dataElements ); map.put( ORGUNIT_DIM_ID, organisationUnits ); map.put( PERIOD_DIM_ID, periods ); @@ -115,6 +114,24 @@ return map; } + public List getDimensionNames() + { + List list = new ArrayList(); + + list.add( DATAELEMENT_DIM_ID ); + list.add( CATEGORYOPTIONCOMBO_DIM_ID ); + list.add( PERIOD_DIM_ID ); + list.add( ORGUNIT_DIM_ID ); + list.addAll( dimensions.keySet() ); + + return list; + } + + public List getDynamicDimensionNames() + { + return new ArrayList( dimensions.keySet() ); + } + public void setDimension( String dimension, List values ) { if ( DATAELEMENT_DIM_ID.equals( dimension ) ) === modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultAnalyticsService.java' --- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultAnalyticsService.java 2012-12-14 12:37:06 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultAnalyticsService.java 2012-12-14 15:04:48 +0000 @@ -45,6 +45,8 @@ public class DefaultAnalyticsService implements AnalyticsService { + private static final String VALUE_NAME = "value"; + //TODO period aggregation for multiple period types //TODO hierarchy aggregation for org units at multiple levels //TODO indicator aggregation @@ -59,11 +61,11 @@ Grid grid = new ListGrid(); - grid.addHeader( new GridHeader( "Data element", "de", String.class.getName(), false, true ) ); - grid.addHeader( new GridHeader( "Category option combo", "coc", String.class.getName(), false, true ) ); - grid.addHeader( new GridHeader( "Period", "de", String.class.getName(), false, true ) ); - grid.addHeader( new GridHeader( "Organisation unit", "ou", String.class.getName(), false, true ) ); - grid.addHeader( new GridHeader( "Value", "value", Double.class.getName(), false, false ) ); + for ( String col : params.getDimensionNames() ) + { + grid.addHeader( new GridHeader( col, false, true ) ); + grid.addHeader( new GridHeader( VALUE_NAME, false, false ) ); + } for ( Map.Entry entry : map.entrySet() ) { === modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/JdbcAnalyticsManager.java' --- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/JdbcAnalyticsManager.java 2012-12-14 12:37:06 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/JdbcAnalyticsManager.java 2012-12-14 15:04:48 +0000 @@ -27,9 +27,11 @@ * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. */ +import static org.hisp.dhis.system.util.TextUtils.getCommaDelimitedString; import static org.hisp.dhis.system.util.TextUtils.getQuotedCommaDelimitedString; import java.util.HashMap; +import java.util.List; import java.util.Map; import java.util.concurrent.Future; @@ -75,6 +77,8 @@ // Implementation // ------------------------------------------------------------------------- + //TODO optimize when all options in dimensions are selected + @Async public Future> getAggregatedDataValueTotals( DataQueryParams params ) { @@ -82,14 +86,28 @@ String periodType = PeriodType.getPeriodTypeFromIsoString( params.getPeriods().iterator().next() ).getName().toLowerCase(); - final String sql = - "SELECT dataelement, 0 as categoryoptioncombo, " + - periodType + " as period, uidlevel" + level + " as organisationunit, SUM(value) as value " + + List dimensions = params.getDimensionNames(); + List extraDimensions = params.getDynamicDimensionNames(); + + String sql = + "SELECT " + dimensions.get( 0 ) + ", " + + dimensions.get( 1 ) + ", " + + periodType + " as " + dimensions.get( 2 ) + ", " + + "uidlevel" + level + " as " + dimensions.get( 3 ) + ", " + + getCommaDelimitedString( extraDimensions, false, true ) + + "SUM(value) as value " + + "FROM " + params.getTableName() + " " + - "WHERE dataelement IN ( " + getQuotedCommaDelimitedString( params.getDataElements() ) + " ) " + + "WHERE " + dimensions.get( 0 ) + " IN ( " + getQuotedCommaDelimitedString( params.getDataElements() ) + " ) " + "AND " + periodType + " IN ( " + getQuotedCommaDelimitedString( params.getPeriods() ) + " ) " + "AND uidlevel" + level + " IN ( " + getQuotedCommaDelimitedString( params.getOrganisationUnits() ) + " ) " + - "GROUP BY dataelement, " + periodType + ", uidlevel" + level; + getExtraDimensionQuery( params ) + + + "GROUP BY " + dimensions.get( 0 ) + ", " + + dimensions.get( 1 ) + ", " + + periodType + ", " + + "uidlevel" + level + + getCommaDelimitedString( extraDimensions, true, false ); log.info( sql ); @@ -99,17 +117,38 @@ while ( rowSet.next() ) { - String key = - rowSet.getString( "dataelement" ) + SEP + - rowSet.getString( "categoryoptioncombo" ) + SEP + - rowSet.getString( "period" ) + SEP + - rowSet.getString( "organisationunit" ); + StringBuilder key = new StringBuilder(); + + for ( String dim : dimensions ) + { + key.append( rowSet.getString( dim ) + SEP ); + } + + key.deleteCharAt( key.length() - SEP.length() ); Double value = rowSet.getDouble( "value" ); - map.put( key, value ); + map.put( key.toString(), value ); } return new AsyncResult>( map ); } + + // ------------------------------------------------------------------------- + // Supportive methods + // ------------------------------------------------------------------------- + + private String getExtraDimensionQuery( DataQueryParams params ) + { + Map> dimensionValues = params.getDimensions(); + + String sql = ""; + + for ( String dim : params.getDynamicDimensionNames() ) + { + sql += "AND " + dim + " IN ( " + getQuotedCommaDelimitedString( dimensionValues.get( dim ) ) + " ) "; + } + + return sql; + } } === modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcAnalyticsTableManager.java' --- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcAnalyticsTableManager.java 2012-12-14 12:37:06 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcAnalyticsTableManager.java 2012-12-14 15:04:48 +0000 @@ -86,7 +86,6 @@ // Implementation // ------------------------------------------------------------------------- - //TODO use uid instead of id //TODO average aggregation operator data, pre-aggregate in time dimension, not in org unit dimension public void createTable( String tableName ) @@ -231,8 +230,8 @@ columns.add( col ); } - String[] de = { "dataelement", "character(11) not null", "de.uid" }; - String[] co = { "categoryoptioncombo", "character(11) not null", "coc.uid" }; + String[] de = { "de", "character(11) not null", "de.uid" }; + String[] co = { "coc", "character(11) not null", "coc.uid" }; columns.addAll( Arrays.asList( de, co ) ); === modified file 'dhis-2/dhis-support/dhis-support-system/src/main/java/org/hisp/dhis/system/util/TextUtils.java' --- dhis-2/dhis-support/dhis-support-system/src/main/java/org/hisp/dhis/system/util/TextUtils.java 2012-11-26 12:54:18 +0000 +++ dhis-2/dhis-support/dhis-support-system/src/main/java/org/hisp/dhis/system/util/TextUtils.java 2012-12-14 15:04:48 +0000 @@ -39,6 +39,7 @@ public static final TextUtils INSTANCE = new TextUtils(); private static final Pattern LINK_PATTERN = Pattern.compile( "((http://|https://|www\\.).+?)($|\\n|\\r|\\r\\n| )" ); + private static final String DELIMITER = ", "; /** * Performs the htmlNewline(String) and htmlLinks(String) methods against @@ -152,26 +153,60 @@ } /** - * Transforms a collection of Integers into a comma delimited String. - * - * @param elements the collection of Integers - * @return a comma delimited String. - */ - public static String getCommaDelimitedString( Collection elements ) - { - if ( elements != null && elements.size() > 0 ) - { - final StringBuffer buffer = new StringBuffer(); - - for ( Integer element : elements ) - { - buffer.append( element.toString() ).append( ", " ); - } - - return buffer.substring( 0, buffer.length() - ", ".length() ); - } - - return null; + * Transforms a collection of Integers into a comma delimited String. If the + * given collection of elements are null or is empty, an empty String is + * returned. + * + * @param elements the collection of Integers + * @return a comma delimited String. + */ + public static String getCommaDelimitedString( Collection elements ) + { + final StringBuilder builder = new StringBuilder(); + + if ( elements != null && !elements.isEmpty() ) + { + for ( Object element : elements ) + { + builder.append( element.toString() ).append( DELIMITER ); + } + + return builder.substring( 0, builder.length() - DELIMITER.length() ); + } + + return builder.toString(); + } + + /** + * Transforms a collection of Integers into a comma delimited String. If the + * given collection of elements are null or is empty, an empty String is + * returned. + * + * @param delimitPrefix whether to prefix the string with a delimiter. + * @param delimitSuffix whether to suffix the string with a delimiter. + * @param elements the collection of Integers + * @return a comma delimited String. + */ + public static String getCommaDelimitedString( Collection elements, boolean delimitPrefix, boolean delimitSuffix ) + { + final StringBuilder builder = new StringBuilder(); + + if ( elements != null && !elements.isEmpty() ) + { + if ( delimitPrefix ) + { + builder.append( DELIMITER ); + } + + builder.append( getCommaDelimitedString( elements ) ); + + if ( delimitSuffix ) + { + builder.append( DELIMITER ); + } + } + + return builder.toString(); } /** === modified file 'dhis-2/dhis-web/dhis-web-api/src/main/java/org/hisp/dhis/api/controller/AnalyticsController.java' --- dhis-2/dhis-web/dhis-web-api/src/main/java/org/hisp/dhis/api/controller/AnalyticsController.java 2012-12-14 12:37:06 +0000 +++ dhis-2/dhis-web/dhis-web-api/src/main/java/org/hisp/dhis/api/controller/AnalyticsController.java 2012-12-14 15:04:48 +0000 @@ -37,6 +37,7 @@ import org.hisp.dhis.api.utils.ContextUtils.CacheStrategy; import org.hisp.dhis.common.Grid; import org.hisp.dhis.dxf2.utils.JacksonUtils; +import org.hisp.dhis.system.grid.GridUtils; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Controller; import org.springframework.ui.Model; @@ -54,18 +55,33 @@ private ContextUtils contextUtils; @RequestMapping( method = RequestMethod.GET, consumes = { "application/json" }, produces = { "application/json" } ) - public String get( InputStream in, + public String getJson( InputStream in, Model model, HttpServletResponse response ) throws Exception { + contextUtils.configureResponse( response, ContextUtils.CONTENT_TYPE_JSON, CacheStrategy.NO_CACHE ); //TODO + DataQueryParams params = JacksonUtils.fromJson( in, DataQueryParams.class ); Grid grid = analyticsService.getAggregatedDataValueTotals( params ); model.addAttribute( "model", grid ); model.addAttribute( "viewClass", "detailed" ); - contextUtils.configureResponse( response, ContextUtils.CONTENT_TYPE_JSON, CacheStrategy.NO_CACHE ); //TODO return "grid"; } + + @RequestMapping( method = RequestMethod.GET, consumes = { "application/json" }, produces = { "application/xml" } ) + public void getXml( InputStream in, + Model model, + HttpServletResponse response ) throws Exception + { + contextUtils.configureResponse( response, ContextUtils.CONTENT_TYPE_XML, CacheStrategy.NO_CACHE ); //TODO + + DataQueryParams params = JacksonUtils.fromJson( in, DataQueryParams.class ); + + Grid grid = analyticsService.getAggregatedDataValueTotals( params ); + + GridUtils.toXml( grid, response.getOutputStream() ); + } }