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

\'------设置时段统计--------

Dim sd As WinForm.DateTimePicker

Dim ed As WinForm.DateTimePicker

sd = e.Form.Controls("起始日期")

ed = e.Form.Controls("终止日期")

If sd.Value Is Nothing OrElse ed.Value Is Nothing Then

MessageBox.Show("请输入起始日期和终止日期!","提示", MessageBoxButtons.OK ,MessageBoxIcon.Information)

e.Cancel = True

End If

output.Show(sd.Value)

output.Show(ed.Value)

Dim f As String

f = "日期 >= #" & sd.Value & "# And 日期 < #" & ed.Value.AddDays(1) & "#"

MessageBox.Show(F)

Dim g1 As New CrossTableBuilder("结存统计", DataTables("上月结存"))

Dim f1 As fxDataSource

g1.HGroups.AddDef("款号")

g1.VGroups.AddDef("尺码")

g1.Totals.AddDef("数量", "结存_数量")

g1.FromServer = True

g1.Filter=f

g1.Build()

\'MainTable = Tables("结存统计")

f1 =g1.BuildDataSource()

Dim g2 As New CrossTableBuilder("入库统计", DataTables("本月入库"))

Dim f2 As fxDataSource

g2.HGroups.AddDef("款号")

g2.VGroups.AddDef("尺码")

g2.Totals.AddDef("数量", "入库_数量")

g2.FromServer = True

g2.Filter=f

g2.Build()

\'MainTable = Tables("入库统计")

f2 =g2.BuildDataSource()

Dim g3 As New CrossTableBuilder("出库统计", DataTables("本月出库"))

Dim f3 As fxDataSource

g3.HGroups.AddDef("款号")

g3.VGroups.AddDef("尺码")

g3.Totals.AddDef("数量", "出库_数量")

g3.FromServer = True

g3.Filter=f

g3.Build()

\'MainTable = Tables("出库统计")

f3 =g3.BuildDataSource()

Dim g4 As New CrossTableBuilder("销售统计", DataTables("本月销售"))

Dim f4 As fxDataSource

g4.HGroups.AddDef("款号")

g4.VGroups.AddDef("尺码")

g4.Totals.AddDef("数量", "销售_数量")

g4.FromServer = True

g4.Filter=f

g4.Build()

\'MainTable = Tables("销售统计")

f4 =g4.BuildDataSource()

f1.Combine("款号",f2,"款号") \'将入库统计数据组合到结存统计数据

f1.Combine("款号",f3,"款号") \'将出库统计数据组合到结存统计数据

f1.Combine("款号",f4,"款号") \'将销售统计数据组合到结存统计数据

Tables("统计窗口_Table1").DataSource = f1 \'将统计结果绑定到Table

Dim s As String

For i As Integer=1 To Tables("统计窗口_Table1").Cols.count-1

s= s & "IsNull([" & Tables("统计窗口_Table1").Cols(i).name & "],0) +"

MessageBox.show(s)

With DataTables("统计窗口_Table1").DataCols \'用表达式列计算库存数据

.Add("库存_总量",Gettype(Integer), s.trim("+"))

Dim str As String = ""

If .Contains("数量_1") Then

str += "IsNull([数量_1],0)+"

End If

If .Contains("数量_11") Then

str += "IsNull([数量_11],0)+"

End If

If .Contains("数量_12") Then

str += "IsNull([数量_12],0)+"

End If

If .Contains("数量_13") Then

str += "IsNull([数量_13],0) +"

End If

.Add("库存_L",Gettype(Integer), str.Trim("+"))

If .Contains("数量_2") Then

str += "IsNull([数量_2],0)+"

End If

If .Contains("数量_21") Then

str += "IsNull([数量_21],0)+"

End If

If .Contains("数量_22") Then

str += "IsNull([数量_22],0)+"

End If

If .Contains("数量_23") Then

str += "IsNull([数量_23],0) +"

End If

.Add("库存_M",Gettype(Integer), str.Trim("+"))

If .Contains("数量_3") Then

str += "IsNull([数量_3],0)+"

End If

If .Contains("数量_31") Then

str += "IsNull([数量_31],0)+"

End If

If .Contains("数量_32") Then

str += "IsNull([数量_32],0)+"

End If

If .Contains("数量_33") Then

str += "IsNull([数量_33],0) +"

End If

.Add("库存_S",Gettype(Integer), str.Trim("+"))

If .Contains("数量_4") Then

str += "IsNull([数量_4],0)+"

End If

If .Contains("数量_41") Then

str += "IsNull([数量_41],0)+"

End If

If .Contains("数量_42") Then

str += "IsNull([数量_42],0)+"

End If

If .Contains("数量_43") Then

str += "IsNull([数量_43],0) +"

End If

.Add("库存_XL",Gettype(Integer), str.Trim("+"))

End With

\'Tables("统计窗口_Table1").SysStyles("Alternate").BackColor = Color.pink

Tables("统计窗口_Table1").SetColVisibleWidth("款号|100|库存_S|60|库存_M|60|库存_L|60|库存_XL|60|库存_总量|70")