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);
}
