private List <MailMessage> GetFilteredConversations(IDbManager db, int tenant, string user, MailFilter filter,
DateTime?utcChainFromDate, int fromMessageId, bool?prevFlag, out bool hasMore)
{
var res = new List <MailMessage>();
var chainsToSkip = new List <ChainInfo>();
var skipFlag = false;
var chunkIndex = 0;
var sortOrder = filter.SortOrder == "ascending";
if (prevFlag.GetValueOrDefault(false))
{
sortOrder = !sortOrder;
}
const string mm_alias = "ch";
const string mtm_alias = "tm";
var queryMessages = new SqlQuery(MailTable.Name.Alias(mm_alias))
.Select(GetMailMessagesColumns(mm_alias));
if (filter.CustomLabels != null && filter.CustomLabels.Count > 0)
{
queryMessages = queryMessages
.InnerJoin(TagMailTable.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), filter.CustomLabels));
}
queryMessages = queryMessages
.ApplyFilter(filter, mm_alias);
if (queryMessages == null)
{
hasMore = false;
return(res);
}
queryMessages
.Where(TagMailTable.Columns.Tenant.Prefix(mm_alias), tenant)
.Where(TagMailTable.Columns.User.Prefix(mm_alias), user)
.Where(MailTable.Columns.IsRemoved.Prefix(mm_alias), false);
if (filter.CustomLabels != null && filter.CustomLabels.Count > 0)
{
queryMessages = queryMessages
.GroupBy(1)
.Having(Exp.Eq(string.Format("count({0})", MailTable.Columns.Id.Prefix(mm_alias)),
filter.CustomLabels.Count()));
}
queryMessages = queryMessages.OrderBy(MailTable.Columns.ChainDate, sortOrder);
if (null != utcChainFromDate)
{
queryMessages = queryMessages.Where(sortOrder
? Exp.Ge(MailTable.Columns.ChainDate, utcChainFromDate)
: Exp.Le(MailTable.Columns.ChainDate, utcChainFromDate));
skipFlag = true;
}
List <int> ids;
if (TryGetFullTextSearchIds(filter, out ids))
{
if (!ids.Any())
{
hasMore = false;
return(res);
}
}
var tenantInfo = CoreContext.TenantManager.GetTenant(tenant);
var utcNow = DateTime.UtcNow;
var pageSize = filter.PageSize;
// We are increasing the size of the page to check whether it is necessary to show the Next button.
while (res.Count < pageSize + 1)
{
queryMessages
.SetFirstResult(chunkIndex * CHUNK_SIZE * pageSize)
.SetMaxResults(CHUNK_SIZE * pageSize);
chunkIndex++;
var list = db
.ExecuteList(queryMessages)
.ConvertAll(r =>
ConvertToMailMessage(r, tenantInfo, utcNow));
if (0 == list.Count)
{
break;
}
if (ids.Any())
{
list = list.Where(m => ids.Exists(id => id == m.Id)).ToList();
}
foreach (var item in list)
{
var chainInfo = new ChainInfo {
id = item.ChainId, mailbox = item.MailboxId
};
// Skip chains that was stored before and within from_message's chain.
if (skipFlag)
{
if (item.ChainDate !=
TenantUtil.DateTimeFromUtc(tenantInfo.TimeZone, utcChainFromDate.GetValueOrDefault()))
{
skipFlag = false;
}
else
{
if (item.Id == fromMessageId)
{
skipFlag = false;
}
chainsToSkip.Add(chainInfo);
continue;
}
}
if (chainsToSkip.Contains(chainInfo))
{
continue;
}
var alreadyContains = false;
foreach (var chain in res)
{
if (chain.ChainId == item.ChainId && chain.MailboxId == item.MailboxId)
{
alreadyContains = true;
if (chain.Date < item.Date)
{
res[res.IndexOf(chain)] = item;
}
break;
}
}
if (!alreadyContains)
{
res.Add(item);
}
if (pageSize + 1 == res.Count)
{
break;
}
}
if (pageSize + 1 == res.Count)
{
break;
}
}
hasMore = res.Count > pageSize;
if (hasMore)
{
res.RemoveAt(pageSize);
}
return(res);
}
private List <MailMessage> GetFilteredChains(IDbManager db, int tenant, string user, MailFilter filter, DateTime?utcChainFromDate, int fromMessageId, bool?prevFlag, out bool hasMore)
{
var res = new List <MailMessage>();
var chainsToSkip = new List <ChainInfo>();
var skipFlag = false;
var chunkIndex = 0;
var sortOrder = filter.SortOrder == "ascending";
if (prevFlag.GetValueOrDefault(false))
{
sortOrder = !sortOrder;
}
const string mm_alias = "ch";
const string mtm_alias = "tm";
var queryMessages = new SqlQuery(MailTable.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.reply.Prefix(mm_alias),
MailTable.Columns.subject.Prefix(mm_alias),
MailTable.Columns.importance.Prefix(mm_alias),
MailTable.Columns.date_sent.Prefix(mm_alias),
MailTable.Columns.size.Prefix(mm_alias),
MailTable.Columns.attach_count.Prefix(mm_alias),
MailTable.Columns.unread.Prefix(mm_alias),
MailTable.Columns.is_answered.Prefix(mm_alias),
MailTable.Columns.is_forwarded.Prefix(mm_alias),
MailTable.Columns.is_from_crm.Prefix(mm_alias),
MailTable.Columns.is_from_tl.Prefix(mm_alias),
MailTable.Columns.folder_restore.Prefix(mm_alias),
MailTable.Columns.folder.Prefix(mm_alias),
MailTable.Columns.chain_id.Prefix(mm_alias),
MailTable.Columns.id_mailbox.Prefix(mm_alias),
MailTable.Columns.chain_date.Prefix(mm_alias));
if (filter.CustomLabels != null && filter.CustomLabels.Count > 0)
{
queryMessages = queryMessages
.InnerJoin(TagMailTable.name + " " + mtm_alias,
Exp.EqColumns(MailTable.Columns.id.Prefix(mm_alias), TagMailTable.Columns.id_mail.Prefix(mtm_alias)))
.Where(Exp.In(TagMailTable.Columns.id_tag.Prefix(mtm_alias), filter.CustomLabels));
}
queryMessages = queryMessages
.ApplyFilter(filter, mm_alias)
.Where(TagMailTable.Columns.id_tenant.Prefix(mm_alias), tenant)
.Where(TagMailTable.Columns.id_user.Prefix(mm_alias), user)
.Where(MailTable.Columns.is_removed.Prefix(mm_alias), false);
if (filter.CustomLabels != null && filter.CustomLabels.Count > 0)
{
queryMessages = queryMessages
.GroupBy(1)
.Having(Exp.Eq(string.Format("count({0})", MailTable.Columns.id.Prefix(mm_alias)), filter.CustomLabels.Count()));
}
queryMessages = queryMessages.OrderBy(MailTable.Columns.chain_date, sortOrder);
if (null != utcChainFromDate)
{
queryMessages = queryMessages.Where(sortOrder ?
Exp.Ge(MailTable.Columns.chain_date, utcChainFromDate) :
Exp.Le(MailTable.Columns.chain_date, utcChainFromDate));
skipFlag = true;
}
// We are increasing the size of the page to check whether it is necessary to show the Next button.
while (res.Count < filter.PageSize + 1)
{
queryMessages.SetFirstResult(chunkIndex * CHUNK_SIZE * filter.PageSize).SetMaxResults(CHUNK_SIZE * filter.PageSize);
chunkIndex++;
var tenantInfo = CoreContext.TenantManager.GetTenant(tenant);
var list = db
.ExecuteList(queryMessages)
.ConvertAll(r =>
ConvertToConversation(r, tenantInfo));
if (0 == list.Count)
{
break;
}
foreach (var item in list)
{
var chainInfo = new ChainInfo {
id = item.ChainId, mailbox = item.MailboxId
};
// Skip chains that was stored before and within from_message's chain.
if (skipFlag)
{
if (item.ChainDate != TenantUtil.DateTimeFromUtc(tenantInfo.TimeZone, utcChainFromDate.GetValueOrDefault()))
{
skipFlag = false;
}
else
{
if (item.Id == fromMessageId)
{
skipFlag = false;
}
chainsToSkip.Add(chainInfo);
continue;
}
}
if (chainsToSkip.Contains(chainInfo))
{
continue;
}
var alreadyContains = false;
foreach (var chain in res)
{
if (chain.ChainId == item.ChainId && chain.MailboxId == item.MailboxId)
{
alreadyContains = true;
if (chain.Date < item.Date)
{
res[res.IndexOf(chain)] = item;
}
break;
}
}
if (!alreadyContains)
{
res.Add(item);
}
if (filter.PageSize + 1 == res.Count)
{
break;
}
}
var isAllNeededConversationGathered = filter.PageSize + 1 == res.Count;
if (isAllNeededConversationGathered)
{
break;
}
var isEnoughMessagesForPage = CHUNK_SIZE * filter.PageSize <= list.Count;
if (!isEnoughMessagesForPage)
{
break;
}
}
hasMore = res.Count > filter.PageSize;
if (hasMore)
{
res.RemoveAt(filter.PageSize);
}
return(res);
}