using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using MyOffice.Models;
namespace MyOffice.DAL
{
/*
*用户信息数据访问类
* 创建人:zwn
* 创建时间:2009-09-21
* 说明:用于对用户表中数据进行操作
* 修改人:tu
* 修改时间:2009-09-22
* 修改说明:
* 1.在读取用户表中RealName,ImageUrl时判别是否未空
* 2.各方法添加注释
*/
public static partial class UserInfoService
{
private static string publicSql = " and UserState=(select UserStateId from UserState where UserStateName!='被屏蔽') and RoleId!=(select RoleId from RoleInfo where RoleName='超级管理员') ";
/// <summary>
/// 根据用户ID修改用户密码
/// </summary>
/// <param name="userInfo"></param>
/// <returns></returns>
public static bool ModifyPasswordByUserId(UserInfo userInfo)
{
string sql =
"UPDATE UserInfo " +
"SET " + "Password = @Password " +
"WHERE UserId = @UserId";
try
{
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@Password", userInfo.Password),
new SqlParameter("@UserId",userInfo.UserId)
};
int result = DBHelper.ExecuteNonQuery(DBHelper.connString, CommandType.Text, sql, para);
if (result > 0)
return true;
return false;
}
catch (Exception e)
{
Console.WriteLine(e.Message);
throw e;
}
}
/// <summary>
/// 根据查询参数获取用户信息
/// </summary>
/// <param name="condition">查询条件</param>
/// <param name="conditionValue">对应查询条件的值</param>
/// <returns></returns>
public static IList<UserInfo> GetUserInfoByParam(string condition, string conditionValue)
{
string sql = "select * from [UserInfo] where 1=1 ";
if (!conditionValue.Equals("") && conditionValue.Length > 0)
{
if (condition.Equals("DepartId"))//当条件为“部门名称”时
{
sql += "and DepartId in (select [DepartId] from dbo.DepartInfo where [DepartName] like @conditionValue)";
}
else if (condition.Equals("RoleId"))//当条件为“角色”时
{
sql += "and RoleId in (select [RoleId] from dbo.RoleInfo where [RoleName] like @conditionValue)";
}
else if (condition.Equals("UserState"))//当条件为“状态”时
{
sql += "and UserState in (select [UserStateId] from dbo.UserState where [UserStateName] like @conditionValue)";
}
else if (condition.Equals("Gender"))//当条件为“性别”时
{
int Gender = 0;
if (conditionValue.Equals("女"))
Gender = 1;
else if (conditionValue.Equals("男"))
Gender = 0;
else
Gender = -1;
sql += " and " + condition + " =" + Gender;
}
else
{
sql += " and " + condition + " like @conditionValue";
}
}
return GetUserInfosBySql(sql + publicSql + " order by userId desc ", new SqlParameter("@conditionValue", "%" + conditionValue + "%"));
}
/// <summary>
/// 检查用户名是否存在数据库
/// </summary>
/// <param name="userName">用户名</param>
/// <returns></returns>
public static UserInfo CheckUserName(string userName)
{
IList<UserInfo> list = GetUserInfosBySql("select * from [UserInfo] where [UserName]=@UserName", new SqlParameter("@UserName", userName));
if (list.Count > 0)
return list[0];
return null;
}
/// <summary>
/// 根据部门ID获取所有用户信息
/// </summary>
/// <param name="departId">部门ID</param>
/// <returns></returns>
public static IList<UserInfo> GetAllUserInfoByDepartId(int departId)
{
string sql = "select * from [UserInfo] where DepartId=@DepartId";
return GetUserInfosBySql(sql, new SqlParameter("@DepartId", departId));
}
/// <summary>
/// 根据部门ID获取所有用户信息,除了自己
/// </summary>
/// <param name="departId">部门ID</param>
/// <returns></returns>
public static IList<UserInfo> GetAllUserInfoByDepartId(int departId, int UserId)
{
string sql = "select * from [UserInfo] where DepartId=@DepartId and UserId != @UserId";
SqlParameter[] para = new SqlParameter[] { new SqlParameter("@DepartId", departId), new SqlParameter("@UserId", UserId) };
return GetUserInfosBySql(sql, para);
}
/// <summary>
/// 添加用户信息
/// </summary>
/// <param name="userInfo">用户对象</param>
/// <returns>添加的用户信息</returns>
public static UserInfo AddUserInfo(UserInfo userInfo)
{
string sql =
"INSERT into UserInfo " +
"VALUES (@UserName, @Password, @DepartId, @Gender, @RoleId, @UserState,@RealName,@ImageURL)";
sql += " SELECT @@IDENTITY";
try
{
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@DepartId", userInfo.Depart.DepartId), //FK
new SqlParameter("@RoleId", userInfo.Role.RoleId), //FK
new SqlParameter("@UserState", userInfo.UserState.UserStateId), //FK
new SqlParameter("@UserName", userInfo.UserName),
new SqlParameter("@Password", userInfo.Password),
new SqlParameter("@Gender", userInfo.Gender),
new SqlParameter("@RealName",userInfo.RealName),
new SqlParameter("@ImageURL",userInfo.ImageURL)
};
int newId = Convert.ToInt32(DBHelper.ExecuteScalar(DBHelper.connString, CommandType.Text, sql, para));
return GetUserInfoByUserId(newId);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
throw e;
}
}
/// <summary>
/// 删除用户信息
/// </summary>
/// <param name="userInfo"></param>
/// <returns></returns>
public static bool DeleteUserInfo(UserInfo userInfo)
{
return DeleteUserInfoByUserId(userInfo.UserId);
}
/// <summary>
/// 根据用户ID删除用户信息
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public static bool DeleteUserInfoByUserId(int userId)
{
//string sql = "DELETE UserInfo WHERE UserId = @UserId";
string sql = "update UserInfo set UserState=(select UserStateId from UserState where UserStateName='被屏蔽') where userId=@UserId";
int result = DBHelper.ExecuteNonQuery(DBHelper.connString, CommandType.Text, sql, new SqlParameter("@UserId", userId));
if (result > 0)
return true;
return false;
}
/// <summary>
/// 修改用户信息
/// </summary>
/// <param name="userInfo"></param>
/// <returns></returns>
public static bool ModifyUserInfo(UserInfo userInfo)
{
string sql =
"UPDATE UserInfo " +
"SET " +
"DepartId = @DepartId, " + //FK
"RoleId = @RoleId, " + //FK
"UserState = @UserState, " + //FK
"UserName = @UserName, " +
"Gender = @Gender, " +
"RealName=@RealName, " +
"ImageURL=@ImageURL " +
"WHERE UserId = @UserId";
try
{
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@DepartId", userInfo.Depart.DepartId), //FK
new SqlParameter("@RoleId", userInfo.Role.RoleId), //FK
new SqlParameter("@UserState", userInfo.UserState.UserStateId), //FK
new SqlParameter("@UserName", userInfo.UserName),
new SqlParameter("@Gender", userInfo.Gender),
new SqlParameter("@RealName",userInfo.RealName),
new SqlParameter("@ImageURL",userInfo.ImageURL),
new SqlParameter("@UserId",userInfo.UserId)
};
int result = DBHelper.ExecuteNonQuery(DBHelper.connString, CommandType.Text, sql, para);
if (result > 0)
return true;
else
return false;
}
catch (Exception e)
{
Console.WriteLine(e.Message);
throw e;
}
}
/// <summary>
/// 得到全部用户信息信息
/// </summary>
/// <returns></returns>
public static IList<UserInfo> GetAllUserInfos()
{
string sqlAll = "SELECT * FROM UserInfo where 1=1" + publicSql + " order by userId desc ";//除超级管理员,全部查询
return GetUserInfosBySql(sqlAll);
}
/// <summary>
/// 根据用户ID得到用户信息
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public static UserInfo GetUserInfoByUserId(int userId)
{
string sql = "SELECT * FROM UserInfo WHERE UserId = @UserId";
int departId = 0;
int roleId;
int userState;
try
{
SqlDataReader reader = DBHelper.ExecuteReader(DBHelper.connString, CommandType.Text, sql, new SqlParameter("@UserId", userId));
if (reader.Read())
{
UserInfo userInfo = new UserInfo();
userInfo.UserId = (int)reader["UserId"];
userInfo.UserName = (string)reader["UserName"];
userInfo.Password = (string)reader["Password"];
userInfo.Gender = (int)reader["Gender"];
if (reader["RealName"].GetType().FullName != "System.DBNull")
userInfo.RealName = (string)reader["RealName"];
if (reader["ImageURL"].GetType().FullName != "System.DBNull")
userInfo.ImageURL = (string)reader["ImageURL"];
if (reader["DepartId"].GetType().FullName != "System.DBNull")
departId = Convert.ToInt32(reader["DepartId"]);
roleId = (int)reader["RoleId"]; //FK
userState = (int)reader["UserState"]; //FK
reader.Close();
if (departId != 0)
userInfo.Depart = DepartInfoService.GetDepartInfoByDepartId(departId);
userInfo.Role = RoleInfoService.GetRoleInfoByRoleId(roleId);
userInfo.UserState = UserStateService.GetUserStateByUserStateId(userState);
return userInfo;
}
else
{
reader.Close();
return null;
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
throw e;
}
}
/// <summary>
/// 使用sql语句对用户表进行操作(集合)
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="values"></param>
/// <returns></returns>
private static IList<UserInfo> GetUserInfosBySql(string safeSql)
{
List<UserInfo> list = new List<UserInfo>();
try
{
DataSet ds = new DataSet();
DBHelper.FillDataset(DBHelper.connString, CommandType.Text, safeSql, ds, new string[] { "UserInfo" });
DataTable table = ds.Tables[0];
foreach (DataRow row in table.Rows)
{
UserInfo userInfo = new UserInfo();
userInfo.UserId = Convert.ToInt32(row["UserId"]);
userInfo.UserName = (string)row["UserName"];
userInfo.Password = (string)row["Password"];
userInfo.Gender = (int)row["Gender"];
if (row["RealName"].GetType().FullName != "System.DBNull")
userInfo.RealName = (string)row["RealName"];
if (row["ImageURL"].GetType().FullName != "System.DBNull")
userInfo.ImageURL = (string)row["ImageURL"];
if (row["DepartId"].GetType().FullName != "System.DBNull")
userInfo.Depart = DepartInfoService.GetDepartInfoByDepartId(Convert.ToInt32(row["DepartId"])); //FK
userInfo.Role = RoleInfoService.GetRoleInfoByRoleId(Convert.ToInt32(row["RoleId"])); //FK
userInfo.UserState = UserStateService.GetUserStateByUserStateId(Convert.ToInt32(row["UserState"])); //FK
list.Add(userInfo);
}
return list;
}
catch (Exception e)
{
Console.WriteLine(e.Message);
throw e;
}
}
/// <summary>
/// 根据角色id查询用户
/// </summary>
/// <param name="roleId">角色id</param>
/// <returns>用户集合</returns>
public static IList<UserInfo> GetUserByRoleId(int roleId)
{
IList<UserInfo> list = new List<UserInfo>();
string sql = "select * from userInfo where roleId=@roleId";
try
{
DataSet ds = new DataSet();
DBHelper.FillDataset(DBHelper.connString, CommandType.Text, sql + publicSql, ds, new string[] { "UserInfo" }, new SqlParameter[] { new SqlParameter("@roleId", roleId) });
DataTable table = ds.Tables[0];
foreach (DataRow row in table.Rows)
{
UserInfo userInfo = new UserInfo();
userInfo.UserId = Convert.ToInt32(row["UserId"]);
userInfo.UserName = (string)row["UserName"];
userInfo.Password = (string)row["Password"];
userInfo.Gender = (int)row["Gender"];
if (row["RealName"].GetType().FullName != "System.DBNull")
userInfo.RealName = (string)row["RealName"];
if (row["ImageURL"].GetType().FullName != "System.DBNull")
userInfo.ImageURL = (string)row["ImageURL"];
if (row["DepartId"].GetType().FullName != "System.DBNull")
userInfo.Depart = DepartInfoService.GetDepartInfoByDepartId((int)row["DepartId"]); //FK
userInfo.Role = RoleInfoService.GetRoleInfoByRoleId((int)row["RoleId"]); //FK
userInfo.UserState = UserStateService.GetUserStateByUserStateId((int)row["UserState"]); //FK
list.Add(userInfo);
}
return list;
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 检查用户是否存在
/// </summary>
/// <param name="userInfo">用户信息</param>
/// <returns></returns>
public static UserInfo CheckUserInfo(UserInfo userInfo)
{
string sql = "select * from userInfo where userName=@name and password =@pass and UserState=(select UserStateId from UserState where UserStateName!='被屏蔽')";
SqlParameter[] paramItem = new SqlParameter[]{
new SqlParameter("@name",userInfo.UserName),
new SqlParameter("@pass",userInfo.Password)
};
int departId = 0;
int roleId;
int userState;
SqlDataReader reader = DBHelper.ExecuteReader(DBHelper.connString, CommandType.Text, sql, paramItem);
if (reader.Read())
{
userInfo.UserId = (int)reader["UserId"];
userInfo.UserName = (string)reader["UserName"];
userInfo.Password = (string)reader["Password"];
userInfo.Gender = (int)reader["Gender"];
if (reader["RealName"].GetType().FullName != "System.DBNull")
userInfo.RealName = (string)reader["RealName"];
if (reader["ImageURL"].GetType().FullName != "System.DBNull")
userInfo.ImageURL = (string)reader["ImageURL"];
if (reader["DepartId"].GetType().FullName != "System.DBNull")
departId = Convert.ToInt32(reader["DepartId"]);
roleId = (int)reader["RoleId"]; //FK
userState = (int)reader["UserState"]; //FK
reader.Close();
if (departId != 0)
userInfo.Depart = DepartInfoService.GetDepartInfoByDepartId(departId);
userInfo.Role = RoleInfoService.GetRoleInfoByRoleId(roleId);
userInfo.UserState = UserStateService.GetUserStateByUserStateId(userState);
}
else
return null;
return userInfo;
}
/// <summary>
/// 使用sql语句对用户表进行操作
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="values"></param>
/// <returns></returns>
private static IList<UserInfo> GetUserInfosBySql(string sql, params SqlParameter[] values)
{
List<UserInfo> list = new List<UserInfo>();
try
{
DataSet ds = new DataSet();
DBHelper.FillDataset(DBHelper.connString, CommandType.Text, sql, ds, new string[] { "UserInfo" }, values);
DataTable table = ds.Tables[0];
foreach (DataRow row in table.Rows)
{
UserInfo userInfo = new UserInfo();
userInfo.UserId = Convert.ToInt32(row["UserId"]);
userInfo.UserName = (string)row["UserName"];
userInfo.Password = (string)row["Password"];
userInfo.Gender = (int)row["Gender"];
if (row["RealName"].GetType().FullName != "System.DBNull")
userInfo.RealName = (string)row["RealName"];
if (row["ImageURL"].GetType().FullName != "System.DBNull")
userInfo.ImageURL = (string)row["ImageURL"];
if (row["DepartId"].GetType().FullName != "System.DBNull")
userInfo.Depart = DepartInfoService.GetDepartInfoByDepartId((int)row["DepartId"]); //FK
userInfo.Role = RoleInfoService.GetRoleInfoByRoleId((int)row["RoleId"]); //FK
userInfo.UserState = UserStateService.GetUserStateByUserStateId((int)row["UserState"]); //FK
list.Add(userInfo);
}
return list;
}
catch (Exception e)
{
Console.WriteLine(e.Message);
throw e;
}
}
/// <summary>
/// 根据 部门 机构 登录ID 用户名 查询用户
/// </summary>
/// <param name="dId">部门ID</param>
/// <param name="loginId">登录ID(userName)</param>
/// <param name="userName">用户名(realName)</param>
/// <param name="bid">机构ID</param>
/// <returns></returns>
public static List<UserInfo> GetUserInfoBySerach(int dId, string loginId, string userName, int bid)
{
List<SqlParameter> paramItem = new List<SqlParameter>();
string sql = "select Distinct u.* from userInfo u,DepartInfo d where u.departId = d.departId";
if (dId != 0)
{
sql += " and d.departId =" + dId;
}
if (bid != 0)
{
sql += " and d.branchId =" + bid;
}
if (loginId != "")
{
sql += " and userName =@userName";
paramItem.Add(new SqlParameter("@userName", loginId));
}
if (userName != "")
{
sql += " and realName like @realName";
paramItem.Add(new SqlParameter("@realName", "%" + userName + "%"));
}
return (List<UserInfo>)GetUserInfosBySql(sql, paramItem.ToArray());
}
/// <summary>
/// 根据用户ID得到用户信息 (王浩)
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public static UserInfo GetUserInfoByRealName(string realName)
{
string sql = "SELECT * FROM UserInfo WHERE RealName like @RealName";
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@RealName", "%"+realName+"%"),
};
IList<UserInfo> list = GetUserInfosBySql(sql, para);
if (list.Count > 0)
return list[0];
return null;
}
/// <summary>
/// 根据机构、部门查询用户
/// </summary>
/// <param name="branchId"></param>
/// <param name="departId"></param>
/// <returns></returns>
public static IList<UserInfo> GetUserByBranchIdOrDepartId(int branchId, int departId)
{
string sql = "select * from userInfo where 1=1 and userId!=(select userid from userInfo where userName='admin') ";
if (branchId > 0)
sql += " and DepartId in(select departId from DepartInfo where BranchID in(select branchId from branchInfo where branchid=" + branchId + ")) ";
if (departId > 0)
sql += " and DepartId in(select departId from DepartInfo where departId=" + departId + ") ";
return GetUserInfosBySql(sql);
}
}
}