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