DataTables("人员清点日志").LoadFilter = "清点日期 >= '" & yc & "' and 清点日期 <= '" & yd & "'"
DataTables("人员清点日志").Load
Dim sql As String = "S elect 清点日期,清点单位,操作信息_开始时间,操作信息_结束时间,Count(清点单位) As 计数,0.1-0.1 as 平均用时 F ROM {人员清点日志} WHERE 清点日期 >= '" & yc & "' And 清点日期 <= '" & yd & "'Group By 清点日期,清点单位,操作信息_开始时间,操作信息_结束时间"
Dim b As New CrossTableBuilder("人员清点月汇总",sql,"SCGL")
b.HGroups.AddDef("清点日期",DateGroupEnum.None)
b.VGroups.AddDef("清点单位")
b.Totals.AddDef("清点单位",AggregateEnum.Count,"批次") '添加数量列用于统计
b.Totals.AddDef("平均用时")
b.HorizontalTotal = True
b.VerticalTotal = True
b.Build '生成统计表
MainTable = Tables("人员清点月汇总")
Dim dw1 As String
Dim qdrq As Date
Dim pc As Integer
Dim t As Table = Tables("人员清点月汇总")
Dim i As Integer =(Tables("人员清点月汇总").Cols.Count - 3) / 2
For Each r As Row In t.Rows
For j As Byte = 1 To i
dw1 = Left(t.Cols("清点单位_" & j).Caption,4) '单位
If r.IsNull("清点单位_" & j) = False And r("清点日期") <> "合计"Then
qdrq = r("清点日期") '清点日期
pc = r("清点单位_" & j) '批次
Dim N1,h1,m1,s1 As Integer
N1 = DataTables("人员清点日志").Compute("Sum(操作信息_用时秒)", "清点日期 = '" & qdrq & "' and 清点单位 = '" & dw1 & "'") / pc
h1 = N1 \ 3600
m1 = (N1 Mod 3600) \ 60
s1 = N1 Mod 60
Dim sj1 As Date = Format(new Date(1,1,1,h1,m1,s1), "HH:mm:ss")
r("平均用时_" & j) = N1
ElseIf r.IsNull("清点单位_" & j) = False And r("清点日期") = "合计"Then
Dim N11,h11,m11,s11 As Integer
N11 = DataTables("人员清点日志").Compute("Sum(操作信息_用时秒)", " 清点单位 = '" & dw1 & "'") / r("清点单位_" & j)
h11 = N11 \ 3600
m11 = (N11 Mod 3600) \ 60
s11 = N11 Mod 60
Dim sj11 As Date = Format(new Date(1,1,1,h11,m11,s11), "HH:mm:ss")
r("平均用时_" & j) = N11
End If
Next
Dim N2,h2,m2,s2 As Integer
N2 = DataTables("人员清点日志").Compute("Sum(操作信息_用时秒)", "清点日期 = '" & qdrq & "'") / r("合计_批次")
h2 = N2 \ 3600
m2 = (N2 Mod 3600) \ 60
s2 = N2 Mod 60
Dim sj2 As Date = Format(new Date(1,1,1,h2,m2,s2), "HH:mm:ss")
r("合计_平均用时") = N2
Next
Dim dr2 As DataRow = DataTables("人员清点月汇总").Find("清点日期 = '合计'")
If dr2 IsNot Nothing Then
Dim N22,h22,m22,s22 As Integer
N22 = DataTables("人员清点日志").Compute("Sum(操作信息_用时秒)", "清点日期 >= '" & yc & "' and 清点日期 <= '" & yd & "'") / dr2("合计_批次")
h22 = N22 \ 3600
m22 = (N22 Mod 3600) \ 60
s22 = N22 Mod 60
Dim sj22 As Date = Format(new Date(1,1,1,h22,m22,s22), "HH:mm:ss")
dr2("合计_平均用时") = N22
End If
For Each cl As Col In Tables("人员清点月汇总").Cols '居中
If cl.name <> "清点日期" Then
cl.TextAlign = TextAlignEnum.Center
End If
Next
Tables("人员清点月汇总").AutoSizeCols()
Tables("人员清点月汇总").Cols("合计_批次").Move(1)
Tables("人员清点月汇总").Cols("合计_平均用时").Move(2)
DataTables("人员清点月汇总").SysStyles("Frozen").BackColor = Color.LightCyan
DataTables("人员清点月汇总").SysStyles("CurrentRow").BackColor = Color.Blue
DataTables("人员清点月汇总").SysStyles("CurrentRow").ForeColor = Color.White
Tables("人员清点月汇总").ListMode = True
Tables("人员清点月汇总").Cols.Frozen = 3