protected void btnImport_Click(object sender, ImageClickEventArgs e) { //从请求中获取排序列 string orderString = hidOrderBy.Value.Trim(); //排序:默认为升序 string orderBy = "asc"; //要排序的字段,如果为空,默认为"RectApplyNo" string orderByCol = (!string.IsNullOrEmpty(orderString)) ? orderString.Substring(0, orderString.Length - 2) : "InterviewNo"; //降序时如果设置为降序 if (orderString.EndsWith("_d")) { //排序:降序 orderBy = "desc"; } //从请求中获取当前页 //int pageIndex = Convert.ToInt32(txtToPage.Value); ////从请求中获取每页显示记录数 //int pageCount = Convert.ToInt32(txtShowPageCount.Value); ////跳过记录数 //int skipRecord = (pageIndex - 1) * pageCount; //获取数据 RectInterviewModel searchModel = new RectInterviewModel(); //设置查询条件 //编号 searchModel.InterviewNo = txtRectInterviewNo.Text.Trim(); //面试日期 searchModel.InterviewDate = txtInterviewDate.Text.Trim(); searchModel.Attachment = txtInterviewToDate.Text.Trim(); searchModel.InterviewResult = Request.Form["ddlInterviewResult"].ToString(); //应聘岗位 searchModel.QuarterID = Request.Form["ddlQuarter"].ToString(); //姓名 searchModel.StaffName = UserTxtStaffName.Text.Trim(); //人力资源 searchModel.CheckDate = txtCheckStartDate.Text.Trim(); ////部门主管 searchModel.CheckNote = txtCheckEndDate.Text.Trim(); //最终结果 searchModel.FinalResult = Request.Form["ddlFinalResult"].ToString(); searchModel.PlanID = Request.Form["ddlRectPlan"].ToString(); searchModel.RectType = Request.Form["ddlRectType"].ToString(); int aaa = searchModel.InterviewNo.IndexOf('%');///过滤字符串 if (aaa != -1) { searchModel.InterviewNo = searchModel.InterviewNo.Replace('%', ' '); } if (searchModel.StaffName != null) { int bbb = searchModel.StaffName.IndexOf('%');///过滤字符串 if (bbb != -1) { searchModel.StaffName = searchModel.StaffName.Replace('%', ' '); } } string ord = orderByCol + " " + orderBy; int TotalCount = 0; //查询数据 DataTable dtRectApply = new DataTable(); if (!string.IsNullOrEmpty(txtToPage.Value)) { dtRectApply = RectInterviewBus.SearchInterviewCSInfo(searchModel, 1, 10000, ord, ref TotalCount); } string[,] ht = { { "面试编号", "InterviewNo"}, { "对应招聘计划", "PlanName"}, { "招聘方式", "RectType" }, { "姓名", "StaffName" }, { "应聘岗位", "QuarterName"}, { "初试日期", "InterviewDate"}, { "初试结果","InterviewResult"}, { "复试日期","CheckDate"}, { "复试结果","FinalResult"} }; ExportExcel(dtRectApply, ht, "", "面试记录列表"); }
/// <summary> /// 更新面试记录信息 /// </summary> /// <param name="model">面试记录信息</param> /// <returns></returns> public static bool UpdateInterviewInfo(RectInterviewModel model) { #region SQL文拼写 StringBuilder updateSql = new StringBuilder(); updateSql.AppendLine(" UPDATE officedba.RectInterview "); updateSql.AppendLine(" SET "); updateSql.AppendLine(" PlanID = @PlanID "); updateSql.AppendLine(" ,InterviewDate=@InterviewDate "); updateSql.AppendLine(" ,StaffName=@StaffName "); updateSql.AppendLine(" ,QuarterID=@QuarterID "); updateSql.AppendLine(" ,RectType=@RectType "); updateSql.AppendLine(" ,TemplateNo=@TemplateNo "); updateSql.AppendLine(" ,InterviewType=@InterviewType "); updateSql.AppendLine(" ,InterviewPlace=@InterviewPlace "); updateSql.AppendLine(" ,InterviewNote=@InterviewNote "); updateSql.AppendLine(" ,TestScore=@TestScore "); updateSql.AppendLine(" ,InterviewResult=@InterviewResult "); updateSql.AppendLine(" ,CheckDate=@CheckDate "); updateSql.AppendLine(" ,CheckType=@CheckType "); updateSql.AppendLine(" ,CheckPlace=@CheckPlace "); updateSql.AppendLine(" ,CheckNote=@CheckNote "); updateSql.AppendLine(" ,OurSalary =@OurSalary "); updateSql.AppendLine(" ,FinalSalary=@FinalSalary "); updateSql.AppendLine(" ,OtherNote=@OtherNote "); updateSql.AppendLine(" ,ManNote=@ManNote "); updateSql.AppendLine(" ,KnowNote=@KnowNote "); updateSql.AppendLine(" ,WorkNote=@WorkNote "); updateSql.AppendLine(" ,SalaryNote=@SalaryNote "); updateSql.AppendLine(" ,FinalResult=@FinalResult "); updateSql.AppendLine(" ,Remark=@Remark "); updateSql.AppendLine(" ,Attachment=@Attachment "); updateSql.AppendLine(" ,ModifiedDate=getdate() "); updateSql.AppendLine(" ,ModifiedUserID=@ModifiedUserID "); updateSql.AppendLine(" ,AttachmentName=@AttachmentName "); updateSql.AppendLine(" WHERE "); updateSql.AppendLine(" CompanyCD = @CompanyCD "); updateSql.AppendLine(" AND InterviewNo = @InterviewNo "); #endregion //定义更新基本信息的命令 SqlCommand comm = new SqlCommand(); comm.CommandText = updateSql.ToString(); //添加参数 SetSaveParameter(comm, model); //定义变量 ArrayList lstUpdate = new ArrayList(); lstUpdate.Add(comm); //添加要素得分 EditElemScoreInfo(lstUpdate, model.ElemScoreList, model.InterviewNo, model.CompanyCD); //执行更新并设置更新结果 return SqlHelper.ExecuteTransWithArrayList(lstUpdate); }
/// <summary> /// 保存时参数设置 /// </summary> /// <param name="comm">命令</param> /// <param name="model">人才代理信息</param> private static void SetSaveParameter(SqlCommand comm, RectInterviewModel model) { comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));//企业代码 comm.Parameters.Add(SqlHelper.GetParameterFromString("@PlanID", model.PlanID));//招聘计划ID comm.Parameters.Add(SqlHelper.GetParameterFromString("@InterviewNo", model.InterviewNo));//面试记录编号 comm.Parameters.Add(SqlHelper.GetParameterFromString("@InterviewDate", model.InterviewDate));//面试日期 comm.Parameters.Add(SqlHelper.GetParameterFromString("@StaffName", model.StaffName));//面试者 comm.Parameters.Add(SqlHelper.GetParameterFromString("@QuarterID", model.QuarterID));//应聘岗位ID comm.Parameters.Add(SqlHelper.GetParameterFromString("@TemplateNo", model.TemplateNo));//消息渠道 comm.Parameters.Add(SqlHelper.GetParameterFromString("@RectType", model.RectType ));//消息渠道 comm.Parameters.Add(SqlHelper.GetParameterFromString("@InterviewType", model.InterviewType ));//期望最高月薪 comm.Parameters.Add(SqlHelper.GetParameterFromString("@InterviewPlace", model.InterviewPlace ));//面试方式(对应系统分类代码表ID) comm.Parameters.Add(SqlHelper.GetParameterFromString("@InterviewNote", model.InterviewNote ));//面试总成绩 comm.Parameters.Add(SqlHelper.GetParameterFromString("@TestScore", model.TestScore ));//面试地点 comm.Parameters.Add(SqlHelper.GetParameterFromString("@InterviewResult", model.InterviewResult ));//面试状况(0未面试,1已面试) comm.Parameters.Add(SqlHelper.GetParameterFromString("@CheckDate", model.CheckDate ));//录用状况(0未录用,1已录用,2不录用) comm.Parameters.Add(SqlHelper.GetParameterFromString("@CheckType", model.CheckType ));//通知单状况(0未通知,1已通知,2不通知) comm.Parameters.Add(SqlHelper.GetParameterFromString("@CheckPlace", model.CheckPlace ));//试用工资 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CheckNote", model.CheckNote ));//登记日期 comm.Parameters.Add(SqlHelper.GetParameterFromString("@ManNote", model.ManNote ));//截止日期 comm.Parameters.Add(SqlHelper.GetParameterFromString("@KnowNote", model.KnowNote ));//面试记录 comm.Parameters.Add(SqlHelper.GetParameterFromString("@WorkNote", model.WorkNote ));//面试人 comm.Parameters.Add(SqlHelper.GetParameterFromString("@SalaryNote", model.SalaryNote ));//人力资源结果(0拒绝、1同意) comm.Parameters.Add(SqlHelper.GetParameterFromString("@FinalResult", model.FinalResult ));//部门主管结果(0拒绝、1同意) comm.Parameters.Add(SqlHelper.GetParameterFromString("@Remark", model.Remark ));//最终结果(0拒绝、1同意) comm.Parameters.Add(SqlHelper.GetParameterFromString("@Attachment", model.PageAttachment ));//备注 comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedUserID", model.ModifiedUserID ));//附件 comm.Parameters.Add(SqlHelper.GetParameterFromString("@AttachmentName", model.AttachmentName));//附件 comm.Parameters.Add(SqlHelper.GetParameterFromString("@OurSalary", model.OurSalary));//备注 comm.Parameters.Add(SqlHelper.GetParameterFromString("@FinalSalary", model.FinalSalary));//附件 comm.Parameters.Add(SqlHelper.GetParameterFromString("@OtherNote", model.OtherNote));//附件 }
/// <summary> /// 新建面试记录信息 /// </summary> /// <param name="model">面试记录信息</param> /// <returns></returns> public static bool InsertInterviewInfo(RectInterviewModel model) { #region 登陆SQL文 StringBuilder insertSql = new StringBuilder(); insertSql.AppendLine(" INSERT INTO officedba.RectInterview "); insertSql.AppendLine(" (CompanyCD "); insertSql.AppendLine(" ,PlanID "); insertSql.AppendLine(" ,InterviewNo "); insertSql.AppendLine(" ,InterviewDate "); insertSql.AppendLine(" ,StaffName "); insertSql.AppendLine(" ,QuarterID "); insertSql.AppendLine(" ,RectType "); insertSql.AppendLine(" ,TemplateNo "); insertSql.AppendLine(" ,InterviewType "); insertSql.AppendLine(" ,InterviewPlace "); insertSql.AppendLine(" ,InterviewNote "); insertSql.AppendLine(" ,TestScore "); insertSql.AppendLine(" ,InterviewResult "); insertSql.AppendLine(" ,CheckDate "); insertSql.AppendLine(" ,CheckType "); insertSql.AppendLine(" ,CheckPlace "); insertSql.AppendLine(" ,CheckNote "); insertSql.AppendLine(" ,ManNote "); insertSql.AppendLine(" ,KnowNote "); insertSql.AppendLine(" ,WorkNote "); insertSql.AppendLine(" ,SalaryNote "); insertSql.AppendLine(" ,FinalResult "); insertSql.AppendLine(" ,Remark "); insertSql.AppendLine(" ,Attachment "); insertSql.AppendLine(" ,ModifiedDate "); insertSql.AppendLine(" ,ModifiedUserID "); insertSql.AppendLine(" ,OurSalary "); insertSql.AppendLine(" ,FinalSalary "); insertSql.AppendLine(" ,OtherNote "); insertSql.AppendLine(" ,AttachmentName ) "); insertSql.AppendLine(" VALUES "); insertSql.AppendLine(" (@CompanyCD "); insertSql.AppendLine(" ,@PlanID "); insertSql.AppendLine(" ,@InterviewNo "); insertSql.AppendLine(" ,@InterviewDate "); insertSql.AppendLine(" ,@StaffName "); insertSql.AppendLine(" ,@QuarterID "); insertSql.AppendLine(" ,@RectType "); insertSql.AppendLine(" ,@TemplateNo "); insertSql.AppendLine(" ,@InterviewType "); insertSql.AppendLine(" ,@InterviewPlace "); insertSql.AppendLine(" ,@InterviewNote "); insertSql.AppendLine(" ,@TestScore "); insertSql.AppendLine(" ,@InterviewResult "); insertSql.AppendLine(" ,@CheckDate "); insertSql.AppendLine(" ,@CheckType "); insertSql.AppendLine(" ,@CheckPlace "); insertSql.AppendLine(" ,@CheckNote "); insertSql.AppendLine(" ,@ManNote "); insertSql.AppendLine(" ,@KnowNote "); insertSql.AppendLine(" ,@WorkNote "); insertSql.AppendLine(" ,@SalaryNote "); insertSql.AppendLine(" ,@FinalResult "); insertSql.AppendLine(" ,@Remark "); insertSql.AppendLine(" ,@Attachment "); insertSql.AppendLine(" ,getdate() "); insertSql.AppendLine(" ,@ModifiedUserID "); insertSql.AppendLine(" ,@OurSalary "); insertSql.AppendLine(" ,@FinalSalary "); insertSql.AppendLine(" ,@OtherNote "); insertSql.AppendLine(" ,@AttachmentName ) "); insertSql.AppendLine(" SET @InterviewID= @@IDENTITY "); #endregion //定义更新基本信息的命令 SqlCommand comm = new SqlCommand(); //设置存储过程名 comm.CommandText = insertSql.ToString(); //设置保存的参数 SetSaveParameter(comm, model); //添加返回参数 comm.Parameters.Add(SqlHelper.GetOutputParameter("@InterviewID", SqlDbType.Int)); ArrayList lstInsert = new ArrayList(); lstInsert.Add(comm); //添加要素得分 EditElemScoreInfo(lstInsert, model.ElemScoreList, model.InterviewNo, model.CompanyCD); //执行登陆操作 bool isSuccess = SqlHelper.ExecuteTransWithArrayList(lstInsert); //获取插入后的命令 SqlCommand commID = (SqlCommand)lstInsert[0]; //设置ID model.ID = commID.Parameters["@InterviewID"].Value.ToString(); //执行插入并返回插入结果 return isSuccess; }
/// <summary> /// 查询面试记录信息 /// </summary> /// <param name="model">查询条件</param> /// <returns></returns> /// public static DataTable SearchInterviewInfo(RectInterviewModel model) { #region 查询语句 StringBuilder searchSql = new StringBuilder(); searchSql.AppendLine(" SELECT "); searchSql.AppendLine(" A.ID "); searchSql.AppendLine(" ,A.InterviewNo "); searchSql.AppendLine(" ,ISNULL(CONVERT(VARCHAR(10),A.InterviewDate,21),'') "); searchSql.AppendLine(" AS InterviewDate "); searchSql.AppendLine(" ,ISNULL(CONVERT(VARCHAR(10),A.CheckDate,21),'') "); searchSql.AppendLine(" AS CheckDate "); searchSql.AppendLine(" ,ISNULL(C.EmployeeName, '') AS StaffName "); searchSql.AppendLine(" ,ISNULL(B.QuarterName, '') AS QuarterName "); searchSql.AppendLine(" ,isnull(d.Title,'') as PlanName "); searchSql.AppendLine(" ,CASE A.InterviewResult WHEN '1' THEN '列入考虑' WHEN '2' THEN '不予考虑' ELSE '' END AS InterviewResult "); searchSql.AppendLine(" ,CASE A.FinalResult WHEN '0' THEN '不予考虑' WHEN '1' THEN '拟予试用' ELSE '' END AS FinalResult "); searchSql.AppendLine(" ,CASE A.RectType WHEN '1' THEN '公开招聘' WHEN '2' THEN '推荐' WHEN '3' THEN '内部竞聘' ELSE '' END AS RectType ,isnull( Convert(varchar(100),A.ModifiedDate,23),'') AS ModifiedDate "); searchSql.AppendLine(" FROM "); searchSql.AppendLine(" officedba.RectInterview A "); searchSql.AppendLine(" LEFT JOIN officedba.DeptQuarter B "); searchSql.AppendLine(" ON B.companyCD=A.companyCD AND A.QuarterID = B.ID "); searchSql.AppendLine(" LEFT JOIN officedba.EmployeeInfo C "); searchSql.AppendLine(" ON C.companyCD=A.companyCD AND A.StaffName = C.ID "); searchSql.AppendLine(" left outer join officedba.RectPlan D "); searchSql.AppendLine(" on D.CompanyCD=A.CompanyCD and a.planID=D.PlanNo "); searchSql.AppendLine(" WHERE "); searchSql.AppendLine(" A.CompanyCD = @CompanyCD "); #endregion //定义查询的命令 SqlCommand comm = new SqlCommand(); //公司代码 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); //编号 if (!string.IsNullOrEmpty(model.InterviewNo)) { searchSql.AppendLine(" AND A.InterviewNo LIKE '%' + @InterviewNo + '%'"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@InterviewNo", model.InterviewNo)); } //初试日期 if (!string.IsNullOrEmpty(model.InterviewDate)) { searchSql.AppendLine(" AND A.InterviewDate >= @InterviewDate "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@InterviewDate", model.InterviewDate)); } if (!string.IsNullOrEmpty(model.Attachment)) { searchSql.AppendLine(" AND A.InterviewDate <= @InterviewToDate "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@InterviewToDate", model.Attachment)); } if (!string.IsNullOrEmpty(model.PlanID)) { searchSql.AppendLine(" AND A.PlanID = @PlanID "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@PlanID", model.PlanID)); } if (!string.IsNullOrEmpty(model.RectType)) { searchSql.AppendLine(" AND A.RectType= @RectType "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@RectType", model.RectType)); } //复试日期 if (!string.IsNullOrEmpty(model.CheckDate)) { searchSql.AppendLine(" AND A.CheckDate >= @CheckDate "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@CheckDate", model.CheckDate)); } if (!string.IsNullOrEmpty(model.CheckNote)) { searchSql.AppendLine(" AND A.CheckDate <= @CheckNote "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@CheckNote", model.CheckNote)); } //应聘岗位 if (!string.IsNullOrEmpty(model.QuarterID)) { searchSql.AppendLine(" AND A.QuarterID = @QuarterID "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@QuarterID", model.QuarterID)); } //姓名 if (!string.IsNullOrEmpty(model.StaffName)) { searchSql.AppendLine(" AND C.EmployeeName LIKE '%' + @StaffName + '%'"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@StaffName",model.StaffName)); } //////人力资源 if (!string.IsNullOrEmpty(model.InterviewResult)) { searchSql.AppendLine(" AND A.InterviewResult = @InterviewResult "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@InterviewResult", model.InterviewResult)); } //部门主管 //if (!string.IsNullOrEmpty(model.DepartmentResult)) //{ // searchSql.AppendLine(" AND A.DepartmentResult = @DepartmentResult "); // comm.Parameters.Add(SqlHelper.GetParameterFromString("@DepartmentResult", model.DepartmentResult)); //} //最终结果 if (!string.IsNullOrEmpty(model.FinalResult)) { searchSql.AppendLine(" AND A.FinalResult = @FinalResult "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@FinalResult", model.FinalResult)); } //指定命令的SQL文 comm.CommandText = searchSql.ToString(); //执行查询 return SqlHelper.ExecuteSearch(comm); }
public static DataTable SearchInterviewCSInfo(RectInterviewModel model, int pageIndex, int pageCount, string ord, ref int TotalCount) { //获取登陆用户信息 UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; //设置公司代码 model.CompanyCD = userInfo.CompanyCD; return RectInterviewDBHelper.SearchInterviewCSInfo(model, pageIndex,pageCount ,ord ,ref TotalCount ); }
/// <summary> /// 查询面试记录信息 /// </summary> /// <param name="model">查询条件</param> /// <returns></returns> public static DataTable SearchInterviewInfo(RectInterviewModel model) { //获取登陆用户信息 UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; //设置公司代码 model.CompanyCD = userInfo.CompanyCD; return RectInterviewDBHelper.SearchInterviewInfo(model); }
/// <summary> /// 编辑面试记录信息 /// </summary> /// <param name="model">面试记录信息</param> /// <returns></returns> public static bool SaveInterviewInfo(RectInterviewModel model) { //获取登陆用户信息 UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; //设置公司代码 model.CompanyCD = userInfo.CompanyCD; model.ModifiedUserID = userInfo.UserID; //定义返回变量 bool isSucc = false; //操作日志 LogInfoModel logModel = InitLogInfo(model.InterviewNo); //更新 if (!string.IsNullOrEmpty(model.ID)) { try { logModel.Element = ConstUtil.LOG_PROCESS_UPDATE; //执行更新操作 isSucc = RectInterviewDBHelper.UpdateInterviewInfo(model); } catch (Exception ex) { //输出系统日志 WriteSystemLog(userInfo, ex); } } //插入 else { try { logModel.Element = ConstUtil.LOG_PROCESS_INSERT; //执行插入操作 isSucc = RectInterviewDBHelper.InsertInterviewInfo(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; }