USP_BBNC_COMMITPROFILEUPDATEEMAILBYID

CREATE procedure dbo.USP_BBNC_COMMITPROFILEUPDATEEMAILBYID
(
    @ID uniqueidentifier = null,                
    @CONSTITUENTID uniqueidentifier = null,            
    @EMAILADDRESSTYPECODEID uniqueidentifier = null,
    @EMAILADDRESS nvarchar(100) = '',
    @CHANGEAGENTID uniqueidentifier = null,
    @CHANGEDATE datetime = null
) 
as
set nocount on;

declare @SEQUENCE int;
declare @PRIMARY bit;
declare @EMAILADDRESSCOUNT int;
declare @STARTDATE datetime;
declare @ENDDATE datetime;

if @CONSTITUENTID is null
    begin
        raiserror('The constituent ID is required',16,1);
        return -2;
    end

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

if @CHANGEDATE is null
    set @CHANGEDATE = getdate();

if @STARTDATE is null
    set @STARTDATE = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0);

if @ENDDATE is null
    set @ENDDATE = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0);

declare @DEFAULTEMAILADDRESSTYPECODEID uniqueidentifier;
select top (1)
    @DEFAULTEMAILADDRESSTYPECODEID = [EMAILADDRESSTYPECODEID]
from
    dbo.NETCOMMUNITYDEFAULTCODEMAP;

declare @INFOSOURCECODEID uniqueidentifier;
select top (1)
    @INFOSOURCECODEID = [INFOSOURCECODEID]
from
    dbo.NETCOMMUNITYDEFAULTCODEMAP;

declare @NULLVALUE uniqueidentifier = newid();

begin try
    if @ID is not null
        begin
            -- check from USP_DATAFORMTEMPLATE_EDIT_EMAILADDRESS_5

            -- #428270 Check to see if there are duplicate email addresses.  Same email address type and same email address

            if not exists (select 'X' from dbo.EMAILADDRESS a
                        inner join dbo.EMAILADDRESS a2 on a2.CONSTITUENTID = a.CONSTITUENTID and a2.ID <> @ID
                        where (coalesce(a.EMAILADDRESS,'') <> coalesce(@EMAILADDRESS,'') 
                                or coalesce(a.EMAILADDRESSTYPECODEID,@NULLVALUE) <> coalesce(@EMAILADDRESSTYPECODEID,@NULLVALUE)) 
                        and a.ID = @ID
                        and a2.ENDDATE is null and a2.DONOTEMAIL = 0
                        and coalesce(a2.EMAILADDRESS,'') = coalesce(@EMAILADDRESS,'') 
                        and coalesce(a2.EMAILADDRESSTYPECODEID,@NULLVALUE) = coalesce(@EMAILADDRESSTYPECODEID,@NULLVALUE)) 
                begin
                    select @SEQUENCE = max([SEQUENCE]) 
                        from dbo.EMAILADDRESS
                        where [CONSTITUENTID] = @CONSTITUENTID;

                        set @PRIMARY = 1

                        if @SEQUENCE is null
                            begin
                                set @SEQUENCE = 1
                            end

                        -- Check the same as USP_DATAFORMTEMPLATE_ADD_EMAILADDRESS!

                        -- #428270 Check to see if there are duplicate email addresses.  Same email address type and same email address

                        select    
                                @EMAILADDRESSCOUNT = count(*) 
                        from    
                                dbo.EMAILADDRESS
                        where    
                                ENDDATE is null 
                                and    DONOTEMAIL = 0 
                                and    CONSTITUENTID = @CONSTITUENTID
                                and    coalesce(EMAILADDRESS,'') = coalesce(@EMAILADDRESS,'') 
                                and    coalesce(EMAILADDRESSTYPECODEID,@NULLVALUE) = coalesce(@EMAILADDRESSTYPECODEID,@NULLVALUE);

                        if (@EMAILADDRESSCOUNT = 0)
                        begin

                            --action the update


                            update dbo.EMAILADDRESS set
                                [ISPRIMARY] = 0,
                                [DONOTEMAIL] = 1,
                                [CHANGEDBYID] = @CHANGEAGENTID,
                                [DATECHANGED] = @CHANGEDATE,
                                [ENDDATE] = @ENDDATE
                            where 
                                [CONSTITUENTID] = @CONSTITUENTID and [ISPRIMARY] = 1;

                            insert into dbo.EMAILADDRESS
                            (
                                [ID],
                                [CONSTITUENTID],
                                [EMAILADDRESSTYPECODEID],
                                [EMAILADDRESS],
                                [ISPRIMARY],
                                [SEQUENCE],
                                [INFOSOURCECODEID],
                                [ADDEDBYID],
                                [CHANGEDBYID],
                                [DATEADDED],
                                [DATECHANGED],
                                [STARTDATE]
                            )
                            values
                            (
                                newid(),
                                @CONSTITUENTID,
                                coalesce(@EMAILADDRESSTYPECODEID, @DEFAULTEMAILADDRESSTYPECODEID),
                                @EMAILADDRESS,
                                @PRIMARY,
                                @SEQUENCE + 1,
                                @INFOSOURCECODEID,
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
                                @CHANGEDATE,
                                @CHANGEDATE,
                                @STARTDATE
                            );
                        end
                end;
        end
    else
        begin
            select @SEQUENCE = max([SEQUENCE]) 
            from dbo.EMAILADDRESS
            where [CONSTITUENTID] = @CONSTITUENTID;

            set @PRIMARY = 1

            if @SEQUENCE is null
                begin
                    set @SEQUENCE = 1
                end

            -- Check the same as USP_DATAFORMTEMPLATE_ADD_EMAILADDRESS!

            -- #428270 Check to see if there are duplicate email addresses.  Same email address type and same email address

            select    
                    @EMAILADDRESSCOUNT = count(*) 
            from    
                    dbo.EMAILADDRESS
            where    
                    ENDDATE is null 
                    and    DONOTEMAIL = 0 
                    and    CONSTITUENTID = @CONSTITUENTID
                    and    coalesce(EMAILADDRESS,'') = coalesce(@EMAILADDRESS,'') 
                    and    coalesce(EMAILADDRESSTYPECODEID,@NULLVALUE) = coalesce(@EMAILADDRESSTYPECODEID,@NULLVALUE);

            if (@EMAILADDRESSCOUNT = 0)
            begin

                update dbo.EMAILADDRESS set
                    [ISPRIMARY] = 0,
                    [DONOTEMAIL] = 1,
                    [CHANGEDBYID] = @CHANGEAGENTID,
                    [DATECHANGED] = @CHANGEDATE,
                    [ENDDATE] = @ENDDATE
                where 
                    [CONSTITUENTID] = @CONSTITUENTID and [ISPRIMARY] = 1;

                insert into dbo.EMAILADDRESS
                (
                    [ID],
                    [CONSTITUENTID],
                    [EMAILADDRESSTYPECODEID],
                    [EMAILADDRESS],
                    [ISPRIMARY],
                    [SEQUENCE],
                    [INFOSOURCECODEID],
                    [ADDEDBYID],
                    [CHANGEDBYID],
                    [DATEADDED],
                    [DATECHANGED],
                    [STARTDATE]
                )
                values
                (
                    newid(),
                    @CONSTITUENTID,
                    coalesce(@EMAILADDRESSTYPECODEID, @DEFAULTEMAILADDRESSTYPECODEID),
                    @EMAILADDRESS,
                    @PRIMARY,
                    @SEQUENCE + 1,
                    @INFOSOURCECODEID,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CHANGEDATE,
                    @CHANGEDATE,
                    @STARTDATE
                );
            end
        end
end try
begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
end catch

return 0;