/// <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 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)); }
/// <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="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)); }
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>"); } }
public async Task <ApiResponse> Handle(GetContractByEmployeeIdQuery request, CancellationToken cancellationToken) { ApiResponse response = new ApiResponse(); try { if (request.EmployeeId != 0) { EmployeeContractModel EmployeeContractModel = new EmployeeContractModel(); ICollection <ContractTypeContent> contractTypeDariModel = await _dbContext.ContractTypeContent.Where(x => x.IsDeleted == false).ToListAsync(); List <EmployeeContractModel> dataModel = (from ec in _dbContext.EmployeeContract join e in _dbContext.EmployeeDetail on ec.EmployeeId equals e.EmployeeID join pd in _dbContext.ProvinceDetails on ec.Province equals pd.ProvinceId join epd in _dbContext.EmployeeProfessionalDetail on e.EmployeeID equals epd.EmployeeId join dd in _dbContext.DesignationDetail on ec.Designation equals dd.DesignationId join od in _dbContext.OfficeDetail on epd.OfficeId equals od.OfficeId join ect in _dbContext.EmployeeContractType on epd.EmployeeContractTypeId equals ect.EmployeeContractTypeId join jg in _dbContext.JobGrade on ec.Grade equals jg.GradeId join cd in _dbContext.CountryDetails on ec.Country equals cd.CountryId join bld in _dbContext.ProjectBudgetLineDetail on ec.BudgetLine equals bld.BudgetLineId join p in _dbContext.ProjectDetail on ec.Project equals Convert.ToInt32(p.ProjectId) where ec.IsDeleted == false && ec.EmployeeId == request.EmployeeId select new EmployeeContractModel { EmployeeContractId = ec.EmployeeContractId, EmployeeId = ec.EmployeeId, EmployeeName = ec.Employee.EmployeeName, FatherName = ec.Employee.FatherName, EmployeeCode = ec.Employee.EmployeeCode, DesignationId = ec.Designation, Designation = dd.Designation, ContractStartDate = ec.ContractStartDate, ContractEndDate = ec.ContractEndDate, DurationOfContract = ec.DurationOfContract, Salary = ec.Salary, Grade = ec.Grade, DutyStationId = od.OfficeId, DutyStation = od.OfficeName, ProvinceId = pd.ProvinceId, Province = pd.ProvinceName, CountryId = ec.Country, Country = cd.CountryName, JobId = null, Job = ec.Job, WorkTime = ec.WorkTime, WorkDayHours = ec.WorkDayHours, ContentEnglish = contractTypeDariModel.FirstOrDefault(c => c.EmployeeContractTypeId == epd.EmployeeContractTypeId).ContentEnglish, ContentDari = contractTypeDariModel.FirstOrDefault(c => c.EmployeeContractTypeId == epd.EmployeeContractTypeId).ContentDari, EmployeeImage = e.DocumentGUID + e.Extension, CountryDari = ec.CountryDari, DesignationDari = ec.DesignationDari, DutyStationDari = ec.DutyStationDari, GradeDari = ec.GradeDari, FatherNameDari = ec.FatherNameDari, JobDari = ec.JobDari, ProvinceDari = ec.ProvinceDari, EmployeeNameDari = ec.EmployeeNameDari, GradeName = jg.GradeName, ProjectNameDari = ec.ProjectNameDari, ProjectName = p.ProjectName, BudgetLine = bld.BudgetName, BudgetLineDari = ec.BudgetLineDari, ProjectCode = p.ProjectCode }).ToList(); response.data.EmployeeContractDetails = dataModel; response.StatusCode = StaticResource.successStatusCode; response.Message = "Success"; } else { response.StatusCode = StaticResource.failStatusCode; response.Message = "Employee Id Cannot be 0"; } } catch (Exception ex) { response.StatusCode = StaticResource.failStatusCode; response.Message = ex.Message; } return(response); }
/// <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); }
/// <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); }