来访人员登记系统（七）签到签退模块
签到
签到部分首先让管理员确认待签到用户信息，然后查询该用户是否已签到，如果未签到则记录当前时间作为签到时间，当前操作用户作为签到管理员。
private void button_signin_Click(object sender, EventArgs e)
{
string value = dataGridView4.CurrentRow.Cells[0].Value.ToString();
int number = Convert.ToInt32(value);
try
{
string cmd = "select name, company, idcard, phone, staff, ETA, date, relevants, intention from registration where " +
"number =" + number + ";";
MySqlCommand SQLCmd = new MySqlCommand(cmd, DBconn);
MySqlDataReader dr = SQLCmd.ExecuteReader();
dr.Read();
string name = dr.GetString(0);
string company = dr.GetString(1);
string idcard = dr.GetString(2);
string phone = dr.GetString(3);
string staff = dr.GetString(4);
string ETA = dr.GetString(5);
string date = dr.GetString(6).Split(' ')[0];
string relevants = dr.GetString(7);
string intention = dr.GetString(8);
dr.Close();
if (MessageBox.Show(Form_Login.login_user_name +
"，请确认签到人员信息\n" +
"姓名：" + name + "\n" +
"单位：" + company + "\n" +
"身份证号：" + idcard + "\n" +
"电话：" + phone + "\n" +
"陪同人员：" + staff + "\n" +
"预约时间：" + ETA + "\n" +
"预约日期：" + date + "\n" +
"涉及设备/系统：" + relevants + "\n" +
"来访目的：" + intention, "提示", MessageBoxButtons.OKCancel) == DialogResult.OK)
{
cmd = "select signindatetime from registration where number=" + number + ";";
SQLCmd = new MySqlCommand(cmd, DBconn);
dr = SQLCmd.ExecuteReader();
dr.Read();
// 查看是否已签到
if (dr[0] == null || dr[0].ToString() == "")
{
dr.Close();
// 签到
cmd = "update registration set signindatetime='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") +
"', signinstaff='" + Form_Login.login_user_name + "' where number=" + number + ";";
SQLCmd = new MySqlCommand(cmd, DBconn);
SQLCmd.ExecuteNonQuery();
MessageBox.Show("签到成功", "提示", MessageBoxButtons.OK);
button_sign_reset_Click(sender, e);
}
else
{
dr.Close();
MessageBox.Show("已有签到记录，无法重复签到", "提示", MessageBoxButtons.OK);
}
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK);
}
}
签退
签退部分首先要查看该用户是否签到，如果已经签到则让管理员确认信息，然后还需要确认该用户是否已经签退，签退成功后将此记录从预约表中删除，并添加到日志表。
private void button_signout_Click(object sender, EventArgs e)
{
string value = dataGridView4.CurrentRow.Cells[0].Value.ToString();
int number = Convert.ToInt32(value);
try
{
string cmd = "select signindatetime from registration where number=" + number + ";";
MySqlCommand SQLCmd = new MySqlCommand(cmd, DBconn);
MySqlDataReader dr = SQLCmd.ExecuteReader();
dr.Read();
// 查看是否签到
if (dr[0] != null && dr[0].ToString() != "")
{
dr.Close();
cmd = "select name, company, idcard, phone, staff, signindatetime, relevants, intention from " +
"registration where number=" + number + ";";
SQLCmd = new MySqlCommand(cmd, DBconn);
dr = SQLCmd.ExecuteReader();
dr.Read();
string name = dr.GetString(0);
string company = dr.GetString(1);
string idcard = dr.GetString(2);
string phone = dr.GetString(3);
string staff = dr.GetString(4);
string signindatetime = dr.GetString(5);
string relevants = dr.GetString(6);
string intention = dr.GetString(7);
dr.Close();
if (MessageBox.Show(Form_Login.login_user_name +
"，请确认签退人员信息\n" +
"姓名：" + name + "\n" +
"单位：" + company + "\n" +
"身份证号：" + idcard + "\n" +
"电话：" + phone + "\n" +
"陪同人员：" + staff + "\n" +
"签到时间：" + signindatetime + "\n" +
"涉及设备/系统：" + relevants + "\n" +
"来访目的：" + intention, "提示", MessageBoxButtons.OKCancel) == DialogResult.OK)
{
cmd = "select signoutdatetime from registration where number=" + number + ";";
SQLCmd = new MySqlCommand(cmd, DBconn);
dr = SQLCmd.ExecuteReader();
dr.Read();
// 查看是否已签退
if (dr[0] == null || dr[0].ToString() == "")
{
dr.Close();
// 签退
cmd = "update registration set signoutdatetime='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") +
"', signoutstaff='" + Form_Login.login_user_name + "' where number=" + number + ";";
SQLCmd = new MySqlCommand(cmd, DBconn);
SQLCmd.ExecuteNonQuery();
// 记录来访日志
cmd = "select name, company, idcard, phone, staff, signinstaff, signindatetime, signoutstaff, " +
"signoutdatetime, relevants, intention from registration where number=" + number + ";";
SQLCmd = new MySqlCommand(cmd, DBconn);
dr = SQLCmd.ExecuteReader();
dr.Read();
name = dr.GetString(0);
company = dr.GetString(1);
idcard = dr.GetString(2);
phone = dr.GetString(3);
staff = dr.GetString(4);
string signinstaff = dr.GetString(5);
signindatetime = dr.GetString(6);
string date = signindatetime.Split(' ')[0];
string signoutstaff = dr.GetString(7);
string signoutdatetime = dr.GetString(8);
relevants = dr.GetString(9);
intention = dr.GetString(10);
dr.Close();
cmd = "insert into log values('" + name + "','" + company + "','" + idcard + "','" + phone +
"','" + staff + "','" + date + "','" + signinstaff + "','" + signindatetime + "','" +
signoutstaff + "','" + signoutdatetime + "','" + relevants + "','" + intention + "');";
SQLCmd = new MySqlCommand(cmd, DBconn);
SQLCmd.ExecuteNonQuery();
// 删除预约
cmd = "delete from registration where number=" + number + ";";
SQLCmd = new MySqlCommand(cmd, DBconn);
SQLCmd.ExecuteNonQuery();
MessageBox.Show("签退成功，来访信息已记录到日志", "提示", MessageBoxButtons.OK);
button_sign_reset_Click(sender, e);
}
else
{
dr.Close();
MessageBox.Show("已有签退记录，无法重复签退", "提示", MessageBoxButtons.OK);
}
}
}
else
{
dr.Close();
MessageBox.Show("该人员尚未签到，无法签退", "提示", MessageBoxButtons.OK);
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK);
}
}
总结
可以在主窗口内定义静态变量以便进行全局调用，如本例中在登录窗口创建了静态变量login_user_name，然后在签到签退窗口获取此变量的值加入到数据库中；
datareader的GetString()方法可以获取读取到的记录的多个字段，根据数据库中的顺序从0向后依次排列，但如果记录的某个字段为空值，此方法将报错。
浙公网安备 33010602011771号