private void button4_Click(object sender, EventArgs e)
{
this.dataGridView2.DataSource = null;
this.dataGridView3.DataSource = null;
this.dataGridView2.Rows.Clear();
this.dataGridView3.Rows.Clear();
//DataTable nochoosestudent = SQLDBhelper.ExecuteDataTable(string.Format("select captainid,captainname from team where grade='{0}' and major='{1}' and account not in (select stuid from ischoose)", comboBox4.SelectedItem.ToString(), comboBox3.SelectedItem.ToString()));
DataTable nochoosestudent = SQLDBhelper.ExecuteDataTable(string.Format("select captainname,captainid from team where captainid in (select account from stupasswordtable where grade='{0}' and major='{1}') and captainid not in (select stuid from finish)", comboBox4.SelectedItem.ToString(), comboBox3.SelectedItem.ToString()));
if (nochoosestudent.Rows.Count != 0)
{
for (int i = 0; i < nochoosestudent.Rows.Count; i++)
{
dataGridView2.Rows.Add(nochoosestudent);
dataGridView2.Rows[i].Cells[0].Value = nochoosestudent.Rows[i]["captainid"].ToString();
dataGridView2.Rows[i].Cells[1].Value = nochoosestudent.Rows[i]["captainname"].ToString();
}
}
this.dataGridView3.DataSource = null;
DataTable tea = SQLDBhelper.ExecuteDataTable(string.Format("select name,id,number from teapasswordtable where grade='{0}' and major='{1}' and number>0", comboBox4.SelectedItem.ToString(), comboBox3.SelectedItem.ToString()));
if (tea.Rows.Count != 0)
{
for (int i = 0; i < tea.Rows.Count; i++)
{
dataGridView3.Rows.Add(tea);
dataGridView3.Rows[i].Cells[0].Value = tea.Rows[i]["id"].ToString();
dataGridView3.Rows[i].Cells[1].Value = tea.Rows[i]["name"].ToString();
dataGridView3.Rows[i].Cells[2].Value = tea.Rows[i]["number"].ToString();
}
}
}
//对应的年级专业的学生信息，使用函数以便后面使用
public void select_stu()
{
this.dataGridView1.DataSource = null;
this.dataGridView1.Rows.Clear();
//dataGridView1.DataSource = bll.select(comboBox1.Text.Trim(), comboBox2.Text.Trim());
DataTable selectmessage = SQLDBhelper.ExecuteDataTable(string.Format("select * from stupasswordtable where grade='{0}' and major='{1}' ", comboBox1.Text.Trim(), comboBox2.Text.Trim()));
if (selectmessage.Rows.Count != 0)
{
for (int i = 0; i < selectmessage.Rows.Count; i++)
{
dataGridView1.Rows.Add(dt);
dataGridView1.Rows[i].Cells[0].Value = selectmessage.Rows[i]["account"].ToString();
dataGridView1.Rows[i].Cells[1].Value = selectmessage.Rows[i]["password"].ToString();
dataGridView1.Rows[i].Cells[2].Value = selectmessage.Rows[i]["name"].ToString();
dataGridView1.Rows[i].Cells[3].Value = selectmessage.Rows[i]["sex"].ToString();
dataGridView1.Rows[i].Cells[4].Value = selectmessage.Rows[i]["id"].ToString();
dataGridView1.Rows[i].Cells[5].Value = selectmessage.Rows[i]["grade"].ToString();
dataGridView1.Rows[i].Cells[6].Value = selectmessage.Rows[i]["major"].ToString();
dataGridView1.Rows[i].Cells[7].Value = selectmessage.Rows[i]["class"].ToString();
dataGridView1.Rows[i].Cells[8].Value = selectmessage.Rows[i]["phone"].ToString();
dataGridView1.Rows[i].Cells[9].Value = selectmessage.Rows[i]["email"].ToString();
dataGridView1.Rows[i].Cells[10].Value = selectmessage.Rows[i]["introduce"].ToString();
dataGridView1.Rows[i].Cells[11].Value = selectmessage.Rows[i]["academy"].ToString();
dataGridView1.Rows[i].Cells[12].Value = selectmessage.Rows[i]["groupid"].ToString();
}
}
}
private void button4_Click(object sender, EventArgs e)
{
this.dataGridView1.DataSource = null;
this.dataGridView1.Rows.Clear();
DataTable stuidandteaid = SQLDBhelper.ExecuteDataTable(string.Format("select * from team where Teamnumber in (select account from stupasswordtable where grade='{0}' and major='{1}') and Teamnumber in (select stuid from finish)", comboBox4.SelectedItem.ToString(), comboBox3.SelectedItem.ToString()));
stuidandteaid.Columns.Add("teaname");
stuidandteaid.Columns.Add("teaid");
for (int i = 0; i < stuidandteaid.Rows.Count; i++)
{
SqlDataReader readteaname = SQLDBhelper.ExecuteReader(string.Format("select t.name ,a.teaid from system2.dbo.teapasswordtable t left join system2.dbo.finish a on a.teaid=t.account where a.stuid='{0}'", stuidandteaid.Rows[i]["captainid"].ToString()));
readteaname.Read();
stuidandteaid.Rows[i]["teaname"] = readteaname[0].ToString();
stuidandteaid.Rows[i]["teaid"] = readteaname[1].ToString();
dataGridView1.Rows.Add(stuidandteaid);
dataGridView1.Rows[i].Cells[0].Value = stuidandteaid.Rows[i]["captainname"].ToString();
dataGridView1.Rows[i].Cells[1].Value = stuidandteaid.Rows[i]["captainid"].ToString();
dataGridView1.Rows[i].Cells[2].Value = stuidandteaid.Rows[i]["memberonename"].ToString();
dataGridView1.Rows[i].Cells[3].Value = stuidandteaid.Rows[i]["memberoneid"].ToString();
dataGridView1.Rows[i].Cells[4].Value = stuidandteaid.Rows[i]["membertwoname"].ToString();
dataGridView1.Rows[i].Cells[5].Value = stuidandteaid.Rows[i]["membertwoid"].ToString();
dataGridView1.Rows[i].Cells[6].Value = stuidandteaid.Rows[i]["memberthreename"].ToString();
dataGridView1.Rows[i].Cells[7].Value = stuidandteaid.Rows[i]["memberthreeid"].ToString();
dataGridView1.Rows[i].Cells[8].Value = stuidandteaid.Rows[i]["teaname"].ToString();
dataGridView1.Rows[i].Cells[9].Value = stuidandteaid.Rows[i]["teaid"].ToString();
}
}
//Excel表中的列名和数据库中的列名一定要对应
private void insertToSql_stu(DataRow dr)
{
string account = dr["账号"].ToString();
string password = dr["密码"].ToString();
string name = dr["姓名"].ToString();
string sex = dr["性别"].ToString();
string id = dr["学号"].ToString();
string grade = dr["年级"].ToString();
string major = dr["专业"].ToString();
string Class = dr["班别"].ToString();
string phone = dr["电话号码"].ToString();
string email = dr["邮箱地址"].ToString();
string introduce = dr["自我介绍"].ToString();
string academy = dr["学院"].ToString();
string groupID = dr["团队编号"].ToString();
SQLDBhelper.ExecuteNonQuery(string.Format("insert into stupasswordtable values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}')", account, password, name, sex, id, grade, major, Class, phone, email, introduce, academy, groupID));
}
//button1的查询功能所对应的函数
public void selectmess()
{
this.dataGridView1.DataSource = null;
this.dataGridView1.Rows.Clear();
DataTable selectmessage = SQLDBhelper.ExecuteDataTable(string.Format("select * from ischoose where stuid in (select account from stupasswordtable where grade='{0}' and major='{1}') and stuid not in (select stuid from finish)", comboBox1.SelectedItem.ToString(), comboBox2.SelectedItem.ToString()));
if (selectmessage.Rows.Count != 0)
//dataGridView1.DataSource = selectmessage.DefaultView;
{
for (int i = 0; i < selectmessage.Rows.Count; i++)
{
dataGridView1.Rows.Add(selectmessage);
dataGridView1.Rows[i].Cells[0].Value = selectmessage.Rows[i]["stuid"].ToString();
dataGridView1.Rows[i].Cells[1].Value = selectmessage.Rows[i]["stuname"].ToString();
dataGridView1.Rows[i].Cells[2].Value = selectmessage.Rows[i]["teaid"].ToString();
dataGridView1.Rows[i].Cells[3].Value = selectmessage.Rows[i]["teaname"].ToString();
}
}
}
//Excel表中的列名和数据库中的列名一定要对应
private void insertToSql_tea(DataRow dr)
{
string Account = dr["账号"].ToString();
string Password = dr["密码"].ToString();
string Name = dr["姓名"].ToString();
string Sex = dr["性别"].ToString();
string ID = dr["工号"].ToString();
string Position = dr["职称"].ToString();
string Number = dr["名额"].ToString();
string groupnumber = dr["名额"].ToString();
string Phone = dr["电话号码"].ToString();
string Email = dr["邮箱地址"].ToString();
string Academy = dr["学院"].ToString();
string Research = dr["研究方向"].ToString();
string grade = dr["年级"].ToString();
string major = dr["专业"].ToString();
SQLDBhelper.ExecuteNonQuery(string.Format("insert into teapasswordtable values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}')", Account, Password, Name, Sex, ID, Position, Number, groupnumber, Phone, Email, Academy, Research, grade, major));
}
private void find_Click(object sender, EventArgs e)
{
this.dataGridView1.DataSource = null;
this.dataGridView1.Rows.Clear();
DataTable stu = SQLDBhelper.ExecuteDataTable(string.Format("select name ,id ,class,phone,email from stupasswordtable where grade='{0}' and major='{1}' and groupid='0'", grade.SelectedItem.ToString(), major.SelectedItem.ToString()));
if (stu.Rows.Count != 0)
{
for (int i = 0; i < stu.Rows.Count; i++)
{
dataGridView1.Rows.Add(stu);
dataGridView1.Rows[i].Cells[0].Value = stu.Rows[i]["name"].ToString();
dataGridView1.Rows[i].Cells[1].Value = stu.Rows[i]["id"].ToString();
dataGridView1.Rows[i].Cells[2].Value = stu.Rows[i]["class"].ToString();
dataGridView1.Rows[i].Cells[3].Value = stu.Rows[i]["phone"].ToString();
dataGridView1.Rows[i].Cells[4].Value = stu.Rows[i]["email"].ToString();
}
}
}
private void button2_Click(object sender, EventArgs e)
{
if (dataGridView1.Rows.Count == 0)
{
MessageBox.Show("列表中暂无学生互选信息");
return;
}
DialogResult ischoose = MessageBox.Show("是否对列表的师生进行匹配？", "自动匹配", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (ischoose == DialogResult.No)
{
return;
}
string[] stuid = new string[this.dataGridView1.Rows.Count];
string[] teaid = new string[this.dataGridView1.Rows.Count];
//int stucount = 0;
bool issame = false;
#region
for (int i = 0; i < this.dataGridView1.Rows.Count; i++)
{
//if (i == 0)
// stuid[stucount] = dataGridView1.Rows[i].Cells[0].Value.ToString();
for (int j = i; j >= 0; j--)
{
if (j == i)
{
continue;
}
if (dataGridView1.Rows[j].Cells[0].Value.ToString() == dataGridView1.Rows[i].Cells[0].Value.ToString())
{
issame = true;
}
}
if (issame)
{
issame = false;
}
else
{
stuid[stucount] = dataGridView1.Rows[i].Cells[0].Value.ToString();
stucount++;
}
}//读取已经被老师预选的学生学号 进入到stuid字符串数组中
#endregion//读取团队编号进入数组stuid[] stucount
#region
for (int i = 0; i < this.dataGridView1.Rows.Count; i++)
{
for (int j = i; j >= 0; j--)
{
if (j == i)
{
continue;
}
if (dataGridView1.Rows[j].Cells[2].Value.ToString() == dataGridView1.Rows[i].Cells[2].Value.ToString())
{
issame = true;
}
}
if (issame)
{
issame = false;
}
else
{
teaid[teacount] = dataGridView1.Rows[i].Cells[2].Value.ToString();
teacount++;
}
}//读取已完成互选的导师工号
#endregion//读取导师工号进入数组 teaid[] teacount
teacarrynumber = new int[teacount];//记录导师可带多少团队
for (int i = 0; i < teacount; i++)
{
SqlDataReader read = SQLDBhelper.ExecuteReader(string.Format("select number from teapasswordtable where account='{0}'", teaid[i]));
read.Read();
teacarrynumber[i] = (int)read[0];
}
used = new bool[teacount]; //标记是否已经进行访问
cnt = new int[teacount]; //记录已有多少匹配了
for (int i = 0; i < teacount; i++)
{
cnt[i] = 0;
used[i] = false;
}
line = new int[stucount, teacount];//声明一个二维数组 用于存储匹配的关系
//int k = 0;
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
int x = stuid.ToList().IndexOf(dataGridView1.Rows[i].Cells[0].Value.ToString());
int y = teaid.ToList().IndexOf(dataGridView1.Rows[i].Cells[2].Value.ToString());
line[x, y] = 1;
}//读取标记已经完成互选的关系图 进入line数组
//读取导师表中导师能带最大团队数
SqlDataReader readmaxnumber = SQLDBhelper.ExecuteReader("select max(groupnumber) groupnumber from teapasswordtable");
readmaxnumber.Read();
maxnum = Convert.ToInt32(readmaxnumber[0]);
match = new int[teacount, maxnum];
//button1.Text = max_match().ToString();
int number = max_match();
for (int i = 0; i < teacount; i++)
{
for (int j = 0; j < maxnum; j++)
{
if (match[i, j] != -1)
{
SqlConnection conn = new SqlConnection(SQLDBhelper.ConnectionString);
conn.Open();
SqlCommand insert = new SqlCommand(string.Format("insert into finish values('{0}','{1}','0')", stuid[match[i, j]], teaid[i]), conn);//导师id
insert.ExecuteNonQuery();
conn.Close();
}
}
}
MessageBox.Show(string.Format("匹配完成,当前学生团队数{0}，已完成匹配数{1}", stucount, number));
using (SqlConnection con = new SqlConnection(SQLDBhelper.ConnectionString))
{
con.Open();
for (int i = 0; i < teacount; i++)
{
SqlCommand command = new SqlCommand(string.Format("update teapasswordtable set number-={0} where account='{1}'", cnt[i], teaid[i]), con);
command.ExecuteNonQuery();
}
}
stucount = 0;
teacount = 0;
//调用函数，重新刷新dataGridView1，显示的是未分配到老师的学生团队
selectmess();
//this.dataGridView1.DataSource = null;
//DataTable selectmessage = SQLDBhelper.ExecuteDataTable(string.Format("select * from ischoose where stuid in (select account from stupasswordtable where grade='{0}' and major='{1}') and stuid not in (select stuid from finish)", comboBox1.SelectedItem.ToString(), comboBox2.SelectedItem.ToString()));
//if (selectmessage.Rows.Count != 0)
// dataGridView1.DataSource = selectmessage.DefaultView;
}
