16-06-20, 09:10 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
المشكلة فى جذء حساب متوسط السعر الجذء الاتى
كود :
Try
_AvgExchange = _SumExchange / _CountSum
_AvgExchange = Math.Floor(_AvgExchange * 100000000)
_AvgExchange = _AvgExchange / 100000000
dgvItems.Rows.Add()
وهو انة بيجيب متوسط السعر لكل الكمية الى اشتريتها من البداية حتى لة تم بيعها ويحسب اجمالى الكمية المشتراة واجمالى السعر ويقسم ويظهر متوسط السعر
المطلوب هو ان يكون المتوسط يتم عملة مثلا على اخر سعرين شراء فقط او لمده قليلة فقط وليس من بداية المشتريات
شكراا جدااا لما اجده من دعم داخل المنتدى