root/trunk/grails-app/services/dbnp/importer/ImporterService.groovy @ 1553

Revision 1553, 20.6 KB (checked in by robert@…, 3 years ago)

First version of a simple wizard

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