USP_DATAFORMTEMPLATE_VIEW_DAILYSALESPATRONMEMBERSHIPS

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_DAILYSALESPATRONMEMBERSHIPS
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @MEMBERSHIPS xml = null output
)
as
    set nocount on;

    declare @CLIENTCURRENTDATE date = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEFROMUTC(getutcdate());

    set @DATALOADED = 1;

    set @MEMBERSHIPS = (
        select
            [MEMBERSHIP].[ID] as [ID],
            MEMBERSHIPPROGRAM.NAME + ' - ' +
            MEMBERSHIPLEVEL.NAME +
                (case
                    when [MEMBERSHIP].[EXPIRATIONDATE] is null then
                        ''
                    else
                        ' (' + dbo.UFN_MEMBERSHIPLEVELTERM_GETVALUE([MEMBERSHIP].[MEMBERSHIPLEVELTERMID]) + ')'
                end) +
                (case
                    when [MEMBERSHIP].[MEMBERSHIPLEVELTYPECODEID] is null then
                        ''
                        else
                        ': ' + dbo.UFN_MEMBERSHIPLEVELTYPECODE_GETDESCRIPTION([MEMBERSHIP].[MEMBERSHIPLEVELTYPECODEID])
                end)
            as [DESCRIPTION],
            case
                when [MEMBERSHIP].[STATUSCODE] = 1 then [MT].[TRANSACTIONDATE]
                else [MEMBERSHIP].[EXPIRATIONDATE]
            end [MEMBERSHIPEXPIRATION],
            case when @CLIENTCURRENTDATE > cast([MEMBERSHIP].[EXPIRATIONDATE] as date) then 1
                else 0
            end [ISLAPSED],
            case
                when cast(dateadd(month, [MEMBERSHIPLEVEL].[AFTEREXPIRATION], [MEMBERSHIP].[EXPIRATIONDATE]) as date) <= @CLIENTCURRENTDATE then 0 --Rejoin
                when MEMBERSHIP.STATUSCODE = 1 then 0 --Rejoin
                else 1 --Renew
            end RENEWSTATUS,
            (
                select
                    CONSTITUENT.NAME
                from dbo.MEMBER
                inner join dbo.CONSTITUENT on MEMBER.CONSTITUENTID = CONSTITUENT.ID
                where MEMBERSHIPID = MEMBERSHIP.ID
                    and ISDROPPED = 0
                order by MEMBER.ISPRIMARY desc
                for xml raw('ITEM'),type,elements, binary base64
            ) as OTHERMEMBERS,
            (
                select ID
                from dbo.MEMBERSHIPLEVEL as SUBMEMBERSHIPLEVEL
                where SUBMEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
                and SUBMEMBERSHIPLEVEL.SEQUENCE = MEMBERSHIPLEVEL.SEQUENCE + 1
            ) UPGRADELEVELID,
            (
                select TERM.ID
                from dbo.MEMBERSHIPLEVEL as SUBMEMBERSHIPLEVEL
                inner join dbo.MEMBERSHIPLEVELTERM TERM on TERM.LEVELID = SUBMEMBERSHIPLEVEL.ID
                where SUBMEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
                and SUBMEMBERSHIPLEVEL.SEQUENCE = MEMBERSHIPLEVEL.SEQUENCE + 1
                and TERM.SEQUENCE = MEMBERSHIPLEVELTERM.SEQUENCE + 1
            ) UPGRADETERMID,
            MEMBER.ID as MEMBERID,
            [MEMBERSHIPLEVEL].[ISACTIVE] as [MEMBERSHIPLEVELACTIVE],
            [MEMBERSHIPPROGRAM].[PROGRAMTYPECODE] as PROGRAMTYPECODE,
            [MEMBERSHIPPROGRAM].[PROGRAMBASEDONCODE] as PROGRAMBASEDONCODE,
            [MEMBERSHIPPROGRAM].[ONEPAYMENTEACHTERM] as PROGRAMONEPAYMENTEACHTERM,
            [MEMBERSHIP].[STATUSCODE] as MEMBERSHIPSTATUSCODE,
            MEMBERSHIPPROGRAM.ISACTIVE as MEMBERSHIPPROGRAMISACTIVE,
            MEMBERSHIPLEVELTERM.AMOUNT as TERMAMOUNT,
            MEMBERSHIPLEVELTERM.BASECURRENCYID as TERMBASECURRENCYID,
            MEMBERSHIPLEVELTERM.RECURRINGPAYMENTOPTION,
            [MEMBERSHIP].NUMBEROFCHILDREN,
            (
                select
                    ADDON.NAME,
                    MEMBERSHIPADDON.QUANTITY - MEMBERSHIPADDON.NUMCANCELLED as QUANTITY
                            from MEMBERSHIPADDON
                inner join ADDON on MEMBERSHIPADDON.ADDONID = ADDON.ID
                where MEMBERSHIPADDON.MEMBERSHIPID = MEMBERSHIP.ID
                    and MEMBERSHIPADDON.QUANTITY - MEMBERSHIPADDON.NUMCANCELLED > 0
                    and MEMBERSHIPADDON.MEMBERSHIPTRANSACTIONID = dbo.UFN_MEMBERSHIP_GETLASTMEMBERSHIPTRANSACTIONID(MEMBERSHIP.ID)  -- Omit addons on previous transactions
                            order by ADDON.NAME
                for xml raw('ITEM'),type,elements, binary base64
            ) as ADDONS
        from dbo.[MEMBER]
            inner join dbo.[MEMBERSHIP] on [MEMBER].[MEMBERSHIPID] = [MEMBERSHIP].[ID]
            inner join dbo.MEMBERSHIPPROGRAM on MEMBERSHIP.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
            inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIP.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
            inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIP.MEMBERSHIPLEVELTERMID = MEMBERSHIPLEVELTERM.ID
            left join dbo.[MEMBERSHIPTRANSACTION] [MT] on ([MEMBERSHIP].[ID] = [MT].[MEMBERSHIPID] and [MT].[ACTIONCODE] in (4,6)) and ([MT].[ID] in (select top 1 [ID] from dbo.[MEMBERSHIPTRANSACTION] [MT2] where [MT2].[ACTIONCODE] in (4,6) and [MT2].[MEMBERSHIPID] = [MEMBERSHIP].[ID] order by [MT2].[TRANSACTIONDATE] desc))
        where
            [MEMBER].[CONSTITUENTID] = @ID and
            [MEMBER].[ISDROPPED] = 0 and
            [MEMBERSHIP].[STATUSCODE] <> 2
        order by
            MEMBERSHIP.STATUSCODE asc, ISLAPSED asc, MEMBERSHIP.JOINDATE asc
        for xml raw ('ITEM'), type, elements, root('MEMBERSHIPS'), BINARY BASE64
    );

    return 0;