专家坐堂-日期生成统计Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.net/bbs/index.asp)
--  专家坐堂  (http://foxtable.net/bbs/list.asp?boardid=2)
----  日期生成统计  (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=82435)

--  作者：yangwenghd
--  发布时间：2016/3/17 16:50:00
--  日期生成统计
能帮我看看这个吗？
为什么生成的统计表没有年啊?
在图标上的顺序就乱了！
可以让 "业务往来_送货日期把年生成在里面吗？

此主题相关图片如下：a.jpg

此主题相关图片如下：b.jpg

Dim d1 As Date = e.Form.Controls("DateTimePicker5").Value
If d1 = Nothing Then d1 = Date.Today
Dim filter1 As String = ""
If e.Form.Controls("RadioButton1").Checked Then
filter1 = "业务往来_送货日期 = #" & d1 & "#"
ElseIf e.Form.Controls("RadioButton2").Checked Then
Dim w As Integer = d1.DayOfWeek \'算出今天是星期几
Dim dt1 As Date = d1.AddDays(0 - w) \'获取本周的第一天 ,星期一到星期天
Dim dt2 As Date = d1.AddDays(6 - w) \'获取本周的最后一天
filter1 = "业务往来_送货日期 >= #" & dt1 & "# and 业务往来_送货日期 <= #" & dt2 & "#"
Else If e.Form.Controls("RadioButton3").Checked Then
Dim y As Integer = d1.Year
Dim m As Integer = d1.Month
Dim dt1 As New Date(y, m, 1)
Dim dt2 As New Date(y, m, Date.DaysInMonth(y, m)) \'获取本月的最后一天

filter1 = "业务往来_送货日期 >= #" & dt1 & "# and 业务往来_送货日期 <= #" & dt2 & "#"
Else If e.Form.Controls("RadioButton4").Checked Then
Dim y As Integer = d1.Year
Dim q As Integer = (d1.Month - 1) \\ 3 + 1 \'计算现在是第几个季度
Dim dt1 As New Date(y, 3 * (q - 1) + 1, 1) \'获取本季度的第一天
Dim dt2 As New Date(y, 3 * q, Date.DaysInMonth(y,3 * q)) \'获取本季度的最后一天

filter1 = "业务往来_送货日期 >= #" & dt1 & "# and 业务往来_送货日期 <= #" & dt2 & "#"
Else If e.Form.Controls("RadioButton5").Checked Then
Dim y As Integer = d1.Year
Dim dt1 As New Date(y, 1, 1)
Dim dt2 As New Date(y, 12, 31)

filter1 = "业务往来_送货日期 >= #" & dt1 & "# and 业务往来_送货日期 <= #" & dt2 & "#"
Else If e.Form.Controls("RadioButton6").Checked Then
filter1 = "1=1"
Else If e.Form.Controls("RadioButton07").Checked Then
filter1 = "业务往来_送货日期 < #" & d1 & "#"
End If

Dim c6 As String = e.Form.controls("ComboBox6").Value
If c6 <> "全部" Then
Dim tt As Table = e.Form.Controls("Table10").Table
If tt.Current IsNot Nothing Then
filter1 &= " and " & c6 & " = \'" & tt.Current(c6) & "\'"
End If
End If

[此贴子已经被作者于2016/3/17 16:50:37编辑过]

--  作者：大红袍
--  发布时间：2016/3/17 16:54:00
--
你设置生成临时表的时候，可以设置根据年、月分组，而不仅仅是根据月分组啊。

--  作者：yangwenghd
--  发布时间：2016/3/17 19:44:00
--
谢谢，是的，生成的时候有周，月，季，年，全部
在选择周、月、季的时候就是显示的1.2.3，这样的在临时表的时间列，图表显示的时候是按照数字的顺序显示的，有没可能可以在前面加上年
比如选择月的时候，选择的时间是 2015-12月到2016-1到2月
生成的不是 12、01、02。这样生成图表就会把12月的数据显示到最后
如果能生成 2015-12、2016-01、2016-02.这样的话，图表就是从去年2015-12月显示的。感谢感谢

Dim cbx = e.Form.Controls("ComboBox11")
Select Case cbx.Text
Case "年"
tp = DateGroupEnum.Year
Case "季"
tp = DateGroupEnum.Quarter
Case "月"
tp = DateGroupEnum.month
Case "周"
tp = DateGroupEnum.Week
Case "天"
tp = DateGroupEnum.None
Case Else
tp = DateGroupEnum.None
End Select
我想主要应该修改在这里，感谢感谢

这是整个代码：
Dim d1 As Date = e.Form.Controls("DateTimePicker5").Value
If d1 = Nothing Then d1 = Date.Today
Dim filter1 As String = ""
If e.Form.Controls("RadioButton1").Checked Then
filter1 = "业务往来_送货日期 = #" & d1 & "#"
ElseIf e.Form.Controls("RadioButton2").Checked Then
Dim w As Integer = d1.DayOfWeek \'算出今天是星期几
Dim dt1 As Date = d1.AddDays(0 - w) \'获取本周的第一天 ,星期一到星期天
Dim dt2 As Date = d1.AddDays(6 - w) \'获取本周的最后一天
filter1 = "业务往来_送货日期 >= #" & dt1 & "# and 业务往来_送货日期 <= #" & dt2 & "#"
Else If e.Form.Controls("RadioButton3").Checked Then
Dim y As Integer = d1.Year
Dim m As Integer = d1.Month
Dim dt1 As New Date(y, m, 1)
Dim dt2 As New Date(y, m, Date.DaysInMonth(y, m)) \'获取本月的最后一天

filter1 = "业务往来_送货日期 >= #" & dt1 & "# and 业务往来_送货日期 <= #" & dt2 & "#"
Else If e.Form.Controls("RadioButton4").Checked Then
Dim y As Integer = d1.Year
Dim q As Integer = (d1.Month - 1) \\ 3 + 1 \'计算现在是第几个季度
Dim dt1 As New Date(y, 3 * (q - 1) + 1, 1) \'获取本季度的第一天
Dim dt2 As New Date(y, 3 * q, Date.DaysInMonth(y,3 * q)) \'获取本季度的最后一天

filter1 = "业务往来_送货日期 >= #" & dt1 & "# and 业务往来_送货日期 <= #" & dt2 & "#"
Else If e.Form.Controls("RadioButton5").Checked Then
Dim y As Integer = d1.Year
Dim dt1 As New Date(y, 1, 1)
Dim dt2 As New Date(y, 12, 31)

filter1 = "业务往来_送货日期 >= #" & dt1 & "# and 业务往来_送货日期 <= #" & dt2 & "#"
Else If e.Form.Controls("RadioButton6").Checked Then
filter1 = "1=1"
Else If e.Form.Controls("RadioButton07").Checked Then
filter1 = "业务往来_送货日期 < #" & d1 & "#"
End If

Dim c6 As String = e.Form.controls("ComboBox6").Value
If c6 <> "全部" Then
Dim tt As Table = e.Form.Controls("Table10").Table
If tt.Current IsNot Nothing Then
filter1 &= " and " & c6 & " = \'" & tt.Current(c6) & "\'"
End If
End If

Dim Chart As WinForm.Chart = e.Form.Controls("Chart1") \' 引用窗口中的图表
Dim series As winform.ChartSeries
Chart.SeriesList.Clear() \'清除图表原来的图系
Chart.ChartType = ChartTypeEnum.XYPlot
Chart.AxisX.ClearValueLabel
Dim tp As DateGroupEnum = 0
Dim cbx = e.Form.Controls("ComboBox11")
Select Case cbx.Text
Case "年"
tp = DateGroupEnum.Year
Case "季"
tp = DateGroupEnum.Quarter
Case "月"
tp = DateGroupEnum.month
Case "周"
tp = DateGroupEnum.Week
Case "天"
tp = DateGroupEnum.None
Case Else
tp = DateGroupEnum.None
End Select

Dim g As New GroupTableBuilder("临时统计表", DataTables("订单附表"))
g.Groups.AddDef("业务往来_送货日期", tp, "日期")
g.Totals.AddDef("订单核算_合计")
g.Totals.AddDef("订单核算_单项利润")
g.Totals.AddDef("订单核算_单项成本")
g.Totals.AddDef("订单核算_采购合计")
g.Totals.AddDef("订单核算_合计返点")
\'g.Filter = filter1 \'全部
g.filter = filter1 & " and 订单核算_合计 <> 0" \'显示大于0的
g.Build()

Dim t As Table = Tables("临时统计表")
t.Sort = "日期"
For i As Integer = 7 To 11
Dim c As WinForm.CheckBox = e.Form.Controls("CheckBox" & i)
If c.Checked Then
Series = Chart.SeriesList.Add() \'增加一个图系
Series.Length = t.Rows.Count
Series.TooltipText = c.Text & " : {#YVAL}"
Series.Text = c.Text
For r As Integer = 0 To t.Rows.Count - 1
Series.X(r) = r
Series.Y(r) = t.Rows(r)(c.Text)
Next
End If
Next

For r As Integer = 0 To t.Rows.Count - 1
Chart.AxisX.SetValueLabel(r, t.Rows(r)("日期")) \'指定字符表示
Next
Chart.AxisX.AnnoWithLabels = True \'启用字符标示
Chart.VisualEffect = True \'加上这一行,让你的图表更漂亮
Chart.LegendVisible = True \'显示图列
Chart.AxisX.AnnoRotation = - 45 \'X轴标示逆时针旋转45度
Chart.LegendCompass= CompassEnum.South \'图列显示在南方(底端)

[此贴子已经被作者于2016/3/17 19:45:17编辑过]

--  作者：大红袍
--  发布时间：2016/3/17 20:27:00
--

Dim g As New GroupTableBuilder("临时统计表", DataTables("订单附表"))

g.Groups.AddDef("业务往来_送货日期", DateGroupEnum.Year, "年")

g.Groups.AddDef("业务往来_送货日期", tp, "月")

g.Totals.AddDef("订单核算_合计")

g.Totals.AddDef("订单核算_单项利润")

g.Totals.AddDef("订单核算_单项成本")

g.Totals.AddDef("订单核算_采购合计")

g.Totals.AddDef("订单核算_合计返点")

\'g.Filter = filter1 \'全部

g.filter = filter1 & " and 订单核算_合计 <> 0" \'显示大于0的

g.Build()

Dim t As Table = Tables("临时统计表")

t.Sort = "年,月"

For i As Integer = 7 To 11

Dim c As WinForm.CheckBox = e.Form.Controls("CheckBox" & i)

If c.Checked Then

Series = Chart.SeriesList.Add() \'增加一个图系

Series.Length = t.Rows.Count

Series.TooltipText = c.Text & " : {#YVAL}"

Series.Text = c.Text

For r As Integer = 0 To t.Rows.Count - 1

Series.X(r) = r

Series.Y(r) = t.Rows(r)(c.Text)

Next

End If

Next

For r As Integer = 0 To t.Rows.Count - 1

Chart.AxisX.SetValueLabel(r, t.Rows(r)("年") & t.Rows(r)("月")) \'指定字符表示

Next

--  作者：yangwenghd
--  发布时间：2016/3/17 21:00:00
--

又要给你添麻烦了

下载信息  [文件大小：   下载次数： ]
点击浏览该文件:统计时间顺序不对.table

此主题相关图片如下：车上.jpg

--  作者：大红袍
--  发布时间：2016/3/17 21:34:00
--

汗，你根本就没看代码，全依靠别人

Dim g As New GroupTableBuilder("临时统计表", DataTables("表A"))
g.Groups.AddDef("订单日期", DateGroupEnum.Year, "年")
g.Groups.AddDef("订单日期", tp, "月")
g.Totals.AddDef("成本合计")
g.Totals.AddDef("利润合计")
g.Totals.AddDef("返点成本")
g.Totals.AddDef("采购成本")
g.Totals.AddDef("利润合计")
\'g.Filter = filter1 \'全部
g.filter = filter1 & " and 利润合计 <> 0" \'显示大于0的
g.Build()

Dim t As Table = Tables("临时统计表")
t.Sort = "年,月"
For i As Integer = 7 To 11
    Dim c As WinForm.CheckBox = e.Form.Controls("CheckBox" & i)
    If c.Checked Then
        Series = Chart.SeriesList.Add() \'增加一个图系
        Series.Length = t.Rows.Count
        Series.TooltipText = c.Text & " : {#YVAL}"
        Series.Text = c.Text
        For r As Integer = 0 To t.Rows.Count - 1
            Series.X(r) = r
            Series.Y(r) = t.Rows(r)(c.Text)
        Next
    End If
Next

For r As Integer = 0 To t.Rows.Count - 1
    Chart.AxisX.SetValueLabel(r, t.Rows(r)("年") & t.Rows(r)("月")) \'指定字符表示
Next
Chart.AxisX.AnnoWithLabels = True \'启用字符标示
Chart.VisualEffect = True \'加上这一行,让你的图表更漂亮
Chart.LegendVisible = True \'显示图列
Chart.AxisX.AnnoRotation = - 45 \'X轴标示逆时针旋转45度
Chart.LegendCompass= CompassEnum.South \'图列显示在南方(底端)

--  作者：yangwenghd
--  发布时间：2016/3/17 22:11:00
--
感谢感谢再请教一下想图表下面的字体数字可以变大吗？

--  作者：大红袍
--  发布时间：2016/3/17 22:46:00
--
chart.AxisX.Font = new font("宋体", 15)

--  作者：yangwenghd
--  发布时间：2016/3/17 23:04:00
--
感谢你感谢你谢谢你