/// <summary>
/// 根据产品的价格、库存信息
/// </summary>
/// <param name="productid"></param>
/// <param name="origprice"></param>
/// <param name="sellprice"></param>
/// <param name="stock"></param>
/// <returns></returns>
public bool UpdateProdctPrice(string productid, decimal origprice, decimal sellprice, int stock, int priority, Mproduct mproduct)
{
string sql = "update product set ";
if (origprice > 0)
{
sql = sql + " origprice = ?origprice,";
}
if (sellprice > 0)
{
sql = sql + " sellprice = ?sellprice,";
}
if (stock >= 0)
{
sql = sql + " stock = ?stock,";
}
if (priority > 0)
{
sql = sql + " priority = ?priority,";
}
if (!string.IsNullOrEmpty(mproduct.productimgurl))
{
sql = sql + " productimgurl = ?productimgurl,";
}
if (mproduct.supclassid > 0)
{
sql = sql + " supclassid = " + mproduct.supclassid + ",";
sql = sql + " supclassName = (select classname from productclass where classid=" + mproduct.supclassid + "),";
}
if (mproduct.classid > 0)
{
sql = sql + " classid = " + mproduct.classid + ",";
sql = sql + " className = (select classname from productclass where classid=" + mproduct.classid + "),";
}
if (!string.IsNullOrEmpty(mproduct.productname))
{
sql = sql + " productname = ?productname,";
}
sql = sql + " productformat = ?productformat,";
sql = sql + " productformatunit = ?productformatunit,";
sql = sql + " origprice = ?origprice,";
sql = sql + " sellprice = ?sellprice,";
sql = sql + " stock = ?stock,";
sql = sql + " shelfstate = ?shelfstate,";
sql = sql + " priority = ?priority,";
sql = sql + " productdetails = ?productdetails,";
sql = sql.TrimEnd(',') + " where productid=?productid;";
List <MySqlParameter> parameterList = new List <MySqlParameter>();
MySqlParameter parameter = new MySqlParameter("?productid", MySqlDbType.VarChar, 25);
parameter.Value = productid;
parameterList.Add(parameter);
parameter = new MySqlParameter("?origprice", MySqlDbType.Decimal);
parameter.Value = origprice;
parameterList.Add(parameter);
parameter = new MySqlParameter("?sellprice", MySqlDbType.Decimal);
parameter.Value = sellprice;
parameterList.Add(parameter);
parameter = new MySqlParameter("?stock", MySqlDbType.Int32);
parameter.Value = stock;
parameterList.Add(parameter);
parameter = new MySqlParameter("?productformat", MySqlDbType.VarChar, 20);
parameter.Value = mproduct.productformat;
parameterList.Add(parameter);
parameter = new MySqlParameter("?productformatunit", MySqlDbType.VarChar, 50);
parameter.Value = mproduct.productformatunit;
parameterList.Add(parameter);
parameter = new MySqlParameter("?shelfstate", MySqlDbType.Int32);
parameter.Value = mproduct.shelfstate;
parameterList.Add(parameter);
parameter = new MySqlParameter("?productdetails", MySqlDbType.Text);
parameter.Value = mproduct.productdetails;
parameterList.Add(parameter);
parameter = new MySqlParameter("?productimgurl", MySqlDbType.VarChar, 100);
parameter.Value = mproduct.productimgurl;
parameterList.Add(parameter);
parameter = new MySqlParameter("?priority", MySqlDbType.Int32);
parameter.Value = mproduct.priority;
parameterList.Add(parameter);
parameter = new MySqlParameter("?productname", MySqlDbType.VarChar, 100);
parameter.Value = mproduct.productname;
parameterList.Add(parameter);
//// 执行操作
return(PKMySqlHelper.ExecuteNonQuery(sql, parameterList.ToArray()) > 0);
}
/// <summary>
/// 新增产品类别
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool AddProduct(Mproduct model)
{
//// sql语句
string sql = "INSERT INTO product(productid,classid,supclassid,className,supclassName,productname,productformat,productformatunit,origprice,sellprice,stock,shelfstate,hassellnum,productdetails,productimgurl,priority,isDelete,isEffective,great_time,modify_time,recommend) " +
"VALUES (?productid,?classid,?supclassid,(select classname from productclass where classid=?classid),(select classname from productclass where classid=?supclassid),?productname,?productformat,?productformatunit,?origprice,?sellprice,?stock,?shelfstate,?hassellnum,?productdetails,?productimgurl,?priority,?isDelete,?isEffective,?great_time,?modify_time,recommend)";
List <MySqlParameter> parameterList = new List <MySqlParameter>();
MySqlParameter parameter = new MySqlParameter("?productid", MySqlDbType.VarChar, 25);
parameter.Value = model.productid;
parameterList.Add(parameter);
parameter = new MySqlParameter("?classid", MySqlDbType.Int32);
parameter.Value = model.classid;
parameterList.Add(parameter);
parameter = new MySqlParameter("?supclassid", MySqlDbType.Int32);
parameter.Value = model.supclassid;
parameterList.Add(parameter);
parameter = new MySqlParameter("?productname", MySqlDbType.VarChar, 100);
parameter.Value = model.productname;
parameterList.Add(parameter);
parameter = new MySqlParameter("?productformat", MySqlDbType.VarChar, 20);
parameter.Value = model.productformat;
parameterList.Add(parameter);
parameter = new MySqlParameter("?productformatunit", MySqlDbType.VarChar, 50);
parameter.Value = model.productformatunit;
parameterList.Add(parameter);
parameter = new MySqlParameter("?origprice", MySqlDbType.Decimal);
parameter.Value = model.origprice;
parameterList.Add(parameter);
parameter = new MySqlParameter("?sellprice", MySqlDbType.Decimal);
parameter.Value = model.sellprice;
parameterList.Add(parameter);
parameter = new MySqlParameter("?stock", MySqlDbType.Int32);
parameter.Value = model.stock;
parameterList.Add(parameter);
parameter = new MySqlParameter("?shelfstate", MySqlDbType.Int32);
parameter.Value = model.shelfstate;
parameterList.Add(parameter);
parameter = new MySqlParameter("?hassellnum", MySqlDbType.Int32);
parameter.Value = 0;
parameterList.Add(parameter);
parameter = new MySqlParameter("?productdetails", MySqlDbType.Text);
parameter.Value = model.productdetails;
parameterList.Add(parameter);
parameter = new MySqlParameter("?productimgurl", MySqlDbType.VarChar, 100);
parameter.Value = model.productimgurl;
parameterList.Add(parameter);
parameter = new MySqlParameter("?priority", MySqlDbType.Int32);
parameter.Value = model.priority;
parameterList.Add(parameter);
parameter = new MySqlParameter("?isDelete", MySqlDbType.Int32);
parameter.Value = 0;
parameterList.Add(parameter);
parameter = new MySqlParameter("?isEffective", MySqlDbType.Int32);
parameter.Value = 1;
parameterList.Add(parameter);
DateTime dateTime = System.DateTime.Now;
parameter = new MySqlParameter("?great_time", MySqlDbType.DateTime);
parameter.Value = dateTime;
parameterList.Add(parameter);
parameter = new MySqlParameter("?modify_time", MySqlDbType.DateTime);
parameter.Value = dateTime;
parameterList.Add(parameter);
parameter = new MySqlParameter("?recommend", MySqlDbType.Int32);
parameter.Value = model.recommend;
parameterList.Add(parameter);
//// 执行操作
return(PKMySqlHelper.ExecuteNonQuery(sql, parameterList.ToArray()) > 0);
}
/// <summary>
/// 新增订单
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool Add(MshoppingCart model)
{
//// sql语句
string sql = "INSERT INTO shoppingCart (shoppingCartId,userId,userName,productId,productname,productformat,buyNum,origPrice,sellPrice,totalPrice,isDelete,isEffective,great_time,modify_time) " +
"VALUES(?shoppingCartId, ?userId, ?userName, ?productId, ?productname, ?productformat, ?buyNum, ?origPrice, ?sellPrice, ?totalPrice, ?isDelete, ?isEffective, ?great_time, ?modify_time)";
List <MySqlParameter> parameterList = new List <MySqlParameter>();
MySqlParameter parameter = new MySqlParameter("?shoppingCartId", MySqlDbType.VarChar, 25);
parameter.Value = model.shoppingCartId;
parameterList.Add(parameter);
parameter = new MySqlParameter("?userId", MySqlDbType.VarChar, 25);
parameter.Value = model.userId;
parameterList.Add(parameter);
parameter = new MySqlParameter("?userName", MySqlDbType.VarChar, 500);
parameter.Value = Base64.EncodeBase64(model.userName);
parameterList.Add(parameter);
parameter = new MySqlParameter("?productId", MySqlDbType.VarChar, 25);
parameter.Value = model.productId;
parameterList.Add(parameter);
parameter = new MySqlParameter("?productname", MySqlDbType.VarChar, 100);
parameter.Value = model.productname;
parameterList.Add(parameter);
parameter = new MySqlParameter("?productformat", MySqlDbType.VarChar, 50);
parameter.Value = model.productformat;
parameterList.Add(parameter);
parameter = new MySqlParameter("?buyNum", MySqlDbType.Int32);
parameter.Value = model.buyNum;
parameterList.Add(parameter);
parameter = new MySqlParameter("?origPrice", MySqlDbType.Decimal);
parameter.Value = model.origPrice;
parameterList.Add(parameter);
parameter = new MySqlParameter("?sellPrice", MySqlDbType.Decimal);
parameter.Value = model.sellPrice;
parameterList.Add(parameter);
parameter = new MySqlParameter("?totalPrice", MySqlDbType.Decimal);
parameter.Value = model.totalPrice;
parameterList.Add(parameter);
parameter = new MySqlParameter("?isDelete", MySqlDbType.Int16, 1);
parameter.Value = 0;
parameterList.Add(parameter);
parameter = new MySqlParameter("?isEffective", MySqlDbType.Int16, 1);
parameter.Value = 1;
parameterList.Add(parameter);
DateTime dateTime = System.DateTime.Now;
parameter = new MySqlParameter("?great_time", MySqlDbType.DateTime);
parameter.Value = dateTime;
parameterList.Add(parameter);
parameter = new MySqlParameter("?modify_time", MySqlDbType.DateTime);
parameter.Value = dateTime;
parameterList.Add(parameter);
//// 执行操作
return(PKMySqlHelper.ExecuteNonQuery(sql, parameterList.ToArray()) > 0);
}
/// <summary>
/// 新增地址
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool AddMailAddress(MmailAddress model)
{
//// sql语句
string sql = "";
if (model.isDefault == "1")
{
sql = "update mailAddress set isDefault=0 where userId=?userId;";
}
sql += "INSERT INTO mailAddress (addressId,userId,userName,province,city,area,detailedAddress,contactName,contactTell,isDefault,isDelete,isEffective,great_time,modify_time) " +
"VALUES(?addressId,?userId,?userName,?province,?city,?area,?detailedAddress,?contactName,?contactTell,?isDefault,?isDelete,?isEffective,?great_time,?modify_time)";
List <MySqlParameter> parameterList = new List <MySqlParameter>();
MySqlParameter parameter = new MySqlParameter("?addressId", MySqlDbType.VarChar, 25);
parameter.Value = model.addressId;
parameterList.Add(parameter);
parameter = new MySqlParameter("?userId", MySqlDbType.VarChar, 25);
parameter.Value = model.userId;
parameterList.Add(parameter);
parameter = new MySqlParameter("?userName", MySqlDbType.VarChar, 500);
parameter.Value = Base64.EncodeBase64(model.userName);
parameterList.Add(parameter);
parameter = new MySqlParameter("?province", MySqlDbType.VarChar, 30);
parameter.Value = string.IsNullOrEmpty(model.province)?"": model.province;
parameterList.Add(parameter);
parameter = new MySqlParameter("?city", MySqlDbType.VarChar, 30);
parameter.Value = string.IsNullOrEmpty(model.city) ? "" : model.city;
parameterList.Add(parameter);
parameter = new MySqlParameter("?area", MySqlDbType.VarChar, 30);
parameter.Value = string.IsNullOrEmpty(model.area) ? "" : model.area;
parameterList.Add(parameter);
parameter = new MySqlParameter("?detailedAddress", MySqlDbType.VarChar, 200);
parameter.Value = model.detailedAddress;
parameterList.Add(parameter);
parameter = new MySqlParameter("?contactName", MySqlDbType.VarChar, 30);
parameter.Value = model.contactName;
parameterList.Add(parameter);
parameter = new MySqlParameter("?contactTell", MySqlDbType.VarChar, 20);
parameter.Value = model.contactTell;
parameterList.Add(parameter);
parameter = new MySqlParameter("?isDefault", MySqlDbType.Int16, 1);
parameter.Value = model.isDefault;
parameterList.Add(parameter);
parameter = new MySqlParameter("?isDelete", MySqlDbType.Int16, 1);
parameter.Value = 0;
parameterList.Add(parameter);
parameter = new MySqlParameter("?isEffective", MySqlDbType.Int16, 1);
parameter.Value = 1;
parameterList.Add(parameter);
DateTime dateTime = System.DateTime.Now;
parameter = new MySqlParameter("?great_time", MySqlDbType.DateTime);
parameter.Value = dateTime;
parameterList.Add(parameter);
parameter = new MySqlParameter("?modify_time", MySqlDbType.DateTime);
parameter.Value = dateTime;
parameterList.Add(parameter);
//// 执行操作
return(PKMySqlHelper.ExecuteNonQuery(sql, parameterList.ToArray()) > 0);
}
/// <summary>
/// 分页获取新闻信息
/// </summary>
/// <param name="pagIndex">页码（第一页从1 开始）</param>
/// <param name="pagCount">每页数据条数</param>
/// <returns></returns>
public List <Musercoupon> GetPagList(string userId, int isUse, int pagIndex, int pagCount)
{
// 查询条件
StringBuilder sqlWhere = new StringBuilder(" 1=1 ");
if (!string.IsNullOrEmpty(userId))
{
sqlWhere.Append(" and userId = ?userId");
}
if (isUse >= 0)
{
sqlWhere.Append(" and isUse = ?isUse");
}
string sql = " SELECT id,userId,userName,couponId,name,price,consumAmount,effectiveStart,effectiveEnd,receiveTime,isUse,useTime,orderId,isDelete,isEffective,great_time,modify_time " +
$" FROM usercoupon WHERE {sqlWhere.ToString()} and isDelete=0 ORDER BY 1 desc ,1 desc limit {((pagIndex - 1) * pagCount)}, {pagCount}; ";
List <MySqlParameter> parameterList = new List <MySqlParameter>();
MySqlParameter parameter = new MySqlParameter("?userId", MySqlDbType.VarChar);
parameter.Value = userId;
parameterList.Add(parameter);
parameter = new MySqlParameter("?isUse", MySqlDbType.Int32);
parameter.Value = isUse;
parameterList.Add(parameter);
List <Musercoupon> listModel = null;
using (MySqlDataReader sqlDataReader = PKMySqlHelper.ExecuteReader(sql, parameterList.ToArray()))
{
if (sqlDataReader != null)
{
listModel = new List <Musercoupon>();
while (sqlDataReader.Read())
{
Musercoupon model = new Musercoupon();
model.id = sqlDataReader["id"] != DBNull.Value ? sqlDataReader["id"].ToString() : string.Empty;
model.userId = sqlDataReader["userId"] != DBNull.Value ? sqlDataReader["userId"].ToString() : string.Empty;
model.userName = sqlDataReader["userName"] != DBNull.Value ? sqlDataReader["userName"].ToString() : string.Empty;
model.userName = Base64.DecodeBase64(model.userName);
model.couponId = sqlDataReader["couponId"] != DBNull.Value ? sqlDataReader["couponId"].ToString() : string.Empty;
model.name = sqlDataReader["name"] != DBNull.Value ? sqlDataReader["name"].ToString() : string.Empty;
model.price = sqlDataReader["price"] != DBNull.Value ? Convert.ToDecimal(sqlDataReader["price"].ToString()) : 0M;
model.consumAmount = sqlDataReader["consumAmount"] != DBNull.Value ? Convert.ToDecimal(sqlDataReader["consumAmount"].ToString()) : 0M;
model.effectiveStart = sqlDataReader["effectiveStart"] != DBNull.Value ? Convert.ToDateTime(sqlDataReader["effectiveStart"].ToString()) : DateTime.MinValue;
model.effectiveEnd = sqlDataReader["effectiveEnd"] != DBNull.Value ? Convert.ToDateTime(sqlDataReader["effectiveEnd"].ToString()) : DateTime.MinValue;
model.receiveTime = sqlDataReader["receiveTime"] != DBNull.Value ? Convert.ToDateTime(sqlDataReader["receiveTime"].ToString()) : DateTime.MinValue;
model.useTime = sqlDataReader["useTime"] != DBNull.Value ? Convert.ToDateTime(sqlDataReader["useTime"].ToString()) : DateTime.MinValue;
model.isUse = sqlDataReader["isUse"] != DBNull.Value ? Convert.ToInt32(sqlDataReader["isUse"].ToString()) : 0;
model.orderId = sqlDataReader["orderId"] != DBNull.Value ? sqlDataReader["orderId"].ToString() : string.Empty;
model.isEffective = sqlDataReader["isEffective"] != DBNull.Value ? sqlDataReader["isEffective"].ToString() : string.Empty;
model.isDelete = sqlDataReader["isDelete"] != DBNull.Value ? sqlDataReader["isDelete"].ToString() : string.Empty;
model.great_time = sqlDataReader["great_time"] != DBNull.Value ? Convert.ToDateTime(sqlDataReader["great_time"].ToString()) : DateTime.MinValue;
model.modify_time = sqlDataReader["modify_time"] != DBNull.Value ? Convert.ToDateTime(sqlDataReader["modify_time"].ToString()) : DateTime.MinValue;
listModel.Add(model);
}
}
}
return(listModel);
}
/// <summary>
/// 使用优惠券
/// </summary>
/// <param name="id"></param>
/// <param name="orderId"></param>
/// <returns></returns>
public int UseCoupon(string id, string orderId)
{
string sql = $"update usercoupon set isUse=1,orderId='{orderId}',useTime='{System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}',modify_time='{System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}' where id='{id}'";
return(PKMySqlHelper.ExecuteNonQuery(sql));
}
/// <summary>
/// 分页获取微信用户信息
/// </summary>
/// <param name="pagIndex">页码（第一页从1 开始）</param>
/// <param name="pagCount">每页数据条数</param>
/// <returns></returns>
public List <MWXUserInfo> GetWXUserInfoPagList(int pagIndex, int pagCount, string nickname, string subscribe)
{
// 查询条件
StringBuilder sqlWhere = new StringBuilder(" 1=1 ");
if (!string.IsNullOrEmpty(nickname))
{
sqlWhere.Append(" and nickname like CONCAT('%',?nickname,'%') ");
}
if (!string.IsNullOrEmpty(subscribe))
{
sqlWhere.Append(" and subscribe= ?subscribe ");
}
string sql = " SELECT wxuserid,subscribe,openid,nickname,sex,sexdes,city,country,province,language,headimgurl,subscribe_time,remark,tagid_list,subscribe_scene,subscribe_scene_des,qr_scene,qr_scene_str,great_time,modify_time " +
$" FROM wxuser WHERE {sqlWhere.ToString()} ORDER BY wxuserid desc limit {((pagIndex - 1) * pagCount)}, {pagCount}; ";
//string sql = " SELECT TOP " + pagCount * pagIndex + " [wxuserid],[subscribe],[openid],[nickname],[sex],[sexdes],[city],[country],[province],[language],[headimgurl],[subscribe_time],[remark],[tagid_list],[subscribe_scene],[subscribe_scene_des],[qr_scene],[qr_scene_str],[great_time],[modify_time] " +
// " FROM( SELECT ROW_NUMBER() OVER(ORDER BY great_time DESC) AS ROWID,* FROM wxuser]) AS TEMP1 WHERE ROWID> " + pagCount * (pagIndex - 1);
List <MySqlParameter> parameterList = new List <MySqlParameter>();
MySqlParameter parameter = new MySqlParameter("?nickname", MySqlDbType.Int16, 1);
parameter.Value = nickname;
parameterList.Add(parameter);
parameter = new MySqlParameter("?subscribe", MySqlDbType.Int16, 50);
parameter.Value = subscribe;
parameterList.Add(parameter);
List <MWXUserInfo> listModel = null;
using (MySqlDataReader sqlDataReader = PKMySqlHelper.ExecuteReader(sql, parameterList.ToArray()))
{
if (sqlDataReader != null)
{
listModel = new List <MWXUserInfo>();
while (sqlDataReader.Read())
{
MWXUserInfo model = new MWXUserInfo();
model.wxuserid = sqlDataReader["wxuserid"] != DBNull.Value ? sqlDataReader["wxuserid"].ToString() : string.Empty;
model.subscribe = (sqlDataReader["subscribe"] != DBNull.Value && string.IsNullOrEmpty(sqlDataReader["subscribe"].ToString())) ? Convert.ToInt32(sqlDataReader["subscribe"].ToString()) : 0;
model.openid = sqlDataReader["openid"] != DBNull.Value ? sqlDataReader["openid"].ToString() : string.Empty;
model.nickname = sqlDataReader["nickname"] != DBNull.Value ? sqlDataReader["nickname"].ToString() : string.Empty;
model.nickname = Base64.DecodeBase64(model.nickname);
model.sex = sqlDataReader["sex"] != DBNull.Value ? sqlDataReader["sex"].ToString() : string.Empty;
model.sexdes = sqlDataReader["sexdes"] != DBNull.Value ? sqlDataReader["sexdes"].ToString() : string.Empty;
model.city = sqlDataReader["city"] != DBNull.Value ? sqlDataReader["city"].ToString() : string.Empty;
model.country = sqlDataReader["country"] != DBNull.Value ? sqlDataReader["country"].ToString() : string.Empty;
model.province = sqlDataReader["province"] != DBNull.Value ? sqlDataReader["province"].ToString() : string.Empty;
model.language = sqlDataReader["language"] != DBNull.Value ? sqlDataReader["language"].ToString() : string.Empty;
model.headimgurl = sqlDataReader["headimgurl"] != DBNull.Value ? sqlDataReader["headimgurl"].ToString() : string.Empty;
model.subscribe_time = sqlDataReader["subscribe_time"] != DBNull.Value ? sqlDataReader["subscribe_time"].ToString() : string.Empty;
model.remark = sqlDataReader["remark"] != DBNull.Value ? sqlDataReader["remark"].ToString() : string.Empty;
model.tagid_list = sqlDataReader["tagid_list"] != DBNull.Value ? sqlDataReader["tagid_list"].ToString() : string.Empty;
model.subscribe_scene = sqlDataReader["subscribe_scene"] != DBNull.Value ? sqlDataReader["subscribe_scene"].ToString() : string.Empty;
model.subscribe_scene_des = sqlDataReader["subscribe_scene_des"] != DBNull.Value ? sqlDataReader["subscribe_scene_des"].ToString() : string.Empty;
model.great_time = sqlDataReader["great_time"] != DBNull.Value ? Convert.ToDateTime(sqlDataReader["great_time"].ToString()) : DateTime.MinValue;
model.modify_time = sqlDataReader["modify_time"] != DBNull.Value ? Convert.ToDateTime(sqlDataReader["modify_time"].ToString()) : DateTime.MinValue;
listModel.Add(model);
}
}
}
return(listModel);
}
