USP_DATAFORMTEMPLATE_ADD_BATCHPHONEFINDERBATCHCOMMIT

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_BATCHPHONEFINDERBATCHCOMMIT
(
    @ID uniqueidentifier = null output,
    @VALIDATEONLY bit = 0,
    @CHANGEAGENTID uniqueidentifier,
    @PHONEFINDERID uniqueidentifier = null,
    @CONSTITUENTID uniqueidentifier = null,
    @ADDRESSID uniqueidentifier = null,
    @PHONENUMBER nvarchar(20) = '',
    @PHONEMATCHTYPE nvarchar(1) = null,
    @MATCHCOMPOSITESCORE nvarchar(5) = null,
    @PHONESTATUS nvarchar(1) = null,
    @DONOTCALLSTATUS nvarchar(1) = null
)
as begin
set nocount on;

    /* Perform some validation: */
    if not exists(select 1 from dbo.CONSTITUENT where CONSTITUENT.[ID] = @CONSTITUENTID)
        raiserror('ERR_INVALID_CONSTITUENT', 13, 1);

    declare @CURRENTDATE datetime = getdate();

    if @ID is null
        set @ID = newid();

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

    begin try
        -- A = 10-digit input phone matches appended phone
        -- C = 10-digit phone number corrected by appended phone
        -- D = Input phone was invalid; 10-digit corrected phone provided
        -- F = 10-digit input phone did not match appended 10-digit phone; 10-digit phone provided
        --if len(@PHONENUMBER) = 10 -- and @PHONEMATCHTYPE in (N'A', N'C', N'D', N'F')
        --begin
            /* Get PhoneFinder options: */
            declare @INDIVIDUALPHONETYPECODEID uniqueidentifier;
            declare @INDIVIDUALMARKASPRIMARY bit;
            declare @ORGANIZATIONPHONETYPECODEID uniqueidentifier;
            declare @ORGANIZATIONMARKASPRIMARY bit;
            declare @INFOSOURCECODEID uniqueidentifier;

            select
                @INDIVIDUALPHONETYPECODEID = PHONEFINDER.[INDIVIDUALPHONETYPECODEID],
                @INDIVIDUALMARKASPRIMARY = PHONEFINDER.[INDIVIDUALMARKASPRIMARY],
                @ORGANIZATIONPHONETYPECODEID = PHONEFINDER.[ORGANIZATIONPHONETYPECODEID],
                @ORGANIZATIONMARKASPRIMARY = PHONEFINDER.[ORGANIZATIONMARKASPRIMARY],
                @INFOSOURCECODEID = PHONEFINDER.[INFOSOURCECODEID]
            from
                dbo.PHONEFINDER
            where
                PHONEFINDER.[ID] = @PHONEFINDERID;

            /* Determine if this record is an individual or organization and use the correct settings: */
            declare @MARKASPRIMARY bit;
            declare @PHONETYPECODEID uniqueidentifier

            select
                @MARKASPRIMARY = 
                case 
                    when CONSTITUENT.[ISORGANIZATION] = 1 then @ORGANIZATIONMARKASPRIMARY 
                    else @INDIVIDUALMARKASPRIMARY end,

                @PHONETYPECODEID = 
                case
                    when CONSTITUENT.[ISORGANIZATION] = 1 then @ORGANIZATIONPHONETYPECODEID
                    else @INDIVIDUALPHONETYPECODEID end
            from
                dbo.CONSTITUENT
            where
                CONSTITUENT.[ID] = @CONSTITUENTID;

            declare @UNFORMATTEDNUMBER nvarchar(100) = dbo.UFN_PHONE_REMOVEFORMATTING(@PHONENUMBER);

            /* Mark existing phone records as not primary: */
            if @MARKASPRIMARY = 1
                update dbo.PHONE
                    set
                        PHONE.[ISPRIMARY] = 0,
                        PHONE.[DATECHANGED] = @CURRENTDATE,
                        PHONE.[CHANGEDBYID] = @CHANGEAGENTID
                    from dbo.PHONE
                    where
                        PHONE.[CONSTITUENTID] = @CONSTITUENTID and
                        PHONE.[NUMBERNOFORMAT] <> @UNFORMATTEDNUMBER and
                        ISPRIMARY = 1

            /* Determine DNC status: */
            declare @DONOTCALL bit;

            -- Blank = Not on DMA/FTC DNC list and no state DNC list
            -- D = Not on DMA/FTC DNC list and state DNC list not available
            -- H = Not on DMA/FTC DNC list and phone number is not on state DNC list
            select @DONOTCALL = 
                case when @DONOTCALLSTATUS in (N'', N'H', N'D') or @DONOTCALLSTATUS is null then 0 else 1 end;

            /* Check to see if existing phone numbers exist and update them: */
            declare @COUNT int = 0;
            select @COUNT = count(PHONE.[ID])
            from dbo.PHONE
            where PHONE.[CONSTITUENTID] = @CONSTITUENTID and PHONE.[NUMBERNOFORMAT] = @UNFORMATTEDNUMBER;

            if @COUNT > 0
            begin
                --When updating a phone number keep its primary status, even if the process options said not to
                select top 1
                    @ID = PHONE.ID,
                    @MARKASPRIMARY =
                        case
                            when @MARKASPRIMARY = 0 then PHONE.ISPRIMARY
                            else @MARKASPRIMARY
                        end
                from dbo.PHONE
                where
                    PHONE.[CONSTITUENTID] = @CONSTITUENTID and
                    PHONE.[NUMBERNOFORMAT] = @UNFORMATTEDNUMBER
                order by PHONE.ISPRIMARY desc;

                update dbo.PHONE
                    set
                        [CHANGEDBYID] = @CHANGEAGENTID,
                        [DATECHANGED] = @CURRENTDATE,
                        [INFOSOURCECODEID] = @INFOSOURCECODEID,
                        [ISPRIMARY] = case when @COUNT = 1 then @MARKASPRIMARY else [ISPRIMARY] end,
                        [DONOTCALL] = @DONOTCALL,
                        [NUMBER] = @PHONENUMBER
                    where
                        [CONSTITUENTID] = @CONSTITUENTID and
                        [NUMBERNOFORMAT] = @UNFORMATTEDNUMBER;
            end
            else
            begin
                --When adding a phone number make it primary if no other numbers exist, even if the process options said not to
                if not exists (select 1 from dbo.PHONE where PHONE.CONSTITUENTID = @CONSTITUENTID)
                    set @MARKASPRIMARY = 1;

                /* Add new phone record: */
                exec dbo.USP_DATAFORMTEMPLATE_ADD_PHONE
                    @ID,
                    @CHANGEAGENTID,
                    @CONSTITUENTID,
                    @PHONETYPECODEID,
                    @PHONENUMBER,
                    @MARKASPRIMARY,
                    @DONOTCALL,
                    null,
                    null,
                    N'',
                    N'',
                    @INFOSOURCECODEID,
                    N'', -- Info source comments
                    null,
                    null,
                    null,
                    0;
            end
        --end
    end try
    begin catch
        exec.dbo.USP_RAISE_ERROR;
        return 1;
    end catch

    return 0;
end