source: trunk/grails-app/services/CsvService.groovy @ 412

import au.com.bytecode.opencsv.CSVWriter
import au.com.bytecode.opencsv.CSVReader
import org.apache.commons.lang.WordUtils

/**
 * Provides some csv import/export methods.
 * Requires the opencsv jar to be available which is included in the grails-export plugin.
 */
class CsvService {

    boolean transactional = false

    /**
    * Import an asset tree creating items as required.
    * @param request The http request to run getFile against.
    * Get file should return a csv format file containing the asset tree as per template.
    */
    def importAssetTree(request) {
        Asset.withTransaction { status ->
            def result = [:]

            def kByteMultiplier = 1000
            def fileMaxSize = 500 * kByteMultiplier

            def multiPartFile = request.getFile('file')

            InputStreamReader sr = new InputStreamReader(multiPartFile.inputStream)
            CSVReader reader = new CSVReader(sr)

            def fail = { Map m ->
                status.setRollbackOnly()
                reader.close()
                result.error = [ code: m.code, args: m.args ]
                return result
            }

            if(!multiPartFile || multiPartFile.isEmpty())
                return fail(code: "asset.tree.import.file.not.supplied")

            if (multiPartFile.getSize() > fileMaxSize)
                return fail(code: "asset.tree.import.file.over.max.size", args: [fileMaxSize/kByteMultiplier, "kB"])

            def columnIndex = 0
            def nameColumnIndex = 0
            def numberOfColumns = 0
            def maxNumberOfColumns = 20

            // Get first line.
            def line = reader.readNext()
            def lineNumber = 1

            // Check for header line 1.
            if(line != templateHeaderLine1) {
                log.error "Failed to find header line 1. "
                log.error "Required: " + templateHeaderLine1.toString()
                log.error "Supplied: " + line.toString()
                return fail(code: "asset.tree.import.no.header")
            }

            // Get second line.
            line = reader.readNext()
            lineNumber ++

            // Check for header line 2.
            if(line != templateHeaderLine2) {
                log.error "Failed to find header line 2. "
                log.error "Required: " + templateHeaderLine2.toString()
                log.error "Supplied: " + line.toString()
                return fail(code: "asset.tree.import.no.header")
            }

            log.info "Import checks passed, start processing asset file."

            // Prepare the first body line.
            line = reader.readNext()
            lineNumber ++

            def siteInstance
            def departmentInstance
            def sectionInstance
            def assetInstance
            def assetSubItemInstance
            def parentItem

            def column = [:]

            def nextLine = {
                    line = reader.readNext()
                    lineNumber ++
                    log.info "Processing line: " + lineNumber
            }

            def nextColumn = {
                nameColumnIndex = columnIndex

                if( (columnIndex+1) < numberOfColumns ) {
                    // Capitalise and assign the name and description columns.
                    use(WordUtils) {
                    column.name = line[columnIndex].trim().capitalize()
                    column.description = line[++columnIndex].trim().capitalize()
                    }
                }
                else if( columnIndex < numberOfColumns ) {
                    // Capitalise and assign the name column with a blank description.
                    use(WordUtils) {
                    column.name = line[columnIndex].trim().capitalize()
                    column.description = ''
                    }
                }
                else {
                    log.info "No more columns on line: " + lineNumber
                    return false
                }

                if(!column.name) {
                    log.info "No name at " + "line: " + lineNumber + " col: " + nameColumnIndex
                    return false
                }

                columnIndex++
                // Success.
                return column
            }

            // Primary loop.
            while(line) {
                numberOfColumns = Math.min( line.size(), maxNumberOfColumns )
                columnIndex = 0

                if(!nextColumn()) {
                    nextLine()
                    continue
                }

                // Ignore comment lines.
                if(line.toString().toLowerCase().contains("comment")) {
                    log.info "Comment line found."
                    nextLine()
                    continue
                }

                // Ignore example lines.
                if(line.toString().toLowerCase().contains("example")) {
                    log.info "Example line found."
                    nextLine()
                    continue
                }

                // Site.
                siteInstance = Site.findByName(column.name)
                if(!siteInstance) {
                    log.info "Creating site: " + column.name
                    siteInstance = new Site(name: column.name,
                                                                description: column.description)
                    if(!siteInstance.save()) {
                        log.error "Failed to create site on line: " + column.name + "(" + lineNumber + ")"
                        return fail(code: "asset.tree.import.failure", args: [lineNumber])
                    }
                }
                else log.info "Existing site: " + siteInstance

                if(!nextColumn()) {
                    nextLine()
                    continue
                }

                // Department.
                departmentInstance = Department.findByName(column.name)
                if(!departmentInstance) {
                    log.info "Creating department: " + column.name
                    departmentInstance = new Department(name: column.name,
                                                                                            description: column.description,
                                                                                            site: siteInstance)
                    if(!departmentInstance.save()) {
                        log.error "Failed to create department on line: " + column.name + "(" + lineNumber + ")"
                        return fail(code: "asset.tree.import.failure", args: [lineNumber])
                    }
                }
                else log.info "Existing department: " + departmentInstance

                if(!nextColumn()) {
                    nextLine()
                    continue
                }

                // Section.
                sectionInstance = Section.findByName(column.name)
                if(!sectionInstance) {
                    log.info "Creating section: " + column.name
                    sectionInstance =  new Section(name: column.name,
                                                                            description: column.description,
                                                                            site: siteInstance,
                                                                            department: departmentInstance)
                    if(!sectionInstance.save()) {
                        log.error "Failed to create section on line: " + column.name + "(" + lineNumber + ")"
                        return fail(code: "asset.tree.import.failure", args: [lineNumber])
                    }
                }
                else log.info "Existing section: " + sectionInstance

                if(!nextColumn()) {
                    nextLine()
                    continue
                }

                // Asset.
                assetInstance = Asset.findByName(column.name)
                if(!assetInstance) {
                    log.info "Creating asset: " + column.name
                    assetInstance = new Asset(name: column.name,
                                                                    description: column.description,
                                                                    section: sectionInstance)
                    if(!assetInstance.save()) {
                        log.error "Failed to create asset on line: " + column.name + "(" + lineNumber + ")"
                        return fail(code: "asset.tree.import.failure", args: [lineNumber])
                    }
                }
                else log.info "Existing asset: " + assetInstance

                if(!nextColumn()) {
                    nextLine()
                    continue
                }

                // AssetSubItem Level 1.
                assetSubItemInstance = AssetSubItem.findByName(column.name)
                if(!assetSubItemInstance) {
                    log.info "Creating asset sub item: " + column.name
                    assetSubItemInstance = new AssetSubItem(name: column.name,
                                                                                                description: column.description)
                    if(!assetInstance.save()) {
                        log.error "Failed to create assetSubItem on line: " + column.name + "(" + lineNumber + ")"
                        return fail(code: "asset.tree.import.failure", args: [lineNumber])
                    }
                }
                else log.info "Existing asset sub item: " + assetSubItemInstance

                assetInstance.addToAssetSubItems(assetSubItemInstance)

                // Remaining AssetSubItems.
                while( nextColumn() ) {

                    parentItem = assetSubItemInstance
                    assetSubItemInstance = AssetSubItem.findByName(column.name)
                    if(!assetSubItemInstance) {
                        log.info "Creating asset sub item: " + column.name
                        assetSubItemInstance = new AssetSubItem(name: column.name,
                                                                                                    description: column.description,
                                                                                                    parentItem: parentItem)
                        if(!assetSubItemInstance.save()) {
                            log.error "Failed to create assetSubItem on line: " + column.name + "(" + lineNumber + ")"
                            return fail(code: "asset.tree.import.failure", args: [lineNumber])
                        }
                    }
                    else log.info "Existing asset sub item: " + assetSubItemInstance

                } // while( nextColumn() )

                nextLine()
            } //while(line)

            // Success.
            log.info "End of file."
            reader.close()
            return result

        } //end withTransaction
    } // end importAssetTree()

    /**
    * Build an asset tree template csv file.
    * This template can then be populated for import.
    * @returns The template as a String in csv format.
    */
    def buildAssetTreeTemplate() {

        StringWriter sw = new StringWriter()
        CSVWriter writer = new CSVWriter(sw)

        writeTemplateLines(writer)

        writer.close()
        return sw.toString()
    }

    private writeTemplateLines(writer) {
        writer.writeNext(templateHeaderLine1 as String[])
        writer.writeNext(templateHeaderLine2 as String[])
        writer.writeNext()
        writer.writeNext(["Example: Site1", "", "Department 1", "", "Section 1", "", "Asset 1", ""] as String[])
        writer.writeNext()
        writer.writeNext(["Example: Site1", "", "Department 1", "", "Section 1", "", "Asset 2", ""] as String[])
        writer.writeNext()
        writer.writeNext("Comment: The first two header lines are required.")
        writer.writeNext("Comment: Leave a blank line between assets.")
        writer.writeNext("Comment: Names are required, descriptions are optional.")
        writer.writeNext("Comment: Identical and existing names will be considered as the same item.")
        writer.writeNext("Comment: An asset may share the first level of asset sub items with other assets.")
        writer.writeNext("Comment: Lower levels of asset sub items are only parented once but may have many children.")
        writer.writeNext("Comment: Lines containing 'comment' will be ignored.")
        writer.writeNext("Comment: Lines containing 'example' will be ignored.")
        writer.writeNext("Comment: This file must be saved as a CSV file before import.")
        writer.writeNext()
    }

    /**
    * Build an asset tree test file.
    * This test file can be imported to test the import and template methods.
    * @returns The test file as a String in csv format.
    */
    def buildAssetTreeTest() {

        StringWriter sw = new StringWriter()
        CSVWriter writer = new CSVWriter(sw)

        writeTemplateLines(writer)

        writer.writeNext(["Lake Press", "Lake Press Site",
                                        "Print Department", "The printing department",
                                        "Press Section", "Contains all printing units",
                                        "Print Unit 1", "Printing Unit Number 1",
                                        "Print Unit", "Print Unit (Common to all units)",
                                        "Print Couple", "Includes  blanket cylinder",
                                        "Motor", "Main Drive Motor",
                                        "NDS Bearing", "Non Drive Side Main Bearing"
                                        ] as String[])

        writer.writeNext(["Lake Press", "Lake Press Site",
                                        "Print Department", "The printing department",
                                        "Press Section", "Contains all printing units",
                                        "Print Unit 1", "Printing Unit Number 1",
                                        "Print Unit", "Print Unit (Common to all units)",
                                        "Print Couple", "Includes  blanket cylinder",
                                        "Motor", "Main Drive Motor",
                                        "DS Bearing", "Drive Side Main Bearing"
                                        ] as String[])

        writer.close()
        return sw.toString()
    }

    /**
    * Build complete asset trees for export.
    * @param assetList The list of assets to build and export trees for.
    * @returns The tree as a String in csv format.
    */
    def buildAssetTree(List assetList) {

        StringWriter sw = new StringWriter()
        CSVWriter writer = new CSVWriter(sw)

        writeTemplateLines(writer)

        //Rows
        def row

        def writeAssetSubItem4 = { assetSubItem ->
            row.add(assetSubItem.name)
            row.add(assetSubItem.description)
            writer.writeNext(row as String[])
        }

        def writeAssetSubItem3 = { assetSubItem ->
            row.add(assetSubItem.name)
            row.add(assetSubItem.description)

            if(assetSubItem.subItems.size() > 0) {
                assetSubItem.subItems.sort { p1, p2 -> p1.name.compareToIgnoreCase(p2.name) }.each() { assetSubItem4 ->
                    writeAssetSubItem4(assetSubItem4)
                    row.removeRange(row.size()-2, row.size())
                }
            }
            else {
                writer.writeNext(row as String[])
            }

        }

        def writeAssetSubItem2 = { assetSubItem ->
            row.add(assetSubItem.name)
            row.add(assetSubItem.description)

            if(assetSubItem.subItems.size() > 0) {
                assetSubItem.subItems.sort { p1, p2 -> p1.name.compareToIgnoreCase(p2.name) }.each() { assetSubItem3 ->
                    writeAssetSubItem3(assetSubItem3)
                    row.removeRange(row.size()-2, row.size())
                }
            }
            else {
                writer.writeNext(row as String[])
            }

        }

        def writeAssetSubItem1 = { assetSubItem ->
            row.add(assetSubItem.name)
            row.add(assetSubItem.description)

            if(assetSubItem.subItems.size() > 0) {
                assetSubItem.subItems.sort { p1, p2 -> p1.name.compareToIgnoreCase(p2.name) }.each() { assetSubItem2 ->
                    writeAssetSubItem2(assetSubItem2)
                    row.removeRange(row.size()-2, row.size())
                }
            }
            else {
                writer.writeNext(row as String[])
            }

        }

        assetList.sort { p1, p2 -> p1.name.compareToIgnoreCase(p2.name) }.each() { asset ->
            row = []
            writer.writeNext(row as String[]) //blank row between assets.
            row.add(asset.section.site.name)
            row.add(asset.section.site.description)
            row.add(asset.section.department.name)
            row.add(asset.section.department.description)
            row.add(asset.section.name)
            row.add(asset.section.description)
            row.add(asset.name)
            row.add(asset.description)

            if(asset.assetSubItems.size() > 0) {
                asset.assetSubItems.sort { p1, p2 -> p1.name.compareToIgnoreCase(p2.name) }.each() { assetSubItem1 ->
                    writeAssetSubItem1(assetSubItem1)
                    row.removeRange(row.size()-2, row.size())
                }
            }
            else {
                writer.writeNext(row as String[])
            }

        }

        writer.close()
        return sw.toString()
    } // end buildAssetTree

    private getTemplateHeaderLine1() {
            ["Site", "", "Department", "", "Section", "","Asset", "", "Sub Asset", "", "Functional Assembly", "", "Sub Assembly Group", "", "SubItem", ""]
    }

    private getTemplateHeaderLine2() {
            (["Name", "Description"])*8
    }

} // end class