Context Navigation

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

Last change on this file since 1621 was 1621, checked in by robert@…, 11 years ago
Fixed some bugs in simple wizard and study view page
Property svn:keywords set to `Rev Author Date`
File size: 38.2 KB

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

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

Download in other formats: