专家坐堂-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=134832)

--  作者：有点甜
--  发布时间：2019/5/10 11:04:00
--

DataTables("月结").DataRows.Clear
Dim dt As DataTable = DataTables("出入库明细")
Dim str As String = e.Form.Controls("月").Text
Dim d As Date = new Date(str.SubString(0, 4), str.SubString(4,2), 1)
Dim ls As new List(Of String)
For Each dr As DataRow In dt.Select("月 = \'" & str & "\'")
    Dim s = dr("成品编码") & "|" & dr("库位")
    If ls.Contains(s) = False Then
        ls.add(s)
    End If
Next

For Each dr As DataRow In DataTables("期初数据").Select("月 = \'" & Format(d.AddMonths(-1), "yyyyMM") & "\'")
    Dim s = dr("成品编码") & "|" & dr("库位")
    If ls.Contains(s) = False Then
        ls.add(s)
    End If
Next
For Each cl As String In ls
    Dim cl1 = cl.split("|")(0)
    Dim cl2 = cl.split("|")(1)
    Dim dr As DataRow = DataTables("月结").AddNew
    dr("月") = str
    dr("成品编码") = cl1
    dr("库位") = cl2
    Dim Filter As String = "成品编码 = \'" & cl1 & "\' And 月 = \'" & str & "\' and 库位 = \'" & cl2 & "\'"
    Dim dr1 As DataRow = DataTables("期初数据").SQLFind("成品编码=\'" & cl1 & "\' and 月 = \'" & Format(d.AddMonths(-1), "yyyyMM") & "\' and 库位 = \'" & cl2 & "\'")
    If dr1 IsNot Nothing Then
        dr("上月结存_A类") = dr1("期初数量_A类")
        dr("上月结存_B类") = dr1("期初数量_B类")
    End If
    dr("本月入库_A类") = dt.SQLCompute("Sum(入库数量)",Filter & " And 月 = \'" & dr("月") & "\'And 入库类型 = \'A类\'")
    dr("本月入库_B类") = dt.SQLCompute("Sum(入库数量)",Filter & " And 月 = \'" & dr("月") & "\'And 入库类型 = \'B类\'")
    dr("本月出库_A类") = dt.SQLCompute("Sum(出库数量)",Filter & " And 月 = \'" & dr("月") & "\'And 出库类型 = \'A类\'")
    dr("本月出库_B类") = dt.SQLCompute("Sum(出库数量)",Filter & " And 月 = \'" & dr("月") & "\'And 出库类型 = \'B类\'")
    dr("本月结存_A类") = dr("上月结存_A类") + dr("本月入库_A类") - dr("本月出库_A类")
    dr("本月结存_B类") = dr("上月结存_B类") + dr("本月入库_B类") - dr("本月出库_B类")
Next

--  作者：有点甜
--  发布时间：2019/5/10 14:40:00
--

DataTables("月结").DataRows.Clear
Dim dt As DataTable = DataTables("出入库明细")
Dim str As String = e.Form.Controls("月").Text
Dim kw As String = e.Form.Controls("库位").Text
Dim d As Date = new Date(str.SubString(0, 4), str.SubString(4,2), 1)
Dim ls As new List(Of String)
For Each dr As DataRow In dt.Select("月 = \'" & str & "\' and 库位 = \'" & kw & "\'")
    Dim s = dr("成品编码") & "|" & dr("库位")
    If ls.Contains(s) = False Then
        ls.add(s)
    End If
Next

For Each dr As DataRow In DataTables("期初数据").Select("月 = \'" & Format(d.AddMonths(-1), "yyyyMM") & "\' and 库位 = \'" & kw & "\'")
    Dim s = dr("成品编码") & "|" & dr("库位")
    If ls.Contains(s) = False Then
        ls.add(s)
    End If
Next
For Each cl As String In ls
    Dim cl1 = cl.split("|")(0)
    Dim cl2 = cl.split("|")(1)
    Dim dr As DataRow = DataTables("月结").AddNew
    dr("月") = str
    dr("成品编码") = cl1
    dr("库位") = cl2
    Dim Filter As String = "成品编码 = \'" & cl1 & "\' And 月 = \'" & str & "\' and 库位 = \'" & cl2 & "\'"
    Dim dr1 As DataRow = DataTables("期初数据").SQLFind("成品编码=\'" & cl1 & "\' and 月 = \'" & Format(d.AddMonths(-1), "yyyyMM") & "\' and 库位 = \'" & cl2 & "\'")
    If dr1 IsNot Nothing Then
        dr("上月结存_A类") = dr1("期初数量_A类")
        dr("上月结存_B类") = dr1("期初数量_B类")
    End If
    dr("本月入库_A类") = dt.SQLCompute("Sum(入库数量)",Filter & " And 月 = \'" & dr("月") & "\'And 入库类型 = \'A类\'")
    dr("本月入库_B类") = dt.SQLCompute("Sum(入库数量)",Filter & " And 月 = \'" & dr("月") & "\'And 入库类型 = \'B类\'")
    dr("本月出库_A类") = dt.SQLCompute("Sum(出库数量)",Filter & " And 月 = \'" & dr("月") & "\'And 出库类型 = \'A类\'")
    dr("本月出库_B类") = dt.SQLCompute("Sum(出库数量)",Filter & " And 月 = \'" & dr("月") & "\'And 出库类型 = \'B类\'")
    dr("本月结存_A类") = dr("上月结存_A类") + dr("本月入库_A类") - dr("本月出库_A类")
    dr("本月结存_B类") = dr("上月结存_B类") + dr("本月入库_B类") - dr("本月出库_B类")
Next

--  作者：有点甜
--  发布时间：2019/5/29 11:10:00
--
上传具体实例测试

--  作者：有点蓝
--  发布时间：2019/7/5 16:29:00
--
做这么久都学不会调试么

DataTables("成品库存表").DataRows.Clear
Dim dt As DataTable = DataTables("出入库明细")
Dim str As String = e.Form.Controls("月").Text
Dim kw As String = e.Form.Controls("库位1").Text
Dim d As Date = new Date(str.SubString(0, 4), str.SubString(4,2), 1)
msgbox(d)
Dim ls As new List(Of String)
For Each dr As DataRow In dt.Select("月 = \'" & str & "\' and 库位 = \'" & kw & "\'")
Dim s = dr("成品编码") & "|" & dr("库位")
If ls.Contains(s) = False Then
ls.add(s)
End If
Next
msgbox(ls.Count)
For Each dr As DataRow In DataTables("期初数据").Select("月 = \'" & Format(d.AddMonths(-1), "yyyyMM") & "\' and 库位 = \'" & kw & "\'")
Dim s = dr("成品编码") & "|" & dr("库位")
If ls.Contains(s) = False Then
ls.add(s)
End If
Next
msgbox(ls.Count)
For Each cl As String In ls
Dim cl1 = cl.split("|")(0)
msgbox(cl1)
Dim cl2 = cl.split("|")(1)
msgbox(cl2)
Dim dr As DataRow = DataTables("成品库结存_月结").AddNew
dr("月") = str
dr("成品编码") = cl1
dr("库位") = cl2
Dim Filter As String = "成品编码 = \'" & cl1 & "\' And 月 = \'" & str & "\' and 库位 = \'" & cl2 & "\'"
Dim dr1 As DataRow = DataTables("期初数据").SQLFind("成品编码=\'" & cl1 & "\' and 月 = \'" & Format(d.AddMonths(-1), "yyyyMM") & "\' and 库位 = \'" & cl2 & "\'")
msgbox(dr1 IsNot Nothing )
If dr1 IsNot Nothing Then
dr("上月结存_A类") = dr1("期初数量_A类")
dr("上月结存_B类") = dr1("期初数量_B类")
End If
Next

--  作者：有点蓝
--  发布时间：2019/7/5 17:06:00
--
选择的是201905，怎么可能弹出2019-06-01？继续调试啊，不要推一下才走一步啊

DataTables("成品库存表").DataRows.Clear
Dim dt As DataTable = DataTables("出入库明细")
Dim str As String = e.Form.Controls("月").Text
msgbox(str)
msgbox(str.SubString(0, 4))
msgbox(str.SubString(4,2))
Dim kw As String = e.Form.Controls("库位1").Text
Dim d As Date = new Date(str.SubString(0, 4), str.SubString(4,2), 1)
msgbox(d)

[此贴子已经被作者于2019/7/5 17:06:49编辑过]