/// <summary>
/// 查询选课的具体信息
/// </summary>
/// <param name="courseChoosingId"></param>
/// <returns></returns>
public Course_choosing SelectCourseChoosingDetails(string courseChoosingId)
{
string sql = "select * from Course_choosing where course_choosing_id='" + courseChoosingId + "'";
DataTable dataTable = DBUtils.getDBUtils().getRecords(sql);
if (dataTable.Rows.Count > 0)
{
DataRow row = dataTable.Rows[0];
Course_choosing course = new Course_choosing();
int successCount = BeanUils.SetStringValues(course, row);
try
{
course.status = Convert.ToInt32(row["status"]);
course.usual_grade = Convert.ToDecimal(row["usual_grade"]);
course.test_grade = Convert.ToDecimal(row["test_grade"]);
course.course_credit = Convert.ToDecimal(row["course_credit"]);
course.total_grade = Convert.ToDecimal(row["total_grade"]);
}
catch (Exception e)
{
}
return(course);
}
return(null);
}
/// <summary>
/// 查询学生选课记录,查询全部记录，所以当某些字段为null时，会抛出异常
/// </summary>
/// <param name="stuId">学生id</param>
/// <returns></returns>
public IList <Course_choosing> SelectCourseChoosingListByStu(string stuId)
{
string sql = "select * from Course_choosing where student_id='" + stuId + "'";
DataTable dataTable = DBUtils.getDBUtils().getRecords(sql);
IList <Course_choosing> courses = new List <Course_choosing>();
foreach (DataRow row in dataTable.Rows)
{
Course_choosing course = new Course_choosing();
int successCount = BeanUils.SetStringValues(course, row);
try
{
course.status = Convert.ToInt32(row["status"]);
course.usual_grade = Convert.ToDecimal(row["usual_grade"]);
course.test_grade = Convert.ToDecimal(row["test_grade"]);
course.course_credit = Convert.ToDecimal(row["course_credit"]);
course.total_grade = Convert.ToDecimal(row["total_grade"]);
} catch (Exception e)
{
}
courses.Add(course);
}
return(courses);
}
/// <summary>
/// 查询学生选课记录,查询全部记录，所以当某些字段为null时，会抛出异常
/// </summary>
/// <param name="stuId">学生id</param>
/// <returns></returns>
public IList <Course_choosing> SelectCourseChoosingListByStu(string stuId)
{
int currentYear = DateTime.Now.Year % 100;
int currentMonth = DateTime.Now.Month;
string opentime = currentYear + ((currentMonth >= 7) ? "02" : "01");
string sql = "select * from Course_choosing cc ,Course c where cc.course_id=c.course_id and cc.student_id='" + stuId + "' and c.course_opentime='" + opentime + "'";
DataTable dataTable = DBUtils.getDBUtils().getRecords(sql);
IList <Course_choosing> courses = new List <Course_choosing>();
foreach (DataRow row in dataTable.Rows)
{
Course_choosing course = new Course_choosing();
int successCount = BeanUils.SetStringValues(course, row);
try
{
course.status = Convert.ToInt32(row["status"]);
course.usual_grade = Convert.ToDecimal(row["usual_grade"]);
course.test_grade = Convert.ToDecimal(row["test_grade"]);
course.course_credit = Convert.ToDecimal(row["course_credit"]);
course.total_grade = Convert.ToDecimal(row["total_grade"]);
}
catch (Exception e)
{
}
courses.Add(course);
}
return(courses);
}
/// <summary>
/// 选课
/// </summary>
/// <param name="student"></param>
/// <param name="courseInfo"></param>
public void ChooseCourse(Student student, Teacher_course courseInfo)
{
Course_choosing course_Choosing = new Course_choosing();
BeanUils.TransFields(student, course_Choosing);
BeanUils.TransFields(courseInfo, course_Choosing);
//学生课程表id由教师选课表id和学生id联合组成
course_Choosing.course_choosing_id = courseInfo.teacher_course_id + student.student_id;
//status默认为1
string sql = "insert into Course_choosing(course_choosing_id,student_id,student_name,teacher_course_id,teacher_id,teacher_name,course_id,course_name,classroom_id,status,course_credit) "
+ " values(@course_choosing_id,@student_id,@student_name,@teacher_course_id,@teacher_id,@teacher_name,@course_id,@course_name,@classroom_id,@status,@course_credit)";
IList <SqlParameter> sqlParameterList = BeanUils.SetInSQL(sql, course_Choosing);
DBUtils.getDBUtils().cud(sql, sqlParameterList.ToArray());
}
public IList <Course_choosing> SelectAllStudentCourse(string courseid)
{
string sql = "select * from Course_choosing where course_id = @courseid";
SqlParameter[] pars = { new SqlParameter("@courseid", courseid) };
DataTable dataTable = DBUtils.getDBUtils().getRecords(sql, pars);
IList <Course_choosing> CourseList = new List <Course_choosing>();
foreach (DataRow dataRow in dataTable.Rows)
{
Course_choosing course = new Course_choosing
{
course_choosing_id = dataRow["course_choosing_id"].ToString(),
student_id = dataRow["student_id"].ToString(),
student_name = dataRow["student_name"].ToString(),
teacher_course_id = dataRow["teacher_course_id"].ToString(),
teacher_id = dataRow["teacher_id"].ToString(),
teacher_name = dataRow["teacher_name"].ToString(),
course_id = dataRow["course_id"].ToString(),
course_name = dataRow["course_name"].ToString(),
/*usual_grade = Convert.ToDecimal(dataRow["usual_grade"]),
* test_grade = Convert.ToDecimal(dataRow["test_grade"]),
* total_grade = Convert.ToDecimal(dataRow["total_grade"]),*/
course_credit = Convert.ToDecimal(dataRow["course_credit"]),
_class = dataRow["class"].ToString(),
status = Convert.ToInt32(dataRow["status"]),
//yuliu1 = dataRow["yuliu1"].ToString(),
//yuliu2 = dataRow["yuliu2"].ToString(),
//yuliu3 = dataRow["yuliu3"].ToString()
};
if (dataRow["usual_grade"] != null && dataRow["usual_grade"].ToString() != "")
{
course.usual_grade = Convert.ToDecimal(dataRow["usual_grade"]);
}
if (dataRow["test_grade"] != null && dataRow["test_grade"].ToString() != "")
{
course.test_grade = Convert.ToDecimal(dataRow["test_grade"]);
}
if (dataRow["total_grade"] != null && dataRow["total_grade"].ToString() != "")
{
course.total_grade = Convert.ToDecimal(dataRow["total_grade"]);
}
CourseList.Add(course);
}
return(CourseList);
}
public ActionResult ChooseCoursePage(string courseTypeName)
{
if (courseTypeName == null || courseTypeName.Equals(""))
{
return(View("Error"));
}
Login login = (Login)Session["loginInfo"];
if (login == null)
{
//未登录
//跳转到登录页面
Session["prePage"] = "/Student/Index";//将当前页面地址放入session，登录后返回到该页面
return(RedirectToAction("Index", "Login"));
}
Student student = studentService.GetStudentById(login.username);
string collegeId = student.college_id;
//学生可以选择任意学院的公共选修课，所以不需要指定学院id
if (courseTypeName.Equals("公共选修课"))
{
collegeId = null;
}
//所有相应课程类型的课程
IList <Teacher_course> teacher_courses_all = courseService.SelectTeacherCourseList(courseTypeName, collegeId);
IList <Course_choosing> course_choosings = courseService.SelectCourseChoosingListByStu(student.student_id);
IList <Teacher_course> teacher_courses = new List <Teacher_course>();
//筛选teacher_courses_all中还没选的课程,并且查询出课程信息
foreach (Teacher_course t_course in teacher_courses_all)
{
Course course = courseService.SelectCourseById(t_course.course_id);
t_course.College = courseService.SelectCollegeById(course.college_id);
t_course.Course = course;
for (int i = 0; i < course_choosings.Count; i++)
{
Course_choosing course_Choosing = course_choosings[i];
if (!course_Choosing.course_id.Equals(t_course.course_id))
{
if (i == (course_choosings.Count - 1))
{
teacher_courses.Add(t_course);
}
else
{
continue;
}
}
else
{
//该课程已经被选过了
break;
}
}
}
if (course_choosings.Count == 0)
{
//如果没有已选课程
teacher_courses = teacher_courses_all;
}
//查询course_choosings中的课程信息
IList <Course> choosedCourseList = new List <Course>();
foreach (Course_choosing course_chooseing in course_choosings)
{
Course course = courseService.SelectCourseById(course_chooseing.course_id);
choosedCourseList.Add(course);
}
ViewData["teacher_courses"] = teacher_courses;
ViewData["course_choosings"] = choosedCourseList;
return(View());
}
/// <summary>
/// 查询学生的各科成绩
/// </summary>
/// <param name="stuId"></param>
/// <returns>封装成绩字段和课程名，不查询其他信息</returns>
public IList <Course_choosing> SelectGrade(string stuId, int year)
{
//上半学期和下半学期
string year01 = (year % 100) + "01";//将201702转化为1702格式
string year02 = (year % 100) + "02";
string sql = "select c.course_name,usual_grade,test_grade,total_grade,c.course_credit from Course_choosing cs , Course c "
+ " where student_id = @stuId and cs.course_id = c.course_id and c.course_opentime in (@year01, @year02)";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@stuId", stuId));
paramList.Add(new SqlParameter("@year01", year01));
paramList.Add(new SqlParameter("@year02", year02));
DataTable dataTable = DBUtils.getDBUtils().getRecords(sql, paramList.ToArray());
IList <Course_choosing> courses = new List <Course_choosing>();
foreach (DataRow row in dataTable.Rows)
{
Course_choosing course = new Course_choosing();
if (row["course_name"] != null)
{
course.course_name = Convert.ToString(row["course_name"]);
}
if (row["usual_grade"] != null)
{
try
{
course.usual_grade = Convert.ToDecimal(row["usual_grade"]);
}
catch (Exception)
{
course.usual_grade = null;
}
}
if (row["test_grade"] != null)
{
try
{
course.test_grade = Convert.ToDecimal(row["test_grade"]);
}
catch (Exception)
{
course.usual_grade = null;
}
}
if (row["total_grade"] != null)
{
try
{
course.total_grade = Convert.ToDecimal(row["total_grade"]);
}
catch (Exception)
{
course.usual_grade = null;
}
}
if (row["course_credit"] != null)
{
try
{
course.course_credit = Convert.ToDecimal(row["course_credit"]);
}
catch (Exception)
{
course.course_credit = null;
}
}
courses.Add(course);
}
return(courses);
}
