gcmd.CommandText="select * from 产品_应收账清单 where 1=2"
Dim dtb2 As DataTable= gcmd.ExecuteReader
Dim dtb As New DataTableBuilder("统计")
dtb.AddDef("公司", Gettype(String), 32)
dtb.AddDef("单位名称", Gettype(String), 32)
Dim prds As List(of String) = dtb2.SQLGetValues("convert(nvarchar(7),开piao日期,120)")
For Each s As String In prds
dtb.AddDef(s.replace("-","_") & "月_开piao金额", Gettype(String), 32)
dtb.AddDef(s.replace("-","_") & "月_回款金额", Gettype(String), 32)
Next
dtb.AddDef("应收账款", Gettype(Double), 32)
dtb.Build()
For Each nm() As String In dtb2.SQLGetValues("开piao公司|单位名称")
Dim dr As DataRow = DataTables("统计").AddNew()
dr("公司") = nm(0)
dr("单位名称")= nm(1)
For Index As Integer = 0 To prds.Count -1
dr(prds(Index).replace("-","_") & "月_开piao金额") = dtb2.SQLCompute("sum(开piao金额)","编号 Like '%CKB%' and 单位名称='" & nm(1) & "' and 开piao公司='" & nm(0) & "' and 开piao日期 >='" & prds(Index) & "-01' And 开piao日期 <='" & prds(Index) & "-" & Date.DaysInMonth(prds(Index).SubString(0,4),prds(Index).SubString(6,1)) &"'")
dr(prds(Index).replace("-","_") & "月_回款金额") = dtb2.SQLCompute("sum(开piao金额)","编号 Like '%CKH%' and 单位名称='" & nm(1) & "' and 开piao公司='" & nm(0) & "' and 开piao日期 >='" & prds(Index) & "-01' And 开piao日期 <='" & prds(Index) & "-" & Date.DaysInMonth(prds(Index).SubString(0,4),prds(Index).SubString(6,1)) &"'")
Next
dr("应收账款")=dtb2.SQLCompute("sum(开piao金额)","编号 Like '%CKB%' and 单位名称='" & nm(1) & "' and 开piao公司='" & nm(0) & "' ")-dtb2.SQLCompute("sum(开piao金额)","编号 Like '%CKH%' and 单位名称='" & nm(1) & "' and 开piao公司='" & nm(0) & "' ")
Next
MainTable= Tables("统计")
加好友 
发短信
Post By：2020/8/8 21:23:00 [只看该作者]
