spGetConditionalPermutations

CREATE procedure [dbo].[spGetConditionalPermutations] (
                @EmailJobID int,
                @ConditionData xml,
                @TargetListsData xml
        )
            AS
            BEGIN

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

                ---------------------------Step 1:----------------------------------------    

                -- First we populate table variables that represent a common starting ----

                -- point for both final email permutations, as well as preview stats -----

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

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


                    --These table variables mimic the real tables, but are used to abstract the data this runs against from the real table

                    --so it can be populated with temporary data that hasn't been saved to the tables

                    Declare @ConditionBlock Table (ID int)
                    Declare @ConditionBlockCondition Table (ID int, ConditionBlockID int, ConditionID int, Priority int)
                    Declare @ConditionBlockParentCondition Table (ConditionBlockID int, ParentConditionID int)
                    Declare @ConditionList Table (EmailListID int, ConditionBlockConditionID int)


                    CREATE Table #Recipient_List (RecipientID uniqueidentifier, EmailListID INT)                                    

                    IF @ConditionData Is Null
                    BEGIN
                        Declare @EmailID int
                        Set @EmailID = (Select EmailID From EmailJob WHERE ID = @EmailJobID)

                        INSERT INTO @ConditionBlock
                        Select ID
                        FROM ConditionBlock cb
                        INNER JOIN EmailConditionBlock ecb on ecb.ConditionBlockID = cb.id
                        WHERE ecb.EmailID = @EmailID

                        INSERT INTO @ConditionBlockCondition
                        Select cbc.ID, cbc.ConditionBlockID, cbc.ConditionID, cbc.Priority
                        FROM ConditionBlockCondition cbc
                        INNER JOIN ConditionBlock cb on cbc.ConditionBlockID = cb.ID
                        INNER JOIN EmailConditionBlock ecb on ecb.ConditionBlockID = cb.id
                        WHERE ecb.EmailID = @EmailID

                        INSERT INTO @ConditionList
                        Select cl.EmailListID, cl.ConditionBlockConditionID 
                        FROM ConditionList cl
                        INNER Join ConditionBlockCondition cbc on cbc.ID = cl.ConditionBlockConditionID
                        INNER JOIN ConditionBlock cb on cbc.ConditionBlockID = cb.ID
                        INNER JOIN EmailConditionBlock ecb on ecb.ConditionBlockID = cb.id
                        WHERE ecb.EmailID = @EmailID
                    End
                    ELSE
                    BEGIN                                            
                        INSERT @ConditionBlock
                        SELECT
                            cd.cb.value('(ID)[1]', 'int')
                        FROM @ConditionData.nodes('/ConditionalContent/ConditionBlock') cd(cb)

                        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)

                        INSERT @ConditionBlockParentCondition
                        SELECT
                            cd.cbpc.value('(ConditionBlockID)[1]', 'int'),
                            cd.cbpc.value('(ParentConditionID)[1]', 'int')
                        FROM @ConditionData.nodes('/ConditionalContent/ConditionBlockParentCondition') cd(cbpc)

                        INSERT @ConditionList
                        SELECT
                            cd.cl.value('(EmailListID)[1]', 'int'),
                            cd.cl.value('(ConditionBlockConditionID)[1]', 'int')
                        FROM @ConditionData.nodes('/ConditionalContent/ConditionList') cd(cl)

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

                    Declare ConditionListCursor CURSOR LOCAL FAST_FORWARD FOR
                    SELECT cl.EmailListID
                    FROM @ConditionList cl

                    Declare @ConditionListID int
                    Open ConditionListCursor
                    FETCH NEXT FROM ConditionListCursor
                    INTO @ConditionListID

                    WHILE @@FETCH_STATUS = 0
                    BEGIN
                        exec spGetConditionListRecipients @ConditionListID

                        FETCH NEXT FROM ConditionListCursor
                        INTO @ConditionListID
                    END

                    close ConditionListCursor
                    deallocate ConditionListCursor

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

                ---------------------------Step 2:----------------------------------------

                -- Now, select out each recipients highest priority in each block---------

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

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

                    CREATE INDEX IX_Recipient_List ON #Recipient_List(RecipientID , EmailListID )
                    CREATE INDEX IX_Recipient ON #Recipient(ID)        

                    Declare @blocksToProcess Table (ID INT)
                    Declare @processedBlocks Table (ID INT)
                    Declare @tmpBlocks Table (ID INT)

                    INSERT INTO @blocksToProcess
                    SELECT ID FROM @ConditionBlock

                    WHILE (SELECT COUNT(1) FROM @blocksToProcess) > 0
                    BEGIN

                        INSERT INTO #Recipient_Priorities
                        SELECT a.RecipientID, a.ConditionBlockID, IsNull(b.Priority,0)
                        FROM   (
                                    SELECT r.ID AS RecipientID, cb.ID AS ConditionBlockID
                                    FROM #Recipient r, (Select btp.ID
                                                        FROM @blocksToProcess btp
                                                        LEFT OUTER JOIN @ConditionBlockParentCondition cbpc
                                                        on btp.ID = cbpc.ConditionBlockID
                                                        WHERE cbpc.ParentConditionID IS Null) as cb
                                    UNION
                                    SELECT rp.RecipientID, btp.ID AS ConditionBlockID 
                                    FROM #Recipient_Priorities rp
                                    JOIN @ConditionBlockCondition cbc
                                    ON   rp.BlockID = cbc.ConditionBlockID AND rp.Priority = cbc.Priority
                                    Join @ConditionBlockParentCondition cbpc
                                    ON   cbpc.ParentConditionID = cbc.ConditionID
                                    JOIN @blocksToProcess btp
                                    on cbpc.ConditionBlockID = btp.ID
                                ) a
                        LEFT OUTER JOIN (            
                                            Select rl.RecipientID, cbc.ConditionBlockID, Min(cbc.Priority) as Priority
                                            FROM #Recipient_List rl
                                            JOIN @ConditionList cl
                                            on cl.EmailListID = rl.EmailListID
                                            JOIN @ConditionBlockCondition cbc
                                            ON   cbc.ID = cl.ConditionBlockConditionID
                                            Group By rl.RecipientID, cbc.ConditionBlockID
                                        ) b
                        ON a.RecipientID = b.RecipientID AND a.ConditionBlockID = b.ConditionBlockID

                        -- Now delete the newly handled blocks

                        -- Handled blocks are ones that have been inserted into the table, or whose immediate

                        -- parent has already been handled.  This will ensure we handle blocks who noone falls in                

                        INSERT INTO @tmpBlocks
                        SELECT btp.ID
                        FROM @blocksToProcess btp
                        INNER JOIN @ConditionBlockParentCondition cbpc
                        on cbpc.ConditionBlockID = btp.ID
                        INNER JOIN @ConditionBlockCondition cbc
                        ON cbpc.ParentConditionID = cbc.ConditionID
                        INNER JOIN @processedBlocks pb
                        ON pb.ID = cbc.ConditionBlockID


                        --Also count any block that has no parents as handled, even if noone hit a condition in it

                        INSERT INTO @tmpBlocks
                        Select btp.ID
                        FROM @blocksToProcess btp
                        LEFT OUTER JOIN @ConditionBlockParentCondition cbpc
                        on btp.ID = cbpc.ConditionBlockID
                        WHERE cbpc.ParentConditionID IS Null

                        INSERT INTO @tmpBlocks
                        SELECT btp.ID
                        FROM @blocksToProcess btp
                        JOIN #Recipient_Priorities rp
                        on rp.BlockID = btp.ID                        

                        INSERT INTO @processedBlocks
                        SELECT * FROM @tmpBlocks

                        DELETE @blocksToProcess
                        FROM @blocksToProcess btp
                        INNER JOIN @tmpBlocks tb
                        on btp.ID = tb.ID                        

                        DELETE @tmpBlocks

                    END


                    DROP TABLE #Recipient_List
                    DROP TABLE #Recipient

            END