/// <summary>
/// 新增部门信息
/// </summary>
/// <param name="_PBDEPEntity"></param>
/// <returns>返回string "-1"表示部门名称已经存在，否则表示新增成功</returns>
public string AddDeptInfor(PBDEPEntity _PBDEPEntity)
{
//判断是否存在该部门名称
DbParameter[] prams = { MakeInParam("@DeptName",(DbType)SqlDbType.VarChar,50,_PBDEPEntity.DeptName),
MakeInParam("@ParentDeptCode",(DbType)SqlDbType.VarChar,50,_PBDEPEntity.ParentDeptCode),
};
string sql = "select * from [PBDEP] where PBDEPDN = @DeptName and PBDEPPDC=@ParentDeptCode ";
if (ExecuteDataset(CommandType.Text, sql, prams).Tables[0].Rows.Count > 0)
{
return "-1";//该名称已经存在
}
else
{
DbParameter[] pramsInsert = {
MakeInParam("@PBDEPDC",(DbType)SqlDbType.VarChar,50,_PBDEPEntity.DeptCode),
MakeInParam("@PBDEPDN",(DbType)SqlDbType.VarChar,50,_PBDEPEntity.DeptName),
MakeInParam("@PBDEPDEN",(DbType)SqlDbType.VarChar,50,_PBDEPEntity.DeptEName),
MakeInParam("@PBDEPDTWN",(DbType)SqlDbType.VarChar,50,_PBDEPEntity.DeptTWName),
MakeInParam("@PBDEPPDC",(DbType)SqlDbType.VarChar,50,_PBDEPEntity.ParentDeptCode),
MakeInParam("@PBDEPUS",(DbType)SqlDbType.Char,1,_PBDEPEntity.DeptIsValid),
MakeInParam("@PBDEPOI",(DbType)SqlDbType.Int,4,_PBDEPEntity.DeptOrderItem ),
MakeInParam("@PBDEPPID",(DbType)SqlDbType.VarChar,50,_PBDEPEntity.ParentDeptID ),
};
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO [dbo].[PBDEP]");
sb.Append("([PBDEPDC]");
sb.Append(",[PBDEPDN]");
sb.Append(",[PBDEPDEN]");
sb.Append(",[PBDEPDTWN]");
sb.Append(",[PBDEPPDC]");
sb.Append(",[PBDEPPID]");
sb.Append(",[PBDEPOI]");
sb.Append(",[PBDEPUS])");
sb.Append("VALUES");
sb.Append("(@PBDEPDC");
sb.Append(",@PBDEPDN");
sb.Append(",@PBDEPDEN");
sb.Append(",@PBDEPDTWN,");
sb.Append("@PBDEPPDC, ");
sb.Append("@PBDEPPID, ");
sb.Append("@PBDEPOI, ");
sb.Append("@PBDEPUS)");
sb.Append(" select @@identity;");
return ExecuteScalar(CommandType.Text, sb.ToString(), pramsInsert).ToString();
}
}
private string DoInsert(HttpContext context)
{
string result = "";
string PBDEPDN = context.Request.Params["PBDEPDN"];
string PBDEPDEN = context.Request.Params["PBDEPDEN"];
string PBDEPDTWN = context.Request.Params["PBDEPDTWN"];
string PBDEPUS = context.Request.Params["PBDEPUS"];
string PBDEPOI = context.Request.Params["PBDEPOI"];
string PBDEPPDC = context.Request.Params["PBDEPPDC"];
string PBDEPPID = context.Request.Params["PBDEPPID"];
if (PBDEPOI == "")
{
PBDEPOI = "0";
}
string PBDEPDC = DbHelper.GetInstance().GetPBDEPDCbyPBDEPPDC(PBDEPPDC);
if (PBDEPDC == "-1")
{
result = "-1";
return result;
}
PBDEPEntity DeptEntity = new PBDEPEntity();
DeptEntity.DeptCode = PBDEPDC;
DeptEntity.DeptName = PBDEPDN;
DeptEntity.DeptEName = PBDEPDEN;
DeptEntity.DeptTWName = PBDEPDTWN;
DeptEntity.DeptIsValid = PBDEPUS;
DeptEntity.DeptOrderItem = Convert.ToInt16(PBDEPOI);
DeptEntity.ParentDeptCode = PBDEPPDC;
DeptEntity.ParentDeptID = PBDEPPID;
result = DbHelper.GetInstance().AddDeptInfor(DeptEntity);
if (result == "-1")
{
result = "-1";
}
else
{
result = PBDEPDC ;
}
//add Rio
int icount = 0;
string szItemName="";
string szItemValue="";
string AddItemStr = context.Request.Params["str"];
string[] ItemStr = AddItemStr.Split('|');
foreach (string istr in ItemStr)
{
string[] ItemValue = istr.Split('=');
foreach (string i in ItemValue)
{
icount++;
if (icount % 2 != 0)
szItemName = i.ToString();
else
{
szItemValue = i.ToString();
result = DbHelper.GetInstance().AddTempDeptInforTemp(PBDEPDC, szItemName, szItemValue);
if (result == "-1")
{
result = "-1";
}
}
}
}
return result;
}
private string DoUpdate(HttpContext context)
{
string result = "";
string PBDEPDC = context.Request.Params["PBDEPDC"];
string PBDEPDN = context.Request.Params["PBDEPDN"];
string PBDEPDEN = context.Request.Params["PBDEPDEN"];
string PBDEPDTWN = context.Request.Params["PBDEPDTWN"];
string PBDEPUS = context.Request.Params["PBDEPUS"];
string PBDEPOI = context.Request.Params["PBDEPOI"];
string PBDEPPDC = context.Request.Params["PBDEPPDC"];
string PBDEPID = context.Request.Params["PBDEPID"];
string PBDEPPID = context.Request.Params["PBDEPPID"];
string NewPBDEPPID = context.Request.Params["NewPBDEPPID"];
string OldPBDEPPDC = context.Request.Params["OldPBDEPPDC"];
if (PBDEPOI == "")
{
PBDEPOI = "0";
}
PBDEPEntity DeptEntity = new PBDEPEntity();
DeptEntity.DeptID = PBDEPID;
DeptEntity.DeptCode = PBDEPDC;
DeptEntity.DeptName = PBDEPDN;
DeptEntity.DeptEName = PBDEPDEN;
DeptEntity.DeptTWName = PBDEPDTWN;
DeptEntity.DeptIsValid = PBDEPUS;
DeptEntity.DeptOrderItem = Convert.ToInt16(PBDEPOI);
DeptEntity.ParentDeptCode = OldPBDEPPDC;
DeptEntity.ParentDeptID = PBDEPPID;
if (PBDEPPID == NewPBDEPPID)
{
result = DbHelper.GetInstance().UpdateDeptInfor(DeptEntity);
}
else
{
result = DbHelper.GetInstance().UpdateDeptInfor(DeptEntity, NewPBDEPPID);
}
//add Rio
int icount = 0;
string szItemName = "";
string szItemValue = "";
string AddItemStr = context.Request.Params["str"];
string[] ItemStr = AddItemStr.Split('|');
foreach (string istr in ItemStr)
{
string[] ItemValue = istr.Split('=');
foreach (string i in ItemValue)
{
icount++;
if (icount % 2 != 0)
szItemName = i.ToString();
else
{
szItemValue = i.ToString();
//DataTable dt = DbHelper.GetInstance().DoGetAddItem(PBDEPDC);
//if (dt.Rows.Count == 0)
result = DbHelper.GetInstance().AddTempDeptInforTemp(PBDEPDC, szItemName, szItemValue); //PBDEPDC表中没有相应的ID值，没有则添加，有则更新。
//else
// result = DbHelper.GetInstance().UpdateTempDeptInforTemp(PBDEPDC, szItemName, szItemValue);
if (result == "-1")
{
result = "-1";
}
}
}
}
return result;
}
/// <summary>
/// 根据DataTable获取部门实体的数组
/// </summary>
/// <param name="dataTable"></param>
/// <returns></returns>
public PBDEPEntity[] GetDeptEntityArray(DataTable dataTable)
{
int arrayItems = dataTable.Rows.Count;
PBDEPEntity[] deptArray = new PBDEPEntity[arrayItems];
for (int i = 0; i < arrayItems; i++)
{
deptArray[i] = GetDeptEntity(dataTable.Rows[i]);
}
return deptArray;
}
/// <summary>
/// 根据DataRow获取部门实体
/// </summary>
/// <param name="dataRow"></param>
/// <returns></returns>
public PBDEPEntity GetDeptEntity(DataRow dataRow)
{
PBDEPEntity _PBDEPEntity = new PBDEPEntity();
_PBDEPEntity.DeptID = dataRow["PBDEPID"].ToString();
_PBDEPEntity.DeptCode = dataRow["PBDEPDC"].ToString();
_PBDEPEntity.DeptName = dataRow["PBDEPDN"].ToString();
_PBDEPEntity.DeptEName = dataRow["PBDEPDEN"].ToString();
_PBDEPEntity.DeptTWName = dataRow["PBDEPDTWN"].ToString();
_PBDEPEntity.ParentDeptID = dataRow["PBDEPPID"].ToString();
_PBDEPEntity.ParentDeptCode = dataRow["PBDEPPDC"].ToString();
_PBDEPEntity.DeptOrderItem = Convert.ToInt32(dataRow["PBDEPOI"].ToString());
_PBDEPEntity.DeptIsValid = dataRow["PBDEPUS"].ToString();
return _PBDEPEntity;
}
/// <summary>
/// 删除部门信息，以部门实体为参数
/// </summary>
/// <param name="_PBDEPEntity"></param>
/// <returns></returns>
public string DeleteDeptInfor(PBDEPEntity _PBDEPEntity)
{
DbParameter[] prams = {
MakeInParam("@PBDEPDC",(DbType)SqlDbType.VarChar,50,_PBDEPEntity.DeptCode),
};
string sql = "";
sql = "delete from [PBDEP] where PBDEPDC = @PBDEPDC ";
return ExecuteNonQuery(CommandType.Text, sql, prams).ToString();
}
private PBDEPEntity GetPBDEPFromIDataReader(DbDataReader dr)
{
PBDEPEntity dt = new PBDEPEntity();
if (dr.FieldCount > 0)
{
dt.DeptID = dr["PBDEPID"].ToString();
dt.DeptCode = dr["PBDEPDC"].ToString();
dt.DeptName = dr["PBDEPDN"].ToString();
dt.DeptEName = dr["PBDEPDEN"].ToString();
dt.DeptTWName = dr["PBDEPDTWN"].ToString();
dt.ParentDeptID = dr["PBDEPPID"].ToString();
dt.ParentDeptCode = dr["PBDEPPDC"].ToString();
dt.DeptIsValid = dr["PBDEPUS"].ToString();
if (dr["PBDEPOI"].ToString() != "" || dr["PBDEPOI"] != null) dt.DeptOrderItem = Int32.Parse(dr["PBDEPOI"].ToString());
else
dt.DeptOrderItem = 0;
dr.Close();
return dt;
}
dr.Close();
return null;
}
/// <summary>
/// 修改多个部门信息
/// </summary>
/// <param name="_PBDEPEntity"></param>
/// <returns>返回string "0"表示全部修改成功，否则返回已经存在部门名称</returns>
public string UpDateDeptInfor(PBDEPEntity[] _PBDEPEntity)
{
for (int i = 0; i < _PBDEPEntity.Length; i++)
{
string upDateResult = UpDateDeptInfor(_PBDEPEntity[i]);
if (upDateResult == "-1")
{
return _PBDEPEntity[i].DeptName;
}
}
return "0";
}
public string UpDateDeptInfor(PBDEPEntity _PBDEPEntity, string NewPBDEPPID)
{
string connString = ConnectionString;
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand();
SqlTransaction transaction = null;
cmd.Connection = conn;
try
{
conn.Open();
transaction = conn.BeginTransaction();
cmd.Transaction = transaction;
string PBDEPID = _PBDEPEntity.DeptID;//部门ID
//先获得新上级部门的部门代码
string NewPBDEPPDC = "";
string sql = "select * from PBDEP where PBDEPID='" + NewPBDEPPID + "'";
cmd.CommandText = sql;
SqlDataReader rd = cmd.ExecuteReader();
if (rd.Read() || NewPBDEPPID == "0") //判断NewPBDEPPID==0， 是更改为一级部门的情况
{
if (NewPBDEPPID == "0")
NewPBDEPPDC = "";
else
NewPBDEPPDC = rd["PBDEPDC"].ToString();
rd.Close();
//获得变更后的部门代码
//
string NewPBDEPDC = GetPBDEPDCbyPBDEPPDC(NewPBDEPPDC, cmd);
//做更新
string sqlUpDate = "upDate PBDEP set PBDEPDC='" + NewPBDEPDC + "',PBDEPDN='" + _PBDEPEntity.DeptName + "',PBDEPDEN='" + _PBDEPEntity.DeptEName + "',PBDEPDTWN='" + _PBDEPEntity.DeptTWName + "',PBDEPPDC='" + NewPBDEPPDC + "',PBDEPUS='" + _PBDEPEntity.DeptIsValid + "',PBDEPPID='" + NewPBDEPPID + "' where PBDEPID='" + PBDEPID + "'";
cmd.CommandText = sqlUpDate;
int i = cmd.ExecuteNonQuery();
if (i == 1)
{
//看此部门代码下是否有子级代码
//DataTable dt = GetChildDeptbyDeptID(PBDEPID);
string sqldt = "select * from [PBDEP] where [PBDEPPID] ='" + PBDEPID + "'";
cmd.CommandText = sqldt;
SqlDataAdapter ad = new SqlDataAdapter();
ad.SelectCommand = cmd;
DataSet ds = new DataSet();
ad.Fill(ds);
DataTable dt = ds.Tables[0];
//有的话要改其部门代码，上级部门代码
if (dt.Rows.Count > 0)
{
for (int j = 0; j < dt.Rows.Count; j++)
{
string childResult = UpDateChildDept(dt.Rows[j]["PBDEPID"].ToString(), NewPBDEPDC, cmd);
if (childResult == "-1")
{
transaction.Rollback();
conn.Close();
return "-1";
}
}
}
//1。更新人事基本资料中的部门代码
string sqlUpDatePEEBI = "upDate PEEBI set PEEBIDEP=b.PBDEPDC from PEEBI a,PBDEP b where a.PEEBIDEPID=b.PBDEPID";
cmd.CommandText = sqlUpDatePEEBI;
cmd.ExecuteNonQuery();
//2。更新部门异动信息
string sqlUpDatePEDEC = @"
upDate PEDEC set PEDECPR=PBDEPDC from PBDEP where PEDECPRID=PBDEPID;
upDate PEDEC set PEDECNE=PBDEPDC from PBDEP where PEDECNEID=PBDEPID;
";
cmd.CommandText = sqlUpDatePEDEC;
cmd.ExecuteNonQuery();
string sqlUpDatePKDSI = " upDate PKDSI set PKDSIDC=PBDEPDC from PBDEP where PKDSIDID=PBDEPID; ";
cmd.CommandText = sqlUpDatePKDSI;
cmd.ExecuteNonQuery();
string sqlUpDatePKTPT = " upDate PKTPT set PKTPTDC=PBDEPDC from PBDEP where PKTPTDID=PBDEPID; ";
cmd.CommandText = sqlUpDatePKTPT;
cmd.ExecuteNonQuery();
transaction.Commit();
conn.Close();
return "1";
}
else
{
transaction.Rollback();
conn.Close();
return "-1";
}
}
else
{
transaction.Rollback();
conn.Close();
return "-1";
}
}
catch
{
transaction.Rollback();
conn.Close();
return "-1";
}
}
/// <summary>
/// 根据DataTable获取部门实体的数组
/// </summary>
/// <param name="dataTable"></param>
/// <returns></returns>
//public PBDEPEntity[] GetDeptEntityArray(DataTable dataTable)
//{
// int arrayItems = dataTable.Rows.Count;
// PBDEPEntity[] deptArray = new PBDEPEntity[arrayItems];
// for (int i = 0; i < arrayItems; i++)
// {
// deptArray[i] = GetDeptEntity(dataTable.Rows[i]);
// }
// return deptArray;
//}
/// <summary>
/// 修改部门信息
/// </summary>
/// <param name="_PBDEPEntity"></param>
/// <returns>返回string "-1"表示部门名称已经存在，否则表示修改成功</returns>
public string UpDateDeptInfor(PBDEPEntity _PBDEPEntity)
{
if (!_PBDEPEntity.DeptIsValid.Equals("1"))
{
string sqlCount = "select count(*) from PEEBI where PEEBIES != '02' and PEEBIDEP like '" + _PBDEPEntity.DeptCode + "%'";
if (Convert.ToInt32(ExecuteScalar(CommandType.Text, sqlCount)) > 0)
{
return "-2";
}
}
//判断是否存在该部门名称
DbParameter[] prams = {
MakeInParam("@DeptName",(DbType)SqlDbType.VarChar,50,_PBDEPEntity.DeptName),
MakeInParam("@DeptCode",(DbType)SqlDbType.VarChar,50,_PBDEPEntity.DeptCode),
MakeInParam("@PBDEPPDC",(DbType)SqlDbType.VarChar,50,_PBDEPEntity.ParentDeptCode),
};
string sql = "select * from [PBDEP] where [PBDEPDN] = @DeptName and [PBDEPDC]!=@DeptCode and [PBDEPPDC]=@PBDEPPDC";
if (ExecuteDataset(CommandType.Text, sql, prams).Tables[0].Rows.Count > 0)
{
return "-1";//该名称已经存在
}
else
{
DbParameter[] pramsUpDate = {
MakeInParam("@PBDEPDC",(DbType)SqlDbType.VarChar,50,_PBDEPEntity.DeptCode),
MakeInParam("@PBDEPDN",(DbType)SqlDbType.VarChar,50,_PBDEPEntity.DeptName),
MakeInParam("@PBDEPDEN",(DbType)SqlDbType.VarChar,50,_PBDEPEntity.DeptEName),
MakeInParam("@PBDEPDTWN",(DbType)SqlDbType.VarChar,50,_PBDEPEntity.DeptTWName),
MakeInParam("@PBDEPPDC",(DbType)SqlDbType.VarChar,50,_PBDEPEntity.ParentDeptCode),
MakeInParam("@PBDEPOI",(DbType)SqlDbType.Int,4,_PBDEPEntity.DeptOrderItem),
MakeInParam("@PBDEPUS",(DbType)SqlDbType.Char,1,_PBDEPEntity.DeptIsValid)
};
StringBuilder sb = new StringBuilder();
sb.Append("UpDate [dbo].[PBDEP] set ");
sb.Append("[PBDEPDN]=@PBDEPDN");
sb.Append(",[PBDEPDEN]=@PBDEPDEN");
sb.Append(",[PBDEPDTWN]=@PBDEPDTWN");
sb.Append(",[PBDEPPDC]=@PBDEPPDC");
sb.Append(",[PBDEPOI]=@PBDEPOI");
sb.Append(",[PBDEPUS]=@PBDEPUS");
sb.Append(" where [PBDEPDC]=@PBDEPDC");
sb.Append(" select @PBDEPDC;");
//return ExecuteScalar(CommandType.Text, sb.ToString(), pramsUpDate).ToString();
return ExecuteNonQuery(CommandType.Text, sb.ToString(), pramsUpDate).ToString();
}
}
/// <summary>
/// 删除部门信息，以部门实体数组为参数
/// </summary>
/// <param name="_PBDEPEntity"></param>
/// <returns></returns>
public string DeleteDeptInfor(PBDEPEntity[] _PBDEPEntity)
{
for (int i = 0; i < _PBDEPEntity.Length; i++)
{
DeleteDeptInfor(_PBDEPEntity[i]);
}
return "0";
}