USP_CONDITIONALCONTENTRECIPIENT_BULK_ADD

The save procedure used by the add dataform template "ConditionalContentRecipient Add Data Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@EMAILJOBID	int	IN	Input parameter indicating the context ID for the record being added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@EMAILLISTID	int	IN	Email List ID

Definition

Copy



CREATE procedure dbo.USP_CONDITIONALCONTENTRECIPIENT_BULK_ADD
( 
    @ID uniqueidentifier = null output,
    @EMAILJOBID int,
    @CHANGEAGENTID uniqueidentifier = null,
    @EMAILLISTID int = ''    
)
as

set nocount on;

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

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

declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()

begin try

    declare @ListOrigin int
    declare @DataSourceID int
    declare @UserImportID int

    select @DataSourceID = DataSourceID, @ListOrigin = Origin, @UserImportID = UserImportID
    from dbo.EmailList
    where ID = @EMAILLISTID

    if @ListOrigin = 0
    begin
        if @UserImportID > 0
        begin
            insert into dbo.CONDITIONALCONTENTRECIPIENT (EMAILJOBID, USERID, EMAILADDRESS, EMAILLISTID)
            select @EMAILJOBID, cu.ID, cu.Email, @EMAILLISTID 
            from
                (   select ClientUsersID
                    from [dbo].[UserImportMasterList] uiml
                    where UserImportID = @UserImportID
                  ) uiml                
            inner join [dbo].[clientusers] cu ON cu.ID = uiml.ClientUsersID
            where cu.[Deleted] = 0 AND cu.[active]=1 and Len(cu.Email) > 0
        end
        else 
        begin
            if @DataSourceID = 100
            begin
                --insert anonymous subscribers first

                insert into dbo.CONDITIONALCONTENTRECIPIENT (EMAILJOBID, BOSPID, USERID, EMAILLIST_SUBSCRIPTIONID, EMAILADDRESS, EMAILLISTID)
                select @EMAILJOBID, BACKOFFICESYSTEMPEOPLEID, USERID, ID, EMAILADDRESS, @EMAILLISTID
                from dbo.EmailList_Subscription
                where EMAILLISTID = @EMAILLISTID and HASOPTED=1 and USERID is null  and Len(EmailAddress) > 0

                --insert users now

                insert into dbo.CONDITIONALCONTENTRECIPIENT (EMAILJOBID, BOSPID, USERID, EMAILLIST_SUBSCRIPTIONID, EMAILADDRESS, EMAILLISTID)
                select @EMAILJOBID, case EL.BACKOFFICESYSTEMPEOPLEID when 0 then null else EL.BACKOFFICESYSTEMPEOPLEID end, CU.ID, EL.ID, CU.EMAIL, @EMAILLISTID from dbo.EmailList_Subscription EL
                inner join dbo.CLIENTUSERS CU on CU.ID = EL.USERID 
                where EMAILLISTID = @EMAILLISTID and HASOPTED=1 and Len(CU.EMAIL) > 0
            end
            else
            begin
                insert into dbo.CONDITIONALCONTENTRECIPIENT (EMAILJOBID, USERID, EMAILADDRESS, EMAILLISTID)
                select @EMAILJOBID, ID, Email, @EMAILLISTID from dbo.ClientUsers WHERE [Deleted] = 0 AND [active]=1 and LEN([Email]) > 0
            end
        end
    end
    else if @ListOrigin = 1
    begin
        insert into dbo.CONDITIONALCONTENTRECIPIENT (EMAILJOBID, EMAILLISTUPLOADMASTERLISTID, EMAILADDRESS, EMAILLISTID)
        select @EMAILJOBID, BB_XLISTRECORDID, EmailAddress, @EMAILLISTID  FROM EmailListUploadMasterList WHERE EmailListID = @EMAILLISTID  and Len(EmailAddress) > 0
    end
    else if @ListOrigin = 2
    begin
        insert into dbo.CONDITIONALCONTENTRECIPIENT (EMAILJOBID, BOSPID, USERID, EMAILADDRESS, EMAILLISTID)
        select @EMAILJOBID, elp.PeopleID, BOSU.ClientUsersID, elp.EmailAddress, @EMAILLISTID  FROM EmailList_People elp
        left outer join dbo.BackOfficeSystemUsers BOSU on BOSU.BackofficePeopleID = elp.ID    
        where EmailListID = @EMAILLISTID and Len(EmailAddress) > 0
    end 
    else if @ListOrigin = 3
    begin
        insert into dbo.CONDITIONALCONTENTRECIPIENT (EMAILJOBID, EMAILLIST_USERDEFINEDID, EMAILADDRESS, EMAILLISTID)
        select @EMAILJOBID, ID, EmailAddress, @EMAILLISTID FROM EmailList_UserDefined WHERE EmailListID = @EMAILLISTID and Len(EmailAddress) > 0
    end  
end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0