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)
{
}