dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #17608
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 7069: Centralized jdbc code for report table and chart
------------------------------------------------------------
revno: 7069
committer: Lars Helge Overland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Mon 2012-05-28 22:41:19 +0200
message:
Centralized jdbc code for report table and chart
modified:
dhis-2/dhis-api/src/main/java/org/hisp/dhis/chart/Chart.java
dhis-2/dhis-api/src/main/java/org/hisp/dhis/common/BaseNameableObject.java
dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/reporttable/jdbc/JDBCReportTableManager.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/chart/Chart.java'
--- dhis-2/dhis-api/src/main/java/org/hisp/dhis/chart/Chart.java 2012-05-28 18:33:16 +0000
+++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/chart/Chart.java 2012-05-28 20:41:19 +0000
@@ -203,6 +203,12 @@
return organisationUnits;
}
}
+
+ public OrganisationUnit getFirstOrganisationUnit()
+ {
+ List<OrganisationUnit> units = getAllOrganisationUnits();
+ return units != null && !units.isEmpty() ? units.iterator().next() : null;
+ }
private List<NameableObject> dimensionToList( String dimension )
{
@@ -244,10 +250,6 @@
}
}
- // -------------------------------------------------------------------------
- // Logic
- // -------------------------------------------------------------------------
-
/**
* Indicates whether this report table is based on organisation unit groups
* or the organisation unit hierarchy.
@@ -256,7 +258,7 @@
{
return organisationUnitGroupSet != null && organisationUnitGroupSet.getOrganisationUnitGroups() != null;
}
-
+
public void removeAllOrganisationUnits()
{
organisationUnits.clear();
=== modified file 'dhis-2/dhis-api/src/main/java/org/hisp/dhis/common/BaseNameableObject.java'
--- dhis-2/dhis-api/src/main/java/org/hisp/dhis/common/BaseNameableObject.java 2012-05-16 09:54:13 +0000
+++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/common/BaseNameableObject.java 2012-05-28 20:41:19 +0000
@@ -27,13 +27,13 @@
* SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/
-import com.fasterxml.jackson.annotation.JsonIgnore;
+import org.hisp.dhis.common.view.DetailedView;
+import org.hisp.dhis.common.view.ExportView;
+
import com.fasterxml.jackson.annotation.JsonProperty;
import com.fasterxml.jackson.annotation.JsonView;
import com.fasterxml.jackson.dataformat.xml.annotation.JacksonXmlProperty;
import com.fasterxml.jackson.dataformat.xml.annotation.JacksonXmlRootElement;
-import org.hisp.dhis.common.view.DetailedView;
-import org.hisp.dhis.common.view.ExportView;
/**
* @author Bob Jolliffe
=== modified file 'dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/reporttable/jdbc/JDBCReportTableManager.java'
--- dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/reporttable/jdbc/JDBCReportTableManager.java 2012-03-18 12:41:41 +0000
+++ dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/reporttable/jdbc/JDBCReportTableManager.java 2012-05-28 20:41:19 +0000
@@ -29,12 +29,15 @@
import static org.hisp.dhis.reporttable.ReportTable.getIdentifier;
+import java.util.Collection;
import java.util.HashMap;
+import java.util.List;
import java.util.Map;
import org.hisp.dhis.chart.Chart;
import org.hisp.dhis.common.NameableObject;
import org.hisp.dhis.dataelement.DataElement;
+import org.hisp.dhis.dataelement.DataElementCategoryCombo;
import org.hisp.dhis.dataelement.DataElementCategoryOption;
import org.hisp.dhis.dataelement.DataElementCategoryOptionCombo;
import org.hisp.dhis.dataset.DataSet;
@@ -74,32 +77,53 @@
{
if ( reportTable.isOrganisationUnitGroupBased() )
{
- return getAggregatedValueMapOrgUnitGroups( reportTable );
+ return getAggregatedValueMapOrgUnitGroups( reportTable.getDataElements(), reportTable.getIndicators(),
+ reportTable.getAllPeriods(), reportTable.getAllUnits(), reportTable.getParentOrganisationUnit() );
}
else
{
- return getAggregatedValueMapOrgUnitHierarchy( reportTable );
+ return getAggregatedValueMapOrgUnitHierarchy( reportTable.getDataElements(), reportTable.getIndicators(), reportTable.getDataSets(),
+ reportTable.getAllPeriods(), reportTable.getAllUnits(), reportTable.getCategoryCombo(), reportTable.isDimensional(), reportTable.doTotal() );
}
}
- public Map<String, Double> getAggregatedValueMapOrgUnitGroups( ReportTable reportTable )
+ public Map<String, Double> getAggregatedValueMap( Chart chart )
+ {
+ if ( chart.isOrganisationUnitGroupBased() )
+ {
+ return getAggregatedValueMapOrgUnitGroups( chart.getDataElements(), chart.getIndicators(),
+ chart.getRelativePeriods(), chart.getOrganisationUnitGroupSet().getOrganisationUnitGroups(), chart.getFirstOrganisationUnit() );
+ }
+ else
+ {
+ return getAggregatedValueMapOrgUnitHierarchy( chart.getDataElements(), chart.getIndicators(), chart.getDataSets(),
+ chart.getRelativePeriods(), chart.getAllOrganisationUnits(), null, false, false );
+ }
+ }
+
+ // -------------------------------------------------------------------------
+ // Org unit groups
+ // -------------------------------------------------------------------------
+
+ private Map<String, Double> getAggregatedValueMapOrgUnitGroups( List<DataElement> dataElements, List<Indicator> indicators,
+ List<Period> periods, Collection<? extends NameableObject> groups, OrganisationUnit organisationUnit )
{
Map<String, Double> map = new HashMap<String, Double>();
String dataElementIds = TextUtils.getCommaDelimitedString(
- ConversionUtils.getIdentifiers( DataElement.class, reportTable.getDataElements() ) );
+ ConversionUtils.getIdentifiers( DataElement.class, dataElements ) );
String indicatorIds = TextUtils.getCommaDelimitedString(
- ConversionUtils.getIdentifiers( Indicator.class, reportTable.getIndicators() ) );
+ ConversionUtils.getIdentifiers( Indicator.class, indicators ) );
String periodIds = TextUtils.getCommaDelimitedString(
- ConversionUtils.getIdentifiers( Period.class, reportTable.getAllPeriods() ) );
- String unitIds = TextUtils.getCommaDelimitedString(
- ConversionUtils.getIdentifiers( NameableObject.class, reportTable.getAllUnits() ) );
+ ConversionUtils.getIdentifiers( Period.class, periods ) );
+ String groupIds = TextUtils.getCommaDelimitedString(
+ ConversionUtils.getIdentifiers( NameableObject.class, groups ) );
- if ( reportTable.hasDataElements() )
+ if ( dataElementIds != null && !dataElementIds.isEmpty() )
{
final String sql = "SELECT dataelementid, periodid, organisationunitgroupid, SUM(value) FROM aggregatedorgunitdatavalue " +
- "WHERE dataelementid IN (" + dataElementIds + ") AND periodid IN (" + periodIds + ") AND organisationunitgroupid IN (" + unitIds + ") " +
- "AND organisationunitid = " + reportTable.getParentOrganisationUnit().getId() + " " +
+ "WHERE dataelementid IN (" + dataElementIds + ") AND periodid IN (" + periodIds + ") AND organisationunitgroupid IN (" + groupIds + ") " +
+ "AND organisationunitid = " + organisationUnit.getId() + " " +
"GROUP BY dataelementid, periodid, organisationunitgroupid"; // Sum of category option combos
SqlRowSet rowSet = jdbcTemplate.queryForRowSet( sql );
@@ -114,11 +138,11 @@
}
}
- if ( reportTable.hasIndicators() )
+ if ( indicatorIds != null && !indicatorIds.isEmpty() )
{
final String sql = "SELECT indicatorid, periodid, organisationunitgroupid, value FROM aggregatedorgunitindicatorvalue " +
- "WHERE indicatorid IN (" + indicatorIds + ") AND periodid IN (" + periodIds + ") AND organisationunitgroupid IN (" + unitIds + ") " +
- "AND organisationunitid = " + reportTable.getParentOrganisationUnit().getId();
+ "WHERE indicatorid IN (" + indicatorIds + ") AND periodid IN (" + periodIds + ") AND organisationunitgroupid IN (" + groupIds + ") " +
+ "AND organisationunitid = " + organisationUnit.getId();
SqlRowSet rowSet = jdbcTemplate.queryForRowSet( sql );
@@ -134,23 +158,29 @@
return map;
}
-
- public Map<String, Double> getAggregatedValueMapOrgUnitHierarchy( ReportTable reportTable )
+
+ // -------------------------------------------------------------------------
+ // Org unit hierarchy
+ // -------------------------------------------------------------------------
+
+ private Map<String, Double> getAggregatedValueMapOrgUnitHierarchy( List<DataElement> dataElements, List<Indicator> indicators,
+ List<DataSet> dataSets, List<Period> periods, Collection<? extends NameableObject> organisationUnits, DataElementCategoryCombo categoryCombo,
+ boolean isDimensional, boolean doTotal )
{
Map<String, Double> map = new HashMap<String, Double>();
String dataElementIds = TextUtils.getCommaDelimitedString(
- ConversionUtils.getIdentifiers( DataElement.class, reportTable.getDataElements() ) );
+ ConversionUtils.getIdentifiers( DataElement.class, dataElements ) );
String indicatorIds = TextUtils.getCommaDelimitedString(
- ConversionUtils.getIdentifiers( Indicator.class, reportTable.getIndicators() ) );
+ ConversionUtils.getIdentifiers( Indicator.class, indicators ) );
String dataSetIds = TextUtils.getCommaDelimitedString(
- ConversionUtils.getIdentifiers( DataSet.class,reportTable.getDataSets() ) );
+ ConversionUtils.getIdentifiers( DataSet.class, dataSets ) );
String periodIds = TextUtils.getCommaDelimitedString(
- ConversionUtils.getIdentifiers( Period.class, reportTable.getAllPeriods() ) );
+ ConversionUtils.getIdentifiers( Period.class, periods ) );
String unitIds = TextUtils.getCommaDelimitedString(
- ConversionUtils.getIdentifiers( NameableObject.class, reportTable.getAllUnits() ) );
+ ConversionUtils.getIdentifiers( NameableObject.class, organisationUnits ) );
- if ( reportTable.hasDataElements() )
+ if ( dataElementIds != null && !dataElementIds.isEmpty() )
{
final String sql = "SELECT dataelementid, periodid, organisationunitid, SUM(value) FROM aggregateddatavalue " +
"WHERE dataelementid IN (" + dataElementIds + ") AND periodid IN (" + periodIds + ") AND organisationunitid IN (" + unitIds + ") " +
@@ -168,7 +198,7 @@
}
}
- if ( reportTable.hasIndicators() )
+ if ( indicatorIds != null && !indicatorIds.isEmpty() )
{
final String sql = "SELECT indicatorid, periodid, organisationunitid, value FROM aggregatedindicatorvalue " +
"WHERE indicatorid IN (" + indicatorIds + ") AND periodid IN (" + periodIds + ") AND organisationunitid IN (" + unitIds + ")";
@@ -185,7 +215,7 @@
}
}
- if ( reportTable.hasDataSets() )
+ if ( dataSetIds != null && !dataSetIds.isEmpty() )
{
final String sql = "SELECT datasetid, periodid, organisationunitid, value FROM aggregateddatasetcompleteness " +
"WHERE datasetid IN (" + dataSetIds + ") AND periodid IN (" + periodIds + ") AND organisationunitid IN (" + unitIds + ")";
@@ -202,7 +232,7 @@
}
}
- if ( reportTable.isDimensional() )
+ if ( isDimensional )
{
final String sql = "SELECT dataelementid, categoryoptioncomboid, periodid, organisationunitid, value FROM aggregateddatavalue " +
"WHERE dataelementid IN (" + dataElementIds + ") AND periodid IN (" + periodIds + ") AND organisationunitid IN (" + unitIds + ")";
@@ -220,9 +250,9 @@
}
}
- if ( reportTable.doTotal() )
+ if ( doTotal )
{
- for ( DataElementCategoryOption categoryOption : reportTable.getCategoryCombo().getCategoryOptions() )
+ for ( DataElementCategoryOption categoryOption : categoryCombo.getCategoryOptions() ) //categorycombo
{
String cocIds = TextUtils.getCommaDelimitedString(
ConversionUtils.getIdentifiers( DataElementCategoryOptionCombo.class, categoryOption.getCategoryOptionCombos() ) );
@@ -248,76 +278,4 @@
return map;
}
-
- public Map<String, Double> getAggregatedValueMap( Chart chart )
- {
- // A bit misplaced but we will merge chart and report table
-
- Map<String, Double> map = new HashMap<String, Double>();
-
- String dataElementIds = TextUtils.getCommaDelimitedString(
- ConversionUtils.getIdentifiers( DataElement.class, chart.getDataElements() ) );
- String indicatorIds = TextUtils.getCommaDelimitedString(
- ConversionUtils.getIdentifiers( Indicator.class, chart.getIndicators() ) );
- String dataSetIds = TextUtils.getCommaDelimitedString(
- ConversionUtils.getIdentifiers( DataSet.class, chart.getDataSets() ) );
- String periodIds = TextUtils.getCommaDelimitedString(
- ConversionUtils.getIdentifiers( Period.class, chart.getRelativePeriods() ) );
- String unitIds = TextUtils.getCommaDelimitedString(
- ConversionUtils.getIdentifiers( OrganisationUnit.class, chart.getAllOrganisationUnits() ) );
-
- if ( chart.hasDataElements() )
- {
- final String sql = "SELECT dataelementid, periodid, organisationunitid, SUM(value) FROM aggregateddatavalue " +
- "WHERE dataelementid IN (" + dataElementIds + ") AND periodid IN (" + periodIds + ") AND organisationunitid IN (" + unitIds + ") " +
- "GROUP BY dataelementid, periodid, organisationunitid"; // Sum of category option combos
-
- SqlRowSet rowSet = jdbcTemplate.queryForRowSet( sql );
-
- while ( rowSet.next() )
- {
- String id = getIdentifier( getIdentifier( DataElement.class, rowSet.getInt( 1 ) ),
- getIdentifier( Period.class, rowSet.getInt( 2 ) ),
- getIdentifier( OrganisationUnit.class, rowSet.getInt( 3 ) ) );
-
- map.put( id, rowSet.getDouble( 4 ) );
- }
- }
-
- if ( chart.hasIndicators() )
- {
- final String sql = "SELECT indicatorid, periodid, organisationunitid, value FROM aggregatedindicatorvalue " +
- "WHERE indicatorid IN (" + indicatorIds + ") AND periodid IN (" + periodIds + ") AND organisationunitid IN (" + unitIds + ")";
-
- SqlRowSet rowSet = jdbcTemplate.queryForRowSet( sql );
-
- while ( rowSet.next() )
- {
- String id = getIdentifier( getIdentifier( Indicator.class, rowSet.getInt( 1 ) ),
- getIdentifier( Period.class, rowSet.getInt( 2 ) ),
- getIdentifier( OrganisationUnit.class, rowSet.getInt( 3 ) ) );
-
- map.put( id, rowSet.getDouble( 4 ) );
- }
- }
-
- if ( chart.hasDataSets() )
- {
- final String sql = "SELECT datasetid, periodid, organisationunitid, value FROM aggregateddatasetcompleteness " +
- "WHERE datasetid IN (" + dataSetIds + ") AND periodid IN (" + periodIds + ") AND organisationunitid IN (" + unitIds + ")";
-
- SqlRowSet rowSet = jdbcTemplate.queryForRowSet( sql );
-
- while ( rowSet.next() )
- {
- String id = getIdentifier( getIdentifier( DataSet.class, rowSet.getInt( 1 ) ),
- getIdentifier( Period.class, rowSet.getInt( 2 ) ),
- getIdentifier( OrganisationUnit.class, rowSet.getInt( 3 ) ) );
-
- map.put( id, rowSet.getDouble( 4 ) );
- }
- }
-
- return map;
- }
}
