USP_DATALIST_UNDELIVEREDEMAIL

create procedure dbo.USP_DATALIST_UNDELIVEREDEMAIL(
    @DATAWINDOWOPEN datetime = null, 
    @DATAWINDOWCLOSE datetime = null, 
    @FROMDATETIME datetime = null output
)
as
set nocount on;

declare @CURRENTUTC datetime = getutcdate()

if @FROMDATETIME is null 
    SET @FROMDATETIME = DATEADD(MONTH, -1, @CURRENTUTC)--should keep the default in sync with the corresponding summary view

else 
    SET @FROMDATETIME = dbo.UFN_TIMEZONEENTRY_GETUTCDATEFROMSYSTEMDEFAULTDATE(@FROMDATETIME)

select--some of these fields are included just to serve the email alert or rss features

    EmailJob.GUID, 
    dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEFROMUTC(EmailJob.CreateDate) as CreateDate,
    case Email.[Type]
        when 1 then 'acknowledgement'
        when 16 then 'membership renewal'--BBDM, but for Altru we can label it more specifically

    end as TypeLabel,
    Email.Name as MessageName,
    EmailJob.[Status],
    case 
        when (EmailJob.[Status] = 2) then 'Prepared'
        when (EmailJob.[Status] = 3) then 'Processing'
        when (EmailJob.[Status] = 5) then 'No Valid Recipients'
        when (EmailJob.[Status] = 12) then 'Processed'
        when (EmailJob.[Status] = 11) then 'Uploading'
        when (EmailJob.[Status] = 13) then 'Upload failed'
        when (EmailJob.[Status] = 8) then 'Local exception'
        when (EmailJob.[Status] = 4) then 'Accepted'
        when (EmailJob.[Status] = 14) then 'Server exception'
        when (EmailJob.[Status] = 7) and (Email.[Type] = 1) and (EmailJob_Recipient.DSNed = 1) then 'Bounced'--"Delivery Status Notification"

    end as StatusLabel,--could have joined to EmailJobStatus for these, but there's no other reason to join to it, and docs wanted to tweak a few terms

    case Email.[Type]
        when 1 then isnull(CONSTITUENT.NAME, convert(xml, XMLDataSetMergeData).value('(/Recipients/R/N) [1]', 'nvarchar(154)')) 
        when 16 then NULL
    end as RecipientName,
    case Email.[Type]
        when 1 then isnull(EmailJob_Recipient.EmailAddress, convert(xml, XMLDataSetMergeData).value('(/Recipients/R/E) [1]', 'nvarchar(255)')) 
        when 16 then NULL
    end as EmailAddress,
    case Email.[Type]
        when 1 then ' to ' + isnull(EmailJob_Recipient.EmailAddress, convert(xml, XMLDataSetMergeData).value('(/Recipients/R/E) [1]', 'nvarchar(255)')) 
        when 16 then ''
    end as AddresseeLabel,    
    case Email.[Type]
        when 1 then coalesce(EmailJob_Recipient.UpdateDate, EmailJob_Recipient.SentDate, EmailJob_Recipient.MessageDate, dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEFROMUTC(EmailJob.CreateDate))
        when 16 then NULL
    end as UpdateDate
from EmailJob 
inner join Email 
    on EmailJob.EmailID = Email.ID
left outer join EmailJob_Recipient
    on EmailJob.ID = EmailJob_Recipient.EmailJobID 
    and Email.[Type] = 1
left outer join BackOfficeSystemPeople
    on EmailJob_Recipient.BackOfficeSystemPeopleID = BackOfficeSystemPeople.ID 
    and Email.[Type] = 1
left outer join CONSTITUENT
    on BackOfficeSystemPeople.BackofficeRecordID = CONSTITUENT.SEQUENCEID 
    and BackOfficeSystemPeople.BackOfficeSystemID = 0 
    and Email.[Type] = 1
where (Email.[Type] in (1, 16))--1 = Acknowledgement, 16 = BBDM (for membership renewals)

and (EmailJob.CreateDate > @FROMDATETIME)
and (((@DATAWINDOWOPEN is null) or (@DATAWINDOWCLOSE is null)) 
    or (DATEADD(MINUTE, DATEPART(TZOFFSET, SYSDATETIMEOFFSET()), EmailJob.CreateDate) between DATEADD(HOUR, -1, @DATAWINDOWOPEN) and @DATAWINDOWCLOSE)--set the @DATAWINDOWOPEN adjustment to the maximum of the per-status maximum tolerance is below...email alert jobs scheduled more frequently than this may send some redundant information.

)
and (((EmailJob.[Status] = 2) and (EmailJob.CreateDate < DATEADD(MINUTE, -10, @CURRENTUTC)))--Prepared

    or ((EmailJob.[Status] = 3) and (EmailJob.CreateDate < DATEADD(MINUTE, -15, @CURRENTUTC)))--Processing

    or (EmailJob.[Status] = 5)--No Valid Recipients

    or ((EmailJob.[Status] = 12) and (EmailJob.CreateDate < DATEADD(MINUTE, -20, @CURRENTUTC)))--Processed

    or ((EmailJob.[Status] = 11) and (EmailJob.CreateDate < DATEADD(HOUR, -1, @CURRENTUTC)))--Uploading

    or (EmailJob.[Status] = 13)--Upload failed

    or (EmailJob.[Status] = 8)--Local exception

    or ((EmailJob.[Status] = 4) and (EmailJob.CreateDate < DATEADD(HOUR, -1, @CURRENTUTC)))--Accepted

    or (EmailJob.[Status] = 14)--Server exception

    or ((EmailJob.[Status] = 7) and (Email.[Type] = 1) and (EmailJob_Recipient.DSNed = 1))--Bounced (Delivery Status Notification)

)
order by EmailJob.CreateDate desc