/// <summary>
/// 通过检索条件查询培训信息
/// </summary>
/// <param name="model">查询条件</param>
/// <returns></returns>
public static DataTable SearchTrainingInfo(TrainingSearchModel model)
{
//获取登陆用户信息
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//设置公司代码
model.CompanyCD = userInfo.CompanyCD;
//执行查询并返回查询值
return TrainingDBHelper.SearchTrainingInfo(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;
//获取数据
TrainingSearchModel searchModel = new TrainingSearchModel();
//设置查询条件
//培训编号
searchModel.TrainingNo = txtTrainingNo.Value.Trim();
//培训名称
searchModel.TrainingName = txtTrainingName.Value.Trim();
//培训方式
searchModel.TrainingWayID = ddlTrainingWay.SelectedValue;
//开始时间
searchModel.StartDate = txtStartDate.Text.Trim();
searchModel.StartToDate = txtAsseEndDate.Text.Trim();
//查询数据
DataTable dt = TrainingBus.SearchTrainingInfo(searchModel);
OutputToExecl.ExportToTableFormat(this, dt,
new string[] { "培训编号", "培训名称", "培训方式", "培训老师", "开始时间", "结束时间", "培训地点"},
new string[] { "TrainingNo", "TrainingName", "TrainingWayName", "TrainingTeacher", "StartDate", "EndDate", "TrainingPlace" },
"培训列表");
}
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 SearchTrainingInfo(TrainingSearchModel model)
{
#region 查询语句
//查询SQL拼写
StringBuilder searchSql = new StringBuilder();
searchSql.AppendLine(" SELECT ");
searchSql.AppendLine(" A.ID AS ID ");
searchSql.AppendLine(" ,A.TrainingNo AS TrainingNo ");
searchSql.AppendLine(" ,A.TrainingName AS TrainingName ");
searchSql.AppendLine(" ,ISNULL(A.TrainingPlace, '') AS TrainingPlace ");
searchSql.AppendLine(" ,ISNULL(B.TypeName, '') AS TrainingWayName ");
searchSql.AppendLine(" ,ISNULL(A.TrainingTeacher,'') AS TrainingTeacher ");
searchSql.AppendLine(" ,ISNULL(CONVERT(VARCHAR(10),A.StartDate,21),'') AS StartDate ");
searchSql.AppendLine(" ,ISNULL(CONVERT(VARCHAR(10),A.EndDate,21),'') AS EndDate ");
searchSql.AppendLine(" FROM ");
searchSql.AppendLine(" officedba.EmployeeTraining A ");
searchSql.AppendLine(" LEFT JOIN officedba.CodePublicType B ");
searchSql.AppendLine(" ON A.TrainingWay = B.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.TrainingNo))
{
searchSql.AppendLine(" AND A.TrainingNo LIKE '%' + @TrainingNo + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TrainingNo", model.TrainingNo));
}
//培训名称
if (!string.IsNullOrEmpty(model.TrainingName))
{
searchSql.AppendLine(" AND A.TrainingName LIKE '%' + @TrainingName + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TrainingName", model.TrainingName));
}
//培训方式
if (!string.IsNullOrEmpty(model.TrainingWayID))
{
searchSql.AppendLine(" AND A.TrainingWay = @TrainingWayID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TrainingWayID", model.TrainingWayID));
}
//开始时间
if (!string.IsNullOrEmpty(model.StartDate))
{
searchSql.AppendLine(" AND A.StartDate >= @StartDate ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@StartDate", model.StartDate));
}
if (!string.IsNullOrEmpty(model.StartToDate))
{
searchSql.AppendLine(" AND A.StartDate <= @StartToDate ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@StartToDate", model.StartToDate));
}
//指定命令的SQL文
comm.CommandText = searchSql.ToString();
//执行查询
return SqlHelper.ExecuteSearch(comm);
}