#region
|
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Text;
|
using CSFrameworkV5.Common;
|
using CSFrameworkV5.Core;
|
using CSFrameworkV5.Core.CodeGenerator;
|
using CSFrameworkV5.Interfaces;
|
using CSFrameworkV5.Models;
|
|
///*************************************************************************/
|
///*
|
///* 文件名 ：dalUser.cs
|
///* 程序说明 : 用户数据字典的DAL层
|
///* 原创作者 ：www.csframework.com
|
///*
|
///* Copyright 2006-2021 wwww.csframework.com, 保留所有权利.
|
///*
|
///**************************************************************************/
|
|
#endregion
|
|
namespace CSFrameworkV5.DataAccess
|
{
|
/// <summary>
|
/// 用户数据字典的DAL层
|
/// </summary>
|
[DefaultORM_UpdateMode(typeof(tb_MyUser), true)]
|
public class dalUser : dalBaseDataDict, IBridge_User
|
{
|
public dalUser(Loginer loginer)
|
: base(loginer)
|
{
|
_TableName = tb_MyUser.__TableName; //表名
|
_KeyName = tb_MyUser.__KeyName; //主键字段
|
_ModelType = typeof(tb_MyUser);
|
_Database = DatabaseProvider.SystemDatabase;
|
}
|
|
public bool CopyPermission(string sourceUser, string targetUser)
|
{
|
var sp = _Database.CreateSqlProc("usp_CopyPermission");
|
sp.AddParam("DataSetID", DbType.String, _Loginer.DBID);
|
sp.AddParam("AccountFrom", DbType.String, sourceUser);
|
sp.AddParam("AccountTo", DbType.String, targetUser);
|
sp.AddParam("OperateUser", DbType.String, _Loginer.Account);
|
var i = _Database.ExecuteCommand(sp.Command);
|
return i > 0;
|
}
|
|
/// <summary>
|
/// 删除用户
|
/// </summary>
|
/// <param name="account">帐号</param>
|
/// <returns></returns>
|
public bool DeleteUser(string account)
|
{
|
var sp = _Database.CreateSqlProc("usp_DeleteUser");
|
sp.AddParam("Account", DbType.String, account);
|
sp.AddParam("DataSetID", DbType.String, _Loginer.DBID);
|
var i = _Database.ExecuteCommand(sp.Command);
|
return i > 0;
|
}
|
|
public bool DeleteUserGroup(string account, string groupCode)
|
{
|
var sql =
|
$"DELETE FROM tb_MyGroupUser WHERE DataSetID={_Database.ParamSymboName}DataSetID AND Account={_Database.ParamSymboName}Account AND GroupCode={_Database.ParamSymboName}GroupCode";
|
|
var cmd = _Database.CreateCommand(sql.ToStringEx());
|
cmd.AddParam("DataSetID", DbType.String, _Loginer.DBID);
|
cmd.AddParam("Account", DbType.String, account);
|
cmd.AddParam("GroupCode", DbType.String, groupCode);
|
|
var i = _Database.ExecuteCommand(cmd.Command);
|
return i > 0;
|
}
|
|
public bool DeleteUserRole(string account, string roleID)
|
{
|
var sql =
|
$"DELETE FROM tb_MyUserRoles WHERE DataSetID={_Database.ParamSymboName}DataSetID AND Account={_Database.ParamSymboName}Account AND RoleID={_Database.ParamSymboName}RoleID";
|
|
var cmd = _Database.CreateCommand(sql.ToStringEx());
|
cmd.AddParam("DataSetID", DbType.String, _Loginer.DBID);
|
cmd.AddParam("Account", DbType.String, account);
|
cmd.AddParam("RoleID", DbType.String, roleID);
|
|
var i = _Database.ExecuteCommand(cmd.Command);
|
return i > 0;
|
}
|
|
public bool DestroyRights(string account)
|
{
|
var p = _Database.ParamSymboName;
|
|
var sql1 =
|
$"DELETE FROM tb_MyUserRoles WHERE DataSetID={p}DataSetID AND Account={p}Account;";
|
var sql2 =
|
$"DELETE FROM tb_MyGroupUser WHERE DataSetID={p}DataSetID1 AND Account={p}Account1;";
|
|
var cmd = _Database.CreateCommand(sql1 + sql2);
|
cmd.AddParam("DataSetID", _Loginer.DBID);
|
cmd.AddParam("DataSetID1", _Loginer.DBID);
|
cmd.AddParam("Account", account);
|
cmd.AddParam("Account1", account);
|
|
_Database.ExecuteCommand(cmd.Command);
|
|
return true;
|
}
|
|
/// <summary>
|
/// 检查用户是否存在
|
/// </summary>
|
/// <param name="userid">用户编号</param>
|
/// <returns></returns>
|
public bool ExistsUser(string account)
|
{
|
var sql =
|
$"SELECT COUNT(*) FROM tb_MyUser where Account={_Database.ParamSymboName}Account";
|
var cmd = _Database.CreateCommand(sql);
|
cmd.AddParam("Account", DbType.String, account);
|
var o = _Database.ExecuteScalar(cmd.Command);
|
return int.Parse(o.ToStringEx()) > 0;
|
}
|
|
/// <summary>
|
/// 获取用户数据
|
/// </summary>
|
/// <param name="account">帐号</param>
|
/// <returns></returns>
|
public DataTable GetUser(string account)
|
{
|
var sql = "SELECT * FROM tb_MyUser WHERE Account=" +
|
_Database.ParamSymboName + "Account";
|
var cmd = _Database.CreateCommand(sql);
|
cmd.AddParam("Account", DbType.String, account);
|
var dt = _Database.GetTable(cmd.Command, tb_MyUser.__TableName);
|
return dt;
|
}
|
|
public DataTable GetUserActionsList(string account)
|
{
|
var sp = _Database.CreateSqlProc("usp_GetUserActionsList");
|
sp.AddParam("Account", DbType.String, account);
|
sp.AddParam("DataSetID", DbType.String, _Loginer.DBID);
|
return _Database.GetTable(sp.Command, "usp_GetUserActionsList");
|
}
|
|
public DataTable GetUserGroups(string account)
|
{
|
var sql = "SELECT a.GroupCode,b.GroupName FROM tb_MyGroupUser a " +
|
"LEFT JOIN tb_MyGroup b ON a.GroupCode=b.GroupCode " +
|
"WHERE a.Account=" + _Database.ParamSymboName +
|
"Account AND a.DataSetID=" +
|
_Database.ParamSymboName + "DataSetID ";
|
var sp = _Database.CreateCommand(sql);
|
sp.AddParam("Account", DbType.String, account);
|
sp.AddParam("DataSetID", DbType.String, _Loginer.DBID);
|
return _Database.GetTable(sp.Command, tb_MyGroup.__TableName);
|
}
|
|
public DataSet GetUserReportData(DateTime createDateFrom,
|
DateTime createDateTo)
|
{
|
var cmd = _Database.CreateSqlProc("usp_GetUserList");
|
cmd.AddParam("DateFrom", createDateFrom);
|
cmd.AddParam("DateTo", createDateTo);
|
return _Database.GetDataSet(cmd.Command);
|
}
|
|
public DataTable GetUserRoles(string currentUser)
|
{
|
var sql =
|
"SELECT a.*,b.RoleName FROM tb_MyUserRoles a LEFT JOIN tb_MyRole b ON a.RoleID=b.RoleID WHERE a.DataSetID=" +
|
_Database.ParamSymboName + "DataSetID AND a.Account=" +
|
_Database.ParamSymboName + "Account";
|
var cmd = _Database.CreateCommand(sql);
|
cmd.AddParam("DataSetID", DbType.String, _Loginer.DBID);
|
cmd.AddParam("Account", DbType.String, currentUser);
|
return _Database.GetTable(cmd.Command, tb_MyUserRoles.__TableName);
|
}
|
|
public DataTable GetUserRoles4Picker(string currentUser, string content)
|
{
|
var sp = _Database.CreateSqlProc("usp_GetUserRoles4Picker");
|
sp.AddParam("DataSetID", DbType.String, _Loginer.DBID);
|
sp.AddParam("Account", DbType.String, currentUser);
|
sp.AddParam("SearchContent", DbType.String, content);
|
return _Database.GetTable(sp.Command, tb_MyRole.__TableName);
|
}
|
|
/// <summary>
|
/// 获取用户的角色(包括临时角色)
|
/// </summary>
|
/// <param name="account"></param>
|
/// <returns></returns>
|
public DataTable GetUserRolesAll(string account)
|
{
|
var sp = _Database.CreateSqlProc("usp_GetUserRoles");
|
sp.AddParam("Account", DbType.String, account);
|
sp.AddParam("DataSetID", DbType.String, _Loginer.DBID);
|
return _Database.GetTable(sp.Command, tb_MyUserRoles.__TableName);
|
}
|
|
/// <summary>
|
/// 获取所有用户列表,预设表格数据，必须返回图片
|
/// </summary>
|
/// <returns></returns>
|
public DataTable GetUsers()
|
{
|
var sql = "SELECT * FROM tb_MyUser";
|
return _Database.GetTable(sql, "tb_MyUser");
|
}
|
|
/// <summary>
|
/// 用户登录
|
/// </summary>
|
/// <param name="loginUser">登录信息</param>
|
/// <returns></returns>
|
public DataTable Login(LoginUser loginUser)
|
{
|
var cmd = _Database.CreateSqlProc("sp_sys_Login");
|
cmd.AddParam("Account", DbType.String, loginUser.Account);
|
cmd.AddParam("Password", DbType.String, loginUser.Password);
|
cmd.AddParam("DataSetID", DbType.String, loginUser.DBID);
|
cmd.AddParam("LoginUserType", DbType.String,
|
(int)loginUser.LoginAuthType);
|
cmd.AddParam("IP", DbType.String, loginUser.IP);
|
cmd.AddParam("MAC", DbType.String, loginUser.MAC);
|
var ds = _Database.GetDataSet(cmd.Command);
|
|
if (ds.Tables.Count == 2)
|
{
|
var error = ConvertEx.ToString(ds.Tables[1].Rows[0][0]);
|
if (error.Trim() != string.Empty)
|
throw new CustomException(error); //抛出异常
|
|
if (ds.Tables[0].Rows.Count >= 1) return ds.Tables[0];
|
|
return null;
|
}
|
|
return null;
|
}
|
|
/// <summary>
|
/// 登出
|
/// </summary>
|
/// <param name="loginUser">登录信息</param>
|
public void Logout(LoginUser loginUser)
|
{
|
var cmd = _Database.CreateSqlProc("sp_sys_Logout");
|
cmd.AddParam("Account", DbType.String, loginUser.Account);
|
cmd.AddParam("IP", DbType.String, loginUser.IP);
|
cmd.AddParam("MAC", DbType.String, loginUser.MAC);
|
_Database.ExecuteCommand(cmd.Command);
|
}
|
|
/// <summary>
|
/// 修改用户密码
|
/// </summary>
|
/// <param name="account">帐号</param>
|
/// <param name="pwd">密码</param>
|
/// <returns></returns>
|
public bool ModifyPassword(string account, string OldPwd, string NewPwd)
|
{
|
var sql = "UPDATE tb_MyUser SET Password=" +
|
_Database.ParamSymboName + "NewPwd WHERE Account=" +
|
_Database.ParamSymboName + "Account";
|
var cmd = _Database.CreateCommand(sql);
|
cmd.AddParam("OldPwd", DbType.String, OldPwd);
|
cmd.AddParam("NewPwd", DbType.String, NewPwd);
|
cmd.AddParam("Account", DbType.String, account);
|
object o = _Database.ExecuteCommand(cmd.Command);
|
return int.Parse(o.ToStringEx()) == 1;
|
}
|
|
/// <summary>
|
/// 插入PDA用户表
|
/// </summary>
|
/// <param name="account">帐号</param>
|
/// <param name="pwd">密码</param>
|
/// <returns></returns>
|
public bool insrer(string account, string OldPwd, string NewPwd)
|
{
|
var sql = string.Format(
|
@"INSERT INTO [dbo].[POWPAA](PAA001,PAA002,PAA003,PAA004,PAA006,PAA008,PAA011)VALUES('{0}','{1}','{2}','IT','normal','9999','MES')",
|
account, OldPwd, NewPwd);
|
return _Database.ExecuteSQL(sql) == 1;
|
}
|
|
|
public DataTable Search(string content)
|
{
|
var sql = "SELECT * FROM tb_MyUser ";
|
|
var cmd = _Database.CreateCommand("");
|
|
if (!string.IsNullOrEmpty(content))
|
{
|
sql = sql +
|
$" WHERE Account LIKE {_Database.ParamSymboName}Account OR UserName LIKE {_Database.ParamSymboName}UserName ";
|
cmd.AddParam("Account", "%" + content + "%");
|
cmd.AddParam("UserName", "%" + content + "%");
|
}
|
|
cmd.Command.CommandText = CodeSafeHelper.GetSafeSQL(sql);
|
|
var dt = _Database.GetTable(cmd.Command, tb_MyUser.__TableName);
|
return dt;
|
}
|
|
/// <summary>
|
/// 搜索用户资料
|
/// </summary>
|
/// <param name="content">搜索内容</param>
|
/// <param name="ignoreGroup">忽略改组的用户</param>
|
/// <returns></returns>
|
public List<AccountModel> SearchEx(string content, string ignoreGroup)
|
{
|
var sql = "SELECT Account,UserName FROM tb_MyUser WHERE 1=1 ";
|
|
var cmd = _Database.CreateCommand("");
|
|
if (!string.IsNullOrEmpty(content))
|
{
|
sql = sql +
|
$" AND (Account LIKE {_Database.ParamSymboName}Account OR UserName LIKE {_Database.ParamSymboName}UserName) ";
|
cmd.AddParam("Account", "%" + content + "%");
|
cmd.AddParam("UserName", "%" + content + "%");
|
}
|
|
if (!string.IsNullOrEmpty(ignoreGroup))
|
{
|
sql = sql +
|
$" AND Account NOT IN (SELECT Account FROM tb_MyGroupUser Where GroupCode={_Database.ParamSymboName}GroupCode) ";
|
cmd.AddParam("GroupCode", ignoreGroup);
|
}
|
|
cmd.Command.CommandText = CodeSafeHelper.GetSafeSQL(sql);
|
|
var list = _Database.ExecuteReaderList<AccountModel>(cmd.Command);
|
return list;
|
}
|
|
public bool SetLockState(string account, bool isLock)
|
{
|
var sql =
|
$"UPDATE tb_MyUser SET IsLocked={_Database.ParamSymboName}IsLocked WHERE Account={_Database.ParamSymboName}Account";
|
var cmd = _Database.CreateCommand(sql);
|
cmd.AddParam("IsLocked", isLock ? "Y" : "N");
|
cmd.AddParam("Account", account);
|
var i = _Database.ExecuteCommand(cmd.Command);
|
return true;
|
}
|
|
/// <summary>
|
/// 判断用户名密码是否正确
|
/// </summary>
|
/// <param name="User"></param>
|
/// <param name="Pw"></param>
|
/// <returns></returns>
|
public bool TryLogin(string user, string encodedPwd)
|
{
|
var sql = new StringBuilder();
|
sql.Append(" SELECT COUNT(*) FROM tb_MyUser ");
|
sql.Append(" WHERE Account=" + _Database.ParamSymboName +
|
"User AND Password=" + _Database.ParamSymboName +
|
"Pwd");
|
var cmd = _Database.CreateCommand(sql.ToStringEx());
|
cmd.AddParam("User", DbType.String, user);
|
cmd.AddParam("Pwd", DbType.String, encodedPwd);
|
return ConvertEx.ToInt(_Database.ExecuteScalar(cmd.Command)) > 0;
|
}
|
|
/// <summary>
|
/// 跟据表名创建SQL命令生成器
|
/// </summary>
|
/// <param name="tableName">表名</param>
|
/// <returns></returns>
|
protected override IGenerateSqlCommand CreateSqlGenerator(
|
DataTable table)
|
{
|
Type ORM = null;
|
|
if (table.TableName == tb_MyUser.__TableName)
|
ORM = typeof(tb_MyUser);
|
|
if (ORM == null) throw new Exception(table.TableName + "表没有ORM模型！");
|
|
return new GenerateSqlCmdByTableFields(ORM, table,
|
GeneratorFactory);
|
}
|
|
public override DataTable GetLookupData()
|
{
|
var sql =
|
"SELECT Account,UserName,Password,FlagAdmin,DataSets FROM tb_MyUser ORDER BY UserName";
|
return _Database.GetTable(sql, tb_MyUser.__TableName);
|
}
|
|
/// <summary>
|
/// 获取窗体的可用权限值
|
/// </summary>
|
/// <param name="account">登录帐号</param>
|
/// <param name="menuName">菜单名称</param>
|
/// <returns></returns>
|
public int GetUserActions(string account, string menuName)
|
{
|
var sp = _Database.CreateSqlProc("usp_GetUserActions");
|
sp.AddParam("Account", DbType.String, account);
|
sp.AddParam("menuName", DbType.String, menuName);
|
sp.AddParam("DataSetID", DbType.String, _Loginer.DBID);
|
var dt = _Database.GetTable(sp.Command);
|
if (dt.Rows.Count == 0) return 0;
|
|
return ConvertEx.ToInt(dt.Rows[0]["Actions"]);
|
}
|
|
/// <summary>
|
/// 跟据Novell网帐号获取系统帐号
|
/// </summary>
|
/// <param name="novellAccount">Novell网帐号</param>
|
/// <returns></returns>
|
public DataTable GetUserByNovellID(string novellAccount)
|
{
|
var sql =
|
$"SELECT * FROM tb_MyUser WHERE NovellAccount={_Database.ParamSymboName}NovellAccount";
|
var cmd = _Database.CreateCommand(sql);
|
cmd.AddParam("NovellAccount", DbType.String, novellAccount);
|
var dt = _Database.GetTable(cmd.Command, tb_MyUser.__TableName);
|
return dt;
|
}
|
|
public DataTable GetUserCodeNames()
|
{
|
var sql = "SELECT DISTINCT Account,UserName FROM tb_MyUser";
|
return _Database.GetTable(sql, "tb_MyUser");
|
}
|
|
public DataTable GetUserDirect(string account)
|
{
|
var sql = "SELECT * FROM tb_MyUser WHERE Account=" +
|
_Database.ParamSymboName + "Account";
|
var cmd = _Database.CreateCommand(sql);
|
cmd.AddParam("Account", DbType.String, account);
|
var dt = _Database.GetTable(cmd.Command, tb_MyUser.__TableName);
|
return dt;
|
}
|
|
/// <summary>
|
/// 仅返回帐号和密码资料，用于服务端的实时检测
|
/// </summary>
|
/// <returns></returns>
|
public static DataTable GetUserList4Cache()
|
{
|
var sp =
|
DatabaseProvider.SystemDatabase.CreateSqlProc(
|
"usp_GetUserList4Cache");
|
var dt =
|
DatabaseProvider.SystemDatabase.GetTable(sp.Command,
|
tb_MyUser.__TableName);
|
return dt;
|
}
|
}
|
}
|
