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