Changeset 1591 for trunk/grails-app/services/dbnp

Timestamp:

Mar 7, 2011, 12:01:52 PM (11 years ago)

Author:

robert@…

Message:

Improved simple wizard so that is can edit studies

File:

• trunk/grails-app/services/dbnp/importer/ImporterService.groovy (modified) (4 diffs)

trunk/grails-app/services/dbnp/importer/ImporterService.groovy

@@ -15 +15 @@
  */
 package dbnp.importer
+
 import org.dbnp.gdt.*
 import org.apache.poi.ss.usermodel.*
@@ -186 +187 @@
         }
 
+        
+        /**
+         * Retrieves records with sample, subject, samplingevent etc. from a study
+         * @param s             Study to retrieve records from
+         * @return              A list with hashmaps [ 'objects': [ 'Sample': .., 'Subject': .., 'SamplingEvent': .., 'Event': '.. ], 'templates': [], 'templateCombination': .. ]
+         */
+        protected def getRecords( Study s ) {
+                def records = [];
+                
+                s.samples?.each {
+                        def record = [ 'objects': retrieveEntitiesBySample( it ) ];
+                
+                        def templates = [:]
+                        def templateCombination = [];
+                        record.objects.each { entity ->
+                                templates[ entity.key ] = entity.value?.template
+                                if( entity.value?.template )
+                                        templateCombination << entity.key + ": " + entity.value?.template?.name;
+                        }
+                        
+                        record.templates = templates;
+                        record.templateCombination = templateCombination.join( ', ' )
+                        
+                        records << record
+                }
+                
+                return records;
+        }
+        
+        /**
+         * Returns a subject, event and samplingEvent that belong to this sample
+         * @param s             Sample to find the information for
+         * @return
+         */
+        protected retrieveEntitiesBySample( Sample s ) {
+                return [
+                        'Sample': s,
+                        'Subject': s?.parentSubject,
+                        'SamplingEvent': s?.parentEvent,
+                        'Event': s?.parentEventGroup?.events?.getAt(0)
+                ]
+        }
+        
+        /**
+         * Imports data from a workbook into a list of ImportRecords. If some entities are already in the database,
+         * these records are updated.
+         * 
+         * This method is capable of importing Subject, Samples, SamplingEvents and Events
+         * 
+         * @param       templates       Map of templates, identified by their entity as a key. For example: [ Subject: Template x, Sample: Template y ]
+         * @param       wb                      Excel workbook to import
+         * @param       sheetindex      Number of the sheet to import data from
+         * @param       rowindex        Row to start importing from.
+         * @param       mcmap           Hashmap of mappingcolumns, with the first entry in the hashmap containing information about the first column, etc.
+         * @param       parent          Study to import all data into. Is used for determining which sample/event/subject/assay to update
+         * @param       createAllEntities       If set to true, the system will also create objects for entities that have no data imported, but do have
+         *                                                              a template assigned
+         * @return      List            List with two entries:
+         *                      0                       List with ImportRecords, one for each row in the excelsheet
+         *                      1                       List with ImportCell objects, mentioning the cells that could not be correctly imported
+         *                                              (because the value in the excelsheet can't be entered into the template field)
+         */
+        def importOrUpdateDataBySampleIdentifier( def templates, Workbook wb, int sheetindex, int rowindex, def mcmap, Study parent = null, boolean createAllEntities = true ) {
+                if( !mcmap )
+                        return;
+                        
+                // Check whether the rows should be imported in one or more entities
+                def entities
+                if( createAllEntities ) {
+                        entities = templates.entrySet().value.findAll { it }.entity;
+                } else {
+                        entities = mcmap.findAll{ !it.dontimport }.entityclass.unique();
+                }
+                
+                def sheet = wb.getSheetAt(sheetindex)
+                def table = []
+                def failedcells = [] // list of cells that have failed to import
+
+                // First check for each record whether an entity in the database should be updated,
+                // or a new entity should be added. This is done before any new object is created, since
+                // searching after new objects have been created (but not yet saved) will result in
+                //      org.hibernate.AssertionFailure: collection [...] was not processed by flush()
+                // errors
+                def existingEntities = [:]
+                for( int i = rowindex; i <= sheet.getLastRowNum(); i++ ) {
+                        existingEntities[i] = findExistingEntities( entities, sheet.getRow(i), mcmap, parent );
+                }
+                                
+                // walk through all rows and fill the table with records
+                for( int i = rowindex; i <= sheet.getLastRowNum(); i++ ) {
+                        // Create an entity record based on a row read from Excel and store the cells which failed to be mapped
+                        def (record, failed) = importOrUpdateRecord( templates, entities, sheet.getRow(i), mcmap, parent, table, existingEntities[i] );
+                        
+                        // Setup the relationships between the imported entities
+                        relateEntities( record );
+                        
+                        // Add record with entities and its values to the table
+                        table.add(record)
+
+                        // If failed cells have been found, add them to the failed cells list
+                        if (failed?.importcells?.size() > 0) failedcells.add(failed)
+                }
+                
+                return [ "table": table, "failedCells": failedcells ]
+        }
+
+        /**
+        * Checks whether entities in the given row already exist in the database
+        * they are updated.
+        *
+        * @param        entities        Entities that have to be imported for this row
+        * @param        excelRow        Excel row to import into this record
+        * @param        mcmap           Hashmap of mappingcolumns, with the first entry in the hashmap containing information about the first column, etc.
+        * @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>]
+        */
+   def findExistingEntities(def entities, Row excelRow, mcmap, parent ) {
+           DataFormatter df = new DataFormatter();
+
+           // Find entities based on sample identifier
+           def sample = findEntityByRow( dbnp.studycapturing.Sample, excelRow, mcmap, parent, [], df );
+           return retrieveEntitiesBySample( sample );
+   }
+   
+        /**
+         * Imports a records from the excelsheet into the database. If the entities are already in the database
+         * they are updated.
+         * 
+         * This method is capable of importing Subject, Samples, SamplingEvents and Events
+         * 
+         * @param       templates       Map of templates, identified by their entity as a key. For example: [ Sample: Template y ]
+         * @param       entities        Entities that have to be imported for this row
+         * @param       excelRow        Excel row to import into this record
+         * @param       mcmap           Hashmap of mappingcolumns, with the first entry in the hashmap containing information about the first column, etc.
+         * @param       parent          Study to import all data into. Is used for determining which sample/event/subject/assay to update
+         * @param       importedRows    Rows that have been imported before this row. These rows might contain the same entities as are
+         *                                                      imported in this row. These entities should be used again, to avoid importing duplicates.
+         * @return      List            List with two entries:
+         *                      0                       List with ImportRecords, one for each row in the excelsheet
+         *                      1                       List with ImportCell objects, mentioning the cells that could not be correctly imported
+         *                                              (because the value in the excelsheet can't be entered into the template field)
+         */
+        def importOrUpdateRecord(def templates, def entities, Row excelRow, mcmap, Study parent = null, List importedRows, Map existingEntities ) {
+                DataFormatter df = new DataFormatter();
+                def record = [] // list of entities and the read values
+                def failed = new ImportRecord() // map with entity identifier and failed mappingcolumn
+        
+                // Check whether this record mentions a sample that has been imported before. In that case,
+                // we update that record, in order to prevent importing the same sample multiple times
+                def importedEntities = [];
+                if( importedRows )
+                        importedEntities = importedRows.flatten().findAll { it.class == dbnp.studycapturing.Sample }.unique();
+
+                def importedSample = findEntityInImportedEntities( dbnp.studycapturing.Sample, excelRow, mcmap, importedEntities, df )
+                def imported = retrieveEntitiesBySample( importedSample );
+                
+                for( entity in entities ) {
+                        // Check whether this entity should be added or updated
+                        // The entity is updated is an entity with the same 'identifier' (field
+                        // specified to be the identifying field) is found in the database
+                        def entityName = entity.name[ entity.name.lastIndexOf( '.' ) + 1..-1];
+                        def template = templates[ entityName ];
+                        
+                        // If no template is specified for this entity, continue with the next
+                        if( !template )
+                                continue;
+                        
+                        // Check whether the object exists in the list of already imported entities
+                        def entityObject = imported[ entityName ]
+                        
+                        // If it doesn't, search for the entity in the database
+                        if( !entityObject && existingEntities )
+                                entityObject = existingEntities[ entityName ];
+                        
+                        // Otherwise, create a new object
+                        if( !entityObject )
+                                entityObject = entity.newInstance();
+                        
+                        // Update the template
+                        entityObject.template = template;
+                        
+                        // Go through the Excel row cell by cell
+                        for (Cell cell: excelRow) {
+                                // get the MappingColumn information of the current cell
+                                def mc = mcmap[cell.getColumnIndex()]
+                                def value
+         
+                                // Check if column must be imported
+                                if (mc != null && !mc.dontimport && mc.entityclass == entity) {
+                                        try {
+                                                value = formatValue(df.formatCellValue(cell), mc.templatefieldtype)
+                                        } catch (NumberFormatException nfe) {
+                                                value = ""
+                                        }
+         
+                                        try {
+                                                entityObject.setFieldValue(mc.property, value)
+                                        } catch (Exception iae) {
+                                                log.error ".import wizard error could not set property `" + mc.property + "` to value `" + value + "`"
+
+                                                // store the mapping column and value which failed
+                                                def identifier = entityName.toLowerCase() + "_" + entityObject.getIdentifier() + "_" + mc.property
+         
+                                                def mcInstance = new MappingColumn()
+                                                mcInstance.properties = mc.properties
+                                                failed.addToImportcells(new ImportCell(mappingcolumn: mcInstance, value: value, entityidentifier: identifier))
+                                        }
+                                } // end if
+                        } // end for
+                        
+                        // If a Study is entered, use it as a 'parent' for other entities
+                        if( entity == Study )
+                                parent = entityObject;
+                        
+                        record << entityObject;
+                }
+                
+                // a failed column means that using the entity.setFieldValue() threw an exception
+                return [record, failed]
+        }
+        
+        /**
+         * Looks into the database to find an object of the given entity that should be updated, given the excel row.
+         * This is done by looking at the 'preferredIdentifier' field of the object. If it exists in the row, and the
+         * value is already in the database for that field, an existing object is returned. Otherwise, null is returned
+         * 
+         * @param       entity          Entity to search
+         * @param       excelRow        Excelrow to search for
+         * @param       mcmap           Map with MappingColumns
+         * @param       parent          Parent study for the entity (if applicable). The returned entity will also have this parent
+         * @param       importedRows    List of entities that have been imported before. The function will first look through this list to find
+         *                                                      a matching entity.
+         * @return      An entity that has the same identifier as entered in the excelRow. The entity is first sought in the importedRows. If it
+         *                      is not found there, the database is queried. If no entity is found at all, null is returned.
+         */
+        def findEntityByRow( Class entity, Row excelRow, def mcmap, Study parent = null, List importedEntities = [], DataFormatter df = null ) {
+                if( df == null )
+                        df = new DataFormatter();
+                        
+                def identifierField = givePreferredIdentifier( entity );
+                
+                if( identifierField ) {
+                        // Check whether the identifierField is chosen in the column matching
+                        def identifierColumn = mcmap.find { it.entityclass == entity && it.property == identifierField.name };
+                        
+                        // If it is, find the identifier and look it up in the database
+                        if( identifierColumn ) {
+                                def identifierCell = excelRow.getCell( identifierColumn.index );
+                                def identifier;
+                                try {
+                                        identifier = formatValue(df.formatCellValue(identifierCell), identifierColumn.templatefieldtype)
+                                } catch (NumberFormatException nfe) {
+                                        identifier = null
+                                }
+                                
+                                // Search for an existing object with the same identifier.
+                                if( identifier ) {
+                                        // First search the already imported rows
+                                        if( importedEntities ) {
+                                                def imported = importedEntities.find { it.getFieldValue( identifierField.name ) == identifier };
+                                                if( imported )
+                                                        return imported;
+                                        }
+                                        
+                                        def c = entity.createCriteria();
+                                        
+                                        // If the entity has a field 'parent', the search should be limited to
+                                        // objects with the same parent. The method entity.hasProperty( "parent" ) doesn't
+                                        // work, since the java.lang.Class entity doesn't know of the parent property.
+                                        if( entity.belongsTo?.containsKey( "parent" ) ) {
+                                                // If the entity requires a parent, but none is given, no
+                                                // results are given from the database. This prevents the user
+                                                // of changing data in another study 
+                                                if( parent && parent.id ) {
+                                                        println "Searching (with parent ) for " + entity.name + " with " + identifierField.name + " = " + identifier
+                                                        return c.get {
+                                                                eq( identifierField.name, identifier )
+                                                                eq( "parent", parent )
+                                                        }
+                                                }
+                                        } else  {
+                                                println "Searching (without parent ) for " + entity.name + " with " + identifierField.name + " = " + identifier
+                                                return c.get {
+                                                        eq( identifierField.name, identifier )
+                                                }
+                                        }
+                                }
+                        }
+                }
+                
+                // No object is found
+                return null;
+        }
+        
+        /**
+        * Looks into the list of already imported entities to find an object of the given entity that should be 
+        * updated, given the excel row. This is done by looking at the 'preferredIdentifier' field of the object. 
+        * If it exists in the row, and the list of imported entities contains an object with the same
+        * identifier, the existing object is returned. Otherwise, null is returned
+        *
+        * @param        entity          Entity to search
+        * @param        excelRow        Excelrow to search for
+        * @param        mcmap           Map with MappingColumns
+        * @param        importedRows    List of entities that have been imported before. The function will first look through this list to find
+        *                                                       a matching entity.
+        * @return       An entity that has the same identifier as entered in the excelRow. The entity is first sought in the importedRows. If it
+        *                       is not found there, the database is queried. If no entity is found at all, null is returned.
+        */
+   def findEntityInImportedEntities( Class entity, Row excelRow, def mcmap, List importedEntities = [], DataFormatter df = null ) {
+           if( df == null )
+                   df = new DataFormatter();
+                   
+           def allFields = entity.giveDomainFields();
+           def identifierField = allFields.find { it.preferredIdentifier }
+           
+           if( identifierField ) {
+                   // Check whether the identifierField is chosen in the column matching
+                   def identifierColumn = mcmap.find { it.entityclass == entity && it.property == identifierField.name };
+                   
+                   // If it is, find the identifier and look it up in the database
+                   if( identifierColumn ) {
+                           def identifierCell = excelRow.getCell( identifierColumn.index );
+                           def identifier;
+                           try {
+                                   identifier = formatValue(df.formatCellValue(identifierCell), identifierColumn.templatefieldtype)
+                           } catch (NumberFormatException nfe) {
+                                   identifier = null
+                           }
+                           
+                           // Search for an existing object with the same identifier.
+                           if( identifier ) {
+                                        // First search the already imported rows
+                                        if( importedEntities ) {
+                                                def imported = importedEntities.find {
+                                                        def fieldValue = it.getFieldValue( identifierField.name )
+
+                                                        if( fieldValue instanceof String )
+                                                                return fieldValue.toLowerCase() == identifier.toLowerCase();
+                                                        else
+                                                                return fieldValue == identifier
+
+                                                };
+                                           if( imported )
+                                                   return imported;
+                                   }
+                           }
+                   }
+           }
+           
+           // No object is found
+           return null;
+   }
+
+        
+        /**
+         * Creates relation between multiple entities that have been imported. The entities are
+         * all created from one row in the excel sheet.
+         */
+        def relateEntities( List entities) {
+                def study = entities.find { it instanceof Study }
+                def subject = entities.find { it instanceof Subject }
+                def sample = entities.find { it instanceof Sample }
+                def event = entities.find { it instanceof Event }
+                def samplingEvent = entities.find { it instanceof SamplingEvent }
+                def assay = entities.find { it instanceof Assay }
+                
+                // A study object is found in the entity list
+                if( study ) {
+                        if( subject ) {
+                                subject.parent = study;
+                                study.addToSubjects( subject );
+                        }
+                        if( sample ) {
+                                sample.parent = study
+                                study.addToSamples( sample );
+                        }
+                        if( event ) {
+                                event.parent = study 
+                                study.addToEvents( event );
+                        }
+                        if( samplingEvent ) {
+                                samplingEvent.parent = study
+                                study.addToSamplingEvents( samplingEvent );
+                        }
+                        if( assay ) {
+                                assay.parent = study;
+                                study.addToAssays( assay );
+                        }
+                }
+
+                if( sample ) {
+                        if( subject ) sample.parentSubject = subject
+                        if( samplingEvent ) sample.parentEvent = samplingEvent;
+                        if( event ) {
+                                def evGroup = new EventGroup();
+                                evGroup.addToEvents( event );
+                                if( subject ) evGroup.addToSubjects( subject );
+                                if( samplingEvent ) evGroup.addToSamplingEvents( samplingEvent );
+                                
+                                sample.parentEventGroup = evGroup;
+                        }
+                        
+                        if( assay ) assay.addToSamples( sample );
+                }
+        }
+
         /**
          * Method to read data from a workbook and to import data into a two dimensional
@@ -258 +664 @@
          */
         def getFieldNameInTableEditor(entity, field) {
+                def entityName = entity?.class.name[ entity?.class.name.lastIndexOf(".") + 1..-1]
+                
                 if( field instanceof TemplateField )
                         field = field.escapedName();
 
-                return "entity_" + entity.getIdentifier() + "_" + field
+                return entityName.toLowerCase() + "_" + entity.getIdentifier() + "_" + field
         }
 
@@ -562 +970 @@
                 }
         }
+        
+        /**
+         * Returns the preferred identifier field for a given entity or 
+         * null if no preferred identifier is given
+         * @param entity        TemplateEntity class
+         * @return      The preferred identifier field or NULL if no preferred identifier is given
+         */
+        public TemplateField givePreferredIdentifier( Class entity ) {
+                def allFields = entity.giveDomainFields();
+                return allFields.find { it.preferredIdentifier }
+        }
 
         // classes for fuzzy string matching