/// <summary>
/// 查询培训考核信息
/// </summary>
/// <param name="model">查询条件</param>
/// <returns></returns>
public static DataTable SearchTrainingAsseInfo(TrainingAsseSearchModel model)
{
//获取登陆用户信息
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//设置公司代码
model.CompanyCD = userInfo.CompanyCD;
return(TrainingAsseDBHelper.SearchTrainingAsseInfo(model));
}
protected void btnImport_Click(object sender, System.Web.UI.ImageClickEventArgs e)
{
try
{
string orderString = hiddExpOrder.Value.Trim(); //排序
string order = "asc"; //排序：降序
string orderBy = (!string.IsNullOrEmpty(orderString)) ? orderString.Substring(0, orderString.Length - 2) : "ID"; //要排序的字段，如果为空，默认为"ID"
if (orderString.EndsWith("_d"))
{
order = "desc";//排序：降序
}
string ord = " ORDER BY " + orderBy + " " + order;
//获取数据
TrainingAsseSearchModel searchModel = new TrainingAsseSearchModel();
//设置查询条件
//考核编号
searchModel.AsseNo = txtTrainingAsseNo.Value.Trim();
//培训编号
searchModel.TrainingNo = txtTrainingNo.Value.Trim();
//培训名称
searchModel.TrainingName = txtTrainingName.Value.Trim();
//培训方式
searchModel.TrainingWayID = ddlTrainingWay.SelectedValue;
//考评人
searchModel.CheckPerson = txtCheckPerson.Value.Trim();
//考评时间
searchModel.AsseDate = txtAsseDate.Value.Trim();
searchModel.AsseEndDate = txtAsseEndDate.Value.Trim();
//查询数据
DataTable dt = TrainingAsseBus.SearchTrainingAsseInfo(searchModel);
OutputToExecl.ExportToTableFormat(this, dt,
new string[] { "考核编号", "培训编号", "培训名称", "培训方式", "培训老师", "考评人", "考核方式", "考评时间" },
new string[] { "AsseNo", "TrainingNo", "TrainingName", "TrainingWayName", "TrainingTeacher", "CheckPerson", "AsseWay", "AsseDate" },
"培训考核列表");
}
catch
{
ClientScript.RegisterStartupScript(this.GetType(), "Exp", "<script language=javascript>showPopup('../../../Images/Pic/Close.gif','../../../Images/Pic/note.gif','导出发生异常');</script>");
}
}
/// <summary>
/// 查询培训考核信息
/// </summary>
/// <param name="model">查询条件</param>
/// <returns></returns>
public static DataTable SearchTrainingAsseInfo(TrainingAsseSearchModel model)
{
#region 查询语句
//查询SQL拼写
StringBuilder searchSql = new StringBuilder();
searchSql.AppendLine(" SELECT ");
searchSql.AppendLine(" A.ID AS ID ");
searchSql.AppendLine(" ,A.AsseNo AS AsseNo ");
searchSql.AppendLine(" ,A.TrainingNo AS TrainingNo ");
searchSql.AppendLine(" ,ISNULL(B.TrainingName, '') AS TrainingName ");
searchSql.AppendLine(" ,ISNULL(B.TrainingTeacher,'') AS TrainingTeacher ");
searchSql.AppendLine(" ,ISNULL(C.TypeName,'') AS TrainingWayName ");
searchSql.AppendLine(" ,ISNULL(A.CheckPerson,'') AS CheckPerson ");
searchSql.AppendLine(" ,ISNULL(A.CheckWay,'') AS AsseWay ");
searchSql.AppendLine(" ,ISNULL(CONVERT(VARCHAR(10),A.CheckDate,21),'') AS AsseDate ");
searchSql.AppendLine(" FROM ");
searchSql.AppendLine(" officedba.TrainingAsse A ");
searchSql.AppendLine(" LEFT JOIN officedba.EmployeeTraining B ON ");
searchSql.AppendLine(" A.CompanyCD = B.CompanyCD ");
searchSql.AppendLine(" AND A.TrainingNo = B.TrainingNo ");
searchSql.AppendLine(" LEFT JOIN officedba.CodePublicType C ON ");
searchSql.AppendLine(" B.TrainingWay = C.ID ");
searchSql.AppendLine(" WHERE ");
searchSql.AppendLine(" A.CompanyCD = @CompanyCD ");
#endregion
//定义查询的命令
SqlCommand comm = new SqlCommand();
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
//考核编号
if (!string.IsNullOrEmpty(model.AsseNo))
{
searchSql.AppendLine(" AND A.AsseNo LIKE '%' + @AsseNo + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@AsseNo", model.AsseNo));
}
//培训编号
if (!string.IsNullOrEmpty(model.TrainingNo))
{
searchSql.AppendLine(" AND A.TrainingNo LIKE '%' + @TrainingNo + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TrainingNo", model.TrainingNo));
}
//培训名称
if (!string.IsNullOrEmpty(model.TrainingName))
{
searchSql.AppendLine(" AND B.TrainingName LIKE '%' + @TrainingName + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TrainingName", model.TrainingName));
}
//培训方式
if (!string.IsNullOrEmpty(model.TrainingWayID))
{
searchSql.AppendLine(" AND B.TrainingWay = @TrainingWayID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TrainingWayID", model.TrainingWayID));
}
//考评人
if (!string.IsNullOrEmpty(model.CheckPerson))
{
searchSql.AppendLine(" AND A.CheckPerson LIKE '%' + @CheckPerson + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CheckPerson", model.CheckPerson));
}
//考核时间
if (!string.IsNullOrEmpty(model.AsseDate))
{
searchSql.AppendLine(" AND A.CheckDate >= @CheckDate ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CheckDate", model.AsseDate));
}
if (!string.IsNullOrEmpty(model.AsseEndDate))
{
searchSql.AppendLine(" AND A.CheckDate <= @AsseEndDate ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@AsseEndDate", model.AsseEndDate));
}
//指定命令的SQL文
comm.CommandText = searchSql.ToString();
//执行查询
return(SqlHelper.ExecuteSearch(comm));
}