Dim bd1 As New sqlCrossTableBuilder("挂账与未收回统计表","要统计的明细表测试数据" )
'bd1.ConnectionName = gs_strActiveConn
bd1.HGroups.AddDef("客户名称")
bd1.HGroups.AddDef("经办人部门") '根据名称分组
bd1.HGroups.AddDef("经办人") '根据名称分组
bd1.vGroups.AddDef("所属年份") '添加产品列用于垂直分组
bd1.VGroups.AddDef("所属月份") '添加产品列用于垂直分组
bd1.Totals.AddDef("收款金额") '对金额进行统计
bd1.HorizontalTotal = True
bd1.VerticalTotal = True
bd1.Build
Dim dict As new Dictionary(of String,String)
For Each c As Col In Tables("挂账与未收回统计表").Cols
If c.Name.Contains("_")
If c.Caption > "" Then
dict.Add(c.Caption,c.Name)
Else
dict.Add(c.Name,c.Name)
End If
End If
Next
Dim Products As List(Of String()) = DataTables("要统计的明细表测试数据").GetValues("所属年份|所属月份")
For Each s1 As String() In Products
Dim s As String = s1(0) & "_" & s1(1)
Dim idx As Integer = Tables("挂账与未收回统计表").Cols(dict(s)).Index
Tables("挂账与未收回统计表").Cols(dict(s)).Caption = s & "_收款金额"
DataTables("挂账与未收回统计表").DataCols.Add(s & "_收款状态",Gettype(String),32)
Tables("挂账与未收回统计表").Cols(s & "_收款状态").Move(idx)
DataTables("挂账与未收回统计表").DataCols.Add(s & "_收款项目",Gettype(String),32)
Tables("挂账与未收回统计表").Cols(s & "_收款项目").Move(idx)
DataTables("挂账与未收回统计表").DataCols.Add(s & "_收款项目类型",Gettype(String),32)
Tables("挂账与未收回统计表").Cols(s & "_收款项目类型").Move(idx)
Next
Dim dr As DataRow
Dim s2() As String = {"收款项目类型","收款项目","收款状态"}
For Each r As Row In Tables("挂账与未收回统计表").Rows
If r("客户名称") <> "合计" Then
For Each s1 As String() In Products
dr = DataTables("要统计的明细表测试数据").Find("客户名称='" & r("客户名称") & "' And 经办人部门='" & r("经办人部门") & "' and 经办人='" & r("经办人") & "' and 所属年份=" & s1(0) & " And 所属月份=" & s1(1))
If dr IsNot Nothing Then
Dim s As String = s1(0) & "_" & s1(1) & "_"
For Each ss As String In s2
r(s & ss) = dr(ss)
Next
End If
Next
End If
Next
加好友 
发短信
Post By：2021/4/12 11:34:00 [显示全部帖子]
