一、任务需求

管理员可以查询统计不同时间段内各个部门的迟到人数，并查询各部门迟到详情

二、准备（统计界面、详情界面）

统计界面

统计界面.jpg

详情界面

详情界面.jpg

三、统计界面主要代码

private void bt_Query_Click(object sender, EventArgs e)
        {
            // 连接字符串，注意与实际环境保持一致
            String connStr1 = ConfigurationManager.ConnectionStrings["KQ"].ConnectionString;
            SqlConnection sqlConn = new SqlConnection(connStr1);
            try
            {
                // 连接数据库
                sqlConn.Open();

                // 构造命令
                String sqlStr = @"select t4.department,count(*) as count from(
                                    select t3.*, t.name, t.department from (
                                        select t1.employee_id, t1.date, 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>=@start
                                        and t1.date<=@end
                                    ) t3,employee t where t3.employee_id=t.id
                                ) t4 where t4.diff<540 group by t4.department";

                SqlCommand cmd = new SqlCommand(sqlStr, sqlConn);
                cmd.Parameters.Add(new SqlParameter("@start", this.dtp_start.Value.ToShortDateString()));
                cmd.Parameters.Add(new SqlParameter("@end", this.dtp_stop.Value.ToShortDateString()));

                // 将该查询过程绑定到DataAdapter
                SqlDataAdapter adp = new SqlDataAdapter();
                adp.SelectCommand = cmd;

                // 将DataSet和DataAdapter绑定
                DataSet ds = new DataSet();

                // 自定义一个表来标识数据库的record表
                adp.Fill(ds, "tongjib");

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

四、详情界面主要代码

1. 从统计界面跳转到详情界面

 private void dgv_tongji_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            //在调试时看查看定位到哪个单元格
            if (e.RowIndex != -1 && e.ColumnIndex == 4)
            {
                //传参数
                string department = this.dgv_tongji["department", e.RowIndex].Value.ToString();
                DateTime start = this.dtp_start.Value;
                DateTime end = this.dtp_stop.Value;
                xs xsl = new xs(department,start,end);
                xsl.Show();
            }
        }

2. 详情界面主要代码

        //接收传来的参数
        private string Department = null;
        private DateTime Start = DateTime.Now;
        private DateTime End = DateTime.Now;
        public xs(string department,DateTime start,DateTime end)
        {
            this.Department = department;
            this.Start = start;
            this.End = end;
            InitializeComponent();
        }
//连接数据库查询结果
private void xs_Load(object sender, EventArgs e)
        {
            // 连接字符串，注意与实际环境保持一致
            String connStr = ConfigurationManager.ConnectionStrings["KQ"].ConnectionString;
            SqlConnection sqlConn = new SqlConnection(connStr);
            try
            {
                // 连接数据库
                sqlConn.Open();

                // 构造命令
                String sqlStr = @"select t3.*, t.name, t.department from (
                                    select t1.employee_id, t1.date, 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>=@start
                                    and t1.date<=@end
                                    ) t3,employee t where t3.employee_id=t.id and t3.diff<540 and t.department=@department";
                SqlCommand cmd = new SqlCommand(sqlStr, sqlConn);

                cmd.Parameters.Add(new SqlParameter("@start", this.Start.ToShortDateString()));
                cmd.Parameters.Add(new SqlParameter("@end", this.End.ToShortDateString()));
                cmd.Parameters.Add(new SqlParameter("@department", this.Department));

                // 将该查询过程绑定到DataAdapter
                SqlDataAdapter adp = new SqlDataAdapter();
                adp.SelectCommand = cmd;

                // 将DataSet和DataAdapter绑定
                DataSet ds = new DataSet();

                // 自定义一个表来标识数据库的record表
                adp.Fill(ds, "xianshi");

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

五、成果展示

1、统计查询

统计查询.gif

2、详情展示

详情.gif