dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #20536
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 9405: Added column for value x days in period, useful for aggregating data for data elements with avera...
------------------------------------------------------------
revno: 9405
committer: Lars Helge Øverland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Thu 2012-12-27 19:15:23 +0100
message:
Added column for value x days in period, useful for aggregating data for data elements with average aggregation operator
modified:
dhis-2/dhis-api/src/main/java/org/hisp/dhis/resourcetable/ResourceTableService.java
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
dhis-2/dhis-services/dhis-service-analytics/src/test/java/org/hisp/dhis/analytics/data/QueryPlannerTest.java
dhis-2/dhis-web/dhis-web-api/src/main/java/org/hisp/dhis/api/controller/ResourceTableController.java
dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/java/org/hisp/dhis/dataadmin/action/resourcetable/GenerateResourceTableAction.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/resourcetable/ResourceTableService.java'
--- dhis-2/dhis-api/src/main/java/org/hisp/dhis/resourcetable/ResourceTableService.java 2012-11-25 20:54:29 +0000
+++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/resourcetable/ResourceTableService.java 2012-12-27 18:15:23 +0000
@@ -74,9 +74,6 @@
/**
* Generates a resource table for all periods.
- *
- * @param noDisaggregation whether to include period types with higher
- * frequency order than the period itself.
*/
- void generatePeriodTable( boolean noDisaggregation );
+ void generatePeriodTable();
}
=== 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-12-25 18:30:44 +0000
+++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/resourcetable/ResourceTableStore.java 2012-12-27 18:15:23 +0000
@@ -105,5 +105,5 @@
/**
* Creates table.
*/
- void createPeriodStructure( boolean noDisaggregation );
+ void createPeriodStructure();
}
=== 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-26 09:09:06 +0000
+++ dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/DefaultResourceTableService.java 2012-12-27 18:15:23 +0000
@@ -27,6 +27,8 @@
* SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/
+import static org.hisp.dhis.resourcetable.ResourceTableStore.TABLE_NAME_PERIOD_STRUCTURE;
+
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
@@ -63,9 +65,6 @@
import org.hisp.dhis.resourcetable.statement.CreateDataElementGroupSetTableStatement;
import org.hisp.dhis.resourcetable.statement.CreateIndicatorGroupSetTableStatement;
import org.hisp.dhis.resourcetable.statement.CreateOrganisationUnitGroupSetTableStatement;
-import org.hisp.dhis.system.util.DateUtils;
-
-import static org.hisp.dhis.resourcetable.ResourceTableStore.*;
/**
* @author Lars Helge Overland
@@ -444,7 +443,7 @@
// PeriodTable
// -------------------------------------------------------------------------
- public void generatePeriodTable( boolean noDisaggregation )
+ public void generatePeriodTable()
{
// ---------------------------------------------------------------------
// Create table
@@ -452,16 +451,14 @@
Collection<Period> periods = periodService.getAllPeriods();
- resourceTableStore.createPeriodStructure( noDisaggregation );
+ resourceTableStore.createPeriodStructure();
// ---------------------------------------------------------------------
// Populate table
// ---------------------------------------------------------------------
-
- String tableName = noDisaggregation ? TABLE_NAME_PERIOD_NO_DISAGGREGATION_STRUCTURE : TABLE_NAME_PERIOD_STRUCTURE;
BatchHandler<Object> batchHandler = batchHandlerFactory.createBatchHandler( GenericBatchHandler.class ).
- setTableName( tableName ).init();
+ setTableName( TABLE_NAME_PERIOD_STRUCTURE ).init();
for ( Period period : periods )
{
@@ -470,15 +467,13 @@
final List<String> values = new ArrayList<String>();
- int days = DateUtils.daysBetween( period.getStartDate(), period.getEndDate() ) + 1;
-
values.add( String.valueOf( period.getId() ) );
values.add( period.getIsoDate() );
- values.add( String.valueOf( days ) );
+ values.add( String.valueOf( rowType.getFrequencyOrder() ) );
for ( PeriodType periodType : PeriodType.PERIOD_TYPES )
{
- if ( rowType.getFrequencyOrder() <= periodType.getFrequencyOrder() || !noDisaggregation )
+ if ( rowType.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-26 09:09:06 +0000
+++ dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/jdbc/JdbcResourceTableStore.java 2012-12-27 18:15:23 +0000
@@ -227,20 +227,18 @@
// PeriodTable
// -------------------------------------------------------------------------
- public void createPeriodStructure( boolean noDisaggregation )
+ public void createPeriodStructure()
{
- String tableName = noDisaggregation ? TABLE_NAME_PERIOD_NO_DISAGGREGATION_STRUCTURE : TABLE_NAME_PERIOD_STRUCTURE;
-
try
{
- jdbcTemplate.update( "DROP TABLE " + tableName );
+ jdbcTemplate.update( "DROP TABLE " + TABLE_NAME_PERIOD_STRUCTURE );
}
catch ( BadSqlGrammarException ex )
{
// Do nothing, table does not exist
}
- String sql = "CREATE TABLE " + tableName + " (periodid INTEGER NOT NULL PRIMARY KEY, iso VARCHAR(10) NOT NULL, daysno INTEGER NOT NULL";
+ String sql = "CREATE TABLE " + TABLE_NAME_PERIOD_STRUCTURE + " (periodid INTEGER NOT NULL PRIMARY KEY, iso VARCHAR(10) NOT NULL, daysno INTEGER NOT NULL";
for ( PeriodType periodType : PeriodType.PERIOD_TYPES )
{
=== 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-25 18:30:44 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcAnalyticsTableManager.java 2012-12-27 18:15:23 +0000
@@ -114,7 +114,7 @@
sqlCreate += col[0] + " " + col[1] + ",";
}
- sqlCreate += "value double precision)";
+ sqlCreate += "daysxvalue double precision, value double precision)";
log.info( "Create SQL: " + sqlCreate );
@@ -156,14 +156,14 @@
@Async
public Future<?> populateTableAsync( String tableName, Date startDate, Date endDate )
{
- populateSumTable( tableName, startDate, endDate, "cast(dv.value as double precision)", "int" );
+ populateTable( tableName, startDate, endDate, "cast(dv.value as double precision)", "int" );
- populateSumTable( tableName, startDate, endDate, "1 as value" , "bool" );
+ populateTable( tableName, startDate, endDate, "1" , "bool" );
return null;
}
- private void populateSumTable( String tableName, Date startDate, Date endDate, String valueExpression, String valueType )
+ private void populateTable( String tableName, Date startDate, Date endDate, String valueExpression, String valueType )
{
final String start = DateUtils.getMediumDateString( startDate );
final String end = DateUtils.getMediumDateString( endDate );
@@ -175,7 +175,7 @@
insert += col[0] + ",";
}
- insert += "value) ";
+ insert += "daysxvalue, value) ";
String select = "select ";
@@ -186,17 +186,18 @@
select = select.replace( "organisationunitid", "sourceid" ); // Legacy fix
- select += valueExpression + " " +
+ select +=
+ valueExpression + " * ps.daysno as value, " +
+ valueExpression + " as value " +
"from datavalue dv " +
"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 _periodstructure 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 = 'sum' " +
"and pe.startdate >= '" + start + "' " +
"and pe.startdate <= '" + end + "'" +
"and dv.value != ''" +
=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/test/java/org/hisp/dhis/analytics/data/QueryPlannerTest.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/test/java/org/hisp/dhis/analytics/data/QueryPlannerTest.java 2012-12-21 16:50:36 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/test/java/org/hisp/dhis/analytics/data/QueryPlannerTest.java 2012-12-27 18:15:23 +0000
@@ -38,6 +38,8 @@
import org.hisp.dhis.DhisSpringTest;
import org.hisp.dhis.analytics.DataQueryParams;
import org.hisp.dhis.analytics.QueryPlanner;
+import org.hisp.dhis.dataelement.DataElement;
+import org.hisp.dhis.dataelement.DataElementService;
import org.hisp.dhis.organisationunit.OrganisationUnit;
import org.hisp.dhis.organisationunit.OrganisationUnitService;
import org.hisp.dhis.period.Cal;
@@ -52,12 +54,20 @@
private QueryPlanner queryPlanner;
@Autowired
+ private DataElementService dataElementService;
+
+ @Autowired
private OrganisationUnitService organisationUnitService;
// -------------------------------------------------------------------------
// Fixture
// -------------------------------------------------------------------------
+ private DataElement deA;
+ private DataElement deB;
+ private DataElement deC;
+ private DataElement deD;
+
private OrganisationUnit ouA;
private OrganisationUnit ouB;
private OrganisationUnit ouC;
@@ -67,6 +77,16 @@
@Override
public void setUpTest()
{
+ deA = createDataElement( 'A' );
+ deB = createDataElement( 'B' );
+ deC = createDataElement( 'C' );
+ deD = createDataElement( 'D' );
+
+ dataElementService.addDataElement( deA );
+ dataElementService.addDataElement( deB );
+ dataElementService.addDataElement( deC );
+ dataElementService.addDataElement( deD );
+
ouA = createOrganisationUnit( 'A' );
ouB = createOrganisationUnit( 'B' );
ouC = createOrganisationUnit( 'C' );
@@ -93,7 +113,7 @@
public void planQueryA()
{
DataQueryParams params = new DataQueryParams();
- params.setDataElements( Arrays.asList( "a", "b", "c", "d" ) );
+ params.setDataElements( Arrays.asList( deA.getUid(), deB.getUid(), deC.getUid(), deD.getUid() ) );
params.setOrganisationUnits( Arrays.asList( ouA.getUid(), ouB.getUid(), ouC.getUid(), ouD.getUid(), ouE.getUid() ) );
params.setPeriods( Arrays.asList( "2000Q1", "2000Q2", "2000Q3", "2000Q4", "2001Q1", "2001Q2" ) );
@@ -117,7 +137,7 @@
public void planQueryB()
{
DataQueryParams params = new DataQueryParams();
- params.setDataElements( Arrays.asList( "a", "b", "c", "d" ) );
+ params.setDataElements( Arrays.asList( deA.getUid(), deB.getUid(), deC.getUid(), deD.getUid() ) );
params.setOrganisationUnits( Arrays.asList( ouA.getUid(), ouB.getUid(), ouC.getUid(), ouD.getUid(), ouE.getUid() ) );
params.setPeriods( Arrays.asList( "2000Q1", "2000Q2", "2000", "200002", "200003", "200004" ) );
@@ -155,7 +175,7 @@
organisationUnitService.updateOrganisationUnit( ouE );
DataQueryParams params = new DataQueryParams();
- params.setDataElements( Arrays.asList( "a", "b", "c", "d" ) );
+ params.setDataElements( Arrays.asList( deA.getUid(), deB.getUid(), deC.getUid(), deD.getUid() ) );
params.setOrganisationUnits( Arrays.asList( ouA.getUid(), ouB.getUid(), ouC.getUid(), ouD.getUid(), ouE.getUid() ) );
params.setPeriods( Arrays.asList( "2000Q1", "2000Q2", "2000Q3" ) );
@@ -177,7 +197,7 @@
public void planQueryD()
{
DataQueryParams params = new DataQueryParams();
- params.setDataElements( Arrays.asList( "a", "b", "c" ) );
+ params.setDataElements( Arrays.asList( deA.getUid(), deB.getUid(), deC.getUid() ) );
params.setOrganisationUnits( Arrays.asList( ouA.getUid() ) );
params.setPeriods( Arrays.asList( "200001", "200002", "200003", "200004", "200005", "200006", "200007", "200008", "200009" ) );
@@ -199,7 +219,7 @@
public void planQueryE()
{
DataQueryParams params = new DataQueryParams();
- params.setDataElements( Arrays.asList( "a", "b", "c" ) );
+ params.setDataElements( Arrays.asList( deA.getUid(), deB.getUid(), deC.getUid() ) );
params.setPeriods( Arrays.asList( "200001", "200002", "200003", "200004", "200005", "200006", "200007", "200008", "200009" ) );
List<DataQueryParams> queries = queryPlanner.planQuery( params, 4 );
@@ -241,7 +261,7 @@
public void planQueryG()
{
DataQueryParams params = new DataQueryParams();
- params.setDataElements( Arrays.asList( "a", "b", "c" ) );
+ params.setDataElements( Arrays.asList( deA.getUid(), deB.getUid(), deC.getUid() ) );
params.setOrganisationUnits( Arrays.asList( ouA.getUid(), ouB.getUid(), ouC.getUid(), ouD.getUid(), ouE.getUid() ) );
queryPlanner.planQuery( params, 4 );
@@ -256,7 +276,7 @@
public void planQueryH()
{
DataQueryParams params = new DataQueryParams();
- params.setDataElements( Arrays.asList( "a", "b", "c", "d" ) );
+ params.setDataElements( Arrays.asList( deA.getUid(), deB.getUid(), deC.getUid(), deD.getUid() ) );
params.setOrganisationUnits( Arrays.asList( ouA.getUid(), ouB.getUid(), ouC.getUid(), ouD.getUid(), ouE.getUid() ) );
params.setFilterPeriods( Arrays.asList( "2000Q1", "2000Q2", "2000Q3", "2000Q4", "2001Q1", "2001Q2" ) );
@@ -274,7 +294,7 @@
public void planQueryI()
{
DataQueryParams params = new DataQueryParams();
- params.setDataElements( Arrays.asList( "a", "b", "c", "d" ) );
+ params.setDataElements( Arrays.asList( deA.getUid(), deB.getUid(), deC.getUid(), deD.getUid() ) );
params.setFilterOrganisationUnits( Arrays.asList( ouA.getUid(), ouB.getUid(), ouC.getUid(), ouD.getUid(), ouE.getUid() ) );
params.setPeriods( Arrays.asList( "2000Q1", "2000Q2", "2000", "200002", "200003", "200004" ) );
=== modified file 'dhis-2/dhis-web/dhis-web-api/src/main/java/org/hisp/dhis/api/controller/ResourceTableController.java'
--- dhis-2/dhis-web/dhis-web-api/src/main/java/org/hisp/dhis/api/controller/ResourceTableController.java 2012-12-14 17:23:37 +0000
+++ dhis-2/dhis-web/dhis-web-api/src/main/java/org/hisp/dhis/api/controller/ResourceTableController.java 2012-12-27 18:15:23 +0000
@@ -79,8 +79,7 @@
resourceTableService.generateIndicatorGroupSetTable();
resourceTableService.generateOrganisationUnitGroupSetTable();
resourceTableService.generateOrganisationUnitStructures();
- resourceTableService.generatePeriodTable( true );
- resourceTableService.generatePeriodTable( false );
+ resourceTableService.generatePeriodTable();
sqlViewService.createAllViewTables();
=== modified file 'dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/java/org/hisp/dhis/dataadmin/action/resourcetable/GenerateResourceTableAction.java'
--- dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/java/org/hisp/dhis/dataadmin/action/resourcetable/GenerateResourceTableAction.java 2012-11-25 20:54:29 +0000
+++ dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/java/org/hisp/dhis/dataadmin/action/resourcetable/GenerateResourceTableAction.java 2012-12-27 18:15:23 +0000
@@ -169,8 +169,7 @@
if ( periodStructure )
{
- resourceTableService.generatePeriodTable( false );
- resourceTableService.generatePeriodTable( true );
+ resourceTableService.generatePeriodTable();
}
log.info( "Generated resource tables" );
