/// <summary>
/// 获取黄页模型
/// </summary>
/// <returns></returns>
///
DataTable ListPageModel()
{
string strSql = "Select * from ZL_Model Where TableName like 'ZL_Reg_%' and ModelType=3";
DataTable dt = DBCenter.ExecuteTable(strSql, null);
return(dt);
}
DataTable ListModel()
{
string strSql = "Select * from ZL_Model Where ModelType=1 or ModelType=5 or ModelType=2 order by ModelID ";
DataTable dt = DBCenter.ExecuteTable(strSql, null);
return(dt);
}
/// <summary>
/// 获取互动模型
/// </summary>
/// <returns></returns>
///
DataTable ListPubModel()
{
string strSql = "Select * from ZL_Model Where ModelType=7";
DataTable dt = DBCenter.ExecuteTable(strSql, null);
return(dt);
}
/// <summary>
/// 读取用户商城模型列表
/// </summary>
/// <returns></returns>
///
DataTable ListUserShopModel()
{
string strSql = "Select * from ZL_Model Where ModelType=5";
DataTable dt = DBCenter.ExecuteTable(strSql);
return(dt);
}
/// <summary>
///获取子节点列表,不包含父节点信息
/// </summary>
/// <param name="pid">父节点ID</param>
/// <param name="isall">true所有子节点信息,false:仅下一级</param>
public DataTable SelByPid(int pid, bool isall = false)
{
DataTable dt = new DataTable();
if (isall)
{
//string sql = "with Tree as(SELECT *,cast(1 as int) as [level] FROM ZL_Node WHERE ParentID=" + pid + " UNION ALL SELECT a.*,b.[level]+1 FROM ZL_Node a JOIN Tree b on a.ParentID=b.NodeID) SELECT *,(SELECT Count(NodeID) From ZL_Node WHERE A.NodeID=ParentID) ChildCount FROM Tree AS A ORDER BY OrderID,NodeID ASC";
//DataTable dt = Sel();
//string nids = "";
//RecursionByPid(dt, pid, ref nids);
//nids = StrHelper.PureIDSForDB(nids);
//dt.DefaultView.RowFilter = "NodeID IN(" + nids + ")";
//return dt.DefaultView.ToTable();
//WITH Tree AS(
//SELECT * FROM ZL_Node WHERE ParentID=0 UNION ALL SELECT A.* FROM ZL_Node A JOIN Tree B on A.ParentID=B.NodeID)
//SELECT * FROM Tree ORDER BY OrderID,NodeID ASC
string sql = "with Tree as(SELECT * FROM ZL_Node WHERE ParentID=" + pid + " UNION ALL SELECT a.* FROM ZL_Node a JOIN Tree b on a.ParentID=b.NodeID) SELECT *,(SELECT Count(NodeID) From ZL_Node WHERE A.NodeID=ParentID AND ZStatus=99) ChildCount FROM Tree AS A ORDER BY OrderID,NodeID ASC";
//获取父节点下子级后,再去除父级
string oracle = "SELECT * FROM(SELECT * FROM ZL_Node START WITH NODEID IN(" + pid + ") CONNECT BY nocycle prior NodeID=ParentID )WHERE NodeID!=" + pid;
dt = DBCenter.ExecuteTable(DBCenter.GetSqlByDB(sql, oracle));
}
else
{
string fields = "A.*,(SELECT Count(NodeID) From ZL_Node WHERE A.NodeID=ParentID) ChildCount ";
//string sql = "SELECT " + fields + " FROM " + TbName + " A WHERE ParentID=" + pid+" ORDER BY OrderID,NodeID ASC";
dt = DBCenter.SelWithField(TbName, fields, "ParentID=" + pid, defOrder);
}
dt.DefaultView.RowFilter = "ZStatus IS NULL OR ZStatus NOT IN ('" + (int)ZLEnum.ConStatus.Recycle + "')";
dt = dt.DefaultView.ToTable();
return(dt);
}
public static int SelFirstNodeID(string TbName, string PK, int nodeid, ref string nodeTree)
{
int firstNodeID = 0;
if (nodeid < 1)
{
return(firstNodeID);
}
string sql = "with f as(SELECT * FROM " + TbName + " WHERE " + PK + "=" + nodeid + " UNION ALL SELECT A.* FROM " + TbName + " A, f WHERE a." + PK + "=f.ParentID) SELECT * FROM " + TbName + " WHERE " + PK + " IN(SELECT " + PK + " FROM f)";
string oracle = "SELECT * FROM " + TbName + " Where " + PK + "=" + nodeid;//[need deal]
DataTable dt = DBCenter.ExecuteTable(DBCenter.GetSqlByDB(sql, oracle));
if (dt.Rows.Count < 1)
{
return(firstNodeID);
}
//顺序无法确定,首位可能是第一,也可能是最后???
foreach (DataRow dr in dt.Rows)
{
if (DataConvert.CLng(dr["ParentID"]) == 0)
{
firstNodeID = Convert.ToInt32(dr[PK]);
}
nodeTree += dr[PK] + ",";
}
nodeTree = nodeTree.Trim(',');
return(firstNodeID);
}
/// <summary>
/// 选取最近几次的不重复的发票信息(根据Head)
/// </summary>
/// <returns></returns>
public DataTable SelForTop(int uid)
{
string childSql = "SELECT max(a.ID) FROM ZL_Order_Invoice A LEFT JOIN ZL_Orderinfo B ON A.ORDERID = B.ID WHERE B.UserID=" + uid + " GROUP BY A.InvHead, b.Userid";
string sql = "SELECT TOP 5 * FROM ZL_Order_Invoice WHERE ID IN (" + childSql + ")";
DataTable dt = DBCenter.ExecuteTable(sql);
return(dt);
}
public DataTable SelByJS(Com_Filter filter)
{
List <SqlParameter> sp = new List <SqlParameter>();
string where = GetWhere(filter, sp);
return(DBCenter.ExecuteTable("SELECT A.Attribute,SUM(CAST(A.AllMoney_Json AS MONEY)) AllMoney_Json "
+ " FROM ZL_CartPro A LEFT JOIN ZL_OrderInfo B ON A.OrderListID=B.ID "
+ " WHERE A.Attribute!='' AND " + where + " GROUP BY Attribute", sp));
}
public static DataTable SelByPid(string TbName, string PK, int pid)
{
string sql = "with Tree as(SELECT * FROM {0} WHERE ParentID=" + pid + " UNION ALL SELECT a.* FROM {0} a JOIN Tree b on a.ParentID=b.{1}) SELECT * FROM Tree AS A ORDER {1} ASC";
//获取父节点下子级后,再去除父级
string oracle = "SELECT * FROM(SELECT * FROM {0} START WITH {1} IN(" + pid + ") CONNECT BY nocycle prior {1}=ParentID )WHERE {1}!=" + pid;
sql = string.Format(sql, TbName, PK);
oracle = string.Format(oracle, TbName, PK);
DataTable dt = DBCenter.ExecuteTable(DBCenter.GetSqlByDB(sql, oracle));
return(dt);
}
/// <summary>
/// 获取子级链,包含起始ID
/// </summary>
private string SelChildTree(string tbname, string pk, string pfield, int startID)
{
string ids = "";
string sql = "WITH TREE as(SELECT * FROM {ZL_Node} WHERE {ParentID}=" + startID + " UNION ALL SELECT a.* FROM {ZL_Node} a JOIN Tree b on a.{ParentID}=b.{NodeID}) SELECT {NodeID} FROM Tree AS A";
sql = sql.Replace("{ZL_Node}", tbname).Replace("{NodeID}", pk).Replace("{ParentID}", pfield);
DataTable dt = DBCenter.ExecuteTable(sql);
foreach (DataRow dr in dt.Rows)
{
ids += dr[pk] + ",";
}
return(ids.Trim(','));
}
public DataTable SelOrderCount()
{
string tbname = "ZL_Orderinfo";
string sql = "SELECT COUNT(ID) AS [all]";
sql += ",(SELECT COUNT(ID) FROM " + tbname + " WHERE Aside=0 AND PaymentStatus=" + (int)M_OrderList.PayEnum.HasPayed + " AND StateLogistics=" + (int)M_OrderList.ExpEnum.NoSend + ") unexp";
sql += ",(SELECT COUNT(ID) FROM " + tbname + " WHERE Aside=0 AND PaymentStatus=" + (int)M_OrderList.PayEnum.NoPay + ") unpaid";
sql += ",(SELECT COUNT(ID) FROM " + tbname + " WHERE Aside=0 AND StateLogistics>" + (int)M_OrderList.ExpEnum.NoSend + ") exped";
sql += ",(SELECT COUNT(ID) FROM " + tbname + " WHERE Aside=0 AND OrderStatus=" + (int)M_OrderList.StatusEnum.OrderFinish + ") finished";
sql += ",(SELECT COUNT(ID) FROM " + tbname + " WHERE Aside=0 AND PaymentStatus=" + (int)M_OrderList.PayEnum.RequestRefund + ") unrefund";
sql += ",(SELECT COUNT(ID) FROM " + tbname + " WHERE Aside=0 AND PaymentStatus=" + (int)M_OrderList.PayEnum.Refunded + ") refunded";
sql += ",(SELECT COUNT(ID) FROM " + tbname + " WHERE Aside=1) recycle";
sql += " FROM " + tbname + " WHERE Aside=0";
return(DBCenter.ExecuteTable(sql));
}
/// <summary>
/// 返回父级链,包含起始ID
/// </summary>
/// <param name="tbname">ZL_Node</param>
/// <param name="pk">NodeID</param>
/// <param name="pfield">示例:ParentID</param>
/// <param name="startid">起始的ID值,如UserID的值</param>
/// <param name="ids">返回的层级IDS</param>
/// <returns></returns>
private string SelParentTree(string tbname, string pk, string pfield, int startID)
{
string ids = "";
if (startID < 1)
{
return(ids);
}
string sql = "WITH f AS(SELECT * FROM {ZL_Node} WHERE {NodeID}=" + startID + " UNION ALL SELECT A.* FROM {ZL_Node} A, f WHERE a.{NodeID}=f.{ParentID}) SELECT * FROM {ZL_Node} WHERE {NodeID} IN(SELECT {NodeID} FROM f)";
string oracle = "SELECT * FROM {tbname} START WITH {NodeID} =" + startID + " CONNECT BY PRIOR {ParentID} = {NodeID}";
sql = sql.Replace("{ZL_Node}", tbname).Replace("{NodeID}", pk).Replace("{ParentID}", pfield);
DataTable dt = DBCenter.ExecuteTable(DBCenter.GetSqlByDB(sql, oracle));
foreach (DataRow dr in dt.Rows)
{
ids += dr[pk] + ",";
}
return(ids.Trim(','));
}
public int SelFirstID(int id, ref string nodeTree)
{
if (id < 1)
{
return(0);
}
string sql = "with f as(SELECT * FROM " + TbName + " WHERE ID=" + id + " UNION ALL SELECT A.* FROM " + TbName + " A, f WHERE a.ID=f.PID) SELECT * FROM " + TbName + " WHERE ID IN(SELECT ID FROM f)";
DataTable dt = DBCenter.ExecuteTable(sql);
if (dt.Rows.Count < 1)
{
return(0);
}
foreach (DataRow dr in dt.Rows)
{
nodeTree += dr[PK] + ",";
}
nodeTree = nodeTree.Trim(',');
return(DataConvert.CLng(dt.Rows[0][PK]));
}
/// <summary>
/// 获取指定节点的起始父节点,并生成节点树,不包含0
/// 注意节点不要存在循环(如NodeID=ParentID,或圆形)
/// </summary>
public int SelFirstNodeID(int nodeid, ref string nodeTree)
{
if (nodeid < 1)
{
return(0);
}
string sql = "with f as(SELECT * FROM ZL_Node WHERE NodeID=" + nodeid + " UNION ALL SELECT A.* FROM ZL_Node A, f WHERE a.NodeID=f.ParentID) SELECT * FROM ZL_Node WHERE NodeID IN(SELECT NodeID FROM f)";
//string oracle = "SELECT * FROM ZL_Node START WITH NODEID =" + nodeid + " CONNECT BY PRIOR ParentID = NodeID";
string oracle = "SELECT * FROM ZL_Node Where NodeID=" + nodeid;//[need deal]
DataTable dt = DBCenter.ExecuteTable(DBCenter.GetSqlByDB(sql, oracle));
if (dt.Rows.Count < 1)
{
return(0);
}
foreach (DataRow dr in dt.Rows)
{
nodeTree += dr["NodeID"] + ",";
}
nodeTree = nodeTree.Trim(',');
return(DataConvert.CLng(dt.Rows[0]["NodeID"]));
}
public IActionResult Default_Result()
{
string SQL = EncryptHelper.Base64Decrypt(Request.Form["sql"]);
DataTable dt = null;
try
{
if (!string.IsNullOrEmpty(SQL))
{
dt = DBCenter.ExecuteTable(SQL);
}
else
{
throw new Exception("未指定需要查询的参数");
}
}
catch (Exception ex)
{
dt = null;
ViewBag.err = ex.Message;
}
return(PartialView());
}
public PageSetting SelForOrderList(Filter_Order filter)
{
string where = "OrderType!=" + (int)M_OrderList.OrderEnum.Hide;
string whereCount = " WHERE OrderType!=" + (int)M_OrderList.OrderEnum.Hide;
List <SqlParameter> sp = new List <SqlParameter>();
//是否包含回收站订单
if (filter.aside != -100)
{
where += " AND Aside=" + filter.aside;
}
#region 用户中心快速筛选
switch (filter.fast)
{
case "all": //全部(不含回收站)
where += " AND Aside=0";
break;
case "unpaid": //待付款==状态为未付款的
where += " AND PaymentStatus=" + (int)M_OrderList.PayEnum.NoPay;
break;
case "prepay": //已预付款(尚未支付尾款的订单)
where += " AND Aside=0 AND Delivery=1 AND IsCount=0";
break;
case "paid": //已支付(只支付了预付款的不在此列)
where += " AND Aside=0 AND ((PaymentStatus=" + (int)M_OrderList.PayEnum.HasPayed + " AND Delivery=0) OR (Delivery=1 AND IsCount=1 AND Settle=1)) ";
break;
case "needpay": //需付款
where += " AND Aside=0 AND PaymentStatus=0";
break;
case "receive": //需确认收货
where += " AND Aside=0 AND StateLogistics=1";
break;
case "comment": //已付款未评价
//where += " AND (OrderStatus=" + (int)M_OrderList.StatusEnum.OrderFinish + " AND StateLogistics=" + (int)M_OrderList.ExpEnum.HasReceived + ")";
//where += " AND (SELECT COUNT(*) FROM ZL_CartPro WHERE Orderlistid=ID AND (AddStatus IS NULL OR AddStatus=''))>0";//AddStatus中会有退货记录,所以筛选为必须为空
//break;
case "finish": //客户已付款收货 ||客户已完成退货
{
where += string.Format(" AND ({0} OR {1})",
"(OrderStatus=" + (int)M_OrderList.StatusEnum.OrderFinish + " AND StateLogistics=" + (int)M_OrderList.ExpEnum.HasReceived + ")",
"(PaymentStatus=" + (int)M_OrderList.PayEnum.Refunded + ")");
}
break;
case "issure":
where += " AND IsSure=0 ";
break;
case "recycle": //订单回收站
where = "OrderType!=" + (int)M_OrderList.OrderEnum.Hide + " AND Aside=1";
break;
}
#endregion
#region 后台快速筛选
switch (filter.addon)
{
case "unpaid": //待付款==状态为未付款的
where += " AND PaymentStatus=" + (int)M_OrderList.PayEnum.NoPay;
break;
case "prepay": //已预付款(尚未支付尾款的订单)
where += " AND Delivery=1 AND IsCount=0";
break;
case "paid":
where += " AND ((PaymentStatus=" + (int)M_OrderList.PayEnum.HasPayed + " AND Delivery=0) OR (Delivery=1 AND IsCount=1 AND Settle=1)) ";
//where += " AND PaymentStatus=" + (int)M_OrderList.PayEnum.HasPayed;
break;
case "unexp": //待发货==已付款+未发货
where += " AND PaymentStatus=" + (int)M_OrderList.PayEnum.HasPayed + " AND StateLogistics=" + (int)M_OrderList.ExpEnum.NoSend;
break;
case "exped": //已发货==大于未发货状态的订单
where += " AND StateLogistics>" + (int)M_OrderList.ExpEnum.NoSend;
break;
case "finished":
//where += " AND OrderStatus=" + (int)M_OrderList.StatusEnum.OrderFinish + " AND StateLogistics=" + (int)M_OrderList.ExpEnum.HasReceived;
//后期移除,暂时支持预付
where += " AND (OrderStatus=" + (int)M_OrderList.StatusEnum.OrderFinish + " AND StateLogistics=" + (int)M_OrderList.ExpEnum.HasReceived + ")";
break;
case "unrefund":
where += " AND PaymentStatus=" + (int)M_OrderList.PayEnum.RequestRefund;
break;
case "refunded":
where += " AND PaymentStatus=" + (int)M_OrderList.PayEnum.Refunded;
break;
case "recycle": //订单回收站==已关闭
where = " Aside=1 ";
break;
case "all": //全部(不含回收站)
default:
break;
}
#endregion
//店铺类型
switch (filter.storeType)
{
case "all":
break;
case "store":
where += " AND StoreID>0";
whereCount += " AND StoreID>0";
break;
case "shop":
where += " AND StoreID=0 ";
whereCount += " AND StoreID=0";
break;
default:
if (DataConvert.CLng(filter.storeType) > 0)
{
where += " AND StoreID=" + DataConvert.CLng(filter.storeType) + " ";
whereCount += " AND StoreID=" + DataConvert.CLng(filter.storeType) + " ";
}
break;
}
if (filter.isSure != -100)
{
where += " AND IsSure=" + filter.isSure;
}
//订单类型，未指定则抽出常规订单
if (string.IsNullOrEmpty(filter.orderType))
{
where += " AND OrderType IN (0,1,4)";
whereCount += " AND OrderType IN (0,1,4)";
}
else if (filter.orderType.Equals("-100") || filter.orderType.Equals("-1"))
{
}
else
{
SafeSC.CheckIDSEx(filter.orderType);
where += " AND OrderType IN (" + filter.orderType + ")";
whereCount += " AND OrderType IN (" + filter.orderType + ")";
}
//商品名,订单号,用户名,手机号,用户ids
if (!string.IsNullOrEmpty(filter.proname))
{
where += " AND ProName LIKE @proname"; sp.Add(new SqlParameter("proname", "%" + filter.proname + "%"));
}
if (!string.IsNullOrEmpty(filter.orderno))
{
where += " AND OrderNo LIKE @orderno"; sp.Add(new SqlParameter("orderno", "%" + filter.orderno + "%"));
}
if (!string.IsNullOrEmpty(filter.reuser))
{
where += " AND (Rename LIKE @reuser OR Receiver LIKE @reuser)"; sp.Add(new SqlParameter("reuser", "%" + filter.reuser + "%"));
}
if (!string.IsNullOrEmpty(filter.mobile))
{
where += " AND MobileNum LIKE @mobile"; sp.Add(new SqlParameter("mobile", "%" + filter.mobile + "%"));
}
if (!string.IsNullOrEmpty(filter.uids) && SafeSC.CheckIDS(filter.uids))
{
where += " AND UserID IN (" + filter.uids + ")";
}
//下单日期
if (!string.IsNullOrEmpty(filter.stime))
{
DateTime result = DateTime.Now;
if (DateTime.TryParse(filter.stime, out result))
{
where += " AND AddTime>=@stime"; sp.Add(new SqlParameter("stime", result.ToString("yyyy/MM/dd 00:00:00")));
}
}
if (!string.IsNullOrEmpty(filter.etime))
{
DateTime result = DateTime.Now;
if (DateTime.TryParse(filter.etime, out result))
{
where += " AND AddTime<=@etime"; sp.Add(new SqlParameter("etime", result.ToString("yyyy/MM/dd 23:59:59")));
}
}
//发货时间
if (!string.IsNullOrEmpty(filter.expstime) || !string.IsNullOrEmpty(filter.expetime))
{
where += " AND ExpSTime IS NOT NULL ";
}
if (!string.IsNullOrEmpty(filter.expstime))//按发货日期筛选
{
DateTime result = DateTime.Now;
if (DateTime.TryParse(filter.expstime, out result))
{
where += " AND ExpSTime>=@expstime"; sp.Add(new SqlParameter("expstime", result.ToString("yyyy/MM/dd 00:00:00")));
}
}
if (!string.IsNullOrEmpty(filter.expetime))
{
DateTime result = DateTime.Now;
if (DateTime.TryParse(filter.expetime, out result))
{
where += " AND ExpSTime<=@expetime"; sp.Add(new SqlParameter("expetime", result.ToString("yyyy/MM/dd 23:59:59")));
}
}
//搜索,支持指定条件
if (!string.IsNullOrEmpty(filter.skey))
{
sp.Add(new SqlParameter("skey", "%" + filter.skey + "%"));
switch (filter.stype)
{
case "exp":
where += " AND ExpressDelivery LIKE @skey";
break;
case "oid":
where += " AND ID= " + DataConvert.CLng(filter.skey);
break;
}
}
if (!String.IsNullOrEmpty(filter.oids))
{
SafeSC.CheckIDSEx(filter.oids);
where += " AND ID IN (" + filter.oids + ")";
}
if (filter.payType != -100)
{
where += " AND PayType=" + filter.payType;
}
////商品信息
//string mtbname = "(SELECT *,LinPrice,Thumbnails,GuessXML,ProCode FROM ZL_CartPro A LEFT JOIN ZL_Commodities B ON ProID=ID)";
////订单与用户信息
//string stbname = "(SELECT *,ParentUserID,HoneyName,ZL_Order_Exp.CDate AS ExpSTime FROM ZL_OrderInfo A LEFT JOIN ZL_User B ON UserID=UserID LEFT JOIN ZL_Order_Exp ON ExpressNum=ZL_Order_Exp.ID)";
//string fields = "ID AS CartID,PClass,ProID,ProName,Shijia,Pronum,AddStatus,AllMoney,StoreID,Thumbnails,GuessXML"//ZL_CartPro
// + ",Freight,ID,OrderNo,AddUser,AddTime,OrderStatus,PaymentStatus,OrdersAmount,StateLogistics,Aside,Delivery,Service_charge"//ZL_OrderInfo
// + ",OrderType,OrderMessage,Receivablesamount,ExpressNum,PaymentNo" //ZL_OrderInfo
// + ",Shengfen,Jiedao,ZipCode,MobileNum,Phone,Email,Receiver,Rename,ExpressDelivery"//地址信息 ZL_OrderInfo
// + ",UserID,ParentUserID,HoneyName"//User
// + ",ExpSTime";//ZL_Order_Exp
//PageSetting setting=PageSetting.Double(filter.cpage,filter.psize,mtbname,stbname,"ID","OrderListID=ID",where,"ID DESC",sp,fields);
//return DBCenter.JoinQuery(fields, mtbname, stbname, "OrderListID=ID", where, "ID DESC", sp.ToArray());
string view = "ZL_CartProView";
//只取订单的ID
PageSetting setting = PageSetting.Single(filter.cpage, filter.psize, view, "ID", where, " GROUP BY ID ORDER BY ID DESC", sp, "ID");
DBCenter.SelPage(setting);
string ids = "";
foreach (DataRow dr in setting.dt.Rows)
{
ids += dr["id"] + ",";
}
ids = ids.TrimEnd(',');
setting.itemCount = DataConvert.CLng(DBCenter.Count("(SELECT ID FROM ZL_CartProView WHERE " + where + " GROUP BY ID) A", "", sp));
setting.pageCount = SqlBase.GetPageCount(setting.itemCount, setting.psize);
//根据订单ID取出购物车中的数据,需要进行名称等筛选
if (!string.IsNullOrEmpty(ids))
{
sp.Clear();
string cartWhere = "ID IN (" + ids + ") ";
if (!string.IsNullOrEmpty(filter.proname))
{
cartWhere += " AND ProName LIKE @proname"; sp.Add(new SqlParameter("proname", "%" + filter.proname + "%"));
}
setting.dt = DBCenter.Sel(view, cartWhere, "ID DESC", sp);
}
if (filter.needCount)
{
string tbname = view;
filter.countMod = new OrderCount();
string sql = "SELECT COUNT(ID) AS [all]";
sql += ",(SELECT COUNT(ID) FROM " + tbname + whereCount + " AND Aside=0 AND PaymentStatus=" + (int)M_OrderList.PayEnum.HasPayed + " AND StateLogistics=" + (int)M_OrderList.ExpEnum.NoSend + ") unexp";
sql += ",(SELECT COUNT(ID) FROM " + tbname + whereCount + " AND Aside=0 AND PaymentStatus=" + (int)M_OrderList.PayEnum.NoPay + ") unpaid";
sql += ",(SELECT COUNT(ID) FROM " + tbname + whereCount + " AND Aside=0 AND Delivery=1 AND IsCount=0) prepay";
sql += ",(SELECT COUNT(ID) FROM " + tbname + whereCount + " AND ((PaymentStatus=1 AND Delivery=0) OR (Delivery=1 AND IsCount=1 AND Settle=1))) paid";
sql += ",(SELECT COUNT(ID) FROM " + tbname + whereCount + " AND Aside=0 AND StateLogistics>" + (int)M_OrderList.ExpEnum.NoSend + ") exped";
sql += ",(SELECT COUNT(ID) FROM " + tbname + whereCount + " AND Aside=0 AND OrderStatus=" + (int)M_OrderList.StatusEnum.OrderFinish + " AND StateLogistics=" + (int)M_OrderList.ExpEnum.HasReceived + ") finished";
sql += ",(SELECT COUNT(ID) FROM " + tbname + whereCount + " AND Aside=0 AND PaymentStatus=" + (int)M_OrderList.PayEnum.RequestRefund + ") unrefund";
sql += ",(SELECT COUNT(ID) FROM " + tbname + whereCount + " AND Aside=0 AND PaymentStatus=" + (int)M_OrderList.PayEnum.Refunded + ") refunded";
sql += ",(SELECT COUNT(ID) FROM " + tbname + whereCount + " AND Aside=1) recycle";
sql += " FROM " + tbname + whereCount + " AND Aside=0";
DataTable dt = DBCenter.ExecuteTable(sql);
filter.countMod.all = DataConvert.CLng(dt.Rows[0]["all"]);
filter.countMod.unexp = DataConvert.CLng(dt.Rows[0]["unexp"]);
filter.countMod.unpaid = DataConvert.CLng(dt.Rows[0]["unpaid"]);
filter.countMod.prepay = DataConvert.CLng(dt.Rows[0]["prepay"]);
filter.countMod.paid = DataConvert.CLng(dt.Rows[0]["paid"]);
filter.countMod.exped = DataConvert.CLng(dt.Rows[0]["exped"]);
filter.countMod.finished = DataConvert.CLng(dt.Rows[0]["finished"]);
filter.countMod.unrefund = DataConvert.CLng(dt.Rows[0]["unrefund"]);
filter.countMod.refunded = DataConvert.CLng(dt.Rows[0]["refunded"]);
filter.countMod.recycle = DataConvert.CLng(dt.Rows[0]["recycle"]);
}
return(setting);
}
public DataTable SelByType(string ids)
{
SafeSC.CheckIDSEx(ids);
return(DBCenter.ExecuteTable("SELECT * FROM " + TbName + " WHERE ModelType IN(" + ids + ") "));
}
/// <summary>
/// 已有多少人参加了活动
/// </summary>
/// <param name="luckid"></param>
/// <returns></returns>
public int Count(int luckid)
{
return(DataConvert.CLng(
DBCenter.ExecuteTable("SELECT COUNT(USERID) FROM (SELECT UserID FROM ZL_WX_LuckDrawList GROUP BY UserID) AS A").Rows[0][0]
));
}
