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