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

以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.com/bbs/index.asp)
--  专家坐堂  (http://foxtable.com/bbs/list.asp?boardid=2)
----  统计明细  (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=157576)

--  作者：yetle
--  发布时间：2020/10/21 14:11:00
--  统计明细

想统计每个款不同颜色尺码的入库、出库、退货的明细记录（不是合计），应该是怎么改，下面这么改不行

Dim ddh As WinForm.ComboBox = e.Form.Controls("订单号")

Dim kh As WinForm.ComboBox = e.Form.Controls("款号")
Dim ys As WinForm.ComboBox = e.Form.Controls("颜色")
Dim cm As WinForm.ComboBox = e.Form.Controls("尺码")

Dim dtt1 As WinForm.DateTimePicker = e.Form.Controls("开始日期")
Dim dtt2 As WinForm.DateTimePicker = e.Form.Controls("结束日期")

Dim filter As String = " _identify>0 "

If ddh.Text > "" Then
filter= filter & " and 订单号 = \'" & ddh.Text & "\'"
End If

If kh.Text > "" Then
filter= filter & " And 款号 = \'" & kh.Text & "\'"
End If

If ys.Text > "" Then
filter= filter & " And 颜色 = \'" & ys.Text & "\'"
End If

If cm.Text > "" Then
filter= filter & " And 尺码 = \'" & cm.Text & "\'"
End If

Dim filter1 As String
filter1= filter & " And 工序号 = 104 And 单据状态 = \'已完成\' "

Dim bd1 As New SQLGroupTableBuilder("统计表1","派发工序表")
bd1.C
Dim dt1 As fxDataSource
bd1.Groups.AddDef("订单号") \'根据型号分组
bd1.Groups.AddDef("制单号") \'根据型号分组
bd1.Groups.AddDef("款号") \'根据型号分组
bd1.Groups.AddDef("颜色") \'根据型号分组
bd1.Groups.AddDef("尺码") \'根据型号分组
bd1.Groups.AddDef("成品入库") \'根据型号分组
\'bd1.Totals.AddDef("成品入库","入库_数量") \'对数量进行统计
bd1.Filter = filter1
dt1 = bd1.BuildDataSource()

Dim bd2 As New SqlGroupTableBuilder("统计表2","销售发货明细表")
bd2.C
Dim dt2 As fxDataSource
bd2.Groups.AddDef("订单号") \'根据型号分组
bd2.Groups.AddDef("制单号") \'根据型号分组
bd2.Groups.AddDef("款号") \'根据型号分组
bd2.Groups.AddDef("颜色") \'根据型号分组
bd2.Groups.AddDef("尺码") \'根据型号分组
bd2.Groups.AddDef("发货数量") \'根据型号分组
\'bd2.Totals.AddDef("发货数量","出库_数量") \'对数量进行统计
bd2.Filter = filter
dt2 = bd2.BuildDataSource()

Dim bd3 As New SqlGroupTableBuilder("统计表3","成品退货明细表")
bd3.C
Dim dt3 As fxDataSource
bd3.Groups.AddDef("订单号") \'根据型号分组
bd3.Groups.AddDef("制单号") \'根据型号分组
bd3.Groups.AddDef("款号") \'根据型号分组
bd3.Groups.AddDef("颜色") \'根据型号分组
bd3.Groups.AddDef("尺码") \'根据型号分组
bd3.Groups.AddDef("退货数量") \'根据型号分组
\'bd3.Totals.AddDef("退货数量","退货_数量") \'对数量进行统计
bd3.Filter = filter
dt3 = bd3.BuildDataSource()

Dim nms As String() = {"订单号","制单号","款号","颜色","尺码"}
dt1.Combine(nms,dt2,nms) \'将销售统计数据组合到进货统计数据
Dim nmc As String() = {"订单号","制单号","款号","颜色","尺码"}
dt1.Combine(nmc,dt3,nmc) \'将销售统计数据组合到进货统计数据

Tables("出入明细表_Table1").DataSource = dt1 \'将统计结果绑定到Table
With DataTables("出入明细表_Table1").DataCols \'用表达式列计算库存数据
.Add("库存数量",Gettype(Integer), "IsNull([成品入库],0) - ISNULL([发货数量],0) + ISNULL([退货数量],0) ")
End With

Tables("出入明细表_Table1").Cols("库存数量").GrandTotal = True \'指定要合计的列
Tables("出入明细表_Table1").GrandTotal = True

--  作者：有点蓝
--  发布时间：2020/10/21 14:33:00
--

在Totals集合中添加一个统计列的语法如下：　
Totals.AddDef(Name)
Totals.AddDef(Name, NewName)
Totals.AddDef(Name, NewName, NewCaption)
Totals.AddDef(Name, Aggregate)
Totals.AddDef(Name, Aggregate, NewName)
Totals.AddDef(Name, Aggregate, NewName, NewCaption)
参数说明　
Name:        字符型，指定统计列。
NewName:     字符型，用于给统计列指定一个新的名称，这个名称在统计表中显示。
NewCaption: 字符型，用于给统计列设置标题。
Aggregate： AggregateEnum枚举型，用于指定统计类型，默认是求和。
AggregateEnum型枚举的可选值：
Average：平均值
Count：记录数
Max：    最大值
Min：    最小值
Sum：    累积值

bd3.Totals.AddDef("订单号"，AggregateEnum.Count,"退货_数量")

[此贴子已经被作者于2020/10/21 14:33:33编辑过]

--  作者：yetle
--  发布时间：2020/10/27 9:26:00
--

老师，按您的写法，弹出这个提示
.NET Framework 版本：4.0.30319.18063
Foxtable 版本：2020.5.1.8
错误所在事件：窗口,出入明细表,Button5,Click
详细错误信息：
An item with the same key has already been added.

--  作者：有点蓝
--  发布时间：2020/10/27 9:30:00
--
贴出完整代码，或者上传实例说明

--  作者：yetle
--  发布时间：2020/10/27 11:16:00
--

Dim ddh As WinForm.ComboBox = e.Form.Controls("订单号")

Dim kh As WinForm.ComboBox = e.Form.Controls("款号")
Dim ys As WinForm.ComboBox = e.Form.Controls("颜色")
Dim cm As WinForm.ComboBox = e.Form.Controls("尺码")

Dim dtt1 As WinForm.DateTimePicker = e.Form.Controls("开始日期")
Dim dtt2 As WinForm.DateTimePicker = e.Form.Controls("结束日期")

Dim filter As String = " _identify>0 "

If ddh.Text > "" Then
filter= filter & " and 订单号 = \'" & ddh.Text & "\'"
End If

If kh.Text > "" Then
filter= filter & " And 款号 = \'" & kh.Text & "\'"
End If

If ys.Text > "" Then
filter= filter & " And 颜色 = \'" & ys.Text & "\'"
End If

If cm.Text > "" Then
filter= filter & " And 尺码 = \'" & cm.Text & "\'"
End If

\'If dtt1.Text > "" Then
\'filter= filter & " And 扫码时间 >= \'" & dtt1.Text & "\'"
\'End If
\'
\'If dtt2.Text > "" Then
\'filter= filter & " And 扫码时间 <= \'" & dtt2.Text & "\'"
\'End If

\'Dim ddhc As WinForm.ComboBox = e.form.Controls("ComboBox7")
\'Dim ddh As String = ddhc.text
Dim filter1 As String
filter1= filter & " And 工序号 = 104 And 单据状态 = \'已完成\' "

Dim bd1 As New SQLGroupTableBuilder("统计表1","派发工序表")
bd1.C
Dim dt1 As fxDataSource
bd1.Groups.AddDef("订单号") \'根据型号分组
bd1.Groups.AddDef("制单号") \'根据型号分组
bd1.Groups.AddDef("款号") \'根据型号分组
bd1.Groups.AddDef("颜色") \'根据型号分组
bd1.Groups.AddDef("尺码") \'根据型号分组

bd1.Totals.AddDef("订单号",AggregateEnum.Count,"入库_数量")
bd1.Filter = filter1
dt1 = bd1.BuildDataSource()

Dim bd2 As New SqlGroupTableBuilder("统计表2","销售发货明细表")
bd2.C
Dim dt2 As fxDataSource
bd2.Groups.AddDef("订单号") \'根据型号分组
bd2.Groups.AddDef("制单号") \'根据型号分组
bd2.Groups.AddDef("款号") \'根据型号分组
bd2.Groups.AddDef("颜色") \'根据型号分组
bd2.Groups.AddDef("尺码") \'根据型号分组

bd2.Totals.AddDef("订单号",AggregateEnum.Count,"出库_数量")
bd2.Filter = filter
dt2 = bd2.BuildDataSource()

Dim bd3 As New SqlGroupTableBuilder("统计表3","成品退货明细表")
bd3.C
Dim dt3 As fxDataSource
bd3.Groups.AddDef("订单号") \'根据型号分组
bd3.Groups.AddDef("制单号") \'根据型号分组
bd3.Groups.AddDef("款号") \'根据型号分组
bd3.Groups.AddDef("颜色") \'根据型号分组
bd3.Groups.AddDef("尺码") \'根据型号分组

bd3.Totals.AddDef("订单号",AggregateEnum.Count,"退货_数量")
bd3.Filter = filter
dt3 = bd3.BuildDataSource()

这样写，每个统计表可以显示明细了，我想将这三个表的显示明细都绑定到Tables("出入明细表_Table1")怎么写？

--  作者：有点蓝
--  发布时间：2020/10/27 11:28:00
--
这个就是啊

Dim nms As String() = {"订单号","制单号","款号","颜色","尺码"}
dt1.Combine(nms,dt2,nms) \'将销售统计数据组合到进货统计数据
Dim nmc As String() = {"订单号","制单号","款号","颜色","尺码"}
dt1.Combine(nmc,dt3,nmc) \'将销售统计数据组合到进货统计数据

Tables("出入明细表_Table1").DataSource = dt1 \'将统计结果绑定到Table