dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #24541
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 12051: Event analytics, added paging in response
------------------------------------------------------------
revno: 12051
committer: Lars Helge Øverland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Wed 2013-09-11 22:04:18 +0200
message:
Event analytics, added paging in response
modified:
dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/AnalyticsService.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/data/DefaultEventAnalyticsService.java
dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/data/JdbcEventAnalyticsManager.java
dhis-2/pom.xml
--
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-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/AnalyticsService.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/AnalyticsService.java 2013-08-23 16:05:01 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/AnalyticsService.java 2013-09-11 20:04:18 +0000
@@ -92,6 +92,7 @@
public interface AnalyticsService
{
final String NAMES_META_KEY = "names";
+ final String PAGER_META_KEY = "pager";
/**
* Generates aggregated values for the given query.
=== 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-08-23 16:05:01 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/EventAnalyticsManager.java 2013-09-11 20:04:18 +0000
@@ -36,4 +36,6 @@
public interface EventAnalyticsManager
{
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/data/DefaultEventAnalyticsService.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/data/DefaultEventAnalyticsService.java 2013-09-10 18:29:50 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/data/DefaultEventAnalyticsService.java 2013-09-11 20:04:18 +0000
@@ -46,6 +46,7 @@
import org.hisp.dhis.common.Grid;
import org.hisp.dhis.common.GridHeader;
import org.hisp.dhis.common.IdentifiableObject;
+import org.hisp.dhis.common.Pager;
import org.hisp.dhis.dataelement.DataElement;
import org.hisp.dhis.dataelement.DataElementService;
import org.hisp.dhis.organisationunit.OrganisationUnit;
@@ -101,9 +102,7 @@
// EventAnalyticsService implementation
// -------------------------------------------------------------------------
- //TODO org unit children / descendants
- //TODO order the event analytics tables up front to avoid sorting in queries
- //TODO include ordered column to avoid slow offset clause for paging
+ //TODO order the event analytics tables up front to avoid default sorting in queries
public Grid getEvents( EventQueryParams params )
{
@@ -132,17 +131,31 @@
List<EventQueryParams> queries = EventQueryPlanner.planQuery( params );
+ int count = 0;
+
for ( EventQueryParams query : queries )
{
+ if ( params.isPaging() )
+ {
+ count += analyticsManager.getEventCount( query );
+ }
+
analyticsManager.getEvents( query, grid );
}
-
+
// ---------------------------------------------------------------------
// Meta-data
// ---------------------------------------------------------------------
- Map<Object, Object> metaData = new HashMap<Object, Object>();
+ Map<Object, Object> metaData = new HashMap<Object, Object>();
metaData.put( AnalyticsService.NAMES_META_KEY, getUidNameMap( params ) );
+
+ if ( params.isPaging() )
+ {
+ Pager pager = new Pager( params.getPageWithDefault(), count, params.getPageSizeWithDefault() );
+ metaData.put( AnalyticsService.PAGER_META_KEY, pager );
+ }
+
grid.setMetaData( metaData );
return grid;
@@ -278,7 +291,7 @@
// -------------------------------------------------------------------------
// Supportive methods
// -------------------------------------------------------------------------
-
+
private Map<String, String> getUidNameMap( EventQueryParams params )
{
Map<String, String> map = new HashMap<String, String>();
=== 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-10 14:22:41 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/data/JdbcEventAnalyticsManager.java 2013-09-11 20:04:18 +0000
@@ -49,6 +49,8 @@
import org.springframework.jdbc.support.rowset.SqlRowSet;
/**
+ * TODO could use row_number() and filtering for paging, but not supported on MySQL.
+ *
* @author Lars Helge Overland
*/
public class JdbcEventAnalyticsManager
@@ -80,48 +82,13 @@
}
sql = removeLast( sql, 1 ) + " ";
-
- sql += "from " + params.getTableName() + " ";
- sql += "where executiondate >= '" + getMediumDateString( params.getStartDate() ) + "' ";
- sql += "and executiondate <= '" + getMediumDateString( params.getEndDate() ) + "' ";
-
- if ( params.isOrganisationUnitMode( EventQueryParams.OU_MODE_SELECTED ) )
- {
- sql += "and ou in (" + getQuotedCommaDelimitedString( getUids( params.getOrganisationUnits() ) ) + ") ";
- }
- else if ( params.isOrganisationUnitMode( EventQueryParams.OU_MODE_CHILDREN ) )
- {
- sql += "and ou in (" + getQuotedCommaDelimitedString( getUids( params.getOrganisationUnitChildren() ) ) + ") ";
- }
- else // Descendants
- {
- sql += "and (";
-
- for ( OrganisationUnit unit : params.getOrganisationUnits() )
- {
- sql += "uidlevel" + unit.getLevel() + " = '" + unit.getUid() + "' or ";
- }
-
- sql = TextUtils.removeLast( sql, 3 ) + ") ";
- }
-
- if ( params.getProgramStage() != null )
- {
- sql += "and ps = '" + params.getProgramStage().getUid() + "' ";
- }
-
- // ---------------------------------------------------------------------
- // Filters
- // ---------------------------------------------------------------------
-
- for ( QueryItem filter : params.getItems() )
- {
- if ( filter.hasFilter() )
- {
- sql += "and lower(" + filter.getItem().getUid() + ") " + filter.getSqlOperator() + " " + getSqlFilter( filter ) + " ";
- }
- }
-
+
+ // ---------------------------------------------------------------------
+ // Criteria
+ // ---------------------------------------------------------------------
+
+ sql += getFromWhereClause( params );
+
// ---------------------------------------------------------------------
// Sorting
// ---------------------------------------------------------------------
@@ -179,10 +146,69 @@
return grid;
}
+ public int getEventCount( EventQueryParams params )
+ {
+ String sql = "select count(psi) ";
+
+ sql += getFromWhereClause( params );
+
+ Timer t = new Timer().start();
+
+ int count = jdbcTemplate.queryForObject( sql, Integer.class );
+
+ t.getTime( "Analytics event count SQL: " + sql );
+
+ return count;
+ }
+
// -------------------------------------------------------------------------
// Supportive methods
// -------------------------------------------------------------------------
+ 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.isOrganisationUnitMode( EventQueryParams.OU_MODE_SELECTED ) )
+ {
+ sql += "and ou in (" + getQuotedCommaDelimitedString( getUids( params.getOrganisationUnits() ) ) + ") ";
+ }
+ else if ( params.isOrganisationUnitMode( EventQueryParams.OU_MODE_CHILDREN ) )
+ {
+ sql += "and ou in (" + getQuotedCommaDelimitedString( getUids( params.getOrganisationUnitChildren() ) ) + ") ";
+ }
+ else // Descendants
+ {
+ sql += "and (";
+
+ for ( OrganisationUnit unit : params.getOrganisationUnits() )
+ {
+ sql += "uidlevel" + unit.getLevel() + " = '" + unit.getUid() + "' or ";
+ }
+
+ sql = TextUtils.removeLast( sql, 3 ) + ") ";
+ }
+
+ if ( params.getProgramStage() != null )
+ {
+ sql += "and ps = '" + params.getProgramStage().getUid() + "' ";
+ }
+
+ for ( QueryItem filter : params.getItems() )
+ {
+ if ( filter.hasFilter() )
+ {
+ sql += "and lower(" + filter.getItem().getUid() + ") " + filter.getSqlOperator() + " " + getSqlFilter( filter ) + " ";
+ }
+ }
+
+ return sql;
+ }
+
private String getSqlFilter( QueryItem item )
{
String operator = item.getOperator();
=== modified file 'dhis-2/pom.xml'
--- dhis-2/pom.xml 2013-09-03 14:53:19 +0000
+++ dhis-2/pom.xml 2013-09-11 20:04:18 +0000
@@ -640,7 +640,7 @@
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
- <version>1.3.166</version>
+ <version>1.3.173</version>
</dependency>
<dependency>
<groupId>org.hsqldb</groupId>
