dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #24043
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 11732: Analytics, impl generation of event analytics tables
Merge authors:
Lars Helge Øverland (larshelge)
------------------------------------------------------------
revno: 11732 [merge]
committer: Lars Helge Øverland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Wed 2013-08-21 13:57:43 +0200
message:
Analytics, impl generation of event analytics tables
added:
dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcEventAnalyticsTableManager.java
modified:
dhis-2/dhis-services/dhis-service-analytics/pom.xml
dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/AnalyticsTable.java
dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/AnalyticsTableManager.java
dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/scheduling/AnalyticsTableTask.java
dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/AbstractJdbcTableManager.java
dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/DefaultAnalyticsTableService.java
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/JdbcCompletenessTargetTableManager.java
dhis-2/dhis-services/dhis-service-analytics/src/main/resources/META-INF/dhis/beans.xml
dhis-2/dhis-support/dhis-support-system/src/main/java/org/hisp/dhis/system/util/TextUtils.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-services/dhis-service-analytics/pom.xml'
--- dhis-2/dhis-services/dhis-service-analytics/pom.xml 2013-05-31 08:27:38 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/pom.xml 2013-08-21 10:08:02 +0000
@@ -27,6 +27,10 @@
</dependency>
<dependency>
<groupId>org.hisp.dhis</groupId>
+ <artifactId>dhis-service-patient</artifactId>
+ </dependency>
+ <dependency>
+ <groupId>org.hisp.dhis</groupId>
<artifactId>dhis-service-administration</artifactId>
</dependency>
<dependency>
=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/AnalyticsTable.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/AnalyticsTable.java 2013-08-20 22:48:23 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/AnalyticsTable.java 2013-08-21 11:56:16 +0000
@@ -27,6 +27,8 @@
* SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/
+import java.util.List;
+
import org.hisp.dhis.period.Period;
import org.hisp.dhis.program.Program;
@@ -36,6 +38,8 @@
public class AnalyticsTable
{
private String baseName;
+
+ private List<String[]> dimensionColumns;
private Period period;
@@ -45,20 +49,23 @@
{
}
- public AnalyticsTable( String baseName )
+ public AnalyticsTable( String baseName, List<String[]> dimensionColumns )
{
this.baseName = baseName;
+ this.dimensionColumns = dimensionColumns;
}
- public AnalyticsTable( String baseName, Period period )
+ public AnalyticsTable( String baseName, List<String[]> dimensionColumns, Period period )
{
this.baseName = baseName;
+ this.dimensionColumns = dimensionColumns;
this.period = period;
}
- public AnalyticsTable( String baseName, Period period, Program program )
+ public AnalyticsTable( String baseName, List<String[]> dimensionColumns, Period period, Program program )
{
this.baseName = baseName;
+ this.dimensionColumns = dimensionColumns;
this.period = period;
this.program = program;
}
@@ -113,6 +120,16 @@
this.baseName = baseName;
}
+ public List<String[]> getDimensionColumns()
+ {
+ return dimensionColumns;
+ }
+
+ public void setDimensionColumns( List<String[]> dimensionColumns )
+ {
+ this.dimensionColumns = dimensionColumns;
+ }
+
public Period getPeriod()
{
return period;
=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/AnalyticsTableManager.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/AnalyticsTableManager.java 2013-08-20 23:17:09 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/AnalyticsTableManager.java 2013-08-21 11:56:16 +0000
@@ -107,17 +107,6 @@
Future<?> populateTableAsync( ConcurrentLinkedQueue<AnalyticsTable> tables );
/**
- * Returns a list of string arrays in where the first index holds the database
- * column name, the second index holds the database column data type and the
- * third column holds a table alias and name, i.e.:
- *
- * 0 = database column name
- * 1 = database column data type
- * 2 = column alias and name
- */
- List<String[]> getDimensionColumns( AnalyticsTable table );
-
- /**
* Retrieves the start date of the period of the earliest data value row.
*/
Date getEarliestData();
=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/scheduling/AnalyticsTableTask.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/scheduling/AnalyticsTableTask.java 2013-03-15 16:33:34 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/scheduling/AnalyticsTableTask.java 2013-08-21 11:56:16 +0000
@@ -52,6 +52,9 @@
@Resource(name="org.hisp.dhis.analytics.CompletenessTargetTableService")
private AnalyticsTableService completenessTargetTableService;
+ @Resource(name="org.hisp.dhis.analytics.EventAnalyticsTableService")
+ private AnalyticsTableService eventAnalyticsTableService;
+
@Autowired
private ResourceTableService resourceTableService;
@@ -95,6 +98,10 @@
completenessTargetTableService.update( last3Years, taskId );
+ notifier.notify( taskId, "Updating event analytics tables" );
+
+ eventAnalyticsTableService.update( last3Years, taskId );
+
notifier.notify( taskId, INFO, "Analytics tables updated", true );
}
}
=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/AbstractJdbcTableManager.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/AbstractJdbcTableManager.java 2013-08-20 23:17:09 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/AbstractJdbcTableManager.java 2013-08-21 11:56:16 +0000
@@ -51,6 +51,7 @@
import org.springframework.jdbc.BadSqlGrammarException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.scheduling.annotation.Async;
+import org.springframework.transaction.annotation.Transactional;
/**
* @author Lars Helge Overland
@@ -83,9 +84,25 @@
protected JdbcTemplate jdbcTemplate;
// -------------------------------------------------------------------------
+ // Abstract methods
+ // -------------------------------------------------------------------------
+
+ /**
+ * Returns a list of string arrays in where the first index holds the database
+ * column name, the second index holds the database column data type and the
+ * third column holds a table alias and name, i.e.:
+ *
+ * 0 = database column name
+ * 1 = database column data type
+ * 2 = column alias and name
+ */
+ protected abstract List<String[]> getDimensionColumns( AnalyticsTable table );
+
+ // -------------------------------------------------------------------------
// Implementation
// -------------------------------------------------------------------------
-
+
+ @Transactional
public List<AnalyticsTable> getTables( boolean last3YearsOnly )
{
Date threeYrsAgo = new Cal().subtract( Calendar.YEAR, 2 ).set( 1, 1 ).time();
@@ -95,6 +112,7 @@
return getTables( earliest, latest );
}
+ @Transactional
public List<AnalyticsTable> getTables( Date earliest, Date latest )
{
String baseName = getTableName();
@@ -105,7 +123,7 @@
for ( Period period : periods )
{
- tables.add( new AnalyticsTable( baseName, period ) );
+ tables.add( new AnalyticsTable( baseName, getDimensionColumns( null ), period ) );
}
return tables;
=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/DefaultAnalyticsTableService.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/DefaultAnalyticsTableService.java 2013-08-20 23:17:09 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/DefaultAnalyticsTableService.java 2013-08-21 11:56:16 +0000
@@ -147,7 +147,7 @@
// -------------------------------------------------------------------------
// Supportive methods
// -------------------------------------------------------------------------
-
+
private void createTables( List<AnalyticsTable> tables )
{
for ( AnalyticsTable table : tables )
@@ -218,7 +218,7 @@
for ( AnalyticsTable table : tables )
{
- List<String[]> columns = tableManager.getDimensionColumns( table );
+ List<String[]> columns = table.getDimensionColumns();
for ( String[] column : columns )
{
=== 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 2013-08-20 23:17:09 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcAnalyticsTableManager.java 2013-08-21 10:08:02 +0000
@@ -80,7 +80,7 @@
{
return "analytics";
}
-
+
public void createTable( AnalyticsTable table )
{
final String tableName = table.getTempTableName();
=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcCompletenessTargetTableManager.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcCompletenessTargetTableManager.java 2013-08-20 23:17:09 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcCompletenessTargetTableManager.java 2013-08-21 11:56:16 +0000
@@ -38,6 +38,7 @@
import org.hisp.dhis.organisationunit.OrganisationUnitGroupSet;
import org.hisp.dhis.organisationunit.OrganisationUnitLevel;
import org.springframework.scheduling.annotation.Async;
+import org.springframework.transaction.annotation.Transactional;
/**
* @author Lars Helge Overland
@@ -46,10 +47,11 @@
extends AbstractJdbcTableManager
{
@Override
+ @Transactional
public List<AnalyticsTable> getTables( boolean last3YearsOnly )
{
List<AnalyticsTable> tables = new ArrayList<AnalyticsTable>();
- tables.add( new AnalyticsTable( getTableName() ) );
+ tables.add( new AnalyticsTable( getTableName(), getDimensionColumns( null ) ) );
return tables;
}
=== added file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcEventAnalyticsTableManager.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcEventAnalyticsTableManager.java 1970-01-01 00:00:00 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcEventAnalyticsTableManager.java 2013-08-21 11:56:16 +0000
@@ -0,0 +1,221 @@
+package org.hisp.dhis.analytics.table;
+
+/*
+ * Copyright (c) 2004-2012, University of Oslo
+ * All rights reserved.
+ *
+ * Redistribution and use in source and binary forms, with or without
+ * modification, are permitted provided that the following conditions are met:
+ * * Redistributions of source code must retain the above copyright notice, this
+ * list of conditions and the following disclaimer.
+ * * Redistributions in binary form must reproduce the above copyright notice,
+ * this list of conditions and the following disclaimer in the documentation
+ * and/or other materials provided with the distribution.
+ * * Neither the name of the HISP project nor the names of its contributors may
+ * be used to endorse or promote products derived from this software without
+ * specific prior written permission.
+ *
+ * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
+ * ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
+ * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
+ * DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR
+ * ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
+ * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
+ * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON
+ * ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
+ * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
+ * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
+ */
+
+import java.util.ArrayList;
+import java.util.Arrays;
+import java.util.Collection;
+import java.util.Date;
+import java.util.List;
+import java.util.concurrent.ConcurrentLinkedQueue;
+import java.util.concurrent.Future;
+
+import org.hisp.dhis.analytics.AnalyticsTable;
+import org.hisp.dhis.dataelement.DataElement;
+import org.hisp.dhis.organisationunit.OrganisationUnitLevel;
+import org.hisp.dhis.period.Period;
+import org.hisp.dhis.program.Program;
+import org.hisp.dhis.program.ProgramService;
+import org.hisp.dhis.system.util.DateUtils;
+import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.scheduling.annotation.Async;
+import org.springframework.transaction.annotation.Transactional;
+
+import static org.hisp.dhis.system.util.TextUtils.removeLast;
+
+/**
+ * @author Lars Helge Overland
+ */
+public class JdbcEventAnalyticsTableManager
+ extends AbstractJdbcTableManager
+{
+ @Autowired
+ private ProgramService programService;
+
+ // -------------------------------------------------------------------------
+ // Implementation
+ // -------------------------------------------------------------------------
+
+ @Override
+ @Transactional
+ public List<AnalyticsTable> getTables( Date earliest, Date latest )
+ {
+ String baseName = getTableName();
+
+ List<Period> periods = PartitionUtils.getPeriods( earliest, latest );
+
+ List<AnalyticsTable> tables = new ArrayList<AnalyticsTable>();
+
+ for ( Period period : periods )
+ {
+ for ( Program program : programService.getAllPrograms() )
+ {
+ AnalyticsTable table = new AnalyticsTable( baseName, null, period, program );
+ List<String[]> dimensionColumns = getDimensionColumns( table );
+ table.setDimensionColumns( dimensionColumns );
+ tables.add( table );
+ }
+ }
+
+ return tables;
+ }
+
+ public boolean validState()
+ {
+ return jdbcTemplate.queryForRowSet( "select dataelementid from patientdatavalue limit 1" ).next();
+ }
+
+ public String getTableName()
+ {
+ return "analytics_event";
+ }
+
+ public void createTable( AnalyticsTable table )
+ {
+ final String tableName = table.getTempTableName();
+
+ final String sqlDrop = "drop table " + tableName;
+
+ executeSilently( sqlDrop );
+
+ String sqlCreate = "create table " + tableName + " (";
+
+ for ( String[] col : getDimensionColumns( table ) )
+ {
+ sqlCreate += col[0] + " " + col[1] + ",";
+ }
+
+ sqlCreate = removeLast( sqlCreate, 1 ) + ")";
+
+ log.info( "Create SQL: " + sqlCreate );
+
+ executeSilently( sqlCreate );
+ }
+
+ @Async
+ @Override
+ public Future<?> populateTableAsync( ConcurrentLinkedQueue<AnalyticsTable> tables )
+ {
+ taskLoop : while ( true )
+ {
+ AnalyticsTable table = tables.poll();
+
+ if ( table == null )
+ {
+ break taskLoop;
+ }
+
+ final String start = DateUtils.getMediumDateString( table.getPeriod().getStartDate() );
+ final String end = DateUtils.getMediumDateString( table.getPeriod().getEndDate() );
+
+ String sql = "insert into " + table.getTempTableName() + " (";
+
+ for ( String[] col : getDimensionColumns( table ) )
+ {
+ sql += col[0] + ",";
+ }
+
+ sql = removeLast( sql, 1 ) + ") select ";
+
+ for ( String[] col : getDimensionColumns( table ) )
+ {
+ sql += col[2] + ",";
+ }
+
+ sql = removeLast( sql, 1 ) + " ";
+
+ sql +=
+ "from programstageinstance psi " +
+ "left join programinstance pi on psi.programinstanceid=pi.programinstanceid " +
+ "left join programstage ps on psi.programstageid=ps.programstageid " +
+ "left join program pr on pi.programid=pr.programid " +
+ "left join _orgunitstructure ous on psi.organisationunitid=ous.organisationunitid " +
+ "where psi.executiondate >= '" + start + "' " +
+ "and psi.executiondate <= '" + end + "' " +
+ "and pr.programid=" + table.getProgram().getId() + ";";
+
+ log.info( "Populate SQL: "+ sql );
+
+ jdbcTemplate.execute( sql );
+ }
+
+ return null;
+ }
+
+ public List<String[]> getDimensionColumns( AnalyticsTable table )
+ {
+ List<String[]> columns = new ArrayList<String[]>();
+
+ Collection<OrganisationUnitLevel> levels =
+ organisationUnitService.getOrganisationUnitLevels();
+
+ for ( OrganisationUnitLevel level : levels )
+ {
+ String column = PREFIX_ORGUNITLEVEL + level.getLevel();
+ String[] col = { column, "character(11)", "ous." + column };
+ columns.add( col );
+ }
+
+ for ( DataElement dataElement : table.getProgram().getAllDataElements() )
+ {
+ String select = "(select value from patientdatavalue where programstageinstanceid=" +
+ "psi.programstageinstanceid and dataelementid=" + dataElement.getId() + ") as " + dataElement.getUid();
+
+ String[] col = { dataElement.getUid(), "character(255)", select };
+ columns.add( col );
+ }
+
+ String[] psi = { "psi", "character(11) not null", "psi.uid" };
+ String[] ps = { "ps", "character(11) not null", "ps.uid" };
+ String[] ed = { "executiondate", "date", "psi.executiondate" };
+
+ columns.addAll( Arrays.asList( psi, ps, ed ) );
+
+ return columns;
+ }
+
+ public Date getEarliestData()
+ {
+ final String sql = "select min(pdv.timestamp) from patientdatavalue pdv";
+
+ return jdbcTemplate.queryForObject( sql, Date.class );
+ }
+
+ public Date getLatestData()
+ {
+ final String sql = "select max(pdv.timestamp) from patientdatavalue pdv";
+
+ return jdbcTemplate.queryForObject( sql, Date.class );
+ }
+
+ @Async
+ public Future<?> applyAggregationLevels( ConcurrentLinkedQueue<AnalyticsTable> tables, Collection<String> dataElements, int aggregationLevel )
+ {
+ return null; // Not relevant
+ }
+}
=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/resources/META-INF/dhis/beans.xml'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/resources/META-INF/dhis/beans.xml 2013-03-04 14:44:15 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/resources/META-INF/dhis/beans.xml 2013-08-21 10:08:02 +0000
@@ -8,6 +8,8 @@
<bean id="org.hisp.dhis.analytics.CompletenessTargetTableManager" class="org.hisp.dhis.analytics.table.JdbcCompletenessTargetTableManager" />
+ <bean id="org.hisp.dhis.analytics.EventAnalyticsTableManager" class="org.hisp.dhis.analytics.table.JdbcEventAnalyticsTableManager" />
+
<bean id="org.hisp.dhis.analytics.AnalyticsTableService" class="org.hisp.dhis.analytics.table.DefaultAnalyticsTableService">
<property name="tableManager" ref="org.hisp.dhis.analytics.AnalyticsTableManager" />
</bean>
@@ -20,6 +22,10 @@
<property name="tableManager" ref="org.hisp.dhis.analytics.CompletenessTargetTableManager" />
</bean>
+ <bean id="org.hisp.dhis.analytics.EventAnalyticsTableService" class="org.hisp.dhis.analytics.table.DefaultAnalyticsTableService">
+ <property name="tableManager" ref="org.hisp.dhis.analytics.EventAnalyticsTableManager" />
+ </bean>
+
<bean id="org.hisp.dhis.analytics.AnalyticsManager" class="org.hisp.dhis.analytics.data.JdbcAnalyticsManager" />
<bean id="org.hisp.dhis.analytics.AnalyticsService" class="org.hisp.dhis.analytics.data.DefaultAnalyticsService" />
=== 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 2013-01-16 18:23:37 +0000
+++ dhis-2/dhis-support/dhis-support-system/src/main/java/org/hisp/dhis/system/util/TextUtils.java 2013-08-21 11:56:16 +0000
@@ -136,6 +136,30 @@
}
/**
+ * Removes the last given number of characters from the given string. Returns
+ * null if the string is null. Returns an empty string if characters is less
+ * than zero or greater than the length of the string.
+ *
+ * @param string the string.
+ * @param characters number of characters to remove.
+ * @return the substring.
+ */
+ public static String removeLast( String string, int characters )
+ {
+ if ( string == null )
+ {
+ return null;
+ }
+
+ if ( characters < 0 || characters > string.length() )
+ {
+ return EMPTY;
+ }
+
+ return string.substring( 0, string.length() - characters );
+ }
+
+ /**
* Trims the given string from the end.
*
* @param value the value to trim.
