Changeset 550 for trunk/grails-app/services/InventoryReportService.groovy

Timestamp:

May 28, 2010, 1:29:19 AM (14 years ago)

Author:

gav

Message:

Improvements to stockTakeByLocation report, includes heap memory protection improvements.
Tested against MySQL with 767 InventoryLocations, 3770 InventoryItems and 270MiB of images in database.

File:

• trunk/grails-app/services/InventoryReportService.groovy (modified) (6 diffs)

trunk/grails-app/services/InventoryReportService.groovy

@@ -13 +13 @@
     def g = new org.codehaus.groovy.grails.plugins.web.taglib.ApplicationTagLib()
 
+    // Protect java heap memory.
+    // Most likely want to set paramsMax and inClauseMax to the same values.
     def paramsMax = 250
+
+    // At least with Oracle and MSSQL db limits are 1000 (in list) and 2000 (nodes) respectively.
+    // But 255 has also been mentioned on the internet as a possible limit for some databases.
+    def inClauseMax = 250
 
     /**
@@ -52 +58 @@
         def result = [:]
 
+        result.inventoryItemList = []
+        result.inventoryItemCount = 0
+        result.locationCount = 0
+        result.errorMessage = null
         result.summaryOfCalculationMethod = 'This report should be used in conjunction with the `Stock Take (Overview)` Report.'
 
-        // Sanitise the locations string and convert to a list.
+        def fail = { Map m ->
+            result.error = [ code: m.code, args: m.args ]
+            result.errorMessage = g.message(result.error)
+            result.locations = ''
+            return result
+        }
+
+        def paginateParams = [:]
+        paginateParams.max = Math.min(params?.max?.toInteger() ?: paramsMax, paramsMax)
+
+        def namedParams = [:]
+        namedParams.locationList = []
+
+        // Sanitise the user supplied locations string and convert to a list.
         result.locations = params.locationString.trim()
         if(result.locations.startsWith('e.g:'))
@@ -61 +84 @@
         result.locations = result.locations.collect {it.trim()}
 
-        def paginateParams = [:]
-        paginateParams.max = Math.min(params?.max?.toInteger() ?: paramsMax, paramsMax)
-
-        def namedParams = [:]
-        namedParams.locationList = [null] // null protects against HQL unexpected end of subtree exception with an empty list.
-
-        // Fill namedParams.locationList
-        result.locations.each() {
-            InventoryLocation.findAllByNameIlike(it).each() {
-                namedParams.locationList << it
+        // Fill namedParams.locationList.
+        result.locations.each() { location ->
+            if(namedParams.locationList.size() < paramsMax) {
+                // paramsMax+1 to ensure the too many locations check bellow is triggered.
+                namedParams.locationList += InventoryLocation.findAllByNameIlike(location, [max: paramsMax+1])
             }
+            namedParams.locationList.unique()
         }
 
-        // Return the actual locations as a string.
-        if(namedParams.locationList.size() > 1)
-            result.locations = namedParams.locationList[1..-1].toString()[1..-2]
+        // Return the actual locations as a string, along with a count.
+        result.locationCount = namedParams.locationList.size()
+        if(result.locationCount > 0) {
+            result.locations = namedParams.locationList.toString()[1..-2]
+        }
         else
             result.locations = g.message(code: 'default.none.text')
+
+        // Exit if empty location list.
+        // Protects against HQL unexpected end of subtree exception with an empty list.
+        if(namedParams.locationList.isEmpty())
+            return fail(code:'report.error.no.locations.found')
+
+        // Exit if IN clause list too big.
+        if(namedParams.locationList.size() > inClauseMax)
+            return fail(code:'report.error.too.many.locations', args: [inClauseMax])
 
         // Inventory List.
@@ -91 +121 @@
 
         // Exit if too many results.
-        result.countWarning = null
-        if(result.inventoryItemCount > paramsMax) {
-            result.countWarning = g.message(code: 'report.too.many.results.warning',
-                                                                    args: [paramsMax],
-                                                                    default: "Warning over ${paramsMax} results, please run report again!")
-            result.inventoryItemList = []
-            return result
-        }
+        if(result.inventoryItemCount > paramsMax) 
+            return fail(code:'report.error.too.many.results', args: [paramsMax])
 
         result.inventoryListQuery = "select distinct inventoryItem " + result.inventoryListQuery
@@ -107 +131 @@
         namedParams.inventoryList = inventoryList
 
-        // Note: HQL docs advise not using fetch aliases in where clause (or any other clause).
+        // Exit if empty inventory list.
+        // Protects against HQL unexpected end of subtree exception with an empty list.
+        if(namedParams.inventoryList.isEmpty())
+            return fail(code:'report.error.no.inventory.items.found')
+
+        // Exit if inventory list too big.
+        if(namedParams.inventoryList.size() > inClauseMax)
+            return fail(code:'report.error.too.many.inventory.items', args: [inClauseMax])
+
+        // Note: HQL docs advise 'not using fetch aliases in where clause (or any other clause)'.
         // Access is via the parent object, however that does not work for the order by clause in this case.
         result.query = "from InventoryItem as inventoryItem \
@@ -119 +152 @@
                                         order by inventoryStore.name, inventoryLocation.name"
 
-        result.query = "select inventoryItem " + result.query
+        result.query = "select  distinct inventoryItem " + result.query
         result.inventoryItemList = InventoryItem.executeQuery(result.query, namedParams, paginateParams)