protected void Button_submit_Click(object sender, EventArgs e)
{
//点击此按钮，清除该学科组收到的所有反馈信息
/*create table gcgFeedBack( --排课组内部专用表
* class_id char(4) not null,
* CourseNo char(3) not null,
* TeacherNo char(8) not null,
* primary key(class_id,CourseNo)
* )*/
string subjectGroupName = Label_SubjectGroup.Text;
string sql0 = "select subjectGroupNo from gcgSubjectGroup where subjectGroupName ='" + subjectGroupName + "'";
DBManipulation dbm = new DBManipulation();
Object o = dbm.ExecuteScalar(sql0, null);
if (o == null)
{
//说明没有这个学科组
System.Web.UI.ScriptManager.RegisterStartupScript(UpdatePanel1, this.GetType(), "Button6_Click", "alert('没有该学科组存在！')", true);
//实际上是不可能的
return;//不再查询数据
}
string subjectGroupNo = o.ToString();
dbm.Close();
string sql1 = "select CourseNo from gcgSubject,gcgCourse where gcgCourse.SubjectNo = gcgSubject.subjectno and Grade = @Grade and subjectGroupNo = @subjectGroupNo";
ParameterStruct p1 = new ParameterStruct("@Grade", DropDownList_Grade.SelectedValue);
ParameterStruct p2 = new ParameterStruct("@subjectGroupNo", subjectGroupNo);
ArrayList pList1 = new ArrayList();
pList1.Add(p1);
pList1.Add(p2);
SqlDataReader dr = dbm.ExecuteQueryOnLine(sql1, pList1);
ArrayList courseNoList = new ArrayList();
while (dr.Read())
{
courseNoList.Add(dr.GetString(0));
}
dr.Close();
foreach (string courseno in courseNoList)
{
string sql_deletefromGcgFeedBack = "delete from gcgFeedBack where CourseNo = '" + courseno + "'";
dbm.ExecuteNonQuery(sql_deletefromGcgFeedBack, null);
}
//如果发现反馈表中已经没有了记录，那么就直接删掉，神不知鬼不觉~
string sql_checkgcgFeedBack = "select * from gcgFeedBack";
object isEmpty = dbm.ExecuteScalar(sql_checkgcgFeedBack, null);
if (isEmpty == null)
{
//没有反馈记录了
string sql_droptable = "drop table gcgFeedBack";
dbm.ExecuteNonQuery(sql_droptable, null);
}
dbm.Close();
System.Web.UI.ScriptManager.RegisterStartupScript(UpdatePanel1, this.GetType(), "Button6_Click", "alert('已清除反馈信息！')", true);
CreateTable();
}
protected void ButtonDelete_Click(object sender, EventArgs e)
{
Button b = (Button)sender;
string ButtonID = b.ID;
int i;
for (i = 0; i < ButtonLists.Count; i++)
{
Button b1 = (Button)ButtonLists[i];
if (ButtonID.Equals(b1.ID))
{
break;
}
}
TableCell t = (TableCell)TableCellLists[i];
string courseName = t.Text;
string grade = DropDownList_Grade.SelectedValue;
string sql1 = "select subjectno from gcgSubject where subjectname ='" + courseName + "'";
DBManipulation dbm = new DBManipulation();
Object o = dbm.ExecuteScalar(sql1, null);
if (o != null)
{
string subjectno = o.ToString();
string sql2 = "delete from gcgCourse where SubjectNo ='" + subjectno + "' and Grade ='" + grade + "'";
dbm.ExecuteNonQuery(sql2, null);
}
dbm.Close();
SetItemForChoosableCourse();
this.Page_Load(this, null);
}
protected void Button2_Click(object sender, EventArgs e)
{//删除一个学科组，先将组内的学科设为待定组
// string sql2 = "select subjectGroupNo,subjectGroupName from gcgSubjectGroup";
//string sql1 = "select subjectno from gcgSubject where subjectGroupNo in (select subjectGroupNo from gcgSubjectGroup where subjectGroupName = @subjectGroupName)";
string subjectGroupNo = DropDownList1.SelectedValue;
DBManipulation dbm = new DBManipulation();
string sql1 = "select subjectGroupNo from gcgSubjectGroup where subjectGroupName = '待定'";
Object o = dbm.ExecuteScalar(sql1, null);
string subjectGroup_DefalutNo = o.ToString();
string sql2 = "select subjectno from gcgSubject where subjectGroupNo ='" + subjectGroupNo + "'";
DataSet ds = dbm.ExecuteQueryOffLine(sql2, null);
DataTable t = ds.Tables["defaultTable"];
foreach (DataRow r in t.Rows)
{
string subjectno = r["subjectno"].ToString();
string sql3 = "update gcgSubject set subjectGroupNo = @subjectGroupNo where subjectno = @subjectno";
ParameterStruct p1 = new ParameterStruct("@subjectGroupNo", subjectGroup_DefalutNo);
ParameterStruct p2 = new ParameterStruct("@subjectno", subjectno);
ArrayList p = new ArrayList();
p.Add(p1);
p.Add(p2);
dbm.ExecuteNonQuery(sql3, p);
}
string sql = "delete from gcgSubjectGroup where subjectGroupNo = '" + subjectGroupNo + "'";
dbm.ExecuteNonQuery(sql, null);
setItem_deleteDropDownList();//为删除下拉框提供新的数据
this.Page_Load(this, null);
}
protected void Button_submit_Click(object sender, EventArgs e)
{ //点击确认，提交课时安排
//通过 TableCellLists DropDownLists获得每一条记录的科目名以及课时，通过DropDownList_Grade获得年级
//步骤：一条一条来看，如果原先有课时安排的，就更新，没有的，就插入
string grade = DropDownList_Grade.SelectedValue;
DBManipulation dbm = new DBManipulation();
// Response.Write("<script>alert('有新"+ HasNewCourse+" ')</script>");
for (int i = 0; i < DropDownLists.Count; i++)
{
//步骤一：查询这门科目是否有过旧的课时安排记录
//注意：一开始没有学科，所以要通过按钮增加学科，此时会保证所有记录都有初始课时分布，所以提交按钮仅仅需要将这些记录更新就行了
TableCell tc = (TableCell)TableCellLists[i];
DropDownList ddl = (DropDownList)DropDownLists[i];
string courseName = tc.Text; //获得科目名
string courseCount = ddl.SelectedValue; //获得新的分配课时
string SearchSql = "select CourseNo from gcgCourse,gcgSubject where gcgSubject.subjectno = gcgCourse.SubjectNo and Grade = @Grade and subjectname = @subjectname";
ParameterStruct p1 = new ParameterStruct("@Grade", grade);
ParameterStruct p2 = new ParameterStruct("@subjectname", courseName);
ArrayList parameterList1 = new ArrayList();
parameterList1.Add(p1);
parameterList1.Add(p2);
Object o = dbm.ExecuteScalar(SearchSql, parameterList1);
string CourseNo = o.ToString();
string sql4 = "update gcgCourse set CourseTime = " + courseCount + " where CourseNo = " + CourseNo;
dbm.ExecuteNonQuery(sql4, null);
}
dbm.Close();
System.Web.UI.ScriptManager.RegisterStartupScript(UpdatePanel1, this.GetType(), "Button6_Click", "alert('提交已完成！')", true);
}
/*
* protected override void OnLoad(EventArgs e)
* {
*
* }
*/
protected void Page_Load(object sender, EventArgs e)
{
//外部添加局部刷新控件
ScriptManager1.RegisterAsyncPostBackControl(this.DropDownList_Grade);
ScriptManager1.RegisterAsyncPostBackControl(this.DropDownList_semester);
ScriptManager1.RegisterAsyncPostBackControl(this.DropDownList_year);
if (!IsPostBack)
{
if (!(LoginAndPermissionChecking.LoginChecking()))
{
Response.Redirect("/ErrorPage/error_NotLogin.aspx");
}
if (!(LoginAndPermissionChecking.PermissionChecking(PermissionEnum.EducationDean)))
{
Response.Redirect("/ErrorPage/error_DeniedPermission.aspx");
}
//首先先检查是否有gcgFeedBack这张表，如果不存在就新建
string sql_LookForgcgFeedBack = "select * from sysobjects where name = 'gcgFeedBack' and xtype = 'U'";
DBManipulation dbm = new DBManipulation();
object o = dbm.ExecuteScalar(sql_LookForgcgFeedBack, null);
if (o == null)
{
string sql_CreategcgFeedBack = "create table gcgFeedBack(" +
"class_id char(4) not null," +
"CourseNo char(3) not null," +
"TeacherNo char(8) not null," +
"primary key(class_id, CourseNo)" +
")";
dbm.ExecuteNonQuery(sql_CreategcgFeedBack, null);
}
/*create table gcgFeedBack( --排课组内部专用表
* class_id char(4) not null,
* CourseNo char(3) not null,
* TeacherNo char(8) not null,
* primary key(class_id,CourseNo)
* )*/
//页面第一次载入之前，要查询gcgFeedBack授课安排反馈表，看看是不是还有未确认的反馈信息
string sql = "select class_id,CourseNo from gcgFeedBack";
// DBManipulation dbm = new DBManipulation();
SqlDataReader dr = dbm.ExecuteQueryOnLine(sql, null);
while (dr.Read())
{
string buttonName = dr.GetString(0) + dr.GetString(1);
//Session[buttonName] = "nameRedButton";//我们使用Session保存有意见的老师安排的css样式
Session[buttonName] = "aspTableCellBrown2";//把原先红色的恢复成黑色
}
dr.Close();
dbm.Close();
}
createtable(GetSemesterNo());
}
//生成该年级该学科组所包含的所有科目，为下拉框绑定数据
protected void ProvideDataForSelectSubject()
{
string grade = DropDownList_Grade.SelectedValue;
//debug1.Text = "年级：" + grade;
string subjectGroupName = Label_SubjectGroup.Text;
string sql0 = "select subjectGroupNo from gcgSubjectGroup where subjectGroupName ='" + subjectGroupName + "'";
DBManipulation dbm = new DBManipulation();
Object o = dbm.ExecuteScalar(sql0, null);
if (o == null)
{
//说明没有这个学科组
System.Web.UI.ScriptManager.RegisterStartupScript(UpdatePanel1, this.GetType(), "Button6_Click", "alert('没有该学科组存在！')", true);
return;//不再查询数据
}
string subjectGroupNo = o.ToString();
dbm.Close();
string sql1 = "select CourseNo,subjectname from gcgSubject,gcgCourse where gcgCourse.SubjectNo = gcgSubject.subjectno and Grade = @Grade and subjectGroupNo = @subjectGroupNo";
ParameterStruct p1 = new ParameterStruct("@Grade", grade);
ParameterStruct p2 = new ParameterStruct("@subjectGroupNo", subjectGroupNo);
ArrayList pList1 = new ArrayList();
pList1.Add(p1);
pList1.Add(p2);
/*
* SqlDataReader dr = dbm.ExecuteQueryOnLine(sql1,pList1);
* while (dr.Read()) {
* ListItem item = new ListItem(dr.GetString(1),dr.GetString(0));
* item.Attributes.Add("style", "color:red");
* DropDownList_Course.Items.Add(item);
* }*/
DataSet ds_Course = dbm.ExecuteQueryOffLine(sql1, pList1);
DropDownList_Course.AutoPostBack = true;
DropDownList_Course.DataSource = ds_Course.Tables["defaultTable"];
DropDownList_Course.DataTextField = ds_Course.Tables["defaultTable"].Columns[1].ColumnName;
DropDownList_Course.DataValueField = ds_Course.Tables["defaultTable"].Columns[0].ColumnName;
ListItem item = new ListItem();
//DropDownList_Subject.SelectedIndex = 0;
DropDownList_Course.DataBind();
//要先清洗掉上一个年级残存的老师集合
foreach (DropDownList d in DropDownList_select)
{
d.Items.Clear();
}
setDataForEachTeacherDropDownList();
}
private void CreateTable()
{
/*create table gcgFeedBack(
* class_id char(4) not null,
* CourseNo char(3) not null,
* TeacherNo char(8) not null,
* primary key(class_id,CourseNo)
* )*/
//首先这也是一个二维表，行显示各个学科，列属性为班级
//先显示学科
Table1.Rows.Clear();
string sql_hasgcgFeedBackTable = "select * from sysobjects where name = 'gcgFeedBack'and xtype = 'U'";
DBManipulation dbm = new DBManipulation();
object gcgFeedBack = dbm.ExecuteScalar(sql_hasgcgFeedBackTable, null);
if (gcgFeedBack == null) //如果表都不存在，说明教导主任没有反对意见
{
this.Button_submit.Visible = false;
return;
}
string sql1 = "select distinct subjectname from gcgFeedBack, gcgCourse, gcgSubject, temp_class, gcgSubjectGroup where gcgFeedBack.CourseNo = gcgCourse.CourseNo and gcgCourse.SubjectNo = gcgSubject.subjectno and gcgSubject.subjectGroupNo = gcgSubjectGroup.subjectGroupNo and temp_class.class_id = gcgFeedBack.class_id and Grade = @Grade and subjectGroupName = @subjectGroupName";
ParameterStruct p_grade = new ParameterStruct("@Grade", DropDownList_Grade.SelectedValue);
ParameterStruct p_subjectGroupName = new ParameterStruct("@subjectGroupName", Label_SubjectGroup.Text);
ArrayList plist1 = new ArrayList();
plist1.Add(p_grade);
plist1.Add(p_subjectGroupName);
SqlDataReader dataReader1 = dbm.ExecuteQueryOnLine(sql1, plist1);
TableRow tableRow = new TableRow();
if (!dataReader1.HasRows)//如果没有反馈，那么什么都不显示
{
this.Button_submit.Visible = false;
return;
}
else
{
this.Button_submit.Visible = true;
}
tableRow.CssClass = "aspTableRow1";
//先加入一个空格子，这个格子是二维表的左上角的那个方格
TableCell cell_blank = new TableCell();
cell_blank.Text = " "; //没有文本显示
// cell_blank.Attributes.Add("onclick", "getdate(this)");
cell_blank.CssClass = "aspTableCell3";
tableRow.Controls.Add(cell_blank);
List <string> courseset = new List <string>();
courseset.Clear();//习惯性清空
while (dataReader1.Read())
{
TableCell cell_CourseName = new TableCell();
cell_CourseName.Text = dataReader1.GetString(0); //文本为科目名
// cell_CourseName.Attributes.Add("onclick", "getdate(this)");
cell_CourseName.CssClass = "aspTableCell3";
tableRow.Controls.Add(cell_CourseName);
courseset.Add(cell_CourseName.Text);//将这个科目名添加到
}
Table1.Controls.Add(tableRow);
dataReader1.Close();
dbm.Close();
//---------------------------------------------分割线，自此表头学科一栏生成完毕--------------------------------------------
//查询某一个年级在该学科组范围内出问题的所有班级
string sql2 = "select distinct temp_class.class_id,class_name from temp_class,gcgFeedBack,gcgCourse,gcgSubject,gcgSubjectGroup where temp_class.class_id = gcgFeedBack.class_id and gcgFeedBack.CourseNo = gcgCourse.CourseNo and gcgCourse.SubjectNo = gcgSubject.subjectno and gcgSubject.subjectGroupNo = gcgSubjectGroup.subjectGroupNo and class_grade = @Grade and subjectGroupName = @subjectGroupName order by class_id asc";
SqlDataReader dataReader2 = dbm.ExecuteQueryOnLine(sql2, plist1);
List <ClassInfo> classset = new List <ClassInfo>();
classset.Clear();
//班级编号写入链表
while (dataReader2.Read())
{
ClassInfo cdata = new ClassInfo(dataReader2.GetString(0), dataReader2.GetString(1));//存放class_id，class_name
classset.Add(cdata);
}
dataReader2.Close();
SqlDataReader dataReader3;//准备接受DataReader对象
string sql3;
for (int i = 0; i < classset.Count; i++)
{
ClassInfo cinfo = classset[i];
//依据班级号和学期号查询这些班级在特定时间段的授课记录，一个班一个班的显示数据
sql3 = "select gcgFeedBack.CourseNo,gcgFeedBack.TeacherNo,TeacherName,subjectName from gcgFeedBack,gcgCourse,gcgSubject,temp_teacher where gcgFeedBack.CourseNo = gcgCourse.CourseNo and gcgFeedBack.TeacherNo = temp_teacher.TeacherNo and gcgCourse.SubjectNo = gcgSubject.subjectno and class_id = @class_id";
ParameterStruct p_class_id = new ParameterStruct("@class_id", cinfo.No);
// ParameterStruct p_semester = new ParameterStruct("@SemesterNo", semeterNo);
ArrayList parameterList3 = new ArrayList();
parameterList3.Add(p_class_id);
// parameterList3.Add(p_semester);
dataReader3 = dbm.ExecuteQueryOnLine(sql3, parameterList3);
//保存这个班出问题的科目号,科目名与老师号
List <TeaInfo> tset = new List <TeaInfo>();
tset.Clear();
while (dataReader3.Read())
{
TeaInfo tdata = new TeaInfo(dataReader3.GetString(1), dataReader3.GetString(2), dataReader3.GetString(3), dataReader3.GetString(0));//no,name,sbj,courseno
tset.Add(tdata);
}
tableRow = new TableRow();
//tableRow.CssClass = "aspTableRow1";
//先加入一个班级格子,显示这个班级的名字
TableCell cell_class = new TableCell();
cell_class.Text = cinfo.Name;
cell_class.CssClass = "aspTableCell";
tableRow.Controls.Add(cell_class);
//循环学科链表，为每一行（每一个班）的每一门学科填入对应老师
for (int j = 0; j < courseset.Count; j++)
{
string s1 = courseset[j];
TableCell cell = new TableCell();
//通过遍历这个班的老师链表实现
foreach (TeaInfo s2 in tset)
{
if (s1.Equals(s2.Sbj))
{
cell.Text = s2.Name;
break;
}
}
cell.CssClass = "aspTableCell";
tableRow.Controls.Add(cell);
}
Table1.Controls.Add(tableRow);
dataReader3.Close();//注意每一次循环都会执行一次数据库查询，返回一个DataReader，所以这次循环体执行完之后必须将其关闭，避免占用connection
}
dbm.Close();
}
protected void Button_SubmitSchedule_Click(object sender, EventArgs e)
{
if (Cache["auto"] == null)
{
System.Web.UI.ScriptManager.RegisterStartupScript(UpdatePanel1, this.GetType(), "Button6_Click", "alert('请先生成课表。')", true);
//Response.Write("<script>alert('请先生成课表。')</script>");
return;
}
string lessonTableName = "LessonTable" + DropDownList_Grade.SelectedValue;
if (Cache[lessonTableName] == null)
{
System.Web.UI.ScriptManager.RegisterStartupScript(UpdatePanel1, this.GetType(), "Button6_Click", "alert('请先生成课表。')", true);
return;
}
string dict_CourseNo_SubjectName_Name = "dict_CourseNo_SubjectName" + DropDownList_Grade.SelectedValue;
if (Cache[dict_CourseNo_SubjectName_Name] == null)
{
System.Web.UI.ScriptManager.RegisterStartupScript(UpdatePanel1, this.GetType(), "Button6_Click", "alert('请先生成课表。')", true);
return;
}
string dict_SubjectName_CourseNo_Name = "dict_SubjectName_CourseNo" + DropDownList_Grade.SelectedValue;
if (Cache[dict_SubjectName_CourseNo_Name] == null)
{
System.Web.UI.ScriptManager.RegisterStartupScript(UpdatePanel1, this.GetType(), "Button6_Click", "alert('请先生成课表。')", true);
return;
}
string dict_ClassName_ClassNo_Name = "dict_ClassName_ClassNo" + DropDownList_Grade.SelectedValue;
if (Cache[dict_ClassName_ClassNo_Name] == null)
{
System.Web.UI.ScriptManager.RegisterStartupScript(UpdatePanel1, this.GetType(), "Button6_Click", "alert('请先生成课表。')", true);
return;
}
Dictionary <string, string> dict_SubjectName_CourseNo = (Dictionary <string, string>)Cache[dict_SubjectName_CourseNo_Name];
Dictionary <string, string> dict_CourseNo_SubjectName = (Dictionary <string, string>)Cache[dict_CourseNo_SubjectName_Name];
Dictionary <string, string> dict_ClassName_ClassNo = (Dictionary <string, string>)Cache[dict_ClassName_ClassNo_Name];
Tea[,] Lessontable = (Tea[, ])Cache[lessonTableName];
//以上已获得某一个年级的全部课表，老师号-科目名映射，学科号-科目名映射，班级名和班级号映射
/*
* create table gcgLectureForm(--授课安排表
* LectureNo char(7) primary key,--授课安排编号
* SemesterNo char(6),--学期号，蕴含年份和上下学期信息
* TeacherNo char(8),--教师工号
* CourseNo char(3) foreign key references gcgcourse(CourseNo),--学科编号
* ClassNo char(4)--班级编号
* )
* create table gcgLesson(--课表
* LectureNo char(7) foreign key references gcgLectureForm(LectureNo),--授课安排编号
* --ClassRoomNo char(*)
* Timeid char(4) foreign key references gcgSchedule(Timeid),--时间段编号
* primary key(LectureNo,Timeid)--表级主码
* )
*/
string sql_forSemester = "select MAX(SemesterNo) from gcgLectureForm ";
DBManipulation dbm = new DBManipulation();
object o = dbm.ExecuteScalar(sql_forSemester, null);
string semesterNo = o.ToString();
string grade = DropDownList_Grade.SelectedValue;
string sql_deleteTheSameSemesterRecord = "delete from gcgLesson where LectureNo in ( select LectureNo from gcgLectureForm,temp_class where gcgLectureForm.ClassNo = temp_class.class_id and class_grade = @Grade and SemesterNo = @SemesterNo)";
ParameterStruct p_grade = new ParameterStruct("@Grade", grade);
ParameterStruct p_seme = new ParameterStruct("@SemesterNo", semesterNo);
ArrayList palist = new ArrayList();
palist.Add(p_grade);
palist.Add(p_seme);
dbm.ExecuteNonQuery(sql_deleteTheSameSemesterRecord, palist);
/*string sql_MappingClassNameAndClassNo =
* " select class_name,class_id
* from temp_class,(
* select distinct ClassNo
* from gcgLectureForm
* where SemesterNo = (
* select MAX(SemesterNo)
* from gcgLectureForm)) AS temp
* where class_id = ClassNo and class_grade = '" + DropDownList_Grade.SelectedValue + "'";
*/
string sql_forClassNo = "select class_id from temp_class,(select distinct ClassNo from gcgLectureForm where SemesterNo = (select MAX(SemesterNo) from gcgLectureForm)) AS temp where class_id = ClassNo and class_grade = '" + DropDownList_Grade.SelectedValue + "'";
ArrayList classlist = new ArrayList();
SqlDataReader dr = dbm.ExecuteQueryOnLine(sql_forClassNo, null);
while (dr.Read())
{
classlist.Add(dr.GetString(0));
}
dr.Close();
for (int i = 0; i < classlist.Count; i++)
{//一个一个班的写入
//[,]Lessontable一行就存放了一个班的所有记录
Tea[] classTable = new Tea[Lessontable.GetLength(1) + 1];
string[] courseTable = new string[Lessontable.GetLength(1) + 1];
for (int t = 0; t < 40; t++)
{
classTable[t] = Lessontable[i + 1, t + 1]; //取到某一个班40节课的所有教师工号
string subjectname = dict_CourseNo_SubjectName[classTable[t].cno];
courseTable[t] = dict_SubjectName_CourseNo[subjectname]; //取到这四十节课的学科号
}
for (int t = 0; t < 40; t++)
{
//classlist[i-1]就是对应的班级号
string sql_forLectureID = "select LectureNo from gcgLectureForm where SemesterNo = @SemesterNo and ClassNo = @ClassNo and CourseNo = @CourseNo";
ParameterStruct p_semester = new ParameterStruct("@SemesterNo", semesterNo);
ParameterStruct p_classNo = new ParameterStruct("@ClassNo", (string)classlist[i]);//第i个班级的班级号
ParameterStruct p_courseNo = new ParameterStruct("@CourseNo", courseTable[t]);
ArrayList plist = new ArrayList();
plist.Add(p_semester); plist.Add(p_classNo); plist.Add(p_courseNo);
Object lectureID = dbm.ExecuteScalar(sql_forLectureID, plist);
//string Timeid1 = grade + week + "0" + "1";
string Time_part1 = DropDownList_Grade.SelectedValue;
int week = 0;
string Time_part3;
int temp = t + 1;
if (temp % 8 == 0)
{
week = temp / 8;
Time_part3 = "0" + 8;
}
else
{
week = temp / 8 + 1;
Time_part3 = "0" + (temp - (week - 1) * 8);
}
string Time_part2 = week + "";
// string Time_part3 = "0" + (t - (t / 8) * 8);
string sql_insert = "insert into gcgLesson values(@lectureID,@TimeID)";
ParameterStruct p_LectureID = new ParameterStruct("@lectureID", lectureID.ToString());
ParameterStruct p_timeID = new ParameterStruct("@TimeID", Time_part1 + Time_part2 + Time_part3);//第i个班级的班级号
ArrayList plist1 = new ArrayList();
plist1.Add(p_LectureID);
plist1.Add(p_timeID);
dbm.ExecuteNonQuery(sql_insert, plist1);
}
}
dbm.Close();
System.Web.UI.ScriptManager.RegisterStartupScript(UpdatePanel1, this.GetType(), "Button6_Click", "alert('课表已保存！')", true);
}
protected void CreateTheWholeSchedule()
{
string sql_courseNum = "select COUNT(*) from gcgCourse where Grade = '" + DropDownList_Grade.SelectedValue + "'";
DBManipulation dbm = new DBManipulation();
Object obj_countCourse = dbm.ExecuteScalar(sql_courseNum, null);
if (obj_countCourse == null)
{//这个年级还没有安排课程计划，无需接着往下了
System.Web.UI.ScriptManager.RegisterStartupScript(UpdatePanel1, this.GetType(), "Button6_Click", "alert('没有安排学科！')", true);
return;
}
int count_courseNum = int.Parse(obj_countCourse.ToString());
if (DropDownList_class.Items.Count == 0)
{
//没有班级参与排课，无需接着往下了
System.Web.UI.ScriptManager.RegisterStartupScript(UpdatePanel1, this.GetType(), "Button6_Click", "alert('没有班级参与排课！')", true);
return;
}
string sql_checkTime = "select count(*) from gcgSchedule where Grade = " + DropDownList_Grade.SelectedValue;
Object obj_time = dbm.ExecuteScalar(sql_checkTime, null);
if (obj_time.ToString().Equals("0"))
{
System.Web.UI.ScriptManager.RegisterStartupScript(UpdatePanel1, this.GetType(), "Button6_Click", "alert('尚未设置每节课的时间段！')", true);
return;
}
// debug.Text = count_courseNum + "";
Struct_CourseName[] courseList = new Struct_CourseName[count_courseNum];
if (courseList == null)
{
Response.Redirect("1.aspx");
}
//准备科目名与学科号的映射,名字与年级有关，因为不同年级对于同一个科目的学科号是不一样的。,所以必须对应年级。
string sql_MappingCourseNoAndCourseName = "select CourseNo,subjectname from gcgCourse,gcgSubject where gcgCourse.SubjectNo = gcgSubject.subjectno and Grade = '" + DropDownList_Grade.SelectedValue + "'";
SqlDataReader dr00 = dbm.ExecuteQueryOnLine(sql_MappingCourseNoAndCourseName, null);
Dictionary <string, string> dict_SubjectName_CourseNo = new Dictionary <string, string>();
while (dr00.Read())
{
dict_SubjectName_CourseNo.Add(dr00.GetString(1), dr00.GetString(0));
//因为年级一旦确定，科目名就与学科号 一 一 对应了
}
dr00.Close();
string dict_SubjectName_CourseNo_Name = "dict_SubjectName_CourseNo" + DropDownList_Grade.SelectedValue;
Cache[dict_SubjectName_CourseNo_Name] = dict_SubjectName_CourseNo;
//准备学科号与学科名的映射
Dictionary <string, string> dict_CourseNo_SubjectName = new Dictionary <string, string>();
SqlDataReader dr01 = dbm.ExecuteQueryOnLine(sql_MappingCourseNoAndCourseName, null);
while (dr01.Read())
{
dict_CourseNo_SubjectName.Add(dr01.GetString(0), dr01.GetString(1));
}
dr01.Close();
string dict_CourseNo_SubjectName_Name = "dict_CourseNo_SubjectName" + DropDownList_Grade.SelectedValue;
Cache[dict_CourseNo_SubjectName_Name] = dict_CourseNo_SubjectName;
//准备班级号与班级名的映射,查询授课安排表中的最新一个学期的某一个年级的班级号与其对应的班级名，肯定是 一 一 对应的。
string sql_MappingClassNameAndClassNo = "select class_name,class_id from temp_class,(select distinct ClassNo from gcgLectureForm where SemesterNo = (select MAX(SemesterNo) from gcgLectureForm)) AS temp where class_id = ClassNo and class_grade = '" + DropDownList_Grade.SelectedValue + "'";
SqlDataReader dr_MappingClassNameAndClassNo = dbm.ExecuteQueryOnLine(sql_MappingClassNameAndClassNo, null);
Dictionary <string, string> dict_ClassName_ClassNo = new Dictionary <string, string>();
while (dr_MappingClassNameAndClassNo.Read())
{
dict_ClassName_ClassNo.Add(dr_MappingClassNameAndClassNo.GetString(0), dr_MappingClassNameAndClassNo.GetString(1));
}
dr_MappingClassNameAndClassNo.Close();
string dict_ClassName_ClassNo_Name = "dict_ClassName_ClassNo" + DropDownList_Grade.SelectedValue;
Cache[dict_ClassName_ClassNo_Name] = dict_ClassName_ClassNo;
string sql_fillCourseList = "select CourseNo,subjectname,CourseTime from gcgCourse,gcgSubject where gcgCourse.SubjectNo = gcgSubject.subjectno and Grade = '" + DropDownList_Grade.SelectedValue + "'";
SqlDataReader dr1 = dbm.ExecuteQueryOnLine(sql_fillCourseList, null);
if (dr1 == null)
{
Response.Redirect("1.aspx");
}
for (int i = 0; i < courseList.Length && dr1.Read(); i++)
{
courseList[i] = new Struct_CourseName();
courseList[i].CourseNo1 = dr1.GetString(0);
courseList[i].SubjectName1 = dr1.GetString(1);
Byte b = dr1.GetByte(2);
courseList[i].Hour1 = (int)b;
}
dr1.Close();
dbm.Close();
string sql_countClassNum = "select COUNT(*) from( select distinct ClassNo from gcgLectureForm, temp_class where gcgLectureForm.ClassNo = temp_class.class_id and class_grade = @Grade) AS temp ";
//为什么上面这一句这么复杂，是为了防止有些班级还没有参与排课计划
ParameterStruct p_grade = new ParameterStruct("@Grade", DropDownList_Grade.SelectedValue);
ArrayList plist1 = new ArrayList();
plist1.Add(p_grade);
Object obj_countClassNum = dbm.ExecuteScalar(sql_countClassNum, plist1);
if (obj_countClassNum == null)
{
return;
}
int countClassNum = int.Parse(obj_countClassNum.ToString());
string sql_LectureForm_orderByClassNoAndCourseNo = "select ClassNo,gcgLectureForm.CourseNo,gcgLectureForm.TeacherNo,temp_teacher.TeacherName from gcgLectureForm, gcgCourse, gcgSubject, temp_teacher where gcgCourse.CourseNo = gcgLectureForm.CourseNo and gcgCourse.SubjectNo = gcgSubject.subjectno and temp_teacher.TeacherNo = gcgLectureForm.TeacherNo and Grade = @Grade order by gcgLectureForm.ClassNo,gcgLectureForm.CourseNo";
SqlDataReader dr2 = dbm.ExecuteQueryOnLine(sql_LectureForm_orderByClassNoAndCourseNo, plist1);
ArrayList plist2 = new ArrayList();
TeaInfo[,] teacherArrange = new TeaInfo[countClassNum, count_courseNum];
for (int i = 0; i < countClassNum; i++)
{
for (int j = 0; j < count_courseNum; j++)
{
teacherArrange[i, j] = new TeaInfo();
if (!dr2.HasRows)
{
break;
}
else
{
dr2.Read();
}
try
{
teacherArrange[i, j].No = dr2.GetString(2); //教师工号
teacherArrange[i, j].Name = dr2.GetString(3); //教师名
teacherArrange[i, j].Courseno = dr2.GetString(1); //学科号，可能是擅长的，可能是非擅长的
}
catch (Exception)
{
System.Web.UI.ScriptManager.RegisterStartupScript(UpdatePanel1, this.GetType(), "Button6_Click", "alert('数据不够，请检查是否已经安排所有班级的老师！')", true);
return;
}
}
if (!dr2.HasRows)
{
break;
}
}
dr2.Close();
dbm.Close();
AutoSchedule auto = new AutoSchedule();
Cache["auto"] = auto;
auto.CourseList = courseList;
auto.TeacherArrange = teacherArrange;
auto.MakingSchedule();
Tea[,] Lessontable = auto.func();//注意：这个课表的行是一个个班级，列是这个班级一周的额全部课程分布，都是从1开始的
string lessonTableName = "LessonTable" + DropDownList_Grade.SelectedValue;
Cache[lessonTableName] = Lessontable;//使用ViewState保存，生命周期为该页面，只要这个页面没有关闭就存在
if (Lessontable == null)
{
Response.Redirect("1.aspx");
}
ShowClassTable();
}
protected void Button_submit_Click(object sender, EventArgs e)
{
//点击确认按钮，写入数据库
//首先检查是否该年级该门学科所有班级都已经分配了老师，如果没有，则不允许插入，并弹窗提醒
foreach (DropDownList d in DropDownList_select)
{
if (d.SelectedValue == "-1")
{
System.Web.UI.ScriptManager.RegisterStartupScript(UpdatePanel1, this.GetType(), "Button6_Click", "alert('尚未完成所有班级的教师安排！')", true);
return;
}
}
// Response.Write("<script>alert('aaa')</script>");
string year = DropDownList_year.SelectedValue;
string semester = DropDownList_semester.SelectedValue;
string semesterNo = year + semester;
//得到学期号
DBManipulation dbm = new DBManipulation();
string sql1 = "select class_id,class_name from temp_class where class_grade = '" + DropDownList_Grade.SelectedValue + "'";
DataSet ds_class = dbm.ExecuteQueryOffLine(sql1, null);
DataTable classTable = ds_class.Tables["defaultTable"];
for (int i = 0; i < TableCellList_class.Count; i++)
{
TableCell tc = (TableCell)TableCellList_class[i];
string className = tc.Text;
string classNo = "";
//查询班级编号
foreach (DataRow r in classTable.Rows)
{
if (className.Equals(r["class_name"].ToString()))
{
classNo = r["class_id"].ToString();
break;
}
}
DropDownList d = (DropDownList)DropDownList_select[i];
string TeacherNo = d.SelectedValue;
/*
* if (TeacherNo.Equals("-1")) {//如果教师号为-1，说明还没有安排老师，不写入
* continue;
* }
*/
//得到教师工号
string courseNo = DropDownList_Course.SelectedValue;
//得到学科号
/*主码，前2位代表年份，一位代表年级+学期（初一1，2 初二3，4 初三5，6），2位代表班级，2位代表学科*/
string LectureNo_part1 = year.Substring(2);
string LectureNo_part2 = "";
switch (DropDownList_Grade.SelectedValue)
{
case "1":
{
if (DropDownList_semester.SelectedValue.Equals("01"))
{
LectureNo_part2 = "1";
}
else
{
LectureNo_part2 = "2";
}
break;
}
case "2":
{
if (DropDownList_semester.SelectedValue.Equals("01"))
{
LectureNo_part2 = "3";
}
else
{
LectureNo_part2 = "4";
}
break;
}
case "3":
{
if (DropDownList_semester.SelectedValue.Equals("01"))
{
LectureNo_part2 = "5";
}
else
{
LectureNo_part2 = "6";
}
break;
}
}
/*主码，前2位代表年份，一位代表年级+学期（初一1，2 初二3，4 初三5，6），2位代表班级，2位代表学科*/
string LectureNo_part3 = classNo.Substring(2);
string LectureNo_part4 = courseNo.Substring(1);
string LectureNo = LectureNo_part1 + LectureNo_part2 + LectureNo_part3 + LectureNo_part4;
//得到授课记录主码
//查询gcgLectureForm看是不是已经记录了这条记录，如果存在，则删除
string sql3 = "select * from gcgLectureForm where LectureNo ='" + LectureNo + "'";
Object o = dbm.ExecuteScalar(sql3, null);
if (o != null)
{
string sql4 = "delete from gcgLectureForm where LectureNo ='" + LectureNo + "'";
dbm.ExecuteNonQuery(sql4, null);
}
/*create table gcgLectureForm(--授课安排表
* LectureNo char(7) primary key,--授课安排编号
* SemesterNo char(6),--学期号 201601
* TeacherNo char(8),--教师工号
* CourseNo char(3) ,--学科编号
* ClassNo char(4)--班级编号
* )*/
string sql2 = "insert into gcgLectureForm values(@LectureNo,@SemesterNo,@TeacherNo,@CourseNo,@ClassNo)";
ParameterStruct p1 = new ParameterStruct("@LectureNo", LectureNo);
ParameterStruct p2 = new ParameterStruct("@SemesterNo", semesterNo);
ParameterStruct p3 = new ParameterStruct("@TeacherNo", TeacherNo);
ParameterStruct p4 = new ParameterStruct("@CourseNo", courseNo);
ParameterStruct p5 = new ParameterStruct("@ClassNo", classNo);
ArrayList plist1 = new ArrayList();
plist1.Add(p1);
plist1.Add(p2);
plist1.Add(p3);
plist1.Add(p4);
plist1.Add(p5);
dbm.ExecuteNonQuery(sql2, plist1);
}
System.Web.UI.ScriptManager.RegisterStartupScript(UpdatePanel1, this.GetType(), "Button6_Click", "alert('修改已完成！')", true);
}
protected void setDataForEachTeacherDropDownList()
{
if (DropDownList_Course.Items.Count == 0)
{
//说明没有课，停止查询
return;
}
string courseNo = DropDownList_Course.SelectedValue;//注意这里选出来的是学科号
// debug1.Text = "courseNo:"+courseNo;
// Response.Write("<script>alert('" + courseNo + "')</script>");\
string subjectno = courseNo.Substring(1);//学科号的后两位是科目号，因为我们的学科Id就是按照这样的规则计算出来的
string sql_forA = "select TeacherNo,TeacherName from temp_teacher where SubjectA ='" + subjectno + "' order by TeacherNo asc";
DBManipulation dbm = new DBManipulation();
DataSet ds_teacher_A = dbm.ExecuteQueryOffLine(sql_forA, null);//查询出所有可以教这门课的擅长老师
string sql_forB = "select TeacherNo,TeacherName from temp_teacher where SubjectB ='" + subjectno + "' order by TeacherNo asc";
string className = "";
DataSet ds_teacher_B = dbm.ExecuteQueryOffLine(sql_forB, null);//查询出所有可以教这门课的非擅长老师
Dictionary <string, int> dict_TeacherNo_CourseTime = (Dictionary <string, int>)Cache["dict_TeacherNo_CourseTime"];
for (int i = 0; i < DropDownList_select.Count; i++)
{
DropDownList d = (DropDownList)DropDownList_select[i];
d.Items.Clear();
d.ID = DropDownList_Grade.SelectedValue + DropDownList_Course.SelectedValue + i;
///这里可以排个版
foreach (DataRow teacher_A in ds_teacher_A.Tables["defaultTable"].Rows)
{
ListItem item = new ListItem(teacher_A[1].ToString() + " " + dict_TeacherNo_CourseTime[teacher_A[0].ToString()], teacher_A[0].ToString());
//ListItem item = new ListItem(itemText, teacher_A[0].ToString());
item.Attributes.Add("style", "color:red");
d.Items.Add(item);
}
foreach (DataRow teacher_B in ds_teacher_B.Tables["defaultTable"].Rows)
{
ListItem item = new ListItem(teacher_B[1].ToString() + " " + dict_TeacherNo_CourseTime[teacher_B[0].ToString()], teacher_B[0].ToString());
item.Attributes.Add("style", "color:blue");
d.Items.Add(item);
}
int count_Teacher = ds_teacher_A.Tables["defaultTable"].Rows.Count + ds_teacher_B.Tables["defaultTable"].Rows.Count;
//干脆下标为count_Teacher的就作为默认待定项吧，如果数据库里面没有给这个班分配老师，就设置为待定
ListItem default_item = new ListItem("待定", "-1");
d.Items.Add(default_item);
//d.Items.Insert(d.Items.Count, new ListItem( "待定","-1"));
//debug1.Text = d.Items.Count.ToString();
TableCell tc = (TableCell)TableCellList_class[i];
/* if (Session[d.UniqueID] == null)
* {//不能使用UniqueID，这个是系统自动生成的，下一次页面刷新会重新赋予新的值！
* d.SelectedIndex = index;//如果o != null,那么不管怎么样总是会拿到一个索引值
* }
* else {
* d.SelectedIndex = (int)Session[d.UniqueID];
* }*/
if (Session[d.ID] != null)
{
d.SelectedIndex = (int)Session[d.ID];
}
else
{
className = tc.Text;
string grade = DropDownList_Grade.SelectedValue;
string sql2 = "select class_id from temp_class where class_name = @ClassName and class_grade = @Grade";
ParameterStruct p1 = new ParameterStruct("@ClassName", className);
ParameterStruct p2 = new ParameterStruct("@Grade", grade);
ArrayList plist1 = new ArrayList();
plist1.Add(p1);
plist1.Add(p2);
Object o = dbm.ExecuteScalar(sql2, plist1);
string classNo = o.ToString();
// string sql1 = "select TeacherNo,TeacherName from temp_teacher where SubjectA ='" + subjectno + "'";
string sql3 = "select gcgLectureForm.TeacherNo from gcgLectureForm,temp_teacher where gcgLectureForm.TeacherNo = temp_teacher.TeacherNo and ClassNo = @class_id and CourseNo = @CourseNo";
ParameterStruct p3 = new ParameterStruct("@class_id", classNo);
ParameterStruct p4 = new ParameterStruct("@CourseNo", courseNo);
ArrayList plist2 = new ArrayList();
plist2.Add(p3);
plist2.Add(p4);
Object o1 = dbm.ExecuteScalar(sql3, plist2);
int index = 0;
if (o1 != null)
{
string DefaultTeacherNo = o1.ToString();//拿到这个班这门课原先定下的老师的工号
//先检查这个老师的这门科目是不是擅长的
bool isSubjectA = false;
DataTable ta = ds_teacher_A.Tables["defaultTable"];
for (index = 0; index < ta.Rows.Count; index++)
{
if (DefaultTeacherNo.Equals(ta.Rows[index]["TeacherNo"].ToString()))
{
isSubjectA = true;//是擅长的
break;
}
}
if (!isSubjectA)
{//否则检查是不是非擅长的
DataTable tb = ds_teacher_B.Tables["defaultTable"];
for (index = 0; index < tb.Rows.Count; index++)
{
if (DefaultTeacherNo.Equals(tb.Rows[index]["TeacherNo"].ToString()))
{
break;
}
}
}
}
else
{//如果这个班真的没有分配过老师
index = count_Teacher;
}
d.SelectedIndex = index;//如果o != null,那么不管怎么样总是会拿到一个索引值
Session[d.ID] = index;
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
//ScriptManager1.RegisterAsyncPostBackControl(this.DropDownList_Grade);
ScriptManager1.RegisterAsyncPostBackControl(this.DropDownList_Course);
ScriptManager1.RegisterAsyncPostBackControl(this.DropDownList_semester);
ScriptManager1.RegisterAsyncPostBackControl(this.DropDownList_year);
if (!IsPostBack)
{
if (!(LoginAndPermissionChecking.LoginChecking()))
{
Response.Redirect("/ErrorPage/error_NotLogin.aspx");
}
if (!(LoginAndPermissionChecking.PermissionChecking(PermissionEnum.CourseMaster)))
{
Response.Redirect("/ErrorPage/error_DeniedPermission.aspx");
}
Object o = Session["subjectGroupName"];
if (o == null)
{
Response.Redirect("/login/login.aspx");
}
else
{
//Label_SubjectGroup.Text = Request.QueryString["subjectGroupName"].ToString();
Label_SubjectGroup.Text = Session["subjectGroupName"].ToString();
}
ProvideDataForSelectSubject();
//页面刚刚载入的时候查询学科表中所有学科的对应课时数，并放置在一张学科课时表中，以待后面使用
string sql_MappingCourseNoAndCourseTime = "select CourseNo,CourseTime from gcgCourse";
DBManipulation dbm = new DBManipulation();
SqlDataReader dr = dbm.ExecuteQueryOnLine(sql_MappingCourseNoAndCourseTime, null);
Dictionary <string, Byte> dict_CourseNo_CourseTime = new Dictionary <string, Byte>();
while (dr.Read())
{
dict_CourseNo_CourseTime.Add(dr.GetString(0), dr.GetByte(1));
}
dr.Close();
dbm.Close();
Cache["dict_CourseNo_CourseTime"] = dict_CourseNo_CourseTime;
//同时计算所有老师的授课课时数，放在一个映射表中，以待后面使用
string sql_getTeacherSet = "select TeacherNo from temp_teacher";
DataSet ds = dbm.ExecuteQueryOffLine(sql_getTeacherSet, null);
DataTable teacherTable = ds.Tables["defaultTable"];
Dictionary <string, int> dict_TeacherNo_CourseTime = new Dictionary <string, int>();
foreach (DataRow r in teacherTable.Rows)
{
string TeacherNo = r["TeacherNo"].ToString();
string sql_countTeacherTime = "select sum(CourseTime) from gcgLectureForm,temp_teacher,gcgCourse " +
"where gcgLectureForm.TeacherNo = temp_teacher.TeacherNo " +
"and gcgLectureForm.CourseNo = gcgCourse.CourseNo " +
"and temp_teacher.TeacherNo = '" + TeacherNo + "'";
// DBManipulation dbm = new DBManipulation();
object obj = dbm.ExecuteScalar(sql_countTeacherTime, null);
if (obj == null || obj.ToString() == "")
{//还没有给这个老师安排带班
dict_TeacherNo_CourseTime[TeacherNo] = 0;
}
else
{
dict_TeacherNo_CourseTime[TeacherNo] = int.Parse(obj.ToString());
}
}
Cache["dict_TeacherNo_CourseTime"] = dict_TeacherNo_CourseTime;
//保存每一个下拉菜单的索引值，方便下面进行课时计算
CreateTableBasedOnClass();
setDataForEachTeacherDropDownList();
foreach (DropDownList l in DropDownList_select)
{
Session[l.ID] = l.SelectedIndex;//使用对象的唯一标识符作为唯一ID保存
}
}
//debug1.Text = Request.QueryString["subjectGroupName"].ToString();
CreateTableBasedOnClass();
setDataForEachTeacherDropDownList();
// Page.MaintainScrollPositionOnPostBack = true;//刷新后滚动条回到之前的位置,但是会导致页面闪烁
}