spGetAppealListsSummary

CREATE procedure dbo.spGetAppealListsSummary
@ProjectAppealID int

AS

DECLARE @JobData TABLE(ID int, 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, DonationMethod int, Donations int, DonationGiftAmount int)
DECLARE @EventData TABLE(ID int, Events int, EventGiftAmount int)
DECLARE @MembershipData TABLE(ID int, Memberships int, MembershipGiftAmount int)

INSERT INTO @JobData (ID , Constituents, NotAddressed, OptOut,InvalidAccount,InactiveAccount)
SELECT epael.[ID], 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]
INNER JOIN EmailProjectAppeal_EmailList epael on epale.[EmailProjectAppealListID] = epael.[ID] 
WHERE (epale.EmailProjectAppealID = @ProjectAppealID) AND j.Status IN 
(4,6,7,14)
AND epale.Deleted = 0 AND epael.AppealListType = 1
GROUP BY epael.[ID]

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

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

        INSERT INTO @RecipientData (ID, Queued, Sent, Opened, DSN,SPAMCOMPLAINT)
        SELECT epael.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]
        INNER JOIN EmailProjectAppeal_EmailList epael on epale.[EmailProjectAppealListID] = epael.[ID] 
        LEFT OUTER JOIN EmailJob_Recipient as r on r.EmailJobID = j.ID AND r.[Current] = 1
        WHERE epael.EmailProjectAppealID = @ProjectAppealID AND epael.AppealListType = 1
        AND epale.Deleted = 0
        GROUP BY epael.ID

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

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

        INSERT INTO @DonationData (ID, DonationMethod, Donations, DonationGiftAmount)
        SELECT epael.[ID], et.[GiftPaymentMethod],
                    count(et.DonationTransactionsID), sum(et.GiftAmount)
            FROM DonationTransactions et 
            INNER JOIN Email e on et.EmailID = e.[ID]
            INNER JOIN emailprojectappeal_ListEmail epale on e.ProjectAppealListEmailID = epale.[ID]
            INNER JOIN EmailProjectAppeal_EmailList epael on epale.[EmailProjectAppealListID] = epael.[ID] 
            WHERE epael.EmailProjectAppealID = @ProjectAppealID AND epael.AppealListType = 1
            and et.isdeleted = 0
            and et.Status = 1
            GROUP BY et.[GiftPaymentMethod], epael.[ID]

        -----EventData-----------------------------------

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

                INSERT INTO @EventData(ID, Events, EventGiftAmount)
                SELECT epael.[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]
                        INNER JOIN EmailProjectAppeal_EmailList epael on epale.[EmailProjectAppealListID] = epael.[ID] 
                WHERE epael.EmailProjectAppealID = @ProjectAppealID AND epael.AppealListType = 1
                        and et.isdeleted = 0
                        and et.Status = 1
                GROUP BY epael.ID

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

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

                INSERT INTO @MembershipData(ID, Memberships, MembershipGiftAmount)
                SELECT epael.[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]
                        INNER JOIN EmailProjectAppeal_EmailList epael on epale.[EmailProjectAppealListID] = epael.[ID] 
                WHERE epael.EmailProjectAppealID = @ProjectAppealID AND epael.AppealListType = 1
                        and mt.isdeleted = 0
                        and mt.Status = 1
                GROUP BY epael.ID

    END

SELECT epael.[ID], el.[Name], epael.[AppealListType], Constituents, NotAddressed, OptOut,InvalidAccount, 
    DonationMethod, Donations, (ISNULL(EventGiftAmount,0) + ISNULL(MembershipGiftAmount,0) + ISNULL(DonationGiftAmount,0)) as GiftAmount,
    Sent, (Queued - Sent) as UnSent, Opened, 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_Emaillist epael on rd.id = epael.id 
    INNER JOIN EMailList el
    on epael.EmaillistID = el.ID
    WHERE epael.[EmailProjectAppealID] = @ProjectAppealID AND epael.[AppealListType] = 1