/// <summary>
/// 查询所有权限
/// </summary>
/// <returns>用户组信息</returns>
public List <view_sys_g2m> SelectAllG2m()
{
try
{
DataSet ds = new DataSet();
string sql = "Select * from view_sys_g2m ";
List <view_sys_g2m> group = new List <view_sys_g2m>();
ds = SqlHelper.ExecuteDataset(ConStr, CommandType.Text, sql);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
view_sys_g2m group1 = new view_sys_g2m();
group1.id = (int)ds.Tables[0].Rows[i][nameof(view_sys_g2m.id)];
group1.menu_id = (int)ds.Tables[0].Rows[i][nameof(view_sys_g2m.menu_id)];
group1.group_name = (string)ds.Tables[0].Rows[i][nameof(view_sys_g2m.group_name)];
group1.menu_name = (string)ds.Tables[0].Rows[i][nameof(view_sys_g2m.menu_name)];
group.Add(group1);
}
return(group);
}
catch (Exception e)
{
new LogSysDAL().Add(LogOperations.LogSys(e.Message));
return(null);
}
}
/// <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>
/// <returns>数据字典list表</returns>
public List <sys_dict> SelectAll()
{
try
{
List <sys_dict> dict = new List <sys_dict>();
DataSet ds = new DataSet();
string sql = "select * from sys_dict";
ds = SqlHelper.ExecuteDataset(ConStr, CommandType.Text, sql);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
sys_dict dict1 = new sys_dict();
dict1.id = (int)ds.Tables[0].Rows[i][nameof(sys_dict.id)];
dict1.dict_name = (string)ds.Tables[0].Rows[i][nameof(sys_dict.dict_name)];
dict1.category_name = (string)ds.Tables[0].Rows[i][nameof(sys_dict.category_name)];
dict1.create_time = (DateTime)ds.Tables[0].Rows[i][nameof(sys_dict.create_time)];
dict1.modify_time = (DateTime)ds.Tables[0].Rows[i][nameof(sys_dict.modify_time)];
dict.Add(dict1);
}
return(dict);
}
catch (Exception e)
{
new LogSysDAL().Add(LogOperations.LogSys("查询数据字典" + e.Message));
return(null);
}
}
private void SetUpTextBoxes()
{
_currentBalance = LogOperations.GetBalanceExcludingToday();
try
{
Day selectedDay = LogOperations.GetSelectedDay(CurrentDate);
_startTimeHour = selectedDay.StartTime.ToString("HH");
_startTimeMin = selectedDay.StartTime.ToString("mm");
_lunchInMin = selectedDay.LunchInMin;
_endTimeHour = selectedDay.EndTime.ToString("HH");
_endTimeMin = selectedDay.EndTime.ToString("mm");
}
catch (NullReferenceException)
{
_startTimeHour = "00";
_startTimeMin = "00";
_lunchInMin = 45;
_endTimeHour = "00";
_endTimeMin = "00";
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
/// <summary>
/// 根据id查找用户组
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public List <sys_group> SelectGroupByID(int id)
{
DataSet ds = new DataSet();
string sql = "Select * from sys_group where id = @p1";
SqlParameter sqlParameter1 = new SqlParameter("@p1", id);
List <sys_group> group2 = new List <sys_group>();
ds = SqlHelper.ExecuteDataset(ConStr, CommandType.Text, sql, sqlParameter1);
try
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
sys_group group1 = new sys_group();
group1.id = (int)ds.Tables[0].Rows[i][nameof(sys_group.id)];
group1.group_name = (string)ds.Tables[0].Rows[i][nameof(sys_group.group_name)];
group1.remark = (string)ds.Tables[0].Rows[i][nameof(sys_group.remark)];
group1.create_time = (DateTime)ds.Tables[0].Rows[i][nameof(sys_group.create_time)];
group1.modify_time = (DateTime)ds.Tables[0].Rows[i][nameof(sys_group.modify_time)];
group2.Add(group1);
}
}
catch (Exception e)
{
new LogSysDAL().Add(LogOperations.LogSys("id查询用户组" + e.Message));
}
return(group2);
}
//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);
}
public MainViewModel()
{
string logPath = @$ "{Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)}\Stempeluhr";
Directory.CreateDirectory(logPath);
try
{
if (LogOperations.WasEndtimeRecorded())
{
LogOperations.RecordStartTime();
}
else if (LogOperations.GetRecordedDays().Last().Date == DateTime.Today)
{
MessageBox.Show("Willkommen zurück!");
}
else
{
MessageBox.Show($"Endzeit vom {LogOperations.GetRecordedDays().Last().Date.ToString("dd.MM.yyyy")} konnte nicht ermittelt werden. Bitte tragen Sie diese nach.");
LogOperations.RecordEndTime(new DateTime {
});
CurrentDate = LogOperations.GetRecordedDays().Last().Date;
LogOperations.RecordStartTime();
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
SystemEvents.SessionEnding += SystemEvents_SessionEnding;
SetUpTextBoxes();
}
/// <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>
/// 通过id查询用户
/// </summary>
/// <param name="id">用户id</param>
/// <returns>查询到的用户信息</returns>
public List <view_sys_u2g> SelectById(int id)
{
try
{
List <view_sys_u2g> user = new List <view_sys_u2g>();
string sql = "SELECT * from view_sys_u2g where id ='" + id + "'";
DataSet ds = new DataSet();
ds = SqlHelper.ExecuteDataset(ConStr, CommandType.Text, sql);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
view_sys_u2g user1 = new view_sys_u2g();
user1.id = (int)ds.Tables[0].Rows[i][nameof(view_sys_u2g.id)];
user1.name = (string)ds.Tables[0].Rows[i][nameof(view_sys_u2g.name)];
user1.username = (string)ds.Tables[0].Rows[i][nameof(view_sys_u2g.username)];
user1.password = (string)ds.Tables[0].Rows[i][nameof(view_sys_u2g.password)];
user1.gender = (string)ds.Tables[0].Rows[i][nameof(view_sys_u2g.gender)];
user1.phone = (string)ds.Tables[0].Rows[i][nameof(view_sys_u2g.phone)];
user1.email = (string)ds.Tables[0].Rows[i][nameof(view_sys_u2g.email)];
user1.job = (string)ds.Tables[0].Rows[i][nameof(view_sys_u2g.job)];
user1.status = (bool)ds.Tables[0].Rows[i][nameof(view_sys_u2g.status)];
user1.org_name = (string)ds.Tables[0].Rows[i][nameof(view_sys_u2g.org_name)];
user.Add(user1);
}
return(user);
}
catch (Exception e)
{
new LogSysDAL().Add(LogOperations.LogSys("查询用户信息" + e.Message));
return(null);
}
}
/// <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>
/// 查询用户组中的用户
/// </summary>
/// <param name="groupId">用户组id</param>
/// <returns>用户组信息</returns>
public List <view_sys_u2g> SelectU2g(int groupId)
{
try
{
DataSet ds = new DataSet();
string sql = "Select * from view_sys_u2g where group_id= '" + groupId + "'";
List <view_sys_u2g> group = new List <view_sys_u2g>();
ds = SqlHelper.ExecuteDataset(ConStr, CommandType.Text, sql);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
view_sys_u2g group1 = new view_sys_u2g();
group1.group_name = (string)ds.Tables[0].Rows[i][nameof(view_sys_u2g.group_name)];
group1.username = (string)ds.Tables[0].Rows[i][nameof(view_sys_u2g.username)];
group1.name = (string)ds.Tables[0].Rows[i][nameof(view_sys_u2g.name)];
group1.phone = (string)ds.Tables[0].Rows[i][nameof(view_sys_u2g.phone)];
group1.job = (string)ds.Tables[0].Rows[i][nameof(view_sys_u2g.job)];
group.Add(group1);
}
return(group);
}
catch (Exception e)
{
new LogSysDAL().Add(LogOperations.LogSys(e.Message));
return(null);
}
}
public void GetGroupsTest()
{
LogOperations groupOperations = new LogOperations();
groupOperations.GetGroups();
groupOperations.GetGroupById(0);
groupOperations.AddGroup(new Models.MGroup());
}
protected LogEntry CreateLogEntry(string message, LogOperations LogOperation, Exception ex = null)
{
var result = new LogEntry(Request.UserHostAddress,
User.Identity.Name,
message,
LogOperation.ToString(),
ex?.ToString());
return result;
}
/// <summary>
/// 通过输入条件查询用户
/// </summary>
/// <param name="UserInfo">查询条件</param>
/// <returns>查询到的用户信息</returns>
public List <view_sys_u2g> Select(sys_user UserInfo)
{
try
{
List <view_sys_u2g> user = new List <view_sys_u2g>();
string sql = "SELECT * from view_sys_u2g where 1=1 ";
List <SqlParameter> SqlPara = new List <SqlParameter>();
if (!string.IsNullOrEmpty(UserInfo.username))
{
sql += " and username like @username";
SqlPara.Add(new SqlParameter("@username", "%" + UserInfo.username + "%"));
}
if (!string.IsNullOrEmpty(UserInfo.name))
{
sql += " and name like @name";
SqlPara.Add(new SqlParameter("@name", "%" + UserInfo.name + "%"));
}
if (!string.IsNullOrEmpty(UserInfo.gender))
{
sql += " and gender like @gender";
SqlPara.Add(new SqlParameter("@gender", "%" + UserInfo.gender + "%"));
}
if (!string.IsNullOrEmpty(UserInfo.job))
{
sql += " and job like @job";
SqlPara.Add(new SqlParameter("@job", "%" + UserInfo.job + "%"));
}
DataSet ds = new DataSet();
ds = SqlHelper.ExecuteDataset(ConStr, CommandType.Text, sql, SqlPara.ToArray());
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
view_sys_u2g user1 = new view_sys_u2g();
user1.id = (int)ds.Tables[0].Rows[i][nameof(view_sys_u2g.id)];
user1.name = (string)ds.Tables[0].Rows[i][nameof(view_sys_u2g.name)];
user1.username = (string)ds.Tables[0].Rows[i][nameof(view_sys_u2g.username)];
user1.gender = (string)ds.Tables[0].Rows[i][nameof(view_sys_u2g.gender)];
user1.phone = (string)ds.Tables[0].Rows[i][nameof(view_sys_u2g.phone)];
user1.job = (string)ds.Tables[0].Rows[i][nameof(view_sys_u2g.job)];
user1.status = (bool)ds.Tables[0].Rows[i][nameof(view_sys_u2g.status)];
user1.org_name = (string)ds.Tables[0].Rows[i][nameof(view_sys_u2g.org_name)];
user.Add(user1);
}
return(user);
}
catch (Exception e)
{
new LogSysDAL().Add(LogOperations.LogSys("查询用户信息" + e.Message));
return(null);
}
//return new DBOperationsSelect<sys_user>().SelectByConditions(conditions);
}
/// <summary>
/// 删除用户组的权限
/// </summary>
/// <param name="groupId">用户组id</param>
/// <returns>删除条数</returns>
public int DelG2m(int groupId)
{
try
{
int res = 0;
string sql = "Delete from sys_g2m where group_id='" + groupId + "'";
res = SqlHelper.ExecuteNonQuery(ConStr, CommandType.Text, sql);
return(res);
}
catch (Exception e)
{
new LogSysDAL().Add(LogOperations.LogSys(e.Message));
return(0);
}
}
/// <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="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="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="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="dictName"></param>
/// <returns>category_name和id</returns>
public List <sys_dict> SelectByDictName(sys_dict_type dictName)
{
try {
List <sys_dict> list = new List <sys_dict>();
string sql = "select * from sys_dict where dict_name = '";
switch (dictName)
{
case sys_dict_type.Cost:
sql += sys_dict_type.Cost.ToString() + "' ";
break;
case sys_dict_type.BelongPlace:
sql += sys_dict_type.BelongPlace.ToString() + "'";
break;
case sys_dict_type.Person:
sql += sys_dict_type.Person.ToString() + " ' ";
break;
default:
break;
}
DataSet ds = new DataSet();
ds = SqlHelper.ExecuteDataset(ConStr, CommandType.Text, sql);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
sys_dict dict1 = new sys_dict();
dict1.id = (int)ds.Tables[0].Rows[i][nameof(sys_dict.id)];
dict1.dict_name = (string)ds.Tables[0].Rows[i][nameof(sys_dict.dict_name)];
dict1.category_name = (string)ds.Tables[0].Rows[i][nameof(sys_dict.category_name)];
dict1.create_time = (DateTime)ds.Tables[0].Rows[i][nameof(sys_dict.create_time)];
dict1.modify_time = (DateTime)ds.Tables[0].Rows[i][nameof(sys_dict.modify_time)];
list.Add(dict1);
}
return(list);
}
catch (Exception e)
{
new LogSysDAL().Add(LogOperations.LogSys("根据数据字典查询" + e.Message));
return(null);
}
}
/// <summary>
/// 用户组权限添加
/// </summary>
/// <param name="groupId">用户组id</param>
/// <param name="menuId">权限id</param>
/// <returns>添加条数</returns>
public int AddG2m(int groupId, int menuId)
{
try
{
int res;
string sql = "Insert into sys_g2m(group_id,menu_id) values(@p1,@p2)";
SqlParameter sqlParameter1 = new SqlParameter("@p1", groupId);
SqlParameter sqlParameter2 = new SqlParameter("@p2", menuId);
res = SqlHelper.ExecuteNonQuery(ConStr, CommandType.Text, sql, sqlParameter1, sqlParameter2);
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="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="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="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="SysDict">数据字典</param>
/// <returns>
/// 返回修改条数
/// </returns>
public int Add(sys_dict SysDict)
{
try
{
int res = 0;
string sql = "insert into sys_dict (category_name,dict_name,create_time,modify_time) values(@p1,@p2,@p3,@p4)";
SqlParameter sqlParameter = new SqlParameter("@p1", SysDict.category_name);
SqlParameter sqlParameter2 = new SqlParameter("@p2", SysDict.dict_name);
SqlParameter sqlParameter3 = new SqlParameter("@p3", DateTime.Now);
SqlParameter sqlParameter4 = new SqlParameter("@p4", DateTime.Now);
res = SqlHelper.ExecuteNonQuery(ConStr, CommandType.Text, sql, sqlParameter, sqlParameter2, sqlParameter3, sqlParameter4);
//new LogUserDAL().Add(LogOperations.LogUser("添加数据字典"));
return(res);
}
catch (Exception e)
{
new LogSysDAL().Add(LogOperations.LogSys("添加数据字典：" + e.Message));
return(0);
}
}
public List <string> SelectAllDictName()
{
try {
DataSet ds = new DataSet();
List <string> dict = new List <string>();
string sql = "select dict_name from sys_dict group by dict_name";
ds = SqlHelper.ExecuteDataset(ConStr, CommandType.Text, sql);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
string name;
name = (string)ds.Tables[0].Rows[i][nameof(sys_dict.dict_name)];
dict.Add(name);
}
return(dict);
}
catch (Exception e)
{
new LogSysDAL().Add(LogOperations.LogSys("查询所有数据字典" + e.Message));
return(null);
}
}
