private void textBox1_TextChanged(object sender, EventArgs ea)
{
if (txtBox_name.Text.EndsWith("*"))
{
mark++;
string name = txtBox_name.Text.Split('*')[0];
//通过学员姓名查询学员信息
MySqlConnection con = DBConnector.getcon();
con.Open();
string sql = "select * from student where stuname = '" + name + "'";
MySqlCommand comm = new MySqlCommand(sql, con);
MySqlDataReader reader = comm.ExecuteReader();
student stu = new student();
try
{
if (!reader.HasRows)
{
MessageBox.Show("无此学员信息！");
return;
}
while (reader.Read())
{
stu.id = reader.GetInt32(0);
stu.stuname = reader.GetString(1);
stu.englishname = reader.GetString(2);
stu.classtime = reader.GetInt32(3);
stu.signtime = reader.GetInt32(4);
stu.classcode = reader.GetString(5);
stu.singleclasstime = reader.GetInt32(6);
stu.remainderclasstime = reader.GetInt32(7);
}
}
catch
{
MessageBox.Show("查询学员信息失败！");
}
reader.Close();
//判断此学员是今天第几次打卡
bool isSign = false;
string sql2 = "select * from signlist where stuid = " + stu.id + " and signtime >= '" + DateTime.Now.ToString().Split(' ')[0] + "'";
MySqlCommand comm2 = new MySqlCommand(sql2, con);
MySqlDataReader reader2 = comm2.ExecuteReader();
try
{
//今天没有当前学员的打卡记录
if (!reader2.HasRows)
{
stu.signtime++;
isSign = false;
}
else
{
isSign = true;
}
}
catch (Exception e)
{
MessageBox.Show("查询当前学员打卡记录失败！---" + e.Message);
}
finally
{
reader2.Close();
}
//保存此学员打卡信息
if (!isSign)
{
stu.remainderclasstime = stu.remainderclasstime - stu.singleclasstime;
}
string sql1 = "update student set signtime = " + stu.signtime + ",remainderclasstime = " + stu.remainderclasstime + " where id = '" + stu.id + "'";
MySqlCommand comm1 = new MySqlCommand(sql1, con);
try
{
if (comm1.ExecuteNonQuery() > 0)
{
//
}
else
{
MessageBox.Show("保存学员打卡信息失败！");
}
}
catch
{
MessageBox.Show("保存学员打卡信息失败！");
}
string now = DateTime.Now.ToString();
string sql3 = "insert into signlist(signtime,stuid) values('" + now + "'," + stu.id + ")";
MySqlCommand comm3 = new MySqlCommand(sql3, con);
try
{
if (comm3.ExecuteNonQuery() > 0)
{
//
}
else
{
MessageBox.Show("保存打卡信息失败！");
}
}
catch
{
MessageBox.Show("保存打卡信息失败！");
}
finally {
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
if (con.State == ConnectionState.Open)
{
con.Close();
}
//显示打卡信息
signList.BeginUpdate();
ListViewItem lvi1 = new ListViewItem();
lvi1.Text = mark.ToString();
lvi1.SubItems.Add(stu.classcode);
lvi1.SubItems.Add(stu.stuname);
lvi1.SubItems.Add(stu.englishname);
lvi1.SubItems.Add(DateTime.Now.ToString().Split(' ')[1]);
lvi1.SubItems.Add(stu.signtime.ToString());
lvi1.SubItems.Add(stu.remainderclasstime.ToString());
lvi1.SubItems.Add(stu.classtime.ToString());
signList.Items.Add(lvi1);
signList.EndUpdate();
txtBox_name.Text = "";
}
}
private void btn_get_Click_1(object sender, EventArgs ea)
{
string beginDate = datetime_begin.Text;
string endDate = datetime_end.Text;
string classcode = txtbox_classcode.Text;
string stuname = txtbox_stuname.Text;
if (beginDate.Equals("") && endDate.Equals("") && classcode.Equals("") && stuname.Equals(""))
{
MessageBox.Show("请输入查询条件！");
return;
}
else
{
listview_record.Items.Clear();
listview_record.Refresh();
MySqlConnection con = DBConnector.getcon();
con.Open();
//首先查询出符合条件的学员
string sql1 = "select * from student where 1=1";
if (!stuname.Equals(""))
{
sql1 += " and stuname = '" + stuname + "'";
}
if (!classcode.Equals(""))
{
sql1 += " and classcode = '" + classcode + "'";
}
MySqlCommand comm1 = new MySqlCommand(sql1, con);
MySqlDataReader reader1 = comm1.ExecuteReader();
List <student> studentList = new List <student>();
try
{
if (reader1.HasRows)
{
while (reader1.Read())
{
student stu = new student();
stu.id = reader1.GetInt32(0);
stu.stuname = reader1.GetString(1);
stu.englishname = reader1.GetString(2);
stu.classtime = reader1.GetInt32(3);
stu.signtime = reader1.GetInt32(4);
stu.classcode = reader1.GetString(5);
stu.singleclasstime = reader1.GetInt32(6);
stu.remainderclasstime = reader1.GetInt32(7);
studentList.Add(stu);
}
}
}
catch (Exception e)
{
MessageBox.Show("查询学员信息失败：" + e.Message);
}
finally
{
reader1.Close();
}
//单独列出符合条件的学员ID
List <int> stuidList = new List <int>();
string idstring = "";
foreach (student stu in studentList)
{
stuidList.Add(stu.id);
idstring += "'" + stu.id.ToString() + "',";
}
if (!idstring.Equals(""))
{
idstring = idstring.Substring(0, idstring.Length - 1);
}
//防止起截止时间为空
if (beginDate.Equals(""))
{
beginDate = "1949-01-01";
}
if (endDate.Equals(""))
{
endDate = "9999-12-31";
}
string sql2 = "select * from signlist where signtime >= '" + beginDate + "' and signtime <= '" + endDate + "'";
MySqlCommand comm2 = new MySqlCommand(sql2, con);
MySqlDataReader reader2 = comm2.ExecuteReader();
List <signlist> Signlist = new List <signlist>();
while (reader2.Read())
{
signlist list = new signlist();
list.id = reader2.GetInt32(0);
list.signtime = reader2.GetString(1);
list.stuid = reader2.GetInt32(2);
Signlist.Add(list);
}
reader2.Close();
List <record> records = new List <record>();
//MySqlDataReader reader3;
foreach (signlist sign in Signlist)
{
record record = new record();
int stuid = sign.stuid;
foreach (student stu in studentList)
{
if (stu.id == stuid)
{
record.stuname = stu.stuname;
record.englishname = stu.englishname;
record.classcode = stu.classcode;
record.signdate = sign.signtime;
record.signtime = stu.signtime;
record.classtime = stu.classtime;
record.remainderclasstime = stu.remainderclasstime;
records.Add(record);
}
}
}
int mark = 0;
listview_record.BeginUpdate();
foreach (record record in records)
{
mark++;
ListViewItem lvi1 = new ListViewItem();
lvi1.Text = mark.ToString();
lvi1.SubItems.Add(record.classcode);
lvi1.SubItems.Add(record.stuname);
lvi1.SubItems.Add(record.englishname);
lvi1.SubItems.Add(record.signdate);
lvi1.SubItems.Add(record.signtime.ToString());
lvi1.SubItems.Add(record.classtime.ToString());
lvi1.SubItems.Add(record.remainderclasstime.ToString());
listview_record.Items.Add(lvi1);
}
listview_record.EndUpdate();
}
}