public string Set_user_purview(string empid, string purviewStr)
{
string[] purviewList = purviewStr.Split(",");
string purviewListstr = String.Empty;
foreach (var item in purviewList)
{
if (String.IsNullOrEmpty(purviewListstr))
{
purviewListstr += "('" + empid + "','" + item + "')";
}
else
{
purviewListstr += ",('" + empid + "','" + item + "')";
}
}
SqlCommand cmd = PMCommand.ModCmd();
cmd.CommandText = "insert into wapEmpUserMap(empID,userName) values " + purviewListstr;
int count = cmd.ExecuteNonQuery();
cmd.Connection.Dispose();
if (count >= 1)
{
return("用户添加成功");
}
else
{
return("用户添加失败");
}
}
public void Add_user(JObject data)
{
//增加用户
string username = data["username"].ToString().Trim();
string pwd = data["password"].ToString().Trim();
string name = data["name"].ToString().Trim();
string phone = data["phone"].ToString().Trim();
string email = data["email"].ToString().Trim();
MD5 md5 = MD5.Create();
//PMStaticModels.UserModels.PMUser.UserSysID
pwd += username;
byte[] s = md5.ComputeHash(Encoding.UTF8.GetBytes(pwd));
string password = "";
for (int i = 0; i < s.Length; i++)
{
// 将得到的字符串使用十六进制类型格式。格式后的字符是小写的字母，如果使用大写（X）则格式后的字符是大写字符
password += s[i].ToString("X");
}
SqlCommand cmd = PMCommand.ModCmd();
cmd.CommandText = "insert into wapEmpList(empID,empName,password,phoneNum,email,sysID) values('" + username + "','" + name + "','" + password + "','" + phone + "','" + email + "','" + PMUser.UserSysID + "')";
cmd.ExecuteScalar();
cmd.Connection.Dispose();
}
//查看用户
public ActionResult <DataTable> Get_All_Users(int count)
{
DataTable table = new DataTable();
//string[] Authority = null;
if (count == 1)
{
SqlCommand cmd = PMCommand.ModCmd();
cmd.CommandText = "select top 10 empID,empName,phoneNum,email from wapEmpList";
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(table);
da.Dispose();
cmd.Connection.Dispose();
}
else
{
SqlCommand cmd = PMCommand.ModCmd();
cmd.CommandText = "declare @m int = " + count + "; declare @n int = 8; select top(@n) empID,empName,phoneNum,email from wapEmpList where empID not in(select top(@m - 1) empID from wapEmpList)";
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(table);
da.Dispose();
cmd.Connection.Dispose();
}
//for (int i = 0; i < table.Rows.Count; i++)
//{
// Authority.Append(table.Rows[i]["empID"].ToString());
//}
return(table);
}
public int Get_user(string username)
{
SqlCommand cmd = PMCommand.ModCmd();
cmd.CommandText = "select count(*) from wapEmpList where empID = '" + username + "'";
int counts = (int)cmd.ExecuteScalar();
cmd.Connection.Dispose();
return(counts);
}
public ActionResult <DataTable> UserMessage()
{
DataTable table = new DataTable();
SqlCommand cmd = PMCommand.ModCmd();
cmd.CommandText = "select empName,phoneNum,sysID,email from wapEmpList where empID='" + PMUser.EmpID + "'";
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(table);
da.Dispose();
cmd.Connection.Close();
return(table);
}
public string GetempName(string empid)
{
string empname = string.Empty;
SqlCommand cmd = PMCommand.ModCmd();
cmd.CommandText = "select empName from wapEmpList where empID = '" + empid + "'";
SqlDataReader rd = cmd.ExecuteReader();
if (rd.Read())
{
empname = rd["empName"].ToString();
}
rd.Close();
cmd.Connection.Close();
return(empname);
}
public string Set_wapUser_purview(JObject data)
{
//设置用户的权限
string username = data["username"].ToString();
string wapUserStr = "";
string wapEmpUserMapStr = "";
if (!String.IsNullOrEmpty(data["ADMIN"].ToString()))
{
//用户有这个权限走里面的方法
wapUserStr += wapUserStr == ""?"('admin_" + username + "','ADMIN','" + PMUser.UserSysID + "')" : ",('admin_" + username + "','ADMIN','" + PMUser.UserSysID + "')";
wapEmpUserMapStr += wapEmpUserMapStr == "" ? "admin_" + username : "******" + username;
}
if (!String.IsNullOrEmpty(data["BOARD"].ToString()))
{
wapUserStr += wapUserStr == "" ? "('board_" + username + "','BOARD','" + PMUser.UserSysID + "')" : ",('board_" + username + "','BOARD','" + PMUser.UserSysID + "')";
wapEmpUserMapStr += wapEmpUserMapStr == "" ? "board_" + username : "******" + username;
}
if (!String.IsNullOrEmpty(data["REP"].ToString()))
{
wapUserStr += wapUserStr == "" ? "('rep_" + username + "','REP','" + PMUser.UserSysID + "')" : ",('rep_" + username + "','REP','" + PMUser.UserSysID + "')";
wapEmpUserMapStr += wapEmpUserMapStr == "" ? "rep_" + username : "******" + username;
}
if (!String.IsNullOrEmpty(data["VIEW"].ToString()))
{
wapUserStr += wapUserStr == "" ? "('view_" + username + "','VIEW','" + PMUser.UserSysID + "')" : ",('view_" + username + "','VIEW','" + PMUser.UserSysID + "')";
wapEmpUserMapStr += wapEmpUserMapStr == "" ? "view_" + username : "******" + username;
}
if (!String.IsNullOrEmpty(data["CFM"].ToString()))
{
wapUserStr += wapUserStr == "" ? "('cfm_" + username + "','CFM','" + PMUser.UserSysID + "')" : ",('cfm_" + username + "','CFM','" + PMUser.UserSysID + "')";
wapEmpUserMapStr += wapEmpUserMapStr == "" ? "cfm_" + username : "******" + username;
}
SqlCommand cmd = PMCommand.ModCmd();
cmd.CommandText = "insert into wapUser(userName,shopUserGroupID,sysID) values " + wapUserStr;
cmd.ExecuteNonQuery();
cmd.Connection.Dispose();
return(this.Set_user_purview(username, wapEmpUserMapStr));
}
public List <string> GetuserGroup(string empID)
{
List <string> tmp = new List <string>();
SqlCommand cmd = PMCommand.ModCmd();
cmd.CommandText = "select userName from wapEmpUserMap where empID = '" + empID + "'";
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable DtUsermap = new DataTable();
da.Fill(DtUsermap);
da.Dispose();
cmd.CommandText = "select * from wapUser";
da = new SqlDataAdapter(cmd);
DataTable DtWapuser = new DataTable();
da.Fill(DtWapuser);
da.Dispose();
cmd.Connection.Close();
if (DtUsermap.Rows.Count > 0) //一定会有数据，防呆
{
foreach (DataRow item in DtUsermap.Rows)
{
string username = item["userName"].ToString();
DataRow[] dr = DtWapuser.Select("userName = '******'");
if (dr.Count() > 0)
{
string addstring = dr[0][1].ToString();
if (tmp.Contains(addstring) == false)
{
tmp.Add(addstring);
}
}
}
}
return(tmp);
}
public DataTable GetAttrTable(DataTable productID)
{
DataTable table = new DataTable();
JObject SQLFileds = PMAppSettings.TableFileds.SelectToken("SQLAttrFiled").ToObject <JObject>();
string SQLFiledStr = "itemName";
string productStr = "";
for (int i = 0; i < productID.Rows.Count; i++)
{
if (i < productID.Rows.Count - 1)
{
productStr += "'" + productID.Rows[i][0].ToString() + "',";
}
else
{
productStr += "'" + productID.Rows[i][0].ToString() + "'";
}
}
foreach (var item in SQLFileds)
{
SQLFiledStr += "," + item.Key;
}
SqlCommand cmd = PMCommand.ModCmd();
cmd.CommandText = "Select " + SQLFiledStr + " from objProduct where sysID = '" + PMStaticModels.UserModels.PMUser.UserSysID + "' and itemName in (" + productStr + ")";
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(table);
da.Dispose();
cmd.Connection.Dispose();
foreach (var item in SQLFileds)
{
table.Columns[item.Key].ColumnName = item.Value.Value <string>();
}
return(table);
}
public LoginMessage LoginMessage()
{
List <string> loginInfo = new List <string>();
string empName = GetempName(PMUser.EmpID);
int errortimes = 0;
DateTime errortime = new DateTime();
SqlCommand cmd = PMCommand.CtrlCmd();
cmd.CommandText = "select * from wapUserstate where empID = '" + PMUser.EmpID + "'";
DataTable DtuserState = new DataTable();
SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
dataAdapter.Fill(DtuserState);
dataAdapter.Dispose();
cmd.Connection.Close();
LoginMessage loginMessage = new LoginMessage();
if (DtuserState.Rows.Count > 0)
{
errortimes = Convert.ToInt32(DtuserState.Rows[0]["errortimes"]);
errortime = Convert.ToDateTime(DtuserState.Rows[0]["errortime"]);
string online = DtuserState.Rows[0]["online"].ToString();
string ipaddress = DtuserState.Rows[0]["userIpaddress"].ToString();
if (online == "0")
{
//如果已经大于5分钟了，删除数据库记录
if ((DateTime.Now - errortime).Minutes > 5)
{
cmd = PMCommand.CtrlCmd();
cmd.CommandText = "delete from wapUserstate where empID = '" + PMUser.EmpID + "' and online = '0'";
cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
}
else
{
loginMessage.LoginState = "2";
loginMessage.Message = "用户已经在IP:" + ipaddress + " 上登陆。";
loginMessage.EmpID = PMUser.EmpID;
loginMessage.EmpName = empName;
return(loginMessage);
}
}
//判断用户是否被锁定
if (errortimes < 3)
{
cmd = PMCommand.ModCmd();
cmd.CommandText = "select * from wapEmpList where empID = '" + PMUser.EmpID + "'";
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read() != true)
{
loginMessage.LoginState = "0";
loginMessage.Message = "登陆失败，没有这个用户名。";
loginMessage.EmpID = PMUser.EmpID;
loginMessage.EmpName = empName;
reader.Close();
}
else
{
//存在用户名，验证密码
string dbpass = reader["password"].ToString();
PMUser.UserSysID = reader["sysID"].ToString();
reader.Close();
if (PMUser.UserPass != dbpass)
{
//如果密码错误，去查询库里是不是第一次错误，如果是，计入错误记录和计数
DataRow[] dr;
if ((dr = DtuserState.Select("empID = '" + PMUser.EmpID + "'")).Count() > 0)
{
errortimes = Convert.ToInt32(dr[0][6]) + 1;
cmd = PMCommand.CtrlCmd();
cmd.CommandText = "update wapUserstate set userpass = '******',errortimes = '" + errortimes + "', errorTime = '" + DateTime.Now + "' where empID = '" + PMUser.EmpID + "'";
cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
else
{
errortimes = 1;
cmd = PMCommand.CtrlCmd();
cmd.CommandText = "insert into wapUserstate (empID,empName,userPass,userIpaddress,onLine,errorTimes,errorTime,message) values ('" + PMUser.EmpID + "','" + empName + "','" + PMUser.UserPass + "','" + PMUser.UserIpAdress + "','0','" + errortimes + "','" + DateTime.Now + "','用户密码错误')";
cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
if (errortimes <= 3)
{
loginMessage.LoginState = "0";
int interr = 3 - errortimes;
if (interr != 0)
{
loginMessage.Message = "用户密码错误！再输入" + (3 - errortimes).ToString() + "次错误密码后，账号将被锁定5分钟。";
}
else
{
loginMessage.Message = "用户被锁定，请在" + (3000 - (DateTime.Now - errortime).TotalSeconds).ToString() + "秒后登陆。";
}
loginMessage.EmpID = PMUser.EmpID;
loginMessage.EmpName = empName;
loginInfo.Add(PMUser.EmpID);
loginInfo.Add(empName);
}
}
else
{
cmd = PMCommand.CtrlCmd();
string userguid = Guid.NewGuid().ToString();
//查询是否有相同登陆记录，如果有，是否推出。
if ((_ = DtuserState.Select("empID = '" + PMUser.EmpID + "'")).Count() > 0)
{
cmd.CommandText = "update wapUserstate set userpass = '******',errortimes = '0',errortime = '" + DateTime.Now + "',online = '1',message = '登陆成功',userGuid = '" + userguid + "',useripaddress = '" + PMUser.UserIpAdress + "' where empID = '" + PMUser.EmpID + "'";
cmd.ExecuteNonQuery();
}
else
{
cmd.CommandText = "insert into wapUserstate (empID,empName,userPass,userIpaddress,onLine,errorTimes,errorTime,message,userGuid) values ('" + PMUser.EmpID + "','" + empName + "','" + PMUser.UserPass + "','" + PMUser.UserIpAdress + "','1','0','" + DateTime.Now + "','登陆成功','" + userguid + "')";
cmd.ExecuteNonQuery();
}
cmd.Connection.Close();
loginMessage.LoginState = "1";
loginMessage.Message = "登陆成功！";
loginMessage.EmpID = PMUser.EmpID;
loginMessage.EmpName = empName;
loginMessage.UserGuid = userguid;
}
}
}
else
{
loginMessage.LoginState = "0";
loginMessage.Message = "用户被锁定，请在" + (3000 - (DateTime.Now - errortime).TotalSeconds).ToString() + "秒后登陆。";
loginMessage.EmpID = PMUser.EmpID;
loginMessage.EmpName = empName;
}
return(loginMessage);
}