USP_DATAFORMTEMPLATE_VIEW_WEBFORMS_MEMBERSHIP_BYEMAILJOBRECIPIENT

            CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_WEBFORMS_MEMBERSHIP_BYEMAILJOBRECIPIENT
            (
                @ID nvarchar(40),
                @DATALOADED bit = 0 output,
                @CONSTITUENTID uniqueidentifier = null output,
                @MEMBERSHIPID uniqueidentifier = null output,
                @MEMBERSHIPPROGRAMID uniqueidentifier = null output,
                @MEMBERSHIPLEVELID uniqueidentifier = null output,
                @MEMBERSHIPLEVELTERMID uniqueidentifier = null output,
                @MEMBERSHIPLEVELTYPECODEID uniqueidentifier = null output,
                @EXPIRATIONDATE datetime = null output,
                @NUMBEROFCHILDREN smallint = null output,
                @ISGIFT bit = null output,
                @SENDRENEWALCODE smallint = null output,
                @GIVENBYID uniqueidentifier = null output,
                @GIFTERFIRSTNAME nvarchar(50) = null output,
                @GIFTERKEYNAME nvarchar(100) = null output,
                @STATUSCODE tinyint = null output,
                @MEMBERS xml = null output,
                @EMAILTRACKINGID uniqueidentifier = null output,
                @BACKOFFICERECORDID bigint = null output,
                @SEQUENCEID integer = null output,
                @GIFTERSEQUENCEID integer = null output
            )
            as
                set nocount on;

                set @DATALOADED = 0;

                declare @CURRENTDATE datetime = getdate();

                declare @EMAILJOBRECIPIENTID int;
                declare @FINDERNUMBER bigint;

                declare @SEPARATOR tinyint;
                select @SEPARATOR = CHARINDEX('|', @ID);
                if @SEPARATOR = 0
                    set @SEPARATOR = len(@ID)

                set @EMAILJOBRECIPIENTID = cast(left(@ID, @SEPARATOR - 1) as integer)
                set @FINDERNUMBER = cast(right(@ID, len(@ID) - @SEPARATOR) as bigint)

                if @EMAILJOBRECIPIENTID = 0
                    return 0;

                select 
                    @BACKOFFICERECORDID = [BackOfficeSystemPeople].[BackofficeRecordID],
                    @EMAILTRACKINGID = [EmailJob_Recipient].[MERGEID]
                from dbo.[EmailJob_Recipient]
                inner join dbo.[BackOfficeSystemPeople]
                    on  [EmailJob_Recipient].[BackOfficeSystemPeopleID] = [BackOfficeSystemPeople].[ID]
                where [EmailJob_Recipient].[ID] = @EMAILJOBRECIPIENTID;

                if @FINDERNUMBER is null or @FINDERNUMBER = 0
                    set @FINDERNUMBER = @BACKOFFICERECORDID;

                -- Find the mailing for this finder number
                declare @SQL nvarchar(max);
                declare @PARAMS nvarchar(200);
                declare @MAILINGID uniqueidentifier = dbo.[UFN_MKTSEGMENTATIONFINDERNUMBER_GETSEGMENTATION](@FINDERNUMBER);
                declare @ACTIVATEDMAILINGTABLE nvarchar(128) = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME](@MAILINGID);

                --Getting constituent and membership id
                --Making sure output table has the membership id column
                if exists(select 1 from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @ACTIVATEDMAILINGTABLE and [COLUMN_NAME] = 'MEMBERSHIPID')
                begin
                    set @SQL = 
                        'select
                            @CONSTITUENTID = [DONORID],
                            @MEMBERSHIPID = [MEMBERSHIPID]
                        from dbo.[' + @ACTIVATEDMAILINGTABLE + ']
                        where [FINDERNUMBER] = @FINDERNUMBER';

                    set @PARAMS = '@FINDERNUMBER bigint, @CONSTITUENTID uniqueidentifier output, @MEMBERSHIPID uniqueidentifier output';
                    exec sp_executesql @SQL, @PARAMS, @FINDERNUMBER = @FINDERNUMBER, @CONSTITUENTID = @CONSTITUENTID output, @MEMBERSHIPID = @MEMBERSHIPID output;
                end
                else
                begin
                    set @SQL = 
                        'select @CONSTITUENTID = [DONORID]
                        from dbo.[' + @ACTIVATEDMAILINGTABLE + ']
                        where [FINDERNUMBER] = @FINDERNUMBER';

                    set @PARAMS = '@FINDERNUMBER bigint, @CONSTITUENTID uniqueidentifier output';
                    exec sp_executesql @SQL, @PARAMS, @FINDERNUMBER = @FINDERNUMBER, @CONSTITUENTID = @CONSTITUENTID output;
                end

                --The membership id should have been set, but let's do our best to figure out what it should have been if not
                if @MEMBERSHIPID is null
                begin
                    select @MEMBERSHIPPROGRAMID =  [APPEAL].[MEMBERSHIPPROGRAMID]
                    from dbo.[MKTSEGMENTATION] with (nolock)
                    inner join dbo.[MKTSEGMENTATIONACTIVATE] with (nolock) on 
                        [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID] and
                        [MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID] = 'DFB4B8C1-5E9A-4C14-ACE3-01C096B53BA0' --Constituent marketing information record source
                    inner join dbo.[APPEAL] with (nolock) on
                        [MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID] = [APPEAL].[ID]
                    where [MKTSEGMENTATION].[ID] = @MAILINGID;

                    select top 1 @MEMBERSHIPID = [MEMBERSHIP].[ID]
                    from dbo.[MEMBER] with (nolock)
                    inner join dbo.[MEMBERSHIP] with (nolock) on
                        [MEMBER].[MEMBERSHIPID] = [MEMBERSHIP].[ID]
                    inner join dbo.[MEMBERSHIPLEVEL] with (nolock) on
                        [MEMBERSHIP].[MEMBERSHIPLEVELID] = [MEMBERSHIPLEVEL].[ID]
                    cross apply(
                        select dateadd(month, [MEMBERSHIPLEVEL].[AFTEREXPIRATION], [MEMBERSHIP].[EXPIRATIONDATE]) as [END]
                    ) as [RENEWALWINDOW]
                    cross apply(
                        select datediff(day, @CURRENTDATE, [RENEWALWINDOW].[END]) as [DIFFERENCE]
                    ) as [WINDOWCLOSING]
                    where 
                        (
                            [MEMBERSHIP].[MEMBERSHIPPROGRAMID] = @MEMBERSHIPPROGRAMID or
                            @MEMBERSHIPPROGRAMID is null
                        ) and
                        [MEMBERSHIP].[STATUSCODE] <> 2 and --2: Canceled
                        [MEMBER].[ISDROPPED] = 0 and
                        (
                            [MEMBER].[CONSTITUENTID] = @CONSTITUENTID or
                            [MEMBERSHIP].[GIVENBYID] = @CONSTITUENTID
                        )
                    order by abs([WINDOWCLOSING].[DIFFERENCE]) asc --Getting the membership closest to falling out of the renewal window
                end
                else
                begin
                    --If we do have a membership, let's make sure it's still active
                    if not exists(
                        select 1 
                        from dbo.[MEMBERSHIP] with (nolock)
                        inner join dbo.[MEMBER] with (nolock) on
                            [MEMBERSHIP].[ID] = [MEMBER].[MEMBERSHIPID]
                        where
                            [MEMBERSHIP].[ID] = @MEMBERSHIPID and
                            [MEMBERSHIP].[STATUSCODE] <> 2 and --2: Canceled
                            [MEMBER].[ISDROPPED] = 0
                    )
                        set @MEMBERSHIPID = null;
                end

                if @MEMBERSHIPID is null
                    return 0;

                select
                    @DATALOADED = 1,
                    @MEMBERSHIPPROGRAMID =[MEMBERSHIP].[MEMBERSHIPPROGRAMID],
                    @MEMBERSHIPLEVELID = [MEMBERSHIP].[MEMBERSHIPLEVELID],
                    @MEMBERSHIPLEVELTERMID = [MEMBERSHIP].[MEMBERSHIPLEVELTERMID],
                    @MEMBERSHIPLEVELTYPECODEID = [MEMBERSHIP].[MEMBERSHIPLEVELTYPECODEID],
                    @EXPIRATIONDATE = [MEMBERSHIP].[EXPIRATIONDATE],
                    @NUMBEROFCHILDREN = [MEMBERSHIP].[NUMBEROFCHILDREN],
                    @ISGIFT = [MEMBERSHIP].[ISGIFT],
                    @SENDRENEWALCODE = [MEMBERSHIP].[SENDRENEWALCODE],
                    @GIVENBYID = [MEMBERSHIP].[GIVENBYID],
                    @STATUSCODE = [MEMBERSHIP].[STATUSCODE],
                    @MEMBERS = (
                        select
                            [MEMBER].[ID] as [MEMBERID],
                            [MEMBER].[CONSTITUENTID],
                            [MEMBER].[ISPRIMARY],
                            [CONSTITUENT].[FIRSTNAME],
                            [CONSTITUENT].[KEYNAME],
                            [TITLECODE].[DESCRIPTION] as [TITLE]
                        from dbo.[MEMBER] with (nolock)
                        inner join dbo.[CONSTITUENT] with (nolock)
                            on [MEMBER].[CONSTITUENTID] = [CONSTITUENT].[ID]
                        left join dbo.[TITLECODE]
                            on [CONSTITUENT].[TITLECODEID] = [TITLECODE].[ID]
                        where
                            [MEMBERSHIP].[ID] = [MEMBER].[MEMBERSHIPID] and
                            [MEMBER].[ISDROPPED] = 0
                        order by [MEMBER].[ISPRIMARY] desc
                        for xml raw ('ITEM'), type, elements, root('MEMBERS'), binary base64
                    )
                from dbo.[MEMBERSHIP]
                where [MEMBERSHIP].[ID] = @MEMBERSHIPID;

                select @SEQUENCEID = [SEQUENCEID]
                from dbo.[CONSTITUENT] where [ID] = @CONSTITUENTID

                select @GIFTERSEQUENCEID = [SEQUENCEID]
                from dbo.[CONSTITUENT] where [ID] = @GIVENBYID

                if @ISGIFT = 1
                begin
                    --See if giver is the recipient of the email and default their name is so       
                    --Option 1: Checking to see if the back office record id happens to be the giver's sequence ID (should be case in 2.9)
                    if exists(select 1 from dbo.[CONSTITUENT] where [ID] = @GIVENBYID and [SEQUENCEID] = @BACKOFFICERECORDID)
                        select
                            @GIFTERFIRSTNAME = [FIRSTNAME],
                            @GIFTERKEYNAME = [KEYNAME]
                        from dbo.[CONSTITUENT] with (nolock)
                        where [ID] = @GIVENBYID
                    --Option 2: See if the membership is set to send renewals to the giver and then check if the email address of the recipient exists on the giver's record
                    else if @SENDRENEWALCODE in (0,2)
                    begin
                        declare @GIVEREMAILADDRESS nvarchar(255)
                        select @GIVEREMAILADDRESS = [EmailAddress] from dbo.[EmailJob_Recipient] where [ID] = @EMAILJOBRECIPIENTID
                        if exists(select 1 from dbo.[EMAILADDRESS] where [CONSTITUENTID]  = @GIVENBYID and [EMAILADDRESS] = @GIVEREMAILADDRESS)
                        begin
                            select
                                @GIFTERFIRSTNAME = [FIRSTNAME],
                                @GIFTERKEYNAME = [KEYNAME]
                            from dbo.[CONSTITUENT] with (nolock)
                            where [ID] = @GIVENBYID
                        end
                        else if @SENDRENEWALCODE = 2
                            set @ISGIFT = 0
                    end
                    --Otherwise, let's not call this a gift
                    else
                        set @ISGIFT = 0
                end

                return 0;