Dim ryrq As WinForm.DateTimePicker = e.Form.Controls("每日人员日期")
Dim dt1 As DataTable
Dim sql As String = "select 检测日期, 检测方法, 检测人员,sum(检测量) as 检测量,sum(返修量) as 返修量 from (select 首检_检测日期 as 检测日期,首检_检测长度 AS 检测量,首检_检测人员 as 检测人员,首检_返修长度 AS 返修量,检测方法 AS 检测方法 from {无损_检测明细} union all " & _
"Select 一次复检_检测日期 As 检测日期,一次复检_检测长度 as 检测量,一次复检_检测人员 as 检测人员,一次复检_返修长度 as 返修量,检测方法 as 检测方法 from {无损_检测明细} union all " & _
"Select 二次复检_检测日期 As 检测日期,二次复检_检测长度 as 检测量,二次复检_检测人员 as 检测人员,二次复检_返修长度 as 返修量,检测方法 as 检测方法 from {无损_检测明细}) as a where 检测日期 = #" & ryrq.Value & "# GROUP BY 检测日期,检测方法,检测人员"
Dim cmd As new SQLCommand
cmd.ConnectionName = "检测系统"
cmd.CommandText = sql
dt1 = cmd.ExecuteReader()
Dim lst As new List(of DataRow)
For Each dr1 As DataRow In dt1.Select("检测人员 like '%,%'")
lst.Add(dr1)
Dim rys() As String = dr1("检测人员").Split(",")
For Each ry As String In rys
Dim v As Double = dr1("检测量") / rys.Length '获得平均之后每个检测人员的检测量
Dim v1 As Double = dr1("返修量") / rys.Length
Dim dr2 As DataRow = dt1.AddNew()
dr2("检测人员") = ry '新增行的工号列设置为此工号
dr2("检测方法") = dr1("检测方法") '设置增行的产品
dr2("检测日期") = dr1("检测日期")
dr2("检测量") = v '加上平均后的产量
dr2("返修量") = v1
Next
Next
For Each dr1 As DataRow In lst
dr1.Delete
Next
Dim dt As DataTable
Dim dtb As New GroupTableBuilder("个人检测量",dt1)
dtb.Groups.AddDef("检测日期")
dtb.Groups.AddDef("检测方法")
dtb.Groups.AddDef("检测人员")
dtb.Totals.AddDef("检测量")
dtb.Totals.AddDef("返修量")
e.Form.Controls("Table1").Table.datasource = dtb.BuildDataSource
加好友 
发短信
Post By：2018/7/21 12:13:00 [只看该作者]
