コード例 #1
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);
        }
コード例 #2
0
        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);
        }
コード例 #3
0
        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);
        }
コード例 #4
0
        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);
        }
コード例 #5
0
        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;
            }
        }
コード例 #6
0
        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);
            }
        }
コード例 #7
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);
        }
コード例 #8
0
        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);
        }
コード例 #9
0
        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);
        }
コード例 #10
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);
            }
        }
コード例 #11
0
        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);
        }
コード例 #12
0
        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);
        }
コード例 #13
0
        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));
        }
コード例 #14
0
        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);
        }
コード例 #15
0
 public static SqlDelete ApplyFilter(this SqlDelete query, MailFilter filter)
 {
     return(ApplyFilter(query, filter, false));
 }
コード例 #16
0
 public static SqlQuery ApplyFilter(this SqlQuery query, MailFilter filter, string alias)
 {
     return(ApplyFilter(query, filter, alias, false));
 }
コード例 #17
0
        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);
        }
コード例 #18
0
        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);
        }
コード例 #19
0
        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);
        }
コード例 #20
0
 private IEnumerable <MailMessageItem> GetFilteredMessages(MailFilter filter, int page, int pageSize, out long totalMessagesCount)
 {
     return(MailBoxManager.GetMailsFiltered(TenantId, Username, filter, page, pageSize, out totalMessagesCount));
 }
コード例 #21
0
 protected abstract List <int> DB_GetFilteredMessagesIds(MailFilter filter);
コード例 #22
0
        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);
        }
コード例 #23
0
 protected override List <int> DB_GetFilteredMessagesIds(MailFilter filter)
 {
     return(mailBoxManager.GetFilteredMessagesIds(TenantId, Username, filter));
 }
コード例 #24
0
 protected override long DB_GetFilteredMessagesCount(MailFilter filter)
 {
     return(mailBoxManager.GetMailsFilteredCount(TenantId, Username, filter));
 }
コード例 #25
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));
        }
コード例 #26
0
 public static SqlQuery ApplyFilter(this SqlQuery query, MailFilter filter)
 {
     return(ApplyFilter(query, filter, false));
 }
コード例 #27
0
        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);
            }
        }
コード例 #28
0
        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);
        }
コード例 #29
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));
 }
コード例 #30
0
 protected abstract long DB_GetFilteredMessagesCount(MailFilter filter);