spGetEmailViewAsPermutations

CREATE procedure dbo.spGetEmailViewAsPermutations
(
    @StatKey nvarchar(1000),
    @ConditionData xml,
    @TargetLists xml
)
as
begin

    ----------- **** IMPORTANT **** --------------------

    ---- This SP contains a Temp table.  The SPGen Utility

    ---- cannot handle Temp tables due to a limitation in DataAdapter.FillSchema.

    ---- If you make changes to this SP and need to regen the file, you will have to

    ---- replace the temp table with a table variable. Then you can successfully regen this file.

    ---- Afterwards, you can switch back to a temp table.


    --Insert this new ke

    EXEC spAddUpdate_ConditionRecipientStatistics @StatKey, Null, 0

    Declare @ConditionBlockCondition Table (ID int, ConditionBlockID int, ConditionID int, Priority int)            
    INSERT @ConditionBlockCondition
    SELECT
        cd.cbc.value('(ID)[1]', 'int'),
        cd.cbc.value('(ConditionBlockID)[1]', 'int'),
        cd.cbc.value('(ConditionID)[1]', 'int'),
        cd.cbc.value('(Priority)[1]', 'int')
    FROM @ConditionData.nodes('/ConditionalContent/ConditionBlockCondition') cd(cbc)

    Declare @TargetList Table (EmailListID int)
    INSERT @TargetList
    SELECT
        tld.l.value('@ID', 'int')
    FROM @TargetLists.nodes('/TargetLists/List') tld(l)

    ----------------------------------

    -- Create a master recipient list-

    ----------------------------------

    CREATE TABLE #Recipient  (ID uniqueidentifier, EmailAddress nvarchar(256) COLLATE database_default  not null , PeopleID int null, ClientUserID int null)

    -- Insert people from query based lists

    INSERT INTO    #Recipient    
    SELECT NEWID(), elp.EmailAddress, elp.PeopleID, BOSU.ClientUsersID
    FROM [dbo].[EmailList_People] elp
    JOIN @TargetList tl
    ON tl.EmailListID = elp.EmailListID
    left outer join dbo.BackOfficeSystemUsers BOSU on BOSU.BackofficePeopleID = elp.ID                            
    WHERE Len(elp.EmailAddress) > 0
    GROUP BY elp.EmailAddress, elp.PeopleID, BOSU.ClientUsersID

    -- Insert people from Uploaded (Import) lists

    INSERT INTO    #Recipient    
    SELECT NEWID(), eluml.EmailAddress, NULL, NULL
    FROM [dbo].[EmailListUploadMasterList] eluml
    JOIN @TargetList tl
    ON tl.EmailListID = eluml.EmailListID    
    WHERE Len(eluml.EmailAddress) > 0

    -- Insert people from user defined lists

    INSERT INTO    #Recipient    
    SELECT NEWID(), elud.EmailAddress, NULL, NULL
    FROM [dbo].[EmailList_UserDefined] elud
    JOIN @TargetList tl
    ON tl.EmailListID = elud.EmailListID  
    WHERE Len(elud.EmailAddress) > 0

    -- Insert people from anonymous subscription lists

    INSERT INTO    #Recipient    
    SELECT NEWID(), els.EmailAddress, NULL, NULL
    FROM [dbo].[EmailList_Subscription] els
    JOIN @TargetList tl
    ON tl.EmailListID = els.EmailListID    
  WHERE UserID IS NULL AND HasOpted = 1 AND Len(els.EmailAddress) > 0

    -- Insert people from appeal lists

    declare @APPEALLISTID int = null
    select @APPEALLISTID = EL.ID
    from @TARGETLIST TL
    inner join dbo.EMAILLIST EL on EL.ID = TL.EMAILLISTID
    where EL.Datasourceid = 400

    if @APPEALLISTID is not null
    begin
        create table #APPEALEMAILLISTMEMBER (CONSTITUENTID uniqueidentifier, ANONYMOUSEMAILSUBSCRIBERID uniqueidentifier, EMAILADDRESS nvarchar(100) COLLATE database_default)

        exec dbo.USP_EMAILLIST_PROCESSAPPEALLIST @APPEALLISTID

        merge #Recipient as T
        using (select distinct EMAILADDRESS from #APPEALEMAILLISTMEMBER) as M
        on T.EMAILADDRESS = M.EMAILADDRESS collate database_default
        when not matched by target
        then insert (ID, EMAILADDRESS) values (NEWID(), EMAILADDRESS);

        drop table #APPEALEMAILLISTMEMBER
    end    

    -- Insert constituents from subscription lists

    INSERT INTO    #Recipient    
    SELECT NEWID(), cu.Email,  case els.BackofficeSystemPeopleID when 0 then null else els.BackofficeSystemPeopleID end, UserID
    FROM [dbo].[EmailList_Subscription] els
    JOIN @TargetList tl
    ON tl.EmailListID = els.EmailListID
    JOIN [dbo].[ClientUsers] cu
    ON cu.ID = els.UserID
    WHERE els.UserID IS NOT NULL AND els.HasOpted = 1 AND Len(cu.Email) > 0

    -- Insert people from Imported Users lists

    INSERT INTO    #Recipient    
    SELECT NEWID(), cu.Email, NULL, cu.ID
    FROM [dbo].[EmailList] el
    JOIN @TargetList tl
    ON tl.EmailListID = el.ID
    JOIN [dbo].[UserImportMasterList] uiml
    ON el.UserImportID = uiml.UserImportID
    JOIN [dbo].[clientusers] cu
    ON cu.ID = uiml.ClientUsersID
    WHERE cu.[Deleted] = 0 AND cu.[active]=1 AND len(cu.[Email]) > 0

    -- Insert all Netcom users if a NetCom list is selected

    DECLARE @netComListID int
    SET @netComListID = 0
    SET @netComListID = (Select top 1 el.id
                        FROM [dbo].[EmailList] el
                        JOIN @TargetList tl
                        ON tl.EmailListID = el.ID
                        WHERE el.DataSourceID = 101 AND el.UserImportID = 0)

    IF @netComListiD > 0 BEGIN
        INSERT INTO    #Recipient    
        SELECT NEWID(), Email, NULL, ID
        FROM [dbo].[clientusers]
        WHERE [Deleted] = 0 AND [active]=1 AND len([Email]) > 0
    END

    Create INDEX IX_Recipients_Address ON #Recipient(EmailAddress, ID)
    Create INDEX IX_Recipients_People ON #Recipient(PeopleID, ID)
    Create INDEX IX_Recipients_User ON #Recipient(ClientUserID, ID)

    Declare @messageCount int
    SELECT @messageCount = COUNT(1)
    FROM #Recipient r

    Create Table #Recipient_Priorities (RecipientID uniqueidentifier, BlockID INT, Priority INT)
    EXEC spGetConditionalPermutations null,@ConditionData,@TargetLists

    Create Index IX_Recipient_Priorities on #Recipient_Priorities (RecipientID, BlockID, Priority)

    DECLARE @counts varchar(8000)
    SET @counts = ''

    Declare ConditionCursor CURSOR LOCAL FAST_FORWARD FOR
  SELECT cbc.ID, bpc.RecipientCount
    FROM @ConditionBlockCondition cbc
    JOIN    (SELECT BlockID, Priority, Count(1) as RecipientCount
            FROM #Recipient_Priorities
            GROUP By BlockID, Priority) bpc
    ON  bpc.BlockID = cbc.ConditionBlockID
    AND bpc.Priority = cbc.Priority

    DECLARE @conditionID int
    DECLARE @recipCount int
    Open ConditionCursor
    FETCH NEXT FROM ConditionCursor
    INTO @conditionID, @recipCount

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @counts = @counts + Convert(varchar(5), @conditionID) + ':' + Convert(varchar(10), @recipCount) + ';'

        FETCH NEXT FROM ConditionCursor
        INTO @conditionID, @recipCount
    END

    close ConditionCursor
    deallocate ConditionCursor

    SET @counts = @counts + Convert(varchar(10),@messageCount)                

    EXEC spAddUpdate_ConditionRecipientStatistics @StatKey, @counts, 1

    drop table #Recipient_Priorities

end