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 = [:] // 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' } result.attribTypes = Asset.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' if(params.section instanceof Section) { namedParams.section = params.section where 'asset.section = :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] = null } } // 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.asset instanceof Asset) { namedParams.asset = params.asset where 'asset = :asset' } 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 = [:] // 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' } result.attribTypes = Asset.executeQuery(attribTypesQ.query, attribTypesQ.namedParams) // Since there will be nothing to show in the report table for assets without level 1 assetSubItems, // a list needs 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' } result.assetsWithoutEquipment = AssetSubItem.executeQuery(assetsWithoutEquipmentQ.query, assetsWithoutEquipmentQ.namedParams) // 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', '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. if(params.section instanceof Section) { namedParams.section = params.section and 'asset.section = :section' } order 'by asset.name asc, assetSubItem.name asc, attribT.name asc' } def equipmentResults = AssetSubItem.executeQuery(q.query, q.namedParams) // 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 -> // Create row if it does not exist yet. def rowKey = equipmentResult.assetName+equipmentResult.name if(!rows.containsKey(rowKey)) { rows[rowKey] = ['assetName': equipmentResult.assetName, 'name':equipmentResult.name, 'description':equipmentResult.description, 'comment':equipmentResult.comment] // Add all attribType columns. result.attribTypes.each { column -> rows[rowKey][column] = null } } // Assign value to column. rows[rowKey][equipmentResult.attribType] = equipmentResult.attribValue } // 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