如何将时钟输入/输出时间从SQL服务器排序到数据网格视图中 [英] How do I sort clock in/out times from an SQL server into a datagridview
问题描述
我在SQL中有一个表,其中包含从一个时钟输入点收集的时钟时间。时间表如下所示:
I have a table in SQL that contains clock times gathered from the one clock-in point. The times appear in the table like so:
pkClockingTime dtDateTime fkEmployee
1 2017-02-06, 07:59:00.000 49
2 2017-02-06, 08:00:00.000 50
3 2017-02-06, 12:00:00.000 49
32 2017-02-06, 12:01:00.000 50
33 2017-02-06, 13:00:00.000 50
34 2017-02-06, 13:04:00.000 49
35 2017-02-06, 17:30:00.000 50
36 2017-02-06, 17:32:00.000 49
等等;这只是典型日两个人的时钟时间的示例,其中时钟输入为8,午餐时钟为12,时钟输入为1,然后在5:30返回。
如果有人可以提供帮助或提供任何令人惊叹的建议!
我尝试了什么:
目前,我通过从TableAdapter创建DataTable并将其附加到绑定到DataGridView的BindingSource,按照Employee和date的顺序显示时间,但它在DataGridView中每个员工有2行,每个时钟输入/输出一个,我希望在一行上全部4次。
我希望它可以修改为在1行的同一天从Employee输出所有4次
到目前为止我创建的DataTable代码输出2行员工如下:
and so on; this is just an example of two peoples clock times on a typical day with clock-in at 8, clock-out for lunch at 12, clock back in at 1, then going home at 5:30.
If anyone can help or offer any advice that would be amazing!
What I have tried:
At the moment I have it displaying the times in order of Employee and date by creating a DataTable from the TableAdapter and attaching that to a BindingSource which is bound to the DataGridView, but it has 2 lines per employee in the DataGridView, one for each Clock In/Out where as i would like all 4 times on one line.
I was hoping it can be modified to output all 4 times from the Employee on the same date on 1 line
The code i have so far for creating the DataTable which outputs 2 lines per Employee is as follows:
Private Function FormatedClockingTimes() As DataTable
Dim dtResults As New DataTable
dtResults.Columns.Add("EmployeeID", GetType(Integer))
dtResults.Columns.Add("Date", GetType(Date))
dtResults.Columns.Add("ClockIn", GetType(DateTime))
dtResults.Columns.Add("ClockOut", GetType(DateTime))
Dim intLastEmployee As Integer = 0
Dim intClockCounter As Integer = 0
Dim drLastRow As DataRow
Dim dtCurrentDate As Date
Dim dtPreviousDate As Date
For Each drSourceRow As DataRow In TblAttendanceTimesTableAdapter.GetData() 'Get the raw clocking times and loop through each row
'increment the counter by 1
intClockCounter += 1
'Store the date from the row as CurrentDate
dtCurrentDate = drSourceRow("dtDateTime").ToString
'If previous date has not been set yet (its the first loop), then set the PreviousDate as the current date
If (dtPreviousDate = Nothing) Then
dtPreviousDate = dtCurrentDate
End If
'Check that the employee from this row is equal to the employee from previous row, unless the employee from last row is 0 (its the firs row in the loop)
'Also, check the date from this row is equal to the date from previous row, as the PreviousDate was set on the first lopp to match curerent date, the first row of the loop will always onsert a new row in to the results table
If ((drSourceRow("fkEmployee").ToString() = intLastEmployee Or intLastEmployee = 0) And dtCurrentDate.ToShortDateString = dtPreviousDate.ToShortDateString) Then
If (intClockCounter = 1) Then 'If the counter is still 1, the add a new record.
'Insert new row into the results table based on the current row in the loop of the raw clocking data
dtResults.Rows.Add(drSourceRow("fkEmployee").ToString(), dtCurrentDate, drSourceRow("dtDateTime").ToString(), Nothing) 'Nothing is added last as it is the clock out time, which wont be set as this part of the loop always sets the clock in data
Else
'Update last row (newly inserted row in the results table) as clock out time
drLastRow = dtResults.Rows(dtResults.Rows.Count - 1) 'this gets the last row index
drLastRow("ClockOut") = drSourceRow("dtDateTime").ToString() 'now update the ClockOut on the last results row with the current row in the loop for the raw data
intClockCounter = 0 ' now that the clock out time has been set, we need to make the next loop insert a new row into the results data table again for the next clock in row, so we set the counter to 0
End If
Else
'if the previous employee or date did not match, then assume its a clock in again for either a new employee or a new date
dtResults.Rows.Add(drSourceRow("fkEmployee").ToString(), dtCurrentDate.ToShortDateString, drSourceRow("dtDateTime").ToString(), Nothing)
'set the counter to 1 so that the next loop will update and not insert
intClockCounter = 1
End If
'store the employee from the current raw data row as last employee so that on the next lopp it can check last employee against its current employee
intLastEmployee = drSourceRow("fkEmployee").ToString()
'do same for date
dtPreviousDate = dtCurrentDate
Next
'return the results data table to the function
Return dtResults
推荐答案
当您使用SQL从数据库获取数据时,SQL关键字ORDER BY将起作用。以下是关键字的相关信息: SQL ORDER BY关键字 [ ^ ]
As you are getting your data from a DB by using SQL, the SQL keyword ORDER BY will do the trick. Here is information on the keyword: SQL ORDER BY Keyword[^]
这篇关于如何将时钟输入/输出时间从SQL服务器排序到数据网格视图中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!