/// <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);
}
