-- 作者:lal021962
-- 发布时间:2020/4/2 19:37:00
-- 合并查询表按日期升序
以下代码,请问如何让合并后的查询表按日期升序排列。
Dim k As String = Tables("资金流向明细表").current("账号名称") Dim g As Date = Tables("资金流向明细表").current("开始时间") Dim f As Date = Tables("资金流向明细表").current("结束时间")
Dim jb1 As New SQLJoinTableBuilder("查询表1","资金账号") jb1.C jb1.AddTable("资金账号","账号编号","资金支出单","账号编号") jb1.AddCols("付款单编号","{资金支出单}.单据类型","账号名称",True) jb1.AddExp("支出金额","(-支出金额)",True) jb1.AddCols("付款日期",True) jb1.filter = "账号名称 =\'" & k & "\' and 付款日期>=\'" & g & "\' and 付款日期<=\'" & f & "\'"
Dim jb2 As New SQLJoinTableBuilder("查询表1","资金账号") jb2.C jb2.AddTable("资金账号","账号编号","资金收入单","账号编号") jb2.AddCols("收款单编号","{资金收入单}.单据类型","账号名称","收入金额","收款日期") jb2.filter = "账号名称 = \'" & k & "\' and 收款日期>=\'" & g & "\' and 收款日期<=\'" & f & "\'"
Dim jb3 As New SQLJoinTableBuilder("查询表1","资金账号") jb3.C jb3.AddTable("资金账号","账号编号","工厂销售收款单","账号编号") jb3.AddTable("工厂销售收款单","收款单编号","对内销售收款明细表","收款单编号") jb3.AddCols("{工厂销售收款单}.收款单编号","{工厂销售收款单}.表名代码","账号名称",True) jb3.AddExp("实际收款金额", "isnull(Sum({对内销售收款明细表}.本次收款金额),0)-isnull(Sum({对内销售收款明细表}.抹零金额),0)") jb3.AddCols("{工厂销售收款单}.收款日期",True) jb3.filter = "账号名称 =\'" & k & "\' and {工厂销售收款单}.收款日期>=\'" & g & "\' and {工厂销售收款单}.收款日期<=\'" & f & "\'"
Dim jb4 As New SQLJoinTableBuilder("查询表1","资金账号") jb4.C jb4.AddTable("资金账号","账号编号","易装店销售收款单","账号编号") jb4.AddTable("易装店销售收款单","收款单编号","易装店销售收款明细表","易装收款单编号") jb4.AddCols("{易装店销售收款单}.收款单编号","{易装店销售收款单}.表名代码","账号名称",True) jb4.AddExp("实际总收款", "isnull(Sum({易装店销售收款明细表}.本次收款金额),0)-isnull(Sum({易装店销售收款明细表}.抹零金额),0)") jb4.AddCols("{易装店销售收款单}.收款日期",True) jb4.filter = "账号名称 =\'" & k & "\' and {易装店销售收款单}.收款日期>=\'" & g & "\' and {易装店销售收款单}.收款日期<=\'" & f & "\'"
Dim jb5 As New SQLJoinTableBuilder("查询表1","资金账号") jb5.C jb5.AddTable("资金账号","账号编号","易装店销售收款单","账号编号") jb5.AddTable("易装店销售收款单","收款单编号","应收款明细","销售收款单编号") jb5.AddCols("{易装店销售收款单}.收款单编号","{易装店销售收款单}.表名代码","账号名称",True) jb5.AddExp("实际总收款", "isnull(Sum({应收款明细}.本次收款),0)-isnull(Sum({应收款明细}.本次抹零),0)") jb5.AddCols("{易装店销售收款单}.收款日期",True) jb5.filter = "账号名称 =\'" & k & "\' and {易装店销售收款单}.收款日期>=\'" & g & "\' and {易装店销售收款单}.收款日期<=\'" & f & "\'"
Dim jb6 As New SQLJoinTableBuilder("查询表1","资金账号") jb6.C jb6.AddTable("资金账号","账号编号","自产付款单","账号编号") jb6.AddTable("自产付款单","付款单编号","自产付款明细表","付款单编号") jb6.AddCols("{自产付款单}.付款单编号","{自产付款单}.表名代码","账号名称",True) jb6.AddExp("实际总收款", "-isnull(Sum({自产付款明细表}.本次付款金额),0)+isnull(Sum({自产付款明细表}.抹零金额),0)") jb6.AddCols("{自产付款单}.付款日期",True) jb6.filter = "账号名称 =\'" & k & "\' and {自产付款单}.付款日期>=\'" & g & "\' and {自产付款单}.付款日期<=\'" & f & "\'"
Dim jb7 As New SQLJoinTableBuilder("查询表1","资金账号") jb7.C jb7.AddTable("资金账号","账号编号","自产付款单","账号编号") jb7.AddTable("自产付款单","付款单编号","安装付款明细表","付款单编号") jb7.AddCols("{自产付款单}.付款单编号","{自产付款单}.表名代码","账号名称",True) jb7.AddExp("实际总收款", "isnull(-Sum({安装付款明细表}.本次付款金额),0)+isnull(Sum({安装付款明细表}.抹零金额),0)") jb7.AddCols("{自产付款单}.付款日期",True) jb7.filter = "账号名称 =\'" & k & "\' and {自产付款单}.付款日期>=\'" & g & "\' and {自产付款单}.付款日期<=\'" & f & "\'"
Dim jb8 As New SQLJoinTableBuilder("查询表1","资金账号") jb8.C jb8.AddTable("资金账号","账号编号","采购付款单","账号编号") jb8.AddTable("采购付款单","付款单编号","采购订单明细表","采购付款单编号") jb8.AddCols("{采购付款单}.付款单编号","{采购付款单}.表名代码","账号名称",True) jb8.AddExp("实际总收款", "-isnull(Sum({采购订单明细表}.本次付款金额),0)+isnull(Sum({采购订单明细表}.抹零金额),0)") jb8.AddCols("{采购付款单}.付款日期",True) jb8.filter = "账号名称 =\'" & k & "\' and {采购付款单}.付款日期>=\'" & g & "\' and {采购付款单}.付款日期<=\'" & f & "\'"
jb1.Build() jb1.Union(jb2) \'组合jb2 jb1.Union(jb3) \'组合jb2 jb1.Union(jb4) \'组合jb2 jb1.Union(jb5) \'组合jb2 jb1.Union(jb6) \'组合jb2 jb1.Union(jb7) \'组合jb2 jb1.Union(jb8) \'组合jb2
Tables("资金流向明细表_Table1").datasource =jb1.builddatasource
DataTables("资金流向明细表_Table1").DataCols.Add("余额",Gettype(Double)) Dim sum As Double = val(e.Form.Controls("Label6").text) For Each dr As Row In Tables("资金流向明细表_Table1").Rows sum = sum + dr("支出金额") dr("余额") = sum Next
|