USP_DATAFORMTEMPLATE_ADD_BATCHEMAILFINDERBATCHCOMMIT

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_BATCHEMAILFINDERBATCHCOMMIT
(
    @ID uniqueidentifier = null output,
    @VALIDATEONLY bit = 0,
    @CHANGEAGENTID uniqueidentifier,
    @EMAILFINDERID uniqueidentifier,
    @CONSTITUENTID uniqueidentifier = null,
    @ADDRESSID uniqueidentifier = null,
    @EMAILADDRESS dbo.UDT_EMAILADDRESS = null,
    @OPTOUT bit = null
) 
as
    set nocount on;

    declare @CURRENTDATE datetime;

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

    set @CURRENTDATE = getdate();

    begin try
        --Get EmailFinder process options
        declare @EMAILADDRESSTYPECODEID uniqueidentifier;
        declare @MARKASPRIMARY bit;
        declare @INFOSOURCECODEID uniqueidentifier;
        declare @INFOSOURCECOMMENTS nvarchar(256);

        select
            @EMAILADDRESSTYPECODEID = EMAILFINDER.EMAILADDRESSTYPECODEID,
            @MARKASPRIMARY = EMAILFINDER.MARKASPRIMARY,
            @INFOSOURCECODEID = EMAILFINDER.INFOSOURCECODEID,
            @INFOSOURCECOMMENTS =  EMAILFINDER.INFOSOURCECOMMENTS
        from
            dbo.EMAILFINDER
        where
            EMAILFINDER.ID = @EMAILFINDERID;

        --Check for existing address
        set @ID = null;
        select @ID = ID from dbo.EMAILADDRESS where CONSTITUENTID = @CONSTITUENTID and EMAILADDRESS = @EMAILADDRESS;

        --Bug 268367 The process options say to not mark addresses as primary, but there are cases
        -- where the address should be marked as primary anyway.
        if @MARKASPRIMARY = 0
        begin
            if @ID is null
            begin
                --When adding an address make it primary if no other addresses exist, even if the process options said not to
                if not exists (select 1 from dbo.EMAILADDRESS where EMAILADDRESS.CONSTITUENTID = @CONSTITUENTID)
                begin
                    set @MARKASPRIMARY = 1;
                end
            end
            else
            begin
                --When updating an address keep its primary status, even if the process options said not to
                select
                    @MARKASPRIMARY = EMAILADDRESS.ISPRIMARY
                from
                    dbo.EMAILADDRESS
                where
                    EMAILADDRESS.ID = @ID;
            end
        end

        --If they have not opt's out then add or update the email address
        if @OPTOUT = 0
        begin
            --If the email doesn't exist then add it
            if @ID is null
                exec dbo.USP_DATAFORMTEMPLATE_ADD_EMAILADDRESS @ID = @ID output, @CHANGEAGENTID = @CHANGEAGENTID, @CONSTITUENTID = @CONSTITUENTID, @EMAILADDRESSTYPECODEID = @EMAILADDRESSTYPECODEID, @EMAILADDRESS = @EMAILADDRESS, @PRIMARY = @MARKASPRIMARY, @UPDATEMATCHINGSPOUSEEMAILADDRESS = 0, @UPDATEMATCHINGHOUSEHOLDEMAILADDRESS = 0, @DONOTEMAIL = 0, @INFOSOURCECODEID = @INFOSOURCECODEID, @INFOSOURCECOMMENTS = @INFOSOURCECOMMENTS
            else
            begin
                --If there is a matching email with no other data update the record
                if exists(select ID from dbo.EMAILADDRESS where ID = @ID and EMAILADDRESSTYPECODEID is null and INFOSOURCECODEID is null and len(INFOSOURCECOMMENTS) > 0)
                    exec dbo.USP_DATAFORMTEMPLATE_EDIT_EMAILADDRESS_3 @ID, @CHANGEAGENTID, @EMAILADDRESSTYPECODEID, @EMAILADDRESS, @MARKASPRIMARY, 0, 0, 0, @INFOSOURCECODEID, @INFOSOURCECOMMENTS;
                else
                    set @ID = newID();
            end
        end
        else
        begin
            --If they have opt'd out then mark it do not email and update the info source
            if @ID is not null
            begin
                update dbo.EMAILADDRESS
                set
                    DONOTEMAIL = 1,
                    INFOSOURCECODEID = @INFOSOURCECODEID,
                    INFOSOURCECOMMENTS = @INFOSOURCECOMMENTS,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                where
                    ID = @ID;
            end
            else
            begin
                set @ID = newID();
            end
        end
    end try
    begin catch
        exec.dbo.USP_RAISE_ERROR;
        return 1;
    end catch

    return 0;