//return new DBOperationsInsert<sys_user, DBNull>().Insert(user);
#endregion
#region 用户信息修改
/// <summary>
/// 用户信息修改
/// </summary>
/// <param name="user">用户信息</param>
/// <returns>修改条数</returns>
public int Update(sys_user user, int userid)
{
try
{
int res;
string sql = "Update sys_user SET username = @p1,name = @p2,password = @p3,gender = @p4,org_id = @p6,job = @p5,phone = @p7,email = @p8,status = @p9,modify_time=getdate() where id = '" + userid + "'";
SqlParameter sqlParameter = new SqlParameter("@p1", user.username);
SqlParameter sqlParameter1 = new SqlParameter("@p2", user.name);
SqlParameter sqlParameter2 = new SqlParameter("@p3", user.password);
SqlParameter sqlParameter3 = new SqlParameter("@p4", user.gender);
SqlParameter sqlParameter4 = new SqlParameter("@p5", user.job);
SqlParameter sqlParameter5 = new SqlParameter("@p6", user.org_id);
SqlParameter sqlParameter6 = new SqlParameter("@p7", user.phone);
SqlParameter sqlParameter7 = new SqlParameter("@p8", user.email);
SqlParameter sqlParameter8 = new SqlParameter("@p9", user.status);
res = SqlHelper.ExecuteNonQuery(ConStr, CommandType.Text, sql, sqlParameter, sqlParameter1, sqlParameter2, sqlParameter3, sqlParameter4, sqlParameter5, sqlParameter6, sqlParameter7, sqlParameter8);
new LogUserDAL().Add(LogOperations.LogUser("修改用户信息"));
return(res);
}
catch (Exception e)
{
new LogSysDAL().Add(LogOperations.LogSys("修改用户信息" + e.Message));
return(0);
}
//return new DBOperationsUpdate<sys_dict>().UpdateById(id, newValues);
}
/// <summary>
/// 用户添加
/// </summary>
/// <param name="user">用户信息</param>
/// <returns>添加条数</returns>
public int Add(sys_user user)
{
try
{
int res;
string sql = "insert into sys_user (username,name,password,gender,job,org_id,phone,email,status,create_time,modify_time) values(@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,getdate(),getdate())";
SqlParameter sqlparameter = new SqlParameter("@p1", user.username);
SqlParameter sqlparameter1 = new SqlParameter("@p2", user.name);
SqlParameter sqlparameter2 = new SqlParameter("@p3", user.password);
SqlParameter sqlparameter3 = new SqlParameter("@p4", user.gender);
SqlParameter sqlparameter4 = new SqlParameter("@p5", user.job);
SqlParameter sqlparameter5 = new SqlParameter("@p6", user.org_id);
SqlParameter sqlparameter6 = new SqlParameter("@p7", user.phone);
SqlParameter sqlparameter7 = new SqlParameter("@p8", user.email);
SqlParameter sqlparameter8 = new SqlParameter("@p9", user.status);
res = SqlHelper.ExecuteNonQuery(ConStr, CommandType.Text, sql, sqlparameter, sqlparameter1, sqlparameter2, sqlparameter3, sqlparameter4, sqlparameter5, sqlparameter6, sqlparameter7, sqlparameter8);
new LogUserDAL().Add(LogOperations.LogUser("添加用户"));
return(res);
}
catch (Exception e)
{
new LogSysDAL().Add(LogOperations.LogSys("添加用户" + e.Message));
return(0);
}
}
/// <summary>
/// 通过parent_id查询组织机构
/// </summary>
/// <param name="pid"></param>
/// <returns>List<sys_org></returns>
public List <sys_org> SelectByparentid(int pid)
{
try
{
DataSet ds = new DataSet();
string sql = "Select * from sys_org where parent_id= '" + pid + "'";
List <sys_org> org = new List <sys_org>();
ds = SqlHelper.ExecuteDataset(ConStr, CommandType.Text, sql);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
sys_org org1 = new sys_org();
org1.id = (int)ds.Tables[0].Rows[i][nameof(sys_org.id)];
org1.org_name = (string)ds.Tables[0].Rows[i][nameof(sys_org.org_name)];
org1.create_time = (DateTime)ds.Tables[0].Rows[i][nameof(sys_org.create_time)];
org1.modify_time = (DateTime)ds.Tables[0].Rows[i][nameof(sys_org.modify_time)];
org.Add(org1);
}
new LogUserDAL().Add(LogOperations.LogUser("查询组织机构"));
return(org);
}
catch (Exception e)
{
new LogSysDAL().Add(LogOperations.LogSys("查询组织机构" + e.Message));
return(null);
}
}
/// <summary>
/// 用户组权限管理
/// </summary>
/// <param name="groupId">用户组ID</param>
/// <param name="menu_id">权限ID列表</param>
/// <returns>执行结果</returns>
public Result Perm(int groupId, List <int> menu_id)
{
PermDAL perm = new PermDAL();
Result res = new Result();
int re = 0;
try
{
re += perm.DelG2m(groupId);
foreach (var menuId in menu_id)
{
re += perm.AddG2m(groupId, menuId);
}
if (re == 0)
{
res.Code = RES.ERROR;
res.Message = "修改失败！";
return(res);
}
else
{
res.Code = RES.OK;
res.Message = "修改成功！";
new LogUserDAL().Add(LogOperations.LogUser("用户组权限修改"));
return(res);
}
}
catch
{
res.Code = RES.ERROR;
res.Message = "修改失败！";
return(res);
}
}
/// <summary>
/// 用户删除
/// </summary>
/// <param name="user_id">用户id</param>
/// <returns>删除条数</returns>
public int Del(int UserId)
{
string sql1 = "delete from sys_u2g where user_id='" + UserId + "'";
string sql2 = "delete from log_user where user_id='" + UserId + "'";
string sql3 = "delete from sys_user where id= '" + UserId + "'";
List <String> SQLStringList = new List <string>();
SQLStringList.Add(sql1);
SQLStringList.Add(sql2);
SQLStringList.Add(sql3);
using (SqlConnection conn = new SqlConnection(ConStr))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
SqlTransaction trans = conn.BeginTransaction();
cmd.Transaction = trans;
try
{
int count = 0;
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n];
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
count += cmd.ExecuteNonQuery();
}
}
trans.Commit();
new LogUserDAL().Add(LogOperations.LogUser("删除用户"));
return(count);
}
catch (Exception e)
{
trans.Rollback();
new LogSysDAL().Add(LogOperations.LogSys("删除用户" + e.Message));
return(0);
}
finally
{
conn.Close();
trans.Dispose();
conn.Dispose();
}
//return new DBOperationsDelete<sys_user, DBNull>().DeleteById(userId);
}
}
/// <summary>
/// 删除用户组中的用户
/// </summary>
/// <param name="userId">用户id</param>
/// <param name="groupId">用户id</param>
/// <returns>删除条数</returns>
public int DelG2u(int groupId, int userId)
{
try
{
int res;
string sql = "delete from sys_u2g where user_id='" + userId + "' and group_id= '" + groupId + "' ";
res = SqlHelper.ExecuteNonQuery(ConStr, CommandType.Text, sql);
new LogUserDAL().Add(LogOperations.LogUser("删除用户组中的用户"));
return(res);
}
catch (Exception e)
{
new LogSysDAL().Add(LogOperations.LogSys("" + e.Message));
return(0);
}
}
/// <summary>
/// 重置用户密码
/// </summary>
/// <param name="uesrid">用户id</param>
/// <returns>修改条数</returns>
public int RePassword(int userid)
{
try
{
int res;
string sql = "Update sys_user SET password = '******' modify_time=getdate() where id = '" + userid + "'";
res = SqlHelper.ExecuteNonQuery(ConStr, CommandType.Text, sql);
new LogUserDAL().Add(LogOperations.LogUser("重置密码"));
return(res);
}
catch (Exception e)
{
new LogSysDAL().Add(LogOperations.LogSys("重置密码" + e.Message));
return(0);
}
}
/// <summary>
/// 删除用户组
/// </summary>
/// <param name="id">用户组id</param>
/// <returns>删除条数</returns>
public int Del(int id)
{
string sql1 = "delete from sys_g2m where group_id='" + id + "'";
string sql2 = "delete from sys_u2g where group_id='" + id + "'";
string sql3 = "delete from sys_group where id='" + id + "'";
List <String> SQLStringList = new List <string>();
SQLStringList.Add(sql1);
SQLStringList.Add(sql2);
SQLStringList.Add(sql3);
using (SqlConnection conn = new SqlConnection(ConStr))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
SqlTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
int count = 0;
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n];
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
count += cmd.ExecuteNonQuery();
}
}
tx.Commit();
new LogUserDAL().Add(LogOperations.LogUser("删除用户组"));
return(count);
}
catch (Exception e)
{
tx.Rollback();
new LogSysDAL().Add(LogOperations.LogSys("删除用户组" + e.Message));
return(0);
}
finally
{
conn.Close();
tx.Dispose();
conn.Dispose();
}
}
}
/// <summary>
/// 删除组织机构
/// </summary>
/// <param name="orgid"></param>
/// <returns>删除条数</returns>
public int Del(int orgid)
{
try
{
int res = 0;
string sql = "delete from sys_org where id=@p1";
SqlParameter sqlparameter1 = new SqlParameter("@p", orgid);
res = SqlHelper.ExecuteNonQuery(ConStr, CommandType.Text, sql, sqlparameter1);
new LogUserDAL().Add(LogOperations.LogUser("删除组织机构"));
return(res);
}
catch (Exception e)
{
new LogSysDAL().Add(LogOperations.LogSys("删除组织机构" + e.Message));
return(0);
}
}
/// <summary>
///添加用户组
/// </summary>
/// <param name="group">用户组信息</param>
/// <returns>添加条数</returns>
public int Add(sys_group group)
{
try
{
int res;
string sql1 = "Insert into sys_group(group_name,remark,create_time,modify_time) values(@p1,@p2,getdate(),getdate())";
SqlParameter sqlparameter1 = new SqlParameter("@p1", group.group_name);
SqlParameter sqlparameter2 = new SqlParameter("@p2", group.remark);
res = SqlHelper.ExecuteNonQuery(ConStr, CommandType.Text, sql1, sqlparameter1, sqlparameter2);
new LogUserDAL().Add(LogOperations.LogUser("添加用户组"));
return(res);
}
catch (Exception e)
{
new LogSysDAL().Add(LogOperations.LogSys("添加用户组" + e.Message));
return(0);
}
}
/// <summary>
/// 用户组信息修改
/// </summary>
/// <param name="group">用户组信息</param>
/// <returns>返回修改条数</returns>
public int Update(sys_group group)
{
try {
int res;
string sql = "update sys_group set group_name = @p2,remark = @p3,modify_time = getdate() where id= @p1";
SqlParameter sqlParameter1 = new SqlParameter("@p1", group.id);
SqlParameter sqlParameter2 = new SqlParameter("@p2", group.group_name);
SqlParameter sqlParameter3 = new SqlParameter("@p3", group.remark);
res = SqlHelper.ExecuteNonQuery(ConStr, CommandType.Text, sql, sqlParameter1, sqlParameter2, sqlParameter3);
new LogUserDAL().Add(LogOperations.LogUser("修改用户组"));
return(res);
}
catch (Exception e)
{
new LogSysDAL().Add(LogOperations.LogSys("修改用户组" + e.Message));
return(0);
}
}
/// <summary>
/// 添加用户进用户组
/// </summary>
/// <param name="userId">用户id</param>
/// <param name="groupId">用户组id</param>
/// <returns>返回添加条数</returns>
public int AddU2g(int groupId, int userId)
{
try
{
int res;
string sql = "insert into sys_u2g (user_id,group_id) values(@p1,@p2)";
SqlParameter sqlparameter = new SqlParameter("@p1", userId);
SqlParameter sqlparameter1 = new SqlParameter("@p2", groupId);
res = SqlHelper.ExecuteNonQuery(ConStr, CommandType.Text, sql, sqlparameter, sqlparameter1);
new LogUserDAL().Add(LogOperations.LogUser("用户组添加用户"));
return(res);
}
catch (Exception e)
{
new LogSysDAL().Add(LogOperations.LogSys("用户组添加用户" + e.Message));
return(0);
}
}
/// <summary>
/// 根据id进行数据字典修改
/// </summary>
/// <param name="SysDict">数据字典信息</param>
/// <returns>修改修改条数</returns>
public int Update(sys_dict SysDict)
{
try {
int res = 0;
SqlParameter sqlParameter = new SqlParameter("@p1", SysDict.category_name);
SqlParameter sqlParameter2 = new SqlParameter("@p2", SysDict.id);
SqlParameter sqlParameter3 = new SqlParameter("@p3", DateTime.Now);
string sql = "update sys_dict set category_name = @p1,modify_time = @p3 where id = @p2";
res = SqlHelper.ExecuteNonQuery(ConStr, CommandType.Text, sql, sqlParameter, sqlParameter2, sqlParameter3);
new LogUserDAL().Add(LogOperations.LogUser("修改数据字典"));
return(res);
}
catch (Exception e)
{
new LogSysDAL().Add(LogOperations.LogSys("修改数据字典" + e.Message));
return(0);
}
}
/// <summary>
/// 人员信息修改
/// </summary>
/// <param name="info">需要更新的人员信息</param>
/// <returns>更新条数</returns>
public int Update(person_basic info)
{
try
{
// sql语句
string sql = "update person_basic set "
+ "name = @name, former_name = @former_name, gender = @gender, identity_number = @identity_number, birth_date = @birth_date, native_place = @native_place, marry_status = @marry_status, job_status = @job_status, income = @income, temper = @temper, family = @family, person_type_id = @person_type_id, qq = @qq, address = @address, phone = @phone, belong_place_id = @belong_place_id, nation = @nation, input_time = @input_time, user_id = @user_id, isdel = @isdel "
+ "where id = @id";
// 参数赋值
SqlParameter name = new SqlParameter("@name", info.name);
SqlParameter former_name = new SqlParameter("@former_name", info.former_name);
SqlParameter gender = new SqlParameter("@gender", info.gender);
SqlParameter identity_number = new SqlParameter("@identity_number", info.identity_number);
SqlParameter birth_date = new SqlParameter("@birth_date", info.birth_date);
SqlParameter native_place = new SqlParameter("@native_place", info.native_place);
SqlParameter marry_status = new SqlParameter("@marry_status", info.marry_status);
SqlParameter job_status = new SqlParameter("@job_status", info.job_status);
SqlParameter income = new SqlParameter("@income", info.income);
SqlParameter temper = new SqlParameter("@temper", info.temper);
SqlParameter family = new SqlParameter("@family", info.family);
SqlParameter person_type_id = new SqlParameter("@person_type_id", info.person_type_id);
SqlParameter qq = new SqlParameter("@qq", info.qq);
SqlParameter address = new SqlParameter("@address", info.address);
SqlParameter phone = new SqlParameter("@phone", info.phone);
SqlParameter belong_place_id = new SqlParameter("@belong_place_id", info.belong_place_id);
SqlParameter nation = new SqlParameter("@nation", info.nation);
SqlParameter input_time = new SqlParameter("@input_time", info.input_time);
SqlParameter user_id = new SqlParameter("@user_id", info.user_id);
SqlParameter isdel = new SqlParameter("@isdel", info.isdel);
SqlParameter id = new SqlParameter("@id", info.id);
// 执行sql语句
res = SqlHelper.ExecuteNonQuery(DALBase.ConStr, CommandType.Text, sql, name, former_name, gender, identity_number, birth_date, native_place, marry_status, job_status, income, temper, family, person_type_id, qq, address, phone, belong_place_id, nation, input_time, user_id, isdel, id);
// 添加用户日志
new LogUserDAL().Add(LogOperations.LogUser("人员信息修改"));
}
catch (Exception e)
{
// 添加系统日志
new LogSysDAL().Add(LogOperations.LogSys("人员信息修改：" + e.Message));
//return 0;
}
// 返回执行成功条数
return(res);
}
/// <summary>
/// 修改组织机构
/// </summary>
/// <param name="org"></param>
/// <returns>修改条数</returns>
public int Update(sys_org org)
{
try
{
int res = 0;
string sql = "upadate sys_menu SET parent_id = @p1,org_name = @p2,create_time = @p3,modify_time = @p4 where id = '" + org.id + "'";
SqlParameter sqlParameter1 = new SqlParameter("@p1", org.parent_id);
SqlParameter sqlParameter2 = new SqlParameter("@p2", org.org_name);
SqlParameter sqlParameter3 = new SqlParameter("@p3", org.create_time);
SqlParameter sqlParameter4 = new SqlParameter("@p4", org.modify_time);
res = SqlHelper.ExecuteNonQuery(ConStr, CommandType.Text, sql, sqlParameter1, sqlParameter2, sqlParameter3, sqlParameter4);
new LogUserDAL().Add(LogOperations.LogUser("修改组织机构"));
return(res);
}
catch (Exception e)
{
new LogSysDAL().Add(LogOperations.LogSys(e.Message));
return(0);
}
}
/// <summary>
/// 添加组织机构
/// </summary>
/// <param name="org"></param>
/// <returns>添加条数</returns>
public int Add(sys_org org)
{
try
{
int res = 0;
string sql = "insert into sys_menu(parent_id,org_name,creat_time,modify_time) values(@p1,@p2,@p3,@p4) where parent_id = @id";
SqlParameter sqlParameter1 = new SqlParameter("@p1", org.parent_id);
SqlParameter sqlParameter2 = new SqlParameter("@p2", org.org_name);
SqlParameter sqlParameter3 = new SqlParameter("@p3", org.create_time);
SqlParameter sqlParameter4 = new SqlParameter("@p4", org.modify_time);
res = SqlHelper.ExecuteNonQuery(ConStr, CommandType.Text, sql, sqlParameter1, sqlParameter2, sqlParameter3, sqlParameter4);
new LogUserDAL().Add(LogOperations.LogUser("添加组织机构"));
return(res);
}
catch (Exception e)
{
new LogSysDAL().Add(LogOperations.LogSys(e.Message));
return(0);
}
}
/// <summary>
/// 人员信息删除
/// </summary>
/// <param name="id">人员信息id</param>
/// <returns>删除条数</returns>
public int Del(int id)
{
try
{
// sql语句
string sql = "delete from person_basic where id = @id";
// 参数赋值
SqlParameter sp_id = new SqlParameter("@id", id);
// 执行sql语句
res = SqlHelper.ExecuteNonQuery(DALBase.ConStr, CommandType.Text, sql, sp_id);
// 添加用户日志
new LogUserDAL().Add(LogOperations.LogUser("人员信息删除"));
}
catch (Exception e)
{
// 添加系统日志
new LogSysDAL().Add(LogOperations.LogSys("人员信息删除：" + e.Message));
//return 0;
}
// 返回成功条数
return(res);
}
/// <summary>
/// 登陆时，通过用户名查询用户信息
/// </summary>
/// <param name="UserName">用户名</param>
/// <returns>用户信息</returns>
public string SelectLogin(string UserName)
{
try
{
sys_user user = new sys_user();
string sql = "select id,username,password from sys_user where username='******'";
DataSet ds = SqlHelper.ExecuteDataset(ConStr, CommandType.Text, sql);
user.password = (string)ds.Tables[0].Rows[0][nameof(sys_user.password)];
string username = (string)ds.Tables[0].Rows[0][nameof(sys_user.username)];
int id = (int)ds.Tables[0].Rows[0][nameof(sys_user.id)];
//保存用户信息
UserInfoDAL.UserId = id;
UserInfoDAL.UserName = username;
new LogUserDAL().Add(LogOperations.LogUser("登录"));
return(user.password);
}
catch (Exception e)
{
new LogSysDAL().Add(LogOperations.LogSys(e.Message));
return(null);
}
}
/// <summary>
/// 人员信息检索
/// </summary>
/// <param name="info">查询条件</param>
/// <returns>List类型</returns>
public List <person_basic> Query(person_basic info)
{
// 用于返回的列表
List <person_basic> list = new List <person_basic>();
if (info.Equals(null))
{
return(list);
}
try
{
// sql语句
string sql = "select * from person_basic where isdel = " + info.isdel + " and user_id = " + info.user_id;
// 用于拼接查询
List <SqlParameter> sqlPara = new List <SqlParameter>();
// 判断参数
if (info.id != 0) // id
{
sql += " and id = " + info.id;
}
if (!string.IsNullOrEmpty(info.name)) // name
{
sql += " and name like @name";
sqlPara.Add(new SqlParameter("@name", "%" + info.name + "%"));
}
if (!string.IsNullOrEmpty(info.identity_number)) // identity_number
{
sql += " and identity_number like @identity_number";
sqlPara.Add(new SqlParameter("@identity_number", "%" + info.identity_number + "%"));
}
if (info.person_type_id > 0) // person_type_id
{
sql += " and person_type_id = " + info.person_type_id;
}
if (!string.IsNullOrEmpty(info.native_place)) // native_place
{
sql += " and native_place like @native_place";
sqlPara.Add(new SqlParameter("@native_place", "%" + info.native_place + "%"));
}
DataSet ds = new DataSet();
// 执行sql语句并返回数据集
ds = SqlHelper.ExecuteDataset(DALBase.ConStr, CommandType.Text, sql, sqlPara.ToArray());
// 遍历表中的行
foreach (DataRow dr in ds.Tables[0].Rows)
{
// 封装实体类
person_basic pb = new person_basic();
pb.id = int.Parse(dr[0].ToString());
pb.name = dr[1].ToString();
pb.former_name = dr[2].ToString();
pb.gender = dr[3].ToString();
pb.identity_number = dr[4].ToString();
pb.birth_date = DateTime.Parse(dr[5].ToString());
pb.native_place = dr[6].ToString();
pb.marry_status = bool.Parse(dr[7].ToString());
pb.job_status = dr[8].ToString();
if (!(dr[9] is DBNull))
{
pb.income = decimal.Parse(dr[9].ToString());
}
pb.temper = dr[10].ToString();
pb.family = dr[11].ToString();
pb.person_type_id = int.Parse(dr[12].ToString());
pb.qq = dr[13].ToString();
pb.address = dr[14].ToString();
pb.phone = dr[15].ToString();
pb.belong_place_id = int.Parse(dr[16].ToString());
pb.nation = dr[17].ToString();
pb.input_time = DateTime.Parse(dr[18].ToString());
pb.user_id = int.Parse(dr[19].ToString());
pb.isdel = int.Parse(dr[20].ToString());
list.Add(pb);
}
// 添加用户日志
new LogUserDAL().Add(LogOperations.LogUser("人员信息检索"));
}
catch (Exception e)
{
// 添加系统日志
new LogSysDAL().Add(LogOperations.LogSys("人员信息检索：" + e.Message));
}
// 返回列表
return(list);
}
