spConditionBlockConditionCombinationStats

CREATE PROCEDURE [dbo].[spConditionBlockConditionCombinationStats]
(
    @EmailID        int,
    @CBCCombination XML
)
AS
BEGIN
    -- Get the stats for all the email jobs related to this
    -- conditional content ConditionBlockCondition node path
    -- in the specified email.

    CREATE TABLE #EmailJobs
    (
        [ID] int NOT NULL
    )

    INSERT INTO #EmailJobs
        SELECT
            *
        FROM [dbo].[fnEmailJobsHavingConditionBlockConditionCombination](@EmailID, @CBCCombination)

    declare @EmailClickThroughs table
    (
        [EMAILJOBID] int NOT NULL,
        [CLICKTHROUGHS] int
    )

    insert into @EmailClickThroughs
    select
    EJR.[EmailJobID] As [EmailJobID],
    Count(s.[StatsID]) As [Clickthroughs]
    from #EmailJobs EJ
    inner join dbo.emailjob REALEJ on EJ.ID = REALEJ.ID
    inner join dbo.EmailJob_Recipient EJR on EJR.EmailJobID = EJ.ID    
    inner join dbo.Stats S on EJR.ID =  S.EmailJobRecipientID    
    inner join dbo.EmailLink EL on (EL.PID = S.PageID OR S.URL = EL.URL OR S.DocumentId = EL.DocumentID) and EL.EmailID = REALEJ.Emailid
    group by ejr.EmailJobID      

    SELECT
        Sum(ejs.[Sent])    AS [Sent],
        Sum(ejs.[Opened])  AS [Opened],
        Sum(ejs.[Bounced]) AS [Bounced],
        Sum(ISNULL(ejc.[Clickthroughs], 0))                   AS [Clickthroughs],
        Sum(ISNULL(ejds.[DonationTransactionsCount], 0))      AS [DonationTransactionsCount],
        Sum(ISNULL(ejes.[EventTransactionsCount], 0))         AS [EventTransactionsCount],
        Sum(ISNULL(ejms.[MembershipTransactionsCount], 0))    AS [MembershipTransactionsCount],
        Sum(ISNULL(ejps.[ProfileUpdateTransactionsCount], 0)) AS [ProfileUpdateTransactionsCount],
        Sum(ISNULL(ejss.[SignupTransactionsCount], 0))        AS [SignupTransactionsCount],
        Sum(ISNULL(ejvs.[VolunteerTransactionsCount], 0))     AS [VolunteerTransactionsCount]
    FROM
        #EmailJobs ej
    INNER JOIN [dbo].[vwEmailJobStats] ejs
        ON ej.[ID] = ejs.[EmailJobID]
    LEFT OUTER JOIN @EmailClickThroughs ejc
        ON ej.[ID] = ejc.[EmailJobID]
    LEFT OUTER JOIN [dbo].[vwEmailJobDonationStats] ejds
        ON ej.[ID] = ejds.[EmailJobID]
    LEFT OUTER JOIN [dbo].[vwEmailJobEventStats] ejes
        ON ej.[ID] = ejes.[EmailJobID]
    LEFT OUTER JOIN [dbo].[vwEmailJobMembershipStats] ejms
        ON ej.[ID] = ejms.[EmailJobID]
    LEFT OUTER JOIN [dbo].[vwEmailJobProfileUpdateStats] ejps
        ON ej.[ID] = ejps.[EmailJobID]
    LEFT OUTER JOIN [dbo].[vwEmailJobSignupStats] ejss
        ON ej.[ID] = ejss.[EmailJobID]
    LEFT OUTER JOIN [dbo].[vwEmailJobVolunteerStats] ejvs
        ON ej.[ID] = ejvs.[EmailJobID]

    --------------------------------
    -- Amount columns
    --------------------------------

    SELECT
        [GiftCurrencyType],
        Sum([GiftAmount]) As [GiftAmountTotal]
    FROM
        (
            SELECT
                [GiftCurrencyType],
                [GiftAmount]
            FROM
                [dbo].[DonationTransactions] dt
            INNER JOIN [dbo].[EmailJob_Recipient] ejr
                ON dt.[EmailJobRecipientID] = ejr.[ID]
            INNER JOIN #EmailJobs ej
                ON ejr.[EmailJobID] = ej.[ID]
            where dt.isdeleted = 0 and dt.[status] = 1

            UNION ALL

            SELECT
                [GiftCurrencyType],
                [GiftAmount]
            FROM
                [dbo].[EventTransactions] et
            INNER JOIN [dbo].[EmailJob_Recipient] ejr
                ON et.[EmailJobRecipientID] = ejr.[ID]
            INNER JOIN #EmailJobs ej
                ON ejr.[EmailJobID] = ej.[ID]
            where et.isdeleted = 0 and et.[status] = 1

            UNION ALL

            SELECT
                [GiftCurrencyType],
                [GiftAmount]
            FROM
                [dbo].[MembershipTransactions] mt
            INNER JOIN [dbo].[EmailJob_Recipient] ejr
                ON mt.[EmailJobRecipientID] = ejr.[ID]
            INNER JOIN #EmailJobs ej
                ON ejr.[EmailJobID] = ej.[ID]
            where mt.isdeleted = 0 and mt.[status] = 1                

        ) AS AllGiftTransactionTables
    GROUP BY
        AllGiftTransactionTables.[GiftCurrencyType]

    DROP TABLE #EmailJobs
END