- Foxtable(狐表) (http://foxtable.com/bbs/index.asp)
-- 专家坐堂 (http://foxtable.com/bbs/list.asp?boardid=2)
---- 统计问题 (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=106852)
-- 发布时间：2017/9/16 17:13:00
-- 统计问题
希望在一个窗口的两个日期输入框中输入日期（默认为本月）范围后，点击一个按钮，能够统计"开票明细"表中的"产品"，“型号”，不重复的“规格”，的“数量”和“金额”。到“产成品收付”表中的“本月付出_数量”和“本月付出_金额”里。而且是合并模式下“产品”“型号”为升序。日期为降序。并且自动填入上月的到“上月付出_数量”“上月付出_金额”
-- 发布时间：2017/9/16 17:41:00
--
请上传具体例子说明
-- 发布时间：2017/9/18 15:20:00
-- ...
| 下载信息 [文件大小： 下载次数： ] | |
 点击浏览该文件:开票管理.table |
我尝试了先刷选开票明细表，然后在从明细表生成统计表，但是统计出来的总是所有。刷选没起到作用。还尝试了统计副本表，也失败了.....
-- 发布时间：2017/9/18 15:55:00
--
参考代码
Dim dt As DataTable = DataTables("产成品收付存")
dt.DataRows.Clear
Dim dt1 As DataTable = DataTables("开票明细")
Dim d1 As Date = new Date(Date.Today.year, Date.Today.Month,1)
Dim d2 As Date = d1.AddMonths(1)
For Each ary() As String In dt1.GetValues("型号|规格|单位")
Dim ndr As DataRow = dt.AddNew
ndr("型号") = ary(0)
ndr("规格") = ary(1)
ndr("单位") = ary(2)
Dim filter As String = "型号 = \'" & ary(0) & "\' and 规格 = \'" & ary(1) & "\' and 单位 = \'" & ary(2) & "\'"
filter = filter.Replace("*", "[*]")
output.show(filter & " and 开票日期 < #" & d1 & "#")
Dim psum_sl As Double = dt1.Compute("sum(数量)", filter & " and 开票日期 < #" & d1 & "#")
Dim sum_sl_in As Double = dt1.Compute("sum(数量)", filter & " and 开票日期 >= #" & d1 & "# and 开票日期 < #" & d2 & "# and 数量 >= 0")
Dim sum_sl_out As Double = dt1.Compute("sum(数量)", filter & " and 开票日期 >= #" & d1 & "# and 开票日期 < #" & d2 & "# and 数量 <= 0")
ndr("上月结存_数量") = psum_sl
ndr("本月收进_数量") = sum_sl_in
ndr("本月付出_数量") = sum_sl_out
Next
-- 发布时间：2017/9/19 13:40:00
-- 出现了这个问题
此主题相关图片如下：qq截图20170919133805.png
-- 发布时间：2017/9/19 15:18:00
--
代码我测试没问题，如果选日期，修改红色代码即可
Dim dt As DataTable = DataTables("产成品收付存")
dt.DataRows.Clear
Dim dt1 As DataTable = DataTables("开票明细")
Dim d1 As Date = new Date(Date.Today.year, Date.Today.Month,1)
Dim d2 As Date = d1.AddMonths(1)
For Each ary() As String In dt1.GetValues("型号|规格|单位")
Dim ndr As DataRow = dt.AddNew
ndr("型号") = ary(0)
ndr("规格") = ary(1)
ndr("单位") = ary(2)
Dim filter As String = "型号 = \'" & ary(0) & "\' and 规格 = \'" & ary(1) & "\' and 单位 = \'" & ary(2) & "\'"
filter = filter.Replace("*", "[*]")
output.show(filter & " and 开票日期 < #" & d1 & "#")
Dim psum_sl As Double = dt1.Compute("sum(数量)", filter & " and 开票日期 < #" & d1 & "#")
Dim sum_sl_in As Double = dt1.Compute("sum(数量)", filter & " and 开票日期 >= #" & d1 & "# and 开票日期 < #" & d2 & "# and 数量 >= 0")
Dim sum_sl_out As Double = dt1.Compute("sum(数量)", filter & " and 开票日期 >= #" & d1 & "# and 开票日期 < #" & d2 & "# and 数量 <= 0")
ndr("上月结存_数量") = psum_sl
ndr("本月收进_数量") = sum_sl_in
ndr("本月付出_数量") = sum_sl_out
Next