/// <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); }