如何获得上午和下午从值班到值班的时差

本文关键字：时差 何获得 | 更新日期: 2023-09-27 18:31:44

更新的出勤数据

我们

被困在如何获取时间列的总小时数的函数上，我们想要的是，值班时间减去早上时间的值班时间，以及下午时间，以便我们可以对时间列的小时数求和。我们希望显示时间列最后一行的总计。

样本出勤数据

//display attendance data from database
    private void btnShow_Click(object sender, EventArgs e)
    {
        SqlConnection conn = new SqlConnection(@"Data Source=.'SQLEXPRESS;AttachDbFilename=C:'Users'Ralph'Documents'User.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"); ;
        // SqlDataAdapter adapSel;
        conn.Open();
        //SqlDataAdapter sda = new SqlDataAdapter("SELECT * from AMPS  where DateandTime >= '" + dtpFrom.Value.ToString("dd/MMM/yyyy") + "' AND DateandTime <= '" + dtpTo.Value.ToString("dd/MMM/yyyy") + "'", conn);
        SqlDataAdapter sda = new SqlDataAdapter("SELECT WorkersInfo.WorkersID, WorkersInfo.LName, WorkersInfo.FName, WorkersInfo.MName, WorkersInfo.Position, WorkersInfo.Salary, AMPS.InandOut, cast(DateandTime as date) [Date], RIGHT(CONVERT(VARCHAR, DateandTime, 100),7) as Time FROM AMPS INNER JOIN WorkersInfo ON AMPS.EnNo = WorkersInfo.WorkersID where DateandTime between '" + dtpFrom.Value.ToString("dd/MMM/yyyy") + "' and '" + dtpTo.Value.ToString("dd/MMM/yyyy") + "'", conn);
        DataGridViewColumn InandOut = grdList.Columns[6];
        InandOut.Width = 100;
        DataGridViewColumn DateandTime = grdList.Columns[7];//column DateandTime
        DateandTime.Width = 145;
        DataGridViewColumn MName = grdList.Columns[3];//column MiddleName
        MName.Width = 50;//changing the column width
        //da.Fill(ds);
        DataTable dt = new DataTable();
        sda.Fill(dt);
        grdList.DataSource = dt;
        conn.Close();
    }
    private void DisplayData()
    {
        CON.Open();
        DataTable dt = new DataTable();
        adapt = new SqlDataAdapter("SELECT WorkersInfo.WorkersID, WorkersInfo.LName, WorkersInfo.FName, WorkersInfo.MName, WorkersInfo.Position, WorkersInfo.Salary, AMPS.InandOut, AMPS.DateandTime FROM AMPS INNER JOIN WorkersInfo ON AMPS.EnNo = WorkersInfo.WorkersID", CON);
        adapt.Fill(dt);
        grdList.DataSource = dt;
        CON.Close();
    }
    private void grdList_CellContentClick(object sender, DataGridViewCellEventArgs e)
    {
        txtWorkersID.Text = grdList.Rows[e.RowIndex].Cells[0].Value.ToString();
        txtLast_Name.Text = grdList.Rows[e.RowIndex].Cells[1].Value.ToString() + ", " + grdList.Rows[e.RowIndex].Cells[2].Value.ToString() + ", " + grdList.Rows[e.RowIndex].Cells[3].Value.ToString();
        txtPosition.Text = grdList.Rows[e.RowIndex].Cells[4].Value.ToString();
        txtDRate.Text = grdList.Rows[e.RowIndex].Cells[5].Value.ToString();
        //cboGender.Text = grdWorkers.Rows[e.RowIndex].Cells[5].Value.ToString();
    }

但是我们被困在如何计算从值班到值班的小时数或时差上

如何获得上午和下午从值班到值班的时差

您可以使用DateTime和TimeSpan执行此操作。 SQL 查询实际上将日期和时间对象拆分为单独的日期和时间列。可以在 C# 中重新构造 DateTime，也可以只从查询中提取其他列。

DateTime timeOut = x;//use your dateandtime column value here for time out
DateTime timeIn = y;//same here for time in
TimeSpan timeSpan = timeOut.Subtract(timeIn);

然后，您可以访问：

timeSpan.Hours;
timeSpan.Minutes;
timeSpan.Seconds;

如果您遍历所有条目并计算每天 2 个时间跨度，然后将它们相加，您将获得工作人员花费的总时间的值。

更新：要遍历数据表，您可以像这样，但请注意，它有很多假设，如果可能的话，您可能应该更改数据表的结构。 我会有一行带有输入时间和退出时间，而不必循环浏览每个条目并找到相应的输入和输出条目。但这取决于您是否可以更改基础数据库）。

综上所述，在您填写完数据表之后 - 我会做如下操作：

foreach (var dateGroup in dt.AsEnumerable().GroupBy(dg => dg[7])) //this groups your entries by date
        {
            TimeSpan totalTimeForDate = new TimeSpan(0);
            var ins = dateGroup.Where(dg => dg[6].ToString() == "DutyOn").OrderBy(dg => dg[8]).ToList();//this gets your dutyins and orders by time
            var outs = dateGroup.Where(dg => dg[6].ToString() == "DutyOff").OrderBy(dg => dg[8]).ToList();//this gets your dutyoffs and orders by time
            for (int inOutCounter = 0; inOutCounter < ins.Count(); inOutCounter++)
            {
                DateTime timeIn = DateTime.Parse(ins[inOutCounter][7].ToString() + ins[8].ToString()); //you will need to modify this to parse the date correctly, 
                DateTime timeOut = DateTime.Parse(outs[inOutCounter][7].ToString() + outs[8].ToString()); //or change you sql query to bring back the datetime in another column and parse that
                totalTimeForDate += timeOut.Subtract(timeIn);
                //You will need to do some error handling in here in case there are missing entries
                //This code assumes there is always an out time to match an in time
            }
            //here you can now access:
            var hours = totalTimeForDate.Hours;
            var minutes = totalTimeForDate.Minutes;
            var seconds = totalTimeForDate.Seconds;
        }
相关文章：