public IEnumerable <MailMessageItem> GetFilteredMessages(int?folder,
bool?unread,
bool?attachments,
long?period_from,
long?period_to,
bool?important,
string find_address,
int?mailbox_id,
IEnumerable <int> tags,
string search,
int?page,
int?page_size,
ApiDateTime last_check_date,
string sort,
string sortorder
)
{
var filter = new MailFilter
{
PrimaryFolder = folder.GetValueOrDefault(MailFolder.Ids.inbox),
Unread = unread,
Attachments = attachments.GetValueOrDefault(false),
Period_from = period_from.GetValueOrDefault(0),
Period_to = period_to.GetValueOrDefault(0),
Important = important.GetValueOrDefault(false),
FindAddress = find_address,
MailboxId = mailbox_id,
CustomLabels = new ASC.Mail.Aggregator.Collection.ItemList <int>(tags),
SearchFilter = search,
PageSize = page_size.GetValueOrDefault(25),
SortOrder = sortorder
};
mailBoxManager.UpdateUserActivity(TenantId, Username);
if (null != last_check_date)
{
var date_time = mailBoxManager.GetFolderModifyDate(TenantId, Username, filter.PrimaryFolder);
var api_date = new ApiDateTime(date_time);
var compare_rez = api_date.CompareTo(last_check_date);
if (compare_rez == 0) // equals
{
return(null);
}
if (compare_rez == -1) // less
{
return(new List <MailMessageItem>());
}
}
long total_messages;
var messages = GetFilteredMessages(filter, filter.Page, filter.PageSize, out total_messages);
CorrectPageValue(filter, total_messages);
_context.SetTotalCount(total_messages);
return(messages);
}
public IEnumerable <MailMessage> GetFilteredConversations(int?folder,
bool?unread,
bool?attachments,
long?period_from,
long?period_to,
bool?important,
string find_address,
int?mailbox_id,
IEnumerable <int> tags,
string search,
int?page_size,
string sortorder,
ApiDateTime from_date,
int?from_message,
bool?prev_flag,
bool?with_calendar
)
{
var filter = new MailFilter
{
PrimaryFolder = folder.GetValueOrDefault(MailFolder.Ids.inbox),
Unread = unread,
Attachments = attachments,
PeriodFrom = period_from,
PeriodTo = period_to,
Important = important,
FindAddress = find_address,
MailboxId = mailbox_id,
CustomLabels = new ItemList <int>(tags),
SearchText = search,
PageSize = page_size.GetValueOrDefault(25),
SortOrder = sortorder,
WithCalendar = with_calendar
};
bool hasMore;
var conversations = MailBoxManager.GetConversations(
TenantId,
Username,
filter,
from_date,
from_message.GetValueOrDefault(0),
prev_flag,
out hasMore);
if (hasMore)
{
var page = filter.PageSize;
_context.SetTotalCount(page + 1);
}
else
{
_context.SetTotalCount(conversations.Count);
}
return(conversations);
}
private static SqlQuery ApplyFilter(this SqlQuery query, MailFilter filter, bool skipFolder)
{
var conditions = GetMailFilterConditions(filter, skipFolder, string.Empty);
if (conditions != null)
{
query.Where(conditions);
}
return(query);
}
private static SqlDelete ApplyFilter(this SqlDelete query, MailFilter filter, string alias, bool skipFolder)
{
var conditions = GetMailFilterConditions(filter, skipFolder, alias);
if (conditions != null)
{
query.Where(conditions);
}
return(query);
}
private void CorrectPageValue(MailFilter filter, long totalMessages)
{
var maxPage = (int)Math.Ceiling((double)totalMessages / filter.PageSize);
if (filter.Page > maxPage)
{
filter.Page = maxPage;
}
if (filter.Page < 1)
{
filter.Page = 1;
}
}
public long GetNextConversationId(int tenant, string user, int id, MailFilter filter)
{
using (var db = GetDb())
{
var chainDate = db.ExecuteScalar <DateTime>(new SqlQuery(MailTable.Name)
.Select(MailTable.Columns.ChainDate)
.Where(GetUserWhere(user, tenant))
.Where(MailTable.Columns.Id, id));
filter.PageSize = 1;
bool hasMore;
var messages = GetFilteredConversations(db, tenant, user, filter, chainDate, id, false, out hasMore);
return(messages.Any() ? messages.First().Id : 0);
}
}
private static SqlQuery ApplyFilter(this SqlQuery query, MailFilter filter, string alias, bool skipFolder)
{
var conditions = GetMailFilterConditions(filter, skipFolder, alias);
if (conditions == null) // skip query
{
return(null);
}
if (conditions != Exp.Empty)
{
query.Where(conditions);
}
return(query);
}
public long GetPrevNextMessageId(int id,
string direction,
int?folder,
bool?unread,
bool?attachments,
long?period_from,
long?period_to,
bool?important,
string find_address,
int?mailbox_id,
IEnumerable <int> tags,
string search,
int?page_size,
string sortorder,
bool?with_calendar)
{
// inverse sort order if prev message require
if ("prev" == direction)
{
sortorder = "ascending" == sortorder ? "descending" : "ascending";
}
var filter = new MailFilter
{
PrimaryFolder = folder.GetValueOrDefault(MailFolder.Ids.inbox),
Unread = unread,
Attachments = attachments,
PeriodFrom = period_from,
PeriodTo = period_to,
Important = important,
FindAddress = find_address,
MailboxId = mailbox_id,
CustomLabels = new ItemList <int>(tags),
SearchText = search,
PageSize = page_size.GetValueOrDefault(25),
SortOrder = sortorder,
WithCalendar = with_calendar
};
var nextId = MailBoxManager.GetNextMessageId(TenantId, Username, id, filter);
return(nextId);
}
private static bool TryGetFullTextSearchIds(MailFilter filter, out List <int> ids)
{
ids = new List <int>();
var actionSucceed = false;
if (!string.IsNullOrEmpty(filter.SearchText) && FullTextSearch.SupportModule(FullTextSearch.MailModule))
{
var mailModule =
FullTextSearch.MailModule.Match(filter.SearchText)
.OrderBy(MailTable.Columns.DateSent, filter.SortOrder == "ascending");
if (filter.PrimaryFolder != 1 && filter.PrimaryFolder != 2)
{
mailModule.AddAttribute("folder", filter.PrimaryFolder);
}
else
{
mailModule.AddAttribute("folder", new[] { 1, 2 });
}
ids = FullTextSearch.Search(mailModule);
actionSucceed = true;
}
if (!string.IsNullOrEmpty(filter.FindAddress) && FullTextSearch.SupportModule(FullTextSearch.MailModule))
{
var tmpIds =
FullTextSearch.Search(FullTextSearch.MailModule.Match(filter.FindAddress,
filter.PrimaryFolder == MailFolder.Ids.sent || filter.PrimaryFolder == MailFolder.Ids.drafts
? MailTable.Columns.To
: MailTable.Columns.From));
actionSucceed = true;
if (tmpIds.Any())
{
ids = ids.Any() ? ids.Where(id => tmpIds.Exists(tid => tid == id)).ToList() : tmpIds;
}
}
return(actionSucceed);
}
public ItemList <int> GetFilteredMessagesIds(MailFilter filter)
{
try
{
if (filter == null)
{
throw new ArgumentNullException("filter");
}
CheckPermission();
return(new ItemList <int>(DB_GetFilteredMessagesIds(filter)));
}
catch (WebProtocolException)
{
throw;
}
catch (Exception e)
{
throw GenerateException(e);
}
}
public static SqlQuery ApplySorting(this SqlQuery query, MailFilter filter)
{
var sortField = MailTable.Columns.DateSent;
switch (filter.Sort)
{
case "subject":
sortField = MailTable.Columns.Subject;
break;
case "sender":
sortField = MailTable.Columns.From;
break;
}
var sortOrder = filter.SortOrder == "ascending";
query.OrderBy(sortField, sortOrder);
return(query);
}
public IEnumerable <MailMessage> GetFilteredMessages(int?folder,
bool?unread,
bool?attachments,
long?period_from,
long?period_to,
bool?important,
string find_address,
int?mailbox_id,
IEnumerable <int> tags,
string search,
int?page,
int?page_size,
string sortorder,
bool?with_calendar)
{
var filter = new MailFilter
{
PrimaryFolder = folder.GetValueOrDefault(MailFolder.Ids.inbox),
Unread = unread,
Attachments = attachments,
PeriodFrom = period_from,
PeriodTo = period_to,
Important = important,
FindAddress = find_address,
MailboxId = mailbox_id,
CustomLabels = new ItemList <int>(tags),
SearchText = search,
Page = page,
PageSize = page_size.GetValueOrDefault(25),
SortOrder = sortorder,
WithCalendar = with_calendar
};
long totalMessages;
var messages = MailBoxManager.GetFilteredMessages(TenantId, Username, filter, out totalMessages);
CorrectPageValue(filter, totalMessages);
_context.SetTotalCount(totalMessages);
return(messages);
}
public long GetPrevNextConversationId(int id,
string direction,
int?folder,
bool?unread,
bool?attachments,
long?period_from,
long?period_to,
bool?important,
string find_address,
int?mailbox_id,
IEnumerable <int> tags,
string search,
int?page_size,
string sortorder)
{
// inverse sort order if prev message require
if ("prev" == direction)
{
sortorder = "ascending" == sortorder ? "descending" : "ascending";
}
var filter = new MailFilter
{
PrimaryFolder = folder.GetValueOrDefault(MailFolder.Ids.inbox),
Unread = unread,
Attachments = attachments.GetValueOrDefault(false),
Period_from = period_from.GetValueOrDefault(0),
Period_to = period_to.GetValueOrDefault(0),
Important = important.GetValueOrDefault(false),
FindAddress = find_address,
MailboxId = mailbox_id,
CustomLabels = new ASC.Mail.Aggregator.Collection.ItemList <int>(tags),
SearchFilter = search,
PageSize = page_size.GetValueOrDefault(25),
SortOrder = sortorder
};
return(mailBoxManager.GetNextConversationId(TenantId, Username, id, filter));
}
public static Exp GetMailFilterConditions(MailFilter filter, bool skipFolder, string alias = "")
{
var conditions = Exp.Empty;
if (!skipFolder)
{
conditions = Exp.Eq(MailTable.Columns.Folder.Prefix(alias), filter.PrimaryFolder);
}
if (filter.Unread.HasValue)
{
conditions &= Exp.Eq(MailTable.Columns.Unread.Prefix(alias), filter.Unread);
}
if (filter.Attachments.HasValue)
{
conditions &= Exp.Gt(MailTable.Columns.AttachCount.Prefix(alias), 0);
}
if (filter.PeriodFrom.HasValue && filter.PeriodTo.HasValue)
{
var from = new DateTime(1970, 1, 1) + new TimeSpan(filter.PeriodFrom.Value * 10000);
var to = new DateTime(1970, 1, 1) + new TimeSpan(filter.PeriodTo.Value * 10000) +
new TimeSpan(1, 0, 0, 0, 0); // 1 day was added to make the "To" date limit inclusive
conditions &= Exp.Between(MailTable.Columns.DateSent.Prefix(alias), from, to);
}
if (filter.Important.HasValue)
{
conditions &= Exp.Eq(MailTable.Columns.Importance.Prefix(alias), true);
}
if (filter.WithCalendar.HasValue)
{
conditions &= !Exp.Eq(MailTable.Columns.CalendarUid.Prefix(alias), null);
}
if (!string.IsNullOrEmpty(filter.FindAddress) && !FullTextSearch.SupportModule(FullTextSearch.MailModule))
{
if (filter.PrimaryFolder == MailFolder.Ids.sent || filter.PrimaryFolder == MailFolder.Ids.drafts)
{
conditions &= Exp.Like(MailTable.Columns.To.Prefix(alias), filter.FindAddress, SqlLike.AnyWhere);
}
else
{
conditions &= Exp.Like(MailTable.Columns.From.Prefix(alias), filter.FindAddress,
SqlLike.AnyWhere);
}
}
if (filter.MailboxId.HasValue)
{
conditions &= Exp.Eq(MailTable.Columns.MailboxId.Prefix(alias), filter.MailboxId.Value);
}
if (!string.IsNullOrEmpty(filter.SearchText) && !FullTextSearch.SupportModule(FullTextSearch.MailModule))
{
conditions &=
Exp.Or(Exp.Like(MailTable.Columns.From.Prefix(alias), filter.SearchText, SqlLike.AnyWhere),
Exp.Or(
Exp.Like(MailTable.Columns.To.Prefix(alias), filter.SearchText, SqlLike.AnyWhere),
Exp.Or(
Exp.Like(MailTable.Columns.Cc.Prefix(alias), filter.SearchText,
SqlLike.AnyWhere),
Exp.Or(
Exp.Like(MailTable.Columns.Bcc.Prefix(alias), filter.SearchText,
SqlLike.AnyWhere),
Exp.Like(MailTable.Columns.Subject.Prefix(alias), filter.SearchText,
SqlLike.AnyWhere)))));
}
return(conditions);
}
public static SqlDelete ApplyFilter(this SqlDelete query, MailFilter filter)
{
return(ApplyFilter(query, filter, false));
}
public static SqlQuery ApplyFilter(this SqlQuery query, MailFilter filter, string alias)
{
return(ApplyFilter(query, filter, alias, false));
}
public static Exp GetMailFilterConditions(MailFilter filter, bool skipFolder, string alias)
{
Exp conditions = null;
if (!string.IsNullOrEmpty(alias))
{
alias += ".";
}
if (!skipFolder)
{
conditions = Exp.Eq(alias + MailTable.Columns.folder, filter.PrimaryFolder);
}
if (filter.CustomLabels != null && filter.CustomLabels.Count > 0)
{
var idsWithAllTagsQuery = new SqlQuery(TagMailTable.name)
.Select(TagMailTable.Columns.id_mail)
.Where(Exp.In(TagMailTable.Columns.id_tag, filter.CustomLabels))
.GroupBy(1)
.Having(Exp.Eq("count(" + TagMailTable.Columns.id_mail + ")", filter.CustomLabels.Count()));
conditions &= Exp.In(alias + MailTable.Columns.id, idsWithAllTagsQuery);
}
if (filter.Unread.HasValue)
{
conditions &= Exp.Eq(alias + MailTable.Columns.unread, filter.Unread);
}
if (filter.Attachments)
{
conditions &= Exp.Gt(alias + MailTable.Columns.attach_count, 0);
}
if (filter.PeriodFrom > 0)
{
var from = new DateTime(1970, 1, 1) + new TimeSpan(filter.PeriodFrom * 10000);
var to = new DateTime(1970, 1, 1) + new TimeSpan(filter.PeriodTo * 10000) +
new TimeSpan(1, 0, 0, 0, 0); // 1 day was added to make the "To" date limit inclusive
conditions &= Exp.Between(alias + MailTable.Columns.date_sent, from, to);
}
if (filter.Important)
{
conditions &= Exp.Eq(alias + MailTable.Columns.importance, true);
}
if (!string.IsNullOrEmpty(filter.FindAddress))
{
if (filter.PrimaryFolder == MailFolder.Ids.sent || filter.PrimaryFolder == MailFolder.Ids.drafts)
{
conditions &= Exp.Like(alias + MailTable.Columns.to, filter.FindAddress, SqlLike.AnyWhere);
}
else
{
conditions &= Exp.Like(alias + MailTable.Columns.from, filter.FindAddress, SqlLike.AnyWhere);
}
}
if (filter.MailboxId.HasValue)
{
conditions &= Exp.Eq(alias + MailTable.Columns.id_mailbox, filter.MailboxId.Value);
}
if (!string.IsNullOrEmpty(filter.SearchFilter))
{
if (FullTextSearch.SupportModule(FullTextSearch.MailModule))
{
var ids = FullTextSearch.Search(FullTextSearch.MailModule.Match(filter.SearchFilter));
conditions &= Exp.In(alias + MailTable.Columns.id, ids.Take(MailBoxManager.FULLTEXTSEARCH_IDS_COUNT).ToList());
}
else
{
conditions &= Exp.Or(Exp.Like(alias + MailTable.Columns.from, filter.SearchFilter, SqlLike.AnyWhere),
Exp.Or(
Exp.Like(alias + MailTable.Columns.to, filter.SearchFilter, SqlLike.AnyWhere),
Exp.Or(
Exp.Like(alias + MailTable.Columns.cc, filter.SearchFilter,
SqlLike.AnyWhere),
Exp.Or(
Exp.Like(alias + MailTable.Columns.bcc, filter.SearchFilter,
SqlLike.AnyWhere),
Exp.Like(alias + MailTable.Columns.subject, filter.SearchFilter,
SqlLike.AnyWhere)))));
}
}
return(conditions);
}
public IEnumerable <MailMessageItem> GetFilteredConversations(int?folder,
bool?unread,
bool?attachments,
long?period_from,
long?period_to,
bool?important,
string find_address,
int?mailbox_id,
IEnumerable <int> tags,
string search,
int?page_size,
ApiDateTime last_check_date,
string sortorder,
ApiDateTime from_date,
int?from_message,
bool?prev_flag
)
{
var filter = new MailFilter
{
PrimaryFolder = folder.GetValueOrDefault(MailFolder.Ids.inbox),
Unread = unread,
Attachments = attachments.GetValueOrDefault(false),
Period_from = period_from.GetValueOrDefault(0),
Period_to = period_to.GetValueOrDefault(0),
Important = important.GetValueOrDefault(false),
FindAddress = find_address,
MailboxId = mailbox_id,
CustomLabels = new ASC.Mail.Aggregator.Collection.ItemList <int>(tags),
SearchFilter = search,
PageSize = page_size.GetValueOrDefault(25),
SortOrder = sortorder
};
mailBoxManager.UpdateUserActivity(TenantId, Username);
if (null != last_check_date)
{
var date_time = mailBoxManager.GetFolderModifyDate(TenantId, Username, filter.PrimaryFolder);
var api_date = new ApiDateTime(date_time);
var compare_res = api_date.CompareTo(last_check_date);
switch (compare_res)
{
case 0:
return(null);
case -1:
return(new List <MailMessageItem>());
}
}
bool has_more;
var conversations = mailBoxManager.GetConversations(
TenantId,
Username,
filter,
from_date,
from_message.GetValueOrDefault(0),
prev_flag,
out has_more);
if (has_more)
{
_context.SetTotalCount(filter.PageSize + 1);
}
else
{
_context.SetTotalCount(conversations.Count);
}
return(conversations);
}
public static Exp GetMailFilterConditions(MailFilter filter, bool skipFolder, string alias)
{
Exp conditions = null;
if (!string.IsNullOrEmpty(alias))
{
alias += ".";
}
if (!skipFolder)
{
conditions = Exp.Eq(alias + MailTable.Columns.folder, filter.PrimaryFolder);
}
if (filter.Unread.HasValue)
{
conditions &= Exp.Eq(alias + MailTable.Columns.unread, filter.Unread);
}
if (filter.Attachments)
{
conditions &= Exp.Gt(alias + MailTable.Columns.attach_count, 0);
}
if (filter.PeriodFrom > 0)
{
var from = new DateTime(1970, 1, 1) + new TimeSpan(filter.PeriodFrom * 10000);
var to = new DateTime(1970, 1, 1) + new TimeSpan(filter.PeriodTo * 10000) +
new TimeSpan(1, 0, 0, 0, 0); // 1 day was added to make the "To" date limit inclusive
conditions &= Exp.Between(alias + MailTable.Columns.date_sent, from, to);
}
if (filter.Important)
{
conditions &= Exp.Eq(alias + MailTable.Columns.importance, true);
}
if (!string.IsNullOrEmpty(filter.FindAddress))
{
if (FullTextSearch.SupportModule(FullTextSearch.MailModule))
{
List <int> ids;
if (filter.PrimaryFolder == MailFolder.Ids.sent || filter.PrimaryFolder == MailFolder.Ids.drafts)
{
ids = FullTextSearch.Search(FullTextSearch.MailModule.Match(filter.FindAddress, MailTable.Columns.to));
}
else
{
ids = FullTextSearch.Search(FullTextSearch.MailModule.Match(filter.FindAddress, MailTable.Columns.from));
}
conditions &= Exp.In(alias + MailTable.Columns.id, ids.Take(MailBoxManager.FULLTEXTSEARCH_IDS_COUNT).ToList());
}
else
{
if (filter.PrimaryFolder == MailFolder.Ids.sent || filter.PrimaryFolder == MailFolder.Ids.drafts)
{
conditions &= Exp.Like(alias + MailTable.Columns.to, filter.FindAddress, SqlLike.AnyWhere);
}
else
{
conditions &= Exp.Like(alias + MailTable.Columns.from, filter.FindAddress, SqlLike.AnyWhere);
}
}
}
if (filter.MailboxId.HasValue)
{
conditions &= Exp.Eq(alias + MailTable.Columns.id_mailbox, filter.MailboxId.Value);
}
if (!string.IsNullOrEmpty(filter.SearchFilter))
{
if (FullTextSearch.SupportModule(FullTextSearch.MailModule))
{
var mailModule = FullTextSearch.MailModule.Match(filter.SearchFilter).OrderBy(MailTable.Columns.date_sent, filter.SortOrder == "ascending");
if (filter.PrimaryFolder != 1 && filter.PrimaryFolder != 2)
{
mailModule.AddAttribute("folder", filter.PrimaryFolder);
}
else
{
mailModule.AddAttribute("folder", new[] { 1, 2 });
}
var ids = FullTextSearch.Search(mailModule);
conditions &= Exp.In(alias + MailTable.Columns.id, ids.Take(MailBoxManager.FULLTEXTSEARCH_IDS_COUNT).ToList());
}
else
{
conditions &= Exp.Or(Exp.Like(alias + MailTable.Columns.from, filter.SearchFilter, SqlLike.AnyWhere),
Exp.Or(
Exp.Like(alias + MailTable.Columns.to, filter.SearchFilter, SqlLike.AnyWhere),
Exp.Or(
Exp.Like(alias + MailTable.Columns.cc, filter.SearchFilter,
SqlLike.AnyWhere),
Exp.Or(
Exp.Like(alias + MailTable.Columns.bcc, filter.SearchFilter,
SqlLike.AnyWhere),
Exp.Like(alias + MailTable.Columns.subject, filter.SearchFilter,
SqlLike.AnyWhere)))));
}
}
return(conditions);
}
private IEnumerable <MailMessageItem> GetFilteredMessages(MailFilter filter, int page, int pageSize, out long totalMessagesCount)
{
return(MailBoxManager.GetMailsFiltered(TenantId, Username, filter, page, pageSize, out totalMessagesCount));
}
protected abstract List <int> DB_GetFilteredMessagesIds(MailFilter filter);
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);
}
protected override List <int> DB_GetFilteredMessagesIds(MailFilter filter)
{
return(mailBoxManager.GetFilteredMessagesIds(TenantId, Username, filter));
}
protected override long DB_GetFilteredMessagesCount(MailFilter filter)
{
return(mailBoxManager.GetMailsFilteredCount(TenantId, Username, filter));
}
protected override IList <MailMessageItem> DB_GetFilteredMessages(MailFilter filter, int page, int page_size)
{
int total_messages_count;
return(mailBoxManager.GetMailsFiltered(TenantId, Username, filter, page, page_size, out total_messages_count));
}
public static SqlQuery ApplyFilter(this SqlQuery query, MailFilter filter)
{
return(ApplyFilter(query, filter, false));
}
public List <MailMessage> GetConversations(
int tenant,
string user,
MailFilter filter,
DateTime?utcChainFromDate,
int fromMessageId,
bool?prevFlag,
out bool hasMore)
{
if (filter == null)
{
throw new ArgumentNullException("filter");
}
using (var db = GetDb())
{
var res = GetFilteredConversations(
db,
tenant,
user,
filter,
utcChainFromDate,
fromMessageId,
prevFlag,
out hasMore);
if (!res.Any())
{
return(res);
}
if (prevFlag.GetValueOrDefault(false) && !hasMore)
{
if (res.Count == filter.PageSize)
{
res.Reverse();
}
else
{
res = GetFilteredConversations(db, tenant, user, filter, null, 0, false, out hasMore);
hasMore = false;
if (!res.Any())
{
return(res);
}
}
}
else if (prevFlag.GetValueOrDefault(false))
{
res.Reverse();
}
var chainIds = new List <string>();
res.ForEach(x => chainIds.Add(x.ChainId));
var query = new SqlQuery(ChainTable.Name)
.Select(
ChainTable.Columns.Id,
ChainTable.Columns.MailboxId,
ChainTable.Columns.Length,
ChainTable.Columns.Unread,
ChainTable.Columns.Importance,
ChainTable.Columns.HasAttachments,
ChainTable.Columns.Tags)
.Where(GetUserWhere(user, tenant))
.Where(Exp.In(ChainTable.Columns.Id, chainIds))
.Where(GetSearchFolders(ChainTable.Columns.Folder, filter.PrimaryFolder));
var extendedInfo = db.ExecuteList(query)
.ConvertAll(x => new {
id = x[0].ToString(),
id_mailbox = Convert.ToInt32(x[1]),
length = Convert.ToInt32(x[2]),
unread = Convert.ToBoolean(x[3]),
importance = Convert.ToBoolean(x[4]),
has_attachments = Convert.ToBoolean(x[5]),
tags = x[6].ToString()
});
foreach (var item in res)
{
var foundedInfo = extendedInfo.FindAll(x => x.id_mailbox == item.MailboxId && x.id == item.ChainId);
if (!foundedInfo.Any())
{
continue;
}
item.IsNew = foundedInfo.Any(x => x.unread);
item.HasAttachments = foundedInfo.Any(x => x.has_attachments);
item.Important = foundedInfo.Any(x => x.importance);
item.ChainLength = foundedInfo.Sum(x => x.length);
var firstOrDefault = foundedInfo.FirstOrDefault(x => !string.IsNullOrEmpty(x.tags));
item.LabelsString = firstOrDefault != null ? firstOrDefault.tags : "";
}
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);
}
private List <MailMessageItem> GetFilteredMessages(MailFilter filter, int page, int page_size, out long total_messages_count)
{
return(mailBoxManager.GetMailsFiltered(TenantId, Username, filter, page, page_size, out total_messages_count));
}
protected abstract long DB_GetFilteredMessagesCount(MailFilter filter);
