/// <summary>
/// 获取教师所在学校所有班级
/// </summary>
/// <param name="TeacherId"></param>
/// <returns></returns>
public List <Model.Class> GetClass(int TeacherId)
{
List <Model.Class> list = new List <Model.Class>();
SqlConnection co = SQLSeverOpen();
SqlCommand cmd = new SqlCommand();
cmd.Connection = co;
cmd.CommandText = "select SchoolId from T_Base_Teacher where Id = " + TeacherId;
SqlDataReader reader = cmd.ExecuteReader();
int schoolId = -1;
while (reader.Read())
{
schoolId = Convert.ToInt32(reader["SchoolId"]);
}
co.Close();
co = SQLSeverOpen();
cmd = new SqlCommand();
cmd.Connection = co;
cmd.CommandText = "select * from T_Base_Class where SchoolId = " + schoolId;
reader = cmd.ExecuteReader();
while (reader.Read())
{
Model.Class tempClass = new Model.Class();
tempClass.Id = Convert.ToInt32(reader["Id"]);
tempClass.ClassName = Convert.ToString(reader["ClassName"]);
tempClass.SchoolId = Convert.ToInt32(reader["SchoolId"]);
list.Add(tempClass);
}
co.Close();
return(list);
}
/// <summary>
/// 根据学校不同获取班级信息
/// </summary>
/// <param name="SchoolId"></param>
/// <returns></returns>
public List <Model.Class> GetClass(int SchoolId)
{
List <Model.Class> list = new List <Model.Class>();
SqlConnection co = SQLSeverOpen();
SqlCommand cmd = new SqlCommand();
cmd.Connection = co;
cmd.CommandText = "select * from V_Class_School where SchoolId = " + SchoolId;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Model.Class tempClass = new Model.Class();
Model.School school = new Model.School();
tempClass.Id = Convert.ToInt32(reader["Id"]);
tempClass.ClassName = Convert.ToString(reader["ClassName"]);
tempClass.SchoolId = Convert.ToInt32(reader["SchoolId"]);
school.Id = Convert.ToInt32(reader["SchoolId"]);
school.SchoolName = Convert.ToString(reader["SchoolName"]);
school.SchoolNum = Convert.ToString(reader["SchoolNum"]);
tempClass.School = school;
list.Add(tempClass);
}
reader.Close();
co.Close();
return(list);
}
/// <summary>
/// 获取单个学生信息
/// </summary>
/// <param name="Id"></param>
/// <returns></returns>
public Model.Student GetStudent(int Id)
{
SqlConnection co = SQLSeverOpen();
SqlCommand cmd = new SqlCommand();
cmd.Connection = co;
Model.Student student = new Model.Student();
cmd.CommandText = "select * from V_Student_Class_School where Id = " + Id;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Model.Class tempClass = new Model.Class();
Model.School school = new Model.School();
student.Id = Convert.ToInt32(reader["Id"]);
student.StudentName = Convert.ToString(reader["StudentName"]);
student.ClassId = Convert.ToInt32(reader["ClassId"]);
student.StudentNum = Convert.ToString(reader["StudentNum"]);
student.StudentPWD = Convert.ToString(reader["StudentPWD"]);
student.Sex = Convert.ToInt32(reader["Sex"]);
tempClass.Id = Convert.ToInt32(reader["ClassId"]);
tempClass.ClassName = Convert.ToString(reader["ClassName"]);
tempClass.SchoolId = Convert.ToInt32(reader["SchoolId"]);
school.Id = Convert.ToInt32(reader["SchoolId"]);
school.SchoolName = Convert.ToString(reader["SchoolName"]);
school.SchoolNum = Convert.ToString(reader["SchoolNum"]);
student.Class = tempClass;
student.School = school;
}
reader.Close();
co.Close();
return(student);
}
//private int getCount(int TeacherId, int ClassId)
//{
// SqlConnection co = SQLSeverOpen();
// SqlCommand cmd = new SqlCommand();
// cmd.Connection = co;
// List<Model.Score> list = new List<Model.Score>();
// cmd.CommandText = "select COUNT(1) from V_QuestionHead_Select where TeacherId = " + TeacherId + " and ClassId = " + ClassId;
// return (int)cmd.ExecuteScalar();
//}
public List <Model.Score> GetTeacherClassScore(int TeacherId, int ClassId)
{
SqlConnection co = SQLSeverOpen();
SqlCommand cmd = new SqlCommand();
cmd.Connection = co;
List <Model.Score> list = new List <Model.Score>();
cmd.CommandText = "select top 10 * from V_QuestionHead_Select where TeacherId = " + TeacherId + " and ClassId = " + ClassId;
SqlDataReader reader = cmd.ExecuteReader();
List <Model.Question> questionList = new List <Model.Question>();
Model.Score score = new Model.Score();
while (reader.Read())
{
try
{
Model.Teacher teacher = new Model.Teacher();
Model.Class tempClass = new Model.Class();
Model.Question question = new Model.Question();
score.TeacherId = Convert.ToInt32(reader["TeacherId"]);
score.ClassId = Convert.ToInt32(reader["ClassId"]);
score.TotalScore = Convert.ToInt32(reader["TotalScore"]);
score.TeacherId = Convert.ToInt32(reader["TeacherId"]);
List <int> scoreList = GetTotalScore(TeacherId, ClassId);
score.QScore = scoreList;
question.Content = Convert.ToString(reader["Content"]);
questionList.Add(question);
;
tempClass.Id = Convert.ToInt32(reader["ClassId"]);
tempClass.ClassName = Convert.ToString(reader["ClassName"]);
teacher.Id = Convert.ToInt32(reader["TeacherId"]);
teacher.TeacherName = Convert.ToString(reader["TeacherName"]);
score.Teacher = teacher;
score.Class = tempClass;
score.Question = questionList;
list.Add(score);
}
catch (Exception)
{
continue;
}
}
reader.Close();
co.Close();
return(list);
}
public List <Model.Score> GetSchoolTeacherScore(int SchoolId)
{
SqlConnection co = SQLSeverOpen();
SqlCommand cmd = new SqlCommand();
cmd.Connection = co;
List <Model.Score> list = new List <Model.Score>();
cmd.CommandText = "select TeacherId,ClassId,ClassName,SUM(TotalScore)as TotalScore" +
",TeacherName from V_Write_Questionnaire_Class_School where SchoolId = " +
SchoolId + " group by TeacherId,ClassId,ClassName,TeacherName";
SqlDataReader reader = cmd.ExecuteReader();
List <Model.Score> tempList = new List <Model.Score>();
while (reader.Read())
{
Model.Score score = new Model.Score();
Model.Teacher teacher = new Model.Teacher();
Model.Class tempClass = new Model.Class();
score.TeacherId = Convert.ToInt32(reader["TeacherId"]);
score.ClassId = Convert.ToInt32(reader["ClassId"]);
score.TotalScore = Convert.ToInt32(reader["TotalScore"]);
score.TeacherId = Convert.ToInt32(reader["TeacherId"]);
tempClass.Id = Convert.ToInt32(reader["ClassId"]);
tempClass.ClassName = Convert.ToString(reader["ClassName"]);
teacher.Id = Convert.ToInt32(reader["TeacherId"]);
teacher.TeacherName = Convert.ToString(reader["TeacherName"]);
score.Teacher = teacher;
score.Class = tempClass;
tempList.Add(score);
}
reader.Close();
co.Close();
co = SQLSeverOpen();
cmd.Connection = co;
foreach (Model.Score item in tempList)
{
cmd.CommandText = "select COUNT(1) from V_Write_Questionnaire_Class_School where teacherId = " + item.TeacherId;
int count = (int)cmd.ExecuteScalar();
item.TotalScore = item.TotalScore / count;
list.Add(item);
}
reader.Close();
co.Close();
return(list);
}
/// <summary>
/// 获取老师所教的所有课程
/// </summary>
/// <param name="teacherId"></param>
/// <param name="pageSize"></param>
/// <param name="pageNumber"></param>
/// <returns></returns>
public List <Model.Teacher> GetList(int teacherId, int pageSize, int pageNumber)
{
List <Model.Teacher> list = new List <Model.Teacher>();
SqlConnection co = SQLSeverOpen();
SqlCommand cmd = new SqlCommand();
cmd.Connection = co;
cmd.CommandText = "select * from V_Teacher_Course_Class_School where Id = " + teacherId;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Model.Teacher teacher = new Model.Teacher();
Model.Course course = new Model.Course();
Model.Class tempClass = new Model.Class();
Model.School school = new Model.School();
teacher.Id = Convert.ToInt32(reader["Id"]);
teacher.TeacherName = Convert.ToString(reader["TeacherName"]);
teacher.TeacherWorkNum = Convert.ToString(reader["TeacherWorkNum"]);
teacher.SchoolId = Convert.ToInt32(reader["SchoolId"]);
teacher.IsLeader = Convert.ToInt32(reader["IsLeader"]);
teacher.Sex = Convert.ToInt32(reader["Sex"]);
teacher.CourseId = Convert.ToInt32(reader["CourseId"]);
course.Id = Convert.ToInt32(reader["CourseId"]);
course.CourseName = Convert.ToString(reader["CourseName"]);
try
{
tempClass.Id = Convert.ToInt32(reader["ClassId"]);
tempClass.ClassName = Convert.ToString(reader["ClassName"]);
tempClass.SchoolId = Convert.ToInt32(reader["SchoolId"]);
}
catch (Exception)
{
continue;
}
school.Id = Convert.ToInt32(reader["SchoolId"]);
school.SchoolName = Convert.ToString(reader["SchoolName"]);
school.SchoolNum = Convert.ToString(reader["SchoolNum"]);
teacher.School = school;
teacher.Course = course;
teacher.TempClass = tempClass;
list.Add(teacher);
}
return(list);
}
/// <summary>
/// 获取数据库中学生班级学校等基础信息
/// </summary>
/// <param name="pageSize"></param>
/// <param name="pageNumber"></param>
/// <returns></returns>
public List <Model.Student> GetList(int pageSize, int pageNumber, string ClassName, string StudentNum, string StudentName)
{
ClassName = "'%" + ClassName + "%'";
StudentNum = "'%" + StudentNum + "%'";
StudentName = "'%" + StudentName + "%'";
List <Model.Student> list = new List <Model.Student>();
SqlConnection co = SQLSeverOpen();
SqlCommand cmd = new SqlCommand();
cmd.Connection = co;
cmd.CommandText = "select top " + pageSize + " * from [V_Student_Class_School] where Id not in (select top " + (pageNumber - 1) * pageSize + " Id from [V_Student_Class_School] where ClassName like " + ClassName + " and StudentNum like " + StudentNum + " and StudentName like " + StudentName + ")and ClassName like " + ClassName + " and StudentNum like " + StudentNum + " and StudentName like " + StudentName;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Model.Student student = new Model.Student();
Model.Class tempClass = new Model.Class();
Model.School school = new Model.School();
student.Id = Convert.ToInt32(reader["Id"]);
student.StudentName = Convert.ToString(reader["StudentName"]);
student.Sex = Convert.ToInt32(reader["Sex"]);
student.ClassId = Convert.ToInt32(reader["ClassId"]);
student.StudentNum = Convert.ToString(reader["StudentNum"]);
student.StudentPWD = Convert.ToString(reader["StudentPWD"]);
tempClass.Id = Convert.ToInt32(reader["ClassId"]);
tempClass.ClassName = Convert.ToString(reader["ClassName"]);
tempClass.SchoolId = Convert.ToInt32(reader["SchoolId"]);
school.Id = Convert.ToInt32(reader["SchoolId"]);
school.SchoolName = Convert.ToString(reader["SchoolName"]);
school.SchoolNum = Convert.ToString(reader["SchoolNum"]);
student.Class = tempClass;
student.School = school;
list.Add(student);
}
reader.Close();
co.Close();
return(list);
}