예제 #1
0
        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);
        }
예제 #2
0
        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);
        }
예제 #3
0
        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);
        }
예제 #4
0
        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);
        }
예제 #5
0
        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));
        }
예제 #6
0
        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);
        }
예제 #7
0
        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);
        }