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

Last change on this file since 1050 was 1050, checked in by t.w.abma@…, 10 years ago
  • initial "failed cells" logic figured out, now extracts cells from the datamatrix which were not able to be stored in an entity
  • Property svn:keywords set to Date Author Rev
File size: 17.8 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: 1050 $
13 * $Author: t.w.abma@umcutrecht.nl $
14 * $Date: 2010-11-02 15:48:11 +0000 (di, 02 nov 2010) $
15 */
16
17package dbnp.importer
18import org.apache.poi.hssf.usermodel.*
19import org.apache.poi.poifs.filesystem.POIFSFileSystem
20import org.apache.poi.ss.usermodel.DataFormatter
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
30import dbnp.data.Term
31
32class ImporterService {
33
34    boolean transactional = true
35
36    /**
37    * @param is input stream representing the (workbook) resource
38    * @return high level representation of the workbook
39    */
40    HSSFWorkbook getWorkbook(InputStream is) {
41        POIFSFileSystem fs = new POIFSFileSystem(is)
42        HSSFWorkbook    wb = new HSSFWorkbook(fs);
43        return wb;
44    }
45
46    /**
47     * @param wb high level representation of the workbook
48     * @param sheetindex sheet to use within the workbook
49     * @return header representation as a MappingColumn hashmap
50     */
51    def getHeader(HSSFWorkbook wb, int sheetindex, int headerrow, int datamatrix_start, theEntity=null){
52
53        def sheet = wb.getSheetAt(sheetindex)   
54        def sheetrow = sheet.getRow(datamatrix_start)
55        //def header = []
56        def header = [:]
57        def df = new DataFormatter()
58        def property = new String()
59
60        //for (HSSFCell c: sheet.getRow(datamatrix_start)) {
61
62        (0..sheetrow.getLastCellNum() -1 ).each { columnindex ->
63
64            //def index =   c.getColumnIndex()
65            def datamatrix_celltype = sheet.getRow(datamatrix_start).getCell(columnindex, org.apache.poi.ss.usermodel.Row.CREATE_NULL_AS_BLANK).getCellType()
66            def datamatrix_celldata = df.formatCellValue(sheet.getRow(datamatrix_start).getCell(columnindex))
67            def datamatrix_cell     = sheet.getRow(datamatrix_start).getCell(columnindex)           
68            def headercell = sheet.getRow(headerrow-1+sheet.getFirstRowNum()).getCell(columnindex)
69            def tft = TemplateFieldType.STRING //default templatefield type
70
71            // Check for every celltype, currently redundant code, but possibly this will be
72            // a piece of custom code for every cell type like specific formatting         
73               
74            switch (datamatrix_celltype) {
75                    case HSSFCell.CELL_TYPE_STRING:
76                            //parse cell value as double
77                            def doubleBoolean = true
78                            def fieldtype = TemplateFieldType.STRING
79
80                            // is this string perhaps a double?
81                            try {
82                                formatValue(datamatrix_celldata, TemplateFieldType.DOUBLE)
83                            } catch (NumberFormatException nfe) { doubleBoolean = false }
84                            finally {
85                                if (doubleBoolean) fieldtype = TemplateFieldType.DOUBLE
86                            }
87
88                            header[columnindex] = new dbnp.importer.MappingColumn(name:df.formatCellValue(headercell),
89                                                                            templatefieldtype:fieldtype,
90                                                                            index:columnindex,
91                                                                            entity:theEntity,
92                                                                            property:property);
93
94                            break
95                    case HSSFCell.CELL_TYPE_NUMERIC:
96                            def fieldtype = TemplateFieldType.INTEGER
97                            def doubleBoolean = true
98                            def integerBoolean = true
99
100                            // is this cell really an integer?
101                            try {
102                                Integer.valueOf(datamatrix_celldata)
103                            } catch (NumberFormatException nfe) { integerBoolean = false }
104                            finally {
105                                if (integerBoolean) fieldtype = TemplateFieldType.INTEGER
106                            }
107
108                            // it's not an integer, perhaps a double?
109                            if (!integerBoolean)
110                                try {
111                                    formatValue(datamatrix_celldata, TemplateFieldType.DOUBLE)
112                                } catch (NumberFormatException nfe) { doubleBoolean = false }
113                                finally {
114                                    if (doubleBoolean) fieldtype = TemplateFieldType.DOUBLE
115                                }
116
117                            if (HSSFDateUtil.isCellDateFormatted(datamatrix_cell)) fieldtype = TemplateFieldType.DATE
118
119                            header[columnindex] = new dbnp.importer.MappingColumn(name:df.formatCellValue(headercell),
120                                                                            templatefieldtype:fieldtype,
121                                                                            index:columnindex,
122                                                                            entity:theEntity,
123                                                                            property:property);
124                            break
125                    case HSSFCell.CELL_TYPE_BLANK:
126                            header[columnindex] = new dbnp.importer.MappingColumn(name:df.formatCellValue(headercell),
127                                                                            templatefieldtype:TemplateFieldType.STRING,
128                                                                            index:columnindex,
129                                                                            entity:theEntity,
130                                                                            property:property);
131                            break
132                    default:
133                            header[columnindex] = new dbnp.importer.MappingColumn(name:df.formatCellValue(headercell),
134                                                                            templatefieldtype:TemplateFieldType.STRING,
135                                                                            index:columnindex,
136                                                                            entity:theEntity,
137                                                                            property:property);
138                            break
139            } // end of switch
140        } // end of cell loop
141        return header
142    }
143
144    /**
145     * This method is meant to return a matrix of the rows and columns
146     * used in the preview
147     *
148     * @param wb workbook object
149     * @param sheetindex sheet index used
150     * @param rows amount of rows returned
151     * @return two dimensional array (matrix) of HSSFCell objects
152     */
153
154    HSSFCell[][] getDatamatrix(HSSFWorkbook wb, header, int sheetindex, int datamatrix_start, int count) {
155        def sheet = wb.getSheetAt(sheetindex)
156        def rows  = []
157        def df = new DataFormatter()   
158
159        // walk through all rows
160        (count <= sheet.getLastRowNum()) ?
161        ((datamatrix_start+sheet.getFirstRowNum())..count).each { rowindex ->
162            def row = []
163
164            // walk through every cell
165            /*for (HSSFCell c: sheet.getRow(rowindex)) {
166                row.add(c)
167                println c.getColumnIndex() + "=" +c
168            }*/
169           
170            (0..header.size()-1).each { columnindex ->
171                def c = sheet.getRow(rowindex).getCell(columnindex, org.apache.poi.ss.usermodel.Row.CREATE_NULL_AS_BLANK)               
172                //row.add(df.formatCellValue(c))
173                row.add(c)
174                //if (c.getCellType() == c.CELL_TYPE_STRING) println "STR"+c.getStringCellValue()
175                //if (c.getCellType() == c.CELL_TYPE_NUMERIC) println "INT" +c.getNumericCellValue()
176            }
177                //row.add(df.formatCellValue(c))
178            rows.add(row)
179        } : 0
180
181        return rows
182    }
183
184    /**
185    * This method will move a file to a new location.
186    *
187    * @param file File object to move
188    * @param folderpath folder to move the file to
189    * @param filename (new) filename to give
190    * @return if file has been moved succesful, the new path and filename will be returned, otherwise an empty string will be returned
191    */
192    def moveFile(File file, String folderpath, String filename) {
193        try {
194                def rnd = ""; //System.currentTimeMillis()
195                file.transferTo(new File(folderpath, rnd+filename))
196                return folderpath + filename
197            } catch(Exception exception) {
198                log.error "File move error, ${exception}"
199                return ""
200                }
201    }
202
203    /**
204    * @return random numeric value
205    */
206    def random = {
207            return System.currentTimeMillis() + Runtime.runtime.freeMemory()
208        }
209
210    /**
211    * Method to read data from a workbook and to import data into a two dimensional
212    * array
213    *
214    * @param template_id template identifier to use fields from
215    * @param wb POI horrible spreadsheet formatted workbook object
216    * @param mcmap linked hashmap (preserved order) of MappingColumns
217    * @param sheetindex sheet to use when using multiple sheets
218    * @param rowindex first row to start with reading the actual data (NOT the header)
219    * @return two dimensional array containing records (with entities)
220    *
221    * @see dbnp.importer.MappingColumn
222    */
223    def importData(template_id, HSSFWorkbook wb, int sheetindex, int rowindex, mcmap) {
224        def sheet = wb.getSheetAt(sheetindex)
225        def table = []
226       
227        // walk through all rows and fill the table with records
228        (rowindex..sheet.getLastRowNum()).each { i ->
229            table.add(createRecord(template_id, sheet.getRow(i), mcmap))
230        }
231        return table   
232    }
233
234    /** Method to extract failed cells from the datamatrix. Failed cells are cell values
235     * which could not be stored in an entity (e.g. Humu Supiuns in an ontology field).
236     *
237     * @param datamatrix two dimensional array containing entities and possibly also failed cells
238     * @return array of failed cells
239     * */
240    def getFailedCells(datamatrix) {
241       def failedcells = []
242       
243       datamatrix.each { record ->
244            record.each { column ->
245                column.each {
246                    if (it.getClass().getName().equals('java.util.LinkedHashMap$Entry')) {
247                        println it.key
248                        println it.value
249                        failedcells.add(it)
250                    }
251                }
252            }
253        }
254
255        return failedcells
256    }
257   
258    /**
259     * Method to store a matrix containing the entities in a record like structure. Every row in the table
260     * contains one or more entity objects (which contain fields with values). So actually a row represents
261     * a record with fields from one or more different entities.
262     *
263     * @param study entity Study
264     * @param datamatrix two dimensional array containing entities with values read from Excel file
265     */   
266    def saveDatamatrix(Study study, datamatrix) {
267        def validatedSuccesfully = 0
268        def entitystored = null
269        study.refresh()       
270       
271        // go through the data matrix, read every record and validate the entity and try to persist it
272        datamatrix.each { record ->
273            record.each { entity ->
274                        switch (entity.getClass()) {
275                        case Study       :  print "Persisting Study `" + entity + "`: "
276                                                if (persistEntity(entity)) validatedSuccesfully++
277                                                break
278                        case Subject     :  print "Persisting Subject `" + entity + "`: "
279                                                entity.parent = study
280                                               
281                                                // is the current entity not already in the database?
282                                                entitystored = isEntityStored(entity)
283                                               
284                                                // this entity is new, so add it to the study
285                                                if (entitystored==null) study.addToSubjects(entity)
286                                                else // existing entity, so update it
287                                                    updateEntity(entitystored, entity)
288
289                                                if (persistEntity(study)) validatedSuccesfully++
290                                                break
291                        case Event       :  print "Persisting Event `" + entity + "`: "
292                                                entity.parent = study
293                                                study.addToEvents(entity)
294                                                if (persistEntity(entity)) validatedSuccesfully++
295                                                break
296                        case Sample      :  print "Persisting Sample `" + entity +"`: "
297                                                entity.parent = study
298                                                study.addToSamples(entity)
299                                                if (persistEntity(entity)) validatedSuccesfully++
300                                                break
301                        case SamplingEvent: print "Persisting SamplingEvent `" + entity + "`: "
302                                                entity.parent = study
303                                                study.addToSamplingEvents(entity)
304                                                if (persistEntity(entity)) validatedSuccesfully++
305                                                break
306                        default          :  println "Skipping persisting of `" + entity.getclass() +"`"
307                                                break
308                        } // end switch
309            } // end record
310        } // end datamatrix
311        return validatedSuccesfully
312    }
313
314    /**
315     * Check whether an entity already exists within a study. A combination of
316     * the study where the entity will be stored and a unique field in the entity is
317     * used to check whether the instantiated entity (read from Excel) is new.
318     * If the entity is found in the database it will be returned as so and
319     * it should update the entity-values (read from Ecel) into the record in the database.
320     *
321     * @param entity entity object like a Study, Subject, Sample et cetera
322     * @return entity if found, otherwise null
323     */
324    def isEntityStored(entity) {
325            switch (entity.getClass()) {
326                        case Study          :  return Study.findByCode(entity.code)
327                                               break
328                        case Subject        :  return Subject.findByParentAndName(entity.parent, entity.name)
329                                               break
330                        case Event          :  break
331                        case Sample         :  break
332                        case SamplingEvent  :  break
333                        default             :  // unknown entity
334                                               return null
335            }
336    }
337
338    /**
339     * Find the entity and update the fields. The entity is an instance
340     * read from Excel. This method looks in the database for the entity
341     * having the same identifier. If it has found the same entity
342     * already in the database, it will update the record.
343     *
344     * @param entitystored existing record in the database to update
345     * @param entity entity read from Excel
346     */
347    def updateEntity(entitystored, entity) {
348        switch (entity.getClass()) {
349                        case Study          :  break
350                        case Subject        :  entitystored.properties = entity.properties
351                                               entitystored.save()
352                                               break
353                        case Event          :  break
354                        case Sample         :  break
355                        case SamplingEvent  :  break
356                        default             :  // unknown entity
357                                               return null
358        }
359    }
360
361    /**
362     * Method to persist entities into the database
363     * Checks whether entity already exists (based on identifier column 'name')
364     *
365     * @param entity entity object like Study, Subject, Protocol et cetera
366     *
367     */
368    boolean persistEntity(entity) {
369            println "persisting ${entity}"           
370            // if not validated
371                if (entity.validate()) {
372                        if (entity.save()) { //.merge?
373                                return true
374                        }
375                        else { // if save was unsuccesful
376                                entity.errors.allErrors.each {
377                                        println it
378                                }
379                                return false
380                        }
381                }
382            else { // if not validated
383                    entity.errors.each {
384                            println it
385                    }
386                        return false
387            }
388         }
389
390        /**
391         * This method creates a record (array) containing entities with values
392         *
393         * @param template_id template identifier
394         * @param excelrow POI based Excel row containing the cells
395         * @param mcmap map containing MappingColumn objects
396         */
397        def createRecord(template_id, HSSFRow excelrow, mcmap) {
398                def df = new DataFormatter()
399                def template = Template.get(template_id)
400                def record = []
401                def failed = [:]
402
403                // Initialize all possible entities with the chosen template
404                def study = new Study(template: template)
405                def subject = new Subject(template: template)
406                def samplingEvent = new SamplingEvent(template: template)
407                def event = new Event(template: template)
408                def sample = new Sample(template: template)
409
410                // Go through the Excel row cell by cell
411                for (HSSFCell cell: excelrow) {
412                        // get the MappingColumn information of the current cell
413                        def mc = mcmap[cell.getColumnIndex()]
414                        def value                       
415
416                        // Check if column must be imported
417                        if (mc!=null) if (!mc.dontimport) {
418                                try {
419                                        value = formatValue(df.formatCellValue(cell), mc.templatefieldtype)
420                                } catch (NumberFormatException nfe) {
421                                        value = ""
422                                }
423
424                                try {
425
426                                // which entity does the current cell (field) belong to?
427                                    switch (mc.entity) {
428                                        case Study: // does the entity already exist in the record? If not make it so.
429                                                (record.any {it.getClass() == mc.entity}) ? 0 : record.add(study)
430                                                study.setFieldValue(mc.property, value)
431                                                break
432                                        case Subject: (record.any {it.getClass() == mc.entity}) ? 0 : record.add(subject)
433                                                subject.setFieldValue(mc.property, value)
434                                                break
435                                        case SamplingEvent: (record.any {it.getClass() == mc.entity}) ? 0 : record.add(samplingEvent)
436                                                samplingEvent.setFieldValue(mc.property, value)
437                                                break
438                                        case Event: (record.any {it.getClass() == mc.entity}) ? 0 : record.add(event)
439                                                event.setFieldValue(mc.property, value)
440                                                break
441                                        case Sample: (record.any {it.getClass() == mc.entity}) ? 0 : record.add(sample)
442                                                sample.setFieldValue(mc.property, value)
443                                                break
444                                        case Object:   // don't import
445                                                break
446                                    } // end switch
447                                } catch (IllegalArgumentException iae) {
448                                    // leave the field empty and let the user choose the ontology manually in a later step
449                                    failed.put(mc, value)
450                                    println "failed ("+mc.templatefieldtype+"`" + value + "`"
451                                }
452                        } // end
453                } // end for
454
455        // add the failed columns to the record (might also be just an empty map if nothing failed)
456        // a failed column means that using the entity.setFieldValue() threw an exception
457        //record.add(failed)
458        return record
459    }
460
461    /**
462    * Method to parse a value conform a specific type
463    * @param value string containing the value
464    * @return object corresponding to the TemplateFieldType
465    */
466    def formatValue(String value, TemplateFieldType type) throws NumberFormatException {
467            switch (type) {
468                case TemplateFieldType.STRING       :   return value.trim()
469                case TemplateFieldType.TEXT         :   return value.trim()
470                case TemplateFieldType.INTEGER      :   return (int) Double.valueOf(value)
471                case TemplateFieldType.FLOAT        :   return Float.valueOf(value.replace(",","."));
472                case TemplateFieldType.DOUBLE       :   return Double.valueOf(value.replace(",","."));
473                case TemplateFieldType.STRINGLIST   :   return value.trim()
474                case TemplateFieldType.ONTOLOGYTERM :   return value.trim()
475                case TemplateFieldType.DATE         :   return value
476                default                             :   return value
477            }
478    }
479
480}
Note: See TracBrowser for help on using the repository browser.