public T GetFieldMaxValue <T>(IMessagesExp exp, string field)
{
var fieldQuery = new SqlQuery(MailTable.TABLE_NAME.Alias(MM_ALIAS))
.SelectMax(field.Prefix(MM_ALIAS))
.Where(exp.GetExpression());
var fieldVal = Db.ExecuteScalar <T>(fieldQuery);
return(fieldVal);
}
public int SetFieldsEqual(IMessagesExp exp, string fieldFrom, string fieldTo)
{
var query =
new SqlUpdate(MailTable.TABLE_NAME.Alias(MM_ALIAS))
.Set(string.Format("{0}={1}", fieldTo.Prefix(MM_ALIAS), fieldFrom.Prefix(MM_ALIAS)))
.Where(exp.GetExpression());
var result = Db.ExecuteNonQuery(query);
return(result);
}
public int SetFieldValue <T>(IMessagesExp exp, string field, T value)
{
var query =
new SqlUpdate(MailTable.TABLE_NAME.Alias(MM_ALIAS))
.Set(field.Prefix(MM_ALIAS), value)
.Where(exp.GetExpression());
var result = Db.ExecuteNonQuery(query);
return(result);
}
public Tuple <int, int> GetRangeMails(IMessagesExp exp)
{
var query = new SqlQuery(MailTable.TABLE_NAME.Alias(MM_ALIAS))
.SelectMin(MailTable.Columns.Id.Prefix(MM_ALIAS))
.SelectMax(MailTable.Columns.Id.Prefix(MM_ALIAS))
.Where(exp.GetExpression());
var range = Db.ExecuteList(query)
.ConvertAll(r => new Tuple <int, int>(Convert.ToInt32(r[0]), Convert.ToInt32(r[1])))
.SingleOrDefault();
return(range);
}
public Dictionary <int, int> GetMailCount(IMessagesExp exp)
{
var query = new SqlQuery(MailTable.TABLE_NAME.Alias(MM_ALIAS))
.Select(MailTable.Columns.Folder.Prefix(MM_ALIAS))
.SelectCount()
.Where(exp.GetExpression())
.GroupBy(MailTable.Columns.Folder.Prefix(MM_ALIAS));
return(Db.ExecuteList(query)
.ConvertAll(r => new
{
folder = Convert.ToInt32(r[0]),
count = Convert.ToInt32(r[1])
})
.ToDictionary(o => o.folder, o => o.count));
}
public long GetMailInfoTotal(IMessagesExp exp)
{
long total;
var query = new SqlQuery(MailTable.TABLE_NAME.Alias(MM_ALIAS))
.SelectCount(MailTable.Columns.Id.Prefix(MM_ALIAS));
if (exp.TagIds != null && exp.TagIds.Any())
{
query
.InnerJoin(TagMailTable.TABLE_NAME.Alias(MTM_ALIAS),
Exp.EqColumns(MailTable.Columns.Id.Prefix(MM_ALIAS),
TagMailTable.Columns.MailId.Prefix(MTM_ALIAS)))
.Where(Exp.In(TagMailTable.Columns.TagId.Prefix(MTM_ALIAS), exp.TagIds))
.GroupBy(MailTable.Columns.Id.Prefix(MM_ALIAS))
.Having(Exp.Eq(CountMailId, exp.TagIds.Count));
}
if (exp.UserFolderId.HasValue)
{
query
.InnerJoin(UserFoldertXMailTable.TABLE_NAME.Alias(UFXM_ALIAS),
Exp.EqColumns(MailTable.Columns.Id.Prefix(MM_ALIAS),
UserFoldertXMailTable.Columns.MailId.Prefix(UFXM_ALIAS)))
.Where(UserFoldertXMailTable.Columns.FolderId.Prefix(UFXM_ALIAS), exp.UserFolderId.Value);
}
query.Where(exp.GetExpression());
if (exp.TagIds != null && exp.TagIds.Any())
{
var queryTempCount = new SqlQuery()
.SelectCount()
.From(query, "tbl");
total = Db.ExecuteScalar <long>(queryTempCount);
}
else
{
total = Db.ExecuteScalar <long>(query);
}
return(total);
}
private List <MailMessageData> GetFilteredConversations(IDaoFactory daoFactory, MailSearchFilterData filter, out bool hasMore)
{
var conversations = new List <MailMessageData>();
var skipFlag = false;
var chunkIndex = 0;
if (filter.FromDate.HasValue && filter.FromMessage.HasValue && filter.FromMessage.Value > 0)
{
skipFlag = true;
}
var prevFlag = filter.PrevFlag.GetValueOrDefault(false);
var tenantInfo = CoreContext.TenantManager.GetTenant(Tenant);
var utcNow = DateTime.UtcNow;
var pageSize = filter.PageSize.GetValueOrDefault(25);
var daoMailInfo = daoFactory.CreateMailInfoDao(Tenant, User);
while (conversations.Count < pageSize + 1)
{
filter.PageSize = CHUNK_SIZE * pageSize;
IMessagesExp exp = null;
if (!filter.IsDefault() && FactoryIndexer <MailWrapper> .Support && FactoryIndexer.CheckState(false))
{
filter.Page = chunkIndex * CHUNK_SIZE * pageSize; // Elastic Limit from {index of last message} to {count of messages}
List <MailWrapper> mailWrappers;
if (FilterChainMessagesExp.TryGetFullTextSearchChains(filter, User, out mailWrappers))
{
if (!mailWrappers.Any())
{
break;
}
var ids = mailWrappers.Select(c => c.Id).ToList();
var query = SimpleMessagesExp.CreateBuilder(Tenant, User)
.SetMessageIds(ids)
.SetOrderBy(filter.Sort);
if (prevFlag)
{
query.SetOrderAsc(!(filter.SortOrder == Defines.ASCENDING));
}
else
{
query.SetOrderAsc(filter.SortOrder == Defines.ASCENDING);
}
exp = query
.Build();
}
}
else
{
filter.Page = chunkIndex; // MySQL Limit from {page by size} to {size}
exp = new FilterChainMessagesExp(filter, Tenant, User);
}
chunkIndex++;
var listMessages = daoMailInfo.GetMailInfoList(exp, true)
.ConvertAll(m => MessageEngine.ToMailMessage(m, tenantInfo, utcNow));
if (0 == listMessages.Count)
{
break;
}
if (skipFlag && filter.FromMessage.HasValue)
{
var messageData = listMessages.FirstOrDefault(m => m.Id == filter.FromMessage.Value);
if (messageData != null)
{
// Skip chain messages by FromMessage.
listMessages =
listMessages.Where(
m => !(m.ChainId.Equals(messageData.ChainId) && m.MailboxId == messageData.MailboxId))
.ToList();
}
skipFlag = false;
}
foreach (var messageData in listMessages)
{
var existingChainIndex =
conversations.FindIndex(
c => c.ChainId == messageData.ChainId && c.MailboxId == messageData.MailboxId);
if (existingChainIndex > -1)
{
if (conversations[existingChainIndex].Date < messageData.Date)
{
conversations[existingChainIndex] = messageData;
}
}
else
{
conversations.Add(messageData);
}
}
if (conversations.Count > pageSize)
{
break;
}
}
hasMore = conversations.Count > pageSize;
if (hasMore)
{
conversations = conversations.Take(pageSize).ToList();
}
if (prevFlag)
{
conversations.Reverse();
}
return(conversations);
}
public List <MailInfo> GetMailInfoList(IMessagesExp exp, bool skipSelectTags = false)
{
var query = new SqlQuery(MailTable.TABLE_NAME.Alias(MM_ALIAS))
.Select(MailTable.Columns.Id.Prefix(MM_ALIAS),
MailTable.Columns.From.Prefix(MM_ALIAS),
MailTable.Columns.To.Prefix(MM_ALIAS),
MailTable.Columns.Cc.Prefix(MM_ALIAS),
MailTable.Columns.Reply.Prefix(MM_ALIAS),
MailTable.Columns.Subject.Prefix(MM_ALIAS),
MailTable.Columns.Importance.Prefix(MM_ALIAS),
MailTable.Columns.DateSent.Prefix(MM_ALIAS),
MailTable.Columns.Size.Prefix(MM_ALIAS),
MailTable.Columns.AttachCount.Prefix(MM_ALIAS),
MailTable.Columns.Unread.Prefix(MM_ALIAS),
MailTable.Columns.IsAnswered.Prefix(MM_ALIAS),
MailTable.Columns.IsForwarded.Prefix(MM_ALIAS),
skipSelectTags ? "\"\" as tagIds" : ConcatTagIds,
MailTable.Columns.FolderRestore.Prefix(MM_ALIAS),
MailTable.Columns.Folder.Prefix(MM_ALIAS),
MailTable.Columns.ChainId.Prefix(MM_ALIAS),
MailTable.Columns.ChainDate.Prefix(MM_ALIAS),
MailTable.Columns.MailboxId.Prefix(MM_ALIAS),
MailTable.Columns.CalendarUid.Prefix(MM_ALIAS),
MailTable.Columns.Stream.Prefix(MM_ALIAS),
MailTable.Columns.Uidl.Prefix(MM_ALIAS),
MailTable.Columns.IsRemoved.Prefix(MM_ALIAS));
if (exp.TagIds != null && exp.TagIds.Any())
{
query
.InnerJoin(TagMailTable.TABLE_NAME.Alias(MTM_ALIAS),
Exp.EqColumns(MailTable.Columns.Id.Prefix(MM_ALIAS),
TagMailTable.Columns.MailId.Prefix(MTM_ALIAS)))
.Where(Exp.In(TagMailTable.Columns.TagId.Prefix(MTM_ALIAS), exp.TagIds))
.GroupBy(1)
.Having(Exp.Eq(CountMailId, exp.TagIds.Count));
}
if (exp.UserFolderId.HasValue)
{
query
.InnerJoin(UserFoldertXMailTable.TABLE_NAME.Alias(UFXM_ALIAS),
Exp.EqColumns(MailTable.Columns.Id.Prefix(MM_ALIAS),
UserFoldertXMailTable.Columns.MailId.Prefix(UFXM_ALIAS)))
.Where(UserFoldertXMailTable.Columns.FolderId.Prefix(UFXM_ALIAS), exp.UserFolderId.Value);
}
query.Where(exp.GetExpression());
if (exp.StartIndex.HasValue)
{
query.SetFirstResult(exp.StartIndex.Value);
}
if (exp.Limit.HasValue)
{
query.SetMaxResults(exp.Limit.Value);
}
if (!string.IsNullOrEmpty(exp.OrderBy))
{
var sortField = MailTable.Columns.DateSent.Prefix(MM_ALIAS);
if (exp.OrderBy == Defines.ORDER_BY_SUBJECT)
{
sortField = MailTable.Columns.Subject.Prefix(MM_ALIAS);
}
else if (exp.OrderBy == Defines.ORDER_BY_SENDER)
{
sortField = MailTable.Columns.From.Prefix(MM_ALIAS);
}
else if (exp.OrderBy == Defines.ORDER_BY_DATE_CHAIN)
{
sortField = MailTable.Columns.ChainDate.Prefix(MM_ALIAS);
}
query.OrderBy(sortField, exp.OrderAsc != null && exp.OrderAsc.Value);
}
var list = Db.ExecuteList(query)
.ConvertAll(ToMailInfo);
return(list);
}