public ActionResult GetJsonData(SearchDataTable dt, MsgViewQuery svm) { int totalNum = 0; MessageRecordQuery fillter = new MessageRecordQuery(); fillter.PageIndex = dt.pageIndex; fillter.PageSize = dt.length; if (svm != null) { fillter.CreateDate = svm.CreateDate; fillter.MessageId = svm.MsgId; fillter.SendState = svm.SendState; } List <MessageRecord> msgList = msgService.GetPage(fillter, out totalNum); ReturnPageResultIList <MessageRecord> data = new ReturnPageResultIList <MessageRecord>(msgList, totalNum); IList <MsgViewModel> gmList = new List <MsgViewModel>(); if (data != null && data.DataT != null) { gmList = data.DataT.Select(g => new MsgViewModel() { Id = g.Id, UserId = g.OperatorId, Number = g.MessageId, State = g.SendState, CreateTime = g.CreateDate.ToString("yyyy-MM-dd HH:mm"), Phone = g.ToAddress, SendDate = g.SendDate == null || g.SendDate == DateTime.MinValue ? "--" : g.SendDate.ToString("yyyy-MM-dd HH:mm"), OrderDate = DateTime.MinValue.Equals(g.MsgData.OrderDate) ? "--" : g.MsgData.OrderDate.ToString("yyyy-MM-dd"), OrderName = g.MsgData.OrderName }).ToList(); } return(Json(new { recordsFiltered = data.totalRecords, recordsTotal = data.totalPages, data = gmList }, JsonRequestBehavior.AllowGet)); }
/// <summary> /// 获取消息分页信息 /// </summary> /// <param name="condition">筛选条件</param> /// <param name="totalNum">总条数</param> /// <returns>会员集合</returns> public List <MessageRecord> GetPage(MessageRecordQuery condition, out int totalNum) { totalNum = 0; List <MessageRecord> MessageRecordList = new List <MessageRecord>(); IList <DbParameter> parameList = new List <DbParameter>(); string where = GetWhereStr(condition, ref parameList); DBHelper dbHelper = new DBHelper(ConnectionString, DbProviderType.SqlServer); string strSqlQuery = @"select count(ID) from [MessageRecord] where IsDeleted=0 " + where;//查询有多少条记录 totalNum = Convert.ToInt32(dbHelper.ExecuteScalar(strSqlQuery, parameList)); parameList.Add(new SqlParameter("@pageSize", condition.PageSize)); parameList.Add(new SqlParameter("@pageIndex", condition.PageIndex)); string strSql = @"SELECT [Id] ,[MessageId] ,[ToAddress] ,[SendDate] ,[MsgData] ,[State] ,[OperatorId] ,[CreateDate] ,IsRighting FROM (select top (@pageSize*@pageIndex) [ID] ,[MessageId] ,[ToAddress] ,[SendDate] ,[MsgData] ,[State] ,[OperatorId] ,[CreateDate] ,IsRighting ,ROW_NUMBER() over(order by [state],CreateDate ) as num from [MessageRecord] where IsDeleted=0 " + where + ") as t where num between (@pageIndex - 1) * @pageSize + 1 and @pageIndex*@pageSize order by [state],CreateDate"; using (DbDataReader reader = dbHelper.ExecuteReader(strSql, parameList)) { MessageRecordList = new List <MessageRecord>(); MessageRecord MessageRecordInfo = null; while (reader.Read()) { MessageRecordInfo = GetMessageRecordFromReader(reader); MessageRecordList.Add(MessageRecordInfo); } } return(MessageRecordList); }
public List <MessageRecord> GetPage(MessageRecordQuery filterModel, out int totalNum) { totalNum = 0; //参数验证 if (filterModel == null) { ErrorMsg = ErrorCode.ParameterNull; return(null); } //获取数据 List <MessageRecord> MessageRecordList = _MessageRecordProvider.GetPage(filterModel, out totalNum); return(MessageRecordList); }
/// <summary> /// 获取所有消息信息 /// </summary> /// <param name="condition">筛选条件</param> /// <returns>会员集合</returns> public List <MessageRecord> GetAll(MessageRecordQuery condition) { List <MessageRecord> MessageRecordList = new List <MessageRecord>(); IList <DbParameter> parameList = new List <DbParameter>(); string whereSort = GetWhereStr(condition, ref parameList); DBHelper dbHelper = new DBHelper(ConnectionString, DbProviderType.SqlServer); string strSql = @"SELECT Id, MessageId, ToAddress,SendDate, MsgData, [State], OperatorId,CreateDate,IsRighting from MessageRecord where IsDeleted=0 " + whereSort; using (DbDataReader reader = dbHelper.ExecuteReader(strSql, parameList)) { MessageRecord MessageRecordInfo = null; while (reader.Read()) { MessageRecordInfo = GetMessageRecordFromReader(reader); MessageRecordList.Add(MessageRecordInfo); } } return(MessageRecordList); }
public MessageRecord GetByMsgId(string msgId) { //参数验证 if (string.IsNullOrEmpty(msgId)) { ErrorMsg = ErrorCode.ParameterNull; return(null); } MessageRecordQuery query = new MessageRecordQuery(); query.MessageId = msgId; List <MessageRecord> MessageRecordList = _MessageRecordProvider.GetAll(query); if (MessageRecordList != null && MessageRecordList.Count > 0) { return(MessageRecordList.FirstOrDefault()); } return(null); }
private string GetWhereStr(MessageRecordQuery condition, ref IList <DbParameter> parList) { if (condition == null) { return(string.Empty); } StringBuilder sb = new StringBuilder(); if (!string.IsNullOrEmpty(condition.MessageId)) { sb.Append(" and MessageId=@MessageId"); parList.Add(new SqlParameter("@MessageId", condition.MessageId)); } if (condition.SendState >= 0) { sb.Append(" and [State]=@State"); parList.Add(new SqlParameter("@State", condition.SendState)); } if (!string.IsNullOrEmpty(condition.CreateDate)) { sb.Append(" and CreateDate>@StartDate and CreateDate<@EndDate"); DateTime sdate = Convert.ToDateTime(condition.CreateDate).Date; parList.Add(new SqlParameter("@StartDate", sdate)); parList.Add(new SqlParameter("@EndDate", sdate.AddDays(1))); } if (condition.Righting >= 0) { sb.Append(" and [IsRighting]=@IsRighting"); parList.Add(new SqlParameter("@IsRighting", condition.Righting)); } return(sb.ToString()); }
public List <MessageRecord> GetAll(MessageRecordQuery filterModel = null) { List <MessageRecord> MessageRecordList = _MessageRecordProvider.GetAll(filterModel); return(MessageRecordList); }