spREINT_GetEmailRecipientsInfo

CREATE procedure dbo.spREINT_GetEmailRecipientsInfo
(
@MaxRecords int,
@DownloadStartpoint nvarchar(50),
@LastID as int
)
as
begin

    create table #Result(ejrid int, updatedate datetime, emailid int, bospid int, reid int, userid int, ejrUpdatedate datetime)

      insert into #result(ejrid, updatedate, emailid, bospid, userid, reid, ejrUpdatedate)
      SELECT top(@MaxRecords) ejr.id,
      coalesce(EJRD.DONATIONDATE,ejr.updatedate) UpdateDate,
      ejr.emailid,
      ejr.backofficesystempeopleid,
      ejr.userid,
      bosp.backofficerecordid,
      coalesce(ejr.updatedate, EJRD.DONATIONDATE) EjrUpdateDate
      FROM dbo.emailjob_recipient ejr with (nolock)
      INNER JOIN dbo.backofficesystempeople BOSP ON ejr.BackOfficeSystemPeopleID = BOSP.ID and BOSP.BackOfficeSystemID = 0
      LEFT JOIN dbo.EMAILJOB_RECIPIENT_DONATED EJRD on ejr.ID = EJRD.EMAILJOBRECIPIENTID
      WHERE (BOSP.BackofficeRecordID <> 0 and ejr.UpdateDate >= @DownloadStartpoint and NOT (ejr.UpdateDate = @DownloadStartpoint and ejr.ID <= @LastID))
             or EJRD.ID is not null
      order by ejr.UpdateDate, ejr.ID

      delete from dbo.EMAILJOB_RECIPIENT_DONATED where EMAILJOBRECIPIENTID in (select ejrid from #Result)        

      SELECT r.ejrid id,
      r.reid AS "REID",
      e.name,
      r.emailID AS "EmailID",
      e.emailTemplateID AS "EmailTemplateID",
      e.subject,
      ejr.SentDate,
      case when ejr.DSNed =1 and  ejr.RecentDSNCategory <> 112 then 1 else 0 end AS "BOUNCED",
      case 
        when ejr.DSNed=1 and ejr.RecentDSNCategory= 2 then 'Hard - '+convert(nvarchar(1000),ejr.statusreport)
        when ejr.DSNed=1 and ejr.RecentDSNCategory= 108 then 'Previously marked as hard bounce - '+convert(nvarchar(1000),ejr.statusreport)
        when ejr.DSNed=1 and ejr.RecentDSNCategory= 109 then 'Previously marked as spam complaint - '+convert(nvarchar(1000),ejr.statusreport)
        when ejr.DSNed=1 and ejr.RecentDSNCategory = 112 then 'Spam complaint - '+convert(nvarchar(1000),ejr.statusreport)
        when ejr.DSNed=1 and ejr.RecentDSNCategory not in (2,108,109,112) then 'Soft - '+convert(nvarchar(1000),ejr.statusreport)
        else convert(nvarchar(1000),ejr.statusreport)
     end AS "BOUNCETEXT",
      ejr.Opened as "OPENED",
      r.UpdateDate,
      ejr.emailaddress,
      CASE WHEN exists (select * from stats where ejr.ID = stats.EmailJobRecipientID) THEN 1 ELSE 0 END as "ClickedThrough",
      CASE WHEN exists (select * FROM dbo.DonationTransactions dtran WHERE dtran.EmailJobRecipientID = ejr.id AND dtran.EmailID = e.id AND dtran.Status = 1)
      THEN 1
      ELSE 0
      END AS "Donated",
      '' AS "URL",
      e.ClientSitesID,
      case when ejr.DSNed =1 and  ejr.RecentDSNCategory = 112 then 1 else 0 end AS "SpamComplaint",
      ejr.Globaloptedout AS "OptedOut", r.EjrUpdateDate
      FROM #Result r
      INNER JOIN dbo.EmailJob_Recipient ejr with (nolock) on ejr.id = r.ejrid
      INNER JOIN dbo.email e ON e.id = ejr.EmailID
      ORDER BY r.UpdateDate, r.ejrID ASC

      drop table #result
end