主题：库存先进先出

参考

 

http://www.foxtable.com/bbs/dispbbs.asp?BoardID=2&ID=97084&skin=0

 

Select Case e.DataCol.name
    Case "商品编号", "数量"
        If e.DataRow("数量") IsNot Nothing Then
            Dim r As DataRow = e.DataRow
            Dim sum As Double = DataTables("进").compute("sum(库存)", "商品编号 = '" & e.DataRow("商品编号") & "' And 库存状态 = '可出库'")
            If sum < r("数量") Then
                msgbox("库存不够")
            Else
                Dim count As Integer = 0
                Dim drs As List(Of DataRow) = DataTables("进").Select("商品编号 = '" & e.DataRow("商品编号") & "' And 库存状态 = '可出库'", "日期, _Identify")
                Dim s As String = ""
                For i As Integer = 0 To drs.count - 1
                    Dim dr As DataRow = drs(i)
                    count += dr("库存")
                    Dim sy As Integer = count - r("数量")
                    s &= dr("进货单号") & ","
                    If sy >= 0 Then
                        dr("出库") = dr("数量") - sy
                        dr("库存") = sy
                        r("进货单号") = s.trim(",")
                        Exit For
                    Else
                        dr("出库") = dr("数量")
                        dr("库存") = 0
                    End If
                Next
            End If
        End If
End Select

                For i As Integer = 0 To drs.count - 1    '循环查找到的数据
                    Dim dr As DataRow = drs(i)            
                    count += dr("库存")                        '用count记录累加的库存
                    Dim sy As Integer = count - r("数量")     '如果累加库存减去数量
                    s &= dr("进货单号") & ","                  '用s记录进货单号
                    If sy >= 0 Then                              '如果累加库存大于数量，说明足够出库
                        dr("出库") = dr("数量") - sy           '数量减去剩余就是出库
                        dr("库存") = sy                         
                        r("进货单号") = s.trim(",")             '赋值
                        Exit For
                    Else                                                '库存不够的时候
                        dr("出库") = dr("数量")                  
                        dr("库存") = 0
                    End If
                Next

 成本也是同样计算，用一个变量记录每一行的成本，累加就是答案。

Select Case e.DataCol.name
    Case "商品编号", "数量"
        If e.DataRow("数量") IsNot Nothing Then
            Dim r As DataRow = e.DataRow
            Dim sum As Double = DataTables("进").compute("sum(库存)", "商品编号 = '" & e.DataRow("商品编号") & "' And 库存状态 = '可出库'")
            If sum < r("数量") Then
                msgbox("库存不够")
            Else
                Dim count As Integer = 0
                Dim cb As Double = 0
                Dim drs As List(Of DataRow) = DataTables("进").Select("商品编号 = '" & e.DataRow("商品编号") & "' And 库存状态 = '可出库'", "日期, _Identify")
                Dim s As String = ""
                For i As Integer = 0 To drs.count - 1
                    Dim dr As DataRow = drs(i)
                    count += dr("库存")
                    Dim sy As Integer = count - r("数量")
                    s &= dr("进货单号") & ","
                    If sy >= 0 Then
                        dr("出库") = dr("数量") - sy
                        cb += dr("单价") * dr("出库")
                        dr("库存") = sy
                        r("进货单号") = s.trim(",")
                        r("成本") = cb
                        Exit For
                    Else
                        dr("出库") = dr("数量")
                        dr("库存") = 0
                        cb += dr("单价") * dr("出库")
                    End If
                Next
            End If
        End If
End Select

 

多用户操作的时候，用改成sqlfind，sqlselect，同时你修改进销存的时候，要实时保存记录。

 

http://www.foxtable.com/webhelp/scr/2911.htm