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;
        }
Пример #2
0
        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;
            }
        }
Пример #4
0
        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;
        }
Пример #9
0
 protected abstract long DB_GetFilteredMessagesCount(MailFilter filter);
Пример #10
0
 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);
 }
Пример #11
0
 public static SqlQuery ApplyFilter(this SqlQuery query, MailFilter filter)
 {
     return ApplyFilter(query, filter, false);
 }
Пример #12
0
 public static SqlDelete ApplyFilter(this SqlDelete query, MailFilter filter)
 {
     return ApplyFilter(query, filter, false);
 }
Пример #13
0
        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;
        }
Пример #14
0
 protected override List<int> DB_GetFilteredMessagesIds(MailFilter filter)
 {
     return mailBoxManager.GetFilteredMessagesIds(TenantId, Username, filter);
 }
Пример #15
0
 protected override long DB_GetFilteredMessagesCount(MailFilter filter)
 {
     return mailBoxManager.GetMailsFilteredCount(TenantId, Username, filter);
 }
Пример #16
0
 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;
 }
Пример #17
0
 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);
 }
Пример #18
0
 protected abstract IList<MailMessageItem> DB_GetFilteredMessages(MailFilter filter, int page, int page_size);
Пример #19
0
 private IEnumerable<MailMessageItem> GetFilteredMessages(MailFilter filter, int page, int pageSize, out long totalMessagesCount)
 {
     return MailBoxManager.GetMailsFiltered(TenantId, Username, filter, page, pageSize, out totalMessagesCount);
 }
Пример #20
0
 protected abstract List<int> DB_GetFilteredMessagesIds(MailFilter filter);
Пример #21
0
        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;
        }