=== modified file 'dhis-2/dhis-api/src/main/java/org/hisp/dhis/caseaggregation/CaseAggregationCondition.java' --- dhis-2/dhis-api/src/main/java/org/hisp/dhis/caseaggregation/CaseAggregationCondition.java 2014-08-29 06:39:13 +0000 +++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/caseaggregation/CaseAggregationCondition.java 2014-09-08 14:40:15 +0000 @@ -95,6 +95,11 @@ public static String MINUS_ATTRIBUTE_OPERATOR = "ATTRDATEDIFF"; public static String AUTO_STORED_BY = "aggregated_from_tracker"; + + public static final String PARAM_PERIOD_START_DATE = "PERIOD_START_DATE"; + public static final String PARAM_PERIOD_END_DATE = "PARAM_PERIOD_END_DATE"; + public static final String PARAM_PERIOD_ID = "PERIOD_ID"; + public static final String PARAM_PERIOD_ISO_DATE = "PERIOD_ISO_DATE"; public static final String regExp = "\\[(" + OBJECT_ORGUNIT_COMPLETE_PROGRAM_STAGE + "|" + OBJECT_PROGRAM + "|" + OBJECT_PROGRAM_STAGE_PROPERTY + "|" + OBJECT_PROGRAM_STAGE + "|" + OBJECT_TRACKED_ENTITY_PROGRAM_STAGE_PROPERTY === modified file 'dhis-2/dhis-api/src/main/java/org/hisp/dhis/caseaggregation/CaseAggregationConditionService.java' --- dhis-2/dhis-api/src/main/java/org/hisp/dhis/caseaggregation/CaseAggregationConditionService.java 2014-08-03 16:27:47 +0000 +++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/caseaggregation/CaseAggregationConditionService.java 2014-09-08 14:40:15 +0000 @@ -184,29 +184,15 @@ * 3 month, last 6 month and last 12 month */ void aggregate( List caseAggregateSchedules, String taskStrategy ); - - /** - * Return a data value table aggregated of a query builder formula - * - * @param caseAggregationCondition The query builder expression - * @param orgunitIds The ids of organisation unit where to aggregate data - * value - * @param period The date range for aggregate data value - * @param format - * @param i18n - */ - Grid getAggregateValue( CaseAggregationCondition caseAggregationCondition, Collection orgunitIds, - Period period, I18nFormat format, I18n i18n ); - + /** * Insert value aggregated from a {@link CaseAggregationCondition} * - * @param caseAggregationCondition CaseAggregationCondition + * @param caseAggregationConditions {@link CaseAggregationCondition * @param orgunitIds The list of {@link OrganisationUnit} ids - * @param period {@link Period} + * @param periods {@link Period} */ - void insertAggregateValue( CaseAggregationCondition caseAggregationCondition, Collection orgunitIds, - Period period ); + void insertAggregateValue( Collection caseAggregationConditions, Collection orgunitIds, Collection periods ); /** * Retrieve the details of each {@link DataValue} which are generated by a @@ -275,4 +261,17 @@ * @return */ int countCaseAggregationCondition( Collection dataElements, String key ); + + /** + * Return a data value table aggregated of a query builder formula + * + * @param caseAggregationConditions The collection of query builder expressions + * @param orgunitIds The ids of organisation unit where to aggregate data + * value + * @param period The collections of date ranges for aggregate data value + * @param format + * @param i18n + */ + List getAggregateValue( Collection caseAggregationConditions, Collection orgunitIds, + Collection periods, I18nFormat format, I18n i18n ); } === modified file 'dhis-2/dhis-api/src/main/java/org/hisp/dhis/caseaggregation/CaseAggregationConditionStore.java' --- dhis-2/dhis-api/src/main/java/org/hisp/dhis/caseaggregation/CaseAggregationConditionStore.java 2014-08-29 18:08:10 +0000 +++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/caseaggregation/CaseAggregationConditionStore.java 2014-09-08 14:40:15 +0000 @@ -86,19 +86,6 @@ Collection getPeriods( String periodTypeName, String taskStrategy ); /** - * Return a data value table aggregated of a query builder formula - * - * @param caseAggregationCondition The query builder expression - * @param orgunitIds The ids of organisation unit where to aggregate data - * value - * @param period The date range for aggregate data value - * @param format - * @param i18n - */ - Grid getAggregateValue( CaseAggregationCondition caseAggregationCondition, Collection orgunitIds, - Period period, int attributeOptioncomboId, I18nFormat format, I18n i18n ); - - /** * Retrieve the details of each {@link DataValue} which are generated by a * {@link CaseAggregationCondition} * @@ -115,14 +102,15 @@ /** * Insert data values into database directly * - * @param caseAggregationCondition The query builder expression - * @param orgunitIds The ids of organisation unit where to aggregate data + * @param sql SQL filter events + * @param dataElementId The id of aggregate data element + * @param optionComboId The id of category option combo + * @param orgunitIds The id collection of organisation units where to aggregate data * value * @param period The date range for aggregate data value */ - void insertAggregateValue( String expression, String operator, Integer dataElementId, Integer optionComboId, - int attributeOptioncomboId, Integer deSumId, Collection orgunitIds, Period period ); - + void insertAggregateValue( String sql, int dataElementId, int optionComboId, Collection orgunitIds, Period period ); + /** * Return standard SQL from query builder formula * @@ -140,12 +128,18 @@ * element values. This fill is null for other operators. * @param orgunitId The id of organisation unit where to aggregate data * value - * @param startDate Start date - * @param endDate End date */ String parseExpressionToSql( boolean isInsert, String caseExpression, String operator, Integer aggregateDeId, String aggregateDeName, Integer optionComboId, String optionComboName, int attributeOptioncomboId, Integer deSumId, - Collection orgunitIds, Period period ); + Collection orgunitIds ); + + /** + * Return standard SQL from query builder formula + * @param aggregationCondition {@link CaseAggregationCondition} + * @param attributeOptionComboId + * + */ + String parseExpressionToSql( boolean isInsert, CaseAggregationCondition aggregationCondition, int attributeOptionComboId, Collection orgunitIds ); /** * Aggregate data values for the dataset by periods with a organisation unit @@ -185,4 +179,23 @@ * @return */ int count( Collection dataElements, String key ); + + public Collection getServiceOrgunit(); + + Grid getAggregateValue( String sql, I18nFormat format, I18n i18n ); + + + /** + * Insert data values into database directly + * + * @param sql SQL filter events + * @param dataElement The aggregate data element + * @param optionCombo The category option combo + * @param attributeOptionCombo + * @param orgunitIds The id collection of organisation units where to aggregate data + * value + * @param period The date range for aggregate data value + */ + void insertAggregateValue( String sql, DataElement dataElement, DataElementCategoryOptionCombo optionCombo, + DataElementCategoryOptionCombo attributeOptionCombo, Collection orgunitIds, Period period ); } === modified file 'dhis-2/dhis-services/dhis-service-eventreporting/src/main/java/org/hisp/dhis/caseaggregation/DefaultCaseAggregationConditionService.java' --- dhis-2/dhis-services/dhis-service-eventreporting/src/main/java/org/hisp/dhis/caseaggregation/DefaultCaseAggregationConditionService.java 2014-08-29 18:08:10 +0000 +++ dhis-2/dhis-services/dhis-service-eventreporting/src/main/java/org/hisp/dhis/caseaggregation/DefaultCaseAggregationConditionService.java 2014-09-08 14:40:15 +0000 @@ -29,10 +29,14 @@ */ import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.OBJECT_ORGUNIT_COMPLETE_PROGRAM_STAGE; -import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.OBJECT_TRACKED_ENTITY_ATTRIBUTE; import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.OBJECT_PROGRAM; import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.OBJECT_PROGRAM_STAGE; import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.OBJECT_PROGRAM_STAGE_DATAELEMENT; +import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.OBJECT_TRACKED_ENTITY_ATTRIBUTE; +import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.PARAM_PERIOD_END_DATE; +import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.PARAM_PERIOD_START_DATE; +import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.PARAM_PERIOD_ID; +import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.PARAM_PERIOD_ISO_DATE; import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.SEPARATOR_ID; import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.SEPARATOR_OBJECT; import static org.hisp.dhis.i18n.I18nUtils.i18n; @@ -62,6 +66,7 @@ import org.hisp.dhis.program.ProgramStage; import org.hisp.dhis.program.ProgramStageService; import org.hisp.dhis.system.util.ConcurrentUtils; +import org.hisp.dhis.system.util.DateUtils; import org.hisp.dhis.system.util.SystemUtils; import org.hisp.dhis.trackedentity.TrackedEntityAttribute; import org.hisp.dhis.trackedentity.TrackedEntityAttributeService; @@ -425,16 +430,39 @@ ConcurrentUtils.waitForCompletion( futures ); } - public Grid getAggregateValue( CaseAggregationCondition caseAggregationCondition, Collection orgunitIds, - Period period, I18nFormat format, I18n i18n ) + @Override + public List getAggregateValue( Collection caseAggregationConditions, Collection orgunitIds, + Collection periods, I18nFormat format, I18n i18n ) { - periodService.reloadPeriod( period ); + Collection _orgunitIds = aggregationConditionStore.getServiceOrgunit(); + _orgunitIds.retainAll( orgunitIds ); + if ( _orgunitIds.size() > 0 ) + { + int attributeOptioncomboId = categoryService.getDefaultDataElementCategoryOptionCombo().getId(); + List grids = new ArrayList<>(); + for ( CaseAggregationCondition condition : caseAggregationConditions ) + { + String sql = aggregationConditionStore.parseExpressionToSql( false, condition, attributeOptioncomboId, _orgunitIds ); + for ( Period period : periods ) + { + period = periodService.reloadPeriod( period ); + String periodSQL = sql; + periodSQL = replacePeriodSql( periodSQL, period ); + + Grid grid = aggregationConditionStore.getAggregateValue( periodSQL, format, i18n ); + grid.setTitle( condition.getDisplayName() ); + grid.setSubtitle( format.formatPeriod( period ) ); - int attributeOptioncomboId = categoryService.getDefaultDataElementCategoryOptionCombo().getId(); + grids.add( grid ); + } + } + + return grids; + } - return aggregationConditionStore.getAggregateValue( caseAggregationCondition, orgunitIds, period, attributeOptioncomboId, format, i18n ); + return null; } - + @Override public Grid getAggregateValueDetails( CaseAggregationCondition aggregationCondition, OrganisationUnit orgunit, Period period, I18nFormat format, I18n i18n ) @@ -445,19 +473,28 @@ return aggregationConditionStore.getAggregateValueDetails( aggregationCondition, orgunit, period, nonRegistrationProgram, format, i18n ); } - public void insertAggregateValue( CaseAggregationCondition caseAggregationCondition, - Collection orgunitIds, Period period ) + @Override + public void insertAggregateValue( Collection caseAggregationConditions, + Collection orgunitIds, Collection periods ) { - periodService.reloadPeriod( period ); - - Integer deSumId = (caseAggregationCondition.getDeSum() == null) ? null : caseAggregationCondition.getDeSum() - .getId(); - - int attributeOptioncomboId = categoryService.getDefaultDataElementCategoryOptionCombo().getId(); - - aggregationConditionStore.insertAggregateValue( caseAggregationCondition.getAggregationExpression(), - caseAggregationCondition.getOperator(), caseAggregationCondition.getAggregationDataElement().getId(), - caseAggregationCondition.getOptionCombo().getId(), attributeOptioncomboId, deSumId, orgunitIds, period ); + Collection _orgunitIds = aggregationConditionStore.getServiceOrgunit(); + _orgunitIds.retainAll( orgunitIds ); + if ( _orgunitIds.size() > 0 ) + { + DataElementCategoryOptionCombo attributeOptioncombo = categoryService.getDefaultDataElementCategoryOptionCombo(); + for( CaseAggregationCondition caseAggregationCondition : caseAggregationConditions ) + { + String sql = aggregationConditionStore.parseExpressionToSql(false, caseAggregationCondition, attributeOptioncombo.getId(), _orgunitIds ); + + for ( Period period : periods ) + { + period = periodService.reloadPeriod( period ); + sql = replacePeriodSql( sql, period ); + aggregationConditionStore.insertAggregateValue( sql, caseAggregationCondition.getAggregationDataElement(), + caseAggregationCondition.getOptionCombo(), attributeOptioncombo, _orgunitIds, period ); + } + } + } } @Override @@ -478,8 +515,10 @@ int attributeOptioncomboId = categoryService.getDefaultDataElementCategoryOptionCombo().getId(); - return aggregationConditionStore.parseExpressionToSql( isInsert, caseExpression, operator, aggregateDeId, - aggregateDeName, optionComboId, optionComboName, attributeOptioncomboId, deSumId, orgunitIds, period ); + String sql = aggregationConditionStore.parseExpressionToSql( isInsert, caseExpression, operator, aggregateDeId, + aggregateDeName, optionComboId, optionComboName, attributeOptioncomboId, deSumId, orgunitIds ); + + return replacePeriodSql( sql, period ); } @Override @@ -492,6 +531,17 @@ // Support Methods // ------------------------------------------------------------------------- + private String replacePeriodSql( String sql, Period period ) + { + sql = sql.replaceAll( "COMBINE", "" ); + sql = sql.replaceAll( PARAM_PERIOD_START_DATE, DateUtils.getMediumDateString( period.getStartDate() )); + sql = sql.replaceAll( PARAM_PERIOD_END_DATE, DateUtils.getMediumDateString( period.getEndDate() )); + sql = sql.replaceAll( PARAM_PERIOD_ID, period.getId() + "" ); + sql = sql.replaceAll( PARAM_PERIOD_ISO_DATE, period.getIsoDate() ); + + return sql; + } + @Async private Future aggregateValueManager( ConcurrentLinkedQueue caseAggregateSchedule, String taskStrategy ) @@ -581,7 +631,7 @@ } } -System.out.println("\n\n\n ==== \n false "); + return false; } } === modified file 'dhis-2/dhis-services/dhis-service-eventreporting/src/main/java/org/hisp/dhis/caseaggregation/hibernate/HibernateCaseAggregationConditionStore.java' --- dhis-2/dhis-services/dhis-service-eventreporting/src/main/java/org/hisp/dhis/caseaggregation/hibernate/HibernateCaseAggregationConditionStore.java 2014-08-31 09:14:47 +0000 +++ dhis-2/dhis-services/dhis-service-eventreporting/src/main/java/org/hisp/dhis/caseaggregation/hibernate/HibernateCaseAggregationConditionStore.java 2014-09-08 14:40:15 +0000 @@ -36,6 +36,10 @@ import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.OBJECT_PROGRAM_STAGE_PROPERTY; import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.OBJECT_TRACKED_ENTITY_ATTRIBUTE; import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.OBJECT_TRACKED_ENTITY_PROGRAM_STAGE_PROPERTY; +import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.PARAM_PERIOD_END_DATE; +import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.PARAM_PERIOD_ID; +import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.PARAM_PERIOD_ISO_DATE; +import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.PARAM_PERIOD_START_DATE; import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.SEPARATOR_ID; import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.SEPARATOR_OBJECT; import static org.hisp.dhis.scheduling.CaseAggregateConditionSchedulingManager.TASK_AGGREGATE_QUERY_BUILDER_LAST_12_MONTH; @@ -68,16 +72,21 @@ import org.hisp.dhis.dataelement.DataElement; import org.hisp.dhis.dataelement.DataElementCategoryOptionCombo; import org.hisp.dhis.dataelement.DataElementService; +import org.hisp.dhis.datavalue.DataValue; +import org.hisp.dhis.datavalue.DataValueService; import org.hisp.dhis.i18n.I18n; import org.hisp.dhis.i18n.I18nFormat; import org.hisp.dhis.jdbc.StatementBuilder; import org.hisp.dhis.organisationunit.OrganisationUnit; +import org.hisp.dhis.organisationunit.OrganisationUnitService; import org.hisp.dhis.period.CalendarPeriodType; import org.hisp.dhis.period.Period; +import org.hisp.dhis.period.PeriodService; import org.hisp.dhis.period.PeriodType; import org.hisp.dhis.system.grid.ListGrid; import org.hisp.dhis.system.util.DateUtils; import org.hisp.dhis.system.util.TextUtils; +import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.support.rowset.SqlRowSet; @@ -101,8 +110,6 @@ private static final String IN_CONDITION_COUNT_X_TIMES = "COUNT"; - public static final String STORED_BY_DHIS_SYSTEM = "aggregated_from_tracker"; - // ------------------------------------------------------------------------- // Dependencies // ------------------------------------------------------------------------- @@ -127,6 +134,15 @@ { this.dataElementService = dataElementService; } + + @Autowired + DataValueService dataValueService; + + @Autowired + private PeriodService periodService; + + @Autowired + private OrganisationUnitService orgunitService; // ------------------------------------------------------------------------- // Implementation Methods @@ -143,20 +159,20 @@ public int count( Collection dataElements, String key ) { Criteria criteria = getCriteria(); - - if( dataElements!= null ) - { - criteria.add( Restrictions.in( "aggregationDataElement", dataElements ) ); - } - if( key != null ) - { - criteria.add( Restrictions.ilike( "name", "%" + key + "%" ) ); - } - - Number rs = ( Number ) criteria.setProjection(Projections.rowCount()).uniqueResult(); + + if ( dataElements != null ) + { + criteria.add( Restrictions.in( "aggregationDataElement", dataElements ) ); + } + if ( key != null ) + { + criteria.add( Restrictions.ilike( "name", "%" + key + "%" ) ); + } + + Number rs = (Number) criteria.setProjection( Projections.rowCount() ).uniqueResult(); return rs != null ? rs.intValue() : 0; } - + @Override public CaseAggregationCondition get( DataElement dataElement, DataElementCategoryOptionCombo optionCombo ) { @@ -166,67 +182,49 @@ @SuppressWarnings( "unchecked" ) @Override - public Collection get( Collection dataElements, String key, Integer first, Integer max ) + public Collection get( Collection dataElements, String key, Integer first, + Integer max ) { Criteria criteria = getCriteria(); - - if( dataElements!= null ) - { - criteria.add( Restrictions.in( "aggregationDataElement", dataElements ) ); - } - if( key != null ) - { - criteria.add( Restrictions.ilike( "name", "%" + key + "%" ) ); - } - - if( first != null && max != null ) + + if ( dataElements != null ) + { + criteria.add( Restrictions.in( "aggregationDataElement", dataElements ) ); + } + if ( key != null ) + { + criteria.add( Restrictions.ilike( "name", "%" + key + "%" ) ); + } + + if ( first != null && max != null ) { criteria.setFirstResult( first ); criteria.setMaxResults( max ); } - - criteria.addOrder(Order.desc("name")); - + + criteria.addOrder( Order.desc( "name" ) ); + return criteria.list(); } - public Grid getAggregateValue( CaseAggregationCondition caseAggregationCondition, Collection orgunitIds, - Period period, int attributeOptioncomboId, I18nFormat format, I18n i18n ) + public Grid getAggregateValue( String sql, I18nFormat format, I18n i18n ) { - Collection _orgunitIds = getServiceOrgunit(); - _orgunitIds.retainAll( orgunitIds ); - - if ( _orgunitIds.size() > 0 ) - { - Grid grid = new ListGrid(); - grid.setTitle( caseAggregationCondition.getDisplayName() ); - grid.setSubtitle( format.formatPeriod( period ) ); - - grid.addHeader( new GridHeader( i18n.getString( "dataelementid" ), true, true ) ); - grid.addHeader( new GridHeader( i18n.getString( "categoryoptioncomboid" ), true, true ) ); - grid.addHeader( new GridHeader( i18n.getString( "periodid" ), true, true ) ); - grid.addHeader( new GridHeader( i18n.getString( "organisationunitid" ), true, true ) ); - grid.addHeader( new GridHeader( i18n.getString( "storedby" ), true, true ) ); - grid.addHeader( new GridHeader( i18n.getString( "dataelementname" ), false, true ) ); - grid.addHeader( new GridHeader( i18n.getString( "categoryoptioncomboname" ), false, true ) ); - grid.addHeader( new GridHeader( i18n.getString( "organisationunitname" ), false, true ) ); - grid.addHeader( new GridHeader( i18n.getString( "value" ), false, true ) ); - - Integer deSumId = (caseAggregationCondition.getDeSum() == null) ? null : caseAggregationCondition - .getDeSum().getId(); - String sql = parseExpressionToSql( false, caseAggregationCondition.getAggregationExpression(), - caseAggregationCondition.getOperator(), caseAggregationCondition.getAggregationDataElement().getId(), - caseAggregationCondition.getAggregationDataElement().getDisplayName(), caseAggregationCondition - .getOptionCombo().getId(), caseAggregationCondition.getOptionCombo().getDisplayName(), attributeOptioncomboId, deSumId, - _orgunitIds, period ); - - SqlRowSet rs = jdbcTemplate.queryForRowSet( sql ); - grid.addRows( rs ); - - return grid; - } - - return null; + Grid grid = new ListGrid(); + + grid.addHeader( new GridHeader( i18n.getString( "dataelementid" ), true, true ) ); + grid.addHeader( new GridHeader( i18n.getString( "categoryoptioncomboid" ), true, true ) ); + grid.addHeader( new GridHeader( i18n.getString( "periodid" ), true, true ) ); + grid.addHeader( new GridHeader( i18n.getString( "organisationunitid" ), true, true ) ); + grid.addHeader( new GridHeader( i18n.getString( "storedby" ), true, true ) ); + grid.addHeader( new GridHeader( i18n.getString( "dataelementname" ), false, true ) ); + grid.addHeader( new GridHeader( i18n.getString( "categoryoptioncomboname" ), false, true ) ); + grid.addHeader( new GridHeader( i18n.getString( "organisationunitname" ), false, true ) ); + grid.addHeader( new GridHeader( i18n.getString( "value" ), false, true ) ); + + SqlRowSet rs = jdbcTemplate.queryForRowSet( sql ); + grid.addRows( rs ); + + return grid; } @Override @@ -259,95 +257,144 @@ int index = 1; while ( rs.next() ) { - grid.addRow(); - for ( int i = 1; i <= cols; i++ ) - { - Object value = rs.getObject( i ); - if( i==1 ) - { - if( !value.toString().equals(idValue) ) - { - grid.addValue( index ); - idValue = value.toString(); - index ++; - } - else - { - grid.addValue( "" ); - } - } - else - { - grid.addValue( value ); - } - } + grid.addRow(); + for ( int i = 1; i <= cols; i++ ) + { + Object value = rs.getObject( i ); + if ( i == 1 ) + { + if ( !value.toString().equals( idValue ) ) + { + grid.addValue( index ); + idValue = value.toString(); + index++; + } + else + { + grid.addValue( "" ); + } + } + else + { + grid.addValue( value ); + } + } + } + } + + @Override + public void insertAggregateValue( String sql, DataElement dataElement, DataElementCategoryOptionCombo optionCombo, + DataElementCategoryOptionCombo attributeOptionCombo, Collection orgunitIds, Period period ) + { + try + { + SqlRowSet row = jdbcTemplate.queryForRowSet(sql); + while (row.next()) + { + int value = row.getInt("value"); + OrganisationUnit source = orgunitService.getOrganisationUnit( row.getInt( "sourceid" ) ); + + DataValue dataValue = dataValueService.getDataValue( dataElement, period, source, optionCombo ); + + if ( dataValue == null && value != 0 ) + { + dataValue = new DataValue( dataElement, period, source, optionCombo, attributeOptionCombo ); + dataValue.setValue( value + "" ); + dataValue.setStoredBy( row.getString("storedby") ); + dataValueService.addDataValue( dataValue ); + } + else if ( dataValue != null && value == 0 ) + { + dataValueService.deleteDataValue( dataValue ); + } + else if ( dataValue != null && value != 0 ) + { + dataValue.setValue( value + "" ); + dataValueService.updateDataValue( dataValue ); + } + } + } + catch ( Exception ex ) + { + ex.printStackTrace(); } } - public void insertAggregateValue( String expression, String operator, Integer dataElementId, Integer optionComboId, int attributeOptioncomboId, - Integer deSumId, Collection orgunitIds, Period period ) - { - // Delete all data value from this period which created from DHIS-system - // after to run Aggregate Query Builder - String periodtypeSql = "select periodtypeid from periodtype where name='" + period.getPeriodType().getName() - + "'"; - int periodTypeId = jdbcTemplate.queryForObject( periodtypeSql, Integer.class ); - String periodSql = "select periodid from period where periodtypeid=" + periodTypeId + " and startdate='" - + DateUtils.getMediumDateString( period.getStartDate() ) + "' and enddate='" - + DateUtils.getMediumDateString( period.getEndDate() ) + "'"; - SqlRowSet rs = jdbcTemplate.queryForRowSet( periodSql ); - int periodid = 0; - - if ( rs.next() ) - { - periodid = rs.getInt( "periodid" ); - } - - if ( periodid == 0 ) - { - String insertSql = "insert into period (periodtypeid,startdate,enddate) " + " VALUES " + "(" - + period.getPeriodType().getId() + ",'" + DateUtils.getMediumDateString( period.getStartDate() ) - + "','" + DateUtils.getMediumDateString( period.getEndDate() ) + "' )"; - jdbcTemplate.execute( insertSql ); - - period.setId( jdbcTemplate.queryForObject( insertSql, Integer.class ) ); - } - else - { - period.setId( periodid ); - - String deleteDataValueSql = "delete from datavalue where dataelementid=" + dataElementId - + " and categoryoptioncomboid=" + optionComboId + " and sourceid in (" - + TextUtils.getCommaDelimitedString( orgunitIds ) + ") and periodid=" + periodid + ""; - - jdbcTemplate.execute( deleteDataValueSql ); - } - - // insert data elements into database directly - - String sql = parseExpressionToSql( true, expression, operator, dataElementId, "dataelementname", optionComboId, - "optionComboname", attributeOptioncomboId, deSumId, orgunitIds, period ); - jdbcTemplate.execute( sql ); - } - - @Override - public String parseExpressionToSql( boolean isInsert, String caseExpression, String operator, - Integer aggregateDeId, String aggregateDeName, Integer optionComboId, String optionComboName, int attributeOptioncomboId, Integer deSumId, - Collection orgunitIds, Period period ) - { + @Override + public void insertAggregateValue( String sql, int dataElementId, int optionComboId, Collection orgunitIds, + Period period ) + { + try + { + int periodId = 0; + periodId = jdbcTemplate.queryForInt( "select periodid from period where periodtypeid = ( select periodtypeid from periodtype where name='" + + period.getPeriodType().getName() + + "' )" + + " and startdate='" + + DateUtils.getMediumDateString( period.getStartDate() ) + + "' and enddate='" + + DateUtils.getMediumDateString( period.getEndDate() ) + "'" ); + + if ( periodId == 0 ) + { + String insertSql = "insert into period (periodid, periodtypeid,startdate,enddate) " + " VALUES " + "(" + + statementBuilder.getAutoIncrementValue() + ","+ period.getPeriodType().getId() + ",'" + DateUtils.getMediumDateString( period.getStartDate() ) + + "','" + DateUtils.getMediumDateString( period.getEndDate() ) + "' )"; + + jdbcTemplate.update( insertSql ); + } + else + { + String deleteDataValueSql = "delete from datavalue where dataelementid=" + dataElementId + + " and categoryoptioncomboid=" + optionComboId + " and sourceid in (" + + TextUtils.getCommaDelimitedString( orgunitIds ) + ") " + + "and periodid = " + periodId; + jdbcTemplate.update( deleteDataValueSql ); + } + + jdbcTemplate.update( sql ); + + } + catch ( Exception ex ) + { + ex.printStackTrace(); + } + } + + @Override + public String parseExpressionToSql( boolean isInsert, CaseAggregationCondition aggregationCondition, + int attributeOptionComboId, Collection orgunitIds ) + { + String caseExpression = aggregationCondition.getAggregationExpression(); + String operator = aggregationCondition.getOperator(); + Integer aggregateDeId = aggregationCondition.getAggregationDataElement().getId(); + String aggregateDeName = aggregationCondition.getAggregationDataElement().getName(); + Integer optionComboId = aggregationCondition.getOptionCombo().getId(); + String optionComboName = aggregationCondition.getOptionCombo().getName(); + Integer deSumId = (aggregationCondition.getDeSum() == null) ? null : aggregationCondition.getDeSum().getId(); + + return parseExpressionToSql( isInsert, caseExpression, operator, aggregateDeId, aggregateDeName, optionComboId, + optionComboName, attributeOptionComboId, deSumId, orgunitIds ); + } + + @Override + public String parseExpressionToSql( boolean isInsert, String caseExpression, String operator, Integer aggregateDeId, + String aggregateDeName, Integer optionComboId, String optionComboName, int attributeOptioncomboId, Integer deSumId, + Collection orgunitIds ) + { String sql = "SELECT '" + aggregateDeId + "' as dataelementid, '" + optionComboId + "' as categoryoptioncomboid, '" + attributeOptioncomboId - + "' as attributeoptioncomboid, ou.organisationunitid as sourceid, '" + period.getId() + "' as periodid,'" + + "' as attributeoptioncomboid, ou.organisationunitid as sourceid, '" + PARAM_PERIOD_ID + "' as periodid,'" + CaseAggregationCondition.AUTO_STORED_BY + "' as storedby, "; if ( isInsert ) { - sql = "INSERT INTO datavalue (dataelementid, categoryoptioncomboid, attributeoptioncomboid, sourceid, periodid, storedby, value) " - + sql; + sql = "INSERT INTO datavalue (dataelementid, categoryoptioncomboid, attributeoptioncomboid, sourceid, periodid, storedby, lastupdated, followup, created, value) " + + sql + " now(), false, now(), "; } else { - sql += "'" + period.getIsoDate() + "' as periodIsoDate,'" + aggregateDeName + "' as dataelementname, '" + sql += "'" + PARAM_PERIOD_ISO_DATE + "' as periodIsoDate,'" + aggregateDeName + "' as dataelementname, '" + optionComboName + "' as categoryoptioncomboname, ou.name as organisationunitname, "; } @@ -356,9 +403,7 @@ { if ( hasOrgunitProgramStageCompleted( caseExpression ) ) { - sql += createSQL( caseExpression, operator, orgunitIds, - DateUtils.getMediumDateString( period.getStartDate() ), - DateUtils.getMediumDateString( period.getEndDate() ) ); + sql += createSQL( caseExpression, operator, orgunitIds ); } else { @@ -394,93 +439,81 @@ sql += " INNER JOIN organisationunit ou ON ou.organisationunitid=psi.organisationunitid "; } - sql += " WHERE " - + createSQL( caseExpression, operator, orgunitIds, - DateUtils.getMediumDateString( period.getStartDate() ), - DateUtils.getMediumDateString( period.getEndDate() ) ); + sql += " WHERE " + createSQL( caseExpression, operator, orgunitIds ); sql += "GROUP BY ou.organisationunitid, ou.name"; } } else - { + { sql += " " + operator + "( cast( pdv.value as " + statementBuilder.getDoubleColumnType() + " ) ) "; sql += "FROM trackedentitydatavalue pdv "; sql += " INNER JOIN programstageinstance psi "; sql += " ON psi.programstageinstanceid = pdv.programstageinstanceid "; sql += " INNER JOIN organisationunit ou "; sql += " ON ou.organisationunitid=psi.organisationunitid "; - sql += "WHERE executiondate >='" + DateUtils.getMediumDateString( period.getStartDate() ) + "' "; - sql += " AND executiondate <='" + DateUtils.getMediumDateString( period.getEndDate() ) - + "' AND pdv.dataelementid=" + deSumId; + sql += "WHERE executiondate >='" + PARAM_PERIOD_START_DATE + "' "; + sql += " AND executiondate <='" + PARAM_PERIOD_END_DATE + "' AND pdv.dataelementid=" + deSumId; if ( caseExpression != null && !caseExpression.isEmpty() ) { - sql += " AND " - + createSQL( caseExpression, operator, orgunitIds, - DateUtils.getMediumDateString( period.getStartDate() ), - DateUtils.getMediumDateString( period.getEndDate() ) ); + sql += " AND " + createSQL( caseExpression, operator, orgunitIds ); } sql += "GROUP BY ou.organisationunitid, ou.name"; - + } - sql = sql.replaceAll( "COMBINE", "" ); - return sql; } @Override public void runAggregate( Collection orgunitIds, CaseAggregateSchedule dataSet, Collection periods, int attributeOptioncomboId ) { - String sql = "select caseaggregationconditionid, aggregationdataelementid, optioncomboid, " - + " cagg.aggregationexpression as caseexpression, cagg.operator as caseoperator, cagg.desum as desumid " - + " from caseaggregationcondition cagg inner join datasetmembers dm " - + " on cagg.aggregationdataelementid=dm.dataelementid inner join dataset ds " - + " on ds.datasetid = dm.datasetid inner join periodtype pt " - + " on pt.periodtypeid=ds.periodtypeid inner join dataelement de " - + " on de.dataelementid=dm.dataelementid where ds.datasetid = " + dataSet.getDataSetId(); - - SqlRowSet rs = jdbcTemplate.queryForRowSet( sql ); - - while ( rs.next() ) - { - for ( Period period : periods ) + Collection _orgunitIds = getServiceOrgunit(); + + if ( orgunitIds == null ) + { + orgunitIds = new HashSet<>(); + orgunitIds.addAll( _orgunitIds ); + } + else + { + orgunitIds.retainAll( _orgunitIds ); + } + + if(orgunitIds.size() > 0 ) + { + String sql = "select caseaggregationconditionid, aggregationdataelementid, optioncomboid, " + + " cagg.aggregationexpression as caseexpression, cagg.operator as caseoperator, cagg.desum as desumid " + + " from caseaggregationcondition cagg inner join datasetmembers dm " + + " on cagg.aggregationdataelementid=dm.dataelementid inner join dataset ds " + + " on ds.datasetid = dm.datasetid inner join periodtype pt " + + " on pt.periodtypeid=ds.periodtypeid inner join dataelement de " + + " on de.dataelementid=dm.dataelementid where ds.datasetid = " + dataSet.getDataSetId(); + + SqlRowSet rs = jdbcTemplate.queryForRowSet( sql ); + + while ( rs.next() ) { - // ------------------------------------------------------------- - // Get formula, agg-dataelement and option-combo - // ------------------------------------------------------------- - - int dataelementId = rs.getInt( "aggregationdataelementid" ); - int optionComboId = rs.getInt( "optioncomboid" ); - String caseExpression = rs.getString( "caseexpression" ); - String caseOperator = rs.getString( "caseoperator" ); - int deSumId = rs.getInt( "desumid" ); - - Collection _orgunitIds = getServiceOrgunit(); - - if ( orgunitIds == null ) - { - orgunitIds = new HashSet<>(); - orgunitIds.addAll( _orgunitIds ); - } - else - { - orgunitIds.retainAll( _orgunitIds ); - } - - // --------------------------------------------------------------------- - // Aggregation - // --------------------------------------------------------------------- - - if ( !orgunitIds.isEmpty() ) - { - insertAggregateValue( caseExpression, caseOperator, dataelementId, optionComboId, attributeOptioncomboId, deSumId, - orgunitIds, period ); - } + String caseExpression = rs.getString( "caseexpression" ); + int dataElementId = rs.getInt( "aggregationdataelementid" ); + int optionComboId = rs.getInt( "optioncomboid" ); + String caseOperator = rs.getString( "caseoperator" ); + int deSumId = rs.getInt( "desumid" ); + String insertParamsSql = parseExpressionToSql( true, caseExpression, caseOperator, dataElementId, "de_name", optionComboId, "optioncombo_name", attributeOptioncomboId, deSumId, _orgunitIds ); + + for ( Period period : periods ) + { + String insertSql = replacePeriodSql( insertParamsSql, period ); + + if ( !orgunitIds.isEmpty() ) + { + insertAggregateValue( insertSql, dataElementId, optionComboId, _orgunitIds, period ); + } + } + } - } } @@ -494,11 +527,8 @@ * @param deType Aggregate Data element type * @param orgunitIds The ids of organisation units where to aggregate data * value - * @param startDate Start date - * @param endDate End date */ - private String createSQL( String caseExpression, String operator, Collection orgunitIds, String startDate, - String endDate ) + private String createSQL( String caseExpression, String operator, Collection orgunitIds ) { caseExpression = caseExpression.replaceAll( "\"", "'" ); boolean orgunitCompletedProgramStage = false; @@ -522,7 +552,7 @@ minus2SQLMap.put( idx2, getConditionForMisus2DataElement( orgunitIds, ids1[1], ids1[2], ids2[1], ids2[2], - matcherMinus2.group( 6 ) + matcherMinus2.group( 7 ), startDate, endDate ) ); + matcherMinus2.group( 6 ) + matcherMinus2.group( 7 ) ) ); caseExpression = caseExpression.replace( matcherMinus2.group( 0 ), CaseAggregationCondition.MINUS_DATAELEMENT_OPERATOR + "_" + idx2 ); @@ -538,21 +568,18 @@ Pattern patternAttrMinus2 = Pattern.compile( CaseAggregationCondition.minusAttributeRegExp ); Matcher matcherAttrMinus2 = patternAttrMinus2.matcher( caseExpression ); while ( matcherAttrMinus2.find() ) - { - String attribute1 = matcherAttrMinus2.group( 2 ); - String attribute2 = matcherAttrMinus2.group( 5 ); - String compareSide = matcherAttrMinus2.group( 6 ) + matcherAttrMinus2.group( 7 ); - minus2AttributeSQLMap.put( - idx1, - getConditionForMisus2Attribute(attribute1, attribute2, compareSide )); - + { + String attribute1 = matcherAttrMinus2.group( 2 ); + String attribute2 = matcherAttrMinus2.group( 5 ); + String compareSide = matcherAttrMinus2.group( 6 ) + matcherAttrMinus2.group( 7 ); + minus2AttributeSQLMap.put( idx1, getConditionForMisus2Attribute( attribute1, attribute2, compareSide ) ); + caseExpression = caseExpression.replace( matcherAttrMinus2.group( 0 ), CaseAggregationCondition.MINUS_ATTRIBUTE_OPERATOR + "_" + idx1 ); idx1++; } - // Get minus(date dataelement, date) out from the expression and run // them later @@ -567,7 +594,7 @@ minusSQLMap.put( idx, getConditionForMinusDataElement( orgunitIds, Integer.parseInt( ids[1] ), Integer.parseInt( ids[2] ), - matcherMinus.group( 4 ), startDate, endDate ) ); + matcherMinus.group( 4 ) ) ); caseExpression = caseExpression.replace( matcherMinus.group( 0 ), CaseAggregationCondition.MINUS_OPERATOR + "_" + idx ); @@ -618,18 +645,17 @@ String compareValue = expression[index].replace( "[" + match + "]", "" ).trim(); boolean isExist = compareValue.equals( IS_NULL ) ? false : true; - condition = getConditionForDataElement( isExist, programId, programStageId, dataElementId, orgunitIds, - startDate, endDate ); + condition = getConditionForDataElement( isExist, programId, programStageId, dataElementId, orgunitIds ); } else if ( info[0].equalsIgnoreCase( OBJECT_PROGRAM_PROPERTY ) ) { - condition = getConditionForProgramProperty( operator, startDate, endDate, info[1] ); + condition = getConditionForProgramProperty( operator, info[1] ); } else if ( info[0].equalsIgnoreCase( OBJECT_PROGRAM ) ) { String[] ids = info[1].split( SEPARATOR_ID ); - condition = getConditionForProgram( ids[0], operator, orgunitIds, startDate, endDate ); + condition = getConditionForProgram( ids[0], operator, orgunitIds ); if ( ids.length > 1 ) { condition += ids[1]; @@ -640,25 +666,25 @@ String[] ids = info[1].split( SEPARATOR_ID ); if ( ids.length == 2 && ids[1].equals( IN_CONDITION_COUNT_X_TIMES ) ) { - condition = getConditionForCountProgramStage( ids[0], operator, orgunitIds, startDate, endDate ); + condition = getConditionForCountProgramStage( ids[0], operator, orgunitIds ); } else { - condition = getConditionForProgramStage( ids[0], orgunitIds, startDate, endDate ); + condition = getConditionForProgramStage( ids[0], orgunitIds ); } } else if ( info[0].equalsIgnoreCase( OBJECT_PROGRAM_STAGE_PROPERTY ) ) { - condition = getConditionForProgramStageProperty( info[1], operator, orgunitIds, startDate, endDate ); + condition = getConditionForProgramStageProperty( info[1], operator, orgunitIds ); } else if ( info[0].equalsIgnoreCase( OBJECT_TRACKED_ENTITY_PROGRAM_STAGE_PROPERTY ) ) { - condition = getConditionForTrackedEntityProgramStageProperty( info[1], operator, startDate, endDate ); + condition = getConditionForTrackedEntityProgramStageProperty( info[1], operator ); } else if ( info[0].equalsIgnoreCase( OBJECT_ORGUNIT_COMPLETE_PROGRAM_STAGE ) ) { sqlOrgunitCompleted += getConditionForOrgunitProgramStageCompleted( info[1], operator, orgunitIds, - startDate, endDate, orgunitCompletedProgramStage ); + orgunitCompletedProgramStage ); orgunitCompletedProgramStage = true; } @@ -692,14 +718,13 @@ sql = sql .replace( CaseAggregationCondition.MINUS_DATAELEMENT_OPERATOR + "_" + key, minus2SQLMap.get( key ) ); } - + for ( int key = 0; key < idx1; key++ ) { - sql = sql - .replace( CaseAggregationCondition.MINUS_ATTRIBUTE_OPERATOR + "_" + key, minus2AttributeSQLMap.get( key ) ); + sql = sql.replace( CaseAggregationCondition.MINUS_ATTRIBUTE_OPERATOR + "_" + key, + minus2AttributeSQLMap.get( key ) ); } - return sql + " ) "; } @@ -708,7 +733,7 @@ * */ private String getConditionForDataElement( boolean isExist, int programId, String programStageId, - int dataElementId, Collection orgunitIds, String startDate, String endDate ) + int dataElementId, Collection orgunitIds ) { String keyExist = (isExist == true) ? "EXISTS" : "NOT EXISTS"; @@ -718,8 +743,8 @@ + "ON _pi.programinstanceid=_psi.programinstanceid " + "WHERE psi.programstageinstanceid=_pdv.programstageinstanceid AND _pdv.dataelementid=" + dataElementId + " AND _psi.organisationunitid in (" + TextUtils.getCommaDelimitedString( orgunitIds ) + ") " - + " AND _pi.programid = " + programId + " AND _psi.executionDate>='" + startDate - + "' AND _psi.executionDate <= '" + endDate + "' "; + + " AND _pi.programid = " + programId + " AND _psi.executionDate>='" + PARAM_PERIOD_START_DATE + + "' AND _psi.executionDate <= '" + PARAM_PERIOD_END_DATE + "' "; if ( !programStageId.equals( IN_CONDITION_GET_ALL ) ) { @@ -738,10 +763,10 @@ sql += " AND _pdv.value "; } } - - if( !isExist ) + + if ( !isExist ) { - sql = "(" + sql + " ) AND " + getConditionForProgramStage( programStageId, orgunitIds, startDate, endDate ) + ")"; + sql = "(" + sql + " ) AND " + getConditionForProgramStage( programStageId, orgunitIds ) + ")"; } return sql; @@ -756,7 +781,6 @@ boolean isExist ) { String sql = " SELECT * FROM trackedentityattributevalue _pav "; - if ( attributeId.split( SEPARATOR_ID ).length == 2 ) { @@ -764,23 +788,26 @@ { sql += " inner join programinstance _pi on _pav.trackedentityinstanceid=_pi.trackedentityinstanceid "; sql += " inner join programstageinstance _psi on _pi.programinstanceid=_psi.programinstanceid "; - + attributeId = attributeId.split( SEPARATOR_ID )[0]; - sql += " WHERE _pav.trackedentityinstanceid=pi.trackedentityinstanceid AND _pav.trackedentityattributeid=" + attributeId + " AND DATE(_psi.executiondate) - DATE( _pav.value ) "; + sql += " WHERE _pav.trackedentityinstanceid=pi.trackedentityinstanceid AND _pav.trackedentityattributeid=" + + attributeId + " AND DATE(_psi.executiondate) - DATE( _pav.value ) "; } - else if ( attributeId.split( SEPARATOR_ID )[1].equals( CaseAggregationCondition.FORMULA_AGE ) ) + else if ( attributeId.split( SEPARATOR_ID )[1].equals( CaseAggregationCondition.FORMULA_AGE ) ) { sql += " inner join programinstance _pi on _pav.trackedentityinstanceid=_pi.trackedentityinstanceid "; - + attributeId = attributeId.split( SEPARATOR_ID )[0]; - sql += " WHERE _pav.trackedentityinstanceid=pi.trackedentityinstanceid AND _pav.trackedentityattributeid=" + attributeId + " AND DATE(_psi.enrollmentdate) - DATE( _pav.value ) "; + sql += " WHERE _pav.trackedentityinstanceid=pi.trackedentityinstanceid AND _pav.trackedentityattributeid=" + + attributeId + " AND DATE(_psi.enrollmentdate) - DATE( _pav.value ) "; } } else { - sql += " WHERE _pav.trackedentityinstanceid=pi.trackedentityinstanceid AND _pav.trackedentityattributeid=" + attributeId; - - if( isExist ) + sql += " WHERE _pav.trackedentityinstanceid=pi.trackedentityinstanceid AND _pav.trackedentityattributeid=" + + attributeId; + + if ( isExist ) { sql += " AND _pav.value "; } @@ -803,12 +830,11 @@ * [PC:executionDate] * */ - private String getConditionForTrackedEntityProgramStageProperty( String propertyName, String operator, - String startDate, String endDate ) + private String getConditionForTrackedEntityProgramStageProperty( String propertyName, String operator ) { String sql = " EXISTS ( SELECT _psi.programstageinstanceid from programstageinstance _psi " + "WHERE _psi.programstageinstanceid=psi.programstageinstanceid AND ( _psi.executionDate BETWEEN '" - + startDate + "' AND '" + endDate + "') AND " + propertyName; + + PARAM_PERIOD_START_DATE + "' AND '" + PARAM_PERIOD_END_DATE + "') AND " + propertyName; return sql; } @@ -819,16 +845,13 @@ * days between date of enrollment and date of incident. * */ - private String getConditionForProgramProperty( String operator, String startDate, String endDate, String property ) + private String getConditionForProgramProperty( String operator, String property ) { String sql = " EXISTS ( SELECT _pi.programinstanceid FROM programinstance as _pi WHERE _pi.programinstanceid=pi.programinstanceid AND " + "pi.enrollmentdate >= '" - + startDate + + PARAM_PERIOD_START_DATE + "' AND pi.enrollmentdate <= '" - + endDate - + "' AND " - + property - + " "; + + PARAM_PERIOD_END_DATE + "' AND " + property + " "; return sql; } @@ -838,15 +861,17 @@ * program. E.g [PG:1] * */ - private String getConditionForProgram( String programId, String operator, Collection orgunitIds, - String startDate, String endDate ) + private String getConditionForProgram( String programId, String operator, Collection orgunitIds ) { String sql = " EXISTS ( SELECT * FROM programinstance as _pi inner join trackedentityinstance _p on _p.trackedentityinstanceid=_pi.trackedentityinstanceid " + "WHERE _pi.trackedentityinstanceid=pi.trackedentityinstanceid AND _pi.programid=" + programId + " AND _p.organisationunitid in (" + TextUtils.getCommaDelimitedString( orgunitIds ) - + ") AND _pi.enrollmentdate >= '" + startDate + "' AND _pi.enrollmentdate <= '" + endDate + "' "; + + ") AND _pi.enrollmentdate >= '" + + PARAM_PERIOD_START_DATE + + "' AND _pi.enrollmentdate <= '" + + PARAM_PERIOD_END_DATE + "' "; return sql; } @@ -856,13 +881,13 @@ * [PS:1] * */ - private String getConditionForProgramStage( String programStageId, Collection orgunitIds, - String startDate, String endDate ) + private String getConditionForProgramStage( String programStageId, Collection orgunitIds ) { String sql = " EXISTS ( SELECT _psi.programstageinstanceid FROM programstageinstance _psi " + "WHERE _psi.programstageinstanceid=psi.programstageinstanceid " + "AND _psi.programstageid=" - + programStageId + " AND _psi.executiondate >= '" + startDate + "' AND _psi.executiondate <= '" + endDate - + "' AND _psi.organisationunitid in (" + TextUtils.getCommaDelimitedString( orgunitIds ) + ") "; + + programStageId + " AND _psi.executiondate >= '" + PARAM_PERIOD_START_DATE + + "' AND _psi.executiondate <= '" + PARAM_PERIOD_END_DATE + "' AND _psi.organisationunitid in (" + + TextUtils.getCommaDelimitedString( orgunitIds ) + ") "; return sql; } @@ -874,13 +899,13 @@ * */ private String getConditionForCountProgramStage( String programStageId, String operator, - Collection orgunitIds, String startDate, String endDate ) + Collection orgunitIds ) { String sql = " EXISTS ( SELECT _psi.programstageinstanceid FROM programstageinstance as _psi " + "WHERE psi.programstageinstanceid=_psi.programstageinstanceid AND _psi.organisationunitid in (" + TextUtils.getCommaDelimitedString( orgunitIds ) + ") and _psi.programstageid = " + programStageId + " " - + "AND _psi.executionDate >= '" + startDate + "' AND _psi.executionDate <= '" + endDate + "' " - + "GROUP BY _psi.programinstanceid,_psi.programstageinstanceid " + + "AND _psi.executionDate >= '" + PARAM_PERIOD_START_DATE + "' AND _psi.executionDate <= '" + + PARAM_PERIOD_END_DATE + "' " + "GROUP BY _psi.programinstanceid,_psi.programstageinstanceid " + "HAVING count(_psi.programstageinstanceid) "; return sql; @@ -892,13 +917,13 @@ * and due-date. E.g [PSP:DATE@executionDate#-DATE@dueDate#] * */ - private String getConditionForProgramStageProperty( String property, String operator, - Collection orgunitIds, String startDate, String endDate ) + private String getConditionForProgramStageProperty( String property, String operator, Collection orgunitIds ) { String sql = " EXISTS ( SELECT * FROM programstageinstance _psi " - + "WHERE psi.programstageinstanceid=_psi.programstageinstanceid AND _psi.executiondate >= '" + startDate - + "' AND _psi.executiondate <= '" + endDate + "' AND _psi.organisationunitid in (" - + TextUtils.getCommaDelimitedString( orgunitIds ) + ") AND " + property + " "; + + "WHERE psi.programstageinstanceid=_psi.programstageinstanceid AND _psi.executiondate >= '" + + PARAM_PERIOD_START_DATE + "' AND _psi.executiondate <= '" + PARAM_PERIOD_END_DATE + + "' AND _psi.organisationunitid in (" + TextUtils.getCommaDelimitedString( orgunitIds ) + ") AND " + + property + " "; return sql; } @@ -911,7 +936,7 @@ * */ private String getConditionForOrgunitProgramStageCompleted( String programStageId, String operator, - Collection orgunitIds, String startDate, String endDate, boolean flag ) + Collection orgunitIds, boolean flag ) { String sql = ""; if ( !flag ) @@ -922,14 +947,14 @@ sql += " AND EXISTS ( SELECT programstageinstanceid FROM programstageinstance _psi " + " WHERE _psi.organisationunitid=ou.organisationunitid AND _psi.programstageid = " + programStageId - + " AND _psi.completed=true AND _psi.executiondate >= '" + startDate + "' AND _psi.executiondate <= '" - + endDate + "' ) "; + + " AND _psi.completed=true AND _psi.executiondate >= '" + PARAM_PERIOD_START_DATE + + "' AND _psi.executiondate <= '" + PARAM_PERIOD_END_DATE + "' ) "; return sql; } private String getConditionForMinusDataElement( Collection orgunitIds, Integer programStageId, - Integer dataElementId, String compareSide, String startDate, String endDate ) + Integer dataElementId, String compareSide ) { return " EXISTS ( SELECT _pdv.value FROM trackedentitydatavalue _pdv inner join programstageinstance _psi " + " ON _pdv.programstageinstanceid=_psi.programstageinstanceid " @@ -938,13 +963,12 @@ + " AND _pdv.dataelementid=" + dataElementId + " AND _psi.organisationunitid in (" + TextUtils.getCommaDelimitedString( orgunitIds ) + ") " + " AND _psi.programstageid = " + programStageId - + " AND ( _psi.executionDate BETWEEN '" + startDate + "' AND '" + endDate + "') " - + " AND ( DATE(_pdv.value) - DATE(" + compareSide + ") ) "; + + " AND ( _psi.executionDate BETWEEN '" + PARAM_PERIOD_START_DATE + "' AND '" + PARAM_PERIOD_END_DATE + + "') " + " AND ( DATE(_pdv.value) - DATE(" + compareSide + ") ) "; } private String getConditionForMisus2DataElement( Collection orgunitIds, String programStageId1, - String dataElementId1, String programStageId2, String dataElementId2, String compareSide, String startDate, - String endDate ) + String dataElementId1, String programStageId2, String dataElementId2, String compareSide ) { return " EXISTS ( SELECT * FROM ( SELECT _pdv.value FROM trackedentitydatavalue _pdv " + " INNER JOIN programstageinstance _psi ON _pdv.programstageinstanceid=_psi.programstageinstanceid " @@ -957,10 +981,10 @@ + " AND _psi.programstageid = " + programStageId1 + " AND _psi.executionDate>='" - + startDate + + PARAM_PERIOD_START_DATE + "' " + " AND _psi.executionDate <= '" - + endDate + + PARAM_PERIOD_END_DATE + "' ) AS d1 cross join " + " ( SELECT _pdv.value FROM trackedentitydatavalue _pdv INNER JOIN programstageinstance _psi " + " ON _pdv.programstageinstanceid=_psi.programstageinstanceid " @@ -973,16 +997,16 @@ + " AND _psi.programstageid = " + programStageId2 + " AND _psi.executionDate>='" - + startDate + + PARAM_PERIOD_START_DATE + "' " + " AND _psi.executionDate <= '" - + endDate + + PARAM_PERIOD_END_DATE + "' ) AS d2 WHERE DATE(d1.value ) - DATE(d2.value) " + compareSide; } - - private String getConditionForMisus2Attribute(String attribute1, String attribute2, String compareSide) + + private String getConditionForMisus2Attribute( String attribute1, String attribute2, String compareSide ) { - return " EXISTS ( SELECT * FROM ( SELECT _teav.value FROM trackedentityattributevalue _teav " + return " EXISTS ( SELECT * FROM ( SELECT _teav.value FROM trackedentityattributevalue _teav " + " WHERE _teav.trackedentityinstanceid=p.trackedentityinstanceid " + " and _teav.trackedentityattributeid = " + attribute1 + " ) as a1 , " + " ( SELECT _teav.value FROM trackedentityattributevalue _teav " @@ -991,13 +1015,13 @@ + " WHERE DATE(a1.value ) - DATE(a2.value) " + compareSide; } - /** * Return the Ids of organisation units which entity instances registered or * events happened. * */ - private Collection getServiceOrgunit() + @Override + public Collection getServiceOrgunit() { String sql = "(select distinct organisationunitid from trackedentityinstance)"; sql += " UNION "; @@ -1020,16 +1044,13 @@ Period period, boolean nonRegistrationProgram ) { String sql = "SELECT "; - + Collection orgunitIds = new HashSet<>(); orgunitIds.add( orgunitId ); if ( hasOrgunitProgramStageCompleted( caseExpression ) ) { - sql += "ou.name " - + createSQL( caseExpression, operator, orgunitIds, - DateUtils.getMediumDateString( period.getStartDate() ), - DateUtils.getMediumDateString( period.getEndDate() ) ); + sql += "ou.name " + createSQL( caseExpression, operator, orgunitIds ); } else if ( nonRegistrationProgram ) { @@ -1051,18 +1072,16 @@ sql += "INNER JOIN programinstance as pi on p.trackedentityinstanceid=pi.trackedentityinstanceid "; sql += "INNER JOIN program pg on pg.programid=pi.programid "; sql += "INNER JOIN programstage pgs on pgs.programid=pg.programid "; - - if( hasDataelementCriteria( caseExpression ) ) + + if ( hasDataelementCriteria( caseExpression ) ) { sql += " INNER JOIN programstageinstance psi on pi.programinstanceid=psi.programinstanceid "; sql += " INNER JOIN organisationunit ou on ou.organisationunitid=psi.organisationunitid "; sql += " INNER JOIN trackedentitydatavalue pdv on pdv.programstageinstanceid=psi.programstageinstanceid "; } - } + } - sql += " WHERE " - + createSQL( caseExpression, operator, orgunitIds, DateUtils.getMediumDateString( period.getStartDate() ), - DateUtils.getMediumDateString( period.getEndDate() ) ); + sql += " WHERE " + createSQL( caseExpression, operator, orgunitIds ); sql = sql.replaceAll( "COMBINE", "" ); @@ -1075,6 +1094,8 @@ sql += " ORDER BY p.trackedentityinstanceid "; } + sql = replacePeriodSql( sql, period ); + return sql; } @@ -1148,11 +1169,12 @@ { periodid = rs.getInt( "periodid" ); } - + if ( periodid == null ) { String insertSql = "insert into period (periodid, periodtypeid,startdate,enddate) " + " VALUES " + "(" - + statementBuilder.getAutoIncrementValue() +"," + periodTypeId + ",'" + start + "','" + end + "' )"; + + statementBuilder.getAutoIncrementValue() + "," + periodTypeId + ",'" + start + "','" + end + + "' )"; jdbcTemplate.execute( insertSql ); period.setId( jdbcTemplate.queryForObject( sql, Integer.class ) ); @@ -1231,6 +1253,17 @@ return false; } + + + private String replacePeriodSql( String sql, Period period ) + { + sql = sql.replaceAll( "COMBINE", "" ); + sql = sql.replaceAll( PARAM_PERIOD_START_DATE, DateUtils.getMediumDateString( period.getStartDate() ) ); + sql = sql.replaceAll( PARAM_PERIOD_END_DATE, DateUtils.getMediumDateString( period.getEndDate() ) ); + sql = sql.replaceAll( PARAM_PERIOD_ID, period.getId() + "" ); + sql = sql.replaceAll( PARAM_PERIOD_ISO_DATE, period.getIsoDate() ); + + return sql; + } - } === modified file 'dhis-2/dhis-web/dhis-web-caseentry/src/main/java/org/hisp/dhis/caseentry/action/caseaggregation/CaseAggregationResultAction.java' --- dhis-2/dhis-web/dhis-web-caseentry/src/main/java/org/hisp/dhis/caseentry/action/caseaggregation/CaseAggregationResultAction.java 2014-08-15 07:40:20 +0000 +++ dhis-2/dhis-web/dhis-web-caseentry/src/main/java/org/hisp/dhis/caseentry/action/caseaggregation/CaseAggregationResultAction.java 2014-09-08 14:40:15 +0000 @@ -165,7 +165,7 @@ DataSet selectedDataSet = dataSetService.getDataSet( dataSetId ); Collection aggregationConditions = aggregationConditionService - .getCaseAggregationConditions( selectedDataSet.getDataElements(), null, null , null ); + .getCaseAggregationConditions( selectedDataSet.getDataElements(), null, null, null ); // --------------------------------------------------------------------- // Get selected periods list @@ -212,20 +212,14 @@ // Aggregation // --------------------------------------------------------------------- - for ( CaseAggregationCondition condition : aggregationConditions ) - { - for ( Period period : periods ) - { - if ( autoSave ) - { - aggregationConditionService.insertAggregateValue( condition, orgunitIds, period ); - } - else - { - grids.add( aggregationConditionService.getAggregateValue( condition, orgunitIds, period, format, - i18n ) ); - } - } + if ( autoSave ) + { + aggregationConditionService.insertAggregateValue( aggregationConditions, orgunitIds, periods ); + } + else + { + grids = aggregationConditionService.getAggregateValue( aggregationConditions, orgunitIds, periods, format, + i18n ); } return SUCCESS; }