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

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