Context Navigation

ImporterService.groovy @ 1002

Revision 1002, 16.5 KB checked in by t.w.abma@…, 6 years ago (diff)
added functionality to check for existing entities when importing from Excel, now updates existing entities (at least for Subject it does currently)
Property svn:keywords set to `Date Author Rev`

Line
1	/**
2	* Importer service
3	*
4	* The importer service handles the import of tabular, comma delimited and Excel format
5	* based files.
6	*
7	* @package importer
8	* @author t.w.abma@umcutrecht.nl
9	* @since 20100126
10	*
11	* Revision information:
12	* $Rev$
13	* $Author$
14	* $Date$
15	*/
16
17	package dbnp.importer
18	import org.apache.poi.hssf.usermodel.*
19	import org.apache.poi.poifs.filesystem.POIFSFileSystem
20	import org.apache.poi.ss.usermodel.DataFormatter
21
22	import dbnp.studycapturing.TemplateFieldType
23	import dbnp.studycapturing.Template
24	import dbnp.studycapturing.SamplingEvent
25	import dbnp.studycapturing.Study
26	import dbnp.studycapturing.Subject
27	import dbnp.studycapturing.Event
28	import dbnp.studycapturing.Sample
29
30	import dbnp.data.Term
31
32	class ImporterService {
33
34	boolean transactional = true
35
36	/**
37	* @param is input stream representing the (workbook) resource
38	* @return high level representation of the workbook
39	*/
40	HSSFWorkbook getWorkbook(InputStream is) {
41	POIFSFileSystem fs = new POIFSFileSystem(is)
42	HSSFWorkbook wb = new HSSFWorkbook(fs);
43	return wb;
44	}
45
46	/**
47	* @param wb high level representation of the workbook
48	* @param sheetindex sheet to use within the workbook
49	* @return header representation as a MappingColumn hashmap
50	*/
51	def getHeader(HSSFWorkbook wb, int sheetindex, int headerrow, int datamatrix_start, theEntity=null){
52
53	def sheet = wb.getSheetAt(sheetindex)
54	def sheetrow = sheet.getRow(datamatrix_start)
55	//def header = []
56	def header = [:]
57	def df = new DataFormatter()
58	def property = new String()
59
60	//for (HSSFCell c: sheet.getRow(datamatrix_start)) {
61
62	(0..sheetrow.getLastCellNum() -1 ).each { columnindex ->
63
64	//def index = c.getColumnIndex()
65	def datamatrix_celltype = sheet.getRow(datamatrix_start).getCell(columnindex, org.apache.poi.ss.usermodel.Row.CREATE_NULL_AS_BLANK).getCellType()
66	def datamatrix_celldata = df.formatCellValue(sheet.getRow(datamatrix_start).getCell(columnindex))
67	def datamatrix_cell = sheet.getRow(datamatrix_start).getCell(columnindex)
68	println "frn is "+sheet.getFirstRowNum()
69	def headercell = sheet.getRow(headerrow-1+sheet.getFirstRowNum()).getCell(columnindex)
70	def tft = TemplateFieldType.STRING //default templatefield type
71
72	// Check for every celltype, currently redundant code, but possibly this will be
73	// a piece of custom code for every cell type like specific formatting
74
75	switch (datamatrix_celltype) {
76	case HSSFCell.CELL_TYPE_STRING:
77	//parse cell value as double
78	def doubleBoolean = true
79	def fieldtype = TemplateFieldType.STRING
80
81	// is this string perhaps a double?
82	try {
83	formatValue(datamatrix_celldata, TemplateFieldType.DOUBLE)
84	} catch (NumberFormatException nfe) { doubleBoolean = false }
85	finally {
86	if (doubleBoolean) fieldtype = TemplateFieldType.DOUBLE
87	}
88
89	header[columnindex] = new dbnp.importer.MappingColumn(name:df.formatCellValue(headercell),
90	templatefieldtype:fieldtype,
91	index:columnindex,
92	entity:theEntity,
93	property:property);
94
95	break
96	case HSSFCell.CELL_TYPE_NUMERIC:
97	def fieldtype = TemplateFieldType.INTEGER
98	def doubleBoolean = true
99	def integerBoolean = true
100
101	// is this cell really an integer?
102	try {
103	Integer.valueOf(datamatrix_celldata)
104	} catch (NumberFormatException nfe) { integerBoolean = false }
105	finally {
106	if (integerBoolean) fieldtype = TemplateFieldType.INTEGER
107	}
108
109	// it's not an integer, perhaps a double?
110	if (!integerBoolean)
111	try {
112	formatValue(datamatrix_celldata, TemplateFieldType.DOUBLE)
113	} catch (NumberFormatException nfe) { doubleBoolean = false }
114	finally {
115	if (doubleBoolean) fieldtype = TemplateFieldType.DOUBLE
116	}
117
118	if (HSSFDateUtil.isCellDateFormatted(datamatrix_cell)) fieldtype = TemplateFieldType.DATE
119
120	header[columnindex] = new dbnp.importer.MappingColumn(name:df.formatCellValue(headercell),
121	templatefieldtype:fieldtype,
122	index:columnindex,
123	entity:theEntity,
124	property:property);
125	break
126	case HSSFCell.CELL_TYPE_BLANK:
127	header[columnindex] = new dbnp.importer.MappingColumn(name:df.formatCellValue(headercell),
128	templatefieldtype:TemplateFieldType.STRING,
129	index:columnindex,
130	entity:theEntity,
131	property:property);
132	break
133	default:
134	header[columnindex] = new dbnp.importer.MappingColumn(name:df.formatCellValue(headercell),
135	templatefieldtype:TemplateFieldType.STRING,
136	index:columnindex,
137	entity:theEntity,
138	property:property);
139	break
140	} // end of switch
141	} // end of cell loop
142	return header
143	}
144
145	/**
146	* This method is meant to return a matrix of the rows and columns
147	* used in the preview
148	*
149	* @param wb workbook object
150	* @param sheetindex sheet index used
151	* @param rows amount of rows returned
152	* @return two dimensional array (matrix) of HSSFCell objects
153	*/
154
155	HSSFCell[][] getDatamatrix(HSSFWorkbook wb, header, int sheetindex, int datamatrix_start, int count) {
156	def sheet = wb.getSheetAt(sheetindex)
157	def rows = []
158	def df = new DataFormatter()
159
160	// walk through all rows
161	(count <= sheet.getLastRowNum()) ?
162	((datamatrix_start+sheet.getFirstRowNum())..count).each { rowindex ->
163	def row = []
164
165	// walk through every cell
166	/*for (HSSFCell c: sheet.getRow(rowindex)) {
167	row.add(c)
168	println c.getColumnIndex() + "=" +c
169	}*/
170
171	(0..header.size()-1).each { columnindex ->
172	def c = sheet.getRow(rowindex).getCell(columnindex, org.apache.poi.ss.usermodel.Row.CREATE_NULL_AS_BLANK)
173	//row.add(df.formatCellValue(c))
174	row.add(c)
175	//if (c.getCellType() == c.CELL_TYPE_STRING) println "STR"+c.getStringCellValue()
176	//if (c.getCellType() == c.CELL_TYPE_NUMERIC) println "INT" +c.getNumericCellValue()
177	}
178	//row.add(df.formatCellValue(c))
179	rows.add(row)
180	} : 0
181
182	return rows
183	}
184
185	/**
186	* This method will move a file to a new location.
187	*
188	* @param file File object to move
189	* @param folderpath folder to move the file to
190	* @param filename (new) filename to give
191	* @return if file has been moved succesful, the new path and filename will be returned, otherwise an empty string will be returned
192	*/
193	def moveFile(File file, String folderpath, String filename) {
194	try {
195	def rnd = ""; //System.currentTimeMillis()
196	file.transferTo(new File(folderpath, rnd+filename))
197	return folderpath + filename
198	} catch(Exception exception) {
199	log.error "File move error, ${exception}"
200	return ""
201	}
202	}
203
204	/**
205	* @return random numeric value
206	*/
207	def random = {
208	return System.currentTimeMillis() + Runtime.runtime.freeMemory()
209	}
210
211	/**
212	* Method to read data from a workbook and to import data into a two dimensional
213	* array
214	*
215	* @param template_id template identifier to use fields from
216	* @param wb POI horrible spreadsheet formatted workbook object
217	* @param mcmap linked hashmap (preserved order) of MappingColumns
218	* @param sheetindex sheet to use when using multiple sheets
219	* @param rowindex first row to start with reading the actual data (NOT the header)
220	* @return two dimensional array containing records (with entities)
221	*
222	* @see dbnp.importer.MappingColumn
223	*/
224	def importdata(template_id, HSSFWorkbook wb, int sheetindex, int rowindex, mcmap) {
225	def sheet = wb.getSheetAt(sheetindex)
226	def table = []
227
228	// walk through all rows and fill the table with records
229	(rowindex..sheet.getLastRowNum()).each { i ->
230	table.add(createRecord(template_id, sheet.getRow(i), mcmap))
231	}
232	return table
233	}
234
235	/**
236	* Method to store a matrix containing the entities in a record like structure. Every row in the table
237	* contains one or more entity objects (which contain fields with values). So actually a row represents
238	* a record with fields from one or more different entities.
239	*
240	* @param study entity Study
241	* @param datamatrix two dimensional array containing entities with values read from Excel file
242	*/
243	def saveDatamatrix(Study study, datamatrix) {
244	def validatedSuccesfully = 0
245	def entitystored = null
246	study.refresh()
247
248	// go through the data matrix, read every record and validate the entity and try to persist it
249	datamatrix.each { record ->
250	record.each { entity ->
251	switch (entity.getClass()) {
252	case Study : print "Persisting Study `" + entity + "`: "
253	if (persistEntity(entity)) validatedSuccesfully++
254	break
255	case Subject : print "Persisting Subject `" + entity + "`: "
256	entity.parent = study
257
258	// is the current entity not already in the database?
259	entitystored = isEntityStored(entity)
260
261	// this entity is new, so add it to the study
262	if (entitystored==null) study.addToSubjects(entity)
263	else // existing entity, so update it
264	updateEntity(entitystored, entity)
265
266	if (persistEntity(study)) validatedSuccesfully++
267	break
268	case Event : print "Persisting Event `" + entity + "`: "
269	entity.parent = study
270	study.addToEvents(entity)
271	if (persistEntity(entity)) validatedSuccesfully++
272	break
273	case Sample : print "Persisting Sample `" + entity +"`: "
274	entity.parent = study
275	study.addToSamples(entity)
276	if (persistEntity(entity)) validatedSuccesfully++
277	break
278	case SamplingEvent: print "Persisting SamplingEvent `" + entity + "`: "
279	entity.parent = study
280	study.addToSamplingEvents(entity)
281	if (persistEntity(entity)) validatedSuccesfully++
282	break
283	default : println "Skipping persisting of `" + entity.getclass() +"`"
284	break
285	} // end switch
286	} // end record
287	} // end datamatrix
288	return validatedSuccesfully
289	}
290
291	/**
292	* Check whether an entity already exists within a study. A combination of
293	* the study where the entity will be stored and a unique field in the entity is
294	* used to check whether the instantiated entity (read from Excel) is new.
295	* If the entity is found in the database it will be returned as so and
296	* it should update the entity-values (read from Ecel) into the record in the database.
297	*
298	* @param entity entity object like a Study, Subject, Sample et cetera
299	* @return entity if found, otherwise null
300	*/
301	def isEntityStored(entity) {
302	switch (entity.getClass()) {
303	case Study : return Study.findByCode(entity.code)
304	break
305	case Subject : return Subject.findByParentAndName(entity.parent, entity.name)
306	break
307	case Event : break
308	case Sample : break
309	case SamplingEvent : break
310	default : // unknown entity
311	return null
312	}
313	}
314
315	/**
316	* Find the entity and update the fields. The entity is an instance
317	* read from Excel. This method looks in the database for the entity
318	* having the same identifier. If it has found the same entity
319	* already in the database, it will update the record.
320	*
321	* @param entitystored existing record in the database to update
322	* @param entity entity read from Excel
323	*/
324	def updateEntity(entitystored, entity) {
325	switch (entity.getClass()) {
326	case Study : break
327	case Subject : entitystored.properties = entity.properties
328	entitystored.save()
329	break
330	case Event : break
331	case Sample : break
332	case SamplingEvent : break
333	default : // unknown entity
334	return null
335	}
336	}
337
338	/**
339	* Method to persist entities into the database
340	* Checks whether entity already exists (based on identifier column 'name')
341	*
342	* @param entity entity object like Study, Subject, Protocol et cetera
343	*
344	*/
345	boolean persistEntity(entity) {
346	println "persisting ${entity}"
347	// if not validated
348	if (entity.validate()) {
349	if (entity.save()) { //.merge?
350	return true
351	}
352	else { // if save was unsuccesful
353	entity.errors.allErrors.each {
354	println it
355	}
356	return false
357	}
358	}
359	else { // if not validated
360	entity.errors.each {
361	println it
362	}
363	return false
364	}
365	}
366
367	/**
368	* This method creates a record (array) containing entities with values
369	*
370	* @param template_id template identifier
371	* @param excelrow POI based Excel row containing the cells
372	* @param mcmap map containing MappingColumn objects
373	*/
374	def createRecord(template_id, HSSFRow excelrow, mcmap) {
375	def df = new DataFormatter()
376	def template = Template.get(template_id)
377	def record = []
378
379	// Initialize all possible entities with the chosen template
380	def study = new Study(template: template)
381	def subject = new Subject(template: template)
382	def samplingEvent = new SamplingEvent(template: template)
383	def event = new Event(template: template)
384	def sample = new Sample(template: template)
385
386	// Go through the Excel row cell by cell
387	for (HSSFCell cell: excelrow) {
388	// get the MappingColumn information of the current cell
389	def mc = mcmap[cell.getColumnIndex()]
390	def value
391
392	// Check if column must be imported
393	if (!mc.dontimport) {
394	try {
395	value = formatValue(df.formatCellValue(cell), mc.templatefieldtype)
396	} catch (NumberFormatException nfe) {
397	value = ""
398	}
399
400	try {
401
402	// which entity does the current cell (field) belong to?
403	switch (mc.entity) {
404	case Study: (record.any {it.getClass() == mc.entity}) ? 0 : record.add(study)
405	study.setFieldValue(mc.property, value)
406	break
407	case Subject: (record.any {it.getClass() == mc.entity}) ? 0 : record.add(subject)
408	subject.setFieldValue(mc.property, value)
409	break
410	case SamplingEvent: (record.any {it.getClass() == mc.entity}) ? 0 : record.add(samplingEvent)
411	samplingEvent.setFieldValue(mc.property, value)
412	break
413	case Event: (record.any {it.getClass() == mc.entity}) ? 0 : record.add(event)
414	event.setFieldValue(mc.property, value)
415	break
416	case Sample: (record.any {it.getClass() == mc.entity}) ? 0 : record.add(sample)
417	sample.setFieldValue(mc.property, value)
418	break
419	case Object: // don't import
420	break
421	} // end switch
422	} catch (IllegalArgumentException iae) {
423	// leave the field empty and let the user choose the ontology manually in a later step
424
425	}
426	} // end
427	} // end for
428
429	return record
430	}
431
432	/**
433	* Method to parse a value conform a specific type
434	* @param value string containing the value
435	* @return object corresponding to the TemplateFieldType
436	*/
437	def formatValue(String value, TemplateFieldType type) throws NumberFormatException {
438	switch (type) {
439	case TemplateFieldType.STRING : return value.trim()
440	case TemplateFieldType.TEXT : return value.trim()
441	case TemplateFieldType.INTEGER : return (int) Double.valueOf(value)
442	case TemplateFieldType.FLOAT : return Float.valueOf(value.replace(",","."));
443	case TemplateFieldType.DOUBLE : return Double.valueOf(value.replace(",","."));
444	case TemplateFieldType.STRINGLIST : return value.trim()
445	case TemplateFieldType.ONTOLOGYTERM : return value.trim()
446	case TemplateFieldType.DATE : return value
447	default : return value
448	}
449	}
450
451	}

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

Download in other formats:

Original Format