/** * Service class that encapsulates the business logic for Inventory Reports. */ class InventoryReportService { boolean transactional = false // def authService // def dateUtilService // def messageSource 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 /** * Get the data for the inventory stock take overiew report. * @param params The request params, may contain params to specify the search. * @param locale The locale to use when generating result.message. */ def getStockTakeOverview(params, locale) { def result = [:] result.summaryOfCalculationMethod = 'This report should be used in conjunction with the `Stock Take (By Location)` Report.' def namedParams = [:] result.query = "from InventoryLocation as inventoryLocation \ left join inventoryLocation.inventoryStore as inventoryStore \ where (inventoryLocation.isActive = true \ ) \ order by inventoryStore.name, inventoryLocation.name" result.query = "select new Map(inventoryLocation.name as location, inventoryStore.name as store) " + result.query result.queryResult = InventoryLocation.executeQuery(result.query, namedParams) result.inventoryLocationCount = result.queryResult.size() result.inventoryLocationList = result.queryResult // Success. return result } // getStockTakeOverview() /** * Get the data for the inventory stock take by location report. * @param params The request params, may contain params to specify the search. * @param locale The locale to use when generating result.message. */ def getStockTakeByLocation(params, locale) { 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.' 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:')) result.locations = result.locations.split(':')[-1].trim() result.locations = result.locations.split(',') result.locations = result.locations.collect {it.trim()} // 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, along with a count. result.locationCount = namedParams.locationList.size() if(result.locationCount > 0) { namedParams.locationList.sort { p1, p2 -> p1.name.compareToIgnoreCase(p2.name) } 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. result.inventoryListQuery = "from InventoryItem as inventoryItem \ left join inventoryItem.inventoryLocation as inventoryLocation \ where (inventoryItem.isActive = true \ and inventoryItem.inventoryLocation in (:locationList) \ ) " result.inventoryCountQuery = "select count(distinct inventoryItem) " + result.inventoryListQuery result.inventoryItemCount = InventoryItem.executeQuery(result.inventoryCountQuery, namedParams)[0] // Exit if too many results. if(result.inventoryItemCount > paramsMax) return fail(code:'report.error.too.many.results', args: [paramsMax]) result.inventoryListQuery = "select distinct inventoryItem " + result.inventoryListQuery def inventoryList = InventoryItem.executeQuery(result.inventoryListQuery, namedParams, paginateParams) // Reset namedParams for next query. namedParams = [:] namedParams.inventoryList = inventoryList // 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 \ left join fetch inventoryItem.unitOfMeasure as unitOfMeasure \ left join fetch inventoryItem.inventoryLocation as inventoryLocation \ left join fetch inventoryLocation.inventoryStore as inventoryStore \ left join fetch inventoryItem.picture as picture \ left join fetch picture.images as Image \ where (inventoryItem in (:inventoryList) \ ) \ order by inventoryStore.name, inventoryLocation.name" // MSSQL will not do distinct here, for some reason it tries to compare the image data type! result.query = "select inventoryItem " + result.query result.inventoryItemList = InventoryItem.executeQuery(result.query, namedParams, paginateParams) result.inventoryItemList.unique() // Success. return result } // getStockTakeOverview() } // end class