/// <summary>
/// 新增职位信息
/// </summary>
/// <param name="_PCPOSEntity"></param>
/// <returns>返回string "-1"表示职位名称已经存在，否则表示新增成功</returns>
public string AddPCPOS(PCPOSEntity _PCPOSEntity)
{
//判断是否存在该职位名称
DbParameter[] prams = { MakeInParam("@DeptName",(DbType)SqlDbType.VarChar,50,_PCPOSEntity.DeptName),
MakeInParam("@ParentDeptCode",(DbType)SqlDbType.VarChar,50,_PCPOSEntity.ParentDeptCode),
};
string sql = "select * from [PCPOS] where PCPOSDN = @DeptName and PCPOSPDC=@ParentDeptCode ";
if (ExecuteDataset(CommandType.Text, sql, prams).Tables[0].Rows.Count > 0)
{
return "-1";//该名称已经存在
}
else
{
DbParameter[] pramsInsert = {
MakeInParam("@PCPOSDC",(DbType)SqlDbType.VarChar,50,_PCPOSEntity.DeptCode),
MakeInParam("@PCPOSDN",(DbType)SqlDbType.VarChar,50,_PCPOSEntity.DeptName),
MakeInParam("@PCPOSDEN",(DbType)SqlDbType.VarChar,50,_PCPOSEntity.DeptEName),
MakeInParam("@PCPOSDTWN",(DbType)SqlDbType.VarChar,50,_PCPOSEntity.DeptTWName),
MakeInParam("@PCPOSPDC",(DbType)SqlDbType.VarChar,50,_PCPOSEntity.ParentDeptCode),
MakeInParam("@PCPOSUS",(DbType)SqlDbType.Char,1,_PCPOSEntity.DeptIsValid),
MakeInParam("@PCPOSOI",(DbType)SqlDbType.Int,4,_PCPOSEntity.DeptOrderItem ),
};
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO [dbo].[PCPOS]");
sb.Append("([PCPOSDC]");
sb.Append(",[PCPOSDN]");
sb.Append(",[PCPOSDEN]");
sb.Append(",[PCPOSDTWN]");
sb.Append(",[PCPOSPDC]");
sb.Append(",[PCPOSOI]");
sb.Append(",[PCPOSUS])");
sb.Append("VALUES");
sb.Append("(@PCPOSDC");
sb.Append(",@PCPOSDN");
sb.Append(",@PCPOSDEN");
sb.Append(",@PCPOSDTWN,");
sb.Append("@PCPOSPDC, ");
sb.Append("@PCPOSOI, ");
sb.Append("@PCPOSUS)");
sb.Append(" select @@identity;");
return ExecuteScalar(CommandType.Text, sb.ToString(), pramsInsert).ToString();
}
}
/// <summary>
/// 修改多个职位信息
/// </summary>
/// <param name="_PCPOSEntity"></param>
/// <returns>返回string "0"表示全部修改成功，否则返回已经存在职位名称</returns>
public string UpDatePCPOS(PCPOSEntity[] _PCPOSEntity)
{
for (int i = 0; i < _PCPOSEntity.Length; i++)
{
string upDateResult = UpDatePCPOS(_PCPOSEntity[i]);
if (upDateResult == "-1")
{
return _PCPOSEntity[i].DeptName;
}
}
return "0";
}
private PCPOSEntity GetPCPOSFromIDataReader(DbDataReader dr)
{
PCPOSEntity dt = new PCPOSEntity();
if (dr.FieldCount > 0)
{
dt.DeptCode = dr["PCPOSDC"].ToString();
dt.DeptName = dr["PCPOSDN"].ToString();
dt.DeptEName = dr["PCPOSDEN"].ToString();
dt.DeptTWName = dr["PCPOSDTWN"].ToString();
dt.ParentDeptCode = dr["PCPOSPDC"].ToString();
dt.DeptIsValid = dr["PCPOSUS"].ToString();
if (dr["PCPOSOI"].ToString() != "" || dr["PCPOSOI"] != null) dt.DeptOrderItem = Int32.Parse(dr["PCPOSOI"].ToString());
else
dt.DeptOrderItem = 0;
dr.Close();
return dt;
}
dr.Close();
return null;
}
/// <summary>
/// 修改职位信息
/// </summary>
/// <param name="_PCPOSEntity"></param>
/// <returns>返回string "-1"表示职位名称已经存在，否则表示修改成功</returns>
public string UpDatePCPOS(PCPOSEntity _PCPOSEntity)
{
if (!_PCPOSEntity.DeptIsValid.Equals("1"))
{
string sqlCount = "select count(*) from PEEBI where PEEBIES != '02' and PEEBIDEP like '" + _PCPOSEntity.DeptCode + "%'";
if (Convert.ToInt32(ExecuteScalar(CommandType.Text, sqlCount)) > 0)
{
return "-2";
}
}
//判断是否存在该职位名称
DbParameter[] prams = {
MakeInParam("@DeptName",(DbType)SqlDbType.VarChar,50,_PCPOSEntity.DeptName),
MakeInParam("@DeptCode",(DbType)SqlDbType.VarChar,50,_PCPOSEntity.DeptCode),
MakeInParam("@PCPOSPDC",(DbType)SqlDbType.VarChar,50,_PCPOSEntity.ParentDeptCode),
};
string sql = "select * from [PCPOS] where [PCPOSDN] = @DeptName and [PCPOSDC]!=@DeptCode and [PCPOSPDC]=@PCPOSPDC";
if (ExecuteDataset(CommandType.Text, sql, prams).Tables[0].Rows.Count > 0)
{
return "-1";//该名称已经存在
}
else
{
DbParameter[] pramsUpDate = {
MakeInParam("@PCPOSDC",(DbType)SqlDbType.VarChar,50,_PCPOSEntity.DeptCode),
MakeInParam("@PCPOSDN",(DbType)SqlDbType.VarChar,50,_PCPOSEntity.DeptName),
MakeInParam("@PCPOSDEN",(DbType)SqlDbType.VarChar,50,_PCPOSEntity.DeptEName),
MakeInParam("@PCPOSDTWN",(DbType)SqlDbType.VarChar,50,_PCPOSEntity.DeptTWName),
MakeInParam("@PCPOSPDC",(DbType)SqlDbType.VarChar,50,_PCPOSEntity.ParentDeptCode),
MakeInParam("@PCPOSOI",(DbType)SqlDbType.Int,4,_PCPOSEntity.DeptOrderItem),
MakeInParam("@PCPOSUS",(DbType)SqlDbType.Char,1,_PCPOSEntity.DeptIsValid)
};
StringBuilder sb = new StringBuilder();
sb.Append("UpDate [dbo].[PCPOS] set ");
sb.Append("[PCPOSDN]=@PCPOSDN");
sb.Append(",[PCPOSDEN]=@PCPOSDEN");
sb.Append(",[PCPOSDTWN]=@PCPOSDTWN");
sb.Append(",[PCPOSPDC]=@PCPOSPDC");
sb.Append(",[PCPOSOI]=@PCPOSOI");
sb.Append(",[PCPOSUS]=@PCPOSUS");
sb.Append(" where [PCPOSDC]=@PCPOSDC");
sb.Append(" select @PCPOSDC;");
return ExecuteScalar(CommandType.Text, sb.ToString(), pramsUpDate).ToString();
}
}
/// <summary>
/// 根据DataTable获取职位实体的数组
/// </summary>
/// <param name="dataTable"></param>
/// <returns></returns>
public PCPOSEntity[] GetPCPOSEntityArray(DataTable dataTable)
{
int arrayItems = dataTable.Rows.Count;
PCPOSEntity[] deptArray = new PCPOSEntity[arrayItems];
for (int i = 0; i < arrayItems; i++)
{
deptArray[i] = GetPCPOSEntity(dataTable.Rows[i]);
}
return deptArray;
}
/// <summary>
/// 根据DataRow获取职位实体
/// </summary>
/// <param name="dataRow"></param>
/// <returns></returns>
public PCPOSEntity GetPCPOSEntity(DataRow dataRow)
{
PCPOSEntity _PCPOSEntity = new PCPOSEntity();
_PCPOSEntity.DeptCode = dataRow["PCPOSDC"].ToString();
_PCPOSEntity.DeptName = dataRow["PCPOSDN"].ToString();
_PCPOSEntity.DeptEName = dataRow["PCPOSDEN"].ToString();
_PCPOSEntity.DeptTWName = dataRow["PCPOSDTWN"].ToString();
_PCPOSEntity.ParentDeptCode = dataRow["PCPOSPDC"].ToString();
_PCPOSEntity.DeptOrderItem = Convert.ToInt16(dataRow["PCPOSOI"].ToString());
_PCPOSEntity.DeptIsValid = dataRow["PCPOSUS"].ToString();
return _PCPOSEntity;
}
/// <summary>
/// 删除职位信息，以职位实体数组为参数
/// </summary>
/// <param name="_PCPOSEntity"></param>
/// <returns></returns>
public string DeletePCPOS(PCPOSEntity[] _PCPOSEntity)
{
for (int i = 0; i < _PCPOSEntity.Length; i++)
{
DeletePCPOS(_PCPOSEntity[i]);
}
return "0";
}
/// <summary>
/// 删除职位信息，以职位实体为参数
/// </summary>
/// <param name="_PCPOSEntity"></param>
/// <returns></returns>
public string DeletePCPOS(PCPOSEntity _PCPOSEntity)
{
DbParameter[] prams = {
MakeInParam("@PCPOSDC",(DbType)SqlDbType.VarChar,50,_PCPOSEntity.DeptCode),
};
string sql = "";
sql = "delete from [PCPOS] where PCPOSDC = @PCPOSDC ";
return ExecuteNonQuery(CommandType.Text, sql, prams).ToString();
}
