public IList <Teacher_course> SelectCourseList(string courseType, string collegeId)
{
string collegeSql = " ";
if (collegeId != null && !"".Equals(collegeId))
{
collegeSql = " college_id='" + collegeId + "' and ";
}
int currentYear = DateTime.Now.Year % 100;
int currentMonth = DateTime.Now.Month;
string opentime = currentYear + ((currentMonth >= 7) ? "02" : "01");
string sql = "select * from Teacher_course where " + collegeSql + " course_id in (select course_id from Course where course_type=@courseType and course_opentime=@opentime)";
SqlParameter[] pars =
{
new SqlParameter("@courseType", courseType),
new SqlParameter("@opentime", opentime)
};
DataTable dataTable = DBUtils.getDBUtils().getRecords(sql, pars);
IList <Teacher_course> courseList = new List <Teacher_course>();
foreach (DataRow row in dataTable.Rows)
{
Teacher_course course = new Teacher_course();
BeanUils.SetStringValues(course, row);
if (row["status"] != null)
{
course.status = Convert.ToInt32(row["status"]);
}
courseList.Add(course);
}
return(courseList);
}
/// <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);
}
Course ICourseDAL.SelectCourseById(string id)
{
string sql = "select * from course where course_id = @course_id";
SqlParameter[] parameters =
{
new SqlParameter("@course_id", id + "")
};
DataTable dataTable = DBUtils.getDBUtils().getRecords(sql, parameters);
Course course = new Course();
if (dataTable.Rows.Count > 0)
{
//mdzz
DataRow dataRow = dataTable.Rows[0];
BeanUils.SetStringValues(course, dataRow);
if (dataRow["status"] != null)
{
course.status = Convert.ToInt32(dataRow["status"]);
}
if (dataRow["course_credit"] != null)
{
course.course_credit = Convert.ToDecimal(dataRow["course_credit"]);
}
}
return(course);
}
/// <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);
}
public Student SelectStudentById(string stuId)
{
string sql = "select * from Student where student_id=@student_id ";
SqlParameter[] sqlParameter = new SqlParameter[]
{
new SqlParameter("@student_id", stuId)
};
DataTable dataTable = DBUtils.getDBUtils().getRecords(sql, sqlParameter);
Student student = new Student();
if (dataTable.Rows.Count > 0)
{
BeanUils.SetStringValues(student, dataTable.Rows[0]);
DataRow row = dataTable.Rows[0];
if (row["student_age"] != null)
{
student.student_age = Int32.Parse(row["student_age"].ToString());
}
if (row["status"] != null)
{
student.status = Int32.Parse(row["status"].ToString());
}
return(student);
}
return(null);
}
/// <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);
}
public IList <Teacher_course> SelectCourseList(string courseType, string collegeId)
{
string collegeSql = " ";
if (collegeId != null && !"".Equals(collegeId))
{
collegeSql = " college_id='" + collegeId + "' and ";
}
string sql = "select * from Teacher_course where " + collegeSql + " course_id in (select course_id from Course where course_type=@courseType)";
SqlParameter[] pars =
{
new SqlParameter("@courseType", courseType)
};
DataTable dataTable = DBUtils.getDBUtils().getRecords(sql, pars);
IList <Teacher_course> courseList = new List <Teacher_course>();
foreach (DataRow row in dataTable.Rows)
{
Teacher_course course = new Teacher_course();
BeanUils.SetStringValues(course, row);
if (row["status"] != null)
{
course.status = Convert.ToInt32(row["status"]);
}
courseList.Add(course);
}
return(courseList);
}
/// <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());
}
/// <summary>
/// 根据主键查询所有信息
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public Teacher_course SelectById(string id)
{
string sql = "select * from Teacher_course where teacher_course_id='" + id + "'";
DataTable dataTable = DBUtils.getDBUtils().getRecords(sql);
if (dataTable.Rows.Count > 0)
{
DataRow row = dataTable.Rows[0];
Teacher_course teacher_Course = new Teacher_course();
BeanUils.SetStringValues(teacher_Course, row);
if (row["course_credit"] != null)
{
teacher_Course.course_credit = Convert.ToDecimal(row["course_credit"]);
}
if (row["status"] != null)
{
teacher_Course.status = Convert.ToInt32(row["status"]);
}
return(teacher_Course);
}
return(null);
}
public College SelectById(string id)
{
string sql = "select * from College where college_id=@id";
SqlParameter[] parameters =
{
new SqlParameter("@id", id)
};
DataTable dataTable = DBUtils.getDBUtils().getRecords(sql, parameters);
College college = new College();
college.college_id = id;
if (dataTable.Rows.Count > 0)
{
DataRow row = dataTable.Rows[0];
BeanUils.SetStringValues(college, row);
college.sort = Convert.ToInt32(row["sort"]);
college.student_number = Convert.ToInt32(row["student_number"]);
college.class_number = Convert.ToInt32(row["class_number"]);
college.teacher_number = Convert.ToInt32(row["teacher_number"]);
}
return(college);
}
