=== modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/aggregation/dataelement/AverageBoolAggregator.java' --- dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/aggregation/dataelement/AverageBoolAggregator.java 2011-01-10 10:06:18 +0000 +++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/aggregation/dataelement/AverageBoolAggregator.java 2011-01-10 15:53:32 +0000 @@ -35,6 +35,7 @@ import java.util.Date; import java.util.HashMap; import java.util.HashSet; +import java.util.List; import java.util.Map; import java.util.Map.Entry; @@ -49,7 +50,6 @@ /** * @author Lars Helge Overland - * @version $Id: AverageBoolAggregator.java 6049 2008-10-28 09:36:17Z larshelg $ */ public class AverageBoolAggregator implements DataElementAggregator @@ -77,7 +77,7 @@ // ------------------------------------------------------------------------- public Map getAggregatedValues( final Collection operands, - final Period period, final OrganisationUnit unit, int unitLevel, final OrganisationUnitHierarchy hierarchy, String key ) + final Period period, final OrganisationUnit unit, int unitLevel, final OrganisationUnitHierarchy hierarchy, List keys ) { if ( operands == null || operands.size() == 0 ) { @@ -85,7 +85,7 @@ } final Collection crossTabValues = crossTabService.getCrossTabDataValues( operands, - aggregationCache.getIntersectingPeriods( period.getStartDate(), period.getEndDate() ), hierarchy.getChildren( unit.getId() ), key ); + aggregationCache.getIntersectingPeriods( period.getStartDate(), period.getEndDate() ), hierarchy.getChildren( unit.getId() ), keys ); final Map entries = getAggregate( crossTabValues, period.getStartDate(), period.getEndDate(), period.getStartDate(), period.getEndDate(), unitLevel ); // === modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/aggregation/dataelement/AverageIntAggregator.java' --- dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/aggregation/dataelement/AverageIntAggregator.java 2011-01-10 10:06:18 +0000 +++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/aggregation/dataelement/AverageIntAggregator.java 2011-01-10 15:53:32 +0000 @@ -35,6 +35,7 @@ import java.util.Date; import java.util.HashMap; import java.util.HashSet; +import java.util.List; import java.util.Map; import java.util.Map.Entry; @@ -51,7 +52,6 @@ /** * @author Lars Helge Overland - * @version $Id: AverageIntAggregator.java 6049 2008-10-28 09:36:17Z larshelg $ */ public class AverageIntAggregator implements DataElementAggregator @@ -81,7 +81,7 @@ // ------------------------------------------------------------------------- public Map getAggregatedValues( final Collection operands, - final Period period, final OrganisationUnit unit, int unitLevel, OrganisationUnitHierarchy hierarchy, String key ) + final Period period, final OrganisationUnit unit, int unitLevel, OrganisationUnitHierarchy hierarchy, List keys ) { if ( operands == null || operands.size() == 0 ) { @@ -98,7 +98,7 @@ for ( final Integer unitId : unitIds ) { final Collection crossTabValues = - crossTabService.getCrossTabDataValues( operands, aggregationCache.getIntersectingPeriods( period.getStartDate(), period.getEndDate() ), unitId, key ); + crossTabService.getCrossTabDataValues( operands, aggregationCache.getIntersectingPeriods( period.getStartDate(), period.getEndDate() ), unitId, keys ); final Map entries = getAggregate( crossTabValues, period.getStartDate(), period.getEndDate(), period.getStartDate(), period.getEndDate(), unitLevel ); // === modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/aggregation/dataelement/AverageIntSingleValueAggregator.java' --- dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/aggregation/dataelement/AverageIntSingleValueAggregator.java 2011-01-10 10:06:18 +0000 +++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/aggregation/dataelement/AverageIntSingleValueAggregator.java 2011-01-10 15:53:32 +0000 @@ -35,6 +35,7 @@ import java.util.Date; import java.util.HashMap; import java.util.HashSet; +import java.util.List; import java.util.Map; import java.util.Map.Entry; @@ -51,7 +52,6 @@ /** * @author Lars Helge Overland - * @version $Id: AverageIntAggregator.java 6049 2008-10-28 09:36:17Z larshelg $ */ public class AverageIntSingleValueAggregator implements DataElementAggregator @@ -81,7 +81,7 @@ // ------------------------------------------------------------------------- public Map getAggregatedValues( final Collection operands, - final Period period, final OrganisationUnit unit, int unitLevel, OrganisationUnitHierarchy hierarchy, String key ) + final Period period, final OrganisationUnit unit, int unitLevel, OrganisationUnitHierarchy hierarchy, List keys ) { if ( operands == null || operands.size() == 0 ) { @@ -89,7 +89,7 @@ } final Collection crossTabValues = crossTabService.getCrossTabDataValues( operands, - aggregationCache.getIntersectingPeriods( period.getStartDate(), period.getEndDate() ), hierarchy.getChildren( unit.getId() ), key ); + aggregationCache.getIntersectingPeriods( period.getStartDate(), period.getEndDate() ), hierarchy.getChildren( unit.getId() ), keys ); final Map entries = getAggregate( crossTabValues, period.getStartDate(), period.getEndDate(), period.getStartDate(), period.getEndDate(), unitLevel ); // === modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/aggregation/dataelement/DataElementAggregator.java' --- dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/aggregation/dataelement/DataElementAggregator.java 2011-01-10 10:06:18 +0000 +++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/aggregation/dataelement/DataElementAggregator.java 2011-01-10 15:53:32 +0000 @@ -29,6 +29,7 @@ import java.util.Collection; import java.util.Date; +import java.util.List; import java.util.Map; import org.hisp.dhis.dataelement.DataElementOperand; @@ -40,14 +41,13 @@ /** * @author Lars Helge Overland - * @version $Id: DataElementAggregator.java 6049 2008-10-28 09:36:17Z larshelg $ */ public interface DataElementAggregator { final String TRUE = "true"; Map getAggregatedValues( final Collection operands, - final Period period, final OrganisationUnit unit, int unitLevel, final OrganisationUnitHierarchy hierarchy, String key ); + final Period period, final OrganisationUnit unit, int unitLevel, final OrganisationUnitHierarchy hierarchy, List keys ); Map getAggregate( final Collection crossTabValues, final Date startDate, final Date endDate, final Date aggregationStartDate, final Date aggregationEndDate, int unitLevel ); === modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/aggregation/dataelement/SumBoolAggregator.java' --- dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/aggregation/dataelement/SumBoolAggregator.java 2011-01-10 10:06:18 +0000 +++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/aggregation/dataelement/SumBoolAggregator.java 2011-01-10 15:53:32 +0000 @@ -36,6 +36,7 @@ import java.util.Date; import java.util.HashMap; import java.util.HashSet; +import java.util.List; import java.util.Map; import java.util.Map.Entry; @@ -50,7 +51,6 @@ /** * @author Lars Helge Overland - * @version $Id: SumBoolAggregator.java 6049 2008-10-28 09:36:17Z larshelg $ */ public class SumBoolAggregator implements DataElementAggregator @@ -78,7 +78,7 @@ // ------------------------------------------------------------------------- public Map getAggregatedValues( final Collection operands, - final Period period, final OrganisationUnit unit, int unitLevel, OrganisationUnitHierarchy hierarchy, String key ) + final Period period, final OrganisationUnit unit, int unitLevel, OrganisationUnitHierarchy hierarchy, List keys ) { if ( operands == null || operands.size() == 0 ) { @@ -86,7 +86,7 @@ } final Collection crossTabValues = crossTabService.getCrossTabDataValues( operands, - aggregationCache.getIntersectingPeriods( period.getStartDate(), period.getEndDate() ), hierarchy.getChildren( unit.getId() ), key ); + aggregationCache.getIntersectingPeriods( period.getStartDate(), period.getEndDate() ), hierarchy.getChildren( unit.getId() ), keys ); final Map entries = getAggregate( crossTabValues, period.getStartDate(), period.getEndDate(), period.getStartDate(), period.getEndDate(), unitLevel ); // === modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/aggregation/dataelement/SumIntAggregator.java' --- dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/aggregation/dataelement/SumIntAggregator.java 2011-01-10 10:06:18 +0000 +++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/aggregation/dataelement/SumIntAggregator.java 2011-01-10 15:53:32 +0000 @@ -35,6 +35,7 @@ import java.util.Date; import java.util.HashMap; import java.util.HashSet; +import java.util.List; import java.util.Map; import java.util.Map.Entry; @@ -51,7 +52,6 @@ /** * @author Lars Helge Overland - * @version $Id: SumIntAggregator.java 6049 2008-10-28 09:36:17Z larshelg $ */ public class SumIntAggregator implements DataElementAggregator @@ -81,7 +81,7 @@ // ------------------------------------------------------------------------- public Map getAggregatedValues( final Collection operands, - final Period period, final OrganisationUnit unit, int unitLevel, OrganisationUnitHierarchy hierarchy, String key ) + final Period period, final OrganisationUnit unit, int unitLevel, OrganisationUnitHierarchy hierarchy, List keys ) { if ( operands == null || operands.size() == 0 ) { @@ -89,7 +89,7 @@ } final Collection crossTabValues = crossTabService.getCrossTabDataValues( operands, - aggregationCache.getIntersectingPeriods( period.getStartDate(), period.getEndDate() ), hierarchy.getChildren( unit.getId() ), key ); + aggregationCache.getIntersectingPeriods( period.getStartDate(), period.getEndDate() ), hierarchy.getChildren( unit.getId() ), keys ); final Map entries = getAggregate( crossTabValues, period.getStartDate(), period.getEndDate(), period.getStartDate(), period.getEndDate(), unitLevel ); // === modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/calculateddataelement/CalculatedDataElementDataMart.java' --- dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/calculateddataelement/CalculatedDataElementDataMart.java 2010-12-02 22:32:52 +0000 +++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/calculateddataelement/CalculatedDataElementDataMart.java 2011-01-10 15:53:32 +0000 @@ -28,6 +28,7 @@ */ import java.util.Collection; +import java.util.List; import org.hisp.dhis.dataelement.CalculatedDataElement; import org.hisp.dhis.dataelement.DataElementOperand; @@ -41,5 +42,5 @@ public interface CalculatedDataElementDataMart { int exportCalculatedDataElements( Collection calculatedDataElements, - Collection periods, Collection organisationUnits, Collection operands, String key ); + Collection periods, Collection organisationUnits, Collection operands, List keys ); } === modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/calculateddataelement/DefaultCalculatedDataElementDataMart.java' --- dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/calculateddataelement/DefaultCalculatedDataElementDataMart.java 2011-01-10 10:06:18 +0000 +++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/calculateddataelement/DefaultCalculatedDataElementDataMart.java 2011-01-10 15:53:32 +0000 @@ -33,6 +33,7 @@ import java.util.Collection; import java.util.HashMap; +import java.util.List; import java.util.Map; import org.amplecode.quick.BatchHandler; @@ -119,7 +120,7 @@ // ------------------------------------------------------------------------- public int exportCalculatedDataElements( final Collection calculatedDataElements, final Collection periods, - final Collection organisationUnits, final Collection operands, String key ) + final Collection organisationUnits, final Collection operands, List keys ) { final DataElementCategoryOptionCombo categoryOptionCombo = categoryService.getDefaultDataElementCategoryOptionCombo(); @@ -147,9 +148,9 @@ { final int level = aggregationCache.getLevelOfOrganisationUnit( unit.getId() ); - final Map sumIntValueMap = sumIntAggregator.getAggregatedValues( sumOperands, period, unit, level, hierarchy, key ); - final Map averageIntValueMap = averageIntAggregator.getAggregatedValues( averageOperands, period, unit, level, hierarchy, key ); - final Map averageIntSingleValueMap = averageIntSingleValueAggregator.getAggregatedValues( averageSingleValueOperands, period, unit, level, hierarchy, key ); + final Map sumIntValueMap = sumIntAggregator.getAggregatedValues( sumOperands, period, unit, level, hierarchy, keys ); + final Map averageIntValueMap = averageIntAggregator.getAggregatedValues( averageOperands, period, unit, level, hierarchy, keys ); + final Map averageIntSingleValueMap = averageIntSingleValueAggregator.getAggregatedValues( averageSingleValueOperands, period, unit, level, hierarchy, keys ); final Map valueMap = new HashMap( sumIntValueMap ); valueMap.putAll( averageIntValueMap ); === modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/CrossTabService.java' --- dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/CrossTabService.java 2011-01-10 10:27:03 +0000 +++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/CrossTabService.java 2011-01-10 15:53:32 +0000 @@ -28,28 +28,29 @@ */ import java.util.Collection; +import java.util.List; import org.hisp.dhis.dataelement.DataElementOperand; import org.hisp.dhis.datamart.CrossTabDataValue; /** * @author Lars Helge Overland - * @version $Id: CrossTabService.java 6217 2008-11-06 18:53:04Z larshelg $ */ public interface CrossTabService { String ID = CrossTabService.class.getName(); /** - * Creates and populates the crosstab table. + * Creates and populates the crosstab table. Operands without data will be + * removed from the operands argument collection. * * @param operands the collection of DataElementOperands. * @param periodIds the collection of Period identifiers. * @param organisationUnitIds the collection of OrganisationUnit identifiers. - * @return the DataElementOperands where data exists. + * @return a List of random keys for each generated crosstab table. */ - Collection populateCrossTabTable( Collection operands, - Collection periodIds, Collection organisationUnitIds, String key ); + List populateCrossTabTable( Collection operands, + Collection periodIds, Collection organisationUnitIds ); /** * Drops the crosstab table. @@ -64,15 +65,6 @@ void trimCrossTabTable( Collection operands, String key ); /** - * Validates whether the number of columns in the crosstab table will be valid - * for the current DBMS. - * - * @param operands the DataElementOperands. - * @return 0 if OK, the number of excess columns if not. - */ - int validateCrossTabTable( Collection operands ); - - /** * Gets all CrossTabDataValues for the given collection of period ids and source ids. * * @param dataElementIds the dataelement identifiers. @@ -81,7 +73,7 @@ * @return collection of CrossTabDataValues. */ Collection getCrossTabDataValues( Collection operands, Collection periodIds, - Collection sourceIds, String key ); + Collection sourceIds, List keys ); /** * Gets all CrossTabDataValues for the given collection of period ids and the source id. @@ -92,5 +84,5 @@ * @return collection of CrossTabDataValues. */ Collection getCrossTabDataValues( Collection operands, Collection periodIds, - int sourceId, String key ); + int sourceId, List keys ); } === modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/DefaultCrossTabService.java' --- dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/DefaultCrossTabService.java 2011-01-10 10:27:03 +0000 +++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/DefaultCrossTabService.java 2011-01-10 15:53:32 +0000 @@ -36,6 +36,7 @@ import org.amplecode.quick.BatchHandler; import org.amplecode.quick.BatchHandlerFactory; +import org.apache.commons.lang.RandomStringUtils; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.hisp.dhis.aggregation.AggregatedDataValueService; @@ -43,11 +44,10 @@ import org.hisp.dhis.datamart.CrossTabDataValue; import org.hisp.dhis.datamart.crosstab.jdbc.CrossTabStore; import org.hisp.dhis.jdbc.batchhandler.GenericBatchHandler; +import org.hisp.dhis.system.util.PaginatedList; /** * @author Lars Helge Overland - * @version $Id: DefaultCrossTabService.java 6268 2008-11-12 15:16:02Z larshelg - * $ */ public class DefaultCrossTabService implements CrossTabService @@ -55,6 +55,7 @@ private static final Log log = LogFactory.getLog( DefaultCrossTabService.class ); private static final int MAX_LENGTH = 20; + private static final int MAX_COLUMNS = 1500; // ------------------------------------------------------------------------- // Dependencies @@ -85,77 +86,94 @@ // CrossTabService implementation // ------------------------------------------------------------------------- - public Collection populateCrossTabTable( final Collection operands, - final Collection periodIds, final Collection organisationUnitIds, String key ) + public List populateCrossTabTable( final Collection operands, + final Collection periodIds, final Collection organisationUnitIds ) { if ( validate( operands, periodIds, organisationUnitIds ) ) { final Set operandsWithData = new HashSet(); - final List operandList = new ArrayList( operands ); - - crossTabStore.dropCrossTabTable( key ); - - log.info( "Dropped crosstab table" ); - - crossTabStore.createCrossTabTable( operandList, key ); - - log.info( "Created crosstab table" ); - - final BatchHandler batchHandler = batchHandlerFactory.createBatchHandler( GenericBatchHandler.class ); - batchHandler.setTableName( CrossTabStore.TABLE_NAME + key ); - batchHandler.init(); - - for ( final Integer periodId : periodIds ) + final PaginatedList operandList = new PaginatedList( operands, MAX_COLUMNS ); + + final List crossTabTableKeys = new ArrayList(); + + List operandPage = new ArrayList(); + + while ( ( operandPage = operandList.nextPage() ) != null ) { - for ( final Integer sourceId : organisationUnitIds ) + final String key = RandomStringUtils.randomAlphanumeric( 8 ); + + crossTabTableKeys.add( key ); + + final Set operandsWithDataInPage = new HashSet(); + + crossTabStore.dropCrossTabTable( key ); + + crossTabStore.createCrossTabTable( operandPage, key ); + + log.debug( "Created crosstab table for key: " + key ); + + final BatchHandler batchHandler = batchHandlerFactory.createBatchHandler( GenericBatchHandler.class ); + batchHandler.setTableName( CrossTabStore.TABLE_NAME + key ); + batchHandler.init(); + + for ( final Integer periodId : periodIds ) { - final Map map = aggregatedDataValueService.getDataValueMap( periodId, sourceId ); - - final List valueList = new ArrayList( operandList.size() + 2 ); - - valueList.add( String.valueOf( periodId ) ); - valueList.add( String.valueOf( sourceId ) ); - - boolean hasValues = false; - - for ( DataElementOperand operand : operandList ) - { - String value = map.get( operand ); - - if ( value != null && value.length() > MAX_LENGTH ) - { - log.warn( "Value ignored, too long: '" + value + "', for dataelement id: '" - + operand.getDataElementId() + "', categoryoptioncombo id: '" - + operand.getOptionComboId() + "', period id: '" + periodId + "', source id: '" - + sourceId + "'" ); - - value = null; - } - - if ( value != null ) - { - hasValues = true; - operandsWithData.add( operand ); - } - - valueList.add( value ); - } - - if ( hasValues ) - { - batchHandler.addObject( valueList ); - } + for ( final Integer sourceId : organisationUnitIds ) + { + final Map map = aggregatedDataValueService.getDataValueMap( periodId, sourceId ); + + final List valueList = new ArrayList( operandPage.size() + 2 ); + + valueList.add( String.valueOf( periodId ) ); + valueList.add( String.valueOf( sourceId ) ); + + boolean hasValues = false; + + for ( DataElementOperand operand : operandPage ) + { + String value = map.get( operand ); + + if ( value != null && value.length() > MAX_LENGTH ) + { + log.warn( "Value ignored, too long: '" + value + "', for dataelement: '" + + operand.getDataElementId() + "', categoryoptioncombo: '" + + operand.getOptionComboId() + "', period: '" + periodId + "', source: '" + + sourceId + "'" ); + + value = null; + } + + if ( value != null ) + { + hasValues = true; + operandsWithData.add( operand ); + operandsWithDataInPage.add( operand ); + } + + valueList.add( value ); + } + + if ( hasValues ) + { + batchHandler.addObject( valueList ); + } + } + + log.debug( "Crosstabulated data for period " + periodId ); } + + batchHandler.flush(); + + trimCrossTabTable( operandsWithDataInPage, key ); - log.debug( "Crosstabulated data for period " + periodId ); + log.info( "Populated crosstab table for key: " + key ); + } - - batchHandler.flush(); - - trimCrossTabTable( operandsWithData, key ); - - return operandsWithData; + + operands.retainAll( operandsWithData ); // Remove operands without data + + return crossTabTableKeys; } return null; @@ -169,7 +187,7 @@ public void trimCrossTabTable( Collection operands, String key ) { // TODO use H2 in-memory table for datavaluecrosstab table ? - // TODO more compact crosstab tables by dividing up based on periodtype? + // TODO optimize retrieval by dividing up based on agg operator / type? if ( operands != null && key != null ) { @@ -181,21 +199,16 @@ } } - public int validateCrossTabTable( Collection operands ) - { - return crossTabStore.validateCrossTabTable( operands ); - } - - public Collection getCrossTabDataValues( Collection operands, - Collection periodIds, Collection sourceIds, String key ) - { - return crossTabStore.getCrossTabDataValues( operands, periodIds, sourceIds, key ); - } - - public Collection getCrossTabDataValues( Collection operands, - Collection periodIds, int sourceId, String key ) - { - return crossTabStore.getCrossTabDataValues( operands, periodIds, sourceId, key ); + public Collection getCrossTabDataValues( Collection operands, + Collection periodIds, Collection sourceIds, List keys ) + { + return crossTabStore.getCrossTabDataValues( operands, periodIds, sourceIds, keys ); + } + + public Collection getCrossTabDataValues( Collection operands, + Collection periodIds, int sourceId, List keys ) + { + return crossTabStore.getCrossTabDataValues( operands, periodIds, sourceId, keys ); } // ------------------------------------------------------------------------- === modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/CrossTabStore.java' --- dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/CrossTabStore.java 2011-01-10 10:06:18 +0000 +++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/CrossTabStore.java 2011-01-10 15:53:32 +0000 @@ -71,15 +71,6 @@ void createTrimmedCrossTabTable( Collection operands, String key ); /** - * Validates whether the number of columns in the crosstab table will be valid - * for the current DBMS. - * - * @param operands the DataElementOperands. - * @return 0 if OK, the number of excess columns if not. - */ - int validateCrossTabTable( Collection operands ); - - /** * Gets all CrossTabDataValues for the given collection of period ids and source ids. * * @param dataElementIds the dataelement identifiers. @@ -88,7 +79,7 @@ * @return collection of CrossTabDataValues. */ Collection getCrossTabDataValues( Collection operands, Collection periodIds, - Collection sourceIds, String key ); + Collection sourceIds, List keys ); /** * Gets all CrossTabDataValues for the given collection of period ids and the source id. @@ -99,5 +90,5 @@ * @return collection of CrossTabDataValues. */ Collection getCrossTabDataValues( Collection operands, Collection periodIds, - int sourceId, String key ); + int sourceId, List keys ); } === modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/JDBCCrossTabStore.java' --- dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/JDBCCrossTabStore.java 2011-01-10 10:06:18 +0000 +++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/JDBCCrossTabStore.java 2011-01-10 15:53:32 +0000 @@ -39,7 +39,6 @@ import org.amplecode.quick.StatementManager; import org.hisp.dhis.dataelement.DataElementOperand; import org.hisp.dhis.datamart.CrossTabDataValue; -import org.hisp.dhis.jdbc.StatementBuilder; /** * @author Lars Helge Overland @@ -48,6 +47,8 @@ public class JDBCCrossTabStore implements CrossTabStore { + private static final String ALIAS_PREFIX = "c"; + // ------------------------------------------------------------------------- // Dependencies // ------------------------------------------------------------------------- @@ -59,13 +60,6 @@ this.statementManager = statementManager; } - private StatementBuilder statementBuilder; - - public void setStatementBuilder( StatementBuilder statementBuilder ) - { - this.statementBuilder = statementBuilder; - } - // ------------------------------------------------------------------------- // CrossTabStore implementation // ------------------------------------------------------------------------- @@ -172,30 +166,28 @@ holder.close(); } } - - public int validateCrossTabTable( final Collection operands ) - { - int maxColumns = statementBuilder.getMaximumNumberOfColumns(); - return ( operands != null && operands.size() > maxColumns ) ? operands.size() - maxColumns : 0; - } - // ------------------------------------------------------------------------- // CrossTabDataValue // ------------------------------------------------------------------------- public Collection getCrossTabDataValues( Collection operands, - Collection periodIds, Collection sourceIds, String key ) + Collection periodIds, Collection sourceIds, List keys ) { final StatementHolder holder = statementManager.getHolder(); - + try { - final String sql = - "SELECT * " + - "FROM " + TABLE_NAME + key + " " + - "WHERE periodid IN ( " + getCommaDelimitedString( periodIds ) + " ) " + - "AND sourceid IN ( " + getCommaDelimitedString( sourceIds ) + " )"; + String sql = "SELECT * FROM " + TABLE_NAME + keys.get( 0 ) + " AS c0 "; + + for ( int i = 1; i < keys.size(); i++ ) + { + final String alias = ALIAS_PREFIX + i; + + sql += "FULL JOIN " + TABLE_NAME + keys.get( i ) + " AS " + alias + " ON c0.periodid=" + alias + ".periodid AND c0.sourceid=" + alias + ".sourceid "; + } + + sql += "WHERE c0.periodid IN (" + getCommaDelimitedString( periodIds ) + ") AND c0.sourceid IN (" + getCommaDelimitedString( sourceIds ) + ")"; final ResultSet resultSet = holder.getStatement().executeQuery( sql ); @@ -212,18 +204,23 @@ } public Collection getCrossTabDataValues( Collection operands, - Collection periodIds, int sourceId, String key ) + Collection periodIds, int sourceId, List keys ) { final StatementHolder holder = statementManager.getHolder(); try { - final String sql = - "SELECT * " + - "FROM " + TABLE_NAME + key + " " + - "WHERE periodid IN ( " + getCommaDelimitedString( periodIds ) + " ) " + - "AND sourceid = " + sourceId; - + String sql = "SELECT * FROM " + TABLE_NAME + keys.get( 0 ) + " AS c0 "; + + for ( int i = 1; i < keys.size(); i++ ) + { + final String alias = ALIAS_PREFIX + i; + + sql += "FULL JOIN " + TABLE_NAME + keys.get( i ) + " AS " + alias + " ON c0.periodid=" + alias + ".periodid AND c0.sourceid=" + alias + ".sourceid "; + } + + sql += "WHERE c0.periodid IN (" + getCommaDelimitedString( periodIds ) + ") AND c0.sourceid=" + sourceId; + final ResultSet resultSet = holder.getStatement().executeQuery( sql ); return getCrossTabDataValues( resultSet, operands ); === modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/dataelement/DataElementDataMart.java' --- dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/dataelement/DataElementDataMart.java 2010-12-02 21:24:43 +0000 +++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/dataelement/DataElementDataMart.java 2011-01-10 15:53:32 +0000 @@ -28,6 +28,7 @@ */ import java.util.Collection; +import java.util.List; import org.hisp.dhis.dataelement.DataElementOperand; import org.hisp.dhis.datamart.aggregation.dataelement.DataElementAggregator; @@ -36,10 +37,9 @@ /** * @author Lars Helge Overland - * @version $Id: DataElementDataMart.java 5510 2008-07-30 16:30:27Z larshelg $ */ public interface DataElementDataMart { int exportDataValues( Collection operands, Collection periods, - Collection organisationUnits, DataElementAggregator dataElementAggregator, String key ); + Collection organisationUnits, DataElementAggregator dataElementAggregator, List keys ); } === modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/dataelement/DefaultDataElementDataMart.java' --- dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/dataelement/DefaultDataElementDataMart.java 2011-01-10 10:06:18 +0000 +++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/dataelement/DefaultDataElementDataMart.java 2011-01-10 15:53:32 +0000 @@ -30,6 +30,7 @@ import static org.hisp.dhis.system.util.MathUtils.getRounded; import java.util.Collection; +import java.util.List; import java.util.Map; import java.util.Map.Entry; @@ -47,7 +48,6 @@ /** * @author Lars Helge Overland - * @version $Id: DefaultDataElementDataMart.java 6049 2008-10-28 09:36:17Z larshelg $ */ public class DefaultDataElementDataMart implements DataElementDataMart @@ -84,7 +84,7 @@ // ------------------------------------------------------------------------- public int exportDataValues( final Collection operands, final Collection periods, - final Collection organisationUnits, final DataElementAggregator dataElementAggregator, String key ) + final Collection organisationUnits, final DataElementAggregator dataElementAggregator, List keys ) { final BatchHandler batchHandler = batchHandlerFactory.createBatchHandler( AggregatedDataValueBatchHandler.class ).init(); @@ -104,7 +104,7 @@ { final int level = aggregationCache.getLevelOfOrganisationUnit( unit.getId() ); - final Map valueMap = dataElementAggregator.getAggregatedValues( currentOperands, period, unit, level, hierarchy, key ); + final Map valueMap = dataElementAggregator.getAggregatedValues( currentOperands, period, unit, level, hierarchy, keys ); for ( Entry entry : valueMap.entrySet() ) { === modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/engine/DefaultDataMartEngine.java' --- dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/engine/DefaultDataMartEngine.java 2011-01-10 10:27:03 +0000 +++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/engine/DefaultDataMartEngine.java 2011-01-10 15:53:32 +0000 @@ -29,9 +29,9 @@ import java.util.Collection; import java.util.HashSet; +import java.util.List; import java.util.Set; -import org.apache.commons.lang.RandomStringUtils; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.hisp.dhis.aggregation.AggregatedDataValueService; @@ -57,6 +57,7 @@ import org.hisp.dhis.system.util.ConversionUtils; import org.hisp.dhis.system.util.TimeUtils; import org.springframework.transaction.annotation.Transactional; +import org.springframework.util.CollectionUtils; /** * @author Lars Helge Overland @@ -253,41 +254,27 @@ // Create and trim crosstabtable // --------------------------------------------------------------------- - String key = RandomStringUtils.randomAlphanumeric( 8 ); - - if ( crossTabService.validateCrossTabTable( allOperands ) != 0 ) - { - int excess = crossTabService.validateCrossTabTable( allOperands ); - - log.warn( "Cannot crosstabulate since the number of data elements exceeded maximum columns: " + excess ); - - state.setMessage( "could_not_export_too_many_data_elements" ); - - return 0; - } - state.setMessage( "crosstabulating_data" ); Collection childrenIds = organisationUnitService.getOrganisationUnitHierarchy().getChildren( organisationUnitIds ); Collection intersectingPeriodIds = ConversionUtils.getIdentifiers( Period.class, periodService.getIntersectionPeriods( periods ) ); + + List keys = crossTabService.populateCrossTabTable( allOperands, intersectingPeriodIds, childrenIds ); - Collection operandsWithData = crossTabService.populateCrossTabTable( allOperands, intersectingPeriodIds, childrenIds, key ); - - log.info( "Populated crosstab table: " + TimeUtils.getHMS() ); - - if ( operandsWithData == null ) + if ( CollectionUtils.isEmpty( allOperands ) || CollectionUtils.isEmpty( keys ) ) { return 0; } - + + log.info( "Number of crosstab tables: " + keys.size() + " " + TimeUtils.getHMS() ); + // --------------------------------------------------------------------- // Remove operands without data // --------------------------------------------------------------------- - nonCalculatedOperands.retainAll( operandsWithData ); - indicatorOperands.retainAll( operandsWithData ); - calculatedOperands.retainAll( operandsWithData ); - allOperands.retainAll( operandsWithData ); + nonCalculatedOperands.retainAll( allOperands ); + indicatorOperands.retainAll( allOperands ); + calculatedOperands.retainAll( allOperands ); // --------------------------------------------------------------------- // Data element export @@ -297,23 +284,23 @@ if ( nonCalculatedOperands.size() > 0 ) { - count += dataElementDataMart.exportDataValues( nonCalculatedOperands, periods, organisationUnits, sumIntAggregator, key ); + count += dataElementDataMart.exportDataValues( nonCalculatedOperands, periods, organisationUnits, sumIntAggregator, keys ); log.info( "Exported values for data element operands with sum aggregation operator of type number: " + TimeUtils.getHMS() ); - count += dataElementDataMart.exportDataValues( nonCalculatedOperands, periods, organisationUnits, averageIntAggregator, key ); + count += dataElementDataMart.exportDataValues( nonCalculatedOperands, periods, organisationUnits, averageIntAggregator, keys ); log.info( "Exported values for data element operands with average aggregation operator of type number: " + TimeUtils.getHMS() ); - count += dataElementDataMart.exportDataValues( nonCalculatedOperands, periods, organisationUnits, averageIntSingleValueAggregator, key ); + count += dataElementDataMart.exportDataValues( nonCalculatedOperands, periods, organisationUnits, averageIntSingleValueAggregator, keys ); log.info( "Exported values for data element operands with average aggregation operator with single value of type number: " + TimeUtils.getHMS() ); - count += dataElementDataMart.exportDataValues( nonCalculatedOperands, periods, organisationUnits, sumBoolAggregator, key ); + count += dataElementDataMart.exportDataValues( nonCalculatedOperands, periods, organisationUnits, sumBoolAggregator, keys ); log.info( "Exported values for data element operands with sum aggregation operator of type yes/no: " + TimeUtils.getHMS() ); - count += dataElementDataMart.exportDataValues( nonCalculatedOperands, periods, organisationUnits, averageBoolAggregator, key ); + count += dataElementDataMart.exportDataValues( nonCalculatedOperands, periods, organisationUnits, averageBoolAggregator, keys ); log.info( "Exported values for data element operands with average aggregation operator of type yes/no: " + TimeUtils.getHMS() ); } @@ -326,7 +313,7 @@ if ( indicators != null && indicators.size() > 0 ) { - count += indicatorDataMart.exportIndicatorValues( indicators, periods, organisationUnits, indicatorOperands, key ); + count += indicatorDataMart.exportIndicatorValues( indicators, periods, organisationUnits, indicatorOperands, keys ); log.info( "Exported values for indicators: " + TimeUtils.getHMS() ); } @@ -339,12 +326,15 @@ if ( calculatedDataElements != null && calculatedDataElements.size() > 0 ) { - count += calculatedDataElementDataMart.exportCalculatedDataElements( calculatedDataElements, periods, organisationUnits, calculatedOperands, key ); + count += calculatedDataElementDataMart.exportCalculatedDataElements( calculatedDataElements, periods, organisationUnits, calculatedOperands, keys ); log.info( "Exported values for calculated data elements: " + TimeUtils.getHMS() ); } - crossTabService.dropCrossTabTable( key ); + for ( String key : keys ) + { + crossTabService.dropCrossTabTable( key ); + } log.info( "Export process completed: " + TimeUtils.getHMS() ); === modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/indicator/DefaultIndicatorDataMart.java' --- dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/indicator/DefaultIndicatorDataMart.java 2011-01-10 10:06:18 +0000 +++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/indicator/DefaultIndicatorDataMart.java 2011-01-10 15:53:32 +0000 @@ -35,6 +35,7 @@ import java.util.Collection; import java.util.HashMap; +import java.util.List; import java.util.Map; import org.amplecode.quick.BatchHandler; @@ -55,7 +56,6 @@ /** * @author Lars Helge Overland - * @version $Id: DefaultIndicatorDataMart.java 6069 2008-10-28 17:31:02Z larshelg $ */ public class DefaultIndicatorDataMart implements IndicatorDataMart @@ -123,7 +123,7 @@ // ------------------------------------------------------------------------- public int exportIndicatorValues( final Collection indicators, final Collection periods, - final Collection organisationUnits, final Collection operands, String key ) + final Collection organisationUnits, final Collection operands, List keys ) { final BatchHandler batchHandler = batchHandlerFactory.createBatchHandler( AggregatedIndicatorValueBatchHandler.class ).init(); @@ -152,9 +152,9 @@ { final int level = aggregationCache.getLevelOfOrganisationUnit( unit.getId() ); - final Map sumIntValueMap = sumIntAggregator.getAggregatedValues( sumOperands, period, unit, level, hierarchy, key ); - final Map averageIntValueMap = averageIntAggregator.getAggregatedValues( averageOperands, period, unit, level, hierarchy, key ); - final Map averageIntSingleValueMap = averageIntSingleValueAggregator.getAggregatedValues( averageSingleValueOperands, period, unit, level, hierarchy, key ); + final Map sumIntValueMap = sumIntAggregator.getAggregatedValues( sumOperands, period, unit, level, hierarchy, keys ); + final Map averageIntValueMap = averageIntAggregator.getAggregatedValues( averageOperands, period, unit, level, hierarchy, keys ); + final Map averageIntSingleValueMap = averageIntSingleValueAggregator.getAggregatedValues( averageSingleValueOperands, period, unit, level, hierarchy, keys ); final Map valueMap = new HashMap( sumIntValueMap ); valueMap.putAll( averageIntValueMap ); === modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/indicator/IndicatorDataMart.java' --- dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/indicator/IndicatorDataMart.java 2010-12-02 21:24:43 +0000 +++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/indicator/IndicatorDataMart.java 2011-01-10 15:53:32 +0000 @@ -28,6 +28,7 @@ */ import java.util.Collection; +import java.util.List; import org.hisp.dhis.dataelement.DataElementOperand; import org.hisp.dhis.indicator.Indicator; @@ -36,10 +37,9 @@ /** * @author Lars Helge Overland - * @version $Id: IndicatorDataMart.java 5510 2008-07-30 16:30:27Z larshelg $ */ public interface IndicatorDataMart { int exportIndicatorValues( Collection indicators, Collection periods, - Collection organisationUnits, Collection operands, String key ); + Collection organisationUnits, Collection operands, List keys ); } === modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/resources/META-INF/dhis/beans.xml' --- dhis-2/dhis-services/dhis-service-datamart-default/src/main/resources/META-INF/dhis/beans.xml 2011-01-10 10:06:18 +0000 +++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/resources/META-INF/dhis/beans.xml 2011-01-10 15:53:32 +0000 @@ -79,7 +79,6 @@ - periodIds; private Collection organisationUnitIds; - private String key = RandomStringUtils.randomAlphanumeric( 8 ); - // ------------------------------------------------------------------------- // Fixture // ------------------------------------------------------------------------- @@ -181,9 +179,9 @@ @Test public void testPopulateCrossTabValue() { - crossTabService.populateCrossTabTable( operands, periodIds, organisationUnitIds, key ); + List keys = crossTabService.populateCrossTabTable( operands, periodIds, organisationUnitIds ); - Collection values = crossTabService.getCrossTabDataValues( operands, periodIds, organisationUnitIds, key ); + Collection values = crossTabService.getCrossTabDataValues( operands, periodIds, organisationUnitIds, keys ); assertNotNull( values ); @@ -204,12 +202,4 @@ } } } - - @Test - public void testTrimCrossTabTable() - { - Collection operandsWithData = crossTabService.populateCrossTabTable( operands, periodIds, organisationUnitIds, key ); - - crossTabService.trimCrossTabTable( operandsWithData, key ); - } }