USP_GET_EMAILADDRESS_ACTION_HISTORY

CREATE procedure dbo.USP_GET_EMAILADDRESS_ACTION_HISTORY
(
@EMAILADDRESS varchar(50),
@EMAILID int,
@EMAILSUBJECTFILTER varchar(100) = null,
@FROMDATEFILTER datetime =null,
@TODATEFILTER datetime =null,
@PAGE int = 1,
@ITEMCOUNT int = 10
)
as
begin
    -- Create a table that will hold all of the return details

    create table #EMAILINTERACTION_TABLE (ActionDescription nvarchar(2093), ActionDate datetime, EmailJobId int, ActionType int, TransactionAmount money,EmailSubject nvarchar(4000),sentDate datetime);
    create table #EMAILJOBRECIPIENT_DETAILS(EmailJobRecipientId int, EmailJobId int, IsSent int,EmailSubject nvarchar(4000),sentDate datetime); 
  declare @Offset int = (@PAGE * @ITEMCOUNT) - @ITEMCOUNT;

  -- Get the email job recipient IDs of the email address

    insert into #EMAILJOBRECIPIENT_DETAILS
        select er.ID, er.EmailJobId, er.Sent,EM.[Subject],ej.CreateDate
        from dbo.EmailJob_Recipient er
        inner join EmailJob_Recipient ejr on er.userid=ejr.userid and ejr.emailAddress=er.emailaddress
        inner join dbo.EmailJob EJ on EJ.Id = er.EmailJobId
        inner join dbo.Email EM on EJ.EmailID=EM.ID
        where ejr.EmailAddress = @EMAILADDRESS and ejr.EmailId = @EMAILID
         and   (@FROMDATEFILTER is null or EJ.CreateDate >= @FROMDATEFILTER)
         and   (@TODATEFILTER is null or EJ.CreateDate < @TODATEFILTER)

  -- Get the bounced email jobs and the date they were bounced

  -- ActionType for Bounced = 0

insert into #EMAILINTERACTION_TABLE
    select
    case when DSNed = 1
        then 'Bounced'
     when Opened = 1 and ERD.IsSent = 1
        then  'Opened'
    end, 
    case when DSNed = 1
        then ER.SentDate
    when Opened = 1 and ERD.IsSent = 1    
        then OpenedDate 
    end, 
    ERD.EmailJobId, 
    case when DSNed = 1
        then 0
    when Opened = 1 and ERD.IsSent = 1
        then 2 
    end, 
    0,
    EM.Subject As EmailSubject,
    ERD.sentDate 
    from dbo.EmailJob_Recipient ER
    inner join dbo.Email EM on ER.EmailID=EM.ID
    inner join #EMAILJOBRECIPIENT_DETAILS ERD on ERD.EmailJobRecipientId = ER.ID
    where DSNed = 1 or (Opened = 1 and ERD.IsSent = 1)
    ;

    -- Determine which emails were clicked through using dbo.Stats

    -- Stats rows with an email recipient ID indicate that they were access via an email

  -- ActionType for Clicks = 3

    insert into #EMAILINTERACTION_TABLE
    select 
        'Clicked - ' + coalesce(P.PageName, S.URL), 
        S.RequestDate, 
        ERD.EmailJobId, 
        3, 
        0,
        ERD.EmailSubject ,
        ERD.sentDate 
    from dbo.Stats S
    inner join 
        #EMAILJOBRECIPIENT_DETAILS ERD on ERD.EmailJobRecipientId = S.EmailJobRecipientID
    left outer join 
        dbo.SitePages P on P.ID = S.PageID
    where 
        ERD.IsSent = 1;

    -- Get the transactions (donation, event, membership) related to the user's email jobs

    -- The action description for each transaction is formatted: <Transaction action> - $<gift amount>

      insert into #EMAILINTERACTION_TABLE
    select T.ActionDescription, T.DateAdded, T.EmailJobId, 4, T.GiftAmount,T.EmailSubject ,T.sentDate 
    from
    (
        -- For each type of transaction, we want to return what that specific transaction's action is

        select 
            DT.EmailJobRecipientID, 
            DT.GiftAmount, 
            'Donation' as ActionDescription, 
            DT.DateAdded,
            ERD.EmailSubject ,
            ERD.sentDate,
            EmailJobId  
        from 
            dbo.DonationTransactions DT
        inner join 
            #EMAILJOBRECIPIENT_DETAILS ERD on ERD.EmailJobRecipientId = DT.EmailJobRecipientID
        where 
            ERD.IsSent = 1
        union
        select 
            ET.EmailJobRecipientID, 
            ET.GiftAmount, 
            'Event registration' as ActionDescription, 
            ET.DateAdded,
            ERD.EmailSubject ,
            ERD.sentDate ,
            EmailJobId 
        from 
            dbo.EventTransactions ET
        inner join 
            #EMAILJOBRECIPIENT_DETAILS ERD on ERD.EmailJobRecipientId = ET.EmailJobRecipientID
        where 
            ERD.IsSent = 1
        union
        select 
            MT.EmailJobRecipientID, 
            MT.GiftAmount, 
            'Membership', 
            MT.DateAdded as ActionDescription,
            ERD.EmailSubject ,
            ERD.sentDate ,
            EmailJobId 
        from
            dbo.MembershipTransactions MT
        inner join 
            #EMAILJOBRECIPIENT_DETAILS ERD on ERD.EmailJobRecipientId = MT.EmailJobRecipientID
        where ERD.IsSent = 1
    ) T

    -- Return everything!

    select 
         count(1) over()    as TotalRecordCount,
             EI.ActionDescription as ActionDescription,
         EI.ActionDate as ActionDate,
         EI.ActionType as ActionType,
         EI.TransactionAmount as TransactionAmount,        
         @EMAILID as EmailId,
         EI.[EmailSubject] as [Subject],
         EI.SentDate as SentDate
    from 
        #EMAILINTERACTION_TABLE EI  
    where  
     (@EMAILSUBJECTFILTER  is null   or  EI.[EmailSubject] like '%' + @EMAILSUBJECTFILTER + '%')     
    order by ActionDate desc
  offset @Offset rows
    fetch next @ITEMCOUNT rows only

drop table #EMAILINTERACTION_TABLE
drop table #EMAILJOBRECIPIENT_DETAILS

end