/// <summary> /// 编辑合同信息 /// </summary> /// <param name="model">合同信息</param> /// <returns></returns> public static bool SaveEmployeeContractInfo(EmployeeContractModel model) { //获取登陆用户信息 UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; //设置公司代码 model.CompanyCD = userInfo.CompanyCD; model.ModifiedUserID = userInfo.UserID; //定义返回变量 bool isSucc = false; //操作日志 LogInfoModel logModel = InitLogInfo(model.ContractNo); //更新 if (!string.IsNullOrEmpty(model.ID)) { try { logModel.Element = ConstUtil.LOG_PROCESS_UPDATE; //执行更新操作 isSucc = EmployeeContractDBHelper.UpdateEmployeeContractInfo(model); } catch (Exception ex) { //输出系统日志 WriteSystemLog(userInfo, ex); } } //插入 else { try { logModel.Element = ConstUtil.LOG_PROCESS_INSERT; //执行插入操作 isSucc = EmployeeContractDBHelper.InsertEmployeeContractInfo(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; }
/// <summary> /// 查询合同信息 /// </summary> /// <param name="model">查询条件</param> /// <returns></returns> public static DataTable SearchEmployeeContractInfo(EmployeeContractModel model) { #region 查询语句 StringBuilder searchSql = new StringBuilder(); searchSql.AppendLine(" SELECT "); searchSql.AppendLine(" A.ID "); searchSql.AppendLine(" ,A.ContractNo "); searchSql.AppendLine(" ,A.EmployeeID "); searchSql.AppendLine(" ,ISNULL(B.EmployeeNo, '') AS EmployeeNo "); searchSql.AppendLine(" ,ISNULL(B.EmployeeName, '') AS EmployeeName "); searchSql.AppendLine(" ,ISNULL(A.Title, '') AS Title "); searchSql.AppendLine(" ,ISNULL(CONVERT(VARCHAR(10),A.SigningDate,21),'') "); searchSql.AppendLine(" AS SigningDate "); searchSql.AppendLine(" ,ISNULL(CONVERT(VARCHAR(10),A.StartDate,21),'') "); searchSql.AppendLine(" AS StartDate "); searchSql.AppendLine(" ,ISNULL(CONVERT(VARCHAR(10),A.EndDate,21),'') "); searchSql.AppendLine(" AS EndDate "); searchSql.AppendLine(" ,A.ModifiedDate "); searchSql.AppendLine(" FROM "); searchSql.AppendLine(" officedba.EmployeeContract A "); searchSql.AppendLine(" LEFT JOIN officedba.EmployeeInfo B "); searchSql.AppendLine(" ON B.ID = A.EmployeeID "); searchSql.AppendLine(" WHERE "); searchSql.AppendLine(" A.CompanyCD = @CompanyCD "); #endregion //定义查询的命令 SqlCommand comm = new SqlCommand(); //公司代码 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); #region 页面查询条件 //申请编号 if (!string.IsNullOrEmpty(model.ContractNo)) { searchSql.AppendLine(" AND A.ContractNo LIKE '%' + @ContractNo + '%'"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ContractNo", model.ContractNo)); } //主题 if (!string.IsNullOrEmpty(model.Title)) { searchSql.AppendLine(" AND A.Title LIKE '%' + @Title + '%'"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Title", model.Title)); } //员工 if (!string.IsNullOrEmpty(model.EmployeeID)) { searchSql.AppendLine(" AND A.EmployeeID = @EmployeeID "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeID", model.EmployeeID)); } #endregion //指定命令的SQL文 comm.CommandText = searchSql.ToString(); //执行查询 return SqlHelper.ExecuteSearch(comm); }
protected void btnImport_Click(object sender, System.Web.UI.ImageClickEventArgs e) { try { string orderString = hiddExpOrder.Value.Trim();//排序 string order = "asc";//排序:降序 string orderBy = (!string.IsNullOrEmpty(orderString)) ? orderString.Substring(0, orderString.Length - 2) : "ModifiedDate";//要排序的字段,如果为空,默认为"ID" if (orderString.EndsWith("_d")) { order = "desc";//排序:降序 } string ord = " ORDER BY " + orderBy + " " + order; //获取数据 EmployeeContractModel searchModel = new EmployeeContractModel(); //设置查询条件 //编号 searchModel.ContractNo = txtContractNo.Text.Trim(); //主题 searchModel.Title = txtTitle.Text.Trim(); //员工 searchModel.EmployeeID = hidEmployeeID.Value; //查询数据 DataTable dt = EmployeeContractBus.SearchEmployeeContractInfo(searchModel); OutputToExecl.ExportToTableFormat(this, dt, new string[] { "合同编号", "合同名称", "员工编号", "员工姓名", "签约时间", "生效时间", "失效时间" }, new string[] { "ContractNo", "Title", "EmployeeNo", "EmployeeName", "SigningDate", "StartDate", "EndDate" }, "合同列表"); } catch { ClientScript.RegisterStartupScript(this.GetType(), "Exp", "<script language=javascript>showPopup('../../../Images/Pic/Close.gif','../../../Images/Pic/note.gif','导出发生异常');</script>"); } }
/// <summary> /// 查询合同信息 /// </summary> /// <param name="model">查询条件</param> /// <returns></returns> public static DataTable SearchEmployeeContractInfo(EmployeeContractModel model) { //获取登陆用户信息 UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; //设置公司代码 model.CompanyCD = userInfo.CompanyCD; //执行查询 return EmployeeContractDBHelper.SearchEmployeeContractInfo(model); }
/// <summary> /// 保存时参数设置 /// </summary> /// <param name="comm">命令</param> /// <param name="model">人才代理信息</param> private static void SetSaveParameter(SqlCommand comm, EmployeeContractModel model) { comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));//企业代码 comm.Parameters.Add(SqlHelper.GetParameterFromString("@ContractNo", model.ContractNo));//合同编号 comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeID", model.EmployeeID));//员工ID comm.Parameters.Add(SqlHelper.GetParameterFromString("@Title", model.Title));//主题 comm.Parameters.Add(SqlHelper.GetParameterFromString("@ContractName", model.ContractName));//合同名称 comm.Parameters.Add(SqlHelper.GetParameterFromString("@ContractKind", model.ContractKind));//工种 comm.Parameters.Add(SqlHelper.GetParameterFromString("@ContractType", model.ContractType));//合同类型 comm.Parameters.Add(SqlHelper.GetParameterFromString("@ContractProperty", model.ContractProperty));//合同属性 comm.Parameters.Add(SqlHelper.GetParameterFromString("@ContractStatus", model.ContractStatus));//合同状态 comm.Parameters.Add(SqlHelper.GetParameterFromString("@ContractPeriod", model.ContractPeriod));//合同期限 comm.Parameters.Add(SqlHelper.GetParameterFromString("@TestWage", model.TestWage));//试用工资(元) comm.Parameters.Add(SqlHelper.GetParameterFromString("@Wage", model.Wage));//转正工资(元) comm.Parameters.Add(SqlHelper.GetParameterFromString("@SigningDate", model.SigningDate));//签约时间 comm.Parameters.Add(SqlHelper.GetParameterFromString("@StartDate", model.StartDate));//生效时间 comm.Parameters.Add(SqlHelper.GetParameterFromString("@EndDate", model.EndDate));//失效时间 comm.Parameters.Add(SqlHelper.GetParameterFromString("@TrialMonthCount", model.TrialMonthCount));//试用月数 comm.Parameters.Add(SqlHelper.GetParameterFromString("@Flag", model.Flag));//转正标识 comm.Parameters.Add(SqlHelper.GetParameterFromString("@Attachment", model.PageAttachment));//附件 comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedUserID", model.ModifiedUserID));//更新用户ID comm.Parameters.Add(SqlHelper.GetParameterFromString("@AttachmentName", model.AttachmentName));//附件名称 comm.Parameters.Add(SqlHelper.GetParameterFromString("@Reminder", model.Reminder));//提醒人 comm.Parameters.Add(SqlHelper.GetParameterFromString("@AheadDay", model.AheadDay));//提前时间 }
/// <summary> /// 更新合同信息 /// </summary> /// <param name="model">合同信息</param> /// <returns></returns> public static bool UpdateEmployeeContractInfo(EmployeeContractModel model) { #region SQL文拼写 StringBuilder updateSql = new StringBuilder(); updateSql.AppendLine(" UPDATE officedba.EmployeeContract "); updateSql.AppendLine(" SET EmployeeID = @EmployeeID "); updateSql.AppendLine(" ,Title = @Title "); updateSql.AppendLine(" ,ContractName = @ContractName "); updateSql.AppendLine(" ,ContractKind = @ContractKind "); updateSql.AppendLine(" ,ContractType = @ContractType "); updateSql.AppendLine(" ,ContractProperty = @ContractProperty "); updateSql.AppendLine(" ,ContractStatus = @ContractStatus "); updateSql.AppendLine(" ,ContractPeriod = @ContractPeriod "); updateSql.AppendLine(" ,TestWage = @TestWage "); updateSql.AppendLine(" ,Wage = @Wage "); updateSql.AppendLine(" ,SigningDate = @SigningDate "); updateSql.AppendLine(" ,StartDate = @StartDate "); updateSql.AppendLine(" ,EndDate = @EndDate "); updateSql.AppendLine(" ,TrialMonthCount = @TrialMonthCount "); updateSql.AppendLine(" ,Flag = @Flag "); updateSql.AppendLine(" ,Attachment = @Attachment "); updateSql.AppendLine(" ,ModifiedDate = getdate() "); updateSql.AppendLine(" ,ModifiedUserID = @ModifiedUserID "); updateSql.AppendLine(" ,AttachmentName = @AttachmentName "); updateSql.AppendLine(" ,Reminder = @Reminder "); updateSql.AppendLine(" ,AheadDay = @AheadDay "); updateSql.AppendLine(" WHERE "); updateSql.AppendLine(" CompanyCD = @CompanyCD "); updateSql.AppendLine(" AND ContractNo = @ContractNo "); #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 InsertEmployeeContractInfo(EmployeeContractModel model) { #region 登陆SQL文 StringBuilder insertSql = new StringBuilder(); insertSql.AppendLine(" INSERT INTO "); insertSql.AppendLine(" officedba.EmployeeContract "); insertSql.AppendLine(" (CompanyCD "); insertSql.AppendLine(" ,ContractNo "); insertSql.AppendLine(" ,EmployeeID "); insertSql.AppendLine(" ,Title "); insertSql.AppendLine(" ,ContractName "); insertSql.AppendLine(" ,ContractKind "); insertSql.AppendLine(" ,ContractType "); insertSql.AppendLine(" ,ContractProperty "); insertSql.AppendLine(" ,ContractStatus "); insertSql.AppendLine(" ,ContractPeriod "); insertSql.AppendLine(" ,TestWage "); insertSql.AppendLine(" ,Wage "); insertSql.AppendLine(" ,SigningDate "); insertSql.AppendLine(" ,StartDate "); insertSql.AppendLine(" ,EndDate "); insertSql.AppendLine(" ,TrialMonthCount "); insertSql.AppendLine(" ,Flag "); insertSql.AppendLine(" ,Attachment "); insertSql.AppendLine(" ,ModifiedDate "); insertSql.AppendLine(" ,ModifiedUserID "); insertSql.AppendLine(" ,Reminder "); insertSql.AppendLine(" ,AheadDay "); insertSql.AppendLine(" ,AttachmentName) "); insertSql.AppendLine(" VALUES "); insertSql.AppendLine(" (@CompanyCD "); insertSql.AppendLine(" ,@ContractNo "); insertSql.AppendLine(" ,@EmployeeID "); insertSql.AppendLine(" ,@Title "); insertSql.AppendLine(" ,@ContractName "); insertSql.AppendLine(" ,@ContractKind "); insertSql.AppendLine(" ,@ContractType "); insertSql.AppendLine(" ,@ContractProperty "); insertSql.AppendLine(" ,@ContractStatus "); insertSql.AppendLine(" ,@ContractPeriod "); insertSql.AppendLine(" ,@TestWage "); insertSql.AppendLine(" ,@Wage "); insertSql.AppendLine(" ,@SigningDate "); insertSql.AppendLine(" ,@StartDate "); insertSql.AppendLine(" ,@EndDate "); insertSql.AppendLine(" ,@TrialMonthCount "); insertSql.AppendLine(" ,@Flag "); insertSql.AppendLine(" ,@Attachment "); insertSql.AppendLine(" ,getdate() "); insertSql.AppendLine(" ,@ModifiedUserID "); insertSql.AppendLine(" ,@Reminder "); insertSql.AppendLine(" ,@AheadDay "); insertSql.AppendLine(" ,@AttachmentName) "); insertSql.AppendLine(" SET @ContractID= @@IDENTITY "); #endregion //定义更新基本信息的命令 SqlCommand comm = new SqlCommand(); //设置存储过程名 comm.CommandText = insertSql.ToString(); //设置保存的参数 SetSaveParameter(comm, model); //添加返回参数 comm.Parameters.Add(SqlHelper.GetOutputParameter("@ContractID", SqlDbType.Int)); //执行登陆操作 bool isSucc = SqlHelper.ExecuteTransWithCommand(comm); //设置ID model.ID = comm.Parameters["@ContractID"].Value.ToString(); //执行插入并返回插入结果 return isSucc; }