以下是生成月结表的代码
DataTables("材料月结").DataRows.Clear
Dim Arys As List(Of String())
Arys = DataTables("材料明细").GetValues("月|材料分类|材料名称|型号规格","月 >= '" & e.Form.Controls("起始月").Text & "' And 月 <= '" & e.Form.Controls("截止月").Text & "' ")
For Each Ary As String() In Arys
Dim r As Row = Tables("材料月结").AddNew
r("月") = Ary(0)
r("材料分类") = Ary(1)
r("材料名称") = Ary(2)
r("型号规格") = Ary(3)
Next
Dim dt As DataTable = DataTables("材料明细")
For Each dr As DataRow In DataTables("材料月结").DataRows
Dim Filter As String = "材料分类 = '" & dr("材料分类") & "' and 材料名称 = '" & dr("材料名称") & "' and 型号规格 = '" & dr("型号规格") & "'"
Dim Sum,Sum1 As Double
dr("本月入仓_数量") = dt.Compute("Sum(入仓数量)",Filter & " And 月 = '" & dr("月") & "'")
dr("本月入仓_金额") = dt.Compute("Sum(入仓金额)",Filter & " And 月 = '" & dr("月") & "'")
dr("本月出仓_数量") = dt.Compute("Sum(出仓数量)",Filter & " And 月 = '" & dr("月") & "'")
dr("上月结存_数量") = dt.Compute("Sum(入仓数量)",Filter & " And 月 < '" & dr("月") & "'") - dt.Compute("Sum(出仓数量)",Filter & " And 月 < '" & dr("月") & "'")
Sum = DataTables("材料明细").Compute("Sum(入仓金额)",Filter & " And 月 < '" & dr("月") & "'")
Sum1 = DataTables("材料明细").Compute("Sum(入仓数量)",Filter & " And 月 < '" & dr("月") & "'")
dr("上月结存_金额") = Sum / Sum1 * dr("上月结存_数量")
dr("本月结存_数量") = dr("上月结存_数量") + dr("本月入仓_数量") - dr("本月出仓_数量")
dr("本月加权平均单价") = (dr("上月结存_金额") + dr("本月入仓_金额"))/(dr("上月结存_数量") + dr("本月入仓_数量"))
dr("本月出仓_金额") = dr("本月加权平均单价") * dr("本月出仓_数量")
dr("本月结存_金额") = dr("本月加权平均单价") * dr("本月结存_数量")
'dr("本月结存_金额") = dr("上月结存_金额") + dr("本月入仓_金额") - dr("本月出仓_金额")
Next