=== modified file 'dhis-2/dhis-services/dhis-service-tracker/src/main/java/org/hisp/dhis/program/hibernate/HibernateProgramStageInstanceStore.java' --- dhis-2/dhis-services/dhis-service-tracker/src/main/java/org/hisp/dhis/program/hibernate/HibernateProgramStageInstanceStore.java 2014-05-16 13:32:48 +0000 +++ dhis-2/dhis-services/dhis-service-tracker/src/main/java/org/hisp/dhis/program/hibernate/HibernateProgramStageInstanceStore.java 2014-05-16 13:37:03 +0000 @@ -292,16 +292,17 @@ public Grid getCompleteness( Collection orgunitIds, Program program, String startDate, String endDate, I18n i18n ) { - String sql = "SELECT ou.name as orgunit, ps.name as events, psi.completeduser as user_name, count(psi.programstageinstanceid) as number_of_events " - + "FROM programstageinstance psi INNER JOIN programstage ps " - + "ON psi.programstageid = ps.programstageid " - + "INNER JOIN organisationunit ou ON ou.organisationunitid=psi.organisationunitid " - + "INNER JOIN program pg ON pg.programid = ps.programid " - + "WHERE ou.organisationunitid in ( " + TextUtils.getCommaDelimitedString( orgunitIds ) + " ) " - + "AND pg.programid = " + program.getId() - + "GROUP BY ou.name, ps.name, psi.completeduser, psi.completeddate, psi.completed " - + "HAVING psi.completeddate >= '" + startDate + "' AND psi.completeddate <= '" + endDate + "' " - + "AND psi.completed=true ORDER BY ou.name, ps.name, psi.completeduser"; + String sql = "select ou.name as orgunit, ps.name as events, psi.completeduser as user_name, count(psi.programstageinstanceid) as number_of_events " + + "from programstageinstance psi " + + "inner join programstage ps on psi.programstageid = ps.programstageid " + + "inner join organisationunit ou on ou.organisationunitid=psi.organisationunitid " + + "inner join program pg on pg.programid = ps.programid " + + "where ou.organisationunitid in ( " + TextUtils.getCommaDelimitedString( orgunitIds ) + " ) " + + "and pg.programid = " + program.getId() + + "group by ou.name, ps.name, psi.completeduser, psi.completeddate, psi.completed " + + "having psi.completeddate >= '" + startDate + "' AND psi.completeddate <= '" + endDate + "' " + + "and psi.completed=true " + + "order by ou.name, ps.name, psi.completeduser"; SqlRowSet rs = jdbcTemplate.queryForRowSet( sql ); @@ -412,6 +413,7 @@ { Criteria criteria = getCriteria( Restrictions.eq( "programStage", programStage ), Restrictions.isNull( "programInstance.endDate" ) ); + criteria.createAlias( "programInstance", "programInstance" ); criteria.createAlias( "programInstance.entityInstance", "entityInstance" ); criteria.createAlias( "entityInstance.organisationUnit", "regOrgunit" ); @@ -419,26 +421,26 @@ switch ( status ) { - case ProgramStageInstance.COMPLETED_STATUS: - criteria.add( Restrictions.eq( "completed", true ) ); - criteria.add( Restrictions.between( "executionDate", startDate, endDate ) ); - break; - case ProgramStageInstance.VISITED_STATUS: - criteria.add( Restrictions.eq( "completed", false ) ); - criteria.add( Restrictions.between( "executionDate", startDate, endDate ) ); - break; - case ProgramStageInstance.FUTURE_VISIT_STATUS: - criteria.add( Restrictions.between( "programInstance.enrollmentDate", startDate, endDate ) ); - criteria.add( Restrictions.isNull( "executionDate" ) ); - criteria.add( Restrictions.ge( "dueDate", new Date() ) ); - break; - case ProgramStageInstance.LATE_VISIT_STATUS: - criteria.add( Restrictions.between( "programInstance.enrollmentDate", startDate, endDate ) ); - criteria.add( Restrictions.isNull( "executionDate" ) ); - criteria.add( Restrictions.lt( "dueDate", new Date() ) ); - break; - default: - break; + case ProgramStageInstance.COMPLETED_STATUS: + criteria.add( Restrictions.eq( "completed", true ) ); + criteria.add( Restrictions.between( "executionDate", startDate, endDate ) ); + break; + case ProgramStageInstance.VISITED_STATUS: + criteria.add( Restrictions.eq( "completed", false ) ); + criteria.add( Restrictions.between( "executionDate", startDate, endDate ) ); + break; + case ProgramStageInstance.FUTURE_VISIT_STATUS: + criteria.add( Restrictions.between( "programInstance.enrollmentDate", startDate, endDate ) ); + criteria.add( Restrictions.isNull( "executionDate" ) ); + criteria.add( Restrictions.ge( "dueDate", new Date() ) ); + break; + case ProgramStageInstance.LATE_VISIT_STATUS: + criteria.add( Restrictions.between( "programInstance.enrollmentDate", startDate, endDate ) ); + criteria.add( Restrictions.isNull( "executionDate" ) ); + criteria.add( Restrictions.lt( "dueDate", new Date() ) ); + break; + default: + break; } return criteria; @@ -450,6 +452,7 @@ { Collection programInstances = programInstanceService.getProgramInstancesByStatus( ProgramInstance.STATUS_COMPLETED, program, orgunitIds, after, before ); + Criteria criteria = getCriteria(); criteria.createAlias( "programInstance", "programInstance" ); criteria.createAlias( "programStage", "programStage" ); @@ -459,11 +462,14 @@ criteria.add( Restrictions.in( "organisationUnit.id", orgunitIds ) ); criteria.add( Restrictions.between( "programInstance.endDate", after, before ) ); criteria.add( Restrictions.eq( "completed", true ) ); + if ( programInstances != null && programInstances.size() > 0 ) { criteria.add( Restrictions.not( Restrictions.in( "programInstance", programInstances ) ) ); } + Number rs = (Number) criteria.setProjection( Projections.rowCount() ).uniqueResult(); + return rs != null ? rs.intValue() : 0; } @@ -475,6 +481,7 @@ criteria.createAlias( "organisationUnit", "orgunit" ); criteria.setProjection( Projections.distinct( Projections.projectionList().add( Projections.property( "orgunit.id" ), "orgunitid" ) ) ); + return criteria.list(); } @@ -482,6 +489,8 @@ // Supportive methods // --------------------------------------------------------------------- + //TODO this must be re-written + private String sendMessageToTrackedEntityInstanceSql() { return "select psi.programstageinstanceid, pav.value as phonenumber, prm.templatemessage, org.name as orgunitName "