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)
Next
Dim f2 As String = "1=2 "
For Each key As String In dict.Keys
f2 = f2 & "or ( 库位 ='" & key & "' and 单据日期 > '" & dict(key) & "')"
Next
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
加好友 
发短信
Post By：2023/7/10 8:21:00 [显示全部帖子]
