/// <summary>
/// 更新离职申请信息
/// </summary>
/// <param name="model">离职申请信息</param>
/// <returns></returns>
public static bool UpdateMoveApplyInfo(MoveApplyModel model)
{
#region SQL文拼写
StringBuilder updateSql = new StringBuilder();
updateSql.AppendLine(" UPDATE officedba.MoveApply ");
updateSql.AppendLine(" SET ");
updateSql.AppendLine(" EmployeeID = @EmployeeID ");
updateSql.AppendLine(" ,Title = @Title ");
updateSql.AppendLine(" ,EnterDate = @EnterDate ");
updateSql.AppendLine(" ,ApplyDate = @ApplyDate ");
updateSql.AppendLine(" ,HopeDate = @HopeDate ");
updateSql.AppendLine(" ,DeptID = @DeptID ");
updateSql.AppendLine(" ,QuarterID = @QuarterID ");
updateSql.AppendLine(" ,ContractValid = @ContractValid ");
updateSql.AppendLine(" ,MoveDate = @MoveDate ");
updateSql.AppendLine(" ,MoveType = @MoveType ");
updateSql.AppendLine(" ,Interview = @Interview ");
updateSql.AppendLine(" ,Reason = @Reason ");
updateSql.AppendLine(" ,Remark = @Remark ");
updateSql.AppendLine(" ,ModifiedDate = getdate() ");
updateSql.AppendLine(" ,ModifiedUserID = @ModifiedUserID ");
updateSql.AppendLine(" WHERE ");
updateSql.AppendLine(" CompanyCD = @CompanyCD ");
updateSql.AppendLine(" AND MoveApplyNo = @MoveApplyNo ");
#endregion
//定义更新基本信息的命令
SqlCommand comm = new SqlCommand();
comm.CommandText = updateSql.ToString();
SetSaveParameter(comm, model);//其他参数
//执行更新并设置更新结果
return(SqlHelper.ExecuteTransWithCommand(comm));
}
/// <summary>
/// 查询离职申请信息
/// </summary>
/// <param name="model">查询条件</param>
/// <returns></returns>
public static DataTable SearchMoveApplyInfo(MoveApplyModel model, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
//获取登陆用户信息
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//设置公司代码
model.CompanyCD = userInfo.CompanyCD;
return(MoveApplyDBHelper.SearchMoveApplyInfo(model, pageIndex, pageCount, ord, ref TotalCount));
}
/// <summary>
/// 保存时参数设置
/// </summary>
/// <param name="comm">命令</param>
/// <param name="model">人才代理信息</param>
private static void SetSaveParameter(SqlCommand comm, MoveApplyModel model)
{
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); //企业代码
comm.Parameters.Add(SqlHelper.GetParameterFromString("@MoveApplyNo", model.MoveApplyNo)); //离职申请编号
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Title", model.Title)); //主题
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeID", model.EmployeeID)); //申请人
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EnterDate", model.EnterDate)); //入职时间
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ApplyDate", model.ApplyDate)); //申请日期
comm.Parameters.Add(SqlHelper.GetParameterFromString("@HopeDate", model.HopeDate)); //希望离职日期
comm.Parameters.Add(SqlHelper.GetParameterFromString("@DeptID", model.DeptID)); //部门(对应部门表ID)
comm.Parameters.Add(SqlHelper.GetParameterFromString("@QuarterID", model.QuarterID)); //岗位(对应部门岗位表ID)
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ContractValid", model.ContractValid)); //合同有效期
comm.Parameters.Add(SqlHelper.GetParameterFromString("@MoveDate", model.MoveDate)); //通知离职日期
comm.Parameters.Add(SqlHelper.GetParameterFromString("@MoveType", model.MoveType)); //离职类型(1主动辞职,2辞退,3合同期满离职)
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Interview", model.Interview)); //访谈记录
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Reason", model.Reason)); //事由
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Remark", model.Remark)); //备注
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedUserID", model.ModifiedUserID)); //更新用户ID
}
/// <summary>
/// 新建离职申请信息
/// </summary>
/// <param name="model">离职申请信息</param>
/// <returns></returns>
public static bool InsertMoveApplyInfo(MoveApplyModel model)
{
#region 登陆SQL文
StringBuilder insertSql = new StringBuilder();
insertSql.AppendLine(" INSERT INTO ");
insertSql.AppendLine(" officedba.MoveApply ");
insertSql.AppendLine(" (CompanyCD ");
insertSql.AppendLine(" ,MoveApplyNo ");
insertSql.AppendLine(" ,Title ");
insertSql.AppendLine(" ,EmployeeID ");
insertSql.AppendLine(" ,EnterDate ");
insertSql.AppendLine(" ,ApplyDate ");
insertSql.AppendLine(" ,HopeDate ");
insertSql.AppendLine(" ,DeptID ");
insertSql.AppendLine(" ,QuarterID ");
insertSql.AppendLine(" ,ContractValid ");
insertSql.AppendLine(" ,MoveDate ");
insertSql.AppendLine(" ,MoveType ");
insertSql.AppendLine(" ,Interview ");
insertSql.AppendLine(" ,Reason ");
insertSql.AppendLine(" ,Remark ");
insertSql.AppendLine(" ,Status ");
insertSql.AppendLine(" ,ModifiedDate ");
insertSql.AppendLine(" ,ModifiedUserID) ");
insertSql.AppendLine(" VALUES ");
insertSql.AppendLine(" (@CompanyCD ");
insertSql.AppendLine(" ,@MoveApplyNo ");
insertSql.AppendLine(" ,@Title ");
insertSql.AppendLine(" ,@EmployeeID ");
insertSql.AppendLine(" ,@EnterDate ");
insertSql.AppendLine(" ,@ApplyDate ");
insertSql.AppendLine(" ,@HopeDate ");
insertSql.AppendLine(" ,@DeptID ");
insertSql.AppendLine(" ,@QuarterID ");
insertSql.AppendLine(" ,@ContractValid ");
insertSql.AppendLine(" ,@MoveDate ");
insertSql.AppendLine(" ,@MoveType ");
insertSql.AppendLine(" ,@Interview ");
insertSql.AppendLine(" ,@Reason ");
insertSql.AppendLine(" ,@Remark ");
insertSql.AppendLine(" ,'0' ");
insertSql.AppendLine(" ,getdate() ");
insertSql.AppendLine(" ,@ModifiedUserID) ");
insertSql.AppendLine(" SET @MoveApplyID= @@IDENTITY ");
#endregion
//定义更新基本信息的命令
SqlCommand comm = new SqlCommand();
//设置存储过程名
comm.CommandText = insertSql.ToString();
//设置保存的参数
SetSaveParameter(comm, model);
//添加返回参数
comm.Parameters.Add(SqlHelper.GetOutputParameter("@MoveApplyID", SqlDbType.Int));
//执行登陆操作
bool isSucc = SqlHelper.ExecuteTransWithCommand(comm);
//设置ID
model.ID = comm.Parameters["@MoveApplyID"].Value.ToString();
//执行插入并返回插入结果
return(isSucc);
}
/// <summary>
/// 查询离职申请信息
/// </summary>
/// <param name="model">查询条件</param>
/// <returns></returns>
public static DataTable SearchMoveApplyInfo(MoveApplyModel model, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
#region 查询语句
StringBuilder searchSql = new StringBuilder();
searchSql.AppendLine(" SELECT ");
searchSql.AppendLine(" A.ID ");
searchSql.AppendLine(" ,A.MoveApplyNo ");
searchSql.AppendLine(" ,A.ModifiedDate ");
searchSql.AppendLine(" ,ISNULL(B.EmployeeName, '') AS EmployeeName ");
searchSql.AppendLine(" ,ISNULL(CONVERT(VARCHAR(10),A.ApplyDate,21),'') AS ApplyDate ");
searchSql.AppendLine(" ,ISNULL(C.DeptName, '') AS DeptName ");
searchSql.AppendLine(" ,ISNULL(D.QuarterName, '') AS QuarterName ");
searchSql.AppendLine(" ,ISNULL(CONVERT(VARCHAR(10),A.HopeDate,21),'') AS HopeDate ");
searchSql.AppendLine(" ,CASE h.FlowStatus ");
searchSql.AppendLine(" WHEN '0' THEN '' ");
searchSql.AppendLine(" WHEN '1' THEN '待审批' ");
searchSql.AppendLine(" WHEN '2' THEN '审批中' ");
searchSql.AppendLine(" WHEN '3' THEN '审批通过' ");
searchSql.AppendLine(" WHEN '4' THEN '审批不通过' ");
searchSql.AppendLine(" WHEN '5' THEN '撤销审批' ");
searchSql.AppendLine(" ELSE '' ");
searchSql.AppendLine(" END AS FlowStatusName ");
searchSql.AppendLine(" FROM ");
searchSql.AppendLine(" officedba.MoveApply A ");
searchSql.AppendLine(" LEFT JOIN officedba.EmployeeInfo B ");
searchSql.AppendLine(" ON A.EmployeeID = B.ID ");
searchSql.AppendLine(" LEFT JOIN officedba.DeptInfo C ");
searchSql.AppendLine(" ON A.DeptID = C.ID ");
searchSql.AppendLine(" LEFT JOIN officedba.DeptQuarter D ");
searchSql.AppendLine(" ON A.QuarterID = D.ID ");
searchSql.AppendLine(" LEFT JOIN ( ");
searchSql.AppendLine(" SELECT ");
searchSql.AppendLine(" MAX(E.id) ID,E.BillID,E.BillNo ");
searchSql.AppendLine(" FROM ");
searchSql.AppendLine(" officedba.FlowInstance E,officedba.MoveApply n ");
searchSql.AppendLine(" WHERE ");
searchSql.AppendLine(" E.CompanyCD = n.CompanyCD ");
searchSql.AppendLine(" AND E.BillID = n.ID ");
searchSql.AppendLine(" AND E.BillTypeFlag = @BillTypeFlag ");
searchSql.AppendLine(" AND E.BillTypeCode = @BillTypeCode group by E.BillID,E.BillNo ) g ");
searchSql.AppendLine(" on A.ID=g.BillID ");
searchSql.AppendLine(" LEFT OUTER JOIN officedba.FlowInstance h ON g.ID=h.ID ");
searchSql.AppendLine(" WHERE ");
searchSql.AppendLine(" A.CompanyCD = @CompanyCD ");
#endregion
//定义查询的命令
SqlCommand comm = new SqlCommand();
//公司代码
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
//单据类别标识
comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillTypeFlag", ConstUtil.BILL_TYPEFLAG_HUMAN));
//单据类别编码
comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillTypeCode", ConstUtil.BILL_TYPECODE_HUMAN_MOVE_APPLY));
#region 页面查询条件
//申请编号
if (!string.IsNullOrEmpty(model.MoveApplyNo))
{
searchSql.AppendLine(" AND A.MoveApplyNo LIKE '%' + @MoveApplyNo + '%'");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@MoveApplyNo", model.MoveApplyNo));
}
//申请人
if (!string.IsNullOrEmpty(model.EmployeeID))
{
searchSql.AppendLine(" AND A.EmployeeID = @EmployeeID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeID", model.EmployeeID));
}
//申请日期
if (!string.IsNullOrEmpty(model.ApplyDate))
{
searchSql.AppendLine(" AND A.ApplyDate >= @ApplyDate ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ApplyDate", model.ApplyDate));
}
if (!string.IsNullOrEmpty(model.ApplyToDate))
{
searchSql.AppendLine(" AND A.ApplyDate <= @ApplyToDate ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ApplyToDate", model.ApplyToDate));
}
//审批状态
if (!string.IsNullOrEmpty(model.FlowStatusID))
{
//待提交时
if ("0".Equals(model.FlowStatusID))
{
searchSql.AppendLine(" AND h.FlowStatus IS NULL ");
}
else
{
searchSql.AppendLine(" AND h.FlowStatus = @FlowStatus ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@FlowStatus", model.FlowStatusID));
}
}
#endregion
//指定命令的SQL文
comm.CommandText = searchSql.ToString();
//执行查询
return(SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, ord, ref TotalCount));
//return SqlHelper.ExecuteSearch(comm);
}
/// <summary>
/// 编辑离职申请信息
/// </summary>
/// <param name="model">离职申请信息</param>
/// <returns></returns>
public static bool SaveMoveApplyInfo(MoveApplyModel model)
{
//获取登陆用户信息
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//设置公司代码
model.CompanyCD = userInfo.CompanyCD;
model.ModifiedUserID = userInfo.UserID;
//定义返回变量
bool isSucc = false;
//操作日志
LogInfoModel logModel = InitLogInfo(model.MoveApplyNo);
//更新
if (model.ID.Trim() != "")
{
try
{
logModel.Element = ConstUtil.LOG_PROCESS_UPDATE;
//执行更新操作
isSucc = MoveApplyDBHelper.UpdateMoveApplyInfo(model);
}
catch (Exception ex)
{
//输出系统日志
WriteSystemLog(userInfo, ex);
}
}
//插入
else
{
try
{
logModel.Element = ConstUtil.LOG_PROCESS_INSERT;
//执行插入操作
isSucc = MoveApplyDBHelper.InsertMoveApplyInfo(model);
}
catch (Exception ex)
{
//输出系统日志
WriteSystemLog(userInfo, ex);
}
}
//更新成功时
if (isSucc)
{
//设置操作成功标识
logModel.Remark = ConstUtil.LOG_PROCESS_SUCCESS;
}
//更新不成功
else
{
//设置操作成功标识
logModel.Remark = ConstUtil.LOG_PROCESS_FAILED;
}
//登陆日志
LogDBHelper.InsertLog(logModel);
return(isSucc);
}