spBulkAddUpdate_Setting

CREATE procedure dbo.spBulkAddUpdate_Setting (@XML ntext, @CMSUSERID int = null)
as
begin
    DECLARE @iDoc int
                BEGIN TRANSACTION

                declare @d datetime
                declare @setting table([ID] int, 
                    [Name] nvarchar(128), 
                    [Value] nvarchar(1024) )

                set @d = getutcdate()
                EXEC sp_xml_preparedocument @iDoc OUTPUT, @XML
                INSERT INTO @setting(
                    [ID],
                    [Name], 
                    [Value]
                ) SELECT
                [ID], [Name], [Value]
                FROM OPENXML (@iDoc, '/Settings/Setting',3) 
                    WITH(
                        [ID] int,
                        [Name] nvarchar(128),
                        [Value] nvarchar(1024)
                    )

                EXEC sp_xml_removedocument @iDoc

                BEGIN TRY
                    EXEC dbo.USP_GET_KEY_ACCESS 
                    UPDATE @setting  set [Value] = EncryptByKey(Key_GUID('sym_BBInfinity'), [Value]) where [ID] in (7,8,36,37,55,56,77,78,125,115,85,86)
                    CLOSE symmetric key sym_BBInfinity;
                END TRY        
                BEGIN CATCH
                  EXEC dbo.USP_RAISE_ERROR;
                  CLOSE symmetric key sym_BBInfinity;
                  return 1;
                END CATCH


                declare @CHANGEAGENTID uniqueidentifier
                exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

                if @CMSUSERID <= 0
                    set @CMSUSERID = null

          --Remove ForceMFA from @setting. We do not want to save it in DB because it is read from extended properties

        DELETE FROM @setting where id = 174

                UPDATE SETTING
                SET [Name] = t.[Name], Value = t.Value, UpdateDate = getutcdate(),
                CHANGEDBYCMSUSERID = @CMSUSERID, CHANGEDBYID = @CHANGEAGENTID
                FROM @Setting t                
                INNER JOIN Setting s on s.[id]= t.[id] and isnull(t.value,'') <> isnull(s.value,'')
                WHERE t.[ID] not in (9,10,11,12,71,72,118)

                INSERT INTO SETTING(        
                    [ID],
                    [Name], 
                    [Value],
                    [CreateDate],
                    [UpdateDate],
                    [ADDEDBYCMSUSERID],
                    [CHANGEDBYCMSUSERID],
                    [ADDEDBYID],
                    [CHANGEDBYID]
                )
                SELECT
                    [ID],
                    [Name], 
                    [Value],
                    getutcdate(),
                    getutcdate(),
                    @CMSUSERID,
                    @CMSUSERID,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID
                FROM @Setting t
                WHERE t.[ID] not in (9,10,11,12,71,72,118)
                    AND NOT EXISTS (SELECT ID
                                    FROM Setting s
                                    WHERE s.id = t.id)



                ---- Handle BBPS credentials separately

                if exists(select * from @setting t where t.[ID] = 71) AND exists(select * from @setting t where t.[ID] = 72)
                begin                    
                    DECLARE @BBPS_USERNAME nvarchar(100),
                            @BBPS_PASSWORD nvarchar(100);

                    set @BBPS_USERNAME = (select top 1 [Value] from @setting where ID = 71);
                    set @BBPS_PASSWORD = (select top 1 [Value] from @setting where ID = 72);
                    EXEC [dbo].[USP_DATAFORMTEMPLATE_EDIT_BBPSLOGIN] @USERNAME = @BBPS_USERNAME, @PASSWORD = @BBPS_PASSWORD;
                end


                ---- Handle Services configuration separately

                if exists(select * from @setting t where t.[ID] = 9) AND exists(select * from @setting t where t.[ID] = 10) AND exists(select * from @setting t where t.[ID] = 11) AND exists(select * from @setting t where t.[ID] = 12)
                begin                    
                    DECLARE @BBSERVICE_USER nvarchar(2048),
                            @BBSERVICE_PASSWORD nvarchar(2048),
                            @BBSERVICE_HOST nvarchar(2048),
                            @BBSERVICE_USESECURE nvarchar(2048);

                    set @BBSERVICE_HOST = (select top 1 [Value] from @setting where ID = 9);
                    set @BBSERVICE_USER = (select top 1 [Value] from @setting where ID = 10);
                    set @BBSERVICE_PASSWORD = (select top 1 [Value] from @setting where ID = 11);                    
                    set @BBSERVICE_USESECURE = (select top 1 [Value] from @setting where ID = 12);

                    --Enable BBCS

                    declare @TRANSACTIONALPATH nvarchar(2048) = 'Transactional/EmailSubmission/blackbaud/services/EmailSubmissionService.svc'
                    declare @GENERALPURPOSEPATH nvarchar(2048) = 'GeneralPurpose/EmailSubmission/blackbaud/services/EmailSubmissionService.svc'
                    declare @STATUSPATH nvarchar(2048) = 'EmailStatus/blackbaud/services/EmailStatusService.svc' 
                    declare @BINDING nvarchar(256) = '0'
                    declare @USE_BBCS nvarchar(2048) = 'true'


                    EXEC [dbo].[USP_SAVE_EXTERNALSERVICECONFIGURATION] @USER = @BBSERVICE_USER, @PASS = @BBSERVICE_PASSWORD, @HOST = @BBSERVICE_HOST, @USESECURE = @BBSERVICE_USESECURE,
                            @TRANSACTIONALPATH  = @TRANSACTIONALPATH, @GENERALPURPOSEPATH = @GENERALPURPOSEPATH,@STATUSPATH = @STATUSPATH,@BINDING = @BINDING,@USESHAREDSERVICESCOMMSERVICE = @USE_BBCS;
                    EXEC [dbo].[USP_EMAIL_PROCESSINGSCHEDULE_ENABLE] @CHANGEAGENTID;
                end

                COMMIT TRANSACTION
end