- Foxtable(狐表) (http://foxtable.com/bbs/index.asp)
-- 专家坐堂 (http://foxtable.com/bbs/list.asp?boardid=2)
---- 求助：考勤报表产生 (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=92885)
-- 发布时间：2016/11/15 17:34:00
-- 求助：考勤报表产生
考勤数据已从考勤机获取，表结构如下所示：
(困惑：上/下班数据不在同一行上。)
此主题相关图片如下：111.jpg
查询了论坛的一些帖子，感觉下面的代码有用：
Dim dt1 As Date = #11/14/2016#
Dim dt2 As Date = #11/14/2016#
Dim dtb As New DataTableBuilder("统计表1")
dtb.AddDef("name", Gettype(String), 32)
dtb.AddDef("cardno", Gettype(String), 32)
dtb.AddDef("date", Gettype(Date))
dtb.AddDef("t1", Gettype(String))
dtb.AddDef("t2", Gettype(String))
dtb.Build
For Each dr1 As DataRow In DataTables("AtdRecord88").DataRows
For i As Integer = 0 To (dt2 - dt1).TotalDays
Dim dr2 As DataRow
dr2 = DataTables("统计表1").Addnew
dr2("name") = dr1("recName")
dr2("CardNo") = dr1("CardNo")
dr2("date") = dt1.AddDays(i)
Dim drs As List(of DataRow) = DataTables("AtdRecord88").Select("CardNo = \'" & dr2("CardNo") & "\'And Time >= #" & dr2("Date") & "# And Time < #" & dr2("Date").AddDays(1) & "# ")
For n As Integer = 0 To drs.count -1
If n = 0 Then
dr2("t1") = Format(drs(n)("Time"),"HH:mm")
ElseIf n = 1 Then
dr2("t2") = Format(drs(n)("Time"),"HH:mm")
Exit For
End If
Next
Next
Next
得到如下结果：
此主题相关图片如下：222.jpg
需要麻烦解决：
1.同一人的数据产生的2次(重复数据)，删除其中1次；
2.若只刷了下班时候(12:00以后)，希望时间能新增到t2，t1时间留空；---难的话，这点可以不解决
非常感谢~
[此贴子已经被作者于2016/11/15 17:36:58编辑过]
-- 发布时间：2016/11/15 20:03:00
--
导出这个表部分数据测试，或者上传例子
-- 发布时间：2016/11/16 9:31:00
--
Dim dt1 As Date = #11/14/2016#
Dim dt2 As Date = #11/14/2016#
Dim dtb As New DataTableBuilder("统计表1")
dtb.AddDef("name", Gettype(String), 32)
dtb.AddDef("cardno", Gettype(String), 32)
dtb.AddDef("date", Gettype(Date))
dtb.AddDef("t1", Gettype(String))
dtb.AddDef("t2", Gettype(String))
dtb.Build
For Each dr1 As DataRow In DataTables("AtdRecord88").DataRows
For i As Integer = 0 To (dt2 - dt1).TotalDays
Dim dr2 As DataRow = DataTables("统计表1").find("CardNo = \'" & dr1("CardNo") & "\'And date >= #" & dr1("time") & "# And date < #" & dr1("time").AddDays(1) & "# ")
If dr2 IsNot Nothing Then
dr2 = DataTables("统计表1").Addnew
dr2("name") = dr1("recName")
dr2("CardNo") = dr1("CardNo")
dr2("date") = dt1.AddDays(i)
Dim drs As List(of DataRow) = DataTables("AtdRecord88").Select("CardNo = \'" & dr2("CardNo") & "\'And Time >= #" & dr2("Date") & "# And Time < #" & dr2("Date").AddDays(1) & "# ")
For n As Integer = 0 To drs.count -1
Dim t As String = Format(drs(n)("Time"),"HH:mm")
If t <= "12:00" Then
dr2("t1") = t
Else
dr2("t2") = t
End If
Exit For
Next
End If
Next
Next
-- 发布时间：2016/11/18 15:39:00
--
| 下载信息 [文件大小： 下载次数： ] | |
 点击浏览该文件:测试1116.foxdb |
[此贴子已经被作者于2016/11/18 16:35:52编辑过]
-- 发布时间：2016/11/18 16:54:00
--
Dim dtb As New DataTableBuilder("统计表1")
dtb.AddDef("name", Gettype(String), 32)
dtb.AddDef("recgroup", Gettype(String), 32)
dtb.AddDef("cardno", Gettype(String), 32)
dtb.AddDef("date", Gettype(Date))
dtb.AddDef("t1", Gettype(String))
dtb.AddDef("t2", Gettype(String))
dtb.Build
Dim drs As List(Of DataRow) = DataTables("表A").Select("","CardNo,time")
Dim drpre As DataRow = drs(0)
Dim drnew As DataRow = DataTables("统计表1").Addnew
drnew("name") = drpre("recName")
drnew("recgroup") = drpre("recgroup")
drnew("CardNo") = drpre("CardNo")
Dim d As Date = drpre("time")
drnew("date") = drpre("time").Date
Dim d2 As Date = new Date(d.Year,d.Month,d.Day,12,0,0)
If d < d2 Then
drnew("t1") = Format(drpre("Time"),"HH:mm")
Else
drnew("t2") = Format(drpre("Time"),"HH:mm")
End If
For i As Integer = 1 To drs.Count - 1
drpre = drs(i)
If drnew("CardNo") = drpre("CardNo") AndAlso drnew("date") = drpre("time").Date Then
d = drpre("time")
d2 = new Date(d.Year,d.Month,d.Day,12,0,0)
If d < d2 Then
drnew("t1") = Format(drpre("Time"),"HH:mm")
Else
drnew("t2") = Format(drpre("Time"),"HH:mm")
End If
Else
drnew = DataTables("统计表1").Addnew
drnew("name") = drpre("recName")
drnew("recgroup") = drpre("recgroup")
drnew("CardNo") = drpre("CardNo")
d = drpre("time")
drnew("date") = drpre("time").Date
d2 = new Date(d.Year,d.Month,d.Day,12,0,0)
If d < d2 Then
drnew("t1") = Format(drpre("Time"),"HH:mm")
Else
drnew("t2") = Format(drpre("Time"),"HH:mm")
End If
End If
Next
[此贴子已经被作者于2016/11/18 16:54:30编辑过]
-- 发布时间：2017/12/1 16:08:00
--
楼主，可否把考勤数据已从考勤机获取 的代码贴出来学习一下，谢谢，或发到我的QQ邮箱 249592026@qq.com
-- 发布时间：2017/12/1 17:04:00
--
以下是引用yj249592026在2017/12/1 16:08:00的发言：
楼主，可否把考勤数据已从考勤机获取 的代码贴出来学习一下，谢谢，或发到我的QQ邮箱 249592026@qq.com
楼主，可否把考勤数据已从考勤机获取 的代码贴出来学习一下，谢谢，或发到我的QQ邮箱 249592026@qq.com