Dim Filter , f3 As String
With e.Form.Controls("库位2")
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
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("期初数量_A类","上月结存_A类") '对数量进行统计
bd3.Totals.AddDef("期初数量_B类","上月结存_B类") '对数量进行统计
bd3.FromServer = True
bd3.Filter = "月='" & m1 & "'" & iif(filter > ""," and ","") & filter
dt3 = bd3.BuildDataSource()
Dim bd4 As New GroupTableBuilder("统计表2",DataTables("出入库明细"))
Dim dt4 As fxDataSource
bd4.Groups.AddDef("库位") '根据成品编码分组
bd4.Groups.AddDef("成品编码") '根据成品编码分组
bd4.Groups.AddDef("成品描述") '根据成品编码分组
bd4.Groups.AddDef("生产型号") '根据成品编码分组
bd4.Filter = f2 & "and 入库类型= 'A类'"
bd4.Totals.AddDef("入库数量","入库数量_A类") '对数量进行统计
bd4.FromServer = True
dt4 = bd4.BuildDataSource()
Dim bd5 As New GroupTableBuilder("统计表7",DataTables("出入库明细"))
Dim dt5 As fxDataSource
bd5.Groups.AddDef("库位") '根据成品编码分组
bd5.Groups.AddDef("成品编码") '根据成品编码分组
bd5.Groups.AddDef("成品描述") '根据成品编码分组
bd5.Groups.AddDef("生产型号") '根据成品编码分组
bd5.Filter = f2 & "and 入库类型='B类'"
bd5.Totals.AddDef("入库数量","入库数量_B类") '对数量进行统计
bd5.FromServer = True
dt5 = bd5.BuildDataSource()
Dim bd6 As New GroupTableBuilder("统计表1",DataTables("出入库明细"))
Dim dt6 As fxDataSource
bd6.Groups.AddDef("库位") '根据成品编码分组
bd6.Groups.AddDef("成品编码") '根据成品编码分组
bd6.Groups.AddDef("成品描述") '根据成品编码分组
bd6.Groups.AddDef("生产型号") '根据成品编码分组
bd6.Filter = f2 & "and 出库类型 = 'A类'"
bd6.Totals.AddDef("出库数量","出库数量_A类") '对数量进行统计
bd6.FromServer = True
dt6 = bd6.BuildDataSource()
Dim bd7 As New GroupTableBuilder("统计表5",DataTables("出入库明细"))
Dim dt7 As fxDataSource
bd7.Groups.AddDef("库位") '根据成品编码分组
bd7.Groups.AddDef("成品编码") '根据成品编码分组
bd7.Groups.AddDef("成品描述") '根据成品编码分组
bd7.Groups.AddDef("生产型号") '根据成品编码分组
bd7.Filter = f2 & "and 出库类型 = 'B类'"
bd7.Totals.AddDef("出库数量","出库数量_B类") '对数量进行统计
bd7.FromServer = True
dt7 = bd7.BuildDataSource()
Dim nms() As String = {"库位","成品编码","成品描述","生产型号"}
dt3 = bd3.BuildDataSource()
dt4.Combine(nms,dt3,nms) '将退货统计数据组合到进货统计数据
dt4.Combine(nms,dt5,nms) '将退货统计数据组合到进货统计数据
dt4.Combine(nms,dt6,nms) '将退货统计数据组合到进货统计数据
dt4.Combine(nms,dt7,nms) '将退货统计数据组合到进货统计数据
Tables("仓库现存量_现存量1").DataSource = dt4
With DataTables("仓库现存量_现存量1").DataCols '用表达式列计算库存数据
.Add("库存数量_A类",Gettype(Integer), "IsNull([上月结存_A类],0) + ISNULL([入库数量_A类],0) - ISNULL([出库数量_A类],0)")
.Add("库存数量_B类",Gettype(Integer), "IsNull([上月结存_B类],0) + ISNULL([入库数量_B类],0) - ISNULL([出库数量_B类],0)")
End With