public DataTable GetSalesByProduct(F_Shop_SaleReport filter)
{
List <SqlParameter> sp = new List <SqlParameter>();
string where = "(PaymentNO IS NOT NULL AND PaymentNO!='') AND OrderStatus=99 ";
if (!string.IsNullOrEmpty(filter.SDate))
{
where += " AND PayTime>=@stime";
sp.Add(new SqlParameter("stime", Convert.ToDateTime(filter.SDate).ToString("yyyy/MM/dd 00:00:00")));
}
if (!string.IsNullOrEmpty(filter.EDate))
{
where += " AND PayTime<=@etime";
sp.Add(new SqlParameter("etime", Convert.ToDateTime(filter.EDate).ToString("yyyy/MM/dd 23:59:59")));
}
if (!string.IsNullOrEmpty(filter.NodeIDS))
{
SafeSC.CheckIDSEx(filter.NodeIDS);
where += " AND NodeID IN (" + filter.NodeIDS + ")";
}
if (!string.IsNullOrEmpty(filter.ProName))
{
where += " AND ProName LIKE @proname";
sp.Add(new SqlParameter("proname", "%" + filter.ProName + "%"));
}
string mtable = "(SELECT SUM(AllMoney)AS AllMoney,SUM(Pronum)AS Pronum,ProID,ProName,Nodeid FROM ZL_Order_ProSaleView WHERE " + where + " GROUP BY ProID,ProName,Nodeid)";
return(DBCenter.JoinQuery("A.*,B.NodeName", mtable, "ZL_Node", "A.NodeID=B.NodeID", "", "AllMoney DESC", sp.ToArray()));
}
/// <summary>
/// 返回店铺销售汇总
/// </summary>
/// <param name="sid"></param>
/// <returns></returns>
public DataRow GetSummary(F_Shop_SaleReport filter)
{
string fields = "Count(ID) OrderNum,SUM(Receivablesamount) OrderTotal";
string where = "1=1 ";
if (filter.StoreID != -100)
{
where += " AND StoreID=" + filter.StoreID;
}
List <SqlParameter> sp = new List <SqlParameter>();
if (!string.IsNullOrEmpty(filter.SDate))
{
where += " AND PayTime>=@stime";
sp.Add(new SqlParameter("stime", Convert.ToDateTime(filter.SDate).ToString("yyyy/MM/dd 00:00:00")));
}
if (!string.IsNullOrEmpty(filter.EDate))
{
where += " AND PayTime<=@etime";
sp.Add(new SqlParameter("etime", Convert.ToDateTime(filter.EDate).ToString("yyyy/MM/dd 23:59:59")));
}
DataTable dt = DBCenter.SelWithField("ZL_Order_PayedView", fields, where, "", sp);
dt.Columns.Add("KDJ", typeof(string));
DataRow dr = dt.Rows[0];
dr["OrderTotal"] = DataConvert.CDouble(dr["OrderTotal"]).ToString("F2");
dr["KDJ"] = (DataConvert.CDouble(dr["OrderTotal"]) / DataConvert.CLng(dr["OrderNum"])).ToString("F2");
return(dr);
}
public DataTable GetSaleByDay(F_Shop_SaleReport filter)
{
B_OrderList orderBll = new B_OrderList();
if (string.IsNullOrEmpty(filter.SDate))
{
filter.SDate = DateTime.Now.AddMonths(-2).ToString("yyyy/MM/dd");
}
if (string.IsNullOrEmpty(filter.EDate))
{
filter.EDate = DateTime.Now.ToString("yyyy/MM/dd");
}
DateTime STime = DataConvert.CDate(filter.SDate), ETime = DataConvert.CDate(filter.EDate);
DataTable saleDT = orderBll.Report_SelByDate(STime, ETime, filter.StoreID);
DataTable dayDT = new DataTable();
dayDT.Columns.Add("date", typeof(string));
dayDT.Columns.Add("total", typeof(double));
dayDT.Columns.Add("pay_online", typeof(double));
dayDT.Columns.Add("pay_purse", typeof(double));
for (DateTime s = STime; s <= ETime; s = s.AddDays(1))
{
DataRow day = dayDT.NewRow();
//DateTime sdate = Convert.ToDateTime("{0}/{1}/{2} 00:00:00");
string sdate = s.ToString("#yyyy/MM/dd 00:00:00#"), edate = s.ToString("#yyyy/MM/dd 23:59:59#");
saleDT.DefaultView.RowFilter = "PayTime>= " + sdate + " AND PayTime<= " + edate;
day["date"] = s.ToString("yyyy-MM-dd");
day["total"] = 0;
day["pay_online"] = 0;
day["pay_purse"] = 0;
foreach (DataRow dr in saleDT.DefaultView.ToTable().Rows)
{
day["Total"] = DataConvert.CDouble(day["Total"]) + DataConvert.CDouble(dr["OrdersAmount"]);
if (Convert.ToInt32(dr["PayPlatID"]) == 0)
{
day["pay_purse"] = DataConvert.CDouble(day["pay_purse"]) + DataConvert.CDouble(dr["OrdersAmount"]);
}
else
{
day["pay_online"] = DataConvert.CDouble(day["pay_online"]) + DataConvert.CDouble(dr["OrdersAmount"]);
}
}
dayDT.Rows.Add(day);
}
return(dayDT);
}
public DataTable GetSalesByClass(F_Shop_SaleReport filter)
{
string where = "(PaymentNO IS NOT NULL AND PaymentNO!='') AND OrderStatus=99 ";
List <SqlParameter> sp = new List <SqlParameter>();
if (!string.IsNullOrEmpty(filter.SDate))
{
where += " AND PayTime>=@stime";
sp.Add(new SqlParameter("stime", Convert.ToDateTime(filter.SDate).ToString("yyyy/MM/dd 00:00:00")));
}
if (!string.IsNullOrEmpty(filter.EDate))
{
where += " AND PayTime<=@etime";
sp.Add(new SqlParameter("etime", Convert.ToDateTime(filter.EDate).ToString("yyyy/MM/dd 23:59:59")));
}
//return DBCenter.JoinQuery(field,mtable,"ZL_Order_PayedView","A.Orderlistid=B.ID",where," ALLMoney DESC");
return(DBCenter.SelWithField("ZL_Order_ProSaleView", "SUM(AllMoney)AS AllMoney,NodeID,NodeName", where + " GROUP BY NodeID,NodeName", "ALLMoney DESC", sp));
}
