- Foxtable(狐表) (http://foxtable.com/bbs/index.asp)
-- 专家坐堂 (http://foxtable.com/bbs/list.asp?boardid=2)
---- [求助]取值 (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=90561)
-- 发布时间：2016/9/17 10:13:00
-- [求助]取值
老师好，我要在"日期"列（日期型）2016-01-06中取201601赋值给日期1。
-- 发布时间：2016/9/17 10:38:00
--
Dim d As Date = #1/6/2016#
Dim d2 As Date = new Date(d.Year,d.Month,1)
Output.Show(d2)
-- 发布时间：2016/9/17 18:17:00
--
谢谢老师，下面代码的上月结存_金额列的代码怎样设置
DataTables("商品进出月结").DataRows.Clear
Dim Arys As List(Of String())
Arys = DataTables("进销存").GetValues("月|项目分类|商品名称|规格","月 >= \'" & e.Form.Controls("起始月").Text & "\' And 月 <= \'" & e.Form.Controls("截止月").Text & "\' ")
For Each Ary As String() In Arys
Dim r As Row = Tables("商品进出月结").AddNew
r("月") = Ary(0)
r("项目分类") = Ary(1)
r("商品名称") = Ary(2)
r("规格") = Ary(3)
Next
Dim dt As DataTable = DataTables("进销存")
For Each dr As DataRow In DataTables("商品进出月结").DataRows
Dim Filter As String = "项目分类 = \'" & dr("项目分类") & "\' and 商品名称 = \'" & dr("商品名称") & "\' and 规格 = \'" & dr("规格") & "\'"
Dim Sum,Sum1,P As Double
dr("本月入库_数量") = dt.Compute("Sum(入库_数量)",Filter & " And 月 = \'" & dr("月") & "\'")
dr("本月入库_金额") = dt.Compute("Sum(入库_金额)",Filter & " And 月 = \'" & dr("月") & "\'")
dr("本月出库_数量") = dt.Compute("Sum(出库_数量)",Filter & " And 月 = \'" & dr("月") & "\'")
dr("上月结存_数量") = dt.Compute("Sum(入库_数量)",Filter & " And 月 < \'" & dr("月") & "\'") - dt.Compute("Sum(出库_数量)",Filter & " And 月 < \'" & dr("月") & "\'")
Sum = DataTables("进销存").Compute("Sum(入库_金额)",Filter & " And 月 < \'" & dr("月") & "\'")
Sum1 = DataTables("进销存").Compute("Sum(入库_数量)",Filter & " And 月 < \'" & dr("月") & "\'")
Dim pdr As DataRow = DataTables("商品进出月结").find(filter & " And 月 < \'" & dr("月") & "\'", "月 desc")
If pdr IsNot Nothing Then
dr("上月结存_金额") = pdr("上月结存_金额")
Else
dr("上月结存_金额") = Sum / Sum1 * dr("本月结存_数量")
End If
dr("本月结存_数量") = dr("上月结存_数量") + dr("本月入库_数量") - dr("本月出库_数量")
dr("本月加权平均单价") = (P + dr("本月入库_金额"))/(dr("上月结存_数量") + dr("本月入库_数量"))
dr("本月出库_金额") = dr("本月加权平均单价") * dr("本月出库_数量")
dr("本月结存_金额") = dr("本月加权平均单价") * dr("本月结存_数量")
Next
[此贴子已经被作者于2016/9/17 21:10:49编辑过]
-- 发布时间：2016/9/18 8:34:00
--
具体什么问题，什么错误？
-- 发布时间：2016/9/18 8:59:00
--
老师，求"上月结存_金额"数据
| 下载信息 [文件大小： 下载次数： ] | |
 点击浏览该文件:复件 月库存.table |
-- 发布时间：2016/9/18 10:52:00
--
计算逻辑（公式）是怎样的？
-- 发布时间：2016/9/18 10:55:00
--
老师，设置时间段通过按钮代码得出上月和本月的数据。（代码在窗口按钮中）
[此贴子已经被作者于2016/9/18 10:55:33编辑过]
-- 发布时间：2016/9/18 11:41:00
--
DataTables("商品进出月结").DataRows.Clear
Dim Arys As List(Of String())
Arys = DataTables("进销存").GetValues("月|项目分类|商品名称|规格","月 >= \'" & e.Form.Controls("起始月").Text & "\' And 月 <= \'" & e.Form.Controls("截止月").Text & "\' ")
For Each Ary As String() In Arys
Dim r As Row = Tables("商品进出月结").AddNew
r("月") = Ary(0)
r("项目分类") = Ary(1)
r("商品名称") = Ary(2)
r("规格") = Ary(3)
Next
Dim dt As DataTable = DataTables("进销存")
For Each dr As DataRow In DataTables("商品进出月结").DataRows
Dim Filter As String = "项目分类 = \'" & dr("项目分类") & "\' and 商品名称 = \'" & dr("商品名称") & "\' and 规格 = \'" & dr("规格") & "\'"
Dim Sum,Sum1,P As Double
dr("本月入库_数量") = dt.Compute("Sum(入库_数量)",Filter & " And 月 = \'" & dr("月") & "\'")
dr("本月入库_金额") = dt.Compute("Sum(入库_金额)",Filter & " And 月 = \'" & dr("月") & "\'")
dr("本月出库_数量") = dt.Compute("Sum(出库_数量)",Filter & " And 月 = \'" & dr("月") & "\'")
Dim fd As Date = new Date(dr("月").SubString(0,4),dr("月").SubString(4),1).AddMonths(-1)
Dim fy As String = Format(fd,"yyyyMM")
\'Sum1 = DataTables("进销存").Compute("Sum(入库_数量)",Filter & " And 月 = \'" & fy & "\'")
\'dr("上月结存_数量") = Sum1 - dt.Compute("Sum(出库_数量)",Filter & " And 月 = \'" & fy & "\'")
\'Sum = DataTables("进销存").Compute("Sum(入库_金额)",Filter & " And 月 = \'" & fy & "\'")
\'============================================================================================
Dim pdr As DataRow = DataTables("商品进出月结").find(filter & " And 月 = \'" & fy & "\'", "月 desc")
If pdr IsNot Nothing Then
dr("上月结存_金额") = pdr("本月结存_金额")
dr("上月结存_数量") = pdr("本月结存_数量")
End If
\'Dim pdr As DataRow = DataTables("商品进出月结").find(filter & " And 月 < \'" & dr("月") & "\'", "月 desc")
\' If pdr IsNot Nothing Then
\' dr("上月结存_金额") = pdr("上月结存_金额")
\'End If
dr("本月结存_数量") = dr("上月结存_数量") + dr("本月入库_数量") - dr("本月出库_数量")
dr("本月加权平均单价") = (P + dr("本月入库_金额"))/(dr("上月结存_数量") + dr("本月入库_数量"))
dr("本月出库_金额") = dr("本月加权平均单价") * dr("本月出库_数量")
dr("本月结存_金额") = dr("本月加权平均单价") * dr("本月结存_数量")
\'dr("本月结存_金额") = dr("上月结存_金额") + dr("本月入库_金额") - dr("本月出库_金额")
Next
-- 发布时间：2016/9/18 20:53:00
--
老师好，通过修改代码让"商品进出月结"表"月"列和"进销存"表"月"能否改成日期型的属性。
-- 发布时间：2016/9/19 8:45:00
--
改不了，另外创建一个列吧