Context Navigation

source: trunk/grails-app/services/CsvService.groovy @ 358

Last change on this file since 358 was 340, checked in by gav, 15 years ago
Small adjustment to CsvService asset tree import, to handle blank description on last column correctly.
File size: 17.7 KB

Rev	Line
[271]	1	import au.com.bytecode.opencsv.CSVWriter
	2	import au.com.bytecode.opencsv.CSVReader
[315]	3	import org.apache.commons.lang.WordUtils
[271]	4
	5	/**
	6	* Provides some csv import/export methods.
	7	* Requires the opencsv jar to be available which is included in the grails-export plugin.
	8	*/
	9	class CsvService {
	10
	11	boolean transactional = false
	12
	13	/**
[287]	14	* Import an asset tree creating items as required.
	15	* @param request The http request to run getFile against.
	16	* Get file should return a csv format file containing the asset tree as per template.
[271]	17	*/
	18	def importAssetTree(request) {
[290]	19	Asset.withTransaction { status ->
	20	def result = [:]
[271]	21
[300]	22	def kByteMultiplier = 1000
	23	def fileMaxSize = 500 * kByteMultiplier
[271]	24
[290]	25	def multiPartFile = request.getFile('file')
[287]	26
[290]	27	InputStreamReader sr = new InputStreamReader(multiPartFile.inputStream)
	28	CSVReader reader = new CSVReader(sr)
[287]	29
[290]	30	def fail = { Map m ->
	31	status.setRollbackOnly()
	32	reader.close()
	33	result.error = [ code: m.code, args: m.args ]
	34	return result
	35	}
[271]	36
[290]	37	if(!multiPartFile \|\| multiPartFile.isEmpty())
	38	return fail(code: "asset.tree.import.file.not.supplied")
[271]	39
[290]	40	if (multiPartFile.getSize() > fileMaxSize)
[300]	41	return fail(code: "asset.tree.import.file.over.max.size", args: [fileMaxSize/kByteMultiplier, "kB"])
[271]	42
[300]	43	def columnIndex = 0
[340]	44	def nameColumnIndex = 0
[300]	45	def numberOfColumns = 0
	46	def maxNumberOfColumns = 20
	47
	48	// Get first line.
[290]	49	def line = reader.readNext()
	50	def lineNumber = 1
[271]	51
[300]	52	// Check for header line 1.
	53	if(line != templateHeaderLine1) {
	54	log.error "Failed to find header line 1. "
	55	log.error "Required: " + templateHeaderLine1.toString()
	56	log.error "Supplied: " + line.toString()
	57	return fail(code: "asset.tree.import.no.header")
	58	}
[287]	59
[300]	60	// Get second line.
	61	line = reader.readNext()
	62	lineNumber ++
	63
	64	// Check for header line 2.
	65	if(line != templateHeaderLine2) {
	66	log.error "Failed to find header line 2. "
	67	log.error "Required: " + templateHeaderLine2.toString()
	68	log.error "Supplied: " + line.toString()
[290]	69	return fail(code: "asset.tree.import.no.header")
[300]	70	}
[271]	71
[290]	72	log.info "Import checks passed, start processing asset file."
[287]	73
[290]	74	// Prepare the first body line.
	75	line = reader.readNext()
	76	lineNumber ++
[271]	77
[290]	78	def siteInstance
[300]	79	def departmentInstance
[290]	80	def sectionInstance
	81	def assetInstance
[300]	82	def assetSubItemInstance
	83	def parentItem
[271]	84
[301]	85	def column = [:]
	86
	87	def nextLine = {
	88	line = reader.readNext()
	89	lineNumber ++
	90	log.info "Processing line: " + lineNumber
	91	}
	92
	93	def nextColumn = {
[340]	94	nameColumnIndex = columnIndex
	95
	96	if( (columnIndex+1) < numberOfColumns ) {
	97	// Capitalise and assign the name and description columns.
	98	use(WordUtils) {
	99	column.name = line[columnIndex].trim().capitalize()
	100	column.description = line[++columnIndex].trim().capitalize()
	101	}
	102	}
	103	else if( columnIndex < numberOfColumns ) {
	104	// Capitalise and assign the name column with a blank description.
	105	use(WordUtils) {
	106	column.name = line[columnIndex].trim().capitalize()
	107	column.description = ''
	108	}
	109	}
	110	else {
[301]	111	log.info "No more columns on line: " + lineNumber
	112	return false
	113	}
[340]	114
	115	if(!column.name) {
	116	log.info "No name at " + "line: " + lineNumber + " col: " + nameColumnIndex
[301]	117	return false
	118	}
[340]	119
[301]	120	columnIndex++
	121	// Success.
	122	return column
	123	}
	124
[315]	125	// Primary loop.
[290]	126	while(line) {
[301]	127	numberOfColumns = Math.min( line.size(), maxNumberOfColumns )
[300]	128	columnIndex = 0
[271]	129
[301]	130	if(!nextColumn()) {
	131	nextLine()
[300]	132	continue
[290]	133	}
[271]	134
[302]	135	// Ignore comment lines.
[315]	136	if(line.toString().toLowerCase().contains("comment")) {
[302]	137	log.info "Comment line found."
	138	nextLine()
	139	continue
	140	}
	141
	142	// Ignore example lines.
[315]	143	if(line.toString().toLowerCase().contains("example")) {
[302]	144	log.info "Example line found."
	145	nextLine()
	146	continue
	147	}
	148
[315]	149	// Site.
[301]	150	siteInstance = Site.findByName(column.name)
	151	if(!siteInstance) {
	152	log.info "Creating site: " + column.name
	153	siteInstance = new Site(name: column.name,
	154	description: column.description)
	155	if(!siteInstance.save()) {
	156	log.error "Failed to create site on line: " + column.name + "(" + lineNumber + ")"
	157	return fail(code: "asset.tree.import.failure", args: [lineNumber])
[300]	158	}
[301]	159	}
	160	else log.info "Existing site: " + siteInstance
[271]	161
[301]	162	if(!nextColumn()) {
	163	nextLine()
	164	continue
	165	}
	166
[315]	167	// Department.
[301]	168	departmentInstance = Department.findByName(column.name)
	169	if(!departmentInstance) {
	170	log.info "Creating department: " + column.name
	171	departmentInstance = new Department(name: column.name,
	172	description: column.description,
	173	site: siteInstance)
	174	if(!departmentInstance.save()) {
	175	log.error "Failed to create department on line: " + column.name + "(" + lineNumber + ")"
	176	return fail(code: "asset.tree.import.failure", args: [lineNumber])
[300]	177	}
[301]	178	}
	179	else log.info "Existing department: " + departmentInstance
[300]	180
[301]	181	if(!nextColumn()) {
	182	nextLine()
	183	continue
	184	}
[290]	185
[315]	186	// Section.
[301]	187	sectionInstance = Section.findByName(column.name)
	188	if(!sectionInstance) {
	189	log.info "Creating section: " + column.name
	190	sectionInstance = new Section(name: column.name,
	191	description: column.description,
	192	site: siteInstance,
	193	department: departmentInstance)
	194	if(!sectionInstance.save()) {
	195	log.error "Failed to create section on line: " + column.name + "(" + lineNumber + ")"
	196	return fail(code: "asset.tree.import.failure", args: [lineNumber])
	197	}
	198	}
	199	else log.info "Existing section: " + sectionInstance
[300]	200
[301]	201	if(!nextColumn()) {
	202	nextLine()
	203	continue
	204	}
[300]	205
[315]	206	// Asset.
[301]	207	assetInstance = Asset.findByName(column.name)
	208	if(!assetInstance) {
	209	log.info "Creating asset: " + column.name
	210	assetInstance = new Asset(name: column.name,
	211	description: column.description,
	212	section: sectionInstance)
	213	if(!assetInstance.save()) {
	214	log.error "Failed to create asset on line: " + column.name + "(" + lineNumber + ")"
	215	return fail(code: "asset.tree.import.failure", args: [lineNumber])
	216	}
	217	}
	218	else log.info "Existing asset: " + assetInstance
[300]	219
[301]	220	if(!nextColumn()) {
	221	nextLine()
	222	continue
	223	}
[300]	224
[315]	225	// AssetSubItem Level 1.
[301]	226	assetSubItemInstance = AssetSubItem.findByName(column.name)
	227	if(!assetSubItemInstance) {
	228	log.info "Creating asset sub item: " + column.name
	229	assetSubItemInstance = new AssetSubItem(name: column.name,
	230	description: column.description)
	231	if(!assetInstance.save()) {
	232	log.error "Failed to create assetSubItem on line: " + column.name + "(" + lineNumber + ")"
	233	return fail(code: "asset.tree.import.failure", args: [lineNumber])
	234	}
	235	}
	236	else log.info "Existing asset sub item: " + assetSubItemInstance
[300]	237
[301]	238	assetInstance.addToAssetSubItems(assetSubItemInstance)
[300]	239
[315]	240	// Remaining AssetSubItems.
[301]	241	while( nextColumn() ) {
[300]	242
[301]	243	parentItem = assetSubItemInstance
	244	assetSubItemInstance = AssetSubItem.findByName(column.name)
	245	if(!assetSubItemInstance) {
	246	log.info "Creating asset sub item: " + column.name
	247	assetSubItemInstance = new AssetSubItem(name: column.name,
	248	description: column.description,
	249	parentItem: parentItem)
	250	if(!assetSubItemInstance.save()) {
	251	log.error "Failed to create assetSubItem on line: " + column.name + "(" + lineNumber + ")"
	252	return fail(code: "asset.tree.import.failure", args: [lineNumber])
	253	}
	254	}
[315]	255	else log.info "Existing asset sub item: " + assetSubItemInstance
[300]	256
[301]	257	} // while( nextColumn() )
[300]	258
[301]	259	nextLine()
[290]	260	} //while(line)
	261
	262	// Success.
[302]	263	log.info "End of file."
[290]	264	reader.close()
	265	return result
	266
	267	} //end withTransaction
[271]	268	} // end importAssetTree()
	269
	270	/**
	271	* Build an asset tree template csv file.
	272	* This template can then be populated for import.
	273	* @returns The template as a String in csv format.
	274	*/
	275	def buildAssetTreeTemplate() {
	276
	277	StringWriter sw = new StringWriter()
	278	CSVWriter writer = new CSVWriter(sw)
	279
[302]	280	writeTemplateLines(writer)
	281
	282	writer.close()
	283	return sw.toString()
	284	}
	285
	286	private writeTemplateLines(writer) {
[300]	287	writer.writeNext(templateHeaderLine1 as String[])
	288	writer.writeNext(templateHeaderLine2 as String[])
	289	writer.writeNext()
[302]	290	writer.writeNext(["Example: Site1", "", "Department 1", "", "Section 1", "", "Asset 1", ""] as String[])
	291	writer.writeNext()
	292	writer.writeNext(["Example: Site1", "", "Department 1", "", "Section 1", "", "Asset 2", ""] as String[])
	293	writer.writeNext()
[315]	294	writer.writeNext("Comment: The first two header lines are required.")
	295	writer.writeNext("Comment: Leave a blank line between assets.")
	296	writer.writeNext("Comment: Names are required, descriptions are optional.")
	297	writer.writeNext("Comment: Identical and existing names will be considered as the same item.")
	298	writer.writeNext("Comment: An asset may share the first level of asset sub items with other assets.")
	299	writer.writeNext("Comment: Lower levels of asset sub items are only parented once but may have many children.")
	300	writer.writeNext("Comment: Lines containing 'comment' will be ignored.")
	301	writer.writeNext("Comment: Lines containing 'example' will be ignored.")
	302	writer.writeNext("Comment: This file must be saved as a CSV file before import.")
[302]	303	writer.writeNext()
	304	}
[287]	305
[302]	306	/**
	307	* Build an asset tree test file.
	308	* This test file can be imported to test the import and template methods.
	309	* @returns The test file as a String in csv format.
	310	*/
	311	def buildAssetTreeTest() {
	312
	313	StringWriter sw = new StringWriter()
	314	CSVWriter writer = new CSVWriter(sw)
	315
	316	writeTemplateLines(writer)
	317
	318	writer.writeNext(["Lake Press", "Lake Press Site",
	319	"Print Department", "The printing department",
	320	"Press Section", "Contains all printing units",
	321	"Print Unit 1", "Printing Unit Number 1",
	322	"Print Unit", "Print Unit (Common to all units)",
	323	"Print Couple", "Includes blanket cylinder",
	324	"Motor", "Main Drive Motor",
	325	"NDS Bearing", "Non Drive Side Main Bearing"
	326	] as String[])
	327
	328	writer.writeNext(["Lake Press", "Lake Press Site",
	329	"Print Department", "The printing department",
	330	"Press Section", "Contains all printing units",
	331	"Print Unit 1", "Printing Unit Number 1",
	332	"Print Unit", "Print Unit (Common to all units)",
	333	"Print Couple", "Includes blanket cylinder",
	334	"Motor", "Main Drive Motor",
	335	"DS Bearing", "Drive Side Main Bearing"
	336	] as String[])
	337
[271]	338	writer.close()
	339	return sw.toString()
	340	}
	341
	342	/**
	343	* Build complete asset trees for export.
	344	* @param assetList The list of assets to build and export trees for.
	345	* @returns The tree as a String in csv format.
	346	*/
	347	def buildAssetTree(List assetList) {
	348
	349	StringWriter sw = new StringWriter()
	350	CSVWriter writer = new CSVWriter(sw)
	351
[315]	352	writeTemplateLines(writer)
[271]	353
	354	//Rows
	355	def row
	356
	357	def writeAssetSubItem4 = { assetSubItem ->
[315]	358	row.add(assetSubItem.name)
	359	row.add(assetSubItem.description)
[271]	360	writer.writeNext(row as String[])
	361	}
	362
	363	def writeAssetSubItem3 = { assetSubItem ->
[315]	364	row.add(assetSubItem.name)
	365	row.add(assetSubItem.description)
[271]	366
	367	if(assetSubItem.subItems.size() > 0) {
	368	assetSubItem.subItems.sort { p1, p2 -> p1.name.compareToIgnoreCase(p2.name) }.each() { assetSubItem4 ->
	369	writeAssetSubItem4(assetSubItem4)
[327]	370	row.removeRange(row.size()-2, row.size())
[271]	371	}
	372	}
	373	else {
	374	writer.writeNext(row as String[])
	375	}
	376
	377	}
	378
	379	def writeAssetSubItem2 = { assetSubItem ->
[315]	380	row.add(assetSubItem.name)
	381	row.add(assetSubItem.description)
[271]	382
	383	if(assetSubItem.subItems.size() > 0) {
	384	assetSubItem.subItems.sort { p1, p2 -> p1.name.compareToIgnoreCase(p2.name) }.each() { assetSubItem3 ->
	385	writeAssetSubItem3(assetSubItem3)
[327]	386	row.removeRange(row.size()-2, row.size())
[271]	387	}
	388	}
	389	else {
	390	writer.writeNext(row as String[])
	391	}
	392
	393	}
	394
	395	def writeAssetSubItem1 = { assetSubItem ->
[315]	396	row.add(assetSubItem.name)
	397	row.add(assetSubItem.description)
[271]	398
	399	if(assetSubItem.subItems.size() > 0) {
	400	assetSubItem.subItems.sort { p1, p2 -> p1.name.compareToIgnoreCase(p2.name) }.each() { assetSubItem2 ->
	401	writeAssetSubItem2(assetSubItem2)
[327]	402	row.removeRange(row.size()-2, row.size())
[271]	403	}
	404	}
	405	else {
	406	writer.writeNext(row as String[])
	407	}
	408
	409	}
	410
	411	assetList.sort { p1, p2 -> p1.name.compareToIgnoreCase(p2.name) }.each() { asset ->
	412	row = []
	413	writer.writeNext(row as String[]) //blank row between assets.
[315]	414	row.add(asset.section.site.name)
	415	row.add(asset.section.site.description)
	416	row.add(asset.section.department.name)
	417	row.add(asset.section.department.description)
	418	row.add(asset.section.name)
	419	row.add(asset.section.description)
[271]	420	row.add(asset.name)
[315]	421	row.add(asset.description)
[271]	422
	423	if(asset.assetSubItems.size() > 0) {
	424	asset.assetSubItems.each() { assetSubItem1 ->
	425	writeAssetSubItem1(assetSubItem1)
[327]	426	row.removeRange(row.size()-2, row.size())
[271]	427	}
	428	}
	429	else {
	430	writer.writeNext(row as String[])
	431	}
	432
	433	}
	434
	435	writer.close()
	436	return sw.toString()
	437	} // end buildAssetTree
	438
[300]	439	private getTemplateHeaderLine1() {
	440	["Site", "", "Department", "", "Section", "","Asset", "", "Sub Asset", "", "Functional Assembly", "", "Sub Assembly Group", "", "SubItem", ""]
	441	}
	442
	443	private getTemplateHeaderLine2() {
	444	(["Name", "Description"])*8
	445	}
	446
[271]	447	} // end class

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

Download in other formats: