Context Navigation

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

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

Line
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
29	def fileMaxSize = 800 * kByteMultiplier
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 = []
84	def tempSupplierItem = ''
85	def tempSupplierType = ''
86	def tempSupplierItemAndType = []
87	def tempManufacturers = []
88	def tempManufacturerItem = ''
89	def tempManufacturerType = ''
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 = {
119	if( (it == null) \|\| (it.trim() == '') ) return []
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
185	inventoryParams.site = WordUtils.capitalize(inventoryParams.site)
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
196	inventoryParams.inventoryStore = WordUtils.capitalizeFully(inventoryParams.inventoryStore)
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
208	inventoryParams.inventoryLocation = WordUtils.capitalize(inventoryParams.inventoryLocation)
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
220	inventoryParams.inventoryLocation = WordUtils.capitalizeFully(inventoryParams.inventoryLocation)
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
231	inventoryParams.inventoryType = WordUtils.capitalizeFully(inventoryParams.inventoryType)
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)
266	tempManufacturerItem = WordUtils.capitalizeFully(tempManufacturerItemAndType[0])
267
268	manufacturerInstance = Manufacturer.findByName(tempManufacturerItem)
269	if(!manufacturerInstance) {
270
271	// ManufacturerType.
272	if(tempManufacturerItemAndType.size == 2) {
273	tempManufacturerType = WordUtils.capitalizeFully(tempManufacturerItemAndType[1])
274	manufacturerTypeInstance = ManufacturerType.findByName(tempManufacturerType)
275	}
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
283	manufacturerInstance = new Manufacturer(name: tempManufacturerItem,
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)
300	tempSupplierItem = WordUtils.capitalizeFully(tempSupplierItemAndType[0])
301
302	supplierInstance = Supplier.findByName(tempSupplierItem)
303	if(!supplierInstance) {
304
305	// SupplierType.
306	if(tempSupplierItemAndType.size == 2) {
307	tempSupplierType = WordUtils.capitalizeFully(tempSupplierItemAndType[1])
308	supplierTypeInstance = SupplierType.findByName(tempSupplierType)
309	}
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
317	supplierInstance = new Supplier(name: tempSupplierItem,
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) {
333	tempAlternateItem = WordUtils.capitalize(tempAlternateItem)
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
358	asset = WordUtils.capitalize(asset)
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
376	// Name.
377	// Checked above for blank string.
378	inventoryParams.name = WordUtils.capitalize(inventoryParams.name)
379
380	// Description.
381	if(inventoryParams.description != '')
382	inventoryParams.description = inventoryParams.description[0].toUpperCase() + inventoryParams.description[1..-1]
383
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: