Tables("订单开piao_Table1").StopRedraw
Dim Filter1 As String
With e.Form.Controls("起始日期")
If .Value IsNot Nothing Then
If Filter1 >"" Then
Filter1 = Filter1 & " And "
End If
Filter1 = Filter1 & "日期 < '" & .Value & "'"
End If
End With
Dim bd3 As New SQLGroupTableBuilder("统计表1","订单明细")
bd3.C
Dim dt3 As fxDataSource
bd3.Groups.AddDef("日期",DateGroupEnum.Year,"年")
bd3.Groups.AddDef("日期",DateGroupEnum.Month,"月")
bd3.Groups.AddDef("订单编号") '根据型号分组
bd3.Totals.AddDef("订单数量","订单数量") '对数量进行统计
bd3.Totals.AddDef("订单金额","订单金额") '对金额进行统计
bd3.Filter = Filter1
dt3 = bd3.BuildDataSource()
Dim bd4 As New SQLGroupTableBuilder("统计表1","fa piao明细")
bd4.C
Dim dt4 As fxDataSource
bd4.Groups.AddDef("日期",DateGroupEnum.Year,"年")
bd4.Groups.AddDef("日期",DateGroupEnum.Month,"月")
bd4.Groups.AddDef("订单编号") '根据型号分组
bd4.Totals.AddDef("fa piao数量","fa piao数量") '对数量进行统计
bd4.Totals.AddDef("合计金额","合计金额") '对金额进行统计
bd4.Filter = Filter1
dt4 = bd4.BuildDataSource()
dt3.Combine("订单编号",dt4,"订单编号") '将销售统计数据组合到进货统计数据
Tables("订单开piao_Table1").DataSource = dt3 '将统计结果绑定到Table
With DataTables("订单开piao_Table1").DataCols '用表达式列计算库存数据
.Add("期初数量",Gettype(Double), "IsNull([订单数量],0) - ISNULL([fa piao数量],0)","未开piao数量")
.Add("期初金额",Gettype(Double), "IsNull([订单金额],0) - ISNULL([合计金额],0)","未开piao金额")
End With
Dim Filter As String
With e.Form.Controls("起始日期")
If .Value IsNot Nothing Then
If Filter >"" Then
Filter = Filter & " And "
End If
Filter = Filter & "日期 >= '" & .Value & "'"
End If
End With
With e.Form.Controls("结束日期")
If .Value IsNot Nothing Then
If Filter >"" Then
Filter = Filter & " And "
End If
Filter = Filter & "日期 <= '" & .Value & "'"
End If
End With
Dim bd1 As New SQLGroupTableBuilder("统计表1","订单明细")
bd1.C
Dim dt1 As fxDataSource
bd1.Groups.AddDef("日期",DateGroupEnum.Year,"年")
bd1.Groups.AddDef("日期",DateGroupEnum.Month,"月")
bd1.Groups.AddDef("订单编号") '根据型号分组
bd1.Totals.AddDef("订单数量","订单数量") '对数量进行统计
bd1.Totals.AddDef("订单金额","订单金额") '对金额进行统计
bd1.Filter = Filter
dt1 = bd1.BuildDataSource()
Dim bd2 As New SQLGroupTableBuilder("统计表1","fa piao明细")
bd2.C
Dim dt2 As fxDataSource
bd2.Groups.AddDef("日期",DateGroupEnum.Year,"年")
bd2.Groups.AddDef("日期",DateGroupEnum.Month,"月")
bd2.Groups.AddDef("订单编号") '根据型号分组
bd2.Totals.AddDef("fa piao数量","fa piao数量") '对数量进行统计
bd2.Totals.AddDef("合计金额","合计金额") '对金额进行统计
bd2.Filter = Filter
dt2 = bd2.BuildDataSource()
dt1.Combine("订单编号",dt2,"订单编号") '将销售统计数据组合到进货统计数据'
dt1.Combine("订单编号",dt3,"订单编号") '将销售统计数据组合到进货统计数据
'
Tables("订单开piao_Table1").DataSource = dt1 '将统计结果绑定到Table
With DataTables("订单开piao_Table1").DataCols '用表达式列计算库存数据
.Add("未开piao数量",Gettype(Double), "IsNull([期初数量],0) + IsNull([订单数量],0) - ISNULL([fa piao数量],0)","未开piao数量")
.Add("未开piao金额",Gettype(Double), "IsNull([期初金额],0) + IsNull([订单金额],0) - ISNULL([合计金额],0)","未开piao金额")
End With
Tables("订单开piao_Table1").SetColVisibleWidth("年|60|月|40|订单编号|100|期初数量|90|期初金额|90|订单数量|90|订单金额|90|fa piao数量|90|合计金额|90|未开piao数量|90|未开piao金额|90")
Tables("订单开piao_Table1").DefaultRowHeight = 35
DataTables("订单开piao_Table1").DataCols("期初数量").SetFormat("#,###.00")
DataTables("订单开piao_Table1").DataCols("期初金额").SetFormat("#,###.00")
DataTables("订单开piao_Table1").DataCols("订单数量").SetFormat("#,###.00")
DataTables("订单开piao_Table1").DataCols("订单金额").SetFormat("#,###.00")
DataTables("订单开piao_Table1").DataCols("fa piao数量").SetFormat("#,###.00")
DataTables("订单开piao_Table1").DataCols("合计金额").SetFormat("#,###.00")
DataTables("订单开piao_Table1").DataCols("未开piao数量").SetFormat("#,###.00")
DataTables("订单开piao_Table1").DataCols("未开piao金额").SetFormat("#,###.00")
Tables("订单开piao_Table1").Cols("年").TextAlign = TextAlignEnum.Center
Tables("订单开piao_Table1").Cols("月").TextAlign = TextAlignEnum.Center
With Tables("订单开piao_Table1")
.MergeMode = MergeModeEnum.Standard '标准合并模式
.MergeCols.Clear() '清除原来的合并列
.MergeCols.Add("年") '加入要合并的列
.MergeCols.Add("月")
.MergeSort = "年,月" '设置合并模式下的排序方式
.AllowMerge = True '启用合并模式
.Cols("期初数量").GrandTotal = True
.Cols("期初金额").GrandTotal = True
.Cols("订单数量").GrandTotal = True
.Cols("订单金额").GrandTotal = True
.Cols("fa piao数量").GrandTotal = True
.Cols("合计金额").GrandTotal = True
.Cols("未开piao数量").GrandTotal = True
.Cols("未开piao金额").GrandTotal = True
.GrandTotal = True
End With
Tables("订单开piao_Table1").AutoSizeCols()
Tables("订单开piao_Table1").ResumeRedraw
加好友 
发短信
Post By：2015/7/21 19:26:00 [只看该作者]
