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

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