Beispiel #1
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);
        }
Beispiel #2
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);
        }