=== modified file 'dhis-2/dhis-api/src/main/java/org/hisp/dhis/organisationunit/OrganisationUnit.java' --- dhis-2/dhis-api/src/main/java/org/hisp/dhis/organisationunit/OrganisationUnit.java 2015-06-24 06:17:15 +0000 +++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/organisationunit/OrganisationUnit.java 2015-07-07 14:54:50 +0000 @@ -34,6 +34,7 @@ import com.fasterxml.jackson.dataformat.xml.annotation.JacksonXmlElementWrapper; import com.fasterxml.jackson.dataformat.xml.annotation.JacksonXmlProperty; import com.fasterxml.jackson.dataformat.xml.annotation.JacksonXmlRootElement; +import com.google.common.base.Joiner; import org.apache.commons.lang3.StringUtils; import org.hisp.dhis.attribute.AttributeValue; import org.hisp.dhis.common.BaseIdentifiableObject; @@ -107,6 +108,8 @@ private OrganisationUnit parent; + private String path; + private Date openingDate; private Date closedDate; @@ -824,6 +827,42 @@ } @JsonProperty + @JsonView( { DetailedView.class, ExportView.class } ) + @JacksonXmlProperty( namespace = DxfNamespaces.DXF_2_0 ) + public String getPath() + { + List pathList = new ArrayList<>(); + OrganisationUnit currentParent = parent; + + pathList.add( uid ); + + while ( currentParent != null ) + { + if ( !pathList.contains( currentParent.getUid() ) ) + { + pathList.add( currentParent.getUid() ); + currentParent = currentParent.getParent(); + } + else + { + // we have tests in the system which needs cyclic OU graphs, so we need to short-circuit here if we encounter that + currentParent = null; + } + } + + Collections.reverse( pathList ); + + path = "/" + Joiner.on( "/" ).join( pathList ); + + return path; + } + + public void setPath( String path ) + { + this.path = path; + } + + @JsonProperty @JsonSerialize( contentUsing = JacksonOrganisationUnitChildrenSerializer.class ) @JsonView( { DetailedView.class } ) @JacksonXmlElementWrapper( localName = "children", namespace = DxfNamespaces.DXF_2_0 ) === modified file 'dhis-2/dhis-api/src/main/java/org/hisp/dhis/organisationunit/OrganisationUnitService.java' --- dhis-2/dhis-api/src/main/java/org/hisp/dhis/organisationunit/OrganisationUnitService.java 2015-07-07 08:19:09 +0000 +++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/organisationunit/OrganisationUnitService.java 2015-07-07 14:54:50 +0000 @@ -551,6 +551,16 @@ */ int getOfflineOrganisationUnitLevels(); + /** + * Update all OUs where paths is null. + */ + void updatePaths(); + + /** + * Update all OUs (thus forcing update of path). + */ + void forceUpdatePaths(); + // ------------------------------------------------------------------------- // Version // ------------------------------------------------------------------------- === modified file 'dhis-2/dhis-api/src/main/java/org/hisp/dhis/organisationunit/OrganisationUnitStore.java' --- dhis-2/dhis-api/src/main/java/org/hisp/dhis/organisationunit/OrganisationUnitStore.java 2015-06-16 05:11:29 +0000 +++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/organisationunit/OrganisationUnitStore.java 2015-07-07 14:54:50 +0000 @@ -217,4 +217,7 @@ */ void updateOrganisationUnitParent( int organisationUnitId, int parentId ); + void updatePaths(); + + void forceUpdatePaths(); } === modified file 'dhis-2/dhis-api/src/main/java/org/hisp/dhis/program/ProgramInstanceStore.java' --- dhis-2/dhis-api/src/main/java/org/hisp/dhis/program/ProgramInstanceStore.java 2015-07-07 06:11:54 +0000 +++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/program/ProgramInstanceStore.java 2015-07-07 14:54:50 +0000 @@ -46,6 +46,14 @@ String ID = ProgramInstanceStore.class.getName(); /** + * Count all program instances by PI query params. + * + * @param params ProgramInstanceQueryParams to use + * @return Count of matching PIs + */ + int countProgramInstances( ProgramInstanceQueryParams params ); + + /** * Get all program instances by PI query params. * * @param params ProgramInstanceQueryParams to use === modified file 'dhis-2/dhis-services/dhis-service-administration/src/test/java/org/hisp/dhis/dataintegrity/DataIntegrityServiceTest.java' --- dhis-2/dhis-services/dhis-service-administration/src/test/java/org/hisp/dhis/dataintegrity/DataIntegrityServiceTest.java 2015-01-17 07:41:26 +0000 +++ dhis-2/dhis-services/dhis-service-administration/src/test/java/org/hisp/dhis/dataintegrity/DataIntegrityServiceTest.java 2015-07-07 14:54:50 +0000 @@ -160,7 +160,7 @@ organisationUnitService.addOrganisationUnit( unitF ); unitA.setParent( unitC ); - + organisationUnitService.updateOrganisationUnit( unitA ); dataSetA = createDataSet( 'A', new MonthlyPeriodType() ); === modified file 'dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/organisationunit/DefaultOrganisationUnitService.java' --- dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/organisationunit/DefaultOrganisationUnitService.java 2015-07-07 08:19:09 +0000 +++ dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/organisationunit/DefaultOrganisationUnitService.java 2015-07-07 14:54:50 +0000 @@ -1031,6 +1031,18 @@ return 1; } + @Override + public void updatePaths() + { + organisationUnitStore.updatePaths(); + } + + @Override + public void forceUpdatePaths() + { + organisationUnitStore.forceUpdatePaths(); + } + /** * Get all the Organisation Units within the distance of a coordinate. */ === modified file 'dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/organisationunit/hibernate/HibernateOrganisationUnitStore.java' --- dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/organisationunit/hibernate/HibernateOrganisationUnitStore.java 2015-06-16 05:11:29 +0000 +++ dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/organisationunit/hibernate/HibernateOrganisationUnitStore.java 2015-07-07 14:54:50 +0000 @@ -28,23 +28,13 @@ * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. */ -import java.sql.ResultSet; -import java.sql.SQLException; -import java.sql.Timestamp; -import java.util.ArrayList; -import java.util.Collection; -import java.util.Date; -import java.util.HashSet; -import java.util.List; -import java.util.Map; -import java.util.Set; - import org.apache.commons.collections.CollectionUtils; import org.apache.commons.lang3.StringUtils; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.hibernate.Criteria; import org.hibernate.Query; +import org.hibernate.Session; import org.hibernate.criterion.Restrictions; import org.hisp.dhis.common.AuditLogUtil; import org.hisp.dhis.common.SetMap; @@ -60,6 +50,17 @@ import org.springframework.jdbc.core.RowCallbackHandler; import org.springframework.security.access.AccessDeniedException; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Timestamp; +import java.util.ArrayList; +import java.util.Collection; +import java.util.Date; +import java.util.HashSet; +import java.util.List; +import java.util.Map; +import java.util.Set; + /** * @author Kristian Nordal */ @@ -121,7 +122,7 @@ } @Override - @SuppressWarnings("unchecked") + @SuppressWarnings( "unchecked" ) public List getAllOrganisationUnitsByStatus( boolean active ) { Query query = getQuery( "from OrganisationUnit o where o.active is :active" ); @@ -137,7 +138,7 @@ } @Override - @SuppressWarnings("unchecked") + @SuppressWarnings( "unchecked" ) public List getAllOrganisationUnitsByStatusLastUpdated( boolean active, Date lastUpdated ) { return getCriteria().add( Restrictions.ge( "lastUpdated", lastUpdated ) ).add( Restrictions.eq( "active", active ) ).list(); @@ -150,21 +151,21 @@ } @Override - @SuppressWarnings("unchecked") + @SuppressWarnings( "unchecked" ) public List getRootOrganisationUnits() { return getQuery( "from OrganisationUnit o where o.parent is null" ).list(); } @Override - @SuppressWarnings("unchecked") + @SuppressWarnings( "unchecked" ) public List getOrganisationUnitsWithoutGroups() { return getQuery( "from OrganisationUnit o where o.groups.size = 0" ).list(); } @Override - @SuppressWarnings("unchecked") + @SuppressWarnings( "unchecked" ) public List getOrganisationUnitsByNameAndGroups( String query, Collection groups, boolean limit ) { @@ -227,7 +228,7 @@ "left join organisationunit ou on ou.organisationunitid=d.sourceid " + "left join dataset ds on ds.datasetid=d.datasetid"; - final SetMap map = new SetMap<>(); + final SetMap map = new SetMap<>(); jdbcTemplate.query( sql, new RowCallbackHandler() { @@ -264,7 +265,7 @@ } @Override - @SuppressWarnings("unchecked") + @SuppressWarnings( "unchecked" ) public List getBetweenByStatus( boolean status, int first, int max ) { Criteria criteria = getCriteria().add( Restrictions.eq( "active", status ) ); @@ -275,7 +276,7 @@ } @Override - @SuppressWarnings("unchecked") + @SuppressWarnings( "unchecked" ) public List getBetweenByLastUpdated( Date lastUpdated, int first, int max ) { Criteria criteria = getCriteria().add( Restrictions.ge( "lastUpdated", lastUpdated ) ); @@ -286,7 +287,7 @@ } @Override - @SuppressWarnings("unchecked") + @SuppressWarnings( "unchecked" ) public List getBetweenByStatusLastUpdated( boolean status, Date lastUpdated, int first, int max ) { Criteria criteria = getCriteria().add( Restrictions.ge( "lastUpdated", lastUpdated ) ).add( Restrictions.eq( "active", status ) ); @@ -297,16 +298,16 @@ } @Override - @SuppressWarnings("unchecked") + @SuppressWarnings( "unchecked" ) public List getWithinCoordinateArea( double[] box ) { return getQuery( "from OrganisationUnit o" - + " where o.featureType='Point'" - + " and o.coordinates is not null" - + " and CAST( SUBSTRING(o.coordinates, 2, LOCATE(',', o.coordinates) - 2) AS big_decimal ) >= " + box[3] - + " and CAST( SUBSTRING(o.coordinates, 2, LOCATE(',', o.coordinates) - 2) AS big_decimal ) <= " + box[1] - + " and CAST( SUBSTRING(coordinates, LOCATE(',', o.coordinates) + 1, LOCATE(']', o.coordinates) - LOCATE(',', o.coordinates) - 1 ) AS big_decimal ) >= " + box[2] - + " and CAST( SUBSTRING(coordinates, LOCATE(',', o.coordinates) + 1, LOCATE(']', o.coordinates) - LOCATE(',', o.coordinates) - 1 ) AS big_decimal ) <= " + box[0] + + " where o.featureType='Point'" + + " and o.coordinates is not null" + + " and CAST( SUBSTRING(o.coordinates, 2, LOCATE(',', o.coordinates) - 2) AS big_decimal ) >= " + box[3] + + " and CAST( SUBSTRING(o.coordinates, 2, LOCATE(',', o.coordinates) - 2) AS big_decimal ) <= " + box[1] + + " and CAST( SUBSTRING(coordinates, LOCATE(',', o.coordinates) + 1, LOCATE(']', o.coordinates) - LOCATE(',', o.coordinates) - 1 ) AS big_decimal ) >= " + box[2] + + " and CAST( SUBSTRING(coordinates, LOCATE(',', o.coordinates) + 1, LOCATE(']', o.coordinates) - LOCATE(',', o.coordinates) - 1 ) AS big_decimal ) <= " + box[0] ).list(); } @@ -332,4 +333,34 @@ jdbcTemplate.execute( sql ); } + + @Override + @SuppressWarnings( "unchecked" ) + public void updatePaths() + { + List organisationUnits = new ArrayList<>( getQuery( "from OrganisationUnit ou where ou.path IS NULL" ).list() ); + Session session = sessionFactory.getCurrentSession(); + + // use SF directly since we don't need to check for access etc here, just a simple update with no changes (so that path gets re-generated) + for ( OrganisationUnit organisationUnit : organisationUnits ) + { + organisationUnit.setAutoFields(); + session.update( organisationUnit ); + } + } + + @Override + @SuppressWarnings( "unchecked" ) + public void forceUpdatePaths() + { + List organisationUnits = new ArrayList<>( getQuery( "from OrganisationUnit" ).list() ); + Session session = sessionFactory.getCurrentSession(); + + // use SF directly since we don't need to check for access etc here, just a simple update with no changes (so that path gets re-generated) + for ( OrganisationUnit organisationUnit : organisationUnits ) + { + organisationUnit.setAutoFields(); + session.update( organisationUnit ); + } + } } === modified file 'dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/program/hibernate/HibernateProgramInstanceStore.java' --- dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/program/hibernate/HibernateProgramInstanceStore.java 2015-07-07 06:11:54 +0000 +++ dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/program/hibernate/HibernateProgramInstanceStore.java 2015-07-07 14:54:50 +0000 @@ -33,9 +33,9 @@ import org.hibernate.criterion.Order; import org.hibernate.criterion.Projections; import org.hibernate.criterion.Restrictions; +import org.hisp.dhis.common.OrganisationUnitSelectionMode; import org.hisp.dhis.common.hibernate.HibernateIdentifiableObjectStore; import org.hisp.dhis.commons.util.SqlHelper; -import org.hisp.dhis.commons.util.TextUtils; import org.hisp.dhis.organisationunit.OrganisationUnit; import org.hisp.dhis.program.Program; import org.hisp.dhis.program.ProgramInstance; @@ -85,6 +85,15 @@ // ------------------------------------------------------------------------- @Override + public int countProgramInstances( ProgramInstanceQueryParams params ) + { + String hql = buildProgramInstanceHql( params ); + Query query = getQuery( hql ); + + return ((Number) query.iterate().next()).intValue(); + } + + @Override @SuppressWarnings( "unchecked" ) public List getProgramInstances( ProgramInstanceQueryParams params ) { @@ -102,10 +111,9 @@ private String buildProgramInstanceHql( ProgramInstanceQueryParams params ) { + String hql = "from ProgramInstance pi"; SqlHelper hlp = new SqlHelper( true ); - String hql = "from ProgramInstance pi"; - if ( params.hasLastUpdated() ) { hql += hlp.whereAnd() + "pi.lastUpdated >= '" + getMediumDateString( params.getLastUpdated() ) + "'"; @@ -123,7 +131,24 @@ if ( params.hasOrganisationUnits() ) { - hql += hlp.whereAnd() + "pi.organisationUnit.uid in (" + getQuotedCommaDelimitedString( getUids( params.getOrganisationUnits() ) ) + ")"; + if ( params.isOrganisationUnitMode( OrganisationUnitSelectionMode.DESCENDANTS ) ) + { + String ouClause = "("; + SqlHelper orHlp = new SqlHelper( true ); + + for ( OrganisationUnit organisationUnit : params.getOrganisationUnits() ) + { + ouClause += orHlp.or() + "pi.organisationUnit.path LIKE '" + organisationUnit.getPath() + "%'"; + } + + ouClause += ")"; + + hql += hlp.whereAnd() + ouClause; + } + else + { + hql += hlp.whereAnd() + "pi.organisationUnit.uid in (" + getQuotedCommaDelimitedString( getUids( params.getOrganisationUnits() ) ) + ")"; + } } if ( params.hasProgram() ) @@ -402,7 +427,7 @@ + " INNER JOIN trackedentityattribute tea on tea.trackedentityattributeid=teav.trackedentityattributeid " + " INNER JOIN programinstance ps on teav.trackedentityinstanceid=ps.trackedentityinstanceid " + " INNER JOIN programstageinstance psi on ps.programinstanceid=psi.programinstanceid " - + " where tea.uid in ( " + TextUtils.getQuotedCommaDelimitedString( attributeUids ) + ") " + + " where tea.uid in ( " + getQuotedCommaDelimitedString( attributeUids ) + ") " + " and ps.programinstanceid=" + programInstanceId ); while ( attributeValueRow.next() ) { === modified file 'dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/startup/TableAlteror.java' --- dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/startup/TableAlteror.java 2015-07-07 03:32:02 +0000 +++ dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/startup/TableAlteror.java 2015-07-07 14:54:50 +0000 @@ -37,6 +37,7 @@ import org.hisp.dhis.dataelement.DataElementCategoryCombo; import org.hisp.dhis.jdbc.StatementBuilder; import org.hisp.dhis.jdbc.batchhandler.RelativePeriodsBatchHandler; +import org.hisp.dhis.organisationunit.OrganisationUnitService; import org.hisp.dhis.period.RelativePeriods; import org.hisp.dhis.system.startup.AbstractStartupRoutine; import org.springframework.beans.factory.annotation.Autowired; @@ -70,6 +71,9 @@ @Autowired private BatchHandlerFactory batchHandlerFactory; + @Autowired + private OrganisationUnitService organisationUnitService; + // ------------------------------------------------------------------------- // Execute // ------------------------------------------------------------------------- @@ -835,6 +839,7 @@ upgradeAggregationType( "chart" ); updateRelativePeriods(); + organisationUnitService.updatePaths(); log.info( "Tables updated" ); } === modified file 'dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/trackedentity/hibernate/HibernateTrackedEntityInstanceStore.java' --- dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/trackedentity/hibernate/HibernateTrackedEntityInstanceStore.java 2015-07-07 08:07:05 +0000 +++ dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/trackedentity/hibernate/HibernateTrackedEntityInstanceStore.java 2015-07-07 14:54:50 +0000 @@ -133,21 +133,33 @@ private String buildTrackedEntityInstanceHql( TrackedEntityInstanceQueryParams params ) { String hql = "from TrackedEntityInstance tei"; - - if ( params.hasTrackedEntity() || params.hasOrganisationUnits() || params.hasFilters() || params.hasProgram() ) - { - hql += " where "; - } + SqlHelper hlp = new SqlHelper( true ); if ( params.hasTrackedEntity() ) { - hql += "tei.trackedEntity.uid='" + params.getTrackedEntity().getUid() + "'"; - hql += params.hasOrganisationUnits() ? " and " : ""; + hql += hlp.whereAnd() + "tei.trackedEntity.uid='" + params.getTrackedEntity().getUid() + "'"; } if ( params.hasOrganisationUnits() ) { - hql += "tei.organisationUnit.uid in (" + getQuotedCommaDelimitedString( getUids( params.getOrganisationUnits() ) ) + ")"; + if ( params.isOrganisationUnitMode( OrganisationUnitSelectionMode.DESCENDANTS ) ) + { + String ouClause = "("; + SqlHelper orHlp = new SqlHelper( true ); + + for ( OrganisationUnit organisationUnit : params.getOrganisationUnits() ) + { + ouClause += orHlp.or() + "tei.organisationUnit.path LIKE '" + organisationUnit.getPath() + "%'"; + } + + ouClause += ")"; + + hql += hlp.whereAnd() + ouClause; + } + else + { + hql += hlp.whereAnd() + "tei.organisationUnit.uid in (" + getQuotedCommaDelimitedString( getUids( params.getOrganisationUnits() ) ) + ")"; + } } if ( params.hasFilters() ) @@ -176,27 +188,27 @@ if ( params.hasProgram() ) { - hql += " and exists (from ProgramInstance pi where pi.entityInstance=tei"; - hql += " and pi.program.uid = '" + params.getProgram().getUid() + "'"; + hql += hlp.whereAnd() + "exists (from ProgramInstance pi where pi.entityInstance=tei"; + hql += hlp.whereAnd() + "pi.program.uid = '" + params.getProgram().getUid() + "'"; if ( params.hasProgramStatus() ) { - hql += " and pi.status = " + PROGRAM_STATUS_MAP.get( params.getProgramStatus() ); + hql += hlp.whereAnd() + "pi.status = " + PROGRAM_STATUS_MAP.get( params.getProgramStatus() ); } if ( params.hasFollowUp() ) { - hql += " and pi.followup = " + params.getFollowUp() + " "; + hql += hlp.whereAnd() + "pi.followup = " + params.getFollowUp(); } if ( params.hasProgramStartDate() ) { - hql += " and pi.enrollmentDate >= '" + getMediumDateString( params.getProgramStartDate() ) + "' "; + hql += hlp.whereAnd() + "pi.enrollmentDate >= '" + getMediumDateString( params.getProgramStartDate() ) + "'"; } if ( params.hasProgramEndDate() ) { - hql += "and pi.enrollmentDate <= '" + getMediumDateString( params.getProgramEndDate() ) + "' "; + hql += hlp.whereAnd() + "pi.enrollmentDate <= '" + getMediumDateString( params.getProgramEndDate() ) + "'"; } hql += ")"; @@ -369,13 +381,13 @@ else if ( params.isOrganisationUnitMode( OrganisationUnitSelectionMode.CHILDREN ) ) { Set orgUnits = new HashSet<>(); - + for ( OrganisationUnit orgUnit : params.getOrganisationUnits() ) { orgUnits.add( orgUnit ); orgUnits.addAll( orgUnit.getChildren() ); } - + sql += hlp.whereAnd() + " tei.organisationunitid in (" + getCommaDelimitedString( getIdentifiers( orgUnits ) ) + ") "; } === modified file 'dhis-2/dhis-services/dhis-service-core/src/main/resources/org/hisp/dhis/organisationunit/hibernate/OrganisationUnit.hbm.xml' --- dhis-2/dhis-services/dhis-service-core/src/main/resources/org/hisp/dhis/organisationunit/hibernate/OrganisationUnit.hbm.xml 2015-06-24 06:17:15 +0000 +++ dhis-2/dhis-services/dhis-service-core/src/main/resources/org/hisp/dhis/organisationunit/hibernate/OrganisationUnit.hbm.xml 2015-07-07 14:54:50 +0000 @@ -28,6 +28,8 @@ + + === modified file 'dhis-2/dhis-support/dhis-support-commons/src/main/java/org/hisp/dhis/commons/util/SqlHelper.java' --- dhis-2/dhis-support/dhis-support-commons/src/main/java/org/hisp/dhis/commons/util/SqlHelper.java 2015-07-07 06:11:54 +0000 +++ dhis-2/dhis-support/dhis-support-commons/src/main/java/org/hisp/dhis/commons/util/SqlHelper.java 2015-07-07 14:54:50 +0000 @@ -33,7 +33,7 @@ */ public class SqlHelper { - private boolean whereAndInvoked = false; + private boolean invoked = false; private boolean includeSpaces = false; @@ -51,9 +51,45 @@ */ public String whereAnd() { - String str = whereAndInvoked ? "and" : "where"; - - whereAndInvoked = true; + String str = invoked ? "and" : "where"; + + invoked = true; + + return includeSpaces ? " " + str + " " : str; + } + + /** + * Returns "where" the first time it is invoked, then "or" for subsequent invocations. + */ + public String whereOr() + { + String str = invoked ? "or" : "where"; + + invoked = true; + + return includeSpaces ? " " + str + " " : str; + } + + /** + * Returns "" the first time it is invoked, then "and" for subsequent invocations. + */ + public String and() + { + String str = invoked ? "and" : ""; + + invoked = true; + + return includeSpaces ? " " + str + " " : str; + } + + /** + * Returns "" the first time it is invoked, then "or" for subsequent invocations. + */ + public String or() + { + String str = invoked ? "or" : ""; + + invoked = true; return includeSpaces ? " " + str + " " : str; }