USP_REPORT_MEMBERSHIPSALES

create procedure dbo.USP_REPORT_MEMBERSHIPSALES
(
    @FROMDATE datetime = null, 
    @TODATE datetime = null,
    @MEMBERSHIPPROGRAMID uniqueidentifier = null,
    @SALESMETHODID uniqueidentifier = null
)
as
    set nocount on;

    -- Validate the date range


    if (@FROMDATE is null) or (@TODATE is null) or (@FROMDATE > @TODATE) begin
        raiserror('Invalid date range.', 13, 1);
        return 1;
    end

    -- End date validation


    set @FROMDATE = cast(@FROMDATE as date);
    set @TODATE = dbo.UFN_DATE_GETLATESTTIME(@TODATE);

    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 for 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.UFN_SALESORDER_BETWEENDATES(@FROMDATE, @TODATE) as FILTEREDSALESORDERS
    inner join
        dbo.SALESORDER on SALESORDER.ID = FILTEREDSALESORDERS.ID
    inner join
        dbo.MEMBER on MEMBER.CONSTITUENTID = SALESORDER.CONSTITUENTID
    inner join
        dbo.MEMBERSHIP on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
    where
        SALESORDER.STATUSCODE = 1  -- Complete

        and (@SALESMETHODTYPECODE is null or SALESORDER.SALESMETHODTYPECODE = @SALESMETHODTYPECODE)
        and MEMBERSHIP.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
        and MEMBER.ISDROPPED = 0
    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,
        'Non-members',
        @NONMEMBERRANK,
        isnull(sum(SALESORDERITEMMEMBERSHIPITEMPROMOTION.AMOUNT), 0)
    from
        dbo.UFN_SALESORDER_BETWEENDATES(@FROMDATE, @TODATE) as FILTEREDSALESORDERS
    inner join
        dbo.SALESORDER on SALESORDER.ID = FILTEREDSALESORDERS.ID
    left outer join
        dbo.SALESORDERITEM on SALESORDERITEM.SALESORDERID = SALESORDER.ID
    left outer join
        dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION on SALESORDERITEMMEMBERSHIPITEMPROMOTION.SALESORDERITEMID = SALESORDERITEM.ID
    where
        SALESORDER.STATUSCODE = 1  -- Complete

        and (@SALESMETHODTYPECODE is null or SALESORDER.SALESMETHODTYPECODE = @SALESMETHODTYPECODE)
        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 on CREDITITEM_EXT.ID = LI.ID
    left 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
    inner join
        dbo.SALESORDERITEM on SALESORDERITEM.ID in (SALESORDERITEMMEMBERSHIP.ID, CREDITITEM_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
        FT.TYPECODE = 23  -- Refund

        and (SALESORDER.SALESMETHODTYPECODE = @SALESMETHODTYPECODE or @SALESMETHODTYPECODE is null)
        and FT.CALCULATEDDATE between @FROMDATE and @TODATE
        and SALESORDER.STATUSCODE = 1
        and MEMBERSHIP.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
        and MEMBER.ISDROPPED = 0
    option (recompile);

    insert into @VALIDREFUNDS
    (
        ID,
        MEMBERSHIPLEVELID
    )
    select distinct
        FT.ID,
        dbo.UFN_MEMBERSHIP_GETLEVELBYDATE(MEMBER.MEMBERSHIPID, cast(FT.DATE as datetime))
    from
        dbo.FINANCIALTRANSACTION as FT
    inner join
        dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID
    inner join
        dbo.CREDIT_EXT as EXT on EXT.ID = FT.ID
    left outer join
        dbo.SALESORDER on SALESORDER.REVENUEID = FT.PARENTID
    inner join
        dbo.CREDITITEMMEMBERSHIP on CREDITITEMMEMBERSHIP.ID = LI.ID
    inner join
        dbo.MEMBER on MEMBER.CONSTITUENTID = FT.CONSTITUENTID
    inner join
        dbo.MEMBERSHIP on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
    where
        FT.CALCULATEDDATE between @FROMDATE and @TODATE
        and SALESORDER.ID is null
        and EXT.SALESORDERID is null
        and MEMBERSHIP.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
        and MEMBER.ISDROPPED = 0

    -- Event registration refunds outside a sales order

    insert into @VALIDREFUNDS
    (
        ID,
        MEMBERSHIPLEVELID
    )
    select distinct
        FT.ID,
        dbo.UFN_MEMBERSHIP_GETLEVELBYDATE(MEMBER.MEMBERSHIPID, cast(FT.DATE as datetime))
    from
        dbo.FINANCIALTRANSACTION as FT
    inner join
        dbo.CREDIT_EXT as EXT on EXT.ID = FT.ID
    left outer join
        dbo.SALESORDER on SALESORDER.REVENUEID = FT.PARENTID
    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
    inner join
        dbo.MEMBER on REFUNDEDFT.CONSTITUENTID = MEMBER.CONSTITUENTID
    inner join
        dbo.MEMBERSHIP on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
    where
        FT.CALCULATEDDATE between @FROMDATE and @TODATE
        and SALESORDER.ID is null
        and EXT.SALESORDERID is null
        and MEMBERSHIP.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
        and MEMBER.ISDROPPED = 0

    --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.CREDITITEMMEMBERSHIP on CREDITITEMMEMBERSHIP.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
        FT.TYPECODE = 23  -- Refund

        and (SALESORDER.SALESMETHODTYPECODE = @SALESMETHODTYPECODE or @SALESMETHODTYPECODE is null)
        and FT.CALCULATEDDATE between @FROMDATE and @TODATE
        and SALESORDER.STATUSCODE = 1
        and FT.ID not in (select ID from @VALIDREFUNDS)
    option (recompile);

    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.CREDIT_EXT as EXT on EXT.ID = FT.ID
    inner join
        dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID
    inner join
        dbo.CREDITITEMMEMBERSHIP on CREDITITEMMEMBERSHIP.ID = LI.ID
    left outer join
        dbo.SALESORDER on SALESORDER.REVENUEID = FT.PARENTID
    where
        FT.CALCULATEDDATE between @FROMDATE and @TODATE
        and SALESORDER.ID is null
        and EXT.SALESORDERID is null
        and FT.ID not in (select ID from @VALIDREFUNDS)

    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.CREDIT_EXT as EXT on EXT.ID = FT.ID
    inner join
        dbo.CREDITPAYMENT on CREDITPAYMENT.CREDITID = FT.ID
    inner join
        dbo.EVENTREGISTRANTPAYMENT on EVENTREGISTRANTPAYMENT.PAYMENTID = CREDITPAYMENT.REVENUESPLITID
    left outer join
        dbo.SALESORDER on SALESORDER.REVENUEID = FT.PARENTID
    where
        FT.CALCULATEDDATE between @FROMDATE and @TODATE
        and SALESORDER.ID is null
        and EXT.SALESORDERID is null
        and FT.ID not in (select ID from @VALIDREFUNDS)

    --Throw all membership levels in

    select
        -1 as SALESORDERITEMTYPE,
        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 SALESORDERITEMTYPE,
        PROGRAM.NAME as PROGRAMNAME,
        coalesce(sum(SALESORDERITEM.QUANTITY),0) as QUANTITY,
        coalesce(sum(SALESORDERITEM.TOTAL),0) as GROSS,
        coalesce(sum(ITEMDISCOUNTS.AMOUNT),0) + coalesce(sum(SALESORDERITEMORDERDISCOUNTDETAIL.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 (
        select sum(AMOUNT) as AMOUNT, SALESORDERITEMID 
        from dbo.SALESORDERITEMITEMDISCOUNT 
        group by SALESORDERITEMID
    ) as ITEMDISCOUNTS on SALESORDERITEMTICKET.ID = ITEMDISCOUNTS.SALESORDERITEMID
    left join (
        select SALESORDERITEMID, sum(AMOUNT) as AMOUNT
        from dbo.SALESORDERITEMORDERDISCOUNTDETAIL
        group by SALESORDERITEMID
    ) as SALESORDERITEMORDERDISCOUNTDETAIL on SALESORDERITEMORDERDISCOUNTDETAIL.SALESORDERITEMID = SALESORDERITEM.ID
    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 SALESORDERITEMTYPE,
        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) - isnull(sum(SALESORDERITEMORDERDISCOUNTDETAIL.AMOUNT), 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)
    left outer join (
        select SALESORDERITEMID, sum(AMOUNT) as AMOUNT
        from dbo.SALESORDERITEMORDERDISCOUNTDETAIL
        group by SALESORDERITEMID
    ) as SALESORDERITEMORDERDISCOUNTDETAIL on SALESORDERITEMORDERDISCOUNTDETAIL.SALESORDERITEMID = EXT.SALESORDERITEMID
    group by
        VR.MEMBERSHIPLEVEL,
        VR.MEMBERSHIPRANK,
        PROGRAM.NAME

    --Event Registrations

    union all
    select 
        1 as SALESORDERITEMTYPE,
        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,
                isnull(sum(REFUNDEDLI.BASEAMOUNT), 0) as REFUND,
                VR.MEMBERSHIPLEVEL,
                VR.MEMBERSHIPRANK,
                EVENTREGISTRANTPAYMENT.REGISTRANTID as REGISTRANTID
            from @VALIDREFUNDS VR
            inner join dbo.CREDITPAYMENT on VR.ID = CREDITPAYMENT.CREDITID
            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
            group by EVENTREGISTRANTPAYMENT.REGISTRANTID, EVENT.NAME, VR.MEMBERSHIPLEVEL, VR.MEMBERSHIPRANK
        ) PAYMENTPIECES
        group by PROGRAMNAME, MEMBERSHIPLEVEL, MEMBERSHIPRANK, REGISTRANTID
    ) REGISTRANTS
    group by PROGRAMNAME, MEMBERSHIPLEVEL, MEMBERSHIPRANK

    --Membership

    union all
    select
        2 as SALESORDERITEMTYPE,
        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

    --Membership add-ons

    union all
    select
        11 as SALESORDERITEMTYPE,
        ADDON.NAME 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 as MEMBERSHIPLEVEL,
        VSO.MEMBERSHIPRANK as MEMBERSHIPRANK
    from
        dbo.SALESORDERITEMMEMBERSHIPADDON
    inner join
        dbo.SALESORDERITEM on SALESORDERITEM.ID = SALESORDERITEMMEMBERSHIPADDON.ID
    inner join
        @VALIDSALESORDERS VSO on VSO.ID = SALESORDERITEM.SALESORDERID
    inner join
        dbo.ADDON on ADDON.ID = SALESORDERITEMMEMBERSHIPADDON.ADDONID
    group by
        ADDON.NAME,
        VSO.MEMBERSHIPLEVEL,
        VSO.MEMBERSHIPRANK

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

    union all
    select
        8 as SALESORDERITEMTYPE,
        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 SALESORDERITEMTYPE,
        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 SALESORDERITEMTYPE,
        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
    group by
        VR.MEMBERSHIPLEVEL,
        VR.MEMBERSHIPRANK,
        MEMBERSHIPPROGRAM.NAME,
        CREDITITEMMEMBERSHIP.MEMBERSHIPPROGRAMNAME

    --Membership add-on refunds

    union all
    select
        11 as SALESORDERITEMTYPE,
        ADDON.NAME,
        -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.MEMBERSHIPADDON on MEMBERSHIPADDON.REVENUESPLITID = LI.SOURCELINEITEMID
    inner join
        dbo.ADDON on ADDON.ID = MEMBERSHIPADDON.ADDONID
    group by
        VR.MEMBERSHIPLEVEL,
        VR.MEMBERSHIPRANK,
        ADDON.NAME

    --Merchandise

    union all
    select
        3 as SALESORDERITEMTYPE,
        MERCHANDISEDEPARTMENT.NAME as PROGRAMNAME,
        coalesce(sum(SALESORDERITEM.QUANTITY),0) as QUANTITY,
        coalesce(sum(SALESORDERITEM.TOTAL),0) as GROSS,
        coalesce(sum(ITEMDISCOUNTS.AMOUNT),0) + coalesce(sum(SALESORDERITEMORDERDISCOUNTDETAIL.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 (
        select sum(AMOUNT) as AMOUNT, SALESORDERITEMID 
        from dbo.SALESORDERITEMITEMDISCOUNT 
        group by SALESORDERITEMID
    ) as ITEMDISCOUNTS on SALESORDERITEMMERCHANDISE.ID = ITEMDISCOUNTS.SALESORDERITEMID
    left join (
        select SALESORDERITEMID, sum(AMOUNT) as AMOUNT
        from dbo.SALESORDERITEMORDERDISCOUNTDETAIL group by  SALESORDERITEMID
    ) as SALESORDERITEMORDERDISCOUNTDETAIL on SALESORDERITEMORDERDISCOUNTDETAIL.SALESORDERITEMID = SALESORDERITEM.ID
    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) - isnull(sum(SALESORDERITEMORDERDISCOUNTDETAIL.AMOUNT), 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
    left outer join (
        select SALESORDERITEMID, sum(AMOUNT) as AMOUNT
        from dbo.SALESORDERITEMORDERDISCOUNTDETAIL
        group by SALESORDERITEMID
    ) as SALESORDERITEMORDERDISCOUNTDETAIL on SALESORDERITEMORDERDISCOUNTDETAIL.SALESORDERITEMID = EXT.SALESORDERITEMID
    group by
        VR.MEMBERSHIPLEVEL,
        VR.MEMBERSHIPRANK,
        MERCHANDISEDEPARTMENT.NAME

    -- Donations, Fees, Taxes

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

                4
            when 3 then  -- Fees

                5
            else  -- Taxes

                6
        end as SALESORDERITEMTYPE,
        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)  -- Donations, Fees, Taxes

    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 SALESORDERITEMTYPE,
        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

    -- Facilities

    union all
    select
        12 as SALESORDERITEMTYPE,
        SOIF.EVENTLOCATIONNAME as PROGRAMNAME,
        sum(SOI.QUANTITY) as QUANTITY,
        sum(SOI.TOTAL) as GROSS,
        0 as DISCOUNT,
        0 as REFUND,
        0 as ISORDERDISCOUNT,
        null as CATEGORY,
        VSO.MEMBERSHIPLEVEL,
        VSO.MEMBERSHIPRANK
    from @VALIDSALESORDERS VSO
        inner join dbo.SALESORDERITEM SOI on SOI.SALESORDERID = VSO.ID
        inner join dbo.SALESORDERITEMFACILITY SOIF on SOIF.ID = SOI.ID
    group by
        VSO.MEMBERSHIPLEVEL,
        VSO.MEMBERSHIPRANK,
        SOIF.EVENTLOCATIONNAME

    order by
        MEMBERSHIPRANK asc,
        ISORDERDISCOUNT asc,
        SALESORDERITEMTYPE asc,
        CATEGORY asc,
        PROGRAMNAME asc;

    return 0;