20-06-20, 02:31 PM
عندى مشكلة فى حساب متوسط التكلفة وهى ان الكود الاتى بيجيب متوسط السعر لكل الكميات التى اشتريتها للصنف من البداية حتى لو تم بيعها ويحسب اجمالى الكميات التى تم شرائها واجمالى الاسعار التى تم الشراء بها ويقسم اجمالى السعر / اجمالى الكمية ويظهر متوسط التكلفة
المطلوب هو ان يكون المتوسط يتم عملة مثلا على اخر سعرين شراء فقط او لمده قليلة فقط وليس من بداية المشتريات
يعنى مثلا يجيب اخر فاتورتين شراء تم شراء الصنف بهم ويحدد متوسط السعر على اساسهم
ولكن لم استطيع تنفيذ هذا اترك الكود للاطلاع ولو ممكن تعديل
الكود كامل الخاص بحساب المخزون وحساب متوسط التكلفة
الكود كامل
المشكلة
اسف للاطالة وشاكرين ومقدرين حسن تعاونكم ومجهودكم الرائع
المطلوب هو ان يكون المتوسط يتم عملة مثلا على اخر سعرين شراء فقط او لمده قليلة فقط وليس من بداية المشتريات
يعنى مثلا يجيب اخر فاتورتين شراء تم شراء الصنف بهم ويحدد متوسط السعر على اساسهم
ولكن لم استطيع تنفيذ هذا اترك الكود للاطلاع ولو ممكن تعديل
الكود كامل الخاص بحساب المخزون وحساب متوسط التكلفة
الكود كامل
كود :
Private Sub CalcStock()
Try
Dim _FirstVal As Double = 0
Dim _PurchVal As Double = 0
Dim _RetPurchVal As Double = 0
Dim _SaleVal As Double = 0
Dim _RetSaleVal As Double = 0
Dim dtt As New DataTable
dtt.Columns.Add("currency")
dtt.Columns.Add("sum")
dtt.Columns.Add("type")
Dim InvSafeCond As String = ""
Dim TransfFromSafeCond As String = ""
Dim TransfToSafeCond As String = ""
If chkAll.Checked = False Then
InvSafeCond = " inv.safe=" & cmbSafes.SelectedValue & " and "
End If
Dim CondBranch As String = ""
If MainClass.BranchNo <> -1 Then
CondBranch = "branch=" & MainClass.BranchNo & " and "
End If
'مخزون أول المدة
Dim da As New SqlDataAdapter("select currency_from,sum(val) from inv,inv_sub where " & CondBranch & InvSafeCond & " inv.proc_type=3 and inv.proc_id=inv_sub.proc_id and IS_Deleted=0 group by currency_from", conn)
Dim dt As New DataTable
da.Fill(dt)
For i = 0 To dt.Rows.Count - 1
dtt.Rows.Add(dt.Rows(i)(0), Val("" & dt.Rows(i)(1)), 1)
Next
'مشتريات
da = New SqlDataAdapter("select currency_from,sum(val) from inv,inv_sub where " & CondBranch & InvSafeCond & " inv.proc_type=1 and inv_sub.proc_type=1 and inv.proc_id=inv_sub.proc_id and IS_Deleted=0 group by currency_from", conn)
dt = New DataTable
da.Fill(dt)
For i = 0 To dt.Rows.Count - 1
dtt.Rows.Add(dt.Rows(i)(0), Val("" & dt.Rows(i)(1)), 1)
Next
'مرتد مشتريات
da = New SqlDataAdapter("select currency_from,sum(val) from inv,inv_sub where " & CondBranch & InvSafeCond & " inv.proc_type=2 and inv_sub.proc_type=1 and inv.proc_id=inv_sub.proc_id and IS_Deleted=0 group by currency_from", conn)
dt = New DataTable
da.Fill(dt)
For i = 0 To dt.Rows.Count - 1
dtt.Rows.Add(dt.Rows(i)(0), Val("" & dt.Rows(i)(1)), 2)
Next
'مبيعات
da = New SqlDataAdapter("select currency_from,sum(val) from inv,inv_sub where " & CondBranch & InvSafeCond & " inv.proc_type=1 and inv_sub.proc_type=2 and inv.proc_id=inv_sub.proc_id and IS_Deleted=0 group by currency_from", conn)
dt = New DataTable
da.Fill(dt)
For i = 0 To dt.Rows.Count - 1
dtt.Rows.Add(dt.Rows(i)(0), Val("" & dt.Rows(i)(1)), 2)
Next
'مرتد مبيعات
da = New SqlDataAdapter("select currency_from,sum(val) from inv,inv_sub where " & CondBranch & InvSafeCond & " inv.proc_type=2 and inv_sub.proc_type=2 and inv.proc_id=inv_sub.proc_id and IS_Deleted=0 group by currency_from", conn)
dt = New DataTable
da.Fill(dt)
For i = 0 To dt.Rows.Count - 1
dtt.Rows.Add(dt.Rows(i)(0), Val("" & dt.Rows(i)(1)), 1)
Next
If chkAll.Checked = False Then
'محول اليه
da = New SqlDataAdapter("select currency,sum(value) from SafesTransfer,SafesTransfer_Sub where " & CondBranch & " SafesTransfer.safe_to=" & cmbSafes.SelectedValue & " and SafesTransfer.id=SafesTransfer_Sub.transfer_id and IS_Deleted=0 group by currency", conn)
dt = New DataTable
da.Fill(dt)
For i = 0 To dt.Rows.Count - 1
dtt.Rows.Add(dt.Rows(i)(0), Val("" & dt.Rows(i)(1)), 1)
Next
'محول منه
da = New SqlDataAdapter("select currency,sum(value) from SafesTransfer,SafesTransfer_Sub where " & CondBranch & " SafesTransfer.safe_from=" & cmbSafes.SelectedValue & " and SafesTransfer.id=SafesTransfer_Sub.transfer_id and IS_Deleted=0 group by currency", conn)
dt = New DataTable
da.Fill(dt)
For i = 0 To dt.Rows.Count - 1
dtt.Rows.Add(dt.Rows(i)(0), Val("" & dt.Rows(i)(1)), 2)
Next
End If
Dim _count As Integer = dtt.Rows.Count - 1
For i = 0 To _count
If i <= _count Then
If dtt.Rows(i)(2) = 2 Then
dtt.Rows(i)(1) = -Val(dtt.Rows(i)(1))
End If
End If
For j = i + 1 To _count
If j <= _count Then
If dtt.Rows(i)(0) = dtt.Rows(j)(0) Then
If dtt.Rows(j)(2) = 2 Then
dtt.Rows(j)(1) = -Val(dtt.Rows(j)(1))
End If
dtt.Rows(i)(1) = Val(dtt.Rows(i)(1)) + Val(dtt.Rows(j)(1))
dtt.Rows.RemoveAt(j)
j -= 1
_count -= 1
End If
End If
Next
Next
Dim _sum As Double = 0
Dim _sum2 As Double = 0
ProgressBar1.Value = 0
ProgressBar1.Maximum = dtt.Rows.Count
For i = 0 To dtt.Rows.Count - 1
Dim dashow As New SqlDataAdapter("select is_deleted,sale_price from currencies where id=" + dtt.Rows(i)(0), conn)
Dim dtshow As New DataTable
dashow.Fill(dtshow)
If Not Convert.ToBoolean(dtshow.Rows(0)(0)) Then
Dim _SumExchange As Double = 0
Dim _CountSum As Integer = 0
Dim _AvgExchange As Double = 0
da = New SqlDataAdapter("select sum(val),sum(val1*exchange_price) from inv,inv_sub where " & CondBranch & " currency_from=" & dtt.Rows(i)(0) & _
" and inv.proc_type=1 and inv_sub.proc_type=1 and inv.proc_id=inv_sub.proc_id and IS_Deleted=0", conn)
dt = New DataTable
da.Fill(dt)
If dt.Rows.Count > 0 And dt.Rows(0)(1).ToString <> "" Then
_SumExchange += Val(dt.Rows(0)(1))
_CountSum += Val(dt.Rows(0)(0))
End If
da = New SqlDataAdapter("select sum(val),sum(val1*exchange_price) from inv,inv_sub where " & CondBranch & " currency_from=" & dtt.Rows(i)(0) & _
" and inv.proc_type=3 and inv.proc_id=inv_sub.proc_id and IS_Deleted=0", conn)
dt = New DataTable
da.Fill(dt)
If dt.Rows.Count > 0 And dt.Rows(0)(1).ToString <> "" Then
_SumExchange += Val(dt.Rows(0)(1))
_CountSum += Val(dt.Rows(0)(0))
End If
Try
_AvgExchange = _SumExchange / _CountSum
_AvgExchange = Math.Floor(_AvgExchange * 100000000)
_AvgExchange = _AvgExchange / 100000000
dgvItems.Rows.Add()
Dim _unit As String = ""
If chkNormal.Checked Then
_unit = GetUnit(dtt.Rows(i)(0))
Else
_unit = GetUnitDefault(dtt.Rows(i)(0))
End If
Dim _perc As Double = 1
If chkDefault.Checked Then
_perc = GetUnitDefaultPerc(dtt.Rows(i)(0))
End If
dgvItems.Rows(dgvItems.Rows.Count - 1).Cells(0).Value = dtt.Rows(i)(0)
dgvItems.Rows(dgvItems.Rows.Count - 1).Cells(1).Value = GetCurrencyName(dtt.Rows(i)(0))
dgvItems.Rows(dgvItems.Rows.Count - 1).Cells(2).Value = Math.Round(Val(dtt.Rows(i)(1)) / _perc, 3)
dgvItems.Rows(dgvItems.Rows.Count - 1).Cells(3).Value = _unit
dgvItems.Rows(dgvItems.Rows.Count - 1).Cells(4).Value = String.Format("{0:0.#,##.##}", _AvgExchange)
dgvItems.Rows(dgvItems.Rows.Count - 1).Cells(5).Value = String.Format("{0:0.#,##.##}", Math.Round(_AvgExchange * Val(dtt.Rows(i)(1)), 4))
dgvItems.Rows(dgvItems.Rows.Count - 1).Cells(6).Value = String.Format("{0:0.#,##.##}", dtshow.Rows(0)("sale_price"))
dgvItems.Rows(dgvItems.Rows.Count - 1).Cells(7).Value = String.Format("{0:0.#,##.##}", Math.Round(Val(dtshow.Rows(0)("sale_price")) * Val(dtt.Rows(i)(1)), 4))
_sum += Math.Round(_AvgExchange * Val(dtt.Rows(i)(1)), 4)
_sum2 += Math.Round(Val(dtshow.Rows(0)("sale_price")) * Val(dtt.Rows(i)(1)), 4)
ProgressBar1.Value += 1
Catch ex As Exception
End Try
End If
Next
txtSum.Text = String.Format("{0:0.#,##.##}", _sum)
txtSum2.Text = String.Format("{0:0.#,##.##}", _sum2)
Catch ex As Exception
Finally
_Finished = True
End Try
End Subالمشكلة
كود :
Dim _SumExchange As Double = 0
Dim _CountSum As Integer = 0
Dim _AvgExchange As Double = 0
da = New SqlDataAdapter("select sum(val),sum(val1*exchange_price) from inv,inv_sub where " & CondBranch & " currency_from=" & dtt.Rows(i)(0) & _
" and inv.proc_type=1 and inv_sub.proc_type=1 and inv.proc_id=inv_sub.proc_id and IS_Deleted=0", conn)
dt = New DataTable
da.Fill(dt)
If dt.Rows.Count > 0 And dt.Rows(0)(1).ToString <> "" Then
_SumExchange += Val(dt.Rows(0)(1))
_CountSum += Val(dt.Rows(0)(0))
End If
da = New SqlDataAdapter("select sum(val),sum(val1*exchange_price) from inv,inv_sub where " & CondBranch & " currency_from=" & dtt.Rows(i)(0) & _
" and inv.proc_type=3 and inv.proc_id=inv_sub.proc_id and IS_Deleted=0", conn)
dt = New DataTable
da.Fill(dt)
If dt.Rows.Count > 0 And dt.Rows(0)(1).ToString <> "" Then
_SumExchange += Val(dt.Rows(0)(1))
_CountSum += Val(dt.Rows(0)(0))
End If
Try
_AvgExchange = _SumExchange / _CountSum
_AvgExchange = Math.Floor(_AvgExchange * 100000000)
_AvgExchange = _AvgExchange / 100000000اسف للاطالة وشاكرين ومقدرين حسن تعاونكم ومجهودكم الرائع
