Context Navigation

source: trunk/grails-app/services/dbnp/importer/ImporterService.groovy @ 1367

Last change on this file since 1367 was 1367, checked in by t.w.abma@…, 13 years ago
added dialog (based on study wizard) for more detailed information what goes wrong in validation step
Property svn:keywords set to `Date Author Rev`
File size: 23.0 KB

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: 1367 $
13	* $Author: t.w.abma@umcutrecht.nl $
14	* $Date: 2011-01-12 10:50:56 +0000 (wo, 12 jan 2011) $
15	*/
16
17	package dbnp.importer
18	import org.apache.poi.ss.usermodel.*
19	import org.apache.poi.xssf.usermodel.XSSFCell
20
21	import dbnp.studycapturing.TemplateFieldType
22	import dbnp.studycapturing.Template
23	import dbnp.studycapturing.SamplingEvent
24	import dbnp.studycapturing.Study
25	import dbnp.studycapturing.Subject
26	import dbnp.studycapturing.Event
27	import dbnp.studycapturing.Sample
28
29	class ImporterService {
30	def AuthenticationService
31
32	boolean transactional = true
33
34	/**
35	* @param is input stream representing the (workbook) resource
36	* @return high level representation of the workbook
37	*/
38	Workbook getWorkbook(InputStream is) {
39	WorkbookFactory.create(is)
40	}
41
42	/**
43	* @param wb high level representation of the workbook
44	* @param sheetindex sheet to use within the workbook
45	* @return header representation as a MappingColumn hashmap
46	*/
47	def getHeader(Workbook wb, int sheetindex, int headerrow, int datamatrix_start, theEntity=null) {
48	def sheet = wb.getSheetAt(sheetindex)
49	def sheetrow = sheet.getRow(datamatrix_start)
50	//def header = []
51	def header = [:]
52	def df = new DataFormatter()
53	def property = new String()
54
55	//for (Cell c: sheet.getRow(datamatrix_start)) {
56
57	(0..sheetrow.getLastCellNum() -1 ).each { columnindex ->
58
59	//def index = c.getColumnIndex()
60	def datamatrix_celltype = sheet.getRow(datamatrix_start).getCell(columnindex,Row.CREATE_NULL_AS_BLANK).getCellType()
61	def datamatrix_celldata = df.formatCellValue(sheet.getRow(datamatrix_start).getCell(columnindex))
62	def datamatrix_cell = sheet.getRow(datamatrix_start).getCell(columnindex)
63	def headercell = sheet.getRow(headerrow-1+sheet.getFirstRowNum()).getCell(columnindex)
64	def tft = TemplateFieldType.STRING //default templatefield type
65
66	// Check for every celltype, currently redundant code, but possibly this will be
67	// a piece of custom code for every cell type like specific formatting
68
69	switch (datamatrix_celltype) {
70	case Cell.CELL_TYPE_STRING:
71	//parse cell value as double
72	def doubleBoolean = true
73	def fieldtype = TemplateFieldType.STRING
74
75	// is this string perhaps a double?
76	try {
77	formatValue(datamatrix_celldata, TemplateFieldType.DOUBLE)
78	} catch (NumberFormatException nfe) { doubleBoolean = false }
79	finally {
80	if (doubleBoolean) fieldtype = TemplateFieldType.DOUBLE
81	}
82
83	header[columnindex] = new dbnp.importer.MappingColumn(name:df.formatCellValue(headercell),
84	templatefieldtype:fieldtype,
85	index:columnindex,
86	entity:theEntity,
87	property:property);
88
89	break
90	case Cell.CELL_TYPE_NUMERIC:
91	def fieldtype = TemplateFieldType.LONG
92	def doubleBoolean = true
93	def longBoolean = true
94
95	// is this cell really an integer?
96	try {
97	Long.valueOf(datamatrix_celldata)
98	} catch (NumberFormatException nfe) { longBoolean = false }
99	finally {
100	if (longBoolean) fieldtype = TemplateFieldType.LONG
101	}
102
103	// it's not an long, perhaps a double?
104	if (!longBoolean)
105	try {
106	formatValue(datamatrix_celldata, TemplateFieldType.DOUBLE)
107	} catch (NumberFormatException nfe) { doubleBoolean = false }
108	finally {
109	if (doubleBoolean) fieldtype = TemplateFieldType.DOUBLE
110	}
111
112	if (DateUtil.isCellDateFormatted(datamatrix_cell)) fieldtype = TemplateFieldType.DATE
113
114	header[columnindex] = new dbnp.importer.MappingColumn(name:df.formatCellValue(headercell),
115	templatefieldtype:fieldtype,
116	index:columnindex,
117	entity:theEntity,
118	property:property);
119	break
120	case Cell.CELL_TYPE_BLANK:
121	header[columnindex] = new dbnp.importer.MappingColumn(name:df.formatCellValue(headercell),
122	templatefieldtype:TemplateFieldType.STRING,
123	index:columnindex,
124	entity:theEntity,
125	property:property);
126	break
127	default:
128	header[columnindex] = new dbnp.importer.MappingColumn(name:df.formatCellValue(headercell),
129	templatefieldtype:TemplateFieldType.STRING,
130	index:columnindex,
131	entity:theEntity,
132	property:property);
133	break
134	} // end of switch
135	} // end of cell loop
136	return header
137	}
138
139	/**
140	* This method is meant to return a matrix of the rows and columns
141	* used in the preview
142	*
143	* @param wb workbook object
144	* @param sheetindex sheet index used
145	* @param rows amount of rows returned
146	* @return two dimensional array (matrix) of Cell objects
147	*/
148
149	Object[][] getDatamatrix(Workbook wb, header, int sheetindex, int datamatrix_start, int count) {
150	def sheet = wb.getSheetAt(sheetindex)
151	def rows = []
152	def df = new DataFormatter()
153
154	count = (count < sheet.getLastRowNum()) ? count : sheet.getLastRowNum()
155
156	// walk through all rows
157	((datamatrix_start+sheet.getFirstRowNum())..count).each { rowindex ->
158	def row = []
159
160	(0..header.size()-1).each { columnindex ->
161	def c = sheet.getRow(rowindex).getCell(columnindex, Row.CREATE_NULL_AS_BLANK)
162	row.add(c)
163	}
164
165	rows.add(row)
166	}
167
168	return rows
169	}
170
171	/**
172	* This method will move a file to a new location.
173	*
174	* @param file File object to move
175	* @param folderpath folder to move the file to
176	* @param filename (new) filename to give
177	* @return if file has been moved succesful, the new path and filename will be returned, otherwise an empty string will be returned
178	*/
179	def moveFile(File file, String folderpath, String filename) {
180	try {
181	def rnd = ""; //System.currentTimeMillis()
182	file.transferTo(new File(folderpath, rnd+filename))
183	return folderpath + filename
184	} catch(Exception exception) {
185	log.error "File move error, ${exception}"
186	return ""
187	}
188	}
189
190	/**
191	* @return random numeric value
192	*/
193	def random = {
194	return System.currentTimeMillis() + Runtime.runtime.freeMemory()
195	}
196
197	/**
198	* Method to read data from a workbook and to import data into a two dimensional
199	* array
200	*
201	* @param template_id template identifier to use fields from
202	* @param wb POI horrible spreadsheet formatted workbook object
203	* @param mcmap linked hashmap (preserved order) of MappingColumns
204	* @param sheetindex sheet to use when using multiple sheets
205	* @param rowindex first row to start with reading the actual data (NOT the header)
206	* @return two dimensional array containing records (with entities)
207	*
208	* @see dbnp.importer.MappingColumn
209	*/
210	def importData(template_id, Workbook wb, int sheetindex, int rowindex, mcmap) {
211	def sheet = wb.getSheetAt(sheetindex)
212	def table = []
213	def failedcells = [] // list of records
214
215	// walk through all rows and fill the table with records
216	(rowindex..sheet.getLastRowNum()).each { i ->
217	// Create an entity record based on a row read from Excel and store the cells which failed to be mapped
218	def (record, failed) = createRecord(template_id, sheet.getRow(i), mcmap)
219
220	// Add record with entity and its values to the table
221	table.add(record)
222
223	// If failed cells have been found, add them to the failed cells list
224	if (failed?.importcells?.size()>0) failedcells.add(failed)
225	}
226
227	return [table,failedcells]
228	}
229
230	/** Method to put failed cells back into the datamatrix. Failed cells are cell values
231	* which could not be stored in an entity (e.g. Humu Supiuns in an ontology field).
232	* Empty corrections should not be stored
233	*
234	* @param datamatrix two dimensional array containing entities and possibly also failed cells
235	* @param failedcells list with maps of failed cells in [mappingcolumn, cell] format
236	* @param correctedcells map of corrected cells in [cellhashcode, value] format
237	**/
238	def saveCorrectedCells(datamatrix, failedcells, correctedcells) {
239
240	// Loop through all failed cells (stored as
241	failedcells.each { record ->
242	record.value.importcells.each { cell ->
243
244	// Get the corrected value
245	def correctedvalue = correctedcells.find { it.key.toInteger() == cell.getIdentifier()}.value
246
247	// Find the record in the table which the mappingcolumn belongs to
248	def tablerecord = datamatrix.find { it.hashCode() == record.key }
249
250	// Loop through all entities in the record and correct them if necessary
251	tablerecord.each { rec ->
252	rec.each { entity ->
253	try {
254	// Update the entity field
255	entity.setFieldValue(cell.mappingcolumn.property, correctedvalue)
256	//println "Adjusted " + cell.mappingcolumn.property + " to " + correctedvalue
257	}
258	catch (Exception e) {
259	//println "Could not map corrected ontology: " + cell.mappingcolumn.property + " to " + correctedvalue
260	}
261	}
262	} // end of table record
263	} // end of cell record
264	} // end of failedlist
265	}
266
267	/**
268	* Method to store a matrix containing the entities in a record like structure. Every row in the table
269	* contains one or more entity objects (which contain fields with values). So actually a row represents
270	* a record with fields from one or more different entities.
271	*
272	* @param study entity Study
273	* @param datamatrix two dimensional array containing entities with values read from Excel file
274	*/
275	def saveDatamatrix(Study study, datamatrix) {
276	def validatedSuccesfully = 0
277	def entitystored = null
278	def failedtopersist = []
279	def persisterrors = []
280	def updatedentities = []
281	study.refresh()
282
283	// go through the data matrix, read every record and validate the entity and try to persist it
284	datamatrix.each { record ->
285	record.each { entity ->
286	switch (entity.getClass()) {
287	case Study : log.info "Persisting Study `" + entity + "`: "
288	entity.owner = AuthenticationService.getLoggedInUser()
289	if (persistEntity(entity)) validatedSuccesfully++;
290	else failedtopersist.add(entity)
291	break
292	case Subject : log.info "Persisting Subject `" + entity + "`: "
293
294	// is the current entity not already in the database?
295	entitystored = isEntityStored(entity)
296
297	// this entity is new, so add it to the study
298	if (entitystored==null) study.addToSubjects(entity)
299	else { // existing entity, so update it
300	updateEntity(entitystored, entity)
301	updatedentities.add(entity)
302	}
303
304	if (persistEntity(study)) validatedSuccesfully++;
305	else failedtopersist.add(entity)
306	break
307	case Event : log.info "Persisting Event `" + entity + "`: "
308	study.addToEvents(entity)
309	if (persistEntity(entity)) validatedSuccesfully++;
310	else failedtopersist.add(entity)
311	break
312	case Sample : log.info "Persisting Sample `" + entity +"`: "
313
314	// is this sample validatable (sample name unique for example?)
315	if (entity.validate()) {
316	study.addToSamples(entity)
317	if (persistEntity(study)) validatedSuccesfully++;
318	} else {
319	failedtopersist.add(entity)
320	}
321	break
322	case SamplingEvent: log.info "Persisting SamplingEvent `" + entity + "`: "
323	study.addToSamplingEvents(entity)
324	if (persistEntity(entity)) validatedSuccesfully++;
325	else failedtopersist.add(entity)
326	break
327	default : log.info "Skipping persisting of `" + entity.getclass() +"`"
328	failedtopersist.add(entity)
329	break
330	} // end switch
331	} // end record
332	} // end datamatrix
333	return [validatedSuccesfully, updatedentities, failedtopersist]
334	}
335
336	/**
337	* Check whether an entity already exist. A unique field in the entity is
338	* used to check whether the instantiated entity (read from Excel) is new.
339	* If the entity is found in the database it will be returned as is.
340	*
341	* @param entity entity object like a Study, Subject, Sample et cetera
342	* @return entity if found, otherwise null
343	*/
344	def isEntityStored(entity) {
345	switch (entity.getClass()) {
346	case Study : return Study.findByCode(entity.code)
347	break
348	case Subject : return Subject.findByParentAndName(entity.parent, entity.name)
349	break
350	case Event : break
351	case Sample :
352	break
353	case SamplingEvent : break
354	default : // unknown entity
355	return null
356	}
357	}
358
359	/**
360	* Find the entity and update the fields. The entity is an instance
361	* read from Excel. This method looks in the database for the entity
362	* having the same identifier. If it has found the same entity
363	* already in the database, it will update the record.
364	*
365	* @param entitystored existing record in the database to update
366	* @param entity entity read from Excel
367	*/
368	def updateEntity(entitystored, entity) {
369	switch (entity.getClass()) {
370	case Study : break
371	case Subject : entitystored.properties = entity.properties
372	entitystored.save()
373	break
374	case Event : break
375	case Sample : break
376	case SamplingEvent : break
377	default : // unknown entity
378	return null
379	}
380	}
381
382	/**
383	* Method to persist entities into the database
384	* Checks whether entity already exists (based on identifier column 'name')
385	*
386	* @param entity entity object like Study, Subject, Protocol et cetera
387	*
388	*/
389	boolean persistEntity(entity) {
390	log.info ".import wizard persisting ${entity}"
391
392	if (entity.save(flush:true))
393	return true
394	else { // if save was unsuccesful
395	entity.errors.allErrors.each {
396	log.error ".import wizard: " + it
397	}
398	return false
399	}
400	}
401
402	/**
403	* This method creates a record (array) containing entities with values
404	*
405	* @param template_id template identifier
406	* @param excelrow POI based Excel row containing the cells
407	* @param mcmap map containing MappingColumn objects
408	* @return list of entities and list of failed cells
409	*/
410	def createRecord(template_id, Row excelrow, mcmap) {
411	def df = new DataFormatter()
412	def template = Template.get(template_id)
413	def tft = TemplateFieldType
414	def record = [] // list of entities and the read values
415	def failed = new ImportRecord() // map with entity identifier and failed mappingcolumn
416
417	// Initialize all possible entities with the chosen template
418	def study = new Study(template: template)
419	def subject = new Subject(template: template)
420	def samplingEvent = new SamplingEvent(template: template)
421	def event = new Event(template: template)
422	def sample = new Sample(template: template)
423
424	// Go through the Excel row cell by cell
425	for (Cell cell: excelrow) {
426	// get the MappingColumn information of the current cell
427	def mc = mcmap[cell.getColumnIndex()]
428	def value
429
430	// Check if column must be imported
431	if (mc!=null) if (!mc.dontimport) {
432	try {
433	value = formatValue(df.formatCellValue(cell), mc.templatefieldtype)
434	} catch (NumberFormatException nfe) {
435	value = ""
436	}
437
438	try {
439	// which entity does the current cell (field) belong to?
440	switch (mc.entity) {
441	case Study: // does the entity already exist in the record? If not make it so.
442	(record.any {it.getClass() == mc.entity}) ? 0 : record.add(study)
443	study.setFieldValue(mc.property, value)
444	break
445	case Subject: (record.any {it.getClass() == mc.entity}) ? 0 : record.add(subject)
446	subject.setFieldValue(mc.property, value)
447	break
448	case SamplingEvent: (record.any {it.getClass() == mc.entity}) ? 0 : record.add(samplingEvent)
449	samplingEvent.setFieldValue(mc.property, value)
450	break
451	case Event: (record.any {it.getClass() == mc.entity}) ? 0 : record.add(event)
452	event.setFieldValue(mc.property, value)
453	break
454	case Sample: (record.any {it.getClass() == mc.entity}) ? 0 : record.add(sample)
455	sample.setFieldValue(mc.property, value)
456	break
457	case Object: // don't import
458	break
459	} // end switch
460	} catch (IllegalArgumentException iae) {
461	// store the mapping column and value which failed
462	def identifier
463
464	switch (mc.entity) {
465	case Study: identifier = study.getIdentifier()
466	break
467	case Subject: identifier = subject.getIdentifier()
468	break
469	case SamplingEvent: identifier = samplingEvent.getIdentifier()
470	break
471	case Event: identifier = event.getIdentifier()
472	break
473	case Sample: identifier = sample.getIdentifier()
474	break
475	case Object: // don't import
476	break
477	}
478
479	def mcInstance = new MappingColumn()
480	mcInstance.properties = mc.properties
481	failed.addToImportcells(new ImportCell(mappingcolumn:mcInstance, value:value, entityidentifier:identifier))
482	}
483	} // end
484	} // end for
485	// a failed column means that using the entity.setFieldValue() threw an exception
486	return [record, failed]
487	}
488
489	/**
490	* Method to parse a value conform a specific type
491	* @param value string containing the value
492	* @return object corresponding to the TemplateFieldType
493	*/
494	def formatValue(String value, TemplateFieldType type) throws NumberFormatException {
495	switch (type) {
496	case TemplateFieldType.STRING : return value.trim()
497	case TemplateFieldType.TEXT : return value.trim()
498	case TemplateFieldType.LONG : return (long) Double.valueOf(value)
499	//case TemplateFieldType.FLOAT : return Float.valueOf(value.replace(",","."));
500	case TemplateFieldType.DOUBLE : return Double.valueOf(value.replace(",","."));
501	case TemplateFieldType.STRINGLIST : return value.trim()
502	case TemplateFieldType.ONTOLOGYTERM : return value.trim()
503	case TemplateFieldType.DATE : return value
504	default : return value
505	}
506	}
507
508	// classes for fuzzy string matching
509	// <FUZZY MATCHING>
510	static def similarity(l_seq, r_seq, degree=2) {
511	def l_histo = countNgramFrequency(l_seq, degree)
512	def r_histo = countNgramFrequency(r_seq, degree)
513
514	dotProduct(l_histo, r_histo) /
515	Math.sqrt(dotProduct(l_histo, l_histo) *
516	dotProduct(r_histo, r_histo))
517	}
518
519	static def countNgramFrequency(sequence, degree) {
520	def histo = [:]
521	def items = sequence.size()
522
523	for (int i = 0; i + degree <= items; i++)
524	{
525	def gram = sequence[i..<(i + degree)]
526	histo[gram] = 1 + histo.get(gram, 0)
527	}
528	histo
529	}
530
531	static def dotProduct(l_histo, r_histo) {
532	def sum = 0
533	l_histo.each { key, value ->
534	sum = sum + l_histo[key] * r_histo.get(key, 0)
535	}
536	sum
537	}
538
539	static def stringSimilarity (l_str, r_str, degree=2) {
540
541	similarity(l_str.toString().toLowerCase().toCharArray(),
542	r_str.toString().toLowerCase().toCharArray(),
543	degree)
544	}
545
546	static def mostSimilar(pattern, candidates, threshold=0) {
547	def topScore = 0
548	def bestFit = null
549
550	candidates.each { candidate ->
551	def score = stringSimilarity(pattern, candidate)
552	if (score > topScore) {
553	topScore = score
554	bestFit = candidate
555	}
556	}
557
558	if (topScore < threshold)
559	bestFit = null
560
561	bestFit
562	}
563	// </FUZZY MATCHING>
564
565	}

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

Download in other formats: