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 Add(LinkModel model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into odnshop_link(");
strSql.Append("linkname,linkurl,includepic,possymbol,createtime,orderno)");
strSql.Append(" values (");
strSql.Append("?linkname,?linkurl,?includepic,?possymbol,?createtime,?orderno)");
MySqlParameter[] parameters =
{
new MySqlParameter("?linkname", MySqlDbType.VarChar, 45),
new MySqlParameter("?linkurl", MySqlDbType.VarChar, 200),
new MySqlParameter("?includepic", MySqlDbType.VarChar, 200),
new MySqlParameter("?possymbol", MySqlDbType.VarChar, 45),
new MySqlParameter("?createtime", MySqlDbType.Datetime),
new MySqlParameter("?orderno", MySqlDbType.Int32, 11)
};
parameters[0].Value = model.linkname;
parameters[1].Value = model.linkurl;
parameters[2].Value = model.includepic;
parameters[3].Value = model.possymbol;
parameters[4].Value = model.createtime;
parameters[5].Value = model.orderno;
MySqlDbHelper.ExecuteSql(strSql.ToString(), 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 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(UserGroupModel model)
{
string sql = "INSERT INTO odnshop_usergroup(groupid,groupname,picurl,grouplevel,isdefalut,upgradejf,discount) VALUES(?groupid,?groupname,?picurl,?grouplevel,?isdefalut,?upgradejf,?discount)";
MySqlParameter[] parameters =
{
new MySqlParameter("?groupid", MySqlDbType.Int32, 11),
new MySqlParameter("?groupname", MySqlDbType.VarChar, 50),
new MySqlParameter("?picurl", MySqlDbType.VarChar, 50),
new MySqlParameter("?grouplevel", MySqlDbType.Int32, 11),
new MySqlParameter("?isdefalut", MySqlDbType.Int32),
new MySqlParameter("?upgradejf", MySqlDbType.Int32, 11),
new MySqlParameter("?discount", MySqlDbType.Int32, 11)
};
parameters[0].Value = model.groupid;
parameters[1].Value = model.groupname;
parameters[2].Value = model.picurl;
parameters[3].Value = model.grouplevel;
parameters[4].Value = model.isdefalut?1:0;
parameters[5].Value = model.upgradejf;
parameters[5].Value = model.discount;
MySqlDbHelper.ExecuteSql(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 void Update(LinkModel model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update odnshop_link set ");
strSql.Append("linkname=?linkname,");
strSql.Append("linkurl=?linkurl,");
strSql.Append("includepic=?includepic,");
strSql.Append("possymbol=?possymbol,");
strSql.Append("createtime=?createtime,");
strSql.Append("orderno=?orderno");
strSql.Append(" where linkid=?linkid");
MySqlParameter[] parameters =
{
new MySqlParameter("?linkname", MySqlDbType.VarChar, 45),
new MySqlParameter("?linkurl", MySqlDbType.VarChar, 200),
new MySqlParameter("?includepic", MySqlDbType.VarChar, 200),
new MySqlParameter("?possymbol", MySqlDbType.VarChar, 45),
new MySqlParameter("?createtime", MySqlDbType.Datetime),
new MySqlParameter("?orderno", MySqlDbType.Int32, 11),
new MySqlParameter("?linkid", MySqlDbType.Int32, 11)
};
parameters[0].Value = model.linkname;
parameters[1].Value = model.linkurl;
parameters[2].Value = model.includepic;
parameters[3].Value = model.possymbol;
parameters[4].Value = model.createtime;
parameters[5].Value = model.orderno;
parameters[6].Value = model.linkid;
MySqlDbHelper.ExecuteSql(strSql.ToString(), parameters);
}
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 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 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 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 bool IsFavorite(int uid, int productid)
{
int favcount = MySqlDbHelper.ExecuteScalar(string.Format("select count(*) from odnshop_favorite where uid={0} and productid={1}", uid, productid));
if (favcount > 0)
{
return(true);
}
return(false);
}
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 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 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 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 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 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 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);
}
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 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);
}
//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 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 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 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(AdminModel model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update odnshop_admin set ");
strSql.Append("username=?username,");
strSql.Append("userpwd=?userpwd,");
strSql.Append("email=?email,");
strSql.Append("tel=?tel,");
strSql.Append("usertype=?usertype,");
strSql.Append("lastlogindate=?lastlogindate,");
strSql.Append("createdate=?createdate,");
strSql.Append("lastloginip=?lastloginip,");
strSql.Append("logincount=?logincount,");
strSql.Append("adminqx=?adminqx");
strSql.Append(" where adminid=?adminid ");
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),
new MySqlParameter("?adminid", MySqlDbType.Int32, 11)
};
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;
parameters[10].Value = model.adminid;
MySqlDbHelper.ExecuteSql(strSql.ToString(), 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 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);
}