spGetAppealListEmailsSummary

CREATE procedure dbo.spGetAppealListEmailsSummary
      @ProjectAppealListID int,
            @Type nvarchar(10)

            AS

            DECLARE @JobData TABLE(ID int, JobID int, Status tinyint, Constituents int, NotAddressed int, OptOut int,InvalidAccount int, InactiveAccount int)
            DECLARE @RecipientData TABLE(ID int, Queued int, Sent int, Opened int, DSN int, SPAMCOMPLAINT int)
            DECLARE @DonationData TABLE(ID int, Donations int, DonationGiftAmount int)
            DECLARE @EventData TABLE(ID int, Events int, EventGiftAmount int)
            DECLARE @MembershipData TABLE(ID int, Memberships int, MembershipGiftAmount int)
            DECLARE @Types TABLE (ID int)

            INSERT INTO @Types  SELECT [ID] FROM fnMakeIDsTableFromString(@Type,',')

            INSERT INTO @JobData (ID , JobID, Status, Constituents, NotAddressed, OptOut,InvalidAccount,InactiveAccount)
            SELECT epale.[ID], j.ID, j.Status, SUM(j.NumberRequested),
                SUM(j.NumberAbsentAddress),
                SUM(j.NumberOptedOut),
                SUM(j.NumberInvalidAccount),
                SUM(ISNULL(j.NumberInactiveAccount,0))
            FROM EmailJob j inner join Email e on j.EmailID = e.[ID]
            INNER JOIN emailprojectappeal_ListEmail epale on e.ProjectAppealListEmailID = epale.[ID]
            WHERE (epale.[EmailProjectAppealListID] = @ProjectAppealListID) 
            AND epale.Deleted = 0 AND EXISTS (SELECT NULL FROM @Types WHERE ID = e.[Type] AND j.ConditionalParentJobID Is Null)
            GROUP BY j.ID, j.Status, epale.[ID]

            IF EXISTS(SELECT TOP 1 [ID] FROM @JobData) 
                BEGIN
                    ----RecipientData---------------------------------------------------------------------------------------------------------------------

                            INSERT INTO @RecipientData (ID, Queued, Sent, Opened, DSN, SPAMCOMPLAINT)
                            SELECT epale.ID, COUNT(r.EmailAddress), SUM(case r.Sent when 1 then 1 else 0 end),
                                SUM(case r.Opened when 1 then 1 else 0 end),
                SUM(case when r.DSNed =1 and r.RecentDSNCategory <>112 then 1 else 0 end),
                SUM(case when r.DSNed =1 and r.RecentDSNCategory =112 then 1 else 0 end)
                            FROM Email e
                            INNER JOIN EmailJob j on j.EmailID = e.[ID]
                            INNER JOIN emailprojectappeal_ListEmail epale on e.ProjectAppealListEmailID = epale.[ID]
                            LEFT OUTER JOIN EmailJob_Recipient as r on r.EmailJobID = j.ID AND r.[Current] = 1
                            WHERE (epale.[EmailProjectAppealListID] = @ProjectAppealListID) 
                            AND epale.Deleted = 0
                            GROUP BY epale.ID

                    -----DonataData--------------------------------------------------------------------------------------------------------------------

                            INSERT INTO @DonationData (ID, Donations, DonationGiftAmount)
                            SELECT    epale.[ID], 
                                        count(dt.DonationTransactionsID), sum(dt.GiftAmount)
                                FROM DonationTransactions dt 
                                INNER JOIN Email e on dt.EmailID = e.[ID]
                                INNER JOIN emailprojectappeal_ListEmail epale on e.ProjectAppealListEmailID = epale.[ID]
                                WHERE epale.[EmailProjectAppealListID] = @ProjectAppealListID 
                                and dt.isdeleted = 0
                                and dt.Status = 1
                                GROUP BY epale.[ID]

                    -----EventData

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

                            INSERT INTO @EventData(ID, Events, EventGiftAmount)
                            SELECT epale.[ID], count(et.EventTransactionsID), sum(et.GiftAmount)

                FROM EventTransactions et 
                                    INNER JOIN Email e on et.EmailID = e.[ID]
                                    INNER JOIN emailprojectappeal_ListEmail epale on e.ProjectAppealListEmailID = epale.[ID]
                            WHERE epale.[EmailProjectAppealListID] = @ProjectAppealListID 

                and et.isdeleted = 0
                                    and et.Status = 1
                            GROUP BY epale.[ID]

                    -----MembershipData--------------------------------------------------------------------------------------------------------------------

                            INSERT INTO @MembershipData
(ID, Memberships, MembershipGiftAmount)
                            SELECT epale.[ID], count(mt.ID), sum(mt.GiftAmount)
                            FROM MembershipTransactions mt 
                                    INNER JOIN Email e on mt.EmailID = e.[ID]
                                    INNER JOIN emailprojectappeal_ListEmail epale on e.ProjectAppealListEmailID = epale.[ID]
                            WHERE epale.[EmailProjectAppealListID] = @ProjectAppealListID 
                                    and mt.isdeleted = 0
                                    and mt.Status = 1
                            GROUP BY epale.[ID]

                END


            --Pull everything together

            SELECT epale.[ID], e.[Name], jd.JobID, jd.Status, e.[type], e.[Subject], e.[FromDisplayName],Constituents, NotAddressed, Sent, (Queued - Sent) as UnSent, Opened,
                (ISNULL(EventGiftAmount,0) + ISNULL(MembershipGiftAmount,0) + ISNULL(DonationGiftAmount,0)) as GiftAmount,InvalidAccount, OptOut,  DSN ,InactiveAccount, SpamComplaint
                FROM @JobData jd 
                INNER JOIN @RecipientData rd on jd.[ID] = rd.[ID]
                LEFT OUTER JOIN @DonationData dd on rd.[ID] = dd.[ID]  
                LEFT OUTER JOIN @EventData ed on rd.[ID] = ed.[ID]  
                LEFT OUTER JOIN @MembershipData md on rd.[ID] = md.[ID]  
                RIGHT OUTER JOIN EmailProjectAppeal_listEmail epale on rd.id = epale.id inner join Email e
                on epale.ID = e.ProjectAppealListEmailID
                WHERE epale.[EmailProjectAppealListID] = @ProjectAppealListID AND epale.Deleted = 0 AND EXISTS (SELECT NULL FROM @Types WHERE ID = e.[Type])
                ORDER BY e.[Type] ASC