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