← Back to team overview

dhis2-devs team mailing list archive

[Branch ~dhis2-devs-core/dhis2/trunk] Rev 10728: Improve Aggregate Query Builder (WIP).

 

------------------------------------------------------------
revno: 10728
committer: Tran Chau <tran.hispvietnam@xxxxxxxxx>
branch nick: dhis2
timestamp: Thu 2013-05-02 10:31:45 +0700
message:
  Improve Aggregate Query Builder (WIP).
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-services/dhis-service-patient/src/main/java/org/hisp/dhis/caseaggregation/CaseAggregationConditionManager.java
  dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/caseaggregation/DefaultCaseAggregationConditionService.java
  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/resources/META-INF/dhis/beans.xml
  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/CaseAggregationResultDetailsAction.java
  dhis-2/dhis-web/dhis-web-caseentry/src/main/java/org/hisp/dhis/caseentry/action/caseaggregation/SaveAggregateDataValueAction.java
  dhis-2/dhis-web/dhis-web-caseentry/src/main/resources/META-INF/dhis/beans.xml
  dhis-2/dhis-web/dhis-web-caseentry/src/main/resources/org/hisp/dhis/caseentry/i18n_module.properties
  dhis-2/dhis-web/dhis-web-caseentry/src/main/webapp/dhis-web-caseentry/caseAggregationResult.vm
  dhis-2/dhis-web/dhis-web-caseentry/src/main/webapp/dhis-web-caseentry/javascript/caseagg.js
  dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-patient/src/main/webapp/dhis-web-maintenance-patient/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	2013-04-28 08:54:29 +0000
+++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/caseaggregation/CaseAggregationCondition.java	2013-05-02 03:31:45 +0000
@@ -46,17 +46,17 @@
     public static final String SEPARATOR_OBJECT = ":";
 
     public static final String AGGRERATION_COUNT = "COUNT";
-    
+
     public static final String AGGRERATION_SUM = "times";
-    
+
     public static final String AGGRERATION_SUM_VALUE = "sum";
-    
+
     public static final String AGGRERATION_AVG_VALUE = "avg";
-    
+
     public static final String AGGRERATION_AVG_MIN = "min";
-    
+
     public static final String AGGRERATION_AVG_MAX = "max";
-    
+
     public static final String OPERATOR_AND = "AND";
 
     public static final String OPERATOR_OR = "OR";
@@ -74,21 +74,24 @@
     public static String OBJECT_PATIENT = "PT";
 
     public static String OBJECT_PROGRAM_STAGE = "PS";
-    
+
     public static String OBJECT_PROGRAM_STAGE_PROPERTY = "PSP";
-    
+
     public static String OBJECT_PATIENT_PROGRAM_STAGE_PROPERTY = "PC";
-    
+
     public static String OBJECT_ORGUNIT_COMPLETE_PROGRAM_STAGE = "PSIC";
-    
+
     public static String OBJECT_PROGRAM_PROPERTY_INCIDENT_DATE = "dateOfIncident";
+
     public static String OBJECT_PROGRAM_PROPERTY_ENROLLEMENT_DATE = "enrollmentDate";
 
-    public static final String regExp = "\\[(" + OBJECT_ORGUNIT_COMPLETE_PROGRAM_STAGE + "|" + OBJECT_PATIENT + "|" + OBJECT_PROGRAM + "|" + OBJECT_PROGRAM_STAGE + "|"
-    + OBJECT_PROGRAM_STAGE_PROPERTY + "|" + OBJECT_PATIENT_PROGRAM_STAGE_PROPERTY + "|"
-    + OBJECT_PROGRAM_STAGE_DATAELEMENT + "|" + OBJECT_PATIENT_ATTRIBUTE + "|" + OBJECT_PATIENT_PROPERTY + "|"
-    + OBJECT_PROGRAM_PROPERTY + ")" + SEPARATOR_OBJECT + "([a-zA-Z0-9@#\\- ]+[" + SEPARATOR_ID + "[a-zA-Z0-9]*]*)"
-    + "\\]";
+    public static String AUTO_STORED_BY = "DHIS-SYSTEM";
+
+    public static final String regExp = "\\[(" + OBJECT_ORGUNIT_COMPLETE_PROGRAM_STAGE + "|" + OBJECT_PATIENT + "|"
+        + OBJECT_PROGRAM + "|" + OBJECT_PROGRAM_STAGE_PROPERTY + "|" + OBJECT_PROGRAM_STAGE + "|"
+        + OBJECT_PATIENT_PROGRAM_STAGE_PROPERTY + "|" + OBJECT_PROGRAM_STAGE_DATAELEMENT + "|"
+        + OBJECT_PATIENT_ATTRIBUTE + "|" + OBJECT_PATIENT_PROPERTY + "|" + OBJECT_PROGRAM_PROPERTY + ")"
+        + SEPARATOR_OBJECT + "([a-zA-Z0-9@#\\- ]+[" + SEPARATOR_ID + "[a-zA-Z0-9]*]*)" + "\\]";
 
     // -------------------------------------------------------------------------
     // Fields
@@ -101,7 +104,7 @@
     private DataElement aggregationDataElement;
 
     private DataElementCategoryOptionCombo optionCombo;
-    
+
     private DataElement deSum;
 
     // -------------------------------------------------------------------------
@@ -123,9 +126,8 @@
         this.optionCombo = optionCombo;
     }
 
-    
-    public CaseAggregationCondition( String name, String operator, String aggregationExpression, DataElement aggregationDataElement,
-        DataElementCategoryOptionCombo optionCombo, DataElement deSum )
+    public CaseAggregationCondition( String name, String operator, String aggregationExpression,
+        DataElement aggregationDataElement, DataElementCategoryOptionCombo optionCombo, DataElement deSum )
     {
         this.name = name;
         this.operator = operator;

=== 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	2013-04-28 08:54:29 +0000
+++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/caseaggregation/CaseAggregationConditionService.java	2013-05-02 03:31:45 +0000
@@ -30,9 +30,11 @@
 import java.util.Collection;
 import java.util.List;
 
+import org.hisp.dhis.common.Grid;
 import org.hisp.dhis.dataelement.DataElement;
 import org.hisp.dhis.dataelement.DataElementCategoryOptionCombo;
-import org.hisp.dhis.organisationunit.OrganisationUnit;
+import org.hisp.dhis.i18n.I18n;
+import org.hisp.dhis.i18n.I18nFormat;
 import org.hisp.dhis.patient.Patient;
 import org.hisp.dhis.patient.PatientAttribute;
 import org.hisp.dhis.patientdatavalue.PatientDataValue;
@@ -52,34 +54,42 @@
     void updateCaseAggregationCondition( CaseAggregationCondition caseAggregationCondition );
 
     void deleteCaseAggregationCondition( CaseAggregationCondition caseAggregationCondition );
-    
+
     CaseAggregationCondition getCaseAggregationCondition( int id );
-    
-    Collection<CaseAggregationCondition> getAllCaseAggregationCondition( );
-   
+
+    Collection<CaseAggregationCondition> getAllCaseAggregationCondition();
+
     Collection<CaseAggregationCondition> getCaseAggregationCondition( DataElement dataElement );
-    
-    CaseAggregationCondition getCaseAggregationCondition( DataElement dataElement, DataElementCategoryOptionCombo optionCombo );
-    
+
+    CaseAggregationCondition getCaseAggregationCondition( DataElement dataElement,
+        DataElementCategoryOptionCombo optionCombo );
+
     Collection<CaseAggregationCondition> getCaseAggregationCondition( Collection<DataElement> dataElements );
-    
-    Double getAggregateValue( CaseAggregationCondition aggregationCondition, OrganisationUnit orgunit, Period period );
-        
-    Collection<PatientDataValue> getPatientDataValues( CaseAggregationCondition aggregationCondition, OrganisationUnit orgunit, Period period );
-    
-    Collection<Patient> getPatients( CaseAggregationCondition aggregationCondition, OrganisationUnit orgunit, Period period );
-    
-    Collection<ProgramStageInstance> getProgramStageInstances( CaseAggregationCondition aggregationCondition, OrganisationUnit orgunit, Period period );
-    
+
+    Grid getAggregateValue( CaseAggregationCondition aggregationCondition, Collection<Integer> orgunitIds,
+        Period period, I18nFormat format, I18n i18n );
+
+    void insertAggregateValue( CaseAggregationCondition caseAggregationCondition, Collection<Integer> orgunitIds,
+        Period period );
+
+    Collection<PatientDataValue> getPatientDataValues( CaseAggregationCondition aggregationCondition,
+        Collection<Integer> orgunitIds, Period period );
+
+    Collection<Patient> getPatients( CaseAggregationCondition aggregationCondition, Collection<Integer> orgunitIds,
+        Period period );
+
+    Collection<ProgramStageInstance> getProgramStageInstances( CaseAggregationCondition aggregationCondition,
+        Collection<Integer> orgunitIds, Period period );
+
     Collection<DataElement> getDataElementsInCondition( String aggregationExpression );
-        
+
     Collection<Program> getProgramsInCondition( String aggregationExpression );
-    
+
     Collection<PatientAttribute> getPatientAttributesInCondition( String aggregationExpression );
-    
+
     String getConditionDescription( String condition );
-    
+
     void aggregate( List<CaseAggregateSchedule> caseAggregateSchedules, String taskStrategy );
-    
+
     boolean hasOrgunitProgramStageCompleted( String expresstion );
 }

=== modified file 'dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/caseaggregation/CaseAggregationConditionManager.java'
--- dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/caseaggregation/CaseAggregationConditionManager.java	2013-04-28 08:54:29 +0000
+++ dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/caseaggregation/CaseAggregationConditionManager.java	2013-05-02 03:31:45 +0000
@@ -27,10 +27,14 @@
 
 package org.hisp.dhis.caseaggregation;
 
+import java.util.Collection;
 import java.util.List;
 import java.util.concurrent.ConcurrentLinkedQueue;
 import java.util.concurrent.Future;
 
+import org.hisp.dhis.common.Grid;
+import org.hisp.dhis.i18n.I18n;
+import org.hisp.dhis.i18n.I18nFormat;
 import org.hisp.dhis.period.Period;
 
 /**
@@ -45,36 +49,48 @@
      * datasets which have data elements defined in the formulas
      * 
      * @param caseAggregateSchedule
-     * @param taskStrategy Specify how to get period list based on period type of each
-     *        dataset. There are four options, include last month, last 3 month,
-     *        last 6 month and last 12 month
+     * @param taskStrategy Specify how to get period list based on period type
+     *        of each dataset. There are four options, include last month, last
+     *        3 month, last 6 month and last 12 month
      */
     Future<?> aggregate( ConcurrentLinkedQueue<CaseAggregateSchedule> caseAggregateSchedule, String taskStrategy );
 
     /**
-     * Return a data value aggregated of query builder formula
-     * 
-     * @param caseExpression The query builder expression
-     * @param operator There are six operators, includes Number of persons,
-     *        Number of visits, Sum, Average, Minimum and Maximum of data
-     *        element values.
-     * @param deSumId The id of aggregate data element which used for aggregate
-     *        data values for operator Sum, Average, Minimum and Maximum of data
-     *        element values. This fill is null for other operators.
-     * @param orgunitId The id of organisation unit where to aggregate data
-     *        value
-     * @param period The date range for aggregate data value
-     */
-    Double getAggregateValue( String caseExpression, String operator, Integer deSumId,
-        Integer orgunitId, Period period );
-
-    /**
-     * Return standard SQL from query builder formula 
-     * 
-     * @param caseExpression The query builder expression
-     * @param operator There are six operators, includes Number of persons,
-     *        Number of visits, Sum, Average, Minimum and Maximum of data
-     *        element values.
+     * 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 data values into database directly
+     * 
+     * @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
+     */
+    void insertAggregateValue( CaseAggregationCondition caseAggregationCondition, Collection<Integer> orgunitIds,
+        Period period );
+
+    /**
+     * Return standard SQL from query builder formula
+     * 
+     * @param isInsert Insert aggregate result into database directly
+     * @param caseExpression The query builder expression
+     * @param operator There are six operators, includes Number of persons,
+     *        Number of visits, Sum, Average, Minimum and Maximum of data
+     *        element values.
+     * @param aggregateDeId The id of aggregate data element
+     * @param aggregateDeName The name of aggregate data element
+     * @param optionComboId The id of category option combo
+     * @param optionComboName The name of category option combo
      * @param deSumId The id of aggregate data element which used for aggregate
      *        data values for operator Sum, Average, Minimum and Maximum of data
      *        element values. This fill is null for other operators.
@@ -83,8 +99,9 @@
      * @param startDate Start date
      * @param endDate End date
      */
-    String parseExpressionToSql( String aggregationExpression, String operator, Integer deSumId,
-        Integer orgunitId, String startDate, String endDate );
+    String parseExpressionToSql( boolean isInsert, String caseExpression, String operator, Integer aggregateDeId,
+        String aggregateDeName, Integer optionComboId, String optionComboName, Integer deSumId,
+        Collection<Integer> orgunitIds, Period period );
 
     boolean hasOrgunitProgramStageCompleted( String expresstion );
 }

=== modified file 'dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/caseaggregation/DefaultCaseAggregationConditionService.java'
--- dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/caseaggregation/DefaultCaseAggregationConditionService.java	2013-04-29 12:08:00 +0000
+++ dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/caseaggregation/DefaultCaseAggregationConditionService.java	2013-05-02 03:31:45 +0000
@@ -28,6 +28,7 @@
 package org.hisp.dhis.caseaggregation;
 
 import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.AGGRERATION_SUM;
+import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.OBJECT_ORGUNIT_COMPLETE_PROGRAM_STAGE;
 import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.OBJECT_PATIENT;
 import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.OBJECT_PATIENT_ATTRIBUTE;
 import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.OBJECT_PROGRAM;
@@ -35,8 +36,6 @@
 import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.OBJECT_PROGRAM_STAGE_DATAELEMENT;
 import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.SEPARATOR_ID;
 import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.SEPARATOR_OBJECT;
-import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.OBJECT_ORGUNIT_COMPLETE_PROGRAM_STAGE;
-
 import static org.hisp.dhis.i18n.I18nUtils.i18n;
 
 import java.util.ArrayList;
@@ -48,11 +47,13 @@
 import java.util.regex.Matcher;
 import java.util.regex.Pattern;
 
+import org.hisp.dhis.common.Grid;
 import org.hisp.dhis.dataelement.DataElement;
 import org.hisp.dhis.dataelement.DataElementCategoryOptionCombo;
 import org.hisp.dhis.dataelement.DataElementService;
+import org.hisp.dhis.i18n.I18n;
+import org.hisp.dhis.i18n.I18nFormat;
 import org.hisp.dhis.i18n.I18nService;
-import org.hisp.dhis.organisationunit.OrganisationUnit;
 import org.hisp.dhis.patient.Patient;
 import org.hisp.dhis.patient.PatientAttribute;
 import org.hisp.dhis.patient.PatientAttributeService;
@@ -211,23 +212,25 @@
     }
 
     @Override
-    public Double getAggregateValue( CaseAggregationCondition aggregationCondition, OrganisationUnit orgunit,
-        Period period )
-    {
-        DataElement deSum = aggregationCondition.getDeSum();
-        Integer deSumId = (deSum == null) ? null : deSum.getId();
-
-        return aggregationConditionManager.getAggregateValue( aggregationCondition.getAggregationExpression(),
-            aggregationCondition.getOperator(), deSumId, orgunit.getId(), period );
-    }
-
+    public Grid getAggregateValue( CaseAggregationCondition aggregationCondition, Collection<Integer> orgunitIds,
+        Period period, I18nFormat format, I18n i18n )
+    {
+        return aggregationConditionManager.getAggregateValue( aggregationCondition, orgunitIds, period, format, i18n );
+    }
+
+    @Override
+    public void insertAggregateValue( CaseAggregationCondition caseAggregationCondition,
+        Collection<Integer> orgunitIds, Period period )
+    {
+        aggregationConditionManager.insertAggregateValue( caseAggregationCondition, orgunitIds, period);
+    }
+        
     @Override
     public Collection<PatientDataValue> getPatientDataValues( CaseAggregationCondition aggregationCondition,
-        OrganisationUnit orgunit, Period period )
+        Collection<Integer> orgunitIds, Period period )
     {
         // get params
 
-        int orgunitId = orgunit.getId();
         String startDate = DateUtils.getMediumDateString( period.getStartDate() );
         String endDate = DateUtils.getMediumDateString( period.getEndDate() );
         DataElement deSum = aggregationCondition.getDeSum();
@@ -235,9 +238,10 @@
 
         Collection<PatientDataValue> result = new HashSet<PatientDataValue>();
 
-        String sql = aggregationConditionManager.parseExpressionToSql( aggregationCondition.getAggregationExpression(),
-            aggregationCondition.getOperator(), deSumId, orgunitId, startDate, endDate );
+//        String sql = aggregationConditionManager.parseExpressionToSql( aggregationCondition.getAggregationExpression(),
+//            aggregationCondition.getOperator(), deSumId, orgunitIds, startDate, endDate );
 
+        String sql = "";
         Collection<DataElement> dataElements = getDataElementsInCondition( aggregationCondition
             .getAggregationExpression() );
 
@@ -259,17 +263,18 @@
         return result;
     }
 
-    public Collection<Patient> getPatients( CaseAggregationCondition aggregationCondition, OrganisationUnit orgunit,
-        Period period )
+    public Collection<Patient> getPatients( CaseAggregationCondition aggregationCondition,
+        Collection<Integer> orgunitIds, Period period )
     {
         DataElement deSum = aggregationCondition.getDeSum();
         Integer deSumId = (deSum == null) ? null : deSum.getId();
 
-        String sql = aggregationConditionManager
-            .parseExpressionToSql( aggregationCondition.getAggregationExpression(), aggregationCondition.getOperator(),
-                deSumId, orgunit.getId(), DateUtils.getMediumDateString( period.getStartDate() ),
-                DateUtils.getMediumDateString( period.getEndDate() ) );
+//        String sql = aggregationConditionManager
+//            .parseExpressionToSql( aggregationCondition.getAggregationExpression(), aggregationCondition.getOperator(),
+//                deSumId, orgunitIds, DateUtils.getMediumDateString( period.getStartDate() ),
+//                DateUtils.getMediumDateString( period.getEndDate() ) );
 
+        String sql = "";
         Collection<Patient> result = new HashSet<Patient>();
 
         Collection<Integer> patientIds = aggregationConditionManager.executeSQL( sql );
@@ -286,12 +291,11 @@
     }
 
     public Collection<ProgramStageInstance> getProgramStageInstances( CaseAggregationCondition aggregationCondition,
-        OrganisationUnit orgunit, Period period )
+        Collection<Integer> orgunitIds, Period period )
     {
         Collection<ProgramStageInstance> result = new HashSet<ProgramStageInstance>();
 
         // get params
-        int orgunitId = orgunit.getId();
         String startDate = DateUtils.getMediumDateString( period.getStartDate() );
         String endDate = DateUtils.getMediumDateString( period.getEndDate() );
 
@@ -307,8 +311,9 @@
             DataElement deSum = aggregationCondition.getDeSum();
             Integer deSumId = (deSum == null) ? null : deSum.getId();
 
-            sql = aggregationConditionManager.parseExpressionToSql( aggregationCondition.getAggregationExpression(),
-                aggregationCondition.getOperator(), deSumId, orgunitId, startDate, endDate );
+//            sql = aggregationConditionManager.parseExpressionToSql( aggregationCondition.getAggregationExpression(),
+//                aggregationCondition.getOperator(), deSumId, orgunitIds, startDate, endDate );
+            sql = "";
         }
         else
         {
@@ -330,12 +335,14 @@
                 DataElement deSum = aggregationCondition.getDeSum();
                 Integer deSumId = (deSum == null) ? null : deSum.getId();
 
-                String conditionSql = aggregationConditionManager.parseExpressionToSql(
-                    aggregationCondition.getAggregationExpression(), aggregationCondition.getOperator(), deSumId,
-                    orgunit.getId(), DateUtils.getMediumDateString( period.getStartDate() ),
-                    DateUtils.getMediumDateString( period.getEndDate() ) );
-
-                sql += conditionSql + " ) ";
+//                String conditionSql = aggregationConditionManager.parseExpressionToSql(
+//                    aggregationCondition.getAggregationExpression(), aggregationCondition.getOperator(), deSumId,
+//                    orgunitIds, DateUtils.getMediumDateString( period.getStartDate() ),
+//                    DateUtils.getMediumDateString( period.getEndDate() ) );
+//
+//                sql += conditionSql + " ) ";
+                
+                sql = "";
             }
         }
 
@@ -428,8 +435,8 @@
                     }
                     matcher.appendReplacement( description, "[" + programDes + "]" );
                 }
-                else if ( info[0].equalsIgnoreCase( OBJECT_PROGRAM_STAGE ) 
-                        || info[0].equalsIgnoreCase( OBJECT_ORGUNIT_COMPLETE_PROGRAM_STAGE ) )
+                else if ( info[0].equalsIgnoreCase( OBJECT_PROGRAM_STAGE )
+                    || info[0].equalsIgnoreCase( OBJECT_ORGUNIT_COMPLETE_PROGRAM_STAGE ) )
                 {
                     int objectId = Integer.parseInt( ids[0] );
                     ProgramStage programStage = programStageService.getProgramStage( objectId );
@@ -440,8 +447,8 @@
                     }
 
                     String count = (ids.length == 2) ? SEPARATOR_ID + ids[1] : "";
-                    matcher.appendReplacement( description, "[" + info[0] + SEPARATOR_OBJECT
-                        + programStage.getDisplayName() + count + "]" );
+                    matcher.appendReplacement( description,
+                        "[" + info[0] + SEPARATOR_OBJECT + programStage.getDisplayName() + count + "]" );
                 }
             }
 
@@ -584,4 +591,5 @@
     {
         return patientIds.size();
     }
+
 }

=== 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-04-29 12:08:00 +0000
+++ dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/caseaggregation/jdbc/JdbcCaseAggregationConditionManager.java	2013-05-02 03:31:45 +0000
@@ -27,7 +27,7 @@
 
 package org.hisp.dhis.caseaggregation.jdbc;
 
-import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.AGGRERATION_COUNT;
+import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.OBJECT_ORGUNIT_COMPLETE_PROGRAM_STAGE;
 import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.OBJECT_PATIENT;
 import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.OBJECT_PATIENT_ATTRIBUTE;
 import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.OBJECT_PATIENT_PROGRAM_STAGE_PROPERTY;
@@ -37,8 +37,6 @@
 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_PROGRAM_STAGE_PROPERTY;
-import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.OBJECT_ORGUNIT_COMPLETE_PROGRAM_STAGE;
-import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.OPERATOR_AND;
 import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.SEPARATOR_ID;
 import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.SEPARATOR_OBJECT;
 import static org.hisp.dhis.patient.scheduling.CaseAggregateConditionSchedulingManager.TASK_AGGREGATE_QUERY_BUILDER_LAST_12_MONTH;
@@ -48,7 +46,6 @@
 
 import java.sql.ResultSet;
 import java.sql.SQLException;
-import java.util.ArrayList;
 import java.util.Calendar;
 import java.util.Collection;
 import java.util.Date;
@@ -62,14 +59,16 @@
 import org.hisp.dhis.caseaggregation.CaseAggregateSchedule;
 import org.hisp.dhis.caseaggregation.CaseAggregationCondition;
 import org.hisp.dhis.caseaggregation.CaseAggregationConditionManager;
-import org.hisp.dhis.dataelement.DataElement;
-import org.hisp.dhis.dataelement.DataElementService;
-import org.hisp.dhis.jdbc.StatementBuilder;
+import org.hisp.dhis.common.Grid;
+import org.hisp.dhis.common.GridHeader;
+import org.hisp.dhis.i18n.I18n;
+import org.hisp.dhis.i18n.I18nFormat;
 import org.hisp.dhis.period.CalendarPeriodType;
 import org.hisp.dhis.period.Period;
 import org.hisp.dhis.period.PeriodType;
+import org.hisp.dhis.system.grid.ListGrid;
 import org.hisp.dhis.system.util.DateUtils;
-import org.nfunk.jep.JEP;
+import org.hisp.dhis.system.util.TextUtils;
 import org.springframework.jdbc.core.JdbcTemplate;
 import org.springframework.jdbc.core.RowMapper;
 import org.springframework.jdbc.support.rowset.SqlRowSet;
@@ -106,20 +105,6 @@
         this.jdbcTemplate = jdbcTemplate;
     }
 
-    private StatementBuilder statementBuilder;
-
-    public void setStatementBuilder( StatementBuilder statementBuilder )
-    {
-        this.statementBuilder = statementBuilder;
-    }
-
-    public DataElementService dataElementService;
-
-    public void setDataElementService( DataElementService dataElementService )
-    {
-        this.dataElementService = dataElementService;
-    }
-
     // -------------------------------------------------------------------------
     // Implementation Methods
     // -------------------------------------------------------------------------
@@ -167,87 +152,187 @@
         return null;
     }
 
-    public Double getAggregateValue( String caseExpression, String operator, Integer deSumId, Integer orgunitId,
-        Period period )
-    {
-        String startDate = DateUtils.getMediumDateString( period.getStartDate() );
-        String endDate = DateUtils.getMediumDateString( period.getEndDate() );
+    public Grid getAggregateValue( CaseAggregationCondition caseAggregationCondition, Collection<Integer> orgunitIds,
+        Period period, I18nFormat format, I18n i18n )
+    {
+        Collection<Integer> _orgunitIds = getServiceOrgunit( DateUtils.getMediumDateString( period.getStartDate() ),
+            DateUtils.getMediumDateString( period.getEndDate() ) );
+        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( "comment" ), 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(), deSumId,
+                orgunitIds, period );
+
+            SqlRowSet rs = jdbcTemplate.queryForRowSet( sql );
+            grid.addRows( rs );
+
+            return grid;
+        }
+
+        return null;
+    }
+
+    /**
+     * Insert data elements into database directly
+     * 
+     */
+    public void insertAggregateValue( CaseAggregationCondition caseAggregationCondition,
+        Collection<Integer> orgunitIds, Period period )
+    {
+        Integer deSumId = (caseAggregationCondition.getDeSum() == null) ? null : caseAggregationCondition.getDeSum()
+            .getId();
+
+        insertAggregateValue( caseAggregationCondition.getAggregationExpression(),
+            caseAggregationCondition.getOperator(), caseAggregationCondition.getAggregationDataElement().getId(),
+            caseAggregationCondition.getOptionCombo().getId(), deSumId, orgunitIds, period );
+    }
+
+    private void insertAggregateValue( String expression, String operator, Integer dataElementId,
+        Integer optionComboId, 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", deSumId, orgunitIds, period );
+
+        jdbcTemplate.execute( sql );
+    }
+
+    public String parseExpressionToSql( boolean isInsert, String caseExpression, String operator,
+        Integer aggregateDeId, String aggregateDeName, Integer optionComboId, String optionComboName, Integer deSumId,
+        Collection<Integer> orgunitIds, Period period )
+    {
+        String sql = "SELECT '" + aggregateDeId + "' as dataelementid, '" + optionComboId
+            + "' as categoryoptioncomboid, ou.organisationunitid as sourceid, '" + period.getId() + "' as periodid,'"
+            + CaseAggregationCondition.AUTO_STORED_BY + "' as comment, ";
+
+        if ( isInsert )
+        {
+            sql = "INSERT INTO datavalue (dataelementid, categoryoptioncomboid, sourceid, periodid, comment, value)"
+                + sql;
+        }
+        else
+        {
+            sql += "'" + period.getIsoDate() + "' as periodIsoDate,'" + aggregateDeName + "' as dataelementname, '"
+                + optionComboName + "' as categoryoptioncomboname, " + "ou.name as organisationunitname, ";
+        }
 
         if ( operator.equals( CaseAggregationCondition.AGGRERATION_COUNT )
             || operator.equals( CaseAggregationCondition.AGGRERATION_SUM ) )
         {
-            String sql = parseExpressionToSql( caseExpression, operator, deSumId, orgunitId, startDate, endDate );
-            Collection<Integer> ids = this.executeSQL( sql );
-
             if ( hasOrgunitProgramStageCompleted( caseExpression ) )
             {
-                return (ids == null || ids.size() == 0) ? null : ids.iterator().next() + 0.0;
+                sql += createSQL( caseExpression, operator, orgunitIds,
+                    DateUtils.getMediumDateString( period.getStartDate() ),
+                    DateUtils.getMediumDateString( period.getEndDate() ) );
             }
             else
             {
-                return (ids == null || ids.size() == 0) ? null : ids.size() + 0.0;
-            }
-        }
-
-        String sql = "SELECT " + operator + "( cast( pdv.value as DOUBLE PRECISION ) ) ";
-        sql += "FROM patientdatavalue pdv ";
-        sql += "    INNER JOIN programstageinstance psi  ";
-        sql += "    ON psi.programstageinstanceid = pdv.programstageinstanceid ";
-        sql += "WHERE executiondate >='" + DateUtils.getMediumDateString( period.getStartDate() ) + "'  ";
-        sql += "    AND executiondate <='" + DateUtils.getMediumDateString( period.getEndDate() )
-            + "' AND pdv.dataelementid=" + deSumId;
-
-        if ( caseExpression != null && !caseExpression.isEmpty() )
-        {
-            sql = sql + " AND pdv.programstageinstanceid in ( "
-                + parseExpressionToSql( caseExpression, operator, deSumId, orgunitId, startDate, endDate ) + " ) ";
-        }
-System.out.println("\n " + sql + " \n ");
-        Collection<Integer> ids = this.executeSQL( sql );
-        return (ids == null) ? null : ids.iterator().next() + 0.0;
-    }
-
-    public String parseExpressionToSql( String aggregationExpression, String operator, Integer deSumId,
-        Integer orgunitId, String startDate, String endDate )
-    {
-        // Get operators between ( )
-        Pattern patternOperator = Pattern.compile( "(\\)\\s*(OR|AND)\\s*\\( )" );
-
-        Matcher matcherOperator = patternOperator.matcher( aggregationExpression );
-
-        List<String> operators = new ArrayList<String>();
-
-        while ( matcherOperator.find() )
-        {
-            operators.add( matcherOperator.group( 2 ) );
-        }
-
-        List<String> subSQL = new ArrayList<String>();
-
-        String[] conditions = aggregationExpression.split( "(\\)\\s*(OR|AND)\\s*\\()" );
-
-        // Create SQL statement for the first condition
-        String condition = conditions[0].replace( "(", "" ).replace( ")", "" );
-
-        String sql = createSQL( condition, operator, orgunitId, startDate, endDate );
-
-        subSQL.add( sql );
-
-        // Create SQL statement for others
-        for ( int index = 1; index < conditions.length; index++ )
-        {
-            condition = conditions[index].replace( "(", "" ).replace( ")", "" );
-
-            sql = "(" + createSQL( condition, operator, orgunitId, startDate, endDate ) + ")";
-
-            subSQL.add( sql );
-        }
-
-        sql = getSQL( operator, subSQL, operators ).replace( IN_CONDITION_START_SIGN, "(" ).replaceAll(
-            IN_CONDITION_END_SIGN, ")" );
-
-        sql.replaceAll( "COMBINE", " " );
-
+                if ( operator.equals( CaseAggregationCondition.AGGRERATION_COUNT ) )
+                {
+                    sql += operator + " (distinct(pi.patientid) ) as value ";
+                }
+                else
+                {
+                    sql += operator + " (distinct(psi.programinstanceid ) ) as value ";
+                }
+
+                sql += "FROM programstageinstance as psi "
+                    + "INNER JOIN programinstance as pi ON pi.programinstanceid = psi.programinstanceid "
+                    + "INNER JOIN organisationunit ou on ou.organisationunitid=psi.organisationunitid "
+                    + "INNER JOIN patient p on p.patientid=pi.patientid WHERE "
+                    + createSQL( caseExpression, operator, orgunitIds,
+                        DateUtils.getMediumDateString( period.getStartDate() ),
+                        DateUtils.getMediumDateString( period.getEndDate() ) );
+
+                sql += "GROUP BY ou.organisationunitid, ou.name";
+            }
+        }
+        else
+        {
+            sql += " " + operator + "( cast( pdv.value as DOUBLE PRECISION ) ) ";
+            sql += "FROM patientdatavalue 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;
+
+            if ( caseExpression != null && !caseExpression.isEmpty() )
+            {
+                sql += " AND "
+                    + createSQL( caseExpression, operator, orgunitIds,
+                        DateUtils.getMediumDateString( period.getStartDate() ),
+                        DateUtils.getMediumDateString( period.getEndDate() ) );
+            }
+
+            sql += "GROUP BY ou.organisationunitid, ou.name";
+
+        }
+
+        sql = sql.replaceAll( "COMBINE", "" );
+        System.out.println( "\n\n ==== \n " + sql );
         return sql;
     }
 
@@ -263,13 +348,12 @@
     private void runAggregate( Collection<Integer> orgunitIds, CaseAggregateSchedule dataSet, Collection<Period> periods )
     {
         String sql = "select caseaggregationconditionid, aggregationdataelementid, optioncomboid, "
-            + " cagg.aggregationexpression as caseexpression, cagg.\"operator\" as caseoperator, cagg.desum as desumid "
+            + " 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();
+            + "             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 );
 
@@ -305,64 +389,8 @@
                 // Aggregation
                 // ---------------------------------------------------------------------
 
-                for ( Integer orgunitId : orgunitIds )
-                {
-                    String dataValueSql = "select * from datavalue where dataelementid=" + dataelementId
-                        + " and categoryoptioncomboid=" + optionComboId + " and sourceid=" + orgunitId
-                        + " and periodid=" + period.getId() + "";
-
-                    boolean hasValue = jdbcTemplate.queryForRowSet( dataValueSql ).next();
-
-                    Double resultValue = getAggregateValue( caseExpression, caseOperator, deSumId, orgunitId, period );
-
-                    if ( resultValue != null && resultValue != 0 )
-                    {
-                        // -----------------------------------------------------
-                        // Add dataValue
-                        // -----------------------------------------------------
-
-                        if ( !hasValue )
-                        {
-                            String insertValueSql = "INSERT INTO datavalue ( dataelementid, periodid, sourceid, categoryoptioncomboid, value, storedby, lastupdated, followup ) "
-                                + "VALUES ( "
-                                + dataelementId
-                                + ", "
-                                + period.getId()
-                                + ", "
-                                + orgunitId
-                                + ", "
-                                + optionComboId
-                                + ", "
-                                + resultValue
-                                + ", '"
-                                + STORED_BY_DHIS_SYSTEM
-                                + "', '"
-                                + DateUtils.getMediumDateString( new Date() ) + "', false )";
-                            jdbcTemplate.execute( insertValueSql );
-                        }
-
-                        // -----------------------------------------------------
-                        // Update dataValue
-                        // -----------------------------------------------------
-                        else
-                        {
-                            sql = "UPDATE datavalue" + " SET value='" + resultValue + "',lastupdated='" + new Date()
-                                + "' where dataelementId=" + dataelementId + " and periodid=" + period.getId()
-                                + " and sourceid=" + orgunitId + " and categoryoptioncomboid=" + optionComboId
-                                + " and storedby='" + STORED_BY_DHIS_SYSTEM + "'";
-                            jdbcTemplate.execute( sql );
-                        }
-                    }
-
-                    // ---------------------------------------------------------
-                    // Delete dataValue
-                    // ---------------------------------------------------------
-                    else if ( hasValue )
-                    {
-                        String deleteSql = "DELETE from datavalue where dataelementid=dataelementid and periodid=periodid and sourceid=sourceid and categoryoptioncomboid=categoryoptioncomboid";
-                        jdbcTemplate.execute( deleteSql );
-                    }
-                }
+                insertAggregateValue( caseExpression, caseOperator, dataelementId, optionComboId, deSumId, orgunitIds,
+                    period );
             }
 
         }
@@ -449,33 +477,23 @@
      *        Number of visits, Sum, Average, Minimum and Maximum of data
      *        element values.
      * @param deType Aggregate Data element type
-     * @param orgunitId The id of organisation unit where to aggregate data
+     * @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, int orgunitId, String startDate, String endDate )
+    private String createSQL( String caseExpression, String operator, Collection<Integer> orgunitIds, String startDate,
+        String endDate )
     {
         boolean orgunitCompletedProgramStage = false;
 
+        StringBuffer sqlResult = new StringBuffer();
+
         String sqlOrgunitCompleted = "";
 
-        // ---------------------------------------------------------------------
-        // get operators
-        // ---------------------------------------------------------------------
-
-        Pattern patternOperator = Pattern.compile( "(AND|OR)" );
-
-        Matcher matcherOperator = patternOperator.matcher( caseExpression );
-
-        List<String> operators = new ArrayList<String>();
-
-        while ( matcherOperator.find() )
-        {
-            operators.add( matcherOperator.group() );
-        }
-
-        String[] expression = caseExpression.split( "(AND|OR|COMBINE)" );
+        String[] expression = caseExpression.split( "(AND|OR)" );
+        caseExpression = caseExpression.replaceAll( "AND", " ) AND " );
+        caseExpression = caseExpression.replaceAll( "OR", " ) OR " );
 
         // ---------------------------------------------------------------------
         // parse expressions
@@ -483,255 +501,153 @@
 
         Pattern patternCondition = Pattern.compile( CaseAggregationCondition.regExp );
 
-        List<String> conditions = new ArrayList<String>();
-        double value = 0.0;
-
-        for ( int i = 0; i < expression.length; i++ )
+        Matcher matcherCondition = patternCondition.matcher( caseExpression );
+
+        String condition = "";
+
+        int index = 0;
+        while ( matcherCondition.find() )
         {
-            String subExp = expression[i];
-
-            List<String> subConditions = new ArrayList<String>();
-
-            Matcher matcherCondition = patternCondition.matcher( expression[i] );
-
-            String condition = "";
-
-            while ( matcherCondition.find() )
-            {
-                String match = matcherCondition.group();
-
-                subExp = subExp.replace( match, "~" );
-                match = match.replaceAll( "[\\[\\]]", "" );
-
-                String[] info = match.split( SEPARATOR_OBJECT );
-
-                if ( info[0].equalsIgnoreCase( OBJECT_PATIENT ) )
-                {
-                    condition = getConditionForPatient( orgunitId, operator, startDate, endDate );
-                }
-                else if ( info[0].equalsIgnoreCase( OBJECT_PATIENT_PROPERTY ) )
-                {
-                    String propertyName = info[1];
-                    condition = getConditionForPatientProperty( propertyName, operator, startDate, endDate );
-                }
-                else if ( info[0].equalsIgnoreCase( OBJECT_PATIENT_ATTRIBUTE ) )
-                {
-                    int attributeId = Integer.parseInt( info[1] );
-                    condition = getConditionForPatientAttribute( attributeId, operator );
-                }
-                else if ( info[0].equalsIgnoreCase( OBJECT_PROGRAM_STAGE_DATAELEMENT ) )
-                {
-                    String[] ids = info[1].split( SEPARATOR_ID );
-
-                    int programId = Integer.parseInt( ids[0] );
-                    String programStageId = ids[1];
-                    int dataElementId = Integer.parseInt( ids[2] );
-                    DataElement dataElement = dataElementService.getDataElement( dataElementId );
-
-                    String valueToCompare = expression[i].replace( "[" + match + "]", "" ).trim();
-
-                    if ( valueToCompare.equalsIgnoreCase( IS_NULL ) )
-                    {
-                        condition = getConditionForNotDataElement( programId, programStageId, operator, dataElementId,
-                            orgunitId, startDate, endDate );
-
-                        expression[i] = expression[i].replace( valueToCompare, "" );
-                    }
-                    else
-                    {
-                        condition = getConditionForDataElement( programId, programStageId, operator, dataElementId,
-                            orgunitId, startDate, endDate );
-
-                        if ( !expression[i].contains( "+" ) )
-                        {
-                            if ( dataElement.getType().equals( DataElement.VALUE_TYPE_INT ) )
-                            {
-                                condition += " AND cast( pd.value as " + statementBuilder.getDoubleColumnType() + ") ";
-                            }
-                            else
-                            {
-                                condition += " AND pd.value ";
-                            }
-                        }
-                        else
-                        {
-                            subConditions.add( condition );
-                        }
-                    }
-                }
-
-                else if ( info[0].equalsIgnoreCase( OBJECT_PROGRAM_PROPERTY ) )
-                {
-                    condition = getConditionForProgramProperty( operator, startDate, endDate, info[1] );
-                }
-                else if ( info[0].equalsIgnoreCase( OBJECT_PROGRAM ) )
-                {
-                    String[] ids = info[1].split( SEPARATOR_ID );
-                    condition = getConditionForProgram( ids[0], operator, orgunitId, startDate, endDate );
-                    if ( ids.length > 1 )
-                    {
-                        condition += ids[1];
-                    }
-                }
-                else if ( info[0].equalsIgnoreCase( OBJECT_PROGRAM_STAGE ) )
-                {
-                    String[] ids = info[1].split( SEPARATOR_ID );
-                    if ( ids.length == 2 && ids[1].equals( IN_CONDITION_COUNT_X_TIMES ) )
-                    {
-                        condition = getConditionForCountProgramStage( ids[0], operator, orgunitId, startDate, endDate );
-                    }
-                    else
-                    {
-                        condition = getConditionForProgramStage( ids[0], operator, orgunitId, startDate, endDate );
-                    }
-                }
-                else if ( info[0].equalsIgnoreCase( OBJECT_PROGRAM_STAGE_PROPERTY ) )
-                {
-                    condition = getConditionForProgramStageProperty( info[1], operator, orgunitId, startDate, endDate );
-                }
-                else if ( info[0].equalsIgnoreCase( OBJECT_PATIENT_PROGRAM_STAGE_PROPERTY ) )
-                {
-                    condition = getConditionForPatientProgramStageProperty( info[1], operator, startDate, endDate );
-                }
-                else if ( info[0].equalsIgnoreCase( OBJECT_ORGUNIT_COMPLETE_PROGRAM_STAGE ) )
-                {
-                    sqlOrgunitCompleted += getConditionForOrgunitProgramStageCompleted( info[1], operator, orgunitId,
-                        startDate, endDate, orgunitCompletedProgramStage );
-                    orgunitCompletedProgramStage = true;
-                }
-                // -------------------------------------------------------------
-                // Replacing the operand with 1 in order to later be able to
-                // verify
-                // that the formula is mathematically valid
-                // -------------------------------------------------------------
-
-                if ( expression[i].contains( "+" ) )
-                {
-                    Collection<Integer> patientIds = executeSQL( condition );
-                    value = patientIds.size();
-
-                    subExp = subExp.replace( "~", value + "" );
-                }
-
-                condition = expression[i].replace( match, condition ).replaceAll( "[\\[\\]]", "" );
-            }
-
-            if ( expression[i].contains( "+" ) )
-            {
-                final JEP parser = new JEP();
-
-                parser.parseExpression( subExp );
-
-                String _subExp = (parser.getValue() == 1.0) ? " AND 1 = 1 " : " AND 0 = 1 ";
-
-                int noPlus = expression[i].split( "\\+" ).length - 1;
-                List<String> subOperators = new ArrayList<String>();
-                for ( int j = 0; j < noPlus; j++ )
-                {
-                    subOperators.add( "AND" );
-                }
-
-                condition = getSQL( operator, subConditions, subOperators ) + _subExp;
-            }
-
-            condition = condition.trim();
-            if ( !condition.isEmpty() )
-            {
-                conditions.add( condition );
-            }
+            String match = matcherCondition.group();
+
+            match = match.replaceAll( "[\\[\\]]", "" );
+
+            String[] info = match.split( SEPARATOR_OBJECT );
+            if ( info[0].equalsIgnoreCase( OBJECT_PATIENT ) )
+            {
+                condition = getConditionForPatient( orgunitIds, operator, startDate, endDate );
+            }
+            else if ( info[0].equalsIgnoreCase( OBJECT_PATIENT_PROPERTY ) )
+            {
+                String propertyName = info[1];
+                condition = getConditionForPatientProperty( propertyName, operator, startDate, endDate );
+            }
+            else if ( info[0].equalsIgnoreCase( OBJECT_PATIENT_ATTRIBUTE ) )
+            {
+                int attributeId = Integer.parseInt( info[1] );
+                condition = getConditionForPatientAttribute( attributeId, orgunitIds );
+                condition += ")";
+            }
+            else if ( info[0].equalsIgnoreCase( OBJECT_PROGRAM_STAGE_DATAELEMENT ) )
+            {
+                String[] ids = info[1].split( SEPARATOR_ID );
+
+                int programId = Integer.parseInt( ids[0] );
+                String programStageId = ids[1];
+                int dataElementId = Integer.parseInt( ids[2] );
+
+                String compareValue = expression[index].replace( "[" + match + "]", "" ).trim();
+
+                boolean isExist = compareValue.equals( IS_NULL ) ? false : true;
+                condition = getConditionForDataElement( isExist, programId, programStageId, dataElementId, orgunitIds,
+                    startDate, endDate );
+            }
+
+            else if ( info[0].equalsIgnoreCase( OBJECT_PROGRAM_PROPERTY ) )
+            {
+                condition = getConditionForProgramProperty( operator, startDate, endDate, info[1] );
+            }
+            else if ( info[0].equalsIgnoreCase( OBJECT_PROGRAM ) )
+            {
+                String[] ids = info[1].split( SEPARATOR_ID );
+                condition = getConditionForProgram( ids[0], operator, orgunitIds, startDate, endDate );
+                if ( ids.length > 1 )
+                {
+                    condition += ids[1];
+                }
+            }
+            else if ( info[0].equalsIgnoreCase( OBJECT_PROGRAM_STAGE ) )
+            {
+                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 );
+                }
+                else
+                {
+                    condition = getConditionForProgramStage( ids[0], operator, orgunitIds, startDate, endDate );
+                }
+            }
+            else if ( info[0].equalsIgnoreCase( OBJECT_PROGRAM_STAGE_PROPERTY ) )
+            {
+                condition = getConditionForProgramStageProperty( info[1], operator, orgunitIds, startDate, endDate );
+            }
+            else if ( info[0].equalsIgnoreCase( OBJECT_PATIENT_PROGRAM_STAGE_PROPERTY ) )
+            {
+                condition = getConditionForPatientProgramStageProperty( info[1], operator, startDate, endDate );
+            }
+            else if ( info[0].equalsIgnoreCase( OBJECT_ORGUNIT_COMPLETE_PROGRAM_STAGE ) )
+            {
+                sqlOrgunitCompleted += getConditionForOrgunitProgramStageCompleted( info[1], operator, orgunitIds,
+                    startDate, endDate, orgunitCompletedProgramStage );
+                orgunitCompletedProgramStage = true;
+            }
+
+            matcherCondition.appendReplacement( sqlResult, condition );
+
+            index++;
         }
 
+        matcherCondition.appendTail( sqlResult );
+
         if ( !sqlOrgunitCompleted.isEmpty() )
         {
-            conditions.add( sqlOrgunitCompleted );
+            sqlOrgunitCompleted = sqlOrgunitCompleted.substring( 0, sqlOrgunitCompleted.length() - 2 );
         }
 
-        return getSQL( operator, conditions, operators );
+        sqlResult.append( sqlOrgunitCompleted );
+
+        String sql = sqlResult.toString();
+
+        sql = sql.replaceAll( IN_CONDITION_START_SIGN, "(" );
+        sql = sql.replaceAll( IN_CONDITION_END_SIGN, ")" );
+        sql = sql.replaceAll( IS_NULL, " " );
+
+        return sql + " ) ";
     }
 
     /**
      * Return standard SQL of the expression to compare data value as null
      * 
      */
-    private String getConditionForNotDataElement( int programId, String programStageId, String operator,
-        int dataElementId, int orgunitId, String startDate, String endDate )
-    {
-        String sql = "SELECT distinct(pi.patientid) ";
-        String from = "FROM programstageinstance as psi "
-            + "INNER JOIN programinstance as pi ON pi.programinstanceid = psi.programinstanceid ";
-
-        String condition = "pi.patientid ";
-
-        if ( !operator.equals( AGGRERATION_COUNT ) )
-        {
-            sql = "SELECT psi.programstageinstanceid ";
-            condition = "psi.programstageinstanceid ";
-        }
-
-        sql += from
-            + "LEFT OUTER JOIN patientdatavalue as pd ON psi.programstageinstanceid = pd.programstageinstanceid "
-            + "WHERE psi.executionDate >= '" + startDate + "' AND psi.executionDate <= '" + endDate + "' "
-            + "AND pd.value IS NULL AND " + condition + " NOT IN  ( " + "SELECT " + condition + from
-            + "WHERE psi.organisationunitid = " + orgunitId + " AND pi.programid = " + programId + " "
-            + "AND psi.executionDate >= '" + startDate + "' AND psi.executionDate <= '" + endDate + "' "
-            + "AND pd.dataelementid = " + dataElementId + " ";
-
-        if ( !programStageId.equals( IN_CONDITION_GET_ALL ) )
-        {
-            sql += " AND psi.programstageid = " + programStageId;
-        }
-
-        return sql + "  ) ";
-    }
-
-    /**
-     * Return standard SQL of a data element expression. E.g [DE:1.2.3]
-     * 
-     */
-    private String getConditionForDataElement( int programId, String programStageId, String operator,
-        int dataElementId, int orgunitId, String startDate, String endDate )
-    {
-        String sql = "SELECT distinct(pi.patientid) ";
-        String from = "FROM programstageinstance as psi "
-            + "INNER JOIN patientdatavalue as pd ON psi.programstageinstanceid = pd.programstageinstanceid "
-            + "INNER JOIN programinstance as pi ON pi.programinstanceid = psi.programinstanceid ";
-
-        if ( !operator.equals( AGGRERATION_COUNT ) )
-        {
-            sql = "SELECT psi.programstageinstanceid ";
-            from = "FROM programstageinstance as psi "
-                + "INNER JOIN patientdatavalue as pd ON psi.programstageinstanceid = pd.programstageinstanceid ";
-        }
-
-        sql += from + " WHERE pd.dataelementid=" + dataElementId + "  AND psi.organisationunitid=" + orgunitId
-            + "             AND psi.executionDate>='" + startDate + "' AND psi.executionDate <= '" + endDate + "'";
-
-        if ( !programStageId.equals( IN_CONDITION_GET_ALL ) )
-        {
-            sql += " AND psi.programstageid = " + programStageId;
-        }
-
-        return sql;
-    }
-
-    /**
-     * Return standard SQL of a patient-attribute expression. E.g [CA:1]
-     * 
-     */
-    private String getConditionForPatientAttribute( int attributeId, String operator )
-    {
-        String sql = "SELECT distinct(pi.patientid) ";
-        String from = "FROM patientattributevalue pi ";
-
-        if ( !operator.equals( AGGRERATION_COUNT ) )
-        {
-            sql = "SELECT psi.programstageinstanceid ";
-            from = "FROM programstageinstance psi inner join programinstance pi "
-                + "on psi.programinstanceid=pi.programinstanceid " + "inner join patientattributevalue pav "
-                + "on pav.patientid=pi.patientid ";
-        }
-
-        return sql + from + "WHERE patientattributeid=" + attributeId + " AND value ";
+    private String getConditionForDataElement( boolean isExist, int programId, String programStageId,
+        int dataElementId, Collection<Integer> orgunitIds, String startDate, String endDate )
+    {
+        String keyExist = (isExist == true) ? "EXISTS" : "NOT EXISTS";
+
+        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>='" + startDate
+            + "' AND psi.executionDate <= '" + endDate + "' ";
+
+        if ( !programStageId.equals( IN_CONDITION_GET_ALL ) )
+        {
+            sql += " AND _psi.programstageid = " + programStageId;
+        }
+
+        if ( isExist )
+        {
+            sql += " AND _pdv.value ";
+        }
+
+        return sql;
+    }
+
+    /**
+     * Return standard SQL of a dynamic patient-attribute expression. E.g [CA:1]
+     * 
+     */
+    private String getConditionForPatientAttribute( int attributeId, Collection<Integer> orgunitIds )
+    {
+        String sql = " EXISTS ( SELECT * " + "FROM patientattributevalue _pav "
+            + "WHERE _pav.patientid = pi.patientid " + "and _pav.patientattributeid=" + attributeId
+            + "  AND p.organisationunitid in (" + TextUtils.getCommaDelimitedString( orgunitIds ) + ") AND _pav.value ";
+
+        return sql;
     }
 
     /**
@@ -739,24 +655,14 @@
      * of person registration
      * 
      */
-    private String getConditionForPatient( int orgunitId, String operator, String startDate, String endDate )
+    private String getConditionForPatient( Collection<Integer> orgunitIds, String operator, String startDate,
+        String endDate )
     {
-        String sql = "SELECT pi.patientid ";
-        String from = "FROM patient pi ";
-        String where = "WHERE pi.organisationunitid=" + orgunitId + "  AND pi.registrationdate>= '" + startDate + "' "
-            + "AND pi.registrationdate <= '" + endDate + "'";
-
-        if ( !operator.equals( AGGRERATION_COUNT ) )
-        {
-            sql = "SELECT psi.programstageinstanceid ";
-            from = "FROM programstageinstance psi inner join programinstance pi "
-                + "on psi.programinstanceid=pi.programinstanceid "
-                + "inner join patient p on p.patientid=pi.patientid ";
-            where = "WHERE p.organisationunitid=" + orgunitId + "  AND p.registrationdate>= '" + startDate + "' "
-                + "AND p.registrationdate <= '" + endDate + "'";
-        }
-
-        return sql + from + where;
+        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;
     }
 
     /**
@@ -767,14 +673,7 @@
     private String getConditionForPatientProperty( String propertyName, String operator, String startDate,
         String endDate )
     {
-        String sql = "SELECT distinct(pi.patientid) FROM patient pi WHERE ";
-
-        if ( !operator.equals( AGGRERATION_COUNT ) )
-        {
-            sql = "SELECT psi.programstageinstanceid " + "FROM programstageinstance psi inner join programinstance pi "
-                + "on psi.programinstanceid=pi.programinstanceid "
-                + "inner join patient p on p.patientid=pi.patientid WHERE ";
-        }
+        String sql = " EXISTS (SELECT * FROM patient _p WHERE _p.patientid = pi.patientid AND ";
 
         if ( propertyName.equals( PROPERTY_AGE ) )
         {
@@ -796,19 +695,9 @@
     private String getConditionForPatientProgramStageProperty( String propertyName, String operator, String startDate,
         String endDate )
     {
-        String sql = "SELECT distinct(pi.patientid) ";
-        String from = "FROM programinstance pi INNER JOIN programstageinstance psi "
-            + "ON psi.programinstanceid=pi.programinstanceid ";
-        if ( !operator.equals( AGGRERATION_COUNT ) )
-        {
-            sql = "SELECT psi.programstageinstance ";
-            from = "FROM programstageinstance psi ";
-        }
-
-        from += "inner join patient p on p.patientid=pi.patientid ";
-
-        sql += from + "WHERE executionDate>='" + startDate + "' and executionDate<='" + endDate + "' and "
-            + propertyName;
+        String sql = " EXISTS ( SELECT * from programstageinstance _psi "
+            + "WHERE _psi.programstageinstanceid=psi.programstageinstanceid AND _psi.executionDate>='" + startDate
+            + "' and _psi.executionDate<='" + endDate + "' and " + propertyName;
 
         return sql;
     }
@@ -821,16 +710,17 @@
      */
     private String getConditionForProgramProperty( String operator, String startDate, String endDate, String property )
     {
-        String sql = "SELECT pi.patientid FROM programinstance as pi ";
-
-        if ( !operator.equals( AGGRERATION_COUNT ) )
-        {
-            sql = "SELECT psi.programstageinstanceid FROM programinstance as pi "
-                + "INNER JOIN programstageinstance psi ON psi.programinstanceid=pi.programinstanceid ";
-        }
-
-        return sql + "WHERE pi.enrollmentdate>='" + startDate + "' " + "AND pi.enrollmentdate<='" + endDate + "'  AND "
-            + property;
+        String sql = " EXISTS ( SELECT * FROM programinstance as _pi WHERE psi.programinstanceid=_pi.programsinstanceid AND "
+            + "_pi.enrollmentdate>='"
+            + startDate
+            + "' "
+            + "AND _pi.enrollmentdate<='"
+            + endDate
+            + "'  AND "
+            + property
+            + " ";
+
+        return sql;
     }
 
     /**
@@ -838,20 +728,16 @@
      * program. E.g [PG:1]
      * 
      */
-    private String getConditionForProgram( String programId, String operator, int orgunitId, String startDate,
-        String endDate )
+    private String getConditionForProgram( String programId, String operator, Collection<Integer> orgunitIds,
+        String startDate, String endDate )
     {
-        String sql = "SELECT distinct(pi.patientid) FROM programinstance as pi "
-            + "inner join patient psi on psi.patientid=pi.patientid ";
-
-        if ( !operator.equals( AGGRERATION_COUNT ) )
-        {
-            sql = "SELECT psi.programstageinstanceid FROM programinstance as pi "
-                + "INNER JOIN programstageinstance psi ON pi.programinstanceid=psi.programinstanceid ";
-        }
-
-        return sql + "WHERE pi.programid=" + programId + " " + " AND psi.organisationunitid = " + orgunitId
-            + " AND pi.enrollmentdate >= '" + startDate + "' AND pi.enrollmentdate <= '" + endDate + "' ";
+        String sql = " EXISTS ( SELECT * FROM programinstance as _pi "
+            + "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 _pi.enrollmentdate >= '" + startDate + "' AND _pi.enrollmentdate <= '" + endDate + "' ";
+
+        return sql;
     }
 
     /**
@@ -859,20 +745,16 @@
      * [PS:1]
      * 
      */
-    private String getConditionForProgramStage( String programStageId, String operator, int orgunitId,
+    private String getConditionForProgramStage( String programStageId, String operator, Collection<Integer> orgunitIds,
         String startDate, String endDate )
     {
-        String select = "SELECT distinct(pi.patientid) ";
-
-        if ( !operator.equals( AGGRERATION_COUNT ) )
-        {
-            select = "SELECT psi.programstageinstanceid ";
-        }
-
-        return select + "FROM programinstance as pi INNER JOIN programstageinstance psi "
-            + "ON pi.programinstanceid = psi.programinstanceid WHERE psi.programstageid=" + programStageId + " "
-            + "AND psi.executiondate >= '" + startDate + "' AND psi.executiondate <= '" + endDate
-            + "' AND psi.organisationunitid = " + orgunitId + " ";
+        String sql = " EXISTS ( SELECT * 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 >= '" + startDate
+            + "' AND _psi.executiondate <= '" + endDate + "' AND _psi.organisationunitid in ("
+            + TextUtils.getCommaDelimitedString( orgunitIds ) + ")  ";
+
+        return sql;
     }
 
     /**
@@ -881,30 +763,18 @@
      * trimester.
      * 
      */
-    private String getConditionForCountProgramStage( String programStageId, String operator, int orgunitId,
-        String startDate, String endDate )
+    private String getConditionForCountProgramStage( String programStageId, String operator,
+        Collection<Integer> orgunitIds, String startDate, String endDate )
     {
-        String select = "SELECT distinct(pi.patientid) ";
-
-        if ( !operator.equals( AGGRERATION_COUNT ) )
-        {
-            select = "SELECT psi.programstageinstanceid ";
-        }
-
-        select += "FROM programstageinstance as psi "
-            + "INNER JOIN programinstance as pi ON pi.programinstanceid = psi.programinstanceid "
-            + "WHERE psi.organisationunitid = " + orgunitId + " and psi.programstageid = " + programStageId + " "
-            + "AND psi.executionDate >= '" + startDate + "' AND psi.executionDate <= '" + endDate + "' "
-            + "GROUP BY psi.programinstanceid ";
-
-        if ( operator.equals( AGGRERATION_COUNT ) )
-        {
-            select += ",pi.patientid ";
-        }
-
-        select += "HAVING count(psi.programstageinstanceid) ";
-
-        return select;
+        String sql = " EXISTS ( SELECT * " + "FROM programstageinstance as _psi "
+            + "INNER JOIN programinstance as _pi ON psi.programinstanceid=psi.programinstanceid "
+            + "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 "
+            + "HAVING count(_psi.programstageinstanceid) ";
+
+        return sql;
 
     }
 
@@ -913,20 +783,15 @@
      * and due-date. E.g [PSP:DATE@executionDate#-DATE@dueDate#]
      * 
      */
-    private String getConditionForProgramStageProperty( String property, String operator, int orgunitId,
-        String startDate, String endDate )
+    private String getConditionForProgramStageProperty( String property, String operator,
+        Collection<Integer> orgunitIds, String startDate, String endDate )
     {
-        String select = "SELECT distinct(pi.patientid) ";
-
-        if ( !operator.equals( AGGRERATION_COUNT ) )
-        {
-            select = "SELECT psi.programstageinstanceid ";
-        }
-
-        return select + "FROM programinstance as pi INNER JOIN programstageinstance psi "
-            + "ON pi.programinstanceid = psi.programinstanceid WHERE " + " psi.executiondate >= '" + startDate
-            + "' AND psi.executiondate <= '" + endDate + "' AND psi.organisationunitid = " + orgunitId + " AND "
-            + property;
+        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;
     }
 
     /**
@@ -936,64 +801,25 @@
      * @flag True if there are many stages in the expression
      * 
      */
-    private String getConditionForOrgunitProgramStageCompleted( String programStageId, String operator, int orgunitId,
-        String startDate, String endDate, boolean flag )
+    private String getConditionForOrgunitProgramStageCompleted( String programStageId, String operator,
+        Collection<Integer> orgunitIds, String startDate, String endDate, boolean flag )
     {
         String sql = "";
         if ( !flag )
         {
-            sql = "SELECT '1' FROM organisationunit ou WHERE ou.organisationunitid=" + orgunitId + "  ";
+            sql = " '1' FROM organisationunit ou WHERE ou.organisationunitid in ("
+                + TextUtils.getCommaDelimitedString( orgunitIds ) + ")  ";
         }
-        
-        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 <= '"
+
+        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 + "' ) ";
 
         return sql;
     }
 
     /**
-     * Return standard SQL by combining all sub-expressions of an aggregate
-     * query builder formula.
-     * 
-     */
-    private String getSQL( String aggregateOperator, List<String> conditions, List<String> operators )
-    {
-        String sql = conditions.get( 0 );
-
-        String sqlAnd = "";
-
-        int index = 0;
-
-        for ( index = 0; index < operators.size(); index++ )
-        {
-            if ( operators.get( index ).equalsIgnoreCase( OPERATOR_AND ) )
-            {
-                if ( aggregateOperator.equals( AGGRERATION_COUNT ) )
-                {
-                    sql += " AND pi.patientid IN ( " + conditions.get( index + 1 );
-                }
-                else
-                {
-                    sql += " AND psi.programstageinstanceid IN ( " + conditions.get( index + 1 );
-                }
-                sqlAnd += ")";
-            }
-            else
-            {
-                sql += sqlAnd;
-                sql += " UNION ( " + conditions.get( index + 1 ) + " ) ";
-                sqlAnd = "";
-            }
-        }
-
-        sql += sqlAnd;
-        
-        return sql;
-    }
-
-    /**
      * Return the Ids of organisation units which patients registered or events
      * happened.
      * 
@@ -1039,4 +865,5 @@
 
         return false;
     }
+
 }

=== modified file 'dhis-2/dhis-services/dhis-service-patient/src/main/resources/META-INF/dhis/beans.xml'
--- dhis-2/dhis-services/dhis-service-patient/src/main/resources/META-INF/dhis/beans.xml	2013-04-17 07:58:14 +0000
+++ dhis-2/dhis-services/dhis-service-patient/src/main/resources/META-INF/dhis/beans.xml	2013-05-02 03:31:45 +0000
@@ -15,8 +15,6 @@
 	<bean id="org.hisp.dhis.caseaggregation.CaseAggregationConditionManager"
 		class="org.hisp.dhis.caseaggregation.jdbc.JdbcCaseAggregationConditionManager">
 		<property name="jdbcTemplate" ref="jdbcTemplate" />
-		<property name="statementBuilder" ref="statementBuilder" />
-		<property name="dataElementService" ref="org.hisp.dhis.dataelement.DataElementService" />
 	</bean>
 	
 	<bean id="org.hisp.dhis.caseaggregation.CaseAggregationConditionStore"

=== 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	2013-04-28 14:35:49 +0000
+++ dhis-2/dhis-web/dhis-web-caseentry/src/main/java/org/hisp/dhis/caseentry/action/caseaggregation/CaseAggregationResultAction.java	2013-05-02 03:31:45 +0000
@@ -38,6 +38,7 @@
 
 import org.hisp.dhis.caseaggregation.CaseAggregationCondition;
 import org.hisp.dhis.caseaggregation.CaseAggregationConditionService;
+import org.hisp.dhis.common.Grid;
 import org.hisp.dhis.dataelement.DataElement;
 import org.hisp.dhis.dataelement.DataElementCategoryOptionCombo;
 import org.hisp.dhis.dataset.DataSet;
@@ -161,39 +162,11 @@
         return autoSave;
     }
 
-    private Map<String, String> mapStatusValues = new HashMap<String, String>();
-
-    public Map<String, String> getMapStatusValues()
-    {
-        return mapStatusValues;
-    }
-
-    private Map<String, Set<DataValue>> mapDataValues = new HashMap<String, Set<DataValue>>();
-
-    public Map<String, Set<DataValue>> getMapDataValues()
-    {
-        return mapDataValues;
-    }
-
-    private Map<DataValue, CaseAggregationCondition> mapCaseAggCondition = new HashMap<DataValue, CaseAggregationCondition>();
-
-    public Map<DataValue, CaseAggregationCondition> getMapCaseAggCondition()
-    {
-        return mapCaseAggCondition;
-    }
-
-    private Collection<OrganisationUnit> orgunits = new HashSet<OrganisationUnit>();
-
-    public Collection<OrganisationUnit> getOrgunits()
-    {
-        return orgunits;
-    }
-
-    private List<Period> periods = new ArrayList<Period>();
-
-    public List<Period> getPeriods()
-    {
-        return periods;
+    private List<Grid> grids = new ArrayList<Grid>();
+
+    public List<Grid> getGrids()
+    {
+        return grids;
     }
 
     // -------------------------------------------------------------------------
@@ -221,7 +194,7 @@
 
         CalendarPeriodType periodType = (CalendarPeriodType) PeriodType.getPeriodTypeByName( selectedDataSet
             .getPeriodType().getName() );
-
+        List<Period> periods = new ArrayList<Period>();
         periods.addAll( periodType.generatePeriods( sDate, eDate ) );
 
         // ---------------------------------------------------------------------
@@ -256,74 +229,29 @@
         // ---------------------------------------------------------------------
         // Aggregation
         // ---------------------------------------------------------------------
-        for ( Integer orgUnitId : orgunitIds )
+        // for ( Integer orgUnitId : orgunitIds )
+        // {
+        // OrganisationUnit orgUnit =
+        // organisationUnitService.getOrganisationUnit( orgUnitId );
+
+        for ( CaseAggregationCondition condition : aggregationConditions )
         {
-            OrganisationUnit orgUnit = organisationUnitService.getOrganisationUnit( orgUnitId );
+            DataElement dElement = condition.getAggregationDataElement();
+            DataElementCategoryOptionCombo optionCombo = condition.getOptionCombo();
 
-            for ( CaseAggregationCondition condition : aggregationConditions )
+            for ( Period period : periods )
             {
-                DataElement dElement = condition.getAggregationDataElement();
-                DataElementCategoryOptionCombo optionCombo = condition.getOptionCombo();
-
-                for ( Period period : periods )
-                {
-                    Double resultValue = aggregationConditionService.getAggregateValue( condition, orgUnit, period );
-                    DataValue dataValue = dataValueService.getDataValue( orgUnit, dElement, period, optionCombo );
-
-                    String key = orgUnitId + "-" + format.formatPeriod( period );
-                    String keyStatus = key + "-" + dElement.getId();
-
-                    if ( resultValue != null && resultValue != 0 )
-                    {
-                        if ( dataValue == null )
-                        {
-                            dataValue = new DataValue( dElement, period, orgUnit, "" + resultValue, "", new Date(),
-                                null, optionCombo );
-                            mapStatusValues.put( keyStatus, i18n.getString( ADD_STATUS ) );
-                            if ( autoSave )
-                            {
-                                dataValueService.addDataValue( dataValue );
-                            }
-                        }
-                        else
-                        {
-                            dataValue.setValue( "" + resultValue );
-                            dataValue.setTimestamp( new Date() );
-                            mapStatusValues.put( keyStatus, i18n.getString( UPDATE_STATUS ) );
-                            if ( autoSave )
-                            {
-                                dataValueService.updateDataValue( dataValue );
-                            }
-                        }
-                        mapCaseAggCondition.put( dataValue, condition );
-                    }
-                    else if ( dataValue != null )
-                    {
-                        mapStatusValues.put( keyStatus, i18n.getString( DELETE_STATUS ) );
-                        if ( autoSave )
-                        {
-                            dataValueService.deleteDataValue( dataValue );
-                        }
-                    }
-
-                    if ( dataValue != null )
-                    {
-                        Set<DataValue> dataValues = null;
-                        if ( mapDataValues.containsKey( key ) )
-                        {
-                            dataValues = mapDataValues.get( key );
-                        }
-                        else
-                        {
-                            dataValues = new HashSet<DataValue>();
-                        }
-
-                        dataValues.add( dataValue );
-                        mapDataValues.put( key, dataValues );
-                        orgunits.add( orgUnit );
-                    }
+                if ( autoSave )
+                {
+                    aggregationConditionService.insertAggregateValue( condition, orgunitIds, period );
+                }
+                else
+                {
+                    grids.add( aggregationConditionService.getAggregateValue( condition, orgunitIds, period, format,
+                        i18n ) );
                 }
             }
+            // }
 
         }
         return SUCCESS;

=== modified file 'dhis-2/dhis-web/dhis-web-caseentry/src/main/java/org/hisp/dhis/caseentry/action/caseaggregation/CaseAggregationResultDetailsAction.java'
--- dhis-2/dhis-web/dhis-web-caseentry/src/main/java/org/hisp/dhis/caseentry/action/caseaggregation/CaseAggregationResultDetailsAction.java	2013-02-07 14:28:18 +0000
+++ dhis-2/dhis-web/dhis-web-caseentry/src/main/java/org/hisp/dhis/caseentry/action/caseaggregation/CaseAggregationResultDetailsAction.java	2013-05-02 03:31:45 +0000
@@ -152,7 +152,7 @@
     public String execute()
         throws Exception
     {
-        OrganisationUnit orgunit = organisationUnitService.getOrganisationUnit( orgunitId );
+//        OrganisationUnit orgunit = organisationUnitService.getOrganisationUnit( orgunitId );
 
         PeriodType periodType = periodService.getPeriodTypeByName( periodTypeName );
         Period period = periodType.createPeriod( format.parseDate( startDate ) );
@@ -164,7 +164,8 @@
         {
             mapPatients = new HashMap<Patient, Collection<PatientDataValue>>();
 
-            Collection<Patient> patients = aggregationConditionService.getPatients( aggCondition, orgunit, period );
+            Collection<Patient> patients = null;
+//            Collection<Patient> patients = aggregationConditionService.getPatients( aggCondition, orgunit, period );
 
             for ( Patient patient : patients )
             {
@@ -186,8 +187,9 @@
         {
             mapEvents = new HashMap<ProgramStageInstance, Collection<PatientDataValue>>();
 
-            Collection<ProgramStageInstance> programStageInstances = aggregationConditionService
-                .getProgramStageInstances( aggCondition, orgunit, period );
+            Collection<ProgramStageInstance> programStageInstances = null;
+//            Collection<ProgramStageInstance> programStageInstances = aggregationConditionService
+//                .getProgramStageInstances( aggCondition, orgunit, period );
 
             for ( ProgramStageInstance programStageInstance : programStageInstances )
             {

=== modified file 'dhis-2/dhis-web/dhis-web-caseentry/src/main/java/org/hisp/dhis/caseentry/action/caseaggregation/SaveAggregateDataValueAction.java'
--- dhis-2/dhis-web/dhis-web-caseentry/src/main/java/org/hisp/dhis/caseentry/action/caseaggregation/SaveAggregateDataValueAction.java	2012-02-27 05:35:23 +0000
+++ dhis-2/dhis-web/dhis-web-caseentry/src/main/java/org/hisp/dhis/caseentry/action/caseaggregation/SaveAggregateDataValueAction.java	2013-05-02 03:31:45 +0000
@@ -41,7 +41,6 @@
 import org.hisp.dhis.organisationunit.OrganisationUnit;
 import org.hisp.dhis.organisationunit.OrganisationUnitService;
 import org.hisp.dhis.period.Period;
-import org.hisp.dhis.period.PeriodService;
 import org.hisp.dhis.period.PeriodType;
 import org.hisp.dhis.user.CurrentUserService;
 
@@ -82,13 +81,6 @@
         this.categoryService = categoryService;
     }
 
-    private PeriodService periodService;
-
-    public void setPeriodService( PeriodService periodService )
-    {
-        this.periodService = periodService;
-    }
-
     private DataValueService dataValueService;
 
     public void setDataValueService( DataValueService dataValueService )
@@ -102,14 +94,7 @@
     {
         this.currentUserService = currentUserService;
     }
-
-    private I18nFormat format;
-
-    public void setFormat( I18nFormat format )
-    {
-        this.format = format;
-    }
-
+    
     // -------------------------------------------------------------------------
     // Input
     // -------------------------------------------------------------------------
@@ -135,17 +120,14 @@
             // -----------------------------------------------------------------
             // Get params
             // -----------------------------------------------------------------
+            
             String[] info = aggregateValue.split( SEPERATE_SIGN );
 
             int dataElementId = Integer.parseInt( info[0] );
             int optionComboId = Integer.parseInt( info[1] );
-
-            String periodTypeName = info[2];
-            String startDate = info[3];
-
-            int orgunitId = Integer.parseInt( info[4] );
-
-            String resultValue = info[5];
+            String periodIsoId = info[2];
+            int orgunitId = Integer.parseInt( info[3] );
+            String resultValue = info[4];
             
             // -----------------------------------------------------------------
             // Create objects
@@ -154,9 +136,8 @@
             DataElement dataElement = dataElementService.getDataElement( dataElementId );
             DataElementCategoryOptionCombo optionCombo = categoryService
                 .getDataElementCategoryOptionCombo( optionComboId );
-
-            PeriodType periodType = periodService.getPeriodTypeByName( periodTypeName );
-            Period period = periodType.createPeriod( format.parseDate( startDate ) );
+            
+            Period period = PeriodType.getPeriodFromIsoString( periodIsoId );
 
             OrganisationUnit orgunit = organisationUnitService.getOrganisationUnit( orgunitId );
 

=== modified file 'dhis-2/dhis-web/dhis-web-caseentry/src/main/resources/META-INF/dhis/beans.xml'
--- dhis-2/dhis-web/dhis-web-caseentry/src/main/resources/META-INF/dhis/beans.xml	2013-04-28 14:35:49 +0000
+++ dhis-2/dhis-web/dhis-web-caseentry/src/main/resources/META-INF/dhis/beans.xml	2013-05-02 03:31:45 +0000
@@ -326,7 +326,6 @@
 		<property name="dataElementService" ref="org.hisp.dhis.dataelement.DataElementService" />
 		<property name="categoryService"
 			ref="org.hisp.dhis.dataelement.DataElementCategoryService" />
-		<property name="periodService" ref="org.hisp.dhis.period.PeriodService" />
 		<property name="dataValueService" ref="org.hisp.dhis.datavalue.DataValueService" />
 		<property name="currentUserService" ref="org.hisp.dhis.user.CurrentUserService" />
 

=== modified file 'dhis-2/dhis-web/dhis-web-caseentry/src/main/resources/org/hisp/dhis/caseentry/i18n_module.properties'
--- dhis-2/dhis-web/dhis-web-caseentry/src/main/resources/org/hisp/dhis/caseentry/i18n_module.properties	2013-04-30 12:08:42 +0000
+++ dhis-2/dhis-web/dhis-web-caseentry/src/main/resources/org/hisp/dhis/caseentry/i18n_module.properties	2013-05-02 03:31:45 +0000
@@ -636,4 +636,7 @@
 program_stage_completeness_form = Program stage completeness
 number_of_events = Number of events
 followup = Follow-up
-filter_on_section = Filter on section
\ No newline at end of file
+filter_on_section = Filter on section
+dataelementname = Data element
+categoryoptioncomboname = Category option combo
+organisationunitname = Organisation unit
\ No newline at end of file

=== modified file 'dhis-2/dhis-web/dhis-web-caseentry/src/main/webapp/dhis-web-caseentry/caseAggregationResult.vm'
--- dhis-2/dhis-web/dhis-web-caseentry/src/main/webapp/dhis-web-caseentry/caseAggregationResult.vm	2013-04-10 04:39:05 +0000
+++ dhis-2/dhis-web/dhis-web-caseentry/src/main/webapp/dhis-web-caseentry/caseAggregationResult.vm	2013-05-02 03:31:45 +0000
@@ -1,72 +1,64 @@
 <h4> $i18n.getString( "case_aggregation_result" )</h4>
-#if($orgunits.size()>0)
-	<div>
+<div>
 		<input type="button" value="$i18n.getString( 'back' )" onclick="backBtnOnClick();" style="width:10em">
 		<input type='button' value="$i18n.getString( 'save_selected_values' )" onclick='saveAggregateDataValues( false )' >
 		<input type='button' value="$i18n.getString( 'save_all_values' )" onclick='saveAggregateDataValues( true )' >
-	</div>
-	<p></p>
-
-	#set( $index = 0 )
-	#foreach( $orgunit in $orgunits)
-		<h2>$orgunit.name</h2>
-		#foreach( $period in $periods)
-			#set( $index = $index + 1 )
-			#set( $periodInfo = $orgunit.id + '-' + $index )
-			#set( $periodButton = $periodInfo + "-button")
-			#set( $periodDiv = $periodInfo + "-div")
-			#set( $key = $orgunit.id + '-' + $format.formatPeriod($period) )
-			
-			#if( $!mapDataValues.get( $key ) )
-				<h4>$format.formatPeriod( $period )
-				<img id='$periodButton' src="../images/down.png" onclick="toggleResult('$periodInfo');" style="cursor: pointer;"></h4>
-			
-				#set( $dataValues = $mapDataValues.get( $key ) ) 
-				<table class="formSection mainPageTable hidden" id='$periodDiv'>
-					<col width="40"> 
-					<col>
-					<col>
-					<col>
-					<tbody>
-					#set( $mark = false )
-					#set($no = 1)
-						<tr>
-							<th>#</th>
-							<th><input type='checkbox' onchange="toogleAllCheckBoxes( '$periodDiv', this.checked );"></th>
-							<th>$i18n.getString('name')</th>
-							<th>$i18n.getString('value')</th>
-							<th>$i18n.getString('status')</th>
-							<th>$i18n.getString('operator')</th>
-						</tr>
-						#foreach( $dataValue in $dataValues)
-							#set( $statusKey = $key + '-' + $dataValue.dataElement.id )
-							<tr #alternate( $mark )>
-								<td>$no</td>
-								<td>
-									#set( $item=$dataValue.dataElement.id + '_' + $dataValue.optionCombo.id + '_' + $period.periodType.name + '_' + $format.formatDate($period.startDate) + '_' + $orgunit.id + '_' + $dataValue.value )
-									<input type='checkbox' name='aggregateValues' value='$item'>
-								</td>
-								<td>$dataValue.dataElement.name $dataValue.optionCombo.name </td>
-								<td>$dataValue.value</td>
-								<td>$!mapStatusValues.get( $statusKey )</td>
-								<td>
-									#if( $mapCaseAggCondition.get($dataValue).id )
-									<a href='javascript:viewResultDetails($orgunit.id, "$period.periodType.name", "$format.formatDate($period.startDate)",$mapCaseAggCondition.get($dataValue).id)' title="$i18n.getString( 'show_details' )"><img src="../images/information.png" alt="$i18n.getString( 'show_details' )"></a>
-									#end
-								</td>
-							</tr>
-							#set( $mark = !$mark  )
-							#set ($no = $no + 1)
+</div><br>
+
+#set($divIdx=0)
+#set($hashValues=false)
+#foreach( $grid in $grids )
+	#if($grid.getRows().size() > 0)
+	#set($hashValues=true)
+	#set($divIdx=$divIdx+1)
+	<h3>
+		$!encoder.htmlEncode( $grid.title ) - $!encoder.htmlEncode( $grid.subtitle )
+		<img id='$periodButton' src="../images/down.png" onclick="toggleResult('$divIdx');" style="cursor: pointer;"></h4>
+	</h3>
+
+	<h5>
+		</h5>
+	<table class="listTable gridTable" id='div-$divIdx' style='display:none;'>
+		<thead>
+			<tr>
+				<th><input type='checkbox' onchange="toogleAllCheckBoxes( '$divIdx', this.checked );"></th>
+				#foreach( $header in $grid.getVisibleHeaders() )
+				<th #if( $header.meta )style="text-align:left"#end>$!encoder.htmlEncode( $header.name )</th>
+				#end
+			</tr>
+		</thead>
+		<tbody>
+			#foreach( $row in $grid.getRows() )
+				<tr>
+					#foreach( $col in $row )
+						#if($velocityCount==1)
+							#set($dataelementid=$col)
+						#elseif($velocityCount==2)
+							#set($categoryoptioncomboid=$col)
+						#elseif($velocityCount==3)
+							#set($periodid=$col)
+						#elseif($velocityCount==4)
+							#set($organisationunitid=$col)
+						#elseif($velocityCount==$row.size())
+							#set($value=$col)
 						#end
-					</tbody>
-				</table>
+						#set($item=$dataelementid + '_' + $categoryoptioncomboid + '_' + $periodid + '_' + $organisationunitid + '_' + $value)
+					#end
+					<td>
+						<input type='checkbox' name='aggregateValues' value='$item'>
+					</td>
+					
+					#foreach( $col in $row )
+						#if($velocityCount>6) <td>$col</td> #end
+					#end
+				</tr>
 			#end
-		#end
+		</tbody>
+	</table>
 	#end
-	
-	<h2>$i18n.getString('no_aggregate_date_values_are_added_or_update_in_other_facilities')</h2>
-	<div id='contentDetails'></div>
-#else
+#end
+
+#if($hashValues==false)
 	<input type="button" value="$i18n.getString( 'back' )" onclick="backBtnOnClick();" style="width:10em">
 	<h2>$i18n.getString('no_aggregate_date_values_are_added_or_update_in_all_facilities')</h2>
 #end

=== modified file 'dhis-2/dhis-web/dhis-web-caseentry/src/main/webapp/dhis-web-caseentry/javascript/caseagg.js'
--- dhis-2/dhis-web/dhis-web-caseentry/src/main/webapp/dhis-web-caseentry/javascript/caseagg.js	2013-03-11 13:09:11 +0000
+++ dhis-2/dhis-web/dhis-web-caseentry/src/main/webapp/dhis-web-caseentry/javascript/caseagg.js	2013-05-02 03:31:45 +0000
@@ -100,7 +100,7 @@
 
 function toggleResult( id )
 {
-	$( "#" + id + "-div" ).slideToggle( "fast" );
+	$( "#div-" + id ).slideToggle( "fast" );
 }
 
 function saveAggregateDataValues( isSaveAll )
@@ -147,5 +147,5 @@
 
 function toogleAllCheckBoxes( tableDiv, checked )
 {
-	jQuery("#" + tableDiv + " input[name=aggregateValues]").attr( 'checked', checked );
+	jQuery("#div-" + tableDiv + " input[name=aggregateValues]").attr( 'checked', checked );
 }
\ No newline at end of file

=== modified file 'dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-patient/src/main/webapp/dhis-web-maintenance-patient/caseAggregationForm.vm'
--- dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-patient/src/main/webapp/dhis-web-maintenance-patient/caseAggregationForm.vm	2013-04-29 12:08:00 +0000
+++ dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-patient/src/main/webapp/dhis-web-maintenance-patient/caseAggregationForm.vm	2013-05-02 03:31:45 +0000
@@ -96,7 +96,7 @@
 			<input type='button' class="small-button" alt="$i18n.getString( 'equal' )" onclick='insertOperator( "=" );' value="=" />
 			<input type='button' class="small-button" alt="$i18n.getString( 'diff' )" onclick='insertOperator( "!=" );' value="!=" />
 			&nbsp;&nbsp;&nbsp;
-			<input type='button' class="small-button" alt="$i18n.getString( 'plus' )" onclick='insertOperator( "+" );' value="+" />
+			<!-- input type='button' class="small-button" alt="$i18n.getString( 'plus' )" onclick='insertOperator( "+" );' value="+" / -->
 			<input type='button' class="small-button" alt="$i18n.getString( 'left_parent' )" onclick='insertOperator( "(" );' value="(" />
 			<input type='button' class="small-button" alt="$i18n.getString( 'right_parent' )" onclick='insertOperator( ")" );' value=")" />
 			&nbsp;&nbsp;&nbsp;
@@ -107,7 +107,7 @@
 			&nbsp;&nbsp;&nbsp;
 			<input type='button' style="width:45px;" alt="$i18n.getString( 'and' )" onclick='insertOperator( "AND" );' value="AND" />
 			<input type='button' style="width:45px;" alt="$i18n.getString( 'or' )" onclick='insertOperator( "OR" );' value="OR" />
-			<input type='button' style="width:45px;" alt="$i18n.getString( 'combine' )" onclick='insertOperator( "COMBINE" );' value="COMBINE" />
+			<input type='button' style="width:85px;" alt="$i18n.getString( 'combine' )" onclick='insertOperator( "COMBINE" );' value="COMBINE" />
 			&nbsp;&nbsp;&nbsp;
 			<input type='button' style="width:45px;" align="right" alt="$i18n.getString( 'clear' )" onclick="byId('aggregationCondition').value='';" value="$i18n.getString('clear')" />
 		</td>