-- 作者:wealthwind
-- 发布时间:2014/5/5 20:45:00
-- 关于客户合计,为什么有错?
以下是合计客户销量,的代码,请问是哪里出现问题了??
MainTable = Tables("订单") If CustomFilter = "" Then CustomFilter = "1 = 1" \'或者CustomFilter = "1 = 2" End If 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) dtb.AddDef("客户编码", Gettype(String), 150) dtb.AddDef("客户", Gettype(String), 150) Dim prds As List(of String()) = dt1.GetUniqueValues("","年","月份") Dim exp As String dtb.AddDef("目标总额", Gettype(Double)) dtb.AddDef("销售总额", Gettype(Double)) dtb.AddDef("达成率", Gettype(Double),"[销售总额]/[目标总额]") For Each prd As String() 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) Next dtb.Build() Cmd.CommandText = "select 分区,客户编码,客户,year(日期) as 年,month(日期) as 月份,sum(金额) as 销售 from 订单 where " & CustomFilter & " group by 分区,客户编码,客户,year(日期),month(日期)" Dim dt2 As DataTable = Cmd.ExecuteReader For Each cus As String() In dt1.GetUniqueValues("","分区","客户编码","客户") Dim dr As DataRow = DataTables("统计").AddNew() dr("分区") = cus(0) dr("客户编码") = cus(1) dr("客户") = cus(2) For Each prd As String() In prds dr(prd(0) & "_" & prd(1) & "_目标") = dt1.Compute("Sum(目标)","分区 = \'" & cus(0) & "\' And 客户编码 = \'" & cus(1) & "\' And [年] = \'" & prd(0) & "\' and 月份 = \'"& prd(1) &"\'") dr(prd(0) & "_" & prd(1) & "_销售") = dt2.Compute("Sum(销售)","分区 = \'" & cus(0) & "\' And 客户编码 = \'" & cus(1) & "\' And [年] = \'" & prd(0) & "\' and 月份 = \'"& prd(1) &"\'") Next dr("目标总额") = dt1.Compute("Sum(目标)","分区 = \'" & cus(0) & "\' And 客户编码 = \'" & cus(1) & "\'") dr("销售总额") = dt2.Compute("Sum(销售)","分区 = \'" & cus(0) & "\' And 客户编码 = \'" & cus(1) & "\'") \'******************************合计行 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) &"\'") Next dr("目标总额") = DataTables("统计").Compute("Sum(目标总额)") dr("销售总额") = DataTables("统计").Compute("Sum(销售总额)") \'********************************
Next For Each prd As String() In prds DataTables("统计").DataCols(prd(0) & "_" & prd(1) & "_达成率").SetFormat("0.00%") Next DataTables("统计").DataCols("达成率").SetFormat("0.00%") MainTable = Tables("统计") Tables("统计").AutoSizeCols 此主题相关图片如下:360截图20140505204429015.jpg
|
-- 作者:wealthwind
-- 发布时间:2014/5/5 21:14:00
--
现在的代码是
MainTable = Tables("订单") If CustomFilter = "" Then CustomFilter = "1 = 1" \'或者CustomFilter = "1 = 2" End If 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) dtb.AddDef("客户编码", Gettype(String), 150) dtb.AddDef("客户", Gettype(String), 150) Dim prds As List(of String()) = dt1.GetUniqueValues("","年","月份") Dim exp As String dtb.AddDef("目标总额", Gettype(Double)) dtb.AddDef("销售总额", Gettype(Double)) dtb.AddDef("达成率", Gettype(Double),"[销售总额]/[目标总额]") For Each prd As String() 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) Next dtb.Build() Cmd.CommandText = "select 分区,客户编码,客户,year(日期) as 年,month(日期) as 月份,sum(金额) as 销售 from 订单 where " & CustomFilter & " group by 分区,客户编码,客户,year(日期),month(日期)" Dim dt2 As DataTable = Cmd.ExecuteReader For Each cus As String() In dt1.GetUniqueValues("","分区","客户编码","客户") Dim dr As DataRow = DataTables("统计").AddNew() dr("分区") = cus(0) dr("客户编码") = cus(1) dr("客户") = cus(2) For Each prd As String() In prds dr(prd(0) & "_" & prd(1) & "_目标") = dt1.Compute("Sum(目标)","分区 = \'" & cus(0) & "\' And 客户编码 = \'" & cus(1) & "\' And [年] = \'" & prd(0) & "\' and 月份 = \'"& prd(1) &"\'") dr(prd(0) & "_" & prd(1) & "_销售") = dt2.Compute("Sum(销售)","分区 = \'" & cus(0) & "\' And 客户编码 = \'" & cus(1) & "\' And [年] = \'" & prd(0) & "\' and 月份 = \'"& prd(1) &"\'") Next dr("目标总额") = dt1.Compute("Sum(目标)","分区 = \'" & cus(0) & "\' And 客户编码 = \'" & cus(1) & "\'") dr("销售总额") = dt2.Compute("Sum(销售)","分区 = \'" & cus(0) & "\' And 客户编码 = \'" & cus(1) & "\'") \'******************************合计行 dr = DataTables("统计").AddNew() dr("客户") = "合计" For Each prd As String() 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) &"\'") Next dr("目标总额") = DataTables("统计").Compute("Sum(目标总额)") dr("销售总额") = DataTables("统计").Compute("Sum(销售总额)") \'******************************** Next For Each prd As String() In prds DataTables("统计").DataCols(prd(0) & "_" & prd(1) & "_达成率").SetFormat("0.00%") Next DataTables("统计").DataCols("达成率").SetFormat("0.00%") MainTable = Tables("统计") Tables("统计").AutoSizeCols
|