source: trunk/grails-app/services/InventoryReportService.groovy @ 808

Last change on this file since 808 was 690, checked in by gav, 14 years ago

Fix for ticket #85, Null pointer bug on inventory reports.
File size: 16.5 KB
Line 
1
2import net.kromhouts.HqlBuilder
3
4/**
5* Service class that encapsulates the business logic for Inventory Reports.
6*/
7class InventoryReportService {
8
9    boolean transactional = false
10
11//     def authService
12//     def dateUtilService
13//     def messageSource
14
15    def g = new org.codehaus.groovy.grails.plugins.web.taglib.ApplicationTagLib()
16
17    // Protect java heap memory.
18    // Most likely want to set paramsMax and inClauseMax to the same values.
19    def paramsMax = 250
20
21    // At least with Oracle and MSSQL db limits are 1000 (in list) and 2000 (nodes) respectively.
22    // But 255 has also been mentioned on the internet as a possible limit for some databases.
23    def inClauseMax = 250
24
25    /**
26    * Get the data for the inventory stock take overiew report.
27    * @param params The request params, may contain params to specify the search.
28    * @param locale The locale to use when generating result.message.
29    */
30    def getStockTakeOverview(params, locale) {
31        def result = [:]
32
33        result.summaryOfCalculationMethod = 'This report should be used in conjunction with the `Stock Take (By Location)` Report.'
34
35        def namedParams = [:]
36
37        result.query = "from InventoryLocation as inventoryLocation \
38                                        left join inventoryLocation.inventoryStore as inventoryStore \
39                                        where (inventoryLocation.isActive = true \
40                                                    ) \
41                                        order by inventoryStore.name, inventoryLocation.name"
42
43        result.query = "select new Map(inventoryLocation.name as location, inventoryStore.name as store) " + result.query
44        result.queryResult = InventoryLocation.executeQuery(result.query, namedParams)
45        result.inventoryLocationCount = result.queryResult.size()
46
47        result.inventoryLocationList = result.queryResult
48
49        // Success.
50        return result
51
52    } // getStockTakeOverview()
53
54    /**
55    * Get the data for the inventory stock take by location report.
56    * @param params The request params, may contain params to specify the search.
57    * @param locale The locale to use when generating result.message.
58    */
59    def getStockTakeByLocation(params, locale) {
60        def result = [:]
61
62        result.inventoryItemList = []
63        result.inventoryItemCount = 0
64        result.locationCount = 0
65        result.errorMessage = null
66        result.summaryOfCalculationMethod = 'This report should be used in conjunction with the `Stock Take (Overview)` Report.'
67
68        def fail = { Map m ->
69            result.error = [ code: m.code, args: m.args ]
70            result.errorMessage = g.message(result.error)
71            result.locations = ''
72            return result
73        }
74
75        def paginateParams = [:]
76        paginateParams.max = Math.min(params?.max?.toInteger() ?: paramsMax, paramsMax)
77
78        def namedParams = [:]
79        namedParams.locationList = []
80
81        // Sanitise the user supplied locations string and convert to a list.
82        result.locations = params.locationString.trim()
83        if(result.locations.startsWith('e.g:'))
84            result.locations = result.locations.split(':')[-1].trim()
85        result.locations = result.locations.split(',')
86        result.locations = result.locations.collect {it.trim()}
87
88        // Fill namedParams.locationList.
89        result.locations.each() { location ->
90            if(namedParams.locationList.size() < paramsMax) {
91                // paramsMax+1 to ensure the too many locations check bellow is triggered.
92                namedParams.locationList += InventoryLocation.findAllByNameIlike(location, [max: paramsMax+1])
93            }
94            namedParams.locationList.unique()
95        }
96
97        // Return the actual locations as a string, along with a count.
98        result.locationCount = namedParams.locationList.size()
99        if(result.locationCount > 0) {
100            namedParams.locationList.sort { p1, p2 -> p1.name.compareToIgnoreCase(p2.name) }
101            result.locations = namedParams.locationList.toString()[1..-2]
102        }
103        else
104            result.locations = g.message(code: 'default.none.text')
105
106        // Exit if empty location list.
107        // Protects against HQL unexpected end of subtree exception with an empty list.
108        if(namedParams.locationList.isEmpty())
109            return fail(code:'report.error.no.locations.found')
110
111        // Exit if IN clause list too big.
112        if(namedParams.locationList.size() > inClauseMax)
113            return fail(code:'report.error.too.many.locations', args: [inClauseMax])
114
115        // Inventory List.
116        result.inventoryListQuery = "from InventoryItem as inventoryItem \
117                                                        left join inventoryItem.inventoryLocation as inventoryLocation \
118                                                        where (inventoryItem.isActive = true \
119                                                                    and  inventoryItem.inventoryLocation in (:locationList) \
120                                                                    ) "
121
122        result.inventoryCountQuery = "select count(distinct inventoryItem) " + result.inventoryListQuery
123        result.inventoryItemCount = InventoryItem.executeQuery(result.inventoryCountQuery, namedParams)[0]
124
125        // Exit if too many results.
126        if(result.inventoryItemCount > paramsMax) 
127            return fail(code:'report.error.too.many.results', args: [paramsMax])
128
129        result.inventoryListQuery = "select distinct inventoryItem " + result.inventoryListQuery
130        def inventoryList = InventoryItem.executeQuery(result.inventoryListQuery, namedParams, paginateParams)
131
132        // Reset namedParams for next query.
133        namedParams = [:]
134        namedParams.inventoryList = inventoryList
135
136        // Exit if empty inventory list.
137        // Protects against HQL unexpected end of subtree exception with an empty list.
138        if(namedParams.inventoryList.isEmpty())
139            return fail(code:'report.error.no.inventory.items.found')
140
141        // Exit if inventory list too big.
142        if(namedParams.inventoryList.size() > inClauseMax)
143            return fail(code:'report.error.too.many.inventory.items', args: [inClauseMax])
144
145        // Note: HQL docs advise 'not using fetch aliases in where clause (or any other clause)'.
146        // Access is via the parent object, however that does not work for the order by clause in this case.
147        result.query = "from InventoryItem as inventoryItem \
148                                        left join fetch inventoryItem.unitOfMeasure as unitOfMeasure \
149                                        left join fetch inventoryItem.inventoryLocation as inventoryLocation \
150                                        left join fetch inventoryLocation.inventoryStore as inventoryStore \
151                                        left join fetch inventoryItem.picture as picture \
152                                        left join fetch picture.images as Image \
153                                        where (inventoryItem in (:inventoryList) \
154                                                    ) \
155                                        order by inventoryStore.name, inventoryLocation.name"
156
157        // MSSQL will not do distinct here, for some reason it tries to compare the image data type!
158        result.query = "select inventoryItem " + result.query
159        result.inventoryItemList = InventoryItem.executeQuery(result.query, namedParams, paginateParams)
160
161        result.inventoryItemList.unique()
162
163        // Success.
164        return result
165
166    } // getStockTakeOverview()
167
168    /**
169    * Get the data for the inventory value with detail.
170    * @param params The request params, may contain params to specify the search.
171    * @param locale The locale to use when generating result.message.
172    */
173    def getInventoryValueDetailed(params, locale) {
174        def result = [:]
175
176        result.inventoryItemList = []
177        result.inventoryItemCount = 0
178        result.inventoryItemTotalValue = new BigDecimal(0)
179        result.currency = null
180        result.errorMessage = null
181        result.summaryOfCalculationMethod = "This report does not convert between different currency.\n"
182        result.summaryOfCalculationMethod += "Therefore all item's are checked to ensure that currency is the same."
183
184        result.site = Site.get(params.site.id.toLong())
185
186        result.inventoryTypes = []
187        if(params.inventoryTypes instanceof String)
188            result.inventoryTypes << InventoryType.get(params.inventoryTypes.toInteger())
189        else if(params.inventoryTypes)
190            result.inventoryTypes = params.inventoryTypes.collect { InventoryType.get(it.toInteger()) }
191        else
192            result.inventoryTypes = InventoryType.findAllByIsActive(true, [max:254, sort:'name'])
193
194        result.inventoryGroups = []
195        if(params.inventoryGroups instanceof String)
196            result.inventoryGroups << InventoryGroup.get(params.inventoryGroups.toInteger())
197        else if(params.inventoryGroups)
198            result.inventoryGroups = params.inventoryGroups.collect { InventoryGroup.get(it.toInteger()) }
199        else
200            result.inventoryGroups = InventoryGroup.findAllByIsActive(true, [max:254, sort:'name'])
201
202        def fail = { Map m ->
203            result.error = [ code: m.code, args: m.args ]
204            result.errorMessage = g.message(result.error)
205            result.currency = null
206            result.inventoryItemTotalValue = new BigDecimal(0)
207            return result
208        }
209
210        def q = new HqlBuilder().query {
211            select 'distinct inventoryItem'
212            from 'InventoryItem as inventoryItem',
213                    'left join fetch inventoryItem.inventoryLocation as inventoryLocation',
214                    'left join fetch inventoryLocation.inventoryStore as inventoryStore',
215                    'left join fetch inventoryItem.unitOfMeasure as unitOfMeasure'
216            where 'inventoryItem.isActive = true'
217                namedParams.siteId = result.site.id
218                and 'inventoryStore.site.id = :siteId'
219                if(result.inventoryTypes) {
220                    namedParams.inventoryTypeIds = result.inventoryTypes.collect {it.id}
221                    and 'inventoryItem.inventoryType.id in(:inventoryTypeIds)'
222                }
223                if(result.inventoryGroups) {
224                    namedParams.inventoryGroupIds = result.inventoryGroups.collect {it.id}
225                    and 'inventoryItem.inventoryGroup.id in(:inventoryGroupIds)'
226                }
227            order 'by inventoryItem.name asc'
228        }
229
230        result.inventoryItemList = InventoryItem.executeQuery(q.query, q.namedParams)
231        result.inventoryItemCount = result.inventoryItemList.size()
232        result.currency = result.inventoryItemList[0]?.estimatedUnitPriceCurrency
233
234        for(inventoryItem in result.inventoryItemList) {
235            // Check all currency is the same.
236            if(result.currency != inventoryItem.estimatedUnitPriceCurrency) {
237                fail(code:'report.error.multiple.currency.found') // No return, populate errors but continue report.
238                break
239            }
240            if(inventoryItem.estimatedUnitPriceAmount && inventoryItem.unitsInStock) // Some items have null estimatedUnitPriceAmount.
241                result.inventoryItemTotalValue += inventoryItem.estimatedUnitPriceAmount * inventoryItem.unitsInStock
242        } // for
243
244        /// @todo: This protects against a bug in the report layout, remove when solved.
245        if(result.inventoryTypes.size() > 4)
246            result.inventoryTypes = [[name:'More than 4']]
247
248        if(result.inventoryGroups.size() > 4)
249            result.inventoryGroups =  [[name:'More than 4']]
250
251        // Success.
252        return result
253
254    } // getInventoryValueDetailed()
255
256    /**
257    * Get the data for the inventory overiew value.
258    * @param params The request params, may contain params to specify the search.
259    * @param locale The locale to use when generating result.message.
260    */
261    def getInventoryValueOverview(params, locale) {
262        def result = [:]
263
264        result.inventoryItemCount = 0
265        result.inventoryItemTotalValue = new BigDecimal(0)
266        result.currency = null
267        result.errorMessage = null
268        result.summaryOfCalculationMethod = "This report does not convert between different currency.\n"
269        result.summaryOfCalculationMethod += "Therefore all item's are checked to ensure that currency is the same."
270
271        result.site = Site.get(params.site.id.toLong())
272
273        result.inventoryTypes = []
274        if(params.inventoryTypes instanceof String)
275            result.inventoryTypes << InventoryType.get(params.inventoryTypes.toInteger())
276        else if(params.inventoryTypes)
277            result.inventoryTypes = params.inventoryTypes.collect { InventoryType.get(it.toInteger()) }
278        else
279            result.inventoryTypes = InventoryType.findAllByIsActive(true, [max:254, sort:'name'])
280
281        result.inventoryGroups = []
282        if(params.inventoryGroups instanceof String)
283            result.inventoryGroups << InventoryGroup.get(params.inventoryGroups.toInteger())
284        else if(params.inventoryGroups)
285            result.inventoryGroups = params.inventoryGroups.collect { InventoryGroup.get(it.toInteger()) }
286        else
287            result.inventoryGroups = InventoryGroup.findAllByIsActive(true, [max:254, sort:'name'])
288
289        def fail = { Map m ->
290            result.error = [ code: m.code, args: m.args ]
291            result.errorMessage = g.message(result.error)
292            result.currency = null
293            //result.inventoryItemTotalValue = new BigDecimal(0)
294            return result
295        }
296
297        // Base query.
298        def q = new HqlBuilder().query {
299            select ''
300            from 'InventoryItem as inventoryItem',
301                    'left join inventoryItem.inventoryLocation as inventoryLocation',
302                    'left join inventoryLocation.inventoryStore as inventoryStore'
303            where 'inventoryItem.isActive = true'
304                namedParams.siteId = result.site.id
305                and 'inventoryStore.site.id = :siteId'
306                namedParams.groupIds = result.inventoryGroups.collect {it.id}
307                and 'inventoryItem.inventoryGroup.id in(:groupIds)'
308                namedParams.typeIds = result.inventoryTypes.collect {it.id}
309                and 'inventoryItem.inventoryType.id in(:typeIds)'
310        }
311        def baseWhereLogic = new ArrayList(q.whereClauseTerms)
312
313        // Count the inventoryItems.
314        q.select = 'count(distinct inventoryItem)'
315        result.inventoryItemCount = InventoryItem.executeQuery(q.query, q.namedParams)[0]
316
317        // Get the first currency found on this site.
318        q.paginateParams.max = 1
319        q.select = 'inventoryItem.estimatedUnitPriceCurrency'
320        result.currency = InventoryItem.executeQuery(q.query, q.namedParams, q.paginateParams)[0]
321
322        // Count the distinct currency found.
323        q.select = 'count(distinct inventoryItem.estimatedUnitPriceCurrency)'
324        def currencyCount = InventoryItem.executeQuery(q.query, q.namedParams)[0]
325
326        // Get total value.
327        q.select = 'sum (inventoryItem.estimatedUnitPriceAmount * inventoryItem.unitsInStock)'
328        result.inventoryItemTotalValue = InventoryItem.executeQuery(q.query, q.namedParams)[0]
329
330        // Get values for each group.
331        def tempGroups = []
332        result.inventoryGroups.each() { group ->
333            q.namedParams.groupIds = [group.id]
334            def groupValue = InventoryItem.executeQuery(q.query, q.namedParams)[0] ?: 0
335            tempGroups << [name: group.name, value: groupValue]
336        }
337        q.namedParams.groupIds = result.inventoryGroups.collect {it.id} // reset.
338        result.inventoryGroups = tempGroups
339
340        // Get values for each type.
341        def tempTypes = []
342        result.inventoryTypes.each() { type ->
343            q.namedParams.typeIds = [type.id]
344            def typeValue = InventoryItem.executeQuery(q.query, q.namedParams)[0] ?: 0
345            tempTypes << [name: type.name, value: typeValue]
346        }
347        result.inventoryTypes = tempTypes
348
349        /// @todo: This protects against a bug in the report layout, remove when solved.
350        if(result.inventoryTypes.size() > 4)
351            result.inventoryTypes = [[name:'More than 4', value: 0]]
352
353        if(result.inventoryGroups.size() > 4)
354            result.inventoryGroups =  [[name:'More than 4', value: 0]]
355
356        // Check all currency is the same.
357        if(currencyCount > 1)
358            fail(code:'report.error.multiple.currency.found') // No return, populate errors but continue report.
359
360        // Success.
361        return result
362
363    } // getInventoryValueOverview()
364
365} // end class
Note: See TracBrowser for help on using the repository browser.