=== modified file 'local/in/dhis-in-api/src/main/java/org/hisp/dhis/reports/ReportService.java' --- local/in/dhis-in-api/src/main/java/org/hisp/dhis/reports/ReportService.java 2011-10-13 07:01:58 +0000 +++ local/in/dhis-in-api/src/main/java/org/hisp/dhis/reports/ReportService.java 2011-11-02 09:54:19 +0000 @@ -158,5 +158,7 @@ Map getDataFromDataValueTableByPeriodAgg( String orgUnitIdsByComma, String dataElmentIdsByComma, String periodIdsByComma ); List getReportDesignWithMergeCells( String fileName ); - + + //Map getLLDeathDataFromLLDataValueTable( Integer orgunitId, String dataElmentIdsForLLDeathByComma, String periodIdsByComma , Integer recordNo ); + Map getLLDeathDataFromLLDataValueTable( Integer orgunitId, String dataElmentIdsForLLDeathByComma, String periodIdsByComma , String recordNoByComma ); } === modified file 'local/in/dhis-in-api/src/main/java/org/hisp/dhis/reports/ReportType.java' --- local/in/dhis-in-api/src/main/java/org/hisp/dhis/reports/ReportType.java 2011-10-13 07:01:58 +0000 +++ local/in/dhis-in-api/src/main/java/org/hisp/dhis/reports/ReportType.java 2011-11-02 09:54:19 +0000 @@ -59,7 +59,8 @@ public final static String RT_IDSP_REPORT = "IDSP Reports"; public final static String RT_LINELIST_BULK_REPORT = "Linelisting Bulk Reports"; - + + public final static String RT_COLDCHAIN_REPORT = "Cold Chain Reports"; public static List getReportTypes() { @@ -93,6 +94,8 @@ reportTypes.add( RT_LINELIST_BULK_REPORT ); + reportTypes.add( RT_COLDCHAIN_REPORT ); + return reportTypes; } } === modified file 'local/in/dhis-in-services/dhis-in-service-reports/src/main/java/org/hisp/dhis/reports/DefaultReportService.java' --- local/in/dhis-in-services/dhis-in-service-reports/src/main/java/org/hisp/dhis/reports/DefaultReportService.java 2011-10-13 07:01:58 +0000 +++ local/in/dhis-in-services/dhis-in-service-reports/src/main/java/org/hisp/dhis/reports/DefaultReportService.java 2011-11-02 09:54:19 +0000 @@ -2583,4 +2583,44 @@ } } + // + + public Map getLLDeathDataFromLLDataValueTable( Integer orgunitId, String dataElmentIdsForLLDeathByComma, String periodIdsByComma , String recordNoByComma ) + { + Map aggDeForLLDeathMap = new HashMap(); + // DatabaseInfo dataBaseInfo = databaseInfoProvider.getDatabaseInfo(); + try + { + String query = ""; + query = "SELECT value,dataelementid,categoryoptioncomboid,recordno FROM lldatavalue" + + " WHERE dataelementid IN (" + dataElmentIdsForLLDeathByComma + " ) AND "+ + " sourceid = "+ orgunitId +" AND "+ + " periodid IN (" + periodIdsByComma +") AND recordno IN (" + recordNoByComma + ")"; + + SqlRowSet rs = jdbcTemplate.queryForRowSet( query ); + + String tempValue = ""; + + while ( rs.next() ) + { + tempValue = rs.getString( 1 ); + Integer deId = rs.getInt( 2 ); + Integer optionComId = rs.getInt( 3 ); + Integer recordNo = rs.getInt( 4 ); + //Double aggregatedValue = rs.getDouble( 3 ); + if( tempValue != null ) + { + aggDeForLLDeathMap.put( deId+"."+optionComId+":"+recordNo, ""+ tempValue ); + } + } + + return aggDeForLLDeathMap; + } + catch( Exception e ) + { + throw new RuntimeException( "Illegal DataElement id", e ); + } + } + + } === modified file 'local/in/dhis-web-reports-national/src/main/java/org/hisp/dhis/reports/auto/action/GenerateAutoReportAnalyserResultAction.java' --- local/in/dhis-web-reports-national/src/main/java/org/hisp/dhis/reports/auto/action/GenerateAutoReportAnalyserResultAction.java 2011-04-15 11:39:32 +0000 +++ local/in/dhis-web-reports-national/src/main/java/org/hisp/dhis/reports/auto/action/GenerateAutoReportAnalyserResultAction.java 2011-11-02 09:54:19 +0000 @@ -32,6 +32,7 @@ */ import static org.hisp.dhis.system.util.ConversionUtils.getIdentifiers; +import static org.hisp.dhis.system.util.TextUtils.getCommaDelimitedString; import java.io.BufferedInputStream; import java.io.File; @@ -44,9 +45,13 @@ import java.util.Calendar; import java.util.Collection; import java.util.Date; +import java.util.HashMap; import java.util.Iterator; import java.util.List; +import java.util.Map; import java.util.UUID; +import java.util.regex.Matcher; +import java.util.regex.Pattern; import java.util.zip.ZipEntry; import java.util.zip.ZipOutputStream; @@ -65,6 +70,7 @@ import jxl.write.WritableWorkbook; import org.amplecode.quick.StatementManager; +import org.hisp.dhis.config.Configuration_IN; import org.hisp.dhis.i18n.I18nFormat; import org.hisp.dhis.organisationunit.OrganisationUnit; import org.hisp.dhis.organisationunit.OrganisationUnitGroup; @@ -74,6 +80,7 @@ import org.hisp.dhis.reports.ReportService; import org.hisp.dhis.reports.Report_in; import org.hisp.dhis.reports.Report_inDesign; +import org.hisp.dhis.system.util.MathUtils; import com.opensymphony.xwork2.Action; @@ -180,6 +187,8 @@ private SimpleDateFormat simpleDateFormat; private SimpleDateFormat monthFormat; + + private SimpleDateFormat yearFormat; private SimpleDateFormat simpleMonthFormat; @@ -209,6 +218,7 @@ String deCodesXMLFileName = ""; simpleDateFormat = new SimpleDateFormat( "MMM-yyyy" ); monthFormat = new SimpleDateFormat( "MMMM" ); + yearFormat = new SimpleDateFormat( "yyyy" ); simpleMonthFormat = new SimpleDateFormat( "MMM" ); String parentUnit = ""; @@ -221,8 +231,8 @@ String inputTemplatePath = System.getenv( "DHIS2_HOME" ) + File.separator + raFolderName + File.separator + "template" + File.separator + reportFileNameTB; - String outputReportFolderPath = System.getenv( "DHIS2_HOME" ) + File.separator + raFolderName + File.separator + "output" + File.separator + UUID.randomUUID().toString(); - + //String outputReportFolderPath = System.getenv( "DHIS2_HOME" ) + File.separator + raFolderName + File.separator + "output" + File.separator + UUID.randomUUID().toString(); + String outputReportFolderPath = System.getenv( "DHIS2_HOME" ) + File.separator + Configuration_IN.DEFAULT_TEMPFOLDER + File.separator + UUID.randomUUID().toString(); File newdir = new File( outputReportFolderPath ); if( !newdir.exists() ) { @@ -241,6 +251,11 @@ return INPUT; } + + // System.out.println( "---Size of Org Unit List ----: " + orgUnitList.size() + ",Report Group name is :---" + selReportObj.getOrgunitGroup().getName() + ", Size of Group member is ----:" + selReportObj.getOrgunitGroup().getMembers().size() ); + + System.out.println( " ---- Size of OrgUnit List is ---- " + orgUnitList.size() ); + OrganisationUnit selOrgUnit = organisationUnitService.getOrganisationUnit( ouIDTB ); System.out.println( selOrgUnit.getName()+ " : " + selReportObj.getName()+" : Report Generation Start Time is : " + new Date() ); @@ -253,9 +268,20 @@ Workbook templateWorkbook = Workbook.getWorkbook( new File( inputTemplatePath ) ); - + + // collect periodId by commaSepareted + List tempPeriodList = new ArrayList( periodService.getIntersectingPeriods( sDate, eDate ) ); + + Collection tempPeriodIds = new ArrayList( getIdentifiers(Period.class, tempPeriodList ) ); + + String periodIdsByComma = getCommaDelimitedString( tempPeriodIds ); + // Getting DataValues List reportDesignList = reportService.getReportDesign( deCodesXMLFileName ); + + // collect dataElementIDs by commaSepareted + String dataElmentIdsByComma = reportService.getDataelementIds( reportDesignList ); + int orgUnitCount = 0; Iterator it = orgUnitList.iterator(); @@ -269,7 +295,27 @@ String outputReportPath = outputReportFolderPath + File.separator + outPutFileName; WritableWorkbook outputReportWorkbook = Workbook.createWorkbook( new File( outputReportPath ), templateWorkbook ); + + + Map aggDeMap = new HashMap(); + if( aggData.equalsIgnoreCase( USEEXISTINGAGGDATA ) ) + { + aggDeMap.putAll( reportService.getResultDataValueFromAggregateTable( currentOrgUnit.getId(), dataElmentIdsByComma, periodIdsByComma ) ); + } + else if( aggData.equalsIgnoreCase( GENERATEAGGDATA ) ) + { + List childOrgUnitTree = new ArrayList( organisationUnitService.getOrganisationUnitWithChildren( currentOrgUnit.getId() ) ); + List childOrgUnitTreeIds = new ArrayList( getIdentifiers( OrganisationUnit.class, childOrgUnitTree ) ); + String childOrgUnitsByComma = getCommaDelimitedString( childOrgUnitTreeIds ); + aggDeMap.putAll( reportService.getAggDataFromDataValueTable( childOrgUnitsByComma, dataElmentIdsByComma, periodIdsByComma ) ); + } + else if( aggData.equalsIgnoreCase( USECAPTUREDDATA ) ) + { + aggDeMap.putAll( reportService.getAggDataFromDataValueTable( ""+currentOrgUnit.getId(), dataElmentIdsByComma, periodIdsByComma ) ); + } + + int count1 = 0; Iterator reportDesignIterator = reportDesignList.iterator(); while ( reportDesignIterator.hasNext() ) @@ -328,6 +374,10 @@ { tempStr = monthFormat.format( sDate ); } + else if( deCodeString.equalsIgnoreCase( "PERIOD-YEAR" ) ) + { + tempStr = yearFormat.format( sDate ); + } else if( deCodeString.equalsIgnoreCase( "MONTH-START-SHORT" ) ) { tempStr = simpleMonthFormat.format( sDate ); @@ -358,17 +408,23 @@ { if ( aggData.equalsIgnoreCase( USECAPTUREDDATA ) ) { - tempStr = reportService.getIndividualResultDataValue(deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit, reportModelTB ); + tempStr = getAggVal( deCodeString, aggDeMap ); + //tempStr = reportService.getIndividualResultDataValue(deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit, reportModelTB ); } else if( aggData.equalsIgnoreCase( GENERATEAGGDATA ) ) { - tempStr = reportService.getResultDataValue( deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit, reportModelTB ); + tempStr = getAggVal( deCodeString, aggDeMap ); + //tempStr = reportService.getResultDataValue( deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit, reportModelTB ); } else if( aggData.equalsIgnoreCase( USEEXISTINGAGGDATA ) ) { + + tempStr = getAggVal( deCodeString, aggDeMap ); + /* List periodList = new ArrayList( periodService.getPeriodsBetweenDates( tempStartDate.getTime(), tempEndDate.getTime() ) ); Collection periodIds = new ArrayList( getIdentifiers(Period.class, periodList ) ); tempStr = reportService.getResultDataValueFromAggregateTable( deCodeString, periodIds, currentOrgUnit, reportModelTB ); + */ } } else if ( sType.equalsIgnoreCase( "dataelement-boolean" ) ) @@ -558,5 +614,58 @@ return true; } - + + // getting data value using Map + private String getAggVal( String expression, Map aggDeMap ) + { + try + { + Pattern pattern = Pattern.compile( "(\\[\\d+\\.\\d+\\])" ); + + Matcher matcher = pattern.matcher( expression ); + StringBuffer buffer = new StringBuffer(); + + String resultValue = ""; + + while ( matcher.find() ) + { + String replaceString = matcher.group(); + + replaceString = replaceString.replaceAll( "[\\[\\]]", "" ); + + replaceString = aggDeMap.get( replaceString ); + + if( replaceString == null ) + { + replaceString = "0"; + } + + matcher.appendReplacement( buffer, replaceString ); + + resultValue = replaceString; + } + + matcher.appendTail( buffer ); + + double d = 0.0; + try + { + d = MathUtils.calculateExpression( buffer.toString() ); + } + catch ( Exception e ) + { + d = 0.0; + resultValue = ""; + } + + resultValue = "" + (double) d; + + return resultValue; + } + catch ( NumberFormatException ex ) + { + throw new RuntimeException( "Illegal DataElement id", ex ); + } + } + } === modified file 'local/in/dhis-web-reports-national/src/main/java/org/hisp/dhis/reports/auto/action/GenerateLLBulkReportAnalyserResultAction.java' --- local/in/dhis-web-reports-national/src/main/java/org/hisp/dhis/reports/auto/action/GenerateLLBulkReportAnalyserResultAction.java 2011-10-13 08:00:58 +0000 +++ local/in/dhis-web-reports-national/src/main/java/org/hisp/dhis/reports/auto/action/GenerateLLBulkReportAnalyserResultAction.java 2011-11-02 09:54:19 +0000 @@ -1,16 +1,18 @@ package org.hisp.dhis.reports.auto.action; +import static org.hisp.dhis.system.util.ConversionUtils.getIdentifiers; +import static org.hisp.dhis.system.util.TextUtils.getCommaDelimitedString; + import java.io.BufferedInputStream; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; -import java.sql.ResultSet; -import java.sql.Statement; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Calendar; +import java.util.Collection; import java.util.Collections; import java.util.Date; import java.util.HashMap; @@ -40,10 +42,6 @@ import org.amplecode.quick.StatementManager; import org.hisp.dhis.config.Configuration_IN; -import org.hisp.dhis.dataelement.DataElement; -import org.hisp.dhis.dataelement.DataElementCategoryOptionCombo; -import org.hisp.dhis.dataelement.DataElementCategoryService; -import org.hisp.dhis.dataelement.DataElementService; import org.hisp.dhis.i18n.I18nFormat; import org.hisp.dhis.organisationunit.OrganisationUnit; import org.hisp.dhis.organisationunit.OrganisationUnitGroup; @@ -53,6 +51,7 @@ import org.hisp.dhis.reports.ReportService; import org.hisp.dhis.reports.Report_in; import org.hisp.dhis.reports.Report_inDesign; +import org.hisp.dhis.system.util.MathUtils; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.support.rowset.SqlRowSet; @@ -105,7 +104,7 @@ { this.jdbcTemplate = jdbcTemplate; } - +/* private DataElementService dataElementService; public void setDataElementService( DataElementService dataElementService ) @@ -120,7 +119,7 @@ { this.dataElementCategoryOptionComboService = dataElementCategoryOptionComboService; } - +*/ // ------------------------------------------------------------------------- // Properties // ------------------------------------------------------------------------- @@ -172,6 +171,8 @@ private SimpleDateFormat simpleMonthFormat; + private SimpleDateFormat yearFormat; + private String reportFileNameTB; private String reportModelTB; @@ -197,6 +198,7 @@ String deCodesXMLFileName = ""; simpleDateFormat = new SimpleDateFormat( "MMM-yyyy" ); monthFormat = new SimpleDateFormat( "MMMM" ); + yearFormat = new SimpleDateFormat( "yyyy" ); simpleMonthFormat = new SimpleDateFormat( "MMM" ); String parentUnit = ""; @@ -234,6 +236,7 @@ } System.out.println( "---Size of Org Unit List ----: " + orgUnitList.size() + ",Report Group name is :---" + selReportObj.getOrgunitGroup().getName() + ", Size of Group member is ----:" + selReportObj.getOrgunitGroup().getMembers().size() ); + OrganisationUnit selOrgUnit = organisationUnitService.getOrganisationUnit( ouIDTB ); System.out.println( selOrgUnit.getName()+ " : " + selReportObj.getName()+" : Report Generation Start Time is : " + new Date() ); @@ -245,12 +248,23 @@ eDate = format.parseDate( String.valueOf( selectedPeriod.getEndDate() ) ); Workbook templateWorkbook = Workbook.getWorkbook( new File( inputTemplatePath ) ); + + // collect periodId by commaSepareted + List tempPeriodList = new ArrayList( periodService.getIntersectingPeriods( sDate, eDate ) ); + + Collection tempPeriodIds = new ArrayList( getIdentifiers(Period.class, tempPeriodList ) ); + + String periodIdsByComma = getCommaDelimitedString( tempPeriodIds ); // Getting DataValues List reportDesignList = reportService.getReportDesign( deCodesXMLFileName ); List reportDesignListLLDeath = reportService.getReportDesign( deCodesXMLFileName ); List reportDesignListLLMaternalDeath = reportService.getReportDesign( deCodesXMLFileName ); + // collect dataElementIDs by commaSepareted + String dataElmentIdsByComma = reportService.getDataelementIds( reportDesignList ); + String dataElmentIdsForLLDeathByComma = reportService.getDataelementIds( reportDesignList ); + String dataElmentIdsForMaternalDeathByComma = reportService.getDataelementIds( reportDesignList ); int orgUnitCount = 0; @@ -266,6 +280,14 @@ String outputReportPath = outputReportFolderPath + File.separator + outPutFileName; WritableWorkbook outputReportWorkbook = Workbook.createWorkbook( new File( outputReportPath ), templateWorkbook ); + Map aggDeMap = new HashMap(); + List childOrgUnitTree = new ArrayList( organisationUnitService.getOrganisationUnitWithChildren( currentOrgUnit.getId() ) ); + List childOrgUnitTreeIds = new ArrayList( getIdentifiers( OrganisationUnit.class, childOrgUnitTree ) ); + String childOrgUnitsByComma = getCommaDelimitedString( childOrgUnitTreeIds ); + + aggDeMap.putAll( reportService.getAggDataFromDataValueTable( childOrgUnitsByComma, dataElmentIdsByComma, periodIdsByComma ) ); + + int count1 = 0; Iterator reportDesignIterator = reportDesignList.iterator(); while ( reportDesignIterator.hasNext() ) @@ -323,6 +345,10 @@ else if( deCodeString.equalsIgnoreCase( "PERIOD-MONTH" ) ) { tempStr = monthFormat.format( sDate ); + } + else if( deCodeString.equalsIgnoreCase( "PERIOD-YEAR" ) ) + { + tempStr = yearFormat.format( sDate ); } else if( deCodeString.equalsIgnoreCase( "MONTH-START-SHORT" ) ) { @@ -352,7 +378,8 @@ { if( sType.equalsIgnoreCase( "dataelement" ) ) { - tempStr = reportService.getResultDataValue( deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit, reportModelTB ); + tempStr = getAggVal( deCodeString, aggDeMap ); + //tempStr = reportService.getResultDataValue( deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit, reportModelTB ); } else if ( sType.equalsIgnoreCase( "dataelement-boolean" ) ) { @@ -443,6 +470,15 @@ List llrecordNos = new ArrayList(); llrecordNos = getLinelistingDeathRecordNos( currentOrgUnit, selectedPeriod ); + String llDeathRecordNoByComma = getRecordNoByComma( llrecordNos ); + + + Map aggDeForLLDeathMap = new HashMap(); + + aggDeForLLDeathMap.putAll( reportService.getLLDeathDataFromLLDataValueTable( currentOrgUnit.getId(), dataElmentIdsForLLDeathByComma, periodIdsByComma, llDeathRecordNoByComma ) ); + + + // for Line Listing Death DataElements int tempLLDeathRowNo = 0; @@ -459,6 +495,11 @@ { recordNo = (Integer) itlldeath.next(); } + + //Map aggDeForLLDeathMap = new HashMap(); + + // aggDeForLLDeathMap.putAll( reportService.getLLDeathDataFromLLDataValueTable( currentOrgUnit.getId(), dataElmentIdsForLLDeathByComma, periodIdsByComma, recordNo ) ); + flag = 0; Iterator reportDesignIterator1 = reportDesignListLLDeath.iterator(); int count2 = 0; @@ -500,12 +541,14 @@ { if ( sType.equalsIgnoreCase( "lldeathdataelement" ) ) { - tempStr = getLLDataValue( deCodeString, selectedPeriod, currentOrgUnit, recordNo ); + tempStr = getLlDeathVal( deCodeString, recordNo, aggDeForLLDeathMap ); + //tempStr = getLLDataValue( deCodeString, selectedPeriod, currentOrgUnit, recordNo ); } else if ( sType.equalsIgnoreCase( "lldeathdataelementage" ) ) { - tempLLDeathValuStr = getLLDataValue( deCodeString, selectedPeriod, currentOrgUnit, recordNo ); + tempLLDeathValuStr = getLlDeathVal( deCodeString, recordNo, aggDeForLLDeathMap ); + //tempLLDeathValuStr = getLLDataValue( deCodeString, selectedPeriod, currentOrgUnit, recordNo ); } else { @@ -617,8 +660,15 @@ //llMaternalDeathrecordNos = getLinelistingMateralanRecordNos( currentOrgUnit, selectedPeriod, deCodesXMLFileName ); llMaternalDeathrecordNos = getLinelistingMateralanRecordNos( currentOrgUnit, selectedPeriod ); - System.out.println( "Line Listing Maternal Death Record Count is :" + llMaternalDeathrecordNos.size() ); - + //System.out.println( "Line Listing Maternal Death Record Count is :" + llMaternalDeathrecordNos.size() ); + + String llMaternalDeathRecordNoByComma = getRecordNoByComma( llMaternalDeathrecordNos ); + + Map aggDeForLLMaternalDeathMap = new HashMap(); + + aggDeForLLMaternalDeathMap.putAll( reportService.getLLDeathDataFromLLDataValueTable( currentOrgUnit.getId(), dataElmentIdsForMaternalDeathByComma, periodIdsByComma, llMaternalDeathRecordNoByComma ) ); + + // int testRowNo = 0; int flagmdeath = 0; @@ -634,7 +684,13 @@ maternalDeathRecordNo = (Integer) itllmaternaldeath.next(); } flagmdeath = 0; - + + + // Map aggDeForLLMaternalDeathMap = new HashMap(); + + //aggDeForLLMaternalDeathMap.putAll( reportService.getLLDeathDataFromLLDataValueTable( currentOrgUnit.getId(), dataElmentIdsForMaternalDeathByComma, periodIdsByComma, maternalDeathRecordNo ) ); + + // Iterator it1 = deCodesList.iterator(); Iterator reportDesignIterator2 = reportDesignListLLMaternalDeath.iterator(); int count3 = 0; @@ -683,7 +739,8 @@ { if ( sType.equalsIgnoreCase( "llmaternaldeathdataelement" ) ) { - tempStr = getLLDataValue( deCodeString, selectedPeriod, currentOrgUnit, maternalDeathRecordNo ); + tempStr = getLlDeathVal( deCodeString, maternalDeathRecordNo, aggDeForLLMaternalDeathMap ); + //tempStr = getLLDataValue( deCodeString, selectedPeriod, currentOrgUnit, maternalDeathRecordNo ); } } int tempRowNo1 = report_inDesign.getRowno(); @@ -976,7 +1033,7 @@ return recordNosList; } - + /* public String getLLDataValue( String formula, Period period, OrganisationUnit organisationUnit, Integer recordNo ) { Statement st1 = null; @@ -1081,7 +1138,8 @@ } }// finally block end } - +*/ + public List getLinelistingMateralanRecordNos( OrganisationUnit organisationUnit, Period period ) { List recordNosList = new ArrayList(); @@ -1111,4 +1169,118 @@ return recordNosList; } + // getting data value using Map + private String getAggVal( String expression, Map aggDeMap ) + { + try + { + Pattern pattern = Pattern.compile( "(\\[\\d+\\.\\d+\\])" ); + + Matcher matcher = pattern.matcher( expression ); + StringBuffer buffer = new StringBuffer(); + + String resultValue = ""; + + while ( matcher.find() ) + { + String replaceString = matcher.group(); + + replaceString = replaceString.replaceAll( "[\\[\\]]", "" ); + + replaceString = aggDeMap.get( replaceString ); + + if( replaceString == null ) + { + replaceString = "0"; + } + + matcher.appendReplacement( buffer, replaceString ); + + resultValue = replaceString; + } + + matcher.appendTail( buffer ); + + double d = 0.0; + try + { + d = MathUtils.calculateExpression( buffer.toString() ); + } + catch ( Exception e ) + { + d = 0.0; + resultValue = ""; + } + + resultValue = "" + (double) d; + + return resultValue; + } + catch ( NumberFormatException ex ) + { + throw new RuntimeException( "Illegal DataElement id", ex ); + } + } + + + private String getLlDeathVal( String expression,Integer recordNo, Map aggDeForLLDeathMap ) + { + try + { + Pattern pattern = Pattern.compile( "(\\[\\d+\\.\\d+\\])" ); + + Matcher matcher = pattern.matcher( expression ); + StringBuffer buffer = new StringBuffer(); + + String resultValue = ""; + + while ( matcher.find() ) + { + String replaceString = matcher.group(); + + replaceString = replaceString.replaceAll( "[\\[\\]]", "" ); + + + replaceString = aggDeForLLDeathMap.get( replaceString+":"+recordNo ); + + if( replaceString == null ) + { + replaceString = ""; + } + + matcher.appendReplacement( buffer, replaceString ); + + resultValue = replaceString; + } + + matcher.appendTail( buffer ); + + resultValue = buffer.toString(); + + return resultValue; + + } + catch ( NumberFormatException ex ) + { + throw new RuntimeException( "Illegal DataElement id", ex ); + } + catch ( Exception e ) + { + System.out.println( "SQL Exception : " + e.getMessage() ); + return null; + } + } + + public String getRecordNoByComma( List recordNosList ) + { + String recordNoByComma = "-1"; + + for( Integer recordNo : recordNosList ) + { + recordNoByComma += "," + recordNo; + } + + return recordNoByComma; + } + } === modified file 'local/in/dhis-web-reports-national/src/main/java/org/hisp/dhis/reports/routine/action/GenerateColdChainReportAnalyserResultAction.java' --- local/in/dhis-web-reports-national/src/main/java/org/hisp/dhis/reports/routine/action/GenerateColdChainReportAnalyserResultAction.java 2011-10-13 08:00:58 +0000 +++ local/in/dhis-web-reports-national/src/main/java/org/hisp/dhis/reports/routine/action/GenerateColdChainReportAnalyserResultAction.java 2011-11-02 09:54:19 +0000 @@ -59,13 +59,13 @@ import com.opensymphony.xwork2.Action; public class GenerateColdChainReportAnalyserResultAction -implements Action + implements Action { - + // ------------------------------------------------------------------------- // Dependencies // ------------------------------------------------------------------------- - + private StatementManager statementManager; public void setStatementManager( StatementManager statementManager ) @@ -107,21 +107,21 @@ { this.format = format; } - + private JdbcTemplate jdbcTemplate; public void setJdbcTemplate( JdbcTemplate jdbcTemplate ) { this.jdbcTemplate = jdbcTemplate; } - + private DataElementService dataElementService; public void setDataElementService( DataElementService dataElementService ) { this.dataElementService = dataElementService; } - + private DataElementCategoryService dataElementCategoryOptionComboService; public void setDataElementCategoryOptionComboService( @@ -129,6 +129,7 @@ { this.dataElementCategoryOptionComboService = dataElementCategoryOptionComboService; } + // ------------------------------------------------------------------------- // Properties // ------------------------------------------------------------------------- @@ -213,8 +214,9 @@ private PeriodType periodType; private String raFolderName; - + private OrganisationUnit currentOrgUnit; + // ------------------------------------------------------------------------- // Action implementation // ------------------------------------------------------------------------- @@ -239,23 +241,24 @@ deCodesXMLFileName = selReportObj.getXmlTemplateName(); reportModelTB = selReportObj.getModel(); reportFileNameTB = selReportObj.getExcelTemplateName(); - - String inputTemplatePath = System.getenv( "DHIS2_HOME" ) + File.separator + raFolderName + File.separator + "template" + File.separator + reportFileNameTB; - String outputReportPath = System.getenv( "DHIS2_HOME" ) + File.separator + Configuration_IN.DEFAULT_TEMPFOLDER; + + String inputTemplatePath = System.getenv( "DHIS2_HOME" ) + File.separator + raFolderName + File.separator + + "template" + File.separator + reportFileNameTB; + String outputReportPath = System.getenv( "DHIS2_HOME" ) + File.separator + Configuration_IN.DEFAULT_TEMPFOLDER; File newdir = new File( outputReportPath ); - if( !newdir.exists() ) + if ( !newdir.exists() ) { newdir.mkdirs(); } outputReportPath += File.separator + UUID.randomUUID().toString() + ".xls"; - if( reportModelTB.equalsIgnoreCase( "DYNAMIC-ORGUNIT" ) ) + if ( reportModelTB.equalsIgnoreCase( "DYNAMIC-ORGUNIT" ) ) { OrganisationUnit orgUnit = organisationUnitService.getOrganisationUnit( ouIDTB ); orgUnitList = new ArrayList( orgUnit.getChildren() ); Collections.sort( orgUnitList, new OrganisationUnitNameComparator() ); } - else if( reportModelTB.equalsIgnoreCase( "dynamicwithrootfacility" ) ) + else if ( reportModelTB.equalsIgnoreCase( "dynamicwithrootfacility" ) ) { OrganisationUnit orgUnit = organisationUnitService.getOrganisationUnit( ouIDTB ); orgUnitList = new ArrayList( orgUnit.getChildren() ); @@ -264,57 +267,69 @@ parentUnit = orgUnit.getName(); } - else if( reportModelTB.equalsIgnoreCase( "STATIC" ) || reportModelTB.equalsIgnoreCase( "STATIC-DATAELEMENTS" ) || reportModelTB.equalsIgnoreCase( "STATIC-FINANCIAL" ) ) + else if ( reportModelTB.equalsIgnoreCase( "STATIC" ) || reportModelTB.equalsIgnoreCase( "STATIC-DATAELEMENTS" ) + || reportModelTB.equalsIgnoreCase( "STATIC-FINANCIAL" ) ) { orgUnitList = new ArrayList(); OrganisationUnit orgUnit = organisationUnitService.getOrganisationUnit( ouIDTB ); orgUnitList.add( orgUnit ); } - System.out.println( orgUnitList.get( 0 ).getName()+ " : " + selReportObj.getName()+" : Report Generation Start Time is : " + new Date() ); + System.out.println( orgUnitList.get( 0 ).getName() + " : " + selReportObj.getName() + + " : Report Generation Start Time is : " + new Date() ); OrganisationUnitGroup orgUnitGroup = null; - + List orgGroupMembers = null; - if ( organisationUnitGroupId.equalsIgnoreCase( "ALL" ) || organisationUnitGroupId.equalsIgnoreCase( "Selected_Only" ) || organisationUnitGroupId.equalsIgnoreCase( "useexistingaggdata" ) ) + if ( organisationUnitGroupId.equalsIgnoreCase( "ALL" ) + || organisationUnitGroupId.equalsIgnoreCase( "Selected_Only" ) + || organisationUnitGroupId.equalsIgnoreCase( "useexistingaggdata" ) ) { - + } else { - orgUnitGroup = organisationUnitGroupService.getOrganisationUnitGroup( Integer.parseInt( organisationUnitGroupId ) ); + orgUnitGroup = organisationUnitGroupService.getOrganisationUnitGroup( Integer + .parseInt( organisationUnitGroupId ) ); orgGroupMembers = new ArrayList( orgUnitGroup.getMembers() ); } - + selectedPeriod = periodService.getPeriod( availablePeriods ); sDate = format.parseDate( String.valueOf( selectedPeriod.getStartDate() ) ); eDate = format.parseDate( String.valueOf( selectedPeriod.getEndDate() ) ); Workbook templateWorkbook = Workbook.getWorkbook( new File( inputTemplatePath ) ); - WritableWorkbook outputReportWorkbook = Workbook.createWorkbook( new File( outputReportPath ), templateWorkbook ); + WritableWorkbook outputReportWorkbook = Workbook + .createWorkbook( new File( outputReportPath ), templateWorkbook ); OrganisationUnitGroup excludeOrgUnitGroup = selReportObj.getOrgunitGroup(); List excludeOrgUnits = new ArrayList(); - if( excludeOrgUnitGroup != null ) + if ( excludeOrgUnitGroup != null ) { excludeOrgUnits.addAll( excludeOrgUnitGroup.getMembers() ); } - + // Getting DataValues List reportDesignList = reportService.getReportDesign( deCodesXMLFileName ); - List reportDesignColdChainList = reportService.getReportDesign( deCodesXMLFileName ); + // List reportDesignColdChainList = + // reportService.getReportDesign( deCodesXMLFileName ); + List reportDesignColdChainList = reportService + .getReportDesignWithMergeCells( deCodesXMLFileName ); int orgUnitCount = 0; Iterator it = orgUnitList.iterator(); while ( it.hasNext() ) { OrganisationUnit currentOrgUnit = (OrganisationUnit) it.next(); - List ouList = new ArrayList(); + List ouList = new ArrayList(); - if ( organisationUnitGroupId.equalsIgnoreCase( "ALL" ) || organisationUnitGroupId.equalsIgnoreCase( "Selected_Only" ) || organisationUnitGroupId.equalsIgnoreCase( "useexistingaggdata" ) ) + if ( organisationUnitGroupId.equalsIgnoreCase( "ALL" ) + || organisationUnitGroupId.equalsIgnoreCase( "Selected_Only" ) + || organisationUnitGroupId.equalsIgnoreCase( "useexistingaggdata" ) ) { - excludeOrgUnits.retainAll( organisationUnitService.getOrganisationUnitWithChildren( currentOrgUnit.getId() ) ); + excludeOrgUnits.retainAll( organisationUnitService.getOrganisationUnitWithChildren( currentOrgUnit + .getId() ) ); } else { @@ -322,7 +337,7 @@ excludeOrgUnits.retainAll( ouList ); ouList.retainAll( orgGroupMembers ); } - + Iterator reportDesignIterator = reportDesignList.iterator(); while ( reportDesignIterator.hasNext() ) { @@ -336,7 +351,8 @@ Calendar tempStartDate = Calendar.getInstance(); Calendar tempEndDate = Calendar.getInstance(); - List calendarList = new ArrayList( reportService.getStartingEndingPeriods( deType, selectedPeriod ) ); + List calendarList = new ArrayList( reportService.getStartingEndingPeriods( deType, + selectedPeriod ) ); if ( calendarList == null || calendarList.isEmpty() ) { tempStartDate.setTime( selectedPeriod.getStartDate() ); @@ -377,7 +393,8 @@ { tempStr = currentOrgUnit.getComment(); } - else if ( deCodeString.equalsIgnoreCase( "PERIOD" ) || deCodeString.equalsIgnoreCase( "PERIOD-NOREPEAT" ) ) + else if ( deCodeString.equalsIgnoreCase( "PERIOD" ) + || deCodeString.equalsIgnoreCase( "PERIOD-NOREPEAT" ) ) { tempStr = simpleDateFormat.format( sDate ); } @@ -761,9 +778,9 @@ String startMonth = ""; startMonth = monthFormat.format( sDateTemp ); - if ( startMonth.equalsIgnoreCase("January") ) + if ( startMonth.equalsIgnoreCase( "January" ) ) { - tempCalendar.set(Calendar.MONTH, Calendar.DECEMBER); + tempCalendar.set( Calendar.MONTH, Calendar.DECEMBER ); tempCalendar.roll( Calendar.YEAR, -1 ); sDateTemp = tempCalendar.getTime(); } @@ -795,15 +812,17 @@ { if ( organisationUnitGroupId.equalsIgnoreCase( "ALL" ) ) { - tempStr = reportService.getResultDataValue( deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit, reportModelTB ); - - if( excludeOrgUnits != null && excludeOrgUnits.size() != 0 ) + tempStr = reportService.getResultDataValue( deCodeString, tempStartDate.getTime(), + tempEndDate.getTime(), currentOrgUnit, reportModelTB ); + + if ( excludeOrgUnits != null && excludeOrgUnits.size() != 0 ) { double tempExcludeAggVal = 0.0; double value = 0.0; for ( OrganisationUnit unit : excludeOrgUnits ) { - String tempStr1 = reportService.getResultDataValue( deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), unit, reportModelTB ); + String tempStr1 = reportService.getResultDataValue( deCodeString, tempStartDate + .getTime(), tempEndDate.getTime(), unit, reportModelTB ); try { @@ -815,28 +834,30 @@ } tempExcludeAggVal += value; } - + try { value = Double.parseDouble( tempStr ) - tempExcludeAggVal; - tempStr = ""+value; + tempStr = "" + value; } - catch( Exception e ) + catch ( Exception e ) { } } } else if ( organisationUnitGroupId.equalsIgnoreCase( "Selected_Only" ) ) { - tempStr = reportService.getIndividualResultDataValue( deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit, reportModelTB ); - - if( excludeOrgUnits != null && excludeOrgUnits.size() != 0 ) + tempStr = reportService.getIndividualResultDataValue( deCodeString, + tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit, reportModelTB ); + + if ( excludeOrgUnits != null && excludeOrgUnits.size() != 0 ) { double tempExcludeAggVal = 0.0; double value = 0.0; for ( OrganisationUnit unit : excludeOrgUnits ) { - String tempStr1 = reportService.getResultDataValue( deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), unit, reportModelTB ); + String tempStr1 = reportService.getResultDataValue( deCodeString, tempStartDate + .getTime(), tempEndDate.getTime(), unit, reportModelTB ); try { @@ -848,30 +869,34 @@ } tempExcludeAggVal += value; } - + try { value = Double.parseDouble( tempStr ) - tempExcludeAggVal; - tempStr = ""+value; + tempStr = "" + value; } - catch( Exception e ) + catch ( Exception e ) { } } } else if ( organisationUnitGroupId.equalsIgnoreCase( "useexistingaggdata" ) ) { - List periodList = new ArrayList( periodService.getPeriodsBetweenDates( tempStartDate.getTime(), tempEndDate.getTime() ) ); - Collection periodIds = new ArrayList( getIdentifiers(Period.class, periodList ) ); - tempStr = reportService.getResultDataValueFromAggregateTable( deCodeString, periodIds, currentOrgUnit, reportModelTB ); - - if( excludeOrgUnits != null && excludeOrgUnits.size() != 0 ) + List periodList = new ArrayList( periodService.getPeriodsBetweenDates( + tempStartDate.getTime(), tempEndDate.getTime() ) ); + Collection periodIds = new ArrayList( getIdentifiers( Period.class, + periodList ) ); + tempStr = reportService.getResultDataValueFromAggregateTable( deCodeString, periodIds, + currentOrgUnit, reportModelTB ); + + if ( excludeOrgUnits != null && excludeOrgUnits.size() != 0 ) { double tempExcludeAggVal = 0.0; double value = 0.0; for ( OrganisationUnit unit : excludeOrgUnits ) { - String tempStr1 = reportService.getResultDataValue( deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), unit, reportModelTB ); + String tempStr1 = reportService.getResultDataValue( deCodeString, tempStartDate + .getTime(), tempEndDate.getTime(), unit, reportModelTB ); try { @@ -883,28 +908,32 @@ } tempExcludeAggVal += value; } - + try { value = Double.parseDouble( tempStr ) - tempExcludeAggVal; - tempStr = ""+value; + tempStr = "" + value; } - catch( Exception e ) + catch ( Exception e ) { } } } else { - //List ouList = new ArrayList( organisationUnitService.getOrganisationUnitWithChildren( currentOrgUnit.getId() ) ); - - //ouList.retainAll( orgGroupMembers ); - + // List ouList = new + // ArrayList( + // organisationUnitService.getOrganisationUnitWithChildren( + // currentOrgUnit.getId() ) ); + + // ouList.retainAll( orgGroupMembers ); + double temp = 0.0; double value = 0.0; for ( OrganisationUnit unit : ouList ) { - tempStr = reportService.getResultDataValue( deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), unit, reportModelTB ); + tempStr = reportService.getResultDataValue( deCodeString, tempStartDate.getTime(), + tempEndDate.getTime(), unit, reportModelTB ); try { @@ -919,14 +948,15 @@ tempNum = temp; tempStr = String.valueOf( (int) temp ); - - if( excludeOrgUnits != null && excludeOrgUnits.size() != 0 ) + + if ( excludeOrgUnits != null && excludeOrgUnits.size() != 0 ) { double tempExcludeAggVal = 0.0; value = 0.0; for ( OrganisationUnit unit : excludeOrgUnits ) { - String tempStr1 = reportService.getResultDataValue( deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), unit, reportModelTB ); + String tempStr1 = reportService.getResultDataValue( deCodeString, tempStartDate + .getTime(), tempEndDate.getTime(), unit, reportModelTB ); try { @@ -938,13 +968,13 @@ } tempExcludeAggVal += value; } - + try { value = Double.parseDouble( tempStr ) - tempExcludeAggVal; - tempStr = ""+value; + tempStr = "" + value; } - catch( Exception e ) + catch ( Exception e ) { } } @@ -954,23 +984,29 @@ { if ( organisationUnitGroupId.equalsIgnoreCase( "ALL" ) ) { - tempStr = reportService.getAggCountForTextData( deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit ); + tempStr = reportService.getAggCountForTextData( deCodeString, tempStartDate.getTime(), + tempEndDate.getTime(), currentOrgUnit ); } else if ( organisationUnitGroupId.equalsIgnoreCase( "Selected_Only" ) ) { - tempStr = reportService.getCountForTextData( deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit ); + tempStr = reportService.getCountForTextData( deCodeString, tempStartDate.getTime(), + tempEndDate.getTime(), currentOrgUnit ); } else { - //List ouList = new ArrayList( organisationUnitService.getOrganisationUnitWithChildren( currentOrgUnit.getId() ) ); - - //ouList.retainAll( orgGroupMembers ); - + // List ouList = new + // ArrayList( + // organisationUnitService.getOrganisationUnitWithChildren( + // currentOrgUnit.getId() ) ); + + // ouList.retainAll( orgGroupMembers ); + double temp = 0.0; double value = 0.0; for ( OrganisationUnit unit : ouList ) { - tempStr = reportService.getAggCountForTextData( deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), unit ); + tempStr = reportService.getAggCountForTextData( deCodeString, tempStartDate.getTime(), + tempEndDate.getTime(), unit ); try { @@ -993,25 +1029,31 @@ int year = Integer.parseInt( deType ); tempStartDate.set( year, 1, 1, 0, 0, 0 ); tempEndDate.setTime( selectedPeriod.getEndDate() ); - + if ( organisationUnitGroupId.equalsIgnoreCase( "ALL" ) ) { - tempStr = reportService.getResultDataValue( deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit, reportModelTB ); + tempStr = reportService.getResultDataValue( deCodeString, tempStartDate.getTime(), + tempEndDate.getTime(), currentOrgUnit, reportModelTB ); } else if ( organisationUnitGroupId.equalsIgnoreCase( "Selected_Only" ) ) { - tempStr = reportService.getIndividualResultDataValue( deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit, reportModelTB ); + tempStr = reportService.getIndividualResultDataValue( deCodeString, + tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit, reportModelTB ); } else { - //List ouList = new ArrayList( organisationUnitService.getOrganisationUnitWithChildren( currentOrgUnit.getId() ) ); - //ouList.retainAll( orgGroupMembers ); - + // List ouList = new + // ArrayList( + // organisationUnitService.getOrganisationUnitWithChildren( + // currentOrgUnit.getId() ) ); + // ouList.retainAll( orgGroupMembers ); + double temp = 0.0; double value = 0.0; for ( OrganisationUnit unit : ouList ) { - tempStr = reportService.getResultDataValue( deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), unit, reportModelTB ); + tempStr = reportService.getResultDataValue( deCodeString, tempStartDate.getTime(), + tempEndDate.getTime(), unit, reportModelTB ); try { value = Double.valueOf( tempStr ); @@ -1031,7 +1073,7 @@ { int year = Integer.parseInt( deType ); tempStartDate.set( year, 1, 1, 0, 0, 0 ); - + tempEndDate.setTime( selectedPeriod.getEndDate() ); if ( tempEndDate.get( Calendar.MONTH ) < Calendar.JULY ) { @@ -1041,22 +1083,28 @@ if ( organisationUnitGroupId.equalsIgnoreCase( "ALL" ) ) { - tempStr = reportService.getResultDataValue( deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit, reportModelTB ); + tempStr = reportService.getResultDataValue( deCodeString, tempStartDate.getTime(), + tempEndDate.getTime(), currentOrgUnit, reportModelTB ); } else if ( organisationUnitGroupId.equalsIgnoreCase( "Selected_Only" ) ) { - tempStr = reportService.getIndividualResultDataValue( deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit, reportModelTB ); + tempStr = reportService.getIndividualResultDataValue( deCodeString, + tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit, reportModelTB ); } else { - //List ouList = new ArrayList( organisationUnitService.getOrganisationUnitWithChildren( currentOrgUnit.getId() ) ); - //ouList.retainAll( orgGroupMembers ); - + // List ouList = new + // ArrayList( + // organisationUnitService.getOrganisationUnitWithChildren( + // currentOrgUnit.getId() ) ); + // ouList.retainAll( orgGroupMembers ); + double temp = 0.0; double value = 0.0; for ( OrganisationUnit unit : ouList ) { - tempStr = reportService.getResultDataValue( deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), unit, reportModelTB ); + tempStr = reportService.getResultDataValue( deCodeString, tempStartDate.getTime(), + tempEndDate.getTime(), unit, reportModelTB ); try { value = Double.valueOf( tempStr ); @@ -1072,11 +1120,11 @@ tempStr = String.valueOf( (int) temp ); } } - else if( sType.equalsIgnoreCase( "survey" )) + else if ( sType.equalsIgnoreCase( "survey" ) ) { tempStr = reportService.getResultSurveyValue( deCodeString, currentOrgUnit ); } - else if( sType.equalsIgnoreCase( "surveydesc" ) ) + else if ( sType.equalsIgnoreCase( "surveydesc" ) ) { tempStr = reportService.getSurveyDesc( deCodeString ); } @@ -1084,33 +1132,41 @@ { if ( aggCB == null ) { - tempStr = reportService.getBooleanDataValue( deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit, reportModelTB ); + tempStr = reportService.getBooleanDataValue( deCodeString, tempStartDate.getTime(), + tempEndDate.getTime(), currentOrgUnit, reportModelTB ); } else { - tempStr = reportService.getBooleanDataValue( deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit, reportModelTB ); + tempStr = reportService.getBooleanDataValue( deCodeString, tempStartDate.getTime(), + tempEndDate.getTime(), currentOrgUnit, reportModelTB ); } } else { if ( organisationUnitGroupId.equalsIgnoreCase( "ALL" ) ) { - tempStr = reportService.getResultIndicatorValue( deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit ); + tempStr = reportService.getResultIndicatorValue( deCodeString, tempStartDate.getTime(), + tempEndDate.getTime(), currentOrgUnit ); } else if ( organisationUnitGroupId.equalsIgnoreCase( "Selected_Only" ) ) { - tempStr = reportService.getIndividualResultIndicatorValue( deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit ); + tempStr = reportService.getIndividualResultIndicatorValue( deCodeString, tempStartDate + .getTime(), tempEndDate.getTime(), currentOrgUnit ); } else { - //List ouList = new ArrayList( organisationUnitService.getOrganisationUnitWithChildren( currentOrgUnit.getId() ) ); - //ouList.retainAll( orgGroupMembers ); - + // List ouList = new + // ArrayList( + // organisationUnitService.getOrganisationUnitWithChildren( + // currentOrgUnit.getId() ) ); + // ouList.retainAll( orgGroupMembers ); + double temp = 0.0; double value = 0.0; for ( OrganisationUnit unit : ouList ) { - tempStr = reportService.getResultIndicatorValue( deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), unit ); + tempStr = reportService.getResultIndicatorValue( deCodeString, tempStartDate.getTime(), + tempEndDate.getTime(), unit ); try { @@ -1129,16 +1185,16 @@ } } } - + int tempRowNo = report_inDesign.getRowno(); int tempColNo = report_inDesign.getColno(); int sheetNo = report_inDesign.getSheetno(); WritableSheet sheet0 = outputReportWorkbook.getSheet( sheetNo ); - + if ( tempStr == null || tempStr.equals( " " ) ) { tempRowNo += orgUnitCount; - + WritableCellFormat wCellformat = new WritableCellFormat(); wCellformat.setBorder( Border.ALL, BorderLineStyle.THIN ); wCellformat.setWrap( true ); @@ -1146,7 +1202,7 @@ sheet0.addCell( new Blank( tempColNo, tempRowNo, wCellformat ) ); } - else + else { if ( reportModelTB.equalsIgnoreCase( "DYNAMIC-ORGUNIT" ) ) { @@ -1189,8 +1245,8 @@ { if ( deCodeString.equalsIgnoreCase( "FACILITYP" ) || deCodeString.equalsIgnoreCase( "FACILITY-NOREPEAT" ) - || deCodeString.equalsIgnoreCase( "FACILITYPP" ) - || deCodeString.equalsIgnoreCase( "FACILITYPPP" ) + || deCodeString.equalsIgnoreCase( "FACILITYPP" ) + || deCodeString.equalsIgnoreCase( "FACILITYPPP" ) || deCodeString.equalsIgnoreCase( "FACILITYPPPP" ) ) { @@ -1257,13 +1313,13 @@ }// outer while loop end // for printing the cold-chain data - + // OrgUnit Info currentOrgUnit = organisationUnitService.getOrganisationUnit( ouIDTB ); - + List coldChainrecordNos = new ArrayList(); coldChainrecordNos = getColdChainRecordNos( currentOrgUnit, selectedPeriod ); - + int tempColdChainRowNo = 0; int flag = 0; if ( coldChainrecordNos.size() == 0 ) @@ -1271,7 +1327,7 @@ Iterator itColdChain = coldChainrecordNos.iterator(); int recordCount = 0; int currentRowNo = 0; - + while ( itColdChain.hasNext() ) { Integer recordNo = -1; @@ -1280,7 +1336,7 @@ recordNo = (Integer) itColdChain.next(); } flag = 0; - + Iterator reportDesignIterator = reportDesignColdChainList.iterator(); int count1 = 0; while ( reportDesignIterator.hasNext() ) @@ -1291,7 +1347,7 @@ String sType = report_inDesign.getStype(); String deCodeString = report_inDesign.getExpression(); String tempStr = ""; - + Calendar tempStartDate = Calendar.getInstance(); Calendar tempEndDate = Calendar.getInstance(); // List calendarList = new ArrayList( @@ -1309,11 +1365,11 @@ tempStartDate = calendarList.get( 0 ); tempEndDate = calendarList.get( 1 ); } - + if ( deCodeString.equalsIgnoreCase( "NA" ) ) { tempStr = " "; - + } else { @@ -1322,16 +1378,18 @@ tempStr = getColdChainDataValue( deCodeString, selectedPeriod, currentOrgUnit, recordNo ); } - } - + tempColdChainRowNo = report_inDesign.getRowno(); int tempRowNo = report_inDesign.getRowno(); - + currentRowNo = tempColdChainRowNo; int tempColNo = report_inDesign.getColno(); int sheetNo = report_inDesign.getSheetno(); - + + int tempMergeCol = report_inDesign.getColmerge(); + int tempMergeRow = report_inDesign.getRowmerge(); + WritableSheet sheet0 = outputReportWorkbook.getSheet( sheetNo ); if ( tempStr == null || tempStr.equals( " " ) ) { @@ -1341,11 +1399,11 @@ { if ( deCodeString.equalsIgnoreCase( "FACILITYP" ) || deCodeString.equalsIgnoreCase( "FACILITY-NOREPEAT" ) - || deCodeString.equalsIgnoreCase( "FACILITYPP" ) - || deCodeString.equalsIgnoreCase( "FACILITYPPP" ) + || deCodeString.equalsIgnoreCase( "FACILITYPP" ) + || deCodeString.equalsIgnoreCase( "FACILITYPPP" ) || deCodeString.equalsIgnoreCase( "FACILITYPPPP" ) ) { - + } else { @@ -1353,34 +1411,62 @@ currentRowNo += recordCount; tempRowNo += recordCount; } - - + WritableCellFormat wCellformat = new WritableCellFormat(); wCellformat.setBorder( Border.ALL, BorderLineStyle.THIN ); wCellformat.setWrap( true ); wCellformat.setAlignment( Alignment.CENTRE ); wCellformat.setVerticalAlignment( VerticalAlignment.CENTRE ); - + + WritableCell cell = sheet0.getWritableCell( tempColNo, tempRowNo ); + CellFormat cellFormat = cell.getCellFormat(); + if ( sType.equalsIgnoreCase( "coldchaindataelement" ) ) { - try - { - sheet0.addCell( new Number( tempColNo, tempRowNo, Integer.parseInt( tempStr ), getCellFormat1() ) ); - } - catch ( Exception e ) - { - sheet0.addCell( new Label( tempColNo, tempRowNo, tempStr, getCellFormat1() ) ); - } + + if ( cellFormat != null ) + { + if ( tempMergeCol > 0 || tempMergeRow > 0 ) + { + sheet0.mergeCells( tempColNo, tempRowNo, tempColNo + tempMergeCol, tempRowNo + + tempMergeRow ); + } + try + { + sheet0.addCell( new Number( tempColNo, tempRowNo, Integer.parseInt( tempStr ), + getCellFormat1() ) ); + } + catch ( Exception e ) + { + sheet0.addCell( new Label( tempColNo, tempRowNo, tempStr, getCellFormat1() ) ); + } + } + else + { + if ( tempMergeCol > 0 || tempMergeRow > 0 ) + { + sheet0.mergeCells( tempColNo, tempRowNo, tempColNo + tempMergeCol, tempRowNo + + tempMergeRow ); + } + try + { + sheet0.addCell( new Number( tempColNo, tempRowNo, Integer.parseInt( tempStr ), + getCellFormat1() ) ); + } + catch ( Exception e ) + { + sheet0.addCell( new Label( tempColNo, tempRowNo, tempStr, getCellFormat1() ) ); + } + } + } } count1++; } recordCount++; } - - - + outputReportWorkbook.write(); outputReportWorkbook.close(); @@ -1390,139 +1476,141 @@ File outputReportFile = new File( outputReportPath ); inputStream = new BufferedInputStream( new FileInputStream( outputReportFile ) ); - System.out.println( orgUnitList.get( 0 ).getName()+ " : " + selReportObj.getName()+" : Report Generation End Time is : " + new Date() ); + System.out.println( orgUnitList.get( 0 ).getName() + " : " + selReportObj.getName() + + " : Report Generation End Time is : " + new Date() ); outputReportFile.deleteOnExit(); statementManager.destroy(); return SUCCESS; - - } - // Method Finding no of records of coldChain - - public List getColdChainRecordNos( OrganisationUnit organisationUnit, Period period ) - { - List recordNosList = new ArrayList(); - - int dataElementid = 1027; - String query = ""; - - try - { - query = "SELECT recordno FROM lldatavalue WHERE dataelementid = " + dataElementid + " AND periodid = " - + period.getId() + " AND sourceid = " + organisationUnit.getId(); - - SqlRowSet rs1 = jdbcTemplate.queryForRowSet( query ); - - while ( rs1.next() ) - { - recordNosList.add( rs1.getInt( 1 ) ); - } - - Collections.sort( recordNosList ); - } - catch ( Exception e ) - { - System.out.println( "SQL Exception : " + e.getMessage() ); - } - - return recordNosList; - } - - - public String getColdChainDataValue( String formula, Period period, OrganisationUnit organisationUnit, Integer recordNo ) - { - Statement st1 = null; - ResultSet rs1 = null; - - String query = ""; - try - { - Pattern pattern = Pattern.compile( "(\\[\\d+\\.\\d+\\])" ); - - Matcher matcher = pattern.matcher( formula ); - StringBuffer buffer = new StringBuffer(); - - while ( matcher.find() ) - { - String replaceString = matcher.group(); - - replaceString = replaceString.replaceAll( "[\\[\\]]", "" ); - String optionComboIdStr = replaceString.substring( replaceString.indexOf( '.' ) + 1, replaceString - .length() ); - - replaceString = replaceString.substring( 0, replaceString.indexOf( '.' ) ); - - int dataElementId = Integer.parseInt( replaceString ); - int optionComboId = Integer.parseInt( optionComboIdStr ); - - DataElement dataElement = dataElementService.getDataElement( dataElementId ); - DataElementCategoryOptionCombo optionCombo = dataElementCategoryOptionComboService.getDataElementCategoryOptionCombo( optionComboId ); - - if ( dataElement == null || optionCombo == null ) - { - replaceString = ""; - matcher.appendReplacement( buffer, replaceString ); - continue; - } - - - - query = "SELECT value FROM lldatavalue WHERE sourceid = " + organisationUnit.getId() - + " AND periodid = " + period.getId() + " AND dataelementid = " + dataElement.getId() - + " AND recordno = " + recordNo; - // rs1 = st1.executeQuery( query ); - - SqlRowSet sqlResultSet = jdbcTemplate.queryForRowSet( query ); - - String tempStr = ""; - - if ( sqlResultSet.next() ) - { - tempStr = sqlResultSet.getString( 1 ); - } - - replaceString = tempStr; - + + } + + // Method Finding no of records of coldChain + + public List getColdChainRecordNos( OrganisationUnit organisationUnit, Period period ) + { + List recordNosList = new ArrayList(); + + int dataElementid = 5786; + String query = ""; + + try + { + query = "SELECT recordno FROM lldatavalue WHERE dataelementid = " + dataElementid + " AND periodid = " + + period.getId() + " AND sourceid = " + organisationUnit.getId(); + + SqlRowSet rs1 = jdbcTemplate.queryForRowSet( query ); + + while ( rs1.next() ) + { + recordNosList.add( rs1.getInt( 1 ) ); + } + + Collections.sort( recordNosList ); + } + catch ( Exception e ) + { + System.out.println( "SQL Exception : " + e.getMessage() ); + } + + return recordNosList; + } + + public String getColdChainDataValue( String formula, Period period, OrganisationUnit organisationUnit, + Integer recordNo ) + { + Statement st1 = null; + ResultSet rs1 = null; + + String query = ""; + try + { + Pattern pattern = Pattern.compile( "(\\[\\d+\\.\\d+\\])" ); + + Matcher matcher = pattern.matcher( formula ); + StringBuffer buffer = new StringBuffer(); + + while ( matcher.find() ) + { + String replaceString = matcher.group(); + + replaceString = replaceString.replaceAll( "[\\[\\]]", "" ); + String optionComboIdStr = replaceString.substring( replaceString.indexOf( '.' ) + 1, replaceString + .length() ); + + replaceString = replaceString.substring( 0, replaceString.indexOf( '.' ) ); + + int dataElementId = Integer.parseInt( replaceString ); + int optionComboId = Integer.parseInt( optionComboIdStr ); + + DataElement dataElement = dataElementService.getDataElement( dataElementId ); + DataElementCategoryOptionCombo optionCombo = dataElementCategoryOptionComboService + .getDataElementCategoryOptionCombo( optionComboId ); + + if ( dataElement == null || optionCombo == null ) + { + replaceString = ""; matcher.appendReplacement( buffer, replaceString ); - } - - matcher.appendTail( buffer ); - - String resultValue = ""; - - resultValue = buffer.toString(); - - return resultValue; + continue; + } + + query = "SELECT value FROM lldatavalue WHERE sourceid = " + organisationUnit.getId() + + " AND periodid = " + period.getId() + " AND dataelementid = " + dataElement.getId() + + " AND recordno = " + recordNo; + // rs1 = st1.executeQuery( query ); + + SqlRowSet sqlResultSet = jdbcTemplate.queryForRowSet( query ); + + String tempStr = ""; + + if ( sqlResultSet.next() ) + { + tempStr = sqlResultSet.getString( 1 ); + } + + replaceString = tempStr; + + matcher.appendReplacement( buffer, replaceString ); } - catch ( NumberFormatException ex ) + + matcher.appendTail( buffer ); + + String resultValue = ""; + + resultValue = buffer.toString(); + + return resultValue; + } + catch ( NumberFormatException ex ) + { + throw new RuntimeException( "Illegal DataElement id", ex ); + } + catch ( Exception e ) + { + System.out.println( "SQL Exception : " + e.getMessage() ); + return null; + } + finally + { + try { - throw new RuntimeException( "Illegal DataElement id", ex ); + if ( st1 != null ) + st1.close(); + + if ( rs1 != null ) + rs1.close(); } catch ( Exception e ) { System.out.println( "SQL Exception : " + e.getMessage() ); return null; } - finally - { - try - { - if ( st1 != null ) - st1.close(); + }// finally block end + } - if ( rs1 != null ) - rs1.close(); - } - catch ( Exception e ) - { - System.out.println( "SQL Exception : " + e.getMessage() ); - return null; - } - }// finally block end - } - public WritableCellFormat getCellFormat1() + public WritableCellFormat getCellFormat1() throws Exception { WritableCellFormat wCellformat = new WritableCellFormat(); @@ -1532,6 +1620,5 @@ wCellformat.setWrap( true ); return wCellformat; - } + } } - === modified file 'local/in/dhis-web-reports-national/src/main/java/org/hisp/dhis/reports/routine/action/GenerateColdChainRoutineReportAnalyserFormAction.java' --- local/in/dhis-web-reports-national/src/main/java/org/hisp/dhis/reports/routine/action/GenerateColdChainRoutineReportAnalyserFormAction.java 2011-10-13 08:00:58 +0000 +++ local/in/dhis-web-reports-national/src/main/java/org/hisp/dhis/reports/routine/action/GenerateColdChainRoutineReportAnalyserFormAction.java 2011-11-02 09:54:19 +0000 @@ -68,8 +68,8 @@ public String execute() throws Exception { - reportTypeName = ReportType.RT_ROUTINE; - + //reportTypeName = ReportType.RT_ROUTINE; + reportTypeName = ReportType.RT_COLDCHAIN_REPORT; periodTypes = periodService.getAllPeriodTypes(); Iterator periodTypeIterator = periodTypes.iterator(); === modified file 'local/in/dhis-web-reports-national/src/main/resources/META-INF/dhis/beans.xml' --- local/in/dhis-web-reports-national/src/main/resources/META-INF/dhis/beans.xml 2011-10-13 08:00:58 +0000 +++ local/in/dhis-web-reports-national/src/main/resources/META-INF/dhis/beans.xml 2011-11-02 09:54:19 +0000 @@ -243,8 +243,8 @@ - - + === modified file 'local/in/dhis-web-reports-national/src/main/resources/org/hisp/dhis/reports/i18n_module.properties' --- local/in/dhis-web-reports-national/src/main/resources/org/hisp/dhis/reports/i18n_module.properties 2011-10-13 08:00:58 +0000 +++ local/in/dhis-web-reports-national/src/main/resources/org/hisp/dhis/reports/i18n_module.properties 2011-11-02 09:54:19 +0000 @@ -1,6 +1,6 @@ goi_ra = Government of India Reports Analyser routine_ra = Routine Report Analyser -routine_ra_cold_chain = Routine(Cold-Chain) Report Analyser +ra_cold_chain = Cold Chain Report Analyser physical_output_ra = Quarterly Physical Output Report periodwise_progress_ra = PeriodWise Progress Report Analyser orgunitwise_progress_ra = OrganisationunitWise Progress Report Analyser === modified file 'local/in/dhis-web-reports-national/src/main/webapp/dhis-web-reports/coldChainRoutineReportAnalyserFront.vm' --- local/in/dhis-web-reports-national/src/main/webapp/dhis-web-reports/coldChainRoutineReportAnalyserFront.vm 2011-10-13 08:00:58 +0000 +++ local/in/dhis-web-reports-national/src/main/webapp/dhis-web-reports/coldChainRoutineReportAnalyserFront.vm 2011-11-02 09:54:19 +0000 @@ -28,7 +28,7 @@ } -

$i18n.getString( "routine_ra_cold_chain" )

+

$i18n.getString( "ra_cold_chain" )