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

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

Work around for 'more than 4' bug in inventory value reports.
The inventoryGroup and inventoryType lists as layed out on the reports cause an infinate loop and out of memory.
File size: 15.8 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        if(params.inventoryTypes) {
187            result.inventoryTypes = params.inventoryTypes.collect { InventoryType.get(it.toInteger()) }
188        }
189        else
190            result.inventoryTypes = InventoryType.findAllByIsActive(true, [max:254, sort:'name'])
191
192        if(params.inventoryGroups) {
193            result.inventoryGroups = params.inventoryGroups.collect { InventoryGroup.get(it.toInteger()) }
194        }
195        else
196            result.inventoryGroups = InventoryGroup.findAllByIsActive(true, [max:254, sort:'name'])
197
198        def fail = { Map m ->
199            result.error = [ code: m.code, args: m.args ]
200            result.errorMessage = g.message(result.error)
201            result.currency = null
202            result.inventoryItemTotalValue = new BigDecimal(0)
203            return result
204        }
205
206        def q = new HqlBuilder().query {
207            select 'distinct inventoryItem'
208            from 'InventoryItem as inventoryItem',
209                    'left join fetch inventoryItem.inventoryLocation as inventoryLocation',
210                    'left join fetch inventoryLocation.inventoryStore as inventoryStore',
211                    'left join fetch inventoryItem.unitOfMeasure as unitOfMeasure'
212            where 'inventoryItem.isActive = true'
213                namedParams.siteId = result.site.id
214                and 'inventoryStore.site.id = :siteId'
215                if(result.inventoryTypes) {
216                    namedParams.inventoryTypeIds = result.inventoryTypes.collect {it.id}
217                    and 'inventoryItem.inventoryType.id in(:inventoryTypeIds)'
218                }
219                if(result.inventoryGroups) {
220                    namedParams.inventoryGroupIds = result.inventoryGroups.collect {it.id}
221                    and 'inventoryItem.inventoryGroup.id in(:inventoryGroupIds)'
222                }
223            order 'by inventoryItem.name asc'
224        }
225
226        result.inventoryItemList = InventoryItem.executeQuery(q.query, q.namedParams)
227        result.inventoryItemCount = result.inventoryItemList.size()
228        result.currency = result.inventoryItemList[0]?.estimatedUnitPriceCurrency
229
230        for(inventoryItem in result.inventoryItemList) {
231            // Check all currency is the same.
232            if(result.currency != inventoryItem.estimatedUnitPriceCurrency) {
233                fail(code:'report.error.multiple.currency.found') // No return, populate errors but continue report.
234                break
235            }
236            if(inventoryItem.estimatedUnitPriceAmount && inventoryItem.unitsInStock) // Some items have null estimatedUnitPriceAmount.
237                result.inventoryItemTotalValue += inventoryItem.estimatedUnitPriceAmount * inventoryItem.unitsInStock
238        } // for
239
240        /// @todo: This protects against a bug in the report layout, remove when solved.
241        if(result.inventoryTypes.size() > 4)
242            result.inventoryTypes = [[name:'More than 4']]
243
244        if(result.inventoryGroups.size() > 4)
245            result.inventoryGroups =  [[name:'More than 4']]
246
247        // Success.
248        return result
249
250    } // getInventoryValueDetailed()
251
252    /**
253    * Get the data for the inventory overiew value.
254    * @param params The request params, may contain params to specify the search.
255    * @param locale The locale to use when generating result.message.
256    */
257    def getInventoryValueOverview(params, locale) {
258        def result = [:]
259
260        result.inventoryItemCount = 0
261        result.inventoryItemTotalValue = new BigDecimal(0)
262        result.currency = null
263        result.errorMessage = null
264        result.summaryOfCalculationMethod = "This report does not convert between different currency.\n"
265        result.summaryOfCalculationMethod += "Therefore all item's are checked to ensure that currency is the same."
266
267        result.site = Site.get(params.site.id.toLong())
268
269        if(params.inventoryTypes)
270            result.inventoryTypes = params.inventoryTypes.collect { InventoryType.get(it.toInteger()) }
271        else
272            result.inventoryTypes = InventoryType.findAllByIsActive(true, [max:254, sort:'name'])
273
274        if(params.inventoryGroups)
275            result.inventoryGroups = params.inventoryGroups.collect { InventoryGroup.get(it.toInteger()) }
276        else
277            result.inventoryGroups = InventoryGroup.findAllByIsActive(true, [max:254, sort:'name'])
278
279        def fail = { Map m ->
280            result.error = [ code: m.code, args: m.args ]
281            result.errorMessage = g.message(result.error)
282            result.currency = null
283            //result.inventoryItemTotalValue = new BigDecimal(0)
284            return result
285        }
286
287        // Base query.
288        def q = new HqlBuilder().query {
289            select ''
290            from 'InventoryItem as inventoryItem',
291                    'left join inventoryItem.inventoryLocation as inventoryLocation',
292                    'left join inventoryLocation.inventoryStore as inventoryStore'
293            where 'inventoryItem.isActive = true'
294                namedParams.siteId = result.site.id
295                and 'inventoryStore.site.id = :siteId'
296                namedParams.groupIds = result.inventoryGroups.collect {it.id}
297                and 'inventoryItem.inventoryGroup.id in(:groupIds)'
298                namedParams.typeIds = result.inventoryTypes.collect {it.id}
299                and 'inventoryItem.inventoryType.id in(:typeIds)'
300        }
301        def baseWhereLogic = new ArrayList(q.whereClauseTerms)
302
303        // Count the inventoryItems.
304        q.select = 'count(distinct inventoryItem)'
305        result.inventoryItemCount = InventoryItem.executeQuery(q.query, q.namedParams)[0]
306
307        // Get the first currency found on this site.
308        q.paginateParams.max = 1
309        q.select = 'inventoryItem.estimatedUnitPriceCurrency'
310        result.currency = InventoryItem.executeQuery(q.query, q.namedParams, q.paginateParams)[0]
311
312        // Count the distinct currency found.
313        q.select = 'count(distinct inventoryItem.estimatedUnitPriceCurrency)'
314        def currencyCount = InventoryItem.executeQuery(q.query, q.namedParams)[0]
315
316        // Get total value.
317        q.select = 'sum (inventoryItem.estimatedUnitPriceAmount * inventoryItem.unitsInStock)'
318        result.inventoryItemTotalValue = InventoryItem.executeQuery(q.query, q.namedParams)[0]
319
320        // Get values for each group.
321        def tempGroups = []
322        result.inventoryGroups.each() { group ->
323            q.namedParams.groupIds = [group.id]
324            def groupValue = InventoryItem.executeQuery(q.query, q.namedParams)[0] ?: 0
325            tempGroups << [name: group.name, value: groupValue]
326        }
327        q.namedParams.groupIds = result.inventoryGroups.collect {it.id} // reset.
328        result.inventoryGroups = tempGroups
329
330        // Get values for each type.
331        def tempTypes = []
332        result.inventoryTypes.each() { type ->
333            q.namedParams.typeIds = [type.id]
334            def typeValue = InventoryItem.executeQuery(q.query, q.namedParams)[0] ?: 0
335            tempTypes << [name: type.name, value: typeValue]
336        }
337        result.inventoryTypes = tempTypes
338
339        /// @todo: This protects against a bug in the report layout, remove when solved.
340        if(result.inventoryTypes.size() > 4)
341            result.inventoryTypes = [[name:'More than 4', value: 0]]
342
343        if(result.inventoryGroups.size() > 4)
344            result.inventoryGroups =  [[name:'More than 4', value: 0]]
345
346        // Check all currency is the same.
347        if(currencyCount != 1)
348            fail(code:'report.error.multiple.currency.found') // No return, populate errors but continue report.
349
350        // Success.
351        return result
352
353    } // getInventoryValueOverview()
354
355} // end class
Note: See TracBrowser for help on using the repository browser.