spGetEmailSummary

CREATE PROCEDURE [dbo].[spGetEmailSummary]
( 
    @EmailID int,
    @EmailListID int = null 
)
AS
BEGIN
set nocount on

DECLARE @JobStatus int;
DECLARE @Constituents int;
DECLARE @NotAddressed int;
DECLARE @OptOuts int;
DECLARE @InvalidAccounts int;
DECLARE @Queued int = 0;
DECLARE @Sent int = 0;
DECLARE @InactiveAccounts int = 0;
DECLARE @Opened int = 0;
DECLARE @DSN int = 0;
DECLARE @SPAM int = 0;
DECLARE @Unsent int = 0;
Declare @ListType int =0;

DECLARE @FirstJobID int
DECLARE @ListTypeMatched as int =0
IF @EmailListID is not null and @EmailListID > 0 BEGIN

    select @Constituents = isnull(el.AcquiredRecordCount,0) from dbo.EmailList el where el.ID= @EmailListID

    select @ListType = el.DataSourceID from dbo.EmailList el where el.ID = @EmailListID

    IF @ListType = 1 BEGIN -- Constituents type list

     SET @ListTypeMatched =1
        select 
            @NotAddressed    = isnull(sum(case r.InvalidAddress when 1 then 1 else 0 end),0),
            @OptOuts         = isnull(sum(case r.OptOut when 1 then 1 else 0 end),0),
            @InvalidAccounts = isnull(sum(case r.InvalidAccount when 1 then 1 else 0 end),0),
      @InactiveAccounts = isnull(sum(case when isnull(r.Inactive,0) = 1 and (r.OptOut <> 1 and r.InvalidAccount <>1 ) then 1 else 0 end),0)
        from EmailList_People ep  with (nolock)
        INNER JOIN Email_Recipient r with (nolock) on r.BackOfficeSystemPeopleID = ep.PeopleID
        where r.EmailID=  @EmailID  AND r.UserID = 0
        AND ep.EmailListID= @EmailListID
        AND r.BackOfficeSystemPeopleID IS NOT NULL

        SELECT 
            @Queued    = coalesce(COUNT(r.EmailAddress), 0),
            @Sent    = coalesce(SUM(case when r.Sent =1 and r.dsned <>1 then 1 else 0 end), 0),
            @Opened = coalesce(SUM(case when r.Opened=1 and r.Sent =1 then 1 else 0 end), 0),
            @DSN    = coalesce(SUM(case when r.DSNed =1 and RecentDSNCategory <> 112 then 1 else 0 end), 0),
            @SPAM    = coalesce(SUM(case when r.DSNed =1 and RecentDSNCategory = 112 then 1 else 0 end), 0)
        FROM EmailJob j  with (nolock)
        LEFT OUTER JOIN EmailJob_Recipient as r with (nolock) on r.EmailJobID = j.ID AND r.[Current] = 1
        INNER JOIN EmailList_People as ep with (nolock) on ep.PeopleID = r.BackOfficeSystemPeopleID 
        WHERE j.EmailID = @EmailID
        AND j.[Current] = 1
        AND j.[Status] = 7
        AND ep.EmailListID = @EmailListID
        AND r.UserID = 0
        AND r.BackOfficeSystemPeopleID IS NOT NULL

    END
    ELSE IF @ListType = 303 BEGIN -- User Defined List

     SET @ListTypeMatched =1
        select 
            @NotAddressed    = isnull(sum(case r.InvalidAddress when 1 then 1 else 0 end),0),
            @OptOuts         = isnull(sum(case r.OptOut when 1 then 1 else 0 end),0),
            @InvalidAccounts = isnull(sum(case r.InvalidAccount when 1 then 1 else 0 end),0),
      @InactiveAccounts = isnull(sum(case when isnull(r.Inactive,0) = 1 and (r.OptOut <> 1 and r.InvalidAccount <>1 ) then 1 else 0 end),0)
        from dbo.Email_Recipient r  with (nolock)
        INNER JOIN dbo.EmailList_UserDefined eu with (nolock) on r.EmailAddress  = eu.EmailAddress
        where r.EmailID=  @EmailID 
        AND eu.EmailListID= @EmailListID 
        AND r.UserID = 0
        AND eu.Enabled = 1
        AND r.BackOfficeSystemPeopleID IS NULL
        AND R.SOURCEID = eu.[GUID]

        SELECT 
            @Queued    = coalesce(COUNT(r.EmailAddress), 0),
            @Sent    = coalesce(SUM(case when r.Sent =1 and r.dsned <>1 then 1 else 0 end), 0),
            @Opened = coalesce(SUM(case when r.Opened=1 and r.Sent =1 then 1 else 0 end), 0),
            @DSN    = coalesce(SUM(case when r.DSNed =1 and RecentDSNCategory <> 112 then 1 else 0 end), 0),
            @SPAM    = coalesce(SUM(case when r.DSNed =1 and RecentDSNCategory = 112 then 1 else 0 end), 0)
        FROM EmailJob j  with (nolock)
        LEFT OUTER JOIN dbo.EmailJob_Recipient as r with (nolock) on r.EmailJobID = j.ID AND r.[Current] = 1
        INNER JOIN dbo.EmailList_UserDefined as eu with (nolock) on eu.EmailAddress = r.EmailAddress 
        WHERE j.EmailID = @EmailID
        AND j.[Current] = 1
        AND j.[Status] = 7
        AND eu.EmailListID = @EmailListID
        AND r.UserID = 0
        AND eu.Enabled = 1
        AND r.BackOfficeSystemPeopleID IS NULL    
        AND R.SOURCEID = eu.[GUID]
    END
    ELSE IF @ListType = 101 BEGIN -- Registered Users List

     SET @ListTypeMatched =1
        select 
            @NotAddressed    = isnull(sum(case r.InvalidAddress when 1 then 1 else 0 end),0),
            @OptOuts         = isnull(sum(case r.OptOut when 1 then 1 else 0 end),0),
            @InvalidAccounts = isnull(sum(case r.InvalidAccount when 1 then 1 else 0 end),0),
      @InactiveAccounts = isnull(sum(case when isnull(r.Inactive,0) = 1 and (r.OptOut <> 1 and r.InvalidAccount <>1 ) then 1 else 0 end),0)
        from dbo.Email_Recipient r   with (nolock)
        INNER JOIN dbo.ClientUsers cu  with (nolock) on r.UserID  = cu.ID
        where r.EmailID=  @EmailID 


        SELECT 
            @Queued    = coalesce(COUNT(r.EmailAddress), 0),
            @Sent    = coalesce(SUM(case when r.Sent =1 and r.dsned <>1 then 1 else 0 end), 0),
            @Opened = coalesce(SUM(case when r.Opened=1 and r.Sent =1 then 1 else 0 end), 0),
            @DSN    = coalesce(SUM(case when r.DSNed =1 and RecentDSNCategory <> 112 then 1 else 0 end), 0),
            @SPAM    = coalesce(SUM(case when r.DSNed =1 and RecentDSNCategory = 112 then 1 else 0 end), 0)
        FROM EmailJob j  with (nolock)
        LEFT OUTER JOIN dbo.EmailJob_Recipient as r with (nolock) on r.EmailJobID = j.ID AND r.[Current] = 1
        INNER JOIN dbo.ClientUsers cu with (nolock) on cu.ID = r.UserID 
        WHERE j.EmailID = @EmailID
        AND j.[Current] = 1
        AND j.[Status] = 7            

    END
    ELSE IF @ListType = 300 BEGIN -- Imported Users list

    SET @ListTypeMatched =1
        select 
            @NotAddressed    = isnull(sum(case r.InvalidAddress when 1 then 1 else 0 end),0),
            @OptOuts         = isnull(sum(case r.OptOut when 1 then 1 else 0 end),0),
            @InvalidAccounts = isnull(sum(case r.InvalidAccount when 1 then 1 else 0 end),0),
      @InactiveAccounts = isnull(sum(case when isnull(r.Inactive,0) = 1 and (r.OptOut <> 1 and r.InvalidAccount <>1 ) then 1 else 0 end),0)
        from dbo.Email_Recipient r with (nolock)
        INNER JOIN dbo.EmailListUploadMasterList ml with (nolock) on r.SOURCEID  = ml.Guid
        where r.EmailID=  @EmailID 
        AND ml.EmailListID =@EmailListID        

        SELECT 
            @Queued    = coalesce(COUNT(r.EmailAddress), 0),
            @Sent    = coalesce(SUM(case when r.Sent =1 and r.dsned <>1 then 1 else 0 end), 0),
            @Opened = coalesce(SUM(case when r.Opened=1 and r.Sent =1 then 1 else 0 end), 0),
            @DSN    = coalesce(SUM(case when r.DSNed =1 and RecentDSNCategory <> 112 then 1 else 0 end), 0),
            @SPAM    = coalesce(SUM(case when r.DSNed =1 and RecentDSNCategory = 112 then 1 else 0 end), 0)
        FROM EmailJob j with (nolock)
        LEFT OUTER JOIN dbo.EmailJob_Recipient as r with (nolock) on r.EmailJobID = j.ID AND r.[Current] = 1
        INNER JOIN dbo.EmailListUploadMasterList ml with (nolock) on r.SOURCEID  = ml.Guid
        where r.EmailID=  @EmailID 
        AND ml.EmailListID =@EmailListID
        AND j.[Current] = 1
        AND j.[Status] = 7

    END
END

IF @ListTypeMatched <>1
BEGIN

    SELECT TOP 1 @FirstJobID=[ID]
    FROM EmailJob
    WHERE EmailID = @EmailID
    ORDER BY ID ASC

    SELECT 
        @Constituents    = SUM(j.NumberRequested), @NotAddressed    = SUM(j.NumberAbsentAddress),
        @OptOuts         = SUM(j.NumberOptedOut),
        @InvalidAccounts = SUM(j.NumberInvalidAccount),
    @InactiveAccounts = isnull(SUM(isnull(j.NumberInactiveAccount,0)),0)
    FROM EmailJob j  with (nolock)
    WHERE j.ID = @FirstJobID
    AND j.Status IN (4,5,6,7)

    if @@rowcount = 1
    BEGIN

        SELECT 
            @Constituents    = @Constituents + ISNULL(SUM(j.NumberRequested), 0),
            @NotAddressed    = @NotAddressed + ISNULL(SUM(j.NumberAbsentAddress), 0),
            @OptOuts         = @OptOuts + ISNULL(SUM(j.NumberOptedOut), 0),
            @InvalidAccounts = @InvalidAccounts + ISNULL(SUM(j.NumberInvalidAccount),0),
      @InactiveAccounts = @InactiveAccounts + isnull(SUM(isnull(j.NumberInactiveAccount,0)),0)
        FROM EmailJob j  with (nolock)
        WHERE j.ConditionalParentJobID = @FirstJobID
        AND j.Status IN (4,5,6,7)


        SELECT 
            @Queued    = coalesce(COUNT(r.EmailAddress), 0),
            @Sent    = coalesce(SUM(case when r.Sent =1 and r.dsned <>1 then 1 else 0 end), 0),
            @Opened = coalesce(SUM(case when r.Opened=1 and r.Sent =1 then 1 else 0 end), 0),
            @DSN    = coalesce(SUM(case when r.DSNed =1 and RecentDSNCategory <> 112 then 1 else 0 end), 0),
            @SPAM    = coalesce(SUM(case when r.DSNed =1 and RecentDSNCategory = 112 then 1 else 0 end), 0)
        FROM EmailJob j  with (nolock)
        LEFT OUTER JOIN EmailJob_Recipient as r with (nolock) on r.EmailJobID = j.ID AND r.[Current] = 1
        WHERE j.EmailID = @EmailID
        AND j.[Current] = 1
        AND j.[Status] = 7         
    END
END

SET @UnSent = @Queued - @Sent
  SELECT
      e.[ID], j.[Status], isnull(j.[WSDateLastAccessed],j.CreateDate) WSRefreshed,
      @Constituents Constituents, @Queued Queued, @NotAddressed NotAddressed,
      @OptOuts OptOuts, @InvalidAccounts InvalidAccounts, @Sent Sent, @Unsent Unsent, @Opened Opened, @DSN DSN ,@InactiveAccounts InactiveAccounts,@SPAM SpamComplaint
  FROM Email e  with (nolock)
  INNER JOIN EmailJob j  with (nolock) on j.EmailID = e.[ID]
  WHERE e.[ID] = @EmailID
  AND j.[Current] = 1

END