=== modified file 'dhis-2/dhis-services/dhis-service-tracker/src/main/java/org/hisp/dhis/trackedentity/hibernate/HibernateTrackedEntityInstanceStore.java' --- dhis-2/dhis-services/dhis-service-tracker/src/main/java/org/hisp/dhis/trackedentity/hibernate/HibernateTrackedEntityInstanceStore.java 2014-04-17 20:10:10 +0000 +++ dhis-2/dhis-services/dhis-service-tracker/src/main/java/org/hisp/dhis/trackedentity/hibernate/HibernateTrackedEntityInstanceStore.java 2014-04-18 04:25:11 +0000 @@ -28,13 +28,38 @@ * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. */ +import static org.hisp.dhis.common.IdentifiableObjectUtils.getIdentifiers; +import static org.hisp.dhis.system.util.TextUtils.getCommaDelimitedString; +import static org.hisp.dhis.system.util.TextUtils.getTokens; +import static org.hisp.dhis.system.util.TextUtils.removeLastAnd; +import static org.hisp.dhis.system.util.TextUtils.removeLastComma; +import static org.hisp.dhis.system.util.TextUtils.removeLastOr; +import static org.hisp.dhis.trackedentity.TrackedEntityInstance.PREFIX_PROGRAM; +import static org.hisp.dhis.trackedentity.TrackedEntityInstance.PREFIX_PROGRAM_EVENT_BY_STATUS; +import static org.hisp.dhis.trackedentity.TrackedEntityInstance.PREFIX_PROGRAM_INSTANCE; +import static org.hisp.dhis.trackedentity.TrackedEntityInstance.PREFIX_PROGRAM_STAGE; +import static org.hisp.dhis.trackedentity.TrackedEntityInstance.PREFIX_TRACKED_ENTITY_ATTRIBUTE; +import static org.hisp.dhis.trackedentity.TrackedEntityInstanceQueryParams.CREATED_ID; +import static org.hisp.dhis.trackedentity.TrackedEntityInstanceQueryParams.LAST_UPDATED_ID; +import static org.hisp.dhis.trackedentity.TrackedEntityInstanceQueryParams.ORG_UNIT_ID; +import static org.hisp.dhis.trackedentity.TrackedEntityInstanceQueryParams.TRACKED_ENTITY_ID; +import static org.hisp.dhis.trackedentity.TrackedEntityInstanceQueryParams.TRACKED_ENTITY_INSTANCE_ID; + +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.ArrayList; +import java.util.Collection; +import java.util.Date; +import java.util.HashMap; +import java.util.HashSet; +import java.util.List; +import java.util.Map; + import org.apache.commons.lang.StringUtils; 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.Projections; import org.hibernate.criterion.Restrictions; import org.hisp.dhis.common.Grid; @@ -65,21 +90,6 @@ import org.springframework.jdbc.support.rowset.SqlRowSet; import org.springframework.transaction.annotation.Transactional; -import java.sql.ResultSet; -import java.sql.SQLException; -import java.util.ArrayList; -import java.util.Collection; -import java.util.Date; -import java.util.HashMap; -import java.util.HashSet; -import java.util.List; -import java.util.Map; - -import static org.hisp.dhis.common.IdentifiableObjectUtils.getIdentifiers; -import static org.hisp.dhis.system.util.TextUtils.*; -import static org.hisp.dhis.trackedentity.TrackedEntityInstance.*; -import static org.hisp.dhis.trackedentity.TrackedEntityInstanceQueryParams.*; - /** * @author Abyot Asalefew Gizaw */ @@ -281,7 +291,8 @@ else if ( params.isOrganisationUnitMode( OrganisationUnitSelectionMode.ALL ) ) { } - else // SELECTED (default) + else + // SELECTED (default) { sql += hlp.whereAnd() + " tei.organisationunitid in (" + getCommaDelimitedString( getIdentifiers( params.getOrganisationUnits() ) ) + ") "; @@ -289,16 +300,15 @@ if ( params.hasProgram() ) { - sql += hlp.whereAnd() + " exists (" + - "select trackedentityinstanceid from programinstance pi " + - "where pi.trackedentityinstanceid=tei.trackedentityinstanceid " + - "and pi.programid = " + params.getProgram().getId() + " "; + sql += hlp.whereAnd() + " exists (" + "select trackedentityinstanceid from programinstance pi " + + "where pi.trackedentityinstanceid=tei.trackedentityinstanceid " + "and pi.programid = " + + params.getProgram().getId() + " "; if ( params.hasProgramStatus() ) { sql += "and pi.status = " + PROGRAM_STATUS_MAP.get( params.getProgramStatus() + " " ); } - + if ( params.hasFollowUp() ) { sql += "and pi.followup = " + params.getFollowUp() + " "; @@ -347,7 +357,7 @@ } @Override - @SuppressWarnings("unchecked") + @SuppressWarnings( "unchecked" ) public Collection getByOrgUnit( OrganisationUnit organisationUnit, Integer min, Integer max ) { String hql = "select p from TrackedEntityInstance p where p.organisationUnit = :organisationUnit order by p.id DESC"; @@ -364,7 +374,7 @@ } @Override - @SuppressWarnings("unchecked") + @SuppressWarnings( "unchecked" ) public Collection getByOrgUnitProgram( OrganisationUnit organisationUnit, Program program, Integer min, Integer max ) { @@ -381,7 +391,7 @@ } @Override - @SuppressWarnings("unchecked") + @SuppressWarnings( "unchecked" ) public Collection getByProgram( Program program, Integer min, Integer max ) { String hql = "select pt from TrackedEntityInstance pt inner join pt.programInstances pi " @@ -420,7 +430,7 @@ } @Override - @SuppressWarnings("unchecked") + @SuppressWarnings( "unchecked" ) public Collection getRepresentatives( TrackedEntityInstance instance ) { String hql = "select distinct p from TrackedEntityInstance p where p.representative = :representative order by p.id DESC"; @@ -429,7 +439,7 @@ } @Override - @SuppressWarnings("unchecked") + @SuppressWarnings( "unchecked" ) public Collection getByPhoneNumber( String phoneNumber, Integer min, Integer max ) { Criteria criteria = getCriteria(); @@ -451,7 +461,6 @@ { if ( instance.getAttributeValues() != null && instance.getAttributeValues().size() > 0 ) { - boolean hasUnique = false; for ( TrackedEntityAttributeValue attributeValue : instance.getAttributeValues() ) { @@ -459,60 +468,38 @@ if ( attribute.isUnique() ) { - hasUnique = true; - break; - } - } - - if ( hasUnique ) - { - Criteria criteria = getCriteria(); - criteria.createAlias( "attributeValues", "attributeValue" ); - criteria.createAlias( "attributeValue.attribute", "attribute" ); - criteria.createAlias( "organisationUnit", "orgunit" ); - criteria.createAlias( "programInstances", "programInstance" ); - - Disjunction disjunction = Restrictions.disjunction(); - - for ( TrackedEntityAttributeValue attributeValue : instance.getAttributeValues() ) - { - TrackedEntityAttribute attribute = attributeValue.getAttribute(); - - if ( attribute.isUnique() ) - { - Conjunction conjunction = Restrictions.conjunction(); - conjunction.add( Restrictions.eq( "attributeValue.value", attributeValue.getValue() ) ); - conjunction.add( Restrictions.eq( "attribute", attribute ) ); - - if ( attribute.getId() != 0 ) - { - conjunction.add( Restrictions.ne( "id", instance.getId() ) ); - } - - if ( attribute.getOrgunitScope() ) - { - conjunction.add( Restrictions.eq( "orgunit.id", instance.getOrganisationUnit().getId() ) ); - } - - if ( program != null - && attribute.getProgramScope() ) - { - conjunction.add( Restrictions.eq( "programInstance.program", program ) ); - } - - disjunction.add( conjunction ); - } - } - - criteria.add( disjunction ); - - Number rs = (Number) criteria.setProjection( - Projections.projectionList().add( Projections.property( "attribute.id" ) ) ).uniqueResult(); - - if ( rs != null && rs.intValue() > 0 ) - { - return TrackedEntityInstanceService.ERROR_DUPLICATE_IDENTIFIER - + TrackedEntityInstanceService.SAPERATOR + rs.intValue(); + + Criteria criteria = getCriteria(); + criteria.createAlias( "attributeValues", "attributeValue" ); + criteria.createAlias( "attributeValue.attribute", "attribute" ); + criteria.add( Restrictions.eq( "attributeValue.value", attributeValue.getValue() ) ); + criteria.add( Restrictions.eq( "attributeValue.attribute", attribute ) ); + + if ( attribute.getId() != 0 ) + { + criteria.add( Restrictions.ne( "id", attribute.getId() ) ); + } + + if ( attribute.getOrgunitScope() ) + { + criteria.createAlias( "organisationUnit", "orgunit" ); + criteria.add( Restrictions.eq( "orgunit.id", instance.getOrganisationUnit().getId() ) ); + } + + if ( program != null && attribute.getProgramScope() ) + { + criteria.createAlias( "programInstances", "programInstance" ); + criteria.add( Restrictions.eq( "programInstance.program", program ) ); + } + + Number rs = (Number) criteria.setProjection( + Projections.projectionList().add( Projections.property( "attribute.id" ) ) ).uniqueResult(); + + if ( rs != null && rs.intValue() > 0 ) + { + return TrackedEntityInstanceService.ERROR_DUPLICATE_IDENTIFIER + + TrackedEntityInstanceService.SAPERATOR + rs.intValue(); + } } } } @@ -792,116 +779,116 @@ int statusEvent = Integer.parseInt( keys[index] ); switch ( statusEvent ) { - case ProgramStageInstance.COMPLETED_STATUS: - instanceWhere += 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" ) ) - { - instanceWhere += " and psi.organisationunitid in( " - + getCommaDelimitedString( orgunitChilrenIds ) + " )"; - } - - // get events by selected orgunit - else if ( !keys[4].equals( "0" ) ) - { - instanceWhere += " and psi.organisationunitid=" + getOrgUnitId( keys ); - } - - instanceWhere += ")"; - operatorStatus = " OR "; - condition = ""; - continue; - case ProgramStageInstance.VISITED_STATUS: - instanceWhere += 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" ) ) - { - instanceWhere += " and psi.organisationunitid in( " - + getCommaDelimitedString( orgunitChilrenIds ) + " )"; - } - - // get events by selected orgunit - else if ( !keys[4].equals( "0" ) ) - { - instanceWhere += " and psi.organisationunitid=" + getOrgUnitId( keys ); - } - - instanceWhere += ")"; - operatorStatus = " OR "; - condition = ""; - continue; - case ProgramStageInstance.FUTURE_VISIT_STATUS: - instanceWhere += 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" ) ) - { - instanceWhere += " and p.organisationunitid in( " - + getCommaDelimitedString( orgunitChilrenIds ) + " )"; - } - - // get events by selected orgunit - else if ( !keys[4].equals( "0" ) ) - { - instanceWhere += " and p.organisationunitid=" + getOrgUnitId( keys ); - } - - instanceWhere += ")"; - operatorStatus = " OR "; - condition = ""; - continue; - case ProgramStageInstance.LATE_VISIT_STATUS: - instanceWhere += 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" ) ) - { - instanceWhere += " and p.organisationunitid in( " - + getCommaDelimitedString( orgunitChilrenIds ) + " )"; - } - - // get events by selected orgunit - else if ( !keys[4].equals( "0" ) ) - { - instanceWhere += " and p.organisationunitid=" + getOrgUnitId( keys ); - } - - instanceWhere += ")"; - operatorStatus = " OR "; - condition = ""; - continue; - case ProgramStageInstance.SKIPPED_STATUS: - instanceWhere += 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" ) ) - { - instanceWhere += " and psi.organisationunitid in( " - + getCommaDelimitedString( orgunitChilrenIds ) + " )"; - } - - // get events by selected orgunit - else if ( !keys[4].equals( "0" ) ) - { - instanceWhere += " and p.organisationunitid=" + getOrgUnitId( keys ); - } - instanceWhere += ")"; - operatorStatus = " OR "; - condition = ""; - continue; - default: - continue; + case ProgramStageInstance.COMPLETED_STATUS: + instanceWhere += 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" ) ) + { + instanceWhere += " and psi.organisationunitid in( " + + getCommaDelimitedString( orgunitChilrenIds ) + " )"; + } + + // get events by selected orgunit + else if ( !keys[4].equals( "0" ) ) + { + instanceWhere += " and psi.organisationunitid=" + getOrgUnitId( keys ); + } + + instanceWhere += ")"; + operatorStatus = " OR "; + condition = ""; + continue; + case ProgramStageInstance.VISITED_STATUS: + instanceWhere += 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" ) ) + { + instanceWhere += " and psi.organisationunitid in( " + + getCommaDelimitedString( orgunitChilrenIds ) + " )"; + } + + // get events by selected orgunit + else if ( !keys[4].equals( "0" ) ) + { + instanceWhere += " and psi.organisationunitid=" + getOrgUnitId( keys ); + } + + instanceWhere += ")"; + operatorStatus = " OR "; + condition = ""; + continue; + case ProgramStageInstance.FUTURE_VISIT_STATUS: + instanceWhere += 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" ) ) + { + instanceWhere += " and p.organisationunitid in( " + + getCommaDelimitedString( orgunitChilrenIds ) + " )"; + } + + // get events by selected orgunit + else if ( !keys[4].equals( "0" ) ) + { + instanceWhere += " and p.organisationunitid=" + getOrgUnitId( keys ); + } + + instanceWhere += ")"; + operatorStatus = " OR "; + condition = ""; + continue; + case ProgramStageInstance.LATE_VISIT_STATUS: + instanceWhere += 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" ) ) + { + instanceWhere += " and p.organisationunitid in( " + + getCommaDelimitedString( orgunitChilrenIds ) + " )"; + } + + // get events by selected orgunit + else if ( !keys[4].equals( "0" ) ) + { + instanceWhere += " and p.organisationunitid=" + getOrgUnitId( keys ); + } + + instanceWhere += ")"; + operatorStatus = " OR "; + condition = ""; + continue; + case ProgramStageInstance.SKIPPED_STATUS: + instanceWhere += 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" ) ) + { + instanceWhere += " and psi.organisationunitid in( " + + getCommaDelimitedString( orgunitChilrenIds ) + " )"; + } + + // get events by selected orgunit + else if ( !keys[4].equals( "0" ) ) + { + instanceWhere += " and p.organisationunitid=" + getOrgUnitId( keys ); + } + instanceWhere += ")"; + operatorStatus = " OR "; + condition = ""; + continue; + default: + continue; } } if ( condition.isEmpty() ) @@ -923,20 +910,20 @@ int statusEvent = Integer.parseInt( keys[2] ); switch ( statusEvent ) { - case ProgramStageInstance.COMPLETED_STATUS: - instanceWhere += "psi.completed=true"; - break; - case ProgramStageInstance.VISITED_STATUS: - instanceWhere += "psi.executiondate is not null and psi.completed=false"; - break; - case ProgramStageInstance.FUTURE_VISIT_STATUS: - instanceWhere += "psi.executiondate is null and psi.duedate >= now()"; - break; - case ProgramStageInstance.LATE_VISIT_STATUS: - instanceWhere += "psi.executiondate is null and psi.duedate < now()"; - break; - default: - break; + case ProgramStageInstance.COMPLETED_STATUS: + instanceWhere += "psi.completed=true"; + break; + case ProgramStageInstance.VISITED_STATUS: + instanceWhere += "psi.executiondate is not null and psi.completed=false"; + break; + case ProgramStageInstance.FUTURE_VISIT_STATUS: + instanceWhere += "psi.executiondate is null and psi.duedate >= now()"; + break; + case ProgramStageInstance.LATE_VISIT_STATUS: + instanceWhere += "psi.executiondate is null and psi.duedate < now()"; + break; + default: + break; } instanceWhere += " and pgi.status=" + ProgramInstance.STATUS_ACTIVE + " "; @@ -1059,7 +1046,7 @@ return orgUnitIds; } - @SuppressWarnings("unchecked") + @SuppressWarnings( "unchecked" ) @Override public Collection getByAttributeValue( String searchText, int attributeId, Integer min, Integer max )