dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #26281
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 13024: Revert R 13022
------------------------------------------------------------
revno: 13024
committer: Tran Chau <tran.hispvietnam@xxxxxxxxx>
branch nick: dhis2
timestamp: Tue 2013-11-26 15:29:22 +0700
message:
Revert R 13022
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-26 07:47:15 +0000
+++ dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/patient/hibernate/HibernatePatientStore.java 2013-11-26 08:29:22 +0000
@@ -39,28 +39,23 @@
import static org.hisp.dhis.patient.Patient.PREFIX_PROGRAM_INSTANCE;
import static org.hisp.dhis.patient.Patient.PREFIX_PROGRAM_STAGE;
-import java.lang.reflect.Field;
-import java.util.Calendar;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.ArrayList;
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;
@@ -72,13 +67,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.DateUtils;
+import org.hisp.dhis.system.util.TextUtils;
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;
@@ -90,6 +85,8 @@
extends HibernateIdentifiableObjectStore<Patient>
implements PatientStore
{
+ private static final Log log = LogFactory.getLog( HibernatePatientStore.class );
+
// -------------------------------------------------------------------------
// Dependencies
// -------------------------------------------------------------------------
@@ -104,7 +101,7 @@
// -------------------------------------------------------------------------
// Implementation methods
// -------------------------------------------------------------------------
-
+
@Override
public Collection<Patient> getByNames( String fullName, Integer min, Integer max )
{
@@ -224,16 +221,11 @@
@Override
public int countGetPatientsByOrgUnitProgram( OrganisationUnit organisationUnit, Program program )
{
- 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;
+ 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 );
}
@Override
@@ -246,39 +238,65 @@
}
@Override
- @SuppressWarnings( "unchecked" )
+ // TODO this method must be changed - cannot retrieve one by one
public Collection<Patient> search( List<String> searchKeys, Collection<OrganisationUnit> orgunits,
Boolean followup, Collection<PatientAttribute> patientAttributes,
Collection<PatientIdentifierType> identifierTypes, Integer statusEnrollment, Integer min, Integer max )
{
- Criteria criteria = searchPatientCriteria( false, searchKeys, orgunits, followup, patientAttributes,
- identifierTypes, statusEnrollment, min, max );
-
- return criteria.list();
+ 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;
}
@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 )
{
- Criteria criteria = searchPatientCriteria( false, searchKeys, orgunits, followup, patientAttributes,
- identifierTypes, statusEnrollment, min, max );
- criteria.setProjection( Projections.property( "programStageInstance" ) );
-
- return criteria.list();
+ 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;
}
public int countSearch( List<String> searchKeys, Collection<OrganisationUnit> orgunits, Boolean followup,
Integer statusEnrollment )
{
- 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;
+ String sql = searchPatientSql( true, searchKeys, orgunits, followup, null, null, statusEnrollment, null, null );
+ return jdbcTemplate.queryForObject( sql, Integer.class );
}
@Override
@@ -286,40 +304,12 @@
Boolean followup, Collection<PatientAttribute> patientAttributes,
Collection<PatientIdentifierType> identifierTypes, Integer statusEnrollment, Integer min, Integer max )
{
-
- 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();
- }
+ String sql = searchPatientSql( false, searchKeys, orgunits, followup, patientAttributes, identifierTypes,
+ statusEnrollment, min, max );
+
+ SqlRowSet rowSet = jdbcTemplate.queryForRowSet( sql );
+
+ GridUtils.addRows( grid, rowSet );
return grid;
}
@@ -328,13 +318,15 @@
@SuppressWarnings( "unchecked" )
public Collection<Patient> getByPhoneNumber( String phoneNumber, Integer min, Integer max )
{
- Criteria criteria = getCriteria( Restrictions.ilike( "phoneNumber", phoneNumber ) );
+ String hql = "select p from Patient p where p.phoneNumber like '%" + phoneNumber + "%'";
+ Query query = getQuery( hql );
+
if ( min != null && max != null )
{
- criteria.setFirstResult( min ).setMaxResults( max );
+ query.setFirstResult( min ).setMaxResults( max );
}
- return criteria.list();
+ return query.list();
}
@Override
@@ -416,7 +408,7 @@
criteria.add( disjunction );
Number rs = (Number) criteria.setProjection( Projections.rowCount() ).uniqueResult();
-
+
if ( rs != null && rs.intValue() > 0 )
{
return PatientService.ERROR_DUPLICATE_IDENTIFIER;
@@ -440,17 +432,41 @@
// Supportive methods TODO Remplement all this!
// -------------------------------------------------------------------------
- private Criteria searchPatientCriteria( boolean count, List<String> searchKeys,
- Collection<OrganisationUnit> orgunits, Boolean followup, Collection<PatientAttribute> patientAttributes,
+ private String searchPatientSql( boolean count, List<String> searchKeys, Collection<OrganisationUnit> orgunits,
+ Boolean followup, Collection<PatientAttribute> patientAttributes,
Collection<PatientIdentifierType> identifierTypes, Integer statusEnrollment, Integer min, Integer max )
{
- Criteria criteria = getCriteria();
- criteria.createAlias( "identifiers", "patientIdentifier" );
- criteria.createAlias( "organisationUnit", "orgunit" );
-
+ 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;
boolean isSearchEvent = false;
- boolean searchAttr = false;
- boolean searchProgram = false;
+ boolean isPriorityEvent = false;
Collection<Integer> orgunitChilrenIds = null;
if ( orgunits != null )
@@ -477,288 +493,321 @@
if ( keys[0].equals( PREFIX_FIXED_ATTRIBUTE ) )
{
+ patientWhere += patientOperator;
+
if ( id.equals( FIXED_ATTR_BIRTH_DATE ) )
{
- criteria.add( Restrictions.eq( id, Integer.parseInt( value ) ) );
+ patientWhere += " p." + id + value;
}
else if ( id.equals( FIXED_ATTR_AGE ) )
{
- Calendar c = Calendar.getInstance();
- PeriodType.clearTimeOfDay( c );
- c.add( Calendar.YEAR, -1 * Integer.parseInt( value ) );
- criteria.add( Restrictions.eq( "birthdate", c.getTime() ) );
+ patientWhere += " ((DATE(now()) - DATE(birthdate))/365) " + value;
}
else if ( id.equals( FIXED_ATTR_REGISTRATION_DATE ) )
{
- Calendar c = Calendar.getInstance();
- PeriodType.clearTimeOfDay( c );
- c.add( Calendar.YEAR, -1 * Integer.parseInt( value ) );
- criteria.add( Restrictions.eq( "registrationDate", c.getTime() ) );
+ patientWhere += "p." + id + value;
}
else
{
- criteria.add( Restrictions.ilike( id, "%" + value + "%" ) );
+ patientWhere += " lower(p." + id + ")='" + value + "'";
}
+ patientOperator = " and ";
}
else if ( keys[0].equals( PREFIX_IDENTIFIER_TYPE ) )
{
+
String[] keyValues = id.split( " " );
- Disjunction disjunction = Restrictions.disjunction();
+ patientWhere += patientOperator + " (";
+ String opt = "";
for ( String v : keyValues )
{
- 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 += opt + " lower( p.name ) like '%" + v + "%' or ( lower(pi.identifier) like '%" + v
+ + "%' and pi.patientidentifiertypeid is not null ) ";
+ opt = "or";
}
- criteria.add( disjunction );
+
+ patientWhere += ")";
+ patientOperator = " and ";
+ hasIdentifier = true;
}
else if ( keys[0].equals( PREFIX_PATIENT_ATTRIBUTE ) )
{
- if ( !searchAttr )
- {
- criteria.createAlias( "attributeValues", "attributeValue" );
- searchAttr = true;
- }
+ sql += "(select value from patientattributevalue where patientid=p.patientid and patientattributeid="
+ + id + " ) as " + PREFIX_PATIENT_ATTRIBUTE + "_" + id + ",";
String[] keyValues = value.split( " " );
- Conjunction conjunction = Restrictions.conjunction();
+ otherWhere += operator + "(";
+ String opt = "";
+
for ( String v : keyValues )
{
- conjunction.add( Restrictions.eq( "attributeValue.patientAttribute.id", Integer.parseInt( id ) ) );
- conjunction.add( Restrictions.ilike( "attributeValue.value", "%" + v + "%" ) );
+ otherWhere += opt + " lower(" + PREFIX_PATIENT_ATTRIBUTE + "_" + id + ") like '%" + v + "%'";
+ opt = "or";
}
- criteria.add( conjunction );
+
+ otherWhere += ")";
+ operator = " and ";
}
else if ( keys[0].equals( PREFIX_PROGRAM ) )
{
- if ( !searchProgram )
- {
- criteria.createAlias( "programInstances", "programInstance" );
- criteria.createAlias( "programInstance.program", "program" );
- searchProgram = true;
- }
-
- criteria.add( Restrictions.eq( "program.id", Integer.parseInt( id ) ) );
+ sql += "(select programid from programinstance pi where patientid=p.patientid and programid=" + id;
if ( statusEnrollment != null )
{
- criteria.add( Restrictions.eq( "programInstance.status", statusEnrollment ) );
+ sql += " and pi.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 ) )
{
- if ( !searchProgram )
- {
- criteria.createAlias( "programInstances", "programInstance" );
- criteria.createAlias( "programInstance.program", "program" );
- searchProgram = true;
- }
-
- criteria.add( Restrictions.eq( "programInstance.status", statusEnrollment ) );
+ sql += "(select pi." + id + " from programinstance pi where patientid=p.patientid and pi.status=0 ";
if ( keys.length == 5 )
{
- criteria.add( Restrictions.eq( "program.id", Integer.parseInt( keys[4] ) ) );
- }
+ 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 ";
}
else if ( keys[0].equals( PREFIX_PROGRAM_EVENT_BY_STATUS ) )
{
- if ( !searchProgram )
- {
- criteria.createAlias( "programInstances", "programInstance" );
- criteria.createAlias( "programInstance.program", "program" );
- searchProgram = true;
- }
+ 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;
- criteria.add( Restrictions.eq( "program.id", Integer.parseInt( id ) ) );
- criteria.add( Restrictions.eq( "programInstance.status", ProgramInstance.STATUS_ACTIVE ) );
+ String operatorStatus = "";
+ String condition = " and ( ";
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:
- 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 ) );
+ patientWhere += condition + operatorStatus
+ + "( psi.executiondate is not null and psi.executiondate>='" + keys[2]
+ + "' and psi.executiondate<='" + keys[3] + "' and psi.completed=true ";
// get events by orgunit children
if ( keys[4].equals( "-1" ) )
{
- criteria.add( Restrictions.in( "eventOrg.id", orgunitChilrenIds ) );
+ patientWhere += " and psi.organisationunitid in( "
+ + TextUtils.getCommaDelimitedString( orgunitChilrenIds ) + " )";
}
// get events by selected orgunit
else if ( !keys[4].equals( "0" ) )
{
- criteria.add( Restrictions.eq( "eventOrg.id", Integer.parseInt( keys[4] ) ) );
+ patientWhere += " and psi.organisationunitid=" + keys[4];
}
+
+ patientWhere += ")";
+ operatorStatus = " OR ";
+ condition = "";
continue;
case ProgramStageInstance.VISITED_STATUS:
- 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 ) );
+ patientWhere += condition + operatorStatus
+ + "( psi.executiondate is not null and psi.executiondate>='" + keys[2]
+ + "' and psi.executiondate<='" + keys[3] + "' and psi.completed=false ";
// get events by orgunit children
if ( keys[4].equals( "-1" ) )
{
- criteria.add( Restrictions.in( "eventOrg.id", orgunitChilrenIds ) );
+ patientWhere += " and psi.organisationunitid in( "
+ + TextUtils.getCommaDelimitedString( orgunitChilrenIds ) + " )";
}
// get events by selected orgunit
else if ( !keys[4].equals( "0" ) )
{
- criteria.add( Restrictions.eq( "eventOrg.id", Integer.parseInt( keys[4] ) ) );
+ patientWhere += " and psi.organisationunitid=" + keys[4];
}
+
+ patientWhere += ")";
+ operatorStatus = " OR ";
+ condition = "";
continue;
case ProgramStageInstance.FUTURE_VISIT_STATUS:
- 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() ) );
+ 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) ";
// get events by orgunit children
if ( keys[4].equals( "-1" ) )
{
- criteria.add( Restrictions.in( "eventOrg.id", orgunitChilrenIds ) );
+ patientWhere += " and p.organisationunitid in( "
+ + TextUtils.getCommaDelimitedString( orgunitChilrenIds ) + " )";
}
// get events by selected orgunit
else if ( !keys[4].equals( "0" ) )
{
- criteria.add( Restrictions.eq( "eventOrg.id", Integer.parseInt( keys[4] ) ) );
+ patientWhere += " and p.organisationunitid=" + keys[4];
}
+
+ patientWhere += ")";
+ operatorStatus = " OR ";
+ condition = "";
continue;
case ProgramStageInstance.LATE_VISIT_STATUS:
- 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() ) );
+ 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) ";
// get events by orgunit children
if ( keys[4].equals( "-1" ) )
{
- criteria.add( Restrictions.in( "eventOrg.id", orgunitChilrenIds ) );
+ patientWhere += " and p.organisationunitid in( "
+ + TextUtils.getCommaDelimitedString( orgunitChilrenIds ) + " )";
}
// get events by selected orgunit
else if ( !keys[4].equals( "0" ) )
{
- criteria.add( Restrictions.eq( "eventOrg.id", Integer.parseInt( keys[4] ) ) );
+ patientWhere += " and p.organisationunitid=" + keys[4];
}
+
+ patientWhere += ")";
+ operatorStatus = " OR ";
+ condition = "";
continue;
case ProgramStageInstance.SKIPPED_STATUS:
- criteria.add( Restrictions.between( "programStageInstance.duedate",
- DateUtils.getDefaultDate( keys[2] ), DateUtils.getDefaultDate( keys[3] ) ) );
- criteria.add( Restrictions.eq( "programStageInstance.status",
- ProgramStageInstance.SKIPPED_STATUS ) );
+ patientWhere += condition + operatorStatus + "( psi.status=5 and psi.duedate>='" + keys[2]
+ + "' and psi.duedate<='" + keys[3] + "' ";
// get events by orgunit children
if ( keys[4].equals( "-1" ) )
{
- criteria.add( Restrictions.in( "eventOrg.id", orgunitChilrenIds ) );
+ patientWhere += " and psi.organisationunitid in( "
+ + TextUtils.getCommaDelimitedString( orgunitChilrenIds ) + " )";
}
// get events by selected orgunit
else if ( !keys[4].equals( "0" ) )
{
- criteria.add( Restrictions.eq( "eventOrg.id", Integer.parseInt( keys[4] ) ) );
+ patientWhere += " and p.organisationunitid=" + keys[4];
}
+ patientWhere += ")";
+ operatorStatus = " OR ";
+ condition = "";
continue;
default:
continue;
}
}
+ if ( condition.isEmpty() )
+ {
+ patientWhere += ")";
+ }
- criteria.add( Restrictions.eq( "programInstance.status", ProgramInstance.STATUS_ACTIVE ) );
+ patientWhere += " and pgi.status=" + ProgramInstance.STATUS_ACTIVE + " ";
+ patientOperator = " and ";
}
else if ( keys[0].equals( PREFIX_PROGRAM_STAGE ) )
{
-
- 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 );
+ 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 ";
int statusEvent = Integer.parseInt( keys[2] );
switch ( statusEvent )
{
case ProgramStageInstance.COMPLETED_STATUS:
- criteria.add( Restrictions.eq( "programStageInstance.completed", true ) );
+ patientWhere += "psi.completed=true";
break;
case ProgramStageInstance.VISITED_STATUS:
- criteria.add( Restrictions.isNotNull( "programStageInstance.executiondate" ) );
- criteria.add( Restrictions.eq( "programStageInstance.completed", false ) );
+ patientWhere += "psi.executiondate is not null and psi.completed=false";
break;
case ProgramStageInstance.FUTURE_VISIT_STATUS:
- criteria.add( Restrictions.isNull( "programStageInstance.executiondate" ) );
- criteria.add( Restrictions.ge( "programStageInstance.duedate", c.getTime() ) );
+ patientWhere += "psi.executiondate is null and psi.duedate >= now()";
break;
case ProgramStageInstance.LATE_VISIT_STATUS:
- criteria.add( Restrictions.isNull( "programStageInstance.executiondate" ) );
- criteria.add( Restrictions.le( "programStageInstance.duedate", c.getTime() ) );
+ patientWhere += "psi.executiondate is null and psi.duedate < now()";
break;
default:
break;
}
- criteria.add( Restrictions.le( "programInstance.status", ProgramInstance.STATUS_ACTIVE ) );
+
+ patientWhere += " and pgi.status=" + ProgramInstance.STATUS_ACTIVE + " ";
+ patientOperator = " and ";
}
}
if ( orgunits != null && !isSearchEvent )
{
- criteria.add( Restrictions.in( "orgunit.id", getOrganisationUnitIds( orgunits ) ) );
-
- }
-
+ 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;
if ( followup != null )
{
- if ( !searchProgram )
- {
- criteria.createAlias( "programInstances", "programInstance" );
- criteria.createAlias( "programInstance.program", "program" );
- searchProgram = true;
- }
- criteria.add( Restrictions.eq( "programInstance.followup", followup ) );
- }
+ sql += " AND pgi.followup=" + followup;
+ }
+ if ( isSearchEvent )
+ {
+ sql += patientGroupBy;
+ }
+ sql += orderBy;
+ sql += " ) as searchresult";
+ sql += otherWhere;
if ( min != null && max != null )
{
- criteria.setFirstResult( min ).setMaxResults( max );
+ sql += " limit " + max + " offset " + min;
}
- return criteria;
+ log.info( "Search patient SQL: " + sql );
+
+ return sql;
}
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-26 07:47:15 +0000
+++ dhis-2/dhis-services/dhis-service-patient/src/test/java/org/hisp/dhis/patient/PatientStoreTest.java 2013-11-26 08:29:22 +0000
@@ -102,35 +102,33 @@
private Program programB;
- private OrganisationUnit organisationUnitA;
-
- private OrganisationUnit organisationUnitB;
+ private OrganisationUnit organisationUnit;
private Date date = new Date();
@Override
public void setUpTest()
{
- organisationUnitA = createOrganisationUnit( 'A' );
- organisationUnitService.addOrganisationUnit( organisationUnitA );
+ organisationUnit = createOrganisationUnit( 'A' );
+ organisationUnitService.addOrganisationUnit( organisationUnit );
- organisationUnitB = createOrganisationUnit( 'B' );
+ OrganisationUnit organisationUnitB = createOrganisationUnit( 'B' );
organisationUnitService.addOrganisationUnit( organisationUnitB );
-
+
PatientIdentifierType patientIdentifierType = createPatientIdentifierType( 'A' );
identifierTypeService.savePatientIdentifierType( patientIdentifierType );
patientAttribute = createPatientAttribute( 'A' );
attributeId = patientAttributeService.savePatientAttribute( patientAttribute );
- patientA1 = createPatient( 'A', "F", organisationUnitA );
+ patientA1 = createPatient( 'A', "F", organisationUnit );
patientA2 = createPatient( 'A', "F", organisationUnitB );
- patientA3 = createPatient( 'A', organisationUnitA, patientIdentifierType );
- patientB1 = createPatient( 'B', "M", organisationUnitA );
- patientB2 = createPatient( 'B', organisationUnitA );
+ patientA3 = createPatient( 'A', organisationUnit, patientIdentifierType );
+ patientB1 = createPatient( 'B', "M", organisationUnit );
+ patientB2 = createPatient( 'B', organisationUnit );
- programA = createProgram( 'A', new HashSet<ProgramStage>(), organisationUnitA );
- programB = createProgram( 'B', new HashSet<ProgramStage>(), organisationUnitA );
+ programA = createProgram( 'A', new HashSet<ProgramStage>(), organisationUnit );
+ programB = createProgram( 'B', new HashSet<ProgramStage>(), organisationUnit );
}
@Test
@@ -190,12 +188,12 @@
patientStore.save( patientB1 );
patientStore.save( patientB2 );
- Collection<Patient> patients = patientStore.getByFullName( "NameA", organisationUnitA );
+ Collection<Patient> patients = patientStore.getByFullName( "NameA", organisationUnit );
assertEquals( 1, patients.size() );
assertTrue( patients.contains( patientA1 ) );
- patients = patientStore.getByFullName( "NameB", organisationUnitA );
+ patients = patientStore.getByFullName( "NameB", organisationUnit );
assertEquals( 2, patients.size() );
assertTrue( patients.contains( patientB1 ) );
@@ -213,18 +211,18 @@
patientStore.save( patientA2 );
patientStore.save( patientB2 );
- 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 );
+ 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 );
- Collection<Patient> patients = patientStore.getByOrgUnitProgram( organisationUnitA, programA, 0, 100 );
+ Collection<Patient> patients = patientStore.getByOrgUnitProgram( organisationUnit, programA, 0, 100 );
assertEquals( 2, patients.size() );
assertTrue( patients.contains( patientA1 ) );
assertTrue( patients.contains( patientB1 ) );
- patients = patientStore.getByOrgUnitProgram( organisationUnitA, programB, 0, 100 );
+ patients = patientStore.getByOrgUnitProgram( organisationUnit, programB, 0, 100 );
assertEquals( 1, patients.size() );
assertTrue( patients.contains( patientB2 ) );
@@ -241,10 +239,10 @@
patientStore.save( patientA2 );
patientStore.save( patientB2 );
- 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 );
+ 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 );
Collection<Patient> patients = patientStore.getByProgram( programA, 0, 100 );
@@ -253,7 +251,7 @@
assertTrue( patients.contains( patientA2 ) );
assertTrue( patients.contains( patientB1 ) );
- patients = patientStore.getByOrgUnitProgram( organisationUnitA, programB, 0, 100 );
+ patients = patientStore.getByOrgUnitProgram( organisationUnit, programB, 0, 100 );
assertEquals( 1, patients.size() );
assertTrue( patients.contains( patientB2 ) );
@@ -292,7 +290,7 @@
patientStore.save( patientA2 );
patientStore.save( patientA3 );
- Collection<Patient> patients = patientStore.getByOrgUnitAndNameLike( organisationUnitA, "A", null, null );
+ Collection<Patient> patients = patientStore.getByOrgUnitAndNameLike( organisationUnit, "A", null, null );
assertEquals( 2, patients.size() );
assertTrue( patients.contains( patientA1 ) );
assertTrue( patients.contains( patientA3 ) );
@@ -335,18 +333,18 @@
PatientAttributeValue attributeValue = createPatientAttributeValue( 'A', patientA3, patientAttribute );
patientAttributeValueService.savePatientAttributeValue( attributeValue );
- programInstanceService.enrollPatient( patientA3, programA, date, date, organisationUnitA, null );
- programInstanceService.enrollPatient( patientB1, programA, date, date, organisationUnitA, null );
+ programInstanceService.enrollPatient( patientA3, programA, date, date, organisationUnit, null );
+ programInstanceService.enrollPatient( patientB1, programA, date, date, organisationUnit, null );
List<String> searchKeys = new ArrayList<String>();
searchKeys.add( Patient.PREFIX_IDENTIFIER_TYPE + Patient.SEARCH_SAPERATE + "a" + Patient.SEARCH_SAPERATE
- + organisationUnitA.getId() );
+ + organisationUnit.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( organisationUnitA );
+ orgunits.add( organisationUnit );
Collection<Patient> patients = patientStore.search( searchKeys, orgunits, null, null, null,
ProgramStageInstance.ACTIVE_STATUS, null, null );
@@ -375,27 +373,4 @@
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 );
- }
}
