private ListShopData GetDbShopData(int ShopId, DateTime starttime, DateTime endtime)
{
IList <Shopdata> listadily = new List <Shopdata>();
ListShopData lsd = new ListShopData();
lsd.List_X_Key = new List <string>();
lsd.List_X_Value = new List <Shopdata>();
try
{
listadily = bao.DailyHighlights(ShopId, starttime, endtime);
for (int i = 0; i < 7; i++)
{
Shopdata shopd = listadily.FirstOrDefault((e) => { return(e.Paydate == starttime.AddDays(i)); });
if (shopd == null)
{
shopd = new Shopdata();
shopd.Paydate = starttime.AddDays(i);
shopd.OrderCount = 0;
shopd.OrderPrice = 0;
shopd.TransactionCount = 0;
shopd.TransactionCountPrice = 0;
}
lsd.List_X_Key.Add(starttime.AddDays(i).Day.ToString());
lsd.List_X_Value.Add(shopd);
}
}
catch (Exception ex)
{
throw ex;
}
return(lsd);
}
/// <summary>
/// 每日要闻
/// </summary>
/// <param name="shopId"></param>
public IList <Shopdata> DailyHighlights(int shopId, DateTime StartTime, DateTime EndtTime)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT Convert ( VARCHAR(10), OrdersDate, 120) AS Paydate, /*日期*/");
strSql.Append(" SUM(OrdersAccounts) AS PaymentCountPrice,/*订单金额*/");
strSql.Append(" SUM(OrdersPaid) AS TransactionCountPrice,/*支付金额*/ ");
strSql.Append(" COUNT(PayDate) AS TransactionCount,/*支付笔数*/");
strSql.Append(" COUNT(OrdersId) AS PaymentCount /*订单数*/");
strSql.Append(" FROM (SELECT OrdersDate,OrdersAccounts,OrdersPaid, PayDate,OrdersId FROM tbOrdersInfo WHERE ShopId=@ShopId AND OrdersDate BETWEEN @StartTime AND @EndtTime) AS tbOrdersInfo ");
strSql.Append("GROUP BY Convert ( VARCHAR(10), OrdersDate, 120) order by Paydate ");
SqlParameter[] parameters =
{
new SqlParameter("@ShopId", SqlDbType.Int, 4),
new SqlParameter("@StartTime", SqlDbType.DateTime),
new SqlParameter("@EndtTime", SqlDbType.DateTime)
};
parameters[0].Value = shopId;
parameters[1].Value = StartTime;
parameters[2].Value = EndtTime;
IList <Shopdata> listdaily = new List <Shopdata>();
Shopdata daily = null;
using (IDataReader MyReader = DBHelper.ExecuteReader(CommandType.Text, strSql.ToString(), parameters))
{
if (MyReader.Read())
{
daily = new Shopdata()
{
Paydate = Convert.ToDateTime(MyReader.GetString(0)),
OrderPrice = Convert.ToInt64(MyReader.GetValue(1)),
TransactionCountPrice = Convert.ToInt64(MyReader.GetValue(2)),
TransactionCount = Convert.ToInt32(MyReader.GetValue(3)),
OrderCount = Convert.ToInt32(MyReader.GetValue(4))
};
listdaily.Add(daily);
}
}
return(listdaily);
}
