public void Initialize()
{
styleForeColorRed = new DataGridViewCellStyle();
styleForeColorRed.ForeColor = Color.Red;
schedule = new Schedule(StaticSQLiteHelper.GetSchedule());
nightSchedule = new Schedule(StaticSQLiteHelper.GetNightSchedule());
morningSchedule = new Schedule(StaticSQLiteHelper.GetMorningSchedule());
dgv.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
TestConflict();
lockedStyle = new DataGridViewCellStyle();
lockedStyle.BackColor = dgv.DefaultCellStyle.SelectionBackColor;
lockedStyle.ForeColor = dgv.DefaultCellStyle.SelectionForeColor;
LoadClassList();
adjustDataGridViewStyle();
loadClassSchedule(0);
rbExchange.Checked = true;
loadHistory();
}
private void btnCancle_Click(object sender, EventArgs e)
{
//直接根据选中的项的数量来还原
string result = null;
int n = clbHistory.CheckedItems.Count;
for (int i = 0; i < n; i++)
{
result = StaticSQLiteHelper.Recovery();
}
//string result=StaticSQLiteHelper.Recovery();
if (result == null)
{
MessageBox.Show("已经没有可以撤销的操作！");
}
else
{
//MessageBox.Show(result);
schedule = new Schedule(StaticSQLiteHelper.GetSchedule());
nightSchedule = new Schedule(StaticSQLiteHelper.GetNightSchedule());
loadClassSchedule(currentClassID);
loadHistory();
}
}
private int batchExecuteSQL(string[] sql)
{
int buffer = 0;
SQLiteConnection _connection = new SQLiteConnection(this._connectionString);
_connection.Open();
SQLiteTransaction trans = _connection.BeginTransaction();
SQLiteCommand cmd = _connection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.Transaction = trans;
try
{
for (int i = 0; i < sql.Length; i++)
{
cmd.CommandText = sql[i];
cmd.ExecuteNonQuery();
buffer++;
//触发事件
if (buffer == NotifyAfter)
{
if (RowsCopied != null)
{
RowsCopied(this, new SQLiteRowsCopiedEventArgs(buffer, sql.Length));
}
buffer = 0;
}
}
if (buffer != 0)
{
if (RowsCopied != null)
{
RowsCopied(this, new SQLiteRowsCopiedEventArgs(buffer, sql.Length));
}
buffer = 0;
}
//提交事务,只有所有的数据都没有问题才提交事务.
trans.Commit();
//异步压缩,分析数据库,确保所得的分析是最佳的.
ThreadPool.QueueUserWorkItem(new WaitCallback((object o) =>
{
StaticSQLiteHelper.ExecuteNonQuery("VACUUM ANALYZE");
}));
}
catch (SQLiteException)
{
trans.Rollback();
return(0);
}
finally
{
trans.Dispose();
cmd.Dispose();
}
return(sql.Length);
}
private void button2_Click(object sender, EventArgs e)
{
StaticSQLiteHelper.ClearAndVacuumTable("schedule");
int rows = StaticSQLiteHelper.BatchExecuteNonQuery(schedule.ToSQLStringList("schedule"));
MessageBox.Show(string.Format("影响的行数为:{0}", rows), "执行完成");
}
private void Form1_Load(object sender, EventArgs e)
{
this.cbTeachers.DataSource = StaticSQLiteHelper.GetTeacherInformation();
this.cbTeachers.DisplayMember = "教师姓名";
this.cbTeachers.ValueMember = "教师编号";
//this.cbTeachers.SelectedIndex = 0;
}
private void setupWeight()
{
//Global.Empty = true;
StaticSQLiteHelper.ExecuteNonQuery(string.Format("update class_course set 优先级=1 where 周课时<{0};", Global.DayPerWeek));
StaticSQLiteHelper.ExecuteNonQuery(string.Format("update class_course set 优先级=2 where 周课时={0};", Global.DayPerWeek));
StaticSQLiteHelper.ExecuteNonQuery(string.Format("update class_course set 优先级=3 where 周课时>{0};", Global.DayPerWeek));
//StaticSQLiteHelper.ExecuteNonQuery("update class_course set 优先级=0 where 学科名字='体育';");
}
private void cbSubject_SelectedIndexChanged(object sender, EventArgs e)
{
teacherName = cbSubject.SelectedValue.ToString();
dtTeacher = StaticSQLiteHelper.GetSubjectTeacher(teacherName);
cbTeacher.DisplayMember = "教师姓名";
cbTeacher.ValueMember = "教师编号";
cbTeacher.DataSource = dtTeacher;
cbTeacher.SelectedIndex = 0;
}
private void lvQuery_MouseDoubleClick(object sender, MouseEventArgs e)
{
if (currentSelectedItem != null)
{
string sql = (currentSelectedItem as MyListViewItem).SQL;
DataTable dt = StaticSQLiteHelper.ExecuteQuery(sql);
dataGridView1.DataSource = dt;
//MessageBox.Show(sql);
}
}
/// <summary>
/// 载入年级班主任
/// </summary>
public static void LoadGradeClassMaster()
{
_gradeClassMaster = new GradeClassMaster(ClassCount);
DataTable dt = StaticSQLiteHelper.GetGradeClassMaster();
foreach (DataRow row in dt.Rows)
{
_gradeClassMaster.Add(Convert.ToInt32(row[0]), Convert.ToInt32(row[1]));
}
}
private void loadSubjectList()
{
//_subjectList = new List<string>();
string sql = "select distinct(学科名字) as 学科 from class_course order by 学科";
DataTable dt = StaticSQLiteHelper.ExecuteQuery(sql);
cbSubject.SelectedIndexChanged -= new EventHandler(cbSubject_SelectedIndexChanged);
cbSubject.ValueMember = "学科";
cbSubject.DisplayMember = "学科";
cbSubject.DataSource = dt;
cbSubject.SelectedIndexChanged += new EventHandler(cbSubject_SelectedIndexChanged);
}
private void adjust()
{
//设置副科和主科
string sql = "update class_course set 副科=0;";
StaticSQLiteHelper.ExecuteNonQuery(sql);
sql = "update class_course set 副科=1 where 学科名字 in ('政治','历史','地理','体育','音乐','信息')";
//sql = "update class_course set 优先级=3 where 学科名字 in ('政治','历史','地理','体育','音乐','信息')";
StaticSQLiteHelper.ExecuteNonQuery(sql);
sql = "insert into teacher values(0,'无','自习',' ');";
StaticSQLiteHelper.ExecuteNonQuery(sql);
}
private void cmsMenu_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
{
bool night = false;
int section;
string sn = e.ClickedItem.Text;
int dow = currentCellColumnIndex;
if (currentCellRowIndex < Global.LessonPerMorning)
{
morning = true;
night = false;
section = currentCellRowIndex;
}
else if (currentCellRowIndex < Global.LessonPerDay + Global.LessonPerMorning)
{
morning = false;
night = false;
section = currentCellRowIndex - Global.LessonPerMorning;
}
else
{
night = true;
morning = false;
section = currentCellRowIndex - Global.LessonPerDay - Global.LessonPerMorning;
}
int ti = (int)e.ClickedItem.Tag;
if (morning)
{
StaticSQLiteHelper.ReforceSetMorningLesson(currentClassID, dow, section, ti);
}
else if (night)
{
StaticSQLiteHelper.ReforceSetNightLesson(currentClassID, dow, section, ti);
}
else
{
StaticSQLiteHelper.ReforceSetLesson(currentClassID, dow, section, ti);
}
if (ti == 0)
{
dgv.Rows[currentCellRowIndex].Cells[currentCellColumnIndex].Value = "";
}
else
{
dgv.Rows[currentCellRowIndex].Cells[currentCellColumnIndex].Value = sn.Substring(0, 1);
}
loadHistory();
}
private void loadHistory()
{
clbHistory.Items.Clear();
DataTable dt = StaticSQLiteHelper.ExecuteQuery("select time,description from history order by id desc");
foreach (DataRow r in dt.Rows)
{
clbHistory.Items.Add(r[1]);
}
if (clbHistory.Items.Count != 0)
{
clbHistory.SetItemChecked(0, true);
}
}
/// <summary>
/// 载入全年级课程设置
/// </summary>
public static void LoadGradeSubjectSetting()
{
DataTable dt;
_gradeSubjectSetting = new GradeSubjectSetting(ClassCount);
for (int i = 0; i <= ClassCount; i++)
{
dt = StaticSQLiteHelper.GetClassSubjectSetting(i);
ClassSubjectSettingCollection cssc = new ClassSubjectSettingCollection(dt.Rows.Count);
foreach (DataRow row in dt.Rows)
{
cssc.Add(row[0].ToString(), new ClassSubjectSetting(row[0].ToString(), Convert.ToInt32(row[1]), Convert.ToInt32(row[2]), (bool)row[3], row.IsNull(3)?0:Convert.ToInt32(row[3])));
}
_gradeSubjectSetting.Add(i, cssc);
}
}
private void ChangeTeacher()
{
string sql = string.Format("select 教师编号 from teacher where 教师姓名='{0}'", otn);
oti = Convert.ToInt32(StaticSQLiteHelper.ExecuteScalar(sql));
sql = string.Format("select 教师编号 from teacher where 教师姓名='{0}';", ntn);
nti = Convert.ToInt32(StaticSQLiteHelper.ExecuteScalar(sql));
//更新课程设置
sql = string.Format("update class_course set 教师姓名='{0}' where 班级={1} and 学科名字='{2}'", ntn, ci, sn);
StaticSQLiteHelper.ExecuteNonQuery(sql);
//更新白天课程
sql = string.Format("update schedule set teacher_id={0} where class_id={1} and teacher_id={2}", nti, ci, oti);
StaticSQLiteHelper.ExecuteNonQuery(sql);
//更新晚上课程
sql = string.Format("update night set teacher_id={0} where class_id={1} and teacher_id={2}", nti, ci, oti);
StaticSQLiteHelper.ExecuteNonQuery(sql);
}
public void Initialize(bool enabled)
{
dtSubject = StaticSQLiteHelper.GetSubjectList();
subjects = new List <string>();
schedule = new Schedule(StaticSQLiteHelper.GetSchedule());
nightSchedule = new Schedule(StaticSQLiteHelper.GetNightSchedule());
AdjustDataGridView();
UpdateDataGridView();
if (enabled)
{
foreach (DataRow r in dtSubject.Rows)
{
subjects.Add(r[0].ToString());
}
cbSubject.DataSource = subjects;
}
}
/// <summary>
/// 载入全年级教师信息
/// </summary>
public static void LoadGradTeachers()
{
DataTable dt = StaticSQLiteHelper.GetTeacherInformation();
_gradeTeacher = new TeacherCollection(dt.Rows.Count);
int ti;
string tn, sn, ssn;
foreach (DataRow row in dt.Rows)
{
ti = Convert.ToInt32(row[0]);
tn = row[1].ToString();
sn = row[2].ToString();
ssn = row[3].ToString();
_gradeTeacher.Add(ti, new Teacher(ti, tn, sn, ssn));
}
}
private void Import()
{
string sql;
sql = Dictionary2Sql(GetTeacherInforFromTable(_dtClassTeacher));
StaticSQLiteHelper.ClearAndVacuumTable("teacher");
StaticSQLiteHelper.ExecuteNonQuery(sql);
sql = GetTeacherSQLFromTable(_dtClassTeacher);
StaticSQLiteHelper.ClearAndVacuumTable("class_course");
StaticSQLiteHelper.ExecuteNonQuery(sql);
sql = GetCourseAssignSQLFromTable(_dtCourse);
StaticSQLiteHelper.ExecuteNonQuery(sql);
sql = GetMasterSQLFromTable(_dtClassTeacher);
StaticSQLiteHelper.ExecuteNonQuery(sql);
setupWeight();
adjust();
//Global.Dirty = true;
}
private void cbSubject_SelectedIndexChanged(object sender, EventArgs e)
{
if (cbSubject.SelectedItem == null)
{
return;
}
sn = cbSubject.Text;
string sql = string.Format("select distinct(班级)+1 as 班级 from class_course where 学科名字='{0}' order by 班级;", sn);
DataTable dt = StaticSQLiteHelper.ExecuteQuery(sql);
cbClass.SelectedIndexChanged -= new EventHandler(cbClass_SelectedIndexChanged);
cbClass.BeginUpdate();
cbClass.ValueMember = "班级";
cbClass.DisplayMember = "班级";
cbClass.DataSource = dt;
cbClass.SelectedIndex = 0;
cbClass.EndUpdate();
cbClass.SelectedIndexChanged += new EventHandler(cbClass_SelectedIndexChanged);
}
private void cbClass_SelectedIndexChanged(object sender, EventArgs e)
{
if (cbSubject.SelectedItem == null)
{
return;
}
cn = Convert.ToInt32(cbClass.SelectedValue);
ci = cn - 1;
string sql = "select 教师姓名 from class_course where 班级=" + ci.ToString() + " and 学科名字='" + sn + "'";
otn = StaticSQLiteHelper.ExecuteScalar(sql).ToString();
sql = "select distinct(教师姓名) as 教师姓名 from teacher where 教师姓名<>'" + otn + "' and 学科名字='" + sn + "'";
DataTable dt = StaticSQLiteHelper.ExecuteQuery(sql);
cbTeacherName.BeginUpdate();
cbTeacherName.ValueMember = "教师姓名";
cbTeacherName.DisplayMember = "教师姓名";
cbTeacherName.DataSource = dt;
cbTeacherName.EndUpdate();
}
static Global()
{
Random = new Random();
_classCount = StaticSQLiteHelper.GetClassCount();
//------------------------------设置运行参数-----------------------------------
//运行参数
PopulationSize = 50; //种群规模
TotalFitness = PopulationSize * (PopulationSize + 1) / 2; //种群的总适应度之和.
TotalEvolveTime = 1000; //进化的总代数,进化到这么多代后退出
MutateRate = 60; //变异概率
CrossRate = 10; //交叉概率
//--------------------------运行参数设置,请勿随意设置--------------------------------
DayPerWeek = 6; //每周上课的天数
LessonPerDay = 7; //每天安排的节数
LessonPerForenoon = 4; //上午安排的节数
NightLessonPerDay = 3; //晚自习安排的节数
//-------------------------------------------------------------------------------------
if (!_empty)
{
LoadGradTeachers(); //加载全年级教师信息
LoadGradeSubjectSetting(); //加载全年级的课程设置要求
LoadGradeClassMaster(); //加载全年级的班主任列表
_dirty = false;
}
}
public void ReportTeacherSchedule(string fileName)
{
string[] week = new string[7] {
"星期一", "星期二", "星期三", "星期四", "星期五", "星期六", "星期天"
};
string[] subjects;
using (DataTable dt = StaticSQLiteHelper.ExecuteQuery("select distinct(学科名字) from teacher;"))
{
subjects = new string[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
subjects[i] = dt.Rows[i][0].ToString();
}
}
DataTable dts;
int ti, start_row, start_column;
ExcelWriter ew = new ExcelWriter();
for (int s = 0; s < subjects.Length; s++)
{
ew.CreateSheet(subjects[s]);
dts = StaticSQLiteHelper.ExecuteQuery(string.Format("select 教师编号 from teacher where 学科名字='{0}' and 教师姓名 in (select 教师姓名 from class_course);", subjects[s]));
for (int i = 0; i < dts.Rows.Count; i++)
{
ti = Convert.ToInt32(dts.Rows[i][0]);
if (ti == 0)
{
continue;
}
ew.Write(i * (Global.LessonPerDay + Global.NightLessonPerDay + 3), 0, Global.GradeTeachers[ti].Name);
start_row = i * (Global.LessonPerDay + Global.NightLessonPerDay + 3) + 1;
start_column = 0;
for (int w = 0; w < Global.DayPerWeek; w++)
{
ew.Write(start_row, w + 1, week[w]);
}
for (int sec = 1; sec <= Global.LessonPerDay + Global.NightLessonPerDay; sec++)
{
if (chbContainNight.Checked && sec > Global.LessonPerDay)
{
ew.Write(start_row + sec, 0, (sec - Global.LessonPerDay).ToString());
}
else
{
ew.Write(start_row + sec, 0, sec.ToString());
}
}
foreach (TeacherLesson tl in _schedule.TeacherSchedule[ti])
{
ew.Write(start_row + tl.Section + 1, start_column + tl.DayOfWeek + 1, tl.ClassNameListString);
}
//如果包含晚自习
if (chbContainNight.Checked && _nightSchedule.TeacherSchedule.ContainKey(ti))
{
foreach (TeacherLesson tl in _nightSchedule.TeacherSchedule[ti])
{
ew.Write(start_row + tl.Section + Global.LessonPerDay + 1, start_column + tl.DayOfWeek + 1, tl.ClassNameListString);
}
}
}
}
ew.SaveAs(fileName);
}
public void loadSchedule()
{
//DataTable dt = StaticSQLiteHelper.GetSchedule();
_schedule = new Schedule(StaticSQLiteHelper.GetSchedule());
_nightSchedule = new Schedule(StaticSQLiteHelper.GetNightSchedule());
}
private void dgv_CellMouseClick(object sender, DataGridViewCellMouseEventArgs e)
{
if (e.Button == MouseButtons.Right)
{
if (e.RowIndex < Global.LessonPerMorning)
{
morning = true;
night = false;
section = e.RowIndex;
}
else if (e.RowIndex < Global.LessonPerDay + Global.LessonPerMorning)
{
morning = false;
night = false;
section = e.RowIndex - Global.LessonPerMorning;
}
else
{
night = true;
holdMoring = false;
section = e.RowIndex - Global.LessonPerDay - Global.LessonPerMorning;
}
if (operationState == OperationState.ExchangeMode)
{
if (hold)
{
//如果要交换的两节课为同一门课或者要交换的两节课一节是白天一节是晚自习,都返回失败
//if( (e.RowIndex == holdCellRowIndex && e.ColumnIndex == holdCellColumnIndex)|| (e.RowIndex -Global.LessonPerMorning == holdCellRowIndex && e.ColumnIndex == holdCellColumnIndex) || (e.RowIndex-Global.LessonPerDay-Global.LessonPerMorning == holdCellRowIndex && e.ColumnIndex == holdCellColumnIndex) ||(night!=holdNight)||(morning!=holdMoring))
if ((night != holdNight) || (morning != holdMoring) ||
(night && e.RowIndex - Global.LessonPerMorning - Global.LessonPerDay == holdCellRowIndex && e.ColumnIndex == holdCellColumnIndex) ||
(morning && e.RowIndex == holdCellRowIndex && e.ColumnIndex == holdCellColumnIndex) ||
(!morning && !night && e.RowIndex - Global.LessonPerMorning == holdCellRowIndex && e.ColumnIndex == holdCellColumnIndex)
)
{
setHoldState(false);
tslExchange.Text = "不能更换";
}
else
{
if (morning)
{
//交换缓存的课程表中的数据
morningSchedule[currentClassID].Exchange2Lesson(e.ColumnIndex, section, holdCellColumnIndex, holdCellRowIndex);
//更新数据库中数据
StaticSQLiteHelper.Exchange2MorningLesson(currentClassID, e.ColumnIndex, section, holdCellColumnIndex, holdCellRowIndex);
}
if (night)
{
//交换缓存的课程表中的数据
nightSchedule[currentClassID].Exchange2Lesson(e.ColumnIndex, section, holdCellColumnIndex, holdCellRowIndex);
//更新数据库中数据
StaticSQLiteHelper.Exchange2NightLesson(currentClassID, e.ColumnIndex, section, holdCellColumnIndex, holdCellRowIndex);
}
else
{
//交换缓存的课程表中的数据
schedule[currentClassID].Exchange2Lesson(e.ColumnIndex, section, holdCellColumnIndex, holdCellRowIndex);
//更新数据库中数据
///有问题.....
StaticSQLiteHelper.Exchange2Lesson(currentClassID, e.ColumnIndex, section, holdCellColumnIndex, holdCellRowIndex);
}
//重新加载以刷新界面
loadClassSchedule(currentClassID);
loadHistory();
setHoldState(false);
TestConflict();
}
}
else
{
setHoldState(true);
}
}
else if (operationState == OperationState.ReforceMode)
{
selectCell(e.RowIndex, e.ColumnIndex, true);
cmsMenu.Show(dgv, e.X + dgv.CurrentCell.Size.Width * e.ColumnIndex + dgv.RowHeadersWidth, e.Y + dgv.CurrentCell.Size.Height * e.RowIndex + dgv.ColumnHeadersHeight);
}
}
}