/// <summary>
/// 查询某级类别的所有类别
/// </summary>
/// <param name="level">类别等级(1,2,3)</param>
/// <returns></returns>
public static List <GoodsType> QueryAlltypes(int level)
{
try
{
string tb_name = "tb_goodstype";
if (level == 2)
{
tb_name += "_second";
}
else if (level == 3)
{
tb_name += "_third";
}
string sql = "select * from " + tb_name + ";";
DataTable dt = DAL_MysqlHelper.ExecuteDataSet(sql).Tables[0];
List <GoodsType> list = new List <GoodsType>();
for (int i = 0; i < dt.Rows.Count; i++)
{
GoodsType type = new GoodsType();
type.TypeID = dt.Rows[i]["goodsTypeID"].ToString();
type.TypeLevel = level;
type.TypeName = dt.Rows[i]["goodsTypeName"].ToString();
if (level != 1)
{
type.ParentTypeID = dt.Rows[i]["goodsParentTypeID"].ToString();
}
list.Add(type);
}
return(list);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 根据TypeID查询某级类别详细信息
/// </summary>
/// <param name="level">类别等级（1,2,3）</param>
/// <param name="typeID">TypeID</param>
/// <returns>GoodsType</returns>
public static GoodsType QueryTypeName(int level, string typeID)
{
try
{
string tb_name = "tb_goodstype";
if (level == 2)
{
tb_name += "_second";
}
else if (level == 3)
{
tb_name += "_third";
}
string sql = "select * from " + tb_name + " where goodsTypeID=?typeID;";
MySqlParameter para = new MySqlParameter("?typeID", typeID);
DataTable dt = DAL_MysqlHelper.ExecuteDataSet(sql, para).Tables[0];
GoodsType type = new GoodsType();
type.TypeID = typeID;
type.TypeLevel = level;
type.TypeName = dt.Rows[0]["goodsTypeName"].ToString();
if (level != 1)
{
type.ParentTypeID = dt.Rows[0]["goodsParentTypeID"].ToString();
}
return(type);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 查询某级类别的所有子类
/// </summary>
/// <param name="level">类别等级（1,2）</param>
/// <param name="typeID">TypeID</param>
/// <returns>List<GoodsType></returns>
public static List <GoodsType> QueryTypesChild(int level, string typeID)
{
try
{
level++;
string tb_name = "tb_goodstype";
if (level == 2)
{
tb_name += "_second";
}
else if (level == 3)
{
tb_name += "_third";
}
string sql = "select * from " + tb_name + " where goodsParentTypeID=?typeID;";
MySqlParameter para = new MySqlParameter("?typeID", typeID);
DataTable dt = DAL_MysqlHelper.ExecuteDataSet(sql, para).Tables[0];
List <GoodsType> list = new List <GoodsType>();
for (int i = 0; i < dt.Rows.Count; i++)
{
GoodsType type = new GoodsType();
type.TypeID = dt.Rows[i]["goodsTypeID"].ToString();
type.TypeLevel = level;
type.TypeName = dt.Rows[i]["goodsTypeName"].ToString();
list.Add(type);
}
return(list);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 关键词查询
/// </summary>
/// <param name="key">关键词</param>
/// <param name="part">是否取部分内容（加快查询和传输速度）</param>
/// <param name="pos">起始位置</param>
/// <param name="length">记录条数</param>
/// <returns></returns>
public static DataSet QueryGoodsKey(string key, bool part = false, int pos = 0, int length = 0)
{
try
{
if (part)
{
string sql = "select * from tb_goods where goodsName like ?key limit ?st, ?ed;";
MySqlParameter[] para = new MySqlParameter[3];
para[0] = new MySqlParameter("?key", "%" + key + "%");
para[1] = new MySqlParameter("?st", pos);
para[2] = new MySqlParameter("?ed", length);
DataSet ds = DAL_MysqlHelper.ExecuteDataSet(sql, para);
return(ds);
}
else
{
string sql = "select * from tb_goods where goodsName like ?key;";
MySqlParameter para = new MySqlParameter("?key", "%" + key + "%");
DataSet ds = DAL_MysqlHelper.ExecuteDataSet(sql, para);
return(ds);
}
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 查询某类别从pos开始length条商品记录
/// </summary>
/// <param name="typeID"></param>
/// <param name="pos"></param>
/// <param name="length"></param>
/// <returns></returns>
public static List <Goods> QueryGoods(string typeID, int pos, int length)
{
try
{
string sql = "select * from tb_goods where goodsTypeID=?typeID limit ?st,?ed;";
MySqlParameter[] para = new MySqlParameter[3];
para[0] = new MySqlParameter("?typeID", typeID);
para[1] = new MySqlParameter("?st", pos);
para[2] = new MySqlParameter("?ed", length);
List <Entitys.Goods> list = new List <Goods>();
DataSet ds = DAL_MysqlHelper.ExecuteDataSet(sql, para);
for (int i = 0; i < length; i++)
{
Entitys.Goods good = new Goods();
good.Id = ds.Tables[0].Rows[i][0].ToString();
good.Name = ds.Tables[0].Rows[i][1].ToString();
good.TypeID = ds.Tables[0].Rows[i][2].ToString();
good.Desctipt = ds.Tables[0].Rows[i][3].ToString();
good.UnitPrice = decimal.Parse(ds.Tables[0].Rows[i][4].ToString());
good.ImageName = ds.Tables[0].Rows[i][5].ToString();
good.SellCount = ds.Tables[0].Rows[i][6].ToString();
good.Date = ds.Tables[0].Rows[i][7].ToString();
list.Add(good);
}
return(list);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 添加订单详细信息
/// </summary>
/// <param name="name"></param>
/// <param name="goods"></param>
/// <param name="gcnt"></param>
/// <returns></returns>
public static bool AddOrderDetail(string name, List <Goods> goods, List <int> gcnt)
{
try
{
string sql = "select orderID from tb_order where customerName=?name order by orderDate limit 0,1;";
MySqlParameter para = new MySqlParameter("?name", name);
string id = DAL_MysqlHelper.ExecuteDataSet(sql, para).Tables[0].Rows[0][0].ToString();
List <string> sqls = new List <string>();
List <MySqlParameter[]> paras = new List <MySqlParameter[]>();
for (int i = 0; i < goods.Count; i++)
{
var _goods = goods.ElementAt(i);
string _sql = "insert into tb_orderdetail values(?orderID,?goodsID,?count);";
MySqlParameter[] _para = { new MySqlParameter("?orderID", id), new MySqlParameter("?goodsID", _goods.Id), new MySqlParameter("?count", gcnt.ElementAt(i)) };
sqls.Add(_sql);
paras.Add(_para);
}
bool ret = DAL_MysqlHelper.ExecuteNoQueryTran(sqls, paras);
return(ret);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 查询所有用户收货信息与个人信息（管理功能）
/// </summary>
/// <returns>DataSet 或 null</returns>
public static DataSet QueryAllCustomer()
{
try
{
string sql = "select customerName,customerTrueName,customerAddress,customerPostCode,customerBirthday,customerPhone,customerEmail from tb_customer;";
DataSet ds = DAL_MysqlHelper.ExecuteDataSet(sql);
return(ds);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 查询销量降序排列的第pos条记录开始的连续length条记录
/// </summary>
/// <param name="pos"></param>
/// <param name="length"></param>
/// <returns></returns>
public static DataSet QueryGoodsSellCountDesc(int pos, int length)
{
try
{
string sql = "select * from tb_goods order by sellCount limit ?st,?ed;";
MySqlParameter[] para = new MySqlParameter[2];
para[0] = new MySqlParameter("?st", pos);
para[1] = new MySqlParameter("?ed", length);
DataSet ds = DAL_MysqlHelper.ExecuteDataSet(sql, para);
return(ds);
}
catch (Exception e)
{
throw e;
}
}
public static DataSet QueryManagers(int pos, int length)
{
try
{
string sql = "select managerName from tb_manage limit ?st,?ed;";
MySqlParameter[] para = new MySqlParameter[2];
para[0] = new MySqlParameter("?st", pos);
para[1] = new MySqlParameter("?ed", length);
var ret = DAL_MysqlHelper.ExecuteDataSet(sql, para);
return(ret);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 根据关键词查询商品（高级版）
/// </summary>
/// <param name="key"></param>
/// <param name="part"></param>
/// <param name="pos"></param>
/// <param name="length"></param>
/// <param name="typed"></param>
/// <param name="type"></param>
/// <param name="priceLimit"></param>
/// <param name="price_u"></param>
/// <param name="price_t"></param>
/// <returns></returns>
public static DataSet QueryGoodsKey(string key, bool part = false, int pos = 0, int length = 0, bool typed = false, string type = "", int priceLimit = 0, int price_u = 0, int price_t = 0)
{
try
{
List <MySqlParameter> para_list = new List <MySqlParameter>();
string sql = "select * from tb_goods where 1=1";
if (key != null && key != "")
{
sql += " and goodsName like ?key"; para_list.Add(new MySqlParameter("?key", "%" + key + "%"));
}
if (typed)
{
sql += " and goodsTypeID=?type"; para_list.Add(new MySqlParameter("?type", type));
}
if (priceLimit == 1)
{
sql += " and unitPrice>?price_u"; para_list.Add(new MySqlParameter("?price_u", price_u));
}
else if (priceLimit == 2)
{
sql += " and unitPrice<?price_t"; para_list.Add(new MySqlParameter("?price_t", price_t));
}
else if (priceLimit == 3)
{
sql += " and unitPrice>?price_u and unitPrice<?price_t"; para_list.Add(new MySqlParameter("?price_u", price_u)); para_list.Add(new MySqlParameter("?price_t", price_t));
}
if (part)
{
sql += " limit ?st,?ed"; para_list.Add(new MySqlParameter("?st", pos)); para_list.Add(new MySqlParameter("?ed", length));
}
sql += ";";
MySqlParameter[] para = para_list.ToArray();
DataSet ds = DAL_MysqlHelper.ExecuteDataSet(sql, para);
return(ds);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 查询一个用户的个人信息
/// </summary>
/// <param name="name">Name</param>
/// <returns>Customer 或 null</returns>
public static Customer QueryCustomerPersonalInfo(string name)
{
try
{
string sql = "select customerBirthday,customerPhone,customerEmail from tb_customer where customerName=?name;";
MySqlParameter para = new MySqlParameter("?name", name);
DataTable dataTable = DAL_MysqlHelper.ExecuteDataSet(sql, para).Tables[0];
if (dataTable == null)
{
return(null);
}
Customer customer = new Customer();
customer.Birthday = dataTable.Rows[0]["customerBirthday"].ToString();
customer.Phone = dataTable.Rows[0]["customerPhone"].ToString();
customer.Email = dataTable.Rows[0]["customerEmail"].ToString();
return(customer);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 查询一个用户的收货信息
/// </summary>
/// <param name="name">Name</param>
/// <returns>Customer 或 null</returns>
public static Customer QueryCustomerPostInfo(string name)
{
try
{
string sql = "select customerTrueName,customerAddress,customerPostCode from tb_customer where customerName=?name;";
MySqlParameter para = new MySqlParameter("?name", name);
DataTable dataTable = DAL_MysqlHelper.ExecuteDataSet(sql, para).Tables[0];
if (dataTable == null)
{
return(null);
}
Customer customer = new Customer();
customer.TrueName = dataTable.Rows[0]["customerTrueName"].ToString();
customer.Address = dataTable.Rows[0]["customerAddress"].ToString();
customer.PostCode = dataTable.Rows[0]["customerPostCode"].ToString();
return(customer);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 根据ID查询商品信息
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static Entitys.Goods QueryGood(string id)
{
try
{
string sql = "select * from tb_goods where goodsID=?id;";
MySqlParameter para = new MySqlParameter("?id", id);
DataSet ds = DAL_MysqlHelper.ExecuteDataSet(sql, para);
Entitys.Goods good = new Goods();
good.Id = ds.Tables[0].Rows[0][0].ToString();
good.Name = ds.Tables[0].Rows[0][1].ToString();
good.TypeID = ds.Tables[0].Rows[0][2].ToString();
good.Desctipt = ds.Tables[0].Rows[0][3].ToString();
good.UnitPrice = decimal.Parse(ds.Tables[0].Rows[0][4].ToString());
good.ImageName = ds.Tables[0].Rows[0][5].ToString();
good.SellCount = ds.Tables[0].Rows[0][6].ToString();
good.Date = ds.Tables[0].Rows[0][7].ToString();
return(good);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 根据关键词查询查询出的商品数量
/// </summary>
/// <param name="key"></param>
/// <param name="typed"></param>
/// <param name="type"></param>
/// <param name="priceLimit"></param>
/// <param name="price_u"></param>
/// <param name="price_t"></param>
/// <returns></returns>
public static int QueryGoodsKeyCount(string key, bool typed = false, string type = "", int priceLimit = 0, int price_u = 0, int price_t = 0)
{
try
{
List <MySqlParameter> para_list = new List <MySqlParameter>();
string sql = "select count(*) from tb_goods where 1=1";
if (key != null && key != "")
{
sql += " and goodsName like ?key"; para_list.Add(new MySqlParameter("?key", "%" + key + "%"));
}
if (typed)
{
sql += " and goodsTypeID=?type"; para_list.Add(new MySqlParameter("?type", type));
}
if (priceLimit == 1)
{
sql += " and unitPrice>?price_u"; para_list.Add(new MySqlParameter("?price_u", price_u));
}
else if (priceLimit == 2)
{
sql += " and unitPrice<?price_t"; para_list.Add(new MySqlParameter("?price_t", price_t));
}
else if (priceLimit == 3)
{
sql += " and unitPrice>?price_u and unitPrice<?price_t"; para_list.Add(new MySqlParameter("?price_u", price_u)); para_list.Add(new MySqlParameter("?price_t", price_t));
}
sql += ";";
MySqlParameter[] para = para_list.ToArray();
int ret = Int32.Parse(DAL_MysqlHelper.ExecuteDataSet(sql, para).Tables[0].Rows[0][0].ToString());
return(ret);
}
catch (Exception e)
{
throw e;
}
}
