From 705909e14fe4e9f2fc261ee4eb40a8b41fa2f6d4 Mon Sep 17 00:00:00 2001
From: wzp <2880584989@qq.com>
Date: 星期二, 12 七月 2022 17:37:26 +0800
Subject: [PATCH] 增加免密登陆的token
---
web/Dao/UserDao.cs | 156 +++++++++++++++++++++++++++++++++++++++++++++++----
1 files changed, 143 insertions(+), 13 deletions(-)
diff --git a/web/Dao/UserDao.cs b/web/Dao/UserDao.cs
index a049159..3e8e6d3 100644
--- a/web/Dao/UserDao.cs
+++ b/web/Dao/UserDao.cs
@@ -6,6 +6,7 @@
using System.Collections.Generic;
using System.Configuration;
using System.Data.Common;
+using System.Text;
namespace Dao
{
@@ -23,7 +24,9 @@
if (!((DbDataReader)reader).Read() || !this.ReadInfo(reader, user))
return false;
}
- user.MenuIDList = this.LoadUserMenuList(user.UserID);
+ //浣滃簾鐢ㄦ埛鏉冮檺锛屾敼浣跨敤瑙掕壊鏉冮檺
+ //user.MenuIDList = this.LoadUserMenuList(user.UserID);
+ user.MenuIDList = this.LoadUserRoleMenuList(user.UserID);
return true;
}
@@ -31,12 +34,25 @@
{
List<SysUser> list = new List<SysUser>();
recordCount = 0;
- using (OracleDataReader reader = OracleHelper.ExecuteReader("select count(*) as count from Sys_User", OracleHelper.Connection))
+ StringBuilder stringBuilder = new StringBuilder();
+ stringBuilder.Append(" FROM ( ");
+ stringBuilder.Append(" SELECT su.*, t.ROLE_NAMES FROM sys_user su ");
+ stringBuilder.Append(" LEFT JOIN ( ");
+ stringBuilder.Append(" select su.user_id, wm_concat( sdd.dict_label) ROLE_NAMES from sys_user su ");
+ stringBuilder.Append(" LEFT JOIN (select DICT_VALUE, DICT_LABEL from sys_dict_data where dict_type='USER_ROLE' ) sdd ");
+ stringBuilder.Append(" on INSTR (user_type, sdd.dict_value) > 0 ");
+ stringBuilder.Append(" GROUP BY su.user_id ");
+ stringBuilder.Append(" ) t on t.user_id = su.user_id ");
+ stringBuilder.Append(" ) ");
+
+ //using (OracleDataReader reader = OracleHelper.ExecuteReader("select count(*) as count from Sys_User", OracleHelper.Connection))
+ using (OracleDataReader reader = OracleHelper.ExecuteReader("select count(*) as count " + stringBuilder.ToString(), OracleHelper.Connection))
{
while (((DbDataReader)reader).Read())
recordCount = this.ReadCount(reader);
}
- using (OracleDataReader reader = OracleHelper.ExecuteReader(PubConstant.doOracleSql(pageIndex, pageSize, recordCount, "select * from Sys_User").ToString(), OracleHelper.Connection))
+ //using (OracleDataReader reader = OracleHelper.ExecuteReader(PubConstant.doOracleSql(pageIndex, pageSize, recordCount, "select * from Sys_User").ToString(), OracleHelper.Connection))
+ using (OracleDataReader reader = OracleHelper.ExecuteReader(PubConstant.doOracleSql(pageIndex, pageSize, recordCount, "select * " + stringBuilder.ToString() ).ToString(), OracleHelper.Connection))
{
while (((DbDataReader)reader).Read())
{
@@ -48,6 +64,7 @@
return list;
}
+ //edit:yangzh by 20220615 宸蹭綔搴燂紙宸蹭娇鐢ㄧ敤鎴疯鑹插叧鑱旀潈闄�--LoadUserRoleMenuList(int userID) 锛�
public Dictionary<string, SysUserMenu> LoadUserMenuList(int userID)
{
Dictionary<string, SysUserMenu> dictionary = new Dictionary<string, SysUserMenu>();
@@ -64,6 +81,66 @@
return dictionary;
}
+ public Dictionary<string, SysUserMenu> LoadUserRoleMenuList(int userID)
+ {
+ StringBuilder stringBuilder = new StringBuilder();
+
+ Dictionary<string, SysUserMenu> dictionary = new Dictionary<string, SysUserMenu>();
+ string userType = GetUserRoleByUserId(userID);
+ if (!string.IsNullOrEmpty(userType))
+ {
+ List<object> list = DataConverter.stringToList(userType);
+ if (list.Count > 0)
+ {
+ stringBuilder.Append(" select sm.* from sys_menu sm ");
+ stringBuilder.Append(" left join ( ");
+ stringBuilder.Append(" select distinct * from ( ");
+ stringBuilder.Append(" select * from sys_role_menu where role_id = " + list[0] + " ");
+
+ for (int i = 1; i < list.Count; i++)
+ {
+ stringBuilder.Append(" union (select * from sys_role_menu where role_id = " + list[i] + ") ");
+
+ }
+ stringBuilder.Append(" ) bb ");
+ stringBuilder.Append(" ) cc on cc.menu_id = sm.menu_id ");
+ stringBuilder.Append(" where cc.menu_id is not null ");
+ using (OracleDataReader reader = OracleHelper.ExecuteReader(stringBuilder.ToString(), OracleHelper.Connection))
+ {
+ while (reader != null && ((DbDataReader)reader).Read())
+ {
+ /**
+ OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader);
+ string menuID = oracleReaderWrapper.GetString("MENU_ID", "");
+ string menuName = oracleReaderWrapper.GetString("MENU_NAME", "");
+ string remark = oracleReaderWrapper.GetString("REMARK", "");
+ string parentID = oracleReaderWrapper.GetString("PARENT_ID", "");
+ int menuLV = oracleReaderWrapper.GetInt("MENU_LV", 0);
+ SysMenu sysMenu = new SysMenu();
+ sysMenu.MenuID = menuID;
+ sysMenu.MenuName = menuName;
+ sysMenu.Remark = remark;
+ sysMenu.ParentID = parentID;
+ sysMenu.MenuLV = menuLV;
+
+ dictionary[menuID] = sysMenu;
+ **/
+
+ OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader);
+ string string1 = oracleReaderWrapper.GetString("MENU_ID", "");
+ //string string2 = oracleReaderWrapper.GetString("MENU_DATA", "");
+ string string2 = "";
+
+ dictionary[string1] = new SysUserMenu(string1, string2);
+
+ }
+ }
+ }
+
+ }
+ return dictionary;
+ }
+
public List<SysMenu> LoadSysMenuList()
{
List<SysMenu> list = new List<SysMenu>();
@@ -75,7 +152,7 @@
return list;
}
-//鏂板姞鐧婚檰鏃ュ織
+ //鏂板姞鐧婚檰鏃ュ織
public List<SysMenu> LoadSysMenuIDList(int menuid, int menulv)
{
List<SysMenu> list = new List<SysMenu>();
@@ -130,6 +207,10 @@
e.UserID = oracleReaderWrapper.GetInt("USER_ID", 0);
e.Account = oracleReaderWrapper.GetString("ACCOUNT", "");
e.Password = oracleReaderWrapper.GetString("PASSWORD", "");
+ e.Salt = oracleReaderWrapper.GetString("SALT", "");
+ e.UserName = oracleReaderWrapper.GetString("USER_NAME", "");
+ e.UserType = oracleReaderWrapper.GetString("USER_TYPE", "");
+ e.RoleNames = oracleReaderWrapper.GetString("ROLE_NAMES", "");
e.ExpireTime = oracleReaderWrapper.GetDateTime("EXPIRE_TIME");
e.CreateTime = oracleReaderWrapper.GetDateTime("CREATE_TIME");
e.Remark = oracleReaderWrapper.GetString("REMARK", "");
@@ -141,6 +222,7 @@
e.IsEncryption = oracleReaderWrapper.GetInt("ISENCRYPTION", 0);
e.IsVerification = oracleReaderWrapper.GetInt("ISVERIFICATION", 0);
e.ClientIp = oracleReaderWrapper.GetString("CLIENTIP", "");
+ e.Status = oracleReaderWrapper.GetInt("STATUS", 0);
return true;
}
@@ -163,8 +245,22 @@
if (reader != null && ((DbDataReader)reader).Read())
this.ReadInfo(reader, e);
}
- e.MenuIDList = this.LoadUserMenuList(userID);
+ //浣滃簾鐢ㄦ埛鏉冮檺锛屾敼浣跨敤瑙掕壊鏉冮檺
+ //e.MenuIDList = this.LoadUserMenuList(userID);
+ e.MenuIDList = this.LoadUserRoleMenuList(userID);
return e;
+ }
+
+ //鏍规嵁璐︽埛ID鑾峰彇鐢ㄦ埛瑙掕壊闆�
+ public string GetUserRoleByUserId(int userID)
+ {
+ SysUser e = new SysUser();
+ using (OracleDataReader reader = OracleHelper.ExecuteReader("SELECT * FROM SYS_USER WHERE USER_ID=:USER_ID", OracleHelper.Connection, new OracleParameter("USER_ID", (object)userID)))
+ {
+ if (reader != null && ((DbDataReader)reader).Read())
+ this.ReadInfo(reader, e);
+ }
+ return e.UserType;
}
public SysUser GetUser(string account)
@@ -197,19 +293,53 @@
return Convert.ToInt32(OracleHelper.ExecuteScalar("select COUNT(*) from SYS_USER WHERE ACCOUNT=:ACCOUNT", OracleHelper.Connection, new OracleParameter(":ACCOUNT", (object)account))) > 0;
}
+ public bool UserNameExists(string userName, int userId)//鏌ヨ鐢ㄦ埛鍚嶇О
+ {
+ //return Convert.ToInt32(OracleHelper.ExecuteScalar("select COUNT(*) from SYS_USER WHERE USER_NAME=:USER_NAME AND USER_ID!=:USER_ID", OracleHelper.Connection, new OracleParameter(":USER_NAME", (object)userName), new OracleParameter(":USER_ID", (object)userId))) > 0;
+
+ using (OracleDataReader reader = OracleHelper.ExecuteReader("select * from SYS_USER WHERE USER_NAME=:USER_NAME ", OracleHelper.Connection, new OracleParameter(":USER_NAME", (object)userName) ))
+ {
+ while (((DbDataReader)reader).Read())
+ {
+ SysUser e = new SysUser();
+ if (this.ReadInfo(reader, e))
+ {
+ if(e.UserID!= userId)
+ {
+ return true;
+ }
+ }
+ }
+ }
+
+ return false;
+ }
+
public bool Update(SysUser o)//淇敼璐﹀彿
{
- return OracleHelper.ExecuteSql("update SYS_USER SET ACCOUNT=:ACCOUNT,PASSWORD=:PASSWORD,EXPIRE_TIME=:EXPIRE_TIME,REMARK=:REMARK,MOBILE=:MOBILE,EMAIL=:EMAIL,MOBILE_FLAG=:MOBILE_FLAG,EMAIL_FLAG=:EMAIL_FLAG,ISENCRYPTION=:ISENCRYPTION,ISVERIFICATION=:ISVERIFICATION,CLIENTIP=:CLIENTIP,CREATE_TIME=:CREATE_TIME WHERE USER_ID=:USER_ID", OracleHelper.Connection, new OracleParameter(":ACCOUNT", (object)o.Account), new OracleParameter(":PASSWORD", (object)o.Password), new OracleParameter(":EXPIRE_TIME", (object)o.ExpireTime), new OracleParameter(":REMARK", (object)o.Remark), new OracleParameter(":USER_ID", (object)o.UserID), new OracleParameter(":MOBILE", (object)o.Mobile), new OracleParameter(":EMAIL", (object)o.Email), new OracleParameter(":MOBILE_FLAG", (object)o.MobileFlag), new OracleParameter(":EMAIL_FLAG", (object)o.EmailFlag), new OracleParameter(":ISENCRYPTION", (object)o.IsEncryption), new OracleParameter(":ISVERIFICATION", (object)o.IsVerification), new OracleParameter(":CLIENTIP", (object)o.ClientIp), new OracleParameter(":CREATE_TIME", (object)o.CreateTime)) > 0;
+ return OracleHelper.ExecuteSql("update SYS_USER SET ACCOUNT=:ACCOUNT, USER_NAME=:USER_NAME,USER_TYPE=:USER_TYPE,EXPIRE_TIME=:EXPIRE_TIME,REMARK=:REMARK,MOBILE=:MOBILE,EMAIL=:EMAIL,MOBILE_FLAG=:MOBILE_FLAG,EMAIL_FLAG=:EMAIL_FLAG,ISENCRYPTION=:ISENCRYPTION,ISVERIFICATION=:ISVERIFICATION,CLIENTIP=:CLIENTIP,CREATE_TIME=:CREATE_TIME WHERE USER_ID=:USER_ID", OracleHelper.Connection, new OracleParameter(":ACCOUNT", (object)o.Account), new OracleParameter(":USER_NAME", (object)o.UserName), new OracleParameter(":USER_TYPE", (object)o.UserType), new OracleParameter(":EXPIRE_TIME", (object)o.ExpireTime), new OracleParameter(":REMARK", (object)o.Remark), new OracleParameter(":USER_ID", (object)o.UserID), new OracleParameter(":MOBILE", (object)o.Mobile), new OracleParameter(":EMAIL", (object)o.Email), new OracleParameter(":MOBILE_FLAG", (object)o.MobileFlag), new OracleParameter(":EMAIL_FLAG", (object)o.EmailFlag), new OracleParameter(":ISENCRYPTION", (object)o.IsEncryption), new OracleParameter(":ISVERIFICATION", (object)o.IsVerification), new OracleParameter(":CLIENTIP", (object)o.ClientIp), new OracleParameter(":CREATE_TIME", (object)o.CreateTime) ) > 0;
+ }
+
+ //鏇存柊鐘舵��
+ public bool UpdateStatus(SysUser o)//淇敼璐﹀彿
+ {
+ return OracleHelper.ExecuteSql("update SYS_USER SET STATUS=:STATUS WHERE USER_ID=:USER_ID", OracleHelper.Connection, new OracleParameter(":USER_ID", (object)o.UserID), new OracleParameter(":STATUS", (object)o.Status)) > 0;
}
public bool Add(SysUser e)//娣诲姞璐﹀彿
{
- return OracleHelper.ExecuteSql("INSERT INTO SYS_USER(USER_ID,ACCOUNT,PASSWORD,EXPIRE_TIME,REMARK,MOBILE,EMAIL,MOBILE_FLAG,EMAIL_FLAG,ISENCRYPTION,ISVERIFICATION,CLIENTIP,CREATE_TIME) VALUES(SYS_USER_ID_SEQ.NextVal,:ACCOUNT,:PASSWORD,:EXPIRE_TIME,:REMARK,:MOBILE,:EMAIL,:MOBILE_FLAG,:EMAIL_FLAG,:ISENCRYPTION,:ISVERIFICATION,:CLIENTIP,:CREATE_TIME)", OracleHelper.Connection, new OracleParameter(":ACCOUNT", (object)e.Account), new OracleParameter(":PASSWORD", (object)e.Password), new OracleParameter(":EXPIRE_TIME", (object)e.ExpireTime), new OracleParameter(":REMARK", (object)e.Remark), new OracleParameter(":MOBILE", (object)e.Mobile), new OracleParameter(":EMAIL", (object)e.Email), new OracleParameter(":MOBILE_FLAG", (object)e.MobileFlag), new OracleParameter(":EMAIL_FLAG", (object)e.EmailFlag), new OracleParameter(":ISENCRYPTION", (object)e.IsEncryption), new OracleParameter(":ISVERIFICATION", (object)e.IsVerification), new OracleParameter(":CLIENTIP", (object)e.ClientIp), new OracleParameter(":CREATE_TIME", (object)e.CreateTime)) > 0;
+ return OracleHelper.ExecuteSql("INSERT INTO SYS_USER(USER_ID,ACCOUNT,PASSWORD, SALT, USER_NAME, USER_TYPE, EXPIRE_TIME,REMARK,MOBILE,EMAIL,MOBILE_FLAG,EMAIL_FLAG,ISENCRYPTION,ISVERIFICATION,CLIENTIP,CREATE_TIME) VALUES(SYS_USER_ID_SEQ.NextVal,:ACCOUNT,:PASSWORD, :SALT, :USER_NAME,:USER_TYPE,:EXPIRE_TIME,:REMARK,:MOBILE,:EMAIL,:MOBILE_FLAG,:EMAIL_FLAG,:ISENCRYPTION,:ISVERIFICATION,:CLIENTIP,:CREATE_TIME)", OracleHelper.Connection, new OracleParameter(":ACCOUNT", (object)e.Account), new OracleParameter(":PASSWORD", (object)e.Password), new OracleParameter(":SALT", (object)e.Salt), new OracleParameter(":USER_NAME", (object)e.UserName), new OracleParameter(":USER_TYPE", (object)e.UserType), new OracleParameter(":EXPIRE_TIME", (object)e.ExpireTime), new OracleParameter(":REMARK", (object)e.Remark), new OracleParameter(":MOBILE", (object)e.Mobile), new OracleParameter(":EMAIL", (object)e.Email), new OracleParameter(":MOBILE_FLAG", (object)e.MobileFlag), new OracleParameter(":EMAIL_FLAG", (object)e.EmailFlag), new OracleParameter(":ISENCRYPTION", (object)e.IsEncryption), new OracleParameter(":ISVERIFICATION", (object)e.IsVerification), new OracleParameter(":CLIENTIP", (object)e.ClientIp), new OracleParameter(":CREATE_TIME", (object)e.CreateTime) ) > 0;
}
public bool UpdatePassword(int userID, string oldPassword, string newPassword)
{
return OracleHelper.ExecuteSql("update SYS_USER SET PASSWORD=:NEWPASSWORD,ISENCRYPTION=1 WHERE USER_ID=:USER_ID AND PASSWORD=:OLDPASSWORD", OracleHelper.Connection, new OracleParameter(":NEWPASSWORD", (object)newPassword), new OracleParameter(":OLDPASSWORD", (object)oldPassword), new OracleParameter(":USER_ID", (object)userID)) >= 1;
+ }
+
+ //鍔犲瘑鐩愪慨鏀瑰瘑鐮�
+ public bool UpdatePwd(string account, string password)
+ {
+ return OracleHelper.ExecuteSql("UPDATE SYS_USER SET PASSWORD=:PASSWORD WHERE ACCOUNT=:ACCOUNT", OracleHelper.Connection, new OracleParameter(":ACCOUNT", (object)account), new OracleParameter(":PASSWORD", (object)password) ) > 0;
}
public void UpdatePermission(int userid, string[] menuIDArray)
@@ -228,14 +358,14 @@
foreach (string str in menuIDArray)
{
OracleParameter[] oracleParameterArray2 = new OracleParameter[3]
- {
- new OracleParameter(":USER_ID", (object) userid),
- new OracleParameter(":MENU_ID", (object) str),
- new OracleParameter(":MENU_DATA", (object) "")
- };
+ {
+ new OracleParameter(":USER_ID", (object) userid),
+ new OracleParameter(":MENU_ID", (object) str),
+ new OracleParameter(":MENU_DATA", (object) "")
+ };
list.Add(new KeyValuePair<string, OracleParameter[]>(key2, oracleParameterArray2));
- OracleHelper.ExecuteSqlTran(list, OracleHelper.Connection);
}
+ OracleHelper.ExecuteSqlTran(list, OracleHelper.Connection);
}
private int ReadCount(OracleDataReader reader)
--
Gitblit v1.9.1