public IList <Course> SelectCoursesOrderBySelectCount(CourseType courseType, int size)
{
string sql = " select top @size " + BaseInfo
+ " from (select course_id, COUNT(*) as c from Course_choosing group by course_id) as Id_C(course_id,c),Course c "
+ " where Id_C.course_id=c.course_id and course_type = @course_type "
+ " order by Id_c.c desc ";
string courseTypeDesc = CourseTypeUtils.GetInfo(courseType);//获取课程类型的中文表示（字段上的注解）
SqlParameter[] parameters =
{
new SqlParameter("@size", size),
new SqlParameter("@course_type", courseTypeDesc)
};
//查询
DataTable dataTable = DBUtils.getDBUtils().getRecords(sql, parameters);
//存放结果
IList <Course> courseList = new List <Course>();
//遍历
foreach (DataRow dataRow in dataTable.Rows)
{
//public const string BaseInfo = "course_id,course_name,course_credit,course_hour,college_id ";
Course course = new Course();
course.course_id = dataRow["course_id"].ToString();
course.course_name = dataRow["course_name"].ToString();
course.course_credit = decimal.Parse(dataRow["course_credit"].ToString());
course.course_hour = dataRow["course_hour"].ToString();
course.college_id = dataRow["college_id"].ToString();
courseList.Add(course);
}
return(courseList);
}
public IList <Course> SelectCoursesOrderByDate(CourseType courseType, int size)
{
string sql = "SELECT TOP @size " + BaseInfo + " from Course "
+ "where course_type = @course_type and status='1' order by course_id ";
string courseTypeDesc = CourseTypeUtils.GetInfo(courseType);//获取课程类型的中文表示（字段上的注解）
SqlParameter[] parameters =
{
new SqlParameter("@size", size),
new SqlParameter("@course_type", courseTypeDesc)
};
//查询
DataTable dataTable = DBUtils.getDBUtils().getRecords(sql, parameters);
//存放结果
IList <Course> courseList = new List <Course>();
//遍历
foreach (DataRow dataRow in dataTable.Rows)
{
//public const string BaseInfo = "course_id,course_name,course_credit,course_hour,college_id ";
Course course = new Course();
course.course_id = dataRow["course_id"].ToString();
course.course_name = dataRow["course_name"].ToString();
course.course_credit = decimal.Parse(dataRow["course_credit"].ToString());
course.course_hour = dataRow["course_hour"].ToString();
course.college_id = dataRow["college_id"].ToString();
courseList.Add(course);
}
return(courseList);
}
/// <summary>
/// 获取导航栏信息
/// </summary>
/// <returns></returns>
public ActionResult GetNavbarInfo()
{
//courseTypeName,courseTypeId
IList <Object> courseTypes = CourseTypeUtils.GetCourseTypes();
IList <College> colleges = courseService.SelectColleges();
return(Json(new { courseTypes = courseTypes, colleges = colleges, courseTypeCount = courseTypes.Count, collegeCount = colleges.Count }));
}
public IList <Course> SelectCourse(CourseType courseType, string courseOpentime, string collegeId, string courseName,
int page, int size)
{
string sql = "select " + BaseInfo +
" from Course where course_type=@courseType ";
// and course_opentime=@courseOpentime and college_id = @collegeId order by course_id
string courseTypeDesc = CourseTypeUtils.GetInfo(courseType);//获取课程类型的中文表示（字段上的注解）
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@courseType", courseTypeDesc));
//拼接sql
#region 拼接sql，并把相应参数放入list
if (courseOpentime != null && !"".Equals(courseOpentime))
{
sql += " and course_opentime=@courseOpentime ";
paramList.Add(new SqlParameter("@courseOpentime", courseOpentime));
}
if (collegeId != null && !"".Equals(collegeId))
{
sql += " and college_id = @collegeId ";
paramList.Add(new SqlParameter("@collegeId", collegeId));
}
if (courseName != null && !"".Equals(courseName))
{
sql += " and course_name like @course_name ";
courseName = "%" + courseName + "%";
paramList.Add(new SqlParameter("@course_name", courseName));
}
sql += " order by course_id ";
#endregion
SqlParameter[] parameters = paramList.ToArray();
//查询
DataTable dataTable = DBUtils.getDBUtils().getRecordsWithPage(sql, parameters, size, page);
//存放结果
IList <Course> courseList = new List <Course>();
//遍历
foreach (DataRow dataRow in dataTable.Rows)
{
//public const string BaseInfo = "course_id,course_name,course_credit,course_hour,college_id ";
Course course = new Course();
course.course_id = dataRow["course_id"].ToString();
course.course_name = dataRow["course_name"].ToString();
course.course_credit = decimal.Parse(dataRow["course_credit"].ToString());
course.course_hour = dataRow["course_hour"].ToString();
course.college_id = dataRow["college_id"].ToString();
courseList.Add(course);
}
return(courseList);
}
public IList <Course> SelectCourseByType(CourseType courseType, int size)
{
String sql = "select course_id, course_name, course_credit, course_opentime, course_type," +
"status, course_photo from Course where course_type like @courseType";
SqlParameter[] pars =
{
new SqlParameter("@courseType", CourseTypeUtils.GetInfo(courseType))
};
return(DoSelect(sql, pars, size));
}
//public const string BaseInfo = "course_id,course_name,course_credit,course_hour,course_type,course_theory,course_experiment,course_opentime,course_prior,status";
public int SelectCountByType(CourseType courseType, int status)
{
string sql = "select count(*) as result from Course where course_type = @course_type and status = @status";
string courseTypeDesc = CourseTypeUtils.GetInfo(courseType);//获取课程类型的中文表示（字段上的注解）
SqlParameter[] parameters =
{
new SqlParameter("@course_type", courseTypeDesc),
new SqlParameter("@status", status),
};
//查询
DataTable dataTable = DBUtils.getDBUtils().getRecords(sql, parameters);
DataRow dataRow = dataTable.Rows[0];
int count = Convert.ToInt32(dataRow["result"]);
return(count);
}
public ActionResult ChooseCourseList(String courseTypeName)
{
CourseType courseType = CourseTypeUtils.GetCourseType(courseTypeName);
Login login = (Login)Session["loginInfo"];
Student student = studentService.GetStudentById(login.username);
string collegeId = student.college_id;
//学生可以选择任意学院的公共选修课，所以不需要指定学院id
if (courseTypeName.Equals("公共选修课"))
{
collegeId = null;
}
//所有相应课程类型的课程
IList <Teacher_course> teacher_courses = courseService.SelectTeacherCourseList(courseTypeName, collegeId);
IList <Course_choosing> course_Choosings = courseService.SelectCourseChoosingListByStu(student.student_id);
return(Json(new { t_course_count = teacher_courses.Count, t_course = teacher_courses,
course_choose_count = course_Choosings.Count, course_choose = course_Choosings }));
}
/// <summary>
/// 查询课程总数
/// </summary>
/// <param name="courseType"></param>
/// <param name="courseOpentime"></param>
/// <param name="collegeId"></param>
/// <param name="courseName"></param>
/// <returns></returns>
public int SelectCount(CourseType courseType, string courseOpentime, string collegeId, string courseName)
{
string sql = "select count(*) as res " +
" from Course where course_type=@courseType ";
// and course_opentime=@courseOpentime and college_id = @collegeId order by course_id
string courseTypeDesc = CourseTypeUtils.GetInfo(courseType);//获取课程类型的中文表示（字段上的注解）
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@courseType", courseTypeDesc));
//拼接sql
#region 拼接sql，并把相应参数放入list
if (courseOpentime != null && !"".Equals(courseOpentime))
{
sql += " and course_opentime=@courseOpentime ";
paramList.Add(new SqlParameter("@courseOpentime", courseOpentime));
}
if (collegeId != null && !"".Equals(collegeId))
{
sql += " and college_id = @collegeId ";
paramList.Add(new SqlParameter("@collegeId", collegeId));
}
if (courseName != null && !"".Equals(courseName))
{
sql += " and course_name like @course_name";
courseName = "%" + courseName + "%";
paramList.Add(new SqlParameter("@course_name", courseName));
}
#endregion
SqlParameter[] parameters = paramList.ToArray();
//查询
DataTable dataTable = DBUtils.getDBUtils().getRecords(sql, parameters);
DataRow row = dataTable.Rows[0];
int count = Convert.ToInt32(row["res"]);
return(count);
}
public IList <Course> SelectCoursesBaseInfo(CourseType courseType, int status,
int start, int size)
{
//string sql = "SELECT TOP @size " + BaseInfo + " from Course "
// + " where course_type = @course_type and status = @status and course_id not in "
// + " (SELECT TOP (@size2 * ( @start - 1 )) course_id FROM Course ORDER BY course_id) "
// + " ORDER BY course_id ";
string sql = "SELECT " + BaseInfo + " from Course where course_type = @course_type and status = @status ORDER BY course_id";
string courseTypeDesc = CourseTypeUtils.GetInfo(courseType);//获取课程类型的中文表示（字段上的注解）
SqlParameter[] parameters =
{
//new SqlParameter("@size",size),
//new SqlParameter("@start",start),
new SqlParameter("@course_type", courseTypeDesc),
//new SqlParameter("@size2",size),
new SqlParameter("@status", status),
};
//查询
DataTable dataTable = DBUtils.getDBUtils().getRecordsWithPage(sql, parameters, size, start);
//存放结果
IList <Course> courseList = new List <Course>();
//遍历
foreach (DataRow dataRow in dataTable.Rows)
{
//public const string BaseInfo = "course_id,course_name,course_credit,course_hour,college_id ";
Course course = new Course();
course.course_id = dataRow["course_id"].ToString();
course.course_name = dataRow["course_name"].ToString();
course.course_credit = decimal.Parse(dataRow["course_credit"].ToString());
course.course_hour = dataRow["course_hour"].ToString();
course.course_experiment = dataRow["college_id"].ToString();
courseList.Add(course);
}
return(courseList);
}
