USP_GET_EMAIL_STATS

CREATE procedure dbo.USP_GET_EMAIL_STATS
(
  @EmailID int 
)
as
begin
    set nocount on

declare @TOTALDELIVERED int
declare @TOTALBOUNCED int
declare @TOTALOPENED int
declare @TOTALCLICKS int
declare @TOTALDONATIONS int
declare @TOTALOPTEDOUT int
declare @TOTALNOTSENT int
declare @TOTALINACTIVE int
declare @TOTALSPAM int
declare @TOTALDONATIONAMOUNT decimal(20,2)

declare @STATUS int
declare @SENDAFTERDATE datetime
declare @EMAILLIST nvarchar(500)
declare @FROMADDRESS nvarchar(500)
declare @FROMDISPLAYNAME nvarchar(500)
declare @EMAILSUBJECT as nvarchar(4000)
declare @SENDDATE as datetime
-- Basic stats

create table #stats
(
    ID int,
    [Status] int,
    WSRefreshed datetime,
    Constituents int,
    Queued int,
    NotAddressed int,
    OptOuts int,
    InvalidAccounts int,
    [Sent] int,
    Unsent int,
    Opened int,
    DSN int,
    Inactive int,
  SpamComplaint int
)

insert into #stats exec spGetEmailSummary @EmailID
select 
    @TOTALDELIVERED = Sent,
    @TOTALOPENED = Opened,
    @STATUS = [Status]
from #stats
drop table #stats


-- Donation stats

select @TOTALDONATIONS = COUNT(distinct T.EmailJobRecipientID),@TOTALDONATIONAMOUNT = SUM(T.GiftAmount) from 
(
    select EmailJobRecipientID,GiftAmount,EmailID,IsDeleted,[Status] from dbo.DonationTransactions
    union all
    select EmailJobRecipientID,GiftAmount,EmailID,IsDeleted,[Status] from dbo.EventTransactions
    union all
    select EmailJobRecipientID,GiftAmount,EmailID,IsDeleted,[Status] from dbo.MembershipTransactions
) T
where T.EmailID = @EmailID and T.IsDeleted = 0 and T.Status = 1 and @STATUS = 7

-- Click stats

-- As per the fixing of Bug #808096, if in future any changes are needed in joins of below query, kindly make respective changes in USP_GET_EMAIL_JOB_DETAILS stored procedure as well to maintain the click counts in sync.      

SELECT    @TOTALCLICKS = count(distinct s.EmailJobRecipientID)
FROM [EmailLink] el
LEFT OUTER JOIN Stats s 
on (s.pageid = el.pid and s.sourceid = el.emailid and s.sourcetypeid = 1 and el.PID is NOT NULL)
or (s.URL = el.URL and s.sourceid = el.emailid  and s.sourcetypeid = 1 and el.[URL] is not null)
or (s.DocumentId = el.DocumentID and s.sourceid = el.emailid  and s.sourcetypeid = 0 and el.[DocumentID] is not null)
INNER JOIN EmailJob_Recipient EJR on s.emailjobrecipientId=EJR.id and EJR.[sent]=1
WHERE el.EmailID = @EmailID and @STATUS = 7

select @SENDDATE = EJ.CREATEDATE ,@SENDAFTERDATE = EJ.SendAfterDate,@FROMADDRESS = EJ.FROMADDRESS, @FROMDISPLAYNAME = EJ.FROMDISPLAYNAME, @EMAILSUBJECT = E.Subject from dbo.EmailJob EJ
inner join dbo.Email E on E.id = EJ.EmailID
where EJ.EmailID = @EmailID

select 
    @TOTALBOUNCED = isnull(sum(case when RecentDSNCategory <>112 then 1 else 0 end),0), @TOTALSPAM = isnull(sum(case when RecentDSNCategory =112 then 1 else 0 end),0) -- 112 represent spam complaint.

  from EMAILJOB_RECIPIENT (nolock) where DSNED =1 and EMAILID = @EMAILID 

select @TOTALNOTSENT = Count(*)
  from dbo.email_recipient R
       left outer join dbo.EmailJob_Recipient EJR on R.iD = EJR.EmailRecipientID
       inner join dbo.Email E on E.ID = R.EmailID 
       where R.EmailID =@EmailID and  ((EJR.DSNed = 'true' and EJR.Sent ='true') or R.InvalidAddress = 'true' or R.InvalidAccount ='true' or R.OptOut ='true' or isnull(R.Inactive,0) = 'true')

select @TOTALINACTIVE = Count(1)
  from dbo.email_recipient R      
       where R.EmailID =@EmailID and isnull(R.Inactive,0) = 'true' and ( R.InvalidAddress <> 'true' and R.InvalidAccount <> 'true' and  R.OptOut <> 'true')

select @TOTALOPTEDOUT = Count(*)
  from dbo.email_recipient R
       left outer join dbo.EmailJob_Recipient EJR on R.iD = EJR.EmailRecipientID
       inner join dbo.Email E on E.ID = R.EmailID 
       where R.EmailID =@EmailID and R.OptOut ='true'


DECLARE @tempEmailListName TABLE(Name nvarchar(200))

INSERT INTO @tempEmailListName
select case when El.DataSourceID =301 then 'Direct Marketing Email List'
        else EL.Name END as ListName from dbo.Email_EmailList EEL
inner join dbo.EmailList EL on EEL.EmailListID = EL.ID
where EEL.EmailID = @EmailID and EEL.IsTest = 0 

SELECT @EMAILLIST = STUFF((SELECT ', ' + CAST(Name AS VARCHAR(200)) [text()]
         FROM @tempEmailListName 
         FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,2,' ')

select 
    @EmailID 'EmailID',
    ISNULL(@TOTALDELIVERED,0) 'TOTALDELIVERED', 
    ISNULL(@TOTALBOUNCED,0) 'TOTALBOUNCED',
    ISNULL(@TOTALOPENED,0) 'TOTALOPENED', 
    ISNULL(@TOTALCLICKS,0) 'TOTALCLICKS',
    ISNULL(@TOTALDONATIONS,0) 'TOTALDONATIONS',
    ISNULL(@TOTALOPTEDOUT,0) 'TOTALOPTEDOUT',
    ISNULL(@TOTALDONATIONAMOUNT,0) 'TOTALDONATIONAMOUNT' ,
    ISNULL(@STATUS,0) 'STATUS',
    @SENDAFTERDATE 'SENDAFTERDATE',
    @FROMADDRESS 'FROMADDRESS',
    @FROMDISPLAYNAME 'FROMDISPLAYNAME',
    @EMAILLIST 'EmailListName',
  @EMAILSUBJECT 'EmailSubject',
  @SENDDATE 'SendDate',
  ISNULL(@TOTALNOTSENT, 0) 'TOTALNOTSENT',
  ISNULL(@TOTALINACTIVE,0) 'TOTALINACTIVE',
  ISNULL(@TOTALSPAM,0) 'TOTALSPAM'
 end