=== modified file 'local/vn/dhis-service-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/ExportReport.java' --- local/vn/dhis-service-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/ExportReport.java 2012-11-26 09:12:15 +0000 +++ local/vn/dhis-service-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/ExportReport.java 2012-12-11 20:24:41 +0000 @@ -65,7 +65,7 @@ private String excelTemplateFile; - private String createdBy = "[unknown]"; + private String createdBy = "[DHIS-System]"; // ------------------------------------------------------------------------- // Constructors @@ -77,6 +77,25 @@ this.organisationAssocitions = new HashSet(); } + public ExportReport( String name, String group, String excelTemplateFile ) + { + this.name = name; + this.group = group; + this.excelTemplateFile = excelTemplateFile; + this.exportItems = new HashSet(); + this.organisationAssocitions = new HashSet(); + } + + public ExportReport( String name, String group, String excelTemplateFile, String createdBy ) + { + this.name = name; + this.group = group; + this.excelTemplateFile = excelTemplateFile; + this.createdBy = (createdBy == null || createdBy.trim().isEmpty()) ? this.createdBy : createdBy; + this.exportItems = new HashSet(); + this.organisationAssocitions = new HashSet(); + } + public Collection getExportItemBySheet( Integer sheetNo ) { Set results = new HashSet(); @@ -385,6 +404,6 @@ public void setCreatedBy( String createdBy ) { - this.createdBy = createdBy; + this.createdBy = (createdBy == null || createdBy.trim().isEmpty()) ? this.createdBy : createdBy; } } === modified file 'local/vn/dhis-service-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/ExportReportAttribute.java' --- local/vn/dhis-service-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/ExportReportAttribute.java 2012-07-04 10:12:19 +0000 +++ local/vn/dhis-service-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/ExportReportAttribute.java 2012-12-11 20:24:41 +0000 @@ -49,6 +49,16 @@ super(); } + public ExportReportAttribute( String name, String group, String excelTemplateFile ) + { + super( name, group, excelTemplateFile ); + } + + public ExportReportAttribute( String name, String group, String excelTemplateFile, String createdBy ) + { + super( name, group, excelTemplateFile, createdBy ); + } + // ------------------------------------------------------------------------- // Logic // ------------------------------------------------------------------------- === modified file 'local/vn/dhis-service-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/ExportReportCategory.java' --- local/vn/dhis-service-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/ExportReportCategory.java 2012-04-26 18:25:07 +0000 +++ local/vn/dhis-service-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/ExportReportCategory.java 2012-12-11 20:24:41 +0000 @@ -47,6 +47,16 @@ super(); } + public ExportReportCategory( String name, String group, String excelTemplateFile ) + { + super( name, group, excelTemplateFile ); + } + + public ExportReportCategory( String name, String group, String excelTemplateFile, String createdBy ) + { + super( name, group, excelTemplateFile, createdBy ); + } + // ------------------------------------------------------------------------- // Getters and setters // ------------------------------------------------------------------------- @@ -74,7 +84,7 @@ types.add( ExportItem.TYPE.DATAELEMENT ); types.add( ExportItem.TYPE.DATAELEMENT_CODE ); types.add( ExportItem.TYPE.DATAELEMENT_NAME ); - types.add( ExportItem.TYPE.FORMULA_EXCEL); + types.add( ExportItem.TYPE.FORMULA_EXCEL ); types.add( ExportItem.TYPE.SERIAL ); return types; === modified file 'local/vn/dhis-service-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/ExportReportNormal.java' --- local/vn/dhis-service-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/ExportReportNormal.java 2012-04-26 18:25:07 +0000 +++ local/vn/dhis-service-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/ExportReportNormal.java 2012-12-11 20:24:41 +0000 @@ -45,6 +45,16 @@ super(); } + public ExportReportNormal( String name, String group, String excelTemplateFile ) + { + super( name, group, excelTemplateFile ); + } + + public ExportReportNormal( String name, String group, String excelTemplateFile, String createdBy ) + { + super( name, group, excelTemplateFile, createdBy ); + } + @Override public String getReportType() { === modified file 'local/vn/dhis-service-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/ExportReportOrganizationGroupListing.java' --- local/vn/dhis-service-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/ExportReportOrganizationGroupListing.java 2012-04-26 18:25:07 +0000 +++ local/vn/dhis-service-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/ExportReportOrganizationGroupListing.java 2012-12-11 20:24:41 +0000 @@ -54,6 +54,16 @@ super(); } + public ExportReportOrganizationGroupListing( String name, String group, String excelTemplateFile ) + { + super( name, group, excelTemplateFile ); + } + + public ExportReportOrganizationGroupListing( String name, String group, String excelTemplateFile, String createdBy ) + { + super( name, group, excelTemplateFile, createdBy ); + } + // ------------------------------------------------------------------------- // Getters and setters // ------------------------------------------------------------------------- === modified file 'local/vn/dhis-service-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/ExportReportPeriodColumnListing.java' --- local/vn/dhis-service-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/ExportReportPeriodColumnListing.java 2012-04-27 10:13:06 +0000 +++ local/vn/dhis-service-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/ExportReportPeriodColumnListing.java 2012-12-11 20:24:41 +0000 @@ -50,6 +50,16 @@ super(); } + public ExportReportPeriodColumnListing( String name, String group, String excelTemplateFile ) + { + super( name, group, excelTemplateFile ); + } + + public ExportReportPeriodColumnListing( String name, String group, String excelTemplateFile, String createdBy ) + { + super( name, group, excelTemplateFile, createdBy ); + } + public void addPeriodColumn( PeriodColumn periodColumn ) { periodColumns.add( periodColumn ); === modified file 'local/vn/dhis-service-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/ExportReportVerticalCategory.java' --- local/vn/dhis-service-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/ExportReportVerticalCategory.java 2012-04-26 18:25:07 +0000 +++ local/vn/dhis-service-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/ExportReportVerticalCategory.java 2012-12-11 20:24:41 +0000 @@ -35,7 +35,7 @@ */ public class ExportReportVerticalCategory extends ExportReport -{ +{ private List categoryOptionGroupOrders; // ------------------------------------------------------------------------- @@ -47,6 +47,16 @@ super(); } + public ExportReportVerticalCategory( String name, String group, String excelTemplateFile ) + { + super( name, group, excelTemplateFile ); + } + + public ExportReportVerticalCategory( String name, String group, String excelTemplateFile, String createdBy ) + { + super( name, group, excelTemplateFile, createdBy ); + } + // ------------------------------------------------------------------------- // Getters and setters // ------------------------------------------------------------------------- === modified file 'local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/exportreport/action/AddExportReportAction.java' --- local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/exportreport/action/AddExportReportAction.java 2012-11-16 11:04:48 +0000 +++ local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/exportreport/action/AddExportReportAction.java 2012-12-11 20:24:41 +0000 @@ -194,6 +194,7 @@ exportReport.setName( name ); exportReport.setExcelTemplateFile( excel ); exportReport.setGroup( group ); + exportReport.setCreatedBy( currentUserService.getCurrentUsername() ); if ( periodCol != null && periodRow != null ) { @@ -212,15 +213,6 @@ exportReport.setDataSets( new HashSet( dataSetService.getDataSets( dataSetIds ) ) ); } - String createdBy = currentUserService.getCurrentUsername(); - - if ( createdBy == null ) - { - createdBy = "[unknown]"; - } - - exportReport.setCreatedBy( createdBy ); - exportReportService.addExportReport( exportReport ); return SUCCESS; === modified file 'local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/preview/action/AutoGenerateFormByTemplate.java' --- local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/preview/action/AutoGenerateFormByTemplate.java 2012-12-11 15:34:39 +0000 +++ local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/preview/action/AutoGenerateFormByTemplate.java 2012-12-11 20:24:41 +0000 @@ -27,35 +27,53 @@ * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. */ +import static org.apache.commons.io.FilenameUtils.getBaseName; import static org.apache.commons.io.FilenameUtils.getExtension; +import static org.apache.commons.io.FilenameUtils.getName; +import static org.hisp.dhis.dataelement.DataElement.AGGREGATION_OPERATOR_SUM; +import static org.hisp.dhis.dataelement.DataElement.DOMAIN_TYPE_AGGREGATE; +import static org.hisp.dhis.dataelement.DataElement.VALUE_TYPE_INT; +import static org.hisp.dhis.reportsheet.ExportItem.PERIODTYPE.SELECTED_MONTH; +import static org.hisp.dhis.reportsheet.ExportItem.TYPE.DATAELEMENT; +import static org.hisp.dhis.reportsheet.utils.ExcelUtils.convertAlignmentString; +import static org.hisp.dhis.reportsheet.utils.ExcelUtils.readValueByPOI; -import java.io.File; import java.io.FileInputStream; +import java.io.IOException; import java.io.InputStream; import java.util.Collection; +import java.util.HashMap; import java.util.HashSet; +import java.util.Map; import java.util.Set; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Comment; +import org.apache.poi.ss.usermodel.Font; +import org.apache.poi.ss.usermodel.FormulaEvaluator; +import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; +import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.hisp.dhis.dataelement.DataElement; -import org.hisp.dhis.dataelement.DataElementCategoryCombo; +import org.hisp.dhis.dataelement.DataElementCategoryOptionCombo; import org.hisp.dhis.dataelement.DataElementCategoryService; import org.hisp.dhis.dataelement.DataElementService; import org.hisp.dhis.dataentryform.DataEntryForm; import org.hisp.dhis.dataentryform.DataEntryFormService; import org.hisp.dhis.dataset.DataSet; import org.hisp.dhis.dataset.DataSetService; +import org.hisp.dhis.period.MonthlyPeriodType; import org.hisp.dhis.period.PeriodType; import org.hisp.dhis.reportsheet.ExportItem; import org.hisp.dhis.reportsheet.ExportReport; import org.hisp.dhis.reportsheet.ExportReportNormal; import org.hisp.dhis.reportsheet.ExportReportService; +import org.hisp.dhis.reportsheet.state.SelectionManager; import org.springframework.beans.factory.annotation.Autowired; import com.opensymphony.xwork2.Action; @@ -69,8 +87,39 @@ * @version $Id$ */ -public class AutoGenerateFormByTemplate implements Action +public class AutoGenerateFormByTemplate + implements Action { + private static final String REPORT_EXCEL_GROUP = "BAO CAO THONG KE"; + + private static final String WORKBOOK_OPENTAG = ""; + + private static final String WORKBOOK_CLOSETAG = ""; + + private static final String MERGEDCELL_OPENTAG = ""; + + private static final String MERGEDCELL_CLOSETAG = ""; + + private String excelFileName = ""; + + private String commonName = ""; + + /** + * The workbook we are reading from a given file + */ + private Workbook WORKBOOK; + + private FormulaEvaluator evaluatorFormula; + + /** + * The encoding to write + */ + private StringBuffer xml = new StringBuffer( 200000 ); + + // ------------------------------------------------------------------------- + // Dependencies + // ------------------------------------------------------------------------- + @Autowired private DataElementService dataElementService; @@ -78,7 +127,7 @@ private DataElementCategoryService categoryService; @Autowired - private DataEntryFormService dataEntryFormService; + private DataEntryFormService dataEntryFormService; @Autowired private DataSetService dataSetService; @@ -86,10 +135,12 @@ @Autowired private ExportReportService exportReportService; - /** - * The workbook we are reading from a given file - */ - private Workbook WORKBOOK; + @Autowired + protected SelectionManager selectionManager; + + // ------------------------------------------------------------------------- + // Action implementation + // ------------------------------------------------------------------------- public String execute() { @@ -98,7 +149,7 @@ try { - autoGenerateFormByTemplate( "d:\\template_file.xls", collectSheets ); + autoGenerateFormByTemplate( selectionManager.getUploadFilePath(), collectSheets ); } catch ( Exception e ) { @@ -107,12 +158,12 @@ return SUCCESS; } - - // ------------------------------------------------------------------------- - // Get & Set methods - // ------------------------------------------------------------------------- - - private void cleanUpForResponse() + + // ------------------------------------------------------------------------- + // Sub-methods + // ------------------------------------------------------------------------- + + private void cleanUp() { System.gc(); } @@ -128,15 +179,14 @@ * @exception java.io.IOException */ - private String excelFileName = ""; - private void autoGenerateFormByTemplate( String pathFileName, Set collectSheets ) throws Exception { - this.cleanUpForResponse(); + this.cleanUp(); InputStream inputSteam = new FileInputStream( pathFileName ); - excelFileName = new File( pathFileName ).getName(); + excelFileName = getName( pathFileName ); + commonName = getBaseName( pathFileName ); if ( getExtension( pathFileName ).equals( "xls" ) ) { @@ -161,100 +211,135 @@ private void writeFormattedXML( Collection collectSheets ) throws Exception { - for ( Integer sheet : collectSheets ) - { - createFormByComment( sheet ); - } - } - - // ------------------------------------------------------------------------- - // Sub-methods - // ------------------------------------------------------------------------- - - private void createFormByComment( int sheetNo ) - { - DataElementCategoryCombo catagoryCombo = categoryService.getDefaultDataElementCategoryOptionCombo() - .getCategoryCombo(); - int catagoryOptionComboId = categoryService.getDefaultDataElementCategoryOptionCombo().getId(); - PeriodType periodType = PeriodType.getPeriodTypeByName( "Monthly" ); + this.writeXMLMergedDescription( collectSheets ); + + xml.append( WORKBOOK_OPENTAG ); + + PeriodType periodType = PeriodType.getPeriodTypeByName( MonthlyPeriodType.NAME ); + + DataElementCategoryOptionCombo optionCombo = categoryService.getDefaultDataElementCategoryOptionCombo(); + + // Create new DataSet + DataSet dataSet = new DataSet( commonName, commonName, periodType ); + dataSetService.addDataSet( dataSet ); + + // Create new ExportReport + ExportReport exportReport = new ExportReportNormal( commonName, REPORT_EXCEL_GROUP, excelFileName, null ); + exportReportService.addExportReport( exportReport ); + StringBuffer htmlCode = new StringBuffer(); + + for ( Integer sheet : collectSheets ) + { + createFormByComment( sheet, optionCombo, periodType, htmlCode, dataSet, exportReport ); + } + + // Update DataSet + DataEntryForm dataEntryForm = new DataEntryForm( commonName, htmlCode.toString() ); + dataEntryFormService.addDataEntryForm( dataEntryForm ); + + dataSet.setDataEntryForm( dataEntryForm ); + dataSetService.updateDataSet( dataSet ); + + // Update ExportReport + Set dataSets = new HashSet(); + dataSets.add( dataSet ); + + exportReport.setDataSets( dataSets ); + exportReportService.updateExportReport( exportReport ); + + xml.append( WORKBOOK_CLOSETAG ); + } + + private void createFormByComment( int sheetNo, DataElementCategoryOptionCombo optionCombo, PeriodType periodType, + StringBuffer htmlCode, DataSet dataSet, ExportReport exportReport ) + { Sheet s = WORKBOOK.getSheetAt( sheetNo - 1 ); - DataSet dataSet = new DataSet( excelFileName, excelFileName, periodType ); - - // Generate report - ExportReport exportReport = new ExportReportNormal(); - exportReport.setName( WORKBOOK.getSheetName( sheetNo - 1 ) ); - exportReport.setExcelTemplateFile( excelFileName ); - exportReport.setGroup( excelFileName ); - exportReport.setCreatedBy( "DHIS-System" ); - int reportId = exportReportService.addExportReport( exportReport ); + xml.append( "" ); + xml.append( "" ); try { for ( Row row : s ) - { + { + xml.append( "" ); + + Map idxMap = new HashMap(); + for ( Cell cell : row ) { Comment cmt = cell.getCellComment(); - if ( cell.getCellComment() != null ) + + if ( cmt != null ) { String deName = cell.getStringCellValue(); - String[] indexes = cmt.getString().toString().split( "," ); + String[] indexes = cmt.getString().getString().split( "," ); + int rowIndex = cell.getRowIndex(); for ( String index : indexes ) { - String name = deName + "(" + index + ")"; - int idx = Integer.parseInt( index ) - 1; - - // Generate dataElement + String name = deName + " (" + index + ")"; + int idx = Integer.parseInt( index ); + + // Generate DataElement DataElement dataElement = new DataElement( name ); + /** TAKE CARE OF SHORT_NAME IS TOO LONG */ dataElement.setShortName( name ); dataElement.setActive( true ); - dataElement.setDomainType( "aggregate" ); - dataElement.setType( DataElement.VALUE_TYPE_INT ); - dataElement.setNumberType( DataElement.VALUE_TYPE_INT ); - dataElement.setCategoryCombo( catagoryCombo ); - dataElement.setAggregationOperator( "sum" ); dataElement.setZeroIsSignificant( false ); + dataElement.setDomainType( DOMAIN_TYPE_AGGREGATE ); + dataElement.setType( VALUE_TYPE_INT ); + dataElement.setNumberType( VALUE_TYPE_INT ); + dataElement.setAggregationOperator( AGGREGATION_OPERATOR_SUM ); + dataElement.setCategoryCombo( optionCombo.getCategoryCombo() ); + int deId = dataElementService.addDataElement( dataElement ); - // Add the dataelement into the dataset + idxMap.put( idx - 1, deId ); + + // Add the dataElement into the dataSet dataSet.addDataElement( dataElement ); - // Put text field into the cell(rowIndex,idx) - // htmlCode.append( str ); - - // Generate report item + // Generate Report Item ExportItem exportItem = new ExportItem(); exportItem.setName( name ); - exportItem.setItemType( "dataelement" ); - exportItem.setRow( rowIndex ); + exportItem.setItemType( DATAELEMENT ); + exportItem.setRow( rowIndex + 1 ); exportItem.setColumn( idx ); - exportItem.setExpression( "[" + deId + "." + catagoryOptionComboId + "]" ); - exportItem.setPeriodType( "selected_month" ); - exportItem.setSheetNo( (sheetNo) ); - exportItem.setExportReport( exportReportService.getExportReport( reportId ) ); + exportItem.setExpression( "[" + deId + "." + optionCombo.getId() + "]" ); + exportItem.setPeriodType( SELECTED_MONTH ); + exportItem.setSheetNo( sheetNo ); + exportItem.setExportReport( exportReport ); + exportReportService.addExportItem( exportItem ); - } } + + xml.append( "" ); + + if ( idxMap.containsKey( cell.getColumnIndex() ) ) + { + xml.append( "" + "]]>" ); + } + else if ( (cell.getCellStyle() != null || cell.getCellType() != Cell.CELL_TYPE_BLANK) + && !s.isColumnHidden( cell.getColumnIndex() ) ) + { + xml.append( "" ); + + this.readingDetailsFormattedCell( s, cell ); + + } + xml.append( "" ); } + xml.append( "" ); } - - DataEntryForm dataEntryForm = new DataEntryForm( "DataEntry form", htmlCode.toString() ); - dataEntryFormService.addDataEntryForm( dataEntryForm ); - - dataSet.setDataEntryForm( dataEntryForm ); - dataSetService.addDataSet( dataSet ); - - Set dataSets = new HashSet(); - dataSets.add( dataSet ); - - exportReport.setDataSets( dataSets ); - exportReportService.updateExportReport( exportReport ); - + xml.append( "" ); } catch ( Exception e ) { @@ -262,4 +347,92 @@ System.err.println( "Error: " + e.getMessage() ); } } + + // ------------------------------------------------------------------------- + // Get the merged cell's information + // ------------------------------------------------------------------------- + + private void readingDetailsFormattedCell( Sheet sheet, Cell objCell ) + { + // The format information + CellStyle format = objCell.getCellStyle(); + + if ( format != null ) + { + xml.append( "" ); + + xml.append( "" ); + } + else + { + xml.append( "/>" ); + } + } + } + + private void writeXMLMergedDescription( Collection collectSheets ) + throws IOException + { + // Open the main Tag // + xml.append( MERGEDCELL_OPENTAG ); + + for ( Integer sheet : collectSheets ) + { + writeMergedInfoBySheetNo( sheet ); + } + + // Close the main Tag // + xml.append( MERGEDCELL_CLOSETAG ); + } + + private void writeMergedInfoBySheetNo( int sheetNo ) + { + Sheet sheet = WORKBOOK.getSheetAt( sheetNo - 1 ); + CellRangeAddress cellRangeAddress = null; + + for ( int i = 0; i < sheet.getNumMergedRegions(); i++ ) + { + cellRangeAddress = sheet.getMergedRegion( i ); + + if ( cellRangeAddress.getFirstColumn() != cellRangeAddress.getLastColumn() ) + { + xml.append( "" + + (cellRangeAddress.getLastColumn() - cellRangeAddress.getFirstColumn() + 1) + "" ); + } + } + } + + private String getSimilarColor( short index ) + { + if ( IndexedColors.BLUE.getIndex() == index ) + { + return "blue"; + } + + if ( IndexedColors.DARK_BLUE.getIndex() == index ) + { + return "darkblue"; + } + + if ( IndexedColors.BROWN.getIndex() == index ) + { + return "brown"; + } + + return ""; + } } \ No newline at end of file === modified file 'local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/preview/action/XMLStructureResponse.java' --- local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/preview/action/XMLStructureResponse.java 2012-11-02 04:42:01 +0000 +++ local/vn/dhis-web-spreadsheet-reporting/src/main/java/org/hisp/dhis/reportsheet/preview/action/XMLStructureResponse.java 2012-12-11 20:24:41 +0000 @@ -225,6 +225,7 @@ // ------------------------------------------------------------------------- // Get the merged cell's information // ------------------------------------------------------------------------- + private void writeXMLMergedDescription( Collection collectSheets ) throws IOException {