dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #23423
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 11393: Fix bug - Don't run aggregate query builder formulas.
------------------------------------------------------------
revno: 11393
committer: Tran Chau <tran.hispvietnam@xxxxxxxxx>
branch nick: dhis2
timestamp: Mon 2013-07-15 13:18:13 +0700
message:
Fix bug - Don't run aggregate query builder formulas.
modified:
dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/caseaggregation/jdbc/JdbcCaseAggregationConditionManager.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-services/dhis-service-patient/src/main/java/org/hisp/dhis/caseaggregation/jdbc/JdbcCaseAggregationConditionManager.java'
--- dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/caseaggregation/jdbc/JdbcCaseAggregationConditionManager.java 2013-07-10 05:10:43 +0000
+++ dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/caseaggregation/jdbc/JdbcCaseAggregationConditionManager.java 2013-07-15 06:18:13 +0000
@@ -775,9 +775,8 @@
sql = sql
.replace( CaseAggregationCondition.MINUS_DATAELEMENT_OPERATOR + "_" + key, minus2SQLMap.get( key ) );
}
-
+
return sql + " ) ";
-
}
/**
@@ -789,14 +788,14 @@
{
String keyExist = (isExist == true) ? "EXISTS" : "NOT EXISTS";
- String sql = " " + keyExist + " ( SELECT _psi.programstageinstanceid "
+ String sql = " " + keyExist + " ( SELECT * "
+ "FROM patientdatavalue _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 _pi.programid = " + programId + " AND ( _psi.executionDate BETWEEN '" + startDate + "' AND '"
- + endDate + "') ";
+ + " AND _pi.programid = " + programId + " AND _psi.executionDate>='" + startDate
+ + "' AND _psi.executionDate <= '" + endDate + "' ";
if ( !programStageId.equals( IN_CONDITION_GET_ALL ) )
{
@@ -840,8 +839,8 @@
private String getConditionForPatient( Collection<Integer> orgunitIds, String operator, String startDate,
String endDate )
{
- String sql = " EXISTS ( SELECT _p.patientid FROM patient _p " + "WHERE _p.patientid = pi.patientid "
- + "AND ( _p.registrationdate BETWEEN '" + startDate + "' AND '" + endDate + "') "
+ String sql = " EXISTS ( SELECT * " + "FROM patient _p " + "WHERE _p.patientid = pi.patientid "
+ + "AND _p.registrationdate>='" + startDate + "' AND _p.registrationdate<='" + endDate + "' "
+ "AND p.organisationunitid in (" + TextUtils.getCommaDelimitedString( orgunitIds ) + ") ";
return sql;
@@ -893,7 +892,13 @@
private String getConditionForProgramProperty( String operator, String startDate, String endDate, String property )
{
String sql = " EXISTS ( SELECT _pi.programinstanceid FROM programinstance as _pi WHERE psi.programinstanceid=_pi.programsinstanceid AND "
- + "( psi.executionDate BETWEEN '" + startDate + "' AND '" + endDate + "') AND " + property + " ";
+ + "psi.executionDate >= '"
+ + startDate
+ + "' AND psi.executionDate <= '"
+ + endDate
+ + "' AND "
+ + property
+ + " ";
return sql;
}
@@ -910,7 +915,7 @@
+ "INNER JOIN programstageinstance _psi ON _pi.programinstanceid=_psi.programinstanceid "
+ "WHERE psi.programstageinstanceid=_psi.programstageinstanceid AND _pi.programid=" + programId + " "
+ " AND _psi.organisationunitid in (" + TextUtils.getCommaDelimitedString( orgunitIds )
- + ") AND (_psi.executionDate BETWEEN '" + startDate + "' AND '" + endDate + "') ";
+ + ") AND _pi.enrollmentdate >= '" + startDate + "' AND _pi.enrollmentdate <= '" + endDate + "' ";
return sql;
}
@@ -925,8 +930,13 @@
{
String sql = " EXISTS ( SELECT _psi.programstageinstanceid FROM programinstance as _pi INNER JOIN programstageinstance _psi "
+ "ON _pi.programinstanceid = _psi.programinstanceid WHERE _psi.programstageinstanceid=psi.programstageinstanceid "
- + "AND _psi.programstageid=" + programStageId + " AND (_psi.executionDate BETWEEN '" + startDate
- + "' AND '" + endDate + "') AND _psi.organisationunitid in ("
+ + "AND _psi.programstageid="
+ + programStageId
+ + " AND _psi.executiondate >= '"
+ + startDate
+ + "' AND _psi.executiondate <= '"
+ + endDate
+ + "' AND _psi.organisationunitid in ("
+ TextUtils.getCommaDelimitedString( orgunitIds ) + ") ";
return sql;
@@ -944,7 +954,7 @@
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 BETWEEN '" + startDate + "' AND '" + endDate + "') "
+ + "AND _psi.executionDate >= '" + startDate + "' AND _psi.executionDate <= '" + endDate + "' "
+ "GROUP BY _psi.programinstanceid,_psi.programstageinstanceid "
+ "HAVING count(_psi.programstageinstanceid) ";
@@ -960,9 +970,9 @@
private String getConditionForProgramStageProperty( String property, String operator,
Collection<Integer> orgunitIds, String startDate, String endDate )
{
- String sql = " EXISTS ( SELECT _psi.programstageinstanceid FROM programstageinstance _psi "
- + "WHERE psi.programstageinstanceid=_psi.programstageinstanceid AND (_psi.executionDate BETWEEN '"
- + startDate + "' AND '" + endDate + "') AND _psi.organisationunitid in ("
+ 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 + " ";
return sql;
@@ -987,7 +997,8 @@
sql += " AND EXISTS ( SELECT programstageinstanceid FROM programstageinstance _psi "
+ " WHERE _psi.organisationunitid=ou.organisationunitid AND _psi.programstageid = " + programStageId
- + " AND _psi.completed=true AND ( _psi.executionDate BETWEEN '" + startDate + "' AND '" + endDate + "') ) ";
+ + " AND _psi.completed=true AND _psi.executiondate >= '" + startDate + "' AND _psi.executiondate <= '"
+ + endDate + "' ) ";
return sql;
}
@@ -995,7 +1006,7 @@
private String getConditionForMinusDataElement( Collection<Integer> orgunitIds, Integer programStageId,
Integer dataElementId, String compareSide, String startDate, String endDate )
{
- return " EXISTS ( SELECT _psi.programstageinstanceid FROM patientdatavalue _pdv inner join programstageinstance _psi "
+ return " EXISTS ( SELECT_pdv.value FROM patientdatavalue _pdv inner join programstageinstance _psi "
+ " ON _pdv.programstageinstanceid=_psi.programstageinstanceid "
+ " JOIN programinstance _pi ON _pi.programinstanceid=_psi.programinstanceid "
+ " WHERE psi.programstageinstanceid=_pdv.programstageinstanceid "
@@ -1010,7 +1021,7 @@
String dataElementId1, String programStageId2, String dataElementId2, String compareSide, String startDate,
String endDate )
{
- return " EXISTS ( SELECT * FROM ( SELECT _psi.programstageinstanceid FROM patientdatavalue _pdv "
+ return " EXISTS ( SELECT * FROM ( SELECT _pdv.value FROM patientdatavalue _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= "
@@ -1018,10 +1029,15 @@
+ " AND _psi.organisationunitid in ("
+ TextUtils.getCommaDelimitedString( orgunitIds )
+ ") "
- + " AND _psi.programstageid = " + programStageId1
- + " AND (_psi.executionDate BETWEEN '" + startDate + "' AND '" + endDate
- + "') ) AS d1 cross join "
- + " ( SELECT _psi.programstageinstanceid FROM patientdatavalue _pdv INNER JOIN programstageinstance _psi "
+ + " AND _psi.programstageid = "
+ + programStageId1
+ + " AND _psi.executionDate>='"
+ + startDate
+ + "' "
+ + " AND _psi.executionDate <= '"
+ + endDate
+ + "' ) AS d1 cross join "
+ + " ( SELECT _pdv.value FROM patientdatavalue _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= "
@@ -1029,9 +1045,14 @@
+ " AND _psi.organisationunitid in ("
+ TextUtils.getCommaDelimitedString( orgunitIds )
+ ") "
- + " AND _psi.programstageid = " + programStageId2
- + " AND( _psi.executionDate BETWEEN '" + startDate + "' AND '" + endDate
- + "') ) AS d2 WHERE DATE(d1.value ) - DATE(d2.value) " + compareSide;
+ + " AND _psi.programstageid = "
+ + programStageId2
+ + " AND _psi.executionDate>='"
+ + startDate
+ + "' "
+ + " AND _psi.executionDate <= '"
+ + endDate
+ + "' ) AS d2 WHERE DATE(d1.value ) - DATE(d2.value) " + compareSide;
}
/**
