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

Last change on this file since 1087 was 1087, checked in by s.h.sikkema@…, 13 years ago

Added xlsx import functionality
  • Property svn:keywords set to Author Date Rev
File size: 12.1 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: 1087 $
17 * $Author: s.h.sikkema@gmail.com $
18 * $Date: 2010-11-05 10:55:58 +0000 (vr, 05 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
31@Secured(['IS_AUTHENTICATED_REMEMBERED'])
32class ImporterController {
33    def ImporterService
34    def AuthenticationService
35
36    /**
37     * Default page
38     **/
39
40    def index = {
41        //session.import_referer = request.forwardURI
42        // should do a check what is in the url, strip it?
43        session.import_referer = params.redirectTo       
44    }
45
46    def simpleWizard = {
47        render(view:"index_simple", model:[studies:Study.findAllWhere(owner:AuthenticationService.getLoggedInUser()), entities: grailsApplication.config.gscf.domain.importableEntities])
48    }
49
50    def advancedWizard = {
51        render(view:"index_advanced", model:[templates:Template.list()])
52    }
53
54    /**
55    * This method will move the uploaded file to a temporary path and send the header
56    * and the first n rows to the preview
57    * @param importfile uploaded file to import
58    * @param study.id study identifier
59    */
60    def upload_advanced = {
61        def wb = handleUpload('importfile')
62
63        session.importer_header = ImporterService.getHeader(wb, 0)
64        session.importer_study = Study.get(params.study.id.toInteger())
65        session.importer_template_id = params.template_id
66        session.importer_workbook = wb
67
68        render (view:"step1_advanced", model:[header:session.importer_header, datamatrix:ImporterService.getDatamatrix(wb, session.importer_header, 0, 5)])
69    }
70
71    /**
72    * This method will move the uploaded file to a temporary path and send the header
73    * and the rows to the postview
74    *
75    * @param importfile uploaded file to import
76    * @param entity string representation of the entity chosen
77    */
78    def upload_simple = {
79        def wb = handleUpload('importfile')
80        def selectedentities = []
81        def entity = grailsApplication.config.gscf.domain.importableEntities.get(params.entity).entity
82        def entityClass = Class.forName(entity, true, this.getClass().getClassLoader())
83
84        // Initialize some session variables
85        session.importer_workbook = wb
86        session.importer_study = Study.get(params.study.id.toInteger())
87        session.importer_template_id = params.template_id
88        session.importer_sheetindex = params.sheetindex.toInteger() -1 // 0 == first sheet
89        session.importer_datamatrix_start = params.datamatrix_start.toInteger() -1 // 0 == first row
90        session.importer_headerrow = params.headerrow.toInteger()
91
92        // Get the header from the Excel file using the arguments given in the first step of the wizard
93        session.importer_header = ImporterService.getHeader(wb,
94                                                            session.importer_sheetindex,
95                                                            session.importer_headerrow,
96                                                            session.importer_datamatrix_start,
97                                                            entityClass)
98       
99        // Initialize 'selected entities', used to show entities above the columns
100        session.importer_header.each {
101            selectedentities.add([name:params.entity, columnindex:it.key.toInteger()])
102        }
103
104        def templates = Template.get(session.importer_template_id)
105       
106        render(view:"step2_simple", model:[entities: selectedentities,
107                                           header:session.importer_header,
108                                           datamatrix:ImporterService.getDatamatrix(
109                                               wb, session.importer_header,
110                                               session.importer_sheetindex,
111                                               session.importer_datamatrix_start,
112                                               5),
113                                           templates:templates])
114    }
115
116    /**
117     * This method handles a file being uploaded and storing it in a temporary directory
118     * and returning a workbook
119     *
120     * @param formfilename name used for the file field in the form
121     * @return workbook object reference
122     */
123    private Workbook handleUpload(formfilename) {
124
125        def downloadedfile = request.getFile(formfilename);
126        def tempfile = new File(System.getProperty('java.io.tmpdir') + File.separatorChar + System.currentTimeMillis() + ".nmcdsp")
127        downloadedfile.transferTo(tempfile)
128
129        return ImporterService.getWorkbook(new FileInputStream(tempfile))
130    }
131
132    /**
133     * Method to save the missing properties
134     * @param entity entity class we are using (dbnp.studycapturing.Subject etc.)
135     */
136
137    def saveMissingProperties = {
138
139        session.importer_importeddata.each { table ->
140            table.each { entity ->
141                entity.giveFields().each { field ->
142                    //print ":" + params["entity_" + entity.hashCode() + "_" + field.escapedName()]
143                    entity.setFieldValue (field.toString(), params["entity_" + entity.hashCode() + "_" + field.escapedName()])
144                }               
145            }
146        }
147
148        render(view:"step3", model:[datamatrix:session.importer_importeddata]) 
149    }
150
151    /*
152     * Store the corrected cells back into the datamatrix. Be sure to check
153     * if the corrected ontology is not blank. If so, it should keep
154     * the original value which was read from the Excel file.
155     *
156     * @param cell array of cells with updated ontologies
157     *
158    */
159    def saveCorrectedCells = {
160        def correctedcells = [:]
161
162        // Loop through the form with cell fields and values
163        params.cell.index.each { cellhashcode, value ->
164            correctedcells.put(cellhashcode, value)
165        }
166
167        // Store the corrected cells back into the datamatrix
168        ImporterService.saveCorrectedCells(
169                    session.importer_importeddata,
170                    session.imported_failedcells,
171                    correctedcells)
172
173        //render("failed cells saved")
174     
175        render(view:"step3_simple", model:[datamatrix:session.importer_importeddata])
176
177    }
178
179    /**
180    * User has assigned all entities and templatefieldtypes to the columns and continues to the next step (assigning properties to columns)
181    * All information of the columns is stored in a session as MappingColumn object
182    *
183    * @param entities list of entities and columns it has been assigned to (columnindex.entitytype)
184    * @param templatefieldtype list of celltypes and columns it has been assigned to (columnindex:templatefieldtype format)
185    * @return properties page
186    *
187    * @see celltype: http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Cell.html
188    */
189    def savePreview = {
190        def tft = null 
191        def identifiercolumnindex = (params.identifier!=null) ? params.identifier.toInteger() : -1
192        def selectedentities = []
193
194        // loop all entities and see which column has been assigned which entitytype
195        // and build an array containing the selected entities
196        params.entity.index.each { columnindex, entityname ->
197            def _entity = [name:entityname,columnindex:columnindex.toInteger()]
198            selectedentities.add(_entity)
199        }
200
201        params.templatefieldtype.index.each { columnindex, _templatefieldtype ->
202            switch (_templatefieldtype) {
203                case "STRING"       : tft = TemplateFieldType.STRING
204                                      break
205                case "TEXT"         : tft = TemplateFieldType.TEXT
206                                      break
207                case "INTEGER"      : tft = TemplateFieldType.INTEGER
208                                      break
209                case "FLOAT"        : tft = TemplateFieldType.FLOAT
210                                      break
211                case "DOUBLE"       : tft = TemplateFieldType.DOUBLE
212                                      break
213                case "STRINGLIST"   : tft = TemplateFieldType.STRINGLIST
214                                      break
215                case "ONTOLOGYTERM" : tft = TemplateFieldType.ONTOLOGYTERM
216                                      break
217                case "DATE"         : tft = TemplateFieldType.DATE
218                                      break
219                default: break
220            }
221           
222            // Set the templatefield type for this column
223            session.importer_header[columnindex.toInteger()].templatefieldtype = tft
224        }
225
226        // Detect the entity type
227        params.entity.index.each { columnindex, entityname ->
228            Class clazz = null
229
230            switch (entityname) {
231                case "Study"    : clazz = dbnp.studycapturing.Study
232                        break
233                case "Subject"  : clazz = dbnp.studycapturing.Subject
234                        break
235                case "Event"    : clazz = dbnp.studycapturing.Event
236                        break
237                case "Protocol" : clazz = dbnp.studycapturing.Protocol
238                        break
239                case "Sample"   : clazz = dbnp.studycapturing.Sample
240                        break
241                default: clazz = Object
242                        break
243            }
244
245            // Store properties for this column
246            session.importer_header[columnindex.toInteger()].identifier = (columnindex.toInteger() == identifiercolumnindex) ? true : false
247            session.importer_header[columnindex.toInteger()].index = columnindex.toInteger()
248            session.importer_header[columnindex.toInteger()].entity = clazz
249        }
250
251        // currently only one template is used for all entities
252        // TODO: show template fields per entity
253       
254        def templates = Template.get(session.importer_template_id)
255
256        render(view:"step2", model:[entities:selectedentities, header:session.importer_header, templates:templates])
257    }
258   
259    /**
260     * Method which stores the properties set per column and then imports the data.
261     *
262     * @param columnproperty array of columns containing index and property (represented as a String)
263    *
264    */
265    def saveProperties = {       
266
267        // Find actual Template object from the chosen template name
268        def template = Template.get(session.importer_template_id)
269
270        params.columnproperty.index.each { columnindex, property ->
271
272                // Create an actual class instance of the selected entity with the selected template
273                // This should be inside the closure because in some cases in the advanced importer, the fields can have different target entities
274                def entityClass = Class.forName(session.importer_header[columnindex.toInteger()].entity.getName(), true, this.getClass().getClassLoader())
275                def entityObj = entityClass.newInstance(template:template)
276
277                // Store the selected property for this column into the column map for the ImporterService
278                session.importer_header[columnindex.toInteger()].property = property
279
280                // Look up the template field type of the target TemplateField and store it also in the map
281                session.importer_header[columnindex.toInteger()].templatefieldtype = entityObj.giveFieldType(property)
282
283                // Is a "Don't import" property assigned to the column?
284                session.importer_header[columnindex.toInteger()].dontimport = (property=="dontimport") ? true : false
285
286                //if it's an identifier set the mapping column true or false
287                entityObj.giveFields().each {
288                    (it.preferredIdentifier && (it.name==property)) ? session.importer_header[columnindex.toInteger()].identifier = true : false
289                }
290        }
291
292        //import workbook and store it in a session
293        session.importer_importeddata = ImporterService.importData(session.importer_template_id, session.importer_workbook, session.importer_sheetindex, session.importer_datamatrix_start, session.importer_header)
294
295        // First handle the "failed cells"
296        session.imported_failedcells = ImporterService.getFailedCells(session.importer_importeddata)
297
298        //render(view:"step2a_simple", model:[failedcells:session.imported_failedcells])
299
300        if (params.layout=="horizontal")
301            render(view:"step3_simple", model:[datamatrix:session.importer_importeddata])
302        else if (params.layout=="vertical")
303            render(view:"step3", model:[datamatrix:session.importer_importeddata])
304    }
305
306    /**
307     * Method which saves the data matrix to the database
308     */
309    def savePostview = {
310        def validatedSuccesfully = ImporterService.saveDatamatrix(session.importer_study, session.importer_importeddata)
311        render(view:"step4", model:[validatedSuccesfully:validatedSuccesfully, totalrows:session.importer_importeddata.size, referer: session.import_referer])
312    }
313
314    /**
315    * Return templates which belong to a certain entity type
316    *
317    * @param entity entity name string (Sample, Subject, Study et cetera)
318    * @return JSON object containing the found templates
319    */
320    def ajaxGetTemplatesByEntity = {
321        def entityClass = grailsApplication.config.gscf.domain.importableEntities.get(params.entity).entity
322       
323
324        // fetch all templates for a specific entity
325        def templates = Template.findAllByEntity(Class.forName(entityClass, true, this.getClass().getClassLoader()))   
326
327        // render as JSON
328        render templates as JSON
329    }
330}
Note: See TracBrowser for help on using the repository browser.