If e.Form.Controls("ComboBox2").Value Is Nothing Then
MessageBox.Show("请在右侧选择生成报表的年份","提示")
Return
End If
'根据年份生成临时表
Dim nf,sql1,sql2,sql3,s1,s2,jgf,rjs,rjjgf,wb,Bs As String
nf = e.Form.Controls("ComboBox2").Value
sql1 = " From(Select 报表年月,日期,单位,Sum(加工费) As 加工费,Sum(投工) As 投工 FROM {日报源表} WHERE Year(日期) = "
sql1+= nf & " Group By 报表年月,日期,单位 ORDER BY 单位,日期,报表年月) Group By 单位"
sql2="Select DISTINCT 报表年月 FROM {日报源表} WHERE Year(日期) = " & nf
Dim cmd As New SQLCommand
Dim dt As DataTable
cmd.CommandText = sql2
dt = cmd.ExecuteReader()
Dim Ls As List(Of String)
Ls =dt.GetUniqueValues("","报表年月")
For n As Integer = 0 To ls.Count-1
s1 = "sum(iif(报表年月 = " & Ls(n) & ",加工费,Null))"
jgf = s1 & " as " & Ls(n) & "_加工费"
s2 = "avg(iif(报表年月 = " & Ls(n) & ",投工,Null))"
rjs = s2 & " as " & Ls(n) & "_月均人数"
rjjgf= s1 & "/" & s2 & " as " & Ls(n) & "_人均加工费"
If n >0 Then
wb = s1 & "/" & s2 & "/(sum(iif(报表年月 = " & Ls(n-1) & ",加工费,Null))/ avg(iif(报表年月 = " & Ls(n-1) & ",投工,Null))" & ") as " & Ls(n) & "_环比"
If n = ls.Count-1 Then
wb = wb & ",Sum(加工费) As 合计_加工费,sum(投工) as 合计_年均人数,sum(加工费) as 年人均加工费,Sum(加工费) As 名次"
End If
End If
sql3+ = "," & jgf & "," & rjs & "," & rjjgf & "," & wb
Next
sql3="select 单位" & sql3.Replace(",,",",") & sql1
Dim b As New SQLGroupTableBuilder("年度汇总","日报源表")
b.Groups.AddDef("单位") '根据产品分组
b.Totals.AddDef("加工费") '对数量进行统计
b.Build '生成统计表
DataTables("年度汇总").Fill(sql3,True)
For Each dc As DataCol In DataTables("年度汇总").DataCols
If dc.name <> "单位" Then
DataTables("年度汇总").DataCols(dc.Name).SetFormat("0.00")
bs+="," & dc.name
End If
Next
DataTables("年度汇总").DataCols("名次").SetFormat("0")
Dim sum As Double
For Each r As Row In Tables("年度汇总").Rows
sum =0
For Each dc As DataCol In DataTables("年度汇总").DataCols
If dc.name.IndexOf("_月均人数")> -1 Then
sum+= r(dc.name)
End If
If dc.name = "合计_年均人数" Then
r("合计_年均人数") = sum/ls.Count
r("年人均加工费") = ls.Count*r("合计_加工费")/sum
Exit For
End If
Next
Next
Dim drs As List(Of DataRow) = DataTables("年度汇总").Select("[单位] <> '合计'","年人均加工费 DESC")
For n As Integer = 0 To drs.Count - 1 '遍历所有行
If n > 0 AndAlso drs(n)("年人均加工费") = drs(n-1)("年人均加工费") Then '如果年人均加工费和上一行相同
drs(n)("名次") = drs(n-1)("名次") '则排名等于上一行
Else
drs(n)("名次") = n + 1 '设置名次
End If
Next
Dim t As Table = Tables("年度汇总")
Dim g As SubtotalGroup
t.SubtotalGroups.Clear()
t.GroupAboveData = False
t.TreeVisible = False
t.SpillNode = True
g = New Subtotalgroup
g.Aggregate = AggregateEnum.Sum
g.GroupOn = "*"
g.TotalOn = bs.Trim(",").Replace(",名次","")
g.Caption = "合计"
t.SubtotalGroups.Add(g)
t.Subtotal()
Dim r1 As Row
With Tables("年度汇总")
For i As Integer = 0 To .Rows.Count(True) - 1 'Count加上参数True
r1 = .Rows(i,True) 'Rows也需要加上参数True
If r1.IsGroup Then '如果是分组行
For n As Integer = 0 To .Cols.Count -1
If .cols(n).name.Indexof("人均加工费")> -1 Then
r1(.cols(n).name) = r1(.cols(n-2).name)/r1(.cols(n-1).name)
End If
Next
End If
Next
End With
Tables("年度汇总").Cols("单位").TextAlign = TextAlignEnum.Center
Tables("年度汇总").Cols("名次").TextAlign = TextAlignEnum.Center
Tables("年度汇总").AutoSizeCols()
MainTable = Tables("年度汇总") '
加好友 
发短信
Post By：2011/7/12 16:48:00 [显示全部帖子]
