source: trunk/grails-app/services/nl/tno/metagenomics/SampleExcelService.groovy @ 9

package nl.tno.metagenomics

class SampleExcelService {
        def excelService
        def fuzzySearchService
        
    static transactional = true

        // Fields to be edited using excel file and manually
        def sampleNameName = "Sample name"
        def runName = "Run"
        def tagNameName = "Tag name"
        def tagSequenceName = "Tag sequence"
        def oligoNumberName = "Oligo number"
        def possibleFields = [sampleNameName, runName, tagNameName, tagSequenceName, oligoNumberName]
        
    def downloadSampleExcel( def assaySamples, boolean includeRun = true ) {
                def sheetIndex = 0;
                
                if( assaySamples == null )
                        assaySamples = []
                        
                def sortedSamples = assaySamples.toList().sort { it.sample.name }
                
                // Create an excel sheet
                def wb = excelService.create();

                def fields = possibleFields
                if( !includeRun )
                        fields = fields - runName
                
                // Put the headers on the first row
                excelService.writeHeader( wb, fields, sheetIndex );

                // Adding the next lines
                ArrayList data = [];
                sortedSamples.each { assaySample ->
                        def rowData = [assaySample.sample?.name];
                        if( includeRun )
                                rowData << assaySample.run?.name
                        
                        rowData << assaySample.tagName
                        rowData << assaySample.tagSequence
                        rowData << assaySample.oligoNumber
                        data << rowData;
                }
                excelService.writeData( wb, data, sheetIndex, 1 );

                // Auto resize columns
                excelService.autoSizeColumns( wb, sheetIndex, 0..2)

                return wb;              
    }
        
        def parseTagsExcel( File file, boolean includeRun = true ) {
                def sheetIndex = 0
                def headerRow = 0
                def dataStartsAtRow = 1
                def numExampleRows = 5
                
                // Create an excel workbook instance of the file
                def     workbook = excelService.open( file );

                // Read headers from the first row and 5 of the first lines as example data
                def headers = excelService.readRow( workbook, sheetIndex, headerRow );
                def exampleData = excelService.readData( workbook, sheetIndex, dataStartsAtRow, -1, numExampleRows ); // -1 means: determine number of rows yourself

                // Try to guess best matches between the excel file and the column names
                def bestMatches = [:]
                def fields = possibleFields
                if( !includeRun )
                        fields = fields - runName
                        
                headers.eachWithIndex { header, idx ->
                        // Do matching using fuzzy search. The 0.1 treshold makes sure that no match if chosen if
                        // there is actually no match at all.
                        bestMatches[idx] = fuzzySearchService.mostSimilar( header, possibleFields, 0.1 );
                }
                
                return [headers: headers, exampleData: exampleData, bestMatches: bestMatches, possibleFields: fields]
        }
        
        def updateTagsByExcel( def matchColumns, def possibleFields, File file, def assaySamples ) {
                def sheetIndex = 0
                def headerRow = 0
                def dataStartsAtRow = 1

                if( !matchColumns ) {
                        // Now delete the file, since we don't need it anymore
                        file?.delete()

                        return [ success: false, message: "No column matches found for excel file. Please try again." ]
                }

                // Determine column numbers
                def columns = [:]
                def dataMatches = false;
                possibleFields.each { columnName ->
                        columns[ columnName ] = matchColumns.findIndexOf { it.value == columnName }

                        if( columnName != sampleNameName && columns[ columnName ] != -1 )
                                dataMatches = true
                }

                // A column to match the sample name must be present
                if( columns[ sampleNameName ] == -1 ) {
                        // Now delete the file, since we don't need it anymore
                        _deleteUploadedFileFromSession()

                        return [ success: false, message: "There must be a column present in the excel file that matches the sample name. Please try again." ]
                }

                // A column with data should also be present
                if( !dataMatches ) {
                        // Now delete the file, since we don't need it anymore
                        _deleteUploadedFileFromSession()

                        return [ success: false, message: "There are no data columns present in the excel file. No samples are updated." ]
                }

                // Now loop through the excel sheet and update all samples with the specified data
                if( !file.exists() || !file.canRead() ) {
                        return [ success: false, message: "Excel file has been removed since previous step. Please try again." ]
                }

                def workbook = excelService.open( file )
                ArrayList data = excelService.readData( workbook, sheetIndex, dataStartsAtRow )

                // Check whether the excel file contains any data
                if( data.size() == 0 ) {
                        // Now delete the file, since we don't need it anymore
                        file.delete()

                        return [ success: false, message: "The excel sheet contains no data to import. Please upload another excel file." ]
                }

                def numSuccesful = 0
                def failedRows = []

                // walk through all rows and fill the table with records
                for( def i = 0; i < data.size(); i++ ) {
                        def rowData = data[ i ];

                        String sampleName = rowData[ columns[ sampleNameName ] ] as String

                        // Find assay by sample name. Since sample names are unique within an assay (enforced by GSCF),
                        // this will always work when only using one assay. When multiple assays are used, this might pose
                        // a problem 
                        AssaySample assaySample = assaySamples.find { it.sample.id == Sample.findByName( sampleName )?.id };

                        // If no assaysample is found, add this row to the failed-row list
                        if( !assaySample ) {
                                failedRows << [ row: rowData, sampleName: sampleName ];
                                continue;
                        }

                        columns.each {
                                if( it.value > -1 ) {
                                        switch( it.key ) {
                                                case tagNameName:               assaySample.tagName = rowData[ it.value ]; break
                                                case tagSequenceName:   assaySample.tagSequence = rowData[ it.value ]; break
                                                case oligoNumberName:   assaySample.oligoNumber = rowData[ it.value ]; break
                                                case runName:                   assaySample.run = Run.findByName( rowData[ it.value ] ); break
                                        }
                                }
                        }

                        assaySample.save()

                        numSuccesful++;
                }

                // Now delete the file, since we don't need it anymore
                file.delete()

                // Return a message to the user
                if( numSuccesful == 0 ) {
                        return [success: false, message: "None of the " + failedRows.size() + " row(s) could be imported, because none of the sample names matched. Have you provided the right excel file?" ]
                } else {
                        def message = numSuccesful + " samples have been updated. "

                        if( failedRows.size() > 0 )
                                message += failedRows.size() + " row(s) could not be imported, because the sample names could not be found in the database."

                        return [success: true, message: message, numSuccesful: numSuccesful, failedRows: failedRows ]

                }
        }
}