← Back to team overview

dhis2-devs team mailing list archive

[Branch ~dhis2-devs-core/dhis2/trunk] Rev 14679: Event sql query, using SqlHelper

 

------------------------------------------------------------
revno: 14679
committer: Lars Helge Øverland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Fri 2014-04-04 23:19:37 +0200
message:
  Event sql query, using SqlHelper
modified:
  dhis-2/dhis-services/dhis-service-dxf2/src/main/java/org/hisp/dhis/dxf2/events/event/DefaultEventStore.java


--
lp:dhis2
https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk

Your team DHIS 2 developers is subscribed to branch lp:dhis2.
To unsubscribe from this branch go to https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk/+edit-subscription
=== modified file 'dhis-2/dhis-services/dhis-service-dxf2/src/main/java/org/hisp/dhis/dxf2/events/event/DefaultEventStore.java'
--- dhis-2/dhis-services/dhis-service-dxf2/src/main/java/org/hisp/dhis/dxf2/events/event/DefaultEventStore.java	2014-04-04 20:46:36 +0000
+++ dhis-2/dhis-services/dhis-service-dxf2/src/main/java/org/hisp/dhis/dxf2/events/event/DefaultEventStore.java	2014-04-04 21:19:37 +0000
@@ -28,28 +28,29 @@
  * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  */
 
-import com.fasterxml.jackson.core.type.TypeReference;
-import com.fasterxml.jackson.databind.ObjectMapper;
+import static org.hisp.dhis.common.IdentifiableObjectUtils.getIdList;
+import static org.hisp.dhis.system.util.DateUtils.getMediumDateString;
+import static org.hisp.dhis.system.util.TextUtils.getCommaDelimitedString;
+
+import java.io.IOException;
+import java.util.ArrayList;
+import java.util.Arrays;
+import java.util.Date;
+import java.util.List;
 
 import org.hisp.dhis.dxf2.events.trackedentity.TrackedEntityInstance;
 import org.hisp.dhis.organisationunit.OrganisationUnit;
 import org.hisp.dhis.program.Program;
 import org.hisp.dhis.program.ProgramStage;
-import org.hisp.dhis.system.util.TextUtils;
+import org.hisp.dhis.system.util.SqlHelper;
 import org.hisp.dhis.trackedentity.TrackedEntityInstanceService;
 import org.springframework.beans.factory.annotation.Autowired;
 import org.springframework.jdbc.core.JdbcTemplate;
 import org.springframework.jdbc.support.rowset.SqlRowSet;
 import org.springframework.util.StringUtils;
 
-import java.io.IOException;
-import java.util.ArrayList;
-import java.util.Arrays;
-import java.util.Date;
-import java.util.List;
-
-import static org.hisp.dhis.common.IdentifiableObjectUtils.getIdList;
-import static org.hisp.dhis.system.util.DateUtils.getMediumDateString;
+import com.fasterxml.jackson.core.type.TypeReference;
+import com.fasterxml.jackson.databind.ObjectMapper;
 
 /**
  * @author Morten Olav Hansen <mortenoh@xxxxxxxxx>
@@ -172,6 +173,8 @@
     private String buildSql( List<Integer> programIds, List<Integer> programStageIds, List<Integer> orgUnitIds,
         Integer trackedEntityInstanceId, Date startDate, Date endDate )
     {
+        SqlHelper hlp = new SqlHelper();
+        
         String sql = "select p.uid as p_uid, ps.uid as ps_uid, ps.capturecoordinates as ps_capturecoordinates, pa.uid as pa_uid, psi.uid as psi_uid, psi.status as psi_status, ou.uid as ou_uid, "
             + "psi.executiondate as psi_executiondate, psi.completeduser as psi_completeduser, psi.longitude as psi_longitude, psi.latitude as psi_latitude,"
             + " pdv.value as pdv_value, pdv.storedby as pdv_storedby, pdv.providedelsewhere as pdv_providedelsewhere, de.uid as de_uid"
@@ -184,58 +187,24 @@
             + " left join dataelement de on pdv.dataelementid=de.dataelementid "
             + " left join trackedentityinstance pa on pa.trackedentityinstanceid=pi.trackedentityinstanceid ";
 
-        boolean startedWhere = false;
-
         if ( trackedEntityInstanceId != null )
         {
-            if ( startedWhere )
-            {
-                sql += " and pa.trackedentityinstanceid=" + trackedEntityInstanceId;
-            }
-            else
-            {
-                sql += " where pa.trackedentityinstanceid=" + trackedEntityInstanceId;
-                startedWhere = true;
-            }
+            sql += hlp.whereAnd() + " pa.trackedentityinstanceid=" + trackedEntityInstanceId + " ";
         }
 
         if ( !programIds.isEmpty() )
         {
-            if ( startedWhere )
-            {
-                sql += " and p.programid in (" + TextUtils.getCommaDelimitedString( programIds ) + ") ";
-            }
-            else
-            {
-                sql += " where p.programid in (" + TextUtils.getCommaDelimitedString( programIds ) + ") ";
-                startedWhere = true;
-            }
+            sql += hlp.whereAnd() + " p.programid in (" + getCommaDelimitedString( programIds ) + ") ";
         }
 
         if ( !programStageIds.isEmpty() )
         {
-            if ( startedWhere )
-            {
-                sql += " and ps.programstageid in (" + TextUtils.getCommaDelimitedString( programStageIds ) + ") ";
-            }
-            else
-            {
-                sql += " where ps.programstageid in (" + TextUtils.getCommaDelimitedString( programStageIds ) + ") ";
-                startedWhere = true;
-            }
+            sql += hlp.whereAnd() + " ps.programstageid in (" + getCommaDelimitedString( programStageIds ) + ") ";
         }
 
         if ( !orgUnitIds.isEmpty() )
         {
-            if ( startedWhere )
-            {
-                sql += " and ou.organisationunitid in (" + TextUtils.getCommaDelimitedString( orgUnitIds ) + ") ";
-            }
-            else
-            {
-                sql += " where ou.organisationunitid in (" + TextUtils.getCommaDelimitedString( orgUnitIds ) + ") ";
-                startedWhere = true;
-            }
+            sql += hlp.whereAnd() + " ou.organisationunitid in (" + getCommaDelimitedString( orgUnitIds ) + ") ";
         }
 
         if ( startDate != null )