USP_DATALIST_SALESORDER_MEMBERSHIPS

CREATE procedure dbo.USP_DATALIST_SALESORDER_MEMBERSHIPS
(
    @CONTEXTID uniqueidentifier
)
as
    set nocount on;

    declare @CURRENTDATE date = getdate();

    with MEMBERSHIP_CTE as
    (
        select
            SALESORDERITEM.ID,
            case
                when FT.DATE is not null then
                    'Refund ' + convert(nvarchar(20), cast(FT.DATE as datetime), 1) + ', ' + SALESORDERITEM.DESCRIPTION
                else
                    SALESORDERITEM.DESCRIPTION
            end DESCRIPTION,
            SALESORDERITEM.PRICE,
            SALESORDERITEM.QUANTITY,
            SALESORDERITEM.TOTAL,
            coalesce(
                (
                    select top 1 MEMBER.ID
                    from dbo.MEMBER
                    where 
                        MEMBER.MEMBERSHIPID = MEMBERSHIP.ID and
                        MEMBER.CONSTITUENTID = SALESORDER.CONSTITUENTID
                ),
                (
                    select top 1 MEMBER.ID
                    from dbo.MEMBER
                    where 
                        MEMBER.MEMBERSHIPID = MEMBERSHIP.ID and
                        MEMBER.ISPRIMARY = 1 and
                        MEMBER.ISDROPPED = 0
                )
            ) as MEMBERID,
            MEMBERSHIP.ID as MEMBERSHIPID,
            case
                when exists(
                    select MEMBERSHIPCARD.ID 
                    from dbo.MEMBERSHIPCARD 
                    inner join.MEMBER on MEMBERSHIPCARD.MEMBERID = MEMBER.ID
                    where MEMBERSHIPCARD.STATUSCODE = 0 and
                    @CURRENTDATE < MEMBERSHIPCARD.EXPIRATIONDATE and
                    MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
                ) then 1
                else 0 
            end as HASCARDSTOPRINT,
            (
                select MEMBERSHIPTRANSACTION.REVENUESPLITID 
                from dbo.MEMBERSHIPTRANSACTION
                inner join dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.ID = MEMBERSHIPTRANSACTION.REVENUESPLITID
                inner join SALESORDER on SALESORDER.REVENUEID = LI.FINANCIALTRANSACTIONID
                where
                    MEMBERSHIPTRANSACTION.MEMBERSHIPID = MEMBERSHIP.ID and
                    SALESORDER.ID = SALESORDERITEM.SALESORDERID
            ) as REVENUESPLITID,
            case
                when LI.ID is null then
                    0
                else
                    1
            end ISREFUNDED,
            APPLIEDTICKETSSALESORDER.APPLIEDTOMEMBERSHIPSALESORDERID,
            case
                when APPLIEDTICKETSSALESORDER.APPLIEDTOMEMBERSHIPSALESORDERID is null then
                    0
                else
                    1
            end MEMBERSHIPHASAPPLIEDTICKETS
        from
            dbo.SALESORDER
        inner join
            dbo.SALESORDERITEM on SALESORDER.ID = SALESORDERITEM.SALESORDERID
        inner join
            dbo.SALESORDERITEMMEMBERSHIP on SALESORDERITEM.ID = SALESORDERITEMMEMBERSHIP.ID
        left join
            dbo.MEMBERSHIP on SALESORDERITEMMEMBERSHIP.MEMBERSHIPID = MEMBERSHIP.ID
        left join (
            select distinct
                SALESORDERITEMMEMBERSHIPITEMPROMOTION.SALESORDERITEMID SALESORDERITEMMEMBERSHIPID,
                APPLIEDITEM.SALESORDERID APPLIEDTOMEMBERSHIPSALESORDERID
            from
                dbo.SALESORDERITEM
            inner join
                dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION on SALESORDERITEM.ID = SALESORDERITEMMEMBERSHIPITEMPROMOTION.SALESORDERITEMID
            inner join
                dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTIONAPPLIEDITEM on SALESORDERITEMMEMBERSHIPITEMPROMOTION.ID = SALESORDERITEMMEMBERSHIPITEMPROMOTIONAPPLIEDITEM.SALESORDERITEMMEMBERSHIPITEMPROMOTIONID
            inner join
                dbo.SALESORDERITEM APPLIEDITEM on SALESORDERITEMMEMBERSHIPITEMPROMOTIONAPPLIEDITEM.APPLIEDSALESORDERITEMID = APPLIEDITEM.ID
            where
                SALESORDERITEM.SALESORDERID = @CONTEXTID
        ) as APPLIEDTICKETSSALESORDER on SALESORDERITEMMEMBERSHIP.ID = APPLIEDTICKETSSALESORDER.SALESORDERITEMMEMBERSHIPID
        left outer join
            dbo.CREDITITEM_EXT as EXT on EXT.SALESORDERITEMID = SALESORDERITEM.ID
        left outer join
            dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.ID = EXT.ID
        left outer join
            dbo.FINANCIALTRANSACTION as FT on FT.ID = LI.FINANCIALTRANSACTIONID
        where
            SALESORDER.ID = @CONTEXTID
    )
    select
        ID,
        DESCRIPTION,
        PRICE,
        QUANTITY,
        TOTAL,
        MEMBERID,
        MEMBERSHIPID,
        HASCARDSTOPRINT,
        REVENUESPLITID,
        ISREFUNDED,
        APPLIEDTOMEMBERSHIPSALESORDERID,
        MEMBERSHIPHASAPPLIEDTICKETS,
        0 as ISADDONREFUND
    from MEMBERSHIP_CTE

    union all
    --we have a separate repeater view to house add-ons that have been refunded

    --for a given membership

    select
        --We need it to pick one of the refunded add-on id's... it doesn't matter which one

        max(convert(nvarchar(36), SALESORDERITEMMEMBERSHIPADDON.ID)),
        'Refund, Add-on(s)' as DESCRIPTION,
        null as PRICE,
        null as QUANTITY,
        null as TOTAL,
        MEMBERSHIP_CTE.MEMBERID,
        MEMBERSHIP_CTE.MEMBERSHIPID,
        0 as HASCARDSTOPRINT,
        null as REVENUESPLITID,
        1 as ISREFUNDED,
        null as APPLIEDTOMEMBERSHIPSALESORDERID,
        0 as MEMBERSHIPHASAPPLIEDTICKETS,
        1 as ISADDONREFUND
    from MEMBERSHIP_CTE
    inner join dbo.SALESORDERITEMMEMBERSHIPADDON on SALESORDERITEMMEMBERSHIPADDON.SALESORDERITEMMEMBERSHIPID = MEMBERSHIP_CTE.ID
    inner join dbo.CREDITITEM_EXT on CREDITITEM_EXT.SALESORDERITEMID = SALESORDERITEMMEMBERSHIPADDON.ID
    group by MEMBERSHIP_CTE.ID, MEMBERSHIP_CTE.MEMBERID, MEMBERSHIP_CTE.MEMBERSHIPID

    order by MEMBERSHIP_CTE.MEMBERSHIPID, ISADDONREFUND

    return 0;