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