public static DataTable SearchTaskInfo(PerformanceScoreModel model)
{
#region 查询语句
//查询SQL拼写
StringBuilder searchSql = new StringBuilder();
searchSql.AppendLine(" select distinct(a.TaskNo),a.TemplateNo,isnull( f.Title,'') as TemplateName,isnull(b.Title,'') as TaskTitle,a.EmployeeID");
searchSql.AppendLine(" ,CASE b.TaskFlag ");
searchSql.AppendLine(" WHEN '1' THEN '月考核' ");
searchSql.AppendLine(" WHEN '2' THEN '季考核' ");
searchSql.AppendLine(" WHEN '3' THEN '半年考核' ");
searchSql.AppendLine(" WHEN '4' THEN '年考核' ");
searchSql.AppendLine(" WHEN '5' THEN '临时考核' ");
searchSql.AppendLine(" WHEN null THEN ' ' ");
searchSql.AppendLine(" ELSE '' ");
searchSql.AppendLine(" END AS TaskFlag,a.TemplateNo ");
searchSql.AppendLine(" , isnull(c.EmployeeName,'') as EmployeeName,isnull(d.EmployeeName,'') as ScoreEmployeeName ,isnull(e.EmployeeName,'') as CreateEmployeeName, ISNULL( CONVERT(VARCHAR(10), b.CreateDate ,21),'') as CreateDate from officedba.PerformanceScore a left outer join officedba.PerformanceTask b on a.CompanyCD=b.CompanyCD and a.TaskNo=b.TaskNo left outer join officedba.EmployeeInfo c on c.CompanyCD=a.CompanyCD and a.EmployeeID=c.ID left outer join officedba.EmployeeInfo d on d.CompanyCD=a.CompanyCD and a.ScoreEmployee=d.ID left outer join officedba.EmployeeInfo e on e.CompanyCD=a.CompanyCD and b.Creator =e.ID left outer join officedba.PerformanceTemplate f on a.CompanyCD=f.CompanyCD and a.TemplateNo=f.TemplateNo");
// searchSql.AppendLine(" WHERE a.CompanyCD = @CompanyCD ");
searchSql.AppendLine(" WHERE a.CompanyCD = @CompanyCD and a.Status=@Status and a.ScoreEmployee=@ScoreEmployee");
#endregion
//定义查询的命令
SqlCommand comm = new SqlCommand();
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ScoreEmployee", model.ScoreEmployee));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Status", model.Status));
if (!string.IsNullOrEmpty(model.TaskNo))
{
searchSql.AppendLine(" AND a.TaskNo like @TaskNo ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TaskNo", "%" + model.TaskNo + "%"));
}
if (!string.IsNullOrEmpty(model.TaskFlag))
{
searchSql.AppendLine(" AND b.TaskFlag = @TaskFlag ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TaskFlag", model.TaskFlag));
}
if (!string.IsNullOrEmpty(model.TaskTitle))
{
searchSql.AppendLine(" AND b.Title LIKE @Title ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Title", "%" + model.TaskTitle + "%"));
}
if (!string.IsNullOrEmpty(model.EmployeeID))
{
searchSql.AppendLine(" AND a.EmployeeID = @EmployeeID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeID", model.EmployeeID));
}
//启用状态
//指定命令的SQL文
comm.CommandText = searchSql.ToString();
//执行查询
return(SqlHelper.ExecuteSearch(comm));
}
public static DataTable SearchTaskInfo(PerformanceScoreModel model)
{
//获取登陆用户信息
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//获取公司代码
model.CompanyCD = userInfo.CompanyCD;
//执行查询
return(PerformanceGradeDBHelper.SearchTaskInfo(model));
}
protected void btnImport_Click(object sender, ImageClickEventArgs e)
{
PerformanceScoreModel searchModel = new PerformanceScoreModel();
searchModel.TaskNo = txtSearchTask.Value.Trim();
//启用状态
searchModel.TaskTitle = inptSearchTitle.Value.Trim();
string selSearchTaskFlag = Request.Form["selSearchTaskFlag"].ToString();
if (selSearchTaskFlag != "0")
{
searchModel.TaskFlag = selSearchTaskFlag;
}
//启用状态
searchModel.EmployeeID = txtSearchScoreEmployee.Value;
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
searchModel.ScoreEmployee = userInfo.EmployeeID.ToString();
//查询数据
DataTable dtData = PerformanceGradeBus.SearchTaskInfo(searchModel);
//导出标题
string headerTitle = "任务编号|任务主题|考核期间类型|考核模板|被考评人|任务创建人|任务创建日期";
string[] header = headerTitle.Split('|');
//导出标题所对应的列字段名称
string columnFiled = "TaskNo|TaskTitle|TaskFlag|TemplateName|EmployeeName|CreateEmployeeName|CreateDate";
string[] field = columnFiled.Split('|');
XBase.Common.OutputToExecl.ExportToTable(this.Page, dtData, header, field, "待评分列表");
}