public T GetFieldMaxValue <T>(IMessagesExp exp, string field) { var fieldQuery = new SqlQuery(MailTable.TABLE_NAME.Alias(MM_ALIAS)) .SelectMax(field.Prefix(MM_ALIAS)) .Where(exp.GetExpression()); var fieldVal = Db.ExecuteScalar <T>(fieldQuery); return(fieldVal); }
public int SetFieldsEqual(IMessagesExp exp, string fieldFrom, string fieldTo) { var query = new SqlUpdate(MailTable.TABLE_NAME.Alias(MM_ALIAS)) .Set(string.Format("{0}={1}", fieldTo.Prefix(MM_ALIAS), fieldFrom.Prefix(MM_ALIAS))) .Where(exp.GetExpression()); var result = Db.ExecuteNonQuery(query); return(result); }
public int SetFieldValue <T>(IMessagesExp exp, string field, T value) { var query = new SqlUpdate(MailTable.TABLE_NAME.Alias(MM_ALIAS)) .Set(field.Prefix(MM_ALIAS), value) .Where(exp.GetExpression()); var result = Db.ExecuteNonQuery(query); return(result); }
public Tuple <int, int> GetRangeMails(IMessagesExp exp) { var query = new SqlQuery(MailTable.TABLE_NAME.Alias(MM_ALIAS)) .SelectMin(MailTable.Columns.Id.Prefix(MM_ALIAS)) .SelectMax(MailTable.Columns.Id.Prefix(MM_ALIAS)) .Where(exp.GetExpression()); var range = Db.ExecuteList(query) .ConvertAll(r => new Tuple <int, int>(Convert.ToInt32(r[0]), Convert.ToInt32(r[1]))) .SingleOrDefault(); return(range); }
public Dictionary <int, int> GetMailCount(IMessagesExp exp) { var query = new SqlQuery(MailTable.TABLE_NAME.Alias(MM_ALIAS)) .Select(MailTable.Columns.Folder.Prefix(MM_ALIAS)) .SelectCount() .Where(exp.GetExpression()) .GroupBy(MailTable.Columns.Folder.Prefix(MM_ALIAS)); return(Db.ExecuteList(query) .ConvertAll(r => new { folder = Convert.ToInt32(r[0]), count = Convert.ToInt32(r[1]) }) .ToDictionary(o => o.folder, o => o.count)); }
public long GetMailInfoTotal(IMessagesExp exp) { long total; var query = new SqlQuery(MailTable.TABLE_NAME.Alias(MM_ALIAS)) .SelectCount(MailTable.Columns.Id.Prefix(MM_ALIAS)); if (exp.TagIds != null && exp.TagIds.Any()) { query .InnerJoin(TagMailTable.TABLE_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), exp.TagIds)) .GroupBy(MailTable.Columns.Id.Prefix(MM_ALIAS)) .Having(Exp.Eq(CountMailId, exp.TagIds.Count)); } if (exp.UserFolderId.HasValue) { query .InnerJoin(UserFoldertXMailTable.TABLE_NAME.Alias(UFXM_ALIAS), Exp.EqColumns(MailTable.Columns.Id.Prefix(MM_ALIAS), UserFoldertXMailTable.Columns.MailId.Prefix(UFXM_ALIAS))) .Where(UserFoldertXMailTable.Columns.FolderId.Prefix(UFXM_ALIAS), exp.UserFolderId.Value); } query.Where(exp.GetExpression()); if (exp.TagIds != null && exp.TagIds.Any()) { var queryTempCount = new SqlQuery() .SelectCount() .From(query, "tbl"); total = Db.ExecuteScalar <long>(queryTempCount); } else { total = Db.ExecuteScalar <long>(query); } return(total); }
private List <MailMessageData> GetFilteredConversations(IDaoFactory daoFactory, MailSearchFilterData filter, out bool hasMore) { var conversations = new List <MailMessageData>(); var skipFlag = false; var chunkIndex = 0; if (filter.FromDate.HasValue && filter.FromMessage.HasValue && filter.FromMessage.Value > 0) { skipFlag = true; } var prevFlag = filter.PrevFlag.GetValueOrDefault(false); var tenantInfo = CoreContext.TenantManager.GetTenant(Tenant); var utcNow = DateTime.UtcNow; var pageSize = filter.PageSize.GetValueOrDefault(25); var daoMailInfo = daoFactory.CreateMailInfoDao(Tenant, User); while (conversations.Count < pageSize + 1) { filter.PageSize = CHUNK_SIZE * pageSize; IMessagesExp exp = null; if (!filter.IsDefault() && FactoryIndexer <MailWrapper> .Support && FactoryIndexer.CheckState(false)) { filter.Page = chunkIndex * CHUNK_SIZE * pageSize; // Elastic Limit from {index of last message} to {count of messages} List <MailWrapper> mailWrappers; if (FilterChainMessagesExp.TryGetFullTextSearchChains(filter, User, out mailWrappers)) { if (!mailWrappers.Any()) { break; } var ids = mailWrappers.Select(c => c.Id).ToList(); var query = SimpleMessagesExp.CreateBuilder(Tenant, User) .SetMessageIds(ids) .SetOrderBy(filter.Sort); if (prevFlag) { query.SetOrderAsc(!(filter.SortOrder == Defines.ASCENDING)); } else { query.SetOrderAsc(filter.SortOrder == Defines.ASCENDING); } exp = query .Build(); } } else { filter.Page = chunkIndex; // MySQL Limit from {page by size} to {size} exp = new FilterChainMessagesExp(filter, Tenant, User); } chunkIndex++; var listMessages = daoMailInfo.GetMailInfoList(exp, true) .ConvertAll(m => MessageEngine.ToMailMessage(m, tenantInfo, utcNow)); if (0 == listMessages.Count) { break; } if (skipFlag && filter.FromMessage.HasValue) { var messageData = listMessages.FirstOrDefault(m => m.Id == filter.FromMessage.Value); if (messageData != null) { // Skip chain messages by FromMessage. listMessages = listMessages.Where( m => !(m.ChainId.Equals(messageData.ChainId) && m.MailboxId == messageData.MailboxId)) .ToList(); } skipFlag = false; } foreach (var messageData in listMessages) { var existingChainIndex = conversations.FindIndex( c => c.ChainId == messageData.ChainId && c.MailboxId == messageData.MailboxId); if (existingChainIndex > -1) { if (conversations[existingChainIndex].Date < messageData.Date) { conversations[existingChainIndex] = messageData; } } else { conversations.Add(messageData); } } if (conversations.Count > pageSize) { break; } } hasMore = conversations.Count > pageSize; if (hasMore) { conversations = conversations.Take(pageSize).ToList(); } if (prevFlag) { conversations.Reverse(); } return(conversations); }
public List <MailInfo> GetMailInfoList(IMessagesExp exp, bool skipSelectTags = false) { var query = new SqlQuery(MailTable.TABLE_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.Cc.Prefix(MM_ALIAS), MailTable.Columns.Reply.Prefix(MM_ALIAS), MailTable.Columns.Subject.Prefix(MM_ALIAS), MailTable.Columns.Importance.Prefix(MM_ALIAS), MailTable.Columns.DateSent.Prefix(MM_ALIAS), MailTable.Columns.Size.Prefix(MM_ALIAS), MailTable.Columns.AttachCount.Prefix(MM_ALIAS), MailTable.Columns.Unread.Prefix(MM_ALIAS), MailTable.Columns.IsAnswered.Prefix(MM_ALIAS), MailTable.Columns.IsForwarded.Prefix(MM_ALIAS), skipSelectTags ? "\"\" as tagIds" : ConcatTagIds, MailTable.Columns.FolderRestore.Prefix(MM_ALIAS), MailTable.Columns.Folder.Prefix(MM_ALIAS), MailTable.Columns.ChainId.Prefix(MM_ALIAS), MailTable.Columns.ChainDate.Prefix(MM_ALIAS), MailTable.Columns.MailboxId.Prefix(MM_ALIAS), MailTable.Columns.CalendarUid.Prefix(MM_ALIAS), MailTable.Columns.Stream.Prefix(MM_ALIAS), MailTable.Columns.Uidl.Prefix(MM_ALIAS), MailTable.Columns.IsRemoved.Prefix(MM_ALIAS)); if (exp.TagIds != null && exp.TagIds.Any()) { query .InnerJoin(TagMailTable.TABLE_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), exp.TagIds)) .GroupBy(1) .Having(Exp.Eq(CountMailId, exp.TagIds.Count)); } if (exp.UserFolderId.HasValue) { query .InnerJoin(UserFoldertXMailTable.TABLE_NAME.Alias(UFXM_ALIAS), Exp.EqColumns(MailTable.Columns.Id.Prefix(MM_ALIAS), UserFoldertXMailTable.Columns.MailId.Prefix(UFXM_ALIAS))) .Where(UserFoldertXMailTable.Columns.FolderId.Prefix(UFXM_ALIAS), exp.UserFolderId.Value); } query.Where(exp.GetExpression()); if (exp.StartIndex.HasValue) { query.SetFirstResult(exp.StartIndex.Value); } if (exp.Limit.HasValue) { query.SetMaxResults(exp.Limit.Value); } if (!string.IsNullOrEmpty(exp.OrderBy)) { var sortField = MailTable.Columns.DateSent.Prefix(MM_ALIAS); if (exp.OrderBy == Defines.ORDER_BY_SUBJECT) { sortField = MailTable.Columns.Subject.Prefix(MM_ALIAS); } else if (exp.OrderBy == Defines.ORDER_BY_SENDER) { sortField = MailTable.Columns.From.Prefix(MM_ALIAS); } else if (exp.OrderBy == Defines.ORDER_BY_DATE_CHAIN) { sortField = MailTable.Columns.ChainDate.Prefix(MM_ALIAS); } query.OrderBy(sortField, exp.OrderAsc != null && exp.OrderAsc.Value); } var list = Db.ExecuteList(query) .ConvertAll(ToMailInfo); return(list); }