Dim b As New SQLGroupTableBuilder("统计表1","检测样品登记")
b.Groups.AddDef("送检单位")
b.Groups.AddDef("送检日期",DateGroupEnum.Year,"年")
b.Groups.AddDef("送检日期",DateGroupEnum.Month,"月")
b.Groups.AddDef("送检日期",DateGroupEnum.Day,"日")
b.Totals.AddDef("送检日期",AggregateEnum.Count,"样品数量")
b.Build() '参数设置为False,生成一个临时表,不在主界面显示
'然后根据统计表生成目录树
dim dt as datatable = DataTables("统计表1")
Dim trv As WinForm.TreeView = e.Form.Controls("TreeView1")
trv.BuildTree(dt, "送检单位|年|月|日|送检单位")
trv.StopRedraw
For Each nd As WinForm.TreeNode In trv.AllNodes
Dim Product As String = nd.DataRow("送检单位")
Dim Year As Integer = nd.DataRow("年")
Dim Month As Integer = nd.DataRow("月")
Dim Day As Integer = nd.DataRow("日")
Select Case nd.Level
Case 0
nd.Text = nd.text & "(" & dt.Compute("Sum(样品数量)","送检单位 = '" & product & "'") & "条)"
Case 1
nd.Text = nd.text & "年(" & dt.Compute("Sum(样品数量)","送检单位 = '" & product & "' and 年 = " & Year) & "条)"
Case 2
nd.Text = nd.text & "月(" & dt.Compute("Sum(样品数量)","送检单位 = '" & product & "' and 年 = " & Year & " And 月 = " & Month) & "条)"
Case 3
nd.Text = nd.text & "日(" & dt.Compute("Sum(样品数量)","送检单位 = '" & product & "' and 年 = " & Year & " And 月 = " & Month & " And 日 = " & Day) & "条)"
End Select
Next
trv.Nodes.Insert("加载所有行","加载所有行(" & dt.Compute("Sum(样品数量)") & "条)",0)
trv.ResumeRedraw
-------------------------
Dim Filter As String = ""
If e.Node.Name <> "加载所有行" Then '要用Name属性,不能用Text属性
Dim Product As String = e.Node.DataRow("送检单位")
Dim Year As Integer = e.Node.DataRow("年")
Dim Month As Integer = e.Node.DataRow("月")
Dim Day As Integer = e.Node.DataRow("日")
Select Case e.Node.Level
Case 0
Filter ="送检单位 = '" & product & "'"
Case 1
Filter ="送检单位 = '" & product & "' and Year(检测日期) = " & Year
Case 2
Filter ="送检单位 = '" & product & "' and Year(检测日期) = " & Year & " And Month(检测日期) = " & Month
Case 3
Filter ="送检单位 = '" & product & "' and Year(检测日期) = " & Year & " And Month(检测日期) = " & Year & " And Day(检测日期) = " & Day
End Select
End If
DataTables("检测样品登记").LoadFilter = Filter
DataTables("检测样品登记").Load()