public ActionResult WeChat(int?id, string weixinId, string startDate, string endDate)
{
if (Request.IsAjaxRequest())
{
//列表分页开始
var model = new PagedList <CBMkWeixinQuestion>();
var filter = new ParaMkWeixinQuestionFilter {
WeixinId = weixinId
};
if (!string.IsNullOrEmpty(startDate))
{
filter.StartTime = DateTime.Parse(startDate);
}
if (!string.IsNullOrEmpty(endDate))
{
filter.EndTime = DateTime.Parse(endDate);
}
if (filter.EndTime != null)
{
filter.EndTime = filter.EndTime.Value.AddDays(1);
}
var modelRef = new Pager <CBMkWeixinQuestion> {
CurrentPage = id ?? 1, PageSize = model.PageSize
};
WeChatBo.Instance.GetMkWeixinQuestionStaticsList(ref modelRef, filter);
model.TotalItemCount = modelRef.TotalRows;
model.TData = modelRef.Rows;
model.CurrentPageIndex = modelRef.CurrentPage;
return(PartialView("_AjaxPagerWeChat", model));
}
return(View());
}
/// <summary>
/// 分页查询微信咨询信息（统计）列表
/// </summary>
/// <param name="pager">微信咨询列表分页对象</param>
/// <param name="filter">查询条件</param>
/// <returns>void</returns>
/// <remarks>
/// 2013-11-07 郑荣华 创建
/// </remarks>
public void GetMkWeixinQuestionStaticsList(ref Pager <CBMkWeixinQuestion> pager,
ParaMkWeixinQuestionFilter filter)
{
IMkWeixinQuestionDao.Instance.GetMkWeixinQuestionStaticsList(ref pager, filter);
if (pager.Rows != null)
{
foreach (var item in pager.Rows)
{
var userInfo = Hyt.BLL.Weixin.CallCenterReplyBo.Instance.GetUserInfo(item.WeixinId);
item.ShowName = userInfo == null ? item.WeixinId : userInfo.nickname;
}
#region 微信ID展示为实际昵称，数据库里记录的为OpenId，查询需特殊处理 2013-12-31 陶辉 添加
if (!string.IsNullOrEmpty(filter.WeixinId))
{
var rowsList = new List <CBMkWeixinQuestion>(pager.Rows);
rowsList = rowsList.FindAll(r => r.ShowName == filter.WeixinId);
pager.Rows = rowsList;
pager.TotalRows = rowsList.Count;
}
#endregion
}
}
/// <summary>
/// 分页查询微信咨询信息（统计）列表
/// </summary>
/// <param name="pager">微信咨询列表分页对象</param>
/// <param name="filter">查询条件</param>
/// <remarks>
/// 2013-11-07 郑荣华 创建
/// 2013-12-31 陶辉 修改
/// </remarks>
public override void GetMkWeixinQuestionStaticsList(ref Pager <CBMkWeixinQuestion> pager, ParaMkWeixinQuestionFilter filter)
{
using (var context = Context.UseSharedConnection(true))
{
const string sqlSelect = @"t.*,CASE b.newsnum when null then 0 else b.newsnum END newsnum";
const string sqlFrom = @"MkWeixinQuestion t left join
(select t.weixinid,count(1) newsnum from MkWeixinQuestion t where t.status=@status and t.type=@type group by t.weixinid)b
on t.weixinid=b.weixinid";
const string sqlWhere = @"t.type=@type and exists (select 1 from
(select t.weixinid,max(t.messagestime) lasttime from MkWeixinQuestion t where t.type=@type group by t.weixinid) a
where t.weixinid=a.weixinid and t.messagestime=a.lasttime)
--and (@weixinid is null or charindex(t.weixinid,@weixinid)>0)
and (@startdate is null or t.messagestime>=@startdate) and (@enddate is null or t.messagestime<=@enddate)";
#region sqlcount
const string sqlCount = @"select count(1) from " + sqlFrom + " where " + sqlWhere;
pager.TotalRows = context.Sql(sqlCount)
.Parameter("status", (int)MarketingStatus.微信咨询消息状态.未读)
.Parameter("type", (int)MarketingStatus.微信咨询类型.咨询)
//.Parameter("weixinid", filter.WeixinId)
//.Parameter("weixinid", filter.WeixinId)
.Parameter("startdate", filter.StartTime)
.Parameter("enddate", filter.EndTime)
.QuerySingle <int>();
#endregion
pager.Rows = context.Select <CBMkWeixinQuestion>(sqlSelect)
.From(sqlFrom)
.Where(sqlWhere)
.Parameter("status", (int)MarketingStatus.微信咨询消息状态.未读)
.Parameter("type", (int)MarketingStatus.微信咨询类型.咨询)
//.Parameter("weixinid", filter.WeixinId)
//.Parameter("weixinid", filter.WeixinId)
.Parameter("startdate", filter.StartTime)
.Parameter("enddate", filter.EndTime)
.OrderBy("t.MessagesTime desc")
.Paging(pager.CurrentPage, pager.PageSize)
.QueryMany();
}
}
/// <summary> /// 分页查询微信咨询信息（统计）列表 /// </summary> /// <param name="pager">微信咨询列表分页对象</param> /// <param name="filter">查询条件</param> /// <returns></returns> /// <remarks> /// 2013-11-07 郑荣华 创建 /// </remarks> public abstract void GetMkWeixinQuestionStaticsList(ref Pager <CBMkWeixinQuestion> pager, ParaMkWeixinQuestionFilter filter);
