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

Last change on this file since 967 was 743, checked in by gav, 13 years ago

Add mandatoryRequirements report.

File size: 20.4 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        result.startDate = params.startDate ?: dateUtilService.oneWeekAgo
145        result.endDate = params.endDate ?: dateUtilService.today
146        // Auto swap date range.
147        if(result.startDate > result.endDate) {
148            def tempStartDate = result.startDate
149            result.startDate = result.endDate
150            result.endDate = tempStartDate
151        }
152
153        result.startDateString = g.formatDate(format: "EEE, dd-MMM-yyyy", date: result.startDate)
154        result.endDateString = g.formatDate(format: "EEE, dd-MMM-yyyy", date: result.endDate)
155
156        // Inner join used to return only attribTypes that are used by AssetSubItemExtendedAttributes.
157        // So the result is only assetSubItem extendedAttributeTypes.
158//         def attribTypesQ = new HqlBuilder().query {
159//             select 'distinct attribT.name'
160//             from 'AssetSubItemExtendedAttribute attrib',
161//                     'join attrib.extendedAttributeType as attribT'
162//             order 'by attribT.name asc'
163//         }
164
165        // All active ExtendedAttributes.
166        def attribTypesQ = new HqlBuilder().query {
167            select 'distinct attribT.name'
168            from 'ExtendedAttributeType attribT'
169            where 'attribT.isActive = true'
170            order 'by attribT.name asc'
171        }
172        result.attribTypes = ExtendedAttributeType.executeQuery(attribTypesQ.query, attribTypesQ.namedParams)
173
174        // A useful list of assets without subItems to be given to the user.
175        def assetsWithoutEquipmentQ = new HqlBuilder().query {
176            select 'distinct asset'
177            from 'Asset asset',
178                    'left join asset.assetSubItems as assetSubItem'
179            where 'assetSubItem = null'
180                namedParams.section = result.section
181                and 'asset.section = :section'
182        }
183        result.assetsWithoutEquipment = Asset.executeQuery(assetsWithoutEquipmentQ.query, assetsWithoutEquipmentQ.namedParams)
184
185        // Subquery to count regulatory tasks.
186        def regulatoryTaskCountQ = new HqlBuilder().query {
187
188            select 'count (distinct task)'
189            from 'Task as task',
190                    'left join task.associatedAssets as associatedAsset'
191            where 'task.regulatoryRequirement = true'
192                and 'task.targetStartDate < :endDate'
193                and 'task.targetCompletionDate >= :startDate'
194                and '(task.primaryAsset.id = asset.id or associatedAsset.id = asset.id)'
195                and 'task.trash = false'
196        }
197
198        def totalRegulatoryTaskCountQ = regulatoryTaskCountQ.query
199
200        regulatoryTaskCountQ.and 'task.taskStatus.id = 3'
201        def completedRegulatoryTaskCountQ = regulatoryTaskCountQ.query
202
203        // A result is returned for every level 1 assetSubItem and for any extended attributes.
204        def q = new HqlBuilder().query {
205
206            select 'new map(asset.name as assetName',
207                        'assetSubItem.name as name',
208                        'assetSubItem.description as description',
209                        'assetSubItem.comment as comment',
210                        "0 as totalRegulatoryTaskCount",
211                        "0 as completedRegulatoryTaskCount",
212                        'attribT.name as attribType',
213                        'attrib.value as attribValue)'
214            from 'AssetSubItem assetSubItem',
215                    'inner join assetSubItem.assets as asset',
216                    'left join assetSubItem.assetSubItemExtendedAttributes as attrib',
217                    'left join attrib.extendedAttributeType as attribT'
218            where 'asset != null' // ensure that only level 1 assetSubItems are returned.
219                namedParams.section = result.section
220                and 'asset.section = :section'
221            order 'by asset.name asc, assetSubItem.name asc, attribT.name asc'
222        }
223        def equipmentResults = AssetSubItem.executeQuery(q.query, q.namedParams)
224
225        // A result is returned for every asset and for any extended attributes.
226        def assetResultsQ = new HqlBuilder().query {
227
228            // Subquery namedParams.
229            namedParams.startDate = result.startDate
230            namedParams.endDate = result.endDate+1
231
232            select 'new map(asset.name as assetName',
233                        "'   Asset Details' as name", // Place holder 'equipment' name, 3 leading spaces for sorting.
234                        'asset.description as description',
235                        'asset.comment as comment',
236                        "($totalRegulatoryTaskCountQ) as totalRegulatoryTaskCount",
237                        "($completedRegulatoryTaskCountQ) as completedRegulatoryTaskCount",
238                        'attribT.name as attribType',
239                        'attrib.value as attribValue)'
240            from 'Asset asset',
241                    'left join asset.assetExtendedAttributes as attrib',
242                    'left join attrib.extendedAttributeType as attribT'
243            where 'asset.section = :section'
244                    namedParams.section = result.section
245            order 'by asset.name asc, attribT.name asc'
246        }
247        def assetResults = Asset.executeQuery(assetResultsQ.query, assetResultsQ.namedParams)
248
249        // Add asset details to equipmentResults.
250        equipmentResults.addAll(assetResults)
251        equipmentResults.sort { p1, p2 -> p1.assetName.compareToIgnoreCase(p2.assetName) ?: p1.name.compareToIgnoreCase(p2.name) }
252
253        // Build the report table rows.
254        // Rows are keyed by equipmentResult.assetName+equipmentResult.name` while the value is a Map of the attributes.
255        // The report table then groups by assetName.
256        def rows = [:]
257        equipmentResults.each { equipmentResult ->
258
259            def rowKey = equipmentResult.assetName+equipmentResult.name
260
261            // Create new row if it does not exist yet.
262            if(!rows.containsKey(rowKey)) {
263                rows[rowKey] = ['assetName': equipmentResult.assetName,
264                                            'name':equipmentResult.name,
265                                            'description':equipmentResult.description,
266                                            'comment':equipmentResult.comment,
267                                            'Regulatory Task Completion': ' ']
268
269                // Add all attribType columns.
270                result.attribTypes.each { column ->
271                    rows[rowKey][column] = ' '
272                }
273
274                // Caluculate and assign RegulatoryTaskCompletion, only for Assets.
275                if(params.calculateRegulatoryTaskCompletion) {
276
277                    if(equipmentResult.totalRegulatoryTaskCount) {
278                        def percentComplete = (equipmentResult.completedRegulatoryTaskCount / equipmentResult.totalRegulatoryTaskCount)*100
279                        rows[rowKey]['Regulatory Task Completion'] = "${percentComplete.toInteger()}% (${equipmentResult.completedRegulatoryTaskCount}/${equipmentResult.totalRegulatoryTaskCount})"
280                    }
281                    else if(equipmentResult.name == '   Asset Details')
282                        rows[rowKey]['Regulatory Task Completion'] = 'N/A'
283                }
284
285            } // Create new row.
286
287            // Assign value to column.
288            rows[rowKey][equipmentResult.attribType] = equipmentResult.attribValue
289        } // each.
290
291        // The value of each row is the dataList used by the report table.
292        result.dataList = rows.collect {it.value}
293        // Print formatting, since we are done with these as objects.
294        result.attribTypes = result.attribTypes.join(', ')
295        result.assetsWithoutEquipment = result.assetsWithoutEquipment.collect {it.name}.join(', ')
296
297        // Success.
298        return result
299
300    } // getEquipmentRegister
301
302    /**
303    * Selects and returns assets regulatory requirements as specified in recurring regulatory tasks.
304    * @param params The request params, may contain params to specify the search.
305    * @param locale The locale to use when generating result.message.
306    */
307    def getRegulatoryRequirements(params, locale) {
308        def result = [:]
309
310        def fail = { Map m ->
311            result.error = [ code: m.code, args: [] ]
312            return result
313        }
314
315        result.section = Section.get(params.section.id.toLong())
316        result.site = result.section.site
317
318        result.startDate = params.startDate ?: dateUtilService.oneWeekAgo
319        result.endDate = params.endDate ?: dateUtilService.today
320        // Auto swap date range.
321        if(result.startDate > result.endDate) {
322            def tempStartDate = result.startDate
323            result.startDate = result.endDate
324            result.endDate = tempStartDate
325        }
326
327        result.startDateString = g.formatDate(format: "EEE, dd-MMM-yyyy", date: result.startDate)
328        result.endDateString = g.formatDate(format: "EEE, dd-MMM-yyyy", date: result.endDate)
329
330        result.summary = "This report only selects primary assets and not associated assets. \n"
331        result.summary += "Tasks must have recurrence enabled and regulatory requirement set."
332
333        // Subquery to count subTasks..
334        def subTaskQ = new HqlBuilder().query {
335            select 'count(subTask)'
336            from 'task.subTasks as subTask'
337            where 'subTask.trash = false'
338                and 'subTask.targetStartDate < :endDate'
339                and 'subTask.targetCompletionDate >= :startDate'
340        }
341        def subTaskTotalQ = subTaskQ.query
342
343        subTaskQ.and 'subTask.taskStatus.id = 3' // Complete.
344        def subTaskCompletedQ = subTaskQ.query
345
346        def regulatoryTaskQ = new HqlBuilder().query {
347            select 'new map(primaryAsset.name as assetName',
348                        'primaryAsset.description as assetDescription',
349                        'primaryAsset.isActive as assetIsActive',
350                        'task.id as taskId',
351                        'task.description as taskDescription',
352                        "($subTaskTotalQ) as subTaskTotalCount",
353                        "($subTaskCompletedQ) as subTaskCompletedCount)"
354                        namedParams.startDate = result.startDate
355                        namedParams.endDate = result.endDate
356            from 'Task task',
357                    'left join task.primaryAsset as primaryAsset',
358                    'left join task.taskRecurringSchedule as taskRecurringSchedule'
359            where 'task.regulatoryRequirement = true'
360                and 'taskRecurringSchedule.enabled = true'
361                and 'task.trash = false'
362                        namedParams.sectionId = result.section.id
363                and 'primaryAsset.section.id = :sectionId'
364        }
365        result.tasks = Task.executeQuery(regulatoryTaskQ.query, regulatoryTaskQ.namedParams)
366
367        // Build the report table row for each task.
368        result.tasks.each { task ->
369
370            // Caluculate percentages and build description.
371            def percentComplete
372            def completionFigures
373            if(task.subTaskTotalCount) {
374                percentComplete = (task.subTaskCompletedCount / task.subTaskTotalCount) * 100
375                task.completionFigures = "${percentComplete.toInteger()}% ($task.subTaskCompletedCount/$task.subTaskTotalCount)"
376            }
377            else
378                task.completionFigures = '0 sub tasks in date range'
379        } // tasks.each
380
381        result.dataList = result.tasks
382        result.dataList.sort { p1, p2 -> p1.assetName.compareToIgnoreCase(p2.assetName) }
383
384        // Success.
385        return result
386
387    } // getRegulatoryRequirements
388
389    /**
390    * Selects and returns assets mandatory requirements as specified in recurring mandatory tasks.
391    * @param params The request params, may contain params to specify the search.
392    * @param locale The locale to use when generating result.message.
393    */
394    def getMandatoryRequirements(params, locale) {
395        def result = [:]
396
397        def fail = { Map m ->
398            result.error = [ code: m.code, args: [] ]
399            return result
400        }
401
402        result.section = Section.get(params.section.id.toLong())
403        result.site = result.section.site
404
405        result.startDate = params.startDate ?: dateUtilService.oneWeekAgo
406        result.endDate = params.endDate ?: dateUtilService.today
407        // Auto swap date range.
408        if(result.startDate > result.endDate) {
409            def tempStartDate = result.startDate
410            result.startDate = result.endDate
411            result.endDate = tempStartDate
412        }
413
414        result.startDateString = g.formatDate(format: "EEE, dd-MMM-yyyy", date: result.startDate)
415        result.endDateString = g.formatDate(format: "EEE, dd-MMM-yyyy", date: result.endDate)
416
417        result.summary = "This report only selects primary assets and not associated assets. \n"
418        result.summary += "Tasks must have recurrence enabled and mandatory requirement set."
419
420        // Subquery to count subTasks..
421        def subTaskQ = new HqlBuilder().query {
422            select 'count(subTask)'
423            from 'task.subTasks as subTask'
424            where 'subTask.trash = false'
425                and 'subTask.targetStartDate < :endDate'
426                and 'subTask.targetCompletionDate >= :startDate'
427        }
428        def subTaskTotalQ = subTaskQ.query
429
430        subTaskQ.and 'subTask.taskStatus.id = 3' // Complete.
431        def subTaskCompletedQ = subTaskQ.query
432
433        def mandatoryTaskQ = new HqlBuilder().query {
434            select 'new map(primaryAsset.name as assetName',
435                        'primaryAsset.description as assetDescription',
436                        'primaryAsset.isActive as assetIsActive',
437                        'task.id as taskId',
438                        'task.description as taskDescription',
439                        "($subTaskTotalQ) as subTaskTotalCount",
440                        "($subTaskCompletedQ) as subTaskCompletedCount)"
441                        namedParams.startDate = result.startDate
442                        namedParams.endDate = result.endDate
443            from 'Task task',
444                    'left join task.primaryAsset as primaryAsset',
445                    'left join task.taskRecurringSchedule as taskRecurringSchedule'
446            where 'task.mandatoryRequirement = true'
447                and 'taskRecurringSchedule.enabled = true'
448                and 'task.trash = false'
449                        namedParams.sectionId = result.section.id
450                and 'primaryAsset.section.id = :sectionId'
451        }
452        result.tasks = Task.executeQuery(mandatoryTaskQ.query, mandatoryTaskQ.namedParams)
453
454        // Build the report table row for each task.
455        result.tasks.each { task ->
456
457            // Caluculate percentages and build description.
458            def percentComplete
459            def completionFigures
460            if(task.subTaskTotalCount) {
461                percentComplete = (task.subTaskCompletedCount / task.subTaskTotalCount) * 100
462                task.completionFigures = "${percentComplete.toInteger()}% ($task.subTaskCompletedCount/$task.subTaskTotalCount)"
463            }
464            else
465                task.completionFigures = '0 sub tasks in date range'
466        } // tasks.each
467
468        result.dataList = result.tasks
469        result.dataList.sort { p1, p2 -> p1.assetName.compareToIgnoreCase(p2.assetName) }
470
471        // Success.
472        return result
473
474    } // getMandatoryRequirements
475
476} // end class
Note: See TracBrowser for help on using the repository browser.