//public int Register(RegisterModel model)
//{
// string sql = string.Format("insert into person( no , name , password)");
// //MySqlHelper.ExecuteSql();
// return - 1;
//}
protected User DataRowToUser(DataRow row)
{
User model = new User();
if ( row["userid"].ToString() != "")
{
model.userid = int.Parse( row["userid"].ToString());
}
model.username = row["username"].ToString();
model.realname = row["realname"].ToString();
model.password = row["password"].ToString();
model.createtime = DateTime.Parse( row["createtime"].ToString());
model.modifytime = DateTime.Parse( row["modifytime"].ToString());
//model.link = row["link"].ToString();
model.phone = row["phone"].ToString();
model.sex = row["sex"].ToString();
if (row["enable"].ToString() != "")
{
model.enable = int.Parse(row["enable"].ToString());
}
model.address = row["address"].ToString();
model.roletype = row["roletype"].ToString();
model.createman = row["createman"].ToString();
model.modifyman = row["modifyman"].ToString();
return model;
}
public JsonResult EditUser(User model)
{
ContractMvcWeb.Models.AccountContext dbContext = new Models.AccountContext();
JsonResult jsonResult = new JsonResult();
Result result = null;
if (string.IsNullOrEmpty(model.username))
{
result = new Result( (int)ResultCodeEnum.Error , "请输入用户名",null);
jsonResult.Data = result;
return jsonResult;
}
bool isExist = dbContext.ExistUserName(model.username, model.userid);
if (isExist)
{
result = new Result((int)ResultCodeEnum.Error, "用户名已经存在", null);
jsonResult.Data = result;
return jsonResult;
}
bool isSuccess = dbContext.EditUser(model);
result = new Result(isSuccess ? (int)ResultCodeEnum.Success : (int)ResultCodeEnum.Error, isSuccess ? "更新成功" : "更新失败", null);
jsonResult.Data = result;
return jsonResult;
}
/// <summary>
///
/// </summary>
/// <param name="username"></param>
/// <param name="password"></param>
/// <returns></returns>
public User Login(string username, string password)
{
string sql = "select * from t_user where username=@username and password=@password";
MySqlParameter[] parameters ={
new MySqlParameter("@username", MySqlDbType.VarChar,45),
new MySqlParameter("@password", MySqlDbType.VarChar,45)
};
parameters[0].Value = username;
parameters[1].Value = password;
DataSet result = MySqlHelper.Query(sql , parameters );
if (result == null || result.Tables.Count < 1 || result.Tables[0].Rows.Count < 1) return null;
User model = new User();
if (result.Tables[0].Rows[0]["userid"].ToString() != "")
{
model.userid = int.Parse(result.Tables[0].Rows[0]["userid"].ToString());
}
model.username = result.Tables[0].Rows[0]["username"].ToString();
model.password = result.Tables[0].Rows[0]["password"].ToString();
model.createtime=DateTime.Parse( result.Tables[0].Rows[0]["createtime"].ToString());
model.modifytime=DateTime.Parse( result.Tables[0].Rows[0]["modifytime"].ToString());
if (result.Tables[0].Rows[0]["enable"].ToString() != "")
{
model.enable = int.Parse(result.Tables[0].Rows[0]["enable"].ToString());
}
//model.link = result.Tables[0].Rows[0]["link"].ToString();
model.sex = result.Tables[0].Rows[0]["sex"].ToString();
model.address = result.Tables[0].Rows[0]["address"].ToString();
model.roletype = result.Tables[0].Rows[0]["roletype"].ToString();
if (result.Tables[0].Columns.Contains("realname"))
{
model.realname = result.Tables[0].Rows[0]["realname"].ToString();
}
return model;
}
public bool EditUser(User model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update t_user set ");
strSql.Append("username=@username,");
strSql.Append("realname=@realname,");
strSql.Append("phone=@phone,");
strSql.Append("enable=@enable,");
//strSql.Append("createtime=@createtime,");
//strSql.Append("modifytime=@modifytime,");
strSql.Append("sex=@sex,");
strSql.Append("address=@address,");
strSql.Append("roletype=@roletype,");
strSql.Append("modifyman=@modifyman,");
strSql.Append("modifytime=@modifytime");
strSql.Append(" where userid=@userid");
MySqlParameter[] parameters = {
new MySqlParameter("@userid", MySqlDbType.Int32,11),
new MySqlParameter("@username", MySqlDbType.VarChar,45),
new MySqlParameter("@realname", MySqlDbType.VarChar,45),
new MySqlParameter("@phone", MySqlDbType.VarChar,45),
new MySqlParameter("@enable", MySqlDbType.Int32,11),
//new MySqlParameter("@createtime", MySqlDbType.Timestamp),
//new MySqlParameter("@modifytime", MySqlDbType.Timestamp),
new MySqlParameter("@sex", MySqlDbType.VarChar,10),
new MySqlParameter("@address", MySqlDbType.VarChar,256),
new MySqlParameter("@roletype",MySqlDbType.VarChar,256),
new MySqlParameter("@modifyman",MySqlDbType.VarChar,256),
new MySqlParameter("@modifytime",MySqlDbType.Timestamp)
};
parameters[0].Value = model.userid;
parameters[1].Value = model.username;
parameters[2].Value = model.realname;
parameters[3].Value = model.phone;
parameters[4].Value = model.enable;
//parameters[4].Value = model.createtime;
//parameters[5].Value = model.modifytime;
parameters[5].Value = model.sex;
parameters[6].Value = model.address;
parameters[7].Value = model.roletype;
parameters[8].Value = model.modifyman;
parameters[9].Value = model.modifytime;
int rows = MySqlHelper.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return true;
}
else
{
return false;
}
}
public int AddUser(User model)
{
string sql = string.Format(" insert into t_user( username , password , enable , phone ,sex,address, roletype, createman,modifyman,realname) values(@username,@password,@enable,@phone,@sex,@address,@roletype,@createman,@modifyman,@realname)");
MySql.Data.MySqlClient.MySqlParameter[] paras = new MySql.Data.MySqlClient.MySqlParameter[10];
paras[0] = new MySql.Data.MySqlClient.MySqlParameter();
paras[0].ParameterName = "@username";
paras[0].MySqlDbType = MySql.Data.MySqlClient.MySqlDbType.VarChar;
paras[0].Value = model.username;
paras[1] = new MySql.Data.MySqlClient.MySqlParameter();
paras[1].ParameterName ="@password";
paras[1].MySqlDbType = MySql.Data.MySqlClient.MySqlDbType.VarChar;
paras[1].Value = model.password ;
paras[2] = new MySql.Data.MySqlClient.MySqlParameter();
paras[2].ParameterName ="@enable";
paras[2].MySqlDbType = MySql.Data.MySqlClient.MySqlDbType.Int16 ;
paras[2].Value= model.enable ;
paras[3] = new MySql.Data.MySqlClient.MySqlParameter();
paras[3].ParameterName = "@phone";
paras[3].MySqlDbType = MySql.Data.MySqlClient.MySqlDbType.VarChar;
paras[3].Value = model.phone;
paras[4] = new MySql.Data.MySqlClient.MySqlParameter();
paras[4].ParameterName = "@sex";
paras[4].MySqlDbType = MySql.Data.MySqlClient.MySqlDbType.VarChar;
paras[4].Value = model.sex;
paras[5] = new MySql.Data.MySqlClient.MySqlParameter();
paras[5].ParameterName = "@address";
paras[5].MySqlDbType = MySql.Data.MySqlClient.MySqlDbType.VarChar;
paras[5].Value = model.address;
paras[6] = new MySql.Data.MySqlClient.MySqlParameter();
paras[6].ParameterName = "@roletype";
paras[6].MySqlDbType = MySql.Data.MySqlClient.MySqlDbType.VarChar;
paras[6].Value = model.roletype;
paras[7] = new MySql.Data.MySqlClient.MySqlParameter();
paras[7].ParameterName = "@createman";
paras[7].MySqlDbType = MySql.Data.MySqlClient.MySqlDbType.VarChar;
paras[7].Value = model.createman;
paras[8] = new MySql.Data.MySqlClient.MySqlParameter();
paras[8].ParameterName = "@modifyman";
paras[8].MySqlDbType = MySql.Data.MySqlClient.MySqlDbType.VarChar;
paras[8].Value = model.modifyman;
paras[9] = new MySql.Data.MySqlClient.MySqlParameter();
paras[9].ParameterName = "@realname";
paras[9].MySqlDbType = MySql.Data.MySqlClient.MySqlDbType.VarChar;
paras[9].Value = model.realname;
int result = MySqlHelper.ExecuteSql(sql, paras);
return result;
}
public Page<User> QueryByPage( User query, int pageidx, int pagesize = 20)
{
Page<User> page = new Page<User>();
page.PageIdx = pageidx;
page.PageSize = pagesize;
string where = GetWhere(query);
string limit = string.Format(" limit {0} , {1}", pageidx < 0 ? 0 : pageidx * pagesize, pagesize);
string orderby = "";//"order by operatetime desc , id desc";
string sql = string.Format("select count(1) from t_user where {0} ", where);
int totalrecord = 0;
object obj = MySqlHelper.GetSingle(sql);
if (obj == null) totalrecord = 0;
int.TryParse(obj.ToString(), out totalrecord);
int totalPages = 0;
totalPages = totalrecord / pagesize;
totalPages += totalrecord % pagesize == 0 ? 0 : 1;
page.TotalPages = totalPages;
page.TotalRecords = totalrecord;
sql = string.Format(" select * from t_user where {0} {1} {2}", where, orderby, limit);
DataSet ds = MySqlHelper.Query(sql);
if (ds == null || ds.Tables.Count < 1 || ds.Tables[0].Rows.Count < 1) return page;
int count = ds.Tables[0].Rows.Count;
List<User> list = new List<User>();
for (int i = 0; i < count; i++)
{
DataRow row = ds.Tables[0].Rows[i];
User model = DataRowToUser(row);
list.Add(model);
}
page.Data = list;
return page;
}
protected string GetWhere(User query)
{
query.username = FilterSpecial(query.username);
query.realname= FilterSpecial(query.realname);
query.phone = FilterSpecial(query.phone);
string where = "";
if (string.IsNullOrEmpty(query.username) == false)
{
if (string.IsNullOrEmpty(where) == false) where += " or ";
where += " username like '%" + query.username + "%'";
}
if (string.IsNullOrEmpty(query.realname) == false)
{
if (string.IsNullOrEmpty(where) == false) where += " or ";
where += string.Format(" realname like '%{0}%'", query.realname);
}
if (string.IsNullOrEmpty(query.phone) == false)
{
if (string.IsNullOrEmpty(where) == false) where += " or ";
where += string.Format(" phone like '%{0}%'", query.phone);
}
if (string.IsNullOrEmpty(where) ) where =" 1=1 ";
return where;
}
public ActionResult EditUser(User model)
{
SetDropDownlist((int)EnableEnum.ENABLE);
SetUserTypeDropDownlist(((int)UserTypeEnum.QUERY).ToString());
ContractMvcWeb.Models.AccountContext dbContext = new Models.AccountContext();
if (model == null) return View();
if (string.IsNullOrEmpty(model.username))
{
ModelState.AddModelError("", "请输入用户名");
return View();
}
bool isSuccess = dbContext.EditUser(model);
return new RedirectResult("~/user/userlist");
}
