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

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

Adjust InventoryReportService getStockTakeByLocation() for MSSQL.

File size: 7.4 KB
Line 
1
2/**
3* Service class that encapsulates the business logic for Inventory Reports.
4*/
5class InventoryReportService {
6
7    boolean transactional = false
8
9//     def authService
10//     def dateUtilService
11//     def messageSource
12
13    def g = new org.codehaus.groovy.grails.plugins.web.taglib.ApplicationTagLib()
14
15    // Protect java heap memory.
16    // Most likely want to set paramsMax and inClauseMax to the same values.
17    def paramsMax = 250
18
19    // At least with Oracle and MSSQL db limits are 1000 (in list) and 2000 (nodes) respectively.
20    // But 255 has also been mentioned on the internet as a possible limit for some databases.
21    def inClauseMax = 250
22
23    /**
24    * Get the data for the inventory stock take overiew report.
25    * @param params The request params, may contain params to specify the search.
26    * @param locale The locale to use when generating result.message.
27    */
28    def getStockTakeOverview(params, locale) {
29        def result = [:]
30
31        result.summaryOfCalculationMethod = 'This report should be used in conjunction with the `Stock Take (By Location)` Report.'
32
33        def namedParams = [:]
34
35        result.query = "from InventoryLocation as inventoryLocation \
36                                        left join inventoryLocation.inventoryStore as inventoryStore \
37                                        where (inventoryLocation.isActive = true \
38                                                    ) \
39                                        order by inventoryStore.name, inventoryLocation.name"
40
41        result.query = "select new Map(inventoryLocation.name as location, inventoryStore.name as store) " + result.query
42        result.queryResult = InventoryLocation.executeQuery(result.query, namedParams)
43        result.inventoryLocationCount = result.queryResult.size()
44
45        result.inventoryLocationList = result.queryResult
46
47        // Success.
48        return result
49
50    } // getStockTakeOverview()
51
52    /**
53    * Get the data for the inventory stock take by location report.
54    * @param params The request params, may contain params to specify the search.
55    * @param locale The locale to use when generating result.message.
56    */
57    def getStockTakeByLocation(params, locale) {
58        def result = [:]
59
60        result.inventoryItemList = []
61        result.inventoryItemCount = 0
62        result.locationCount = 0
63        result.errorMessage = null
64        result.summaryOfCalculationMethod = 'This report should be used in conjunction with the `Stock Take (Overview)` Report.'
65
66        def fail = { Map m ->
67            result.error = [ code: m.code, args: m.args ]
68            result.errorMessage = g.message(result.error)
69            result.locations = ''
70            return result
71        }
72
73        def paginateParams = [:]
74        paginateParams.max = Math.min(params?.max?.toInteger() ?: paramsMax, paramsMax)
75
76        def namedParams = [:]
77        namedParams.locationList = []
78
79        // Sanitise the user supplied locations string and convert to a list.
80        result.locations = params.locationString.trim()
81        if(result.locations.startsWith('e.g:'))
82            result.locations = result.locations.split(':')[-1].trim()
83        result.locations = result.locations.split(',')
84        result.locations = result.locations.collect {it.trim()}
85
86        // Fill namedParams.locationList.
87        result.locations.each() { location ->
88            if(namedParams.locationList.size() < paramsMax) {
89                // paramsMax+1 to ensure the too many locations check bellow is triggered.
90                namedParams.locationList += InventoryLocation.findAllByNameIlike(location, [max: paramsMax+1])
91            }
92            namedParams.locationList.unique()
93        }
94
95        // Return the actual locations as a string, along with a count.
96        result.locationCount = namedParams.locationList.size()
97        if(result.locationCount > 0) {
98            result.locations = namedParams.locationList.toString()[1..-2]
99        }
100        else
101            result.locations = g.message(code: 'default.none.text')
102
103        // Exit if empty location list.
104        // Protects against HQL unexpected end of subtree exception with an empty list.
105        if(namedParams.locationList.isEmpty())
106            return fail(code:'report.error.no.locations.found')
107
108        // Exit if IN clause list too big.
109        if(namedParams.locationList.size() > inClauseMax)
110            return fail(code:'report.error.too.many.locations', args: [inClauseMax])
111
112        // Inventory List.
113        result.inventoryListQuery = "from InventoryItem as inventoryItem \
114                                                        left join inventoryItem.inventoryLocation as inventoryLocation \
115                                                        where (inventoryItem.isActive = true \
116                                                                    and  inventoryItem.inventoryLocation in (:locationList) \
117                                                                    ) "
118
119        result.inventoryCountQuery = "select count(distinct inventoryItem) " + result.inventoryListQuery
120        result.inventoryItemCount = InventoryItem.executeQuery(result.inventoryCountQuery, namedParams)[0]
121
122        // Exit if too many results.
123        if(result.inventoryItemCount > paramsMax) 
124            return fail(code:'report.error.too.many.results', args: [paramsMax])
125
126        result.inventoryListQuery = "select distinct inventoryItem " + result.inventoryListQuery
127        def inventoryList = InventoryItem.executeQuery(result.inventoryListQuery, namedParams, paginateParams)
128
129        // Reset namedParams for next query.
130        namedParams = [:]
131        namedParams.inventoryList = inventoryList
132
133        // Exit if empty inventory list.
134        // Protects against HQL unexpected end of subtree exception with an empty list.
135        if(namedParams.inventoryList.isEmpty())
136            return fail(code:'report.error.no.inventory.items.found')
137
138        // Exit if inventory list too big.
139        if(namedParams.inventoryList.size() > inClauseMax)
140            return fail(code:'report.error.too.many.inventory.items', args: [inClauseMax])
141
142        // Note: HQL docs advise 'not using fetch aliases in where clause (or any other clause)'.
143        // Access is via the parent object, however that does not work for the order by clause in this case.
144        result.query = "from InventoryItem as inventoryItem \
145                                        left join fetch inventoryItem.unitOfMeasure as unitOfMeasure \
146                                        left join fetch inventoryItem.inventoryLocation as inventoryLocation \
147                                        left join fetch inventoryLocation.inventoryStore as inventoryStore \
148                                        left join fetch inventoryItem.picture as picture \
149                                        left join fetch picture.images as Image \
150                                        where (inventoryItem in (:inventoryList) \
151                                                    ) \
152                                        order by inventoryStore.name, inventoryLocation.name"
153
154        // MSSQL will not do distinct here, for some reason it tries to compare the image data type!
155        result.query = "select inventoryItem " + result.query
156        result.inventoryItemList = InventoryItem.executeQuery(result.query, namedParams, paginateParams)
157
158        result.inventoryItemList.unique()
159
160        // Success.
161        return result
162
163    } // getStockTakeOverview()
164
165} // end class
Note: See TracBrowser for help on using the repository browser.