=== modified file 'dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/program/hibernate/HibernateProgramStageInstanceStore.java' --- dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/program/hibernate/HibernateProgramStageInstanceStore.java 2013-02-13 15:58:32 +0000 +++ dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/program/hibernate/HibernateProgramStageInstanceStore.java 2013-02-15 12:08:07 +0000 @@ -423,13 +423,13 @@ { String orgunitName = organisationUnitService.getOrganisationUnit( orgunitIds.iterator().next() ) .getDisplayName(); - + String filterDataDes = getFilterDataDescription( deFilters ); if ( !filterDataDes.isEmpty() ) { filterDataDes = "; " + i18n.getString( "data_filter" ) + ": " + filterDataDes; } - grid.setSubtitle( subTitle + i18n.getString( "orgunit" ) + ": " + orgunitName + filterDataDes); + grid.setSubtitle( subTitle + i18n.getString( "orgunit" ) + ": " + orgunitName + filterDataDes ); } // Filter is only one period else if ( position == PatientAggregateReport.POSITION_ROW_ORGUNIT @@ -450,14 +450,14 @@ String endDate = format.formatDate( period.getEndDate() ); periodName += startDate + " -> " + endDate; } - + String filterDataDes = getFilterDataDescription( deFilters ); if ( !filterDataDes.isEmpty() ) { filterDataDes = "; " + i18n.getString( "data_filter" ) + ": " + filterDataDes; } - - grid.setSubtitle( subTitle + i18n.getString( "period" ) + ": " + periodName + filterDataDes); + + grid.setSubtitle( subTitle + i18n.getString( "period" ) + ": " + periodName + filterDataDes ); } else { @@ -465,8 +465,7 @@ String filterOrgunitDes = ""; if ( position == PatientAggregateReport.POSITION_ROW_DATA_COLUMN_PERIOD || position == PatientAggregateReport.POSITION_ROW_PERIOD - || position == PatientAggregateReport.POSITION_ROW_DATA - || position == PatientAggregateReport.POSITION_ROW_DATA_COLUMN_PERIOD ) + || position == PatientAggregateReport.POSITION_ROW_DATA ) { filterOrgunitDes = getFilterOrgunitDescription( orgunitIds ); @@ -557,15 +556,21 @@ periods.iterator().next(), aggregateType, limit, useCompletedEvents, format ); } + // Type = 9 && With group-by + else if ( position == PatientAggregateReport.POSITION_ROW_DATA_COLUMN_PERIOD && deGroupBy != null ) + { + sql = getAggregateReportSQL9( programStage, orgunitIds.iterator().next(), facilityLB, filterSQL, deGroupBy, + deSum, periods, aggregateType, limit, useCompletedEvents, format ); + } + // Type = 6 && With group-by - else if ( (position == PatientAggregateReport.POSITION_ROW_PERIOD_COLUMN_DATA || position == PatientAggregateReport.POSITION_ROW_DATA_COLUMN_PERIOD) - && deGroupBy != null ) + else if ( position == PatientAggregateReport.POSITION_ROW_PERIOD_COLUMN_DATA && deGroupBy != null ) { sql = getAggregateReportSQL6( programStage, orgunitIds.iterator().next(), facilityLB, filterSQL, deGroupBy, deSum, periods, aggregateType, limit, useCompletedEvents, format ); } - // Type = 6 && NOT group-by + // Type = 6-9 && NOT group-by else if ( (position == PatientAggregateReport.POSITION_ROW_PERIOD_COLUMN_DATA || position == PatientAggregateReport.POSITION_ROW_DATA_COLUMN_PERIOD) && deGroupBy == null ) { @@ -597,19 +602,22 @@ sql = getAggregateReportSQL8( programStage, orgunitIds, facilityLB, filterSQL, deGroupBy, periods .iterator().next(), aggregateType, limit, useCompletedEvents, format ); } - - SqlRowSet rowSet = jdbcTemplate.queryForRowSet( sql ); - - // Type ==2 && ==9 && ==10 - if ( position == PatientAggregateReport.POSITION_ROW_PERIOD_COLUMN_ORGUNIT - || position == PatientAggregateReport.POSITION_ROW_DATA_COLUMN_PERIOD - || position == PatientAggregateReport.POSITION_ROW_DATA_COLUMN_ORGUNIT ) - { - pivotTable( grid, rowSet, i18n ); - } - else - { - fillDataInGrid( grid, rowSet, i18n ); + + if ( !sql.isEmpty() ) + { + SqlRowSet rowSet = jdbcTemplate.queryForRowSet( sql ); + + // Type ==2 && ==9 && ==10 + if ( position == PatientAggregateReport.POSITION_ROW_PERIOD_COLUMN_ORGUNIT + || position == PatientAggregateReport.POSITION_ROW_DATA_COLUMN_ORGUNIT + || (position == PatientAggregateReport.POSITION_ROW_DATA_COLUMN_PERIOD && deGroupBy == null) ) + { + pivotTable( grid, rowSet, i18n ); + } + else + { + fillDataInGrid( grid, rowSet, i18n ); + } } return grid; @@ -864,6 +872,7 @@ sql += filterSQL + "LIMIT 1 ) as \"" + periodName + "\" ,"; } sql = sql.substring( 0, sql.length() - 1 ) + " "; + // -- end period sql += " ) "; @@ -1100,13 +1109,25 @@ * with group-by **/ private String getAggregateReportSQL6( ProgramStage programStage, Integer root, String facilityLB, - String filterSQL, Integer deGroupBy, Integer deSum, Collection periods, - String aggregateType, Integer limit, Boolean useCompletedEvents, I18nFormat format ) + String filterSQL, Integer deGroupBy, Integer deSum, Collection periods, String aggregateType, + Integer limit, Boolean useCompletedEvents, I18nFormat format ) { String sql = ""; Collection orgunitIds = getOrganisationUnits( root, facilityLB ); - String dataValueSql = "select DISTINCT(value) from patientdatavalue where dataelementid=" + deGroupBy; + String dataValueSql = "SELECT DISTINCT(pdv.value) "; + dataValueSql += "FROM patientdatavalue pdv JOIN programstageinstance psi"; + dataValueSql += " ON pdv.programstageinstanceid=psi.programstageinstanceid "; + dataValueSql += "WHERE pdv.dataelementid=" + deGroupBy + " "; + dataValueSql += " AND psi.programstageid=" + programStage.getId() + " AND ( "; + for ( Period period : periods ) + { + dataValueSql += " ( psi.executiondate >= '" + format.formatDate( period.getStartDate() ) + "' AND "; + dataValueSql += " psi.executiondate <= '" + format.formatDate( period.getEndDate() ) + "') OR "; + } + dataValueSql = dataValueSql.substring( 0, dataValueSql.length() - 3 ); + dataValueSql += ") ORDER BY value asc"; + Collection deValues = new HashSet(); try { @@ -1123,71 +1144,75 @@ { ex.printStackTrace(); } - - for ( Period period : periods ) + + if ( deValues.size() > 0 ) { - String periodName = ""; - String startDate = format.formatDate( period.getStartDate() ); - String endDate = format.formatDate( period.getEndDate() ); - if ( period.getPeriodType() != null ) - { - periodName = format.formatPeriod( period ); - } - else - { - periodName = startDate + " -> " + endDate; - } - - sql += "(SELECT '" + periodName + "' as period, "; - for ( String deValue : deValues ) - { - if ( aggregateType.equals( PatientAggregateReport.AGGREGATE_TYPE_COUNT ) ) + for ( Period period : periods ) + { + String periodName = ""; + String startDate = format.formatDate( period.getStartDate() ); + String endDate = format.formatDate( period.getEndDate() ); + if ( period.getPeriodType() != null ) { - sql += "(SELECT " + aggregateType + "(value) "; + periodName = format.formatPeriod( period ); } else { - sql += "(SELECT ( SELECT " + aggregateType + "( cast( value as " - + statementBuilder.getDoubleColumnType() + " ))"; - sql += " FROM patientdatavalue where dataelementid=pdv_1.dataelementid and dataelementid=" - + deSum + " ) "; - } - sql += "FROM programstageinstance psi_1 JOIN patientdatavalue pdv_1 "; - sql += " on psi_1.programstageinstanceid = pdv_1.programstageinstanceid "; + periodName = startDate + " -> " + endDate; + } + + sql += "(SELECT DISTINCT '" + periodName + "' as period, "; + for ( String deValue : deValues ) + { + if ( aggregateType.equals( PatientAggregateReport.AGGREGATE_TYPE_COUNT ) ) + { + sql += "(SELECT " + aggregateType + "(value) "; + } + else + { + sql += "(SELECT ( SELECT " + aggregateType + "( cast( value as " + + statementBuilder.getDoubleColumnType() + " ))"; + sql += " FROM patientdatavalue where dataelementid=pdv_1.dataelementid and dataelementid=" + + deSum + " ) "; + } + sql += "FROM programstageinstance psi_1 JOIN patientdatavalue pdv_1 "; + sql += " on psi_1.programstageinstanceid = pdv_1.programstageinstanceid "; + sql += "WHERE "; + sql += " psi_1.organisationunitid in ( " + TextUtils.getCommaDelimitedString( orgunitIds ) + + " ) AND "; + sql += " psi_1.executiondate >= '" + startDate + "' AND "; + sql += " psi_1.executiondate <= '" + endDate + "' "; + sql += filterSQL + " AND "; + sql += " (SELECT value from patientdatavalue "; + sql += " WHERE programstageinstanceid=psi_1.programstageinstanceid AND "; + sql += " dataelementid=" + deGroupBy + ") = '" + deValue + "' "; + sql += " LIMIT 1 ) as \"" + deValue + "\","; + } + sql = sql.substring( 0, sql.length() - 1 ) + " "; + + sql += "FROM programstageinstance psi JOIN patientdatavalue pdv "; + sql += " on psi.programstageinstanceid = pdv.programstageinstanceid "; sql += "WHERE "; - sql += " psi_1.organisationunitid in ( " + TextUtils.getCommaDelimitedString( orgunitIds ) - + " ) AND "; - sql += " psi_1.executiondate >= '" + startDate + "' AND "; - sql += " psi_1.executiondate <= '" + endDate + "' "; - sql += filterSQL + " AND "; - sql += " (SELECT value from patientdatavalue "; - sql += " WHERE programstageinstanceid=psi_1.programstageinstanceid AND "; - sql += " dataelementid=" + deGroupBy + ") = '" + deValue + "' "; - sql += " LIMIT 1 ) as \"" + deValue + "\","; + sql += " psi.programstageid=" + programStage.getId() + " "; + if ( useCompletedEvents ) + { + sql += " AND psi.completed = true "; + } + sql += "GROUP BY dataelementid "; + + sql += ") UNION "; + } - sql = sql.substring( 0, sql.length() - 1 ) + " "; - sql += "FROM programstageinstance psi JOIN patientdatavalue pdv "; - sql += " on psi.programstageinstanceid = pdv.programstageinstanceid "; - sql += "WHERE "; - sql += " psi.programstageid=" + programStage.getId() + " "; - if ( useCompletedEvents ) + sql = sql.substring( 0, sql.length() - 6 ); + sql += "ORDER BY \"" + deValues.iterator().next() + "\" desc "; + if ( limit != null ) { - sql += " AND psi.completed = true "; + sql += " LIMIT " + limit; } - sql += "GROUP BY dataelementid "; - sql += " LIMIT 1 "; - - sql += ") UNION "; - - } - - sql = sql.substring( 0, sql.length() - 6 ); - if ( limit != null ) - { - sql += " LIMIT " + limit; - } - + + } + return sql; } @@ -1217,7 +1242,7 @@ periodName = startDate + " -> " + endDate; } - sql += "(SELECT '" + periodName + "' as period, "; + sql += "(SELECT DISTINCT '" + periodName + "' as period, "; if ( aggregateType.equals( PatientAggregateReport.AGGREGATE_TYPE_COUNT ) ) { @@ -1250,7 +1275,6 @@ sql += " AND psi.completed = true "; } sql += "GROUP BY dataelementid "; - sql += " LIMIT 1 "; sql += ") UNION "; } @@ -1428,80 +1452,120 @@ return sql; } - private void pivotTable( Grid grid, SqlRowSet rowSet, I18n i18n ) + /** + * Aggregate report Position Orgunit Filter - Period Columns - Data Rows + * with group-by + **/ + private String getAggregateReportSQL9( ProgramStage programStage, Integer root, String facilityLB, + String filterSQL, Integer deGroupBy, Integer deSum, Collection periods, String aggregateType, + Integer limit, Boolean useCompletedEvents, I18nFormat format ) { + String sql = ""; + Collection orgunitIds = getOrganisationUnits( root, facilityLB ); + + String dataValueSql = "SELECT DISTINCT(pdv.value) "; + dataValueSql += "FROM patientdatavalue pdv JOIN programstageinstance psi"; + dataValueSql += " ON pdv.programstageinstanceid=psi.programstageinstanceid "; + dataValueSql += "WHERE pdv.dataelementid=" + deGroupBy + " "; + dataValueSql += " AND psi.programstageid=" + programStage.getId() + " AND ( "; + for ( Period period : periods ) + { + dataValueSql += " ( psi.executiondate >= '" + format.formatDate( period.getStartDate() ) + "' AND "; + dataValueSql += " psi.executiondate <= '" + format.formatDate( period.getEndDate() ) + "') OR "; + } + dataValueSql = dataValueSql.substring( 0, dataValueSql.length() - 3 ); + dataValueSql += ") ORDER BY value asc"; + + Collection deValues = new HashSet(); try { - int cols = rowSet.getMetaData().getColumnCount(); - int total = 0; - Map> columnValues = new HashMap>(); - int index = 2; - - grid.addHeader( new GridHeader( "", false, true ) ); - while ( rowSet.next() ) - { - // Header grid - grid.addHeader( new GridHeader( rowSet.getString( 1 ), false, false ) ); - - // Column values - List column = new ArrayList(); - total = 0; - for ( int i = 2; i <= cols; i++ ) - { - column.add( rowSet.getObject( i ) ); - // value - if ( rowSet.getMetaData().getColumnType( i ) != Types.VARCHAR ) - { - total += rowSet.getInt( i ); - } - } - column.add( total ); - columnValues.put( index, column ); - index++; - } - // Add total header - grid.addHeader( new GridHeader( i18n.getString( "total" ), false, false ) ); - - // First column - List column = new ArrayList(); - for ( int i = 2; i <= cols; i++ ) - { - grid.addRow(); - column.add( i18n.getString( rowSet.getMetaData().getColumnLabel( i ) ) ); - } - grid.addRow(); - column.add( i18n.getString( "total" ) ); - grid.addColumn( column ); - - // Other columns - for ( int i = 2; i < index; i++ ) - { - grid.addColumn( columnValues.get( i ) ); - } - - // Total column - int allTotal = 0; - column = new ArrayList(); - for ( int j = 0; j < cols - 1; j++ ) - { - total = 0; - for ( int i = 2; i < index; i++ ) - { - if ( rowSet.getMetaData().getColumnType( i ) != Types.VARCHAR ) - { - total += (Long) columnValues.get( i ).get( j ); - } - } - column.add( total ); - allTotal += total; - } - column.add( allTotal ); - grid.addColumn( column ); + deValues = jdbcTemplate.query( dataValueSql, new RowMapper() + { + public String mapRow( ResultSet rs, int rowNum ) + throws SQLException + { + return rs.getString( 1 ); + } + } ); } catch ( Exception ex ) { ex.printStackTrace(); } + + String firstPeriodName = ""; + for ( String deValue : deValues ) + { + + sql += "(SELECT DISTINCT '" + deValue + "' as devalue, "; + + for ( Period period : periods ) + { + String periodName = ""; + String startDate = format.formatDate( period.getStartDate() ); + String endDate = format.formatDate( period.getEndDate() ); + if ( period.getPeriodType() != null ) + { + periodName = format.formatPeriod( period ); + } + else + { + periodName = startDate + " -> " + endDate; + } + + if ( firstPeriodName.isEmpty() ) + { + firstPeriodName = periodName; + } + + if ( aggregateType.equals( PatientAggregateReport.AGGREGATE_TYPE_COUNT ) ) + { + sql += "( SELECT " + aggregateType + "(value) "; + } + else + { + sql += "( SELECT " + aggregateType + "( cast( value as " + statementBuilder.getDoubleColumnType() + " ))"; + sql += " FROM patientdatavalue where dataelementid=pdv_1.dataelementid and dataelementid=" + + deSum + " "; + } + + sql += "FROM programstageinstance psi_1 JOIN patientdatavalue pdv_1 "; + sql += " on psi_1.programstageinstanceid = pdv_1.programstageinstanceid "; + sql += "WHERE "; + sql += " psi_1.organisationunitid in ( " + TextUtils.getCommaDelimitedString( orgunitIds ) + + " ) AND "; + sql += " psi_1.executiondate >= '" + startDate + "' AND "; + sql += " psi_1.executiondate <= '" + endDate + "' "; + sql += filterSQL + " AND "; + sql += " (SELECT value from patientdatavalue "; + sql += " WHERE programstageinstanceid=psi_1.programstageinstanceid AND "; + sql += " dataelementid=" + deGroupBy + ") = '" + deValue + "' "; + if ( useCompletedEvents ) + { + sql += " AND psi_1.completed = true "; + } + + sql += ") as \"" + periodName + "\","; + } + sql = sql.substring( 0, sql.length() - 1 ); + sql += " ) UNION "; + } + + if ( !sql.isEmpty() ) + { + sql = sql.substring( 0, sql.length() - 6 ); + if ( periods.size() == 1 ) + { + sql += "ORDER BY \"" + firstPeriodName + "\" desc "; + } + + if ( limit != null ) + { + sql += " LIMIT " + limit; + } + } + + return sql; } private String filterSQLStatement( Map> deFilters ) @@ -1622,16 +1686,26 @@ return orgunitIds; } - public static void fillDataInGrid( Grid grid, SqlRowSet rs, I18n i18n ) + public void fillDataInGrid( Grid grid, SqlRowSet rs, I18n i18n ) { int cols = rs.getMetaData().getColumnCount(); + int dataCols = 0; // Create column with Total column for ( int i = 1; i <= cols; i++ ) { grid.addHeader( new GridHeader( i18n.getString( rs.getMetaData().getColumnLabel( i ) ), false, false ) ); - } - grid.addHeader( new GridHeader( i18n.getString( "total" ), false, false ) ); + if ( rs.getMetaData().getColumnType( i ) != Types.VARCHAR ) + { + dataCols++; + } + } + + // Add total column if the number of columns is greater then 1 + if ( dataCols > 1 ) + { + grid.addHeader( new GridHeader( i18n.getString( "total" ), false, false ) ); + } int[] sumRow = new int[rs.getMetaData().getColumnCount() + 1]; while ( rs.next() ) @@ -1657,17 +1731,127 @@ } // total - grid.addValue( total ); - } - - grid.addRow(); - grid.addValue( i18n.getString( "total" ) ); - int total = 0; - for ( int i = 2; i <= cols; i++ ) - { - total += sumRow[i]; - grid.addValue( sumRow[i] ); - } - grid.addValue( total ); - } + if ( dataCols > 1 ) + { + grid.addValue( total ); + } + } + + // Add total row if the number of rows is greater then 1 + if ( grid.getRows().size() > 1 ) + { + grid.addRow(); + grid.addValue( i18n.getString( "total" ) ); + int total = 0; + for ( int i = cols - dataCols + 1; i <= cols; i++ ) + { + total += sumRow[i]; + grid.addValue( sumRow[i] ); + } + if ( cols > cols - dataCols + 1 ) + { + grid.addValue( total ); + } + } + } + + private void pivotTable( Grid grid, SqlRowSet rowSet, I18n i18n ) + { + try + { + int cols = rowSet.getMetaData().getColumnCount(); + int rows = 0; + int total = 0; + Map> columnValues = new HashMap>(); + int index = 2; + + grid.addHeader( new GridHeader( "", false, true ) ); + while ( rowSet.next() ) + { + rows++; + + // Header grid + grid.addHeader( new GridHeader( rowSet.getString( 1 ), false, false ) ); + + // Column values + List column = new ArrayList(); + total = 0; + for ( int i = 2; i <= cols; i++ ) + { + column.add( rowSet.getObject( i ) ); + // Total value of the column + if ( rowSet.getMetaData().getColumnType( i ) != Types.VARCHAR ) + { + total += rowSet.getInt( i ); + } + } + + // Add total value of the column + if ( cols > 2 ) + { + column.add( total ); + } + + columnValues.put( index, column ); + index++; + } + + // Add total header + if ( rows > 1 ) + { + grid.addHeader( new GridHeader( i18n.getString( "total" ), false, false ) ); + } + + // First column + List column = new ArrayList(); + for ( int i = 2; i <= cols; i++ ) + { + grid.addRow(); + column.add( i18n.getString( rowSet.getMetaData().getColumnLabel( i ) ) ); + } + + if ( cols > 2 ) + { + grid.addRow(); + column.add( i18n.getString( "total" ) ); + } + grid.addColumn( column ); + + // Other columns + for ( int i = 2; i < index; i++ ) + { + grid.addColumn( columnValues.get( i ) ); + } + + if ( rows > 1 ) + { + // Total column + int allTotal = 0; + column = new ArrayList(); + for ( int j = 0; j < cols - 1; j++ ) + { + total = 0; + for ( int i = 2; i < index; i++ ) + { + if ( rowSet.getMetaData().getColumnType( j + 2 ) != Types.VARCHAR ) + { + total += (Long) columnValues.get( i ).get( j ); + } + } + column.add( total ); + allTotal += total; + } + if ( cols > 2 ) + { + column.add( allTotal ); + } + grid.addColumn( column ); + } + } + catch ( Exception ex ) + { + ex.printStackTrace(); + } + } + } === modified file 'dhis-2/dhis-web/dhis-web-caseentry/src/main/webapp/dhis-web-caseentry/app/app.js' --- dhis-2/dhis-web/dhis-web-caseentry/src/main/webapp/dhis-web-caseentry/app/app.js 2013-02-13 15:16:11 +0000 +++ dhis-2/dhis-web/dhis-web-caseentry/src/main/webapp/dhis-web-caseentry/app/app.js 2013-02-15 12:08:07 +0000 @@ -3571,7 +3571,7 @@ items: [ { xtype: 'label', - text: 'Auto-select organisation units by', + text: TR.i18n.auto_select_orgunit_by, style: 'padding-left:8px; color:#666; line-height:24px' }, '->', @@ -3660,6 +3660,10 @@ multipleSelectIf: function() { if (this.recordsToSelect.length === this.numberOfRecords) { this.getSelectionModel().select(this.recordsToSelect); + TR.state.orgunitIds = []; + for( var i in this.recordsToSelect){ + TR.state.orgunitIds.push( this.recordsToSelect[i].data.localid ); + } this.recordsToSelect = []; this.numberOfRecords = 0; } === modified file 'dhis-2/dhis-web/dhis-web-caseentry/src/main/webapp/dhis-web-caseentry/javascript/commons.js' --- dhis-2/dhis-web/dhis-web-caseentry/src/main/webapp/dhis-web-caseentry/javascript/commons.js 2013-02-13 16:20:07 +0000 +++ dhis-2/dhis-web/dhis-web-caseentry/src/main/webapp/dhis-web-caseentry/javascript/commons.js 2013-02-15 12:08:07 +0000 @@ -971,8 +971,6 @@ jQuery('#loaderDiv').hide(); showById('editPatientDiv'); }); - - jQuery('#resultSearchDiv').dialog('close'); } function validateUpdatePatient()