dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #20518
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 9392: Analytics, impl support for data elements with average aggregation operator, step 1
------------------------------------------------------------
revno: 9392
committer: Lars Helge Øverland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Sun 2012-12-23 15:38:42 +0100
message:
Analytics, impl support for data elements with average aggregation operator, step 1
modified:
dhis-2/dhis-api/src/main/java/org/hisp/dhis/resourcetable/ResourceTableStore.java
dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/DefaultResourceTableService.java
dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/jdbc/JdbcResourceTableStore.java
dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcAnalyticsTableManager.java
resources/sql/div.sql
--
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/resourcetable/ResourceTableStore.java'
--- dhis-2/dhis-api/src/main/java/org/hisp/dhis/resourcetable/ResourceTableStore.java 2012-11-25 21:24:19 +0000
+++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/resourcetable/ResourceTableStore.java 2012-12-23 14:38:42 +0000
@@ -44,8 +44,8 @@
final String TABLE_NAME_CATEGORY_OPTION_COMBO_NAME = "_categoryoptioncomboname";
final String TABLE_NAME_ORGANISATION_UNIT_STRUCTURE = "_orgunitstructure";
final String TABLE_NAME_DATA_ELEMENT_STRUCTURE = "_dataelementstructure";
- final String TABLE_NAME_PERIOD_STRUCTURE = "_periodstructure";
- final String TABLE_NAME_PERIOD_NO_DISAGGREGATION_STRUCTURE = "_period_no_disagg_structure";
+ final String TABLE_NAME_PERIOD_AGGREGATION_STRUCTURE = "_period_aggregation_structure";
+ final String TABLE_NAME_PERIOD_DISAGGREGATION_STRUCTURE = "_period_disaggregation_structure";
// -------------------------------------------------------------------------
// OrganisationUnitStructure
=== modified file 'dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/DefaultResourceTableService.java'
--- dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/DefaultResourceTableService.java 2012-12-14 09:25:29 +0000
+++ dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/DefaultResourceTableService.java 2012-12-23 14:38:42 +0000
@@ -443,7 +443,7 @@
// PeriodTable
// -------------------------------------------------------------------------
- public void generatePeriodTable( boolean noDisaggregation )
+ public void generatePeriodTable( boolean disAggregation )
{
// ---------------------------------------------------------------------
// Create table
@@ -451,13 +451,13 @@
Collection<Period> periods = periodService.getAllPeriods();
- resourceTableStore.createPeriodStructure( noDisaggregation );
+ resourceTableStore.createPeriodStructure( disAggregation );
// ---------------------------------------------------------------------
// Populate table
// ---------------------------------------------------------------------
- String tableName = noDisaggregation ? TABLE_NAME_PERIOD_NO_DISAGGREGATION_STRUCTURE : TABLE_NAME_PERIOD_STRUCTURE;
+ String tableName = disAggregation ? TABLE_NAME_PERIOD_DISAGGREGATION_STRUCTURE : TABLE_NAME_PERIOD_AGGREGATION_STRUCTURE;
BatchHandler<Object> batchHandler = batchHandlerFactory.createBatchHandler( GenericBatchHandler.class ).
setTableName( tableName ).init();
@@ -465,7 +465,7 @@
for ( Period period : periods )
{
final Date startDate = period.getStartDate();
- final PeriodType rowType = period.getPeriodType();
+ final PeriodType rowPeriodType = period.getPeriodType();
final List<String> values = new ArrayList<String>();
@@ -473,7 +473,11 @@
for ( PeriodType periodType : PeriodType.PERIOD_TYPES )
{
- if ( rowType.getFrequencyOrder() <= periodType.getFrequencyOrder() || !noDisaggregation )
+ if ( !disAggregation && ( rowPeriodType.getFrequencyOrder() <= periodType.getFrequencyOrder() ) )
+ {
+ values.add( periodType.createPeriod( startDate ).getIsoDate() );
+ }
+ else if ( disAggregation && ( rowPeriodType.getFrequencyOrder() >= periodType.getFrequencyOrder() ) )
{
values.add( periodType.createPeriod( startDate ).getIsoDate() );
}
=== modified file 'dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/jdbc/JdbcResourceTableStore.java'
--- dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/jdbc/JdbcResourceTableStore.java 2012-12-13 20:45:26 +0000
+++ dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/jdbc/JdbcResourceTableStore.java 2012-12-23 14:38:42 +0000
@@ -227,9 +227,9 @@
// PeriodTable
// -------------------------------------------------------------------------
- public void createPeriodStructure( boolean noDisaggregation )
+ public void createPeriodStructure( boolean disAggregation )
{
- String tableName = noDisaggregation ? TABLE_NAME_PERIOD_NO_DISAGGREGATION_STRUCTURE : TABLE_NAME_PERIOD_STRUCTURE;
+ String tableName = disAggregation ? TABLE_NAME_PERIOD_DISAGGREGATION_STRUCTURE : TABLE_NAME_PERIOD_AGGREGATION_STRUCTURE;
try
{
@@ -249,6 +249,8 @@
sql += ")";
+ log.info( "Create period structure SQL: " + sql );
+
jdbcTemplate.update( 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-22 18:09:46 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcAnalyticsTableManager.java 2012-12-23 14:38:42 +0000
@@ -27,6 +27,12 @@
* SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/
+import static org.hisp.dhis.dataelement.DataElement.AGGREGATION_OPERATOR_AVERAGE;
+import static org.hisp.dhis.dataelement.DataElement.AGGREGATION_OPERATOR_SUM;
+import static org.hisp.dhis.dataelement.DataElement.VALUE_TYPE_BOOL;
+import static org.hisp.dhis.dataelement.DataElement.VALUE_TYPE_INT;
+import static org.hisp.dhis.system.util.TextUtils.getQuotedCommaDelimitedString;
+
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
@@ -53,8 +59,6 @@
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.scheduling.annotation.Async;
-import static org.hisp.dhis.system.util.TextUtils.getQuotedCommaDelimitedString;
-
/**
* This class manages the analytics table. The analytics table is a denormalized
* table designed for analysis which contains raw data values. It has columns for
@@ -156,17 +160,22 @@
@Async
public Future<?> populateTableAsync( String tableName, Date startDate, Date endDate )
{
- populateTable( tableName, startDate, endDate, "cast(dv.value as double precision)", "int" );
-
- populateTable( tableName, startDate, endDate, "1 as value" , "bool" );
+ populateTable( tableName, startDate, endDate, "cast(dv.value as double precision)", VALUE_TYPE_INT, AGGREGATION_OPERATOR_SUM );
+
+ populateTable( tableName, startDate, endDate, "1 as value" , VALUE_TYPE_BOOL, AGGREGATION_OPERATOR_SUM );
+
+ populateTable( tableName, startDate, endDate, "cast(dv.value as double precision)", VALUE_TYPE_INT, AGGREGATION_OPERATOR_AVERAGE );
+
+ populateTable( tableName, startDate, endDate, "1 as value" , VALUE_TYPE_BOOL, AGGREGATION_OPERATOR_AVERAGE );
return null;
}
- private void populateTable( String tableName, Date startDate, Date endDate, String valueExpression, String valueType )
+ private void populateTable( String tableName, Date startDate, Date endDate, String valueExpression, String valueType, String aggregationType )
{
final String start = DateUtils.getMediumDateString( startDate );
- final String end = DateUtils.getMediumDateString( endDate );
+ final String end = DateUtils.getMediumDateString( endDate );
+ final String periodTable = AGGREGATION_OPERATOR_SUM.equals( aggregationType ) ? "_period_aggregation_structure" : "_period_disaggregation_structure";
String insert = "insert into " + tableName + " (";
@@ -191,11 +200,12 @@
"left join _dataelementgroupsetstructure degs on dv.dataelementid=degs.dataelementid " +
"left join _organisationunitgroupsetstructure ougs on dv.sourceid=ougs.organisationunitid " +
"left join _orgunitstructure ous on dv.sourceid=ous.organisationunitid " +
- "left join _period_no_disagg_structure ps on dv.periodid=ps.periodid " +
+ "left join " + periodTable + " ps on dv.periodid=ps.periodid " +
"left join dataelement de on dv.dataelementid=de.dataelementid " +
"left join categoryoptioncombo coc on dv.categoryoptioncomboid=coc.categoryoptioncomboid " +
"left join period pe on dv.periodid=pe.periodid " +
"where de.valuetype='" + valueType + "' " +
+ "and de.aggregationtype = '" + aggregationType + "' " +
"and pe.startdate >= '" + start + "' " +
"and pe.startdate <= '" + end + "'" +
"and dv.value != ''" +
=== modified file 'resources/sql/div.sql'
--- resources/sql/div.sql 2012-12-07 14:13:31 +0000
+++ resources/sql/div.sql 2012-12-23 14:38:42 +0000
@@ -9,11 +9,12 @@
-- Data elements and frequency with average agg operator (higher than yearly negative for data mart performance)
-select d.dataelementid, d.name, pt.name from dataelement d
+select d.dataelementid, d.name as dataelement, pt.name as periodtype from dataelement d
join datasetmembers dsm on d.dataelementid=dsm.dataelementid
join dataset ds on dsm.datasetid=ds.datasetid
join periodtype pt on ds.periodtypeid = pt.periodtypeid
-where d.aggregationtype = 'average';
+where d.aggregationtype = 'average'
+order by pt.name;
-- Data elements with aggregation levels
