import au.com.bytecode.opencsv.CSVWriter import au.com.bytecode.opencsv.CSVReader import org.apache.commons.lang.WordUtils /** * Provides some csv import/export methods. * Requires the opencsv jar to be available which is included in the grails-export plugin. */ class CsvService { boolean transactional = false /** * Import an asset tree creating items as required. * @param request The http request to run getFile against. * Get file should return a csv format file containing the asset tree as per template. */ def importAssetTree(request) { Asset.withTransaction { status -> def result = [:] def kByteMultiplier = 1000 def fileMaxSize = 500 * kByteMultiplier def multiPartFile = request.getFile('file') InputStreamReader sr = new InputStreamReader(multiPartFile.inputStream) CSVReader reader = new CSVReader(sr) def fail = { Map m -> status.setRollbackOnly() reader.close() result.error = [ code: m.code, args: m.args ] return result } if(!multiPartFile || multiPartFile.isEmpty()) return fail(code: "asset.tree.import.file.not.supplied") if (multiPartFile.getSize() > fileMaxSize) return fail(code: "asset.tree.import.file.over.max.size", args: [fileMaxSize/kByteMultiplier, "kB"]) def columnIndex = 0 def nameColumnIndex = 0 def numberOfColumns = 0 def maxNumberOfColumns = 20 // Get first line. def line = reader.readNext() def lineNumber = 1 // Check for header line 1. if(line != templateHeaderLine1) { log.error "Failed to find header line 1. " log.error "Required: " + templateHeaderLine1.toString() log.error "Supplied: " + line.toString() return fail(code: "asset.tree.import.no.header") } // Get second line. line = reader.readNext() lineNumber ++ // Check for header line 2. if(line != templateHeaderLine2) { log.error "Failed to find header line 2. " log.error "Required: " + templateHeaderLine2.toString() log.error "Supplied: " + line.toString() return fail(code: "asset.tree.import.no.header") } log.info "Import checks passed, start processing asset file." // Prepare the first body line. line = reader.readNext() lineNumber ++ def siteInstance def departmentInstance def sectionInstance def assetInstance def assetSubItemInstance def parentItem def column = [:] def nextLine = { line = reader.readNext() lineNumber ++ log.info "Processing line: " + lineNumber } def nextColumn = { nameColumnIndex = columnIndex if( (columnIndex+1) < numberOfColumns ) { // Capitalise and assign the name and description columns. use(WordUtils) { column.name = line[columnIndex].trim().capitalize() column.description = line[++columnIndex].trim().capitalize() } } else if( columnIndex < numberOfColumns ) { // Capitalise and assign the name column with a blank description. use(WordUtils) { column.name = line[columnIndex].trim().capitalize() column.description = '' } } else { log.info "No more columns on line: " + lineNumber return false } if(!column.name) { log.info "No name at " + "line: " + lineNumber + " col: " + nameColumnIndex return false } columnIndex++ // Success. return column } // Primary loop. while(line) { numberOfColumns = Math.min( line.size(), maxNumberOfColumns ) columnIndex = 0 if(!nextColumn()) { nextLine() continue } // Ignore comment lines. if(line.toString().toLowerCase().contains("comment")) { log.info "Comment line found." nextLine() continue } // Ignore example lines. if(line.toString().toLowerCase().contains("example")) { log.info "Example line found." nextLine() continue } // Site. siteInstance = Site.findByName(column.name) if(!siteInstance) { log.info "Creating site: " + column.name siteInstance = new Site(name: column.name, description: column.description) if(!siteInstance.save()) { log.error "Failed to create site on line: " + column.name + "(" + lineNumber + ")" return fail(code: "asset.tree.import.failure", args: [lineNumber]) } } else log.info "Existing site: " + siteInstance if(!nextColumn()) { nextLine() continue } // Department. departmentInstance = Department.findByName(column.name) if(!departmentInstance) { log.info "Creating department: " + column.name departmentInstance = new Department(name: column.name, description: column.description, site: siteInstance) if(!departmentInstance.save()) { log.error "Failed to create department on line: " + column.name + "(" + lineNumber + ")" return fail(code: "asset.tree.import.failure", args: [lineNumber]) } } else log.info "Existing department: " + departmentInstance if(!nextColumn()) { nextLine() continue } // Section. sectionInstance = Section.findByName(column.name) if(!sectionInstance) { log.info "Creating section: " + column.name sectionInstance = new Section(name: column.name, description: column.description, site: siteInstance, department: departmentInstance) if(!sectionInstance.save()) { log.error "Failed to create section on line: " + column.name + "(" + lineNumber + ")" return fail(code: "asset.tree.import.failure", args: [lineNumber]) } } else log.info "Existing section: " + sectionInstance if(!nextColumn()) { nextLine() continue } // Asset. assetInstance = Asset.findByName(column.name) if(!assetInstance) { log.info "Creating asset: " + column.name assetInstance = new Asset(name: column.name, description: column.description, section: sectionInstance) if(!assetInstance.save()) { log.error "Failed to create asset on line: " + column.name + "(" + lineNumber + ")" return fail(code: "asset.tree.import.failure", args: [lineNumber]) } } else log.info "Existing asset: " + assetInstance if(!nextColumn()) { nextLine() continue } // AssetSubItem Level 1. assetSubItemInstance = AssetSubItem.findByName(column.name) if(!assetSubItemInstance) { log.info "Creating asset sub item: " + column.name assetSubItemInstance = new AssetSubItem(name: column.name, description: column.description) if(!assetInstance.save()) { log.error "Failed to create assetSubItem on line: " + column.name + "(" + lineNumber + ")" return fail(code: "asset.tree.import.failure", args: [lineNumber]) } } else log.info "Existing asset sub item: " + assetSubItemInstance assetInstance.addToAssetSubItems(assetSubItemInstance) // Remaining AssetSubItems. while( nextColumn() ) { parentItem = assetSubItemInstance assetSubItemInstance = AssetSubItem.findByName(column.name) if(!assetSubItemInstance) { log.info "Creating asset sub item: " + column.name assetSubItemInstance = new AssetSubItem(name: column.name, description: column.description, parentItem: parentItem) if(!assetSubItemInstance.save()) { log.error "Failed to create assetSubItem on line: " + column.name + "(" + lineNumber + ")" return fail(code: "asset.tree.import.failure", args: [lineNumber]) } } else log.info "Existing asset sub item: " + assetSubItemInstance } // while( nextColumn() ) nextLine() } //while(line) // Success. log.info "End of file." reader.close() return result } //end withTransaction } // end importAssetTree() /** * Build an asset tree template csv file. * This template can then be populated for import. * @returns The template as a String in csv format. */ def buildAssetTreeTemplate() { StringWriter sw = new StringWriter() CSVWriter writer = new CSVWriter(sw) writeTemplateLines(writer) writer.close() return sw.toString() } private writeTemplateLines(writer) { writer.writeNext(templateHeaderLine1 as String[]) writer.writeNext(templateHeaderLine2 as String[]) writer.writeNext() writer.writeNext(["Example: Site1", "", "Department 1", "", "Section 1", "", "Asset 1", ""] as String[]) writer.writeNext() writer.writeNext(["Example: Site1", "", "Department 1", "", "Section 1", "", "Asset 2", ""] as String[]) writer.writeNext() writer.writeNext("Comment: The first two header lines are required.") writer.writeNext("Comment: Leave a blank line between assets.") writer.writeNext("Comment: Names are required, descriptions are optional.") writer.writeNext("Comment: Identical and existing names will be considered as the same item.") writer.writeNext("Comment: An asset may share the first level of asset sub items with other assets.") writer.writeNext("Comment: Lower levels of asset sub items are only parented once but may have many children.") writer.writeNext("Comment: Lines containing 'comment' will be ignored.") writer.writeNext("Comment: Lines containing 'example' will be ignored.") writer.writeNext("Comment: This file must be saved as a CSV file before import.") writer.writeNext() } /** * Build an asset tree test file. * This test file can be imported to test the import and template methods. * @returns The test file as a String in csv format. */ def buildAssetTreeTest() { StringWriter sw = new StringWriter() CSVWriter writer = new CSVWriter(sw) writeTemplateLines(writer) writer.writeNext(["Lake Press", "Lake Press Site", "Print Department", "The printing department", "Press Section", "Contains all printing units", "Print Unit 1", "Printing Unit Number 1", "Print Unit", "Print Unit (Common to all units)", "Print Couple", "Includes blanket cylinder", "Motor", "Main Drive Motor", "NDS Bearing", "Non Drive Side Main Bearing" ] as String[]) writer.writeNext(["Lake Press", "Lake Press Site", "Print Department", "The printing department", "Press Section", "Contains all printing units", "Print Unit 1", "Printing Unit Number 1", "Print Unit", "Print Unit (Common to all units)", "Print Couple", "Includes blanket cylinder", "Motor", "Main Drive Motor", "DS Bearing", "Drive Side Main Bearing" ] as String[]) writer.close() return sw.toString() } /** * Build complete asset trees for export. * @param assetList The list of assets to build and export trees for. * @returns The tree as a String in csv format. */ def buildAssetTree(List assetList) { StringWriter sw = new StringWriter() CSVWriter writer = new CSVWriter(sw) writeTemplateLines(writer) //Rows def row def writeAssetSubItem4 = { assetSubItem -> row.add(assetSubItem.name) row.add(assetSubItem.description) writer.writeNext(row as String[]) } def writeAssetSubItem3 = { assetSubItem -> row.add(assetSubItem.name) row.add(assetSubItem.description) if(assetSubItem.subItems.size() > 0) { assetSubItem.subItems.sort { p1, p2 -> p1.name.compareToIgnoreCase(p2.name) }.each() { assetSubItem4 -> writeAssetSubItem4(assetSubItem4) row.removeRange(row.size()-2, row.size()) } } else { writer.writeNext(row as String[]) } } def writeAssetSubItem2 = { assetSubItem -> row.add(assetSubItem.name) row.add(assetSubItem.description) if(assetSubItem.subItems.size() > 0) { assetSubItem.subItems.sort { p1, p2 -> p1.name.compareToIgnoreCase(p2.name) }.each() { assetSubItem3 -> writeAssetSubItem3(assetSubItem3) row.removeRange(row.size()-2, row.size()) } } else { writer.writeNext(row as String[]) } } def writeAssetSubItem1 = { assetSubItem -> row.add(assetSubItem.name) row.add(assetSubItem.description) if(assetSubItem.subItems.size() > 0) { assetSubItem.subItems.sort { p1, p2 -> p1.name.compareToIgnoreCase(p2.name) }.each() { assetSubItem2 -> writeAssetSubItem2(assetSubItem2) row.removeRange(row.size()-2, row.size()) } } else { writer.writeNext(row as String[]) } } assetList.sort { p1, p2 -> p1.name.compareToIgnoreCase(p2.name) }.each() { asset -> row = [] writer.writeNext(row as String[]) //blank row between assets. row.add(asset.section.site.name) row.add(asset.section.site.description) row.add(asset.section.department.name) row.add(asset.section.department.description) row.add(asset.section.name) row.add(asset.section.description) row.add(asset.name) row.add(asset.description) if(asset.assetSubItems.size() > 0) { asset.assetSubItems.sort { p1, p2 -> p1.name.compareToIgnoreCase(p2.name) }.each() { assetSubItem1 -> writeAssetSubItem1(assetSubItem1) row.removeRange(row.size()-2, row.size()) } } else { writer.writeNext(row as String[]) } } writer.close() return sw.toString() } // end buildAssetTree private getTemplateHeaderLine1() { ["Site", "", "Department", "", "Section", "","Asset", "", "Sub Asset", "", "Functional Assembly", "", "Sub Assembly Group", "", "SubItem", ""] } private getTemplateHeaderLine2() { (["Name", "Description"])*8 } } // end class