[Pre-Alpha] Send Communications job missing scheduled communications

Description

When the Send Communications job is running, there are communications with a Future Send Date Time that is valid and they should be sent but they are not. Using observability to look at the SQL generated by the job, it appears that the dates being used to find recipients are not correctly calculating start and end dates to choose communications.

Actual Behavior

Communications with an approval date more than 3 days in the past and a FutureSendDateTime of today, do not get sent.

Communications with a review date less than 3 days in the past and a FutureSendDateTime of today do get sent.

This is with the default Send Communications job settings in place.

Observability provides the following query being run as a part of the job.

DECLARE @p__linq__0 DATETIME = '11/15/2024 1:40:00 PM' 
DECLARE @p__linq__1 DATETIME = '11/8/2024 1:40:00 PM' 
DECLARE @p__linq__2 DATETIME = '11/15/2024 1:40:00 PM' 
DECLARE @p__linq__3 DATETIME = '11/8/2024 1:40:00 PM'

SELECT TOP 10 [Extent1].[Id] AS [Id]
     , [Extent1].[PersonAliasId] AS [PersonAliasId]
     , [Extent1].[CommunicationId] AS [CommunicationId]
     , [Extent1].[MediumEntityTypeId] AS [MediumEntityTypeId]
     , [Extent1].[Status] AS [Status]
     , [Extent1].[StatusNote] AS [StatusNote]
     , [Extent1].[SendDateTime] AS [SendDateTime]
     , [Extent1].[OpenedDateTime] AS [OpenedDateTime]
     , [Extent1].[OpenedClient] AS [OpenedClient]
     , [Extent1].[TransportEntityTypeName] AS [TransportEntityTypeName]
     , [Extent1].[UniqueMessageId] AS [UniqueMessageId]
     , [Extent1].[ResponseCode] AS [ResponseCode]
     , [Extent1].[SentMessage] AS [SentMessage]
     , [Extent1].[PersonalDeviceId] AS [PersonalDeviceId]
     , [Extent1].[AdditionalMergeValuesJson] AS [AdditionalMergeValuesJson]
     , [Extent1].[CreatedDateTime] AS [CreatedDateTime]
     , [Extent1].[ModifiedDateTime] AS [ModifiedDateTime]
     , [Extent1].[CreatedByPersonAliasId] AS [CreatedByPersonAliasId]
     , [Extent1].[ModifiedByPersonAliasId] AS [ModifiedByPersonAliasId]
     , [Extent1].[Guid] AS [Guid]
     , [Extent1].[ForeignId] AS [ForeignId]
     , [Extent1].[ForeignGuid] AS [ForeignGuid]
     , [Extent1].[ForeignKey] AS [ForeignKey] 
FROM [dbo].[CommunicationRecipient] AS [Extent1] 
INNER JOIN [dbo].[Communication] AS [Extent2] ON [Extent1].[CommunicationId] = [Extent2].[Id] 
WHERE 1=1
    AND (3 = [Extent2].[Status]) 
    AND (0 = [Extent1].[Status]) 

    AND ((([Extent2].[FutureSendDateTime] IS NULL) 
            AND ([Extent2].[ReviewedDateTime] IS NOT NULL) 
            AND ([Extent2].[ReviewedDateTime] < @p__linq__0) -- 11/15/2024 1:40:00 PM
            AND ([Extent2].[ReviewedDateTime] > @p__linq__1)) -- 11/8/2024 1:40:00 PM
        OR (([Extent2].[FutureSendDateTime] IS NOT NULL) 
        AND ([Extent2].[FutureSendDateTime] < @p__linq__2) -- 11/15/2024 1:40:00 PM
        AND ([Extent2].[FutureSendDateTime] > @p__linq__3))) --11/8/2024 1:40:00 PM

This was for an instance of the job that ran on 11/18/2024 at 1:40:00 PM.

@p_linq_0 should be "11/18/2024 1:36:00 PM" - the current date time minus the 4 minute delay period @p_linq_1 shoud be "1/15/2024 1:40:00 PM" - the current date time minus the 3 day expiration period @p_linq_2 should be "11/18/2024 1:40:00 PM" - the current date time @p_linq_3 should be "1/15/2024 1:40:00 PM" - the current date time minus the 3 day expiration period

Without SQL access in pre-alpha it was not possible to recreate there, we had to recreate in our instance running this pre-alpha version.

Expected Behavior

An approved communication with a future send date time should send when that date time is met, regardless of how far back the reviewed date time is.

Steps to Reproduce

Create a communication and schedule it to happen in the future.
Use SQL to put the reviewed date time further in the past than the Expiration Period set for the job.
Wait for the job to run, or manually run it once the time future send date and time have passed.
The communication will not be sent.

Issue Confirmation

[X] Perform a search on the Github Issues to see if your bug or enhancement is already reported.
[X] Reproduced the problem on a fresh install or on the demo site.

Rock Version

1.17.0.31

Client Culture Setting

en-US

SparkDevNetwork / Rock