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