USP_DAILYSALES_MEMBERSHIPRENEWAL_GETMEMBERSANDCARDS

CREATE procedure dbo.USP_DAILYSALES_MEMBERSHIPRENEWAL_GETMEMBERSANDCARDS
(
    @SALESORDERID uniqueidentifier,
    @MEMBERSHIPID uniqueidentifier = null,
    @MEMBERSHIPPROGRAMID uniqueidentifier = null,
    @MEMBERSHIPLEVELID uniqueidentifier = null,
    @MEMBERSHIPLEVELTERMID uniqueidentifier = null,
    @MEMBERSHIPLEVELTYPECODEID uniqueidentifier = null,
    @EXPIRATIONDATE datetime = null,
    @MEMBERS xml = null output,
    @MEMBERSHIPCARDS xml = null output,
    @MEMBERSHIPPROGRAMADDON xml = null output,
    @ADDONMEMBERCOUNT integer = null output
)
as begin
    set nocount on;

    declare @NUMBEROFMEMBERSALLOWED tinyint = 0;
    declare @NUMBEROFMEMBERSHIPCARDSALLOWED tinyint = 0;
    declare @LASTTRANSACTIONID uniqueidentifier;                    
    declare @NUMBEROFMEMBERS tinyint;
    declare @NUMBEROFCARDS tinyint;

    select 
        top 1 @LASTTRANSACTIONID = ID 
    from 
        dbo.MEMBERSHIPTRANSACTION 
    where 
        MEMBERSHIPID = @MEMBERSHIPID 
        and REVENUESPLITID is not null
    order by 
        TRANSACTIONDATE desc, DATEADDED desc;

    -- Populating this collection because the save needs it to create the proper add-on records

    declare @MEMBERSHIPPROGRAMADDONSTABLE table  (ID uniqueidentifier, APPLY bit, ADDONID uniqueidentifier, ADDONTYPE tinyint, PRICE money, NUMBEROFADDONS integer, MULTIPLEALLOWED bit)
    insert into @MEMBERSHIPPROGRAMADDONSTABLE
    select
        newid() as ID,
        1 as APPLY,
        ADDON.ID as ADDONID,
        ADDON.ADDONTYPECODE as ADDONTYPE,
        MEMBERSHIPPROGRAMADDON.PRICE as PRICE,
        (MEMBERSHIPADDON.QUANTITY -  MEMBERSHIPADDON.NUMCANCELLED) NUMBEROFADDONS,
        MEMBERSHIPPROGRAMADDON.MULTIPLEALLOWED as MULTIPLEALLOWED
        --FINANCIALTRANSACTION.ID

    from 
        dbo.MEMBERSHIPPROGRAMADDON
    inner join
        dbo.ADDON on ADDON.ID = MEMBERSHIPPROGRAMADDON.ADDONID
    inner join 
        dbo.MEMBERSHIPADDON on MEMBERSHIPADDON.ADDONID = ADDON.ID
    inner join dbo.MEMBERSHIP
        on MEMBERSHIPADDON.MEMBERSHIPID = MEMBERSHIP.ID
    where 
        MEMBERSHIPADDON.MEMBERSHIPID = @MEMBERSHIPID
        and ADDON.ADDONTYPECODE = 1
        and (MEMBERSHIPADDON.EXPIRATIONDATE >= convert(date, coalesce(MEMBERSHIP.LASTRENEWEDON,MEMBERSHIP.JOINDATE)) or MEMBERSHIPADDON.EXPIRATIONDATE is null)
        and (MEMBERSHIPADDON.MEMBERSHIPTRANSACTIONID = @LASTTRANSACTIONID)
        and MEMBERSHIPPROGRAMADDON.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID

    set @MEMBERSHIPPROGRAMADDON =
    (
        select
            ID,
            APPLY,
            ADDONID,
            ADDONTYPE,
            PRICE,
            NUMBEROFADDONS,
            MULTIPLEALLOWED
        from
            @MEMBERSHIPPROGRAMADDONSTABLE
        where NUMBEROFADDONS > 0
        for xml raw('ITEM'),type,elements,root('MEMBERSHIPPROGRAMADDON'),binary base64
    )

    -- Need to know how many members are added through the add-ons... this value will be passed into the save to validate members allowed.

    select
        @ADDONMEMBERCOUNT = isnull(sum(T.membershipprogramaddon.value('(NUMBEROFADDONS)[1]','smallint')), 0)
    from
        @MEMBERSHIPPROGRAMADDON.nodes('/MEMBERSHIPPROGRAMADDON/ITEM') T(membershipprogramaddon)
    where 
        T.membershipprogramaddon.value('(ADDONTYPE)[1]','tinyint') = 1
        and T.membershipprogramaddon.value('(APPLY)[1]','bit') = 1

    select
        @NUMBEROFCARDS = count(MEMBERSHIPCARD.ID)
    from
        dbo.MEMBERSHIPCARD
    inner join
        dbo.MEMBER on MEMBER.ID = MEMBERSHIPCARD.MEMBERID
    where
        MEMBER.MEMBERSHIPID = @MEMBERSHIPID
        and MEMBER.ISDROPPED = 0
        and MEMBERSHIPCARD.STATUSCODE <> 2

    select
        @NUMBEROFMEMBERS = count(MEMBER.ID)
    from
        dbo.MEMBER
    where
        MEMBER.ISDROPPED = 0
        and MEMBER.MEMBERSHIPID = @MEMBERSHIPID

    select 
        @NUMBEROFMEMBERSALLOWED = MEMBERSALLOWED,
        @NUMBEROFMEMBERSHIPCARDSALLOWED = CARDSALLOWED
    from 
        dbo.MEMBERSHIPLEVEL
    where 
        ID = @MEMBERSHIPLEVELID

    declare @MEMBERSTABLE table (
        ID uniqueidentifier,
        MEMBERID uniqueidentifier,
        CONSTITUENTID uniqueidentifier,
        ISPRIMARY bit,
        SEQUENCE int  -- used so we can order the members who get cards

    );

    ;with MEMBERS_CTE as (
        select 
            newid() [ID],
            [MEMBER].[ID] as [MEMBERID],
            [MEMBER].[CONSTITUENTID],
            [MEMBER].[ISPRIMARY],
            row_number() over (order by ISPRIMARY desc, DATEADDED asc) as SEQUENCE
        from
            dbo.[MEMBER]
        where
            [MEMBER].[MEMBERSHIPID] = @MEMBERSHIPID and
            [MEMBER].[ISDROPPED] = 0
    )
    insert into @MEMBERSTABLE
    select
        *
    from
        MEMBERS_CTE
    where
        SEQUENCE <= (@NUMBEROFMEMBERSALLOWED + @ADDONMEMBERCOUNT);

    set @MEMBERS = (
        select
            ID,
            MEMBERID,
            CONSTITUENTID,
            ISPRIMARY
        from
            @MEMBERSTABLE
        order by
            SEQUENCE
        for xml raw('ITEM'),type,elements,root('MEMBERS'),binary base64
    );

    set @MEMBERSHIPCARDS = (
        select top (@NUMBEROFMEMBERSHIPCARDSALLOWED)
            newid() as ID,
            case
                when MEMBERSHIPCARD.STATUSCODE = 1 then -- Printed so we want to cancel and reissue

                    null
                else
                    MEMBERSHIPCARD.ID
            end as MEMBERSHIPCARDID,
            MEMBERSTABLE.ID as MEMBERID,
            isnull(MEMBERSHIPCARD.NAMEONCARD, NF.NAME) as NAMEONCARD,
            @EXPIRATIONDATE as EXPIRATIONDATE
        from
            @MEMBERSTABLE as MEMBERSTABLE
        left outer join
            dbo.MEMBERSHIPCARD on MEMBERSHIPCARD.MEMBERID = MEMBERSTABLE.MEMBERID and MEMBERSHIPCARD.STATUSCODE <> 2  -- Cancelled

        outer apply
            dbo.UFN_CONSTITUENT_DISPLAYNAME(MEMBERSTABLE.CONSTITUENTID) as NF
        where
            @NUMBEROFCARDS = 0  -- If there are currently no cards, treat this like a membership join and add as many cards as possible

            or MEMBERSHIPCARD.ID is not null  -- Otherwise, include cards that have not been cancelled

        order by
            case
                when MEMBERSHIPCARD.STATUSCODE = 1 then  -- Printed, if they were printed, they are probably important

                    0
                when MEMBERSHIPCARD.STATUSCODE = 0 then  -- Issued, these weren't cancelled so they are next in priority

                    1
                else  -- New card or existing cancelled card, least important to try an keep

                    2
            end,
            MEMBERSTABLE.SEQUENCE
        for xml raw('ITEM'),type,elements,root('MEMBERSHIPCARDS'),binary base64
    );
end