كيف احصل على رصيد بالمخزن؟ - نسخة قابلة للطباعة

كود :
Option Explicit '' البعض يحذف هذا السطر مما يتسبب لنفسه بالمتاعب



'' AccessDatabase4.0: store.mdb

''  Tables:-   Operations: (ID(Key)>AutoNumber , date>DateTime , ItemID>Integer , QtyIn>Single , QtyOut>Single , Operation>Byte )

''             Items:      (ID(Key)>AutoNumber , Description>Text , Quantity(محجوز)>Single , OrderPoint(محجوز)>Single )



'' 2 MSFlexGrid



Dim myConnection As New ADODB.Connection

Dim myCommand As New ADODB.Command

Dim myRecordSet As New ADODB.Recordset





Private Sub Form_Load()

    myConnection.ConnectionString = " Provider=Microsoft.Jet.OLEDB.4.0;         " & _

                                    " Data Source=" & App.Path & "\store.mdb;   " & _

                                    " Persist Security Info=False               "

    myConnection.CursorLocation = adUseClient

    

    Call OperatiosFlexGrid

    Call OperatiosDBLoad

    

    Call ItemsFlexGrid

    Call ItemsDBLoad

    

End Sub



Private Sub OperatiosFlexGrid()

    With Me.MSFlexGrid1

        .Rows = 1

        .Cols = 6

        .ColWidth(0) = 1000: .ColAlignment(0) = flexAlignCenterCenter

        .ColWidth(1) = 1200: .ColAlignment(1) = flexAlignCenterCenter

        .ColWidth(2) = 800: .ColAlignment(2) = flexAlignCenterCenter

        .ColWidth(3) = 2200: .ColAlignment(3) = flexAlignLeftCenter

        .ColWidth(4) = 800:  .ColAlignment(4) = flexAlignCenterCenter

        .ColWidth(5) = 800:  .ColAlignment(5) = flexAlignCenterCenter

        .TextMatrix(0, 0) = "رقم العملية"

        .TextMatrix(0, 1) = "التاريخ"

        .TextMatrix(0, 2) = "رقم الصنف"

        .TextMatrix(0, 3) = "وصف الصنف"

        .TextMatrix(0, 4) = "الكمية"

        .TextMatrix(0, 5) = "العملية"

    End With

End Sub



Private Sub OperatiosDBLoad()



    Dim sql As String

    sql = " SELECT *                            " & _

          "      , IIf(QtyIn = 0, QtyOut, QtyIn) AS Quantity                        " & _

          "      , IIf(Operations.Operation = 0, 'بيع' ,'شراء') AS OperationName    " & _

          "                                     " & _

          "   FROM Operations                   " & _

          "                                     " & _

          " INNER JOIN Items                    " & _

          " ON Operations.ItemID = Items.ID     " & _

          "                                     " & _

          " ORDER BY [Date]                     "

          

    myConnection.Open

    

    myCommand.ActiveConnection = myConnection

    myCommand.CommandText = sql

    myCommand.CommandType = adCmdText

    

    myRecordSet.CursorType = adOpenStatic

    myRecordSet.CursorLocation = adUseClient

    myRecordSet.LockType = adLockOptimistic

    myRecordSet.Open myCommand

  

    If myRecordSet.RecordCount > 0 Then

        With Me.MSFlexGrid1

            

            .Rows = myRecordSet.RecordCount + 1

            Dim r As Integer

            For r = 1 To myRecordSet.RecordCount

                    .TextMatrix(r, 0) = Format$(myRecordSet.Fields("Operations.id"), "0000")    '' رقم العملية

                    .TextMatrix(r, 1) = Format$(myRecordSet.Fields("Date"), "yyyy/MM/dd")   '' تاريخ العملية

                    .TextMatrix(r, 2) = Format$(myRecordSet.Fields("ItemID"), "0000")       '' رقم الصنف

                    .TextMatrix(r, 3) = myRecordSet.Fields("Description")   '' وصف الصنف

                    .TextMatrix(r, 4) = myRecordSet.Fields("Quantity")      '' الكمية

                    .TextMatrix(r, 5) = myRecordSet.Fields("OperationName") '' نوع العملية

                myRecordSet.MoveNext

            Next r

            Me.Label1.Caption = "مجموع العمليات: " & Str(myRecordSet.RecordCount)

            

        End With

    End If

        

    myConnection.Close

    

End Sub







Private Sub ItemsFlexGrid()

    With Me.MSFlexGrid2

        .Rows = 1

        .Cols = 5

        .ColWidth(0) = 1000: .ColAlignment(0) = flexAlignCenterCenter

        .ColWidth(1) = 2200: .ColAlignment(1) = flexAlignLeftCenter

        .ColWidth(2) = 1200: .ColAlignment(2) = flexAlignCenterCenter

        .ColWidth(3) = 1200: .ColAlignment(3) = flexAlignCenterCenter

        .ColWidth(4) = 1200:  .ColAlignment(4) = flexAlignCenterCenter

        .TextMatrix(0, 0) = "رقم الصنف"

        .TextMatrix(0, 1) = "وصف الصنف"

        .TextMatrix(0, 2) = "كمية الشراء"

        .TextMatrix(0, 3) = "كمية البيع"

        .TextMatrix(0, 4) = "الكمية المتبقية"

    End With

End Sub





Private Sub ItemsDBLoad()

    

    Dim sql As String

    sql = " SELECT    SUM(QtyIn)  AS SUMSale    " & _

          "         , SUM(QtyOut) AS SUMBuy     " & _

          "         , (SUM(QtyIn) - SUM(QtyOut)) AS SUMQuantity     " & _

          "         , Description, Operations.ItemID                " & _

          "                                     " & _

          "   FROM Operations                   " & _

          "                                     " & _

          " INNER JOIN Items                    " & _

          " ON Operations.ItemID = Items.ID     " & _

          "                                     " & _

          " GROUP BY Description,ItemID         " & _

          "                                     " & _

          " ORDER BY ItemID                     "

          

    myConnection.Open

    

    myCommand.ActiveConnection = myConnection

    myCommand.CommandText = sql

    myCommand.CommandType = adCmdText

    

    myRecordSet.CursorType = adOpenStatic

    myRecordSet.CursorLocation = adUseClient

    myRecordSet.LockType = adLockOptimistic

    myRecordSet.Open myCommand

  

    If myRecordSet.RecordCount > 0 Then

        With Me.MSFlexGrid2

            

            .Rows = myRecordSet.RecordCount + 1

            Dim r As Integer

            For r = 1 To myRecordSet.RecordCount

                    .TextMatrix(r, 0) = Format$(myRecordSet.Fields("ItemID"), "0000")   '' رقم الصنف

                    .TextMatrix(r, 1) = myRecordSet.Fields("Description")   '' نوع الصنف

                    .TextMatrix(r, 2) = myRecordSet.Fields("SUMSale")       '' كمية الشراء

                    .TextMatrix(r, 3) = myRecordSet.Fields("SUMBuy")        '' كمية البيع

                    .TextMatrix(r, 4) = myRecordSet.Fields("SUMQuantity")   '' الكمية المتبقية

                myRecordSet.MoveNext

            Next r

        

        End With

    End If

        

    myConnection.Close

    

End Sub