Dim dtb As New DataTableBuilder("年阶梯差额")
dtb.AddDef("姓名", Gettype(String), 32)
dtb.AddDef("住址", Gettype(String), 255)
dtb.AddDef("日期", Gettype(Date))
dtb.AddDef("抄见电量", Gettype(Integer))
dtb.AddDef("电费代码",Gettype(String),32)
dtb.AddDef("总费用",Gettype(Double))
dtb.Build()
Dim nList As List(of String) = DataTables("电费").GetUniqueValues("","姓名")
Dim yList As new List(of Integer)
Dim d As Date = DataTables("电费").Compute("min(日期)")
For i As Integer = Val(d.Year) To Val(Date.Today.Year)
ylist.Add(i)
Next
Dim nameList As new Dictionary(of String,String)
For i As Integer = 0 To nList.Count-1
For j As Integer = 0 To ylist.Count -1
If DataTables("电费").Compute("sum(抄见电量)","姓名='" & nList(i) & "' and 日期 <= #12/31/" & ylist(j) & "# and 日期 >= #1/1/" & ylist(j) & "#") > 2160 Then
If NameList.ContainsKey(nlist(i)) Then
NameList(nlist(i))=NameList(nlist(i)) & "," & ylist(j)
Else
NameList.Add(nlist(i),ylist(j))
End If
End If
Next
Next
For i As Integer = 0 To DataTables("电费").DataRows.Count-1
Dim dr2 As DataRow =DataTables("电费").DataRows(i)
If dr2("电费代码")="A" AndAlso nameList.ContainsKey(dr2("姓名")) Then
For Each s As String In nameList(dr2("姓名")).split(",")
If dr2("日期").year=s Then
Dim dr As DataRow = DataTables("年阶梯差额").addNew
dr("姓名")=dr2("姓名")
dr("住址")=dr2("住址")
dr("日期")=dr2("日期")
dr("抄见电量")=dr2("抄见电量")
dr("电费代码")=dr2("电费代码")
If dr2("抄见电量")<=2160 Then
dr("总费用")=dr2("抄见电量")*0.573
Else If dr2("抄见电量")<=4800 Then
dr("总费用")=(dr2("抄见电量")-2160)*0.05
Else
dr("总费用")=((4800-2160)*0.05)+((dr2("抄见电量")-4800)*0.3)
End If
End If
Next
End If
Next
MainTable= Tables("年阶梯差额")
Tables("年_居民生活电价").DataSource=DataTables("年阶梯差额")
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 = "抄见电量,总费用"
g.Caption = "{0}年 小计"
g.DateGroup = DateGroupEnum.Year
t.SubtotalGroups.Add(g)
g = New Subtotalgroup
g.Aggregate = AggregateEnum.Sum
g.GroupOn = "住址"
g.TotalOn = "抄见电量,总费用"
g.Caption = "{0} 小计"
t.SubtotalGroups.Add(g)
t.Subtotal()
For i As Integer = 1 To t.grid.Rows.Count-1
If t.grid.Rows(i).IsNode AndAlso t.grid.Rows(i).Node.level=1 Then
Dim dr2=t.grid.Rows(i)
If dr2("抄见电量")<=2160 Then
dr2("总费用")=dr2("抄见电量")*0.573
Else If dr2("抄见电量")<=4800 Then
dr2("总费用")=(dr2("抄见电量")-2160)*0.05
Else
dr2("总费用")=((4800-2160)*0.05)+((dr2("抄见电量")-4800)*0.3)
End If
End If
If t.grid.Rows(i).IsNode AndAlso t.grid.Rows(i).Node.level=0 Then
For j As Integer = 2 To t.grid.Rows.Count-1
If t.grid.Rows(j-1)("姓名")=t.grid.Rows(i-2)("姓名") AndAlso t.grid.Rows(j).IsNode Then
'MessageBox.Show(t.grid.Rows(j)("姓名") & t.grid.Rows(j).Node.level)
t.grid.Rows(i)("总费用")=t.grid.Rows(i)("总费用")+t.grid.Rows(j)("总费用")
End If
Next
End If
Next