private void button_CX_Click(object sender, EventArgs e)//查询
{
getkahao();
if (ADMKaHao == null)
{
//没有识别到卡片将初始化
string nul = " ";
label8_kahao.Text = "未识别到卡片";
label9_name.Text = nul;
label10_gonghao.Text = nul;
return;
}
string sql = "select * from kaika_tb where kaika_tb.cardID = '" + ADMKaHao + "'";//查询语句
List <LoginResult> r = MysqlHelp.GetKaiKas(sql);
if (r != null && r.Count > 0)
{
ADMXinMing = r[0].Name; //获取学生名字
ADMGongHao = r[0].WorkerNO; //获取学生学号
}
if (ADMXinMing == null)
{
MessageBox.Show("该卡片还没开通！");
return;
}
//显示信息
label8_kahao.Text = ADMKaHao;
label9_name.Text = ADMXinMing;
label10_gonghao.Text = ADMGongHao;
}
//查询按钮点击后根据判断条件提取数据;
private void button1_Click(object sender, EventArgs e)
{
DateTime dStart = this.dateTimePicker_Start.Value.Date;
string startDate = dStart.ToString("yyyy-MM-dd HH:mm:ss"); //转成字符串
DateTime dEnd = new DateTime(this.dateTimePicker_End.Value.Year, this.dateTimePicker_End.Value.Month, this.dateTimePicker_End.Value.Day, 23, 59, 59);
string endDate = dEnd.ToString("yyyy-MM-dd HH:mm:ss"); //转成字符串
string MJXinMing = textBox_XM.Text.Trim().ToString();
string MJGongHao = textBox_GH.Text.Trim().ToString();
string StartTime = startDate.ToString();
string EndTime = endDate.ToString();
string sqlformat1 = @"select menjinjilu_tb.menjinjiluID AS 记录ID,
kaika_tb.cardID AS 卡号,
kaika_tb.KKName AS 姓名,
kaika_tb.KKGongHao AS 工号,
menjinjilu_tb.entryTime AS 进入时间,
menjinjilu_tb.departureTime AS 离开时间,
menjinjilu_tb.YX AS 当前是否在场
from kaika_tb,menjinjilu_tb
WHERE kaika_tb.cardID=menjinjilu_tb.cardID {0}{1}{2}{3}";
string sqlformat2 = " and menjinjilu_tb.entryTime >= '" + StartTime + "' and menjinjilu_tb.entryTime <= '" + EndTime + "'";
string sqlformat3 = (textBox_XM.ForeColor == Color.Black)?" and kaika_tb.KKName = '" + MJXinMing + "'":"";
string sqlformat4 = (textBox_GH.ForeColor == Color.Black) ?" and kaika_tb.KKGongHao = '" + MJGongHao + "'":"";
string sqlformat5 = (comboBox1.SelectedIndex == 0) ? " and menjinjilu_tb.YX = '1'":"";
string sql1 = String.Format(sqlformat1, sqlformat2, sqlformat3, sqlformat4, sqlformat5);
DataSet ds = MysqlHelp.getAllData(sql1);
dataGridView1.DataSource = ds.Tables[0];
}
//刷卡离开
private void button6_Click(object sender, EventArgs e)
{
getkahao();//请求防碰撞组合并获取卡号
//1.查询该卡是否存在;
MJKaHao = textBox_SHLK.Text;
string rdsql1 = "select * from kaika_tb where kaika_tb.cardID = '" + MJKaHao + "'";
List <LoginResult> r = MysqlHelp.GetKaiKas(rdsql1);
if (r != null & r.Count > 0)
{
//1.1提取查询到的其他列数据;
MJXinMing = r[0].Name;
MJGongHao = r[0].WorkerNO;
//2.查询该卡是否为进入状态;
string lkfomat = @"select * from menjinjilu_tb
where menjinjilu_tb.cardID = '{0}'
and menjinjilu_tb.departureTime is NULL";
string lksql = string.Format(lkfomat, MJKaHao);
List <MenjinjiluResult> m = MysqlHelp.GetMenJijilus(lksql);
if (m != null && m.Count > 0)
{
//2.1如果为进入状态将其修改为离开状态并填入离开时间;
string upfomat = @"update menjinjilu_tb
set menjinjilu_tb.departureTime = '{0}',menjinjilu_tb.YX = '0'
where menjinjilu_tb.cardID = '{1}'
and menjinjilu_tb.YX = '1'";
string upsql = string.Format(upfomat, MJTime, MJKaHao);
int b = MysqlHelp.ExcuteSql(upsql);
if (b > 0)
{
MessageBox.Show("成功离开");
}
textBox_SHLK.Text = "";
label5.Text = "开门成功！";
pictureBox1.Image = imageList1.Images[1];
label11_kahao.Text = MJKaHao.ToString();
label11_name.Text = MJXinMing.ToString();
label11_xuehao.Text = MJGongHao.ToString();
}
//2.2如不存在则返回提示框;
else
{
MessageBox.Show("此卡未进入,不能离开");
}
}
//1.2否则提示该卡未启用;
else
{
MessageBox.Show("该卡未启用");
}
}
//实时更新在场人数;
private void timer1_Tick(object sender, EventArgs e)
{
string count = "SELECT COUNT(*) as YX from menjinjilu_tb WHERE YX = '1'";
int c = MysqlHelp.GetRowCount(count);
if (c > 0)
{
label7.Text = c.ToString();
}
else
{
label7.Text = "0";
}
}
//刷卡进入
private void button3_Click(object sender, EventArgs e)
{
getkahao();//请求防碰撞组合并获取卡号
//1.查询卡号是否存在;
string dtlength = DateTime.Now.ToString("yyyyMMdd");
string rdsql = "select * from kaika_tb where kaika_tb.cardID = '" + MJKaHao + "'";
List <LoginResult> r = MysqlHelp.GetKaiKas(rdsql);
if (r != null && r.Count > 0)
{
//1.1提取查询到的数据其他列数据;
MJXinMing = r[0].Name;
MJGongHao = r[0].WorkerNO;
//2.查询该卡是否为进入状态;
string rdsqlformat = @"select * from menjinjilu_tb
where menjinjilu_tb.cardID = '{0}'
and menjinjilu_tb.departureTime is NULL";
string rdsql1 = string.Format(rdsqlformat, MJKaHao);
List <MenjinjiluResult> rdmdr1 = MysqlHelp.GetMenJijilus(rdsql1);
//2.1如果查询不到数据或者不在进入状态;
if (rdmdr1 == null || //这是查询不到数据
(rdmdr1 != null && rdmdr1.Count > 0 && !rdmdr1[0].YX)) //这是有效状态
{
//3查询当天是否有流水号信息记录;
string leftidsql = @"select distinct left(menjinjilu_tb.menjinjiluID,8) from menjinjilu_tb
WHERE to_days(menjinjilu_tb.menjinjiluID) = to_days(now())";
List <SingleRowResult> s = MysqlHelp.GetSingleRows(leftidsql);
if (s == null)
{
//3.1如果当天没有流水号就执行开门并创建门禁记录信息写入到数据库;
YX = "1";
string laterID = "00000001";
string intofomat = @"insert into menjinjilu_tb values ('{0}{1}','{2}','{3}',NULL,'{4}')";
string intosql = string.Format(intofomat, dtlength, laterID, MJKaHao, MJTime, YX);
int a = MysqlHelp.ExcuteSql(intosql);
if (a > 0)
{
MessageBox.Show("成功进入");
}
}
else
{
string strmaxid = "";
//3.2如果当天有流水号记录,则查询当天最大流水号信息记录;
string rightidsql = @"select MAX(right(menjinjilu_tb.menjinjiluID,8)) as maxmenjinjiluID from menjinjilu_tb
WHERE to_days(menjinjilu_tb.menjinjiluID) = to_days(now())";
List <SingleRowResult> s1 = MysqlHelp.GetSingleRows(rightidsql);
if (s1 != null && s1.Count > 0)
{
//2.4将流水号后8位进行自增长;
int maxID = 0;
maxID = int.Parse(s1[0].Value);
maxID++;
strmaxid = maxID.ToString().PadLeft(8, '0');
}
//3.3根据自增长流水号将新进入人员卡号和以及其他信息写入到数据库并执行开门;
YX = "1";
string intofomat1 = @"insert into menjinjilu_tb values ('{0}{1}','{2}','{3}',NULL,'{4}')";
string intosql1 = string.Format(intofomat1, dtlength, strmaxid, MJKaHao, MJTime, YX);
int a = MysqlHelp.ExcuteSql(intosql1);
if (a > 0)
{
MessageBox.Show("成功进入");
}
}
textBox_SHJR.Text = "";
label5.Text = "开门成功！";
pictureBox1.Image = imageList1.Images[1];
label11_kahao.Text = MJKaHao.ToString();
label11_name.Text = MJXinMing.ToString();
label11_xuehao.Text = MJGongHao.ToString();
}
//2.2否则提示此人已进入;
else
{
MessageBox.Show("此人已进入");
}
}
}
private void button3_kaika_Click(object sender, EventArgs e)//确认开卡按钮
{
string Str_name;
string Str_gonghao;
string Str_kahao;
Str_name = textBox1_name.Text.ToString();
Str_gonghao = textBox2_gonghao.Text.ToString();
Str_kahao = textBox3_kahao.Text.ToString();
if (!string.IsNullOrEmpty(Str_name) && !string.IsNullOrEmpty(Str_gonghao) && textBox3_kahao.ForeColor != Color.LightGray)
{
////********************************选择********************************
//wrbuffer[0] = 0x0b;
//wrbuffer[1] = 0x02;
//wrbuffer[2] = 0x43;
//wrbuffer[3] = 0x05;
//wrbuffer[4] = 0x93;
//wrbuffer[5] = cardid[0];
//wrbuffer[6] = cardid[1];
//wrbuffer[7] = cardid[2];
//wrbuffer[8] = cardid[3];
//wrbuffer[9] = bccCode(wrbuffer, 9);
//wrbuffer[10] = 0x03;
//sp.Write(wrbuffer, 0, 0x0b); //往串口写入命令
//sp.Read(rdbuffer, 0, 1);
//if (rdbuffer[0] == 0x07)
//{
// for (int i = 1; i < 0x07; i++)
// sp.Read(rdbuffer, i, 1);
//}
//else
//{
// sp.DiscardInBuffer();
// return;
//}
////********************************验证********************************
//wrbuffer[0] = 0x12;
//wrbuffer[1] = 0x02;
//wrbuffer[2] = 0x46;
//wrbuffer[3] = 0x0c;
//wrbuffer[4] = 0x60;
//wrbuffer[5] = cardid[0]; //卡号
//wrbuffer[6] = cardid[1];
//wrbuffer[7] = cardid[2];
//wrbuffer[8] = cardid[3];
//wrbuffer[9] = 0xff; //秘钥
//wrbuffer[10] = 0xff;
//wrbuffer[11] = 0xff;
//wrbuffer[12] = 0xff;
//wrbuffer[13] = 0xff;
//wrbuffer[14] = 0xff;
//wrbuffer[15] = 0x04; //块号
//wrbuffer[16] = bccCode(wrbuffer, 16);
//wrbuffer[17] = 0x03;
//sp.Write(wrbuffer, 0, 0x12); //往串口写入
//sp.Read(rdbuffer, 0, 1);
//if (rdbuffer[0] == 0x06)
//{
// for (int i = 1; i < 0x06; i++)
// sp.Read(rdbuffer, i, 1);
//}
//else
//{
// sp.DiscardInBuffer();
// return;
//}
////********************************写数据********************************
//name = Encoding.UTF8.GetBytes(textBox1_name.Text.Trim());
//int t = Encoding.UTF8.GetByteCount(textBox1_name.Text);
//xuehao = System.Text.Encoding.ASCII.GetBytes(textBox2_xuehao.Text.Trim());
//int g = Encoding.UTF8.GetByteCount(textBox2_xuehao.Text);
//MessageBox.Show(name.ToString());
//if (t != 9 || g != 10)
//{
// MessageBox.Show("请确认3位汉字姓名和5位完整学号");
// return;
//}
//wrbuffer[0] = 0x17;
//wrbuffer[1] = 0x02;
//wrbuffer[2] = 0x48;
//wrbuffer[3] = 0x11;
//wrbuffer[4] = 0x04;//块号
//for (int i = 0; i < 10; i++) //存放5位学号
// wrbuffer[i + 5] = xuehao[i];
//for (int i = 0; i < 9; i++) //存放9个字节的名字
// wrbuffer[i + 12] = name[i];
//wrbuffer[21] = bccCode(wrbuffer, 21);
//wrbuffer[22] = 0x03;
//sp.Write(wrbuffer, 0, 0x17); //往串口写入写命令数据
//System.Threading.Thread.Sleep(200);
//sp.Read(rdbuffer, 0, 1);
//if (rdbuffer[0] == 0x06)
//{
// for (int i = 1; i < 0x06; i++)
// sp.Read(rdbuffer, i, 1);
// sp.DiscardInBuffer();
// label15_zhuangt.Text = "开卡操作成功！ ";
// DateTime.Now.ToShortTimeString();
//}
//else
//{
// label15_zhuangt.Text = "开卡操作失败！ ";
// sp.DiscardInBuffer();
// return;
//}
/**********************【查询是否已经开卡】***************************/
string sql1 = "select * from kaika_tb where cardID='" + Str_kahao + "'";
List <LoginResult> rd = MysqlHelp.GetKaiKas(sql1);
if (rd != null && rd.Count > 0)
{
MessageBox.Show("该卡号已存在！请勿重复开卡！");
return;
}
/**********************【开卡信息存入数据库】***************************/
string sql = "insert into kaika_tb values ('" + Str_kahao + "','" + Str_name + "','" + Str_gonghao + "')";
int c = MysqlHelp.ExcuteSql(sql);
try
{
if (c > 0)
{
MessageBox.Show(" 开卡成功" + "\n卡号:" + Str_kahao +
"\n姓名:" + Str_name +
"\n工号:" + Str_gonghao, "提示",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
textBox1_name.Text = "";
textBox2_gonghao.Text = "";
textBox3_kahao.Text = "";
}
}
catch (Exception)
{
MessageBox.Show("添加失败，请重试");
}
}
else
{
MessageBox.Show("请正确填写信息");
}
}
private void button1_Click(object sender, EventArgs e)
{
//1.判断comboBox和textBox是否为空,如果都不为空则执行;
if (!string.IsNullOrEmpty(comboBox1.Text) && !string.IsNullOrEmpty(textBox_CX.Text))
{
//2.如果comboBox的文本是(卡号:)则执行;
if (comboBox1.Text == "卡号:")
{
this.KaHao = textBox_CX.Text;
string sql = "Select * from kaika_tb where kaika_tb.cardID ='" + KaHao + "'";
List <LoginResult> r = MysqlHelp.GetKaiKas(sql);
if (r != null && r.Count > 0)
{
XingMing = r[0].Name; //获取学生名字
GongHao = r[0].WorkerNO; //获取学生学号
this.DialogResult = DialogResult.OK;
}
else
{
MessageBox.Show("查无此信息");
}
}
//3.如果comboBox的文本是(姓名:)则执行;
else if (comboBox1.Text == "姓名:")
{
this.XingMing = textBox_CX.Text;
string sql = "select * from kaika_tb where kaika_tb.KKName = '" + XingMing + "'";
List <LoginResult> r = MysqlHelp.GetKaiKas(sql);
if (r != null && r.Count > 0)
{
KaHao = r[0].ID;
GongHao = r[0].WorkerNO;
this.DialogResult = DialogResult.OK;
}
else
{
MessageBox.Show("查无此信息");
}
}
//4.如果comboBox的文本是(工号:)则执行;
else if (comboBox1.Text == "工号:")
{
this.GongHao = textBox_CX.Text;
string sql = "select * from kaika_tb where kaika_tb.KKGongHao = '" + GongHao + "'";
List <LoginResult> r = MysqlHelp.GetKaiKas(sql);
if (r != null && r.Count > 0)
{
KaHao = r[0].ID;
XingMing = r[0].Name;
this.DialogResult = DialogResult.OK;
}
else
{
MessageBox.Show("查无此信息");
}
}
}
//1.1否则输出提示消息;
else
{
MessageBox.Show("请选择或输入正确信息");
}
}