=== modified file 'dhis-2/dhis-api/src/main/java/org/hisp/dhis/databrowser/DataBrowserTable.java' --- dhis-2/dhis-api/src/main/java/org/hisp/dhis/databrowser/DataBrowserTable.java 2010-09-01 09:34:51 +0000 +++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/databrowser/DataBrowserTable.java 2011-02-16 13:25:39 +0000 @@ -43,7 +43,7 @@ /** * A List of List with integers to simulate a 2D array. */ - private List> counts = new Vector>(); + private List> counts = new Vector>(); /** * A List of the MetaValues for columns. @@ -85,7 +85,7 @@ { Integer rowId = resultSet.getInt( 1 ); String rowName = resultSet.getString( 2 ); - List rowItem = new Vector(); + List rowItem = new Vector(); counts.add( rowItem ); addRowNameAndId( rowId, rowName ); } @@ -157,9 +157,9 @@ { if ( resultSet.isFirst() ) { - for ( List rowItem : this.counts ) + for ( List rowItem : this.counts ) { - rowItem.add( 0 ); + rowItem.add( "0" ); } if ( hasPeriodIds && hasColumnName ) { @@ -184,9 +184,9 @@ if ( makeEmptyCol ) { makeEmptyCol = false; - for ( List rowItem : this.counts ) + for ( List rowItem : this.counts ) { - rowItem.add( 0 ); + rowItem.add( "0" ); } if ( hasColumnName ) { @@ -196,8 +196,8 @@ } String name = resultSet.getString( 2 ); - int value = resultSet.getInt( 3 ); - List rowItem = getRowBasedOnRowName( name ); + String value = resultSet.getString( 3 ); + List rowItem = getRowBasedOnRowName( name ); rowItem.remove( rowItem.size() - 1 ); rowItem.add( value ); countRows++; @@ -219,9 +219,9 @@ public void addZeroColumn() { this.addColumnName( "counts_of_aggregated_values" ); - for ( List rowItem : this.counts ) + for ( List rowItem : this.counts ) { - rowItem.add( 0 ); + rowItem.add( "0" ); } } @@ -276,7 +276,7 @@ * @param rowName the rowName to check * @return index in rowMeta */ - public List getRowBasedOnRowName( String rowName ) + public List getRowBasedOnRowName( String rowName ) { int rowIndex = rowMeta.indexOf( new MetaValue( rowName ) ); return counts.get( rowIndex ); @@ -292,9 +292,10 @@ * @param y * @return */ - public Integer getCountFromRowAndColumnIndex( int x, int y ) + public String getCountFromRowAndColumnIndex( int x, int y ) { return counts.get( x ).get( y ); + } public long getQueryTime() @@ -312,12 +313,12 @@ this.queryTime += queryTime; } - public List> getCounts() + public List> getCounts() { return counts; } - public void setCounts( List> counts ) + public void setCounts( List> counts ) { this.counts = counts; } @@ -380,12 +381,12 @@ ret += "\n"; Iterator it = this.getRows().iterator(); - for ( List col : this.getCounts() ) + for ( List col : this.getCounts() ) { MetaValue rowMeta = it.next(); ret += "|" + rowMeta.getName(); - for ( Integer rowItem : col ) + for ( String rowItem : col ) { ret += "|" + rowItem; } === modified file 'dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/databrowser/DefaultDataBrowserPdfService.java' --- dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/databrowser/DefaultDataBrowserPdfService.java 2010-12-26 21:40:52 +0000 +++ dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/databrowser/DefaultDataBrowserPdfService.java 2011-02-16 13:25:39 +0000 @@ -186,7 +186,7 @@ int i = 0; Iterator rowIt = dataBrowserTable.getRows().iterator(); - for ( List col : dataBrowserTable.getCounts() ) + for ( List col : dataBrowserTable.getCounts() ) { i = i + 1; MetaValue rowMeta = rowIt.next(); @@ -205,13 +205,13 @@ table.addCell( cell ); - for ( int rowItem : col ) + for ( String rowItem : col ) { Phrase phrase = new Phrase( new Integer( rowItem ).toString(), FontFactory.getFont( FontFactory.HELVETICA, fontSize, Font.NORMAL, Color.BLACK ) ); // Color zero values as bold red - if ( rowItem == 0 ) + if ( rowItem.trim().matches("0") ) { phrase.getFont().setStyle( Font.BOLD ); phrase.getFont().setColor( Color.RED ); === modified file 'dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/databrowser/DefaultDataBrowserService.java' --- dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/databrowser/DefaultDataBrowserService.java 2011-01-16 07:12:01 +0000 +++ dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/databrowser/DefaultDataBrowserService.java 2011-02-16 13:25:39 +0000 @@ -41,7 +41,6 @@ import org.hisp.dhis.period.Period; import org.hisp.dhis.period.PeriodService; import org.hisp.dhis.period.PeriodType; -import org.hisp.dhis.jdbc.StatementBuilder; import org.hisp.dhis.period.comparator.AscendingPeriodComparator; import org.hisp.dhis.system.util.DateUtils; === modified file 'dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/databrowser/jdbc/StatementManagerDataBrowserStore.java' --- dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/databrowser/jdbc/StatementManagerDataBrowserStore.java 2011-02-14 08:56:35 +0000 +++ dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/databrowser/jdbc/StatementManagerDataBrowserStore.java 2011-02-16 13:25:39 +0000 @@ -8,7 +8,8 @@ import java.util.ArrayList; import java.util.Iterator; - +import org.apache.commons.logging.Log; +import org.apache.commons.logging.LogFactory; import org.hisp.dhis.jdbc.StatementBuilder; import org.amplecode.quick.StatementHolder; @@ -18,591 +19,550 @@ import org.hisp.dhis.organisationunit.OrganisationUnitService; import org.hisp.dhis.system.util.Timer; - - /** * @author joakibj, martinwa, briane, eivinhb * @version $Id StatementManagerDataBrowserStore.java 2010-04-06 Jason * Pickering, Dang Duy Hieu$ */ -public class StatementManagerDataBrowserStore - implements DataBrowserStore -{ - // ------------------------------------------------------------------------- - // Dependencies - // ------------------------------------------------------------------------- - - private StatementManager statementManager; - - - - public void setStatementManager( StatementManager statementManager ) - { - this.statementManager = statementManager; - } - - private OrganisationUnitService organisationUnitService; - - public void setOrganisationUnitService( OrganisationUnitService organisationUnitService ) - { - this.organisationUnitService = organisationUnitService; - } - private StatementBuilder statementBuilder ; - - public void setStatementBuilder( StatementBuilder statementBuilder ) - { - this.statementBuilder = statementBuilder; - } - - // ------------------------------------------------------------------------- - // DataBrowserStore implementation - // ------------------------------------------------------------------------- - - public DataBrowserTable getDataSetsBetweenPeriods( List betweenPeriodIds ) - { - - StringBuffer sqlsb = new StringBuffer(); - - sqlsb.append( "(SELECT d.datasetid AS ID, d.name AS DataSet, COUNT(*) AS counts_of_aggregated_values " ); - sqlsb.append( "FROM datavalue dv " ); - sqlsb.append( "JOIN datasetmembers dsm ON (dv.dataelementid = dsm.dataelementid) " ); - sqlsb.append( "JOIN dataset d ON (d.datasetid = dsm.datasetid) " ); - sqlsb.append( "JOIN period p ON (dv.periodid = p.periodid) " ); - sqlsb.append( "WHERE dv.periodid IN " + splitListHelper( betweenPeriodIds ) + " " ); - sqlsb.append( "GROUP BY d.datasetid, d.name " ); - sqlsb.append( "ORDER BY counts_of_aggregated_values DESC)" ); - - - // Gets all the dataSets in a period with a count attached to the - // dataSet. The table returned has only 2 columns. They are created here - // in this method directly - - List columnNames = new ArrayList(); - columnNames.add("drilldown_data_set"); - columnNames.add("counts_of_aggregated_values"); - DataBrowserTable table = getTablefromSQL(sqlsb, columnNames ); - - return table; - } - - public DataBrowserTable getDataElementGroupsBetweenPeriods( List betweenPeriodIds ) - { - StringBuffer sqlsb = new StringBuffer(); - - sqlsb.append( "(SELECT d.dataelementgroupid AS ID, d.name AS DataElementGroup, COUNT(*) AS counts_of_aggregated_values " ); - sqlsb.append( "FROM datavalue dv " ); - sqlsb.append( "JOIN dataelementgroupmembers degm ON (dv.dataelementid = degm.dataelementid)" ); - sqlsb.append( "JOIN dataelementgroup d ON (d.dataelementgroupid = degm.dataelementgroupid) " ); - sqlsb.append( "WHERE dv.periodid IN " + splitListHelper( betweenPeriodIds ) + " " ); - sqlsb.append( "GROUP BY d.dataelementgroupid, d.name " ); - sqlsb.append( "ORDER BY counts_of_aggregated_values DESC)" ); - - - List columnNames = new ArrayList(); - columnNames.add("drilldown_data_element_group"); - columnNames.add("counts_of_aggregated_values"); - DataBrowserTable table = getTablefromSQL(sqlsb, columnNames ); - - - return table; - } - - public DataBrowserTable getOrgUnitGroupsBetweenPeriods( List betweenPeriodIds ) - { - - - StringBuffer sqlsb = new StringBuffer(); - sqlsb.append( "(SELECT oug.orgunitgroupid, oug.name AS OrgUnitGroup, COUNT(*) AS counts_of_aggregated_values " ); - sqlsb.append( "FROM orgunitgroup oug " ); - sqlsb.append( "JOIN orgunitgroupmembers ougm ON oug.orgunitgroupid = ougm.orgunitgroupid " ); - sqlsb.append( "JOIN organisationunit ou ON ougm.organisationunitid = ou.organisationunitid " ); - sqlsb.append( "JOIN datavalue dv ON ou.organisationunitid = dv.sourceid " ); - sqlsb.append( "WHERE dv.periodid IN " + splitListHelper( betweenPeriodIds ) + " " ); - sqlsb.append( "GROUP BY oug.orgunitgroupid, oug.name " ); - sqlsb.append( "ORDER BY counts_of_aggregated_values DESC) " ); - - - List columnNames = new ArrayList(); - columnNames.add("drilldown_orgunit_group"); - columnNames.add("counts_of_aggregated_values"); - DataBrowserTable table = getTablefromSQL(sqlsb, columnNames); - - - return table; - } - - public void setDataElementStructureForDataSetBetweenPeriods( DataBrowserTable table, Integer dataSetId, - List betweenPeriods ) - { - - StringBuffer sqlsb = new StringBuffer(); - - sqlsb.append( "(SELECT de.dataelementid, de.name AS DataElement " ); - sqlsb.append( "FROM dataelement de " ); - sqlsb.append( "JOIN datasetmembers dsm ON (de.dataelementid = dsm.dataelementid) " ); - sqlsb.append( "WHERE dsm.datasetid = '" + dataSetId + "' " ); - sqlsb.append( "ORDER BY de.name) " ); - - List columnNames = new ArrayList(); - columnNames.add( "drilldown_data_element" ); - setTableStructure(table, sqlsb, columnNames ); - - } - - public void setDataElementGroupStructureForOrgUnitGroupBetweenPeriods( DataBrowserTable table, - Integer orgUnitGroupId, List betweenPeriods ) - { - - StringBuffer sqlsb = new StringBuffer(); - - sqlsb.append( "(SELECT deg.dataelementgroupid, deg.name AS DataElementGroup " ); - sqlsb.append( "FROM dataelementgroup deg " ); - sqlsb.append( "JOIN dataelementgroupmembers degm ON deg.dataelementgroupid = degm.dataelementgroupid " ); - sqlsb.append( "JOIN datavalue dv ON degm.dataelementid = dv.dataelementid " ); - sqlsb.append( "JOIN organisationunit ou ON dv.sourceid = ou.organisationunitid " ); - sqlsb.append( "JOIN orgunitgroupmembers ougm ON ou.organisationunitid = ougm.organisationunitid " ); - sqlsb.append( "WHERE ougm.orgunitgroupid = '" + orgUnitGroupId + "' " ); - sqlsb.append( "GROUP BY deg.dataelementgroupid, deg.name " ); - sqlsb.append( "ORDER BY deg.name ASC) " ); - - List columnNames = new ArrayList(); - columnNames.add( "drilldown_data_element_group" ); - setTableStructure(table, sqlsb, columnNames ); - - } - - public void setDataElementStructureForDataElementGroupBetweenPeriods( DataBrowserTable table, - Integer dataElementGroupId, List betweenPeriods ) - { - - StringBuffer sqlsb = new StringBuffer(); - - sqlsb.append( "(SELECT de.dataelementid, de.name AS DataElement " ); - sqlsb.append( "FROM dataelement de " ); - sqlsb.append( "JOIN dataelementgroupmembers degm ON (de.dataelementid = degm.dataelementid) " ); - sqlsb.append( "WHERE degm.dataelementgroupid = '" + dataElementGroupId + "' " ); - sqlsb.append( "GROUP BY de.dataelementid, de.name " ); - sqlsb.append( "ORDER BY de.name) " ); - - List columnNames = new ArrayList(); - columnNames.add( "drilldown_data_element" ); - setTableStructure(table, sqlsb, columnNames ); - } - - public void setStructureForOrgUnitBetweenPeriods( DataBrowserTable table, Integer orgUnitParent, - List betweenPeriods ) - { - - StringBuffer sqlsb = new StringBuffer(); - - sqlsb.append( "(SELECT o.organisationunitid, o.name AS OrganisationUnit " ); - sqlsb.append( "FROM organisationunit o " ); - sqlsb.append( "WHERE o.parentid = '" + orgUnitParent + "' " ); - sqlsb.append( "ORDER BY o.name)" ); - - List columnNames = new ArrayList(); - columnNames.add( "drilldown_data_element" ); - setTableStructure(table, sqlsb, columnNames ); - - } - - public void setDataElementStructureForOrgUnitBetweenPeriods( DataBrowserTable table, Integer orgUnitId, - List betweenPeriods ) - { - - StringBuffer sqlsb = new StringBuffer(); - String sql = statementBuilder.queryDataElementStructureForOrgUnitBetweenPeriods(); - sqlsb.append( sql ); - List columnNames = new ArrayList(); - columnNames.add( "drilldown_data_element" ); - setTableStructure(table, sqlsb, columnNames ); - } - - public Integer setCountDataElementsForDataSetBetweenPeriods( DataBrowserTable table, Integer dataSetId, - List betweenPeriodIds ) - { - StatementHolder holder = statementManager.getHolder(); - - // Here we uses a for loop to create one big sql statement using UNION. - // This is done because the count and GROUP BY parts of this query can't - // be done in another way. The alternative to this method is to actually - // query the database as many time than betweenPeriodIds.size() tells. - // But the overhead cost of doing that is bigger than the creation of - // this UNION query. - Integer numResults = 0; - StringBuffer sqlsb = new StringBuffer(); - - int i = 0; - for ( Integer periodId : betweenPeriodIds ) - { - i++; - - sqlsb.append( "(SELECT de.dataelementid, de.name AS dataelement, COUNT(dv.value) AS counts_of_aggregated_values, p.periodid AS PeriodId, p.startdate AS ColumnHeader " ); - sqlsb.append( "FROM dataelement de JOIN datavalue dv ON (de.dataelementid = dv.dataelementid) " ); - sqlsb.append( "JOIN datasetmembers dsm ON (de.dataelementid = dsm.dataelementid) " ); - sqlsb.append( "JOIN period p ON (dv.periodid = p.periodid) " ); - sqlsb.append( "WHERE dsm.datasetid = '" + dataSetId + "' AND dv.periodid = '" + periodId + "' " ); - sqlsb.append( "GROUP BY de.dataelementid, de.name, p.periodid, p.startDate)" ); - - sqlsb.append( i == betweenPeriodIds.size() ? "ORDER BY ColumnHeader" : " UNION " ); - } - - try - { - Timer timer = new Timer(); - timer.start(); - ResultSet resultSet = getScrollableResult( sqlsb.toString(), holder ); - table.addQueryTime( timer.getMilliSec() ); - - - table.incrementQueryCount(); - - numResults = table.addColumnToAllRows( resultSet ); - } - catch ( SQLException e ) - { - throw new RuntimeException( "Failed to get aggregated data value\n" + sqlsb.toString(), e ); - } - finally - { - holder.close(); - } - - return numResults; - } - - public Integer setCountDataElementsForDataElementGroupBetweenPeriods( DataBrowserTable table, - Integer dataElementGroupId, List betweenPeriodIds ) - { - - StringBuffer sqlsb = new StringBuffer(); - - int i = 0; - for ( Integer periodid : betweenPeriodIds ) - { - i++; - - sqlsb.append( "(SELECT de.dataelementid, de.name AS DataElement, COUNT(dv.value) AS counts_of_aggregated_values, p.periodid AS PeriodId, p.startDate AS ColumnHeader " ); - sqlsb.append( "FROM dataelement de JOIN datavalue dv ON (de.dataelementid = dv.dataelementid) " ); - sqlsb.append( "JOIN dataelementgroupmembers degm ON (de.dataelementid = degm.dataelementid) " ); - sqlsb.append( "JOIN period p ON (dv.periodid = p.periodid) " ); - sqlsb.append( "WHERE degm.dataelementgroupid = '" + dataElementGroupId + "' " ); - sqlsb.append( "AND dv.periodid = '" + periodid + "' " ); - sqlsb.append( "GROUP BY de.dataelementid, de.name, p.periodid, p.startDate) " ); - - sqlsb.append( i == betweenPeriodIds.size() ? "ORDER BY ColumnHeader" : " UNION " ); - } - - return setCountFromSQL(table, sqlsb.toString()) ; - } - - public Integer setCountDataElementGroupsForOrgUnitGroupBetweenPeriods( DataBrowserTable table, - Integer orgUnitGroupId, List betweenPeriodIds ) - { - - StringBuffer sqlsb = new StringBuffer(); - - int i = 0; - for ( Integer periodid : betweenPeriodIds ) - { - i++; - - sqlsb.append( "(SELECT deg.dataelementgroupid, deg.name, COUNT(dv.value) AS counts_of_aggregated_values, p.periodid AS PeriodId, p.startdate AS ColumnHeader " ); - sqlsb.append( "FROM dataelementgroup AS deg " ); - sqlsb.append( "INNER JOIN dataelementgroupmembers AS degm ON deg.dataelementgroupid = degm.dataelementgroupid " ); - sqlsb.append( "INNER JOIN datavalue AS dv ON degm.dataelementid = dv.dataelementid " ); - sqlsb.append( "INNER JOIN period AS p ON dv.periodid = p.periodid " ); - sqlsb.append( "INNER JOIN organisationunit AS ou ON dv.sourceid = ou.organisationunitid " ); - sqlsb.append( "INNER JOIN orgunitgroupmembers AS ougm ON ou.organisationunitid = ougm.organisationunitid " ); - sqlsb.append( "WHERE p.periodid = '" + periodid + "' AND ougm.orgunitgroupid = '" + orgUnitGroupId + "' " ); - sqlsb.append( "GROUP BY deg.dataelementgroupid,deg.name,p.periodid,p.startdate) " ); - - sqlsb.append( i == betweenPeriodIds.size() ? "ORDER BY ColumnHeader" : " UNION " ); - } - - - return setCountFromSQL(table, sqlsb.toString()) ; - } - - - public Integer setCountOrgUnitsBetweenPeriods( DataBrowserTable table, Integer orgUnitParent, - List betweenPeriodIds, Integer maxLevel ) - { - StatementHolder holder = statementManager.getHolder(); - - Integer numResults = 0; - StringBuffer sqlsbDescentdants = new StringBuffer(); - - this.setUpQueryForDrillDownDescendants( sqlsbDescentdants, orgUnitParent, betweenPeriodIds, maxLevel ); - - return setCountFromSQL(table, sqlsbDescentdants.toString()) ; - - } - - public Integer setCountDataElementsForOrgUnitBetweenPeriods( DataBrowserTable table, Integer orgUnitId, - List betweenPeriodIds ) - { - StatementHolder holder = statementManager.getHolder(); - - Integer numResults = 0; - String sql = statementBuilder.queryCountDataElementsForOrgUnitBetweenPeriods(orgUnitId, betweenPeriodIds); - - try - { - Timer timer = new Timer(); - timer.start(); - ResultSet resultSet = getScrollableResult( sql, holder ); - table.addQueryTime( timer.getMilliSec() ); - - - table.incrementQueryCount(); - - numResults = table.addColumnToAllRows( resultSet ); - } - catch ( SQLException e ) - { - throw new RuntimeException( "Failed to get aggregated data value\n" + sql, e ); - } - finally - { - holder.close(); - } - - return numResults; - } - - // ------------------------------------------------------------------------- - // Supportive methods - // ------------------------------------------------------------------------- - - /** - * Splits a list of integers by by comma. Use this method if you have a list - * that will be used in f.ins. a WHERE xxx IN (list) clause in SQL. - * - * @param List list of Integers - * @return the list as a string splitted by a comma. - */ - private String splitListHelper( List list ) - { - StringBuffer sb = new StringBuffer(); - int count = 0; - - sb.append( "(" ); - for ( Integer i : list ) - { - sb.append( i ); - - count++; - - if ( count < list.size() ) - { - sb.append( "," ); - } - } - sb.append( ")" ); - - return sb.toString(); - } - - /** - * Uses StatementManager to obtain a scrollable, read-only ResultSet based - * on the query string. - * - * @param sql the query - * @param holder the StatementHolder object - * @return null or the ResultSet - */ - private ResultSet getScrollableResult( String sql, StatementHolder holder ) - throws SQLException - { - Connection con = holder.getConnection(); - Statement stm = con.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY ); - stm.execute( sql ); - return stm.getResultSet(); - } - - private String setUpQueryForDrillDownDescendants( StringBuffer sb, Integer orgUnitSelected, - List betweenPeriodIds, Integer maxLevel ) - { - if ( maxLevel == null ) - { - maxLevel = organisationUnitService.getMaxOfOrganisationUnitLevels(); - } - - int curLevel = organisationUnitService.getLevelOfOrganisationUnit( orgUnitSelected ); - int loopSize = betweenPeriodIds.size(); - - String descendantQuery = this.setUpQueryGetDescendants( curLevel, maxLevel, orgUnitSelected ); - int i = 0; - - for ( Integer periodid : betweenPeriodIds ) - { - i++; - /** - * Get all descendant level data for all orgunits under the - * selected, grouped by the next immediate children of the selected - * orgunit Looping through each period UNION construct appears to be - * faster with an index placed on periodid's rather than joining on - * periodids and then performing the aggregation step. - * - */ - sb.append( " SELECT a.parentid,a.name AS organisationunit,COUNT(*),p.periodid,p.startdate AS columnheader" ); - sb.append( " FROM datavalue dv" ); - sb.append( " INNER JOIN (SELECT DISTINCT x.parentid,x.childid,ou.name FROM(" + descendantQuery + ") x" ); - sb.append( " INNER JOIN organisationunit ou ON x.parentid=ou.organisationunitid) a ON dv.sourceid=a.childid" ); - sb.append( " INNER JOIN period p ON dv.periodid=p.periodid" ); - sb.append( " WHERE dv.periodid=" + periodid ); - sb.append( " GROUP BY a.parentid,a.name,p.periodid,p.startdate" ); - sb.append( i < loopSize ? " UNION " : "" ); - - } - sb.append( " ORDER BY columnheader,organisationunit" ); - - return sb.toString(); - } - - private String setUpQueryGetDescendants( int curLevel, int maxLevel, Integer orgUnitSelected ) - { - Integer childLevel = curLevel + 1; - Integer diffLevel = maxLevel - curLevel; - - // The immediate child level can probably be combined into the for loop - // but we need to clarify whether the selected unit should be present, - // and if so, how? - - final StringBuilder desc_query = new StringBuilder(); - - // Loop through each of the descendants until the diff level is reached - for ( int j = 0; j < diffLevel; j++ ) - { - desc_query.append( j != 0 ? " UNION " : "" ); - desc_query.append( "SELECT DISTINCT idlevel" + (childLevel) + " AS parentid," ); - desc_query.append( "idlevel" + (childLevel + j) + " AS childid" ); - desc_query.append( " FROM _orgunitstructure" ); - desc_query.append( " WHERE idlevel" + (curLevel) + "='" + orgUnitSelected + "'" ); - desc_query.append( " AND idlevel" + (childLevel + j) + "<>0" ); - } - - return desc_query.toString(); - } - - private DataBrowserTable getTablefromSQL(StringBuffer sqlsb, List columnNames ) - { - StatementHolder holder = statementManager.getHolder(); - DataBrowserTable table = new DataBrowserTable(); - Timer timer = new Timer(); - timer.start(); - try - { - ResultSet resultSet = getScrollableResult( sqlsb.toString(), holder ); - table.setQueryTime( timer.getMilliSec() ); - table.incrementQueryCount(); - - - - Iterator it = columnNames.iterator(); - while (it.hasNext()) - { - table.addColumnName(it.next().toString()); - - } - - table.createStructure( resultSet ); - table.addColumnToAllRows( resultSet ); - - } - catch (SQLException e) - { - throw new RuntimeException( "Failed to get aggregated data value\n" + sqlsb.toString(), e ); - } - catch(Exception e) - { - throw new RuntimeException("Oops. Somthing else went wrong" ,e); - - } - finally - { - holder.close(); - - } - - return table; - - } - - private void setTableStructure(DataBrowserTable table, StringBuffer sqlsb, List columnNames ) - { - StatementHolder holder = statementManager.getHolder(); - - Timer timer = new Timer(); - timer.start(); - try - { - ResultSet resultSet = getScrollableResult( sqlsb.toString(), holder ); - table.setQueryTime( timer.getMilliSec() ); - table.incrementQueryCount(); - - - - Iterator it = columnNames.iterator(); - while (it.hasNext()) - { - table.addColumnName(it.next().toString()); - - } - - table.createStructure( resultSet ); - - - } - catch (SQLException e) - { - throw new RuntimeException( "Failed to get aggregated data value\n" + sqlsb.toString(), e ); - } - catch(Exception e) - { - throw new RuntimeException("Oops. Somthing else went wrong" ,e); - - } - finally - { - holder.close(); - - } - - - } - - - private Integer setCountFromSQL(DataBrowserTable table, String sql ) - { - StatementHolder holder = statementManager.getHolder(); - - Integer numResults = 0; - - - try - { - Timer timer = new Timer(); - timer.start(); - ResultSet resultSet = getScrollableResult( sql, holder ); - table.addQueryTime( timer.getMilliSec() ); - - table.incrementQueryCount(); - - numResults = table.addColumnToAllRows( resultSet ); - } - catch ( SQLException e ) - { - throw new RuntimeException( "Failed to get aggregated data value\n" + sql, e ); - } - finally - { - holder.close(); - } - - return numResults; - } +public class StatementManagerDataBrowserStore implements DataBrowserStore { + // ------------------------------------------------------------------------- + // Dependencies + // ------------------------------------------------------------------------- + + private StatementManager statementManager; + + private static final Log log = LogFactory + .getLog(StatementManagerDataBrowserStore.class); + + public void setStatementManager(StatementManager statementManager) { + this.statementManager = statementManager; + } + + private OrganisationUnitService organisationUnitService; + + public void setOrganisationUnitService( + OrganisationUnitService organisationUnitService) { + this.organisationUnitService = organisationUnitService; + } + + private StatementBuilder statementBuilder; + + public void setStatementBuilder(StatementBuilder statementBuilder) { + this.statementBuilder = statementBuilder; + } + + // ------------------------------------------------------------------------- + // DataBrowserStore implementation + // ------------------------------------------------------------------------- + + public DataBrowserTable getDataSetsBetweenPeriods( + List betweenPeriodIds) { + + StringBuffer sqlsb = new StringBuffer(); + + sqlsb.append("(SELECT d.datasetid AS ID, d.name AS DataSet, COUNT(*) AS counts_of_aggregated_values "); + sqlsb.append("FROM datavalue dv "); + sqlsb.append("JOIN datasetmembers dsm ON (dv.dataelementid = dsm.dataelementid) "); + sqlsb.append("JOIN dataset d ON (d.datasetid = dsm.datasetid) "); + sqlsb.append("JOIN period p ON (dv.periodid = p.periodid) "); + sqlsb.append("WHERE dv.periodid IN " + + splitListHelper(betweenPeriodIds) + " "); + sqlsb.append("GROUP BY d.datasetid, d.name "); + sqlsb.append("ORDER BY counts_of_aggregated_values DESC)"); + + // Gets all the dataSets in a period with a count attached to the + // dataSet. The table returned has only 2 columns. They are created here + // in this method directly + + List columnNames = new ArrayList(); + columnNames.add("drilldown_data_set"); + columnNames.add("counts_of_aggregated_values"); + DataBrowserTable table = getTablefromSQL(sqlsb, columnNames); + + return table; + } + + public DataBrowserTable getDataElementGroupsBetweenPeriods( + List betweenPeriodIds) { + StringBuffer sqlsb = new StringBuffer(); + + sqlsb.append("(SELECT d.dataelementgroupid AS ID, d.name AS DataElementGroup, COUNT(*) AS counts_of_aggregated_values "); + sqlsb.append("FROM datavalue dv "); + sqlsb.append("JOIN dataelementgroupmembers degm ON (dv.dataelementid = degm.dataelementid)"); + sqlsb.append("JOIN dataelementgroup d ON (d.dataelementgroupid = degm.dataelementgroupid) "); + sqlsb.append("WHERE dv.periodid IN " + + splitListHelper(betweenPeriodIds) + " "); + sqlsb.append("GROUP BY d.dataelementgroupid, d.name "); + sqlsb.append("ORDER BY counts_of_aggregated_values DESC)"); + + List columnNames = new ArrayList(); + columnNames.add("drilldown_data_element_group"); + columnNames.add("counts_of_aggregated_values"); + DataBrowserTable table = getTablefromSQL(sqlsb, columnNames); + + return table; + } + + public DataBrowserTable getOrgUnitGroupsBetweenPeriods( + List betweenPeriodIds) { + + StringBuffer sqlsb = new StringBuffer(); + sqlsb.append("(SELECT oug.orgunitgroupid, oug.name AS OrgUnitGroup, COUNT(*) AS counts_of_aggregated_values "); + sqlsb.append("FROM orgunitgroup oug "); + sqlsb.append("JOIN orgunitgroupmembers ougm ON oug.orgunitgroupid = ougm.orgunitgroupid "); + sqlsb.append("JOIN organisationunit ou ON ougm.organisationunitid = ou.organisationunitid "); + sqlsb.append("JOIN datavalue dv ON ou.organisationunitid = dv.sourceid "); + sqlsb.append("WHERE dv.periodid IN " + + splitListHelper(betweenPeriodIds) + " "); + sqlsb.append("GROUP BY oug.orgunitgroupid, oug.name "); + sqlsb.append("ORDER BY counts_of_aggregated_values DESC) "); + + List columnNames = new ArrayList(); + columnNames.add("drilldown_orgunit_group"); + columnNames.add("counts_of_aggregated_values"); + DataBrowserTable table = getTablefromSQL(sqlsb, columnNames); + + return table; + } + + public void setDataElementStructureForDataSetBetweenPeriods( + DataBrowserTable table, Integer dataSetId, + List betweenPeriods) { + + StringBuffer sqlsb = new StringBuffer(); + + sqlsb.append("(SELECT de.dataelementid, de.name AS DataElement "); + sqlsb.append("FROM dataelement de "); + sqlsb.append("JOIN datasetmembers dsm ON (de.dataelementid = dsm.dataelementid) "); + sqlsb.append("WHERE dsm.datasetid = '" + dataSetId + "' "); + sqlsb.append("ORDER BY de.name) "); + + List columnNames = new ArrayList(); + columnNames.add("drilldown_data_element"); + setTableStructure(table, sqlsb, columnNames); + + } + + public void setDataElementGroupStructureForOrgUnitGroupBetweenPeriods( + DataBrowserTable table, Integer orgUnitGroupId, + List betweenPeriods) { + + StringBuffer sqlsb = new StringBuffer(); + + sqlsb.append("(SELECT deg.dataelementgroupid, deg.name AS DataElementGroup "); + sqlsb.append("FROM dataelementgroup deg "); + sqlsb.append("JOIN dataelementgroupmembers degm ON deg.dataelementgroupid = degm.dataelementgroupid "); + sqlsb.append("JOIN datavalue dv ON degm.dataelementid = dv.dataelementid "); + sqlsb.append("JOIN organisationunit ou ON dv.sourceid = ou.organisationunitid "); + sqlsb.append("JOIN orgunitgroupmembers ougm ON ou.organisationunitid = ougm.organisationunitid "); + sqlsb.append("WHERE ougm.orgunitgroupid = '" + orgUnitGroupId + "' "); + sqlsb.append("GROUP BY deg.dataelementgroupid, deg.name "); + sqlsb.append("ORDER BY deg.name ASC) "); + + List columnNames = new ArrayList(); + columnNames.add("drilldown_data_element_group"); + setTableStructure(table, sqlsb, columnNames); + + } + + public void setDataElementStructureForDataElementGroupBetweenPeriods( + DataBrowserTable table, Integer dataElementGroupId, + List betweenPeriods) { + + StringBuffer sqlsb = new StringBuffer(); + + sqlsb.append("(SELECT de.dataelementid, de.name AS DataElement "); + sqlsb.append("FROM dataelement de "); + sqlsb.append("JOIN dataelementgroupmembers degm ON (de.dataelementid = degm.dataelementid) "); + sqlsb.append("WHERE degm.dataelementgroupid = '" + dataElementGroupId + + "' "); + sqlsb.append("GROUP BY de.dataelementid, de.name "); + sqlsb.append("ORDER BY de.name) "); + + List columnNames = new ArrayList(); + columnNames.add("drilldown_data_element"); + setTableStructure(table, sqlsb, columnNames); + } + + public void setStructureForOrgUnitBetweenPeriods(DataBrowserTable table, + Integer orgUnitParent, List betweenPeriods) { + + StringBuffer sqlsb = new StringBuffer(); + + sqlsb.append("(SELECT o.organisationunitid, o.name AS OrganisationUnit "); + sqlsb.append("FROM organisationunit o "); + sqlsb.append("WHERE o.parentid = '" + orgUnitParent + "' "); + sqlsb.append("ORDER BY o.name)"); + + List columnNames = new ArrayList(); + columnNames.add("drilldown_data_element"); + setTableStructure(table, sqlsb, columnNames); + + } + + public void setDataElementStructureForOrgUnitBetweenPeriods( + DataBrowserTable table, Integer orgUnitId, + List betweenPeriods) { + + StringBuffer sqlsb = new StringBuffer(); + String sql = statementBuilder + .queryDataElementStructureForOrgUnitBetweenPeriods(); + sqlsb.append(sql); + List columnNames = new ArrayList(); + columnNames.add("drilldown_data_element"); + setTableStructure(table, sqlsb, columnNames); + } + + public Integer setCountDataElementsForDataSetBetweenPeriods( + DataBrowserTable table, Integer dataSetId, + List betweenPeriodIds) { + StatementHolder holder = statementManager.getHolder(); + + // Here we uses a for loop to create one big sql statement using UNION. + // This is done because the count and GROUP BY parts of this query can't + // be done in another way. The alternative to this method is to actually + // query the database as many time than betweenPeriodIds.size() tells. + // But the overhead cost of doing that is bigger than the creation of + // this UNION query. + Integer numResults = 0; + StringBuffer sqlsb = new StringBuffer(); + + int i = 0; + for (Integer periodId : betweenPeriodIds) { + i++; + + sqlsb.append("(SELECT de.dataelementid, de.name AS dataelement, COUNT(dv.value) AS counts_of_aggregated_values, p.periodid AS PeriodId, p.startdate AS ColumnHeader "); + sqlsb.append("FROM dataelement de JOIN datavalue dv ON (de.dataelementid = dv.dataelementid) "); + sqlsb.append("JOIN datasetmembers dsm ON (de.dataelementid = dsm.dataelementid) "); + sqlsb.append("JOIN period p ON (dv.periodid = p.periodid) "); + sqlsb.append("WHERE dsm.datasetid = '" + dataSetId + + "' AND dv.periodid = '" + periodId + "' "); + sqlsb.append("GROUP BY de.dataelementid, de.name, p.periodid, p.startDate)"); + + sqlsb.append(i == betweenPeriodIds.size() ? "ORDER BY ColumnHeader" + : " UNION "); + } + + try { + Timer timer = new Timer(); + timer.start(); + ResultSet resultSet = getScrollableResult(sqlsb.toString(), holder); + table.addQueryTime(timer.getMilliSec()); + + table.incrementQueryCount(); + + numResults = table.addColumnToAllRows(resultSet); + } catch (SQLException e) { + throw new RuntimeException("Failed to get aggregated data value\n" + + sqlsb.toString(), e); + } finally { + holder.close(); + } + + return numResults; + } + + public Integer setCountDataElementsForDataElementGroupBetweenPeriods( + DataBrowserTable table, Integer dataElementGroupId, + List betweenPeriodIds) { + + StringBuffer sqlsb = new StringBuffer(); + + int i = 0; + for (Integer periodid : betweenPeriodIds) { + i++; + + sqlsb.append("(SELECT de.dataelementid, de.name AS DataElement, COUNT(dv.value) AS counts_of_aggregated_values, p.periodid AS PeriodId, p.startDate AS ColumnHeader "); + sqlsb.append("FROM dataelement de JOIN datavalue dv ON (de.dataelementid = dv.dataelementid) "); + sqlsb.append("JOIN dataelementgroupmembers degm ON (de.dataelementid = degm.dataelementid) "); + sqlsb.append("JOIN period p ON (dv.periodid = p.periodid) "); + sqlsb.append("WHERE degm.dataelementgroupid = '" + + dataElementGroupId + "' "); + sqlsb.append("AND dv.periodid = '" + periodid + "' "); + sqlsb.append("GROUP BY de.dataelementid, de.name, p.periodid, p.startDate) "); + + sqlsb.append(i == betweenPeriodIds.size() ? "ORDER BY ColumnHeader" + : " UNION "); + } + + return setCountFromSQL(table, sqlsb.toString()); + } + + public Integer setCountDataElementGroupsForOrgUnitGroupBetweenPeriods( + DataBrowserTable table, Integer orgUnitGroupId, + List betweenPeriodIds) { + + StringBuffer sqlsb = new StringBuffer(); + + int i = 0; + for (Integer periodid : betweenPeriodIds) { + i++; + + sqlsb.append("(SELECT deg.dataelementgroupid, deg.name, COUNT(dv.value) AS counts_of_aggregated_values, p.periodid AS PeriodId, p.startdate AS ColumnHeader "); + sqlsb.append("FROM dataelementgroup AS deg "); + sqlsb.append("INNER JOIN dataelementgroupmembers AS degm ON deg.dataelementgroupid = degm.dataelementgroupid "); + sqlsb.append("INNER JOIN datavalue AS dv ON degm.dataelementid = dv.dataelementid "); + sqlsb.append("INNER JOIN period AS p ON dv.periodid = p.periodid "); + sqlsb.append("INNER JOIN organisationunit AS ou ON dv.sourceid = ou.organisationunitid "); + sqlsb.append("INNER JOIN orgunitgroupmembers AS ougm ON ou.organisationunitid = ougm.organisationunitid "); + sqlsb.append("WHERE p.periodid = '" + periodid + + "' AND ougm.orgunitgroupid = '" + orgUnitGroupId + "' "); + sqlsb.append("GROUP BY deg.dataelementgroupid,deg.name,p.periodid,p.startdate) "); + + sqlsb.append(i == betweenPeriodIds.size() ? "ORDER BY ColumnHeader" + : " UNION "); + } + + return setCountFromSQL(table, sqlsb.toString()); + } + + public Integer setCountOrgUnitsBetweenPeriods(DataBrowserTable table, + Integer orgUnitParent, List betweenPeriodIds, + Integer maxLevel) { + + StringBuffer sqlsbDescentdants = new StringBuffer(); + + this.setUpQueryForDrillDownDescendants(sqlsbDescentdants, + orgUnitParent, betweenPeriodIds, maxLevel); + + return setCountFromSQL(table, sqlsbDescentdants.toString()); + + } + + public Integer setCountDataElementsForOrgUnitBetweenPeriods( + DataBrowserTable table, Integer orgUnitId, + List betweenPeriodIds) { + StatementHolder holder = statementManager.getHolder(); + + Integer numResults = 0; + String sql = statementBuilder + .queryCountDataElementsForOrgUnitBetweenPeriods(orgUnitId, + betweenPeriodIds); + + try { + Timer timer = new Timer(); + timer.start(); + ResultSet resultSet = getScrollableResult(sql, holder); + table.addQueryTime(timer.getMilliSec()); + + table.incrementQueryCount(); + + numResults = table.addColumnToAllRows(resultSet); + } catch (SQLException e) { + throw new RuntimeException("Failed to get aggregated data value\n" + + sql, e); + } finally { + holder.close(); + } + + return numResults; + } + + // ------------------------------------------------------------------------- + // Supportive methods + // ------------------------------------------------------------------------- + + /** + * Splits a list of integers by by comma. Use this method if you have a list + * that will be used in f.ins. a WHERE xxx IN (list) clause in SQL. + * + * @param List + * list of Integers + * @return the list as a string splitted by a comma. + */ + private String splitListHelper(List list) { + StringBuffer sb = new StringBuffer(); + int count = 0; + + sb.append("("); + for (Integer i : list) { + sb.append(i); + + count++; + + if (count < list.size()) { + sb.append(","); + } + } + sb.append(")"); + + return sb.toString(); + } + + /** + * Uses StatementManager to obtain a scrollable, read-only ResultSet based + * on the query string. + * + * @param sql + * the query + * @param holder + * the StatementHolder object + * @return null or the ResultSet + */ + private ResultSet getScrollableResult(String sql, StatementHolder holder) + throws SQLException { + Connection con = holder.getConnection(); + Statement stm = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, + ResultSet.CONCUR_READ_ONLY); + stm.execute(sql); + return stm.getResultSet(); + } + + private String setUpQueryForDrillDownDescendants(StringBuffer sb, + Integer orgUnitSelected, List betweenPeriodIds, + Integer maxLevel) { + if (maxLevel == null) { + maxLevel = organisationUnitService.getMaxOfOrganisationUnitLevels(); + } + + int curLevel = organisationUnitService + .getLevelOfOrganisationUnit(orgUnitSelected); + int loopSize = betweenPeriodIds.size(); + + String descendantQuery = this.setUpQueryGetDescendants(curLevel, + maxLevel, orgUnitSelected); + int i = 0; + + for (Integer periodid : betweenPeriodIds) { + i++; + /** + * Get all descendant level data for all orgunits under the + * selected, grouped by the next immediate children of the selected + * orgunit Looping through each period UNION construct appears to be + * faster with an index placed on periodid's rather than joining on + * periodids and then performing the aggregation step. + * + */ + sb.append(" SELECT a.parentid,a.name AS organisationunit,COUNT(*),p.periodid,p.startdate AS columnheader"); + sb.append(" FROM datavalue dv"); + sb.append(" INNER JOIN (SELECT DISTINCT x.parentid,x.childid,ou.name FROM(" + + descendantQuery + ") x"); + sb.append(" INNER JOIN organisationunit ou ON x.parentid=ou.organisationunitid) a ON dv.sourceid=a.childid"); + sb.append(" INNER JOIN period p ON dv.periodid=p.periodid"); + sb.append(" WHERE dv.periodid=" + periodid); + sb.append(" GROUP BY a.parentid,a.name,p.periodid,p.startdate"); + sb.append(i < loopSize ? " UNION " : ""); + + } + sb.append(" ORDER BY columnheader,organisationunit"); + + return sb.toString(); + } + + private String setUpQueryGetDescendants(int curLevel, int maxLevel, + Integer orgUnitSelected) { + Integer childLevel = curLevel + 1; + Integer diffLevel = maxLevel - curLevel; + + // The immediate child level can probably be combined into the for loop + // but we need to clarify whether the selected unit should be present, + // and if so, how? + + final StringBuilder desc_query = new StringBuilder(); + + // Loop through each of the descendants until the diff level is reached + for (int j = 0; j < diffLevel; j++) { + desc_query.append(j != 0 ? " UNION " : ""); + desc_query.append("SELECT DISTINCT idlevel" + (childLevel) + + " AS parentid,"); + desc_query.append("idlevel" + (childLevel + j) + " AS childid"); + desc_query.append(" FROM _orgunitstructure"); + desc_query.append(" WHERE idlevel" + (curLevel) + "='" + + orgUnitSelected + "'"); + desc_query.append(" AND idlevel" + (childLevel + j) + "<>0"); + } + + return desc_query.toString(); + } + + private DataBrowserTable getTablefromSQL(StringBuffer sqlsb, + List columnNames) { + StatementHolder holder = statementManager.getHolder(); + DataBrowserTable table = new DataBrowserTable(); + Timer timer = new Timer(); + timer.start(); + try { + log.debug("getTableFromSQL: " + sqlsb.toString()); + ResultSet resultSet = getScrollableResult(sqlsb.toString(), holder); + table.setQueryTime(timer.getMilliSec()); + table.incrementQueryCount(); + Iterator it = columnNames.iterator(); + while (it.hasNext()) { + table.addColumnName(it.next().toString()); + } + table.createStructure(resultSet); + table.addColumnToAllRows(resultSet); + } catch (SQLException e) { + throw new RuntimeException("Failed to get aggregated data value\n" + + sqlsb.toString(), e); + } catch (Exception e) { + throw new RuntimeException("Oops. Somthing else went wrong", e); + } finally { + holder.close(); + } + return table; + } + + private void setTableStructure(DataBrowserTable table, StringBuffer sqlsb, + List columnNames) { + StatementHolder holder = statementManager.getHolder(); + + Timer timer = new Timer(); + timer.start(); + try { + log.debug("setTableStructure: " + sqlsb.toString()); + ResultSet resultSet = getScrollableResult(sqlsb.toString(), holder); + table.setQueryTime(timer.getMilliSec()); + table.incrementQueryCount(); + Iterator it = columnNames.iterator(); + while (it.hasNext()) { + table.addColumnName(it.next().toString()); + } + table.createStructure(resultSet); + } catch (SQLException e) { + throw new RuntimeException("Failed to get aggregated data value\n" + + sqlsb.toString(), e); + } catch (Exception e) { + throw new RuntimeException("Oops. Somthing else went wrong", e); + } finally { + holder.close(); + } + } + + /** + * Generates a count + * + * @return setCountFromSQL + * @param DataBrowserTable + * , the DataBrowserTable to generate the count for. + * @param sql + * , SQL which generates the count + */ + + private Integer setCountFromSQL(DataBrowserTable table, String sql) { + StatementHolder holder = statementManager.getHolder(); + + Integer numResults = 0; + + try { + Timer timer = new Timer(); + timer.start(); + log.debug("setCountFromSQL: " + sql); + ResultSet resultSet = getScrollableResult(sql, holder); + table.addQueryTime(timer.getMilliSec()); + + table.incrementQueryCount(); + + numResults = table.addColumnToAllRows(resultSet); + } catch (SQLException e) { + throw new RuntimeException("Failed to get aggregated data value\n" + + sql, e); + } finally { + holder.close(); + } + + return numResults; + } } === modified file 'dhis-2/dhis-services/dhis-service-administration/src/test/java/org/hisp/dhis/databrowser/DataBrowserServiceTest.java' --- dhis-2/dhis-services/dhis-service-administration/src/test/java/org/hisp/dhis/databrowser/DataBrowserServiceTest.java 2011-02-14 08:56:35 +0000 +++ dhis-2/dhis-services/dhis-service-administration/src/test/java/org/hisp/dhis/databrowser/DataBrowserServiceTest.java 2011-02-16 13:25:39 +0000 @@ -94,11 +94,11 @@ assertEquals( dataSetC.getId(), table.getRows().get( 2 ).getId().intValue() ); assertEquals( "Row count entries", 3, table.getCounts().size() ); - assertEquals( "DataValues in dataSetB", 18, table.getRowBasedOnRowName( dataSetB.getName() ).get( 0 ) - .intValue() ); - assertEquals( "DataValues in dataSetA", 12, table.getRowBasedOnRowName( dataSetA.getName() ).get( 0 ) - .intValue() ); - assertEquals( "DataValues in dataSetC", 3, table.getRowBasedOnRowName( dataSetC.getName() ).get( 0 ).intValue() ); + assertEquals( "DataValues in dataSetB", "18", table.getRowBasedOnRowName( dataSetB.getName() ).get( 0 ) + ); + assertEquals( "DataValues in dataSetA", "12", table.getRowBasedOnRowName( dataSetA.getName() ).get( 0 ) + ); + assertEquals( "DataValues in dataSetC", "3" , table.getRowBasedOnRowName( dataSetC.getName() ).get( 0 ) ); // Get all DataSets from 2005-05-01 to 2005-05-31 registered on weekly // basis (this should be only period D data values) @@ -119,7 +119,7 @@ assertEquals( dataSetC.getId(), table.getRows().get( 0 ).getId().intValue() ); assertEquals( "Row count entries", 1, table.getCounts().size() ); - assertEquals( "DataValues in dataSetC", 6, table.getRowBasedOnRowName( dataSetC.getName() ).get( 0 ).intValue() ); + assertEquals( "DataValues in dataSetC", "6", table.getRowBasedOnRowName( dataSetC.getName() ).get( 0 ) ); } /** @@ -152,12 +152,12 @@ assertEquals( dataElementGroupC.getId(), table.getRows().get( 2 ).getId().intValue() ); assertEquals( "Row count entries", 3, table.getCounts().size() ); - assertEquals( "DataValues in dataElementGroupB", 18, table.getRowBasedOnRowName( dataElementGroupB.getName() ) - .get( 0 ).intValue() ); - assertEquals( "DataValues in dataElementGroupA", 12, table.getRowBasedOnRowName( dataElementGroupA.getName() ) - .get( 0 ).intValue() ); - assertEquals( "DataValues in dataElementGroupC", 3, table.getRowBasedOnRowName( dataElementGroupC.getName() ) - .get( 0 ).intValue() ); + assertEquals( "DataValues in dataElementGroupB", "18", table.getRowBasedOnRowName( dataElementGroupB.getName() ) + .get( 0 ) ); + assertEquals( "DataValues in dataElementGroupA", "12", table.getRowBasedOnRowName( dataElementGroupA.getName() ) + .get( 0 ) ); + assertEquals( "DataValues in dataElementGroupC", "3", table.getRowBasedOnRowName( dataElementGroupC.getName() ) + .get( 0 ) ); // Get all DataElementGroups from 2005-05-01 to 2005-05-31 registered on // weekly basis (this should be only period D data values) @@ -178,8 +178,8 @@ assertEquals( dataElementGroupC.getId(), table.getRows().get( 0 ).getId().intValue() ); assertEquals( "Row count entries", 1, table.getCounts().size() ); - assertEquals( "DataValues in dataElementGroupC", 6, table.getRowBasedOnRowName( dataElementGroupC.getName() ) - .get( 0 ).intValue() ); + assertEquals( "DataValues in dataElementGroupC", "6", table.getRowBasedOnRowName( dataElementGroupC.getName() ) + .get( 0 ) ); } /** @@ -213,11 +213,11 @@ assertEquals( "Row count entries", 2, table.getCounts().size() ); // unitD has 6 datavalues, unitE has 6 datavalues and unitF has 5 // datavalues for periods A and B - assertEquals( "DataValues in unitGroupB", 17, table.getRowBasedOnRowName( unitGroupB.getName() ).get( 0 ) - .intValue() ); + assertEquals( "DataValues in unitGroupB", "17", table.getRowBasedOnRowName( unitGroupB.getName() ).get( 0 ) + ); // unitB has 0 datavalues and unitC has 6 datavalues for periods A and B - assertEquals( "DataValues in unitGroupA", 6, table.getRowBasedOnRowName( unitGroupA.getName() ).get( 0 ) - .intValue() ); + assertEquals( "DataValues in unitGroupA", "6", table.getRowBasedOnRowName( unitGroupA.getName() ).get( 0 ) + ); } /** @@ -252,18 +252,18 @@ assertEquals( unitF.getId(), table.getRows().get( 2 ).getId().intValue() ); assertEquals( "Row count entries", 3, table.getCounts().size() ); - assertEquals( "DataValues in unitD for periodA", 4, table.getRowBasedOnRowName( unitD.getName() ).get( 0 ) - .intValue() ); - assertEquals( "DataValues in unitD for periodB", 2, table.getRowBasedOnRowName( unitD.getName() ).get( 1 ) - .intValue() ); - assertEquals( "DataValues in unitE for periodA", 4, table.getRowBasedOnRowName( unitE.getName() ).get( 0 ) - .intValue() ); - assertEquals( "DataValues in unitE for periodB", 2, table.getRowBasedOnRowName( unitE.getName() ).get( 1 ) - .intValue() ); - assertEquals( "DataValues in unitF for periodA", 2, table.getRowBasedOnRowName( unitF.getName() ).get( 0 ) - .intValue() ); - assertEquals( "DataValues in unitF for periodB", 3, table.getRowBasedOnRowName( unitF.getName() ).get( 1 ) - .intValue() ); + assertEquals( "DataValues in unitD for periodA", "4", table.getRowBasedOnRowName( unitD.getName() ).get( 0 ) + ); + assertEquals( "DataValues in unitD for periodB", "2", table.getRowBasedOnRowName( unitD.getName() ).get( 1 ) + ); + assertEquals( "DataValues in unitE for periodA", "4", table.getRowBasedOnRowName( unitE.getName() ).get( 0 ) + ); + assertEquals( "DataValues in unitE for periodB", "2", table.getRowBasedOnRowName( unitE.getName() ).get( 1 ) + ); + assertEquals( "DataValues in unitF for periodA", "2", table.getRowBasedOnRowName( unitF.getName() ).get( 0 ) + ); + assertEquals( "DataValues in unitF for periodB", "3", table.getRowBasedOnRowName( unitF.getName() ).get( 1 ) + ); // Retrieve children of unitG - zero children table = dataBrowserService.getOrgUnitsInPeriod( unitG.getId(), null, null, periodA.getPeriodType(), 4, @@ -307,8 +307,8 @@ assertEquals( dataElementA.getId(), table.getRows().get( 0 ).getId().intValue() ); assertEquals( "Row count entries", 1, table.getCounts().size() ); - assertEquals( "DataValues in dataElementA", 6, table.getRowBasedOnRowName( dataElementA.getName() ).get( 0 ) - .intValue() ); + assertEquals( "DataValues in dataElementA", "6", table.getRowBasedOnRowName( dataElementA.getName() ).get( 0 ) + ); // Get count for dataSetC from 2005-05-01 to 2005-05-31 registered on // weekly basis (this should be only period D data values) @@ -331,10 +331,10 @@ assertEquals( dataElementE.getId(), table.getRows().get( 1 ).getId().intValue() ); assertEquals( "Row count entries", 3, table.getCounts().size() ); - assertEquals( "DataValues in dataElementC", 3, table.getRowBasedOnRowName( dataElementC.getName() ).get( 0 ) - .intValue() ); - assertEquals( "DataValues in dataElementE", 3, table.getRowBasedOnRowName( dataElementE.getName() ).get( 0 ) - .intValue() ); + assertEquals( "DataValues in dataElementC", "3", table.getRowBasedOnRowName( dataElementC.getName() ).get( 0 ) + ); + assertEquals( "DataValues in dataElementE", "3", table.getRowBasedOnRowName( dataElementE.getName() ).get( 0 ) + ); } /** @@ -365,8 +365,8 @@ assertEquals( dataElementA.getId(), table.getRows().get( 0 ).getId().intValue() ); assertEquals( "Row count entries", 1, table.getCounts().size() ); - assertEquals( "DataValues in dataElementA", 6, table.getRowBasedOnRowName( dataElementA.getName() ).get( 0 ) - .intValue() ); + assertEquals( "DataValues in dataElementA", "6", table.getRowBasedOnRowName( dataElementA.getName() ).get( 0 ) + ); // Get count for dataElementGroupC from 2005-05-01 to 2005-05-31 // registered on weekly basis (this should be only period D data values) @@ -389,10 +389,10 @@ assertEquals( dataElementE.getId(), table.getRows().get( 1 ).getId().intValue() ); assertEquals( "Row count entries", 3, table.getCounts().size() ); - assertEquals( "DataValues in dataElementC", 3, table.getRowBasedOnRowName( dataElementC.getName() ).get( 0 ) - .intValue() ); - assertEquals( "DataValues in dataElementE", 3, table.getRowBasedOnRowName( dataElementE.getName() ).get( 0 ) - .intValue() ); + assertEquals( "DataValues in dataElementC", "3", table.getRowBasedOnRowName( dataElementC.getName() ).get( 0 ) + ); + assertEquals( "DataValues in dataElementE", "3", table.getRowBasedOnRowName( dataElementE.getName() ).get( 0 ) + ); } /** @@ -430,18 +430,18 @@ assertEquals( dataElementGroupC.getId(), table.getRows().get( 2 ).getId().intValue() ); assertEquals( "Row count entries", 3, table.getCounts().size() ); - assertEquals( "DataValues in dataElementGroupA for periodA", 1, table.getRowBasedOnRowName( - dataElementGroupA.getName() ).get( 0 ).intValue() ); - assertEquals( "DataValues in dataElementGroupA for PeriodB", 1, table.getRowBasedOnRowName( - dataElementGroupA.getName() ).get( 1 ).intValue() ); - assertEquals( "DataValues in dataElementGroupB for PeriodA", 2, table.getRowBasedOnRowName( - dataElementGroupB.getName() ).get( 0 ).intValue() ); - assertEquals( "DataValues in dataElementGroupB for PeriodB", 1, table.getRowBasedOnRowName( - dataElementGroupB.getName() ).get( 1 ).intValue() ); - assertEquals( "DataValues in dataElementGroupC for PeriodA", 1, table.getRowBasedOnRowName( - dataElementGroupC.getName() ).get( 0 ).intValue() ); - assertEquals( "DataValues in dataElementGroupC for PeriodB", 0, table.getRowBasedOnRowName( - dataElementGroupC.getName() ).get( 1 ).intValue() ); + assertEquals( "DataValues in dataElementGroupA for periodA", "1", table.getRowBasedOnRowName( + dataElementGroupA.getName() ).get( 0 ) ); + assertEquals( "DataValues in dataElementGroupA for PeriodB", "1", table.getRowBasedOnRowName( + dataElementGroupA.getName() ).get( 1 ) ); + assertEquals( "DataValues in dataElementGroupB for PeriodA", "2", table.getRowBasedOnRowName( + dataElementGroupB.getName() ).get( 0 ) ); + assertEquals( "DataValues in dataElementGroupB for PeriodB", "1", table.getRowBasedOnRowName( + dataElementGroupB.getName() ).get( 1 ) ); + assertEquals( "DataValues in dataElementGroupC for PeriodA", "1", table.getRowBasedOnRowName( + dataElementGroupC.getName() ).get( 0 ) ); + assertEquals( "DataValues in dataElementGroupC for PeriodB", "0", table.getRowBasedOnRowName( + dataElementGroupC.getName() ).get( 1 ) ); } /** @@ -504,18 +504,18 @@ assertEquals( "Row count entries", 4, table.getCounts().size() ); - assertEquals( "DataValues in dataElementA for periodA", 1, table.getRowBasedOnRowName( dataElementA.getName() ) - .get( 0 ).intValue() ); - assertEquals( "DataValues in dataElementA for PeriodB", 1, table.getRowBasedOnRowName( dataElementA.getName() ) - .get( 1 ).intValue() ); - assertEquals( "DataValues in dataElementB for PeriodA", 1, table.getRowBasedOnRowName( dataElementB.getName() ) - .get( 0 ).intValue() ); - assertEquals( "DataValues in dataElementB for PeriodB", 1, table.getRowBasedOnRowName( dataElementB.getName() ) - .get( 1 ).intValue() ); - assertEquals( "DataValues in dataElementD for PeriodA", 0, table.getRowBasedOnRowName( dataElementD.getName() ) - .get( 0 ).intValue() ); - assertEquals( "DataValues in dataElementD for PeriodB", 1, table.getRowBasedOnRowName( dataElementD.getName() ) - .get( 1 ).intValue() ); + assertEquals( "DataValues in dataElementA for periodA", "1", table.getRowBasedOnRowName( dataElementA.getName() ) + .get( 0 ) ); + assertEquals( "DataValues in dataElementA for PeriodB", "1", table.getRowBasedOnRowName( dataElementA.getName() ) + .get( 1 ) ); + assertEquals( "DataValues in dataElementB for PeriodA", "1", table.getRowBasedOnRowName( dataElementB.getName() ) + .get( 0 ) ); + assertEquals( "DataValues in dataElementB for PeriodB", "1", table.getRowBasedOnRowName( dataElementB.getName() ) + .get( 1 ) ); + assertEquals( "DataValues in dataElementD for PeriodA", "0", table.getRowBasedOnRowName( dataElementD.getName() ) + .get( 0 ) ); + assertEquals( "DataValues in dataElementD for PeriodB", "1", table.getRowBasedOnRowName( dataElementD.getName() ) + .get( 1 ) ); } /** === modified file 'dhis-2/dhis-services/dhis-service-administration/src/test/java/org/hisp/dhis/databrowser/DataBrowserStoreTest.java' --- dhis-2/dhis-services/dhis-service-administration/src/test/java/org/hisp/dhis/databrowser/DataBrowserStoreTest.java 2010-04-13 09:05:14 +0000 +++ dhis-2/dhis-services/dhis-service-administration/src/test/java/org/hisp/dhis/databrowser/DataBrowserStoreTest.java 2011-02-16 13:25:39 +0000 @@ -88,12 +88,12 @@ assertEquals( dataSetC.getId(), table.getRows().get( 2 ).getId().intValue() ); assertEquals( "Row count entries", 3, table.getCounts().size() ); - assertEquals( "DataValues in dataSetB", 24, table.getRowBasedOnRowName( dataSetB.getName() ).get( 0 ) - .intValue() ); - assertEquals( "DataValues in dataSetA", 18, table.getRowBasedOnRowName( dataSetA.getName() ).get( 0 ) - .intValue() ); - assertEquals( "DataValues in dataSetC", 12, table.getRowBasedOnRowName( dataSetC.getName() ).get( 0 ) - .intValue() ); + assertEquals( "DataValues in dataSetB", "24", table.getRowBasedOnRowName( dataSetB.getName() ).get( 0 ) + ); + assertEquals( "DataValues in dataSetA", "18", table.getRowBasedOnRowName( dataSetA.getName() ).get( 0 ) + ); + assertEquals( "DataValues in dataSetC", "12", table.getRowBasedOnRowName( dataSetC.getName() ).get( 0 ) + ); } /** @@ -128,12 +128,12 @@ assertEquals( dataElementGroupC.getId(), table.getRows().get( 2 ).getId().intValue() ); assertEquals( "Row count entries", 3, table.getCounts().size() ); - assertEquals( "DataValues in dataElementGroupB", 24, table.getRowBasedOnRowName( dataElementGroupB.getName() ) - .get( 0 ).intValue() ); - assertEquals( "DataValues in dataElementGroupA", 18, table.getRowBasedOnRowName( dataElementGroupA.getName() ) - .get( 0 ).intValue() ); - assertEquals( "DataValues in dataElementGroupC", 12, table.getRowBasedOnRowName( dataElementGroupC.getName() ) - .get( 0 ).intValue() ); + assertEquals( "DataValues in dataElementGroupB", "24", table.getRowBasedOnRowName( dataElementGroupB.getName() ) + .get( 0 ) ); + assertEquals( "DataValues in dataElementGroupA", "18", table.getRowBasedOnRowName( dataElementGroupA.getName() ) + .get( 0 ) ); + assertEquals( "DataValues in dataElementGroupC", "12", table.getRowBasedOnRowName( dataElementGroupC.getName() ) + .get( 0 ) ); } /** @@ -166,11 +166,11 @@ assertEquals( "Row count entries", 2, table.getCounts().size() ); // unitD has 10 DataValues, unitE has 10 DataValues and unitF has 8 DataValues - assertEquals( "DataValues in unitGroupB", 28, table.getRowBasedOnRowName( unitGroupB.getName() ) - .get( 0 ).intValue() ); + assertEquals( "DataValues in unitGroupB", "28", table.getRowBasedOnRowName( unitGroupB.getName() ) + .get( 0 ) ); // unitB has 0 DataValues and unitC has 10 DataValues - assertEquals( "DataValues in unitGroupA", 10, table.getRowBasedOnRowName( unitGroupA.getName() ) - .get( 0 ).intValue() ); + assertEquals( "DataValues in unitGroupA", "10", table.getRowBasedOnRowName( unitGroupA.getName() ) + .get( 0 ) ); } /** @@ -441,8 +441,8 @@ assertEquals( dataElementA.getId(), table.getRows().get( 0 ).getId().intValue() ); assertEquals( "Row count entries", 1, table.getCounts().size() ); - assertEquals( "DataValues in dataElementA for periodA", 6, table.getRowBasedOnRowName( dataElementA.getName() ) - .get( 0 ).intValue() ); + assertEquals( "DataValues in dataElementA for periodA", "6", table.getRowBasedOnRowName( dataElementA.getName() ) + .get( 0 ) ); } /** @@ -478,8 +478,8 @@ assertEquals( dataElementA.getId(), table.getRows().get( 0 ).getId().intValue() ); assertEquals( "Row count entries", 1, table.getCounts().size() ); - assertEquals( "DataValues in dataElementA for periodA", 6, table.getRowBasedOnRowName( dataElementA.getName() ) - .get( 0 ).intValue() ); + assertEquals( "DataValues in dataElementA for periodA", "6", table.getRowBasedOnRowName( dataElementA.getName() ) + .get( 0 ) ); } /** @@ -518,12 +518,12 @@ assertEquals( dataElementGroupC.getId(), table.getRows().get( 2 ).getId().intValue() ); assertEquals( "Row count entries", 3, table.getCounts().size() ); - assertEquals( "DataValues in dataElementGroupA for periodA", 1, table.getRowBasedOnRowName( dataElementGroupA.getName() ) - .get( 0 ).intValue() ); - assertEquals( "DataValues in dataElementGroupB for periodA", 2, table.getRowBasedOnRowName( dataElementGroupB.getName() ) - .get( 0 ).intValue() ); - assertEquals( "DataValues in dataElementGroupC for periodA", 1, table.getRowBasedOnRowName( dataElementGroupC.getName() ) - .get( 0 ).intValue() ); + assertEquals( "DataValues in dataElementGroupA for periodA", "1", table.getRowBasedOnRowName( dataElementGroupA.getName() ) + .get( 0 ) ); + assertEquals( "DataValues in dataElementGroupB for periodA", "2", table.getRowBasedOnRowName( dataElementGroupB.getName() ) + .get( 0 ) ); + assertEquals( "DataValues in dataElementGroupC for periodA", "1", table.getRowBasedOnRowName( dataElementGroupC.getName() ) + .get( 0 ) ); } /** @@ -565,18 +565,18 @@ // unitD has all six dataElements but only dataValues in periodC for // three of them. The other three (C, D, F) are zero assertEquals( "Row count entries", 6, table.getCounts().size() ); - assertEquals( "DataValues in dataElementA for periodC", 1, table.getRowBasedOnRowName( dataElementA.getName() ) - .get( 0 ).intValue() ); - assertEquals( "DataValues in dataElementB for periodC", 1, table.getRowBasedOnRowName( dataElementB.getName() ) - .get( 0 ).intValue() ); - assertEquals( "DataValues in dataElementC for periodC", 0, table.getRowBasedOnRowName( dataElementC.getName() ) - .get( 0 ).intValue() ); - assertEquals( "DataValues in dataElementD for periodC", 0, table.getRowBasedOnRowName( dataElementD.getName() ) - .get( 0 ).intValue() ); - assertEquals( "DataValues in dataElementE for periodC", 1, table.getRowBasedOnRowName( dataElementE.getName() ) - .get( 0 ).intValue() ); - assertEquals( "DataValues in dataElementF for periodC", 0, table.getRowBasedOnRowName( dataElementF.getName() ) - .get( 0 ).intValue() ); + assertEquals( "DataValues in dataElementA for periodC", "1", table.getRowBasedOnRowName( dataElementA.getName() ) + .get( 0 ) ); + assertEquals( "DataValues in dataElementB for periodC", "1", table.getRowBasedOnRowName( dataElementB.getName() ) + .get( 0 ) ); + assertEquals( "DataValues in dataElementC for periodC", "0", table.getRowBasedOnRowName( dataElementC.getName() ) + .get( 0 ) ); + assertEquals( "DataValues in dataElementD for periodC", "0", table.getRowBasedOnRowName( dataElementD.getName() ) + .get( 0 ) ); + assertEquals( "DataValues in dataElementE for periodC", "1", table.getRowBasedOnRowName( dataElementE.getName() ) + .get( 0 ) ); + assertEquals( "DataValues in dataElementF for periodC", "0", table.getRowBasedOnRowName( dataElementF.getName() ) + .get( 0 ) ); } /** @@ -622,18 +622,18 @@ // unitC has all six dataElements but only dataValues in periodA for // four of them. The other two (C and E) are zero assertEquals( "Row count entries", 6, table.getCounts().size() ); - assertEquals( "DataValues in dataElementA for periodA", 1, table.getRowBasedOnRowName( dataElementA.getName() ) - .get( 0 ).intValue() ); - assertEquals( "DataValues in dataElementB for periodA", 1, table.getRowBasedOnRowName( dataElementB.getName() ) - .get( 0 ).intValue() ); - assertEquals( "DataValues in dataElementC for periodA", 0, table.getRowBasedOnRowName( dataElementC.getName() ) - .get( 0 ).intValue() ); - assertEquals( "DataValues in dataElementD for periodA", 1, table.getRowBasedOnRowName( dataElementD.getName() ) - .get( 0 ).intValue() ); - assertEquals( "DataValues in dataElementE for periodA", 0, table.getRowBasedOnRowName( dataElementE.getName() ) - .get( 0 ).intValue() ); - assertEquals( "DataValues in dataElementF for periodA", 1, table.getRowBasedOnRowName( dataElementF.getName() ) - .get( 0 ).intValue() ); + assertEquals( "DataValues in dataElementA for periodA", "1" , table.getRowBasedOnRowName( dataElementA.getName() ) + .get( 0 ) ); + assertEquals( "DataValues in dataElementB for periodA", "1" , table.getRowBasedOnRowName( dataElementB.getName() ) + .get( 0 ) ); + assertEquals( "DataValues in dataElementC for periodA", "0" , table.getRowBasedOnRowName( dataElementC.getName() ) + .get( 0 ) ); + assertEquals( "DataValues in dataElementD for periodA", "1" , table.getRowBasedOnRowName( dataElementD.getName() ) + .get( 0 ) ); + assertEquals( "DataValues in dataElementE for periodA", "0" , table.getRowBasedOnRowName( dataElementE.getName() ) + .get( 0 ) ); + assertEquals( "DataValues in dataElementF for periodA", "1" , table.getRowBasedOnRowName( dataElementF.getName() ) + .get( 0 ) ); } } === modified file 'dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/java/org/hisp/dhis/dataadmin/action/databrowser/SearchAction.java' --- dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/java/org/hisp/dhis/dataadmin/action/databrowser/SearchAction.java 2010-11-22 09:54:30 +0000 +++ dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/java/org/hisp/dhis/dataadmin/action/databrowser/SearchAction.java 2011-02-16 13:25:39 +0000 @@ -180,7 +180,7 @@ return dataElementService.getAllDataElementGroups(); } - public List> getAllCounts() + public List> getAllCounts() { return dataBrowserTable.getCounts(); } === modified file 'dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/webapp/dhis-web-maintenance-dataadmin/dataBrowserResult.vm' --- dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/webapp/dhis-web-maintenance-dataadmin/dataBrowserResult.vm 2010-10-21 05:41:06 +0000 +++ dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/webapp/dhis-web-maintenance-dataadmin/dataBrowserResult.vm 2011-02-16 13:25:39 +0000 @@ -165,13 +165,13 @@ $rowMeta.name #end #foreach ( $rowItem in $col ) - #if ( $rowItem > 0 )$rowItem #else$rowItem #end + #if ( $rowItem == "0" )$rowItem #else$rowItem #end #end #end - + #end