dhis2-devs team mailing list archive

[noreply@xxxxxxxxxxxxx]

------------------------------------------------------------
revno: 12771
committer: Tran Chau <tran.hispvietnam@xxxxxxxxx>
branch nick: dhis2
timestamp: Fri 2013-10-18 14:38:06 +0700
message:
  Fix bugs in Aggregate query builder.
modified:
  dhis-2/dhis-services/dhis-service-eventreporting/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-eventreporting/src/main/java/org/hisp/dhis/caseaggregation/jdbc/JdbcCaseAggregationConditionManager.java'
--- dhis-2/dhis-services/dhis-service-eventreporting/src/main/java/org/hisp/dhis/caseaggregation/jdbc/JdbcCaseAggregationConditionManager.java	2013-10-16 04:53:40 +0000
+++ dhis-2/dhis-services/dhis-service-eventreporting/src/main/java/org/hisp/dhis/caseaggregation/jdbc/JdbcCaseAggregationConditionManager.java	2013-10-18 07:38:06 +0000
@@ -343,7 +343,7 @@
                 sql += "FROM ";
                 boolean hasDataelement = hasDataelementCriteria( caseExpression );
 
-                if( hasDataelement)
+                if ( hasDataelement )
                 {
                     sql += " programinstance as pi ";
                     sql += " INNER JOIN patient p on p.patientid=pi.patientid ";
@@ -355,8 +355,7 @@
                     sql += " programinstance as pi INNER JOIN patient p on p.patientid=pi.patientid ";
                     sql += " INNER JOIN organisationunit ou ON ou.organisationunitid=p.organisationunitid ";
                 }
-               
-                
+
                 sql += " WHERE "
                     + createSQL( caseExpression, operator, orgunitIds,
                         DateUtils.getMediumDateString( period.getStartDate() ),
@@ -428,7 +427,7 @@
         sql = sql.substring( 0, sql.length() - 1 );
         sql += " FROM ";
 
-        if( hasDataelement)
+        if ( hasDataelement )
         {
             sql += " programinstance as pi INNER JOIN patient p on p.patientid=pi.patientid";
             sql += " INNER JOIN programstageinstance psi ON pi.programinstanceid=psi.programinstanceid ";
@@ -448,7 +447,7 @@
                 DateUtils.getMediumDateString( period.getEndDate() ) );
 
         sql = sql.replaceAll( "COMBINE", "" );
-        
+
         return sql;
     }
 
@@ -690,7 +689,11 @@
             else if ( info[0].equalsIgnoreCase( OBJECT_PATIENT_ATTRIBUTE ) )
             {
                 int attributeId = Integer.parseInt( info[1] );
-                condition = getConditionForPatientAttribute( attributeId, orgunitIds );
+
+                String compareValue = expression[index].replace( "[" + match + "]", "" ).trim();
+
+                boolean isExist = compareValue.equals( IS_NULL ) ? false : true;
+                condition = getConditionForPatientAttribute( attributeId, orgunitIds, isExist );
             }
             else if ( info[0].equalsIgnoreCase( OBJECT_PROGRAM_STAGE_DATAELEMENT ) )
             {
@@ -824,10 +827,19 @@
      * Return standard SQL of a dynamic patient-attribute expression. E.g [CA:1]
      * 
      */
-    private String getConditionForPatientAttribute( int attributeId, Collection<Integer> orgunitIds )
+    private String getConditionForPatientAttribute( int attributeId, Collection<Integer> orgunitIds, boolean isExist )
     {
         String sql = " EXISTS ( SELECT * FROM patientattributevalue _pav "
-            + " WHERE _pav.patientid=pi.patientid AND _pav.patientattributeid=" + attributeId + "  AND _pav.value ";
+            + " WHERE _pav.patientid=pi.patientid AND _pav.patientattributeid=" + attributeId;
+
+        if ( isExist )
+        {
+            sql += "  AND _pav.value ";
+        }
+        else
+        {
+            sql = " NOT " + sql;
+        }
 
         return sql;
     }
@@ -880,7 +892,7 @@
         String sql = " EXISTS ( SELECT _psi.programstageinstanceid from programstageinstance _psi "
             + "WHERE _psi.programstageinstanceid=psi.programstageinstanceid AND ( _psi.executionDate BETWEEN '"
             + startDate + "' AND '" + endDate + "') AND " + propertyName;
-        
+
         return sql;
     }
 
@@ -933,15 +945,9 @@
         String startDate, String endDate )
     {
         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 ) + ")  ";
+            + "WHERE _psi.programstageinstanceid=psi.programstageinstanceid " + "AND _psi.programstageid="
+            + programStageId + "  AND _psi.executiondate >= '" + startDate + "' AND _psi.executiondate <= '" + endDate
+            + "' AND _psi.organisationunitid in (" + TextUtils.getCommaDelimitedString( orgunitIds ) + ")  ";
 
         return sql;
     }