تقييم الموضوع :
  • 0 أصوات - بمعدل 0
  • 1
  • 2
  • 3
  • 4
  • 5
[كود] مشكلة بكود حساب المخزون و متوسط التكلفة
#1
كود :
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()


وهو انة بيجيب متوسط السعر لكل الكمية الى اشتريتها من البداية حتى لة تم بيعها ويحسب اجمالى الكمية المشتراة واجمالى السعر ويقسم ويظهر متوسط السعر
المطلوب هو ان يكون المتوسط يتم عملة مثلا على اخر سعرين شراء فقط او لمده قليلة فقط وليس من بداية المشتريات
شكراا جدااا لما اجده من دعم داخل المنتدى
الرد }}}
تم الشكر بواسطة:
#2
[quote='ahmedramzyaish' pid='162874' dateline='1592327437']


برجاء المساعدة
كل المطلوب هو حساب متوسط السعر ولكن ليس عن كل الكمية وكل الاسعار فقط عن اخر عمليتين شراء
هو يحسب سعر التكلفة الان هكذا
مثلا تم شراء 1 ق بسعر 100ج
ثم تم شراء 1 ق بسعر 200ج
ثم تم شراء 1 ق بسعر 75 ج
هو يحسب 100+200+75= 375ج
ومتوسط السعر يكون 375 / 3= 125ج
المطلوب هو
حساب المتوسط عن اخر عمليتين فقط بمعنى اخر 2 فاتورة كانو
ثم تم شراء 1 ق بسعر 200ج
ثم تم شراء 1 ق بسعر 75 ج
فيكون المتوسط 200+75=275
الرد }}}
تم الشكر بواسطة:
#3
اعمل استعلام شبيه بهاذ حسب جدولك و بإذن الله ستحصل على النتيجة المرجوة :

PHP كود :
SELECT        SUM(UnitPrice) / AS UnitPrice
FROM            Products
WHERE        
(ProductID >
 
                            (SELECT        MAX(ProductID) - AS ProductID
                                FROM            Products 
AS Products_1)) 
الرد }}}
#4
(17-06-20, 02:26 PM)asemshahen5 كتب : اعمل استعلام شبيه بهاذ حسب جدولك و بإذن الله ستحصل على النتيجة المرجوة :

PHP كود :
SELECT        SUM(UnitPrice) / AS UnitPrice
FROM            Products
WHERE        
(ProductID >
 
                            (SELECT        MAX(ProductID) - AS ProductID
                                FROM            Products 
AS Products_1)) 
الف الف شكر استاذنا
اين يتم وضع هذا الاستعلام فى الكود الخاص بى
الرد }}}
تم الشكر بواسطة: asemshahen5
#5
ضعه قبل التكست الذي تريد عرض النتيجة به .
الرد }}}
تم الشكر بواسطة: ابراهيم ايبو
#6
(17-06-20, 02:33 PM)asemshahen5 كتب : ضعه قبل التكست الذي تريد عرض النتيجة به .

كود :
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

حيث ان
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)
الرد }}}
تم الشكر بواسطة: asemshahen5 , ابراهيم ايبو
#7
اضف هذا الاستعلام الى نهاية استعلامك لانه بصراحة جداولك و الفورم ليست عندي لاطبق عليها :

PHP كود :
WHERE (ProductID > (SELECT MAX(ProductID) - AS ProductID FROM Products AS Products_1)) 
الرد }}}
تم الشكر بواسطة: ابراهيم ايبو
#8
(17-06-20, 02:54 PM)asemshahen5 كتب : اضف هذا الاستعلام الى نهاية استعلامك لانه بصراحة جداولك و الفورم ليست عندي لاطبق عليها :

PHP كود :
WHERE (ProductID > (SELECT MAX(ProductID) - AS ProductID FROM Products AS Products_1)) 
اسف تعبك معاى بس الموضوع مهم جدا
هل تقصد كده
كود :
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 (ProductID > (SELECT MAX(currency_from) - 2 AS ProductID FROM inv AS Products_1)) and
inv_sub.proc_type=1 and inv.proc_id=inv_sub.proc_id and IS_Deleted=0", conn)
الرد }}}
تم الشكر بواسطة: asemshahen5
#9
استبدل ProductID برقم الفاتورة فقط لا غير .
الرد }}}
تم الشكر بواسطة: ابراهيم ايبو , أبوبكر سويدان



التنقل السريع :


يقوم بقرائة الموضوع: بالاضافة الى ( 1 ) ضيف كريم