private int AddCourseToNewTeacher(string teacher_id, string course_id)
{
DataTable old_courses = SQL_Help.ExecuteDataTable("select courses from teacher_info where id=@id;", connection, new MySqlParameter[] { new MySqlParameter("@id", MySqlDbType.VarChar)
{
Value = teacher_id
} });
List <string> old_courses_list = new List <string>(old_courses.Rows[0][0].ToString().Split(','));
if (old_courses_list[0].Length == 0)
{
old_courses_list.Clear();
}
old_courses_list.Add(course_id);
string new_courses = string.Join(",", old_courses_list.ToArray());
return(SQL_Help.ExecuteNonQuery("update teacher_info set courses=@courses where id=@id;", connection, new MySqlParameter[]
{
new MySqlParameter("@id", MySqlDbType.VarChar)
{
Value = teacher_id
},
new MySqlParameter("@courses", MySqlDbType.Text)
{
Value = new_courses
}
}));
}
private void course_id_textBox_Leave(object sender, EventArgs e)
{
if (course_id_textBox.Text.Length == 0)
{
return;
}
DataTable dt = SQL_Help.ExecuteDataTable("select distinct name,credit,grade_limit,canceled_year from course_info where id=@id;", connection, new MySqlParameter[] { new MySqlParameter("@id", MySqlDbType.VarChar)
{
Value = course_id_textBox.Text
} });
if (dt.Rows.Count == 0)
{
cancel_year_textBox.ReadOnly = false;
course_name_textBox.ReadOnly = false;
credit_textBox.ReadOnly = false;
grade_comboBox.Enabled = true;
course_name_textBox.Clear();
credit_textBox.Clear();
grade_comboBox.Text = "";
cancel_year_textBox.Clear();
}
else
{
course_name_textBox.Text = dt.Rows[0][0].ToString();
credit_textBox.Text = dt.Rows[0][1].ToString();
grade_comboBox.Text = dt.Rows[0][2].ToString();
cancel_year_textBox.Text = dt.Rows[0][3].ToString();
course_name_textBox.ReadOnly = true;
credit_textBox.ReadOnly = true;
grade_comboBox.Enabled = false;
cancel_year_textBox.ReadOnly = true;
}
}
private void course_comboBox_TextChanged(object sender, EventArgs e)
{
if (course_comboBox.Items.Contains(course_comboBox.Text))
{
teacher_comboBox.Items.Clear();
year_comboBox.Items.Clear();
DataTable teacher_dt = SQL_Help.ExecuteDataTable("select distinct course_info.teacher_id,teacher_info.name " +
"from course_info join teacher_info on (teacher_info.id=course_info.teacher_id) " +
"where course_info.id = @course_id;", connection, new MySqlParameter[]
{
new MySqlParameter("@course_id", MySqlDbType.VarChar)
{
Value = course_comboBox.Text.Substring(0, 7)
}
});
for (int i = 0; i < teacher_dt.Rows.Count; i++)
{
teacher_comboBox.Items.Add(string.Format("{0}({1})", teacher_dt.Rows[i][0], teacher_dt.Rows[i][1]));
}
if (teacher_dt.Rows.Count != 0)
{
teacher_comboBox.Text = teacher_comboBox.Items[0].ToString();
}
}
else
{
teacher_comboBox.Items.Clear();
year_comboBox.Items.Clear();
}
}
private void teacher_query_button_Click(object sender, EventArgs e)
{
if (teacher_current_id_textBox.Text.Length == 0)
{
Common.ShowError("Null value error!", "ID can not be empty!");
return;
}
teacher_current_id_textBox.ReadOnly = true;
MySqlParameter teacher_id_parameter = new MySqlParameter("@id", MySqlDbType.VarChar)
{
Value = teacher_current_id_textBox.Text
};
DataTable current_info_dt = SQL_Help.ExecuteDataTable("select name from teacher_info where id=@id;", connection, new MySqlParameter[] { teacher_id_parameter });
if (current_info_dt.Rows.Count == 0)
{
Common.ShowError("Empty query result!", "No corresponding teacher found with id = " + teacher_current_id_textBox.Text + ".\nPlease check again.");
teacher_current_id_textBox.ReadOnly = false;
}
else
{
teacher_new_name_textBox.Text = teacher_current_name_textBox.Text = current_info_dt.Rows[0][0].ToString();
teacher_new_id_textBox.Text = teacher_current_id_textBox.Text;
teacher_new_info_groupBox.Enabled = true;
teacher_apply_button.Enabled = true;
teacher_delete_button.Enabled = true;
}
DataTable teach_course_dt = SQL_Help.ExecuteDataTable("select id,name from course_info where teacher_id=@id;", connection, new MySqlParameter[] { teacher_id_parameter });
teacher_courses_textBox.Clear();
for (int i = 0; i < teach_course_dt.Rows.Count; i++)
{
teacher_courses_textBox.Text += string.Format("{0},{1}" + Environment.NewLine, teach_course_dt.Rows[i][0].ToString(), teach_course_dt.Rows[i][1].ToString());
}
}
private void ModifyInfo_Load(object sender, EventArgs e)
{
switch (role)
{
case Common.UserType.STUDENT:
Enabled = false;
break;
case Common.UserType.TEACHER:
student_info_tabPage.Parent = null;
course_info_tabPage.Parent = null;
course_choosing_info_tabPage.Parent = null;
break;
case Common.UserType.ADMIN:
break;
default:
break;
}
DataTable teacher_dt = SQL_Help.ExecuteDataTable("select id,name from teacher_info;", connection);
for (int i = 0; i < teacher_dt.Rows.Count; i++)
{
course_teacher_mapping.Add(teacher_dt.Rows[i][0].ToString(), teacher_dt.Rows[i][1].ToString());
course_new_teacher_comboBox.Items.Add(string.Format("{0}({1})", teacher_dt.Rows[i][0].ToString(), teacher_dt.Rows[i][1].ToString()));
}
course_new_year_textBox.Text = "Optional";
course_new_year_textBox.ForeColor = Color.Gray;
}
private void RefreshYearData()
{
DataTable year_dt = SQL_Help.ExecuteDataTable("select distinct chosen_year from course_choosing_info where (teacher_id=@teacher_id and course_id=@course_id);", connection, new MySqlParameter[]
{
new MySqlParameter("@teacher_id", MySqlDbType.VarChar)
{
Value = current_teacher_id
},
new MySqlParameter("@course_id", MySqlDbType.VarChar)
{
Value = course_comboBox.Text.Substring(0, 7)
}
});
if (year_dt.Rows.Count == 0)
{
Common.ShowInfo("Empty query result!", "Currently there is no student in course " + course_comboBox.Text + "!");
ClearDGV();
return;
}
for (int i = 0; i < year_dt.Rows.Count; i++)
{
year_comboBox.Items.Add(year_dt.Rows[i][0]);
}
year_comboBox.Text = year_comboBox.Items[0].ToString();
}
private void teacher_comboBox_TextChanged(object sender, EventArgs e)
{
DataTable year_dt = SQL_Help.ExecuteDataTable("select distinct chosen_year " +
"from course_choosing_info " +
"where course_id = @cid and teacher_id = @tid;", connection, new MySqlParameter[]
{
new MySqlParameter("@cid", MySqlDbType.VarChar)
{
Value = course_comboBox.Text.Substring(0, 7)
},
new MySqlParameter("@tid", MySqlDbType.VarChar)
{
Value = (year_comboBox.Items.Count == 0?teacher_comboBox.Items[0].ToString():teacher_comboBox.Text).Substring(0, 5)
}
});
year_comboBox.Items.Clear();
for (int i = 0; i < year_dt.Rows.Count; i++)
{
year_comboBox.Items.Add(string.Format("{0}", year_dt.Rows[i][0]));
}
if (year_dt.Rows.Count != 0)
{
year_comboBox.Text = year_comboBox.Items[0].ToString();
}
}
private void class_ComboBox_SelectedIndexChanged(object sender, EventArgs e)
{
alternative_listBox.Items.Clear();
if (class_ComboBox.Text == "All class...")
{
for (int i = 0; i < all_student_items.Count; i++)
{
alternative_listBox.Items.Add(all_student_items[i]);
}
return;
}
int index = class_combobox_items.IndexOf(class_ComboBox.Text);
if (index != -1)
{
DataTable student_dt = SQL_Help.ExecuteDataTable("select id,name,class from student_info where class = @classname", connection, new MySqlParameter[] { new MySqlParameter("@classname", MySqlDbType.VarChar)
{
Value = class_ComboBox.Text
} });
for (int i = 0; i < student_dt.Rows.Count; i++)
{
alternative_listBox.Items.Add(student_dt.Rows[i][0] + "(" + student_dt.Rows[i][1] + "," + student_dt.Rows[i][2] + ")");
}
}
else
{
alternative_listBox.Items.Clear();
}
}
private void AddCourseChoosingInfo_Load(object sender, EventArgs e)
{
year_textBox.Text = "Input chosen year here...";
year_textBox.ForeColor = Color.Gray;
student_id_ComboBox.Text = "Input student id here to add single student...";
student_id_ComboBox.ForeColor = Color.Gray;
course_dt = SQL_Help.ExecuteDataTable("select distinct id,name from course_info;", connection);
for (int i = 0; i < course_dt.Rows.Count; i++)
{
string comboBox_item = course_dt.Rows[i][0] + "(" + course_dt.Rows[i][1] + ")";
course_id_ComboBox.Items.Add(comboBox_item);
course_combobox_items.Add(comboBox_item);
}
class_dt = SQL_Help.ExecuteDataTable("select distinct class from student_info;", connection);
for (int i = 0; i < class_dt.Rows.Count; i++)
{
string comboBox_item = class_dt.Rows[i][0].ToString();
class_ComboBox.Items.Add(comboBox_item);
class_combobox_items.Add(comboBox_item);
}
class_ComboBox.Items.Add("All class...");
DataTable student_dt = SQL_Help.ExecuteDataTable("select id,name,class from student_info;", connection);
for (int i = 0; i < student_dt.Rows.Count; i++)
{
string comboBox_item = student_dt.Rows[i][0] + "(" + student_dt.Rows[i][1] + "," + student_dt.Rows[i][2] + ")";
all_student_items.Add(comboBox_item);
student_id_ComboBox.Items.Add(comboBox_item);
}
}
private void MainWindow_Load(object sender, EventArgs e)
{
user_text_menuitem.Text = "User: "******"Role: Student";
queryTeacherInformationToolStripMenuItem.Visible = false;
break;
case Common.UserType.TEACHER:
role_text_menuitem.Text = "Role: Teacher";
submitScoreToolStripMenuItem.Visible = true;
break;
case Common.UserType.ADMIN:
role_text_menuitem.Text = "Role: Administrator";
addAccountToolStripMenuItem.Visible = true;
resetUserPasswordToolStripMenuItem.Visible = true;
addCourseToolStripMenuItem.Visible = true;
addCourseChoosingInfoToolStripMenuItem.Visible = true;
modifyInformationToolStripMenuItem.Visible = true;
break;
default:
role_text_menuitem.Text = "Role: Null";
break;
}
}
private void CourseAddProcess_Load(object sender, EventArgs e)
{
for (int i = 0; i < students.Count; i++)
{
DataRow dr = students_dt.NewRow();
dr["state"] = "Ready";
dr["student"] = students[i].ToString();
dr["teacher"] = teacher;
dr["course"] = course;
dr["year"] = year;
students_dt.Rows.Add(dr);
}
BindingSource bs = new BindingSource();
bs.DataSource = students_dt;
dgv.DataSource = bs;
dgv.Font = new Font("微软雅黑", 10.8F);
string[] head_texts = { "Student Info", "Teacher Info", "Course Info", "Chosen year" };
string[] column_names = { "student", "teacher", "course", "year" };
for (int i = 1; i < dgv.Columns.Count; i++)
{
dgv.Columns[i].HeaderText = head_texts[i - 1];
dgv.Columns[i].Name = column_names[i - 1];
dgv.Columns[i].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
}
//for (int i = 0; i < dgv.Rows.Count; i++)
//{
// dgv.Rows[i].Cells["state"].Style.ForeColor = Color.Goldenrod;
// students_dt.Rows[i]["state"] = "Ready";
//}
DataTable course_limit = SQL_Help.ExecuteDataTable("select grade_limit,canceled_year from course_info where id=@id", connection, new MySqlParameter[] { new MySqlParameter("@id", MySqlDbType.VarChar)
{
Value = course.Substring(0, 7)
} });
grade_limit = course_limit.Rows[0][0].ToString();
cancel_limit = course_limit.Rows[0][1].ToString();
if (DateTime.Now.Month <= 8)
{
school_year_label.Text = "School year: " + (DateTime.Now.Year - 1) + "-" + DateTime.Now.Year;
if (DateTime.Now.Month <= 2)
{
semester_label.Text = "Semester: 1";
}
else
{
semester_label.Text = "Semester: 2";
}
}
else
{
school_year_label.Text = "School year: " + DateTime.Now.Year + "-" + (DateTime.Now.Year + 1);
semester_label.Text = "Semester: 1";
}
}
private void dgv_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
if (e.RowIndex >= 0 && dgv.Rows[e.RowIndex].Cells[e.ColumnIndex] is DataGridViewButtonCell)
{
if (dgv.Columns[e.ColumnIndex].DefaultCellStyle.NullValue.ToString() == "Modify")
{
using (ChoosingInfoModify cim = new ChoosingInfoModify()
{
connection = connection,
year = dgv.Rows[e.RowIndex].Cells["Chosen Year"].Value.ToString(),
score = dgv.Rows[e.RowIndex].Cells["Score"].Value.ToString(),
student = string.Format("{0}({1})", dgv.Rows[e.RowIndex].Cells["Student ID"].Value, dgv.Rows[e.RowIndex].Cells["Student Name"].Value),
teacher = string.Format("{0}({1})", dgv.Rows[e.RowIndex].Cells["Teacher ID"].Value, dgv.Rows[e.RowIndex].Cells["Teacher Name"].Value),
course = string.Format("{0}({1})", dgv.Rows[e.RowIndex].Cells["Course ID"].Value, dgv.Rows[e.RowIndex].Cells["Course Name"].Value),
credit = dgv.Rows[e.RowIndex].Cells["Credit"].Value.ToString(),
role = Common.UserType.ADMIN
})
{
cim.ShowDialog();
choosing_clear_button.PerformClick();
choosing_query_button.PerformClick();
}
}
else if (dgv.Columns[e.ColumnIndex].DefaultCellStyle.NullValue.ToString() == "Delete")
{
if (Common.ShowChoice("Delete Confirm", string.Format("Are you sure to delete the record ({0},{1},{2},{3})", dgv.Rows[e.RowIndex].Cells["Student Name"].Value, dgv.Rows[e.RowIndex].Cells["Course Name"].Value, dgv.Rows[e.RowIndex].Cells["Teacher Name"].Value, dgv.Rows[e.RowIndex].Cells["Chosen Year"].Value) + "?", MessageBoxIcon.Warning) == DialogResult.Yes)
{
int delete_result = SQL_Help.ExecuteNonQuery("delete from course_choosing_info where (course_id=@course_id and teacher_id=@teacher_id and student_id=@student_id and chosen_year=@year);", connection, new MySqlParameter[]
{
new MySqlParameter("@course_id", MySqlDbType.VarChar)
{
Value = dgv.Rows[e.RowIndex].Cells["Course ID"].Value
},
new MySqlParameter("@teacher_id", MySqlDbType.VarChar)
{
Value = dgv.Rows[e.RowIndex].Cells["Teacher ID"].Value
},
new MySqlParameter("@student_id", MySqlDbType.VarChar)
{
Value = dgv.Rows[e.RowIndex].Cells["Student ID"].Value
},
new MySqlParameter("@year", MySqlDbType.Int32)
{
Value = dgv.Rows[e.RowIndex].Cells["Chosen Year"].Value
},
});
if (delete_result > 0)
{
Common.ShowInfo("Done", "Delete successfully!");
choosing_clear_button.PerformClick();
choosing_query_button.PerformClick();
}
}
}
}
}
private void CourseAverageQueryDialog_Load(object sender, EventArgs e)
{
DataTable course_dt = SQL_Help.ExecuteDataTable("select distinct id,name from course_info;", connection);
for (int i = 0; i < course_dt.Rows.Count; i++)
{
course_comboBox.Items.Add(string.Format("{0}({1})", course_dt.Rows[i][0], course_dt.Rows[i][1]));
}
course_comboBox.Text = course_comboBox.Items[0].ToString();
}
public void QueryForLogin()
{
using (MySqlConnection connection = SQL_Help.getConnection(Common.UserType.STUDENT))
{
connection.Open();
LoginResult(SQL_Help.ExecuteDataTable("select authority from user_data where username = @name and password = SHA1(@pwd)",
connection, new MySqlParameter[] { new MySqlParameter("@name", username.Text), new MySqlParameter("@pwd", password.Text) }));
connection.Close();
}
}
private void teacher_apply_button_Click(object sender, EventArgs e)
{
if (teacher_new_id_textBox.Text.Length == 0 || teacher_new_name_textBox.Text.Length == 0)
{
Common.ShowError("Format error!", "All the field should not be null! Please check again!");
return;
}
if (!Regex.IsMatch(teacher_new_id_textBox.Text, @"^\d{5}$"))
{
Common.ShowError("Format checking error!", "ID format error! The length of teacher ID should be 5!");
return;
}
if (teacher_new_name_textBox.TextLength == 0)
{
Common.ShowError("Format checking error!", "Name format error! Teacher name cannot be empty!");
return;
}
MySqlParameter teacher_id_parameter = new MySqlParameter("@id", MySqlDbType.VarChar)
{
Value = teacher_current_id_textBox.Text
};
MySqlParameter teacher_new_id_parameter = new MySqlParameter("@new_id", MySqlDbType.VarChar)
{
Value = teacher_new_id_textBox.Text
};
int id_modify_result = SQL_Help.ExecuteNonQuery("update teacher_info set id=@new_id,name=@new_name where id=@id;", connection, new MySqlParameter[]
{
teacher_id_parameter,
teacher_new_id_parameter,
new MySqlParameter("@new_name", MySqlDbType.VarChar)
{
Value = teacher_new_name_textBox.Text
}
});
if (id_modify_result < 0)
{
return;
}
if (teacher_new_id_textBox.Text == teacher_current_id_textBox.Text)
{
Common.ShowInfo("Done!", "Modify successfully!");
teacher_query_button.PerformClick();
return;
}
int other_modify_result = SQL_Help.ExecuteNonQuery("update user_data set username=@new_id where username=@id;update course_info set teacher_id=@new_id where teacher_id=@id;", connection,
new MySqlParameter[] { teacher_new_id_parameter, teacher_id_parameter });
if (other_modify_result >= 0)
{
Common.ShowInfo("Done!", "Modify successfully!");
teacher_current_id_textBox.Text = teacher_new_id_textBox.Text;
teacher_query_button.PerformClick();
}
}
private void queryStudentInformationToolStripMenuItem_Click(object sender, EventArgs e)
{
ClearDGV();
string condition = "";
string basic_query_command = "select id as 'Student ID',name as 'Student Name',sex as 'Sex',entrance_age as 'Entrance Age',entrance_year as 'Entrance Year',class as 'Class' from student_info";
if (role == Common.UserType.STUDENT)
{
info_dt = SQL_Help.ExecuteDataTable(basic_query_command + " where id=@id;", connection, new MySqlParameter[]
{
new MySqlParameter("@id", MySqlDbType.VarChar)
{
Value = current_user_name
}
});
}
else
{
condition = Common.ShowInput("Please enter student ID or name: ", "Query for student info", "Empty for querying all student.");
if (condition == null)
{
return;
}
if (condition.Length == 0)
{
info_dt = SQL_Help.ExecuteDataTable(basic_query_command + ";", connection);
}
else
{
info_dt = SQL_Help.ExecuteDataTable(basic_query_command + " where(id=@condition or name=@condition);", connection, new MySqlParameter[]
{
new MySqlParameter("@condition", MySqlDbType.VarChar)
{
Value = condition
}
});
}
}
if (info_dt.Rows.Count == 0)
{
Common.ShowError("Empty query result", "No such student found with ID or name is " + condition + "!");
return;
}
dgv.DataSource = new BindingSource()
{
DataSource = info_dt
};
DataColumn show_role_column = new DataColumn("Role")
{
Caption = "Role", DefaultValue = "Student"
};
info_dt.Columns.Add(show_role_column);
}
private void ChooseClassDialog_Load(object sender, EventArgs e)
{
DataTable class_dt = SQL_Help.ExecuteDataTable("select distinct class from student_info;", connection);
for (int i = 0; i < class_dt.Rows.Count; i++)
{
comboBox1.Items.Add(class_dt.Rows[i][0]);
}
comboBox1.Items.Add("All Class");
comboBox1.Text = comboBox1.Items[0].ToString();
}
private void Add_Course_Load(object sender, EventArgs e)
{
DataTable teacher_dt = SQL_Help.ExecuteDataTable("select id,name from teacher_info;", connection);
for (int i = 0; i < teacher_dt.Rows.Count; i++)
{
string comboBox_item = teacher_dt.Rows[i][0] + "(" + teacher_dt.Rows[i][1] + ")";
teacher_id_ComboBox.Items.Add(comboBox_item);
teacher_list.Add(comboBox_item);
}
}
private void singleStudentToolStripMenuItem_Click(object sender, EventArgs e)
{
ClearDGV();
string condition = "";
string basic_query_command = "select student_id as 'Student ID',name as 'Student Name',round(sum(cs)/sum(credit),2) as 'Weighted average score' from " +
"(select distinct student_id,credit*score as cs,credit,course_id,chosen_year,course_choosing_info.teacher_id " +
"from course_choosing_info join course_info on (course_choosing_info.course_id=course_info.id) " +
"where course_choosing_info.score is not null) as a join student_info on (a.student_id=student_info.id) ";
string basic_query_command_end = " group by student_id;";
if (role == Common.UserType.STUDENT)
{
info_dt = SQL_Help.ExecuteDataTable(basic_query_command + " where student_id=@id" + basic_query_command_end, connection, new MySqlParameter[]
{
new MySqlParameter("@id", MySqlDbType.VarChar)
{
Value = current_user_name
}
});
}
else
{
condition = Common.ShowInput("Please enter student ID or name: ", "Query for student info", "Empty for querying all student.");
if (condition == null)
{
return;
}
if (condition.Length == 0)
{
info_dt = SQL_Help.ExecuteDataTable(basic_query_command + basic_query_command_end, connection);
}
else
{
info_dt = SQL_Help.ExecuteDataTable(basic_query_command + " where(student_id=@condition or name=@condition)" + basic_query_command_end, connection, new MySqlParameter[]
{
new MySqlParameter("@condition", MySqlDbType.VarChar)
{
Value = condition
}
});
}
}
if (info_dt.Rows.Count == 0)
{
Common.ShowError("Empty query result", "No such student found with ID or name is " + condition + "!");
return;
}
dgv.DataSource = new BindingSource()
{
DataSource = info_dt
};
}
//public static Dictionary<string, string> choosing_data_table_columns_name_mapping = new Dictionary<string, string>()
//{
// ["chosen_year"] = "Chosen Year",
// ["score"] = "Score",
// ["student_id"] = "Student",
// ["teacher_id"] = "Teacher",
// ["course_id"] = "Course",
// ["name"] = "Name",
// ["name2"] = "Course Name",
// ["name1"] = "Teacher Name",
// ["credit"] = "Credit"
//};
private void choosing_query_button_Click(object sender, EventArgs e)
{
choosing_query_button.Enabled = false;
if (choosing_student_id_textbox.Text.Length == 0)
{
Common.ShowError("Null value error", "Student ID can not be null!");
return;
}
student_choosing_dt = SQL_Help.ExecuteDataTable(
"select distinct course_choosing_info.course_id as 'Course ID',course_info.name as 'Course Name',course_choosing_info.student_id as 'Student ID',student_info.name as 'Student Name',course_choosing_info.teacher_id as 'Teacher ID',teacher_info.name as 'Teacher Name',course_choosing_info.chosen_year as 'Chosen Year',course_choosing_info.score as 'Score',course_info.credit as 'Credit' " +
"from course_choosing_info join course_info join student_info join teacher_info " +
"on (course_choosing_info.course_id=course_info.id and course_choosing_info.teacher_id=teacher_info.id and course_choosing_info.student_id=student_info.id) " +
"where course_choosing_info.student_id = @sid;", connection, new MySqlParameter[] { new MySqlParameter("@sid", MySqlDbType.VarChar)
{
Value = choosing_student_id_textbox.Text
} });
//student_choosing_dt.Columns["chosen_year"].SetOrdinal(4);
//student_choosing_dt.Columns["score"].SetOrdinal(3);
if (student_choosing_dt.Rows.Count == 0)
{
Common.ShowError("Empty query result error", "No records found with student_id = " + choosing_student_id_textbox.Text + "!");
choosing_clear_button.PerformClick();
return;
}
dgv.DataSource = new BindingSource()
{
DataSource = student_choosing_dt
};
//for (int i = 0; i < student_choosing_dt.Columns.Count; i++)
//{
// dgv.Columns[i].HeaderText = choosing_data_table_columns_name_mapping[student_choosing_dt.Columns[i].Caption];
//}
//dgv.Columns["score"].DisplayIndex = dgv.Columns.Count - 1;
//dgv.Columns["chosen_year"].DisplayIndex = dgv.Columns.Count - 1;
DataGridViewButtonColumn modify_btn = new DataGridViewButtonColumn();
modify_btn.HeaderText = "Modify";
modify_btn.Name = "modify";
modify_btn.DefaultCellStyle.NullValue = "Modify";
dgv.Columns.Add(modify_btn);
dgv.Columns["modify"].DisplayIndex = 0;
DataGridViewButtonColumn delete_btn = new DataGridViewButtonColumn();
delete_btn.HeaderText = "Delete";
delete_btn.Name = "delete";
delete_btn.DefaultCellStyle.NullValue = "Delete";
dgv.Columns.Add(delete_btn);
dgv.Columns["delete"].DisplayIndex = 1;
}
private void studentsInSameClassToolStripMenuItem_Click(object sender, EventArgs e)
{
ClearDGV();
string basic_query_command = "select class as 'Class',round(avg(was),2) as \"Class Weighted Average Score\" " +
"from (select student_id as 'Student ID',name as 'Student Name',round(sum(cs)/sum(credit),2) as was,class " +
"from (select distinct student_id,credit*score as cs,credit,course_id,chosen_year,course_choosing_info.teacher_id " +
"from course_choosing_info join course_info on (course_choosing_info.course_id=course_info.id) " +
"where course_choosing_info.score is not null) as a join student_info on (a.student_id=student_info.id) " +
"where student_id in (select student_info.id from student_info ";
string basic_query_command_end = " ) group by student_id) as b group by class;";
using (ChooseClassDialog ccd = new ChooseClassDialog()
{
connection = connection
})
{
if (ccd.ShowDialog() == DialogResult.OK)
{
if (ccd.result != "All Class")
{
info_dt = SQL_Help.ExecuteDataTable(basic_query_command + "where class=@class" + basic_query_command_end, connection, new MySqlParameter[]
{
new MySqlParameter("@class", MySqlDbType.VarChar)
{
Value = ccd.result
}
});
}
else
{
info_dt = SQL_Help.ExecuteDataTable(basic_query_command + basic_query_command_end, connection);
}
}
else
{
return;
}
}
if (info_dt.Rows.Count == 0)
{
Common.ShowError("Empty query result", "Class not found or this class has no score information can be queried!");
return;
}
dgv.DataSource = new BindingSource()
{
DataSource = info_dt
};
}
private void student_delete_button_Click(object sender, EventArgs e)
{
if (Common.ShowChoice("Delete Confirm", string.Format("Are you sure to delete the student ({0},{1})", student_current_id_textBox.Text, student_current_name_textBox.Text) + "?\nAll information about the student in the database will be deleted", MessageBoxIcon.Warning) == DialogResult.Yes)
{
int delete_result = SQL_Help.ExecuteNonQuery("delete from student_info where id=@id;delete from user_data where username=@id;", connection, new MySqlParameter[]
{ new MySqlParameter("@id", MySqlDbType.VarChar)
{
Value = student_current_id_textBox.Text
} });
if (delete_result > 0)
{
Common.ShowInfo("Done", "Delete successfully!");
student_clear_button.PerformClick();
}
}
}
private void add_process()
{
int add_result;
int year_lower_bound = Convert.ToInt32(grade_limit) - 1;
int year_upper_bound = (cancel_limit == "") ? int.MaxValue : Convert.ToInt32(cancel_limit);
for (int i = 0; i < dgv.Rows.Count; i++)
{
DataTable entry_year_dt = SQL_Help.ExecuteDataTable("select entrance_year from student_info where id=@id;", connection, new MySqlParameter[] { new MySqlParameter("@id", MySqlDbType.VarChar)
{
Value = students[i].ToString().Substring(0, 10)
} });
int entry_year = Convert.ToInt32(entry_year_dt.Rows[0][0]);
bool lower_bound_satisfied = Convert.ToInt32(year) - entry_year >= year_lower_bound;
bool upper_bound_satisfied = Convert.ToInt32(year) <= year_upper_bound;
if (!lower_bound_satisfied || !upper_bound_satisfied)
{
string error_msg = "Students: " + students[i] + ":";
error_msg += (lower_bound_satisfied ? "" : "\nThis course is only available for students whose grade is larger than " + grade_limit + ".")
+ (upper_bound_satisfied ? "" : "\nThis course is only available before " + cancel_limit + ".");
Common.ShowError("Time error!", error_msg);
add_result = -1;
}
else
{
add_result = SQL_Help.ExecuteNonQuery("insert into course_choosing_info (student_id,teacher_id,course_id,chosen_year) values(@1,@2,@3,@4);", connection, new MySqlParameter[]
{ new MySqlParameter("@1", MySqlDbType.VarChar)
{
Value = dgv.Rows[i].Cells["student"].Value.ToString().Substring(0, 10)
},
new MySqlParameter("@2", MySqlDbType.VarChar)
{
Value = dgv.Rows[i].Cells["teacher"].Value.ToString().Substring(0, 5)
},
new MySqlParameter("@3", MySqlDbType.VarChar)
{
Value = dgv.Rows[i].Cells["course"].Value.ToString().Substring(0, 7)
},
new MySqlParameter("@4", MySqlDbType.Int32)
{
Value = dgv.Rows[i].Cells["year"].Value.ToString()
} });
}
SetAddProgress(i, add_result > 0 ? "Done" : "Failed");
}
}
private void SubmitScore_Load(object sender, EventArgs e)
{
DataTable course_dt = SQL_Help.ExecuteDataTable("select id,name from course_info where teacher_id=@teacher_id;", connection, new MySqlParameter[]
{
new MySqlParameter("@teacher_id", MySqlDbType.VarChar)
{
Value = current_teacher_id
}
});
for (int i = 0; i < course_dt.Rows.Count; i++)
{
string course_item = string.Format("{0}({1})", course_dt.Rows[i][0], course_dt.Rows[i][1]);
course_list_items.Add(course_item);
course_comboBox.Items.Add(course_item);
}
}
private void submitScoreToolStripMenuItem_Click(object sender, EventArgs e)
{
DataTable teacher_dt = SQL_Help.ExecuteDataTable("select id,name from teacher_info where id=@id;", connection, new MySqlParameter[]
{
new MySqlParameter("@id", MySqlDbType.VarChar)
{
Value = user_text_menuitem.Text.Substring(user_text_menuitem.Text.Length - 5, 5)
}
});
using (SubmitScore ss = new SubmitScore(teacher_dt.Rows[0][0].ToString(), teacher_dt.Rows[0][1].ToString())
{
connection = connection
})
{
ss.ShowDialog();
}
}
private void apply_Click(object sender, EventArgs e)
{
DataTable dt = SQL_Help.ExecuteDataTable("select authority from user_data where username = @name and password = SHA1(@pwd)",
connection, new MySqlParameter[] { new MySqlParameter("@name", username), new MySqlParameter("@pwd", old_pwd.Text) });
if (dt.Rows.Count == 0)
{
Common.ShowError("Password Changing Error", "Old Password is not correct!");
}
else
{
SQL_Help.ExecuteNonQuery("update user_data set password = SHA1(@pwd) where username = @name",
connection, new MySqlParameter[] { new MySqlParameter("@name", username), new MySqlParameter("@pwd", new_pwd.Text) });
Common.ShowInfo("Done!", "Password changing successful!");
DialogResult = DialogResult.OK;
Close();
}
}
private void course_id_ComboBox_TextChanged(object sender, EventArgs e)
{
teacher_id_comboBox.Items.Clear();
int index = course_combobox_items.IndexOf(course_id_ComboBox.Text);
if (index != -1)
{
DataTable teacher_dt = SQL_Help.ExecuteDataTable("select id,name from teacher_info where id in (select teacher_id from course_info where id=@id);", connection, new MySqlParameter[] { new MySqlParameter("@id", MySqlDbType.VarChar)
{
Value = course_id_ComboBox.Text.Substring(0, 7)
} });
for (int i = 0; i < teacher_dt.Rows.Count; i++)
{
teacher_id_comboBox.Items.Add(teacher_dt.Rows[i][0].ToString() + "(" + teacher_dt.Rows[i][1].ToString() + ")");
}
teacher_id_comboBox.Text = teacher_id_comboBox.Items[0].ToString();
//teacher_id_textBox.Text = course_dt.Rows[index][2] + "("+ teacher_dt.Rows[0][0].ToString()+")";
}
}
private void course_query_button_Click(object sender, EventArgs e)
{
if (course_current_id_textBox.Text.Length == 0)
{
Common.ShowError("Null value error!", "ID can not be empty!");
return;
}
course_current_id_textBox.ReadOnly = true;
DataTable current_info_dt = SQL_Help.ExecuteDataTable("select * from course_info where id=@id;", connection, new MySqlParameter[] { new MySqlParameter("@id", MySqlDbType.VarChar)
{
Value = course_current_id_textBox.Text
} });
if (current_info_dt.Rows.Count == 0)
{
Common.ShowError("Empty query result!", "No corresponding course found with id = " + course_current_id_textBox.Text + ".\nPlease check again.");
course_current_id_textBox.ReadOnly = false;
}
else
{
course_new_name_textBox.Text = course_current_name_textBox.Text = current_info_dt.Rows[0][1].ToString();
//course_new_teacher_comboBox.Text = course_current_teacher_comboBox.Text = current_info_dt.Rows[0][2].ToString() + string.Format("({0})", course_teacher_mapping[current_info_dt.Rows[0][2].ToString()]);
for (int i = 0; i < current_info_dt.Rows.Count; i++)
{
course_current_teacher_comboBox.Items.Add(current_info_dt.Rows[i][2].ToString() + string.Format("({0})", course_teacher_mapping[current_info_dt.Rows[i][2].ToString()]));
}
course_new_teacher_comboBox.Text = course_current_teacher_comboBox.Text = course_current_teacher_comboBox.Items[0].ToString();
course_current_teacher_comboBox.Enabled = true;
course_new_credit_textBox.Text = course_current_credit_textBox.Text = current_info_dt.Rows[0][3].ToString();
course_new_year_textBox.Text = course_current_year_textBox.Text = current_info_dt.Rows[0][5].ToString();
canceled_year_textbox_has_real_text = course_current_year_textBox.Text.Length != 0;
if (canceled_year_textbox_has_real_text)
{
course_new_year_textBox.ForeColor = Color.Black;
}
course_new_grade_comboBox.Text = course_current_grade_textBox.Text = current_info_dt.Rows[0][4].ToString();
course_new_id_textBox.Text = course_current_id_textBox.Text;
course_new_info_groupBox.Enabled = true;
course_apply_button.Enabled = true;
course_delete_button.Enabled = true;
course_delete_record_button.Enabled = true;
}
}
private void singleCourseToolStripMenuItem_Click(object sender, EventArgs e)
{
using (CourseAverageQueryDialog caqd = new CourseAverageQueryDialog()
{
connection = connection
})
{
if (caqd.ShowDialog() == DialogResult.OK)
{
info_dt = SQL_Help.ExecuteDataTable("select course_id as 'Course ID',course_info.name as 'Course Name',course_choosing_info.teacher_id as 'Teacher ID',teacher_info.name as 'Teacher Name',chosen_year as 'Chosen year',round(avg(score),2) as 'Course Average Score' " +
"from course_choosing_info join teacher_info join course_info on (course_choosing_info.course_id=course_info.id and course_choosing_info.teacher_id=teacher_info.id) " +
"where course_id=@cid and course_choosing_info.teacher_id=@tid and chosen_year=@year and score is not null;", connection, new MySqlParameter[]
{
new MySqlParameter("@cid", MySqlDbType.VarChar)
{
Value = caqd.course.Substring(0, 7)
},
new MySqlParameter("@tid", MySqlDbType.VarChar)
{
Value = caqd.teacher.Substring(0, 5)
},
new MySqlParameter("@year", MySqlDbType.Int32)
{
Value = caqd.year
}
});
}
else
{
return;
}
}
if (info_dt.Rows.Count == 0)
{
Common.ShowError("Empty query result", "Course not found or this course has no score information can be queried!");
return;
}
dgv.DataSource = new BindingSource()
{
DataSource = info_dt
};
}
private void teacher_delete_button_Click(object sender, EventArgs e)
{
string delete_notice = ((teacher_courses_textBox.TextLength == 0) ? ""
: "This teacher still have some course! \nIf continue, all the information about the courses that this teacher teach will be deleted!") +
string.Format("Are you sure to delete teacher ({0},{1})?", teacher_current_id_textBox.Text, teacher_current_name_textBox.Text);
if (Common.ShowChoice("Delete Confirm", delete_notice, MessageBoxIcon.Warning) == DialogResult.Yes)
{
MySqlParameter teacher_id_parameter = new MySqlParameter("@id", MySqlDbType.VarChar)
{
Value = teacher_current_id_textBox.Text
};
if (SQL_Help.ExecuteNonQuery("delete from teacher_info where id=@id;delete from course_info where teacher_id=@id;delete from user_data where username=@id;",
connection, new MySqlParameter[] { teacher_id_parameter }) > 0)
{
Common.ShowInfo("Done", "Delete successfully!");
teacher_clear_button.PerformClick();
}
}
}
