← Back to team overview

dhis2-devs team mailing list archive

[Branch ~dhis2-devs-core/dhis2/trunk] Rev 12288: Event analytics, impl first cut of aggregated events API

 

------------------------------------------------------------
revno: 12288
committer: Lars Helge Øverland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Fri 2013-09-27 20:32:05 +0200
message:
  Event analytics, impl first cut of aggregated events API
modified:
  dhis-2/dhis-api/src/main/java/org/hisp/dhis/common/DimensionalObject.java
  dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/EventAnalyticsManager.java
  dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/EventAnalyticsService.java
  dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/EventQueryParams.java
  dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/data/DefaultEventAnalyticsService.java
  dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/data/EventQueryPlanner.java
  dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/data/JdbcEventAnalyticsManager.java
  dhis-2/dhis-services/dhis-service-analytics/src/test/java/org/hisp/dhis/analytics/event/data/EventQueryPlannerTest.java
  dhis-2/dhis-web/dhis-web-api/src/main/java/org/hisp/dhis/api/controller/EventAnalyticsController.java


--
lp:dhis2
https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk

Your team DHIS 2 developers is subscribed to branch lp:dhis2.
To unsubscribe from this branch go to https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk/+edit-subscription
=== modified file 'dhis-2/dhis-api/src/main/java/org/hisp/dhis/common/DimensionalObject.java'
--- dhis-2/dhis-api/src/main/java/org/hisp/dhis/common/DimensionalObject.java	2013-08-23 15:56:19 +0000
+++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/common/DimensionalObject.java	2013-09-27 18:32:05 +0000
@@ -45,6 +45,7 @@
     final String CATEGORYOPTIONCOMBO_DIM_ID = "co";
     final String PERIOD_DIM_ID = "pe";
     final String ORGUNIT_DIM_ID = "ou";
+    final String ITEM_DIM_ID = "item";
     
     final String DIMENSION_SEP = "-";
 

=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/EventAnalyticsManager.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/EventAnalyticsManager.java	2013-09-11 20:04:18 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/EventAnalyticsManager.java	2013-09-27 18:32:05 +0000
@@ -35,6 +35,8 @@
  */
 public interface EventAnalyticsManager
 {
+    Grid getAggregatedEventData( EventQueryParams params, Grid grid );
+    
     Grid getEvents( EventQueryParams params, Grid grid );
     
     int getEventCount( EventQueryParams params );

=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/EventAnalyticsService.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/EventAnalyticsService.java	2013-09-02 17:38:21 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/EventAnalyticsService.java	2013-09-27 18:32:05 +0000
@@ -37,8 +37,13 @@
  */
 public interface EventAnalyticsService
 {
+    Grid getAggregatedEventData( EventQueryParams params );
+    
     Grid getEvents( EventQueryParams params );
 
+    EventQueryParams getFromUrl( String program, String stage, String startDate, String endDate, String ou, String ouMode, 
+        Set<String> item );
+    
     EventQueryParams getFromUrl( String program, String stage, String startDate, String endDate, String ou, String ouMode,
         Set<String> item, Set<String> asc, Set<String> desc, Integer page, Integer pageSize );
 }

=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/EventQueryParams.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/EventQueryParams.java	2013-09-02 17:38:21 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/EventQueryParams.java	2013-09-27 18:32:05 +0000
@@ -72,6 +72,14 @@
     private Integer pageSize;
 
     // -------------------------------------------------------------------------
+    // Transient properties
+    // -------------------------------------------------------------------------
+    
+    private String periodType;
+    
+    private int organisationUnitLevel;
+    
+    // -------------------------------------------------------------------------
     // Constructors
     // -------------------------------------------------------------------------
 
@@ -89,9 +97,12 @@
         this.asc = new ArrayList<String>( params.getAsc() );
         this.desc = new ArrayList<String>( params.getDesc() );
         this.organisationUnits = new ArrayList<OrganisationUnit>( params.getOrganisationUnits() );
+        this.organisationUnitMode = params.getOrganisationUnitMode();
         this.tableName = params.getTableName();
         this.page = params.getPage();
         this.pageSize = params.getPageSize();
+        this.periodType = params.getPeriodType();
+        this.organisationUnitLevel = params.getOrganisationUnitLevel();
     }
 
     // -------------------------------------------------------------------------
@@ -263,4 +274,24 @@
     {
         this.pageSize = pageSize;
     }
+
+    public String getPeriodType()
+    {
+        return periodType;
+    }
+
+    public void setPeriodType( String periodType )
+    {
+        this.periodType = periodType;
+    }
+
+    public int getOrganisationUnitLevel()
+    {
+        return organisationUnitLevel;
+    }
+
+    public void setOrganisationUnitLevel( int organisationUnitLevel )
+    {
+        this.organisationUnitLevel = organisationUnitLevel;
+    }
 }

=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/data/DefaultEventAnalyticsService.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/data/DefaultEventAnalyticsService.java	2013-09-27 11:13:20 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/data/DefaultEventAnalyticsService.java	2013-09-27 18:32:05 +0000
@@ -29,6 +29,7 @@
  */
 
 import static org.hisp.dhis.analytics.DataQueryParams.OPTION_SEP;
+import static org.hisp.dhis.common.DimensionalObject.*;
 
 import java.util.Date;
 import java.util.HashMap;
@@ -104,20 +105,61 @@
 
     //TODO order the event analytics tables up front to avoid default sorting in queries
     
+    public Grid getAggregatedEventData( EventQueryParams params )
+    {
+        EventQueryPlanner.validate( params );
+        
+        Grid grid = new ListGrid();
+
+        // ---------------------------------------------------------------------
+        // Headers
+        // ---------------------------------------------------------------------
+
+        grid.addHeader( new GridHeader( ITEM_DIM_ID, "Item" ) );
+        grid.addHeader( new GridHeader( PERIOD_DIM_ID, "Period" ) );
+        grid.addHeader( new GridHeader( ORGUNIT_DIM_ID, "Organisation unit" ) );
+        grid.addHeader( new GridHeader( "value", "Value" ) );
+
+        // ---------------------------------------------------------------------
+        // Data
+        // ---------------------------------------------------------------------
+
+        //TODO relative periods
+                
+        List<EventQueryParams> queries = EventQueryPlanner.planQuery( params );
+        
+        for ( EventQueryParams query : queries )
+        {
+            analyticsManager.getAggregatedEventData( query, grid );
+        }        
+
+        // ---------------------------------------------------------------------
+        // Meta-data
+        // ---------------------------------------------------------------------
+
+        Map<Object, Object> metaData = new HashMap<Object, Object>();        
+        metaData.put( AnalyticsService.NAMES_META_KEY, getUidNameMap( params ) );
+        grid.setMetaData( metaData );
+        
+        return grid;        
+    }
+    
     public Grid getEvents( EventQueryParams params )
     {
+        EventQueryPlanner.validate( params );
+        
         Grid grid = new ListGrid();
-                
-        grid.addHeader( new GridHeader( "Event", ITEM_EVENT ) );
-        grid.addHeader( new GridHeader( "Program stage", ITEM_PROGRAM_STAGE ) );
-        grid.addHeader( new GridHeader( "Execution date", ITEM_EXECUTION_DATE ) );
-        grid.addHeader( new GridHeader( "Organisation unit", ITEM_ORG_UNIT ) );
-        grid.addHeader( new GridHeader( "Organisation unit name", ITEM_ORG_UNIT_NAME ) );
 
         // ---------------------------------------------------------------------
         // Headers
         // ---------------------------------------------------------------------
 
+        grid.addHeader( new GridHeader( ITEM_EVENT, "Event" ) );
+        grid.addHeader( new GridHeader( ITEM_PROGRAM_STAGE, "Program stage" ) );
+        grid.addHeader( new GridHeader( ITEM_EXECUTION_DATE, "Execution date" ) );
+        grid.addHeader( new GridHeader( ITEM_ORG_UNIT, "Organisation unit" ) );
+        grid.addHeader( new GridHeader( ITEM_ORG_UNIT_NAME, "Organisation unit name" ) );
+
         for ( QueryItem queryItem : params.getItems() )
         {
             IdentifiableObject item = queryItem.getItem();
@@ -160,9 +202,15 @@
         
         return grid;
     }
+
+    public EventQueryParams getFromUrl( String program, String stage, String startDate, String endDate, 
+        String ou, String ouMode, Set<String> item )
+    {
+        return getFromUrl( program, stage, startDate, endDate, ou, ouMode, item, null, null, null, null );
+    }
     
-    public EventQueryParams getFromUrl( String program, String stage, String startDate, String endDate, String ou, String ouMode,
-        Set<String> item, Set<String> asc, Set<String> desc, Integer page, Integer pageSize )
+    public EventQueryParams getFromUrl( String program, String stage, String startDate, String endDate, 
+        String ou, String ouMode, Set<String> item, Set<String> asc, Set<String> desc, Integer page, Integer pageSize )
     {
         EventQueryParams params = new EventQueryParams();
         

=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/data/EventQueryPlanner.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/data/EventQueryPlanner.java	2013-08-23 16:05:01 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/data/EventQueryPlanner.java	2013-09-27 18:32:05 +0000
@@ -32,7 +32,12 @@
 import java.util.Date;
 import java.util.List;
 
+import org.apache.commons.logging.Log;
+import org.apache.commons.logging.LogFactory;
+import org.hisp.dhis.analytics.IllegalQueryException;
 import org.hisp.dhis.analytics.event.EventQueryParams;
+import org.hisp.dhis.common.ListMap;
+import org.hisp.dhis.organisationunit.OrganisationUnit;
 import org.hisp.dhis.period.Cal;
 import org.hisp.dhis.program.Program;
 
@@ -41,14 +46,53 @@
  */
 public class EventQueryPlanner
 {
+    private static final Log log = LogFactory.getLog( EventQueryPlanner.class );
+    
     private static final String TABLE_BASE_NAME = "analytics_event_";
     
+    public static void validate( EventQueryParams params )
+        throws IllegalQueryException
+    {
+        String violation = null;
+
+        if ( params == null )
+        {
+            throw new IllegalQueryException( "Params cannot be null" );
+        }
+        
+        if ( params.getOrganisationUnits().isEmpty() )
+        {
+            violation = "At least one organisation unit must be specified";
+        }
+        
+        if ( params.getStartDate() == null || params.getEndDate() == null )
+        {
+            violation = "Start and end date or at least one period must be specified";
+        }
+
+        if ( violation != null )
+        {
+            log.warn( "Validation failed: " + violation );
+            
+            throw new IllegalQueryException( violation );
+        }
+    }
+    
     public static List<EventQueryParams> planQuery( EventQueryParams params )
     {
-        return splitByPartition( params );
+        List<EventQueryParams> queries = new ArrayList<EventQueryParams>();
+        
+        List<EventQueryParams> groupedByPartition = groupByPartition( params );
+        
+        for ( EventQueryParams byPartition : groupedByPartition )
+        {
+            queries.addAll( groupByOrgUnitLevel( byPartition ) );
+        }
+        
+        return queries;
     }
     
-    private static List<EventQueryParams> splitByPartition( EventQueryParams params )
+    private static List<EventQueryParams> groupByPartition( EventQueryParams params )
     {
         List<EventQueryParams> list = new ArrayList<EventQueryParams>();
         
@@ -85,6 +129,28 @@
         return list;
     }
     
+    private static List<EventQueryParams> groupByOrgUnitLevel( EventQueryParams params )
+    {
+        ListMap<Integer, OrganisationUnit> levelOrgUnitMap = new ListMap<Integer, OrganisationUnit>();
+        
+        for ( OrganisationUnit unit : params.getOrganisationUnits() )
+        {
+            levelOrgUnitMap.putValue( unit.getLevel(), unit );
+        }
+        
+        List<EventQueryParams> queries = new ArrayList<EventQueryParams>();
+        
+        for ( Integer level : levelOrgUnitMap.keySet() )
+        {
+            EventQueryParams query = new EventQueryParams( params );
+            query.setOrganisationUnits( levelOrgUnitMap.get( level ) );
+            query.setOrganisationUnitLevel( level );
+            queries.add( query );
+        }
+        
+        return queries;
+    }
+    
     private static EventQueryParams getQuery( EventQueryParams params, Date startDate, Date endDate, Program program )
     {
         EventQueryParams query = new EventQueryParams( params );

=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/data/JdbcEventAnalyticsManager.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/data/JdbcEventAnalyticsManager.java	2013-09-25 09:18:11 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/data/JdbcEventAnalyticsManager.java	2013-09-27 18:32:05 +0000
@@ -66,22 +66,53 @@
     // EventAnalyticsManager implementation
     // -------------------------------------------------------------------------
 
+    public Grid getAggregatedEventData( EventQueryParams params, Grid grid )
+    {
+        String sql = "select count(psi) as value, uidlevel" +
+            params.getOrganisationUnitLevel() + getItemColumns( params ) + " ";
+
+        // ---------------------------------------------------------------------
+        // Criteria
+        // ---------------------------------------------------------------------
+
+        sql += getFromWhereClause( params );
+        
+        // ---------------------------------------------------------------------
+        // Group by
+        // ---------------------------------------------------------------------
+
+        sql += "group by uidlevel" + params.getOrganisationUnitLevel() + getItemColumns( params );
+
+        // ---------------------------------------------------------------------
+        // Grid
+        // ---------------------------------------------------------------------
+
+        Timer t = new Timer().start();
+        
+        SqlRowSet rowSet = jdbcTemplate.queryForRowSet( sql );
+
+        t.getTime( "Analytics event aggregate SQL: " + sql );
+        
+        while ( rowSet.next() )
+        {
+            int value = rowSet.getInt( "value" );
+            String ou = rowSet.getString( params.getOrganisationUnitLevel() );
+            
+            for ( QueryItem queryItem : params.getItems() )
+            {
+                String itemValue = rowSet.getString( queryItem.getItem().getUid() );
+                String item = queryItem.getItem().getName() + ": " + itemValue;
+                
+                grid.addRow().addValue( item ).addValue( null ).addValue( ou ).addValue( value );
+            }
+        }
+
+        return grid;
+    }
+    
     public Grid getEvents( EventQueryParams params, Grid grid )
     {
-        String sql = "select psi,ps,executiondate,ou,ouname,";
-
-        // ---------------------------------------------------------------------
-        // Items
-        // ---------------------------------------------------------------------
-
-        for ( QueryItem queryItem : params.getItems() )
-        {
-            IdentifiableObject item = queryItem.getItem();
-            
-            sql += item.getUid() + ",";
-        }
-        
-        sql = removeLast( sql, 1 ) + " ";
+        String sql = "select psi,ps,executiondate,ou,ouname" + getItemColumns( params ) + " ";
 
         // ---------------------------------------------------------------------
         // Criteria
@@ -129,7 +160,7 @@
         
         SqlRowSet rowSet = jdbcTemplate.queryForRowSet( sql );
 
-        t.getTime( "Analytics event SQL: " + sql );
+        t.getTime( "Analytics event query SQL: " + sql );
         
         while ( rowSet.next() )
         {
@@ -165,13 +196,31 @@
     // Supportive methods
     // -------------------------------------------------------------------------
 
+    private String getItemColumns( EventQueryParams params )
+    {
+        String sql = params.getItems().isEmpty() ? "" : ",";
+        
+        for ( QueryItem queryItem : params.getItems() )
+        {
+            IdentifiableObject item = queryItem.getItem();
+            
+            sql += item.getUid() + ",";
+        }
+        
+        return removeLast( sql, 1 );
+    }
+    
     private String getFromWhereClause( EventQueryParams params )
     {
         String sql = "";
         
         sql += "from " + params.getTableName() + " ";
-        sql += "where executiondate >= '" + getMediumDateString( params.getStartDate() ) + "' ";
-        sql += "and executiondate <= '" + getMediumDateString( params.getEndDate() ) + "' ";
+        
+        if ( params.getStartDate() != null && params.getEndDate() != null )
+        {        
+            sql += "where executiondate >= '" + getMediumDateString( params.getStartDate() ) + "' ";
+            sql += "and executiondate <= '" + getMediumDateString( params.getEndDate() ) + "' ";
+        }
         
         if ( params.isOrganisationUnitMode( EventQueryParams.OU_MODE_SELECTED ) )
         {

=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/test/java/org/hisp/dhis/analytics/event/data/EventQueryPlannerTest.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/test/java/org/hisp/dhis/analytics/event/data/EventQueryPlannerTest.java	2013-08-23 16:05:01 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/test/java/org/hisp/dhis/analytics/event/data/EventQueryPlannerTest.java	2013-09-27 18:32:05 +0000
@@ -30,28 +30,48 @@
 
 import static org.junit.Assert.assertEquals;
 
+import java.util.Arrays;
 import java.util.List;
 
+import org.hisp.dhis.DhisConvenienceTest;
 import org.hisp.dhis.analytics.event.EventQueryParams;
+import org.hisp.dhis.organisationunit.OrganisationUnit;
 import org.hisp.dhis.period.Cal;
 import org.hisp.dhis.program.Program;
+import org.junit.Before;
 import org.junit.Test;
 
 /**
  * @author Lars Helge Overland
  */
 public class EventQueryPlannerTest
+    extends DhisConvenienceTest
 {
+    private Program prA;
+    private OrganisationUnit ouA;
+    private OrganisationUnit ouB;
+    
+    @Before
+    public void before()
+    {
+        prA = new Program();
+        prA.setUid( "programuidA" );
+        
+        ouA = createOrganisationUnit( 'A' );
+        ouB = createOrganisationUnit( 'B' );
+        
+        ouA.setLevel( 1 );
+        ouB.setLevel( 2 );        
+    }
+    
     @Test
     public void testPlanQueryA()
-    {
-        Program prA = new Program();
-        prA.setUid( "programuidA" );
-        
+    {        
         EventQueryParams params = new EventQueryParams();
         params.setProgram( prA );
         params.setStartDate( new Cal( 2010, 6, 1 ).time() );
         params.setEndDate( new Cal( 2012, 3, 20 ).time() );
+        params.getOrganisationUnits().add( ouA );
         
         List<EventQueryParams> queries = EventQueryPlanner.planQuery( params );
         
@@ -71,14 +91,12 @@
 
     @Test
     public void testPlanQueryB()
-    {
-        Program prA = new Program();
-        prA.setUid( "programuidA" );
-        
+    {        
         EventQueryParams params = new EventQueryParams();
         params.setProgram( prA );
         params.setStartDate( new Cal( 2010, 3, 1 ).time() );
         params.setEndDate( new Cal( 2010, 9, 20 ).time() );
+        params.getOrganisationUnits().add( ouA );
         
         List<EventQueryParams> queries = EventQueryPlanner.planQuery( params );
 
@@ -88,5 +106,20 @@
         assertEquals( new Cal( 2010, 9, 20 ).time(), queries.get( 0 ).getEndDate() );
 
         assertEquals( "analytics_event_2010_programuidA", queries.get( 0 ).getTableName() );
-    }        
+    }
+    
+
+    @Test
+    public void testPlanQueryC()
+    {        
+        EventQueryParams params = new EventQueryParams();
+        params.setProgram( prA );
+        params.setStartDate( new Cal( 2010, 6, 1 ).time() );
+        params.setEndDate( new Cal( 2012, 3, 20 ).time() );
+        params.setOrganisationUnits( Arrays.asList( ouA, ouB ) );
+        
+        List<EventQueryParams> queries = EventQueryPlanner.planQuery( params );
+        
+        assertEquals( 6, queries.size() );
+    }
 }

=== modified file 'dhis-2/dhis-web/dhis-web-api/src/main/java/org/hisp/dhis/api/controller/EventAnalyticsController.java'
--- dhis-2/dhis-web/dhis-web-api/src/main/java/org/hisp/dhis/api/controller/EventAnalyticsController.java	2013-09-16 11:22:02 +0000
+++ dhis-2/dhis-web/dhis-web-api/src/main/java/org/hisp/dhis/api/controller/EventAnalyticsController.java	2013-09-27 18:32:05 +0000
@@ -54,7 +54,7 @@
 @Controller
 public class EventAnalyticsController
 {
-    private static final String RESOURCE_PATH = "/analytics/events/query";
+    private static final String RESOURCE_PATH = "/analytics/events";
 
     @Autowired
     private EventAnalyticsService analyticsService;
@@ -63,11 +63,55 @@
     private ContextUtils contextUtils;
 
     // -------------------------------------------------------------------------
-    // Resources
-    // -------------------------------------------------------------------------
-    
-    @RequestMapping( value = RESOURCE_PATH + "/{program}", method = RequestMethod.GET, produces = { "application/json", "application/javascript" } )
-    public String getJson( // JSON, JSONP
+    // Aggregate
+    // -------------------------------------------------------------------------
+    
+    @RequestMapping( value = RESOURCE_PATH + "/aggregate/{program}", method = RequestMethod.GET, produces = { "application/json", "application/javascript" } )
+    public String getAggregateJson( // JSON, JSONP
+        @PathVariable String program,
+        @RequestParam(required=false) String stage,
+        @RequestParam String startDate,
+        @RequestParam String endDate,
+        @RequestParam String ou,
+        @RequestParam(required=false) String ouMode,
+        @RequestParam Set<String> item,
+        Model model,
+        HttpServletResponse response ) throws Exception
+    {
+        EventQueryParams params = analyticsService.getFromUrl( program, stage, startDate, endDate, ou, ouMode, item );
+        
+        contextUtils.configureResponse( response, ContextUtils.CONTENT_TYPE_JSON, CacheStrategy.RESPECT_SYSTEM_SETTING );
+        Grid grid = analyticsService.getAggregatedEventData( params );
+        model.addAttribute( "model", grid );
+        model.addAttribute( "viewClass", "detailed" );
+        return "grid";
+    }
+
+    @RequestMapping( value = RESOURCE_PATH + "/aggregate/{program}.xls", method = RequestMethod.GET )
+    public void getAggregateXls(
+        @PathVariable String program,
+        @RequestParam(required=false) String stage,
+        @RequestParam String startDate,
+        @RequestParam String endDate,
+        @RequestParam String ou,
+        @RequestParam(required=false) String ouMode,
+        @RequestParam Set<String> item,
+        Model model,
+        HttpServletResponse response ) throws Exception
+    {
+        EventQueryParams params = analyticsService.getFromUrl( program, stage, startDate, endDate, ou, ouMode, item );
+        
+        contextUtils.configureResponse( response, ContextUtils.CONTENT_TYPE_EXCEL, CacheStrategy.RESPECT_SYSTEM_SETTING, "events.xls", true );
+        Grid grid = analyticsService.getAggregatedEventData( params );
+        GridUtils.toXls( grid, response.getOutputStream() );
+    }
+    
+    // -------------------------------------------------------------------------
+    // Query
+    // -------------------------------------------------------------------------
+    
+    @RequestMapping( value = RESOURCE_PATH + "/query/{program}", method = RequestMethod.GET, produces = { "application/json", "application/javascript" } )
+    public String getQueryJson( // JSON, JSONP
         @PathVariable String program,
         @RequestParam(required=false) String stage,
         @RequestParam String startDate,
@@ -91,8 +135,8 @@
         return "grid";
     }
 
-    @RequestMapping( value = RESOURCE_PATH + "/{program}.xls", method = RequestMethod.GET )
-    public void getXls(
+    @RequestMapping( value = RESOURCE_PATH + "/query/{program}.xls", method = RequestMethod.GET )
+    public void getQueryXls(
         @PathVariable String program,
         @RequestParam(required=false) String stage,
         @RequestParam String startDate,