root/trunk/grails-app/controllers/dbnp/importer/ImporterController.groovy @ 1202

Revision 1202, 14.7 KB (checked in by t.w.abma@…, 3 years ago)

- removed extra step and implemented a failed cells check in step 3 (missing mappings) which shows the failed ontologies in the table editor (might have to tune appearance)

  • Property svn:keywords set to Author Date Rev
Line 
1/**
2 * Importer controller
3 *
4 * The importer controller handles the uploading of tabular, comma delimited and Excel format
5 * based files. When uploaded a preview is shown of the data and the user can adjust the column
6 * type. Data in cells which don't correspond to the specified column type will be represented as "#error".
7 *
8 * The importer controller catches the actions and consecutively performs the
9 * logic behind it.
10 *
11 * @package     importer
12 * @author      t.w.abma@umcutrecht.nl
13 * @since       20100126
14 *
15 * Revision information:
16 * $Rev$
17 * $Author$
18 * $Date$
19 */
20
21package dbnp.importer
22
23import dbnp.studycapturing.Template
24import dbnp.studycapturing.Study
25
26import dbnp.studycapturing.TemplateFieldType
27import grails.converters.JSON
28import org.apache.poi.ss.usermodel.Workbook
29import grails.plugins.springsecurity.Secured
30
31import cr.co.arquetipos.crypto.Blowfish
32
33@Secured(['IS_AUTHENTICATED_REMEMBERED'])
34class ImporterController {
35    def ImporterService
36    def AuthenticationService
37
38    /**
39     * Default page
40     **/
41
42    def index = {
43        // no data has been imported yet
44        session.import_wizard_init = true
45
46        // should do a check what is in the url, strip it?
47        session.import_referer = params.redirectTo
48
49        grailsApplication.config.gscf.domain.importableEntities.each {           
50            it.value.encrypted =
51                                Blowfish.encryptBase64(
52                                        it.value.entity.toString().replaceAll(/^class /, ''),
53                                        grailsApplication.config.crypto.shared.secret
54                                )
55        }
56
57        render(view:"index_simple",
58               model:[studies:Study.findAllWhere(owner:AuthenticationService.getLoggedInUser()),
59               entities: grailsApplication.config.gscf.domain.importableEntities])
60    }
61
62    def simpleWizard = {
63        //render(view:"index_simple", model:[studies:Study.findAllWhere(owner:AuthenticationService.getLoggedInUser()), entities: grailsApplication.config.gscf.domain.importableEntities])
64    }
65
66    def advancedWizard = {
67        //render(view:"index_advanced", model:[templates:Template.list()])
68    }
69
70    /**
71    * This method will move the uploaded file to a temporary path and send the header
72    * and the first n rows to the preview
73    * @param importfile uploaded file to import
74    * @param study.id study identifier
75    */
76    def upload_advanced = {
77        def wb = handleUpload('importfile')
78
79        session.importer_header = ImporterService.getHeader(wb, 0)
80        session.importer_study = Study.get(params.study.id.toInteger())
81        session.importer_template_id = params.template_id
82        session.importer_workbook = wb
83
84        render (view:"step1_advanced", model:[header:session.importer_header, datamatrix:ImporterService.getDatamatrix(wb, session.importer_header, 0, 5)])
85    }
86
87    /**
88    * This method will move the uploaded file to a temporary path and send the header
89    * and the rows to the postview
90    *
91    * @param importfile uploaded file to import
92    * @param entity string representation of the entity chosen
93    */
94    def upload_simple = {
95        def wb = handleUpload('importfile')
96        def selectedentities = []
97        //def entity = grailsApplication.config.gscf.domain.importableEntities.get(params.entity).entity
98        def entityName = Blowfish.decryptBase64(
99                            params.entity,
100                            grailsApplication.config.crypto.shared.secret
101                         )
102
103        def entityClass = Class.forName(entityName, true, this.getClass().getClassLoader())
104       
105        // Initialize some session variables
106        session.importer_workbook = wb
107        session.importer_study = Study.get(params.study.id.toInteger())
108
109        // Is the current logged in user allowed to write to this study?
110        if (session.importer_study.canWrite(AuthenticationService.getLoggedInUser())) {
111            session.importer_template_id = params.template_id
112            session.importer_sheetindex = params.sheetindex.toInteger() -1 // 0 == first sheet
113            session.importer_datamatrix_start = params.datamatrix_start.toInteger() -1 // 0 == first row
114            session.importer_headerrow = params.headerrow.toInteger()
115
116            // Get the header from the Excel file using the arguments given in the first step of the wizard
117            session.importer_header = ImporterService.getHeader(wb,
118                                                                session.importer_sheetindex,
119                                                                session.importer_headerrow,
120                                                                session.importer_datamatrix_start,
121                                                                entityClass)
122       
123            // Initialize 'selected entities', used to show entities above the columns
124            session.importer_header.each {               
125                selectedentities.add([name:entityName, columnindex:it.key.toInteger()])
126            }
127
128            def templates = Template.get(session.importer_template_id)
129
130            render(view:"step2_simple", model:[entities: selectedentities,
131                                        header:session.importer_header,
132                                        datamatrix:ImporterService.getDatamatrix(
133                                        wb, session.importer_header,
134                                        session.importer_sheetindex,
135                                        session.importer_datamatrix_start,
136                                        5),
137                                        templates:templates])
138        } // end of if
139        else {
140            render (template:"common/error",
141                    model:[error:"Wrong permissions: you are not allowed to write to the study you selected (${session.importer_study})."])
142        }
143    }
144
145    /**
146     * This method handles a file being uploaded and storing it in a temporary directory
147     * and returning a workbook
148     *
149     * @param formfilename name used for the file field in the form
150     * @return workbook object reference
151     */
152    private Workbook handleUpload(formfilename) {
153
154        def downloadedfile = request.getFile(formfilename);
155        def tempfile = new File(System.getProperty('java.io.tmpdir') + File.separatorChar + System.currentTimeMillis() + ".nmcdsp")
156        downloadedfile.transferTo(tempfile)
157
158        return ImporterService.getWorkbook(new FileInputStream(tempfile))
159    }
160
161    /**
162     * Method to save the missing properties.
163     *
164     * @param entity entity class we are using (dbnp.studycapturing.Subject etc.)
165     */
166
167    def saveMissingProperties = {
168       
169        session.importer_importeddata.each { table ->
170            table.each { entity ->
171                entity.giveFields().each { field ->                 
172                    entity.setFieldValue (field.toString(), params["entity_" + entity.getIdentifier() + "_" + field.escapedName()])
173                }               
174            }
175        }
176
177        // a new ontology term was added, so stay at the current step otherwise go to the next step
178        if (params.updatefield) render(view:"step3_simple", model:[datamatrix:session.importer_importeddata, failedcells:session.importer_failedcells])
179            else render(view:"step3", model:[datamatrix:session.importer_importeddata])
180    }
181
182    /*
183     * Store the corrected cells back into the datamatrix. Be sure to check
184     * if the corrected ontology is not blank. If so, it should keep
185     * the original value which was read from the Excel file.
186     *
187     * @param cell array of cells with corrected cells (ontologies)
188     *
189    */
190    def saveCorrectedCells = {
191        def correctedcells = [:]
192
193        // Loop through the form with cell fields and values
194        params.cell.index.each { cellhash, value ->
195            correctedcells.put(cellhash, value)
196        }
197
198        // Store the corrected cells back into the datamatrix
199        ImporterService.saveCorrectedCells(
200                    session.importer_importeddata,
201                    session.importer_failedcells,
202                    correctedcells)
203
204        render(view:"step3_simple", model:[datamatrix:session.importer_importeddata])
205
206    }
207
208    /**
209    * User has assigned all entities and templatefieldtypes to the columns and continues to the next step (assigning properties to columns)
210    * All information of the columns is stored in a session as MappingColumn object
211    *
212    * @param entities list of entities and columns it has been assigned to (columnindex.entitytype)
213    * @param templatefieldtype list of celltypes and columns it has been assigned to (columnindex:templatefieldtype format)
214    * @return properties page
215    *
216    * @see celltype: http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Cell.html
217    */
218    def savePreview = {
219        def tft = null 
220        def identifiercolumnindex = (params.identifier!=null) ? params.identifier.toInteger() : -1
221        def selectedentities = []
222
223        // loop all entities and see which column has been assigned which entitytype
224        // and build an array containing the selected entities
225        params.entity.index.each { columnindex, entityname ->
226            def _entity = [name:entityname,columnindex:columnindex.toInteger()]
227            selectedentities.add(_entity)
228        }
229
230        params.templatefieldtype.index.each { columnindex, _templatefieldtype ->
231            switch (_templatefieldtype) {
232                case "STRING"       : tft = TemplateFieldType.STRING
233                                      break
234                case "TEXT"         : tft = TemplateFieldType.TEXT
235                                      break
236                case "LONG"         : tft = TemplateFieldType.LONG
237                                      break
238                case "DOUBLE"       : tft = TemplateFieldType.DOUBLE
239                                      break
240                case "STRINGLIST"   : tft = TemplateFieldType.STRINGLIST
241                                      break
242                case "ONTOLOGYTERM" : tft = TemplateFieldType.ONTOLOGYTERM
243                                      break
244                case "DATE"         : tft = TemplateFieldType.DATE
245                                      break
246                default: break
247            }
248           
249            // Set the templatefield type for this column
250            session.importer_header[columnindex.toInteger()].templatefieldtype = tft
251        }
252
253        // Detect the entity type
254        params.entity.index.each { columnindex, entityname ->
255            Class clazz = null
256
257            switch (entityname) {
258                case "Study"    : clazz = dbnp.studycapturing.Study
259                        break
260                case "Subject"  : clazz = dbnp.studycapturing.Subject
261                        break
262                case "Event"    : clazz = dbnp.studycapturing.Event
263                        break
264                case "Protocol" : clazz = dbnp.studycapturing.Protocol
265                        break
266                case "Sample"   : clazz = dbnp.studycapturing.Sample
267                        break
268                default: clazz = Object
269                        break
270            }
271
272            // Store properties for this column
273            session.importer_header[columnindex.toInteger()].identifier = (columnindex.toInteger() == identifiercolumnindex) ? true : false
274            session.importer_header[columnindex.toInteger()].index = columnindex.toInteger()
275            session.importer_header[columnindex.toInteger()].entity = clazz
276        }
277
278        // currently only one template is used for all entities
279        // TODO: show template fields per entity
280       
281        def templates = Template.get(session.importer_template_id)
282
283        render(view:"step2", model:[entities:selectedentities, header:session.importer_header, templates:templates])
284    }
285   
286    /**
287     * Method which stores the properties set per column and then imports the data.
288     *
289     * @param columnproperty array of columns containing index and property (represented as a String)
290    *
291    */
292    def saveProperties = {       
293
294        // Find actual Template object from the chosen template name
295        def template = Template.get(session.importer_template_id)
296
297        params.columnproperty.index.each { columnindex, property ->
298
299                // Create an actual class instance of the selected entity with the selected template
300                // This should be inside the closure because in some cases in the advanced importer, the fields can have different target entities
301                def entityClass = Class.forName(session.importer_header[columnindex.toInteger()].entity.getName(), true, this.getClass().getClassLoader())
302                def entityObj = entityClass.newInstance(template:template)
303
304                // Store the selected property for this column into the column map for the ImporterService
305                session.importer_header[columnindex.toInteger()].property = property
306
307                // Look up the template field type of the target TemplateField and store it also in the map
308                session.importer_header[columnindex.toInteger()].templatefieldtype = entityObj.giveFieldType(property)
309
310                // Is a "Don't import" property assigned to the column?
311                session.importer_header[columnindex.toInteger()].dontimport = (property=="dontimport") ? true : false
312
313                //if it's an identifier set the mapping column true or false
314                entityObj.giveFields().each {
315                    (it.preferredIdentifier && (it.name==property)) ? session.importer_header[columnindex.toInteger()].identifier = true : false
316                }
317        }
318
319        // Import the workbook and store the table with entity records and store the failed cells
320        def (table, failedcells) = ImporterService.importData(session.importer_template_id, session.importer_workbook, session.importer_sheetindex, session.importer_datamatrix_start, session.importer_header)
321
322        session.importer_importeddata = table       
323        session.importer_failedcells = failedcells
324
325        // Are there any failed cells, then show an extra step to correct the cells
326        /*if (failedcells.size()!=0)
327            render(view:"step2a_simple", model:[failedcells:session.importer_failedcells])
328        else {*/
329            if (params.layout=="horizontal")
330                render(view:"step3_simple", model:[datamatrix:session.importer_importeddata, failedcells:session.importer_failedcells])
331            else if (params.layout=="vertical")
332                render(view:"step3", model:[datamatrix:session.importer_importeddata])
333        //}
334    }
335
336    /**
337     * Method which saves the data matrix to the database
338     */
339    def savePostview = {       
340
341        // Called this page directly, then display an error message.
342        if ( (!session?.import_wizard_init) ) {
343            render (template:"common/error",
344                    model:[error:"Data is already imported or you are calling the url directly without following the previous import steps."])
345        } else {
346            session.validatedSuccesfully = ImporterService.saveDatamatrix(session.importer_study, session.importer_importeddata)
347            render(view:"step4", model:[validatedSuccesfully:session.validatedSuccesfully, totalrows:session.importer_importeddata.size, referer: session.import_referer])
348            session.import_wizard_init = false
349        }
350    }
351
352    /**
353    * Return templates which belong to a certain entity type
354    *
355    * @param entity entity name string (Sample, Subject, Study et cetera)
356    * @return JSON object containing the found templates
357    */
358    def ajaxGetTemplatesByEntity = {       
359        def entityName = Blowfish.decryptBase64(
360                        params.entity,
361                        grailsApplication.config.crypto.shared.secret
362                    )
363
364        //def entityClass = grailsApplication.config.gscf.domain.importableEntities.get(params.entity).entity
365        def entityClass = entityName
366
367        // fetch all templates for a specific entity
368        def templates = Template.findAllByEntity(Class.forName(entityClass, true, this.getClass().getClassLoader()))   
369
370        // render as JSON
371        render templates as JSON
372    }
373
374    def refresh = {
375       // params
376    }
377}
Note: See TracBrowser for help on using the browser.