USP_GET_EMAIL_JOB_DETAILS

CREATE procedure dbo.USP_GET_EMAIL_JOB_DETAILS
(
  @EmailID int = 0,
  @Filter varchar(100) = '',
  @Page int = 1,
  @ItemCount int = 10,
  @TransactionType int = 1, 
  @BounceMessage nvarchar(200) =NULL,
  @BounceType nvarchar(10) =0,
  @EmailAddress nvarchar(100) =NULL,
  @Name nvarchar(100) =NULL,
  @NotSentFilter int =0
)
as
BEGIN
    set nocount on
      --Section Email Stats

            select max(clicks) as Clicks, StatsInternal.EmailJobRecipientID,'Clicked - ' + max(coalesce(SP.PageName, ST.URL)) as PageClicked into #stats
                                                 from (select count(1) as Clicks, ST.EmailJobRecipientID, max(ST.StatsID) as statsid
                                                              from [EmailLink] EL with (nolock) 
                                                                    left outer join Stats ST with (nolock) on (ST.pageid = EL.pid and ST.sourceid = EL.emailid and ST.sourcetypeid = 1 and EL.PID is NOT NULL)
                                                                                or (ST.URL = EL.URL and ST.sourceid = EL.emailid  and ST.sourcetypeid = 1 and EL.[URL] is not null)
                                                                                or (ST.DocumentId = EL.DocumentID and ST.sourceid = EL.emailid  and ST.sourcetypeid = 0 and EL.[DocumentID] is not null)
                                                              inner join EmailJob_Recipient EJR with (nolock) on EJR.id = ST.EmailJobRecipientID
                                                              where EJR.EmailID = @EmailId
                                                              group by EmailJobRecipientID
                                                        ) StatsInternal 
                                                  inner join Stats ST with (nolock) on ST.StatsID = StatsInternal.statsid
                                                  left outer join SitePages SP with (nolock) on SP.ID = ST.PageID
                                                  group by StatsInternal.emailJobRecipientID 
      --Section Email Stats end


      --Section Email Transactions

                          if OBJECT_ID('tempdb..#TRANSACTIONS') IS NOT NULL
                       drop table #TRANSACTIONS
                 select * into #TRANSACTIONS from(
                          select tr.EmailJobRecipientID , count(tr.EmailJobRecipientID) as Transactions, 'Donation' as TransactionType, max(TR.DateAdded) as DateAdded  
                                from dbo.DonationTransactions TR with (nolock)  
                                inner join EmailJob_Recipient EJR with (nolock) on TR.EmailJobRecipientID = EJR.ID 
                                where EJR.EmailID = @EmailID and ISDELETED = 0 and Status= 1
                                group by EmailJobRecipientID 
                                union all 
                select tr.EmailJobRecipientID , count(tr.EmailJobRecipientID) as Transactions, 'Event registration' as TransactionType ,max(TR.DateAdded) as DateAdded  
                                 from dbo.EventTransactions TR with (nolock)  
                                 inner join EmailJob_Recipient EJR with (nolock) on TR.EmailJobRecipientID = EJR.ID 
                                 where EJR.EmailID = @EmailID and ISDELETED = 0 and Status= 1
                                 group by EmailJobRecipientID
                                 union all 
               select tr.EmailJobRecipientID, count(tr.EmailJobRecipientID) as Transactions, 'Membership' as TransactionType,max(TR.DateAdded) as DateAdded  
                                 from dbo.MembershipTransactions TR with (nolock)  
                                 inner join EmailJob_Recipient EJR with (nolock) on TR.EmailJobRecipientID = EJR.ID
                                 where EJR.EmailID = @EmailID and ISDELETED = 0 and Status= 1
                                 group by EmailJobRecipientID )  AS AllEmailTransactions

           if OBJECT_ID('tempdb..#EMAILTRANSACTIONS') IS NOT NULL
              drop table #EMAILTRANSACTIONS

            select  Transactions as TotalTransactions,EmailJobRecipientID, TransactionType, DateAdded  into #EMAILTRANSACTIONS from
            (
            select tr.EmailJobRecipientID, dta.Transactions, tr.TransactionType, tr.DateAdded
            from (
               select EmailJobRecipientID,SUM(ISNULL(Transactions,0)) as Transactions, max(dateadded) as DateAdded 
               from #transactions group by EmailJobRecipientID
            ) as dta inner join #TRANSACTIONS as tr on tr.EmailJobRecipientID = dta.EmailJobRecipientID and tr.dateadded = dta.dateadded) AS tmpdata 

        -- Section Email transactions ends


        declare @EmailSubject AS nvarchar(4000);
        declare @EmailSendAfterDate AS DateTime;

        select @EmailSubject = E.[Subject], @EmailSendAfterDate = E.SendAfterDate
                from dbo.Email E with (nolock) where E.ID = @EmailId; 

      --Section Email Job Recipient starts

       select ER.EmailID, 
              isNull(EJR.SentDate, @EmailSendAfterDate) as SendDate, 
              @EmailSubject as EmailSubject, 
              ER.EmailAddress     as EmailAddress, 
              ER.DisplayName as EmailDisplayName, 
              ER.OptOut as Optout, 
                  ER.BackOfficeSystemPeopleID as BackOfficeSystemPeopleID,
              case when @TransactionType = -1 and ( ER.OptOut = 'true') then 'OptedOut'
                   when (@TransactionType = 0 and EJR.DSNed = 'true') and 
                          (
                              (@BounceMessage  is null or  EJR.StatusReport like '%' + @BounceMessage + '%')  and   
                               ( 1 = case when  @BounceType = 0 and EJR.RecentDSNCategory <> 112 then 1  
                                          when  @BounceType = 1 and EJR.RecentDSNCategory = 2 then 1
                                          when  @BounceType = 2 and EJR.RecentDSNCategory = 108 then 1 
                                          when  @BounceType = 3 and EJR.RecentDSNCategory = 109 then 1
                                          when  @BounceType = 4 and EJR.RecentDSNCategory not in (2, 108, 109, 112) then 1
                                                            end
                               ) and
                             (@EmailAddress        is null   or ER.EmailAddress like '%' + @EmailAddress + '%') and
                             (@Name        is null   or ER.DisplayName  like '%' + @Name + '%')
                          )
                          then 'Bounced'                                        
                   when @TransactionType = 1 and EJR.[Sent] = 'true' and EJR.DSNed = 'false' then 'Received'     
                   when @TransactionType = 2 and EJR.Opened = 'true'                         then 'Opened'            
                   when @TransactionType = 3    and ST.Clicks > 0 then ST.PageClicked
                   when @TransactionType = 4    and ETR.Totaltransactions > 0 and EJR.[Sent] = 'true' then ETR.transactiontype
                   when (@TransactionType = 6    and ((EJR.DSNed = 'true' and EJR.Sent ='true') or ER.InvalidAddress = 'true' or ER.InvalidAccount ='true' or ER.OptOut ='true' or isnull(ER.Inactive,0) ='true'))
                             and      (1 = case         when   @NotSentFilter = 0 then 1  
                                                     when   @NotSentFilter = 1       and   ER.InvalidAddress  ='true'             then 1 --No email address

                                                     when   @NotSentFilter = 2       and   ER.InvalidAccount  ='true'       then 1 --Invalid

                                                     when   @NotSentFilter = 3       and   EJR.DSNed  ='true' and EJR.RecentDSNCategory <> 112  then 1
                                                     when   @NotSentFilter = 4       and   ER.OptOut                 ='true'                    then 1 
                                                     when   @NotSentFilter = 5       and   isnull(ER.Inactive,0)    ='true'  and ( ER.OptOut <> 'true' and ER.InvalidAccount <> 1)                     then 1 
                                                     when   @NotSentFilter = 6       and   EJR.DSNed  ='true' and EJR.RecentDSNCategory = 112  then 1
                                                     when   @NotSentFilter = 7       and   EJR.DSNed  ='true' and EJR.RecentDSNCategory = 2  then 1
                                                     when   @NotSentFilter = 8       and   EJR.DSNed  ='true' and EJR.RecentDSNCategory = 108  then 1
                                                     when   @NotSentFilter = 9       and   EJR.DSNed  ='true' and EJR.RecentDSNCategory = 109  then 1
                                                     when   @NotSentFilter = 10       and   EJR.DSNed  ='true' and EJR.RecentDSNCategory not in (2,108,109,112)  then 1
                                                     else 0
                                                     end
                                        ) and 
                                        (@EmailAddress  is null       or ER.EmailAddress like '%' + @EmailAddress + '%') and 
                                        (@Name                 is null             or ER.DisplayName  like '%' + @Name + '%')

                           then 'Not Sent'
                  when @TransactionType =7 and EJR.DSNed  ='true' and EJR.RecentDSNCategory = 112 then 'Spam Complaint'
                           else '' end as [ActionTaken],                          
             case  when ER.InvalidAddress =1  then 'No email address'
                   when ER.InvalidAccount = 1 then 'Invalid email address'
                   when EJR.DSNed =1   and EJR.RecentDSNCategory = 2    then 'Hard bounce' 
                   when EJR.DSNed =1   and EJR.RecentDSNCategory = 108    then 'Previously marked as hard bounce' 
                   when EJR.DSNed =1   and EJR.RecentDSNCategory = 109    then 'Previously marked as spam complaint' 
                   when EJR.DSNed =1    and EJR.RecentDSNCategory not in ( 2,108,109,112)   then 'Soft bounce'
                   when EJR.DSNed =1 and EJR.RecentDSNCategory = 112 then 'Spam complaint'
                   when ER.OptOut =1          then 'Opt-out'
                   when isnull(ER.Inactive,0) =1 and ( ER.OptOut <> 'true' and ER.InvalidAccount <> 1)   then 'Unengaged email address'                  
                   else '' End as NotSentType,
             case  when EJR.DSNed =1   and EJR.RecentDSNCategory = 2    then 'Hard bounce' 
                   when EJR.DSNed =1   and EJR.RecentDSNCategory = 108    then 'Previously marked as hard bounce' 
                   when EJR.DSNed =1   and EJR.RecentDSNCategory = 109    then 'Previously marked as spam complaint' 
                   when EJR.DSNed=1    and EJR.RecentDSNCategory not in ( 2,108,109,112)   then 'Soft bounce' 
                   else '' END as BounceType,
             EJR.StatusReport as BounceMessage,
             EJR.Opened as Opened
             into #emailjobrecipient
             from email_recipient ER with (nolock)
             left outer join emailJob_recipient EJR  with (nolock) on ER.ID = EJR.EmailRecipientID
             left outer join #stats ST on ST.EmailJobRecipientID = EJR.ID
             left outer join #emailtransactions ETR on  EJR.ID = ETR.EmailJobRecipientID
             where  ER.EmailID = @EmailId and (@EmailAddress is null or ER.EmailAddress like '%'+ @EmailAddress + '%' )

       --Section Email Job Recipient end


         --Section Backoffice starts

                   select ejr.BackOfficeSystemPeopleID AS ID, Max(CO.SEQUENCEID) as SEQUENCEID, max(CO.NAME) as NAME, max(CO.LOOKUPID) as LOOKUPID, case when max(CU.ID) is null then 0 else 1 end as IsLinkedUser into #bosp 
                                     from #emailjobrecipient ejr
                                                           inner join dbo.BackOfficeSystemPeople BOP with (nolock) on BOP.ID = ejr.BackOfficeSystemPeopleID
                                     inner join dbo.Constituent CO with (nolock) on CO.SEQUENCEID = BOP.BackofficeRecordID
                                     left join  dbo.BackOfficeSystemUsers as BOU with (nolock) On BOP.Id = BOU.BackofficePeopleID
                                     left join ClientUsers as CU with (nolock) on CU.ID = BOU.ClientUsersID
                                                           where CO.ISINACTIVE = 0  and EJR.EmailID = @EmailId
                                                           group by ejr.BackOfficeSystemPeopleID
         --Section Backoffice end


begin try 
  if @ItemCount > -1 begin 

          declare @Offset int = (@Page * @ItemCount) - @ItemCount;

        select FilteredResult.* from (
                                                          select SendDate,
                                               EmailSubject, 
                                               EmailAddress, 
                                               EmailDisplayName,
                                                                     count(1) over()     as TotalRecordCount,
                                                                     case when BOSP.IsLinkedUser = 1 then BOSP.SEQUENCEID else NULL end as SequenceId,
                                                                     case when BOSP.IsLinkedUser = 1 then BOSP.NAME else NULL end as Name,
                                                                     Isnull(IsLinkedUser,0) as IsLinkedUser,  
                                                                     BounceType, 
                                               BounceMessage, 
                                               ActionTaken as [Action],
                                                                     NotSentType,
                                                                     ROW_NUMBER() OVER (ORDER BY EJR.EmailAddress) AS RowNum
                                                                     from #emailjobrecipient EJR 
                                                                     left outer join #bosp BOSP on BOSP.ID = ejr.BackOfficeSystemPeopleID
                                               where (ActionTaken <> '') and
                                                                             (@Filter = ''  or 
                                                                                 EmailAddress like '%' + @Filter + '%' or 
                                                                                 EmailDisplayName  like '%' + @Filter + '%' or 
                                                                                 ActionTaken like '%' + @Filter + '%'
                                                      )

                                                           ) AS FilteredResult
                                     where FilteredResult.RowNum BETWEEN @offset+1 AND @ItemCount*(@Page)
  end    

Else Begin -- In Case of export to CSV we need all data with some more conditions


          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;

          select    EmailID,
                      SendDate,
                  EmailSubject,
                      @EmailListName as EmailListName, 
                  EmailAddress, 
                  EmailDisplayName,
                  case when BOSP.IsLinkedUser = 1 then BOSP.SEQUENCEID else NULL end as SequenceId,
                  BOSP.LOOKUPID as LookupID,
                      EmailDisplayName as DisplayName, 
                      Isnull(IsLinkedUser,0) as IsLinkedUser,  
                      BounceType, 
                      BounceMessage, 
                      ActionTaken as [Action], NotSentType
                    from #emailjobrecipient EJR 
                    left outer join #bosp BOSP on BOSP.ID = ejr.BackOfficeSystemPeopleID
                  where (ActionTaken <> '') and
                            (@Filter = ''  or 
                         EmailAddress like '%' + @Filter + '%' or 
                         EmailDisplayName  like '%' + @Filter + '%' or 
                         ActionTaken like '%' + @Filter + '%'
                        )
                    order by EmailAddress
        end
    end try
    --If error occurs then drop all temp tables

    begin catch
      drop table #emailjobrecipient
      drop table #bosp
      drop table #stats
      drop table #emailtransactions
      drop table #transactions
    end catch

      drop table #emailjobrecipient
      drop table #bosp
      drop table #stats
      drop table #emailtransactions
      drop table #transactions
END