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

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

    /**
    * Selects and returns assets regulatory requirements as specified in recurring regulatory tasks.
    * @param params The request params, may contain params to specify the search.
    * @param locale The locale to use when generating result.message.
    */
    def getRegulatoryRequirements(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)

        result.summary = "This report only selects primary assets and not associated assets. \n"
        result.summary += "Tasks must have recurrence enabled and regulatory requirement set."

        // Subquery to count subTasks..
        def subTaskQ = new HqlBuilder().query {
            select 'count(subTask)'
            from 'task.subTasks as subTask'
            where 'subTask.trash = false'
                and 'subTask.targetStartDate < :endDate'
                and 'subTask.targetCompletionDate >= :startDate'
        }
        def subTaskTotalQ = subTaskQ.query

        subTaskQ.and 'subTask.taskStatus.id = 3' // Complete.
        def subTaskCompletedQ = subTaskQ.query

        def regulatoryTaskQ = new HqlBuilder().query {
            select 'new map(primaryAsset.name as assetName',
                        'primaryAsset.description as assetDescription',
                        'primaryAsset.isActive as assetIsActive',
                        'task.id as taskId',
                        'task.description as taskDescription',
                        "($subTaskTotalQ) as subTaskTotalCount",
                        "($subTaskCompletedQ) as subTaskCompletedCount)"
                        namedParams.startDate = result.startDate
                        namedParams.endDate = result.endDate
            from 'Task task',
                    'left join task.primaryAsset as primaryAsset',
                    'left join task.taskRecurringSchedule as taskRecurringSchedule'
            where 'task.regulatoryRequirement = true'
                and 'taskRecurringSchedule.enabled = true'
                and 'task.trash = false'
                        namedParams.sectionId = result.section.id
                and 'primaryAsset.section.id = :sectionId'
        }
        result.tasks = Task.executeQuery(regulatoryTaskQ.query, regulatoryTaskQ.namedParams)

        // Build the report table row for each task.
        result.tasks.each { task ->

            // Caluculate percentages and build description.
            def percentComplete
            def completionFigures
            if(task.subTaskTotalCount) {
                percentComplete = (task.subTaskCompletedCount / task.subTaskTotalCount) * 100
                task.completionFigures = "${percentComplete.toInteger()}% ($task.subTaskCompletedCount/$task.subTaskTotalCount)"
            }
            else
                task.completionFigures = '0 sub tasks in date range'
        } // tasks.each

        result.dataList = result.tasks
        result.dataList.sort { p1, p2 -> p1.assetName.compareToIgnoreCase(p2.assetName) }

        // Success.
        return result

    } // getRegulatoryRequirements

    /**
    * Selects and returns assets mandatory requirements as specified in recurring mandatory tasks.
    * @param params The request params, may contain params to specify the search.
    * @param locale The locale to use when generating result.message.
    */
    def getMandatoryRequirements(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)

        result.summary = "This report only selects primary assets and not associated assets. \n"
        result.summary += "Tasks must have recurrence enabled and mandatory requirement set."

        // Subquery to count subTasks..
        def subTaskQ = new HqlBuilder().query {
            select 'count(subTask)'
            from 'task.subTasks as subTask'
            where 'subTask.trash = false'
                and 'subTask.targetStartDate < :endDate'
                and 'subTask.targetCompletionDate >= :startDate'
        }
        def subTaskTotalQ = subTaskQ.query

        subTaskQ.and 'subTask.taskStatus.id = 3' // Complete.
        def subTaskCompletedQ = subTaskQ.query

        def mandatoryTaskQ = new HqlBuilder().query {
            select 'new map(primaryAsset.name as assetName',
                        'primaryAsset.description as assetDescription',
                        'primaryAsset.isActive as assetIsActive',
                        'task.id as taskId',
                        'task.description as taskDescription',
                        "($subTaskTotalQ) as subTaskTotalCount",
                        "($subTaskCompletedQ) as subTaskCompletedCount)"
                        namedParams.startDate = result.startDate
                        namedParams.endDate = result.endDate
            from 'Task task',
                    'left join task.primaryAsset as primaryAsset',
                    'left join task.taskRecurringSchedule as taskRecurringSchedule'
            where 'task.mandatoryRequirement = true'
                and 'taskRecurringSchedule.enabled = true'
                and 'task.trash = false'
                        namedParams.sectionId = result.section.id
                and 'primaryAsset.section.id = :sectionId'
        }
        result.tasks = Task.executeQuery(mandatoryTaskQ.query, mandatoryTaskQ.namedParams)

        // Build the report table row for each task.
        result.tasks.each { task ->

            // Caluculate percentages and build description.
            def percentComplete
            def completionFigures
            if(task.subTaskTotalCount) {
                percentComplete = (task.subTaskCompletedCount / task.subTaskTotalCount) * 100
                task.completionFigures = "${percentComplete.toInteger()}% ($task.subTaskCompletedCount/$task.subTaskTotalCount)"
            }
            else
                task.completionFigures = '0 sub tasks in date range'
        } // tasks.each

        result.dataList = result.tasks
        result.dataList.sort { p1, p2 -> p1.assetName.compareToIgnoreCase(p2.assetName) }

        // Success.
        return result

    } // getMandatoryRequirements

} // end class