/// <summary>
/// 增加一条数据
/// </summary>
public int Add(SCZM.Model.System.sys_Person model, out string message)
{
message = "新增成功！";
if (model.Account != "")
{
if (dal.ExistsAccount(model.Account))
{
message = "对不起，该账号已存在！";
return(0);
}
}
//获得6位的salt加密字符串
model.Salt = Utils.GetCheckCode(6);
//以随机生成的6位字符串做为密钥加密
model.Password = DESEncrypt.Encrypt("123", model.Salt);
model.IsAdmin = false;
int rowId = dal.Add(model);
if (rowId < 1)
{
message = "新增失败！";
}
return(rowId);
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(SCZM.Model.System.sys_Person model, out string message)
{
message = "修改成功！";
int flagDep = 0;
int flagRole = 0;
int flagCtrlPer = 0;
Model.System.sys_Person tempModel = dal.GetModel(model.ID);
if (tempModel == null)
{
message = "对不起，该条数据已被其他人删除！";
return(false);
}
if (model.Account != "" && model.Account != tempModel.Account)
{
if (dal.ExistsAccount(model.Account))
{
message = "对不起，账号已存在！";
return(false);
}
}
model.Salt = tempModel.Salt;
model.Password = tempModel.Password;
model.IsAdmin = tempModel.IsAdmin;
if (model.DepId != tempModel.DepId)
{
flagDep = 1;
}
if (model.RoleId != tempModel.RoleId)
{
flagRole = 1;
}
if (model.CtrlPerId != tempModel.CtrlPerId)
{
flagCtrlPer = 1;
}
int rows = dal.Update(model, flagDep, flagRole, flagCtrlPer);
if (rows == 0)
{
message = "对不起，该条数据已被其他人删除！";
return(false);
}
else
{
SetPersonRoleCache(model.ID);
return(true);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public SCZM.Model.System.sys_Person GetModel(int ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 ID,PerName,DepId,PostId,PerTel,PerEmail,DDNo,WXNo,Account,Password,Salt,IsAdmin,RoleId,RoleName,CtrlPersonType,CtrlDepId,CtrlPerId,FlagDel,OperaName,OperaTime from sys_Person ");
strSql.Append(" where FlagDel=0 and ID=@ID");
SqlParameter[] parameters =
{
new SqlParameter("@ID", SqlDbType.Int, 4)
};
parameters[0].Value = ID;
SCZM.Model.System.sys_Person model = new SCZM.Model.System.sys_Person();
DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(SCZM.Model.System.sys_Person model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into sys_Person(");
strSql.Append("PerName,DepId,PostId,PerTel,PerEmail,DDNo,WXNo,Account,Password,Salt,IsAdmin,RoleId,RoleName,CtrlPersonType,CtrlDepId,CtrlPerId,FlagDel,OperaName,OperaTime)");
strSql.Append(" values (");
strSql.Append("@PerName,@DepId,@PostId,@PerTel,@PerEmail,@DDNo,@WXNo,@Account,@Password,@Salt,@IsAdmin,@RoleId,@RoleName,@CtrlPersonType,@CtrlDepId,@CtrlPerId,@FlagDel,@OperaName,@OperaTime)");
strSql.Append(";set @ReturnValue= @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@PerName", SqlDbType.NVarChar, 20),
new SqlParameter("@DepId", SqlDbType.Int, 4),
new SqlParameter("@PostId", SqlDbType.Int, 4),
new SqlParameter("@PerTel", SqlDbType.VarChar, 50),
new SqlParameter("@PerEmail", SqlDbType.VarChar, 50),
new SqlParameter("@DDNo", SqlDbType.VarChar, 50),
new SqlParameter("@WXNo", SqlDbType.VarChar, 50),
new SqlParameter("@Account", SqlDbType.NVarChar, 20),
new SqlParameter("@Password", SqlDbType.VarChar, 50),
new SqlParameter("@Salt", SqlDbType.VarChar, 50),
new SqlParameter("@IsAdmin", SqlDbType.Bit, 1),
new SqlParameter("@RoleId", SqlDbType.VarChar, 50),
new SqlParameter("@RoleName", SqlDbType.NVarChar, 50),
new SqlParameter("@CtrlPersonType", SqlDbType.Int, 4),
new SqlParameter("@CtrlDepId", SqlDbType.Int, 4),
new SqlParameter("@CtrlPerId", SqlDbType.VarChar, 1000),
new SqlParameter("@FlagDel", SqlDbType.Bit, 1),
new SqlParameter("@OperaName", SqlDbType.NVarChar, 20),
new SqlParameter("@OperaTime", SqlDbType.DateTime),
new SqlParameter("@ReturnValue", SqlDbType.Int)
};
parameters[0].Value = model.PerName;
parameters[1].Value = model.DepId;
parameters[2].Value = model.PostId;
parameters[3].Value = model.PerTel;
parameters[4].Value = model.PerEmail;
parameters[5].Value = model.DDNo;
parameters[6].Value = model.WXNo;
parameters[7].Value = model.Account;
parameters[8].Value = model.Password;
parameters[9].Value = model.Salt;
parameters[10].Value = model.IsAdmin;
parameters[11].Value = model.RoleId;
parameters[12].Value = model.RoleName;
parameters[13].Value = model.CtrlPersonType;
parameters[14].Value = model.CtrlDepId;
parameters[15].Value = model.CtrlPerId;
parameters[16].Value = model.FlagDel;
parameters[17].Value = model.OperaName;
parameters[18].Value = model.OperaTime;
parameters[19].Direction = ParameterDirection.Output;
List <CommandInfo> sqllist = new List <CommandInfo>();
CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters);
sqllist.Add(cmd);
StringBuilder strSql3 = new StringBuilder();
strSql3.Append("insert into sys_PersonDep(PerId,DepId,OperaName) ");
strSql3.Append("values(@PerId,@DepId,@OperaName)");
SqlParameter[] parameters3 =
{
new SqlParameter("@PerId", SqlDbType.Int, 4),
new SqlParameter("@DepId", SqlDbType.Int, 4),
new SqlParameter("@OperaName", SqlDbType.NVarChar, 20)
};
parameters3[0].Direction = ParameterDirection.InputOutput;
parameters3[1].Value = model.DepId;
parameters3[2].Value = model.OperaName;
cmd = new CommandInfo(strSql3.ToString(), parameters3);
sqllist.Add(cmd);
if (model.RoleId != "")
{
StringBuilder strSql5;
string[] sArray = model.RoleId.Split(',');
foreach (string roleId in sArray)
{
strSql5 = new StringBuilder();
strSql5.Append("insert into sys_PersonRole(PerId,RoleId,OperaName) ");
strSql5.Append("values(@PerId,@RoleId,@OperaName)");
SqlParameter[] parameters5 =
{
new SqlParameter("@PerId", SqlDbType.Int, 4),
new SqlParameter("@RoleId", SqlDbType.Int, 4),
new SqlParameter("@OperaName", SqlDbType.NVarChar, 20)
};
parameters5[0].Direction = ParameterDirection.InputOutput;
parameters5[1].Value = int.Parse(roleId);
parameters5[2].Value = model.OperaName;
cmd = new CommandInfo(strSql5.ToString(), parameters5);
sqllist.Add(cmd);
}
}
//人员控制部门
if (model.CtrlPerId != "")
{
StringBuilder strSql7;
string[] sArray = model.CtrlPerId.Split(',');
foreach (string ctrlPerId in sArray)
{
strSql7 = new StringBuilder();
strSql7.Append("insert into sys_PersonCtrl(PerId,CtrlPerId) ");
strSql7.Append("values(@PerId,@CtrlPerId)");
SqlParameter[] parameters7 =
{
new SqlParameter("@PerId", SqlDbType.Int, 4),
new SqlParameter("@CtrlPerId", SqlDbType.Int, 4)
};
parameters7[0].Direction = ParameterDirection.InputOutput;
parameters7[1].Value = int.Parse(ctrlPerId);
cmd = new CommandInfo(strSql7.ToString(), parameters7);
sqllist.Add(cmd);
}
}
DbHelperSQL.ExecuteSqlTranWithIndentity(sqllist);
return((int)parameters[19].Value);
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public SCZM.Model.System.sys_Person DataRowToModel(DataRow row)
{
SCZM.Model.System.sys_Person model = new SCZM.Model.System.sys_Person();
if (row != null)
{
if (row["ID"] != null && row["ID"].ToString() != "")
{
model.ID = int.Parse(row["ID"].ToString());
}
if (row["PerName"] != null)
{
model.PerName = row["PerName"].ToString();
}
if (row["DepId"] != null && row["DepId"].ToString() != "")
{
model.DepId = int.Parse(row["DepId"].ToString());
}
if (row["PostId"] != null && row["PostId"].ToString() != "")
{
model.PostId = int.Parse(row["PostId"].ToString());
}
if (row["PerTel"] != null)
{
model.PerTel = row["PerTel"].ToString();
}
if (row["PerEmail"] != null)
{
model.PerEmail = row["PerEmail"].ToString();
}
if (row["DDNo"] != null)
{
model.DDNo = row["DDNo"].ToString();
}
if (row["WXNo"] != null)
{
model.WXNo = row["WXNo"].ToString();
}
if (row["Account"] != null)
{
model.Account = row["Account"].ToString();
}
if (row["Password"] != null)
{
model.Password = row["Password"].ToString();
}
if (row["Salt"] != null)
{
model.Salt = row["Salt"].ToString();
}
if (row["IsAdmin"] != null && row["IsAdmin"].ToString() != "")
{
if ((row["IsAdmin"].ToString() == "1") || (row["IsAdmin"].ToString().ToLower() == "true"))
{
model.IsAdmin = true;
}
else
{
model.IsAdmin = false;
}
}
if (row["RoleId"] != null)
{
model.RoleId = row["RoleId"].ToString();
}
if (row["RoleName"] != null)
{
model.RoleName = row["RoleName"].ToString();
}
if (row["CtrlPersonType"] != null && row["CtrlPersonType"].ToString() != "")
{
model.CtrlPersonType = int.Parse(row["CtrlPersonType"].ToString());
}
if (row["CtrlDepId"] != null && row["CtrlDepId"].ToString() != "")
{
model.CtrlDepId = int.Parse(row["CtrlDepId"].ToString());
}
if (row["CtrlPerId"] != null)
{
model.CtrlPerId = row["CtrlPerId"].ToString();
}
if (row["FlagDel"] != null && row["FlagDel"].ToString() != "")
{
if ((row["FlagDel"].ToString() == "1") || (row["FlagDel"].ToString().ToLower() == "true"))
{
model.FlagDel = true;
}
else
{
model.FlagDel = false;
}
}
if (row["OperaName"] != null)
{
model.OperaName = row["OperaName"].ToString();
}
if (row["OperaTime"] != null && row["OperaTime"].ToString() != "")
{
model.OperaTime = DateTime.Parse(row["OperaTime"].ToString());
}
}
return(model);
}
/// <summary>
/// 更新一条数据
/// </summary>
public int Update(SCZM.Model.System.sys_Person model, int flagDep, int flagRole, int flagCtrlPer)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update sys_Person set ");
strSql.Append("PerName=@PerName,");
strSql.Append("DepId=@DepId,");
strSql.Append("PostId=@PostId,");
strSql.Append("PerTel=@PerTel,");
strSql.Append("PerEmail=@PerEmail,");
strSql.Append("DDNo=@DDNo,");
strSql.Append("WXNo=@WXNo,");
strSql.Append("Account=@Account,");
strSql.Append("Password=@Password,");
strSql.Append("Salt=@Salt,");
strSql.Append("IsAdmin=@IsAdmin,");
strSql.Append("RoleId=@RoleId,");
strSql.Append("RoleName=@RoleName,");
strSql.Append("CtrlPersonType=@CtrlPersonType,");
strSql.Append("CtrlDepId=@CtrlDepId,");
strSql.Append("CtrlPerId=@CtrlPerId,");
strSql.Append("FlagDel=@FlagDel,");
strSql.Append("OperaName=@OperaName,");
strSql.Append("OperaTime=@OperaTime");
strSql.Append(" where FlagDel=0 and ID=@ID ");
SqlParameter[] parameters =
{
new SqlParameter("@PerName", SqlDbType.NVarChar, 20),
new SqlParameter("@DepId", SqlDbType.Int, 4),
new SqlParameter("@PostId", SqlDbType.Int, 4),
new SqlParameter("@PerTel", SqlDbType.VarChar, 50),
new SqlParameter("@PerEmail", SqlDbType.VarChar, 50),
new SqlParameter("@DDNo", SqlDbType.VarChar, 50),
new SqlParameter("@WXNo", SqlDbType.VarChar, 50),
new SqlParameter("@Account", SqlDbType.NVarChar, 20),
new SqlParameter("@Password", SqlDbType.VarChar, 50),
new SqlParameter("@Salt", SqlDbType.VarChar, 50),
new SqlParameter("@IsAdmin", SqlDbType.Bit, 1),
new SqlParameter("@RoleId", SqlDbType.VarChar, 50),
new SqlParameter("@RoleName", SqlDbType.NVarChar, 50),
new SqlParameter("@CtrlPersonType", SqlDbType.Int, 4),
new SqlParameter("@CtrlDepId", SqlDbType.Int, 4),
new SqlParameter("@CtrlPerId", SqlDbType.VarChar, 1000),
new SqlParameter("@FlagDel", SqlDbType.Bit, 1),
new SqlParameter("@OperaName", SqlDbType.NVarChar, 20),
new SqlParameter("@OperaTime", SqlDbType.DateTime),
new SqlParameter("@ID", SqlDbType.Int, 4)
};
parameters[0].Value = model.PerName;
parameters[1].Value = model.DepId;
parameters[2].Value = model.PostId;
parameters[3].Value = model.PerTel;
parameters[4].Value = model.PerEmail;
parameters[5].Value = model.DDNo;
parameters[6].Value = model.WXNo;
parameters[7].Value = model.Account;
parameters[8].Value = model.Password;
parameters[9].Value = model.Salt;
parameters[10].Value = model.IsAdmin;
parameters[11].Value = model.RoleId;
parameters[12].Value = model.RoleName;
parameters[13].Value = model.CtrlPersonType;
parameters[14].Value = model.CtrlDepId;
parameters[15].Value = model.CtrlPerId;
parameters[16].Value = model.FlagDel;
parameters[17].Value = model.OperaName;
parameters[18].Value = model.OperaTime;
parameters[19].Value = model.ID;
List <CommandInfo> sqllist = new List <CommandInfo>();
CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters);
sqllist.Add(cmd);
if (flagDep == 1)
{
StringBuilder strSql2 = new StringBuilder();
strSql2.Append("update sys_PersonDep set FlagDel=1 where FlagDel=0 and PerId=@PerId");
SqlParameter[] parameters2 =
{
new SqlParameter("@PerId", SqlDbType.Int, 4)
};
parameters2[0].Value = model.ID;
cmd = new CommandInfo(strSql2.ToString(), parameters2);
sqllist.Add(cmd);
StringBuilder strSql3 = new StringBuilder();
strSql3.Append("insert into sys_PersonDep(PerId,DepId,OperaName) ");
strSql3.Append("values(@PerId,@DepId,@OperaName)");
SqlParameter[] parameters3 =
{
new SqlParameter("@PerId", SqlDbType.Int, 4),
new SqlParameter("@DepId", SqlDbType.Int, 4),
new SqlParameter("@OperaName", SqlDbType.NVarChar, 20)
};
parameters3[0].Value = model.ID;
parameters3[1].Value = model.DepId;
parameters3[2].Value = model.OperaName;
cmd = new CommandInfo(strSql3.ToString(), parameters3);
sqllist.Add(cmd);
}
if (flagRole == 1)
{
StringBuilder strSql4 = new StringBuilder();
strSql4.Append("update sys_PersonRole set FlagDel=1 where FlagDel=0 and PerId=@PerId");
SqlParameter[] parameters4 =
{
new SqlParameter("@PerId", SqlDbType.Int, 4)
};
parameters4[0].Value = model.ID;
cmd = new CommandInfo(strSql4.ToString(), parameters4);
sqllist.Add(cmd);
if (model.RoleId != "")
{
StringBuilder strSql5;
string[] sArray = model.RoleId.Split(',');
foreach (string roleId in sArray)
{
strSql5 = new StringBuilder();
strSql5.Append("insert into sys_PersonRole(PerId,RoleId,OperaName) ");
strSql5.Append("values(@PerId,@RoleId,@OperaName)");
SqlParameter[] parameters5 =
{
new SqlParameter("@PerId", SqlDbType.Int, 4),
new SqlParameter("@RoleId", SqlDbType.Int, 4),
new SqlParameter("@OperaName", SqlDbType.NVarChar, 20)
};
parameters5[0].Value = model.ID;
parameters5[1].Value = int.Parse(roleId);
parameters5[2].Value = model.OperaName;
cmd = new CommandInfo(strSql5.ToString(), parameters5);
sqllist.Add(cmd);
}
}
}
//人员控制
if (flagCtrlPer == 1)
{
StringBuilder strSql6 = new StringBuilder();
strSql6.Append("delete from sys_PersonCtrl where PerId=@PerId");
SqlParameter[] parameters6 =
{
new SqlParameter("@PerId", SqlDbType.Int, 4)
};
parameters6[0].Value = model.ID;
cmd = new CommandInfo(strSql6.ToString(), parameters6);
sqllist.Add(cmd);
if (model.CtrlPerId != "")
{
StringBuilder strSql7;
string[] sArray = model.CtrlPerId.Split(',');
foreach (string ctrlPerId in sArray)
{
strSql7 = new StringBuilder();
strSql7.Append("insert into sys_PersonCtrl(PerId,CtrlPerId) ");
strSql7.Append("values(@PerId,@CtrlPerId)");
SqlParameter[] parameters7 =
{
new SqlParameter("@PerId", SqlDbType.Int, 4),
new SqlParameter("@CtrlPerId", SqlDbType.Int, 4)
};
parameters7[0].Value = model.ID;
parameters7[1].Value = int.Parse(ctrlPerId);
cmd = new CommandInfo(strSql7.ToString(), parameters7);
sqllist.Add(cmd);
}
}
}
int rows = DbHelperSQL.ExecuteSqlTran(sqllist);
return(rows);
}