protected void btnImport_Click(object sender, ImageClickEventArgs e) { PerformancePersonalModel searchModel = new PerformancePersonalModel(); //设置查询条件 //要素名称 //search += "TaskNo=" + document.getElementById("txtSearchTaskNo").value; ////启用状态 //search += "&Title=" + document.getElementById("inptTitle").value; //search += "&TaskFlag=" + document.getElementById("selTaskFlag").value; //search += "&TaskNum=" + document.getElementById("selTaskNum").value; UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; searchModel.Creator = userInfo.EmployeeID.ToString(); searchModel.TaskNo = txtSearchTask.Value.Trim(); //启用状态 searchModel.Title = inptSearchTitle.Value.Trim(); string selSearchTaskFlag = Request.Form["selSearchTaskFlag"].ToString(); if (selSearchTaskFlag != "0") searchModel.TaskFlag = selSearchTaskFlag; string selSearchTaskYear = Request.Form["selSearchTaskYear"].ToString(); if (selSearchTaskYear != "0") searchModel.TaskDate = selSearchTaskYear; string selSearchTaskNum = Request.Form["selSearchTaskNum"].ToString(); //启用状态 if (selSearchTaskNum != "0") searchModel.TaskNum = selSearchTaskNum; // searchModel.Status = "0";//默认草稿状态 //查询数据 DataTable dtData = PerformancePersonalBus.SearchTaskInfo(searchModel); //导出标题 string headerTitle = "自我鉴定编号|主题|鉴定周期分类|考核周期|开始日期|结束日期|鉴定状态|创建时间"; string[] header = headerTitle.Split('|'); //导出标题所对应的列字段名称 string columnFiled = "TaskNo|Title|TaskFlag|TaskNum|StartDate|EndDate|Status|CreateDate"; string[] field = columnFiled.Split('|'); XBase.Common.OutputToExecl.ExportToTable(this.Page, dtData, header, field, "自我鉴定列表"); }
public static bool UpdateProPersonalInfo(PerformancePersonalModel model) { //获取登陆用户信息 UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; //设置公司代码 model.CompanyCD = userInfo.CompanyCD; //设置最后修改者 model.ModifiedUserID = userInfo.UserID; //定义返回变量 bool isSucc = false; //操作日志 LogInfoModel logModel = InitLogInfo(model.TaskNo); //更新 if (ConstUtil.EDIT_FLAG_UPDATE.Equals(model.EditFlag)) { try { logModel.Element = ConstUtil.LOG_PROCESS_UPDATE; //执行更新操作 isSucc = PerformancePersonalDBHelper.CheckPerPersonalInfo (model); } catch (Exception ex) { //输出系统日志 WriteSystemLog(userInfo, ex); } } //插入 else { try { logModel.Element = ConstUtil.LOG_PROCESS_INSERT; //执行插入操作 isSucc = false ; logModel.ObjectID = model.TaskNo; } 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 SearchTaskInfo(PerformancePersonalModel model) { //获取登陆用户信息 UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; //获取公司代码 model.CompanyCD = userInfo.CompanyCD; //执行查询 return PerformancePersonalDBHelper.SearchTaskInfo(model); }
public static DataTable SearchTaskInfo(PerformancePersonalModel model) { #region 查询语句 //查询SQL拼写 StringBuilder searchSql = new StringBuilder(); searchSql.AppendLine(" SELECT isnull( Convert(varchar(100),ModifiedDate,23),'') AS ModifiedDate, "); searchSql.AppendLine(" ID "); searchSql.AppendLine(" ,CASE when taskflag='2' and TaskNum='1' then TaskDate+'第一季度'"); searchSql.AppendLine(" when taskflag='2' and TaskNum='2' then TaskDate+'第二季度'"); searchSql.AppendLine(" when taskflag='2' and TaskNum='3' then TaskDate+'第三季度'"); searchSql.AppendLine(" when taskflag='2' and TaskNum='4' then TaskDate+'第四季度'"); searchSql.AppendLine(" when taskflag='3' and TaskNum='1' then TaskDate+'年'+'上半年'"); searchSql.AppendLine(" when taskflag='3' and TaskNum='2' then TaskDate+'年'+'下半年'"); searchSql.AppendLine(" when taskflag='1' then TaskDate+'年'+cast(TaskNum as varchar)+'月' "); searchSql.AppendLine(" when taskflag='4' then cast(TaskNum as varchar) "); searchSql.AppendLine(" when taskflag='5' then cast(TaskNum as varchar) "); searchSql.AppendLine(" when taskflag is null then ''"); searchSql.AppendLine(" end as TaskNum "); searchSql.AppendLine(" , ISNULL( CONVERT(VARCHAR(10), StartDate ,21),'') as StartDate"); searchSql.AppendLine(" , ISNULL( CONVERT(VARCHAR(10), EndDate ,21),'') as EndDate"); searchSql.AppendLine(" , ISNULL( CONVERT(VARCHAR(10), TaskDate ,21),'') as TaskDate"); searchSql.AppendLine(" , ISNULL( CONVERT(VARCHAR(10), CreateDate ,21),'') as CreateDate"); searchSql.AppendLine(" ,CASE when TaskFlag='1' then '月考核'"); searchSql.AppendLine(" when TaskFlag='2' then '季考核'"); searchSql.AppendLine(" when TaskFlag='3' then '半年考核'"); searchSql.AppendLine(" when TaskFlag='4' then '年考核'"); searchSql.AppendLine(" when TaskFlag='5' then '临时考核'"); searchSql.AppendLine(" when TaskFlag is null then ''"); searchSql.AppendLine(" end as TaskFlag"); searchSql.AppendLine(" ,CASE when Status='0' then '草稿'"); searchSql.AppendLine(" when Status='1' then '已确认'"); searchSql.AppendLine(" end as Status"); searchSql.AppendLine(" ,isnull( Title,'') as Title "); searchSql.AppendLine(" ,TaskNo "); searchSql.AppendLine(" FROM officedba.PerformancePersonal "); searchSql.AppendLine(" WHERE CompanyCD = @CompanyCD and Creator=@Creator "); #endregion //定义查询的命令 SqlCommand comm = new SqlCommand(); //添加公司代码参数 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Creator", model.Creator )); //l if (!string.IsNullOrEmpty(model.TaskNo)) { searchSql.AppendLine(" AND TaskNo like @TaskNo "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@TaskNo","%" + model.TaskNo + "%")); } if (!string.IsNullOrEmpty(model.TaskDate )) { searchSql.AppendLine(" AND TaskDate = @TaskDate "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@TaskDate", model.TaskDate)); } if (!string.IsNullOrEmpty(model.TaskFlag)) { searchSql.AppendLine(" AND TaskFlag = @TaskFlag "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@TaskFlag", model.TaskFlag)); } if (model.TaskFlag != "4" || model.TaskFlag != "5") { if (!string.IsNullOrEmpty(model.TaskNum)) { searchSql.AppendLine(" AND TaskNum = @TaskNum "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@TaskNum", model.TaskNum)); } } if (!string.IsNullOrEmpty(model.Title)) { searchSql.AppendLine(" AND Title LIKE @Title "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Title", "%" + model.Title + "%")); } //启用状态 //指定命令的SQL文 comm.CommandText = searchSql.ToString(); //执行查询 return SqlHelper.ExecuteSearch(comm); }
/// <summary> /// 确认信息 /// </summary> /// <param name="model"></param> /// <returns></returns> public static bool CheckPerPersonalInfo(PerformancePersonalModel model) { #region 插入SQL拼写 StringBuilder insertSql = new StringBuilder(); insertSql.AppendLine("update officedba.PerformancePersonal "); insertSql.AppendLine(" set Title=@Title "); insertSql.AppendLine(" ,TaskFlag=@TaskFlag "); insertSql.AppendLine(" ,TaskDate=@TaskDate "); insertSql.AppendLine(" ,TaskNum=@TaskNum "); insertSql.AppendLine(" ,StartDate=@StartDate "); insertSql.AppendLine(" ,EndDate=@EndDate "); insertSql.AppendLine(" ,ModifiedDate=getdate() "); insertSql.AppendLine(" ,ModifiedUserID=@ModifiedUserID "); insertSql.AppendLine(" ,WorkContent=@WorkContent "); insertSql.AppendLine(" ,Complete=@Complete "); insertSql.AppendLine(" ,AfterWork=@AfterWork "); insertSql.AppendLine(" ,Defects=@Defects "); insertSql.AppendLine(" ,Problems=@Problems "); insertSql.AppendLine(" ,Advices=@Advices "); insertSql.AppendLine(" ,Checker=@Checker "); insertSql.AppendLine(" ,CheckDate=getdate() "); insertSql.AppendLine(" ,Status=@Status "); insertSql.AppendLine(" ,Remark=@Remark where CompanyCD=@CompanyCD and TaskNo=@TaskNo "); // insertSql.AppendLine(" set @ElemID= @@IDENTITY "); #endregion //定义插入基本信息的命令 SqlCommand comm = new SqlCommand(); comm.CommandText = insertSql.ToString(); //设置保存的参数 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); //公司代码 comm.Parameters.Add(SqlHelper.GetParameterFromString("@TaskNo", model.TaskNo)); //类型名称 comm.Parameters.Add(SqlHelper.GetParameterFromString("@Title", model.Title)); //创建人 comm.Parameters.Add(SqlHelper.GetParameterFromString("@TaskFlag", model.TaskFlag)); //启用状态 comm.Parameters.Add(SqlHelper.GetParameterFromString("@TaskDate", model.TaskDate)); //更新用户ID comm.Parameters.Add(SqlHelper.GetParameterFromString("@TaskNum", model.TaskNum)); //更新用户ID comm.Parameters.Add(SqlHelper.GetParameterFromString("@StartDate", model.StartDate)); //更新用户ID comm.Parameters.Add(SqlHelper.GetParameterFromString("@EndDate", model.EndDate)); //更新用户ID comm.Parameters.Add(SqlHelper.GetParameterFromString("@Remark", model.Remark)); //更新用户ID comm.Parameters.Add(SqlHelper.GetParameterFromString("@Checker", model.Checker )); //更新用户ID comm.Parameters.Add(SqlHelper.GetParameterFromString("@WorkContent", model.WorkContent)); //更新用户ID comm.Parameters.Add(SqlHelper.GetParameterFromString("@Complete", model.Complete)); //更新用户ID comm.Parameters.Add(SqlHelper.GetParameterFromString("@AfterWork", model.AfterWork)); //更新用户ID comm.Parameters.Add(SqlHelper.GetParameterFromString("@Defects", model.Defects)); //更新用户ID comm.Parameters.Add(SqlHelper.GetParameterFromString("@Problems", model.Problems)); //更新用户ID comm.Parameters.Add(SqlHelper.GetParameterFromString("@Advices", model.Advices)); //更新用户ID comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedUserID", model.ModifiedUserID)); //更新用户ID comm.Parameters.Add(SqlHelper.GetParameterFromString("@Status", model.Status )); //更新用户ID //添加返回参数 // comm.Parameters.Add(SqlHelper.GetOutputParameter("@ElemID", SqlDbType.Int)); //执行插入操作 bool isSucc = SqlHelper.ExecuteTransWithCommand(comm); return isSucc; }
/// <summary> /// 批插入考核任务表信息 /// </summary> /// <param name="modeList"></param> /// <returns></returns> public static bool InsertPerPersonalInfo(PerformancePersonalModel model) { #region 插入SQL拼写 StringBuilder insertSql = new StringBuilder(); insertSql.AppendLine("INSERT INTO officedba.PerformancePersonal "); insertSql.AppendLine(" (CompanyCD "); insertSql.AppendLine(" ,TaskNo "); insertSql.AppendLine(" ,Title "); insertSql.AppendLine(" ,TaskFlag "); insertSql.AppendLine(" ,TaskDate "); insertSql.AppendLine(" ,TaskNum "); insertSql.AppendLine(" ,StartDate "); insertSql.AppendLine(" ,EndDate "); insertSql.AppendLine(" ,Status "); insertSql.AppendLine(" ,CreateDate "); insertSql.AppendLine(" ,Remark "); insertSql.AppendLine(" ,WorkContent "); insertSql.AppendLine(" ,Complete "); insertSql.AppendLine(" ,AfterWork "); insertSql.AppendLine(" ,Defects "); insertSql.AppendLine(" ,Problems "); insertSql.AppendLine(" ,Advices "); insertSql.AppendLine(" ,Creator) "); insertSql.AppendLine(" VALUES "); insertSql.AppendLine(" (@CompanyCD "); insertSql.AppendLine(" ,@TaskNo "); insertSql.AppendLine(" ,@Title "); insertSql.AppendLine(" ,@TaskFlag "); insertSql.AppendLine(" ,@TaskDate "); insertSql.AppendLine(" ,@TaskNum "); insertSql.AppendLine(" ,@StartDate "); insertSql.AppendLine(" ,@EndDate "); insertSql.AppendLine(" ,@Status "); insertSql.AppendLine(" ,getdate() "); insertSql.AppendLine(" ,@Remark "); insertSql.AppendLine(" ,@WorkContent "); insertSql.AppendLine(" ,@Complete "); insertSql.AppendLine(" ,@AfterWork "); insertSql.AppendLine(" ,@Defects "); insertSql.AppendLine(" ,@Problems "); insertSql.AppendLine(" ,@Advices "); insertSql.AppendLine(" ,@Creator) "); // insertSql.AppendLine(" set @ElemID= @@IDENTITY "); #endregion //定义插入基本信息的命令 SqlCommand comm = new SqlCommand(); comm.CommandText = insertSql.ToString(); //设置保存的参数 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); //公司代码 comm.Parameters.Add(SqlHelper.GetParameterFromString("@TaskNo", model.TaskNo)); //类型名称 comm.Parameters.Add(SqlHelper.GetParameterFromString("@Title", model.Title)); //创建人 comm.Parameters.Add(SqlHelper.GetParameterFromString("@TaskFlag", model.TaskFlag)); //启用状态 comm.Parameters.Add(SqlHelper.GetParameterFromString("@TaskDate", model.TaskDate)); //更新用户ID comm.Parameters.Add(SqlHelper.GetParameterFromString("@TaskNum", model.TaskNum)); //更新用户ID comm.Parameters.Add(SqlHelper.GetParameterFromString("@StartDate", model.StartDate)); //更新用户ID comm.Parameters.Add(SqlHelper.GetParameterFromString("@EndDate", model.EndDate)); //更新用户ID comm.Parameters.Add(SqlHelper.GetParameterFromString("@Status", model.Status)); //更新用户ID comm.Parameters.Add(SqlHelper.GetParameterFromString("@Remark", model.Remark)); //更新用户ID comm.Parameters.Add(SqlHelper.GetParameterFromString("@WorkContent", model.WorkContent )); //更新用户ID comm.Parameters.Add(SqlHelper.GetParameterFromString("@Complete", model.Complete )); //更新用户ID comm.Parameters.Add(SqlHelper.GetParameterFromString("@AfterWork", model.AfterWork )); //更新用户ID comm.Parameters.Add(SqlHelper.GetParameterFromString("@Defects", model.Defects )); //更新用户ID comm.Parameters.Add(SqlHelper.GetParameterFromString("@Problems", model.Problems )); //更新用户ID comm.Parameters.Add(SqlHelper.GetParameterFromString("@Advices", model.Advices )); //更新用户ID comm.Parameters.Add(SqlHelper.GetParameterFromString("@Creator", model.Creator)); //更新用户ID //添加返回参数 // comm.Parameters.Add(SqlHelper.GetOutputParameter("@ElemID", SqlDbType.Int)); //执行插入操作 bool isSucc = SqlHelper.ExecuteTransWithCommand(comm); return isSucc; }