USP_BBNC_COMMITPROFILEUPDATEPHONENUMBERS

CREATE procedure dbo.USP_BBNC_COMMITPROFILEUPDATEPHONENUMBERS
(
    @CONSTITUENTID uniqueidentifier = null,                            
    @PHONETYPECODEID uniqueidentifier = null,
    @NUMBER nvarchar(100) = '',
    @PHONEID uniqueidentifier = null,
    @ISPRIMARY bit = null,
    @ADDNEWPHONE bit = null,
    @PREVIOUSPHONETYPECODEID uniqueidentifier = null,
    @CHANGEAGENTID uniqueidentifier = null,
    @CHANGEDATE datetime = null,
    @STARTDATE date = null,
    @ENDDATE date = null,
    @DONOTCALL bit = null
) as
    set nocount on;

    declare @SEQUENCE int;
    declare @DUPLICATEPHONEID uniqueidentifier;
    declare @PRIMARYPHONEID uniqueidentifier;

    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();

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

    begin try
        if @ISPRIMARY = 1
            begin
                select 
                    @PRIMARYPHONEID = [ID]
                from 
                    dbo.PHONE
                where
                    [CONSTITUENTID] = @CONSTITUENTID
                    and [ISPRIMARY] = 1;

                if (@PRIMARYPHONEID is not null and @PRIMARYPHONEID <> coalesce(@PHONEID, '00000000-0000-0000-0000-000000000000')) or
                   (@PRIMARYPHONEID is not null and @PRIMARYPHONEID = @PHONEID and @ADDNEWPHONE = 1)
                    begin
                        update dbo.PHONE set
                            [ISPRIMARY] = 0
                        where
                            [ID] = @PRIMARYPHONEID;
                    end            
            end

        if @ADDNEWPHONE = 0
            begin
                if @PHONEID is not null
                    begin
                        select
                            @DUPLICATEPHONEID = [ID]
                        from
                            dbo.PHONE
                        where
                            [CONSTITUENTID] = @CONSTITUENTID
                            and [PHONETYPECODEID] = @PHONETYPECODEID
                            and [NUMBER] = @NUMBER;

                        -- The type and number for constituent phone must be unique.

                        if @DUPLICATEPHONEID is not null and @DUPLICATEPHONEID <> @PHONEID
                            exec dbo.USP_PHONE_DELETEBYID_WITHCHANGEAGENTID @PHONEID, @CHANGEAGENTID;

                        if len(@NUMBER) > 0
                            begin
                                update dbo.PHONE set
                                    [PHONETYPECODEID] = @PHONETYPECODEID,
                                    [NUMBER] = @NUMBER,
                                    [ISPRIMARY] = @ISPRIMARY,
                                    [INFOSOURCECODEID] = @INFOSOURCECODEID,
                                    [CHANGEDBYID] = @CHANGEAGENTID,
                                    [DATECHANGED] = @CHANGEDATE,
                                    [STARTDATE] = @STARTDATE,
                                    [ENDDATE] = @ENDDATE,
                                    [DONOTCALL] = @DONOTCALL
                                where
                                    [ID] = @PHONEID
                                    and
                                    (
                                        @DUPLICATEPHONEID is null
                                        or
                                        [ID] = @DUPLICATEPHONEID
                                    );
                            end
                        else
                            begin
                                --JamesWill 01/29/2008 CR292112-012408 Delete phone entries when BBNC sends us an empty number

                                exec dbo.USP_PHONE_DELETEBYID_WITHCHANGEAGENTID @PHONEID, @CHANGEAGENTID;
                            end
                    end
            end
        else    -- update phone

            begin
                if len(@NUMBER) > 0 --JamesWill 01/29/2008 CR292112-012408 Don't try to insert blank phone numbers

                    begin
                        select @SEQUENCE = max([SEQUENCE]) 
                        from dbo.PHONE
                        where [CONSTITUENTID] = @CONSTITUENTID;

                        if @SEQUENCE is null
                                set @SEQUENCE = 0;

                        -- The type and number for constituent phone must be unique.

                        if not exists
                        (
                            select
                                1
                            from
                                dbo.PHONE
                            where
                                [CONSTITUENTID] = @CONSTITUENTID
                                and [PHONETYPECODEID] is null
                                and @PHONETYPECODEID is null
                                and [NUMBER] = @NUMBER
                        )
                            begin
                                if @PREVIOUSPHONETYPECODEID is not null
                                    begin
                                        if @PHONEID is not null
                                            update dbo.PHONE set
                                                [PHONETYPECODEID] = @PREVIOUSPHONETYPECODEID,
                                                [DATECHANGED] = @CHANGEDATE,
                                                [CHANGEDBYID] = @CHANGEAGENTID
                                            where
                                                [ID] = @PHONEID                          
                                    end

                                insert into dbo.PHONE
                                (
                                    [ID],
                                    [CONSTITUENTID],
                                    [PHONETYPECODEID],
                                    [NUMBER],
                                    [ISPRIMARY],
                                    [SEQUENCE],
                                    [INFOSOURCECODEID],
                                    [ADDEDBYID],
                                    [CHANGEDBYID],
                                    [DATEADDED],
                                    [DATECHANGED],
                                    [STARTDATE],
                                    [ENDDATE],
                                    [DONOTCALL]
                                )
                                values
                                (
                                    newid(),
                                    @CONSTITUENTID,
                                    @PHONETYPECODEID,
                                    @NUMBER,
                                    @ISPRIMARY,
                                    @SEQUENCE + 1,
                                    @INFOSOURCECODEID,
                                    @CHANGEAGENTID,
                                    @CHANGEAGENTID,
                                    @CHANGEDATE,
                                    @CHANGEDATE,
                                    @STARTDATE,
                                    @ENDDATE,
                                    @DONOTCALL
                                );

                            end
                    end -- end len(@NUMBER)

            end -- end update

    end try
    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch

    return 0;