Context Navigation

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

Last change on this file since 426 was 426, checked in by gav, 14 years ago
Small improvements to InventoryCsvService, increase max file size, capitalise and protect parse against nulls.
File size: 25.8 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	// Format remaining properties.
377	inventoryParams.name = WordUtils.capitalize(inventoryParams.name)
378	inventoryParams.description = inventoryParams.description[0].toUpperCase() + inventoryParams.description[1..-1]
379
380	// Debug
381	log.debug "InventoryParams: "
382	log.debug inventoryParams
383
384	// Create new or update.
385	inventoryItemInstance = InventoryItem.findByName(inventoryParams.name)
386	if(inventoryItemInstance) {
387	log.info "Updating existing item: " + inventoryItemInstance
388	inventoryItemInstance.properties = inventoryParams
389	}
390	else {
391	log.info "Creating new item: " + inventoryParams.name
392	inventoryItemInstance = new InventoryItem(inventoryParams)
393	}
394
395	// Save inventoryItem.
396	if(inventoryItemInstance.hasErrors() \|\| !inventoryItemInstance.save()) {
397	log.error "Failed to create item on line: " + column + "(" + lineNumber + ")"
398	log.debug inventoryItemInstance.errors
399	return fail(code: "inventory.import.failure", args: [lineNumber, logFileLink])
400	}
401
402	if(lineNumber % 100 == 0)
403	cleanUpGorm()
404
405	if(!result.error) nextLine()
406	} //while(line)
407
408	// Success.
409	log.info "End of file."
410	reader.close()
411	return result
412
413	} //end withTransaction
414	} // end importInventory()
415
416	/**
417	* Build an inventory template csv file.
418	* This template can then be populated for import.
419	* @returns The template as a String in csv format.
420	*/
421	def buildInventoryTemplate() {
422
423	StringWriter sw = new StringWriter()
424	CSVWriter writer = new CSVWriter(sw)
425
426	writeTemplateLines(writer)
427
428	writer.close()
429	return sw.toString()
430	}
431
432	private writeTemplateLines(writer) {
433	writer.writeNext(templateHeaderLine1 as String[])
434	writer.writeNext()
435	writer.writeNext("Comment: The header line is required.")
436	writer.writeNext("Comment: Required columns are marked with a (*) in the header line.")
437	writer.writeNext("Comment: Lists of items in a column must be separated by a semicolon (;), not a comma.")
438	writer.writeNext("Comment: The at symbol (@) is reserved for indicating supplier and manufacturer types.")
439	writer.writeNext("Comment: Identical and existing names will be considered as the same item.")
440	writer.writeNext("Comment: Lines containing 'comment' will be ignored.")
441	writer.writeNext("Comment: Lines containing 'example' will be ignored.")
442	writer.writeNext("Comment: This file must be saved as a CSV file before import.")
443	writer.writeNext()
444	}
445
446	/**
447	* Build an inventory example/test file.
448	* This test file can be imported to test the import and export methods.
449	* @returns The test file as a String in csv format.
450	*/
451	def buildInventoryExample() {
452
453	StringWriter sw = new StringWriter()
454	CSVWriter writer = new CSVWriter(sw)
455
456	writeTemplateLines(writer)
457
458	// Requires creation of some of the base/group/type data.
459	writer.writeNext(["Split19", "19mm split pin", "Very usefull item.",
460	"1024", "0", "1",
461	"each", "5", "NZD",
462	"false", "BR4",
463	"Store #99", "Inventory Depot",
464	"Mechanical Stock",
465	"Consumable",
466	"7", "Week(s)",
467	"123", "Multi Distributors1@OEM; Multi Distributors2@Local",
468	"321", "Mega Manufacturer1@OEM;Mega Manufacturer2@Alternate",
469	"2204E-2RS", ""
470	] as String[])
471
472	// Using existing base data.
473	writer.writeNext(["2204E-2RS", "Double Row Self Align Ball Bearing 2204E-2RS - Sealed - 20/47x18", "",
474	"4", "1", "9",
475	"each", "16.35", "USD",
476	"TRUE", "BR4",
477	"Store #99", "Inventory Depot",
478	"Mechanical Stock",
479	"Consumable",
480	"2", "Month(s)",
481	"456KL", "Multi Distributors1; Multi Distributors2",
482	"654OP", "Mega Manufacturer1;Mega Manufacturer2",
483	"", ""
484	] as String[])
485
486	writer.close()
487	return sw.toString()
488	}
489
490	/**
491	* Build complete inventory for export.
492	* @param inventoryItemList The list of inventory items to build..
493	* @returns The inventory as a String in csv format.
494	*/
495	def buildInventory(List inventoryItemList) {
496
497	def sw = new StringWriter()
498	def writer = new CSVWriter(sw)
499
500	writeTemplateLines(writer)
501
502	//Rows
503	def row
504
505	inventoryItemList.sort { p1, p2 -> p1.name.compareToIgnoreCase(p2.name) }.each() { inventoryItem ->
506	row = []
507	row.add(inventoryItem.name)
508	row.add(inventoryItem.description)
509	row.add(inventoryItem.comment)
510	row.add(inventoryItem.unitsInStock)
511	row.add(inventoryItem.reorderPoint)
512	row.add(inventoryItem.recommendedReorderPoint)
513	row.add(inventoryItem.unitOfMeasure)
514	row.add(inventoryItem.estimatedUnitPriceAmount)
515	row.add(inventoryItem.estimatedUnitPriceCurrency)
516	row.add(inventoryItem.enableReorder)
517	row.add(inventoryItem.inventoryLocation)
518	row.add(inventoryItem.inventoryLocation.inventoryStore)
519	row.add(inventoryItem.inventoryLocation.inventoryStore.site)
520	row.add(inventoryItem.inventoryGroup)
521	row.add(inventoryItem.inventoryType)
522	row.add(inventoryItem.averageDeliveryTime)
523	row.add(inventoryItem.averageDeliveryPeriod)
524	row.add(inventoryItem.suppliersPartNumber)
525
526	row.add( inventoryItem.suppliers.sort { p1, p2 ->
527	p1.name.compareToIgnoreCase(p2.name)
528	}.collect { it.name + "@" + it.supplierType }.join(';') )
529
530	row.add(inventoryItem.manufacturersPartNumber)
531
532	row.add(inventoryItem.manufacturers.sort { p1, p2 ->
533	p1.name.compareToIgnoreCase(p2.name)
534	}.collect { it.name + "@" + it.manufacturerType }.join(';'))
535
536	row.add(inventoryItem.alternateItems.sort { p1, p2 ->
537	p1.name.compareToIgnoreCase(p2.name)
538	}.collect { it.name }.join(';') )
539
540	row.add(inventoryItem.spareFor.sort { p1, p2 ->
541	p1.name.compareToIgnoreCase(p2.name)
542	}.collect { it.name }.join(';'))
543
544	writer.writeNext(row as String[])
545	}
546
547	writer.close()
548	return sw.toString()
549	} // end buildInventory
550
551	private getTemplateHeaderLine1() {
552	["Name", "Description", "Comment", "Units In Stock", "Reorder Point", "Recommended Reorder Point", "Unit Of Measure*",
553	"Estimated Unit Price", "Currency", "Enable Reorder", "Location", "Store", "Site", "Group", "Type*",
554	"averageDeliveryTime", "averageDeliveryPeriod", "Supplier's Part Number", "Supplier",
555	"Manufacturer's Part Number", "Manufacturer", "Alternate Item", "Spare For"]
556	}
557
558	/**
559	* This cleans up the hibernate session and a grails map.
560	* For more info see: http://naleid.com/blog/2009/10/01/batch-import-performance-with-grails-and-mysql/
561	* The hibernate session flush is normal for hibernate.
562	* The map is apparently used by grails for domain object validation errors.
563	* A starting point for clean up is every 100 objects.
564	*/
565	def cleanUpGorm() {
566	def session = sessionFactory.currentSession
567	session.flush()
568	session.clear()
569	propertyInstanceMap.get().clear()
570	}
571
572	} // end class

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

Download in other formats: