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@…, 11 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

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: 1608 $
	13	* $Author: robert@isdat.nl $
	14	* $Date: 2011-03-09 14:56:26 +0000 (wo, 09 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)
	71	} catch (NumberFormatException nfe) { doubleBoolean = false }
	72	finally {
	73	if (doubleBoolean) fieldtype = TemplateFieldType.DOUBLE
	74	}
[545]	75
[1417]	76	header[columnindex] = new dbnp.importer.MappingColumn(name: df.formatCellValue(headercell),
[1553]	77	templatefieldtype: fieldtype,
	78	index: columnindex,
	79	entityclass: theEntity,
	80	property: property);
[634]	81
[1417]	82	break
	83	case Cell.CELL_TYPE_NUMERIC:
	84	def fieldtype = TemplateFieldType.LONG
	85	def doubleBoolean = true
	86	def longBoolean = true
[634]	87
[1553]	88	// is this cell really an integer?
[1417]	89	try {
	90	Long.valueOf(datamatrix_celldata)
	91	} catch (NumberFormatException nfe) { longBoolean = false }
	92	finally {
	93	if (longBoolean) fieldtype = TemplateFieldType.LONG
	94	}
[634]	95
[1553]	96	// it's not an long, perhaps a double?
[1417]	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	}
[706]	104
[1417]	105	if (DateUtil.isCellDateFormatted(datamatrix_cell)) fieldtype = TemplateFieldType.DATE
[147]	106
[1417]	107	header[columnindex] = new dbnp.importer.MappingColumn(name: df.formatCellValue(headercell),
[1553]	108	templatefieldtype: fieldtype,
	109	index: columnindex,
	110	entityclass: theEntity,
	111	property: property);
[1417]	112	break
	113	case Cell.CELL_TYPE_BLANK:
	114	header[columnindex] = new dbnp.importer.MappingColumn(name: df.formatCellValue(headercell),
[1553]	115	templatefieldtype: TemplateFieldType.STRING,
	116	index: columnindex,
	117	entityclass: theEntity,
	118	property: property);
[1417]	119	break
	120	default:
	121	header[columnindex] = new dbnp.importer.MappingColumn(name: df.formatCellValue(headercell),
[1553]	122	templatefieldtype: TemplateFieldType.STRING,
	123	index: columnindex,
	124	entityclass: theEntity,
	125	property: property);
[1417]	126	break
	127	} // end of switch
	128	} // end of cell loop
	129	return header
	130	}
[169]	131
[1417]	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()
[169]	145
[1417]	146	count = (count < sheet.getLastRowNum()) ? count : sheet.getLastRowNum()
[1122]	147
[1417]	148	// walk through all rows
	149	((datamatrix_start + sheet.getFirstRowNum())..count).each { rowindex ->
	150	def row = []
[169]	151
[1417]	152	(0..header.size() - 1).each { columnindex ->
[1553]	153	if (sheet.getRow(rowindex))
	154	row.add( sheet.getRow(rowindex).getCell(columnindex, Row.CREATE_NULL_AS_BLANK) )
[1417]	155	}
	156
	157	rows.add(row)
	158	}
	159
	160	return rows
[1122]	161	}
[169]	162
[1417]	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	}
[169]	181
[1417]	182	/**
	183	* @return random numeric value
	184	*/
	185	def random = {
	186	return System.currentTimeMillis() + Runtime.runtime.freeMemory()
	187	}
	188
[1591]	189
[1417]	190	/**
[1591]	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
[1608]	341	def importedSample = null // findEntityInImportedEntities( dbnp.studycapturing.Sample, excelRow, mcmap, importedEntities, df )
	342	def imported = [] // retrieveEntitiesBySample( importedSample );
[1591]	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	/**
[1417]	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
[1553]	612
[1417]	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)
[147]	623	}
	624
[1417]	625	return [table, failedcells]
[147]	626	}
[231]	627
[1553]	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) {
[1591]	666	def entityName = entity?.class.name[ entity?.class.name.lastIndexOf(".") + 1..-1]
	667
[1553]	668	if( field instanceof TemplateField )
	669	field = field.escapedName();
	670
[1591]	671	return entityName.toLowerCase() + "_" + entity.getIdentifier() + "_" + field
[1553]	672	}
	673
	674	/**
	675	* Retrieves a mapping column from a list based on the given fieldname
	676	* @param mappingColumns List of mapping columns
[1608]	677	* @param fieldName Field name to find
	678	* @return Mapping column if a column is found, null otherwise
[1553]	679	*/
	680	def findMappingColumn( mappingColumns, String fieldName ) {
	681	return mappingColumns.find { it.property == fieldName.toLowerCase() }
	682	}
	683
[1417]	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) {
[1093]	693
[1417]	694	// Loop through all failed cells (stored as
	695	failedcells.each { record ->
	696	record.value.importcells.each { cell ->
[1093]	697
[1417]	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)
[1422]	710	//log.info "Adjusted " + cell.mappingcolumn.property + " to " + correctedvalue
[1417]	711	}
	712	catch (Exception e) {
[1422]	713	//log.info "Could not map corrected ontology: " + cell.mappingcolumn.property + " to " + correctedvalue
[1417]	714	}
	715	}
	716	} // end of table record
	717	} // end of cell record
	718	} // end of failedlist
[274]	719	}
[1050]	720
[1417]	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	*/
[1603]	729	static saveDatamatrix(Study study, importerEntityType, datamatrix, authenticationService, log) {
[1417]	730	def validatedSuccesfully = 0
	731	def entitystored = null
[1083]	732
[1417]	733	// Study passed? Sync data
[1603]	734	if (study != null && importerEntityType != 'Study') study.refresh()
[1103]	735
[1417]	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()) {
[1469]	740	case Study: log.info ".importer wizard, persisting Study `" + entity + "`: "
[1421]	741	entity.owner = authenticationService.getLoggedInUser()
[1083]	742
[1603]	743	if (entity.validate()) {
[1553]	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
[1469]	754	case Subject: log.info ".importer wizard, persisting Subject `" + entity + "`: "
	755
[1553]	756	// is the current entity not already in the database?
	757	//entitystored = isEntityStored(entity)
[1103]	758
[1553]	759	// this entity is new, so add it to the study
	760	//if (entitystored==null)
[1411]	761
[1417]	762	study.addToSubjects(entity)
[1277]	763
[1417]	764	break
[1469]	765	case Event: log.info ".importer wizard, persisting Event `" + entity + "`: "
[1417]	766	study.addToEvents(entity)
	767	break
[1469]	768	case Sample: log.info ".importer wizard, persisting Sample `" + entity + "`: "
[1411]	769
[1553]	770	// is this sample validatable (sample name unique for example?)
[1417]	771	study.addToSamples(entity)
[1411]	772
[1417]	773	break
[1469]	774	case SamplingEvent: log.info ".importer wizard, persisting SamplingEvent `" + entity + "`: "
[1417]	775	study.addToSamplingEvents(entity)
	776	break
[1469]	777	default: log.info ".importer wizard, skipping persisting of `" + entity.getclass() + "`"
[1417]	778	break
	779	} // end switch
	780	} // end record
	781	} // end datamatrix
[284]	782
[1417]	783	// validate study
[1603]	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')
[1417]	792	}
	793	}
[1002]	794
[1417]	795	//persistEntity(study)
[1002]	796
[1417]	797	//return [validatedSuccesfully, updatedentities, failedtopersist]
	798	//return [0,0,0]
	799	return true
	800	}
[1411]	801
[1417]	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	}
[1411]	824
[1417]	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	}
[1411]	847
[1417]	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) {
[1469]	856	/*log.info ".import wizard persisting ${entity}"
[1553]	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"
[1417]	867	}
	868
[725]	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
[1417]	875	* @return list of entities and list of failed cells
[725]	876	*/
[1411]	877	def createRecord(template, Row excelrow, mcmap) {
[1417]	878	def df = new DataFormatter()
	879	def tft = TemplateFieldType
[1093]	880	def record = [] // list of entities and the read values
[1417]	881	def failed = new ImportRecord() // map with entity identifier and failed mappingcolumn
[273]	882
[735]	883	// Initialize all possible entities with the chosen template
[725]	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)
[273]	889
[735]	890	// Go through the Excel row cell by cell
[1087]	891	for (Cell cell: excelrow) {
[735]	892	// get the MappingColumn information of the current cell
[725]	893	def mc = mcmap[cell.getColumnIndex()]
[1417]	894	def value
[666]	895
[725]	896	// Check if column must be imported
[1417]	897	if (mc != null) if (!mc.dontimport) {
[725]	898	try {
	899	value = formatValue(df.formatCellValue(cell), mc.templatefieldtype)
	900	} catch (NumberFormatException nfe) {
	901	value = ""
	902	}
[274]	903
[1553]	904	try {
[1417]	905	// which entity does the current cell (field) belong to?
[1527]	906	switch (mc.entityclass) {
[1417]	907	case Study: // does the entity already exist in the record? If not make it so.
[1527]	908	(record.any {it.getClass() == mc.entityclass}) ? 0 : record.add(study)
[1417]	909	study.setFieldValue(mc.property, value)
	910	break
[1527]	911	case Subject: (record.any {it.getClass() == mc.entityclass}) ? 0 : record.add(subject)
[1417]	912	subject.setFieldValue(mc.property, value)
	913	break
[1527]	914	case SamplingEvent: (record.any {it.getClass() == mc.entityclass}) ? 0 : record.add(samplingEvent)
[1417]	915	samplingEvent.setFieldValue(mc.property, value)
	916	break
[1527]	917	case Event: (record.any {it.getClass() == mc.entityclass}) ? 0 : record.add(event)
[1417]	918	event.setFieldValue(mc.property, value)
	919	break
[1527]	920	case Sample: (record.any {it.getClass() == mc.entityclass}) ? 0 : record.add(sample)
[1417]	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
[1553]	929	def identifier
[1202]	930
[1527]	931	switch (mc.entityclass) {
[1492]	932	case Study: identifier = "entity_" + study.getIdentifier() + "_" + mc.property
[1417]	933	break
[1492]	934	case Subject: identifier = "entity_" + subject.getIdentifier() + "_" + mc.property
[1417]	935	break
[1492]	936	case SamplingEvent: identifier = "entity_" + samplingEvent.getIdentifier() + "_" + mc.property
[1417]	937	break
[1492]	938	case Event: identifier = "entity_" + event.getIdentifier() + "_" + mc.property
[1417]	939	break
[1492]	940	case Sample: identifier = "entity_" + sample.getIdentifier() + "_" + mc.property
[1417]	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	}
[725]	950	} // end
	951	} // end for
[1417]	952	// a failed column means that using the entity.setFieldValue() threw an exception
	953	return [record, failed]
	954	}
[297]	955
[1417]	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)
[1553]	966	//case TemplateFieldType.FLOAT : return Float.valueOf(value.replace(",","."));
[1417]	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	}
[1591]	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	}
[684]	985
[1417]	986	// classes for fuzzy string matching
	987	// <FUZZY MATCHING>
[1126]	988
[1417]	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)
[1126]	992
[1417]	993	dotProduct(l_histo, r_histo) /
[1553]	994	Math.sqrt(dotProduct(l_histo, l_histo) *
[1417]	995	dotProduct(r_histo, r_histo))
	996	}
[1126]	997
[1417]	998	static def countNgramFrequency(sequence, degree) {
	999	def histo = [:]
	1000	def items = sequence.size()
[1126]	1001
[1417]	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	}
[1126]	1008
[1417]	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	}
[1126]	1016
[1417]	1017	static def stringSimilarity(l_str, r_str, degree = 2) {
[1126]	1018
[1417]	1019	similarity(l_str.toString().toLowerCase().toCharArray(),
[1553]	1020	r_str.toString().toLowerCase().toCharArray(),
	1021	degree)
[1417]	1022	}
[1126]	1023
[1417]	1024	static def mostSimilar(pattern, candidates, threshold = 0) {
	1025	def topScore = 0
	1026	def bestFit = null
[1126]	1027
[1417]	1028	candidates.each { candidate ->
	1029	def score = stringSimilarity(pattern, candidate)
	1030	if (score > topScore) {
	1031	topScore = score
	1032	bestFit = candidate
	1033	}
	1034	}
[1126]	1035
[1417]	1036	if (topScore < threshold)
	1037	bestFit = null
	1038
	1039	bestFit
	1040	}
	1041	// </FUZZY MATCHING>
	1042
[147]	1043	}

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

Download in other formats: