专家坐堂-关于数据统计，市场人员达成，如果订单未空，将出现错误~~~Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

Dim Cmd As new SQLCommand

Cmd.C

Cmd.CommandText = "select distinct 分区,年,月份,目标 from 目标 where " & CustomFilter

Dim dt1 As DataTable = Cmd.ExecuteReader

Dim dtb As New DataTableBuilder("统计")

dtb.AddDef("分区", Gettype(String), 50)

Dim prds As List(of String()) = dt1.GetUniqueValues("","年","月份")

Dim exp As String

Dim prd As String()

dtb.AddDef("目标总额", Gettype(Double))

dtb.AddDef("销售总额", Gettype(Double))

dtb.AddDef("达成率", Gettype(Double),"[销售总额]/[目标总额]")

For Each prd In prds

dtb.AddDef(prd(0) & "_" & prd(1) & "_目标", Gettype(Double))

dtb.AddDef(prd(0) & "_" & prd(1) & "_销售", Gettype(Double))

exp = String.format("[{0}]/[{1}]",prd(0) & "_" & prd(1) & "_销售" ,prd(0) & "_" & prd(1) & "_目标" )

dtb.AddDef(prd(0) & "_" & prd(1) & "_达成率", Gettype(Double),exp)

dtb.Build()

Cmd.CommandText = "select 分区,year(日期) as 年,month(日期) as 月份,sum(金额) as 销售 from 订单 where " & CustomFilter & " group by 分区,year(日期),month(日期)"

Dim dt2 As DataTable = Cmd.ExecuteReader

Dim dr As DataRow

For Each cus As String In dt1.GetUniqueValues("","分区")

dr = DataTables("统计").AddNew()

dr("分区") = cus

For Each prd In prds

dr(prd(0) & "_" & prd(1) & "_目标") = dt1.Compute("Sum(目标)","分区 = \'" & cus & "\' And [年] = \'" & prd(0) & "\' and 月份 = \'"& prd(1) &"\'")

dr(prd(0) & "_" & prd(1) & "_销售") = dt2.Compute("Sum(销售)","分区 = \'" & cus & "\' And [年] = \'" & prd(0) & "\' and 月份 = \'"& prd(1) &"\'")

dr("目标总额") = dt1.Compute("Sum(目标)","分区 = \'" & cus & "\'")

dr("销售总额") = dt2.Compute("Sum(销售)","分区 = \'" & cus & "\'")

\'******************************合计行

dr = DataTables("统计").AddNew()

dr("分区") = "合计"

For Each prd In prds

Dim ColName1 As String = prd(0) & "_" & prd(1) & "_目标"

Dim ColName2 As String = prd(0) & "_" & prd(1) & "_销售"

dr(ColName1) = dt1.Compute("Sum(目标)","[年] = \'" & prd(0) & "\' and 月份 = \'"& prd(1) &"\'")

dr(ColName2) = dt2.Compute("Sum(销售)","[年] = \'" & prd(0) & "\' and 月份 = \'"& prd(1) &"\'")

dr("目标总额") = DataTables("统计").Compute("Sum(目标总额)")

dr("销售总额") = DataTables("统计").Compute("Sum(销售总额)")

\'********************************

For Each prd In prds

DataTables("统计").DataCols(prd(0) & "_" & prd(1) & "_达成率").SetFormat("0.00%")

DataTables("统计").DataCols("达成率").SetFormat("0.00%")

MainTable = Tables("统计")

Tables("统计").AutoSizeCols