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

Last change on this file since 730 was 728, checked in by gav, 14 years ago

Domain change, split Task.mandatoryRegulatory into regulatoryRequirement and mandatoryRequirement.

File size: 13.0 KB
Line 
1import net.kromhouts.HqlBuilder
2
3/**
4* Service class that encapsulates the business logic for Asset Reports.
5*/
6class AssetReportService {
7
8    boolean transactional = false
9
10    def authService
11    def dateUtilService
12//     def messageSource
13
14    def g = new org.codehaus.groovy.grails.plugins.web.taglib.ApplicationTagLib()
15
16    def paramsMax = 100000
17
18    /**
19    * Selects and returns the assets and their details.
20    * @param params The request params, may contain params to specify the search.
21    * @param locale The locale to use when generating result.message.
22    */
23    def getAssetRegister(params, locale) {
24        def result = [:]
25
26        result.section = Section.get(params.section.id.toLong())
27        result.site = result.section.site
28
29        // Inner join used to return only attribTypes that are used by AssetExtendedAttributes.
30        // So the result is only asset extendedAttributeTypes.
31//         def attribTypesQ = new HqlBuilder().query {
32//             select 'distinct attribT.name'
33//             from 'AssetExtendedAttribute attrib',
34//                     'join attrib.extendedAttributeType as attribT'
35//             order 'by attribT.name asc'
36//         }
37
38        // All active ExtendedAttributes.
39        def attribTypesQ = new HqlBuilder().query {
40            select 'distinct attribT.name'
41            from 'ExtendedAttributeType attribT'
42            where 'attribT.isActive = true'
43            order 'by attribT.name asc'
44        }
45        result.attribTypes = ExtendedAttributeType.executeQuery(attribTypesQ.query, attribTypesQ.namedParams)
46
47        // A result is returned for every asset and for any extended attributes.
48        def q = new HqlBuilder().query {
49            select 'new map(asset.name as name',
50                        'asset.description as description',
51                        'asset.comment as comment',
52                        'attribT.name as attribType',
53                        'attrib.value as attribValue)'
54            from 'Asset asset',
55                    'left join asset.assetExtendedAttributes as attrib',
56                    'left join attrib.extendedAttributeType as attribT'
57            where 'asset.section = :section'
58                    namedParams.section = result.section
59            order 'by asset.name asc, attribT.name asc'
60        }
61        def assetResults = Asset.executeQuery(q.query, q.namedParams)
62
63        // Build the report table row for each asset.
64        // Rows are keyed by asset.name and the value is a Map of the attributes.
65        def rows = [:]
66        assetResults.each { assetResult ->
67            // Create row if it does not exist yet.
68            if(!rows.containsKey(assetResult.name)) {
69                rows[assetResult.name] = ['name':assetResult.name,
70                                                            'description':assetResult.description,
71                                                            'comment':assetResult.comment]
72
73                // Add all attribType columns.
74                result.attribTypes.each { column ->
75                    rows[assetResult.name][column] = ' '
76                }
77            }
78
79            // Assign value to column.
80            rows[assetResult.name][assetResult.attribType] = assetResult.attribValue
81        }
82
83        // The value of each row is the dataList used by the report table.
84        result.dataList = rows.collect {it.value}
85
86        // Success.
87        return result
88
89    } // getAssetRegister
90
91    /**
92    * Selects and returns an asset (or all) and its details.
93    * @param params The request params, may contain params to specify the search.
94    * @param locale The locale to use when generating result.message.
95    */
96    def getAssetDetail(params, locale) {
97        //def result = [:]
98        def result
99
100        //result.summaryOfCalculationMethod = ''
101
102        // A result is returned for every asset and for any extended attributes.
103        // The report then groups by asset.name
104        def q = new HqlBuilder().query {
105            select 'new map(asset.name as name',
106                        'asset.description as description',
107                        'asset.comment as comment',
108                        'attribT.name as attribType',
109                        'attrib.value as attribValue)'
110            from 'Asset asset',
111                    'left join asset.assetExtendedAttributes as attrib',
112                    'left join attrib.extendedAttributeType as attribT'
113            if(params.section instanceof Section) {
114                namedParams.section = params.section
115                where 'asset.section = :section'
116            }
117            order 'by asset.name asc, attribT.name asc'
118        }
119
120        // result.dataList = Asset.list()
121        result = Asset.executeQuery(q.query, q.namedParams)
122
123        // Success.
124        return result
125
126    } // getAssetDetail
127
128    /**
129    * Selects and returns level 1 sub items (aka machines or equipment) and their details.
130    * @param params The request params, may contain params to specify the search.
131    * @param locale The locale to use when generating result.message.
132    */
133    def getEquipmentRegister(params, locale) {
134        def result = [:]
135
136        def fail = { Map m ->
137            result.error = [ code: m.code, args: [] ]
138            return result
139        }
140
141        result.section = Section.get(params.section.id.toLong())
142        result.site = result.section.site
143
144        // Start date.
145        if(params.startDate)
146            params.startDate = dateUtilService.makeDate(params.startDate_year, params.startDate_month, params.startDate_day)
147        else
148            params.startDate = dateUtilService.oneWeekAgo
149
150        // End date.
151        if(params.endDate)
152            params.endDate = dateUtilService.makeDate(params.endDate_year, params.endDate_month, params.endDate_day)
153        else
154            params.endDate = dateUtilService.today
155
156        if(params.endDate < params.startDate)
157            return fail(code: "default.end.date.before.start.date")
158
159        // Inner join used to return only attribTypes that are used by AssetSubItemExtendedAttributes.
160        // So the result is only assetSubItem extendedAttributeTypes.
161//         def attribTypesQ = new HqlBuilder().query {
162//             select 'distinct attribT.name'
163//             from 'AssetSubItemExtendedAttribute attrib',
164//                     'join attrib.extendedAttributeType as attribT'
165//             order 'by attribT.name asc'
166//         }
167
168        // All active ExtendedAttributes.
169        def attribTypesQ = new HqlBuilder().query {
170            select 'distinct attribT.name'
171            from 'ExtendedAttributeType attribT'
172            where 'attribT.isActive = true'
173            order 'by attribT.name asc'
174        }
175        result.attribTypes = ExtendedAttributeType.executeQuery(attribTypesQ.query, attribTypesQ.namedParams)
176
177        // A useful list of assets without subItems to be given to the user.
178        def assetsWithoutEquipmentQ = new HqlBuilder().query {
179            select 'distinct asset'
180            from 'Asset asset',
181                    'left join asset.assetSubItems as assetSubItem'
182            where 'assetSubItem = null'
183                namedParams.section = result.section
184                and 'asset.section = :section'
185        }
186        result.assetsWithoutEquipment = AssetSubItem.executeQuery(assetsWithoutEquipmentQ.query, assetsWithoutEquipmentQ.namedParams)
187
188        // Subquery to count regulatory tasks.
189        def regulatoryTaskCountQ = new HqlBuilder().query {
190
191            select 'count (distinct task)'
192            from 'Task as task',
193                    'left join task.associatedAssets as associatedAsset'
194            where 'task.regulatoryRequirement = true'
195                and 'task.targetStartDate < :endDate'
196                and 'task.targetCompletionDate >= :startDate'
197                and '(task.primaryAsset.id = asset.id or associatedAsset.id = asset.id)'
198                and 'task.trash = false'
199        }
200
201        def totalRegulatoryTaskCountQ = regulatoryTaskCountQ.query
202
203        regulatoryTaskCountQ.and 'task.taskStatus.id = 3'
204        def completedRegulatoryTaskCountQ = regulatoryTaskCountQ.query
205
206        // A result is returned for every level 1 assetSubItem and for any extended attributes.
207        def q = new HqlBuilder().query {
208
209            select 'new map(asset.name as assetName',
210                        'assetSubItem.name as name',
211                        'assetSubItem.description as description',
212                        'assetSubItem.comment as comment',
213                        "0 as totalRegulatoryTaskCount",
214                        "0 as completedRegulatoryTaskCount",
215                        'attribT.name as attribType',
216                        'attrib.value as attribValue)'
217            from 'AssetSubItem assetSubItem',
218                    'inner join assetSubItem.assets as asset',
219                    'left join assetSubItem.assetSubItemExtendedAttributes as attrib',
220                    'left join attrib.extendedAttributeType as attribT'
221            where 'asset != null' // ensure that only level 1 assetSubItems are returned.
222                namedParams.section = result.section
223                and 'asset.section = :section'
224            order 'by asset.name asc, assetSubItem.name asc, attribT.name asc'
225        }
226        def equipmentResults = AssetSubItem.executeQuery(q.query, q.namedParams)
227
228        // A result is returned for every asset and for any extended attributes.
229        def assetResultsQ = new HqlBuilder().query {
230
231            // Subquery namedParams.
232            namedParams.startDate = params.startDate
233            namedParams.endDate = params.endDate+1
234
235            select 'new map(asset.name as assetName',
236                        "'   Asset Details' as name", // Place holder 'equipment' name, 3 leading spaces for sorting.
237                        'asset.description as description',
238                        'asset.comment as comment',
239                        "($totalRegulatoryTaskCountQ) as totalRegulatoryTaskCount",
240                        "($completedRegulatoryTaskCountQ) as completedRegulatoryTaskCount",
241                        'attribT.name as attribType',
242                        'attrib.value as attribValue)'
243            from 'Asset asset',
244                    'left join asset.assetExtendedAttributes as attrib',
245                    'left join attrib.extendedAttributeType as attribT'
246            where 'asset.section = :section'
247                    namedParams.section = result.section
248            order 'by asset.name asc, attribT.name asc'
249        }
250        def assetResults = Asset.executeQuery(assetResultsQ.query, assetResultsQ.namedParams)
251
252        // Add asset details to equipmentResults.
253        equipmentResults.addAll(assetResults)
254        equipmentResults.sort { p1, p2 -> p1.assetName.compareToIgnoreCase(p2.assetName) ?: p1.name.compareToIgnoreCase(p2.name) }
255
256        // Build the report table rows.
257        // Rows are keyed by equipmentResult.assetName+equipmentResult.name` while the value is a Map of the attributes.
258        // The report table then groups by assetName.
259        def rows = [:]
260        equipmentResults.each { equipmentResult ->
261
262            def rowKey = equipmentResult.assetName+equipmentResult.name
263
264            // Create new row if it does not exist yet.
265            if(!rows.containsKey(rowKey)) {
266                rows[rowKey] = ['assetName': equipmentResult.assetName,
267                                            'name':equipmentResult.name,
268                                            'description':equipmentResult.description,
269                                            'comment':equipmentResult.comment,
270                                            'Regulatory Task Completion': ' ']
271
272                // Add all attribType columns.
273                result.attribTypes.each { column ->
274                    rows[rowKey][column] = ' '
275                }
276
277                // Caluculate and assign RegulatoryTaskCompletion, only for Assets.
278                if(params.calculateRegulatoryTaskCompletion) {
279
280                    if(equipmentResult.totalRegulatoryTaskCount) {
281                        def percentComplete = (equipmentResult.completedRegulatoryTaskCount / equipmentResult.totalRegulatoryTaskCount)*100
282                        rows[rowKey]['Regulatory Task Completion'] = "${percentComplete.toInteger()}% (${equipmentResult.completedRegulatoryTaskCount}/${equipmentResult.totalRegulatoryTaskCount})"
283                    }
284                    else if(equipmentResult.name == '   Asset Details')
285                        rows[rowKey]['Regulatory Task Completion'] = 'N/A'
286                }
287
288            } // Create new row.
289
290            // Assign value to column.
291            rows[rowKey][equipmentResult.attribType] = equipmentResult.attribValue
292        } // each.
293
294        // The value of each row is the dataList used by the report table.
295        result.dataList = rows.collect {it.value}
296        // Print formatting, since we are done with these as objects.
297        result.attribTypes = result.attribTypes.join(', ')
298        result.assetsWithoutEquipment = result.assetsWithoutEquipment.collect {it.name}.join(', ')
299
300        // Success.
301        return result
302
303    } // getEquipmentRegister
304
305} // end class
Note: See TracBrowser for help on using the repository browser.