=== 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-09-08 14:40:15 +0000 +++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/caseaggregation/CaseAggregationCondition.java 2014-09-22 14:40:22 +0000 @@ -107,7 +107,7 @@ + SEPARATOR_OBJECT + "([a-zA-Z0-9@#\\- ]+[" + SEPARATOR_ID + "[a-zA-Z0-9]*]*)" + "\\]"; public static final String dataelementRegExp = MINUS_OPERATOR + "{1}\\s*\\(\\s*(\\[" - + OBJECT_PROGRAM_STAGE_DATAELEMENT + SEPARATOR_OBJECT + "([0-9]+" + SEPARATOR_ID + "[0-9]+" + SEPARATOR_ID + + OBJECT_PROGRAM_STAGE_DATAELEMENT + SEPARATOR_OBJECT + "([0-9\\*]+" + SEPARATOR_ID + "[0-9\\*]+" + SEPARATOR_ID + "[0-9]+)+\\])\\s*(,)+\\s*(" + OBJECT_PROGRAM_PROPERTY_INCIDENT_DATE + "|" + OBJECT_PROGRAM_PROPERTY_REPORT_DATE_DATE + "|" + OBJECT_PROGRAM_PROPERTY_ENROLLEMENT_DATE + ")+\\s*\\)\\s*"; === 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-09-22 09:56:45 +0000 +++ dhis-2/dhis-services/dhis-service-eventreporting/src/main/java/org/hisp/dhis/caseaggregation/hibernate/HibernateCaseAggregationConditionStore.java 2014-09-22 14:40:22 +0000 @@ -594,9 +594,21 @@ { String[] ids = matcherMinus.group( 2 ).split( SEPARATOR_ID ); + Integer programId = null; + Integer programStageId = null; + if( !ids[1].equals(IN_CONDITION_GET_ALL)) + { + programId = Integer.parseInt( ids[0] ); + } + + if( !ids[1].equals(IN_CONDITION_GET_ALL)) + { + programStageId = Integer.parseInt( ids[1] ); + } + minusSQLMap.put( idx, - getConditionForMinusDataElement( orgunitIds, Integer.parseInt( ids[1] ), Integer.parseInt( ids[2] ), + getConditionForMinusDataElement( orgunitIds, programId, programStageId, Integer.parseInt( ids[2] ), matcherMinus.group( 4 ) ) ); caseExpression = caseExpression.replace( matcherMinus.group( 0 ), CaseAggregationCondition.MINUS_OPERATOR @@ -956,18 +968,32 @@ return sql; } - private String getConditionForMinusDataElement( Collection orgunitIds, Integer programStageId, + private String getConditionForMinusDataElement( Collection orgunitIds, Integer programId, Integer programStageId, Integer dataElementId, String compareSide ) { - return " EXISTS ( SELECT _pdv.value FROM trackedentitydatavalue _pdv inner join programstageinstance _psi " + String sql = " EXISTS ( SELECT _pdv.value FROM trackedentitydatavalue _pdv inner join programstageinstance _psi " + " ON _pdv.programstageinstanceid=_psi.programstageinstanceid " + " JOIN programinstance _pi ON _pi.programinstanceid=_psi.programinstanceid " + " WHERE psi.programstageinstanceid=_pdv.programstageinstanceid " + " AND _pdv.dataelementid=" + dataElementId + " AND _psi.organisationunitid in (" + TextUtils.getCommaDelimitedString( orgunitIds ) - + ") " + " AND _psi.programstageid = " + programStageId - + " AND ( _psi.executionDate BETWEEN '" + PARAM_PERIOD_START_DATE + "' AND '" + PARAM_PERIOD_END_DATE + + ") "; + + + if (programId != null) + { + sql += " AND_pi.programid "; + } + + if (programId != null) + { + sql += " AND _psi.programstageid = " + programStageId; + } + + sql += " AND ( _psi.executionDate BETWEEN '" + PARAM_PERIOD_START_DATE + "' AND '" + PARAM_PERIOD_END_DATE + "') " + " AND ( DATE(_pdv.value) - DATE(" + compareSide + ") ) "; + + return sql; } private String getConditionForMisus2DataElement( Collection orgunitIds, String programStageId1,