dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #29152
[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 )