Context Navigation

source: trunk/grails-app/services/InventoryCsvService.groovy @ 430

Last change on this file since 430 was 427, checked in by gav, 15 years ago
Small improvements to InventoryCsvService, protect against array out of bounds.
File size: 26.0 KB

Rev	Line
[423]	1	import grails.util.GrailsUtil
	2	import au.com.bytecode.opencsv.CSVWriter
	3	import au.com.bytecode.opencsv.CSVReader
	4	import org.apache.commons.lang.WordUtils
	5
	6	/**
	7	* Provides some csv import/export methods.
	8	* Requires the opencsv jar to be available which is included in the grails-export plugin.
	9	*/
	10	class InventoryCsvService {
	11
	12	boolean transactional = false
	13
	14	def g = new org.codehaus.groovy.grails.plugins.web.taglib.ApplicationTagLib()
	15
	16	def sessionFactory
	17	def propertyInstanceMap = org.codehaus.groovy.grails.plugins.DomainClassGrailsPlugin.PROPERTY_INSTANCE_MAP
	18
	19	/**
	20	* Import inventory creating items as required.
	21	* @param request The http request to run getFile against.
	22	* Get file should return a csv format file containing the inventory as per template.
	23	*/
	24	def importInventory(request) {
	25	InventoryItem.withTransaction { status ->
	26	def result = [:]
	27
	28	def kByteMultiplier = 1000
[426]	29	def fileMaxSize = 800 * kByteMultiplier
[423]	30	def logFileLink = g.link(controller: "appCore", action: "appLog") {"log"}
	31
	32	def multiPartFile = request.getFile('file')
	33
	34	InputStreamReader sr = new InputStreamReader(multiPartFile.inputStream)
	35	CSVReader reader = new CSVReader(sr)
	36
	37	def fail = { Map m ->
	38	status.setRollbackOnly()
	39	reader.close()
	40	result.error = [ code: m.code, args: m.args ]
	41	return result
	42	}
	43
	44	if(!multiPartFile \|\| multiPartFile.isEmpty())
	45	return fail(code: "default.file.not.supplied")
	46
	47	if (multiPartFile.getSize() > fileMaxSize)
	48	return fail(code: "default.file.over.max.size", args: [fileMaxSize/kByteMultiplier, "kB"])
	49
	50	//TODO: delete
	51	def columnValue = ''
	52	def columnIndex = 0
	53	def numberOfColumns = 0
	54
	55	def line = []
	56	def lineNumber = 0
	57	def maxNumberOfColumns = 23
	58	def inventoryParams = [:]
	59	def inventoryProperties = ["name", "description", "comment", "unitsInStock", "reorderPoint", "recommendedReorderPoint",
	60	"unitOfMeasure", "estimatedUnitPriceAmount", "estimatedUnitPriceCurrency",
	61	"enableReorder", "inventoryLocation", "inventoryStore", "site",
	62	"inventoryGroup", "inventoryType", "averageDeliveryTime", "averageDeliveryPeriod",
	63	"suppliersPartNumber", "suppliers",
	64	"manufacturersPartNumber", "manufacturers", "alternateItems", "spareFor"]
	65
	66	def siteInstance
	67	def supplierInstance
	68	def supplierTypeInstance
	69	def supplierTypeUnknown = SupplierType.get(1)
	70	def spareForInstance
	71	def alternateItemInstance
	72	def manufacturerInstance
	73	def manufacturerTypeInstance
	74	def manufacturerTypeUnknown = ManufacturerType.get(1)
	75	def inventoryTypeInstance
	76	def unitOfMeasureInstance
	77	def inventoryGroupInstance
	78	def inventoryItemInstance
	79	def inventoryStoreInstance
	80	def inventoryLocationInstance
	81	def averageDeliveryPeriodInstance
	82
	83	def tempSuppliers = []
[426]	84	def tempSupplierItem = ''
	85	def tempSupplierType = ''
[423]	86	def tempSupplierItemAndType = []
	87	def tempManufacturers = []
[426]	88	def tempManufacturerItem = ''
	89	def tempManufacturerType = ''
[423]	90	def tempManufacturerItemAndType = []
	91
	92	def tempSpareFor = []
	93	def tempAlternateItems = []
	94
	95	def column = ''
	96
	97	def nextLine = {
	98	line = reader.readNext()
	99	lineNumber ++
	100	log.info "Processing line: " + lineNumber
	101	}
	102
	103	def nextColumn = {
	104
	105	if( columnIndex < numberOfColumns ) {
	106	column = line[columnIndex].trim()
	107	}
	108	else {
	109	log.info "No more columns on line: " + lineNumber
	110	return false
	111	}
	112
	113	columnIndex++
	114	// Success.
	115	return column
	116	}
	117
	118	def parseInputList = {
[426]	119	if( (it == null) \|\| (it.trim() == '') ) return []
[423]	120	return it.split(";").collect{it.trim()}
	121	}
	122
	123	def parseItemAndType = {
	124	return it.split("@").collect{it.trim()}
	125	}
	126
	127	// Get first line.
	128	nextLine()
	129
	130	// Check for header line 1.
	131	if(line != templateHeaderLine1) {
	132	log.error "Failed to find header line 1. "
	133	log.error "Required: " + templateHeaderLine1.toString()
	134	log.error "Supplied: " + line.toString()
	135	return fail(code: "default.file.no.header")
	136	}
	137
	138	log.info "Header line found."
	139
	140	// Prepare the first body line.
	141	nextLine()
	142
	143	// Primary loop.
	144	while(line) {
	145
	146	if(line.size() > maxNumberOfColumns) {
	147	log.error "Too many columns on line: " + lineNumber
	148	return fail(code: "inventory.import.failure", args: [lineNumber, logFileLink])
	149	}
	150
	151	// Ignore comment lines.
	152	if(line.toString().toLowerCase().contains("comment")) {
	153	log.info "Comment line found."
	154	nextLine()
	155	continue
	156	}
	157
	158	// Ignore example lines.
	159	if(line.toString().toLowerCase().contains("example")) {
	160	log.info "Example line found."
	161	nextLine()
	162	continue
	163	}
	164
	165	// Parse the line into the params map.
	166	inventoryParams = [:]
	167	line.eachWithIndex { it, j ->
	168	inventoryParams."${inventoryProperties[j]}" = it.trim()
	169	}
	170
	171	// Debug
	172	log.debug " Supplied params: "
	173	log.debug inventoryParams
	174
	175	// Ignore blank lines.
	176	if(inventoryParams.name == '') {
	177	log.info "No name found."
	178	nextLine()
	179	continue
	180	}
	181
	182	/** Prepare the params and create supporting items as required. */
	183
	184	// Site
[426]	185	inventoryParams.site = WordUtils.capitalize(inventoryParams.site)
[423]	186	siteInstance = Site.findByName(inventoryParams.site)
	187	if(!siteInstance) {
	188	siteInstance = new Site(name: inventoryParams.site)
	189	if(!siteInstance.save()) {
	190	log.error "Failed to create site on line: " + lineNumber
	191	return fail(code: "inventory.import.failure", args: [lineNumber, logFileLink])
	192	}
	193	}
	194
	195	// InventoryStore
[426]	196	inventoryParams.inventoryStore = WordUtils.capitalizeFully(inventoryParams.inventoryStore)
[423]	197	inventoryStoreInstance = InventoryStore.findByName(inventoryParams.inventoryStore)
	198	if(!inventoryStoreInstance) {
	199	inventoryStoreInstance = new InventoryStore(name: inventoryParams.inventoryStore,
	200	site: siteInstance)
	201	if(!inventoryStoreInstance.save()) {
	202	log.error "Failed to create inventory store on line: " + lineNumber
	203	return fail(code: "inventory.import.failure", args: [lineNumber, logFileLink])
	204	}
	205	}
	206
	207	// InventoryLocation
[426]	208	inventoryParams.inventoryLocation = WordUtils.capitalize(inventoryParams.inventoryLocation)
[423]	209	inventoryLocationInstance = InventoryLocation.findByName(inventoryParams.inventoryLocation)
	210	if(!inventoryLocationInstance) {
	211	inventoryLocationInstance = new InventoryLocation(name: inventoryParams.inventoryLocation,
	212	inventoryStore: inventoryStoreInstance)
	213	if(!inventoryLocationInstance.save()) {
	214	log.error "Failed to create inventory location on line: " + lineNumber
	215	return fail(code: "inventory.import.failure", args: [lineNumber, logFileLink])
	216	}
	217	}
	218
	219	// InventoryGroup
[426]	220	inventoryParams.inventoryLocation = WordUtils.capitalizeFully(inventoryParams.inventoryLocation)
[423]	221	inventoryGroupInstance = InventoryGroup.findByName(inventoryParams.inventoryGroup)
	222	if(!inventoryGroupInstance) {
	223	inventoryGroupInstance = new InventoryGroup(name: inventoryParams.inventoryGroup)
	224	if(!inventoryGroupInstance.save()) {
	225	log.error "Failed to create inventory group on line: " + lineNumber
	226	return fail(code: "inventory.import.failure", args: [lineNumber, logFileLink])
	227	}
	228	}
	229
	230	// InventoryType
[426]	231	inventoryParams.inventoryType = WordUtils.capitalizeFully(inventoryParams.inventoryType)
[423]	232	inventoryTypeInstance = InventoryType.findByName(inventoryParams.inventoryType)
	233	if(!inventoryTypeInstance) {
	234	inventoryTypeInstance = new InventoryType(name: inventoryParams.inventoryType)
	235	if(!inventoryTypeInstance.save()) {
	236	log.error "Failed to create inventory type on line: " + lineNumber
	237	return fail(code: "inventory.import.failure", args: [lineNumber, logFileLink])
	238	}
	239	}
	240
	241	// UnitOfMeasure.
	242	unitOfMeasureInstance = UnitOfMeasure.findByName(inventoryParams.unitOfMeasure)
	243	if(!unitOfMeasureInstance) {
	244	unitOfMeasureInstance = new UnitOfMeasure(name: inventoryParams.unitOfMeasure)
	245	if(!unitOfMeasureInstance.save()) {
	246	log.error "Failed to create unit of measure on line: " + lineNumber
	247	return fail(code: "inventory.import.failure", args: [lineNumber, logFileLink])
	248	}
	249	}
	250
	251	// AverageDeliveryPeriod.
	252	if(inventoryParams.averageDeliveryPeriod) {
	253	averageDeliveryPeriodInstance = Period.findByPeriod(inventoryParams.averageDeliveryPeriod)
	254	if(!averageDeliveryPeriodInstance) {
	255	log.error "Failed, not a valid delivery period on line: " + lineNumber
	256	return fail(code: "inventory.import.failure", args: [lineNumber, logFileLink])
	257	}
	258	}
	259
	260	// Manufacturers.
	261	tempManufacturers = parseInputList(inventoryParams.manufacturers)
	262	inventoryParams.manufacturers = []
	263
	264	for(tempManufacturer in tempManufacturers) {
	265	tempManufacturerItemAndType = parseItemAndType(tempManufacturer)
[426]	266	tempManufacturerItem = WordUtils.capitalizeFully(tempManufacturerItemAndType[0])
[423]	267
[426]	268	manufacturerInstance = Manufacturer.findByName(tempManufacturerItem)
[423]	269	if(!manufacturerInstance) {
	270
	271	// ManufacturerType.
[426]	272	if(tempManufacturerItemAndType.size == 2) {
	273	tempManufacturerType = WordUtils.capitalizeFully(tempManufacturerItemAndType[1])
	274	manufacturerTypeInstance = ManufacturerType.findByName(tempManufacturerType)
	275	}
[423]	276	else
	277	manufacturerTypeInstance = manufacturerTypeUnknown
	278	if(!manufacturerTypeInstance) {
	279	log.error "Failed to find manufacturer type on line: " + lineNumber
	280	return fail(code: "inventory.import.failure", args: [lineNumber, logFileLink])
	281	}
	282
[426]	283	manufacturerInstance = new Manufacturer(name: tempManufacturerItem,
[423]	284	manufacturerType: manufacturerTypeInstance)
	285	if(!manufacturerInstance.save()) {
	286	log.error "Failed to create manufacturers on line: " + lineNumber
	287	return fail(code: "inventory.import.failure", args: [lineNumber, logFileLink])
	288	}
	289	}
	290
	291	inventoryParams.manufacturers.add(manufacturerInstance)
	292	}
	293
	294	// Suppliers.
	295	tempSuppliers = parseInputList(inventoryParams.suppliers)
	296	inventoryParams.suppliers = []
	297
	298	for(tempSupplier in tempSuppliers) {
	299	tempSupplierItemAndType = parseItemAndType(tempSupplier)
[426]	300	tempSupplierItem = WordUtils.capitalizeFully(tempSupplierItemAndType[0])
[423]	301
[426]	302	supplierInstance = Supplier.findByName(tempSupplierItem)
[423]	303	if(!supplierInstance) {
	304
	305	// SupplierType.
[426]	306	if(tempSupplierItemAndType.size == 2) {
	307	tempSupplierType = WordUtils.capitalizeFully(tempSupplierItemAndType[1])
	308	supplierTypeInstance = SupplierType.findByName(tempSupplierType)
	309	}
[423]	310	else
	311	supplierTypeInstance = supplierTypeUnknown
	312	if(!supplierTypeInstance) {
	313	log.error "Failed to find supplier type on line: " + lineNumber
	314	return fail(code: "inventory.import.failure", args: [lineNumber, logFileLink])
	315	}
	316
[426]	317	supplierInstance = new Supplier(name: tempSupplierItem,
[423]	318	supplierType: supplierTypeInstance)
	319	if(!supplierInstance.save()) {
	320	log.error "Failed to create suppliers on line: " + lineNumber
	321	return fail(code: "inventory.import.failure", args: [lineNumber, logFileLink])
	322	}
	323	}
	324
	325	inventoryParams.suppliers.add(supplierInstance)
	326	}
	327
	328	// AlternateItems.
	329	tempAlternateItems = parseInputList(inventoryParams.alternateItems)
	330	inventoryParams.alternateItems = []
	331
	332	for(tempAlternateItem in tempAlternateItems) {
[426]	333	tempAlternateItem = WordUtils.capitalize(tempAlternateItem)
[423]	334	alternateItemInstance = InventoryItem.findByName(tempAlternateItem)
	335	if(!alternateItemInstance) {
	336	alternateItemInstance = new InventoryItem(name: tempAlternateItem,
	337	description: "Generated from alternateItems during import, details may not be correct.",
	338	reorderPoint: 0,
	339	inventoryGroup: inventoryGroupInstance,
	340	inventoryType: inventoryTypeInstance,
	341	unitOfMeasure: unitOfMeasureInstance,
	342	inventoryLocation: inventoryLocationInstance)
	343	if(!alternateItemInstance.save()) {
	344	log.error "Failed to create alternateItems on line: " + lineNumber
	345	return fail(code: "inventory.import.failure", args: [lineNumber, logFileLink])
	346	}
	347	}
	348
	349	inventoryParams.alternateItems.add(alternateItemInstance)
	350	}
	351
	352	// spareFor.
	353	tempSpareFor = parseInputList(inventoryParams.spareFor)
	354	inventoryParams.spareFor = []
	355
	356	for(asset in tempSpareFor) {
	357
[426]	358	asset = WordUtils.capitalize(asset)
[423]	359
	360	spareForInstance = Asset.findByName(asset)
	361	if(!spareForInstance) {
	362	log.error "Failed to find 'Spare For' asset on line: " + lineNumber
	363	return fail(code: "inventory.import.failure", args: [lineNumber, logFileLink])
	364	}
	365
	366	inventoryParams.spareFor.add(spareForInstance)
	367	}
	368
	369	// Assign the retrieved or created instances to params.
	370	inventoryParams.inventoryLocation = inventoryLocationInstance
	371	inventoryParams.inventoryGroup = inventoryGroupInstance
	372	inventoryParams.inventoryType = inventoryTypeInstance
	373	inventoryParams.unitOfMeasure = unitOfMeasureInstance
	374	inventoryParams.averageDeliveryPeriod = averageDeliveryPeriodInstance
	375
[427]	376	// Name.
	377	// Checked above for blank string.
[426]	378	inventoryParams.name = WordUtils.capitalize(inventoryParams.name)
	379
[427]	380	// Description.
	381	if(inventoryParams.description != '')
	382	inventoryParams.description = inventoryParams.description[0].toUpperCase() + inventoryParams.description[1..-1]
	383
[423]	384	// Debug
	385	log.debug "InventoryParams: "
	386	log.debug inventoryParams
	387
	388	// Create new or update.
	389	inventoryItemInstance = InventoryItem.findByName(inventoryParams.name)
	390	if(inventoryItemInstance) {
	391	log.info "Updating existing item: " + inventoryItemInstance
	392	inventoryItemInstance.properties = inventoryParams
	393	}
	394	else {
	395	log.info "Creating new item: " + inventoryParams.name
	396	inventoryItemInstance = new InventoryItem(inventoryParams)
	397	}
	398
	399	// Save inventoryItem.
	400	if(inventoryItemInstance.hasErrors() \|\| !inventoryItemInstance.save()) {
	401	log.error "Failed to create item on line: " + column + "(" + lineNumber + ")"
	402	log.debug inventoryItemInstance.errors
	403	return fail(code: "inventory.import.failure", args: [lineNumber, logFileLink])
	404	}
	405
	406	if(lineNumber % 100 == 0)
	407	cleanUpGorm()
	408
	409	if(!result.error) nextLine()
	410	} //while(line)
	411
	412	// Success.
	413	log.info "End of file."
	414	reader.close()
	415	return result
	416
	417	} //end withTransaction
	418	} // end importInventory()
	419
	420	/**
	421	* Build an inventory template csv file.
	422	* This template can then be populated for import.
	423	* @returns The template as a String in csv format.
	424	*/
	425	def buildInventoryTemplate() {
	426
	427	StringWriter sw = new StringWriter()
	428	CSVWriter writer = new CSVWriter(sw)
	429
	430	writeTemplateLines(writer)
	431
	432	writer.close()
	433	return sw.toString()
	434	}
	435
	436	private writeTemplateLines(writer) {
	437	writer.writeNext(templateHeaderLine1 as String[])
	438	writer.writeNext()
	439	writer.writeNext("Comment: The header line is required.")
	440	writer.writeNext("Comment: Required columns are marked with a (*) in the header line.")
	441	writer.writeNext("Comment: Lists of items in a column must be separated by a semicolon (;), not a comma.")
	442	writer.writeNext("Comment: The at symbol (@) is reserved for indicating supplier and manufacturer types.")
	443	writer.writeNext("Comment: Identical and existing names will be considered as the same item.")
	444	writer.writeNext("Comment: Lines containing 'comment' will be ignored.")
	445	writer.writeNext("Comment: Lines containing 'example' will be ignored.")
	446	writer.writeNext("Comment: This file must be saved as a CSV file before import.")
	447	writer.writeNext()
	448	}
	449
	450	/**
	451	* Build an inventory example/test file.
	452	* This test file can be imported to test the import and export methods.
	453	* @returns The test file as a String in csv format.
	454	*/
	455	def buildInventoryExample() {
	456
	457	StringWriter sw = new StringWriter()
	458	CSVWriter writer = new CSVWriter(sw)
	459
	460	writeTemplateLines(writer)
	461
	462	// Requires creation of some of the base/group/type data.
	463	writer.writeNext(["Split19", "19mm split pin", "Very usefull item.",
	464	"1024", "0", "1",
	465	"each", "5", "NZD",
	466	"false", "BR4",
	467	"Store #99", "Inventory Depot",
	468	"Mechanical Stock",
	469	"Consumable",
	470	"7", "Week(s)",
	471	"123", "Multi Distributors1@OEM; Multi Distributors2@Local",
	472	"321", "Mega Manufacturer1@OEM;Mega Manufacturer2@Alternate",
	473	"2204E-2RS", ""
	474	] as String[])
	475
	476	// Using existing base data.
	477	writer.writeNext(["2204E-2RS", "Double Row Self Align Ball Bearing 2204E-2RS - Sealed - 20/47x18", "",
	478	"4", "1", "9",
	479	"each", "16.35", "USD",
	480	"TRUE", "BR4",
	481	"Store #99", "Inventory Depot",
	482	"Mechanical Stock",
	483	"Consumable",
	484	"2", "Month(s)",
	485	"456KL", "Multi Distributors1; Multi Distributors2",
	486	"654OP", "Mega Manufacturer1;Mega Manufacturer2",
	487	"", ""
	488	] as String[])
	489
	490	writer.close()
	491	return sw.toString()
	492	}
	493
	494	/**
	495	* Build complete inventory for export.
	496	* @param inventoryItemList The list of inventory items to build..
	497	* @returns The inventory as a String in csv format.
	498	*/
	499	def buildInventory(List inventoryItemList) {
	500
	501	def sw = new StringWriter()
	502	def writer = new CSVWriter(sw)
	503
	504	writeTemplateLines(writer)
	505
	506	//Rows
	507	def row
	508
	509	inventoryItemList.sort { p1, p2 -> p1.name.compareToIgnoreCase(p2.name) }.each() { inventoryItem ->
	510	row = []
	511	row.add(inventoryItem.name)
	512	row.add(inventoryItem.description)
	513	row.add(inventoryItem.comment)
	514	row.add(inventoryItem.unitsInStock)
	515	row.add(inventoryItem.reorderPoint)
	516	row.add(inventoryItem.recommendedReorderPoint)
	517	row.add(inventoryItem.unitOfMeasure)
	518	row.add(inventoryItem.estimatedUnitPriceAmount)
	519	row.add(inventoryItem.estimatedUnitPriceCurrency)
	520	row.add(inventoryItem.enableReorder)
	521	row.add(inventoryItem.inventoryLocation)
	522	row.add(inventoryItem.inventoryLocation.inventoryStore)
	523	row.add(inventoryItem.inventoryLocation.inventoryStore.site)
	524	row.add(inventoryItem.inventoryGroup)
	525	row.add(inventoryItem.inventoryType)
	526	row.add(inventoryItem.averageDeliveryTime)
	527	row.add(inventoryItem.averageDeliveryPeriod)
	528	row.add(inventoryItem.suppliersPartNumber)
	529
	530	row.add( inventoryItem.suppliers.sort { p1, p2 ->
	531	p1.name.compareToIgnoreCase(p2.name)
	532	}.collect { it.name + "@" + it.supplierType }.join(';') )
	533
	534	row.add(inventoryItem.manufacturersPartNumber)
	535
	536	row.add(inventoryItem.manufacturers.sort { p1, p2 ->
	537	p1.name.compareToIgnoreCase(p2.name)
	538	}.collect { it.name + "@" + it.manufacturerType }.join(';'))
	539
	540	row.add(inventoryItem.alternateItems.sort { p1, p2 ->
	541	p1.name.compareToIgnoreCase(p2.name)
	542	}.collect { it.name }.join(';') )
	543
	544	row.add(inventoryItem.spareFor.sort { p1, p2 ->
	545	p1.name.compareToIgnoreCase(p2.name)
	546	}.collect { it.name }.join(';'))
	547
	548	writer.writeNext(row as String[])
	549	}
	550
	551	writer.close()
	552	return sw.toString()
	553	} // end buildInventory
	554
	555	private getTemplateHeaderLine1() {
	556	["Name", "Description", "Comment", "Units In Stock", "Reorder Point", "Recommended Reorder Point", "Unit Of Measure*",
	557	"Estimated Unit Price", "Currency", "Enable Reorder", "Location", "Store", "Site", "Group", "Type*",
	558	"averageDeliveryTime", "averageDeliveryPeriod", "Supplier's Part Number", "Supplier",
	559	"Manufacturer's Part Number", "Manufacturer", "Alternate Item", "Spare For"]
	560	}
	561
	562	/**
	563	* This cleans up the hibernate session and a grails map.
	564	* For more info see: http://naleid.com/blog/2009/10/01/batch-import-performance-with-grails-and-mysql/
	565	* The hibernate session flush is normal for hibernate.
	566	* The map is apparently used by grails for domain object validation errors.
	567	* A starting point for clean up is every 100 objects.
	568	*/
	569	def cleanUpGorm() {
	570	def session = sessionFactory.currentSession
	571	session.flush()
	572	session.clear()
	573	propertyInstanceMap.get().clear()
	574	}
	575
	576	} // end class

Note: See TracBrowser for help on using the repository browser.

Download in other formats: