spGetEmailProjectSummary

CREATE PROCEDURE [dbo].[spGetEmailProjectSummary]
@EmailProjectID int,
@ProjectAppealID int,
@AppealListID int,
@AppealListType int

as

DECLARE @JobStatus int
DECLARE @Constituents int
DECLARE @NotAddressed int
DECLARE @OptOuts int
DECLARE @InvalidAccounts int
DECLARE @InactiveAccounts int
DECLARE @Queued int
DECLARE @Sent int
DECLARE @Opened int
DECLARE @DSN int
DECLARE @SPAM int
DECLARE @Unsent int
DECLARE @Donations int
DECLARE @DonationGiftAmount int
DECLARE @EventDonations int
DECLARE @EventGiftAmount int
DECLARE @MembershipDonations int
DECLARE @MembershipGiftAmount int


SELECT 
    @Constituents    = SUM(j.NumberRequested),
    @NotAddressed    = SUM(j.NumberAbsentAddress),
    @OptOuts         = SUM(j.NumberOptedOut),
    @InvalidAccounts = SUM(j.NumberInvalidAccount),
    @InactiveAccounts = 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] 
INNER JOIN EmailProjectAppeal epa on epael.EmailProjectAppealID = epa.[ID] 
INNER JOIN EmailProject ep on epa.[EmailProjectID] = ep.[ID]
WHERE (ep.[ID] = @EmailProjectID OR @EmailProjectID = 0) AND (epa.[ID] = @ProjectAppealID OR @ProjectAppealID = 0) 
   AND (epael.[ID] = @AppealListID OR @AppealListID = 0) AND j
.Status IN (4,6,7) AND epael.[AppealListType] = @AppealListType
   AND (j.ConditionalParentJobID IS NULL)

IF @@ROWCOUNT = 1
    BEGIN
----RecipientData----------------------------------------------------------------------------------------------------------

-----------

        declare @JOBS table (ID int)
        insert into @JOBS
        select j.ID
        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] 
        INNER JOIN EmailProjectAppeal epa on epael.EmailProjectAppealID = epa.[ID] 
        INNER JOIN EmailProject ep on epa.[EmailProjectID] = ep.[ID]
        WHERE (ep.[ID] = @EmailProjectID OR @EmailProjectID = 0) AND (epa.[ID] = @ProjectAppealID OR @ProjectAppealID = 0) 
           AND (epael.[ID] = @AppealListID OR @AppealListID = 0) AND epael.[AppealListType] = @AppealListType

        SELECT @Queued = COUNT(r.EmailAddress),
                @Sent = SUM(case r.Sent
 when 1 then 1 else 0 end),
                @Opened = SUM(case r.Opened when 1 then 1 else 0 end),
                @DSn = SUM(case when r.DSNed =1 and r.RecentDSNCategory <> 112 then 1 else 0 end),
                @SPAM = SUM(case when r.DSNed =1 and r.RecentDSNCategory = 112 then 1 else 0 end)
        FROM EmailJob_Recipient as r 
        inner join @JOBS j on j.ID = r.EmailJobID
        where r.[Current] = 1


    SET @UnSent = @Queued - @Sent

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

    SELECT    @Donations = count(et.DonationTransactionsID), @DonationGiftAmount = 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] 

INNER JOIN EmailProjectAppeal epa on epael.EmailProjectAppealID = epa.[ID] 
    INNER JOIN EmailProject ep on epa.[EmailProjectID] = ep.[ID]
    WHERE (ep.[ID] = @EmailProjectID OR @EmailProjectID = 0) AND (epa.[ID] = @ProjectAppealID OR @ProjectAppealID = 0) 

       AND (epael.[ID] = @AppealListID OR @AppealListID = 0) AND epael.[AppealListType] = @AppealListType
    and et.isdeleted = 0
    and et.Status = 1

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

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

    SELECT    @EventDonations = count(et.EventTransactionsID), @EventGiftAmount = 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] 
    INNER JOIN EmailProjectAppeal epa on epael.EmailProjectAppealID = epa.[ID] 
    INNER JOIN EmailProject ep on epa.[EmailProjectID] = ep.[ID]
    WHERE (ep.[ID] = @EmailProjectID OR @EmailProjectID = 0) AND (epa.[ID] = @ProjectAppealID OR @ProjectAppealID = 0) 
       AND (epael.[ID] = @AppealListID OR @AppealListID = 0) AND epael.[AppealListType] = @AppealListType
    and et.isdeleted = 0
    and et.Status = 1

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


    SELECT    @MembershipDonations = count(mt.ID), @MembershipGiftAmount = 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] 
    INNER JOIN EmailProjectAppeal epa on epael.EmailProjectAppealID = epa.[ID] 
    INNER JOIN EmailProject ep on epa.[EmailProjectID] = ep.[ID]
    WHERE (ep.[ID] = @EmailProjectID OR @EmailProjectID = 0) AND (epa.[ID] = @ProjectAppealID OR @ProjectAppealID = 0) 
       AND (epael.[ID] = @AppealListID OR @AppealListID = 0) AND epael.[AppealListType] = @AppealListType
    and mt.isdeleted = 0
    and mt.Status = 1

    END
ELSE
    BEGIN
        SET @Queued = 0
        SET @Sent = 0
        SET @Opened = 0
        SET @DSN = 0
        SET @UnSent = 0
    SET @SPAM = 0
    END



DECLARE @PKID
 int

SELECT @PKID = (@EmailProjectID + @ProjectAppealID + @AppealListID)
SELECT @PKID as ID, @Constituents Constituents,
(ISNULL(@DonationGiftAmount,0) + ISNULL(@EventGiftAmount,0) + ISNULL(@MembershipGiftAmount,0)) GiftAmount, @NotAddressed NotAddressed,
    @OptOuts OptOuts, @InvalidAccounts InvalidAccounts, @Sent Sent, @Unsent Unsent, @Opened Opened, @DSN DSN, @InactiveAccounts InactiveAccounts, @SPAM SpamComplaint