public static bool UpdateBetterInfobyPlanNo(PerformanceBetterModel model)
{
bool isSucc = false;
#region 插入SQL拼写
StringBuilder insertSql = new StringBuilder();
insertSql.AppendLine("update officedba.PerformanceBetter ");
insertSql.AppendLine(" set Title=@Title ");
insertSql.AppendLine(" ,Remark=@Remark ");
insertSql.AppendLine(" , ModifiedUserID=@ModifiedUserID ");
insertSql.AppendLine(" , ModifiedDate=getdate() ");
insertSql.AppendLine("where CompanyCD=@CompanyCD and PlanNo=@PlanNo ");
#endregion
//定义插入基本信息的命令
SqlCommand comm = new SqlCommand();
comm.CommandText = insertSql.ToString();
//设置保存的参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); //公司代码
comm.Parameters.Add(SqlHelper.GetParameterFromString("@PlanNo", model.PlanNo)); //类型名称
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Remark", model.Remark)); //创建人
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Title", model.Title)); //启用状态
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedUserID", model.ModifiedUserID));
//执行插入操作
isSucc = SqlHelper.ExecuteTransWithCommand(comm);
return(isSucc);
}
protected void btnImport_Click(object sender, ImageClickEventArgs e)
{
PerformanceBetterModel searchModel = new PerformanceBetterModel();
searchModel.PlanNo = txtSearchTask.Value.Trim();
//启用状态
searchModel.Title = inptSearchTitle.Value.Trim();
searchModel.CreateDate = txtStartDate.Text.Trim();
searchModel.EndDate = txtEndDate.Text.Trim();
searchModel.EmployeeId = txtSearchScoreEmployee.Value;
//启用状态
//查询数据
DataTable dtData = PerformanceBetterBus.SearchPlanInfo(searchModel);
//导出标题
string headerTitle = "计划编号|计划主题|创建人|创建时间";
string[] header = headerTitle.Split('|');
//导出标题所对应的列字段名称
string columnFiled = "PlanNo|Title|Creator|CreateDate";
string[] field = columnFiled.Split('|');
XBase.Common.OutputToExecl.ExportToTable(this.Page, dtData, header, field, "绩效改进列表");
}
public static DataTable SearchPlanInfo(PerformanceBetterModel model)
{
//获取登陆用户信息
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//获取公司代码
model.CompanyCD = userInfo.CompanyCD;
//执行查询
return(PerformanceBetterDBHelper.SearchBetterInfo(model));
}
public static bool InsertBetterInfo(PerformanceBetterModel model)
{
bool isSucc = false;
#region 插入SQL拼写
StringBuilder insertSql = new StringBuilder();
insertSql.AppendLine("INSERT INTO officedba.PerformanceBetter ");
insertSql.AppendLine(" (CompanyCD ");
insertSql.AppendLine(" ,PlanNo ");
insertSql.AppendLine(" ,Title ");
insertSql.AppendLine(" ,Remark ");
insertSql.AppendLine(" ,Creator ");
insertSql.AppendLine(" ,CreateDate) ");
insertSql.AppendLine(" VALUES ");
insertSql.AppendLine(" (@CompanyCD ");
insertSql.AppendLine(" ,@PlanNo ");
insertSql.AppendLine(" ,@Title ");
insertSql.AppendLine(" ,@Remark ");
insertSql.AppendLine(" ,@Creator ");
insertSql.AppendLine(" ,getdate() ) ");
#endregion
//定义插入基本信息的命令
SqlCommand comm = new SqlCommand();
comm.CommandText = insertSql.ToString();
//设置保存的参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); //公司代码
comm.Parameters.Add(SqlHelper.GetParameterFromString("@PlanNo", model.PlanNo)); //类型名称
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Title", model.Title)); //创建人
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Remark", model.Remark)); //启用状态
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Creator", model.Creator)); //启用状态
//添加返回参数
// comm.Parameters.Add(SqlHelper.GetOutputParameter("@ElemID", SqlDbType.Int));
//执行插入操作
isSucc = SqlHelper.ExecuteTransWithCommand(comm);
return(isSucc);
}
public static DataTable SearchBetterInfo(PerformanceBetterModel model)
{
#region 查询语句
//查询SQL拼写
StringBuilder searchSql = new StringBuilder();
searchSql.AppendLine(" SELECT distinct( a.PlanNo),a.ID ,isnull(a.Title,'') as Title,CONVERT(VARCHAR(10),a.CreateDate,21) AS CreateDate ,isnull(b.EmployeeName,'') as Creator ,isnull(Convert(varchar(100),a.ModifiedDate,23),'') AS ModifiedDate ");
searchSql.AppendLine(" FROM officedba.PerformanceBetter a left outer join officedba.EmployeeInfo b on b.CompanyCD=a.CompanyCD and a.Creator=b.ID left outer join officedba.PerformanceBetterDetail c on a.CompanyCD=c.CompanyCD and a.PlanNo=c.PlanNo");
searchSql.AppendLine(" WHERE a.CompanyCD = @CompanyCD ");
#endregion
//定义查询的命令
SqlCommand comm = new SqlCommand();
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
//l
if (!string.IsNullOrEmpty(model.EmployeeId))
{
searchSql.AppendLine(" AND c.EmployeeID = @EmployeeID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeID", model.EmployeeId));
}
if (!string.IsNullOrEmpty(model.CreateDate) && !string.IsNullOrEmpty(model.EndDate))
{
searchSql.AppendLine(" AND a.CreateDate between @CreateDate and @EndDate ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CreateDate", model.CreateDate));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EndDate", model.EndDate));
}
if (!string.IsNullOrEmpty(model.EndDate) && string.IsNullOrEmpty(model.CreateDate))
{
searchSql.AppendLine(" AND a.CreateDate <@EndDate ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EndDate", model.EndDate));
}
if (string.IsNullOrEmpty(model.EndDate) && !string.IsNullOrEmpty(model.CreateDate))
{
searchSql.AppendLine(" AND a.CreateDate between @CreateDate and @EndDate ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CreateDate", model.CreateDate));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EndDate", DateTime.Now.ToString()));
}
if (!string.IsNullOrEmpty(model.PlanNo))
{
searchSql.AppendLine(" AND a.PlanNo like @PlanNo ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@PlanNo", "%" + model.PlanNo + "%"));
}
if (!string.IsNullOrEmpty(model.Title))
{
searchSql.AppendLine(" AND a.Title LIKE @Title ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Title", "%" + model.Title + "%"));
}
//启用状态
//指定命令的SQL文
comm.CommandText = searchSql.ToString();
//执行查询
return(SqlHelper.ExecuteSearch(comm));
}
public static bool SaveBetterInfo(PerformanceBetterModel model, IList <PerformanceBetterDetailModel> modleSummaryList)
{
//获取登陆用户信息
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//定义返回变量
bool isSucc = false;
//操作日志
LogInfoModel logModel = InitLogInfo(model.PlanNo);
try
{
logModel.Element = ConstUtil.LOG_PROCESS_UPDATE;
//执行更新操作
/// isSucc = PerformanceTaskDBHelper.UpdatePerTaskInfo(model, modellist, modleSummaryList);
if (model.EditFlag == ConstUtil.EDIT_FLAG_INSERT)
{
if (PerformanceBetterDBHelper.InsertBetterInfo(model))
{
if (PerformanceBetterDBHelper.InsertBetterDetaiInfo(modleSummaryList))
{
isSucc = true;
}
else
{
isSucc = false;
}
}
else
{
isSucc = false;
}
}
else
{
if (PerformanceBetterDBHelper.UpdateBetterInfobyPlanNo(model))
{
if (PerformanceBetterDBHelper.DeleteBetterDetatilInfobyPlanNo(model.PlanNo, model.CompanyCD))
{
if (PerformanceBetterDBHelper.InsertBetterDetaiInfo(modleSummaryList))
{
isSucc = true;
}
else
{
isSucc = false;
}
}
else
{
isSucc = false;
}
}
else
{
isSucc = false;
}
}
}
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);
}
