日志

多種存貨計價方式集成

熱度 3已有 3715 次閱讀2009-4-23 13:19 |個人分類:習(xí)作|

Private Sub 計算_Click()
If IsNull(Me.物資ID.Value) Then Exit Sub
Select Case Me.選項
Case 1 '先進(jìn)先出法
      先后法
Case 2 '后進(jìn)先出法
      先后法
Case 3 '移動平均法
      移動法
Case 4 '加權(quán)平均法
      加權(quán)法
End Select
Me.收發(fā)存子窗體.Form.Requery
End Sub

Private Sub 先后法()
Dim rs1 As New ADODB.Recordset
Dim sql1 As String
Dim rs2 As New ADODB.Recordset
Dim sql2 As String
Dim 收料數(shù)量 As Single
Dim 發(fā)料數(shù)量 As Single
Dim 累計金額 As Single
Dim 收發(fā)余額 As Single
sql1 = "select * from 發(fā)料查詢 where format(日期,'yymm')='" & Me.月度.Value & "'and 物資ID=" & Me.物資ID.Value
rs1.Open sql1, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If Me.選項.Value <> 2 Then
sql2 = "select * from 庫存及收料查詢 where format(日期,'yymm')='" & Me.月度.Value & "'and 物資ID=" & Me.物資ID.Value
Else
sql2 = "select * from 庫存及收料查詢 where format(日期,'yymm')='" & Me.月度.Value & "'and 物資ID=" & Me.物資ID.Value & " ORDER BY 編號 DESC"
End If
rs2.Open sql2, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
收發(fā)余額 = 0
Do While Not (rs1.EOF)
If 收發(fā)余額 > 0 Then
      收料數(shù)量 = 收發(fā)余額
      收發(fā)余額 = 0
Else
      收料數(shù)量 = rs2("數(shù)量")
End If
If rs1("數(shù)量") < 收料數(shù)量 Then
      rs1("單價") = rs2("單價")
      rs1("金額") = rs1("數(shù)量") * rs1("單價")
      rs1.Update
      收發(fā)余額 = 收料數(shù)量 - rs1("數(shù)量")
Else
      累計金額 = 0
      發(fā)料數(shù)量 = rs1("數(shù)量")
      Do While Not (rs2.EOF)
         累計金額 = 累計金額 + 收料數(shù)量 * rs2("單價")
         發(fā)料數(shù)量 = 發(fā)料數(shù)量 - 收料數(shù)量
         rs2.MoveNext
         收料數(shù)量 = rs2("數(shù)量")
         If 發(fā)料數(shù)量 < 收料數(shù)量 Then
            累計金額 = 累計金額 + 發(fā)料數(shù)量 * rs2("單價")
            rs1("金額") = 累計金額
            rs1("單價") = Round(rs1("金額") / rs1("數(shù)量"), 2)
            rs1.Update
            收發(fā)余額 = rs2("數(shù)量") - 發(fā)料數(shù)量
            Exit Do
         End If
      Loop
End If
rs1.MoveNext
Loop
Me.收發(fā)子窗體.Form.Requery
rs1.Close
rs2.Close
End Sub

Private Sub 移動法()
Dim rs1 As New ADODB.Recordset
Dim sql1 As String
Dim rs2 As New ADODB.Recordset
Dim sql2 As String
Dim 累計數(shù)量 As Single
Dim 累計金額 As Single
Dim 庫存數(shù)量 As Single
Dim 庫存金額 As Single
Dim i As Long
sql1 = "select * from 發(fā)料查詢 where format(日期,'yymm')='" & Me.月度.Value & "'and 物資ID=" & Me.物資ID.Value
rs1.Open sql1, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
sql2 = "select * from 庫存及收料查詢 where format(日期,'yymm')='" & Me.月度.Value & "'and 物資ID=" & Me.物資ID.Value
rs2.Open sql2, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
庫存數(shù)量 = 0
庫存金額 = 0
For i = 1 To rs1.RecordCount
累計數(shù)量 = 庫存數(shù)量
累計金額 = 庫存金額
Do While Not (rs2.EOF)
      If rs2("編號").Value < rs1("編號").Value Then
         累計數(shù)量 = 累計數(shù)量 + rs2("數(shù)量")
         累計金額 = 累計金額 + rs2("金額")
         rs2.MoveNext
      Else
         rs2.MovePrevious
         Exit Do
      End If
Loop
      rs1("單價") = Round(累計金額 / 累計數(shù)量, 2)
      rs1("金額") = rs1("數(shù)量") * rs1("單價")
      rs1.Update
      庫存數(shù)量 = 累計數(shù)量 - rs1("數(shù)量")
      庫存金額 = 累計金額 - rs1("金額")
If rs2.EOF = False Then rs2.MoveNext
rs1.MoveNext
Next
Me.收發(fā)子窗體.Form.Requery
rs1.Close
rs2.Close
End Sub

Private Sub 加權(quán)法()
Dim rs1 As New ADODB.Recordset
Dim sql1 As String
Dim rs2 As New ADODB.Recordset
Dim sql2 As String
Dim 累計數(shù)量 As Single
Dim 累計金額 As Single
Dim 單價 As Single
sql1 = "select * from 發(fā)料查詢 where format(日期,'yymm')='" & Me.月度.Value & "'and 物資ID=" & Me.物資ID.Value
rs1.Open sql1, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
sql2 = "select * from 庫存及收料查詢 where format(日期,'yymm')='" & Me.月度.Value & "'and 物資ID=" & Me.物資ID.Value
rs2.Open sql2, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
累計數(shù)量 = 0
累計金額 = 0
For i = 1 To rs2.RecordCount
累計數(shù)量 = 累計數(shù)量 + rs2("數(shù)量")
累計金額 = 累計金額 + rs2("金額")
rs2.MoveNext
Next
單價 = Round(累計金額 / 累計數(shù)量, 2)
For i = 1 To rs1.RecordCount
rs1("單價") = 單價
rs1("金額") = rs1("數(shù)量") * rs1("單價")
rs1.Update
rs1.MoveNext
Next
Me.收發(fā)子窗體.Form.Requery
rs1.Close
rs2.Close
End Sub