/// <summary>
/// 更新招聘申请信息
/// </summary>
/// <param name="model">招聘申请信息</param>
/// <returns></returns>
public static RectApplyModel UpdateRectApplyInfo(RectApplyModel model)
{
#region SQL文拼写
StringBuilder updateSql = new StringBuilder();
updateSql.AppendLine(" UPDATE officedba.RectApply ");
updateSql.AppendLine(" SET DeptID = @DeptID ");
updateSql.AppendLine(" ,MaxNum=@MaxNum ");
updateSql.AppendLine(" ,NowNum=@NowNum ");
updateSql.AppendLine(" ,RequireNum=@RequireNum ");
updateSql.AppendLine(" ,Principal=@Principal ");
updateSql.AppendLine(" ,RequstReason=@RequstReason ");
updateSql.AppendLine(" ,Remark=@Remark ");
updateSql.AppendLine(" ,ModifiedDate = getdate() ");
updateSql.AppendLine(" ,ModifiedUserID = @ModifiedUserID ");
updateSql.AppendLine(" WHERE CompanyCD = @CompanyCD ");
updateSql.AppendLine(" AND RectApplyNo = @RectApplyNo ");
#endregion
//定义更新基本信息的命令
SqlCommand comm = new SqlCommand();
comm.CommandText = updateSql.ToString();
SetSaveParameter(comm, model);//其他参数
//执行更新并设置更新结果
bool sign = SqlHelper.ExecuteTransWithCommand(comm);
if (sign)
{
if (InsertRectApplyDetailsInfo(model.GoalList, model.RectApplyNo, model.CompanyCD))
{
model.IsSuccess = true;
}
else
{
model.IsSuccess = false;
}
}
else
{
model.IsSuccess = false;
}
return model;
}
/// <summary>
/// 保存时参数设置
/// </summary>
/// <param name="comm">命令</param>
/// <param name="model">人才代理信息</param>
private static void SetSaveParameter(SqlCommand comm, RectApplyModel model)
{
comm.Parameters.Add(SqlHelper.GetParameterFromString("@RectApplyNo", model.RectApplyNo));//招聘申请编号
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));//企业代码
comm.Parameters.Add(SqlHelper.GetParameterFromString("@DeptID", model.DeptID));//申请部门
comm.Parameters.Add(SqlHelper.GetParameterFromString("@MaxNum", model.MaxNum ));//直接主管(对应员工表ID)
comm.Parameters.Add(SqlHelper.GetParameterFromString("@NowNum", model.NowNum ));//职位名称
comm.Parameters.Add(SqlHelper.GetParameterFromString("@RequireNum", model.RequireNum ));//工作地点
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Principal", model.Principal ));//申请人(对应员工表ID)
comm.Parameters.Add(SqlHelper.GetParameterFromString("@RequstReason", model.RequstReason ));//申请日期
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Remark", model.Remark ));//工作要求
comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillStatus","1"));//其他能力
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Creator", model.Creator ));//备注
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedUserID", model.ModifiedUserID));//备注
}
/// <summary>
/// 新建招聘申请信息
/// </summary>
/// <param name="model">招聘申请信息</param>
/// <returns></returns>
public static RectApplyModel InsertRectApplyInfo(RectApplyModel model)
{
#region 登陆SQL文
StringBuilder insertSql = new StringBuilder();
insertSql.AppendLine(" INSERT INTO officedba.RectApply ");
insertSql.AppendLine(" (RectApplyNo ");
insertSql.AppendLine(" ,CompanyCD ");
insertSql.AppendLine(" ,DeptID ");
insertSql.AppendLine(" ,MaxNum ");
insertSql.AppendLine(" ,NowNum ");
insertSql.AppendLine(" ,RequireNum ");
insertSql.AppendLine(" ,Principal ");
insertSql.AppendLine(" ,RequstReason ");
insertSql.AppendLine(" ,Remark ");
insertSql.AppendLine(" ,BillStatus ");
insertSql.AppendLine(" ,Creator ");
insertSql.AppendLine(" ,CreateDate ) ");
insertSql.AppendLine(" VALUES ");
insertSql.AppendLine(" (@RectApplyNo ");
insertSql.AppendLine(" ,@CompanyCD ");
insertSql.AppendLine(" ,@DeptID ");
insertSql.AppendLine(" ,@MaxNum ");
insertSql.AppendLine(" ,@NowNum ");
insertSql.AppendLine(" ,@RequireNum ");
insertSql.AppendLine(" ,@Principal ");
insertSql.AppendLine(" ,@RequstReason ");
insertSql.AppendLine(" ,@Remark ");
insertSql.AppendLine(" ,@BillStatus ");
insertSql.AppendLine(" ,@Creator ");
insertSql.AppendLine(" ,getdate() ) ");
insertSql.AppendLine(" SET @RectApplyID= @@IDENTITY ");
#endregion
//定义更新基本信息的命令
SqlCommand comm = new SqlCommand();
//设置存储过程名
comm.CommandText = insertSql.ToString();
//设置保存的参数
SetSaveParameter(comm, model);
//添加返回参数
comm.Parameters.Add(SqlHelper.GetOutputParameter("@RectApplyID", SqlDbType.Int));
//执行登陆操作
bool sign = SqlHelper.ExecuteTransWithCommand(comm);
if (sign)
{
if (InsertRectApplyDetailsInfo(model.GoalList, model.RectApplyNo, model.CompanyCD))
{
model.IsSuccess = true;
}
else
{
model.IsSuccess = false;
}
}
else
{
model.IsSuccess = false ;
}
//设置ID
model.ID = comm.Parameters["@RectApplyID"].Value.ToString();
//执行插入并返回插入结果
return model;
}
/// <summary>
/// 查询招聘申请信息
/// </summary>
/// <param name="model">查询条件</param>
/// <returns></returns>
public static DataTable SearchRectApplyInfo(RectApplyModel 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.RectApplyNo ");
searchSql.AppendLine(" ,CONVERT(VARCHAR(10),A.CreateDate,21) AS CreateDate ");
searchSql.AppendLine(" ,ISNULL(B.DeptName, '') AS DeptName ");
searchSql.AppendLine(" ,isnull(cast (A.MaxNum as varchar),'') as MaxNum ");
searchSql.AppendLine(" ,isnull(cast (A.RequireNum as varchar),'') as RequireNum ");
searchSql.AppendLine(" ,isnull(cast (A.NowNum as varchar),'') as NowNum ");
searchSql.AppendLine(" ,ISNULL(C.EmployeeName, '') AS PrincipalName ");
searchSql.AppendLine(" ,CASE A.BillStatus ");
searchSql.AppendLine(" WHEN '1' THEN '制单' ");
searchSql.AppendLine(" WHEN '2' THEN '执行' ");
searchSql.AppendLine(" WHEN '4' THEN '手工结单' ");
searchSql.AppendLine(" WHEN '5' THEN '自动结单' ");
searchSql.AppendLine(" END BillStatus ");
searchSql.AppendLine(" ,CASE h.FlowStatus ");
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 ,isnull( Convert(varchar(100),A.ModifiedDate,23),'') AS ModifiedDate ");
searchSql.AppendLine(" FROM ");
searchSql.AppendLine(" officedba.RectApply A ");
searchSql.AppendLine(" LEFT JOIN officedba.DeptInfo B ");
searchSql.AppendLine(" ON B.companyCD=A.companyCD AND A.DeptID = B.ID ");
searchSql.AppendLine(" LEFT JOIN officedba.EmployeeInfo C ");
searchSql.AppendLine(" ON C.companyCD=A.companyCD AND A.Principal = C.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.RectApply 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_RECT_APPLY));
//申请编号
if (!string.IsNullOrEmpty(model.RectApplyNo))
{
searchSql.AppendLine(" AND A.RectApplyNo LIKE @RectApplyNo ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@RectApplyNo", "%" + model.RectApplyNo + "%"));
}
//申请部门
if (!string.IsNullOrEmpty(model.DeptID))
{
searchSql.AppendLine(" AND A.DeptID = @DeptID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@DeptID", model.DeptID));
}
////职位名称
//if (!string.IsNullOrEmpty(model.JobName))
//{
// searchSql.AppendLine(" AND k.JobName LIKE @JobName ");
// comm.Parameters.Add(SqlHelper.GetParameterFromString("@JobName", "%" + model.JobName + "%"));
//}
//申请日期
if (!string.IsNullOrEmpty(model.UsedDate))
{
searchSql.AppendLine(" AND A.CreateDate >= @CreateDate ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CreateDate", model.UsedDate));
}
//申请日期
if (!string.IsNullOrEmpty(model.BillStatus))
{
searchSql.AppendLine(" AND A.BillStatus= @BillStatus ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillStatus", model.BillStatus));
}
//审批状态
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));
}
}
//指定命令的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 RectApplyModel SaveRectApplyInfo(RectApplyModel model)
{
//获取登陆用户信息
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//设置公司代码
model.CompanyCD = userInfo.CompanyCD;
model.ModifiedUserID = userInfo.UserID;
//操作日志
LogInfoModel logModel = InitLogInfo(model.RectApplyNo);
//更新
if (ConstUtil.EDIT_FLAG_UPDATE.Equals(model.EditFlag))
{
try
{
logModel.Element = ConstUtil.LOG_PROCESS_UPDATE;
//执行更新操作
model = RectApplyDBHelper.UpdateRectApplyInfo(model);
}
catch (Exception ex)
{
//输出系统日志
WriteSystemLog(userInfo, ex);
}
}
//插入
else
{
try
{
logModel.Element = ConstUtil.LOG_PROCESS_INSERT;
//执行插入操作
model = RectApplyDBHelper.InsertRectApplyInfo(model);
}
catch (Exception ex)
{
//输出系统日志
WriteSystemLog(userInfo, ex);
}
}
//更新成功时
if (model.IsSuccess)
{
//设置操作成功标识
logModel.Remark = ConstUtil.LOG_PROCESS_SUCCESS;
}
//更新不成功
else
{
//设置操作成功标识
logModel.Remark = ConstUtil.LOG_PROCESS_FAILED;
}
//登陆日志
LogDBHelper.InsertLog(logModel);
return model;
}
/// <summary>
/// 查询招聘申请信息
/// </summary>
/// <param name="model">查询条件</param>
/// <returns></returns>
public static DataTable SearchRectApplyInfo(RectApplyModel model,int pageIndex,int pageCount,string ord, ref int TotalCount)
{
//获取登陆用户信息
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//设置公司代码
model.CompanyCD = userInfo.CompanyCD;
return RectApplyDBHelper.SearchRectApplyInfo(model, pageIndex, pageCount, ord, ref TotalCount);
}
protected void btnImport_Click(object sender, System.Web.UI.ImageClickEventArgs e)
{
//从请求中获取排序列
string orderString = hidOrderBy.Value .Trim ();
//排序：默认为升序
string orderBy = "asc";
//要排序的字段，如果为空，默认为"RectApplyNo"
string orderByCol = (!string.IsNullOrEmpty(orderString)) ? orderString.Substring(0, orderString.Length - 2) : "RectApplyNo";
//降序时如果设置为降序
if (orderString.EndsWith("_d"))
{
//排序：降序
orderBy = "desc";
}
//从请求中获取当前页
//int pageIndex =Convert .ToInt32 ( txtToPage.Value);
////从请求中获取每页显示记录数
//int pageCount = Convert.ToInt32(txtShowPageCount.Value);
////跳过记录数
//int skipRecord = (pageIndex - 1) * pageCount;
//获取数据
RectApplyModel searchModel = new RectApplyModel();
//设置查询条件
searchModel.RectApplyNo = txtRectApplyNo.Text.Trim();
searchModel.DeptID = hidDeptID.Value;//申请部门
//申请日期
//searchModel.UsedDate = request.QueryString["ApplyDate"];
//searchModel.JobName = request.QueryString["JobName"];//职位名称
searchModel.FlowStatusID = Request.Form["ddlFlowStatus"].ToString();
searchModel.BillStatus = Request.Form["DropDownList1"].ToString();
string ord = orderByCol + " " + orderBy;
int TotalCount = 0;
//查询数据
DataTable dtRectApply = new DataTable();
if (!string.IsNullOrEmpty(txtToPage.Value))
{
dtRectApply = RectApplyBus.SearchRectApplyInfo(searchModel, 1, 10000, ord, ref TotalCount);//查询数据
}
string[,] ht = {
{ "申请编号", "RectApplyNo"},
{ "制单时间 ", "CreateDate"},
{ "申请部门 ", "DeptName" },
{ "编制定额 ", "MaxNum" },
{ "现有人数 ", "NowNum"},
{ "总需求人数", "RequireNum"},
{ "审批状态","FlowStatusName"}
};
ExportExcel(dtRectApply, ht, "", "招聘申请列表");
}