/// <summary>
///
/// </summary>
/// <param name="sellId"></param>
/// <param name="type"></param>
/// <param name="index"></param>
/// <param name="size"></param>
/// <param name="order">排序sql</param>
/// <returns></returns>
public static PagResults <Comment> GetPagings(int sellerId, CommentType type, int typeId, int index, int size, string order = "order by CreateTime desc")
{
var results = new PagResults <Comment>();
results.Results = new List <Comment>();
int skipnum = index * size;
int totalnum = 0;
string commandText = "";
if (type == CommentType.All)
{
commandText = @"select * from comment where sellerId = ?sellerId " + order + " LIMIT ?index,?size";
}
else
{
commandText = @"select * from comment where sellerId = ?sellerId and type = ?type and typeId = ?typeId " + order + " LIMIT ?index,?size";
}
List <MySqlParameter> parameters = new List <MySqlParameter>();
parameters.Add(new MySqlParameter("?sellerId", sellerId));
if (type != CommentType.All)
{
parameters.Add(new MySqlParameter("?type", (int)type));
parameters.Add(new MySqlParameter("?typeId", typeId));
}
parameters.Add(new MySqlParameter("?index", index));
parameters.Add(new MySqlParameter("?size", size));
try
{
using (var conn = Utility.ObtainConn(Utility._gameDbConn))
{
MySqlDataReader reader = MySqlHelper.ExecuteReader(conn, CommandType.Text, commandText, parameters.ToArray());
while (reader.Read())
{
Comment c = new Comment();
c.Id = (int)reader["Id"];
c.SellerId = (int)reader["SellerId"];
c.Type = (CommentType)reader["Type"];
c.TypeId = (int)reader["TypeId"];
c.Content = reader["Content"].ToString();
c.CreateTime = (DateTime)reader["CreateTime"];
c.UserId = (int)reader["UserId"];
c.Img = reader["Img"].ToString();
c.Title = reader["Title"].ToString();
c.Feedback = reader["Feedback"].ToString();
results.Results.Add(c);
}
//一个函数有两次连接数据库 先把连接断开 然后重连
conn.Close();
conn.Dispose();
conn.Open();
if (type == CommentType.All)
{
commandText = @"select count(*) from comment where sellerId = ?sellerId ";
}
else
{
commandText = "select count(*) from comment where sellerId = ?sellerId and type = ?type and typeId = ?typeId";
}
parameters.Clear();
parameters.Add(new MySqlParameter("?sellerId", sellerId));
parameters.Add(new MySqlParameter("?type", (int)type));
parameters.Add(new MySqlParameter("?typeId", typeId));
reader = MySqlHelper.ExecuteReader(conn, CommandType.Text, commandText, parameters.ToArray());
while (reader.Read())
{
results.TotalCount = reader.GetInt32(0);
}
}
}
catch (System.Exception ex)
{
throw;
}
return(results);
}
/// <summary>
/// 获取用户评论
/// </summary>
/// <param name="uid"></param>
/// <param name="sellerId"></param>
/// <param name="type"></param>
/// <param name="start"></param>
/// <param name="limit"></param>
/// <returns></returns>
public static PagResults <Comment> GetList(int uid, int sellerId, CommentType type, int start, int limit, int isgetcount = 0)
{
var results = new PagResults <Comment>();
results.Results = new List <Comment>();
var wheresql = (type == CommentType.All ? string.Empty : "and Type = ?Type ");
var ordersql = " order by CreateTime desc ";
var limitsql = limit != 0 ? " LIMIT ?Start,?Limit" : string.Empty;
string commandText = @"select * from comment where UserId = ?UserId and SellerId = ?SellerId " + wheresql + ordersql + limitsql;
List <MySqlParameter> parameters = new List <MySqlParameter>();
parameters.Add(new MySqlParameter("?UserId", uid));
parameters.Add(new MySqlParameter("?SellerId", sellerId));
parameters.Add(new MySqlParameter("?Type", (int)type));
parameters.Add(new MySqlParameter("?Start", start));
parameters.Add(new MySqlParameter("?Limit", limit));
try
{
using (var conn = Utility.ObtainConn(Utility._gameDbConn))
{
MySqlDataReader reader = MySqlHelper.ExecuteReader(conn, CommandType.Text, commandText, parameters.ToArray());
while (reader.Read())
{
Comment c = new Comment();
c.Id = (int)reader["Id"];
c.SellerId = (int)reader["SellerId"];
c.Type = (CommentType)reader["Type"];
c.TypeId = (int)reader["TypeId"];
c.Content = reader["Content"].ToString();
c.Img = reader["Img"].ToString();
c.Title = reader["Title"].ToString();
c.CreateTime = (DateTime)reader["CreateTime"];
c.UserId = (int)reader["UserId"];
results.Results.Add(c);
}
if (isgetcount > 0)
{
//一个函数有两次连接数据库 先把连接断开 然后重连
conn.Close();
conn.Dispose();
conn.Open();
commandText = "select count(*) from comment where UserId = ?UserId and SellerId = ?SellerId " + wheresql;
parameters.Clear();
parameters.Add(new MySqlParameter("?UserId", uid));
parameters.Add(new MySqlParameter("?SellerId", sellerId));
parameters.Add(new MySqlParameter("?Type", (int)type));
reader = MySqlHelper.ExecuteReader(conn, CommandType.Text, commandText, parameters.ToArray());
while (reader.Read())
{
results.TotalCount = reader.GetInt32(0);
}
}
}
}
catch (System.Exception ex)
{
throw;
}
return(results);
}
/// <summary>
/// 插入或更新用户
/// </summary>
/// <param name="account"></param>
/// <returns></returns>
public static int SaveAccount(Account account)
{
var cmdText = String.Empty;
List <MySqlParameter> parameters = new List <MySqlParameter>();
if (account.Id > 0)
{
cmdText = @"UPDATE account SET
UserName = ?UserName,
NickName = ?NickName,
Pwd = ?Pwd,
SellerId = ?SellerId,
RoleType = ?RoleType,
Avatar = ?Avatar,
Sex = ?Sex,
LinkMan = ?LinkMan,
Phone = ?Phone,
Address = ?Address,
Money = ?Money,
Integral = ?Integral,
CreateTime = ?CreateTime,
RegisterTime = ?RegisterTime ,
Status = ?Status ,
Discount = ?Discount ,
TotalRecharge = ?TotalRecharge ,
TotalConsume = ?TotalConsume ,
TotalLoginCount = ?TotalLoginCount ,
TotalOrdersCount = ?TotalOrdersCount,
LastLoginTime = ?LastLoginTime,
Remark = ?Remark
WHERE
Id = ?Id";
parameters.Add(new MySqlParameter("?Id", account.Id));
parameters.Add(new MySqlParameter("?UserName", account.UserName));
parameters.Add(new MySqlParameter("?NickName", account.NickName));
parameters.Add(new MySqlParameter("?Pwd", account.Pwd));
parameters.Add(new MySqlParameter("?RoleType", account.RoleType));
parameters.Add(new MySqlParameter("?Avatar", account.Avatar));
parameters.Add(new MySqlParameter("?Sex", account.Sex));
parameters.Add(new MySqlParameter("?CreateTime", account.CreateTime));
parameters.Add(new MySqlParameter("?Phone", account.Phone));
parameters.Add(new MySqlParameter("?Address", account.Address));
parameters.Add(new MySqlParameter("?Money", account.Money));
parameters.Add(new MySqlParameter("?Integral", account.Integral));
parameters.Add(new MySqlParameter("?LinkMan", account.LinkMan));
parameters.Add(new MySqlParameter("?SellerId", account.SellerId));
parameters.Add(new MySqlParameter("?RegisterTime", account.RegisterTime));
parameters.Add(new MySqlParameter("?Status", account.Status));
parameters.Add(new MySqlParameter("?Discount", account.Discount));
parameters.Add(new MySqlParameter("?TotalRecharge", account.TotalRecharge));
parameters.Add(new MySqlParameter("?TotalConsume", account.TotalConsume));
parameters.Add(new MySqlParameter("?TotalLoginCount", account.TotalLoginCount));
parameters.Add(new MySqlParameter("?TotalOrdersCount", account.TotalOrdersCount));
parameters.Add(new MySqlParameter("?LastLoginTime", account.LastLoginTime));
parameters.Add(new MySqlParameter("?Remark", account.Remark));
}
else
{
cmdText = @"insert into account
(
UserName ,
NickName ,
Pwd ,
RoleType ,
Avatar ,
Sex ,
Phone ,
Address ,
SellerId ,
Money ,
Integral ,
LinkMan ,
RegisterTime ,
Status ,
Discount ,
TotalRecharge ,
TotalConsume ,
TotalLoginCount ,
TotalOrdersCount ,
LastLoginTime ,
Remark ,
CreateTime
)
values
(
?UserName ,
?NickName ,
?Pwd ,
?RoleType ,
?Avatar ,
?Sex ,
?Phone ,
?Address ,
?SellerId ,
?Money ,
?Integral ,
?LinkMan ,
?RegisterTime ,
?Status ,
?Discount ,
?TotalRecharge ,
?TotalConsume ,
?TotalLoginCount ,
?TotalOrdersCount ,
?LastLoginTime ,
?Remark ,
?CreateTime
)";
parameters.Add(new MySqlParameter("?UserName", account.UserName));
parameters.Add(new MySqlParameter("?NickName", account.NickName));
parameters.Add(new MySqlParameter("?Pwd", account.Pwd));
parameters.Add(new MySqlParameter("?RoleType", account.RoleType));
parameters.Add(new MySqlParameter("?Avatar", account.Avatar));
parameters.Add(new MySqlParameter("?Sex", account.Sex));
parameters.Add(new MySqlParameter("?CreateTime", account.CreateTime));
parameters.Add(new MySqlParameter("?Phone", account.Phone));
parameters.Add(new MySqlParameter("?Address", account.Address));
parameters.Add(new MySqlParameter("?Money", account.Money));
parameters.Add(new MySqlParameter("?SellerId", account.SellerId));
parameters.Add(new MySqlParameter("?Integral", account.Integral));
parameters.Add(new MySqlParameter("?LinkMan", account.LinkMan));
parameters.Add(new MySqlParameter("?RegisterTime", account.RegisterTime));
parameters.Add(new MySqlParameter("?Status", account.Status));
parameters.Add(new MySqlParameter("?Discount", account.Discount));
parameters.Add(new MySqlParameter("?TotalRecharge", account.TotalRecharge));
parameters.Add(new MySqlParameter("?TotalConsume", account.TotalConsume));
parameters.Add(new MySqlParameter("?TotalLoginCount", account.TotalLoginCount));
parameters.Add(new MySqlParameter("?TotalOrdersCount", account.TotalOrdersCount));
parameters.Add(new MySqlParameter("?LastLoginTime", account.LastLoginTime));
parameters.Add(new MySqlParameter("?Remark", account.Remark));
}
try
{
using (var conn = Utility.ObtainConn(Utility._gameDbConn))
{
var num = MySqlHelper.ExecuteNonQuery(conn, CommandType.Text, cmdText, parameters.ToArray());
if (account.Id == 0)
{
cmdText = @"select LAST_INSERT_ID();";
var reader = MySqlHelper.ExecuteReader(conn, CommandType.Text, cmdText);
if (reader.HasRows)
{
if (reader.Read())
{
num = reader.GetInt32(0);
}
}
}
return(num);
}
}
catch (Exception ex)
{
return(0);
}
}
public static PagResults <Account> GetUserList(string wheresql = "", string ordersql = ""
, int start = 0, int limit = 0, int isgetcount = 0)
{
var result = new PagResults <Account>();
result.Results = new List <Account>();
if (ordersql == "")
{
ordersql = " order by CreateTime desc";
}
string limitsql = limit != 0 ? " LIMIT ?start,?limit" : String.Empty;
var cmdText = @"select * from account " + wheresql + ordersql + limitsql;
List <MySqlParameter> parameters = new List <MySqlParameter>();
if (!String.IsNullOrEmpty(limitsql))
{
parameters.Add(new MySqlParameter("?start", start));
parameters.Add(new MySqlParameter("?limit", limit));
}
try
{
using (var conn = Utility.ObtainConn(Utility._gameDbConn))
{
MySqlDataReader reader = MySqlHelper.ExecuteReader(conn, CommandType.Text, cmdText,
parameters.ToArray());
while (reader.Read())
{
Account user = new Account();
user.Id = reader.GetInt32(0);
user.UserName = reader["UserName"].ToString();
user.NickName = reader["NickName"].ToString();
user.Pwd = reader["Pwd"].ToString();
user.RoleType = (RoleType)reader["RoleType"];
user.Avatar = reader["Avatar"].ToString();
user.Sex = (SexType)reader["Sex"];
user.Phone = reader["Phone"].ToString();
user.Address = reader["Address"].ToString();
user.Money = (float)reader["Money"];
user.SellerId = (int)reader["SellerId"];
user.CreateTime = (DateTime)reader["CreateTime"];
user.Integral = (int)reader["Integral"];
user.LinkMan = reader["LinkMan"].ToString();
user.Discount = (float)reader["Discount"];
user.TotalRecharge = (float)reader["TotalRecharge"];
user.TotalConsume = (float)reader["TotalConsume"];
user.Status = (int)reader["Status"];
user.TotalLoginCount = (int)reader["TotalLoginCount"];
user.TotalOrdersCount = (int)reader["TotalOrdersCount"];
user.RegisterTime = (DateTime)reader["RegisterTime"];
user.LastLoginTime = (DateTime)reader["LastLoginTime"];
user.Remark = reader["Remark"].ToString();
result.Results.Add(user);
}
if (isgetcount == 1)
{
//一个函数有两次连接数据库 先把连接断开 然后重连
conn.Close();
conn.Dispose();
conn.Open();
cmdText = @"select count(*) from account " + wheresql;
reader = MySqlHelper.ExecuteReader(conn, CommandType.Text, cmdText);
if (reader.HasRows)
{
if (reader.Read())
{
result.TotalCount = reader.GetInt32(0);
}
}
}
}
}
catch (Exception ex)
{
throw;
}
return(result);
}
public static IList <Account> GetUserList(IList <int> uids)
{
IList <Account> list = new List <Account>();
if (uids.Count < 1)
{
return(list);
}
string ids_string = "(";
foreach (var uid in uids)
{
ids_string += (uid + ",");
}
ids_string = ids_string.TrimEnd(',') + ")";
string cmdText = String.Format(@"select * from account where id in {0}", ids_string);
try
{
using (var conn = Utility.ObtainConn(Utility._gameDbConn))
{
MySqlDataReader reader = MySqlHelper.ExecuteReader(conn, CommandType.Text, cmdText);
while (reader.Read())
{
Account user = new Account();
user.Id = reader.GetInt32(0);
user.UserName = reader["UserName"].ToString();
user.NickName = reader["NickName"].ToString();
user.Pwd = reader["Pwd"].ToString();
user.RoleType = (RoleType)reader["RoleType"];
user.Avatar = reader["Avatar"].ToString();
user.Sex = (SexType)reader["Sex"];
user.Phone = reader["Phone"].ToString();
user.Address = reader["Address"].ToString();
user.Money = (float)reader["Money"];
user.SellerId = (int)reader["SellerId"];
user.CreateTime = (DateTime)reader["CreateTime"];
user.Integral = (int)reader["Integral"];
user.LinkMan = reader["LinkMan"].ToString();
user.Discount = (float)reader["Discount"];
user.TotalRecharge = (float)reader["TotalRecharge"];
user.TotalConsume = (float)reader["TotalConsume"];
user.Status = (int)reader["Status"];
user.TotalLoginCount = (int)reader["TotalLoginCount"];
user.TotalOrdersCount = (int)reader["TotalOrdersCount"];
user.RegisterTime = (DateTime)reader["RegisterTime"];
user.LastLoginTime = (DateTime)reader["LastLoginTime"];
user.Remark = reader["Remark"].ToString();
list.Add(user);
}
}
}
catch (Exception ex)
{
throw;
}
return(list);
}
public static PagResults <Account> GetPagAdmins(int index, int size)
{
var items = new PagResults <Account>();
string cmdText = @"select * from account where (roletype=1 or roletype=2) and Status != -1 limit ?index,?size";
List <MySqlParameter> parameters = new List <MySqlParameter>();
parameters.Add(new MySqlParameter("?index", index));
parameters.Add(new MySqlParameter("?size", size));
try
{
using (var conn = Utility.ObtainConn(Utility._gameDbConn))
{
MySqlDataReader reader = MySqlHelper.ExecuteReader(conn, CommandType.Text, cmdText, parameters.ToArray());
while (reader.Read())
{
Account user = new Account();
user.Id = reader.GetInt32(0);
user.UserName = reader["UserName"].ToString();
user.NickName = reader["NickName"].ToString();
user.Pwd = reader["Pwd"].ToString();
user.RoleType = (RoleType)reader["RoleType"];
user.Avatar = reader["Avatar"].ToString();
user.Sex = (SexType)reader["Sex"];
user.Phone = reader["Phone"].ToString();
user.Address = reader["Address"].ToString();
user.Money = (float)reader["Money"];
user.SellerId = (int)reader["SellerId"];
user.CreateTime = (DateTime)reader["CreateTime"];
user.Integral = (int)reader["Integral"];
user.LinkMan = reader["LinkMan"].ToString();
user.Discount = (float)reader["Discount"];
user.TotalRecharge = (float)reader["TotalRecharge"];
user.TotalConsume = (float)reader["TotalConsume"];
user.Status = (int)reader["Status"];
user.TotalLoginCount = (int)reader["TotalLoginCount"];
user.TotalOrdersCount = (int)reader["TotalOrdersCount"];
user.RegisterTime = (DateTime)reader["RegisterTime"];
user.LastLoginTime = (DateTime)reader["LastLoginTime"];
user.Remark = reader["Remark"].ToString();
items.Results.Add(user);
}
//一个函数有两次连接数据库 先把连接断开 然后重连
conn.Close();
conn.Dispose();
conn.Open();
cmdText = @"select count(*) from account where (roletype=1 or roletype=2) and Status != -1 ";
reader = MySqlHelper.ExecuteReader(conn, CommandType.Text, cmdText);
if (reader.HasRows)
{
if (reader.Read())
{
items.TotalCount = reader.GetInt32(0);
}
}
}
}
catch (Exception ex)
{
throw;
}
return(items);
}
