//Constructor
public MemberRegistry(View.Console a_view)
{
m_isInStartup = true;
m_view = a_view;
//try to load member list from storage
m_memberListDAL = new Model.MemberListDAL();
m_memberList = m_memberListDAL.LoadMemberList();
if (m_memberList == null)
{
m_memberList = new Model.MemberList();
}
//subscribe to the MemberList
m_memberList.AddSubscriber(this);
//try to load user list from storage
m_userListDAL = new Model.UserListDAL();
m_userList = m_userListDAL.LoadUserList();
if (m_userList == null)
{
m_userList = new Model.UserList();
}
//subscribe to the UserList
m_userList.AddSubscriber(this);
m_addingEditingMember = null;
m_loggedInUser = null;
m_memberFiltering = null;
m_isInStartup = false;
}
/// <summary>
/// 根据用户ID获取用户
/// </summary>
/// <param name="inUserID"></param>
/// <returns></returns>
public static Model.UserList GetUser(int inUserID)
{
string sql = @" SELECT * FROM Tx_UserList WHERE UserID = @inUserID ";
//StringBuilder sql = new StringBuilder();
//sql.Append(" SELECT * FROM Tx_UserList WHERE UserID = @inUserID ");
//sql.Append(" LEFT JOIN Tx_UserCommodityList b ON a.UserID = b.UserID ");
//sql.Append(" LEFT JOIN Tx_CommodityList c ON c.CommodityID = b.CommodityID ");
//sql.Append(" WHERE a.UserID = @inUserID ");
SqlParameter para = new SqlParameter("@inUserID", SqlDbType.Int, 32);
para.Value = inUserID;
DataTable dt = SqlHelper.ExecuteDataTable(CommandType.Text, sql.ToString(), para);
List <Model.UserList> list = GetUserList(dt);
Model.UserList model = list.Count > 0 ? list[0] : null;
//if (model != null)
//{
// model.Unit = dt.Rows[0]["Unit"].ToString();
//}
return(model);
}
/// <summary>
/// 私有方法，获取用户列表
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
private static List <Model.UserList> GetUserList(DataTable dt)
{
List <Model.UserList> list = new List <Model.UserList>();
foreach (DataRow item in dt.Rows)
{
Model.UserList model = new Model.UserList
{
UserID = Convert.ToInt32(item["UserID"]),
UserName = item["UserName"].ToString(),
CompanyName = item["CompanyName"].ToString(),
Password = item["Password"].ToString(),
Class = item["Class"].ToString(),
Sex = Convert.ToInt32(item["Sex"]),
Email = item["Email"].ToString(),
PhoneNumber = item["PhoneNumber"].ToString(),
WeChat = item["WeChat"].ToString(),
QQ = item["QQ"].ToString(),
Address = item["Address"].ToString(),
EnterpriseInfo = item["EnterpriseInfo"].ToString(),
IsAdmin = Convert.ToInt32(item["IsAdmin"])
};
list.Add(model);
}
return(list);
}
public static BaseHelper UserList(Model.UserList data)
{
var retVal = new BaseHelper();
var objJSON = new Output.Session();
var objJSONPage = new Output.User();
try
{
var entity = User.ValidateSession(data, objJSON);
retVal.SQLElapsed = entity.SQLElapsed;
retVal.SQLInfo($"sp : {entity.SQLDetail}, elapsed : {entity.SQLElapsed}");
if (objJSON.ResultCode == 1)
{
data.StampUser = objJSON.Content.Data[0].UserLogin;
data.UserID = objJSON.Content.Data[0].UserID;
var entityPage = User.UserList(data, objJSONPage);
retVal.SQLElapsed += entityPage.SQLElapsed;
retVal.SQLInfo($"sp : {entityPage.SQLDetail}, elapsed : {entityPage.SQLElapsed}");
retVal.IsError = (entityPage.ResultCode == 1) ? false : true;
}
else
{
objJSONPage.ResultCode = objJSON.ResultCode;
objJSONPage.ErrorMessage = objJSON.ErrorMessage;
}
}
catch (Exception ex)
{
retVal.Exception = ex;
if (ex is System.Data.SqlClient.SqlException sqlEx)
{
retVal.SQLInfo($"sp:{sqlEx.Procedure}, line:{sqlEx.LineNumber}, detail:{data.SqlDetail}");
retVal.SQLException = true;
objJSON.ResultCode = 69998;
objJSON.ErrorMessage = "SQL Exception";
}
else
{
objJSON.ResultCode = 69999;
objJSON.ErrorMessage = "Unknown Error";
}
}
finally
{
retVal.SerializeObject <Output.User>(objJSONPage);
}
return(retVal);
}
public static BasicEntity UserList(Model.UserList data, Output.User obj)
{
var retVal = new BasicEntity();
retVal.AddParameter("@user_login", data.Username);
retVal.AddParameter("@user_group_name", data.UserGroupName);
retVal.AddParameter("@user_id", data.UserID);
retVal.AddParameter("@page", data.Page);
retVal.AddParameter("@page_size", data.PageSize);
data.SqlDetail = retVal.SQLCommandBuilder("spUserList");
using (SqlDataReader reader = retVal.ExecReader())
{
while (reader.Read())
{
obj.ResultCode = (reader.IsDBNull(0)) ? 0 : reader.GetInt32(0);
obj.ErrorMessage = (reader.IsDBNull(1)) ? string.Empty : reader.GetString(1);
retVal.ResultCode = obj.ResultCode;
}
if (retVal.ResultCode == 1)
{
reader.NextResult();
while (reader.Read())
{
obj.Content.TotalRows = (reader.IsDBNull(0)) ? 0 : reader.GetInt32(0);
}
reader.NextResult();
while (reader.Read())
{
var item = new Output.UserData();
item.UserID = (reader.IsDBNull(0)) ? 0 : reader.GetInt32(0);
item.UserGroupName = (reader.IsDBNull(1)) ? string.Empty : reader.GetString(1);
item.UserLogin = (reader.IsDBNull(2)) ? string.Empty : reader.GetString(2);
item.UserActive = (reader.IsDBNull(3)) ? string.Empty : reader.GetString(3);
item.UserSuspend = (reader.IsDBNull(4)) ? string.Empty : reader.GetString(4);
obj.Content.Data.Add(item);
}
}
reader.Close();
}
retVal.Close();
return(retVal);
}
/// <summary>
/// 更新用户
/// </summary>
/// <param name="inModel"></param>
/// <returns></returns>
public static int UpdateUser(Model.UserList inModel)
{
StringBuilder sql = new StringBuilder();
sql.Append(" UPDATE Tx_UserList SET ");
sql.Append(" UserName = @inUserName, CompanyName = @inCompanyName, Class = @inClass, Sex = @inSex, ");
sql.Append(" Email = @inEmail, PhoneNumber = @inPhoneNumber, WeChat = @inWeChat, ");
sql.Append(" QQ = @inQQ, Address = @inAddress, EnterpriseInfo = @inEnterpriseInfo, ");
sql.Append(" IsAdmin = @inIsAdmin WHERE UserID = @inUserID ");
SqlParameter[] paras = GetParameters(inModel);
int result = SqlHelper.ExecuteNonQuery(CommandType.Text, sql.ToString(), paras);
return(result);
}
public static BasicEntity UserList(Model.UserList data, Output.UserList obj)
{
var retVal = new BasicEntity();
retVal.AddParameter("@page", data.Page);
retVal.AddParameter("@page_size", data.PageSize);
retVal.AddParameter("@level", data.Level);
data.SqlDetail = retVal.SQLCommandBuilder("spLoginUserList");
using (SqlDataReader reader = retVal.ExecReader())
{
while (reader.Read())
{
obj.ResultCode = (reader.IsDBNull(0)) ? 0 : reader.GetInt32(0);
obj.ErrorMessage = (reader.IsDBNull(1)) ? string.Empty : reader.GetString(1);
retVal.ResultCode = obj.ResultCode;
}
if (retVal.ResultCode == 1)
{
reader.NextResult();
while (reader.Read())
{
obj.Content.TotalRows = (reader.IsDBNull(0)) ? 0 : reader.GetInt32(0);
}
reader.NextResult();
while (reader.Read())
{
var item = new Output.UserListData();
item.UserLogin = (reader.IsDBNull(0)) ? string.Empty : reader.GetString(0);
item.Level = (reader.IsDBNull(1)) ? string.Empty : reader.GetString(1);
obj.Content.Data.Add(item);
}
}
reader.Close();
}
retVal.Close();
return(retVal);
}
/// <summary>
/// 创建用户
/// </summary>
/// <param name="inModel"></param>
/// <returns></returns>
public static int CreateUser(Model.UserList inModel)
{
StringBuilder sql = new StringBuilder();
sql.Append(" INSERT INTO Tx_UserList ( ");
sql.Append(" UserName, Password, CompanyName, Class, Sex, ");
sql.Append(" Email, PhoneNumber, WeChat, ");
sql.Append(" QQ, Address, EnterpriseInfo, IsAdmin ");
sql.Append(" ) VALUES ( ");
sql.Append(" @inUserName, @inPassword, @inCompanyName, @inClass, @inSex, ");
sql.Append(" @inEmail, @inPhoneNumber, @inWeChat, ");
sql.Append(" @inQQ, @inAddress, @inEnterpriseInfo, @inIsAdmin ) ");
SqlParameter[] paras = GetParameters(inModel);
int result = SqlHelper.ExecuteNonQuery(CommandType.Text, sql.ToString(), paras);
return(result);
}
public JsonResult ExistUser(string inEmail, string inPassword)
{
JsonResult json = new JsonResult();
try
{
if (string.IsNullOrEmpty(inEmail) || string.IsNullOrEmpty(inPassword))
{
json.Data = new { result = false, msg = "用户名或密码不能为空！" };
return(json);
}
Model.UserList model = Logic.UserList.ExistUser(inEmail, inPassword);
if (model != null)
{
ViewModels.LoginViewModel vModel = new ViewModels.LoginViewModel
{
UserID = model.UserID,
UserName = model.UserName,
Email = model.Email,
IsAdmin = Convert.ToBoolean(model.IsAdmin)
};
Logic.LogList.InsertLog(vModel.UserID, vModel.UserName, DateTime.Now, "成功登录");
HttpContext.Session["UserInfo"] = vModel;
json.Data = new { result = true, msg = "" };
}
else
{
json.Data = new { result = false, msg = "用户名或密码错误！" };
}
}
catch (Exception ex)
{
Logic.LogList.InsertLog(0, "system", DateTime.Now, ex.Message);
json.Data = new { result = false, msg = "登录失败" };
}
return(json);
}
/// <summary>
/// 更新用户
/// </summary>
/// <param name="inModel"></param>
/// <returns></returns>
public static int UpdateUser(Model.UserList inModel)
{
return(Data.UserList.UpdateUser(inModel));
}
/// <summary>
/// 私有方法，获取User参数
/// </summary>
/// <param name="inModel"></param>
/// <returns></returns>
private static SqlParameter[] GetParameters(Model.UserList inModel)
{
List <SqlParameter> list = new List <SqlParameter>();
if (inModel.UserID != 0)
{
SqlParameter userid = new SqlParameter("@inUserID", SqlDbType.Int, 32);
userid.Value = inModel.UserID;
list.Add(userid);
}
SqlParameter name = new SqlParameter("@inUserName", SqlDbType.NVarChar, 50);
name.Value = inModel.UserName ?? string.Empty;
list.Add(name);
SqlParameter password = new SqlParameter("@inPassword", SqlDbType.NVarChar, 50);
password.Value = inModel.Password ?? string.Empty;
list.Add(password);
SqlParameter companyName = new SqlParameter("@inCompanyName", SqlDbType.NVarChar, 150);
companyName.Value = inModel.CompanyName ?? string.Empty;
list.Add(companyName);
SqlParameter _class = new SqlParameter("@inClass", SqlDbType.NVarChar, 50);
_class.Value = inModel.Class ?? string.Empty;
list.Add(_class);
SqlParameter sex = new SqlParameter("@inSex", SqlDbType.Int, 32);
sex.Value = inModel.Sex;
list.Add(sex);
SqlParameter email = new SqlParameter("@inEmail", SqlDbType.NVarChar, 50);
email.Value = inModel.Email ?? string.Empty;
list.Add(email);
SqlParameter phonenumber = new SqlParameter("@inPhoneNumber", SqlDbType.NVarChar, 50);
phonenumber.Value = inModel.PhoneNumber ?? string.Empty;
list.Add(phonenumber);
SqlParameter wechat = new SqlParameter("@inWeChat", SqlDbType.NVarChar, 50);
wechat.Value = inModel.WeChat ?? string.Empty;
list.Add(wechat);
SqlParameter qq = new SqlParameter("@inQQ", SqlDbType.NVarChar, 50);
qq.Value = inModel.QQ ?? string.Empty;
list.Add(qq);
SqlParameter address = new SqlParameter("@inAddress", SqlDbType.NVarChar, 500);
address.Value = inModel.Address ?? string.Empty;
list.Add(address);
SqlParameter enterpriseinfo = new SqlParameter("@inEnterpriseInfo", SqlDbType.NVarChar, -1);
enterpriseinfo.Value = inModel.EnterpriseInfo ?? string.Empty;
list.Add(enterpriseinfo);
SqlParameter isadmin = new SqlParameter("@inIsAdmin", SqlDbType.Int, 32);
isadmin.Value = inModel.IsAdmin;
list.Add(isadmin);
return(list.ToArray());
}
public JsonResult RegisterUser(ViewModels.LoginViewModel inModel)
{
JsonResult json = new JsonResult();
try
{
if (inModel.Password != inModel.ConfirmPwd)
{
json.Data = new { result = false, msg = "密码不一致" }; return(json);
}
if (inModel.VerificationCode != VerificationCode)
{
json.Data = new { result = false, msg = "验证码不正确" }; return(json);
}
if (Logic.UserList.ExistUser(inModel.Email) > 0)
{
json.Data = new { result = false, msg = "邮箱已被注册" }; return(json);
}
Model.UserList model = new Model.UserList
{
UserName = inModel.UserName,
Email = inModel.Email,
Password = inModel.Password,
PhoneNumber = inModel.PhoneNumber
};
int result = Logic.UserList.CreateUser(model);
if (result == 1)
{
Model.UserList login = Logic.UserList.ExistUser(model.Email, model.Password);
ViewModels.LoginViewModel vModel = new ViewModels.LoginViewModel
{
UserID = login.UserID,
UserName = login.UserName,
Email = login.Email,
IsAdmin = Convert.ToBoolean(login.IsAdmin)
};
Logic.LogList.InsertLog(vModel.UserID, vModel.UserName, DateTime.Now, "注册并成功登录");
HttpContext.Session["UserInfo"] = vModel;
json.Data = new { result = true, msg = "" };
}
else
{
json.Data = new { result = false, msg = "注册失败" };
}
}
catch (Exception ex)
{
Logic.LogList.InsertLog(0, "system", DateTime.Now, ex.Message);
json.Data = new { result = false, msg = "注册失败" };
}
return(json);
}