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 dateUtilService def createDataService 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) // Turn off index mirroring. createDataService.stopSearchableIndex() def fail = { Map m -> status.setRollbackOnly() createDataService.startSearchableIndex() // Start mirroring again and rebuild index. 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"]) def line = [] def lineNumber = 0 def maxNumberOfColumns = 19 def inventoryParams = [:] def inventoryProperties = ["name", "description", "comment", "unitsInStock", "reorderPoint", "reorderQuantity", "unitOfMeasure", "estimatedUnitPriceAmount", "estimatedUnitPriceCurrency", "enableReorderListing", "inventoryLocation", "inventoryStore", "site", "inventoryGroup", "inventoryType", "suppliersPartNumber", "preferredSupplier", "alternateSuppliers", "spareFor"] def siteInstance def alternateSupplierInstance def preferredSupplierInstance def supplierTypeInstance def supplierTypeUnknown = SupplierType.get(1) def spareForInstance def inventoryTypeInstance def unitOfMeasureInstance def inventoryGroupInstance def inventoryItemInstance def inventoryStoreInstance def inventoryLocationInstance def tempPreferredSupplierItemAndType = '' def tempPreferredSupplierItem = '' def tempPreferredSupplierType = '' def tempAlternateSuppliers = [] def tempSupplierItem = '' def tempSupplierType = '' def tempSupplierItemAndType = [] def tempSpareFor = [] def nextLine = { line = reader.readNext() lineNumber ++ log.info "Processing line: " + lineNumber } 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]) } } // Preferred Supplier if(inventoryParams.preferredSupplier) { tempPreferredSupplierItemAndType = parseItemAndType(inventoryParams.preferredSupplier) tempPreferredSupplierItem = WordUtils.capitalize(tempPreferredSupplierItemAndType[0]) preferredSupplierInstance = Supplier.findByName(tempPreferredSupplierItem) if(!preferredSupplierInstance) { // SupplierType. if(tempPreferredSupplierItemAndType.size == 2) { tempPreferredSupplierType = WordUtils.capitalize(tempPreferredSupplierItemAndType[1]) supplierTypeInstance = SupplierType.findByName(tempPreferredSupplierType) } else supplierTypeInstance = supplierTypeUnknown if(!supplierTypeInstance) { log.error "Failed to find preferred supplier type on line: " + lineNumber return fail(code: "inventory.import.failure", args: [lineNumber, logFileLink]) } preferredSupplierInstance = new Supplier(name: tempPreferredSupplierItem, supplierType: supplierTypeInstance) if(!preferredSupplierInstance.save()) { log.error "Failed to create preferred supplier on line: " + lineNumber return fail(code: "inventory.import.failure", args: [lineNumber, logFileLink]) } } } else preferredSupplierInstance = null // Alternate Suppliers. tempAlternateSuppliers = parseInputList(inventoryParams.alternateSuppliers) inventoryParams.alternateSuppliers = [] for(tempSupplier in tempAlternateSuppliers) { tempSupplierItemAndType = parseItemAndType(tempSupplier) tempSupplierItem = WordUtils.capitalizeFully(tempSupplierItemAndType[0]) alternateSupplierInstance = Supplier.findByName(tempSupplierItem) if(!alternateSupplierInstance) { // SupplierType. if(tempSupplierItemAndType.size == 2) { tempSupplierType = WordUtils.capitalize(tempSupplierItemAndType[1]) supplierTypeInstance = SupplierType.findByName(tempSupplierType) } else supplierTypeInstance = supplierTypeUnknown if(!supplierTypeInstance) { log.error "Failed to find alternate supplier type on line: " + lineNumber return fail(code: "inventory.import.failure", args: [lineNumber, logFileLink]) } alternateSupplierInstance = new Supplier(name: tempSupplierItem, supplierType: supplierTypeInstance) if(!alternateSupplierInstance.save()) { log.error "Failed to create alternate suppliers on line: " + lineNumber return fail(code: "inventory.import.failure", args: [lineNumber, logFileLink]) } } inventoryParams.alternateSuppliers.add(alternateSupplierInstance) } // 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.preferredSupplier = preferredSupplierInstance // 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: " + 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." createDataService.startSearchableIndex() // Start mirroring again and rebuild index. 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 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", "123", "Multi Supplier@Local", "Multi Distributors1@OEM; Multi Distributors2@Local", "" ] 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", "456KL", "Multi Supplier", "Multi Distributors1; Multi Distributors2", "" ] 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.reorderQuantity) row.add(inventoryItem.unitOfMeasure) row.add(inventoryItem.estimatedUnitPriceAmount) row.add(inventoryItem.estimatedUnitPriceCurrency) row.add(inventoryItem.enableReorderListing) 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.suppliersPartNumber) if(inventoryItem.preferredSupplier) row.add( inventoryItem.preferredSupplier.name + "@" + inventoryItem.preferredSupplier.supplierType ) else row.add('') row.add( inventoryItem.alternateSuppliers.sort { p1, p2 -> p1.name.compareToIgnoreCase(p2.name) }.collect { it.name + "@" + it.supplierType }.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() /** * Import inventoryItemPurchases creating items as required. */ def importInventoryItemPurchases(request) { InventoryItemPurchase.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"]) def line = [] def lineNumber = 0 def maxNumberOfColumns = 10 def inventoryItemPurchaseParams = [:] def inventoryItemPurchaseProperties = ["inventoryItem", "purchaseOrderNumber", "quantity", "inventoryItemPurchaseType", "costCode", "enteredBy", "dateEntered", "orderValueAmount", "orderValueCurrency", "invoiceNumber"] def personInstance def costCodeInstance def inventoryItemInstance def inventoryItemPurchaseInstance def inventoryItemPurchaseTypeInstance def nextLine = { line = reader.readNext() lineNumber ++ log.info "Processing line: " + lineNumber } def parseInputDate = { if( (it == null) || (it.trim() == '') ) { log.error "Failed to find any date on line: " + lineNumber return fail(code: "inventoryItemPurchase.import.failure", args: [lineNumber, logFileLink]) } def d = it.split("/").collect{it.trim()} if(d.size() != 3) { log.error "Failed to find full date on line: " + lineNumber return fail(code: "inventoryItemPurchase.import.failure", args: [lineNumber, logFileLink]) } dateUtilService.makeDate(d[0], d[1], d[2]) } // Get first line. nextLine() // Check for header line 1. if(line != purchasesTemplateHeaderLine1) { log.error "Failed to find header line 1. " log.error "Required: " + purchasesTemplateHeaderLine1.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: "inventoryItemPurchase.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. inventoryItemPurchaseParams = [:] line.eachWithIndex { it, j -> inventoryItemPurchaseParams."${inventoryItemPurchaseProperties[j]}" = it.trim() } // Debug log.debug " Supplied params: " log.debug inventoryItemPurchaseParams // Ignore blank lines. if(inventoryItemPurchaseParams.inventoryItem == '') { log.info "No inventory item name found." nextLine() continue } // Inventory Item. inventoryItemPurchaseParams.inventoryItem = WordUtils.capitalize(inventoryItemPurchaseParams.inventoryItem) inventoryItemInstance = InventoryItem.findByName(inventoryItemPurchaseParams.inventoryItem) if(!inventoryItemInstance) { log.error "Inventory item not found on line: " + lineNumber return fail(code: "inventoryItemPurchase.import.failure", args: [lineNumber, logFileLink]) } inventoryItemPurchaseParams.inventoryItem = inventoryItemInstance // Quantity. if(inventoryItemPurchaseParams.quantity.isInteger()) inventoryItemPurchaseParams.quantity = inventoryItemPurchaseParams.quantity.toInteger() else { log.error "Quantity is not a valid number on line: " + lineNumber return fail(code: "inventoryItemPurchase.import.failure", args: [lineNumber, logFileLink]) } // InventoryItemPurchaseType. inventoryItemPurchaseParams.inventoryItemPurchaseType = WordUtils.capitalizeFully(inventoryItemPurchaseParams.inventoryItemPurchaseType) inventoryItemPurchaseTypeInstance = InventoryItemPurchaseType.findByName(inventoryItemPurchaseParams.inventoryItemPurchaseType) if(!inventoryItemPurchaseTypeInstance) { log.error "Inventory item purchase type not found on line: " + lineNumber log.debug inventoryItemPurchaseParams.inventoryItemPurchaseType return fail(code: "inventoryItemPurchase.import.failure", args: [lineNumber, logFileLink]) } inventoryItemPurchaseParams.inventoryItemPurchaseType = inventoryItemPurchaseTypeInstance // CostCode. if(inventoryItemPurchaseParams.costCode != '') { inventoryItemPurchaseParams.costCode = WordUtils.capitalizeFully(inventoryItemPurchaseParams.costCode) costCodeInstance = CostCode.findByName(inventoryItemPurchaseParams.costCode) if(!costCodeInstance) { costCodeInstance = new CostCode(name: inventoryItemPurchaseParams.costCode) if(!costCodeInstance.save()) { log.error "Failed to create cost code on line: " + lineNumber return fail(code: "inventoryItemPurchase.import.failure", args: [lineNumber, logFileLink]) } } inventoryItemPurchaseParams.costCode = costCodeInstance } // Entered By. inventoryItemPurchaseParams.enteredBy = inventoryItemPurchaseParams.enteredBy.toLowerCase() personInstance = Person.findByLoginName(inventoryItemPurchaseParams.enteredBy) if(!personInstance) { log.error "Entered by person not found on line: " + lineNumber return fail(code: "inventoryItemPurchase.import.failure", args: [lineNumber, logFileLink]) } inventoryItemPurchaseParams.enteredBy = personInstance // Date Entered. inventoryItemPurchaseParams.dateEntered = parseInputDate(inventoryItemPurchaseParams.dateEntered) // Debug log.debug "InventoryItemPurchaseParams: " log.debug inventoryItemPurchaseParams // Save inventoryItem. log.info "Creating new purchase." inventoryItemPurchaseInstance = new InventoryItemPurchase(inventoryItemPurchaseParams) if(inventoryItemPurchaseInstance.hasErrors() || !inventoryItemPurchaseInstance.save()) { log.error "Failed to create item on line: " + lineNumber log.debug inventoryItemPurchaseInstance.errors return fail(code: "inventoryItemPurchase.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 importInventoryItemPurchases() private getTemplateHeaderLine1() { ["Name*", "Description", "Comment", "Units In Stock", "Reorder Point*", "Reorder Quantity", "Unit Of Measure*", "Estimated Unit Price", "Currency", "Enable Reorder", "Location*", "Store*", "Site*", "Group*", "Type*", "Supplier's Part Number", "Preferred Supplier", "Alternate Suppliers", "Spare For"] } private getPurchasesTemplateHeaderLine1() { ["Inventory Item*", "Purchase Order Number*", "Quantity*", "Purchase Type*", "Cost Code*", "Entered By*", "Date Entered*", "Order Value", "Currency", "Invoice Number"] } /** * 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