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