USP_REPORT_MERCHANDISEINVENTORY

create procedure dbo.USP_REPORT_MERCHANDISEINVENTORY
(
    @DEPARTMENTID uniqueidentifier = null,
    @DEPARTMENTQUERY uniqueidentifier = null,
    @INCLUDEINACTIVE bit = 0,
    @ASOFDATE datetime = null,
    @CURRENTAPPUSERID uniqueidentifier = null
)
as
    set nocount on;

    if @ASOFDATE is null 
        set @ASOFDATE = getDate();

    set @ASOFDATE = dbo.UFN_DATE_GETLATESTTIME(@ASOFDATE);

    declare @USERGRANTEDMERCHANDISEPAGE bit = 0;

    if dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
        set @USERGRANTEDMERCHANDISEPAGE = 1;        
    else        
        select 
            @USERGRANTEDMERCHANDISEPAGE = [dbo].[UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE](@CURRENTAPPUSERID, 'b59e03d7-f2a1-47de-a1d9-adb92f56233f');                


    declare @INVENTORY table (
        MERCHANDISEPRODUCTID uniqueidentifier,
        DEPARTMENT nvarchar(100),
        CATEGORY nvarchar(100),
        MERCHANDISEPRODUCTINSTANCEID uniqueidentifier,
        BARCODE nvarchar(24),
        DESCRIPTION nvarchar(150),    
        VENDOR nvarchar(154),
        AVERAGECOST money,
        RETAILPRICE money,
        QUANTITYTYPECODE tinyint,            -- 0 - Historical, 1 - Future, 2 - DNE

        HISTORYACTIONDATE datetime,
        QUANTITY int
    );

    declare @DEPARTMENTS table (
        ID uniqueidentifier,
        NAME nvarchar(100)    
    );

    if @DEPARTMENTID is null and @DEPARTMENTQUERY is null
        insert into @DEPARTMENTS
            select
                ID,
                NAME
            from dbo.MERCHANDISEDEPARTMENT;

    if @DEPARTMENTID is not null and @DEPARTMENTQUERY is null
        insert into @DEPARTMENTS 
            select
                ID,
                NAME
            from dbo.MERCHANDISEDEPARTMENT
            where ID = @DEPARTMENTID;

    if @DEPARTMENTQUERY is not null and @DEPARTMENTID is null
        insert into @DEPARTMENTS
            select 
                QUERYRESULTS.ID,
                MERCHANDISEDEPARTMENT.NAME
            from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@DEPARTMENTQUERY) QUERYRESULTS
            inner join dbo.MERCHANDISEDEPARTMENT
                on QUERYRESULTS.ID = MERCHANDISEDEPARTMENT.ID;

    insert into @INVENTORY
        (MERCHANDISEPRODUCTID, DEPARTMENT, CATEGORY, MERCHANDISEPRODUCTINSTANCEID, BARCODE, DESCRIPTION, VENDOR, AVERAGECOST, RETAILPRICE, QUANTITY)
        select 
            MERCHANDISEPRODUCT.ID MERCHANDISEPRODUCTID,            
            DEPARTMENTS.NAME DEPARTMENT,
            isnull(MERCHANDISEDEPARTMENTCATEGORY.NAME, '<No Category>') CATEGORY,
            MERCHANDISEPRODUCTINSTANCE.ID MERCHANDISEPRODUCTINSTANCEID,
            MERCHANDISEPRODUCTINSTANCE.BARCODE,
            MERCHANDISEPRODUCTINSTANCE.ITEMDETAILS DESCRIPTION,    
            case
                when MERCHANDISEPRODUCTINSTANCE.VENDORID is not null then dbo.UFN_CONSTITUENT_BUILDNAME(MERCHANDISEPRODUCTINSTANCE.VENDORID)
                else dbo.UFN_CONSTITUENT_BUILDNAME(PRODUCTVENDOR.VENDORID)
            end VENDOR,
            MERCHANDISEPRODUCTINSTANCE.COST AVERAGECOST,
            MERCHANDISEPRODUCTINSTANCE.SALEPRICE RETAILPRICE,
            MERCHANDISEPRODUCTINSTANCE.ONHANDQUANTITY QUANTITY
        from dbo.MERCHANDISEPRODUCTINSTANCE
        inner join dbo.MERCHANDISEPRODUCT  
            on MERCHANDISEPRODUCTINSTANCE.MERCHANDISEPRODUCTID = MERCHANDISEPRODUCT.ID
        inner join dbo.PRODUCTVENDOR 
            on MERCHANDISEPRODUCTINSTANCE.MERCHANDISEPRODUCTID = PRODUCTVENDOR.PRODUCTID
        inner join @DEPARTMENTS DEPARTMENTS
            on MERCHANDISEPRODUCT.MERCHANDISEDEPARTMENTID = DEPARTMENTS.ID
        left outer join dbo.MERCHANDISEDEPARTMENTCATEGORY  
            on MERCHANDISEPRODUCT.MERCHANDISEDEPARTMENTCATEGORYID = MERCHANDISEDEPARTMENTCATEGORY.ID
        where MERCHANDISEPRODUCTINSTANCE.DATEADDED <= dbo.UFN_DATE_GETLATESTTIME(@ASOFDATE)
            and (@INCLUDEINACTIVE = 1 or (MERCHANDISEPRODUCT.ISACTIVE = 1 and MERCHANDISEPRODUCTINSTANCE.ISACTIVE = 1));

    -- get the quantity value from the most recent history item

    with HISTORY_CTE as (
        select
            MERCHANDISEPRODUCTINSTANCEHISTORY.MERCHANDISEPRODUCTINSTANCEID,
            cast(MERCHANDISEPRODUCTINSTANCEHISTORY.ACTIONDATE as datetime) ACTIONDATE,
            MERCHANDISEPRODUCTINSTANCEHISTORY.ACTIONTYPECODE,
            MERCHANDISEPRODUCTINSTANCEHISTORY.ONHANDQUANTITY,
            case MERCHANDISEPRODUCTINSTANCEHISTORY.ACTIONTYPECODE
                when 0 then MERCHANDISEPRODUCTINSTANCEHISTORY.ONHANDQUANTITY + MERCHANDISEPRODUCTINSTANCEHISTORY.QUANTITY            -- Shipment

                when 1 then MERCHANDISEPRODUCTINSTANCEHISTORY.ONHANDQUANTITY + MERCHANDISEPRODUCTINSTANCEHISTORY.QUANTITY            -- Adjustment

                when 2 then MERCHANDISEPRODUCTINSTANCEHISTORY.QUANTITY                                                                -- Physical count

            end QUANTITY,
            MERCHANDISEPRODUCTINSTANCEHISTORY.AVERAGECOST
        from dbo.MERCHANDISEPRODUCTINSTANCEHISTORY
        inner join @INVENTORY INVENTORY
            on MERCHANDISEPRODUCTINSTANCEHISTORY.MERCHANDISEPRODUCTINSTANCEID = INVENTORY.MERCHANDISEPRODUCTINSTANCEID                    
    )
    update @INVENTORY set        
        HISTORYACTIONDATE = isnull(HISTORY.ACTIONDATE, FUTURE.ACTIONDATE),
        QUANTITYTYPECODE = 
            case
                when HISTORY.ACTIONDATE is not null then 0
                when FUTURE.ACTIONDATE is not null then 1
                else 2
            end,        
        QUANTITY = coalesce(HISTORY.QUANTITY, FUTURE.ONHANDQUANTITY, INVENTORY.QUANTITY),
        AVERAGECOST = coalesce(SHIPMENTHISTORY.AVERAGECOST, SHIPMENTFUTURE.AVERAGECOST, INVENTORY.AVERAGECOST)
    from @INVENTORY INVENTORY
    outer apply (
        select top 1
            HISTORY_CTE.AVERAGECOST
        from HISTORY_CTE
        where HISTORY_CTE.MERCHANDISEPRODUCTINSTANCEID = INVENTORY.MERCHANDISEPRODUCTINSTANCEID
            and (
                HISTORY_CTE.ACTIONTYPECODE = 0                                                        -- Shipment

                or (HISTORY_CTE.ACTIONTYPECODE = 2 and HISTORY_CTE.AVERAGECOST is not null)            -- Initial physical count

            )
            and HISTORY_CTE.ACTIONDATE <= @ASOFDATE
        order by HISTORY_CTE.ACTIONDATE desc
    ) SHIPMENTHISTORY
    outer apply (
        select top 1
            HISTORY_CTE.AVERAGECOST
        from HISTORY_CTE
        where SHIPMENTHISTORY.AVERAGECOST is null
            and HISTORY_CTE.MERCHANDISEPRODUCTINSTANCEID = INVENTORY.MERCHANDISEPRODUCTINSTANCEID
            and (
                HISTORY_CTE.ACTIONTYPECODE = 0                                                        -- Shipment

                or (HISTORY_CTE.ACTIONTYPECODE = 2 and HISTORY_CTE.AVERAGECOST is not null)            -- Initial physical count

            )
            and HISTORY_CTE.ACTIONDATE > @ASOFDATE
        order by HISTORY_CTE.ACTIONDATE asc
    ) SHIPMENTFUTURE
    outer apply (
        select top 1
            HISTORY_CTE.ACTIONDATE,
            HISTORY_CTE.QUANTITY
        from HISTORY_CTE
        where HISTORY_CTE.MERCHANDISEPRODUCTINSTANCEID = INVENTORY.MERCHANDISEPRODUCTINSTANCEID
            and HISTORY_CTE.ACTIONDATE <= @ASOFDATE
        order by HISTORY_CTE.ACTIONDATE desc
    ) HISTORY
    outer apply (
        select top 1
            HISTORY_CTE.ACTIONDATE,
            HISTORY_CTE.ONHANDQUANTITY
        from HISTORY_CTE
        where HISTORY.ACTIONDATE is null
            and HISTORY_CTE.MERCHANDISEPRODUCTINSTANCEID = INVENTORY.MERCHANDISEPRODUCTINSTANCEID
            and HISTORY_CTE.ACTIONDATE > @ASOFDATE
        order by HISTORY_CTE.ACTIONDATE asc
    ) FUTURE;

    -- PERFORMANCE CONCERNS!!!

    -- The following queries cast FINANCIALTRANSACTION.DATE to a datetime, which means that the index will not be used to seek.

    -- This currently needs to be done instead of using CALCUATEDDATE because we need to account for the time of the HISTORYACTIONDATEs.

    -- We currently can compare DATETIMEOFFSET fields because the refunds are being stored without offsets.

    -- This could potentially be fixed in code and with a revision for existing refunds.


    -- Update inventory items with history info        Quantity As Of = Quantity - sum(Sold) + sum(Returned)

    update @INVENTORY set
        QUANTITY -= isnull(SALES.NUMSOLD, 0)
    from @INVENTORY INVENTORY
    outer apply (
        select
            SALESORDERITEMMERCHANDISE.MERCHANDISEPRODUCTINSTANCEID,
            SUM(SALESORDERITEM.QUANTITY) NUMSOLD
        from dbo.SALESORDERITEMMERCHANDISE            
        inner join dbo.SALESORDERITEM
            on SALESORDERITEMMERCHANDISE.ID = SALESORDERITEM.ID
        inner join dbo.SALESORDER
            on SALESORDERITEM.SALESORDERID = SALESORDER.ID
        where SALESORDERITEMMERCHANDISE.MERCHANDISEPRODUCTINSTANCEID = INVENTORY.MERCHANDISEPRODUCTINSTANCEID
            and SALESORDER.TRANSACTIONDATE between INVENTORY.HISTORYACTIONDATE and @ASOFDATE
        group by SALESORDERITEMMERCHANDISE.MERCHANDISEPRODUCTINSTANCEID
    ) SALES
    where INVENTORY.QUANTITYTYPECODE = 0;

    update @INVENTORY set
        QUANTITY += isnull(RETURNS.NUMRETURNED, 0)
    from @INVENTORY INVENTORY
    outer apply (
        select
            sum(
                case
                    when CREDITITEM_EXT.SALESORDERITEMIZEDITEMID is null then  -- Pre-SALESORDERITEMMERCHANDISEUNIT refunds

                        FINANCIALTRANSACTIONLINEITEM.QUANTITY
                    else
                        case
                            when SALESORDERITEMMERCHANDISEUNIT.ISRETURNED = 1 then
                                1
                            else
                                0
                        end
                end
            ) as NUMRETURNED
        from dbo.FINANCIALTRANSACTION
        inner join dbo.FINANCIALTRANSACTIONLINEITEM
            on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
        inner join dbo.CREDITITEM_EXT
            on FINANCIALTRANSACTIONLINEITEM.ID = CREDITITEM_EXT.ID
        inner join dbo.SALESORDERITEMMERCHANDISE
            on SALESORDERITEMMERCHANDISE.ID = CREDITITEM_EXT.SALESORDERITEMID
        left join dbo.SALESORDERITEMMERCHANDISEUNIT
            on SALESORDERITEMMERCHANDISEUNIT.ID = CREDITITEM_EXT.SALESORDERITEMIZEDITEMID
        where SALESORDERITEMMERCHANDISE.MERCHANDISEPRODUCTINSTANCEID = INVENTORY.MERCHANDISEPRODUCTINSTANCEID
            and cast(FINANCIALTRANSACTION.DATE as datetime) between INVENTORY.HISTORYACTIONDATE and @ASOFDATE
    ) RETURNS
    where INVENTORY.QUANTITYTYPECODE = 0;

    -- Update inventory items with future info        Quantity As Of = Quantity + sum(Sold) - sum(Returned)

    update @INVENTORY set
        QUANTITY += isnull(SALES.NUMSOLD, 0)
    from @INVENTORY INVENTORY
    outer apply (
        select
            SALESORDERITEMMERCHANDISE.MERCHANDISEPRODUCTINSTANCEID,
            SUM(SALESORDERITEM.QUANTITY) NUMSOLD
        from dbo.SALESORDERITEMMERCHANDISE            
        inner join dbo.SALESORDERITEM
            on SALESORDERITEMMERCHANDISE.ID = SALESORDERITEM.ID
        inner join dbo.SALESORDER
            on SALESORDERITEM.SALESORDERID = SALESORDER.ID
        where SALESORDERITEMMERCHANDISE.MERCHANDISEPRODUCTINSTANCEID = INVENTORY.MERCHANDISEPRODUCTINSTANCEID
            and SALESORDER.TRANSACTIONDATE between @ASOFDATE and INVENTORY.HISTORYACTIONDATE
        group by SALESORDERITEMMERCHANDISE.MERCHANDISEPRODUCTINSTANCEID
    ) SALES
    where INVENTORY.QUANTITYTYPECODE = 1;

    update @INVENTORY set
        QUANTITY -= isnull(RETURNS.NUMRETURNED, 0)
    from @INVENTORY INVENTORY
    outer apply (
        select
            sum(
                case
                    when CREDITITEM_EXT.SALESORDERITEMIZEDITEMID is null then  -- Pre-SALESORDERITEMMERCHANDISEUNIT refunds

                        FINANCIALTRANSACTIONLINEITEM.QUANTITY
                    else
                        case
                            when SALESORDERITEMMERCHANDISEUNIT.ISRETURNED = 1 then
                                1
                            else
                                0
                        end
                end
            ) as NUMRETURNED
        from dbo.FINANCIALTRANSACTION
        inner join dbo.FINANCIALTRANSACTIONLINEITEM
            on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
        inner join dbo.CREDITITEM_EXT
            on FINANCIALTRANSACTIONLINEITEM.ID = CREDITITEM_EXT.ID
        inner join dbo.SALESORDERITEMMERCHANDISE
            on SALESORDERITEMMERCHANDISE.ID = CREDITITEM_EXT.SALESORDERITEMID
        left join dbo.SALESORDERITEMMERCHANDISEUNIT
            on SALESORDERITEMMERCHANDISEUNIT.ID = CREDITITEM_EXT.SALESORDERITEMIZEDITEMID
        where SALESORDERITEMMERCHANDISE.MERCHANDISEPRODUCTINSTANCEID = INVENTORY.MERCHANDISEPRODUCTINSTANCEID
            and cast(FINANCIALTRANSACTION.DATE as datetime) between @ASOFDATE and INVENTORY.HISTORYACTIONDATE
    ) RETURNS
    where INVENTORY.QUANTITYTYPECODE = 1;

    -- Update inventory items with no history items        Quantity As Of = (Quantity Today) + sum(Sold) - sum(Returned)

    declare @CURRENTDATE datetime = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEFROMUTC(getutcdate());

    update @INVENTORY set
        QUANTITY += isnull(SALES.NUMSOLD, 0)
    from @INVENTORY INVENTORY
    outer apply (
        select
            SALESORDERITEMMERCHANDISE.MERCHANDISEPRODUCTINSTANCEID,
            SUM(SALESORDERITEM.QUANTITY) NUMSOLD
        from dbo.SALESORDERITEMMERCHANDISE            
        inner join dbo.SALESORDERITEM
            on SALESORDERITEMMERCHANDISE.ID = SALESORDERITEM.ID
        inner join dbo.SALESORDER
            on SALESORDERITEM.SALESORDERID = SALESORDER.ID
        where SALESORDERITEMMERCHANDISE.MERCHANDISEPRODUCTINSTANCEID = INVENTORY.MERCHANDISEPRODUCTINSTANCEID
            and SALESORDER.TRANSACTIONDATE between @ASOFDATE and @CURRENTDATE
        group by SALESORDERITEMMERCHANDISE.MERCHANDISEPRODUCTINSTANCEID
    ) SALES
    where INVENTORY.QUANTITYTYPECODE = 2;

    update @INVENTORY set
        QUANTITY -= isnull(RETURNS.NUMRETURNED, 0)
    from @INVENTORY INVENTORY
    outer apply (
        select
            sum(
                case
                    when CREDITITEM_EXT.SALESORDERITEMIZEDITEMID is null then  -- Pre-SALESORDERITEMMERCHANDISEUNIT refunds

                        FINANCIALTRANSACTIONLINEITEM.QUANTITY
                    else
                        case
                            when SALESORDERITEMMERCHANDISEUNIT.ISRETURNED = 1 then
                                1
                            else
                                0
                        end
                end
            ) as NUMRETURNED
        from dbo.FINANCIALTRANSACTION
        inner join dbo.FINANCIALTRANSACTIONLINEITEM
            on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
        inner join dbo.CREDITITEM_EXT
            on FINANCIALTRANSACTIONLINEITEM.ID = CREDITITEM_EXT.ID
        inner join dbo.SALESORDERITEMMERCHANDISE
            on SALESORDERITEMMERCHANDISE.ID = CREDITITEM_EXT.SALESORDERITEMID
        left join dbo.SALESORDERITEMMERCHANDISEUNIT
            on SALESORDERITEMMERCHANDISEUNIT.ID = CREDITITEM_EXT.SALESORDERITEMIZEDITEMID
        where SALESORDERITEMMERCHANDISE.MERCHANDISEPRODUCTINSTANCEID = INVENTORY.MERCHANDISEPRODUCTINSTANCEID
            and cast(FINANCIALTRANSACTION.DATE as datetime) between @ASOFDATE and @CURRENTDATE
    ) RETURNS
    where INVENTORY.QUANTITYTYPECODE = 2;

    select
        'http://www.blackbaud.com/MERCHANDISEPRODUCTID?MERCHANDISEPRODUCTID=' + CONVERT(nvarchar(36), MERCHANDISEPRODUCTID) as MERCHANDISELINK,
        @USERGRANTEDMERCHANDISEPAGE USERGRANTEDMERCHANDISELINK,
        BARCODE,
        DESCRIPTION,
        DEPARTMENT,
        CATEGORY,
        QUANTITY,
        VENDOR,
        AVERAGECOST,
        RETAILPRICE
    from @INVENTORY
    order by DESCRIPTION;

    return 0;