USP_REPORT_MERCHANDISESALES

create procedure dbo.USP_REPORT_MERCHANDISESALES 
(
  @DEPARTMENTID uniqueidentifier = null,
  @DEPARTMENTQUERY uniqueidentifier = null,
  @FROMDATE datetime = null,
  @TODATE datetime = null,
  @SALESMETHODID uniqueidentifier = null,
  @VENDORID uniqueidentifier = null,
  @VENDORQUERY uniqueidentifier = null
)
as
    set nocount on;

    set @FROMDATE = convert(date, @FROMDATE);
    set @TODATE = dbo.UFN_DATE_GETLATESTTIME(@TODATE);

    declare @SALESMETHODTYPECODE tinyint = null;
    select @SALESMETHODTYPECODE = TYPECODE
    from dbo.SALESMETHOD
    where ID = @SALESMETHODID;

    declare @DEPARTMENTS table (ID uniqueidentifier);
    declare @VENDORS table (ID uniqueidentifier);

    if @DEPARTMENTQUERY is not null begin
        insert into @DEPARTMENTS
        select ID from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@DEPARTMENTQUERY);
    end

    if @VENDORQUERY is not null begin
        insert into @VENDORS
        select ID from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@VENDORQUERY);
    end

    ;with MERCHANDISE_CTE as (
        select
            MERCHANDISEPRODUCTINSTANCE.ID,
            MERCHANDISEPRODUCTINSTANCE.ITEMDETAILS,
            MERCHANDISEPRODUCTINSTANCE.BARCODE,
            MERCHANDISEPRODUCTINSTANCE.COST,
            VENDOR_NF.NAME as VENDOR,
            MERCHANDISEDEPARTMENT.NAME DEPARTMENT,
            MERCHANDISEDEPARTMENTCATEGORY.NAME CATEGORY
        from dbo.MERCHANDISEPRODUCTINSTANCE with (nolock)
        inner join dbo.MERCHANDISEPRODUCT with (nolock)
            on MERCHANDISEPRODUCTINSTANCE.MERCHANDISEPRODUCTID = MERCHANDISEPRODUCT.ID
        inner join dbo.PRODUCTVENDOR with (nolock)
            on PRODUCTVENDOR.PRODUCTID = MERCHANDISEPRODUCTINSTANCE.MERCHANDISEPRODUCTID
        inner join dbo.MERCHANDISEDEPARTMENT with (nolock)
            on MERCHANDISEPRODUCT.MERCHANDISEDEPARTMENTID = MERCHANDISEDEPARTMENT.ID
        left outer join dbo.MERCHANDISEDEPARTMENTCATEGORY with (nolock)
            on MERCHANDISEPRODUCT.MERCHANDISEDEPARTMENTCATEGORYID = MERCHANDISEDEPARTMENTCATEGORY.ID
        outer apply
            dbo.UFN_CONSTITUENT_DISPLAYNAME(PRODUCTVENDOR.VENDORID) as VENDOR_NF
        where
            (@DEPARTMENTID is null or MERCHANDISEDEPARTMENT.ID = @DEPARTMENTID)
            and (@DEPARTMENTQUERY is null or MERCHANDISEDEPARTMENT.ID in (select ID from @DEPARTMENTS))
            and (@VENDORID is null or PRODUCTVENDOR.VENDORID = @VENDORID)
            and (@VENDORQUERY is null or PRODUCTVENDOR.VENDORID in (select ID from @VENDORS))
    ),    
    SHIPMENT_CTE as
    (
        select
            MERCHANDISEPRODUCTINSTANCEID,
            ACTIONDATE,
            AVERAGECOST            
        from dbo.MERCHANDISEPRODUCTINSTANCEHISTORY with (nolock)
        inner join MERCHANDISE_CTE
            on MERCHANDISEPRODUCTINSTANCEHISTORY.MERCHANDISEPRODUCTINSTANCEID = MERCHANDISE_CTE.ID
        where
            (
                MERCHANDISEPRODUCTINSTANCEHISTORY.ACTIONTYPECODE = 0                                        -- Shipments

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

            )        
    ), 
    MERCHANDISEWITHSALESORDERS_CTE as 
    (
        select 
            isnull(sum(SALESORDERITEM.QUANTITY), 0) QUANTITY,
            isnull(sum(SALESORDERITEM.TOTAL), 0) TOTALS,
            SALESORDERITEMMERCHANDISE.MERCHANDISEPRODUCTINSTANCEID,
            MERCHANDISE_CTE.ITEMDETAILS,
            MERCHANDISE_CTE.BARCODE,
            MERCHANDISE_CTE.VENDOR,
            isnull(sum(SALESORDERITEM.QUANTITY * coalesce(SHIPMENTHISTORY.AVERAGECOST, SHIPMENTFUTURE.AVERAGECOST, MERCHANDISE_CTE.COST, 0)), 0) COST,
            MERCHANDISE_CTE.DEPARTMENT,
            MERCHANDISE_CTE.CATEGORY
        from dbo.SALESORDER with (nolock)
        inner join 
            dbo.SALESORDERITEM with (nolock) on SALESORDER.ID = SALESORDERITEM.SALESORDERID
        inner join 
            dbo.SALESORDERITEMMERCHANDISE with (nolock) on SALESORDERITEM.ID= SALESORDERITEMMERCHANDISE.ID
        inner join
            MERCHANDISE_CTE on SALESORDERITEMMERCHANDISE.MERCHANDISEPRODUCTINSTANCEID = MERCHANDISE_CTE.ID        
        outer apply (
            select top 1
                SHIPMENT_CTE.MERCHANDISEPRODUCTINSTANCEID,
                SHIPMENT_CTE.AVERAGECOST
            from SHIPMENT_CTE
            where SHIPMENT_CTE.MERCHANDISEPRODUCTINSTANCEID = MERCHANDISE_CTE.ID
                and SALESORDER.TRANSACTIONDATEWITHTIMEOFFSET >= SHIPMENT_CTE.ACTIONDATE
            order by SHIPMENT_CTE.ACTIONDATE desc
        ) SHIPMENTHISTORY
        outer apply (
            select top 1
                AVERAGECOST
            from SHIPMENT_CTE
            where SHIPMENT_CTE.MERCHANDISEPRODUCTINSTANCEID = MERCHANDISE_CTE.ID
                and SALESORDER.TRANSACTIONDATEWITHTIMEOFFSET <= SHIPMENT_CTE.ACTIONDATE
            order by SHIPMENT_CTE.ACTIONDATE asc
        ) SHIPMENTFUTURE    
        where
            (@FROMDATE is null or SALESORDER.TRANSACTIONDATE >= @FROMDATE)
            and (@TODATE is null or SALESORDER.TRANSACTIONDATE <= @TODATE)
            and SALESORDER.STATUSCODE = 1 -- Completed Orders Only   

            and (@SALESMETHODTYPECODE is null or SALESORDER.SALESMETHODTYPECODE = @SALESMETHODTYPECODE)
        group by
            SALESORDERITEMMERCHANDISE.MERCHANDISEPRODUCTINSTANCEID,
            MERCHANDISE_CTE.ITEMDETAILS,
            MERCHANDISE_CTE.DEPARTMENT,
            MERCHANDISE_CTE.CATEGORY,            
            MERCHANDISE_CTE.BARCODE,
            MERCHANDISE_CTE.VENDOR
    ), 
    DISCOUNTAMOUNTS_CTE as
    (
        select
            isnull(sum(SALESORDERITEMITEMDISCOUNT.AMOUNT), 0) + isnull(sum([SALESORDERITEMORDERDISCOUNTDETAIL].[AMOUNT]), 0) as DISCOUNTS,
            SALESORDERITEMMERCHANDISE.MERCHANDISEPRODUCTINSTANCEID MERCHANDISEPRODUCTINSTANCEID
        from 
            dbo.SALESORDER with (nolock)
        inner join 
            dbo.SALESORDERITEM with (nolock) on SALESORDERITEM.SALESORDERID = SALESORDER.ID
        inner join 
            dbo.SALESORDERITEMMERCHANDISE with (nolock) on SALESORDERITEMMERCHANDISE.ID = SALESORDERITEM.ID
          left join 
            dbo.SALESORDERITEMITEMDISCOUNT with (nolock) on SALESORDERITEM.ID = SALESORDERITEMITEMDISCOUNT.SALESORDERITEMID
        left outer join 
        (
            select 
                SALESORDERITEMID, 
                SUM(AMOUNT) as AMOUNT 
            from 
                dbo.[SALESORDERITEMORDERDISCOUNTDETAIL] with (nolock)
            group by  
                [SALESORDERITEMORDERDISCOUNTDETAIL].[SALESORDERITEMID] 
        ) [SALESORDERITEMORDERDISCOUNTDETAIL] on [SALESORDERITEMORDERDISCOUNTDETAIL].[SALESORDERITEMID] = [SALESORDERITEM].[ID]
        where 
            (@FROMDATE is null or SALESORDER.TRANSACTIONDATE >= @FROMDATE)
            and (@TODATE is null or SALESORDER.TRANSACTIONDATE <= @TODATE)
            and SALESORDER.STATUSCODE = 1 -- Completed Orders Only          

            and (@SALESMETHODTYPECODE is null or SALESORDER.SALESMETHODTYPECODE = @SALESMETHODTYPECODE)
        group by 
            MERCHANDISEPRODUCTINSTANCEID           
    ),
    REPORTPARAMETERS_CTE as 
    (
        -- merchandise sold

        select 
            MERCHANDISEWITHSALESORDERS_CTE.MERCHANDISEPRODUCTINSTANCEID as ID,
            MERCHANDISEWITHSALESORDERS_CTE.ITEMDETAILS DESCRIPTION,
            MERCHANDISEWITHSALESORDERS_CTE.DEPARTMENT, 
            isnull(MERCHANDISEWITHSALESORDERS_CTE.CATEGORY, '<No Category>') as CATEGORY,
            MERCHANDISEWITHSALESORDERS_CTE.TOTALS as GROSS,
            DISCOUNTAMOUNTS_CTE.DISCOUNTS,
            0 as REFUNDS,
            0 as FEES,
            MERCHANDISEWITHSALESORDERS_CTE.QUANTITY as QUANTITY,
            0 as NUMREFUNDED,
            MERCHANDISEWITHSALESORDERS_CTE.BARCODE,
            MERCHANDISEWITHSALESORDERS_CTE.VENDOR,
            MERCHANDISEWITHSALESORDERS_CTE.COST as COST
        from
            MERCHANDISEWITHSALESORDERS_CTE
        --there can be more than one discount applied to a merchandise item, so

        left outer join
            DISCOUNTAMOUNTS_CTE on DISCOUNTAMOUNTS_CTE.MERCHANDISEPRODUCTINSTANCEID = MERCHANDISEWITHSALESORDERS_CTE.MERCHANDISEPRODUCTINSTANCEID
        group by 
            MERCHANDISEWITHSALESORDERS_CTE.ITEMDETAILS,
            MERCHANDISEWITHSALESORDERS_CTE.MERCHANDISEPRODUCTINSTANCEID,
            MERCHANDISEWITHSALESORDERS_CTE.DEPARTMENT, 
            MERCHANDISEWITHSALESORDERS_CTE.CATEGORY,
            MERCHANDISEWITHSALESORDERS_CTE.TOTALS,
            DISCOUNTAMOUNTS_CTE.DISCOUNTS,
            MERCHANDISEWITHSALESORDERS_CTE.QUANTITY,
            MERCHANDISEWITHSALESORDERS_CTE.BARCODE,
            MERCHANDISEWITHSALESORDERS_CTE.VENDOR,
            MERCHANDISEWITHSALESORDERS_CTE.COST

        union all

            -- merchandise refunded

        select 
            MERCHANDISE_CTE.ID,
            MERCHANDISE_CTE.ITEMDETAILS DESCRIPTION,
            MERCHANDISE_CTE.DEPARTMENT, 
            isnull(MERCHANDISE_CTE.CATEGORY, '<No Category>') CATEGORY,
            0 as GROSS,
            0 as DISCOUNTS,
            isnull(sum(CREDITITEM.TOTAL + CREDITITEM.FEES), 0) as REFUNDS,
            0 as FEES,
            0 as QUANTITY,
            isnull(sum(CREDITITEM.REFUNDED), 0) as NUMREFUNDED,
            MERCHANDISE_CTE.BARCODE,
            MERCHANDISE_CTE.VENDOR,
            -isnull(sum(CREDITITEM.REFUNDED * coalesce(SHIPMENTHISTORY.AVERAGECOST, SHIPMENTFUTURE.AVERAGECOST, MERCHANDISE_CTE.COST, 0)), 0) COST
        from
            dbo.SALESORDERITEMMERCHANDISE with (nolock)
        inner join 
            dbo.SALESORDERITEM with (nolock) on SALESORDERITEM.ID = SALESORDERITEMMERCHANDISE.ID
        inner join 
            dbo.SALESORDER with (nolock) on SALESORDER.ID = SALESORDERITEM.SALESORDERID
        inner join 
            MERCHANDISE_CTE on SALESORDERITEMMERCHANDISE.MERCHANDISEPRODUCTINSTANCEID = MERCHANDISE_CTE.ID    
        inner join
        (
            select 
                EXT.SALESORDERITEMID,
                isnull(sum((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS), 0) - isnull(ORDERDISCOUNTDETAIL.AMOUNT, 0) as TOTAL, 
                sum(EXT.FEES) FEES,
                sum(LI.QUANTITY) REFUNDED
            from 
                dbo.FINANCIALTRANSACTIONLINEITEM as LI with (nolock)
            inner join
                dbo.CREDITITEM_EXT as EXT with (nolock) on EXT.ID = LI.ID
            inner join
                dbo.FINANCIALTRANSACTION as FT with (nolock) on FT.ID = LI.FINANCIALTRANSACTIONID
            outer apply (
                select sum(SALESORDERITEMORDERDISCOUNTDETAIL.AMOUNT) as AMOUNT
                from dbo.SALESORDERITEMORDERDISCOUNTDETAIL with (nolock)
                where SALESORDERITEMORDERDISCOUNTDETAIL.SALESORDERITEMID = EXT.SALESORDERITEMID
            ) as ORDERDISCOUNTDETAIL
            where
                (@FROMDATE is null or FT.CALCULATEDDATE >= @FROMDATE)
                and (@TODATE is null or FT.CALCULATEDDATE <= @TODATE)
            group by 
                EXT.SALESORDERITEMID, 
                ORDERDISCOUNTDETAIL.AMOUNT
        ) CREDITITEM on    SALESORDERITEM.ID = CREDITITEM.SALESORDERITEMID
        outer apply (
            select top 1
                SHIPMENT_CTE.MERCHANDISEPRODUCTINSTANCEID,
                SHIPMENT_CTE.AVERAGECOST
            from SHIPMENT_CTE
            where SHIPMENT_CTE.MERCHANDISEPRODUCTINSTANCEID = MERCHANDISE_CTE.ID
                and SALESORDER.TRANSACTIONDATEWITHTIMEOFFSET >= SHIPMENT_CTE.ACTIONDATE
            order by SHIPMENT_CTE.ACTIONDATE desc
        ) SHIPMENTHISTORY
        outer apply (
            select top 1
                AVERAGECOST
            from SHIPMENT_CTE
            where SHIPMENT_CTE.MERCHANDISEPRODUCTINSTANCEID = MERCHANDISE_CTE.ID
                and SALESORDER.TRANSACTIONDATEWITHTIMEOFFSET <= SHIPMENT_CTE.ACTIONDATE
            order by SHIPMENT_CTE.ACTIONDATE asc
        ) SHIPMENTFUTURE            
        where (@SALESMETHODTYPECODE is null or SALESORDER.SALESMETHODTYPECODE = @SALESMETHODTYPECODE) 
            and SALESORDER.STATUSCODE = 1
        group by 
            MERCHANDISE_CTE.ID,
            MERCHANDISE_CTE.ITEMDETAILS, 
            MERCHANDISE_CTE.DEPARTMENT, 
            MERCHANDISE_CTE.CATEGORY,            
            MERCHANDISE_CTE.BARCODE,
            MERCHANDISE_CTE.VENDOR
    )
    select 
        DESCRIPTION,
        DEPARTMENT,
        CATEGORY,
        SUM(QUANTITY) - SUM(NUMREFUNDED) as QUANTITY,
        SUM(GROSS) + SUM(FEES) as GROSS,
        SUM(DISCOUNTS) as DISCOUNTS,
        SUM(REFUNDS) as REFUNDS,
        SUM(GROSS) - SUM(DISCOUNTS) - SUM(REFUNDS) as NET,
        BARCODE,
        VENDOR,
        SUM(COST) as COST
    from 
        REPORTPARAMETERS_CTE
    group by 
        REPORTPARAMETERS_CTE.ID,
        REPORTPARAMETERS_CTE.DESCRIPTION,
        REPORTPARAMETERS_CTE.DEPARTMENT,
        REPORTPARAMETERS_CTE.CATEGORY,
        REPORTPARAMETERS_CTE.BARCODE,
        REPORTPARAMETERS_CTE.VENDOR
    order by
        DEPARTMENT, DESCRIPTION
    option (recompile);

    return 0;