/// <summary>
/// 获取订单时间间隔统计
/// </summary>
/// <param name="paramOrderCompleteTimeSpan"></param>
/// <returns></returns>
public IList <OrderCompleteTimeSpanInfo> QueryOrderCompleteTimeSpan(
ParamOrderCompleteTimeSpan paramOrderCompleteTimeSpan)
{
#region sql text
const string SQL_TEXT = @"
select convert(char(10),o.PubDate,120) 'Date',
count(1) 'TaskCount',
sum(case when datediff(second,o.PubDate,o.ActualDoneDate)<60*5 then 1 else 0 end) 'LessThanFiveTaskCount',
sum(case when datediff(second,o.PubDate,o.ActualDoneDate)<60*5 then 1 else 0 end)/ (count(1)*1.0) 'LessThanFiveRate' ,
sum(case when datediff(second,o.PubDate,o.ActualDoneDate) between 60*5 and 60*10 then 1 else 0 end) 'FiveToTenTaskCount',
sum(case when datediff(second,o.PubDate,o.ActualDoneDate) between 60*5 and 60*10 then 1 else 0 end) / (count(1)*1.0) 'FiveToTenRate',
sum(case when datediff(second,o.PubDate,o.ActualDoneDate) between 601 and 60*15 then 1 else 0 end) 'TenToFifteenTaskCount',
sum(case when datediff(second,o.PubDate,o.ActualDoneDate) between 601 and 60*15 then 1 else 0 end) / (count(1)*1.0) 'TenToFifteenRate',
sum(case when datediff(second,o.PubDate,o.ActualDoneDate)>60*15 then 1 else 0 end) 'GreaterThanFifteenCount',
sum(case when datediff(second,o.PubDate,o.ActualDoneDate)>60*15 then 1 else 0 end)/ (count(1)*1.0) 'GreaterThanFifteenRate',
sum(case when datediff(second,o.PubDate,o.ActualDoneDate)>60*120 then 1 else 0 end) 'GreaterThanTwoHoursCount',
sum(case when datediff(second,o.PubDate,o.ActualDoneDate)>60*1440 then 1 else 0 end) 'GreaterThanOneDayCount'
from dbo.[order] o(nolock)
where o.Status=1
and o.PubDate between @StartTime and @EndTime
group by convert(char(10),o.PubDate,120)
order by convert(char(10),o.PubDate,120) desc";
#endregion
IDbParameters dbParameters = DbHelper.CreateDbParameters();
dbParameters.Add("StartTime", DbType.DateTime).Value = paramOrderCompleteTimeSpan.StartDate.Value;
dbParameters.Add("EndTime", DbType.DateTime).Value = paramOrderCompleteTimeSpan.EndDate.Value;
return(DbHelper.QueryWithRowMapper(SuperMan_Read, SQL_TEXT, dbParameters,
new OrderCompleteTimeSpanInfoRowMapper()));
}
/// <summary>
/// 获取各城市时间间隔统计
/// </summary>
/// <param name="paramOrderCompleteTimeSpan"></param>
/// <returns></returns>
public IList <OrderCompleteTimeSpanInfo> QueryCityCompleteTimeSpan(
ParamOrderCompleteTimeSpan paramOrderCompleteTimeSpan)
{
#region sql text
const string SQL_TEXT = @"
with t as(
select convert(char(10),o.PubDate,120) 'date',count(1) 'taskCount'
from dbo.[order] o(nolock)
where o.Status=1
and o.PubDate between @StartTime and @EndTime
group by convert(char(10),o.PubDate,120)
)
--每天各城市在不同处理时长的订单数量
select temp.date 'Date',t.taskCount 'TaskCount',temp.ReceviceCity 'City',temp.cityCount 'CityTaskCount',
temp.cityCount*1.0 / t.taskCount 'CityTaskRate',
temp.a5 'LessThanFiveTaskCount',temp.a5r 'LessThanFiveRate',temp.a10 'FiveToTenTaskCount',temp.a10r 'FiveToTenRate',
temp.a15 'TenToFifteenTaskCount',temp.a15r 'TenToFifteenRate',temp.a155 'GreaterThanFifteenCount',temp.a155r 'GreaterThanFifteenRate',
temp.a120 'GreaterThanTwoHoursCount',temp.a1440 'GreaterThanOneDayCount'
from
(
select convert(char(10),o.PubDate,120) 'date',o.ReceviceCity,count(1) 'cityCount',
sum(case when datediff(second,o.PubDate,o.ActualDoneDate)<60*5 then 1 else 0 end) 'a5',
sum(case when datediff(second,o.PubDate,o.ActualDoneDate)<60*5 then 1 else 0 end)/ (count(1)*1.0) 'a5r' ,
sum(case when datediff(second,o.PubDate,o.ActualDoneDate) between 60*5 and 60*10 then 1 else 0 end) 'a10',
sum(case when datediff(second,o.PubDate,o.ActualDoneDate) between 60*5 and 60*10 then 1 else 0 end) / (count(1)*1.0) 'a10r',
sum(case when datediff(second,o.PubDate,o.ActualDoneDate) between 601 and 60*15 then 1 else 0 end) 'a15',
sum(case when datediff(second,o.PubDate,o.ActualDoneDate) between 601 and 60*15 then 1 else 0 end) / (count(1)*1.0) 'a15r',
sum(case when datediff(second,o.PubDate,o.ActualDoneDate)>60*15 then 1 else 0 end) 'a155',
sum(case when datediff(second,o.PubDate,o.ActualDoneDate)>60*15 then 1 else 0 end)/ (count(1)*1.0) 'a155r',
sum(case when datediff(second,o.PubDate,o.ActualDoneDate)>60*120 then 1 else 0 end) 'a120',
sum(case when datediff(second,o.PubDate,o.ActualDoneDate)>60*1440 then 1 else 0 end) 'a1440'
from dbo.[order] o(nolock)
where o.Status=1
and o.PubDate between @StartTime and @EndTime
group by convert(char(10),o.PubDate,120), o.ReceviceCity
--order by convert(char(10),o.PubDate,120),count(1) desc
) as temp
join t on t.date = temp.date
order by temp.date,temp.cityCount desc";
#endregion
IDbParameters dbParameters = DbHelper.CreateDbParameters();
dbParameters.Add("StartTime", DbType.DateTime).Value = paramOrderCompleteTimeSpan.StartDate.Value;
dbParameters.Add("EndTime", DbType.DateTime).Value = paramOrderCompleteTimeSpan.EndDate.Value;
return(DbHelper.QueryWithRowMapper(SuperMan_Read, SQL_TEXT, dbParameters,
new OrderCompleteTimeSpanInfoRowMapper()));
}
public object QueryCompleteTimeSpan([FromBody] ParamOrderCompleteTimeSpan 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)));
}
IList <OrderCompleteTimeSpanInfo> lstOrderTimeSpans = orderProvider.QueryOrderCompleteTimeSpan(queryInfo);
return(new ResultModel(true, string.Empty, lstOrderTimeSpans));
}
/// <summary>
/// 订单完成时间间隔查询
/// </summary>
/// <param name="queryInfo"></param>
/// <returns></returns>
public IList <OrderCompleteTimeSpanInfo> QueryOrderCompleteTimeSpan(ParamOrderCompleteTimeSpan 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.QueryCityCompleteTimeSpan(queryInfo));
}
return(orderStatisticsDao.QueryOrderCompleteTimeSpan(queryInfo));
}
/// <summary>
/// 订单完成时间间隔统计
/// </summary>
/// <returns></returns>
public ActionResult OrderCompleteTimeSpan()
{
DateTime startDate = DateTime.Now.AddDays(-7).Date;
DateTime endDate = DateTime.Now.AddDays(-1).Date;
ViewBag.StartDate = startDate;
ViewBag.EndDate = endDate;
ParamOrderCompleteTimeSpan defaultParams = new ParamOrderCompleteTimeSpan()
{
StartDate = startDate,
EndDate = endDate,
AsCityQuery = false,
};
IList <OrderCompleteTimeSpanInfo> lstOrderTimeSpans = orderProvider.QueryOrderCompleteTimeSpan(defaultParams);
ViewBag.LstOrderTimeSpans = lstOrderTimeSpans;
return(View());
}
