月结表的本月加权平均单价采用的是月加权平均法计算,截图中的本月期初金额≠上月结存金额导致月结表错误,【生成月结表】按钮代码如下,问题可能出在红色粗字,请老师指点。
此主题相关图片如下:snap1.jpg
![dvubb 图片点击可在新窗口打开查看](UploadFile/2016-10/201610297532943444.jpg)
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("本月出仓_金额")
Next
DataTables("材料月结").DeleteFor(" [材料名称] is null AND [型号规格] is null " )
DataTables("材料月结").DeleteFor(" 上月结存_数量 = 0 AND 本月入仓_数量 is null AND 本月出仓_数量 is null AND 本月结存_数量 = 0 " )
[此贴子已经被作者于2016/10/29 7:55:23编辑过]