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