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

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.
    * @param params The request params, may contain params to specify the search.
    * @param locale The locale to use when generating result.message.
    */
    def getInventoryValue(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())
        result.inventoryTypes = params.inventoryTypes.collect { InventoryType.get(it.toInteger()) }
        result.inventoryGroups = params.inventoryGroups.collect { InventoryGroup.get(it.toInteger()) }

        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',
                    'left join fetch inventoryItem.picture as picture',
                    'left join fetch picture.images as Image'
            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
            }
            result.inventoryItemTotalValue += inventoryItem.estimatedUnitPriceAmount * inventoryItem.unitsInStock
        } // for

        // Success.
        return result

    } // getInventoryValueByGroupAndType()

} // end class