protected void btnImport_Click(object sender, System.Web.UI.ImageClickEventArgs e) { //try //{ int TotalCount = 0; XBase.Model.Office.HumanManager.MoveApplyModel searchModel = new XBase.Model.Office.HumanManager.MoveApplyModel(); //获取数据 //设置查询条件 searchModel.MoveApplyNo = txtMoveApplyNo.Text.Trim(); //申请编号 searchModel.EmployeeID = hidApplyID.Value.Trim(); //申请人 searchModel.ApplyDate = txtApplyDate.Text.Trim(); //申请日期 searchModel.ApplyToDate = txtApplyToDate.Text.Trim(); searchModel.FlowStatusID = ddlFlowStatus.Text.Trim(); //审批状态 DataTable dt = XBase.Business.Office.HumanManager.MoveApplyBus.SearchMoveApplyInfo(searchModel, 1, 1000000, "MoveApplyNo", ref TotalCount); //查询数据 //导出标题 string headerTitle = "申请编号|申请人|申请时间|部门|岗位|到职日期|审批状态"; string[] header = headerTitle.Split('|'); //导出标题所对应的列字段名称 string columnFiled = "MoveApplyNo|EmployeeName|ApplyDate|DeptName|QuarterName|HopeDate|FlowStatusName"; string[] field = columnFiled.Split('|'); XBase.Common.OutputToExecl.ExportToTable(this.Page, dt, header, field, "离职申请列表"); //} //catch { } }
/// <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; }
protected void btnImport_Click(object sender, System.Web.UI.ImageClickEventArgs e) { //try //{ int TotalCount = 0; XBase.Model.Office.HumanManager.MoveApplyModel searchModel = new XBase.Model.Office.HumanManager.MoveApplyModel();//获取数据 //设置查询条件 searchModel.MoveApplyNo = txtMoveApplyNo.Text.Trim();//申请编号 searchModel.EmployeeID = hidApplyID.Value.Trim();//申请人 searchModel.ApplyDate = txtApplyDate.Text.Trim();//申请日期 searchModel.ApplyToDate = txtApplyToDate.Text.Trim(); searchModel.FlowStatusID = ddlFlowStatus.Text.Trim();//审批状态 DataTable dt = XBase.Business.Office.HumanManager.MoveApplyBus.SearchMoveApplyInfo(searchModel, 1, 1000000, "MoveApplyNo", ref TotalCount);//查询数据 //导出标题 string headerTitle = "申请编号|申请人|申请时间|部门|岗位|到职日期|审批状态"; string[] header = headerTitle.Split('|'); //导出标题所对应的列字段名称 string columnFiled = "MoveApplyNo|EmployeeName|ApplyDate|DeptName|QuarterName|HopeDate|FlowStatusName"; string[] field = columnFiled.Split('|'); XBase.Common.OutputToExecl.ExportToTable(this.Page, dt, header, field, "离职申请列表"); //} //catch { } }
/// <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 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 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 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); }