//刷卡离开 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 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("此人已进入"); } } }