public static DataTable GetAll(int parentid)
{
string sql = "select * from odnshop_productcategory where parentid=" + parentid + " order by orderid asc";
DataTable dt = MySqlDbHelper.Query(sql).Tables[0];
return(dt);
}
public static void Add(ProductModel info)
{
string sql = @"INSERT INTO odnshop_product(
productname ,
includepicpath ,
productpics,
iscommend,
productcode ,
description ,
specification ,
salecount ,
hits ,productcount,
price,itemprice,categoryid,createtime) VALUES (?productname,?includepicpath,?productpics,?iscommend,?productcode,?description,?specification,?salecount,?hits,?productcount,?price,?itemprice,?categoryid,?createtime)";
MySqlParameter[] parameters =
{
MySqlDbHelper.MakeInParam("?productname", MySqlDbType.VarChar, 50, info.productname),
MySqlDbHelper.MakeInParam("?includepicpath", MySqlDbType.VarChar, 50, info.includepicpath),
MySqlDbHelper.MakeInParam("?productpics", MySqlDbType.MediumText, 0, info.productpics),
MySqlDbHelper.MakeInParam("?iscommend", MySqlDbType.Bit, 1, info.iscommend),
MySqlDbHelper.MakeInParam("?productcode", MySqlDbType.Int32, 4, info.productcode),
MySqlDbHelper.MakeInParam("?description", MySqlDbType.VarChar, 0, info.description),
MySqlDbHelper.MakeInParam("?specification", MySqlDbType.VarChar, 0, info.specification),
MySqlDbHelper.MakeInParam("?salecount", MySqlDbType.Int32, 4, info.salecount),
MySqlDbHelper.MakeInParam("?hits", MySqlDbType.Int32, 4, info.hits),
MySqlDbHelper.MakeInParam("?productcount", MySqlDbType.Int32, 4, info.productcount),
MySqlDbHelper.MakeInParam("?price", MySqlDbType.Decimal, 8, info.price),
MySqlDbHelper.MakeInParam("?itemprice", MySqlDbType.VarChar, 0, info.itemprice),
MySqlDbHelper.MakeInParam("?categoryid", MySqlDbType.Int32, 4, info.categoryid),
MySqlDbHelper.MakeInParam("?createtime", MySqlDbType.Date, 8, info.createtime)
};
MySqlDbHelper.Query(sql, parameters);
}
public static void Add(AdminModel model)
{
string sql = "INSERT INTO odnshop_admin(username,userpwd,email,tel,usertype,lastlogindate,createdate,lastloginip,logincount,adminqx) VALUES(?username,?userpwd,?email,?tel,?usertype,?lastlogindate,?createdate,?lastloginip,?logincount,?adminqx)";
MySqlParameter[] parameters =
{
new MySqlParameter("?username", MySqlDbType.VarChar, 45),
new MySqlParameter("?userpwd", MySqlDbType.VarChar, 45),
new MySqlParameter("?email", MySqlDbType.VarChar, 45),
new MySqlParameter("?tel", MySqlDbType.VarChar, 45),
new MySqlParameter("?usertype", MySqlDbType.Int16, 4),
new MySqlParameter("?lastlogindate", MySqlDbType.Datetime),
new MySqlParameter("?createdate", MySqlDbType.Datetime),
new MySqlParameter("?lastloginip", MySqlDbType.VarChar, 45),
new MySqlParameter("?logincount", MySqlDbType.Int32, 11),
new MySqlParameter("?adminqx", MySqlDbType.MediumText)
};
parameters[0].Value = model.username;
parameters[1].Value = model.userpwd;
parameters[2].Value = model.email;
parameters[3].Value = model.tel;
parameters[4].Value = model.usertype;
parameters[5].Value = model.lastlogindate;
parameters[6].Value = model.createdate;
parameters[7].Value = model.lastloginip;
parameters[8].Value = model.logincount;
parameters[9].Value = model.adminqx;
MySqlDbHelper.Query(sql, parameters);
}
public static void Update(UserModel info)
{
string sql = @"update odnshop_user set
nickname = ?nickname,
openid = ?openid,
fullname = ?fullname,
sex = ?sex,
tel = ?tel,
address = ?address,
headpicurl = ?headpicurl,
jbnum = ?jbnum,
jfnum = ?jfnum,
createdate = ?createdate,
fromuid = ?fromuid,
usertype = ?usertype where uid = ?uid";
MySqlParameter[] parameters =
{
MySqlDbHelper.MakeInParam("?nickname", MySqlDbType.VarChar, 50, info.nickname),
MySqlDbHelper.MakeInParam("?openid", MySqlDbType.VarChar, 255, info.openid),
MySqlDbHelper.MakeInParam("?fullname", MySqlDbType.VarChar, 50, info.fullname),
MySqlDbHelper.MakeInParam("?sex", MySqlDbType.VarChar, 50, info.sex),
MySqlDbHelper.MakeInParam("?tel", MySqlDbType.VarChar, 50, info.tel),
MySqlDbHelper.MakeInParam("?address", MySqlDbType.VarChar, 50, info.address),
MySqlDbHelper.MakeInParam("?headpicurl", MySqlDbType.VarChar, 255, info.headpicurl),
MySqlDbHelper.MakeInParam("?jbnum", MySqlDbType.Int32, 4, info.jbnum),
MySqlDbHelper.MakeInParam("?jfnum", MySqlDbType.Int32, 4, info.jfnum),
MySqlDbHelper.MakeInParam("?createdate", MySqlDbType.Date, 8, info.createdate),
MySqlDbHelper.MakeInParam("?fromuid", MySqlDbType.Int32, 4, info.fromuid),
MySqlDbHelper.MakeInParam("?usertype", MySqlDbType.Int32, 4, info.usertype),
MySqlDbHelper.MakeInParam("?uid", MySqlDbType.Int32, 4, info.uid)
};
MySqlDbHelper.Query(sql, parameters);
}
public static List <FavoriteModel> GetListByUid(int uid, int pageSize, int pageIndex, out int totalcount)
{
int start = (pageIndex - 1) * pageSize;
string sql = string.Format("select * from odnshop_favorite where uid={0} order by fid desc limit {1},{2}", uid.ToString(), start, pageSize);
List <FavoriteModel> list = new List <FavoriteModel>();
DataTable dt = MySqlDbHelper.Query(sql).Tables[0];
FavoriteModel info = null;
foreach (DataRow dr in dt.Rows)
{
info = new FavoriteModel();
info.product = (ProductModel)SerializeHelper.LoadFromXml(new ProductModel().GetType(), dr["productxml"].ToString());
info.fid = Int32.Parse(dr["fid"].ToString());
info.uid = Int32.Parse(dr["uid"].ToString());
info.productid = Int32.Parse(dr["productid"].ToString());
info.createtime = DateTime.Parse(dr["createtime"].ToString());
list.Add(info);
}
totalcount = MySqlDbHelper.ExecuteScalar("select count(*) from odnshop_favorite where uid=" + uid);
return(list);
}
public static DataTable GetList(string whereSql)
{
string sql = string.Format("select * from odnshop_user {0} order by uid desc", whereSql);
DataTable dt = MySqlDbHelper.Query(sql).Tables[0];
return(dt);
}
public static DataTable GetList(int count, string whereSql)
{
string sql = string.Format("select * from odnshop_order {0} order by orderid desc limit {1}", whereSql, count);
DataTable dt = MySqlDbHelper.Query(sql).Tables[0];
return(dt);
}
public static DataTable GetAll()
{
string sql = "select * from odnshop_singlepage order by pageid asc";
DataTable dt = MySqlDbHelper.Query(sql).Tables[0];
return(dt);
}
public static DataTable GetList(int pageSize, int pageIndex, string whereSql, string orderBy, out int totalcount)
{
int start = (pageIndex - 1) * pageSize;
string sql = string.Format("select * from odnshop_user {0}{1} limit {2},{3}", whereSql, orderBy, start, pageSize);
DataTable dt = MySqlDbHelper.Query(sql).Tables[0];
totalcount = MySqlDbHelper.ExecuteScalar(string.Format("select count(*) from odnshop_user {0}", whereSql));
return(dt);
}
public static void UpdateOrderStatus(string orderno, int orderstatus)
{
string sql = "UPDATE odnshop_order set orderstatus=?orderstatus where orderno=?orderno";
MySqlParameter[] parameters =
{
MySqlDbHelper.MakeInParam("?orderstatus", MySqlDbType.Int32, 4, orderstatus),
MySqlDbHelper.MakeInParam("?orderno", MySqlDbType.VarChar, 32, orderno)
};
MySqlDbHelper.Query(sql, parameters);
}
public static void UpdateStatus(int orderstatus, int orderid)
{
string sql = "UPDATE odnshop_order set orderstatus=?orderstatus where orderid=?orderid";
MySqlParameter[] parameters =
{
MySqlDbHelper.MakeInParam("?orderstatus", MySqlDbType.Int32, 4, orderstatus),
MySqlDbHelper.MakeInParam("?orderid", MySqlDbType.Int32, 4, orderid)
};
MySqlDbHelper.Query(sql, parameters);
}
public static void UpdateUsertype(int uid, int usertype)
{
string sql = @"update odnshop_user set usertype=?usertype where uid = ?uid";
MySqlParameter[] parameters =
{
MySqlDbHelper.MakeInParam("?usertype", MySqlDbType.Int32, 4, usertype),
MySqlDbHelper.MakeInParam("?uid", MySqlDbType.Int32, 4, uid)
};
MySqlDbHelper.Query(sql, parameters);
}
public static void Update(ProductCategoryModel info)
{
string sql = @"update odnshop_productcategory set categoryname = ?categoryname,orderid = ?orderid,parentid = ?parentid where categoryid = ?categoryid";
MySqlParameter[] parameters =
{
MySqlDbHelper.MakeInParam("?categoryname", MySqlDbType.VarChar, 50, info.categoryname),
MySqlDbHelper.MakeInParam("?orderid", MySqlDbType.Int32, 4, info.orderid),
MySqlDbHelper.MakeInParam("?parentid", MySqlDbType.Int32, 4, info.parentid),
MySqlDbHelper.MakeInParam("?categoryid", MySqlDbType.Int32, 4, info.categoryid)
};
MySqlDbHelper.Query(sql, parameters);
}
public static void Add(FavoriteModel info)
{
string sql = @"INSERT INTO odnshop_favorite (uid,productid,createtime,productxml) VALUES (?uid,?productid,?createtime,?productxml)";
MySqlParameter[] parameters =
{
MySqlDbHelper.MakeInParam("?uid", MySqlDbType.Int32, 4, info.uid),
MySqlDbHelper.MakeInParam("?productid", MySqlDbType.Int32, 4, info.productid),
MySqlDbHelper.MakeInParam("?createtime", MySqlDbType.Datetime, 8, info.createtime),
MySqlDbHelper.MakeInParam("?productxml", MySqlDbType.VarChar, 0, SerializeHelper.SaveToString(info.product))
};
MySqlDbHelper.Query(sql, parameters);
}
public static DataTable GetList(string possymbol)
{
string sql1 = "select * from odnshop_link order by orderno asc";
string sql2 = "select * from odnshop_link where possymbol='" + possymbol + "' order by orderno asc";
if (string.IsNullOrEmpty(possymbol))
{
return(MySqlDbHelper.Query(sql1).Tables[0]);
}
else
{
return(MySqlDbHelper.Query(sql2).Tables[0]);
}
}
public static UserModel GetFirst()
{
string sql = "select * from odnshop_user order by uid asc limit 1";
DataTable dt = MySqlDbHelper.Query(sql).Tables[0];
UserModel info = null;
if (dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
info = PopulateModel(dr, new UserModel());
}
return(info);
}
public static UserGroupModel Get(int groupid)
{
string sql = "select * from odnshop_usergroup where groupid=" + groupid;
DataTable dt = MySqlDbHelper.Query(sql).Tables[0];
UserGroupModel info = null;
if (dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
info = PopulateModel(dr, new UserGroupModel());
}
return(info);
}
//salecount,增加的销量=减少的库存量
public static void UpdateSalecount(int productid, int salecount)
{
string sql = @"UPDATE odnshop_product set
salecount=salecount+?salecount ,
productcount=productcount-?salecount WHERE productid = ?productid";
MySqlParameter[] parameters =
{
MySqlDbHelper.MakeInParam("?salecount", MySqlDbType.Int32, 4, salecount),
MySqlDbHelper.MakeInParam("?productcount", MySqlDbType.Int32, 4, salecount),
MySqlDbHelper.MakeInParam("?productid", MySqlDbType.Int32, 4, productid)
};
MySqlDbHelper.Query(sql, parameters);
}
public static AdminModel Get(int adminid)
{
string sql = "select * from odnshop_admin where adminid=" + adminid.ToString();
DataSet ds = MySqlDbHelper.Query(sql);
AdminModel info = null;
if (ds.Tables[0].Rows.Count > 0)
{
DataRow dr = ds.Tables[0].Rows[0];
info = PopulateModel(dr, new AdminModel());
}
return(info);
}
public static UserModel Get(string openid)
{
string sql = "select * from odnshop_user where openid='" + openid + "'";
DataTable dt = MySqlDbHelper.Query(sql).Tables[0];
UserModel info = null;
if (dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
info = PopulateModel(dr, new UserModel());
}
return(info);
}
public static SinglePageModel Get(string serialno)
{
string sql = "select * from odnshop_singlepage where serialno = '" + serialno + "'";
SinglePageModel info = null;
DataTable dt = MySqlDbHelper.Query(sql).Tables[0];
if (dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
info = PopulateModel(dr, new SinglePageModel());
}
return(info);
}
public static ProductModel Get(int productid)
{
string sql = "select * from odnshop_product where productid=" + productid;
ProductModel info = null;
DataTable dt = MySqlDbHelper.Query(sql).Tables[0];
if (dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
info = PopulateModel(dr, new ProductModel());
}
return(info);
}
public static AdminModel Get(string username)
{
string sql = "select * from odnshop_admin where username='******'";
DataSet ds = MySqlDbHelper.Query(sql);
AdminModel info = null;
if (ds.Tables[0].Rows.Count > 0)
{
DataRow dr = ds.Tables[0].Rows[0];
info = PopulateModel(dr, new AdminModel());
}
return(info);
}
public static void Update(UserGroupModel info)
{
string sql = "UPDATE odnshop_usergroup set groupname=?groupname,picurl=?picurl,grouplevel=?grouplevel,isdefalut=?isdefalut,upgradejf=?upgradejf,discount=?discount where groupid=?groupid";
MySqlParameter[] parameters =
{
MySqlDbHelper.MakeInParam("?groupname", MySqlDbType.VarChar, 50, info.groupname),
MySqlDbHelper.MakeInParam("?picurl", MySqlDbType.VarChar, 50, info.picurl),
MySqlDbHelper.MakeInParam("?grouplevel", MySqlDbType.VarChar, 100, info.grouplevel),
MySqlDbHelper.MakeInParam("?isdefalut", MySqlDbType.Int32, 4, info.isdefalut?1:0),
MySqlDbHelper.MakeInParam("?upgradejf", MySqlDbType.Int32, 4, info.upgradejf),
MySqlDbHelper.MakeInParam("?discount", MySqlDbType.Date, 8, info.discount),
MySqlDbHelper.MakeInParam("?groupid", MySqlDbType.Int32, 4, info.groupid)
};
MySqlDbHelper.Query(sql, parameters);
}
public static LinkModel Get(int linkid)
{
string sql = "select * from odnshop_link where linkid = " + linkid;
LinkModel info = null;
DataTable dt = MySqlDbHelper.Query(sql).Tables[0];
if (dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
info = PopulateModel(dr, new LinkModel());
}
return(info);
}
public static List <UserGroupModel> GetList(string whereSql)
{
string sql = string.Format("select * from odnshop_usergroup {0} order by grouplevel asc", whereSql);
DataTable dt = MySqlDbHelper.Query(sql).Tables[0];
List <UserGroupModel> list = new List <UserGroupModel>();
UserGroupModel info = null;
foreach (DataRow dr in dt.Rows)
{
info = PopulateModel(dr, new UserGroupModel());
list.Add(info);
}
return(list);
}
public static void Add(ProductCategoryModel info)
{
string sql = @"INSERT INTO odnshop_productcategory (
categoryname ,
orderid ,
parentid) VALUES (?categoryname,?orderid,?parentid)";
MySqlParameter[] parameters =
{
MySqlDbHelper.MakeInParam("?categoryname", MySqlDbType.VarChar, 50, info.categoryname),
MySqlDbHelper.MakeInParam("?orderid", MySqlDbType.Int32, 4, info.orderid),
MySqlDbHelper.MakeInParam("?parentid", MySqlDbType.Int32, 4, info.parentid)
};
MySqlDbHelper.Query(sql, parameters);
}
public static void Update(OrderModel info)
{
string sql = "UPDATE odnshop_order set customername=?customername,tel=?tel,address=?address,orderstatus=?orderstatus,deliverstatus=?deliverstatus,createtime=?createtime,orderxml=?orderxml where orderid=?orderid";
MySqlParameter[] parameters =
{
MySqlDbHelper.MakeInParam("?customername", MySqlDbType.VarChar, 50, info.customername),
MySqlDbHelper.MakeInParam("?tel", MySqlDbType.VarChar, 50, info.tel),
MySqlDbHelper.MakeInParam("?address", MySqlDbType.VarChar, 100, info.address),
MySqlDbHelper.MakeInParam("?orderstatus", MySqlDbType.Int32, 4, info.orderstatus),
MySqlDbHelper.MakeInParam("?deliverstatus", MySqlDbType.Int32, 4, info.deliverstatus),
MySqlDbHelper.MakeInParam("?createtime", MySqlDbType.Date, 8, info.createtime),
MySqlDbHelper.MakeInParam("?orderxml", MySqlDbType.VarChar, 0, SerializeHelper.SaveToString(info)),
MySqlDbHelper.MakeInParam("?orderid", MySqlDbType.Int32, 4, info.orderid)
};
MySqlDbHelper.Query(sql, parameters);
}
public static List <ProductModel> GetList(int count, string whereSql)
{
string sql = string.Format("select * from odnshop_product {0} order by productid desc limit {1}", whereSql, count);
DataTable dt = MySqlDbHelper.Query(sql).Tables[0];
List <ProductModel> list = new List <ProductModel>();
ProductModel info = null;
foreach (DataRow dr in dt.Rows)
{
info = PopulateModel(dr, new ProductModel());
list.Add(info);
}
return(list);
}
public static OrderModel Get(int orderid)
{
string sql = "select * from odnshop_order where orderid=" + orderid;
OrderModel info = null;
DataTable dt = MySqlDbHelper.Query(sql).Tables[0];
if (dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
info = (OrderModel)SerializeHelper.LoadFromXml(new OrderModel().GetType(), dr["orderxml"].ToString());
info.orderid = Int32.Parse(dr["orderid"].ToString());
info.orderstatus = Int32.Parse(dr["orderstatus"].ToString());
info.deliverstatus = Int32.Parse(dr["deliverstatus"].ToString());
}
return(info);
}