public void SubscriberQueries_SelectTest()
{
//параметры
var logger = new ShoutExceptionLogger();
SqlSubscriberQueries target = new SqlSubscriberQueries(logger, SignaloBotTestParameters.SqlConnetion);
var subscriberParameters = new SubscribtionParameters()
{
DeliveryType = SignaloBotTestParameters.ExistingDeliveryType,
CategoryID = SignaloBotTestParameters.ExistingCategoryID,
TopicID = SignaloBotTestParameters.ExistingSubscriptionTopicID,
CheckBlockedOfNDR = true,
CheckCategoryEnabled = true
};
var usersRange = new UsersRangeParameters<Guid>()
{
FromUserIDs = new List<Guid>()
{
SignaloBotTestParameters.ExistingUserID,
Guid.NewGuid()
}
};
//проверка
QueryResult<List<Subscriber<Guid>>> subscribers =
target.Select(subscriberParameters, usersRange).Result;
}
//методы
public IQueryable<Subscriber<Guid>> CreateQuery(SubscribtionParameters parameters
, UsersRangeParameters<Guid> usersRange, ClientDbContext context)
{
if(usersRange.UserIDRangeFromExcludingSelf != null
|| usersRange.UserIDRangeToIncludingSelf != null)
{
throw new NotImplementedException("Not supported for SQL DAL");
}
IQueryable<UserDeliveryTypeSettings<Guid>> typeQueryPart =
CreateDeliveryTypeQueryPart(parameters, usersRange, context);
if (parameters.SelectFromCategories)
{
IQueryable<UserCategorySettings<Guid>> categoryQueryPart = CreateCategoryQueryPart(parameters, context);
typeQueryPart = JoinWithCategories(parameters, typeQueryPart, categoryQueryPart);
}
if (parameters.SelectFromTopics)
{
IQueryable<UserTopicSettings<Guid>> topicQueryPart = CreateTopicQueryPart(parameters, context);
typeQueryPart = JoinWithTopics(parameters, typeQueryPart, topicQueryPart);
}
IQueryable<Subscriber<Guid>> subscribers = typeQueryPart.Select(d => new Subscriber<Guid>
{
UserID = d.UserID,
DeliveryType = d.DeliveryType,
Address = d.Address,
TimeZoneID = d.TimeZoneID,
Language = d.Language
});
if(usersRange.Limit != null)
{
subscribers = subscribers.Take(usersRange.Limit.Value);
}
return subscribers;
}
//DeliveryType
protected virtual IQueryable<UserDeliveryTypeSettings<Guid>> CreateDeliveryTypeQueryPart(
SubscribtionParameters parameters, UsersRangeParameters<Guid> usersRange, ClientDbContext context)
{
IQueryable<UserDeliveryTypeSettings<Guid>> query = context.UserDeliveryTypeSettings
.Where(p => p.Address != null);
if (parameters.DeliveryType != null)
{
query = query.Where(p => p.DeliveryType == parameters.DeliveryType.Value);
}
if (parameters.CheckBlockedOfNDR)
{
query = query.Where(p => p.IsBlockedOfNDR == false);
}
if (parameters.CheckDeliveryTypeEnabled)
{
query = query.Where(p => p.IsEnabled == true);
}
if (parameters.CheckDeliveryTypeLastSendDate)
{
query = query.Where(p => p.LastSendDateUtc == null ||
p.LastSendDateUtc < p.LastUserVisitUtc);
}
if (parameters.CheckDeliveryTypeSendCountNotGreater != null)
{
int sendCountLimitValue = parameters.CheckDeliveryTypeSendCountNotGreater.Value;
query = query.Where(p => p.SendCount <= sendCountLimitValue);
}
if (usersRange.FromUserIDs != null)
{
query = query.Where(p => usersRange.FromUserIDs.Contains(p.UserID));
}
return query;
}
protected virtual IQueryable<UserDeliveryTypeSettings<Guid>> JoinWithTopics(SubscribtionParameters parameters
, IQueryable<UserDeliveryTypeSettings<Guid>> typeQueryPart, IQueryable<UserTopicSettings<Guid>> topicQueryPart)
{
int categoryIDValue = parameters.CategoryID.Value;
string topicIDValue = parameters.TopicID;
IQueryable<UserDeliveryTypeSettings<Guid>> query = null;
if (parameters.CheckTopicLastSendDate)
{
query = from d in typeQueryPart
join t in topicQueryPart on
new
{
UserID = d.UserID,
CategoryID = categoryIDValue,
TopicID = topicIDValue
} equals new
{
UserID = t.UserID,
CategoryID = t.CategoryID,
TopicID = t.TopicID
}
where t.LastSendDateUtc == null
|| (d.LastUserVisitUtc != null
&& t.LastSendDateUtc < d.LastUserVisitUtc)
select d;
}
else
{
query = from d in typeQueryPart
join t in topicQueryPart on
new
{
UserID = d.UserID,
CategoryID = categoryIDValue,
TopicID = topicIDValue
} equals new
{
UserID = t.UserID,
CategoryID = t.CategoryID,
TopicID = t.TopicID
}
select d;
}
return query;
}
//Topic
protected virtual IQueryable<UserTopicSettings<Guid>> CreateTopicQueryPart(
SubscribtionParameters parameters, ClientDbContext context)
{
IQueryable<UserTopicSettings<Guid>> query = context.UserTopicSettings;
query = query.Where(p => p.IsDeleted == false);
if (parameters.CheckTopicEnabled)
{
query = query.Where(p => p.IsEnabled == true);
}
if (parameters.CheckTopicSendCountNotGreater != null)
{
int sendCountLimitValue = parameters.CheckTopicSendCountNotGreater.Value;
query = query.Where(p => p.SendCount <= sendCountLimitValue);
}
return query;
}
protected virtual IQueryable<UserDeliveryTypeSettings<Guid>> JoinWithCategories(
SubscribtionParameters parameters, IQueryable<UserDeliveryTypeSettings<Guid>> typeQueryPart
, IQueryable<UserCategorySettings<Guid>> categoryQueryPart)
{
int categoryIDValue = parameters.CategoryID.Value;
IQueryable<UserDeliveryTypeSettings<Guid>> query = null;
if (parameters.CheckCategoryLastSendDate)
{
query = from d in typeQueryPart
join c in categoryQueryPart on
new
{
UserID = d.UserID,
DeliveryType = d.DeliveryType,
CategoryID = categoryIDValue
} equals new
{
UserID = c.UserID,
DeliveryType = c.DeliveryType,
CategoryID = c.CategoryID
}
into gr
from catGroup in gr.DefaultIfEmpty()
where catGroup != null
&& (catGroup.LastSendDateUtc == null
|| (d.LastUserVisitUtc != null && catGroup.LastSendDateUtc < d.LastUserVisitUtc))
select d;
}
else
{
query = from d in typeQueryPart
join c in categoryQueryPart on
new
{
UserID = d.UserID,
DeliveryType = d.DeliveryType,
CategoryID = categoryIDValue
} equals new
{
UserID = c.UserID,
DeliveryType = c.DeliveryType,
CategoryID = c.CategoryID
}
into gr
from catGroup in gr.DefaultIfEmpty()
select d;
}
return query;
}
