专家坐堂-库存计算Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

Dim Filter , f3 As String

With e.Form.Controls("库位1")

If .Value IsNot Nothing Then

If Filter > "" Then

Filter = Filter & " And "

End If

Filter = Filter & "库位 = \'" & .Value & "\'"

f3 = "库位 = \'" & .Value & "\'"

End If

End With

With e.Form.Controls("成品编码1")

If .Value IsNot Nothing Then

If Filter > "" Then

Filter = Filter & " And "

End If

Filter = Filter & "成品编码 = \'" & .Value & "\'"

End If

End With

With e.Form.Controls("成品描述1")

If .Value IsNot Nothing Then

If Filter > "" Then

Filter = Filter & " And "

End If

Filter = Filter & "成品描述 = \'" & .Value & "\'"

End If

End With

With e.Form.Controls("规格型号1")

If .Value IsNot Nothing Then

If Filter > "" Then

Filter = Filter & " And "

End If

Filter = Filter & "规格型号 = \'" & .Value & "\'"

End If

End With

Dim m1 As String = DataTables("期初数据").SQLCompute("max(月)", Filter)

Dim dict As New Dictionary(Of String, Date)

For Each y As String In DataTables("月末结账记录").SQLGetValues("库位", f3)

Dim d As Date = DataTables("月末结账记录").SQLCompute("max(结账期止)", "库位=\'" & y & "\'")

dict.Add(y, d)

Dim f2 As String = "1=2 "

For Each key As String In dict.Keys

f2 = f2 & "or ( 库位 =\'" & key & "\' and 单据日期 > \'" & dict(key) & "\')"

f2 = "(" & f2 & ") "

With e.Form.Controls("成品编码1")

If .Value IsNot Nothing Then

If f2 > "" Then

f2 = f2 & " And "

End If

f2 = f2 & "成品编码 = \'" & .Value & "\'"

End If

End With

With e.Form.Controls("成品描述1")

If .Value IsNot Nothing Then

If f2 > "" Then

f2 = f2 & " And "

End If

f2 = f2 & "成品描述 = \'" & .Value & "\'"

End If

End With

With e.Form.Controls("规格型号1")

If .Value IsNot Nothing Then

If f2 > "" Then

f2 = f2 & " And "

End If

f2 = f2 & "规格型号 = \'" & .Value & "\'"

End If

End With

Dim bd3 As New GroupTableBuilder("统计表3", DataTables("期初数据"))

Dim dt3 As fxDataSource

bd3.Groups.AddDef("库位") \'根据成品编码分组

bd3.Groups.AddDef("成品编码") \'根据成品编码分组

bd3.Groups.AddDef("成品描述") \'根据成品编码分组

bd3.Groups.AddDef("规格型号") \'根据成品编码分组

bd3.Totals.AddDef("期初数量", "上月结存") \'对数量进行统计

bd3.FromServer = True

bd3.Filter = "月=\'" & m1 & "\'" & IIf(filter > "", " and ", "") & filter

dt3 = bd3.BuildDataSource()

Dim bd1 As New GroupTableBuilder("统计表1", DataTables("出入库明细"))

Dim dt1 As fxDataSource

bd1.Groups.AddDef("库位") \'根据成品编码分组

bd1.Groups.AddDef("成品编码") \'根据成品编码分组

bd1.Groups.AddDef("成品描述") \'根据成品编码分组

bd1.Groups.AddDef("规格型号") \'根据成品编码分组

bd1.Totals.AddDef("入库数量", "入库数量") \'对数量进行统计

bd1.FromServer = True

bd1.Filter = f2

dt1 = bd1.BuildDataSource()

Dim bd2 As New GroupTableBuilder("统计表2", DataTables("出入库明细"))

Dim dt2 As fxDataSource

bd2.Groups.AddDef("库位") \'根据成品编码分组

bd2.Groups.AddDef("成品编码") \'根据成品编码分组

bd2.Groups.AddDef("成品描述") \'根据成品编码分组

bd2.Groups.AddDef("规格型号") \'根据成品编码分组

bd2.Totals.AddDef("出库数量", "出库数量") \'对数量进行统计

bd2.FromServer = True

bd2.Filter = f2

dt2 = bd2.BuildDataSource()

Dim nms() As String = {"库位", "成品编码", "成品描述", "规格型号"}

dt3 = bd3.BuildDataSource()

dt1.Combine(nms, dt2, nms) \'将销售统计数据组合到进货统计数据

dt1.Combine(nms, dt3, nms) \'将退货统计数据组合到进货统计数据

Tables("设备备件库存_现存量").DataSource = dt1

With DataTables("设备备件库存_现存量").DataCols \'用表达式列计算库存数据

.Add("库存数量", GetType(Integer), "IsNull([上月结存],0) + ISNULL([入库数量],0) - ISNULL([出库数量],0)")

End With