public static DataTable SelByProduct(F_Order_Sale filter)
{
List <SqlParameter> sp = new List <SqlParameter>();
//已付款的订单,不包含退货
string where = "(PaymentNO IS NOT NULL AND PaymentNO!='') AND PaymentStatus=1";
if (!string.IsNullOrEmpty(filter.stime))
{
where += " AND PayTime>=@stime";
sp.Add(new SqlParameter("stime", Convert.ToDateTime(filter.stime).ToString("yyyy/MM/dd 00:00:00")));
}
if (!string.IsNullOrEmpty(filter.etime))
{
where += " AND PayTime<=@etime";
sp.Add(new SqlParameter("etime", Convert.ToDateTime(filter.etime).ToString("yyyy/MM/dd 23:59:59")));
}
if (!string.IsNullOrEmpty(filter.nodeIds))
{
SafeSC.CheckIDSEx(filter.storeIds); where += " AND NodeID IN (" + filter.nodeIds + ")";
}
if (!string.IsNullOrEmpty(filter.storeIds))
{
SafeSC.CheckIDSEx(filter.storeIds); where += " AND UserShopID IN (" + filter.storeIds + ")";
}
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)";
DataTable saleDT = DBCenter.JoinQuery("A.*,B.NodeName", mtable, "ZL_Node", "A.NodeID=B.NodeID", "", "", sp.ToArray());
return(saleDT);
}
/// <summary>
/// 查询详细销售情况,并按分类统计
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
public static DataTable SelByClass(F_Order_Sale filter)
{
string where = "(PaymentNO IS NOT NULL AND PaymentNO!='') AND OrderStatus>=0 ";
DateTime time = DateTime.Now;
if (DateTime.TryParse(filter.stime, out time))
{
where += " AND PayTime>='" + time.ToString("yyyy/MM/dd 00:00:00") + "'";
}
if (DateTime.TryParse(filter.etime, out time))
{
where += " AND PayTime<='" + time.ToString("yyyy/MM/dd 23:59:59") + "'";
}
return(DBCenter.SelWithField("ZL_Order_ProSaleView", "SUM(AllMoney)AS AllMoney,NodeID,NodeName", where + " GROUP BY NodeID,NodeName", "ALLMoney DESC"));
}
/*
* 1,ZL_Order_ProSaleView:已付款商品信息表
* 2,ZL_Order_PayedView :已付款订单表
*/
/// <summary>
/// 根据条件,返回符合条件的已支付订单
/// </summary>
public static DataTable SelPayment(F_Order_Sale filter)
{
string stime = filter.stime, etime = filter.etime;
List <SqlParameter> sp = new List <SqlParameter>();
string where = "";
switch (filter.fast)
{
case "payed":
where = "(A.PaymentNO IS NOT NULL AND A.PaymentNO!='') AND A.PaymentStatus=1 ";
break;
default:
where = "1=1 ";
break;
}
DateTime time = DateTime.Now;
if (!string.IsNullOrEmpty(stime) && DateTime.TryParse(stime, out time))
{
where += " AND PayTime>=@stime";
sp.Add(new SqlParameter("stime", time));
}
if (!string.IsNullOrEmpty(etime) && DateTime.TryParse(etime, out time))
{
where += " AND PayTime<@etime";
sp.Add(new SqlParameter("etime", time));
}
if (!string.IsNullOrEmpty(filter.storeIds) && !filter.storeIds.Equals("-100"))
{
SafeSC.CheckIDSEx(filter.storeIds);
where += " AND A.StoreID IN (" + filter.storeIds + ")";
}
if (!string.IsNullOrEmpty(filter.orderType))
{
SafeSC.CheckIDSEx(filter.orderType);
where += " AND A.OrderType IN (" + filter.orderType + ")";
}
//不含重复的支付单
//string childSql = "SELECT PaymentID FROM ZL_OrderInfo A LEFT JOIN ZL_Payment B ON A.PaymentNo=B.PayNO WHERE " + where + "";
//DataTable payDT = DBCenter.Sel("ZL_Payment", "PaymentID IN (" + childSql + ")", "", sp);
//return payDT;
DataTable payDT = DBCenter.Sel("ZL_Order_PayedView", where, "", sp);
return(payDT);
}
public static DataTable SumMoneyByPlat(F_Order_Sale filter)
{
DataTable payDT = SelPayment(filter);
return(SumMoneyByPlat(payDT));
}
