source: trunk/grails-app/services/AssetReportService.groovy @ 735

import net.kromhouts.HqlBuilder

/**
* Service class that encapsulates the business logic for Asset Reports.
*/
class AssetReportService {

    boolean transactional = false

    def authService
    def dateUtilService
//     def messageSource

    def g = new org.codehaus.groovy.grails.plugins.web.taglib.ApplicationTagLib()

    def paramsMax = 100000

    /**
    * Selects and returns the assets and their details.
    * @param params The request params, may contain params to specify the search.
    * @param locale The locale to use when generating result.message.
    */
    def getAssetRegister(params, locale) {
        def result = [:]

        result.section = Section.get(params.section.id.toLong())
        result.site = result.section.site

        // Inner join used to return only attribTypes that are used by AssetExtendedAttributes.
        // So the result is only asset extendedAttributeTypes.
//         def attribTypesQ = new HqlBuilder().query {
//             select 'distinct attribT.name'
//             from 'AssetExtendedAttribute attrib',
//                     'join attrib.extendedAttributeType as attribT'
//             order 'by attribT.name asc'
//         }

        // All active ExtendedAttributes.
        def attribTypesQ = new HqlBuilder().query {
            select 'distinct attribT.name'
            from 'ExtendedAttributeType attribT'
            where 'attribT.isActive = true'
            order 'by attribT.name asc'
        }
        result.attribTypes = ExtendedAttributeType.executeQuery(attribTypesQ.query, attribTypesQ.namedParams)

        // A result is returned for every asset and for any extended attributes.
        def q = new HqlBuilder().query {
            select 'new map(asset.name as name',
                        'asset.description as description',
                        'asset.comment as comment',
                        'attribT.name as attribType',
                        'attrib.value as attribValue)'
            from 'Asset asset',
                    'left join asset.assetExtendedAttributes as attrib',
                    'left join attrib.extendedAttributeType as attribT'
            where 'asset.section = :section'
                    namedParams.section = result.section
            order 'by asset.name asc, attribT.name asc'
        }
        def assetResults = Asset.executeQuery(q.query, q.namedParams)

        // Build the report table row for each asset.
        // Rows are keyed by asset.name and the value is a Map of the attributes.
        def rows = [:]
        assetResults.each { assetResult ->
            // Create row if it does not exist yet.
            if(!rows.containsKey(assetResult.name)) {
                rows[assetResult.name] = ['name':assetResult.name,
                                                            'description':assetResult.description,
                                                            'comment':assetResult.comment]

                // Add all attribType columns.
                result.attribTypes.each { column ->
                    rows[assetResult.name][column] = ' '
                }
            }

            // Assign value to column.
            rows[assetResult.name][assetResult.attribType] = assetResult.attribValue
        }

        // The value of each row is the dataList used by the report table.
        result.dataList = rows.collect {it.value}

        // Success.
        return result

    } // getAssetRegister

    /**
    * Selects and returns an asset (or all) and its details.
    * @param params The request params, may contain params to specify the search.
    * @param locale The locale to use when generating result.message.
    */
    def getAssetDetail(params, locale) {
        //def result = [:]
        def result

        //result.summaryOfCalculationMethod = ''

        // A result is returned for every asset and for any extended attributes.
        // The report then groups by asset.name
        def q = new HqlBuilder().query {
            select 'new map(asset.name as name',
                        'asset.description as description',
                        'asset.comment as comment',
                        'attribT.name as attribType',
                        'attrib.value as attribValue)'
            from 'Asset asset',
                    'left join asset.assetExtendedAttributes as attrib',
                    'left join attrib.extendedAttributeType as attribT'
            if(params.section instanceof Section) {
                namedParams.section = params.section
                where 'asset.section = :section'
            }
            order 'by asset.name asc, attribT.name asc'
        }

        // result.dataList = Asset.list()
        result = Asset.executeQuery(q.query, q.namedParams)

        // Success.
        return result

    } // getAssetDetail

    /**
    * Selects and returns level 1 sub items (aka machines or equipment) and their details.
    * @param params The request params, may contain params to specify the search.
    * @param locale The locale to use when generating result.message.
    */
    def getEquipmentRegister(params, locale) {
        def result = [:]

        def fail = { Map m ->
            result.error = [ code: m.code, args: [] ]
            return result
        }

        result.section = Section.get(params.section.id.toLong())
        result.site = result.section.site

        result.startDate = params.startDate ?: dateUtilService.oneWeekAgo
        result.endDate = params.endDate ?: dateUtilService.today
        // Auto swap date range.
        if(result.startDate > result.endDate) {
            def tempStartDate = result.startDate
            result.startDate = result.endDate
            result.endDate = tempStartDate
        }

        result.startDateString = g.formatDate(format: "EEE, dd-MMM-yyyy", date: result.startDate)
        result.endDateString = g.formatDate(format: "EEE, dd-MMM-yyyy", date: result.endDate)

        // Inner join used to return only attribTypes that are used by AssetSubItemExtendedAttributes.
        // So the result is only assetSubItem extendedAttributeTypes.
//         def attribTypesQ = new HqlBuilder().query {
//             select 'distinct attribT.name'
//             from 'AssetSubItemExtendedAttribute attrib',
//                     'join attrib.extendedAttributeType as attribT'
//             order 'by attribT.name asc'
//         }

        // All active ExtendedAttributes.
        def attribTypesQ = new HqlBuilder().query {
            select 'distinct attribT.name'
            from 'ExtendedAttributeType attribT'
            where 'attribT.isActive = true'
            order 'by attribT.name asc'
        }
        result.attribTypes = ExtendedAttributeType.executeQuery(attribTypesQ.query, attribTypesQ.namedParams)

        // A useful list of assets without subItems to be given to the user.
        def assetsWithoutEquipmentQ = new HqlBuilder().query {
            select 'distinct asset'
            from 'Asset asset',
                    'left join asset.assetSubItems as assetSubItem'
            where 'assetSubItem = null'
                namedParams.section = result.section
                and 'asset.section = :section'
        }
        result.assetsWithoutEquipment = Asset.executeQuery(assetsWithoutEquipmentQ.query, assetsWithoutEquipmentQ.namedParams)

        // Subquery to count regulatory tasks.
        def regulatoryTaskCountQ = new HqlBuilder().query {

            select 'count (distinct task)'
            from 'Task as task',
                    'left join task.associatedAssets as associatedAsset'
            where 'task.regulatoryRequirement = true'
                and 'task.targetStartDate < :endDate'
                and 'task.targetCompletionDate >= :startDate'
                and '(task.primaryAsset.id = asset.id or associatedAsset.id = asset.id)'
                and 'task.trash = false'
        }

        def totalRegulatoryTaskCountQ = regulatoryTaskCountQ.query

        regulatoryTaskCountQ.and 'task.taskStatus.id = 3'
        def completedRegulatoryTaskCountQ = regulatoryTaskCountQ.query

        // A result is returned for every level 1 assetSubItem and for any extended attributes.
        def q = new HqlBuilder().query {

            select 'new map(asset.name as assetName',
                        'assetSubItem.name as name',
                        'assetSubItem.description as description',
                        'assetSubItem.comment as comment',
                        "0 as totalRegulatoryTaskCount",
                        "0 as completedRegulatoryTaskCount",
                        'attribT.name as attribType',
                        'attrib.value as attribValue)'
            from 'AssetSubItem assetSubItem',
                    'inner join assetSubItem.assets as asset',
                    'left join assetSubItem.assetSubItemExtendedAttributes as attrib',
                    'left join attrib.extendedAttributeType as attribT'
            where 'asset != null' // ensure that only level 1 assetSubItems are returned.
                namedParams.section = result.section
                and 'asset.section = :section'
            order 'by asset.name asc, assetSubItem.name asc, attribT.name asc'
        }
        def equipmentResults = AssetSubItem.executeQuery(q.query, q.namedParams)

        // A result is returned for every asset and for any extended attributes.
        def assetResultsQ = new HqlBuilder().query {

            // Subquery namedParams.
            namedParams.startDate = result.startDate
            namedParams.endDate = result.endDate+1

            select 'new map(asset.name as assetName',
                        "'   Asset Details' as name", // Place holder 'equipment' name, 3 leading spaces for sorting.
                        'asset.description as description',
                        'asset.comment as comment',
                        "($totalRegulatoryTaskCountQ) as totalRegulatoryTaskCount",
                        "($completedRegulatoryTaskCountQ) as completedRegulatoryTaskCount",
                        'attribT.name as attribType',
                        'attrib.value as attribValue)'
            from 'Asset asset',
                    'left join asset.assetExtendedAttributes as attrib',
                    'left join attrib.extendedAttributeType as attribT'
            where 'asset.section = :section'
                    namedParams.section = result.section
            order 'by asset.name asc, attribT.name asc'
        }
        def assetResults = Asset.executeQuery(assetResultsQ.query, assetResultsQ.namedParams)

        // Add asset details to equipmentResults.
        equipmentResults.addAll(assetResults)
        equipmentResults.sort { p1, p2 -> p1.assetName.compareToIgnoreCase(p2.assetName) ?: p1.name.compareToIgnoreCase(p2.name) }

        // Build the report table rows.
        // Rows are keyed by equipmentResult.assetName+equipmentResult.name` while the value is a Map of the attributes.
        // The report table then groups by assetName.
        def rows = [:]
        equipmentResults.each { equipmentResult ->

            def rowKey = equipmentResult.assetName+equipmentResult.name

            // Create new row if it does not exist yet.
            if(!rows.containsKey(rowKey)) {
                rows[rowKey] = ['assetName': equipmentResult.assetName,
                                            'name':equipmentResult.name,
                                            'description':equipmentResult.description,
                                            'comment':equipmentResult.comment,
                                            'Regulatory Task Completion': ' ']

                // Add all attribType columns.
                result.attribTypes.each { column ->
                    rows[rowKey][column] = ' '
                }

                // Caluculate and assign RegulatoryTaskCompletion, only for Assets.
                if(params.calculateRegulatoryTaskCompletion) {

                    if(equipmentResult.totalRegulatoryTaskCount) {
                        def percentComplete = (equipmentResult.completedRegulatoryTaskCount / equipmentResult.totalRegulatoryTaskCount)*100
                        rows[rowKey]['Regulatory Task Completion'] = "${percentComplete.toInteger()}% (${equipmentResult.completedRegulatoryTaskCount}/${equipmentResult.totalRegulatoryTaskCount})"
                    }
                    else if(equipmentResult.name == '   Asset Details')
                        rows[rowKey]['Regulatory Task Completion'] = 'N/A'
                }

            } // Create new row.

            // Assign value to column.
            rows[rowKey][equipmentResult.attribType] = equipmentResult.attribValue
        } // each.

        // The value of each row is the dataList used by the report table.
        result.dataList = rows.collect {it.value}
        // Print formatting, since we are done with these as objects.
        result.attribTypes = result.attribTypes.join(', ')
        result.assetsWithoutEquipment = result.assetsWithoutEquipment.collect {it.name}.join(', ')

        // Success.
        return result

    } // getEquipmentRegister

} // end class