private static SqlDelete ApplyFilter(this SqlDelete query, MailFilter filter, bool skip_folder)
{
var conditions = GetMailFilterConditions(filter, skip_folder, string.Empty);
if (conditions != null)
query.Where(conditions);
return query;
}
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;
}
// ReSharper restore InconsistentNaming
public List<MailMessageItem> GetConversations(
int id_tenant,
string id_user,
MailFilter filter,
DateTime? utc_chain_from_date,
int from_message,
bool? prev_flag,
out bool has_more)
{
if (filter == null)
throw new ArgumentNullException("filter");
using (var db = GetDb())
{
var res = GetFilteredChains(
db,
id_tenant,
id_user,
filter,
utc_chain_from_date,
from_message,
prev_flag,
out has_more);
if (prev_flag.GetValueOrDefault(false) && !has_more)
{
if (res.Count == filter.PageSize)
res.Reverse();
else
{
res = GetFilteredChains(db, id_tenant, id_user, filter, null, 0, false, out has_more);
has_more = false;
}
} else if(prev_flag.GetValueOrDefault(false))
res.Reverse();
var chain_ids = new List<string>();
res.ForEach(x => chain_ids.Add(x.ChainId));
var query = new SqlQuery(ChainTable.name)
.Select(
ChainTable.Columns.id,
ChainTable.Columns.id_mailbox,
ChainTable.Columns.length,
ChainTable.Columns.unread,
ChainTable.Columns.importance,
ChainTable.Columns.has_attachments,
ChainTable.Columns.tags)
.Where(GetUserWhere(id_user, id_tenant))
.Where(Exp.In(ChainTable.Columns.id, chain_ids))
.Where(GetSearchFolders(ChainTable.Columns.folder, filter.PrimaryFolder));
var extended_info = 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 founded_info = extended_info.FindAll(x => x.id_mailbox == item.MailboxId && x.id == item.ChainId);
if (!founded_info.Any()) continue;
item.IsNew = founded_info.Any(x => x.unread);
item.HasAttachments = founded_info.Any(x => x.has_attachments);
item.Important = founded_info.Any(x => x.importance);
item.ChainLength = founded_info.Sum(x => x.length);
var first_or_default = founded_info.FirstOrDefault(x => !string.IsNullOrEmpty(x.tags));
item.LabelsString = first_or_default != null ? first_or_default.tags : "";
}
return res;
}
}
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 long GetNextConversationId(int tenant, string user, int id, MailFilter filter)
{
using (var db = GetDb())
{
var chain_date = db.ExecuteScalar<DateTime>(new SqlQuery(MailTable.name)
.Select(MailTable.Columns.chain_date)
.Where(GetUserWhere(user, tenant))
.Where(MailTable.Columns.id, id));
filter.PageSize = 1;
bool has_more;
var messages = GetFilteredChains(db, tenant, user, filter, chain_date, id, false, out has_more);
return messages.Any() ? messages.First().Id : 0;
}
}
private List<MailMessageItem> GetFilteredChains(
IDbManager db,
int id_tenant,
string id_user,
MailFilter filter,
DateTime? utc_chain_from_date,
int from_message,
bool? prev_flag,
out bool has_more)
{
var res = new List<MailMessageItem>();
var chains_to_skip = new List<ChainInfo>();
var skip_flag = false;
var chunck_index = 0;
var sort_order = filter.SortOrder == "ascending";
if (prev_flag.GetValueOrDefault(false))
sort_order = !sort_order;
var query_messages = new SqlQuery(MailTable.name)
.Select(
MailTable.Columns.id,
MailTable.Columns.from,
MailTable.Columns.to,
MailTable.Columns.reply,
MailTable.Columns.subject,
MailTable.Columns.importance,
MailTable.Columns.date_sent,
MailTable.Columns.size,
MailTable.Columns.attach_count,
MailTable.Columns.unread,
MailTable.Columns.is_answered,
MailTable.Columns.is_forwarded,
MailTable.Columns.is_from_crm,
MailTable.Columns.is_from_tl,
MailTable.Columns.folder_restore,
MailTable.Columns.folder,
MailTable.Columns.chain_id,
MailTable.Columns.id_mailbox,
MailTable.Columns.chain_date)
.Where(GetUserWhere(id_user, id_tenant))
.Where(MailTable.Columns.is_removed, false)
.ApplyFilter(filter)
.OrderBy(MailTable.Columns.chain_date, sort_order);
if (null != utc_chain_from_date)
{
query_messages = query_messages.Where(sort_order ?
Exp.Ge(MailTable.Columns.chain_date, utc_chain_from_date) :
Exp.Le(MailTable.Columns.chain_date, utc_chain_from_date));
skip_flag = 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)
{
query_messages.SetFirstResult(chunck_index * chunk_size * filter.PageSize).SetMaxResults(chunk_size * filter.PageSize);
chunck_index++;
var tenant_obj = CoreContext.TenantManager.GetTenant(id_tenant);
var list = db
.ExecuteList(query_messages)
.ConvertAll(r =>
ConvertToConversation(r, tenant_obj));
if (0 == list.Count)
break;
foreach (var item in list)
{
var chain_info = new ChainInfo {id = item.ChainId, mailbox = item.MailboxId};
// Skip chains that was stored before and within from_message's chain.
if (skip_flag)
{
var tenant = CoreContext.TenantManager.GetTenant(id_tenant);
if (item.ChainDate != TenantUtil.DateTimeFromUtc(tenant, utc_chain_from_date.GetValueOrDefault()))
skip_flag = false;
else
{
if (item.Id == from_message)
skip_flag = false;
chains_to_skip.Add(chain_info);
continue;
}
}
if (chains_to_skip.Contains(chain_info))
continue;
var already_contains = false;
foreach(var chain in res){
if(chain.ChainId == item.ChainId && chain.MailboxId == item.MailboxId){
already_contains = true;
if(chain.Date < item.Date)
res[res.IndexOf(chain)] = item;
break;
}
}
if(!already_contains)
res.Add(item);
if (filter.PageSize + 1 == res.Count)
break;
}
var is_all_needed_conversation_gathered = filter.PageSize + 1 == res.Count;
if (is_all_needed_conversation_gathered)
break;
var is_enough_messages_for_page = chunk_size*filter.PageSize <= list.Count;
if (!is_enough_messages_for_page)
break;
}
has_more = res.Count > filter.PageSize;
if (has_more)
res.RemoveAt(filter.PageSize);
return res;
}
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 ItemList<int>(tags),
SearchFilter = search,
PageSize = page_size.GetValueOrDefault(25),
SortOrder = sortorder
};
return MailBoxManager.GetNextConversationId(TenantId, Username, id, filter);
}
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 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;
}
protected abstract long DB_GetFilteredMessagesCount(MailFilter 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 static SqlDelete ApplyFilter(this SqlDelete query, MailFilter filter)
{
return ApplyFilter(query, filter, false);
}
public static SqlQuery ApplySorting(this SqlQuery query, MailFilter filter)
{
var sort_field = MailTable.Columns.date_sent;
switch (filter.Sort)
{
case "subject":
sort_field = MailTable.Columns.subject;
break;
case "sender":
sort_field = MailTable.Columns.@from;
break;
}
var sort_order = filter.SortOrder == "ascending";
query.OrderBy(sort_field, sort_order);
return query;
}
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);
}
private void CorrectPageValue(MailFilter filter, long total_messages)
{
int max_page = (int)Math.Ceiling((double)total_messages / (double)filter.PageSize);
if (filter.Page > max_page) filter.Page = max_page;
if (filter.Page < 1) filter.Page = 1;
}
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 IList<MailMessageItem> DB_GetFilteredMessages(MailFilter filter, int page, int page_size);
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);
public static Exp GetMailFilterConditions(MailFilter filter, bool skip_folder, string alias)
{
Exp conditions = null;
if (!string.IsNullOrEmpty(alias))
alias += ".";
if (!skip_folder)
conditions = Exp.Eq(alias + MailTable.Columns.folder, filter.PrimaryFolder);
if (filter.CustomLabels != null && filter.CustomLabels.Count > 0)
{
var ids_with_any_of_tags = new SqlQuery(MailBoxManager.MAIL_TAG_MAIL)
.Select(MailBoxManager.TagMailFields.id_mail)
.Where(Exp.In(MailBoxManager.TagMailFields.id_tag, filter.CustomLabels));
var ids_with_all_tags = new SqlQuery()
.Select(MailBoxManager.TagMailFields.id_mail)
.From(ids_with_any_of_tags, "a")
.GroupBy(MailBoxManager.TagMailFields.id_mail)
.Having(
Exp.Sql("count(a." + MailBoxManager.TagMailFields.id_mail + ")=" + filter.CustomLabels.Count()));
conditions &= Exp.In(alias + MailTable.Columns.id, ids_with_all_tags);
}
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.Period_from > 0)
{
var from = new DateTime(1970, 1, 1) + new TimeSpan(filter.Period_from * 10000);
var to = new DateTime(1970, 1, 1) + new TimeSpan(filter.Period_to * 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(filter.SearchFilter, FullTextSearch.MailModule)
.GetIdentifiers()
.Select(id => int.Parse(id));
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;
}
