USP_DATAFORMTEMPLATE_EDIT_MEMBERS

CREATE procedure dbo.[USP_DATAFORMTEMPLATE_EDIT_MEMBERS]
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @MEMBERS xml,
    @NUMBEROFCHILDREN smallint
)
as
    begin try
        declare @CURRENTDATE datetime;
        set @CURRENTDATE = getdate();

        if @CHANGEAGENTID is null
            exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

        declare @MEMBERSTABLE table (
            ID uniqueidentifier,
            CONSTITUENTID uniqueidentifier,
            ISPRIMARY bit
        );

        insert into @MEMBERSTABLE (
            ID,
            CONSTITUENTID,
            ISPRIMARY
        )
        select
            T.members.value('(MEMBERID)[1]', 'uniqueidentifier'),
            T.members.value('(CONSTITUENTID)[1]', 'uniqueidentifier'),
            T.members.value('(ISPRIMARY)[1]', 'bit')
        from
            @MEMBERS.nodes('/MEMBERS/ITEM') T(members);

        if not exists (select 1 from @MEMBERSTABLE where ISPRIMARY = 1)
            raiserror('BBERR_PRIMARYMEMBERMISSING', 13, 1);

        update @MEMBERSTABLE set ID = newid()
        from
            @MEMBERSTABLE MEMBERSTABLE
        left join dbo.MEMBER
            on MEMBERSTABLE.ID = MEMBER.ID
        where MEMBERSTABLE.ID is null
            or MEMBERSTABLE.ID = '00000000-0000-0000-0000-000000000000'
            or (MEMBER.ID is not null and MEMBERSTABLE.CONSTITUENTID <> MEMBER.CONSTITUENTID);

        -- Drop member records not in the collection
        update dbo.MEMBER set
            ISDROPPED = 1,
            ISPRIMARY = 0,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        from
            dbo.MEMBER
        left outer join
            @MEMBERSTABLE as MEMBERSTABLE on MEMBERSTABLE.ID = MEMBER.ID
        where
            MEMBER.MEMBERSHIPID = @ID
            and MEMBERSTABLE.ID is null;

        -- Cancel cards belonging to dropped members
        update dbo.MEMBERSHIPCARD set
            STATUSCODE = 2,  -- Cancelled
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        from
            dbo.MEMBERSHIPCARD
        inner join
            dbo.MEMBER on MEMBER.ID = MEMBERSHIPCARD.MEMBERID
        left outer join
            @MEMBERSTABLE as MEMBERSTABLE on MEMBERSTABLE.ID = MEMBER.ID
        where
            MEMBER.MEMBERSHIPID = @ID
            and MEMBERSTABLE.ID is null;

        -- Update existing member records
        update dbo.MEMBER set
            CONSTITUENTID = MEMBERSTABLE.CONSTITUENTID,
            ISPRIMARY = MEMBERSTABLE.ISPRIMARY,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        from
            dbo.MEMBER
        inner join
            @MEMBERSTABLE as MEMBERSTABLE on MEMBERSTABLE.ID = MEMBER.ID
        where
            MEMBER.ISDROPPED = 0;

        -- Insert new member records
        insert into dbo.[MEMBER]
        (
            [ID],
            [CONSTITUENTID],
            [MEMBERSHIPID],
            [ISPRIMARY],
            [ADDEDBYID],
            [CHANGEDBYID],
            [DATEADDED],
            [DATECHANGED]
        )
        select
            newid(),
            CONSTITUENTID,
            @ID as [MEMBERSHIPID],
            ISPRIMARY,
            @CHANGEAGENTID as [ADDEDBYID],
            @CHANGEAGENTID as [CHANGEDBYID],
            @CURRENTDATE as [DATEADDED],
            @CURRENTDATE as [DATECHANGED]
        from
            @MEMBERSTABLE
        where
            ID not in (select ID from dbo.[MEMBER] where [MEMBERSHIPID] = @ID);

        update dbo.[MEMBERSHIP] set
            [NUMBEROFCHILDREN] = @NUMBEROFCHILDREN,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where ID = @ID;
    end try

    begin catch
        exec dbo.[USP_RAISE_ERROR];
        return 1;
    end catch

    return 0;