spGetConditionListRecipients

CREATE procedure dbo.spGetConditionListRecipients
(
    @ConditionListID int
)
as
begin

    declare @ListOrigin INT
    declare @UserImportID INT

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

    if @ListOrigin = 0
    begin
        if @UserImportID > 0
        begin
            insert into    #Recipient_List    
            select R.ID, @ConditionListID
            from #Recipient R
            join ( select CU.ID, CU.Email from
                    (select ClientUsersID
                    from [dbo].[UserImportMasterList] uiml WHERE UserImportID = @UserImportID) UIML
                    join [dbo].[clientusers] CU
                    on CU.ID = UIML.ClientUsersID
                    where CU.[Deleted] = 0 and CU.[active]=1) [USER]
            on R.ClientUserID = [USER].ID or (R.ClientUserID is null and R.EmailAddress = [USER].Email collate database_default)
        end
        else
        begin
            -- This is a NetCom list that isnt tied to an import, so it represents all Client Users

            insert into    #Recipient_List
            select R.ID, @ConditionListID
            from #Recipient R
            join (select Email, ID from [dbo].[CLIENTUSERS]    where [Deleted] = 0 and [active]=1) [USER]
            on R.ClientUserID = [USER].ID or (R.ClientUserID is null and R.EmailAddress = [USER].Email collate database_default)
        end
    end
    else if @ListOrigin = 1
    begin
        insert into #Recipient_List
        select R.ID, @ConditionListID
        from #Recipient R
        join (select EmailAddress from EmailListUploadMasterList where EmailListID = @ConditionListID) ELUML
        on R.EmailAddress = ELUML.EmailAddress collate database_default            
    end
    Else IF @ListOrigin = 2 BEGIN
        --If a recipients BOS People ID is in the list, then its a match.            

        INSERT INTO #Recipient_List
        SELECT r.ID, @ConditionListID
        FROM #Recipient r
        JOIN (SELECT PeopleID FROM EmailList_People WHERE EmailListID = @ConditionListID) elp
        On r.PeopleID = elp.PeopleID
        UNION
        --If an #Recipient has a null BOS People ID and USER ID in the table, match on email address                

        SELECT r.ID, @ConditionListID
        FROM (SELECT EmailAddress, ID FROM #Recipient WHERE PeopleID Is Null and ClientUserID is null) r
        JOIN (Select EmailAddress FROM EmailList_People WHERE EmailListID = @ConditionListID) elp
        On r.EmailAddress = elp.EmailAddress collate database_default
        UNION
        --If an #Recipient has a null BOS People ID and an actual USER ID in the table, look up peopleid

        SELECT r.ID, @ConditionListID
        FROM (select BOSU.BackofficePeopleID as PeopleID, RE.ID from #Recipient RE inner join dbo.BackOfficeSystemUsers BOSU on BOSU.ClientUsersID = RE.CLIENTUSERID where RE.PeopleID Is Null)r
        JOIN (SELECT PeopleID FROM EmailList_People WHERE EmailListID = @ConditionListID) elp
        On r.PeopleID = elp.PeopleID                
        UNION
        -- If an #Recipient has a BOS People IDbut its for a different BOS,

        -- match on email address.  This is to handle users from RE to EE.

        SELECT recipients.RecipientID, @ConditionListID
        FROM    (    SELECT r.ID as RecipientID, r.EmailAddress, bosp.BackOfficeSystemID AS BOSID
                    FROM (SELECT ID, PeopleID, EmailAddress FROM #Recipient PeopleID) r
                    JOIN BackOfficeSystemPeople bosp
                    ON   r.PeopleID = bosp.ID) recipients
        JOIN     (    SELECT elp.EmailAddress, bosp.BackOfficeSystemID AS BOSID
                    FROM (SELECT PeopleID, EmailAddress FROM EmailList_People WHERE EmailListID = @ConditionListID)elp
                    JOIN BackOfficeSystemPeople bosp
                    ON   elp.PeopleID = bosp.ID)  list
        ON recipients.EmailAddress = list.EmailAddress collate database_default AND recipients.BOSID <> list.BOSID

    end
    else if @ListOrigin = 3
    begin
        insert into #Recipient_List
        select r.ID, @ConditionListID
        from #Recipient r
        join (select EmailAddress from EmailList_UserDefined where EmailListID = @ConditionListID) elud
        on r.EmailAddress = elud.EmailAddress collate database_default
    end    
end