dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #20420
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 9311: Uids in analytics table
------------------------------------------------------------
revno: 9311
committer: Lars Helge Øverland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Thu 2012-12-13 21:45:26 +0100
message:
Uids in analytics table
modified:
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-administration/src/main/java/org/hisp/dhis/resourcetable/statement/CreateDataElementGroupSetTableStatement.java
dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/statement/CreateIndicatorGroupSetTableStatement.java
dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/statement/CreateOrganisationUnitGroupSetTableStatement.java
dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultAnalyticsService.java
dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/JdbcAnalyticsManager.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
--
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-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-13 18:44:23 +0000
+++ dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/DefaultResourceTableService.java 2012-12-13 20:45:26 +0000
@@ -244,7 +244,7 @@
DataElementGroup group = groupSet.getGroup( dataElement );
values.add( group != null ? group.getName() : null );
- values.add( group != null ? String.valueOf( group.getId() ) : null );
+ values.add( group != null ? group.getUid() : null );
}
batchHandler.addObject( values );
@@ -292,7 +292,7 @@
IndicatorGroup group = groupSet.getGroup( indicator );
values.add( group != null ? group.getName() : null );
- values.add( group != null ? String.valueOf( group.getId() ) : null );
+ values.add( group != null ? group.getUid() : null );
}
batchHandler.addObject( values );
@@ -342,7 +342,7 @@
OrganisationUnitGroup group = groupSet.getGroup( unit );
values.add( group != null ? group.getName() : null );
- values.add( group != null ? String.valueOf( group.getId() ) : null );
+ values.add( group != null ? group.getUid() : null );
}
batchHandler.addObject( values );
=== 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 18:44:23 +0000
+++ dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/jdbc/JdbcResourceTableStore.java 2012-12-13 20:45:26 +0000
@@ -86,8 +86,8 @@
for ( int k = 1 ; k <= maxLevel; k++ )
{
- String levelName = "idlevel" + String.valueOf( k );
- sql.append( ", " ).append ( levelName ).append (" INTEGER");
+ sql.append( ", " ).append( "idlevel" + k ).append (" INTEGER, " ).
+ append( "uidlevel" + k ).append( " CHARACTER(11)" );
}
sql.append( ");" );
=== modified file 'dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/statement/CreateDataElementGroupSetTableStatement.java'
--- dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/statement/CreateDataElementGroupSetTableStatement.java 2012-11-12 16:42:09 +0000
+++ dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/statement/CreateDataElementGroupSetTableStatement.java 2012-12-13 20:45:26 +0000
@@ -63,7 +63,7 @@
for ( DataElementGroupSet groupSet : groupSets )
{
statement += CodecUtils.databaseEncode( groupSet.getName() ) + SPACE + LONG_TEXT_COLUMN_TYPE + SEPARATOR;
- statement += "dgs_" + groupSet.getUid() + SPACE + "INTEGER" + SEPARATOR;
+ statement += "dgs_" + groupSet.getUid() + SPACE + "CHARACTER(11)" + SEPARATOR;
}
statement += "PRIMARY KEY ( dataelementid ) )";
=== modified file 'dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/statement/CreateIndicatorGroupSetTableStatement.java'
--- dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/statement/CreateIndicatorGroupSetTableStatement.java 2012-11-12 16:42:09 +0000
+++ dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/statement/CreateIndicatorGroupSetTableStatement.java 2012-12-13 20:45:26 +0000
@@ -63,7 +63,7 @@
for ( IndicatorGroupSet groupSet : groupSets )
{
statement += CodecUtils.databaseEncode( groupSet.getName() ) + SPACE + LONG_TEXT_COLUMN_TYPE + SEPARATOR;
- statement += "igs_" + groupSet.getUid() + SPACE + "INTEGER" + SEPARATOR;
+ statement += "igs_" + groupSet.getUid() + SPACE + "CHARACTER(11)" + SEPARATOR;
}
statement += "PRIMARY KEY ( indicatorid ) )";
=== modified file 'dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/statement/CreateOrganisationUnitGroupSetTableStatement.java'
--- dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/statement/CreateOrganisationUnitGroupSetTableStatement.java 2012-11-12 16:42:09 +0000
+++ dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/statement/CreateOrganisationUnitGroupSetTableStatement.java 2012-12-13 20:45:26 +0000
@@ -63,7 +63,7 @@
for ( OrganisationUnitGroupSet groupSet : groupSets )
{
statement += CodecUtils.databaseEncode( groupSet.getName() ) + SPACE + LONG_TEXT_COLUMN_TYPE + SEPARATOR;
- statement += "ougs_" + groupSet.getUid() + SPACE + "INTEGER" + SEPARATOR;
+ statement += "ougs_" + groupSet.getUid() + SPACE + "CHARACTER(11)" + SEPARATOR;
}
statement += "PRIMARY KEY ( organisationunitid ) )";
=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultAnalyticsService.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultAnalyticsService.java 2012-12-13 13:56:39 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultAnalyticsService.java 2012-12-13 20:45:26 +0000
@@ -44,6 +44,7 @@
//TODO period aggregation for multiple period types
//TODO hierarchy aggregation for org units at multiple levels
//TODO indicator aggregation
+ //TODO dimensional data analysis
@Autowired
private AnalyticsManager analyticsManager;
=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/JdbcAnalyticsManager.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/JdbcAnalyticsManager.java 2012-12-13 13:56:39 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/JdbcAnalyticsManager.java 2012-12-13 20:45:26 +0000
@@ -27,11 +27,9 @@
* SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/
-import static org.hisp.dhis.system.util.TextUtils.getCommaDelimitedString;
import static org.hisp.dhis.system.util.TextUtils.getQuotedCommaDelimitedString;
import java.util.List;
-import java.util.Set;
import java.util.concurrent.Future;
import org.apache.commons.logging.Log;
@@ -39,10 +37,7 @@
import org.hisp.dhis.aggregation.AggregatedDataValue;
import org.hisp.dhis.analytics.AnalyticsManager;
import org.hisp.dhis.analytics.DataQueryParams;
-import org.hisp.dhis.common.IdentifiableObjectManager;
-import org.hisp.dhis.dataelement.DataElement;
import org.hisp.dhis.expression.ExpressionService;
-import org.hisp.dhis.organisationunit.OrganisationUnit;
import org.hisp.dhis.organisationunit.OrganisationUnitService;
import org.hisp.dhis.period.PeriodService;
import org.hisp.dhis.period.PeriodType;
@@ -76,9 +71,6 @@
@Autowired
private ExpressionService expressionService;
- @Autowired
- private IdentifiableObjectManager idObjectManager;
-
// -------------------------------------------------------------------------
// Implementation
// -------------------------------------------------------------------------
@@ -90,15 +82,12 @@
String periodType = PeriodType.getPeriodTypeFromIsoString( params.getPeriods().iterator().next() ).getName().toLowerCase();
- Set<Integer> de = idObjectManager.convertToId( DataElement.class, params.getDataElements() );
- Set<Integer> ou = idObjectManager.convertToId( OrganisationUnit.class, params.getOrganisationUnits() );
-
final String sql =
- "SELECT dataelementid, 0 as categoryoptioncomboid, periodid, idlevel" + level + " as organisationunitid, SUM(value) as value " +
+ "SELECT dataelementid, 0 as categoryoptioncomboid, " + periodType + " as periodid, idlevel" + level + " as organisationunitid, SUM(value) as value " +
"FROM " + params.getTableName() + " " +
- "WHERE dataelementid IN ( " + getCommaDelimitedString( de ) + " ) " +
+ "WHERE dataelementid IN ( " + getQuotedCommaDelimitedString( params.getDataElements() ) + " ) " +
"AND " + periodType + " IN ( " + getQuotedCommaDelimitedString( params.getPeriods() ) + " ) " +
- "AND idlevel" + level + " IN ( " + getCommaDelimitedString( ou ) + " ) " +
+ "AND idlevel" + level + " IN ( " + getQuotedCommaDelimitedString( params.getOrganisationUnits() ) + " ) " +
"GROUP BY dataelementid, periodid, idlevel" + level;
log.info( sql );
=== 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 2012-12-11 20:46:36 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/DefaultAnalyticsTableService.java 2012-12-13 20:45:26 +0000
@@ -70,7 +70,7 @@
final List<String> tables = PartitionUtils.getTempTableNames( earliest, latest );
clock.logTime( "Checked data timespan" );
- //dropTables( tables ); //remove
+ //dropTables( tables );
createTables( tables );
clock.logTime( "Created analytics tables" );
=== 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-12 15:47:03 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcAnalyticsTableManager.java 2012-12-13 20:45:26 +0000
@@ -70,7 +70,7 @@
private static final Log log = LogFactory.getLog( JdbcAnalyticsTableManager.class );
public static final String PREFIX_ORGUNITGROUPSET = "ougs_";
- public static final String PREFIX_ORGUNITLEVEL = "idlevel";
+ public static final String PREFIX_ORGUNITLEVEL = "uidlevel";
public static final String PREFIX_INDEX = "index_";
@Autowired
@@ -169,7 +169,7 @@
for ( String[] col : getDimensionColumns() )
{
- select += col[2] + col[0] + ",";
+ select += col[2] + ",";
}
select = select.replace( "organisationunitid", "sourceid" ); // Legacy fix
@@ -180,6 +180,7 @@
"left join _orgunitstructure ous on dv.sourceid=ous.organisationunitid " +
"left join _period_no_disaggregation_structure 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 pe.startdate >= '" + start + "' " +
@@ -192,6 +193,13 @@
jdbcTemplate.execute( sql );
}
+ /**
+ * Returns a list of dimension columns. Each entry is an array with:
+ *
+ * 0 = column name
+ * 1 = data type
+ * 2 = column alias and value
+ */
public List<String[]> getDimensionColumns()
{
List<String[]> columns = new ArrayList<String[]>();
@@ -204,28 +212,29 @@
for ( OrganisationUnitGroupSet groupSet : orgUnitGroupSets )
{
- String[] col = { PREFIX_ORGUNITGROUPSET + groupSet.getUid(), "integer", "ougs." };
+ String column = PREFIX_ORGUNITGROUPSET + groupSet.getUid();
+ String[] col = { column, "character(11)", "ougs." + column };
columns.add( col );
}
for ( OrganisationUnitLevel level : levels )
{
- String[] col = { PREFIX_ORGUNITLEVEL + level.getLevel(), "integer", "ous." };
+ String column = PREFIX_ORGUNITLEVEL + level.getLevel();
+ String[] col = { column, "character(11)", "ous." + column };
columns.add( col );
}
for ( PeriodType periodType : PeriodType.getAvailablePeriodTypes().subList( 0, 7 ) )
{
- String[] col = { periodType.getName().toLowerCase(), "character varying(10)", "ps." };
+ String column = periodType.getName().toLowerCase();
+ String[] col = { column, "character varying(10)", "ps." + column };
columns.add( col );
}
- String[] de = { "dataelementid", "integer not null", "dv." };
- String[] pe = { "periodid", "integer not null", "dv." };
- String[] ou = { "organisationunitid", "integer not null", "dv." };
- String[] co = { "categoryoptioncomboid", "integer not null", "dv." };
+ String[] de = { "dataelement", "character(11) not null", "de.uid" };
+ String[] co = { "categoryoptioncombo", "character(11) not null", "coc.uid" };
- columns.addAll( Arrays.asList( de, pe, ou, co ) );
+ columns.addAll( Arrays.asList( de, co ) );
return columns;
}
