六、打卡，查询，统计系统功能实现

1、打卡

1）效果图

捕获.PNG

2）画面实现效果

可以打开串口，进行卡片信息写入并读取卡号实现打卡功能。

3）重要代码片段及详细描述

1、寻卡，将RFID的卡号读出来

string response = "";
            serialPort1.Write(ISO15693Card.COMMAND_WRITE_REG);
            Thread.Sleep(MILLISECOND_IN_SLEEP);
            if (serialPort1.BytesToRead > 0) response = serialPort1.ReadExisting();

            serialPort1.Write(ISO15693Card.COMMAND_SET_AGC);
            Thread.Sleep(MILLISECOND_IN_SLEEP);
            if (serialPort1.BytesToRead > 0) response = serialPort1.ReadExisting();

            serialPort1.Write(ISO15693Card.COMMAND_SET_RECV_MODE);
            Thread.Sleep(MILLISECOND_IN_SLEEP);
            if (serialPort1.BytesToRead > 0) response = serialPort1.ReadExisting();

            serialPort1.Write(ISO15693Card.COMMAND_INVEN_CARD);// 寻卡
            Thread.Sleep(MILLISECOND_IN_SLEEP);
            if (serialPort1.BytesToRead > 0) response = serialPort1.ReadExisting();

            List<ISO15693Card> cards = ISO15693CardHandler.InventoryCard(response);

2、读取卡上第00块的数据，获得职员编号

 String stuffId = ReadSingleBlock(ISO15693CardHandler.CovertEndian(cards[0].ID), "00"); 

3、向数据库中插入一条打卡记录，插入成功后显示打卡成功

 String connStr = ConfigurationManager.ConnectionStrings["Attendance system"].ConnectionString;
            SqlConnection sqlConn = new SqlConnection(connStr);
            try
            {
                // 连接数据库
                sqlConn.Open();

                // 构造命令
                String sqlStr = "insert into  record(employee_id, date, time, machine_id) VALUES(@Employee_id, @date,@time, @machine_id)";
                
                SqlCommand cmd = new SqlCommand(sqlStr, sqlConn);

                // SQL字符串参数赋值
                cmd.Parameters.Add(new SqlParameter("@employee_id", stuffId));
                cmd.Parameters.Add(new SqlParameter("@date", DateTime.Now.ToString("yyyy - MM - dd").ToString()));
                cmd.Parameters.Add(new SqlParameter("@time", DateTime.Now.ToString("HH: mm:ss").ToString()));
                cmd.Parameters.Add(new SqlParameter("@machine_id", '1'));


                // 将命令发送给数据库
                int res = cmd.ExecuteNonQuery();

                // 根据返回值判断是否插入成功
                if (res != 0)
                {
                    MessageBox.Show("打卡成功");
                }
                else
                {
                    MessageBox.Show("打卡失败");
                }
            }
            catch (Exception exp)
            {
                MessageBox.Show("访问数据库错误：" + exp.Message);
            }
            finally
            {
                sqlConn.Close();
            }
            

        }

2、查询

1）效果图

员工查询.gif

2）画面实现效果

员工登录系统后可查询自己的历史打卡记录；
管理员登录系统后可查询所有员工的打卡记录。

3）重要代码片段及详细描述

1、窗口加载时，显示当前时间，显示当前用户名

  private void MainForm_Load(object sender, EventArgs e)
        {
           
            // TODO: 这行代码将数据加载到表“AttendanceDataSet.record”中。您可以根据需要移动或删除它。
            this.recordTableAdapter.Fill(this.AttendanceDataSet.record);
            this.tssl_CurrentTime.Text = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            this.tssl_User.Text = UserInfo.userQx + UserInfo.userName;
        }

2、员工查询打卡信息

  private void tsmi_Search_Click(object sender, EventArgs e)
        {
            RecordForm recordForm = new RecordForm();
            recordForm.MdiParent = this;
            recordForm.WindowState = FormWindowState.Maximized;
            recordForm.Show();
        }

3、工作时长计算

String sqlStr = @"select t4.department, t4.name, t3.date, t3.start_time, t3.end_time, t3.diff from (
                                   select t1.employee_id, t1.date, t1.time as start_time, t2.time as end_time, datediff(n,t1.time,t2.time) as diff 
                                   from record t1
                                   inner join record t2 
                                   on t1.date = t2.date 
                                   and t1.employee_id=t2.employee_id 
                                   and t1.machine_id=1 
                                   and t2.machine_id=2
                                   and t1.date>=@begin
                                   and t1.date<=@end
                               ) t3, employee t4 where t3.employee_id=t4.id and t3.diff<540 and t4.department=@department";

3、统计

1）效果图

统计.PNG
详情.PNG

2）画面实现效果

管理员登录系统，可以查看统计部门考勤情况

3）重要代码片段及详细描述

1、查看部门考勤

        private void 统计部门考勤DToolStripMenuItem_Click(object sender, EventArgs e)
        {
            BmRecordForm bmrecordForm = new BmRecordForm();
            bmrecordForm.MdiParent = this;
            bmrecordForm.WindowState = FormWindowState.Maximized;
            bmrecordForm.Show();
        }

2、连接到数据库

 String connStr = ConfigurationManager.ConnectionStrings["Attendance"].ConnectionString;
            SqlConnection sqlConn = new SqlConnection(connStr);

            try
            {
                sqlConn.Open();
                {
                    String sqlStr = @"select t4.department, t4.name, t3.date, t3.start_time, t3.end_time, t3.diff from (
                                    select t1.employee_id, t1.date, t1.time as start_time, t2.time as end_time, datediff(n,t1.time,t2.time) as diff 
                                    from record t1
                                    inner join record t2 
                                    on t1.date = t2.date 
                                    and t1.employee_id=t2.employee_id 
                                    and t1.machine_id=1 
                                    and t2.machine_id=2
                                    and t1.date>=@begin
                                    and t1.date<=@end
                                ) t3, employee t4 where t3.employee_id=t4.id and t3.diff<540 and t4.department=@department";

 SqlCommand cmd = new SqlCommand(sqlStr, sqlConn);
                    cmd.Parameters.Add(new SqlParameter("@begin", this.begin.ToShortDateString()));
                    cmd.Parameters.Add(new SqlParameter("@end", this.end.ToShortDateString()));
                    cmd.Parameters.Add(new SqlParameter("@department", this.department));

                    SqlDataAdapter adp = new SqlDataAdapter();
                    adp.SelectCommand = cmd;

3、绑定数据表

                 DataSet ds = new DataSet();
                    // 自定义一个表(MyGoods)来标识数据库的GOODS表
                    adp.Fill(ds, "Attendance");

                    // 指定DataGridView的数据源为DataSet的MyGoods表
                    this.dataGridView1.DataSource = ds.Tables["Attendance"];
                }
            }
            catch (Exception exp)
            {
                MessageBox.Show("访问数据库错误：" + exp.Message);

            }
            finally
            {
                sqlConn.Close();
            }
        }