|
Forms("统计").Open()
Dim et As Date = Forms("统计").Controls("ETime1").value
Dim st As Date = Forms("统计").Controls("STime1").value
Dim str As String = Format(st,"yyyy-MM-dd") & "---" & Format(et,"yyyy-MM-dd")
Dim b As New SQLGroupTableBuilder("统计表1","例子")
b.Groups.AddDef("产品")
b.Totals.AddDef("入库", "入库", str & "_入库")
b.Totals.AddDef("出库", "出库", str & "_出库")
b.Totals.AddDef("库存", "库存", str & "_截止库存")
b.Totals.AddExp("期初库存","入库",""& str &"_期初库存")
b.filter = "日期 >= #" & st &"# And 日期 <= #"& et &"#"
b.Build
Tables("统计表1").Cols("期初库存").Move(1)
For Each cp As String In DataTables("例子").GetValues("产品", "产品 not in ('"& DataTables("统计表1").GetComboListString("产品").Replace("|", "','") & "')")
Dim nr As Row = Tables("统计表1").AddNew
nr("产品") = cp
Next
For Each r As Row In Tables("统计表1").Rows
Dim filter = " 日期 <= #"& et &"# and 产品 = '" & r("产品") & "'"
Dim kc As Double = DataTables("例子").Compute("sum(入库)",filter) - DataTables("例子").Compute("sum(出库)",filter)
r("库存") = kc
r("期初库存") = kc + r("出库") - r("入库")
Next
MainTable = Tables("统计表1")
Tables("统计表1").AutoSizeCols()
DataTables("统计表1").Save()
|
加好友 
发短信
Post By：2015/3/5 20:32:00 [显示全部帖子]
