/// <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);
}
//获取该学生老师名字列表
public List <Model.Teacher> getTeacherName(int StudentId)
{
SqlConnection co = new SqlConnection();
co.ConnectionString = url;
co.Open();
SqlCommand cm = new SqlCommand();
cm.Connection = co;
cm.CommandText =
"select Id,TeacherName from T_Base_Teacher where id in (select TeacherId from Class_Teacher where classId in(select classId from t_base_Student where Id=" +
StudentId + "))";
List <Model.Teacher> lst = new List <Model.Teacher>();
SqlDataReader dr = cm.ExecuteReader();
while (dr.Read())
{
Model.Teacher teacher = new Model.Teacher();
teacher.Id = Convert.ToInt32(dr["Id"]);
teacher.TeacherName = Convert.ToString(dr["TeacherName"]);
lst.Add(teacher);
}
dr.Close();
co.Close();
return(lst);
}
public bool Add(Model.Teacher model)
{
string strSql = "insert into [teacher] values(@teacherno,@teachername,@Subject,@College,@research,@brief)";
SqlParameter[] parameters =
{
new SqlParameter("@teacherno", SqlDbType.VarChar, 50),
new SqlParameter("@teachername", SqlDbType.VarChar, 50),
new SqlParameter("@Subject", SqlDbType.VarChar, 50),
new SqlParameter("@College", SqlDbType.VarChar, 50),
new SqlParameter("@research", SqlDbType.VarChar, 50),
new SqlParameter("@brief", SqlDbType.VarChar, 50),
};
parameters[0].Value = model.TeacherNo;
parameters[1].Value = model.TeacherName;
parameters[2].Value = model.Subject;
parameters[3].Value = model.College;
parameters[4].Value = model.Research;
parameters[5].Value = model.Brief;
int n = SqlDbHelper.ExecuteNonQuery(strSql, CommandType.Text, parameters);
if (n == 1)
{
return(true);
}
else
{
return(false);
}
}
//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);
}
//查找教师编号
public string SelectTno(Model.Teacher teacher)
{
string tno = null;
IDataParameter[] parameter =
{
new SqlParameter("@Tname", SqlDbType.VarChar, 50)
};
parameter[0].Value = teacher.Tname;
DataSet ds = pr.GetDataSet("SelectTno", parameter);
if (ds.Tables[0].Rows.Count > 0)
{
tno = ds.Tables[0].Rows[0]["Tno"].ToString();
}
return(tno);
}
/// <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="teacherNo">教师号</param>
/// <returns></returns>
public Model.Teacher Get(string teacherNo)
{
string strSql = "select top 1 teachername,Subject,College,research,brief from [teacher] where teacherno=@teacherno";
SqlParameter[] parameters =
{
new SqlParameter("@teacherno", SqlDbType.VarChar, 50),
};
parameters[0].Value = teacherNo;
DataTable dt = SqlDbHelper.ExecuteDataTable(strSql, CommandType.Text, parameters);
if (dt.Rows.Count > 0)
{
Model.Teacher model = new Model.Teacher();
model.TeacherNo = teacherNo;
if (dt.Rows[0]["teachername"] != null && dt.Rows[0]["teachername"].ToString() != "")
{
model.TeacherName = dt.Rows[0]["teachername"].ToString();
}
if (dt.Rows[0]["Subject"] != null && dt.Rows[0]["Subject"].ToString() != "")
{
model.Subject = dt.Rows[0]["Subject"].ToString();
}
if (dt.Rows[0]["College"] != null && dt.Rows[0]["College"].ToString() != "")
{
model.College = dt.Rows[0]["College"].ToString();
}
if (dt.Rows[0]["research"] != null && dt.Rows[0]["research"].ToString() != "")
{
model.Research = dt.Rows[0]["research"].ToString();
}
if (dt.Rows[0]["brief"] != null && dt.Rows[0]["brief"].ToString() != "")
{
model.Brief = dt.Rows[0]["brief"].ToString();
}
return(model);
}
else
{
return(null);
}
}
/// <summary>
/// 检查教师登录信息
/// </summary>
/// <param name="RoleId"></param>
/// <param name="SchoolId"></param>
/// <param name="LoginName"></param>
/// <param name="Password"></param>
/// <returns></returns>
public Model.Teacher CheckTeacher(int RoleId, int SchoolId, string LoginName, string Password)
{
SqlConnection co = SQLSeverOpen();
SqlCommand cmd = new SqlCommand();
cmd.Connection = co;
cmd.CommandText = "select * from T_Base_Teacher where SchoolId = " + SchoolId +
" and TeacherWorkNum = '" + LoginName + "' and TeacherPWD = '" + Password + "' and RoleId = " + RoleId;
SqlDataReader reader = cmd.ExecuteReader();
int result = 0;
Model.Teacher teacher = new Model.Teacher();
Model.User user = new Model.User();
int roleId = -1;
while (reader.Read())
{
result++;
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"]);
teacher.IsLeader = Convert.ToInt32(reader["IsLeader"]);
teacher.Sex = Convert.ToInt32(reader["Sex"]);
teacher.CourseId = Convert.ToInt32(reader["CourseId"]);
teacher.RoleId = Convert.ToInt32(reader["RoleId"]);
roleId = Convert.ToInt32(reader["RoleId"]);
}
reader.Close();
co.Close();
if (result == 1)
{
return(teacher);
}
else
{
teacher.RoleId = -1;
return(teacher);
}
}
public bool Update(Model.Teacher model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update [teacher] set ");
strSql.Append("teachername=@teachername,");
strSql.Append("subject=@Subject,");
strSql.Append("college=@College,");
strSql.Append("research=@research,");
strSql.Append("brief=@brief");
strSql.Append(" where teacherno=@teacherno ");
SqlParameter[] parameters =
{
new SqlParameter("@teacherno", SqlDbType.VarChar, 50),
new SqlParameter("@teachername", SqlDbType.VarChar, 50),
new SqlParameter("@Subject", SqlDbType.VarChar, 50),
new SqlParameter("@College", SqlDbType.VarChar, 50),
new SqlParameter("@research", SqlDbType.VarChar, 50),
new SqlParameter("@brief", SqlDbType.VarChar, 50),
};
parameters[0].Value = model.TeacherNo;
parameters[1].Value = model.TeacherName;
parameters[2].Value = model.Subject;
parameters[3].Value = model.College;
parameters[4].Value = model.Research;
parameters[5].Value = model.Brief;
int n = SqlDbHelper.ExecuteNonQuery(strSql.ToString(), CommandType.Text, parameters);
if (n == 1)
{
return(true);
}
else
{
return(false);
}
}
