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