=== 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 );
+    }
 }