/// <summary>
/// 获取每日手机橱窗新增商品
/// </summary>
/// <param name="stDate"></param>
/// <param name="edDate"></param>
/// <returns></returns>
public DxChartModel GetDailyNewGoodsNum(DateTime stDate, DateTime edDate)
{
StringBuilder strSql = new StringBuilder();
List <dynamic> listGoods = new List <dynamic>();
DxChartModel chartModel = new DxChartModel();
DateTime iter = stDate;
while (iter < edDate)
{
chartModel.DataList.Add(new DxChartData(iter.ToShortDateString()));
iter = iter.AddDays(1);
}
strSql.Append(
"select CAST(updateTime as date) opDate,count(*) cnt from [i200].[dbo].[t_GoodsExtend] " +
"where ge_stat=1 and updateTime between @stDate and @edDate group by CAST(updateTime as date);");
listGoods = DapperHelper.Query <dynamic>(strSql.ToString(), new { stDate = stDate, edDate = edDate }).ToList();
foreach (DxChartData day in chartModel.DataList)
{
List <dynamic> dayGoodsList = listGoods.FindAll(x => x.opDate.ToShortDateString() == day.Date);
if (dayGoodsList.Count > 0)
{
day.Data.Add("当日新增商品", (dayGoodsList.Exists(x => x.opDate.ToShortDateString() == day.Date) ? dayGoodsList.Find(x => x.opDate.ToShortDateString() == day.Date).cnt : 0));
}
else
{
day.Data.Add("当日新增商品", 0);
}
}
return(chartModel);
}
/// <summary>
/// 获取每日订单总数
/// </summary>
/// <param name="stDate"></param>
/// <param name="edDate"></param>
/// <returns></returns>
public DxChartModel GetDataSource(DateTime stDate, DateTime edDate)
{
StringBuilder strSql = new StringBuilder();
List <dynamic> listGoods = new List <dynamic>();
DxChartModel chartModel = new DxChartModel();
DateTime iter = stDate;
while (iter < edDate)
{
chartModel.DataList.Add(new DxChartData(iter.ToShortDateString()));
iter = iter.AddDays(1);
}
strSql.Append(
"select CAST(bInsertTime as date) opDate,COUNT(*) cnt from [i200].[dbo].[T_Goods_Booking] " +
"where bState in (1,2,3,4) and payType=1 and bInsertTime between @stDate and @edDate group by CAST(bInsertTime as date);");
listGoods = DapperHelper.Query <dynamic>(strSql.ToString(), new { stDate = stDate, edDate = edDate }).ToList();
foreach (DxChartData day in chartModel.DataList)
{
List <dynamic> dayGoodsList = listGoods.FindAll(x => x.opDate.ToShortDateString() == day.Date);
if (dayGoodsList.Count > 0)
{
day.Data.Add("手机橱窗当日订单", (dayGoodsList.Exists(x => x.opDate.ToShortDateString() == day.Date) ? dayGoodsList.Find(x => x.opDate.ToShortDateString() == day.Date).cnt : 0));
}
else
{
day.Data.Add("手机橱窗当日订单", 0);
}
}
return(chartModel);
}
/// <summary>
/// 获取每日UV和PV
/// </summary>
/// <param name="stDate"></param>
/// <param name="edDate"></param>
/// <returns></returns>
public DxChartModel GetDailyViewData(DateTime stDate, DateTime edDate)
{
StringBuilder strSql = new StringBuilder();
List <dynamic> uvList = new List <dynamic>();
List <dynamic> pvList = new List <dynamic>();
DxChartModel chartModel = new DxChartModel();
DateTime iter = stDate;
while (iter < edDate)
{
chartModel.DataList.Add(new DxChartData(iter.ToShortDateString()));
iter = iter.AddDays(1);
}
strSql.Append(
"select CAST(l_time as date) opDate,count(distinct l_ip) cnt from [I200_Log].[dbo].[HTML_Log] " +
"where l_time between @stDate and @edDate group by CAST(l_time as date);");
uvList = DapperHelper.Query <dynamic>(strSql.ToString(), new { stDate = stDate, edDate = edDate }).ToList();
strSql.Clear();
strSql.Append(
"select CAST(l_time as date) opDate,count(*) cnt from [I200_Log].[dbo].[HTML_Log] " +
"where l_time between @stDate and @edDate group by CAST(l_time as date);");
pvList = DapperHelper.Query <dynamic>(strSql.ToString(), new { stDate = stDate, edDate = edDate }).ToList();
foreach (DxChartData day in chartModel.DataList)
{
List <dynamic> dailyUv = uvList.FindAll(x => x.opDate.ToShortDateString() == day.Date);
List <dynamic> dailyPv = pvList.FindAll(x => x.opDate.ToShortDateString() == day.Date);
if (dailyUv.Count > 0)
{
day.Data.Add("日UV", (dailyUv.Exists(x => x.opDate.ToShortDateString() == day.Date) ? dailyUv.Find(x => x.opDate.ToShortDateString() == day.Date).cnt : 0));
}
else
{
day.Data.Add("日UV", 0);
}
if (dailyPv.Count > 0)
{
day.Data.Add("日PV", (dailyPv.Exists(x => x.opDate.ToShortDateString() == day.Date) ? dailyPv.Find(x => x.opDate.ToShortDateString() == day.Date).cnt : 0));
}
else
{
day.Data.Add("日PV", 0);
}
}
return(chartModel);
}
public DxChartModel GetDataSource(DateTime stDate, DateTime edDate, string[] sourceType,
string[] conditions)
{
StringBuilder strSql = new StringBuilder();
List <dynamic> listSale = new List <dynamic>();
List <dynamic> listReg = new List <dynamic>();
List <dynamic> listGoods = new List <dynamic>();
DxChartModel chartModel = new DxChartModel();
//List<DxChartData> dataList = new List<DxChartData>();
DateTime iter = stDate;
while (iter < edDate)
{
chartModel.DataList.Add(new DxChartData(iter.ToShortDateString()));
iter = iter.AddDays(1);
}
foreach (string conds in conditions)
{
strSql.Clear();
switch (conds)
{
case "销售":
strSql.Append(
"select CAST(saleTime as date) opDate,COUNT(*) cnt,saleFlag from [i200].[dbo].[T_SaleInfo] " +
"where saleTime between @stDate and @edDate group by CAST(saleTime as date),saleFlag;");
listSale =
DapperHelper.Query <dynamic>(strSql.ToString(), new { stDate = stDate, edDate = edDate })
.ToList();
break;
case "商品":
strSql.Append(
"select CAST(gAddTime as date) opDate,COUNT(*) cnt,gFlag from [i200].[dbo].[T_GoodsInfo] " +
"where gAddTime between @stDate and @edDate group by CAST(gAddTime as date),gFlag;");
listGoods =
DapperHelper.Query <dynamic>(strSql.ToString(), new { stDate = stDate, edDate = edDate })
.ToList();
break;
case "会员":
strSql.Append(
"select CAST(uRegTime as date) opDate,COUNT(*) cnt,uFlag from [i200].[dbo].[T_UserInfo] " +
"where uRegTime between @stDate and @edDate group by CAST(uRegTime as date),uFlag;");
listReg =
DapperHelper.Query <dynamic>(strSql.ToString(), new { stDate = stDate, edDate = edDate })
.ToList();
break;
}
}
foreach (DxChartData day in chartModel.DataList)
{
List <dynamic> daySaleList = listSale.FindAll(x => x.opDate.ToShortDateString() == day.Date);
List <dynamic> dayGoodsList = listGoods.FindAll(x => x.opDate.ToShortDateString() == day.Date);
List <dynamic> dayRegList = listReg.FindAll(x => x.opDate.ToShortDateString() == day.Date);
if (daySaleList.Count > 0)
{
if (sourceType.Contains("IPHONE"))
{
day.Data.Add("IPHONE销售",
(daySaleList.Exists(x => x.saleFlag == 1)
? daySaleList.Find(x => x.saleFlag == 1).cnt
: 0));
}
if (sourceType.Contains("Android"))
{
day.Data.Add("Android销售",
(daySaleList.Exists(x => x.saleFlag == 2)
? daySaleList.Find(x => x.saleFlag == 2).cnt
: 0));
}
if (sourceType.Contains("Web"))
{
day.Data.Add("Web销售",
(daySaleList.Exists(x => x.saleFlag == 0)
? daySaleList.Find(x => x.saleFlag == 0).cnt
: 0));
}
if (sourceType.Contains("iPad"))
{
day.Data.Add("iPad销售",
(daySaleList.Exists(x => x.saleFlag == 3)
? daySaleList.Find(x => x.saleFlag == 3).cnt
: 0));
}
}
else if (conditions.Contains("销售"))
{
foreach (string str in sourceType)
{
day.Data.Add(str + "销售", 0);
}
}
if (dayGoodsList.Count > 0)
{
if (sourceType.Contains("IPHONE"))
{
day.Data.Add("IPHONE商品",
(dayGoodsList.Exists(x => x.gFlag == 1)
? dayGoodsList.Find(x => x.gFlag == 1).cnt
: 0));
}
if (sourceType.Contains("Android"))
{
day.Data.Add("Android商品",
(dayGoodsList.Exists(x => x.gFlag == 2)
? dayGoodsList.Find(x => x.gFlag == 2).cnt
: 0));
}
if (sourceType.Contains("Web"))
{
day.Data.Add("Web商品",
(dayGoodsList.Exists(x => x.gFlag == 0)
? dayGoodsList.Find(x => x.gFlag == 0).cnt
: 0));
}
if (sourceType.Contains("iPad"))
{
day.Data.Add("iPad商品",
(dayGoodsList.Exists(x => x.gFlag == 3)
? dayGoodsList.Find(x => x.gFlag == 3).cnt
: 0));
}
}
else if (conditions.Contains("商品"))
{
foreach (string str in sourceType)
{
day.Data.Add(str + "商品", 0);
}
}
if (dayRegList.Count > 0)
{
if (sourceType.Contains("IPHONE"))
{
day.Data.Add("IPHONE会员",
(dayRegList.Exists(x => x.uFlag == 1)
? dayRegList.Find(x => x.uFlag == 1).cnt
: 0));
}
if (sourceType.Contains("Android"))
{
day.Data.Add("Android会员",
(dayRegList.Exists(x => x.uFlag == 2)
? dayRegList.Find(x => x.uFlag == 2).cnt
: 0));
}
if (sourceType.Contains("Web"))
{
day.Data.Add("Web会员",
(dayRegList.Exists(x => x.uFlag == 0)
? dayRegList.Find(x => x.uFlag == 0).cnt
: 0));
}
if (sourceType.Contains("iPad"))
{
day.Data.Add("iPad会员",
(dayRegList.Exists(x => x.uFlag == 3)
? dayRegList.Find(x => x.uFlag == 3).cnt
: 0));
}
}
else if (conditions.Contains("会员"))
{
foreach (string str in sourceType)
{
day.Data.Add(str + "会员", 0);
}
}
}
return(chartModel);
}
