统计与查询

1.界面

1.1管理员统计查询界面

捕获1.PNG

1.2管理员详细查看界面

捕获2.PNG

1.3员工查询界面

捕获.PNG

2.代码部分

2.1管理员统计代码

主要是SQL语句实现了整个过程的统计和查询:

 @"select t5.department, COUNT(*) as num from (
               select t4.department,t4.name,t3.date,t3.mintime,t3.maxtime,t3.diff 
               from employee t4 inner join (
                   select n1.employee_id,n1.date,n1.time as mintime,n2.time as maxtime,DATEDIFF(n,n1.time,n2.time) as diff 
                   from record n1 inner join record n2 
                   on n1.employee_id=n2.employee_id 
                   where n1.date=n2.date and n1.machine_id=1 and n2.machine_id=2
                                    ) t3 
                   on t4.id=t3.employee_id
             ) t5 where diff<540 and date>=@date1 and date<=@date2 group by t5.department;";

点击查看实现

private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.ColumnIndex != -1 && dataGridView1.Columns[e.ColumnIndex].Name.Equals("Column3"))
            {
            }
      }

2.2管理员详细查看界面

通过传值实现实现详细查询:
父界面

String dep = dataGridView1["Column1", e.RowIndex].Value.ToString(); 。
                DateTime date1 = dateTimePicker1.Value;
                DateTime date2 = dateTimePicker2.Value;
                chakanForm ckf = new chakanForm(dep, date1, date2);
                ckf.ShowDialog();

子界面

private string dep;
        private DateTime start;
        private DateTime end;
        public chakanForm(string dep, DateTime start, DateTime end)
        {
            this.dep = dep;
            this.start = start;
            this.end = end;
            InitializeComponent();
        }

2.3员工查询界面

整个查询过程

string connStr = ConfigurationManager.ConnectionStrings["Attendance"].ConnectionString;
            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();
            
            //2.利用DataAdapter对象，建立与数据库的连接桥
            string selectStr = "select record.serial,record.employee_id,employee.name,employee.department,record.date,record.time,record.machine_id from record inner join employee on record.employee_id=employee.id where 1=1 and date>=@date1 and date<=@date2 and id=@id;";
            SqlCommand cmd = new SqlCommand(selectStr, conn);

            // 将该查询过程绑定到DataAdapter
            
            cmd.Parameters.Add(new SqlParameter("@id", UserInfo.userId));
            cmd.Parameters.Add(new SqlParameter("@date1", dateTimePicker1.Value.ToString()));
            cmd.Parameters.Add(new SqlParameter("@date2", dateTimePicker2.Value.ToString()));
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = cmd;
            //3.通过DataAdapter桥，将查询结果存储到DataSet对象中
            DataSet ds = new DataSet();
            adapter.Fill(ds);

            //4.利用DataGridView控件将DataSet中的查询结果显示出来
            dataGridView1.DataSource = ds.Tables[0];

            //5.关闭数据库连接
            conn.Close();