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

以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.net/bbs/index.asp)
--  专家坐堂  (http://foxtable.net/bbs/list.asp?boardid=2)
----  [求教]代码优化  (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=135616)

--  作者：hcfbehero
--  发布时间：2019/5/30 11:43:00
--  [求教]代码优化
甜版，您之前帮我做的这个代码，我弄半天没把他放到我的窗口临时表里去，请您帮我修改下，将统计表1放到窗口表10，将统计表2放到窗口表11里，还有就是将统计表1里前段的重复值（行）那些显示为空呢，比如：销档，客户名称等这些列的重复行，麻烦您了，谢谢！
Dim ny As WinForm.ComboBox = e.Form.Controls("ComboBox10")

Dim g1 As New GroupTableBuilder("统计表1", DataTables("明细根表"))
Dim dt1 As fxDataSource
g1.Groups.AddDef("运输审核_结算月", "结算月")
g1.Groups.AddDef("销售审核_销档", "销档")
g1.Groups.AddDef("销售名称", "客户名称")
g1.Groups.AddDef("销售审核_吉", "吉")
g1.Groups.AddDef("销售审核_开票", "票")
g1.Totals.AddDef("销", AggregateEnum.Count,"销")
g1.Totals.AddDef("生产信息_类型", AggregateEnum.Count, "砼车数")
g1.Totals.AddDef("销量", "总方量")
g1.Totals.AddDef("销售_砼销额", "砼金额")
g1.Totals.AddDef("GDBFJE", "运输补助")
g1.Totals.AddDef("GDBCJE", "超时补助")
g1.Totals.AddDef("工地补助_工补其", "其他费用")
g1.Totals.AddDef("月调整_方量")
g1.Totals.AddDef("月调整_金额")
g1.VerticalTotal = True
g1.Filter = "[运输审核_结算月] = \'" & ny.text & "\' and [销] = \'X\' and [生产信息_类型] = \'砼\'"
\'g1.Build
dt1 = g1.BuildDataSource()
\'
\'
Dim g2 As New GroupTableBuilder("统计表1", DataTables("明细根表"))
Dim dt2 As fxDataSource
g2.Groups.AddDef("运输审核_结算月", "结算月")
g2.Groups.AddDef("销售名称", "客户名称")
g2.Totals.AddDef("生产信息_类型", AggregateEnum.Count, "水")
g2.Totals.AddDef("销售_水销额", "水金额")
g2.VerticalTotal = True
g2.Filter = "[运输审核_结算月] = \'" & ny.text & "\' and [生产信息_类型] = \'水\'"
dt2 = g2.BuildDataSource()
\'g2.build

\'
Dim g3 As New GroupTableBuilder("统计表2", DataTables("泵送明细"))
Dim dt3 As fxDataSource
g3.Groups.AddDef("财务结算年月")
g3.Groups.AddDef("销售工地名称")
g3.Totals.AddDef("结算_销售","泵送费用")
g3.VerticalTotal = True
g3.Filter = "[财务结算年月] = \'" & ny.text & "\'"
dt3 = g3.BuildDataSource()

\'

Dim ar1() As String = {"结算月","客户名称"}
Dim ar2() As String = {"结算月","客户名称"}
Dim ar3() As String = {"财务结算年月","销售工地名称"}
\'Dim ar4() As String = {"结算月","客户名称"}
\'
\'
dt1.Combine(ar1,dt2,ar2)
dt1.Combine(ar1,dt3,ar3)
\'dt1.Combine(ar1,dt4,ar4)
\'
dt1.show("统计表1")

Dim g4 As New GroupTableBuilder("统计表2", DataTables("明细根表"))
Dim dt4 As fxDataSource
g4.Groups.AddDef("运输审核_结算月", "结算月")
g4.Groups.AddDef("销售名称", "客户名称")
g4.Groups.AddDef("销售标号")
g4.Totals.AddDef("销售_砼销价", AggregateEnum.Average, "单价")
g4.Totals.AddDef("销量", "方/车")
g4.VerticalTotal = True
g4.Filter = "[运输审核_结算月] = \'" & ny.text & "\' and [销] = \'X\' and [销售标号] is not Null"
\'dt4 = g4.BuildDataSource()
g4.build

Dim t1 As Table = Tables("统计表1")
Dim t2 As Table = Tables("统计表2")
t1.StopRedraw
For i As Integer = 2 To t2.Cols.count-1
t1.DataTable.DataCols.Add(t2.Cols(i).name, Gettype(String), 32, "", t2.Cols(i).caption)
Next
For i As Integer = 0 To t1.Rows.count-2
Dim r1 As Row = t1.Rows(i)
Dim nr1 As Row = Nothing
For Each dr2 As DataRow In t2.DataTable.Select("结算月=\'" & r1("结算月") & "\' and 客户名称=\'" & r1("客户名称") & "\'")
If nr1 Is Nothing Then
nr1 = r1
Else
nr1 = t1.addnew
End If
For Each c As Col In t1.Cols
nr1(c.name) = r1(c.name)
Next
For c As Integer = 2 To t2.Cols.count-1
nr1(t2.cols(c).name) = dr2(t2.cols(c).name)
Next
Next
Next
t1.ResumeRedraw
t1.sort = "结算月,客户名称"
MainTable = t1

--  作者：有点甜
--  发布时间：2019/5/30 11:50:00
--

1、参考 http://www.foxtable.com/webhelp/scr/1909.htm

2、循环每一行，比较，清空，如

Dim t As Table = Tables("表A")
Dim pr As Row = Nothing
For Each r As Row In t.Rows
    If pr IsNot Nothing AndAlso r("第一列") = pr("第一列") AndAlso r("第二列") = pr("第二列") Then
        r("第一列") = Nothing
        r("第二列") = Nothing
    End If
    pr = r
Next

--  作者：hcfbehero
--  发布时间：2019/6/4 16:44:00
--
甜版所做第2点，在只有2行及以下重复的话，就能实现，但要是三行级以上重复的话，就只会两行两行比，请甜版解决三行及以上重复的

--  作者：有点甜
--  发布时间：2019/6/4 16:50:00
--

以下是引用hcfbehero在2019/6/4 16:44:00的发言：
甜版所做第2点，在只有2行及以下重复的话，就能实现，但要是三行级以上重复的话，就只会两行两行比，请甜版解决三行及以上重复的

不理解你的问题。上传具体实例，说明你要做到什么效果。

--  作者：hcfbehero
--  发布时间：2019/6/5 9:10:00
--
第一图是原代码的输出结果

此主题相关图片如下：20190605001.png

第二图您帮忙优化后进行比较输出的结果

此主题相关图片如下：20190605002.png

第三图是我想要的结果

此主题相关图片如下：20190605003.jpg

[此贴子已经被作者于2019/6/5 9:10:30编辑过]

--  作者：有点蓝
--  发布时间：2019/6/5 10:38:00
--
贴出优化后的代码看看

--  作者：hcfbehero
--  发布时间：2019/6/5 11:18:00
--

Dim ny As WinForm.ComboBox = e.Form.Controls("ComboBox10")

Dim g1 As New GroupTableBuilder("统计表1", DataTables("明细根表"))
Dim dt1 As fxDataSource
g1.Groups.AddDef("运输审核_结算月", "结算月")
g1.Groups.AddDef("销售审核_销档", "销档")
g1.Groups.AddDef("销售名称", "客户名称")
g1.Groups.AddDef("销售审核_吉", "吉")
g1.Groups.AddDef("销售审核_开票", "票")
g1.Totals.AddDef("销", AggregateEnum.Count,"销")
g1.Totals.AddDef("生产信息_类型", AggregateEnum.Count, "砼车数")
g1.Totals.AddDef("销量", "总方量")
g1.Totals.AddDef("销售_砼销额", "砼金额")
g1.Totals.AddDef("GDBFJE", "运输补助")
g1.Totals.AddDef("GDBCJE", "超时补助")
g1.Totals.AddDef("工地补助_工补其", "其他费用")
g1.Totals.AddDef("月调整_方量")
g1.Totals.AddDef("月调整_金额")
g1.VerticalTotal = True
g1.Filter = "[运输审核_结算月] = \'" & ny.text & "\' and [销] = \'X\' and [生产信息_类型] = \'砼\'"
\'g1.Build
dt1 = g1.BuildDataSource()
\'
\'
Dim g2 As New GroupTableBuilder("统计表1", DataTables("明细根表"))
Dim dt2 As fxDataSource
g2.Groups.AddDef("运输审核_结算月", "结算月")
g2.Groups.AddDef("销售名称", "客户名称")
g2.Totals.AddDef("生产信息_类型", AggregateEnum.Count, "水")
g2.Totals.AddDef("销售_水销额", "水金额")
g2.VerticalTotal = True
g2.Filter = "[运输审核_结算月] = \'" & ny.text & "\' and [生产信息_类型] = \'水\'"
dt2 = g2.BuildDataSource()
\'g2.build

\'
Dim g3 As New GroupTableBuilder("统计表2", DataTables("泵送明细"))
Dim dt3 As fxDataSource
g3.Groups.AddDef("财务结算年月")
g3.Groups.AddDef("销售工地名称")
g3.Totals.AddDef("结算_销售","泵送费用")
g3.VerticalTotal = True
g3.Filter = "[财务结算年月] = \'" & ny.text & "\'"
dt3 = g3.BuildDataSource()

\'

Dim ar1() As String = {"结算月","客户名称"}
Dim ar2() As String = {"结算月","客户名称"}
Dim ar3() As String = {"财务结算年月","销售工地名称"}
\'Dim ar4() As String = {"结算月","客户名称"}
\'
\'
dt1.Combine(ar1,dt2,ar2)
dt1.Combine(ar1,dt3,ar3)
\'dt1.Combine(ar1,dt4,ar4)
\'
Tables("明细根表_Table10").DataSource = dt1

Dim g4 As New GroupTableBuilder("统计表2", DataTables("明细根表"))
Dim dt4 As fxDataSource
g4.Groups.AddDef("运输审核_结算月", "结算月")
g4.Groups.AddDef("销售名称", "客户名称")
g4.Groups.AddDef("销售标号")
g4.Totals.AddDef("销售_砼销价", AggregateEnum.Average, "单价")
g4.Totals.AddDef("销量", "方/车")
g4.VerticalTotal = True
g4.Filter = "[运输审核_结算月] = \'" & ny.text & "\' and [销] = \'X\' and [销售标号] is not Null"
dt4 = g4.BuildDataSource()
\'g4.build
Tables("明细根表_Table11").DataSource = dt4

Dim t1 As Table = Tables("明细根表_Table10")
Dim t2 As Table = Tables("明细根表_Table11")
t1.StopRedraw
For i As Integer = 2 To t2.Cols.count-1
t1.DataTable.DataCols.Add(t2.Cols(i).name, Gettype(String), 32, "", t2.Cols(i).caption)
Next
For i As Integer = 0 To t1.Rows.count-2
Dim r1 As Row = t1.Rows(i)
Dim nr1 As Row = Nothing
For Each dr2 As DataRow In t2.DataTable.Select("结算月=\'" & r1("结算月") & "\' and 客户名称=\'" & r1("客户名称") & "\'")
If nr1 Is Nothing Then
nr1 = r1
Else
nr1 = t1.addnew
End If
For Each c As Col In t1.Cols
nr1(c.name) = r1(c.name)
Next
For c As Integer = 2 To t2.Cols.count-1
nr1(t2.cols(c).name) = dr2(t2.cols(c).name)
Next
Next
Next
t1.ResumeRedraw
t1.sort = "结算月,客户名称"
MainTable = t1

Tables("明细根表_Table10").sort = "销档"

Dim t As Table = Tables("明细根表_Table10")
Dim pr As Row = Nothing
For Each r As Row In t.Rows
If pr IsNot Nothing AndAlso r("客户名称") = pr("客户名称") AndAlso r("票") = pr("票") Then
r("客户名称") = Nothing
r("票") = Nothing
End If
pr = r
Next

Tables("明细根表_Table10").AutoSizeCols()
Tables("明细根表_Table11").AutoSizeCols()

--  作者：有点蓝
--  发布时间：2019/6/5 11:29:00
--

Dim t As Table = Tables("明细根表_Table10")
Dim pr As Row = Nothing
For Each r As Row In t.Rows
If pr IsNot Nothing AndAlso r("客户名称") = pr("客户名称") AndAlso r("票") = pr("票") Then
r("客户名称") = Nothing
r("票") = Nothing
else
pr = r
End If
Next

--  作者：hcfbehero
--  发布时间：2019/6/5 11:40:00
--
完美解决，我再加几个条件就OK了，谢谢了，蓝版

--  作者：hcfbehero
--  发布时间：2019/6/5 15:02:00
--
不好意思了老师，我在这个表里新加了DOUBLE列，在进行删除重复行时，系统提示，新加列为只读，怎么解决呢，老师