dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #26279
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 13022: Replace sql query in PatientStore to hql
------------------------------------------------------------
revno: 13022
committer: Tran Chau <tran.hispvietnam@xxxxxxxxx>
branch nick: dhis2
timestamp: Tue 2013-11-26 14:47:15 +0700
message:
Replace sql query in PatientStore to hql
modified:
dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/patient/hibernate/HibernatePatientStore.java
dhis-2/dhis-services/dhis-service-patient/src/test/java/org/hisp/dhis/patient/PatientStoreTest.java
--
lp:dhis2
https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk
Your team DHIS 2 developers is subscribed to branch lp:dhis2.
To unsubscribe from this branch go to https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk/+edit-subscription
=== modified file 'dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/patient/hibernate/HibernatePatientStore.java'
--- dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/patient/hibernate/HibernatePatientStore.java 2013-11-12 08:07:20 +0000
+++ dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/patient/hibernate/HibernatePatientStore.java 2013-11-26 07:47:15 +0000
@@ -39,23 +39,28 @@
import static org.hisp.dhis.patient.Patient.PREFIX_PROGRAM_INSTANCE;
import static org.hisp.dhis.patient.Patient.PREFIX_PROGRAM_STAGE;
-import java.sql.ResultSet;
-import java.sql.SQLException;
-import java.util.ArrayList;
+import java.lang.reflect.Field;
+import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
-import org.apache.commons.logging.Log;
-import org.apache.commons.logging.LogFactory;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.criterion.Conjunction;
import org.hibernate.criterion.Disjunction;
import org.hibernate.criterion.Order;
+import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
+import org.hibernate.engine.spi.LoadQueryInfluencers;
+import org.hibernate.engine.spi.SessionFactoryImplementor;
+import org.hibernate.internal.CriteriaImpl;
+import org.hibernate.internal.SessionImpl;
+import org.hibernate.loader.OuterJoinLoader;
+import org.hibernate.loader.criteria.CriteriaLoader;
+import org.hibernate.persister.entity.OuterJoinLoadable;
import org.hisp.dhis.common.Grid;
import org.hisp.dhis.common.hibernate.HibernateIdentifiableObjectStore;
import org.hisp.dhis.organisationunit.OrganisationUnit;
@@ -67,13 +72,13 @@
import org.hisp.dhis.patient.PatientService;
import org.hisp.dhis.patient.PatientStore;
import org.hisp.dhis.period.Period;
+import org.hisp.dhis.period.PeriodType;
import org.hisp.dhis.program.Program;
import org.hisp.dhis.program.ProgramInstance;
import org.hisp.dhis.program.ProgramStageInstance;
import org.hisp.dhis.system.grid.GridUtils;
-import org.hisp.dhis.system.util.TextUtils;
+import org.hisp.dhis.system.util.DateUtils;
import org.hisp.dhis.validation.ValidationCriteria;
-import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.transaction.annotation.Transactional;
@@ -85,8 +90,6 @@
extends HibernateIdentifiableObjectStore<Patient>
implements PatientStore
{
- private static final Log log = LogFactory.getLog( HibernatePatientStore.class );
-
// -------------------------------------------------------------------------
// Dependencies
// -------------------------------------------------------------------------
@@ -101,7 +104,7 @@
// -------------------------------------------------------------------------
// Implementation methods
// -------------------------------------------------------------------------
-
+
@Override
public Collection<Patient> getByNames( String fullName, Integer min, Integer max )
{
@@ -221,11 +224,16 @@
@Override
public int countGetPatientsByOrgUnitProgram( OrganisationUnit organisationUnit, Program program )
{
- String sql = "select count(p.patientid) from patient p join programinstance pi on p.patientid=pi.patientid "
- + "where p.organisationunitid=" + organisationUnit.getId() + " and pi.programid=" + program.getId()
- + " and pi.status=" + ProgramInstance.STATUS_ACTIVE;
-
- return jdbcTemplate.queryForObject( sql, Integer.class );
+ Criteria criteria = getCriteria();
+ criteria.add( Restrictions.eq( "organisationUnit", organisationUnit ) );
+ criteria.createAlias( "programInstances", "programInstance" );
+ criteria.createAlias( "programInstance.program", "program" );
+ criteria.add( Restrictions.eq( "program.id", program.getId() ) );
+ criteria.add( Restrictions.eq( "programInstance.status", ProgramInstance.STATUS_ACTIVE ) );
+
+ Number rs = (Number) criteria.setProjection( Projections.rowCount() ).uniqueResult();
+
+ return rs != null ? rs.intValue() : 0;
}
@Override
@@ -238,65 +246,39 @@
}
@Override
- // TODO this method must be changed - cannot retrieve one by one
+ @SuppressWarnings( "unchecked" )
public Collection<Patient> search( List<String> searchKeys, Collection<OrganisationUnit> orgunits,
Boolean followup, Collection<PatientAttribute> patientAttributes,
Collection<PatientIdentifierType> identifierTypes, Integer statusEnrollment, Integer min, Integer max )
{
- String sql = searchPatientSql( false, searchKeys, orgunits, followup, patientAttributes, identifierTypes,
- statusEnrollment, min, max );
- Collection<Patient> patients = new HashSet<Patient>();
- try
- {
- patients = jdbcTemplate.query( sql, new RowMapper<Patient>()
- {
- public Patient mapRow( ResultSet rs, int rowNum )
- throws SQLException
- {
- return get( rs.getInt( 1 ) );
- }
- } );
- }
- catch ( Exception ex )
- {
- ex.printStackTrace();
- }
- return patients;
+ Criteria criteria = searchPatientCriteria( false, searchKeys, orgunits, followup, patientAttributes,
+ identifierTypes, statusEnrollment, min, max );
+
+ return criteria.list();
}
@Override
+ @SuppressWarnings( "unchecked" )
public List<Integer> getProgramStageInstances( List<String> searchKeys, Collection<OrganisationUnit> orgunits,
Boolean followup, Collection<PatientAttribute> patientAttributes,
Collection<PatientIdentifierType> identifierTypes, Integer statusEnrollment, Integer min, Integer max )
{
- String sql = searchPatientSql( false, searchKeys, orgunits, followup, patientAttributes, identifierTypes,
- statusEnrollment, min, max );
-
- List<Integer> programStageInstanceIds = new ArrayList<Integer>();
- try
- {
- programStageInstanceIds = jdbcTemplate.query( sql, new RowMapper<Integer>()
- {
- public Integer mapRow( ResultSet rs, int rowNum )
- throws SQLException
- {
- return rs.getInt( "programstageinstanceid" );
- }
- } );
- }
- catch ( Exception ex )
- {
- ex.printStackTrace();
- }
-
- return programStageInstanceIds;
+ Criteria criteria = searchPatientCriteria( false, searchKeys, orgunits, followup, patientAttributes,
+ identifierTypes, statusEnrollment, min, max );
+ criteria.setProjection( Projections.property( "programStageInstance" ) );
+
+ return criteria.list();
}
public int countSearch( List<String> searchKeys, Collection<OrganisationUnit> orgunits, Boolean followup,
Integer statusEnrollment )
{
- String sql = searchPatientSql( true, searchKeys, orgunits, followup, null, null, statusEnrollment, null, null );
- return jdbcTemplate.queryForObject( sql, Integer.class );
+ Criteria criteria = searchPatientCriteria( true, searchKeys, orgunits, followup, null, null, statusEnrollment,
+ null, null );
+
+ Number rs = (Number) criteria.setProjection( Projections.rowCount() ).uniqueResult();
+
+ return rs != null ? rs.intValue() : 0;
}
@Override
@@ -304,12 +286,40 @@
Boolean followup, Collection<PatientAttribute> patientAttributes,
Collection<PatientIdentifierType> identifierTypes, Integer statusEnrollment, Integer min, Integer max )
{
- String sql = searchPatientSql( false, searchKeys, orgunits, followup, patientAttributes, identifierTypes,
- statusEnrollment, min, max );
-
- SqlRowSet rowSet = jdbcTemplate.queryForRowSet( sql );
-
- GridUtils.addRows( grid, rowSet );
+
+ Criteria criteria = searchPatientCriteria( false, searchKeys, orgunits, followup, patientAttributes,
+ identifierTypes, statusEnrollment, min, max );
+
+ ProjectionList proList = Projections.projectionList();
+ proList.add( Projections.property( "registrationDate" ) );
+ proList.add( Projections.property( "name" ) );
+ proList.add( Projections.property( "birthDate" ) );
+ proList.add( Projections.property( "phoneNumber" ) );
+ proList.add( Projections.property( "attributeValue.patientAttribute.name" ) );
+ criteria.setProjection( proList );
+
+ // Convert HQL to SQL
+ try
+ {
+ CriteriaImpl c = (CriteriaImpl) criteria;
+ SessionImpl s = (SessionImpl) c.getSession();
+ SessionFactoryImplementor factory = (SessionFactoryImplementor) s.getSessionFactory();
+ String[] implementors = factory.getImplementors( c.getEntityOrClassName() );
+ LoadQueryInfluencers lqis = new LoadQueryInfluencers();
+ CriteriaLoader loader = new CriteriaLoader(
+ (OuterJoinLoadable) factory.getEntityPersister( implementors[0] ), factory, c, implementors[0], lqis );
+ Field f = OuterJoinLoader.class.getDeclaredField( "sql" );
+ f.setAccessible( true );
+ String sql = (String) f.get( loader );
+
+ SqlRowSet rowSet = jdbcTemplate.queryForRowSet( sql );
+
+ GridUtils.addRows( grid, rowSet );
+ }
+ catch ( Exception ex )
+ {
+ ex.printStackTrace();
+ }
return grid;
}
@@ -318,15 +328,13 @@
@SuppressWarnings( "unchecked" )
public Collection<Patient> getByPhoneNumber( String phoneNumber, Integer min, Integer max )
{
- String hql = "select p from Patient p where p.phoneNumber like '%" + phoneNumber + "%'";
- Query query = getQuery( hql );
-
+ Criteria criteria = getCriteria( Restrictions.ilike( "phoneNumber", phoneNumber ) );
if ( min != null && max != null )
{
- query.setFirstResult( min ).setMaxResults( max );
+ criteria.setFirstResult( min ).setMaxResults( max );
}
- return query.list();
+ return criteria.list();
}
@Override
@@ -408,7 +416,7 @@
criteria.add( disjunction );
Number rs = (Number) criteria.setProjection( Projections.rowCount() ).uniqueResult();
-
+
if ( rs != null && rs.intValue() > 0 )
{
return PatientService.ERROR_DUPLICATE_IDENTIFIER;
@@ -432,41 +440,17 @@
// Supportive methods TODO Remplement all this!
// -------------------------------------------------------------------------
- private String searchPatientSql( boolean count, List<String> searchKeys, Collection<OrganisationUnit> orgunits,
- Boolean followup, Collection<PatientAttribute> patientAttributes,
+ private Criteria searchPatientCriteria( boolean count, List<String> searchKeys,
+ Collection<OrganisationUnit> orgunits, Boolean followup, Collection<PatientAttribute> patientAttributes,
Collection<PatientIdentifierType> identifierTypes, Integer statusEnrollment, Integer min, Integer max )
{
- String selector = count ? "count(*) " : "* ";
- String sql = "select " + selector + " from ( select distinct p.patientid, p.name, p.gender, p.phonenumber,";
-
- if ( identifierTypes != null )
- {
- for ( PatientIdentifierType identifierType : identifierTypes )
- {
- sql += "(select identifier from patientidentifier where patientid=p.patientid and patientidentifiertypeid="
- + identifierType.getId() + " ) as " + PREFIX_IDENTIFIER_TYPE + "_" + identifierType.getId() + " ,";
- }
- }
-
- if ( patientAttributes != null )
- {
- for ( PatientAttribute patientAttribute : patientAttributes )
- {
- sql += "(select value from patientattributevalue where patientid=p.patientid and patientattributeid="
- + patientAttribute.getId() + " ) as " + PREFIX_PATIENT_ATTRIBUTE + "_" + patientAttribute.getId()
- + " ,";
- }
- }
-
- String patientWhere = "";
- String patientOperator = " where ";
- String patientGroupBy = " GROUP BY p.patientid, p.name, p.gender, p.phonenumber ";
- String otherWhere = "";
- String operator = " where ";
- String orderBy = "";
- boolean hasIdentifier = false;
+ Criteria criteria = getCriteria();
+ criteria.createAlias( "identifiers", "patientIdentifier" );
+ criteria.createAlias( "organisationUnit", "orgunit" );
+
boolean isSearchEvent = false;
- boolean isPriorityEvent = false;
+ boolean searchAttr = false;
+ boolean searchProgram = false;
Collection<Integer> orgunitChilrenIds = null;
if ( orgunits != null )
@@ -493,321 +477,288 @@
if ( keys[0].equals( PREFIX_FIXED_ATTRIBUTE ) )
{
- patientWhere += patientOperator;
-
if ( id.equals( FIXED_ATTR_BIRTH_DATE ) )
{
- patientWhere += " p." + id + value;
+ criteria.add( Restrictions.eq( id, Integer.parseInt( value ) ) );
}
else if ( id.equals( FIXED_ATTR_AGE ) )
{
- patientWhere += " ((DATE(now()) - DATE(birthdate))/365) " + value;
+ Calendar c = Calendar.getInstance();
+ PeriodType.clearTimeOfDay( c );
+ c.add( Calendar.YEAR, -1 * Integer.parseInt( value ) );
+ criteria.add( Restrictions.eq( "birthdate", c.getTime() ) );
}
else if ( id.equals( FIXED_ATTR_REGISTRATION_DATE ) )
{
- patientWhere += "p." + id + value;
+ Calendar c = Calendar.getInstance();
+ PeriodType.clearTimeOfDay( c );
+ c.add( Calendar.YEAR, -1 * Integer.parseInt( value ) );
+ criteria.add( Restrictions.eq( "registrationDate", c.getTime() ) );
}
else
{
- patientWhere += " lower(p." + id + ")='" + value + "'";
+ criteria.add( Restrictions.ilike( id, "%" + value + "%" ) );
}
- patientOperator = " and ";
}
else if ( keys[0].equals( PREFIX_IDENTIFIER_TYPE ) )
{
-
String[] keyValues = id.split( " " );
- patientWhere += patientOperator + " (";
- String opt = "";
+ Disjunction disjunction = Restrictions.disjunction();
for ( String v : keyValues )
{
- patientWhere += opt + " lower( p.name ) like '%" + v + "%' or ( lower(pi.identifier) like '%" + v
- + "%' and pi.patientidentifiertypeid is not null ) ";
- opt = "or";
+ disjunction.add( Restrictions.ilike( "name", "%" + v + "%" ) );
+
+ Conjunction conjunction = Restrictions.conjunction();
+ conjunction.add( Restrictions.ilike( "patientIdentifier.identifier", "%" + v + "%" ) );
+ conjunction.add( Restrictions.isNotNull( "patientIdentifier.identifierType" ) );
+
+ disjunction.add( conjunction );
}
-
- patientWhere += ")";
- patientOperator = " and ";
- hasIdentifier = true;
+ criteria.add( disjunction );
}
else if ( keys[0].equals( PREFIX_PATIENT_ATTRIBUTE ) )
{
- sql += "(select value from patientattributevalue where patientid=p.patientid and patientattributeid="
- + id + " ) as " + PREFIX_PATIENT_ATTRIBUTE + "_" + id + ",";
+ if ( !searchAttr )
+ {
+ criteria.createAlias( "attributeValues", "attributeValue" );
+ searchAttr = true;
+ }
String[] keyValues = value.split( " " );
- otherWhere += operator + "(";
- String opt = "";
-
+ Conjunction conjunction = Restrictions.conjunction();
for ( String v : keyValues )
{
- otherWhere += opt + " lower(" + PREFIX_PATIENT_ATTRIBUTE + "_" + id + ") like '%" + v + "%'";
- opt = "or";
+ conjunction.add( Restrictions.eq( "attributeValue.patientAttribute.id", Integer.parseInt( id ) ) );
+ conjunction.add( Restrictions.ilike( "attributeValue.value", "%" + v + "%" ) );
}
-
- otherWhere += ")";
- operator = " and ";
+ criteria.add( conjunction );
}
else if ( keys[0].equals( PREFIX_PROGRAM ) )
{
- sql += "(select programid from programinstance pi where patientid=p.patientid and programid=" + id;
+ if ( !searchProgram )
+ {
+ criteria.createAlias( "programInstances", "programInstance" );
+ criteria.createAlias( "programInstance.program", "program" );
+ searchProgram = true;
+ }
+
+ criteria.add( Restrictions.eq( "program.id", Integer.parseInt( id ) ) );
if ( statusEnrollment != null )
{
- sql += " and pi.status=" + statusEnrollment;
+ criteria.add( Restrictions.eq( "programInstance.status", statusEnrollment ) );
}
-
- sql += " limit 1 ) as " + PREFIX_PROGRAM + "_" + id + ",";
- otherWhere += operator + PREFIX_PROGRAM + "_" + id + "=" + id;
- operator = " and ";
}
else if ( keys[0].equals( PREFIX_PROGRAM_INSTANCE ) )
{
- sql += "(select pi." + id + " from programinstance pi where patientid=p.patientid and pi.status=0 ";
+ if ( !searchProgram )
+ {
+ criteria.createAlias( "programInstances", "programInstance" );
+ criteria.createAlias( "programInstance.program", "program" );
+ searchProgram = true;
+ }
+
+ criteria.add( Restrictions.eq( "programInstance.status", statusEnrollment ) );
if ( keys.length == 5 )
{
- sql += " and pi.programid=" + keys[4];
- }
- else
- {
- sql += " limit 1 ";
- }
-
- sql += ") as " + PREFIX_PROGRAM_INSTANCE + "_" + id + ",";
- otherWhere += operator + PREFIX_PROGRAM_INSTANCE + "_" + id + keys[2];
- operator = " and ";
+ criteria.add( Restrictions.eq( "program.id", Integer.parseInt( keys[4] ) ) );
+ }
}
else if ( keys[0].equals( PREFIX_PROGRAM_EVENT_BY_STATUS ) )
{
- isSearchEvent = true;
- isPriorityEvent = Boolean.parseBoolean( keys[5] );
- patientWhere += patientOperator + "pgi.patientid=p.patientid and ";
- patientWhere += "pgi.programid=" + id + " and ";
- patientWhere += "pgi.status=" + ProgramInstance.STATUS_ACTIVE;
+ if ( !searchProgram )
+ {
+ criteria.createAlias( "programInstances", "programInstance" );
+ criteria.createAlias( "programInstance.program", "program" );
+ searchProgram = true;
+ }
- String operatorStatus = "";
- String condition = " and ( ";
+ criteria.add( Restrictions.eq( "program.id", Integer.parseInt( id ) ) );
+ criteria.add( Restrictions.eq( "programInstance.status", ProgramInstance.STATUS_ACTIVE ) );
for ( int index = 6; index < keys.length; index++ )
{
int statusEvent = Integer.parseInt( keys[index] );
+
+ Calendar c = Calendar.getInstance();
+ PeriodType.clearTimeOfDay( c );
+
switch ( statusEvent )
{
case ProgramStageInstance.COMPLETED_STATUS:
- patientWhere += condition + operatorStatus
- + "( psi.executiondate is not null and psi.executiondate>='" + keys[2]
- + "' and psi.executiondate<='" + keys[3] + "' and psi.completed=true ";
+ criteria.add( Restrictions.isNotNull( "programStageInstance.executiondate" ) );
+ criteria.add( Restrictions.between( "programStageInstance.executiondate",
+ DateUtils.getDefaultDate( keys[2] ), DateUtils.getDefaultDate( keys[3] ) ) );
+ criteria.add( Restrictions.eq( "programStageInstance.completed", true ) );
// get events by orgunit children
if ( keys[4].equals( "-1" ) )
{
- patientWhere += " and psi.organisationunitid in( "
- + TextUtils.getCommaDelimitedString( orgunitChilrenIds ) + " )";
+ criteria.add( Restrictions.in( "eventOrg.id", orgunitChilrenIds ) );
}
// get events by selected orgunit
else if ( !keys[4].equals( "0" ) )
{
- patientWhere += " and psi.organisationunitid=" + keys[4];
+ criteria.add( Restrictions.eq( "eventOrg.id", Integer.parseInt( keys[4] ) ) );
}
-
- patientWhere += ")";
- operatorStatus = " OR ";
- condition = "";
continue;
case ProgramStageInstance.VISITED_STATUS:
- patientWhere += condition + operatorStatus
- + "( psi.executiondate is not null and psi.executiondate>='" + keys[2]
- + "' and psi.executiondate<='" + keys[3] + "' and psi.completed=false ";
+ criteria.add( Restrictions.isNotNull( "programStageInstance.executiondate" ) );
+ criteria.add( Restrictions.between( "programStageInstance.executiondate",
+ DateUtils.getDefaultDate( keys[2] ), DateUtils.getDefaultDate( keys[3] ) ) );
+ criteria.add( Restrictions.eq( "programStageInstance.completed", false ) );
// get events by orgunit children
if ( keys[4].equals( "-1" ) )
{
- patientWhere += " and psi.organisationunitid in( "
- + TextUtils.getCommaDelimitedString( orgunitChilrenIds ) + " )";
+ criteria.add( Restrictions.in( "eventOrg.id", orgunitChilrenIds ) );
}
// get events by selected orgunit
else if ( !keys[4].equals( "0" ) )
{
- patientWhere += " and psi.organisationunitid=" + keys[4];
+ criteria.add( Restrictions.eq( "eventOrg.id", Integer.parseInt( keys[4] ) ) );
}
-
- patientWhere += ")";
- operatorStatus = " OR ";
- condition = "";
continue;
case ProgramStageInstance.FUTURE_VISIT_STATUS:
- patientWhere += condition + operatorStatus + "( psi.executiondate is null and psi.duedate>='"
- + keys[2] + "' and psi.duedate<='" + keys[3]
- + "' and psi.status is not null and (DATE(now()) - DATE(psi.duedate) <= 0) ";
+ criteria.add( Restrictions.isNull( "programStageInstance.executiondate" ) );
+ criteria.add( Restrictions.between( "programStageInstance.duedate",
+ DateUtils.getDefaultDate( keys[2] ), DateUtils.getDefaultDate( keys[3] ) ) );
+ criteria.add( Restrictions.eq( "programStageInstance.status",
+ ProgramStageInstance.ACTIVE_STATUS ) );
+ criteria.add( Restrictions.le( "duedate", c.getTime() ) );
// get events by orgunit children
if ( keys[4].equals( "-1" ) )
{
- patientWhere += " and p.organisationunitid in( "
- + TextUtils.getCommaDelimitedString( orgunitChilrenIds ) + " )";
+ criteria.add( Restrictions.in( "eventOrg.id", orgunitChilrenIds ) );
}
// get events by selected orgunit
else if ( !keys[4].equals( "0" ) )
{
- patientWhere += " and p.organisationunitid=" + keys[4];
+ criteria.add( Restrictions.eq( "eventOrg.id", Integer.parseInt( keys[4] ) ) );
}
-
- patientWhere += ")";
- operatorStatus = " OR ";
- condition = "";
continue;
case ProgramStageInstance.LATE_VISIT_STATUS:
- patientWhere += condition + operatorStatus + "( psi.executiondate is null and psi.duedate>='"
- + keys[2] + "' and psi.duedate<='" + keys[3]
- + "' and psi.status is not null and (DATE(now()) - DATE(psi.duedate) > 0) ";
+ criteria.add( Restrictions.isNull( "programStageInstance.executiondate" ) );
+ criteria.add( Restrictions.between( "programStageInstance.duedate",
+ DateUtils.getDefaultDate( keys[2] ), DateUtils.getDefaultDate( keys[3] ) ) );
+ criteria.add( Restrictions.eq( "programStageInstance.completed", false ) );
+ criteria.add( Restrictions.le( "duedate", c.getTime() ) );
// get events by orgunit children
if ( keys[4].equals( "-1" ) )
{
- patientWhere += " and p.organisationunitid in( "
- + TextUtils.getCommaDelimitedString( orgunitChilrenIds ) + " )";
+ criteria.add( Restrictions.in( "eventOrg.id", orgunitChilrenIds ) );
}
// get events by selected orgunit
else if ( !keys[4].equals( "0" ) )
{
- patientWhere += " and p.organisationunitid=" + keys[4];
+ criteria.add( Restrictions.eq( "eventOrg.id", Integer.parseInt( keys[4] ) ) );
}
-
- patientWhere += ")";
- operatorStatus = " OR ";
- condition = "";
continue;
case ProgramStageInstance.SKIPPED_STATUS:
- patientWhere += condition + operatorStatus + "( psi.status=5 and psi.duedate>='" + keys[2]
- + "' and psi.duedate<='" + keys[3] + "' ";
+ criteria.add( Restrictions.between( "programStageInstance.duedate",
+ DateUtils.getDefaultDate( keys[2] ), DateUtils.getDefaultDate( keys[3] ) ) );
+ criteria.add( Restrictions.eq( "programStageInstance.status",
+ ProgramStageInstance.SKIPPED_STATUS ) );
// get events by orgunit children
if ( keys[4].equals( "-1" ) )
{
- patientWhere += " and psi.organisationunitid in( "
- + TextUtils.getCommaDelimitedString( orgunitChilrenIds ) + " )";
+ criteria.add( Restrictions.in( "eventOrg.id", orgunitChilrenIds ) );
}
// get events by selected orgunit
else if ( !keys[4].equals( "0" ) )
{
- patientWhere += " and p.organisationunitid=" + keys[4];
+ criteria.add( Restrictions.eq( "eventOrg.id", Integer.parseInt( keys[4] ) ) );
}
- patientWhere += ")";
- operatorStatus = " OR ";
- condition = "";
continue;
default:
continue;
}
}
- if ( condition.isEmpty() )
- {
- patientWhere += ")";
- }
- patientWhere += " and pgi.status=" + ProgramInstance.STATUS_ACTIVE + " ";
- patientOperator = " and ";
+ criteria.add( Restrictions.eq( "programInstance.status", ProgramInstance.STATUS_ACTIVE ) );
}
else if ( keys[0].equals( PREFIX_PROGRAM_STAGE ) )
{
- isSearchEvent = true;
- patientWhere += patientOperator + "pgi.patientid=p.patientid and psi.programstageid=" + id + " and ";
- patientWhere += "psi.duedate>='" + keys[3] + "' and psi.duedate<='" + keys[4] + "' and ";
- patientWhere += "psi.organisationunitid = " + keys[5] + " and ";
+
+ if ( !searchProgram )
+ {
+ criteria.createAlias( "programInstances", "programInstance" );
+ criteria.createAlias( "programInstance.program", "program" );
+ searchProgram = true;
+ }
+
+ if ( !isSearchEvent )
+ {
+ criteria.createAlias( "programInstance.programStageInstances", "programStageInstance" );
+ criteria.createAlias( "programStageInstance.organisationUnit", "eventOrg" );
+ criteria.createAlias( "programStageInstance.programStage", "programStage" );
+ isSearchEvent = true;
+ }
+ criteria.add( Restrictions.eq( "programStage.id", Integer.parseInt( id ) ) );
+ criteria.add( Restrictions.between( "programStageInstance.duedate",
+ DateUtils.getDefaultDate( keys[3] ), DateUtils.getDefaultDate( keys[4] ) ) );
+ criteria.add( Restrictions.eq( "eventOrg.id", Integer.parseInt( keys[5] ) ) );
+
+ Calendar c = Calendar.getInstance();
+ PeriodType.clearTimeOfDay( c );
int statusEvent = Integer.parseInt( keys[2] );
switch ( statusEvent )
{
case ProgramStageInstance.COMPLETED_STATUS:
- patientWhere += "psi.completed=true";
+ criteria.add( Restrictions.eq( "programStageInstance.completed", true ) );
break;
case ProgramStageInstance.VISITED_STATUS:
- patientWhere += "psi.executiondate is not null and psi.completed=false";
+ criteria.add( Restrictions.isNotNull( "programStageInstance.executiondate" ) );
+ criteria.add( Restrictions.eq( "programStageInstance.completed", false ) );
break;
case ProgramStageInstance.FUTURE_VISIT_STATUS:
- patientWhere += "psi.executiondate is null and psi.duedate >= now()";
+ criteria.add( Restrictions.isNull( "programStageInstance.executiondate" ) );
+ criteria.add( Restrictions.ge( "programStageInstance.duedate", c.getTime() ) );
break;
case ProgramStageInstance.LATE_VISIT_STATUS:
- patientWhere += "psi.executiondate is null and psi.duedate < now()";
+ criteria.add( Restrictions.isNull( "programStageInstance.executiondate" ) );
+ criteria.add( Restrictions.le( "programStageInstance.duedate", c.getTime() ) );
break;
default:
break;
}
-
- patientWhere += " and pgi.status=" + ProgramInstance.STATUS_ACTIVE + " ";
- patientOperator = " and ";
+ criteria.add( Restrictions.le( "programInstance.status", ProgramInstance.STATUS_ACTIVE ) );
}
}
if ( orgunits != null && !isSearchEvent )
{
- sql += "(select organisationunitid from patient where patientid=p.patientid and organisationunitid in ( "
- + TextUtils.getCommaDelimitedString( getOrganisationUnitIds( orgunits ) ) + " ) ) as orgunitid,";
- otherWhere += operator + "orgunitid in ( "
- + TextUtils.getCommaDelimitedString( getOrganisationUnitIds( orgunits ) ) + " ) ";
- }
-
- sql = sql.substring( 0, sql.length() - 1 ) + " "; // Removing last comma
-
- String from = " from patient p ";
-
- if ( isSearchEvent )
- {
- String subSQL = " , psi.programstageinstanceid as programstageinstanceid, pgs.name as programstagename, psi.duedate as duedate ";
-
- if ( isPriorityEvent )
- {
- subSQL += ",pgi.followup ";
- orderBy = " ORDER BY pgi.followup desc, p.patientid, p.name, duedate asc ";
- patientGroupBy += ",pgi.followup ";
- }
- else
- {
- orderBy = " ORDER BY p.patientid, p.name, duedate asc ";
- }
-
- sql = sql + subSQL + from + " inner join programinstance pgi on " + " (pgi.patientid=p.patientid) "
- + " inner join programstageinstance psi on (psi.programinstanceid=pgi.programinstanceid) "
- + " inner join programstage pgs on (pgs.programstageid=psi.programstageid) ";
-
- patientGroupBy += ",psi.programstageinstanceid, pgs.name, psi.duedate ";
-
- from = " ";
- }
-
- if ( hasIdentifier )
- {
- sql += from + " left join patientidentifier pi on p.patientid=pi.patientid ";
- from = " ";
- }
-
- sql += from + patientWhere;
+ criteria.add( Restrictions.in( "orgunit.id", getOrganisationUnitIds( orgunits ) ) );
+
+ }
+
if ( followup != null )
{
- sql += " AND pgi.followup=" + followup;
- }
- if ( isSearchEvent )
- {
- sql += patientGroupBy;
- }
- sql += orderBy;
- sql += " ) as searchresult";
- sql += otherWhere;
+ if ( !searchProgram )
+ {
+ criteria.createAlias( "programInstances", "programInstance" );
+ criteria.createAlias( "programInstance.program", "program" );
+ searchProgram = true;
+ }
+ criteria.add( Restrictions.eq( "programInstance.followup", followup ) );
+ }
if ( min != null && max != null )
{
- sql += " limit " + max + " offset " + min;
+ criteria.setFirstResult( min ).setMaxResults( max );
}
- log.info( "Search patient SQL: " + sql );
-
- return sql;
+ return criteria;
}
private Collection<Integer> getOrgunitChildren( Collection<OrganisationUnit> orgunits )
=== modified file 'dhis-2/dhis-services/dhis-service-patient/src/test/java/org/hisp/dhis/patient/PatientStoreTest.java'
--- dhis-2/dhis-services/dhis-service-patient/src/test/java/org/hisp/dhis/patient/PatientStoreTest.java 2013-11-18 11:50:20 +0000
+++ dhis-2/dhis-services/dhis-service-patient/src/test/java/org/hisp/dhis/patient/PatientStoreTest.java 2013-11-26 07:47:15 +0000
@@ -102,33 +102,35 @@
private Program programB;
- private OrganisationUnit organisationUnit;
+ private OrganisationUnit organisationUnitA;
+
+ private OrganisationUnit organisationUnitB;
private Date date = new Date();
@Override
public void setUpTest()
{
- organisationUnit = createOrganisationUnit( 'A' );
- organisationUnitService.addOrganisationUnit( organisationUnit );
+ organisationUnitA = createOrganisationUnit( 'A' );
+ organisationUnitService.addOrganisationUnit( organisationUnitA );
- OrganisationUnit organisationUnitB = createOrganisationUnit( 'B' );
+ organisationUnitB = createOrganisationUnit( 'B' );
organisationUnitService.addOrganisationUnit( organisationUnitB );
-
+
PatientIdentifierType patientIdentifierType = createPatientIdentifierType( 'A' );
identifierTypeService.savePatientIdentifierType( patientIdentifierType );
patientAttribute = createPatientAttribute( 'A' );
attributeId = patientAttributeService.savePatientAttribute( patientAttribute );
- patientA1 = createPatient( 'A', "F", organisationUnit );
+ patientA1 = createPatient( 'A', "F", organisationUnitA );
patientA2 = createPatient( 'A', "F", organisationUnitB );
- patientA3 = createPatient( 'A', organisationUnit, patientIdentifierType );
- patientB1 = createPatient( 'B', "M", organisationUnit );
- patientB2 = createPatient( 'B', organisationUnit );
+ patientA3 = createPatient( 'A', organisationUnitA, patientIdentifierType );
+ patientB1 = createPatient( 'B', "M", organisationUnitA );
+ patientB2 = createPatient( 'B', organisationUnitA );
- programA = createProgram( 'A', new HashSet<ProgramStage>(), organisationUnit );
- programB = createProgram( 'B', new HashSet<ProgramStage>(), organisationUnit );
+ programA = createProgram( 'A', new HashSet<ProgramStage>(), organisationUnitA );
+ programB = createProgram( 'B', new HashSet<ProgramStage>(), organisationUnitA );
}
@Test
@@ -188,12 +190,12 @@
patientStore.save( patientB1 );
patientStore.save( patientB2 );
- Collection<Patient> patients = patientStore.getByFullName( "NameA", organisationUnit );
+ Collection<Patient> patients = patientStore.getByFullName( "NameA", organisationUnitA );
assertEquals( 1, patients.size() );
assertTrue( patients.contains( patientA1 ) );
- patients = patientStore.getByFullName( "NameB", organisationUnit );
+ patients = patientStore.getByFullName( "NameB", organisationUnitA );
assertEquals( 2, patients.size() );
assertTrue( patients.contains( patientB1 ) );
@@ -211,18 +213,18 @@
patientStore.save( patientA2 );
patientStore.save( patientB2 );
- programInstanceService.enrollPatient( patientA1, programA, date, date, organisationUnit, null );
- programInstanceService.enrollPatient( patientB1, programA, date, date, organisationUnit, null );
- programInstanceService.enrollPatient( patientA2, programA, date, date, organisationUnit, null );
- programInstanceService.enrollPatient( patientB2, programB, date, date, organisationUnit, null );
+ programInstanceService.enrollPatient( patientA1, programA, date, date, organisationUnitA, null );
+ programInstanceService.enrollPatient( patientB1, programA, date, date, organisationUnitA, null );
+ programInstanceService.enrollPatient( patientA2, programA, date, date, organisationUnitA, null );
+ programInstanceService.enrollPatient( patientB2, programB, date, date, organisationUnitA, null );
- Collection<Patient> patients = patientStore.getByOrgUnitProgram( organisationUnit, programA, 0, 100 );
+ Collection<Patient> patients = patientStore.getByOrgUnitProgram( organisationUnitA, programA, 0, 100 );
assertEquals( 2, patients.size() );
assertTrue( patients.contains( patientA1 ) );
assertTrue( patients.contains( patientB1 ) );
- patients = patientStore.getByOrgUnitProgram( organisationUnit, programB, 0, 100 );
+ patients = patientStore.getByOrgUnitProgram( organisationUnitA, programB, 0, 100 );
assertEquals( 1, patients.size() );
assertTrue( patients.contains( patientB2 ) );
@@ -239,10 +241,10 @@
patientStore.save( patientA2 );
patientStore.save( patientB2 );
- programInstanceService.enrollPatient( patientA1, programA, date, date, organisationUnit, null );
- programInstanceService.enrollPatient( patientA2, programA, date, date, organisationUnit, null );
- programInstanceService.enrollPatient( patientB1, programA, date, date, organisationUnit, null );
- programInstanceService.enrollPatient( patientB2, programB, date, date, organisationUnit, null );
+ programInstanceService.enrollPatient( patientA1, programA, date, date, organisationUnitA, null );
+ programInstanceService.enrollPatient( patientA2, programA, date, date, organisationUnitA, null );
+ programInstanceService.enrollPatient( patientB1, programA, date, date, organisationUnitA, null );
+ programInstanceService.enrollPatient( patientB2, programB, date, date, organisationUnitA, null );
Collection<Patient> patients = patientStore.getByProgram( programA, 0, 100 );
@@ -251,7 +253,7 @@
assertTrue( patients.contains( patientA2 ) );
assertTrue( patients.contains( patientB1 ) );
- patients = patientStore.getByOrgUnitProgram( organisationUnit, programB, 0, 100 );
+ patients = patientStore.getByOrgUnitProgram( organisationUnitA, programB, 0, 100 );
assertEquals( 1, patients.size() );
assertTrue( patients.contains( patientB2 ) );
@@ -290,7 +292,7 @@
patientStore.save( patientA2 );
patientStore.save( patientA3 );
- Collection<Patient> patients = patientStore.getByOrgUnitAndNameLike( organisationUnit, "A", null, null );
+ Collection<Patient> patients = patientStore.getByOrgUnitAndNameLike( organisationUnitA, "A", null, null );
assertEquals( 2, patients.size() );
assertTrue( patients.contains( patientA1 ) );
assertTrue( patients.contains( patientA3 ) );
@@ -333,18 +335,18 @@
PatientAttributeValue attributeValue = createPatientAttributeValue( 'A', patientA3, patientAttribute );
patientAttributeValueService.savePatientAttributeValue( attributeValue );
- programInstanceService.enrollPatient( patientA3, programA, date, date, organisationUnit, null );
- programInstanceService.enrollPatient( patientB1, programA, date, date, organisationUnit, null );
+ programInstanceService.enrollPatient( patientA3, programA, date, date, organisationUnitA, null );
+ programInstanceService.enrollPatient( patientB1, programA, date, date, organisationUnitA, null );
List<String> searchKeys = new ArrayList<String>();
searchKeys.add( Patient.PREFIX_IDENTIFIER_TYPE + Patient.SEARCH_SAPERATE + "a" + Patient.SEARCH_SAPERATE
- + organisationUnit.getId() );
+ + organisationUnitA.getId() );
searchKeys.add( Patient.PREFIX_PATIENT_ATTRIBUTE + Patient.SEARCH_SAPERATE + attributeId
+ Patient.SEARCH_SAPERATE + "a" );
searchKeys.add( Patient.PREFIX_PROGRAM + Patient.SEARCH_SAPERATE + idA );
Collection<OrganisationUnit> orgunits = new HashSet<OrganisationUnit>();
- orgunits.add( organisationUnit );
+ orgunits.add( organisationUnitA );
Collection<Patient> patients = patientStore.search( searchKeys, orgunits, null, null, null,
ProgramStageInstance.ACTIVE_STATUS, null, null );
@@ -373,4 +375,27 @@
assertEquals( 0, validatePatientA1 );
assertEquals( 2, validatePatientB1 );
}
+
+ @Test
+ public void testCountGetPatientsByOrgUnitProgram()
+ {
+ programService.addProgram( programA );
+ programService.addProgram( programB );
+
+ patientStore.save( patientA1 );
+ patientStore.save( patientA2 );
+ patientStore.save( patientA3 );
+ patientStore.save( patientB1 );
+ patientStore.save( patientB2 );
+
+ programInstanceService.enrollPatient( patientA1, programA, date, date, organisationUnitA, null );
+ programInstanceService.enrollPatient( patientA3, programA, date, date, organisationUnitA, null );
+ programInstanceService.enrollPatient( patientB1, programB, date, date, organisationUnitA, null );
+
+ int count = patientStore.countGetPatientsByOrgUnitProgram( organisationUnitA, programA );
+ assertEquals( 2, count );
+
+ count = patientStore.countGetPatientsByOrgUnitProgram( organisationUnitA, programB );
+ assertEquals( 1, count );
+ }
}
