/// <summary>
/// 取得课程列表
/// </summary>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="where"></param>
/// <param name="orderBy"></param>
/// <returns></returns>
public List<Course_CourseInfo> GetTrainingInfoList(int pageSize, int pageIndex, string where, string orderBy)
{
int start = (pageIndex - 1) * pageSize + 1;
int end = pageIndex * pageSize;
StringBuilder sql = new StringBuilder();
sql.Append("select *,");
sql.Append("(select count(1) from Traning_ApplyApplication where Delflag=0 and Status=2 and TraningId=T.Id) as Trainers,");
sql.Append("(select Title from Organ_Detail where Delflag=0 and Id=T.OrganId) as OrganName,");
sql.Append("(select Title from Traning_InfoFk where Delflag=0 and CategoryType=6 and Id=T.TeacherTitle) as TeacherTitleName");
sql.Append(" from (select A.*,ROW_NUMBER() over (order by " + orderBy + " desc) as [RowNum] from [dbo].[Traning_Detail] as A,[dbo].[Traning_Teacher] as B,dbo.PlatformManager_Detail as C");
sql.Append(" where B.PlatformManagerId = C.Id and A.Id = B.TraningId and C.Delflag =0 and B.Delflag = 0 and A.Delflag=0");
if (!string.IsNullOrEmpty(where))
sql.Append(" and " + where);
sql.Append(") as T where [RowNum] between " + start + " and " + end);
List<Course_CourseInfo> list = new List<Course_CourseInfo>();
using (IDataReader reader = MSEntLibSqlHelper.ExecuteDataReaderBySql(sql.ToString()))
{
while (reader.Read())
{
Course_CourseInfo model = new Course_CourseInfo();
ConvertToModel(reader, model, 1);
list.Add(model);
}
}
return list;
}
/// <summary>
/// 将结果集转化为Model
/// </summary>
/// <param name="reader"></param>
/// <param name="model"></param>
/// <param name="Type"></param>
private void ConvertToModel(IDataReader reader, Course_CourseInfo model, int Type)
{
if (reader["Id"] != DBNull.Value)
model.Id = Convert.ToInt32(reader["Id"]);
if (reader["Title"] != DBNull.Value)
model.Title = reader["Title"].ToString();
if (reader["Content"] != DBNull.Value)
model.Content = reader["Content"].ToString();
if (reader["Pic"] != DBNull.Value)
model.Pic = reader["Pic"].ToString();
if (reader["Creater"] != DBNull.Value)
model.Creater = Convert.ToInt32(reader["Creater"]);
if (reader["OrganId"] != DBNull.Value)
model.OrganId = Convert.ToInt32(reader["OrganId"]);
if (reader["TraingField"] != DBNull.Value)
model.TraingField = Convert.ToInt32(reader["TraingField"]);
if (reader["TraingCategory"] != DBNull.Value)
model.TraingCategory = Convert.ToInt32(reader["TraingCategory"]);
if (reader["TraingTopic"] != DBNull.Value)
model.TraingTopic = Convert.ToInt32(reader["TraingTopic"]);
if (reader["TraningObject"] != DBNull.Value)
model.TraningObject = reader["TraningObject"].ToString();
if (reader["Subject"] != DBNull.Value)
model.Subject = reader["Subject"].ToString();
if (reader["StudyLevel"] != DBNull.Value)
model.StudyLevel = reader["StudyLevel"].ToString();
if (reader["TotalTime"] != DBNull.Value)
model.TotalTime = Convert.ToDouble(reader["TotalTime"]);
if (reader["TrainingForm"] != DBNull.Value)
model.TrainingForm = Convert.ToInt32(reader["TrainingForm"]);
if (reader["TeacherTitle"] != DBNull.Value)
model.TeacherTitle = Convert.ToInt32(reader["TeacherTitle"]);
if (reader["TeacherName"] != DBNull.Value)
model.TeacherName = reader["TeacherName"].ToString();
if (reader["TeacherFrom"] != DBNull.Value)
model.TeacherFrom = reader["TeacherFrom"].ToString();
if (reader["TeacherPic"] != DBNull.Value)
model.TeacherPic = reader["TeacherPic"].ToString();
if (reader["Outline"] != DBNull.Value)
model.Outline = reader["Outline"].ToString();
if (reader["PartitionId"] != DBNull.Value)
model.PartitionId = Convert.ToInt32(reader["PartitionId"]);
if (reader["OutSideType"] != DBNull.Value)
model.OutSideType = Convert.ToInt32(reader["OutSideType"]);
if (reader["OutSideLink"] != DBNull.Value)
model.OutSideLink = reader["OutSideLink"].ToString();
if (reader["Range"] != DBNull.Value)
model.Range = Convert.ToInt32(reader["Range"]);
if (reader["Status"] != DBNull.Value)
model.Status = Convert.ToInt32(reader["Status"]);
if (reader["ApplyRemark"] != DBNull.Value)
model.ApplyRemark = reader["ApplyRemark"].ToString();
if (reader["Display"] != DBNull.Value)
model.Display = Convert.ToBoolean(reader["Display"]);
if (reader["Delflag"] != DBNull.Value)
model.Delflag = Convert.ToBoolean(reader["Delflag"]);
if (reader["CreateDate"] != DBNull.Value)
model.CreateDate = Convert.ToDateTime(reader["CreateDate"]);
if (reader["ParentOrganId"] != DBNull.Value)
model.ParentOrganId = Convert.ToInt32(reader["ParentOrganId"]);
if (reader["OrganName"] != DBNull.Value)
model.OrganName = reader["OrganName"].ToString();
if (Type == 1)
{
if (reader["Trainers"] != DBNull.Value)
model.Trainers = Convert.ToInt32(reader["Trainers"]);
if (reader["TeacherTitleName"] != DBNull.Value)
model.TeacherTitleName = reader["TeacherTitleName"].ToString();
}
}
/// <summary>
/// 取得指定课程的课程信息
/// </summary>
/// <param name="Id"></param>
/// <returns></returns>
public Course_CourseInfo GetTrainingInfoById(int Id)
{
StringBuilder sql = new StringBuilder();
sql.Append("select *,");
sql.Append("(select Title from Organ_Detail where Delflag=0 and Id=T.OrganId) as OrganName");
sql.Append(" from (select * from [dbo].[Traning_Detail]");
sql.Append(" where Id = " + Id.ToString() + ") as T");
using (IDataReader reader = MSEntLibSqlHelper.ExecuteDataReaderBySql(sql.ToString()))
{
if (reader.Read())
{
Course_CourseInfo model = new Course_CourseInfo();
ConvertToModel(reader, model, 2);
return model;
}
else
{
return null;
}
}
}
