protected void SetItemForDropDownList_Class()
{
string sql = "select distinct ClassNo,class_name from gcgLectureForm, temp_class where gcgLectureForm.ClassNo = temp_class.class_id and class_grade = '" + DropDownList_Grade.SelectedValue + "'";
DBManipulation dbm = new DBManipulation();
DropDownList_class.Items.Clear();
/*
* DataSet ds = dbm.ExecuteQueryOffLine(sql, null);
* DropDownList_class.AutoPostBack = true;
* DropDownList_class.DataSource = ds.Tables["defaultTable"];
* DropDownList_class.DataTextField = ds.Tables["defaultTable"].Columns[1].ColumnName;
* DropDownList_class.DataValueField = ds.Tables["defaultTable"].Columns[0].ColumnName;
* DropDownList_class.DataBind();
*/
SqlDataReader dr = dbm.ExecuteQueryOnLine(sql, null);
while (dr.Read())
{
ListItem item = new ListItem(dr.GetString(1), dr.GetString(0));
DropDownList_class.Items.Add(item);
}
dr.Close();
dbm.Close();
}
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 login_Click(object sender, EventArgs e)
{ //用户点击登入
string userName = username.Text;
string token = password.Text; //Token：令牌
string sql = "select * from UserTable where username = @username and token = @token";
ArrayList parameterList = new ArrayList();
ParameterStruct p1 = new ParameterStruct("@username", userName);//设置参数映射列表
ParameterStruct p2 = new ParameterStruct("@token", token);
parameterList.Add(p1);
parameterList.Add(p2);
DBManipulation dbm = new DBManipulation();
SqlDataReader dataReader = dbm.ExecuteQueryOnLine(sql, parameterList);
if (dataReader.HasRows) //如果有记录，说明是合法账户
{
dataReader.Read(); //指针后移，读取第一条记录
string position = dataReader.GetString(3); //获得身份
Session["username"] = dataReader.GetString(1);
dataReader.Close();
dbm.Close();
switch (position)
{
case "教导主任":
Session["UserRank"] = PermissionEnum.EducationDean;
Response.Redirect("../educationDean/TimeArrange.aspx");
break;
case "学科组长":
Session["UserRank"] = PermissionEnum.CourseMaster;
Session["subjectGroupName"] = "综合组";
Response.Redirect("../courseMaster/setTeacher.aspx");
break;
case "排课组长":
Session["UserRank"] = PermissionEnum.AcademicDean;
Response.Redirect("../academicDean/courseArrangement.aspx");
break;
case "教师":
Session["UserRank"] = PermissionEnum.Teacher;
Response.Redirect("../Teacher/viewSchedule_Class.aspx");
break;
default:
break;
}
}
else
{
Response.Redirect("error.aspx");
}
}
/*
* 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 string[] ProvideLessonDataByClass(string _grade, string _class)
{
DBManipulation dbm = new DBManipulation();
string sql2 = "select subjectname,gcgLesson.Timeid from gcgLesson, gcgLectureForm, gcgSchedule, gcgCourse, gcgSubject, temp_class where gcgLesson.LectureNo = gcgLectureForm.LectureNo and gcgLectureForm.CourseNo = gcgCourse.CourseNo and gcgCourse.SubjectNo = gcgSubject.subjectno and gcgLesson.Timeid = gcgSchedule.Timeid and gcgLectureForm.ClassNo = temp_class.class_id and ClassNo = @classv and class_grade = @gradev order by gcgLesson.Timeid";
ParameterStruct p1 = new ParameterStruct("@gradev", _grade);
ParameterStruct p2 = new ParameterStruct("@classv", _class);
ArrayList _plist = new ArrayList();
_plist.Add(p1);
_plist.Add(p2);
SqlDataReader dr = dbm.ExecuteQueryOnLine(sql2, _plist);
string[] class_1 = new string[40 + 1];
int i = 1;
while (dr.Read())
{
class_1[i] = dr.GetString(0);
i++;
}
dr.Close();
dbm.Close();
return(class_1);
}
protected void CreateTheWholeSchedule()
{
Table1.Rows.Clear();
Table1.Rows.Clear();
TableRow head = new TableRow();
TableCell t0 = new TableCell();
TableCell t1 = new TableCell();
t1.Text = "星期一";
TableCell t2 = new TableCell();
t2.Text = "星期二";
TableCell t3 = new TableCell();
t3.Text = "星期三";
TableCell t4 = new TableCell();
t4.Text = "星期四";
TableCell t5 = new TableCell();
t5.Text = "星期五";
head.Controls.Add(t0);
head.Controls.Add(t1);
head.Controls.Add(t2);
head.Controls.Add(t3);
head.Controls.Add(t4);
head.Controls.Add(t5);
head.CssClass = "aspTableRow1";
Table1.Controls.Add(head);
DBManipulation dbm = new DBManipulation();
string sql2 = "select subjectname,gcgLesson.Timeid from gcgLesson, gcgLectureForm, gcgSchedule, gcgCourse, gcgSubject, temp_class where gcgLesson.LectureNo = gcgLectureForm.LectureNo and gcgLectureForm.CourseNo = gcgCourse.CourseNo and gcgCourse.SubjectNo = gcgSubject.subjectno and gcgLesson.Timeid = gcgSchedule.Timeid and gcgLectureForm.ClassNo = temp_class.class_id and ClassNo = @classv and class_grade = @gradev order by gcgLesson.Timeid";
ParameterStruct p1 = new ParameterStruct("@gradev", DropDownList_Grade.SelectedValue);
ParameterStruct p2 = new ParameterStruct("@classv", DropDownList_class.SelectedValue);
ArrayList _plist = new ArrayList();
_plist.Add(p1);
_plist.Add(p2);
SqlDataReader dr = dbm.ExecuteQueryOnLine(sql2, _plist);
string[] class_1 = new string[40 + 1];
int kk = 0;
while (dr.Read())
{
class_1[kk] = dr.GetString(0);
kk++;
}
dr.Close();
dbm.Close();
for (int i = 0; i < 8; i++)
{
TableRow tablerow = new TableRow();
tablerow.CssClass = "aspTableRow2";
TableCell tc = new TableCell();
tc.CssClass = "aspTableCell4";//第几节
tc.Text = "第" + (i + 1) + "节";
tablerow.Controls.Add(tc);
for (int j = 0; j < 5; j++)
{
TableCell cell = new TableCell();
cell.Text = class_1[i + j * 8];
cell.CssClass = "aspTableCell2";//具体安排
tablerow.Controls.Add(cell);
}
Table1.Controls.Add(tablerow);
}
}
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();
}
private void SelectCourseByGrade()
{
Table1.Controls.Clear(); //把原先的表全部清干净,注意动态生成的时候绝对要把全部的行都清掉，否则会出现玄学，仔细检查Rows.count
TableCellLists.Clear(); //把保存的上一次的控件引用清理掉，不需要了，这次会生成一个新的表
DropDownLists.Clear();
string grade = DropDownList_Grade.SelectedValue;
//重新设置表头
TableRow tableRowHeader = new TableRow();
tableRowHeader.CssClass = "aspTableRow1";
TableCell tablecell_courseNameHeader = new TableCell();
tablecell_courseNameHeader.Text = "科目";
//td.Attributes.Add("onclick", "javascript:alert('" + day + "');");
//tablecell_courseNameHeader.Attributes.Add("onclick", "javascript:alert('1111111111')");
//这段代码可以给动态生成的TableCell加事件，用来捕捉用户点击
tablecell_courseNameHeader.CssClass = "aspTableCell";
TableCell tablecell_courseCountHeader = new TableCell();
tablecell_courseCountHeader.Text = "课时";
tablecell_courseCountHeader.CssClass = "aspTableCell";
TableCell tablecell_deleteButtonHeader = new TableCell();
tablecell_deleteButtonHeader.Text = "操作";
tablecell_deleteButtonHeader.CssClass = "aspTableCell";
tableRowHeader.Controls.Add(tablecell_courseNameHeader);
tableRowHeader.Controls.Add(tablecell_courseCountHeader);
tableRowHeader.Controls.Add(tablecell_deleteButtonHeader);
Table1.Controls.Add(tableRowHeader);
//根据年级查该年级的所有教学科目
string sql = "select CourseNo,subjectname,CourseTime from gcgCourse, gcgSubject where gcgCourse.SubjectNo = gcgSubject.subjectno and Grade = @Grade";
ParameterStruct p = new ParameterStruct("@Grade", grade);
ArrayList parameterList = new ArrayList();
parameterList.Add(p);
DBManipulation dbm = new DBManipulation();
SqlDataReader dataReader = dbm.ExecuteQueryOnLine(sql, parameterList);
int count = 0;
//循环增加行
while (dataReader.Read())
{
//准备一个行对象，和三个单元格对象
TableRow tableRow = new TableRow();
TableCell tablecell_courseName = new TableCell();
TableCell tablecell_courseCount = new TableCell();
TableCell tablecell_deleteButton = new TableCell();
//第一个单元格存放科目名
tablecell_courseName.ID = "TableCell_CourseName_" + count;
tablecell_courseName.Text = dataReader.GetString(1);
tablecell_courseName.CssClass = "aspTableCell";
TableCellLists.Add(tablecell_courseName);//保存引用
//第二个单元格存放课时下拉框，记得添加事件处理
tablecell_courseCount.ID = "TableCell_CourseCount_" + count;
tablecell_courseCount.CssClass = "aspTableCell";
DropDownList dropDownList = new DropDownList();
dropDownList.ID = "DropDownCell_" + count;
dropDownList.SelectedIndexChanged += DropDownList_CourseCount_TextChanged;//监听事件
dropDownList.AutoPostBack = true;
ListItem[] items = new ListItem[9];
for (int i = 0; i < items.Length; i++)
{
items[i] = new ListItem();
// string str = " " + i;
//ListItem.Text = HttpUtility.HtmlDecode(" ")+">>SubItem1";
items[i].Text = HttpUtility.HtmlDecode(" ") + i + "";
//items[i].Text = str;
items[i].Value = +i + "";
dropDownList.Items.Add(items[i]);
}
int CourseTime = dataReader.GetByte(2); //注意我们的CourseTime字段是tinyint，是Byte类型，不能用GetString()
dropDownList.SelectedIndex = CourseTime; //下拉框的初始值
this.DropDownLists.Add(dropDownList); //保存引用，方便其他函数使用
tablecell_courseCount.Controls.Add(dropDownList);
//第三个单元格存放删除按钮
tablecell_deleteButton.ID = "TableButton_" + count;
tablecell_deleteButton.CssClass = "aspTableCell";
Button button = new Button();
button.Text = "删除";
//button.CommandArgument = button.ID;
button.CssClass = "button white bigrounded";
button.ID = "Button_" + count;
button.Click += ButtonDelete_Click;
ButtonLists.Add(button);
tablecell_deleteButton.Controls.Add(button);
//将三个单元格放入行对象内
tableRow.ID = "TableRow_" + count;
tableRow.CssClass = "aspTableRow2";
tableRow.Controls.Add(tablecell_courseName);
tableRow.Controls.Add(tablecell_courseCount);
tableRow.Controls.Add(tablecell_deleteButton);
//将这个行放入表中
Table1.Controls.Add(tableRow);
count++;
}
dataReader.Close();
dbm.Close();
lessLessonTime.Text = lessHour() + "";//每次生成页面都计算一下剩余课时
}
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 createtable(string semeterNo)
{
Table1.Rows.Clear();
ArrayList buttonlist = new ArrayList();//存放所有按钮的信息
//形参是学期号，蕴含年份和上下学期
string grade = DropDownList_Grade.SelectedValue.ToString();
//查出这个年级的所有学科，以便形成列表头
//一个年级的学科是没有历史记录的，设定即覆盖
string sql1 = "select CourseNo,subjectname from gcgCourse, gcgSubject where gcgCourse.SubjectNo = gcgSubject.subjectno and Grade = @Grade";
ParameterStruct p_grade = new ParameterStruct("@Grade", grade);
ArrayList parameterList1 = new ArrayList();
parameterList1.Add(p_grade);
DBManipulation dbm = new DBManipulation();
SqlDataReader dataReader1 = dbm.ExecuteQueryOnLine(sql1, parameterList1);
List <string> courseset = new List <string>();
courseset.Clear();//习惯性清空
if (!dataReader1.HasRows)
{
return;
}
TableRow tableRow = new TableRow();
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);
// int id = 0;
while (dataReader1.Read())
{
TableCell cell_CourseName = new TableCell();
cell_CourseName.Text = dataReader1.GetString(1);//文本为科目名
// 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();
//---------------------------------------------分割线，自此表头学科一栏生成完毕--------------------------------------------
//-------------------------------------------------以下开始按行生成数据----------------------------------------------------
// ParameterStruct p_year = new ParameterStruct("@year", year+"%");
ParameterStruct p_semesterNo = new ParameterStruct("@SemesterNo", semeterNo);
ArrayList parameterList2 = new ArrayList();
parameterList2.Add(p_grade);
parameterList2.Add(p_semesterNo);
dbm = new DBManipulation();
//查询出某一个学期某一个年级的所有班级
string sql2 = "select distinct class_id,class_name from temp_class,gcgLectureForm where temp_class.class_id = gcgLectureForm.ClassNo and class_grade = @Grade and SemesterNo = @SemesterNo order by class_id asc";
SqlDataReader dataReader2 = dbm.ExecuteQueryOnLine(sql2, parameterList2);
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;
//string SemesterNo = GetSemesterNo();
for (int i = 0; i < classset.Count; i++)
{
ClassInfo cinfo = classset[i];
//依据班级号和学期号查询这些班级在特定时间段的授课记录，一个班一个班的显示数据
sql3 = "select gcgLectureForm.TeacherNo,TeacherName,subjectname,gcgCourse.CourseNo from gcgLectureForm,gcgCourse,gcgSubject,temp_teacher where gcgCourse.CourseNo = gcgLectureForm.CourseNo and gcgCourse.SubjectNo = gcgSubject.subjectno and temp_teacher.TeacherNo = gcgLectureForm.TeacherNo and ClassNo = @ClassNo and SemesterNo = @SemesterNo";
ParameterStruct p_classNo = new ParameterStruct("@ClassNo", cinfo.No);
ParameterStruct p_semester = new ParameterStruct("@SemesterNo", semeterNo);
ArrayList parameterList3 = new ArrayList();
parameterList3.Add(p_classNo);
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(0), dataReader3.GetString(1), dataReader3.GetString(2), dataReader3.GetString(3));//no,name,sbj,courseno
tset.Add(tdata);
}
tableRow = new TableRow();
//tableRow.CssClass = "aspTableRow1";
//先加入一个班级格子,显示这个班级的名字
TableCell cell_class = new TableCell();
// cell_class.Attributes.Add("onclick", "getdate(this)");
cell_class.Text = cinfo.Name;
// cell_class.ID = "cell_class_" + i + times;
cell_class.CssClass = "aspTableCell4";
tableRow.Controls.Add(cell_class);
//循环学科链表，为每一行（每一个班）的每一门学科填入对应老师
for (int j = 0; j < courseset.Count; j++)
{
string s1 = courseset[j];
// foreach (string s1 in courseset) {
TableCell cell = new TableCell();
cell.CssClass = "aspTableCell";//先设置格子的样式为默认样式
// cell.Attributes.Add("onclick", "getdata()");
//通过遍历这个班的老师链表实现
foreach (TeaInfo s2 in tset)
{
if (s1.Equals(s2.Sbj))
{
// public MyButton(string class_id,string CourseNo,string TeacherNo) {
MyButton b = new MyButton(cinfo.No, s2.Courseno, s2.No);
b.Text = s2.Name;
//bt.Click += new EventHandler(bt_Click);
string buttonName = b.Class_id + b.CourseNo1;
if (Session[buttonName] == null)
{
// Session[buttonName] = "namebutton";
Session[buttonName] = "aspTableCell";
}
b.Click += new EventHandler(ButtonForFeedBack_onClick);
// b.CssClass = Session[buttonName].ToString();//如果刷新之前已经有记录，那么直接用之前的样式
b.CssClass = "namebutton";
buttonlist.Add(b);
//cell.Text = s2.Name;// dataReader.GetString(1);
cell.Controls.Add(b);
cell.CssClass = Session[buttonName].ToString();//看要不要改样式
break;
}
}
tableRow.Controls.Add(cell);
}
Table1.Controls.Add(tableRow);
dataReader3.Close();//注意每一次循环都会执行一次数据库查询，返回一个DataReader，所以这次循环体执行完之后必须将其关闭，避免占用connection
}
dbm.Close();
Session["buttonlist"] = buttonlist;
// times++;
}
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;//刷新后滚动条回到之前的位置,但是会导致页面闪烁
}