dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #33227
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 16957: Support aggregate query builder formula to calculate the number of days between Date data element...
------------------------------------------------------------
revno: 16957
committer: Tran Chau<tran.hispvietnam@xxxxxxxxx>
branch nick: dhis2
timestamp: Sat 2014-10-04 22:48:07 +0700
message:
Support aggregate query builder formula to calculate the number of days between Date data elements, attributes, enrollment-date, incident-date, reporting-date and current-date.
modified:
dhis-2/dhis-api/src/main/java/org/hisp/dhis/caseaggregation/CaseAggregationCondition.java
dhis-2/dhis-services/dhis-service-eventreporting/src/main/java/org/hisp/dhis/caseaggregation/hibernate/HibernateCaseAggregationConditionStore.java
dhis-2/dhis-services/dhis-service-tracker/src/main/java/org/hisp/dhis/trackedentity/startup/TableAlteror.java
dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-program/src/main/resources/org/hisp/dhis/trackedentity/i18n_module.properties
dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-program/src/main/resources/struts.xml
dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-program/src/main/webapp/dhis-web-maintenance-program/caseAggregationForm.vm
--
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-09-22 14:40:22 +0000
+++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/caseaggregation/CaseAggregationCondition.java 2014-10-04 15:48:07 +0000
@@ -86,14 +86,24 @@
public static String OBJECT_PROGRAM_PROPERTY_ENROLLEMENT_DATE = "enrollmentDate";
- public static String OBJECT_PROGRAM_PROPERTY_REPORT_DATE_DATE = "executionDate";
+ public static String OBJECT_PROGRAM_PROPERTY_REPORT_DATE = "executionDate";
public static String MINUS_OPERATOR = "DATEDIFF";
-
- public static String MINUS_DATAELEMENT_OPERATOR = "DEDATEDIFF";
-
- public static String MINUS_ATTRIBUTE_OPERATOR = "ATTRDATEDIFF";
-
+
+ public static String MINUS_DATAELEMENT_OPERATOR_TYPE_ONE = "DEDATEDIFF_TYPE_ONE";
+
+ public static String MINUS_DATAELEMENT_OPERATOR_TYPE_TWO = "DEDATEDIFF_TYPE_TWO";
+
+ public static String MINUS_2DATAELEMENT_OPERATOR = "DE2DATEDIFF";
+
+ public static String MINUS_2ATTRIBUTE_OPERATOR = "ATTR2DATEDIFF";
+
+ public static String MINUS_ATTRIBUTE_OPERATOR_TYPE_ONE = "ATTRDATEDIFF_TYPE_ONE";
+
+ public static String MINUS_ATTRIBUTE_OPERATOR_TYPE_TWO = "ATTRDATEDIFF_TYPE_TWO";
+
+ public static String CURRENT_DATE = "current_date";
+
public static String AUTO_STORED_BY = "aggregated_from_tracker";
public static final String PARAM_PERIOD_START_DATE = "PERIOD_START_DATE";
@@ -102,24 +112,48 @@
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
- + "|" + OBJECT_PROGRAM_STAGE_DATAELEMENT + "|" + OBJECT_TRACKED_ENTITY_ATTRIBUTE + "|" + OBJECT_PROGRAM_PROPERTY + ")"
- + 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
- + "[0-9]+)+\\])\\s*(,)+\\s*(" + OBJECT_PROGRAM_PROPERTY_INCIDENT_DATE + "|"
- + OBJECT_PROGRAM_PROPERTY_REPORT_DATE_DATE + "|" + OBJECT_PROGRAM_PROPERTY_ENROLLEMENT_DATE + ")+\\s*\\)\\s*";
-
- public static final String minusDataelementRegExp = MINUS_DATAELEMENT_OPERATOR + "{1}\\s*\\(\\s*(\\["
+ + OBJECT_PROGRAM_STAGE_PROPERTY + "|" + OBJECT_PROGRAM_STAGE + "|"
+ + OBJECT_TRACKED_ENTITY_PROGRAM_STAGE_PROPERTY + "|" + OBJECT_PROGRAM_STAGE_DATAELEMENT + "|"
+ + OBJECT_TRACKED_ENTITY_ATTRIBUTE + "|" + OBJECT_PROGRAM_PROPERTY + ")" + SEPARATOR_OBJECT
+ + "([a-zA-Z0-9@#\\- ]+[" + SEPARATOR_ID + "[a-zA-Z0-9]*]*)" + "\\]";
+
+ // Date dataElement - currentDate/dateOfIncident/executionDate/enrollmentDate
+ public static final String minusDataelementRegExp1 = MINUS_OPERATOR + "{1}\\s*\\(\\s*\\["
+ + OBJECT_PROGRAM_STAGE_DATAELEMENT + SEPARATOR_OBJECT + "([0-9]+)+" + SEPARATOR_ID + "([0-9]+)+" + SEPARATOR_ID
+ + "([0-9]+)+\\]\\s*(,)\\s*" + "(" + CURRENT_DATE + "|" + OBJECT_PROGRAM_PROPERTY_INCIDENT_DATE + "|"
+ + OBJECT_PROGRAM_PROPERTY_ENROLLEMENT_DATE + "|" + OBJECT_PROGRAM_PROPERTY_REPORT_DATE
+ + ")\\s*\\)\\s*(>=|<=|!=|>|<|=){1}\\s*([0-9]+){1}";
+
+ // currentDate/dateOfIncident/executionDate/enrollmentDate - Date dataElement
+ public static final String minusDataelementRegExp2 = MINUS_OPERATOR + "{1}\\s*\\(\\s*(" + CURRENT_DATE + "|"
+ + OBJECT_PROGRAM_PROPERTY_INCIDENT_DATE + "|" + OBJECT_PROGRAM_PROPERTY_ENROLLEMENT_DATE + "|"
+ + OBJECT_PROGRAM_PROPERTY_REPORT_DATE + ")\\s*(,)\\s*\\[" + OBJECT_PROGRAM_STAGE_DATAELEMENT + SEPARATOR_OBJECT
+ + "([0-9]+)+" + SEPARATOR_ID + "([0-9]+)+" + SEPARATOR_ID
+ + "([0-9]+)+\\]\\s*\\)\\s*(>=|<=|!=|>|<|=){1}\\s*([0-9]+){1}";
+
+ // Date dataElement - Date dataElement
+ public static final String minus2DataelementRegExp = MINUS_OPERATOR + "{1}\\s*\\(\\s*(\\["
+ OBJECT_PROGRAM_STAGE_DATAELEMENT + SEPARATOR_OBJECT + "([0-9]+" + SEPARATOR_ID + "[0-9]+" + SEPARATOR_ID
+ "[0-9]+)+\\])\\s*(,)\\s*(\\[" + OBJECT_PROGRAM_STAGE_DATAELEMENT + SEPARATOR_OBJECT + "([0-9]+"
+ SEPARATOR_ID + "[0-9]+" + SEPARATOR_ID + "[0-9]+)+\\])\\s*\\)\\s*(>=|<=|!=|>|<|=){1}\\s*([0-9]+)";
- public static final String minusAttributeRegExp = MINUS_ATTRIBUTE_OPERATOR + "{1}\\s*\\(\\s*(\\["
- + OBJECT_TRACKED_ENTITY_ATTRIBUTE + SEPARATOR_OBJECT + "([0-9]+)+\\])\\s*(,)\\s*(\\["
+
+ // currentDate/ dateOfIncident/executionDate/enrollmentDate - Date attribute
+ public static final String minusAttributeRegExp1 = MINUS_OPERATOR + "{1}\\s*\\(\\s*(" + CURRENT_DATE + "|"
+ + OBJECT_PROGRAM_PROPERTY_INCIDENT_DATE + "|" + OBJECT_PROGRAM_PROPERTY_ENROLLEMENT_DATE + "|"
+ + OBJECT_PROGRAM_PROPERTY_REPORT_DATE + ")\\s*(,)\\s*\\[" + OBJECT_TRACKED_ENTITY_ATTRIBUTE + SEPARATOR_OBJECT
+ + "([0-9]+)+\\]\\s*\\)\\s(>=|<=|!=|>|<|=){1}\\s*([0-9]+){1}";
+
+ // Date attribute - currentDate/ dateOfIncident/executionDate/enrollmentDate
+ public static final String minusAttributeRegExp2 = MINUS_OPERATOR + "{1}\\s*\\(\\s*\\["
+ + OBJECT_TRACKED_ENTITY_ATTRIBUTE + SEPARATOR_OBJECT + "([0-9]+)+\\]\\s*(,)\\s*(" + CURRENT_DATE + "|"
+ + OBJECT_PROGRAM_PROPERTY_INCIDENT_DATE + "|" + OBJECT_PROGRAM_PROPERTY_ENROLLEMENT_DATE + "|"
+ + OBJECT_PROGRAM_PROPERTY_REPORT_DATE + ")\\s*\\)\\s*(>=|<=|!=|>|<|=){1}\\s*([0-9]+){1}";
+
+ // Date attribute - Date attribute
+ public static final String minus2AttributeRegExp = MINUS_OPERATOR + "{1}\\s*\\(\\s*(\\["
+ + OBJECT_TRACKED_ENTITY_ATTRIBUTE + SEPARATOR_OBJECT + "([0-9]+)+\\])\\s*(,)\\s*(\\["
+ OBJECT_TRACKED_ENTITY_ATTRIBUTE + SEPARATOR_OBJECT + "([0-9]+)+\\])\\s*\\)\\s*(>=|<=|!=|>|<|=){1}\\s*([0-9]+)";
-
-
+
// -------------------------------------------------------------------------
// Fields
// -------------------------------------------------------------------------
=== 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-23 14:49:00 +0000
+++ dhis-2/dhis-services/dhis-service-eventreporting/src/main/java/org/hisp/dhis/caseaggregation/hibernate/HibernateCaseAggregationConditionStore.java 2014-10-04 15:48:07 +0000
@@ -466,7 +466,7 @@
sql += "GROUP BY ou.organisationunitid, ou.name";
}
-
+
return sql;
}
@@ -539,84 +539,165 @@
StringBuffer sqlResult = new StringBuffer();
String sqlOrgunitCompleted = "";
-
- // Get minus(DATE dataelement, DATE dataelement) out from the expression
- // and run them later
-
- Map<Integer, String> minus2SQLMap = new HashMap<>();
- int idx2 = 0;
- Pattern patternMinus2 = Pattern.compile( CaseAggregationCondition.minusDataelementRegExp );
+
+ // Date dataElement - dateOfIncident/executionDate/enrollmentDate
+
+ Map<Integer, String> minusDe1SQLMap = new HashMap<>();
+ int idxDe1 = 0;
+ Pattern patternMinus = Pattern.compile( CaseAggregationCondition.minusDataelementRegExp1 );
+ Matcher matcherMinus = patternMinus.matcher( caseExpression );
+ while ( matcherMinus.find() )
+ {
+ String programIdStr = matcherMinus.group( 1 );
+ String programStageIdStr = matcherMinus.group( 2 );
+ String dataElementId = matcherMinus.group( 3 );
+ String dateProperty = matcherMinus.group( 5 );
+ String compareSide = matcherMinus.group( 6 ) + matcherMinus.group( 7 );
+
+ Integer programId = null;
+ Integer programStageId = null;
+ if( !programIdStr.equals(IN_CONDITION_GET_ALL))
+ {
+ programId = Integer.parseInt( programIdStr );
+ }
+
+ if( !programStageIdStr.equals(IN_CONDITION_GET_ALL))
+ {
+ programStageId = Integer.parseInt( programStageIdStr );
+ }
+
+ minusDe1SQLMap.put(
+ idxDe1,
+ getConditionForMinusDataElement1( orgunitIds, programId, programStageId, Integer.parseInt( dataElementId ),
+ dateProperty, compareSide ) );
+
+ caseExpression = caseExpression.replace( matcherMinus.group( 0 ), CaseAggregationCondition.MINUS_DATAELEMENT_OPERATOR_TYPE_ONE
+ + "_" + idxDe1 );
+
+ idxDe1++;
+ }
+
+ // dateOfIncident/executionDate/enrollmentDate - Date dataElement
+
+ Map<Integer, String> minusDe2SQLMap = new HashMap<>();
+ int idxDe2 = 0;
+ Pattern patternDE1Map = Pattern.compile( CaseAggregationCondition.minusDataelementRegExp2 );
+ Matcher matcherMinusDE1 = patternDE1Map.matcher( caseExpression );
+ while ( matcherMinusDE1.find() )
+ {
+ String dateProperty = matcherMinusDE1.group( 1 );
+ String programIdStr = matcherMinusDE1.group( 3 );
+ String programStageIdStr = matcherMinusDE1.group( 4 );
+ String dataElementId = matcherMinusDE1.group( 5 );
+ String compareSide = matcherMinusDE1.group( 6 ) + matcherMinusDE1.group( 7 );
+
+ Integer programId = null;
+ Integer programStageId = null;
+ if ( !programIdStr.equals( IN_CONDITION_GET_ALL ) )
+ {
+ programId = Integer.parseInt(programIdStr );
+ }
+
+ if ( !programStageIdStr.equals( IN_CONDITION_GET_ALL ) )
+ {
+ programStageId = Integer.parseInt( programStageIdStr );
+ }
+
+ minusDe2SQLMap.put(
+ idxDe2,
+ getConditionForMinusDataElement2( orgunitIds, programId, programStageId, Integer.parseInt( dataElementId ),
+ dateProperty, compareSide ) );
+
+ caseExpression = caseExpression.replace( matcherMinusDE1.group( 0 ),
+ CaseAggregationCondition.MINUS_DATAELEMENT_OPERATOR_TYPE_TWO + "_" + idxDe2 );
+
+ idxDe2++;
+ }
+
+ // Date dataElement - Date dataElement
+
+ Map<Integer, String> minus2DeSQLMap = new HashMap<>();
+ int idx2De = 0;
+ Pattern patternMinus2 = Pattern.compile( CaseAggregationCondition.minus2DataelementRegExp );
Matcher matcherMinus2 = patternMinus2.matcher( caseExpression );
+
while ( matcherMinus2.find() )
{
String[] ids1 = matcherMinus2.group( 2 ).split( SEPARATOR_ID );
String[] ids2 = matcherMinus2.group( 5 ).split( SEPARATOR_ID );
- minus2SQLMap.put(
- idx2,
+ minus2DeSQLMap.put(
+ idx2De,
getConditionForMisus2DataElement( orgunitIds, ids1[1], ids1[2], ids2[1], ids2[2],
matcherMinus2.group( 6 ) + matcherMinus2.group( 7 ) ) );
-
caseExpression = caseExpression.replace( matcherMinus2.group( 0 ),
- CaseAggregationCondition.MINUS_DATAELEMENT_OPERATOR + "_" + idx2 );
-
- idx2++;
- }
-
- // Get minus(DATE attribute, DATE attribute) out from the expression
- // and run them later
-
- Map<Integer, String> minus2AttributeSQLMap = new HashMap<>();
- int idx1 = 0;
- Pattern patternAttrMinus2 = Pattern.compile( CaseAggregationCondition.minusAttributeRegExp );
+ CaseAggregationCondition.MINUS_2DATAELEMENT_OPERATOR + "_" + idx2De );
+
+ idx2De++;
+ }
+
+ // currentDate/ dateOfIncident/executionDate/enrollmentDate - Date attribute
+
+ Map<Integer, String> minusAttr1SQLMap = new HashMap<>();
+ int idxAttr1 = 0;
+ Pattern patternMinus3 = Pattern.compile( CaseAggregationCondition.minusAttributeRegExp1 );
+ Matcher matcherMinus3 = patternMinus3.matcher( caseExpression );
+ while ( matcherMinus3.find() )
+ {
+ String property = matcherMinus3.group( 1 );
+ String attributeId = matcherMinus3.group( 3 );
+ String compareSide = matcherMinus3.group( 4 ) + matcherMinus3.group( 5 );
+ minusAttr1SQLMap.put(
+ idxAttr1,
+ getConditionForMisusAttribute1( attributeId, property , compareSide ) );
+
+ caseExpression = caseExpression.replace( matcherMinus3.group( 0 ),
+ CaseAggregationCondition.MINUS_ATTRIBUTE_OPERATOR_TYPE_ONE + "_" + idxAttr1 );
+
+ idxAttr1++;
+ }
+
+
+ // Date attribute - currentDate/ dateOfIncident/executionDate/enrollmentDate
+
+ Map<Integer, String> minusAttr2SQLMap = new HashMap<>();
+ int idxAttr2 = 0;
+ Pattern patternAttr2Minus = Pattern.compile( CaseAggregationCondition.minusAttributeRegExp2 );
+
+ Matcher matcherAttr2Minus = patternAttr2Minus.matcher( caseExpression );
+ while ( matcherAttr2Minus.find() )
+ {
+ minusAttr2SQLMap.put(
+ idxAttr2,
+ getConditionForMisusAttribute2( matcherAttr2Minus.group( 1 ), matcherAttr2Minus.group( 3 ), matcherAttr2Minus.group( 4 ) + matcherAttr2Minus.group( 5 ) ) );
+
+ caseExpression = caseExpression.replace( matcherAttr2Minus.group( 0 ),
+ CaseAggregationCondition.MINUS_ATTRIBUTE_OPERATOR_TYPE_TWO + "_" + idxAttr2 );
+
+ idxAttr2++;
+ }
+
+
+ // Date attribute - Date attribute
+
+ Map<Integer, String> minus2AttrSQLMap = new HashMap<>();
+ int idx2Attr = 0;
+ Pattern patternAttrMinus2 = Pattern.compile( CaseAggregationCondition.minus2AttributeRegExp );
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 ) );
+ minus2AttrSQLMap.put( idx2Attr, 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
-
- Map<Integer, String> minusSQLMap = new HashMap<>();
- int idx = 0;
- Pattern patternMinus = Pattern.compile( CaseAggregationCondition.dataelementRegExp );
- Matcher matcherMinus = patternMinus.matcher( caseExpression );
- while ( matcherMinus.find() )
- {
- 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, programId, programStageId, Integer.parseInt( ids[2] ),
- matcherMinus.group( 4 ) ) );
-
- caseExpression = caseExpression.replace( matcherMinus.group( 0 ), CaseAggregationCondition.MINUS_OPERATOR
- + "_" + idx );
-
- idx++;
- }
-
+ CaseAggregationCondition.MINUS_2ATTRIBUTE_OPERATOR + "_" + idx2Attr );
+
+ idx2Attr++;
+ }
+
+
// Run nornal expression
String[] expression = caseExpression.split( "(AND|OR)" );
caseExpression = caseExpression.replaceAll( "AND", " ) AND " );
@@ -722,24 +803,44 @@
sql = sql.replaceAll( IN_CONDITION_START_SIGN, "(" );
sql = sql.replaceAll( IN_CONDITION_END_SIGN, ")" );
sql = sql.replaceAll( IS_NULL, " " );
-
- for ( int key = 0; key < idx; key++ )
- {
- sql = sql.replace( CaseAggregationCondition.MINUS_OPERATOR + "_" + key, minusSQLMap.get( key ) );
- }
-
- for ( int key = 0; key < idx2; key++ )
- {
- 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 ) );
- }
-
+ for ( int i = 0; i < idxDe1; i++ )
+ {
+ sql = sql.replace( CaseAggregationCondition.MINUS_DATAELEMENT_OPERATOR_TYPE_ONE + "_" + i, minusDe1SQLMap.get( i ) );
+ }
+
+
+ for ( int i = 0; i < idxDe2; i++ )
+ {
+ sql = sql.replace( CaseAggregationCondition.MINUS_DATAELEMENT_OPERATOR_TYPE_TWO + "_" + i,
+ minusDe2SQLMap.get( i ) );
+ }
+
+ for ( int i = 0; i < idx2De; i++ )
+ {
+ sql = sql
+ .replace( CaseAggregationCondition.MINUS_2DATAELEMENT_OPERATOR + "_" + i, minus2DeSQLMap.get( i ) );
+ }
+
+ for ( int i = 0; i < idxAttr1; i++ )
+ {
+ sql = sql
+ .replace( CaseAggregationCondition.MINUS_ATTRIBUTE_OPERATOR_TYPE_ONE + "_" + i, minusAttr1SQLMap.get( i ) );
+ }
+
+ for ( int i = 0; i < idxAttr2; i++ )
+ {
+ sql = sql
+ .replace( CaseAggregationCondition.MINUS_ATTRIBUTE_OPERATOR_TYPE_TWO + "_" + i, minusAttr2SQLMap.get( i ) );
+ }
+
+ for ( int i = 0; i < idx2Attr; i++ )
+ {
+ sql = sql
+ .replace( CaseAggregationCondition.MINUS_2ATTRIBUTE_OPERATOR + "_" + i, minus2AttrSQLMap.get( i ) );
+ }
+
+ sql = sql.replaceAll( CaseAggregationCondition.CURRENT_DATE, "now()");
+
return sql + " ) ";
}
@@ -968,8 +1069,36 @@
return sql;
}
- private String getConditionForMinusDataElement( Collection<Integer> orgunitIds, Integer programId, Integer programStageId,
- Integer dataElementId, String compareSide )
+ private String getConditionForMinusDataElement1( Collection<Integer> orgunitIds, Integer programId, Integer programStageId,
+ Integer dataElementId, String dateProperty, String compareSide )
+ {
+ 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 )
+ + ") ";
+
+
+ if (programId != null)
+ {
+ sql += " AND _pi.programid = " + 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(" + dateProperty + ") " + compareSide + " ) ";
+
+ return sql;
+ }
+
+ private String getConditionForMinusDataElement2( Collection<Integer> orgunitIds, Integer programId, Integer programStageId,
+ Integer dataElementId, String dateProperty, String compareSide )
{
String sql = " EXISTS ( SELECT _pdv.value FROM trackedentitydatavalue _pdv inner join programstageinstance _psi "
+ " ON _pdv.programstageinstanceid=_psi.programstageinstanceid "
@@ -991,7 +1120,7 @@
}
sql += " AND ( _psi.executionDate BETWEEN '" + PARAM_PERIOD_START_DATE + "' AND '" + PARAM_PERIOD_END_DATE
- + "') " + " AND ( DATE(_pdv.value) - DATE(" + compareSide + ") ) ";
+ + "') " + " AND ( DATE(" + dateProperty + ") - DATE(_pdv.value) " + compareSide + " ) ";
return sql;
}
@@ -1002,7 +1131,7 @@
return " EXISTS ( SELECT * FROM ( 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= "
+ + " WHERE _pi.programinstanceid=pi.programinstanceid AND _pdv.dataelementid= "
+ dataElementId1
+ " AND _psi.organisationunitid in ("
+ TextUtils.getCommaDelimitedString( orgunitIds )
@@ -1018,7 +1147,7 @@
+ " ( 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= "
+ + " WHERE _pi.programinstanceid=pi.programinstanceid and _pdv.dataelementid= "
+ dataElementId2
+ " AND _psi.organisationunitid in ("
+ TextUtils.getCommaDelimitedString( orgunitIds )
@@ -1043,6 +1172,48 @@
+ " and _teav.trackedentityattributeid = " + attribute2 + " ) as a2 "
+ " WHERE DATE(a1.value ) - DATE(a2.value) " + compareSide;
}
+
+ private String getConditionForMisusAttribute1( String attribute, String dateProperty, String compareSide )
+ {
+ if( dateProperty.equals( CaseAggregationCondition.OBJECT_PROGRAM_PROPERTY_INCIDENT_DATE )
+ || dateProperty.equals( CaseAggregationCondition.OBJECT_PROGRAM_PROPERTY_ENROLLEMENT_DATE ) )
+ {
+ return " EXISTS ( select * from trackedentityattributevalue _teav "
+ + "inner join programinstance _pi on _teav.trackedentityinstanceid=_pi.trackedentityinstanceid "
+ + "where _teav.trackedentityattributeid=" + attribute + " and date(" + dateProperty + ") - date(_teav.value) " + compareSide ;
+ }
+ else if( dateProperty.equals( CaseAggregationCondition.OBJECT_PROGRAM_PROPERTY_REPORT_DATE ) )
+ {
+ return " EXISTS ( select * from trackedentityattributevalue _teav "
+ + "inner join programinstance _pi on _teav.trackedentityinstanceid=_pi.trackedentityinstanceid "
+ + "inner join programstageinstance _psi on _psi.programinstanceid=_pi.programinstanceid "
+ + "where _teav.trackedentityattributeid=" + attribute + " and date(" + dateProperty + ") - date(_teav.value) " + compareSide ;
+ }
+
+ return " EXISTS (select * from trackedentityattributevalue _teav where _teav.trackedentityattributeid="
+ + attribute + " and date(now()) - date(_teav.value) " + compareSide;
+ }
+
+ private String getConditionForMisusAttribute2( String attribute, String dateProperty, String compareSide )
+ {
+ if( dateProperty.equals( CaseAggregationCondition.OBJECT_PROGRAM_PROPERTY_INCIDENT_DATE )
+ || dateProperty.equals( CaseAggregationCondition.OBJECT_PROGRAM_PROPERTY_ENROLLEMENT_DATE ) )
+ {
+ return " EXISTS ( select * from trackedentityattributevalue _teav "
+ + "inner join programinstance _pi on _teav.trackedentityinstanceid=_pi.trackedentityinstanceid "
+ + "where _teav.trackedentityattributeid=" + attribute + " and date(_teav.value) - date(" + dateProperty + ") " + compareSide ;
+ }
+ else if( dateProperty.equals( CaseAggregationCondition.OBJECT_PROGRAM_PROPERTY_REPORT_DATE ) )
+ {
+ return " EXISTS ( select * from trackedentityattributevalue _teav "
+ + "inner join programinstance _pi on _teav.trackedentityinstanceid=_pi.trackedentityinstanceid "
+ + "inner join programstageinstance _psi on _psi.programinstanceid=_pi.programinstanceid "
+ + "where _teav.trackedentityattributeid=" + attribute + " and date(_teav.value) - date(" + dateProperty + ") " + compareSide ;
+ }
+
+ return " EXISTS (select * from trackedentityattributevalue _teav where _teav.trackedentityattributeid="
+ + attribute + " and date(_teav.value) - date(now()) " + compareSide;
+ }
/**
* Return the Ids of organisation units which entity instances registered or
=== modified file 'dhis-2/dhis-services/dhis-service-tracker/src/main/java/org/hisp/dhis/trackedentity/startup/TableAlteror.java'
--- dhis-2/dhis-services/dhis-service-tracker/src/main/java/org/hisp/dhis/trackedentity/startup/TableAlteror.java 2014-09-29 19:08:34 +0000
+++ dhis-2/dhis-services/dhis-service-tracker/src/main/java/org/hisp/dhis/trackedentity/startup/TableAlteror.java 2014-10-04 15:48:07 +0000
@@ -294,6 +294,8 @@
updateProgramStageList();
updateProgramAttributeList();
+ updateFixedAttributeInCaseAggregate( "DEDATEDIFF", CaseAggregationCondition.MINUS_OPERATOR );
+
}
// -------------------------------------------------------------------------
@@ -359,7 +361,7 @@
holder.close();
}
}
-
+
private void updateProgramInstanceStatus()
{
// Set active status for events
@@ -506,7 +508,7 @@
holder.close();
}
}
-
+
private int executeSql( String sql )
{
try
=== modified file 'dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-program/src/main/resources/org/hisp/dhis/trackedentity/i18n_module.properties'
--- dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-program/src/main/resources/org/hisp/dhis/trackedentity/i18n_module.properties 2014-09-08 10:02:47 +0000
+++ dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-program/src/main/resources/org/hisp/dhis/trackedentity/i18n_module.properties 2014-10-04 15:48:07 +0000
@@ -381,9 +381,6 @@
allow_enrollment_at_all_orgunits = Allow enrollment at all orgunits
other_program_stages = Other program stages
minus = Minus
-minus_with_dateOfIncident = Incident Date Diff
-minus_with_enrollmentDate = Enrollment Date Diff
-minus_with_executionDate = Report Date Diff
and_operator = AND
or_operator = OR
combine_operator = COMBINE
@@ -412,7 +409,6 @@
selected_dynamic_attributes = Selected dynamic attributes
registrationDate = Registration date
create_default_registration_form = Create default registration form
-minus_with_dataelement = Data element date diff
use_birth_date_as_incident_date = Set incidence date to date of birth
use_birth_date_as_enrollment_date = Set enrollment date to date of birth
default_form_name = Default form name
@@ -506,4 +502,4 @@
filter_by_program = Filter by program
enter_an_attribute = Enter an attribute
enter_a_key = Enter a key
-minus_with_attribute = Attribute date diff
\ No newline at end of file
+date_diff = Date diff
\ No newline at end of file
=== modified file 'dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-program/src/main/resources/struts.xml'
--- dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-program/src/main/resources/struts.xml 2014-08-11 12:53:29 +0000
+++ dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-program/src/main/resources/struts.xml 2014-10-04 15:48:07 +0000
@@ -1209,7 +1209,7 @@
<result name="success" type="velocity">/main.vm</result>
<param name="page">/dhis-web-maintenance-program/updateTrackedEntity.vm</param>
<param name="javascripts">javascript/trackedEntity.js</param>
- <param name="requiredAuthorities">F_TRACKED_ENTITY_ADD</param>
+ <param name="requiredAuthorities">F_TRACKED_ENTITY_UPDATE</param>
</action>
<action name="updateTrackedEntity"
=== modified file 'dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-program/src/main/webapp/dhis-web-maintenance-program/caseAggregationForm.vm'
--- dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-program/src/main/webapp/dhis-web-maintenance-program/caseAggregationForm.vm 2014-08-29 10:14:40 +0000
+++ dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-program/src/main/webapp/dhis-web-maintenance-program/caseAggregationForm.vm 2014-10-04 15:48:07 +0000
@@ -134,18 +134,19 @@
<tr>
<td>
<select id="programProperty" name="programProperty" size="10" ondblclick="insertInfo(this, false);" disabled>
+ <option value="enrollmentDate">$i18n.getString( "date_of_enrollment" )</option>
+ <option value="dateOfIncident">$i18n.getString( "date_of_incident" )</option>
<option value="[PG:*]">$i18n.getString( "program_enrollment" )</option>
- <option value="[PG:*] AND [PP:DATE@enrollmentdate#-DATE@dateofincident#]">$i18n.getString( "date_of_enrollment" ) - $i18n.getString( "date_of_incident" )</option>
</select>
</td>
<td>
<select multiple id="programStageProperty" size="10" name="programStageProperty" ondblclick="insertInfo(this, true);" disabled >
+ <option value="executionDate">$i18n.getString( "report_date" )</option>
<option value="[PS:*]">$i18n.getString( "visit_selected_program_stage" )</option>
<option value="[PSIC:*]">$i18n.getString( "completed" )</option>
<option value="[PS:*.COUNT]" name="multiProgram">$i18n.getString( "visit_selected_program_stage_x_th_time" )</option>
<option value="[PS:*] AND [PSP:DATE@executionDate#-DATE@dueDate#]" name="multiProgram">$i18n.getString( "report_date" ) - $i18n.getString( "due_date" )</option>
</select>
- </select>
</td>
</tr>
</table>
@@ -153,7 +154,7 @@
</div>
<p></p>
-<table>
+<table width='100%'>
<tr>
<td>
<input type='button' class="small-button" onclick='insertOperator( "(" );' value="(" />
@@ -169,20 +170,15 @@
<input type='button' class="nornal-button" onclick='insertBoolValue( "false" );' value="$i18n.getString( 'no' )" />
<input type='button' class="nornal-button" onclick='insertOperator( "is null" );' value="==NULL" />
<input type='button' class="nornal-button" onclick='insertOperator( "is not null" );' value="!=NULL" />
- </span>
- <span style='padding-left:10px'>
- <input type='button' style="width:54px;" onclick='insertOperator( "AND" );' value="$i18n.getString( 'and_operator' )" />
- <input type='button' style="width:54px;" onclick='insertOperator( "OR" );' value="$i18n.getString( 'or_operator' )" />
- <input type='button' style="width:80px;" onclick='insertOperator( "COMBINE" );' value="$i18n.getString( 'combine_operator' )" />
+ <input type='button' style="width:100px;" onclick='insertOperator( "current_date" );' value="$i18n.getString('current_date')" />
</span>
<br><br>
- <input type='button' style="width:175px;" onclick='insertOperator( "DATEDIFF( , dateOfIncident) " );' value="$i18n.getString('minus_with_dateOfIncident')" />
- <input type='button' style="width:175px;" onclick='insertOperator( "DATEDIFF( , enrollmentDate) " );' value="$i18n.getString('minus_with_enrollmentDate')" />
- <input type='button' style="width:175px;" onclick='insertOperator( "DATEDIFF( , executionDate) " );' value="$i18n.getString('minus_with_executionDate')" />
- <input type='button' style="width:175px;" onclick='insertOperator( "DEDATEDIFF( , ) " );' value="$i18n.getString('minus_with_dataelement')" />
- <input type='button' style="width:175px;" onclick='insertOperator( "ATTRDATEDIFF( , ) " );' value="$i18n.getString('minus_with_attribute')" />
- <span style='padding-left:110px'>
- <input type='button' style="width:45px;" align="right" alt="$i18n.getString( 'clear' )" onclick="byId('aggregationCondition').value='';" value="$i18n.getString('clear')" />
+ <span>
+ <input type='button' class="nornal-button" onclick='insertOperator( "AND" );' value="$i18n.getString( 'and_operator' )" />
+ <input type='button' class="nornal-button" onclick='insertOperator( "OR" );' value="$i18n.getString( 'or_operator' )" />
+ <input type='button' style="width:100px;" onclick='insertOperator( "DATEDIFF( , ) " );' value="$i18n.getString('date_diff')" />
+ <input type='button' style="width:100px;" onclick='insertOperator( "COMBINE" );' value="$i18n.getString( 'combine_operator' )" />
+ |<input type='button' class="nornal-button" alt="$i18n.getString( 'clear' )" onclick="byId('aggregationCondition').value='';" value="$i18n.getString('clear')" />
</span>
</td>
</tr>
