Foxtable(狐表)用户栏目专家坐堂 → 要得到按用户输入的时间条件来得到相应的统计报表,用以下的代码后出错,请指正,谢谢。


  共有5493人关注过本帖树形打印复制链接

主题:要得到按用户输入的时间条件来得到相应的统计报表,用以下的代码后出错,请指正,谢谢。

帅哥哟,离线,有人找我吗?
soap_lwb
  1楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:幼狐 帖子:65 积分:631 威望:0 精华:0 注册:2018/10/17 18:29:00
要得到按用户输入的时间条件来得到相应的统计报表,用以下的代码后出错,请指正,谢谢。  发帖心情 Post By:2018/12/3 11:22:00 [只看该作者]

 
 下载信息  [文件大小:   下载次数: ]
点击浏览该文件:管理项目1.zip


此主题相关图片如下:统计结果.png
按此在新窗口浏览图片

Dim tjb As String = "统计表1"
Dim b As New SQLGroupTableBuilder(tjb,"管网管理所抢修热线")
b.Groups.AddExp("来电时间","CDate(日期)")
b.Totals.AddExp("表A_接单量","1")
b.Totals.AddExp("表A_已处理数量","iif(是否解决=true,1,0)")
b.Totals.AddExp("表A_未处理数量","iif(是否解决=true,0,1)")
Dim dd As Date = "2018-10-01"
b.filter = "来电时间>=#" & dd & "# and 来电时间 < #" & dd.addmonths(1) & "#"
'b.Build
Dim d As fxDataSource = b.BuildDataSource()
Dim bms As String = "用户管理水表热线|公司内部业务热线登记表|咨询业务热线登记表"
For Each bm As String In bms.split("|")
    Dim b1 As New SQLGroupTableBuilder(tjb,bm)
    b1.Groups.AddExp("来电时间","CDate(日期)")
    b1.Totals.AddExp(bm & "_接单量","1")
    b1.Totals.AddExp(bm & "_已处理数量","iif(是否解决=true,1,0)")
    b1.Totals.AddExp(bm & "_未处理数量","iif(是否解决=true,0,1)")
    b.filter = "来电时间>=#" & dd & "# and 来电时间 < #" & dd.addmonths(1) & "#"
    'b1.Build
    Dim d1 As fxDataSource = b1.BuildDataSource()
    d.Combine("来电时间",d1,"来电时间")
Next
d.Show(tjb)
MainTable = Tables(tjb) '打开生成的统计表"

 回到顶部
帅哥哟,离线,有人找我吗?
有点甜
  2楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:版主 帖子:85326 积分:427815 威望:0 精华:5 注册:2012/10/18 22:13:00
  发帖心情 Post By:2018/12/3 11:31:00 [只看该作者]

Dim Filter1 As String = "1=1"

With e.Form.Controls("StartDate")
    If .Value IsNot Nothing Then
        If Filter1 >"" Then
            Filter1 = Filter1 & " And "
        End If
        Filter1 = Filter1 & "来电时间 >= #" & .Value & "#"
    End If
End With
With e.Form.Controls("EndDate")
    If .Value IsNot Nothing Then
        If Filter1 >"" Then
            Filter1 = Filter1 & " And "
        End If
        Filter1 = Filter1 & "来电时间 <= #" & .Value & "#"
    End If
End With

Tables("管网管理所抢修热线").Filter = Filter1
Tables("用户管理水表热线").Filter = Filter1


Dim dt1 As fxDataSource
Dim a As New GroupTableBuilder("统计表1",DataTables("管网管理所抢修热线"))


a.filter = Tables("管网管理所抢修热线").Filter

a.Groups.AddDef("来电时间",DateGroupEnum.Year,"年")
a.Groups.AddDef("来电时间","月")
a.Groups.AddDef("来电时间",DateGroupEnum.Day,"日")

a.Totals.AddDef("来电信息",AggregateEnum.Count,"管网A单_接单数量") '对接单数量进行count


a.filter = Tables("管网管理所抢修热线").Filter & " and [是否解决] = True"


a.Totals.AddDef("是否解决",AggregateEnum.Count,"管网A单_已处理数量")'对已处理数量进行count

 

a.Totals.AddDef("是否解决",AggregateEnum.Count,"管网A单_未处理数量")

dt1 = a.BuildDataSource()

Dim dt2  As fxDataSource
Dim b As New GroupTableBuilder("统计表2",DataTables("用户管理水表热线"))
b.filter = Tables("用户管理水表热线").Filter

b.Groups.AddDef("来电时间",DateGroupEnum.Year,"年")
b.Groups.AddDef("来电时间","月")
b.Groups.AddDef("来电时间",DateGroupEnum.Day,"日")

b.Totals.AddDef("来电信息",AggregateEnum.Count,"表务B单_接单数量") '对数量进行count
b.Totals.AddDef("是否解决",AggregateEnum.Count,"表务B单_已处理数量")
b.Totals.AddDef("是否解决",AggregateEnum.Count,"表务B单_未处理数量")

dt2 = b.BuildDataSource()

Dim cls() As String = {"年","月","日"}
dt1.Combine(cls,dt2,cls)
dt1.Show("统计表bb")
MainTable = Tables("统计表bb")


 回到顶部
帅哥哟,离线,有人找我吗?
有点甜
  3楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:版主 帖子:85326 积分:427815 威望:0 精华:5 注册:2012/10/18 22:13:00
  发帖心情 Post By:2018/12/3 11:39:00 [只看该作者]

Dim Filter1 As String = "1=1"

With e.Form.Controls("StartDate")
    If .Value IsNot Nothing Then
        If Filter1 >"" Then
            Filter1 = Filter1 & " And "
        End If
        Filter1 = Filter1 & "来电时间 >= #" & .Value & "#"
    End If
End With
With e.Form.Controls("EndDate")
    If .Value IsNot Nothing Then
        If Filter1 >"" Then
            Filter1 = Filter1 & " And "
        End If
        Filter1 = Filter1 & "来电时间 <= #" & .Value & "#"
    End If
End With

 

Dim tjb As String = "统计表1"
Dim d As fxDataSource
Dim bms As String = "内部业务热线登记表|咨询业务热线登记表|用户管理水表热线|管网管理所抢修热线"
Dim i As Integer = 0
For Each bm As String In bms.split("|")
    Dim b1 As New SQLGroupTableBuilder(tjb,bm)
    b1.Groups.AddExp("来电时间","来电时间")
    b1.Totals.AddExp(bm & "_接单量","1")
    b1.Totals.AddExp(bm & "_已处理数量","iif(是否解决=true,1,0)")
    b1.Totals.AddExp(bm & "_未处理数量","iif(是否解决=true,0,1)")
    b1.filter = filter1
    Dim d1 As fxDataSource = b1.BuildDataSource()
    If i = 0 Then
        d = d1
    Else
        d.Combine("来电时间",d1,"来电时间")
    End If
Next
d.Show(tjb)
MainTable = Tables(tjb) '打开生成的统计表"


 回到顶部
帅哥哟,离线,有人找我吗?
soap_lwb
  4楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:幼狐 帖子:65 积分:631 威望:0 精华:0 注册:2018/10/17 18:29:00
用了以后,发现只统计出了“管网管理所抢修热线”一张表,其它三张表的统计结果没有列出  发帖心情 Post By:2018/12/3 12:39:00 [只看该作者]


图片点击可在新窗口打开查看此主题相关图片如下:统计后结果1203.png
图片点击可在新窗口打开查看

 回到顶部
帅哥哟,离线,有人找我吗?
有点甜
  5楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:版主 帖子:85326 积分:427815 威望:0 精华:5 注册:2012/10/18 22:13:00
  发帖心情 Post By:2018/12/3 12:42:00 [只看该作者]

Dim Filter1 As String = "1=1"

With e.Form.Controls("StartDate")
    If .Value IsNot Nothing Then
        If Filter1 >"" Then
            Filter1 = Filter1 & " And "
        End If
        Filter1 = Filter1 & "来电时间 >= #" & .Value & "#"
    End If
End With
With e.Form.Controls("EndDate")
    If .Value IsNot Nothing Then
        If Filter1 >"" Then
            Filter1 = Filter1 & " And "
        End If
        Filter1 = Filter1 & "来电时间 <= #" & .Value & "#"
    End If
End With

 

Dim tjb As String = "统计表1"
Dim d As fxDataSource
Dim bms As String = "内部业务热线登记表|咨询业务热线登记表|用户管理水表热线|管网管理所抢修热线"
Dim i As Integer = 0
For Each bm As String In bms.split("|")
    Dim b1 As New SQLGroupTableBuilder(tjb,bm)
    b1.Groups.AddExp("来电时间","来电时间")
    b1.Totals.AddExp(bm & "_接单量","1")
    b1.Totals.AddExp(bm & "_已处理数量","iif(是否解决=true,1,0)")
    b1.Totals.AddExp(bm & "_未处理数量","iif(是否解决=true,0,1)")
    b1.filter = filter1
    Dim d1 As fxDataSource = b1.BuildDataSource()
    If i = 0 Then
        d = d1
    Else
        d.Combine("来电时间",d1,"来电时间")
    End If

    i += 1
Next
d.Show(tjb)
MainTable = Tables(tjb) '打开生成的统计表"


 回到顶部
帅哥哟,离线,有人找我吗?
soap_lwb
  6楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:幼狐 帖子:65 积分:631 威望:0 精华:0 注册:2018/10/17 18:29:00
如果在统计表的最后一行需要增加一行汇总统计,怎么加代码?  发帖心情 Post By:2018/12/3 14:18:00 [只看该作者]

如果在统计表的最后一行需要增加一行汇总统计,如何加代码

 回到顶部
帅哥哟,离线,有人找我吗?
有点甜
  7楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:版主 帖子:85326 积分:427815 威望:0 精华:5 注册:2012/10/18 22:13:00
  发帖心情 Post By:2018/12/3 15:25:00 [只看该作者]

以下是引用soap_lwb在2018/12/3 14:18:00的发言:
如果在统计表的最后一行需要增加一行汇总统计,如何加代码

 

设置汇总模式,或者合计模式

 

Dim Filter1 As String = "1=1"

With e.Form.Controls("StartDate")
    If .Value IsNot Nothing Then
        If Filter1 >"" Then
            Filter1 = Filter1 & " And "
        End If
        Filter1 = Filter1 & "来电时间 >= #" & .Value & "#"
    End If
End With
With e.Form.Controls("EndDate")
    If .Value IsNot Nothing Then
        If Filter1 >"" Then
            Filter1 = Filter1 & " And "
        End If
        Filter1 = Filter1 & "来电时间 <= #" & .Value & "#"
    End If
End With

 

Dim tjb As String = "统计表1"
Dim d As fxDataSource
Dim bms As String = "内部业务热线登记表|咨询业务热线登记表|用户管理水表热线|管网管理所抢修热线"
Dim i As Integer = 0
For Each bm As String In bms.split("|")
    Dim b1 As New SQLGroupTableBuilder(tjb,bm)
    b1.Groups.AddExp("来电时间","来电时间")
    b1.Totals.AddExp(bm & "_接单量","1")
    b1.Totals.AddExp(bm & "_已处理数量","iif(是否解决=true,1,0)")
    b1.Totals.AddExp(bm & "_未处理数量","iif(是否解决=true,0,1)")
    b1.filter = filter1
    Dim d1 As fxDataSource = b1.BuildDataSource()
    If i = 0 Then
        d = d1
    Else
        d.Combine("来电时间",d1,"来电时间")
    End If
    i += 1
Next
d.Show(tjb)
MainTable = Tables(tjb) '打开生成的统计表"

Dim t As Table = Tables(tjb)
Dim g As Subtotalgroup
t.SubtotalGroups.Clear()
t.GroupAboveData = False
t.TreeVisible = False
t.SpillNode = True

g = New Subtotalgroup
g.Aggregate = AggregateEnum.Sum
g.GroupOn = "*"
Dim ls As new List(Of String)
For Each c As Col In t.Cols
    If c.IsNumeric Then
        ls.add(c.Name)
    End If
Next
g.TotalOn = String.join(",", ls.ToArray)
g.Caption = "合计"
t.SubtotalGroups.Add(g)

t.Subtotal()


 回到顶部