/// <summary>
/// 获取编辑时单个教师的信息
/// </summary>
/// <param name="Id"></param>
/// <returns></returns>
public Model.Teacher GetTeacher(int Id)
{
Model.Teacher teacher = new Model.Teacher();
Model.Course course = new Model.Course();
Model.School school = new Model.School();
SqlConnection co = SQLSeverOpen();
SqlCommand cmd = new SqlCommand();
cmd.Connection = co;
cmd.CommandText = "select * from V_Teacher_Course_School where Id = " + Id;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
teacher.Id = Convert.ToInt32(reader["Id"]);
teacher.TeacherName = Convert.ToString(reader["TeacherName"]);
teacher.TeacherWorkNum = Convert.ToString(reader["TeacherWorkNum"]);
teacher.TeacherPWD = Convert.ToString(reader["TeacherPWD"]);
teacher.SchoolId = Convert.ToInt32(reader["SchoolId"]);
school.Id = Convert.ToInt32(reader["SchoolId"]);
school.SchoolName = Convert.ToString(reader["SchoolName"]);
school.SchoolNum = Convert.ToString(reader["SchoolNum"]);
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"]);
teacher.Course = course;
teacher.School = school;
}
co.Close();
return(teacher);
}
/// <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="pageSize"></param>
/// <param name="pageNumber"></param>
/// <returns></returns>
public List <Model.School> GetList(int pageSize, int pageNumber, string SchoolName, string Address)
{
SchoolName = "'%" + SchoolName + "%'";
Address = "'%" + Address + "%'";
List <Model.School> list = new List <Model.School>();
SqlConnection co = SQLSeverOpen();
SqlCommand cmd = new SqlCommand();
cmd.Connection = co;
cmd.CommandText = "select top " + pageSize + " * from T_Base_School where Id not in (select top " + (pageNumber - 1) * pageSize + " Id from T_Base_School where SchoolName like " + SchoolName + " and Address like " + Address + ") and SchoolName like " + SchoolName + " and Address like " + Address;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Model.School school = new Model.School();
school.Id = Convert.ToInt32(reader["Id"]);
school.SchoolName = Convert.ToString(reader["SchoolName"]);
school.SchoolNum = Convert.ToString(reader["SchoolNum"]);
school.Address = Convert.ToString(reader["Address"]);
list.Add(school);
}
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);
}
/// <summary>
/// 保存Excel导入学生信息
/// </summary>
/// <param name="Path"></param>
/// <returns></returns>
public int ExcelToDS(string fillPath)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + fillPath + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel = "select * from [sheet1$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet(); myCommand.Fill(ds, "table1");
DataTable dt = ds.Tables[0];
List <Model.Student> list = new List <Model.Student>();
try
{
for (int i = 0; i >= 0; i++)
{
Model.Student student = new Model.Student();
Model.Class tempClass = new Model.Class();
Model.School school = new Model.School();
student.StudentNum = dt.Rows[i][0].ToString();
student.StudentPWD = dt.Rows[i][0].ToString();
student.StudentName = dt.Rows[i][1].ToString();
var sex = dt.Rows[i][2].ToString();
if (sex.Equals("男"))
{
student.Sex = 1;
}
else if (sex.Equals("女"))
{
student.Sex = 0;
}
tempClass.ClassName = dt.Rows[i][3].ToString();
school.SchoolNum = dt.Rows[i][4].ToString();
school.SchoolName = dt.Rows[i][5].ToString();
school.Address = dt.Rows[i][6].ToString();
student.Class = tempClass;
student.School = school;
list.Add(student);
}
}
catch (Exception)
{
}
BLL.Student bll = new BLL.Student();
int result = bll.AddExcel(list);
conn.Close();
return(result);
}
/// <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.Teacher> GetList(int pageSize, int pageNumber, string TeacherWorkNum, string TeacherName, string CourseName)
{
TeacherWorkNum = "'%" + TeacherWorkNum + "%'";
TeacherName = "'%" + TeacherName + "%'";
CourseName = "'%" + CourseName + "%'";
List <Model.Teacher> list = new List <Model.Teacher>();
SqlConnection co = SQLSeverOpen();
SqlCommand cmd = new SqlCommand();
cmd.Connection = co;
cmd.CommandText = "select top " + pageSize + " * from V_Teacher_Course_School where Id not in (select top " + (pageNumber - 1) * pageSize + " Id from V_Teacher_Course_School where TeacherWorkNum like " + TeacherWorkNum + " and TeacherName like " + TeacherName + " and CourseName like " + CourseName + ") and TeacherWorkNum like " + TeacherWorkNum + " and TeacherName like " + TeacherName + " and CourseName like " + CourseName;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
try
{
Model.Teacher teacher = new Model.Teacher();
Model.Course course = new Model.Course();
Model.School school = new Model.School();
teacher.Id = Convert.ToInt32(reader["Id"]);
teacher.TeacherName = Convert.ToString(reader["TeacherName"]);
teacher.SchoolId = Convert.ToInt32(reader["SchoolId"]);
teacher.Sex = Convert.ToInt32(reader["Sex"]);
teacher.IsLeader = Convert.ToInt32(reader["IsLeader"]);
teacher.TeacherWorkNum = Convert.ToString(reader["TeacherWorkNum"]);
teacher.TeacherPWD = Convert.ToString(reader["TeacherPWD"]);
course.Id = Convert.ToInt32(reader["CourseId"]);
course.CourseName = Convert.ToString(reader["CourseName"]);
school.Id = Convert.ToInt32(reader["SchoolId"]);
school.SchoolName = Convert.ToString(reader["SchoolName"]);
school.SchoolNum = Convert.ToString(reader["SchoolNum"]);
teacher.Course = course;
teacher.School = school;
list.Add(teacher);
}
catch (Exception)
{
continue;
}
}
reader.Close();
co.Close();
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);
}
public List <Model.Score> GetSchoolScore()
{
List <Model.Score> list = new List <Model.Score>();
SqlConnection co = SQLSeverOpen();
SqlCommand cmd = new SqlCommand();
cmd.Connection = co;
cmd.CommandText = "select SchoolId,SchoolName,SUM(TotalScore) as TotalScore from V_Write_Questionnaire_Class_School group by SchoolId,SchoolName,SchoolName";
SqlDataReader reader = cmd.ExecuteReader();
List <Model.Score> tempList = new List <Model.Score>();
while (reader.Read())
{
Model.Score score = new Model.Score();
Model.School school = new Model.School();
score.SchoolId = Convert.ToInt32(reader["SchoolId"]);
score.TotalScore = Convert.ToInt32(reader["TotalScore"]);
school.Id = Convert.ToInt32(reader["SchoolId"]);
school.SchoolName = Convert.ToString(reader["SchoolName"]);
score.School = school;
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 SchoolId = " + item.SchoolId;
int count = (int)cmd.ExecuteScalar();
item.TotalScore = item.TotalScore / count;
list.Add(item);
}
reader.Close();
co.Close();
return(list);
}
/// <summary>
/// 获取学校信息
/// </summary>
/// <returns></returns>
public List <Model.School> GetSchool()
{
SqlConnection co = SQLSeverOpen();
SqlCommand cmd = new SqlCommand();
cmd.Connection = co;
List <Model.School> list = new List <Model.School>();
cmd.CommandText = "select * from T_Base_School";
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Model.School school = new Model.School();
school.Id = Convert.ToInt32(reader["Id"]);
school.SchoolName = Convert.ToString(reader["SchoolName"]);
school.SchoolNum = Convert.ToString(reader["SchoolNum"]);
list.Add(school);
}
reader.Close();
co.Close();
return(list);
}
private List <Model.School> SetSchools(JObject jObj, List <Model.School> schools)
{
if (jObj["schoolInfo"] == null)
{
return(schools);
}
int dataCount = Convert.ToInt32(jObj["schoolInfo"][0]["head"][0]["list_total_count"]);
int length = (dataCount > 100) ? 100 : dataCount;
for (int i = 0; i < length; i++)
{
Model.School school = new Model.School();
school.EducationOfficeCode = jObj["schoolInfo"][1]["row"][i]["ATPT_OFCDC_SC_CODE"].ToString();
school.SchoolCode = jObj["schoolInfo"][1]["row"][i]["SD_SCHUL_CODE"].ToString();
school.SchoolName = jObj["schoolInfo"][1]["row"][i]["SCHUL_NM"].ToString();
school.SchoolAddress = jObj["schoolInfo"][1]["row"][i]["ORG_RDNMA"].ToString();
schools.Add(school);
}
return(schools);
}
