import net.kromhouts.HqlBuilder /** * 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() /** * Get the data for the inventory value with detail. * @param params The request params, may contain params to specify the search. * @param locale The locale to use when generating result.message. */ def getInventoryValueDetailed(params, locale) { def result = [:] result.inventoryItemList = [] result.inventoryItemCount = 0 result.inventoryItemTotalValue = new BigDecimal(0) result.currency = null result.errorMessage = null result.summaryOfCalculationMethod = "This report does not convert between different currency.\n" result.summaryOfCalculationMethod += "Therefore all item's are checked to ensure that currency is the same." result.site = Site.get(params.site.id.toLong()) if(params.inventoryTypes) { result.inventoryTypes = params.inventoryTypes.collect { InventoryType.get(it.toInteger()) } } else result.inventoryTypes = InventoryType.findAllByIsActive(true, [max:254, sort:'name']) if(params.inventoryGroups) { result.inventoryGroups = params.inventoryGroups.collect { InventoryGroup.get(it.toInteger()) } } else result.inventoryGroups = InventoryGroup.findAllByIsActive(true, [max:254, sort:'name']) def fail = { Map m -> result.error = [ code: m.code, args: m.args ] result.errorMessage = g.message(result.error) result.currency = null result.inventoryItemTotalValue = new BigDecimal(0) return result } def q = new HqlBuilder().query { select 'distinct inventoryItem' from 'InventoryItem as inventoryItem', 'left join fetch inventoryItem.inventoryLocation as inventoryLocation', 'left join fetch inventoryLocation.inventoryStore as inventoryStore', 'left join fetch inventoryItem.unitOfMeasure as unitOfMeasure' where 'inventoryItem.isActive = true' namedParams.siteId = result.site.id and 'inventoryStore.site.id = :siteId' if(result.inventoryTypes) { namedParams.inventoryTypeIds = result.inventoryTypes.collect {it.id} and 'inventoryItem.inventoryType.id in(:inventoryTypeIds)' } if(result.inventoryGroups) { namedParams.inventoryGroupIds = result.inventoryGroups.collect {it.id} and 'inventoryItem.inventoryGroup.id in(:inventoryGroupIds)' } order 'by inventoryItem.name asc' } result.inventoryItemList = InventoryItem.executeQuery(q.query, q.namedParams) result.inventoryItemCount = result.inventoryItemList.size() result.currency = result.inventoryItemList[0]?.estimatedUnitPriceCurrency for(inventoryItem in result.inventoryItemList) { // Check all currency is the same. if(result.currency != inventoryItem.estimatedUnitPriceCurrency) { fail(code:'report.error.multiple.currency.found') // No return, populate errors but continue report. break } if(inventoryItem.estimatedUnitPriceAmount && inventoryItem.unitsInStock) // Some items have null estimatedUnitPriceAmount. result.inventoryItemTotalValue += inventoryItem.estimatedUnitPriceAmount * inventoryItem.unitsInStock } // for /// @todo: This protects against a bug in the report layout, remove when solved. if(result.inventoryTypes.size() > 4) result.inventoryTypes = [[name:'More than 4']] if(result.inventoryGroups.size() > 4) result.inventoryGroups = [[name:'More than 4']] // Success. return result } // getInventoryValueDetailed() /** * Get the data for the inventory overiew value. * @param params The request params, may contain params to specify the search. * @param locale The locale to use when generating result.message. */ def getInventoryValueOverview(params, locale) { def result = [:] result.inventoryItemCount = 0 result.inventoryItemTotalValue = new BigDecimal(0) result.currency = null result.errorMessage = null result.summaryOfCalculationMethod = "This report does not convert between different currency.\n" result.summaryOfCalculationMethod += "Therefore all item's are checked to ensure that currency is the same." result.site = Site.get(params.site.id.toLong()) if(params.inventoryTypes) result.inventoryTypes = params.inventoryTypes.collect { InventoryType.get(it.toInteger()) } else result.inventoryTypes = InventoryType.findAllByIsActive(true, [max:254, sort:'name']) if(params.inventoryGroups) result.inventoryGroups = params.inventoryGroups.collect { InventoryGroup.get(it.toInteger()) } else result.inventoryGroups = InventoryGroup.findAllByIsActive(true, [max:254, sort:'name']) def fail = { Map m -> result.error = [ code: m.code, args: m.args ] result.errorMessage = g.message(result.error) result.currency = null //result.inventoryItemTotalValue = new BigDecimal(0) return result } // Base query. def q = new HqlBuilder().query { select '' from 'InventoryItem as inventoryItem', 'left join inventoryItem.inventoryLocation as inventoryLocation', 'left join inventoryLocation.inventoryStore as inventoryStore' where 'inventoryItem.isActive = true' namedParams.siteId = result.site.id and 'inventoryStore.site.id = :siteId' namedParams.groupIds = result.inventoryGroups.collect {it.id} and 'inventoryItem.inventoryGroup.id in(:groupIds)' namedParams.typeIds = result.inventoryTypes.collect {it.id} and 'inventoryItem.inventoryType.id in(:typeIds)' } def baseWhereLogic = new ArrayList(q.whereClauseTerms) // Count the inventoryItems. q.select = 'count(distinct inventoryItem)' result.inventoryItemCount = InventoryItem.executeQuery(q.query, q.namedParams)[0] // Get the first currency found on this site. q.paginateParams.max = 1 q.select = 'inventoryItem.estimatedUnitPriceCurrency' result.currency = InventoryItem.executeQuery(q.query, q.namedParams, q.paginateParams)[0] // Count the distinct currency found. q.select = 'count(distinct inventoryItem.estimatedUnitPriceCurrency)' def currencyCount = InventoryItem.executeQuery(q.query, q.namedParams)[0] // Get total value. q.select = 'sum (inventoryItem.estimatedUnitPriceAmount * inventoryItem.unitsInStock)' result.inventoryItemTotalValue = InventoryItem.executeQuery(q.query, q.namedParams)[0] // Get values for each group. def tempGroups = [] result.inventoryGroups.each() { group -> q.namedParams.groupIds = [group.id] def groupValue = InventoryItem.executeQuery(q.query, q.namedParams)[0] ?: 0 tempGroups << [name: group.name, value: groupValue] } q.namedParams.groupIds = result.inventoryGroups.collect {it.id} // reset. result.inventoryGroups = tempGroups // Get values for each type. def tempTypes = [] result.inventoryTypes.each() { type -> q.namedParams.typeIds = [type.id] def typeValue = InventoryItem.executeQuery(q.query, q.namedParams)[0] ?: 0 tempTypes << [name: type.name, value: typeValue] } result.inventoryTypes = tempTypes /// @todo: This protects against a bug in the report layout, remove when solved. if(result.inventoryTypes.size() > 4) result.inventoryTypes = [[name:'More than 4', value: 0]] if(result.inventoryGroups.size() > 4) result.inventoryGroups = [[name:'More than 4', value: 0]] // Check all currency is the same. if(currencyCount != 1) fail(code:'report.error.multiple.currency.found') // No return, populate errors but continue report. // Success. return result } // getInventoryValueOverview() } // end class