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())); }
public DataTable Sel(int userid = 0, string stype = "", string skey = "") { string where = "1=1"; List <SqlParameter> sp = new List <SqlParameter>(); if (userid > 0) { where += " AND A.UserID=" + userid; } if (!string.IsNullOrEmpty(stype) && !string.IsNullOrEmpty(skey)) { sp.Add(new SqlParameter("skey", "%" + skey + "%")); switch (stype) { case "uname": where += " AND A.PeopleName LIKE @skey"; break; case "bname": where += " AND A.BankName LIKE @skey"; break; case "bnum": where += " AND A.CardNum LIKE @skey"; break; } } return(DBCenter.JoinQuery("A.*,B.UserName", TbName, "ZL_User", "A.UserID=B.UserID", where, "AddTime DESC", sp.ToArray())); }
public DataTable Search(string ztype, string ids, string remind, int status, int uid) { List <SqlParameter> sp = new List <SqlParameter>(); string where = " 1=1 "; if (!string.IsNullOrEmpty(ztype)) { where += " AND A.ZType=@ztype"; sp.Add(new SqlParameter("ztype", ztype)); } if (!string.IsNullOrEmpty(remind)) { where += " AND A.Remind=@remind"; sp.Add(new SqlParameter("remind", remind)); } if (status != -100) { where += " AND A.Status=" + status; } if (uid > 0) { where += " AND A.UserID=" + uid; } if (!string.IsNullOrEmpty(ids)) { SafeSC.CheckIDSEx(ids); where += " AND A.ID IN (" + ids + ")"; } return(DBCenter.JoinQuery("A.*,B.UserFace", TbName, "ZL_User_PlatView", "A.UserID=B.UserID", where, "A.ID DESC", sp.ToArray())); }
//是否仅用于list public DataTable Sel(int uid, int askid, string skey = "") { string where = " 1=1 "; List <SqlParameter> sp = new List <SqlParameter>(); if (uid != -100) { where += " AND A.UserID=" + uid; } if (askid != -100 && askid != 0) { where += " AND A.AskID=" + askid; } if (!string.IsNullOrEmpty(skey)) { where += " AND (B.UserName LIKE @username"; if (DataConvert.CLng(skey) > 0) { where += " OR A.ID = " + skey; } where += ")"; sp.Add(new SqlParameter("username", "%" + skey + "%")); } string stable = "(SELECT A.*,B.UserName FROM ZL_Design_Ask A LEFT JOIN ZL_User B ON A.CUser=B.UserID)"; return(DBCenter.JoinQuery("A.*,B.Title,B.Remind,B.UserName", TbName, stable, "A.AskID=B.ID", where, "A.ID DESC", sp.ToArray())); }
//--------------TO CMS public static DataTable Report_SelByProduct(Sale_Filter filter) { string SDate = filter.stime; string EDate = filter.etime; int StoreID = filter.sid; List <SqlParameter> sp = new List <SqlParameter>(); string where = "(PaymentNO IS NOT NULL AND PaymentNO!='') AND OrderStatus=99 "; if (!string.IsNullOrEmpty(SDate)) { where += " AND PayTime>=@stime"; sp.Add(new SqlParameter("stime", Convert.ToDateTime(SDate).ToString("yyyy/MM/dd 00:00:00"))); } if (!string.IsNullOrEmpty(EDate)) { where += " AND PayTime<=@etime"; sp.Add(new SqlParameter("etime", Convert.ToDateTime(EDate).ToString("yyyy/MM/dd 23:59:59"))); } if (StoreID != -100) { where += " AND StoreID=" + StoreID; } //if (NodeID > 0) { where += " AND NodeID=" + NodeID; } 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())); }
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); }
public DataTable SelWith(int classID = 0, int ztype = 0, int psize = 0, string skey = "", int status = -100) { string where = " 1=1 "; string fields = "A.*,B.Name AS ClassName"; List <SqlParameter> sp = new List <SqlParameter>(); if (classID > 0) { where += " AND ClassID=" + classID; } if (ztype != -100) { where += " AND ZType=" + ztype; } if (psize > 0) { fields = " TOP " + psize + " " + fields; } if (!string.IsNullOrEmpty(skey)) { where += " AND TlpName LIKE @skey"; sp.Add(new SqlParameter("skey", "%" + skey + "%")); } if (status == -100) { where += " AND ZStatus !=" + (int)ZLEnum.ConStatus.Recycle; } else { where += " AND ZStatus = " + status; } return(DBCenter.JoinQuery(fields, TbName, "ZL_Design_TlpClass", "A.ClassID=B.ID", where, "ZStatus DESC,ID DESC", sp.ToArray())); }
public DataTable U_Sel(int uid) { string field = "A.*,B.LinPrice,B.Thumbnails,B.ProClass,B.ProUnit"; string where = "A.UserID=" + uid; return(DBCenter.JoinQuery(field, TbName, "ZL_Commodities", "A.ProID=B.ID", where)); }
/// <summary> /// 用于BarList.aspx,主题数,回贴总数,最新一条贴子 /// </summary> public DataTable GetCateList(int parentID = 0) { string statuwhere = "Status!=" + ((int)ZoomLa.Model.ZLEnum.ConStatus.Recycle);//非删除状态 string fields = "A.CateName,A.CateID,A.BarImage,A.ParentID,"; fields += "B.ID,B.Title,B.CDate,B.R_CDate,B.Status,"; fields += "(SELECT COUNT(*) From ZL_Guest_Bar Where CateID=A.Cateid And Pid=0 AND " + statuwhere + ") ItemCount,"; //主题 fields += "(Select COUNT(*) From ZL_Guest_Bar Where CateID=a.Cateid And Pid>0 AND " + statuwhere + ") ReCount"; //回贴 string where = ""; if (parentID > 0) { where += "A.ParentID=" + parentID; } string tbview = ""; switch (DBCenter.DB.DBType)//分组取第一条数据 { case "oracle": tbview = "(SELECT * FROM (SELECT ROW_NUMBER() OVER(PARTITION BY CateID ORDER BY R_CDate DESC) rn,ZL_Guest_BarView.* FROM ZL_Guest_BarView) WHERE rn = 1)"; break; default: tbview = "(SELECT * FROM ZL_Guest_BarView AS b where ID = (Select Top 1 ID FROM ZL_Guest_BarView Where Pid=0 And Cateid = b.Cateid AND " + statuwhere + " ORDER BY R_CDate DESC))"; break; } return(DBCenter.JoinQuery(fields, TbName, tbview, "A.CateID=B.CateID", where, "B.OrderID")); }
public DataTable SelByCateIDS(string ids, string key = "", int status = -10) { SqlParameter[] sp = new SqlParameter[] { new SqlParameter("key", "%" + key + "%") }; string wherestr = ""; if (!string.IsNullOrEmpty(ids)) { SafeSC.CheckIDSEx(ids); wherestr += " AND A.CateID IN (" + ids + ")"; } if (!string.IsNullOrEmpty(key)) { wherestr += " And Title Like @key"; } //不包含回收站 if (status == -10) { wherestr += " AND A.[Status]NOT IN (" + (int)ZLEnum.ConStatus.Recycle + ")"; } else { wherestr += " AND A.[Status]=" + status; } wherestr += " AND B.GType=1"; return(DBCenter.JoinQuery("A.*,B.CateName,(Select COUNT(ID) From " + TbName + " Where Pid=A.ID ) RCount", TbName, "ZL_GuestCate", "A.CateID=B.CateID", "A.Pid=0" + wherestr, "OrderFlag Desc,A.CDate Desc", sp)); }
public DataTable SelByParent(int pid) { string where = "(" + "A.ParentID=" + pid + " OR A.ID=" + pid + ")"; DataTable dt = DBCenter.JoinQuery("A.*,B.Proname", "ZL_Shop_Bonus", "ZL_Commodities", "A.ProID=B.ID", where, "ParentID ASC"); dt.Rows[0]["Proname"] = "统一设置"; return(dt); }
//--------------------------Report /// <summary> /// 按日期获取数据,精确到日 /// </summary> public DataTable Report_SelByDate(DateTime stime, DateTime etime) { string field = "A.ID,A.OrderNo,A.Ordersamount,A.Receivablesamount,B.PayTime,B.PayPlatID"; string where = "(A.PaymentNO IS NOT NULL AND A.PaymentNO!='') AND A.OrderStatus=99 "; where += " AND PayTime>='" + stime.ToString("yyyy/MM/dd 00:00:00") + "'"; where += " AND PayTime<='" + etime.ToString("yyyy/MM/dd 23:59:59") + "'"; return(DBCenter.JoinQuery(field, "ZL_OrderInfo", "ZL_Payment", "A.PaymentNo=B.PayNo", where)); }
private DataTable Sel() { //(A.NodeID=" + NodeID + " OR A.FirstNodeID=" + NodeID + ") and string fields = "A.GeneralID,A.CreateTime,A.NodeID,A.Title,B.addr,B.content,B.logo,B.lxr,B.synopsis,B.map"; string where = " A.Status=99 and A.ModelID=24"; DataTable dt = DBCenter.JoinQuery(fields, "ZL_CommonModel", "ZL_Store_Reg", "A.ItemID=B.ID", where, "A.GeneralID DESC"); return(dt); }
public DataTable SelByMsgIDS(string ids, string source) { SafeSC.CheckIDSEx(ids); List <SqlParameter> sp = new List <SqlParameter>() { new SqlParameter("source", source) }; return(DBCenter.JoinQuery("A.*,B.salt,B.HoneyName,B.UserName", TbName, "ZL_User", "A.CUser=B.UserID AND Source=@source", "MsgID IN (" + ids + ")", "", sp.ToArray())); }
//指定节点下的商品信息 private DataTable SelProByPName(string name) { //SELECT A.* FROM ZL_Commodities A LEFT JOIN ZL_Node B ON A.Nodeid=B.NodeID WHERE B.NodeName='微建站3' List <SqlParameter> sp = new List <SqlParameter>() { new SqlParameter("name", name) }; return(DBCenter.JoinQuery("A.*", "ZL_Commodities", "ZL_Node", "A.NodeID=B.NodeID", "B.NodeName=@name", "", sp.ToArray())); }
public DataTable Sel(string skey = "") { string where = "1=1"; List <SqlParameter> sp = new List <SqlParameter>(); if (!string.IsNullOrEmpty(skey)) { where += " AND Name LIKE @key"; sp.Add(new SqlParameter("key", "%" + skey + "%")); } return(DBCenter.JoinQuery("A.*,B.AdminName", TbName, "ZL_Manager", "A.AdminID=B.AdminID", where, "", sp.ToArray())); }
/// <summary> /// 关注我的用户 /// </summary> public DataTable SelByTUser(int tuid, string skey = "") { string where = "A.TUserID=" + tuid; List <SqlParameter> sp = new List <SqlParameter>(); if (!string.IsNullOrEmpty(skey)) { where += " AND (B.UserName LIKE @skey OR B.HoneyName LIKE @skey)"; sp.Add(new SqlParameter("skey", "%" + skey + "%")); } return(DBCenter.JoinQuery("A.*,B.UserName,B.HoneyName,B.salt", TbName, "ZL_User", "A.UserID=B.UserID", where, PK + " DESC", sp.ToArray())); }
public DataTable SelForList(string key = "") { string where = ""; List <SqlParameter> sp = new List <SqlParameter>(); if (!string.IsNullOrEmpty(key)) { where += "A.CompName LIKE @compname"; sp.Add(new SqlParameter("compname", "%" + key + "%")); } return(DBCenter.JoinQuery("A.*,B.UserName", TbName, "ZL_User", "A.CreateUser=B.UserID", where, "CreateTime DESC", sp.ToArray())); }
public DataTable Sel(string skey = "") { string where = " 1=1 "; List <SqlParameter> sp = new List <SqlParameter>(); if (!string.IsNullOrEmpty(skey)) { where += " AND A.TRName LIKE @skey "; sp.Add(new SqlParameter("skey", "%" + skey + "%")); } return(DBCenter.JoinQuery("A.*,B.ID AS ProID,B.Producername AS ProName", TbName, "ZL_Manufacturers", "A.Producer=B.ID", where, "A.ID DESC", sp.ToArray())); }
/// <summary> /// plat,bar /// </summary> public DataTable Sel(int pid, string form = "") { string where = "pid=" + pid; List <SqlParameter> sp = new List <SqlParameter>(); if (!string.IsNullOrEmpty(form)) { sp.Add(new SqlParameter("form", form)); where += " AND Source=@form "; } return(DBCenter.JoinQuery("A.*,B.Salt AS UserFace", TbView, "ZL_User", "A.CUser=B.UserID", where, "CDate DESC", sp.ToArray())); }
//用于全站搜索 public DataTable SelByTitle2(string title) { //自我描述PageUrl SqlParameter[] sp = new SqlParameter[] { new SqlParameter("Title", "%" + title + "%") }; DataTable dt = DBCenter.JoinQuery("A.ID,A.Title,A.CDate CreateTime,TagKey='',PageUrl='',A.CateID", TbName, "ZL_Guestcate", "B.Cateid=A.CateID", " B.NeedLog!=2 AND A.Title Like @Title", "", sp); for (int i = 0; i < dt.Rows.Count; i++) { dt.Rows[i]["PageUrl"] = B_Guest_Bar.CreateUrl(1, Convert.ToInt32(dt.Rows[i]["cateid"])); } return(dt); }
public DataTable SelWith(string uname = "") { string where = ""; List <SqlParameter> sp = new List <SqlParameter>(); if (!string.IsNullOrEmpty(uname)) { where += " (UserName LIKE @uname OR SiteName LIKE @uname)"; sp.Add(new SqlParameter("uname", "%" + uname + "%")); } return(DBCenter.JoinQuery("A.*,B.DomName", TbName, "ZL_IDC_DomainList", "A.DomainID=B.ID", where, PK + " DESC", sp.ToArray())); }
/// <summary> /// 带缩略图的列表 /// </summary> public static DataTable SelCart(int oid, int cid = -100) { string where = "A.OrderlistID=" + oid + " AND (A.Attribute !='' AND A.Attribute IS NOT NULL)"; string mTbName = "(SELECT A.*,B.NodeID,B.Thumbnails FROM ZL_CartPro A LEFT JOIN ZL_Commodities B ON A.ProID=B.ID)"; if (cid != -100) { where += " AND A.ID=" + cid; } return(DBCenter.JoinQuery("A.*,B.PreViewImg", mTbName, "ZL_Logo_Design", "A.Attribute=B.ID", where)); }
/// <summary> /// 返回购物车商品列表 /// </summary> public DataTable GetCarProList(string cartno) { SqlParameter[] sp = new SqlParameter[] { new SqlParameter("cartno", cartno) }; string cartsql = "Select * From ZL_Cart Where Cartid=@cartno"; DataTable dt = SqlHelper.ExecuteTable(CommandType.Text, cartsql, sp); if (dt.Rows.Count < 1) { return(null); } int cartid = Convert.ToInt32(dt.Rows[0]["ID"]); return(DBCenter.JoinQuery("A.*,B.ProClass", "ZL_CartPro", "ZL_Commodities", "A.ProID=B.ID", "A.CartID=" + cartid)); }
public static void OrderFinish(M_OrderList mod, M_Payment pinfo) { B_User buser = new B_User(); DataTable prodt = DBCenter.JoinQuery("A.ProID,A.Pronum,B.PointVal", "ZL_CartPro", "ZL_Commodities", "A.ProID=B.ID", "A.OrderListID=" + mod.id); foreach (DataRow dr in prodt.Rows) { double point = DataConvert.CDouble(dr["PointVal"]) * Convert.ToInt32(dr["Pronum"]); if (point > 0) { buser.AddMoney(mod.Userid, point, M_UserExpHis.SType.Point, "购买商品[" + dr["ProID"] + "],赠送积分"); } } }
/// <summary> /// 获取本公司下所有群组,用于管理页 /// </summary> public DataTable SelByCompID(int compID, int pid = -100) { //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) ChildCount FROM Tree AS A ORDER BY OrderID,NodeID ASC"; string where = "B.CompID=" + compID; if (pid != -100) { where += " AND Pid=" + pid; } DataTable dt = DBCenter.JoinQuery("A.*,b.UserName", TbName, "ZL_User_PlatView", "a.CreateUser=b.UserID", where, "OrderID ASC,ID DESC"); DataTable result = dt.Clone(); AddToDT(dt, result, 0); return(result); }
public DataTable Sel(int type, int state, string flow, string name, string stime, string etime, string username = "", string addon = "") { string where = " 1=1 "; List <SqlParameter> sp = new List <SqlParameter>(); if (type != -100) { where += " AND A.type=" + type; } if (state != -100) { where += " AND A.state=" + state; } if (!string.IsNullOrEmpty(flow)) { where += " AND A.flow=@flow"; sp.Add(new SqlParameter("flow", flow)); } if (!string.IsNullOrEmpty(name)) { where += " AND A.ArriveName LIKE @name"; sp.Add(new SqlParameter("name", "%" + name + "%")); } if (!string.IsNullOrEmpty(stime)) { where += " AND A.AgainTime>=@stime"; sp.Add(new SqlParameter("stime", stime)); } if (!string.IsNullOrEmpty(etime)) { where += " AND A.EndTime<=@etime"; sp.Add(new SqlParameter("etime", etime)); } if (!string.IsNullOrEmpty(username)) { where += " AND B.UserName LIKE @username"; sp.Add(new SqlParameter("username", "%" + username + "%")); } switch (addon.ToLower()) { case "isbind": where += " AND A.UserID>0"; break; case "nobind": where += " AND A.UserID=0"; break; case "expired": where += " AND A.EndTime < GETDATE()"; break; } return(DBCenter.JoinQuery("A.*,B.UserName", TbName, "ZL_User", "A.UserID=B.UserID", where, PK + " DESC", sp.ToArray())); }
public DataTable Sel(int storeid, string skey) { List <SqlParameter> sp = new List <SqlParameter>(); string where = "1=1 "; if (storeid != -100) { where += " AND StoreID=" + storeid; } if (!string.IsNullOrEmpty(skey)) { where += " AND OrderNO LIKE @skey"; sp.Add(new SqlParameter("skey", "%" + skey + "%")); } return(DBCenter.JoinQuery("A.*,B.StoreID", TbName, "ZL_Orderinfo", "A.OrderID=B.ID", where, "A.ID DESC", sp.ToArray())); }
public DataTable SelAll(int status = -100, int storeid = -100) { string where = "1=1 "; if (status != -100) { where += " AND A.Status=" + status; } if (storeid != -100) { where += " AND StoreID=" + storeid; } string mtable = "(SELECT A.*,B.StoreID FROM ZL_Order_Repair A LEFT JOIN ZL_OrderInfo B ON A.OrderNo=B.OrderNo)"; return(DBCenter.JoinQuery("A.*,B.Proname,B.Thumbnails", mtable, "ZL_Commodities", "A.ProID=B.ID", where, "A.ID DESC")); }
public void ChangeDomain(string newdomain) { DataTable dt = DBCenter.JoinQuery("A.ID,B.UserName,B.UserID", strTableName, "ZL_User", "A.UserID=B.UserID", "SType=2"); foreach (DataRow dr in dt.Rows) { //admin.site.2013.hx008.com //用户名.新域名 List <SqlParameter> sp = new List <SqlParameter>() { new SqlParameter("domain", dr["UserID"] + "." + newdomain) }; DBCenter.UpdateSQL(strTableName, "DomName=@domain", "ID=" + dr["ID"], sp); } }