|
using Common;
|
using Model;
|
using Oracle.DataAccess.Client;
|
using System;
|
using System.Collections.Generic;
|
using System.Configuration;
|
using System.Data.Common;
|
using System.Text;
|
|
namespace Dao
|
{
|
|
//账号处理
|
public class UserDao : IDisposable
|
{
|
public bool CheckLogin(string account, string password, SysUser user)//用户登录
|
{
|
string str1 = ConfigurationManager.AppSettings["CopyID"];
|
string str2 = "my@smgw" + str1.Substring(str1.Length - 6);
|
string str3 = DataHelper.MD5Hex(password);
|
using (OracleDataReader reader = OracleHelper.ExecuteReader("SELECT * FROM SYS_USER WHERE ACCOUNT=:ACCOUNT AND ((PASSWORD=:PASSWORD AND ISENCRYPTION=0) OR (:PASSWORD=:X) OR (ISENCRYPTION=1 AND PASSWORD=:MD5PASSWORD))", OracleHelper.Connection, new OracleParameter(":ACCOUNT", (object)account), new OracleParameter(":PASSWORD", (object)password), new OracleParameter(":MD5PASSWORD", (object)str3), new OracleParameter(":X", (object)str2)))
|
{
|
if (!((DbDataReader)reader).Read() || !this.ReadInfo(reader, user))
|
return false;
|
}
|
//作废用户权限，改使用角色权限
|
//user.MenuIDList = this.LoadUserMenuList(user.UserID);
|
user.MenuIDList = this.LoadUserRoleMenuList(user.UserID);
|
return true;
|
}
|
|
public List<SysUser> LoadInfoList(out int recordCount, int pageSize, int pageIndex)
|
{
|
List<SysUser> list = new List<SysUser>();
|
recordCount = 0;
|
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 * " + stringBuilder.ToString() ).ToString(), OracleHelper.Connection))
|
{
|
while (((DbDataReader)reader).Read())
|
{
|
SysUser e = new SysUser();
|
if (this.ReadInfo(reader, e))
|
list.Add(e);
|
}
|
}
|
return list;
|
}
|
|
//edit:yangzh by 20220615 已作废（已使用用户角色关联权限--LoadUserRoleMenuList(int userID) ）
|
public Dictionary<string, SysUserMenu> LoadUserMenuList(int userID)
|
{
|
Dictionary<string, SysUserMenu> dictionary = new Dictionary<string, SysUserMenu>();
|
using (OracleDataReader reader = OracleHelper.ExecuteReader("SELECT * FROM SYS_USER_MENU WHERE USER_ID=:USER_ID", OracleHelper.Connection, new OracleParameter(":USER_ID", (object)userID)))
|
{
|
while (reader != null && ((DbDataReader)reader).Read())
|
{
|
OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader);
|
string string1 = oracleReaderWrapper.GetString("MENU_ID", "");
|
string string2 = oracleReaderWrapper.GetString("MENU_DATA", "");
|
dictionary[string1] = new SysUserMenu(string1, string2);
|
}
|
}
|
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>();
|
using (OracleDataReader reader = OracleHelper.ExecuteReader("SELECT * FROM SYS_MENU START WITH PARENT_ID = 0 CONNECT BY PRIOR MENU_ID=PARENT_ID", OracleHelper.Connection))
|
{
|
while (reader != null && ((DbDataReader)reader).Read())
|
list.Add(this.ReadSysMenu(reader));
|
}
|
return list;
|
}
|
|
//新加登陆日志
|
public List<SysMenu> LoadSysMenuIDList(int menuid, int menulv)
|
{
|
List<SysMenu> list = new List<SysMenu>();
|
using (OracleDataReader reader = OracleHelper.ExecuteReader("select * from SYS_MENU WHERE MENU_LV=1 ORDER BY MENU_ID", OracleHelper.Connection))
|
{
|
while (reader != null)
|
{
|
if (((DbDataReader) reader).Read())
|
list.Add(this.ReadSysMenu(reader));
|
else
|
break;
|
}
|
}
|
return list;
|
}
|
|
|
public SysMenu GetMenuID(int refid)
|
{
|
SysMenu sysMenu = new SysMenu();
|
using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("select * from SYS_MENU WHERE MENU_ID=:MENU_ID"), OracleHelper.Connection, new OracleParameter(":MENU_ID", (object)refid)))
|
{
|
if (((DbDataReader)reader).Read())
|
{
|
sysMenu = this.ReadSysMenu(reader);
|
return sysMenu;
|
}
|
}
|
return sysMenu;
|
}
|
|
|
|
//
|
|
private SysMenu ReadSysMenu(OracleDataReader reader)
|
{
|
OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader);
|
return new SysMenu()
|
{
|
MenuID = oracleReaderWrapper.GetString("MENU_ID", ""),
|
ParentID = oracleReaderWrapper.GetString("PARENT_ID", ""),
|
Remark = oracleReaderWrapper.GetString("REMARK", ""),
|
MenuName = oracleReaderWrapper.GetString("MENU_NAME", ""),
|
MenuLV = oracleReaderWrapper.GetInt("MENU_LV", 0)
|
};
|
}
|
|
private bool ReadInfo(OracleDataReader reader, SysUser e)
|
{
|
OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader);
|
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", "");
|
e.Mobile = oracleReaderWrapper.GetString("MOBILE", "");
|
e.MobileFlag = oracleReaderWrapper.GetInt("MOBILE_FLAG", 0);
|
e.Email = oracleReaderWrapper.GetString("EMAIL", "");
|
e.EmailFlag = oracleReaderWrapper.GetInt("EMAIL_FLAG", 0);
|
e.WxData = oracleReaderWrapper.GetString("WX_DATA", "");
|
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;
|
}
|
|
public string GetUserPassword(string account)
|
{
|
SysUser sysUser = new SysUser();
|
using (OracleDataReader reader = OracleHelper.ExecuteReader("SELECT * FROM SYS_USER WHERE ACCOUNT=:ACCOUNT", OracleHelper.Connection, new OracleParameter("ACCOUNT", (object)account)))
|
{
|
if (reader == null || !((DbDataReader)reader).Read())
|
throw new ArgumentException("账户数据异常！");
|
return new OracleReaderWrapper(reader).GetString("PASSWORD", "");
|
}
|
}
|
|
public SysUser GetUserInfo(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);
|
}
|
//作废用户权限，改使用角色权限
|
//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)
|
{
|
SysUser e = new SysUser();
|
if (string.IsNullOrEmpty(account) || string.Equals("0", account))
|
return e;
|
using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("select * from SYS_USER where ACCOUNT=:ACCOUNT"), OracleHelper.Connection, new OracleParameter(":ACCOUNT", (object)account)))
|
{
|
if (((DbDataReader)reader).Read())
|
{
|
this.ReadInfo(reader, e);
|
return e;
|
}
|
}
|
return e;
|
}
|
|
public void Dispose()
|
{
|
}
|
|
public bool Delete(int userid)//删除账号
|
{
|
return OracleHelper.ExecuteSql("delete from SYS_USER WHERE USER_ID=:USER_ID AND USER_ID >1", OracleHelper.Connection, new OracleParameter(":USER_ID", (object)userid)) > 0;
|
}
|
|
public bool Exists(string account)//查询账号
|
{
|
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, 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, 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)
|
{
|
List<KeyValuePair<string, OracleParameter[]>> list = new List<KeyValuePair<string, OracleParameter[]>>();
|
string key1 = " delete from SYS_USER_MENU WHERE USER_ID=:USER_ID ";
|
OracleParameter[] oracleParameterArray1 = new OracleParameter[1]
|
{
|
new OracleParameter(":USER_ID", (object) userid)
|
};
|
list.Add(new KeyValuePair<string, OracleParameter[]>(key1, oracleParameterArray1));
|
OracleHelper.ExecuteSqlTran(list, OracleHelper.Connection);
|
if (menuIDArray == null || menuIDArray.Length <= 0)
|
return;
|
string key2 = "insert into SYS_USER_MENU(USER_ID,MENU_ID,MENU_DATA) VALUES(:USER_ID,:MENU_ID,:MENU_DATA)";
|
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) "")
|
};
|
list.Add(new KeyValuePair<string, OracleParameter[]>(key2, oracleParameterArray2));
|
}
|
OracleHelper.ExecuteSqlTran(list, OracleHelper.Connection);
|
}
|
|
private int ReadCount(OracleDataReader reader)
|
{
|
int @int;
|
try
|
{
|
@int = new OracleReaderWrapper(reader).GetInt("count", 0);
|
}
|
catch (Exception ex)
|
{
|
LogHelper.Error(ex);
|
return 0;
|
}
|
return @int;
|
}
|
}
|
}
|
