/// <summary>
/// 查询调职申请信息
/// </summary>
/// <param name="model">查询条件</param>
/// <returns></returns>
public static DataTable SearchEmplApplyInfo(EmplApplyModel model, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
//获取登陆用户信息
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//设置公司代码
model.CompanyCD = userInfo.CompanyCD;
//执行查询
return(EmplApplyDBHelper.SearchEmplApplyInfo(model, pageIndex, pageCount, ord, ref TotalCount));
}
/// <summary>
/// 保存时参数设置
/// </summary>
/// <param name="comm">命令</param>
/// <param name="model">人才代理信息</param>
private static void SetSaveParameter(SqlCommand comm, EmplApplyModel model)
{
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); //企业代码
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmplApplyNo", model.EmplApplyNo)); //调职申请编号
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("@NowDeptID", model.NowDeptID)); //部门(对应部门表ID)
comm.Parameters.Add(SqlHelper.GetParameterFromString("@NowQuarterID", model.NowQuarterID)); //岗位(对应岗位表ID)
comm.Parameters.Add(SqlHelper.GetParameterFromString("@NowAdminLevelID", model.NowAdminLevelID)); //岗位职等ID
comm.Parameters.Add(SqlHelper.GetParameterFromString("@NowWage", model.NowWage)); //调职前工资
comm.Parameters.Add(SqlHelper.GetParameterFromString("@NewDeptID", model.NewDeptID)); //调至部门ID
comm.Parameters.Add(SqlHelper.GetParameterFromString("@NewQuarterID", model.NewQuarterID)); //调至岗位ID
comm.Parameters.Add(SqlHelper.GetParameterFromString("@NewAdminLevelID", model.NewAdminLevelID)); //调至岗位职等ID
comm.Parameters.Add(SqlHelper.GetParameterFromString("@NewWage", model.NewWage)); //调职后工资
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ApplyType", model.ApplyType)); //申报类别
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 UpdateEmplApplyInfo(EmplApplyModel model)
{
#region SQL文拼写
StringBuilder updateSql = new StringBuilder();
updateSql.AppendLine(" UPDATE officedba.EmplApply ");
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(" ,NowDeptID = @NowDeptID ");
updateSql.AppendLine(" ,NowQuarterID = @NowQuarterID ");
updateSql.AppendLine(",NowAdminLevelID = @NowAdminLevelID ");
updateSql.AppendLine(" ,NowWage = @NowWage ");
updateSql.AppendLine(" ,NewDeptID = @NewDeptID ");
updateSql.AppendLine(" ,NewQuarterID = @NewQuarterID ");
updateSql.AppendLine(",NewAdminLevelID = @NewAdminLevelID ");
updateSql.AppendLine(" ,NewWage = @NewWage ");
updateSql.AppendLine(" ,ApplyType = @ApplyType ");
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 EmplApplyNo = @EmplApplyNo ");
#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 bool InsertEmplApplyInfo(EmplApplyModel model)
{
#region 登陆SQL文
StringBuilder insertSql = new StringBuilder();
insertSql.AppendLine(" INSERT INTO ");
insertSql.AppendLine(" officedba.EmplApply ");
insertSql.AppendLine(" (CompanyCD ");
insertSql.AppendLine(" ,EmplApplyNo ");
insertSql.AppendLine(" ,Title ");
insertSql.AppendLine(" ,EmployeeID ");
insertSql.AppendLine(" ,EnterDate ");
insertSql.AppendLine(" ,ApplyDate ");
insertSql.AppendLine(" ,HopeDate ");
insertSql.AppendLine(" ,NowDeptID ");
insertSql.AppendLine(" ,NowQuarterID ");
insertSql.AppendLine(" ,NowAdminLevelID ");
insertSql.AppendLine(" ,NowWage ");
insertSql.AppendLine(" ,NewDeptID ");
insertSql.AppendLine(" ,NewQuarterID ");
insertSql.AppendLine(" ,NewAdminLevelID ");
insertSql.AppendLine(" ,NewWage ");
insertSql.AppendLine(" ,ApplyType ");
insertSql.AppendLine(" ,Reason ");
insertSql.AppendLine(" ,Remark ");
insertSql.AppendLine(" ,Status ");
insertSql.AppendLine(" ,ModifiedDate ");
insertSql.AppendLine(" ,ModifiedUserID) ");
insertSql.AppendLine(" VALUES ");
insertSql.AppendLine(" (@CompanyCD ");
insertSql.AppendLine(" ,@EmplApplyNo ");
insertSql.AppendLine(" ,@Title ");
insertSql.AppendLine(" ,@EmployeeID ");
insertSql.AppendLine(" ,@EnterDate ");
insertSql.AppendLine(" ,@ApplyDate ");
insertSql.AppendLine(" ,@HopeDate ");
insertSql.AppendLine(" ,@NowDeptID ");
insertSql.AppendLine(" ,@NowQuarterID ");
insertSql.AppendLine(" ,@NowAdminLevelID ");
insertSql.AppendLine(" ,@NowWage ");
insertSql.AppendLine(" ,@NewDeptID ");
insertSql.AppendLine(" ,@NewQuarterID ");
insertSql.AppendLine(" ,@NewAdminLevelID ");
insertSql.AppendLine(" ,@NewWage ");
insertSql.AppendLine(" ,@ApplyType ");
insertSql.AppendLine(" ,@Reason ");
insertSql.AppendLine(" ,@Remark ");
insertSql.AppendLine(" ,'0' ");
insertSql.AppendLine(" ,getdate() ");
insertSql.AppendLine(" ,@ModifiedUserID) ");
insertSql.AppendLine(" SET @EmplApplyID= @@IDENTITY ");
#endregion
//定义更新基本信息的命令
SqlCommand comm = new SqlCommand();
//设置存储过程名
comm.CommandText = insertSql.ToString();
//设置保存的参数
SetSaveParameter(comm, model);
//添加返回参数
comm.Parameters.Add(SqlHelper.GetOutputParameter("@EmplApplyID", SqlDbType.Int));
//执行登陆操作
bool isSucc = SqlHelper.ExecuteTransWithCommand(comm);
//设置ID
model.ID = comm.Parameters["@EmplApplyID"].Value.ToString();
//执行插入并返回插入结果
return(isSucc);
}
/// <summary>
/// 查询调职申请信息
/// </summary>
/// <param name="model">查询条件</param>
/// <returns></returns>
public static DataTable SearchEmplApplyInfo(EmplApplyModel 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.EmplApplyNo ");
searchSql.AppendLine(" ,A.ModifiedDate ");
searchSql.AppendLine(" ,ISNULL(X.TypeName, '') AS NowAdminLevelName ");
searchSql.AppendLine(" ,ISNULL(Y.TypeName, '') AS NewAdminLevelName ");
searchSql.AppendLine(" ,ISNULL(B.EmployeeName, '') AS EmployeeName ");
searchSql.AppendLine(" ,ISNULL(CONVERT(VARCHAR(10),A.ApplyDate,21),'') AS ApplyDate ");
searchSql.AppendLine(" ,ISNULL(C.DeptName, '') AS NowDeptName ");
searchSql.AppendLine(" ,ISNULL(D.QuarterName, '') AS NowQuarterName ");
searchSql.AppendLine(" ,ISNULL(CONVERT(VARCHAR(10),A.HopeDate,21),'') AS HopeDate ");
searchSql.AppendLine(" ,ISNULL(E.DeptName, '') AS NewDeptName ");
searchSql.AppendLine(" ,ISNULL(F.QuarterName, '') AS NewQuarterName ");
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.EmplApply A ");
searchSql.AppendLine(" LEFT JOIN officedba.CodePublicType X ");
searchSql.AppendLine(" ON A.NowAdminLevelID = X.ID ");
searchSql.AppendLine(" LEFT JOIN officedba.CodePublicType Y ");
searchSql.AppendLine(" ON A.NewAdminLevelID = Y.ID ");
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.NowDeptID = C.ID ");
searchSql.AppendLine(" LEFT JOIN officedba.DeptQuarter D ");
searchSql.AppendLine(" ON A.NowQuarterID = D.ID ");
searchSql.AppendLine(" LEFT JOIN officedba.DeptInfo E ");
searchSql.AppendLine(" ON A.NewDeptID = E.ID ");
searchSql.AppendLine(" LEFT JOIN officedba.DeptQuarter F ");
searchSql.AppendLine(" ON A.NewQuarterID = F.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.EmplApply 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_EMPL_APPLY));
#region 页面查询条件
//申请编号
if (!string.IsNullOrEmpty(model.EmplApplyNo))
{
searchSql.AppendLine(" AND A.EmplApplyNo LIKE '%' + @EmplApplyNo + '%'");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmplApplyNo", model.EmplApplyNo));
}
//申请人
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 SaveEmplApplyInfo(EmplApplyModel model)
{
//获取登陆用户信息
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//设置公司代码
model.CompanyCD = userInfo.CompanyCD;
model.ModifiedUserID = userInfo.UserID;
//定义返回变量
bool isSucc = false;
//操作日志
LogInfoModel logModel = InitLogInfo(model.EmplApplyNo);
//更新
if (model.ID.Trim() != "")
{
try
{
logModel.Element = ConstUtil.LOG_PROCESS_UPDATE;
//执行更新操作
isSucc = EmplApplyDBHelper.UpdateEmplApplyInfo(model);
}
catch (Exception ex)
{
//输出系统日志
WriteSystemLog(userInfo, ex);
}
}
//插入
else
{
try
{
logModel.Element = ConstUtil.LOG_PROCESS_INSERT;
//执行插入操作
isSucc = EmplApplyDBHelper.InsertEmplApplyInfo(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);
}