import grails.util.GrailsUtil 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 InventoryCsvService { boolean transactional = false def g = new org.codehaus.groovy.grails.plugins.web.taglib.ApplicationTagLib() def sessionFactory def propertyInstanceMap = org.codehaus.groovy.grails.plugins.DomainClassGrailsPlugin.PROPERTY_INSTANCE_MAP /** * Import inventory creating items as required. * @param request The http request to run getFile against. * Get file should return a csv format file containing the inventory as per template. */ def importInventory(request) { InventoryItem.withTransaction { status -> def result = [:] def kByteMultiplier = 1000 def fileMaxSize = 800 * kByteMultiplier def logFileLink = g.link(controller: "appCore", action: "appLog") {"log"} 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: "default.file.not.supplied") if (multiPartFile.getSize() > fileMaxSize) return fail(code: "default.file.over.max.size", args: [fileMaxSize/kByteMultiplier, "kB"]) //TODO: delete def columnValue = '' def columnIndex = 0 def numberOfColumns = 0 def line = [] def lineNumber = 0 def maxNumberOfColumns = 23 def inventoryParams = [:] def inventoryProperties = ["name", "description", "comment", "unitsInStock", "reorderPoint", "recommendedReorderPoint", "unitOfMeasure", "estimatedUnitPriceAmount", "estimatedUnitPriceCurrency", "enableReorder", "inventoryLocation", "inventoryStore", "site", "inventoryGroup", "inventoryType", "averageDeliveryTime", "averageDeliveryPeriod", "suppliersPartNumber", "suppliers", "manufacturersPartNumber", "manufacturers", "alternateItems", "spareFor"] def siteInstance def supplierInstance def supplierTypeInstance def supplierTypeUnknown = SupplierType.get(1) def spareForInstance def alternateItemInstance def manufacturerInstance def manufacturerTypeInstance def manufacturerTypeUnknown = ManufacturerType.get(1) def inventoryTypeInstance def unitOfMeasureInstance def inventoryGroupInstance def inventoryItemInstance def inventoryStoreInstance def inventoryLocationInstance def averageDeliveryPeriodInstance def tempSuppliers = [] def tempSupplierItem = '' def tempSupplierType = '' def tempSupplierItemAndType = [] def tempManufacturers = [] def tempManufacturerItem = '' def tempManufacturerType = '' def tempManufacturerItemAndType = [] def tempSpareFor = [] def tempAlternateItems = [] def column = '' def nextLine = { line = reader.readNext() lineNumber ++ log.info "Processing line: " + lineNumber } def nextColumn = { if( columnIndex < numberOfColumns ) { column = line[columnIndex].trim() } else { log.info "No more columns on line: " + lineNumber return false } columnIndex++ // Success. return column } def parseInputList = { if( (it == null) || (it.trim() == '') ) return [] return it.split(";").collect{it.trim()} } def parseItemAndType = { return it.split("@").collect{it.trim()} } // Get first line. nextLine() // 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: "default.file.no.header") } log.info "Header line found." // Prepare the first body line. nextLine() // Primary loop. while(line) { if(line.size() > maxNumberOfColumns) { log.error "Too many columns on line: " + lineNumber return fail(code: "inventory.import.failure", args: [lineNumber, logFileLink]) } // 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 } // Parse the line into the params map. inventoryParams = [:] line.eachWithIndex { it, j -> inventoryParams."${inventoryProperties[j]}" = it.trim() } // Debug log.debug " Supplied params: " log.debug inventoryParams // Ignore blank lines. if(inventoryParams.name == '') { log.info "No name found." nextLine() continue } /** Prepare the params and create supporting items as required. */ // Site inventoryParams.site = WordUtils.capitalize(inventoryParams.site) siteInstance = Site.findByName(inventoryParams.site) if(!siteInstance) { siteInstance = new Site(name: inventoryParams.site) if(!siteInstance.save()) { log.error "Failed to create site on line: " + lineNumber return fail(code: "inventory.import.failure", args: [lineNumber, logFileLink]) } } // InventoryStore inventoryParams.inventoryStore = WordUtils.capitalizeFully(inventoryParams.inventoryStore) inventoryStoreInstance = InventoryStore.findByName(inventoryParams.inventoryStore) if(!inventoryStoreInstance) { inventoryStoreInstance = new InventoryStore(name: inventoryParams.inventoryStore, site: siteInstance) if(!inventoryStoreInstance.save()) { log.error "Failed to create inventory store on line: " + lineNumber return fail(code: "inventory.import.failure", args: [lineNumber, logFileLink]) } } // InventoryLocation inventoryParams.inventoryLocation = WordUtils.capitalize(inventoryParams.inventoryLocation) inventoryLocationInstance = InventoryLocation.findByName(inventoryParams.inventoryLocation) if(!inventoryLocationInstance) { inventoryLocationInstance = new InventoryLocation(name: inventoryParams.inventoryLocation, inventoryStore: inventoryStoreInstance) if(!inventoryLocationInstance.save()) { log.error "Failed to create inventory location on line: " + lineNumber return fail(code: "inventory.import.failure", args: [lineNumber, logFileLink]) } } // InventoryGroup inventoryParams.inventoryLocation = WordUtils.capitalizeFully(inventoryParams.inventoryLocation) inventoryGroupInstance = InventoryGroup.findByName(inventoryParams.inventoryGroup) if(!inventoryGroupInstance) { inventoryGroupInstance = new InventoryGroup(name: inventoryParams.inventoryGroup) if(!inventoryGroupInstance.save()) { log.error "Failed to create inventory group on line: " + lineNumber return fail(code: "inventory.import.failure", args: [lineNumber, logFileLink]) } } // InventoryType inventoryParams.inventoryType = WordUtils.capitalizeFully(inventoryParams.inventoryType) inventoryTypeInstance = InventoryType.findByName(inventoryParams.inventoryType) if(!inventoryTypeInstance) { inventoryTypeInstance = new InventoryType(name: inventoryParams.inventoryType) if(!inventoryTypeInstance.save()) { log.error "Failed to create inventory type on line: " + lineNumber return fail(code: "inventory.import.failure", args: [lineNumber, logFileLink]) } } // UnitOfMeasure. unitOfMeasureInstance = UnitOfMeasure.findByName(inventoryParams.unitOfMeasure) if(!unitOfMeasureInstance) { unitOfMeasureInstance = new UnitOfMeasure(name: inventoryParams.unitOfMeasure) if(!unitOfMeasureInstance.save()) { log.error "Failed to create unit of measure on line: " + lineNumber return fail(code: "inventory.import.failure", args: [lineNumber, logFileLink]) } } // AverageDeliveryPeriod. if(inventoryParams.averageDeliveryPeriod) { averageDeliveryPeriodInstance = Period.findByPeriod(inventoryParams.averageDeliveryPeriod) if(!averageDeliveryPeriodInstance) { log.error "Failed, not a valid delivery period on line: " + lineNumber return fail(code: "inventory.import.failure", args: [lineNumber, logFileLink]) } } // Manufacturers. tempManufacturers = parseInputList(inventoryParams.manufacturers) inventoryParams.manufacturers = [] for(tempManufacturer in tempManufacturers) { tempManufacturerItemAndType = parseItemAndType(tempManufacturer) tempManufacturerItem = WordUtils.capitalizeFully(tempManufacturerItemAndType[0]) manufacturerInstance = Manufacturer.findByName(tempManufacturerItem) if(!manufacturerInstance) { // ManufacturerType. if(tempManufacturerItemAndType.size == 2) { tempManufacturerType = WordUtils.capitalizeFully(tempManufacturerItemAndType[1]) manufacturerTypeInstance = ManufacturerType.findByName(tempManufacturerType) } else manufacturerTypeInstance = manufacturerTypeUnknown if(!manufacturerTypeInstance) { log.error "Failed to find manufacturer type on line: " + lineNumber return fail(code: "inventory.import.failure", args: [lineNumber, logFileLink]) } manufacturerInstance = new Manufacturer(name: tempManufacturerItem, manufacturerType: manufacturerTypeInstance) if(!manufacturerInstance.save()) { log.error "Failed to create manufacturers on line: " + lineNumber return fail(code: "inventory.import.failure", args: [lineNumber, logFileLink]) } } inventoryParams.manufacturers.add(manufacturerInstance) } // Suppliers. tempSuppliers = parseInputList(inventoryParams.suppliers) inventoryParams.suppliers = [] for(tempSupplier in tempSuppliers) { tempSupplierItemAndType = parseItemAndType(tempSupplier) tempSupplierItem = WordUtils.capitalizeFully(tempSupplierItemAndType[0]) supplierInstance = Supplier.findByName(tempSupplierItem) if(!supplierInstance) { // SupplierType. if(tempSupplierItemAndType.size == 2) { tempSupplierType = WordUtils.capitalizeFully(tempSupplierItemAndType[1]) supplierTypeInstance = SupplierType.findByName(tempSupplierType) } else supplierTypeInstance = supplierTypeUnknown if(!supplierTypeInstance) { log.error "Failed to find supplier type on line: " + lineNumber return fail(code: "inventory.import.failure", args: [lineNumber, logFileLink]) } supplierInstance = new Supplier(name: tempSupplierItem, supplierType: supplierTypeInstance) if(!supplierInstance.save()) { log.error "Failed to create suppliers on line: " + lineNumber return fail(code: "inventory.import.failure", args: [lineNumber, logFileLink]) } } inventoryParams.suppliers.add(supplierInstance) } // AlternateItems. tempAlternateItems = parseInputList(inventoryParams.alternateItems) inventoryParams.alternateItems = [] for(tempAlternateItem in tempAlternateItems) { tempAlternateItem = WordUtils.capitalize(tempAlternateItem) alternateItemInstance = InventoryItem.findByName(tempAlternateItem) if(!alternateItemInstance) { alternateItemInstance = new InventoryItem(name: tempAlternateItem, description: "Generated from alternateItems during import, details may not be correct.", reorderPoint: 0, inventoryGroup: inventoryGroupInstance, inventoryType: inventoryTypeInstance, unitOfMeasure: unitOfMeasureInstance, inventoryLocation: inventoryLocationInstance) if(!alternateItemInstance.save()) { log.error "Failed to create alternateItems on line: " + lineNumber return fail(code: "inventory.import.failure", args: [lineNumber, logFileLink]) } } inventoryParams.alternateItems.add(alternateItemInstance) } // spareFor. tempSpareFor = parseInputList(inventoryParams.spareFor) inventoryParams.spareFor = [] for(asset in tempSpareFor) { asset = WordUtils.capitalize(asset) spareForInstance = Asset.findByName(asset) if(!spareForInstance) { log.error "Failed to find 'Spare For' asset on line: " + lineNumber return fail(code: "inventory.import.failure", args: [lineNumber, logFileLink]) } inventoryParams.spareFor.add(spareForInstance) } // Assign the retrieved or created instances to params. inventoryParams.inventoryLocation = inventoryLocationInstance inventoryParams.inventoryGroup = inventoryGroupInstance inventoryParams.inventoryType = inventoryTypeInstance inventoryParams.unitOfMeasure = unitOfMeasureInstance inventoryParams.averageDeliveryPeriod = averageDeliveryPeriodInstance // Name. // Checked above for blank string. inventoryParams.name = WordUtils.capitalize(inventoryParams.name) // Description. if(inventoryParams.description != '') inventoryParams.description = inventoryParams.description[0].toUpperCase() + inventoryParams.description[1..-1] // Debug log.debug "InventoryParams: " log.debug inventoryParams // Create new or update. inventoryItemInstance = InventoryItem.findByName(inventoryParams.name) if(inventoryItemInstance) { log.info "Updating existing item: " + inventoryItemInstance inventoryItemInstance.properties = inventoryParams } else { log.info "Creating new item: " + inventoryParams.name inventoryItemInstance = new InventoryItem(inventoryParams) } // Save inventoryItem. if(inventoryItemInstance.hasErrors() || !inventoryItemInstance.save()) { log.error "Failed to create item on line: " + column + "(" + lineNumber + ")" log.debug inventoryItemInstance.errors return fail(code: "inventory.import.failure", args: [lineNumber, logFileLink]) } if(lineNumber % 100 == 0) cleanUpGorm() if(!result.error) nextLine() } //while(line) // Success. log.info "End of file." reader.close() return result } //end withTransaction } // end importInventory() /** * Build an inventory template csv file. * This template can then be populated for import. * @returns The template as a String in csv format. */ def buildInventoryTemplate() { 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() writer.writeNext("Comment: The header line is required.") writer.writeNext("Comment: Required columns are marked with a (*) in the header line.") writer.writeNext("Comment: Lists of items in a column must be separated by a semicolon (;), not a comma.") writer.writeNext("Comment: The at symbol (@) is reserved for indicating supplier and manufacturer types.") writer.writeNext("Comment: Identical and existing names will be considered as the same item.") 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 inventory example/test file. * This test file can be imported to test the import and export methods. * @returns The test file as a String in csv format. */ def buildInventoryExample() { StringWriter sw = new StringWriter() CSVWriter writer = new CSVWriter(sw) writeTemplateLines(writer) // Requires creation of some of the base/group/type data. writer.writeNext(["Split19", "19mm split pin", "Very usefull item.", "1024", "0", "1", "each", "5", "NZD", "false", "BR4", "Store #99", "Inventory Depot", "Mechanical Stock", "Consumable", "7", "Week(s)", "123", "Multi Distributors1@OEM; Multi Distributors2@Local", "321", "Mega Manufacturer1@OEM;Mega Manufacturer2@Alternate", "2204E-2RS", "" ] as String[]) // Using existing base data. writer.writeNext(["2204E-2RS", "Double Row Self Align Ball Bearing 2204E-2RS - Sealed - 20/47x18", "", "4", "1", "9", "each", "16.35", "USD", "TRUE", "BR4", "Store #99", "Inventory Depot", "Mechanical Stock", "Consumable", "2", "Month(s)", "456KL", "Multi Distributors1; Multi Distributors2", "654OP", "Mega Manufacturer1;Mega Manufacturer2", "", "" ] as String[]) writer.close() return sw.toString() } /** * Build complete inventory for export. * @param inventoryItemList The list of inventory items to build.. * @returns The inventory as a String in csv format. */ def buildInventory(List inventoryItemList) { def sw = new StringWriter() def writer = new CSVWriter(sw) writeTemplateLines(writer) //Rows def row inventoryItemList.sort { p1, p2 -> p1.name.compareToIgnoreCase(p2.name) }.each() { inventoryItem -> row = [] row.add(inventoryItem.name) row.add(inventoryItem.description) row.add(inventoryItem.comment) row.add(inventoryItem.unitsInStock) row.add(inventoryItem.reorderPoint) row.add(inventoryItem.recommendedReorderPoint) row.add(inventoryItem.unitOfMeasure) row.add(inventoryItem.estimatedUnitPriceAmount) row.add(inventoryItem.estimatedUnitPriceCurrency) row.add(inventoryItem.enableReorder) row.add(inventoryItem.inventoryLocation) row.add(inventoryItem.inventoryLocation.inventoryStore) row.add(inventoryItem.inventoryLocation.inventoryStore.site) row.add(inventoryItem.inventoryGroup) row.add(inventoryItem.inventoryType) row.add(inventoryItem.averageDeliveryTime) row.add(inventoryItem.averageDeliveryPeriod) row.add(inventoryItem.suppliersPartNumber) row.add( inventoryItem.suppliers.sort { p1, p2 -> p1.name.compareToIgnoreCase(p2.name) }.collect { it.name + "@" + it.supplierType }.join(';') ) row.add(inventoryItem.manufacturersPartNumber) row.add(inventoryItem.manufacturers.sort { p1, p2 -> p1.name.compareToIgnoreCase(p2.name) }.collect { it.name + "@" + it.manufacturerType }.join(';')) row.add(inventoryItem.alternateItems.sort { p1, p2 -> p1.name.compareToIgnoreCase(p2.name) }.collect { it.name }.join(';') ) row.add(inventoryItem.spareFor.sort { p1, p2 -> p1.name.compareToIgnoreCase(p2.name) }.collect { it.name }.join(';')) writer.writeNext(row as String[]) } writer.close() return sw.toString() } // end buildInventory private getTemplateHeaderLine1() { ["Name*", "Description", "Comment", "Units In Stock", "Reorder Point*", "Recommended Reorder Point", "Unit Of Measure*", "Estimated Unit Price", "Currency", "Enable Reorder", "Location*", "Store*", "Site*", "Group*", "Type*", "averageDeliveryTime", "averageDeliveryPeriod", "Supplier's Part Number", "Supplier", "Manufacturer's Part Number", "Manufacturer", "Alternate Item", "Spare For"] } /** * This cleans up the hibernate session and a grails map. * For more info see: http://naleid.com/blog/2009/10/01/batch-import-performance-with-grails-and-mysql/ * The hibernate session flush is normal for hibernate. * The map is apparently used by grails for domain object validation errors. * A starting point for clean up is every 100 objects. */ def cleanUpGorm() { def session = sessionFactory.currentSession session.flush() session.clear() propertyInstanceMap.get().clear() } } // end class