dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #32674
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 16657: Fixed bug - The exception thrown when to click Aggregate and Save button in Manual aggregation.
------------------------------------------------------------
revno: 16657
committer: Tran Chau<tran.hispvietnam@xxxxxxxxx>
branch nick: dhis2
timestamp: Mon 2014-09-08 21:40:15 +0700
message:
Fixed bug - The exception thrown when to click Aggregate and Save button in Manual aggregation.
modified:
dhis-2/dhis-api/src/main/java/org/hisp/dhis/caseaggregation/CaseAggregationCondition.java
dhis-2/dhis-api/src/main/java/org/hisp/dhis/caseaggregation/CaseAggregationConditionService.java
dhis-2/dhis-api/src/main/java/org/hisp/dhis/caseaggregation/CaseAggregationConditionStore.java
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/hibernate/HibernateCaseAggregationConditionStore.java
dhis-2/dhis-web/dhis-web-caseentry/src/main/java/org/hisp/dhis/caseentry/action/caseaggregation/CaseAggregationResultAction.java
--
lp:dhis2
https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk
Your team DHIS 2 developers is subscribed to branch lp:dhis2.
To unsubscribe from this branch go to https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk/+edit-subscription
=== modified file 'dhis-2/dhis-api/src/main/java/org/hisp/dhis/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<CaseAggregateSchedule> 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<Integer> 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<Integer> orgunitIds,
- Period period );
+ void insertAggregateValue( Collection<CaseAggregationCondition> caseAggregationConditions, Collection<Integer> orgunitIds, Collection<Period> periods );
/**
* Retrieve the details of each {@link DataValue} which are generated by a
@@ -275,4 +261,17 @@
* @return
*/
int countCaseAggregationCondition( Collection<DataElement> 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<Grid> getAggregateValue( Collection<CaseAggregationCondition> caseAggregationConditions, Collection<Integer> orgunitIds,
+ Collection<Period> 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<Period> 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<Integer> 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<Integer> orgunitIds, Period period );
-
+ void insertAggregateValue( String sql, int dataElementId, int optionComboId, Collection<Integer> 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<Integer> orgunitIds, Period period );
+ Collection<Integer> orgunitIds );
+
+ /**
+ * Return standard SQL from query builder formula
+ * @param aggregationCondition {@link CaseAggregationCondition}
+ * @param attributeOptionComboId
+ *
+ */
+ String parseExpressionToSql( boolean isInsert, CaseAggregationCondition aggregationCondition, int attributeOptionComboId, Collection<Integer> orgunitIds );
/**
* Aggregate data values for the dataset by periods with a organisation unit
@@ -185,4 +179,23 @@
* @return
*/
int count( Collection<DataElement> dataElements, String key );
+
+ public Collection<Integer> 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<Integer> 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<Integer> orgunitIds,
- Period period, I18nFormat format, I18n i18n )
+ @Override
+ public List<Grid> getAggregateValue( Collection<CaseAggregationCondition> caseAggregationConditions, Collection<Integer> orgunitIds,
+ Collection<Period> periods, I18nFormat format, I18n i18n )
{
- periodService.reloadPeriod( period );
+ Collection<Integer> _orgunitIds = aggregationConditionStore.getServiceOrgunit();
+ _orgunitIds.retainAll( orgunitIds );
+ if ( _orgunitIds.size() > 0 )
+ {
+ int attributeOptioncomboId = categoryService.getDefaultDataElementCategoryOptionCombo().getId();
+ List<Grid> 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<Integer> orgunitIds, Period period )
+ @Override
+ public void insertAggregateValue( Collection<CaseAggregationCondition> caseAggregationConditions,
+ Collection<Integer> orgunitIds, Collection<Period> 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<Integer> _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> 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<DataElement> 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<CaseAggregationCondition> get( Collection<DataElement> dataElements, String key, Integer first, Integer max )
+ public Collection<CaseAggregationCondition> get( Collection<DataElement> 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<Integer> orgunitIds,
- Period period, int attributeOptioncomboId, I18nFormat format, I18n i18n )
+ public Grid getAggregateValue( String sql, I18nFormat format, I18n i18n )
{
- Collection<Integer> _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<Integer> 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<Integer> 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<Integer> orgunitIds, Period period )
- {
+ @Override
+ public void insertAggregateValue( String sql, int dataElementId, int optionComboId, Collection<Integer> 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<Integer> 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<Integer> 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<Integer> orgunitIds, CaseAggregateSchedule dataSet, Collection<Period> 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<Integer> _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<Integer> _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<Integer> orgunitIds, String startDate,
- String endDate )
+ private String createSQL( String caseExpression, String operator, Collection<Integer> 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<Integer> orgunitIds, String startDate, String endDate )
+ int dataElementId, Collection<Integer> 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<Integer> orgunitIds,
- String startDate, String endDate )
+ private String getConditionForProgram( String programId, String operator, Collection<Integer> 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<Integer> orgunitIds,
- String startDate, String endDate )
+ private String getConditionForProgramStage( String programStageId, Collection<Integer> 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<Integer> orgunitIds, String startDate, String endDate )
+ Collection<Integer> 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<Integer> orgunitIds, String startDate, String endDate )
+ private String getConditionForProgramStageProperty( String property, String operator, Collection<Integer> 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<Integer> orgunitIds, String startDate, String endDate, boolean flag )
+ Collection<Integer> 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<Integer> 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<Integer> 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<Integer> getServiceOrgunit()
+ @Override
+ public Collection<Integer> getServiceOrgunit()
{
String sql = "(select distinct organisationunitid from trackedentityinstance)";
sql += " UNION ";
@@ -1020,16 +1044,13 @@
Period period, boolean nonRegistrationProgram )
{
String sql = "SELECT ";
-
+
Collection<Integer> 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<CaseAggregationCondition> 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;
}
