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

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

Tweak asset and equipment register reports to show blank strings in empty cells.

File size: 10.3 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.asset instanceof Asset) {
114                namedParams.asset = params.asset
115                where 'asset = :asset'
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        result.section = Section.get(params.section.id.toLong())
137        result.site = result.section.site
138
139        // Inner join used to return only attribTypes that are used by AssetSubItemExtendedAttributes.
140        // So the result is only assetSubItem extendedAttributeTypes.
141//         def attribTypesQ = new HqlBuilder().query {
142//             select 'distinct attribT.name'
143//             from 'AssetSubItemExtendedAttribute attrib',
144//                     'join attrib.extendedAttributeType as attribT'
145//             order 'by attribT.name asc'
146//         }
147
148        // All active ExtendedAttributes.
149        def attribTypesQ = new HqlBuilder().query {
150            select 'distinct attribT.name'
151            from 'ExtendedAttributeType attribT'
152            where 'attribT.isActive = true'
153            order 'by attribT.name asc'
154        }
155        result.attribTypes = ExtendedAttributeType.executeQuery(attribTypesQ.query, attribTypesQ.namedParams)
156
157        // A useful list of assets without subItems to be given to the user.
158        def assetsWithoutEquipmentQ = new HqlBuilder().query {
159            select 'distinct asset'
160            from 'Asset asset',
161                    'left join asset.assetSubItems as assetSubItem'
162            where 'assetSubItem = null'
163                namedParams.section = result.section
164                and 'asset.section = :section'
165        }
166        result.assetsWithoutEquipment = AssetSubItem.executeQuery(assetsWithoutEquipmentQ.query, assetsWithoutEquipmentQ.namedParams)
167
168        // A result is returned for every level 1 assetSubItem and for any extended attributes.
169        def q = new HqlBuilder().query {
170            select 'new map(asset.name as assetName',
171                        'assetSubItem.name as name',
172                        'assetSubItem.description as description',
173                        'assetSubItem.comment as comment',
174                        'attribT.name as attribType',
175                        'attrib.value as attribValue)'
176            from 'AssetSubItem assetSubItem',
177                    'inner join assetSubItem.assets as asset',
178                    'left join assetSubItem.assetSubItemExtendedAttributes as attrib',
179                    'left join attrib.extendedAttributeType as attribT'
180            where 'asset != null' // ensure that only level 1 assetSubItems are returned.
181                namedParams.section = result.section
182                and 'asset.section = :section'
183            order 'by asset.name asc, assetSubItem.name asc, attribT.name asc'
184        }
185        def equipmentResults = AssetSubItem.executeQuery(q.query, q.namedParams)
186
187        // A result is returned for every asset and for any extended attributes.
188        def assetResultsQ = new HqlBuilder().query {
189            select 'new map(asset.name as assetName',
190                        "'   Asset Details' as name", // Place holder 'equipment' name, 3 leading spaces for sorting.
191                        'asset.description as description',
192                        'asset.comment as comment',
193                        'attribT.name as attribType',
194                        'attrib.value as attribValue)'
195            from 'Asset asset',
196                    'left join asset.assetExtendedAttributes as attrib',
197                    'left join attrib.extendedAttributeType as attribT'
198            where 'asset.section = :section'
199                    namedParams.section = result.section
200            order 'by asset.name asc, attribT.name asc'
201        }
202        def assetResults = Asset.executeQuery(assetResultsQ.query, assetResultsQ.namedParams)
203
204        // Add asset details to equipmentResults.
205        equipmentResults.addAll(assetResults)
206        equipmentResults.sort { p1, p2 -> p1.assetName.compareToIgnoreCase(p2.assetName) ?: p1.name.compareToIgnoreCase(p2.name) }
207
208        // Build the report table rows.
209        // Rows are keyed by equipmentResult.assetName+equipmentResult.name` while the value is a Map of the attributes.
210        // The report table then groups by assetName.
211        def rows = [:]
212        equipmentResults.each { equipmentResult ->
213            // Create row if it does not exist yet.
214            def rowKey = equipmentResult.assetName+equipmentResult.name
215            if(!rows.containsKey(rowKey)) {
216                rows[rowKey] = ['assetName': equipmentResult.assetName,
217                                                            'name':equipmentResult.name,
218                                                            'description':equipmentResult.description,
219                                                            'comment':equipmentResult.comment]
220
221                // Add all attribType columns.
222                result.attribTypes.each { column ->
223                    rows[rowKey][column] = ' '
224                }
225            }
226
227            // Assign value to column.
228            rows[rowKey][equipmentResult.attribType] = equipmentResult.attribValue
229        }
230
231        // The value of each row is the dataList used by the report table.
232        result.dataList = rows.collect {it.value}
233        // Print formatting, since we are done with these as objects.
234        result.attribTypes = result.attribTypes.join(', ')
235        result.assetsWithoutEquipment = result.assetsWithoutEquipment.collect {it.name}.join(', ')
236
237        // Success.
238        return result
239
240    } // getEquipmentRegister
241
242} // end class
Note: See TracBrowser for help on using the repository browser.