spAddUpdate_EmailList

      CREATE  PROCEDURE [dbo].[spAddUpdate_EmailList]
(
    @UserID          int,
    @ClientsID        int,
    @PKID            int output,
    @Name             nvarchar(256),
    @Description    nvarchar(256),
    @Subscription    bit,
    @QueryXML        ntext,
    @Guid             uniqueidentifier output,
    @Type            int,
    @DataSourceID    int,
    @Options ntext,
    @AcquiredStatus int,
    @AcquiredDate datetime,
    @AcquiredMsg ntext,
    @AcquiredStartDate datetime,
    @AcquiredRecordCount int,
    @EmailDisplayNameFieldName nvarchar(128),
    @EmailAddressFieldName nvarchar(128),
    @UniqueKeyFieldName nvarchar(128),
    @UploadFileName nvarchar(256),
    @UploadFileSize int,
    @UploadedDataSourceID int, 
    @ListDataSchema ntext,
    @FileContents image,
    @Origin tinyint,
    @UserImportID int,
    @BBDMListID nvarchar(255),
    @SystemList bit,
    @CLIENTSITESID int = 0
)
AS
BEGIN
    set nocount on

        DECLARE @PopulateEmailField bit
        declare @UploadOrigin int
        SET @PopulateEmailField = 0

        if @CLIENTSITESID <= 0
            select @CLIENTSITESID = Value from dbo.Setting where ID = 1

        --For Uploaded Lists only
        --We need to update the masterlist with the email address of each record in the list,
        --but we only want to do this if this is an existing list and the EmailAddressFieldName has been changed
        --This process will take place in [spAddUploadedListToMasterList] proc for a new list
        declare @OldEmailColumnName nvarchar(200) 
        IF (@DataSourceID = 300 or @DataSourceID = 301) AND @PKID > 0 AND @EmailAddressFieldName <> (SELECT EmailAddressFieldName FROM dbo.EmailList WHERE [ID] = @PKID)
        begin
            SET @PopulateEmailField = 1
            set @OldEmailColumnName = (SELECT EmailAddressFieldName FROM dbo.EmailList WHERE [ID] = @PKID)
        end


        declare @NewRecord bit
        declare @NewDataSourceID int
        if (@PKID<=0) begin

            set @NewRecord = 1

            INSERT INTO EmailList
            (
                ClientsID,
                [Name],
                [Description],
                Guid,
                OwnerID,
                Subscription,
                Type,
                DatasourceID,
                Options,
                AcquiredStatus,
                AcquiredDate,
                AcquiredMsg,
                AcquiredStartDate,
                AcquiredRecordCount,
                EmailDisplayNameFieldName,
                EmailAddressFieldName,
                UniqueKeyFieldName,
                ListDataSchema,
                UploadedDataSourceID,
                Origin,
                UserImportID,
                BBDMListID,
                SystemList,
                CLIENTSITESID
            )

            VALUES
            (
                @ClientsID,
                @Name,
                @Description,
                NewID(),
                @UserID,
                @Subscription,
                @Type,
                @DatasourceID,
                @Options,
                @AcquiredStatus,
                @AcquiredDate,
                @AcquiredMsg,
                @AcquiredStartDate,
                @AcquiredRecordCount,
                @EmailDisplayNameFieldName,
                @EmailAddressFieldName,
                @UniqueKeyFieldName,
                @ListDataSchema,
                @UploadedDataSourceID,
                @Origin,
                @UserImportID,
                @BBDMListID,
                @SystemList,
                @CLIENTSITESID
            )

            select    @PKID = @@Identity

            select @Guid = Guid from dbo.EmailList where ID = @PKID

            exec spAuditThis @UserID, 1, @Guid, 12

            end

        ELSE
            begin

                set @NewRecord = 0

                UPDATE
                    EmailList

                SET
                    [Name] = @Name,
                    [Description] = @Description,
                    [Subscription] = @Subscription,
                    [Type] = @Type,
                    [DataSourceID] = @DataSourceID,
                    [Options] = @Options,
                    AcquiredStatus = @AcquiredStatus,
                    AcquiredDate = @AcquiredDate,
                    AcquiredMsg = @AcquiredMsg,
                    AcquiredStartDate = @AcquiredStartDate,
                    AcquiredRecordCount = @AcquiredRecordCount,
                    EmailDisplayNameFieldName = @EmailDisplayNameFieldName,
                    EmailAddressFieldName = @EmailAddressFieldName,
                    UniqueKeyFieldName = @UniqueKeyFieldName,
                    UploadedDataSourceID = @UploadedDataSourceID,
                    ListDataSchema = @ListDataSchema,
                    Origin = @Origin,
                    UserImportID=@UserImportID,
                    BBDMListID = @BBDMListID,
                    SystemList = @SystemList,
                    CLIENTSITESID = @CLIENTSITESID
                WHERE
                    Id    =     @PKID 

                if not exists
                (
                    select 1 from dbo.emaillist EL 
                    inner join dbo.sitefolders SF on SF.FolderID = EL.SITEFOLDERSID
                    where EL.ID = @PKID and sf.clientsitesid = @CLIENTSITESID
                )
                    update dbo.emaillist set SITEFOLDERSID = 0 where ID = @PKID;

                select @Guid = Guid from dbo.EmailList where ID = @PKID

                exec spAuditThis @UserID, 2, @Guid, 12


                --there may be a 1:1 linked datasource - update its name to keep it in sync
                update datasources 
                set [name] = @Name
                where ImportListID = @PKID


            end

        -- only hit filecontent cols if passing up a file        
        if (@UploadFileSize > 0) begin

            update EmailList 
                set FileContents = @FileContents,
                UploadFileSize = @UploadFileSize,
                UploadFileName = @UploadFileName
                where id=@PKID
        end



        -- adding new uploaded list - create datasource for it
        if (@NewRecord =1 and (@DataSourceID = 300)) begin


            if (@DataSourceID = 300) 
                -- uppoaded file
                set @UploadOrigin=1
            else
                -- API
                set @UploadOrigin=2

            -- generate next uploaded datasource id available
            select @NewDataSourceID=coalesce(max([DataSourceID])+1, 1001) 
                from dbo.DataSources 
                where [DataSourceID]>1000

            -- clone Datasource 300
            INSERT INTO [dbo].[DataSources]
                       ([DataSourceID]
                       ,[Name]
                       ,[TypeNamespace]
                       ,[TypeName]
                       ,[IsEmailListSource]
                       ,[BackOfficeSystemId]
                       ,[Available]
                       ,[SubscriberProviderForSystemID]
                       ,[ImportListID]
                       ,[UploadOrigin]
                       ,[DATAPROCESSOR]
                       ,[LISTPROCESSOR])
                 select
                       @NewDataSourceID,
                       @Name,
                       TypeNamespace,
                       TypeName,
                       0,
                       BackOfficeSystemId,
                       Available,
                       -1,
                       @PKID,
                       @UploadOrigin,
                       DATAPROCESSOR,
                       LISTPROCESSOR                       
                    from dbo.DataSources 
                    where DataSourceID = 300


            -- store datasourceid on emaillist rec
            update EmailList 
                set UploadedDataSourceID = @NewDataSourceID
                where id=@PKID

        end

        IF @PopulateEmailField = 1
        BEGIN
            DECLARE @UPLOADLISTTABLENAME nvarchar(128)
            DECLARE @SQL nvarchar(4000)

            SET @UPLOADLISTTABLENAME = dbo.fnUloadedListBuildTableName(@PKID);

            -- Rename the old EmailAddress column on the Xlist table. The dynamic sql below expects the new column name.
            declare @RenameTarget nvarchar(300) = 'dbo.[' + @UPLOADLISTTABLENAME + '].[' + @OldEmailColumnName + ']'
            exec sp_RENAME @RenameTarget, @EmailAddressFieldName , 'COLUMN'

            SET @SQL ='UPDATE dbo.EmailListUploadMasterList SET EmailAddress = xl.' + @EmailAddressFieldName + 
            ' FROM dbo.EmailListUploadMasterList ml inner join ' + @UPLOADLISTTABLENAME +
            ' xl on ml.GUID = xl.BB_XListRecordGuid WHERE ml.EmailListID = '  + cast(@PKID as nvarchar(10)) 

            exec(@SQL)
        END


        DECLARE @idoc int
        EXEC sp_xml_preparedocument @idoc OUTPUT, @QueryXML

        DELETE FROM dbo.EmailList_Query WHERE EmailListID = @PKID 

        INSERT EmailList_Query (EmailListID, QUERYID, QueryName, BBSystem) 
        SELECT @PKID, QueryID, QueryName, BBSystem 
        FROM OPENXML (@idoc, '/root/QueryList', 1)
        WITH (     QueryID int, 
            QueryName nvarchar(300),
            BBSystem int)

        EXEC sp_xml_removedocument @idoc
END