=== modified file 'dhis-2/dhis-api/src/main/java/org/hisp/dhis/resourcetable/ResourceTableStore.java' --- dhis-2/dhis-api/src/main/java/org/hisp/dhis/resourcetable/ResourceTableStore.java 2014-03-18 08:10:10 +0000 +++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/resourcetable/ResourceTableStore.java 2014-05-04 09:55:24 +0000 @@ -82,6 +82,8 @@ * @param groupSets the group sets. */ void createDataElementGroupSetStructure( List groupSets ); + + void populateDataElementGroupSetStructure( List groupSets ); /** * Creates table. @@ -90,6 +92,8 @@ */ void createIndicatorGroupSetStructure( List groupSets ); + void populateIndicatorGroupSetStructure( List groupSets ); + /** * Creates table. * @@ -97,6 +101,8 @@ */ void createOrganisationUnitGroupSetStructure( List groupSets ); + void populateOrganisationUnitGroupSetStructure( List groupSets ); + /** * Creates table. * === modified file 'dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/DefaultResourceTableService.java' --- dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/DefaultResourceTableService.java 2014-04-06 13:41:19 +0000 +++ dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/DefaultResourceTableService.java 2014-05-04 09:55:24 +0000 @@ -53,16 +53,12 @@ import org.hisp.dhis.dataelement.DataElementCategoryOption; import org.hisp.dhis.dataelement.DataElementCategoryOptionCombo; import org.hisp.dhis.dataelement.DataElementCategoryService; -import org.hisp.dhis.dataelement.DataElementGroup; import org.hisp.dhis.dataelement.DataElementGroupSet; import org.hisp.dhis.dataelement.DataElementService; import org.hisp.dhis.dataset.DataSet; -import org.hisp.dhis.indicator.Indicator; -import org.hisp.dhis.indicator.IndicatorGroup; import org.hisp.dhis.indicator.IndicatorGroupSet; import org.hisp.dhis.indicator.IndicatorService; import org.hisp.dhis.organisationunit.OrganisationUnit; -import org.hisp.dhis.organisationunit.OrganisationUnitGroup; import org.hisp.dhis.organisationunit.OrganisationUnitGroupService; import org.hisp.dhis.organisationunit.OrganisationUnitGroupSet; import org.hisp.dhis.organisationunit.OrganisationUnitService; @@ -73,9 +69,6 @@ import org.hisp.dhis.period.PeriodType; import org.hisp.dhis.resourcetable.statement.CreateCategoryOptionGroupSetTableStatement; import org.hisp.dhis.resourcetable.statement.CreateCategoryTableStatement; -import org.hisp.dhis.resourcetable.statement.CreateDataElementGroupSetTableStatement; -import org.hisp.dhis.resourcetable.statement.CreateIndicatorGroupSetTableStatement; -import org.hisp.dhis.resourcetable.statement.CreateOrganisationUnitGroupSetTableStatement; import org.hisp.dhis.sqlview.SqlView; import org.hisp.dhis.sqlview.SqlViewService; import org.springframework.transaction.annotation.Transactional; @@ -284,45 +277,13 @@ @Transactional public void generateDataElementGroupSetTable() { - // --------------------------------------------------------------------- - // Create table - // --------------------------------------------------------------------- - - List dataElements = new ArrayList( dataElementService.getAllDataElements() ); - - Collections.sort( dataElements, IdentifiableObjectNameComparator.INSTANCE ); - List groupSets = new ArrayList( dataElementService.getAllDataElementGroupSets() ); Collections.sort( groupSets, IdentifiableObjectNameComparator.INSTANCE ); resourceTableStore.createDataElementGroupSetStructure( groupSets ); - // --------------------------------------------------------------------- - // Populate table - // --------------------------------------------------------------------- - - List batchArgs = new ArrayList(); - - for ( DataElement dataElement : dataElements ) - { - List values = new ArrayList(); - - values.add( dataElement.getId() ); - values.add( dataElement.getName() ); - - for ( DataElementGroupSet groupSet : groupSets ) - { - DataElementGroup group = groupSet.getGroup( dataElement ); - - values.add( group != null ? group.getName() : null ); - values.add( group != null ? group.getUid() : null ); - } - - batchArgs.add( values.toArray() ); - } - - resourceTableStore.batchUpdate( ( groupSets.size() * 2 ) + 2, CreateDataElementGroupSetTableStatement.TABLE_NAME, batchArgs ); + resourceTableStore.populateDataElementGroupSetStructure( groupSets ); log.info( "Data element group set table generated" ); } @@ -334,45 +295,13 @@ @Transactional public void generateIndicatorGroupSetTable() { - // --------------------------------------------------------------------- - // Create table - // --------------------------------------------------------------------- - - List indicators = new ArrayList( indicatorService.getAllIndicators() ); - - Collections.sort( indicators, IdentifiableObjectNameComparator.INSTANCE ); - List groupSets = new ArrayList( indicatorService.getAllIndicatorGroupSets() ); Collections.sort( groupSets, IdentifiableObjectNameComparator.INSTANCE ); resourceTableStore.createIndicatorGroupSetStructure( groupSets ); - // --------------------------------------------------------------------- - // Populate table - // --------------------------------------------------------------------- - - List batchArgs = new ArrayList(); - - for ( Indicator indicator : indicators ) - { - List values = new ArrayList(); - - values.add( indicator.getId() ); - values.add( indicator.getName() ); - - for ( IndicatorGroupSet groupSet : groupSets ) - { - IndicatorGroup group = groupSet.getGroup( indicator ); - - values.add( group != null ? group.getName() : null ); - values.add( group != null ? group.getUid() : null ); - } - - batchArgs.add( values.toArray() ); - } - - resourceTableStore.batchUpdate( ( groupSets.size() * 2 ) + 2, CreateIndicatorGroupSetTableStatement.TABLE_NAME, batchArgs ); + resourceTableStore.populateIndicatorGroupSetStructure( groupSets ); log.info( "Indicator group set table generated" ); } @@ -384,15 +313,6 @@ @Transactional public void generateOrganisationUnitGroupSetTable() { - // --------------------------------------------------------------------- - // Create table - // --------------------------------------------------------------------- - - List units = new ArrayList( organisationUnitService - .getAllOrganisationUnits() ); - - Collections.sort( units, IdentifiableObjectNameComparator.INSTANCE ); - List groupSets = new ArrayList( organisationUnitGroupService.getAllOrganisationUnitGroupSets() ); @@ -400,31 +320,7 @@ resourceTableStore.createOrganisationUnitGroupSetStructure( groupSets ); - // --------------------------------------------------------------------- - // Populate table - // --------------------------------------------------------------------- - - List batchArgs = new ArrayList(); - - for ( OrganisationUnit unit : units ) - { - List values = new ArrayList(); - - values.add( unit.getId() ); - values.add( unit.getName() ); - - for ( OrganisationUnitGroupSet groupSet : groupSets ) - { - OrganisationUnitGroup group = groupSet.getGroup( unit ); - - values.add( group != null ? group.getName() : null ); - values.add( group != null ? group.getUid() : null ); - } - - batchArgs.add( values.toArray() ); - } - - resourceTableStore.batchUpdate( ( groupSets.size() * 2 ) + 2, CreateOrganisationUnitGroupSetTableStatement.TABLE_NAME, batchArgs ); + resourceTableStore.populateOrganisationUnitGroupSetStructure( groupSets ); log.info( "Organisation unit group set table generated" ); } === modified file 'dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/jdbc/JdbcResourceTableStore.java' --- dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/jdbc/JdbcResourceTableStore.java 2014-04-09 18:10:48 +0000 +++ dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/jdbc/JdbcResourceTableStore.java 2014-05-04 09:55:24 +0000 @@ -46,6 +46,7 @@ import org.hisp.dhis.resourcetable.statement.CreateDataElementGroupSetTableStatement; import org.hisp.dhis.resourcetable.statement.CreateIndicatorGroupSetTableStatement; import org.hisp.dhis.resourcetable.statement.CreateOrganisationUnitGroupSetTableStatement; +import org.hisp.dhis.system.util.TextUtils; import org.springframework.jdbc.BadSqlGrammarException; import org.springframework.jdbc.core.JdbcTemplate; @@ -200,6 +201,35 @@ jdbcTemplate.execute( statement.getStatement() ); } + public void populateDataElementGroupSetStructure( List groupSets ) + { + String sql = + "insert into " + CreateDataElementGroupSetTableStatement.TABLE_NAME + " " + + "select d.dataelementid as dataelementid, d.name as dataelementname, "; + + for ( DataElementGroupSet groupSet : groupSets ) + { + sql += "(" + + "select deg.name from dataelementgroup deg " + + "inner join dataelementgroupmembers degm on degm.dataelementgroupid = deg.dataelementgroupid and degm.dataelementid = d.dataelementid " + + "inner join dataelementgroupsetmembers degsm on degsm.dataelementgroupid = degm.dataelementgroupid and degsm.dataelementgroupsetid = " + groupSet.getId() + " " + + "limit 1) as " + statementBuilder.columnQuote( groupSet.getName() ) + ", "; + + sql += "(" + + "select deg.uid from dataelementgroup deg " + + "inner join dataelementgroupmembers degm on degm.dataelementgroupid = deg.dataelementgroupid and degm.dataelementid = d.dataelementid " + + "inner join dataelementgroupsetmembers degsm on degsm.dataelementgroupid = degm.dataelementgroupid and degsm.dataelementgroupsetid = " + groupSet.getId() + " " + + "limit 1) as " + statementBuilder.columnQuote( groupSet.getUid() ) + ", "; + } + + sql = TextUtils.removeLastComma( sql ) + " "; + sql += "from dataelement d"; + + log.info( "Populate data element group set structure SQL: " + sql ); + + jdbcTemplate.execute( sql ); + } + // ------------------------------------------------------------------------- // DataElementGroupSetTable // ------------------------------------------------------------------------- @@ -219,6 +249,35 @@ jdbcTemplate.execute( statement.getStatement() ); } + + public void populateIndicatorGroupSetStructure( List groupSets ) + { + String sql = + "insert into " + CreateIndicatorGroupSetTableStatement.TABLE_NAME + " " + + "select i.indicatorid as indicatorid, i.name as indicatorname, "; + + for ( IndicatorGroupSet groupSet : groupSets ) + { + sql += "(" + + "select ig.name from indicatorgroup ig " + + "inner join indicatorgroupmembers igm on igm.indicatorgroupid = ig.indicatorgroupid and igm.indicatorid = i.indicatorid " + + "inner join indicatorgroupsetmembers igsm on igsm.indicatorgroupid = igm.indicatorgroupid and igsm.indicatorgroupsetid = " + groupSet.getId() + " " + + "limit 1) as " + statementBuilder.columnQuote( groupSet.getName() ) + ", "; + + sql += "(" + + "select ig.uid from indicatorgroup ig " + + "inner join indicatorgroupmembers igm on igm.indicatorgroupid = ig.indicatorgroupid and igm.indicatorid = i.indicatorid " + + "inner join indicatorgroupsetmembers igsm on igsm.indicatorgroupid = igm.indicatorgroupid and igsm.indicatorgroupsetid = " + groupSet.getId() + " " + + "limit 1) as " + statementBuilder.columnQuote( groupSet.getUid() ) + ", "; + } + + sql = TextUtils.removeLastComma( sql ) + " "; + sql += "from indicator i"; + + log.info( "Populate indicator group set structure SQL: " + sql ); + + jdbcTemplate.execute( sql ); + } // ------------------------------------------------------------------------- // OrganisationUnitGroupSetTable @@ -239,6 +298,35 @@ jdbcTemplate.execute( statement.getStatement() ); } + + public void populateOrganisationUnitGroupSetStructure( List groupSets ) + { + String sql = + "insert into " + CreateOrganisationUnitGroupSetTableStatement.TABLE_NAME + " " + + "select ou.organisationunitid as organisationunitid, ou.name as organisationunitname, "; + + for ( OrganisationUnitGroupSet groupSet : groupSets ) + { + sql += "(" + + "select oug.name from orgunitgroup oug " + + "inner join orgunitgroupmembers ougm on ougm.orgunitgroupid = oug.orgunitgroupid and ougm.organisationunitid = ou.organisationunitid " + + "inner join orgunitgroupsetmembers ougsm on ougsm.orgunitgroupid = ougm.orgunitgroupid and ougsm.orgunitgroupsetid = " + groupSet.getId() + " " + + "limit 1) as " + statementBuilder.columnQuote( groupSet.getName() ) + ", "; + + sql += "(" + + "select oug.uid from orgunitgroup oug " + + "inner join orgunitgroupmembers ougm on ougm.orgunitgroupid = oug.orgunitgroupid and ougm.organisationunitid = ou.organisationunitid " + + "inner join orgunitgroupsetmembers ougsm on ougsm.orgunitgroupid = ougm.orgunitgroupid and ougsm.orgunitgroupsetid = " + groupSet.getId() + " " + + "limit 1) as " + statementBuilder.columnQuote( groupSet.getUid() ) + ", "; + } + + sql = TextUtils.removeLastComma( sql ) + " "; + sql += "from organisationunit ou"; + + log.info( "Populate organisation unit group set structure SQL: " + sql ); + + jdbcTemplate.execute( sql ); + } // ------------------------------------------------------------------------- // CategoryTable === modified file 'dhis-2/dhis-web/dhis-web-api/src/main/java/org/hisp/dhis/api/controller/ResourceTableController.java' --- dhis-2/dhis-web/dhis-web-api/src/main/java/org/hisp/dhis/api/controller/ResourceTableController.java 2014-03-18 08:10:10 +0000 +++ dhis-2/dhis-web/dhis-web-api/src/main/java/org/hisp/dhis/api/controller/ResourceTableController.java 2014-05-04 09:55:24 +0000 @@ -82,11 +82,13 @@ @RequestParam(required=false) boolean skipResourceTables, @RequestParam(required=false) boolean skipAggregate, @RequestParam(required=false) boolean skipEvents, + @RequestParam(required=false) boolean last3Years, HttpServletResponse response ) { analyticsTableTask.setSkipResourceTables( skipResourceTables ); analyticsTableTask.setSkipAggregate( skipAggregate ); analyticsTableTask.setSkipEvents( skipEvents ); + analyticsTableTask.setLast3Years( last3Years ); analyticsTableTask.setTaskId( new TaskId( TaskCategory.DATAMART, currentUserService.getCurrentUser() ) ); scheduler.executeTask( analyticsTableTask );