USP_EXPORT_EMAIL_DETAILS

CREATE procedure dbo.USP_EXPORT_EMAIL_DETAILS
(
  @EmailID int = 0  
)
as
begin
    set nocount on

    declare @EmailListName AS varchar(max);

    select @EmailListName =(case when El.DataSourceID =301 then ISNULL(@EmailListName+';','')+ 'Direct Marketing Email List'
        else ISNULL(@EmailListName+';','') + EL.Name  END)    
      from dbo.Email_EmailList EEL
      inner join dbo.EmailList EL on EEL.EmailListID = EL.ID
      where EEL.EmailID = @EmailID and EEL.IsTest = 0;
    With CTE_FilteredResult AS
    (
      select 
           EJR.ID ID
          ,R.EmailID EmailID
          ,IsNull(E.SendAfterDate, EJR.SentDate) SendDate
          ,E.[subject] EmailSubject
          ,@EmailListName EmailListName
          ,R.EmailAddress as EmailAddress
          ,R.DisplayName as EmailDisplayName
      ,EJR.GlobalOptedOut GlobalOptedOut
          ,R.OptOut OptOut
          ,EJR.DSNed DSNed
          ,EJR.RecentDSNCategory RecentDSNCategory
          ,R.InvalidAddress InvalidAddress
          ,R.InvalidAccount InvalidAccount
          ,EJR.[Sent] Sent
          ,EJR.Opened Opened
          ,R.BackOfficeSystemPeopleID as BackOfficeSystemPeopleID
      ,R.Inactive
          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 E.ID = @EmailID

      )

      select 
           FR.EmailID
          ,FR.SendDate
          ,FR.EmailSubject
          ,FR.EmailListName
          ,FR.EmailAddress
          ,FR.EmailDisplayName
             ,BP.LOOKUPID as LookupId
          ,case    when FR.GlobalOptedOut        = 'true' or FR.OptOut = 'true' then 'Opted Out'
                  when FR.DSNed                = 'true'    then    case    
                                                                  when FR.RecentDSNCategory = 2 then 'Hard Bounce'
                                                                  when FR.RecentDSNCategory = 108 then 'Previously Marked As Hard Bounce'
                                                                  when FR.RecentDSNCategory = 109 then 'Previously Marked As Spam Complaint'
                                                                  when FR.RecentDSNCategory not in (2,108,109,112) then 'Soft Bounce'
                                  when FR.RecentDSNCategory = 112 then 'Spam Complaint'
                                                                  else '' 
                                                              end
              when FR.InvalidAddress =1 then 'No email address'
              when FR.InvalidAccount = 1 then 'Invalid email address'                                                                        
              when FR.Inactive = 1 then 'Unengaged email address'        
              when T.Transactions > 0 
                      or S.Clicks > 0 
                      or (FR.[Sent] = 'true' and FR.DSNed = 'false') 
                      or FR.Opened = 'true' then (
                                                      select Top 1 T.Action from 
                                                      (

                                                          select 
                                                                  Id as EmailJobRecipientID
                                                                  ,'Opened' as Action
                                                                  , UpdateDate as DateAdded 
                                                                  from dbo.EmailJob_Recipient where Id = FR.Id and FR.Opened = 1

                                                          union

                                                          select 
                                                                  Id as EmailJobRecipientID
                                                                  , 'Received' as Action
                                                                  , UpdateDate as DateAdded 
                                                                  from dbo.EmailJob_Recipient where Id = FR.Id and FR.[Sent] = 'true' and FR.DSNed = 'false'

                                                          union

                                                          select 
                                                                  S.EmailJobRecipientID
                                                                  ,'Clicked - ' + coalesce(P.PageName, S.URL) as Action
                                                                  , s.RequestDate as DateAdded 
                                                                  from dbo.Stats S 
                                                                      inner join EmailJob_Recipient ERD on ERD.Id = S.EmailJobRecipientID
                                                                      left outer join dbo.SitePages P on P.ID = S.PageID
                                                          union

                                                          select 
                                                                  EmailJobRecipientID
                                                                  ,'Donation' as Action
                                                                  , DateAdded 
                                                                  from dbo.DonationTransactions DT where FR.Id = DT.EmailJobRecipientID and DT.ISDELETED = 0 and DT.Status = 1

                                                          union

                                                          select 
                                                                  EmailJobRecipientID
                                                                  , 'Event registration' as Action
                                                                  , DateAdded 
                                                                  from dbo.EventTransactions ET where FR.Id = ET.EmailJobRecipientID and ET.ISDELETED = 0 and ET.Status = 1

                                                          union

                                                          select 
                                                                  EmailJobRecipientID
                                                                  , 'Membership' as Action
                                                                  , DateAdded 
                                                                  from dbo.MembershipTransactions MT where FR.Id = MT.EmailJobRecipientID and MT.ISDELETED = 0 and MT.Status = 1

                                                      ) T where FR.Id = T.EmailJobRecipientID order by T.DateAdded desc
                                                  )                                              
              else '' end as [Action]

      from CTE_FilteredResult as FR
                left outer join (
                              select 
                                      EmailJobRecipientID
                                      ,count(EmailJobRecipientID) as Clicks 
                                      from dbo.Stats group by EmailJobRecipientID
                          ) S on S.EmailJobRecipientID = FR.ID
          left outer join (
                          select ET.EmailJobRecipientID as EmailJobRecipientID,Sum(isnull(ET.Transactions,0)) as Transactions from 
                                          (              
                                                          select    EmailJobRecipientID , count(EmailJobRecipientID) as Transactions from dbo.DonationTransactions with (nolock) where ISDELETED = 0 and Status = 1 group by EmailJobRecipientID
                                                          union all
                                                          select    EmailJobRecipientID , count(EmailJobRecipientID) as Transactions from dbo.EventTransactions with (nolock) where ISDELETED = 0 and Status = 1 group by EmailJobRecipientID
                                                          union all
                                                          select EmailJobRecipientID, count(EmailJobRecipientID) as Transactions from dbo.MembershipTransactions with (nolock) where ISDELETED = 0 and Status = 1 group by EmailJobRecipientID                                                                                                                       
                                          ) as ET group by ET.EmailJobRecipientID 
          ) AS T on T.EmailJobRecipientID = FR.ID
          left outer join (select BOP.ID, CO.SEQUENCEID, CO.NAME, CO.LOOKUPID  from dbo.BackOfficeSystemPeople BOP
                                       inner join dbo.Constituent CO on CO.SEQUENCEID = BOP.BackofficeRecordID
                                       where CO.ISINACTIVE = 0) as BP on FR.BackOfficeSystemPeopleID = BP.ID

          where FR.EmailID =@EmailID
          order by FR.EmailAddress;
end