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

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

New report: Inventory Value Overview.
File size: 15.2 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        // Success.
241        return result
242
243    } // getInventoryValueDetailed()
244
245    /**
246    * Get the data for the inventory overiew value.
247    * @param params The request params, may contain params to specify the search.
248    * @param locale The locale to use when generating result.message.
249    */
250    def getInventoryValueOverview(params, locale) {
251        def result = [:]
252
253        result.inventoryItemCount = 0
254        result.inventoryItemTotalValue = new BigDecimal(0)
255        result.currency = null
256        result.errorMessage = null
257        result.summaryOfCalculationMethod = "This report does not convert between different currency.\n"
258        result.summaryOfCalculationMethod += "Therefore all item's are checked to ensure that currency is the same."
259
260        result.site = Site.get(params.site.id.toLong())
261
262        if(params.inventoryTypes)
263            result.inventoryTypes = params.inventoryTypes.collect { InventoryType.get(it.toInteger()) }
264        else
265            result.inventoryTypes = InventoryType.findAllByIsActive(true, [max:254, sort:'name'])
266
267        if(params.inventoryGroups)
268            result.inventoryGroups = params.inventoryGroups.collect { InventoryGroup.get(it.toInteger()) }
269        else
270            result.inventoryGroups = InventoryGroup.findAllByIsActive(true, [max:254, sort:'name'])
271
272        def fail = { Map m ->
273            result.error = [ code: m.code, args: m.args ]
274            result.errorMessage = g.message(result.error)
275            result.currency = null
276            //result.inventoryItemTotalValue = new BigDecimal(0)
277            return result
278        }
279
280        // Base query.
281        def q = new HqlBuilder().query {
282            select ''
283            from 'InventoryItem as inventoryItem',
284                    'left join inventoryItem.inventoryLocation as inventoryLocation',
285                    'left join inventoryLocation.inventoryStore as inventoryStore'
286            where 'inventoryItem.isActive = true'
287                namedParams.siteId = result.site.id
288                and 'inventoryStore.site.id = :siteId'
289        }
290        def baseWhereLogic = new ArrayList(q.whereClauseTerms)
291
292        // Count the inventoryItems.
293        q.select = 'count(distinct inventoryItem)'
294        result.inventoryItemCount = InventoryItem.executeQuery(q.query, q.namedParams, q.paginateParams)[0]
295
296        // Get the first currency found on this site.
297        q.paginateParams.max = 1
298        q.select = 'inventoryItem.estimatedUnitPriceCurrency'
299        result.currency = InventoryItem.executeQuery(q.query, q.namedParams, q.paginateParams)[0]
300
301        // Count the distinct currency found.
302        q.select = 'count(distinct inventoryItem.estimatedUnitPriceCurrency)'
303        def currencyCount = InventoryItem.executeQuery(q.query, q.namedParams)[0]
304
305        // Get total value.
306        q.select = 'sum (inventoryItem.estimatedUnitPriceAmount * inventoryItem.unitsInStock)'
307        result.inventoryItemTotalValue = InventoryItem.executeQuery(q.query, q.namedParams)[0]
308
309        // Get values for each group.
310        q.and 'inventoryItem.inventoryGroup.id = :groupId'
311        def tempGroups = []
312        result.inventoryGroups.each() { group ->
313            q.namedParams.groupId = group.id
314            def groupValue = InventoryItem.executeQuery(q.query, q.namedParams)[0] ?: 0
315            tempGroups << [name: group.name, value: groupValue]
316        }
317
318        // Cleanup and reset query.
319        q.namedParams.remove('groupId')
320        q.whereClauseTerms = baseWhereLogic
321        result.inventoryGroups = tempGroups
322
323        // Get values for each type.
324        q.and 'inventoryItem.inventoryType.id = :typeId'
325        def tempTypes = []
326        result.inventoryTypes.each() { type ->
327            q.namedParams.typeId = type.id
328            def typeValue = InventoryItem.executeQuery(q.query, q.namedParams)[0] ?: 0
329            tempTypes << [name: type.name, value: typeValue]
330        }
331
332        // Cleanup and reset query.
333        q.namedParams.remove('typeId')
334        q.whereClauseTerms = baseWhereLogic
335        result.inventoryTypes = tempTypes
336
337        if(currencyCount != 1)
338            fail(code:'report.error.multiple.currency.found') // No return, populate errors but continue report.
339
340        // Success.
341        return result
342
343    } // getInventoryValueOverview()
344
345} // end class
Note: See TracBrowser for help on using the repository browser.