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

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