public object QueryTaskCountPerHour([FromBody] ParamTaskPerHour queryInfo)
{
if (queryInfo == null || !queryInfo.StartDate.HasValue || !queryInfo.EndDate.HasValue)
{
return(Json(new ResultModel(false, "时间条件不允许为null", null)));
}
if ((queryInfo.EndDate.Value.AddDays(1) - queryInfo.StartDate.Value).Days > 7)
{
return(Json(new ResultModel(false, "最多查询7天数据，请更改查询条件", null)));
}
var lstTaskCounts = orderProvider.QueryTaskCountPerHour(queryInfo);
return(new ResultModel(true, string.Empty, lstTaskCounts));
}
public IList <TaskStatisticsPerHourInfo> QueryTaskCountPerHour(ParamTaskPerHour queryInfo)
{
AssertUtils.ArgumentNotNull(queryInfo, "queryInfo");
AssertUtils.ArgumentNotNull(queryInfo.StartDate, "queryInfo.StartDate");
AssertUtils.ArgumentNotNull(queryInfo.EndDate, "queryInfo.EndDate");
//向前加一天，取0点
queryInfo.EndDate = queryInfo.EndDate.Value.AddDays(1).Date;
//如果是按照城市查询
if (queryInfo.AsCityQuery)
{
return(orderStatisticsDao.QueryCityTaskCountPerHour(queryInfo));
}
return(orderStatisticsDao.QueryTaskCountPerHour(queryInfo));
}
public IList <TaskStatisticsPerHourInfo> QueryCityTaskCountPerHour(ParamTaskPerHour queryInfo)
{
#region sql text
const string sqlText = @"
select convert(char(10),o.PubDate,120) 'date',o.ReceviceCity,
sum(case Datename(hour,o.PubDate) when 0 then 1 else 0 end) c0,
sum(case Datename(hour,o.PubDate) when 1 then 1 else 0 end) c1,
sum(case Datename(hour,o.PubDate) when 2 then 1 else 0 end) c2,
sum(case Datename(hour,o.PubDate) when 3 then 1 else 0 end) c3,
sum(case Datename(hour,o.PubDate) when 4 then 1 else 0 end) c4,
sum(case Datename(hour,o.PubDate) when 5 then 1 else 0 end) c5,
sum(case Datename(hour,o.PubDate) when 6 then 1 else 0 end) c6,
sum(case Datename(hour,o.PubDate) when 7 then 1 else 0 end) c7,
sum(case Datename(hour,o.PubDate) when 8 then 1 else 0 end) c8,
sum(case Datename(hour,o.PubDate) when 9 then 1 else 0 end) c9,
sum(case Datename(hour,o.PubDate) when 10 then 1 else 0 end) c10,
sum(case Datename(hour,o.PubDate) when 11 then 1 else 0 end) c11,
sum(case Datename(hour,o.PubDate) when 12 then 1 else 0 end) c12,
sum(case Datename(hour,o.PubDate) when 13 then 1 else 0 end) c13,
sum(case Datename(hour,o.PubDate) when 14 then 1 else 0 end) c14,
sum(case Datename(hour,o.PubDate) when 15 then 1 else 0 end) c15,
sum(case Datename(hour,o.PubDate) when 16 then 1 else 0 end) c16,
sum(case Datename(hour,o.PubDate) when 17 then 1 else 0 end) c17,
sum(case Datename(hour,o.PubDate) when 18 then 1 else 0 end) c18,
sum(case Datename(hour,o.PubDate) when 19 then 1 else 0 end) c19,
sum(case Datename(hour,o.PubDate) when 20 then 1 else 0 end) c20,
sum(case Datename(hour,o.PubDate) when 21 then 1 else 0 end) c21,
sum(case Datename(hour,o.PubDate) when 22 then 1 else 0 end) c22,
sum(case Datename(hour,o.PubDate) when 23 then 1 else 0 end) c23
from dbo.[order] o(nolock)
where o.Status=1
and o.PubDate between @startdate and @enddate
group by convert(char(10),o.PubDate,120),o.ReceviceCity
order by convert(char(10),o.PubDate,120) desc,count(1) desc";
#endregion
var dbParameters = DbHelper.CreateDbParameters();
dbParameters.Add("startdate", DbType.Date).Value = queryInfo.StartDate.Value;
dbParameters.Add("enddate", DbType.Date).Value = queryInfo.EndDate.Value;
return(DbHelper.QueryWithRowMapper(SuperMan_Read, sqlText, dbParameters,
new TaskStatisticsPerHourInfoRowMapper()));
}
public ActionResult TaskCountPerHour()
{
DateTime startDate = DateTime.Now.AddDays(-7).Date;
DateTime endDate = DateTime.Now.AddDays(-1).Date;
ViewBag.StartDate = startDate;
ViewBag.EndDate = endDate;
var defaultParams = new ParamTaskPerHour()
{
StartDate = startDate,
EndDate = endDate,
AsCityQuery = false,
};
IList <TaskStatisticsPerHourInfo> lstTaskCounts = orderProvider.QueryTaskCountPerHour(defaultParams);
ViewBag.LstTaskCounts = lstTaskCounts;
return(View());
}
