/// <summary> /// 根据id获取微信菜单model /// </summary> /// <param name="openid"></param> /// <returns></returns> public Mwxmenu GetWxmenuByOpenid(string id) { Mwxmenu model = null; //// 语句 //string sql = "SELECT TOP 1 [id],[superId],[menuName],[type],[url],[isDelete],[isEffective],[great_time],[modify_time],[sortNum] FROM wxmenu where id=?id"; string sql = " SELECT id,superId,menuName,type,url,sortNum,isDelete,isEffective,great_time,modify_time " + $" FROM wxmenu WHERE id=?id; "; MySqlParameter[] parameterList = new MySqlParameter[1]; MySqlParameter parameter = new MySqlParameter("?id", MySqlDbType.VarChar, 25); parameter.Value = id; parameterList[0] = parameter; using (MySqlDataReader sqlDataReader = PKMySqlHelper.ExecuteReader(sql, parameterList)) { if (sqlDataReader.Read()) { model = new Mwxmenu(); model.sortNum = sqlDataReader["sortNum"] != DBNull.Value ? Convert.ToInt64(sqlDataReader["sortNum"].ToString()) : 10; model.id = sqlDataReader["id"] != DBNull.Value ? sqlDataReader["id"].ToString() : string.Empty; model.superId = sqlDataReader["superId"] != DBNull.Value ? sqlDataReader["superId"].ToString() : string.Empty; model.menuName = sqlDataReader["menuName"] != DBNull.Value ? sqlDataReader["menuName"].ToString() : string.Empty; model.type = sqlDataReader["type"] != DBNull.Value ? sqlDataReader["type"].ToString() : string.Empty; model.url = sqlDataReader["url"] != DBNull.Value ? sqlDataReader["url"].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; } } return(model); }
/// <summary> /// 根据类别ID获取model /// </summary> /// <param name="classid">classid</param> /// <returns></returns> public Mproductclass GetMproductclassByClassid(int classid) { string sql = " SELECT classid,supclassid,classname,priority,isDelete,isEffective,great_time,modify_time,imgpath from productclass where 1=1 and classid = ?classid;"; MySqlParameter[] parameterList = new MySqlParameter[1]; parameterList[0] = new MySqlParameter("?classid", MySqlDbType.Int32); parameterList[0].Value = classid; using (MySqlDataReader sqlDataReader = PKMySqlHelper.ExecuteReader(sql, parameterList)) { if (sqlDataReader != null) { if (sqlDataReader.Read()) { Mproductclass model = new Mproductclass(); model.classid = sqlDataReader["classid"] != DBNull.Value ? Convert.ToInt32(sqlDataReader["classid"].ToString()) : 0; model.supclassid = sqlDataReader["supclassid"] != DBNull.Value ? Convert.ToInt32(sqlDataReader["supclassid"].ToString()) : 0; model.priority = sqlDataReader["priority"] != DBNull.Value ? Convert.ToInt32(sqlDataReader["priority"].ToString()) : 0; model.classname = sqlDataReader["classname"] != DBNull.Value ? sqlDataReader["classname"].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; model.imgpath = sqlDataReader["imgpath"] != DBNull.Value ? sqlDataReader["imgpath"].ToString() : string.Empty; return(model); } } } return(null); }
/// <summary> /// 获取送货人model /// </summary> /// <param name="id"></param> /// <returns></returns> public MsendGoodsUser GetSendGoodsUserModelById(string id) { string sql = " SELECT id,userName,sex,phone,isDelete,isEffective,great_time,modify_time FROM sendGoodsUser where id=?id"; List <MySqlParameter> parameterList = new List <MySqlParameter>(); MySqlParameter parameter = new MySqlParameter("?id", MySqlDbType.VarChar, 25); parameter.Value = id; parameterList.Add(parameter); MsendGoodsUser model = null; using (MySqlDataReader sqlDataReader = PKMySqlHelper.ExecuteReader(sql, parameterList.ToArray())) { if (sqlDataReader != null) { if (sqlDataReader.Read()) { model = new MsendGoodsUser(); model.id = sqlDataReader["id"] != DBNull.Value ? sqlDataReader["id"].ToString() : string.Empty; model.userName = sqlDataReader["userName"] != DBNull.Value ? sqlDataReader["userName"].ToString() : string.Empty; model.userName = Base64.DecodeBase64(model.userName); model.phone = sqlDataReader["phone"] != DBNull.Value ? sqlDataReader["phone"].ToString() : string.Empty; model.sex = sqlDataReader["sex"] != DBNull.Value ? Convert.ToInt32(sqlDataReader["sex"].ToString()) : 0; 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; model.greatTime = model.great_time.ToString("yyyy-MM-dd HH:mm:ss"); } } } return(model); }
/// <summary> /// 根据登录账号获取用户信息 /// </summary> /// <param name="account"></param> /// <returns></returns> public Madminuser GetMadminuserModelByAcount(string account) { //// sql语句 string sql = " SELECT adminuserid,account,name,password,sex,isDelete,isEffective,great_time,modify_time " + "FROM adminuser where account=?account limit 1"; List <MySqlParameter> parameterList = new List <MySqlParameter>(); MySqlParameter parameter = new MySqlParameter("?account", MySqlDbType.VarChar, 50); parameter.Value = account; parameterList.Add(parameter); using (MySqlDataReader sqlDataReader = PKMySqlHelper.ExecuteReader(sql, parameterList.ToArray())) { if (sqlDataReader != null && sqlDataReader.Read()) { Madminuser model = new Madminuser(); model.adminuserid = sqlDataReader["adminuserid"] != DBNull.Value ? sqlDataReader["adminuserid"].ToString() : string.Empty; model.account = sqlDataReader["account"] != DBNull.Value ? sqlDataReader["account"].ToString() : string.Empty; model.name = sqlDataReader["name"] != DBNull.Value ? sqlDataReader["name"].ToString() : string.Empty; model.password = sqlDataReader["password"] != DBNull.Value ? sqlDataReader["password"].ToString() : string.Empty; model.sex = sqlDataReader["sex"] != DBNull.Value ? Convert.ToInt32(sqlDataReader["sex"].ToString()) : 0; 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; return(model); } } return(null); }
/// <summary> /// 根据id获取新闻信息 /// </summary> /// <param name="openid"></param> /// <returns></returns> public Mnews GetNewsById(string id) { Mnews model = null; string sql = " SELECT id,type,title,`value`,isDelete,isEffective,great_time,modify_time,img,url,sortNum " + $" FROM news WHERE id=?id ; "; MySqlParameter[] parameterList = new MySqlParameter[1]; MySqlParameter parameter = new MySqlParameter("?id", MySqlDbType.VarChar, 25); parameter.Value = id; parameterList[0] = parameter; using (MySqlDataReader sqlDataReader = PKMySqlHelper.ExecuteReader(sql, parameterList)) { if (sqlDataReader.Read()) { model = new Mnews(); model.id = sqlDataReader["id"] != DBNull.Value ? sqlDataReader["id"].ToString() : string.Empty; model.title = sqlDataReader["title"] != DBNull.Value ? sqlDataReader["title"].ToString() : string.Empty; model.value = sqlDataReader["value"] != DBNull.Value ? sqlDataReader["value"].ToString() : string.Empty; model.img = sqlDataReader["img"] != DBNull.Value ? sqlDataReader["img"].ToString() : string.Empty; model.url = sqlDataReader["url"] != DBNull.Value ? sqlDataReader["url"].ToString() : string.Empty; model.type = sqlDataReader["type"] != DBNull.Value ? Convert.ToInt32(sqlDataReader["type"].ToString()) : 0; model.sortNum = sqlDataReader["sortNum"] != DBNull.Value ? Convert.ToInt32(sqlDataReader["sortNum"].ToString()) : 0; 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; } } return model; }
/// <summary> /// 分页获取新闻信息 /// </summary> /// <param name="pagIndex">页码(第一页从1 开始)</param> /// <param name="pagCount">每页数据条数</param> /// <returns></returns> public List<Mnews> GetNewsPagList(int pagIndex, int pagCount,int type=1) { string sql = " SELECT id,type,title,`value`,isDelete,isEffective,great_time,modify_time,img,url,sortNum " + $" FROM news WHERE 1=1 and type="+type+$" ORDER BY sortNum asc ,1 desc limit {((pagIndex - 1) * pagCount)}, {pagCount}; "; List<Mnews> listModel = null; using (MySqlDataReader sqlDataReader = PKMySqlHelper.ExecuteReader(sql, null)) { if (sqlDataReader != null) { listModel = new List<Mnews>(); while (sqlDataReader.Read()) { Mnews model = new Mnews(); model.id = sqlDataReader["id"] != DBNull.Value ? sqlDataReader["id"].ToString() : string.Empty; model.title = sqlDataReader["title"] != DBNull.Value ? sqlDataReader["title"].ToString() : string.Empty; model.value = sqlDataReader["value"] != DBNull.Value ? sqlDataReader["value"].ToString() : string.Empty; model.img = sqlDataReader["img"] != DBNull.Value ? sqlDataReader["img"].ToString() : string.Empty; model.url = sqlDataReader["url"] != DBNull.Value ? sqlDataReader["url"].ToString() : string.Empty; model.sortNum = sqlDataReader["sortNum"] != DBNull.Value ? Convert.ToInt32(sqlDataReader["sortNum"].ToString()) : 0; model.type = sqlDataReader["type"] != DBNull.Value ? Convert.ToInt32(sqlDataReader["type"].ToString()) : 0; 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="pagIndex">页码(第一页从1 开始)</param> /// <param name="pagCount">每页数据条数</param> /// <returns></returns> public List <MsendGoodsUser> GetSendGoodsUserPagList(int pagIndex, int pagCount, string phone, string userName) { // 查询条件 StringBuilder sqlWhere = new StringBuilder(" 1=1 "); if (!string.IsNullOrEmpty(phone)) { sqlWhere.Append(" and phone like CONCAT('%',?phone,'%')"); } if (!string.IsNullOrEmpty(userName)) { sqlWhere.Append(" and userName like CONCAT('%',?userName,'%')"); } string sql = " SELECT id,userName,sex,phone,isDelete,isEffective,great_time,modify_time " + $" FROM sendGoodsUser WHERE {sqlWhere.ToString()} ORDER BY great_time desc limit {((pagIndex - 1) * pagCount)}, {pagCount}; "; //string sql = " SELECT TOP " + pagCount * pagIndex + " [id],[userName],[phone],[sex],[isDelete],[isEffective],[great_time],[modify_time] " + // " FROM( SELECT ROW_NUMBER() OVER(ORDER BY great_time DESC) AS ROWID,* FROM sendGoodsUser) AS TEMP1 WHERE ROWID> " + pagCount * (pagIndex - 1); List <MySqlParameter> parameterList = new List <MySqlParameter>(); MySqlParameter parameter = new MySqlParameter("?phone", MySqlDbType.VarChar, 50); parameter.Value = "%" + phone + "%"; parameterList.Add(parameter); parameter = new MySqlParameter("?userName", MySqlDbType.VarChar, 500); parameter.Value = "%" + Base64.EncodeBase64(userName) + "%"; parameterList.Add(parameter); List <MsendGoodsUser> listModel = null; using (MySqlDataReader sqlDataReader = PKMySqlHelper.ExecuteReader(sql, parameterList.ToArray())) { if (sqlDataReader != null) { listModel = new List <MsendGoodsUser>(); while (sqlDataReader.Read()) { MsendGoodsUser model = new MsendGoodsUser(); model.id = sqlDataReader["id"] != DBNull.Value ? sqlDataReader["id"].ToString() : string.Empty; model.userName = sqlDataReader["userName"] != DBNull.Value ? sqlDataReader["userName"].ToString() : string.Empty; model.userName = Base64.DecodeBase64(model.userName); model.phone = sqlDataReader["phone"] != DBNull.Value ? sqlDataReader["phone"].ToString() : string.Empty; model.sex = sqlDataReader["sex"] != DBNull.Value ? Convert.ToInt32(sqlDataReader["sex"].ToString()) : 0; 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; model.greatTime = model.great_time.ToString("yyyy-MM-dd HH:mm:ss"); listModel.Add(model); } } } return(listModel); }
/// <summary> /// 获取产品数据总条数 /// </summary> /// <param name="classid"></param> /// <param name="supclassid"></param> /// <param name="productname"></param> /// <param name="shelfstate"></param> /// <returns></returns> public int GetProductPagCount(int classid, int supclassid, string productname, int shelfstate, int isDelete = 0, int recommend = -1) { string sql = $" SELECT count(productid) as totalCount FROM product WHERE isDelete={isDelete} "; if (classid > 0) { sql = sql + " and classid =?classid"; } if (supclassid > 0) { sql = sql + " and supclassid =?supclassid"; } if (!string.IsNullOrEmpty(productname)) { sql = sql + " and productname like CONCAT('%',?productname,'%')"; } if (shelfstate != 0) { sql = sql + " and shelfstate =" + shelfstate; } if (recommend >= 0) { sql = sql + " and recommend =" + recommend; } List <MySqlParameter> parameterList = new List <MySqlParameter>(); MySqlParameter parameter = new MySqlParameter("?classid", MySqlDbType.Int32); parameter.Value = classid; parameterList.Add(parameter); parameter = new MySqlParameter("?supclassid", MySqlDbType.Int32); parameter.Value = supclassid; parameterList.Add(parameter); parameter = new MySqlParameter("?productname", MySqlDbType.VarChar, 100); parameter.Value = productname; parameterList.Add(parameter); using (MySqlDataReader sqlDataReader = PKMySqlHelper.ExecuteReader(sql, parameterList.ToArray())) { if (sqlDataReader != null) { if (sqlDataReader.Read()) { return((sqlDataReader["totalCount"] != DBNull.Value) ? Convert.ToInt32(sqlDataReader["totalCount"].ToString()) : 0); } } } return(0); }
/// <summary> /// 分页获取管理严用户信息 /// </summary> /// <param name="pagIndex">页码(第一页从1 开始)</param> /// <param name="pagCount">每页数据条数</param> /// <returns></returns> public List <Madminuser> GetAdminUserInfoPagList(int pagIndex, int pagCount, string acount, string name) { // 查询条件 StringBuilder sqlWhere = new StringBuilder(" 1=1 "); if (!string.IsNullOrEmpty(acount)) { sqlWhere.Append(" and account like CONCAT('%',?account,'%')"); } if (!string.IsNullOrEmpty(name)) { sqlWhere.Append(" and name like CONCAT('%',?name,'%')"); } string sql = " SELECT adminuserid,account,name,password,sex,isDelete,isEffective,great_time,modify_time " + $" FROM adminuser WHERE {sqlWhere.ToString()} ORDER BY 1 desc limit {((pagIndex - 1) * pagCount)}, {pagCount}; "; List <MySqlParameter> parameterList = new List <MySqlParameter>(); MySqlParameter parameter = new MySqlParameter("?acount", MySqlDbType.VarChar, 50); parameter.Value = "%" + acount + "%"; parameterList.Add(parameter); parameter = new MySqlParameter("?name", MySqlDbType.VarChar, 50); parameter.Value = "%" + name + "%"; parameterList.Add(parameter); List <Madminuser> listModel = null; using (MySqlDataReader sqlDataReader = PKMySqlHelper.ExecuteReader(sql, parameterList.ToArray())) { if (sqlDataReader != null) { listModel = new List <Madminuser>(); while (sqlDataReader.Read()) { Madminuser model = new Madminuser(); model.adminuserid = sqlDataReader["adminuserid"] != DBNull.Value ? sqlDataReader["adminuserid"].ToString() : string.Empty; model.account = sqlDataReader["account"] != DBNull.Value ? sqlDataReader["account"].ToString() : string.Empty; model.name = sqlDataReader["name"] != DBNull.Value ? sqlDataReader["name"].ToString() : string.Empty; //// model.password = sqlDataReader["password"] != DBNull.Value ? sqlDataReader["password"].ToString() : string.Empty; model.sex = sqlDataReader["sex"] != DBNull.Value ? Convert.ToInt32(sqlDataReader["sex"].ToString()) : 0; 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; model.greatTime = model.great_time.ToString("yyyy-MM-dd HH:mm:ss"); listModel.Add(model); } } } return(listModel); }
/// <summary> /// 根据id获取 /// </summary> /// <param name="id"></param> /// <returns></returns> public Musercoupon GetById(string id) { Musercoupon model = null; 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 id=?id ; "; MySqlParameter[] parameterList = new MySqlParameter[1]; MySqlParameter parameter = new MySqlParameter("?id", MySqlDbType.VarChar, 25); parameter.Value = id; parameterList[0] = parameter; using (MySqlDataReader sqlDataReader = PKMySqlHelper.ExecuteReader(sql, parameterList)) { if (sqlDataReader.Read()) { 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; } } return(model); }
/// <summary> /// 根据用户ID获取其全部购物车产品 /// </summary> /// <param name="userId"></param> /// <returns></returns> public List <MshoppingCart> GetAllShoppingCartListBySserId(string userId) { string sql = @"select sc.shoppingCartId,sc.userId,sc.userName,sc.productId, p.productname,p.productformat,sc.buyNum,p.origPrice,p.sellPrice, sc.totalPrice,sc.isDelete,sc.isEffective,sc.great_time, sc.modify_time from shoppingCart as sc JOIN product as p on sc.productId = p.productId where sc.userId=?userId;"; List <MySqlParameter> parameterList = new List <MySqlParameter>(); MySqlParameter parameter = parameter = new MySqlParameter("?userId", MySqlDbType.VarChar, 25); parameter.Value = userId; parameterList.Add(parameter); List <MshoppingCart> listModel = null; using (MySqlDataReader sqlDataReader = PKMySqlHelper.ExecuteReader(sql, parameterList.ToArray())) { if (sqlDataReader != null) { listModel = new List <MshoppingCart>(); while (sqlDataReader.Read()) { MshoppingCart model = new MshoppingCart(); model.shoppingCartId = sqlDataReader["shoppingCartId"] != DBNull.Value ? sqlDataReader["shoppingCartId"].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.productId = sqlDataReader["productId"] != DBNull.Value ? sqlDataReader["productId"].ToString() : string.Empty; model.productname = sqlDataReader["productname"] != DBNull.Value ? sqlDataReader["productname"].ToString() : string.Empty; model.productformat = sqlDataReader["productformat"] != DBNull.Value ? sqlDataReader["productformat"].ToString() : string.Empty; model.buyNum = sqlDataReader["buyNum"] != DBNull.Value ? Convert.ToInt32(sqlDataReader["buyNum"].ToString()) : 0; model.origPrice = sqlDataReader["origPrice"] != DBNull.Value ? Convert.ToDecimal(sqlDataReader["origPrice"].ToString()) : 0M; model.sellPrice = sqlDataReader["sellPrice"] != DBNull.Value ? Convert.ToDecimal(sqlDataReader["sellPrice"].ToString()) : 0M; model.totalPrice = sqlDataReader["totalPrice"] != DBNull.Value ? Convert.ToDecimal(sqlDataReader["totalPrice"].ToString()) : 0M; 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> /// 根据产品ID获取产品model /// </summary> /// <param name="productid"></param> /// <returns></returns> public Mproduct GetMproductModelById(string productid) { string sql = "select productid,classid,supclassid,className,supclassName,productname,productformat,productformatunit,origprice,sellprice,stock,shelfstate,hassellnum," + "productdetails,productimgurl,priority,isDelete,isEffective,great_time,modify_time,recommend from product where isDelete=0 and productid=?productid;"; List <MySqlParameter> parameterList = new List <MySqlParameter>(); MySqlParameter parameter = new MySqlParameter("?productid", MySqlDbType.VarChar, 25); parameter.Value = productid; parameterList.Add(parameter); using (MySqlDataReader sqlDataReader = PKMySqlHelper.ExecuteReader(sql, parameterList.ToArray())) { if (sqlDataReader != null) { if (sqlDataReader.Read()) { Mproduct model = new Mproduct(); model.productid = sqlDataReader["productid"] != DBNull.Value ? sqlDataReader["productid"].ToString() : string.Empty; model.classid = sqlDataReader["classid"] != DBNull.Value ? Convert.ToInt32(sqlDataReader["classid"].ToString()) : 0; model.supclassid = sqlDataReader["supclassid"] != DBNull.Value ? Convert.ToInt32(sqlDataReader["supclassid"].ToString()) : 0; model.className = sqlDataReader["className"] != DBNull.Value ? sqlDataReader["className"].ToString() : string.Empty; model.supclassName = sqlDataReader["supclassName"] != DBNull.Value ? sqlDataReader["supclassName"].ToString() : string.Empty; model.productname = sqlDataReader["productname"] != DBNull.Value ? sqlDataReader["productname"].ToString() : string.Empty; model.productformat = sqlDataReader["productformat"] != DBNull.Value ? sqlDataReader["productformat"].ToString() : string.Empty; model.productformatunit = sqlDataReader["productformatunit"] != DBNull.Value ? sqlDataReader["productformatunit"].ToString() : string.Empty; model.origprice = sqlDataReader["origprice"] != DBNull.Value ? Convert.ToDecimal(sqlDataReader["origprice"].ToString()) : 0; model.sellprice = sqlDataReader["sellprice"] != DBNull.Value ? Convert.ToDecimal(sqlDataReader["sellprice"].ToString()) : 0; model.stock = sqlDataReader["stock"] != DBNull.Value ? Convert.ToInt32(sqlDataReader["stock"].ToString()) : 0; model.shelfstate = sqlDataReader["shelfstate"] != DBNull.Value ? Convert.ToInt32(sqlDataReader["shelfstate"].ToString()) : 0; model.hassellnum = sqlDataReader["hassellnum"] != DBNull.Value ? Convert.ToInt32(sqlDataReader["hassellnum"].ToString()) : 0; model.productdetails = sqlDataReader["productdetails"] != DBNull.Value ? sqlDataReader["productdetails"].ToString() : string.Empty; model.productimgurl = sqlDataReader["productimgurl"] != DBNull.Value ? sqlDataReader["productimgurl"].ToString() : string.Empty; model.priority = sqlDataReader["priority"] != DBNull.Value ? Convert.ToInt32(sqlDataReader["priority"].ToString()) : 0; 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; model.recommend = sqlDataReader["recommend"] != DBNull.Value ? Convert.ToInt32(sqlDataReader["recommend"].ToString()) : 0; return(model); } } } return(null); }
/// <summary> /// 获取总条数 /// </summary> /// <returns></returns> public int GetPagCount() { string sql = " SELECT count(*) as totalCount FROM coupon WHERE isDelete=0 "; using (MySqlDataReader sqlDataReader = PKMySqlHelper.ExecuteReader(sql, null)) { if (sqlDataReader != null) { if (sqlDataReader.Read()) { return((sqlDataReader["totalCount"] != DBNull.Value) ? Convert.ToInt32(sqlDataReader["totalCount"].ToString()) : 0); } } } return(0); }
/// <summary> /// 根据Openid获取用户信息 /// </summary> /// <param name="openid"></param> /// <returns></returns> public MWXUserInfo GetWXUserInfoByOpenid(string openid) { MWXUserInfo model = null; //// 语句 string sql = "SELECT wxuserid,subscribe,openid,nickname,sex,sexdes,city,country,province,language,headimgurl,subscribe_time,remark,tagid_list,subscribe_scene,subscribe_scene_des,great_time,modify_time FROM wxuser where openid=?openid"; MySqlParameter[] parameterList = new MySqlParameter[1]; MySqlParameter parameter = new MySqlParameter("?openid", MySqlDbType.VarChar, 50); parameter.Value = openid; parameterList[0] = parameter; using (MySqlDataReader sqlDataReader = PKMySqlHelper.ExecuteReader(sql, parameterList)) { if (sqlDataReader.Read()) { 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; } } return(model); }
/// <summary> /// 根据用户ID和产品id获取其对应的购买产品的购物车信息 /// </summary> /// <param name="userId"></param> /// <returns></returns> public MshoppingCart GetShoppingCartListByUserIdAndProductId(string userId, string productId) { string sql = "select shoppingCartId,userId,userName,productId,productname,productformat,buyNum,origPrice,sellPrice,totalPrice,isDelete,isEffective,great_time,modify_time from shoppingCart where userId=?userId and productId=?productId;"; List <MySqlParameter> parameterList = new List <MySqlParameter>(); MySqlParameter parameter = parameter = new MySqlParameter("?userId", MySqlDbType.VarChar, 25); parameter.Value = userId; parameterList.Add(parameter); parameter = parameter = new MySqlParameter("?productId", MySqlDbType.VarChar, 25); parameter.Value = productId; parameterList.Add(parameter); MshoppingCart model = null; using (MySqlDataReader sqlDataReader = PKMySqlHelper.ExecuteReader(sql, parameterList.ToArray())) { if (sqlDataReader != null) { if (sqlDataReader.Read()) { model = new MshoppingCart(); model.shoppingCartId = sqlDataReader["shoppingCartId"] != DBNull.Value ? sqlDataReader["shoppingCartId"].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.productId = sqlDataReader["productId"] != DBNull.Value ? sqlDataReader["productId"].ToString() : string.Empty; model.productname = sqlDataReader["productname"] != DBNull.Value ? sqlDataReader["productname"].ToString() : string.Empty; model.productformat = sqlDataReader["productformat"] != DBNull.Value ? sqlDataReader["productformat"].ToString() : string.Empty; model.buyNum = sqlDataReader["buyNum"] != DBNull.Value ? Convert.ToInt32(sqlDataReader["buyNum"].ToString()) : 0; model.origPrice = sqlDataReader["origPrice"] != DBNull.Value ? Convert.ToDecimal(sqlDataReader["origPrice"].ToString()) : 0M; model.sellPrice = sqlDataReader["sellPrice"] != DBNull.Value ? Convert.ToDecimal(sqlDataReader["sellPrice"].ToString()) : 0M; model.totalPrice = sqlDataReader["totalPrice"] != DBNull.Value ? Convert.ToDecimal(sqlDataReader["totalPrice"].ToString()) : 0M; 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; } } } return(model); }
/// <summary> /// 根据用户ID获取其全部地址信息 /// </summary> /// <param name="userId"></param> /// <returns></returns> public List <MmailAddress> GetMmailAddressesByUserId(string userId) { string sql = "select addressId,userId,userName,province,city,area,detailedAddress,contactName,contactTell,isDefault,isDelete,isEffective,great_time,modify_time from mailAddress where userId=?userId;"; List <MySqlParameter> parameterList = new List <MySqlParameter>(); MySqlParameter parameter = new MySqlParameter("?userId", MySqlDbType.VarChar, 25); parameter.Value = userId; parameterList.Add(parameter); List <MmailAddress> listModel = null; using (MySqlDataReader sqlDataReader = PKMySqlHelper.ExecuteReader(sql, parameterList.ToArray())) { if (sqlDataReader != null) { listModel = new List <MmailAddress>(); while (sqlDataReader.Read()) { MmailAddress model = new MmailAddress(); model.addressId = sqlDataReader["addressId"] != DBNull.Value ? sqlDataReader["addressId"].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.province = sqlDataReader["province"] != DBNull.Value ? sqlDataReader["province"].ToString() : string.Empty; model.city = sqlDataReader["city"] != DBNull.Value ? sqlDataReader["city"].ToString() : string.Empty; model.area = sqlDataReader["area"] != DBNull.Value ? sqlDataReader["area"].ToString() : string.Empty; model.detailedAddress = sqlDataReader["detailedAddress"] != DBNull.Value ? sqlDataReader["detailedAddress"].ToString() : string.Empty; model.contactName = sqlDataReader["contactName"] != DBNull.Value ? sqlDataReader["contactName"].ToString() : string.Empty; model.contactTell = sqlDataReader["contactTell"] != DBNull.Value ? sqlDataReader["contactTell"].ToString() : string.Empty; model.isDefault = sqlDataReader["isDefault"] != DBNull.Value ? sqlDataReader["isDefault"].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="classid"></param> /// <returns></returns> public int CetProductBySupClassid(int supclassid) { string sql = " SELECT count(productid) as totalCount FROM product WHERE isDelete=0 and supclassid=?supclassid"; MySqlParameter[] parameterList = new MySqlParameter[1]; parameterList[0] = new MySqlParameter("?supclassid", MySqlDbType.Int32); parameterList[0].Value = supclassid; using (MySqlDataReader sqlDataReader = PKMySqlHelper.ExecuteReader(sql, parameterList.ToArray())) { if (sqlDataReader != null) { if (sqlDataReader.Read()) { return((sqlDataReader["totalCount"] != DBNull.Value) ? Convert.ToInt32(sqlDataReader["totalCount"].ToString()) : 0); } } } return(0); }
/// <summary> /// 获取总条数 /// </summary> /// <returns></returns> public int GetPagCount(string userId, int isUse) { // 查询条件 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 count(*) as totalCount FROM usercoupon WHERE isDelete=0 and {sqlWhere.ToString()}"; 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); using (MySqlDataReader sqlDataReader = PKMySqlHelper.ExecuteReader(sql, parameterList.ToArray())) { if (sqlDataReader != null) { if (sqlDataReader.Read()) { return((sqlDataReader["totalCount"] != DBNull.Value) ? Convert.ToInt32(sqlDataReader["totalCount"].ToString()) : 0); } } } return(0); }
/// <summary> /// 获取全部微信菜单 /// </summary> /// <param name="superId">-1代表获取全部</param> /// <returns></returns> public List <Mwxmenu> GetAllWxmenu(string superId) { string sql = " SELECT id,superId,menuName,type,url,sortNum,isDelete,isEffective,great_time,modify_time FROM wxmenu where 1=1 "; if (superId != "-1") { sql = sql + " and superId=?superId order by superId desc,id desc "; } MySqlParameter[] parameterList = new MySqlParameter[1]; MySqlParameter parameter = new MySqlParameter("?superId", MySqlDbType.VarChar, 25); parameter.Value = superId; parameterList[0] = parameter; List <Mwxmenu> listModel = null; using (MySqlDataReader sqlDataReader = PKMySqlHelper.ExecuteReader(sql, parameterList)) { if (sqlDataReader != null) { listModel = new List <Mwxmenu>(); while (sqlDataReader.Read()) { Mwxmenu model = new Mwxmenu(); model.sortNum = sqlDataReader["sortNum"] != DBNull.Value ? Convert.ToInt64(sqlDataReader["sortNum"].ToString()) : 10; model.id = sqlDataReader["id"] != DBNull.Value ? sqlDataReader["id"].ToString() : string.Empty; model.superId = sqlDataReader["superId"] != DBNull.Value ? sqlDataReader["superId"].ToString() : string.Empty; model.menuName = sqlDataReader["menuName"] != DBNull.Value ? sqlDataReader["menuName"].ToString() : string.Empty; model.type = sqlDataReader["type"] != DBNull.Value ? sqlDataReader["type"].ToString() : string.Empty; model.url = sqlDataReader["url"] != DBNull.Value ? sqlDataReader["url"].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="pagIndex">页码(第一页从1 开始)</param> /// <param name="pagCount">每页数据条数</param> /// <returns></returns> public List <Mcoupon> GetNewsPagList(int pagIndex, int pagCount) { string sql = " SELECT id,name,price,consumAmount,effectiveStart,effectiveEnd,creatCount,useCount,isDelete,isEffective,great_time,modify_time " + $" FROM coupon WHERE 1=1 and isDelete=0 ORDER BY 1 desc ,1 desc limit {((pagIndex - 1) * pagCount)}, {pagCount}; "; List <Mcoupon> listModel = null; using (MySqlDataReader sqlDataReader = PKMySqlHelper.ExecuteReader(sql, null)) { if (sqlDataReader != null) { listModel = new List <Mcoupon>(); while (sqlDataReader.Read()) { Mcoupon model = new Mcoupon(); model.id = sqlDataReader["id"] != DBNull.Value ? sqlDataReader["id"].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.creatCount = sqlDataReader["creatCount"] != DBNull.Value ? Convert.ToInt32(sqlDataReader["creatCount"].ToString()) : 0; model.useCount = sqlDataReader["useCount"] != DBNull.Value ? Convert.ToInt32(sqlDataReader["useCount"].ToString()) : 0; model.isEffective = sqlDataReader["isEffective"] != DBNull.Value ? sqlDataReader["isEffective"].ToString() : string.Empty; model.isDelete = sqlDataReader["isEffective"] != DBNull.Value ? sqlDataReader["isEffective"].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> /// <returns></returns> public int GetSndGoodsUserPagCount(string phone, string userName) { string sql = " SELECT count(id) as totalCount FROM sendGoodsUser WHERE 1=1 "; if (!string.IsNullOrEmpty(phone)) { sql = sql + " and phone like CONCAT('%',?phone,'%')"; } if (!string.IsNullOrEmpty(userName)) { sql = sql + " and userName like CONCAT('%',?userName,'%')"; } List <MySqlParameter> parameterList = new List <MySqlParameter>(); MySqlParameter parameter = new MySqlParameter("?phone", MySqlDbType.VarChar, 50); parameter.Value = "%" + phone + "%"; parameterList.Add(parameter); parameter = new MySqlParameter("?userName", MySqlDbType.VarChar, 500); parameter.Value = "%" + Base64.EncodeBase64(userName) + "%"; parameterList.Add(parameter); using (MySqlDataReader sqlDataReader = PKMySqlHelper.ExecuteReader(sql, parameterList.ToArray())) { if (sqlDataReader != null) { if (sqlDataReader.Read()) { return((sqlDataReader["totalCount"] != DBNull.Value) ? Convert.ToInt32(sqlDataReader["totalCount"].ToString()) : 0); } } } return(0); }
/// <summary> /// 获取管理员数据总条数 /// </summary> /// <returns></returns> public int GetAdminUserInfoPagCount(string acount, string name) { string sql = " SELECT count(adminuserid) as totalCount FROM adminuser WHERE 1=1 "; if (!string.IsNullOrEmpty(acount)) { sql = sql + " and account like CONCAT('%',?account,'%')"; } if (!string.IsNullOrEmpty(name)) { sql = sql + " and name like CONCAT('%',?name,'%')"; } List <MySqlParameter> parameterList = new List <MySqlParameter>(); MySqlParameter parameter = new MySqlParameter("?acount", MySqlDbType.VarChar, 50); parameter.Value = "%" + acount + "%"; parameterList.Add(parameter); parameter = new MySqlParameter("?name", MySqlDbType.VarChar, 50); parameter.Value = "%" + name + "%"; parameterList.Add(parameter); using (MySqlDataReader sqlDataReader = PKMySqlHelper.ExecuteReader(sql, parameterList.ToArray())) { if (sqlDataReader != null) { if (sqlDataReader.Read()) { return((sqlDataReader["totalCount"] != DBNull.Value) ? Convert.ToInt32(sqlDataReader["totalCount"].ToString()) : 0); } } } return(0); }
/// <summary> /// 获取微信用户信息总条数 /// </summary> /// <returns></returns> public int GetWXUserInfoPagCount(string nickname, string subscribe) { string sql = " SELECT count(wxuserid) as totalCount FROM wxuser WHERE 1=1 "; if (!string.IsNullOrEmpty(nickname)) { sql = sql + " and nickname like CONCAT('%',?nickname,'%') "; } if (!string.IsNullOrEmpty(subscribe)) { sql = sql + " and subscribe= ?subscribe "; } 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); using (MySqlDataReader sqlDataReader = PKMySqlHelper.ExecuteReader(sql, parameterList.ToArray())) { if (sqlDataReader != null) { if (sqlDataReader.Read()) { return((sqlDataReader["totalCount"] != DBNull.Value) ? Convert.ToInt32(sqlDataReader["totalCount"].ToString()) : 0); } } } return(0); }
/// <summary> /// 直接获取所有分类 /// </summary> /// <returns></returns> public List <Mproductclass> GetMproductclasses(int supclassid) { string sql = " SELECT classid,supclassid,classname,priority,isDelete,isEffective,great_time,modify_time,imgpath from productclass where 1=1 "; if (supclassid >= 0) { sql = sql + " and supclassid = ?supclassid "; } sql = sql + " order by priority asc;"; MySqlParameter[] parameterList = new MySqlParameter[1]; parameterList[0] = new MySqlParameter("?supclassid", MySqlDbType.Int32); parameterList[0].Value = supclassid; List <Mproductclass> listModel = null; using (MySqlDataReader sqlDataReader = PKMySqlHelper.ExecuteReader(sql, parameterList)) { if (sqlDataReader != null) { listModel = new List <Mproductclass>(); while (sqlDataReader.Read()) { Mproductclass model = new Mproductclass(); model.classid = sqlDataReader["classid"] != DBNull.Value ? Convert.ToInt32(sqlDataReader["classid"].ToString()) : 0; model.supclassid = sqlDataReader["supclassid"] != DBNull.Value ? Convert.ToInt32(sqlDataReader["supclassid"].ToString()) : 0; model.priority = sqlDataReader["priority"] != DBNull.Value ? Convert.ToInt32(sqlDataReader["priority"].ToString()) : 0; model.classname = sqlDataReader["classname"] != DBNull.Value ? sqlDataReader["classname"].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; model.imgpath = sqlDataReader["imgpath"] != DBNull.Value ? sqlDataReader["imgpath"].ToString() : string.Empty; listModel.Add(model); } } } return(listModel); }
/// <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); }
/// <summary> /// 获取产品数据总条数 /// </summary> /// <param name="classid"></param> /// <param name="supclassid"></param> /// <param name="productname"></param> /// <param name="shelfstate"></param> /// <returns></returns> public List <Mproduct> GetProductPagList(int pagIndex, int pagCount, int classid, int supclassid, string productname, int shelfstate, int isDelete = 0, int recommend = -1) { // 查询条件 StringBuilder sqlWhere = new StringBuilder($" isDelete ={isDelete} "); if (classid > 0) { sqlWhere.Append(" and classid =?classid"); } if (supclassid > 0) { sqlWhere.Append(" and supclassid =?supclassid"); } if (!string.IsNullOrEmpty(productname)) { sqlWhere.Append(" and productname like CONCAT('%',?productname,'%')"); } if (shelfstate != 0) { sqlWhere.Append(" and shelfstate =?shelfstate"); } if (recommend >= 0) { sqlWhere.Append(" and recommend =" + recommend); } string sql = " SELECT productid,classid,supclassid,className,supclassName,productname,productformat,productformatunit,origprice,sellprice,stock,shelfstate,hassellnum," + "productdetails,productimgurl,priority,isDelete,isEffective,great_time,modify_time,recommend " + $" FROM product WHERE {sqlWhere.ToString()} ORDER BY priority ASC , productid desc limit {((pagIndex - 1) * pagCount)}, {pagCount}; "; //string sql = " SELECT TOP " + pagCount * pagIndex + " productid,classid,supclassid,className,supclassName,productname,productformat,productformatunit,origprice,sellprice,stock,shelfstate,hassellnum," + // "productdetails,productimgurl,priority,isDelete,isEffective,great_time,modify_time " + // " FROM( SELECT ROW_NUMBER() OVER(ORDER BY great_time DESC) AS ROWID,* FROM product) AS TEMP1 WHERE isDelete=0 and ROWID> " + pagCount * (pagIndex - 1); List <MySqlParameter> parameterList = new List <MySqlParameter>(); MySqlParameter parameter = new MySqlParameter("?classid", MySqlDbType.Int32); parameter.Value = classid; parameterList.Add(parameter); parameter = new MySqlParameter("?supclassid", MySqlDbType.Int32); parameter.Value = supclassid; parameterList.Add(parameter); parameter = new MySqlParameter("?productname", MySqlDbType.VarChar, 100); parameter.Value = productname; parameterList.Add(parameter); parameter = new MySqlParameter("?shelfstate", MySqlDbType.Int32); parameter.Value = shelfstate; parameterList.Add(parameter); List <Mproduct> listModel = null; using (MySqlDataReader sqlDataReader = PKMySqlHelper.ExecuteReader(sql, parameterList.ToArray())) { if (sqlDataReader != null) { listModel = new List <Mproduct>(); while (sqlDataReader.Read()) { Mproduct model = new Mproduct(); model.productid = sqlDataReader["productid"] != DBNull.Value ? sqlDataReader["productid"].ToString() : string.Empty; model.classid = sqlDataReader["classid"] != DBNull.Value ? Convert.ToInt32(sqlDataReader["classid"].ToString()) : 0; model.supclassid = sqlDataReader["supclassid"] != DBNull.Value ? Convert.ToInt32(sqlDataReader["supclassid"].ToString()) : 0; model.className = sqlDataReader["className"] != DBNull.Value ? sqlDataReader["className"].ToString() : string.Empty; model.supclassName = sqlDataReader["supclassName"] != DBNull.Value ? sqlDataReader["supclassName"].ToString() : string.Empty; model.productname = sqlDataReader["productname"] != DBNull.Value ? sqlDataReader["productname"].ToString() : string.Empty; model.productformat = sqlDataReader["productformat"] != DBNull.Value ? sqlDataReader["productformat"].ToString() : string.Empty; model.productformatunit = sqlDataReader["productformatunit"] != DBNull.Value ? sqlDataReader["productformatunit"].ToString() : string.Empty; model.origprice = sqlDataReader["origprice"] != DBNull.Value ? Convert.ToDecimal(sqlDataReader["origprice"].ToString()) : 0; model.sellprice = sqlDataReader["sellprice"] != DBNull.Value ? Convert.ToDecimal(sqlDataReader["sellprice"].ToString()) : 0; model.stock = sqlDataReader["stock"] != DBNull.Value ? Convert.ToInt32(sqlDataReader["stock"].ToString()) : 0; model.shelfstate = sqlDataReader["shelfstate"] != DBNull.Value ? Convert.ToInt32(sqlDataReader["shelfstate"].ToString()) : 0; model.hassellnum = sqlDataReader["hassellnum"] != DBNull.Value ? Convert.ToInt32(sqlDataReader["hassellnum"].ToString()) : 0; model.productdetails = sqlDataReader["productdetails"] != DBNull.Value ? sqlDataReader["productdetails"].ToString() : string.Empty; model.productimgurl = sqlDataReader["productimgurl"] != DBNull.Value ? sqlDataReader["productimgurl"].ToString() : string.Empty; model.priority = sqlDataReader["priority"] != DBNull.Value ? Convert.ToInt32(sqlDataReader["priority"].ToString()) : 0; 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; model.recommend = sqlDataReader["recommend"] != DBNull.Value ? Convert.ToInt32(sqlDataReader["recommend"].ToString()) : 0; listModel.Add(model); } } } return(listModel); }
/// <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); }