private Select GetMessageResponseSelectQuery(BulkEmailCountLimit rule, int bulkEmailRId) { var periodStartTimeStamp = Utilities.ConvertDateTimeToTimestamp(DateTime.UtcNow.AddDays(-rule.MailingLimitPeriod)); var audienceSubQuery = new Select(_userConnection) .Column("ContactRId") .From("MandrillRecipient") .Where("BulkEmailRId").IsEqual(Column.Parameter(bulkEmailRId)); if (_sessionId != Guid.Empty) { audienceSubQuery.And("SessionUId").IsEqual(Column.Parameter(_sessionId)); } var distinctSelectQuery = new Select(_userConnection) .Distinct() .Column("ContactRId") .Column("BulkEmailRId") .From("MandrillMessageResponse").As("mmr") .LeftOuterJoin("BulkEmailTarget").As("bet").On("bet", "MandrillId").IsEqual("mmr", "MandrillId") .Where("mmr", "ResponseCode").IsNotEqual(Column.Parameter((int)MailingResponseCode.CanceledCommunicationLimit)) .And("mmr", "TimeStamp").IsGreaterOrEqual(Column.Parameter(periodStartTimeStamp)) .And("bet", "MandrillId").IsNull() .And("mmr", "ContactRId").In(audienceSubQuery) as Select; var join = distinctSelectQuery.InnerJoin("BulkEmail") .On("BulkEmail", "RId").IsEqual("mmr", "BulkEmailRId") .And("BulkEmail", "IsSystemEmail").IsEqual(Column.Const(false)); AddBulkEmailFilter(rule, join); var selectQuery = new Select(_userConnection) .Column("Contact", "Id") .From(distinctSelectQuery).As("distinctSelect") .InnerJoin("Contact").On("Contact", "RId").IsEqual("distinctSelect", "ContactRId") as Select; return(selectQuery); }
private Select GetBulkEmailTargetSelectQuery(BulkEmailCountLimit rule, int bulkEmailRId) { var periodStartDateTime = DateTime.UtcNow.AddDays(-rule.MailingLimitPeriod); var audienceSubQuery = new Select(_userConnection) .Column("Contact", "Id") .From("MandrillRecipient") .InnerJoin("Contact").On("Contact", "RId").IsEqual("MandrillRecipient", "ContactRId") .Where("MandrillRecipient", "BulkEmailRId").IsEqual(Column.Parameter(bulkEmailRId)); if (_sessionId != Guid.Empty) { audienceSubQuery.And("SessionUId").IsEqual(Column.Parameter(_sessionId)); } var selectQuery = new Select(_userConnection) .Column("ContactId") .From("BulkEmailTarget").As("bet") .Where("bet", "BulkEmailResponseId").IsNotEqual(Column.Parameter(MailingConsts.BulkEmailResponseLimitedId)) .And("bet", "CreatedOn").IsGreaterOrEqual(Column.Parameter(periodStartDateTime)) .And("bet", "ContactId").In(audienceSubQuery) as Select; var join = selectQuery.InnerJoin("BulkEmail") .On("BulkEmail", "Id").IsEqual("bet", "BulkEmailId") .And("BulkEmail", "IsSystemEmail").IsEqual(Column.Const(false)); AddBulkEmailFilter(rule, join); return(selectQuery); }
/// <summary> /// Creates query to get contacts in current email for whom communication is restricted by given rule. /// Override this method to get custom query for restricted contacts. /// </summary> /// <param name="rule">Communication limit rule. Instance of <see cref="BulkEmailCountLimit"/>.</param> /// <param name="bulkEmailRId">Email identifier (RId field in BulkEmail).</param> /// <returns>Instance of <see cref="Select"/>. It should select single column Contact.Id.</returns> protected virtual Select GetContactSelectQuery(BulkEmailCountLimit rule, int bulkEmailRId) { var betSelect = GetBulkEmailTargetSelectQuery(rule, bulkEmailRId); var mmrSelect = GetMessageResponseSelectQuery(rule, bulkEmailRId); var rootSelect = new Select(_userConnection) .Column("ContactId") .From(betSelect.UnionAll(mmrSelect)).As("src") .GroupBy("ContactId") .Having(Func.Count("src", "ContactId")).IsGreaterOrEqual(Column.Const(rule.MailingLimitCount)) as Select; return(rootSelect); }
private static void AddBulkEmailFilter(BulkEmailCountLimit rule, Query join) { if (rule.EmailCategoryId != Guid.Empty || rule.EmailTypeId != Guid.Empty) { if (rule.EmailCategoryId != Guid.Empty) { join.And("BulkEmail", "CategoryId").IsEqual(Column.Parameter(rule.EmailCategoryId)); } if (rule.EmailTypeId != Guid.Empty) { join.And("BulkEmail", "TypeId").IsEqual(Column.Parameter(rule.EmailTypeId)); } } }
public BulkEmailCountLimit(BulkEmailCountLimit source) : base(source) { }