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