Context Navigation

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

Last change on this file since 1608 was 1608, checked in by robert@…, 12 years ago
Fixed bug with hibernate and transactional services in the simple wizard
Property svn:keywords set to `Rev Author Date`
File size: 37.4 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: 1608 $
13	* $Author: robert@isdat.nl $
14	* $Date: 2011-03-09 14:56:26 +0000 (wo, 09 mrt 2011) $
15	*/
16	package dbnp.importer
17
18	import org.dbnp.gdt.*
19	import org.apache.poi.ss.usermodel.*
20	import dbnp.studycapturing.*
21
22	class ImporterService {
23	def authenticationService
24
25	static transactional = false
26
27	/**
28	* @param is input stream representing the (workbook) resource
29	* @return high level representation of the workbook
30	*/
31	Workbook getWorkbook(InputStream is) {
32	WorkbookFactory.create(is)
33	}
34
35	/**
36	* @param wb high level representation of the workbook
37	* @param sheetindex sheet to use within the workbook
38	* @return header representation as a MappingColumn hashmap
39	*/
40	def getHeader(Workbook wb, int sheetindex, int headerrow, int datamatrix_start, theEntity = null) {
41	def sheet = wb.getSheetAt(sheetindex)
42	def sheetrow = sheet.getRow(datamatrix_start)
43	//def header = []
44	def header = []
45	def df = new DataFormatter()
46	def property = new String()
47
48	//for (Cell c: sheet.getRow(datamatrix_start)) {
49
50	(0..sheetrow.getLastCellNum() - 1).each { columnindex ->
51
52	//def index = c.getColumnIndex()
53	def datamatrix_celltype = sheet.getRow(datamatrix_start).getCell(columnindex, Row.CREATE_NULL_AS_BLANK).getCellType()
54	def datamatrix_celldata = df.formatCellValue(sheet.getRow(datamatrix_start).getCell(columnindex))
55	def datamatrix_cell = sheet.getRow(datamatrix_start).getCell(columnindex)
56	def headercell = sheet.getRow(headerrow - 1 + sheet.getFirstRowNum()).getCell(columnindex)
57	def tft = TemplateFieldType.STRING //default templatefield type
58
59	// Check for every celltype, currently redundant code, but possibly this will be
60	// a piece of custom code for every cell type like specific formatting
61
62	switch (datamatrix_celltype) {
63	case Cell.CELL_TYPE_STRING:
64	//parse cell value as double
65	def doubleBoolean = true
66	def fieldtype = TemplateFieldType.STRING
67
68	// is this string perhaps a double?
69	try {
70	formatValue(datamatrix_celldata, TemplateFieldType.DOUBLE)
71	} catch (NumberFormatException nfe) { doubleBoolean = false }
72	finally {
73	if (doubleBoolean) fieldtype = TemplateFieldType.DOUBLE
74	}
75
76	header[columnindex] = new dbnp.importer.MappingColumn(name: df.formatCellValue(headercell),
77	templatefieldtype: fieldtype,
78	index: columnindex,
79	entityclass: theEntity,
80	property: property);
81
82	break
83	case Cell.CELL_TYPE_NUMERIC:
84	def fieldtype = TemplateFieldType.LONG
85	def doubleBoolean = true
86	def longBoolean = true
87
88	// is this cell really an integer?
89	try {
90	Long.valueOf(datamatrix_celldata)
91	} catch (NumberFormatException nfe) { longBoolean = false }
92	finally {
93	if (longBoolean) fieldtype = TemplateFieldType.LONG
94	}
95
96	// it's not an long, perhaps a double?
97	if (!longBoolean)
98	try {
99	formatValue(datamatrix_celldata, TemplateFieldType.DOUBLE)
100	} catch (NumberFormatException nfe) { doubleBoolean = false }
101	finally {
102	if (doubleBoolean) fieldtype = TemplateFieldType.DOUBLE
103	}
104
105	if (DateUtil.isCellDateFormatted(datamatrix_cell)) fieldtype = TemplateFieldType.DATE
106
107	header[columnindex] = new dbnp.importer.MappingColumn(name: df.formatCellValue(headercell),
108	templatefieldtype: fieldtype,
109	index: columnindex,
110	entityclass: theEntity,
111	property: property);
112	break
113	case Cell.CELL_TYPE_BLANK:
114	header[columnindex] = new dbnp.importer.MappingColumn(name: df.formatCellValue(headercell),
115	templatefieldtype: TemplateFieldType.STRING,
116	index: columnindex,
117	entityclass: theEntity,
118	property: property);
119	break
120	default:
121	header[columnindex] = new dbnp.importer.MappingColumn(name: df.formatCellValue(headercell),
122	templatefieldtype: TemplateFieldType.STRING,
123	index: columnindex,
124	entityclass: theEntity,
125	property: property);
126	break
127	} // end of switch
128	} // end of cell loop
129	return header
130	}
131
132	/**
133	* This method is meant to return a matrix of the rows and columns
134	* used in the preview
135	*
136	* @param wb workbook object
137	* @param sheetindex sheet index used
138	* @param rows amount of rows returned
139	* @return two dimensional array (matrix) of Cell objects
140	*/
141	Object[][] getDatamatrix(Workbook wb, header, int sheetindex, int datamatrix_start, int count) {
142	def sheet = wb.getSheetAt(sheetindex)
143	def rows = []
144	def df = new DataFormatter()
145
146	count = (count < sheet.getLastRowNum()) ? count : sheet.getLastRowNum()
147
148	// walk through all rows
149	((datamatrix_start + sheet.getFirstRowNum())..count).each { rowindex ->
150	def row = []
151
152	(0..header.size() - 1).each { columnindex ->
153	if (sheet.getRow(rowindex))
154	row.add( sheet.getRow(rowindex).getCell(columnindex, Row.CREATE_NULL_AS_BLANK) )
155	}
156
157	rows.add(row)
158	}
159
160	return rows
161	}
162
163	/**
164	* This method will move a file to a new location.
165	*
166	* @param file File object to move
167	* @param folderpath folder to move the file to
168	* @param filename (new) filename to give
169	* @return if file has been moved succesful, the new path and filename will be returned, otherwise an empty string will be returned
170	*/
171	def moveFile(File file, String folderpath, String filename) {
172	try {
173	def rnd = ""; //System.currentTimeMillis()
174	file.transferTo(new File(folderpath, rnd + filename))
175	return folderpath + filename
176	} catch (Exception exception) {
177	log.error "File move error, ${exception}"
178	return ""
179	}
180	}
181
182	/**
183	* @return random numeric value
184	*/
185	def random = {
186	return System.currentTimeMillis() + Runtime.runtime.freeMemory()
187	}
188
189
190	/**
191	* Retrieves records with sample, subject, samplingevent etc. from a study
192	* @param s Study to retrieve records from
193	* @return A list with hashmaps [ 'objects': [ 'Sample': .., 'Subject': .., 'SamplingEvent': .., 'Event': '.. ], 'templates': [], 'templateCombination': .. ]
194	*/
195	protected def getRecords( Study s ) {
196	def records = [];
197
198	s.samples?.each {
199	def record = [ 'objects': retrieveEntitiesBySample( it ) ];
200
201	def templates = [:]
202	def templateCombination = [];
203	record.objects.each { entity ->
204	templates[ entity.key ] = entity.value?.template
205	if( entity.value?.template )
206	templateCombination << entity.key + ": " + entity.value?.template?.name;
207	}
208
209	record.templates = templates;
210	record.templateCombination = templateCombination.join( ', ' )
211
212	records << record
213	}
214
215	return records;
216	}
217
218	/**
219	* Returns a subject, event and samplingEvent that belong to this sample
220	* @param s Sample to find the information for
221	* @return
222	*/
223	protected retrieveEntitiesBySample( Sample s ) {
224	return [
225	'Sample': s,
226	'Subject': s?.parentSubject,
227	'SamplingEvent': s?.parentEvent,
228	'Event': s?.parentEventGroup?.events?.getAt(0)
229	]
230	}
231
232	/**
233	* Imports data from a workbook into a list of ImportRecords. If some entities are already in the database,
234	* these records are updated.
235	*
236	* This method is capable of importing Subject, Samples, SamplingEvents and Events
237	*
238	* @param templates Map of templates, identified by their entity as a key. For example: [ Subject: Template x, Sample: Template y ]
239	* @param wb Excel workbook to import
240	* @param sheetindex Number of the sheet to import data from
241	* @param rowindex Row to start importing from.
242	* @param mcmap Hashmap of mappingcolumns, with the first entry in the hashmap containing information about the first column, etc.
243	* @param parent Study to import all data into. Is used for determining which sample/event/subject/assay to update
244	* @param createAllEntities If set to true, the system will also create objects for entities that have no data imported, but do have
245	* a template assigned
246	* @return List List with two entries:
247	* 0 List with ImportRecords, one for each row in the excelsheet
248	* 1 List with ImportCell objects, mentioning the cells that could not be correctly imported
249	* (because the value in the excelsheet can't be entered into the template field)
250	*/
251	def importOrUpdateDataBySampleIdentifier( def templates, Workbook wb, int sheetindex, int rowindex, def mcmap, Study parent = null, boolean createAllEntities = true ) {
252	if( !mcmap )
253	return;
254
255	// Check whether the rows should be imported in one or more entities
256	def entities
257	if( createAllEntities ) {
258	entities = templates.entrySet().value.findAll { it }.entity;
259	} else {
260	entities = mcmap.findAll{ !it.dontimport }.entityclass.unique();
261	}
262
263	def sheet = wb.getSheetAt(sheetindex)
264	def table = []
265	def failedcells = [] // list of cells that have failed to import
266
267	// First check for each record whether an entity in the database should be updated,
268	// or a new entity should be added. This is done before any new object is created, since
269	// searching after new objects have been created (but not yet saved) will result in
270	// org.hibernate.AssertionFailure: collection [...] was not processed by flush()
271	// errors
272	def existingEntities = [:]
273	for( int i = rowindex; i <= sheet.getLastRowNum(); i++ ) {
274	existingEntities[i] = findExistingEntities( entities, sheet.getRow(i), mcmap, parent );
275	}
276
277	// walk through all rows and fill the table with records
278	for( int i = rowindex; i <= sheet.getLastRowNum(); i++ ) {
279	// Create an entity record based on a row read from Excel and store the cells which failed to be mapped
280	def (record, failed) = importOrUpdateRecord( templates, entities, sheet.getRow(i), mcmap, parent, table, existingEntities[i] );
281
282	// Setup the relationships between the imported entities
283	relateEntities( record );
284
285	// Add record with entities and its values to the table
286	table.add(record)
287
288	// If failed cells have been found, add them to the failed cells list
289	if (failed?.importcells?.size() > 0) failedcells.add(failed)
290	}
291
292	return [ "table": table, "failedCells": failedcells ]
293	}
294
295	/**
296	* Checks whether entities in the given row already exist in the database
297	* they are updated.
298	*
299	* @param entities Entities that have to be imported for this row
300	* @param excelRow Excel row to import into this record
301	* @param mcmap Hashmap of mappingcolumns, with the first entry in the hashmap containing information about the first column, etc.
302	* @return Map Map with entities that have been found for this row. The key for the entities is the entity name (e.g.: [Sample: null, Subject: <subject object>]
303	*/
304	def findExistingEntities(def entities, Row excelRow, mcmap, parent ) {
305	DataFormatter df = new DataFormatter();
306
307	// Find entities based on sample identifier
308	def sample = findEntityByRow( dbnp.studycapturing.Sample, excelRow, mcmap, parent, [], df );
309	return retrieveEntitiesBySample( sample );
310	}
311
312	/**
313	* Imports a records from the excelsheet into the database. If the entities are already in the database
314	* they are updated.
315	*
316	* This method is capable of importing Subject, Samples, SamplingEvents and Events
317	*
318	* @param templates Map of templates, identified by their entity as a key. For example: [ Sample: Template y ]
319	* @param entities Entities that have to be imported for this row
320	* @param excelRow Excel row to import into this record
321	* @param mcmap Hashmap of mappingcolumns, with the first entry in the hashmap containing information about the first column, etc.
322	* @param parent Study to import all data into. Is used for determining which sample/event/subject/assay to update
323	* @param importedRows Rows that have been imported before this row. These rows might contain the same entities as are
324	* imported in this row. These entities should be used again, to avoid importing duplicates.
325	* @return List List with two entries:
326	* 0 List with ImportRecords, one for each row in the excelsheet
327	* 1 List with ImportCell objects, mentioning the cells that could not be correctly imported
328	* (because the value in the excelsheet can't be entered into the template field)
329	*/
330	def importOrUpdateRecord(def templates, def entities, Row excelRow, mcmap, Study parent = null, List importedRows, Map existingEntities ) {
331	DataFormatter df = new DataFormatter();
332	def record = [] // list of entities and the read values
333	def failed = new ImportRecord() // map with entity identifier and failed mappingcolumn
334
335	// Check whether this record mentions a sample that has been imported before. In that case,
336	// we update that record, in order to prevent importing the same sample multiple times
337	def importedEntities = [];
338	if( importedRows )
339	importedEntities = importedRows.flatten().findAll { it.class == dbnp.studycapturing.Sample }.unique();
340
341	def importedSample = null // findEntityInImportedEntities( dbnp.studycapturing.Sample, excelRow, mcmap, importedEntities, df )
342	def imported = [] // retrieveEntitiesBySample( importedSample );
343
344	for( entity in entities ) {
345	// Check whether this entity should be added or updated
346	// The entity is updated is an entity with the same 'identifier' (field
347	// specified to be the identifying field) is found in the database
348	def entityName = entity.name[ entity.name.lastIndexOf( '.' ) + 1..-1];
349	def template = templates[ entityName ];
350
351	// If no template is specified for this entity, continue with the next
352	if( !template )
353	continue;
354
355	// Check whether the object exists in the list of already imported entities
356	def entityObject = imported[ entityName ]
357
358	// If it doesn't, search for the entity in the database
359	if( !entityObject && existingEntities )
360	entityObject = existingEntities[ entityName ];
361
362	// Otherwise, create a new object
363	if( !entityObject )
364	entityObject = entity.newInstance();
365
366	// Update the template
367	entityObject.template = template;
368
369	// Go through the Excel row cell by cell
370	for (Cell cell: excelRow) {
371	// get the MappingColumn information of the current cell
372	def mc = mcmap[cell.getColumnIndex()]
373	def value
374
375	// Check if column must be imported
376	if (mc != null && !mc.dontimport && mc.entityclass == entity) {
377	try {
378	value = formatValue(df.formatCellValue(cell), mc.templatefieldtype)
379	} catch (NumberFormatException nfe) {
380	value = ""
381	}
382
383	try {
384	entityObject.setFieldValue(mc.property, value)
385	} catch (Exception iae) {
386	log.error ".import wizard error could not set property `" + mc.property + "` to value `" + value + "`"
387
388	// store the mapping column and value which failed
389	def identifier = entityName.toLowerCase() + "_" + entityObject.getIdentifier() + "_" + mc.property
390
391	def mcInstance = new MappingColumn()
392	mcInstance.properties = mc.properties
393	failed.addToImportcells(new ImportCell(mappingcolumn: mcInstance, value: value, entityidentifier: identifier))
394	}
395	} // end if
396	} // end for
397
398	// If a Study is entered, use it as a 'parent' for other entities
399	if( entity == Study )
400	parent = entityObject;
401
402	record << entityObject;
403	}
404
405	// a failed column means that using the entity.setFieldValue() threw an exception
406	return [record, failed]
407	}
408
409	/**
410	* Looks into the database to find an object of the given entity that should be updated, given the excel row.
411	* This is done by looking at the 'preferredIdentifier' field of the object. If it exists in the row, and the
412	* value is already in the database for that field, an existing object is returned. Otherwise, null is returned
413	*
414	* @param entity Entity to search
415	* @param excelRow Excelrow to search for
416	* @param mcmap Map with MappingColumns
417	* @param parent Parent study for the entity (if applicable). The returned entity will also have this parent
418	* @param importedRows List of entities that have been imported before. The function will first look through this list to find
419	* a matching entity.
420	* @return An entity that has the same identifier as entered in the excelRow. The entity is first sought in the importedRows. If it
421	* is not found there, the database is queried. If no entity is found at all, null is returned.
422	*/
423	def findEntityByRow( Class entity, Row excelRow, def mcmap, Study parent = null, List importedEntities = [], DataFormatter df = null ) {
424	if( df == null )
425	df = new DataFormatter();
426
427	def identifierField = givePreferredIdentifier( entity );
428
429	if( identifierField ) {
430	// Check whether the identifierField is chosen in the column matching
431	def identifierColumn = mcmap.find { it.entityclass == entity && it.property == identifierField.name };
432
433	// If it is, find the identifier and look it up in the database
434	if( identifierColumn ) {
435	def identifierCell = excelRow.getCell( identifierColumn.index );
436	def identifier;
437	try {
438	identifier = formatValue(df.formatCellValue(identifierCell), identifierColumn.templatefieldtype)
439	} catch (NumberFormatException nfe) {
440	identifier = null
441	}
442
443	// Search for an existing object with the same identifier.
444	if( identifier ) {
445	// First search the already imported rows
446	if( importedEntities ) {
447	def imported = importedEntities.find { it.getFieldValue( identifierField.name ) == identifier };
448	if( imported )
449	return imported;
450	}
451
452	def c = entity.createCriteria();
453
454	// If the entity has a field 'parent', the search should be limited to
455	// objects with the same parent. The method entity.hasProperty( "parent" ) doesn't
456	// work, since the java.lang.Class entity doesn't know of the parent property.
457	if( entity.belongsTo?.containsKey( "parent" ) ) {
458	// If the entity requires a parent, but none is given, no
459	// results are given from the database. This prevents the user
460	// of changing data in another study
461	if( parent && parent.id ) {
462	println "Searching (with parent ) for " + entity.name + " with " + identifierField.name + " = " + identifier
463	return c.get {
464	eq( identifierField.name, identifier )
465	eq( "parent", parent )
466	}
467	}
468	} else {
469	println "Searching (without parent ) for " + entity.name + " with " + identifierField.name + " = " + identifier
470	return c.get {
471	eq( identifierField.name, identifier )
472	}
473	}
474	}
475	}
476	}
477
478	// No object is found
479	return null;
480	}
481
482	/**
483	* Looks into the list of already imported entities to find an object of the given entity that should be
484	* updated, given the excel row. This is done by looking at the 'preferredIdentifier' field of the object.
485	* If it exists in the row, and the list of imported entities contains an object with the same
486	* identifier, the existing object is returned. Otherwise, null is returned
487	*
488	* @param entity Entity to search
489	* @param excelRow Excelrow to search for
490	* @param mcmap Map with MappingColumns
491	* @param importedRows List of entities that have been imported before. The function will first look through this list to find
492	* a matching entity.
493	* @return An entity that has the same identifier as entered in the excelRow. The entity is first sought in the importedRows. If it
494	* is not found there, the database is queried. If no entity is found at all, null is returned.
495	*/
496	def findEntityInImportedEntities( Class entity, Row excelRow, def mcmap, List importedEntities = [], DataFormatter df = null ) {
497	if( df == null )
498	df = new DataFormatter();
499
500	def allFields = entity.giveDomainFields();
501	def identifierField = allFields.find { it.preferredIdentifier }
502
503	if( identifierField ) {
504	// Check whether the identifierField is chosen in the column matching
505	def identifierColumn = mcmap.find { it.entityclass == entity && it.property == identifierField.name };
506
507	// If it is, find the identifier and look it up in the database
508	if( identifierColumn ) {
509	def identifierCell = excelRow.getCell( identifierColumn.index );
510	def identifier;
511	try {
512	identifier = formatValue(df.formatCellValue(identifierCell), identifierColumn.templatefieldtype)
513	} catch (NumberFormatException nfe) {
514	identifier = null
515	}
516
517	// Search for an existing object with the same identifier.
518	if( identifier ) {
519	// First search the already imported rows
520	if( importedEntities ) {
521	def imported = importedEntities.find {
522	def fieldValue = it.getFieldValue( identifierField.name )
523
524	if( fieldValue instanceof String )
525	return fieldValue.toLowerCase() == identifier.toLowerCase();
526	else
527	return fieldValue == identifier
528
529	};
530	if( imported )
531	return imported;
532	}
533	}
534	}
535	}
536
537	// No object is found
538	return null;
539	}
540
541
542	/**
543	* Creates relation between multiple entities that have been imported. The entities are
544	* all created from one row in the excel sheet.
545	*/
546	def relateEntities( List entities) {
547	def study = entities.find { it instanceof Study }
548	def subject = entities.find { it instanceof Subject }
549	def sample = entities.find { it instanceof Sample }
550	def event = entities.find { it instanceof Event }
551	def samplingEvent = entities.find { it instanceof SamplingEvent }
552	def assay = entities.find { it instanceof Assay }
553
554	// A study object is found in the entity list
555	if( study ) {
556	if( subject ) {
557	subject.parent = study;
558	study.addToSubjects( subject );
559	}
560	if( sample ) {
561	sample.parent = study
562	study.addToSamples( sample );
563	}
564	if( event ) {
565	event.parent = study
566	study.addToEvents( event );
567	}
568	if( samplingEvent ) {
569	samplingEvent.parent = study
570	study.addToSamplingEvents( samplingEvent );
571	}
572	if( assay ) {
573	assay.parent = study;
574	study.addToAssays( assay );
575	}
576	}
577
578	if( sample ) {
579	if( subject ) sample.parentSubject = subject
580	if( samplingEvent ) sample.parentEvent = samplingEvent;
581	if( event ) {
582	def evGroup = new EventGroup();
583	evGroup.addToEvents( event );
584	if( subject ) evGroup.addToSubjects( subject );
585	if( samplingEvent ) evGroup.addToSamplingEvents( samplingEvent );
586
587	sample.parentEventGroup = evGroup;
588	}
589
590	if( assay ) assay.addToSamples( sample );
591	}
592	}
593
594	/**
595	* Method to read data from a workbook and to import data into a two dimensional
596	* array
597	*
598	* @param template_id template identifier to use fields from
599	* @param wb POI horrible spreadsheet formatted workbook object
600	* @param mcmap linked hashmap (preserved order) of MappingColumns
601	* @param sheetindex sheet to use when using multiple sheets
602	* @param rowindex first row to start with reading the actual data (NOT the header)
603	* @return two dimensional array containing records (with entities)
604	*
605	* @see dbnp.importer.MappingColumn
606	*/
607	def importData(template_id, Workbook wb, int sheetindex, int rowindex, mcmap) {
608	def sheet = wb.getSheetAt(sheetindex)
609	def template = Template.get(template_id)
610	def table = []
611	def failedcells = [] // list of records
612
613	// walk through all rows and fill the table with records
614	(rowindex..sheet.getLastRowNum()).each { i ->
615	// Create an entity record based on a row read from Excel and store the cells which failed to be mapped
616	def (record, failed) = createRecord(template, sheet.getRow(i), mcmap)
617
618	// Add record with entity and its values to the table
619	table.add(record)
620
621	// If failed cells have been found, add them to the failed cells list
622	if (failed?.importcells?.size() > 0) failedcells.add(failed)
623	}
624
625	return [table, failedcells]
626	}
627
628	/**
629	* Removes a cell from the failedCells list, based on the entity and field. If the entity and field didn't fail before
630	* the method doesn't do anything.
631	*
632	* @param failedcell list of cells that have failed previously
633	* @param entity entity to remove from the failedcells list
634	* @param field field to remove the failed cell for. If no field is given, all cells for this entity will be removed
635	* @return List Updated list of cells that have failed
636	*/
637	def removeFailedCell(failedcells, entity, field = null ) {
638	if( !entity )
639	return failedcells;
640
641	def filterClosure
642	if( field ) {
643	def entityIdField = "entity_" + entity.getIdentifier() + "_" + field.name.toLowerCase()
644	filterClosure = { cell -> cell.entityidentifier != entityIdField }
645	} else {
646	def entityIdField = "entity_" + entity.getIdentifier() + "_"
647	filterClosure = { cell -> !cell.entityidentifier.startsWith( entityIdField ) }
648	}
649
650	failedcells.each { record ->
651	record.importcells = record.importcells.findAll( filterClosure )
652	}
653
654	return failedcells;
655	}
656
657	/**
658	* Returns the name of an input field as it is used for a specific entity in HTML.
659	*
660	* @param entity entity to retrieve the field name for
661	* @param field field to retrieve the field name for
662	* @return String Name of the HTML field for the given entity and field. Can also be used in the map
663	* of request parameters
664	*/
665	def getFieldNameInTableEditor(entity, field) {
666	def entityName = entity?.class.name[ entity?.class.name.lastIndexOf(".") + 1..-1]
667
668	if( field instanceof TemplateField )
669	field = field.escapedName();
670
671	return entityName.toLowerCase() + "_" + entity.getIdentifier() + "_" + field
672	}
673
674	/**
675	* Retrieves a mapping column from a list based on the given fieldname
676	* @param mappingColumns List of mapping columns
677	* @param fieldName Field name to find
678	* @return Mapping column if a column is found, null otherwise
679	*/
680	def findMappingColumn( mappingColumns, String fieldName ) {
681	return mappingColumns.find { it.property == fieldName.toLowerCase() }
682	}
683
684	/** Method to put failed cells back into the datamatrix. Failed cells are cell values
685	* which could not be stored in an entity (e.g. Humu Supiuns in an ontology field).
686	* Empty corrections should not be stored
687	*
688	* @param datamatrix two dimensional array containing entities and possibly also failed cells
689	* @param failedcells list with maps of failed cells in [mappingcolumn, cell] format
690	* @param correctedcells map of corrected cells in [cellhashcode, value] format
691	* */
692	def saveCorrectedCells(datamatrix, failedcells, correctedcells) {
693
694	// Loop through all failed cells (stored as
695	failedcells.each { record ->
696	record.value.importcells.each { cell ->
697
698	// Get the corrected value
699	def correctedvalue = correctedcells.find { it.key.toInteger() == cell.getIdentifier()}.value
700
701	// Find the record in the table which the mappingcolumn belongs to
702	def tablerecord = datamatrix.find { it.hashCode() == record.key }
703
704	// Loop through all entities in the record and correct them if necessary
705	tablerecord.each { rec ->
706	rec.each { entity ->
707	try {
708	// Update the entity field
709	entity.setFieldValue(cell.mappingcolumn.property, correctedvalue)
710	//log.info "Adjusted " + cell.mappingcolumn.property + " to " + correctedvalue
711	}
712	catch (Exception e) {
713	//log.info "Could not map corrected ontology: " + cell.mappingcolumn.property + " to " + correctedvalue
714	}
715	}
716	} // end of table record
717	} // end of cell record
718	} // end of failedlist
719	}
720
721	/**
722	* Method to store a matrix containing the entities in a record like structure. Every row in the table
723	* contains one or more entity objects (which contain fields with values). So actually a row represents
724	* a record with fields from one or more different entities.
725	*
726	* @param study entity Study
727	* @param datamatrix two dimensional array containing entities with values read from Excel file
728	*/
729	static saveDatamatrix(Study study, importerEntityType, datamatrix, authenticationService, log) {
730	def validatedSuccesfully = 0
731	def entitystored = null
732
733	// Study passed? Sync data
734	if (study != null && importerEntityType != 'Study') study.refresh()
735
736	// go through the data matrix, read every record and validate the entity and try to persist it
737	datamatrix.each { record ->
738	record.each { entity ->
739	switch (entity.getClass()) {
740	case Study: log.info ".importer wizard, persisting Study `" + entity + "`: "
741	entity.owner = authenticationService.getLoggedInUser()
742
743	if (entity.validate()) {
744	if (!entity.save(flush:true)) {
745	log.error ".importer wizard, study could not be saved: " + entity
746	throw new Exception('.importer wizard, study could not be saved: ' + entity)
747	}
748	} else {
749	log.error ".importer wizard, study could not be validated: " + entity
750	throw new Exception('.importer wizard, study could not be validated: ' + entity)
751	}
752
753	break
754	case Subject: log.info ".importer wizard, persisting Subject `" + entity + "`: "
755
756	// is the current entity not already in the database?
757	//entitystored = isEntityStored(entity)
758
759	// this entity is new, so add it to the study
760	//if (entitystored==null)
761
762	study.addToSubjects(entity)
763
764	break
765	case Event: log.info ".importer wizard, persisting Event `" + entity + "`: "
766	study.addToEvents(entity)
767	break
768	case Sample: log.info ".importer wizard, persisting Sample `" + entity + "`: "
769
770	// is this sample validatable (sample name unique for example?)
771	study.addToSamples(entity)
772
773	break
774	case SamplingEvent: log.info ".importer wizard, persisting SamplingEvent `" + entity + "`: "
775	study.addToSamplingEvents(entity)
776	break
777	default: log.info ".importer wizard, skipping persisting of `" + entity.getclass() + "`"
778	break
779	} // end switch
780	} // end record
781	} // end datamatrix
782
783	// validate study
784	if (importerEntityType != 'Study') {
785	if (study.validate()) {
786	if (!study.save(flush: true)) {
787	//this.appendErrors(flow.study, flash.wizardErrors)
788	throw new Exception('.importer wizard [saveDatamatrix] error while saving study')
789	}
790	} else {
791	throw new Exception('.importer wizard [saveDatamatrix] study does not validate')
792	}
793	}
794
795	//persistEntity(study)
796
797	//return [validatedSuccesfully, updatedentities, failedtopersist]
798	//return [0,0,0]
799	return true
800	}
801
802	/**
803	* Check whether an entity already exist. A unique field in the entity is
804	* used to check whether the instantiated entity (read from Excel) is new.
805	* If the entity is found in the database it will be returned as is.
806	*
807	* @param entity entity object like a Study, Subject, Sample et cetera
808	* @return entity if found, otherwise null
809	*/
810	def isEntityStored(entity) {
811	switch (entity.getClass()) {
812	case Study: return Study.findByCode(entity.code)
813	break
814	case Subject: return Subject.findByParentAndName(entity.parent, entity.name)
815	break
816	case Event: break
817	case Sample:
818	break
819	case SamplingEvent: break
820	default: // unknown entity
821	return null
822	}
823	}
824
825	/**
826	* Find the entity and update the fields. The entity is an instance
827	* read from Excel. This method looks in the database for the entity
828	* having the same identifier. If it has found the same entity
829	* already in the database, it will update the record.
830	*
831	* @param entitystored existing record in the database to update
832	* @param entity entity read from Excel
833	*/
834	def updateEntity(entitystored, entity) {
835	switch (entity.getClass()) {
836	case Study: break
837	case Subject: entitystored.properties = entity.properties
838	entitystored.save()
839	break
840	case Event: break
841	case Sample: break
842	case SamplingEvent: break
843	default: // unknown entity
844	return null
845	}
846	}
847
848	/**
849	* Method to persist entities into the database
850	* Checks whether entity already exists (based on identifier column 'name')
851	*
852	* @param entity entity object like Study, Subject, Protocol et cetera
853	*
854	*/
855	boolean persistEntity(entity) {
856	/*log.info ".import wizard persisting ${entity}"
857	try {
858	entity.save(flush: true)
859	return true
860	} catch (Exception e) {
861	def session = sessionFactory.currentSession
862	session.setFlushMode(org.hibernate.FlushMode.MANUAL)
863	log.error ".import wizard, failed to save entity:\n" + org.apache.commons.lang.exception.ExceptionUtils.getRootCauseMessage(e)
864	}
865	return true*/
866	//println "persistEntity"
867	}
868
869	/**
870	* This method creates a record (array) containing entities with values
871	*
872	* @param template_id template identifier
873	* @param excelrow POI based Excel row containing the cells
874	* @param mcmap map containing MappingColumn objects
875	* @return list of entities and list of failed cells
876	*/
877	def createRecord(template, Row excelrow, mcmap) {
878	def df = new DataFormatter()
879	def tft = TemplateFieldType
880	def record = [] // list of entities and the read values
881	def failed = new ImportRecord() // map with entity identifier and failed mappingcolumn
882
883	// Initialize all possible entities with the chosen template
884	def study = new Study(template: template)
885	def subject = new Subject(template: template)
886	def samplingEvent = new SamplingEvent(template: template)
887	def event = new Event(template: template)
888	def sample = new Sample(template: template)
889
890	// Go through the Excel row cell by cell
891	for (Cell cell: excelrow) {
892	// get the MappingColumn information of the current cell
893	def mc = mcmap[cell.getColumnIndex()]
894	def value
895
896	// Check if column must be imported
897	if (mc != null) if (!mc.dontimport) {
898	try {
899	value = formatValue(df.formatCellValue(cell), mc.templatefieldtype)
900	} catch (NumberFormatException nfe) {
901	value = ""
902	}
903
904	try {
905	// which entity does the current cell (field) belong to?
906	switch (mc.entityclass) {
907	case Study: // does the entity already exist in the record? If not make it so.
908	(record.any {it.getClass() == mc.entityclass}) ? 0 : record.add(study)
909	study.setFieldValue(mc.property, value)
910	break
911	case Subject: (record.any {it.getClass() == mc.entityclass}) ? 0 : record.add(subject)
912	subject.setFieldValue(mc.property, value)
913	break
914	case SamplingEvent: (record.any {it.getClass() == mc.entityclass}) ? 0 : record.add(samplingEvent)
915	samplingEvent.setFieldValue(mc.property, value)
916	break
917	case Event: (record.any {it.getClass() == mc.entityclass}) ? 0 : record.add(event)
918	event.setFieldValue(mc.property, value)
919	break
920	case Sample: (record.any {it.getClass() == mc.entityclass}) ? 0 : record.add(sample)
921	sample.setFieldValue(mc.property, value)
922	break
923	case Object: // don't import
924	break
925	} // end switch
926	} catch (Exception iae) {
927	log.error ".import wizard error could not set property `" + mc.property + "` to value `" + value + "`"
928	// store the mapping column and value which failed
929	def identifier
930
931	switch (mc.entityclass) {
932	case Study: identifier = "entity_" + study.getIdentifier() + "_" + mc.property
933	break
934	case Subject: identifier = "entity_" + subject.getIdentifier() + "_" + mc.property
935	break
936	case SamplingEvent: identifier = "entity_" + samplingEvent.getIdentifier() + "_" + mc.property
937	break
938	case Event: identifier = "entity_" + event.getIdentifier() + "_" + mc.property
939	break
940	case Sample: identifier = "entity_" + sample.getIdentifier() + "_" + mc.property
941	break
942	case Object: // don't import
943	break
944	}
945
946	def mcInstance = new MappingColumn()
947	mcInstance.properties = mc.properties
948	failed.addToImportcells(new ImportCell(mappingcolumn: mcInstance, value: value, entityidentifier: identifier))
949	}
950	} // end
951	} // end for
952	// a failed column means that using the entity.setFieldValue() threw an exception
953	return [record, failed]
954	}
955
956	/**
957	* Method to parse a value conform a specific type
958	* @param value string containing the value
959	* @return object corresponding to the TemplateFieldType
960	*/
961	def formatValue(String value, TemplateFieldType type) throws NumberFormatException {
962	switch (type) {
963	case TemplateFieldType.STRING: return value.trim()
964	case TemplateFieldType.TEXT: return value.trim()
965	case TemplateFieldType.LONG: return (long) Double.valueOf(value)
966	//case TemplateFieldType.FLOAT : return Float.valueOf(value.replace(",","."));
967	case TemplateFieldType.DOUBLE: return Double.valueOf(value.replace(",", "."));
968	case TemplateFieldType.STRINGLIST: return value.trim()
969	case TemplateFieldType.ONTOLOGYTERM: return value.trim()
970	case TemplateFieldType.DATE: return value
971	default: return value
972	}
973	}
974
975	/**
976	* Returns the preferred identifier field for a given entity or
977	* null if no preferred identifier is given
978	* @param entity TemplateEntity class
979	* @return The preferred identifier field or NULL if no preferred identifier is given
980	*/
981	public TemplateField givePreferredIdentifier( Class entity ) {
982	def allFields = entity.giveDomainFields();
983	return allFields.find { it.preferredIdentifier }
984	}
985
986	// classes for fuzzy string matching
987	// <FUZZY MATCHING>
988
989	static def similarity(l_seq, r_seq, degree = 2) {
990	def l_histo = countNgramFrequency(l_seq, degree)
991	def r_histo = countNgramFrequency(r_seq, degree)
992
993	dotProduct(l_histo, r_histo) /
994	Math.sqrt(dotProduct(l_histo, l_histo) *
995	dotProduct(r_histo, r_histo))
996	}
997
998	static def countNgramFrequency(sequence, degree) {
999	def histo = [:]
1000	def items = sequence.size()
1001
1002	for (int i = 0; i + degree <= items; i++) {
1003	def gram = sequence[i..<(i + degree)]
1004	histo[gram] = 1 + histo.get(gram, 0)
1005	}
1006	histo
1007	}
1008
1009	static def dotProduct(l_histo, r_histo) {
1010	def sum = 0
1011	l_histo.each { key, value ->
1012	sum = sum + l_histo[key] * r_histo.get(key, 0)
1013	}
1014	sum
1015	}
1016
1017	static def stringSimilarity(l_str, r_str, degree = 2) {
1018
1019	similarity(l_str.toString().toLowerCase().toCharArray(),
1020	r_str.toString().toLowerCase().toCharArray(),
1021	degree)
1022	}
1023
1024	static def mostSimilar(pattern, candidates, threshold = 0) {
1025	def topScore = 0
1026	def bestFit = null
1027
1028	candidates.each { candidate ->
1029	def score = stringSimilarity(pattern, candidate)
1030	if (score > topScore) {
1031	topScore = score
1032	bestFit = candidate
1033	}
1034	}
1035
1036	if (topScore < threshold)
1037	bestFit = null
1038
1039	bestFit
1040	}
1041	// </FUZZY MATCHING>
1042
1043	}

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

Download in other formats: