USP_DATALIST_MEMBERSHIPSALESREPORT

CREATE procedure dbo.USP_DATALIST_MEMBERSHIPSALESREPORT
(
    @MEMBERSHIPPROGRAMID uniqueidentifier = null,
    @FROM datetime = null,
    @TO datetime = null,
    @SALESMETHODID uniqueidentifier = null
)
as
    set nocount on;

    -- Validate the date range

    if (@FROM is null) or (@TO is null) or (@FROM > @TO) begin
        raiserror('Invalid date range.', 13, 1);
        return 1;
    end
    -- End date validation


    set @FROM = cast(@FROM as date);
    set @TO = dbo.UFN_DATE_GETLATESTTIME(@TO);

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

    --Note, since users want to see the report in order of Admission, Events, and Memberships... SALESORDERITEM.TYPECODEs are overridden ror sorting

    --Admission = 0, Events = 1, Membership = 2, Merchandise = 3, Donations = 3, Fees = 5, Taxes = 6


    declare @VALIDSALESORDERS table (
        ID uniqueidentifier,
        MEMBERSHIPLEVELID uniqueidentifier,
        MEMBERSHIPLEVEL nvarchar(100),
        MEMBERSHIPRANK int,
        PROMOAMOUNT money
    )

    insert into @VALIDSALESORDERS
    (
        ID,
        MEMBERSHIPLEVELID
    )
    select
        SALESORDER.ID,
        dbo.UFN_MEMBERSHIP_GETLEVELBYDATE([MEMBER].[MEMBERSHIPID], [SALESORDER].[TRANSACTIONDATE])
    from
        dbo.SALESORDER
        inner join dbo.MEMBER on SALESORDER.CONSTITUENTID = MEMBER.CONSTITUENTID
        inner join dbo.MEMBERSHIP on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
    where
        ([SALESORDER].[SALESMETHODTYPECODE] = @SALESMETHODTYPECODE or @SALESMETHODTYPECODE is null) and
        [SALESORDER].[TRANSACTIONDATE] between @FROM and @TO and
        [SALESORDER].[STATUSCODE] = 1 and
        MEMBERSHIP.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
    option (recompile);

    --Cleanup

    delete from @VALIDSALESORDERS where MEMBERSHIPLEVELID is null;

    update VSO set
        VSO.MEMBERSHIPLEVEL = MEMBERSHIPLEVEL.NAME,
        VSO.MEMBERSHIPRANK = MEMBERSHIPLEVEL.SEQUENCE
    from
        @VALIDSALESORDERS as VSO
    inner join
        dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVEL.ID = VSO.MEMBERSHIPLEVELID;

    declare @NONMEMBERRANK int;

    select @NONMEMBERRANK = max(SEQUENCE) + 1
    from dbo.MEMBERSHIPLEVEL 
    where MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID

    --Add other sales orders from time period

    --  Since refunding a membership effectively makes the sale a non-member sale,

    --  we need to add any promotions for those memberships here

    insert into @VALIDSALESORDERS
    (
        ID,
        MEMBERSHIPLEVELID,
        MEMBERSHIPLEVEL,
        MEMBERSHIPRANK,
        PROMOAMOUNT
    )
    select
        SALESORDER.ID,
        null as MEMBERSHIPLEVELID,
        'Non-members' as MEMBERSHIPLEVELNAME,
        @NONMEMBERRANK as MEMBERSHIPLEVELSEQUENCE,
        coalesce(sum([SOIMIP].[AMOUNT]),0)
    from
        dbo.SALESORDER
        left join dbo.SALESORDERITEM on SALESORDER.ID = SALESORDERITEM.SALESORDERID
        left join dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION SOIMIP on SOIMIP.SALESORDERITEMID = SALESORDERITEM.ID
    where
        ([SALESORDER].[SALESMETHODTYPECODE] = @SALESMETHODTYPECODE or @SALESMETHODTYPECODE is null) and
        [SALESORDER].[TRANSACTIONDATE] between @FROM and @TO and
        [SALESORDER].[STATUSCODE] = 1 and
        [SALESORDER].[ID] not in (select ID from @VALIDSALESORDERS)
    group by
        SALESORDER.ID
    option (recompile);

    -- Get valid refunds

    -- (Since refunds can happen days later, we need to check their dates separately)

    declare @VALIDREFUNDS table (
        ID uniqueidentifier,
        MEMBERSHIPLEVELID uniqueidentifier,
        MEMBERSHIPLEVEL nvarchar(100),
        MEMBERSHIPRANK int
    )

    insert into @VALIDREFUNDS
    (
        ID,
        MEMBERSHIPLEVELID
    )
    select distinct
        FT.ID,
        dbo.UFN_MEMBERSHIP_GETLEVELBYDATE([MEMBER].[MEMBERSHIPID], [SALESORDER].[TRANSACTIONDATE])
    from
        dbo.FINANCIALTRANSACTION as FT
    inner join
        dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID
    inner join
        dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
    left join
        dbo.[MEMBERSHIPTRANSACTION] on [MEMBERSHIPTRANSACTION].[REVENUESPLITID] = LI.SOURCELINEITEMID
    left join
        dbo.[SALESORDERITEMMEMBERSHIP] on [MEMBERSHIPTRANSACTION].[ID] = [SALESORDERITEMMEMBERSHIP].[MEMBERSHIPTRANSACTIONID]
    inner join
        dbo.[SALESORDERITEM] on [SALESORDERITEM].[ID] in ([SALESORDERITEMMEMBERSHIP].[ID], EXT.SALESORDERITEMID)
    inner join
        dbo.SALESORDER on SALESORDERITEM.SALESORDERID = SALESORDER.ID
    inner join
        dbo.MEMBER on SALESORDER.CONSTITUENTID = MEMBER.CONSTITUENTID
    inner join
        dbo.MEMBERSHIP on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
    where
        ([SALESORDER].[SALESMETHODTYPECODE] = @SALESMETHODTYPECODE or @SALESMETHODTYPECODE is null) and
        FT.CALCULATEDDATE between @FROM and @TO and
        FT.TYPECODE = 23 and  -- Refund

        [SALESORDER].[STATUSCODE] = 1 and
        MEMBERSHIP.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
    option (recompile);

    --Cleanup

    delete from @VALIDREFUNDS where MEMBERSHIPLEVELID is null;

    update VREF set
        VREF.MEMBERSHIPLEVEL = MEMBERSHIPLEVEL.NAME,
        VREF.MEMBERSHIPRANK = MEMBERSHIPLEVEL.SEQUENCE
    from
        @VALIDREFUNDS as VREF
    inner join
        dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVEL.ID = VREF.MEMBERSHIPLEVELID;

    --Add other refunds from time period

    insert into @VALIDREFUNDS
    (
        ID,
        MEMBERSHIPLEVELID,
        MEMBERSHIPLEVEL,
        MEMBERSHIPRANK
    )
    select distinct
        FT.ID,
        null as MEMBERSHIPLEVELID,
        'Non-members' as MEMBERSHIPLEVELNAME,
        @NONMEMBERRANK as MEMBERSHIPLEVELSEQUENCE
    from
        dbo.FINANCIALTRANSACTION as FT
    inner join
        dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID
    inner join
        dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
    left join
        dbo.[MEMBERSHIPTRANSACTION] on [MEMBERSHIPTRANSACTION].[REVENUESPLITID] = LI.SOURCELINEITEMID
    left join
        dbo.[SALESORDERITEMMEMBERSHIP] on [MEMBERSHIPTRANSACTION].[ID] = [SALESORDERITEMMEMBERSHIP].[MEMBERSHIPTRANSACTIONID]
    inner join
        dbo.[SALESORDERITEM] on [SALESORDERITEM].[ID] in ([SALESORDERITEMMEMBERSHIP].[ID], EXT.SALESORDERITEMID)
    inner join
        dbo.SALESORDER on SALESORDERITEM.SALESORDERID = SALESORDER.ID
    where
        ([SALESORDER].[SALESMETHODTYPECODE] = @SALESMETHODTYPECODE or @SALESMETHODTYPECODE is null) and
        FT.CALCULATEDDATE between @FROM and @TO and
        FT.TYPECODE = 23 and  -- Refund

        [SALESORDER].[STATUSCODE] = 1 and
        FT.ID not in (select ID from @VALIDREFUNDS)
    option (recompile);

    --Throw all membership levels in

    select
        -1 as [SALESORDERITEMTYPECODE],
        null as [PROGRAMNAME],
        0 as [QUANTITY],
        0 as [GROSS],
        0 as [DISCOUNT],
        0 as [REFUND],
        0 as [ISORDERDISCOUNT],
        null as CATEGORY,
        NAME as MEMBERSHIPLEVEL,
        SEQUENCE as MEMBERSHIPRANK
    from dbo.MEMBERSHIPLEVEL
    where
        MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID

    --Tickets

    union all
    select 
        0 as [SALESORDERITEMTYPECODE],
        [PROGRAM].[NAME] as [PROGRAMNAME],
        coalesce(sum([SALESORDERITEM].[QUANTITY]),0) as [QUANTITY],
        coalesce(sum([SALESORDERITEM].[TOTAL]),0) as [GROSS],
        coalesce(sum([SALESORDERITEMITEMDISCOUNT].[AMOUNT]),0) as [DISCOUNT],
        0 as [REFUND],
        0 as [ISORDERDISCOUNT],
        null as CATEGORY,
        VSO.MEMBERSHIPLEVEL,
        VSO.MEMBERSHIPRANK
    from dbo.[SALESORDERITEMTICKET]
    inner join dbo.[SALESORDERITEM]
        on [SALESORDERITEMTICKET].[ID] = [SALESORDERITEM].[ID]
    inner join @VALIDSALESORDERS VSO
        on SALESORDERITEM.SALESORDERID = VSO.ID
    left join dbo.[EVENT]
        on [SALESORDERITEMTICKET].[EVENTID] = [EVENT].[ID]
    left join dbo.[SALESORDERITEMITEMDISCOUNT]
        on [SALESORDERITEMTICKET].[ID] = [SALESORDERITEMITEMDISCOUNT].[SALESORDERITEMID]
    inner join dbo.[PROGRAM]
        on PROGRAM.ID in ([SALESORDERITEMTICKET].[PROGRAMID], [EVENT].[PROGRAMID])
    group by
        VSO.MEMBERSHIPLEVEL,
        VSO.MEMBERSHIPRANK,
        [PROGRAM].[NAME]

    --Ticket refunds/exchanges

    union all
    select 
        0 as [SALESORDERITEMTYPECODE],
        [PROGRAM].[NAME] as [PROGRAMNAME],
        -coalesce(sum(LI.QUANTITY),0) as [QUANTITY],
        0 as [GROSS],
        0 as [DISCOUNT],
        coalesce(sum((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS),0) as [REFUND],
        0 as [ISORDERDISCOUNT],
        null as CATEGORY,
        VR.MEMBERSHIPLEVEL,
        VR.MEMBERSHIPRANK
    from
        @VALIDREFUNDS VR
    inner join
        dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = VR.ID
    inner join
        dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
    inner join
        dbo.[SALESORDERITEMTICKET] on [SALESORDERITEMTICKET].[ID] = EXT.SALESORDERITEMID
    left join dbo.[EVENT]
        on [SALESORDERITEMTICKET].[EVENTID] = [EVENT].[ID]
    inner join
        dbo.[PROGRAM] on PROGRAM.ID in ([SALESORDERITEMTICKET].[PROGRAMID], [EVENT].[PROGRAMID])
    group by
        VR.MEMBERSHIPLEVEL,
        VR.MEMBERSHIPRANK,
        [PROGRAM].[NAME]

    --Order level discounts

    union all
    select
        case DISCOUNTEDITEM.TYPECODE
            when 0 then 0
            when 14 then 3
        end as [SALESORDERITEMTYPECODE],
        [SALESORDERITEM].[DESCRIPTION] as [PROGRAMNAME],
        0 as [QUANTITY],
        0 as [GROSS],
        isnull(sum(SALESORDERITEMORDERDISCOUNTDETAIL.AMOUNT), 0) as [DISCOUNT],
        0 as [REFUND],
        1 as [ISORDERDISCOUNT],
        null as CATEGORY,
        VSO.MEMBERSHIPLEVEL,
        VSO.MEMBERSHIPRANK
    from
        dbo.[SALESORDERITEM]
    inner join
        @VALIDSALESORDERS VSO on SALESORDERITEM.SALESORDERID = VSO.ID
    inner join
        dbo.SALESORDERITEMORDERDISCOUNTDETAIL on SALESORDERITEMORDERDISCOUNTDETAIL.SALESORDERITEMORDERDISCOUNTID = SALESORDERITEM.ID
    inner join
        dbo.SALESORDERITEM as DISCOUNTEDITEM on DISCOUNTEDITEM.ID = SALESORDERITEMORDERDISCOUNTDETAIL.SALESORDERITEMID
    group by
        VSO.MEMBERSHIPLEVEL,
        VSO.MEMBERSHIPRANK,
        [SALESORDERITEM].[DESCRIPTION],
        DISCOUNTEDITEM.TYPECODE

    --Order level discounts refunds/exchanges

    union all
    select
        case DISCOUNTEDITEM.TYPECODE
            when 0 then 0
            when 14 then 3
        end as [SALESORDERITEMTYPECODE],
        [SALESORDERITEM].[DESCRIPTION] as [PROGRAMNAME],
        0 as [QUANTITY],
        0 as [GROSS],
        0 as [DISCOUNT],
        -isnull(sum(SALESORDERITEMORDERDISCOUNTDETAIL.AMOUNT), 0)  as [REFUND],
        1 as [ISORDERDISCOUNT],
        null as CATEGORY,
        VR.MEMBERSHIPLEVEL,
        VR.MEMBERSHIPRANK                
    from
        @VALIDREFUNDS VR
    inner join
        dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = VR.ID
    inner join
        dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
    inner join
        dbo.[SALESORDERITEM] on [SALESORDERITEM].[ID] = EXT.SALESORDERITEMID
    inner join
        dbo.SALESORDERITEMORDERDISCOUNTDETAIL on SALESORDERITEMORDERDISCOUNTDETAIL.SALESORDERITEMORDERDISCOUNTID = SALESORDERITEM.ID
    inner join
        dbo.SALESORDERITEM as DISCOUNTEDITEM on DISCOUNTEDITEM.ID = SALESORDERITEMORDERDISCOUNTDETAIL.SALESORDERITEMID
    group by
        VR.MEMBERSHIPLEVEL,
        VR.MEMBERSHIPRANK,
        [SALESORDERITEM].[DESCRIPTION],
        DISCOUNTEDITEM.TYPECODE

    --Event Registrations

    union all
    select
        1 as [SALESORDERITEMTYPECODE],
        [PROGRAMNAME],
        sum(
            case
                when [GROSS] = 0 and [REFUND] > 0 then
                    -1
                when [GROSS] > [REFUND] or ([GROSS] = 0 and [REFUND] = 0) then
                    1
                else 
                    0
            end
        ) [QUANTITY],
        sum([GROSS]),
        0 as [DISCOUNT],
        sum([REFUND]),
        0 as [ISORDERDISCOUNT],
        null as [CATEGORY],
        [MEMBERSHIPLEVEL],
        [MEMBERSHIPRANK]
    from (  --Sum the gross and refund for each registrant so we can compare it for the quantity calculation

        select
            [PROGRAMNAME],
            sum([GROSS]) as [GROSS],
            sum([REFUND]) as [REFUND],
            [MEMBERSHIPLEVEL],
            [MEMBERSHIPRANK],
            [REGISTRANTID]
        from (  --Get all the payment and refund pieces so that we can aggregate them for each registrant

            select --Event registration payments

                [EVENT].[NAME] as [PROGRAMNAME],
                coalesce(sum([SALESORDERITEM].[TOTAL]),0) as [GROSS],
                0 as [REFUND],
                VSO.MEMBERSHIPLEVEL,
                VSO.MEMBERSHIPRANK,
                [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID] as REGISTRANTID
            from dbo.[SALESORDERITEMEVENTREGISTRATION]
            inner join dbo.[SALESORDERITEM]
                on [SALESORDERITEMEVENTREGISTRATION].[ID] = [SALESORDERITEM].[ID]
            inner join @VALIDSALESORDERS VSO
                on SALESORDERITEM.SALESORDERID = VSO.ID
            inner join dbo.[REGISTRANT]
                on [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID] = [REGISTRANT].[ID]
            inner join dbo.[EVENT]
                on [REGISTRANT].[EVENTID] = [EVENT].[ID]
            group by
                [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID],
                VSO.MEMBERSHIPLEVEL,
                VSO.MEMBERSHIPRANK,
                [EVENT].[NAME]

            --Event Registration refunds/exchanges on the order

            union all
            select 
                [EVENT].[NAME] as [PROGRAMNAME],
                0 as [GROSS],
                coalesce(sum((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS),0) as [REFUND],
                VR.MEMBERSHIPLEVEL,
                VR.MEMBERSHIPRANK,
                [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID] as REGISTRANTID
            from
                @VALIDREFUNDS VR
            inner join
                dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = VR.ID
            inner join
                dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
            inner join
                dbo.[SALESORDERITEMEVENTREGISTRATION] on [SALESORDERITEMEVENTREGISTRATION].[ID] = EXT.SALESORDERITEMID
            inner join
                dbo.[REGISTRANT] on [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID] = [REGISTRANT].[ID]
            inner join
                dbo.[EVENT] on [REGISTRANT].[EVENTID] = [EVENT].[ID]
            group by
                [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID],
                VR.MEMBERSHIPLEVEL,
                VR.MEMBERSHIPRANK,
                [EVENT].[NAME]

            --Event Registration refunds/exchanges purchased outside the context of an order

            union all
            select
                [EVENT].[NAME] as [PROGRAMNAME],
                0 as [GROSS],
                coalesce(sum(REFUNDEDFT.BASEAMOUNT),0) as [REFUND],
                coalesce([MEMBERSHIPLEVEL].[NAME], 'Non-members') as [MEMBERSHIPLEVEL],
                coalesce([MEMBERSHIPLEVEL].[SEQUENCE], 1000) as [MEMBERSHIPRANK],
                [EVENTREGISTRANTPAYMENT].[REGISTRANTID] as REGISTRANTID
            from
                dbo.FINANCIALTRANSACTION as FT
            inner join
                dbo.[CREDITPAYMENT] on [CREDITPAYMENT].[CREDITID] = FT.ID
            inner join
                dbo.FINANCIALTRANSACTIONLINEITEM as REFUNDEDLI on REFUNDEDLI.ID = CREDITPAYMENT.REVENUESPLITID
            inner join
                dbo.[EVENTREGISTRANTPAYMENT] on [EVENTREGISTRANTPAYMENT].[PAYMENTID] = CREDITPAYMENT.REVENUESPLITID
            inner join
                dbo.[REGISTRANT] on [REGISTRANT].[ID] = [EVENTREGISTRANTPAYMENT].[REGISTRANTID]
            inner join
                dbo.[EVENT] on [REGISTRANT].[EVENTID] = [EVENT].[ID]
            inner join
                dbo.FINANCIALTRANSACTION as REFUNDEDFT on REFUNDEDFT.ID = REFUNDEDLI.FINANCIALTRANSACTIONID
            left join
                dbo.[MEMBER] on [MEMBER].[CONSTITUENTID] = REFUNDEDFT.CONSTITUENTID
            outer apply (  -- The LATESTASOF inline functions will cause a MEMBERSHIPTRANSACTION scan here

                select top 1 MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID
                from dbo.MEMBERSHIPTRANSACTION
                where MEMBERSHIPTRANSACTION.MEMBERSHIPID = MEMBER.MEMBERSHIPID and cast(MEMBERSHIPTRANSACTION.TRANSACTIONDATE as date) <= FT.CALCULATEDDATE
                order by MEMBERSHIPTRANSACTION.TRANSACTIONDATE desc, MEMBERSHIPTRANSACTION.DATEADDED desc, MEMBERSHIPTRANSACTION.EXPIRATIONDATE desc
            ) as LEVELATTRANSACTIONTIME
            left join
                dbo.[MEMBERSHIPLEVEL] on [MEMBERSHIPLEVEL].[MEMBERSHIPPROGRAMID] = @MEMBERSHIPPROGRAMID and    [MEMBERSHIPLEVEL].[ID] = LEVELATTRANSACTIONTIME.MEMBERSHIPLEVELID
            where
                FT.CALCULATEDDATE between @FROM and @TO
            group by [EVENTREGISTRANTPAYMENT].[REGISTRANTID], [EVENT].[NAME], [MEMBER].[MEMBERSHIPID], FT.DATE, [MEMBERSHIPLEVEL].[NAME], [MEMBERSHIPLEVEL].[SEQUENCE]
        ) [PAYMENTPIECES]
        group by [PROGRAMNAME], [MEMBERSHIPLEVEL], [MEMBERSHIPRANK], [REGISTRANTID]
    ) [REGISTRANTS]
    group by [PROGRAMNAME], [MEMBERSHIPLEVEL], [MEMBERSHIPRANK]

    --Membership

    union all
    select
        2 as [SALESORDERITEMTYPECODE],
        [MEMBERSHIPPROGRAM].[NAME] as [PROGRAMNAME],
        coalesce(sum([SALESORDERITEM].[QUANTITY]),0) as [QUANTITY],
        coalesce(sum([SALESORDERITEM].[TOTAL]),0) as [GROSS],
        case 
            when VSO.MEMBERSHIPLEVELID is null then coalesce(sum(VSO.PROMOAMOUNT),0)
            else (select 
                      coalesce(sum([SOIMIP].[AMOUNT]),0)
                  from dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION SOIMIP
                      inner join dbo.SALESORDERITEM SOI2 on SOIMIP.SALESORDERITEMID = SOI2.ID
                      inner join dbo.SALESORDERITEMMEMBERSHIP SOIM2 on SOI2.ID = SOIM2.ID
                  where 
                      SOIM2.MEMBERSHIPLEVELID = VSO.MEMBERSHIPLEVELID and
                      SOI2.SALESORDERID in ( select ID from @VALIDSALESORDERS where MEMBERSHIPLEVELID is not null ) )
        end as [DISCOUNT],
        0 as [REFUND],
        0 as [ISORDERDISCOUNT],
        null as CATEGORY,
        VSO.MEMBERSHIPLEVEL,
        VSO.MEMBERSHIPRANK
    from dbo.[SALESORDERITEMMEMBERSHIP]
    inner join dbo.[SALESORDERITEM]
        on [SALESORDERITEMMEMBERSHIP].[ID] = [SALESORDERITEM].[ID]
    inner join @VALIDSALESORDERS VSO
        on SALESORDERITEM.SALESORDERID = VSO.ID
    inner join dbo.[MEMBERSHIPPROGRAM]
        on [SALESORDERITEMMEMBERSHIP].[MEMBERSHIPPROGRAMID] = [MEMBERSHIPPROGRAM].[ID]
    group by
        VSO.MEMBERSHIPLEVEL,
        VSO.MEMBERSHIPRANK,
        VSO.MEMBERSHIPLEVELID,
        [MEMBERSHIPPROGRAM].[NAME]

    --Resources (for the purposes of this report, combine itinerary and itinerary item resources)

    union all
    select
        8 as [SALESORDERITEMTYPECODE],
        T.NAME as [PROGRAMNAME],
        coalesce(sum([T].[QUANTITY]),0) as [QUANTITY],
        coalesce(sum([T].[TOTAL]),0) as [GROSS],
        0 as [DISCOUNT],
        0 as [REFUND],
        0 as [ISORDERDISCOUNT],
        T.CATEGORY as CATEGORY,
        T.MEMBERSHIPLEVEL as MEMBERSHIPLEVEL,
        T.MEMBERSHIPRANK as MEMBERSHIPRANK
    from ( 
        select
            RESOURCE.NAME,
            RESOURCECATEGORYCODE.DESCRIPTION as CATEGORY,
            SALESORDERITEM.QUANTITY,
            SALESORDERITEM.TOTAL,
            VSO.MEMBERSHIPLEVEL,
            VSO.MEMBERSHIPRANK
        from SALESORDERITEM
            inner join SALESORDERITEMITINERARYRESOURCE
                on SALESORDERITEM.ID = SALESORDERITEMITINERARYRESOURCE.SALESORDERITEMID
            inner join ITINERARYRESOURCE
                on SALESORDERITEMITINERARYRESOURCE.ITINERARYRESOURCEID = ITINERARYRESOURCE.ID
            inner join RESOURCE
                on ITINERARYRESOURCE.RESOURCEID = RESOURCE.ID
            inner join @VALIDSALESORDERS VSO
                on SALESORDERITEM.SALESORDERID = VSO.ID
            left join RESOURCECATEGORYCODE
                on RESOURCE.RESOURCECATEGORYCODEID = RESOURCECATEGORYCODE.ID
        where
            [SALESORDERITEM].[TYPECODE] = 8

        union all
        select
            RESOURCE.NAME,
            RESOURCECATEGORYCODE.DESCRIPTION as CATEGORY,
            SALESORDERITEM.QUANTITY,
            SALESORDERITEM.TOTAL,
            VSO.MEMBERSHIPLEVEL,
            VSO.MEMBERSHIPRANK
        from SALESORDERITEM
            inner join SALESORDERITEMITINERARYITEMRESOURCE
                on SALESORDERITEM.ID = SALESORDERITEMITINERARYITEMRESOURCE.SALESORDERITEMID
            inner join ITINERARYITEMRESOURCE
                on SALESORDERITEMITINERARYITEMRESOURCE.ITINERARYITEMRESOURCEID = ITINERARYITEMRESOURCE.ID
            inner join RESOURCE
                on ITINERARYITEMRESOURCE.RESOURCEID = RESOURCE.ID
            left join RESOURCECATEGORYCODE
                on RESOURCE.RESOURCECATEGORYCODEID = RESOURCECATEGORYCODE.ID
            inner join @VALIDSALESORDERS VSO
                on SALESORDERITEM.SALESORDERID = VSO.ID
        where
            [SALESORDERITEM].[TYPECODE] = 9
    ) T
    group by
        T.MEMBERSHIPLEVEL,
        T.MEMBERSHIPRANK,
        T.CATEGORY,
        T.NAME

    --Staffing Resources (for the purposes of this report, combine itinerary and itinerary item resources)

    union all
    select
        10 as [SALESORDERITEMTYPECODE],
        T.NAME as [PROGRAMNAME],
        coalesce(sum([T].[QUANTITY]),0) as [QUANTITY],
        coalesce(sum([T].[TOTAL]),0) as [GROSS],
        0 as [DISCOUNT],
        0 as [REFUND],
        0 as [ISORDERDISCOUNT],
        null as CATEGORY,
        T.MEMBERSHIPLEVEL as MEMBERSHIPLEVEL,
        T.MEMBERSHIPRANK as MEMBERSHIPRANK                
    from
    ( 
        select
            VOLUNTEERTYPE.NAME,
            SALESORDERITEM.QUANTITY,
            SALESORDERITEM.TOTAL,
            VSO.MEMBERSHIPLEVEL,
            VSO.MEMBERSHIPRANK
        from dbo.SALESORDERITEM
            inner join dbo.SALESORDERITEMITINERARYSTAFFRESOURCE
                on SALESORDERITEM.ID = SALESORDERITEMITINERARYSTAFFRESOURCE.SALESORDERITEMID
            inner join dbo.ITINERARYSTAFFRESOURCE
                on SALESORDERITEMITINERARYSTAFFRESOURCE.ITINERARYSTAFFRESOURCEID = ITINERARYSTAFFRESOURCE.ID
            inner join dbo.VOLUNTEERTYPE
                on ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID = VOLUNTEERTYPE.ID
            inner join @VALIDSALESORDERS VSO
                on SALESORDERITEM.SALESORDERID = VSO.ID
        where
            [SALESORDERITEM].[TYPECODE] = 10

        union all
        select
            VOLUNTEERTYPE.NAME,
            SALESORDERITEM.QUANTITY,
            SALESORDERITEM.TOTAL,
            VSO.MEMBERSHIPLEVEL,
            VSO.MEMBERSHIPRANK
        from dbo.SALESORDERITEM
            inner join dbo.SALESORDERITEMITINERARYITEMSTAFFRESOURCE
                on SALESORDERITEM.ID = SALESORDERITEMITINERARYITEMSTAFFRESOURCE.SALESORDERITEMID
            inner join dbo.ITINERARYITEMSTAFFRESOURCE
                on SALESORDERITEMITINERARYITEMSTAFFRESOURCE.ITINERARYITEMSTAFFRESOURCEID = ITINERARYITEMSTAFFRESOURCE.ID
            inner join dbo.VOLUNTEERTYPE
                on ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID = VOLUNTEERTYPE.ID
            inner join @VALIDSALESORDERS VSO
                on SALESORDERITEM.SALESORDERID = VSO.ID
        where
            [SALESORDERITEM].[TYPECODE] = 11
    ) T
    group by
        T.MEMBERSHIPLEVEL,
        T.MEMBERSHIPRANK,
        T.NAME

    --Membership refunds/exchanges

    union all
    select 
        2 as [SALESORDERITEMTYPECODE],
        case
            when [MEMBERSHIPPROGRAM].[NAME] is null then [CREDITITEMMEMBERSHIP].[MEMBERSHIPPROGRAMNAME] 
            else [MEMBERSHIPPROGRAM].[NAME]
        end as [PROGRAMNAME],
        -coalesce(sum(LI.QUANTITY),0) as [QUANTITY],
        0 as [GROSS],
        0 as [DISCOUNT],
        coalesce(sum((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS),0) as [REFUND],
        0 as [ISORDERDISCOUNT],
        null as CATEGORY,
        VR.MEMBERSHIPLEVEL,
        VR.MEMBERSHIPRANK
    from
        @VALIDREFUNDS VR
    inner join
        dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = VR.ID
    inner join
        dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
    inner join
        dbo.[CREDITITEMMEMBERSHIP] on [CREDITITEMMEMBERSHIP].[ID] = LI.ID
    left join
        dbo.[MEMBERSHIPTRANSACTION] on [MEMBERSHIPTRANSACTION].[REVENUESPLITID] = LI.SOURCELINEITEMID
    left join
        dbo.[SALESORDERITEMMEMBERSHIP] on [MEMBERSHIPTRANSACTION].[ID] = [SALESORDERITEMMEMBERSHIP].[MEMBERSHIPTRANSACTIONID]
    left join
        dbo.[MEMBERSHIPPROGRAM] on [SALESORDERITEMMEMBERSHIP].[MEMBERSHIPPROGRAMID] = [MEMBERSHIPPROGRAM].[ID]
    inner join
        dbo.[SALESORDERITEM] on [SALESORDERITEM].[ID] in ([SALESORDERITEMMEMBERSHIP].[ID], EXT.SALESORDERITEMID)
    group by
        VR.MEMBERSHIPLEVEL,
        VR.MEMBERSHIPRANK,
        [MEMBERSHIPPROGRAM].[NAME],
        [CREDITITEMMEMBERSHIP].[MEMBERSHIPPROGRAMNAME]

    --Merchandise

    union all
    select
        3 as [SALESORDERITEMTYPECODE],
        [MERCHANDISEDEPARTMENT].[NAME] as [PROGRAMNAME],
        coalesce(sum([SALESORDERITEM].[QUANTITY]),0) as [QUANTITY],
        coalesce(sum([SALESORDERITEM].[TOTAL]),0) as [GROSS],
        coalesce(sum([SALESORDERITEMITEMDISCOUNT].[AMOUNT]),0) as [DISCOUNT],
        0 as [REFUND],
        0 as [ISORDERDISCOUNT],
        null as [CATEGORY],
        VSO.MEMBERSHIPLEVEL,
        VSO.MEMBERSHIPRANK
    from dbo.[SALESORDERITEMMERCHANDISE]
    inner join dbo.MERCHANDISEDEPARTMENT
        on MERCHANDISEDEPARTMENT.ID = SALESORDERITEMMERCHANDISE.MERCHANDISEDEPARTMENTID
    inner join dbo.[SALESORDERITEM] on [SALESORDERITEMMERCHANDISE].[ID] = [SALESORDERITEM].[ID]
    inner join @VALIDSALESORDERS VSO on SALESORDERITEM.SALESORDERID = VSO.ID
    left join dbo.[SALESORDERITEMITEMDISCOUNT]
        on [SALESORDERITEMMERCHANDISE].[ID] = [SALESORDERITEMITEMDISCOUNT].[SALESORDERITEMID]
    group by
        VSO.MEMBERSHIPLEVEL,
        VSO.MEMBERSHIPRANK,
        [MERCHANDISEDEPARTMENT].[NAME]

    --Merchandise refunds/exchanges

    union all
    select
        3 as [SALESORDERITEMTYPE],
        [MERCHANDISEDEPARTMENT].[NAME] as [PROGRAMNAME],
        -coalesce(sum(LI.QUANTITY),0) as [QUANTITY],
        0 as [GROSS],
        0 as [DISCOUNT],
        coalesce(sum((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS),0) as [REFUND],
        0 as [ISORDERDISCOUNT],
        null as [CATEGORY],
        VR.MEMBERSHIPLEVEL,
        VR.MEMBERSHIPRANK
    from
        @VALIDREFUNDS VR
    inner join
        dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = VR.ID
    inner join
        dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
    inner join
        dbo.[SALESORDERITEMMERCHANDISE] on [SALESORDERITEMMERCHANDISE].[ID] = EXT.SALESORDERITEMID
    inner join
        dbo.MERCHANDISEDEPARTMENT on MERCHANDISEDEPARTMENT.ID = SALESORDERITEMMERCHANDISE.MERCHANDISEDEPARTMENTID
    group by
        VR.MEMBERSHIPLEVEL,
        VR.MEMBERSHIPRANK,
        [MERCHANDISEDEPARTMENT].[NAME]

    -- Donations, Fees, Taxes

    union all
    select
        case SALESORDERITEM.TYPECODE
            when 2 then  -- Donation

                4
            when 3 then  -- Fee

                5
            else  -- Tax

                6
        end as [SALESORDERITEMTYPECODE],
        [SALESORDERITEM].[DESCRIPTION] as [PROGRAMNAME],
        coalesce(sum([SALESORDERITEM].[QUANTITY]),0) as [QUANTITY],
        coalesce(sum([SALESORDERITEM].[TOTAL]),0) as [GROSS],
        0 as [DISCOUNT],
        0 as [REFUND],                    
        0 as [ISORDERDISCOUNT],
        null as CATEGORY,
        VSO.MEMBERSHIPLEVEL,
        VSO.MEMBERSHIPRANK
    from
        dbo.[SALESORDERITEM]
    inner join
        @VALIDSALESORDERS VSO on SALESORDERITEM.SALESORDERID = VSO.ID
    where
        [SALESORDERITEM].[TYPECODE] in (2, 3, 4)  -- Donation, Fee, Tax

    group by
        VSO.MEMBERSHIPLEVEL,
        VSO.MEMBERSHIPRANK,
        [SALESORDERITEM].[DESCRIPTION],
        [SALESORDERITEM].[TYPECODE]

    --Donations, Fees, Taxes refunds/exchanges

    union all
    select
        case SALESORDERITEM.TYPECODE
            when 2 then  -- Donation

                4
            when 3 then  -- Fee

                5
            else  -- Tax

                6
        end as [SALESORDERITEMTYPECODE],
        [SALESORDERITEM].[DESCRIPTION] as [PROGRAMNAME],
        -coalesce(sum(LI.QUANTITY),0) as [QUANTITY],
        0 as [GROSS],
        0 as [DISCOUNT],
        coalesce(sum((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS),0) as [REFUND],
        0 as [ISORDERDISCOUNT],
        null as CATEGORY,
        VR.MEMBERSHIPLEVEL,
        VR.MEMBERSHIPRANK
    from
        @VALIDREFUNDS VR
    inner join
        dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = VR.ID
    inner join
        dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
    inner join
        dbo.[SALESORDERITEM] on [SALESORDERITEM].[ID] = EXT.SALESORDERITEMID
    where
        [SALESORDERITEM].[TYPECODE] in (2, 3, 4)  -- Donation, Fee, Tax

    group by
        VR.MEMBERSHIPLEVEL,
        VR.MEMBERSHIPRANK,
        [SALESORDERITEM].[DESCRIPTION],
        [SALESORDERITEM].[TYPECODE]

    order by
        MEMBERSHIPRANK asc,
        [ISORDERDISCOUNT] asc,
        [SALESORDERITEMTYPECODE] asc,
        [CATEGORY] asc,
        [PROGRAMNAME] asc;

    return 0;