source: trunk/grails-app/controllers/dbnp/importer/ImporterController.groovy @ 1215

Last change on this file since 1215 was 1215, checked in by t.w.abma@…, 11 years ago
  • step 3 (mapping properties) now requires all fields to be valid before one can continue to the next step, added validation
  • Property svn:keywords set to Author Date Rev
File size: 16.4 KB
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: 1215 $
17 * $Author: t.w.abma@umcutrecht.nl $
18 * $Date: 2010-11-30 08:28:59 +0000 (di, 30 nov 2010) $
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        def fielderrors = 0
169        def invalidentities = 0
170       
171        session.importer_importeddata.each { table ->
172            table.each { entity ->
173                // a new entity is being traversed, if a field cannot be set, increase this counter
174                fielderrors = 0
175
176                entity.giveFields().each { field ->                   
177                    try {
178                        // try to set the value
179                        entity.setFieldValue (field.toString(), params["entity_" + entity.getIdentifier() + "_" + field.escapedName()])                       
180                    } catch (Exception e) {                   
181                        fielderrors++
182                    }
183                }
184
185                // a field could not be set in the entity, so the entity failed (is not validated)
186                if (fielderrors) invalidentities++
187
188                // all fields in the entity could be set, no errors, so remove it from the failed cells
189                if (!fielderrors) {
190                     session.importer_failedcells.each { record ->
191                        record.importcells.each { cell ->
192                           // remove the cell from the failed cells session
193                           if (cell.entityidentifier == entity.getIdentifier()) {                           
194                               record.removeFromImportcells(cell)
195                           }
196                        }
197                     }
198                } // end of fielderrors
199            } // end of record
200        } // end of table
201
202        // a new ontology term was added, so stay at the current step otherwise go to the next step
203        if (params.updatefield) render(view:"step3_simple", model:[datamatrix:session.importer_importeddata, failedcells:session.importer_failedcells])
204            else 
205        if (invalidentities)
206            render(view:"step3_simple", model:[datamatrix:session.importer_importeddata, failedcells:session.importer_failedcells])
207        else
208            render(view:"step3", model:[datamatrix:session.importer_importeddata])
209    }
210
211    /*
212     * Store the corrected cells back into the datamatrix. Be sure to check
213     * if the corrected ontology is not blank. If so, it should keep
214     * the original value which was read from the Excel file.
215     *
216     * @param cell array of cells with corrected cells (ontologies)
217     *
218    */
219    def saveCorrectedCells = {
220        def correctedcells = [:]
221
222        // Loop through the form with cell fields and values
223        params.cell.index.each { cellhash, value ->
224            correctedcells.put(cellhash, value)
225        }
226
227        // Store the corrected cells back into the datamatrix
228        ImporterService.saveCorrectedCells(
229                    session.importer_importeddata,
230                    session.importer_failedcells,
231                    correctedcells)
232
233        render(view:"step3_simple", model:[datamatrix:session.importer_importeddata])
234
235    }
236
237    /**
238    * User has assigned all entities and templatefieldtypes to the columns and continues to the next step (assigning properties to columns)
239    * All information of the columns is stored in a session as MappingColumn object
240    *
241    * @param entities list of entities and columns it has been assigned to (columnindex.entitytype)
242    * @param templatefieldtype list of celltypes and columns it has been assigned to (columnindex:templatefieldtype format)
243    * @return properties page
244    *
245    * @see celltype: http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Cell.html
246    */
247    def savePreview = {
248        def tft = null 
249        def identifiercolumnindex = (params.identifier!=null) ? params.identifier.toInteger() : -1
250        def selectedentities = []
251
252        // loop all entities and see which column has been assigned which entitytype
253        // and build an array containing the selected entities
254        params.entity.index.each { columnindex, entityname ->
255            def _entity = [name:entityname,columnindex:columnindex.toInteger()]
256            selectedentities.add(_entity)
257        }
258
259        params.templatefieldtype.index.each { columnindex, _templatefieldtype ->
260            switch (_templatefieldtype) {
261                case "STRING"       : tft = TemplateFieldType.STRING
262                                      break
263                case "TEXT"         : tft = TemplateFieldType.TEXT
264                                      break
265                case "LONG"         : tft = TemplateFieldType.LONG
266                                      break
267                case "DOUBLE"       : tft = TemplateFieldType.DOUBLE
268                                      break
269                case "STRINGLIST"   : tft = TemplateFieldType.STRINGLIST
270                                      break
271                case "ONTOLOGYTERM" : tft = TemplateFieldType.ONTOLOGYTERM
272                                      break
273                case "DATE"         : tft = TemplateFieldType.DATE
274                                      break
275                default: break
276            }
277           
278            // Set the templatefield type for this column
279            session.importer_header[columnindex.toInteger()].templatefieldtype = tft
280        }
281
282        // Detect the entity type
283        params.entity.index.each { columnindex, entityname ->
284            Class clazz = null
285
286            switch (entityname) {
287                case "Study"    : clazz = dbnp.studycapturing.Study
288                        break
289                case "Subject"  : clazz = dbnp.studycapturing.Subject
290                        break
291                case "Event"    : clazz = dbnp.studycapturing.Event
292                        break
293                case "Protocol" : clazz = dbnp.studycapturing.Protocol
294                        break
295                case "Sample"   : clazz = dbnp.studycapturing.Sample
296                        break
297                default: clazz = Object
298                        break
299            }
300
301            // Store properties for this column
302            session.importer_header[columnindex.toInteger()].identifier = (columnindex.toInteger() == identifiercolumnindex) ? true : false
303            session.importer_header[columnindex.toInteger()].index = columnindex.toInteger()
304            session.importer_header[columnindex.toInteger()].entity = clazz
305        }
306
307        // currently only one template is used for all entities
308        // TODO: show template fields per entity
309       
310        def templates = Template.get(session.importer_template_id)
311
312        render(view:"step2", model:[entities:selectedentities, header:session.importer_header, templates:templates])
313    }
314   
315    /**
316     * Method which stores the properties set per column and then imports the data.
317     *
318     * @param columnproperty array of columns containing index and property (represented as a String)
319    *
320    */
321    def saveProperties = {       
322
323        // Find actual Template object from the chosen template name
324        def template = Template.get(session.importer_template_id)
325
326        params.columnproperty.index.each { columnindex, property ->
327
328                // Create an actual class instance of the selected entity with the selected template
329                // This should be inside the closure because in some cases in the advanced importer, the fields can have different target entities
330                def entityClass = Class.forName(session.importer_header[columnindex.toInteger()].entity.getName(), true, this.getClass().getClassLoader())
331                def entityObj = entityClass.newInstance(template:template)
332
333                // Store the selected property for this column into the column map for the ImporterService
334                session.importer_header[columnindex.toInteger()].property = property
335
336                // Look up the template field type of the target TemplateField and store it also in the map
337                session.importer_header[columnindex.toInteger()].templatefieldtype = entityObj.giveFieldType(property)
338
339                // Is a "Don't import" property assigned to the column?
340                session.importer_header[columnindex.toInteger()].dontimport = (property=="dontimport") ? true : false
341
342                //if it's an identifier set the mapping column true or false
343                entityObj.giveFields().each {
344                    (it.preferredIdentifier && (it.name==property)) ? session.importer_header[columnindex.toInteger()].identifier = true : false
345                }
346        }
347
348        // Import the workbook and store the table with entity records and store the failed cells
349        def (table, failedcells) = ImporterService.importData(session.importer_template_id, session.importer_workbook, session.importer_sheetindex, session.importer_datamatrix_start, session.importer_header)
350
351        session.importer_importeddata = table       
352        session.importer_failedcells = failedcells
353
354        // Are there any failed cells, then show an extra step to correct the cells
355        /*if (failedcells.size()!=0)
356            render(view:"step2a_simple", model:[failedcells:session.importer_failedcells])
357        else {*/
358            if (params.layout=="horizontal")
359                render(view:"step3_simple", model:[datamatrix:session.importer_importeddata, failedcells:session.importer_failedcells])
360            else if (params.layout=="vertical")
361                render(view:"step3", model:[datamatrix:session.importer_importeddata])
362        //}
363    }
364
365    /**
366     * Method which saves the data matrix to the database
367     */
368    def savePostview = {       
369
370        // Called this page directly, then display an error message.
371        if ( (!session?.import_wizard_init) ) {
372            render (template:"common/error",
373                    model:[error:"Data is already imported or you are calling the url directly without following the previous import steps."])
374        } else {
375            def (validatedSuccesfully, updatedEntities, failedtopersist) = ImporterService.saveDatamatrix(session.importer_study, session.importer_importeddata)
376            session.validatedSuccesfully = validatedSuccesfully
377            render(view:"step4", model:[validatedSuccesfully:session.validatedSuccesfully, failedtopersist:failedtopersist, updatedentities:updatedEntities, totalrows:session.importer_importeddata.size, referer: session.import_referer])
378            session.import_wizard_init = false
379
380            println "failed:"
381            failedtopersist.each {
382                println it
383            }
384        }
385    }
386
387    /**
388    * Return templates which belong to a certain entity type
389    *
390    * @param entity entity name string (Sample, Subject, Study et cetera)
391    * @return JSON object containing the found templates
392    */
393    def ajaxGetTemplatesByEntity = {       
394        def entityName = Blowfish.decryptBase64(
395                        params.entity,
396                        grailsApplication.config.crypto.shared.secret
397                    )
398
399        //def entityClass = grailsApplication.config.gscf.domain.importableEntities.get(params.entity).entity
400        def entityClass = entityName
401
402        // fetch all templates for a specific entity
403        def templates = Template.findAllByEntity(Class.forName(entityClass, true, this.getClass().getClassLoader()))   
404
405        // render as JSON
406        render templates as JSON
407    }
408
409    def refresh = {
410       // params
411    }
412}
Note: See TracBrowser for help on using the repository browser.