主题：[求助]汇总

 做一个窗口，发上来。

下载信息  [文件大小：   下载次数： ]

点击浏览该文件:管理项目1.rar

 

输入的数据，保存后才会汇总，汇总的时候，只汇总对应日期的数据。

 代码

 

With e.Form.Controls("日期")
    If .Value Is Nothing Then
        Return
    End If
End With
Dim tname As String = "统计_Table1"
Tables(tname ).StopRedraw

Dim filter As String
Dim curD As Date=e.Form.Controls("日期").Value '当前日期
Dim lastD As Date = curD.AddMonths(-1) '上一个月的日期
Dim lastD2 As New Date(lastD.Year, lastD.Month, Date.DaysInMonth(lastD.Year,lastD.Month)) '上个月的最后一天

Dim bd1 As New SQLGroupTableBuilder("统计表1","入库明细")
Dim dt1 As fxDataSource
bd1.Groups.AddDef("规格") '根据型号分组
bd1.Totals.AddDef("入库数量","入库_数量") '对数量进行统计
bd1.Totals.AddDef("价税合计","入库_金额") '对金额进行统计
Dim Filter1 As String  = " 入库日期 > #" & lastD2 & "#" & " And 入库日期  <= #" & curD & "#"
bd1.Filter = Filter1
dt1 = bd1.BuildDataSource()

Dim bd2 As New SQLGroupTableBuilder("统计表2","出库明细")
Dim dt2  As fxDataSource
bd2.Groups.AddDef("规格") '根据规格分组
bd2.Totals.AddDef("出库数量","销售_数量") '对数量进行统计
bd2.Totals.AddDef("价税合计","销售_金额") '对金额进行统计
Dim Filter2 As String =  "出库日期 > #" & lastD2 & "#" & " And 出库日期  <= #" & curD & "#"
bd2.Filter = Filter2
dt2 = bd2.BuildDataSource()

Dim bd3 As New SQLGroupTableBuilder("统计表3","销售退货")
Dim dt3 As fxDataSource
bd3.Groups.AddDef("规格") '根据规格分组
bd3.Totals.AddDef("退货_数量","退货_数量") '对数量进行统计
bd3.Totals.AddDef("退货_金额","退货_金额") '对金额进行统计
Dim Filter3 As String =  "退货日期 > #" & lastD2 & "#" & " And 退货日期  <= #" & curD & "#"
bd3.Filter = Filter3
dt3 = bd3.BuildDataSource()
dt1.Combine("规格",dt2,"规格") '将销售统计数据组合到进货统计数据
dt1.Combine("规格",dt3,"规格") '将退货统计数据组合到进货统计数据
Tables(tname).DataSource = dt1 '将统计结果绑定到Table

With DataTables(tname).DataCols  '用表达式列计算库存数据
    .Add("期初_数量",Gettype(Double),"","期初_数量")
    .Add("期初_金额",Gettype(Double),"","期初_金额")
End With

Filter1 = "入库日期 <=  #" & lastD2 & "#"
Filter2 = "出库日期 <=  #" & lastD2 & "#"
Filter3 = "销售日期 <=  #" & lastD2 & "#"
Dim dt_jh As DataTable
Dim dt_xs As DataTable
Dim dt_th As DataTable
Dim cmd As new SQLCommand
cmd.CommandText = "select * from {入库明细} where " & Filter1
dt_jh = cmd.ExecuteReader
cmd.CommandText = "select * from {出库明细} where " & Filter2
dt_xs = cmd.ExecuteReader
cmd.CommandText = "select * from {销售退货} where " & Filter3
dt_th = cmd.ExecuteReader

For Each r As Row In Tables(tname).Rows   
    Filter = "规格 = '" & r("规格") & "'"
    r("期初_数量") = dt_jh.Compute("sum(入库数量)", filter) - dt_xs.Compute("sum(出库数量)", filter) - dt_th.Compute("sum(退货_数量)", filter)
    r("期初_金额") = dt_jh.Compute("sum(价税合计)", filter) - dt_xs.Compute("sum(价税合计)", filter) - dt_th.Compute("sum(退货_金额)", filter)
Next

With DataTables(tname).DataCols  '用表达式列计算库存数据
    .Add("库存_数量",Gettype(Double), "IsNull([期初_数量],0) + IsNull([入库_数量],0) - ISNULL([销售_数量],0) - ISNULL([退货_数量],0)","库存_数量")
    .Add("库存_金额",Gettype(Double), "IsNull([期初_金额],0) + IsNull([入库_金额],0) - ISNULL([销售_金额],0) - ISNULL([退货_金额],0)","库存_金额")
End With

Tables(tname).DefaultRowHeight = 35
DataTables(tname).DataCols("期初_数量").SetFormat("#,###.00")
DataTables(tname).DataCols("期初_金额").SetFormat("#,###.00")
DataTables(tname).DataCols("销售_数量").SetFormat("#,###.00")
DataTables(tname).DataCols("销售_金额").SetFormat("#,###.00")
DataTables(tname).DataCols("退货_数量").SetFormat("#,###.00")
DataTables(tname).DataCols("退货_金额").SetFormat("#,###.00")
DataTables(tname).DataCols("库存_数量").SetFormat("#,###.00")
DataTables(tname).DataCols("库存_金额").SetFormat("#,###.00")
Tables(tname).ResumeRedraw

Filter1 = "入库日期 <=  #" & lastD2 & "#"
Filter2 = "出库日期 <=  #" & lastD2 & "#"
Filter3 = "退货日期 <=  #" & lastD2 & "#"

呃，这个例子的期初金额和数量，是计算入库、出库和退货后得出的，不是根据期初表。

 这个例子不计算，期初数据都是根据入库、出库和退货最后计算得出的，如果你要加入期初的数据，你先看懂和理解之前的的代码吧。

 

 如果你的期初的数据是不需要计算得出，而是前面就计算好的话，代码要改，比计算的简单。