/// <summary>
/// Returns query for audience of campaign that should be moved to bulk email.
/// </summary>
/// <param name="userConnection">Instance of the <see cref="Terrasoft.Core.UserConnection"/>.</param>
/// <param name="campaignId">Unique identifier of the Campaign.</param>
/// <param name="bulkEmailId">Unique identifier of the BulkEmail.</param>
/// <param name="stepId">Unique identifier of the CampaignStep.</param>
public static Select GetCampaignAudienceToBulkEmailSelect(UserConnection userConnection, Guid campaignId,
Guid bulkEmailId, Guid stepId)
{
var bulkEmailRId = BulkEmailQueryHelper.GetBulkEmailRId(bulkEmailId, userConnection);
var unixTimestamp = Utilities.ConvertDateTimeToTimestamp(DateTime.UtcNow);
var unsubscribeSubSelect = GetBulkEmailUnsubscribeSelect(userConnection, bulkEmailId);
var existingRecipientsSelect = GetExistingRecipientsSelect(userConnection);
var mainQuery = new Select(userConnection)
.Column("main", "BulkEmailRId")
.Column("main", "ContactRId")
.Column("main", "EmailAddress")
.Column(Column.Const(unixTimestamp)).As("Timestamp")
.From(new Select(userConnection)
.Column(Column.Const(bulkEmailRId)).As("BulkEmailRId")
.Column("C", "Id").As("ContactId")
.Column("C", "RId").As("ContactRId")
.Column("C", "Email").As("EmailAddress")
.Column(Column.Const(unixTimestamp)).As("Timestamp")
.From("CampaignTarget").As("CT")
.InnerJoin("Contact").As("C").On("CT", "ContactId").IsEqual("C", "Id")
.Where("CT", "CampaignId").IsEqual(Column.Parameter(campaignId))
.And("CT", "NextStepId").IsEqual(Column.Parameter(stepId)) as Select).As("main")
.Where().Not().Exists(existingRecipientsSelect)
.And().Not().Exists(unsubscribeSubSelect) as Select;
mainQuery.SpecifyNoLockHints();
return(mainQuery);
}
private Select ApplyBulkEmailInfoToAudienceSelect(Select audienceSelect, Guid bulkEmailId, Guid sessionUId)
{
var bulkEmailRId = BulkEmailQueryHelper.GetBulkEmailRId(bulkEmailId, _userConnection);
var select = audienceSelect
.Column(Column.Parameter(bulkEmailRId)).As("BulkEmailRId")
.Column(Column.Parameter(sessionUId)).As("SessionUId")
.Column(Column.Const(false)).As("IsSent")
.Column(Column.Parameter(Utilities.ConvertDateTimeToTimestamp(DateTime.UtcNow))).As("Timestamp");
return(select);
}
